Query to find wrong move order in oracle Apps

Below query will help you to find wrong move order list from oracle apps.

if you are facing any problem to understand any part of this query then please feel free to comment below. I'll try my best to help you in this regards.

 

/*Query to find wrong move order in oracle EBS - Oracle Apps*/

  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 = MTRL.ORGANIZATION_ID)
            OU_NAME,
         (SELECT DISTINCT INVENTORY_ORGANIZATION_NAME
            FROM WBI_INV_ORG_DETAIL
           WHERE INV_ORGANIZATION_ID = MTRL.ORGANIZATION_ID)
            ORG_NAME,
         (SELECT TRANSACTION_TYPE_NAME
            FROM MTL_TRANSACTION_TYPES
           WHERE TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID)
            TRANSACTION_TYPE_NAME,
         MTRL.DATE_REQUIRED MO_DATE,
         MTRL.REQUEST_NUMBER MO_NO,
         WXMD.ITEM_CODE ITEM_CODE,
         WXMD.ITEM_DESC ITEM_DESC,
         MTRL.QUANTITY MO_QTY,
         ABS (MMT.TRANSACTION_QUANTITY) ISSUE_QTY,
         MMT.SUBINVENTORY_CODE SUBINVENTORY,
         MTRL.TO_ACCOUNT_ID,
         (SELECT    SEGMENT1
                 || '.'
                 || SEGMENT2
                 || '.'
                 || SEGMENT3
                 || '.'
                 || SEGMENT4
                 || '.'
                 || SEGMENT5
                 || '.'
                 || SEGMENT6
                 || '.'
                 || SEGMENT7
                 || '.'
                 || SEGMENT8
            FROM GL_CODE_COMBINATIONS
           WHERE CODE_COMBINATION_ID = MTRL.TO_ACCOUNT_ID)
            ACCOUNT_COMBINATION,
         (XX_AP_PKG.GET_ACCOUNT_DESC_FROM_CCID (MTRL.TO_ACCOUNT_ID))
            ACCOUNT_NAME,
         XX_INV_PKG.XXGET_ENAME (TO_CHAR (:P_USER)) USER_NAME,
         XX_INV_PKG.XXGET_ENAME (MTRL.CREATED_BY) PREPARED_BY,
         XX_INV_PKG.XXGET_ENAME (MMT.CREATED_BY) ISSUED_BY
    FROM MTL_MATERIAL_TRANSACTIONS MMT,
         MTL_TXN_REQUEST_LINES_V MTRL,
         WBI_XXKBGITEM_MT_D WXMD
   WHERE     MMT.TRANSACTION_SET_ID = MTRL.TRANSACTION_HEADER_ID
         AND MTRL.TO_ACCOUNT_ID IS NOT NULL
         ------------------------------------------------------------------------------------------------
         AND WXMD.ORGANIZATION_ID = MMT.ORGANIZATION_ID
         AND WXMD.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
         AND WXMD.ORGANIZATION_ID = MTRL.ORGANIZATION_ID
         AND WXMD.INVENTORY_ITEM_ID = MTRL.INVENTORY_ITEM_ID
         ------------------------------------------------------------------------------------------------
         AND MTRL.ORGANIZATION_ID = NVL (:P_ORG, MTRL.ORGANIZATION_ID)
         AND MTRL.REQUEST_NUMBER = NVL (:P_MO_NUMBER, MTRL.REQUEST_NUMBER)
         AND TRUNC (MTRL.DATE_REQUIRED) BETWEEN NVL (
                                                   :P_FROM_DATE,
                                                   TRUNC (MTRL.DATE_REQUIRED))
                                            AND NVL (
                                                   :P_TO_DATE,
                                                   TRUNC (MTRL.DATE_REQUIRED))
         AND MTRL.INVENTORY_ITEM_ID = NVL (:P_ITEM_ID, MTRL.INVENTORY_ITEM_ID)
ORDER BY MTRL.DATE_REQUIRED, MTRL.REQUEST_NUMBER, MTRL.LINE_NUMBER

 

You can find me on Fiverr & LinkedIn