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