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