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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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
x.organization_code=:organization_code
LOV