VIEW: APPS.OE_CUSTOMER_SITE_CONTACTS_V

Source

View Text - Preformatted

SELECT SITE.LOCATION,PARTY.PARTY_NAME CUSTOMER_NAME, PARTY.EMAIL_ADDRESS, SITE.SITE_USE_ID ORGANIZATION_ID, SITE.LOCATION LOCATION_CODE, LOC.ADDRESS1 ADDRESS_LINE_1, LOC.ADDRESS2 ADDRESS_LINE_2, LOC.ADDRESS3 ADDRESS_LINE_3, decode(LOC.CITY,null, null, LOC.CITY|| ', ') ||decode(LOC.state, null, null, LOC.state || ', ') || decode(LOC.postal_code,null, null, LOC.postal_code || ', ') || decode(LOC.country, null, null, LOC.country) address_line_4 , LOC.ADDRESS4 ADDRESS_LINE_5, LOC.CITY, LOC.STATE, LOC.POSTAL_CODE, LOC.COUNTRY, CUST_ACCT.CUST_ACCOUNT_ID customer_id, cust_acct.account_number customer_Number, acct_site.status acct_site_status, cust_acct.status cust_acct_status, site.status site_status, site.site_use_code site_use_code, acct_site.cust_acct_site_id cust_acct_site_id, CONT_PARTY.PERSON_FIRST_NAME FIRST_NAME ,CONT_PARTY.PERSON_LAST_NAME LAST_NAME, REL_PARTY.EMAIL_ADDRESS CONTACT_EMAIL, 'A' dummy, DECODE(ROLE_RES.RESPONSIBILITY_TYPE,'SHIP_TO','SHIP_TO', 'BILL_TO','BILL_TO',ROLE_RES.RESPONSIBILITY_TYPE) ROLE_TYPE, ROLE_RES.RESPONSIBILITY_TYPE ROLE_TYPE_CODE, CONT_PARTY.PERSON_LAST_NAME || DECODE(CONT_PARTY.PERSON_FIRST_NAME, NULL, NULL, ', '|| CONT_PARTY.PERSON_FIRST_NAME)|| DECODE(CONT_ARL.meaning,NULL, NULL, ' '||CONT_ARL.meaning) CONTACT_NAME, ACCT_ROLE.CUST_ACCOUNT_ROLE_ID CONTACT_ID, (select DECODE(SIT.SITE_USE_CODE,'BILL_TO', 'Yes','No') from HZ_CUST_SITE_USES_ALL SIT WHERE sit.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID AND SIT.SITE_USE_CODE='BILL_TO' AND status='A')Bill_TO_SITE, (select DECODE(SIT.SITE_USE_CODE,'SHIP_TO', 'Yes','No') from HZ_CUST_SITE_USES_ALL SIT where sit.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID AND SIT.SITE_USE_CODE='SHIP_TO' AND status='A')SHIP_TO_SITE, (select DECODE(SIT.SITE_USE_CODE,'SOLD_TO', 'Yes','No') from HZ_CUST_SITE_USES_ALL SIT where sit.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID AND SIT.SITE_USE_CODE='SOLD_TO' AND status='A')SOLD_TO_SITE, (select DECODE(SIT.SITE_USE_CODE,'DELIVER_TO', 'Yes','No') from HZ_CUST_SITE_USES_ALL SIT where sit.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID AND SIT.SITE_USE_CODE='DELIVER_TO' AND status='A')DELIVER_TO_SITE, (select DECODE(ROL.RESPONSIBILITY_TYPE,'SHIP_TO','Yes', 'No',NVL(ROLE_RES.RESPONSIBILITY_TYPE,'X')) FROM HZ_ROLE_RESPONSIBILITY ROL WHERE ROL.CUST_ACCOUNT_ROLE_ID=ACCT_ROLE.CUST_ACCOUNT_ROLE_ID AND ROL.RESPONSIBILITY_TYPE='SHIP_TO') Ship_To_Contact, (select DECODE(ROL.RESPONSIBILITY_TYPE,'BILL_TO','Yes', 'No',NVL(ROLE_RES.RESPONSIBILITY_TYPE,'X')) FROM HZ_ROLE_RESPONSIBILITY ROL WHERE ROL.CUST_ACCOUNT_ROLE_ID=ACCT_ROLE.CUST_ACCOUNT_ROLE_ID AND ROL.RESPONSIBILITY_TYPE='BILL_TO') Bill_To_Contact, (select DECODE(ROL.RESPONSIBILITY_TYPE,'DELIVER_TO','Yes', 'No',NVL(ROLE_RES.RESPONSIBILITY_TYPE,'X')) FROM HZ_ROLE_RESPONSIBILITY ROL WHERE ROL.CUST_ACCOUNT_ROLE_ID=ACCT_ROLE.CUST_ACCOUNT_ROLE_ID AND ROL.RESPONSIBILITY_TYPE='DELIVER_TO') Deliver_To_Contact, (select DECODE(NVL(ROL.RESPONSIBILITY_TYPE,'X'),'X','All','None') FROM HZ_ROLE_RESPONSIBILITY ROL,HZ_CUST_ACCOUNT_ROLES ACC_ROL WHERE ROL.CUST_ACCOUNT_ROLE_ID(+)=ACC_ROL.CUST_ACCOUNT_ROLE_ID AND ACC_ROL.CUST_ACCOUNT_ROLE_ID=ACCT_ROLE.CUST_ACCOUNT_ROLE_ID AND NVL(ROL.RESPONSIBILITY_TYPE,'X') NOT IN (SELECT LOOKUP_CODE FROM AR_LOOKUPS ar WHERE ar.lookup_type = 'SITE_USE_CODE' AND ar.enabled_flag = 'Y' AND trunc(sysdate) between ar.start_date_active and nvl(ar.end_date_active,trunc (sysdate))) ) All_Contacts, site.org_id , ACCT_ROLE.STATUS ROLE_STATUS FROM HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS CUST_ACCT, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, HZ_PARTIES CONT_PARTY, HZ_RELATIONSHIPS REL, AR_LOOKUPS CONT_ARL, HZ_PARTIES REL_PARTY, HZ_CUST_ACCOUNTS CONT_ACCT, HZ_ROLE_RESPONSIBILITY ROLE_RES, HZ_CUST_ACCT_SITES ACCT_SITE, HZ_CUST_SITE_USES_ALL SITE, HZ_PARTY_SITES PARTY_SITE, HZ_LOCATIONS LOC WHERE CUST_ACCT.PARTY_ID=PARTY.PARTY_ID AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID AND ACCT_ROLE.ROLE_TYPE = 'CONTACT' AND CONT_ARL.LOOKUP_TYPE(+) = 'CONTACT_TITLE' AND CONT_ARL.LOOKUP_CODE(+) = CONT_PARTY.PERSON_PRE_NAME_ADJUNCT AND REL.SUBJECT_ID = CONT_PARTY.PARTY_ID AND REL.PARTY_ID = REL_PARTY.PARTY_ID AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.OBJECT_ID = CONT_ACCT.PARTY_ID AND ACCT_ROLE.CUST_ACCOUNT_ID= CONT_ACCT.CUST_ACCOUNT_ID AND CUST_ACCT.CUST_ACCOUNT_ID=ACCT_ROLE.CUST_ACCOUNT_ID AND ROLE_RES.CUST_ACCOUNT_ROLE_ID(+)=ACCT_ROLE.CUST_ACCOUNT_ROLE_ID AND ACCT_ROLE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID AND ACCT_ROLE.CUST_ACCT_SITE_ID IS NOT NULL AND ACCT_SITE.CUST_ACCOUNT_ID=CUST_ACCT.CUST_ACCOUNT_ID AND SITE.CUST_ACCT_SITE_ID(+) = ACCT_SITE.CUST_ACCT_SITE_ID AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID AND PARTY_SITE.LOCATION_ID= LOC.LOCATION_ID AND site.org_id(+) = acct_site.org_id AND NVL(SITE.SITE_USE_CODE,'X') IN (select NVL(SIT.SITE_USE_CODE,'X') from HZ_CUST_SITE_USES_ALL SIT,HZ_CUST_ACCT_SITES ACC_SITE where sit.CUST_ACCT_SITE_ID(+) = ACC_SITE.CUST_ACCT_SITE_ID AND ACCT_SITE.CUST_ACCT_SITE_ID=ACC_SITE.CUST_ACCT_SITE_ID ) union all SELECT decode(party.party_name,party.party_name,null) Location, PARTY.PARTY_NAME CUSTOMER_NAME, PARTY.EMAIL_ADDRESS CUSTOMER_EMAIL, decode(party.party_id,party.party_id,-1) ORGANIZATION_ID, decode(party.party_name,party.party_name,null) LOCATION_CODE, decode(party.party_name,party.party_name,null) ADDRESS_LINE_1, decode(party.party_name,party.party_name,null) ADDRESS_LINE_2, decode(party.party_name,party.party_name,null) ADDRESS_LINE_3, decode(party.party_name,party.party_name,null) address_line_4 , decode(party.party_name,party.party_name,null) ADDRESS_LINE_5, decode(party.party_name,party.party_name,null) CITY, decode(party.party_name,party.party_name,null) STATE, decode(party.party_name,party.party_name,null) POSTAL_CODE, decode(party.party_name,party.party_name,null) COUNTRY, CUST_ACCT.CUST_ACCOUNT_ID customer_id, cust_acct.account_number customer_Number, decode(party.party_name,party.party_name,null) acct_site_status, cust_acct.status cust_acct_status, decode(party.party_name,party.party_name,null) site_status, decode(party.party_name,party.party_name,null) site_use_code, decode(party.party_id,party.party_id,-1) cust_acct_site_id, CONT_PARTY.PERSON_FIRST_NAME FIRST_NAME, CONT_PARTY.PERSON_LAST_NAME LAST_NAME, REL_PARTY.EMAIL_ADDRESS CONTACT_EMAIL, 'B' dummy, DECODE(ROLE_RES.RESPONSIBILITY_TYPE,'SHIP_TO','SHIP_TO', 'BILL_TO','BILL_TO',ROLE_RES.RESPONSIBILITY_TYPE) ROLE_TYPE, ROLE_RES.RESPONSIBILITY_TYPE ROLE_TYPE_CODE, CONT_PARTY.PERSON_LAST_NAME || DECODE(CONT_PARTY.PERSON_FIRST_NAME, NULL, NULL, ', '|| CONT_PARTY.PERSON_FIRST_NAME)|| DECODE(CONT_ARL.meaning,NULL, NULL, ' '||CONT_ARL.meaning) CONTACT_NAME, ACCT_ROLE.CUST_ACCOUNT_ROLE_ID CONTACT_ID, 'No' Bill_To_Site, 'No' Ship_To_Site, 'No' Sold_To_Site, 'No' Deliver_To_Site, (select DECODE(ROL.RESPONSIBILITY_TYPE,'SHIP_TO','Yes', 'No',NVL(ROLE_RES.RESPONSIBILITY_TYPE,'X')) FROM HZ_ROLE_RESPONSIBILITY ROL WHERE ROL.CUST_ACCOUNT_ROLE_ID=ACCT_ROLE.CUST_ACCOUNT_ROLE_ID AND ROL.RESPONSIBILITY_TYPE='SHIP_TO') Ship_To_Contact, (select DECODE(ROL.RESPONSIBILITY_TYPE,'BILL_TO','Yes', 'No',NVL(ROLE_RES.RESPONSIBILITY_TYPE,'X')) FROM HZ_ROLE_RESPONSIBILITY ROL WHERE ROL.CUST_ACCOUNT_ROLE_ID=ACCT_ROLE.CUST_ACCOUNT_ROLE_ID AND ROL.RESPONSIBILITY_TYPE='BILL_TO') Bill_To_Contact, (select DECODE(ROL.RESPONSIBILITY_TYPE,'DELIVER_TO','Yes', 'No',NVL(ROLE_RES.RESPONSIBILITY_TYPE,'X')) FROM HZ_ROLE_RESPONSIBILITY ROL WHERE ROL.CUST_ACCOUNT_ROLE_ID=ACCT_ROLE.CUST_ACCOUNT_ROLE_ID AND ROL.RESPONSIBILITY_TYPE='DELIVER_TO') Deliver_To_Contact, (select DECODE(NVL(ROL.RESPONSIBILITY_TYPE,'X'),'X','All','None') FROM HZ_ROLE_RESPONSIBILITY ROL,HZ_CUST_ACCOUNT_ROLES ACC_ROL WHERE ROL.CUST_ACCOUNT_ROLE_ID(+)=ACC_ROL.CUST_ACCOUNT_ROLE_ID AND ACC_ROL.CUST_ACCOUNT_ROLE_ID=ACCT_ROLE.CUST_ACCOUNT_ROLE_ID AND NVL(ROL.RESPONSIBILITY_TYPE,'X') NOT IN (SELECT LOOKUP_CODE FROM AR_LOOKUPS ar WHERE ar.lookup_type = 'SITE_USE_CODE' AND ar.enabled_flag = 'Y' AND trunc(sysdate) between ar.start_date_active and nvl(ar.end_date_active,trunc (sysdate)))) All_Contacts, null org_id, ACCT_ROLE.STATUS ROLE_STATUS FROM HZ_PARTIES PARTY, HZ_CUST_ACCOUNTS CUST_ACCT, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, HZ_PARTIES CONT_PARTY, HZ_RELATIONSHIPS REL, AR_LOOKUPS CONT_ARL, HZ_PARTIES REL_PARTY, HZ_CUST_ACCOUNTS CONT_ACCT, HZ_ROLE_RESPONSIBILITY ROLE_RES WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID AND CUST_ACCT.PARTY_ID=PARTY.PARTY_ID AND ACCT_ROLE.ROLE_TYPE = 'CONTACT' AND CONT_ARL.LOOKUP_TYPE(+) = 'CONTACT_TITLE' AND CONT_ARL.LOOKUP_CODE(+) = CONT_PARTY.PERSON_PRE_NAME_ADJUNCT AND REL.SUBJECT_ID = CONT_PARTY.PARTY_ID AND REL.PARTY_ID = REL_PARTY.PARTY_ID AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.OBJECT_ID = CONT_ACCT.PARTY_ID AND ACCT_ROLE.CUST_ACCOUNT_ID = CONT_ACCT.CUST_ACCOUNT_ID AND CUST_ACCT.CUST_ACCOUNT_ID=ACCT_ROLE.CUST_ACCOUNT_ID AND ROLE_RES.CUST_ACCOUNT_ROLE_ID(+)=ACCT_ROLE.CUST_ACCOUNT_ROLE_ID AND ACCT_ROLE.CUST_ACCT_SITE_ID IS NULL

View Text - HTML Formatted

SELECT SITE.LOCATION
, PARTY.PARTY_NAME CUSTOMER_NAME
, PARTY.EMAIL_ADDRESS
, SITE.SITE_USE_ID ORGANIZATION_ID
, SITE.LOCATION LOCATION_CODE
, LOC.ADDRESS1 ADDRESS_LINE_1
, LOC.ADDRESS2 ADDRESS_LINE_2
, LOC.ADDRESS3 ADDRESS_LINE_3
, DECODE(LOC.CITY
, NULL
, NULL
, LOC.CITY|| '
, ') ||DECODE(LOC.STATE
, NULL
, NULL
, LOC.STATE || '
, ') || DECODE(LOC.POSTAL_CODE
, NULL
, NULL
, LOC.POSTAL_CODE || '
, ') || DECODE(LOC.COUNTRY
, NULL
, NULL
, LOC.COUNTRY) ADDRESS_LINE_4
, LOC.ADDRESS4 ADDRESS_LINE_5
, LOC.CITY
, LOC.STATE
, LOC.POSTAL_CODE
, LOC.COUNTRY
, CUST_ACCT.CUST_ACCOUNT_ID CUSTOMER_ID
, CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER
, ACCT_SITE.STATUS ACCT_SITE_STATUS
, CUST_ACCT.STATUS CUST_ACCT_STATUS
, SITE.STATUS SITE_STATUS
, SITE.SITE_USE_CODE SITE_USE_CODE
, ACCT_SITE.CUST_ACCT_SITE_ID CUST_ACCT_SITE_ID
, CONT_PARTY.PERSON_FIRST_NAME FIRST_NAME
, CONT_PARTY.PERSON_LAST_NAME LAST_NAME
, REL_PARTY.EMAIL_ADDRESS CONTACT_EMAIL
, 'A' DUMMY
, DECODE(ROLE_RES.RESPONSIBILITY_TYPE
, 'SHIP_TO'
, 'SHIP_TO'
, 'BILL_TO'
, 'BILL_TO'
, ROLE_RES.RESPONSIBILITY_TYPE) ROLE_TYPE
, ROLE_RES.RESPONSIBILITY_TYPE ROLE_TYPE_CODE
, CONT_PARTY.PERSON_LAST_NAME || DECODE(CONT_PARTY.PERSON_FIRST_NAME
, NULL
, NULL
, '
, '|| CONT_PARTY.PERSON_FIRST_NAME)|| DECODE(CONT_ARL.MEANING
, NULL
, NULL
, ' '||CONT_ARL.MEANING) CONTACT_NAME
, ACCT_ROLE.CUST_ACCOUNT_ROLE_ID CONTACT_ID
, (SELECT DECODE(SIT.SITE_USE_CODE
, 'BILL_TO'
, 'YES'
, 'NO')
FROM HZ_CUST_SITE_USES_ALL SIT
WHERE SIT.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND SIT.SITE_USE_CODE='BILL_TO'
AND STATUS='A')BILL_TO_SITE
, (SELECT DECODE(SIT.SITE_USE_CODE
, 'SHIP_TO'
, 'YES'
, 'NO')
FROM HZ_CUST_SITE_USES_ALL SIT
WHERE SIT.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND SIT.SITE_USE_CODE='SHIP_TO'
AND STATUS='A')SHIP_TO_SITE
, (SELECT DECODE(SIT.SITE_USE_CODE
, 'SOLD_TO'
, 'YES'
, 'NO')
FROM HZ_CUST_SITE_USES_ALL SIT
WHERE SIT.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND SIT.SITE_USE_CODE='SOLD_TO'
AND STATUS='A')SOLD_TO_SITE
, (SELECT DECODE(SIT.SITE_USE_CODE
, 'DELIVER_TO'
, 'YES'
, 'NO')
FROM HZ_CUST_SITE_USES_ALL SIT
WHERE SIT.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND SIT.SITE_USE_CODE='DELIVER_TO'
AND STATUS='A')DELIVER_TO_SITE
, (SELECT DECODE(ROL.RESPONSIBILITY_TYPE
, 'SHIP_TO'
, 'YES'
, 'NO'
, NVL(ROLE_RES.RESPONSIBILITY_TYPE
, 'X'))
FROM HZ_ROLE_RESPONSIBILITY ROL
WHERE ROL.CUST_ACCOUNT_ROLE_ID=ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
AND ROL.RESPONSIBILITY_TYPE='SHIP_TO') SHIP_TO_CONTACT
, (SELECT DECODE(ROL.RESPONSIBILITY_TYPE
, 'BILL_TO'
, 'YES'
, 'NO'
, NVL(ROLE_RES.RESPONSIBILITY_TYPE
, 'X'))
FROM HZ_ROLE_RESPONSIBILITY ROL
WHERE ROL.CUST_ACCOUNT_ROLE_ID=ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
AND ROL.RESPONSIBILITY_TYPE='BILL_TO') BILL_TO_CONTACT
, (SELECT DECODE(ROL.RESPONSIBILITY_TYPE
, 'DELIVER_TO'
, 'YES'
, 'NO'
, NVL(ROLE_RES.RESPONSIBILITY_TYPE
, 'X'))
FROM HZ_ROLE_RESPONSIBILITY ROL
WHERE ROL.CUST_ACCOUNT_ROLE_ID=ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
AND ROL.RESPONSIBILITY_TYPE='DELIVER_TO') DELIVER_TO_CONTACT
, (SELECT DECODE(NVL(ROL.RESPONSIBILITY_TYPE
, 'X')
, 'X'
, 'ALL'
, 'NONE')
FROM HZ_ROLE_RESPONSIBILITY ROL
, HZ_CUST_ACCOUNT_ROLES ACC_ROL
WHERE ROL.CUST_ACCOUNT_ROLE_ID(+)=ACC_ROL.CUST_ACCOUNT_ROLE_ID
AND ACC_ROL.CUST_ACCOUNT_ROLE_ID=ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
AND NVL(ROL.RESPONSIBILITY_TYPE
, 'X') NOT IN (SELECT LOOKUP_CODE
FROM AR_LOOKUPS AR
WHERE AR.LOOKUP_TYPE = 'SITE_USE_CODE'
AND AR.ENABLED_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN AR.START_DATE_ACTIVE
AND NVL(AR.END_DATE_ACTIVE
, TRUNC (SYSDATE))) ) ALL_CONTACTS
, SITE.ORG_ID
, ACCT_ROLE.STATUS ROLE_STATUS
FROM HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES CONT_PARTY
, HZ_RELATIONSHIPS REL
, AR_LOOKUPS CONT_ARL
, HZ_PARTIES REL_PARTY
, HZ_CUST_ACCOUNTS CONT_ACCT
, HZ_ROLE_RESPONSIBILITY ROLE_RES
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_CUST_SITE_USES_ALL SITE
, HZ_PARTY_SITES PARTY_SITE
, HZ_LOCATIONS LOC
WHERE CUST_ACCT.PARTY_ID=PARTY.PARTY_ID
AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND CONT_ARL.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND CONT_ARL.LOOKUP_CODE(+) = CONT_PARTY.PERSON_PRE_NAME_ADJUNCT
AND REL.SUBJECT_ID = CONT_PARTY.PARTY_ID
AND REL.PARTY_ID = REL_PARTY.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_ID = CONT_ACCT.PARTY_ID
AND ACCT_ROLE.CUST_ACCOUNT_ID= CONT_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.CUST_ACCOUNT_ID=ACCT_ROLE.CUST_ACCOUNT_ID
AND ROLE_RES.CUST_ACCOUNT_ROLE_ID(+)=ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
AND ACCT_ROLE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_ROLE.CUST_ACCT_SITE_ID IS NOT NULL
AND ACCT_SITE.CUST_ACCOUNT_ID=CUST_ACCT.CUST_ACCOUNT_ID
AND SITE.CUST_ACCT_SITE_ID(+) = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND PARTY_SITE.LOCATION_ID= LOC.LOCATION_ID
AND SITE.ORG_ID(+) = ACCT_SITE.ORG_ID
AND NVL(SITE.SITE_USE_CODE
, 'X') IN (SELECT NVL(SIT.SITE_USE_CODE
, 'X')
FROM HZ_CUST_SITE_USES_ALL SIT
, HZ_CUST_ACCT_SITES ACC_SITE
WHERE SIT.CUST_ACCT_SITE_ID(+) = ACC_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.CUST_ACCT_SITE_ID=ACC_SITE.CUST_ACCT_SITE_ID ) UNION ALL SELECT DECODE(PARTY.PARTY_NAME
, PARTY.PARTY_NAME
, NULL) LOCATION
, PARTY.PARTY_NAME CUSTOMER_NAME
, PARTY.EMAIL_ADDRESS CUSTOMER_EMAIL
, DECODE(PARTY.PARTY_ID
, PARTY.PARTY_ID
, -1) ORGANIZATION_ID
, DECODE(PARTY.PARTY_NAME
, PARTY.PARTY_NAME
, NULL) LOCATION_CODE
, DECODE(PARTY.PARTY_NAME
, PARTY.PARTY_NAME
, NULL) ADDRESS_LINE_1
, DECODE(PARTY.PARTY_NAME
, PARTY.PARTY_NAME
, NULL) ADDRESS_LINE_2
, DECODE(PARTY.PARTY_NAME
, PARTY.PARTY_NAME
, NULL) ADDRESS_LINE_3
, DECODE(PARTY.PARTY_NAME
, PARTY.PARTY_NAME
, NULL) ADDRESS_LINE_4
, DECODE(PARTY.PARTY_NAME
, PARTY.PARTY_NAME
, NULL) ADDRESS_LINE_5
, DECODE(PARTY.PARTY_NAME
, PARTY.PARTY_NAME
, NULL) CITY
, DECODE(PARTY.PARTY_NAME
, PARTY.PARTY_NAME
, NULL) STATE
, DECODE(PARTY.PARTY_NAME
, PARTY.PARTY_NAME
, NULL) POSTAL_CODE
, DECODE(PARTY.PARTY_NAME
, PARTY.PARTY_NAME
, NULL) COUNTRY
, CUST_ACCT.CUST_ACCOUNT_ID CUSTOMER_ID
, CUST_ACCT.ACCOUNT_NUMBER CUSTOMER_NUMBER
, DECODE(PARTY.PARTY_NAME
, PARTY.PARTY_NAME
, NULL) ACCT_SITE_STATUS
, CUST_ACCT.STATUS CUST_ACCT_STATUS
, DECODE(PARTY.PARTY_NAME
, PARTY.PARTY_NAME
, NULL) SITE_STATUS
, DECODE(PARTY.PARTY_NAME
, PARTY.PARTY_NAME
, NULL) SITE_USE_CODE
, DECODE(PARTY.PARTY_ID
, PARTY.PARTY_ID
, -1) CUST_ACCT_SITE_ID
, CONT_PARTY.PERSON_FIRST_NAME FIRST_NAME
, CONT_PARTY.PERSON_LAST_NAME LAST_NAME
, REL_PARTY.EMAIL_ADDRESS CONTACT_EMAIL
, 'B' DUMMY
, DECODE(ROLE_RES.RESPONSIBILITY_TYPE
, 'SHIP_TO'
, 'SHIP_TO'
, 'BILL_TO'
, 'BILL_TO'
, ROLE_RES.RESPONSIBILITY_TYPE) ROLE_TYPE
, ROLE_RES.RESPONSIBILITY_TYPE ROLE_TYPE_CODE
, CONT_PARTY.PERSON_LAST_NAME || DECODE(CONT_PARTY.PERSON_FIRST_NAME
, NULL
, NULL
, '
, '|| CONT_PARTY.PERSON_FIRST_NAME)|| DECODE(CONT_ARL.MEANING
, NULL
, NULL
, ' '||CONT_ARL.MEANING) CONTACT_NAME
, ACCT_ROLE.CUST_ACCOUNT_ROLE_ID CONTACT_ID
, 'NO' BILL_TO_SITE
, 'NO' SHIP_TO_SITE
, 'NO' SOLD_TO_SITE
, 'NO' DELIVER_TO_SITE
, (SELECT DECODE(ROL.RESPONSIBILITY_TYPE
, 'SHIP_TO'
, 'YES'
, 'NO'
, NVL(ROLE_RES.RESPONSIBILITY_TYPE
, 'X'))
FROM HZ_ROLE_RESPONSIBILITY ROL
WHERE ROL.CUST_ACCOUNT_ROLE_ID=ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
AND ROL.RESPONSIBILITY_TYPE='SHIP_TO') SHIP_TO_CONTACT
, (SELECT DECODE(ROL.RESPONSIBILITY_TYPE
, 'BILL_TO'
, 'YES'
, 'NO'
, NVL(ROLE_RES.RESPONSIBILITY_TYPE
, 'X'))
FROM HZ_ROLE_RESPONSIBILITY ROL
WHERE ROL.CUST_ACCOUNT_ROLE_ID=ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
AND ROL.RESPONSIBILITY_TYPE='BILL_TO') BILL_TO_CONTACT
, (SELECT DECODE(ROL.RESPONSIBILITY_TYPE
, 'DELIVER_TO'
, 'YES'
, 'NO'
, NVL(ROLE_RES.RESPONSIBILITY_TYPE
, 'X'))
FROM HZ_ROLE_RESPONSIBILITY ROL
WHERE ROL.CUST_ACCOUNT_ROLE_ID=ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
AND ROL.RESPONSIBILITY_TYPE='DELIVER_TO') DELIVER_TO_CONTACT
, (SELECT DECODE(NVL(ROL.RESPONSIBILITY_TYPE
, 'X')
, 'X'
, 'ALL'
, 'NONE')
FROM HZ_ROLE_RESPONSIBILITY ROL
, HZ_CUST_ACCOUNT_ROLES ACC_ROL
WHERE ROL.CUST_ACCOUNT_ROLE_ID(+)=ACC_ROL.CUST_ACCOUNT_ROLE_ID
AND ACC_ROL.CUST_ACCOUNT_ROLE_ID=ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
AND NVL(ROL.RESPONSIBILITY_TYPE
, 'X') NOT IN (SELECT LOOKUP_CODE
FROM AR_LOOKUPS AR
WHERE AR.LOOKUP_TYPE = 'SITE_USE_CODE'
AND AR.ENABLED_FLAG = 'Y'
AND TRUNC(SYSDATE) BETWEEN AR.START_DATE_ACTIVE
AND NVL(AR.END_DATE_ACTIVE
, TRUNC (SYSDATE)))) ALL_CONTACTS
, NULL ORG_ID
, ACCT_ROLE.STATUS ROLE_STATUS
FROM HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES CONT_PARTY
, HZ_RELATIONSHIPS REL
, AR_LOOKUPS CONT_ARL
, HZ_PARTIES REL_PARTY
, HZ_CUST_ACCOUNTS CONT_ACCT
, HZ_ROLE_RESPONSIBILITY ROLE_RES
WHERE ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND CUST_ACCT.PARTY_ID=PARTY.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND CONT_ARL.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
AND CONT_ARL.LOOKUP_CODE(+) = CONT_PARTY.PERSON_PRE_NAME_ADJUNCT
AND REL.SUBJECT_ID = CONT_PARTY.PARTY_ID
AND REL.PARTY_ID = REL_PARTY.PARTY_ID
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_ID = CONT_ACCT.PARTY_ID
AND ACCT_ROLE.CUST_ACCOUNT_ID = CONT_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.CUST_ACCOUNT_ID=ACCT_ROLE.CUST_ACCOUNT_ID
AND ROLE_RES.CUST_ACCOUNT_ROLE_ID(+)=ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
AND ACCT_ROLE.CUST_ACCT_SITE_ID IS NULL