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
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('','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
x.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:operating_unit)
LOV