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;
Recent Posts
