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;
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;