[Home] [Help]
PACKAGE BODY: APPS.CSM_EMAIL_QUERY_PKG
Source
1 PACKAGE BODY CSM_EMAIL_QUERY_PKG AS
2 /* $Header: csmeqpb.pls 120.6 2011/02/15 05:16:37 trajasek noship $ */
3
4
5 /*
6 * The function to be called by Process Email Mobile Queries concurrent program
7 */
8
9 -- Purpose: Per-seeded queries and to execute them
10 --
11 -- MODIFICATION HISTORY
12 -- Person Date Comments
13 -- RAVIR 22 April 2010 Created
14 --
15 -- --------- ------------------- ------------------------------------------
16
17 /*** Globals ***/
18 g_object_name CONSTANT VARCHAR2(30) := 'CSM_EMAIL_QUERY_PKG'; -- package name
19 -- g_omfs_resp_id CONSTANT NUMBER := 23675; -- Oracle Mobile Field Service
20 -- g_omfs_appl_id CONSTANT NUMBER := 883; -- Field Service/Palm
21 g_user_id NUMBER;
22 g_user_name VARCHAR2(240);
23 g_time_stamp CONSTANT VARCHAR2(30) := 'HH24:MI:SS';
24 g_date_format_mask VARCHAR2(30);
25
26 /*Function to get RESOURCE_ID for a given user_id */
27 FUNCTION GET_RESOURCE_ID( p_user_id NUMBER)
28 RETURN NUMBER
29 IS
30 CURSOR c_get_resource(p_user_id NUMBER)
31 IS
32 select RESOURCE_ID FROM
33 jtf_rs_resource_extns
34 WHERE USER_ID = p_user_id;
35
36 l_resource_id NUMBER := NULL;
37 BEGIN
38 CSM_UTIL_PKG.LOG('Entering GET_RESOURCE_ID: ' || p_user_id, g_object_name, FND_LOG.LEVEL_PROCEDURE);
39
40 OPEN c_get_resource(p_user_id);
41 FETCH c_get_resource INTO l_resource_id;
42 CLOSE c_get_resource;
43
44 CSM_UTIL_PKG.LOG('Leaving GET_RESOURCE_ID: ' || l_resource_id, g_object_name, FND_LOG.LEVEL_PROCEDURE);
45 RETURN l_resource_id;
46
47 END GET_RESOURCE_ID;
48
49 /*Function to get RESOURCE_ID for a given user_id */
50 FUNCTION GET_ADMIN_GROUPS( p_user_id NUMBER)
51 RETURN VARCHAR2
52 IS
53 CURSOR c_get_groups(p_resource_id NUMBER)
54 IS
55 select gm.GROUP_ID
56 from jtf_rs_role_relations rr,
57 jtf_rs_roles_b rb,
58 jtf_rs_group_members gm
59 WHERE rr.ROLE_RESOURCE_TYPE ='RS_INDIVIDUAL'
60 AND rr.ROLE_RESOURCE_ID = p_resource_id
61 AND rr.ROLE_ID = rb.ROLE_ID
62 AND rb.ROLE_TYPE_CODE ='CSF_THIRD_PARTY'
63 AND rb.ROLE_CODE = 'CSF_THIRD_PARTY_SERVICE_PROVID'
64 AND rr.ROLE_RESOURCE_ID = gm.RESOURCE_ID;
65
66
67 l_resource_id NUMBER := NULL;
68 l_groups VARCHAR2(4000);
69 BEGIN
70 CSM_UTIL_PKG.LOG('Entering GET_ADMIN_GROUPS: ' || p_user_id, g_object_name, FND_LOG.LEVEL_PROCEDURE);
71 l_resource_id := GET_RESOURCE_ID( p_user_id);
72
73 FOR r_get_group in c_get_groups(l_resource_id) LOOP
74 l_groups := l_groups || r_get_group.GROUP_ID || ',';
75 END LOOP;
76
77 l_groups := '(' || SUBSTR(l_groups, 1, LENGTH(l_groups) - 1) || ')';
78
79 IF l_groups = '()' THEN
80 RETURN NULL;
81 ELSE
82 RETURN l_groups;
83 END IF;
84 CSM_UTIL_PKG.LOG('Leaving GET_ADMIN_GROUPS: ' || p_user_id, g_object_name, FND_LOG.LEVEL_PROCEDURE);
85 END GET_ADMIN_GROUPS;
86
87 /*Function to get FND_USER for the email address
88 If there is a unique valid user associated to this email address
89 return: USER_ID else return -1
90 */
91 FUNCTION IS_FND_USER
92 ( p_email_id VARCHAR2)
93 RETURN NUMBER
94 IS
95
96 CURSOR c_fnd_user(p_email_id VARCHAR2) IS
97 SELECT user_id, count(*) over () row_count
98 FROM fnd_user
99 WHERE UPPER(email_address) = p_email_id
100 AND start_date <= sysdate
101 AND(end_date IS NULL OR end_date > sysdate);
102
103 l_fnd_user_id NUMBER;
104 l_count NUMBER;
105
106 BEGIN
107 l_fnd_user_id := -1;
108 CSM_UTIL_PKG.LOG('Entering IS_FND_USER: ' || p_email_id, g_object_name, FND_LOG.LEVEL_PROCEDURE);
109
110 OPEN c_fnd_user(UPPER(p_email_id));
111 FETCH c_fnd_user INTO l_fnd_user_id, l_count;
112 CLOSE c_fnd_user;
113
114 IF l_count > 1 THEN
115 CSM_UTIL_PKG.LOG('EMAIL_ID: ' || p_email_id || ' is associated to more than one users', g_object_name, FND_LOG.LEVEL_PROCEDURE);
116 l_fnd_user_id := -1;
117 ELSE
118 SELECT user_name INTO g_user_name
119 FROM FND_USER
120 WHERE user_id = l_fnd_user_id;
121 END IF;
122
123 CSM_UTIL_PKG.LOG('Leaving IS_FND_USER: ' || l_fnd_user_id, g_object_name, FND_LOG.LEVEL_PROCEDURE);
124 RETURN l_fnd_user_id;
125
126 END IS_FND_USER;
127
128 FUNCTION GET_EMAIL_PREF
129 ( p_email_id VARCHAR2)
130 RETURN VARCHAR2
131
132 IS
133 l_user_id NUMBER;
134 l_email_format VARCHAR2(240);
135 l_fnd_user_name VARCHAR2(240);
136
137 BEGIN
138
139 CSM_UTIL_PKG.LOG('Entering GET_EMAIL_PREF email_id: ' || p_email_id, g_object_name, FND_LOG.LEVEL_PROCEDURE);
140 l_user_id := IS_FND_USER(p_email_id);
141
142 IF l_user_id = -1 THEN
143 l_email_format := 'MAILHTM2';
144 CSM_UTIL_PKG.LOG('Not a valid FND_USER will use default: ' || l_email_format, g_object_name, FND_LOG.LEVEL_PROCEDURE);
145 ELSE
146 CSM_UTIL_PKG.LOG('A valid FND_USER name : ' || g_user_name, g_object_name, FND_LOG.LEVEL_PROCEDURE);
147 l_email_format := fnd_preference.get(g_user_name,'WF','MAILTYPE');
148 END IF;
149
150 CSM_UTIL_PKG.LOG('Entering GET_EMAIL_PREF format: ' || l_email_format, g_object_name, FND_LOG.LEVEL_PROCEDURE);
151 RETURN l_email_format;
152
153 END GET_EMAIL_PREF;
154
155 FUNCTION CHECK_USER_ACCESS
156 ( p_user_id NUMBER,
157 p_level_id NUMBER,
158 p_level_value NUMBER)
159 RETURN VARCHAR2
160 IS
161
162 CURSOR c_user_resp(p_user_id NUMBER, p_resp_id NUMBER) IS
163 SELECT resp.responsibility_id
164 FROM fnd_user usr,
165 fnd_user_resp_groups resp
166 WHERE usr.user_id = p_user_id
167 AND resp.responsibility_id = p_resp_id
168 AND usr.user_id = resp.user_id
169 AND resp.start_date <= sysdate
170 AND(resp.end_date IS NULL OR resp.end_date >= sysdate);
171
172 CURSOR c_mobile_user_resp(p_user_id NUMBER) IS
173 SELECT resp.responsibility_id
174 FROM fnd_user usr,
175 fnd_user_resp_groups resp,
176 asg_responsibility_vl mresp
177 WHERE usr.user_id = p_user_id
178 AND resp.responsibility_id = mresp.responsibility_id
179 AND usr.user_id = resp.user_id
180 AND resp.start_date <= sysdate
181 AND(resp.end_date IS NULL OR resp.end_date >= sysdate);
182
183 l_responsibility_id NUMBER;
184 l_is_user_access VARCHAR2(1);
185
186 BEGIN
187 CSM_UTIL_PKG.LOG('Entering CHECK_USER_ACCESS user_id: ' || p_user_id, g_object_name, FND_LOG.LEVEL_PROCEDURE);
188 l_is_user_access := 'N';
189 l_responsibility_id := -1;
190
191 IF p_level_id = 10003 THEN
192 OPEN c_user_resp(p_user_id, p_level_value);
193 FETCH c_user_resp INTO l_responsibility_id;
194 CLOSE c_user_resp;
195 ELSIF p_level_id = 10001 THEN
196 OPEN c_mobile_user_resp(p_user_id);
197 FETCH c_mobile_user_resp INTO l_responsibility_id;
198 CLOSE c_mobile_user_resp;
199 END IF;
200
201 IF l_responsibility_id > 0 THEN
202 l_is_user_access := 'Y';
203 CSM_UTIL_PKG.LOG('USER_ID: ' || p_user_id || ' have access to responsibility_id :' || p_level_value, g_object_name, FND_LOG.LEVEL_PROCEDURE);
204 ELSE
205 CSM_UTIL_PKG.LOG('USER_ID: ' || p_user_id || ' does not have access to responsibility_id: ' || p_level_value, g_object_name, FND_LOG.LEVEL_PROCEDURE);
206 END IF;
207
208 CSM_UTIL_PKG.LOG('Leaving CHECK_USER_ACCESS: ' || l_is_user_access, g_object_name, FND_LOG.LEVEL_PROCEDURE);
209
210 RETURN l_is_user_access;
211
212 END CHECK_USER_ACCESS;
213
214 FUNCTION CHECK_TASK_ACCESS
215 ( p_user_id NUMBER,
216 p_task_number VARCHAR2)
217 RETURN VARCHAR2
218 IS
219
220 CURSOR c_task_assignment( p_resource_id NUMBER, p_task_number VARCHAR2)
221 IS
222 SELECT count(*) over () row_count
223 FROM jtf_task_assignments jta,
224 jtf_tasks_b jtb
225 WHERE jtb.task_number = p_task_number
226 AND jtb.task_id = jta.task_id
227 AND jta.resource_id = p_resource_id;
228
229 CURSOR c_grp_task_assignment( p_resource_id NUMBER, p_task_number VARCHAR2)
230 IS
231 select 1 from
232 jtf_rs_role_relations rr,
233 jtf_rs_roles_b rb,
234 jtf_rs_group_members gm
235 WHERE rr.ROLE_RESOURCE_TYPE ='RS_INDIVIDUAL'
236 AND rr.ROLE_RESOURCE_ID = p_resource_id
237 AND rr.ROLE_ID = rb.ROLE_ID
238 AND rb.ROLE_TYPE_CODE ='CSF_THIRD_PARTY'
239 --AND rb.ROLE_CODE = 'CSF_THIRD_PARTY_SERVICE_PROVID'
240 AND rb.ROLE_CODE = 'CSF_THIRD_PARTY_ADMINISTRATOR'
241 AND rr.ROLE_RESOURCE_ID = gm.RESOURCE_ID
242 AND gm.GROUP_ID IN (SELECT jta.resource_id
243 FROM jtf_task_assignments jta,
244 jtf_tasks_b jtb
245 WHERE jtb.task_number = p_task_number
246 AND jtb.task_id = jta.task_id
247 AND jta.resource_type_code ='RS_GROUP');
248
249 CURSOR c_tech_task_assignment( p_resource_id NUMBER, p_task_number VARCHAR2)
250 IS
251 SELECT count(*) over () row_count
252 from JTF_TASK_ASSIGNMENTS JTA,
253 JTF_TASKS_B JTB,
254 JTF_RS_GROUP_MEMBERS jtm
255 WHERE jtb.task_number = p_task_number
256 and JTB.TASK_ID = JTA.TASK_ID
257 and JTA.RESOURCE_ID = JTM.RESOURCE_ID
258 and JTM.DELETE_FLAG = 'N'
259 AND jtm.GROUP_ID IN( select gm.GROUP_ID
260 from jtf_rs_role_relations rr,
261 jtf_rs_roles_b rb,
262 jtf_rs_group_members gm
263 where RR.ROLE_RESOURCE_TYPE ='RS_INDIVIDUAL'
264 and sysdate between NVL(RR.START_DATE_ACTIVE,sysdate) and NVL(RR.END_DATE_ACTIVE,sysdate)
265 and RR.DELETE_FLAG = 'N'
266 and gm.DELETE_FLAG = 'N'
267 AND rr.ROLE_RESOURCE_ID = p_resource_id
268 AND rr.ROLE_ID = rb.ROLE_ID
269 AND rb.ROLE_TYPE_CODE ='CSF_THIRD_PARTY'
270 and RB.ROLE_CODE = 'CSF_THIRD_PARTY_ADMINISTRATOR'
271 and RR.ROLE_RESOURCE_ID = GM.RESOURCE_ID);
272
273
274 l_task_access_flag VARCHAR2(1);
275 l_count NUMBER;
276 l_resource_id NUMBER;
277 BEGIN
278 l_task_access_flag := 'N';
279 l_resource_id := GET_RESOURCE_ID(p_user_id);
280
281 CSM_UTIL_PKG.LOG( 'Entering CHECK_TASK_ACCESS for USER_ID : ' || p_user_id ||' and TASK_NUMBER: ' || p_task_number, g_object_name, FND_LOG.LEVEL_PROCEDURE);
282
283 IF l_resource_id IS NOT NULL THEN
284 OPEN c_task_assignment(l_resource_id, p_task_number);
285 FETCH c_task_assignment INTO l_count;
286 CLOSE c_task_assignment;
287
288 --check if tasks is assigned to Admin group
289 IF l_count > 0 THEN
290 l_task_access_flag := 'Y';
291 ELSE
292 OPEN c_grp_task_assignment(l_resource_id, p_task_number);
293 FETCH c_grp_task_assignment INTO l_count;
294 CLOSE c_grp_task_assignment;
295 IF l_count > 0 THEN
296 l_task_access_flag := 'Y';
297 END IF;
298 end if;
299 --check if assigned to techinicians
300 IF l_task_access_flag = 'N' THEN
301 OPEN c_tech_task_assignment(l_resource_id, p_task_number);
302 FETCH c_tech_task_assignment INTO l_count;
303 CLOSE c_tech_task_assignment;
304 IF l_count > 0 THEN
305 l_task_access_flag := 'Y';
306 END IF;
307 end if;
308
309
310 END IF;--valid resource id
311
312 CSM_UTIL_PKG.LOG( 'Leaving CHECK_TASK_ACCESS for USER_ID : ' || p_user_id ||' and TASK_NUMBER: ' || p_task_number, g_object_name, FND_LOG.LEVEL_PROCEDURE);
313
314 RETURN l_task_access_flag;
315
316 END CHECK_TASK_ACCESS;
317
318 /*Funxtion to convert datetime from sevre timezone to user timezone */
319 FUNCTION ADJUST_TIME
320 ( p_date_time IN DATE
321 ) RETURN DATE
322 IS
323 l_client_tz VARCHAR2(4000);
324 l_server_tz VARCHAR2(4000);
325 l_adj_date DATE;
326 BEGIN
327
328 l_client_tz:= fnd_timezones.get_code(fnd_profile.value_specific('CLIENT_TIMEZONE_ID'));
329 l_server_tz:= fnd_timezones.get_code(fnd_profile.value_specific('SERVER_TIMEZONE_ID'));
330
331 l_adj_date := fnd_timezones_pvt.adjust_datetime(p_date_time,l_server_tz,l_client_tz);
332
333 CSM_UTIL_PKG.LOG( 'Client Timezone : ' || l_client_tz ||
334 ' Server Timezone : ' || l_server_tz ||
335 ' Actual Time : ' || to_char(p_date_time, 'DD-MM-YYYY HH24:MI:SS') ||
336 ' Converted Time : ' || to_char(l_adj_date, 'DD-MM-YYYY HH24:MI:SS'),g_object_name, FND_LOG.LEVEL_PROCEDURE);
337
338 RETURN l_adj_date;
339
340 END ADJUST_TIME;
341
342 PROCEDURE EXECUTE_COMMAND
343 ( p_email_id IN VARCHAR2,
344 p_command_name IN VARCHAR2,
345 p_var_value_lst IN CSM_VARCHAR_LIST,
346 p_instance_id OUT nocopy NUMBER,
347 x_return_status OUT nocopy VARCHAR2,
348 x_error_message OUT nocopy VARCHAR2
349 )
350 AS
351
352 CURSOR c_get_query_id(p_command_name VARCHAR2)
353 IS
354 SELECT QUERY_ID,
355 RESTRICTED_FLAG,
356 DISABLED_FLAG,
357 LEVEL_ID,
358 LEVEL_VALUE
359 FROM CSM_QUERY_B
360 WHERE UPPER(QUERY_NAME) = p_command_name
361 AND NVL(DELETE_FLAG,'N') = 'N';
362
363 CURSOR c_get_all_vars (p_query_id NUMBER)
364 IS
365 SELECT variable_id,
366 variable_value_char,
367 variable_value_date,
368 default_flag
369 FROM CSM_QUERY_VARIABLES_B
370 WHERE QUERY_ID = p_query_id;
371
372 CURSOR c_get_reqd_vars (p_query_id NUMBER)
373 IS
374 SELECT count(*)
375 FROM CSM_QUERY_VARIABLES_B
376 WHERE QUERY_ID = p_query_id
377 AND DEFAULT_FLAG = 'N';
378
379 CURSOR c_get_appl_resp_id
380 is
381 SELECT resp.application_id,
382 resp.responsibility_id
383 FROM fnd_application appl,
384 fnd_responsibility resp
385 WHERE appl.application_short_name = 'CSM'
386 and resp.application_id = appl.application_id
387 AND resp.responsibility_key = 'OMFS_PALM';
388
389 l_command_name VARCHAR2(240);
390 l_query_id NUMBER;
391 l_restricted_flag VARCHAR2(1);
392 l_disabled_flag VARCHAR2(1);
393 l_access_flag VARCHAR2(1);
394 l_level_id NUMBER;
395 l_level_value NUMBER;
396 l_instance_id NUMBER;
397 j NUMBER;
398 l_variable_id_lst CSM_INTEGER_LIST;
399 l_var_value_char_lst CSM_VARCHAR_LIST;
400 l_var_value_date_lst CSM_DATE_LIST;
401 l_var_type_lst CSM_VARCHAR_LIST;
402 l_var_value_lst CSM_VARCHAR_LIST;
403 l_default_flag_lst CSM_VARCHAR_LIST;
404 l_reqd_var_count NUMBER;
405 l_application_id NUMBER;
406 l_resp_id NUMBER;
407 l_old_user_id NUMBER;
408 l_old_resp_id NUMBER;
409 l_old_appl_id NUMBER;
410 BEGIN
411 CSM_UTIL_PKG.LOG( 'Entering EXECUTE_COMMAND for EMAIL_ID : ' || p_email_id ||' and COMMAND_NAME: ' || p_command_name, g_object_name, FND_LOG.LEVEL_PROCEDURE);
412
413 IF p_email_id IS NULL OR p_command_name IS NULL THEN
414 x_return_status := FND_API.G_RET_STS_ERROR;
415 x_error_message := 'Email address : ' || p_email_id || ' or Command Name: ' || p_command_name || 'cannot be blank';
416 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_PROCEDURE);
417 RETURN;
418 END IF;
419
420 l_command_name := UPPER(TRIM(p_command_name));
421
422 OPEN c_get_query_id (l_command_name);
423 FETCH c_get_query_id INTO l_query_id, l_restricted_flag, l_disabled_flag, l_level_id, l_level_value;
424 CLOSE c_get_query_id;
425 IF l_query_id IS NULL THEN
426 x_return_status := FND_API.G_RET_STS_ERROR;
427 x_error_message := 'Error: Invalid Query Name: '|| l_command_name;
428 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_PROCEDURE);
429 RETURN;
430 END IF;
431
432 IF l_disabled_flag = 'Y' THEN
433 x_return_status := FND_API.G_RET_STS_ERROR;
434 x_error_message := 'Query Name: '|| l_command_name || ' is disabled.';
435 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_PROCEDURE);
436 RETURN;
437 END IF;
438
439 IF l_restricted_flag = 'Y' THEN
440 g_user_id := IS_FND_USER(p_email_id);
441 IF g_user_id = -1 THEN
442 x_return_status := FND_API.G_RET_STS_ERROR;
443 x_error_message := 'Invalid FND_USER for EMAIL_ID: ' || p_email_id;
444 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_PROCEDURE);
445 RETURN;
446 END IF;
447
448 CSM_UTIL_PKG.LOG('EMAIL_ID: ' || p_email_id || ' associated to FND_USER_ID: ' || g_user_id, g_object_name, FND_LOG.LEVEL_PROCEDURE);
449
450 l_access_flag := CHECK_USER_ACCESS(g_user_id,l_level_id, l_level_value);
451 IF l_access_flag = 'N' THEN
452 x_return_status := FND_API.G_RET_STS_ERROR;
453 x_error_message := 'User: '|| g_user_name ||' does not have access to execute mobile query: ' || l_command_name;
454 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_PROCEDURE);
455 RETURN;
456 END IF;
457 END IF;
458
459 OPEN c_get_reqd_vars (l_query_id);
460 FETCH c_get_reqd_vars INTO l_reqd_var_count;
461 CLOSE c_get_reqd_vars;
462
463 IF l_reqd_var_count <> p_var_value_lst.count THEN
464 x_return_status := FND_API.G_RET_STS_ERROR;
465 x_error_message := 'Incorrect number of parameters for Query Name: '|| l_command_name
466 || ' Required: '|| l_reqd_var_count
467 || ' Passed: ' || p_var_value_lst.count;
468 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_PROCEDURE);
469 RETURN;
470 END IF;
471
472 l_old_user_id := fnd_global.user_id;
473 l_old_resp_id := fnd_global.resp_id;
474 l_old_appl_id := fnd_global.resp_appl_id;
475
476 OPEN c_get_appl_resp_id;
477 FETCH c_get_appl_resp_id INTO l_application_id, l_resp_id;
478 CLOSE c_get_appl_resp_id;
479
480 IF l_level_id = 10001 THEN
481 CSM_UTIL_PKG.LOG('Set Application Context to user_id : ' || g_user_id || ' resp_id :' || l_resp_id || ' appl_id :' || l_application_id, g_object_name, FND_LOG.LEVEL_PROCEDURE);
482 fnd_global.apps_initialize(g_user_id, l_resp_id, l_application_id);
483 ELSE
484 CSM_UTIL_PKG.LOG('Set Application Context to user_id : ' || g_user_id || ' resp_id :' || l_level_value || ' appl_id :' || l_application_id, g_object_name, FND_LOG.LEVEL_PROCEDURE);
485 fnd_global.apps_initialize(g_user_id, l_level_value, l_application_id);
486 END IF;
487
488 g_date_format_mask := TO_CHAR(fnd_profile.value_specific('ICX_DATE_FORMAT_MASK')) || ' ' ||g_time_stamp;
489
490 OPEN c_get_all_vars (l_query_id);
491 FETCH c_get_all_vars BULK COLLECT INTO l_variable_id_lst, l_var_value_char_lst, l_var_value_date_lst, l_default_flag_lst;
492 CLOSE c_get_all_vars;
493
494 -- Merge Actual parameter passed and Default query parameter
495 -- Assuming no Date parames are passed.
496 IF p_var_value_lst.count = l_variable_id_lst.count THEN
497 l_var_value_lst := p_var_value_lst;
498 ELSE
499 l_var_value_lst := CSM_VARCHAR_LIST();
500 j := 1;
501 FOR i IN 1 .. l_variable_id_lst.count LOOP
502 l_var_value_lst.EXTEND(1);
503 if L_DEFAULT_FLAG_LST(I) = 'Y' then
504 l_var_value_lst(i) := TRIM(l_var_value_char_lst(i));
505 else
506 l_var_value_lst(i) := TRIM(p_var_value_lst(j));
507 j := j + 1;
508 END IF;
509 END LOOP;
510 END IF;
511
512 CSM_QUERY_PKG.INSERT_INSTANCE
513 ( p_USER_ID => g_user_id,
514 p_QUERY_ID => l_query_id,
515 p_INSTANCE_ID => NULL,
516 p_INSTANCE_NAME => NULL,
517 p_VARIABLE_ID => l_variable_id_lst,
518 p_VARIABLE_VALUE_CHAR => l_var_value_lst,
519 p_VARIABLE_VALUE_DATE => l_var_value_date_lst,
520 p_commit => fnd_api.G_TRUE,
521 x_INSTANCE_ID => p_instance_id,
522 x_return_status => x_return_status,
523 x_error_message => x_error_message
524 );
525
526 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
527 x_return_status := FND_API.G_RET_STS_ERROR;
528 x_error_message := 'Error in EXECUTE_COMMAND :'
529 || ' ROOT ERROR: CSM_QUERY_PKG.INSERT_INSTANCE '
530 || ' for QUERY_ID ' || l_query_id || ' Detail: ' || x_error_message;
531 CSM_UTIL_PKG.LOG( x_error_message, g_object_name, FND_LOG.LEVEL_ERROR);
532 RETURN ;
533 END IF;
534
535 CSM_QUERY_PKG.EXECUTE_QUERY(
536 p_USER_ID => g_user_id,
537 p_QUERY_ID => l_query_id,
538 p_INSTANCE_ID => p_instance_id,
539 x_return_status => x_return_status,
540 x_error_message => x_error_message,
541 p_commit => fnd_api.G_TRUE,
542 p_source_module => 'EMAIL');
543
544 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
545 x_return_status := FND_API.G_RET_STS_ERROR;
546 x_error_message := 'Error in EXECUTE_COMMAND :'
547 || ' ROOT ERROR: CSM_QUERY_PKG.EXECUTE_QUERY '
548 || ' for INSTANCE_ID ' || p_instance_id || ' Detail: ' || x_error_message;
549 CSM_UTIL_PKG.LOG( x_error_message, g_object_name, FND_LOG.LEVEL_ERROR);
550 RETURN ;
551 END IF;
552
553 UPDATE csm_query_results_acc
554 SET user_email_id = p_email_id
555 WHERE instance_id = p_instance_id;
556 COMMIT;
557
558 CSM_UTIL_PKG.LOG('Reset Application Context to user_id : ' || l_old_user_id || ' resp_id :' || l_old_resp_id || ' appl_id :' || l_old_appl_id, g_object_name, FND_LOG.LEVEL_PROCEDURE);
559 fnd_global.apps_initialize(l_old_user_id, l_old_resp_id, l_old_appl_id);
560
561 CSM_UTIL_PKG.LOG( 'Leaving EXECUTE_COMMAND for EMAIL_ID : ' || p_email_id ||' and COMMAND_NAME: ' || p_command_name,g_object_name, FND_LOG.LEVEL_PROCEDURE);
562
563 EXCEPTION
564 WHEN others THEN
565 x_return_status := fnd_api.g_ret_sts_error;
566 x_error_message := 'Exception occurred in EXECUTE_COMMAND for Query Id : ' || sqlerrm;
567 CSM_UTIL_PKG.LOG(x_error_message,g_object_name, FND_LOG.LEVEL_EXCEPTION);
568 END EXECUTE_COMMAND;
569
570 /**/
571 PROCEDURE GET_TASKS
572 ( p_email_id IN VARCHAR2,
573 p_result OUT nocopy CLOB,
574 x_return_status OUT nocopy VARCHAR2,
575 x_error_message OUT nocopy VARCHAR2
576 )
577 AS
578
579 CURSOR c_groups(p_user_id NUMBER)
580 IS
581 SELECT gb.group_id,
582 gtl.group_name
583 FROM jtf_rs_resource_extns rs,
584 jtf_rs_groups_b gb,
585 jtf_rs_groups_tl gtl,
586 jtf_rs_group_members gm,
587 jtf_rs_roles_b rb,
588 jtf_rs_role_relations rr_grp,
589 jtf_rs_role_relations rr_res
590 WHERE rs.user_id = p_user_id
591 AND gm.resource_id = rs.resource_id
592 AND gm.delete_flag = 'N'
593 AND gm.group_id = gb.group_id
594 AND gb.start_date_active <= sysdate
595 AND(gb.end_date_active IS NULL OR gb.end_date_active >= sysdate)
596 AND gtl.group_id = gb.group_id
597 AND gtl.LANGUAGE = userenv('LANG')
598 AND rr_grp.role_resource_type = 'RS_GROUP_MEMBER'
599 AND rr_grp.role_resource_id = gm.group_member_id
600 AND rr_grp.delete_flag = 'N'
601 AND rr_grp.role_id = rb.role_id
602 AND rr_grp.start_date_active <= sysdate
603 AND(rr_grp.end_date_active IS NULL OR rr_grp.end_date_active >= sysdate)
604 AND rr_res.role_resource_type = 'RS_INDIVIDUAL'
605 AND rr_res.role_resource_id = gm.resource_id
606 AND rr_res.delete_flag = 'N'
607 AND rr_res.role_id = rb.role_id
608 AND rr_res.start_date_active <= sysdate
609 AND(rr_res.end_date_active IS NULL OR rr_res.end_date_active >= sysdate)
610 AND rb.admin_flag = 'Y';
611
612 CURSOR c_group_members(p_group_id NUMBER)
613 is
614 SELECT fusr.user_id
615 from FND_USER FUSR,
616 JTF_RS_RESOURCE_EXTNS RS,
617 JTF_RS_GROUP_MEMBERS GM
618 where gm.group_id = p_group_id
619 AND gm.delete_flag = 'N'
620 AND rs.resource_id = gm.resource_id
621 AND rs.start_date_active <= sysdate
622 AND(rs.end_date_active IS NULL OR rs.end_date_active >= sysdate)
623 AND fusr.user_id = rs.user_id
624 and FUSR.START_DATE <= sysdate
625 and (FUSR.END_DATE is null or FUSR.END_DATE >= sysdate);
626
627 /* SELECT fusr.user_id
628 from FND_USER FUSR,
629 JTF_RS_RESOURCE_EXTNS RS,
630 jtf_rs_groups_b gb,
631 JTF_RS_GROUP_MEMBERS GM
632 jtf_rs_roles_b rb,
633 jtf_rs_role_relations rr_grp,
634 jtf_rs_role_relations rr_res
635 where AND gm.group_id = p_group_id
636 gb.group_id = p_group_id
637 AND gb.start_date_active <= sysdate
638 AND(gb.end_date_active IS NULL OR gb.end_date_active >= sysdate)
639 AND gm.group_id = gb.group_id
640 AND gm.delete_flag = 'N'
641 AND rs.resource_id = gm.resource_id
642 AND rs.start_date_active <= sysdate
643 AND(rs.end_date_active IS NULL OR rs.end_date_active >= sysdate)
644 AND fusr.user_id = rs.user_id
645 and FUSR.START_DATE <= sysdate
646 and(FUSR.END_DATE is null or FUSR.END_DATE >= sysdate);
647 AND rr_grp.role_resource_id = gm.group_member_id
648 AND rr_grp.delete_flag = 'N'
649 AND rr_grp.role_resource_type = 'RS_GROUP_MEMBER'
650 AND rr_grp.start_date_active <= sysdate
651 AND(rr_grp.end_date_active IS NULL OR rr_grp.end_date_active >= sysdate)
652 AND rr_grp.role_id = rb.role_id
653 AND rr_res.role_resource_id = gm.resource_id
654 AND rr_res.role_resource_type = 'RS_INDIVIDUAL'
655 AND rr_res.delete_flag = 'N'
656 AND rr_res.start_date_active <= sysdate
657 and(RR_RES.END_DATE_ACTIVE is null or RR_RES.END_DATE_ACTIVE >= sysdate)
658 AND rr_res.role_id = rb.role_id;*/
659
660 --Check if a given group has third party provider role assigned
661 CURSOR c_third_party_group (p_group_id NUMBER)
662 IS
663 select 1 from
664 jtf_rs_role_relations rr,
665 jtf_rs_roles_b rb
666 WHERE rr.ROLE_RESOURCE_TYPE ='RS_GROUP'
667 AND rr.ROLE_RESOURCE_ID = p_group_id
668 AND rr.ROLE_ID = rb.ROLE_ID
669 AND rb.ROLE_TYPE_CODE ='CSF_THIRD_PARTY'
670 AND rb.ROLE_CODE = 'CSF_THIRD_PARTY_SERVICE_PROVID';
671 --AND rb.ROLE_CODE = 'CSF_THIRD_PARTY_ADMINISTRATOR';
672
673
674 l_query_text VARCHAR2(32767);
675 qrycontext DBMS_XMLGEN.ctxHandle;
676 l_user_id NUMBER;
677 l_group_id NUMBER;
678 r_groups c_groups%ROWTYPE;
679 r_group_members c_group_members%ROWTYPE;
680 l_user_lst VARCHAR2(32767);
681 l_str_length NUMBER;
682 i NUMBER;
683 l_is_sender_admin BOOLEAN;
684 l_is_user_member BOOLEAN;
685 l_group_lst VARCHAR2(32767);
686 l_is_3rdparty NUMBER := 0;
687 BEGIN
688
689 CSM_UTIL_PKG.LOG('Entering GET_TASKS for EMAIL_ID: ' || p_email_id, g_object_name, FND_LOG.LEVEL_PROCEDURE);
690
691 l_user_id := IS_FND_USER(p_email_id);
692
693 IF l_user_id = -1 THEN
694 x_return_status := FND_API.G_RET_STS_ERROR;
695 x_error_message := 'Invalid user for EMAIL_ID: ' || p_email_id;
696 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_PROCEDURE);
697 RETURN;
698 END IF;
699
700 -- g_user_id is Sender
701 -- l_user_id is User whose task to be queried.
702 -- No email param: A-A, M-M
703 -- Has Email param: A-A, M-M, A-A1, M-M1, A-M1, M-A1
704 l_user_lst := '(';
705 IF l_user_id = g_user_id THEN
706 -- Default, Same user, either no email mentioned or sender's email mentioned.
707 -- No email param: A-A, M-M
708 -- Has Email param: A-A, M-M
709 i := 0;
710 FOR r_groups IN c_groups(g_user_id) LOOP
711 -- A-A: Sender is Group Administrator
712 CSM_UTIL_PKG.LOG('Default: Sender is a Group Administrator in group: ' || r_groups.group_name, g_object_name, FND_LOG.LEVEL_PROCEDURE);
713 --check if the Group has third party role to it
714 OPEN c_third_party_group (r_groups.group_id);
715 FETCH c_third_party_group INTO l_is_3rdparty;
716 CLOSE c_third_party_group;
717
718 IF l_is_3rdparty = 1 THEN
719 l_group_lst := l_group_lst || r_groups.group_id || ',';
720 l_is_3rdparty := 0;
721 END IF;
722
723 --GEt all the Group members
724 FOR r_group_members IN c_group_members(r_groups.group_id) LOOP
725 l_user_lst := l_user_lst || r_group_members.user_id || ',';
726 i := i + 1;
727 END LOOP;
728 END LOOP;
729
730 IF i = 0 THEN
731 -- M-M: Sender is Group Member or didnot found any member
732 l_user_lst := l_user_lst || g_user_id || ',';
733 i := i + 1;
734 END IF;
735 ELSE
736 -- Different email is mentioned
737 -- Has Email param: M-M1, A-M1, M-A1, A-A1
738 OPEN c_groups(g_user_id);
739 FETCH c_groups INTO r_groups;
740 CLOSE c_groups;
741
742 IF r_groups.group_id IS NULL THEN
743 l_is_sender_admin := FALSE;
744 ELSE
745 l_is_sender_admin := TRUE;
746 END IF;
747
748 r_groups := NULL;
749
750 OPEN c_groups(l_user_id);
751 FETCH c_groups INTO r_groups;
752 CLOSE c_groups;
753
754 IF r_groups.group_id IS NULL THEN
755 l_is_user_member := TRUE;
756 ELSE
757 l_is_user_member := FALSE;
758 END IF;
759
760 IF l_is_user_member THEN
761 -- M-M1 or A-M1
762 IF l_is_sender_admin THEN
763 --A-M1
764 i := 0;
765 FOR r_groups IN c_groups(g_user_id) LOOP
766 -- A-M1; If member M1 belongs to same group of A, add to user list
767 CSM_UTIL_PKG.LOG('Has Email ID: Sender is a Group Administrator in group: ' || r_groups.group_name, g_object_name, FND_LOG.LEVEL_PROCEDURE);
768 --check if the Group has third party role to it
769 OPEN c_third_party_group (r_groups.group_id);
770 FETCH c_third_party_group INTO l_is_3rdparty;
771 CLOSE c_third_party_group;
772
773 IF l_is_3rdparty = 1 THEN
774 l_group_lst := l_group_lst || r_groups.group_id || ',';
775 l_is_3rdparty := 0;
776 END IF;
777
778 FOR r_group_members IN c_group_members(r_groups.group_id) LOOP
779 IF l_user_id = r_group_members.user_id THEN
780 CSM_UTIL_PKG.LOG('Has Email ID: Sender and User belong to same group: ' || r_groups.group_name, g_object_name, FND_LOG.LEVEL_PROCEDURE);
781 l_user_lst := l_user_lst || r_group_members.user_id || ',';
782 i := i + 1;
783 END IF;
784 END LOOP;
785 END LOOP;
786 IF i=0 THEN
787 x_return_status := FND_API.G_RET_STS_ERROR;
788 x_error_message := 'User: '|| g_user_name ||' doesnot belong to same group.';
789 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_PROCEDURE);
790 RETURN;
791 END IF;
792 ELSE
793 -- M-M1
794 x_return_status := FND_API.G_RET_STS_ERROR;
795 x_error_message := 'A group member cannot query for another group member.';
796 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_PROCEDURE);
797 RETURN;
798 END IF;
799 ELSE
800 --M-A1 or A-A1
801 IF l_is_sender_admin THEN
802 x_return_status := FND_API.G_RET_STS_ERROR;
803 x_error_message := 'Group administrator cannot query for another group administator';
804 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_PROCEDURE);
805 RETURN;
806 ELSE
807 x_return_status := FND_API.G_RET_STS_ERROR;
808 x_error_message := 'Group member cannot query for group administator.';
809 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_PROCEDURE);
810 RETURN;
811 END IF;
812 END IF;
813 END IF;
814
815 l_str_length := LENGTH(l_user_lst);
816 l_user_lst := SUBSTR(l_user_lst, 1, l_str_length - 1) || ')';
817
818 CSM_UTIL_PKG.LOG('Query for task for Users: ' || i, g_object_name, FND_LOG.LEVEL_PROCEDURE);
819
820 l_query_text := ' SELECT cia.incident_number service_request,
821 hzp.party_name customer,
822 res.source_name assignee,
823 ct.task_number task_number,
824 ctl.task_name as subject,
825 jtstl.name task_status,
826 to_char(CSM_EMAIL_QUERY_PKG.ADJUST_TIME(ct.scheduled_start_date), '''|| g_date_format_mask ||''') scheduled_start_date,
827 to_char(CSM_EMAIL_QUERY_PKG.ADJUST_TIME(ct.scheduled_end_date), '''|| g_date_format_mask ||''') scheduled_end_date
828 FROM jtf_rs_resource_extns res,
829 jtf_task_assignments a,
830 jtf_tasks_b ct,
831 jtf_tasks_tl ctl,
832 cs_incidents_all_b cia,
833 hz_parties hzp,
834 jtf_task_statuses_b jts,
835 jtf_task_statuses_tl jtstl
836 WHERE res.user_id IN '|| l_user_lst || '
837 AND a.resource_id = res.resource_id
838 AND a.resource_type_code = ''RS_EMPLOYEE''
839 AND ct.task_id = a.task_id
840 AND ct.open_flag = ''Y''
841 AND ct.source_object_type_code = ''SR''
842 AND ct.scheduled_start_date IS NOT NULL
843 AND ct.scheduled_end_date IS NOT NULL
844 AND ct.source_object_id = cia.incident_id
845 AND cia.customer_id = hzp.party_id
846 AND ctl.task_id = ct.task_id
847 AND ct.task_status_id = jts.task_status_id
848 AND ctl.LANGUAGE = USERENV(''LANG'')
849 AND jts.assigned_flag = ''Y''
850 AND nvl(jts.COMPLETED_FLAG,''N'') = ''N''
851 AND nvl(jts.CANCELLED_FLAG,''N'') = ''N''
852 AND nvl(jts.CLOSED_FLAG,''N'') = ''N''
853 AND nvl(jts.REJECTED_FLAG,''N'') = ''N''
854 AND jtstl.task_status_id = jts.task_status_id
855 AND jtstl.LANGUAGE = USERENV(''LANG'') ';
856
857 IF l_group_lst IS NOT NULL THEN
858
859 CSM_UTIL_PKG.LOG('Query for task for group: ' || l_group_lst, g_object_name, FND_LOG.LEVEL_PROCEDURE);
860
861 l_group_lst := '(' || SUBSTR(l_group_lst, 1, LENGTH(l_group_lst) - 1) || ')';
862
863 l_query_text := l_query_text || ' UNION ALL SELECT cia.incident_number service_request,
864 hzp.party_name customer,
865 gtl.GROUP_NAME assignee,
866 ct.task_number task_number,
867 ctl.task_name as subject,
868 jtstl.name task_status,
869 to_char(CSM_EMAIL_QUERY_PKG.ADJUST_TIME(ct.scheduled_start_date), '''|| g_date_format_mask ||''') scheduled_start_date,
870 to_char(CSM_EMAIL_QUERY_PKG.ADJUST_TIME(ct.scheduled_end_date), '''|| g_date_format_mask ||''') scheduled_end_date
871 FROM jtf_task_assignments a,
872 jtf_tasks_b ct,
873 jtf_tasks_tl ctl,
874 cs_incidents_all_b cia,
875 hz_parties hzp,
876 jtf_task_statuses_b jts,
877 jtf_task_statuses_tl jtstl,
878 jtf_rs_groups_tl gtl
879 WHERE a.resource_type_code = ''RS_GROUP''
880 AND a.resource_id IN ' || l_group_lst ||'
881 AND ct.task_id = a.task_id
882 AND ct.open_flag = ''Y''
883 AND ct.source_object_type_code = ''SR''
884 AND ct.scheduled_start_date IS NOT NULL
885 AND ct.scheduled_end_date IS NOT NULL
886 AND ct.source_object_id = cia.incident_id
887 AND cia.customer_id = hzp.party_id
888 AND ctl.task_id = ct.task_id
889 AND ct.task_status_id = jts.task_status_id
890 AND ctl.LANGUAGE = USERENV(''LANG'')
891 AND jts.assigned_flag = ''Y''
892 AND nvl(jts.COMPLETED_FLAG,''N'') = ''N''
893 AND nvl(jts.CANCELLED_FLAG,''N'') = ''N''
894 AND nvl(jts.CLOSED_FLAG,''N'') = ''N''
895 AND nvl(jts.REJECTED_FLAG,''N'') = ''N''
896 AND jtstl.task_status_id = jts.task_status_id
897 AND jtstl.LANGUAGE = USERENV(''LANG'')
898 AND a.RESOURCE_ID = gtl.GROUP_ID
899 AND gtl.LANGUAGE = USERENV(''LANG'')
900 ORDER BY task_number ';
901 END IF;
902
903
904 qrycontext := DBMS_XMLGEN.newcontext(l_query_text) ;
905 DBMS_XMLGEN.setnullhandling (qrycontext, DBMS_XMLGEN.empty_tag);
906 p_result := DBMS_XMLGEN.getxml (qrycontext);
907
908 CSM_UTIL_PKG.LOG('Leaving GET_TASKS for EMAIL Id: ' || p_email_id, g_object_name, FND_LOG.LEVEL_PROCEDURE);
909
910 EXCEPTION
911 WHEN OTHERS THEN
912 x_return_status := FND_API.G_RET_STS_ERROR;
913 x_error_message := 'Exception occurred in GET_TASKS: ' || sqlerrm;
914 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_EXCEPTION);
915
916 END GET_TASKS;
917
918 PROCEDURE UPDATE_TASK
919 ( p_task_number IN VARCHAR2,
920 p_task_status_id IN VARCHAR2,
921 p_result OUT nocopy CLOB,
922 x_return_status OUT nocopy VARCHAR2,
923 x_error_message OUT nocopy VARCHAR2
924 )
925 AS
926
927 CURSOR c_task_assignments(p_task_number VARCHAR2, p_resource_id NUMBER)
928 IS
929 SELECT jta.task_id,
930 jta.object_version_number,
931 jta.last_update_date,
932 jta.last_updated_by,
933 jta.task_assignment_id,
934 jta.assignment_status_id
935 FROM jtf_task_assignments jta,
936 jtf_tasks_b jtb
937 WHERE jtb.task_number = p_task_number
938 AND jtb.task_id = jta.task_id
939 AND jta.resource_id = p_resource_id
940 UNION ALL
941 SELECT jta.task_id,
942 jta.object_version_number,
943 jta.last_update_date,
944 jta.last_updated_by,
945 jta.task_assignment_id,
946 jta.assignment_status_id
947 FROM jtf_task_assignments jta,
948 jtf_tasks_b jtb
949 WHERE jtb.task_number = p_task_number
950 AND jtb.task_id = jta.task_id
951 AND jta.resource_type_code ='RS_GROUP'
952 AND jta.resource_id IN ( SELECT gm.GROUP_ID from
953 jtf_rs_role_relations rr,
954 jtf_rs_roles_b rb,
955 jtf_rs_group_members gm
956 WHERE rr.ROLE_RESOURCE_TYPE ='RS_INDIVIDUAL'
957 AND rr.ROLE_RESOURCE_ID = p_resource_id
958 AND rr.ROLE_ID = rb.ROLE_ID
959 AND rb.ROLE_TYPE_CODE ='CSF_THIRD_PARTY'
960 --AND rb.ROLE_CODE = 'CSF_THIRD_PARTY_SERVICE_PROVID'
961 AND rb.ROLE_CODE = 'CSF_THIRD_PARTY_ADMINISTRATOR'
962 AND rr.ROLE_RESOURCE_ID = gm.RESOURCE_ID);
963
964 r_task_assignments c_task_assignments%ROWTYPE;
965
966 l_task_access_flag VARCHAR2(1);
967 l_task_assignment_id NUMBER;
968 l_assignment_status_id NUMBER;
969
970 -- Declare OUT parameters
971 l_task_object_version_number NUMBER;
972 l_msg_count NUMBER;
973 l_msg_data VARCHAR2(4000);
974 l_task_status_id NUMBER;
975 l_task_status_name VARCHAR2(240);
976 l_task_type_id NUMBER;
977 l_resource_id NUMBER;
978
979 BEGIN
980 CSM_UTIL_PKG.LOG('Entering UPDATE_TASK for TASK_NUMBER: ' || p_task_number || ' STATUS_ID is : ' || p_task_status_id, g_object_name, FND_LOG.LEVEL_PROCEDURE);
981 l_resource_id := GET_RESOURCE_ID(g_user_id);
982 l_task_access_flag := CHECK_TASK_ACCESS(g_user_id, p_task_number);
983
984 IF l_task_access_flag = 'N' THEN
985 x_return_status := FND_API.G_RET_STS_ERROR;
986 x_error_message := 'Task Number: ' || p_task_number || ' has no assignment for user: ' || g_user_name;
987 CSM_UTIL_PKG.LOG( x_error_message, g_object_name, FND_LOG.LEVEL_PROCEDURE);
988 RETURN;
989 END IF;
990
991 FOR r_task_assignments IN c_task_assignments(p_task_number, l_resource_id) LOOP
992
993 csf_task_assignments_pub.update_assignment_status
994 ( p_api_version => 1.0
995 , p_init_msg_list => FND_API.G_TRUE
996 , p_commit => FND_API.G_TRUE
997 -- Bug 101406041 , p_validation_level => FND_API.G_VALID_LEVEL_NONE
998 -- Validate task status transitions.
999 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1000 , x_return_status => x_return_status
1001 , x_msg_count => l_msg_count
1002 , x_msg_data => l_msg_data
1003 , p_task_assignment_id => r_task_assignments.task_assignment_id
1004 , p_assignment_status_id => p_task_status_id
1005 , p_object_version_number => r_task_assignments.object_version_number
1006 , p_update_task => 'T'
1007 , x_task_object_version_number => l_task_object_version_number
1008 , x_task_status_id => l_task_status_id
1009 );
1010
1011 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1012 x_return_status := FND_API.G_RET_STS_ERROR;
1013 l_msg_data := replace(l_msg_data,fnd_global.local_chr(10), ' ');
1014 x_error_message := 'Error in UPDATE_TASK :'
1015 || ' ROOT ERROR: csf_task_assignments_pub.update_assignment_status'
1016 || ' for PK : ' || r_task_assignments.task_assignment_id
1017 || ' Details:' || l_msg_data ;
1018 CSM_UTIL_PKG.LOG( x_error_message, g_object_name, FND_LOG.LEVEL_ERROR);
1019 RETURN ;
1020 END IF;
1021 END LOOP;
1022
1023 p_result := TO_CLOB('<NODATA></NODATA>');
1024
1025 CSM_UTIL_PKG.LOG('Leaving UPDATE_TASK for TASK_NUMBER: ' || p_task_number, g_object_name, FND_LOG.LEVEL_PROCEDURE);
1026
1027 EXCEPTION
1028 WHEN OTHERS THEN
1029 x_return_status := FND_API.G_RET_STS_ERROR;
1030 x_error_message := 'Exception occurred in UPDATE_TASK: ' || sqlerrm;
1031 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_EXCEPTION);
1032
1033 END UPDATE_TASK;
1034
1035 PROCEDURE ACCEPT_TASK
1036 ( p_task_number IN VARCHAR2,
1037 p_result OUT nocopy CLOB,
1038 x_return_status OUT nocopy VARCHAR2,
1039 x_error_message OUT nocopy VARCHAR2
1040 )
1041 AS
1042 l_profile_value NUMBER;
1043 BEGIN
1044
1045 CSM_UTIL_PKG.LOG('Entering ACCEPT_TASK for TASK_NUMBER: ' || p_task_number, g_object_name, FND_LOG.LEVEL_PROCEDURE);
1046 l_profile_value := TO_NUMBER(fnd_profile.value('CSF_DEFAULT_ACCEPTED_STATUS'));
1047
1048 IF l_profile_value IS NULL THEN
1049 x_return_status := FND_API.G_RET_STS_ERROR;
1050 x_error_message := 'Please set the Profile option CSF_DEFAULT_ACCEPTED_STATUS value.';
1051 CSM_UTIL_PKG.LOG( x_error_message, g_object_name, FND_LOG.LEVEL_ERROR);
1052 RETURN ;
1053 END IF;
1054
1055 UPDATE_TASK
1056 ( p_task_number => p_task_number,
1057 p_task_status_id => l_profile_value,
1058 p_result => p_result,
1059 x_return_status => x_return_status,
1060 x_error_message => x_error_message);
1061
1062 CSM_UTIL_PKG.LOG('Leaving ACCEPT_TASK for TASK_NUMBER: ' || p_task_number, g_object_name, FND_LOG.LEVEL_PROCEDURE);
1063
1064 EXCEPTION
1065 WHEN OTHERS THEN
1066 x_return_status := FND_API.G_RET_STS_ERROR;
1067 x_error_message := 'Exception occurred in ACCEPT_TASK: ' || sqlerrm;
1068 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_EXCEPTION);
1069
1070 END ACCEPT_TASK;
1071
1072 PROCEDURE CANCEL_TASK
1073 ( p_task_number IN VARCHAR2,
1074 p_result OUT nocopy CLOB,
1075 x_return_status OUT nocopy VARCHAR2,
1076 x_error_message OUT nocopy VARCHAR2
1077 )
1078 AS
1079 l_profile_value NUMBER;
1080 BEGIN
1081
1082 CSM_UTIL_PKG.LOG('Entering CANCEL_TASK for TASK_NUMBER: ' || p_task_number, g_object_name, FND_LOG.LEVEL_PROCEDURE);
1083 l_profile_value := TO_NUMBER(fnd_profile.value('CSF_DEFAULT_TASK_CANCELLED_STATUS'));
1084
1085 IF l_profile_value IS NULL THEN
1086 x_return_status := FND_API.G_RET_STS_ERROR;
1087 x_error_message := 'Please set the Profile option CSF_DEFAULT_TASK_CANCELLED_STATUS value.';
1088 CSM_UTIL_PKG.LOG( x_error_message, g_object_name, FND_LOG.LEVEL_ERROR);
1089 RETURN ;
1090 END IF;
1091
1092 UPDATE_TASK
1093 ( p_task_number => p_task_number,
1094 p_task_status_id => l_profile_value,
1095 p_result => p_result,
1096 x_return_status => x_return_status,
1097 x_error_message => x_error_message);
1098
1099 CSM_UTIL_PKG.LOG('Leaving CANCEL_TASK for TASK_NUMBER: ' || p_task_number, g_object_name, FND_LOG.LEVEL_PROCEDURE);
1100
1101 EXCEPTION
1102 WHEN OTHERS THEN
1103 x_return_status := FND_API.G_RET_STS_ERROR;
1104 x_error_message := 'Exception occurred in CANCEL_TASK: ' || sqlerrm;
1105 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_EXCEPTION);
1106
1107 END CANCEL_TASK;
1108
1109 PROCEDURE CLOSE_TASK
1110 ( p_task_number IN VARCHAR2,
1111 p_result OUT nocopy CLOB,
1112 x_return_status OUT nocopy VARCHAR2,
1113 x_error_message OUT nocopy VARCHAR2
1114 )
1115 AS
1116 l_profile_value NUMBER;
1117 BEGIN
1118
1119 CSM_UTIL_PKG.LOG('Entering CLOSE_TASK for TASK_NUMBER: ' || p_task_number, g_object_name, FND_LOG.LEVEL_PROCEDURE);
1120
1121 l_profile_value := TO_NUMBER(fnd_profile.value('CSF_DFLT_AUTO_CLOSE_TASK_STATUS'));
1122
1123 IF l_profile_value IS NULL THEN
1124 x_return_status := FND_API.G_RET_STS_ERROR;
1125 x_error_message := 'Please set the Profile option CSF_DFLT_AUTO_CLOSE_TASK_STATUS value.';
1126 CSM_UTIL_PKG.LOG( x_error_message, g_object_name, FND_LOG.LEVEL_ERROR);
1127 RETURN ;
1128 END IF;
1129
1130 UPDATE_TASK
1131 ( p_task_number => p_task_number,
1132 p_task_status_id => l_profile_value,
1133 p_result => p_result,
1134 x_return_status => x_return_status,
1135 x_error_message => x_error_message);
1136
1137 CSM_UTIL_PKG.LOG('Leaving CLOSE_TASK for TASK_NUMBER: ' || p_task_number, g_object_name, FND_LOG.LEVEL_PROCEDURE);
1138
1139 EXCEPTION
1140 WHEN OTHERS THEN
1141 x_return_status := FND_API.G_RET_STS_ERROR;
1142 x_error_message := 'Exception occurred in CLOSE_TASK: ' || sqlerrm;
1143 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_EXCEPTION);
1144
1145 END CLOSE_TASK;
1146
1147 /*Procedure to update task statu to Travelling*/
1148 PROCEDURE TRAVELING_TASK
1149 ( p_task_number IN VARCHAR2,
1150 p_result OUT nocopy CLOB,
1151 x_return_status OUT nocopy VARCHAR2,
1152 x_error_message OUT nocopy VARCHAR2
1153 )
1154 AS
1155
1156 l_profile_value NUMBER;
1157
1158 BEGIN
1159 CSM_UTIL_PKG.LOG('Entering TRAVELING_TASK for TASK_NUMBER: ' || p_task_number, g_object_name, FND_LOG.LEVEL_PROCEDURE);
1160
1161 l_profile_value := TO_NUMBER(fnd_profile.value('CSF_DEFAULT_TRAVELING_STATUS'));
1162
1163 IF l_profile_value IS NULL THEN
1164 x_return_status := FND_API.G_RET_STS_ERROR;
1165 x_error_message := 'Please set the Profile option CSF_DEFAULT_TRAVELING_STATUS value.';
1166 CSM_UTIL_PKG.LOG( x_error_message, g_object_name, FND_LOG.LEVEL_ERROR);
1167 RETURN ;
1168 END IF;
1169
1170 UPDATE_TASK
1171 ( p_task_number => p_task_number,
1172 p_task_status_id => l_profile_value,
1173 p_result => p_result,
1174 x_return_status => x_return_status,
1175 x_error_message => x_error_message);
1176
1177 CSM_UTIL_PKG.LOG('Leaving TRAVELING_TASK for TASK_NUMBER: ' || p_task_number, g_object_name, FND_LOG.LEVEL_PROCEDURE);
1178
1179 EXCEPTION
1180 WHEN OTHERS THEN
1181 x_return_status := FND_API.G_RET_STS_ERROR;
1182 x_error_message := 'Exception occurred in TRAVELING_TASK: ' || sqlerrm;
1183 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_EXCEPTION);
1184
1185 END TRAVELING_TASK;
1186
1187 /*Procedure to update task statu to Working*/
1188 PROCEDURE WORKING_TASK
1189 ( p_task_number IN VARCHAR2,
1190 p_result OUT nocopy CLOB,
1191 x_return_status OUT nocopy VARCHAR2,
1192 x_error_message OUT nocopy VARCHAR2
1193 )
1194 AS
1195 l_profile_value NUMBER;
1196 BEGIN
1197 CSM_UTIL_PKG.LOG('Entering WORKING_TASK for TASK_NUMBER: ' || p_task_number, g_object_name, FND_LOG.LEVEL_PROCEDURE);
1198 l_profile_value := TO_NUMBER(fnd_profile.value('CSF_DEFAULT_TASK_WORKING_STATUS'));
1199
1200 IF l_profile_value IS NULL THEN
1201 x_return_status := FND_API.G_RET_STS_ERROR;
1202 x_error_message := 'Please set the Profile option CSF_DEFAULT_TASK_WORKING_STATUS value.';
1203 CSM_UTIL_PKG.LOG( x_error_message, g_object_name, FND_LOG.LEVEL_ERROR);
1204 RETURN ;
1205 END IF;
1206
1207 UPDATE_TASK
1208 ( p_task_number => p_task_number,
1209 p_task_status_id => l_profile_value,
1210 p_result => p_result,
1211 x_return_status => x_return_status,
1212 x_error_message => x_error_message);
1213
1214 CSM_UTIL_PKG.LOG('Leaving WORKING_TASK for TASK_NUMBER: ' || p_task_number, g_object_name, FND_LOG.LEVEL_PROCEDURE);
1215
1216 EXCEPTION
1217 WHEN OTHERS THEN
1218 x_return_status := FND_API.G_RET_STS_ERROR;
1219 x_error_message := 'Exception occurred in WORKING_TASK: ' || sqlerrm;
1220 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_EXCEPTION);
1221
1222 END WORKING_TASK;
1223
1224 /*Procedure to update task statu to Completed*/
1225 PROCEDURE COMPLETED_TASK
1226 ( p_task_number IN VARCHAR2,
1227 p_result OUT nocopy CLOB,
1228 x_return_status OUT nocopy VARCHAR2,
1229 x_error_message OUT nocopy VARCHAR2
1230 )
1231 AS
1232 CURSOR c_task_status
1233 IS
1234 SELECT tsb.task_status_id
1235 FROM jtf_task_statuses_b tsb
1236 WHERE tsb.COMPLETED_FLAG = 'Y'
1237 AND tsb.USAGE ='TASK'
1238 AND tsb.SEEDED_FLAG = 'Y'
1239 AND tsb.CLOSED_FLAG IS NULL
1240 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE,SYSDATE) AND NVL(END_DATE_ACTIVE,SYSDATE);
1241
1242 l_profile_value NUMBER;
1243
1244 BEGIN
1245 CSM_UTIL_PKG.LOG('Entering COMPLETED_TASK for TASK_NUMBER: ' || p_task_number, g_object_name, FND_LOG.LEVEL_PROCEDURE);
1246
1247 l_profile_value := TO_NUMBER(fnd_profile.value('CSF_DEFAULT_COMPLETED_STATUS'));
1248
1249 IF l_profile_value IS NULL THEN
1250 x_return_status := FND_API.G_RET_STS_ERROR;
1251 x_error_message := 'Please set the Profile option CSF_DEFAULT_COMPLETED_STATUS value.';
1252 CSM_UTIL_PKG.LOG( x_error_message, g_object_name, FND_LOG.LEVEL_ERROR);
1253 RETURN ;
1254 END IF;
1255
1256 UPDATE_TASK
1257 ( p_task_number => p_task_number,
1258 p_task_status_id => l_profile_value,
1259 p_result => p_result,
1260 x_return_status => x_return_status,
1261 x_error_message => x_error_message);
1262
1263 CSM_UTIL_PKG.LOG('Leaving COMPLETED_TASK for TASK_NUMBER: ' || p_task_number, g_object_name, FND_LOG.LEVEL_PROCEDURE);
1264
1265 EXCEPTION
1266 WHEN OTHERS THEN
1267 x_return_status := FND_API.G_RET_STS_ERROR;
1268 x_error_message := 'Exception occurred in COMPLETED_TASK: ' || sqlerrm;
1269 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_EXCEPTION);
1270
1271 END COMPLETED_TASK;
1272
1273 PROCEDURE ADD_TASK_NOTE
1274 ( p_task_number IN VARCHAR2,
1275 p_note_text1 IN VARCHAR2,
1276 p_note_text2 IN VARCHAR2,
1277 p_note_visibility IN VARCHAR2,
1278 p_sig_delimiter IN VARCHAR2,
1279 p_result OUT nocopy CLOB,
1280 x_return_status OUT nocopy VARCHAR2,
1281 x_error_message OUT nocopy VARCHAR2
1282 )
1283 AS
1284
1285 CURSOR c_task( p_task_number VARCHAR2, p_user_id NUMBER)
1286 IS
1287 SELECT jtb.task_id
1288 FROM jtf_tasks_b jtb
1289 WHERE jtb.task_number = p_task_number;
1290
1291 CURSOR c_note_status(p_meaning VARCHAR2)
1292 IS
1293 SELECT lookup_code
1294 FROM fnd_lookup_values
1295 WHERE lookup_type = 'JTF_NOTE_STATUS'
1296 AND meaning = p_meaning
1297 AND language = userenv('LANG');
1298
1299 l_task_access_flag VARCHAR2(1);
1300 l_jtf_note_id NUMBER;
1301 l_task_id NUMBER;
1302 l_note_status VARCHAR2(1);
1303 l_msg_count NUMBER;
1304 l_notes VARCHAR2(32767);
1305 l_sig_pos NUMBER;
1306 l_note_type VARCHAR2(30);
1307 BEGIN
1308
1309 CSM_UTIL_PKG.LOG('Entering ADD_TASK_NOTE for TASK_NUMBER: ' || p_task_number, g_object_name, FND_LOG.LEVEL_PROCEDURE);
1310 l_task_access_flag := CHECK_TASK_ACCESS(g_user_id, p_task_number);
1311
1312 IF l_task_access_flag = 'N' THEN
1313 x_return_status := FND_API.G_RET_STS_ERROR;
1314 x_error_message := 'Task Number: ' || p_task_number || ' has no assignment for user: ' || g_user_name;
1315 CSM_UTIL_PKG.LOG( x_error_message, g_object_name, FND_LOG.LEVEL_ERROR);
1316 RETURN ;
1317 END IF;
1318
1319 OPEN c_task(p_task_number,g_user_id);
1320 FETCH c_task INTO l_task_id;
1321 CLOSE c_task;
1322
1323 OPEN c_note_status(p_note_visibility);
1324 FETCH c_note_status INTO l_note_status;
1325 CLOSE c_note_status;
1326
1327 IF l_note_status IS NULL THEN
1328 x_return_status := FND_API.G_RET_STS_ERROR;
1329 x_error_message := 'Command Parameter NOTE_VISIBILITY: ' || p_note_visibility || ' is not a valid Note Status';
1330 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_ERROR);
1331 RETURN ;
1332 END IF;
1333
1334 l_notes := p_note_text1 || p_note_text2;
1335 l_sig_pos := INSTR(l_notes, p_sig_delimiter);
1336
1337 IF l_sig_pos > 0 THEN
1338 l_notes := SUBSTR(l_notes, 1, l_sig_pos - 1);
1339 END IF;
1340
1341 l_note_type := fnd_profile.value('JTF_NTS_NOTE_TYPE');
1342
1343 IF LENGTH(l_notes) > 2000 THEN
1344 jtf_notes_pub.create_note
1345 ( p_api_version => 1.0
1346 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1347 , p_init_msg_list => FND_API.G_TRUE
1348 , p_commit => FND_API.G_TRUE
1349 , x_return_status => x_return_status
1350 , x_msg_count => l_msg_count
1351 , x_msg_data => x_error_message
1352 , p_source_object_id => l_task_id
1353 , p_source_object_code => 'TASK'
1354 , p_notes => SUBSTR(l_notes,1,2000)
1355 , p_notes_detail => l_notes
1356 , p_note_status => l_note_status
1357 , p_entered_by => g_user_id
1358 , p_entered_date => SYSDATE
1359 , p_created_by => g_user_id --NVL(p_record.created_by,FND_GLOBAL.USER_ID) --12.1
1360 , p_creation_date => SYSDATE
1361 , p_last_updated_by => g_user_id --NVL(p_record.last_updated_by,FND_GLOBAL.USER_ID) --12.1
1362 , p_last_update_date => SYSDATE
1363 , p_last_update_login => g_user_id
1364 , p_note_type => l_note_type
1365 , x_jtf_note_id => l_jtf_note_id
1366 );
1367 ELSE
1368 jtf_notes_pub.create_note
1369 ( p_api_version => 1.0
1370 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1371 , p_init_msg_list => FND_API.G_TRUE
1372 , p_commit => FND_API.G_TRUE
1373 , x_return_status => x_return_status
1374 , x_msg_count => l_msg_count
1375 , x_msg_data => x_error_message
1376 , p_source_object_id => l_task_id
1377 , p_source_object_code => 'TASK'
1378 , p_notes => l_notes
1379 , p_notes_detail => NULL
1380 , p_note_status => l_note_status
1381 , p_entered_by => g_user_id
1382 , p_entered_date => SYSDATE
1383 , p_created_by => g_user_id --NVL(p_record.created_by,FND_GLOBAL.USER_ID) --12.1
1384 , p_creation_date => SYSDATE
1385 , p_last_updated_by => g_user_id --NVL(p_record.last_updated_by,FND_GLOBAL.USER_ID) --12.1
1386 , p_last_update_date => SYSDATE
1387 , p_last_update_login => g_user_id
1388 , p_note_type => l_note_type
1389 , x_jtf_note_id => l_jtf_note_id
1390 );
1391 END IF;
1392
1393 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1394 x_return_status := FND_API.G_RET_STS_ERROR;
1395 x_error_message := 'Error in ADD_TASK_NOTE :'
1396 || ' ROOT ERROR: jtf_notes_pub.Create_note'
1397 || ' for PK TASK_NUMBER: ' || p_task_number
1398 || ' Details:' || x_error_message;
1399 CSM_UTIL_PKG.LOG( x_error_message,g_object_name, FND_LOG.LEVEL_ERROR);
1400 RETURN ;
1401 END IF;
1402
1403 p_result := TO_CLOB('<NODATA></NODATA>');
1404 CSM_UTIL_PKG.LOG('Leaving ADD_TASK_NOTE for TASK_NUMBER: ' || p_task_number || ' JTF_NOTE_ID: ' || l_jtf_note_id , g_object_name, FND_LOG.LEVEL_PROCEDURE);
1405
1406 EXCEPTION
1407 WHEN OTHERS THEN
1408 x_return_status := FND_API.G_RET_STS_ERROR;
1409 x_error_message := 'Exception occurred in ADD_TASK_NOTE: ' || sqlerrm;
1410 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_EXCEPTION);
1411
1412 END ADD_TASK_NOTE;
1413
1414 PROCEDURE GET_TASK_DETAILS
1415 ( p_task_number IN VARCHAR2,
1416 p_result OUT nocopy CLOB,
1417 x_return_status OUT nocopy VARCHAR2,
1418 x_error_message OUT nocopy VARCHAR2
1419 )
1420 AS
1421
1422 CURSOR c_task_details(p_task_number VARCHAR2)
1423 IS
1424 SELECT tsk.task_number,
1425 tsk.task_id,
1426 tsktl.task_name,
1427 tsktl.description,
1428 tptl.name priority,
1429 tttl.name type,
1430 tstl.name status,
1431 hzp.party_name customer,
1432 ADJUST_TIME(tsk.scheduled_start_date) scheduled_start_date,
1433 ADJUST_TIME(tsk.scheduled_end_date) scheduled_end_date,
1434 csb.incident_id,
1435 cstl.summary,
1436 csb.problem_code,
1437 hzloc.address1 || decode(hzloc.address1, NULL, '', ',')
1438 || hzloc.address2 || decode(hzloc.address2, NULL, '', ',')
1439 || hzloc.city || decode(hzloc.city, NULL, '', ',')
1440 || hzloc.state || decode(hzloc.state, NULL, '', ',')
1441 || hzloc.postal_code || decode(hzloc.postal_code, NULL, '', ',')
1442 || hzloc.country
1443 AS
1444 address
1445 FROM jtf_tasks_b tsk,
1446 jtf_tasks_tl tsktl,
1447 jtf_task_priorities_b tpb,
1448 jtf_task_priorities_tl tptl,
1449 jtf_task_types_b ttb,
1450 jtf_task_types_tl tttl,
1451 jtf_task_statuses_b ts,
1452 jtf_task_statuses_tl tstl,
1453 cs_incidents_all_b csb,
1454 cs_incidents_all_tl cstl,
1455 hz_parties hzp,
1456 hz_party_sites hzps,
1457 hz_locations hzloc
1458 WHERE tsk.task_number = p_task_number
1459 AND tsktl.task_id = tsk.task_id
1460 AND tsktl.LANGUAGE = userenv('LANG')
1461 AND tpb.task_priority_id(+) = tsk.task_priority_id
1462 AND tptl.task_priority_id = tpb.task_priority_id
1463 AND tptl.LANGUAGE = userenv('LANG')
1464 AND ttb.task_type_id(+) = tsk.task_type_id
1465 AND tttl.task_type_id = ttb.task_type_id
1466 AND tttl.LANGUAGE = userenv('LANG')
1467 AND ts.task_status_id(+) = tsk.task_status_id
1468 AND tstl.task_status_id = ts.task_status_id
1469 AND tstl.LANGUAGE = userenv('LANG')
1470 AND tsk.source_object_type_code = 'SR'
1471 AND csb.incident_id(+) = tsk.source_object_id
1472 AND csb.incident_id = cstl.incident_id
1473 AND cstl.LANGUAGE = userenv('LANG')
1474 AND hzp.party_id(+) = tsk.customer_id
1475 AND hzps.party_site_id = tsk.address_id
1476 AND hzloc.location_id(+) = hzps.location_id;
1477
1478 CURSOR c_task_uom(p_task_id NUMBER)
1479 IS
1480 SELECT tsk.planned_effort,
1481 uom.unit_of_measure
1482 FROM jtf_tasks_b tsk,
1483 mtl_units_of_measure_tl uom
1484 WHERE tsk.task_id = p_task_id
1485 AND uom.uom_code(+) = tsk.planned_effort_uom
1486 AND uom.LANGUAGE = userenv('LANG');
1487
1488 CURSOR c_system_items(p_incident_id NUMBER)
1489 IS
1490 SELECT item.segment1 AS item
1491 FROM cs_incidents_all_b csb,
1492 mtl_system_items_b item
1493 WHERE csb.incident_id = p_incident_id
1494 AND item.inventory_item_id(+) = csb.inventory_item_id
1495 AND item.organization_id = csb.org_id;
1496
1497 CURSOR c_item_instance(p_incident_id NUMBER)
1498 IS
1499 SELECT inst.serial_number
1500 FROM cs_incidents_all_b csb,
1501 csi_item_instances inst
1502 WHERE csb.incident_id = p_incident_id
1503 AND inst.instance_id(+) = csb.customer_product_id
1504 AND inst.inv_master_organization_id = csb.org_id
1505 AND inst.inventory_item_id = csb.inventory_item_id;
1506
1507 CURSOR c_task_notes(p_task_number VARCHAR2)
1508 IS
1509 SELECT nttl.notes note_text,
1510 lkp.meaning note_status,
1511 rs.source_name entered_by,
1512 ADJUST_TIME(ntb.entered_date) entered_date
1513 FROM jtf_tasks_b tsk,
1514 jtf_notes_b ntb,
1515 jtf_notes_tl nttl,
1516 jtf_rs_resource_extns rs,
1517 fnd_lookup_values lkp
1518 WHERE tsk.task_number = p_task_number
1519 AND ntb.source_object_id = tsk.task_id
1520 AND ntb.source_object_code = 'TASK'
1521 AND nttl.jtf_note_id = ntb.jtf_note_id
1522 AND nttl.LANGUAGE = userenv('LANG')
1523 AND rs.user_id = ntb.entered_by
1524 AND lkp.lookup_code = ntb.note_status
1525 AND lkp.lookup_type = 'JTF_NOTE_STATUS'
1526 AND lkp.LANGUAGE = userenv('LANG');
1527
1528 r_task_details c_task_details%ROWTYPE;
1529 r_task_uom c_task_uom%ROWTYPE;
1530 r_task_notes c_task_notes%ROWTYPE;
1531 l_item mtl_system_items_b.segment1%TYPE;
1532 l_serial_number csi_item_instances.serial_number%TYPE;
1533 l_task_access_flag VARCHAR2(1);
1534 l_query_text VARCHAR2(4000);
1535 l_qrycontext DBMS_XMLGEN.ctxHandle;
1536 l_email_format VARCHAR2(240);
1537 l_xml_result LONG;
1538
1539 BEGIN
1540 CSM_UTIL_PKG.LOG('Entering GET_TASK_DETAILS for TASK_NUMBER: ' || p_task_number, g_object_name, FND_LOG.LEVEL_PROCEDURE);
1541
1542 l_task_access_flag := CHECK_TASK_ACCESS(g_user_id, p_task_number);
1543
1544 IF l_task_access_flag = 'N' THEN
1545 x_return_status := FND_API.G_RET_STS_ERROR;
1546 x_error_message := 'Task Number: ' || p_task_number || ' has no assignment for user: ' || g_user_name;
1547 CSM_UTIL_PKG.LOG( x_error_message, g_object_name, FND_LOG.LEVEL_ERROR);
1548 RETURN;
1549 END IF;
1550
1551 OPEN c_task_details(p_task_number);
1552 FETCH c_task_details INTO r_task_details;
1553 CLOSE c_task_details;
1554
1555 l_xml_result := '<?xml version="1.0"?>'
1556 || '<ROWSET>'
1557 || ' <ROW>'
1558 || ' <TASK_NUMBER><![CDATA[' || r_task_details.task_number || ']]></TASK_NUMBER>'
1559 || ' <TASK_NAME><![CDATA[' || r_task_details.task_name || ']]></TASK_NAME>'
1560 || ' <DESCRIPTION><![CDATA[' || r_task_details.description || ']]></DESCRIPTION>'
1561 || ' <PRIORITY><![CDATA[' || r_task_details.priority || ']]></PRIORITY>'
1562 || ' <TYPE><![CDATA[' ||r_task_details.TYPE || ']]></TYPE>'
1563 || ' <STATUS><![CDATA[' ||r_task_details.status || ']]></STATUS>'
1564 || ' <CUSTOMER><![CDATA[' || r_task_details.customer || ']]></CUSTOMER>'
1565 || ' <SCHEDULED_START_DATE> ' || to_char(r_task_details.scheduled_start_date, g_date_format_mask) || '</SCHEDULED_START_DATE>'
1566 || ' <SCHEDULED_END_DATE> ' || to_char(r_task_details.scheduled_end_date, g_date_format_mask) || '</SCHEDULED_END_DATE>'
1567 || ' <SUMMARY><![CDATA[' || r_task_details.SUMMARY || ']]></SUMMARY>'
1568 || ' <PROBLEM_CODE><![CDATA[' || r_task_details.problem_code || ']]></PROBLEM_CODE>'
1569 || ' <ADDRESS><![CDATA[' || r_task_details.address || ']]></ADDRESS>';
1570
1571 OPEN c_task_uom(r_task_details.task_id);
1572 FETCH c_task_uom INTO r_task_uom;
1573 CLOSE c_task_uom;
1574
1575 l_xml_result := l_xml_result
1576 || ' <PLANNED_EFFORT> ' || to_char(r_task_uom.planned_effort) || '</PLANNED_EFFORT>'
1577 || ' <UNIT_OF_MEASURE><![CDATA[' || r_task_uom.unit_of_measure || ']]></UNIT_OF_MEASURE>';
1578
1579 OPEN c_system_items(r_task_details.incident_id);
1580 FETCH c_system_items INTO l_item;
1581 CLOSE c_system_items;
1582
1583 l_xml_result := l_xml_result
1584 || ' <ITEM><![CDATA[' || l_item || ']]></ITEM>';
1585
1586 OPEN c_item_instance(r_task_details.incident_id);
1587 FETCH c_item_instance INTO l_serial_number;
1588 CLOSE c_item_instance;
1589
1590 l_xml_result := l_xml_result
1591 || ' <SERIAL_NUMBER><![CDATA[' || l_serial_number || ']]></SERIAL_NUMBER>';
1592
1593 l_xml_result := l_xml_result || ' </ROW>';
1594
1595 FOR r_task_notes IN c_task_notes(p_task_number) LOOP
1596 l_xml_result := l_xml_result
1597 || ' <ROW> '
1598 || ' <NOTE_TEXT><![CDATA[' || r_task_notes.note_text || ' ]]></NOTE_TEXT>'
1599 || ' <NOTE_STATUS><![CDATA[' || r_task_notes.note_status || ']]></NOTE_STATUS>'
1600 || ' <ENTERED_BY> ' ||to_char( r_task_notes.entered_by) || ' </ENTERED_BY>'
1601 || ' <ENTERED_DATE> ' || to_char(r_task_notes.entered_date, g_date_format_mask) || ' </ENTERED_DATE> '
1602 || ' </ROW> ';
1603 END LOOP;
1604
1605 l_xml_result := l_xml_result || ' </ROWSET>';
1606
1607 p_result := TO_CLOB(l_xml_result);
1608
1609 CSM_UTIL_PKG.LOG('Leaving GET_TASK_DETAILS for TASK_NUMBER: ' || p_task_number, g_object_name, FND_LOG.LEVEL_PROCEDURE);
1610
1611 EXCEPTION
1612 WHEN OTHERS THEN
1613 x_return_status := FND_API.G_RET_STS_ERROR;
1614 x_error_message := 'Exception occurred in GET_TASK_DETAILS: ' || sqlerrm;
1615 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_EXCEPTION);
1616
1617 END GET_TASK_DETAILS;
1618
1619 PROCEDURE GET_SR_DETAILS
1620 ( p_sr_number IN VARCHAR2,
1621 p_result OUT nocopy CLOB,
1622 x_return_status OUT nocopy VARCHAR2,
1623 x_error_message OUT nocopy VARCHAR2
1624 )
1625 AS
1626 CURSOR c_incidents(p_incident_number VARCHAR2, p_resource_id NUMBER)
1627 IS
1628 SELECT sr.incident_id, tsk.TASK_NUMBER
1629 FROM cs_incidents_all_b sr,
1630 jtf_tasks_b tsk
1631 WHERE sr.incident_number = p_incident_number
1632 AND tsk.source_object_id = sr.incident_id
1633 AND tsk.source_object_type_code = 'SR';
1634
1635 CURSOR c_incident_details(p_incident_id NUMBER)
1636 IS
1637 SELECT csb.incident_number,
1638 cstl.summary name,
1639 it.name type,
1640 isevtl.name severity,
1641 isttl.name status,
1642 hzp.party_name customer,
1643 csb.problem_code,
1644 csb.resolution_code,
1645 ADJUST_TIME(csb.incident_date) reported_date
1646 FROM cs_incidents_all_b csb,
1647 cs_incidents_all_tl cstl,
1648 cs_incident_types it,
1649 cs_incident_types_tl ittl,
1650 cs_incident_severities isev,
1651 cs_incident_severities_tl isevtl,
1652 cs_incident_statuses ist,
1653 cs_incident_statuses_tl isttl,
1654 hz_parties hzp
1655 WHERE csb.incident_id = p_incident_id
1656 AND cstl.incident_id = csb.incident_id
1657 AND cstl.LANGUAGE = userenv('LANG')
1658 AND it.incident_type_id(+) = csb.incident_type_id
1659 AND it.incident_type_id = ittl.incident_type_id
1660 AND ittl.LANGUAGE = userenv('LANG')
1661 AND isev.incident_severity_id(+) = csb.incident_severity_id
1662 AND isev.incident_severity_id = isevtl.incident_severity_id
1663 AND isevtl.LANGUAGE = userenv('LANG')
1664 AND ist.incident_status_id(+) = csb.incident_status_id
1665 AND ist.incident_status_id = isttl.incident_status_id
1666 AND isttl.LANGUAGE = userenv('LANG')
1667 AND hzp.party_id(+) = csb.customer_id;
1668
1669 CURSOR c_system_items(p_incident_id NUMBER)
1670 IS
1671 SELECT item.segment1 AS item
1672 FROM cs_incidents_all_b csb,
1673 mtl_system_items_b item
1674 WHERE csb.incident_id = p_incident_id
1675 AND item.inventory_item_id(+) = csb.inventory_item_id
1676 AND item.organization_id = csb.org_id;
1677
1678 CURSOR c_item_instance(p_incident_id NUMBER)
1679 IS
1680 SELECT inst.instance_number AS instance,
1681 inst.serial_number
1682 FROM cs_incidents_all_b csb,
1683 csi_item_instances inst
1684 WHERE csb.incident_id = p_incident_id
1685 AND inst.instance_id(+) = csb.customer_product_id
1686 AND inst.inv_master_organization_id = csb.org_id
1687 AND inst.inventory_item_id = csb.inventory_item_id;
1688
1689 CURSOR c_problem_summary(p_problem_code VARCHAR2)
1690 IS
1691 SELECT description
1692 FROM fnd_lookup_values
1693 WHERE lookup_code = p_problem_code
1694 AND lookup_type = 'REQUEST_PROBLEM_CODE'
1695 AND LANGUAGE = userenv('LANG');
1696
1697 CURSOR c_resolution_summary(p_resolution_code VARCHAR2)
1698 IS
1699 SELECT description
1700 FROM fnd_lookup_values
1701 WHERE lookup_code = p_resolution_code
1702 AND lookup_type = 'REQUEST_RESOLUTION_CODE'
1703 AND LANGUAGE = userenv('LANG');
1704
1705 CURSOR c_sr_notes(p_incident_number VARCHAR2)
1706 IS
1707 SELECT nttl.notes note_text,
1708 lkp.meaning note_status,
1709 rs.source_name entered_by,
1710 ADJUST_TIME(ntb.entered_date) entered_date
1711 FROM cs_incidents_all_b cs,
1712 jtf_notes_b ntb,
1713 jtf_notes_tl nttl,
1714 jtf_rs_resource_extns rs,
1715 fnd_lookup_values lkp
1716 WHERE cs.incident_number = p_incident_number
1717 AND ntb.source_object_id = cs.incident_id
1718 AND ntb.source_object_code = 'SR'
1719 AND nttl.jtf_note_id = ntb.jtf_note_id
1720 AND nttl.LANGUAGE = userenv('LANG')
1721 AND rs.user_id = ntb.entered_by
1722 AND lkp.lookup_code = ntb.note_status
1723 AND lkp.lookup_type = 'JTF_NOTE_STATUS'
1724 AND lkp.LANGUAGE = userenv('LANG');
1725
1726 r_incident_details c_incident_details%ROWTYPE;
1727 l_item mtl_system_items_b.segment1%TYPE;
1728 l_instance_number csi_item_instances.instance_number%TYPE;
1729 l_serial_number csi_item_instances.serial_number%TYPE;
1730 l_description fnd_lookup_values.description%TYPE;
1731 r_sr_notes c_sr_notes%ROWTYPE;
1732 l_incident_id NUMBER;
1733 l_query_text VARCHAR2(4000);
1734 qrycontext DBMS_XMLGEN.ctxHandle;
1735 l_email_format VARCHAR2(240);
1736 l_xml_result CLOB;
1737 l_resource_id NUMBER;
1738 l_task_access_flag VARCHAR2(1) := 'N';
1739 BEGIN
1740 CSM_UTIL_PKG.LOG('Entering GET_SR_DETAILS for SR_NUMBER: ' || p_sr_number, g_object_name, FND_LOG.LEVEL_PROCEDURE);
1741
1742 --Get the Resource id
1743 l_resource_id := GET_RESOURCE_ID( g_user_id);
1744
1745 FOR r_incidents IN c_incidents(p_sr_number,l_resource_id) LOOP
1746 l_task_access_flag := CHECK_TASK_ACCESS(g_user_id, r_incidents.task_number);
1747 IF l_task_access_flag ='Y' THEN
1748 l_incident_id := r_incidents.incident_id;
1749 EXIT;
1750 END IF;
1751 END LOOP;
1752
1753 IF l_task_access_flag = 'N' THEN
1754 x_return_status := FND_API.G_RET_STS_ERROR;
1755 x_error_message := 'Service Request Number: ' || p_sr_number || ' has no assignment for user: ' || g_user_name;
1756 CSM_UTIL_PKG.LOG( x_error_message, g_object_name, FND_LOG.LEVEL_ERROR);
1757 RETURN;
1758 END IF;
1759
1760 OPEN c_incident_details(l_incident_id);
1761 FETCH c_incident_details INTO r_incident_details;
1762 CLOSE c_incident_details;
1763
1764 l_xml_result := '<?xml version="1.0"?>'
1765 || '<ROWSET>'
1766 || ' <ROW>'
1767 || ' <NUMBER><![CDATA[' || r_incident_details.incident_number || ']]></NUMBER>'
1768 || ' <NAME><![CDATA[' || r_incident_details.NAME || ']]></NAME>'
1769 || ' <TYPE><![CDATA[' || r_incident_details.TYPE || ']]></TYPE>'
1770 || ' <STATUS><![CDATA[' || r_incident_details.status || ']]></STATUS>'
1771 || ' <SEVERITY><![CDATA[' ||r_incident_details.severity || ']]></SEVERITY>'
1772 || ' <CUSTOMER><![CDATA[' || r_incident_details.customer || ']]></CUSTOMER>'
1773 || ' <REPORTED_DATE> ' || to_char(r_incident_details.reported_date, g_date_format_mask) || '</REPORTED_DATE>';
1774
1775 OPEN c_system_items(l_incident_id);
1776 FETCH c_system_items INTO l_item;
1777 CLOSE c_system_items;
1778
1779 l_xml_result := l_xml_result
1780 || ' <ITEM> <![CDATA[' || l_item || ']]></ITEM>';
1781
1782 OPEN c_item_instance(l_incident_id);
1783 FETCH c_item_instance INTO l_instance_number, l_serial_number;
1784 CLOSE c_item_instance;
1785
1786 l_xml_result := l_xml_result
1787 || ' <INSTANCE><![CDATA[' || l_instance_number || ']]></INSTANCE>'
1788 || ' <SERIAL_NUMBER><![CDATA[' || l_serial_number || ']]></SERIAL_NUMBER>';
1789
1790 l_xml_result := l_xml_result
1791 || ' <PROBLEM_CODE><![CDATA[' || r_incident_details.problem_code || ']]></PROBLEM_CODE>';
1792
1793 IF r_incident_details.problem_code IS NOT NULL THEN
1794 OPEN c_problem_summary(r_incident_details.problem_code);
1795 FETCH c_problem_summary INTO l_description;
1796 CLOSE c_problem_summary;
1797 l_xml_result := l_xml_result
1798 || ' <PROBLEM_SUMMARY><![CDATA[' || l_description || ']]></PROBLEM_SUMMARY>';
1799 END IF;
1800
1801 l_xml_result := l_xml_result
1802 || ' <RESOLUTION_CODE><![CDATA[' || r_incident_details.resolution_code || ']]></RESOLUTION_CODE>';
1803
1804 IF r_incident_details.resolution_code IS NOT NULL THEN
1805 OPEN c_resolution_summary(r_incident_details.resolution_code);
1806 FETCH c_resolution_summary INTO l_description;
1807 CLOSE c_resolution_summary;
1808 l_xml_result := l_xml_result
1809 || ' <RESOLUTION_SUMMARY><![CDATA[' || l_description || ']]></RESOLUTION_SUMMARY>';
1810 END IF;
1811
1812 l_xml_result := l_xml_result || ' </ROW> ';
1813
1814 FOR r_sr_notes IN c_sr_notes(p_sr_number) LOOP
1815 l_xml_result := l_xml_result
1816 || ' <ROW> '
1817 || ' <NOTE_TEXT> <![CDATA[' || r_sr_notes.note_text || ']]> </NOTE_TEXT>'
1818 || ' <NOTE_STATUS> <![CDATA[' || r_sr_notes.note_status || ']]></NOTE_STATUS>'
1819 || ' <ENTERED_BY> ' || to_char(r_sr_notes.entered_by) || ' </ENTERED_BY>'
1820 || ' <ENTERED_DATE> ' || to_char(r_sr_notes.entered_date, g_date_format_mask) || ' </ENTERED_DATE> '
1821 || ' </ROW> ';
1822 END LOOP;
1823
1824 l_xml_result := l_xml_result || ' </ROWSET>';
1825
1826 p_result := TO_CLOB(l_xml_result);
1827
1828 CSM_UTIL_PKG.LOG('Leaving GET_SR_DETAILS for SR_NUMBER: ' || p_sr_number, g_object_name, FND_LOG.LEVEL_PROCEDURE);
1829
1830 EXCEPTION
1831 WHEN OTHERS THEN
1832 x_return_status := FND_API.G_RET_STS_ERROR;
1833 x_error_message := 'Exception occurred in GET_SR_DETAILS: ' || sqlerrm;
1834 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_EXCEPTION);
1835
1836 END GET_SR_DETAILS;
1837
1838 /**/
1839 PROCEDURE GET_ENTITLEMENTS
1840 ( p_serial_number IN VARCHAR2,
1841 p_result OUT nocopy CLOB,
1842 x_return_status OUT nocopy VARCHAR2,
1843 x_error_message OUT nocopy VARCHAR2
1844 )
1845
1846 AS
1847
1848 CURSOR c_inst_contracts(p_serial_number VARCHAR2, p_resource_id NUMBER)
1849 IS
1850 SELECT csi.instance_id,
1851 cs.contract_service_id
1852 FROM csi_item_instances csi,
1853 cs_incidents_all_b cs,
1854 jtf_tasks_b tsk,
1855 jtf_task_assignments ass
1856 WHERE csi.serial_number = p_serial_number
1857 AND csi.instance_id = cs.customer_product_id
1858 AND tsk.source_object_id = cs.incident_id
1859 AND tsk.source_object_type_code = 'SR'
1860 AND ass.task_id = tsk.task_id
1861 AND ass.resource_id = p_resource_id;
1862
1863 CURSOR c_inst_contracts_group(p_serial_number VARCHAR2, p_resource_id NUMBER)
1864 IS
1865 SELECT csi.instance_id,
1866 cs.contract_service_id
1867 FROM csi_item_instances csi,
1868 cs_incidents_all_b cs,
1869 jtf_tasks_b tsk,
1870 jtf_task_assignments ass
1871 WHERE csi.serial_number = p_serial_number
1872 AND csi.instance_id = cs.customer_product_id
1873 AND tsk.source_object_id = cs.incident_id
1874 AND tsk.source_object_type_code = 'SR'
1875 AND ass.task_id = tsk.task_id
1876 AND ass.resource_type_code ='RS_GROUP'
1877 AND ass.resource_id IN( SELECT gm.GROUP_ID from
1878 jtf_rs_role_relations rr,
1879 jtf_rs_roles_b rb,
1880 jtf_rs_group_members gm
1881 WHERE rr.ROLE_RESOURCE_TYPE ='RS_INDIVIDUAL'
1882 AND rr.ROLE_RESOURCE_ID = p_resource_id
1883 AND rr.ROLE_ID = rb.ROLE_ID
1884 AND rb.ROLE_TYPE_CODE ='CSF_THIRD_PARTY'
1885 --AND rb.ROLE_CODE = 'CSF_THIRD_PARTY_SERVICE_PROVID'
1886 AND rb.ROLE_CODE = 'CSF_THIRD_PARTY_ADMINISTRATOR'
1887 and RR.ROLE_RESOURCE_ID = GM.RESOURCE_ID);
1888
1889 CURSOR c_inst_contracts_tech(p_serial_number VARCHAR2, p_resource_id NUMBER)
1890 IS
1891 SELECT csi.instance_id,
1892 cs.contract_service_id
1893 FROM csi_item_instances csi,
1894 cs_incidents_all_b cs,
1895 jtf_tasks_b tsk,
1896 JTF_TASK_ASSIGNMENTS ASS,
1897 JTF_RS_GROUP_MEMBERS jtm
1898 WHERE csi.serial_number = p_serial_number
1899 AND csi.instance_id = cs.customer_product_id
1900 AND tsk.source_object_id = cs.incident_id
1901 AND tsk.source_object_type_code = 'SR'
1902 and ASS.TASK_ID = TSK.TASK_ID
1903 and ASS.RESOURCE_TYPE_CODE ='RS_EMPLOYEE'
1904 and ASS.RESOURCE_ID = JTM.RESOURCE_ID
1905 AND jtm.GROUP_ID IN( SELECT gm.GROUP_ID from
1906 jtf_rs_role_relations rr,
1907 jtf_rs_roles_b rb,
1908 jtf_rs_group_members gm
1909 WHERE rr.ROLE_RESOURCE_TYPE ='RS_INDIVIDUAL'
1910 AND rr.ROLE_RESOURCE_ID = p_resource_id
1911 AND rr.ROLE_ID = rb.ROLE_ID
1912 AND rb.ROLE_TYPE_CODE ='CSF_THIRD_PARTY'
1913 --AND rb.ROLE_CODE = 'CSF_THIRD_PARTY_SERVICE_PROVID'
1914 and RB.ROLE_CODE = 'CSF_THIRD_PARTY_ADMINISTRATOR'
1915 and RR.ROLE_RESOURCE_ID = GM.RESOURCE_ID);
1916
1917 l_instance_id NUMBER;
1918 l_cont_serive_id NUMBER;
1919
1920 l_inp_rec oks_entitlements_pub.input_rec_ib;
1921 l_ent_contracts oks_entitlements_pub.output_tbl_ib;
1922 l_msg_count NUMBER;
1923 l_msg_data VARCHAR2(4000);
1924
1925 l_xml_result CLOB;
1926 l_resource_id NUMBER;
1927
1928 BEGIN
1929 CSM_UTIL_PKG.LOG('Entering GET_ENTITLEMENTS for SERIAL_NUMBER: ' || p_serial_number, g_object_name, FND_LOG.LEVEL_PROCEDURE);
1930
1931 --Get the Resource id
1932 l_resource_id := GET_RESOURCE_ID( g_user_id);
1933
1934 OPEN c_inst_contracts(p_serial_number, l_resource_id);
1935 FETCH c_inst_contracts INTO l_instance_id, l_cont_serive_id;
1936 CLOSE c_inst_contracts;
1937
1938 IF l_instance_id IS NULL THEN
1939 OPEN c_inst_contracts_group(p_serial_number, l_resource_id);
1940 FETCH c_inst_contracts_group INTO l_instance_id, l_cont_serive_id;
1941 CLOSE c_inst_contracts_group;
1942 IF l_instance_id IS NULL THEN
1943 x_error_message := 'User: ' || g_user_name || ' has no task for serial number: ' || p_serial_number;
1944 CSM_UTIL_PKG.LOG( x_error_message, g_object_name, FND_LOG.LEVEL_ERROR);
1945 RETURN;
1946 END IF;
1947 END IF;
1948
1949 l_inp_rec.service_line_id := l_cont_serive_id;
1950 l_inp_rec.product_id := l_instance_id;
1951 l_inp_rec.validate_flag := 'N';
1952
1953 oks_entitlements_pub.get_contracts
1954 ( p_api_version => 1.0,
1955 p_init_msg_list => fnd_api.g_true,
1956 p_inp_rec => l_inp_rec,
1957 x_return_status => x_return_status,
1958 x_msg_count => l_msg_count,
1959 x_msg_data => l_msg_data,
1960 x_ent_contracts => l_ent_contracts
1961 );
1962
1963 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1964 x_return_status := FND_API.G_RET_STS_ERROR;
1965 x_error_message := 'Error in GET_ENTITLEMENTS :'
1966 || ' ROOT ERROR: oks_entitlements_pub.get_contracts'
1967 || ' for serial number : ' || p_serial_number
1968 || ' Details:' || l_msg_data;
1969
1970 CSM_UTIL_PKG.LOG( x_error_message,g_object_name, FND_LOG.LEVEL_ERROR);
1971 RETURN ;
1972 END IF;
1973
1974 l_xml_result := '<?xml version="1.0"?>'
1975 || '<ROWSET>';
1976
1977 FOR i IN 1..l_ent_contracts.COUNT LOOP
1978
1979 l_xml_result := l_xml_result
1980 || '<ROW>'
1981 || '<CONTRACT_NUMBER><![CDATA[' || l_ent_contracts(i).contract_number || ']]></CONTRACT_NUMBER>'
1982 || '<NAME><![CDATA[' || l_ent_contracts(i).service_name || ']]></NAME>'
1983 || '<DESCRIPTION><![CDATA[' || l_ent_contracts(i).service_description || ']]></DESCRIPTION>'
1984 || '<STATUS><![CDATA[' || l_ent_contracts(i).sts_code || ']]></STATUS>'
1985 || '<COVERAGE_NAME><![CDATA[' || l_ent_contracts(i).coverage_term_name || ']]></COVERAGE_NAME>'
1986 || '<COVERAGE_DESCRIPTION><![CDATA[' || l_ent_contracts(i).coverage_term_description || ']]></COVERAGE_DESCRIPTION>'
1987 || '<WARRANTY><![CDATA[' || l_ent_contracts(i).warranty_flag || ']]></WARRANTY>'
1988 || '<START_DATE>' || to_char(ADJUST_TIME(l_ent_contracts(i).service_start_date), g_date_format_mask) || '</START_DATE>'
1989 || '<END_DATE>' || to_char(ADJUST_TIME(l_ent_contracts(i).service_end_date), g_date_format_mask) || '</END_DATE>'
1990 || '</ROW>';
1991 END LOOP;
1992
1993 l_xml_result := l_xml_result
1994 || '</ROWSET>';
1995
1996 p_result := l_xml_result;
1997
1998 CSM_UTIL_PKG.LOG('Leaving GET_ENTITLEMENTS for SERIAL_NUMBER: ' || p_serial_number, g_object_name, FND_LOG.LEVEL_PROCEDURE);
1999
2000 EXCEPTION
2001 WHEN OTHERS THEN
2002 x_return_status := FND_API.G_RET_STS_ERROR;
2003 x_error_message := 'Exception occurred in GET_ENTITLEMENTS: ' || sqlerrm;
2004 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_EXCEPTION);
2005
2006 END GET_ENTITLEMENTS;
2007
2008 /*Procedure to get the information of mobile query command*/
2009 PROCEDURE HELP_QUERY
2010 ( p_query_name IN VARCHAR2,
2011 p_result OUT nocopy CLOB,
2012 x_return_status OUT nocopy VARCHAR2,
2013 x_error_message OUT nocopy VARCHAR2
2014 )
2015 AS
2016 l_query_text VARCHAR2(4000);
2017 qrycontext DBMS_XMLGEN.ctxHandle;
2018
2019 BEGIN
2020 CSM_UTIL_PKG.LOG('Entering HELP_QUERY for query_name: ' || p_query_name, g_object_name, FND_LOG.LEVEL_PROCEDURE);
2021
2022 l_query_text := 'SELECT query_name command_name,
2023 description
2024 FROM csm_query_tl qtl,
2025 csm_query_b qb
2026 WHERE UPPER(qb.query_name) like UPPER(''%'|| trim(p_query_name) ||'%'')
2027 AND qb.email_enabled = ''Y''
2028 AND NVL(qb.disabled_flag,''N'') = ''N''
2029 AND qtl.language = userenv(''LANG'')
2030 AND qb.query_id = qtl.query_id';
2031
2032 qrycontext := DBMS_XMLGEN.newcontext(l_query_text) ;
2033 DBMS_XMLGEN.setnullhandling (qrycontext, DBMS_XMLGEN.empty_tag);
2034 p_result := DBMS_XMLGEN.getxml (qrycontext);
2035
2036 CSM_UTIL_PKG.LOG('Leaving HELP_QUERY for query_name: ' || p_query_name, g_object_name, FND_LOG.LEVEL_PROCEDURE);
2037
2038 EXCEPTION
2039 WHEN OTHERS THEN
2040 x_return_status := FND_API.G_RET_STS_ERROR;
2041 x_error_message := 'Exception occurred in HELP_QUERY: ' || sqlerrm;
2042 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_EXCEPTION);
2043
2044 END HELP_QUERY;
2045
2046
2047 FUNCTION set_profile
2048 ( p_profile_name VARCHAR2,
2049 p_profile_value VARCHAR2
2050 ) RETURN VARCHAR2
2051 IS
2052 l_return_stat boolean;
2053 l_return_flag VARCHAR2(1);
2054 BEGIN
2055 CSM_UTIL_PKG.LOG('Entering SET_PROFILE for p_profile_name: ' || p_profile_name, g_object_name, FND_LOG.LEVEL_PROCEDURE);
2056 l_return_flag := 'N';
2057
2058 l_return_stat := fnd_profile.save(p_profile_name, p_profile_value, 'SITE');
2059 IF l_return_stat THEN
2060 l_return_flag := 'Y';
2061 COMMIT;
2062 END IF;
2063
2064 CSM_UTIL_PKG.LOG('Leaving SET_PROFILE for p_profile_name: ' || p_profile_name, g_object_name, FND_LOG.LEVEL_PROCEDURE);
2065 RETURN l_return_flag;
2066 END set_profile;
2067
2068 PROCEDURE NOTIFY_EMAIL_EXCEPTION
2069 ( p_email_id IN VARCHAR2,
2070 p_subject IN VARCHAR2,
2071 p_message IN VARCHAR2,
2072 x_return_status OUT nocopy VARCHAR2,
2073 x_error_message OUT nocopy VARCHAR2 )
2074 IS
2075 CURSOR c_role(p_email_id VARCHAR2, p_user_id NUMBER)
2076 IS
2077 SELECT wf.name as role_name, fu.user_name as user_name
2078 FROM wf_local_roles wf,
2079 fnd_user fu
2080 WHERE wf.email_address = p_email_id
2081 AND fu.user_id = p_user_id
2082 AND wf.status = 'ACTIVE'
2083 AND wf.start_date <= sysdate
2084 AND (wf.expiration_date IS NULL OR wf.expiration_date > sysdate);
2085
2086 l_nid NUMBER;
2087 l_role_name wf_local_roles.name%type;
2088 l_user_name fnd_user.user_name%type;
2089 l_user_id NUMBER;
2090 BEGIN
2091
2092 CSM_UTIL_PKG.LOG('Entering NOTIFY_EMAIL_EXCEPTION for EMAIL_ID: ' || p_email_id, g_object_name, FND_LOG.LEVEL_PROCEDURE);
2093
2094 l_user_id := IS_FND_USER(p_email_id);
2095
2096 IF l_user_id = -1 THEN
2097 x_return_status := fnd_api.g_ret_sts_error;
2098 x_error_message := 'EMAIL_ID: ' || p_email_id || ' is not accosiated to a valid FND_USER';
2099 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_ERROR);
2100 RETURN;
2101 END IF;
2102
2103 OPEN c_role(p_email_id, l_user_id);
2104 FETCH c_role INTO l_role_name, l_user_name;
2105 CLOSE c_role;
2106 IF l_role_name IS NULL THEN
2107
2108 l_role_name := l_user_name;
2109
2110 wf_directory.CreateAdHocUser
2111 ( name => l_role_name,
2112 display_name => l_user_name,
2113 notification_preference => 'MAILTEXT',
2114 email_address =>p_email_id);
2115
2116 CSM_UTIL_PKG.LOG('Created Role : ' || l_role_name, g_object_name, FND_LOG.LEVEL_PROCEDURE);
2117
2118 END IF;
2119
2120 l_nid := wf_notification.Send
2121 ( role => l_role_name
2122 , msg_type => 'CSM_MSGS'
2123 , msg_name => 'FYI_MESSAGE'
2124 );
2125 wf_notification.SetAttrText
2126 ( l_nid
2127 , 'MESSAGE_BODY'
2128 , p_message
2129 );
2130
2131 wf_notification.SetAttrText
2132 ( l_nid
2133 , 'SUBJECT'
2134 , p_subject
2135 );
2136
2137 wf_notification.denormalize_notification(l_nid);
2138 x_return_status := fnd_api.g_ret_sts_success;
2139 x_error_message := 'Successfully send notification id: ' || l_nid || ' to role: ' || l_role_name;
2140
2141 CSM_UTIL_PKG.LOG('Leaving NOTIFY_EMAIL_EXECPTION for EMAIL_ID: ' || p_email_id || ' NID: ' || l_nid, g_object_name, FND_LOG.LEVEL_PROCEDURE);
2142
2143 EXCEPTION
2144 WHEN OTHERS THEN
2145 x_return_status := FND_API.G_RET_STS_ERROR;
2146 x_error_message := 'Exception occurred in NOTIFY_EMAIL_EXCEPTION: ' || sqlerrm;
2147 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_EXCEPTION);
2148
2149 END NOTIFY_EMAIL_EXCEPTION;
2150
2151 /*Get item information for a given item*/
2152 PROCEDURE GET_ITEM_DETAILS
2153 ( p_item IN VARCHAR2,
2154 p_result OUT nocopy CLOB,
2155 x_return_status OUT nocopy VARCHAR2,
2156 x_error_message OUT nocopy VARCHAR2
2157 )
2158
2159 AS
2160
2161 CURSOR c_get_item(p_item VARCHAR2, p_org_id NUMBER)
2162 IS
2163 SELECT
2164 B.SEGMENT1,
2165 TL.DESCRIPTION,
2166 (SELECT MEANING FROM FND_LOOKUP_VALUES WHERE
2167 LOOKUP_TYPE = 'CSP_RECOVERED_PART_DISP_CODE'
2168 AND LOOKUP_CODE = B.RECOVERED_PART_DISP_CODE
2169 AND LANGUAGE = USERENV('LANG')
2170 ) AS PART_DISPOSITION,
2171 DECODE(NVL(B.REVISION_QTY_CONTROL_CODE,'1'),'2', 'Yes','1','No') AS REVISION,
2172 (SELECT MEANING FROM FND_LOOKUP_VALUES WHERE
2173 LOOKUP_TYPE = 'MTL_LOCATOR_RESTRICTIONS'
2174 AND LOOKUP_CODE = B.RESTRICT_LOCATORS_CODE
2175 AND LANGUAGE = USERENV('LANG')
2176 ) AS RESTRICT_LOCATORS_CODE
2177 ,
2178
2179 (SELECT MEANING FROM FND_LOOKUP_VALUES WHERE
2180 LOOKUP_TYPE = 'MTL_SUBINVENTORY_RESTRICTIONS'
2181 AND LOOKUP_CODE = B.RESTRICT_SUBINVENTORIES_CODE
2182 AND LANGUAGE = USERENV('LANG')
2183 ) AS RESTRICT_SUBINVENTORIES_CODE,
2184
2185 (SELECT MEANING FROM FND_LOOKUP_VALUES WHERE
2186 LOOKUP_TYPE = 'MTL_LOCATION_CONTROL'
2187 AND LOOKUP_CODE = B.LOCATION_CONTROL_CODE
2188 AND LANGUAGE = USERENV('LANG')
2189 ) AS LOCATION_CONTROL_CODE,
2190
2191 (SELECT MEANING FROM FND_LOOKUP_VALUES WHERE
2192 LOOKUP_TYPE = 'MTL_LOT_CONTROL'
2193 AND LOOKUP_CODE = B.LOT_CONTROL_CODE
2194 AND LANGUAGE = USERENV('LANG')
2195 ) AS LOT_CONTROL_CODE,
2196
2197 (SELECT MEANING FROM FND_LOOKUP_VALUES WHERE
2198 LOOKUP_TYPE = 'MTL_SERIAL_NUMBER'
2199 AND LOOKUP_CODE = B.SERIAL_NUMBER_CONTROL_CODE
2200 AND LANGUAGE = USERENV('LANG')
2201 ) AS SERIAL_NUMBER_CONTROL_CODE ,
2202
2203 DECODE(NVL(B.COMMS_NL_TRACKABLE_FLAG,'N'),'Y', 'Yes','N','No') AS IB_TRACKABLE
2204 FROM MTL_SYSTEM_ITEMS_B B,MTL_SYSTEM_ITEMS_TL TL
2205 WHERE UPPER(B.SEGMENT1) = UPPER(p_item)
2206 AND B.INVENTORY_ITEM_ID = TL.INVENTORY_ITEM_ID
2207 AND B.ORGANIZATION_ID = TL.ORGANIZATION_ID
2208 AND TL.LANGUAGE = USERENV('LANG')
2209 AND B.ORGANIZATION_ID = p_org_id;
2210
2211 CURSOR C_GET_ORG(p_ORG_ID NUMBER)
2212 IS
2213 SELECT NAME
2214 FROM HR_ALL_ORGANIZATION_UNITS_TL
2215 WHERE ORGANIZATION_ID =p_org_id
2216 AND LANGUAGE = USERENV('LANG');
2217
2218 TYPE TBL_ITEM_ATTR IS TABLE OF C_GET_ITEM%ROWTYPE;
2219 LST_ITEM_ATTR TBL_ITEM_ATTR;
2220
2221 l_instance_id NUMBER;
2222 l_cont_serive_id NUMBER;
2223 l_msg_count NUMBER;
2224 l_msg_data VARCHAR2(4000);
2225
2226 L_XML_RESULT CLOB;
2227 l_profile_value NUMBER;
2228 L_ORG_NAME VARCHAR2(2000);
2229
2230 BEGIN
2231 CSM_UTIL_PKG.LOG('Entering GET_ITEM_DETAILS for Item : ' || p_item, g_object_name, FND_LOG.LEVEL_PROCEDURE);
2232
2233 l_profile_value := TO_NUMBER(fnd_profile.value_specific('CS_INV_VALIDATION_ORG',G_USER_ID,NULL,NULL));
2234
2235 --get org name for display
2236 OPEN C_GET_ORG(L_PROFILE_VALUE);
2237 FETCH C_GET_ORG INTO L_ORG_NAME;
2238 CLOSE C_GET_ORG;
2239
2240 OPEN C_GET_ITEM(P_ITEM, L_PROFILE_VALUE);
2241 FETCH C_GET_ITEM BULK COLLECT INTO LST_ITEM_ATTR;
2242 CLOSE C_GET_ITEM;
2243
2244 IF LST_ITEM_ATTR.COUNT = 0 THEN
2245 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2246 x_error_message := 'The Item ' ||P_ITEM || 'is not present in the Organization ' || l_profile_value;
2247 CSM_UTIL_PKG.LOG( x_error_message,g_object_name, FND_LOG.LEVEL_ERROR);
2248 RETURN ;
2249 END IF;
2250
2251 L_XML_RESULT := '<?xml version="1.0"?>'
2252 || '<ROWSET>';
2253
2254 FOR I IN 1..LST_ITEM_ATTR.COUNT LOOP
2255
2256 L_XML_RESULT := L_XML_RESULT || ' <ROW>'
2257 || '<ITEM_NAME><![CDATA[' || LST_ITEM_ATTR(I).SEGMENT1 || ']]></ITEM_NAME>'
2258 || '<ITEM_DESCRIPTION><![CDATA[' || LST_ITEM_ATTR(i).DESCRIPTION || ']]></ITEM_DESCRIPTION>'
2259 || '<ORGANIZATION><![CDATA[' || L_ORG_NAME || ']]></ORGANIZATION>'
2260 || '<PART_DISPOSITION><![CDATA[' || LST_ITEM_ATTR(i).PART_DISPOSITION || ']]></PART_DISPOSITION>'
2261 || '<REVISION><![CDATA[' || LST_ITEM_ATTR(i).REVISION || ']]></REVISION>'
2262 || '<RESTRICT_LOCATORS><![CDATA[' || LST_ITEM_ATTR(I).RESTRICT_LOCATORS_CODE || ']]></RESTRICT_LOCATORS>'
2263 || '<RESTRICT_SUBINVENTORIES><![CDATA[' || LST_ITEM_ATTR(i).RESTRICT_SUBINVENTORIES_CODE || ']]></RESTRICT_SUBINVENTORIES>'
2264 || '<LOCATION_CONTROL><![CDATA[' || LST_ITEM_ATTR(i).LOCATION_CONTROL_CODE || ']]></LOCATION_CONTROL>'
2265 || '<LOT_CONTROL><![CDATA[' || LST_ITEM_ATTR(i).LOT_CONTROL_CODE || ']]></LOT_CONTROL>'
2266 || '<SERIAL_NUMBER_CONTROL><![CDATA[' || LST_ITEM_ATTR(I).SERIAL_NUMBER_CONTROL_CODE || ']]></SERIAL_NUMBER_CONTROL>'
2267 || '<IB_TRACKABLE><![CDATA[' || LST_ITEM_ATTR(I).IB_TRACKABLE || ']]></IB_TRACKABLE>'
2268 || ' </ROW> ';
2269 END LOOP;
2270
2271 l_xml_result := l_xml_result || '</ROWSET>';
2272
2273 p_result := l_xml_result;
2274 CSM_UTIL_PKG.LOG('Leaving GET_ITEM_DETAILS for Item Name : ' || p_item, g_object_name, FND_LOG.LEVEL_PROCEDURE);
2275
2276 EXCEPTION
2277 WHEN OTHERS THEN
2278 x_return_status := FND_API.G_RET_STS_ERROR;
2279 x_error_message := 'Exception occurred in GET_ITEM_DETAILS: ' || sqlerrm;
2280 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_EXCEPTION);
2281
2282 END GET_ITEM_DETAILS;
2283
2284 /*Procedure to get the Available Substitutes or Supersessions*/
2285 PROCEDURE GET_PART_SUB
2286 ( p_item IN VARCHAR2,
2287 p_result OUT nocopy CLOB,
2288 x_return_status OUT nocopy VARCHAR2,
2289 x_error_message OUT nocopy VARCHAR2
2290 )
2291
2292 AS
2293
2294 CURSOR c_get_sub(p_inv_item_id NUMBER, p_org_id NUMBER)
2295 IS
2296 SELECT B.SEGMENT1,
2297 TL.DESCRIPTION,(SELECT MEANING FROM FND_LOOKUP_VALUES WHERE
2298 LOOKUP_TYPE = 'MTL_RELATIONSHIP_TYPES'
2299 AND LOOKUP_CODE = R.RELATIONSHIP_TYPE_ID
2300 AND LANGUAGE = USERENV('LANG')
2301 ) AS ITEM_TYPE,
2302 R.RELATED_ITEM_ID
2303 FROM
2304 MTL_RELATED_ITEMS R,
2305 MTL_SYSTEM_ITEMS_B B,
2306 MTL_SYSTEM_ITEMS_TL TL
2307 WHERE R.INVENTORY_ITEM_ID = p_INV_ITEM_ID
2308 AND R.ORGANIZATION_ID = p_ORG_ID
2309 AND R.RELATIONSHIP_TYPE_ID IN(2,8)
2310 AND B.INVENTORY_ITEM_ID = R.RELATED_ITEM_ID
2311 AND B.ORGANIZATION_ID = R.ORGANIZATION_ID
2312 AND B.INVENTORY_ITEM_ID = TL.INVENTORY_ITEM_ID
2313 AND B.ORGANIZATION_ID = TL.ORGANIZATION_ID
2314 AND TL.LANGUAGE = USERENV('LANG');
2315
2316
2317 CURSOR c_get_item(p_item VARCHAR2, p_org_id NUMBER)
2318 IS
2319 SELECT B.INVENTORY_ITEM_ID,B.SEGMENT1,TL.DESCRIPTION
2320 FROM
2321 MTL_SYSTEM_ITEMS_B B,
2322 MTL_SYSTEM_ITEMS_TL TL
2323 WHERE UPPER(B.SEGMENT1) = UPPER(p_item)
2324 AND B.ORGANIZATION_ID = p_ORG_ID
2325 AND B.INVENTORY_ITEM_ID = TL.INVENTORY_ITEM_ID
2326 AND B.ORGANIZATION_ID = TL.ORGANIZATION_ID
2327 AND TL.LANGUAGE = USERENV('LANG');
2328
2329 CURSOR C_GET_SUBINV(p_inv_item_id NUMBER, p_ORG_ID NUMBER)
2330 IS
2331 SELECT SUBINVENTORY_CODE
2332 FROM MTL_ONHAND_QUANTITIES_DETAIL
2333 WHERE INVENTORY_ITEM_ID = p_INV_ITEM_ID
2334 AND ORGANIZATION_ID = p_ORG_ID
2335 GROUP BY INVENTORY_ITEM_ID, ORGANIZATION_ID, SUBINVENTORY_CODE
2336 HAVING SUM (TRANSACTION_QUANTITY) > 0 ;
2337
2338 CURSOR C_GET_ORG(p_ORG_ID NUMBER)
2339 IS
2340 SELECT NAME
2341 FROM HR_ALL_ORGANIZATION_UNITS_TL
2342 WHERE ORGANIZATION_ID =p_org_id
2343 AND LANGUAGE = USERENV('LANG');
2344
2345
2346 TYPE TBL_PART_SUBS IS TABLE OF C_GET_SUB%ROWTYPE;
2347 LST_PART_SUBS TBL_PART_SUBS;
2348
2349 TYPE TBL_SUB_INV IS TABLE OF C_GET_SUBINV%ROWTYPE;
2350 LST_SUB_INVENTORY TBL_SUB_INV;
2351
2352 l_instance_id NUMBER;
2353 l_cont_serive_id NUMBER;
2354 l_msg_count NUMBER;
2355 l_msg_data VARCHAR2(4000);
2356
2357 L_XML_RESULT CLOB;
2358 L_PROFILE_VALUE NUMBER;
2359 L_GIVEN_ITEM_ID NUMBER;
2360 L_GIVEN_ITEM_NAME VARCHAR2(40);
2361 L_GIVEN_ITEM_DESC VARCHAR2(240);
2362 L_ORG_NAME VARCHAR2(2000);
2363 BEGIN
2364 CSM_UTIL_PKG.LOG('Entering GET_PART_SUB for Item : ' || p_item, g_object_name, FND_LOG.LEVEL_PROCEDURE);
2365
2366 l_profile_value := TO_NUMBER(fnd_profile.value_specific('CS_INV_VALIDATION_ORG',G_USER_ID,NULL,NULL));
2367
2368 --get org name for display
2369 OPEN C_GET_ORG(L_PROFILE_VALUE);
2370 FETCH C_GET_ORG INTO L_ORG_NAME;
2371 CLOSE C_GET_ORG;
2372
2373 --Get whether given item is valid or not
2374 OPEN C_GET_ITEM(P_ITEM, L_PROFILE_VALUE);
2375 FETCH C_GET_ITEM INTO L_GIVEN_ITEM_ID,L_GIVEN_ITEM_NAME,L_GIVEN_ITEM_DESC;
2376 CLOSE C_GET_ITEM;
2377
2378 IF L_GIVEN_ITEM_ID IS NULL THEN
2379 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2380 x_error_message := 'The given Item ' ||P_ITEM || 'is not present in the Organization ' || l_profile_value;
2381 CSM_UTIL_PKG.LOG( x_error_message,g_object_name, FND_LOG.LEVEL_ERROR);
2382 RETURN ;
2383 END IF;
2384
2385 --get the substitutes
2386 OPEN c_get_sub(L_GIVEN_ITEM_ID, l_profile_value);
2387 FETCH c_get_sub BULK COLLECT INTO LST_PART_SUBS;
2388 CLOSE c_get_sub;
2389
2390 IF LST_PART_SUBS.COUNT = 0 THEN
2391 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2392 x_error_message := 'There is no Valid Substitue found for the Item ' ||P_ITEM || 'in the Organization ' || l_profile_value;
2393 CSM_UTIL_PKG.LOG( x_error_message,g_object_name, FND_LOG.LEVEL_ERROR);
2394 RETURN ;
2395 END IF;
2396
2397
2398
2399 L_XML_RESULT := '<?xml version="1.0"?>'
2400 || '<ROWSET>';
2401
2402 FOR I IN 1..LST_PART_SUBS.COUNT LOOP
2403 OPEN C_GET_SUBINV(LST_PART_SUBS(I).RELATED_ITEM_ID, l_profile_value);
2404 FETCH C_GET_SUBINV BULK COLLECT INTO LST_SUB_INVENTORY;
2405 CLOSE C_GET_SUBINV;
2406
2407 IF LST_SUB_INVENTORY.COUNT = 0 THEN
2408 L_XML_RESULT := L_XML_RESULT || ' <ROW>'
2409 || '<SUPPLIED_ITEM_NAME><![CDATA[' || LST_PART_SUBS(I).SEGMENT1 || ']]></SUPPLIED_ITEM_NAME>'
2410 || '<SUPPLIED_ITEM_DESCRIPTION><![CDATA[' || LST_PART_SUBS(I).DESCRIPTION || ']]></SUPPLIED_ITEM_DESCRIPTION>'
2411 || '<ITEM_TYPE><![CDATA[' || LST_PART_SUBS(i).ITEM_TYPE || ']]></ITEM_TYPE>'
2412 || '<REQUIRED_ITEM_NAME><![CDATA[' || L_GIVEN_ITEM_NAME || ']]></REQUIRED_ITEM_NAME>'
2413 || '<REQUIRED_ITEM_DESCRIPTION><![CDATA[' || L_GIVEN_ITEM_DESC || ']]></REQUIRED_ITEM_DESCRIPTION>'
2414 || '<SOURCE_ORG><![CDATA[' || L_ORG_NAME || ']]></SOURCE_ORG>'
2415 || '<SOURCE_SUBINVENTORY/>'
2416 || ' </ROW> ';
2417 ELSE
2418 FOR j IN 1..LST_SUB_INVENTORY.COUNT LOOP
2419 L_XML_RESULT := L_XML_RESULT || ' <ROW>'
2420 || '<SUPPLIED_ITEM_NAME><![CDATA[' || LST_PART_SUBS(I).SEGMENT1 || ']]></SUPPLIED_ITEM_NAME>'
2421 || '<SUPPLIED_ITEM_DESCRIPTION><![CDATA[' || LST_PART_SUBS(I).DESCRIPTION || ']]></SUPPLIED_ITEM_DESCRIPTION>'
2422 || '<ITEM_TYPE><![CDATA[' || LST_PART_SUBS(i).ITEM_TYPE || ']]></ITEM_TYPE>'
2423 || '<REQUIRED_ITEM_NAME><![CDATA[' || L_GIVEN_ITEM_NAME || ']]></REQUIRED_ITEM_NAME>'
2424 || '<REQUIRED_ITEM_DESCRIPTION><![CDATA[' || L_GIVEN_ITEM_DESC || ']]></REQUIRED_ITEM_DESCRIPTION>'
2425
2426 || '<SOURCE_ORG><![CDATA[' || L_ORG_NAME || ']]></SOURCE_ORG>'
2427 || '<SOURCE_SUBINVENTORY><![CDATA[' || LST_SUB_INVENTORY(j).SUBINVENTORY_CODE || ']]></SOURCE_SUBINVENTORY>'
2428 || ' </ROW> ';
2429 END LOOP;
2430 END IF;
2431
2432 END LOOP;
2433
2434 l_xml_result := l_xml_result || '</ROWSET>';
2435
2436 p_result := l_xml_result;
2437
2438 CSM_UTIL_PKG.LOG('Leaving GET_PART_SUB for Item Name : ' || p_item, g_object_name, FND_LOG.LEVEL_PROCEDURE);
2439
2440 EXCEPTION
2441 WHEN OTHERS THEN
2442 x_return_status := FND_API.G_RET_STS_ERROR;
2443 x_error_message := 'Exception occurred in GET_PART_SUB: ' || sqlerrm;
2444 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_EXCEPTION);
2445
2446 END GET_PART_SUB;
2447
2448 /*Procedure to get the Available Ohq*/
2449 PROCEDURE GET_PART_AVAIL
2450 ( p_item IN VARCHAR2,
2451 p_result OUT nocopy CLOB,
2452 x_return_status OUT nocopy VARCHAR2,
2453 x_error_message OUT nocopy VARCHAR2
2454 )
2455
2456 AS
2457
2458 CURSOR c_get_item(p_item VARCHAR2, p_org_id NUMBER)
2459 IS
2460 SELECT B.INVENTORY_ITEM_ID,B.SEGMENT1,TL.DESCRIPTION
2461 FROM
2462 MTL_SYSTEM_ITEMS_B B,
2463 MTL_SYSTEM_ITEMS_TL TL
2464 WHERE UPPER(B.SEGMENT1) = UPPER(p_item)
2465 AND B.ORGANIZATION_ID = p_ORG_ID
2466 AND B.INVENTORY_ITEM_ID = TL.INVENTORY_ITEM_ID
2467 AND B.ORGANIZATION_ID = TL.ORGANIZATION_ID
2468 AND TL.LANGUAGE = USERENV('LANG');
2469
2470 CURSOR C_GET_ORG(p_ORG_ID NUMBER)
2471 IS
2472 SELECT NAME
2473 FROM HR_ALL_ORGANIZATION_UNITS_TL
2474 WHERE ORGANIZATION_ID =p_org_id
2475 AND LANGUAGE = USERENV('LANG');
2476
2477 CURSOR c_get_item_name(p_item_id NUMBER, p_org_id NUMBER)
2478 IS
2479 SELECT B.SEGMENT1,TL.DESCRIPTION
2480 FROM
2481 MTL_SYSTEM_ITEMS_B B,
2482 MTL_SYSTEM_ITEMS_TL TL
2483 WHERE B.INVENTORY_ITEM_ID = p_item_id
2484 AND B.ORGANIZATION_ID = p_ORG_ID
2485 AND B.INVENTORY_ITEM_ID = TL.INVENTORY_ITEM_ID
2486 AND B.ORGANIZATION_ID = TL.ORGANIZATION_ID
2487 AND TL.LANGUAGE = USERENV('LANG');
2488
2489
2490 CURSOR c_get_ohq
2491 IS
2492 SELECT
2493 SUPPLIED_ITEM_ID,
2494 SUPPLIED_ITEM_TYPE,
2495 SUPPLIED_QUANTITY,
2496 ORGANIZATION_ID,
2497 SUBINVENTORY_CODE,
2498 SOURCE_TYPE_CODE,
2499 DISTANCE
2500 FROM
2501 csp_available_parts_temp;
2502
2503 r_get_ohq c_get_ohq%ROWTYPE;
2504
2505 l_instance_id NUMBER;
2506 l_cont_serive_id NUMBER;
2507 l_msg_count NUMBER;
2508 l_msg_data VARCHAR2(4000);
2509
2510 L_XML_RESULT CLOB;
2511 L_PROFILE_VALUE NUMBER;
2512 L_GIVEN_ITEM_ID NUMBER;
2513 L_GIVEN_ITEM_NAME VARCHAR2(200);
2514 L_GIVEN_ITEM_DESC VARCHAR2(4000);
2515 L_ORG_NAME VARCHAR2(240);
2516 L_RETURN_STATUS VARCHAR2(100);
2517 l_required_parts_tbl csp_part_search_pvt.required_parts_tbl;
2518 l_search_params_rec csp_part_search_pvt.search_params_rec;
2519 l_count NUMBER;
2520 l_resource_id NUMBER;
2521
2522 BEGIN
2523 CSM_UTIL_PKG.LOG('Entering GET_PART_AVAIL for Item : ' || p_item, g_object_name, FND_LOG.LEVEL_PROCEDURE);
2524
2525 l_profile_value := TO_NUMBER(fnd_profile.value_specific('CS_INV_VALIDATION_ORG',G_USER_ID,NULL,NULL));
2526
2527 --get org name for display
2528 OPEN C_GET_ORG(L_PROFILE_VALUE);
2529 FETCH C_GET_ORG INTO L_ORG_NAME;
2530 CLOSE C_GET_ORG;
2531
2532 --Get whether given item is valid or not
2533 OPEN C_GET_ITEM(P_ITEM, L_PROFILE_VALUE);
2534 FETCH C_GET_ITEM INTO L_GIVEN_ITEM_ID,L_GIVEN_ITEM_NAME,L_GIVEN_ITEM_DESC;
2535 CLOSE C_GET_ITEM;
2536
2537 IF L_GIVEN_ITEM_ID IS NULL THEN
2538 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2539 x_error_message := 'The given Item ' ||P_ITEM || 'is not present in the Organization ' || l_profile_value;
2540 CSM_UTIL_PKG.LOG( x_error_message,g_object_name, FND_LOG.LEVEL_ERROR);
2541 RETURN ;
2542 END IF;
2543
2544 --Set the item parameter
2545 l_required_parts_tbl(1).inventory_item_id := L_GIVEN_ITEM_ID;
2546 l_required_parts_tbl(1).revision := NULL;
2547 l_required_parts_tbl(1).quantity := NULL;
2548
2549 --Set the Search PArams
2550 SELECT resource_id INTO l_resource_id
2551 FROM jtf_rs_resource_extns
2552 WHERE user_id = G_USER_ID
2553 AND category = 'EMPLOYEE';
2554
2555 l_search_params_rec.search_method := NULL;
2556 l_search_params_rec.my_inventory := TRUE;
2557 l_search_params_rec.technicians := TRUE;
2558 l_search_params_rec.manned_warehouses := TRUE;
2559 l_search_params_rec.unmanned_warehouses := TRUE;
2560 l_search_params_rec.include_alternates := TRUE;
2561 l_search_params_rec.include_closed := TRUE;
2562 l_search_params_rec.quantity_type := 'AVAILABLE';
2563 l_search_params_rec.ship_set := NULL;
2564 l_search_params_rec.need_by_date := NULL;
2565 l_search_params_rec.resource_type := 'RS_EMPLOYEE';
2566 l_search_params_rec.resource_id := l_resource_id;
2567 l_search_params_rec.distance := NULL;
2568 L_SEARCH_PARAMS_REC.DISTANCE_UOM := null;
2569 l_search_params_rec.source_organization_id := NULL; --L_PROFILE_VALUE
2570 l_search_params_rec.source_subinventory := NULL;
2571 l_search_params_rec.to_location_id := NULL;
2572 l_search_params_rec.to_hz_location_id := NULL;
2573 l_search_params_rec.current_location := NULL;
2574
2575
2576 --get the substitutes
2577
2578 csp_part_search_pvt.search(p_required_parts => l_required_parts_tbl,
2579 p_search_params => l_search_params_rec,
2580 x_return_status => L_RETURN_STATUS,
2581 x_msg_data => l_msg_data,
2582 x_msg_count => l_msg_count
2583 );
2584
2585
2586 L_XML_RESULT := '<?xml version="1.0"?>'
2587 || '<ROWSET>';
2588
2589 FOR r_get_ohq IN c_get_ohq LOOP
2590
2591 --get org name for display
2592 OPEN C_GET_ORG(r_get_ohq.ORGANIZATION_ID);
2593 FETCH C_GET_ORG INTO L_ORG_NAME;
2594 CLOSE C_GET_ORG;
2595
2596 --Get whether given item is valid or not
2597 OPEN C_GET_ITEM_NAME(r_get_ohq.SUPPLIED_ITEM_ID, r_get_ohq.ORGANIZATION_ID);
2598 FETCH C_GET_ITEM_NAME INTO L_GIVEN_ITEM_NAME,L_GIVEN_ITEM_DESC;
2599 CLOSE C_GET_ITEM_NAME;
2600
2601 L_XML_RESULT := L_XML_RESULT || ' <ROW>'
2602 || '<ITEM><![CDATA[' || L_GIVEN_ITEM_NAME || ']]></ITEM>'
2603 || '<ITEM_DESCRIPTION><![CDATA[' || L_GIVEN_ITEM_DESC || ']]></ITEM_DESCRIPTION>'
2604 || '<ITEM_TYPE><![CDATA[' || r_get_ohq.SUPPLIED_ITEM_TYPE || ']]></ITEM_TYPE>'
2605 || '<QUANTITY_AVAILABLE>' || r_get_ohq.SUPPLIED_QUANTITY || '</QUANTITY_AVAILABLE>'
2606 || '<SOURCE_ORGANIZATION><![CDATA[' || L_ORG_NAME || ']]></SOURCE_ORGANIZATION>'
2607 || '<SOURCE_SUBINVENTORY><![CDATA[' || r_get_ohq.SUBINVENTORY_CODE || ']]></SOURCE_SUBINVENTORY>'
2608 || '<SOURCE_TYPE><![CDATA[' || r_get_ohq.SOURCE_TYPE_CODE || ']]></SOURCE_TYPE>'
2609 || '<DISTANCE>' || to_char(r_get_ohq.DISTANCE) || '</DISTANCE>'
2610
2611 || ' </ROW> ';
2612
2613 END LOOP;
2614
2615 l_xml_result := l_xml_result || '</ROWSET>';
2616
2617 p_result := l_xml_result;
2618
2619 CSM_UTIL_PKG.LOG('Leaving GET_PART_AVAIL for Item Name : ' || p_item, g_object_name, FND_LOG.LEVEL_PROCEDURE);
2620
2621 EXCEPTION
2622 WHEN OTHERS THEN
2623 x_return_status := FND_API.G_RET_STS_ERROR;
2624 x_error_message := 'Exception occurred in GET_PART_AVAIL: ' || sqlerrm;
2625 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_EXCEPTION);
2626
2627 END GET_PART_AVAIL;
2628
2629 /*Procedure to get the Part Return Information*/
2630 PROCEDURE GET_PART_RETURN_INFO
2631 ( p_item IN VARCHAR2,
2632 p_result OUT nocopy CLOB,
2633 x_return_status OUT nocopy VARCHAR2,
2634 x_error_message OUT nocopy VARCHAR2
2635 )
2636
2637 AS
2638
2639 CURSOR c_get_item(p_item VARCHAR2, p_org_id NUMBER)
2640 IS
2641 SELECT B.INVENTORY_ITEM_ID,B.SEGMENT1,TL.DESCRIPTION
2642 FROM
2643 MTL_SYSTEM_ITEMS_B B,
2644 MTL_SYSTEM_ITEMS_TL TL
2645 WHERE B.segment1 = p_item
2646 AND B.ORGANIZATION_ID = p_ORG_ID
2647 AND B.INVENTORY_ITEM_ID = TL.INVENTORY_ITEM_ID
2648 AND B.ORGANIZATION_ID = TL.ORGANIZATION_ID
2649 AND TL.LANGUAGE = USERENV('LANG');
2650
2651 CURSOR C_GET_ORG(p_ORG_ID NUMBER)
2652 IS
2653 SELECT NAME
2654 FROM HR_ALL_ORGANIZATION_UNITS_TL
2655 WHERE ORGANIZATION_ID =p_org_id
2656 AND LANGUAGE = USERENV('LANG');
2657
2658 CURSOR c_get_item_name(p_item_id NUMBER, p_org_id NUMBER)
2659 IS
2660 SELECT B.SEGMENT1,TL.DESCRIPTION
2661 FROM
2662 MTL_SYSTEM_ITEMS_B B,
2663 MTL_SYSTEM_ITEMS_TL TL
2664 WHERE B.INVENTORY_ITEM_ID = p_item_id
2665 AND B.ORGANIZATION_ID = p_ORG_ID
2666 AND B.INVENTORY_ITEM_ID = TL.INVENTORY_ITEM_ID
2667 AND B.ORGANIZATION_ID = TL.ORGANIZATION_ID
2668 AND TL.LANGUAGE = USERENV('LANG');
2669
2670 CURSOR c_get_sub_inv(p_resource_id NUMBER, p_org_id NUMBER)
2671 IS
2672 SELECT ila.SUBINVENTORY_CODE , csi.condition_type
2673 FROM CSP_INV_LOC_ASSIGNMENTS ila,
2674 csp_sec_inventories csi
2675 WHERE ila.RESOURCE_ID = p_resource_id
2676 AND ila.RESOURCE_TYPE = 'RS_EMPLOYEE'
2677 AND ila.ORGANIZATION_ID = p_org_id
2678 AND csi.secondary_inventory_name = ila.subinventory_code
2679 AND csi.organization_id = ila.organization_id;
2680
2681
2682 CURSOR c_get_ohq
2683 IS
2684 SELECT
2685 SUPPLIED_ITEM_ID,
2686 SUPPLIED_ITEM_TYPE,
2687 SUPPLIED_QUANTITY,
2688 ORGANIZATION_ID,
2689 SUBINVENTORY_CODE,
2690 SOURCE_TYPE_CODE,
2691 DISTANCE
2692 FROM
2693 csp_available_parts_temp;
2694
2695 l_instance_id NUMBER;
2696 l_cont_serive_id NUMBER;
2697 l_msg_count NUMBER;
2698 l_msg_data VARCHAR2(4000);
2699
2700 L_XML_RESULT CLOB;
2701 L_PROFILE_VALUE NUMBER;
2702 L_GIVEN_ITEM_ID NUMBER;
2703 L_GIVEN_ITEM_NAME VARCHAR2(200);
2704 L_GIVEN_ITEM_DESC VARCHAR2(4000);
2705 L_ORG_NAME VARCHAR2(240);
2706 L_RETURN_STATUS VARCHAR2(100);
2707 l_required_parts_tbl csp_part_search_pvt.required_parts_tbl;
2708 L_EXCESS_REC CSP_EXCESS_LISTS_PKG.EXCESS_RECORD_TYPE;
2709 L_RETURN_REC CSP_EXCESS_LISTS_PKG.EXCESS_RECORD_TYPE;
2710 l_count NUMBER;
2711 L_RETURN_ORG_NAME VARCHAR2(240);
2712 l_resource_id NUMBER;
2713 L_CONDITION_CODE VARCHAR2(1000);
2714
2715 BEGIN
2716 CSM_UTIL_PKG.LOG('Entering GET_PART_RETURN_INFO for Item : ' || p_item, g_object_name, FND_LOG.LEVEL_PROCEDURE);
2717
2718 l_profile_value := TO_NUMBER(fnd_profile.value_specific('CS_INV_VALIDATION_ORG',G_USER_ID,NULL,NULL));
2719 l_resource_id := GET_RESOURCE_ID( G_USER_ID);
2720 --get org name for display
2721 OPEN C_GET_ORG(L_PROFILE_VALUE);
2722 FETCH C_GET_ORG INTO L_ORG_NAME;
2723 CLOSE C_GET_ORG;
2724
2725 --Get whether given item is valid or not
2726 OPEN C_GET_ITEM(P_ITEM, L_PROFILE_VALUE);
2727 FETCH C_GET_ITEM INTO L_GIVEN_ITEM_ID,L_GIVEN_ITEM_NAME,L_GIVEN_ITEM_DESC;
2728 CLOSE C_GET_ITEM;
2729
2730 IF L_GIVEN_ITEM_ID IS NULL THEN
2731 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2732 x_error_message := 'The given Item ' ||P_ITEM || 'is not present in the Organization ' || l_profile_value;
2733 CSM_UTIL_PKG.LOG( x_error_message,g_object_name, FND_LOG.LEVEL_ERROR);
2734 RETURN ;
2735 END IF;
2736
2737 L_XML_RESULT := '<?xml version="1.0"?>'
2738 || '<ROWSET>';
2739
2740
2741 FOR r_get_sub_inv in c_get_sub_inv(l_resource_id, L_PROFILE_VALUE) LOOP
2742
2743 IF r_get_sub_inv.CONDITION_TYPE ='G' THEN
2744 L_CONDITION_CODE := 'Excess';
2745 ELSE
2746 L_CONDITION_CODE := 'Defective';
2747 END IF;
2748 --Set the item parameter
2749 L_EXCESS_REC.EXCESS_LINE_ID := NULL;
2750 L_EXCESS_REC.ORGANIZATION_ID := L_PROFILE_VALUE;
2751 L_EXCESS_REC.INVENTORY_ITEM_ID := L_GIVEN_ITEM_ID;
2752 L_EXCESS_REC.EXCESS_QUANTITY := 1;
2753 L_EXCESS_REC.CONDITION_CODE := r_get_sub_inv.CONDITION_TYPE;
2754 L_EXCESS_REC.CREATED_BY := 1;
2755 L_EXCESS_REC.CREATION_DATE := SYSDATE;
2756 L_EXCESS_REC.LAST_UPDATED_BY := 1;
2757 L_EXCESS_REC.LAST_UPDATE_DATE := SYSDATE;
2758 L_EXCESS_REC.LAST_UPDATE_LOGIN := 1;
2759 L_EXCESS_REC.SUBINVENTORY_CODE := r_get_sub_inv.SUBINVENTORY_CODE;
2760 L_EXCESS_REC.RETURNED_QUANTITY := NULL;
2761 L_EXCESS_REC.CURRENT_RETURN_QTY := NULL;
2762 L_EXCESS_REC.REQUISITION_LINE_ID := NULL;
2763 L_EXCESS_REC.EXCESS_STATUS := NULL;
2764 L_EXCESS_REC.REASON_CODE := NULL;
2765 L_EXCESS_REC.RETURN_ORGANIZATION_ID := NULL;
2766 L_EXCESS_REC.RETURN_SUBINVENTORY_NAME:= NULL;
2767
2768 --get the return rules
2769 CSP_EXCESS_PARTS_PVT.populate_excess_list (
2770 p_excess_part => L_EXCESS_REC,
2771 p_is_insert_record => 'N'
2772 );
2773
2774 IF L_EXCESS_REC.RETURN_ORGANIZATION_ID IS NOT NULL THEN
2775 --get org name for display
2776 OPEN C_GET_ORG(L_EXCESS_REC.RETURN_ORGANIZATION_ID);
2777 FETCH C_GET_ORG INTO L_RETURN_ORG_NAME;
2778 CLOSE C_GET_ORG;
2779
2780 L_XML_RESULT := L_XML_RESULT || ' <ROW>'
2781 || '<ITEM><![CDATA[' || L_GIVEN_ITEM_NAME || ']]></ITEM>'
2782 || '<ITEM_DESCRIPTION><![CDATA[' || L_GIVEN_ITEM_DESC || ']]></ITEM_DESCRIPTION>'
2783 || '<SOURCE_ORGANIZATION><![CDATA[' || L_ORG_NAME || ']]></SOURCE_ORGANIZATION>'
2784 || '<SOURCE_SUBINVENTORY><![CDATA[' || L_EXCESS_REC.SUBINVENTORY_CODE || ']]></SOURCE_SUBINVENTORY>'
2785 || '<RETURN_ORGANIZATION><![CDATA[' || L_RETURN_ORG_NAME || ']]></RETURN_ORGANIZATION>'
2786 || '<RETURN_SUBINVENTORY><![CDATA[' || L_EXCESS_REC.RETURN_SUBINVENTORY_NAME || ']]></RETURN_SUBINVENTORY>'
2787 || '<RETURN_TYPE><![CDATA[' || L_CONDITION_CODE || ']]></RETURN_TYPE>'
2788 || ' </ROW> ';
2789 end if;
2790
2791 --For part return info of Defect on Arrival
2792 IF R_GET_SUB_INV.CONDITION_TYPE = 'B' then
2793 L_CONDITION_CODE := 'Defect On Arrival';
2794 L_EXCESS_REC.CONDITION_CODE := 'A';
2795
2796 --get the return org
2797 CSP_EXCESS_PARTS_PVT.POPULATE_EXCESS_LIST (
2798 p_excess_part => L_EXCESS_REC,
2799 p_is_insert_record => 'N'
2800 );
2801
2802 if L_EXCESS_REC.RETURN_ORGANIZATION_ID is not null then
2803
2804 --get org name for display
2805 OPEN C_GET_ORG(L_EXCESS_REC.RETURN_ORGANIZATION_ID);
2806 FETCH C_GET_ORG INTO L_RETURN_ORG_NAME;
2807 CLOSE C_GET_ORG;
2808
2809 L_XML_RESULT := L_XML_RESULT || ' <ROW>'
2810 || '<ITEM><![CDATA[' || L_GIVEN_ITEM_NAME || ']]></ITEM>'
2811 || '<ITEM_DESCRIPTION><![CDATA[' || L_GIVEN_ITEM_DESC || ']]></ITEM_DESCRIPTION>'
2812 || '<SOURCE_ORGANIZATION><![CDATA[' || L_ORG_NAME || ']]></SOURCE_ORGANIZATION>'
2813 || '<SOURCE_SUBINVENTORY><![CDATA[' || L_EXCESS_REC.SUBINVENTORY_CODE || ']]></SOURCE_SUBINVENTORY>'
2814 || '<RETURN_ORGANIZATION><![CDATA[' || L_RETURN_ORG_NAME || ']]></RETURN_ORGANIZATION>'
2815 || '<RETURN_SUBINVENTORY><![CDATA[' || L_EXCESS_REC.RETURN_SUBINVENTORY_NAME || ']]></RETURN_SUBINVENTORY>'
2816 || '<RETURN_TYPE><![CDATA[' || L_CONDITION_CODE || ']]></RETURN_TYPE>'
2817 || ' </ROW> ';
2818
2819 END IF;
2820 END IF;
2821
2822 END LOOP;
2823
2824 l_xml_result := l_xml_result || '</ROWSET>';
2825 p_result := l_xml_result;
2826
2827 CSM_UTIL_PKG.LOG('Leaving GET_PART_RETURN_INFO for Item Name : ' || p_item, g_object_name, FND_LOG.LEVEL_PROCEDURE);
2828
2829 EXCEPTION
2830 WHEN OTHERS THEN
2831 x_return_status := FND_API.G_RET_STS_ERROR;
2832 x_error_message := 'Exception occurred in GET_PART_RETURN_INFO: ' || sqlerrm;
2833 CSM_UTIL_PKG.LOG(x_error_message, g_object_name, FND_LOG.LEVEL_EXCEPTION);
2834
2835 END GET_PART_RETURN_INFO;
2836
2837
2838 END CSM_EMAIL_QUERY_PKG;
2839