Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Monday, 10 July 2017

Query to Get AP Invoice Duplicate Audit Details in Oracle Applications


Following query pull out the details of  Account Payable Duplicate invoice details  



SELECT DISTINCT a.invoice_id,
                  c.vendor_name,
                  c.segment1 vendor_numder,
                  a.invoice_num,
                  a.invoice_amount,
                  a.amount_paid,
                  TO_CHAR (a.invoice_date, 'DD-MON-YYYY') invoice_date,
                  a.payment_status_flag,
                  (SELECT user_name
                     FROM apps.fnd_user
                    WHERE user_id = a.created_by)
                     inv_created_by,
                  TO_CHAR (a.creation_date, 'DD-MON-YYYY') creation_date,
                  TO_CHAR (a.CANCELLED_DATE, 'DD-MON-YYYY') CANCELLED_DATE,
                  (SELECT name
                     FROM apps.hr_operating_units
                    WHERE organization_id = a.org_id)
                     operating_unit
    FROM apps.ap_invoices_all a, apps.ap_invoices_all b, apps.ap_suppliers c
   WHERE     a.vendor_id = b.vendor_id
         AND a.invoice_id <> b.invoice_id
         AND a.vendor_id = c.vendor_id
         AND a.invoice_date = b.invoice_date
         AND a.invoice_amount = b.invoice_amount
         AND (INSTR (a.invoice_num, b.invoice_num) >= 1
              OR INSTR (b.invoice_num, a.invoice_num) >= 1)
ORDER BY a.invoice_num

Share this article :

0 comments:

Post a Comment