-- -----------------------------------------------------------------------------------
-- Processing Rule - Address Level
-- -----------------------------------------------------------------------------------
SELECT distinct
hcasa.ece_tp_location_code "Ship-to Location"
,hp.party_name
,hca.account_number
, hl.city
, hl.postal_code
, hl.province
, hl.county
, hl.address1 || hl.address2 || hl.address3 || hl.address4
address
, mci.customer_item_number
, mci.customer_item_desc
, mif.item_number
, mif.description
, mcc.commodity_code
, terms.customer_id
-----------DEMAND FENCES---------------------------------------------
, terms.pln_frozen_day_from
, terms.pln_frozen_day_to
, terms.pln_firm_day_from
, terms.pln_firm_day_to
, terms.pln_forecast_day_from
, terms.pln_forecast_day_to
, terms.pln_mrp_forecast_day_from
, terms.pln_mrp_forecast_day_to
, terms.shp_frozen_day_from
, terms.shp_frozen_day_to
, terms.shp_firm_day_from
, terms.shp_firm_day_to
, terms.shp_forecast_day_from
, terms.shp_forecast_day_to
, terms.shp_mrp_forecast_day_from
, terms.shp_mrp_forecast_day_to
, terms.seq_frozen_day_from
, terms.seq_frozen_day_to
, terms.seq_firm_day_from
, terms.seq_firm_day_to
, terms.seq_forecast_day_from
, terms.seq_forecast_day_to
, terms.seq_mrp_forecast_day_from
, terms.seq_mrp_forecast_day_to
----------------DEMAND mANAGEMENT--------------------------------------------
-- , terms.schedule_hierarchy_code "Consume Demand Hierarchy Code"
--,(SELECT meaning from fnd_lookups where lookup_code like terms.schedule_hierarchy_code) "Consume Demand Hierarchy"
-- , terms.unshipped_firm_disp_cd "ATS PreHorizon Disp Code"
-- ,(SELECT meaning from fnd_lookups where lookup_code like terms.unshipped_firm_disp_cd) "ATS PreHorizon Disposition"
-- , terms.unship_firm_cutoff_days "ATS Horizon Cutoff Days"
, terms.use_edi_sdp_code_flag "Use Cust Ship Delivery"
, terms.ship_delivery_rule_name "Ship Delivery Code"
, decode(terms.ship_delivery_rule_name,'D','Monday','E','Tuesday','F','Wednesday','G','Thursday',
'H','Friday','J','Saturday','K','Sunday','N','As Directed',
'O','Daily Monday through Friday','P','1/2 Monday and 1/2 Thursday',
'T','1/2 Tuesday and 1/2 Friday','Y','None','R','1/2 Wednwsday and 1/2 Friday',
'SZ','Tuesday,Thursday and Friday','13','Monday','14','Tuesday','15','Wednesday',
'16','Thursday','17','Friday','18','Saturday','19','Sunday','20','Immediately',
'21','As Directed','23','Daily Monday through Friday','ZZZ','Mutually defined') "Default Ship Delivery Pattern"
, terms.demand_tolerance_above "Demand Tolerance Positive"
, terms.demand_tolerance_below "Demand Tolerance Negative"
, terms.round_to_std_pack_flag "Std Pack Round to"
, terms.std_pack_qty "STD Pack Qty"
-------------------Order Management------------------------------------
, ooh.order_number "Order Number"
--, ott.NAME "Sales Order Type"
, terms.intransit_time "Intransit Time"
, terms.time_uom_code "Intransit UOM Code"
, decode (terms.time_uom_code,'DAY','Day','HR','Hour') "Intransit Unit of Measure"
, terms.exclude_non_workdays_flag "Exclude Non-Workdays"
---------------------CUM MANAGEMENT------------------------------------
--, terms.cum_control_code
-- ,(select meaning from fnd_lookups where lookup_code like terms.cum_control_code)"CUM Management Type"
-- , terms.cum_org_level_code "CUM Org Level"
-- , terms.cum_shipment_rule_code "Shipment Rule Code"
-- , terms.cust_shipto_terms_id
---------------------GENERAL------------------------------------------
--, terms.cust_assign_supplier_cd "Assigned Supplier Code"
, terms.address_id
, terms.customer_id
, terms.ship_from_org_id
, terms.ship_method
, terms.intransit_time
, terms.time_uom_code
--, terms.cum_current_record_year
--, terms.cum_previous_record_year
, terms.cum_current_start_date
, terms.cum_previous_start_date
-- , terms.cum_yesterd_time_cutoff
--, terms.cust_assign_supplier_cd
-- , terms.customer_rcv_calendar_cd
, terms.freight_code
--, terms.supplier_shp_calendar_cd
--, terms.unship_firm_cutoff_days
, terms.use_edi_sdp_code_flag
, terms.inactive_date
-- , terms.match_across_key
--, terms.match_within_key
, terms.header_id
, terms.price_list_id
--, terms.critical_attribute_key
, terms.customer_contact_id
, terms.supplier_contact_id
, terms.agreement_id
, terms.agreement_name
, terms.future_agreement_id
, terms.future_agreement_name
, terms.comments
, terms.last_updated_by
, terms.last_update_date
, terms.creation_date
, terms.created_by
, terms.attribute_category
, terms.attribute1
, terms.attribute2
, terms.attribute3
, terms.attribute4
, terms.attribute5
, terms.attribute6
, terms.attribute7
, terms.attribute8
, terms.attribute9
, terms.attribute10
, terms.attribute11
, terms.attribute12
, terms.attribute13
, terms.attribute14
, terms.attribute15
, terms.last_update_login
, terms.request_id
, terms.program_application_id
, terms.program_id
, terms.program_update_date
, terms.tp_attribute1
, terms.tp_attribute2
, terms.tp_attribute3
, terms.tp_attribute4
, terms.tp_attribute5
, terms.tp_attribute6
, terms.tp_attribute7
, terms.tp_attribute8
, terms.tp_attribute9
, terms.tp_attribute10
, terms.tp_attribute11
, terms.tp_attribute12
, terms.tp_attribute13
, terms.tp_attribute14
, terms.tp_attribute15
, terms.tp_attribute_category
--, terms.intransit_calc_basis
--,(select meaning from fnd_lookups where lookup_code=terms.intransit_calc_basis)"intransit calc basis"
, terms.default_ship_from
, terms.pln_frozen_flag
, terms.shp_frozen_flag
, terms.seq_frozen_flag
, terms.issue_warning_drop_parts_flag
, terms.org_id
, terms.blanket_number
, terms.release_rule
, terms.release_time_frame
, terms.release_time_frame_uom
, terms.exclude_non_workdays_flag
, terms.disable_create_cum_key_flag
FROM apps.mtl_customer_items mci
, apps.mtl_commodity_codes mcc
, apps.XXAR4385_RA_CUST_R12_V racust
, apps.XXAR4385_RA_ADDRESS_R12_V raadd
, apps.rlm_cust_shipto_terms_all terms,
APPS.HZ_PARTIES HP,
APPS.HZ_PARTY_SITES HPS,
HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_LOCATIONS HL,
HZ_CUST_ACCOUNTS HCA
, apps.mtl_item_flexfields mif
, apps.mtl_customer_item_xrefs mcix
, apps.oe_order_headers_All ooh
--, oe_transaction_types_tl ott
WHERE terms.address_id = mci.address_id
AND
terms.address_id IS NOT NULL
AND hp.party_id=hps.party_id
and hps.party_site_id=hcasa.party_site_id
and hps.location_id=hl.location_id
AND hca.party_id=hp.party_id
AND hca.cust_account_id = terms.customer_id
AND hcasa.cust_acct_site_id = terms.address_id
AND mci.commodity_code_id = mcc.commodity_code_id
AND mif.inventory_item_id = mcix.inventory_item_id
AND mif.organization_id = mcix.master_organization_id
AND mcix.customer_item_id = mci.customer_item_id
and terms.ship_from_org_id in (select organization_id from org_organization_definitions where organization_code in ('JES','MHF') )
AND mcix.preference_number =
(SELECT MIN (preference_number)
FROM mtl_customer_item_xrefs
WHERE customer_item_id = mci.customer_item_id
AND inactive_flag <> 'Y')
and ooh.header_id=terms.header_id
-- and hca.account_number=:customer_number
and hcasa.ece_tp_location_code is not null;
-- -----------------------------------------------------------------------------------
-- Processing Rule - Customer level
-- -----------------------------------------------------------------------------------
SELECT (select organization_code from org_organization_definitions where organization_id=terms.ship_from_org_id)"Organization code"
, racust.account_number
, hp.party_name
, terms.pln_frozen_day_from
, terms.pln_frozen_day_to
, terms.pln_firm_day_from
, terms.pln_firm_day_to
, terms.pln_forecast_day_from
, terms.pln_forecast_day_to
, terms.pln_mrp_forecast_day_from
, terms.pln_mrp_forecast_day_to
, terms.shp_frozen_day_from
, terms.shp_frozen_day_to
, terms.shp_firm_day_from
, terms.shp_firm_day_to
, terms.shp_forecast_day_from
, terms.shp_forecast_day_to
, terms.shp_mrp_forecast_day_from
, terms.shp_mrp_forecast_day_to
, terms.seq_frozen_day_from
, terms.seq_frozen_day_to
, terms.seq_firm_day_from
, terms.seq_firm_day_to
, terms.seq_forecast_day_from
, terms.seq_forecast_day_to
, terms.seq_mrp_forecast_day_from
, terms.seq_mrp_forecast_day_to
,(SELECT meaning from fnd_lookups where lookup_code like terms.schedule_hierarchy_code) "Consume Demand Hierarchy"
,(SELECT meaning from fnd_lookups where lookup_code like terms.unshipped_firm_disp_cd) "ATS PreHorizon Disposition"
, terms.unship_firm_cutoff_days "ATS Horizon Cutoff Days"
, terms.use_edi_sdp_code_flag "Use Cust Ship Delivery"
, terms.ship_delivery_rule_name "Ship Delivery Code"
, decode(terms.ship_delivery_rule_name,'D','Monday','E','Tuesday','F','Wednesday','G','Thursday',
'H','Friday','J','Saturday','K','Sunday','N','As Directed',
'O','Daily Monday through Friday','P','1/2 Monday and 1/2 Thursday',
'T','1/2 Tuesday and 1/2 Friday','Y','None','R','1/2 Wednwsday and 1/2 Friday',
'SZ','Tuesday,Thursday and Friday','13','Monday','14','Tuesday','15','Wednesday',
'16','Thursday','17','Friday','18','Saturday','19','Sunday','20','Immediately',
'21','As Directed','23','Daily Monday through Friday','ZZZ','Mutually defined') "Default Ship Delivery Pattern"
, terms.demand_tolerance_above "Demand Tolerance Positive"
, terms.demand_tolerance_below "Demand Tolerance Negative"
, terms.round_to_std_pack_flag "Std Pack Round to"
, terms.std_pack_qty "STD Pack Qty"
, ooh.order_number "Order Number"
, terms.intransit_time "Intransit Time"
, terms.time_uom_code "Intransit UOM Code"
, decode (terms.time_uom_code,'DAY','Day','HR','Hour') "Intransit Unit of Measure"
,(select meaning from fnd_lookups where lookup_code=terms.intransit_calc_basis)"intransit calc basis"
, terms.exclude_non_workdays_flag "Exclude Non-Workdays"
, terms.cum_control_code
,(select meaning from fnd_lookups where lookup_code like terms.cum_control_code)"CUM Management Type"
, terms.cum_org_level_code "CUM Org Level"
, terms.cum_shipment_rule_code "Shipment Rule Code"
, terms.cust_shipto_terms_id
, terms.cust_assign_supplier_cd "Assigned Supplier Code"
, terms.address_id
, terms.customer_id
, terms.ship_from_org_id
, terms.ship_method
, terms.cum_current_record_year
, terms.cum_previous_record_year
, terms.cum_current_start_date
, terms.cum_previous_start_date
, terms.cum_yesterd_time_cutoff
, terms.cust_assign_supplier_cd
, terms.customer_rcv_calendar_cd
, terms.freight_code
, terms.supplier_shp_calendar_cd
, terms.unship_firm_cutoff_days
, terms.use_edi_sdp_code_flag
, terms.inactive_date
, terms.match_across_key
, terms.match_within_key
, terms.header_id
, terms.price_list_id
, terms.critical_attribute_key
, terms.customer_contact_id
, terms.supplier_contact_id
, terms.agreement_id
, terms.agreement_name
, terms.future_agreement_id
, terms.future_agreement_name
, terms.comments
, terms.last_updated_by
, terms.last_update_date
, terms.creation_date
, terms.created_by
, terms.attribute_category
, terms.attribute1
, terms.attribute2
, terms.attribute3
, terms.attribute4
, terms.attribute5
, terms.attribute6
, terms.attribute7
, terms.attribute8
, terms.attribute9
, terms.attribute10
, terms.attribute11
, terms.attribute12
, terms.attribute13
, terms.attribute14
, terms.attribute15
, terms.last_update_login
, terms.request_id
, terms.program_application_id
, terms.program_id
, terms.program_update_date
, terms.tp_attribute1
, terms.tp_attribute2
, terms.tp_attribute3
, terms.tp_attribute4
, terms.tp_attribute5
, terms.tp_attribute6
, terms.tp_attribute7
, terms.tp_attribute8
, terms.tp_attribute9
, terms.tp_attribute10
, terms.tp_attribute11
, terms.tp_attribute12
, terms.tp_attribute13
, terms.tp_attribute14
, terms.tp_attribute15
, terms.tp_attribute_category
, terms.intransit_calc_basis
, terms.default_ship_from
, terms.pln_frozen_flag
, terms.shp_frozen_flag
, terms.seq_frozen_flag
, terms.issue_warning_drop_parts_flag
, terms.org_id
, terms.blanket_number
, terms.release_rule
, terms.release_time_frame
, terms.release_time_frame_uom
, terms.disable_create_cum_key_flag
FROM apps.hz_cust_accounts racust
,apps.hz_parties hp
, apps.rlm_cust_shipto_terms_all terms
, apps.org_organization_definitions orgdef
, apps.oe_order_headers_All ooh
WHERE
racust.cust_account_id = terms.customer_id
and hp.party_id=racust.party_id
AND orgdef.organization_id = terms.ship_from_org_id
AND terms.address_id IS NULL
and ooh.header_id=terms.header_id;
-- -----------------------------------------------------------------------------------
-- Processing Rule - Item Level
-- -----------------------------------------------------------------------------------
SELECT distinct cust_acct.account_number customer_number ,acct_site.CUST_ACCT_SITE_ID
,loc.location_id
, mci.customer_item_number
, mci.customer_item_desc
, mif.item_number
, mif.description
, mcc.commodity_code
, terms.customer_id
, terms.pln_frozen_day_from
, terms.pln_frozen_day_to
, terms.pln_firm_day_from
, terms.pln_firm_day_to
, terms.pln_forecast_day_from
, terms.pln_forecast_day_to
, terms.pln_mrp_forecast_day_from
, terms.pln_mrp_forecast_day_to
, terms.shp_frozen_day_from
, terms.shp_frozen_day_to
, terms.shp_firm_day_from
, terms.shp_firm_day_to
, terms.shp_forecast_day_from
, terms.shp_forecast_day_to
, terms.shp_mrp_forecast_day_from
, terms.shp_mrp_forecast_day_to
, terms.seq_frozen_day_from
, terms.seq_frozen_day_to
, terms.seq_firm_day_from
, terms.seq_firm_day_to
, terms.seq_forecast_day_from
, terms.seq_forecast_day_to
, terms.seq_mrp_forecast_day_from
, terms.seq_mrp_forecast_day_to
, terms.use_edi_sdp_code_flag "Use Cust Ship Delivery"
, terms.ship_delivery_rule_name "Ship Delivery Code"
, decode(terms.ship_delivery_rule_name,'D','Monday','E','Tuesday','F','Wednesday','G','Thursday',
'H','Friday','J','Saturday','K','Sunday','N','As Directed',
'O','Daily Monday through Friday','P','1/2 Monday and 1/2 Thursday',
'T','1/2 Tuesday and 1/2 Friday','Y','None','R','1/2 Wednwsday and 1/2 Friday',
'SZ','Tuesday,Thursday and Friday','13','Monday','14','Tuesday','15','Wednesday',
'16','Thursday','17','Friday','18','Saturday','19','Sunday','20','Immediately',
'21','As Directed','23','Daily Monday through Friday','ZZZ','Mutually defined') "Default Ship Delivery Pattern"
, terms.demand_tolerance_above "Demand Tolerance Positive"
, terms.demand_tolerance_below "Demand Tolerance Negative"
, terms.round_to_std_pack_flag "Std Pack Round to"
, terms.std_pack_qty "STD Pack Qty"
, ooh.order_number "Order Number"
, terms.intransit_time "Intransit Time"
, terms.time_uom_code "Intransit UOM Code"
, decode (terms.time_uom_code,'DAY','Day','HR','Hour') "Intransit Unit of Measure"
, terms.exclude_non_workdays_flag "Exclude Non-Workdays"
, terms.address_id
, terms.customer_id
, terms.ship_from_org_id
, terms.ship_method
, terms.intransit_time
, terms.time_uom_code
, terms.cum_current_start_date
, terms.cum_previous_start_date
, terms.freight_code
, terms.use_edi_sdp_code_flag
, terms.inactive_date
, terms.header_id
, terms.price_list_id
, terms.customer_contact_id
, terms.supplier_contact_id
, terms.agreement_id
, terms.agreement_name
, terms.future_agreement_id
, terms.future_agreement_name
, terms.comments
, terms.last_updated_by
, terms.last_update_date
, terms.creation_date
, terms.created_by
, terms.attribute_category
, terms.attribute1
, terms.attribute2
, terms.attribute3
, terms.attribute4
, terms.attribute5
, terms.attribute6
, terms.attribute7
, terms.attribute8
, terms.attribute9
, terms.attribute10
, terms.attribute11
, terms.attribute12
, terms.attribute13
, terms.attribute14
, terms.attribute15
, terms.last_update_login
, terms.request_id
, terms.program_application_id
, terms.program_id
, terms.program_update_date
, terms.tp_attribute1
, terms.tp_attribute2
, terms.tp_attribute3
, terms.tp_attribute4
, terms.tp_attribute5
, terms.tp_attribute6
, terms.tp_attribute7
, terms.tp_attribute8
, terms.tp_attribute9
, terms.tp_attribute10
, terms.tp_attribute11
, terms.tp_attribute12
, terms.tp_attribute13
, terms.tp_attribute14
, terms.tp_attribute15
, terms.tp_attribute_category
, terms.default_ship_from
, terms.pln_frozen_flag
, terms.shp_frozen_flag
, terms.seq_frozen_flag
, terms.issue_warning_drop_parts_flag
, terms.org_id
, terms.blanket_number
, terms.release_rule
, terms.release_time_frame
, terms.release_time_frame_uom
, terms.exclude_non_workdays_flag
, terms.disable_create_cum_key_flag
FROM apps.mtl_customer_items mci
, apps.mtl_commodity_codes mcc
,hz_parties party
,hz_cust_accounts cust_acct
,hz_party_sites party_site
,hz_locations loc
,hz_cust_acct_sites_all acct_site
, apps.rlm_cust_item_terms_all terms
, apps.mtl_item_flexfields mif
, apps.mtl_customer_item_xrefs mcix
, apps.oe_order_headers_All ooh
WHERE terms.customer_item_id = mci.customer_item_id
AND terms.address_id IS NOT NULL
And cust_acct.CUST_ACCount_ID = terms.customer_id
AND cust_acct.PARTY_ID = party.party_id
and party.party_id=party_site.party_id
and acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND acct_site.cust_acct_site_id=terms.address_id
AND mci.commodity_code_id = mcc.commodity_code_id
AND mif.inventory_item_id = mcix.inventory_item_id
AND mif.organization_id = mcix.master_organization_id
AND mcix.customer_item_id = mci.customer_item_id
AND mcix.preference_number =
(SELECT MIN (preference_number)
FROM mtl_customer_item_xrefs
WHERE customer_item_id = mci.customer_item_id
AND inactive_flag <> 'Y')
and ooh.header_id=terms.header_id
and terms.ship_from_org_id in (625,626);
-- -----------------------------------------------------------------------------------
-- Trading Partner Query
-- -----------------------------------------------------------------------------------
SELECT etg.tp_group_code "Group"
,etg.tp_group_description "Description"
,hp.party_name "Customer Name"--rc.customer_name "Customer Name" --mk
,HCA.ACCOUNT_NUMBER "Customer_number" --,rc.CUSTOMER_NUMBER "Customer Number" --mk
,HL.address1||' '||HL.city||' '||HL.state "Site Name"
,hcasa.ece_tp_location_code "EDI Location"
,eth.tp_code "Partner"
,eth.tp_description "Description"
,eth.tp_reference_ext1 "Reference1"
,eth.tp_reference_ext2 "Reference2"
,sub1.document "Transaction"
,sub1.document_type2 "Type"
,sub1.translator_code "Translator Code"
,sub1.document_standard "Document Standard"
,sub1.map_code "Map"
,pvs.ece_tp_location_code "TP Location Code"
,sub1.document_type
,sub1.edi_flag
,sub1.print_flag
,sub1.test_flag
,sub1.ATTRIBUTE_CATEGORY "TP Transaction"
,sub1.ATTRIBUTE1 "Account No"
,sub1.ATTRIBUTE2 "Account Name"
,sub1.ATTRIBUTE3 "Bank Name"
,eth.ATTRIBUTE_CATEGORY "Trading Partner"
,eth.attribute9 "Remit To Name"
,eth.attribute10 "Remit To Street"
,eth.attribute11 "Remit To City"
,eth.attribute12 "Remit To State"
,eth.attribute13 "Remit To Postal Code"
,eth.attribute14 "Remit To Country"
,eth.attribute15 "Remit To VAT NO"
FROM hz_cust_accounts hca --ra_addresses_all ra
,po_vendor_sites_all pvs
,ap_bank_branches ab
,hr_locations hr
,ece_tp_headers eth
,ece_tp_group etg
,hz_parties hp --ra_customers rc --mk
,hz_party_sites hps --mk
,hz_locations hl --mk
,hz_cust_acct_sites_All hcasa --mk
,( SELECT etd.document_id,
etd.document_type,
etd.translator_code,
etd.edi_flag,
etd.print_flag,
etd.test_flag,
elv1.meaning document,
elv2.meaning document_type2,
etd.tp_header_id,
etd.document_standard,
em.map_code,
etd.ATTRIBUTE_CATEGORY,
etd.ATTRIBUTE1,
etd.ATTRIBUTE2,
etd.ATTRIBUTE3
FROM ece_tp_details etd,
ece_lookup_values elv1,
ece_lookup_values elv2,
ece_mappings em
WHERE elv1.lookup_type = 'DOCUMENT'
AND elv1.lookup_code = etd.document_id
AND etd.map_id = em.map_id
AND elv1.enabled_flag = 'Y'
AND elv2.lookup_type = etd.document_id||':DOCUMENT_TYPE'
AND elv2.lookup_code = etd.document_type
AND elv2.enabled_flag = 'Y'
) sub1
WHERE etg.tp_group_id = eth.tp_group_id
AND eth.tp_header_id = pvs.tp_header_id (+)
AND eth.tp_header_id = hcasa.tp_header_id (+) --mk
AND eth.tp_header_id = ab.tp_header_id (+)
AND eth.tp_header_id = hr.tp_header_id (+)
AND eth.tp_header_id = sub1.tp_header_id
AND eth.tp_header_id = hcasa.tp_header_id
AND hca.party_id = hp.party_id --mk
AND HP.PARTY_ID=HPS.PARTY_ID --mk
AND HPS.LOCATION_ID=HL.LOCATION_ID --mk
AND HPS.PARTY_SITE_ID=HCASA.PARTY_SITE_ID --mk
--AND HCA.ACCOUNT_NUMBER=:account_number
--AND hp.party_name=:party_name
--and hcasa.ece_tp_location_code=:EDI_LOCATION
ORDER BY etg.tp_group_code, eth.tp_code
SELECT ooha.ORDER_NUMBER "SALES ORDER"
,ooha.ORDER_CATEGORY_CODE
,oola.ORDERED_ITEM
,oola.SUBINVENTORY
,rsh.SHIPMENT_NUM
,rsh.RECEIPT_NUM
,rsh.CUSTOMER_ID
,rsl.UNIT_OF_MEASURE
,rsl.ITEM_DESCRIPTION
,rsl.SHIPMENT_LINE_STATUS_CODE
,rsl.SOURCE_DOCUMENT_CODE
FROM OE_ORDER_HEADERS_ALL ooha
,OE_ORDER_LINES_ALL oola
,RCV_SHIPMENT_HEADERS rsh
,RCV_SHIPMENT_LINES rsl
WHERE 1=1
AND ooha.header_id = oola.header_id
AND ooha.header_id = rsl.OE_ORDER_HEADER_ID
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.OE_ORDER_LINE_ID = oola.line_id
--AND ooha.ORDER_NUMBER = '56'
AND rsl.SOURCE_DOCUMENT_CODE = 'RMA'
-- Processing Rule - Address Level
-- -----------------------------------------------------------------------------------
SELECT distinct
hcasa.ece_tp_location_code "Ship-to Location"
,hp.party_name
,hca.account_number
, hl.city
, hl.postal_code
, hl.province
, hl.county
, hl.address1 || hl.address2 || hl.address3 || hl.address4
address
, mci.customer_item_number
, mci.customer_item_desc
, mif.item_number
, mif.description
, mcc.commodity_code
, terms.customer_id
-----------DEMAND FENCES---------------------------------------------
, terms.pln_frozen_day_from
, terms.pln_frozen_day_to
, terms.pln_firm_day_from
, terms.pln_firm_day_to
, terms.pln_forecast_day_from
, terms.pln_forecast_day_to
, terms.pln_mrp_forecast_day_from
, terms.pln_mrp_forecast_day_to
, terms.shp_frozen_day_from
, terms.shp_frozen_day_to
, terms.shp_firm_day_from
, terms.shp_firm_day_to
, terms.shp_forecast_day_from
, terms.shp_forecast_day_to
, terms.shp_mrp_forecast_day_from
, terms.shp_mrp_forecast_day_to
, terms.seq_frozen_day_from
, terms.seq_frozen_day_to
, terms.seq_firm_day_from
, terms.seq_firm_day_to
, terms.seq_forecast_day_from
, terms.seq_forecast_day_to
, terms.seq_mrp_forecast_day_from
, terms.seq_mrp_forecast_day_to
----------------DEMAND mANAGEMENT--------------------------------------------
-- , terms.schedule_hierarchy_code "Consume Demand Hierarchy Code"
--,(SELECT meaning from fnd_lookups where lookup_code like terms.schedule_hierarchy_code) "Consume Demand Hierarchy"
-- , terms.unshipped_firm_disp_cd "ATS PreHorizon Disp Code"
-- ,(SELECT meaning from fnd_lookups where lookup_code like terms.unshipped_firm_disp_cd) "ATS PreHorizon Disposition"
-- , terms.unship_firm_cutoff_days "ATS Horizon Cutoff Days"
, terms.use_edi_sdp_code_flag "Use Cust Ship Delivery"
, terms.ship_delivery_rule_name "Ship Delivery Code"
, decode(terms.ship_delivery_rule_name,'D','Monday','E','Tuesday','F','Wednesday','G','Thursday',
'H','Friday','J','Saturday','K','Sunday','N','As Directed',
'O','Daily Monday through Friday','P','1/2 Monday and 1/2 Thursday',
'T','1/2 Tuesday and 1/2 Friday','Y','None','R','1/2 Wednwsday and 1/2 Friday',
'SZ','Tuesday,Thursday and Friday','13','Monday','14','Tuesday','15','Wednesday',
'16','Thursday','17','Friday','18','Saturday','19','Sunday','20','Immediately',
'21','As Directed','23','Daily Monday through Friday','ZZZ','Mutually defined') "Default Ship Delivery Pattern"
, terms.demand_tolerance_above "Demand Tolerance Positive"
, terms.demand_tolerance_below "Demand Tolerance Negative"
, terms.round_to_std_pack_flag "Std Pack Round to"
, terms.std_pack_qty "STD Pack Qty"
-------------------Order Management------------------------------------
, ooh.order_number "Order Number"
--, ott.NAME "Sales Order Type"
, terms.intransit_time "Intransit Time"
, terms.time_uom_code "Intransit UOM Code"
, decode (terms.time_uom_code,'DAY','Day','HR','Hour') "Intransit Unit of Measure"
, terms.exclude_non_workdays_flag "Exclude Non-Workdays"
---------------------CUM MANAGEMENT------------------------------------
--, terms.cum_control_code
-- ,(select meaning from fnd_lookups where lookup_code like terms.cum_control_code)"CUM Management Type"
-- , terms.cum_org_level_code "CUM Org Level"
-- , terms.cum_shipment_rule_code "Shipment Rule Code"
-- , terms.cust_shipto_terms_id
---------------------GENERAL------------------------------------------
--, terms.cust_assign_supplier_cd "Assigned Supplier Code"
, terms.address_id
, terms.customer_id
, terms.ship_from_org_id
, terms.ship_method
, terms.intransit_time
, terms.time_uom_code
--, terms.cum_current_record_year
--, terms.cum_previous_record_year
, terms.cum_current_start_date
, terms.cum_previous_start_date
-- , terms.cum_yesterd_time_cutoff
--, terms.cust_assign_supplier_cd
-- , terms.customer_rcv_calendar_cd
, terms.freight_code
--, terms.supplier_shp_calendar_cd
--, terms.unship_firm_cutoff_days
, terms.use_edi_sdp_code_flag
, terms.inactive_date
-- , terms.match_across_key
--, terms.match_within_key
, terms.header_id
, terms.price_list_id
--, terms.critical_attribute_key
, terms.customer_contact_id
, terms.supplier_contact_id
, terms.agreement_id
, terms.agreement_name
, terms.future_agreement_id
, terms.future_agreement_name
, terms.comments
, terms.last_updated_by
, terms.last_update_date
, terms.creation_date
, terms.created_by
, terms.attribute_category
, terms.attribute1
, terms.attribute2
, terms.attribute3
, terms.attribute4
, terms.attribute5
, terms.attribute6
, terms.attribute7
, terms.attribute8
, terms.attribute9
, terms.attribute10
, terms.attribute11
, terms.attribute12
, terms.attribute13
, terms.attribute14
, terms.attribute15
, terms.last_update_login
, terms.request_id
, terms.program_application_id
, terms.program_id
, terms.program_update_date
, terms.tp_attribute1
, terms.tp_attribute2
, terms.tp_attribute3
, terms.tp_attribute4
, terms.tp_attribute5
, terms.tp_attribute6
, terms.tp_attribute7
, terms.tp_attribute8
, terms.tp_attribute9
, terms.tp_attribute10
, terms.tp_attribute11
, terms.tp_attribute12
, terms.tp_attribute13
, terms.tp_attribute14
, terms.tp_attribute15
, terms.tp_attribute_category
--, terms.intransit_calc_basis
--,(select meaning from fnd_lookups where lookup_code=terms.intransit_calc_basis)"intransit calc basis"
, terms.default_ship_from
, terms.pln_frozen_flag
, terms.shp_frozen_flag
, terms.seq_frozen_flag
, terms.issue_warning_drop_parts_flag
, terms.org_id
, terms.blanket_number
, terms.release_rule
, terms.release_time_frame
, terms.release_time_frame_uom
, terms.exclude_non_workdays_flag
, terms.disable_create_cum_key_flag
FROM apps.mtl_customer_items mci
, apps.mtl_commodity_codes mcc
, apps.XXAR4385_RA_CUST_R12_V racust
, apps.XXAR4385_RA_ADDRESS_R12_V raadd
, apps.rlm_cust_shipto_terms_all terms,
APPS.HZ_PARTIES HP,
APPS.HZ_PARTY_SITES HPS,
HZ_CUST_ACCT_SITES_ALL HCASA,
HZ_LOCATIONS HL,
HZ_CUST_ACCOUNTS HCA
, apps.mtl_item_flexfields mif
, apps.mtl_customer_item_xrefs mcix
, apps.oe_order_headers_All ooh
--, oe_transaction_types_tl ott
WHERE terms.address_id = mci.address_id
AND
terms.address_id IS NOT NULL
AND hp.party_id=hps.party_id
and hps.party_site_id=hcasa.party_site_id
and hps.location_id=hl.location_id
AND hca.party_id=hp.party_id
AND hca.cust_account_id = terms.customer_id
AND hcasa.cust_acct_site_id = terms.address_id
AND mci.commodity_code_id = mcc.commodity_code_id
AND mif.inventory_item_id = mcix.inventory_item_id
AND mif.organization_id = mcix.master_organization_id
AND mcix.customer_item_id = mci.customer_item_id
and terms.ship_from_org_id in (select organization_id from org_organization_definitions where organization_code in ('JES','MHF') )
AND mcix.preference_number =
(SELECT MIN (preference_number)
FROM mtl_customer_item_xrefs
WHERE customer_item_id = mci.customer_item_id
AND inactive_flag <> 'Y')
and ooh.header_id=terms.header_id
-- and hca.account_number=:customer_number
and hcasa.ece_tp_location_code is not null;
-- -----------------------------------------------------------------------------------
-- Processing Rule - Customer level
-- -----------------------------------------------------------------------------------
SELECT (select organization_code from org_organization_definitions where organization_id=terms.ship_from_org_id)"Organization code"
, racust.account_number
, hp.party_name
, terms.pln_frozen_day_from
, terms.pln_frozen_day_to
, terms.pln_firm_day_from
, terms.pln_firm_day_to
, terms.pln_forecast_day_from
, terms.pln_forecast_day_to
, terms.pln_mrp_forecast_day_from
, terms.pln_mrp_forecast_day_to
, terms.shp_frozen_day_from
, terms.shp_frozen_day_to
, terms.shp_firm_day_from
, terms.shp_firm_day_to
, terms.shp_forecast_day_from
, terms.shp_forecast_day_to
, terms.shp_mrp_forecast_day_from
, terms.shp_mrp_forecast_day_to
, terms.seq_frozen_day_from
, terms.seq_frozen_day_to
, terms.seq_firm_day_from
, terms.seq_firm_day_to
, terms.seq_forecast_day_from
, terms.seq_forecast_day_to
, terms.seq_mrp_forecast_day_from
, terms.seq_mrp_forecast_day_to
,(SELECT meaning from fnd_lookups where lookup_code like terms.schedule_hierarchy_code) "Consume Demand Hierarchy"
,(SELECT meaning from fnd_lookups where lookup_code like terms.unshipped_firm_disp_cd) "ATS PreHorizon Disposition"
, terms.unship_firm_cutoff_days "ATS Horizon Cutoff Days"
, terms.use_edi_sdp_code_flag "Use Cust Ship Delivery"
, terms.ship_delivery_rule_name "Ship Delivery Code"
, decode(terms.ship_delivery_rule_name,'D','Monday','E','Tuesday','F','Wednesday','G','Thursday',
'H','Friday','J','Saturday','K','Sunday','N','As Directed',
'O','Daily Monday through Friday','P','1/2 Monday and 1/2 Thursday',
'T','1/2 Tuesday and 1/2 Friday','Y','None','R','1/2 Wednwsday and 1/2 Friday',
'SZ','Tuesday,Thursday and Friday','13','Monday','14','Tuesday','15','Wednesday',
'16','Thursday','17','Friday','18','Saturday','19','Sunday','20','Immediately',
'21','As Directed','23','Daily Monday through Friday','ZZZ','Mutually defined') "Default Ship Delivery Pattern"
, terms.demand_tolerance_above "Demand Tolerance Positive"
, terms.demand_tolerance_below "Demand Tolerance Negative"
, terms.round_to_std_pack_flag "Std Pack Round to"
, terms.std_pack_qty "STD Pack Qty"
, ooh.order_number "Order Number"
, terms.intransit_time "Intransit Time"
, terms.time_uom_code "Intransit UOM Code"
, decode (terms.time_uom_code,'DAY','Day','HR','Hour') "Intransit Unit of Measure"
,(select meaning from fnd_lookups where lookup_code=terms.intransit_calc_basis)"intransit calc basis"
, terms.exclude_non_workdays_flag "Exclude Non-Workdays"
, terms.cum_control_code
,(select meaning from fnd_lookups where lookup_code like terms.cum_control_code)"CUM Management Type"
, terms.cum_org_level_code "CUM Org Level"
, terms.cum_shipment_rule_code "Shipment Rule Code"
, terms.cust_shipto_terms_id
, terms.cust_assign_supplier_cd "Assigned Supplier Code"
, terms.address_id
, terms.customer_id
, terms.ship_from_org_id
, terms.ship_method
, terms.cum_current_record_year
, terms.cum_previous_record_year
, terms.cum_current_start_date
, terms.cum_previous_start_date
, terms.cum_yesterd_time_cutoff
, terms.cust_assign_supplier_cd
, terms.customer_rcv_calendar_cd
, terms.freight_code
, terms.supplier_shp_calendar_cd
, terms.unship_firm_cutoff_days
, terms.use_edi_sdp_code_flag
, terms.inactive_date
, terms.match_across_key
, terms.match_within_key
, terms.header_id
, terms.price_list_id
, terms.critical_attribute_key
, terms.customer_contact_id
, terms.supplier_contact_id
, terms.agreement_id
, terms.agreement_name
, terms.future_agreement_id
, terms.future_agreement_name
, terms.comments
, terms.last_updated_by
, terms.last_update_date
, terms.creation_date
, terms.created_by
, terms.attribute_category
, terms.attribute1
, terms.attribute2
, terms.attribute3
, terms.attribute4
, terms.attribute5
, terms.attribute6
, terms.attribute7
, terms.attribute8
, terms.attribute9
, terms.attribute10
, terms.attribute11
, terms.attribute12
, terms.attribute13
, terms.attribute14
, terms.attribute15
, terms.last_update_login
, terms.request_id
, terms.program_application_id
, terms.program_id
, terms.program_update_date
, terms.tp_attribute1
, terms.tp_attribute2
, terms.tp_attribute3
, terms.tp_attribute4
, terms.tp_attribute5
, terms.tp_attribute6
, terms.tp_attribute7
, terms.tp_attribute8
, terms.tp_attribute9
, terms.tp_attribute10
, terms.tp_attribute11
, terms.tp_attribute12
, terms.tp_attribute13
, terms.tp_attribute14
, terms.tp_attribute15
, terms.tp_attribute_category
, terms.intransit_calc_basis
, terms.default_ship_from
, terms.pln_frozen_flag
, terms.shp_frozen_flag
, terms.seq_frozen_flag
, terms.issue_warning_drop_parts_flag
, terms.org_id
, terms.blanket_number
, terms.release_rule
, terms.release_time_frame
, terms.release_time_frame_uom
, terms.disable_create_cum_key_flag
FROM apps.hz_cust_accounts racust
,apps.hz_parties hp
, apps.rlm_cust_shipto_terms_all terms
, apps.org_organization_definitions orgdef
, apps.oe_order_headers_All ooh
WHERE
racust.cust_account_id = terms.customer_id
and hp.party_id=racust.party_id
AND orgdef.organization_id = terms.ship_from_org_id
AND terms.address_id IS NULL
and ooh.header_id=terms.header_id;
-- -----------------------------------------------------------------------------------
-- Processing Rule - Item Level
-- -----------------------------------------------------------------------------------
SELECT distinct cust_acct.account_number customer_number ,acct_site.CUST_ACCT_SITE_ID
,loc.location_id
, mci.customer_item_number
, mci.customer_item_desc
, mif.item_number
, mif.description
, mcc.commodity_code
, terms.customer_id
, terms.pln_frozen_day_from
, terms.pln_frozen_day_to
, terms.pln_firm_day_from
, terms.pln_firm_day_to
, terms.pln_forecast_day_from
, terms.pln_forecast_day_to
, terms.pln_mrp_forecast_day_from
, terms.pln_mrp_forecast_day_to
, terms.shp_frozen_day_from
, terms.shp_frozen_day_to
, terms.shp_firm_day_from
, terms.shp_firm_day_to
, terms.shp_forecast_day_from
, terms.shp_forecast_day_to
, terms.shp_mrp_forecast_day_from
, terms.shp_mrp_forecast_day_to
, terms.seq_frozen_day_from
, terms.seq_frozen_day_to
, terms.seq_firm_day_from
, terms.seq_firm_day_to
, terms.seq_forecast_day_from
, terms.seq_forecast_day_to
, terms.seq_mrp_forecast_day_from
, terms.seq_mrp_forecast_day_to
, terms.use_edi_sdp_code_flag "Use Cust Ship Delivery"
, terms.ship_delivery_rule_name "Ship Delivery Code"
, decode(terms.ship_delivery_rule_name,'D','Monday','E','Tuesday','F','Wednesday','G','Thursday',
'H','Friday','J','Saturday','K','Sunday','N','As Directed',
'O','Daily Monday through Friday','P','1/2 Monday and 1/2 Thursday',
'T','1/2 Tuesday and 1/2 Friday','Y','None','R','1/2 Wednwsday and 1/2 Friday',
'SZ','Tuesday,Thursday and Friday','13','Monday','14','Tuesday','15','Wednesday',
'16','Thursday','17','Friday','18','Saturday','19','Sunday','20','Immediately',
'21','As Directed','23','Daily Monday through Friday','ZZZ','Mutually defined') "Default Ship Delivery Pattern"
, terms.demand_tolerance_above "Demand Tolerance Positive"
, terms.demand_tolerance_below "Demand Tolerance Negative"
, terms.round_to_std_pack_flag "Std Pack Round to"
, terms.std_pack_qty "STD Pack Qty"
, ooh.order_number "Order Number"
, terms.intransit_time "Intransit Time"
, terms.time_uom_code "Intransit UOM Code"
, decode (terms.time_uom_code,'DAY','Day','HR','Hour') "Intransit Unit of Measure"
, terms.exclude_non_workdays_flag "Exclude Non-Workdays"
, terms.address_id
, terms.customer_id
, terms.ship_from_org_id
, terms.ship_method
, terms.intransit_time
, terms.time_uom_code
, terms.cum_current_start_date
, terms.cum_previous_start_date
, terms.freight_code
, terms.use_edi_sdp_code_flag
, terms.inactive_date
, terms.header_id
, terms.price_list_id
, terms.customer_contact_id
, terms.supplier_contact_id
, terms.agreement_id
, terms.agreement_name
, terms.future_agreement_id
, terms.future_agreement_name
, terms.comments
, terms.last_updated_by
, terms.last_update_date
, terms.creation_date
, terms.created_by
, terms.attribute_category
, terms.attribute1
, terms.attribute2
, terms.attribute3
, terms.attribute4
, terms.attribute5
, terms.attribute6
, terms.attribute7
, terms.attribute8
, terms.attribute9
, terms.attribute10
, terms.attribute11
, terms.attribute12
, terms.attribute13
, terms.attribute14
, terms.attribute15
, terms.last_update_login
, terms.request_id
, terms.program_application_id
, terms.program_id
, terms.program_update_date
, terms.tp_attribute1
, terms.tp_attribute2
, terms.tp_attribute3
, terms.tp_attribute4
, terms.tp_attribute5
, terms.tp_attribute6
, terms.tp_attribute7
, terms.tp_attribute8
, terms.tp_attribute9
, terms.tp_attribute10
, terms.tp_attribute11
, terms.tp_attribute12
, terms.tp_attribute13
, terms.tp_attribute14
, terms.tp_attribute15
, terms.tp_attribute_category
, terms.default_ship_from
, terms.pln_frozen_flag
, terms.shp_frozen_flag
, terms.seq_frozen_flag
, terms.issue_warning_drop_parts_flag
, terms.org_id
, terms.blanket_number
, terms.release_rule
, terms.release_time_frame
, terms.release_time_frame_uom
, terms.exclude_non_workdays_flag
, terms.disable_create_cum_key_flag
FROM apps.mtl_customer_items mci
, apps.mtl_commodity_codes mcc
,hz_parties party
,hz_cust_accounts cust_acct
,hz_party_sites party_site
,hz_locations loc
,hz_cust_acct_sites_all acct_site
, apps.rlm_cust_item_terms_all terms
, apps.mtl_item_flexfields mif
, apps.mtl_customer_item_xrefs mcix
, apps.oe_order_headers_All ooh
WHERE terms.customer_item_id = mci.customer_item_id
AND terms.address_id IS NOT NULL
And cust_acct.CUST_ACCount_ID = terms.customer_id
AND cust_acct.PARTY_ID = party.party_id
and party.party_id=party_site.party_id
and acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND acct_site.cust_acct_site_id=terms.address_id
AND mci.commodity_code_id = mcc.commodity_code_id
AND mif.inventory_item_id = mcix.inventory_item_id
AND mif.organization_id = mcix.master_organization_id
AND mcix.customer_item_id = mci.customer_item_id
AND mcix.preference_number =
(SELECT MIN (preference_number)
FROM mtl_customer_item_xrefs
WHERE customer_item_id = mci.customer_item_id
AND inactive_flag <> 'Y')
and ooh.header_id=terms.header_id
and terms.ship_from_org_id in (625,626);
-- -----------------------------------------------------------------------------------
-- Trading Partner Query
-- -----------------------------------------------------------------------------------
SELECT etg.tp_group_code "Group"
,etg.tp_group_description "Description"
,hp.party_name "Customer Name"--rc.customer_name "Customer Name" --mk
,HCA.ACCOUNT_NUMBER "Customer_number" --,rc.CUSTOMER_NUMBER "Customer Number" --mk
,HL.address1||' '||HL.city||' '||HL.state "Site Name"
,hcasa.ece_tp_location_code "EDI Location"
,eth.tp_code "Partner"
,eth.tp_description "Description"
,eth.tp_reference_ext1 "Reference1"
,eth.tp_reference_ext2 "Reference2"
,sub1.document "Transaction"
,sub1.document_type2 "Type"
,sub1.translator_code "Translator Code"
,sub1.document_standard "Document Standard"
,sub1.map_code "Map"
,pvs.ece_tp_location_code "TP Location Code"
,sub1.document_type
,sub1.edi_flag
,sub1.print_flag
,sub1.test_flag
,sub1.ATTRIBUTE_CATEGORY "TP Transaction"
,sub1.ATTRIBUTE1 "Account No"
,sub1.ATTRIBUTE2 "Account Name"
,sub1.ATTRIBUTE3 "Bank Name"
,eth.ATTRIBUTE_CATEGORY "Trading Partner"
,eth.attribute9 "Remit To Name"
,eth.attribute10 "Remit To Street"
,eth.attribute11 "Remit To City"
,eth.attribute12 "Remit To State"
,eth.attribute13 "Remit To Postal Code"
,eth.attribute14 "Remit To Country"
,eth.attribute15 "Remit To VAT NO"
FROM hz_cust_accounts hca --ra_addresses_all ra
,po_vendor_sites_all pvs
,ap_bank_branches ab
,hr_locations hr
,ece_tp_headers eth
,ece_tp_group etg
,hz_parties hp --ra_customers rc --mk
,hz_party_sites hps --mk
,hz_locations hl --mk
,hz_cust_acct_sites_All hcasa --mk
,( SELECT etd.document_id,
etd.document_type,
etd.translator_code,
etd.edi_flag,
etd.print_flag,
etd.test_flag,
elv1.meaning document,
elv2.meaning document_type2,
etd.tp_header_id,
etd.document_standard,
em.map_code,
etd.ATTRIBUTE_CATEGORY,
etd.ATTRIBUTE1,
etd.ATTRIBUTE2,
etd.ATTRIBUTE3
FROM ece_tp_details etd,
ece_lookup_values elv1,
ece_lookup_values elv2,
ece_mappings em
WHERE elv1.lookup_type = 'DOCUMENT'
AND elv1.lookup_code = etd.document_id
AND etd.map_id = em.map_id
AND elv1.enabled_flag = 'Y'
AND elv2.lookup_type = etd.document_id||':DOCUMENT_TYPE'
AND elv2.lookup_code = etd.document_type
AND elv2.enabled_flag = 'Y'
) sub1
WHERE etg.tp_group_id = eth.tp_group_id
AND eth.tp_header_id = pvs.tp_header_id (+)
AND eth.tp_header_id = hcasa.tp_header_id (+) --mk
AND eth.tp_header_id = ab.tp_header_id (+)
AND eth.tp_header_id = hr.tp_header_id (+)
AND eth.tp_header_id = sub1.tp_header_id
AND eth.tp_header_id = hcasa.tp_header_id
AND hca.party_id = hp.party_id --mk
AND HP.PARTY_ID=HPS.PARTY_ID --mk
AND HPS.LOCATION_ID=HL.LOCATION_ID --mk
AND HPS.PARTY_SITE_ID=HCASA.PARTY_SITE_ID --mk
--AND HCA.ACCOUNT_NUMBER=:account_number
--AND hp.party_name=:party_name
--and hcasa.ece_tp_location_code=:EDI_LOCATION
ORDER BY etg.tp_group_code, eth.tp_code
SELECT ooha.ORDER_NUMBER "SALES ORDER"
,ooha.ORDER_CATEGORY_CODE
,oola.ORDERED_ITEM
,oola.SUBINVENTORY
,rsh.SHIPMENT_NUM
,rsh.RECEIPT_NUM
,rsh.CUSTOMER_ID
,rsl.UNIT_OF_MEASURE
,rsl.ITEM_DESCRIPTION
,rsl.SHIPMENT_LINE_STATUS_CODE
,rsl.SOURCE_DOCUMENT_CODE
FROM OE_ORDER_HEADERS_ALL ooha
,OE_ORDER_LINES_ALL oola
,RCV_SHIPMENT_HEADERS rsh
,RCV_SHIPMENT_LINES rsl
WHERE 1=1
AND ooha.header_id = oola.header_id
AND ooha.header_id = rsl.OE_ORDER_HEADER_ID
AND rsh.shipment_header_id = rsl.shipment_header_id
AND rsl.OE_ORDER_LINE_ID = oola.line_id
--AND ooha.ORDER_NUMBER = '56'
AND rsl.SOURCE_DOCUMENT_CODE = 'RMA'
No comments:
Post a Comment