Duplicate invoice. Query help needed

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Posts: 1
Joined: Sun Dec 09, 2012 5:37 pm

Duplicate invoice. Query help needed

Postby vab_o1984 » Sun Dec 09, 2012 5:41 pm

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.

Code: Select all

00017321   233.35   A321   1-Dec-11   95
00017321   233.35   K452   1-Dec-11   95
[color=#FF0000]193666   101.67   EM9B   18-May-12   91
193666   101.67   1B02   18-May-12   91
193666CR   -101.67   1B02   18-May-12   91[/color]

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

Code: Select all

select distinct
(select segment1 from apps.po_vendors where vendor_id=a1.vendor_id) vendor_name,
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.

I hope i have posted it in right section. If it is wrong, requesting Moderator(s) to guide me..
Thanks in advance

Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Duplicate invoice. Query help needed

Postby Tim... » Mon Dec 10, 2012 9:21 am


I would probably identify the duplicates like this:

Code: Select all

SELECT invoice_num, invoice_amount, invoice_date, COUNT(*) AS recs
FROM   apps.ap_invoices_all a1
WHERE  a1.invoice_date between (TO_DATE('01-SEP-2011','DD-MON-YYYY')) AND (TO_DATE('01-JUN-2012','DD-MON-YYYY'))
                   FROM   apps.ap_invoices_all a2
                   WHERE  a1.invoice_date = a2.invoice_date
                   AND    a2.invoice_number IN (a1.invoice_num || 'CR', a1.invoice_num || 'ADJ')
                   AND    a1.invoice_amount = ABS(a2.invoice_amount))
GROUP BY invoice_num, invoice_amount, invoice_date

You could use that as an inline view and join against the table to get the actual data.

Note. You sent no DDL+DML to build the test case, so I can't run or test this.


Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: https://oracle-base.com
My blog: https://oracle-base.com/blog

Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 1 guest