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