View: MTL_ONHAND_QTY_COST_V

Product:	INV - Inventory
Description:	- Retrofitted
Implementation/DBA Data:	APPS.MTL_ONHAND_QTY_COST_V

View Text

SELECT MOH.INVENTORY_ITEM_ID
, MOH.ORGANIZATION_ID
, MOH.DATE_RECEIVED
, MOH.LAST_UPDATE_DATE
, MOH.LAST_UPDATED_BY
, MOH.CREATION_DATE
, MOH.CREATED_BY
, MOH.LAST_UPDATE_LOGIN
, MOH.PRIMARY_TRANSACTION_QUANTITY TRANSACTION_QUANTITY
, MOH.SUBINVENTORY_CODE
, REVISION
, LOCATOR_ID
, CREATE_TRANSACTION_ID
, UPDATE_TRANSACTION_ID
, LOT_NUMBER
, CCICV.ITEM_COST
FROM MTL_ONHAND_QUANTITIES_DETAIL MOH
, CST_CG_ITEM_COSTS_VIEW CCICV
, MTL_PARAMETERS MP
WHERE MOH.LOCATOR_ID IS NULL
AND CCICV.ORGANIZATION_ID = MOH.ORGANIZATION_ID
AND MP.ORGANIZATION_ID = MOH.ORGANIZATION_ID
AND CCICV.INVENTORY_ITEM_ID = MOH.INVENTORY_ITEM_ID
AND CCICV.COST_GROUP_ID = DECODE( MP.PRIMARY_COST_METHOD
, 1
, 1
, NVL(MOH.COST_GROUP_ID
, 1))
AND MOH.IS_CONSIGNED = 2 UNION ALL SELECT MOH.INVENTORY_ITEM_ID
, MOH.ORGANIZATION_ID
, MOH.DATE_RECEIVED
, MOH.LAST_UPDATE_DATE
, MOH.LAST_UPDATED_BY
, MOH.CREATION_DATE
, MOH.CREATED_BY
, MOH.LAST_UPDATE_LOGIN
, MOH.PRIMARY_TRANSACTION_QUANTITY TRANSACTION_QUANTITY
, MOH.SUBINVENTORY_CODE
, REVISION
, LOCATOR_ID
, CREATE_TRANSACTION_ID
, UPDATE_TRANSACTION_ID
, LOT_NUMBER
, 0 ITEM_COST
FROM MTL_ONHAND_QUANTITIES_DETAIL MOH
WHERE MOH.LOCATOR_ID IS NULL
AND NOT EXISTS (SELECT 'X'
FROM CST_CG_ITEM_COSTS_VIEW CCICV
, MTL_PARAMETERS MP
WHERE CCICV.ORGANIZATION_ID = MOH.ORGANIZATION_ID AND MP.ORGANIZATION_ID = MOH.ORGANIZATION_ID
AND CCICV.INVENTORY_ITEM_ID = MOH.INVENTORY_ITEM_ID
AND CCICV.COST_GROUP_ID = DECODE( MP.PRIMARY_COST_METHOD
, 1
, 1
, NVL(MOH.COST_GROUP_ID
, 1)))
AND MOH.IS_CONSIGNED = 2 UNION ALL SELECT MOH.INVENTORY_ITEM_ID
, MOH.ORGANIZATION_ID
, MOH.DATE_RECEIVED
, MOH.LAST_UPDATE_DATE
, MOH.LAST_UPDATED_BY
, MOH.CREATION_DATE
, MOH.CREATED_BY
, MOH.LAST_UPDATE_LOGIN
, MOH.PRIMARY_TRANSACTION_QUANTITY TRANSACTION_QUANTITY
, MOH.SUBINVENTORY_CODE
, REVISION
, LOCATOR_ID
, CREATE_TRANSACTION_ID
, UPDATE_TRANSACTION_ID
, LOT_NUMBER
, CCICV.ITEM_COST
FROM MTL_ONHAND_QUANTITIES_DETAIL MOH
, MTL_ITEM_LOCATIONS MIL
, CST_CG_ITEM_COSTS_VIEW CCICV
, MTL_PARAMETERS MP
WHERE MOH.LOCATOR_ID IS NOT NULL
AND MIL.ORGANIZATION_ID = MOH.ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID = MOH.LOCATOR_ID
AND MIL.PROJECT_ID IS NULL
AND CCICV.ORGANIZATION_ID = MOH.ORGANIZATION_ID
AND MP.ORGANIZATION_ID = MOH.ORGANIZATION_ID
AND CCICV.INVENTORY_ITEM_ID = MOH.INVENTORY_ITEM_ID
AND CCICV.COST_GROUP_ID = DECODE( MP.PRIMARY_COST_METHOD
, 1
, 1
, NVL(MOH.COST_GROUP_ID
, 1))
AND MOH.IS_CONSIGNED = 2 UNION ALL SELECT MOH.INVENTORY_ITEM_ID
, MOH.ORGANIZATION_ID
, MOH.DATE_RECEIVED
, MOH.LAST_UPDATE_DATE
, MOH.LAST_UPDATED_BY
, MOH.CREATION_DATE
, MOH.CREATED_BY
, MOH.LAST_UPDATE_LOGIN
, MOH.PRIMARY_TRANSACTION_QUANTITY TRANSACTION_QUANTITY
, MOH.SUBINVENTORY_CODE
, REVISION
, LOCATOR_ID
, CREATE_TRANSACTION_ID
, UPDATE_TRANSACTION_ID
, LOT_NUMBER
, 0
FROM MTL_ONHAND_QUANTITIES_DETAIL MOH
, MTL_ITEM_LOCATIONS MIL
WHERE MOH.LOCATOR_ID IS NOT NULL
AND MIL.ORGANIZATION_ID = MOH.ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID = MOH.LOCATOR_ID
AND MIL.PROJECT_ID IS NULL
AND NOT EXISTS (SELECT 'X'
FROM CST_CG_ITEM_COSTS_VIEW CCICV
, MTL_PARAMETERS MP
WHERE CCICV.ORGANIZATION_ID = MOH.ORGANIZATION_ID AND MP.ORGANIZATION_ID = MOH.ORGANIZATION_ID
AND CCICV.INVENTORY_ITEM_ID = MOH.INVENTORY_ITEM_ID
AND CCICV.COST_GROUP_ID = DECODE( MP.PRIMARY_COST_METHOD
, 1
, 1
, NVL(MOH.COST_GROUP_ID
, 1)))
AND MOH.IS_CONSIGNED = 2 UNION ALL SELECT MOH.INVENTORY_ITEM_ID
, MOH.ORGANIZATION_ID
, MOH.DATE_RECEIVED
, MOH.LAST_UPDATE_DATE
, MOH.LAST_UPDATED_BY
, MOH.CREATION_DATE
, MOH.CREATED_BY
, MOH.LAST_UPDATE_LOGIN
, MOH.PRIMARY_TRANSACTION_QUANTITY TRANSACTION_QUANTITY
, MOH.SUBINVENTORY_CODE
, REVISION
, LOCATOR_ID
, CREATE_TRANSACTION_ID
, UPDATE_TRANSACTION_ID
, LOT_NUMBER
, CCICV.ITEM_COST
FROM MTL_ONHAND_QUANTITIES_DETAIL MOH
, MTL_ITEM_LOCATIONS MIL
, MRP_PROJECT_PARAMETERS MRP
, CST_CG_ITEM_COSTS_VIEW CCICV
, MTL_PARAMETERS MP
WHERE MOH.LOCATOR_ID IS NOT NULL
AND MIL.ORGANIZATION_ID = MOH.ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID = MOH.LOCATOR_ID
AND MP.ORGANIZATION_ID = MOH.ORGANIZATION_ID
AND MIL.PROJECT_ID IS NOT NULL
AND MRP.ORGANIZATION_ID = MOH.ORGANIZATION_ID
AND MRP.PROJECT_ID = MIL.PROJECT_ID
AND CCICV.ORGANIZATION_ID = MOH.ORGANIZATION_ID
AND CCICV.INVENTORY_ITEM_ID = MOH.INVENTORY_ITEM_ID
AND CCICV.COST_GROUP_ID = DECODE(MP.PRIMARY_COST_METHOD
, 1
, 1
, MRP.COSTING_GROUP_ID)
AND MOH.IS_CONSIGNED = 2 UNION ALL SELECT MOH.INVENTORY_ITEM_ID
, MOH.ORGANIZATION_ID
, MOH.DATE_RECEIVED
, MOH.LAST_UPDATE_DATE
, MOH.LAST_UPDATED_BY
, MOH.CREATION_DATE
, MOH.CREATED_BY
, MOH.LAST_UPDATE_LOGIN
, MOH.PRIMARY_TRANSACTION_QUANTITY TRANSACTION_QUANTITY
, MOH.SUBINVENTORY_CODE
, REVISION
, LOCATOR_ID
, CREATE_TRANSACTION_ID
, UPDATE_TRANSACTION_ID
, LOT_NUMBER
, 0
FROM MTL_ONHAND_QUANTITIES_DETAIL MOH
, MTL_ITEM_LOCATIONS MIL
, MRP_PROJECT_PARAMETERS MRP
WHERE MOH.LOCATOR_ID IS NOT NULL
AND MIL.ORGANIZATION_ID = MOH.ORGANIZATION_ID
AND MIL.INVENTORY_LOCATION_ID = MOH.LOCATOR_ID
AND MIL.PROJECT_ID IS NOT NULL
AND MRP.ORGANIZATION_ID = MOH.ORGANIZATION_ID
AND MRP.PROJECT_ID = MIL.PROJECT_ID
AND NOT EXISTS (SELECT 'X'
FROM CST_CG_ITEM_COSTS_VIEW CCICV
, MTL_PARAMETERS MP
WHERE CCICV.ORGANIZATION_ID = MOH.ORGANIZATION_ID
AND CCICV.INVENTORY_ITEM_ID = MOH.INVENTORY_ITEM_ID
AND MP.ORGANIZATION_ID = CCICV.ORGANIZATION_ID
AND CCICV.COST_GROUP_ID = DECODE(MP.PRIMARY_COST_METHOD
, 1
, 1
, MRP.COSTING_GROUP_ID))
AND MOH.IS_CONSIGNED = 2

Columns