Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2012
    Posts
    1

    Unanswered: Duplicate invoice. Query help needed

    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 red color(invoice num=193666) has debit invocie 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

  2. #2
    Join Date
    Jan 2013
    Location
    Woodland Hills, CA
    Posts
    18

    Duplicate invoice. Query help needed

    vab_o1984,

    I took the rows that are in your post and created a table that contained your data in a table called dbo.tblTest on my box using the same column names you specified in your example. Given that; try this:

    select t1.*, crAdjInvoices.invoice_num from dbo.tblTest t1
    left join
    (
    select
    left(cr.invoice_num, len(cr.invoice_num) - 2) as invoice_num
    from dbo.tblTest cr
    where
    right(cr.invoice_num, 2) = 'CR'
    or right(cr.invoice_num, 3) = 'ADJ'
    ) crAdjInvoices
    on crAdjInvoices.invoice_num = replace(replace(t1.invoice_num, 'cr', ''), 'adj', '')
    where
    crAdjInvoices.invoice_num is null


    Hope this works for you.
    YesAgile

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •