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