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!

Userful R12 Scripts- 4

Discussion in 'Oracle Apps Technical' started by Ramji, Feb 3, 2012.

  1. Ramji

    Ramji Forum Guru

    Messages:
    525
    Likes Received:
    177
    Trophy Points:
    1,505
    Location:
    Nomad
    User for Month End Balance Reconciliation

    Gives the Opening GL Balance, Movements and Closing GL Balance

    Parameters:
    Balance Type
    Ledger Name
    GL Period

    SCRIPT

    Code (SQL):
    SET linesize 200
    col begin_dr format 999,999,999,999,999.99
    col begin_cr format 999,999,999,999,999.99
    col dr format 999,999,999,999.99
    col cr format 999,999,999,999.99
    col end_dr format 999,999,999,999,999.99
    col end_cr format 999,999,999,999,999.99

    SELECT translated_flag
    , currency_code
    , SUM(begin_balance_dr) begin_dr
    , SUM(begin_balance_cr) begin_cr
    , SUM(period_net_dr) dr
    , SUM(period_net_cr) cr
    , SUM(begin_balance_dr) + SUM(period_net_dr) end_dr
    , SUM(begin_balance_cr) + SUM(period_net_cr) end_cr
          FROM gl_balances
                WHERE ledger_id = &ledger_id
                AND period_name = '&period_name'
                AND actual_flag = '&Balance_type'
                 -- A Actual, B Budget, E Encumbrance
                AND template_id IS NULL
                  GROUP BY translated_flag
                  , currency_code ;