View: PJM_PROJECT_ONHAND_VALUE_V

Product:	PJM - Project Manufacturing
Description:	Project inventory onhand valuation for the Web Inquiry.
Implementation/DBA Data:	APPS.PJM_PROJECT_ONHAND_VALUE_V

View Text

SELECT PARA.PROJECT_ID
, LAYER.COST_GROUP_ID
, GRP.COST_GROUP
, GRP.DESCRIPTION
, PARA.ORGANIZATION_ID
, ORG.NAME
, QTY.INVENTORY_ITEM_ID
, ITEM.CONCATENATED_SEGMENTS
, SUM( NVL( QTY.TRANSACTION_QUANTITY
, 0 ) )
, ROUND( LAYER.ITEM_COST
, NVL( CURR.EXTENDED_PRECISION
, NVL( CURR.PRECISION
, 0 ) ) )
, 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_CACHE.GET_FORMAT_MASK( CURR.CURRENCY_CODE
, 40 ) )
, 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_CACHE.GET_FORMAT_MASK( CURR.CURRENCY_CODE
, 40 ) )
, 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_CACHE.GET_FORMAT_MASK( CURR.CURRENCY_CODE
, 40 ) )
, 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_CACHE.GET_FORMAT_MASK( CURR.CURRENCY_CODE
, 40 ) )
, 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_CACHE.GET_FORMAT_MASK( CURR.CURRENCY_CODE
, 40 ) )
, 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_CACHE.GET_FORMAT_MASK( CURR.CURRENCY_CODE
, 40 ) )
FROM 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_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 = LOC.ORGANIZATION_ID
AND PARA.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND ORG.LANGUAGE = USERENV('LANG')
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.CONCATENATED_SEGMENTS
, QTY.INVENTORY_ITEM_ID
, ORG.NAME
, PARA.ORGANIZATION_ID
, CURR.CURRENCY_CODE
, CURR.PRECISION
, CURR.MINIMUM_ACCOUNTABLE_UNIT
, CURR.EXTENDED_PRECISION UNION ALL SELECT LOC.PROJECT_ID
, TO_NUMBER(NULL)
, NULL
, NULL
, MP.ORGANIZATION_ID
, ORG.NAME
, QTY.INVENTORY_ITEM_ID
, ITEM.CONCATENATED_SEGMENTS
, SUM( NVL( QTY.TRANSACTION_QUANTITY
, 0 ) )
, ROUND( CIC.ITEM_COST
, NVL( CURR.EXTENDED_PRECISION
, NVL( CURR.PRECISION
, 0 ) ) )
, TO_CHAR( 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 ) ) )
, FND_CURRENCY_CACHE.GET_FORMAT_MASK( CURR.CURRENCY_CODE
, 40 ) )
, TO_CHAR( 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 ) ) )
, FND_CURRENCY_CACHE.GET_FORMAT_MASK( CURR.CURRENCY_CODE
, 40 ) )
, TO_CHAR( 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 ) ) )
, FND_CURRENCY_CACHE.GET_FORMAT_MASK( CURR.CURRENCY_CODE
, 40 ) )
, TO_CHAR( 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 ) ) )
, FND_CURRENCY_CACHE.GET_FORMAT_MASK( CURR.CURRENCY_CODE
, 40 ) )
, TO_CHAR( 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 ) ) )
, FND_CURRENCY_CACHE.GET_FORMAT_MASK( CURR.CURRENCY_CODE
, 40 ) )
, TO_CHAR( 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 ) ) )
, FND_CURRENCY_CACHE.GET_FORMAT_MASK( CURR.CURRENCY_CODE
, 40 ) )
FROM MTL_ONHAND_QUANTITIES QTY
, MTL_ITEM_LOCATIONS LOC
, CST_ITEM_COSTS CIC
, MTL_PARAMETERS MP
, HR_ALL_ORGANIZATION_UNITS_TL ORG
, MTL_SYSTEM_ITEMS_KFV ITEM
, HR_ORGANIZATION_INFORMATION ORGINFO
, GL_SETS_OF_BOOKS SOB
, FND_CURRENCIES CURR
WHERE MP.PRIMARY_COST_METHOD = 1
AND CIC.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND CIC.COST_TYPE_ID = 1
AND ITEM.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
AND ITEM.ORGANIZATION_ID = CIC.ORGANIZATION_ID
AND QTY.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
AND QTY.ORGANIZATION_ID = ITEM.ORGANIZATION_ID
AND LOC.INVENTORY_LOCATION_ID = QTY.LOCATOR_ID
AND LOC.ORGANIZATION_ID = QTY.ORGANIZATION_ID
AND ORG.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND ORG.LANGUAGE = USERENV('LANG')
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 CIC.ITEM_COST
, LOC.PROJECT_ID
, ITEM.CONCATENATED_SEGMENTS
, QTY.INVENTORY_ITEM_ID
, ORG.NAME
, MP.ORGANIZATION_ID
, CURR.CURRENCY_CODE
, CURR.PRECISION
, CURR.MINIMUM_ACCOUNTABLE_UNIT
, CURR.EXTENDED_PRECISION

Columns