[Home] [Help]
PACKAGE BODY: APPS.CSF_ALERTS_PUB
Source
1 PACKAGE BODY csf_alerts_pub AS
2 /*$Header: csfAlertb.pls 120.8 2011/01/04 19:29:52 hhaugeru ship $*/
3
4 -- This function will do basic filteration as per the bussiness conditions
5 -- also based on the event type it will call another function
6 -- Main entry point for the CSF wireless alerts
7 -- What to check?
8 -- 1. Assignment should have task attached to it with Schedule Start/End date
9 -- 2. Schedule start date should be greater than sysdate
10 -- 3. Task should be a type of 'Dispatch' and schedulable flag = 'Y' (only for FS tasks)
11 -- 4. Check for Task priority (check against profile value)
12 -- 5. Task assignment status should be of 'CSF: Default Assigned task status' profile value
13 -- at the application level
14
15 function checkForAlerts (p_subscription_guid in raw,
16 p_event in out nocopy WF_EVENT_T) return varchar2 is
17
18 l_event_name varchar2(100);
19 l_event_key varchar2(100);
20 l_task_asgn_id number;
21 l_task_id number;
22 l_scheduled_start_date date;
23 l_scheduled_end_date date;
24 l_resource_id number;
25 l_resource_type_code varchar2(20);
26 -- l_shifts CSF_RESOURCE_PUB.shift_tbl_type;
27 X_RETURN_STATUS VARCHAR2(200);
28 X_MSG_COUNT NUMBER;
29 X_MSG_DATA VARCHAR2(200);
30 l_shift_start_date date;
31 l_minutes_before number;
32 l_send_date date;
33 l_wf_parameter_list_t wf_parameter_list_t;
34 l_wf_parameter_list_t_cp wf_parameter_list_t;
35 l_wf_parameter_t wf_parameter_t;
36 l_old_resource number;
37 l_new_resource number;
38 l_old_resource_type varchar2(100);
39 l_new_resource_type varchar2(100);
40 l_new_asgnmnt_status_id number;
41 l_old_asgnmnt_status_id number;
42 l_assigned_status_flag varchar2(10);
43 l_task_audit_id number;
44 l_task_sch_update_check varchar2(10);
45 l_task_priority_update_check varchar2(10);
46 l_priority_test varchar2(10);
47 l_org_assignment_status_id number;
48
49 cursor c_task_assgn_detail (v_task_assgn_id number) is
50 SELECT distinct jtb.task_id,
51 jtb.scheduled_start_date,
52 jtb.scheduled_end_date,
53 jta.resource_id,
54 jta.resource_type_code,
55 jta.assignment_status_id
56 FROM jtf_tasks_b jtb,
57 jtf_task_assignments jta,
58 jtf_task_priorities_vl jp_vl,
59 jtf_task_types_vl jtt_vl
60 WHERE jta.task_assignment_id = v_task_assgn_id
61 and jta.task_id = jtb.task_id
62 and jtb.source_object_type_code = 'SR'
63 and nvl(jtb.scheduled_start_date, sysdate - 1) > sysdate
64 and nvl(jtb.scheduled_end_date, sysdate - 1) > sysdate
65 and jta.assignment_status_id in (
66 select
67 task_status_id
68 from
69 jtf_task_statuses_b
70 where
71 usage = 'TASK'
72 and nvl(assigned_flag, 'N') = 'Y'
73 and nvl(assignment_status_flag, 'N') = 'Y'
74 and sysdate between nvl(start_date_active, sysdate)
75 and nvl(end_date_active, sysdate + 1)
76 )
77 and jtb.task_type_id = jtt_vl.task_type_id
78 and jtt_vl.task_type_id in (
79 select
80 task_type_id
81 from
82 JTF_TASK_TYPES_B
83 where
84 rule = 'DISPATCH'
85 ) -- only dispatch tasks
86 and nvl(jtt_vl.schedule_flag, 'N') = 'Y' -- schedulable tasks
87 and sysdate between nvl(jtt_vl.start_date_active, sysdate)
88 and nvl(jtt_vl.end_date_active, sysdate + 1)
89 and jtb.task_priority_id = jp_vl.task_priority_id
90 and sysdate between nvl(jp_vl.start_date_active, sysdate)
91 and nvl(jp_vl.end_date_active, sysdate + 1)
92 and jp_vl.importance_level <= fnd_profile.VALUE_SPECIFIC('CSF_ALERT_PRIORITY',
93 getUserId(jta.resource_id, jta.resource_type_code),
94 21685,
95 513,
96 null,
97 null);
98
99 cursor c_task_assgn_detail_status (v_task_assgn_id number) is
100 SELECT distinct jtb.task_id,
101 jtb.scheduled_start_date,
102 jtb.scheduled_end_date,
103 jta.resource_id,
104 jta.resource_type_code
105 FROM jtf_tasks_b jtb,
106 jtf_task_assignments jta,
107 jtf_task_priorities_vl jp_vl,
108 jtf_task_types_vl jtt_vl
109 WHERE jta.task_assignment_id = v_task_assgn_id
110 and jta.task_id = jtb.task_id
111 and jtb.source_object_type_code = 'SR'
112 and nvl(jtb.scheduled_start_date, sysdate - 1) > sysdate
113 and nvl(jtb.scheduled_end_date, sysdate - 1) > sysdate
114 and jtb.task_type_id = jtt_vl.task_type_id
115 and jtt_vl.task_type_id in (
116 select
117 task_type_id
118 from
119 JTF_TASK_TYPES_B
120 where
121 rule = 'DISPATCH'
122 ) -- only dispatch tasks
123 and jtt_vl.schedule_flag = 'Y' -- schedulable tasks
124 and sysdate between nvl(jtt_vl.start_date_active, sysdate)
125 and nvl(jtt_vl.end_date_active, sysdate + 1)
126 and jtb.task_priority_id = jp_vl.task_priority_id
127 and sysdate between nvl(jp_vl.start_date_active, sysdate)
128 and nvl(jp_vl.end_date_active, sysdate + 1)
129 and jp_vl.importance_level <= fnd_profile.VALUE_SPECIFIC('CSF_ALERT_PRIORITY',
130 getUserId(jta.resource_id, jta.resource_type_code),
131 21685,
132 513,
133 null,
134 null);
135
136 cursor c_check_task_status_id (v_new_status_id number, v_old_status_id number) is
137 select 'TRUE' from dual where v_old_status_id in (select
138 task_status_id
139 from
140 jtf_task_statuses_b
141 where
142 usage = 'TASK'
143 and nvl(assigned_flag, 'N') <> 'Y'
144 and nvl(assignment_status_flag, 'N') = 'Y'
145 and sysdate between nvl(start_date_active, sysdate)
146 and nvl(end_date_active, sysdate + 1))
147 and v_new_status_id in (select
148 task_status_id
149 from
150 jtf_task_statuses_b
151 where
152 usage = 'TASK'
153 and nvl(assigned_flag, 'N') = 'Y'
154 and nvl(assignment_status_flag, 'N') = 'Y'
155 and sysdate between nvl(start_date_active, sysdate)
156 and nvl(end_date_active, sysdate + 1));
157
158 cursor c_task_detail (v_task_id number, v_resource_id number, v_resource_type_code varchar2) is
159 SELECT jtb.scheduled_start_date,
160 jtb.scheduled_end_date
161 FROM jtf_tasks_b jtb,
162 jtf_task_priorities_vl jp_vl,
163 jtf_task_types_vl jtt_vl
164 WHERE jtb.task_id = v_task_id
165 and nvl(jtb.scheduled_start_date, sysdate - 1) > sysdate
166 and jtb.source_object_type_code = 'SR'
167 and jtb.task_type_id = jtt_vl.task_type_id
168 and jtt_vl.task_type_id in (
169 select
170 task_type_id
171 from
172 JTF_TASK_TYPES_B
173 where
174 rule = 'DISPATCH'
175 ) -- only dispatch tasks
176 and jtt_vl.schedule_flag = 'Y' -- schedulable tasks
177 and sysdate between nvl(jtt_vl.start_date_active, sysdate)
178 and nvl(jtt_vl.end_date_active, sysdate + 1)
179 and jtb.task_priority_id = jp_vl.task_priority_id
180 and sysdate between nvl(jp_vl.start_date_active, sysdate)
181 and nvl(jp_vl.end_date_active, sysdate + 1)
182 and jp_vl.importance_level <= fnd_profile.VALUE_SPECIFIC('CSF_ALERT_PRIORITY',
183 getUserId(v_resource_id, v_resource_type_code),
184 21685,
185 513,
186 null,
187 null);
188 -- should take from profile here it is Medium
189
190 cursor c_task_update_check (v_task_id number, v_task_audit_id number) is
191 select
192 (select
193 'TRUE'
194 from
195 jtf_task_audits_b jtab,
196 jtf_tasks_b jtb
197 where
198 jtb.task_id = v_task_id
199 and jtb.source_object_type_code = 'SR'
200 and jtb.task_id = jtab.task_id
201 and jtab.task_audit_id = v_task_audit_id
202 and (jtab.new_scheduled_start_date <> jtab.old_scheduled_start_date
203 or jtab.new_scheduled_end_date <> jtab.old_scheduled_end_date)) as is_schedule_dates,
204 (select
205 'TRUE'
206 from
207 jtf_task_audits_b jtab,
208 jtf_tasks_b jtb
209 where
210 jtb.task_id = v_task_id
211 and jtb.source_object_type_code = 'SR'
212 and jtb.task_id = jtab.task_id
213 and jtab.task_audit_id = v_task_audit_id
214 and jtab.new_task_priority_id <> jtab.old_task_priority_id) as is_priority
215 from dual;
216
217 cursor c_get_all_assignments (v_task_id number) is
218 select
219 task_assignment_Id
220 from
221 jtf_task_assignments
222 where task_id = v_task_id;
223
224 cursor c_priority_test (v_task_id number,
225 v_task_audit_id number,
226 v_resource_id number,
227 v_resource_type varchar2) is
228 select
229 'TRUE'
230 from
231 jtf_task_priorities_vl jp_vl1,
232 jtf_task_priorities_vl jp_vl2,
233 jtf_task_audits_b jtab
234 where
235 jtab.task_id = v_task_id
236 and jtab.task_audit_id = v_task_audit_id
237 and jp_vl1.task_priority_id = jtab.new_task_priority_id
238 and jp_vl1.importance_level <= fnd_profile.VALUE_SPECIFIC('CSF_ALERT_PRIORITY',
239 getUserId(v_resource_id, v_resource_type),
240 21685,
241 513,
242 null,
243 null)
244 and jp_vl2.task_priority_id = jtab.old_task_priority_id
245 and jp_vl2.importance_level > fnd_profile.VALUE_SPECIFIC('CSF_ALERT_PRIORITY',
246 getUserId(v_resource_id, v_resource_type),
247 21685,
248 513,
249 null,
250 null);
251 begin
252
253 l_event_name := p_event.getEventName();
254 l_task_asgn_id := p_event.GetValueForParameter('TASK_ASSIGNMENT_ID');
255 l_wf_parameter_list_t := p_event.getParameterList();
256
257 -- create task assignment event
258 if (l_event_name = 'oracle.apps.jtf.cac.task.createTaskAssignment') then -- event type create
259
260 open c_task_assgn_detail(l_task_asgn_id);
261 fetch c_task_assgn_detail into l_task_id,
262 l_scheduled_start_date,
263 l_scheduled_end_date,
264 l_resource_id,
265 l_resource_type_code,
266 l_org_assignment_status_id;
267 close c_task_assgn_detail;
268
269 if l_task_id is null then
270 return 'SUCCESS'; -- nothing to do now :)
271 end if;
272
273 -- calculate SendDate
274 l_send_date := getSendDate(l_resource_id,
275 l_resource_type_code,
276 l_scheduled_start_date,
277 l_scheduled_end_date);
278
279 if checkAlertsEnabled(l_resource_id, l_resource_type_code) then
280
281 l_wf_parameter_t := wf_parameter_t('CSF_EVENT_TYPE', 'CREATE');
282 l_wf_parameter_list_t.EXTEND;
283 l_wf_parameter_list_t(l_wf_parameter_list_t.count()) := l_wf_parameter_t;
284
285 l_wf_parameter_t := wf_parameter_t('ORG_TASK_ASSGN_STS_ID', to_char(l_org_assignment_status_id));
286 l_wf_parameter_list_t.EXTEND;
287 l_wf_parameter_list_t(l_wf_parameter_list_t.count()) := l_wf_parameter_t;
288
289 l_event_key := getItemKey('oracle.apps.csf.createTaskAssignment',
290 l_resource_id,
291 l_resource_type_code,
292 l_task_asgn_id,
293 p_event.getEventKey());
294
295 wf_event.raise(p_event_name => 'oracle.apps.csf.alerts.sendNotification',
296 p_event_key => l_event_key,
297 p_parameters => l_wf_parameter_list_t,
298 p_send_date => l_send_date);
299
300 end if;
301
302 -- delete assignment event
303 elsif (l_event_name = 'oracle.apps.jtf.cac.task.deleteTaskAssignment') then -- event type delete
304
305 l_task_id := p_event.GetValueForParameter('TASK_ID');
306 l_resource_id := p_event.GetValueForParameter('RESOURCE_ID');
307 l_resource_type_code := p_event.GetValueForParameter('RESOURCE_TYPE_CODE');
308
309 open c_task_detail(l_task_id, l_resource_id, l_resource_type_code);
310 fetch c_task_detail into l_scheduled_start_date, l_scheduled_end_date;
311 close c_task_detail;
312
313 if l_task_id is null then
314 return 'SUCCESS'; -- nothing to do now :)
315 end if;
316
317 -- calculate SendDate
318 l_send_date := getSendDate(l_resource_id,
319 l_resource_type_code,
320 l_scheduled_start_date,
321 l_scheduled_end_date);
322
323 if checkAlertsEnabled(l_resource_id, l_resource_type_code) then
324
325 l_wf_parameter_t := wf_parameter_t('CSF_EVENT_TYPE', 'DELETE');
326 l_wf_parameter_list_t.EXTEND;
327 l_wf_parameter_list_t(l_wf_parameter_list_t.count()) := l_wf_parameter_t;
328
329 l_event_key := getItemKey('oracle.apps.csf.deleteTaskAssignment',
330 l_resource_id,
331 l_resource_type_code,
332 l_task_asgn_id,
333 p_event.getEventKey());
334
335 wf_event.raise(p_event_name => 'oracle.apps.csf.alerts.sendNotification',
336 p_event_key => l_event_key,
337 p_parameters => l_wf_parameter_list_t,
338 p_send_date => l_send_date);
339
340 end if;
341
342 -- update assignee event
343 elsif (l_event_name = 'oracle.apps.jtf.cac.task.updateTaskAssignment') then -- event type update assignment
344
345 l_old_resource := p_event.GetValueForParameter('OLD_RESOURCE_ID');
346 l_new_resource := p_event.GetValueForParameter('NEW_RESOURCE_ID');
347 l_old_resource_type := p_event.GetValueForParameter('OLD_RESOURCE_TYPE_CODE');
348 l_new_resource_type := p_event.GetValueForParameter('NEW_RESOURCE_TYPE_CODE');
349
350 l_new_asgnmnt_status_id := p_event.GetValueForParameter('NEW_ASSIGNMENT_STATUS_ID');
351 l_old_asgnmnt_status_id := p_event.GetValueForParameter('OLD_ASSIGNMENT_STATUS_ID');
352
353 -- Assignee change case
354 if l_old_resource is not null and l_new_resource is not null then -- resource change
355
356 open c_task_assgn_detail(l_task_asgn_id);
357 fetch c_task_assgn_detail into l_task_id,
358 l_scheduled_start_date,
359 l_scheduled_end_date,
360 l_resource_id,
361 l_resource_type_code,
362 l_org_assignment_status_id;
363 close c_task_assgn_detail;
364
365 if l_task_id is null then
366 return 'SUCCESS'; -- nothing to do now :)
367 end if;
368
369 -- calculate SendDate
370 l_send_date := getSendDate(l_resource_id,
371 l_resource_type_code,
372 l_scheduled_start_date,
373 l_scheduled_end_date);
374
375 -- create notification to new resource
376 if checkAlertsEnabled(l_new_resource, l_new_resource_type) then -- alerts enables?
377
378 l_wf_parameter_list_t_cp := l_wf_parameter_list_t;
379 l_wf_parameter_t := wf_parameter_t('CSF_EVENT_TYPE', 'CREATE');
380 l_wf_parameter_list_t.EXTEND;
381 l_wf_parameter_list_t(l_wf_parameter_list_t.count()) := l_wf_parameter_t;
382
383 l_wf_parameter_t := wf_parameter_t('ORG_TASK_ASSGN_STS_ID', to_char(l_org_assignment_status_id));
384 l_wf_parameter_list_t.EXTEND;
385 l_wf_parameter_list_t(l_wf_parameter_list_t.count()) := l_wf_parameter_t;
386
387
388 l_event_key := getItemKey('oracle.apps.csf.createTaskAssignment',
389 l_new_resource,
390 l_new_resource_type,
391 l_task_asgn_id,
392 p_event.getEventKey());
393
394 wf_event.raise(p_event_name => 'oracle.apps.csf.alerts.sendNotification',
395 p_event_key => l_event_key,
396 p_parameters => l_wf_parameter_list_t,
397 p_send_date => l_send_date
398 );
399
400 end if; -- alerts enabled?
401
402 -- delete alert to old resource
403 if checkAlertsEnabled(l_old_resource, l_old_resource_type) then -- alerts enabled?
404
405 l_wf_parameter_t := wf_parameter_t('CSF_EVENT_TYPE', 'DELETE_FOR_UPDATE');
406 l_wf_parameter_list_t := l_wf_parameter_list_t_cp;
407 l_wf_parameter_list_t.EXTEND;
408 l_wf_parameter_list_t(l_wf_parameter_list_t.count()) := l_wf_parameter_t;
409
410 l_event_key := getItemKey('oracle.apps.csf.deleteTaskAssignment',
411 l_old_resource,
412 l_old_resource_type,
413 l_task_asgn_id,
414 p_event.getEventKey());
415
416 wf_event.raise(p_event_name => 'oracle.apps.csf.alerts.sendNotification',
417 p_event_key => l_event_key,
418 p_parameters => l_wf_parameter_list_t,
419 p_send_date => l_send_date);
420
421 end if; -- alerts enabled?
422
423 -- Status change case
424 elsif l_new_asgnmnt_status_id is not null and l_old_asgnmnt_status_id is not null then -- assignment status change
425
426 -- check for other conditions without status change
427 open c_task_assgn_detail_status(l_task_asgn_id);
428 fetch c_task_assgn_detail_status into l_task_id,
429 l_scheduled_start_date,
430 l_scheduled_end_date,
431 l_resource_id,
432 l_resource_type_code;
433 close c_task_assgn_detail_status;
434
435 if l_task_id is null then
436 return 'SUCCESS'; -- nothing to do now :)
437 end if;
438
439 if l_new_asgnmnt_status_id = fnd_profile.VALUE_SPECIFIC('CSF_DEFAULT_TASK_CANCELLED_STATUS',
440 getUserId(l_resource_id, l_resource_type_code),
441 21685,
442 513,
443 null,
444 null)
445 and checkAlertsEnabled(l_resource_id, l_resource_type_code)
446 then -- is it cancel status
447
448 -- calculate SendDate
449 l_send_date := getSendDate(l_resource_id,
450 l_resource_type_code,
451 l_scheduled_start_date,
452 l_scheduled_end_date);
453
454 -- delete case
455 l_wf_parameter_t := wf_parameter_t('CSF_EVENT_TYPE', 'DELETE');
456 l_wf_parameter_list_t.EXTEND;
457 l_wf_parameter_list_t(l_wf_parameter_list_t.count()) := l_wf_parameter_t;
458
459 l_event_key := getItemKey('oracle.apps.csf.deleteTaskAssignment',
460 l_resource_id,
461 l_resource_type_code,
462 l_task_asgn_id,
463 p_event.getEventKey());
464
465 wf_event.raise(p_event_name => 'oracle.apps.csf.alerts.sendNotification',
466 p_event_key => l_event_key,
467 p_parameters => l_wf_parameter_list_t,
468 p_send_date => l_send_date);
469
470 else -- not a cancel status
471
472 -- calculate SendDate
473 l_send_date := getSendDate(l_resource_id,
474 l_resource_type_code,
475 l_scheduled_start_date,
476 l_scheduled_end_date);
477
478 if checkAlertsEnabled(l_resource_id, l_resource_type_code) then -- alerts enabled
479 -- check for status non-assigned to assigned
480 l_assigned_status_flag := 'FALSE';
481
482 open c_check_task_status_id(l_new_asgnmnt_status_id, l_old_asgnmnt_status_id);
483 fetch c_check_task_status_id into l_assigned_status_flag;
484 close c_check_task_status_id;
485
486 if l_assigned_status_flag = 'TRUE' then -- new assignment
487 -- status from non-assigned to assigned
488
489 l_wf_parameter_t := wf_parameter_t('CSF_EVENT_TYPE', 'CREATE');
490 l_wf_parameter_list_t.EXTEND;
491 l_wf_parameter_list_t(l_wf_parameter_list_t.count()) := l_wf_parameter_t;
492
493 l_wf_parameter_t := wf_parameter_t('ORG_TASK_ASSGN_STS_ID', to_char(l_new_asgnmnt_status_id));
494 l_wf_parameter_list_t.EXTEND;
495 l_wf_parameter_list_t(l_wf_parameter_list_t.count()) := l_wf_parameter_t;
496
497 l_event_key := getItemKey('oracle.apps.csf.createTaskAssignment',
498 l_resource_id,
499 l_resource_type_code,
500 l_task_asgn_id,
501 p_event.getEventKey());
502
503 wf_event.raise(p_event_name => 'oracle.apps.csf.alerts.sendNotification',
504 p_event_key => l_event_key,
505 p_parameters => l_wf_parameter_list_t,
506 p_send_date => l_send_date
507 );
508
509 end if; -- new assignemnt over
510
511 end if; -- alerts enabled? over
512
513 end if; -- not a cancel status over
514
515 end if; -- status change over
516
517 elsif (l_event_name = 'oracle.apps.jtf.cac.task.updateTask') then
518
519 l_task_id := p_event.GetValueForParameter('TASK_ID');
520 l_task_audit_id := p_event.GetValueForParameter('TASK_AUDIT_ID');
521
522 open c_task_update_check (l_task_id, l_task_audit_id);
523 fetch c_task_update_check into l_task_sch_update_check, l_task_priority_update_check;
524 close c_task_update_check;
525
526 if l_task_sch_update_check = 'TRUE' or l_task_priority_update_check = 'TRUE' then
527
528 -- fetch all the task assignemnts and resources
529 -- loop for each assignment and check other conditions
530 open c_get_all_assignments(l_task_id);
531 loop
532 fetch c_get_all_assignments into l_task_asgn_id;
533 exit when c_get_all_assignments%notfound;
534
535 l_task_id := null;
536 open c_task_assgn_detail(l_task_asgn_id);
537 fetch c_task_assgn_detail into l_task_id,
538 l_scheduled_start_date,
539 l_scheduled_end_date,
540 l_resource_id,
541 l_resource_type_code,
542 l_org_assignment_status_id;
543 close c_task_assgn_detail;
544
545 if l_task_id is null then
546 return 'SUCCESS'; -- nothing to do now :)
547 end if;
548
549 -- calculate SendDate
550 l_send_date := getSendDate(l_resource_id,
551 l_resource_type_code,
552 l_scheduled_start_date,
553 l_scheduled_end_date);
554
555 -- priority change
556 if l_task_priority_update_check = 'TRUE' then
557
558 -- check if new priority is higher than the profile and
559 -- old priority should be less than
560
561 open c_priority_test(l_task_id, l_task_audit_id, l_resource_id, l_resource_type_code);
562 fetch c_priority_test into l_priority_test;
563 close c_priority_test;
564
565 if l_priority_test = 'TRUE'
566 and checkAlertsEnabled(l_resource_id, l_resource_type_code) then -- create assignment notification
567
568 l_wf_parameter_t := wf_parameter_t('CSF_EVENT_TYPE', 'CREATE');
569 l_wf_parameter_list_t.EXTEND;
570 l_wf_parameter_list_t(l_wf_parameter_list_t.count()) := l_wf_parameter_t;
571
572 l_wf_parameter_t := wf_parameter_t('TASK_ASSIGNMENT_ID', to_char(l_task_asgn_id));
573 l_wf_parameter_list_t.EXTEND;
574 l_wf_parameter_list_t(l_wf_parameter_list_t.count()) := l_wf_parameter_t;
575
576 l_wf_parameter_t := wf_parameter_t('ORG_TASK_ASSGN_STS_ID', to_char(l_org_assignment_status_id));
577 l_wf_parameter_list_t.EXTEND;
578 l_wf_parameter_list_t(l_wf_parameter_list_t.count()) := l_wf_parameter_t;
579
580 l_event_key := getItemKey('oracle.apps.csf.createTaskAssignment',
581 l_resource_id,
582 l_resource_type_code,
583 l_task_asgn_id,
584 p_event.getEventKey());
585
586 wf_event.raise(p_event_name => 'oracle.apps.csf.alerts.sendNotification',
587 p_event_key => l_event_key,
588 p_parameters => l_wf_parameter_list_t,
589 p_send_date => l_send_date
590 );
591
592 end if; -- end of create assignment notification
593
594 elsif l_task_sch_update_check = 'TRUE' then
595
596 -- raise and sch_dates update alert
597 if checkAlertsEnabled(l_resource_id, l_resource_type_code) then
598
599 l_wf_parameter_t := wf_parameter_t('CSF_EVENT_TYPE', 'SCH_UPDATE');
600 l_wf_parameter_list_t.EXTEND;
601 l_wf_parameter_list_t(l_wf_parameter_list_t.count()) := l_wf_parameter_t;
602
603 l_wf_parameter_t := wf_parameter_t('TASK_ASSIGNMENT_ID', to_char(l_task_asgn_id));
604 l_wf_parameter_list_t.EXTEND;
605 l_wf_parameter_list_t(l_wf_parameter_list_t.count()) := l_wf_parameter_t;
606
607 l_event_key := getItemKey('oracle.apps.csf.updateScheduleDates',
608 l_resource_id,
609 l_resource_type_code,
610 l_task_asgn_id,
611 p_event.getEventKey());
612
613 wf_event.raise(p_event_name => 'oracle.apps.csf.alerts.sendNotification',
614 p_event_key => l_event_key,
615 p_parameters => l_wf_parameter_list_t,
616 p_send_date => l_send_date
617 );
618
619 end if;
620
621 end if; -- end of priority change
622
623 end loop;
624 close c_get_all_assignments;
625
626 end if;
627
628 end if; -- event type update assignment over
629
630 return 'SUCCESS';
631
632 end;
633
634 function sendNotification (p_subscription_guid in raw,
635 p_event in out nocopy WF_EVENT_T) return varchar2 is
636
637 l_msg_subject varchar(1000);
638 l_msg_subject2 varchar(1000);
639
640 l_task_asgn_record task_asgn_record;
641 itemkey varchar2(150);
642 l_task_asgn_id number;
643 l_task_id number;
644 l_task_audit_id number;
645 l_cust_name varchar2(200);
646 l_schedule_start_date varchar2(100);
647 l_csfw_event_type varchar2(25);
648 l_resource_id number;
649 l_resource_type varchar2(150);
650 l_resource varchar2(150);
651 l_time_out1 number;
652 l_time_out2 number;
653 itemtype varchar2(10);
654 l_auto_reject varchar2(10);
655 l_document_id varchar2(100);
656 l_org_assignment_status_id number;
657 l_curr_assignment_status_id number;
658
659 cursor c_resource_from_task_asgn_id (v_task_asgn_id number) is
660 select
661 jr.resource_id,
662 jt.resource_id,
663 jt.resource_type_code,
664 jt.assignment_status_id
665 from
666 jtf_task_assignments jt,
667 jtf_rs_resource_extns jr
668 where
669 jt.task_assignment_id = v_task_asgn_id
670 and jt.resource_id = jr.resource_id
671 and category_type(jt.resource_type_code) = jr.category;
672 begin
673 itemkey := p_event.getEventKey();
674 l_task_asgn_id := p_event.GetValueForParameter('TASK_ASSIGNMENT_ID');
675 l_task_id := p_event.GetValueForParameter('TASK_ID');
676 l_task_audit_id := p_event.GetValueForParameter('TASK_AUDIT_ID');
677 l_csfw_event_type := p_event.GetValueForParameter('CSF_EVENT_TYPE');
678 l_org_assignment_status_id := p_event.GetValueForParameter('ORG_TASK_ASSGN_STS_ID');
679
680 if l_csfw_event_type = 'CREATE' then
681
682 l_csfw_event_type := 'CREATE_EVENT';
683
684 open c_resource_from_task_asgn_id(l_task_asgn_id);
685 fetch c_resource_from_task_asgn_id into l_resource,
686 l_resource_id,
687 l_resource_type,
688 l_curr_assignment_status_id;
689 close c_resource_from_task_asgn_id;
690
691 -- check if task assignment status has changed from the time event was
692 -- generated
693 if l_org_assignment_status_id is not null
694 and l_org_assignment_status_id <> l_curr_assignment_status_id then
695 return 'SUCCESS';
696 end if;
697
698 l_resource := getWFRole(l_resource_id);
699
700 l_task_asgn_record := getTaskDetails(null, l_task_asgn_id, null);
701
702 l_cust_name := l_task_asgn_record.cust_name;
703 l_schedule_start_date := to_char(getClientTime(l_task_asgn_record.sch_st_date,
704 getUserId(l_resource_id, l_resource_type)), 'DD-MON-YYYY HH24:MI');
705
706 fnd_message.set_name('CSF', 'CSF_ALERTS_ASSIGNED_SUB');
707 fnd_message.set_token('CUST_NAME', l_cust_name);
708 fnd_message.set_token('SCH_START_DT', l_schedule_start_date);
709
710 l_msg_subject := fnd_message.get;
711
712 fnd_message.set_name('CSF', 'CSF_ALERTS_REMINDER_SUB');
713 fnd_message.set_token('CUST_NAME', l_cust_name);
714 fnd_message.set_token('SCH_START_DT', l_schedule_start_date);
715
716 l_msg_subject2 := fnd_message.get;
717
718 l_document_id := to_char(l_resource_id) || '-' || l_resource_type || '-' || to_char(l_task_asgn_id);
719
720 elsif l_csfw_event_type = 'DELETE' or l_csfw_event_type = 'DELETE_FOR_UPDATE' then
721
722 if l_csfw_event_type = 'DELETE' then
723
724 l_resource_id := p_event.GetValueForParameter('RESOURCE_ID');
725 l_resource_type := p_event.GetValueForParameter('RESOURCE_TYPE_CODE');
726
727 else
728
729 l_resource_id := p_event.GetValueForParameter('OLD_RESOURCE_ID');
730 l_resource_type := p_event.GetValueForParameter('OLD_RESOURCE_TYPE_CODE');
731
732 end if;
733
734 l_csfw_event_type := 'DELETE_EVENT';
735 l_resource := getWFRole(l_resource_id);
736 l_task_asgn_record := getTaskDetails(l_task_id, null, null);
737
738 l_cust_name := l_task_asgn_record.cust_name;
739 l_schedule_start_date := to_char(getClientTime(l_task_asgn_record.sch_st_date,
740 getUserId(l_resource_id, l_resource_type)), 'DD-MON-YYYY HH24:MI');
741
742 fnd_message.set_name('CSF', 'CSF_ALERTS_CANCEL_SUB');
743 fnd_message.set_token('CUST_NAME', l_cust_name);
744 fnd_message.set_token('SCH_START_DT', l_schedule_start_date);
745
746 l_msg_subject := fnd_message.get;
747
748 l_document_id := to_char(l_resource_id) || '-' || l_resource_type || '-' || to_char(l_task_id);
749
750 elsif l_csfw_event_type = 'SCH_UPDATE' then
751
752 l_csfw_event_type := 'SCH_UPDATE_EVENT';
753
754 open c_resource_from_task_asgn_id(l_task_asgn_id);
755 fetch c_resource_from_task_asgn_id into l_resource,
756 l_resource_id,
757 l_resource_type,
758 l_curr_assignment_status_id;
759 close c_resource_from_task_asgn_id;
760
761 l_resource := getWFRole(l_resource_id);
762
763 l_task_asgn_record := getTaskDetails(null, l_task_asgn_id, null);
764 l_cust_name := l_task_asgn_record.cust_name;
765 l_schedule_start_date := to_char(getClientTime(l_task_asgn_record.sch_st_date,
766 getUserId(l_resource_id, l_resource_type)), 'DD-MON-YYYY HH24:MI');
767
768
769 fnd_message.set_name('CSF', 'CSF_ALERTS_RESCHEDULE_SUB');
770 fnd_message.set_token('CUST_NAME', l_cust_name);
771 fnd_message.set_token('SCH_START_DT', l_schedule_start_date);
772
773 l_msg_subject := fnd_message.get;
774
775 l_document_id := to_char(l_resource_id) || '-' || l_resource_type || '-' || to_char(l_task_id) || '-' || to_char(l_task_audit_id);
776
777 else
778 return 'SUCCESS';
779 end if;
780
781 itemtype := 'CSFALERT';
782
783 wf_engine.createprocess(itemtype => itemtype,
784 itemkey => itemkey,
785 process => 'MAIN_PROCESS');
786
787 wf_engine.setItemAttrNumber(itemtype => itemtype,
788 itemkey => itemkey,
789 aname => 'TASK_ASSGN_ID',
790 avalue => l_task_asgn_id);
791
792 wf_engine.setItemAttrNumber(itemtype => itemtype,
793 itemkey => itemkey,
794 aname => 'RES_ID',
795 avalue => l_resource_id);
796
797 wf_engine.setItemAttrText(itemtype => itemtype,
798 itemkey => itemkey,
799 aname => 'RES_TYPE',
800 avalue => l_resource_type);
801
802 wf_engine.setItemAttrText(itemtype => itemtype,
803 itemkey => itemkey,
804 aname => 'EVENT_TYPE',
805 avalue => l_csfw_event_type);
806
807 wf_engine.setItemAttrText(itemtype => itemtype,
808 itemkey => itemkey,
809 aname => 'RESOURCE',
810 avalue => l_resource);
811
812 wf_engine.setItemAttrText(itemtype => itemtype,
813 itemkey => itemkey,
814 aname => 'DOCUMENT_ID',
815 avalue => l_document_id);
816
817 wf_engine.setItemAttrText(itemtype => itemtype,
818 itemkey => itemkey,
819 aname => 'MSG_SUBJECT',
820 avalue => l_msg_subject);
821
822 wf_engine.setItemAttrText(itemtype => itemtype,
823 itemkey => itemkey,
824 aname => 'MSG_SUBJECT2',
825 avalue => l_msg_subject2);
826
827 l_auto_reject := fnd_profile.VALUE_SPECIFIC('CSF_ALERTS_AUTO_REJECT',
828 getUserId(l_resource_id, l_resource_type),
829 21685,
830 513,
831 null,
832 null);
833 if l_auto_reject = 'Y' then
834 l_auto_reject := 'TRUE';
835 else
836 l_auto_reject := 'FALSE';
837 end if;
838
839 wf_engine.setItemAttrText(itemtype => itemtype,
840 itemkey => itemkey,
841 aname => 'AUTO_REJECT_VALUE',
842 avalue => l_auto_reject);
843
844 l_time_out1 := fnd_profile.VALUE_SPECIFIC('CSF_ALERTS_RESPONSE_TIME_MIN',
845 getUserId(l_resource_id, l_resource_type),
846 21685,
847 513,
848 null,
849 null);
850
851 wf_engine.setItemAttrNumber(itemtype => itemtype,
852 itemkey => itemkey,
853 aname => 'TIMEOUT1',
854 avalue => l_time_out1);
855
856 l_time_out2 := fnd_profile.VALUE_SPECIFIC('CSF_ALERTS_REMINDER_TIME_MIN',
857 getUserId(l_resource_id, l_resource_type),
858 21685,
859 513,
860 null,
861 null);
862
863 wf_engine.setItemAttrNumber(itemtype => itemtype,
864 itemkey => itemkey,
865 aname => 'TIMEOUT2',
866 avalue => l_time_out2);
867
868 -- original task assignment status id
869 wf_engine.setItemAttrNumber(itemtype => itemtype,
870 itemkey => itemkey,
871 aname => 'ORG_TSK_ASG_STS_ID',
872 avalue => l_org_assignment_status_id);
873
874 wf_engine.startprocess(itemtype => itemtype,
875 itemkey => itemkey);
876
877 return 'SUCCESS';
878
879 end;
880
881 procedure checkEvent (itemtype in varchar2,
882 itemkey in varchar2,
883 actid in number,
884 funcmode in varchar2,
885 resultout out nocopy varchar2) is
886
887 l_event_type varchar2(20);
888 begin
889 l_event_type := wf_engine.GetItemAttrText(itemtype => itemtype,
890 itemkey => itemkey,
891 aname => 'EVENT_TYPE');
892 resultout := 'COMPLETE:' || l_event_type;
893 end;
894
895 procedure check_again (itemtype in varchar2,
896 itemkey in varchar2,
897 actid in number,
898 funcmode in varchar2,
899 resultout out nocopy varchar2) is
900
901 l_task_assgn_id number;
902 l_task_id number;
903 l_reminder_timeout number;
904 l_resource_id number;
905 l_resource_type varchar2(100);
906 l_org_assignment_status_id number;
907 l_curr_assignment_status_id number;
908
909 cursor c_task_assgn_detail (v_task_assgn_id number) is
910 SELECT distinct jtb.task_id,
911 jta.resource_id,
912 jta.resource_type_code,
913 jta.assignment_status_id
914 FROM jtf_tasks_b jtb,
915 jtf_task_assignments jta,
916 jtf_task_priorities_vl jp_vl,
917 jtf_task_types_vl jtt_vl
918 WHERE jta.task_assignment_id = v_task_assgn_id
919 and jta.task_id = jtb.task_id
920 and nvl(jtb.scheduled_start_date, sysdate - 1) > sysdate
921 and nvl(jtb.scheduled_end_date, sysdate - 1) > sysdate
922 and jta.assignment_status_id in (
923 select
924 task_status_id
925 from
926 jtf_task_statuses_b
927 where
928 usage = 'TASK'
929 and nvl(assigned_flag, 'N') = 'Y'
930 and nvl(assignment_status_flag, 'N') = 'Y'
931 and sysdate between nvl(start_date_active, sysdate)
932 and nvl(end_date_active, sysdate + 1)
933 )
934 and jtb.task_type_id = jtt_vl.task_type_id
935 and jtt_vl.task_type_id in (
936 select
937 task_type_id
938 from
939 JTF_TASK_TYPES_B
940 where
941 rule = 'DISPATCH'
942 ) -- only dispatch tasks
943 and nvl(jtt_vl.schedule_flag, 'N') = 'Y' -- schedulable tasks
944 and sysdate between nvl(jtt_vl.start_date_active, sysdate)
945 and nvl(jtt_vl.end_date_active, sysdate + 1)
946 and jtb.task_priority_id = jp_vl.task_priority_id
947 and sysdate between nvl(jp_vl.start_date_active, sysdate)
948 and nvl(jp_vl.end_date_active, sysdate + 1)
949 and jp_vl.importance_level <= fnd_profile.VALUE_SPECIFIC('CSF_ALERT_PRIORITY',
950 getUserId(jta.resource_id, jta.resource_type_code),
951 21685,
952 513,
953 null,
954 null);
955 begin
956
957 resultout := 'COMPLETE:F';
958
959 l_task_assgn_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
960 itemkey => itemkey,
961 aname => 'TASK_ASSGN_ID');
962
963 l_org_assignment_status_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
964 itemkey => itemkey,
965 aname => 'ORG_TSK_ASG_STS_ID');
966
967
968 open c_task_assgn_detail(l_task_assgn_id);
969 fetch c_task_assgn_detail into l_task_id, l_resource_id, l_resource_type, l_curr_assignment_status_id;
970 close c_task_assgn_detail;
971
972 if l_org_assignment_status_id is null then
973 l_org_assignment_status_id := l_curr_assignment_status_id;
974 end if;
975
976 if l_task_id is not null
977 and l_org_assignment_status_id = l_curr_assignment_status_id then
978 -- check for timeout2 profile
979 -- if it is null then do not send reminder
980
981 l_reminder_timeout := fnd_profile.VALUE_SPECIFIC('CSF_ALERTS_REMINDER_TIME_MIN',
982 getUserId(l_resource_id, l_resource_type),
983 21685,
984 513,
985 null,
986 null);
987
988 if l_reminder_timeout is not null and l_reminder_timeout > 0 then
989 resultout := 'COMPLETE:T';
990 end if;
991
992 end if;
993
994 end;
995
996 procedure check_auto_reject (itemtype in varchar2,
997 itemkey in varchar2,
998 actid in number,
999 funcmode in varchar2,
1000 resultout out nocopy varchar2) is
1001
1002 l_AUTO_REJECT_VALUE varchar2(25);
1003
1004 begin
1005 resultout := 'COMPLETE:F';
1006 l_AUTO_REJECT_VALUE := wf_engine.GetItemAttrText(itemtype => itemtype,
1007 itemkey => itemkey,
1008 aname => 'AUTO_REJECT_VALUE');
1009 if l_AUTO_REJECT_VALUE = 'TRUE' then
1010 -- bug # 5845177
1011 wf_engine.setItemAttrText(itemtype => itemtype,
1012 itemkey => itemkey,
1013 aname => 'IS_AUTO_REJECT',
1014 avalue => 'Y');
1015 resultout := 'COMPLETE:T';
1016 end if;
1017 end;
1018
1019 procedure accept_assgn (itemtype in varchar2,
1020 itemkey in varchar2,
1021 actid in number,
1022 funcmode in varchar2,
1023 resultout out nocopy varchar2) is
1024
1025 l_task_assgn_id number;
1026 l_object_version_number number;
1027
1028 l_tmp_user_id number;
1029 l_tmp_resp_id number;
1030 l_tmp_resp_apps_id number;
1031
1032 l_resource_id number;
1033 l_resource_type varchar2(100);
1034 p_user_id number;
1035
1036 l_return_status varchar2(10);
1037 l_msg_count number;
1038 l_msg_data varchar2(2000);
1039 l_task_object_version_number number;
1040 l_task_status_id number;
1041 --l_task_status_name varchar2(100);
1042
1043 l_org_assignment_status_id number;
1044 l_curr_assignment_status_id number;
1045
1046 cursor c_task_assgn_detail (v_task_assgn_id number) is
1047 SELECT jta.object_version_number, jta.assignment_status_id
1048 FROM
1049 jtf_task_assignments jta
1050 WHERE jta.task_assignment_id = v_task_assgn_id;
1051 begin
1052 l_task_assgn_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1053 itemkey => itemkey,
1054 aname => 'TASK_ASSGN_ID');
1055
1056 l_org_assignment_status_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1057 itemkey => itemkey,
1058 aname => 'ORG_TSK_ASG_STS_ID');
1059
1060 open c_task_assgn_detail(l_task_assgn_id);
1061 fetch c_task_assgn_detail into l_object_version_number, l_curr_assignment_status_id;
1062 close c_task_assgn_detail;
1063
1064 -- check if task assignment status has been changed from the original value
1065 if l_org_assignment_status_id is null then
1066 l_org_assignment_status_id := l_curr_assignment_status_id;
1067 end if;
1068
1069 resultout := 'COMPLETE';
1070
1071 if l_org_assignment_status_id = l_curr_assignment_status_id then
1072
1073 l_resource_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1074 itemkey => itemkey,
1075 aname => 'RES_ID');
1076
1077 l_resource_type := wf_engine.GetItemAttrText(itemtype => itemtype,
1078 itemkey => itemkey,
1079 aname => 'RES_TYPE');
1080
1081 p_user_id := getUserId(l_resource_id, l_resource_type);
1082
1083 if p_user_id is null then
1084 p_user_id := 0;
1085 end if;
1086
1087 -- call API
1088 l_tmp_user_id := fnd_global.USER_ID;
1089 l_tmp_resp_id := fnd_global.RESP_ID;
1090 l_tmp_resp_apps_id := fnd_global.RESP_APPL_ID;
1091
1092 fnd_global.APPS_INITIALIZE(user_id => p_user_id, resp_id => 21685, resp_appl_id => 513);
1093
1094 csf_task_assignments_pub.update_assignment_status(
1095 p_api_version => 1.0,
1096 p_task_assignment_id => l_task_assgn_id,
1097 p_assignment_status_id => fnd_profile.VALUE_SPECIFIC('CSF_DEFAULT_ACCEPTED_STATUS',
1098 getUserId(l_resource_id, l_resource_type),
1099 21685,
1100 513,
1101 null,
1102 null), -- accepted
1103 p_object_version_number => l_object_version_number,
1104 x_return_status => l_return_status,
1105 x_msg_count => l_msg_count,
1106 x_msg_data => l_msg_data,
1107 x_task_object_version_number => l_task_object_version_number,
1108 x_task_status_id => l_task_status_id
1109 );
1110
1111 fnd_global.APPS_INITIALIZE(user_id => l_tmp_user_id, resp_id => l_tmp_resp_id, resp_appl_id => l_tmp_resp_apps_id);
1112
1113 if l_return_status = 'S' then
1114 resultout := 'COMPLETE';
1115 else
1116 resultout := 'ERROR' || ':' || l_msg_data;
1117 end if;
1118
1119 end if; -- check for asgn status chng check
1120 end;
1121
1122 procedure cancel_assgn (itemtype in varchar2,
1123 itemkey in varchar2,
1124 actid in number,
1125 funcmode in varchar2,
1126 resultout out nocopy varchar2) is
1127
1128 l_task_assgn_id number;
1129 l_object_version_number number;
1130
1131 l_tmp_user_id number;
1132 l_tmp_resp_id number;
1133 l_tmp_resp_apps_id number;
1134
1135 l_resource_id number;
1136 l_resource_type varchar2(100);
1137 p_user_id number;
1138
1139 l_return_status varchar2(10);
1140 l_msg_count number;
1141 l_msg_data varchar2(2000);
1142 l_task_object_version_number number;
1143 l_task_status_id number;
1144 --l_task_status_name varchar2(100);
1145
1146 l_org_assignment_status_id number;
1147 l_curr_assignment_status_id number;
1148
1149 cursor c_task_assgn_detail (v_task_assgn_id number) is
1150 SELECT jta.object_version_number, jta.assignment_status_id
1151 FROM
1152 jtf_task_assignments jta
1153 WHERE jta.task_assignment_id = v_task_assgn_id;
1154
1155 -- bug # 5220702
1156 l_auto_reject varchar2(1);
1157 l_task_id number;
1158 l_task_asgn_record task_asgn_record;
1159 l_cust_name varchar2(250);
1160 l_schedule_start_date varchar2(100);
1161 l_msg_subject varchar2(1000);
1162 l_document_id varchar2(250);
1163
1164 cursor c_get_task_id (v_task_asgn_id number) is
1165 select task_id
1166 from jtf_task_assignments
1167 where task_assignment_id = v_task_asgn_id;
1168 begin
1169 l_task_assgn_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1170 itemkey => itemkey,
1171 aname => 'TASK_ASSGN_ID');
1172
1173 l_org_assignment_status_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1174 itemkey => itemkey,
1175 aname => 'ORG_TSK_ASG_STS_ID');
1176
1177 open c_task_assgn_detail(l_task_assgn_id);
1178 fetch c_task_assgn_detail into l_object_version_number, l_curr_assignment_status_id;
1179 close c_task_assgn_detail;
1180
1181 -- check if task assignment status has been changed from the original value
1182
1183 if l_org_assignment_status_id is null then
1184 l_org_assignment_status_id := l_curr_assignment_status_id;
1185 end if;
1186
1187 resultout := 'COMPLETE:F';
1188 if l_org_assignment_status_id = l_curr_assignment_status_id then
1189
1190 l_resource_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1191 itemkey => itemkey,
1192 aname => 'RES_ID');
1193
1194 l_resource_type := wf_engine.GetItemAttrText(itemtype => itemtype,
1195 itemkey => itemkey,
1196 aname => 'RES_TYPE');
1197
1198 p_user_id := getUserId(l_resource_id, l_resource_type);
1199
1200 if p_user_id is null then
1201 p_user_id := 0;
1202 end if;
1203
1204 -- call API
1205 l_tmp_user_id := fnd_global.USER_ID;
1206 l_tmp_resp_id := fnd_global.RESP_ID;
1207 l_tmp_resp_apps_id := fnd_global.RESP_APPL_ID;
1208
1209 fnd_global.APPS_INITIALIZE(user_id => p_user_id, resp_id => 21685, resp_appl_id => 513);
1210
1211 csf_task_assignments_pub.update_assignment_status(
1212 p_api_version => 1.0,
1213 p_task_assignment_id => l_task_assgn_id,
1214 p_assignment_status_id => fnd_profile.VALUE_SPECIFIC('CSF_DEFAULT_REJECTED_STATUS',
1215 getUserId(l_resource_id, l_resource_type),
1216 21685,
1217 513,
1218 null,
1219 null), -- Rejected
1220 p_object_version_number => l_object_version_number,
1221 x_return_status => l_return_status,
1222 x_msg_count => l_msg_count,
1223 x_msg_data => l_msg_data,
1224 x_task_object_version_number => l_task_object_version_number,
1225 x_task_status_id => l_task_status_id
1226 );
1227
1228 fnd_global.APPS_INITIALIZE(user_id => l_tmp_user_id, resp_id => l_tmp_resp_id, resp_appl_id => l_tmp_resp_apps_id);
1229
1230 if l_return_status = 'S' then
1231
1232 -- bug # 5220702
1233 l_auto_reject := wf_engine.GetItemAttrText(itemtype => itemtype,
1234 itemkey => itemkey,
1235 aname => 'IS_AUTO_REJECT');
1236 if l_auto_reject = 'Y' then
1237 -- modify subject and document_id
1238 open c_get_task_id(l_task_assgn_id);
1239 fetch c_get_task_id into l_task_id;
1240 close c_get_task_id;
1241
1242 l_task_asgn_record := getTaskDetails(l_task_id, null, null);
1243
1244 l_cust_name := l_task_asgn_record.cust_name;
1245 l_schedule_start_date := to_char(getClientTime(l_task_asgn_record.sch_st_date,
1246 getUserId(l_resource_id, l_resource_type)), 'DD-MON-YYYY HH24:MI');
1247
1248 fnd_message.set_name('CSF', 'CSF_ALERTS_CANCEL_SUB');
1249 fnd_message.set_token('CUST_NAME', l_cust_name);
1250 fnd_message.set_token('SCH_START_DT', l_schedule_start_date);
1251
1252 l_msg_subject := fnd_message.get;
1253
1254 l_document_id := to_char(l_resource_id) || '-' || l_resource_type || '-' || to_char(l_task_id);
1255
1256 wf_engine.setItemAttrText(itemtype => itemtype,
1257 itemkey => itemkey,
1258 aname => 'DOCUMENT_ID',
1259 avalue => l_document_id);
1260
1261 wf_engine.setItemAttrText(itemtype => itemtype,
1262 itemkey => itemkey,
1263 aname => 'MSG_SUBJECT',
1264 avalue => l_msg_subject);
1265
1266 resultout := 'COMPLETE:T';
1267 else
1268 resultout := 'COMPLETE:F';
1269 end if;
1270 else
1271 resultout := 'ERROR' || ':' || l_msg_data;
1272 end if;
1273
1274 end if; -- check for asgn status chng check
1275
1276 end;
1277
1278 function getSendDate (p_resource_id number,
1279 p_resource_type_code varchar2,
1280 p_scheduled_start_date date,
1281 p_scheduled_end_date date) return date is
1282 l_return_date date;
1283 l_profile_value varchar2(100);
1284 l_minutes_before number;
1285 l_shifts CSF_RESOURCE_PUB.shift_tbl_type;
1286 X_RETURN_STATUS VARCHAR2(200);
1287 X_MSG_COUNT NUMBER;
1288 X_MSG_DATA VARCHAR2(200);
1289 begin
1290 l_return_date := null;
1291 l_profile_value := fnd_profile.value_specific('CSF_ALERT_SEND_PREF',
1292 getUserId(p_resource_id, p_resource_type_code),
1293 21685,
1294 513,
1295 null,
1296 null);
1297
1298 if l_profile_value = 'SHIFT_DAY' then -- During Shift
1299
1300 -- fetch shift start date time for the given schedule dates
1301 CSF_RESOURCE_PUB.GET_RESOURCE_SHIFTS(
1302 P_API_VERSION => 1.0,
1303 P_RESOURCE_ID => p_resource_id,
1304 P_RESOURCE_TYPE => p_resource_type_code,
1305 P_START_DATE => p_scheduled_start_date,
1306 P_END_DATE => p_scheduled_end_date,
1307 X_RETURN_STATUS => X_RETURN_STATUS,
1308 X_MSG_COUNT => X_MSG_COUNT,
1309 X_MSG_DATA => X_MSG_DATA,
1310 X_SHIFTS => l_shifts
1311 );
1312
1313 if (X_RETURN_STATUS = 'S') and (l_shifts is not null) and (l_shifts.count > 0) then
1314 l_return_date := l_shifts(1).start_datetime;
1315
1316 -- if shift start is less than current date time then send immediately
1317 if l_return_date > sysdate then
1318 -- fetch next shift start from today
1319 CSF_RESOURCE_PUB.GET_RESOURCE_SHIFTS(
1320 P_API_VERSION => 1.0,
1321 P_RESOURCE_ID => p_resource_id,
1322 P_RESOURCE_TYPE => p_resource_type_code,
1323 P_START_DATE => trunc(sysdate) + 1,
1324 P_END_DATE => trunc(sysdate) + 2,
1325 X_RETURN_STATUS => X_RETURN_STATUS,
1326 X_MSG_COUNT => X_MSG_COUNT,
1327 X_MSG_DATA => X_MSG_DATA,
1328 X_SHIFTS => l_shifts
1329 );
1330
1331 if (X_RETURN_STATUS = 'S') and (l_shifts is not null) and (l_shifts.count > 0) then
1332 l_return_date := l_shifts(1).start_datetime;
1333
1334 -- fetch profile value for how many minutes before should we send the notification
1335 l_minutes_before := fnd_profile.VALUE_SPECIFIC('CSF_ALERTS_MIN_BEFORE_SHIFT',
1336 getUserId(p_resource_id, p_resource_type_code),
1337 21685,
1338 513,
1339 null,
1340 null);
1341
1342 l_return_date := (l_return_date - l_minutes_before / 1440);
1343
1344 -- for testing purpose
1345 -- l_return_date := sysdate + 20/86400;
1346
1347 end if;
1348
1349 end if;
1350
1351 end if;
1352
1353 elsif l_profile_value = 'SCHEDULE_DAY' then -- Scheduled Day
1354
1355 -- fetch shift start date time for the given schedule dates
1356 CSF_RESOURCE_PUB.GET_RESOURCE_SHIFTS(
1357 P_API_VERSION => 1.0,
1358 P_RESOURCE_ID => p_resource_id,
1359 P_RESOURCE_TYPE => p_resource_type_code,
1360 P_START_DATE => p_scheduled_start_date,
1361 P_END_DATE => p_scheduled_end_date,
1362 X_RETURN_STATUS => X_RETURN_STATUS,
1363 X_MSG_COUNT => X_MSG_COUNT,
1364 X_MSG_DATA => X_MSG_DATA,
1365 X_SHIFTS => l_shifts
1366 );
1367
1368 if (X_RETURN_STATUS = 'S') and (l_shifts is not null) and (l_shifts.count > 0) then
1369 l_return_date := l_shifts(1).start_datetime;
1370
1371 -- fetch profile value for how many minutes before should we send the notification
1372 l_minutes_before := fnd_profile.VALUE_SPECIFIC('CSF_ALERTS_MIN_BEFORE_SHIFT',
1373 getUserId(p_resource_id, p_resource_type_code),
1374 21685,
1375 513,
1376 null,
1377 null);
1378
1379 l_return_date := (l_return_date - l_minutes_before / 1440);
1380
1381 -- for testing purpose
1382 -- l_return_date := sysdate + 20/86400;
1383
1384 -- if shift start is less than current date time then send immediately
1385 if l_return_date <= sysdate then
1386 l_return_date := null;
1387 end if;
1388 end if;
1389
1390 else -- Immediate
1391 l_return_date := null;
1392 end if;
1393
1394 return l_return_date;
1395 end;
1396
1397 function getUserId (p_resource_id number,
1398 p_resource_type varchar2) return number is
1399 l_user_id number;
1400 cursor c_user_id (v_resource_id number, v_category varchar2) is
1401 select user_id from jtf_rs_resource_extns where resource_id = v_resource_id and category = v_category;
1402 begin
1403 l_user_id := 0;
1404
1405 open c_user_id (p_resource_id, category_type(p_resource_type));
1406 fetch c_user_id into l_user_id;
1407 close c_user_id;
1408
1409 return l_user_id;
1410 end;
1411
1412 function getUserName (p_resource_id number,
1413 p_resource_type varchar2) return varchar2 is
1414 l_user_name varchar2(100);
1415 begin
1416 return l_user_name;
1417 end;
1418
1419 FUNCTION category_type ( p_rs_category varchar2 ) return varchar2 is
1420 begin
1421 if p_rs_category = 'RS_EMPLOYEE' then
1422 return 'EMPLOYEE';
1423 elsif p_rs_category = 'RS_PARTNER' then
1424 return 'PARTNER';
1425 elsif p_rs_category = 'RS_SUPPLIER_CONTACT' then
1426 return 'SUPPLIER_CONTACT';
1427 elsif p_rs_category = 'RS_PARTY' then
1428 return 'PARTY';
1429 elsif p_rs_category = 'RS_OTHER' then
1430 return 'OTHER';
1431 else
1432 return null;
1433 end if;
1434 end;
1435
1436 function getItemKey (p_event_type varchar2,
1437 p_resource_id number,
1438 p_resource_type_code varchar2,
1439 p_task_assignment_id varchar2,
1440 p_old_event_id varchar2) return varchar2 is
1441
1442 l_new_item_key varchar2(240);
1443 l_old_event_id varchar2(20);
1444 begin
1445 l_old_event_id := substr(p_old_event_id, (INSTR(p_old_event_id, '-') + 1));
1446 l_new_item_key := '';
1447 l_new_item_key := l_new_item_key || p_event_type
1448 || '-' || to_char(p_resource_id)
1449 || '-' || p_resource_type_code
1450 || '-' || to_char(p_task_assignment_id)
1451 || '-' || l_old_event_id;
1452 return l_new_item_key;
1453 end;
1454
1455 function checkAlertsEnabled(p_resource_id number,
1456 p_resource_type_code varchar2) return boolean is
1457 l_return_value boolean;
1458 begin
1459 l_return_value := false;
1460
1461 if fnd_profile.VALUE_SPECIFIC('CSF_ALERTS_ENABLE',
1462 getUserId(p_resource_id, p_resource_type_code),
1463 21685,
1464 513,
1465 null,
1466 null) = 'Y' then
1467 l_return_value := true;
1468 end if;
1469 return l_return_value;
1470 end;
1471
1472 procedure getAssignedMessage(document_id varchar2,
1473 display_type varchar2,
1474 document in out nocopy varchar2,
1475 document_type in out nocopy varchar2) is
1476 l_resource_id number;
1477 l_resource_type varchar2(100);
1478 l_task_asgn_id number;
1479 l_message varchar2(32000);
1480 l_task_detail task_asgn_record;
1481 l_message_header varchar2(1000);
1482
1483 l_tmp_user_id number;
1484 l_tmp_resp_id number;
1485 l_tmp_resp_apps_id number;
1486 p_user_id number;
1487
1488 -- notes
1489 l_is_notes varchar2(1);
1490 l_notes varchar2(30000);
1491 cursor c_notes (v_task_asgn_id number) is
1492 select
1493 n.notes
1494 from
1495 jtf_notes_vl n,
1496 jtf_task_assignments a
1497 where
1498 n.source_object_code = 'TASK'
1499 and n.source_object_id = a.task_id
1500 and a.task_assignment_id = v_task_asgn_id
1501 union select
1502 n.notes
1503 from
1504 jtf_notes_vl n,
1505 jtf_task_assignments a,
1506 jtf_tasks_b t
1507 where
1508 n.source_object_code = 'SR'
1509 and n.source_object_id = t.source_object_id
1510 and t.task_id = a.task_id
1511 and a.task_assignment_id = v_task_asgn_id;
1512 begin
1513
1514 l_resource_id := to_number(substr(document_id, 1, instr(document_id, '-', 1, 1) - 1));
1515 l_resource_type := substr(document_id, instr(document_id, '-', 1, 1) + 1, instr(document_id, '-', 1, 2) - instr(document_id, '-', 1, 1) - 1);
1516 l_task_asgn_id := to_number(substr(document_id, instr(document_id, '-', 1, 2) + 1));
1517
1518
1519 p_user_id := getUserId(l_resource_id, l_resource_type);
1520
1521
1522 if p_user_id is null then
1523 p_user_id := 0;
1524 end if;
1525
1526 -- call API
1527 l_tmp_user_id := fnd_global.USER_ID;
1528 l_tmp_resp_id := fnd_global.RESP_ID;
1529 l_tmp_resp_apps_id := fnd_global.RESP_APPL_ID;
1530
1531 fnd_global.APPS_INITIALIZE(user_id => p_user_id, resp_id => 21685, resp_appl_id => 513);
1532
1533 l_task_detail := getTaskDetails(null, l_task_asgn_id, null);
1534
1535
1536 fnd_message.set_name('CSF', 'CSF_ALERTS_ASSIGNED_HDR');
1537 l_message_header := fnd_message.get;
1538
1539 if display_type = 'text/html' then
1540
1541 l_message := '<P>';
1542 l_message := l_message || l_message_header || '</P>';
1543 l_message := l_message || '<P><B>' || getPrompt('CSF_ALERTS_TASK_DETAILS') || ':</B></P>';
1544 l_message := l_message || '<P>';
1545 l_message := l_message || '<TABLE cellSpacing=0 cellPadding=0 border=1>';
1546 l_message := l_message || '<TR>';
1547 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_TASK') || '</B></TD>';
1548 l_message := l_message || '<TD>' || l_task_detail.task_number || ' ' || l_task_detail.task_name || '</TD>';
1549 l_message := l_message || '</TR>';
1550 l_message := l_message || '<TR>';
1551 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_DESCRIPTION') || '</B></TD>';
1552 l_message := l_message || '<TD>' || l_task_detail.task_desc || '</TD>';
1553 l_message := l_message || '</TR>';
1554 l_message := l_message || '<TR>';
1555 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_SCHEDULE_START') || '</B></TD>';
1556 l_message := l_message || '<TD>' || to_char(getClientTime(l_task_detail.sch_st_date,
1557 getUserId(l_resource_id, l_resource_type)), 'DD-MON-YYYY HH24:MI')
1558 || '</TD>';
1559 l_message := l_message || '</TR>';
1560 l_message := l_message || '<TR>';
1561 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_SCHEDULE_END') || '</B></TD>';
1562 l_message := l_message || '<TD>' || to_char(getClientTime(l_task_detail.sch_end_date,
1563 getUserId(l_resource_id, l_resource_type)), 'DD-MON-YYYY HH24:MI')
1564 || '</TD>';
1565 l_message := l_message || '</TR>';
1566 l_message := l_message || '<TR>';
1567 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_PLANNED_EFFORT') || '</B></TD>';
1568 l_message := l_message || '<TD>' || l_task_detail.planned_effort || '</TD>';
1569 l_message := l_message || '</TR>';
1570 l_message := l_message || '<TR>';
1571 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_PRIORITY') || '</B></TD>';
1572 l_message := l_message || '<TD>' || l_task_detail.priority || '</TD>';
1573 l_message := l_message || '</TR>';
1574 l_message := l_message || '<TR>';
1575 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_STATUS') || '</B></TD>';
1576 l_message := l_message || '<TD>' || l_task_detail.asgm_sts_name || '</TD>';
1577 l_message := l_message || '</TR>';
1578 l_message := l_message || '</TABLE>';
1579 l_message := l_message || '</P>';
1580 l_message := l_message || '<P><B>' || getPrompt('CSF_ALERTS_SERVICE_REQUEST') || ': </B>';
1581 l_message := l_message || l_task_detail.sr_number || ' ' || l_task_detail.sr_summary;
1582 l_message := l_message || '</P>';
1583
1584 if l_task_detail.product_nr is not null then
1585 l_message := l_message || '<P><B>' || getPrompt('CSF_ALERTS_ITEM') || '</B>: ' || l_task_detail.product_nr || ', ' || l_task_detail.item_description;
1586 if l_task_detail.item_serial is not null then
1587 l_message := l_message || '(' || l_task_detail.item_serial || ')';
1588 end if;
1589 l_message := l_message || '</P>';
1590 end if;
1591
1592 l_message := l_message || '<P><B>' || getPrompt('CSF_ALERTS_CUSTOMER') || '</B>:<BR/>';
1593 l_message := l_message || l_task_detail.cust_name || '<BR/>';
1594 l_message := l_message || l_task_detail.cust_address || '<BR/>';
1595
1596 if l_task_detail.contact_name is not null then
1597 l_message := l_message || '<B>' || getPrompt('CSF_ALERTS_CONTACT') || '</B>: ' || l_task_detail.contact_name || '<BR/>';
1598 l_message := l_message || l_task_detail.contact_phone || ' ' || l_task_detail.contact_email;
1599 end if;
1600
1601 l_message := l_message || '</P>';
1602
1603 -- notes
1604 l_is_notes := 'N';
1605
1606 open c_notes(l_task_asgn_id);
1607 loop
1608 fetch c_notes into l_notes;
1609 exit when c_notes%NOTFOUND;
1610
1611 if l_is_notes = 'N' then
1612 l_message := l_message || '<P><B>' || getPrompt('CSF_ALERTS_NOTES') || ':</B></P>';
1613 l_is_notes := 'Y';
1614 end if;
1615
1616 l_message := l_message || l_notes || '<BR/>';
1617
1618 end loop;
1619 close c_notes;
1620
1621 else
1622
1623 l_message := '';
1624 l_message := l_message || '
1625 ' || l_message_header;
1626 l_message := l_message || '
1627 ' || '
1628 ' || getPrompt('CSF_ALERTS_TASK_DETAILS') || ':';
1629 l_message := l_message || '
1630 ' || getPrompt('CSF_ALERTS_TASK') || ': ';
1631 l_message := l_message || l_task_detail.task_number || ' ' || l_task_detail.task_name;
1632 l_message := l_message || '
1633 ' || getPrompt('CSF_ALERTS_DESCRIPTION') || ': ';
1634 l_message := l_message || l_task_detail.task_desc;
1635 l_message := l_message || '
1636 ' || getPrompt('CSF_ALERTS_SCHEDULE_START') || ': ';
1637 l_message := l_message || to_char(getClientTime(l_task_detail.sch_st_date,
1638 getUserId(l_resource_id, l_resource_type)), 'DD-MON-YYYY HH24:MI');
1639 l_message := l_message || '
1640 ' || getPrompt('CSF_ALERTS_SCHEDULE_END') || ': ';
1641 l_message := l_message || to_char(getClientTime(l_task_detail.sch_end_date,
1642 getUserId(l_resource_id, l_resource_type)), 'DD-MON-YYYY HH24:MI');
1643 l_message := l_message || '
1644 ' || getPrompt('CSF_ALERTS_PLANNED_EFFORT') || ': ';
1645 l_message := l_message || l_task_detail.planned_effort;
1646 l_message := l_message || '
1647 ' || getPrompt('CSF_ALERTS_PRIORITY') || ': ';
1648 l_message := l_message || l_task_detail.priority;
1649 l_message := l_message || '
1650 ' || getPrompt('CSF_ALERTS_STATUS') || ': ';
1651 l_message := l_message || l_task_detail.asgm_sts_name;
1652 l_message := l_message || '
1653 ' || '
1654 ' || getPrompt('CSF_ALERTS_SERVICE_REQUEST') || ': ';
1655 l_message := l_message || l_task_detail.sr_number || ' ' || l_task_detail.sr_summary;
1656
1657 if l_task_detail.product_nr is not null then
1658 l_message := l_message || '
1659 ' || '
1660 ' || getPrompt('CSF_ALERTS_ITEM') || ': ' || l_task_detail.product_nr || ', ' || l_task_detail.item_description;
1661 if l_task_detail.item_serial is not null then
1662 l_message := l_message || '(' || l_task_detail.item_serial || ')';
1663 end if;
1664 end if;
1665
1666 l_message := l_message || '
1667 ' || '
1668 ' || getPrompt('CSF_ALERTS_CUSTOMER') || ': ';
1669 l_message := l_message || '
1670 ' || l_task_detail.cust_name;
1671 l_message := l_message || '
1672 ' || l_task_detail.cust_address;
1673
1674 if l_task_detail.contact_name is not null then
1675 l_message := l_message || '
1676 ' || '
1677 ' || getPrompt('CSF_ALERTS_CONTACT') || ': ' || l_task_detail.contact_name;
1678 l_message := l_message || l_task_detail.contact_phone || ' ' || l_task_detail.contact_email;
1679 end if;
1680
1681 -- notes
1682 l_is_notes := 'N';
1683
1684 open c_notes(l_task_asgn_id);
1685 loop
1686 fetch c_notes into l_notes;
1687 exit when c_notes%NOTFOUND;
1688
1689 if l_is_notes = 'N' then
1690 l_message := l_message || '
1691 ' || getPrompt('CSF_ALERTS_NOTES') || ':';
1692 l_is_notes := 'Y';
1693 end if;
1694
1695 l_message := l_message || l_notes || '
1696 ';
1697
1698 end loop;
1699 close c_notes;
1700
1701 end if;
1702
1703 fnd_global.APPS_INITIALIZE(user_id => l_tmp_user_id, resp_id => l_tmp_resp_id, resp_appl_id => l_tmp_resp_apps_id);
1704
1705 document := l_message;
1706
1707 end;
1708
1709 procedure getReminderMessage(document_id varchar2,
1710 display_type varchar2,
1711 document in out nocopy varchar2,
1712 document_type in out nocopy varchar2) is
1713 l_resource_id number;
1714 l_resource_type varchar2(100);
1715 l_task_asgn_id number;
1716
1717 l_message varchar2(32000);
1718 l_task_detail task_asgn_record;
1719 l_message_header1 varchar2(1000);
1720 l_message_header2 varchar2(1000);
1721
1722 l_tmp_user_id number;
1723 l_tmp_resp_id number;
1724 l_tmp_resp_apps_id number;
1725 p_user_id number;
1726
1727 -- notes
1728 l_is_notes varchar2(1);
1729 l_notes varchar2(30000);
1730 cursor c_notes (v_task_asgn_id number) is
1731 select
1732 n.notes
1733 from
1734 jtf_notes_vl n,
1735 jtf_task_assignments a
1736 where
1737 n.source_object_code = 'TASK'
1738 and n.source_object_id = a.task_id
1739 and a.task_assignment_id = v_task_asgn_id
1740 union select
1741 n.notes
1742 from
1743 jtf_notes_vl n,
1744 jtf_task_assignments a,
1745 jtf_tasks_b t
1746 where
1747 n.source_object_code = 'SR'
1748 and n.source_object_id = t.source_object_id
1749 and t.task_id = a.task_id
1750 and a.task_assignment_id = v_task_asgn_id;
1751 begin
1752
1753 l_resource_id := to_number(substr(document_id, 1, instr(document_id, '-', 1, 1) - 1));
1754 l_resource_type := substr(document_id, instr(document_id, '-', 1, 1) + 1, instr(document_id, '-', 1, 2) - instr(document_id, '-', 1, 1) - 1);
1755 l_task_asgn_id := to_number(substr(document_id, instr(document_id, '-', 1, 2) + 1));
1756
1757 p_user_id := getUserId(l_resource_id, l_resource_type);
1758
1759 if p_user_id is null then
1760 p_user_id := 0;
1761 end if;
1762
1763 -- call API
1764 l_tmp_user_id := fnd_global.USER_ID;
1765 l_tmp_resp_id := fnd_global.RESP_ID;
1766 l_tmp_resp_apps_id := fnd_global.RESP_APPL_ID;
1767
1768 fnd_global.APPS_INITIALIZE(user_id => p_user_id, resp_id => 21685, resp_appl_id => 513);
1769
1770 l_task_detail := getTaskDetails(null, l_task_asgn_id, null);
1771
1772 fnd_message.set_name('CSF', 'CSF_ALERTS_REMINDER_HDR');
1773 l_message_header1 := fnd_message.get;
1774 fnd_message.set_name('CSF', 'CSF_ALERTS_ASSIGNED_HDR');
1775 l_message_header2 := fnd_message.get;
1776
1777 if display_type = 'text/html' then
1778
1779 l_message := '<P>';
1780 l_message := l_message || '<B>' || l_message_header1 || '</B>';
1781 l_message := l_message || '<P>';
1782 l_message := l_message || l_message_header2 || '</P>';
1783 l_message := l_message || '<P><B>' || getPrompt('CSF_ALERTS_TASK_DETAILS') || ':</B></P>';
1784 l_message := l_message || '<P>';
1785 l_message := l_message || '<TABLE cellSpacing=0 cellPadding=0 border=1>';
1786 l_message := l_message || '<TR>';
1787 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_TASK') || '</B></TD>';
1788 l_message := l_message || '<TD>' || l_task_detail.task_number || ' ' || l_task_detail.task_name || '</TD>';
1789 l_message := l_message || '</TR>';
1790 l_message := l_message || '<TR>';
1791 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_DESCRIPTION') || '</B></TD>';
1792 l_message := l_message || '<TD>' || l_task_detail.task_desc || '</TD>';
1793 l_message := l_message || '</TR>';
1794 l_message := l_message || '<TR>';
1795 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_SCHEDULE_START') || '</B></TD>';
1796 l_message := l_message || '<TD>' || to_char(getClientTime(l_task_detail.sch_st_date,
1797 getUserId(l_resource_id, l_resource_type)), 'DD-MON-YYYY HH24:MI')
1798 || '</TD>';
1799 l_message := l_message || '</TR>';
1800 l_message := l_message || '<TR>';
1801 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_SCHEDULE_END') || '</B></TD>';
1802 l_message := l_message || '<TD>' || to_char(getClientTime(l_task_detail.sch_end_date,
1803 getUserId(l_resource_id, l_resource_type)), 'DD-MON-YYYY HH24:MI')
1804 || '</TD>';
1805 l_message := l_message || '</TR>';
1806 l_message := l_message || '<TR>';
1807 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_PLANNED_EFFORT') || '</B></TD>';
1808 l_message := l_message || '<TD>' || l_task_detail.planned_effort || '</TD>';
1809 l_message := l_message || '</TR>';
1810 l_message := l_message || '<TR>';
1811 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_PRIORITY') || '</B></TD>';
1812 l_message := l_message || '<TD>' || l_task_detail.priority || '</TD>';
1813 l_message := l_message || '</TR>';
1814 l_message := l_message || '<TR>';
1815 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_STATUS') || '</B></TD>';
1816 l_message := l_message || '<TD>' || l_task_detail.asgm_sts_name || '</TD>';
1817 l_message := l_message || '</TR>';
1818 l_message := l_message || '</TABLE>';
1819 l_message := l_message || '</P>';
1820 l_message := l_message || '<P><B>' || getPrompt('CSF_ALERTS_SERVICE_REQUEST') || ':</B>';
1821 l_message := l_message || l_task_detail.sr_number || ' ' || l_task_detail.sr_summary;
1822 l_message := l_message || '</P>';
1823
1824 if l_task_detail.product_nr is not null then
1825 l_message := l_message || '<P><B>' || getPrompt('CSF_ALERTS_ITEM') || '</B>: ' || l_task_detail.product_nr || ', ' || l_task_detail.item_description;
1826 if l_task_detail.item_serial is not null then
1827 l_message := l_message || '(' || l_task_detail.item_serial || ')';
1828 end if;
1829 l_message := l_message || '</P>';
1830 end if;
1831
1832 l_message := l_message || '<P><B>' || getPrompt('CSF_ALERTS_CUSTOMER') || '</B>:<BR/>';
1833 l_message := l_message || l_task_detail.cust_name || '<BR/>';
1834 l_message := l_message || l_task_detail.cust_address || '<BR/>';
1835
1836 if l_task_detail.contact_name is not null then
1837 l_message := l_message || '<B>' || getPrompt('CSF_ALERTS_CONTACT') || '</B>: ' || l_task_detail.contact_name || '<BR/>';
1838 l_message := l_message || l_task_detail.contact_phone || ' ' || l_task_detail.contact_email;
1839 end if;
1840
1841 l_message := l_message || '</P>';
1842
1843 -- notes
1844 l_is_notes := 'N';
1845
1846 open c_notes(l_task_asgn_id);
1847 loop
1848 fetch c_notes into l_notes;
1849 exit when c_notes%NOTFOUND;
1850
1851 if l_is_notes = 'N' then
1852 l_message := l_message || '<P><B>' || getPrompt('CSF_ALERTS_NOTES') || ':</B></P>';
1853 l_is_notes := 'Y';
1854 end if;
1855
1856 l_message := l_message || l_notes || '<BR/>';
1857
1858 end loop;
1859 close c_notes;
1860
1861 else
1862
1863 l_message := '
1864 ' || l_message_header1;
1865 l_message := l_message || '
1866 ' || '
1867 ' || l_message_header2;
1868 l_message := l_message || '
1869 ' || '
1870 ' || getPrompt('CSF_ALERTS_TASK_DETAILS') || ':';
1871 l_message := l_message || '
1872 ' || getPrompt('CSF_ALERTS_TASK') || ': ';
1873 l_message := l_message || l_task_detail.task_number || ' ' || l_task_detail.task_name;
1874 l_message := l_message || '
1875 ' || getPrompt('CSF_ALERTS_DESCRIPTION') || ': ';
1876 l_message := l_message || l_task_detail.task_desc;
1877 l_message := l_message || '
1878 ' || getPrompt('CSF_ALERTS_SCHEDULE_START') || ': ';
1879 l_message := l_message || to_char(getClientTime(l_task_detail.sch_st_date,
1880 getUserId(l_resource_id, l_resource_type)), 'DD-MON-YYYY HH24:MI');
1881 l_message := l_message || '
1882 ' || getPrompt('CSF_ALERTS_SCHEDULE_END') || ': ';
1883 l_message := l_message || to_char(getClientTime(l_task_detail.sch_end_date,
1884 getUserId(l_resource_id, l_resource_type)), 'DD-MON-YYYY HH24:MI');
1885 l_message := l_message || '
1886 ' || getPrompt('CSF_ALERTS_PLANNED_EFFORT') || ': ';
1887 l_message := l_message || l_task_detail.planned_effort;
1888 l_message := l_message || '
1889 ' || getPrompt('CSF_ALERTS_PRIORITY') || ': ';
1890 l_message := l_message || l_task_detail.priority;
1891 l_message := l_message || '
1892 ' || getPrompt('CSF_ALERTS_STATUS') || ': ';
1893 l_message := l_message || l_task_detail.asgm_sts_name;
1894 l_message := l_message || '
1895 ' || getPrompt('CSF_ALERTS_SERVICE_REQUEST') || ': ';
1896 l_message := l_message || l_task_detail.sr_number || ' ' || l_task_detail.sr_summary;
1897
1898 if l_task_detail.product_nr is not null then
1899 l_message := l_message || '
1900 ' || '
1901 ' || getPrompt('CSF_ALERTS_ITEM') || ': ' || l_task_detail.product_nr || ', ' || l_task_detail.item_description;
1902 if l_task_detail.item_serial is not null then
1903 l_message := l_message || '(' || l_task_detail.item_serial || ')';
1904 end if;
1905 end if;
1906
1907 l_message := l_message || '
1908 ' || '
1909 ' || getPrompt('CSF_ALERTS_CUSTOMER') || ': ';
1910 l_message := l_message || '
1911 ' || l_task_detail.cust_name;
1912 l_message := l_message || '
1913 ' || l_task_detail.cust_address;
1914
1915 if l_task_detail.contact_name is not null then
1916 l_message := l_message || '
1917 ' || getPrompt('CSF_ALERTS_CONTACT') || ': ' || l_task_detail.contact_name;
1918 l_message := l_message || l_task_detail.contact_phone || ' ' || l_task_detail.contact_email;
1919 end if;
1920
1921 -- notes
1922 l_is_notes := 'N';
1923
1924 open c_notes(l_task_asgn_id);
1925 loop
1926 fetch c_notes into l_notes;
1927 exit when c_notes%NOTFOUND;
1928
1929 if l_is_notes = 'N' then
1930 l_message := l_message || '
1931 ' || getPrompt('CSF_ALERTS_NOTES') || ':';
1932 l_is_notes := 'Y';
1933 end if;
1934
1935 l_message := l_message || l_notes || '
1936 ';
1937
1938 end loop;
1939 close c_notes;
1940
1941 end if;
1942
1943 fnd_global.APPS_INITIALIZE(user_id => l_tmp_user_id, resp_id => l_tmp_resp_id, resp_appl_id => l_tmp_resp_apps_id);
1944
1945 document := l_message;
1946
1947 end;
1948
1949 procedure getDeleteMessage(document_id varchar2,
1950 display_type varchar2,
1951 document in out nocopy varchar2,
1952 document_type in out nocopy varchar2) is
1953 l_resource_id number;
1954 l_resource_type varchar2(100);
1955 l_task_id number;
1956
1957 l_message varchar2(32000);
1958 l_task_detail task_asgn_record;
1959 l_message_header varchar2(1000);
1960
1961 l_tmp_user_id number;
1962 l_tmp_resp_id number;
1963 l_tmp_resp_apps_id number;
1964 p_user_id number;
1965
1966 -- notes
1967 l_is_notes varchar2(1);
1968 l_notes varchar2(30000);
1969 cursor c_notes (v_task_id number) is
1970 select
1971 n.notes
1972 from
1973 jtf_notes_vl n
1974 where
1975 n.source_object_code = 'TASK'
1976 and n.source_object_id = v_task_id
1977 union select
1978 n.notes
1979 from
1980 jtf_notes_vl n,
1981 jtf_tasks_b t
1982 where
1983 n.source_object_code = 'SR'
1984 and n.source_object_id = t.source_object_id
1985 and t.task_id = v_task_id;
1986 begin
1987
1988 l_resource_id := to_number(substr(document_id, 1, instr(document_id, '-', 1, 1) - 1));
1989 l_resource_type := substr(document_id, instr(document_id, '-', 1, 1) + 1, instr(document_id, '-', 1, 2) - instr(document_id, '-', 1, 1) - 1);
1990 l_task_id := to_number(substr(document_id, instr(document_id, '-', 1, 2) + 1));
1991
1992 p_user_id := getUserId(l_resource_id, l_resource_type);
1993
1994 if p_user_id is null then
1995 p_user_id := 0;
1996 end if;
1997
1998 -- call API
1999 l_tmp_user_id := fnd_global.USER_ID;
2000 l_tmp_resp_id := fnd_global.RESP_ID;
2001 l_tmp_resp_apps_id := fnd_global.RESP_APPL_ID;
2002
2003 fnd_global.APPS_INITIALIZE(user_id => p_user_id, resp_id => 21685, resp_appl_id => 513);
2004
2005 l_task_detail := getTaskDetails(l_task_id, null, null);
2006
2007 fnd_message.set_name('CSF', 'CSF_ALERTS_DELETE_HDR');
2008 l_message_header := fnd_message.get;
2009
2010 if display_type = 'text/html' then
2011
2012 l_message := '<P>';
2013 l_message := l_message || l_message_header || '</P>';
2014 l_message := l_message || '<P><B>' || getPrompt('CSF_ALERTS_TASK_DETAILS') || ':</B></P>';
2015 l_message := l_message || '<P>';
2016 l_message := l_message || '<TABLE cellSpacing=0 cellPadding=0 border=1>';
2017 l_message := l_message || '<TR>';
2018 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_TASK') || '</B></TD>';
2019 l_message := l_message || '<TD>' || l_task_detail.task_number || ' ' || l_task_detail.task_name || '</TD>';
2020 l_message := l_message || '</TR>';
2021 l_message := l_message || '<TR>';
2022 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_DESCRIPTION') || '</B></TD>';
2023 l_message := l_message || '<TD>' || l_task_detail.task_desc || '</TD>';
2024 l_message := l_message || '</TR>';
2025 l_message := l_message || '<TR>';
2026 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_SCHEDULE_START') || '</B></TD>';
2027 l_message := l_message || '<TD>' || to_char(getClientTime(l_task_detail.sch_st_date,
2028 getUserId(l_resource_id, l_resource_type)), 'DD-MON-YYYY HH24:MI')
2029 || '</TD>';
2030 l_message := l_message || '</TR>';
2031 l_message := l_message || '<TR>';
2032 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_SCHEDULE_END') || '</B></TD>';
2033 l_message := l_message || '<TD>' || to_char(getClientTime(l_task_detail.sch_end_date,
2034 getUserId(l_resource_id, l_resource_type)), 'DD-MON-YYYY HH24:MI')
2035 || '</TD>';
2036 l_message := l_message || '</TR>';
2037 l_message := l_message || '<TR>';
2038 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_PLANNED_EFFORT') || '</B></TD>';
2039 l_message := l_message || '<TD>' || l_task_detail.planned_effort || '</TD>';
2040 l_message := l_message || '</TR>';
2041 l_message := l_message || '<TR>';
2042 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_PRIORITY') || '</B></TD>';
2043 l_message := l_message || '<TD>' || l_task_detail.priority || '</TD>';
2044 l_message := l_message || '</TR>';
2045 l_message := l_message || '</TABLE>';
2046 l_message := l_message || '</P>';
2047 l_message := l_message || '<P><B>' || getPrompt('CSF_ALERTS_SERVICE_REQUEST') || ':</B>';
2048 l_message := l_message || l_task_detail.sr_number || ' ' || l_task_detail.sr_summary;
2049 l_message := l_message || '</P>';
2050
2051 if l_task_detail.product_nr is not null then
2052 l_message := l_message || '<P><B>' || getPrompt('CSF_ALERTS_ITEM') || '</B>: ' || l_task_detail.product_nr || ', ' || l_task_detail.item_description;
2053 if l_task_detail.item_serial is not null then
2054 l_message := l_message || '(' || l_task_detail.item_serial || ')';
2055 end if;
2056 l_message := l_message || '</P>';
2057 end if;
2058
2059 l_message := l_message || '<P><B>' || getPrompt('CSF_ALERTS_CUSTOMER') || '</B>:<BR/>';
2060 l_message := l_message || l_task_detail.cust_name || '<BR/>';
2061 l_message := l_message || l_task_detail.cust_address || '<BR/>';
2062
2063 if l_task_detail.contact_name is not null then
2064 l_message := l_message || '<B>' || getPrompt('CSF_ALERTS_CONTACT') || '</B>: ' || l_task_detail.contact_name || '<BR/>';
2065 l_message := l_message || l_task_detail.contact_phone || ' ' || l_task_detail.contact_email;
2066 end if;
2067
2068 l_message := l_message || '</P>';
2069
2070 -- notes
2071 l_is_notes := 'N';
2072
2073 open c_notes(l_task_id);
2074 loop
2075 fetch c_notes into l_notes;
2076 exit when c_notes%NOTFOUND;
2077
2078 if l_is_notes = 'N' then
2079 l_message := l_message || '<P><B>' || getPrompt('CSF_ALERTS_NOTES') || ':</B></P>';
2080 l_is_notes := 'Y';
2081 end if;
2082
2083 l_message := l_message || l_notes || '<BR/>';
2084
2085 end loop;
2086 close c_notes;
2087
2088 else
2089
2090 l_message := '';
2091 l_message := l_message || '
2092 ' || l_message_header;
2093 l_message := l_message || '
2094 ' || '
2095 ' || getPrompt('CSF_ALERTS_TASK_DETAILS') || ':';
2096 l_message := l_message || '
2097 ' || getPrompt('CSF_ALERTS_TASK') || ': ';
2098 l_message := l_message || l_task_detail.task_number || ' ' || l_task_detail.task_name;
2099 l_message := l_message || '
2100 ' || getPrompt('CSF_ALERTS_DESCRIPTION') || ': ';
2101 l_message := l_message || l_task_detail.task_desc;
2102 l_message := l_message || '
2103 ' || getPrompt('CSF_ALERTS_SCHEDULE_START') || ': ';
2104 l_message := l_message || to_char(getClientTime(l_task_detail.sch_st_date,
2105 getUserId(l_resource_id, l_resource_type)), 'DD-MON-YYYY HH24:MI');
2106 l_message := l_message || '
2107 ' || getPrompt('CSF_ALERTS_SCHEDULE_END') || ': ';
2108 l_message := l_message || to_char(getClientTime(l_task_detail.sch_end_date,
2109 getUserId(l_resource_id, l_resource_type)), 'DD-MON-YYYY HH24:MI');
2110 l_message := l_message || '
2111 ' || getPrompt('CSF_ALERTS_PLANNED_EFFORT') || ': ';
2112 l_message := l_message || l_task_detail.planned_effort;
2113 l_message := l_message || '
2114 ' || getPrompt('CSF_ALERTS_PRIORITY') || ': ';
2115 l_message := l_message || l_task_detail.priority;
2116 l_message := l_message || '
2117 ' || getPrompt('CSF_ALERTS_SERVICE_REQUEST') || ': ';
2118 l_message := l_message || l_task_detail.sr_number || ' ' || l_task_detail.sr_summary;
2119
2120 if l_task_detail.product_nr is not null then
2121 l_message := l_message || '
2122 ' || '
2123 ' || getPrompt('CSF_ALERTS_ITEM') || ': ' || l_task_detail.product_nr || ', ' || l_task_detail.item_description;
2124 if l_task_detail.item_serial is not null then
2125 l_message := l_message || '(' || l_task_detail.item_serial || ')';
2126 end if;
2127 end if;
2128
2129 l_message := l_message || '
2130 ' || '
2131 ' || getPrompt('CSF_ALERTS_CUSTOMER') || ': ';
2132 l_message := l_message || '
2133 ' || l_task_detail.cust_name;
2134 l_message := l_message || '
2135 ' || l_task_detail.cust_address;
2136
2137 if l_task_detail.contact_name is not null then
2138 l_message := l_message || '
2139 ' || getPrompt('CSF_ALERTS_CONTACT') || ': ' || l_task_detail.contact_name;
2140 l_message := l_message || l_task_detail.contact_phone || ' ' || l_task_detail.contact_email;
2141 end if;
2142
2143 -- notes
2144 l_is_notes := 'N';
2145
2146 open c_notes(l_task_id);
2147 loop
2148 fetch c_notes into l_notes;
2149 exit when c_notes%NOTFOUND;
2150
2151 if l_is_notes = 'N' then
2152 l_message := l_message || '
2153 ' || getPrompt('CSF_ALERTS_NOTES') || ':';
2154 l_is_notes := 'Y';
2155 end if;
2156
2157 l_message := l_message || l_notes || '
2158 ';
2159
2160 end loop;
2161 close c_notes;
2162
2163 end if;
2164
2165 fnd_global.APPS_INITIALIZE(user_id => l_tmp_user_id, resp_id => l_tmp_resp_id, resp_appl_id => l_tmp_resp_apps_id);
2166
2167 document := l_message;
2168
2169 end;
2170
2171 procedure getRescheduleMessage(document_id varchar2,
2172 display_type varchar2,
2173 document in out nocopy varchar2,
2174 document_type in out nocopy varchar2) is
2175 l_resource_id number;
2176 l_resource_type varchar2(100);
2177 l_message varchar2(32000);
2178 l_task_detail task_asgn_record;
2179 p_task_id number;
2180 p_task_audit_id number;
2181 l_message_header varchar2(1000);
2182
2183 l_tmp_user_id number;
2184 l_tmp_resp_id number;
2185 l_tmp_resp_apps_id number;
2186 p_user_id number;
2187
2188 -- notes
2189 l_is_notes varchar2(1);
2190 l_notes varchar2(30000);
2191 cursor c_notes (v_task_id number) is
2192 select
2193 n.notes
2194 from
2195 jtf_notes_vl n
2196 where
2197 n.source_object_code = 'TASK'
2198 and n.source_object_id = v_task_id
2199 union select
2200 n.notes
2201 from
2202 jtf_notes_vl n,
2203 jtf_tasks_b t
2204 where
2205 n.source_object_code = 'SR'
2206 and n.source_object_id = t.source_object_id
2207 and t.task_id = v_task_id;
2208 begin
2209
2210 l_resource_id := to_number(substr(document_id, 1, instr(document_id, '-', 1, 1) - 1));
2211 l_resource_type := substr(document_id, instr(document_id, '-', 1, 1) + 1, instr(document_id, '-', 1, 2) - instr(document_id, '-', 1, 1) - 1);
2212
2213 p_task_id := to_number(substr(document_id, instr(document_id, '-', 1, 2) + 1, instr(document_id, '-', 1, 3) - instr(document_id, '-', 1, 2) - 1));
2214 p_task_audit_id := to_number(substr(document_id, instr(document_id, '-', 1, 3) + 1));
2215
2216 p_user_id := getUserId(l_resource_id, l_resource_type);
2217
2218 if p_user_id is null then
2219 p_user_id := 0;
2220 end if;
2221
2222 -- call API
2223 l_tmp_user_id := fnd_global.USER_ID;
2224 l_tmp_resp_id := fnd_global.RESP_ID;
2225 l_tmp_resp_apps_id := fnd_global.RESP_APPL_ID;
2226
2227 fnd_global.APPS_INITIALIZE(user_id => p_user_id, resp_id => 21685, resp_appl_id => 513);
2228
2229 l_task_detail := getTaskDetails(p_task_id, null, p_task_audit_id);
2230
2231 fnd_message.set_name('CSF', 'CSF_ALERTS_RESCHEDULE_HDR');
2232 l_message_header := fnd_message.get;
2233
2234 if display_type = 'text/html' then
2235
2236 l_message := '<P>';
2237 l_message := l_message || l_message_header || '</P>';
2238 l_message := l_message || '<P><B>' || getPrompt('CSF_ALERTS_TASK_DETAILS') || ':</B></P>';
2239 l_message := l_message || '<P>';
2240 l_message := l_message || '<TABLE cellSpacing=0 cellPadding=0 border=1>';
2241 l_message := l_message || '<TR>';
2242 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_TASK') || '</B></TD>';
2243 l_message := l_message || '<TD>' || l_task_detail.task_number || ' ' || l_task_detail.task_name || '</TD>';
2244 l_message := l_message || '</TR>';
2245 l_message := l_message || '<TR>';
2246 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_DESCRIPTION') || '</B></TD>';
2247 l_message := l_message || '<TD>' || l_task_detail.task_desc || '</TD>';
2248 l_message := l_message || '</TR>';
2249 l_message := l_message || '<TR>';
2250 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_N_SCH_START') || '</B></TD>';
2251 l_message := l_message || '<TD>' || to_char(getClientTime(l_task_detail.sch_st_date,
2252 getUserId(l_resource_id, l_resource_type)), 'DD-MON-YYYY HH24:MI')
2253 || '</TD>';
2254 l_message := l_message || '</TR>';
2255 l_message := l_message || '<TR>';
2256 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_O_SCH_START') || '</B></TD>';
2257 l_message := l_message || '<TD>' || to_char(getClientTime(l_task_detail.old_sch_st_date,
2258 getUserId(l_resource_id, l_resource_type)), 'DD-MON-YYYY HH24:MI')
2259 || '</TD>';
2260 l_message := l_message || '</TR>';
2261 l_message := l_message || '<TR>';
2262 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_N_SCH_END') || '</B></TD>';
2263 l_message := l_message || '<TD>' || to_char(getClientTime(l_task_detail.sch_end_date,
2264 getUserId(l_resource_id, l_resource_type)), 'DD-MON-YYYY HH24:MI')
2265 || '</TD>';
2266 l_message := l_message || '</TR>';
2267 l_message := l_message || '<TR>';
2268 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_O_SCH_END') || '</B></TD>';
2269 l_message := l_message || '<TD>' || to_char(getClientTime(l_task_detail.old_sch_end_date,
2270 getUserId(l_resource_id, l_resource_type)), 'DD-MON-YYYY HH24:MI')
2271 || '</TD>';
2272 l_message := l_message || '</TR>';
2273 l_message := l_message || '<TR>';
2274 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_PLANNED_EFFORT') || '</B></TD>';
2275 l_message := l_message || '<TD>' || l_task_detail.planned_effort || '</TD>';
2276 l_message := l_message || '</TR>';
2277 l_message := l_message || '<TR>';
2278 l_message := l_message || '<TD><B>' || getPrompt('CSF_ALERTS_PRIORITY') || '</B></TD>';
2279 l_message := l_message || '<TD>' || l_task_detail.priority || '</TD>';
2280 l_message := l_message || '</TR>';
2281 l_message := l_message || '</TABLE>';
2282 l_message := l_message || '</P>';
2283 l_message := l_message || '<P><B>' || getPrompt('CSF_ALERTS_SERVICE_REQUEST') || ':</B>';
2284 l_message := l_message || l_task_detail.sr_number || ' ' || l_task_detail.sr_summary;
2285 l_message := l_message || '</P>';
2286
2287 if l_task_detail.product_nr is not null then
2288 l_message := l_message || '<P><B>' || getPrompt('CSF_ALERTS_ITEM') || '</B>: ' || l_task_detail.product_nr || ', ' || l_task_detail.item_description;
2289 if l_task_detail.item_serial is not null then
2290 l_message := l_message || '(' || l_task_detail.item_serial || ')';
2291 end if;
2292 l_message := l_message || '</P>';
2293 end if;
2294
2295 l_message := l_message || '<P><B>' || getPrompt('CSF_ALERTS_CUSTOMER') || '</B>:<BR/>';
2296 l_message := l_message || l_task_detail.cust_name || '<BR/>';
2297 l_message := l_message || l_task_detail.cust_address || '<BR/>';
2298
2299 if l_task_detail.contact_name is not null then
2300 l_message := l_message || '<B>' || getPrompt('CSF_ALERTS_CONTACT') || '</B>: ' || l_task_detail.contact_name || '<BR/>';
2301 l_message := l_message || l_task_detail.contact_phone || ' ' || l_task_detail.contact_email;
2302 end if;
2303
2304 l_message := l_message || '</P>';
2305
2306 -- notes
2307 l_is_notes := 'N';
2308
2309 open c_notes(p_task_id);
2310 loop
2311 fetch c_notes into l_notes;
2312 exit when c_notes%NOTFOUND;
2313
2314 if l_is_notes = 'N' then
2315 l_message := l_message || '<P><B>' || getPrompt('CSF_ALERTS_NOTES') || ':</B></P>';
2316 l_is_notes := 'Y';
2317 end if;
2318
2319 l_message := l_message || l_notes || '<BR/>';
2320
2321 end loop;
2322 close c_notes;
2323
2324 else
2325
2326 l_message := '';
2327 l_message := l_message || '
2328 ' || l_message_header;
2329 l_message := l_message || '
2330 ' || '
2331 ' || getPrompt('CSF_ALERTS_TASK_DETAILS') || ':';
2332 l_message := l_message || '
2333 ' || getPrompt('CSF_ALERTS_TASK') || ': ';
2334 l_message := l_message || l_task_detail.task_number || ' ' || l_task_detail.task_name;
2335 l_message := l_message || '
2336 ' || getPrompt('CSF_ALERTS_DESCRIPTION') || ': ';
2337 l_message := l_message || l_task_detail.task_desc;
2338 l_message := l_message || '
2339 ' || getPrompt('CSF_ALERTS_N_SCH_START') || ': ';
2340 l_message := l_message || to_char(getClientTime(l_task_detail.sch_st_date,
2341 getUserId(l_resource_id, l_resource_type)), 'DD-MON-YYYY HH24:MI');
2342 l_message := l_message || '
2343 ' || getPrompt('CSF_ALERTS_O_SCH_START') || ': ';
2344 l_message := l_message || to_char(getClientTime(l_task_detail.old_sch_st_date,
2345 getUserId(l_resource_id, l_resource_type)), 'DD-MON-YYYY HH24:MI');
2346 l_message := l_message || '
2347 ' || getPrompt('CSF_ALERTS_N_SCH_END') || ': ';
2348 l_message := l_message || to_char(getClientTime(l_task_detail.sch_end_date,
2349 getUserId(l_resource_id, l_resource_type)), 'DD-MON-YYYY HH24:MI');
2350 l_message := l_message || '
2351 ' || getPrompt('CSF_ALERTS_O_SCH_END') || ': ';
2352 l_message := l_message || to_char(getClientTime(l_task_detail.old_sch_end_date,
2353 getUserId(l_resource_id, l_resource_type)), 'DD-MON-YYYY HH24:MI');
2354 l_message := l_message || '
2355 ' || getPrompt('CSF_ALERTS_PLANNED_EFFORT') || ': ';
2356 l_message := l_message || l_task_detail.planned_effort;
2357 l_message := l_message || '
2358 ' || getPrompt('CSF_ALERTS_PRIORITY') || ': ';
2359 l_message := l_message || l_task_detail.priority;
2360 l_message := l_message || '
2361 ' || getPrompt('CSF_ALERTS_SERVICE_REQUEST') || ': ';
2362 l_message := l_message || l_task_detail.sr_number || ' ' || l_task_detail.sr_summary;
2363
2364 if l_task_detail.product_nr is not null then
2365 l_message := l_message || '
2366 ' || '
2367 ' || getPrompt('CSF_ALERTS_ITEM') || ': ' || l_task_detail.product_nr || ', ' || l_task_detail.item_description;
2368 if l_task_detail.item_serial is not null then
2369 l_message := l_message || '(' || l_task_detail.item_serial || ')';
2370 end if;
2371 end if;
2372
2373 l_message := l_message || '
2374 ' || '
2375 ' || getPrompt('CSF_ALERTS_CUSTOMER') || ': ';
2376 l_message := l_message || '
2377 ' || l_task_detail.cust_name;
2378 l_message := l_message || '
2379 ' || l_task_detail.cust_address;
2380
2381 if l_task_detail.contact_name is not null then
2382 l_message := l_message || '
2383 ' || getPrompt('CSF_ALERTS_CONTACT') || ': ' || l_task_detail.contact_name;
2384 l_message := l_message || l_task_detail.contact_phone || ' ' || l_task_detail.contact_email;
2385 end if;
2386
2387 -- notes
2388 l_is_notes := 'N';
2389
2390 open c_notes(p_task_id);
2391 loop
2392 fetch c_notes into l_notes;
2393 exit when c_notes%NOTFOUND;
2394
2395 if l_is_notes = 'N' then
2396 l_message := l_message || '
2397 ' || getPrompt('CSF_ALERTS_NOTES') || ':';
2398 l_is_notes := 'Y';
2399 end if;
2400
2401 l_message := l_message || l_notes || '
2402 ';
2403
2404 end loop;
2405 close c_notes;
2406
2407 end if;
2408
2409 fnd_global.APPS_INITIALIZE(user_id => l_tmp_user_id, resp_id => l_tmp_resp_id, resp_appl_id => l_tmp_resp_apps_id);
2410
2411 document := l_message;
2412
2413 end;
2414
2415 function getPrompt (p_name varchar2) return varchar2 is
2416 l_return varchar2(100);
2417 begin
2418 l_return := '';
2419 fnd_message.set_name('CSF', p_name);
2420 l_return := fnd_message.get;
2421 return l_return;
2422 end;
2423
2424 function getContactDetail(p_incident_id number,
2425 p_contact_type varchar2,
2426 p_party_id number) return contact_record is
2427
2428 l_contact contact_record;
2429
2430 l_contact_name varchar2(100);
2431 l_contact_email varchar2(250);
2432 l_contact_phone varchar2(100);
2433
2434 cursor c_EMP_contact(v_id number) is
2435 SELECT
2436 per.full_name contactname,
2437 per.email_address email,
2438 ph.phone_number phone_number
2439 FROM
2440 per_all_people_f per,
2441 per_phones ph
2442 WHERE
2443 per.person_id = v_id
2444 and per.person_id = ph.parent_id
2445 and ph.phone_type = 'W1'
2446 AND ph.parent_table = 'PER_ALL_PEOPLE_F'
2447 AND sysdate between nvl(per.effective_start_date, sysdate)
2448 and nvl(per.effective_end_date, sysdate);
2449
2450 -- bug # 6630754
2451 -- relaced hz_party_relationships with hz_relationships
2452 cursor c_REL_contact(v_id number) is
2453 SELECT
2454 hp.person_first_name ||' '|| hp.person_last_name contactname,
2455 hp.email_address email
2456 FROM
2457 hz_relationships rel,
2458 hz_parties hp
2459 WHERE
2460 rel.party_id = v_id
2461 AND rel.subject_id = hp.party_id
2462 AND rel.subject_table_name = 'HZ_PARTIES'
2463 AND rel.subject_type = 'PERSON';
2464
2465 cursor c_PERSON_contact(v_id number) is
2466 Select PARTY_NAME, EMAIL_ADDRESS from hz_parties where party_id = v_id;
2467
2468 cursor c_rel_person_phone(v_incident_id number) is
2469 SELECT
2470 hcp.phone_country_code || ' ' || hcp.phone_area_code || ' ' || hcp.phone_number PHONE_NUMBER
2471 FROM
2472 cs_incidents_all_b ci_all_b,
2473 cs_hz_sr_contact_points_v chscp,
2474 hz_contact_points hcp
2475 WHERE
2476 ci_all_b.incident_id = chscp.incident_id
2477 AND chscp.contact_point_id = hcp.contact_point_id
2478 AND chscp.primary_flag = 'Y'
2479 AND hcp.contact_point_type = 'PHONE'
2480 AND ci_all_b.incident_id = v_incident_id;
2481 begin
2482
2483 if p_contact_type = 'EMPLOYEE' then
2484
2485 open c_EMP_contact(p_party_id);
2486 fetch c_EMP_contact into l_contact_name, l_contact_email, l_contact_phone;
2487 close c_EMP_contact;
2488
2489 elsif p_contact_type = 'PARTY_RELATIONSHIP' then
2490
2491 open c_REL_contact(p_party_id);
2492 fetch c_REL_contact into l_contact_name, l_contact_email;
2493 close c_REL_contact;
2494
2495 open c_rel_person_phone(p_incident_id);
2496 fetch c_rel_person_phone into l_contact_phone;
2497 close c_rel_person_phone;
2498
2499 elsif p_contact_type = 'PERSON' then
2500
2501 open c_PERSON_contact(p_party_id);
2502 fetch c_PERSON_contact into l_contact_name, l_contact_email;
2503 close c_PERSON_contact;
2504
2505 open c_rel_person_phone(p_incident_id);
2506 fetch c_rel_person_phone into l_contact_phone;
2507 close c_rel_person_phone;
2508
2509 end if;
2510
2511 l_contact.contact_name := l_contact_name;
2512 l_contact.contact_email := l_contact_email;
2513 l_contact.contact_phone := l_contact_phone;
2514
2515 return l_contact;
2516
2517 end;
2518
2519 function getTaskDetails(p_task_id number,
2520 p_task_asgn_id number,
2521 p_task_audit_id number) return task_asgn_record is
2522
2523 l_task_asgn_record task_asgn_record;
2524 l_contact_record contact_record;
2525
2526 l_task_number jtf_tasks_b.task_number%type;
2527 l_task_name jtf_tasks_vl.task_name%type;
2528 l_task_desc jtf_tasks_vl.description%type;
2529 l_sch_st_date jtf_tasks_b.scheduled_start_date%type;
2530 l_old_sch_st_date jtf_tasks_b.scheduled_start_date%type;
2531 l_sch_end_date jtf_tasks_b.scheduled_end_date%type;
2532 l_old_sch_end_date jtf_tasks_b.scheduled_end_date%type;
2533 l_planned_effort varchar2(100);
2534 l_priority jtf_task_priorities_vl.name%type;
2535 l_asgm_sts_name jtf_task_statuses_vl.name%type;
2536 l_sr_number cs_incidents_all_b.incident_number%type;
2537 l_sr_summary cs_incidents_all_tl.summary%type;
2538 l_product_nr mtl_system_items_vl.concatenated_segments%type;
2539 l_item_serial cs_customer_products_all.current_serial_number%type;
2540 l_item_description mtl_system_items_vl.description%type;
2541 l_cust_name hz_parties.party_name%type;
2542 l_cust_address varchar2(1000);
2543 l_contact_name varchar2(100);
2544 l_contact_phone varchar2(100);
2545 l_contact_email varchar2(250);
2546 l_contact_type varchar2(200);
2547 l_contact_party_id number;
2548 l_incident_id number;
2549
2550 cursor c_task_assgn_detail (v_task_assgn_id number) is
2551 SELECT
2552 c_b.incident_id incident_id,
2553 c_b.incident_number sr_number,
2554 c_b.summary sr_summary,
2555 hp.party_name cust_name,
2556 hp.address1 || ', ' || hp.postal_code || ', ' || hp.city address,
2557 jtb.task_number task_number,
2558 j_vl.task_name task_name,
2559 js_vl.name assignment_name,
2560 jp_vl.name priority,
2561 j_vl.PLANNED_EFFORT || ' ' || j_vl.PLANNED_EFFORT_UOM planned_effort,
2562 jtb.scheduled_start_date sch_st_date,
2563 jtb.scheduled_end_date sch_end_date,
2564 j_vl.description task_desc,
2565 msi_b.concatenated_segments product_nr,
2566 ccp_all.current_serial_number item_serial,
2567 msi_b.description item_description,
2568 chscp.contact_type contact_type,
2569 chscp.party_id contact_party_id
2570 FROM
2571 jtf_tasks_b jtb,
2572 jtf_task_assignments jta,
2573 jtf_tasks_vl j_vl,
2574 jtf_task_priorities_vl jp_vl,
2575 jtf_task_statuses_vl js_vl,
2576 cs_incidents_all c_b,
2577 hz_party_sites hps,
2578 hz_parties hp,
2579 mtl_system_items_vl msi_b,
2580 cs_customer_products_all ccp_all,
2581 cs_hz_sr_contact_points_v chscp
2582 WHERE
2583 jta.task_assignment_id = v_task_assgn_id
2584 and jta.task_id = jtb.task_id
2585 and j_vl.task_id = jta.task_id
2586 and jp_vl.task_priority_id (+) = j_vl.task_priority_id
2587 and js_vl.task_status_id = jta.assignment_status_id
2588 and jtb.source_object_type_code = 'SR'
2589 and jtb.source_object_id = c_b.incident_id
2590 and jtb.address_id = hps.party_site_id
2591 and hps.party_id = hp.party_id
2592 and c_b.inventory_item_id = msi_b.inventory_item_id (+)
2593 and c_b.customer_product_id = ccp_all.customer_product_id(+)
2594 and msi_b.organization_id (+) = c_b.org_id
2595 and chscp.primary_flag (+) = 'Y'
2596 and chscp.incident_id (+) = c_b.incident_id;
2597
2598 cursor c_task_detail (v_task_id number) is
2599 SELECT
2600 c_b.incident_id incident_id,
2601 c_b.incident_number sr_number,
2602 c_b.summary sr_summary,
2603 hp.party_name cust_name,
2604 hp.address1 || ', ' || hp.postal_code || ', ' || hp.city address,
2605 jtb.task_number task_number,
2606 j_vl.task_name task_name,
2607 jp_vl.name priority,
2608 j_vl.PLANNED_EFFORT || ' ' || j_vl.PLANNED_EFFORT_UOM planned_effort,
2609 jtb.scheduled_start_date sch_st_date,
2610 jtb.scheduled_end_date sch_end_date,
2611 j_vl.description task_desc,
2612 msi_b.concatenated_segments product_nr,
2613 ccp_all.current_serial_number item_serial,
2614 msi_b.description item_description,
2615 chscp.contact_type contact_type,
2616 chscp.party_id contact_party_id
2617 FROM
2618 jtf_tasks_b jtb,
2619 jtf_tasks_vl j_vl,
2620 jtf_task_priorities_vl jp_vl,
2621 cs_incidents_all c_b,
2622 hz_party_sites hps,
2623 hz_parties hp,
2624 mtl_system_items_vl msi_b,
2625 cs_customer_products_all ccp_all,
2626 cs_hz_sr_contact_points_v chscp
2627 WHERE
2628 jtb.task_id = v_task_id
2629 and j_vl.task_id = jtb.task_id
2630 and jp_vl.task_priority_id (+) = j_vl.task_priority_id
2631 and jtb.source_object_type_code = 'SR'
2632 and jtb.source_object_id = c_b.incident_id
2633 and jtb.address_id = hps.party_site_id
2634 and hps.party_id = hp.party_id
2635 and c_b.inventory_item_id = msi_b.inventory_item_id (+)
2636 and c_b.customer_product_id = ccp_all.customer_product_id(+)
2637 and msi_b.organization_id (+) = c_b.org_id
2638 and chscp.primary_flag (+) = 'Y'
2639 and chscp.incident_id (+) = c_b.incident_id;
2640
2641 cursor c_task_audit_detail (v_task_id number, v_task_audit_id number) is
2642 SELECT
2643 c_b.incident_id incident_id,
2644 c_b.incident_number sr_number,
2645 c_b.summary sr_summary,
2646 hp.party_name cust_name,
2647 hp.address1 || ', ' || hp.postal_code || ', ' || hp.city address,
2648 jtb.task_number task_number,
2649 j_vl.task_name task_name,
2650 jp_vl.name priority,
2651 j_vl.PLANNED_EFFORT || ' ' || j_vl.PLANNED_EFFORT_UOM planned_effort,
2652 jtb.scheduled_start_date sch_st_date,
2653 jtb.scheduled_end_date sch_end_date,
2654 j_vl.description task_desc,
2655 msi_b.concatenated_segments product_nr,
2656 ccp_all.current_serial_number item_serial,
2657 msi_b.description item_description,
2658 chscp.contact_type contact_type,
2659 chscp.party_id contact_party_id,
2660 jtab.old_scheduled_start_date old_sch_st_date,
2661 jtab.old_scheduled_end_date old_sch_end_date
2662 FROM
2663 jtf_tasks_b jtb,
2664 jtf_task_audits_b jtab,
2665 jtf_tasks_vl j_vl,
2666 jtf_task_priorities_vl jp_vl,
2667 cs_incidents_all c_b,
2668 hz_party_sites hps,
2669 hz_parties hp,
2670 mtl_system_items_vl msi_b,
2671 cs_customer_products_all ccp_all,
2672 cs_hz_sr_contact_points_v chscp
2673 WHERE
2674 jtb.task_id = v_task_id
2675 and jtab.task_audit_id = v_task_audit_id
2676 and jtab.task_id = jtb.task_id
2677 and j_vl.task_id = jtb.task_id
2678 and jp_vl.task_priority_id (+) = j_vl.task_priority_id
2679 and jtb.source_object_type_code = 'SR'
2680 and jtb.source_object_id = c_b.incident_id
2681 and jtb.address_id = hps.party_site_id
2682 and hps.party_id = hp.party_id
2683 and c_b.inventory_item_id = msi_b.inventory_item_id (+)
2684 and c_b.customer_product_id = ccp_all.customer_product_id(+)
2685 and msi_b.organization_id (+) = c_b.org_id
2686 and chscp.primary_flag (+) = 'Y'
2687 and chscp.incident_id (+) = c_b.incident_id;
2688 begin
2689
2690
2691 if p_task_asgn_id is not null then
2692
2693 open c_task_assgn_detail(p_task_asgn_id);
2694 fetch c_task_assgn_detail into
2695 l_incident_id,
2696 l_sr_number,
2697 l_sr_summary,
2698 l_cust_name,
2699 l_cust_address,
2700 l_task_number,
2701 l_task_name,
2702 l_asgm_sts_name,
2703 l_priority,
2704 l_planned_effort,
2705 l_sch_st_date,
2706 l_sch_end_date,
2707 l_task_desc,
2708 l_product_nr,
2709 l_item_serial,
2710 l_item_description,
2711 l_contact_type,
2712 l_contact_party_id;
2713 close c_task_assgn_detail;
2714
2715 l_contact_record := getContactDetail(l_incident_id,
2716 l_contact_type,
2717 l_contact_party_id);
2718
2719 l_task_asgn_record.task_number := l_task_number;
2720 l_task_asgn_record.task_name := l_task_name;
2721 l_task_asgn_record.task_desc := l_task_desc;
2722 l_task_asgn_record.sch_st_date := l_sch_st_date;
2723 l_task_asgn_record.sch_end_date := l_sch_end_date;
2724 l_task_asgn_record.planned_effort := l_planned_effort;
2725 l_task_asgn_record.priority := l_priority;
2726 l_task_asgn_record.asgm_sts_name := l_asgm_sts_name;
2727 l_task_asgn_record.sr_number := l_sr_number;
2728 l_task_asgn_record.sr_summary := l_sr_summary;
2729 l_task_asgn_record.product_nr := l_product_nr;
2730 l_task_asgn_record.item_serial := l_item_serial;
2731 l_task_asgn_record.item_description := l_item_description;
2732 l_task_asgn_record.cust_name := l_cust_name;
2733 l_task_asgn_record.cust_address := l_cust_address;
2734 l_task_asgn_record.contact_name := l_contact_record.contact_name;
2735 l_task_asgn_record.contact_phone := l_contact_record.contact_phone;
2736 l_task_asgn_record.contact_email := l_contact_record.contact_email;
2737
2738 elsif p_task_id is not null and p_task_audit_id is null then
2739
2740 open c_task_detail(p_task_id);
2741 fetch c_task_detail into
2742 l_incident_id,
2743 l_sr_number,
2744 l_sr_summary,
2745 l_cust_name,
2746 l_cust_address,
2747 l_task_number,
2748 l_task_name,
2749 l_priority,
2750 l_planned_effort,
2751 l_sch_st_date,
2752 l_sch_end_date,
2753 l_task_desc,
2754 l_product_nr,
2755 l_item_serial,
2756 l_item_description,
2757 l_contact_type,
2758 l_contact_party_id;
2759 close c_task_detail;
2760
2761 l_contact_record := getContactDetail(l_incident_id,
2762 l_contact_type,
2763 l_contact_party_id);
2764
2765 l_task_asgn_record.task_number := l_task_number;
2766 l_task_asgn_record.task_name := l_task_name;
2767 l_task_asgn_record.task_desc := l_task_desc;
2768 l_task_asgn_record.sch_st_date := l_sch_st_date;
2769 l_task_asgn_record.sch_end_date := l_sch_end_date;
2770 l_task_asgn_record.planned_effort := l_planned_effort;
2771 l_task_asgn_record.priority := l_priority;
2772 l_task_asgn_record.sr_number := l_sr_number;
2773 l_task_asgn_record.sr_summary := l_sr_summary;
2774 l_task_asgn_record.product_nr := l_product_nr;
2775 l_task_asgn_record.item_serial := l_item_serial;
2776 l_task_asgn_record.item_description := l_item_description;
2777 l_task_asgn_record.cust_name := l_cust_name;
2778 l_task_asgn_record.cust_address := l_cust_address;
2779 l_task_asgn_record.contact_name := l_contact_record.contact_name;
2780 l_task_asgn_record.contact_phone := l_contact_record.contact_phone;
2781 l_task_asgn_record.contact_email := l_contact_record.contact_email;
2782
2783 elsif p_task_id is not null and p_task_audit_id is not null then
2784
2785 open c_task_audit_detail(p_task_id, p_task_audit_id);
2786 fetch c_task_audit_detail into
2787 l_incident_id,
2788 l_sr_number,
2789 l_sr_summary,
2790 l_cust_name,
2791 l_cust_address,
2792 l_task_number,
2793 l_task_name,
2794 l_priority,
2795 l_planned_effort,
2796 l_sch_st_date,
2797 l_sch_end_date,
2798 l_task_desc,
2799 l_product_nr,
2800 l_item_serial,
2801 l_item_description,
2802 l_contact_type,
2803 l_contact_party_id,
2804 l_old_sch_st_date,
2805 l_old_sch_end_date;
2806 close c_task_audit_detail;
2807
2808 l_contact_record := getContactDetail(l_incident_id,
2809 l_contact_type,
2810 l_contact_party_id);
2811
2812 l_task_asgn_record.task_number := l_task_number;
2813 l_task_asgn_record.task_name := l_task_name;
2814 l_task_asgn_record.task_desc := l_task_desc;
2815 l_task_asgn_record.sch_st_date := l_sch_st_date;
2816 l_task_asgn_record.sch_end_date := l_sch_end_date;
2817 l_task_asgn_record.planned_effort := l_planned_effort;
2818 l_task_asgn_record.priority := l_priority;
2819 l_task_asgn_record.sr_number := l_sr_number;
2820 l_task_asgn_record.sr_summary := l_sr_summary;
2821 l_task_asgn_record.product_nr := l_product_nr;
2822 l_task_asgn_record.item_serial := l_item_serial;
2823 l_task_asgn_record.item_description := l_item_description;
2824 l_task_asgn_record.cust_name := l_cust_name;
2825 l_task_asgn_record.cust_address := l_cust_address;
2826 l_task_asgn_record.contact_name := l_contact_record.contact_name;
2827 l_task_asgn_record.contact_phone := l_contact_record.contact_phone;
2828 l_task_asgn_record.contact_email := l_contact_record.contact_email;
2829 l_task_asgn_record.old_sch_st_date := l_old_sch_st_date;
2830 l_task_asgn_record.old_sch_end_date := l_old_sch_end_date;
2831
2832 end if;
2833
2834 return l_task_asgn_record;
2835 end;
2836
2837 function getClientTime (p_server_time date,
2838 p_user_id number) return date is
2839 l_client_tz_id number;
2840 l_server_tz_id number;
2841 l_msg_count number;
2842 l_status varchar2(1);
2843 x_client_time date;
2844 l_msg_data varchar2(2000);
2845
2846 begin
2847
2848 IF (fnd_timezones.timezones_enabled <> 'Y') THEN
2849 return p_server_time;
2850 END IF;
2851
2852 l_client_tz_id := to_number(fnd_profile.VALUE_SPECIFIC('CLIENT_TIMEZONE_ID',
2853 p_user_id,
2854 21685,
2855 513,
2856 null,
2857 null));
2858
2859 l_server_tz_id := to_number(fnd_profile.VALUE_SPECIFIC('SERVER_TIMEZONE_ID',
2860 p_user_id,
2861 21685,
2862 513,
2863 null,
2864 null));
2865
2866 HZ_TIMEZONE_PUB.GET_TIME(1.0,
2867 'F',
2868 l_server_tz_id,
2869 l_client_tz_id,
2870 p_server_time,
2871 x_client_time,
2872 l_status,
2873 l_msg_count,
2874 l_msg_data);
2875
2876 return x_client_time;
2877
2878 end;
2879
2880 -- Returns WF ROLE NAME
2881 -- Bug # 5245611
2882 function getWFRole (p_resource_id number) return varchar2 is
2883
2884 l_wf_role_name varchar2(150) := NULL;
2885 cursor c_check_user_name (v_resource_id number) is
2886 select
2887 j.user_name
2888 from
2889 jtf_rs_resource_extns j,
2890 wf_roles w
2891 where
2892 j.resource_id = v_resource_id
2893 and j.user_name = w.name;
2894
2895 begin
2896
2897 open c_check_user_name(p_resource_id);
2898 fetch c_check_user_name into l_wf_role_name;
2899 close c_check_user_name;
2900
2901 if l_wf_role_name is NULL
2902 then
2903 l_wf_role_name := JTF_RS_WF_INTEGRATION_PUB.get_wf_role(p_resource_id);
2904 end if;
2905
2906 return l_wf_role_name;
2907
2908 end;
2909
2910 END csf_alerts_pub;