Home » SQL & PL/SQL » SQL & PL/SQL » To tune a PL/SQL block (12c, 11g )
| To tune a PL/SQL block [message #639615] |
Sun, 12 July 2015 12:59  |
 |
13478
Messages: 2
Registered: July 2015
|
Junior Member |
|
|
Hello,
I have PL/SQL block, it runs, but runs slow,
The way to tune it, I think, may use BULK COLLECT.
while, after using BULK COLLECT (for cursor DOCUMENTS), got error:
Error report:
ORA-06550: line 375, column 39:
PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
How could I fix it?
Is there any other way to tune this PL/SQL block?
thank you very much in advance!
original PL/SQL
SET serveroutput ON;
DECLARE
lrefd_id VARCHAR2(75);
lrefd_dtyp VARCHAR2(75);
lrefd_doc_num VARCHAR2(75);
lrefd_itmz_lnum CHAR(5);
lrefd_actg_lnum CHAR(5);
laccd_tlna NUMBER(27, 6);
laccd_flna NUMBER(27, 6);
laccd_lnam NUMBER(27, 6);
laccd_qy NUMBER;
linvd_tlna NUMBER(27, 6);
linvd_flna NUMBER(27, 6);
linvd_lnam NUMBER(27, 6);
linvd_qy NUMBER;
calc_invd_tlna NUMBER(27, 6);
calc_invd_flna NUMBER(27, 6);
calc_invd_lnam NUMBER(27, 6);
calc_accd_tlna NUMBER(27, 6);
calc_accd_flna NUMBER(27, 6);
calc_accd_lnam NUMBER(27, 6);
calc_exnd_tlna NUMBER(27, 6);
calc_exnd_flna NUMBER(27, 6);
calc_exnd_lnam NUMBER(27, 6);
calc_accd_qy NUMBER;
calc_exnd_qy NUMBER;
calc_invd_qy NUMBER;
temp_invd_tlna NUMBER(27, 6);
temp_invd_flna NUMBER(27, 6);
temp_invd_lnam NUMBER(27, 6);
temp_accd_tlna NUMBER(27, 6);
temp_accd_flna NUMBER(27, 6);
temp_accd_lnam NUMBER(27, 6);
temp_exnd_tlna NUMBER(27, 6);
temp_exnd_flna NUMBER(27, 6);
temp_exnd_lnam NUMBER(27, 6);
temp_accd_qy NUMBER;
temp_exnd_qy NUMBER;
temp_invd_qy NUMBER;
lrefg_id VARCHAR2(75);
liq_items_count NUMBER;
temp_liq_items_count NUMBER;
match_count NUMBER;
count1 NUMBER;
count2 NUMBER;
count3 NUMBER;
count4 NUMBER;
count5 NUMBER;
count6 NUMBER;
count7 NUMBER;
count8 NUMBER;
count9 NUMBER;
count10 NUMBER;
count11 NUMBER;
count12 NUMBER;
count13 NUMBER;
error_count1 NUMBER;
error_count2 NUMBER;
error_count3 NUMBER;
error_count4 NUMBER;
bprelim BOOLEAN;
CURSOR documents IS
SELECT l.uidy,
Substr(l.parn_of_line_id, Instr(l.parn_of_line_id, '&', 1, 3) + 1,
( Instr(l.parn_of_line_id, '&', 1, 4) -
Instr(l.parn_of_line_id, '&', 1, 3) - 1
)),
Substr(l.parn_of_line_id, Instr(l.parn_of_line_id, '&', 1, 4) + 1,
( Instr(l.parn_of_line_id, '&', 1, 5) -
Instr(l.parn_of_line_id, '&', 1, 4) - 1
)),
'0',
To_char(l.lnum),
l.accd_tlna,
l.accd_flna,
l.accd_lnam,
invd_tlna,
invd_flna,
invd_lnam,
l.invc_qy,
l.accd_qy
FROM mf_qo_itmz_ln l
WHERE l.uidy IN (((SELECT refd_line_id
FROM mf_vi_itmz_ln
WHERE Substr(refd_line_id, Instr(refd_line_id, '&', 1
, 4)
+
1, (
Instr(refd_line_id, '&', 1, 5) -
Instr(refd_line_id, '&', 1, 4)
- 1 ))
IN (SELECT cd
FROM mf_doc_type
WHERE dtyp_cat = ( 'QO' ))
AND invc_accr_fl = 'T')
UNION
(SELECT refd_line_id
FROM mf_vi_actg_ln iv2
WHERE Substr(refd_line_id, Instr(refd_line_id, '&', 1
, 4)
+
1, (
Instr(refd_line_id, '&', 1, 5) -
Instr(refd_line_id, '&', 1, 4)
- 1 ))
IN (SELECT cd
FROM mf_doc_type
WHERE dtyp_cat = ( 'QO' ))
AND invc_accr_fl = 'T')
UNION
(SELECT refd_line_id
FROM mf_vi_itmz_ln iv2
WHERE Substr(refd_line_id, Instr(refd_line_id, '&', 1
, 4)
+
1, (
Instr(refd_line_id, '&', 1, 5) -
Instr(refd_line_id, '&', 1, 4)
- 1 ))
IN (SELECT cd
FROM mf_doc_type
WHERE dtyp_cat = ( 'QO' ))
AND invc_accr_fl = 'T')
UNION
(SELECT refd_line_id
FROM mf_ti_ln ti
WHERE Substr(refd_line_id, Instr(refd_line_id, '&', 1
, 4)
+
1, (
Instr(refd_line_id, '&', 1, 5) -
Instr(refd_line_id, '&', 1, 4)
- 1 ))
IN (SELECT cd
FROM mf_doc_type
WHERE dtyp_cat = ( 'QO' ))
AND invc_accr_fl = 'T')
UNION
(SELECT refd_line_id
FROM mf_ii_actg_ln ii
WHERE Substr(refd_line_id, Instr(refd_line_id, '&', 1
, 4)
+
1, (
Instr(refd_line_id, '&', 1, 5) -
Instr(refd_line_id, '&', 1, 4)
- 1 ))
IN (SELECT cd
FROM mf_doc_type
WHERE dtyp_cat = ( 'QO' ))
AND invc_accr_fl = 'T')
UNION
(SELECT refd_line_id
FROM mf_ii_itmz_ln ii
WHERE Substr(refd_line_id, Instr(refd_line_id, '&', 1
, 4)
+
1, (
Instr(refd_line_id, '&', 1, 5) -
Instr(refd_line_id, '&', 1, 4)
- 1 ))
IN (SELECT cd
FROM mf_doc_type
WHERE dtyp_cat = ( 'QO' ))
AND invc_accr_fl = 'T')
UNION
(SELECT refd_line_id
FROM mf_ii_hdal ii2
WHERE Substr(refd_line_id, Instr(refd_line_id, '&', 1
, 4)
+
1, (
Instr(refd_line_id, '&', 1, 5) -
Instr(refd_line_id, '&', 1, 4)
- 1 ))
IN (SELECT cd
FROM mf_doc_type
WHERE dtyp_cat = ( 'QO' ))
AND invc_accr_fl = 'T')));
CURSOR postings IS
(SELECT pymt_ta AS ACCD_TLNA,
pymt_fa AS ACCD_FLNA,
pymt_am AS ACCD_LNAM,
exnd_tlna,
exnd_flna,
exnd_lnam,
exnd_qy,
To_number(pymt_qy) AS ACCD_QY,
invd_tlna,
invd_flna,
invd_lnam,
invc_qy
FROM mf_vi_itmz_ln iv2
WHERE refd_line_id = lrefd_id
AND invc_accr_fl = 'T'
UNION
SELECT 0,
0,
0,
0,
0,
0,
0,
0,
invd_tlna,
invd_flna,
invd_lnam,
invc_qy
FROM mf_vi_itmz_ln iv2
WHERE refd_line_id = lrefd_id
AND invc_accr_fl = 'F'
UNION
SELECT pymt_ta AS ACCD_TLNA,
pymt_fa AS ACCD_FLNA,
pymt_am AS ACCD_LNAM,
exnd_tlna,
exnd_flna,
exnd_lnam,
exnd_qy,
To_number(pymt_qy) AS ACCD_QY,
invd_tlna,
invd_flna,
invd_lnam,
invc_qy
FROM mf_ii_itmz_ln iv2
WHERE refd_line_id = lrefd_id
AND invc_accr_fl = 'T'
UNION
SELECT 0,
0,
0,
0,
0,
0,
0,
0,
invd_tlna,
invd_flna,
invd_lnam,
invc_qy
FROM mf_ii_itmz_ln iv2
WHERE refd_line_id = lrefd_id
AND invc_accr_fl = 'F'
UNION
SELECT accd_tlna,
accd_flna,
accd_lnam,
exnd_tlna,
exnd_flna,
exnd_lnam,
exnd_qy,
accd_qy,
0,
0,
0,
0
FROM mf_ic_itmz_ln iv2
WHERE refd_line_id = lrefd_id
AND Substr(parn_of_line_id, Instr(parn_of_line_id, '&', 1, 3) + 1,
(
Instr(
parn_of_line_id, '&', 1, 4) -
Instr(parn_of_line_id, '&', 1, 3)
- 1 ))
IN (SELECT cd
FROM mf_doc_type
WHERE dtyp_cat = 'IC') -- exclude AI
);
CURSOR cnt_liquidate_items IS
SELECT Count(uidy)
FROM (SELECT uidy
FROM mf_ic_hdal
WHERE Substr(refd_line_id, Instr(refd_line_id, '&', 1, 4) + 1, (
Instr(refd_line_id, '&', 1, 5) -
Instr(refd_line_id, '&', 1, 4)
- 1 )) =
lrefd_dtyp
AND Substr(refd_line_id, Instr(refd_line_id, '&', 1, 5) + 1
, (
Instr(refd_line_id, '&', 1, 6) -
Instr(refd_line_id, '&', 1, 5)
- 1 )) =
lrefd_doc_num
AND Substr(refd_line_id, Instr(refd_line_id, '&', 1, 7) + 1
, (
Instr(refd_line_id, '&', 1, 8) -
Instr(refd_line_id, '&', 1, 7)
- 1 )) =
Trim(lrefd_actg_lnum)
AND Substr(parn_of_line_id, Instr(parn_of_line_id, '&', 1,
3) +
1
, (
Instr(
parn_of_line_id, '&', 1, 4) -
Instr(parn_of_line_id, '&', 1, 3)
- 1 ))
IN (SELECT cd
FROM mf_doc_type
WHERE dtyp_cat = 'IC') -- exclude AI
AND ref_liqd_trms = 'T'
UNION
SELECT uidy
FROM mf_iv_hdal
WHERE Substr(refd_line_id, Instr(refd_line_id, '&', 1, 4) + 1, (
Instr(refd_line_id, '&', 1, 5) -
Instr(refd_line_id, '&', 1, 4)
- 1 )) =
lrefd_dtyp
AND Substr(refd_line_id, Instr(refd_line_id, '&', 1, 5) + 1
, (
Instr(refd_line_id, '&', 1, 6) -
Instr(refd_line_id, '&', 1, 5)
- 1 )) =
lrefd_doc_num
AND Substr(refd_line_id, Instr(refd_line_id, '&', 1, 7) + 1
, (
Instr(refd_line_id, '&', 1, 8) -
Instr(refd_line_id, '&', 1, 7)
- 1 )) =
Trim(lrefd_actg_lnum)
AND invc_accr_fl = 'T'
AND ref_liqd_trms = 'T');
BEGIN
-- script variables, adjust these based on when the parameters for the inital conversion job
bprelim := TRUE;
-- change this value to false to have the script perform updates
count1 := 0;
count2 := 0;
count3 := 0;
count4 := 0;
count5 := 0;
count6 := 0;
count7 := 0;
count8 := 0;
count9 := 0;
count10 := 0;
count11 := 0;
count12 := 0;
count13 := 0;
error_count1 := 0;
error_count2 := 0;
error_count3 := 0;
error_count4 := 0;
IF bprelim = TRUE THEN
dbms_output.Put_line('Script executed in Preliminary Mode (No Updates Performed)');
ELSE
dbms_output.Put_line('Script executed in Final Mode');
END IF;
OPEN documents;
dbms_output.ENABLE(NULL);
LOOP
FETCH documents INTO lrefd_id, lrefd_dtyp, lrefd_doc_num, lrefd_itmz_lnum,
lrefd_actg_lnum, laccd_tlna, laccd_flna, laccd_lnam, linvd_tlna, linvd_flna,
linvd_lnam, linvd_qy, laccd_qy;
EXIT WHEN documents%NOTFOUND;
laccd_tlna := Coalesce(laccd_tlna, 0);
laccd_flna := Coalesce(laccd_flna, 0);
laccd_lnam := Coalesce(laccd_lnam, 0);
laccd_qy := Coalesce(laccd_qy, 0);
linvd_tlna := Coalesce(linvd_tlna, 0);
linvd_flna := Coalesce(linvd_flna, 0);
linvd_lnam := Coalesce(linvd_lnam, 0);
linvd_qy := Coalesce(linvd_qy, 0);
dbms_output.Put_line('Processing document: '
|| lrefd_dtyp
|| ' '
|| lrefd_doc_num
|| ' '
|| lrefd_itmz_lnum
|| ' '
|| lrefd_actg_lnum
|| ' with accepted amount '
|| laccd_tlna
|| ' / '
|| laccd_flna
|| ' / '
|| laccd_lnam
|| ' Accepted quantity: '
|| laccd_qy
|| ' and invoiced amount '
|| linvd_tlna
|| ' / '
|| linvd_flna
|| ' / '
|| linvd_lnam
|| ' Invoiced quantity: '
|| linvd_qy
|| ' ...');
count1 := count1 + 1;
calc_invd_tlna := 0.00;
calc_invd_flna := 0.00;
calc_invd_lnam := 0.00;
calc_accd_tlna := 0.00;
calc_accd_flna := 0.00;
calc_accd_lnam := 0.00;
calc_exnd_tlna := 0.00;
calc_exnd_flna := 0.00;
calc_exnd_lnam := 0.00;
calc_accd_qy := 0.00;
calc_exnd_qy := 0.00;
calc_invd_qy := 0.00;
match_count := 0;
OPEN postings;
LOOP
FETCH postings INTO temp_accd_tlna, temp_accd_flna, temp_accd_lnam,
temp_exnd_tlna, temp_exnd_flna, temp_exnd_lnam, temp_exnd_qy,
temp_accd_qy,
temp_invd_tlna, temp_invd_flna, temp_invd_lnam, temp_invd_qy;
EXIT WHEN postings%NOTFOUND;
calc_invd_tlna := calc_invd_tlna + temp_invd_tlna;
calc_invd_flna := calc_invd_flna + temp_invd_flna;
calc_invd_lnam := calc_invd_lnam + temp_invd_lnam;
calc_accd_tlna := calc_accd_tlna + temp_accd_tlna;
calc_accd_flna := calc_accd_flna + temp_accd_flna;
calc_accd_lnam := calc_accd_lnam + temp_accd_lnam;
calc_exnd_tlna := calc_exnd_tlna + temp_exnd_tlna;
calc_exnd_flna := calc_exnd_flna + temp_exnd_flna;
calc_exnd_lnam := calc_exnd_lnam + temp_exnd_lnam;
calc_accd_qy := calc_accd_qy + temp_accd_qy;
calc_exnd_qy := calc_exnd_qy + temp_exnd_qy;
calc_invd_qy := calc_invd_qy + temp_invd_qy;
match_count := match_count + 1;
--DBMS_OUTPUT.PUT_LINE(' Refg doc: ' || lrefg_id || ' - ' || to_char(COALESCE(temp_accd_tlna, 0)) || ' / ' || to_char(COALESCE(temp_accd_flna, 0)) || ' / ' || to_char( COALESCE(temp_accd_lnam, 0)) || ' / ' || to_char(COALESCE(temp_exnd_tlna, 0)) || ' / ' || to_char(COALESCE(temp_exnd_flna, 0)) || ' / ' || to_char(COALESCE(temp_exnd_lnam, 0)) || ' / ' || to_char(COALESCE(temp_exnd_qy, 0)) || ' / ' || to_char(COALESCE(temp_accd_qy, 0)) );
END LOOP;
CLOSE postings;
IF match_count > 0 THEN
-- check to see if there are any referencing lines that are marked to liquidate items
liq_items_count := 0;
OPEN cnt_liquidate_items;
LOOP
FETCH cnt_liquidate_items INTO temp_liq_items_count;
EXIT WHEN cnt_liquidate_items%NOTFOUND;
liq_items_count := temp_liq_items_count;
--DBMS_OUTPUT.PUT_LINE(' Liquidate Items Count: ' || liq_items_count);
END LOOP;
CLOSE cnt_liquidate_items;
-- accepted amount
IF ( Coalesce(calc_accd_tlna, 0) - Coalesce(calc_exnd_tlna, 0) ) =
Coalesce(
laccd_tlna, 0)
AND ( Coalesce(calc_accd_flna, 0) - Coalesce(calc_exnd_flna, 0) ) =
Coalesce(
laccd_flna, 0)
AND ( Coalesce(calc_accd_lnam, 0) - Coalesce(calc_exnd_lnam, 0) ) =
Coalesce(
laccd_lnam, 0) THEN
count2 := count2 + 1;
dbms_output.Put_line(' Calculated accepted amount matches the current accepted amount on the document. No Update Required.');
ELSE
IF liq_items_count > 0 THEN
error_count1 := error_count1 + 1;
dbms_output.Put_line(' Line: '
|| lrefd_dtyp
|| ' '
|| lrefd_doc_num
|| ' '
|| lrefd_itmz_lnum
|| ' '
|| lrefd_actg_lnum
||
' is referenced by lines using liquidate item functionally. Accepted amount will need to be manually computed.'
);
ELSIF ( Coalesce(calc_accd_tlna, 0) - Coalesce(calc_exnd_tlna, 0) ) < 0
OR ( Coalesce(calc_accd_flna, 0) - Coalesce(calc_exnd_flna, 0) ) < 0
OR ( Coalesce(calc_accd_lnam, 0) - Coalesce(calc_exnd_lnam, 0) ) < 0 THEN
error_count1 := error_count1 + 1;
dbms_output.Put_line(' Line: '
|| lrefd_dtyp
|| ' '
|| lrefd_doc_num
|| ' '
|| lrefd_itmz_lnum
|| ' '
|| lrefd_actg_lnum
|| ' calculates a negative accepted amount: '
|| ( Coalesce(calc_accd_tlna, 0) -
Coalesce(calc_exnd_tlna, 0) )
|| ' / '
|| ( Coalesce(calc_accd_flna, 0) -
Coalesce(calc_exnd_flna, 0) )
|| ' / '
|| ( Coalesce(calc_accd_lnam, 0) -
Coalesce(calc_exnd_lnam, 0) )
|| ' Accepted amount will need to be manually computed.'
);
ELSE
count3 := count3 + 1;
dbms_output.Put_line(' Calculated accepted amount: '
|| ( Coalesce(calc_accd_tlna, 0) -
Coalesce(calc_exnd_tlna, 0) )
|| ' / '
|| ( Coalesce(calc_accd_flna, 0) -
Coalesce(calc_exnd_flna, 0) )
|| ' / '
|| ( Coalesce(calc_accd_lnam, 0) -
Coalesce(calc_exnd_lnam, 0) )
||
' does not match accepted amount on document. Update Required.');
IF bprelim = FALSE THEN
UPDATE mf_qo_itmz_ln
SET accd_tlna = ( Coalesce(calc_accd_tlna, 0) -
Coalesce(calc_exnd_tlna, 0)
)
,
accd_flna = ( Coalesce(calc_accd_flna, 0) - Coalesce(
calc_exnd_flna, 0) )
,
accd_lnam = ( Coalesce(calc_accd_lnam, 0) - Coalesce(
calc_exnd_lnam, 0) )
WHERE uidy = lrefd_id;
count4 := count4 + 1;
END IF;
END IF;
END IF;
-- accepted quantity
IF ( Coalesce(calc_accd_qy, 0) - Coalesce(calc_exnd_qy, 0) ) =
Coalesce(laccd_qy, 0) THEN
count8 := count8 + 1;
dbms_output.Put_line(' Calculated accepted quantity matches the current accepted quantity on the document. No Update Required.');
ELSE
IF liq_items_count > 0 THEN
error_count2 := error_count2 + 1;
dbms_output.Put_line(' Line: '
|| lrefd_dtyp
|| ' '
|| lrefd_doc_num
|| ' '
|| lrefd_itmz_lnum
|| ' '
|| lrefd_actg_lnum
||
' is referenced by lines using liquidate item functionally. Accepted quantity will need to be manually computed.'
);
ELSIF ( Coalesce(calc_accd_qy, 0) - Coalesce(calc_exnd_qy, 0) ) < 0 THEN
error_count2 := error_count2 + 1;
dbms_output.Put_line(' Line: '
|| lrefd_dtyp
|| ' '
|| lrefd_doc_num
|| ' '
|| lrefd_itmz_lnum
|| ' '
|| lrefd_actg_lnum
|| ' calculates a negative accepted quantity: '
|| ( Coalesce(calc_accd_qy, 0) -
Coalesce(calc_exnd_qy, 0)
)
||
' Accepted quantity will need to be manually computed.'
);
ELSE
count9 := count9 + 1;
dbms_output.Put_line(' Calculated accepted quantity: '
|| ( Coalesce(calc_accd_qy, 0) -
Coalesce(calc_exnd_qy, 0)
)
||
' does not match quantity amount on document. Update Required.');
IF bprelim = FALSE THEN
UPDATE mf_qo_itmz_ln
SET accd_qy = ( Coalesce(calc_accd_qy, 0) - Coalesce(calc_exnd_qy, 0) )
WHERE uidy = lrefd_id;
count10 := count10 + 1;
END IF;
END IF;
END IF;
-- Invoiced amount
IF Coalesce(calc_invd_tlna, 0) = Coalesce(linvd_tlna, 0)
AND Coalesce(calc_invd_flna, 0) = Coalesce(linvd_flna, 0)
AND Coalesce(calc_invd_lnam, 0) = Coalesce(linvd_lnam, 0) THEN
count5 := count5 + 1;
dbms_output.Put_line(' Calculated invoiced amount matches the current invoiced amount on the document. No Update Required.');
ELSE
IF liq_items_count > 0 THEN
error_count3 := error_count3 + 1;
dbms_output.Put_line(' Line: '
|| lrefd_dtyp
|| ' '
|| lrefd_doc_num
|| ' '
|| lrefd_itmz_lnum
|| ' '
|| lrefd_actg_lnum
||
' is referenced by lines using liquidate item functionally. Invoiced amount will need to be manually computed.'
);
ELSIF Coalesce(calc_invd_tlna, 0) < 0
OR Coalesce(calc_invd_flna, 0) < 0
OR Coalesce(calc_invd_lnam, 0) < 0 THEN
error_count3 := error_count3 + 1;
dbms_output.Put_line(' Line: '
|| lrefd_dtyp
|| ' '
|| lrefd_doc_num
|| ' '
|| lrefd_itmz_lnum
|| ' '
|| lrefd_actg_lnum
|| ' calculates a negative invoiced amount: '
|| ( Coalesce(calc_invd_tlna, 0) )
|| ' / '
|| ( Coalesce(calc_invd_flna, 0) )
|| ' / '
|| ( Coalesce(calc_invd_lnam, 0) )
|| 'Invoiced amount will need to be manually computed.');
ELSE
count6 := count6 + 1;
dbms_output.Put_line(' Calculated invoiced amount: '
|| ( Coalesce(calc_invd_tlna, 0) )
|| ' / '
|| ( Coalesce(calc_invd_flna, 0) )
|| ' / '
|| ( Coalesce(calc_invd_lnam, 0) )
||
' does not match invoiced amount on document. Update Required.');
IF bprelim = FALSE THEN
UPDATE mf_qo_itmz_ln
SET invd_tlna = ( Coalesce(calc_invd_tlna, 0) ),
invd_flna = ( Coalesce(calc_invd_flna, 0) ),
invd_lnam = ( Coalesce(calc_invd_lnam, 0) )
WHERE uidy = lrefd_id;
count7 := count7 + 1;
END IF;
END IF;
END IF;
-- Invoiced quantity
IF Coalesce(calc_invd_qy, 0) = Coalesce(linvd_qy, 0) THEN
count11 := count11 + 1;
dbms_output.Put_line(' Calculated invoiced quantity matches the current invoiced quantity on the document. No Update Required.');
ELSE
IF liq_items_count > 0 THEN
error_count4 := error_count4 + 1;
dbms_output.Put_line(' Line: '
|| lrefd_dtyp
|| ' '
|| lrefd_doc_num
|| ' '
|| lrefd_itmz_lnum
|| ' '
|| lrefd_actg_lnum
||
' is referenced by lines using liquidate item functionally. Invoiced quantity will need to be manually computed.'
);
ELSIF Coalesce(calc_invd_qy, 0) < 0 THEN
error_count4 := error_count4 + 1;
dbms_output.Put_line(' Line: '
|| lrefd_dtyp
|| ' '
|| lrefd_doc_num
|| ' '
|| lrefd_itmz_lnum
|| ' '
|| lrefd_actg_lnum
|| ' calculates a negative invoiced quantity: '
|| ( Coalesce(calc_invd_qy, 0) )
|| 'Invoiced quantity will need to be manually computed.'
);
ELSE
count12 := count12 + 1;
dbms_output.Put_line(' Calculated invoiced quantity: '
|| ( Coalesce(calc_invd_qy, 0) )
||
' does not match invoiced quantity on document. Update Required.');
IF bprelim = FALSE THEN
UPDATE mf_qo_itmz_ln
SET invc_qy = ( Coalesce(calc_invd_qy, 0) )
WHERE uidy = lrefd_id;
count13 := count13 + 1;
END IF;
END IF;
END IF;
ELSE
error_count1 := error_count1 + 1;
error_count2 := error_count2 + 1;
error_count3 := error_count3 + 1;
error_count4 := error_count4 + 1;
dbms_output.Put_line(' Error could not calculate accepted/invoiced amount for: '
|| lrefd_dtyp
|| ' '
|| lrefd_doc_num
|| ' '
|| lrefd_itmz_lnum
|| ' '
|| lrefd_actg_lnum);
END IF;
END LOOP;
CLOSE documents;
dbms_output.Put_line('********************************************************************************');
IF bprelim = TRUE THEN
dbms_output.Put_line('Script executed in Preliminary Mode (No Updates Performed)');
ELSE
dbms_output.Put_line('Script executed in Final Mode');
END IF;
dbms_output.Put_line('Number of Potential incorrect records found: '
|| count1);
dbms_output.Put_line('Number of records that were matched to referenced line accepted amount (no Update Required): '
|| count2);
dbms_output.Put_line('Number of records that do NOT match referenced line accepted amount (Update Required): '
|| count3);
dbms_output.Put_line('Number of Accepted amount Updates Performed: '
|| count4);
dbms_output.Put_line('Number of Accepted amount records that could not be matched and need to be examined manually: '
|| error_count1);
dbms_output.Put_line('Number of records that were matched to referenced line accepted quantity (no Update Required): '
|| count8);
dbms_output.Put_line('Number of records that do NOT match referenced line accepted quantity (Update Required): '
|| count9);
dbms_output.Put_line('Number of Accepted quantity Updates Performed: '
|| count10);
dbms_output.Put_line('Number of Accepted quantity records that could not be matched and need to be examined manually: '
|| error_count2);
dbms_output.Put_line('Number of records that were matched to referenced line invoiced amount (no Update Required): '
|| count5);
dbms_output.Put_line('Number of records that do NOT match referenced line invoiced amount (Update Required): '
|| count6);
dbms_output.Put_line('Number of Invoiced amount Updates Performed: '
|| count7);
dbms_output.Put_line('Number of Invoiced amount records that could not be matched and need to be examined manually: '
|| error_count3);
dbms_output.Put_line('Number of records that were matched to referenced line invoiced quantity (no Update Required): '
|| count11);
dbms_output.Put_line('Number of records that do NOT match referenced line invoiced quantity (Update Required): '
|| count12);
dbms_output.Put_line('Number of Invoiced quantity Updates Performed: '
|| count13);
dbms_output.Put_line('Number of Invoiced quantity records that could not be matched and need to be examined manually: '
|| error_count4);
dbms_output.Put_line('********************************************************************************');
END;
/
---PL/SQL of using BULK COLLECT for cursor DOCUMENTS, get error.
SET serveroutput ON;
DECLARE
TYPE documents_info IS RECORD(
lrefd_id VARCHAR2(75),
lrefd_dtyp VARCHAR2(75),
lrefd_doc_num VARCHAR2(75),
lrefd_itmz_lnum CHAR(5),
lrefd_actg_lnum CHAR(5),
laccd_tlna NUMBER(27, 6),
laccd_flna NUMBER(27, 6),
laccd_lnam NUMBER(27, 6),
linvd_tlna NUMBER(27, 6),
linvd_flna NUMBER(27, 6),
linvd_lnam NUMBER(27, 6),
linvd_qy NUMBER,
laccd_qy NUMBER);
documents_rec DOCUMENTS_INFO;
calc_invd_tlna NUMBER(27, 6);
calc_invd_flna NUMBER(27, 6);
calc_invd_lnam NUMBER(27, 6);
calc_accd_tlna NUMBER(27, 6);
calc_accd_flna NUMBER(27, 6);
calc_accd_lnam NUMBER(27, 6);
calc_exnd_tlna NUMBER(27, 6);
calc_exnd_flna NUMBER(27, 6);
calc_exnd_lnam NUMBER(27, 6);
calc_accd_qy NUMBER;
calc_exnd_qy NUMBER;
calc_invd_qy NUMBER;
temp_invd_tlna NUMBER(27, 6);
temp_invd_flna NUMBER(27, 6);
temp_invd_lnam NUMBER(27, 6);
temp_accd_tlna NUMBER(27, 6);
temp_accd_flna NUMBER(27, 6);
temp_accd_lnam NUMBER(27, 6);
temp_exnd_tlna NUMBER(27, 6);
temp_exnd_flna NUMBER(27, 6);
temp_exnd_lnam NUMBER(27, 6);
temp_accd_qy NUMBER;
temp_exnd_qy NUMBER;
temp_invd_qy NUMBER;
lrefg_id VARCHAR2(75);
liq_items_count NUMBER;
temp_liq_items_count NUMBER;
match_count NUMBER;
count1 NUMBER;
count2 NUMBER;
count3 NUMBER;
count4 NUMBER;
count5 NUMBER;
count6 NUMBER;
count7 NUMBER;
count8 NUMBER;
count9 NUMBER;
count10 NUMBER;
count11 NUMBER;
count12 NUMBER;
count13 NUMBER;
error_count1 NUMBER;
error_count2 NUMBER;
error_count3 NUMBER;
error_count4 NUMBER;
bprelim BOOLEAN;
CURSOR documents IS
SELECT l.uidy,
Substr(l.parn_of_line_id, Instr(l.parn_of_line_id, '&', 1, 3) + 1,
( Instr(l.parn_of_line_id, '&', 1, 4) -
Instr(l.parn_of_line_id, '&', 1, 3) - 1
)),
Substr(l.parn_of_line_id, Instr(l.parn_of_line_id, '&', 1, 4) + 1,
( Instr(l.parn_of_line_id, '&', 1, 5) -
Instr(l.parn_of_line_id, '&', 1, 4) - 1
)),
'0',
To_char(l.lnum),
l.accd_tlna,
l.accd_flna,
l.accd_lnam,
invd_tlna,
invd_flna,
invd_lnam,
l.invc_qy,
l.accd_qy
FROM mf_qo_itmz_ln l
WHERE l.uidy IN (((SELECT refd_line_id
FROM mf_vi_itmz_ln
WHERE Substr(refd_line_id, Instr(refd_line_id, '&', 1
, 4)
+
1, (
Instr(refd_line_id, '&', 1, 5) -
Instr(refd_line_id, '&', 1, 4)
- 1 ))
IN (SELECT cd
FROM mf_doc_type
WHERE dtyp_cat = ( 'QO' ))
AND invc_accr_fl = 'T')
UNION
(SELECT refd_line_id
FROM mf_vi_actg_ln iv2
WHERE Substr(refd_line_id, Instr(refd_line_id, '&', 1
, 4)
+
1, (
Instr(refd_line_id, '&', 1, 5) -
Instr(refd_line_id, '&', 1, 4)
- 1 ))
IN (SELECT cd
FROM mf_doc_type
WHERE dtyp_cat = ( 'QO' ))
AND invc_accr_fl = 'T')
UNION
(SELECT refd_line_id
FROM mf_vi_itmz_ln iv2
WHERE Substr(refd_line_id, Instr(refd_line_id, '&', 1
, 4)
+
1, (
Instr(refd_line_id, '&', 1, 5) -
Instr(refd_line_id, '&', 1, 4)
- 1 ))
IN (SELECT cd
FROM mf_doc_type
WHERE dtyp_cat = ( 'QO' ))
AND invc_accr_fl = 'T')
UNION
(SELECT refd_line_id
FROM mf_ti_ln ti
WHERE Substr(refd_line_id, Instr(refd_line_id, '&', 1
, 4)
+
1, (
Instr(refd_line_id, '&', 1, 5) -
Instr(refd_line_id, '&', 1, 4)
- 1 ))
IN (SELECT cd
FROM mf_doc_type
WHERE dtyp_cat = ( 'QO' ))
AND invc_accr_fl = 'T')
UNION
(SELECT refd_line_id
FROM mf_ii_actg_ln ii
WHERE Substr(refd_line_id, Instr(refd_line_id, '&', 1
, 4)
+
1, (
Instr(refd_line_id, '&', 1, 5) -
Instr(refd_line_id, '&', 1, 4)
- 1 ))
IN (SELECT cd
FROM mf_doc_type
WHERE dtyp_cat = ( 'QO' ))
AND invc_accr_fl = 'T')
UNION
(SELECT refd_line_id
FROM mf_ii_itmz_ln ii
WHERE Substr(refd_line_id, Instr(refd_line_id, '&', 1
, 4)
+
1, (
Instr(refd_line_id, '&', 1, 5) -
Instr(refd_line_id, '&', 1, 4)
- 1 ))
IN (SELECT cd
FROM mf_doc_type
WHERE dtyp_cat = ( 'QO' ))
AND invc_accr_fl = 'T')
UNION
(SELECT refd_line_id
FROM mf_ii_hdal ii2
WHERE Substr(refd_line_id, Instr(refd_line_id, '&', 1
, 4)
+
1, (
Instr(refd_line_id, '&', 1, 5) -
Instr(refd_line_id, '&', 1, 4)
- 1 ))
IN (SELECT cd
FROM mf_doc_type
WHERE dtyp_cat = ( 'QO' ))
AND invc_accr_fl = 'T')));
CURSOR postings IS
(SELECT pymt_ta AS ACCD_TLNA,
pymt_fa AS ACCD_FLNA,
pymt_am AS ACCD_LNAM,
exnd_tlna,
exnd_flna,
exnd_lnam,
exnd_qy,
To_number(pymt_qy) AS ACCD_QY,
invd_tlna,
invd_flna,
invd_lnam,
invc_qy
FROM mf_vi_itmz_ln iv2
WHERE refd_line_id = documents_rec.lrefd_id
AND invc_accr_fl = 'T'
UNION
SELECT 0,
0,
0,
0,
0,
0,
0,
0,
invd_tlna,
invd_flna,
invd_lnam,
invc_qy
FROM mf_vi_itmz_ln iv2
WHERE refd_line_id = documents_rec.lrefd_id
AND invc_accr_fl = 'F'
UNION
SELECT pymt_ta AS ACCD_TLNA,
pymt_fa AS ACCD_FLNA,
pymt_am AS ACCD_LNAM,
exnd_tlna,
exnd_flna,
exnd_lnam,
exnd_qy,
To_number(pymt_qy) AS ACCD_QY,
invd_tlna,
invd_flna,
invd_lnam,
invc_qy
FROM mf_ii_itmz_ln iv2
WHERE refd_line_id = documents_rec.lrefd_id
AND invc_accr_fl = 'T'
UNION
SELECT 0,
0,
0,
0,
0,
0,
0,
0,
invd_tlna,
invd_flna,
invd_lnam,
invc_qy
FROM mf_ii_itmz_ln iv2
WHERE refd_line_id = documents_rec.lrefd_id
AND invc_accr_fl = 'F'
UNION
SELECT accd_tlna,
accd_flna,
accd_lnam,
exnd_tlna,
exnd_flna,
exnd_lnam,
exnd_qy,
accd_qy,
0,
0,
0,
0
FROM mf_ic_itmz_ln iv2
WHERE refd_line_id = documents_rec.lrefd_id
AND Substr(parn_of_line_id, Instr(parn_of_line_id, '&', 1, 3) + 1,
(
Instr(
parn_of_line_id, '&', 1, 4) -
Instr(parn_of_line_id, '&', 1, 3)
- 1 ))
IN (SELECT cd
FROM mf_doc_type
WHERE dtyp_cat = 'IC') -- exclude AI
);
CURSOR cnt_liquidate_items IS
SELECT Count(uidy)
FROM (SELECT uidy
FROM mf_ic_hdal
WHERE Substr(refd_line_id, Instr(refd_line_id, '&', 1, 4) + 1, (
Instr(refd_line_id, '&', 1, 5) -
Instr(refd_line_id, '&', 1, 4)
- 1 )) =
documents_rec.lrefd_dtyp
AND Substr(refd_line_id, Instr(refd_line_id, '&', 1, 5) + 1
, (
Instr(refd_line_id, '&', 1, 6) -
Instr(refd_line_id, '&', 1, 5)
- 1 )) =
documents_rec.lrefd_doc_num
AND Substr(refd_line_id, Instr(refd_line_id, '&', 1, 7) + 1
, (
Instr(refd_line_id, '&', 1, 8) -
Instr(refd_line_id, '&', 1, 7)
- 1 )) =
Trim(documents_rec.lrefd_actg_lnum)
AND Substr(parn_of_line_id, Instr(parn_of_line_id, '&', 1,
3) +
1
, (
Instr(
parn_of_line_id, '&', 1, 4) -
Instr(parn_of_line_id, '&', 1, 3)
- 1 ))
IN (SELECT cd
FROM mf_doc_type
WHERE dtyp_cat = 'IC') -- exclude AI
AND ref_liqd_trms = 'T'
UNION
SELECT uidy
FROM mf_iv_hdal
WHERE Substr(refd_line_id, Instr(refd_line_id, '&', 1, 4) + 1, (
Instr(refd_line_id, '&', 1, 5) -
Instr(refd_line_id, '&', 1, 4)
- 1 )) =
documents_rec.lrefd_dtyp
AND Substr(refd_line_id, Instr(refd_line_id, '&', 1, 5) + 1
, (
Instr(refd_line_id, '&', 1, 6) -
Instr(refd_line_id, '&', 1, 5)
- 1 )) =
documents_rec.lrefd_doc_num
AND Substr(refd_line_id, Instr(refd_line_id, '&', 1, 7) + 1
, (
Instr(refd_line_id, '&', 1, 8) -
Instr(refd_line_id, '&', 1, 7)
- 1 )) =
Trim(documents_rec.lrefd_actg_lnum)
AND invc_accr_fl = 'T'
AND ref_liqd_trms = 'T');
BEGIN
-- script variables, adjust these based on when the parameters for the inital conversion job
bprelim := TRUE;
-- change this value to false to have the script perform updates
count1 := 0;
count2 := 0;
count3 := 0;
count4 := 0;
count5 := 0;
count6 := 0;
count7 := 0;
count8 := 0;
count9 := 0;
count10 := 0;
count11 := 0;
count12 := 0;
count13 := 0;
error_count1 := 0;
error_count2 := 0;
error_count3 := 0;
error_count4 := 0;
IF bprelim = TRUE THEN
dbms_output.Put_line('Script executed in Preliminary Mode (No Updates Performed)');
ELSE
dbms_output.Put_line('Script executed in Final Mode');
END IF;
OPEN documents;
dbms_output.ENABLE(NULL);
LOOP
FETCH documents bulk collect INTO documents_rec limit 1000;
EXIT WHEN documents%NOTFOUND;
documents_rec.laccd_tlna := Coalesce(documents_rec.laccd_tlna, 0);
documents_rec.laccd_flna := Coalesce(documents_rec.laccd_flna, 0);
documents_rec.laccd_lnam := Coalesce(documents_rec.laccd_lnam, 0);
documents_rec.laccd_qy := Coalesce(documents_rec.laccd_qy, 0);
documents_rec.linvd_tlna := Coalesce(documents_rec.linvd_tlna, 0);
documents_rec.linvd_flna := Coalesce(documents_rec.linvd_flna, 0);
documents_rec.linvd_lnam := Coalesce(documents_rec.linvd_lnam, 0);
documents_rec.linvd_qy := Coalesce(documents_rec.linvd_qy, 0);
dbms_output.Put_line('Processing document: '
|| documents_rec.lrefd_dtyp
|| ' '
|| documents_rec.lrefd_doc_num
|| ' '
|| documents_rec.lrefd_itmz_lnum
|| ' '
|| documents_rec.lrefd_actg_lnum
|| ' with accepted amount '
|| documents_rec.laccd_tlna
|| ' / '
|| documents_rec.laccd_flna
|| ' / '
|| documents_rec.laccd_lnam
|| ' Accepted quantity: '
|| documents_rec.laccd_qy
|| ' and invoiced amount '
|| documents_rec.linvd_tlna
|| ' / '
|| documents_rec.linvd_flna
|| ' / '
|| documents_rec.linvd_lnam
|| ' Invoiced quantity: '
|| documents_rec.linvd_qy
|| ' ...');
count1 := count1 + 1;
calc_invd_tlna := 0.00;
calc_invd_flna := 0.00;
calc_invd_lnam := 0.00;
calc_accd_tlna := 0.00;
calc_accd_flna := 0.00;
calc_accd_lnam := 0.00;
calc_exnd_tlna := 0.00;
calc_exnd_flna := 0.00;
calc_exnd_lnam := 0.00;
calc_accd_qy := 0.00;
calc_exnd_qy := 0.00;
calc_invd_qy := 0.00;
match_count := 0;
OPEN postings;
LOOP
FETCH postings INTO temp_accd_tlna, temp_accd_flna, temp_accd_lnam,
temp_exnd_tlna, temp_exnd_flna, temp_exnd_lnam, temp_exnd_qy,
temp_accd_qy,
temp_invd_tlna, temp_invd_flna, temp_invd_lnam, temp_invd_qy;
EXIT WHEN postings%NOTFOUND;
calc_invd_tlna := calc_invd_tlna + temp_invd_tlna;
calc_invd_flna := calc_invd_flna + temp_invd_flna;
calc_invd_lnam := calc_invd_lnam + temp_invd_lnam;
calc_accd_tlna := calc_accd_tlna + temp_accd_tlna;
calc_accd_flna := calc_accd_flna + temp_accd_flna;
calc_accd_lnam := calc_accd_lnam + temp_accd_lnam;
calc_exnd_tlna := calc_exnd_tlna + temp_exnd_tlna;
calc_exnd_flna := calc_exnd_flna + temp_exnd_flna;
calc_exnd_lnam := calc_exnd_lnam + temp_exnd_lnam;
calc_accd_qy := calc_accd_qy + temp_accd_qy;
calc_exnd_qy := calc_exnd_qy + temp_exnd_qy;
calc_invd_qy := calc_invd_qy + temp_invd_qy;
match_count := match_count + 1;
--DBMS_OUTPUT.PUT_LINE(' Refg doc: ' || lrefg_id || ' - ' || to_char(COALESCE(temp_accd_tlna, 0)) || ' / ' || to_char(COALESCE(temp_accd_flna, 0)) || ' / ' || to_char( COALESCE(temp_accd_lnam, 0)) || ' / ' || to_char(COALESCE(temp_exnd_tlna, 0)) || ' / ' || to_char(COALESCE(temp_exnd_flna, 0)) || ' / ' || to_char(COALESCE(temp_exnd_lnam, 0)) || ' / ' || to_char(COALESCE(temp_exnd_qy, 0)) || ' / ' || to_char(COALESCE(temp_accd_qy, 0)) );
END LOOP;
CLOSE postings;
IF match_count > 0 THEN
-- check to see if there are any referencing lines that are marked to liquidate items
liq_items_count := 0;
OPEN cnt_liquidate_items;
LOOP
FETCH cnt_liquidate_items INTO temp_liq_items_count;
EXIT WHEN cnt_liquidate_items%NOTFOUND;
liq_items_count := temp_liq_items_count;
--DBMS_OUTPUT.PUT_LINE(' Liquidate Items Count: ' || liq_items_count);
END LOOP;
CLOSE cnt_liquidate_items;
-- accepted amount
IF ( Coalesce(calc_accd_tlna, 0) - Coalesce(calc_exnd_tlna, 0) ) =
Coalesce(
documents_rec.laccd_tlna, 0)
AND ( Coalesce(calc_accd_flna, 0) - Coalesce(calc_exnd_flna, 0) ) =
Coalesce(
documents_rec.laccd_flna, 0)
AND ( Coalesce(calc_accd_lnam, 0) - Coalesce(calc_exnd_lnam, 0) ) =
Coalesce(
documents_rec.laccd_lnam, 0) THEN
count2 := count2 + 1;
dbms_output.Put_line(' Calculated accepted amount matches the current accepted amount on the document. No Update Required.');
ELSE
IF liq_items_count > 0 THEN
error_count1 := error_count1 + 1;
dbms_output.Put_line(' Line: '
|| documents_rec.lrefd_dtyp
|| ' '
|| documents_rec.lrefd_doc_num
|| ' '
|| documents_rec.lrefd_itmz_lnum
|| ' '
|| documents_rec.lrefd_actg_lnum
||
' is referenced by lines using liquidate item functionally. Accepted amount will need to be manually computed.'
);
ELSIF ( Coalesce(calc_accd_tlna, 0) - Coalesce(calc_exnd_tlna, 0) ) < 0
OR ( Coalesce(calc_accd_flna, 0) - Coalesce(calc_exnd_flna, 0) ) < 0
OR ( Coalesce(calc_accd_lnam, 0) - Coalesce(calc_exnd_lnam, 0) ) < 0 THEN
error_count1 := error_count1 + 1;
dbms_output.Put_line(' Line: '
|| documents_rec.lrefd_dtyp
|| ' '
|| documents_rec.lrefd_doc_num
|| ' '
|| documents_rec.lrefd_itmz_lnum
|| ' '
|| documents_rec.lrefd_actg_lnum
|| ' calculates a negative accepted amount: '
|| ( Coalesce(calc_accd_tlna, 0) -
Coalesce(calc_exnd_tlna, 0) )
|| ' / '
|| ( Coalesce(calc_accd_flna, 0) -
Coalesce(calc_exnd_flna, 0) )
|| ' / '
|| ( Coalesce(calc_accd_lnam, 0) -
Coalesce(calc_exnd_lnam, 0) )
|| ' Accepted amount will need to be manually computed.'
);
ELSE
count3 := count3 + 1;
dbms_output.Put_line(' Calculated accepted amount: '
|| ( Coalesce(calc_accd_tlna, 0) -
Coalesce(calc_exnd_tlna, 0) )
|| ' / '
|| ( Coalesce(calc_accd_flna, 0) -
Coalesce(calc_exnd_flna, 0) )
|| ' / '
|| ( Coalesce(calc_accd_lnam, 0) -
Coalesce(calc_exnd_lnam, 0) )
||
' does not match accepted amount on document. Update Required.');
IF bprelim = FALSE THEN
UPDATE mf_qo_itmz_ln
SET accd_tlna = ( Coalesce(calc_accd_tlna, 0) -
Coalesce(calc_exnd_tlna, 0)
)
,
accd_flna = ( Coalesce(calc_accd_flna, 0) - Coalesce(
calc_exnd_flna, 0) )
,
accd_lnam = ( Coalesce(calc_accd_lnam, 0) - Coalesce(
calc_exnd_lnam, 0) )
WHERE uidy = documents_rec.lrefd_id;
count4 := count4 + 1;
END IF;
END IF;
END IF;
-- accepted quantity
IF ( Coalesce(calc_accd_qy, 0) - Coalesce(calc_exnd_qy, 0) ) = Coalesce(
documents_rec.laccd_qy, 0) THEN
count8 := count8 + 1;
dbms_output.Put_line(' Calculated accepted quantity matches the current accepted quantity on the document. No Update Required.');
ELSE
IF liq_items_count > 0 THEN
error_count2 := error_count2 + 1;
dbms_output.Put_line(' Line: '
|| documents_rec.lrefd_dtyp
|| ' '
|| documents_rec.lrefd_doc_num
|| ' '
|| documents_rec.lrefd_itmz_lnum
|| ' '
|| documents_rec.lrefd_actg_lnum
||
' is referenced by lines using liquidate item functionally. Accepted quantity will need to be manually computed.'
);
ELSIF ( Coalesce(calc_accd_qy, 0) - Coalesce(calc_exnd_qy, 0) ) < 0 THEN
error_count2 := error_count2 + 1;
dbms_output.Put_line(' Line: '
|| documents_rec.lrefd_dtyp
|| ' '
|| documents_rec.lrefd_doc_num
|| ' '
|| documents_rec.lrefd_itmz_lnum
|| ' '
|| documents_rec.lrefd_actg_lnum
|| ' calculates a negative accepted quantity: '
|| ( Coalesce(calc_accd_qy, 0) -
Coalesce(calc_exnd_qy, 0)
)
||
' Accepted quantity will need to be manually computed.'
);
ELSE
count9 := count9 + 1;
dbms_output.Put_line(' Calculated accepted quantity: '
|| ( Coalesce(calc_accd_qy, 0) -
Coalesce(calc_exnd_qy, 0)
)
||
' does not match quantity amount on document. Update Required.');
IF bprelim = FALSE THEN
UPDATE mf_qo_itmz_ln
SET accd_qy = ( Coalesce(calc_accd_qy, 0) - Coalesce(calc_exnd_qy, 0) )
WHERE uidy = documents_rec.lrefd_id;
count10 := count10 + 1;
END IF;
END IF;
END IF;
-- Invoiced amount
IF Coalesce(calc_invd_tlna, 0) = Coalesce(documents_rec.linvd_tlna, 0)
AND Coalesce(calc_invd_flna, 0) = Coalesce(documents_rec.linvd_flna, 0)
AND Coalesce(calc_invd_lnam, 0) = Coalesce(documents_rec.linvd_lnam, 0)
THEN
count5 := count5 + 1;
dbms_output.Put_line(' Calculated invoiced amount matches the current invoiced amount on the document. No Update Required.');
ELSE
IF liq_items_count > 0 THEN
error_count3 := error_count3 + 1;
dbms_output.Put_line(' Line: '
|| documents_rec.lrefd_dtyp
|| ' '
|| documents_rec.lrefd_doc_num
|| ' '
|| documents_rec.lrefd_itmz_lnum
|| ' '
|| documents_rec.lrefd_actg_lnum
||
' is referenced by lines using liquidate item functionally. Invoiced amount will need to be manually computed.'
);
ELSIF Coalesce(calc_invd_tlna, 0) < 0
OR Coalesce(calc_invd_flna, 0) < 0
OR Coalesce(calc_invd_lnam, 0) < 0 THEN
error_count3 := error_count3 + 1;
dbms_output.Put_line(' Line: '
|| documents_rec.lrefd_dtyp
|| ' '
|| documents_rec.lrefd_doc_num
|| ' '
|| documents_rec.lrefd_itmz_lnum
|| ' '
|| documents_rec.lrefd_actg_lnum
|| ' calculates a negative invoiced amount: '
|| ( Coalesce(calc_invd_tlna, 0) )
|| ' / '
|| ( Coalesce(calc_invd_flna, 0) )
|| ' / '
|| ( Coalesce(calc_invd_lnam, 0) )
|| 'Invoiced amount will need to be manually computed.');
ELSE
count6 := count6 + 1;
dbms_output.Put_line(' Calculated invoiced amount: '
|| ( Coalesce(calc_invd_tlna, 0) )
|| ' / '
|| ( Coalesce(calc_invd_flna, 0) )
|| ' / '
|| ( Coalesce(calc_invd_lnam, 0) )
||
' does not match invoiced amount on document. Update Required.');
IF bprelim = FALSE THEN
UPDATE mf_qo_itmz_ln
SET invd_tlna = ( Coalesce(calc_invd_tlna, 0) ),
invd_flna = ( Coalesce(calc_invd_flna, 0) ),
invd_lnam = ( Coalesce(calc_invd_lnam, 0) )
WHERE uidy = documents_rec.lrefd_id;
count7 := count7 + 1;
END IF;
END IF;
END IF;
-- Invoiced quantity
IF Coalesce(calc_invd_qy, 0) = Coalesce(documents_rec.linvd_qy, 0) THEN
count11 := count11 + 1;
dbms_output.Put_line(' Calculated invoiced quantity matches the current invoiced quantity on the document. No Update Required.');
ELSE
IF liq_items_count > 0 THEN
error_count4 := error_count4 + 1;
dbms_output.Put_line(' Line: '
|| documents_rec.lrefd_dtyp
|| ' '
|| documents_rec.lrefd_doc_num
|| ' '
|| documents_rec.lrefd_itmz_lnum
|| ' '
|| documents_rec.lrefd_actg_lnum
||
' is referenced by lines using liquidate item functionally. Invoiced quantity will need to be manually computed.'
);
ELSIF Coalesce(calc_invd_qy, 0) < 0 THEN
error_count4 := error_count4 + 1;
dbms_output.Put_line(' Line: '
|| documents_rec.lrefd_dtyp
|| ' '
|| documents_rec.lrefd_doc_num
|| ' '
|| documents_rec.lrefd_itmz_lnum
|| ' '
|| documents_rec.lrefd_actg_lnum
|| ' calculates a negative invoiced quantity: '
|| ( Coalesce(calc_invd_qy, 0) )
|| 'Invoiced quantity will need to be manually computed.'
);
ELSE
count12 := count12 + 1;
dbms_output.Put_line(' Calculated invoiced quantity: '
|| ( Coalesce(calc_invd_qy, 0) )
||
' does not match invoiced quantity on document. Update Required.');
IF bprelim = FALSE THEN
UPDATE mf_qo_itmz_ln
SET invc_qy = ( Coalesce(calc_invd_qy, 0) )
WHERE uidy = documents_rec.lrefd_id;
count13 := count13 + 1;
END IF;
END IF;
END IF;
ELSE
error_count1 := error_count1 + 1;
error_count2 := error_count2 + 1;
error_count3 := error_count3 + 1;
error_count4 := error_count4 + 1;
dbms_output.Put_line(' Error could not calculate accepted/invoiced amount for: '
|| documents_rec.lrefd_dtyp
|| ' '
|| documents_rec.lrefd_doc_num
|| ' '
|| documents_rec.lrefd_itmz_lnum
|| ' '
|| documents_rec.lrefd_actg_lnum);
END IF;
END LOOP;
CLOSE documents;
dbms_output.Put_line('********************************************************************************');
IF bprelim = TRUE THEN
dbms_output.Put_line('Script executed in Preliminary Mode (No Updates Performed)');
ELSE
dbms_output.Put_line('Script executed in Final Mode');
END IF;
dbms_output.Put_line('Number of Potential incorrect records found: '
|| count1);
dbms_output.Put_line('Number of records that were matched to referenced line accepted amount (no Update Required): '
|| count2);
dbms_output.Put_line('Number of records that do NOT match referenced line accepted amount (Update Required): '
|| count3);
dbms_output.Put_line('Number of Accepted amount Updates Performed: '
|| count4);
dbms_output.Put_line('Number of Accepted amount records that could not be matched and need to be examined manually: '
|| error_count1);
dbms_output.Put_line('Number of records that were matched to referenced line accepted quantity (no Update Required): '
|| count8);
dbms_output.Put_line('Number of records that do NOT match referenced line accepted quantity (Update Required): '
|| count9);
dbms_output.Put_line('Number of Accepted quantity Updates Performed: '
|| count10);
dbms_output.Put_line('Number of Accepted quantity records that could not be matched and need to be examined manually: '
|| error_count2);
dbms_output.Put_line('Number of records that were matched to referenced line invoiced amount (no Update Required): '
|| count5);
dbms_output.Put_line('Number of records that do NOT match referenced line invoiced amount (Update Required): '
|| count6);
dbms_output.Put_line('Number of Invoiced amount Updates Performed: '
|| count7);
dbms_output.Put_line('Number of Invoiced amount records that could not be matched and need to be examined manually: '
|| error_count3);
dbms_output.Put_line('Number of records that were matched to referenced line invoiced quantity (no Update Required): '
|| count11);
dbms_output.Put_line('Number of records that do NOT match referenced line invoiced quantity (Update Required): '
|| count12);
dbms_output.Put_line('Number of Invoiced quantity Updates Performed: '
|| count13);
dbms_output.Put_line('Number of Invoiced quantity records that could not be matched and need to be examined manually: '
|| error_count4);
dbms_output.Put_line('********************************************************************************');
END;
/
* BlackSwan added {code} tags. Please do so yourself in the future. http://www.orafaq.com/forum/t/174502/
* LF applied [spoiler] tags
[Updated on: Sun, 12 July 2015 15:14] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: To tune a PL/SQL block [message #639617 is a reply to message #639615] |
Sun, 12 July 2015 14:10  |
John Watson
Messages: 9003
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You expect someone to tune that???
Break it down: run the queries for each cursor, see how they run. Focus on the slow ones, and the ones executed many times.
btw, I think you are using UNION unnecessarily; UNION ALL will save some time.
|
|
|
|
|
|
| Re: To tune a PL/SQL block [message #639619 is a reply to message #639618] |
Sun, 12 July 2015 15:35 |
|
13478
Messages: 2
Registered: July 2015
|
Junior Member |
|
|
thank you,
tkprof xxx_ora_xx.trc xxx_report.txt explain=system/xxx sys=no sort=exeela,prsela,fchela
find this sql, which took most of time...
SELECT L.UIDY, SUBSTR(L.PARN_OF_LINE_ID, INSTR(L.PARN_OF_LINE_ID, '&', 1, 3)
+ 1, ( INSTR(L.PARN_OF_LINE_ID, '&', 1, 4) - INSTR(L.PARN_OF_LINE_ID, '&',
1, 3) - 1 )), SUBSTR(L.PARN_OF_LINE_ID, INSTR(L.PARN_OF_LINE_ID, '&', 1, 4)
+ 1, ( INSTR(L.PARN_OF_LINE_ID, '&', 1, 5) - INSTR(L.PARN_OF_LINE_ID, '&',
1, 4) - 1 )), '0', TO_CHAR(L.LNUM), L.ACCD_TLNA, L.ACCD_FLNA, L.ACCD_LNAM,
INVD_TLNA, INVD_FLNA, INVD_LNAM, L.INVC_QY, L.ACCD_QY
FROM
MF_QO_ITMZ_LN L
WHERE L.UIDY IN (((SELECT REFD_LINE_ID FROM MF_VI_ITMZ_LN
WHERE SUBSTR(REFD_LINE_ID, INSTR(REFD_LINE_ID, '&', 1 , 4) + 1, (
INSTR(REFD_LINE_ID, '&', 1, 5) - INSTR(REFD_LINE_ID, '&', 1, 4) - 1 )) IN
(SELECT CD FROM MF_DOC_TYPE WHERE DTYP_CAT = ( 'QO' )) AND INVC_ACCR_FL =
'T') UNION (SELECT REFD_LINE_ID FROM MF_VI_ACTG_LN IV2 WHERE
SUBSTR(REFD_LINE_ID, INSTR(REFD_LINE_ID, '&', 1 , 4) + 1, (
INSTR(REFD_LINE_ID, '&', 1, 5) - INSTR(REFD_LINE_ID, '&', 1, 4) - 1 )) IN
(SELECT CD FROM MF_DOC_TYPE WHERE DTYP_CAT = ( 'QO' )) AND INVC_ACCR_FL =
'T') UNION (SELECT REFD_LINE_ID FROM MF_VI_ITMZ_LN IV2 WHERE
SUBSTR(REFD_LINE_ID, INSTR(REFD_LINE_ID, '&', 1 , 4) + 1, (
INSTR(REFD_LINE_ID, '&', 1, 5) - INSTR(REFD_LINE_ID, '&', 1, 4) - 1 )) IN
(SELECT CD FROM MF_DOC_TYPE WHERE DTYP_CAT = ( 'QO' )) AND INVC_ACCR_FL =
'T') UNION (SELECT REFD_LINE_ID FROM MF_TI_LN TI WHERE SUBSTR(REFD_LINE_ID,
INSTR(REFD_LINE_ID, '&', 1 , 4) + 1, ( INSTR(REFD_LINE_ID, '&', 1, 5) -
INSTR(REFD_LINE_ID, '&', 1, 4) - 1 )) IN (SELECT CD FROM MF_DOC_TYPE WHERE
DTYP_CAT = ( 'QO' )) AND INVC_ACCR_FL = 'T') UNION (SELECT REFD_LINE_ID
FROM MF_II_ACTG_LN II WHERE SUBSTR(REFD_LINE_ID, INSTR(REFD_LINE_ID, '&', 1
, 4) + 1, ( INSTR(REFD_LINE_ID, '&', 1, 5) - INSTR(REFD_LINE_ID, '&', 1, 4)
- 1 )) IN (SELECT CD FROM MF_DOC_TYPE WHERE DTYP_CAT = ( 'QO' )) AND
INVC_ACCR_FL = 'T') UNION (SELECT REFD_LINE_ID FROM MF_II_ITMZ_LN II WHERE
SUBSTR(REFD_LINE_ID, INSTR(REFD_LINE_ID, '&', 1 , 4) + 1, (
INSTR(REFD_LINE_ID, '&', 1, 5) - INSTR(REFD_LINE_ID, '&', 1, 4) - 1 )) IN
(SELECT CD FROM MF_DOC_TYPE WHERE DTYP_CAT = ( 'QO' )) AND INVC_ACCR_FL =
'T') UNION (SELECT REFD_LINE_ID FROM MF_II_HDAL II2 WHERE
SUBSTR(REFD_LINE_ID, INSTR(REFD_LINE_ID, '&', 1 , 4) + 1, (
INSTR(REFD_LINE_ID, '&', 1, 5) - INSTR(REFD_LINE_ID, '&', 1, 4) - 1 )) IN
(SELECT CD FROM MF_DOC_TYPE WHERE DTYP_CAT = ( 'QO' )) AND INVC_ACCR_FL =
'T')));
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.02 0.02 0 11171 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.05 0.05 0 11171 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 117 xxx (recursive depth: 1)
Number of plan statistics captured: 1
it is explain is attached.
Ok, now sql tuning of this bad sql is the way to speed up this PL/SQL,hmmm... thinking how to tune this sql...
any suggestions are welcome.
thank you very much
|
|
|
|
|
|
| Re: To tune a PL/SQL block [message #639629 is a reply to message #639620] |
Mon, 13 July 2015 03:27 |
cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You need to bulk collect into an array variable, not a record variable, that's why you're getting the error.
But it's a waste of time.
You've got code of the form:
OPEN cursor
LOOP
FETCH cursor INTO variables
EXIT WHEN cursor%NOTFOUND
other_variables := other_variables + cursor_variables;
END LOOP;
If you change the cursor (postings) to use SUM then you don't need to loop or do additions. The SQL will do all the calculations and you can get the results in a single fetch which should be faster.
|
|
|
|
Goto Forum:
Current Time: Mon Aug 10 05:59:35 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
