eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.MTL_ONHAND_QTY_COST_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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