1 package body CUG_SR_TASK_TYPE_DETS_PKG as
2 /* $Header: CUGSRTTB.pls 115.4 2002/12/04 20:27:36 pkesani noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_SR_TASK_TYPE_DET_ID in NUMBER,
6 X_ASSIGNED_BY_ID in NUMBER,
7 X_ASSIGNEE_TYPE_CODE in VARCHAR2,
8 X_TASK_PRIORITY_ID in NUMBER,
9 X_PLANNED_START_OFFSET in NUMBER,
10 X_PLANNED_END_OFFSET in NUMBER,
11 X_OBJECT_VERSION_NUMBER in NUMBER,
12 X_ATTRIBUTE6 in VARCHAR2,
13 X_ATTRIBUTE7 in VARCHAR2,
14 X_ATTRIBUTE8 in VARCHAR2,
15 X_ATTRIBUTE9 in VARCHAR2,
16 X_ATTRIBUTE10 in VARCHAR2,
17 X_ATTRIBUTE11 in VARCHAR2,
18 X_ATTRIBUTE12 in VARCHAR2,
19 X_ATTRIBUTE13 in VARCHAR2,
20 X_ATTRIBUTE14 in VARCHAR2,
21 X_ATTRIBUTE15 in VARCHAR2,
22 X_ATTRIBUTE_CATEGORY in VARCHAR2,
23 X_ATTRIBUTE2 in VARCHAR2,
24 X_OWNER_TYPE_CODE in VARCHAR2,
25 X_PLANNED_START_UOM in VARCHAR2,
26 X_PLANNED_END_UOM in VARCHAR2,
27 X_SCHEDULED_START_OFFSET in NUMBER,
28 X_TSK_TYP_ATTR_DEP_ID in NUMBER,
29 X_TASK_STATUS_ID in NUMBER,
30 X_ATTRIBUTE3 in VARCHAR2,
31 X_ATTRIBUTE4 in VARCHAR2,
32 X_OWNER_ID in NUMBER,
33 X_SCHEDULED_END_OFFSET in NUMBER,
34 X_SCHEDULED_START_UOM in VARCHAR2,
35 X_SCHEDULED_END_UOM in VARCHAR2,
36 X_PRIVATE_FLAG in VARCHAR2,
37 X_PUBLISH_FLAG in VARCHAR2,
38 X_ATTRIBUTE1 in VARCHAR2,
39 X_ATTRIBUTE5 in VARCHAR2,
40 X_TASK_NAME in VARCHAR2,
41 X_DESCRIPTION in VARCHAR2,
42 X_CREATION_DATE in DATE,
43 X_CREATED_BY in NUMBER,
44 X_LAST_UPDATE_DATE in DATE,
45 X_LAST_UPDATED_BY in NUMBER,
46 X_LAST_UPDATE_LOGIN in NUMBER
47 ) is
48 cursor C is select ROWID from CUG_SR_TASK_TYPE_DETS_B
49 where SR_TASK_TYPE_DET_ID = X_SR_TASK_TYPE_DET_ID
50 ;
51 begin
52 insert into CUG_SR_TASK_TYPE_DETS_B (
53 ASSIGNED_BY_ID,
54 ASSIGNEE_TYPE_CODE,
55 TASK_PRIORITY_ID,
56 PLANNED_START_OFFSET,
57 PLANNED_END_OFFSET,
58 SR_TASK_TYPE_DET_ID,
59 OBJECT_VERSION_NUMBER,
60 ATTRIBUTE6,
61 ATTRIBUTE7,
62 ATTRIBUTE8,
63 ATTRIBUTE9,
64 ATTRIBUTE10,
65 ATTRIBUTE11,
66 ATTRIBUTE12,
67 ATTRIBUTE13,
68 ATTRIBUTE14,
69 ATTRIBUTE15,
70 ATTRIBUTE_CATEGORY,
71 ATTRIBUTE2,
72 OWNER_TYPE_CODE,
73 PLANNED_START_UOM,
74 PLANNED_END_UOM,
75 SCHEDULED_START_OFFSET,
76 TSK_TYP_ATTR_DEP_ID,
77 TASK_STATUS_ID,
78 ATTRIBUTE3,
79 ATTRIBUTE4,
80 OWNER_ID,
81 SCHEDULED_END_OFFSET,
82 SCHEDULED_START_UOM,
83 SCHEDULED_END_UOM,
84 PRIVATE_FLAG,
85 PUBLISH_FLAG,
86 ATTRIBUTE1,
87 ATTRIBUTE5,
88 CREATION_DATE,
89 CREATED_BY,
90 LAST_UPDATE_DATE,
91 LAST_UPDATED_BY,
92 LAST_UPDATE_LOGIN
93 ) values (
94 X_ASSIGNED_BY_ID,
95 X_ASSIGNEE_TYPE_CODE,
96 X_TASK_PRIORITY_ID,
97 X_PLANNED_START_OFFSET,
98 X_PLANNED_END_OFFSET,
99 X_SR_TASK_TYPE_DET_ID,
100 X_OBJECT_VERSION_NUMBER,
101 X_ATTRIBUTE6,
102 X_ATTRIBUTE7,
103 X_ATTRIBUTE8,
104 X_ATTRIBUTE9,
105 X_ATTRIBUTE10,
106 X_ATTRIBUTE11,
107 X_ATTRIBUTE12,
108 X_ATTRIBUTE13,
109 X_ATTRIBUTE14,
110 X_ATTRIBUTE15,
111 X_ATTRIBUTE_CATEGORY,
112 X_ATTRIBUTE2,
113 X_OWNER_TYPE_CODE,
114 X_PLANNED_START_UOM,
115 X_PLANNED_END_UOM,
116 X_SCHEDULED_START_OFFSET,
117 X_TSK_TYP_ATTR_DEP_ID,
118 X_TASK_STATUS_ID,
119 X_ATTRIBUTE3,
120 X_ATTRIBUTE4,
121 X_OWNER_ID,
122 X_SCHEDULED_END_OFFSET,
123 X_SCHEDULED_START_UOM,
124 X_SCHEDULED_END_UOM,
125 X_PRIVATE_FLAG,
126 X_PUBLISH_FLAG,
127 X_ATTRIBUTE1,
128 X_ATTRIBUTE5,
129 X_CREATION_DATE,
130 X_CREATED_BY,
131 X_LAST_UPDATE_DATE,
132 X_LAST_UPDATED_BY,
133 X_LAST_UPDATE_LOGIN
134 );
135
136 insert into CUG_SR_TASK_TYPE_DETS_TL (
137 SR_TASK_TYPE_DET_ID,
138 CREATED_BY,
139 CREATION_DATE,
140 LAST_UPDATED_BY,
141 LAST_UPDATE_LOGIN,
142 LAST_UPDATE_DATE,
143 TASK_NAME,
144 DESCRIPTION,
145 LANGUAGE,
146 SOURCE_LANG
147 ) select
148 X_SR_TASK_TYPE_DET_ID,
149 X_CREATED_BY,
150 X_CREATION_DATE,
151 X_LAST_UPDATED_BY,
152 X_LAST_UPDATE_LOGIN,
153 X_LAST_UPDATE_DATE,
154 X_TASK_NAME,
155 X_DESCRIPTION,
156 L.LANGUAGE_CODE,
157 userenv('LANG')
158 from FND_LANGUAGES L
159 where L.INSTALLED_FLAG in ('I', 'B')
160 and not exists
161 (select NULL
162 from CUG_SR_TASK_TYPE_DETS_TL T
163 where T.SR_TASK_TYPE_DET_ID = X_SR_TASK_TYPE_DET_ID
164 and T.LANGUAGE = L.LANGUAGE_CODE);
165
166 open c;
167 fetch c into X_ROWID;
168 if (c%notfound) then
169 close c;
170 raise no_data_found;
171 end if;
172 close c;
173
174 end INSERT_ROW;
175
176 procedure LOCK_ROW (
177 X_SR_TASK_TYPE_DET_ID in NUMBER,
178 X_ASSIGNED_BY_ID in NUMBER,
179 X_ASSIGNEE_TYPE_CODE in VARCHAR2,
180 X_TASK_PRIORITY_ID in NUMBER,
181 X_PLANNED_START_OFFSET in NUMBER,
182 X_PLANNED_END_OFFSET in NUMBER,
183 X_OBJECT_VERSION_NUMBER in NUMBER,
184 X_ATTRIBUTE6 in VARCHAR2,
185 X_ATTRIBUTE7 in VARCHAR2,
186 X_ATTRIBUTE8 in VARCHAR2,
187 X_ATTRIBUTE9 in VARCHAR2,
188 X_ATTRIBUTE10 in VARCHAR2,
189 X_ATTRIBUTE11 in VARCHAR2,
190 X_ATTRIBUTE12 in VARCHAR2,
191 X_ATTRIBUTE13 in VARCHAR2,
192 X_ATTRIBUTE14 in VARCHAR2,
193 X_ATTRIBUTE15 in VARCHAR2,
194 X_ATTRIBUTE_CATEGORY in VARCHAR2,
195 X_ATTRIBUTE2 in VARCHAR2,
196 X_OWNER_TYPE_CODE in VARCHAR2,
197 X_PLANNED_START_UOM in VARCHAR2,
198 X_PLANNED_END_UOM in VARCHAR2,
199 X_SCHEDULED_START_OFFSET in NUMBER,
200 X_TSK_TYP_ATTR_DEP_ID in NUMBER,
201 X_TASK_STATUS_ID in NUMBER,
202 X_ATTRIBUTE3 in VARCHAR2,
203 X_ATTRIBUTE4 in VARCHAR2,
204 X_OWNER_ID in NUMBER,
205 X_SCHEDULED_END_OFFSET in NUMBER,
206 X_SCHEDULED_START_UOM in VARCHAR2,
207 X_SCHEDULED_END_UOM in VARCHAR2,
208 X_PRIVATE_FLAG in VARCHAR2,
209 X_PUBLISH_FLAG in VARCHAR2,
210 X_ATTRIBUTE1 in VARCHAR2,
211 X_ATTRIBUTE5 in VARCHAR2,
212 X_TASK_NAME in VARCHAR2,
213 X_DESCRIPTION in VARCHAR2
214 ) is
215 cursor c is select
216 ASSIGNED_BY_ID,
217 ASSIGNEE_TYPE_CODE,
218 TASK_PRIORITY_ID,
219 PLANNED_START_OFFSET,
220 PLANNED_END_OFFSET,
221 OBJECT_VERSION_NUMBER,
222 ATTRIBUTE6,
223 ATTRIBUTE7,
224 ATTRIBUTE8,
225 ATTRIBUTE9,
226 ATTRIBUTE10,
227 ATTRIBUTE11,
228 ATTRIBUTE12,
229 ATTRIBUTE13,
230 ATTRIBUTE14,
231 ATTRIBUTE15,
232 ATTRIBUTE_CATEGORY,
233 ATTRIBUTE2,
234 OWNER_TYPE_CODE,
235 PLANNED_START_UOM,
236 PLANNED_END_UOM,
237 SCHEDULED_START_OFFSET,
238 TSK_TYP_ATTR_DEP_ID,
239 TASK_STATUS_ID,
240 ATTRIBUTE3,
241 ATTRIBUTE4,
242 OWNER_ID,
243 SCHEDULED_END_OFFSET,
244 SCHEDULED_START_UOM,
245 SCHEDULED_END_UOM,
246 PRIVATE_FLAG,
247 PUBLISH_FLAG,
248 ATTRIBUTE1,
249 ATTRIBUTE5
250 from CUG_SR_TASK_TYPE_DETS_B
251 where SR_TASK_TYPE_DET_ID = X_SR_TASK_TYPE_DET_ID
252 for update of SR_TASK_TYPE_DET_ID nowait;
253 recinfo c%rowtype;
254
255 cursor c1 is select
256 TASK_NAME,
257 DESCRIPTION,
258 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
259 from CUG_SR_TASK_TYPE_DETS_TL
260 where SR_TASK_TYPE_DET_ID = X_SR_TASK_TYPE_DET_ID
261 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
262 for update of SR_TASK_TYPE_DET_ID nowait;
263 begin
264 open c;
265 fetch c into recinfo;
266 if (c%notfound) then
267 close c;
268 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
269 app_exception.raise_exception;
270 end if;
271 close c;
272 if ( ((recinfo.ASSIGNED_BY_ID = X_ASSIGNED_BY_ID)
273 OR ((recinfo.ASSIGNED_BY_ID is null) AND (X_ASSIGNED_BY_ID is null)))
274 AND ((recinfo.ASSIGNEE_TYPE_CODE = X_ASSIGNEE_TYPE_CODE)
275 OR ((recinfo.ASSIGNEE_TYPE_CODE is null) AND (X_ASSIGNEE_TYPE_CODE is null)))
276 AND ((recinfo.TASK_PRIORITY_ID = X_TASK_PRIORITY_ID)
277 OR ((recinfo.TASK_PRIORITY_ID is null) AND (X_TASK_PRIORITY_ID is null)))
278 AND ((recinfo.PLANNED_START_OFFSET = X_PLANNED_START_OFFSET)
279 OR ((recinfo.PLANNED_START_OFFSET is null) AND (X_PLANNED_START_OFFSET is null)))
280 AND ((recinfo.PLANNED_END_OFFSET = X_PLANNED_END_OFFSET)
281 OR ((recinfo.PLANNED_END_OFFSET is null) AND (X_PLANNED_END_OFFSET is null)))
282 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
283 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
284 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
285 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
286 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
287 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
288 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
289 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
290 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
291 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
292 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
293 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
294 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
295 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
296 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
297 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
298 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
299 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
300 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
301 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
302 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
303 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
304 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
305 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
306 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
307 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
308 AND (recinfo.OWNER_TYPE_CODE = X_OWNER_TYPE_CODE)
309 AND ((recinfo.PLANNED_START_UOM = X_PLANNED_START_UOM)
310 OR ((recinfo.PLANNED_START_UOM is null) AND (X_PLANNED_START_UOM is null)))
311 AND ((recinfo.PLANNED_END_UOM = X_PLANNED_END_UOM)
312 OR ((recinfo.PLANNED_END_UOM is null) AND (X_PLANNED_END_UOM is null)))
313 AND ((recinfo.SCHEDULED_START_OFFSET = X_SCHEDULED_START_OFFSET)
314 OR ((recinfo.SCHEDULED_START_OFFSET is null) AND (X_SCHEDULED_START_OFFSET is null)))
315 AND (recinfo.TSK_TYP_ATTR_DEP_ID = X_TSK_TYP_ATTR_DEP_ID)
316 AND (recinfo.TASK_STATUS_ID = X_TASK_STATUS_ID)
317 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
318 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
319 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
320 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
321 AND (recinfo.OWNER_ID = X_OWNER_ID)
322 AND ((recinfo.SCHEDULED_END_OFFSET = X_SCHEDULED_END_OFFSET)
323 OR ((recinfo.SCHEDULED_END_OFFSET is null) AND (X_SCHEDULED_END_OFFSET is null)))
324 AND ((recinfo.SCHEDULED_START_UOM = X_SCHEDULED_START_UOM)
325 OR ((recinfo.SCHEDULED_START_UOM is null) AND (X_SCHEDULED_START_UOM is null)))
326 AND ((recinfo.SCHEDULED_END_UOM = X_SCHEDULED_END_UOM)
327 OR ((recinfo.SCHEDULED_END_UOM is null) AND (X_SCHEDULED_END_UOM is null)))
328 AND ((recinfo.PRIVATE_FLAG = X_PRIVATE_FLAG)
329 OR ((recinfo.PRIVATE_FLAG is null) AND (X_PRIVATE_FLAG is null)))
330 AND ((recinfo.PUBLISH_FLAG = X_PUBLISH_FLAG)
331 OR ((recinfo.PUBLISH_FLAG is null) AND (X_PUBLISH_FLAG is null)))
332 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
333 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
334 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
335 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
336 ) then
337 null;
338 else
339 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
340 app_exception.raise_exception;
341 end if;
342
343 for tlinfo in c1 loop
344 if (tlinfo.BASELANG = 'Y') then
345 if ( (tlinfo.TASK_NAME = X_TASK_NAME)
346 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
347 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
348 ) then
349 null;
350 else
351 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
352 app_exception.raise_exception;
353 end if;
354 end if;
355 end loop;
356 return;
357 end LOCK_ROW;
358
359 procedure UPDATE_ROW (
360 X_SR_TASK_TYPE_DET_ID in NUMBER,
361 X_ASSIGNED_BY_ID in NUMBER,
362 X_ASSIGNEE_TYPE_CODE in VARCHAR2,
363 X_TASK_PRIORITY_ID in NUMBER,
364 X_PLANNED_START_OFFSET in NUMBER,
365 X_PLANNED_END_OFFSET in NUMBER,
366 X_OBJECT_VERSION_NUMBER in NUMBER,
367 X_ATTRIBUTE6 in VARCHAR2,
368 X_ATTRIBUTE7 in VARCHAR2,
369 X_ATTRIBUTE8 in VARCHAR2,
370 X_ATTRIBUTE9 in VARCHAR2,
371 X_ATTRIBUTE10 in VARCHAR2,
372 X_ATTRIBUTE11 in VARCHAR2,
373 X_ATTRIBUTE12 in VARCHAR2,
374 X_ATTRIBUTE13 in VARCHAR2,
375 X_ATTRIBUTE14 in VARCHAR2,
376 X_ATTRIBUTE15 in VARCHAR2,
377 X_ATTRIBUTE_CATEGORY in VARCHAR2,
378 X_ATTRIBUTE2 in VARCHAR2,
379 X_OWNER_TYPE_CODE in VARCHAR2,
380 X_PLANNED_START_UOM in VARCHAR2,
381 X_PLANNED_END_UOM in VARCHAR2,
382 X_SCHEDULED_START_OFFSET in NUMBER,
383 X_TSK_TYP_ATTR_DEP_ID in NUMBER,
384 X_TASK_STATUS_ID in NUMBER,
385 X_ATTRIBUTE3 in VARCHAR2,
386 X_ATTRIBUTE4 in VARCHAR2,
387 X_OWNER_ID in NUMBER,
388 X_SCHEDULED_END_OFFSET in NUMBER,
389 X_SCHEDULED_START_UOM in VARCHAR2,
390 X_SCHEDULED_END_UOM in VARCHAR2,
391 X_PRIVATE_FLAG in VARCHAR2,
392 X_PUBLISH_FLAG in VARCHAR2,
393 X_ATTRIBUTE1 in VARCHAR2,
394 X_ATTRIBUTE5 in VARCHAR2,
395 X_TASK_NAME in VARCHAR2,
396 X_DESCRIPTION in VARCHAR2,
397 X_LAST_UPDATE_DATE in DATE,
398 X_LAST_UPDATED_BY in NUMBER,
399 X_LAST_UPDATE_LOGIN in NUMBER
400 ) is
401 begin
402 update CUG_SR_TASK_TYPE_DETS_B set
403 ASSIGNED_BY_ID = X_ASSIGNED_BY_ID,
404 ASSIGNEE_TYPE_CODE = X_ASSIGNEE_TYPE_CODE,
405 TASK_PRIORITY_ID = X_TASK_PRIORITY_ID,
406 PLANNED_START_OFFSET = X_PLANNED_START_OFFSET,
407 PLANNED_END_OFFSET = X_PLANNED_END_OFFSET,
408 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
409 ATTRIBUTE6 = X_ATTRIBUTE6,
410 ATTRIBUTE7 = X_ATTRIBUTE7,
411 ATTRIBUTE8 = X_ATTRIBUTE8,
412 ATTRIBUTE9 = X_ATTRIBUTE9,
413 ATTRIBUTE10 = X_ATTRIBUTE10,
414 ATTRIBUTE11 = X_ATTRIBUTE11,
415 ATTRIBUTE12 = X_ATTRIBUTE12,
416 ATTRIBUTE13 = X_ATTRIBUTE13,
417 ATTRIBUTE14 = X_ATTRIBUTE14,
418 ATTRIBUTE15 = X_ATTRIBUTE15,
419 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
420 ATTRIBUTE2 = X_ATTRIBUTE2,
421 OWNER_TYPE_CODE = X_OWNER_TYPE_CODE,
422 PLANNED_START_UOM = X_PLANNED_START_UOM,
423 PLANNED_END_UOM = X_PLANNED_END_UOM,
424 SCHEDULED_START_OFFSET = X_SCHEDULED_START_OFFSET,
425 TSK_TYP_ATTR_DEP_ID = X_TSK_TYP_ATTR_DEP_ID,
426 TASK_STATUS_ID = X_TASK_STATUS_ID,
427 ATTRIBUTE3 = X_ATTRIBUTE3,
428 ATTRIBUTE4 = X_ATTRIBUTE4,
429 OWNER_ID = X_OWNER_ID,
430 SCHEDULED_END_OFFSET = X_SCHEDULED_END_OFFSET,
431 SCHEDULED_START_UOM = X_SCHEDULED_START_UOM,
432 SCHEDULED_END_UOM = X_SCHEDULED_END_UOM,
433 PRIVATE_FLAG = X_PRIVATE_FLAG,
434 PUBLISH_FLAG = X_PUBLISH_FLAG,
435 ATTRIBUTE1 = X_ATTRIBUTE1,
436 ATTRIBUTE5 = X_ATTRIBUTE5,
437 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
438 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
439 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
440 where SR_TASK_TYPE_DET_ID = X_SR_TASK_TYPE_DET_ID;
441
442 if (sql%notfound) then
443 raise no_data_found;
444 end if;
445
446 update CUG_SR_TASK_TYPE_DETS_TL set
447 TASK_NAME = X_TASK_NAME,
448 DESCRIPTION = X_DESCRIPTION,
449 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
450 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
451 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
452 SOURCE_LANG = userenv('LANG')
453 where SR_TASK_TYPE_DET_ID = X_SR_TASK_TYPE_DET_ID
454 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
455
456 if (sql%notfound) then
457 raise no_data_found;
458 end if;
459 end UPDATE_ROW;
460
461 procedure DELETE_ROW (
462 X_SR_TASK_TYPE_DET_ID in NUMBER
463 ) is
464 begin
465 delete from CUG_SR_TASK_TYPE_DETS_TL
466 where SR_TASK_TYPE_DET_ID = X_SR_TASK_TYPE_DET_ID;
467
468 if (sql%notfound) then
469 raise no_data_found;
470 end if;
471
472 delete from CUG_SR_TASK_TYPE_DETS_B
473 where SR_TASK_TYPE_DET_ID = X_SR_TASK_TYPE_DET_ID;
474
475 if (sql%notfound) then
476 raise no_data_found;
477 end if;
478 end DELETE_ROW;
479
480 procedure ADD_LANGUAGE
481 is
482 begin
483 delete from CUG_SR_TASK_TYPE_DETS_TL T
484 where not exists
485 (select NULL
486 from CUG_SR_TASK_TYPE_DETS_B B
487 where B.SR_TASK_TYPE_DET_ID = T.SR_TASK_TYPE_DET_ID
488 );
489
490 update CUG_SR_TASK_TYPE_DETS_TL T set (
491 TASK_NAME,
492 DESCRIPTION
493 ) = (select
494 B.TASK_NAME,
495 B.DESCRIPTION
496 from CUG_SR_TASK_TYPE_DETS_TL B
497 where B.SR_TASK_TYPE_DET_ID = T.SR_TASK_TYPE_DET_ID
498 and B.LANGUAGE = T.SOURCE_LANG)
499 where (
500 T.SR_TASK_TYPE_DET_ID,
501 T.LANGUAGE
502 ) in (select
503 SUBT.SR_TASK_TYPE_DET_ID,
504 SUBT.LANGUAGE
505 from CUG_SR_TASK_TYPE_DETS_TL SUBB, CUG_SR_TASK_TYPE_DETS_TL SUBT
506 where SUBB.SR_TASK_TYPE_DET_ID = SUBT.SR_TASK_TYPE_DET_ID
507 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
508 and (SUBB.TASK_NAME <> SUBT.TASK_NAME
509 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
510 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
511 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
512 ));
513
514 insert into CUG_SR_TASK_TYPE_DETS_TL (
515 SR_TASK_TYPE_DET_ID,
516 CREATED_BY,
517 CREATION_DATE,
518 LAST_UPDATED_BY,
519 LAST_UPDATE_LOGIN,
520 LAST_UPDATE_DATE,
521 TASK_NAME,
522 DESCRIPTION,
523 LANGUAGE,
524 SOURCE_LANG
525 ) select /*+ ORDERED */
526 B.SR_TASK_TYPE_DET_ID,
527 B.CREATED_BY,
528 B.CREATION_DATE,
529 B.LAST_UPDATED_BY,
530 B.LAST_UPDATE_LOGIN,
531 B.LAST_UPDATE_DATE,
532 B.TASK_NAME,
533 B.DESCRIPTION,
534 L.LANGUAGE_CODE,
535 B.SOURCE_LANG
536 from CUG_SR_TASK_TYPE_DETS_TL B, FND_LANGUAGES L
537 where L.INSTALLED_FLAG in ('I', 'B')
538 and B.LANGUAGE = userenv('LANG')
539 and not exists
540 (select NULL
541 from CUG_SR_TASK_TYPE_DETS_TL T
542 where T.SR_TASK_TYPE_DET_ID = B.SR_TASK_TYPE_DET_ID
543 and T.LANGUAGE = L.LANGUAGE_CODE);
544 end ADD_LANGUAGE;
545
546 end CUG_SR_TASK_TYPE_DETS_PKG;
[Home] [Help]