[Home] [Help]
View: WSH_OPSM_ASN_ORDERS_COMM_V
View Text
SELECT DISTINCT WDA.DELIVERY_ID DELIVERY_ID
,
WDD.SOURCE_CODE SOURCE_CODE
,
WDD.SOURCE_HEADER_ID ORDER_HEADER_ID
,
WDD.CUSTOMER_ID SOLDTO_CUSTOMER_ID
,
WDD.SOLD_TO_CONTACT_ID SOLDTO_CONTACT_ID
,
SOLDTO_V.ADDRESS_ID SOLDTO_ADDRESS_ID
,
SOLDTO_V.LOCATION SOLDTO_CODE_INT
,
SUBSTRB(PARTY1.PARTY_NAME
, 1
, 50) SOLDTO_NAME
,
SOLDTO_V.ADDRESS1 SOLDTO_ADDRESS1
,
SOLDTO_V.ADDRESS2 SOLDTO_ADDRESS2
,
SOLDTO_V.ADDRESS3 SOLDTO_ADDRESS3
,
SOLDTO_V.ADDRESS4 SOLDTO_ADDRESS4
,
SOLDTO_V.CITY SOLDTO_CITY
,
SOLDTO_V.POSTAL_CODE SOLDTO_POSTAL_CODE
,
SOLDTO_V.COUNTRY SOLDTO_COUNTRY_INT
,
SOLDTO_V.STATE SOLDTO_STATE_INT
,
SOLDTO_V.PROVINCE SOLDTO_PROVINCE_INT
,
SOLDTO_V.COUNTY SOLDTO_COUNTY
,
REL_PARTY.PERSON_FIRST_NAME||' '||REL_PARTY.PERSON_LAST_NAME SOLDTO_CONT_NAME
,
ORG_CONT.JOB_TITLE SOLDTO_CONT_JOB_TITLE
,
CONTACT1.EMAIL_ADDRESS SOLDTO_CONT_EMAIL
,
CONTACT.PHONE_COUNTRY_CODE SOLDTO_CONT_COUNTRY_CODE
,
CONTACT.PHONE_AREA_CODE SOLDTO_CONT_AREA_CODE
,
CONTACT.PHONE_NUMBER SOLDTO_CONT_PHONE_NUMBER
,
CONTACT.PHONE_EXTENSION SOLDTO_CONT_PHONE_EXTN
,
SOLD_BY.LOCATION_CODE SOLDBY_LOCATION_CODE
,
WSHL.COUNTRY SOLDBY_COUNTRY
,
WSHL.ADDRESS1 SOLDBY_ADDRESS_LINE_1
,
WSHL.ADDRESS2 SOLDBY_ADDRESS_LINE_2
,
WSHL.ADDRESS3 SOLDBY_ADDRESS_LINE_3
,
WSHL.ADDRESS4 SOLDBY_ADDRESS_LINE_4
,
WSHL.COUNTY SOLDBY_COUNTY
,
WSHL.STATE SOLDBY_STATE
,
WSHL.POSTAL_CODE SOLDBY_POSTAL_CODE
,
WSHL.CITY SOLDBY_CITY
,
SOLD_BY.ORGANIZATION_ID SOLDBY_ORGANIZATION_ID
,
SOLD_BY.NAME SOLDBY_NAME
,
PAPF.FULL_NAME SOLDBY_CONTACT_NAME
,
PAPF.EMAIL_ADDRESS SOLDBY_CONTACT_EMAILID
,
(SELECT PP.PHONE_NUMBER
FROM PER_PHONES PP
WHERE PAPF.PERSON_ID = PP.PARENT_ID
AND PP.PARENT_TABLE = 'PER_ALL_PEOPLE_F'
AND PP.PHONE_TYPE = 'W1') SOLDBY_CONTACT_PHONENUMBER
FROM WSH_DELIVERY_ASSIGNMENTS WDA
,
WSH_DELIVERY_DETAILS WDD
,
HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
,
HZ_RELATIONSHIPS REL
,
HZ_ORG_CONTACTS ORG_CONT
,
HZ_CONTACT_POINTS CONTACT
,
HZ_CONTACT_POINTS CONTACT1
,
HZ_PARTIES REL_PARTY
,
HZ_PARTIES PARTY1
,
HZ_CUST_ACCOUNTS CUST_ACCT1
,
(SELECT ACCT_SITE.ORG_ID
,
HCSU.LOCATION
,
ACCT_SITE.CUST_ACCT_SITE_ID ADDRESS_ID
,
ACCT_SITE.CUST_ACCOUNT_ID CUSTOMER_ID
,
LOC.ADDRESS1
,
LOC.ADDRESS2
,
LOC.ADDRESS3
,
LOC.ADDRESS4
,
LOC.CITY
,
LOC.POSTAL_CODE
,
LOC.COUNTRY
,
LOC.STATE
,
LOC.PROVINCE
,
LOC.COUNTY
FROM HZ_PARTY_SITES PARTY_SITE
,
HZ_LOCATIONS LOC
,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
,
HZ_CUST_SITE_USES_ALL HCSU
WHERE ACCT_SITE.CUST_ACCT_SITE_ID+0 = HCSU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID+0 = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND HCSU.SITE_USE_CODE = 'SOLD_TO'
AND HCSU.PRIMARY_FLAG = 'Y'
AND NVL(ACCT_SITE.ORG_ID
, -999 ) = NVL(HCSU.ORG_ID
, -999)
) SOLDTO_V
,
HR_ORGANIZATION_UNITS_V SOLD_BY
,
HR_LOCATIONS_ALL HRL
,
WSH_LOCATIONS WSHL
,
PER_ALL_PEOPLE_F PAPF
WHERE WDD.CUSTOMER_ID+0 = SOLDTO_V.CUSTOMER_ID(+)
AND WDD.ORG_ID = SOLDTO_V.ORG_ID(+)
AND WDD.SOLD_TO_CONTACT_ID+0 = ACCT_ROLE.CUST_ACCOUNT_ROLE_ID(+)
AND REL.PARTY_ID (+) = ACCT_ROLE.PARTY_ID+0
AND ACCT_ROLE.ROLE_TYPE (+) = 'CONTACT'
AND REL.RELATIONSHIP_ID+0 = ORG_CONT.PARTY_RELATIONSHIP_ID(+)
AND REL.SUBJECT_TABLE_NAME (+) = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME (+) = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG (+) = 'F'
AND ACCT_ROLE.PARTY_ID +0 = CONTACT.OWNER_TABLE_ID(+)
AND CONTACT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND CONTACT.CONTACT_POINT_TYPE(+) = 'PHONE'
AND CONTACT.PRIMARY_FLAG(+) = 'Y'
AND CONTACT.STATUS(+) = 'A'
AND ACCT_ROLE.PARTY_ID +0 = CONTACT1.OWNER_TABLE_ID(+)
AND CONTACT1.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND CONTACT1.CONTACT_POINT_TYPE(+)= 'EMAIL'
AND CONTACT1.PRIMARY_FLAG(+) = 'Y'
AND CONTACT1.STATUS(+) = 'A'
AND REL.SUBJECT_ID+0 = REL_PARTY.PARTY_ID (+)
AND WDD.CUSTOMER_ID+0 = CUST_ACCT1.CUST_ACCOUNT_ID(+)
AND CUST_ACCT1.PARTY_ID+0 = PARTY1.PARTY_ID(+)
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID +0
AND WDD.ORG_ID+0 = SOLD_BY.ORGANIZATION_ID
AND SOLD_BY.LOCATION_ID+0 = HRL.LOCATION_ID
AND HRL.DESIGNATED_RECEIVER_ID+0 = PAPF.PERSON_ID(+)
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE (+)
AND PAPF.EFFECTIVE_END_DATE (+)
AND WSHL.SOURCE_LOCATION_ID = HRL.LOCATION_ID+0
AND WSHL.LOCATION_SOURCE_CODE = 'HR'
AND WDD.CONTAINER_FLAG = 'N'
AND NVL(WDD.SHIPPED_QUANTITY
, 0) > 0
AND WDA.DELIVERY_ID IS NOT NULL
APPS.WSH_OPSM_ASN_ORDERS_COMM_V