SQL Query to Link GL Journal and SLA Tables for 'Misc Receipts','Receipts' and 'Reconciled Payments'

AR Model:-

/* Formatted on 7/4/2014 12:53:20 PM (QP5 v5.115.810.9015) */
SELECT ada.line_id,
       jh.je_category category,
       jh.period_name period_name,
       NULL invoice_no_or_memo_no,
       NVL (jl.entered_dr, 0) - NVL (jl.entered_cr, 0) amount_journal,
       NVL (xal.entered_dr, 0) - NVL (xal.entered_cr, 0) amount_xla,
       NVL (ada.amount_dr, 0) - NVL (ada.amount_cr, 0) amount_receivables,
       DECODE (jh.ledger_id, 2027, 'USD', 2029, 'EUR', 2023, 'USD')
          currency_code,
       xdl.source_distribution_type,
       jh.ledger_id book
FROM gl_je_headers jh,
     gl_je_lines jl,
     gl_code_combinations gcc,
     gl_import_references gir,
     xla_ae_lines xal,
     xla_distribution_links xdl,
     xla_ae_headers xah,
     ar_distributions_all ada
WHERE     1 = 1
      AND jh.je_header_id = jl.je_header_id
      AND jl.code_combination_id = gcc.code_combination_id
      AND gir.je_header_id = jh.je_header_id
      AND gir.je_line_num = jl.je_line_num
      AND gir.gl_sl_link_id = xal.gl_sl_link_id
      AND xah.ae_header_id = xal.ae_header_id
      AND xal.application_id = xdl.application_id
      AND xah.ae_header_id = xdl.ae_header_id
      AND xal.ae_line_num = xdl.ae_line_num
      AND xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
      AND xdl.source_distribution_id_num_1 = ada.line_id
      AND gcc.code_combination_id = 46032
      AND jh.period_name LIKE '13-Jun'
      AND jh.je_category IN ('Misc Receipts', 'Receipts')
      AND ABS (NVL (jl.entered_dr, 0) - NVL (jl.entered_cr, 0)) <>
            ABS (NVL (ada.amount_dr, 0) - NVL (ada.amount_cr, 0))
      AND ABS (NVL (xal.entered_dr, 0) - NVL (xal.entered_cr, 0)) <>
            ABS (NVL (ada.amount_dr, 0) - NVL (ada.amount_cr, 0))
      AND jh.ledger_id = '2029';

AP Model:-

/* Formatted on 7/4/2014 12:54:49 PM (QP5 v5.115.810.9015) */
SELECT jh.name,
       jh.je_category category,
       jh.period_name period_name,
       NULL invoice_no_or_memo_no,
       ABS (NVL (jl.entered_dr, 0) - NVL (jl.entered_cr, 0)) amount_journal,
       ABS (NVL (xal.accounted_dr, 0) - NVL (xal.accounted_cr, 0)) amount_xla,
       ABS (SUM (aphd.amount)) amount_payment,
       DECODE (jh.ledger_id, 2027, 'USD', 2029, 'EUR', 2023, 'USD')
          currency_code,
       xdl.source_distribution_type,
       jh.ledger_id book
FROM gl_je_headers jh,
     gl_je_lines jl,
     gl_code_combinations gcc,
     gl_import_references gir,
     xla_ae_lines xal,
     xla_distribution_links xdl,
     xla_ae_headers xah,
     ap_payment_hist_dists aphd
WHERE     1 = 1
      AND jh.je_header_id = jl.je_header_id
      AND jl.code_combination_id = gcc.code_combination_id
      AND gir.je_header_id = jh.je_header_id
      AND gir.je_line_num = jl.je_line_num
      AND gir.gl_sl_link_id = xal.gl_sl_link_id
      AND xah.ae_header_id = xal.ae_header_id
      AND xal.application_id = xdl.application_id
      AND xah.ae_header_id = xdl.ae_header_id
      AND xal.ae_line_num = xdl.ae_line_num
      AND gcc.code_combination_id = 46032
      AND xdl.source_distribution_type = 'AP_PMT_DIST'
      AND xdl.source_distribution_id_num_1 = aphd.payment_hist_dist_id
      AND jh.je_category IN ('Reconciled Payments')
      AND jh.ledger_id = '2029'
      AND jh.period_name LIKE '13-Jun'
--and abs (nvl(jl.entered_dr,0)-nvl(jl.entered_cr,0))  <> abs(sum(aphd.amount) )
--and NVL(xal.accounted_dr,0)-NVL(xal.accounted_cr,0) <> abs(sum(aphd.amount)),
GROUP BY jh.name,
         jh.je_category,
         jh.period_name,
         NVL (jl.entered_dr, 0) - NVL (jl.entered_cr, 0),
         NVL (xal.accounted_dr, 0) - NVL (xal.accounted_cr, 0),
         DECODE (jh.ledger_id, 2027, 'USD', 2029, 'EUR', 2023, 'USD'),
         xdl.source_distribution_type,
         jh.ledger_id
HAVING --abs (nvl(jl.entered_dr,0)-nvl(jl.entered_cr,0))  <> abs(sum(aphd.amount) );
      ABS (NVL (xal.accounted_dr, 0) - NVL (xal.accounted_cr, 0)) <>
          ABS (SUM (aphd.amount));