Tuesday 8 July 2014

Prepayment Query in Oracle

/* Formatted on 7/8/2014 8:51:15 AM (QP5 v5.115.810.9015) */
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

1 comment: