VIEW: APPS.ICX_EDM_COST_GROUP_INV_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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