select aia.INVOICE_NUM
,aba.batch_name	
,aia.INVOICE_AMOUNT
,aia.PAY_GROUP_LOOKUP_CODE INv_group
,aia.INVOICE_CURRENCY_CODE
,aia.CREATION_DATE
,To_char(aia.creation_date,'MONTH') InvCreation_Month
,aia.INVOICE_DATE
,aia.SOURCE
,aia.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE
,PAYMENT_METHOD_CODE INVOICE_PAY_METHOD
,aia.amount_paid amount_paid
,aia.description
,aia.doc_sequence_value VOUCHER_NUM
,(select max(aila.ACCOUNTING_DATE)
        from apps.ap_invoice_lines_all aila            
        where aila.invoice_id = aia.invoice_id
        and aila.org_id = aia.org_id
        and rownum =1) ACCOUNTING_DATE
,aia.GL_DATE
,asu.VENDOR_NAME
,asu.segment1 VENDOR_NUMBER
,assa.VENDOR_SITE_CODE
,(select aca.CHECK_NUMBER 
    from apps.ap_invoice_payments_all aipa,
         apps.ap_checks_all aca 
    where  aipa.check_id = aca.check_id 
           and nvl(aipa.reversal_flag,'N') <> 'Y'
           and  aipa.invoice_id = aia.invoice_id
           and aipa.org_id = aia.org_id
           and rownum = 1) CHECK_NUMBER
,(select aca.CHECK_DATE 
    from apps.ap_invoice_payments_all aipa,
         apps.ap_checks_all aca 
    where  aipa.check_id = aca.check_id 
           and nvl(aipa.reversal_flag,'N') <> 'Y'
           and  aipa.invoice_id = aia.invoice_id
           and aipa.org_id = aia.org_id
           and rownum = 1) CHECK_DATE
,(select distinct poh.segment1
    from apps.po_headers_all poh,
         apps.ap_invoice_lines_all aila
    where aila.po_header_id = poh.po_header_id
      and aila.invoice_id = aia.invoice_id
      and poh.org_id = aia.org_id
      and rownum = 1) PO_NUMBER
,at.name TERMS
,Decode((select distinct poh.segment1
    from apps.po_headers_all poh,
         apps.ap_invoice_lines_all aila
    where aila.po_header_id = poh.po_header_id
      and aila.invoice_id = aia.invoice_id
      and poh.org_id = aia.org_id
      and rownum = 1),null,'N','Y') "PO Y/N"
 from    apps.ap_invoices_all aia, 
         apps.ap_batches_all aba,
         apps.ap_suppliers asu,
         apps.ap_supplier_sites_all assa,
         apps.ap_terms at
        -- apps.ap_invoice_payments_all aipa,
        -- apps.ap_checks_all aca
where aia.batch_id = aba.batch_id
and asu.vendor_id = assa.vendor_id
and aia.vendor_id  = asu.vendor_id
and aia.vendor_site_id = assa.vendor_site_id
and aia.terms_id = at.term_id
--and aia.invoice_id = aipa.invoice_id
--and aipa.check_id = aca.check_id
and aia.gl_date between '01-JAN-2020' and '31-MAR-2020'
and aia.org_id = 83
order by aia.invoice_date