[Home] [Help]
PACKAGE BODY: APPS.CS_EA_AUTOGEN_TASKS_PVT
Source
1 package body cs_ea_autogen_tasks_pvt as
2 /* $Header: cseatskb.pls 120.10 2009/06/29 05:34:19 sshilpam ship $ */
3 --------------------------------------------
4 /* this procedure get the task type present in new task table but not
5 present in old task table
6 */
7 FUNCTION Are_task_Attributes_valid(p_task_type_id number,
8 p_task_status_id number,
9 p_task_priority_id number) return varchar2;
10 -- -----------------------------------------------------------------------------
11 -- Modification History:
12 -- Date Name Desc
13 -- ------- -------- ------------------------------------------------------------
14 -- 01/06/06 smisra fixed bug 4871341
15 -- to get task priority, type and status names,
16 -- used respective _vl tables instead of using jtf_tasks_vl
17 -- This was to avoid excessive shared memory used by sql
18 -- -----------------------------------------------------------------------------
19 PROCEDURE start_task_workflow (p_task_id IN NUMBER,
20 p_tsk_typ_attr_dep_id IN NUMBER,
21 p_wf_process IN VARCHAR2,
22 p_workflow_type IN VARCHAR2,
23 p_task_name in varchar2,
24 p_task_desc in varchar2,
25 x_return_status OUT NOCOPY VARCHAR2,
26 x_msg_count OUT NOCOPY NUMBER,
27 x_msg_data OUT NOCOPY VARCHAR2
28 ) IS
29 l_wf_process_id NUMBER;
30 l_itemkey wf_item_activity_statuses.item_key%TYPE;
31 l_owner_user_name fnd_user.user_name%TYPE;
32 l_owner_code jtf_tasks_b.owner_type_code%TYPE;
33 l_owner_id jtf_tasks_b.owner_id%TYPE;
34 l_task_number jtf_tasks_b.task_number%TYPE;
35 l_task_status_name jtf_tasks_v.task_status%type ;
36 l_task_type_name jtf_tasks_v.task_type%type ;
37 l_task_priority_name jtf_tasks_v.task_priority%type ;
38 l_task_status_id jtf_tasks_b.task_status_id%type ;
39 l_task_type_id jtf_tasks_b.task_type_id%type ;
40 l_task_priority_id jtf_tasks_b.task_priority_id%type ;
41 current_record NUMBER;
42 source_text VARCHAR2(200);
43 l_errname varchar2(60);
44 l_errmsg varchar2(2000);
45 l_errstack varchar2(4000);
46
47 CURSOR c_wf_processs_id
48 IS
49 SELECT jtf_task_workflow_process_s.nextval
50 FROM dual;
51
52 BEGIN
53 SAVEPOINT start_task_workflow;
54 x_return_status := fnd_api.g_ret_sts_success;
55
56 OPEN c_wf_processs_id;
57 FETCH c_wf_processs_id INTO l_wf_process_id;
58 CLOSE c_wf_processs_id;
59 l_itemkey := TO_CHAR (p_task_id) || '-' || TO_CHAR (l_wf_process_id);
60
61 wf_engine.createprocess (
62 itemtype => p_workflow_type,
63 itemkey => l_itemkey,
64 process => p_wf_process
65 );
66
67 wf_engine.setitemuserkey (
68 itemtype => p_workflow_type,
69 itemkey => l_itemkey,
70 userkey => p_task_name
71 );
72
73 wf_engine.setitemattrtext (
74 itemtype => p_workflow_type,
75 itemkey => l_itemkey,
76 aname => 'TASK_NAME',
77 avalue => p_task_name
78 );
79
80 wf_engine.setitemattrtext (
81 itemtype => p_workflow_type,
82 itemkey => l_itemkey,
83 aname => 'TASK_DESC',
84 avalue => p_task_desc
85 );
86
87 select task_status_id, task_priority_id , task_type_id, task_number
88 into l_task_status_id, l_task_priority_id , l_task_type_id,
89 l_task_number
90 from jtf_tasks_b where task_id = p_task_id ;
91
92 SELECT name
93 INTO l_task_type_name
94 FROM jtf_task_types_vl
95 WHERE task_type_id = l_task_type_id;
96
97 SELECT name
98 INTO l_task_status_name
99 FROM jtf_task_statuses_vl
100 WHERE task_status_id = l_task_status_id;
101
102 SELECT name
103 INTO l_task_priority_name
104 FROM jtf_task_priorities_vl
105 WHERE task_priority_id = l_task_priority_id;
106
107 wf_engine.setitemattrtext (
108 itemtype => p_workflow_type,
109 itemkey => l_itemkey,
110 aname => 'TASK_NUMBER',
111 avalue => l_task_number
112 );
113
114 wf_engine.setitemattrtext (
115 itemtype => p_workflow_type,
116 itemkey => l_itemkey,
117 aname => 'TASK_STATUS_NAME',
118 avalue => l_task_status_name
119 );
120
121 wf_engine.setitemattrtext (
122 itemtype => p_workflow_type,
123 itemkey => l_itemkey,
124 aname => 'TASK_PRIORITY_NAME',
125 avalue => l_task_priority_name
126 );
127
128 wf_engine.setitemattrtext (
129 itemtype => p_workflow_type,
130 itemkey => l_itemkey,
131 aname => 'TASK_TYPE_NAME',
132 avalue => l_task_type_name
133 );
134
135 wf_engine.setitemattrtext (
136 itemtype => p_workflow_type,
137 itemkey => l_itemkey,
138 aname => 'OWNER_ID',
139 avalue => l_owner_user_name
140 );
141
142 wf_engine.setitemattrnumber (
143 itemtype => p_workflow_type,
144 itemkey => l_itemkey,
145 aname => 'CUG_TASK_DEP_ID',
146 avalue => p_tsk_typ_attr_dep_id
147 );
148
149 wf_engine.startprocess (
150 itemtype => p_workflow_type,
151 itemkey => l_itemkey
152 );
153
154 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
155
156 EXCEPTION
157 WHEN fnd_api.g_exc_unexpected_error THEN
158 ROLLBACK TO start_task_workflow;
159 x_return_status := fnd_api.g_ret_sts_unexp_error;
160 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
161 ---
162 WHEN OTHERS THEN
163 ROLLBACK TO start_task_workflow ;
164 wf_core.get_error(l_errname, l_errmsg, l_errstack);
165 if (l_errname is not null) then
166 fnd_message.set_name('FND', 'WF_ERROR');
167 fnd_message.set_token('ERROR_MESSAGE', l_errmsg);
168 fnd_message.set_token('ERROR_STACK', l_errstack);
169 fnd_msg_pub.add;
170 end if;
171 x_return_status := fnd_api.g_ret_sts_unexp_error;
172 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
173 END;
174 procedure get_new_task_types(
175 p_task_tbl_old in ea_task_table_type,
176 p_task_tbl_new in ea_task_table_type,
177 x_task_types_tbl OUT NOCOPY task_type_table_type) is
178 l_indx1 number;
179 l_indx2 number;
180 l_indx3 number;
181 l_matched_with_old_task varchar2(1);
182 begin
183 l_indx3 := 0;
184 --dbms_output.put_line('Number of New Task:'||to_char(p_task_tbl_new.count));
185 --dbms_output.put_line('Number of Old Task:'||to_char(p_task_tbl_Old.count));
186 for l_indx2 in 1..p_task_tbl_new.count loop
187 l_matched_with_old_task := 'n';
188 for l_indx1 in 1..p_task_tbl_old.count loop
189 if (p_task_tbl_new(l_indx2).task_status_id =
190 p_task_tbl_old(l_indx1).task_status_id and
191 nvl(p_task_tbl_new(l_indx2).private_flag,'xx') =
192 nvl(p_task_tbl_old(l_indx1).private_flag,'xx') and
193 nvl(p_task_tbl_new(l_indx2).publish_flag,'xx') =
194 nvl(p_task_tbl_old(l_indx1).publish_flag,'xx') and
195 nvl(p_task_tbl_new(l_indx2).task_priority_id,-1) =
196 nvl(p_task_tbl_old(l_indx1).task_priority_id,-1) and
197 p_task_tbl_new(l_indx2).task_type_id =
198 p_task_tbl_old(l_indx1).task_type_id and
199 p_task_tbl_new(l_indx2).task_name =
200 p_task_tbl_old(l_indx1).task_name and
201 nvl(p_task_tbl_new(l_indx2).task_description,'x') =
202 nvl(p_task_tbl_old(l_indx1).task_description,'x')
203 ) then
204 l_matched_with_old_task := 'y';
205 exit;
206 end if;
207 end loop; --- old task records loop
208 if (l_matched_with_old_task = 'n') then -- match for new task not found
209 l_indx3 := l_indx3 + 1;
210 x_task_types_tbl(l_indx3) := p_task_tbl_new(l_indx2).task_type_id;
211 end if;
212 end loop; ------ new task records loop
213 end;
214 --------------------------------------------
215 /* this procedure gets a list of task that need to be created for a
216 service request type, extended attribute code and value.
217 There may be more than one task associated with sr type, attr code
218 and valuei, that is why this procedure appends the needed task to
219 task table and increase the task count acordingly
220 */
221 -- -----------------------------------------------------------------------------
222 -- Modification History:
223 -- Date Name Desc
224 -- ------- -------- ------------------------------------------------------------
225 -- 07/26/05 smisra fixed bug 4272460.
226 -- Retrieved owner id and owner type from SR Type, Task Type
227 -- mapping.
228 -- ------- -------- ------------------------------------------------------------
229 procedure get_tasks_for_sr_attribute(
230 p_incident_type_id number,
231 p_sr_ea_attr_code varchar2,
232 p_sr_ea_attr_val varchar2,
233 p_taskrec_table in out nocopy ea_task_table_type,
234 p_task_count in out nocopy number) is
235 --
236 l_task_type_id jtf_task_types_b.task_type_id % type;
237 l_sr_attr_op cug_tsk_typ_attr_deps_vl.sr_attribute_operator % type;
238 l_sr_attr_val_for_Task cug_tsk_typ_attr_deps_vl.sr_attribute_value % type;
239 l_sr_attr_val_for_Task1 cug_tsk_typ_attr_deps_vl.sr_attribute_value % type;
240 cursor c_sr_attr_tasks is
241 select task_type_id,
242 sr_attribute_operator,
243 sr_attribute_value,
244 tsk_typ_attr_dep_id
245 from cug_tsk_typ_attr_deps_vl
246 where incident_type_id = p_incident_type_id
247 and nvl(sr_attribute_code,'-909') = nvl(p_sr_ea_attr_code,'-909')
248 and trunc(sysdate) between nvl(start_date_active,sysdate-1)
249 and nvl(end_date_active ,sysdate+1)
250 ;
251 cursor c_lookup_code is
252 select 1 from fnd_lookups
253 where description = p_sr_ea_attr_val
254 and lookup_code = l_sr_attr_val_for_task
255 and lookup_type in (select sr_attribute_list_name
256 from cug_sr_type_attr_maps_b
257 where incident_type_id = p_incident_type_id
258 and sr_attribute_code = p_sr_ea_attr_code);
259 --
260 -- Cursor to get planned effort and UOM
261 l_rule jtf_task_types_b.rule % type;
262 l_planned_effort jtf_tasks_b.planned_effort % type;
263 l_planned_effort_uom jtf_tasks_b.planned_effort_uom % type;
264 l_workflow jtf_task_types_b.workflow % type;
265 l_workflow_type jtf_task_types_b.workflow_type % type;
266 cursor c_planned_effort is
267 select planned_effort, planned_effort_uom, rule, workflow, nvl(workflow_type,'JTFTASK')
268 from jtf_task_types_b
269 where task_type_id = l_task_type_id
270 /* and trunc(sysdate) between nvl(start_date_active,sysdate-1)
271 and nvl(end_date_active ,sysdate+1)*/
272 ;
273 --
274 -- cursor to task attributes
275 l_tsk_typ_attr_dep_id cug_sr_task_type_dets_b.tsk_typ_attr_dep_id % type;
276 l_task_status_id jtf_tasks_b.task_status_id % type;
277 l_task_priority_id jtf_tasks_b.task_priority_id % type;
278 l_task_name jtf_tasks_tl.task_name % type;
279 l_task_desc jtf_tasks_tl.description % type;
280 l_publish_flag jtf_tasks_b.publish_flag % type;
281 l_private_flag jtf_tasks_b.private_flag % type;
282 CURSOR c_task_attributes IS
283 SELECT
284 task_status_id
285 , task_priority_id
286 , task_name
287 , description
288 , publish_flag
289 , private_flag
290 , owner_type_code
291 , owner_id
292 , assignee_type_code --5686743
293 , assigned_by_id --5686743
294 FROM cug_sr_task_type_dets_vl
295 WHERE tsk_typ_attr_dep_id = l_tsk_typ_attr_dep_id;
296 l_match_found varchar2(1);
297 l_sr_attr_found varchar2(1);
298 l_sr_attr_task_found varchar2(1);
299 l_dummy fnd_lookups.lookup_type % type;
300 l_owner_type_code cug_sr_task_type_dets_vl.owner_type_code % TYPE;
301 l_owner_id cug_sr_task_type_dets_vl.owner_id % TYPE;
302 l_assignee_type_code cug_sr_task_type_dets_vl.owner_type_code % TYPE; --5686743
303 l_assignee_id cug_sr_task_type_dets_vl.owner_id % TYPE; --5686743
304 begin
305 open c_sr_attr_tasks;
306 l_sr_attr_task_found := 'n';
307 loop
308 fetch c_sr_attr_tasks into l_task_type_id,
309 l_sr_attr_op, l_sr_attr_val_for_task,
310 l_tsk_typ_attr_dep_id;
311 if c_sr_attr_tasks % notfound then
312 exit;
313 end if;
314 -- No matching task are found
315 l_match_found := 'n';
316 l_sr_attr_task_found := 'y';
317 -- This code is needed because cug_tsk_type_attr_deps_vl stores llokup_code
318 -- in tl table instead of meaning. so checking existance of lookup_code meaning
319 -- pair in lookup table.
320 if (l_sr_attr_val_for_task is not null) then
321 open c_lookup_code;
322 fetch c_lookup_code into l_dummy;
323 if c_lookup_code %notfound then
324 l_sr_attr_val_for_task := '-99';
325 --dbms_output.put_line('No match found for, code:'||l_sr_attr_val_for_task);
326 --dbms_output.put_line('............. ...Meaning:'||p_sr_ea_attr_val);
327 else
328 l_sr_attr_val_for_task := p_sr_ea_attr_val;
329 --dbms_output.put_line('match found.. ...Meaning:'||p_sr_ea_attr_val);
330 end if;
331 close c_lookup_code;
332 end if;
333 --
334 if ((l_sr_attr_op = 'EQ' and
335 nvl(p_sr_ea_attr_val,'x') = nvl(l_sr_attr_val_for_task,'x')) or
336 l_sr_attr_val_for_task is null) then
337 -- there exists a task type for input sr type, attr code and value
338 -- this may lead to task creation
339 l_match_found := 'y';
340 -- add code for all other operators
341 end if;
342 if (l_match_found = 'y') then
343 OPEN c_task_attributes;
344 FETCH c_task_attributes
345 INTO
346 l_task_status_id
347 , l_task_priority_id
348 , l_task_name
349 , l_task_desc
350 , l_publish_flag
351 , l_private_flag
352 , l_owner_type_code
353 , l_owner_id
354 , l_assignee_type_code --5686743
355 , l_assignee_id --5686743
356 ;
357 if (c_task_attributes%notfound) then
358 --add a error message
359 -- This should never happen because form force this validation
360 fnd_message.set_name ('CS', 'CS_EA_NO_TASK_ATTRIBUTES');
361 fnd_msg_pub.add;
362 close c_task_attributes;
363 close c_sr_attr_tasks;
364 raise fnd_api.g_exc_unexpected_error;
365 end if;
366 close c_task_attributes;
367 p_task_count := p_task_count + 1;
368 p_taskrec_table(p_task_count).task_name := l_task_name;
369 p_taskrec_table(p_task_count).task_description := l_task_desc;
370 p_taskrec_table(p_task_count).task_type_id := l_task_type_id;
371 p_taskrec_table(p_task_count).task_status_id := l_task_status_id;
372 p_taskrec_table(p_task_count).task_priority_id := l_task_priority_id;
373 p_taskrec_table(p_task_count).publish_flag := l_publish_flag;
374 p_taskrec_table(p_task_count).private_flag := l_private_flag;
375 p_taskrec_table(p_task_count).tsk_typ_attr_dep_id := l_tsk_typ_attr_dep_id;
376 p_taskrec_table(p_task_count).owner_type_code := l_owner_type_code;
377 p_taskrec_table(p_task_count).owner_id := l_owner_id;
378 p_taskrec_table(p_task_count).assignee_type_code := l_assignee_type_code; --5686743
379 p_taskrec_table(p_task_count).assignee_id := l_assignee_id; --5686743
380
381 p_taskrec_table(p_task_count).source_object_type_code := 'SR';
382 --p_taskrec_table(p_task_count).source_object_id := p_request_id;
383 --p_taskrec_table(p_task_count).source_object_name := p_incident_number;
384 -- get planned fields
385 open c_planned_effort;
386 fetch c_planned_effort into l_planned_effort,
387 l_planned_effort_uom, l_rule, l_workflow, l_workflow_type;
388 if (c_planned_effort%notfound) then
389 fnd_message.set_name ('JTF', 'JTF_TASK_INVALID_TYPE_ID');
390 fnd_message.set_token('P_TASK_TYPE_ID',l_task_type_id);
391 fnd_msg_pub.add;
392 close c_planned_effort;
393 close c_sr_attr_tasks;
394 raise fnd_api.g_exc_unexpected_error;
395 end if;
396 close c_planned_effort;
397 p_taskrec_table(p_task_count).workflow := l_workflow;
398 p_taskrec_table(p_task_count).workflow_type := l_workflow_type;
399 -- add planned fields to task record
400 p_taskrec_table(p_task_count).planned_start_date := sysdate;
401 p_taskrec_table(p_task_count).planned_effort := l_planned_effort;
402 p_taskrec_table(p_task_count).planned_effort_uom :=
403 l_planned_effort_uom;
404 if (l_rule = 'DISPATCH') then
405 p_taskrec_table(p_task_count).field_service_task_flag := 'Y';
406 else
407 p_taskrec_table(p_task_count).field_service_task_flag := 'N';
408 end if;
409 --dbms_output.put_line('Status:'||to_char(l_task_status_id));
410 --dbms_output.put_line('type:'||to_char(l_task_type_id));
411 --dbms_output.put_line('priority:'||to_char(l_task_priority_id));
412 --dbms_output.put_line('Desc:'||to_char(l_task_desc));
413 --dbms_output.put_line('name:'||to_char(l_task_name));
414 --dbms_output.put_line('Planned Eff:'||to_char(l_planned_effort));
415 --dbms_output.put_line('Planned EffUOM:'||to_char(l_planned_effort_uom));
416 else
417 null;
418 --dbms_output.put_line('No Match found');
419 /*
420 ***/
421 end if; -- if match found
422 --dbms_output.put_line('=======================================');
423 end loop;
424 close c_sr_attr_tasks;
425 /* 10/28/2003
426 this may happen for check of task types with null attributes.
427 if (l_sr_attr_task_found = 'n') then
428 fnd_message.set_name ('CS', 'CS_EA_NO_CONFIGURED_TASKS');
429 fnd_message.set_token('P_SR_TYPE',p_incident_type_id);
430 fnd_message.set_token('P_EA_CODE',p_sr_ea_attr_code);
431 fnd_msg_pub.add;
432 end if;
433 */
434 end get_tasks_for_sr_attribute;
435 --------------------------------------------
436 procedure get_affected_tasks (
437 p_api_version in number,
438 p_init_msg_list in varchar2 ,
439 p_incident_type_id_old in number,
440 p_incident_type_id_new in number,
441 p_ea_sr_attr_tbl in extended_attribute_table_type,
442 x_tasks_affected_flag out nocopy varchar2,
443 x_task_type_tbl out nocopy task_type_table_type,
444 x_return_status out nocopy varchar2,
445 x_msg_count out nocopy number,
446 x_msg_data out nocopy varchar2
447 ) is
448 l_task_tbl_new ea_task_table_type;
449 l_task_tbl_old ea_task_table_type;
450 l_task_count_new number;
451 l_task_count_old number;
452 begin
453 --
454 -- initialize message list
455 if fnd_api.to_boolean (p_init_msg_list) then
456 fnd_msg_pub.initialize;
457 end if;
458 --
459 -- check API version
460 if (p_api_version <> 1) then
461 fnd_message.set_name ('CS', 'CS_EA_US_INVALID_API_VER');
462 fnd_msg_pub.add;
463 raise fnd_api.g_exc_unexpected_error;
464 end if;
465 --
466 -- check old incident type ID
467 if (p_incident_type_id_old is null) then
468 fnd_message.set_name ('CS', 'CS_EA_NULL_OLD_INCIDENT_TYPE');
469 fnd_msg_pub.add;
470 raise fnd_api.g_exc_unexpected_error;
471 end if;
472 --
473 -- check new incident type ID
474 if (p_incident_type_id_new is null) then
475 fnd_message.set_name ('CS', 'CS_EA_NULL_NEW_INCIDENT_TYPE');
476 fnd_msg_pub.add;
477 raise fnd_api.g_exc_unexpected_error;
478 end if;
479 ----------------------------------------------------------
480 l_task_count_new := 0;
481 l_task_count_old := 0;
482 /** 11/18/2003 smisra
483 As per requirement from UI team, disabling this part.
484 Now this procedure will always return tasks needed for passed
485 attribute code - value pairs
486
487 if (p_incident_type_id_old = p_incident_type_id_new) then
488 for l_indx in 1..p_ea_sr_attr_tbl.count loop
489 --dbms_output.put_line('Inside Get Affected Task:'||to_char(l_indx));
490 if (nvl(p_ea_sr_attr_tbl(l_indx).sr_attribute_value_old,'x') <>
491 nvl(p_ea_sr_attr_tbl(l_indx).sr_attribute_value_new,'x') ) then
492 get_tasks_for_sr_attribute(
493 p_incident_type_id_old,
494 p_ea_sr_attr_tbl(l_indx).sr_attribute_code_old,
495 p_ea_sr_attr_tbl(l_indx).sr_attribute_value_old,
496 l_task_tbl_old,
497 l_task_count_old);
498 get_tasks_for_sr_attribute(
499 p_incident_type_id_new,
500 p_ea_sr_attr_tbl(l_indx).sr_attribute_code_new,
501 p_ea_sr_attr_tbl(l_indx).sr_attribute_value_new,
502 l_task_tbl_new,
503 l_task_count_new);
504 end if;
505 end loop;
506 **/
507 /*
508 no need to call get_tasks_for_sr_attributes with null value for code and value
509 as such task are independed on attrbiure values. we are trying to find out
510 task needs due to change in attribute code values
511 *************************************/
512 --else
513 for l_indx in 1..p_ea_sr_attr_tbl.count loop
514 if (p_ea_sr_attr_tbl(l_indx).sr_attribute_code_new is not null) then
515 get_tasks_for_sr_attribute(
516 p_incident_type_id_new,
517 p_ea_sr_attr_tbl(l_indx).sr_attribute_code_new,
518 p_ea_sr_attr_tbl(l_indx).sr_attribute_value_new,
519 l_task_tbl_new,
520 l_task_count_new);
521 end if;
522 end loop;
523 -- this call will get all configured tasks that have attribute as null
524 -- such tasks are to be created whenever an SR of particular type is created
525 get_tasks_for_sr_attribute(
526 p_incident_type_id_new,
527 null,
528 null,
529 l_task_tbl_new,
530 l_task_count_new);
531 --end if;
532 --dbms_output.put_line('Getting the new tasks type..');
533 get_new_task_types(
534 l_task_tbl_old ,
535 l_task_tbl_new ,
536 x_task_type_tbl ) ;
537 if (x_task_type_tbl.count > 0 ) then
538 x_tasks_affected_flag := 'Y';
539 else
540 x_tasks_affected_flag := 'N';
541 end if;
542 /*****
543 dbms_output.put_line('total new task types:'|| to_char(x_task_type_tbl.count));
544 for l_indx in 1..x_task_type_tbl.count loop
545 dbms_output.put_line(to_char(x_task_type_tbl(l_indx)));
546 end loop;
547 ****/
548 --
549 -- Exception handling
550 --
551 EXCEPTION
552 WHEN fnd_api.g_exc_unexpected_error THEN
553 x_return_status := fnd_api.g_ret_sts_unexp_error;
554 fnd_msg_pub.count_and_get (
555 p_count => x_msg_count,
556 p_data => x_msg_data);
557 WHEN OTHERS THEN
558 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
559 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
560 fnd_msg_pub.add;
561 x_return_status := fnd_api.g_ret_sts_unexp_error;
562 fnd_msg_pub.count_and_get (
563 p_count => x_msg_count,
564 p_data => x_msg_data);
565 end get_affected_tasks;
566 --------------------------------------------
567 procedure get_extnd_attr_tasks (
568 p_api_version in number,
569 p_init_msg_list in varchar2 ,
570 p_sr_rec in CS_ServiceRequest_pub.service_request_rec_type,
571 p_request_id in number ,
572 p_incident_number in varchar2 ,
573 p_sr_attributes_tbl in EA_SR_ATTR_TABLE_TYPE,
574 x_return_status out nocopy varchar2,
575 x_msg_count out nocopy number,
576 x_msg_data out nocopy varchar2,
577 x_task_rec_table out nocopy EA_task_table_type) is
578 -- local variables
579 l_sr_attr_code cug_incidnt_attr_vals_vl.sr_attribute_code % type;
580 l_sr_attr_val cug_incidnt_attr_vals_vl.sr_attribute_value% type;
581 --
582 l_sr_attr_found varchar2(1);
583 l_indx number;
584 begin
585 --
586 -- initialize message list
587 if fnd_api.to_boolean (p_init_msg_list) then
588 fnd_msg_pub.initialize;
589 end if;
590 --
591 -- check API version
592 if (p_api_version <> 1) then
593 fnd_message.set_name ('CS', 'CS_EA_US_INVALID_API_VER');
594 fnd_msg_pub.add;
595 raise fnd_api.g_exc_unexpected_error;
596 end if;
597 --
598 -- Check Service request id
599 if (p_request_id is null) then
600 fnd_message.set_name ('CS', 'CS_EA_NULL_REQUEST_ID');
601 fnd_msg_pub.add;
602 raise fnd_api.g_exc_unexpected_error;
603 end if;
604 -- Get Service Extended Attributes
605 l_sr_attr_found := 'n';
606 l_indx := 0;
607 --dbms_output.put_line('Total Attr:'||to_char(p_sr_attributes_tbl.count));
608 if (p_sr_attributes_tbl.count > 0) then
609 for l_loop_indx in p_sr_attributes_tbl.first..p_sr_attributes_tbl.last loop
610 --dbms_output.put_line('loop indx:' ||to_char(l_loop_indx));
611 l_sr_attr_code := p_sr_attributes_tbl(l_loop_indx).sr_attribute_code;
612 l_sr_attr_val := p_sr_attributes_tbl(l_loop_indx).sr_attribute_value;
613 get_tasks_for_sr_attribute(p_sr_rec.type_id,
614 l_sr_attr_code, l_sr_attr_val,
615 x_task_rec_table, l_indx);
616 --dbms_output.put_line('Index Out,loop indx:'||to_char(l_indx) ||',' ||to_char(l_loop_indx));
617 end loop;
618 end if;
619 -- There may be some tasks configured with attrbiute code as NULL.
620 -- get all the task that have NULL attribute code.
621 -- These tasks are to be created irrespective of attribute codes and values
622 -- These tasks are basically depends on SR Type
623 -- Get the count of message and message into out variables
624 --dbms_output.put_line('before get_task_for_sr_attribute call');
625 get_tasks_for_sr_attribute(p_sr_rec.type_id,
626 null, null,
627 x_task_rec_table, l_indx);
628 --dbms_output.put_line('After get_task_for_sr_attribute call');
629 x_return_status := fnd_api.g_ret_sts_success;
630 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
631 --
632 -- Exception handling
633 --
634 EXCEPTION
635 WHEN fnd_api.g_exc_unexpected_error THEN
636 --dbms_output.put_line('get_extnd g_exc_unexpected error');
637 x_return_status := fnd_api.g_ret_sts_unexp_error;
638 fnd_msg_pub.count_and_get (
639 p_count => x_msg_count,
640 p_data => x_msg_data);
641 WHEN OTHERS THEN
642 --dbms_output.put_line('get_extnd others error');
643 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
644 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
645 fnd_msg_pub.add;
646 x_return_status := fnd_api.g_ret_sts_unexp_error;
647 fnd_msg_pub.count_and_get (
648 p_count => x_msg_count,
649 p_data => x_msg_data);
650 end get_extnd_attr_tasks;
651
652 -- -----------------------------------------------------------------------------
653 -- Modification History:
654 -- Date Name Desc
655 -- ------- -------- ------------------------------------------------------------
656 -- 07/26/05 smisra fixed bug 4477767.
657 -- passed p_date_selected as NULL to task API. If this
658 -- parameter is 'D' then it may cause certain task validations
659 -- to fail and auto task process in not in a position to pass
660 -- those details
661 -- 07/26/05 smisra fixed bug 4272460.
662 -- used owner id, owner type from SR Type, Task Type mapping
663 -- if assignment manager can not determine task owner
664 -- 08/19/05 smisra Fixed bug 4272460 |
665 -- put call to auto tak assignment under comment.
666 -- when decision to use Assignment is manager is made, that
667 -- call can be uncommneted.
668 -- 07/12/06 romehrot Bug 5686743
669 -- Added to code to populate assignee information from setup form.
670
671 -- -----------------------------------------------------------------------------
672 procedure create_extnd_attr_tasks (
673 p_api_version in number,
674 p_init_msg_list in varchar2 ,
675 p_commit in varchar2 ,
676 p_sr_rec in CS_ServiceRequest_pub.service_request_rec_type,
677 p_sr_attributes_tbl in EA_SR_ATTR_TABLE_TYPE,
678 p_request_id in number ,
679 p_incident_number in varchar2 ,
680 x_return_status OUT NOCOPY varchar2,
681 x_msg_count OUT NOCOPY number,
682 x_msg_data OUT NOCOPY varchar2,
683 x_auto_task_gen_attempted OUT NOCOPY varchar2,
684 x_field_service_Task_created OUT NOCOPY varchar2) is
685 l_task_rec_tbl ea_task_table_type;
686 l_task_attr_rec cs_sr_task_autoassign_pkg.Sr_Task_rec_type;
687 l_indx number;
688 l_task_id jtf_tasks_b.task_id % type;
689 l_login_id jtf_notes_b.last_update_login % type;
690 l_user_id jtf_notes_b.last_updated_by % type;
691 l_obj_version cs_incidents_all_b.object_version_number % type;
692 l_owner_type cs_incidents_all_b.resource_type % type;
693 l_owner_id cs_incidents_all_b.incident_owner_id % type;
694 l_owner_group_id cs_incidents_all_b.owner_group_id % type;
695 l_last_updated_by cs_incidents_all_b.last_updated_by % type;
696 l_group_type varchar2(50);
697 l_sr_rec cs_servicerequest_pub.service_request_rec_type ;
698 l_task_owner_type jtf_tasks_b.owner_type_code % type;
699 l_task_owner_id jtf_tasks_b.owner_id%type;
700 l_location_id cs_incidents_all_b.incident_location_id % type;
701 l_dummy varchar2(80);
702 l_address_id NUMBER;
703 l_customer_id NUMBER := p_sr_rec.customer_id;
704 l_task_assignment_id Number; --5686743
705 -- Simplex
706 -- local variable and exception declarations for Simplex Enhancement
707 l_prof_val VARCHAR(1);
708 l_date_selected VARCHAR2(1) := null;
709 l_temp NUMBER(20,4);
710 l_api_name VARCHAR2(100) := 'cs_ea_autogen_tasks_pvt.create_extnd_attr_tasks';
711 l_conv_rate NUMBER(30,6);
712 l_planned_effort NUMBER(30,6):= 0;
713 l_task_type_name VARCHAR2(100) := null;
714 l_planned_uom_value varchar2(30); -- 12.1.2 SHACHOUD
715 l_planned_effort_value number; -- 12.1.2 SHACHOUD
716
717 e_date_pair_exception EXCEPTION ;
718 e_planned_effort_val_exception EXCEPTION ;
719 e_party_site_exception EXCEPTION;
720
721 cursor c_task_type_name(l_task_type_id IN NUMBER) IS
722 select name
723 from jtf_task_types_vl
724 where task_type_id = l_task_type_id;
725
726 -- end Simplex
727
728 l_planned_end_date DATE; -- 12.1.2 SR TASK ENHANCEMENTS PROJECT
729 l_owner_territory_id NUMBER; -- 12.1.3 Task Enh Proj
730
731 begin
732
733 savepoint create_extnd_attr_task_pvt;
734 l_sr_rec := p_sr_rec;
735 x_auto_task_gen_attempted := 'N';
736 x_field_service_task_created := 'N';
737
738 --dbms_output.put_line('Before get_extnd_attrtask');
739 get_extnd_attr_tasks (
740 p_api_version => p_api_version,
741 p_init_msg_list => p_init_msg_list,
742 p_sr_rec => p_sr_rec,
743 p_request_id => p_request_id,
744 p_incident_number => p_incident_number,
745 p_sr_attributes_tbl => p_sr_attributes_tbl,
746 x_return_status => x_return_status,
747 x_msg_count => x_msg_count,
748 x_msg_data => x_msg_data,
749 x_task_rec_table => l_task_rec_tbl);
750 --dbms_output.put_line('Return Status(get_extnd_attr_tasks):'||x_return_status|| ':');
751 -- Create Tasks
752
753 if (x_return_status = fnd_api.g_ret_sts_success) then
754 --l_login_id := fnd_global.login_id;
755 --l_user_id := fnd_global.user_id ;
756 -- Task api does not take user ids as parameter
757
758 -- Simplex
759 -- Get the value for the profile option 'Service : Apply State Restriction on Tasks'
760 -- to decide the enabling/disabling of task state restrictions
761
762 -- in order to fix 4477767, task restriction can not be applied to auto tasks
763 -- FND_PROFILE.Get('CS_SR_ENABLE_TASK_STATE_RESTRICTIONS',l_prof_val);
764
765 -- end of simplex
766
767
768 for l_indx in 1..l_task_rec_tbl.count loop
769 --dbms_output.put_line('Going through the task table:'||to_char(l_indx));
770 -- do validations for field service task
771
772 if (Are_Task_Attributes_valid(l_task_rec_tbl(l_indx).task_type_id,
773 l_task_rec_tbl(l_indx).task_status_id,
774 l_task_rec_tbl(l_indx).task_priority_id) = 'Y') then
775 -- commented the following code for 12.1.2 SR task Enhancement project
776 --l_task_rec_tbl(l_indx).planned_end_date := nvl(p_sr_rec.obligation_date,sysdate);
777
778 if (l_task_rec_tbl(l_indx).field_service_task_flag = 'Y') then
779 -- no need to validate type id, status, task_name as these are not null
780 -- in database
781 -- 1st check: Location as party site -- Removed -- Modified to ensure incident location is NOT NULL
782
783 IF (l_task_rec_tbl(l_indx).field_service_task_flag = 'Y') THEN
784
785 IF p_sr_rec.incident_location_id IS NULL THEN
786 RAISE e_party_site_exception ;
787 END IF ;
788
789 END IF ;
790
791
792 -- 2nd check: Planed End Date
793 /*
794 if (p_sr_rec.obligation_date is null) then
795 fnd_message.set_name ('CS', 'CS_EA_NO_PLANNED_END_DATE');
796 fnd_msg_pub.add;
797 x_return_status := fnd_api.g_ret_sts_error;
798 raise fnd_api.g_exc_error;
799 else
800 */
801 --end if;
802 -- 3rd check: Planned Effort
803 if (l_task_rec_tbl(l_indx).planned_effort is null) then
804 fnd_message.set_name ('CS', 'CS_EA_NO_PLANNED_EFFORT');
805 fnd_message.set_token('P_TYPE_ID',l_task_rec_tbl(l_indx).task_type_id);
806 fnd_msg_pub.add;
807 x_return_status := fnd_api.g_ret_sts_error;
808 raise fnd_api.g_exc_error;
809 end if;
810
811 -- 4th check: Planned Effort UOM
812 if (l_task_rec_tbl(l_indx).planned_effort_uom is null) then
813 fnd_message.set_name ('CS', 'CS_EA_NO_PLANNED_EFFORT_UOM');
814 fnd_message.set_token('P_TYPE_ID',l_task_rec_tbl(l_indx).task_type_id);
815 fnd_msg_pub.add;
816 x_return_status := fnd_api.g_ret_sts_error;
817 raise fnd_api.g_exc_error;
818 end if;
819 -- no need to check task name, status, type and planned start date
820 -- as these can not be null. these are not null in respective
821 -- source tables. planned_start_date is always set to sysdate
822
823 end if;
824 -- end of field service task validation
825 -- Get Resource_id for new tasks.
826 l_task_attr_rec.task_type_id := l_task_rec_tbl(l_indx).task_type_id;
827 l_task_attr_rec.task_status_id := l_task_rec_tbl(l_indx).task_status_id;
828 l_task_attr_rec.task_priority_id := l_task_rec_tbl(l_indx).task_priority_id;
829 l_task_owner_type := l_task_rec_tbl(l_indx).owner_type_code;
830 l_task_owner_id := l_task_rec_tbl(l_indx).owner_id;
831
832 /**********************************************************************
833 task assignment will be based on SR Type, Task Type setup only for time being
834 when decision to use task assignment based on territory is made */
835
836 -- 12.1.3 Task Enh Proj
837 If l_task_owner_type is null or l_task_owner_id is null then
838 l_sr_rec.type_id := null;
839 cs_sr_task_autoassign_pkg.assign_task_resource(
840 p_api_version => 1,
841 p_init_msg_list => fnd_api.g_false,
842 p_commit => fnd_api.g_false,
843 p_incident_id => p_request_id,
844 p_service_request_rec => l_sr_rec,
845 p_task_attribute_rec => l_task_attr_rec,
846 x_owner_group_id => l_owner_group_id,
847 x_owner_type => l_owner_type,
848 x_group_type => l_group_type,
849 x_owner_id => l_owner_id,
850 x_territory_id => l_owner_territory_id ,
851 x_return_status => x_return_status,
852 x_msg_count => x_msg_count,
853 x_msg_data => x_msg_data);
854 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
855 fnd_msg_pub.set_search_name('CS','CS_SR_TASK_NO_OWNER');
856 fnd_msg_pub.set_search_token('API_NAME','CS_SR_TASK_AUTOASSIGN_PKG.Assign_Task_Resource');
857 fnd_message.set_name ('CS', 'CS_EA_ASSIGN_TASK_ERROR');
858 fnd_message.set_token('TASK_NAME',l_task_rec_tbl(l_indx).task_name);
859 l_dummy := fnd_msg_pub.change_msg;
860 raise fnd_api.g_exc_error;
861
862 ELSE
863 IF (l_owner_type is null) then
864 l_task_owner_type := l_group_type;
865 l_task_owner_id := l_owner_group_id;
866 ELSE
867 l_task_owner_type := l_owner_type;
868 l_task_owner_id := l_owner_id;
869 END IF;
870 END IF;
871 END IF;
872 -- End 12.1.3 Task enh proj
873 /******************************************************************************/
874
875 x_auto_task_gen_attempted := 'Y';
876 -- Since location id can be null for task but task takes only
877 -- party sites. so if location type is not party site, set location to null
878 if (nvl(p_sr_rec.incident_location_type,'x') = 'HZ_PARTY_SITE' ) then
879 l_address_id := p_sr_rec.incident_location_id;
880 l_customer_id := p_sr_rec.customer_id ;
881 elsif (nvl(p_sr_rec.incident_location_type,'x')) = 'HZ_LOCATION' THEN
882 l_location_id := p_sr_rec.incident_location_id;
883 l_customer_id := null;
884 else
885 l_address_id := null;
886 l_location_id := null;
887 l_customer_id := p_sr_rec.customer_id;
888 end if;
889
890 -- Simplex
891 -- The below validations should be done every tasks in the task template group and
892 -- hence the validations are inside the loop
893
894 -- Enable task state restrictions depending on the profile value
895 -- 'Service : Apply State Restriction on Tasks'
896 --
897 --IF ( l_prof_val = 'Y') THEN
898 -- l_date_selected := 'D';
899 --END IF;
900
901 -- 12.1.2 SR Task Enhancement project
902 -- Get the Planned End date based on the Profile
903 l_planned_uom_value := l_task_rec_tbl(l_indx).planned_effort_uom;
904 l_planned_effort_value := l_task_rec_tbl(l_indx).planned_effort;
905
906 if ( l_planned_uom_value = 'DAY') then
907 l_planned_effort_value := l_planned_effort_value +1;
908 end if;
909 CS_AutoGen_Task_PVT.Default_Planned_End_Date(p_sr_rec.obligation_date,
910 p_sr_rec.exp_resolution_date,
911 l_planned_uom_value,
912 l_planned_effort_value,
913 l_planned_end_date);
914
915 l_task_rec_tbl(l_indx).planned_end_date := l_planned_end_date;
916 -- End of 12.1.2 project code
917
918 -- The palnned start date and planned end date should appear in pair.
919 -- If not,exception is thrown
920 IF ( ( (l_task_rec_tbl(l_indx).planned_start_date IS NOT NULL AND
921 l_task_rec_tbl(l_indx).planned_start_date <> FND_API.G_MISS_DATE)
922 AND
923 (l_task_rec_tbl(l_indx).planned_end_date IS NULL OR
924 l_task_rec_tbl(l_indx).planned_end_date = FND_API.G_MISS_DATE)
925 )
926 OR
927 ( (l_task_rec_tbl(l_indx).planned_end_date IS NOT NULL AND
928 l_task_rec_tbl(l_indx).planned_end_date <> FND_API.G_MISS_DATE)
929 AND
930 (l_task_rec_tbl(l_indx).planned_start_date IS NULL OR
931 l_task_rec_tbl(l_indx).planned_start_date = FND_API.G_MISS_DATE)
932 )
933 )THEN
934
935 open c_task_type_name(l_task_rec_tbl(l_indx).task_type_id);
936 fetch c_task_type_name into l_task_type_name;
937 close c_task_type_name;
938
939 fnd_message.set_name ('CS', 'CS_EA_DATE_PAIR_ERROR');
940 fnd_message.set_token('TASK_TYPE',l_task_type_name);
941 fnd_message.set_token('API_NAME',l_api_name);
942 fnd_msg_pub.add;
943 x_return_status := fnd_api.g_ret_sts_error;
944 raise fnd_api.g_exc_error;
945 END IF;
946
947 -- no validations for scheduled and actual dates as the parameters are not passed
948
949
950 -- The enabling/disabling of state restrictions is based on tke profile option
951 -- 'Service : Apply State Restriction on Tasks' and the value is stored in the
952 -- local variable l_date_selected
953
954 jtf_tasks_pub.create_task (
955 p_api_version => 1.0,
956 p_init_msg_list => fnd_api.g_false,
957 p_commit => fnd_api.g_false,
958 p_task_name => l_task_rec_tbl(l_indx).task_name,
959 p_description => l_task_rec_tbl(l_indx).task_description,
960 p_task_type_id => l_task_rec_tbl(l_indx).task_type_id,
961 p_task_status_id => l_task_rec_tbl(l_indx).task_status_id,
962 p_task_priority_id => l_task_rec_tbl(l_indx).task_priority_id,
963 p_owner_id => l_task_owner_id,
964 p_owner_type_code => l_task_owner_type,
965 p_planned_start_date => l_task_rec_tbl(l_indx).planned_start_date,
966 p_planned_end_date => l_task_rec_tbl(l_indx).planned_end_date,
967 p_planned_effort => l_task_rec_tbl(l_indx).planned_effort,
968 p_planned_effort_uom => l_task_rec_tbl(l_indx).planned_effort_uom,
969 p_customer_id => l_customer_id , --p_sr_rec.customer_id,
970 p_address_id => l_address_id,
971 p_category_id => NULL,
972 p_source_object_id => p_request_id,
973 p_source_object_name => p_incident_number,
974 p_source_object_type_code => 'SR',
975 p_date_selected => l_date_selected, -- simplex 'D',
976 p_private_flag => l_task_rec_tbl(l_indx).private_flag,
977 p_publish_flag => l_task_rec_tbl(l_indx).publish_flag,
978 p_location_id => l_location_id,
979 x_return_status => x_return_status,
980 x_msg_count => x_msg_count,
981 x_msg_data => x_msg_data,
982 x_task_id => l_task_id)
983 ;
984 --dbms_output.put_line('after Create Task:'||x_return_status);
985 if (x_return_status <> fnd_api.g_ret_sts_success) then
986 /***
987 dbms_output.put_line(l_task_rec_tbl(l_indx).task_name);
988 dbms_output.put_line(l_task_rec_tbl(l_indx).task_description);
989 dbms_output.put_line(l_task_rec_tbl(l_indx).task_type_id);
990 dbms_output.put_line(l_task_rec_tbl(l_indx).task_status_id);
991 dbms_output.put_line(l_task_rec_tbl(l_indx).task_priority_id);
992 dbms_output.put_line(to_char(l_task_rec_tbl(l_indx).planned_start_date,'dd-mon-yyyy hh24:mi:ss'));
993 dbms_output.put_line(to_char(l_task_rec_tbl(l_indx).planned_end_date,'dd-mon-yyyy hh24:mi:ss'));
994 dbms_output.put_line(l_task_rec_tbl(l_indx).planned_effort);
995 dbms_output.put_line(l_task_rec_tbl(l_indx).planned_effort_uom);
996 dbms_output.put_line(p_sr_rec.incident_location_id);
997 dbms_output.put_line(p_request_id);
998 dbms_output.put_line(p_incident_number);
999 dbms_output.put_line(l_task_rec_tbl(l_indx).private_flag);
1000 dbms_output.put_line(l_task_rec_tbl(l_indx).publish_flag);
1001 dbms_output.put_line(':'||to_char(l_owner_id));
1002 dbms_output.put_line('Owner Type:'||l_owner_type||':');
1003 dbms_output.put_line(':'||to_char(l_owner_group_id));
1004 dbms_output.put_line('Owner Type:'||l_group_type||':');
1005 dbms_output.put_line('====');
1006 fnd_message.set_name ('CS', 'CS_EA_CREATE_TASK_API_ERROR');
1007 fnd_msg_pub.add;
1008 *****/
1009 raise fnd_api.g_exc_error;
1010 end if;
1011 if (l_task_rec_tbl(l_indx).field_service_task_flag = 'Y') then
1012 x_field_service_task_created := 'Y';
1013 end if;
1014 /* Start : 5686743 */
1015 If l_task_rec_tbl(l_indx).assignee_id IS NOT NULL Then
1016 jtf_task_assignments_pub.create_task_assignment(
1017 p_api_version => 1.0,
1018 p_init_msg_list => cs_core_util.get_g_true,
1019 p_commit => cs_core_util.get_g_false,
1020 p_task_id => l_task_id,
1021 p_resource_type_code => l_task_rec_tbl(l_indx).assignee_type_code,
1022 p_resource_id => l_task_rec_tbl(l_indx).assignee_id,
1023 p_assignment_status_id => fnd_profile.value('JTF_TASK_DEFAULT_ASSIGNEE_STATUS'),
1024 x_return_status => x_return_status,
1025 x_msg_count => x_msg_count,
1026 x_msg_data => x_msg_data,
1027 x_task_assignment_id => l_task_assignment_id);
1028
1029 If (x_return_status <> fnd_api.g_ret_sts_success) Then
1030 raise fnd_api.g_exc_error;
1031 End if;
1032 End If;
1033 /* End : 5686743 */
1034 if (l_task_rec_tbl(l_indx).workflow is not null) then
1035 start_task_workflow (l_task_id ,
1036 l_task_rec_tbl(l_indx).tsk_typ_attr_dep_id ,
1037 l_task_rec_tbl(l_indx).workflow ,
1038 l_task_rec_tbl(l_indx).workflow_type ,
1039 l_task_rec_tbl(l_indx).task_name,
1040 l_task_rec_tbl(l_indx).task_description,
1041 x_return_status ,
1042 x_msg_count ,
1043 x_msg_data );
1044 if (x_return_status <> fnd_api.g_ret_sts_success) then
1045 fnd_message.set_name ('CS', 'CS_EA_START_WORKFLOW_ERROR');
1046 fnd_msg_pub.add;
1047 raise fnd_api.g_exc_error;
1048 end if;
1049 end if;
1050 else
1051 fnd_msg_pub.initialize;
1052 end if; -- check for validity of task attributes such as type, status, priority
1053 end loop;
1054 end if;
1055 -- All task created
1056 if fnd_api.to_boolean (p_commit) then
1057 commit;
1058 end if;
1059 --
1060 -- Exception handling
1061 --
1062 --raise_application_error(-20001,'For testing msg JTF_TASK_UNKNOWN_ERROR');
1063 EXCEPTION
1064 WHEN e_party_site_exception THEN
1065 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1066 p_data => x_msg_data );
1067 FND_MESSAGE.SET_NAME('CS','CS_EA_NO_PARTY_SITE');
1068 FND_MSG_PUB.ADD;
1069 x_return_status := FND_API.G_RET_STS_ERROR ;
1070 WHEN fnd_api.g_exc_error THEN
1071 rollback to create_extnd_attr_task_pvt;
1072 x_return_status := fnd_api.g_ret_sts_error;
1073 fnd_msg_pub.count_and_get (
1074 p_count => x_msg_count,
1075 p_data => x_msg_data);
1076 WHEN fnd_api.g_exc_unexpected_error THEN
1077 rollback to create_extnd_attr_task_pvt;
1078 x_return_status := fnd_api.g_ret_sts_unexp_error;
1079 fnd_msg_pub.count_and_get (
1080 p_count => x_msg_count,
1081 p_data => x_msg_data);
1082 WHEN OTHERS THEN
1083 rollback to create_extnd_attr_task_pvt;
1084 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
1085 fnd_message.set_token ('P_TEXT', SQLERRM);
1086 fnd_msg_pub.add;
1087 x_return_status := fnd_api.g_ret_sts_unexp_error;
1088 fnd_msg_pub.count_and_get (
1089 p_count => x_msg_count,
1090 p_data => x_msg_data);
1091 end create_extnd_attr_tasks;
1092 --------------------------------------------------
1093 FUNCTION Are_task_Attributes_valid(p_task_type_id number,
1094 p_task_status_id number,
1095 p_task_priority_id number) return varchar2 is
1096 l_type jtf_tasks_v.task_type % type;
1097 l_status jtf_tasks_v.task_status % type;
1098 l_priority jtf_tasks_v.task_priority % type;
1099 l_type_id number;
1100 l_status_id number;
1101 l_priority_id number;
1102 x_return_status varchar2(10);
1103 BEGIN
1104 jtf_task_utl.validate_task_type ( p_task_type_id => p_task_type_id,
1105 p_task_type_name => l_type,
1106 x_return_status => x_return_status,
1107 x_task_type_id => l_type_id
1108 );
1109
1110 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1111 return 'N';
1112 END IF;
1113
1114 IF l_type_id IS NULL THEN
1115 return 'N';
1116 END IF;
1117
1118 -------
1119 ------- Validate Task Status
1120 -------
1121 IF l_type_id = '22' THEN
1122 l_type := 'ESCALATION';
1123 ELSE
1124 l_type := 'TASK';
1125 END IF;
1126
1127 jtf_task_utl.validate_task_status ( p_task_status_id => p_task_status_id,
1128 p_task_status_name => l_status,
1129 p_validation_type => l_type,
1130 x_return_status => x_return_status,
1131 x_task_status_id => l_status_id);
1132
1133 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1134 return 'N';
1135 END IF;
1136 -------
1137 jtf_task_utl.validate_task_priority ( p_task_priority_id => p_task_priority_id,
1138 p_task_priority_name => l_priority,
1139 x_return_status => x_return_status,
1140 x_task_priority_id => l_priority_id);
1141 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1142 return 'N';
1143 END IF;
1144 -- All task attrributes are valid
1145 return 'Y';
1146
1147 Exception when others then
1148 return 'N';
1149 END;
1150 /**** not used anymore 9/30/2003
1151 procedure create_ea_tasks_isupp (
1152 p_api_version in number,
1153 p_init_msg_list in varchar2 := fnd_api.g_false,
1154 p_commit in varchar2 := fnd_api.g_false,
1155 p_sr_attributes_tbl in EA_SR_ATTR_TABLE_TYPE,
1156 p_request_id in number ,
1157 x_return_status OUT NOCOPY varchar2,
1158 x_msg_count OUT NOCOPY number,
1159 x_msg_data OUT NOCOPY varchar2,
1160 x_auto_task_gen_attempted OUT NOCOPY varchar2,
1161 x_field_service_Task_created OUT NOCOPY varchar2) is
1162 i number;
1163 l_prof_value fnd_profile_option_values.profile_option_value % type;
1164 l_err varchar2(4000);
1165 l_msg_index_out number;
1166 l_msg_data varchar2(2000);
1167 l_note_id number;
1168 l_note_type jtf_notes_b.note_type % type;
1169 l_login_id jtf_notes_b.last_update_login % type;
1170 l_user_id jtf_notes_b.last_updated_by % type;
1171 l_sr_rec CS_ServiceRequest_pub.service_request_rec_type;
1172 l_incident_number cs_incidents_all_b.incident_number % type;
1173 cursor c_sr is
1174 select * from cs_incidents_all_b
1175 where incident_id = p_request_id;
1176 begin
1177 l_prof_value := fnd_profile.value('AUTO GENERATE TASKS ON SR CREATE');
1178 if (l_prof_value = 'Task type Attribute configuration' or 1 =1) then
1179 for l_rec in c_sr loop
1180 l_sr_rec.type_id := l_rec.incident_type_id;
1181 l_sr_rec.status_id := l_rec.incident_status_id;
1182 l_sr_rec.urgency_id := l_rec.incident_urgency_id;
1183 l_sr_rec.severity_id := l_rec.incident_severity_id;
1184 l_sr_rec.obligation_date := l_rec.obligation_date;
1185 l_sr_rec.problem_code := l_rec.problem_code;
1186 l_sr_rec.inventory_item_id := l_rec.inventory_item_id;
1187 l_sr_rec.inventory_org_id := l_rec.inv_organization_id;
1188 l_sr_rec.customer_id := l_rec.customer_id;
1189 l_sr_rec.customer_number := l_rec.customer_number;
1190 l_sr_rec.category_id := l_rec.category_id;
1191 l_sr_rec.category_set_id := l_rec.category_set_id;
1192 l_sr_rec.incident_location_id := l_rec.incident_location_id;
1193 l_sr_rec.request_date := l_rec.incident_date;
1194 l_sr_rec.type_id := l_rec.incident_type_id;
1195 l_sr_rec.status_id := l_rec.incident_status_id;
1196 l_sr_rec.severity_id := l_rec.incident_severity_id;
1197 l_sr_rec.urgency_id := l_rec.incident_urgency_id;
1198 l_sr_rec.closed_date := l_rec.close_date;
1199 l_sr_rec.owner_id := l_rec.incident_owner_id;
1200 l_sr_rec.owner_group_id := l_rec.owner_group_id;
1201 l_sr_rec.publish_flag := l_rec.publish_flag;
1202 l_sr_rec.caller_type := l_rec.caller_type;
1203 l_sr_rec.customer_id := l_rec.customer_id;
1204 l_sr_rec.customer_number := l_rec.customer_number;
1205 l_sr_rec.employee_id := l_rec.employee_id;
1206 --l_sr_rec.employee_number := l_rec.employee_number;
1207 --l_sr_rec.verify_cp_flag := l_rec.verify_cp_flag;
1208 l_sr_rec.customer_product_id := l_rec.customer_product_id;
1209 l_sr_rec.platform_id := l_rec.platform_id;
1210 l_sr_rec.platform_version := l_rec.platform_version;
1211 l_sr_rec.db_version := l_rec.db_version;
1212 l_sr_rec.platform_version_id := l_rec.platform_version_id;
1213 l_sr_rec.cp_component_id := l_rec.cp_component_id;
1214 l_sr_rec.cp_component_version_id := l_rec.cp_component_version_id;
1215 l_sr_rec.cp_subcomponent_id := l_rec.cp_subcomponent_id;
1216 l_sr_rec.cp_subcomponent_version_id := l_rec.cp_subcomponent_version_id;
1217 l_sr_rec.language_id := l_rec.language_id;
1218 --l_sr_rec.language := l_rec.language;
1219 --l_sr_rec.cp_ref_number := l_rec.cp_ref_number;
1220 l_sr_rec.inventory_item_id := l_rec.inventory_item_id;
1221 l_sr_rec.inventory_org_id := l_rec.inv_organization_id;
1222 l_sr_rec.current_serial_number := l_rec.current_serial_number;
1223 l_sr_rec.original_order_number := l_rec.original_order_number;
1224 --l_sr_rec.purchase_order_num := l_rec.purchase_order_number;
1225 l_sr_rec.problem_code := l_rec.problem_code;
1226 l_sr_rec.exp_resolution_date := l_rec.expected_resolution_date;
1227 l_sr_rec.install_site_use_id := l_rec.install_site_use_id;
1228 l_sr_rec.request_attribute_1 := l_rec.incident_attribute_1;
1229 l_sr_rec.request_attribute_2 := l_rec.incident_attribute_2;
1230 l_sr_rec.request_attribute_3 := l_rec.incident_attribute_3;
1231 l_sr_rec.request_attribute_4 := l_rec.incident_attribute_4;
1232 l_sr_rec.request_attribute_5 := l_rec.incident_attribute_5;
1233 l_sr_rec.request_attribute_6 := l_rec.incident_attribute_6;
1234 l_sr_rec.request_attribute_7 := l_rec.incident_attribute_7;
1235 l_sr_rec.request_attribute_8 := l_rec.incident_attribute_8;
1236 l_sr_rec.request_attribute_9 := l_rec.incident_attribute_9;
1237 l_sr_rec.request_attribute_10 := l_rec.incident_attribute_10;
1238 l_sr_rec.request_attribute_11 := l_rec.incident_attribute_11;
1239 l_sr_rec.request_attribute_12 := l_rec.incident_attribute_12;
1240 l_sr_rec.request_attribute_13 := l_rec.incident_attribute_13;
1241 l_sr_rec.request_attribute_14 := l_rec.incident_attribute_14;
1242 l_sr_rec.request_attribute_15 := l_rec.incident_attribute_15;
1243 --l_sr_rec.request_context := l_rec.request_context;
1244 l_sr_rec.external_attribute_1 := l_rec.external_attribute_1;
1245 l_sr_rec.external_attribute_2 := l_rec.external_attribute_2;
1246 l_sr_rec.external_attribute_3 := l_rec.external_attribute_3;
1247 l_sr_rec.external_attribute_4 := l_rec.external_attribute_4;
1248 l_sr_rec.external_attribute_5 := l_rec.external_attribute_5;
1249 l_sr_rec.external_attribute_6 := l_rec.external_attribute_6;
1250 l_sr_rec.external_attribute_7 := l_rec.external_attribute_7;
1251 l_sr_rec.external_attribute_8 := l_rec.external_attribute_8;
1252 l_sr_rec.external_attribute_9 := l_rec.external_attribute_9;
1253 l_sr_rec.external_attribute_10 := l_rec.external_attribute_10;
1254 l_sr_rec.external_attribute_11 := l_rec.external_attribute_11;
1255 l_sr_rec.external_attribute_12 := l_rec.external_attribute_12;
1256 l_sr_rec.external_attribute_13 := l_rec.external_attribute_13;
1257 l_sr_rec.external_attribute_14 := l_rec.external_attribute_14;
1258 l_sr_rec.external_attribute_15 := l_rec.external_attribute_15;
1259 l_sr_rec.external_context := l_rec.external_context;
1260 l_sr_rec.bill_to_site_use_id := l_rec.bill_to_site_use_id;
1261 l_sr_rec.bill_to_contact_id := l_rec.bill_to_contact_id;
1262 l_sr_rec.ship_to_site_use_id := l_rec.ship_to_site_use_id;
1263 l_sr_rec.ship_to_contact_id := l_rec.ship_to_contact_id;
1264 l_sr_rec.resolution_code := l_rec.resolution_code;
1265 l_sr_rec.act_resolution_date := l_rec.actual_resolution_date;
1266 --l_sr_rec.public_comment_flag := l_rec.public_comment_flag;
1267 --l_sr_rec.parent_interaction_id := l_rec.parent_iteaction_id;
1268 l_sr_rec.contract_service_id := l_rec.contract_service_id;
1269 --l_sr_rec.contract_service_number := l_rec.contract_service_number;
1270 l_sr_rec.contract_id := l_rec.contract_id;
1271 l_sr_rec.project_number := l_rec.project_number;
1272 l_sr_rec.qa_collection_plan_id := l_rec.qa_collection_id;
1273 l_sr_rec.account_id := l_rec.account_id;
1274 l_sr_rec.resource_type := l_rec.resource_type;
1275 l_sr_rec.resource_subtype_id := l_rec.resource_subtype_id;
1276 --l_sr_rec.cust_po_number := l_rec.cust_po_number;
1277 --l_sr_rec.cust_ticket_number := l_rec.cust_ticket_number;
1278 l_sr_rec.sr_creation_channel := l_rec.sr_creation_channel;
1279 l_sr_rec.obligation_date := l_rec.obligation_date;
1280 l_sr_rec.time_zone_id := l_rec.time_zone_id;
1281 l_sr_rec.time_difference := l_rec.time_difference;
1282 l_sr_rec.site_id := l_rec.site_id;
1283 l_sr_rec.customer_site_id := l_rec.customer_site_id;
1284 l_sr_rec.territory_id := l_rec.territory_id;
1285 --l_sr_rec.initialize_flag := l_rec.initialize_flag;
1286 l_sr_rec.cp_revision_id := l_rec.cp_revision_id;
1287 l_sr_rec.inv_item_revision := l_rec.inv_item_revision;
1288 l_sr_rec.inv_component_id := l_rec.inv_component_id;
1289 l_sr_rec.inv_component_version := l_rec.inv_component_version;
1290 l_sr_rec.inv_subcomponent_id := l_rec.inv_subcomponent_id;
1291 l_sr_rec.inv_subcomponent_version := l_rec.inv_subcomponent_version;
1292 ------jngeorge---------------07/12/01
1293 l_sr_rec.tier := l_rec.tier;
1294 l_sr_rec.tier_version := l_rec.tier_version;
1295 l_sr_rec.operating_system := l_rec.operating_system;
1296 l_sr_rec.operating_system_version := l_rec.operating_system_version;
1297 l_sr_rec.database := l_rec.database;
1298 l_sr_rec.cust_pref_lang_id := l_rec.cust_pref_lang_id;
1299 l_sr_rec.category_id := l_rec.category_id;
1300 l_sr_rec.group_type := l_rec.group_type;
1301 l_sr_rec.group_territory_id := l_rec.group_territory_id;
1302 l_sr_rec.inv_platform_org_id := l_rec.inv_platform_org_id;
1303 l_sr_rec.component_version := l_rec.component_version;
1304 l_sr_rec.subcomponent_version := l_rec.subcomponent_version;
1305 --l_sr_rec.product_revision := l_rec.product_version;
1306 l_sr_rec.comm_pref_code := l_rec.comm_pref_code;
1307 ---- Added for Post 11.5.6 Enhancement
1308 l_sr_rec.cust_pref_lang_code := l_rec.cust_pref_lang_code;
1309 -- Changed the width from 1 to 30 for last_update_channel for bug 2688856
1310 -- shijain 3rd dec 2002
1311 l_sr_rec.last_update_channel := l_rec.last_update_channel;
1312 l_sr_rec.category_set_id := l_rec.category_set_id;
1313 l_sr_rec.external_reference := l_rec.external_reference;
1314 l_sr_rec.system_id := l_rec.system_id;
1315 ------jngeorge---------------07/12/0 := l_rec.
1316 l_sr_rec.error_code := l_rec.error_code;
1317 l_sr_rec.incident_occurred_date := l_rec.incident_occurred_date;
1318 l_sr_rec.incident_resolved_date := l_rec.incident_resolved_date;
1319 l_sr_rec.inc_responded_by_date := l_rec.inc_responded_by_date;
1320 --l_sr_rec.resolution_summary := l_rec.resolution_summary;
1321 l_sr_rec.incident_location_id := l_rec.incident_location_id;
1322 l_sr_rec.incident_address := l_rec.incident_address;
1323 l_sr_rec.incident_city := l_rec.incident_city;
1324 l_sr_rec.incident_state := l_rec.incident_state;
1325 l_sr_rec.incident_country := l_rec.incident_country;
1326 l_sr_rec.incident_province := l_rec.incident_province;
1327 l_sr_rec.incident_postal_code := l_rec.incident_postal_code;
1328 l_sr_rec.incident_county := l_rec.incident_country;
1329 -- Added for Enh# 221666 := l_rec.
1330 --l_sr_rec.owner := l_rec.ARCHAR2(360),
1331 --l_sr_rec.group_owner := l_rec.ARCHAR2(60),
1332 -- Added for Credit Card ER# 2255263 (UI ER#2208078)
1333 l_sr_rec.cc_number := l_rec.credit_card_number;
1334 l_sr_rec.cc_expiration_date := l_rec.credit_card_expiration_date;
1335 l_sr_rec.cc_type_code := l_rec.credit_card_type_code;
1336 l_sr_rec.cc_first_name := l_rec.credit_card_holder_fname;
1337 l_sr_rec.cc_last_name := l_rec.credit_card_holder_lname;
1338 l_sr_rec.cc_middle_name := l_rec.credit_card_holder_mname;
1339 l_sr_rec.cc_id := l_rec.credit_card_id;
1340 l_sr_rec.bill_to_account_id := l_rec.bill_to_account_id;
1341 l_sr_rec.ship_to_account_id := l_rec.ship_to_account_id;
1342 l_sr_rec.customer_phone_id := l_rec.customer_phone_id;
1343 l_sr_rec.customer_email_id := l_rec.customer_email_id;
1344 -- Added for source changes for 1159 by shijain oct 11 2002
1345 l_sr_rec.creation_program_code := l_rec.creation_program_code;
1346 l_sr_rec.last_update_program_code := l_rec.last_update_program_code;
1347 -- Bill_to_party, ship_to_party
1348 l_sr_rec.bill_to_party_id := l_rec.bill_to_party_id;
1349 l_sr_rec.ship_to_party_id := l_rec.ship_to_party_id;
1350 -- Conc request related fields
1351 l_sr_rec.program_id := l_rec.program_id;
1352 l_sr_rec.program_application_id := l_rec.program_application_id;
1353 --l_sr_rec.conc_request_id NUMBER, -- Renamed so that it doesn't clash with SR id
1354 l_sr_rec.program_login_id := l_rec.program_login_id;
1355 -- Bill_to_site, ship_to_site
1356 l_sr_rec.bill_to_site_id := l_rec.bill_to_site_id;
1357 l_sr_rec.ship_to_site_id := l_rec.ship_to_site_id;
1358 l_sr_rec.incident_point_of_interest := l_rec.incident_point_of_interest;
1359 l_sr_rec.incident_cross_street := l_rec.incident_cross_street;
1360 l_sr_rec.incident_direction_qualifier := l_rec.incident_direction_qualifier;
1361 l_sr_rec.incident_distance_qualifier := l_rec.incident_distance_qualifier;
1362 l_sr_rec.incident_distance_qual_uom := l_rec.incident_distance_qual_uom;
1363 l_sr_rec.incident_address2 := l_rec.incident_address2;
1364 l_sr_rec.incident_address3 := l_rec.incident_address3;
1365 l_sr_rec.incident_address4 := l_rec.incident_address4;
1366 l_sr_rec.incident_address_style := l_rec.incident_address_style;
1367 l_sr_rec.incident_addr_lines_phonetic := l_rec.incident_addr_lines_phonetic;
1368 l_sr_rec.incident_po_box_number := l_rec.incident_po_box_number;
1369 l_sr_rec.incident_house_number := l_rec.incident_house_number;
1370 l_sr_rec.incident_street_suffix := l_rec.incident_street_suffix;
1371 l_sr_rec.incident_street := l_rec.incident_street;
1372 l_sr_rec.incident_street_number := l_rec.incident_street_number;
1373 l_sr_rec.incident_floor := l_rec.incident_floor;
1374 l_sr_rec.incident_suite := l_rec.incident_suite;
1375 l_sr_rec.incident_postal_plus4_code := l_rec.incident_postal_plus4_code;
1376 l_sr_rec.incident_position := l_rec.incident_position;
1377 l_sr_rec.incident_location_directions := l_rec.incident_location_directions;
1378 l_sr_rec.incident_location_description := l_rec.incident_location_description;
1379 l_sr_rec.install_site_id := l_rec.install_site_id;
1380
1381 --
1382 l_incident_number := l_rec.incident_number;
1383 end loop;
1384 create_extnd_attr_tasks (
1385 p_api_version ,
1386 p_init_msg_list ,
1387 p_commit ,
1388 l_sr_rec ,
1389 p_sr_attributes_tbl ,
1390 p_request_id ,
1391 l_incident_number ,
1392 x_return_status ,
1393 x_msg_count ,
1394 x_msg_data ,
1395 x_auto_task_gen_attempted ,
1396 x_field_service_Task_created );
1397 --dbms_output.put_line('Return Message(isupp):'||x_return_status||':');
1398 if (x_return_status <> 'S') then
1399 for i in 1..x_msg_count loop
1400 FND_MSG_PUB.Get(p_msg_index=>i,
1401 p_encoded=>'F',
1402 p_data=>l_msg_data,
1403 p_msg_index_out=>l_msg_index_out);
1404 l_err := l_err || l_msg_data || ',';
1405 end loop;
1406 l_note_type := fnd_profile.value('CS_SR_TASK_ERROR_NOTE_TYPE');
1407 if (l_note_type is null) then
1408 fnd_message.set_name ('CS', 'CS_EA_NULL_NOTE_TYPE');
1409 fnd_msg_pub.add;
1410 raise fnd_api.g_exc_unexpected_error;
1411 end if;
1412 l_login_id := fnd_global.login_id;
1413 l_user_id := fnd_global.user_id ;
1414 jtf_notes_pub.create_note(
1415 p_api_version => 1,
1416 p_init_msg_list => p_init_msg_list,
1417 p_commit => p_commit,
1418 p_validation_level => fnd_api.g_valid_level_full,
1419 x_return_status => x_return_status,
1420 x_msg_count => x_msg_count,
1421 x_msg_data => x_msg_data,
1422 p_entered_by => l_user_id,
1423 p_entered_date => sysdate,
1424 p_last_update_date => sysdate,
1425 p_last_updated_by => l_user_id,
1426 p_creation_date => sysdate,
1427 p_created_by => l_user_id,
1428 p_last_update_login => l_login_id,
1429 p_source_object_id => p_request_id,
1430 p_source_object_code => 'SR',
1431 p_notes => l_err,
1432 p_notes_detail => l_err,
1433 p_note_type => l_note_type,
1434 p_note_status => 'P',
1435 x_jtf_note_id => l_note_id
1436 );
1437 --dbms_output.put_line('Return Message(note):'||x_return_status||':');
1438 end if; -- check for errors returned by autogen api
1439 end if; -- profile option check
1440 EXCEPTION
1441 WHEN fnd_api.g_exc_unexpected_error THEN
1442 x_return_status := fnd_api.g_ret_sts_unexp_error;
1443 fnd_msg_pub.count_and_get (
1444 p_count => x_msg_count,
1445 p_data => x_msg_data);
1446 WHEN OTHERS THEN
1447 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
1448 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
1449 fnd_msg_pub.add;
1450 x_return_status := fnd_api.g_ret_sts_unexp_error;
1451 fnd_msg_pub.count_and_get (
1452 p_count => x_msg_count,
1453 p_data => x_msg_data);
1454 end ;
1455 ***** 9/30/2003 *******/
1456 end cs_ea_autogen_tasks_pvt;