VIEW: APPS.ICX_COST_GRP_INV_VALUE_V

Source

View Text - Preformatted

SELECT LAYER.COST_GROUP_ID , GRP.COST_GROUP COST_GROUP , PARA.PROJECT_ID , LOC.TASK_ID , PARA.ORGANIZATION_ID , QTY.INVENTORY_ITEM_ID , QTY.LOT_NUMBER , SUM((LAYER.ITEM_COST) * NVL(QTY.TRANSACTION_QUANTITY, 0)) TOT_INVENTORY_VALUE , SUM(LAYER.PL_MATERIAL + LAYER.TL_MATERIAL) TOT_MATERIAL_VALUE , SUM(LAYER.PL_MATERIAL_OVERHEAD + LAYER.TL_MATERIAL_OVERHEAD) TOT_MATERIAL_OVERHEAD_VALUE , SUM(LAYER.PL_RESOURCE + LAYER.TL_RESOURCE) TOT_RESOURCE_VALUE , SUM(LAYER.PL_OUTSIDE_PROCESSING + LAYER.TL_OUTSIDE_PROCESSING) TOT_OUTSIDE_PROCESSING_VALUE , SUM(LAYER.PL_OVERHEAD + LAYER.TL_OVERHEAD) TOT_OVERHEAD_VALUE FROM CST_COST_GROUPS GRP , CST_QUANTITY_LAYERS LAYER , MTL_ONHAND_QUANTITIES QTY , MTL_ITEM_LOCATIONS LOC , MRP_PROJECT_PARAMETERS PARA WHERE QTY.LOCATOR_ID = LOC.INVENTORY_LOCATION_ID(+) AND PARA.PROJECT_ID = LOC.PROJECT_ID AND QTY.ORGANIZATION_ID = PARA.ORGANIZATION_ID AND QTY.INVENTORY_ITEM_ID = LAYER.INVENTORY_ITEM_ID AND QTY.ORGANIZATION_ID = LAYER.ORGANIZATION_ID AND PARA.COSTING_GROUP_ID = LAYER.COST_GROUP_ID AND PARA.COSTING_GROUP_ID = GRP.COST_GROUP_ID GROUP BY LAYER.COST_GROUP_ID , GRP.COST_GROUP , PARA.PROJECT_ID , LOC.TASK_ID , PARA.ORGANIZATION_ID , QTY.INVENTORY_ITEM_ID , QTY.LOT_NUMBER

View Text - HTML Formatted

SELECT LAYER.COST_GROUP_ID
, GRP.COST_GROUP COST_GROUP
, PARA.PROJECT_ID
, LOC.TASK_ID
, PARA.ORGANIZATION_ID
, QTY.INVENTORY_ITEM_ID
, QTY.LOT_NUMBER
, SUM((LAYER.ITEM_COST) * NVL(QTY.TRANSACTION_QUANTITY
, 0)) TOT_INVENTORY_VALUE
, SUM(LAYER.PL_MATERIAL + LAYER.TL_MATERIAL) TOT_MATERIAL_VALUE
, SUM(LAYER.PL_MATERIAL_OVERHEAD + LAYER.TL_MATERIAL_OVERHEAD) TOT_MATERIAL_OVERHEAD_VALUE
, SUM(LAYER.PL_RESOURCE + LAYER.TL_RESOURCE) TOT_RESOURCE_VALUE
, SUM(LAYER.PL_OUTSIDE_PROCESSING + LAYER.TL_OUTSIDE_PROCESSING) TOT_OUTSIDE_PROCESSING_VALUE
, SUM(LAYER.PL_OVERHEAD + LAYER.TL_OVERHEAD) TOT_OVERHEAD_VALUE
FROM CST_COST_GROUPS GRP
, CST_QUANTITY_LAYERS LAYER
, MTL_ONHAND_QUANTITIES QTY
, MTL_ITEM_LOCATIONS LOC
, MRP_PROJECT_PARAMETERS PARA
WHERE QTY.LOCATOR_ID = LOC.INVENTORY_LOCATION_ID(+)
AND PARA.PROJECT_ID = LOC.PROJECT_ID
AND QTY.ORGANIZATION_ID = PARA.ORGANIZATION_ID
AND QTY.INVENTORY_ITEM_ID = LAYER.INVENTORY_ITEM_ID
AND QTY.ORGANIZATION_ID = LAYER.ORGANIZATION_ID
AND PARA.COSTING_GROUP_ID = LAYER.COST_GROUP_ID
AND PARA.COSTING_GROUP_ID = GRP.COST_GROUP_ID GROUP BY LAYER.COST_GROUP_ID
, GRP.COST_GROUP
, PARA.PROJECT_ID
, LOC.TASK_ID
, PARA.ORGANIZATION_ID
, QTY.INVENTORY_ITEM_ID
, QTY.LOT_NUMBER