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