Tuesday, September 17, 2013

Query to display duplicate invoices for the same(po,shipment,location)

DECLARE
   CURSOR c1
   IS
      SELECT   dup.description || dup.loc total
          FROM (SELECT ai.invoice_num
                      ,ai.description
                      ,(SELECT SUBSTR(aid.description, INSTR(aid.description, 'Loc=', 1), 8)
                          FROM ap_invoice_distributions_all aid
                         WHERE ROWNUM = 1 AND invoice_id = ai.invoice_id) loc
                  FROM ap_invoices_all ai
                 WHERE SOURCE = 'EDI 810'
                   AND TRUNC(ai.creation_date) >= '01-JAN-2013'
                   AND ai.invoice_num NOT LIKE '%QADJ'
                   AND ai.invoice_type_lookup_code = 'STANDARD'
                   AND ai.invoice_num NOT LIKE '%RA'
                   AND ai.description IN(
                          SELECT   description
                              FROM ap_invoices_all
                             WHERE SOURCE = 'EDI 810'
                               AND invoice_num NOT LIKE '%QADJ'
                               AND invoice_type_lookup_code = 'STANDARD'
                               AND invoice_num NOT LIKE '%RA'
                               AND TRUNC(creation_date) >= '01-JAN-2013'
                          GROUP BY description
                            HAVING COUNT(description) > 1) ) dup
      GROUP BY dup.description || dup.loc
        HAVING COUNT(dup.description || dup.loc) > 1;

   CURSOR c2(
      p_desc                     IN       VARCHAR2)
   IS
      SELECT invoice_num
        FROM ap_invoices_all
       WHERE SOURCE IN('EDI 810', 'RETEK ZELLERS') AND description IN(p_desc) AND TRUNC(creation_date) >= '01-JAN-2010';

   CURSOR c3(
      p_inv                      IN       VARCHAR2)
   IS
      SELECT *
        FROM ap_invoices_all
       WHERE invoice_num LIKE p_inv || '%' AND SOURCE IN('EDI 810', 'RETEK ZELLERS') AND TRUNC(creation_date) >= '01-JAN-2010';
BEGIN
   FOR rec_c1 IN c1
   LOOP
      FOR rec_c2 IN c2(SUBSTR(rec_c1.total, 1, INSTR(rec_c1.total, 'Loc=', 1) - 1) )
      LOOP
         FOR rec_c3 IN c3(rec_c2.invoice_num)
         LOOP
            DBMS_OUTPUT.put_line('Invoice_num=' || rec_c3.invoice_num);
         END LOOP;
      END LOOP;
   END LOOP;
END;