Validation Procedure With One Cursor and Bulk Collect

 CREATE OR REPLACE PROCEDURE Po_Header_Validation (

    P_Status_Out     OUT VARCHAR2,

    P_Error_Msg_Out  OUT VARCHAR2

)

IS

    TYPE Po_Headers_Type IS TABLE OF Syb_Po_Header_Table%ROWTYPE;

    Lv_Po_Headers    Po_Headers_Type;

    l_error_msg      VARCHAR2(3000);

    V_supplier_name  Syb_Po_Header_Table.Supplier_name%TYPE;

    CURSOR C1 IS

        SELECT *

        FROM Syb_Po_Header_Table

        WHERE NVL(Process_Status, 'V') = 'V';

BEGIN

    -- Fetch all records with Process_Status 'V'

    OPEN C1;

    FETCH C1 BULK COLLECT INTO Lv_Po_Headers;

    CLOSE C1;

    -- Loop through each record

    FOR I IN 1 .. Lv_Po_Headers.COUNT LOOP

        BEGIN

            -- Check for supplier name existence

            SELECT Supplier_Name

            INTO V_supplier_name

            FROM Syb_Po_Header_Table

            WHERE INTERFACE_HEADER_KEY = Lv_Po_Headers(I).INTERFACE_HEADER_KEY;

        EXCEPTION

            WHEN OTHERS THEN

                l_error_msg := l_error_msg || ' - Supplier Name Not Exist..!';

        END;

        -- Update process status based on validation

        IF l_error_msg IS NOT NULL THEN

            UPDATE Syb_Po_Header_Table

            SET Process_Status   = 'E',

                Process_Message  = l_error_msg

            WHERE INTERFACE_HEADER_KEY = Lv_Po_Headers(I).INTERFACE_HEADER_KEY;

        ELSE

            UPDATE Syb_Po_Header_Table

            SET Process_Status   = 'V',

                Process_Message  = NULL

            WHERE INTERFACE_HEADER_KEY = Lv_Po_Headers(I).INTERFACE_HEADER_KEY;

        END IF;

    END LOOP;

    COMMIT;

EXCEPTION

    WHEN OTHERS THEN

        P_Status_Out     := 'E';

        P_Error_Msg_Out  := 'Error Code: ' || SQLCODE || ' - ' || SQLERRM;

END Po_Header_Validation;