select aia.INVOICE_NUM
,aia.INVOICE_AMOUNT
,aia.CREATION_DATE,aia.INVOICE_DATE,aia.SOURCE,aia.INVOICE_TYPE_LOOKUP_CODE INVOICE_TYPE,
(select aca.STATUS_LOOKUP_CODE
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) Payemnt_Status,
aia.PAY_GROUP_LOOKUP_CODE INVoice_Pay_group,aia.PAYMENT_METHOD_CODE INVOICE_METHOD,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
, (select b.CONCATENATED_SEGMENTS
from apps.ap_invoice_distributions_all a
,apps.gl_code_combinations_kfv b
where a.invoice_id = aia.invoice_id
and a.DIST_CODE_COMBINATION_ID =b.CODE_COMBINATION_ID and rownum=1) CONCATENATED_SEGMENT
,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,
(select b.segment5
from apps.ap_invoice_distributions_all a
,apps.gl_code_combinations_kfv b
where a.invoice_id = aia.invoice_id
and a.DIST_CODE_COMBINATION_ID =b.CODE_COMBINATION_ID and rownum=1) gl_code
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.gl_date between ’01-JAN-2020′ and ’31-JUL-2020′
and aia.org_id = 382
order by aia.invoice_date
Recommended Posts
