Oracle Inventory Transaction Query

To get the whole Oracle Inventory transaction details , below query can be used.

/* Formatted on 10/10/2016 12:53:40 PM (QP5 v5.126.903.23003) */

SELECT   MMT.ORGANIZATION_ID,

         MMT.INVENTORY_ITEM_ID ITEM_ID,

         MMT.SUBINVENTORY_CODE SUBINVENTORY_CODE,

         MMT.TRANSACTION_DATE,

         CID.TRANSACTION_TYPE_NAME,

         CID.TRANSACTION_SOURCE_TYPE_NAME,

         MMT.PRIMARY_QUANTITY,

         CID.UNIT_COST,

         CID.BASE_TRANSACTION_VALUE,

         REFERENCE_ACCOUNT,

         DISTRIBUTION_ACCOUNT_ID,

         ENCUMBRANCE_ACCOUNT,

         SHIPMENT_NUMBER,

         MMT.LOCATOR_ID,

         MMT.TRANSACTION_UOM,

         MMT.TRANSACTION_REFERENCE,

         DECODE (

            CID.TRANSACTION_SOURCE_TYPE_NAME,

            'Move order',

            (SELECT   DISTINCT

                      CONCAT (CONCAT (REQUEST_NUMBER, '--'), DESCRIPTION)

               FROM   mtl_txn_request_headers mtrh

              WHERE   HEADER_ID = mmt.TRANSACTION_SOURCE_ID AND ROWNUM = 1),

            'Purchase order',

            (SELECT   DISTINCT CONCAT (CONCAT (segment1, '--'), COMMENTS)

               FROM   po_headers_all pha

              WHERE   po_header_id = mmt.TRANSACTION_SOURCE_ID AND ROWNUM = 1),

            'Job or Schedule',

            (SELECT   DISTINCT

                      CONCAT (CONCAT (WIP_ENTITY_NAME, '--'), DESCRIPTION)

               FROM   wip_entities

              WHERE   WIP_ENTITY_ID = mmt.TRANSACTION_SOURCE_ID

                      AND ROWNUM = 1),

            CAST (mmt.SOURCE_CODE AS VARCHAR2 (100))

         )

            TRANSACTION_DESC

  FROM   MTL_MATERIAL_TRANSACTIONS MMT, CST_INV_DISTRIBUTION_V CID

 WHERE   MMT.TRANSACTION_ID = CID.TRANSACTION_ID

         AND CID.ACCOUNTING_LINE_TYPE = 1

-- AND MMT.INVENTORY_ITEM_ID IN (41041, 42496)

--and TRANSACTION_SOURCE_TYPE_NAME='Purchase order'


Enjoy and Stay Well.