DECLARE
P_API_VERSION NUMBER;
REQUESTID number;
l_layout BOOLEAN;
l_del_no varchar2(100);
v_counter number;
P_INIT_MSG_LIST VARCHAR2(200);
P_COMMIT VARCHAR2(200);
P_VALIDATION_LEVEL NUMBER;
X_RETURN_STATUS VARCHAR2(200);
X_MSG_COUNT NUMBER;
P_TABOFDELDETS APPS.WSH_DELIVERY_DETAILS_PUB.ID_TAB_TYPE;
P_ACTION VARCHAR2(200);
P_DELIVERY_ID NUMBER;
P_DELIVERY_NAME VARCHAR2(200);
P_COUNT NUMBER;
V_CONTEXT VARCHAR2(100);
v_counter_1 number;
l_organization_id number;
l_organization_code VARCHAR2 (10);
l_return_statuss VARCHAR2 (2);
X_MSG_DATA VARCHAR2 (4000);
L_REQUEST_ID fnd_concurrent_requests.request_id%TYPE;
l_status_code fnd_concurrent_requests.status_code%TYPE := '~';
x_del_rows wsh_util_core.id_tab_type;
x_line_rows wsh_util_core.id_tab_type;
l_debug_level NUMBER := 0; -- OM DEBUG LEVEL (MAX 5)
l_return_status VARCHAR2 (100);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_del_rows NUMBER;
l_user_id NUMBER;
l_batch_id NUMBER;
l_batch_rec wsh_picking_batches_pub.batch_info_rec;
CURSOR cur_main IS
SELECT wdd.delivery_detail_id, wdd.organization_id,wnd.delivery_id,wnd.name
,wdd.source_header_type_name
FROM wsh_delivery_details wdd,wsh_delivery_assignments wda,
wsh_new_deliveries wnd
WHERE wdd.organization_id = p_org_id AND wdd.delivery_detail_id =
wda.delivery_detail_id
AND wda.delivery_id =wnd.delivery_id
AND wdd.released_status = 'S'
AND wdd.customer_id = p_cust_id --5346
ORDER BY wdd.customer_id;
CURSOR cur_assign IS SELECT delivery_detail_id, organization_id
FROM wsh_delivery_details ws
WHERE organization_id =p_org_id
AND released_status = 'S'
AND customer_id = p_cust_id
AND NOT EXISTS
(SELECT delivery_detail_id
FROM wsh_delivery_assignments
WHERE delivery_detail_id = ws.delivery_detail_id
AND delivery_id IS NOT NULL)
ORDER BY customer_id;
FUNCTION set_context( i_user_name IN VARCHAR2,i_resp_name IN VARCHAR2,
i_org_id IN NUMBER)
RETURN VARCHAR2
IS
v_user_id NUMBER;
v_resp_id NUMBER;
v_resp_appl_id NUMBER;
v_lang VARCHAR2(100);
v_session_lang VARCHAR2(100):=fnd_global.current_language;
v_return VARCHAR2(10):='T';
v_nls_lang VARCHAR2(100);
v_org_id NUMBER:=i_org_id;
/* Cursor to get the user id information based on the input user name */
CURSOR cur_user IS
SELECT user_id
FROM fnd_user
WHERE user_name = 'HLIFE';
/* Cursor to get the responsibility information */
CURSOR cur_resp IS
SELECT responsibility_id
,application_id
,language
FROM fnd_responsibility_tl
WHERE responsibility_name = 'India Local Order Management';
/* Cursor to get the nls language information for setting the language context */
CURSOR cur_lang(p_lang_code VARCHAR2)
IS
SELECT nls_language
FROM fnd_languages
WHERE language_code = 'GB';
BEGIN
/* To get the user id details */
OPEN cur_user;
FETCH cur_user INTO v_user_id;
IF cur_user%NOTFOUND
THEN
v_return:='F';
END IF;
CLOSE cur_user;
/* To get the responsibility and responsibility application id */
OPEN cur_resp;
FETCH cur_resp INTO v_resp_id, v_resp_appl_id,v_lang;
IF cur_resp%NOTFOUND
THEN
v_return:='F';
END IF;
CLOSE cur_resp;
DBMS_OUTPUT.PUT_LINE (v_user_id||' ' ||v_resp_id|| ' ' ||v_resp_appl_id);
/* Setting the oracle applications context for the particular session */
fnd_global.apps_initialize ( user_id => v_user_id
,resp_id => v_resp_id
,resp_appl_id => v_resp_appl_id);
/* Setting the org context for the particular session */
mo_global.set_policy_context('S',v_org_id);
/* setting the nls context for the particular session */
IF v_session_lang != v_lang
THEN
OPEN cur_lang(v_lang);
FETCH cur_lang INTO v_nls_lang;
CLOSE cur_lang;
fnd_global.set_nls_context(v_nls_lang);
END IF; --IF v_session_lang != v_lang
RETURN v_return;
EXCEPTION
WHEN OTHERS THEN
RETURN 'F';
END set_context;
BEGIN
DBMS_OUTPUT.PUT_LINE('1');
--1. Set applications context if not already set.
v_context := set_context('1150','50371',93);
IF v_context = 'F'
THEN
DBMS_OUTPUT.PUT_LINE('Error while setting the context');
END IF;
DBMS_OUTPUT.PUT_LINE('2');
MO_GLOBAL.init('ONT');
P_API_VERSION := 1.0;
P_INIT_MSG_LIST := FND_API.G_TRUE;
P_COMMIT := FND_API.G_TRUE;
P_VALIDATION_LEVEL := FND_API.G_VALID_LEVEL_FULL;
-- Initialize the PLSQL table type variable with 'N' number of Deliverie Detail id's
--P_TABOFDELDETS(1) := 14342581;
v_counter :=0;
--P_TABOFDELDETS(1) := 216028;
FOR i IN cur_main
LOOP
v_counter := v_counter + 1;
P_TABOFDELDETS(v_counter) :=i.delivery_detail_id;
P_ACTION := 'UNASSIGN';
P_DELIVERY_ID := i.delivery_id;
P_DELIVERY_NAME := i.name;
END LOOP;
-- P_ACTION := 'UNASSIGN'; -- Provide the proper Action Type
-- P_DELIVERY_ID := 8885454;--null; -- Delivery id from which the delivery detail to be detached
-- P_DELIVERY_NAME := '8885454'; -- respective delivery name
WSH_DELIVERY_DETAILS_PUB.DETAIL_TO_DELIVERY (
P_API_VERSION => P_API_VERSION,
P_INIT_MSG_LIST => P_INIT_MSG_LIST,
P_COMMIT => P_COMMIT,
P_VALIDATION_LEVEL => P_VALIDATION_LEVEL,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA,
P_TABOFDELDETS => P_TABOFDELDETS,
P_ACTION => P_ACTION,
P_DELIVERY_ID => P_DELIVERY_ID,
P_DELIVERY_NAME => P_DELIVERY_NAME
);
IF X_RETURN_STATUS = 'S' THEN
DBMS_OUTPUT.PUT_LINE('Unassigned Sucessfully ');
ELSE
DBMS_OUTPUT.PUT_LINE('Message count ' || X_MSG_COUNT);
IF X_MSG_COUNT = 1 THEN
DBMS_OUTPUT.PUT_LINE('X_msg_data '||X_MSG_DATA);
ELSIF X_MSG_COUNT > 1 THEN
LOOP
P_COUNT := P_COUNT+1;
X_MSG_DATA := FND_MSG_PUB.GET(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
IF X_MSG_DATA IS NULL THEN
EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE('Message' || P_COUNT ||'---'||X_MSG_DATA||' '||
SQLERRM||SQLCODE);
END LOOP;
END IF;
END IF;
BEGIN
v_counter_1 := 0;
FOR r_delivery IN cur_assign
LOOP
v_counter_1 := v_counter_1 + 1;
l_organization_id := r_delivery.organization_id;
x_line_rows (v_counter_1) := r_delivery.delivery_detail_id;
END LOOP;
IF x_line_rows.COUNT > 0
THEN
wsh_delivery_details_pub.autocreate_deliveries (
p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_line_rows => x_line_rows,
x_del_rows => x_del_rows
);
IF (x_return_status <> wsh_util_core.g_ret_sts_success)
THEN
--fnd_file.PUT_LINE (fnd_file.OUTPUT, 'Failure');
DBMS_OUTPUT.put_line ('Failure ' || SQLERRM || SQLCODE);
NULL;
ELSE
COMMIT;
-- fnd_file.PUT_LINE (fnd_file.OUTPUT, 'Success');
DBMS_OUTPUT.put_line ('Sucess ');
DBMS_OUTPUT.put_line (x_del_rows (1));
l_del_rows := x_del_rows (1);
l_del_no := x_del_rows (1);
COMMIT;
/* to call xml output */
BEGIN
l_layout :=
FND_REQUEST.ADD_LAYOUT ('XX',
'XX',
'en',
'US',
'PDF');
IF l_layout
THEN
l_request_id :=
FND_REQUEST.SUBMIT_REQUEST (
'XX',
'XX',
'XX Pick Slip Dealer Picking-Delivery Wise Report',
NULL,
FALSE,
'',
'',
l_del_no,
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
'',
''
);
COMMIT;
requestId := l_request_id;
DBMS_OUTPUT.put_line ('request id ' || requestId);
END IF;
--EXCEPTION
-- WHEN OTHERS
-- THEN
-- --RETCODE := 2;
-- -- ERRBUF := SQLCODE || ':' || SQLERRM;
-- DBMS_OUTPUT.put_line ('erro '||SQLCODE || ':' || SQLERRM);
----fnd_file.put_line(fnd_file.log, sqlcode||':'||sqlerrm);
END;
COMMIT;
--retcode := 0;
END IF;
END IF;
END;
END;
No comments:
Post a Comment