ECC Project Manufacturing, On-Hand Balance
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: Project Manufacturing Inventory Balance
Dataset Key: pjm-inv-balance
Query Procedure: PJM_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: PJM_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValues
Description: Project Manufacturing Inventory Balance
Dataset Key: pjm-inv-balance
Query Procedure: PJM_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: PJM_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValues
Run
ECC Project Manufacturing, On-Hand Balance and other Oracle EBS reports with Blitz Report™ on our demo environment
select x.* from ( select * from ( SELECT bal.ecc_spec_id AS ecc_spec_id, bal.record_type AS record_type, bal.language AS language, GREATEST( NVL( pp.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( pt.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( bal.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ) AS ecc_last_update_date, pp.project_id AS project_id, pp.segment1 AS project_number, pp.org_id AS org_id, pt.task_id AS task_id, pt.task_number AS task_number, bal.inv_org_id AS inv_ord_id, bal.inv_org_name AS inv_org_name, bal.item_id AS item_id, bal.item_number AS item_number, bal.item_description AS item_description, bal.inv_sub_code AS item_sub_code, bal.inv_sup_locator AS inv_sup_locator, bal.inv_primary_uom AS inv_primary_uom, bal.inv_onhand_quantity AS inv_onhand_quantity, ppp.costing_group_id AS inv_cost_group_id, grp.cost_group AS inv_cost_group_name, grp.description AS inv_cost_group_description FROM pa_projects_all pp, pa_tasks pt, ( SELECT 'PROJ_INV_BAL' || '-' || loc.project_id || '-' || NVL( TO_CHAR(loc.task_id), 'NO_TASK' ) || '-' || qty.organization_id || '-' || qty.inventory_item_id || '-' || phyloc.concatenated_segments || '-' || item.primary_uom_code AS ecc_spec_id, 'PROJ_INV_BAL' AS record_type, org.language AS language, MAX( GREATEST( NVL( qty.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( loc.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( item.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( org.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( phyloc.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ) ) AS last_update_date, loc.project_id AS project_id, loc.task_id AS task_id, qty.organization_id AS inv_org_id, org.name AS inv_org_name, qty.inventory_item_id AS item_id, item.concatenated_segments AS item_number, msitl.description AS item_description, qty.subinventory_code AS inv_sub_code, DECODE( mp.project_reference_enabled, NULL , phyloc.concatenated_segments, 2, phyloc.concatenated_segments, 1, inv_project.get_pjm_locsegs(phyloc.concatenated_segments ) ) AS inv_sup_locator, item.primary_uom_code AS inv_primary_uom, NVL( SUM( qty.transaction_quantity ) , 0 ) AS inv_onhand_quantity FROM pjm_project_parameters para, mtl_onhand_quantities_detail qty, mtl_item_locations loc, mtl_system_items_kfv item, mtl_system_items_tl msitl, hr_all_organization_units_tl org, mtl_parameters mp, mtl_item_locations_kfv phyloc WHERE loc.organization_id = qty.organization_id AND loc.organization_id = mp.organization_id AND loc.inventory_location_id = qty.locator_id AND item.inventory_item_id = qty.inventory_item_id AND item.organization_id = qty.organization_id AND org.organization_id = qty.organization_id AND msitl.inventory_item_id = item.inventory_item_id AND msitl.organization_id = item.organization_id AND msitl.language = org.language AND phyloc.organization_id = loc.organization_id AND phyloc.inventory_location_id = NVL( loc.inventory_location_id, loc.physical_location_id ) and loc.project_id = para.project_id and loc.organization_id = para.organization_id GROUP BY loc.project_id, loc.task_id, qty.organization_id, org.name, qty.inventory_item_id, item.concatenated_segments, msitl.description, qty.subinventory_code, mp.project_reference_enabled, phyloc.concatenated_segments, item.primary_uom_code, org.language ) bal, pjm_project_parameters ppp, cst_cost_groups grp WHERE pp.start_date >= NVL(to_date('2021/12/12','RRRR/MM/DD HH24:MI:SS'), pp.start_date) AND pp.project_type != 'AWARD_PROJECT' AND pp.template_flag = 'N' AND pp.project_id = bal.project_id AND pt.task_id(+) = bal.task_id AND ppp.project_id(+) = pp.project_id AND ppp.organization_id = bal.inv_org_id AND grp.cost_group_id(+) = ppp.costing_group_id AND language IN ('US') ) PIVOT( MAX(inv_org_name) AS inv_org_name, MAX(item_description) AS item_description FOR LANGUAGE IN ('US' "US")) ) x where 2=2 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV |