[Home] [Help]
PACKAGE BODY: APPS.CSP_PLANNING_UTILS_PKG
Source
1 PACKAGE BODY CSP_PLANNING_UTILS_PKG AS
2 /* $Header: cspgputb.pls 120.0.12020000.4 2013/03/21 04:00:33 sunarasi noship $ */
3 -- Start of Comments
4 -- Package name :CSP_PLANNING_UTILS_PKG
5 -- Purpose :
6 -- History :
7 -- 01-Mar-13, sunarasi
8 --
9 -- NOTE :
10 -- End of Comments
11 G_PKG_NAME CONSTANT VARCHAR2(30):='CSP_PLANNING_UTILS_PKG';
12 FUNCTION SUBMIT_CONC_REQUEST(p_request Varchar2,p_parameters_id Number) RETURN conc_ref_cursor IS
13 l_req_id Number;
14 l_level_id Varchar2(2000);
15 l_node_value Varchar2(200);
16 l_node_type Varchar2(50);
17 l_org_id Number;
18 l_level Varchar2(200);
19 l_subinv Varchar2(200);
20 l_msg Varchar2(240);
21 the_cursor conc_ref_cursor;
22
23 Cursor c_level_id(p_params_id number) is
24 select level_id
25 from csp_planning_parameters
26 where planning_parameters_id = p_params_id;
27
28 BEGIN
29
30 /*
31 If p_request = 'MIN_MAX' Then
32 If l_node_type = planner.pln_org_node then
33 l_org_id := to_number(name_in('parameter.org_id'));
34 l_level := 'Organization';
35 l_subinv := Null;
36 Elsif l_node_type = planner.pln_subinv_node then
37 l_org_id := to_number(name_in('parameter.org_id'));
38 l_level := 'Subinventory';
39 l_subinv := l_node_value;
40 End if;
41
42 l_req_id := FND_REQUEST.SUBMIT_REQUEST (
43 'INV',
44 'INVISMMX',
45 'Min-max planning report',
46 NULL,
47 FALSE,
48 l_org_id,
49 l_level,
50 chr(0),
51 '','','','','','','',
52 '','','','','','','','','','',
53 '','','','','','','','','','',
54 '','','','','','','','','','',
55 '','','','','','','','','','',
56 '','','','','','','','','','',
57 '','','','','','','','','','',
58 '','','','','','','','','','',
59 '','','','','','','','','','',
60 '','','','','','','','','','');
61
62
63 */
64 open c_level_id(p_parameters_id);
65 Fetch c_level_id into l_level_id;
66 close c_level_id;
67
68 If p_request = 'ASL_RECM' Then
69 l_req_id := FND_REQUEST.SUBMIT_REQUEST (
70 'CSP',
71 'CSPPLPLN',
72 'Generate Recommendations',
73 NULL,
74 FALSE,
75 1.0,
76 l_level_id ,
77 chr(0),
78 '','','','','','','',
79 '','','','','','','','','','',
80 '','','','','','','','','','',
81 '','','','','','','','','','',
82 '','','','','','','','','','',
83 '','','','','','','','','','',
84 '','','','','','','','','','',
85 '','','','','','','','','','',
86 '','','','','','','','','','',
87 '','','','','','','','','','');
88 Elsif p_request = 'FAILURE_RATE' Then
89 l_req_id := FND_REQUEST.SUBMIT_REQUEST (
90 'CSP',
91 'CSPPLFRT',
92 'Calculate Failure Rates',
93 NULL,
94 FALSE,
95 l_level_id,
96 1.0,
97 chr(0),
98 '','','','','','','',
99 '','','','','','','','','','',
100 '','','','','','','','','','',
101 '','','','','','','','','','',
102 '','','','','','','','','','',
103 '','','','','','','','','','',
104 '','','','','','','','','','',
105 '','','','','','','','','','',
106 '','','','','','','','','','',
107 '','','','','','','','','','');
108 Elsif p_request = 'USAGE_ROLLUP' Then
109 l_req_id := FND_REQUEST.SUBMIT_REQUEST (
110 'CSP',
111 'CSPPLURL',
112 'Create Usage Rollup',
113 NULL,
114 FALSE,
115 1.0,
116 chr(0),
117 '',
118 '','','','','','','',
119 '','','','','','','','','','',
120 '','','','','','','','','','',
121 '','','','','','','','','','',
122 '','','','','','','','','','',
123 '','','','','','','','','','',
124 '','','','','','','','','','',
125 '','','','','','','','','','',
126 '','','','','','','','','','',
127 '','','','','','','','','','');
128 Elsif p_request = 'PART_AVAIL' Then
129 l_req_id := FND_REQUEST.SUBMIT_REQUEST (
130 'CSP',
131 'CSPPLSDS',
132 'Create Part Availability Data',
133 NULL,
134 FALSE,
135 1.0,
136 NULL,
137 l_level_id,
138 chr(0),
139 '','','','','','',
140 '','','','','','','','','','',
141 '','','','','','','','','','',
142 '','','','','','','','','','',
143 '','','','','','','','','','',
144 '','','','','','','','','','',
145 '','','','','','','','','','',
146 '','','','','','','','','','',
147 '','','','','','','','','','',
148 '','','','','','','','','','');
149 End If;
150
151 --If l_req_id = 0 Then
152 FND_MESSAGE.RETRIEVE(L_MSG);
153 --End If;
154 open the_cursor FOR
155 select l_req_id,l_msg from DUAL;
156 RETURN the_cursor;
157 END;
158 FUNCTION GET_RUN_FUNCTION_URL(p_function Varchar2) RETURN func_url_cur IS
159 P_FUNCTION_ID NUMBER;
160 P_RESP_APPL_ID NUMBER;
161 P_RESP_ID NUMBER;
162 P_SECURITY_GROUP_ID NUMBER;
163 P_PARAMETERS VARCHAR2(200);
164 P_OVERRIDE_AGENT VARCHAR2(200);
165 P_ORG_ID NUMBER;
166 P_LANG_CODE VARCHAR2(200);
167 P_ENCRYPTPARAMETERS BOOLEAN;
168 v_Return VARCHAR2(200);
169 l_msg Varchar2(240);
170 url_cur func_url_cur;
171 l_function_id Number;
172
173 Cursor c_func_id(p_func_name varchar2) is
174 select function_id from fnd_form_functions
175 where function_name = p_func_name;
176
177 BEGIN
178 P_FUNCTION_ID := 1047063;
179 P_RESP_APPL_ID := 523;
180 P_RESP_ID := 52288;
181 P_SECURITY_GROUP_ID := 0;
182 P_PARAMETERS := 'caller1=' || 'menu';
183 P_OVERRIDE_AGENT := NULL;
184 P_ORG_ID := NULL;
185 P_LANG_CODE := NULL;
186 P_ENCRYPTPARAMETERS := NULL;
187
188 open c_func_id(p_function);
189 Fetch c_func_id into l_function_id;
190 close c_func_id;
191
192 v_Return := FND_RUN_FUNCTION.GET_RUN_FUNCTION_URL(P_FUNCTION_ID => l_function_id,
193 P_RESP_APPL_ID => P_RESP_APPL_ID,
194 P_RESP_ID => P_RESP_ID,
195 P_SECURITY_GROUP_ID => P_SECURITY_GROUP_ID,
196 P_PARAMETERS => P_PARAMETERS,
197 P_OVERRIDE_AGENT => P_OVERRIDE_AGENT,
198 P_ORG_ID => P_ORG_ID,
199 P_LANG_CODE => P_LANG_CODE,
200 P_ENCRYPTPARAMETERS => P_ENCRYPTPARAMETERS);
201 If v_return=null Then
202 FND_MESSAGE.RETRIEVE(l_msg);
203 End If;
204 open url_cur FOR
205 select v_Return,l_msg from DUAL;
206 RETURN url_cur;
207 end;
208 function get_message(app_name varchar2,msg_name varchar2,token1 varchar2,value1 varchar2, token2 varchar2, value2 varchar2, token3 varchar2, value3 varchar2,
209 token4 varchar2, value4 varchar2,token5 varchar2, value5 varchar2) return varchar2 as
210 l_msg varchar2(4000);
211 begin
212 fnd_message.set_name(app_name, msg_name);
213 if token1 <> null then
214 fnd_message.set_token(token1,value1, false);
215 elsif token2 <> null then
216 fnd_message.set_token(token2,value2, false);
217 elsif token3 <> null then
218 fnd_message.set_token(token3,value3, false);
219 elsif token4 <> null then
220 fnd_message.set_token(token4,value4, false);
221 elsif token5 <> null then
222 fnd_message.set_token(token5,value5, false);
223 end if;
224 l_msg := fnd_message.get;
225 return l_msg;
226 END;
227 FUNCTION SET_CONTEXT (P_USER_ID IN VARCHAR2 ,P_RESP_ID IN VARCHAR2,P_RESP_APPL_ID IN VARCHAR2,
228 P_SECURITY_GROUP_ID IN NUMBER) RETURN VARCHAR2
229 IS
230 v_user_id NUMBER;
231 v_resp_id NUMBER;
232 v_resp_appl_id NUMBER;
233 v_lang VARCHAR2(100);
234 v_session_lang VARCHAR2(100):=fnd_global.current_language;
235 v_return VARCHAR2(10):='T';
236 V_NLS_LANG VARCHAR2(100);
237 v_org_id NUMBER;
238 /* Cursor to get the user id information based on the input user name */
239 CURSOR cur_user
240 IS
241 SELECT user_id
242 FROM FND_USER
243 WHERE user_id = p_user_id;
244 /* Cursor to get the responsibility information */
245 CURSOR CUR_RESP
246 IS
247 SELECT responsibility_id
248 ,application_id
249 ,language
250 FROM FND_RESPONSIBILITY_TL
251 WHERE responsibility_id = p_resp_id;
252 /* Cursor to get the nls language information for setting the language context */
253 CURSOR cur_lang(p_lang_code VARCHAR2)
254 IS
255 SELECT nls_language
256 FROM fnd_languages
257 WHERE language_code = p_lang_code;
258 BEGIN
259 /* To get the user id details */
260
261 OPEN cur_user;
262 FETCH cur_user INTO v_user_id;
263 IF cur_user%NOTFOUND
264 THEN
265 v_return:='F';
266
267 END IF; --IF cur_user%NOTFOUND
268 CLOSE CUR_USER;
269
270 /* To get the responsibility and responsibility application id */
271
272 OPEN cur_resp;
273 FETCH cur_resp INTO v_resp_id, v_resp_appl_id,v_lang;
274 IF cur_resp%NOTFOUND
275 THEN
276 v_return:='F';
277
278 END IF; --IF cur_resp%NOTFOUND
279 CLOSE cur_resp;
280
281
282 /* Setting the oracle applications context for the particular session */
283 fnd_global.apps_initialize ( user_id => v_user_id
284 ,resp_id => v_resp_id
285 ,RESP_APPL_ID => V_RESP_APPL_ID
286 ,SECURITY_GROUP_ID => P_SECURITY_GROUP_ID);
287 --,server_id => p_server_id);
288 /* setting the nls context for the particular session */
289 /*
290 FND_GLOBAL.SET_NLS_CONTEXT( P_NLS_LANGUAGE => P_NLS_LANGUAGE
291 ,P_NLS_DATE_FORMAT => P_NLS_DATE_FORMAT
292 ,P_NLS_DATE_LANGUAGE => P_NLS_DATE_LANGUAGE
293 ,P_NLS_NUMERIC_CHARACTERS => P_NLS_NUMERIC_CHARACTERS
294 ,P_NLS_SORT => P_NLS_SORT
295 ,p_nls_territory => p_nls_territory );
296 */
297 /*
298 IF v_session_lang != v_lang
299 THEN
300 OPEN cur_lang(v_lang);
301 FETCH cur_lang INTO v_nls_lang;
302 CLOSE cur_lang;
303 fnd_global.set_nls_context(v_nls_lang);
304 END IF; --IF v_session_lang != v_lang
305 */
306 RETURN v_return;
307 EXCEPTION
308 WHEN OTHERS THEN
309 RETURN 'F';
310 END set_context;
311 END;