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