Oracle PLSQL - Need to Reassign the PR from one user to another user

 DECLARE

    x_errbuf           VARCHAR2(2000);

    x_retcode          VARCHAR2(2000);

    p_curnt_apprvr     VARCHAR2(200) := 'RYANH';  --From User Name

    p_new_apprvr       VARCHAR2(200) := 'DCHAU'; --To User Name

    p_requisition_no   VARCHAR2(200) := '2040807';

    p_comment          VARCHAR2(200) := 'INC0621049';

    p_workflow_type    VARCHAR2(200) := 'REQAPPRV';

    p_org_id           NUMBER;

    lc_wf_item_key     po_requisition_headers_all.wf_item_key%TYPE;

    ln_notification_id wf_notifications.notification_id%TYPE;

    lc_nid_flag        VARCHAR2(1) := 'Y';

    l_userid           NUMBER := 42520;  --'RPERABATHULA' --DBA User id

    l_user             VARCHAR2(50);

    l_current_appr     VARCHAR2(50);

    l_new_appr         VARCHAR2(50);

   

    CURSOR cur_nid (

        p_recipient_role wf_notifications.recipient_role%TYPE,

        p_message_type   wf_notifications.message_type%TYPE

    ) IS

    SELECT

        notification_id,

        item_key

    FROM

        wf_notifications

    WHERE

            recipient_role = p_recipient_role

        AND status = 'OPEN'

        AND message_type = p_message_type;

 

    CURSOR cur_req (

        p_wf_key wf_notifications.item_key%TYPE

    ) IS

    SELECT

        segment1

    FROM

        po_requisition_headers_all

    WHERE

        wf_item_key = p_wf_key;

 

BEGIN

    apps.fnd_profile.get('USER_ID', l_userid);

    BEGIN -- added by prabha -scr 25601

        SELECT

            description

        INTO l_current_appr

        FROM

            fnd_user

        WHERE

            user_name = p_curnt_apprvr;

 

        SELECT

            description

        INTO l_new_appr

        FROM

            fnd_user

        WHERE

            user_name = p_new_apprvr;

 

        SELECT

            description

        INTO l_user

        FROM

            fnd_user

        WHERE

            user_id = l_userid;

 

    EXCEPTION --added by prabha scr 25602

        WHEN OTHERS THEN

            NULL;

    END;

 

    IF p_requisition_no IS NOT NULL THEN

        dbms_output.put_line('start' || lc_nid_flag);

       

        dbms_output.put_line(rpad('CURRENT APPROVER', 35, ' ')

                                        || rpad('NEW APPROVER', 35, ' ')

                                        || rpad('REQUISITION NUMBER', 25, ' ')

                                        || rpad('NOTES', 50, ' ')

                                        || rpad('UPDATED BY', 35, ' ')

                                        || rpad('DATE UPDATED', 15, ' '));

        dbms_output.put_line(rpad(l_current_appr, 35, ' ')

                                        || rpad(l_new_appr, 35, ' ')

                                        || rpad(p_requisition_no, 25, ' ')

                                        || rpad(nvl(p_comment, ' '), 50, ' ')

                                        || rpad(l_user, 35, ' ')

                                        || rpad(sysdate, 15, ' '));

       

 

        BEGIN

            SELECT

                wf_item_key

            INTO lc_wf_item_key

            FROM

                po_requisition_headers_all

            WHERE

                    segment1 = p_requisition_no

                AND authorization_status <> 'APPROVED' --Only need to pick the Un Approved PR

                ;

 

        EXCEPTION

            WHEN no_data_found THEN

                lc_wf_item_key := NULL;

        END;

 

        BEGIN

            SELECT

                notification_id

            INTO ln_notification_id

            FROM

                wf_notifications

        --WHERE  CONTEXT LIKE '%' || lc_wf_item_key || '%'  --commented by prabha SCR25601

            WHERE

                    user_key = p_requisition_no  -- added by prabha SCR 25602

                AND recipient_role = p_curnt_apprvr

                AND status = 'OPEN'

                AND message_type = p_workflow_type;

 

        EXCEPTION

            WHEN no_data_found THEN

                lc_nid_flag := 'N';

  

                dbms_output.put_line('Error: No Notifications found for the given Requisition#. ' || p_requisition_no);

       

        END;

 

        IF lc_nid_flag = 'Y' THEN

            BEGIN

                fnd_wf_notification.forward(ln_notification_id -- nid

                , p_new_apprvr -- new_role

                , p_comment -- forward_comment

                , p_curnt_apprvr -- user

                , 0 -- cnt

                );

            EXCEPTION

                WHEN OTHERS THEN

--                    fnd_file.put_line(fnd_file.log, 'Error: ' || sqlerrm);

                    dbms_output.put_line('Error: ' || sqlerrm);

            END;

        --<start> else part added by prabha SCR 25602

            dbms_output.put_line('if yes' || lc_nid_flag);

        ELSE

            dbms_output.put_line('if no' || lc_nid_flag);

            BEGIN

                SELECT

                    notification_id

                INTO ln_notification_id

                FROM

                    wf_notifications

                WHERE

                    subject LIKE '%'

                                 || p_requisition_no

                                 || '%'

                    AND recipient_role = p_curnt_apprvr

                    AND status = 'OPEN'

                    AND message_type = p_workflow_type;

 

                dbms_output.put_line(ln_notification_id);

            EXCEPTION

                WHEN no_data_found THEN

                    lc_nid_flag := 'N';

                    dbms_output.put_line('expn' || lc_nid_flag);

                 

                     dbms_output.put_line('Error: No Notifications found for the given Requisition even with subject#. ' ||

                    p_requisition_no);

            END;

 

            BEGIN

                fnd_wf_notification.forward(ln_notification_id -- nid

                , p_new_apprvr -- new_role

                , p_comment -- forward_comment

                , p_curnt_apprvr -- user

                , 0 -- cnt

                );

            EXCEPTION

                WHEN OTHERS THEN

--                    fnd_file.put_line(fnd_file.log, 'Error: ' || sqlerrm);

                    dbms_output.put_line('Error: ' || sqlerrm);

            END;

        --<end> of scr25602

        END IF; -- lc_nid_flag = 'Y'

    ELSIF p_requisition_no IS NULL THEN

 

                                       

        dbms_output.put_line(rpad('CURRENT APPROVER', 35, ' ')

                                        || rpad('NEW APPROVER', 35, ' ')

                                        || rpad('REQUISITION NUMBER', 25, ' ')

                                        || rpad('NOTES', 50, ' ')

                                        || rpad('UPDATED BY', 35, ' ')

                                        || rpad('DATE UPDATED', 15, ' '));

 

        FOR rec_nid IN cur_nid(p_curnt_apprvr, p_workflow_type) LOOP

            FOR i IN cur_req(rec_nid.item_key) LOOP

                BEGIN

                    fnd_wf_notification.forward(rec_nid.notification_id -- nid

                    , p_new_apprvr -- new_role

                    , p_comment -- forward_comment

                    , p_curnt_apprvr -- user

                    , 0 -- cnt

                    );

                EXCEPTION

                    WHEN OTHERS THEN

--                        fnd_file.put_line(fnd_file.log, 'Error: ' || sqlerrm);

                        dbms_output.put_line('Error: ' || sqlerrm);

                END;

                dbms_output.put_line(rpad(l_current_appr, 35, ' ')

                                                || rpad(l_new_appr, 35, ' ')

                                                || rpad(i.segment1, 25, ' ')

                                                || rpad(nvl(p_comment, ' '), 50, ' ')

                                                || rpad(l_user, 35, ' ')

                                                || rpad(sysdate, 15, ' '));

            END LOOP;

        END LOOP;

 

    END IF; -- p_requisition_no

    COMMIT; -- SCR16372 added commit and below exception.

EXCEPTION

    WHEN OTHERS THEN

 

        dbms_output.put_line('Error: ' || sqlerrm);

END;