1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Pl/sql tuning

Discussion in 'SQL PL/SQL' started by 13478, Jul 12, 2015.

  1. 13478

    13478 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    Hello,

    I have a PL/SQL block, it runs but runs slow. I try to use BULK COLLECT for cursor DOCUMENTS to speed it up (better performance)

    while I get error as below:

    Error report:
    ORA-06550: line 181, column 43:
    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 better way to tune it up?
    Thank you very much in advance!





    this is the original script:
    Code (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;
    /



    --this is the script of using bulk collect for cursor DOCUMENTS, while get error
    Error report:
    ORA-06550: line 181, COLUMN 43:
    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:



    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;
    /
     
    Last edited by a moderator: Jul 12, 2015
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    I don't know (nor does anyone else on this forum) exactly which portion of your script is taking the most time. Bulk operations help a great deal if the subprogram is processing lots of rows. If your subprogram is processing 100 rows, then switching to bulk operations will likely have no noticeable effect. If your subprogram is processing 100,000 rows, then it is something that will almost certainly speed things up considerably.

    Your anonymous block is poorly indented and very difficult to read, which makes me less willing to spend much time analyzing it. Some obvious problems I see are:

    1. The use of lots of UNIONs in your queries. Unless there is a reason to return only DISTINCT results, the UNION ALL operator is much faster. Since you are apparently only using them to generate an IN-list, where duplicates are unimportant, I'd suggest switching to UNION ALL.
    2. I don't know the size of the tables involved, but the WHERE conditions on your queries are really ugly -- particularly the repeated use of that "SUBSTR(INSTR(REFD_LINE_ID..." It may be that you have no choice but to use that, but if those are large tables, that's got to be an ugly operation.
    3. If you are concerned that the query will produce NULL values, then perform your COALESCE operations in the SELECT list rather than constantly executing the function against each of the variables populated by the queries over and over again. If nothing else, this will make the code somewhat more readable.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    To take Matthew's second point a bit further if you are constantly using a substr() construct in WHERE clauses you would be better off with a function- based index to possibly speed up the queries. As it is every WHERE clause using such constructs (as you have them written) is performing full table scans (a WHERE SUBSTR(....) ... clause can't use a 'standard' index ) which means your PL/SQL is searching far more data than you intended. As an example:

    Code (Text):
    SQL> set autotrace on
    SQL>
    SQL> select *
      2  from fbi_test
      3  where substr(j, 1, 13) = 'Poogmambu100';

             I J                                        K         L
    ---------- ---------------------------------------- --------- ----------
           100 Poogmambu100                             03-AUG-15 Faanmertle


    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3739205581

    ------------------------------------------------------------------------------
    | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |          |  3000 |   117K|   514   (1)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| FBI_TEST |  3000 |   117K|   514   (1)| 00:00:01 |
    ------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       1 - filter(SUBSTR("J",1,13)='Poogmambu100')


    Statistics
    ----------------------------------------------------------
              1  recursive calls
              0  db block gets
           1887  consistent gets
              0  physical reads
              0  redo size
            740  bytes sent via SQL*Net to client
            499  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed

    SQL>
    SQL> set autotrace off
    SQL>
    SQL> create index fba_test_idx2 on fbi_test(substr(j, 1, 13));

    Index created.

    SQL>
    SQL> set autotrace on
    SQL>
    SQL> select *
      2  from fbi_test
      3  where substr(j, 1, 13) = 'Poogmambu100';

             I J                                        K         L
    ---------- ---------------------------------------- --------- ----------
           100 Poogmambu100                             03-AUG-15 Faanmertle


    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 19878110

    ---------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |               |  3000 |   117K|    93   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| FBI_TEST      |  3000 |   117K|    93   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | FBA_TEST_IDX2 |  1200 |       |     3   (0)| 00:00:01 |
    ---------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

       2 - access(SUBSTR("J",1,13)='Poogmambu100')


    Statistics
    ----------------------------------------------------------
              2  recursive calls
              0  db block gets
              7  consistent gets
              2  physical reads
              0  redo size
            740  bytes sent via SQL*Net to client
            499  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
              1  rows processed

    SQL>
     
  4. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    The OP isn't going to be able to use a function-based index because the SUBSTR/INSTR portions of his queries are all tied to a user-supplied parameter via an ampersand that changes the string being searched for in the base column being compared:

    "SUBSTR(l.PARN_OF_LINE_ID, INSTR(l.PARN_OF_LINE_ID,'&',1,3)+1"
     
    13478 likes this.
  5. 13478

    13478 Active Member

    Messages:
    31
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Louisana
    Thank you very much Matthew and Zargon.

    The approach I did now: run a TKPROF against this PL/SQL block, find out the performance bottleneck. (tkprof output sorted by elapsed time)
    the slowest part is the query for cursor DOCUMENTS :

    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)),
    ...
    from MF_QO_ITMZ_LN l
    where l.UIDY IN ..;


    I tuned that query, added a couple of indexes, and use UNION ALL for that query (only that query, not others, because I am not very sure if uniqueness is a must, but for first query, it obviously OK)

    now looks like 90% performance gained.

    I really appreciate all of you.