REPAY INVOICE DETAILS

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,
(-1) * aid1.amount prepay_amount_applied,
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

FROM
ap_invoices_all ai,
ap_invoice_distributions_all aid1,
ap_invoice_distributions_all aid2,
ap_invoices_all ai2

WHERE 1 = 1
AND 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