Query to find All Miscellaneous Transaction in Oracle

This query will help you to find all Miscellaneous Transaction in Oracle Apps. 

 
 

 /*Query to find All Miscellaneous Transaction in Oracle*/

 SELECT (SELECT NAME
            FROM GL_LEDGERS G, ORG_ORGANIZATION_DEFINITIONS OOD
           WHERE     G.LEDGER_ID = OOD.SET_OF_BOOKS_ID
                 AND OOD.ORGANIZATION_ID = MMT.ORGANIZATION_ID
                 AND G.LEDGER_CATEGORY_CODE = 'PRIMARY')
            LEDGER,
         (SELECT DISTINCT OPERATING_UNIT_NAME
            FROM WBI_INV_ORG_DETAIL
           WHERE INV_ORGANIZATION_ID = MMT.ORGANIZATION_ID)
            OU_NAME,
         MMT.ORGANIZATION_ID,
         WXMD.ORGANIZATION_CODE,
         WXMD.ORGANIZATION_NAME,
         MMT.TRANSACTION_TYPE_ID,
         (SELECT TRANSACTION_TYPE_NAME
            FROM MTL_TRANSACTION_TYPES
           WHERE TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID)
            TRANSACTION_TYPE_NAME,
         (SELECT MEANING
            FROM FND_LOOKUP_VALUES_VL
           WHERE LOOKUP_CODE = MMT.transaction_action_id
                 AND lookup_type = 'MTL_TRANSACTION_ACTION')
            MEANING,
         MMT.TRANSACTION_ID--,DECODE(MMT.TRANSACTION_TYPE_ID,21,(SELECT SHIPMENT_NUMBER FROM MTL_MATERIAL_TRANSACTIONS WHERE TRANSACTION_SOURCE_TYPE_ID=13 AND TRANSACTION_TYPE_ID=21
                           --    AND TRANSACTION_ID=MMT.TRANSACTION_ID),XX_INV_PKG.XX_INV_TRANS_NO(MMT.TRANSACTION_ID)) TRANSACTION_NO
         ,
         MMT.TRANSACTION_DATE,
         MMT.INVENTORY_ITEM_ID,
         WXMD.ITEM_CODE,
         WXMD.ITEM_DESC,
         ABS (MMT.TRANSACTION_QUANTITY) TRANSACTION_QUANTITY,
         MMT.SUBINVENTORY_CODE,
         MMT.CREATED_BY TRANSACT_BY,
         XX_INV_PKG.XXGET_ENAME (MMT.CREATED_BY) TRANSACT_BY_NAME,
         MMT.TRANSACTION_REFERENCE REMARKS,
         MMT.DISTRIBUTION_ACCOUNT_ID,
         (SELECT    SEGMENT1
                 || '.'
                 || SEGMENT2
                 || '.'
                 || SEGMENT3
                 || '.'
                 || SEGMENT4
                 || '.'
                 || SEGMENT5
                 || '.'
                 || SEGMENT6
                 || '.'
                 || SEGMENT7
                 || '.'
                 || SEGMENT8
                 || '.'
                 || SEGMENT9
                 || '.'
                 || SEGMENT10
            FROM GL_CODE_COMBINATIONS
           WHERE CODE_COMBINATION_ID = MMT.DISTRIBUTION_ACCOUNT_ID)
            ACCOUNT_COMBINATION,
         (XX_AP_PKG.GET_ACCOUNT_DESC_FROM_CCID (MMT.DISTRIBUTION_ACCOUNT_ID))
            ACCOUNT_NAME,
         XX_AP_PKG.GET_SUB_ACCOUNT_DESC_FROM_CCID (
            TO_NUMBER (MMT.DISTRIBUTION_ACCOUNT_ID))
            SUB_ACCOUNT
    FROM MTL_MATERIAL_TRANSACTIONS MMT, WBI_XXKBGITEM_MT_D WXMD
   WHERE (   MMT.transaction_source_type_id = 3
          OR MMT.transaction_source_type_id = 6
          OR MMT.transaction_source_type_id = 13
          OR MMT.transaction_source_type_id >= 100)
         AND (MMT.transaction_action_id = 1 OR MMT.transaction_action_id = 27)
         AND MMT.transaction_type_id NOT IN (66, 67, 68, 95, 96)
         AND WXMD.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
         AND WXMD.ORGANIZATION_ID = MMT.ORGANIZATION_ID
         AND MMT.ORGANIZATION_ID = NVL (:P_ORG_ID, MMT.ORGANIZATION_ID)
         AND MMT.TRANSACTION_TYPE_ID =
                NVL (:P_TRANSACTION_TYPE_ID, MMT.TRANSACTION_TYPE_ID)
         AND TO_DATE (MMT.TRANSACTION_DATE) BETWEEN NVL (
                                                       TO_DATE (:P_FROM_DATE),
                                                       TO_DATE (
                                                          MMT.TRANSACTION_DATE))
                                                AND NVL (
                                                       TO_DATE (:P_TO_DATE),
                                                       TO_DATE (
                                                          MMT.TRANSACTION_DATE))
         AND WXMD.ITEM_CODE = NVL (:P_ITEM_CODE, WXMD.ITEM_CODE)
         AND MMT.SUBINVENTORY_CODE =
                NVL (:P_SUBINVENTORY_CODE, MMT.SUBINVENTORY_CODE)
         AND MMT.CREATED_BY = NVL (:P_CREATED_BY, MMT.CREATED_BY)
ORDER BY LEDGER, MMT.TRANSACTION_ACTION_ID, MMT.TRANSACTION_ID

 

You can find me on Fiverr & LinkedIn