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;

Oracle EBS - XDO Loader Script to Download & Upload BI Publisher Data Template and XML Template

 --Download XML file --

java oracle.apps.xdo.oa.util.XDOLoader \

DOWNLOAD \

-DB_USERNAME apps \

-DB_PASSWORD apps \

-JDBC_CONNECTION db.test.com:1605/ebs_BSA \

-LOB_TYPE DATA_TEMPLATE  \

-APPS_SHORT_NAME XXGT \

-LOB_CODE MVL_NOTF_PO_BUY_RCVD_PRCNTGE \

-OUTPUT_PATH /tmp/30oct25/MVL_NOTF_PO_BUY_RCVD_PRCNTGE.xml

 

--Download XLS Template file--

java oracle.apps.xdo.oa.util.XDOLoader \

DOWNLOAD \

-DB_USERNAME apps \

-DB_PASSWORD apps \

-JDBC_CONNECTION db.test.com:1605/ebs_BSA \

-LOB_TYPE TEMPLATE  \

-APPS_SHORT_NAME XXGT \

-LOB_CODE MVL_NOTF_PO_BUY_RCVD_PRCNTGE \

-OUTPUT_PATH /tmp/30oct25/MVL_NOTF_PO_BUY_RCVD_PRCNTGE.xls

 

 

--Download RTF Template file--

java oracle.apps.xdo.oa.util.XDOLoader \

DOWNLOAD \

-DB_USERNAME apps \

-DB_PASSWORD apps \

-JDBC_CONNECTION db.test.com:1605/ebs_BSA \

-LOB_TYPE TEMPLATE  \

-APPS_SHORT_NAME XXGT \

-LOB_CODE MVL_NOTF_PO_BUY_RCVD_PRCNTGE \

-OUTPUT_PATH /tmp/30oct25/MVL_NOTF_PO_BUY_RCVD_PRCNTGE.rtf

Oracle EBS - How to Upload RTF Using XDOLoader Utility

 Subject: How to Upload RTF Using XDOLoader Utility

--RTF Upload Using XDOLoader Utility--

 

Step1: Uploading the RTF file on TEST file server home directory. 

Step2: Change the TEST application  DB_USERNAME, DB_PASSWORD,JDBC_CONNECTION, FILE_NAME

Step3: Execute the script in TEST using putty.

 

--RTF Upload Command--
java oracle.apps.xdo.oa.util.XDOLoader \
UPLOAD \
-DB_USERNAME apps \
-DB_PASSWORD p7v8S36 \
-JDBC_CONNECTION odev3.telamon.telamon-corp.com:1522/TEST \
-LOB_TYPE TEMPLATE \
-APPS_SHORT_NAME XXTLM \
-LOB_CODE XXTLM_PRJ_SO_MATRIAL_STATUS \
-LANGUAGE en \
-TERRITORY US \
-NLS_LANG American_America.WE8ISO8859P1 \
-XDO_FILE_TYPE RTF \
-FILE_CONTENT_TYPE 'text/html' \
-FILE_NAME XXTLM_PRJ_SO_MATRIAL_STATUS.rtf

 

SQL Query - Get the Journal Batch Workflow Item Key

SELECT item_type, item_key

FROM wf_items

WHERE user_key = '<journal batch name>';

Oracle PLSQL Script - How to Generate File and Place it on Oracle File Server Using Oracle Utility

 DECLARE

  -- Declare file handler
  file_handle UTL_FILE.FILE_TYPE;

  -- Declare variables for data to be written
  v_line VARCHAR2(100);
  v_filename VARCHAR2(50) := 'handlingunits'||TO_CHAR(SYSDATE,'MMDDRRHH24MISS')||'.csv';  -- Name of the file
  v_dir VARCHAR2(50) := 'XX_CAO_OUTBOUND';  -- Directory path (ensure it's accessible by Oracle)

 

BEGIN
  -- Open the file for writing. If the file does not exist, it will be created.
  file_handle := UTL_FILE.FOPEN(v_dir, v_filename, 'w');  -- 'w' mode means write

  -- Example data to write to the file
  FOR xxtlm_rec IN (SELECT DISTINCT ';;;'||item||';'||vendor_lot||';1;1;1;' line
                    FROM xxtlm.xxtlm_rcv_transactions xrt
                    WHERE xrt.organization_id = (SELECT organization_id FROM mtl_parameters WHERE organization_code = 'TIF')
                        AND xrt.status_id = 2
                        AND transaction_type = 'RECEIVE'
                    UNION
                    SELECT '99999999;99999999;99999999;99999999;99999999;99999999;99999999;99999999;99999999;99999999'
                    FROM dual
                    ORDER BY 1 DESC) 
    LOOP    
    -- Write the data to the file
    UTL_FILE.PUT_LINE(file_handle, xxtlm_rec.line);
  END LOOP;

  -- Close the file after writing
  UTL_FILE.FCLOSE(file_handle);

 

  -- Success message
  DBMS_OUTPUT.PUT_LINE('Data written successfully to ' || v_dir || '/' || v_filename);

 

EXCEPTION
  WHEN OTHERS THEN
    -- Handle any exceptions
    DBMS_OUTPUT.PUT_LINE('Exception: '||SQLERRM);
    IF UTL_FILE.IS_OPEN(file_handle) THEN
      UTL_FILE.FCLOSE(file_handle);
    END IF;
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

Oracle Fusion - SQL Query to get User Need to Approve pending approval Purchase Requestion and Purchase Order Transaction Details

 SELECT

    'Requisition'                             type,

    prha.document_status                      approval_status,

    to_char(prha.creation_date, 'DD-MM-YYYY') po_date,

    prha.requisition_number                   requisition_number,

    replace(replace(prha.description,

                    CHR(13),

                    ''),

            CHR(10),

            '')                               pr_po_desc,

    (

        SELECT

            SUM((nvl(unit_price, amount) * nvl(quantity, 1)))

        FROM

            por_requisition_lines_all

        WHERE

            requisition_header_id = prha.requisition_header_id

    )                                         pr_po_amount,

    (

        SELECT

            prla.currency_code

        FROM

            por_requisition_lines_all prla

        WHERE

                prla.requisition_header_id = prha.requisition_header_id

            AND ROWNUM = 1

    )                                         currency,

    prha.requisition_header_id,

    NULL                                      supplier_name,

    wf.assigneesdisplayname                   pending_with_whom,

    to_char(wf.assigneddate, 'DD-MM-YYYY')    submit_for_appr_date,

    email.*,

    wf.identificationkey,

    wf.PROCESSNAME,

    wf.STATE

FROM

    po_action_history           pah,

    por_requisition_headers_all prha,

    fa_fusion_soainfra.wftask   wf,

-- FA_FUSION_SOAINFRA.WFASSIGNEE_VIEW WASSS_V,

    hr_operating_units          d,

    (

        SELECT

            panf.full_name,

            fu.username,

            pea.email_address

        FROM

            per_users           fu,

            per_all_people_f    papf,

            per_person_names_f  panf,

            per_email_addresses pea

        WHERE

                fu.person_id = papf.person_id

            AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date

            AND panf.person_id = papf.person_id

            AND sysdate BETWEEN panf.effective_start_date AND panf.effective_end_date

            AND panf.name_type = 'GLOBAL'

            AND pea.person_id = papf.person_id

            AND sysdate BETWEEN nvl(pea.date_from, sysdate - 1) AND nvl(pea.date_to, sysdate + 1)

    )                           email

WHERE

        pah.object_id = prha.requisition_header_id

    AND prha.req_bu_id = d.organization_id

    AND pah.object_type_code = 'REQ'

    AND prha.document_status IN ( 'PENDING APPROVAL' )

    AND wf.compositeinstanceid = prha.approval_instance_id

    AND wf.assignees IS NOT NULL

    AND wf.tasknumber = (

        SELECT

            MAX(tasknumber)

        FROM

            fa_fusion_soainfra.wftask wf1

        WHERE

                wf.correlationid = wf1.correlationid -- and wf1.state = 'ASSIGNED'

            AND wf1.compositeinstanceid = prha.approval_instance_id

    )

-- AND WASSS_V.taskid(+) = wf.taskid

    AND email.username (+) = upper(substr(wf.assignees,

                                          1,

                                          instr(wf.assignees, ',') - 1)) ----WASSS_V.ASSIGNEE

    AND pah.sequence_num = (

        SELECT

            MAX(pah1.sequence_num)

        FROM

            po_action_history pah1

        WHERE

                pah1.object_id = pah.object_id -----rEQ---

            AND pah1.object_type_code = 'REQ'

            AND prha.document_status IN ( 'PENDING APPROVAL' ) -- AND pah1.action_code = nvl(:p_status,pah1.action_code)

    )

---Parameters

    -- AND prha.requisition_header_id = nvl(:por_number, prha.requisition_header_id)

    -- AND decode(:p_bu, NULL, 1, d.organization_id) = nvl(:p_bu, 1)

-- AND NVL (:PO_Number,1) = 1

UNION ALL

SELECT

    'Purchase Order',

    pv.change_order_status                  approval_status,

    to_char(ph.creation_date, 'DD-MM-YYYY') po_date,

--------------

    ph.segment1                             po_number,

    replace(replace(ph.comments,

                    CHR(13),

                    ''),

            CHR(10),

            '')                             pr_po_desc,

    (

        SELECT

            SUM(nvl(pl1.unit_price, pl1.amount) * decode(pl1.purchase_basis, 'SERVICES', 1, pl1.quantity))

        FROM

            po_lines_all pl1

        WHERE

            pl1.po_header_id = ph.po_header_id

    )                                       pr_po_amount,

    ph.currency_code                        currency,

    ph.po_header_id,

    pv.vendor_name                          supplier_name,

    wf.assigneesdisplayname                 pending_with_whom,

    to_char(wf.assigneddate, 'DD-MM-YYYY')  submit_for_appr_date,

    email.*,

    wf.identificationkey,

    wf.PROCESSNAME,

    wf.STATE

FROM

    po_action_history         poh,

    fa_fusion_soainfra.wftask wf,

-- FA_FUSION_SOAINFRA.WFASSIGNEE_VIEW WASSS_V,

    po_headers_all            ph,

    po_versions               pv,

    poz_suppliers_v           pv,

    hr_operating_units        d,

    (

        SELECT

            panf.full_name,

            fu.username,

            pea.email_address

        FROM

            per_users           fu,

            per_all_people_f    papf,

            per_person_names_f  panf,

            per_email_addresses pea

        WHERE

                fu.person_id = papf.person_id

            AND sysdate BETWEEN papf.effective_start_date AND papf.effective_end_date

            AND panf.person_id = papf.person_id

            AND sysdate BETWEEN panf.effective_start_date AND panf.effective_end_date

            AND panf.name_type = 'GLOBAL'

            AND pea.person_id = papf.person_id

            AND sysdate BETWEEN nvl(pea.date_from, sysdate - 1) AND nvl(pea.date_to, sysdate + 1)

    )                         email

WHERE

        poh.correlation_id = wf.correlationid

    AND poh.object_id = ph.po_header_id

    AND ph.po_header_id = pv.po_header_id

    AND wf.compositeinstanceid = pv.approval_instance_id

--AND wf.state = 'ASSIGNED'

    AND assignees IS NOT NULL

-- AND ph.segment1 = 'ISG0000048'

    AND poh.object_type_code = 'PO'

-- AND poh.ACTION_CODE = 'SUBMIT'

-- AND ph.DOCUMENT_STATUS IN ('PENDING APPROVAL')

-- ('WITHDRAWN', 'REJECTED', 'PENDING APPROVAL')

    AND pv.change_order_status = 'PENDING APPROVAL'

    AND ph.vendor_id = pv.vendor_id

    AND ph.prc_bu_id = d.organization_id

    AND wf.tasknumber = (

        SELECT

            MAX(tasknumber)

        FROM

            fa_fusion_soainfra.wftask wf1

        WHERE

                wf.correlationid = wf1.correlationid --

            AND wf1.state = 'ASSIGNED'

            AND wf1.compositeinstanceid = pv.approval_instance_id

    )

-- AND WASSS_V.taskid(+) = wf.taskid

    AND email.username (+) = upper(substr(wf.assignees,

                                          1,

                                          instr(wf.assignees, ',') - 1))

    AND poh.sequence_num = (

        SELECT

    max ( pah1.sequence_num ) FROM po_action_history pah1 WHERE pah1.object_id = poh.object_id -----Po_header_id

                                                                AND pah1.object_type_code = 'PO' -- AND pah1.action_code = nvl(:p_status,pah1.action_code)

    )

---Parameters

    -- AND ph.po_header_id = nvl(:po_number, ph.po_header_id)

    -- AND decode(:p_bu, NULL, 1, ph.prc_bu_id) = nvl(:p_bu, 1)

-- AND NVL (:POR_Number,1) =1

Oracle EBS - How to Enable Debug log on user's session

  1) As System Administrator responsibility

       Navigation: Profile -> System

           a) In the FIND window,

                  Enter User Name: User submitting the request
                  Search Profile: FND: Diag%

           b) Set the following profile option at User level:
                  FND: Diagnostics -> Yes

           c) Click the FIND flashlight and search Profile: FND: Debug%

               Set the following profile options at USER level:

                  FND: Debug Log Enabled =  Yes
                  FND: Debug Log Module = %
                  FND: Debug Log Level = Statement

                  FND: Debug Log Mode = Asynchronous with Cross-Tier Sequencing (SITE level)

  2) Run the following query to get the current log sequence
          
      SELECT MAX(log_sequence)
      FROM FND_LOG_MESSAGES;
    
  3) Perform the action that is giving problems.

  4) Run the following query to obtain the FND Messages while replicating the error in step 3:

      SELECT *
      FROM fnd_log_messages
      WHERE log_sequence > &max_log_sequence_from_step_2
      ORDER by log_sequence;

  5) Upload to the SR results of step #4 in an EXCEL spreadsheet, formatted with column headings for readability.

   6) After completion, reset step 1c "FND: Debug Log Enabled" back to "No" to avoid impact on performance.

Oracle EBS - Script to Update the Purchase Order Buyer using Oracle Pubic API

Requirement:

Need script which can update buyer of standard purchase order. This is pretty much useful when buyer of current purchase has to be changed due to various business reasons like buyer left organization or moving to another department 

PLSQL:

SET SERVEROUTPUT ON;

DECLARE

  lv_msg_data          VARCHAR2(100);

  ln_msg_count         NUMBER;

  lv_return_status     VARCHAR2(1); 

  ln_org_id            NUMBER;

  ln_vendor_id         NUMBER;

  ln_changed_buyer_id  NUMBER;

  ln_current_buyer_id  NUMBER;

  ln_new_buyer_id      NUMBER DEFAULT '6148';

  ln_valid_buyer       NUMBER;

  lv_document_no       VARCHAR2(200) DEFAULT '600338'; 

  le_custom_exception  EXCEPTION;

BEGIN 

  -- check whether the given person is a valid buyer

  SELECT COUNT(1)

    INTO ln_valid_buyer

    FROM po_buyers_val_v

   WHERE employee_id = ln_new_buyer_id;

   

  IF ln_valid_buyer = 0

  THEN

    DBMS_OUTPUT.PUT_LINE('Error: The given "to person id" is not a valid buyer');

    RAISE le_custom_exception;

  END IF;

  SELECT org_id,

         vendor_id,

         agent_id        

    INTO ln_org_id,

         ln_vendor_id,

         ln_current_buyer_id

    FROM po_headers_all pha 

   WHERE pha.segment1 = lv_document_no;

  

  -- check whether the given person is already a buyer of the document

  IF ln_current_buyer_id = ln_new_buyer_id

  THEN

    DBMS_OUTPUT.PUT_LINE('Error: The "to person id" is already a buyer of document');

    RAISE le_custom_exception;

  END IF; 

  

  fnd_global.apps_initialize(user_id => 26406, 

                              resp_id => 50769, 

                              resp_appl_id => 201);

                            

  mo_global.init('PO'); 

  fnd_client_info.set_org_context(ln_org_id);

  mo_global.set_policy_context ('S', ln_org_id);

  DBMS_OUTPUT.PUT_LINE('--API Call--');

  PO_MASS_UPDATE_PO_GRP.UPDATE_PERSONS

           (

            p_update_person       => 'BUYER',

            p_old_personid        => ln_current_buyer_id,

            p_new_personid        => ln_new_buyer_id,

            p_document_type       => 'STANDARD',

            p_document_no_from    => lv_document_no,

            p_document_no_to      => lv_document_no,

            p_date_from           => NULL,

            p_date_to             => NULL,

            p_supplier_id         => NULL,

            p_include_close_po    => 'YES',

            p_commit_interval     => 1,

            p_msg_data            => lv_msg_data,

            p_msg_count           => ln_msg_count,

            p_return_status       => lv_return_status

          );

          

   SELECT agent_id 

     INTO ln_changed_buyer_id

     FROM po_headers_all pha 

    WHERE pha.segment1 = lv_document_no;

  

  IF ln_new_buyer_id <> ln_changed_buyer_id

  THEN 

    DBMS_OUTPUT.PUT_LINE('msg_data'|| lv_msg_data);

    DBMS_OUTPUT.PUT_LINE('msg_count'|| ln_msg_count);

    DBMS_OUTPUT.PUT_LINE('return_status'|| lv_return_status);                            

  ELSE 

    DBMS_OUTPUT.PUT_LINE('Successfully Updated');

  END IF;

EXCEPTION 

  WHEN le_custom_exception THEN 

    DBMS_OUTPUT.PUT_LINE('Custom Error. Hence, action Aborted');

  WHEN OTHERS THEN 

    DBMS_OUTPUT.PUT_LINE('Error: '||SQLERRM);

END;