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
Dataset Key: opm-gnt-lottransactions
Query Procedure: GMO_ECC_GNT_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: inv_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Run
ECC Process Genealogy and Trace, Lot Numbers and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |
|
LOV |
