Summary of Subledger Accounting Balances

Summary of Subledger Accounting Balances 

SELECT x.source,
         x.period_name,
        -- to_number(x.year),
         gcc.concatenated_segments account,
         x.debit_amount,
         x.credit_amount,
         x.nett_amount,gl_transfer_status_code gl_transfer_status
    FROM (  SELECT fa.application_name source,
                   b.period_name,
                   --SUBSTR (b.period_name, 5, 2) year,
                   to_char(to_date(period_name,'MON-YY'),'YYMM') year,
                   NVL (SUM (a.accounted_dr), 0) debit_amount,
                   NVL (SUM (a.accounted_cr), 0) credit_amount,
                   NVL (SUM (a.accounted_dr), 0) - NVL (SUM (a.accounted_cr), 0)
                      nett_amount,
                   a.code_combination_id,b.gl_transfer_status_code
              FROM xla_ae_lines a, xla_ae_headers b, fnd_application_tl fa
             WHERE     code_combination_id IN (42248,42063)       -- 1360547, 42063)
                   AND a.ae_header_id = b.ae_header_id
                   --AND SUBSTR (b.period_name, 5, 2) = 19
                   AND a.application_id = b.application_id
                   AND fa.application_id = a.application_id
                   --AND b.period_name = 'JUL-19'
          GROUP BY code_combination_id,
                   b.period_name,
                   fa.application_name,b.gl_transfer_status_code
          UNION ALL
            SELECT source_name source,
                   PERIOD_NAME,
                   --SUBSTR (period_name, 5, 2) year,
                   to_char(to_date(period_name,'MON-YY'),'YYMM') year,
                   NVL (SUM (ENTERED_DR), 0) debit_amount,
                   NVL (SUM (ENTERED_CR), 0) credit_amount,
                   NVL (SUM (ENTERED_DR), 0) - NVL (SUM (ENTERED_CR), 0)
                      nett_amount,
                   code_combination_id, decode(journal_entry_id,NULL,'N','Y') gl_transfer_status_code
              FROM JAI_RCV_JOURNAL_ENTRIES
             WHERE     1 = 1
                  -- AND PERIOD_NAME = 'JUL-19'
                   AND code_combination_id IN ( 42248,42063)
          GROUP BY PERIOD_NAME,
                   source_name,
                   code_combination_id,
                   decode(journal_entry_id,NULL,'N','Y')
                   ) x,
         gl_code_combinations_kfv gcc
   WHERE gcc.code_combination_id = x.code_combination_id
ORDER BY to_number(x.year)