Query to find Trial Balance Details in Oracle

This query will help you to find details of trial balance.

 

 

/*Query to find Trial Balance Details in Oracle*/

  SELECT gjh.name Journal_name,
         gjh.period_name,
         GL.name,
         gjh.je_category category_name,
         gjh.je_source source_name,
         gjb.name batch_name,
         GCC.SEGMENT1,
         GCC.SEGMENT2,
         GCC.SEGMENT3,
         GCC.SEGMENT4,
         GCC.SEGMENT5,
         GCC.SEGMENT6,
         SUM (NVL (GJL.ACCOUNTED_DR, 0)) ACCOUNTED_DR,
         SUM (NVL (GJL.ACCOUNTED_CR, 0)) ACCOUNTED_CR,
         SUM (NVL (GJL.ACCOUNTED_DR, 0) - NVL (GJL.ACCOUNTED_CR, 0)) BALANCE
    FROM gl_je_headers gjh,
         gl_je_lines gjl,
         gl_ledgers gl,
         gl_code_combinations gcc,
         GL_JE_BATCHES GJB
   WHERE     gjl.je_header_id = gjh.je_header_id
         AND gjh.je_batch_id = gjb.je_batch_id
         AND gjl.code_combination_id = gcc.code_combination_id
         AND gjh.ledger_id = gl.ledger_id
         AND gjh.status = 'P'
         AND gjh.actual_flag = 'A'
         AND gjh.period_name = :P_Period_Name
         AND GL.LEDGER_ID = :P_Ledger
         AND GCC.SEGMENT4 = :P_GL_Code
GROUP BY GCC.SEGMENT1,
         GCC.SEGMENT2,
         GCC.SEGMENT3,
         GCC.SEGMENT4,
         GCC.SEGMENT5,
         GCC.SEGMENT6,
         GCC.SEGMENT7,
         gjh.je_category,
         gjh.je_source,
         gjb.name,
         gjh.name,
         gjh.period_name,
         GL.name

You can find me on Fiverr & LinkedIn

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

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