Duplicate invoice. Query help needed

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

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
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
[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
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.

I hope i have posted it in right section. If it is wrong, requesting Moderator(s) to guide me..
Thanks in advance
vab_o1984
Member
 
Posts: 1
Joined: Sun Dec 09, 2012 5:37 pm

Re: Duplicate invoice. Query help needed

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

Hi.

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'))
AND    NOT EXISTS (SELECT 1
                   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
HAVING COUNT(*) = 2;


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.

Cheers

Tim...
Tim...
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: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17933
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 2 guests