eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.WSH_DSNO_ORDERS_SPECIFIC_V

Source

View Text - Preformatted


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 , OEH.CUST_PO_NUMBER PURCHASE_ORDER_NUMBER, OEH.FOB_POINT_CODE FOB_POINT_CODE_INT, OEH.FREIGHT_TERMS_CODE FREIGHT_TERMS_CODE_INT, OEH.TRANSACTIONAL_CURR_CODE TRANSACTIONAL_CURRENCY_CODE 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 rat, hz_cust_site_uses_all hcsu1 WHERE oeh.payment_term_id = rat.term_id (+) 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 , ch.cust_po_number PURCHASE_ORDER_NUMBER, FOB.TERM_VALUE_PK1 FOB_POINT_CODE_INT , FRT.TERM_VALUE_PK1 FREIGHT_TERMS_CODE_INT , WSH_DSNO_OKE.get_oke_CURRENCY_CODE(WDA.DELIVERY_ID,EH.K_HEADER_ID) TRANSACTIONAL_CURRENCY_CODE FROM oke_k_headers eh , okc_k_headers_b ch, oke_k_funding_sources okf, oke_k_terms frt, oke_k_terms fob, 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_v 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 EH.K_HEADER_ID = FOB.k_header_id(+) AND FOB.term_code(+) = 'OB_FOB' AND FOB.K_LINE_ID(+) is null AND EH.K_HEADER_ID = FRT.k_header_id(+) AND FRT.term_code(+) = 'OB_FREIGHT_TERMS' AND FRT.K_LINE_ID(+) is null 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

View Text - HTML Formatted

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
, OEH.CUST_PO_NUMBER PURCHASE_ORDER_NUMBER
, OEH.FOB_POINT_CODE FOB_POINT_CODE_INT
, OEH.FREIGHT_TERMS_CODE FREIGHT_TERMS_CODE_INT
, OEH.TRANSACTIONAL_CURR_CODE TRANSACTIONAL_CURRENCY_CODE
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 RAT
, HZ_CUST_SITE_USES_ALL HCSU1
WHERE OEH.PAYMENT_TERM_ID = RAT.TERM_ID (+)
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
, CH.CUST_PO_NUMBER PURCHASE_ORDER_NUMBER
, FOB.TERM_VALUE_PK1 FOB_POINT_CODE_INT
, FRT.TERM_VALUE_PK1 FREIGHT_TERMS_CODE_INT
, WSH_DSNO_OKE.GET_OKE_CURRENCY_CODE(WDA.DELIVERY_ID
, EH.K_HEADER_ID) TRANSACTIONAL_CURRENCY_CODE
FROM OKE_K_HEADERS EH
, OKC_K_HEADERS_B CH
, OKE_K_FUNDING_SOURCES OKF
, OKE_K_TERMS FRT
, OKE_K_TERMS FOB
, 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_V 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 EH.K_HEADER_ID = FOB.K_HEADER_ID(+)
AND FOB.TERM_CODE(+) = 'OB_FOB'
AND FOB.K_LINE_ID(+) IS NULL
AND EH.K_HEADER_ID = FRT.K_HEADER_ID(+)
AND FRT.TERM_CODE(+) = 'OB_FREIGHT_TERMS'
AND FRT.K_LINE_ID(+) IS NULL
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