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