ECC Genealogy and Trace, Process Batch

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,
                        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('17-APR-20 11:47:46','DD-MON-RR HH24:MI:SS')
                                AND mmt.transaction_date <= TO_DATE('17-APR-21 11:47:46','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)
                            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('17-APR-20 11:47:46','DD-MON-RR HH24:MI:SS')
                                AND mmt.transaction_date <= TO_DATE('17-APR-21 11:47:46','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)
                            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('17-APR-20 11:47:46','DD-MON-RR HH24:MI:SS')
                                AND mmt.transaction_date <= TO_DATE('17-APR-21 11:47:46','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'
                ) lkup1,
                ( SELECT ml1.meaning,ml1.lookup_code,ml1.language
                  FROM fnd_lookup_values ml1
                  WHERE ml1.lookup_type = 'SYS_YES_NO'
                ) lkup2,
               ( select meaning , language
                 FROM fnd_lookup_values
                 WHERE lookup_type = 'WIP_GNT_ECC_NW_NODETYPE'
                 AND lookup_code = '7'
               ) 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