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

Pages

Wednesday, 1 February 2017

Query to get AP Invoice Audit 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 c.VENDOR_TYPE_LOOKUP_CODE NOT IN
                ('INTERCOMPANY', 'TAX AUTHORITY', 'EMPLOYEE')
         AND a.vendor_id = c.vendor_id
         AND a.invoice_date = b.invoice_date
         AND a.invoice_amount = b.invoice_amount
         --- AND a.creation_date BETWEEN TO_DATE (
         --                                        :P_FROM_DATE,
         --                                        'YYYY/MM/DD HH24:MI:SS')
         --                                 AND TO_DATE (
         --                                       :P_TO_DATE,
         --                                       'YYYY/MM/DD HH24:MI:SS')+.99999
         --and a.cancelled_date is null
         --and a.invoice_num like '%117693%'
         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