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