Invoice posted register Query

 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'

order by aia.invoice_date;