ECC Project Manufacturing, On-Hand Values

select
x.*
from
(
select * from ( SELECT    val.ecc_spec_id AS ECC_SPEC_ID, val.record_type AS RECORD_TYPE, val.language AS LANGUAGE,  GREATEST( NVL( pp.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( val.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, val.inv_cost_group_id AS INV_COST_GROUP_ID,  val.inv_cost_group_name AS INV_COST_GROUP_NAME, val.inv_cost_group_description AS INV_COST_GROUP_DESCRIPTION,  val.inv_org_id AS inv_org_id, val.inv_org_name AS inv_org_name, val.item_id AS item_id, val.item_number AS item_number,  val.currency_code AS currency_code, val.inv_onhand_quantity AS inv_onhand_quantity, val.inv_unit_cost AS inv_unit_cost,  val.inv_inventory_value AS inv_inventory_value, val.inv_material_value AS inv_material_value,  val.inv_material_overhead_value AS inv_material_overhead_value, val.inv_resource_value AS inv_resource_value,  val.inv_outside_processing_value AS inv_outside_processing_value, val.inv_overhead_value AS inv_overhead_value  FROM pa_projects_all pp, ( 	SELECT  /*+ leading(LOC) */    'PROJ_INV_VAL_CG' || '-' || para.project_id || '-' || para.organization_id || '-' || qty.inventory_item_id || '-' || layer.cost_group_id AS ecc_spec_id, 'PROJ_INV_VAL' AS record_type,  		org.language AS language,  		MAX( GREATEST( NVL( grp.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( layer.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ,  		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( para.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( mp.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( item.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ,  		NVL( orginfo.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( sob.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ,  		NVL( curr.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ) ) AS last_update_date,  		para.project_id AS project_id, layer.cost_group_id AS inv_cost_group_id, grp.cost_group AS inv_cost_group_name,  		grp.description AS inv_cost_group_description, para.organization_id AS inv_org_id, org.name AS inv_org_name,  		qty.inventory_item_id AS item_id, item.segment1 AS item_number, curr.currency_code AS currency_code,  		SUM( NVL( qty.transaction_quantity, 0 ) ) AS inv_onhand_quantity,  		ROUND( layer.item_cost, NVL( curr.extended_precision, NVL( curr.precision, 0 ) ) ) AS inv_unit_cost,  		ROUND( SUM( layer.item_cost * NVL( qty.transaction_quantity, 0 ) ) / NVL( curr.minimum_accountable_unit, POWER( 10, NVL( - curr.precision, 0 ) ) ) ) * NVL( curr.minimum_accountable_unit, POWER( 10, NVL( - curr.precision, 0 ) ) )  		AS inv_inventory_value,  		ROUND( SUM( layer.material_cost * NVL( qty.transaction_quantity, 0 ) ) / NVL( curr.minimum_accountable_unit, POWER( 10, NVL( - curr.precision, 0 ) ) ) ) * NVL( curr.minimum_accountable_unit, POWER( 10, NVL( - curr.precision, 0 ) ) )  		AS inv_material_value,  		ROUND( SUM( layer.material_overhead_cost * NVL( qty.transaction_quantity, 0 ) ) / NVL( curr.minimum_accountable_unit, POWER( 10, NVL( - curr.precision, 0 ) ) ) ) * NVL( curr.minimum_accountable_unit, POWER( 10, NVL( - curr.precision, 0 ) ) )  		AS inv_material_overhead_value,  		ROUND( SUM( layer.resource_cost * NVL( qty.transaction_quantity, 0 ) ) / NVL( curr.minimum_accountable_unit, POWER( 10, NVL( - curr.precision, 0 ) ) ) ) * NVL( curr.minimum_accountable_unit, POWER( 10, NVL( - curr.precision, 0 ) ) )  		AS inv_resource_value,  		ROUND( SUM( layer.outside_processing_cost * NVL( qty.transaction_quantity, 0 ) ) / NVL( curr.minimum_accountable_unit, POWER( 10, NVL( - curr.precision, 0 ) ) ) ) * NVL( curr.minimum_accountable_unit, POWER( 10, NVL( - curr.precision, 0 ) ) )  		AS inv_outside_processing_value,  		ROUND( SUM( (layer.overhead_cost) * NVL( qty.transaction_quantity, 0 ) ) / NVL( curr.minimum_accountable_unit, POWER( 10, NVL( - curr.precision, 0 ) ) ) ) * NVL( curr.minimum_accountable_unit, POWER( 10, NVL( - curr.precision, 0 ) ) )  		AS inv_overhead_value  	FROM pa_projects_all pp, cst_cost_groups grp, cst_quantity_layers layer, mtl_onhand_quantities_detail qty, mtl_item_locations loc,  		pjm_project_parameters para, mtl_parameters mp, hr_all_organization_units_tl org, mtl_system_items_b item,  		hr_organization_information orginfo, gl_sets_of_books sob, fnd_currencies curr             WHERE pp.start_date >= NVL(to_date('2021/12/12','RRRR/MM/DD HH24:MI:SS'), pp.start_date)       AND pp.project_id = para.project_id         AND PP.PROJECT_TYPE <> 'AWARD_PROJECT'        AND PP.TEMPLATE_FLAG = 'N'         AND item.inventory_item_id = layer.inventory_item_id  		AND item.organization_id = layer.organization_id  		AND qty.inventory_item_id = layer.inventory_item_id  		AND qty.organization_id = layer.organization_id  		AND loc.inventory_location_id = qty.locator_id  		AND loc.organization_id = qty.organization_id  		AND loc.project_id = para.project_id  		AND para.organization_id = loc.organization_id  		AND para.organization_id = org.organization_id  		AND para.costing_group_id = layer.cost_group_id  		AND grp.cost_group_id = layer.cost_group_id  		AND mp.organization_id = para.organization_id  		AND mp.primary_cost_method IN ( 2, 5, 6 )  		AND orginfo.organization_id = org.organization_id  		AND orginfo.org_information_context = 'Accounting Information'  		AND TO_CHAR(sob.set_of_books_id) = orginfo.org_information1  		AND curr.currency_code = sob.currency_code            	GROUP BY grp.cost_group, grp.description, layer.cost_group_id, layer.item_cost, para.project_id, item.segment1,  	qty.inventory_item_id, org.name, para.organization_id, curr.currency_code, curr.precision,  	curr.minimum_accountable_unit, curr.extended_precision, org.language  	UNION ALL  	SELECT    'PROJ_INV_VAL_NCG' || '-' || loc.project_id || '-' || mp.organization_id || '-' || qty.inventory_item_id || '-' || 'NO_COST_GROUP' AS ecc_spec_id, 'PROJ_INV_VAL' 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( cic.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( mp.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( item.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ,  		NVL( orginfo.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( sob.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , 		NVL( curr.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ) ) AS last_update_date,  		loc.project_id AS project_id, to_number(NULL) AS cost_group_id, NULL AS cost_group_name,  		NULL AS cost_group_description, mp.organization_id AS organization_id, org.name AS organization_name,  		qty.inventory_item_id AS inventory_item_id, item.segment1 AS item_number, curr.currency_code AS currency_code,  		SUM( NVL( qty.transaction_quantity, 0 ) ) AS quantity_onhand,  		ROUND( cic.item_cost, NVL( curr.extended_precision, NVL( curr.precision, 0 ) ) ) AS unit_cost,  		ROUND(SUM(cic.item_cost * NVL( qty.transaction_quantity, 0)) / NVL(curr.minimum_accountable_unit, POWER(10,NVL( - curr.precision, 0 )))) * NVL( curr.minimum_accountable_unit, POWER(10, NVL( - curr.precision, 0 ))) AS tot_inventory_value,  		ROUND(SUM(cic.material_cost * NVL( qty.transaction_quantity, 0 ) ) / NVL( curr.minimum_accountable_unit, POWER( 10, NVL( - curr.precision, 0 )))) * NVL( curr.minimum_accountable_unit, POWER(10, NVL( - curr.precision, 0))) AS tot_material_value,  		ROUND(SUM(cic.material_overhead_cost * NVL( qty.transaction_quantity, 0 )) / NVL( curr.minimum_accountable_unit, POWER(10, NVL( - curr.precision, 0 )))) * NVL( curr.minimum_accountable_unit, POWER(10, NVL( - curr.precision, 0)))  		AS tot_material_overhead_value,  		ROUND(SUM(cic.resource_cost * NVL( qty.transaction_quantity, 0 ) ) / NVL( curr.minimum_accountable_unit, POWER( 10, NVL( - curr.precision, 0 )))) * NVL( curr.minimum_accountable_unit, POWER(10, NVL( - curr.precision, 0))) AS tot_resource_value,  		ROUND(SUM(cic.outside_processing_cost * NVL( qty.transaction_quantity, 0 ) ) / NVL( curr.minimum_accountable_unit, POWER( 10, NVL( - curr.precision, 0)))) * NVL( curr.minimum_accountable_unit, POWER(10, NVL( - curr.precision, 0 )))  		AS tot_outside_processing_value,  		ROUND(SUM((cic.overhead_cost) * NVL( qty.transaction_quantity, 0 ) ) / NVL( curr.minimum_accountable_unit, POWER( 10, NVL( - curr.precision, 0 )))) * NVL( curr.minimum_accountable_unit, POWER(10, NVL( - curr.precision, 0 ))) AS tot_overhead_value  	FROM pa_projects_all pp, mtl_onhand_quantities qty, mtl_item_locations loc, cst_item_costs cic, mtl_parameters mp,  		hr_all_organization_units_tl org, mtl_system_items_b item, hr_organization_information orginfo,        pjm_project_parameters para,  		gl_sets_of_books sob, fnd_currencies curr             WHERE pp.start_date >= NVL(to_date('2021/12/12','RRRR/MM/DD HH24:MI:SS'), pp.start_date)       AND pp.project_id = para.project_id         AND PP.PROJECT_TYPE <>