1 Reply Latest reply: Dec 8, 2012 9:35 PM by sb92075 RSS

    Duplicate invoice. Query help needed

    user790117-Oracle
      Hi All,

      We need to build a query which addresses the following condition
      Some of the invoices were created against the wrong vendors and that money needs to be recovered. Some of them are already recovered while some are not
      * list out all the duplicate invoices between a particular date range(invoice date) with same invoice num ,same invoice amount and same invoice date.
      * If there are any debit invoice created(invoice number postfixed with CR or ADJ),those particualr invoices should not be included.
      The table below shows two type of invoice, the one with bold(invoice num=193666) has debit invoice created and all the three records related to it should not be a part of output data
      while the record(invoice num=00017321) should be a part of output data.

      INVOICE_NUM     INVOICE_AMOUNT     VENDOR_NUMBER     INVOICE_DATE     ORG_ID
      00017321     233.35     A321     1-Dec-11     95
      00017321     233.35     K452     1-Dec-11     95
      *193666     101.67     EM9B     18-May-12     91*
      *193666     101.67     1B02     18-May-12     91*
      *193666CR     -101.67     1B02     18-May-12     91*


      Below is the query which i wrote to identify the duplicate invoice


      select distinct
      a1.invoice_amount,
      a1.invoice_num,
      (select segment1 from apps.po_vendors where vendor_id=a1.vendor_id) vendor_name,
      a1.invoice_date,
      A1.ORG_ID
      from apps.ap_invoices_all a1,
      apps.ap_invoices_all a2
      where 1=1
      and a1.org_id in (91,95)
      and a1.org_id in (91,95)
      and a1.invoice_date between (to_DATE('01-SEP-2011','DD-MON-YYYY')) AND (to_DATE('01-JUN-2012','DD-MON-YYYY'))
      and a2.invoice_date between (to_DATE('01-SEP-2011','DD-MON-YYYY')) AND (to_DATE('01-JUN-2012','DD-MON-YYYY'))
      and a2.invoice_num=a1.invoice_num
      and a1.invoice_amount=a2.invoice_amount
      and a1.invoice_id<>a2.invoice_id
      and a1.invoice_amount<>0
      order by a1.invoice_amount,a1.invoice_num

      Can anybody share their thoughts on how to modify the query above which checks if there are any debit invoice created and not include in the final output.

      Thanks in advance