View: OE_CUST_SITE_PHONE_V

Product:	ONT - Order Management
Description:
Implementation/DBA Data:	APPS.OE_CUST_SITE_PHONE_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
, NULL FIRST_NAME
, NULL LAST_NAME
, NULL CONTACT_EMAIL
, NULL DUMMY
, NULL ROLE_TYPE
, NULL ROLE_TYPE_CODE
, NULL CONTACT_NAME
, -1 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
, NULL SHIP_TO_CONTACT
, NULL BILL_TO_CONTACT
, NULL ALL_CONTACTS
, CONT_POINT.PHONE_AREA_CODE AREA_CODE
, CONT_POINT.PHONE_NUMBER
, CONT_POINT.PHONE_EXTENSION
, CONT_POINT.TRANSPOSED_PHONE_NUMBER
, CONT_POINT.CONTACT_POINT_ID
, 'CUSTOMER' PHONE_NUMBER_OF
, (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
, NULL DELIVER_TO_CONTACT
, ACCT_SITE.ORG_ID ORG_ID
, CONT_POINT.STATUS PHONE_STATUS
FROM HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
, HZ_LOCATIONS LOC
, HZ_CUST_SITE_USES_ALL SITE
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_CONTACT_POINTS CONT_POINT
WHERE CUST_ACCT.PARTY_ID=PARTY.PARTY_ID
AND PARTY.PARTY_ID =CONT_POINT.OWNER_TABLE_ID
AND NVL(CONT_POINT.OWNER_TABLE_NAME
, 'HZ_PARTIES') = 'HZ_PARTIES'
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 ACCT_SITE.CUST_ACCOUNT_ID=CUST_ACCT.CUST_ACCOUNT_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_ALL 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 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
, NULL FIRST_NAME
, NULL LAST_NAME
, NULL CONTACT_EMAIL
, NULL DUMMY
, NULL ROLE_TYPE
, NULL ROLE_TYPE_CODE
, NULL CONTACT_NAME
, -1 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
, NULL SHIP_TO_CONTACT
, NULL BILL_TO_CONTACT
, NULL ALL_CONTACTS
, CONT_POINT.PHONE_AREA_CODE AREA_CODE
, CONT_POINT.PHONE_NUMBER
, CONT_POINT.PHONE_EXTENSION
, CONT_POINT.TRANSPOSED_PHONE_NUMBER
, CONT_POINT.CONTACT_POINT_ID
, 'ADDRESS' PHONE_NUMBER_OF
, (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
, NULL DELIVER_TO_CONTACT
, ACCT_SITE.ORG_ID ORG_ID
, CONT_POINT.STATUS PHONE_STATUS
FROM HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
, HZ_LOCATIONS LOC
, HZ_CUST_SITE_USES_ALL SITE
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_CONTACT_POINTS CONT_POINT
WHERE 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 ACCT_SITE.CUST_ACCOUNT_ID=CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID=PARTY.PARTY_ID
AND SITE.ORG_ID = ACCT_SITE.ORG_ID
AND PARTY_SITE.PARTY_SITE_ID =CONT_POINT.OWNER_TABLE_ID
AND NVL(CONT_POINT.OWNER_TABLE_NAME
, 'HZ_PARTY_SITES') = 'HZ_PARTY_SITES'
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_ALL 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 )

Columns