Monday, September 26, 2022

Gl_Balances and Gl_je_lines with Source Details in Oracle fusion

 SELECT

    code.segment3,

    bal_table.period_dr,

    bal_table.period_cr,

    bal_table.period_balances,

    bal_table.year_to_date_balance,

    SUM(nvl(line_table.accounted_dr,0)) line_dr,

    SUM(nvl(accounted_cr,0)) line_cr,

    SUM(nvl(line_table.accounted_dr,0)) - SUM(nvl(accounted_cr,0)) line_bal,src.je_source_name 

FROM

    (

        SELECT

            bal.code_combination_id,

            bal.currency_code,

            bal.ledger_id,

            bal.period_name,

            SUM(begin_balance_dr) begin_dr,

            SUM(begin_balance_cr) begin_cr,

            SUM(period_net_dr) period_dr,

            SUM(period_net_cr) period_cr,

            SUM(period_net_dr) - SUM(period_net_cr) period_balances,

            SUM(begin_balance_dr) + SUM(period_net_dr) end_dr,

            SUM(begin_balance_cr) + SUM(period_net_cr) end_cr,

            ( SUM(begin_balance_dr) + SUM(period_net_dr) ) - ( SUM(begin_balance_cr) + SUM(period_net_cr) ) year_to_date_balance

        FROM

            gl_balances bal

        GROUP BY

            bal.code_combination_id,

            bal.currency_code,

            bal.ledger_id,

            bal.period_name

    ) bal_table,

    gl_je_lines line_table,

    gl_code_combinations code, GL_JE_HEADERS gjh,  gl_je_sources src


WHERE      bal_table.code_combination_id = line_table.code_combination_id

AND src.je_source_name = gjh.je_source

and line_table.je_header_id   = gjh.je_header_id


and line_table.status = 'P'

    AND    bal_table.code_combination_id = code.code_combination_id

    AND    bal_table.period_name = line_table.period_name

    AND    bal_table.ledger_id = line_table.ledger_id

    AND    code.segment1 = '269'

and    code.segment3 = '101269'

    --AND bal_table.code_combination_id = 719953

    AND    bal_table.period_name = 'Jun-22'

    AND    bal_table.currency_code = 'USD'

    AND    bal_table.ledger_id = 300000003474501

GROUP BY

    code.segment3,

    bal_table.period_dr,

    bal_table.period_cr,

    bal_table.period_balances,

    bal_table.year_to_date_balance,src.je_source_name

No comments:

Post a Comment

Oracle Fusion - Cost Lines and Expenditure Item link in Projects

SELECT   ccd.transaction_id,ex.expenditure_item_id,cacat.serial_number FROM fusion.CST_INV_TRANSACTIONS cit,   fusion.cst_cost_distribution_...