This is an example of removing order holds using OM API
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
) IS
BEGIN
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
) IS
BEGIN
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 ;
===================================================================================
Below script will help you to Release Order level or Line Level hold in Oracle Order Management through API OE_HOLDS_PUB.RELEASE_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_order_tbl OE_HOLDS_PVT.order_tbl_type;
v_hold_id NUMBER DEFAULT 50;
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 ------------
BEGIN
v_order_tbl(1).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 Release hold' );
OE_HOLDS_PUB.RELEASE_HOLDS (
p_api_version => 1.0,
p_order_tbl => v_order_tbl,
p_hold_id => v_hold_id,
p_release_reason_code => 'AR_AUTOMATIC',
p_release_comment => 'TESTING',
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('Failed: '|| v_msg_data );
FOR i IN 1 .. oe_msg_pub.count_msg
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 is '||SQLCODE||'---'||SQLERRM);
END;
No comments:
Post a Comment