[Home] [Help]
PACKAGE BODY: APPS.JTF_TASKS_PKG
Source
1 PACKAGE BODY jtf_tasks_pkg AS
2 /* $Header: jtftktab.pls 120.2.12010000.4 2008/11/01 05:28:06 appldev ship $ */
3 PROCEDURE insert_row (
4 x_rowid IN OUT NOCOPY VARCHAR2,
5 x_task_id IN NUMBER,
6 x_attribute4 IN VARCHAR2,
7 x_attribute5 IN VARCHAR2,
8 x_attribute6 IN VARCHAR2,
9 x_attribute7 IN VARCHAR2,
10 x_attribute8 IN VARCHAR2,
11 x_attribute9 IN VARCHAR2,
12 x_attribute10 IN VARCHAR2,
13 x_attribute11 IN VARCHAR2,
14 x_attribute12 IN VARCHAR2,
15 x_attribute13 IN VARCHAR2,
16 x_attribute14 IN VARCHAR2,
17 x_attribute15 IN VARCHAR2,
18 x_attribute_category IN VARCHAR2,
19 x_task_number IN VARCHAR2,
20 x_task_type_id IN NUMBER,
21 x_task_status_id IN NUMBER,
22 x_task_priority_id IN NUMBER,
23 x_owner_id IN NUMBER,
24 x_owner_type_code IN VARCHAR2,
25 x_assigned_by_id IN NUMBER,
26 x_cust_account_id IN NUMBER,
27 x_customer_id IN NUMBER,
28 x_address_id IN NUMBER,
29 x_planned_start_date IN DATE,
30 x_palm_flag IN VARCHAR2,
31 x_wince_flag IN VARCHAR2,
32 x_laptop_flag IN VARCHAR2,
33 x_device1_flag IN VARCHAR2,
34 x_device2_flag IN VARCHAR2,
35 x_device3_flag IN VARCHAR2,
36 x_costs IN NUMBER,
37 x_currency_code IN VARCHAR2,
38 x_attribute1 IN VARCHAR2,
39 x_attribute2 IN VARCHAR2,
40 x_attribute3 IN VARCHAR2,
41 x_notification_period IN NUMBER,
42 x_notification_period_uom IN VARCHAR2,
43 x_parent_task_id IN NUMBER,
44 x_recurrence_rule_id IN NUMBER,
45 x_alarm_start IN NUMBER,
46 x_alarm_start_uom IN VARCHAR2,
47 x_alarm_on IN VARCHAR2,
48 x_alarm_count IN NUMBER,
49 x_alarm_fired_count IN NUMBER,
50 x_alarm_interval IN NUMBER,
51 x_alarm_interval_uom IN VARCHAR2,
52 x_deleted_flag IN VARCHAR2,
53 x_actual_start_date IN DATE,
54 x_actual_end_date IN DATE,
55 x_source_object_type_code IN VARCHAR2,
56 x_timezone_id IN NUMBER,
57 x_source_object_id IN NUMBER,
58 x_source_object_name IN VARCHAR2,
59 x_duration IN NUMBER,
60 x_duration_uom IN VARCHAR2,
61 x_planned_effort IN NUMBER,
62 x_planned_effort_uom IN VARCHAR2,
63 x_actual_effort IN NUMBER,
64 x_actual_effort_uom IN VARCHAR2,
65 x_percentage_complete IN NUMBER,
66 x_reason_code IN VARCHAR2,
67 x_private_flag IN VARCHAR2,
68 x_publish_flag IN VARCHAR2,
69 x_restrict_closure_flag IN VARCHAR2,
70 x_multi_booked_flag IN VARCHAR2,
71 x_milestone_flag IN VARCHAR2,
72 x_holiday_flag IN VARCHAR2,
73 x_billable_flag IN VARCHAR2,
74 x_bound_mode_code IN VARCHAR2,
75 x_soft_bound_flag IN VARCHAR2,
76 x_workflow_process_id IN NUMBER,
77 x_notification_flag IN VARCHAR2,
78 x_planned_end_date IN DATE,
79 x_scheduled_start_date IN DATE,
80 x_scheduled_end_date IN DATE,
81 x_task_name IN VARCHAR2,
82 x_description IN VARCHAR2,
83 x_creation_date IN DATE,
84 x_created_by IN NUMBER,
85 x_last_update_date IN DATE,
86 x_last_updated_by IN NUMBER,
87 x_last_update_login IN NUMBER,
88 x_owner_territory_id IN NUMBER,
89 x_escalation_level IN VARCHAR2,
90 x_calendar_start_date IN DATE ,
91 x_calendar_end_date IN DATE ,
92 x_date_selected IN VARCHAR2 ,
93 x_template_id IN NUMBER ,
94 x_template_group_id IN NUMBER ,
95 x_open_flag IN VARCHAR2,
96 x_entity IN VARCHAR2,
97 x_task_confirmation_status IN VARCHAR2,
98 x_task_confirmation_counter IN NUMBER,
99 x_task_split_flag IN VARCHAR2,
100 x_child_position IN VARCHAR2,
101 x_child_sequence_num IN NUMBER,
102 x_location_id IN NUMBER
103 )
104 IS
105 CURSOR c
106 IS
107 SELECT ROWID
108 FROM jtf_tasks_b
109 WHERE task_id = x_task_id;
110
111 x_return_status varchar2(1);
112 x_msg_data VARCHAR2(2000);
113 x_msg_count NUMBER;
114
115 l_task_audit_id NUMBER;
116
117 l_source_object_type_code jtf_tasks_b.source_object_type_code%type ;
118 l_source_object_id jtf_tasks_b.source_object_id%type ;
119 l_source_object_name jtf_tasks_b.source_object_name%type ;
120
121 BEGIN
122
123
124
125 INSERT INTO jtf_tasks_b (
126 attribute4,
127 attribute5,
128 attribute6,
129 attribute7,
130 attribute8,
131 attribute9,
132 attribute10,
133 attribute11,
134 attribute12,
135 attribute13,
136 attribute14,
137 attribute15,
138 attribute_category,
139 task_id,
140 task_number,
141 task_type_id,
142 task_status_id,
143 task_priority_id,
144 owner_id,
145 owner_type_code,
146 assigned_by_id,
147 cust_account_id,
148 customer_id,
149 address_id,
150 planned_start_date,
151 palm_flag,
152 wince_flag,
153 laptop_flag,
154 device1_flag,
155 device2_flag,
156 device3_flag,
157 costs,
158 currency_code,
159 attribute1,
160 attribute2,
161 attribute3,
162 notification_period,
163 notification_period_uom,
164 parent_task_id,
165 recurrence_rule_id,
166 alarm_start,
167 alarm_start_uom,
168 alarm_on,
169 alarm_count,
170 alarm_fired_count,
171 alarm_interval,
172 alarm_interval_uom,
173 deleted_flag,
174 actual_start_date,
175 actual_end_date,
176 source_object_type_code,
177 timezone_id,
178 source_object_id,
179 source_object_name,
180 duration,
181 duration_uom,
182 planned_effort,
183 planned_effort_uom,
184 actual_effort,
185 actual_effort_uom,
186 percentage_complete,
187 reason_code,
188 private_flag,
189 publish_flag,
190 restrict_closure_flag,
191 multi_booked_flag,
192 milestone_flag,
193 holiday_flag,
194 billable_flag,
195 bound_mode_code,
196 soft_bound_flag,
197 workflow_process_id,
198 notification_flag,
199 planned_end_date,
200 scheduled_start_date,
201 scheduled_end_date,
202 creation_date,
203 created_by,
204 last_update_date,
205 last_updated_by,
206 last_update_login,
207 object_version_number,
208 owner_territory_id,
209 escalation_level,
210 calendar_start_date,
211 calendar_end_date,
212 date_selected,
213 template_id,
214 template_group_id,
215 object_changed_date,
216 open_flag,
217 entity,
218 task_confirmation_status,
219 task_confirmation_counter,
220 task_split_flag,
221 child_position,
222 child_sequence_num,
223 location_id
224 )
225 VALUES (
226 x_attribute4,
227 x_attribute5,
228 x_attribute6,
229 x_attribute7,
230 x_attribute8,
231 x_attribute9,
232 x_attribute10,
233 x_attribute11,
234 x_attribute12,
235 x_attribute13,
236 x_attribute14,
237 x_attribute15,
238 x_attribute_category,
239 x_task_id,
240 x_task_number,
241 x_task_type_id,
242 x_task_status_id,
243 x_task_priority_id,
244 x_owner_id,
245 x_owner_type_code,
246 x_assigned_by_id,
247 x_cust_account_id,
248 x_customer_id,
249 x_address_id,
250 x_planned_start_date,
251 x_palm_flag,
252 x_wince_flag,
253 x_laptop_flag,
254 x_device1_flag,
255 x_device2_flag,
256 x_device3_flag,
257 x_costs,
258 x_currency_code,
259 x_attribute1,
260 x_attribute2,
261 x_attribute3,
262 x_notification_period,
263 x_notification_period_uom,
264 x_parent_task_id,
265 x_recurrence_rule_id,
266 x_alarm_start,
267 x_alarm_start_uom,
268 x_alarm_on,
269 x_alarm_count,
270 x_alarm_fired_count,
271 x_alarm_interval,
272 x_alarm_interval_uom,
273 x_deleted_flag,
274 x_actual_start_date,
275 x_actual_end_date,
276 x_source_object_type_code,
277 x_timezone_id,
278 x_source_object_id,
279 x_source_object_name,
280 x_duration,
281 x_duration_uom,
282 x_planned_effort,
283 x_planned_effort_uom,
284 x_actual_effort,
285 x_actual_effort_uom,
286 x_percentage_complete,
287 x_reason_code,
288 x_private_flag,
289 x_publish_flag,
290 x_restrict_closure_flag,
291 x_multi_booked_flag,
292 x_milestone_flag,
293 x_holiday_flag,
294 x_billable_flag,
295 x_bound_mode_code,
296 x_soft_bound_flag,
297 x_workflow_process_id,
298 x_notification_flag,
299 x_planned_end_date,
300 x_scheduled_start_date,
301 x_scheduled_end_date,
302 x_creation_date,
303 x_created_by,
304 x_last_update_date,
305 x_last_updated_by,
306 x_last_update_login,
307 1,
308 x_owner_territory_id,
309 x_escalation_level,
310 x_calendar_start_date,
311 x_calendar_end_date,
312 x_date_selected,
313 x_template_id,
314 x_template_group_id,
315 SYSDATE,
316 x_open_flag,
317 x_entity,
318 x_task_confirmation_status,
319 x_task_confirmation_counter,
320 x_task_split_flag,
321 x_child_position,
322 x_child_sequence_num,
323 x_location_id
324 );
325
326
327
328
329 OPEN c;
330 FETCH c INTO x_rowid;
331
332 IF (c%NOTFOUND)
333 THEN
334 CLOSE c;
335
336 RAISE NO_DATA_FOUND;
337 END IF;
338
339 CLOSE c;
340
341 INSERT INTO jtf_tasks_tl
342 (task_name,
343 description,
344 created_by,
345 creation_date,
346 last_updated_by,
347 last_update_date,
348 last_update_login,
352 )
349 task_id,
350 language,
351 source_lang
353 SELECT x_task_name,
354 x_description,
355 x_created_by,
356 x_creation_date,
357 x_last_updated_by,
358 x_last_update_date,
359 x_last_update_login,
360 x_task_id,
361 l.language_code,
362 USERENV ('LANG')
363 FROM fnd_languages l
364 WHERE l.installed_flag IN ('I', 'B')
365 AND NOT EXISTS (SELECT NULL
366 FROM jtf_tasks_tl t
367 WHERE t.task_id = x_task_id
368 AND t.language = l.language_code);
369 OPEN c;
370 FETCH c INTO x_rowid;
371
372 IF (c%NOTFOUND)
373 THEN
374 CLOSE c;
375
376 RAISE NO_DATA_FOUND;
377 END IF;
378
379 CLOSE c;
380
381 if( x_template_id is NULL) THEN
382 jtf_task_audits_pvt.create_task_audits (
383 p_api_version => 1,
384 p_init_msg_list => fnd_api.g_false,
385 p_commit => fnd_api.g_false,
386 p_object_version_number => 1,
387 p_task_id => x_task_id,
388 p_new_billable_flag => x_billable_flag,
389 p_new_device1_flag => x_device1_flag,
390 p_new_device2_flag => x_device2_flag,
391 p_new_device3_flag => x_device3_flag,
392 p_new_holiday_flag => x_holiday_flag,
393 p_new_laptop_flag => x_laptop_flag,
394 p_new_milestone_flag => x_milestone_flag,
395 p_new_multi_booked_flag => x_multi_booked_flag,
396 p_new_not_flag => x_notification_flag,
397 p_new_palm_flag => x_palm_flag,
398 p_new_private_flag => x_private_flag,
399 p_new_publish_flag => x_publish_flag,
400 p_new_restrict_closure_flag => x_restrict_closure_flag,
401 p_new_wince_flag => x_wince_flag,
402 p_new_soft_bound_flag => x_soft_bound_flag,
403 p_new_actual_effort => x_actual_effort,
404 p_new_actual_effort_uom => x_actual_effort_uom,
405 p_new_actual_end_date => x_actual_end_date,
406 p_new_actual_start_date => x_actual_start_date,
407 p_new_address_id => x_address_id,
408 p_new_assigned_by_id => x_assigned_by_id,
409 p_new_bound_mode_code => x_bound_mode_code,
410 p_new_costs => x_costs,
411 p_new_currency_code => x_currency_code,
412 p_new_customer_id => x_customer_id,
413 p_new_cust_account_id => x_cust_account_id,
414 p_new_duration => x_duration,
415 p_new_duration_uom => x_duration_uom,
416 p_new_not_period => x_notification_period,
417 p_new_not_period_uom => x_notification_period_uom,
418 p_new_owner_id => x_owner_id,
419 p_new_owner_type_code => x_owner_type_code,
420 p_new_parent_task_id => x_parent_task_id,
421 p_new_per_complete => x_percentage_complete,
422 p_new_planned_effort => x_planned_effort,
423 p_new_planned_effort_uom => x_planned_effort_uom,
424 p_new_planned_end_date => x_planned_end_date,
425 p_new_planned_start_date => x_planned_start_date,
426 p_new_reason_code => x_reason_code,
427 p_new_recurrence_rule_id => x_recurrence_rule_id,
428 p_new_sched_end_date => x_scheduled_end_date,
429 p_new_sched_start_date => x_scheduled_start_date,
430 p_new_src_obj_id => x_source_object_id,
431 p_new_src_obj_name => x_source_object_name,
432 p_new_src_obj_type_code => x_source_object_type_code,
433 p_new_task_priority_id => x_task_priority_id,
434 p_new_task_status_id => x_task_status_id,
435 p_new_task_type_id => x_task_type_id,
436 p_new_timezone_id => x_timezone_id,
437 p_new_workflow_process_id => x_workflow_process_id,
438 p_not_chan_flag => NULL,
439 p_new_description => x_description,
440 p_new_task_name => x_task_name,
441 p_new_escalation_level => x_escalation_level,
442 p_new_owner_territory_id => x_owner_territory_id,
443 p_new_date_selected => x_date_selected,
444 p_new_location_id => x_location_id,
445 -- p_new_task_conf_status => l_Task_Upd_Rec.task_confirmation_status,
446 -- p_new_task_conf_counter => l_Task_Upd_Rec.task_confirmation_counter,
447 -- p_new_task_split_flag => l_Task_Upd_Rec.task_split_flag,
448 x_return_status => x_return_status,
449 x_msg_count => x_msg_count,
450 x_msg_data => x_msg_data,
451 x_task_audit_id => l_task_audit_id
452 );
453 end if;
454 END insert_row;
455
456 PROCEDURE lock_row (x_task_id IN NUMBER, x_object_version_number IN NUMBER)
457 IS
458 CURSOR c
459 IS
460 SELECT object_version_number
461 FROM jtf_tasks_vl
462 WHERE task_id = x_task_id
463 FOR UPDATE OF task_id NOWAIT;
464
465 recinfo c%ROWTYPE;
466
467 CURSOR c1
468 IS
469 SELECT task_name, description, DECODE (language, USERENV ('LANG'), 'Y', 'N') baselang
470 FROM jtf_tasks_tl
471 WHERE task_id = x_task_id
475 e_resource_busy exception;
472 AND USERENV ('LANG') IN (language, source_lang)
473 FOR UPDATE OF task_id NOWAIT;
474
476 pragma exception_init(e_resource_busy, -54);
477
478 BEGIN
479 OPEN c;
480 FETCH c INTO recinfo;
481
482 IF (c%NOTFOUND)
483 THEN
484 CLOSE c;
485 fnd_message.set_name ('JTF', 'JTF_API_RECORD_NOT_FOUND');
486 app_exception.raise_exception;
487 END IF;
488
489 CLOSE c;
490
491 IF (recinfo.object_version_number = x_object_version_number)
492 THEN
493 NULL;
494 ELSE
495 fnd_message.set_name ('JTF', 'JTF_API_RECORD_NOT_FOUND');
496 fnd_msg_pub.add;
497 app_exception.raise_exception;
498 END IF;
499
500 FOR tlinfo IN c1
501 LOOP
502 IF (tlinfo.baselang = 'Y')
503 THEN
504 NULL;
505 END IF;
506 END LOOP;
507
508 RETURN;
509
510 exception
511 when e_resource_busy then
512 fnd_message.set_name('FND', 'FND_LOCK_RECORD_ERROR');
513 fnd_msg_pub.add;
514 app_exception.raise_exception;
515
516 END lock_row;
517
518 --BES Changes
519 -- original version to call the new version which has task_audit_id
520
521 PROCEDURE update_row (
522 x_task_id IN NUMBER,
523 x_object_version_number IN NUMBER,
524 x_attribute4 IN VARCHAR2,
525 x_attribute5 IN VARCHAR2,
526 x_attribute6 IN VARCHAR2,
527 x_attribute7 IN VARCHAR2,
528 x_attribute8 IN VARCHAR2,
529 x_attribute9 IN VARCHAR2,
530 x_attribute10 IN VARCHAR2,
531 x_attribute11 IN VARCHAR2,
532 x_attribute12 IN VARCHAR2,
533 x_attribute13 IN VARCHAR2,
534 x_attribute14 IN VARCHAR2,
535 x_attribute15 IN VARCHAR2,
536 x_attribute_category IN VARCHAR2,
537 x_task_number IN VARCHAR2,
538 x_task_type_id IN NUMBER,
539 x_task_status_id IN NUMBER,
540 x_task_priority_id IN NUMBER,
541 x_owner_id IN NUMBER,
542 x_owner_type_code IN VARCHAR2,
543 x_assigned_by_id IN NUMBER,
544 x_cust_account_id IN NUMBER,
545 x_customer_id IN NUMBER,
546 x_address_id IN NUMBER,
547 x_planned_start_date IN DATE,
548 x_palm_flag IN VARCHAR2,
549 x_wince_flag IN VARCHAR2,
550 x_laptop_flag IN VARCHAR2,
551 x_device1_flag IN VARCHAR2,
552 x_device2_flag IN VARCHAR2,
553 x_device3_flag IN VARCHAR2,
554 x_costs IN NUMBER,
555 x_currency_code IN VARCHAR2,
556 x_attribute1 IN VARCHAR2,
557 x_attribute2 IN VARCHAR2,
558 x_attribute3 IN VARCHAR2,
559 x_notification_period IN NUMBER,
560 x_notification_period_uom IN VARCHAR2,
561 x_parent_task_id IN NUMBER,
562 x_recurrence_rule_id IN NUMBER,
563 x_alarm_start IN NUMBER,
564 x_alarm_start_uom IN VARCHAR2,
565 x_alarm_on IN VARCHAR2,
566 x_alarm_count IN NUMBER,
567 x_alarm_fired_count IN NUMBER,
568 x_alarm_interval IN NUMBER,
569 x_alarm_interval_uom IN VARCHAR2,
570 x_deleted_flag IN VARCHAR2,
571 x_actual_start_date IN DATE,
572 x_actual_end_date IN DATE,
573 x_source_object_type_code IN VARCHAR2,
574 x_timezone_id IN NUMBER,
575 x_source_object_id IN NUMBER,
576 x_source_object_name IN VARCHAR2,
577 x_duration IN NUMBER,
578 x_duration_uom IN VARCHAR2,
579 x_planned_effort IN NUMBER,
580 x_planned_effort_uom IN VARCHAR2,
581 x_actual_effort IN NUMBER,
582 x_actual_effort_uom IN VARCHAR2,
583 x_percentage_complete IN NUMBER,
584 x_reason_code IN VARCHAR2,
585 x_private_flag IN VARCHAR2,
586 x_publish_flag IN VARCHAR2,
587 x_restrict_closure_flag IN VARCHAR2,
588 x_multi_booked_flag IN VARCHAR2,
589 x_milestone_flag IN VARCHAR2,
590 x_holiday_flag IN VARCHAR2,
591 x_billable_flag IN VARCHAR2,
592 x_bound_mode_code IN VARCHAR2,
593 x_soft_bound_flag IN VARCHAR2,
594 x_workflow_process_id IN NUMBER,
595 x_notification_flag IN VARCHAR2,
596 x_planned_end_date IN DATE,
597 x_scheduled_start_date IN DATE,
598 x_scheduled_end_date IN DATE,
599 x_task_name IN VARCHAR2,
603 x_last_update_login IN NUMBER,
600 x_description IN VARCHAR2,
601 x_last_update_date IN DATE,
602 x_last_updated_by IN NUMBER,
604 x_owner_territory_id IN NUMBER,
605 x_escalation_level IN VARCHAR2,
606 x_calendar_start_date IN DATE ,
607 x_calendar_end_date IN DATE ,
608 x_date_selected IN VARCHAR2 ,
609 x_open_flag IN VARCHAR2,
610 x_task_confirmation_status IN VARCHAR2,
611 x_task_confirmation_counter IN NUMBER,
612 x_task_split_flag IN VARCHAR2,
613 x_child_position IN VARCHAR2,
614 x_child_sequence_num IN NUMBER,
615 x_location_id IN NUMBER
616 )
617 IS
618 x_return_status VARCHAR2(1);
619 x_msg_data VARCHAR2(2000);
620 x_msg_count NUMBER;
621
622 l_task_audit_id number ;
623
624 my_message VARCHAR2(2000);
625 l_count NUMBER;
626 l_msg_count NUMBER;
627 l_msg_data VARCHAR2(2000);
628
629 --BES enh 2391065
630 l_Task_Upd_Rec JTF_TASKS_PKG.Task_Upd_Rec;
631
632
633 BEGIN
634
635
636 l_Task_Upd_Rec.TASK_ID := x_task_id;
637 l_Task_Upd_Rec.OBJECT_VERSION_NUMBER := x_object_version_number;
638 l_Task_Upd_Rec.ATTRIBUTE4 := x_attribute4;
639 l_Task_Upd_Rec.ATTRIBUTE5 := x_attribute5;
640 l_Task_Upd_Rec.ATTRIBUTE6 := x_attribute6;
641 l_Task_Upd_Rec.ATTRIBUTE7 := x_attribute7;
642 l_Task_Upd_Rec.ATTRIBUTE8 := x_attribute8;
643 l_Task_Upd_Rec.ATTRIBUTE9 := x_attribute9;
644 l_Task_Upd_Rec.ATTRIBUTE10 := x_attribute10;
645 l_Task_Upd_Rec.ATTRIBUTE11 := x_attribute11;
646 l_Task_Upd_Rec.ATTRIBUTE12 := x_attribute12;
647 l_Task_Upd_Rec.ATTRIBUTE13 := x_attribute13;
648 l_Task_Upd_Rec.ATTRIBUTE14 := x_attribute14;
649 l_Task_Upd_Rec.ATTRIBUTE15 := x_attribute15;
650 l_Task_Upd_Rec.ATTRIBUTE_CATEGORY := x_attribute_category;
651 l_Task_Upd_Rec.TASK_NUMBER := x_task_number;
652 l_Task_Upd_Rec.TASK_TYPE_ID := x_task_type_id;
653 l_Task_Upd_Rec.TASK_STATUS_ID := x_task_status_id;
654 l_Task_Upd_Rec.TASK_PRIORITY_ID := x_task_priority_id;
655 l_Task_Upd_Rec.OWNER_ID := x_owner_id;
656 l_Task_Upd_Rec.OWNER_TYPE_CODE := x_owner_type_code;
657 l_Task_Upd_Rec.ASSIGNED_BY_ID := x_assigned_by_id;
658 l_Task_Upd_Rec.CUST_ACCOUNT_ID := x_cust_account_id;
659 l_Task_Upd_Rec.CUSTOMER_ID := x_customer_id;
660 l_Task_Upd_Rec.ADDRESS_ID := x_address_id;
661 l_Task_Upd_Rec.PLANNED_START_DATE := x_planned_start_date;
662 l_Task_Upd_Rec.PALM_FLAG := x_palm_flag;
663 l_Task_Upd_Rec.WINCE_FLAG := x_wince_flag;
664 l_Task_Upd_Rec.LAPTOP_FLAG := x_laptop_flag;
665 l_Task_Upd_Rec.DEVICE1_FLAG := x_device1_flag;
666 l_Task_Upd_Rec.DEVICE2_FLAG := x_device2_flag;
667 l_Task_Upd_Rec.DEVICE3_FLAG := x_device3_flag;
668 l_Task_Upd_Rec.COSTS := x_costs;
669 l_Task_Upd_Rec.CURRENCY_CODE := x_currency_code;
670 l_Task_Upd_Rec.ATTRIBUTE1 := x_attribute1;
671 l_Task_Upd_Rec.ATTRIBUTE2 := x_attribute2;
672 l_Task_Upd_Rec.ATTRIBUTE3 := x_attribute3;
673 l_Task_Upd_Rec.NOTIFICATION_PERIOD := x_notification_period;
674 l_Task_Upd_Rec.NOTIFICATION_PERIOD_UOM := x_notification_period_uom;
675 l_Task_Upd_Rec.PARENT_TASK_ID := x_parent_task_id;
676 l_Task_Upd_Rec.RECURRENCE_RULE_ID := x_recurrence_rule_id;
677 l_Task_Upd_Rec.ALARM_START := x_alarm_start;
678 l_Task_Upd_Rec.ALARM_START_UOM := x_alarm_start_uom;
679 l_Task_Upd_Rec.ALARM_ON := x_alarm_on;
680 l_Task_Upd_Rec.ALARM_COUNT := x_alarm_count;
681 l_Task_Upd_Rec.ALARM_FIRED_COUNT := x_alarm_fired_count;
682 l_Task_Upd_Rec.ALARM_INTERVAL := x_alarm_interval;
683 l_Task_Upd_Rec.ALARM_INTERVAL_UOM := x_alarm_interval_uom;
684 l_Task_Upd_Rec.DELETED_FLAG := x_deleted_flag;
685 l_Task_Upd_Rec.ACTUAL_START_DATE := x_actual_start_date;
686 l_Task_Upd_Rec.ACTUAL_END_DATE := x_actual_end_date;
687 l_Task_Upd_Rec.SOURCE_OBJECT_TYPE_CODE := x_source_object_type_code;
688 l_Task_Upd_Rec.TIMEZONE_ID := x_timezone_id;
689 l_Task_Upd_Rec.SOURCE_OBJECT_ID := x_source_object_id;
690 l_Task_Upd_Rec.SOURCE_OBJECT_NAME := x_source_object_name;
691 l_Task_Upd_Rec.DURATION := x_duration;
692 l_Task_Upd_Rec.DURATION_UOM := x_duration_uom;
693 l_Task_Upd_Rec.PLANNED_EFFORT := x_planned_effort;
694 l_Task_Upd_Rec.PLANNED_EFFORT_UOM := x_planned_effort_uom;
695 l_Task_Upd_Rec.ACTUAL_EFFORT := x_actual_effort;
696 l_Task_Upd_Rec.ACTUAL_EFFORT_UOM := x_actual_effort_uom;
697 l_Task_Upd_Rec.PERCENTAGE_COMPLETE := x_percentage_complete;
698 l_Task_Upd_Rec.REASON_CODE := x_reason_code;
699 l_Task_Upd_Rec.PRIVATE_FLAG := x_private_flag;
700 l_Task_Upd_Rec.PUBLISH_FLAG := x_publish_flag;
701 l_Task_Upd_Rec.RESTRICT_CLOSURE_FLAG := x_restrict_closure_flag;
702 l_Task_Upd_Rec.MULTI_BOOKED_FLAG := x_multi_booked_flag;
703 l_Task_Upd_Rec.MILESTONE_FLAG := x_milestone_flag;
704 l_Task_Upd_Rec.HOLIDAY_FLAG := x_holiday_flag;
705 l_Task_Upd_Rec.BILLABLE_FLAG := x_billable_flag;
706 l_Task_Upd_Rec.BOUND_MODE_CODE := x_bound_mode_code;
707 l_Task_Upd_Rec.SOFT_BOUND_FLAG := x_soft_bound_flag;
711 l_Task_Upd_Rec.SCHEDULED_START_DATE := x_scheduled_start_date;
708 l_Task_Upd_Rec.WORKFLOW_PROCESS_ID := x_workflow_process_id;
709 l_Task_Upd_Rec.NOTIFICATION_FLAG := x_notification_flag;
710 l_Task_Upd_Rec.PLANNED_END_DATE := x_planned_end_date;
712 l_Task_Upd_Rec.SCHEDULED_END_DATE := x_scheduled_end_date;
713 l_Task_Upd_Rec.TASK_NAME := x_task_name;
714 l_Task_Upd_Rec.DESCRIPTION := x_description;
715 l_Task_Upd_Rec.LAST_UPDATE_DATE := x_last_update_date;
716 l_Task_Upd_Rec.LAST_UPDATED_BY := x_last_updated_by;
717 l_Task_Upd_Rec.LAST_UPDATE_LOGIN := x_last_update_login;
718 l_Task_Upd_Rec.OWNER_TERRITORY_ID := x_owner_territory_id;
719 l_Task_Upd_Rec.ESCALATION_LEVEL := x_escalation_level;
720 l_Task_Upd_Rec.calendar_start_date := x_calendar_start_date;
721 l_Task_Upd_Rec.calendar_end_date := x_calendar_end_date;
722 l_Task_Upd_Rec.date_selected := x_date_selected;
723 l_Task_Upd_Rec.open_flag := x_open_flag;
724 l_Task_Upd_Rec.task_audit_id := FND_API.G_MISS_NUM;
725 l_Task_Upd_Rec.task_confirmation_status := x_task_confirmation_status;
726 l_Task_Upd_Rec.task_confirmation_counter := x_task_confirmation_counter;
727 l_Task_Upd_Rec.task_split_flag := x_task_split_flag;
728 l_Task_Upd_Rec.child_position := x_child_position;
729 l_Task_Upd_Rec.child_sequence_num := x_child_sequence_num;
730 l_Task_Upd_Rec.location_id := x_location_id;
731
732 -- call new version
733
734 update_row ( p_Task_Upd_Rec => l_Task_Upd_Rec,
735 p_task_audit_id => l_task_audit_id);
736
737
738 END update_row;
739
740
741 -- new version which has task_audit_id
742
743 PROCEDURE update_row (
744 p_Task_Upd_Rec IN JTF_TASKS_PKG.Task_Upd_Rec,
745 p_Task_Audit_Id OUT NOCOPY JTF_TASK_AUDITS_B.TASK_AUDIT_ID%TYPE
746 )
747 IS
748 x_return_status VARCHAR2(1);
749 x_msg_data VARCHAR2(2000);
750 x_msg_count NUMBER;
751
752 l_source_object_type_code jtf_tasks_b.source_object_type_code%type ;
753 l_source_object_id jtf_tasks_b.source_object_id%type ;
754 l_source_object_name jtf_tasks_b.source_object_name%type ;
755
756 l_task_audit_id number ;
757 my_message VARCHAR2(2000);
758 l_count NUMBER;
759 l_msg_count NUMBER;
760 l_msg_data VARCHAR2(2000);
761
762 l_Task_Upd_Rec JTF_TASKS_PKG.Task_Upd_Rec := p_Task_Upd_Rec;
763
764 BEGIN
765
766 jtf_task_audits_pvt.create_task_audits (
767 p_api_version => 1,
768 p_init_msg_list => fnd_api.g_false,
769 p_commit => fnd_api.g_false,
770 p_object_version_number => l_Task_Upd_Rec.object_version_number,
771 p_task_id => l_Task_Upd_Rec.task_id,
772 p_new_billable_flag => l_Task_Upd_Rec.billable_flag,
773 p_new_device1_flag => l_Task_Upd_Rec.device1_flag,
774 p_new_device2_flag => l_Task_Upd_Rec.device2_flag,
775 p_new_device3_flag => l_Task_Upd_Rec.device3_flag,
776 p_new_holiday_flag => l_Task_Upd_Rec.holiday_flag,
777 p_new_laptop_flag => l_Task_Upd_Rec.laptop_flag,
778 p_new_milestone_flag => l_Task_Upd_Rec.milestone_flag,
779 p_new_multi_booked_flag => l_Task_Upd_Rec.multi_booked_flag,
780 p_new_not_flag => l_Task_Upd_Rec.notification_flag,
781 p_new_palm_flag => l_Task_Upd_Rec.palm_flag,
782 p_new_private_flag => l_Task_Upd_Rec.private_flag,
783 p_new_publish_flag => l_Task_Upd_Rec.publish_flag,
784 p_new_restrict_closure_flag => l_Task_Upd_Rec.restrict_closure_flag,
785 p_new_wince_flag => l_Task_Upd_Rec.wince_flag,
786 p_new_soft_bound_flag => l_Task_Upd_Rec.soft_bound_flag,
787 p_new_actual_effort => l_Task_Upd_Rec.actual_effort,
788 p_new_actual_effort_uom => l_Task_Upd_Rec.actual_effort_uom,
789 p_new_actual_end_date => l_Task_Upd_Rec.actual_end_date,
790 p_new_actual_start_date => l_Task_Upd_Rec.actual_start_date,
791 p_new_address_id => l_Task_Upd_Rec.address_id,
792 p_new_assigned_by_id => l_Task_Upd_Rec.assigned_by_id,
793 p_new_bound_mode_code => l_Task_Upd_Rec.bound_mode_code,
794 p_new_costs => l_Task_Upd_Rec.costs,
795 p_new_currency_code => l_Task_Upd_Rec.currency_code,
796 p_new_customer_id => l_Task_Upd_Rec.customer_id,
797 p_new_cust_account_id => l_Task_Upd_Rec.cust_account_id,
798 p_new_duration => l_Task_Upd_Rec.duration,
799 p_new_duration_uom => l_Task_Upd_Rec.duration_uom,
800 p_new_not_period => l_Task_Upd_Rec.notification_period,
801 p_new_not_period_uom => l_Task_Upd_Rec.notification_period_uom,
802 p_new_owner_id => l_Task_Upd_Rec.owner_id,
803 p_new_owner_type_code => l_Task_Upd_Rec.owner_type_code,
804 p_new_parent_task_id => l_Task_Upd_Rec.parent_task_id,
805 p_new_per_complete => l_Task_Upd_Rec.percentage_complete,
806 p_new_planned_effort => l_Task_Upd_Rec.planned_effort,
807 p_new_planned_effort_uom => l_Task_Upd_Rec.planned_effort_uom,
808 p_new_planned_end_date => l_Task_Upd_Rec.planned_end_date,
809 p_new_planned_start_date => l_Task_Upd_Rec.planned_start_date,
810 p_new_reason_code => l_Task_Upd_Rec.reason_code,
814 p_new_src_obj_id => l_Task_Upd_Rec.source_object_id,
811 p_new_recurrence_rule_id => l_Task_Upd_Rec.recurrence_rule_id,
812 p_new_sched_end_date => l_Task_Upd_Rec.scheduled_end_date,
813 p_new_sched_start_date => l_Task_Upd_Rec.scheduled_start_date,
815 p_new_src_obj_name => l_Task_Upd_Rec.source_object_name,
816 p_new_src_obj_type_code => l_Task_Upd_Rec.source_object_type_code,
817 p_new_task_priority_id => l_Task_Upd_Rec.task_priority_id,
818 p_new_task_status_id => l_Task_Upd_Rec.task_status_id,
819 p_new_task_type_id => l_Task_Upd_Rec.task_type_id,
820 p_new_timezone_id => l_Task_Upd_Rec.timezone_id,
821 p_new_workflow_process_id => l_Task_Upd_Rec.workflow_process_id,
822 p_not_chan_flag => NULL,
823 p_new_description => l_Task_Upd_Rec.description,
824 p_new_task_name => l_Task_Upd_Rec.task_name,
825 p_new_escalation_level => l_Task_Upd_Rec.escalation_level,
826 p_new_owner_territory_id => l_Task_Upd_Rec.owner_territory_id,
827 p_new_date_selected => l_Task_Upd_Rec.date_selected,
828 p_new_location_id => l_Task_Upd_Rec.location_id,
829 -- p_new_task_conf_status => l_Task_Upd_Rec.task_confirmation_status,
830 -- p_new_task_conf_counter => l_Task_Upd_Rec.task_confirmation_counter,
831 -- p_new_task_split_flag => l_Task_Upd_Rec.task_split_flag,
832 x_return_status => x_return_status,
833 x_msg_count => x_msg_count,
834 x_msg_data => x_msg_data,
835 x_task_audit_id => l_task_audit_id
836 );
837
838 p_task_audit_id := l_task_audit_id;
839
840 UPDATE jtf_tasks_b
841 SET attribute4 = l_Task_Upd_Rec.attribute4,
842 object_version_number = l_Task_Upd_Rec.object_version_number,
843 attribute5 = l_Task_Upd_Rec.attribute5,
844 attribute6 = l_Task_Upd_Rec.attribute6,
845 attribute7 = l_Task_Upd_Rec.attribute7,
846 attribute8 = l_Task_Upd_Rec.attribute8,
847 attribute9 = l_Task_Upd_Rec.attribute9,
848 attribute10 = l_Task_Upd_Rec.attribute10,
849 attribute11 = l_Task_Upd_Rec.attribute11,
850 attribute12 = l_Task_Upd_Rec.attribute12,
851 attribute13 = l_Task_Upd_Rec.attribute13,
852 attribute14 = l_Task_Upd_Rec.attribute14,
853 attribute15 = l_Task_Upd_Rec.attribute15,
854 attribute_category = l_Task_Upd_Rec.attribute_category,
855 task_number = l_Task_Upd_Rec.task_number,
856 task_type_id = l_Task_Upd_Rec.task_type_id,
857 task_status_id = l_Task_Upd_Rec.task_status_id,
858 task_priority_id = l_Task_Upd_Rec.task_priority_id,
859 owner_id = l_Task_Upd_Rec.owner_id,
860 owner_type_code = l_Task_Upd_Rec.owner_type_code,
861 assigned_by_id = l_Task_Upd_Rec.assigned_by_id,
862 cust_account_id = l_Task_Upd_Rec.cust_account_id,
863 customer_id = l_Task_Upd_Rec.customer_id,
864 address_id = l_Task_Upd_Rec.address_id,
865 planned_start_date = l_Task_Upd_Rec.planned_start_date,
866 palm_flag = l_Task_Upd_Rec.palm_flag,
867 wince_flag = l_Task_Upd_Rec.wince_flag,
868 laptop_flag = l_Task_Upd_Rec.laptop_flag,
869 device1_flag = l_Task_Upd_Rec.device1_flag,
870 device2_flag = l_Task_Upd_Rec.device2_flag,
871 device3_flag = l_Task_Upd_Rec.device3_flag,
872 costs = l_Task_Upd_Rec.costs,
873 currency_code = l_Task_Upd_Rec.currency_code,
874 attribute1 = l_Task_Upd_Rec.attribute1,
875 attribute2 = l_Task_Upd_Rec.attribute2,
876 attribute3 = l_Task_Upd_Rec.attribute3,
877 notification_period = l_Task_Upd_Rec.notification_period,
878 notification_period_uom = l_Task_Upd_Rec.notification_period_uom,
879 parent_task_id = l_Task_Upd_Rec.parent_task_id,
880 recurrence_rule_id = l_Task_Upd_Rec.recurrence_rule_id,
881 alarm_start = l_Task_Upd_Rec.alarm_start,
882 alarm_start_uom = l_Task_Upd_Rec.alarm_start_uom,
883 alarm_on = l_Task_Upd_Rec.alarm_on,
884 alarm_count = l_Task_Upd_Rec.alarm_count,
885 alarm_fired_count = l_Task_Upd_Rec.alarm_fired_count,
886 alarm_interval = l_Task_Upd_Rec.alarm_interval,
887 alarm_interval_uom = l_Task_Upd_Rec.alarm_interval_uom,
888 deleted_flag = l_Task_Upd_Rec.deleted_flag,
889 actual_start_date = l_Task_Upd_Rec.actual_start_date,
890 actual_end_date = l_Task_Upd_Rec.actual_end_date,
891 source_object_type_code = l_Task_Upd_Rec.source_object_type_code,
892 timezone_id = l_Task_Upd_Rec.timezone_id,
893 source_object_id = l_Task_Upd_Rec.source_object_id,
894 source_object_name = l_Task_Upd_Rec.source_object_name,
895 duration = l_Task_Upd_Rec.duration,
896 duration_uom = l_Task_Upd_Rec.duration_uom,
897 planned_effort = l_Task_Upd_Rec.planned_effort,
898 planned_effort_uom = l_Task_Upd_Rec.planned_effort_uom,
899 actual_effort = l_Task_Upd_Rec.actual_effort,
900 actual_effort_uom = l_Task_Upd_Rec.actual_effort_uom,
901 percentage_complete = l_Task_Upd_Rec.percentage_complete,
902 reason_code = l_Task_Upd_Rec.reason_code,
906 multi_booked_flag = l_Task_Upd_Rec.multi_booked_flag,
903 private_flag = l_Task_Upd_Rec.private_flag,
904 publish_flag = l_Task_Upd_Rec.publish_flag,
905 restrict_closure_flag = l_Task_Upd_Rec.restrict_closure_flag,
907 milestone_flag = l_Task_Upd_Rec.milestone_flag,
908 holiday_flag = l_Task_Upd_Rec.holiday_flag,
909 billable_flag = l_Task_Upd_Rec.billable_flag,
910 bound_mode_code = l_Task_Upd_Rec.bound_mode_code,
911 soft_bound_flag = l_Task_Upd_Rec.soft_bound_flag,
912 workflow_process_id = l_Task_Upd_Rec.workflow_process_id,
913 notification_flag = l_Task_Upd_Rec.notification_flag,
914 planned_end_date = l_Task_Upd_Rec.planned_end_date,
915 scheduled_start_date = l_Task_Upd_Rec.scheduled_start_date,
916 scheduled_end_date = l_Task_Upd_Rec.scheduled_end_date,
917 last_update_date = l_Task_Upd_Rec.last_update_date,
918 last_updated_by = l_Task_Upd_Rec.last_updated_by,
919 last_update_login = l_Task_Upd_Rec.last_update_login,
920 owner_territory_id = l_Task_Upd_Rec.owner_territory_id,
921 escalation_level = l_Task_Upd_Rec.escalation_level,
922 calendar_start_date = decode(calendar_start_date, jtf_task_utl.g_miss_date, calendar_start_date, l_Task_Upd_Rec.calendar_start_date),
923 calendar_end_date = decode(calendar_end_date, jtf_task_utl.g_miss_date, calendar_end_date, l_Task_Upd_Rec.calendar_end_date),
924 date_selected = decode(date_selected, jtf_task_utl.g_miss_char, date_selected, l_Task_Upd_Rec.date_selected),
925 object_changed_date = SYSDATE,
926 open_flag = l_Task_Upd_Rec.open_flag,
927 task_confirmation_status = l_Task_Upd_Rec.task_confirmation_status,
928 task_confirmation_counter = l_Task_Upd_Rec.task_confirmation_counter,
929 task_split_flag = l_Task_Upd_Rec.task_split_flag,
930 child_position = l_Task_Upd_Rec.child_position,
931 child_sequence_num = l_Task_Upd_Rec.child_sequence_num,
932 --task_audit_id = p_task_audit_id
933 location_id = l_Task_Upd_Rec.location_id
934 WHERE task_id = l_Task_Upd_Rec.task_id;
935
936 IF (SQL%NOTFOUND)
937 THEN
938 RAISE NO_DATA_FOUND;
939 END IF;
940
941 UPDATE jtf_tasks_tl
942 SET task_name = l_Task_Upd_Rec.task_name,
943 description = l_Task_Upd_Rec.description,
944 last_update_date = l_Task_Upd_Rec.last_update_date,
945 last_updated_by = l_Task_Upd_Rec.last_updated_by,
946 last_update_login = l_Task_Upd_Rec.last_update_login,
947 source_lang = USERENV ('LANG')
948 WHERE task_id = l_Task_Upd_Rec.task_id
949 AND USERENV ('LANG') IN (language, source_lang);
950
951 IF (SQL%NOTFOUND)
952 THEN
953 RAISE NO_DATA_FOUND;
954 END IF;
955
956 END update_row;
957
958 --End BES Changes
959
960 PROCEDURE delete_row (x_task_id IN NUMBER)
961 IS
962
963 x_return_status varchar2(1);
964
965 BEGIN
966 null;
967 END delete_row;
968
969 PROCEDURE add_language
970 IS
971 BEGIN
972
973 /* Solving Perf. Bug 3723927*/
974 /* The following delete and update statements are commented out */
975 /* as a quick workaround to fix the time-consuming table handler issue */
976 /*
977
978 /* DELETE
979 FROM jtf_tasks_tl t
980 WHERE NOT EXISTS (SELECT NULL
981 FROM jtf_tasks_b b
982 WHERE b.task_id = t.task_id);
983 UPDATE jtf_tasks_tl t
984 SET (task_name, description) = ( SELECT b.task_name, b.description
985 FROM jtf_tasks_tl b
986 WHERE b.task_id = t.task_id
987 AND b.language = t.source_lang)
988 WHERE (t.task_id, t.language) IN (SELECT subt.task_id, subt.language
989 FROM jtf_tasks_tl subb, jtf_tasks_tl subt
990 WHERE subb.task_id = subt.task_id
991 AND subb.language = subt.source_lang
992 AND ( subb.task_name <> subt.task_name
993 OR subb.description <> subt.description
994 OR ( subb.description IS NULL
995 AND subt.description IS NOT NULL)
996 OR ( subb.description IS NOT NULL
997 AND subt.description IS NULL)));
998
999 INSERT INTO jtf_tasks_tl
1000 (task_name,
1001 description,
1002 created_by,
1003 creation_date,
1004 last_updated_by,
1005 last_update_date,
1006 last_update_login,
1007 task_id,
1008 language,
1009 source_lang
1010 )
1011 SELECT b.task_name,
1012 b.description,
1013 b.created_by,
1014 b.creation_date,
1015 b.last_updated_by,
1016 b.last_update_date,
1017 b.last_update_login,
1018 b.task_id,
1019 l.language_code,
1020 b.source_lang
1021 FROM jtf_tasks_tl b, fnd_languages l
1022 WHERE l.installed_flag IN ('I', 'B')
1023 AND b.language = USERENV ('LANG')
1024 AND NOT EXISTS (SELECT NULL
1025 FROM jtf_tasks_tl t
1026 WHERE t.task_id = b.task_id
1027 AND t.language = l.language_code);
1028
1029 *** Additional fix for the same bug is to add parallel hints to insert.
1030 *** Replaced the original query with one below (mmmarovic on 3/7/05)
1031 */
1032 INSERT /*+ append parallel(tl) */ INTO jtf_tasks_tl tl
1033 (task_name, description, created_by, creation_date,
1034 last_updated_by, last_update_date, last_update_login,
1035 task_id, language, source_lang)
1036 SELECT /*+ parallel(v) parallel(t) use_nl(t) */ v.*
1037 FROM ( SELECT /*+ no_merge ordered parallel(b) */
1038 b.task_name, b.description, b.created_by, b.creation_date,
1039 b.last_updated_by, b.last_update_date, b.last_update_login,
1040 b.task_id, l.language_code, b.source_lang
1041 FROM fnd_languages l, jtf_tasks_tl b
1042 WHERE l.installed_flag IN ('I', 'B')
1043 AND b.language = USERENV ('LANG')
1044 ) v, jtf_tasks_tl t
1045 WHERE t.task_id(+) = v.task_id
1046 AND t.language(+) = v.language_code
1047 AND t.task_id IS NULL;
1048
1049 END add_language;
1050
1051 procedure translate_row(
1052 x_task_id in number,
1053 x_task_name in varchar2,
1054 x_description in varchar2,
1055 x_owner in varchar2 )
1056 as
1057 begin
1058 update jtf_tasks_tl set
1059 task_name = nvl(x_task_name,task_name),
1060 description = nvl(x_description,description),
1061 LAST_UPDATE_DATE = sysdate,
1062 LAST_UPDATEd_by = decode(x_owner,'SEED',1,0),
1063 LAST_UPDATE_LOGIN = 0,
1064 SOURCE_LANG = userenv('LANG')
1065 where task_id = X_task_id
1066 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1067
1068 if (sql%notfound) then
1069 raise no_data_found;
1070 end if;
1071
1072 end;
1073
1074 END jtf_tasks_pkg;