GL Revaluation Accounts Query - FUSION

SELECT   DISTINCT o101368.book_currency AS c_1,
         (o101368.converted_journal_total_dr
          * o101368.currency_conversion_rate
         ) AS c_2,
           o101368.converted_journal_total_cr
         * o101368.currency_conversion_rate AS c_3,
         o101368.currency,
         o101368.currency_conversion_date,
         o101368.currency_conversion_rate,
         o101368.currency_conversion_type,
         o101368.effective_date,
         o101368.journal_batch_name,
         o101368.journal_category,
         o101368.journal_entry_name,
         LPAD(o101368.journal_entry_name,8) reval_reverse,
         o101368.journal_source, o101368.period_name,
         o101368.reversal_period,
         o101368.set_of_books_name set_of_books_name,
         o101400.company_50248 company, o101400.account_50248 account,
         o101400.interco_50248 interco,
         o101368.reverse_status_flag,
         as107663_128801_old AS as107663_128801_old,
         as107665_128817_old AS as107665_128817_old,
         as107668_136855_old AS as107668_136855_old,
         NVL(SUM(o101400.entered_cr), 0) AS e125430_sum,
         NVL(SUM(o101400.entered_dr), 0) AS e125439_sum,
         NVL(SUM(o101400.converted_cr), 0) AS e118546_sum,
         NVL(SUM(o101400.converted_dr), 0) AS e118547_sum,   
         NVL(SUM(o101400.converted_dr), 0) - NVL(SUM(o101400.converted_cr), 0) net_converted
    FROM (SELECT je1.je_header_id journal_entry_id, je1.NAME journal_entry_name, je1.ledger_id, set_of_books.NAME set_of_books_name,
                  journal_category.user_je_category_name journal_category,
                  journal_source.user_je_source_name journal_source, je1.period_name,
                  je1.currency_code currency, je1.default_effective_date effective_date,
                  DECODE (je1.tax_status_code,
                          'N', 'Not Required',
                          'R', 'Required',
                          'T', 'Taxed',
                          NULL
                         ) tax_status,
                  je1.je_batch_id journal_batch_id, journal_batch.NAME journal_batch_name, je1.accrual_rev_effective_date reversal_date,
                  je1.accrual_rev_period_name reversal_period,
                  DECODE (je1.accrual_rev_status,
                          'M', 'Marked for Reversal',
                          'N', 'Not Reversed',
                          'R', 'Reversed',
                          'U', 'Unreversable',
                          NULL
                         ) reverse_status_flag,
                  DECODE (DECODE (je1.accrual_rev_change_sign_flag,
                                  'Y', 'C',
                                  'N', 'S'
                                 ),
                          'C', 'Change Sign',
                          'S', 'Switch Dr/Cr',
                          NULL
                         ) reversal_method,
                  je1.description description, je1.control_total control_total, je1.running_total_dr entered_journal_total_dr,
                  je1.running_total_cr entered_journal_total_cr, je1.running_total_accounted_dr converted_journal_total_dr,
                  je1.running_total_accounted_cr converted_journal_total_cr, je1.currency_conversion_rate currency_conversion_rate,
                  je1.currency_conversion_type currency_conversion_type_code,
                  gl_conversion_type.user_conversion_type currency_conversion_type,
                  je1.currency_conversion_date currency_conversion_date, je1.external_reference,
                  je1.parent_je_header_id primary_journal_entry_id, je2.NAME primary_journal_entry_name, je1.reversed_je_header_id reversed_journal_entry_id,
                  je3.NAME reversed_journal_entry_name, je1.doc_sequence_value document_number, je1.originating_bal_seg_value,
                  set_of_books.currency_code book_currency
             FROM gl_je_headers je1,
                  gl_sets_of_books set_of_books,
                  gl_je_batches journal_batch,
                  gl_je_headers je2,
                  gl_je_headers je3,
                  gl_je_categories journal_category,
                  gl_je_sources journal_source,
                  gl_daily_conversion_types gl_conversion_type
            WHERE je1.ledger_id = set_of_books.set_of_books_id
              AND je1.je_batch_id = journal_batch.je_batch_id
              AND je1.parent_je_header_id = je2.je_header_id(+)
              AND je1.reversed_je_header_id = je3.je_header_id(+)
              AND je1.actual_flag = 'A'
              AND je1.je_category = journal_category.je_category_name
              AND je1.je_source = journal_source.je_source_name
              AND je1.currency_conversion_type = gl_conversion_type.conversion_type) o101368,
          (SELECT journal_line.je_header_id journal_entry_id, journal_entry.NAME journal_entry_name,
                   journal_line.je_line_num journal_line_number,
                   journal_line.ledger_id set_of_books_id,
                   gl_set_of_books.NAME set_of_books_name, journal_line.code_combination_id account_id,
                   DECODE (gcc.chart_of_accounts_id,
                           50248, gcc.segment1
                            || '-'
                            || gcc.segment2
                            || '-'
                            || gcc.segment3
                            || '-'
                            || gcc.segment4
                            || '-'
                            || gcc.segment5
                            || '-'
                            || gcc.segment6,
                           NULL
                          ) ACCOUNT,
                   gcc.segment1 company_50248, gcc.segment2 account_50248, gcc.segment3 department_50248, gcc.segment4 region_50248,
                   gcc.segment5 function_50248, gcc.segment6 interco_50248, journal_line.period_name period_name,
                   journal_line.effective_date effective_date, journal_line.entered_dr entered_dr,
                   journal_line.entered_cr entered_cr, journal_line.accounted_dr converted_dr,
                   journal_line.accounted_cr converted_cr, journal_line.description description,
                   journal_line.stat_amount stat_amount, journal_line.subledger_doc_sequence_value subledger_document_number,
                   journal_line.reference_1, journal_line.reference_2,
                   journal_line.reference_3, journal_line.reference_4,
                   journal_line.reference_5, journal_line.reference_6,
                   journal_line.reference_7, journal_line.reference_8,
                   journal_line.reference_9, journal_line.reference_10,
                   journal_line.attribute_category line_context,
                   journal_line.attribute1 line_project_number_2,
                   journal_line.taxable_line_flag tax_tax_line, journal_line.tax_type_code tax_tax_type_3,
                   journal_line.tax_code_id tax_tax_code_3, journal_line.tax_rounding_rule_code tax_rounding_rule_3,
                   journal_line.amount_includes_tax_flag tax_amount_includes_tax_3,
                   journal_line.tax_document_identifier tax_document_identifier_3,
                   journal_line.tax_document_date tax_document_date_3, journal_line.tax_customer_name "TAX_CUSTOMER/VENDOR_NAME_3",
                   journal_line.tax_customer_reference "TAX_CUSTOMER/VENDOR_REFERENC_3",
                   journal_line.tax_registration_number tax_tax_registration_number_3,                 
                   journal_line.jgzz_recon_ref local_recon_ref
              FROM gl_sets_of_books gl_set_of_books,
                   gl_je_lines journal_line,
                   gl_je_headers journal_entry,
                   gl_code_combinations gcc
             WHERE journal_line.ledger_id = gl_set_of_books.set_of_books_id             
               AND journal_line.je_header_id = journal_entry.je_header_id
               AND journal_line.code_combination_id = gcc.code_combination_id) o101400,
         (SELECT   o101398.journal_batch_name AS as107663_128801_old_2,
                   MAX (o101398.journal_batch_id) AS as107663_128801_old
              FROM (SELECT journal_batch1.je_batch_id journal_batch_id, journal_batch1.NAME journal_batch_name                         
                      FROM gl_je_batches journal_batch1) o101398
          GROUP BY o101398.journal_batch_name),
         (SELECT   o101399.journal_entry_name AS as107665_128817_old_2,
                   MAX (o101399.journal_entry_id) AS as107665_128817_old
              FROM (SELECT je1.je_header_id journal_entry_id, je1.NAME journal_entry_name
                      FROM gl_je_headers je1,
                           gl_sets_of_books set_of_books,
                           gl_encumbrance_types_b gl_encumbrance_type,
                          --gl_budget_versions gl_budget_version,
                          gl_je_batches journal_batch,
                          gl_je_headers je2,
                          gl_je_headers je3,
                          gl_je_categories journal_category,
                          gl_je_sources journal_source,
                          gl_daily_conversion_types gl_conversion_type
                    WHERE je1.ledger_id = set_of_books.set_of_books_id
                      AND je1.encumbrance_type_id = gl_encumbrance_type.encumbrance_type_id(+)                     
                      AND je1.je_batch_id = journal_batch.je_batch_id
                      AND je1.parent_je_header_id = je2.je_header_id(+)
                      AND je1.reversed_je_header_id = je3.je_header_id(+)
                      AND je1.je_category = journal_category.je_category_name
                      AND je1.je_source = journal_source.je_source_name
                      AND je1.currency_conversion_type = gl_conversion_type.conversion_type
                        ) o101399
          GROUP BY o101399.journal_entry_name),
         (SELECT   o101395.period_name AS as107668_136855_old_2,
                   MAX (o101395.period_number) AS as107668_136855_old
             FROM (SELECT gl_period.period_name, gl_period.period_num period_number
                     FROM gl_periods gl_period) o101395
          GROUP BY o101395.period_name)
   WHERE o101368.journal_entry_id = o101400.journal_entry_id
     AND o101368.journal_batch_name = as107663_128801_old_2(+)
     AND o101368.journal_entry_name = as107665_128817_old_2(+)
     AND o101368.period_name = as107668_136855_old_2(+)       
     AND o101368.period_name IN (:P_PERIOD_NAME)
     AND O101368.JOURNAL_SOURCE IN (:P_JOURNAL_SOURCE)
     AND O101368.JOURNAL_CATEGORY IN (:P_JOURNAL_CATEGORY)
GROUP BY as107668_136855_old,
         as107665_128817_old,
         as107663_128801_old,
         o101368.book_currency,
         (o101368.converted_journal_total_dr
          * o101368.currency_conversion_rate
         ),
         o101368.converted_journal_total_cr * o101368.currency_conversion_rate,
         o101368.currency,
         o101368.currency_conversion_date,
         o101368.currency_conversion_rate,
         o101368.currency_conversion_type,
         o101368.effective_date,
         o101368.journal_batch_name,
         o101368.journal_category,
         o101368.journal_entry_name,
         o101368.journal_source,
         o101368.period_name,
         o101368.reversal_period,
         o101368.set_of_books_name,
         o101400.company_50248,
         o101400.account_50248,
         o101400.interco_50248,
         o101368.reverse_status_flag
ORDER BY o101368.set_of_books_name ASC,
         o101368.currency_conversion_date ASC,
         as107668_136855_old ASC,
         o101400.account_50248 ASC,
         o101368.currency ASC