1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

GL Trial Balance Report with Source Field

Discussion in 'Oracle Financials' started by Bharat, Jul 9, 2013.

  1. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi All,

    We are running Trial Balance Report in GL. Here in this report for each account single record is generated and amounts are calculated based on given parameters and period name given. But now, we want to add Source column to this report.

    Can anyone suggest me on this..

    As Trial Balance report uses GL_BALANCES table, how can we get source column in this report.
     
  2. Tom Morello

    Tom Morello Active Member

    Messages:
    26
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    Indonesia
    Hi Bharat,

    What source column you want?

    Salam,

    Tom Morello
     
  3. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Tom,

    Column name is only Source. In GL_JE_HEADERS we can see this as JE_SOURCE. This one.

    In General, Trial Balance report will give amount for accounts as of given period name. But I want to divide the amount according to source for each account.
    Like if some 1234 account is having receivables 1000 and payables 2000 amount. In GL_BALANCES it shows as 3000 as of given month (Jun-13). But I want to display the report output as Receivables 1000 and Payables 2000.
     
  4. Tom Morello

    Tom Morello Active Member

    Messages:
    26
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    Indonesia
    Hi Bharat,

    In GL_JE_HEADERS & GL_JE_LINES you can use CODE_COMBINATION_ID for get information for GL_BALANCES.

    Salam,

    Tom Morello
     
  5. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Tom,

    Wrote query as below based on JE_HEADERS and JE_LINES. Please have a look into this. After running this query, for some accounts begin_balance and end_balance amounts are mismatched when compared with to Trial Balance Report Output.
    Code (Text):

    SELECT GCC.SEGMENT5                                                                                     "ACCOUNT",
           FND.DESCRIPTION                                                                                  "DESCRIPTION",
           GJH.JE_SOURCE                                                                                    "SOURCE",
           (SELECT SUM(NVL(GJL2.ACCOUNTED_DR,0)) - SUM(NVL(GJL2.ACCOUNTED_CR,0))
                        FROM GL_JE_LINES GJL2
                            ,GL_JE_HEADERS GJH2
                        WHERE 1 = 1
                          AND GJL2.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                          AND GJL2.LEDGER_ID = P_LEDGER_ID
                          AND TRUNC(GJL2.EFFECTIVE_DATE)<
                                 (SELECT TRUNC(START_DATE) FROM GL_PERIODS WHERE PERIOD_NAME=P_PERIOD_NAME)
                          AND GJL2.JE_HEADER_ID = GJH2.JE_HEADER_ID
                          AND GJH2.ACTUAL_FLAG = 'A'
                          AND GJH2.JE_SOURCE != 'Consolidation'
                          AND GJH2.LEDGER_ID = GJL2.LEDGER_ID
                          AND GJH2.JE_SOURCE = GJH.JE_SOURCE
                          AND GJL2.STATUS = 'P'
           )                                                                                                "BEGIN_BALANCE",
           (SELECT SUM(NVL(GJL1.ACCOUNTED_DR,0))
                        FROM GL_JE_LINES GJL1
                            ,GL_JE_HEADERS GJH1
                        WHERE 1 = 1
                          AND GJL1.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
                          AND GJL1.LEDGER_ID = P_LEDGER_ID
                          AND GJH1.LEDGER_ID = GJL1.LEDGER_ID
                          AND GJH1.ACTUAL_FLAG = 'A'
                          AND GJH1.JE_SOURCE != 'Consolidation'
                          AND GJH1.JE_HEADER_ID = GJL1.JE_HEADER_ID
                          AND TRUNC(GJL1.EFFECTIVE_DATE)
                                                    BETWEEN
                                                    (SELECT TRUNC(START_DATE)
                                                         FROM GL_PERIODS
                                                             WHERE PERIOD_SET_NAME='CORPORATE'
                                                               AND PERIOD_NAME=P_PERIOD_NAME)
                                                AND (SELECT  TRUNC(END_DATE)
                                                            FROM GL_PERIODS
                                                               WHERE PERIOD_SET_NAME='CORPORATE'
                                                                 AND PERIOD_NAME=P_PERIOD_NAME)
                          AND GJH1.JE_SOURCE = GJH.JE_SOURCE
                          AND GJL1.STATUS = 'P'
                          GROUP BY GCC.SEGMENT5, FND.DESCRIPTION,GJH.JE_SOURCE
           )                                                                                                "DEBIT",
           (SELECT SUM(NVL(GJL1.ACCOUNTED_CR,0))
                        FROM GL_JE_LINES GJL1,
                             GL_JE_HEADERS GJH1
                        WHERE 1 = 1
                          AND GJL1.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
                          AND GJL1.LEDGER_ID = P_LEDGER_ID
                          AND GJH1.LEDGER_ID = GJL1.LEDGER_ID
                          AND GJH1.ACTUAL_FLAG = 'A'
                          AND GJH1.JE_SOURCE != 'Consolidation'
                          AND GJH1.JE_HEADER_ID = GJL1.JE_HEADER_ID
                          AND TRUNC(GJL1.EFFECTIVE_DATE)
                                                    BETWEEN
                                                    (SELECT TRUNC(START_DATE)
                                                         FROM GL_PERIODS
                                                             WHERE PERIOD_SET_NAME='CORPORATE'
                                                               AND PERIOD_NAME=P_PERIOD_NAME)
                                                AND (SELECT  TRUNC(END_DATE)
                                                            FROM GL_PERIODS
                                                               WHERE PERIOD_SET_NAME='CORPORATE'
                                                                 AND PERIOD_NAME=P_PERIOD_NAME)
                          AND GJH1.JE_SOURCE = GJH.JE_SOURCE
                          AND GJL1.STATUS = 'P'
                          GROUP BY GCC.SEGMENT5, FND.DESCRIPTION,GJH.JE_SOURCE
           )                                                                                                "CREDIT",

           (SELECT SUM(NVL(GJL2.ACCOUNTED_DR,0)) - SUM(NVL(GJL2.ACCOUNTED_CR,0))
                        FROM GL_JE_LINES GJL2
                            ,GL_JE_HEADERS GJH2
                        WHERE 1 = 1
                          AND GJL2.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
                          AND GJL2.LEDGER_ID = P_LEDGER_ID
                          AND UPPER(GJL2.EFFECTIVE_DATE) <=
                          (SELECT TRUNC(END_DATE) FROM GL_PERIODS WHERE PERIOD_NAME=P_PERIOD_NAME)
                          AND GJL2.JE_HEADER_ID = GJH2.JE_HEADER_ID
                          AND GJH2.ACTUAL_FLAG = 'A'
                          AND GJH2.JE_SOURCE != 'Consolidation'
                          AND GJH2.LEDGER_ID = GJL2.LEDGER_ID
                          AND GJH2.JE_SOURCE = GJH.JE_SOURCE
                          AND GJL2.STATUS = 'P'
                          )                                                                                 "END_BALANCE"
    FROM
       GL_CODE_COMBINATIONS GCC,
       FND_FLEX_VALUES_VL FND,
       GL_JE_LINES GJL,
       GL_JE_HEADERS GJH

    WHERE 1 = 1
    AND GCC.CHART_OF_ACCOUNTS_ID = P_CHART_OF_ACCT_ID
    AND GCC.SEGMENT1 BETWEEN P_SEGMENT1_LOW AND P_SEGMENT1_HIGH
    AND GCC.SUMMARY_FLAG = 'N'
    AND GCC.TEMPLATE_ID IS NULL
    AND FND.FLEX_VALUE = GCC.SEGMENT5
    AND GJL.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID
    AND GJL.LEDGER_ID = P_LEDGER_ID
    AND GJL.STATUS = 'P'
    --AND GJH.CURRENCY_CODE = 'GBP'
    --AND GJH.LEDGER_ID = GJL.LEDGER_ID
    AND GJH.ACTUAL_FLAG = 'A'
    AND GJH.JE_SOURCE != 'Consolidation'
    AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
    --AND NVL2(P_SOURCE,GJH.JE_SOURCE,1) = NVL2(P_SOURCE,P_SOURCE,1)
    --ADDED ADDITIONAL LOGIC FOR RESTRICTING ADDITIONAL ACCOUNTS WHICH ARE NOT OCCURING IN TRIAL BALANCE REPORT
    AND
    (
        (SELECT SUM(BEGIN_BALANCE_DR) - SUM(BEGIN_BALANCE_CR)
        FROM GL_BALANCES
        WHERE CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
        AND LEDGER_ID = GJL.LEDGER_ID
        AND CURRENCY_CODE = P_LEDGER_CURRENCY
        AND PERIOD_NAME IN (P_PERIOD_NAME)) != 0

        OR

        (SELECT SUM(PERIOD_NET_DR)
        FROM GL_BALANCES
        WHERE CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
        AND LEDGER_ID = GJL.LEDGER_ID
        AND CURRENCY_CODE = P_LEDGER_CURRENCY
        AND PERIOD_NAME IN (P_PERIOD_NAME)) != 0

        OR

        (SELECT SUM(PERIOD_NET_CR)
        FROM GL_BALANCES
        WHERE CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
        AND LEDGER_ID = GJL.LEDGER_ID
        AND CURRENCY_CODE = P_LEDGER_CURRENCY
        AND PERIOD_NAME IN (P_PERIOD_NAME)) != 0
    )
    ---------------------END OF ADDITIONAL LOGIC

    GROUP BY GCC.SEGMENT5, FND.DESCRIPTION,GJH.JE_SOURCE,GCC.CODE_COMBINATION_ID

    ORDER BY 1;
     
     
  6. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi Tom,

    Any information on this ?
     
  7. Tom Morello

    Tom Morello Active Member

    Messages:
    26
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    Indonesia
    Hi Bharat,

    Now i use R11.5.10 so i try your query with Vision R12.1.3.
    I attach output from Run report "Trial Balance - Detail" and your query.
    For some accounts begin_balance and end_balance amounts are mismatched when compared with to Trial Balance Report Output.
    I am still looking what's wrong about the query.

    Salam,
    Tom Morello
     

    Attached Files:

    Bharat likes this.
  8. mkarim59

    mkarim59 Guest

    Any help on the above post Tom.

    Regards,
    karim