ECC Project Manufacturing Genealogy & Trace, Project Locators
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: Project Manufacturing Genealogy & Trace Project Locators
Dataset Key: pjm-gnt-locators
Query Procedure: PJM_ECC_GNT_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: PJM_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValues
Description: Project Manufacturing Genealogy & Trace Project Locators
Dataset Key: pjm-gnt-locators
Query Procedure: PJM_ECC_GNT_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: PJM_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValues
Run
ECC Project Manufacturing Genealogy & Trace, Project Locators and other Oracle EBS reports with Blitz Report™ on our demo environment
select x.* from ( SELECT * FROM ( WITH pjm_projects AS ( SELECT DISTINCT project_id FROM pjm_project_parameters ), inv_txn AS ( SELECT mmt.transaction_id, mmt.transaction_date, mmt.source_project_id AS project_id, mmt.source_task_id AS task_id, mmt.inventory_item_id, mmt.organization_id, mmt.subinventory_code, mmt.locator_id, NULL AS to_locator_id FROM mtl_material_transactions mmt, pjm_projects WHERE mmt.transaction_source_type_id = 5 AND mmt.transaction_action_id IN (1, 31) AND mmt.source_project_id = pjm_projects.project_id UNION ALL SELECT mmt.transaction_id, mmt.transaction_date, mmt.project_id, mmt.task_id, mmt.inventory_item_id, mmt.organization_id, mmt.subinventory_code, mmt.locator_id, NULL FROM mtl_material_transactions mmt, pjm_projects WHERE mmt.transaction_source_type_id = 1 AND mmt.transaction_action_id = 27 AND mmt.project_id = pjm_projects.project_id UNION ALL SELECT mmt.transaction_id, mmt.transaction_date, ool.project_id, ool.task_id, mmt.inventory_item_id, mmt.organization_id, mmt.subinventory_code, mmt.locator_id, NULL FROM mtl_material_transactions mmt, wsh_delivery_details wdd, oe_order_lines_all ool, pjm_projects WHERE mmt.transaction_id = wdd.transaction_id AND mmt.transaction_source_type_id IN (2, 8) AND mmt.transaction_action_id = 28 AND wdd.source_code = 'OE' AND wdd.source_line_id = ool.line_id AND ool.project_id = pjm_projects.project_id UNION ALL SELECT mmt.transaction_id, mmt.transaction_date, oked.project_id, oked.task_id, mmt.inventory_item_id, mmt.organization_id, mmt.subinventory_code, mmt.locator_id, NULL FROM mtl_material_transactions mmt, wsh_delivery_details wdd, oke_k_deliverables_b oked, pjm_projects WHERE mmt.transaction_source_id = wdd.source_header_id AND mmt.trx_source_line_id = wdd.source_line_id AND mmt.picking_line_id = wdd.delivery_detail_id AND mmt.transaction_source_type_id = 16 AND mmt.transaction_action_id = 1 AND wdd.source_code = 'OKE' AND wdd.source_header_id = oked.k_header_id AND wdd.source_line_id = oked.deliverable_id AND oked.project_id = pjm_projects.project_id UNION ALL SELECT mmt.transaction_id, mmt.transaction_date, mmt.project_id, mmt.task_id, mmt.inventory_item_id, mmt.organization_id, mmt.subinventory_code, mmt.locator_id, DECODE(SIGN(mmt.transaction_quantity), -1, mmt.transfer_locator_id) FROM mtl_material_transactions mmt, pjm_projects WHERE mmt.transaction_source_type_id = 13 AND mmt.transaction_action_id IN (2, 27) AND mmt.project_id = pjm_projects.project_id ), inv_onhand_quantity AS ( SELECT organization_id, subinventory_code, locator_id, inventory_item_id, SUM(transaction_quantity) AS quantity FROM mtl_onhand_quantities_detail WHERE locator_id IS NOT NULL GROUP BY organization_id, subinventory_code, locator_id, inventory_item_id ) SELECT 'PJM_GNT_LOCATOR' || '-' || inv_txn.locator_id || '-' || inv_txn.transaction_id AS ecc_spec_id, pp.org_id, pp.project_id AS txn_project_id, pp_loc.project_id, pp_loc.segment1 AS project_number, pp_loc.name AS project_name, pt_loc.task_id, pt_loc.task_number, pt_loc.task_name, inv_txn.transaction_id, inv_txn.transaction_date, inv_txn.organization_id AS inv_org_id, mp.organization_code AS inv_org_code, hou.name AS inv_org, inv_txn.subinventory_code AS subinventory, inv_txn.locator_id, DECODE(mp.project_reference_enabled, 1, mil.segment1 || '.' || mil.segment2 || '.' || mil.segment3 || '.' || pp_loc.segment1 || '.' || pt_loc.task_number, mil.concatenated_segments) AS locator, flv.meaning AS type, mms.status_code AS status, inv_txn.inventory_item_id AS item_id, msi.concatenated_segments AS item_number, msit.description AS item_description, NVL(inv_onhand_quantity.quantity, 0) AS onhand_quantity, msi.primary_uom_code, inv_txn.to_locator_id, hou.language FROM pa_projects_all pp, pa_projects_all pp_loc, pa_tasks pt_loc, mtl_item_locations_kfv mil, mtl_system_items_kfv msi, mtl_system_items_tl msit, mtl_material_statuses_tl mms, mtl_parameters mp, hr_all_organization_units_tl hou, fnd_lookup_values flv, inv_txn, inv_onhand_quantity, pjm_projects WHERE pp.start_date >= NVL(TO_DATE('2021/12/12', 'YYYY/MM/DD'), pp.start_date) AND pp.project_type <> 'AWARD_PROJECT' AND pp.template_flag = 'N' AND pp.project_id = pjm_projects.project_id AND pp.project_id = inv_txn.project_id AND mil.organization_id = inv_txn.organization_id AND mil.inventory_location_id = inv_txn.locator_id AND pp_loc.project_id (+) = mil.project_id AND pt_loc.project_id (+) = mil.project_id AND pt_loc.task_id (+) = mil.task_id AND msi.organization_id = inv_txn.organization_id AND msi.inventory_item_id = inv_txn.inventory_item_id AND msit.organization_id = inv_txn.organization_id AND msit.inventory_item_id = inv_txn.inventory_item_id AND inv_onhand_quantity.organization_id (+) = inv_txn.organization_id AND inv_onhand_quantity.inventory_item_id (+) = inv_txn.inventory_item_id AND inv_onhand_quantity.subinventory_code (+) = inv_txn.subinventory_code AND inv_onhand_quantity.locator_id (+) = inv_txn.locator_id AND mp.organization_id = inv_txn.organization_id AND hou.organization_id = inv_txn.organization_id AND mms.status_id (+) = mil.status_id AND flv.view_application_id (+) = 700 AND flv.lookup_type (+) = 'MTL_LOCATOR_TYPES' AND flv.lookup_code (+) = mil.inventory_location_type AND msit.language = hou.language AND NVL(mms.language, hou.language) = hou.language AND NVL(flv.language, hou.language) = hou.language AND hou.language IN ('US')) PIVOT ( MAX(inv_org) AS inv_org, MAX(type) AS type, MAX(status) AS status, MAX(item_description) AS item_description FOR language IN ('US' "US")) ) x where 2=2 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV |