Apply Order Holds Using OM API

CREATE OR REPLACE PROCEDURE skm_applyHolds( 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; CURSOR c_orders IS select hdr.header_id from oe_order_headers_all hdr where hdr.order_number between p_order_num_low and p_order_num_high ; -- -- CURSOR c_holds IS SELECT hold_id FROM oe_hold_definitions ; 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'; init(p_user_name, 'ORDER_MGMT_SUPER_USER', l_msg_data); -- -- OE_MSG_PUB.initialize; -- -- 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; -- -- FOR l_hold_rec IN c_holds LOOP -- -- Loop through each order and release hold -- FOR l_orders in c_orders LOOP l_order_tbl(1).header_id := l_orders.header_id; OE_HOLDS_PUB.apply_holds ( p_api_version => 6000 /* this parameter is of no significance */ , p_init_msg_list => FND_API.G_FALSE , p_commit => FND_API.G_FALSE , p_validation_level => FND_API.G_VALID_LEVEL_FULL /* this parameter is of no significance */ , p_order_tbl => l_order_tbl , p_hold_id => l_hold_rec.hold_id , p_hold_until_date => SYSDATE + 1 , p_hold_comment => 'Hold API test on ' to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS') , x_return_status => l_return_status , x_msg_count => l_msg_count , x_msg_data => l_msg_data ) ; 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 ':'); END LOOP; END LOOP; -- -- Look for Any error messages -- OE_MSG_PUB.count_and_get (p_encoded => 'T' , p_count => l_msg_count , p_data => l_msg_data ) ; dbms_output.put_line('Total Messages:' l_msg_count); dbms_output.put_line('Error Message:' l_msg_data); -- -- 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; COMMIT;END skm_applyHolds ;