OE_HOLDS_PUB.RELEASE_HOLDS -- Release Holds Script

Below script will help you to Release Order level or Line Level hold in Oracle Order Management through API OE_HOLDS_PUB.RELEASE_HOLDS

This script was tested in R12.1.1 

SET serveroutput ON;

DECLARE

v_return_status    VARCHAR2(30);

v_msg_data         VARCHAR2(4000);

v_msg_count        NUMBER;

v_order_tbl        OE_HOLDS_PVT.order_tbl_type;

v_hold_id          NUMBER DEFAULT 50;

v_header_id        NUMBER DEFAULT 1705;

v_context          VARCHAR2 (2);

FUNCTION set_context( i_user_name    IN VARCHAR2

                     ,i_resp_name    IN VARCHAR2

                     ,i_org_id       IN NUMBER)

RETURN VARCHAR2

IS

BEGIN

 NULL;

    -- In order to reduce the content of the post I moved the implementation part of this function to another post and it is   available here  

END set_context;

 

BEGIN

-- Setting the context ----

v_context := set_context ('&user', '&responsibility', 2038);

IF v_context = 'F'

   THEN

   DBMS_OUTPUT.put_line ('Error while setting the context');

END IF;

--- context done ------------

BEGIN

v_order_tbl(1).header_id           := v_header_id;

v_return_status                    := NULL;

v_msg_data                         := NULL;

v_msg_count                        := NULL;

dbms_output.put_line('Calling the API to Release hold' );

OE_HOLDS_PUB.RELEASE_HOLDS (

                         p_api_version         => 1.0,

                         p_order_tbl           => v_order_tbl,

                         p_hold_id             => v_hold_id,

                         p_release_reason_code => 'AR_AUTOMATIC',

                         p_release_comment     => 'TESTING',

                         x_return_status       => v_return_status,

                         x_msg_count           => v_msg_count,

                         x_msg_data            => v_msg_data

                           );

IF v_return_status = FND_API.G_RET_STS_SUCCESS THEN

dbms_output.put_line('success:');

COMMIT;

ELSIF v_return_status IS NULL THEN

dbms_output.put_line('Status is null');

ELSE

dbms_output.put_line('Failed: '|| v_msg_data );

FOR i IN 1 .. oe_msg_pub.count_msg

     LOOP

        v_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');

        dbms_output.put_line( i|| ') '|| v_msg_data);

     END LOOP;

    

ROLLBACK;

END IF;

EXCEPTION

WHEN OTHERS THEN

 dbms_output.put_line('Error is '||SQLCODE||'---'||SQLERRM);

END;