How TO Release Order Holds ?

Follwoing Procedure To Release Order Hold

    CREATE OR REPLACE PROCEDURE xxx_releaseholds_procedure_xxx (
                                                                    p_user_name  IN       VARCHAR2
                                                                    --,p_order_number IN  NUMBER
                                                                    ,p_errbuf    OUT      VARCHAR2
                                                                    ,p_retcode   OUT      NUMBER
                                                               )
    -- ==================== SQL Script Start ======================
    IS
    -- ============================================================
        -- Created By Chaitanya Tatuskar and Krishna Lobhe and Nilesh Dudhane
        -- Created on 04-JULY-2013
        -- Purpose: Releasing Order holds
        -- Version 1.1
       
        -- DROP PROCEDURE XXXOST_ReleaseHolds;
        -- EXEC XXXOST_ReleaseHolds('SETUPUSER',110042);
    -- ============================================================
       l_org_id                    NUMBER;
       g_retcode_success           NUMBER        := 0;
       g_retcode_warning           NUMBER        := 0;
       g_retcode_error             NUMBER        := 0;
       CURSOR cur_orders_hold
       IS
          /*
            SELECT DISTINCT  hdra.header_id
                          ,hdra.order_number
                          ,hsrc.hold_source_id
                          ,hsrc.hold_id
                     ---------- NEW ADDITION ---------
                          ,hdra.org_id
                          ,ool.request_date
                          ,ool.LINE_ID
                     ---------- NEW Closed ----------
                     FROM ,oe_order_headers_all hdra
                          ,oe_order_holds_all hlda
                          ,oe_hold_sources_all hsrc
                          ,oe_hold_definitions hdef
                    ---------- NEW ADDITION ---------
                          ,oe_order_lines_all ool
                    ---------- NEW Closed -----------
          WHERE           1 = 1
                    ---------- NEW ADDITION ----------
                      AND hdra.order_number = p_order_number --110042
                      AND ool.header_id = hdra.header_id
                      ---------- NEW Closed ----------
                      AND hdra.header_id = hlda.header_id
                      AND hlda.hold_source_id = hsrc.hold_source_id
                      AND hsrc.hold_id = hdef.hold_id
                      AND hlda.released_flag = 'N'
                      AND TO_DATE(ool.request_date - 1) = TO_DATE(SYSDATE,'DD-MON-YYYY');  --'01-JUN-2014';
                      */
                     
          SELECT  --DISTINCT
                    hlda.order_hold_id
                   ,hdra.header_id
                   ,hdra.org_id
                   ,ool.line_id
                   ,hdra.order_number
                   ,hsrc.hold_id
                   ,TO_CHAR(ool.request_date,'DD-MON-YYYY') "Request Date"
          FROM    oe_order_headers_all hdra
                  ,oe_order_holds_all hlda
                  ,oe_order_lines_all ool
                  ,oe_hold_sources_all hsrc
          WHERE   ool.header_id                 = hdra.header_id
          AND     ool.line_id                   = hlda.line_id
          AND     hdra.header_id                = hlda.header_id
          AND     hlda.released_flag            = 'N'
          AND     hlda.hold_source_id           = hsrc.hold_source_id
          --AND    TO_DATE(ool.request_date)    = TO_DATE('01-NOV-2013')
          AND     TO_DATE(ool.request_date - 1) = TO_DATE(SYSDATE,'DD-MON-YYYY')
          ORDER BY ool.LINE_ID;

       p_hold_source_rec    oe_holds_pvt.hold_source_rec_type;
       p_hold_release_rec   oe_holds_pvt.hold_release_rec_type;
       p_order_tbl          oe_holds_pvt.order_tbl_type;
       idx                  NUMBER;
       x_return_status      VARCHAR2 (1);
       x_msg_count          NUMBER;
       x_msg_data           VARCHAR2 (2000);

       --Sub Program
       --Apps Initialize

       PROCEDURE apps_initialize (
                                      p_org_id               NUMBER,
                                      p_user_name            VARCHAR2,
                                      p_resp_key             VARCHAR2,
                                      px_err_msg    IN OUT   VARCHAR2
                                 )
       IS
          l_user_id   NUMBER;
          l_resp_id   NUMBER;
          l_appl_id   NUMBER;
       BEGIN
          px_err_msg := NULL;

          --Get User ID based upon User Name;
          SELECT user_id
            INTO l_user_id
            FROM fnd_user
           WHERE user_name = p_user_name;   --'SETUPUSER';

          --Get User ID based upon User Name;
          SELECT responsibility_id, application_id
            INTO l_resp_id, l_appl_id
            FROM fnd_responsibility
           WHERE responsibility_key = p_resp_key;   --'S_I_O_M_S_U';

          fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);
          DBMS_OUTPUT.put_line ('User ID : ' || l_user_id);
          DBMS_OUTPUT.put_line ('Responsibility ID : ' || l_resp_id);
          DBMS_OUTPUT.put_line ('Application ID : ' || l_appl_id);

          BEGIN
             mo_global.set_policy_context ('S', p_org_id);
             --mo_global.set_policy_context ('S', 85);
             COMMIT;
          EXCEPTION
             WHEN OTHERS
             THEN
                DBMS_OUTPUT.put_line ('Org_id' || SQLERRM);
          END;
       EXCEPTION
          WHEN OTHERS
          THEN
             px_err_msg := 'ExpErr:apps_initialize : ' || SQLERRM;
       END;

    BEGIN
      
       SELECT DISTINCT  hdra.ORG_ID
       INTO             l_org_id
       FROM             oe_order_headers_all hdra
                        ,oe_order_holds_all hlda
                        ,oe_order_lines_all ool
       WHERE            ool.header_id = hdra.header_id
       AND              ool.line_id = hlda.line_id
       AND              hdra.header_id = hlda.header_id
       AND              hlda.released_flag = 'N'
       AND              TO_DATE(ool.request_date) = TO_DATE(SYSDATE,'DD-MON-YYYY');

       --Main Program
       oe_msg_pub.initialize;
       apps_initialize (l_org_id, p_user_name, 'S_I_O_M_S_U', x_msg_data);
                        -- Pass Organization id, User Name , Responsibility key , message data);

       IF x_msg_data IS NOT NULL
       THEN
          DBMS_OUTPUT.put_line ('Init call failed. Error : ' || x_msg_data);
          RETURN;
       END IF;

       x_msg_data := NULL;

       --Loop through each order and unrealed holds
       FOR l_hold_rec IN cur_orders_hold
       LOOP
          p_order_tbl (1).header_id                                             := l_hold_rec.header_id;
          p_order_tbl (1).line_id                                               := l_hold_rec.line_id;

          oe_holds_pub.release_holds(
                                        p_api_version                           => 1.0,
                                        p_order_tbl                             => p_order_tbl,            
                                        --p_hold_id                               => l_hold_rec.order_hold_id,     
                                        p_hold_id                               => l_hold_rec.hold_id,
                                        p_release_reason_code                   => 'OTHER',
                                        p_release_comment                       => 'Released Through Release Hold API Call',
                                        x_return_status                         => x_return_status,
                                        x_msg_count                             => x_msg_count,
                                        x_msg_data                              => x_msg_data
                                    );
          COMMIT;
          DBMS_OUTPUT.put_line ('===== API Status for Order Number :' || l_hold_rec.order_number || ' =====' );
          DBMS_OUTPUT.put_line ('Return Status = ' || x_return_status);
          DBMS_OUTPUT.put_line ('Message Count = ' || x_msg_count);
          DBMS_OUTPUT.put_line ('Message Data = :' || x_msg_data || ' :');
       END LOOP;

       --Look for Any error messages from API
       FOR j IN 1 .. oe_msg_pub.count_msg
       LOOP
          oe_msg_pub.get (
                            p_msg_index                                         => j,
                            p_encoded                                           => 'F',
                            p_data                                              => x_msg_data,
                            p_msg_index_out                                     => idx
                         );
          DBMS_OUTPUT.put_line ('Error : ' || j || ' : ' || x_msg_data);
       END LOOP;
    EXCEPTION WHEN OTHERS THEN
          x_return_status := 'E';
          x_msg_data := 'ExpErr:XXXOST_ReleaseHolds. Error : ' || SQLERRM;
          DBMS_OUTPUT.put_line ('ExpErr:XXXOST_ReleaseHolds. Error : ' || SQLERRM);
          p_retcode := g_retcode_error;
          p_errbuf := 'Error in xx_txis_attendance_report' || SQLERRM;
          apps.fnd_file.put_line (apps.fnd_file.LOG,
                                 'Error in xx_txis_attendance_report' || SQLERRM
                                );
          RETURN;
    -- ==================== SQL Script End ====================
   
    END xxx_releaseholds_procedure_xxx;