1 package body AHL_UNIT_EFFECTIVITIES_PKG as
2 /* $Header: AHLLUEFB.pls 120.2.12020000.2 2012/12/06 23:50:33 sareepar ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_UNIT_EFFECTIVITY_ID in out NOCOPY NUMBER,
6 X_MANUALLY_PLANNED_FLAG in VARCHAR2,
7 X_LOG_SERIES_CODE in VARCHAR2,
8 X_LOG_SERIES_NUMBER in NUMBER,
9 X_FLIGHT_NUMBER in VARCHAR2,
10 X_MEL_CDL_TYPE_CODE in VARCHAR2,
11 X_POSITION_PATH_ID in NUMBER,
12 X_ATA_CODE in VARCHAR2,
13 --X_CLEAR_STATION_ORG_ID in NUMBER,
14 --X_CLEAR_STATION_DEPT_ID in NUMBER,
15 X_UNIT_CONFIG_HEADER_ID in NUMBER,
16 X_QA_COLLECTION_ID in NUMBER,
17 X_CS_INCIDENT_ID in NUMBER,
18 X_OBJECT_TYPE in VARCHAR2,
19 X_APPLICATION_USG_CODE in VARCHAR2,
20 X_COUNTER_ID in NUMBER,
21 X_EARLIEST_DUE_DATE in DATE,
22 X_LATEST_DUE_DATE in DATE,
23 X_FORECAST_SEQUENCE in NUMBER,
24 X_REPETITIVE_MR_FLAG in VARCHAR2,
25 X_TOLERANCE_FLAG in VARCHAR2,
26 X_MESSAGE_CODE in VARCHAR2,
27 X_DATE_RUN in DATE,
28 X_PRECEDING_UE_ID in NUMBER,
29 X_SET_DUE_DATE in DATE,
30 X_ACCOMPLISHED_DATE in DATE,
31 X_SERVICE_LINE_ID in NUMBER,
32 X_PROGRAM_MR_HEADER_ID in NUMBER,
33 X_CANCEL_REASON_CODE in VARCHAR2,
34 X_ATTRIBUTE_CATEGORY in VARCHAR2,
35 X_ATTRIBUTE1 in VARCHAR2,
36 X_ATTRIBUTE2 in VARCHAR2,
37 X_ATTRIBUTE3 in VARCHAR2,
38 X_ATTRIBUTE4 in VARCHAR2,
39 X_ATTRIBUTE5 in VARCHAR2,
40 X_ATTRIBUTE6 in VARCHAR2,
41 X_ATTRIBUTE7 in VARCHAR2,
42 X_ATTRIBUTE8 in VARCHAR2,
43 X_ATTRIBUTE9 in VARCHAR2,
44 X_ATTRIBUTE10 in VARCHAR2,
45 X_ATTRIBUTE11 in VARCHAR2,
46 X_ATTRIBUTE12 in VARCHAR2,
47 X_ATTRIBUTE13 in VARCHAR2,
48 X_ATTRIBUTE14 in VARCHAR2,
49 X_ATTRIBUTE15 in VARCHAR2,
50 X_OBJECT_VERSION_NUMBER in NUMBER,
51 X_CSI_ITEM_INSTANCE_ID in NUMBER,
52 X_MR_HEADER_ID in NUMBER,
53 X_MR_EFFECTIVITY_ID in NUMBER,
54 X_MR_INTERVAL_ID in NUMBER,
55 X_STATUS_CODE in VARCHAR2,
56 X_DUE_DATE in DATE,
57 X_DUE_COUNTER_VALUE in NUMBER,
58 X_DEFER_FROM_UE_ID in NUMBER,
59 X_ORIG_DEFERRAL_UE_ID in NUMBER,
60 X_REMARKS in VARCHAR2,
61 X_CREATION_DATE in DATE,
62 X_CREATED_BY in NUMBER,
63 X_LAST_UPDATE_DATE in DATE,
64 X_LAST_UPDATED_BY in NUMBER,
65 X_LAST_UPDATE_LOGIN in NUMBER,
66 X_FLEET_HEADER_ID IN NUMBER := NULL -- JKJain, NR Analysis and Forecasting
67 ) is
68 cursor C is select ROWID from AHL_UNIT_EFFECTIVITIES_B
69 where UNIT_EFFECTIVITY_ID = X_UNIT_EFFECTIVITY_ID
70 ;
71 begin
72 insert into AHL_UNIT_EFFECTIVITIES_B (
73 MANUALLY_PLANNED_FLAG,
74 LOG_SERIES_CODE,
75 LOG_SERIES_NUMBER,
76 FLIGHT_NUMBER,
77 MEL_CDL_TYPE_CODE,
78 POSITION_PATH_ID,
79 ATA_CODE,
80 --CLEAR_STATION_ORG_ID,
81 --CLEAR_STATION_DEPT_ID,
82 UNIT_CONFIG_HEADER_ID,
83 QA_COLLECTION_ID,
84 CS_INCIDENT_ID,
85 OBJECT_TYPE,
86 APPLICATION_USG_CODE,
87 COUNTER_ID,
88 EARLIEST_DUE_DATE,
89 LATEST_DUE_DATE,
90 FORECAST_SEQUENCE,
91 REPETITIVE_MR_FLAG,
92 TOLERANCE_FLAG,
93 MESSAGE_CODE,
94 DATE_RUN,
95 PRECEDING_UE_ID,
96 SET_DUE_DATE,
97 ACCOMPLISHED_DATE,
98 SERVICE_LINE_ID,
99 PROGRAM_MR_HEADER_ID,
100 CANCEL_REASON_CODE,
101 ATTRIBUTE_CATEGORY,
102 ATTRIBUTE1,
103 ATTRIBUTE2,
104 ATTRIBUTE3,
105 ATTRIBUTE4,
106 ATTRIBUTE5,
107 ATTRIBUTE6,
108 ATTRIBUTE7,
109 ATTRIBUTE8,
110 ATTRIBUTE9,
111 ATTRIBUTE10,
112 ATTRIBUTE11,
113 ATTRIBUTE12,
114 ATTRIBUTE13,
115 ATTRIBUTE14,
116 ATTRIBUTE15,
117 UNIT_EFFECTIVITY_ID,
118 OBJECT_VERSION_NUMBER,
119 CSI_ITEM_INSTANCE_ID,
120 MR_HEADER_ID,
121 MR_EFFECTIVITY_ID,
122 MR_INTERVAL_ID,
123 STATUS_CODE,
124 DUE_DATE,
125 DUE_COUNTER_VALUE,
126 DEFER_FROM_UE_ID,
127 ORIG_DEFERRAL_UE_ID,
128 CREATION_DATE,
129 CREATED_BY,
130 LAST_UPDATE_DATE,
131 LAST_UPDATED_BY,
132 LAST_UPDATE_LOGIN,
133 FLEET_HEADER_ID
134 ) values (
135 X_MANUALLY_PLANNED_FLAG,
136 X_LOG_SERIES_CODE,
137 X_LOG_SERIES_NUMBER,
138 X_FLIGHT_NUMBER,
139 X_MEL_CDL_TYPE_CODE,
140 X_POSITION_PATH_ID,
141 X_ATA_CODE,
142 --X_CLEAR_STATION_ORG_ID,
143 --X_CLEAR_STATION_DEPT_ID,
144 X_UNIT_CONFIG_HEADER_ID,
145 X_QA_COLLECTION_ID,
146 X_CS_INCIDENT_ID,
147 X_OBJECT_TYPE,
148 X_APPLICATION_USG_CODE,
149 X_COUNTER_ID,
150 X_EARLIEST_DUE_DATE,
151 X_LATEST_DUE_DATE,
152 X_FORECAST_SEQUENCE,
153 X_REPETITIVE_MR_FLAG,
154 X_TOLERANCE_FLAG,
155 X_MESSAGE_CODE,
156 X_DATE_RUN,
157 X_PRECEDING_UE_ID,
158 X_SET_DUE_DATE,
159 X_ACCOMPLISHED_DATE,
160 X_SERVICE_LINE_ID,
161 X_PROGRAM_MR_HEADER_ID,
162 X_CANCEL_REASON_CODE,
163 X_ATTRIBUTE_CATEGORY,
164 X_ATTRIBUTE1,
165 X_ATTRIBUTE2,
166 X_ATTRIBUTE3,
167 X_ATTRIBUTE4,
168 X_ATTRIBUTE5,
169 X_ATTRIBUTE6,
170 X_ATTRIBUTE7,
171 X_ATTRIBUTE8,
172 X_ATTRIBUTE9,
173 X_ATTRIBUTE10,
174 X_ATTRIBUTE11,
175 X_ATTRIBUTE12,
176 X_ATTRIBUTE13,
177 X_ATTRIBUTE14,
178 X_ATTRIBUTE15,
179 AHL_UNIT_EFFECTIVITIES_B_S.NEXTVAL,
180 X_OBJECT_VERSION_NUMBER,
181 X_CSI_ITEM_INSTANCE_ID,
182 X_MR_HEADER_ID,
183 X_MR_EFFECTIVITY_ID,
184 X_MR_INTERVAL_ID,
185 X_STATUS_CODE,
186 X_DUE_DATE,
187 X_DUE_COUNTER_VALUE,
188 X_DEFER_FROM_UE_ID,
189 X_ORIG_DEFERRAL_UE_ID,
190 X_CREATION_DATE,
191 X_CREATED_BY,
192 X_LAST_UPDATE_DATE,
193 X_LAST_UPDATED_BY,
194 X_LAST_UPDATE_LOGIN,
195 X_FLEET_HEADER_ID
196 ) RETURNING UNIT_EFFECTIVITY_ID INTO X_UNIT_EFFECTIVITY_ID;
197
198 insert into AHL_UNIT_EFFECTIVITIES_TL (
199 UNIT_EFFECTIVITY_ID,
200 LAST_UPDATE_DATE,
201 LAST_UPDATED_BY,
202 CREATION_DATE,
203 CREATED_BY,
204 LAST_UPDATE_LOGIN,
205 REMARKS,
206 LANGUAGE,
207 SOURCE_LANG
208 ) select
209 X_UNIT_EFFECTIVITY_ID,
210 X_LAST_UPDATE_DATE,
211 X_LAST_UPDATED_BY,
212 X_CREATION_DATE,
213 X_CREATED_BY,
214 X_LAST_UPDATE_LOGIN,
215 X_REMARKS,
216 L.LANGUAGE_CODE,
217 userenv('LANG')
218 from FND_LANGUAGES L
219 where L.INSTALLED_FLAG in ('I', 'B')
220 and not exists
221 (select NULL
222 from AHL_UNIT_EFFECTIVITIES_TL T
223 where T.UNIT_EFFECTIVITY_ID = X_UNIT_EFFECTIVITY_ID
224 and T.LANGUAGE = L.LANGUAGE_CODE);
225
226 open c;
227 fetch c into X_ROWID;
228 if (c%notfound) then
229 close c;
230 raise no_data_found;
231 end if;
232 close c;
233
234 end INSERT_ROW;
235
236 procedure LOCK_ROW (
237 X_UNIT_EFFECTIVITY_ID in NUMBER,
238 X_MANUALLY_PLANNED_FLAG in VARCHAR2,
239 X_LOG_SERIES_CODE in VARCHAR2,
240 X_LOG_SERIES_NUMBER in NUMBER,
241 X_FLIGHT_NUMBER in VARCHAR2,
242 X_MEL_CDL_TYPE_CODE in VARCHAR2,
243 X_POSITION_PATH_ID in NUMBER,
244 X_ATA_CODE in VARCHAR2,
245 --X_CLEAR_STATION_ORG_ID in NUMBER,
246 --X_CLEAR_STATION_DEPT_ID in NUMBER,
247 X_UNIT_CONFIG_HEADER_ID in NUMBER,
248 X_QA_COLLECTION_ID in NUMBER,
249 X_CS_INCIDENT_ID in NUMBER,
250 X_OBJECT_TYPE in VARCHAR2,
251 X_APPLICATION_USG_CODE in VARCHAR2,
252 X_COUNTER_ID in NUMBER,
253 X_EARLIEST_DUE_DATE in DATE,
254 X_LATEST_DUE_DATE in DATE,
255 X_FORECAST_SEQUENCE in NUMBER,
256 X_REPETITIVE_MR_FLAG in VARCHAR2,
257 X_TOLERANCE_FLAG in VARCHAR2,
258 X_MESSAGE_CODE in VARCHAR2,
259 X_DATE_RUN in DATE,
260 X_PRECEDING_UE_ID in NUMBER,
261 X_SET_DUE_DATE in DATE,
262 X_ACCOMPLISHED_DATE in DATE,
263 X_SERVICE_LINE_ID in NUMBER,
264 X_PROGRAM_MR_HEADER_ID in NUMBER,
265 X_CANCEL_REASON_CODE in VARCHAR2,
266 X_ATTRIBUTE_CATEGORY in VARCHAR2,
267 X_ATTRIBUTE1 in VARCHAR2,
268 X_ATTRIBUTE2 in VARCHAR2,
269 X_ATTRIBUTE3 in VARCHAR2,
270 X_ATTRIBUTE4 in VARCHAR2,
271 X_ATTRIBUTE5 in VARCHAR2,
272 X_ATTRIBUTE6 in VARCHAR2,
273 X_ATTRIBUTE7 in VARCHAR2,
274 X_ATTRIBUTE8 in VARCHAR2,
275 X_ATTRIBUTE9 in VARCHAR2,
276 X_ATTRIBUTE10 in VARCHAR2,
277 X_ATTRIBUTE11 in VARCHAR2,
278 X_ATTRIBUTE12 in VARCHAR2,
279 X_ATTRIBUTE13 in VARCHAR2,
280 X_ATTRIBUTE14 in VARCHAR2,
281 X_ATTRIBUTE15 in VARCHAR2,
282 X_OBJECT_VERSION_NUMBER in NUMBER,
283 X_CSI_ITEM_INSTANCE_ID in NUMBER,
284 X_MR_HEADER_ID in NUMBER,
285 X_MR_EFFECTIVITY_ID in NUMBER,
286 X_MR_INTERVAL_ID in NUMBER,
287 X_STATUS_CODE in VARCHAR2,
288 X_DUE_DATE in DATE,
289 X_DUE_COUNTER_VALUE in NUMBER,
290 X_DEFER_FROM_UE_ID in NUMBER,
291 X_ORIG_DEFERRAL_UE_ID in NUMBER,
292 X_REMARKS in VARCHAR2
293 ) is
294 cursor c is select
295 MANUALLY_PLANNED_FLAG,
296 LOG_SERIES_CODE,
297 LOG_SERIES_NUMBER,
298 FLIGHT_NUMBER,
299 MEL_CDL_TYPE_CODE,
300 POSITION_PATH_ID,
301 ATA_CODE,
302 --CLEAR_STATION_ORG_ID,
303 --CLEAR_STATION_DEPT_ID,
304 UNIT_CONFIG_HEADER_ID,
305 QA_COLLECTION_ID,
306 CS_INCIDENT_ID,
307 OBJECT_TYPE,
308 APPLICATION_USG_CODE,
309 COUNTER_ID,
310 EARLIEST_DUE_DATE,
311 LATEST_DUE_DATE,
312 FORECAST_SEQUENCE,
313 REPETITIVE_MR_FLAG,
314 TOLERANCE_FLAG,
315 MESSAGE_CODE,
316 DATE_RUN,
317 PRECEDING_UE_ID,
318 SET_DUE_DATE,
319 ACCOMPLISHED_DATE,
320 SERVICE_LINE_ID,
321 PROGRAM_MR_HEADER_ID,
322 CANCEL_REASON_CODE,
323 ATTRIBUTE_CATEGORY,
324 ATTRIBUTE1,
325 ATTRIBUTE2,
326 ATTRIBUTE3,
327 ATTRIBUTE4,
328 ATTRIBUTE5,
329 ATTRIBUTE6,
330 ATTRIBUTE7,
331 ATTRIBUTE8,
332 ATTRIBUTE9,
333 ATTRIBUTE10,
334 ATTRIBUTE11,
335 ATTRIBUTE12,
336 ATTRIBUTE13,
337 ATTRIBUTE14,
338 ATTRIBUTE15,
339 OBJECT_VERSION_NUMBER,
340 CSI_ITEM_INSTANCE_ID,
341 MR_HEADER_ID,
342 MR_EFFECTIVITY_ID,
343 MR_INTERVAL_ID,
344 STATUS_CODE,
345 DUE_DATE,
346 DUE_COUNTER_VALUE,
347 DEFER_FROM_UE_ID,
348 ORIG_DEFERRAL_UE_ID
349 from AHL_UNIT_EFFECTIVITIES_B
350 where UNIT_EFFECTIVITY_ID = X_UNIT_EFFECTIVITY_ID
351 for update of UNIT_EFFECTIVITY_ID nowait;
352 recinfo c%rowtype;
353
354 cursor c1 is select
355 REMARKS,
356 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
357 from AHL_UNIT_EFFECTIVITIES_TL
358 where UNIT_EFFECTIVITY_ID = X_UNIT_EFFECTIVITY_ID
359 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
360 for update of UNIT_EFFECTIVITY_ID nowait;
361 begin
362 open c;
363 fetch c into recinfo;
364 if (c%notfound) then
365 close c;
366 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
367 app_exception.raise_exception;
368 end if;
369 close c;
370 if ( ((recinfo.MANUALLY_PLANNED_FLAG = X_MANUALLY_PLANNED_FLAG)
371 OR ((recinfo.MANUALLY_PLANNED_FLAG is null) AND (X_MANUALLY_PLANNED_FLAG is null)))
372 AND ((recinfo.LOG_SERIES_CODE = X_LOG_SERIES_CODE)
373 OR ((recinfo.LOG_SERIES_CODE is null) AND (X_LOG_SERIES_CODE is null)))
374 AND ((recinfo.LOG_SERIES_NUMBER = X_LOG_SERIES_NUMBER)
378 AND ((recinfo.MEL_CDL_TYPE_CODE = X_MEL_CDL_TYPE_CODE)
375 OR ((recinfo.LOG_SERIES_NUMBER is null) AND (X_LOG_SERIES_NUMBER is null)))
376 AND ((recinfo.FLIGHT_NUMBER = X_FLIGHT_NUMBER)
377 OR ((recinfo.FLIGHT_NUMBER is null) AND (X_FLIGHT_NUMBER is null)))
379 OR ((recinfo.MEL_CDL_TYPE_CODE is null) AND (X_MEL_CDL_TYPE_CODE is null)))
380 AND ((recinfo.POSITION_PATH_ID = X_POSITION_PATH_ID)
381 OR ((recinfo.POSITION_PATH_ID is null) AND (X_POSITION_PATH_ID is null)))
382 AND ((recinfo.ATA_CODE = X_ATA_CODE)
383 OR ((recinfo.ATA_CODE is null) AND (X_ATA_CODE is null)))
384 --AND ((recinfo.CLEAR_STATION_ORG_ID = X_CLEAR_STATION_ORG_ID)
385 -- OR ((recinfo.CLEAR_STATION_ORG_ID is null) AND (X_CLEAR_STATION_ORG_ID is null)))
386 --AND ((recinfo.CLEAR_STATION_DEPT_ID = X_CLEAR_STATION_DEPT_ID)
387 -- OR ((recinfo.CLEAR_STATION_DEPT_ID is null) AND (X_CLEAR_STATION_DEPT_ID is null)))
388 AND ((recinfo.UNIT_CONFIG_HEADER_ID = X_UNIT_CONFIG_HEADER_ID)
389 OR ((recinfo.UNIT_CONFIG_HEADER_ID is null) AND (X_UNIT_CONFIG_HEADER_ID is null)))
390 AND ((recinfo.QA_COLLECTION_ID = X_QA_COLLECTION_ID)
391 OR ((recinfo.QA_COLLECTION_ID is null) AND (X_QA_COLLECTION_ID is null)))
392 AND ((recinfo.CS_INCIDENT_ID = X_CS_INCIDENT_ID)
393 OR ((recinfo.CS_INCIDENT_ID is null) AND (X_CS_INCIDENT_ID is null)))
394 AND ((recinfo.OBJECT_TYPE = X_OBJECT_TYPE)
395 OR ((recinfo.OBJECT_TYPE is null) AND (X_OBJECT_TYPE is null)))
396 AND ((recinfo.APPLICATION_USG_CODE = X_APPLICATION_USG_CODE)
397 OR ((recinfo.APPLICATION_USG_CODE is null) AND (X_APPLICATION_USG_CODE is null)))
398 AND ((recinfo.COUNTER_ID = X_COUNTER_ID)
399 OR ((recinfo.COUNTER_ID is null) AND (X_COUNTER_ID is null)))
400 AND ((recinfo.EARLIEST_DUE_DATE = X_EARLIEST_DUE_DATE)
401 OR ((recinfo.EARLIEST_DUE_DATE is null) AND (X_EARLIEST_DUE_DATE is null)))
402 AND ((recinfo.LATEST_DUE_DATE = X_LATEST_DUE_DATE)
403 OR ((recinfo.LATEST_DUE_DATE is null) AND (X_LATEST_DUE_DATE is null)))
404 AND ((recinfo.FORECAST_SEQUENCE = X_FORECAST_SEQUENCE)
405 OR ((recinfo.FORECAST_SEQUENCE is null) AND (X_FORECAST_SEQUENCE is null)))
406 AND ((recinfo.REPETITIVE_MR_FLAG = X_REPETITIVE_MR_FLAG)
407 OR ((recinfo.REPETITIVE_MR_FLAG is null) AND (X_REPETITIVE_MR_FLAG is null)))
408 AND ((recinfo.TOLERANCE_FLAG = X_TOLERANCE_FLAG)
409 OR ((recinfo.TOLERANCE_FLAG is null) AND (X_TOLERANCE_FLAG is null)))
410 AND ((recinfo.MESSAGE_CODE = X_MESSAGE_CODE)
411 OR ((recinfo.MESSAGE_CODE is null) AND (X_MESSAGE_CODE is null)))
412 AND ((recinfo.DATE_RUN = X_DATE_RUN)
413 OR ((recinfo.DATE_RUN is null) AND (X_DATE_RUN is null)))
414 AND ((recinfo.PRECEDING_UE_ID = X_PRECEDING_UE_ID)
415 OR ((recinfo.PRECEDING_UE_ID is null) AND (X_PRECEDING_UE_ID is null)))
416 AND ((recinfo.SET_DUE_DATE = X_SET_DUE_DATE)
417 OR ((recinfo.SET_DUE_DATE is null) AND (X_SET_DUE_DATE is null)))
418 AND ((recinfo.ACCOMPLISHED_DATE = X_ACCOMPLISHED_DATE)
419 OR ((recinfo.ACCOMPLISHED_DATE is null) AND (X_ACCOMPLISHED_DATE is null)))
420 AND ((recinfo.SERVICE_LINE_ID = X_SERVICE_LINE_ID)
421 OR ((recinfo.SERVICE_LINE_ID is null) AND (X_SERVICE_LINE_ID is null)))
422 AND ((recinfo.PROGRAM_MR_HEADER_ID = X_PROGRAM_MR_HEADER_ID)
423 OR ((recinfo.PROGRAM_MR_HEADER_ID is null) AND (X_PROGRAM_MR_HEADER_ID is null)))
424 AND ((recinfo.CANCEL_REASON_CODE = X_CANCEL_REASON_CODE)
425 OR ((recinfo.CANCEL_REASON_CODE is null) AND (X_CANCEL_REASON_CODE is null)))
426 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
427 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
428 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
429 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
430 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
431 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
432 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
433 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
434 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
435 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
436 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
437 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
438 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
439 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
440 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
441 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
442 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
443 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
444 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
445 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
446 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
447 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
448 AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
449 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
450 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
451 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
452 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
453 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
454 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
455 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
456 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
457 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
458 AND (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
459 AND (recinfo.CSI_ITEM_INSTANCE_ID = X_CSI_ITEM_INSTANCE_ID)
460 AND ((recinfo.MR_HEADER_ID = X_MR_HEADER_ID)
461 OR ((recinfo.MR_HEADER_ID is null) AND (X_MR_HEADER_ID is null)))
462 AND ((recinfo.MR_EFFECTIVITY_ID = X_MR_EFFECTIVITY_ID)
466 AND ((recinfo.STATUS_CODE = X_STATUS_CODE)
463 OR ((recinfo.MR_EFFECTIVITY_ID is null) AND (X_MR_EFFECTIVITY_ID is null)))
464 AND ((recinfo.MR_INTERVAL_ID = X_MR_INTERVAL_ID)
465 OR ((recinfo.MR_INTERVAL_ID is null) AND (X_MR_INTERVAL_ID is null)))
467 OR ((recinfo.STATUS_CODE is null) AND (X_STATUS_CODE is null)))
468 AND ((recinfo.DUE_DATE = X_DUE_DATE)
469 OR ((recinfo.DUE_DATE is null) AND (X_DUE_DATE is null)))
470 AND ((recinfo.DUE_COUNTER_VALUE = X_DUE_COUNTER_VALUE)
471 OR ((recinfo.DUE_COUNTER_VALUE is null) AND (X_DUE_COUNTER_VALUE is null)))
472 AND ((recinfo.DEFER_FROM_UE_ID = X_DEFER_FROM_UE_ID)
473 OR ((recinfo.DEFER_FROM_UE_ID is null) AND (X_DEFER_FROM_UE_ID is null)))
474 AND ((recinfo.ORIG_DEFERRAL_UE_ID = X_ORIG_DEFERRAL_UE_ID)
475 OR ((recinfo.ORIG_DEFERRAL_UE_ID is null) AND (X_ORIG_DEFERRAL_UE_ID is null)))
476 ) then
477 null;
478 else
479 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
480 app_exception.raise_exception;
481 end if;
482
483 for tlinfo in c1 loop
484 if (tlinfo.BASELANG = 'Y') then
485 if ( ((tlinfo.REMARKS = X_REMARKS)
486 OR ((tlinfo.REMARKS is null) AND (X_REMARKS is null)))
487 ) then
488 null;
489 else
490 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
491 app_exception.raise_exception;
492 end if;
493 end if;
494 end loop;
495 return;
496 end LOCK_ROW;
497
498 procedure UPDATE_ROW (
499 X_UNIT_EFFECTIVITY_ID in NUMBER,
500 X_MANUALLY_PLANNED_FLAG in VARCHAR2,
501 X_LOG_SERIES_CODE in VARCHAR2,
502 X_LOG_SERIES_NUMBER in NUMBER,
503 X_FLIGHT_NUMBER in VARCHAR2,
504 X_MEL_CDL_TYPE_CODE in VARCHAR2,
505 X_POSITION_PATH_ID in NUMBER,
506 X_ATA_CODE in VARCHAR2,
507 --X_CLEAR_STATION_ORG_ID in NUMBER,
508 --X_CLEAR_STATION_DEPT_ID in NUMBER,
509 X_UNIT_CONFIG_HEADER_ID in NUMBER,
510 X_QA_COLLECTION_ID in NUMBER,
511 X_CS_INCIDENT_ID in NUMBER,
512 X_OBJECT_TYPE in VARCHAR2,
513 X_APPLICATION_USG_CODE in VARCHAR2,
514 X_COUNTER_ID in NUMBER,
515 X_EARLIEST_DUE_DATE in DATE,
516 X_LATEST_DUE_DATE in DATE,
517 X_FORECAST_SEQUENCE in NUMBER,
518 X_REPETITIVE_MR_FLAG in VARCHAR2,
519 X_TOLERANCE_FLAG in VARCHAR2,
520 X_MESSAGE_CODE in VARCHAR2,
521 X_DATE_RUN in DATE,
522 X_PRECEDING_UE_ID in NUMBER,
523 X_SET_DUE_DATE in DATE,
524 X_ACCOMPLISHED_DATE in DATE,
525 X_SERVICE_LINE_ID in NUMBER,
526 X_PROGRAM_MR_HEADER_ID in NUMBER,
527 X_CANCEL_REASON_CODE in VARCHAR2,
528 X_ATTRIBUTE_CATEGORY in VARCHAR2,
529 X_ATTRIBUTE1 in VARCHAR2,
530 X_ATTRIBUTE2 in VARCHAR2,
531 X_ATTRIBUTE3 in VARCHAR2,
532 X_ATTRIBUTE4 in VARCHAR2,
533 X_ATTRIBUTE5 in VARCHAR2,
534 X_ATTRIBUTE6 in VARCHAR2,
535 X_ATTRIBUTE7 in VARCHAR2,
536 X_ATTRIBUTE8 in VARCHAR2,
537 X_ATTRIBUTE9 in VARCHAR2,
538 X_ATTRIBUTE10 in VARCHAR2,
539 X_ATTRIBUTE11 in VARCHAR2,
540 X_ATTRIBUTE12 in VARCHAR2,
541 X_ATTRIBUTE13 in VARCHAR2,
542 X_ATTRIBUTE14 in VARCHAR2,
543 X_ATTRIBUTE15 in VARCHAR2,
544 X_OBJECT_VERSION_NUMBER in NUMBER,
545 X_CSI_ITEM_INSTANCE_ID in NUMBER,
546 X_MR_HEADER_ID in NUMBER,
547 X_MR_EFFECTIVITY_ID in NUMBER,
548 X_MR_INTERVAL_ID in NUMBER,
549 X_STATUS_CODE in VARCHAR2,
550 X_DUE_DATE in DATE,
551 X_DUE_COUNTER_VALUE in NUMBER,
552 X_DEFER_FROM_UE_ID in NUMBER,
553 X_ORIG_DEFERRAL_UE_ID in NUMBER,
554 X_REMARKS in VARCHAR2,
555 X_LAST_UPDATE_DATE in DATE,
556 X_LAST_UPDATED_BY in NUMBER,
557 X_LAST_UPDATE_LOGIN in NUMBER,
558 X_FLEET_HEADER_ID IN NUMBER := NULL
559 ) is
560 begin
561 update AHL_UNIT_EFFECTIVITIES_B set
562 MANUALLY_PLANNED_FLAG = X_MANUALLY_PLANNED_FLAG,
563 LOG_SERIES_CODE = X_LOG_SERIES_CODE,
564 LOG_SERIES_NUMBER = X_LOG_SERIES_NUMBER,
565 FLIGHT_NUMBER = X_FLIGHT_NUMBER,
566 MEL_CDL_TYPE_CODE = X_MEL_CDL_TYPE_CODE,
567 POSITION_PATH_ID = X_POSITION_PATH_ID,
568 ATA_CODE = X_ATA_CODE,
569 --CLEAR_STATION_ORG_ID = X_CLEAR_STATION_ORG_ID,
570 --CLEAR_STATION_DEPT_ID = X_CLEAR_STATION_DEPT_ID,
571 UNIT_CONFIG_HEADER_ID = X_UNIT_CONFIG_HEADER_ID,
572 QA_COLLECTION_ID = X_QA_COLLECTION_ID,
573 CS_INCIDENT_ID = X_CS_INCIDENT_ID,
574 OBJECT_TYPE = X_OBJECT_TYPE,
575 APPLICATION_USG_CODE = X_APPLICATION_USG_CODE,
576 COUNTER_ID = X_COUNTER_ID,
577 EARLIEST_DUE_DATE = X_EARLIEST_DUE_DATE,
578 LATEST_DUE_DATE = X_LATEST_DUE_DATE,
579 FORECAST_SEQUENCE = X_FORECAST_SEQUENCE,
580 REPETITIVE_MR_FLAG = X_REPETITIVE_MR_FLAG,
581 TOLERANCE_FLAG = X_TOLERANCE_FLAG,
582 MESSAGE_CODE = X_MESSAGE_CODE,
583 DATE_RUN = X_DATE_RUN,
584 PRECEDING_UE_ID = X_PRECEDING_UE_ID,
585 SET_DUE_DATE = X_SET_DUE_DATE,
586 ACCOMPLISHED_DATE = X_ACCOMPLISHED_DATE,
587 SERVICE_LINE_ID = X_SERVICE_LINE_ID,
588 PROGRAM_MR_HEADER_ID = X_PROGRAM_MR_HEADER_ID,
589 CANCEL_REASON_CODE = X_CANCEL_REASON_CODE,
590 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
591 ATTRIBUTE1 = X_ATTRIBUTE1,
592 ATTRIBUTE2 = X_ATTRIBUTE2,
593 ATTRIBUTE3 = X_ATTRIBUTE3,
594 ATTRIBUTE4 = X_ATTRIBUTE4,
595 ATTRIBUTE5 = X_ATTRIBUTE5,
596 ATTRIBUTE6 = X_ATTRIBUTE6,
597 ATTRIBUTE7 = X_ATTRIBUTE7,
598 ATTRIBUTE8 = X_ATTRIBUTE8,
599 ATTRIBUTE9 = X_ATTRIBUTE9,
600 ATTRIBUTE10 = X_ATTRIBUTE10,
601 ATTRIBUTE11 = X_ATTRIBUTE11,
602 ATTRIBUTE12 = X_ATTRIBUTE12,
603 ATTRIBUTE13 = X_ATTRIBUTE13,
604 ATTRIBUTE14 = X_ATTRIBUTE14,
605 ATTRIBUTE15 = X_ATTRIBUTE15,
606 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
610 MR_INTERVAL_ID = X_MR_INTERVAL_ID,
607 CSI_ITEM_INSTANCE_ID = X_CSI_ITEM_INSTANCE_ID,
608 MR_HEADER_ID = X_MR_HEADER_ID,
609 MR_EFFECTIVITY_ID = X_MR_EFFECTIVITY_ID,
611 STATUS_CODE = X_STATUS_CODE,
612 DUE_DATE = X_DUE_DATE,
613 DUE_COUNTER_VALUE = X_DUE_COUNTER_VALUE,
614 DEFER_FROM_UE_ID = X_DEFER_FROM_UE_ID,
615 ORIG_DEFERRAL_UE_ID = X_ORIG_DEFERRAL_UE_ID,
616 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
617 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
618 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
619 FLEET_HEADER_ID = X_FLEET_HEADER_ID
620 where UNIT_EFFECTIVITY_ID = X_UNIT_EFFECTIVITY_ID;
621
622 if (sql%notfound) then
623 raise no_data_found;
624 end if;
625
626 update AHL_UNIT_EFFECTIVITIES_TL set
627 REMARKS = X_REMARKS,
628 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
629 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
630 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
631 SOURCE_LANG = userenv('LANG')
632 where UNIT_EFFECTIVITY_ID = X_UNIT_EFFECTIVITY_ID
633 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
634
635 if (sql%notfound) then
636 raise no_data_found;
637 end if;
638 end UPDATE_ROW;
639
640 procedure DELETE_ROW (
641 X_UNIT_EFFECTIVITY_ID in NUMBER
642 ) is
643 begin
644 delete from AHL_UNIT_EFFECTIVITIES_TL
645 where UNIT_EFFECTIVITY_ID = X_UNIT_EFFECTIVITY_ID;
646
647 if (sql%notfound) then
648 raise no_data_found;
649 end if;
650
651 delete from AHL_UNIT_EFFECTIVITIES_B
652 where UNIT_EFFECTIVITY_ID = X_UNIT_EFFECTIVITY_ID;
653
654 if (sql%notfound) then
655 raise no_data_found;
656 end if;
657 end DELETE_ROW;
658
659 procedure ADD_LANGUAGE
660 is
661 begin
662 delete from AHL_UNIT_EFFECTIVITIES_TL T
663 where not exists
664 (select NULL
665 from AHL_UNIT_EFFECTIVITIES_B B
666 where B.UNIT_EFFECTIVITY_ID = T.UNIT_EFFECTIVITY_ID
667 );
668
669 update AHL_UNIT_EFFECTIVITIES_TL T set (
670 REMARKS
671 ) = (select
672 B.REMARKS
673 from AHL_UNIT_EFFECTIVITIES_TL B
674 where B.UNIT_EFFECTIVITY_ID = T.UNIT_EFFECTIVITY_ID
675 and B.LANGUAGE = T.SOURCE_LANG)
676 where (
677 T.UNIT_EFFECTIVITY_ID,
678 T.LANGUAGE
679 ) in (select
680 SUBT.UNIT_EFFECTIVITY_ID,
681 SUBT.LANGUAGE
682 from AHL_UNIT_EFFECTIVITIES_TL SUBB, AHL_UNIT_EFFECTIVITIES_TL SUBT
683 where SUBB.UNIT_EFFECTIVITY_ID = SUBT.UNIT_EFFECTIVITY_ID
684 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
685 and (SUBB.REMARKS <> SUBT.REMARKS
686 or (SUBB.REMARKS is null and SUBT.REMARKS is not null)
687 or (SUBB.REMARKS is not null and SUBT.REMARKS is null)
688 ));
689
690 insert into AHL_UNIT_EFFECTIVITIES_TL (
691 UNIT_EFFECTIVITY_ID,
692 LAST_UPDATE_DATE,
693 LAST_UPDATED_BY,
694 CREATION_DATE,
695 CREATED_BY,
696 LAST_UPDATE_LOGIN,
697 REMARKS,
698 LANGUAGE,
699 SOURCE_LANG
700 ) select
701 B.UNIT_EFFECTIVITY_ID,
702 B.LAST_UPDATE_DATE,
703 B.LAST_UPDATED_BY,
704 B.CREATION_DATE,
705 B.CREATED_BY,
706 B.LAST_UPDATE_LOGIN,
707 B.REMARKS,
708 L.LANGUAGE_CODE,
709 B.SOURCE_LANG
710 from AHL_UNIT_EFFECTIVITIES_TL B, FND_LANGUAGES L
711 where L.INSTALLED_FLAG in ('I', 'B')
712 and B.LANGUAGE = userenv('LANG')
713 and not exists
714 (select NULL
715 from AHL_UNIT_EFFECTIVITIES_TL T
716 where T.UNIT_EFFECTIVITY_ID = B.UNIT_EFFECTIVITY_ID
717 and T.LANGUAGE = L.LANGUAGE_CODE);
718 end ADD_LANGUAGE;
719
720 end AHL_UNIT_EFFECTIVITIES_PKG;
[Home] [Help]