VIEW: APPS.WSH_DSNO_ORDERS_COMM_V

Source

View Text - Preformatted

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, NVL(WDD.REFERENCE_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 FROM WSH_DELIVERY_ASSIGNMENTS_V 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

View Text - HTML Formatted

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
, NVL(WDD.REFERENCE_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
FROM WSH_DELIVERY_ASSIGNMENTS_V 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