Customer Query

-- Customer Contact Details :

 select account_number "Account Number"
     , obj.party_name "Customer Name"
     , sub.party_name "Contact Name"
     , hcp.contact_point_type || ': ' ||
       DECODE(hcp.contact_point_type, 'EMAIL', hcp.email_address
                                    , 'PHONE', hcp.phone_area_code || ' ' || hcp.phone_number
                                    , 'WEB'  , hcp.url
                                    , 'Unknow contact Point Type ' || hcp.contact_point_type
             ) "How to Contact"
  from apps.hz_cust_accounts  hca
     , apps.hz_parties        obj
     , apps.hz_relationships  rel
     , apps.hz_contact_points hcp
     , apps.hz_parties        sub
 where hca.party_id           = rel.object_id
   and hca.party_id           = obj.party_id
   and rel.subject_id         = sub.party_id
   and rel.relationship_type  = 'CONTACT'
   and rel.directional_flag   = 'F'
   and rel.party_id           = hcp.owner_table_id
   and hcp.owner_table_name   = 'HZ_PARTIES'
   AND obj.party_name LIKE 'Oxford Unive%';

============================ Customer ==================================

SELECT    hl.orig_system_reference,hl.LAST_UPDATE_DATE
         ,hl.country
         ,hl.address1
         ,hl.address2
         ,hl.address3
         ,hl.address4
         ,hl.city
         ,hl.postal_code
         ,hl.state
         ,hl.province
         ,hl.county
         ,hl.content_source_type
         ,hl.actual_content_source actual_content_source
         ,hps.party_site_number
         ,hps.identifying_address_flag
         ,hps.status
         ,hps.party_site_name
         ,hps.created_by_module
         ,hps.actual_content_source site_actual_content_source
         ,hcasa.orig_system_reference site_orig_system_reference
         ,hcasa.status         acct_site_status
         ,hp.party_number
         ,hca.account_number
         ,hou.NAME operting_unit_name
         ,bill_to_flag
   FROM  apps.hz_locations        hl
        ,apps.hz_party_sites      hps
        ,apps.hz_cust_acct_sites_all  hcasa
        ,apps.hz_parties     hp
        ,apps.hz_cust_accounts   hca
        ,apps.hr_operating_units hou
   WHERE hcasa.party_site_id     = hps.party_site_id
   AND   hps.location_id         = hl.location_id
   AND   hp.party_id             = hca.party_id
   AND   hp.party_id             = hps.party_id
   AND   hca.cust_account_id     = hcasa.cust_account_id
   AND   hcasa.org_id            = hou.organization_id
   AND   hp.party_type           ='ORGANIZATION'
   AND   hps.status              = 'A'
   AND   hcasa.status            = 'A'
   AND   hp.status               = 'A'
   AND   hca.status              = 'A'
  -- AND hl.address1  like '8900%Uni%';
  -- AND   hp.party_number = '14613899';
  AND hp.party_name = 'Stanford University';
 

============================ Customer ==================================
 
   SELECT
       ----------------------------------------------------------
       -- Party Information
       ----------------------------------------------------------
       hp.party_number                      "Registry ID",
       hp.party_name                        "Party Name",
       hp.party_type                        "Party Type",
       DECODE(hp.status,
              'A', 'Active',
              'I', 'Inactive',
              hp.status)                    "Party Status",
       ----------------------------------------------------------
       -- Account Information
       ----------------------------------------------------------
       hca.account_number                   "Account Number",
       DECODE(hca.status,
              'A', 'Active',
              'I', 'Inactive',
              hca.status)                   "Account Status",
       hca.account_name                     "Account Description",
       hca.customer_class_code              "Classification",
       DECODE(hca.customer_type,
              'R', 'External',
              'I', 'Internal',
              hca.customer_type)            "Account Type",
       ----------------------------------------------------------
       -- Site Information
       ----------------------------------------------------------
       hps.party_site_number                "Customer Site Number",
       DECODE(hcas.status,
              'A', 'Active',
              'Inactive')                   "Site Status",
       DECODE(hcas.bill_to_flag,
              'P', 'Primary',
              'Y', 'Yes',
              hcas.bill_to_flag)            "Bill To Flag",
       DECODE(hcas.ship_to_flag,
              'P', 'Primary',
              'Y', 'Yes',
              hcas.ship_to_flag)            "Ship To Flag",
       hcas.cust_acct_site_id               "Customer Acct Site ID",
       ----------------------------------------------------------
       -- Address Information
       ----------------------------------------------------------
       hl.address1                          "Address1",
       hl.address2                          "Address2",
       hl.address3                          "Address3",
       hl.address4                          "Address4",
       hl.city                              "City",
       hl.state                             "State",
       hl.postal_code                       "Zip Code",
       ter.name                             "Territory",

       ----------------------------------------------------------
       -- Collector Information
       ----------------------------------------------------------
       col.name                             "Collector Name",
       ----------------------------------------------------------
       -- Account Profile Information
       ----------------------------------------------------------
       hcp.credit_checking                  "Credit Check Flag",
       hcp.credit_hold                      "Credit Hold Flag",
       hcpa.auto_rec_min_receipt_amount     "Min Receipt Amount",
       hcpa.overall_credit_limit            "Credit Limit",
       hcpa.trx_credit_limit                "Order Credit Limit",
       ----------------------------------------------------------
       -- Attachment Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM apps.fnd_documents_vl doc,
                   apps.fnd_lobs         blo,
                   apps.fnd_attached_documents att
             WHERE doc.media_id = blo.file_id
               AND doc.document_id = att.document_id
               AND att.entity_name = 'AR_CUSTOMERS'
               AND att.pk1_value   = hca.cust_account_id
               AND ROWNUM = 1), 'N'
       ) "Attachment Flag",
       ----------------------------------------------------------
       -- Party Relationship Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM apps.hz_cust_acct_relate_all hzcar
             WHERE hzcar.cust_account_id = hca.cust_account_id
               AND hzcar.relationship_type = 'ALL'
               AND ROWNUM = 1), 'N'
       ) "Party Relationship Flag",
       ----------------------------------------------------------
       -- Account Relationship Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM apps.hz_cust_acct_relate_all hzcar
             WHERE hzcar.cust_account_id = hca.cust_account_id
               AND ROWNUM = 1), 'N'
       ) "Account Relationship Flag",
       ----------------------------------------------------------
       -- Party Contact Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM apps.hz_parties hp2
             WHERE 1=1
               AND hp2.party_id = hp.party_id
               AND (
                    hp2.url IS NOT NULL OR
                    -- LENGTH(TRIM(hp.email_address)) > 5
                    INSTR(hp2.email_address, '@') > 0 OR
                    hp2.primary_phone_purpose IS NOT NULL
                    )
           ), 'N'
       ) "Party Contact Flag",
       ----------------------------------------------------------
       -- Account Contact Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM apps.hz_contact_points
             WHERE status = 'A'
               AND owner_table_id =
                   (SELECT hcar.party_id
                      FROM apps.hz_cust_account_roles   hcar,
                           apps.ar_contacts_v           acv
                     WHERE hcar.cust_account_id   = hca.cust_account_id
                       AND hcar.cust_account_role_id = acv.contact_id
                       AND hcar.cust_acct_site_id IS NULL  -- look for account level only
                       AND ROWNUM = 1 -- add this row to show inactive sites (i.e. with no site id)
                    )
               AND ROWNUM = 1), 'N'
       ) "Account Contact Flag",
       ----------------------------------------------------------
       -- Site Contact Flag
       ----------------------------------------------------------
       NVL((SELECT 'Y'
              FROM apps.hz_contact_points
             WHERE status = 'A'
               AND owner_table_id =
                   (
                      SELECT hcar.party_id
                        FROM apps.hz_cust_account_roles   hcar,
                             apps.ar_contacts_v           acv
                       WHERE hcar.cust_acct_site_id     =  hcas.cust_acct_site_id
                         AND hcar.cust_account_role_id  =  acv.contact_id
                         AND ROWNUM = 1  -- add this row to show inactive sites (i.e. with no site id)
                   )
               AND ROWNUM = 1), 'N'      -- any contact (email, phone, fax) would suffice this condition
       ) "Site Contact Flag"
  FROM
       apps.hz_parties              hp,
       apps.hz_party_sites          hps,
       apps.hz_cust_accounts_all    hca,
       apps.hz_cust_acct_sites_all  hcas,
       apps.hz_customer_profiles    hcp,
       apps.hz_cust_profile_amts    hcpa,
       apps.hz_locations            hl,
       apps.ra_territories          ter,
       apps.ar_collectors           col
 WHERE
       1=1
   AND party_name like  'Florida Power%'
   AND hp.party_id            =  hca.party_id
   AND hca.cust_account_id    =  hcas.cust_account_id(+)
   AND hps.party_site_id(+)   =  hcas.party_site_id
   AND hp.party_id            =  hcp.party_id 
   AND hca.cust_account_id    =  hcp.cust_account_id
   AND hps.location_id        =  hl.location_id(+)
   AND col.collector_id       =  hcp.collector_id
   AND hcas.territory_id      =  ter.territory_id(+)
   AND hcp.cust_account_profile_id = hcpa.cust_account_profile_id
 --  AND hp.party_type          = 'ORGANIZATION'    -- only ORGANIZATION Party types
  -- AND hp.status              = 'A'               -- only Active Parties/Customers
   AND hp.party_number        = 8899
 ORDER BY TO_NUMBER(hp.party_number), hp.party_name, hca.account_number;