Releasing Order Holds

CREATE OR REPLACE PROCEDURE skm_releaseHolds( p_user_name VARCHAR2, p_order_num_low NUMBER, p_order_num_high NUMBER) IS l_order_tbl OE_HOLDS_PVT.order_tbl_type; l_return_status VARCHAR2(5); l_msg_count NUMBER; l_msg_data VARCHAR2(2000); i NUMBER; l_hold_source_rec OE_HOLDS_PVT.hold_source_rec_type; l_hold_release_rec OE_HOLDS_PVT.Hold_Release_Rec_Type; CURSOR c_orders_held IS select /*+ index(hld OE_ORDER_HOLDS_ALL_N1) index(hsrc OE_HOLD_SOURCES_U1)*/ hdr.header_id , hsrc.hold_source_id , hsrc.hold_id from oe_order_headers_all hdr , oe_order_holds_all hld , oe_hold_sources_all hsrc , oe_hold_definitions hdef where hdr.order_number between p_order_num_low and p_order_num_high and hdr.header_id = hld.header_id and hld.hold_source_id = hsrc.hold_source_id and hsrc.hold_id = hdef.hold_id ;PROCEDURE get_user_id ( p_user_name VARCHAR2 , p_user_id IN OUT NUMBER , p_err_msg IN OUT VARCHAR2 ) ISBEGIN p_err_msg := null; SELECT user_id INTO p_user_id FROM fnd_user WHERE user_name = p_user_name; -- -- EXCEPTION WHEN OTHERS THEN p_user_id := -1; p_err_msg := 'procedure Get_user_Id error:'sqlerrm;END;PROCEDURE get_resp_id ( p_resp_key VARCHAR2 , p_resp_id IN OUT NUMBER , p_resp_appl_id IN OUT NUMBER , p_err_msg IN OUT VARCHAR2 ) ISBEGIN SELECT responsibility_id , application_id INTO p_resp_id , p_resp_appl_id FROM fnd_responsibility WHERE responsibility_key = p_resp_key; -- -- EXCEPTION WHEN OTHERS THEN p_resp_id := -1; p_resp_appl_id := -1; p_err_msg := 'procedure Get_resp_Id error:'sqlerrm;END;PROCEDURE init( p_user_name VARCHAR2, p_resp_key VARCHAR2, px_err_msg IN OUT VARCHAR2) Is l_user_id NUMBER; l_resp_id NUMBER; l_appl_id NUMBER;BEGIN px_err_msg := null; get_user_id(p_user_name, l_user_id, px_err_msg); IF px_err_msg IS NOT NULL THEN RETURN; END IF; -- get_resp_id(p_resp_key, l_resp_id, l_appl_id, px_err_msg); IF px_err_msg IS NOT NULL THEN RETURN; END IF; -- -- FND_GLOBAL.apps_initialize(l_user_id, l_resp_id, l_appl_id); dbms_output.put_line(l_user_id ',' l_resp_id ',' l_appl_id);END;begin l_return_status := 'S'; OE_MSG_PUB.initialize; init(p_user_name, 'ORDER_MGMT_SUPER_USER', l_msg_data); -- -- IF l_msg_data IS NOT NULL THEN dbms_output.put_line('init call failed with the following message'); dbms_output.put_line(l_msg_data); return; END IF; -- -- Loop through each order and release hold -- FOR l_orders_held in c_orders_held LOOP l_order_tbl(1).header_id := l_orders_held.header_id; oe_holds_pub.Release_Holds ( p_api_version => 1.0 , p_order_tbl => l_order_tbl , p_hold_id => l_orders_held.hold_id , p_release_reason_code => 'EXPIRE' , p_release_comment => 'Updated Through release hold API Call' , x_return_status => l_return_status , x_msg_count => l_msg_count , x_msg_data => l_msg_data ); END LOOP; dbms_output.put_line('Return Status = ' l_return_status); dbms_output.put_line('Message Count = ' l_msg_count); dbms_output.put_line('Message Data = :' l_msg_data ':'); -- -- Look for Any error messages -- FOR j in 1..OE_MSG_PUB.count_msg LOOP OE_MSG_PUB.get ( p_msg_index => j , p_encoded => 'F' , p_data => l_msg_data , p_msg_index_out => i ); dbms_output.put_line('Error: ' j ':' l_msg_data); END LOOP;end skm_releaseHolds ;