ECC Genealogy and Trace, Process Batch

➜ Back to Library
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: gnt-processbatch
Query Procedure: WIP_ECC_GNT_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: gmo_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Run ECC Genealogy and Trace, Process Batch and other Oracle EBS reports with Blitz Report™ on our demo environment
select
x.*
from
(
 SELECT * FROM ( SELECT /*+ leading(batch) no_merge(batch) */
                batch.*,
                lkup1.meaning   batch_status_meaning,
                lkup2.meaning   batch_terminated_status,
                lkup1.language language,
                nodetype_lkp.meaning node_type
            FROM
                (
                    SELECT /*+ leading(wo_txns) no_merge(wo_txns)  */  to_char(wo_txns.type||'-'||wo_txns.transaction_id||'-'||wo_txns.gen_object_id ) ecc_spec_id,
                        mp.organization_code,
                        mp.organization_id,
                        (select name from hr_all_organization_units h
                         where h.organization_id = mp.organization_id) org_name,
                        msiv.concatenated_segments,
                        msiv.description,
                        msiv.primary_uom_code,
                        wo_txns.transaction_id,
                        decode(wo_txns.type, 1, wo_txns.gen_object_id) lot_gen_obj_id,
                        decode(wo_txns.type, 2, wo_txns.gen_object_id) ser_gen_obj_id,
                        wo_txns.transaction_date,
                        wo_txns.transaction_quantity,
                        wo_txns.transaction_uom,
                        wo_txns.transaction_source_id,
                        wo_txns.revision,
                        mtt.transaction_type_name,
                        gbh.batch_no,
                        gbh.batch_id,
                        gbh.batch_status,
                        gbh.plan_start_date,
                        gbh.plan_cmplt_date,
                        gbh.actual_start_date,
                        gbh.actual_cmplt_date,
                        gr1.recipe_no,
                        gr1.recipe_version,
                        grou.routing_no,
                        grou.routing_vers,
                        gmd1.plan_qty,
                        nvl(gmd1.actual_qty, 0) actual_qty,
                        gmd1.dtl_um,
                        fm.formula_no,
                        fm.formula_vers,
                        gbh.batch_close_date,
                        gbh.terminated_ind
                    FROM
                        (   SELECT /*+ leading(mmt) index(mmt MTL_MATERIAL_TRANSACTIONS_N5)  */ 
                                mmt.transaction_source_id,
                                (select mln.gen_object_id from mtl_lot_numbers mln
                                 where mln.lot_number = mtln.lot_number
                                 and mln.organization_id = mtln.organization_id
                                 and mln.inventory_item_id = mtln.inventory_item_id)gen_object_id,
                                1 type,
                                mtln.transaction_date transaction_date,
                                abs(mtln.transaction_quantity) transaction_quantity,
                                mtln.transaction_id,
                                mmt.transaction_uom,
                                mmt.transaction_action_id,
                                mmt.transaction_type_id,
                                mtln.serial_transaction_id,
                                10 entity_type,
                                mmt.organization_id,
                                mtln.inventory_item_id,
                                mmt.revision
                            FROM mtl_material_transactions     mmt,
                                mtl_transaction_lot_numbers   mtln
                            WHERE mmt.transaction_id = mtln.transaction_id
                                AND mmt.transaction_source_type_id = 5
                                AND mmt.transaction_action_id IN (30,31,32,40,41,42,43)
                                AND mmt.transaction_date >= TO_DATE('12-DEC-21 10:09:50','DD-MON-RR HH24:MI:SS')
                                AND mmt.transaction_date <= TO_DATE('12-DEC-22 10:09:50','DD-MON-RR HH24:MI:SS')
                                ANd mmt.organization_id = nvl('',mmt.organization_id)
                                AND exists(select /*+ no_unnest push_subq */ 1 from wip_entities we
                                           where we.wip_entity_id = mmt.transaction_source_id
                                           and we.organization_id = mmt.organization_id
                                           and we.entity_type = 10
                                           )
                            UNION ALL
                            SELECT /*+ leading(mmt) index(mmt MTL_MATERIAL_TRANSACTIONS_N5)  */ 
                                mmt.transaction_source_id,
                                (select msn.gen_object_id from mtl_serial_numbers msn
                                 where msn.serial_number = mut.serial_number
                                 and msn.inventory_item_id = mut.inventory_item_id) gen_object_id,
                                2,
                                mmt.transaction_date transaction_date,
                                1 transaction_quantity,
                                mmt.transaction_id,
                                mmt.transaction_uom,
                                mmt.transaction_action_id,
                                mmt.transaction_type_id,
                                NULL,
                                10,
                                mmt.organization_id,
                                mmt.inventory_item_id,
                                mmt.revision
                            FROM mtl_material_transactions   mmt,
                                 mtl_unit_transactions       mut
                            WHERE
                                mmt.transaction_id = mut.transaction_id
                                AND mmt.transaction_source_type_id = 5
                                AND mmt.transaction_action_id IN (30,31,32,40,41,42,43)
                                AND mmt.transaction_date >= TO_DATE('12-DEC-21 10:09:50','DD-MON-RR HH24:MI:SS')
                                AND mmt.transaction_date <= TO_DATE('12-DEC-22 10:09:50','DD-MON-RR HH24:MI:SS')
                                AND mmt.organization_id = nvl('',mmt.organization_id)
                                AND exists(select  /*+ no_unnest push_subq */ 1 from wip_entities we
                                           where we.wip_entity_id = mmt.transaction_source_id
                                           and we.organization_id = mmt.organization_id
                                           and we.entity_type = 10
                                           )
                            UNION ALL
                            SELECT /*+ index(mmt MTL_MATERIAL_TRANSACTIONS_N5)  */ 
                                mmt.transaction_source_id,
                                NULL,
                                0,
                                NULL,
                                NULL,
                                mmt.transaction_id,
                                NULL,
                                NULL,
                                mmt.transaction_type_id,
                                NULL,
                                10,
                                mmt.organization_id,
                                mmt.inventory_item_id,
                                mmt.revision
                            FROM mtl_material_transactions   mmt
                            WHERE mmt.transaction_source_type_id = 5
                                AND mmt.transaction_action_id NOT IN (30,31,32,40,41,42,43)
                                AND mmt.transaction_date >= TO_DATE('12-DEC-21 10:09:50','DD-MON-RR HH24:MI:SS')
                                AND mmt.transaction_date <= TO_DATE('12-DEC-22 10:09:50','DD-MON-RR HH24:MI:SS')
                                AND mmt.organization_id = nvl('',mmt.organization_id)
                                AND exists(select 1 from wip_entities we
                                           where we.wip_entity_id = mmt.transaction_source_id
                                           and we.organization_id = mmt.organization_id
                                           and we.entity_type = 10
                                           and rownum=1)
                        ) wo_txns,
                        mtl_system_items_vl         msiv,
                        gme_batch_header            gbh,
                        gmd_recipes_b               gr1,
                        gmd_routings_b              grou,
                        gmd_recipe_validity_rules   grvr,
                        gme_material_details        gmd1,
                        fm_form_mst_b               fm,
                        mtl_parameters              mp,
                        mtl_transaction_types       mtt
                    WHERE
                        gbh.batch_type = 0
                        AND grou.routing_id (+) = gbh.routing_id
                        AND gbh.delete_mark = 0
                        AND gbh.formula_id = fm.formula_id (+)
                        AND gbh.recipe_validity_rule_id = grvr.recipe_validity_rule_id
                        AND grvr.recipe_id = gr1.recipe_id
                        AND grvr.inventory_item_id = msiv.inventory_item_id
                        AND msiv.organization_id = gbh.organization_id
                        AND gmd1.batch_id = gbh.batch_id
                        AND gmd1.inventory_item_id = grvr.inventory_item_id
                        AND gmd1.line_type = 1
                        AND gbh.batch_id = wo_txns.transaction_source_id
                        AND wo_txns.organization_id = mp.organization_id
                        AND wo_txns.transaction_type_id = mtt.transaction_type_id
                ) batch,
                ( SELECT ml1.meaning,ml1.lookup_code,ml1.language
                  FROM fnd_lookup_values ml1
                  WHERE ml1.lookup_type = 'GME_BATCH_STATUS'
                  AND ml1.language in ('US')
                ) lkup1,
                ( SELECT ml1.meaning,ml1.lookup_code,ml1.language
                  FROM fnd_lookup_values ml1
                  WHERE ml1.lookup_type = 'SYS_YES_NO'
                  AND ml1.language in ('US')
                ) lkup2,
               ( select meaning , language
                 FROM fnd_lookup_values
                 WHERE lookup_type = 'WIP_GNT_ECC_NW_NODETYPE'
                 AND lookup_code = '7'
                 AND language in ('US')
               ) nodetype_lkp
            WHERE
                batch.batch_status = lkup1.lookup_code
                AND decode(batch.terminated_ind, 1, 1, 2) = lkup2.lookup_code
                AND lkup1.language = lkup2.language
                AND nodetype_lkp.language = lkup1.language
                AND lkup1.language IN ('US') ) PIVOT ( MAX ( batch_status_meaning ) AS batch_status_meaning ,
            MAX(node_type) AS node_type ,MAX ( batch_terminated_status ) AS batch_terminated_status
            FOR language IN ('US' "US")) 
) x
where
2=2
Parameter Name SQL text Validation
Organization Code 
x.organization_id in (select mp.organization_id from mtl_parameters mp where mp.organization_code=:organization_code)
 LOV
Download
Related Blitz Reports
ECC Process Genealogy and Trace, Process Batch ECC Process Genealogy and Trace, Sales Orders OPM Transactions, Reservations, Pending Product Lots OPM2 ECC Discrete Manufacturing, Work Orders ECC Process Genealogy and Trace, Purchase Orders CAC Purchase Price Variance INV Material Transactions INV Inventory Item Upload
Blitz Report™

Blitz Report™ provides multiple benefits: