eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.CSF_ALERTS_PUB

Source


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