[Home] [Help]
PACKAGE BODY: APPS.JTF_TASK_TYPES_PKG
Source
1 PACKAGE BODY jtf_task_types_pkg AS
2 /* $Header: jtftktyb.pls 120.2.12020000.3 2013/02/22 10:23:03 srguntur ship $ */
3 PROCEDURE insert_row (
4 x_rowid IN OUT NOCOPY VARCHAR2,
5 x_task_type_id IN NUMBER,
6 x_start_date_active IN DATE,
7 x_end_date_active IN DATE,
8 x_seeded_flag IN VARCHAR2,
9 x_workflow IN VARCHAR2,
10 x_planned_effort IN NUMBER,
11 x_planned_effort_uom IN VARCHAR2,
12 x_schedule_flag IN VARCHAR2,
13 x_notification_flag IN VARCHAR2,
14 x_private_flag IN VARCHAR2,
15 x_attribute1 IN VARCHAR2,
16 x_attribute2 IN VARCHAR2,
17 x_attribute3 IN VARCHAR2,
18 x_attribute4 IN VARCHAR2,
19 x_attribute5 IN VARCHAR2,
20 x_attribute6 IN VARCHAR2,
21 x_attribute7 IN VARCHAR2,
22 x_attribute8 IN VARCHAR2,
23 x_attribute9 IN VARCHAR2,
24 x_attribute10 IN VARCHAR2,
25 x_attribute11 IN VARCHAR2,
26 x_attribute12 IN VARCHAR2,
27 x_attribute13 IN VARCHAR2,
28 x_attribute14 IN VARCHAR2,
29 x_attribute15 IN VARCHAR2,
30 x_attribute_category IN VARCHAR2,
31 x_name IN VARCHAR2,
32 x_description IN VARCHAR2,
33 x_creation_date IN DATE,
34 x_created_by IN NUMBER,
35 x_last_update_date IN DATE,
36 x_last_updated_by IN NUMBER,
37 x_last_update_login IN NUMBER,
38 x_rule IN VARCHAR2,
39 x_workflow_type IN VARCHAR2 default null,
40 x_spares_allowed_flag IN VARCHAR2,
41 x_set_actual_dates_flag IN VARCHAR2 default null
42 )
43 IS
44 CURSOR c
45 IS
46 SELECT ROWID
47 FROM jtf_task_types_b
48 WHERE task_type_id = x_task_type_id;
49 BEGIN
50 INSERT INTO jtf_task_types_b (
51 task_type_id,
52 object_version_number,
53 start_date_active,
54 end_date_active,
55 seeded_flag,
56 workflow,
57 planned_effort,
58 planned_effort_uom,
59 schedule_flag,
60 notification_flag,
61 private_flag,
62 attribute1,
63 attribute2,
64 attribute3,
65 attribute4,
66 attribute5,
67 attribute6,
68 attribute7,
69 attribute8,
70 attribute9,
71 attribute10,
72 attribute11,
73 attribute12,
74 attribute13,
75 attribute14,
76 attribute15,
77 attribute_category,
78 creation_date,
79 created_by,
80 last_update_date,
81 last_updated_by,
82 last_update_login,
83 rule,
84 workflow_type,
85 spares_allowed_flag,
86 set_actual_dates_flag
87 )
88 VALUES (
89 x_task_type_id,
90 1,
91 x_start_date_active,
92 x_end_date_active,
93 x_seeded_flag,
94 x_workflow,
95 x_planned_effort,
96 x_planned_effort_uom,
97 x_schedule_flag,
98 x_notification_flag,
99 x_private_flag,
100 x_attribute1,
101 x_attribute2,
102 x_attribute3,
103 x_attribute4,
104 x_attribute5,
105 x_attribute6,
106 x_attribute7,
107 x_attribute8,
108 x_attribute9,
109 x_attribute10,
110 x_attribute11,
111 x_attribute12,
112 x_attribute13,
113 x_attribute14,
114 x_attribute15,
115 x_attribute_category,
116 x_creation_date,
117 x_created_by,
118 x_last_update_date,
119 x_last_updated_by,
120 x_last_update_login,
121 x_rule,
122 x_workflow_type,
123 x_spares_allowed_flag,
124 x_set_actual_dates_flag
125 );
126 INSERT INTO jtf_task_types_tl
127 (task_type_id,
128 name,
129 description,
130 created_by,
131 creation_date,
132 last_updated_by,
133 last_update_date,
134 last_update_login,
135 language,
136 source_lang
137 )
138 SELECT x_task_type_id,
139 x_name,
140 x_description,
141 x_created_by,
142 x_creation_date,
143 x_last_updated_by,
144 x_last_update_date,
145 x_last_update_login,
146 l.language_code,
147 USERENV ('LANG')
148 FROM fnd_languages l
149 WHERE l.installed_flag IN ('I', 'B')
150 AND NOT EXISTS (SELECT NULL
151 FROM jtf_task_types_tl t
152 WHERE t.task_type_id = x_task_type_id
153 AND t.language = l.language_code);
154 OPEN c;
155 FETCH c INTO x_rowid;
156
157 IF (c%NOTFOUND)
158 THEN
159 CLOSE c;
160 RAISE NO_DATA_FOUND;
161 END IF;
162
163 CLOSE c;
164 END insert_row;
165
166 PROCEDURE lock_row (
167 x_task_type_id IN NUMBER,
168 x_object_version_number IN NUMBER
169 )
170 IS
171 CURSOR c
172 IS
173 SELECT object_version_number
174 FROM jtf_task_types_vl
175 WHERE task_type_id = x_task_type_id
176 AND object_version_number = x_object_version_number
177 FOR UPDATE OF task_type_id NOWAIT;
178
179 recinfo c%ROWTYPE;
180 BEGIN
181 OPEN c;
182 FETCH c INTO recinfo;
183
184 IF (c%NOTFOUND)
185 THEN
186 CLOSE c;
187 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
188 app_exception.raise_exception;
189 END IF;
190
191 CLOSE c;
192
193 IF recinfo.object_version_number = x_object_version_number
194 THEN
195 NULL;
196 ELSE
197 fnd_message.set_name ('FND', 'FORM_RECORD_CHANGED');
198 app_exception.raise_exception;
199 NULL;
200 END IF;
201 END lock_row;
202
203 PROCEDURE update_row (
204 x_task_type_id IN NUMBER,
205 x_object_version_number IN NUMBER,
206 x_start_date_active IN DATE,
207 x_end_date_active IN DATE,
208 x_seeded_flag IN VARCHAR2,
209 x_workflow IN VARCHAR2,
210 x_planned_effort IN NUMBER,
211 x_planned_effort_uom IN VARCHAR2,
212 x_schedule_flag IN VARCHAR2,
213 x_notification_flag IN VARCHAR2,
214 x_private_flag IN VARCHAR2,
215 x_attribute1 IN VARCHAR2,
216 x_attribute2 IN VARCHAR2,
217 x_attribute3 IN VARCHAR2,
218 x_attribute4 IN VARCHAR2,
219 x_attribute5 IN VARCHAR2,
220 x_attribute6 IN VARCHAR2,
221 x_attribute7 IN VARCHAR2,
222 x_attribute8 IN VARCHAR2,
223 x_attribute9 IN VARCHAR2,
224 x_attribute10 IN VARCHAR2,
225 x_attribute11 IN VARCHAR2,
226 x_attribute12 IN VARCHAR2,
227 x_attribute13 IN VARCHAR2,
228 x_attribute14 IN VARCHAR2,
229 x_attribute15 IN VARCHAR2,
230 x_attribute_category IN VARCHAR2,
231 x_name IN VARCHAR2,
232 x_description IN VARCHAR2,
233 x_last_update_date IN DATE,
234 x_last_updated_by IN NUMBER,
235 x_last_update_login IN NUMBER,
236 x_rule IN VARCHAR2,
237 x_workflow_type IN VARCHAR2 DEFAULT NULL,
238 x_spares_allowed_flag IN VARCHAR2,
239 x_set_actual_dates_flag IN VARCHAR2 default null
240 )
241 IS
242 BEGIN
243 UPDATE jtf_task_types_b
244 SET start_date_active = x_start_date_active,
245 object_version_number = x_object_version_number + 1,
246 end_date_active = x_end_date_active,
247 seeded_flag = x_seeded_flag,
248 workflow = x_workflow,
249 planned_effort = x_planned_effort,
250 planned_effort_uom = x_planned_effort_uom,
251 schedule_flag = x_schedule_flag,
252 notification_flag = x_notification_flag,
253 private_flag = x_private_flag,
254 attribute1 = x_attribute1,
255 attribute2 = x_attribute2,
256 attribute3 = x_attribute3,
257 attribute4 = x_attribute4,
258 attribute5 = x_attribute5,
259 attribute6 = x_attribute6,
260 attribute7 = x_attribute7,
261 attribute8 = x_attribute8,
262 attribute9 = x_attribute9,
263 attribute10 = x_attribute10,
264 attribute11 = x_attribute11,
265 attribute12 = x_attribute12,
266 attribute13 = x_attribute13,
267 attribute14 = x_attribute14,
268 attribute15 = x_attribute15,
269 attribute_category = x_attribute_category,
270 last_update_date = x_last_update_date,
271 last_updated_by = x_last_updated_by,
272 last_update_login = x_last_update_login,
273 rule = x_rule,
274 workflow_type = x_workflow_type,
275 spares_allowed_flag = x_spares_allowed_flag,
276 set_actual_dates_flag = x_set_actual_dates_flag
277 WHERE task_type_id = x_task_type_id;
278
279 --and OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER ;
280 IF (SQL%NOTFOUND)
281 THEN
282 RAISE NO_DATA_FOUND;
283 END IF;
284
285 -- Added Index Hint on 30/05/2006 for bug# 5213367
286 UPDATE /*+ INDEX(a JTF_TASK_TYPES_TL_U1) */ jtf_task_types_tl a
287 SET a.name = x_name,
288 a.description = x_description,
289 a.last_update_date = x_last_update_date,
290 a.last_updated_by = x_last_updated_by,
291 a.last_update_login = x_last_update_login,
292 a.source_lang = USERENV ('LANG')
293 WHERE a.task_type_id = x_task_type_id
294 AND USERENV ('LANG') IN (a.language, a.source_lang);
295
296 IF (SQL%NOTFOUND)
297 THEN
298 RAISE NO_DATA_FOUND;
299 END IF;
300 END update_row;
301
302 PROCEDURE delete_row (x_task_type_id IN NUMBER)
303 IS
304 BEGIN
305 DELETE
306 FROM jtf_task_types_tl
307 WHERE task_type_id = x_task_type_id;
308
309 IF (SQL%NOTFOUND)
310 THEN
311 RAISE NO_DATA_FOUND;
312 END IF;
313
314 DELETE
315 FROM jtf_task_types_b
316 WHERE task_type_id = x_task_type_id;
317
318 IF (SQL%NOTFOUND)
319 THEN
320 RAISE NO_DATA_FOUND;
321 END IF;
322 END delete_row;
323
324 PROCEDURE add_language
325 IS
326 BEGIN
327
328 /* Solving Perf. Bug 3723927*/
329 /* The following delete and update statements are commented out */
330 /* as a quick workaround to fix the time-consuming table handler issue */
331 /*
332 DELETE
333 FROM jtf_task_types_tl t
334 WHERE NOT EXISTS (SELECT NULL
335 FROM jtf_task_types_b b
336 WHERE b.task_type_id = t.task_type_id);
337 UPDATE jtf_task_types_tl t
338 SET (name, description) = ( SELECT b.name, b.description
339 FROM jtf_task_types_tl b
340 WHERE b.task_type_id = t.task_type_id
341 AND b.language = t.source_lang)
342 WHERE (t.task_type_id, t.language) IN
343 (SELECT subt.task_type_id, subt.language
344 FROM jtf_task_types_tl subb, jtf_task_types_tl subt
345 WHERE subb.task_type_id = subt.task_type_id
346 AND subb.language = subt.source_lang
347 AND ( subb.name <> subt.name
348 OR subb.description <> subt.description
349 OR ( subb.description IS NULL
350 AND subt.description IS NOT NULL)
351 OR ( subb.description IS NOT NULL
352 AND subt.description IS NULL)));
353 */
354
355 INSERT INTO jtf_task_types_tl
356 (task_type_id,
357 name,
358 description,
359 created_by,
360 creation_date,
361 last_updated_by,
362 last_update_date,
363 last_update_login,
364 language,
365 source_lang
366 )
367 SELECT /*+ INDEX(b JTF_TASK_TYPES_TL_U1) INDEX (l FND_LANGUAGES_N1) */ -- Added Index Hint on 30/05/2006 for bug# 5213367
368 b.task_type_id,
369 b.name,
370 b.description,
371 b.created_by,
372 b.creation_date,
373 b.last_updated_by,
374 b.last_update_date,
375 b.last_update_login,
376 l.language_code,
377 b.source_lang
378 FROM jtf_task_types_tl b, fnd_languages l
379 WHERE l.installed_flag IN ('I', 'B')
380 AND b.language = USERENV ('LANG')
381 AND NOT EXISTS (SELECT NULL
382 FROM jtf_task_types_tl t
383 WHERE t.task_type_id = b.task_type_id
384 AND t.language = l.language_code);
385 END add_language;
386
387 PROCEDURE translate_row (
388 x_task_type_id IN VARCHAR2,
389 x_name IN VARCHAR2,
390 x_description IN VARCHAR2,
391 x_owner in varchar2
392 )
393 IS
394 l_user_id NUMBER := 0;
395 BEGIN
396 IF x_owner = 'SEED'
397 THEN
398 l_user_id := 1;
399 END IF;
400
401 -- Added Index Hint on 30/05/2006 for bug# 5213367
402 UPDATE /*+ INDEX(a JTF_TASK_TYPES_TL_U1) */ jtf_task_types_tl a
403 SET a.name = NVL (x_name, a.name),
404 a.description = NVL (x_description, a.description),
405 a.last_update_date = SYSDATE,
406 a.last_update_login = 0,
407 a.source_lang = USERENV ('LANG'),
408 a.last_updated_by = l_user_id
409 WHERE a.task_type_id = x_task_type_id
410 AND USERENV ('LANG') IN (a.language, a.source_lang);
411
412 IF (SQL%NOTFOUND)
413 THEN
414 RAISE NO_DATA_FOUND;
415 END IF;
416 END translate_row;
417
418 PROCEDURE load_row (
419 x_task_type_id IN NUMBER,
420 x_start_date_active IN DATE,
421 x_end_date_active IN DATE,
422 x_seeded_flag IN VARCHAR2,
423 x_workflow IN VARCHAR2,
424 x_planned_effort IN NUMBER,
425 x_planned_effort_uom IN VARCHAR2,
426 x_schedule_flag IN VARCHAR2,
427 x_notification_flag IN VARCHAR2,
428 x_private_flag IN VARCHAR2,
429 x_attribute1 IN VARCHAR2,
430 x_attribute2 IN VARCHAR2,
431 x_attribute3 IN VARCHAR2,
432 x_attribute4 IN VARCHAR2,
433 x_attribute5 IN VARCHAR2,
434 x_attribute6 IN VARCHAR2,
435 x_attribute7 IN VARCHAR2,
436 x_attribute8 IN VARCHAR2,
437 x_attribute9 IN VARCHAR2,
438 x_attribute10 IN VARCHAR2,
439 x_attribute11 IN VARCHAR2,
440 x_attribute12 IN VARCHAR2,
441 x_attribute13 IN VARCHAR2,
442 x_attribute14 IN VARCHAR2,
443 x_attribute15 IN VARCHAR2,
444 x_attribute_category IN VARCHAR2,
445 x_name IN VARCHAR2,
446 x_description IN VARCHAR2,
447 x_creation_date IN DATE,
448 x_created_by IN NUMBER,
449 x_last_update_date IN DATE,
450 x_last_updated_by IN NUMBER,
451 x_last_update_login IN NUMBER,
452 x_rule IN VARCHAR2,
453 x_owner IN VARCHAR2,
454 x_workflow_type IN VARCHAR2,
455 x_spares_allowed_flag IN VARCHAR2
456 )
457 AS
458 l_user_id NUMBER := 0;
459 l_task_type_id NUMBER;
460 l_rowid ROWID;
461 l_object_version_number NUMBER;
462 BEGIN
463 IF x_owner = 'SEED'
464 THEN
465 l_user_id := 1;
466 END IF;
467
468 SELECT task_type_id, object_version_number
469 INTO l_task_type_id, l_object_version_number
470 FROM jtf_task_types_b
471 WHERE task_type_id = x_task_type_id;
472
473
474
475
476 UPDATE jtf_task_types_b
477 SET start_date_active = x_start_date_active,
478 object_version_number = l_object_version_number + 1,
479 end_date_active = x_end_date_active,
480 seeded_flag = x_seeded_flag,
481 workflow = x_workflow,
482 planned_effort = x_planned_effort,
483 planned_effort_uom = x_planned_effort_uom,
484 schedule_flag = x_schedule_flag,
485 notification_flag = x_notification_flag,
486 private_flag = x_private_flag,
487 last_update_date = sysdate,
488 last_updated_by = l_user_id,
489 last_update_login = 0,
490 rule = x_rule,
491 workflow_type = x_workflow_type,
492 spares_allowed_flag = x_spares_allowed_flag
493 WHERE task_type_id = l_task_type_id;
494
495 -- Added Index Hint on 30/05/2006 for bug# 5213367
496 UPDATE /*+ INDEX(a JTF_TASK_TYPES_TL_U1) */ jtf_task_types_tl a
497 SET a.name = x_name,
498 a.description = x_description,
499 a.last_update_date = sysdate,
500 a.last_updated_by = l_user_id,
501 a.last_update_login = 0,
502 a.source_lang = USERENV ('LANG')
503 WHERE a.task_type_id = l_task_type_id
504 AND USERENV ('LANG') IN (a.language, a.source_lang);
505
506
507
508 EXCEPTION
509 WHEN NO_DATA_FOUND
510 THEN
511 jtf_task_types_pkg.insert_row (
512 x_rowid => l_rowid,
513 x_rule => x_rule,
514 x_task_type_id => x_task_type_id,
515 x_start_date_active => x_start_date_active,
516 x_end_date_active => x_end_date_active,
517 x_seeded_flag => x_seeded_flag,
518 x_workflow => x_workflow,
519 x_planned_effort => x_planned_effort,
520 x_planned_effort_uom => x_planned_effort_uom,
521 x_schedule_flag => x_schedule_flag,
522 x_notification_flag => x_notification_flag,
523 x_private_flag => x_private_flag,
524 x_attribute1 => x_attribute1,
525 x_attribute2 => x_attribute2,
526 x_attribute3 => x_attribute3,
527 x_attribute4 => x_attribute4,
528 x_attribute5 => x_attribute5,
529 x_attribute6 => x_attribute6,
530 x_attribute7 => x_attribute7,
531 x_attribute8 => x_attribute8,
532 x_attribute9 => x_attribute9,
533 x_attribute10 => x_attribute10,
534 x_attribute11 => x_attribute11,
535 x_attribute12 => x_attribute12,
536 x_attribute13 => x_attribute13,
537 x_attribute14 => x_attribute14,
538 x_attribute15 => x_attribute15,
539 x_attribute_category => x_attribute_category,
540 x_name => x_name,
541 x_description => x_description,
542 x_creation_date => SYSDATE,
543 x_created_by => l_user_id,
544 x_last_update_date => SYSDATE,
545 x_last_updated_by => l_user_id,
546 x_last_update_login => 0,
547 x_workflow_type => x_workflow_type,
548 x_spares_allowed_flag => x_spares_allowed_flag
549 );
550
551
552 END;
553 END jtf_task_types_pkg;