Introduction
This Post is about to Sales Order Dispatch details (Link between Material Transactions and Invoice) in Oracle EBS R12.
Script to Sales Order Dispatch details (Link between Material Transactions and Invoice)
SELECT
oola.line_number,
oola.line_id,
ooha.orig_sys_document_ref ,
ooha.order_source_id,
ooha.order_number sale_order_no,
trunc(ooha.ordered_date) sale_order_date,
hp_ship.party_name customer_name,
–hl_ship.address1,
hcs_ship.location customer_site,
ooha.cust_po_number customer_po_ref,
rct.trx_number customer_invoice_num,
rct.trx_date invoice_date,
msib.segment1 item_code,
msib.description item_desc,
oola.shipping_quantity qty,
oola.order_quantity_uom uom,
ms.serial_number serial_num,
mth.request_number move_order_ref,
trunc(actual_shipment_date) shipment_date
FROM
oe_order_headers_all ooha,
oe_order_lines_all oola,
mtl_system_items_b msib,
wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd,
hz_cust_site_uses_all hcs_ship,
hz_cust_acct_sites_all hca_ship,
hz_party_sites hps_ship,
hz_parties hp_ship,
hz_locations hl_ship,
oe_order_sources oos,
mtl_txn_request_lines mtl,
mtl_txn_request_headers mth,
mtl_material_transactions mt,
mtl_unit_transactions ms,
ra_customer_trx_all rct,
ra_customer_trx_lines_all rcta
WHERE
ooha.header_id = oola.header_id
AND NVL(ooha.orig_sys_document_ref,-1) NOT IN (select prha.segment1 from po_requisition_headers_all prha)
AND oola.ship_from_org_id = msib.organization_id
AND oola.inventory_item_id = msib.inventory_item_id
AND wdd.source_header_id = ooha.header_id
AND wdd.source_line_id = oola.line_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wnd.delivery_id = wda.delivery_id
AND wdd.released_status = ‘C’
AND wnd.status_code = ‘CL’
AND ooha.ship_to_org_id = hcs_ship.site_use_id
AND hcs_ship.cust_acct_site_id = hca_ship.cust_acct_site_id
AND hca_ship.party_site_id = hps_ship.party_site_id
AND hps_ship.party_id = hp_ship.party_id
AND hps_ship.location_id = hl_ship.location_id
AND oos.order_source_id=ooha.order_source_id
AND mtl.txn_source_line_id = oola.line_id
AND mth.header_id = mtl.header_id
AND mt.move_order_line_id(+) = mtl.line_id
AND mt.transaction_id = ms.transaction_id(+)
AND mt.subinventory_code(+) = mtl.from_subinventory_code
AND rcta.interface_line_attribute1(+)=to_char(ooha.order_number)
AND rcta.interface_line_attribute6(+)=to_char(oola.line_id)
AND rcta.customer_trx_id=rct.customer_trx_id(+)
order by ooha.order_number;
What we expect in the script
This script helps us to Sales Order Dispatch details (Link between Material Transactions and Invoice) . Couple of tables which is being used in the script are. oe_order_headers_all ooha, oe_order_lines_all,mtl_system_items_b,wsh_delivery_details, wsh_delivery_assignments,wsh_new_deliveries,hz_cust_site_uses_all ,hz_cust_acct_sites_all,hz_party_sites hps_ship,hz_parties hp_ship, etc
Summary
This Post described the Sales Order Dispatch details (Link between Material Transactions and Invoice) script Oracle EBS R12.
Got any queries?
Do drop a note by writing us at contact@doyensys.com or use the comment section below to ask your questions.
