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
Ótimas dicas