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