R12 :Query for Bank Book Report

SELECT xe.event_type_code,to_char(ac.check_number) check_number,to_char(ac.check_date,'DD-MON-RRRR') check_date,ai.doc_sequence_value voucher_number,aps.vendor_name,apss.vendor_site_code,
       NVL(xal.accounted_dr,0) accounted_dr,NVL(xal.accounted_cr,0) accounted_cr,xe.event_id,gjb.name batch_name,gjh.je_source,ai.description
  FROM gl_je_lines gjl,
       gl_code_combinations gcc,
       gl_import_references gir,
       xla_ae_lines xal,
       xla_ae_headers xah,
       xla_events xe,
       ap_invoice_distributions_all aid,
       ap_invoices_all ai,
       ap_invoice_payments_all aip,
       ap_checks_all ac,
       ap_suppliers aps,
       ap_supplier_sites_all apss,
       gl_je_batches gjb,
       gl_je_headers gjh
 WHERE gjl.code_combination_id = gcc.code_combination_id
   AND gcc.segment3 = :p_accno
   AND gjl.je_header_id = gir.je_header_id
   AND gjl.je_line_num = gir.je_line_num
   AND gir.gl_sl_link_id = xal.gl_sl_link_id
   AND xal.ae_header_id = xah.ae_header_id
   AND xe.event_id = xah.event_id
   AND xe.event_id = aid.accounting_event_id
   AND ai.invoice_id = aid.invoice_id
   AND aip.invoice_id = aid.invoice_id
   AND aip.check_id = ac.check_id
   AND aps.vendor_id = ai.vendor_id
   AND ai.vendor_site_id = apss.vendor_site_id
   AND aid.dist_code_combination_id = gcc.code_combination_id
   AND gir.je_batch_id = gjb.je_batch_id
   AND gir.je_header_id = gjh.je_header_id
   AND gjl.period_name = :p_period
--   AND ac.check_date BETWEEN :p_from_date and :p_to_date
UNION  ALL
SELECT xe.event_type_code,acr.receipt_number,to_char(acr.receipt_date,'DD-MON-RRRR') check_date,acr.doc_sequence_value voucher_number,hp.party_name,bill_hl.city,
       NVL(xal.accounted_dr,0) accounted_dr,NVL(xal.accounted_cr,0) accounted_cr,xe.event_id,gjb.name batch_name,gjh.je_source,acr.comments
  FROM gl_je_lines gjl,
       gl_code_combinations gcc,
       gl_import_references gir,
       xla_ae_lines xal,
       xla_ae_headers xah,
       xla_events xe,
       ar_cash_receipt_history_all acrh,
       ar_cash_receipts_all acr,
       hz_cust_site_uses_all bill_hcsu,
       hz_cust_acct_sites_all bill_hcas,
       hz_party_sites bill_hps,
       hz_locations bill_hl,
       hz_parties hp,
       gl_je_batches gjb,
       gl_je_headers gjh
 WHERE gjl.code_combination_id = gcc.code_combination_id
   AND gcc.segment3 = :p_accno
   AND gjl.je_header_id = gir.je_header_id
   AND gjl.je_line_num = gir.je_line_num
   AND gir.gl_sl_link_id = xal.gl_sl_link_id
   AND xal.ae_header_id = xah.ae_header_id
   AND xe.event_id = xah.event_id
   AND xe.event_id = acrh.event_id
   AND acrh.cash_receipt_id = acr.cash_receipt_id
   AND bill_hcsu.site_use_id(+) = acr.customer_site_use_id
   AND bill_hcsu.cust_acct_site_id = bill_hcas.cust_acct_site_id(+)
   AND bill_hcas.party_site_id = bill_hps.party_site_id(+)
   AND bill_hps.location_id = bill_hl.location_id(+)
   AND bill_hps.party_id = hp.party_id(+)
   AND gir.je_batch_id = gjb.je_batch_id
   AND gir.je_header_id = gjh.je_header_id
   AND gjl.period_name = :p_period  
UNION ALL
SELECT xe.event_type_code,to_char(ac.check_number),to_char(ac.check_date,'DD-MON-RRRR') check_date,ac.doc_sequence_value voucher_number,
       aps.vendor_name,
       apss.vendor_site_code,
       CASE WHEN aip.amount <0 THEN ROUND(ABS(aip.amount*NVL(aip.exchange_rate,1)),3) ELSE 0 END accounted_dr,
       CASE WHEN aip.amount >0 THEN ROUND((aip.amount*NVL(aip.exchange_rate,1)),3) ELSE 0 END accounted_cr,
       xe.event_id,gjb.name batch_name,gjh.je_source,
       (select description from ap_invoices_all where invoice_id = aip.invoice_id)
  FROM gl_je_lines gjl,
       gl_code_combinations gcc,
       gl_import_references gir,
       xla_ae_lines xal,
       xla_ae_headers xah,
       xla_events xe,
       ap_invoice_payments_all aip,
       ap_checks_all ac,
       ap_suppliers aps,
       ap_supplier_sites_all apss,
       gl_je_batches gjb,
       gl_je_headers gjh
 WHERE gjl.code_combination_id = gcc.code_combination_id
   AND gcc.segment3 = :p_accno
   AND gjl.je_header_id = gir.je_header_id
   AND gjl.je_line_num = gir.je_line_num
   AND gir.gl_sl_link_id = xal.gl_sl_link_id
   AND xal.ae_header_id = xah.ae_header_id
   AND xe.event_id = xah.event_id
   AND xe.event_id = aip.accounting_event_id
   AND ac.check_id = aip.check_id
   AND aps.vendor_id = ac.vendor_id
   AND ac.vendor_site_id = apss.vendor_site_id
   AND gir.je_batch_id = gjb.je_batch_id
   AND gir.je_header_id = gjh.je_header_id
   AND gjl.period_name = :p_period
UNION ALL
SELECT NULL,NULL,to_char(gjh.date_created,'DD-MON-RRRR'),gjh.doc_sequence_value,NULL,NULL,
       NVL(gjl.accounted_dr,0) accounted_dr,NVL(gjl.accounted_cr,0) accounted_cr,NULL,gjb.name batch_name,gjh.je_source,NULL
  FROM gl_je_lines gjl,
       gl_code_combinations gcc,
       gl_je_batches gjb,
       gl_je_headers gjh
WHERE gjl.code_combination_id = gcc.code_combination_id
   AND gjl.period_name = :p_period
   AND gcc.segment3 = :p_accno      
   AND gjl.je_header_id = gjh.je_header_id
   AND gjh.je_batch_id = gjb.je_batch_id
   AND gjh.je_source = 'Manual'
ORDER BY check_date