Monday, June 21, 2010

OE_HOLDS_PUB.APPLY_HOLDS -- Apply Hold Script

Below script will help you to apply Order level or Line Level hold in Oracle Order Management through API OE_HOLDS_PUB.APPLY_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_hold_source_rec  OE_HOLDS_PVT.HOLD_SOURCE_REC_TYPE;
v_hold_id          NUMBER       DEFAULT 50;
v_hold_entity_code VARCHAR2(10) DEFAULT 'O';
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 ------------

v_hold_source_rec                  := OE_HOLDS_PVT.G_MISS_HOLD_SOURCE_REC;
v_hold_source_rec.hold_id          := v_hold_id;
v_hold_source_rec.hold_entity_code := v_hold_entity_code;
v_hold_source_rec.hold_entity_id   := v_header_id;
v_hold_source_rec.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 Apply hold' );

OE_HOLDS_PUB.APPLY_HOLDS (
                       p_api_version     => 1.0,
                       p_init_msg_list   => FND_API.G_TRUE,
                       p_commit          => FND_API.G_FALSE,
                       p_hold_source_rec => v_hold_source_rec,
                       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('failure:'|| v_msg_data );

FOR i IN 1 .. v_msg_count
     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 : '||SQLCODE||'---'||SQLERRM);
END;

2 Responses to “OE_HOLDS_PUB.APPLY_HOLDS -- Apply Hold Script”

Unknown said...
June 22, 2012 at 10:34 AM

Very Usefu..Nice


Anonymous said...
November 14, 2012 at 2:27 AM

Hi,

I am applying holds in OM_POST_BOOK_EVENT getting WHEN-BUTTON-PRESSED unhandled exception after booking order, what could be the issue, any inputs.


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.