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