API to released/apply holds

This post I show how apply/released holds on Oracle EBS. Oracle  provides an API to do this for a scripts

Released Holds

 DECLARE
 l_hold_source_rec OE_Holds_PVT.Hold_Source_REC_type;
 l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
 l_msg_count NUMBER;
 l_msg_data VARCHAR2 (2000);
 l_return_status VARCHAR2 (100);
 l_vmsg_data VARCHAR2 (2000);
 BEGIN

SELECT oe_hold_releases_s.nextval
 INTO l_hold_release_rec.hold_release_id
 FROM dual;

l_hold_source_rec.hold_id := l_hold_source_rec.hold_id;
 l_hold_source_rec.hold_entity_code := 'O';
 l_hold_source_rec.hold_entity_id := l_hold_source_rec.header_id;
 l_hold_source_rec.line_id := l_hold_source_rec.line_id;
 l_hold_source_rec.org_id := l_hold_source_rec.org_id;
 l_hold_release_rec.release_comment := 'Pedido Aprovado';
 l_hold_release_rec.release_reason_code := 'AR_APRO';
 l_hold_release_rec.order_hold_id := l_hold_release_rec.order_hold_id;
 l_hold_release_rec.hold_source_id := l_hold_source_rec.hold_source_id;

MO_GLOBAL.SET_POLICY_CONTEXT ('S', l_hold_source_rec.org_id);
 FND_CLIENT_INFO.SET_ORG_CONTEXT (l_hold_source_rec.org_id);
 FND_MSG_PUB.INITIALIZE;

Oe_Holds_PUB.Release_Holds (
 P_Api_Version => 1.0,
 P_Validation_Level => Fnd_Api.G_Valid_Level_None,
 P_Hold_Source_Rec => l_Hold_Source_Rec,
 p_hold_release_rec => l_Hold_Release_Rec,
 x_Msg_Count => l_Msg_Count,
 x_Msg_Data => l_Msg_Data,
 x_Return_Status => l_Return_Status);
 END;

Apply holds

 DECLARE
 l_hold_source_rec OE_Holds_PVT.Hold_Source_REC_type;
 l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type;
 l_msg_count NUMBER;
 l_msg_data VARCHAR2 (2000);
 l_return_status VARCHAR2 (100);
 l_vmsg_data VARCHAR2 (2000);
 BEGIN
 l_hold_source_rec.hold_id := p_hold_id;
 l_hold_source_rec.hold_entity_code := 'O';
 l_hold_source_rec.hold_entity_id := p_header_id;
 l_hold_source_rec.line_id := p_line_id;
 l_hold_source_rec.org_id := p_org_id;
 --
 OE_Holds_PUB.Apply_Holds ( p_api_version => 1.0,
 p_commit => FND_API.G_TRUE,
 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
 p_hold_source_rec => l_hold_source_rec,
 x_msg_count => l_msg_count,
 x_msg_data => l_msg_data,
 x_return_status => l_return_status);
 END;

Verify permissions to apply/released holds.

DECLARE
 l_hold_source_rec OE_HOLDS_PVT.hold_source_rec_type;
 l_msg_count NUMBER;
 l_msg_data VARCHAR2 (2000);
 l_return_status VARCHAR2 (100);
 v_email VARCHAR2 (400);
 v_select VARCHAR2 (32767);
 
 BEGIN 
 
 FND_GLOBAL.APPS_INITIALIZE (26801, 21623, 660);
 
 mo_global.set_policy_context('S',0);

l_hold_source_rec.hold_id := 1002;
 l_hold_source_rec.hold_entity_code := 'O';
 l_hold_source_rec.hold_entity_id := 8446228;
 l_hold_source_rec.header_id := 8446228;
 l_hold_source_rec.line_id := 7937718;
 l_hold_source_rec.org_id := 0;

OE_Holds_PUB.Apply_Holds ( p_api_version => 1.0,
 p_commit => FND_API.G_TRUE,
 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
 p_hold_source_rec => l_hold_source_rec,
 x_msg_count => l_msg_count,
 x_msg_data => l_msg_data,
 x_return_status => l_return_status );
 
 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
 dbms_output.put_line('success:');
 
 COMMIT;
 
 ELSIF l_return_status IS NULL THEN
 
 DBMS_OUTPUT.PUT_LINE('Status is null');
 
 ELSE
 DBMS_OUTPUT.PUT_LINE('failure:'|| l_msg_data );

FOR i IN 1 .. l_msg_count
 LOOP
 l_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
 dbms_output.put_line( i|| ') '|| l_msg_data);
 END LOOP;
 
 ROLLBACK;
 END IF;

EXCEPTION WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE('Error : '||SQLCODE||'---'||SQLERRM);
 END;

I holp this help