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;
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;
No comments:
Post a Comment