View: OE_CUSTOMER_SITE_CONTACTS_V

Product:	ONT - Order Management
Description:
Implementation/DBA Data:	APPS.OE_CUSTOMER_SITE_CONTACTS_V

View Text

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

Columns