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

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';



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));



Ur's
AmarAlam