Customer Details

/* Formatted on 5/22/2015 5:00:26 PM (QP5 v5.163.1008.3004) */

  SELECT DISTINCT

         'EBS' organization_origin_system,

         hp.party_number ebs_customer_number,            --     "Registry ID",

         hca.account_number ebs_cust_account_id,   -- "Account Number",   NULL

         hp.party_name organization_name,          --            "Party Name",

         hp.party_type customer_type,                 --         "Party Type",

         NULL year_established,

         NULL ceo_name,

         NULL address_purpose,

         hl.address1 address_line_1,                            -- "Address1",

         hl.address2 address_line_2,                            -- "Address2",

         hl.address3 address_line_3,                            -- "Address3",

         hl.address4 address_line_4,                             --"Address4",

         hl.city city,

         hl.state state,

         hl.postal_code postal_code,

         (SELECT UPPER (x.territory_short_name) country

            FROM fnd_territories_vl x

           WHERE x.territory_code = hl.country)

            address_country_code,                                ---LOKANADHAM

         --HL.COUNTRY,

         -- HCA.ACCOUNT_NAME ADDRESS_COUNTRY_CODE,       --"Account Description",

         (SELECT NVL (phone_number, raw_phone_number) phone_number

            FROM hz_contact_points

           WHERE     contact_point_type = 'PHONE' --AND OWNER_TABLE_NAME IN ('HZ_PARTY_SITES','HZ_PARTIES')

                 AND owner_table_id = hca.party_id --OR owner_table_id =   HP.PARTY_ID-- hcas.cust_acct_site_id -- , HP.PARTY_ID----

                 AND phone_number IS NOT NULL

                 AND ROWNUM = 1)

            phone_number,

         NULL fax_phone_number,

         NULL address_purpose1,

         NULL address_line_11,

         NULL address_line_21,

         NULL address_line_21,

         NULL address_line_21,

         NULL city1,

         NULL state1,

         NULL postal_code1,

         NULL address_country_code1,

         (SELECT NVL (phone_number, raw_phone_number) phone_number

            FROM hz_contact_points

           WHERE     contact_point_type = 'PHONE'

                 AND owner_table_id = hp.party_id -- hcas.cust_acct_site_id -- , HP.PARTY_ID

                 AND phone_number IS NOT NULL

                 AND ROWNUM = 1)

            phone_number1,

         NULL fax_phone_number1,

         col.name account_owner,   --                        "Collector Name",

         ter.name geography,          --                          "Territory",

         NULL description

    FROM hz_parties hp,

         hz_party_sites hps,

         hz_cust_accounts_all hca,

         hz_cust_acct_sites_all hcas,

         hz_customer_profiles hcp,

         hz_cust_profile_amts hcpa,

         hz_locations hl,

         ra_territories ter,

         ar_collectors col

   WHERE     1 = 1

         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_name = 'STRATUS  SEEC'

         AND hp.party_type = 'ORGANIZATION'   -- only ORGANIZATION Party types

         AND hp.status = 'A'                  -- only Active Parties/Customers

ORDER BY 1, 2

---loka