ECC Discrete Completions, Component Transactions
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: wip-com-componenttxns
Query Procedure: WIP_ECC_WC_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Dataset Key: wip-com-componenttxns
Query Procedure: WIP_ECC_WC_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Run
ECC Discrete Completions, Component Transactions and other Oracle EBS reports with Blitz Report™ on our demo environment
select x.* from ( SELECT * FROM (SELECT /*+ leading(mmt1) index(mmt1 MTL_MATERIAL_TRANSACTIONS_N5) cardinality(mmt1 10) use_nl(we) */ to_char(mmt1.transaction_id) ecc_spec_id, mmt1.transaction_id, entity_source_lkp.meaning entity_source, entity_source_lkp.language, 'fwkhp_sswafunc.gif' as ITEM_LINK, /* view_item_lkp.meaning item_link,*/ 'fwkhp_sswafunc.gif' as QUALITY_LINK,/* view_quality_lkp.meaning quality_link, */ we.entity_type, we.wip_entity_id, we.wip_entity_name, WIP_ECC_WC_PVT.get_concatenated_segments(mmt1.organization_id, we.primary_item_id) assembly, WIP_ECC_WC_PVT.get_description(mmt1.organization_id, we.primary_item_id) assemblydesc, mmt1.operation_seq_num, WIP_ECC_WC_PVT.get_concatenated_segments(mmt1.organization_id, mmt1.inventory_item_id) component, WIP_ECC_WC_PVT.get_description(mmt1.organization_id, mmt1.inventory_item_id) compdesc, (SELECT acat.concatenated_segments FROM mtl_item_catalog_groups_b_kfv acat WHERE acat.item_catalog_group_id = WIP_ECC_WC_PVT.get_item_catalog_group_id(mmt1.organization_id, mmt1.inventory_item_id) ) component_category, WIP_ECC_WC_PVT.get_primary_uom_code(mmt1.organization_id, mmt1.inventory_item_id) comppriuom, abs(mmt1.primary_quantity) primary_quantity, abs(mmt1.transaction_quantity) transaction_quantity, mmt1.transaction_uom, mmt1.transaction_action_id, mmt1.transaction_type_id, mmt1.transaction_date, ( SELECT mtt.transaction_type_name FROM mtl_transaction_types mtt WHERE mtt.transaction_type_id = mmt1.transaction_type_id ) transaction_type_name, mmt1.subinventory_code, decode(mp.project_reference_enabled, NULL, milk.concatenated_segments, 2, milk.concatenated_segments, 1, inv_project.get_pjm_locsegs(milk.concatenated_segments)) completion_locator, mtr.description transaction_reason_desc, mtr.reason_name transaction_reason, mp.organization_id, mp.organization_code, mmt1.transaction_source_id FROM mtl_material_transactions mmt1, wip_entities we, mtl_transaction_reasons mtr, mtl_parameters mp, mtl_item_locations_kfv milk , ( SELECT meaning , lookup_code, language FROM fnd_lookup_values WHERE lookup_type = 'WIP_ENTITY' ) entity_source_lkp /* ( SELECT ml1.meaning, ml1.lookup_code, ml1.language FROM fnd_lookup_values ml1 WHERE ml1.lookup_type = 'WIP_VIEW_QUALITY_NC' AND ml1.view_application_id = 700 AND ml1.security_group_id = 0 AND ml1.lookup_code = 3 ) view_quality_lkp, ( SELECT ml1.meaning, ml1.lookup_code, ml1.language FROM fnd_lookup_values ml1 WHERE ml1.lookup_type = 'WIP_VIEW_ITEM' AND ml1.view_application_id = 700 AND ml1.security_group_id = 0 AND ml1.lookup_code = 1 ) view_item_lkp */ WHERE mmt1.transaction_source_type_id = 5 AND mmt1.transaction_action_id IN ( 1, 27, 33, 34 ) AND mmt1.transaction_source_id = we.wip_entity_id AND we.entity_type IN ( 1, 3, 4 ) AND mmt1.transaction_date > TO_DATE('12-NOV-22 10:13:51','DD-MON-RR HH24:MI:SS') AND mmt1.transaction_date <= TO_DATE('12-DEC-22 10:13:51','DD-MON-RR HH24:MI:SS') AND mmt1.organization_id = mp.organization_id AND mmt1.locator_id = milk.inventory_location_id (+) AND mmt1.reason_id = mtr.reason_id (+) AND EXISTS ( SELECT 1 FROM mtl_material_transactions mmt WHERE mmt.transaction_source_type_id = 5 AND mmt.transaction_action_id IN ( 31, 32 ) AND mmt.transaction_date > TO_DATE('12-NOV-22 10:13:51','DD-MON-RR HH24:MI:SS') AND mmt.transaction_date <= TO_DATE('12-DEC-22 10:13:51','DD-MON-RR HH24:MI:SS') AND mmt.organization_id = mmt1.organization_id AND mmt.transaction_source_id = mmt1.transaction_source_id AND ROWNUM = 1 ) AND entity_source_lkp.lookup_code = to_char(we.entity_type) /* AND view_item_lkp.language = entity_source_lkp.language AND view_quality_lkp.language = entity_source_lkp.language */ AND entity_source_lkp.language in ('US') ) PIVOT ( MAX ( entity_source ) AS entity_source /* MAX( item_link ) AS item_link , MAX( quality_link ) AS quality_link */ FOR language IN ('US' "US")) ) x where 2=2 |