[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.6.12000000.2 2007/05/03 17:31:11 romehrot 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 -- Modification History:
653 -- Date Name Desc
654 -- ------- -------- ------------------------------------------------------------
655 -- 07/26/05 smisra fixed bug 4477767.
656 -- passed p_date_selected as NULL to task API. If this
657 -- parameter is 'D' then it may cause certain task validations
658 -- to fail and auto task process in not in a position to pass
659 -- those details
660 -- 07/26/05 smisra fixed bug 4272460.
661 -- used owner id, owner type from SR Type, Task Type mapping
662 -- if assignment manager can not determine task owner
663 -- 08/19/05 smisra Fixed bug 4272460 |
664 -- put call to auto tak assignment under comment.
665 -- when decision to use Assignment is manager is made, that
666 -- call can be uncommneted.
667 -- 07/12/06 romehrot Bug 5686743
668 -- Added to code to populate assignee information from setup form.
669
670 -- -----------------------------------------------------------------------------
671 procedure create_extnd_attr_tasks (
672 p_api_version in number,
673 p_init_msg_list in varchar2 ,
674 p_commit in varchar2 ,
675 p_sr_rec in CS_ServiceRequest_pub.service_request_rec_type,
676 p_sr_attributes_tbl in EA_SR_ATTR_TABLE_TYPE,
677 p_request_id in number ,
678 p_incident_number in varchar2 ,
679 x_return_status OUT NOCOPY varchar2,
680 x_msg_count OUT NOCOPY number,
681 x_msg_data OUT NOCOPY varchar2,
682 x_auto_task_gen_attempted OUT NOCOPY varchar2,
683 x_field_service_Task_created OUT NOCOPY varchar2) is
684 l_task_rec_tbl ea_task_table_type;
685 l_task_attr_rec cs_sr_task_autoassign_pkg.Sr_Task_rec_type;
686 l_indx number;
687 l_task_id jtf_tasks_b.task_id % type;
688 l_login_id jtf_notes_b.last_update_login % type;
689 l_user_id jtf_notes_b.last_updated_by % type;
690 l_obj_version cs_incidents_all_b.object_version_number % type;
691 l_owner_type cs_incidents_all_b.resource_type % type;
692 l_owner_id cs_incidents_all_b.incident_owner_id % type;
693 l_owner_group_id cs_incidents_all_b.owner_group_id % type;
694 l_last_updated_by cs_incidents_all_b.last_updated_by % type;
695 l_group_type varchar2(50);
696 l_sr_rec cs_servicerequest_pub.service_request_rec_type ;
697 l_task_owner_type jtf_tasks_b.owner_type_code % type;
698 l_task_owner_id jtf_tasks_b.owner_id%type;
699 l_location_id cs_incidents_all_b.incident_location_id % type;
700 l_dummy varchar2(80);
701 l_address_id NUMBER;
702 l_customer_id NUMBER := p_sr_rec.customer_id;
703 l_task_assignment_id Number; --5686743
704 -- Simplex
705 -- local variable and exception declarations for Simplex Enhancement
706 l_prof_val VARCHAR(1);
707 l_date_selected VARCHAR2(1) := null;
708 l_temp NUMBER(20,4);
709 l_api_name VARCHAR2(100) := 'cs_ea_autogen_tasks_pvt.create_extnd_attr_tasks';
710 l_conv_rate NUMBER(30,6);
711 l_planned_effort NUMBER(30,6):= 0;
712 l_task_type_name VARCHAR2(100) := null;
713
714 e_date_pair_exception EXCEPTION ;
715 e_planned_effort_val_exception EXCEPTION ;
716 e_party_site_exception EXCEPTION;
717
718 cursor c_task_type_name(l_task_type_id IN NUMBER) IS
719 select name
720 from jtf_task_types_vl
721 where task_type_id = l_task_type_id;
722
723 -- end Simplex
724
725 begin
726 savepoint create_extnd_attr_task_pvt;
727 l_sr_rec := p_sr_rec;
728 x_auto_task_gen_attempted := 'N';
729 x_field_service_task_created := 'N';
730 --dbms_output.put_line('Before get_extnd_attrtask');
731 get_extnd_attr_tasks (
732 p_api_version => p_api_version,
733 p_init_msg_list => p_init_msg_list,
734 p_sr_rec => p_sr_rec,
735 p_request_id => p_request_id,
736 p_incident_number => p_incident_number,
737 p_sr_attributes_tbl => p_sr_attributes_tbl,
738 x_return_status => x_return_status,
739 x_msg_count => x_msg_count,
740 x_msg_data => x_msg_data,
741 x_task_rec_table => l_task_rec_tbl);
742 --dbms_output.put_line('Return Status(get_extnd_attr_tasks):'||x_return_status|| ':');
743 -- Create Tasks
744 if (x_return_status = fnd_api.g_ret_sts_success) then
745 --l_login_id := fnd_global.login_id;
746 --l_user_id := fnd_global.user_id ;
747 -- Task api does not take user ids as parameter
748
749 -- Simplex
750 -- Get the value for the profile option 'Service : Apply State Restriction on Tasks'
751 -- to decide the enabling/disabling of task state restrictions
752
753 -- in order to fix 4477767, task restriction can not be applied to auto tasks
754 -- FND_PROFILE.Get('CS_SR_ENABLE_TASK_STATE_RESTRICTIONS',l_prof_val);
755
756 -- end of simplex
757
758
759 for l_indx in 1..l_task_rec_tbl.count loop
760 --dbms_output.put_line('Going through the task table:'||to_char(l_indx));
761 -- do validations for field service task
762 if (Are_Task_Attributes_valid(l_task_rec_tbl(l_indx).task_type_id,
763 l_task_rec_tbl(l_indx).task_status_id,
764 l_task_rec_tbl(l_indx).task_priority_id) = 'Y') then
765 l_task_rec_tbl(l_indx).planned_end_date := nvl(p_sr_rec.obligation_date,sysdate);
766
767
768 if (l_task_rec_tbl(l_indx).field_service_task_flag = 'Y') then
769 -- no need to validate type id, status, task_name as these are not null
770 -- in database
771 -- 1st check: Location as party site -- Removed -- Modified to ensure incident location is NOT NULL
772
773 IF (l_task_rec_tbl(l_indx).field_service_task_flag = 'Y') THEN
774
775 IF p_sr_rec.incident_location_id IS NULL THEN
776 RAISE e_party_site_exception ;
777 END IF ;
778
779 END IF ;
780
781
782 -- 2nd check: Planed End Date
783 /*
784 if (p_sr_rec.obligation_date is null) then
785 fnd_message.set_name ('CS', 'CS_EA_NO_PLANNED_END_DATE');
786 fnd_msg_pub.add;
787 x_return_status := fnd_api.g_ret_sts_error;
788 raise fnd_api.g_exc_error;
789 else
790 */
791 --end if;
792 -- 3rd check: Planned Effort
793 if (l_task_rec_tbl(l_indx).planned_effort is null) then
794 fnd_message.set_name ('CS', 'CS_EA_NO_PLANNED_EFFORT');
795 fnd_message.set_token('P_TYPE_ID',l_task_rec_tbl(l_indx).task_type_id);
796 fnd_msg_pub.add;
797 x_return_status := fnd_api.g_ret_sts_error;
798 raise fnd_api.g_exc_error;
799 end if;
800 -- 4th check: Planned Effort UOM
801 if (l_task_rec_tbl(l_indx).planned_effort_uom is null) then
802 fnd_message.set_name ('CS', 'CS_EA_NO_PLANNED_EFFORT_UOM');
803 fnd_message.set_token('P_TYPE_ID',l_task_rec_tbl(l_indx).task_type_id);
804 fnd_msg_pub.add;
805 x_return_status := fnd_api.g_ret_sts_error;
806 raise fnd_api.g_exc_error;
807 end if;
808 -- no need to check task name, status, type and planned start date
809 -- as these can not be null. these are not null in respective
810 -- source tables. planned_start_date is always set to sysdate
811 end if;
812 -- end of field service task validation
813 -- Get Resource_id for new tasks.
814 l_task_attr_rec.task_type_id := l_task_rec_tbl(l_indx).task_type_id;
815 l_task_attr_rec.task_status_id := l_task_rec_tbl(l_indx).task_status_id;
816 l_task_attr_rec.task_priority_id := l_task_rec_tbl(l_indx).task_priority_id;
817 l_task_owner_type := l_task_rec_tbl(l_indx).owner_type_code;
818 l_task_owner_id := l_task_rec_tbl(l_indx).owner_id;
819
820 /**********************************************************************
821 task assignment will be based on SR Type, Task Type setup only for time being
822 when decision to use task assignment based on territory is made, uncomment
823 this portion of code
824
825 l_sr_rec.type_id := null;
826 cs_sr_task_autoassign_pkg.assign_task_resource(
827 p_api_version => 1,
828 p_init_msg_list => fnd_api.g_false,
829 p_commit => fnd_api.g_false,
830 p_incident_id => p_request_id,
831 p_service_request_rec => l_sr_rec,
832 p_task_attribute_rec => l_task_attr_rec,
833 x_owner_group_id => l_owner_group_id,
834 x_owner_type => l_owner_type,
835 x_group_type => l_group_type,
836 x_owner_id => l_owner_id,
837 x_return_status => x_return_status,
838 x_msg_count => x_msg_count,
839 x_msg_data => x_msg_data);
840 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
841 THEN
842 --fnd_msg_pub.set_search_name('CS','CS_SR_TASK_NO_OWNER');
843 --fnd_msg_pub.set_search_token('API_NAME','CS_SR_TASK_AUTOASSIGN_PKG.Assign_Task_Resource');
844 --fnd_message.set_name ('CS', 'CS_EA_ASSIGN_TASK_ERROR');
845 --fnd_message.set_token('TASK_NAME',l_task_rec_tbl(l_indx).task_name);
846 --l_dummy := fnd_msg_pub.change_msg;
847 --raise fnd_api.g_exc_error;
848 x_return_status := FND_API.G_RET_STS_SUCCESS;
849 ELSE
850 IF (l_owner_type is null) then
851 l_task_owner_type := l_group_type;
852 l_task_owner_id := l_owner_group_id;
853 ELSE
854 l_task_owner_type := l_owner_type;
855 l_task_owner_id := l_owner_id;
856 END IF;
857 END IF;
858 ******************************************************************************/
859
860 x_auto_task_gen_attempted := 'Y';
861 -- Since location id can be null for task but task takes only
862 -- party sites. so if location type is not party site, set location to null
863 if (nvl(p_sr_rec.incident_location_type,'x') = 'HZ_PARTY_SITE' ) then
864 l_address_id := p_sr_rec.incident_location_id;
865 l_customer_id := p_sr_rec.customer_id ;
866 elsif (nvl(p_sr_rec.incident_location_type,'x')) = 'HZ_LOCATION' THEN
867 l_location_id := p_sr_rec.incident_location_id;
868 l_customer_id := null;
869 else
870 l_address_id := null;
871 l_location_id := null;
872 l_customer_id := p_sr_rec.customer_id;
873 end if;
874
875 -- Simplex
876 -- The below validations should be done every tasks in the task template group and
877 -- hence the validations are inside the loop
878
879 -- Enable task state restrictions depending on the profile value
880 -- 'Service : Apply State Restriction on Tasks'
881 --
882 --IF ( l_prof_val = 'Y') THEN
883 -- l_date_selected := 'D';
884 --END IF;
885
886 -- The palnned start date and planned end date should appear in pair.
887 -- If not,exception is thrown
888 IF ( ( (l_task_rec_tbl(l_indx).planned_start_date IS NOT NULL AND
889 l_task_rec_tbl(l_indx).planned_start_date <> FND_API.G_MISS_DATE)
890 AND
891 (l_task_rec_tbl(l_indx).planned_end_date IS NULL OR
892 l_task_rec_tbl(l_indx).planned_end_date = FND_API.G_MISS_DATE)
893 )
894 OR
895 ( (l_task_rec_tbl(l_indx).planned_end_date IS NOT NULL AND
896 l_task_rec_tbl(l_indx).planned_end_date <> FND_API.G_MISS_DATE)
897 AND
898 (l_task_rec_tbl(l_indx).planned_start_date IS NULL OR
899 l_task_rec_tbl(l_indx).planned_start_date = FND_API.G_MISS_DATE)
900 )
901 )THEN
902
903 open c_task_type_name(l_task_rec_tbl(l_indx).task_type_id);
904 fetch c_task_type_name into l_task_type_name;
905 close c_task_type_name;
906
907 fnd_message.set_name ('CS', 'CS_EA_DATE_PAIR_ERROR');
908 fnd_message.set_token('TASK_TYPE',l_task_type_name);
909 fnd_message.set_token('API_NAME',l_api_name);
910 fnd_msg_pub.add;
911 x_return_status := fnd_api.g_ret_sts_error;
912 raise fnd_api.g_exc_error;
913 END IF;
914
915 -- no validations for scheduled and actual dates as the parameters are not passed
916
917
918 -- The enabling/disabling of state restrictions is based on tke profile option
919 -- 'Service : Apply State Restriction on Tasks' and the value is stored in the
920 -- local variable l_date_selected
921
922 jtf_tasks_pub.create_task (
923 p_api_version => 1.0,
924 p_init_msg_list => fnd_api.g_false,
925 p_commit => fnd_api.g_false,
926 p_task_name => l_task_rec_tbl(l_indx).task_name,
927 p_description => l_task_rec_tbl(l_indx).task_description,
928 p_task_type_id => l_task_rec_tbl(l_indx).task_type_id,
929 p_task_status_id => l_task_rec_tbl(l_indx).task_status_id,
930 p_task_priority_id => l_task_rec_tbl(l_indx).task_priority_id,
931 p_owner_id => l_task_owner_id,
932 p_owner_type_code => l_task_owner_type,
933 p_planned_start_date => l_task_rec_tbl(l_indx).planned_start_date,
934 p_planned_end_date => l_task_rec_tbl(l_indx).planned_end_date,
935 p_planned_effort => l_task_rec_tbl(l_indx).planned_effort,
936 p_planned_effort_uom => l_task_rec_tbl(l_indx).planned_effort_uom,
937 p_customer_id => l_customer_id , --p_sr_rec.customer_id,
938 p_address_id => l_address_id,
939 p_category_id => NULL,
940 p_source_object_id => p_request_id,
941 p_source_object_name => p_incident_number,
942 p_source_object_type_code => 'SR',
943 p_date_selected => l_date_selected, -- simplex 'D',
944 p_private_flag => l_task_rec_tbl(l_indx).private_flag,
945 p_publish_flag => l_task_rec_tbl(l_indx).publish_flag,
946 p_location_id => l_location_id,
947 x_return_status => x_return_status,
948 x_msg_count => x_msg_count,
949 x_msg_data => x_msg_data,
950 x_task_id => l_task_id)
951 ;
952 --dbms_output.put_line('after Create Task:'||x_return_status);
953 if (x_return_status <> fnd_api.g_ret_sts_success) then
954 /***
955 dbms_output.put_line(l_task_rec_tbl(l_indx).task_name);
956 dbms_output.put_line(l_task_rec_tbl(l_indx).task_description);
957 dbms_output.put_line(l_task_rec_tbl(l_indx).task_type_id);
958 dbms_output.put_line(l_task_rec_tbl(l_indx).task_status_id);
959 dbms_output.put_line(l_task_rec_tbl(l_indx).task_priority_id);
960 dbms_output.put_line(to_char(l_task_rec_tbl(l_indx).planned_start_date,'dd-mon-yyyy hh24:mi:ss'));
961 dbms_output.put_line(to_char(l_task_rec_tbl(l_indx).planned_end_date,'dd-mon-yyyy hh24:mi:ss'));
962 dbms_output.put_line(l_task_rec_tbl(l_indx).planned_effort);
963 dbms_output.put_line(l_task_rec_tbl(l_indx).planned_effort_uom);
964 dbms_output.put_line(p_sr_rec.incident_location_id);
965 dbms_output.put_line(p_request_id);
966 dbms_output.put_line(p_incident_number);
967 dbms_output.put_line(l_task_rec_tbl(l_indx).private_flag);
968 dbms_output.put_line(l_task_rec_tbl(l_indx).publish_flag);
969 dbms_output.put_line(':'||to_char(l_owner_id));
970 dbms_output.put_line('Owner Type:'||l_owner_type||':');
971 dbms_output.put_line(':'||to_char(l_owner_group_id));
972 dbms_output.put_line('Owner Type:'||l_group_type||':');
973 dbms_output.put_line('====');
974 fnd_message.set_name ('CS', 'CS_EA_CREATE_TASK_API_ERROR');
975 fnd_msg_pub.add;
976 *****/
977 raise fnd_api.g_exc_error;
978 end if;
979 if (l_task_rec_tbl(l_indx).field_service_task_flag = 'Y') then
980 x_field_service_task_created := 'Y';
981 end if;
982 /* Start : 5686743 */
983 If l_task_rec_tbl(l_indx).assignee_id IS NOT NULL Then
984 jtf_task_assignments_pub.create_task_assignment(
985 p_api_version => 1.0,
986 p_init_msg_list => cs_core_util.get_g_true,
987 p_commit => cs_core_util.get_g_false,
988 p_task_id => l_task_id,
989 p_resource_type_code => l_task_rec_tbl(l_indx).assignee_type_code,
990 p_resource_id => l_task_rec_tbl(l_indx).assignee_id,
991 p_assignment_status_id => fnd_profile.value('JTF_TASK_DEFAULT_ASSIGNEE_STATUS'),
992 x_return_status => x_return_status,
993 x_msg_count => x_msg_count,
994 x_msg_data => x_msg_data,
995 x_task_assignment_id => l_task_assignment_id);
996
997 If (x_return_status <> fnd_api.g_ret_sts_success) Then
998 raise fnd_api.g_exc_error;
999 End if;
1000 End If;
1001 /* End : 5686743 */
1002 if (l_task_rec_tbl(l_indx).workflow is not null) then
1003 start_task_workflow (l_task_id ,
1004 l_task_rec_tbl(l_indx).tsk_typ_attr_dep_id ,
1005 l_task_rec_tbl(l_indx).workflow ,
1006 l_task_rec_tbl(l_indx).workflow_type ,
1007 l_task_rec_tbl(l_indx).task_name,
1008 l_task_rec_tbl(l_indx).task_description,
1009 x_return_status ,
1010 x_msg_count ,
1011 x_msg_data );
1012 if (x_return_status <> fnd_api.g_ret_sts_success) then
1013 fnd_message.set_name ('CS', 'CS_EA_START_WORKFLOW_ERROR');
1014 fnd_msg_pub.add;
1015 raise fnd_api.g_exc_error;
1016 end if;
1017 end if;
1018 else
1019 fnd_msg_pub.initialize;
1020 end if; -- check for validity of task attributes such as type, status, priority
1021 end loop;
1022 end if;
1023 -- All task created
1024 if fnd_api.to_boolean (p_commit) then
1025 commit;
1026 end if;
1027 --
1028 -- Exception handling
1029 --
1030 --raise_application_error(-20001,'For testing msg JTF_TASK_UNKNOWN_ERROR');
1031 EXCEPTION
1032 WHEN e_party_site_exception THEN
1033 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1034 p_data => x_msg_data );
1035 FND_MESSAGE.SET_NAME('CS','CS_EA_NO_PARTY_SITE');
1036 FND_MSG_PUB.ADD;
1037 x_return_status := FND_API.G_RET_STS_ERROR ;
1038 WHEN fnd_api.g_exc_error THEN
1039 rollback to create_extnd_attr_task_pvt;
1040 x_return_status := fnd_api.g_ret_sts_error;
1041 fnd_msg_pub.count_and_get (
1042 p_count => x_msg_count,
1043 p_data => x_msg_data);
1044 WHEN fnd_api.g_exc_unexpected_error THEN
1045 rollback to create_extnd_attr_task_pvt;
1046 x_return_status := fnd_api.g_ret_sts_unexp_error;
1047 fnd_msg_pub.count_and_get (
1048 p_count => x_msg_count,
1049 p_data => x_msg_data);
1050 WHEN OTHERS THEN
1051 rollback to create_extnd_attr_task_pvt;
1052 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
1053 fnd_message.set_token ('P_TEXT', SQLERRM);
1054 fnd_msg_pub.add;
1055 x_return_status := fnd_api.g_ret_sts_unexp_error;
1056 fnd_msg_pub.count_and_get (
1057 p_count => x_msg_count,
1058 p_data => x_msg_data);
1059 end create_extnd_attr_tasks;
1060 --------------------------------------------------
1061 FUNCTION Are_task_Attributes_valid(p_task_type_id number,
1062 p_task_status_id number,
1063 p_task_priority_id number) return varchar2 is
1064 l_type jtf_tasks_v.task_type % type;
1065 l_status jtf_tasks_v.task_status % type;
1066 l_priority jtf_tasks_v.task_priority % type;
1067 l_type_id number;
1068 l_status_id number;
1069 l_priority_id number;
1070 x_return_status varchar2(10);
1071 BEGIN
1072 jtf_task_utl.validate_task_type ( p_task_type_id => p_task_type_id,
1073 p_task_type_name => l_type,
1074 x_return_status => x_return_status,
1075 x_task_type_id => l_type_id
1076 );
1077
1078 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1079 return 'N';
1080 END IF;
1081
1082 IF l_type_id IS NULL THEN
1083 return 'N';
1084 END IF;
1085
1086 -------
1087 ------- Validate Task Status
1088 -------
1089 IF l_type_id = '22' THEN
1090 l_type := 'ESCALATION';
1091 ELSE
1092 l_type := 'TASK';
1093 END IF;
1094
1095 jtf_task_utl.validate_task_status ( p_task_status_id => p_task_status_id,
1096 p_task_status_name => l_status,
1097 p_validation_type => l_type,
1098 x_return_status => x_return_status,
1099 x_task_status_id => l_status_id);
1100
1101 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1102 return 'N';
1103 END IF;
1104 -------
1105 jtf_task_utl.validate_task_priority ( p_task_priority_id => p_task_priority_id,
1106 p_task_priority_name => l_priority,
1107 x_return_status => x_return_status,
1108 x_task_priority_id => l_priority_id);
1109 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1110 return 'N';
1111 END IF;
1112 -- All task attrributes are valid
1113 return 'Y';
1114
1115 Exception when others then
1116 return 'N';
1117 END;
1118 /**** not used anymore 9/30/2003
1119 procedure create_ea_tasks_isupp (
1120 p_api_version in number,
1121 p_init_msg_list in varchar2 := fnd_api.g_false,
1122 p_commit in varchar2 := fnd_api.g_false,
1123 p_sr_attributes_tbl in EA_SR_ATTR_TABLE_TYPE,
1124 p_request_id in number ,
1125 x_return_status OUT NOCOPY varchar2,
1126 x_msg_count OUT NOCOPY number,
1127 x_msg_data OUT NOCOPY varchar2,
1128 x_auto_task_gen_attempted OUT NOCOPY varchar2,
1129 x_field_service_Task_created OUT NOCOPY varchar2) is
1130 i number;
1131 l_prof_value fnd_profile_option_values.profile_option_value % type;
1132 l_err varchar2(4000);
1133 l_msg_index_out number;
1134 l_msg_data varchar2(2000);
1135 l_note_id number;
1136 l_note_type jtf_notes_b.note_type % type;
1137 l_login_id jtf_notes_b.last_update_login % type;
1138 l_user_id jtf_notes_b.last_updated_by % type;
1139 l_sr_rec CS_ServiceRequest_pub.service_request_rec_type;
1140 l_incident_number cs_incidents_all_b.incident_number % type;
1141 cursor c_sr is
1142 select * from cs_incidents_all_b
1143 where incident_id = p_request_id;
1144 begin
1145 l_prof_value := fnd_profile.value('AUTO GENERATE TASKS ON SR CREATE');
1146 if (l_prof_value = 'Task type Attribute configuration' or 1 =1) then
1147 for l_rec in c_sr loop
1148 l_sr_rec.type_id := l_rec.incident_type_id;
1149 l_sr_rec.status_id := l_rec.incident_status_id;
1150 l_sr_rec.urgency_id := l_rec.incident_urgency_id;
1151 l_sr_rec.severity_id := l_rec.incident_severity_id;
1152 l_sr_rec.obligation_date := l_rec.obligation_date;
1153 l_sr_rec.problem_code := l_rec.problem_code;
1154 l_sr_rec.inventory_item_id := l_rec.inventory_item_id;
1155 l_sr_rec.inventory_org_id := l_rec.inv_organization_id;
1156 l_sr_rec.customer_id := l_rec.customer_id;
1157 l_sr_rec.customer_number := l_rec.customer_number;
1158 l_sr_rec.category_id := l_rec.category_id;
1159 l_sr_rec.category_set_id := l_rec.category_set_id;
1160 l_sr_rec.incident_location_id := l_rec.incident_location_id;
1161 l_sr_rec.request_date := l_rec.incident_date;
1162 l_sr_rec.type_id := l_rec.incident_type_id;
1163 l_sr_rec.status_id := l_rec.incident_status_id;
1164 l_sr_rec.severity_id := l_rec.incident_severity_id;
1165 l_sr_rec.urgency_id := l_rec.incident_urgency_id;
1166 l_sr_rec.closed_date := l_rec.close_date;
1167 l_sr_rec.owner_id := l_rec.incident_owner_id;
1168 l_sr_rec.owner_group_id := l_rec.owner_group_id;
1169 l_sr_rec.publish_flag := l_rec.publish_flag;
1170 l_sr_rec.caller_type := l_rec.caller_type;
1171 l_sr_rec.customer_id := l_rec.customer_id;
1172 l_sr_rec.customer_number := l_rec.customer_number;
1173 l_sr_rec.employee_id := l_rec.employee_id;
1174 --l_sr_rec.employee_number := l_rec.employee_number;
1175 --l_sr_rec.verify_cp_flag := l_rec.verify_cp_flag;
1176 l_sr_rec.customer_product_id := l_rec.customer_product_id;
1177 l_sr_rec.platform_id := l_rec.platform_id;
1178 l_sr_rec.platform_version := l_rec.platform_version;
1179 l_sr_rec.db_version := l_rec.db_version;
1180 l_sr_rec.platform_version_id := l_rec.platform_version_id;
1181 l_sr_rec.cp_component_id := l_rec.cp_component_id;
1182 l_sr_rec.cp_component_version_id := l_rec.cp_component_version_id;
1183 l_sr_rec.cp_subcomponent_id := l_rec.cp_subcomponent_id;
1184 l_sr_rec.cp_subcomponent_version_id := l_rec.cp_subcomponent_version_id;
1185 l_sr_rec.language_id := l_rec.language_id;
1186 --l_sr_rec.language := l_rec.language;
1187 --l_sr_rec.cp_ref_number := l_rec.cp_ref_number;
1188 l_sr_rec.inventory_item_id := l_rec.inventory_item_id;
1189 l_sr_rec.inventory_org_id := l_rec.inv_organization_id;
1190 l_sr_rec.current_serial_number := l_rec.current_serial_number;
1191 l_sr_rec.original_order_number := l_rec.original_order_number;
1192 --l_sr_rec.purchase_order_num := l_rec.purchase_order_number;
1193 l_sr_rec.problem_code := l_rec.problem_code;
1194 l_sr_rec.exp_resolution_date := l_rec.expected_resolution_date;
1195 l_sr_rec.install_site_use_id := l_rec.install_site_use_id;
1196 l_sr_rec.request_attribute_1 := l_rec.incident_attribute_1;
1197 l_sr_rec.request_attribute_2 := l_rec.incident_attribute_2;
1198 l_sr_rec.request_attribute_3 := l_rec.incident_attribute_3;
1199 l_sr_rec.request_attribute_4 := l_rec.incident_attribute_4;
1200 l_sr_rec.request_attribute_5 := l_rec.incident_attribute_5;
1201 l_sr_rec.request_attribute_6 := l_rec.incident_attribute_6;
1202 l_sr_rec.request_attribute_7 := l_rec.incident_attribute_7;
1203 l_sr_rec.request_attribute_8 := l_rec.incident_attribute_8;
1204 l_sr_rec.request_attribute_9 := l_rec.incident_attribute_9;
1205 l_sr_rec.request_attribute_10 := l_rec.incident_attribute_10;
1206 l_sr_rec.request_attribute_11 := l_rec.incident_attribute_11;
1207 l_sr_rec.request_attribute_12 := l_rec.incident_attribute_12;
1208 l_sr_rec.request_attribute_13 := l_rec.incident_attribute_13;
1209 l_sr_rec.request_attribute_14 := l_rec.incident_attribute_14;
1210 l_sr_rec.request_attribute_15 := l_rec.incident_attribute_15;
1211 --l_sr_rec.request_context := l_rec.request_context;
1212 l_sr_rec.external_attribute_1 := l_rec.external_attribute_1;
1213 l_sr_rec.external_attribute_2 := l_rec.external_attribute_2;
1214 l_sr_rec.external_attribute_3 := l_rec.external_attribute_3;
1215 l_sr_rec.external_attribute_4 := l_rec.external_attribute_4;
1216 l_sr_rec.external_attribute_5 := l_rec.external_attribute_5;
1217 l_sr_rec.external_attribute_6 := l_rec.external_attribute_6;
1218 l_sr_rec.external_attribute_7 := l_rec.external_attribute_7;
1219 l_sr_rec.external_attribute_8 := l_rec.external_attribute_8;
1220 l_sr_rec.external_attribute_9 := l_rec.external_attribute_9;
1221 l_sr_rec.external_attribute_10 := l_rec.external_attribute_10;
1222 l_sr_rec.external_attribute_11 := l_rec.external_attribute_11;
1223 l_sr_rec.external_attribute_12 := l_rec.external_attribute_12;
1224 l_sr_rec.external_attribute_13 := l_rec.external_attribute_13;
1225 l_sr_rec.external_attribute_14 := l_rec.external_attribute_14;
1226 l_sr_rec.external_attribute_15 := l_rec.external_attribute_15;
1227 l_sr_rec.external_context := l_rec.external_context;
1228 l_sr_rec.bill_to_site_use_id := l_rec.bill_to_site_use_id;
1229 l_sr_rec.bill_to_contact_id := l_rec.bill_to_contact_id;
1230 l_sr_rec.ship_to_site_use_id := l_rec.ship_to_site_use_id;
1231 l_sr_rec.ship_to_contact_id := l_rec.ship_to_contact_id;
1232 l_sr_rec.resolution_code := l_rec.resolution_code;
1233 l_sr_rec.act_resolution_date := l_rec.actual_resolution_date;
1234 --l_sr_rec.public_comment_flag := l_rec.public_comment_flag;
1235 --l_sr_rec.parent_interaction_id := l_rec.parent_iteaction_id;
1236 l_sr_rec.contract_service_id := l_rec.contract_service_id;
1237 --l_sr_rec.contract_service_number := l_rec.contract_service_number;
1238 l_sr_rec.contract_id := l_rec.contract_id;
1239 l_sr_rec.project_number := l_rec.project_number;
1240 l_sr_rec.qa_collection_plan_id := l_rec.qa_collection_id;
1241 l_sr_rec.account_id := l_rec.account_id;
1242 l_sr_rec.resource_type := l_rec.resource_type;
1243 l_sr_rec.resource_subtype_id := l_rec.resource_subtype_id;
1244 --l_sr_rec.cust_po_number := l_rec.cust_po_number;
1245 --l_sr_rec.cust_ticket_number := l_rec.cust_ticket_number;
1246 l_sr_rec.sr_creation_channel := l_rec.sr_creation_channel;
1247 l_sr_rec.obligation_date := l_rec.obligation_date;
1248 l_sr_rec.time_zone_id := l_rec.time_zone_id;
1249 l_sr_rec.time_difference := l_rec.time_difference;
1250 l_sr_rec.site_id := l_rec.site_id;
1251 l_sr_rec.customer_site_id := l_rec.customer_site_id;
1252 l_sr_rec.territory_id := l_rec.territory_id;
1253 --l_sr_rec.initialize_flag := l_rec.initialize_flag;
1254 l_sr_rec.cp_revision_id := l_rec.cp_revision_id;
1255 l_sr_rec.inv_item_revision := l_rec.inv_item_revision;
1256 l_sr_rec.inv_component_id := l_rec.inv_component_id;
1257 l_sr_rec.inv_component_version := l_rec.inv_component_version;
1258 l_sr_rec.inv_subcomponent_id := l_rec.inv_subcomponent_id;
1259 l_sr_rec.inv_subcomponent_version := l_rec.inv_subcomponent_version;
1260 ------jngeorge---------------07/12/01
1261 l_sr_rec.tier := l_rec.tier;
1262 l_sr_rec.tier_version := l_rec.tier_version;
1263 l_sr_rec.operating_system := l_rec.operating_system;
1264 l_sr_rec.operating_system_version := l_rec.operating_system_version;
1265 l_sr_rec.database := l_rec.database;
1266 l_sr_rec.cust_pref_lang_id := l_rec.cust_pref_lang_id;
1267 l_sr_rec.category_id := l_rec.category_id;
1268 l_sr_rec.group_type := l_rec.group_type;
1269 l_sr_rec.group_territory_id := l_rec.group_territory_id;
1270 l_sr_rec.inv_platform_org_id := l_rec.inv_platform_org_id;
1271 l_sr_rec.component_version := l_rec.component_version;
1272 l_sr_rec.subcomponent_version := l_rec.subcomponent_version;
1273 --l_sr_rec.product_revision := l_rec.product_version;
1274 l_sr_rec.comm_pref_code := l_rec.comm_pref_code;
1275 ---- Added for Post 11.5.6 Enhancement
1276 l_sr_rec.cust_pref_lang_code := l_rec.cust_pref_lang_code;
1277 -- Changed the width from 1 to 30 for last_update_channel for bug 2688856
1278 -- shijain 3rd dec 2002
1279 l_sr_rec.last_update_channel := l_rec.last_update_channel;
1280 l_sr_rec.category_set_id := l_rec.category_set_id;
1281 l_sr_rec.external_reference := l_rec.external_reference;
1282 l_sr_rec.system_id := l_rec.system_id;
1283 ------jngeorge---------------07/12/0 := l_rec.
1284 l_sr_rec.error_code := l_rec.error_code;
1285 l_sr_rec.incident_occurred_date := l_rec.incident_occurred_date;
1286 l_sr_rec.incident_resolved_date := l_rec.incident_resolved_date;
1287 l_sr_rec.inc_responded_by_date := l_rec.inc_responded_by_date;
1288 --l_sr_rec.resolution_summary := l_rec.resolution_summary;
1289 l_sr_rec.incident_location_id := l_rec.incident_location_id;
1290 l_sr_rec.incident_address := l_rec.incident_address;
1291 l_sr_rec.incident_city := l_rec.incident_city;
1292 l_sr_rec.incident_state := l_rec.incident_state;
1293 l_sr_rec.incident_country := l_rec.incident_country;
1294 l_sr_rec.incident_province := l_rec.incident_province;
1295 l_sr_rec.incident_postal_code := l_rec.incident_postal_code;
1296 l_sr_rec.incident_county := l_rec.incident_country;
1297 -- Added for Enh# 221666 := l_rec.
1298 --l_sr_rec.owner := l_rec.ARCHAR2(360),
1299 --l_sr_rec.group_owner := l_rec.ARCHAR2(60),
1300 -- Added for Credit Card ER# 2255263 (UI ER#2208078)
1301 l_sr_rec.cc_number := l_rec.credit_card_number;
1302 l_sr_rec.cc_expiration_date := l_rec.credit_card_expiration_date;
1303 l_sr_rec.cc_type_code := l_rec.credit_card_type_code;
1304 l_sr_rec.cc_first_name := l_rec.credit_card_holder_fname;
1305 l_sr_rec.cc_last_name := l_rec.credit_card_holder_lname;
1306 l_sr_rec.cc_middle_name := l_rec.credit_card_holder_mname;
1307 l_sr_rec.cc_id := l_rec.credit_card_id;
1308 l_sr_rec.bill_to_account_id := l_rec.bill_to_account_id;
1309 l_sr_rec.ship_to_account_id := l_rec.ship_to_account_id;
1310 l_sr_rec.customer_phone_id := l_rec.customer_phone_id;
1311 l_sr_rec.customer_email_id := l_rec.customer_email_id;
1312 -- Added for source changes for 1159 by shijain oct 11 2002
1313 l_sr_rec.creation_program_code := l_rec.creation_program_code;
1314 l_sr_rec.last_update_program_code := l_rec.last_update_program_code;
1315 -- Bill_to_party, ship_to_party
1316 l_sr_rec.bill_to_party_id := l_rec.bill_to_party_id;
1317 l_sr_rec.ship_to_party_id := l_rec.ship_to_party_id;
1318 -- Conc request related fields
1319 l_sr_rec.program_id := l_rec.program_id;
1320 l_sr_rec.program_application_id := l_rec.program_application_id;
1321 --l_sr_rec.conc_request_id NUMBER, -- Renamed so that it doesn't clash with SR id
1322 l_sr_rec.program_login_id := l_rec.program_login_id;
1323 -- Bill_to_site, ship_to_site
1324 l_sr_rec.bill_to_site_id := l_rec.bill_to_site_id;
1325 l_sr_rec.ship_to_site_id := l_rec.ship_to_site_id;
1326 l_sr_rec.incident_point_of_interest := l_rec.incident_point_of_interest;
1327 l_sr_rec.incident_cross_street := l_rec.incident_cross_street;
1328 l_sr_rec.incident_direction_qualifier := l_rec.incident_direction_qualifier;
1329 l_sr_rec.incident_distance_qualifier := l_rec.incident_distance_qualifier;
1330 l_sr_rec.incident_distance_qual_uom := l_rec.incident_distance_qual_uom;
1331 l_sr_rec.incident_address2 := l_rec.incident_address2;
1332 l_sr_rec.incident_address3 := l_rec.incident_address3;
1333 l_sr_rec.incident_address4 := l_rec.incident_address4;
1334 l_sr_rec.incident_address_style := l_rec.incident_address_style;
1335 l_sr_rec.incident_addr_lines_phonetic := l_rec.incident_addr_lines_phonetic;
1336 l_sr_rec.incident_po_box_number := l_rec.incident_po_box_number;
1337 l_sr_rec.incident_house_number := l_rec.incident_house_number;
1338 l_sr_rec.incident_street_suffix := l_rec.incident_street_suffix;
1339 l_sr_rec.incident_street := l_rec.incident_street;
1340 l_sr_rec.incident_street_number := l_rec.incident_street_number;
1341 l_sr_rec.incident_floor := l_rec.incident_floor;
1342 l_sr_rec.incident_suite := l_rec.incident_suite;
1343 l_sr_rec.incident_postal_plus4_code := l_rec.incident_postal_plus4_code;
1344 l_sr_rec.incident_position := l_rec.incident_position;
1345 l_sr_rec.incident_location_directions := l_rec.incident_location_directions;
1346 l_sr_rec.incident_location_description := l_rec.incident_location_description;
1347 l_sr_rec.install_site_id := l_rec.install_site_id;
1348
1349 --
1350 l_incident_number := l_rec.incident_number;
1351 end loop;
1352 create_extnd_attr_tasks (
1353 p_api_version ,
1354 p_init_msg_list ,
1355 p_commit ,
1356 l_sr_rec ,
1357 p_sr_attributes_tbl ,
1358 p_request_id ,
1359 l_incident_number ,
1360 x_return_status ,
1361 x_msg_count ,
1362 x_msg_data ,
1363 x_auto_task_gen_attempted ,
1364 x_field_service_Task_created );
1365 --dbms_output.put_line('Return Message(isupp):'||x_return_status||':');
1366 if (x_return_status <> 'S') then
1367 for i in 1..x_msg_count loop
1368 FND_MSG_PUB.Get(p_msg_index=>i,
1369 p_encoded=>'F',
1370 p_data=>l_msg_data,
1371 p_msg_index_out=>l_msg_index_out);
1372 l_err := l_err || l_msg_data || ',';
1373 end loop;
1374 l_note_type := fnd_profile.value('CS_SR_TASK_ERROR_NOTE_TYPE');
1375 if (l_note_type is null) then
1376 fnd_message.set_name ('CS', 'CS_EA_NULL_NOTE_TYPE');
1377 fnd_msg_pub.add;
1378 raise fnd_api.g_exc_unexpected_error;
1379 end if;
1380 l_login_id := fnd_global.login_id;
1381 l_user_id := fnd_global.user_id ;
1382 jtf_notes_pub.create_note(
1383 p_api_version => 1,
1384 p_init_msg_list => p_init_msg_list,
1385 p_commit => p_commit,
1386 p_validation_level => fnd_api.g_valid_level_full,
1387 x_return_status => x_return_status,
1388 x_msg_count => x_msg_count,
1389 x_msg_data => x_msg_data,
1390 p_entered_by => l_user_id,
1391 p_entered_date => sysdate,
1392 p_last_update_date => sysdate,
1393 p_last_updated_by => l_user_id,
1394 p_creation_date => sysdate,
1395 p_created_by => l_user_id,
1396 p_last_update_login => l_login_id,
1397 p_source_object_id => p_request_id,
1398 p_source_object_code => 'SR',
1399 p_notes => l_err,
1400 p_notes_detail => l_err,
1401 p_note_type => l_note_type,
1402 p_note_status => 'P',
1403 x_jtf_note_id => l_note_id
1404 );
1405 --dbms_output.put_line('Return Message(note):'||x_return_status||':');
1406 end if; -- check for errors returned by autogen api
1407 end if; -- profile option check
1408 EXCEPTION
1409 WHEN fnd_api.g_exc_unexpected_error THEN
1410 x_return_status := fnd_api.g_ret_sts_unexp_error;
1411 fnd_msg_pub.count_and_get (
1412 p_count => x_msg_count,
1413 p_data => x_msg_data);
1414 WHEN OTHERS THEN
1415 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
1416 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
1417 fnd_msg_pub.add;
1418 x_return_status := fnd_api.g_ret_sts_unexp_error;
1419 fnd_msg_pub.count_and_get (
1420 p_count => x_msg_count,
1421 p_data => x_msg_data);
1422 end ;
1423 ***** 9/30/2003 *******/
1424 end cs_ea_autogen_tasks_pvt;