Overview: Query to fetch prepayment details based on certain condition as written in where clause to display prepayment invoice number and related data as written in Oracle R12 Application.
SELECT /aid1.ROWID row_id, aid1.invoice_id invoice_id, aid1.invoice_distribution_id invoice_distribution_id, aid1.prepay_distribution_id prepay_distribution_id, aid2.distribution_line_number prepay_dist_number,/
/aid1.dist_code_combination_id dist_code_combination_id, aid2.accounting_date accounting_date, aid1.period_name period_name, aid1.set_of_books_id set_of_books_id, aid1.description description, aid1.po_distribution_id po_distribution_id, aid1.rcv_transaction_id rcv_transaction_id, aid1.org_id org_id, ai.invoice_num invoice_num, ai.vendor_id vendor_id, ai.vendor_site_id vendor_site_id, aid2.invoice_id prepay_id,/
ai2.invoice_num prepayment_invoice_num,
ai2.invoice_id prepayment_invoice_id,
ai2.invoice_date prepay_date,
ai2.invoice_currency_code prepay_currency,
ai2.invoice_amount Prepay_original_amount,
SUM ( (-1) * NVL (aid1.amount, 0)) prepay_amount_applied,
(ai2.invoice_amount - (SUM ( (-1) * NVL (aid1.amount, 0))))
prepay_remaining_amt,
pv.vendor_name,
pv.segment1 Vendor_num,
povs.vendor_site_code,
HOU.name Operating_Unit
FROM ap_invoices_all ai,
ap_invoice_distributions_all aid1,
ap_invoice_distributions_all aid2,
ap_invoices_all ai2,
po_vendors pv,
po_vendor_sites_all povs,
hr_operating_units hou
WHERE aid1.prepay_distribution_id(+) = aid2.invoice_distribution_id
AND ai.invoice_id(+) = aid1.invoice_id
AND aid1.amount(+) < 0 AND NVL (aid1.reversal_flag(+), 'N') != 'Y' AND aid1.line_type_lookup_code(+) = 'PREPAY' AND ai.invoice_type_lookup_code(+) NOT IN ('PREPAYMENT', 'CREDIT', 'DEBIT') AND ai2.invoice_id = aid2.invoice_id AND povs.vendor_id = pv.vendor_id AND ai2.vendor_id = pv.vendor_id AND ai2.vendor_site_id = povs.vendor_site_id AND ai2.org_id = hou.organization_id AND ai2.invoice_type_lookup_code = 'PREPAYMENT' AND ai2.invoice_date >= TO_DATE ('01-Apr-2016', 'DD-Mon-RRRR')
GROUP BY ai2.invoice_num,
ai2.invoice_id,
ai2.invoice_date,
ai2.invoice_currency_code,
ai2.invoice_amount,
pv.vendor_name,
pv.segment1,
povs.vendor_site_code,
HOU.name