View: ICX_EDM_COST_GROUP_INV_V

Product:	ICX - Oracle iProcurement
Description:	Project Inventory Onhand Value View
Implementation/DBA Data:	APPS.ICX_EDM_COST_GROUP_INV_V

View Text

SELECT LAYER.COST_GROUP_ID
, GRP.COST_GROUP
, GRP.DESCRIPTION
, PARA.PROJECT_ID
, PROJ.PROJECT_NUMBER
, NULL
, NULL
, PARA.ORGANIZATION_ID
, ORG.NAME
, QTY.INVENTORY_ITEM_ID
, ITEM.CONCATENATED_SEGMENTS ITEM_NUMBER
, NULL
, TO_CHAR(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)))
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(CURR.CURRENCY_CODE
, 40)) TOT_INVENTORY_VALUE
, TO_CHAR(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)))
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(CURR.CURRENCY_CODE
, 40)) TOT_MATERIAL_VALUE
, TO_CHAR(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)))
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(CURR.CURRENCY_CODE
, 40)) TOT_MATERIAL_OVERHEAD_VALUE
, TO_CHAR(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)))
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(CURR.CURRENCY_CODE
, 40)) TOT_RESOURCE_VALUE
, TO_CHAR(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)))
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(CURR.CURRENCY_CODE
, 40)) TOT_OUTSIDE_PROCESSING_VALUE
, TO_CHAR(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)))
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(CURR.CURRENCY_CODE
, 40)) TOT_OVERHEAD_VALUE
FROM CST_COST_GROUPS GRP
, CST_QUANTITY_LAYERS LAYER
, MTL_ONHAND_QUANTITIES QTY
, MTL_ITEM_LOCATIONS LOC
, PJM_PROJECT_PARAMETERS PARA
, PJM_PROJECTS_V PROJ
, HR_ORGANIZATION_UNITS ORG
, MTL_SYSTEM_ITEMS_KFV ITEM
, HR_ORGANIZATION_INFORMATION ORGINFO
, GL_SETS_OF_BOOKS SOB
, FND_CURRENCIES CURR
WHERE 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 = ORG.ORGANIZATION_ID
AND PARA.COSTING_GROUP_ID = LAYER.COST_GROUP_ID
AND PROJ.PROJECT_ID = PARA.PROJECT_ID
AND GRP.COST_GROUP_ID = LAYER.COST_GROUP_ID
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
, PARA.PROJECT_ID
, PROJ.PROJECT_NUMBER
, NULL
, ITEM.CONCATENATED_SEGMENTS
, QTY.INVENTORY_ITEM_ID
, ORG.NAME
, PARA.ORGANIZATION_ID
, NULL
, CURR.CURRENCY_CODE
, CURR.PRECISION
, CURR.MINIMUM_ACCOUNTABLE_UNIT

Columns