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