Wednesday, September 30, 2015

GLRTB2 - GL Trail Balance Additional Segment Deatils Query

SELECT
    GLCC.segment1,
    GLCC.segment2,
    GLCC.segment3,
    gl_flexfields_pkg.get_description_sql (glcc.chart_of_accounts_id,3,glcc.segment3) Account_description,
    GLCC.concatenated_segments gl_account_string,
    GLB.currency_code currency_code,
    GLB.period_name period_name,
    GPY.PERIOD_NAME year_starting_period,
    GPC.start_date period_start_date,
    GPC.end_date period_end_date,
    NVL (GLB.begin_balance_dr, 0) - NVL (GLB.begin_balance_cr, 0) period_begin_balance,
    NVL (GLB.period_net_dr, 0) - NVL (GLB.period_net_cr, 0) period_activity,
    NVL (GLB.period_net_dr, 0) - NVL (GLB.period_net_cr, 0) + NVL (GLB.begin_balance_dr, 0) - NVL (GLB.begin_balance_cr, 0) period_end_balance,
    NVL (GLBY.begin_balance_dr, 0) - NVL (GLBY.begin_balance_cr, 0) year_begin_balance,
    NVL (GLB.period_net_dr, 0) - NVL (GLB.period_net_cr, 0) + NVL (GLB.begin_balance_dr, 0) - NVL (GLB.begin_balance_cr, 0) - (NVL (GLBY.begin_balance_dr, 0) - NVL (GLBY.begin_balance_cr, 0)) year_activity,
    NVL (GLB.period_net_dr, 0) - NVL (GLB.period_net_cr, 0) + NVL (GLB.begin_balance_dr, 0) - NVL (GLB.begin_balance_cr, 0) year_end_balance
FROM
    GL_BALANCES GLB,
    GL_CODE_COMBINATIONS_KFV GLCC,
    GL_LEDGERS LED,
    GL_BALANCES GLBY,
    gl_periods gpy,
    gl_periods gpc
WHERE    1=1
    AND LED.CHART_OF_ACCOUNTS_ID = GLCC.CHART_OF_ACCOUNTS_ID
    AND GLB.CODE_COMBINATION_ID = GLCC.CODE_COMBINATION_ID
    AND GLBY.CODE_COMBINATION_ID = GLCC.CODE_COMBINATION_ID
    AND GLB.LEDGER_ID = LED.LEDGER_ID
    AND GLBY.LEDGER_ID = LED.LEDGER_ID
    AND GLBY.CURRENCY_CODE = GLB.CURRENCY_CODE
    AND GLBY.PERIOD_NAME = GPY.PERIOD_NAME
    AND GPY.PERIOD_SET_NAME = LED.PERIOD_SET_NAME
    AND GPY.PERIOD_TYPE = LED.ACCOUNTED_PERIOD_TYPE
    AND GPY.PERIOD_YEAR = GPC.PERIOD_YEAR                              --
    AND GPY.PERIOD_NUM = 1
    AND GPY.PERIOD_NUM =
        (SELECT MIN(PERIOD_NUM) FROM GL_BALANCES GLB1
         WHERE GLB1.CODE_COMBINATION_ID = GLB.CODE_COMBINATION_ID
         AND GLB1.PERIOD_YEAR = GPY.PERIOD_YEAR
         AND GLB1.CURRENCY_CODE = GLB.CURRENCY_CODE
         AND GLB1.LEDGER_ID = GLB.LEDGER_ID
         AND GLB1.ACTUAL_FLAG = 'A'
         AND (GLB1.TRANSLATED_FLAG != 'R' OR GLB1.TRANSLATED_FLAG IS NULL)
        )
    AND GPC.PERIOD_NAME = GLB.PERIOD_NAME
    AND GPC.PERIOD_SET_NAME = LED.PERIOD_SET_NAME
    AND GPC.PERIOD_TYPE = LED.ACCOUNTED_PERIOD_TYPE
    AND GLBY.PERIOD_NUM = GPY.PERIOD_NUM
    AND GLBY.PERIOD_YEAR = GPY.PERIOD_YEAR
    AND GLB.ACTUAL_FLAG = 'A'
    AND (GLB.TRANSLATED_FLAG != 'R' OR GLB.TRANSLATED_FLAG IS NULL)
    AND GLBY.ACTUAL_FLAG = 'A'
    AND (GLBY.TRANSLATED_FLAG != 'R' OR GLBY.TRANSLATED_FLAG IS NULL)
    AND GLCC.TEMPLATE_ID IS NULL
    and GLB.period_name = 'APR-15'
    and GLCC.segment1 = '01'
    and GLCC.segment2 = '0000'
    --and GLCC.segment3 = '11110'
    and LED.name = 'Vision Operations'
    and GLB.currency_code = 'USD'
and (
           NVL (GLBY.begin_balance_dr, 0) - NVL (GLBY.begin_balance_cr, 0) != 0
        OR NVL (GLB.period_net_dr, 0) - NVL (GLB.period_net_cr, 0) + NVL (GLB.begin_balance_dr, 0) - NVL (GLB.begin_balance_cr, 0) - (NVL (GLBY.begin_balance_dr, 0) - NVL (GLBY.begin_balance_cr, 0)) != 0
        OR NVL (GLB.period_net_dr, 0) - NVL (GLB.period_net_cr, 0) + NVL (GLB.begin_balance_dr, 0) - NVL (GLB.begin_balance_cr, 0) != 0
    )
order by 1,2,3

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect