ECC Process Genealogy and Trace, Lot Numbers

Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: opm-gnt-lottransactions
Query Procedure: GMO_ECC_GNT_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
(
with lot_Split as
                     (SELECT /*+ INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N15)  */ 
                             mmt.organization_id,
                             mmt.inventory_item_id,
                             mtln.lot_number,
                             mmt.transaction_set_id,
                             mmt.transaction_date transaction_date,
                             abs(mmt.transaction_quantity) transaction_quantity,
                             mmt.transaction_id,
                             mmt.transaction_uom,
                             mmt.transaction_action_id,
                             mmt.transaction_type_id,
                             CASE WHEN mmt.transaction_quantity <0 THEN 1
                                  ELSE 2 END txnmode,
                             mmt.revision
                      FROM mtl_material_transactions   mmt,
                           mtl_transaction_lot_numbers mtln
                      WHERE mmt.transaction_action_id in (3,40,41,42)
                      and mmt.transaction_source_type_id = 13
                      AND mmt.transaction_type_id in (82,83,84,3)
                      AND mmt.transaction_id = mtln.transaction_id
                      AND mmt.transaction_date >= TO_DATE('12-DEC-21 10:10:45','DD-MON-RR HH24:MI:SS')
                      AND mmt.transaction_date <= TO_DATE('12-DEC-22 10:10:45','DD-MON-RR HH24:MI:SS')
                      AND mmt.organization_id = nvl('',mmt.organization_id) )SELECT * FROM (SELECT /*+ leading(lot_txn) no_merge(lot_txn)  */  to_char(lot_txn.query_type||'-'||lot_txn.transaction_id||'-'||lot_txn.header_id||
                        '-'||mln.gen_object_id) ecc_spec_id,
                        lot_txn.organization_id,
                        (select name from hr_all_organization_units h
                 where h.organization_id = lot_txn.organization_id) org_name,
                        mp.organization_code,
                        lot_txn.inventory_item_id,
                        msiv.concatenated_segments item,
                        lot_txn.lot_number,
                        msiv.description,
                        mln.expiration_date,
                        disabled_flag_lkp.meaning disabled_flag,
                        disabled_flag_lkp.language language,
                        nodetype_lkp.meaning node_type,
                        /* action_lkp1.meaning */ 'QA' LOT_QA_RESULTS,
                        /* action_lkp2.meaning */ 'VIEW' LOT_TXN_VIEW,
                        mln.grade_code,
                        mln.origination_date,
                        mln.place_of_origin,
                        mln.retest_date,
                        mln.vendor_name,
                        mln.expiration_action_date,
                        mln.hold_date,
                        mln.parent_lot_number,
                        origination_type_lkp.meaning origination_type,
                        origination_type_lkp.lookup_code origination_type_code,
                        null supplier_lot_number,
                        status_code_lkp.status_code status_code,
                        null origination_transaction_type,
                        lot_txn.transaction_date,
                        lot_txn.transaction_quantity,
                        lot_txn.revision,
                        nvl(lot_txn.transaction_uom, msiv.primary_uom_code) transaction_uom,
                        lot_txn.transaction_action_id,
                        lot_txn.transaction_type_id,
                        lot_txn.serial_transaction_id,
                        (SELECT transaction_type_name
                         FROM mtl_transaction_types mtt
                         WHERE mtt.transaction_type_id = lot_txn.transaction_type_id
                        ) transaction_type_name,
                        mln.gen_object_id,
                        decode(lot_txn.query_type,4,lot_txn.header_id,
                                                  5,lot_txn.header_id,
                                                  6,lot_txn.header_id,NULL) res_lot_gen_obj_id,
                        decode(lot_txn.query_type,2,lot_txn.header_id, NULL) work_order_id,
                        decode(lot_txn.query_type,1,lot_txn.header_id,3,lot_txn.header_id,NULL) sales_order_id,
                        null status_flag,
                        null grade_flag,
                        moqd.onhand_quantity,
                        CASE WHEN mln.expiration_date < SYSDATE AND Nvl(moqd.onhand_quantity, 0) > 0 THEN
                          'FALSE'
                        ELSE 'TRUE'
                        END LOT_EXPIRY_FLAG
                    FROM
                        ( SELECT /*+ INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N15)  */ 
                                1 query_type,
                                mmt.organization_id,
                                mmt.inventory_item_id,
                                mtln.lot_number,
                                (select ool.header_id
                                 from oe_order_lines_all ool
                                 where ool.line_id = mmt.trx_source_line_id) header_id ,
                                mmt.transaction_date   transaction_date,
                                abs(mtln.transaction_quantity) transaction_quantity,
                                mmt.transaction_id,
                                mmt.transaction_uom     transaction_uom,
                                mmt.transaction_action_id,
                                mmt.transaction_type_id,
                                mtln.serial_transaction_id,
                                mmt.revision
                            FROM
                                mtl_transaction_lot_numbers   mtln,
                                mtl_material_transactions     mmt
                            WHERE
                                mmt.transaction_source_type_id IN (2,12,8)
                                AND mmt.transaction_id = mtln.transaction_id
                                AND mmt.trx_source_line_id is not null
                                AND mmt.transaction_date >= TO_DATE('12-DEC-21 10:10:45','DD-MON-RR HH24:MI:SS')
                                AND mmt.transaction_date <= TO_DATE('12-DEC-22 10:10:45','DD-MON-RR HH24:MI:SS')
                                AND mmt.organization_id = nvl('',mmt.organization_id)
                            UNION ALL
                            SELECT /*+ INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N15)  */ 
                                2,
                                mmt.organization_id,
                                mmt.inventory_item_id,
                                mtln.lot_number,
                                mmt.transaction_source_id,
                                mmt.transaction_date transaction_date,
                                abs(mtln.transaction_quantity) transaction_quantity,
                                mmt.transaction_id,
                                mmt.transaction_uom,
                                mmt.transaction_action_id,
                                mmt.transaction_type_id,
                                mtln.serial_transaction_id,
                                mmt.revision
                            FROM
                                mtl_transaction_lot_numbers   mtln,
                                mtl_material_transactions     mmt
                            WHERE
                                mmt.transaction_source_type_id = 5
                                AND mmt.transaction_id = mtln.transaction_id
                                AND mmt.transaction_action_id IN (1,27,33,34)
                                AND mmt.transaction_date >= TO_DATE('12-DEC-21 10:10:45','DD-MON-RR HH24:MI:SS')
                                AND mmt.transaction_date <= TO_DATE('12-DEC-22 10:10:45','DD-MON-RR HH24:MI:SS')
                                ANd mmt.organization_id = nvl('',mmt.organization_id)
                            UNION ALL
                            SELECT 4,
                                   par_lot.organization_id,
                                   par_lot.inventory_item_id,
                                   par_lot.lot_number,
                                   (select mln.gen_object_id from mtl_lot_numbers mln
                                    where mln.organization_id =ch_lot.organization_id
                                    and mln.inventory_item_id = ch_lot.inventory_item_id
                                    and mln.lot_number =ch_lot.lot_number) header_id,
                                   par_lot.transaction_date,
                                   par_lot.transaction_quantity,
                                   par_lot.transaction_id,
                                   par_lot.transaction_uom,
                                   par_lot.transaction_action_id,
                                   par_lot.transaction_type_id,
                                   NULL,
                                   par_lot.revision
                            FROM lot_Split par_lot, lot_Split ch_lot
                            where par_lot.transaction_set_id = ch_lot.transaction_set_id
                            and par_lot.transaction_type_id = ch_lot.transaction_type_id
                            and par_lot.transaction_action_id = ch_lot.transaction_action_id
                            and par_lot.txnmode =1
                            and ch_lot.txnmode =2
                            UNION ALL
                            SELECT /*+ INDEX(wlsm WSM_LOT_SPLIT_MERGES_ECC_N1)  */ 
                                5,
                                wlsm.organization_id,
                                wssl.inventory_item_id,
                                wssl.lot_number,
                                (SELECT mln.gen_object_id
                                 FROM mtl_lot_numbers mln
                                 WHERE mln.organization_id = wsrl.organization_id
                                 AND mln.inventory_item_id = wsrl.inventory_item_id
                                 AND mln.lot_number = wsrl.lot_number
                                ),
                                wlsm.transaction_date,
                                decode(wlsm.transaction_type_id, 1, wsrl.quantity, 2, wssl.quantity,
                                       wssl.quantity),
                                wlsm.transaction_id,
                                NULL,
                                wlsm.transaction_type_id + 39,
                                wlsm.transaction_type_id + 81,
                                NULL,
                                wssl.revision
                            FROM
                                wsm_lot_split_merges    wlsm,
                                wsm_sm_starting_lots    wssl,
                                wsm_sm_resulting_lots   wsrl
                            WHERE
                                wlsm.transaction_date >= TO_DATE('12-DEC-21 10:10:45','DD-MON-RR HH24:MI:SS')
                                AND wlsm.transaction_date <= TO_DATE('12-DEC-22 10:10:45','DD-MON-RR HH24:MI:SS')
                                AND wlsm.organization_id = nvl('',wlsm.organization_id)
                                AND wlsm.transaction_id = wssl.transaction_id
                                AND wlsm.transaction_id = wsrl.transaction_id
                                AND wlsm.transaction_type_id in (1,2,3)
                            UNION ALL
                            SELECT /*+ INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N15)  */ 
                                   6,
                                   mtln2.organization_id,
                                   mtln2.inventory_item_id,
                                   mtln2.lot_number,
                                   (SELECT mln.gen_object_id
                                    FROM mtl_lot_numbers mln
                                    WHERE mln.organization_id = mtln.organization_id
                                    AND mln.inventory_item_id =mtln.inventory_item_id
                                    AND mln.lot_number=mtln.lot_number) gen_obj_id,
                                   mmt.transaction_date,
                                   mtln.transaction_quantity,
                                   mtln.transaction_id,
                                   mmt.transaction_uom,
                                   mmt.transaction_action_id,
                                   mmt.transaction_type_id,
                                   NULL,
                                   mmt.revision
                            FROM mtl_material_transactions mmt,
                                 mtl_transaction_lot_numbers mtln,
                                 mtl_transaction_lot_numbers mtln2
                            WHERE mmt.transaction_action_id =12
                            AND mmt.transaction_type_id =12
                            AND mmt.transaction_source_type_id = 13
                            AND mmt.transaction_id = mtln.transaction_id
                            AND mmt.transfer_transaction_id = mtln2.transaction_id
                            AND mmt.transaction_date >= TO_DATE('12-DEC-21 10:10:45','DD-MON-RR HH24:MI:SS')
                            AND mmt.transaction_date <= TO_DATE('12-DEC-22 10:10:45','DD-MON-RR HH24:MI:SS')
                            AND mmt.organization_id = nvl('',mmt.organization_id)
                            AND mtln2.transaction_date >= TO_DATE('12-DEC-21 10:10:45','DD-MON-RR HH24:MI:SS')
                            AND mtln2.transaction_date <= TO_DATE('12-DEC-22 10:10:45','DD-MON-RR HH24:MI:SS')
                            AND mtln2.organization_id = nvl('',mtln2.organization_id)
                            UNION ALL
                            SELECT /*+ INDEX(mtln MTL_TRANSACTION_LOT_NUMBERS_N2)  */ 
                                0,
                                mtln.organization_id,
                                mtln.inventory_item_id,
                                mtln.lot_number,
                                NULL,
                                mtln.transaction_date transaction_date,
                                abs(mtln.transaction_quantity) transaction_quantity,
                                mtln.transaction_id,
                                NULL,
                                NULL,
                                NULL,
                                mtln.serial_transaction_id,
                                NULL
                            FROM
                                mtl_transaction_lot_numbers mtln
                            WHERE
                                mtln.transaction_source_type_id IN (1,5,2,12,8,13,7)
                                AND mtln.transaction_date >= TO_DATE('12-DEC-21 10:10:45','DD-MON-RR HH24:MI:SS')
                                AND mtln.transaction_date <= TO_DATE('12-DEC-22 10:10:45','DD-MON-RR HH24:MI:SS')
                                ANd mtln.organization_id = nvl('',mtln.organization_id)
                        ) lot_txn,
                        mtl_lot_numbers       mln,
                        (SELECT inventory_item_id, lot_number, Organization_Id, Sum(Nvl(primary_transaction_quantity, 0)) onhand_quantity
                         FROM mtl_onhand_quantities_detail
                         GROUP BY inventory_item_id, lot_number, Organization_Id
                        ) moqd,
                        mtl_system_items_vl   msiv,
                        mtl_parameters        mp ,
                        (SELECT meaning,language,lookup_code
                         FROM fnd_lookup_values
                         WHERE lookup_type = 'SYS_YES_NO'
                         AND language in ('US')
                        ) disabled_flag_lkp,
                        (SELECT meaning,language,lookup_code
                         FROM fnd_lookup_values
                         WHERE lookup_type = 'MTL_LOT_ORIGINATION_TYPE'
                         AND language in ('US')
                        ) origination_type_lkp,
                        (SELECT mstl.status_code,language,mstl.status_id
                         FROM mtl_material_statuses_tl mstl
                         WHERE mstl.language in ('US')
                         ) status_code_lkp ,
                        (select meaning , language FROM fnd_lookup_values
                         WHERE lookup_type = 'WIP_GNT_ECC_NW_NODETYPE'
                         AND lookup_code = '1' AND language in ('US')
                        ) nodetype_lkp
						/*,
                        (select meaning , language , lookup_code
                         FROM fnd_lookup_values
                         WHERE lookup_type = 'WIP_GNT_ACTION_LINK_TYPE'
                         AND lookup_code = '2'
                        ) action_lkp1,
                        (select meaning , language , lookup_code
                         FROM fnd_lookup_values
                         WHERE lookup_type = 'WIP_GNT_ACTION_LINK_TYPE'
                         AND lookup_code = '3'
                        ) action_lkp2 */
                    WHERE
                        lot_txn.organization_id = mln.organization_id
                        AND lot_txn.inventory_item_id = mln.inventory_item_id
                        AND lot_txn.lot_number = mln.lot_number
                        AND msiv.organization_id = mln.organization_id
                        AND msiv.inventory_item_id = mln.inventory_item_id
                        AND mp.organization_id = mln.organization_id
                        AND disabled_flag_lkp.lookup_code =decode(mln.disable_flag, 1, 1, 2)
                        AND origination_type_lkp.lookup_code =  mln.origination_type
                        AND status_code_lkp.status_id = mln.status_id
                        AND moqd.inventory_item_id(+) = lot_txn.inventory_item_id
                        AND moqd.lot_number(+) = lot_txn.lot_number
                        AND moqd.Organization_Id(+) = lot_txn.organization_id
                        AND status_code_lkp.language = origination_type_lkp.language
                        AND origination_type_lkp.language = disabled_flag_lkp.language
                        AND nodetype_lkp.language = disabled_flag_lkp.language
                        /* AND action_lkp1.language = disabled_flag_lkp.language
                        AND action_lkp2.language = disabled_flag_lkp.language */
                        AND disabled_flag_lkp.language in ('US') ) PIVOT ( MAX ( disabled_flag ) AS disabled_flag ,
            /* MAX ( LOT_TXN_VIEW ) AS LOT_TXN_VIEW,MAX ( LOT_QA_RESULTS ) AS LOT_QA_RESULTS, */
            MAX ( origination_type ) AS origination_type,MAX(node_type) AS node_type ,MAX ( status_code ) AS status_code
            FOR language IN ('US' "US")) 
) x
where
2=2
Parameter Name SQL text Validation
Organization Code
x.organization_code=:organization_code
LOV
Download
 
Blitz Report™

Blitz Report™ provides multiple benefits: