ECC Process Genealogy and Trace, Process Batch
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: opm-gnt-processbatch
Query Procedure: GMO_ECC_GNT_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: gmo_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Dataset Key: opm-gnt-processbatch
Query Procedure: GMO_ECC_GNT_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: gmo_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 /*+ 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, Decode(terminated_ind, 0, 'TRUE', 'FALSE') terminated_FLAG, CASE WHEN bhold.hold_type IN ('P', 'S') THEN 'FALSE' ELSE 'TRUE' END BATCH_HOLD_FLAG, gmding.inventory_item_id ing_item_id, msiving.concatenated_segments ingredient, msiving.description ingredient_desc, gbsr.resources FROM ( SELECT /*+ leading(mmt) index(mmt MTL_MATERIAL_TRANSACTIONS_N15) */ 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: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 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_N15) */ 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: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 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_N15) */ 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: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 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, mtl_system_items_vl msiving, gme_batch_header gbh, gmd_recipes_b gr1, gmd_routings_b grou, gmd_recipe_validity_rules grvr, gme_material_details gmd1, gme_material_details gmding, fm_form_mst_b fm, mtl_parameters mp, mtl_transaction_types mtt, (SELECT * FROM gme_batch_onhold_dtls WHERE end_date IS NULL OR end_date > SYSDATE) bhold, (SELECT DISTINCT batch_id, resources FROM GME_BATCH_STEP_RESOURCES) gbsr 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 gmding.batch_id = gbh.batch_id AND gmding.line_type = -1 AND gmding.inventory_item_id = msiving.inventory_item_id 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 AND bhold.batch_id (+) = gbh.batch_id AND gbsr.batch_id (+) = gbh.batch_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 |
|
LOV |
