select aia.invoice_num,
aia.invoice_currency_code,
DECODE(aia.PAYMENT_STATUS_FLAG,’N’,’UN-PAID’,’P’,’Partial Paid’,’Y’,’PAID’) PAYMENT_STATUS_FLAG ,
aia.invoice_date,
aps.vendor_name,
apss.vendor_site_code,
aila.line_number,
aia.invoice_amount,
aila.amount line_amount,
pha.segment1 po_number,
aila.line_type_lookup_code,
apt.name Term_name,
gcc.concatenated_segments distributed_code_combinations,
aca.check_number,
aipa.amount payment_amount,
apsa.amount_remaining,
aipa.invoice_payment_type,
hou.name operating_unit,
gl.name ledger_name
from apps.ap_invoices_all aia,
ap_invoice_lines_all aila,
ap_invoice_distributions_all aida,
ap_suppliers aps,
ap_supplier_sites_all apss,
po_headers_all pha,
gl_code_combinations_kfv gcc,
ap_invoice_payments_all aipa,
ap_checks_all aca,
ap_payment_schedules_all apsa,
ap_terms apt,
hr_operating_units hou,
gl_ledgers gl
where aia.invoice_id = aila.invoice_id
AND aps.vendor_id=apss.VENDOR_ID
and aia.po_header_id=pha.po_header_id(+)
and aida.dist_code_combination_id=gcc.code_combination_id
and aipa.invoice_id(+)=aia.invoice_id
and aca.check_id (+)=aipa.check_id
and apsa.invoice_id=aia.invoice_id
and apt.term_id=aia.terms_id
and hou.organization_id=aia.org_id
and gl.ledger_id=aia.set_of_books_id
and aia.ORG_ID=:P_ORG_ID
and aila.invoice_id = aida.invoice_id
and aila.line_number = aida.invoice_line_number
and aia.vendor_id=aps.vendor_id
and aia.PAYMENT_STATUS_FLAG<> ‘Y’
and amount_remaining>0
and aia.VENDOR_SITE_ID=APSS.VENDOR_SITE_ID
select * from hr_operating_units
select * from ap_invoice_payments_All
select * from ap_invoices_all where PAYMENT_STATUS_FLAG<> ‘Y’ and invoice_amount>0
select * from ap_invoice_lines_all
select * from ap_payment_schedules_all
where AMOUNT_REMAINING>0
SELECT ou.NAME operating_unit,aia.invoice_num invoice_number, aia.doc_sequence_value voucher_number,
aia.invoice_date,aia.gl_date,
aia.invoice_amount, aia.exchange_rate
FROM ap_invoices_all aia,
— ap_invoice_lines_all ail,
ap_payment_schedules_all aps,
hr_operating_units ou
WHERE 1 = 1
— AND aia.invoice_id = ail.invoice_id
AND aps.invoice_id = aia.invoice_id
AND aia.payment_status_flag = ‘N’
— and aia.exchange_rate is not null
AND aia.org_id = ou.organization_id
AND (NVL (aps.amount_remaining, 0) * NVL (aia.exchange_rate, 1)) != 0
ORDER BY invoice_num
SELECT ou.NAME operating_unit, aia.invoice_num invoice,
aia.doc_sequence_value document, ap.vendor_name,
aps.vendor_site_code site, aia.invoice_currency_code currency_code,
aia.invoice_date documentdate, aia.gl_date transdate,
aia.invoice_type_lookup_code invoice_type, 0 debitamount,
NULL offsetaccountdisplayvalue, aia.invoice_amount credit_amount,
gcc.concatenated_segments accountdisplayvalue
FROM ap_invoices_all aia,
gl_code_combinations_kfv gcc,
— ap_invoice_lines_all ail,
ap_payment_schedules_all aps,
hr_operating_units ou,
ap_suppliers ap,
ap_supplier_sites_all aps
WHERE 1 = 1
— AND aia.invoice_id = ail.invoice_id
AND aps.invoice_id = aia.invoice_id
AND aia.vendor_id = ap.vendor_id
AND aia.vendor_site_id = aps.vendor_site_id
AND aia.payment_status_flag = ‘N’
— and aia.exchange_rate is not null
AND aia.accts_pay_code_combination_id = gcc.code_combination_id
AND aia.org_id = ou.organization_id
AND (NVL (aps.amount_remaining, 0) * NVL (aia.exchange_rate, 1)) != 0
ORDER BY invoice_num
SELECT ou.NAME operating_unit, aia.invoice_num invoice,
aia.doc_sequence_value document, ap.vendor_name,
aps.vendor_site_code site, aia.invoice_currency_code currency_code,
aia.invoice_date documentdate, aia.gl_date transdate,
0 debitamount,
NULL offsetaccountdisplayvalue, aia.invoice_amount credit_amount,
gcc.concatenated_segments accountdisplayvalue
FROM ap_invoices_all aia,
gl_code_combinations_kfv gcc,
— ap_invoice_lines_all ail,
ap_payment_schedules_all aps,
hr_operating_units ou,
ap_suppliers ap,
ap_supplier_sites_all aps
WHERE 1 = 1
— AND aia.invoice_id = ail.invoice_id
AND aps.invoice_id = aia.invoice_id
AND aia.vendor_id = ap.vendor_id
AND aia.vendor_site_id = aps.vendor_site_id
AND aia.payment_status_flag = ‘N’
— and aia.exchange_rate is not null
AND aia.accts_pay_code_combination_id = gcc.code_combination_id
AND aia.org_id = ou.organization_id
and aia.org_id in (84,85,86)
AND (NVL (aps.amount_remaining, 0) * NVL (aia.exchange_rate, 1)) != 0
ORDER BY invoice_num
