Query to Retrive a Transaction Detail of an Inventory Item


This Query Retrieves the Data of Transaction Details of an Inventory Item on a Particular period,  Including the Lot, Serial and Cost Details.

Please Include the Master Item Name in AND MSIB.SEGMENT1 = '  '

SELECT MSIB.SEGMENT1       AS ITEM,
  CIDV.ORGANIZATION_CODE   AS ORG,
  MMT.TRANSACTION_QUANTITY AS QTY,
  MMT.TRANSACTION_DATE,
  --MMT.ATTRIBUTE14,
  MMT.SOURCE_CODE AS TRANSACTION_TYPE,
  MTLV.LOT_NUMBER,
  MTLV.GRADE_CODE,
  MTLV.EXPIRATION_DATE AS EXP_DATE,
  MUTV.SERIAL_NUMBER,
  CIDV.BASE_TRANSACTION_VALUE AS TRX_VALUE,
  CIDV.UNIT_COST
FROM MTL_MATERIAL_TRANSACTIONS MMT,
  MTL_TRANSACTION_LOT_VAL_V MTLV,
  MTL_UNIT_TRANSACTIONS_ALL_V MUTV,
  CST_INV_DISTRIBUTION_V CIDV,
  MTL_SYSTEM_ITEMS_B MSIB
WHERE MMT.ORGANIZATION_ID       = MSIB.ORGANIZATION_ID
AND MMT.INVENTORY_ITEM_ID       = MSIB.INVENTORY_ITEM_ID
AND MMT.TRANSACTION_ID          = CIDV.TRANSACTION_ID
AND MMT.TRANSACTION_ID          = MTLV.TRANSACTION_ID(+)
AND MTLV.SERIAL_TRANSACTION_ID  = MUTV.TRANSACTION_ID(+)
AND MMT.ORGANIZATION_ID         = CIDV.ORGANIZATION_ID
AND CIDV.LINE_TYPE_NAME         = 'Inv valuation'
AND MSIB.SEGMENT1               = '  '
AND TRUNC(MMT.TRANSACTION_DATE) = TRUNC(Sysdate)

1 comments:

This will not work if you are fetching only serial controlled item...

For pure serial controlled item you need to map the following

AND MMT.TRANSACTION_ID= MUTV.TRANSACTION_ID(+)

instead of

AND MTLV.SERIAL_TRANSACTION_ID = MUTV.TRANSACTION_ID(+)