ECC Inventory Management, Inventory Trace Sales Orders
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: Inventory Trace Sales Orders Data Set
Dataset Key: inv-trace-salesorders
Query Procedure: INV_ECC_TRACK_TRACE_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: inv_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Description: Inventory Trace Sales Orders Data Set
Dataset Key: inv-trace-salesorders
Query Procedure: INV_ECC_TRACK_TRACE_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: inv_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Run
ECC Inventory Management, Inventory Trace Sales Orders and other Oracle EBS reports with Blitz Report™ on our demo environment
select x.* from ( select * from (SELECT ecc_spec_id ,order_number ,header_id ,customer_name ,booked_date ,ordered_date ,order_type ,transaction_id ,transaction_type_id ,transaction_type_name ,transaction_date ,txn_org_id organization_id ,inventory_item_id ,lot_number ,subinventory_code ,locator_id ,locator ,transfer_subinventory ,transfer_locator_id ,transfer_locator ,user_name ,pick_date ,ship_date ,organization_name ,trip_name ,carrier_id ,delivery_name ,transaction_quantity ,item ,sales_order_type ,pick_type ,organization_code ,ship_to_location , ( SELECT hp1.party_name FROM hz_parties hp1 WHERE hp1.party_id (+) = x1.carrier_id ) carrier_name ,packed_lpn ,language ,decode (PACKED_LPN,null,'','Pack') pack_type ,Pack_subinventory ,Pack_locator ,CASE when (('Y'='Y') ) THEN 'Y' ELSE 'N' END onhand_enable FROM ( SELECT /*+ leading(so_txns ooh oola wdd) use_nl(so_txns ooh oola wdd order_type_lkp hou flv1 flv2) no_merge(so_txns) */ to_char (so_txns.query_type || '-' || so_txns.organization_id || '-' || so_txns.transaction_id || '-' ||wdd.delivery_detail_id || '-' || so_txns.lot_number) ecc_spec_id , ooh.order_number , ooh.header_id , hp.party_name customer_name , ooh.booked_date , ordered_date , order_type_lkp.name order_type , order_type_lkp.language language , so_txns.transaction_id , so_txns.transaction_type_id , ( SELECT transaction_type_name FROM mtl_transaction_types mtt WHERE mtt.transaction_type_id = so_txns.transaction_type_id ) transaction_type_name , so_txns.transaction_date , so_txns.organization_id txn_org_id , so_txns.inventory_item_id inventory_item_id , so_txns.lot_number lot_number , so_txns.subinventory_code subinventory_code , so_txns.locator_id locator_id , (SELECT concatenated_segments FROM mtl_item_locations_kfv WHERE inventory_location_id =so_txns.locator_id AND organization_id = so_txns.organization_id AND subinventory_code = nvl(so_txns.subinventory_code,subinventory_code)) locator , so_txns.transfer_subinventory transfer_subinventory , so_txns.transfer_locator_id transfer_locator_id , (SELECT concatenated_segments FROM mtl_item_locations_kfv WHERE inventory_location_id =so_txns.transfer_locator_id AND organization_id = so_txns.organization_id AND subinventory_code = nvl(so_txns.transfer_subinventory,subinventory_code)) transfer_locator , ( SELECT user_name FROM fnd_user WHERE user_id = so_txns.created_by ) user_name , so_txns.pick_date pick_date , so_txns.ship_date ship_date , hou.name organization_name , nvl (wt.name, 'Unassigned') trip_name , CASE WHEN wt.trip_id IS NOT NULL THEN wt.carrier_id WHEN wt.trip_id IS NULL AND wnd.delivery_id IS NOT NULL THEN wnd.carrier_id WHEN wt.trip_id IS NULL AND wnd.delivery_id IS NULL AND wdd.delivery_detail_id IS NOT NULL THEN wdd.carrier_id ELSE NULL END carrier_id , nvl (wnd.name, 'Unassigned') delivery_name , transaction_quantity , (select UI_LOCATION_CODE from wsh_locations where wsh_location_id=wdd.ship_to_location_id) ship_to_location , so_txns.item item , flv1.meaning sales_order_type , flv2.meaning pick_type , mp.organization_code , (SELECT wlpn.license_plate_number FROM wsh_delivery_details wdd_cont, wms_lpn_histories wlpn_hist, wms_license_plate_numbers wlpn WHERE wdd_cont.delivery_detail_id = wda.parent_delivery_detail_id AND wdd_cont.lpn_id = wlpn_hist.lpn_id AND wlpn_hist.lpn_context = 11 AND wlpn_hist.organization_id = wdd.organization_id AND wlpn_hist.outermost_lpn_id = wlpn.lpn_id AND Nvl(wlpn_hist.lpn_id, -1) <> Nvl(wlpn_hist.outermost_lpn_id, -1) AND ROWNUM = 1 ) packed_lpn , (SELECT wlpn_hist.subinventory_code FROM wsh_delivery_details wdd_cont, wms_lpn_histories wlpn_hist, wms_license_plate_numbers wlpn WHERE wdd_cont.delivery_detail_id = wda.parent_delivery_detail_id AND wdd_cont.lpn_id = wlpn_hist.lpn_id AND wlpn_hist.lpn_context = 11 AND wlpn_hist.organization_id = wdd.organization_id AND wlpn_hist.outermost_lpn_id = wlpn.lpn_id AND Nvl(wlpn_hist.lpn_id, -1) <> Nvl(wlpn_hist.outermost_lpn_id, -1) AND ROWNUM = 1 ) Pack_subinventory , ( SELECT (SELECT concatenated_segments FROM mtl_item_locations_kfv WHERE inventory_location_id =wlpn_hist.locator_id AND organization_id = wlpn_hist.organization_id AND subinventory_code = nvl(wlpn_hist.subinventory_code,subinventory_code)) FROM wsh_delivery_details wdd_cont, wms_lpn_histories wlpn_hist, wms_license_plate_numbers wlpn WHERE wdd_cont.delivery_detail_id = wda.parent_delivery_detail_id AND wdd_cont.lpn_id = wlpn_hist.lpn_id AND wlpn_hist.lpn_context = 11 AND wlpn_hist.organization_id = wdd.organization_id AND wlpn_hist.outermost_lpn_id = wlpn.lpn_id AND Nvl(wlpn_hist.lpn_id, -1) <> Nvl(wlpn_hist.outermost_lpn_id, -1) AND ROWNUM = 1 ) Pack_locator FROM ( SELECT /*+ leading(mmt msi) INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N15) use_nl(mmt msi) */ 0 query_type , ( SELECT ool.header_id FROM oe_order_lines_all ool WHERE ool.line_id = mmt.trx_source_line_id AND ool.inventory_item_id = mmt.inventory_item_id ) header_id , mmt.transaction_id , mmt.transaction_type_id , mmt.transaction_quantity transaction_quantity , mmt.transaction_date , mmt.organization_id , mmt.inventory_item_id , '####' lot_number , mmt.subinventory_code , nvl (mmt.locator_id, - 999) locator_id , mmt.transfer_subinventory , nvl (mmt.transfer_locator_id, - 999) transfer_locator_id , mmt.created_by , mmt.transaction_date pick_date , mmt.transaction_date ship_date , msi.concatenated_segments item FROM mtl_material_transactions mmt , mtl_system_items_b_kfv msi WHERE mmt.transaction_source_type_id IN (2, 12, 8) AND mmt.organization_id = msi.organization_id AND mmt.inventory_item_id = msi.inventory_item_id AND mmt.transaction_action_id in (1,2,3,21,28) AND msi.lot_control_code = 1 AND mmt.TRANSACTION_DATE >= TO_DATE('12-NOV-22 10:10:02','DD-MON-RR HH24:MI:SS') AND mmt.TRANSACTION_DATE <= TO_DATE('12-DEC-22 10:10:02','DD-MON-RR HH24:MI:SS') AND mmt.organization_id = nvl('',mmt.organization_id) UNION ALL SELECT /*+ leading(mmt mtln msi) INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N15) use_nl(mmt mtln msi) */ 1 query_type , ( SELECT ool.header_id FROM oe_order_lines_all ool WHERE ool.line_id = mmt.trx_source_line_id AND ool.inventory_item_id = mmt.inventory_item_id ) header_id , mmt.transaction_id , mmt.transaction_type_id , mmt.transaction_quantity transaction_quantity , mmt.transaction_date , mmt.organization_id , mmt.inventory_item_id , mtln.lot_number lot_number , mmt.subinventory_code , nvl (mmt.locator_id, - 999) locator_id , mmt.transfer_subinventory , nvl (mmt.transfer_locator_id, - 999) transfer_locator_id , mmt.created_by , mmt.transaction_date pick_date , mmt.transaction_date ship_date , msi.concatenated_segments item FROM mtl_material_transactions mmt , mtl_transaction_lot_numbers mtln , mtl_system_items_b_kfv msi WHERE mmt.transaction_source_type_id IN (2, 12, 8) AND mmt.transaction_id = mtln.transaction_id AND mmt.transaction_id = mtln.transaction_id AND mmt.transaction_action_id in (1,2,3,21,28) AND mmt.inventory_item_id = mtln.inventory_item_id AND mmt.organization_id = mtln.organization_id AND mmt.organization_id = msi.organization_id AND mmt.inventory_item_id = msi.inventory_item_id AND msi.lot_control_code = 2 AND mmt.TRANSACTION_DATE >= TO_DATE('12-NOV-22 10:10:02','DD-MON-RR HH24:MI:SS') AND mmt.TRANSACTION_DATE <= TO_DATE('12-DEC-22 10:10:02','DD-MON-RR HH24:MI:SS') AND mmt.organization_id = nvl('',mmt.organization_id) UNION ALL SELECT query_type , ( SELECT ool.header_id FROM mtl_material_transactions mmt1 , oe_order_lines_all ool WHERE mmt1.transaction_id = iso_txn.transfer_transaction_id AND ool.line_id = mmt1.source_line_id AND ool.inventory_item_id = iso_txn.inventory_item_id ) header_id , transaction_id , transaction_type_id , transaction_quantity , transaction_date , organization_id , iso_txn.inventory_item_id , iso_txn.lot_number , iso_txn.subinventory_code , iso_txn.locator_id , iso_txn.transfer_subinventory , iso_txn.transfer_locator_id , iso_txn.created_by , iso_txn.transaction_date pick_date , iso_txn.transaction_date ship_date , iso_txn.item FROM ( SELECT /*+ INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N15) */ 1 query_type , mmt.transfer_transaction_id , mmt.inventory_item_id , mmt.transaction_id , mmt.transaction_type_id , mtln.transaction_quantity transaction_quantity , mmt.transaction_date , mmt.organization_id , mtln.lot_number , mmt.subinventory_code , nvl (mmt.locator_id, - 999) locator_id , mmt.transfer_subinventory , nvl (mmt.transfer_locator_id, - 999) transfer_locator_id , mmt.created_by , mmt.transaction_date pick_date , mmt.transaction_date ship_date , msi.concatenated_segments item FROM mtl_material_transactions mmt , mtl_transaction_lot_numbers mtln , mtl_system_items_b_kfv msi WHERE mmt.transaction_action_id = 12 AND mmt.transaction_type_id = 61 AND mmt.transaction_source_type_id = 7 AND mmt.transaction_id = mtln.transaction_id AND mmt.inventory_item_id = msi.inventory_item_id AND mmt.organization_id = msi.organization_id AND msi.lot_control_code = 2 AND mmt.TRANSACTION_DATE >= TO_DATE('12-NOV-22 10:10:02','DD-MON-RR HH24:MI:SS') AND mmt.TRANSACTION_DATE <= TO_DATE('12-DEC-22 10:10:02','DD-MON-RR HH24:MI:SS') AND mmt.organization_id = nvl('',mmt.organization_id) UNION ALL SELECT /*+ INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N15) */ 2 query_type , mmt.transfer_transaction_id , mmt.inventory_item_id , mmt.transaction_id , mmt.transaction_type_id , 1 , mmt.transaction_date , mmt.organization_id , '####' lot_number , mmt.subinventory_code , nvl (mmt.locator_id, - 999) locator_id , mmt.transfer_subinventory , nvl (mmt.transfer_locator_id, - 999) transfer_locator_id , mmt.created_by , mmt.transaction_date pick_date , mmt.transaction_date ship_date , msi.concatenated_segments item FROM mtl_material_transactions mmt , mtl_system_items_b_kfv msi WHERE mmt.transaction_action_id = 12 AND mmt.transaction_type_id = 61 AND mmt.transaction_source_type_id = 7 AND mmt.organization_id = msi.organization_id AND msi.lot_control_code=1 AND mmt.inventory_item_id = msi.inventory_item_id AND mmt.TRANSACTION_DATE >= TO_DATE('12-NOV-22 10:10:02','DD-MON-RR HH24:MI:SS') AND mmt.TRANSACTION_DATE <= TO_DATE('12-DEC-22 10:10:02','DD-MON-RR HH24:MI:SS') AND mmt.organization_id = nvl('',mmt.organization_id) ) iso_txn ) so_txns , oe_order_headers_all ooh , oe_order_lines_all oola , hz_cust_accounts hca , hz_parties hp , ( SELECT t.name name , t.language , b.transaction_type_id , b.org_id FROM oe_transaction_types_tl t , oe_transaction_types_all b WHERE b.transaction_type_code = 'ORDER' AND b.transaction_type_id = t.transaction_type_id AND t.language IN ('US') ) order_type_lkp , wsh_delivery_details wdd , wsh_new_deliveries wnd , wsh_delivery_assignments wda , wsh_delivery_legs wdl , wsh_trip_stops wts , wsh_trips wt , hr_all_organization_units_tl hou , fnd_lookup_values flv1 , fnd_lookup_values flv2 , mtl_parameters mp WHERE ooh.header_id = so_txns.header_id AND ooh.header_id = oola.header_id AND oola.inventory_item_id= so_txns.inventory_item_id AND ooh.sold_to_org_id = hca.cust_account_id AND hca.party_id = hp.party_id AND order_type_lkp.transaction_type_id = ooh.order_type_id AND order_type_lkp.org_id = ooh.org_id AND order_type_lkp.language IN ('US') AND wdd.source_header_id = ooh.header_id (+) AND wdd.source_line_id = oola.line_id AND wdd.delivery_detail_id = wda.delivery_detail_id (+) AND wda.delivery_id = wnd.delivery_id (+) AND wnd.delivery_id = wdl.delivery_id (+) AND wdl.pick_up_stop_id = wts.stop_id (+) AND wts.trip_id = wt.trip_id (+) AND so_txns.organization_id=hou.organization_id AND hou.organization_id=mp.organization_id AND hou.language=order_type_lkp.language AND hou.language IN ('US') AND flv1.lookup_type (+) = 'INV_ECC_TRACK_TRACE_NODETYPE' AND flv1.view_application_id (+) = 700 AND flv1.lookup_code (+) =75 AND flv1.language (+) = hou.language AND flv2.lookup_type (+) = 'INV_ECC_TRACK_TRACE_NODETYPE' AND flv2.view_application_id (+) = 700 AND flv2.lookup_code (+) =80 AND flv2.language (+) = hou.language ) x1 ) PIVOT ( MAX ( sales_order_type ) AS sales_order_type ,MAX( pick_type ) AS pick_type,MAX(ORGANIZATION_NAME) AS ORGANIZATION_NAME FOR language IN ('US' "US")) ) x where 2=2 |
| Parameter Name | SQL text | Validation | |
|---|---|---|---|
| Organization Code |
|
LOV |
