/* Formatted on 5/22/2015 5:00:16 PM (QP5
v5.163.1008.3004) */
SELECT DISTINCT
(SELECT first_name
FROM ar_contacts_v v
WHERE v.customer_id = hca.cust_account_id
AND v.status = 'A'
AND ROWNUM = 1)
first_name,
NULL "MIDDLE_NAME",
(SELECT last_name
FROM ar_contacts_v v
WHERE v.customer_id = hca.cust_account_id
AND v.status = 'A'
AND ROWNUM = 1)
last_name,
(SELECT NVL (job_title, job_title_code_meaning) job_title
FROM ar_contacts_v v
WHERE v.customer_id = hca.cust_account_id
AND v.status = 'A'
AND ROWNUM = 1)
"JOB_TITLE",
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
(SELECT phone_number
FROM hz_contact_points a
WHERE a.owner_table_id = hps.party_site_id -- hp.PARTY_ID
AND contact_point_type = 'PHONE'
AND phone_line_type = 'GEN'
AND ROWNUM = 1)
work_phone,
(SELECT NVL (phone_number, raw_phone_number) phone_number
FROM hz_contact_points a
WHERE a.owner_table_id = hps.party_site_id
--hp.PARTY_ID
AND contact_point_type = 'PHONE'
AND phone_line_type = 'MOBILE'
AND ROWNUM = 1)
mobile_phone_number,
(SELECT email_address
FROM hz_contact_points a
WHERE a.owner_table_id = hps.party_site_id -- hp.PARTY_ID
AND contact_point_type = 'EMAIL'
AND ROWNUM = 1)
email,
NULL "FAVOURITE_CONTACT",
hp.party_name organization_name, --
"Party Name",
'CONTACT' "RELATION_SHIP",
-- HP.PARTY_NUMBER
EBS_CUSTOMER_NUMBER, -- "Registry ID",
hca.account_number ebs_cust_account_id -- "Account Number", NULL
/* 'EBS' ORGANIZATION_ORIGIN_SYSTEM,
HP.PARTY_TYPE CUSTOMER_TYPE, -- "Party Type",
NULL YEAR_ESTABLISHED,
NULL CEO_NAME,
NULL ADDRESS_PURPOSE,
--HL.COUNTRY,
-- HCA.ACCOUNT_NAME ADDRESS_COUNTRY_CODE, --"Account Description",
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,
(SELECT A.ADDRESS1 FROM
HZ_LOCATIONS A, HZ_PARTY_SITES B WHERE A.LOCATION_ID = B.LOCATION_ID AND B.PARTY_ID = HP.PARTY_ID AND ROWNUM=1) CONTACT_ADDRESS1,
(SELECT A.ADDRESS2 FROM
HZ_LOCATIONS A, HZ_PARTY_SITES B
WHERE A.LOCATION_ID =
B.LOCATION_ID AND B.PARTY_ID =
HP.PARTY_ID AND ROWNUM=1) CONTACT_ADDRESS2,
(SELECT A.ADDRESS3 FROM
HZ_LOCATIONS A, HZ_PARTY_SITES B
WHERE A.LOCATION_ID =
B.LOCATION_ID AND B.PARTY_ID =
HP.PARTY_ID AND ROWNUM=1) CONTACT_ADDRESS3,
(SELECT A.ADDRESS4 FROM
HZ_LOCATIONS A, HZ_PARTY_SITES B WHERE A.LOCATION_ID = B.LOCATION_ID AND B.PARTY_ID = HP.PARTY_ID AND ROWNUM=1) CONTACT_ADDRESS4,
(SELECT A.CITY FROM HZ_LOCATIONS
A, HZ_PARTY_SITES B WHERE A.LOCATION_ID = B.LOCATION_ID AND B.PARTY_ID = HP.PARTY_ID AND ROWNUM=1) CITY,
(SELECT A.STATE FROM HZ_LOCATIONS A, HZ_PARTY_SITES B WHERE
A.LOCATION_ID = B.LOCATION_ID
AND B.PARTY_ID = HP.PARTY_ID AND
ROWNUM=1) STATE,
(SELECT A.POSTAL_CODE FROM
HZ_LOCATIONS A, HZ_PARTY_SITES B
WHERE A.LOCATION_ID = B.LOCATION_ID AND
B.PARTY_ID = HP.PARTY_ID AND
ROWNUM=1) POSTAL_CODE,
(SELECT A.COUNTRY FROM
HZ_LOCATIONS A, HZ_PARTY_SITES B
WHERE A.LOCATION_ID =
B.LOCATION_ID AND B.PARTY_ID =
HP.PARTY_ID AND ROWNUM=1) ADDRESS_COUNTRY_CODE
*/
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