View: WSH_DSNO_ORDERS_SPECIFIC_V

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

View Text

SELECT 'OE' SOURCE_CODE
, OEH.HEADER_ID SOURCE_HEADER_ID
, OEH.ORDERED_DATE PURCHASE_ORDER_DATE
, OEH.ORDERED_DATE SALES_ORDER_DATE
, TO_CHAR(OEH.ORDER_NUMBER) SALES_ORDER_NUMBER
, OEA.AGREEMENT_NUM AGREEMENT_NUMBER_INT
, OET.NAME ORDER_TYPE_INT
, OEH.ORDER_CATEGORY_CODE ORDER_CATEGORY_CODE
, OEH.REQUEST_DATE CUSTOMER_REQUEST_DATE
, OEH.ORDER_DATE_TYPE_CODE ORDER_DATE_TYPE_CODE
, OEH.PARTIAL_SHIPMENTS_ALLOWED PARTIAL_SHIPMENTS_ALLOWED
, OEH.PAYMENT_TYPE_CODE FOB_PAYMENT_METHOD_CODE_INT
, ACCT_SITE.CUST_ACCT_SITE_ID INVOICE_ADDRESS_ID
, HCSU1.LOCATION INVOICE_CODE_INT
, ACCT_SITE.ECE_TP_LOCATION_CODE INVOICE_EDI_LOC_CODE
, ETH1.TP_REFERENCE_EXT1 INVOICE_TP_REF_1
, ETH1.TP_REFERENCE_EXT2 INVOICE_TP_REF_2
, LOC.ADDRESS1 INVOICE_ADDRESS1
, LOC.ADDRESS2 INVOICE_ADDRESS2
, LOC.ADDRESS3 INVOICE_ADDRESS3
, LOC.ADDRESS4 INVOICE_ADDRESS4
, LOC.CITY INVOICE_CITY
, LOC.POSTAL_CODE INVOICE_POSTAL_CODE
, LOC.COUNTRY INVOICE_COUNTRY_INT
, LOC.STATE INVOICE_STATE_INT
, LOC.PROVINCE INVOICE_PROVINCE_INT
, LOC.COUNTY INVOICE_COUNTY
, SUBSTRB( REL_PARTY.PERSON_LAST_NAME
, 1
, 50) INVOICE_CONTACT_LAST_NAME
, SUBSTRB( REL_PARTY.PERSON_FIRST_NAME
, 1
, 40) INVOICE_CONTACT_FIRST_NAME
, ORG_CONT.JOB_TITLE INVOICE_CONTACT_JOB_TITLE
, WSH_ECE_VIEWS_DEF.GET_CONT_AREA_CODE(ACCT_ROLE.CUST_ACCOUNT_ROLE_ID) INVOICE_AREA_CODE
, WSH_ECE_VIEWS_DEF.GET_CONT_PHONE_NUMBER INVOICE_TELEPHONE
, OEH.TAX_EXEMPT_FLAG TAX_EXEMPT_FLAG
, OEH.TAX_EXEMPT_NUMBER TAX_EXEMPT_NUMBER
, OEH.TAX_EXEMPT_REASON_CODE TAX_EXEMPT_REASON_CODE_INT
, OEH.CONVERSION_TYPE_CODE CURRENCY_CONVERSION_TYPE_CODE
, OEH.CONVERSION_RATE_DATE CURRENCY_CONVERSION_DATE
, OEH.CONVERSION_RATE CURRENCY_CONVERSION_RATE
, 0 DELIVERY_ID
, RAT.NAME PAYMENT_TERMS_INT
, OEH.PAYMENT_AMOUNT PAYMENT_AMOUNT
, OEH.CONTEXT ORDER_HEADER_CONTEXT
, OEH.ATTRIBUTE1 ORDER_HEADER_ATTRIBUTE1
, OEH.ATTRIBUTE2 ORDER_HEADER_ATTRIBUTE2
, OEH.ATTRIBUTE3 ORDER_HEADER_ATTRIBUTE3
, OEH.ATTRIBUTE4 ORDER_HEADER_ATTRIBUTE4
, OEH.ATTRIBUTE5 ORDER_HEADER_ATTRIBUTE5
, OEH.ATTRIBUTE6 ORDER_HEADER_ATTRIBUTE6
, OEH.ATTRIBUTE7 ORDER_HEADER_ATTRIBUTE7
, OEH.ATTRIBUTE8 ORDER_HEADER_ATTRIBUTE8
, OEH.ATTRIBUTE9 ORDER_HEADER_ATTRIBUTE9
, OEH.ATTRIBUTE10 ORDER_HEADER_ATTRIBUTE10
, OEH.ATTRIBUTE11 ORDER_HEADER_ATTRIBUTE11
, OEH.ATTRIBUTE12 ORDER_HEADER_ATTRIBUTE12
, OEH.ATTRIBUTE13 ORDER_HEADER_ATTRIBUTE13
, OEH.ATTRIBUTE14 ORDER_HEADER_ATTRIBUTE14
, OEH.ATTRIBUTE15 ORDER_HEADER_ATTRIBUTE15
, OEH.GLOBAL_ATTRIBUTE_CATEGORY OH_GLOBAL_CATEGORY
, OEH.GLOBAL_ATTRIBUTE1 OH_GLOBAL_ATTRIBUTE1
, OEH.GLOBAL_ATTRIBUTE2 OH_GLOBAL_ATTRIBUTE2
, OEH.GLOBAL_ATTRIBUTE3 OH_GLOBAL_ATTRIBUTE3
, OEH.GLOBAL_ATTRIBUTE4 OH_GLOBAL_ATTRIBUTE4
, OEH.GLOBAL_ATTRIBUTE5 OH_GLOBAL_ATTRIBUTE5
, OEH.GLOBAL_ATTRIBUTE6 OH_GLOBAL_ATTRIBUTE6
, OEH.GLOBAL_ATTRIBUTE7 OH_GLOBAL_ATTRIBUTE7
, OEH.GLOBAL_ATTRIBUTE8 OH_GLOBAL_ATTRIBUTE8
, OEH.GLOBAL_ATTRIBUTE9 OH_GLOBAL_ATTRIBUTE9
, OEH.GLOBAL_ATTRIBUTE10 OH_GLOBAL_ATTRIBUTE10
, OEH.GLOBAL_ATTRIBUTE11 OH_GLOBAL_ATTRIBUTE11
, OEH.GLOBAL_ATTRIBUTE12 OH_GLOBAL_ATTRIBUTE12
, OEH.GLOBAL_ATTRIBUTE13 OH_GLOBAL_ATTRIBUTE13
, OEH.GLOBAL_ATTRIBUTE14 OH_GLOBAL_ATTRIBUTE14
, OEH.GLOBAL_ATTRIBUTE15 OH_GLOBAL_ATTRIBUTE15
, OEH.GLOBAL_ATTRIBUTE16 OH_GLOBAL_ATTRIBUTE16
, OEH.GLOBAL_ATTRIBUTE17 OH_GLOBAL_ATTRIBUTE17
, OEH.GLOBAL_ATTRIBUTE18 OH_GLOBAL_ATTRIBUTE18
, OEH.GLOBAL_ATTRIBUTE19 OH_GLOBAL_ATTRIBUTE19
, OEH.GLOBAL_ATTRIBUTE20 OH_GLOBAL_ATTRIBUTE20
, OEH.TP_CONTEXT ORDER_HEADER_TP_CONTEXT
, OEH.TP_ATTRIBUTE1 ORDER_HEADER_TP_ATTRIBUTE1
, OEH.TP_ATTRIBUTE2 ORDER_HEADER_TP_ATTRIBUTE2
, OEH.TP_ATTRIBUTE3 ORDER_HEADER_TP_ATTRIBUTE3
, OEH.TP_ATTRIBUTE4 ORDER_HEADER_TP_ATTRIBUTE4
, OEH.TP_ATTRIBUTE5 ORDER_HEADER_TP_ATTRIBUTE5
, OEH.TP_ATTRIBUTE6 ORDER_HEADER_TP_ATTRIBUTE6
, OEH.TP_ATTRIBUTE7 ORDER_HEADER_TP_ATTRIBUTE7
, OEH.TP_ATTRIBUTE8 ORDER_HEADER_TP_ATTRIBUTE8
, OEH.TP_ATTRIBUTE9 ORDER_HEADER_TP_ATTRIBUTE9
, OEH.TP_ATTRIBUTE10 ORDER_HEADER_TP_ATTRIBUTE10
, OEH.TP_ATTRIBUTE11 ORDER_HEADER_TP_ATTRIBUTE11
, OEH.TP_ATTRIBUTE12 ORDER_HEADER_TP_ATTRIBUTE12
, OEH.TP_ATTRIBUTE13 ORDER_HEADER_TP_ATTRIBUTE13
, OEH.TP_ATTRIBUTE14 ORDER_HEADER_TP_ATTRIBUTE14
, OEH.TP_ATTRIBUTE15 ORDER_HEADER_TP_ATTRIBUTE15
FROM OE_ORDER_HEADERS_ALL OEH
, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_PARTIES REL_PARTY
, HZ_PARTY_SITES PARTY_SITE
, HZ_LOCATIONS LOC
, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
, ECE_TP_HEADERS ETH1
, OE_TRANSACTION_TYPES_TL OET
, OE_AGREEMENTS_B OEA
, RA_TERMS_TL RAT
, HZ_CUST_SITE_USES_ALL HCSU1
WHERE OEH.PAYMENT_TERM_ID = RAT.TERM_ID (+)
AND RAT.LANGUAGE = USERENV ( 'LANG' )
AND OEH.INVOICE_TO_ORG_ID = HCSU1.SITE_USE_ID (+)
AND OEH.AGREEMENT_ID = OEA.AGREEMENT_ID (+)
AND OEH.ORDER_TYPE_ID = OET.TRANSACTION_TYPE_ID
AND OET.LANGUAGE = USERENV('LANG')
AND HCSU1.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 HCSU1.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID(+)
AND ACCT_SITE.TP_HEADER_ID = ETH1.TP_HEADER_ID (+)
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID(+)
AND LOC.LOCATION_ID(+) = PARTY_SITE.LOCATION_ID UNION ALL SELECT 'OKE' SOURCE_CODE
, EH.K_HEADER_ID SOURCE_HEADER_ID
, CH.DATE_APPROVED PURCHASE_ORDER_DATE
, EH.AWARD_DATE SALES_ORDER_DATE
, CH.CONTRACT_NUMBER SALES_ORDER_NUMBER
, NULL AGREEMENT_NUMBER_INT
, EH.K_TYPE_CODE ORDER_TYPE_INT
, 'PROJECT' ORDER_CATEGORY_CODE
, EH.DATE_RECEIVED CUSTOMER_REQUEST_DATE
, NULL ORDER_DATE_TYPE_CODE
, NULL PARTIAL_SHIPMENTS_ALLOWED
, NULL FOB_PAYMENT_METHOD_CODE_INT
, ACCT_SITE.CUST_ACCT_SITE_ID INVOICE_ADDRESS_ID
, HCSU1.LOCATION INVOICE_CODE_INT
, ACCT_SITE.ECE_TP_LOCATION_CODE INVOICE_EDI_LOC_CODE
, ETH1.TP_REFERENCE_EXT1 INVOICE_TP_REF_1
, ETH1.TP_REFERENCE_EXT2 INVOICE_TP_REF_2
, LOC.ADDRESS1 INVOICE_ADDRESS1
, LOC.ADDRESS2 INVOICE_ADDRESS2
, LOC.ADDRESS3 INVOICE_ADDRESS3
, LOC.ADDRESS4 INVOICE_ADDRESS4
, LOC.CITY INVOICE_CITY
, LOC.POSTAL_CODE INVOICE_POSTAL_CODE
, LOC.COUNTRY INVOICE_COUNTRY_INT
, LOC.STATE INVOICE_STATE_INT
, LOC.PROVINCE INVOICE_PROVINCE_INT
, LOC.COUNTY INVOICE_COUNTY
, SUBSTRB( REL_PARTY.PERSON_LAST_NAME
, 1
, 50) INVOICE_CONTACT_LAST_NAME
, SUBSTRB( REL_PARTY.PERSON_FIRST_NAME
, 1
, 40) INVOICE_CONTACT_FIRST_NAME
, ORG_CONT.JOB_TITLE INVOICE_CONTACT_JOB_TITLE
, WSH_ECE_VIEWS_DEF.GET_CONT_AREA_CODE(ACCT_ROLE.CUST_ACCOUNT_ROLE_ID) INVOICE_AREA_CODE
, WSH_ECE_VIEWS_DEF.GET_CONT_PHONE_NUMBER INVOICE_TELEPHONE
, NULL TAX_EXEMPT_FLAG
, NULL TAX_EXEMPT_NUMBER
, NULL TAX_EXEMPT_REASON_CODE_INT
, OKF.K_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE_CODE
, OKF.K_CONVERSION_DATE CURRENCY_CONVERSION_DATE
, OKF.K_CONVERSION_RATE CURRENCY_CONVERSION_RATE
, WDA.DELIVERY_ID DELIVERY_ID
, WSH_DSNO_OKE.GET_OKE_TERM_VALUE(WDA.DELIVERY_ID
, EH.K_HEADER_ID) PAYMENT_TERMS_INT
, 0 PAYMENT_AMOUNT
, CH.ATTRIBUTE_CATEGORY ORDER_HEADER_CONTEXT
, CH.ATTRIBUTE1 ORDER_HEADER_ATTRIBUTE1
, CH.ATTRIBUTE2 ORDER_HEADER_ATTRIBUTE2
, CH.ATTRIBUTE3 ORDER_HEADER_ATTRIBUTE3
, CH.ATTRIBUTE4 ORDER_HEADER_ATTRIBUTE4
, CH.ATTRIBUTE5 ORDER_HEADER_ATTRIBUTE5
, CH.ATTRIBUTE6 ORDER_HEADER_ATTRIBUTE6
, CH.ATTRIBUTE7 ORDER_HEADER_ATTRIBUTE7
, CH.ATTRIBUTE8 ORDER_HEADER_ATTRIBUTE8
, CH.ATTRIBUTE9 ORDER_HEADER_ATTRIBUTE9
, CH.ATTRIBUTE10 ORDER_HEADER_ATTRIBUTE10
, CH.ATTRIBUTE11 ORDER_HEADER_ATTRIBUTE11
, CH.ATTRIBUTE12 ORDER_HEADER_ATTRIBUTE12
, CH.ATTRIBUTE13 ORDER_HEADER_ATTRIBUTE13
, CH.ATTRIBUTE14 ORDER_HEADER_ATTRIBUTE14
, CH.ATTRIBUTE15 ORDER_HEADER_ATTRIBUTE15
, NULL OH_GLOBAL_CATEGORY
, NULL OH_GLOBAL_ATTRIBUTE1
, NULL OH_GLOBAL_ATTRIBUTE2
, NULL OH_GLOBAL_ATTRIBUTE3
, NULL OH_GLOBAL_ATTRIBUTE4
, NULL OH_GLOBAL_ATTRIBUTE5
, NULL OH_GLOBAL_ATTRIBUTE6
, NULL OH_GLOBAL_ATTRIBUTE7
, NULL OH_GLOBAL_ATTRIBUTE8
, NULL OH_GLOBAL_ATTRIBUTE9
, NULL OH_GLOBAL_ATTRIBUTE10
, NULL OH_GLOBAL_ATTRIBUTE11
, NULL OH_GLOBAL_ATTRIBUTE12
, NULL OH_GLOBAL_ATTRIBUTE13
, NULL OH_GLOBAL_ATTRIBUTE14
, NULL OH_GLOBAL_ATTRIBUTE15
, NULL OH_GLOBAL_ATTRIBUTE16
, NULL OH_GLOBAL_ATTRIBUTE17
, NULL OH_GLOBAL_ATTRIBUTE18
, NULL OH_GLOBAL_ATTRIBUTE19
, NULL OH_GLOBAL_ATTRIBUTE20
, NULL ORDER_HEADER_TP_CONTEXT
, NULL ORDER_HEADER_TP_ATTRIBUTE1
, NULL ORDER_HEADER_TP_ATTRIBUTE2
, NULL ORDER_HEADER_TP_ATTRIBUTE3
, NULL ORDER_HEADER_TP_ATTRIBUTE4
, NULL ORDER_HEADER_TP_ATTRIBUTE5
, NULL ORDER_HEADER_TP_ATTRIBUTE6
, NULL ORDER_HEADER_TP_ATTRIBUTE7
, NULL ORDER_HEADER_TP_ATTRIBUTE8
, NULL ORDER_HEADER_TP_ATTRIBUTE9
, NULL ORDER_HEADER_TP_ATTRIBUTE10
, NULL ORDER_HEADER_TP_ATTRIBUTE11
, NULL ORDER_HEADER_TP_ATTRIBUTE12
, NULL ORDER_HEADER_TP_ATTRIBUTE13
, NULL ORDER_HEADER_TP_ATTRIBUTE14
, NULL ORDER_HEADER_TP_ATTRIBUTE15
FROM OKE_K_HEADERS EH
, OKC_K_HEADERS_B CH
, OKE_K_FUNDING_SOURCES OKF
, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS ORG_CONT
, HZ_PARTIES REL_PARTY
, HZ_PARTY_SITES PARTY_SITE
, HZ_LOCATIONS LOC
, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
, ECE_TP_HEADERS ETH1
, HZ_CUST_SITE_USES_ALL HCSU1
, WSH_DELIVERY_DETAILS WDD
, WSH_DELIVERY_ASSIGNMENTS WDA
WHERE EH.K_HEADER_ID = CH.ID
AND EH.K_HEADER_ID = OKF.OBJECT_ID (+)
AND EH.K_HEADER_ID = WDD.SOURCE_HEADER_ID
AND WDA.DELIVERY_DETAIL_ID = WDD.DELIVERY_DETAIL_ID
AND WDD.SOURCE_CODE = 'OKE'
AND WDD.CONTAINER_FLAG = 'N'
AND NVL(WDD.SHIPPED_QUANTITY
, 0) > 0
AND WDA.DELIVERY_ID IS NOT NULL
AND WSH_DSNO_OKE.GET_OKE_PARTY(WDD.DELIVERY_DETAIL_ID
, WDD.SOURCE_HEADER_ID) = HCSU1.SITE_USE_ID (+)
AND HCSU1.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 HCSU1.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID(+)
AND ACCT_SITE.TP_HEADER_ID = ETH1.TP_HEADER_ID (+)
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID(+)
AND LOC.LOCATION_ID(+) = PARTY_SITE.LOCATION_ID

Columns