View: WSH_DSNO_ORDERS_COMM_V

Product:	WSH - Shipping Execution
Description:
Implementation/DBA Data:	APPS.WSH_DSNO_ORDERS_COMM_V

View Text

SELECT DISTINCT WDA.DELIVERY_ID DELIVERY_ID
, WDA.PARENT_DELIVERY_DETAIL_ID CONTAINER_INSTANCE_ID
, WDD.SOURCE_HEADER_ID ORDER_HEADER_ID
, WDD.SOURCE_CODE SOURCE_CODE
, WDD.CUST_PO_NUMBER PURCHASE_ORDER_NUMBER
, WDD.SOURCE_HEADER_NUMBER SALES_ORDER_NUMBER
, SOLDTO_V.ADDRESS_ID ORDER_BY_ADDRESS_ID
, SOLDTO_V.LOCATION ORDER_BY_CODE_INT
, SOLDTO_V.ECE_TP_LOCATION_CODE ORDER_BY_EDI_LOC_CODE
, ETH.TP_REFERENCE_EXT1 ORDER_BY_TP_REF_1
, ETH.TP_REFERENCE_EXT2 ORDER_BY_TP_REF_2
, SUBSTRB(PARTY1.PARTY_NAME
, 1
, 50) ORDER_BY_NAME
, SOLDTO_V.ADDRESS1 ORDER_BY_ADDRESS1
, SOLDTO_V.ADDRESS2 ORDER_BY_ADDRESS2
, SOLDTO_V.ADDRESS3 ORDER_BY_ADDRESS3
, SOLDTO_V.ADDRESS4 ORDER_BY_ADDRESS4
, SOLDTO_V.CITY ORDER_BY_CITY
, SOLDTO_V.POSTAL_CODE ORDER_BY_POSTAL_CODE
, SOLDTO_V.COUNTRY ORDER_BY_COUNTRY_INT
, SOLDTO_V.STATE ORDER_BY_STATE_INT
, SOLDTO_V.PROVINCE ORDER_BY_PROVINCE_INT
, SOLDTO_V.COUNTY ORDER_BY_COUNTY
, SUBSTRB( REL_PARTY.PERSON_LAST_NAME
, 1
, 50) ORDER_BY_CONTACT_LAST_NAME
, SUBSTRB( REL_PARTY.PERSON_FIRST_NAME
, 1
, 40) ORDER_BY_CONTACT_FIRST_NAME
, ORG_CONT.JOB_TITLE ORDER_BY_CONTACT_JOB_TITLE
, WSH_ECE_VIEWS_DEF.GET_CONT_AREA_CODE(ACCT_ROLE.CUST_ACCOUNT_ROLE_ID) ORDER_BY_AREA_CODE
, WSH_ECE_VIEWS_DEF.GET_CONT_PHONE_NUMBER ORDER_BY_TELEPHONE
, DECODE(WDD.SOURCE_CODE
, 'OKE'
, NULL
, SUBSTRB(PARTY2.PARTY_NAME
, 1
, 50)) INVOICE_NAME
, WDD.CURRENCY_CODE TRANSACTIONAL_CURRENCY_CODE
, WDD.FOB_CODE FOB_POINT_CODE_INT
, WDD.FREIGHT_TERMS_CODE FREIGHT_TERMS_CODE_INT
FROM WSH_DELIVERY_ASSIGNMENTS WDA
, WSH_DELIVERY_DETAILS WDD
, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_PARTIES REL_PARTY
, HZ_PARTIES PARTY1
, HZ_CUST_ACCOUNTS CUST_ACCT1
, ECE_TP_HEADERS ETH
, HZ_PARTIES PARTY2
, HZ_CUST_ACCOUNTS CUST_ACCT2
, ( SELECT ACCT_SITE.ORG_ID
, HCSU.LOCATION
, ACCT_SITE.TP_HEADER_ID
, ACCT_SITE.ECE_TP_LOCATION_CODE
, 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 = HCSU.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = 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
WHERE WDD.CUSTOMER_ID = CUST_ACCT2.CUST_ACCOUNT_ID (+)
AND CUST_ACCT2.PARTY_ID = PARTY2.PARTY_ID(+)
AND WDD.CUSTOMER_ID = SOLDTO_V.CUSTOMER_ID (+)
AND WDD.ORG_ID = SOLDTO_V.ORG_ID (+)
AND WDD.SOLD_TO_CONTACT_ID = ACCT_ROLE.CUST_ACCOUNT_ROLE_ID (+)
AND REL.PARTY_ID (+) = ACCT_ROLE.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE (+) = 'CONTACT'
AND REL.RELATIONSHIP_ID = 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 REL.SUBJECT_ID = REL_PARTY.PARTY_ID (+)
AND SOLDTO_V.TP_HEADER_ID = ETH.TP_HEADER_ID (+)
AND WDD.CUSTOMER_ID = CUST_ACCT1.CUST_ACCOUNT_ID (+)
AND CUST_ACCT1.PARTY_ID = PARTY1.PARTY_ID(+)
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND WDD.CONTAINER_FLAG = 'N'
AND NVL(WDD.SHIPPED_QUANTITY
, 0) > 0
AND WDA.DELIVERY_ID IS NOT NULL ORDER BY PURCHASE_ORDER_NUMBER

Columns