[Home] [Help]
PACKAGE BODY: APPS.JTF_TASK_STATUSES_PKG
Source
1 PACKAGE body JTF_TASK_STATUSES_PKG as
2 /* $Header: jtftkstb.pls 120.3.12020000.2 2013/02/22 08:37:54 srguntur ship $ */
3 procedure INSERT_ROW(
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_TASK_STATUS_ID in NUMBER,
6 X_CLOSED_FLAG in VARCHAR2,
7 X_START_DATE_ACTIVE in DATE,
8 X_END_DATE_ACTIVE in DATE,
9 X_SEEDED_FLAG in VARCHAR2,
10 X_ASSIGNED_FLAG in VARCHAR2,
11 X_WORKING_FLAG in VARCHAR2,
12 X_APPROVED_FLAG in VARCHAR2,
13 X_COMPLETED_FLAG in VARCHAR2,
14 X_CANCELLED_FLAG in VARCHAR2,
15 X_REJECTED_FLAG in VARCHAR2,
16 X_ACCEPTED_FLAG in VARCHAR2,
17 X_ON_HOLD_FLAG in VARCHAR2,
18 X_SCHEDULABLE_FLAG in VARCHAR2,
19 X_DELETE_ALLOWED_FLAG in VARCHAR2,
20 X_ATTRIBUTE1 in VARCHAR2,
21 X_ATTRIBUTE2 in VARCHAR2,
22 X_ATTRIBUTE3 in VARCHAR2,
23 X_ATTRIBUTE4 in VARCHAR2,
24 X_ATTRIBUTE5 in VARCHAR2,
25 X_ATTRIBUTE6 in VARCHAR2,
26 X_ATTRIBUTE7 in VARCHAR2,
27 X_ATTRIBUTE8 in VARCHAR2,
28 X_ATTRIBUTE9 in VARCHAR2,
29 X_ATTRIBUTE10 in VARCHAR2,
30 X_ATTRIBUTE11 in VARCHAR2,
31 X_ATTRIBUTE12 in VARCHAR2,
32 X_ATTRIBUTE13 in VARCHAR2,
33 X_ATTRIBUTE14 in VARCHAR2,
34 X_ATTRIBUTE15 in VARCHAR2,
35 X_ATTRIBUTE_CATEGORY in VARCHAR2,
36 X_NAME in VARCHAR2,
37 X_DESCRIPTION in VARCHAR2,
38 X_CREATION_DATE in DATE,
39 X_CREATED_BY in NUMBER,
40 X_LAST_UPDATE_DATE in DATE,
41 X_LAST_UPDATED_BY in NUMBER,
42 X_LAST_UPDATE_LOGIN in NUMBER,
43 -- X_UPDATE in VARCHAR2,
44 X_USAGE in VARCHAR2,
45 X_TASK_STATUS_FLAG in VARCHAR2,
46 X_ASSIGNMENT_STATUS_FLAG in VARCHAR2,
47 X_START_DATE_TYPE IN VARCHAR2,
48 X_END_DATE_TYPE IN VARCHAR2,
49 X_TRAVEL_FLAG IN VARCHAR2 DEFAULT NULL,
50 X_PLANNED_FLAG IN VARCHAR2 DEFAULT NULL,
51 X_ENFORCE_VALIDATION_FLAG IN VARCHAR2 DEFAULT NULL,
52 X_VALIDATION_START_DATE IN DATE DEFAULT NULL,
53 X_VALIDATION_END_DATE IN DATE DEFAULT NULL,
54 X_RESTRICT_UPDATES_FLAG in VARCHAR2 DEFAULT NULL
55 ) is
56 cursor C is select ROWID from JTF_TASK_STATUSES_B
57 where TASK_STATUS_ID = X_TASK_STATUS_ID
58 ;
59 begin
60 insert into JTF_TASK_STATUSES_B (
61 TASK_STATUS_ID,
62 CLOSED_FLAG,
63 START_DATE_ACTIVE,
64 END_DATE_ACTIVE,
65 SEEDED_FLAG,
66 ASSIGNED_FLAG,
67 WORKING_FLAG,
68 APPROVED_FLAG,
69 COMPLETED_FLAG,
70 CANCELLED_FLAG,
71 REJECTED_FLAG,
72 ACCEPTED_FLAG,
73 ON_HOLD_FLAG,
74 SCHEDULABLE_FLAG,
75 DELETE_ALLOWED_FLAG,
76 ATTRIBUTE1,
77 ATTRIBUTE2,
78 ATTRIBUTE3,
79 ATTRIBUTE4,
80 ATTRIBUTE5,
81 ATTRIBUTE6,
82 ATTRIBUTE7,
83 ATTRIBUTE8,
84 ATTRIBUTE9,
85 ATTRIBUTE10,
86 ATTRIBUTE11,
87 ATTRIBUTE12,
88 ATTRIBUTE13,
89 ATTRIBUTE14,
90 ATTRIBUTE15,
91 ATTRIBUTE_CATEGORY,
92 CREATION_DATE,
93 CREATED_BY,
94 LAST_UPDATE_DATE,
95 LAST_UPDATED_BY,
96 LAST_UPDATE_LOGIN,
100 ASSIGNMENT_STATUS_FLAG,
97 OBJECT_VERSION_NUMBER,
98 USAGE,
99 TASK_STATUS_FLAG,
101 START_DATE_TYPE,
102 END_DATE_TYPE,
103 TRAVEL_FLAG,
104 PLANNED_FLAG,
105 ENFORCE_VALIDATION_FLAG,
106 VALIDATION_START_DATE,
107 VALIDATION_END_DATE,
108 RESTRICT_UPDATES_FLAG
109 ) values (
110 X_TASK_STATUS_ID,
111 X_CLOSED_FLAG,
112 X_START_DATE_ACTIVE,
113 X_END_DATE_ACTIVE,
114 X_SEEDED_FLAG,
115 X_ASSIGNED_FLAG,
116 X_WORKING_FLAG,
117 X_APPROVED_FLAG,
118 X_COMPLETED_FLAG,
119 X_CANCELLED_FLAG,
120 X_REJECTED_FLAG,
121 X_ACCEPTED_FLAG,
122 X_ON_HOLD_FLAG,
123 X_SCHEDULABLE_FLAG,
124 X_DELETE_ALLOWED_FLAG,
125 X_ATTRIBUTE1,
126 X_ATTRIBUTE2,
127 X_ATTRIBUTE3,
128 X_ATTRIBUTE4,
129 X_ATTRIBUTE5,
130 X_ATTRIBUTE6,
131 X_ATTRIBUTE7,
132 X_ATTRIBUTE8,
133 X_ATTRIBUTE9,
134 X_ATTRIBUTE10,
135 X_ATTRIBUTE11,
136 X_ATTRIBUTE12,
137 X_ATTRIBUTE13,
138 X_ATTRIBUTE14,
139 X_ATTRIBUTE15,
140 X_ATTRIBUTE_CATEGORY,
141 X_CREATION_DATE,
142 X_CREATED_BY,
143 X_LAST_UPDATE_DATE,
144 X_LAST_UPDATED_BY,
145 X_LAST_UPDATE_LOGIN,
146 1,
147 X_USAGE,
148 X_TASK_STATUS_FLAG,
149 X_ASSIGNMENT_STATUS_FLAG,
150 X_START_DATE_TYPE,
151 X_END_DATE_TYPE,
152 X_TRAVEL_FLAG,
153 X_PLANNED_FLAG,
154 X_ENFORCE_VALIDATION_FLAG,
155 X_VALIDATION_START_DATE,
156 X_VALIDATION_END_DATE,
157 X_RESTRICT_UPDATES_FLAG
158 );
159
160 insert into JTF_TASK_STATUSES_TL (
161 TASK_STATUS_ID,
162 NAME,
163 DESCRIPTION,
164 CREATED_BY,
165 CREATION_DATE,
166 LAST_UPDATED_BY,
167 LAST_UPDATE_DATE,
168 LAST_UPDATE_LOGIN,
169 LANGUAGE,
170 SOURCE_LANG
171 ) select
172 X_TASK_STATUS_ID,
173 X_NAME,
174 X_DESCRIPTION,
175 X_CREATED_BY,
176 X_CREATION_DATE,
177 X_LAST_UPDATED_BY,
178 X_LAST_UPDATE_DATE,
179 X_LAST_UPDATE_LOGIN,
180 L.LANGUAGE_CODE,
181 userenv('LANG')
182 from FND_LANGUAGES L
183 where L.INSTALLED_FLAG in ('I', 'B')
184 and not exists
185 (select NULL
186 from JTF_TASK_STATUSES_TL T
187 where T.TASK_STATUS_ID = X_TASK_STATUS_ID
188 and T.LANGUAGE = L.LANGUAGE_CODE);
189
190 open c;
191 fetch c into X_ROWID;
192 if (c%notfound) then
193 close c;
194 raise no_data_found;
195 end if;
196 close c;
197
198 end INSERT_ROW;
199
200 procedure LOCK_ROW (
201 X_TASK_STATUS_ID in NUMBER,
202 X_OBJECT_VERSION_NUMBER in NUMBER
203 ) is
204 cursor c is select
205 OBJECT_VERSION_NUMBER
206 from JTF_TASK_ALL_STATUSES_VL
207 where TASK_STATUS_ID = X_TASK_STATUS_ID
208 and OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER
209 for update of TASK_STATUS_ID nowait;
210 recinfo c%rowtype;
211
212
213 begin
214 open c;
215 fetch c into recinfo;
216 if (c%notfound) then
217 close c;
218 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
219 app_exception.raise_exception;
220 end if;
221 close c;
222
223 if (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
224 then
225 null;
226 else
227 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
228 app_exception.raise_exception;
229 end if;
230
231 end LOCK_ROW;
232
233 procedure UPDATE_ROW (
234 X_TASK_STATUS_ID in NUMBER,
235 X_OBJECT_VERSION_NUMBER in NUMBER,
236 X_CLOSED_FLAG in VARCHAR2,
237 X_START_DATE_ACTIVE in DATE,
238 X_END_DATE_ACTIVE in DATE,
239 X_SEEDED_FLAG in VARCHAR2,
240 X_ASSIGNED_FLAG in VARCHAR2,
241 X_WORKING_FLAG in VARCHAR2,
242 X_APPROVED_FLAG in VARCHAR2,
243 X_COMPLETED_FLAG in VARCHAR2,
244 X_CANCELLED_FLAG in VARCHAR2,
245 X_REJECTED_FLAG in VARCHAR2,
246 X_ACCEPTED_FLAG in VARCHAR2,
247 X_ON_HOLD_FLAG in VARCHAR2,
248 X_SCHEDULABLE_FLAG in VARCHAR2,
249 X_DELETE_ALLOWED_FLAG in VARCHAR2,
250 X_ATTRIBUTE1 in VARCHAR2,
251 X_ATTRIBUTE2 in VARCHAR2,
252 X_ATTRIBUTE3 in VARCHAR2,
253 X_ATTRIBUTE4 in VARCHAR2,
254 X_ATTRIBUTE5 in VARCHAR2,
255 X_ATTRIBUTE6 in VARCHAR2,
256 X_ATTRIBUTE7 in VARCHAR2,
257 X_ATTRIBUTE8 in VARCHAR2,
258 X_ATTRIBUTE9 in VARCHAR2,
259 X_ATTRIBUTE10 in VARCHAR2,
260 X_ATTRIBUTE11 in VARCHAR2,
261 X_ATTRIBUTE12 in VARCHAR2,
262 X_ATTRIBUTE13 in VARCHAR2,
263 X_ATTRIBUTE14 in VARCHAR2,
264 X_ATTRIBUTE15 in VARCHAR2,
265 X_ATTRIBUTE_CATEGORY in VARCHAR2,
266 X_NAME in VARCHAR2,
267 X_DESCRIPTION in VARCHAR2,
268 X_LAST_UPDATE_DATE in DATE,
269 X_LAST_UPDATED_BY in NUMBER,
270 X_LAST_UPDATE_LOGIN in NUMBER,
271 X_USAGE in varchar2,
272 X_TASK_STATUS_FLAG in VARCHAR2,
273 X_ASSIGNMENT_STATUS_FLAG in VARCHAR2,
274 X_START_DATE_TYPE IN VARCHAR2 DEFAULT NULL,
275 X_END_DATE_TYPE IN VARCHAR2 DEFAULT NULL,
276 X_TRAVEL_FLAG IN VARCHAR2 DEFAULT NULL,
277 X_PLANNED_FLAG IN VARCHAR2 DEFAULT NULL ,
278 X_ENFORCE_VALIDATION_FLAG IN VARCHAR2 DEFAULT NULL ,
279 X_VALIDATION_START_DATE IN DATE DEFAULT NULL ,
280 X_VALIDATION_END_DATE IN DATE DEFAULT NULL,
284 update JTF_TASK_STATUSES_B set
281 X_RESTRICT_UPDATES_FLAG in VARCHAR2 DEFAULT NULL
282 ) is
283 begin
285 CLOSED_FLAG = X_CLOSED_FLAG,
286 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER + 1,
287 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
288 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
289 SEEDED_FLAG = X_SEEDED_FLAG,
290 ASSIGNED_FLAG = X_ASSIGNED_FLAG,
291 WORKING_FLAG = X_WORKING_FLAG,
292 APPROVED_FLAG = X_APPROVED_FLAG,
293 COMPLETED_FLAG = X_COMPLETED_FLAG,
294 CANCELLED_FLAG = X_CANCELLED_FLAG,
295 REJECTED_FLAG = X_REJECTED_FLAG,
296 ACCEPTED_FLAG = X_ACCEPTED_FLAG,
297 ON_HOLD_FLAG = X_ON_HOLD_FLAG,
298 SCHEDULABLE_FLAG = X_SCHEDULABLE_FLAG,
299 DELETE_ALLOWED_FLAG = X_DELETE_ALLOWED_FLAG,
300 ATTRIBUTE1 = X_ATTRIBUTE1,
301 ATTRIBUTE2 = X_ATTRIBUTE2,
302 ATTRIBUTE3 = X_ATTRIBUTE3,
303 ATTRIBUTE4 = X_ATTRIBUTE4,
304 ATTRIBUTE5 = X_ATTRIBUTE5,
305 ATTRIBUTE6 = X_ATTRIBUTE6,
306 ATTRIBUTE7 = X_ATTRIBUTE7,
307 ATTRIBUTE8 = X_ATTRIBUTE8,
308 ATTRIBUTE9 = X_ATTRIBUTE9,
309 ATTRIBUTE10 = X_ATTRIBUTE10,
310 ATTRIBUTE11 = X_ATTRIBUTE11,
311 ATTRIBUTE12 = X_ATTRIBUTE12,
312 ATTRIBUTE13 = X_ATTRIBUTE13,
313 ATTRIBUTE14 = X_ATTRIBUTE14,
314 ATTRIBUTE15 = X_ATTRIBUTE15,
315 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
316 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
317 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
318 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
319 USAGE = X_USAGE,
320 TASK_STATUS_FLAG = X_TASK_STATUS_FLAG,
321 ASSIGNMENT_STATUS_FLAG = X_ASSIGNMENT_STATUS_FLAG,
322 START_DATE_TYPE = X_START_DATE_TYPE,
323 END_DATE_TYPE = X_END_DATE_TYPE,
324 TRAVEL_FLAG = X_TRAVEL_FLAG,
325 PLANNED_FLAG = X_PLANNED_FLAG,
326 ENFORCE_VALIDATION_FLAG = X_ENFORCE_VALIDATION_FLAG,
327 VALIDATION_START_DATE = X_VALIDATION_START_DATE,
328 VALIDATION_END_DATE = X_VALIDATION_END_DATE,
329 RESTRICT_UPDATES_FLAG = X_RESTRICT_UPDATES_FLAG
330 where TASK_STATUS_ID = X_TASK_STATUS_ID ;
331
332 if (sql%notfound) then
333 raise no_data_found;
334 end if;
335
336 -- Added Index Hint on 30/05/2006 for bug# 5213367
337 update /*+ INDEX(a JTF_TASK_STATUSES_TL_U1) */ JTF_TASK_STATUSES_TL a set
338 a.NAME = X_NAME,
339 a.DESCRIPTION = X_DESCRIPTION,
340 a.LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
341 a.LAST_UPDATED_BY = X_LAST_UPDATED_BY,
342 a.LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
343 a.SOURCE_LANG = userenv('LANG')
344 where a.TASK_STATUS_ID = X_TASK_STATUS_ID
345 and userenv('LANG') in (a.LANGUAGE, a.SOURCE_LANG);
346
347 if (sql%notfound) then
348 raise no_data_found;
349 end if;
350 end UPDATE_ROW;
351
352 procedure DELETE_ROW (
353 X_TASK_STATUS_ID in NUMBER
354 ) is
355 begin
356 delete from JTF_TASK_STATUSES_TL
357 where TASK_STATUS_ID = X_TASK_STATUS_ID;
358
359 if (sql%notfound) then
360 raise no_data_found;
361 end if;
362
363 delete from JTF_TASK_STATUSES_B
364 where TASK_STATUS_ID = X_TASK_STATUS_ID ;
365
366 if (sql%notfound) then
367 raise no_data_found;
368 end if;
369 end DELETE_ROW;
370
371 procedure ADD_LANGUAGE
372 is
373 begin
374
375 /* Solving Perf. Bug 3723927 */
376 /* The following delete and update statements are commented out */
377 /* as a quick workaround to fix the time-consuming table handler issue */
378
379 /* delete from JTF_TASK_STATUSES_TL T
380 where not exists
381 (select NULL
382 from JTF_TASK_STATUSES_B B
383 where B.TASK_STATUS_ID = T.TASK_STATUS_ID
384 );
385
386 update JTF_TASK_STATUSES_TL T set (
387 NAME,
388 DESCRIPTION
389 ) = (select
390 B.NAME,
391 B.DESCRIPTION
392 from JTF_TASK_STATUSES_TL B
393 where B.TASK_STATUS_ID = T.TASK_STATUS_ID
394 and B.LANGUAGE = T.SOURCE_LANG)
395 where (
396 T.TASK_STATUS_ID,
397 T.LANGUAGE
398 ) in (select
399 SUBT.TASK_STATUS_ID,
400 SUBT.LANGUAGE
401 from JTF_TASK_STATUSES_TL SUBB, JTF_TASK_STATUSES_TL SUBT
402 where SUBB.TASK_STATUS_ID = SUBT.TASK_STATUS_ID
403 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
404 and (SUBB.NAME <> SUBT.NAME
405 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
406 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
407 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
408 )); */
409
410 insert into JTF_TASK_STATUSES_TL (
411 TASK_STATUS_ID,
412 NAME,
413 DESCRIPTION,
414 CREATED_BY,
415 CREATION_DATE,
416 LAST_UPDATED_BY,
417 LAST_UPDATE_DATE,
418 LAST_UPDATE_LOGIN,
419 LANGUAGE,
420 SOURCE_LANG
421 ) select /*+ INDEX(b JTF_TASK_STATUSES_TL_U1) INDEX (l FND_LANGUAGES_N1) */ -- Added Index Hint on 30/05/2006 for bug# 5213367
422 B.TASK_STATUS_ID,
423 B.NAME,
424 B.DESCRIPTION,
425 B.CREATED_BY,
426 B.CREATION_DATE,
427 B.LAST_UPDATED_BY,
428 B.LAST_UPDATE_DATE,
429 B.LAST_UPDATE_LOGIN,
430 L.LANGUAGE_CODE,
431 B.SOURCE_LANG
432 from JTF_TASK_STATUSES_TL B, FND_LANGUAGES L
433 where L.INSTALLED_FLAG in ('I', 'B')
434 and B.LANGUAGE = userenv('LANG')
435 and not exists
436 (select NULL
437 from JTF_TASK_STATUSES_TL T
441
438 where T.TASK_STATUS_ID = B.TASK_STATUS_ID
439 and T.LANGUAGE = L.LANGUAGE_CODE);
440 end ADD_LANGUAGE;
442 procedure TRANSLATE_ROW(
443 X_TASK_STATUS_ID in varchar2,
444 X_NAME in varchar2,
445 X_DESCRIPTION in varchar2,
446 X_OWNER in varchar2) is
447 l_user_id NUMBER := 0;
448 BEGIN
449 IF x_owner = 'SEED'
450 THEN
451 l_user_id := 1;
452 END IF;
453
454 -- Added Index Hint on 30/05/2006 for bug# 5213367
455 update /*+ INDEX(a JTF_TASK_STATUSES_TL_U1) */ jtf_task_statuses_tl a set
456 a.NAME= nvl(X_NAME, a.name) ,
457 a.DESCRIPTION= nvl(X_DESCRIPTION, a.description),
458 a.LAST_UPDATE_DATE = sysdate,
459 a.LAST_UPDATE_LOGIN = 0,
460 a.SOURCE_LANG = userenv('LANG'),
461 a.LAST_UPDATED_BY = l_user_id
462 where a.task_status_id = X_task_status_id
463 and userenv('LANG') in (a.LANGUAGE, a.SOURCE_LANG);
464
465 if (sql%notfound) then
466 raise no_data_found;
467 end if;
468 end TRANSLATE_ROW;
469
470 procedure LOAD_ROW (
471 X_TASK_STATUS_ID in NUMBER,
472 X_CLOSED_FLAG in VARCHAR2,
473 X_START_DATE_ACTIVE in DATE,
474 X_END_DATE_ACTIVE in DATE,
475 X_SEEDED_FLAG in VARCHAR2,
476 X_ASSIGNED_FLAG in VARCHAR2,
477 X_WORKING_FLAG in VARCHAR2,
478 X_APPROVED_FLAG in VARCHAR2,
479 X_COMPLETED_FLAG in VARCHAR2,
480 X_CANCELLED_FLAG in VARCHAR2,
481 X_REJECTED_FLAG in VARCHAR2,
482 X_ACCEPTED_FLAG in VARCHAR2,
483 X_ON_HOLD_FLAG in VARCHAR2,
484 X_SCHEDULABLE_FLAG in VARCHAR2,
485 X_DELETE_ALLOWED_FLAG in VARCHAR2,
486 X_ATTRIBUTE1 in VARCHAR2,
487 X_ATTRIBUTE2 in VARCHAR2,
488 X_ATTRIBUTE3 in VARCHAR2,
489 X_ATTRIBUTE4 in VARCHAR2,
490 X_ATTRIBUTE5 in VARCHAR2,
491 X_ATTRIBUTE6 in VARCHAR2,
492 X_ATTRIBUTE7 in VARCHAR2,
493 X_ATTRIBUTE8 in VARCHAR2,
494 X_ATTRIBUTE9 in VARCHAR2,
495 X_ATTRIBUTE10 in VARCHAR2,
496 X_ATTRIBUTE11 in VARCHAR2,
497 X_ATTRIBUTE12 in VARCHAR2,
498 X_ATTRIBUTE13 in VARCHAR2,
499 X_ATTRIBUTE14 in VARCHAR2,
500 X_ATTRIBUTE15 in VARCHAR2,
501 X_ATTRIBUTE_CATEGORY in VARCHAR2,
502 X_NAME in VARCHAR2,
503 X_DESCRIPTION in VARCHAR2,
504 X_USAGE in VARCHAR2,
505 X_TASK_STATUS_FLAG in VARCHAR2,
506 X_ASSIGNMENT_STATUS_FLAG in VARCHAR2,
507 X_OWNER in VARCHAR2,
508 X_START_DATE_TYPE IN VARCHAR2,
509 X_END_DATE_TYPE IN VARCHAR2,
510 X_TRAVEL_FLAG IN VARCHAR2 DEFAULT NULL,
511 X_PLANNED_FLAG IN VARCHAR2 DEFAULT NULL
512 ) is
513
514 l_user_id NUMBER := 0;
515 l_task_status_id NUMBER;
516 l_rowid ROWID;
517 l_object_version_number NUMBER;
518 BEGIN
519
520 IF x_owner = 'SEED'
521 THEN
522 l_user_id := 1;
523 END IF;
524
525
526 SELECT task_status_id, object_version_number
527 INTO l_task_status_id, l_object_version_number
528 FROM jtf_task_all_statuses_vl
529 WHERE task_status_id = x_task_status_id;
530
531
532
533 update JTF_TASK_STATUSES_B set
534 CLOSED_FLAG = X_CLOSED_FLAG,
535 OBJECT_VERSION_NUMBER = l_object_version_number + 1,
536 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
537 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
538 SEEDED_FLAG = X_SEEDED_FLAG,
539 ASSIGNED_FLAG = X_ASSIGNED_FLAG,
540 WORKING_FLAG = X_WORKING_FLAG,
541 APPROVED_FLAG = X_APPROVED_FLAG,
542 COMPLETED_FLAG = X_COMPLETED_FLAG,
543 CANCELLED_FLAG = X_CANCELLED_FLAG,
544 REJECTED_FLAG = X_REJECTED_FLAG,
545 ACCEPTED_FLAG = X_ACCEPTED_FLAG,
546 ON_HOLD_FLAG = X_ON_HOLD_FLAG,
547 SCHEDULABLE_FLAG = X_SCHEDULABLE_FLAG,
548 DELETE_ALLOWED_FLAG = X_DELETE_ALLOWED_FLAG,
549 LAST_UPDATE_DATE = sysdate,
550 LAST_UPDATED_BY = l_user_id,
551 LAST_UPDATE_LOGIN = 0,
552 USAGE = X_USAGE,
553 TASK_STATUS_FLAG = X_TASK_STATUS_FLAG,
554 ASSIGNMENT_STATUS_FLAG = X_ASSIGNMENT_STATUS_FLAG,
555 START_DATE_TYPE = X_START_DATE_TYPE,
556 END_DATE_TYPE = X_END_DATE_TYPE,
557 TRAVEL_FLAG = X_TRAVEL_FLAG,
558 PLANNED_FLAG = X_PLANNED_FLAG
559 where TASK_STATUS_ID = l_task_status_id ;
560
561 -- Added Index Hint on 30/05/2006 for bug# 5213367
562 update /*+ INDEX(a JTF_TASK_STATUSES_TL_U1) */ JTF_TASK_STATUSES_TL a set
563 a.NAME = X_NAME,
564 a.DESCRIPTION = X_DESCRIPTION,
565 a.LAST_UPDATE_DATE = sysdate,
566 a.LAST_UPDATED_BY = l_user_id,
567 a.LAST_UPDATE_LOGIN = 0,
568 a.SOURCE_LANG = userenv('LANG')
569 where a.TASK_STATUS_ID = l_task_status_id
570 and userenv('LANG') in (a.LANGUAGE, a.SOURCE_LANG);
571
572
573
574 exception
575 when no_data_found then
576
577 jtf_task_statuses_pkg.insert_row (
578 x_rowid => l_rowid ,
579 x_task_status_id => x_task_status_id,
580 x_closed_flag => x_closed_flag,
581 x_start_date_active => x_start_date_active,
582 x_end_date_active => x_end_date_active,
583 x_seeded_flag => x_seeded_flag,
584 x_assigned_flag => x_assigned_flag,
585 x_working_flag => x_working_flag,
586 x_approved_flag => x_approved_flag,
587 x_completed_flag => x_completed_flag,
588 x_cancelled_flag => x_cancelled_flag,
589 x_rejected_flag => x_rejected_flag,
590 x_accepted_flag => x_accepted_flag,
591 x_on_hold_flag => x_on_hold_flag,
592 x_schedulable_flag => x_schedulable_flag,
593 x_delete_allowed_flag => x_delete_allowed_flag,
594 x_task_status_flag => x_task_status_flag,
595 x_assignment_status_flag => x_assignment_status_flag,
596 x_usage => x_usage,
597 x_attribute1 => x_attribute1,
598 x_attribute2 => x_attribute2,
599 x_attribute3 => x_attribute3,
600 x_attribute4 => x_attribute4,
601 x_attribute5 => x_attribute5,
602 x_attribute6 => x_attribute6,
603 x_attribute7 => x_attribute7,
604 x_attribute8 => x_attribute8,
605 x_attribute9 => x_attribute9,
606 x_attribute10 => x_attribute10,
607 x_attribute11 => x_attribute11,
608 x_attribute12 => x_attribute12,
609 x_attribute13 => x_attribute13,
610 x_attribute14 => x_attribute14,
611 x_attribute15 => x_attribute15,
612 x_attribute_category => x_attribute_category,
613 x_name => x_name,
614 x_description => x_description,
615 x_last_update_date => SYSDATE,
616 x_last_updated_by => l_user_id,
617 x_last_update_login => 0,
618 x_creation_date => SYSDATE,
619 x_created_by => l_user_id,
620 x_start_date_type => x_start_date_type,
621 x_end_date_type => x_end_date_type,
622 x_travel_flag => x_travel_flag,
623 x_planned_flag => x_planned_flag
624 );
625
626 end ;
627
628 end JTF_TASK_STATUSES_PKG;