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;