VIEW: APPS.OE_CUST_SITE_PHONE_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, 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 )

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
, 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 )