Sales Order Release Hold

create or replace PACKAGE xdmc_release_hold_pkg AUTHID CURRENT_USER
AS

PROCEDURE call_fnd_global (
p_user_id NUMBER,
p_resp_id NUMBER,
p_resp_appl_id NUMBER
);

PROCEDURE xdmc_release_hold (
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_order_number IN NUMBER
);

END;

create or replace PACKAGE BODY xdmc_release_hold_pkg
AS

PROCEDURE call_fnd_global (
p_user_id NUMBER,
p_resp_id NUMBER,
p_resp_appl_id NUMBER
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
fnd_global.apps_initialize (user_id => p_user_id,
resp_id => p_resp_id,
resp_appl_id => p_resp_appl_id
);
COMMIT;
END;

PROCEDURE xdmc_release_hold (
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_order_number IN NUMBER
)
IS
l_order_tbl oe_holds_pvt.order_tbl_type;
l_return_status VARCHAR2 (30);
l_msg_data VARCHAR2 (4000);
l_msg_count NUMBER;
l_resp_id NUMBER;
l_resp_appl_id NUMBER;
l_header_id NUMBER;
l_hold_id NUMBER;
l_release_flag VARCHAR2 (10);
l_org_id NUMBER;
l_user_id NUMBER := fnd_profile.VALUE (‘USER_ID’);
BEGIN
BEGIN
SELECT responsibility_id, application_id
INTO l_resp_id, l_resp_appl_id
FROM fnd_responsibility_tl
WHERE responsibility_name = ‘Order Management Super User’;
EXCEPTION
WHEN OTHERS
THEN
l_resp_id := 21623;
l_resp_appl_id := 660;
END;

BEGIN
SELECT ooh.header_id, ooh.org_id, ohd.hold_id, ohs.released_flag
INTO l_header_id, l_org_id, l_hold_id, l_release_flag
FROM apps.oe_order_headers_all ooh,
apps.oe_order_holds_all ohld,
apps.oe_hold_sources_all ohs,
apps.oe_hold_definitions ohd,
apps.oe_hold_releases ohr
WHERE ooh.order_category_code = ‘ORDER’
AND ohd.NAME = ‘Credit Check Failure’
AND ohld.header_id(+) = ooh.header_id
AND ooh.order_number = p_order_number
AND ohs.hold_source_id(+) = ohld.hold_source_id
AND ohd.hold_id(+) = ohs.hold_id
AND ohr.hold_release_id(+) = ohs.hold_release_id;
EXCEPTION
WHEN OTHERS
THEN
l_header_id := 0;
l_hold_id := 0;
l_release_flag := NULL;
END;

IF l_header_id > 0 AND l_hold_id > 0 AND l_release_flag = ‘N’
THEN
–Apps initialize
call_fnd_global (l_user_id, l_resp_id, l_resp_appl_id);
mo_global.set_policy_context (‘S’, l_org_id);
oe_debug_pub.setdebuglevel (5);
oe_msg_pub.initialize;
–Derive the Header record Table
l_order_tbl (1).header_id := l_header_id;
oe_debug_pub.ADD (‘Just before calling OE_Holds_PUB.Release_Holds:’);
–Calling Release Hold API
oe_holds_pub.release_holds
(p_api_version => 1.0,
p_order_tbl => l_order_tbl,
p_hold_id => 1,
p_release_reason_code => ‘AUTH_EPAYMENT’,
p_release_comment => ‘Released from Brain Tree Payment Authorization’,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
oe_debug_pub.ADD (‘Just after calling OE_Holds_PUB.Release_Holds:’);

— Check Return Status
IF l_return_status = fnd_api.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line (‘success’);
COMMIT;
ELSE
DBMS_OUTPUT.put_line (‘failure’);
ROLLBACK;
END IF;

— Display Return Status
DBMS_OUTPUT.put_line ( ‘process ORDER ret status IS: ‘
|| l_return_status
);
DBMS_OUTPUT.put_line (‘process ORDER msg data IS: ‘ || l_msg_data);
DBMS_OUTPUT.put_line (‘process ORDER msg COUNT IS: ‘ || l_msg_count);
oe_debug_pub.debug_off;
ELSE
DBMS_OUTPUT.put_line (‘No Hold For This Order: ‘);
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘Unexpected Error -‘ || SQLERRM);
END;
END;