Follwoing Procedure To Release Order Hold
CREATE OR REPLACE PROCEDURE xxx_releaseholds_procedure_xxx (
p_user_name IN VARCHAR2
--,p_order_number IN NUMBER
,p_errbuf OUT VARCHAR2
,p_retcode OUT NUMBER
)
-- ==================== SQL Script Start ======================
IS
-- ============================================================
-- Created By Chaitanya Tatuskar and Krishna Lobhe and Nilesh Dudhane
-- Created on 04-JULY-2013
-- Purpose: Releasing Order holds
-- Version 1.1
-- DROP PROCEDURE XXXOST_ReleaseHolds;
-- EXEC XXXOST_ReleaseHolds('SETUPUSER',110042);
-- ============================================================
l_org_id NUMBER;
g_retcode_success NUMBER := 0;
g_retcode_warning NUMBER := 0;
g_retcode_error NUMBER := 0;
CURSOR cur_orders_hold
IS
/*
SELECT DISTINCT hdra.header_id
,hdra.order_number
,hsrc.hold_source_id
,hsrc.hold_id
---------- NEW ADDITION ---------
,hdra.org_id
,ool.request_date
,ool.LINE_ID
---------- NEW Closed ----------
FROM ,oe_order_headers_all hdra
,oe_order_holds_all hlda
,oe_hold_sources_all hsrc
,oe_hold_definitions hdef
---------- NEW ADDITION ---------
,oe_order_lines_all ool
---------- NEW Closed -----------
WHERE 1 = 1
---------- NEW ADDITION ----------
AND hdra.order_number = p_order_number --110042
AND ool.header_id = hdra.header_id
---------- NEW Closed ----------
AND hdra.header_id = hlda.header_id
AND hlda.hold_source_id = hsrc.hold_source_id
AND hsrc.hold_id = hdef.hold_id
AND hlda.released_flag = 'N'
AND TO_DATE(ool.request_date - 1) = TO_DATE(SYSDATE,'DD-MON-YYYY'); --'01-JUN-2014';
*/
SELECT --DISTINCT
hlda.order_hold_id
,hdra.header_id
,hdra.org_id
,ool.line_id
,hdra.order_number
,hsrc.hold_id
,TO_CHAR(ool.request_date,'DD-MON-YYYY') "Request Date"
FROM oe_order_headers_all hdra
,oe_order_holds_all hlda
,oe_order_lines_all ool
,oe_hold_sources_all hsrc
WHERE ool.header_id = hdra.header_id
AND ool.line_id = hlda.line_id
AND hdra.header_id = hlda.header_id
AND hlda.released_flag = 'N'
AND hlda.hold_source_id = hsrc.hold_source_id
--AND TO_DATE(ool.request_date) = TO_DATE('01-NOV-2013')
AND TO_DATE(ool.request_date - 1) = TO_DATE(SYSDATE,'DD-MON-YYYY')
ORDER BY ool.LINE_ID;
p_hold_source_rec oe_holds_pvt.hold_source_rec_type;
p_hold_release_rec oe_holds_pvt.hold_release_rec_type;
p_order_tbl oe_holds_pvt.order_tbl_type;
idx NUMBER;
x_return_status VARCHAR2 (1);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
--Sub Program
--Apps Initialize
PROCEDURE apps_initialize (
p_org_id NUMBER,
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 based upon User Name;
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = p_user_name; --'SETUPUSER';
--Get User ID based upon User Name;
SELECT responsibility_id, application_id
INTO l_resp_id, l_appl_id
FROM fnd_responsibility
WHERE responsibility_key = p_resp_key; --'S_I_O_M_S_U';
fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);
DBMS_OUTPUT.put_line ('User ID : ' || l_user_id);
DBMS_OUTPUT.put_line ('Responsibility ID : ' || l_resp_id);
DBMS_OUTPUT.put_line ('Application ID : ' || l_appl_id);
BEGIN
mo_global.set_policy_context ('S', p_org_id);
--mo_global.set_policy_context ('S', 85);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Org_id' || SQLERRM);
END;
EXCEPTION
WHEN OTHERS
THEN
px_err_msg := 'ExpErr:apps_initialize : ' || SQLERRM;
END;
BEGIN
SELECT DISTINCT hdra.ORG_ID
INTO l_org_id
FROM oe_order_headers_all hdra
,oe_order_holds_all hlda
,oe_order_lines_all ool
WHERE ool.header_id = hdra.header_id
AND ool.line_id = hlda.line_id
AND hdra.header_id = hlda.header_id
AND hlda.released_flag = 'N'
AND TO_DATE(ool.request_date) = TO_DATE(SYSDATE,'DD-MON-YYYY');
--Main Program
oe_msg_pub.initialize;
apps_initialize (l_org_id, p_user_name, 'S_I_O_M_S_U', x_msg_data);
-- Pass Organization id, User Name , Responsibility key , message data);
IF x_msg_data IS NOT NULL
THEN
DBMS_OUTPUT.put_line ('Init call failed. Error : ' || x_msg_data);
RETURN;
END IF;
x_msg_data := NULL;
--Loop through each order and unrealed holds
FOR l_hold_rec IN cur_orders_hold
LOOP
p_order_tbl (1).header_id := l_hold_rec.header_id;
p_order_tbl (1).line_id := l_hold_rec.line_id;
oe_holds_pub.release_holds(
p_api_version => 1.0,
p_order_tbl => p_order_tbl,
--p_hold_id => l_hold_rec.order_hold_id,
p_hold_id => l_hold_rec.hold_id,
p_release_reason_code => 'OTHER',
p_release_comment => 'Released Through Release Hold API Call',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
COMMIT;
DBMS_OUTPUT.put_line ('===== API Status for Order Number :' || l_hold_rec.order_number || ' =====' );
DBMS_OUTPUT.put_line ('Return Status = ' || x_return_status);
DBMS_OUTPUT.put_line ('Message Count = ' || x_msg_count);
DBMS_OUTPUT.put_line ('Message Data = :' || x_msg_data || ' :');
END LOOP;
--Look for Any error messages from API
FOR j IN 1 .. oe_msg_pub.count_msg
LOOP
oe_msg_pub.get (
p_msg_index => j,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => idx
);
DBMS_OUTPUT.put_line ('Error : ' || j || ' : ' || x_msg_data);
END LOOP;
EXCEPTION WHEN OTHERS THEN
x_return_status := 'E';
x_msg_data := 'ExpErr:XXXOST_ReleaseHolds. Error : ' || SQLERRM;
DBMS_OUTPUT.put_line ('ExpErr:XXXOST_ReleaseHolds. Error : ' || SQLERRM);
p_retcode := g_retcode_error;
p_errbuf := 'Error in xx_txis_attendance_report' || SQLERRM;
apps.fnd_file.put_line (apps.fnd_file.LOG,
'Error in xx_txis_attendance_report' || SQLERRM
);
RETURN;
-- ==================== SQL Script End ====================
END xxx_releaseholds_procedure_xxx;
p_user_name IN VARCHAR2
--,p_order_number IN NUMBER
,p_errbuf OUT VARCHAR2
,p_retcode OUT NUMBER
)
-- ==================== SQL Script Start ======================
IS
-- ============================================================
-- Created By Chaitanya Tatuskar and Krishna Lobhe and Nilesh Dudhane
-- Created on 04-JULY-2013
-- Purpose: Releasing Order holds
-- Version 1.1
-- DROP PROCEDURE XXXOST_ReleaseHolds;
-- EXEC XXXOST_ReleaseHolds('SETUPUSER',110042);
-- ============================================================
l_org_id NUMBER;
g_retcode_success NUMBER := 0;
g_retcode_warning NUMBER := 0;
g_retcode_error NUMBER := 0;
CURSOR cur_orders_hold
IS
/*
SELECT DISTINCT hdra.header_id
,hdra.order_number
,hsrc.hold_source_id
,hsrc.hold_id
---------- NEW ADDITION ---------
,hdra.org_id
,ool.request_date
,ool.LINE_ID
---------- NEW Closed ----------
FROM ,oe_order_headers_all hdra
,oe_order_holds_all hlda
,oe_hold_sources_all hsrc
,oe_hold_definitions hdef
---------- NEW ADDITION ---------
,oe_order_lines_all ool
---------- NEW Closed -----------
WHERE 1 = 1
---------- NEW ADDITION ----------
AND hdra.order_number = p_order_number --110042
AND ool.header_id = hdra.header_id
---------- NEW Closed ----------
AND hdra.header_id = hlda.header_id
AND hlda.hold_source_id = hsrc.hold_source_id
AND hsrc.hold_id = hdef.hold_id
AND hlda.released_flag = 'N'
AND TO_DATE(ool.request_date - 1) = TO_DATE(SYSDATE,'DD-MON-YYYY'); --'01-JUN-2014';
*/
SELECT --DISTINCT
hlda.order_hold_id
,hdra.header_id
,hdra.org_id
,ool.line_id
,hdra.order_number
,hsrc.hold_id
,TO_CHAR(ool.request_date,'DD-MON-YYYY') "Request Date"
FROM oe_order_headers_all hdra
,oe_order_holds_all hlda
,oe_order_lines_all ool
,oe_hold_sources_all hsrc
WHERE ool.header_id = hdra.header_id
AND ool.line_id = hlda.line_id
AND hdra.header_id = hlda.header_id
AND hlda.released_flag = 'N'
AND hlda.hold_source_id = hsrc.hold_source_id
--AND TO_DATE(ool.request_date) = TO_DATE('01-NOV-2013')
AND TO_DATE(ool.request_date - 1) = TO_DATE(SYSDATE,'DD-MON-YYYY')
ORDER BY ool.LINE_ID;
p_hold_source_rec oe_holds_pvt.hold_source_rec_type;
p_hold_release_rec oe_holds_pvt.hold_release_rec_type;
p_order_tbl oe_holds_pvt.order_tbl_type;
idx NUMBER;
x_return_status VARCHAR2 (1);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
--Sub Program
--Apps Initialize
PROCEDURE apps_initialize (
p_org_id NUMBER,
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 based upon User Name;
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = p_user_name; --'SETUPUSER';
--Get User ID based upon User Name;
SELECT responsibility_id, application_id
INTO l_resp_id, l_appl_id
FROM fnd_responsibility
WHERE responsibility_key = p_resp_key; --'S_I_O_M_S_U';
fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);
DBMS_OUTPUT.put_line ('User ID : ' || l_user_id);
DBMS_OUTPUT.put_line ('Responsibility ID : ' || l_resp_id);
DBMS_OUTPUT.put_line ('Application ID : ' || l_appl_id);
BEGIN
mo_global.set_policy_context ('S', p_org_id);
--mo_global.set_policy_context ('S', 85);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Org_id' || SQLERRM);
END;
EXCEPTION
WHEN OTHERS
THEN
px_err_msg := 'ExpErr:apps_initialize : ' || SQLERRM;
END;
BEGIN
SELECT DISTINCT hdra.ORG_ID
INTO l_org_id
FROM oe_order_headers_all hdra
,oe_order_holds_all hlda
,oe_order_lines_all ool
WHERE ool.header_id = hdra.header_id
AND ool.line_id = hlda.line_id
AND hdra.header_id = hlda.header_id
AND hlda.released_flag = 'N'
AND TO_DATE(ool.request_date) = TO_DATE(SYSDATE,'DD-MON-YYYY');
--Main Program
oe_msg_pub.initialize;
apps_initialize (l_org_id, p_user_name, 'S_I_O_M_S_U', x_msg_data);
-- Pass Organization id, User Name , Responsibility key , message data);
IF x_msg_data IS NOT NULL
THEN
DBMS_OUTPUT.put_line ('Init call failed. Error : ' || x_msg_data);
RETURN;
END IF;
x_msg_data := NULL;
--Loop through each order and unrealed holds
FOR l_hold_rec IN cur_orders_hold
LOOP
p_order_tbl (1).header_id := l_hold_rec.header_id;
p_order_tbl (1).line_id := l_hold_rec.line_id;
oe_holds_pub.release_holds(
p_api_version => 1.0,
p_order_tbl => p_order_tbl,
--p_hold_id => l_hold_rec.order_hold_id,
p_hold_id => l_hold_rec.hold_id,
p_release_reason_code => 'OTHER',
p_release_comment => 'Released Through Release Hold API Call',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
COMMIT;
DBMS_OUTPUT.put_line ('===== API Status for Order Number :' || l_hold_rec.order_number || ' =====' );
DBMS_OUTPUT.put_line ('Return Status = ' || x_return_status);
DBMS_OUTPUT.put_line ('Message Count = ' || x_msg_count);
DBMS_OUTPUT.put_line ('Message Data = :' || x_msg_data || ' :');
END LOOP;
--Look for Any error messages from API
FOR j IN 1 .. oe_msg_pub.count_msg
LOOP
oe_msg_pub.get (
p_msg_index => j,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => idx
);
DBMS_OUTPUT.put_line ('Error : ' || j || ' : ' || x_msg_data);
END LOOP;
EXCEPTION WHEN OTHERS THEN
x_return_status := 'E';
x_msg_data := 'ExpErr:XXXOST_ReleaseHolds. Error : ' || SQLERRM;
DBMS_OUTPUT.put_line ('ExpErr:XXXOST_ReleaseHolds. Error : ' || SQLERRM);
p_retcode := g_retcode_error;
p_errbuf := 'Error in xx_txis_attendance_report' || SQLERRM;
apps.fnd_file.put_line (apps.fnd_file.LOG,
'Error in xx_txis_attendance_report' || SQLERRM
);
RETURN;
-- ==================== SQL Script End ====================
END xxx_releaseholds_procedure_xxx;
No comments:
Post a Comment