[Home] [Help]
PACKAGE BODY: APPS.AHL_UMP_PROCESSUNIT_EXTN_PVT
Source
1 PACKAGE BODY AHL_UMP_PROCESSUNIT_EXTN_PVT AS
2 /* $Header: AHLVUMEB.pls 120.5.12010000.2 2008/12/27 01:01:31 sracha ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_UMP_ProcessUnit_EXTN_PVT';
5 G_APPLN_USAGE_CODE CONSTANT VARCHAR2(30) := LTRIM(RTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE')));
6 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
7
8 ------------------------------
9 -- Declare Local Procedures --
10 ------------------------------
11
12 -- To create ahl_unit_effectivities record.
13 PROCEDURE create_record (p_x_temp_mr_rec IN OUT NOCOPY ahl_temp_unit_effectivities%ROWTYPE);
14
15
16 -- To update ahl_unit_effectivities record.
17 PROCEDURE update_record (p_temp_mr_rec IN ahl_temp_unit_effectivities%ROWTYPE,
18 p_mr_rec IN ahl_unit_effectivities_app_v%ROWTYPE);
19
20 -- To create decendent records and build relationships if the MR is a group MR.
21 PROCEDURE create_group (p_x_temp_grp_rec IN OUT NOCOPY ahl_temp_unit_effectivities%ROWTYPE);
22
23 -- To update decendent records and update relationships if the MR is a group MR.
24 PROCEDURE update_group (p_temp_mr_rec IN ahl_temp_unit_effectivities%ROWTYPE,
25 p_mr_rec IN ahl_unit_effectivities_app_v%ROWTYPE);
26
27 -- Process deferral and SR records.
28 -- Added for 11.5.10.
29 PROCEDURE Flush_Unit_SR_Deferrals;
30
31
32 -- Procedure to delete rows from ahl_schedule_materials.
33 PROCEDURE Delete_Sch_Materials(p_unit_effectivity_id IN NUMBER);
34
35
36 ------------------------------
37 -- Define Procedures --
38 ------------------------------
39
40 -- To flush the unit effectivities created in the temporary table (ahl_temp_unit_effectivities)
41 -- by AHL_UMP_PROCESSUNIT_PVT.Process_Unit into ahl_unit_effectivities and ahl_ue_relationships.
42
43 PROCEDURE Flush_From_Temp_table (p_config_node_tbl IN AHL_UMP_PROCESSUNIT_PVT.config_node_tbl_type)
44
45 IS
46
47 -- get individual MRs for item instance and top group records
48 -- from temporary table.
49 CURSOR temp_individual_mrs_csr IS
50 SELECT unit_effectivity_id,
51 csi_item_instance_id,
52 MR_header_id,
53 due_date,
54 mr_interval_id,
55 mr_effectivity_id,
56 due_counter_value,
57 parent_csi_item_instance_id,
58 parent_mr_header_id,
59 orig_csi_item_instance_id,
60 orig_mr_header_id,
61 forecast_sequence,
62 repetitive_mr_flag,
63 tolerance_flag,
64 message_code,
65 service_line_id,
66 program_mr_header_id,
67 earliest_due_date,
68 latest_due_date,
69 counter_id
70 FROM ahl_temp_unit_effectivities
71 WHERE MR_header_id = nvl(orig_mr_header_id, mr_header_id) AND
72 csi_item_instance_id = nvl(orig_csi_item_instance_id,csi_item_instance_id)
73 ORDER by forecast_sequence ASC
74 FOR UPDATE OF unit_effectivity_id;
75
76 -- Cursor for getting all temp unit effectivities that have preceding MRs.
77 CURSOR dependent_mr_csr IS
78 SELECT preceding_mr_header_id, preceding_csi_item_instance_id,
79 preceding_forecast_seq, unit_effectivity_id
80 FROM ahl_temp_unit_effectivities
81 WHERE preceding_mr_header_id IS NOT NULL AND
82 preceding_csi_item_instance_id IS NOT NULL AND
83 preceding_forecast_seq IS NOT NULL;
84
85 -- Cursor for getting the preceding MR unit effectivity from temporary table.
86 CURSOR preceding_mr_csr (p_preceding_mr_header_id IN NUMBER,
87 p_preceding_item_instance IN NUMBER,
88 p_preceding_forecast_seq IN NUMBER) IS
89 SELECT unit_effectivity_id
90 FROM ahl_temp_unit_effectivities
91 WHERE mr_header_id = p_preceding_mr_header_id AND
92 csi_item_instance_id = p_preceding_item_instance AND
93 forecast_sequence = p_preceding_forecast_seq;
94
95
96 -- Cursor to get all details of a unit effectivity record.
97 CURSOR ahl_unit_effectivity_csr ( p_unit_effectivity_id IN NUMBER) IS
98 SELECT
99 UNIT_EFFECTIVITY_ID ,
100 CSI_ITEM_INSTANCE_ID,
101 MR_INTERVAL_ID,
102 MR_EFFECTIVITY_ID ,
103 MR_HEADER_ID,
104 STATUS_CODE ,
105 DUE_DATE ,
106 DUE_COUNTER_VALUE ,
107 FORECAST_SEQUENCE ,
108 REPETITIVE_MR_FLAG ,
109 TOLERANCE_FLAG ,
110 REMARKS ,
111 MESSAGE_CODE ,
112 PRECEDING_UE_ID ,
113 DATE_RUN ,
114 SET_DUE_DATE ,
115 ACCOMPLISHED_DATE ,
116 SERVICE_LINE_ID,
117 PROGRAM_MR_HEADER_ID,
118 CANCEL_REASON_CODE,
119 EARLIEST_DUE_DATE,
120 LATEST_DUE_DATE,
121 defer_from_ue_id,
122 cs_incident_id,
123 qa_collection_id,
124 orig_deferral_ue_id,
125 application_usg_code,
126 object_type,
127 counter_id,
128 manually_planned_flag,
129 LOG_SERIES_CODE,
130 LOG_SERIES_NUMBER,
131 FLIGHT_NUMBER,
132 MEL_CDL_TYPE_CODE,
133 POSITION_PATH_ID,
134 ATA_CODE,
135 UNIT_CONFIG_HEADER_ID,
136 ATTRIBUTE_CATEGORY ,
137 ATTRIBUTE1,
138 ATTRIBUTE2 ,
139 ATTRIBUTE3 ,
140 ATTRIBUTE4 ,
141 ATTRIBUTE5 ,
142 ATTRIBUTE6 ,
143 ATTRIBUTE7 ,
144 ATTRIBUTE8 ,
145 ATTRIBUTE9 ,
146 ATTRIBUTE10,
147 ATTRIBUTE11 ,
148 ATTRIBUTE12 ,
149 ATTRIBUTE13 ,
150 ATTRIBUTE14 ,
151 ATTRIBUTE15 ,
152 OBJECT_VERSION_NUMBER ,
153 LAST_UPDATE_DATE ,
154 LAST_UPDATED_BY ,
155 LAST_UPDATE_LOGIN
156
157 --FROM ahl_unit_effectivities_app_v
158 FROM ahl_unit_effectivities_vl
159 WHERE unit_effectivity_id = p_unit_effectivity_id;
160 --FOR UPDATE OF preceding_ue_id, status_code NOWAIT; -- not required as these rows were locked before.
161
162 -- Cursor for reading unit effectivity records(top nodes) that have not been updated.
163 CURSOR ahl_exception_csr (p_csi_item_instance_id IN NUMBER,
164 p_date_run IN DATE) IS
165 SELECT
166 UNIT_EFFECTIVITY_ID,
167 CSI_ITEM_INSTANCE_ID,
168 MR_HEADER_ID,
169 STATUS_CODE,
170 MR_INTERVAL_ID,
171 MR_EFFECTIVITY_ID ,
172 DUE_DATE ,
173 DUE_COUNTER_VALUE ,
174 FORECAST_SEQUENCE ,
175 REPETITIVE_MR_FLAG ,
176 TOLERANCE_FLAG ,
177 REMARKS ,
178 MESSAGE_CODE ,
179 PRECEDING_UE_ID ,
180 DATE_RUN ,
181 SET_DUE_DATE ,
182 ACCOMPLISHED_DATE ,
183 SERVICE_LINE_ID,
184 PROGRAM_MR_HEADER_ID,
185 CANCEL_REASON_CODE,
186 EARLIEST_DUE_DATE,
187 LATEST_DUE_DATE,
188 defer_from_ue_id,
189 cs_incident_id,
190 qa_collection_id,
191 orig_deferral_ue_id,
192 application_usg_code,
193 object_type,
194 counter_id,
195 manually_planned_flag,
196 LOG_SERIES_CODE,
197 LOG_SERIES_NUMBER,
198 FLIGHT_NUMBER,
199 MEL_CDL_TYPE_CODE,
200 POSITION_PATH_ID,
201 ATA_CODE,
202 UNIT_CONFIG_HEADER_ID,
203 ATTRIBUTE_CATEGORY ,
204 ATTRIBUTE1,
205 ATTRIBUTE2 ,
206 ATTRIBUTE3 ,
207 ATTRIBUTE4 ,
208 ATTRIBUTE5 ,
209 ATTRIBUTE6 ,
210 ATTRIBUTE7 ,
211 ATTRIBUTE8 ,
212 ATTRIBUTE9 ,
213 ATTRIBUTE10,
214 ATTRIBUTE11 ,
215 ATTRIBUTE12 ,
216 ATTRIBUTE13 ,
217 ATTRIBUTE14 ,
218 ATTRIBUTE15 ,
219 OBJECT_VERSION_NUMBER
220
221 FROM ahl_unit_effectivities_app_v ue
222 WHERE csi_item_instance_id = p_csi_item_instance_id AND
223 ( status_code IS NULL OR
224 status_code NOT IN ('ACCOMPLISHED','TERMINATED',
225 'MR-TERMINATE', 'INIT-ACCOMPLISHED',
226 'DEFERRED', 'SR-CLOSED','CANCELLED'))
227 AND date_run < p_date_run
228 --AND defer_from_ue_id IS NULL
229 AND nvl(manually_planned_flag,'N') = 'N'
230 AND NOT EXISTS (SELECT 'x'
231 FROM ahl_ue_relationships
232 WHERE related_ue_id = ue.unit_effectivity_id);
233 -- FOR UPDATE OF message_code NOWAIT;
234 -- UMP rows are already locked by ahl_ump_processunit_pvt.lock_effectivity
235 -- proc.
236
237 -- Cursor to get all decendents for a UE.
238 CURSOR decendent_csr (p_unit_effectivity_id IN NUMBER) IS
239 SELECT ue_relationship_id, related_ue_id
240 FROM ahl_ue_relationships
241 WHERE relationship_code = 'PARENT' AND
242 originator_ue_id = p_unit_effectivity_id;
243
244 -- Cursor to check if init-due excepion still valid.
245 CURSOR exception_init_due_csr (p_unit_effectivity_id IN NUMBER) IS
246 SELECT unit_deferral_id
247 --FROM ahl_unit_thresholds
248 FROM ahl_unit_deferrals_b
249 WHERE unit_effectivity_id = p_unit_effectivity_id
250 AND unit_deferral_type = 'INIT-DUE';
251
252 -- to check if ue has child ue records.
253 CURSOR chk_child_ue_csr (p_unit_effectivity_id IN NUMBER) IS
254 SELECT 'x'
255 FROM ahl_ue_relationships
256 WHERE ue_id = p_unit_effectivity_id;
257
258 l_visit_status_code VARCHAR2(40);
259
260 l_csi_item_instance_id NUMBER;
261 l_start_time DATE;
262
263 l_temp_individual_mr_rec temp_individual_mrs_csr%ROWTYPE;
264
265 l_temp_mr_rec ahl_temp_unit_effectivities%ROWTYPE;
266 l_mr_rec ahl_unit_effectivities_app_v%ROWTYPE;
267
268 l_temp_mr_found BOOLEAN;
269 l_mr_found BOOLEAN;
270
271 l_unit_effectivity_id NUMBER;
272 l_ue_rec ahl_unit_effectivity_csr%ROWTYPE;
273
274 l_last_accomplishment_date DATE;
275 l_acc_unit_effectivity_id NUMBER;
276 l_acc_status_code ahl_unit_effectivities_app_v.status_code%TYPE;
277 l_return_val BOOLEAN;
278
279 l_exception_upd_flag BOOLEAN;
280 l_delete_flag BOOLEAN;
281 l_exception_code fnd_lookups.lookup_code%TYPE;
282 l_unit_effectivity_rec ahl_unit_effectivity_csr%ROWTYPE;
283 l_junk VARCHAR2(1);
284
285 l_incident_id NUMBER;
286 l_incident_number CS_INCIDENTS_ALL_VL.incident_number%TYPE;
287 l_scheduled_date DATE;
288
289 -- Cursor to check whether contract for exiting UE is valid(bug#4692366)
290 CURSOR valid_contract_csr (p_unit_effectivity_id IN NUMBER) IS
291 SELECT 'x' from okc_k_lines_b OKCL, ahl_unit_effectivities_b UE
292 --WHERE NVL(NVL(DATE_TERMINATED,END_DATE),SYSDATE) >= SYSDATE
293 -- Fix for bug# 5517930
294 WHERE NVL(NVL(DATE_TERMINATED,END_DATE),SYSDATE+1) >= trunc(SYSDATE)
295 AND OKCL.id = UE.service_line_id
296 AND UE.unit_effectivity_id = p_unit_effectivity_id;
297
298 -- Added for bug fix 5764351).
299 -- delete UMP rows only if due date > termination date/end date.
300 CURSOR valid_due_date_csr (p_unit_effectivity_id IN NUMBER,
301 p_due_date IN DATE) IS
302 SELECT 'x'
303 FROM okc_k_lines_b OKCL, ahl_unit_effectivities_b UE
304 WHERE trunc(p_due_date) <= NVL(DATE_TERMINATED,END_DATE)
305 AND OKCL.id = UE.service_line_id
306 AND UE.unit_effectivity_id = p_unit_effectivity_id;
307
308 -- Fix for bug# 6711228.
309 l_acc_deferral_flag BOOLEAN;
310
311 l_unit_deferral_id NUMBER;
312
313 BEGIN
314
315 IF G_DEBUG = 'Y' THEN
316 AHL_DEBUG_PUB.Debug('Start Flush from Temporary Table');
317 END IF;
318
319 -- record start time.
320 l_start_time := sysdate;
321
322 -- Read temporary table.
323 FOR temp_individual_mr_rec IN temp_individual_mrs_csr LOOP
324
325 l_temp_individual_mr_rec := temp_individual_mr_rec;
326 IF (l_temp_individual_mr_rec.unit_effectivity_id is null) THEN /* no previous record */
327
328 l_mr_found := FALSE;
329 ELSE
330 OPEN ahl_unit_effectivity_csr (l_temp_individual_mr_rec.unit_effectivity_id);
331 -- get unit effectivity record details as we need to update it.
332 FETCH ahl_unit_effectivity_csr INTO l_ue_rec;
333 IF (ahl_unit_effectivity_csr%FOUND) THEN
334 l_mr_found := TRUE;
335 -- fix for bug number 4692366
336 IF (l_ue_rec.service_line_id IS NOT NULL) THEN
337 OPEN valid_contract_csr(l_temp_individual_mr_rec.unit_effectivity_id);
338 FETCH valid_contract_csr INTO l_junk;
339 IF(valid_contract_csr%NOTFOUND)THEN
340 l_mr_found := FALSE;
341 END IF;
342 CLOSE valid_contract_csr;
343 -- end of fix for bug number 4692366
344 ELSE
345 -- this check is not need for PM flow.
346 -- added this check for case where applicable MR is not a group but associated UE is a group MR.
347 -- in this case we create a new ue.
348 IF (l_temp_individual_mr_rec.orig_mr_header_id IS NULL) THEN
349 OPEN chk_child_ue_csr(l_temp_individual_mr_rec.unit_effectivity_id);
350 FETCH chk_child_ue_csr INTO l_junk;
351 IF (chk_child_ue_csr%FOUND) THEN
352 l_mr_found := FALSE;
353 END IF;
354 CLOSE chk_child_ue_csr;
355 END IF; -- l_temp_individual_mr_rec.orig_mr_header_id
356 END IF; -- l_ue_rec.service_line_id
357 ELSE
358 l_mr_found := FALSE;
359 END IF;
360 CLOSE ahl_unit_effectivity_csr;
361
362 END IF;
363
364 -- convert cursor rowtype to table rowtype.
365 l_temp_mr_rec.unit_effectivity_id := l_temp_individual_mr_rec.unit_effectivity_id;
366 l_temp_mr_rec.csi_item_instance_id := l_temp_individual_mr_rec.csi_item_instance_id;
367 l_temp_mr_rec.MR_HEADER_ID := l_temp_individual_mr_rec.MR_header_id;
368 l_temp_mr_rec.due_date := l_temp_individual_mr_rec.due_date;
369 l_temp_mr_rec.mr_interval_id := l_temp_individual_mr_rec.mr_interval_id;
370 l_temp_mr_rec.mr_effectivity_id := l_temp_individual_mr_rec.mr_effectivity_id;
371 l_temp_mr_rec.due_counter_value := l_temp_individual_mr_rec.due_counter_value;
372 l_temp_mr_rec.parent_csi_item_instance_id := l_temp_individual_mr_rec.parent_csi_item_instance_id;
373 l_temp_mr_rec.parent_mr_header_id := l_temp_individual_mr_rec.parent_mr_header_id;
374 l_temp_mr_rec.orig_csi_item_instance_id := l_temp_individual_mr_rec.orig_csi_item_instance_id;
375 l_temp_mr_rec.orig_mr_header_id := l_temp_individual_mr_rec.orig_mr_header_id;
376 l_temp_mr_rec.forecast_sequence := l_temp_individual_mr_rec.forecast_sequence;
377 l_temp_mr_rec.repetitive_mr_flag := l_temp_individual_mr_rec.repetitive_mr_flag;
378 l_temp_mr_rec.tolerance_flag := l_temp_individual_mr_rec.tolerance_flag;
379 l_temp_mr_rec.message_code := l_temp_individual_mr_rec.message_code;
380 l_temp_mr_rec.service_line_id := l_temp_individual_mr_rec.service_line_id;
381 l_temp_mr_rec.program_mr_header_id := l_temp_individual_mr_rec.program_mr_header_id;
382 l_temp_mr_rec.earliest_due_date := l_temp_individual_mr_rec.earliest_due_date;
383 l_temp_mr_rec.latest_due_date := l_temp_individual_mr_rec.latest_due_date;
384 l_temp_mr_rec.counter_id := l_temp_individual_mr_rec.counter_id;
385
386
387 IF (l_mr_found) THEN
388 -- convert cursor rowtype to table rowtype.
389 l_mr_rec.unit_effectivity_id := l_ue_rec.unit_effectivity_id;
390 l_mr_rec.csi_item_instance_id := l_ue_rec.csi_item_instance_id;
391 l_mr_rec.MR_header_id := l_ue_rec.MR_header_id ;
392 l_mr_rec.STATUS_CODE := l_ue_rec.STATUS_CODE ;
393 l_mr_rec.SET_DUE_DATE := l_ue_rec.SET_DUE_DATE;
394 l_mr_rec.ACCOMPLISHED_DATE := l_ue_rec.ACCOMPLISHED_DATE;
395 l_mr_rec.CANCEL_REASON_CODE := l_ue_rec.CANCEL_REASON_CODE;
396 l_mr_rec.defer_from_ue_id := l_ue_rec.defer_from_ue_id;
397 l_mr_rec.cs_incident_id := l_ue_rec.cs_incident_id;
398 l_mr_rec.qa_collection_id := l_ue_rec.qa_collection_id;
399 l_mr_rec.orig_deferral_ue_id := l_ue_rec.orig_deferral_ue_id;
400 l_mr_rec.application_usg_code := l_ue_rec.application_usg_code;
401 l_mr_rec.object_type := l_ue_rec.object_type;
402 --l_mr_rec.counter_id := l_ue_rec.counter_id;
403 l_mr_rec.ATTRIBUTE_CATEGORY := l_ue_rec.ATTRIBUTE_CATEGORY;
404 l_mr_rec.ATTRIBUTE1 := l_ue_rec.ATTRIBUTE1;
405 l_mr_rec.ATTRIBUTE2 := l_ue_rec.ATTRIBUTE2;
406 l_mr_rec.ATTRIBUTE3 := l_ue_rec.ATTRIBUTE3;
407 l_mr_rec.ATTRIBUTE4 := l_ue_rec.ATTRIBUTE4;
408 l_mr_rec.ATTRIBUTE5 := l_ue_rec.ATTRIBUTE5;
409 l_mr_rec.ATTRIBUTE6 := l_ue_rec.ATTRIBUTE6;
410 l_mr_rec.ATTRIBUTE7 := l_ue_rec.ATTRIBUTE7;
411 l_mr_rec.ATTRIBUTE8 := l_ue_rec.ATTRIBUTE8;
412 l_mr_rec.ATTRIBUTE9 := l_ue_rec.ATTRIBUTE9;
413 l_mr_rec.ATTRIBUTE10 := l_ue_rec.ATTRIBUTE10;
414 l_mr_rec.ATTRIBUTE11 := l_ue_rec.ATTRIBUTE11;
415 l_mr_rec.ATTRIBUTE12 := l_ue_rec.ATTRIBUTE12;
416 l_mr_rec.ATTRIBUTE13 := l_ue_rec.ATTRIBUTE13;
417 l_mr_rec.ATTRIBUTE14 := l_ue_rec.ATTRIBUTE14;
418 l_mr_rec.ATTRIBUTE15 := l_ue_rec.ATTRIBUTE15;
419 l_mr_rec.OBJECT_VERSION_NUMBER := l_ue_rec.OBJECT_VERSION_NUMBER;
420 l_mr_rec.REMARKS := l_ue_rec.REMARKS;
421 END IF;
422
423 -- Check if l_temp_individual_mr_rec is a group mr.
424
425 IF (l_temp_individual_mr_rec.orig_mr_header_id IS NOT NULL) THEN
426
427 IF (l_mr_found) THEN
428
429 Update_group(l_temp_mr_rec,
430 l_mr_rec);
431 ELSE
432
433 Create_group(l_temp_mr_rec);
434
435 END IF;
436 ELSE /* not a group */
437 IF (l_mr_found) THEN
438 -- update unit_effectivity record with temp details.
439
440 update_record(l_temp_mr_rec,
441 l_mr_rec);
442
443 ELSE
444
445 create_record(l_temp_mr_rec);
446 -- update unit effectivity ID.
447 UPDATE ahl_temp_unit_effectivities
448 SET unit_effectivity_id = l_temp_mr_rec.unit_effectivity_id
449 WHERE CURRENT OF temp_individual_mrs_csr;
450 END IF;
451 END IF;
452
453 END LOOP; /* for temp_individual_mrs_csr */
454
455 IF G_DEBUG = 'Y' THEN
456 AHL_DEBUG_PUB.Debug('End Process of all MRs from temp table');
457 END IF;
458
459 -- Now read ahl_unit_effectivities and update preceding_ue_id.
460 FOR dependent_rec IN dependent_mr_csr LOOP
461
462 OPEN preceding_mr_csr (dependent_rec.preceding_mr_header_id,
463 dependent_rec.preceding_csi_item_instance_id,
464 dependent_rec.preceding_forecast_seq);
465 FETCH preceding_mr_csr INTO l_unit_effectivity_id;
466
467 IF (preceding_mr_csr%NOTFOUND) THEN
468 -- check if accomplishment exists for preceding MR to get UE Id.
469 -- Fix for bug# 6711228.
470 AHL_UMP_UTIL_PKG.get_first_accomplishment(
471 dependent_rec.preceding_csi_item_instance_id,
472 dependent_rec.preceding_mr_header_id,
473 l_last_accomplishment_date,
474 l_acc_unit_effectivity_id,
475 l_acc_deferral_flag,
476 l_acc_status_code,
477 l_return_val);
478
479 IF (l_acc_unit_effectivity_id IS NOT NULL) THEN
480 l_unit_effectivity_id := l_acc_unit_effectivity_id;
481 ELSE
482 FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_PRECID_NOTFOUND');
483 FND_MESSAGE.Set_Token('UE_ID',dependent_rec.unit_effectivity_id);
484 FND_MSG_PUB.ADD;
485 -- dbms_output.put_line('preceding mr not found for dependent ue id in temporary table');
486 CLOSE preceding_mr_csr;
487 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
488 END IF;
489 END IF; -- preceding_mr_csr%NOTFOUND
490 CLOSE preceding_mr_csr;
491
492 OPEN ahl_unit_effectivity_csr (dependent_rec.unit_effectivity_id);
493 FETCH ahl_unit_effectivity_csr INTO l_ue_rec;
494 IF (ahl_unit_effectivity_csr%FOUND) THEN
495
496 AHL_UNIT_EFFECTIVITIES_PKG.Update_Row(
497 X_UNIT_EFFECTIVITY_ID => l_ue_rec.unit_effectivity_id,
498 X_CSI_ITEM_INSTANCE_ID => l_ue_rec.csi_item_instance_id,
499 X_MR_INTERVAL_ID => l_ue_rec.mr_interval_id,
500 X_MR_EFFECTIVITY_ID => l_ue_rec.mr_effectivity_id,
501 X_MR_HEADER_ID => l_ue_rec.mr_header_id,
502 X_STATUS_CODE => l_ue_rec.status_code,
503 X_DUE_DATE => l_ue_rec.due_date,
504 X_DUE_COUNTER_VALUE => l_ue_rec.due_counter_value,
505 X_FORECAST_SEQUENCE => l_ue_rec.forecast_sequence,
506 X_REPETITIVE_MR_FLAG => l_ue_rec.repetitive_mr_flag,
507 X_TOLERANCE_FLAG => l_ue_rec.tolerance_flag,
508 X_REMARKS => l_ue_rec.remarks,
509 X_MESSAGE_CODE => l_ue_rec.message_code,
510 X_PRECEDING_UE_ID => l_unit_effectivity_id,
511 X_DATE_RUN => l_ue_rec.date_run,
512 X_SET_DUE_DATE => l_ue_rec.set_due_date,
513 X_ACCOMPLISHED_DATE => l_ue_rec.accomplished_date,
514 X_SERVICE_LINE_ID => l_ue_rec.service_line_id,
515 X_PROGRAM_MR_HEADER_ID => l_ue_rec.program_mr_header_id,
516 X_CANCEL_REASON_CODE => l_ue_rec.cancel_reason_code,
517 X_EARLIEST_DUE_DATE => l_ue_rec.earliest_due_date,
518 X_LATEST_DUE_DATE => l_ue_rec.latest_due_date,
519 X_defer_from_ue_id => l_ue_rec.defer_from_ue_id,
520 X_cs_incident_id => l_ue_rec.cs_incident_id,
521 X_qa_collection_id => l_ue_rec.qa_collection_id,
522 X_orig_deferral_ue_id => l_ue_rec.orig_deferral_ue_id,
523 X_application_usg_code => l_ue_rec.application_usg_code,
524 X_object_type => l_ue_rec.object_type,
525 X_counter_id => l_ue_rec.counter_id,
526 X_MANUALLY_PLANNED_FLAG => l_ue_rec.MANUALLY_PLANNED_FLAG,
527 X_LOG_SERIES_CODE => l_ue_rec.log_series_code,
528 X_LOG_SERIES_NUMBER => l_ue_rec.log_series_number,
529 X_FLIGHT_NUMBER => l_ue_rec.flight_number,
530 X_MEL_CDL_TYPE_CODE => l_ue_rec.mel_cdl_type_code,
531 X_POSITION_PATH_ID => l_ue_rec.position_path_id,
532 X_ATA_CODE => l_ue_rec.ATA_CODE,
533 X_UNIT_CONFIG_HEADER_ID => l_ue_rec.unit_config_header_id,
534 X_ATTRIBUTE_CATEGORY => l_ue_rec.attribute_category,
535 X_ATTRIBUTE1 => l_ue_rec.attribute1,
536 X_ATTRIBUTE2 => l_ue_rec.attribute2,
537 X_ATTRIBUTE3 => l_ue_rec.attribute3,
538 X_ATTRIBUTE4 => l_ue_rec.attribute4,
539 X_ATTRIBUTE5 => l_ue_rec.attribute5,
540 X_ATTRIBUTE6 => l_ue_rec.attribute6,
541 X_ATTRIBUTE7 => l_ue_rec.attribute7,
542 X_ATTRIBUTE8 => l_ue_rec.attribute8,
543 X_ATTRIBUTE9 => l_ue_rec.attribute9,
544 X_ATTRIBUTE10 => l_ue_rec.attribute10,
545 X_ATTRIBUTE11 => l_ue_rec.attribute11,
546 X_ATTRIBUTE12 => l_ue_rec.attribute12,
547 X_ATTRIBUTE13 => l_ue_rec.attribute13,
548 X_ATTRIBUTE14 => l_ue_rec.attribute14,
549 X_ATTRIBUTE15 => l_ue_rec.attribute15,
550 X_OBJECT_VERSION_NUMBER => l_ue_rec.object_version_number, -- no change to this needed.
551 X_LAST_UPDATE_DATE => sysdate,
552 X_LAST_UPDATED_BY => fnd_global.user_id,
553 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
554 ELSE
555
556 FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_UE_NOTFOUND');
557 FND_MESSAGE.Set_Token('UE_ID',l_ue_rec.unit_effectivity_id);
558 FND_MSG_PUB.ADD;
559 -- dbms_output.put_line('preceding mr not found for dependent ue id');
560 ClOSE ahl_unit_effectivity_csr;
561 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
562 END IF;
563 ClOSE ahl_unit_effectivity_csr;
564
565 END LOOP;
566
567 IF G_DEBUG = 'Y' THEN
568 AHL_DEBUG_PUB.Debug('End Preceding MR processing');
569 AHL_DEBUG_PUB.Debug('Start Deferral-SR Processing');
570 END IF;
571
572 -- Process deferral and SR records.
573 Flush_Unit_SR_Deferrals;
574
575 IF G_DEBUG = 'Y' THEN
576 AHL_DEBUG_PUB.Debug('End Deferral-SR processing');
577 AHL_DEBUG_PUB.Debug('Start Exception Processing');
578 END IF;
579
580 -- Now read ahl_unit_effectivities
581 -- for records with date_run < l_startTime.
582
583 -- Read configuration table.
584 IF (p_config_node_tbl.COUNT > 0 ) THEN
585 FOR i IN p_config_node_tbl.FIRST..p_config_node_tbl.LAST LOOP
586 l_csi_item_instance_id := p_config_node_tbl(i).csi_item_instance_id;
587
588 FOR exception_rec IN ahl_exception_csr(l_csi_item_instance_id, l_start_time) LOOP
589
590 IF G_DEBUG = 'Y' THEN
591 AHL_DEBUG_PUB.Debug('Exception for unit effectivity ID:' || exception_rec.unit_effectivity_id);
592 END IF;
593
594 -- Initialize.
595 l_exception_upd_flag := FALSE;
596 l_delete_flag := TRUE;
597
598 /*
599 -- If status code is INIT-DUE mark as exception.
600 IF (exception_rec.status_code = 'INIT-DUE') THEN
601 l_visit_status_code := AHL_UMP_UTIL_PKG.get_visit_status (exception_rec.unit_effectivity_id);
602 IF G_DEBUG = 'Y' THEN
603 AHL_DEBUG_PUB.Debug('Visit Status Code from VWP is:' || l_visit_status_code);
604 END IF;
605
606 -- If visit is in released and closed status then, do not mark exception or deletion.
607 IF (l_visit_status_code IS NOT NULL AND l_visit_status_code = 'PLANNING') THEN
608 l_exception_upd_flag := TRUE;
609 l_exception_code := 'INIT-DUE';
610
611 IF G_DEBUG = 'Y' THEN
612 AHL_DEBUG_PUB.Debug('Exception - Init-Due');
613 END IF;
614 ELSIF (l_visit_status_code IS NULL) THEN -- not assigned to visit
615 l_delete_flag := TRUE;
616 ELSE -- on shop floor
617 -- do not update to exception status or delete.
618 l_exception_upd_flag := FALSE;
619 l_delete_flag := FALSE;
620 END IF;
621
622 ELSIF (exception_rec.status_code = 'INIT-ACCOMPLISHED') THEN
623 -- Check if accomplishments exist after init-accomplished.
624 AHL_UMP_UTIL_PKG.get_last_accomplishment(l_csi_item_instance_id,
625 exception_rec.mr_header_id,
626 l_last_accomplishment_date,
627 l_acc_unit_effectivity_id,
628 l_acc_status_code,
629 l_return_val);
630
631 IF (l_acc_unit_effectivity_id IS NULL OR
632 l_acc_unit_effectivity_id = exception_rec.unit_effectivity_id) THEN
633 l_exception_upd_flag := TRUE;
634 l_exception_code := 'INIT-ACCOMPLISHED';
635
636 IF G_DEBUG = 'Y' THEN
637 AHL_DEBUG_PUB.Debug('Exception - Init-Accomplished');
638 END IF;
639 END IF;
640
641 ELSIF (exception_rec.status_code = 'DEFERRED') THEN
642 l_exception_upd_flag := TRUE;
643 l_exception_code := 'DEFERRED';
644
645 IF G_DEBUG = 'Y' THEN
646 AHL_DEBUG_PUB.Debug('Exception - Deferred');
647 END IF;
648
649 ELSIF (exception_rec.status_code = 'EXCEPTION' AND
650 exception_rec.message_code = 'INIT-DUE'
651 -- OR exception_rec.message_code = 'DEFERRED')
652 ) THEN
653 -- Check if exception corrected.
654 IF (exception_rec.set_due_date IS NOT NULL) THEN
655 l_exception_upd_flag := TRUE;
656 l_exception_code := exception_rec.message_code;
657 ELSE
658 OPEN exception_init_due_csr (exception_rec.unit_effectivity_id);
659 FETCH exception_init_due_csr INTO l_junk;
660 IF (exception_init_due_csr%FOUND) THEN
661 l_exception_upd_flag := TRUE;
662 l_exception_code := exception_rec.message_code;
663 IF G_DEBUG = 'Y' THEN
664 AHL_DEBUG_PUB.Debug('Exception - Init-Due');
665 END IF;
666 END IF;
667 CLOSE exception_init_due_csr;
668 END IF;
669 END IF;
670 */
671
672 -- check if assigned to visit if not flagged for exception.
673 IF (AHL_UTIL_PKG.IS_PM_INSTALLED = 'N') THEN -- AHL Installation.
674 l_visit_status_code := AHL_UMP_UTIL_PKG.get_visit_status (exception_rec.unit_effectivity_id);
675 IF G_DEBUG = 'Y' THEN
676 AHL_DEBUG_PUB.Debug('Visit Status Code from VWP is:' || l_visit_status_code);
677 END IF;
678
679 -- If visit is in released and closed status then, do not mark exception or deletion.
680 IF (l_visit_status_code IS NOT NULL) THEN
681 IF (l_visit_status_code = 'PLANNING') THEN
682 IF (exception_rec.status_code = 'INIT-DUE') THEN
683 l_exception_upd_flag := TRUE;
684 l_exception_code := 'INIT-DUE';
685
686 IF G_DEBUG = 'Y' THEN
687 AHL_DEBUG_PUB.Debug('Exception - Init-Due');
688 END IF;
689
690 ELSE
691 l_exception_upd_flag := TRUE;
692 l_exception_code := 'VISIT-ASSIGN';
693 IF G_DEBUG = 'Y' THEN
694 AHL_DEBUG_PUB.Debug('Exception - Visit Assign');
695 END IF;
696 END IF;
697
698 ELSE
699 -- set no deletion if visit in released/closed status.
700 l_delete_flag := FALSE;
701 END IF;
702 ELSE
703 IF (exception_rec.defer_from_ue_id IS NULL) OR
704 (exception_rec.status_code = 'EXCEPTION' AND exception_rec.defer_from_ue_id IS NOT NULL) THEN
705 -- not assigned to any visit.
706 l_delete_flag := TRUE;
707 ELSE
708 -- do not delete deferrals not in exception status.
709 l_delete_flag := FALSE;
710 END IF;
711 END IF;
712 ELSE
713 -- PM Installation.
714 AHL_UMP_UTIL_PKG.get_ServiceRequest_Details(exception_rec.unit_effectivity_id,
715 l_incident_id,
716 l_incident_number,
717 l_scheduled_date);
718 IF G_DEBUG = 'Y' THEN
719 AHL_DEBUG_PUB.Debug('Service request ID-NUM is:' || l_incident_id || '-' || l_incident_number);
720 END IF;
721
722 IF (l_incident_id IS NOT NULL) THEN
723 l_delete_flag := FALSE;
724 ELSE
725 IF (exception_rec.due_date IS NOT NULL) THEN
726 -- check if associated service line ID has expired. If expired, do
727 -- not delete UMP row if due date <= contract termination date.
728 OPEN valid_due_date_csr(exception_rec.unit_effectivity_id,
729 exception_rec.due_date);
730 FETCH valid_due_date_csr INTO l_junk;
731 IF(valid_due_date_csr%FOUND)THEN
732 l_delete_flag := FALSE;
733 IF AHL_DEBUG_PUB.G_FILE_DEBUG THEN
734 AHL_DEBUG_PUB.Debug('Service Line Expired for UE with due date < termination/end date. Will not delete');
735 END IF;
736 END IF;
737 CLOSE valid_due_date_csr;
738 END IF; -- exception_rec.due_date IS NOT NULL
739 END IF; -- l_incident_id
740 END IF; -- AHL Installation.
741
742 IF (l_exception_upd_flag) THEN
743 IF G_DEBUG = 'Y' THEN
744 AHL_DEBUG_PUB.Debug('Updating exception code..');
745 END IF;
746
747 -- update unit effectivity.
748 exception_rec.message_code := l_exception_code;
749 exception_rec.status_code := 'EXCEPTION';
750
751 AHL_UNIT_EFFECTIVITIES_PKG.Update_Row(
752 X_UNIT_EFFECTIVITY_ID => exception_rec.unit_effectivity_id,
753 X_CSI_ITEM_INSTANCE_ID => exception_rec.csi_item_instance_id,
754 X_MR_INTERVAL_ID => exception_rec.mr_interval_id,
755 X_MR_EFFECTIVITY_ID => exception_rec.mr_effectivity_id,
756 X_MR_HEADER_ID => exception_rec.mr_header_id,
757 X_STATUS_CODE => exception_rec.status_code,
758 X_DUE_DATE => exception_rec.due_date,
759 X_DUE_COUNTER_VALUE => exception_rec.due_counter_value,
760 X_FORECAST_SEQUENCE => exception_rec.forecast_sequence,
761 X_REPETITIVE_MR_FLAG => exception_rec.repetitive_mr_flag,
762 X_TOLERANCE_FLAG => exception_rec.tolerance_flag,
763 X_REMARKS => exception_rec.remarks,
764 X_MESSAGE_CODE => exception_rec.message_code,
765 X_PRECEDING_UE_ID => exception_rec.preceding_ue_id,
766 X_DATE_RUN => sysdate,
767 X_SET_DUE_DATE => exception_rec.set_due_date,
768 X_ACCOMPLISHED_DATE => exception_rec.accomplished_date,
769 X_SERVICE_LINE_ID => exception_rec.service_line_id,
770 X_PROGRAM_MR_HEADER_ID => exception_rec.program_mr_header_id,
771 X_CANCEL_REASON_CODE => exception_rec.cancel_reason_code,
772 X_EARLIEST_DUE_DATE => exception_rec.earliest_due_date,
773 X_LATEST_DUE_DATE => exception_rec.latest_due_date,
774 X_defer_from_ue_id => exception_rec.defer_from_ue_id,
775 X_cs_incident_id => exception_rec.cs_incident_id,
776 X_qa_collection_id => exception_rec.qa_collection_id,
777 X_orig_deferral_ue_id => exception_rec.orig_deferral_ue_id,
778 X_application_usg_code => exception_rec.application_usg_code,
779 X_object_type => exception_rec.object_type,
780 X_counter_id => exception_rec.counter_id,
781 X_MANUALLY_PLANNED_FLAG => exception_rec.MANUALLY_PLANNED_FLAG,
782 X_LOG_SERIES_CODE => exception_rec.log_series_code,
783 X_LOG_SERIES_NUMBER => exception_rec.log_series_number,
784 X_FLIGHT_NUMBER => exception_rec.flight_number,
785 X_MEL_CDL_TYPE_CODE => exception_rec.mel_cdl_type_code,
786 X_POSITION_PATH_ID => exception_rec.position_path_id,
787 X_ATA_CODE => exception_rec.ATA_CODE,
788 X_UNIT_CONFIG_HEADER_ID => exception_rec.unit_config_header_id,
789 X_ATTRIBUTE_CATEGORY => exception_rec.attribute_category,
790 X_ATTRIBUTE1 => exception_rec.attribute1,
791 X_ATTRIBUTE2 => exception_rec.attribute2,
792 X_ATTRIBUTE3 => exception_rec.attribute3,
793 X_ATTRIBUTE4 => exception_rec.attribute4,
794 X_ATTRIBUTE5 => exception_rec.attribute5,
795 X_ATTRIBUTE6 => exception_rec.attribute6,
796 X_ATTRIBUTE7 => exception_rec.attribute7,
797 X_ATTRIBUTE8 => exception_rec.attribute8,
798 X_ATTRIBUTE9 => exception_rec.attribute9,
799 X_ATTRIBUTE10 => exception_rec.attribute10,
800 X_ATTRIBUTE11 => exception_rec.attribute11,
801 X_ATTRIBUTE12 => exception_rec.attribute12,
802 X_ATTRIBUTE13 => exception_rec.attribute13,
803 X_ATTRIBUTE14 => exception_rec.attribute14,
804 X_ATTRIBUTE15 => exception_rec.attribute15,
805 X_OBJECT_VERSION_NUMBER => exception_rec.object_version_number + 1,
806 X_LAST_UPDATE_DATE => sysdate,
807 X_LAST_UPDATED_BY => fnd_global.user_id,
808 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
809
810 -- Delete the corresponding rows in ahl_schedule materials for this ue.
811 Delete_Sch_Materials(exception_rec.unit_effectivity_id);
812
813 -- update all group element's status too.
814 FOR ue_reln_rec IN decendent_csr(exception_rec.unit_effectivity_id) LOOP
815
816 OPEN ahl_unit_effectivity_csr (ue_reln_rec.related_ue_id);
817 FETCH ahl_unit_effectivity_csr INTO l_ue_rec;
818 IF (ahl_unit_effectivity_csr%FOUND) THEN
819 AHL_UNIT_EFFECTIVITIES_PKG.Update_Row(
820 X_UNIT_EFFECTIVITY_ID => l_ue_rec.unit_effectivity_id,
821 X_CSI_ITEM_INSTANCE_ID => l_ue_rec.csi_item_instance_id,
822 X_MR_INTERVAL_ID => l_ue_rec.mr_interval_id,
823 X_MR_EFFECTIVITY_ID => l_ue_rec.mr_effectivity_id,
824 X_MR_HEADER_ID => l_ue_rec.mr_header_id,
825 X_STATUS_CODE => exception_rec.status_code,
826 X_DUE_DATE => l_ue_rec.due_date,
827 X_DUE_COUNTER_VALUE => l_ue_rec.due_counter_value,
828 X_FORECAST_SEQUENCE => l_ue_rec.forecast_sequence,
829 X_REPETITIVE_MR_FLAG => l_ue_rec.repetitive_mr_flag,
830 X_TOLERANCE_FLAG => l_ue_rec.tolerance_flag,
831 X_REMARKS => l_ue_rec.remarks,
832 X_MESSAGE_CODE => l_ue_rec.message_code,
833 X_PRECEDING_UE_ID => l_ue_rec.preceding_ue_id,
834 X_DATE_RUN => sysdate,
835 X_SET_DUE_DATE => l_ue_rec.set_due_date,
836 X_ACCOMPLISHED_DATE => l_ue_rec.accomplished_date,
837 X_SERVICE_LINE_ID => l_ue_rec.service_line_id,
838 X_PROGRAM_MR_HEADER_ID => l_ue_rec.program_mr_header_id,
839 X_CANCEL_REASON_CODE => l_ue_rec.cancel_reason_code,
840 X_EARLIEST_DUE_DATE => l_ue_rec.earliest_due_date,
841 X_LATEST_DUE_DATE => l_ue_rec.latest_due_date,
842 X_defer_from_ue_id => l_ue_rec.defer_from_ue_id,
843 X_cs_incident_id => l_ue_rec.cs_incident_id,
844 X_qa_collection_id => l_ue_rec.qa_collection_id,
845 X_orig_deferral_ue_id => l_ue_rec.orig_deferral_ue_id,
846 X_application_usg_code => l_ue_rec.application_usg_code,
847 X_object_type => l_ue_rec.object_type,
848 X_counter_id => l_ue_rec.counter_id,
849 X_MANUALLY_PLANNED_FLAG => l_ue_rec.MANUALLY_PLANNED_FLAG,
850 X_LOG_SERIES_CODE => l_ue_rec.log_series_code,
851 X_LOG_SERIES_NUMBER => l_ue_rec.log_series_number,
852 X_FLIGHT_NUMBER => l_ue_rec.flight_number,
853 X_MEL_CDL_TYPE_CODE => l_ue_rec.mel_cdl_type_code,
854 X_POSITION_PATH_ID => l_ue_rec.position_path_id,
855 X_ATA_CODE => l_ue_rec.ATA_CODE,
856 X_UNIT_CONFIG_HEADER_ID => l_ue_rec.unit_config_header_id,
857 X_ATTRIBUTE_CATEGORY => l_ue_rec.attribute_category,
858 X_ATTRIBUTE1 => l_ue_rec.attribute1,
859 X_ATTRIBUTE2 => l_ue_rec.attribute2,
860 X_ATTRIBUTE3 => l_ue_rec.attribute3,
861 X_ATTRIBUTE4 => l_ue_rec.attribute4,
862 X_ATTRIBUTE5 => l_ue_rec.attribute5,
863 X_ATTRIBUTE6 => l_ue_rec.attribute6,
864 X_ATTRIBUTE7 => l_ue_rec.attribute7,
865 X_ATTRIBUTE8 => l_ue_rec.attribute8,
866 X_ATTRIBUTE9 => l_ue_rec.attribute9,
867 X_ATTRIBUTE10 => l_ue_rec.attribute10,
868 X_ATTRIBUTE11 => l_ue_rec.attribute11,
869 X_ATTRIBUTE12 => l_ue_rec.attribute12,
870 X_ATTRIBUTE13 => l_ue_rec.attribute13,
871 X_ATTRIBUTE14 => l_ue_rec.attribute14,
872 X_ATTRIBUTE15 => l_ue_rec.attribute15,
873 X_OBJECT_VERSION_NUMBER => l_ue_rec.object_version_number + 1,
874 X_LAST_UPDATE_DATE => sysdate,
875 X_LAST_UPDATED_BY => fnd_global.user_id,
876 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
877
878 -- Delete the corresponding rows in ahl_schedule materials for this ue.
879 Delete_Sch_Materials(l_ue_rec.unit_effectivity_id);
880
881 ELSE
882 FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_UE_NOTFOUND');
883 FND_MESSAGE.Set_Token('UE_ID',l_ue_rec.unit_effectivity_id);
884 FND_MSG_PUB.ADD;
885 -- dbms_output.put_line('preceding mr not found for dependent ue id');
886 ClOSE ahl_unit_effectivity_csr;
887 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
888 END IF;
889 ClOSE ahl_unit_effectivity_csr;
890 END LOOP;
891
892 ELSIF (l_delete_flag) THEN
893
894 IF G_DEBUG = 'Y' THEN
895 AHL_DEBUG_PUB.Debug('Deleting exception code..');
896 END IF;
897
898 FOR ue_reln_rec IN decendent_csr(exception_rec.unit_effectivity_id) LOOP
899
900 -- delete relationship.
901 AHL_UE_RELATIONSHIPS_PKG.Delete_Row (ue_reln_rec.ue_relationship_id);
902 -- delete unit effectivity record.
903 AHL_UNIT_EFFECTIVITIES_PKG.Delete_Row(ue_reln_rec.related_ue_id);
904
905 -- Delete the corresponding rows in ahl_schedule materials for this ue.
906 Delete_Sch_Materials(ue_reln_rec.related_ue_id);
907
908
909 END LOOP;
910
911 -- Delete the corresponding rows in ahl_schedule materials for this ue.
912 Delete_Sch_Materials(exception_rec.unit_effectivity_id);
913
914 IF (exception_rec.status_code = 'INIT-DUE' OR
915 exception_rec.message_code = 'INIT-DUE') THEN
916 IF G_DEBUG = 'Y' THEN
917 AHL_DEBUG_PUB.Debug('Exception - Init-Due');
918 END IF;
919
920 OPEN exception_init_due_csr (exception_rec.unit_effectivity_id);
921 FETCH exception_init_due_csr INTO l_unit_deferral_id;
922 IF (exception_init_due_csr%FOUND) THEN
923 DELETE from ahl_unit_thresholds
924 WHERE unit_deferral_id = l_unit_deferral_id;
925
926 AHL_UNIT_DEFERRALS_PKG.Delete_Row(l_unit_deferral_id);
927 END IF;
928 CLOSE exception_init_due_csr;
929
930 END IF; -- exception_rec.status_code
931
932 -- delete ue.
933 AHL_UNIT_EFFECTIVITIES_PKG.Delete_Row(exception_rec.unit_effectivity_id);
934 END IF;
935
936 END LOOP; /* for exception rec */
937 END LOOP; /* for node */
938 END IF;
939
940 IF G_DEBUG = 'Y' THEN
941 AHL_DEBUG_PUB.Debug('End Flush from temporary table.');
942 END IF;
943
944
945 END Flush_From_Temp_table;
946
947 -----------------------------------------------------------------------------
948 -- To create ahl_unit_effectivities record.
949
950 PROCEDURE create_record (p_x_temp_mr_rec IN OUT NOCOPY ahl_temp_unit_effectivities%ROWTYPE)
951
952 IS
953 l_unit_effectivity_id NUMBER;
954 l_rowid VARCHAR2(30);
955
956 BEGIN
957
958 IF G_DEBUG = 'Y' THEN
959 AHL_DEBUG_PUB.Debug('Start Create Record');
960 AHL_DEBUG_PUB.Debug('CSI:MR:' || p_x_temp_mr_rec.csi_item_instance_id || ',' || p_x_temp_mr_rec.MR_header_id);
961 END IF;
962
963 -- Default object type and application usage values.
964
965 AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row (
966 X_ROWID => l_rowid,
967 X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_id,
968 X_CSI_ITEM_INSTANCE_ID => p_x_temp_mr_rec.csi_item_instance_id,
969 X_MR_INTERVAL_ID => p_x_temp_mr_rec.mr_interval_id,
970 X_MR_EFFECTIVITY_ID => p_x_temp_mr_rec.mr_effectivity_id,
971 X_MR_HEADER_ID => p_x_temp_mr_rec.MR_header_id,
972 X_STATUS_CODE => null, /* status_code */
973 X_DUE_DATE => p_x_temp_mr_rec.due_date,
974 X_DUE_COUNTER_VALUE => p_x_temp_mr_rec.due_counter_value,
975 X_FORECAST_SEQUENCE => p_x_temp_mr_rec.forecast_sequence,
976 X_REPETITIVE_MR_FLAG => p_x_temp_mr_rec.repetitive_mr_flag,
977 X_TOLERANCE_FLAG => p_x_temp_mr_rec.tolerance_flag,
978 X_REMARKS => null, /* remarks */
979 X_MESSAGE_CODE => p_x_temp_mr_rec.message_code,
980 X_PRECEDING_UE_ID => null, /* p_x_temp_mr_rec.preceding_ue_id */
981 X_DATE_RUN => sysdate, /* date_run */
982 X_SET_DUE_DATE => null, /* set due date */
983 X_ACCOMPLISHED_DATE => null, /* accomplished date */
984 X_SERVICE_LINE_ID => p_x_temp_mr_rec.service_line_id,
985 X_PROGRAM_MR_HEADER_ID => p_x_temp_mr_rec.program_mr_header_id,
986 X_CANCEL_REASON_CODE => null, /* cancel_reason_code */
987 X_EARLIEST_DUE_DATE => p_x_temp_mr_rec.earliest_due_date,
988 X_LATEST_DUE_DATE => p_x_temp_mr_rec.latest_due_date,
989 X_defer_from_ue_id => null,
990 X_cs_incident_id => null,
991 X_qa_collection_id => null,
992 X_orig_deferral_ue_id => null,
993 X_application_usg_code => G_APPLN_USAGE_CODE,
994 X_object_type => 'MR',
995 X_counter_id => p_x_temp_mr_rec.counter_id,
996 X_MANUALLY_PLANNED_FLAG => 'N',
997 X_LOG_SERIES_CODE => NULL,
998 X_LOG_SERIES_NUMBER => NULL,
999 X_FLIGHT_NUMBER => NULL,
1000 X_MEL_CDL_TYPE_CODE => NULL,
1001 X_POSITION_PATH_ID => NULL,
1002 X_ATA_CODE => NULL,
1003 X_UNIT_CONFIG_HEADER_ID => NULL,
1004 X_ATTRIBUTE_CATEGORY => null, /* ATTRIBUTE_CATEGORY */
1005 X_ATTRIBUTE1 => null, /* ATTRIBUTE1 */
1006 X_ATTRIBUTE2 => null, /* ATTRIBUTE2 */
1007 X_ATTRIBUTE3 => null, /* ATTRIBUTE3 */
1008 X_ATTRIBUTE4 => null, /* ATTRIBUTE4 */
1009 X_ATTRIBUTE5 => null, /* ATTRIBUTE5 */
1010 X_ATTRIBUTE6 => null, /* ATTRIBUTE6 */
1011 X_ATTRIBUTE7 => null, /* ATTRIBUTE7 */
1012 X_ATTRIBUTE8 => null, /* ATTRIBUTE8 */
1013 X_ATTRIBUTE9 => null, /* ATTRIBUTE9 */
1014 X_ATTRIBUTE10 => null, /* ATTRIBUTE10 */
1015 X_ATTRIBUTE11 => null, /* ATTRIBUTE11 */
1016 X_ATTRIBUTE12 => null, /* ATTRIBUTE12 */
1017 X_ATTRIBUTE13 => null, /* ATTRIBUTE13 */
1018 X_ATTRIBUTE14 => null, /* ATTRIBUTE14 */
1019 X_ATTRIBUTE15 => null, /* ATTRIBUTE15 */
1020 X_OBJECT_VERSION_NUMBER => 1, /* object version */
1021 X_CREATION_DATE => sysdate,
1022 X_CREATED_BY => fnd_global.user_id,
1023 X_LAST_UPDATE_DATE => sysdate,
1024 X_LAST_UPDATED_BY => fnd_global.user_id,
1025 X_LAST_UPDATE_LOGIN => fnd_global.login_id );
1026
1027 p_x_temp_mr_rec.unit_effectivity_id := l_unit_effectivity_id;
1028
1029 IF G_DEBUG = 'Y' THEN
1030 AHL_DEBUG_PUB.Debug('End Create Record');
1031 END IF;
1032
1033
1034 EXCEPTION
1035 -- If any error occurs, then, abort API.
1036 WHEN NO_DATA_FOUND THEN
1037 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1038 FND_MSG_PUB.ADD;
1039 RAISE FND_API.G_EXC_ERROR;
1040
1041 END create_record;
1042
1043
1044 -----------------------------------------------------------------------------
1045 -- To update ahl_unit_effectivities record.
1046
1047 PROCEDURE update_record (p_temp_mr_rec IN ahl_temp_unit_effectivities%ROWTYPE,
1048 p_mr_rec IN ahl_unit_effectivities_app_v%ROWTYPE)
1049 IS
1050
1051 BEGIN
1052
1053 IF G_DEBUG = 'Y' THEN
1054 AHL_DEBUG_PUB.Debug('Start Update Record-' || p_mr_rec.unit_effectivity_id);
1055 AHL_DEBUG_PUB.Debug('CSI:MR:' || p_mr_rec.csi_item_instance_id || ',' || p_mr_rec.MR_header_id);
1056 END IF;
1057
1058 AHL_UNIT_EFFECTIVITIES_PKG.Update_Row (
1059 X_UNIT_EFFECTIVITY_ID => p_mr_rec.unit_effectivity_id,
1060 X_CSI_ITEM_INSTANCE_ID => p_mr_rec.csi_item_instance_id,
1061 X_MR_INTERVAL_ID => p_temp_mr_rec.mr_interval_id,
1062 X_MR_EFFECTIVITY_ID => p_temp_mr_rec.mr_effectivity_id,
1063 X_MR_HEADER_ID => p_mr_rec.MR_header_id,
1064 X_STATUS_CODE => p_mr_rec.status_code,
1065 X_DUE_DATE => p_temp_mr_rec.due_date,
1066 X_DUE_COUNTER_VALUE => p_temp_mr_rec.due_counter_value,
1067 X_FORECAST_SEQUENCE => p_temp_mr_rec.forecast_sequence,
1068 X_REPETITIVE_MR_FLAG => p_temp_mr_rec.repetitive_mr_flag,
1069 X_TOLERANCE_FLAG => p_temp_mr_rec.tolerance_flag,
1070 X_REMARKS => p_mr_rec.remarks,
1071 X_MESSAGE_CODE => p_temp_mr_rec.message_code,
1072 X_PRECEDING_UE_ID => null, /* preceding_ue_id */
1073 X_DATE_RUN => sysdate, /* date run */
1074 X_SET_DUE_DATE => p_mr_rec.set_due_date,
1075 X_ACCOMPLISHED_DATE => p_mr_rec.accomplished_date,
1076 X_SERVICE_LINE_ID => p_temp_mr_rec.service_line_id,
1077 X_PROGRAM_MR_HEADER_ID => p_temp_mr_rec.program_mr_header_id,
1078 X_CANCEL_REASON_CODE => p_mr_rec.cancel_reason_code,
1079 X_EARLIEST_DUE_DATE => p_temp_mr_rec.earliest_due_date,
1080 X_LATEST_DUE_DATE => p_temp_mr_rec.latest_due_date,
1081 X_defer_from_ue_id => p_mr_rec.defer_from_ue_id,
1082 X_cs_incident_id => p_mr_rec.cs_incident_id,
1083 X_qa_collection_id => p_mr_rec.qa_collection_id,
1084 X_orig_deferral_ue_id => p_mr_rec.orig_deferral_ue_id,
1085 X_application_usg_code => p_mr_rec.application_usg_code,
1086 X_object_type => p_mr_rec.object_type,
1087 X_counter_id => p_temp_mr_rec.counter_id,
1088 X_MANUALLY_PLANNED_FLAG => p_mr_rec.MANUALLY_PLANNED_FLAG,
1089 X_LOG_SERIES_CODE => p_mr_rec.log_series_code,
1090 X_LOG_SERIES_NUMBER => p_mr_rec.log_series_number,
1091 X_FLIGHT_NUMBER => p_mr_rec.flight_number,
1092 X_MEL_CDL_TYPE_CODE => p_mr_rec.mel_cdl_type_code,
1093 X_POSITION_PATH_ID => p_mr_rec.position_path_id,
1094 X_ATA_CODE => p_mr_rec.ATA_CODE,
1095 X_UNIT_CONFIG_HEADER_ID => p_mr_rec.unit_config_header_id,
1096 X_ATTRIBUTE_CATEGORY => p_mr_rec.ATTRIBUTE_CATEGORY,
1097 X_ATTRIBUTE1 => p_mr_rec.ATTRIBUTE1,
1098 X_ATTRIBUTE2 => p_mr_rec.ATTRIBUTE2,
1099 X_ATTRIBUTE3 => p_mr_rec.ATTRIBUTE3,
1100 X_ATTRIBUTE4 => p_mr_rec.ATTRIBUTE4,
1101 X_ATTRIBUTE5 => p_mr_rec.ATTRIBUTE5,
1102 X_ATTRIBUTE6 => p_mr_rec.ATTRIBUTE6,
1103 X_ATTRIBUTE7 => p_mr_rec.ATTRIBUTE7,
1104 X_ATTRIBUTE8 => p_mr_rec.ATTRIBUTE8,
1105 X_ATTRIBUTE9 => p_mr_rec.ATTRIBUTE9,
1106 X_ATTRIBUTE10 => p_mr_rec.ATTRIBUTE10,
1107 X_ATTRIBUTE11 => p_mr_rec.ATTRIBUTE11,
1108 X_ATTRIBUTE12 => p_mr_rec.ATTRIBUTE12,
1109 X_ATTRIBUTE13 => p_mr_rec.ATTRIBUTE13,
1110 X_ATTRIBUTE14 => p_mr_rec.ATTRIBUTE14,
1111 X_ATTRIBUTE15 => p_mr_rec.ATTRIBUTE15,
1112 X_OBJECT_VERSION_NUMBER => p_mr_rec.object_version_number+1,
1113 X_LAST_UPDATE_DATE => sysdate,
1114 X_LAST_UPDATED_BY => fnd_global.user_id,
1115 X_LAST_UPDATE_LOGIN => fnd_global.login_id );
1116
1117 IF (p_temp_mr_rec.due_date IS NULL AND p_mr_rec.object_type = 'MR') THEN
1118 -- Delete the corresponding rows in ahl_schedule materials for this ue.
1119 Delete_Sch_Materials(p_mr_rec.unit_effectivity_id);
1120 END IF;
1121
1122 IF G_DEBUG = 'Y' THEN
1123 AHL_DEBUG_PUB.Debug('End Update Record');
1124 END IF;
1125
1126 EXCEPTION
1127 -- If any error occurs, then, abort API.
1128 WHEN NO_DATA_FOUND THEN
1129 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1130 FND_MSG_PUB.ADD;
1131 RAISE FND_API.G_EXC_ERROR;
1132
1133 END update_record;
1134
1135
1136 -----------------------------------------------------------------------------
1137 -- To create decendent records and build relationships if the MR is a group MR.
1138
1139 PROCEDURE create_group (p_x_temp_grp_rec IN OUT NOCOPY ahl_temp_unit_effectivities%ROWTYPE)
1140
1141 IS
1142 -- Read group elements.
1143 CURSOR ahl_temp_grp_csr(p_csi_item_instance_id IN NUMBER,
1144 p_mr_header_id IN NUMBER,
1145 p_forecast_sequence IN NUMBER) IS
1146 SELECT unit_effectivity_id,
1147 csi_item_instance_id,
1148 MR_header_id,
1149 due_date,
1150 mr_interval_id,
1151 mr_effectivity_id,
1152 due_counter_value,
1153 parent_csi_item_instance_id,
1154 parent_mr_header_id,
1155 orig_csi_item_instance_id,
1156 orig_mr_header_id,
1157 forecast_sequence,
1158 repetitive_mr_flag,
1159 tolerance_flag,
1160 message_code,
1161 earliest_due_date,
1162 latest_due_date,
1163 counter_id
1164 FROM ahl_temp_unit_effectivities
1165 START WITH parent_csi_item_instance_id = p_csi_item_instance_id
1166 AND parent_mr_header_id = p_mr_header_id
1167 AND orig_csi_item_instance_id = p_csi_item_instance_id
1168 AND orig_mr_header_id = p_mr_header_id
1169 AND orig_forecast_sequence = p_forecast_sequence
1170 AND nvl(preceding_check_flag,'N') = 'N'
1171 CONNECT BY PRIOR MR_header_id = parent_mr_header_id
1172 AND PRIOR csi_item_instance_id = parent_csi_item_instance_id
1173 AND orig_csi_item_instance_id = p_csi_item_instance_id
1174 AND orig_mr_header_id = p_mr_header_id
1175 AND orig_forecast_sequence = p_forecast_sequence
1176 AND nvl(preceding_check_flag,'N') = 'N'
1177 FOR UPDATE OF due_date;
1178
1179 -- get parent unit effectivity id.
1180 CURSOR ahl_temp_parent_csr (p_parent_csi_item_instance_id IN NUMBER,
1181 p_parent_mr_header_id IN NUMBER,
1182 p_orig_csi_item_instance_id IN NUMBER,
1183 p_orig_mr_header_id IN NUMBER,
1184 p_forecast_sequence IN NUMBER) IS
1185 SELECT unit_effectivity_id
1186 FROM ahl_temp_unit_effectivities
1187 WHERE csi_item_instance_id = p_parent_csi_item_instance_id
1188 AND MR_header_id = p_parent_mr_header_id
1189 AND orig_csi_item_instance_id = p_orig_csi_item_instance_id
1190 AND orig_mr_header_id = p_orig_mr_header_id
1191 AND orig_forecast_sequence = p_forecast_sequence;
1192
1193 l_ue_relationship_id NUMBER;
1194
1195 l_originator_ue_id NUMBER;
1196 l_orig_csi_item_instance_id NUMBER;
1197 l_orig_mr_header_id NUMBER;
1198
1199 l_parent_ue_id NUMBER;
1200
1201 l_temp_child_rec ahl_temp_unit_effectivities%ROWTYPE;
1202
1203 BEGIN
1204
1205 IF G_DEBUG = 'Y' THEN
1206 AHL_DEBUG_PUB.Debug('Start Create Group');
1207 AHL_DEBUG_PUB.Debug('CSI:MR:' || p_x_temp_grp_rec.csi_item_instance_id || ',' || p_x_temp_grp_rec.MR_header_id);
1208 END IF;
1209
1210 -- For top node.
1211 Create_Record (p_x_temp_grp_rec);
1212
1213 -- Update ahl_temp_unit_effectivities with the unit effectivity id.
1214 UPDATE ahl_temp_unit_effectivities
1215 SET unit_effectivity_id = p_x_temp_grp_rec.unit_effectivity_id
1216 WHERE csi_item_instance_id = p_x_temp_grp_rec.csi_item_instance_id AND
1217 mr_header_id = p_x_temp_grp_rec.mr_header_id AND
1218 forecast_sequence = p_x_temp_grp_rec.forecast_sequence;
1219
1220 -- Read all elements.
1221 FOR l_temp_grp_rec IN ahl_temp_grp_csr(p_x_temp_grp_rec.csi_item_instance_id,
1222 p_x_temp_grp_rec.mr_header_id,
1223 p_x_temp_grp_rec.forecast_sequence)
1224 LOOP
1225
1226 -- set record values.
1227 l_temp_child_rec.unit_effectivity_id := null;
1228 l_temp_child_rec.csi_item_instance_id := l_temp_grp_rec.csi_item_instance_id;
1229 l_temp_child_rec.mr_interval_id := l_temp_grp_rec.mr_interval_id;
1230 l_temp_child_rec.mr_effectivity_id := l_temp_grp_rec.mr_effectivity_id;
1231 l_temp_child_rec.MR_header_id := l_temp_grp_rec.mr_header_id;
1232 l_temp_child_rec.due_date := l_temp_grp_rec.due_date;
1233 l_temp_child_rec.due_counter_value := l_temp_grp_rec.due_counter_value;
1234 l_temp_child_rec.forecast_sequence := l_temp_grp_rec.forecast_sequence;
1235 l_temp_child_rec.repetitive_mr_flag := l_temp_grp_rec.repetitive_mr_flag;
1236 l_temp_child_rec.tolerance_flag := l_temp_grp_rec.tolerance_flag;
1237 l_temp_child_rec.message_code := l_temp_grp_rec.message_code;
1238 l_temp_child_rec.earliest_due_date := l_temp_grp_rec.earliest_due_date;
1239 l_temp_child_rec.latest_due_date := l_temp_grp_rec.latest_due_date;
1240 l_temp_child_rec.counter_id := l_temp_grp_rec.counter_id;
1241
1242 -- Insert into ahl_unit_effectivities.
1243 Create_Record (l_temp_child_rec);
1244
1245 -- Update ahl_temp_unit_effectivities with the unit effectivity id.
1246 UPDATE ahl_temp_unit_effectivities
1247 SET unit_effectivity_id = l_temp_child_rec.unit_effectivity_id
1248 WHERE CURRENT OF ahl_temp_grp_csr;
1249
1250 --dbms_output.put_line ('generated unit effectivity id' || l_unit_effectivity_id);
1251
1252 END LOOP;
1253
1254 -- Read from the top group node and build relationships by inserting
1255 -- into the relationships table.
1256
1257 l_originator_ue_id := p_x_temp_grp_rec.unit_effectivity_id;
1258 l_orig_csi_item_instance_id := p_x_temp_grp_rec.csi_item_instance_id;
1259 l_orig_mr_header_id := p_x_temp_grp_rec.mr_header_id;
1260
1261 --dbms_output.put_line ('before relationships built');
1262
1263 FOR l_temp_grp_rec IN ahl_temp_grp_csr(p_x_temp_grp_rec.csi_item_instance_id,
1264 p_x_temp_grp_rec.mr_header_id,
1265 p_x_temp_grp_rec.forecast_sequence)
1266 LOOP
1267
1268 OPEN ahl_temp_parent_csr(l_temp_grp_rec.parent_csi_item_instance_id,
1269 l_temp_grp_rec.parent_mr_header_id,
1270 l_orig_csi_item_instance_id,
1271 l_orig_mr_header_id,
1272 p_x_temp_grp_rec.forecast_sequence);
1273 FETCH ahl_temp_parent_csr INTO l_parent_ue_id;
1274 IF (ahl_temp_parent_csr%NOTFOUND) THEN
1275 FND_MESSAGE.Set_Name ('AHL','AHL_UMP_PUE_PARENT_NOTFOUND');
1276 FND_MESSAGE.Set_Token ('INST_ID',l_temp_grp_rec.csi_item_instance_id);
1277 FND_MESSAGE.Set_Token ('MR_ID',l_temp_grp_rec.mr_header_id);
1278 FND_MSG_PUB.ADD;
1279 CLOSE ahl_temp_parent_csr;
1280 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1281 END IF;
1282 CLOSE ahl_temp_parent_csr;
1283
1284 -- Insert into ahl_ue_relationships.
1285 AHL_UE_RELATIONSHIPS_PKG.Insert_Row(
1286 X_UE_RELATIONSHIP_ID => l_ue_relationship_id,
1287 X_UE_ID => l_parent_ue_id,
1288 X_RELATED_UE_ID => l_temp_grp_rec.unit_effectivity_id,
1289 X_RELATIONSHIP_CODE => 'PARENT',
1290 X_ORIGINATOR_UE_ID => l_originator_ue_id,
1291 X_ATTRIBUTE_CATEGORY => null, /* ATTRIBUTE_CATEGORY */
1292 X_ATTRIBUTE1 => null, /* ATTRIBUTE1 */
1293 X_ATTRIBUTE2 => null, /* ATTRIBUTE2 */
1294 X_ATTRIBUTE3 => null, /* ATTRIBUTE3 */
1295 X_ATTRIBUTE4 => null, /* ATTRIBUTE4 */
1296 X_ATTRIBUTE5 => null, /* ATTRIBUTE5 */
1297 X_ATTRIBUTE6 => null, /* ATTRIBUTE6 */
1298 X_ATTRIBUTE7 => null, /* ATTRIBUTE7 */
1299 X_ATTRIBUTE8 => null, /* ATTRIBUTE8 */
1300 X_ATTRIBUTE9 => null, /* ATTRIBUTE9 */
1301 X_ATTRIBUTE10 => null, /* ATTRIBUTE10 */
1302 X_ATTRIBUTE11 => null, /* ATTRIBUTE11 */
1303 X_ATTRIBUTE12 => null, /* ATTRIBUTE12 */
1304 X_ATTRIBUTE13 => null, /* ATTRIBUTE13 */
1305 X_ATTRIBUTE14 => null, /* ATTRIBUTE14 */
1306 X_ATTRIBUTE15 => null, /* ATTRIBUTE15 */
1307 X_OBJECT_VERSION_NUMBER => 1,
1308 X_LAST_UPDATE_DATE => sysdate,
1309 X_LAST_UPDATED_BY => fnd_global.user_id,
1310 X_CREATION_DATE => sysdate,
1311 X_CREATED_BY => fnd_global.user_id,
1312 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
1313
1314 END LOOP;
1315
1316 IF G_DEBUG = 'Y' THEN
1317 AHL_DEBUG_PUB.Debug('End Create Group');
1318 END IF;
1319
1320 EXCEPTION
1321 -- If any error occurs, then, abort API.
1322 WHEN NO_DATA_FOUND THEN
1323 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1324 FND_MSG_PUB.ADD;
1325 RAISE FND_API.G_EXC_ERROR;
1326
1327 END create_group;
1328
1329 --------------------------------------------------------------------------------
1330 -- To update decendent records and update relationships if the MR is a group MR.
1331
1332 PROCEDURE update_group (p_temp_mr_rec IN ahl_temp_unit_effectivities%ROWTYPE,
1333 p_mr_rec IN ahl_unit_effectivities_app_v%ROWTYPE)
1334 IS
1335
1336 -- Read group elements.
1337 CURSOR ahl_temp_grp_csr(p_csi_item_instance_id IN NUMBER,
1338 p_mr_header_id IN NUMBER,
1339 p_forecast_sequence IN NUMBER,
1340 p_level IN NUMBER) IS
1341 SELECT csi_item_instance_id,
1342 MR_header_id,
1343 parent_csi_item_instance_id,
1344 parent_mr_header_id,
1345 orig_csi_item_instance_id,
1346 orig_mr_header_id,
1347 forecast_sequence
1348 FROM ahl_temp_unit_effectivities
1349 WHERE level = p_level
1350 START WITH parent_csi_item_instance_id = p_csi_item_instance_id
1351 AND parent_mr_header_id = p_mr_header_id
1352 AND orig_csi_item_instance_id = p_csi_item_instance_id
1353 AND orig_mr_header_id = p_mr_header_id
1354 AND orig_forecast_sequence = p_forecast_sequence
1355 AND nvl(preceding_check_flag,'N') = 'N'
1356 CONNECT BY PRIOR MR_header_id = parent_mr_header_id
1357 AND PRIOR csi_item_instance_id = parent_csi_item_instance_id
1358 AND orig_csi_item_instance_id = p_csi_item_instance_id
1359 AND orig_mr_header_id = p_mr_header_id
1360 AND orig_forecast_sequence = p_forecast_sequence
1361 AND nvl(preceding_check_flag,'N') = 'N';
1362
1363 -- Read group elements from ue relationships.
1364 CURSOR ahl_ue_reln_csr(p_unit_effectivity_id IN NUMBER,
1365 p_level IN NUMBER) IS
1366 SELECT UE_ID parent_ue_id,
1367 RELATED_UE_ID ue_id
1368 FROM ahl_ue_relationships
1369 WHERE level = p_level
1370 START WITH ue_id = p_unit_effectivity_id AND
1371 relationship_code = 'PARENT'
1372 CONNECT BY PRIOR related_ue_id = ue_id AND
1373 relationship_code = 'PARENT';
1374
1375 -- get related unit effectivities details.
1376 CURSOR ahl_ue_grp_csr ( p_ue_id IN NUMBER,
1377 p_parent_ue_id IN NUMBER ) IS
1378 SELECT ue1.mr_header_id, ue1.csi_item_instance_id, ue1.unit_effectivity_id,
1379 ue2.mr_header_id parent_mr_header_id,
1380 ue2.csi_item_instance_id parent_csi_item_instance_id
1381 --FROM ahl_unit_effectivities_app_v ue1, ahl_unit_effectivities_app_v ue2
1382 FROM ahl_unit_effectivities_b ue1, ahl_unit_effectivities_b ue2
1383 WHERE ue1.unit_effectivity_id = p_ue_id AND
1384 ue2.unit_effectivity_id = p_parent_ue_id;
1385
1386 -- Cursor to get all details of a unit effectivity record.
1387 CURSOR ahl_unit_effectivity_csr ( p_unit_effectivity_id IN NUMBER) IS
1388 SELECT
1389 UNIT_EFFECTIVITY_ID ,
1390 CSI_ITEM_INSTANCE_ID,
1391 MR_INTERVAL_ID,
1392 MR_EFFECTIVITY_ID ,
1393 MR_HEADER_ID,
1394 STATUS_CODE ,
1395 DUE_DATE ,
1396 DUE_COUNTER_VALUE ,
1397 FORECAST_SEQUENCE ,
1398 --REPETITIVE_MR_FLAG ,
1399 TOLERANCE_FLAG ,
1400 REMARKS ,
1401 MESSAGE_CODE ,
1402 PRECEDING_UE_ID ,
1403 DATE_RUN ,
1404 SET_DUE_DATE ,
1405 ACCOMPLISHED_DATE ,
1406 CANCEL_REASON_CODE,
1407 --EARLIEST_DUE_DATE,
1408 --LATEST_DUE_DATE,
1409 defer_from_ue_id,
1410 cs_incident_id,
1411 qa_collection_id,
1412 orig_deferral_ue_id,
1413 application_usg_code,
1414 object_type,
1415 --counter_id,
1416 ATTRIBUTE_CATEGORY ,
1417 ATTRIBUTE1,
1418 ATTRIBUTE2 ,
1419 ATTRIBUTE3 ,
1420 ATTRIBUTE4 ,
1421 ATTRIBUTE5 ,
1422 ATTRIBUTE6 ,
1423 ATTRIBUTE7 ,
1424 ATTRIBUTE8 ,
1425 ATTRIBUTE9 ,
1426 ATTRIBUTE10,
1427 ATTRIBUTE11 ,
1428 ATTRIBUTE12 ,
1429 ATTRIBUTE13 ,
1430 ATTRIBUTE14 ,
1431 ATTRIBUTE15 ,
1432 OBJECT_VERSION_NUMBER
1433 --FROM ahl_unit_effectivities_app_v
1434 FROM ahl_unit_effectivities_vl
1435 WHERE unit_effectivity_id = p_unit_effectivity_id;
1436 --FOR UPDATE OF due_date NOWAIT;
1437
1438 TYPE temp_grp_rec_type IS RECORD (
1439 csi_item_instance_id NUMBER,
1440 MR_header_id NUMBER,
1441 parent_csi_item_instance_id NUMBER,
1442 parent_mr_header_id NUMBER,
1443 orig_csi_item_instance_id NUMBER,
1444 orig_mr_header_id NUMBER,
1445 forecast_sequence NUMBER );
1446
1447
1448 TYPE temp_grp_tbl_type IS TABLE OF temp_grp_rec_type INDEX BY BINARY_INTEGER;
1449
1450 l_temp_grp_rec temp_grp_rec_type;
1451 l_temp_grp_tbl temp_grp_tbl_type;
1452
1453
1454 TYPE ue_grp_rec_type IS RECORD (
1455 mr_header_id NUMBER,
1456 csi_item_instance_id NUMBER,
1457 unit_effectivity_id NUMBER,
1458 parent_mr_header_id NUMBER,
1459 parent_csi_item_instance_id NUMBER);
1460
1461 TYPE ue_grp_tbl_type IS TABLE OF ue_grp_rec_type INDEX BY BINARY_INTEGER;
1462
1463 l_ue_grp_tbl ue_grp_tbl_type;
1464 l_ue_grp_rec ue_grp_rec_type;
1465
1466 l_level NUMBER;
1467 l_grp_match_found BOOLEAN;
1468 l_temp_grp_found BOOLEAN;
1469 l_ue_grp_found BOOLEAN;
1470
1471 i NUMBER;
1472 l_grp_match_flag BOOLEAN;
1473
1474 l_temp_mr_rec ahl_temp_unit_effectivities%ROWTYPE := p_temp_mr_rec;
1475 l_unit_effectivity_rec ahl_unit_effectivities_app_v%ROWTYPE;
1476
1477 -- added for bug# 7586838
1478 CURSOR unit_deferral_csr(p_ue_id IN NUMBER) IS
1479 SELECT unit_deferral_id
1480 FROM ahl_unit_deferrals_b
1481 WHERE UNIT_EFFECTIVITY_ID = p_ue_id
1482 AND UNIT_DEFERRAL_TYPE = 'INIT-DUE';
1483
1484 l_unit_deferral_id NUMBER;
1485 l_visit_status ahl_visits_b.status_code%TYPE;
1486
1487 BEGIN
1488
1489 IF G_DEBUG = 'Y' THEN
1490 AHL_DEBUG_PUB.Debug('Start Update Group');
1491 AHL_DEBUG_PUB.Debug('CSI:MR:' || p_mr_rec.csi_item_instance_id || ',' || p_mr_rec.MR_header_id);
1492 END IF;
1493
1494 -- Set savepoint.
1495 SAVEPOINT update_group;
1496
1497 l_visit_status := AHL_UMP_UTIL_PKG.get_visit_status (p_temp_mr_rec.unit_effectivity_id);
1498
1499 IF (l_visit_status IN ('RELEASED','CLOSED')) THEN
1500 l_grp_match_found := TRUE; -- ignore matching group as UE on shop floor.
1501 -- update existing UE children.
1502 UPDATE AHL_UNIT_EFFECTIVITIES_B
1503 SET mr_interval_id = p_temp_mr_rec.mr_interval_id,
1504 mr_effectivity_id = p_temp_mr_rec.mr_effectivity_id,
1505 due_date = p_temp_mr_rec.due_date,
1506 due_counter_value = p_temp_mr_rec.due_counter_value,
1507 forecast_sequence = p_temp_mr_rec.forecast_sequence,
1508 repetitive_mr_flag = p_temp_mr_rec.repetitive_mr_flag,
1509 tolerance_flag = p_temp_mr_rec.tolerance_flag,
1510 message_code = p_temp_mr_rec.message_code,
1511 date_run = sysdate,
1512 earliest_due_date = p_temp_mr_rec.earliest_due_date,
1513 latest_due_date = p_temp_mr_rec.latest_due_date,
1514 counter_id = p_temp_mr_rec.counter_id,
1515 object_version_number = object_version_number+1,
1516 LAST_UPDATE_DATE = sysdate,
1517 LAST_UPDATED_BY = fnd_global.user_id,
1518 LAST_UPDATE_LOGIN = fnd_global.login_id
1519 WHERE unit_effectivity_id IN (SELECT related_ue_id
1520 FROM ahl_ue_relationships
1521 WHERE originator_ue_id = p_temp_mr_rec.unit_effectivity_id
1522 AND relationship_code = 'PARENT');
1523
1524 ELSE
1525 -- For each tree level compare and update the effectivity details.
1526 l_level := 0;
1527 l_grp_match_found := TRUE;
1528 l_temp_grp_found := TRUE; /* temp group record found */
1529 l_ue_grp_found := TRUE; /* ue grp found */
1530
1531
1532 WHILE ((l_temp_grp_found) AND (l_ue_grp_found) AND (l_grp_match_found)) LOOP
1533
1534 l_level := l_level + 1;
1535
1536 -- initialize tables.
1537 l_temp_grp_tbl.DELETE;
1538 l_ue_grp_tbl.DELETE;
1539
1540 -- Build table from temp unit effectivities.
1541 i := 1;
1542 FOR temp_rec IN ahl_temp_grp_csr(p_temp_mr_rec.orig_csi_item_instance_id,
1543 p_temp_mr_rec.orig_mr_header_id,
1544 p_temp_mr_rec.forecast_sequence,
1545 l_level)
1546 LOOP
1547 l_temp_grp_tbl(i) := temp_rec;
1548 i := i + 1;
1549 END LOOP;
1550
1551 -- Build table from unit effectivities.
1552 i := 1;
1553 FOR reln_rec IN ahl_ue_reln_csr (p_temp_mr_rec.unit_effectivity_id,
1554 l_level)
1555 LOOP
1556 OPEN ahl_ue_grp_csr(reln_rec.ue_id,
1557 reln_rec.parent_ue_id);
1558 FETCH ahl_ue_grp_csr INTO l_ue_grp_rec;
1559 IF ahl_ue_grp_csr%NOTFOUND THEN
1560 FND_Message.Set_Name ('AHL','AHL_UMP_PUE_RELN_NOTFOUND');
1561 FND_Message.set_token ('UE_ID',reln_rec.parent_ue_id);
1562 FND_Message.set_token ('RELATED_UE_ID',reln_rec.ue_id);
1563 FND_MSG_PUB.ADD;
1564 CLOSE ahl_ue_grp_csr;
1565 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1566 END IF;
1567 CLOSE ahl_ue_grp_csr;
1568 l_ue_grp_tbl(i) := l_ue_grp_rec;
1569 i := i + 1;
1570 END LOOP;
1571
1572 IF (l_temp_grp_tbl.COUNT > 0) THEN
1573 l_temp_grp_found := TRUE;
1574 ELSE
1575 l_temp_grp_found := FALSE;
1576 END IF;
1577
1578 IF (l_ue_grp_tbl.COUNT > 0) THEN
1579 l_ue_grp_found := TRUE;
1580 ELSE
1581 l_ue_grp_found := FALSE;
1582 END IF;
1583
1584 l_grp_match_flag := TRUE;
1585
1586 -- match table count.
1587 IF (l_temp_grp_found) THEN
1588 IF (l_ue_grp_found) THEN
1589 IF (l_temp_grp_tbl.COUNT <> l_ue_grp_tbl.COUNT) THEN
1590 l_grp_match_flag := FALSE;
1591 END IF;
1592 ELSE
1593 l_grp_match_flag := FALSE;
1594 END IF;
1595 ELSE
1596 IF (l_ue_grp_found) THEN
1597 l_grp_match_flag := FALSE;
1598 END IF;
1599 END IF;
1600
1601 -- update.
1602 IF ((l_grp_match_flag) AND (l_ue_grp_found) AND (l_temp_grp_found)) THEN
1603 -- update.
1604 FOR i IN l_temp_grp_tbl.FIRST..l_temp_grp_tbl.LAST LOOP
1605 -- Find the matching entry in l_ue_grp_tbl.
1606 l_grp_match_flag := FALSE; -- this will be set to true when record gets updated.
1607 FOR j IN l_ue_grp_tbl.FIRST..l_ue_grp_tbl.LAST LOOP
1608 IF (l_ue_grp_tbl(j).csi_item_instance_id = l_temp_grp_tbl(i).csi_item_instance_id
1609 AND l_ue_grp_tbl(j).mr_header_id = l_temp_grp_tbl(i).mr_header_id
1610 AND l_ue_grp_tbl(j).parent_csi_item_instance_id = l_temp_grp_tbl(i).parent_csi_item_instance_id
1611 AND l_ue_grp_tbl(j).parent_mr_header_id = l_temp_grp_tbl(i).parent_mr_header_id)
1612 THEN
1613 --dbms_output.put_line ('matched');
1614
1615 -- Read Unit Effectivity record.
1616 OPEN ahl_unit_effectivity_csr(l_ue_grp_tbl(j).unit_effectivity_id);
1617 FETCH ahl_unit_effectivity_csr INTO l_unit_effectivity_rec.UNIT_EFFECTIVITY_ID ,
1618 l_unit_effectivity_rec.CSI_ITEM_INSTANCE_ID,
1619 l_unit_effectivity_rec.MR_INTERVAL_ID,
1620 l_unit_effectivity_rec.MR_EFFECTIVITY_ID ,
1621 l_unit_effectivity_rec.MR_HEADER_ID,
1622 l_unit_effectivity_rec.STATUS_CODE ,
1623 l_unit_effectivity_rec.DUE_DATE ,
1624 l_unit_effectivity_rec.DUE_COUNTER_VALUE ,
1625 l_unit_effectivity_rec.FORECAST_SEQUENCE ,
1626 --l_unit_effectivity_rec.REPETITIVE_MR_FLAG ,
1627 l_unit_effectivity_rec.TOLERANCE_FLAG ,
1628 l_unit_effectivity_rec.REMARKS ,
1629 l_unit_effectivity_rec.MESSAGE_CODE ,
1630 l_unit_effectivity_rec.PRECEDING_UE_ID ,
1631 l_unit_effectivity_rec.DATE_RUN ,
1632 l_unit_effectivity_rec.SET_DUE_DATE ,
1633 l_unit_effectivity_rec.ACCOMPLISHED_DATE ,
1634 l_unit_effectivity_rec.CANCEL_REASON_CODE,
1635 --l_unit_effectivity_rec.earliest_due_date,
1636 --l_unit_effectivity_rec.latest_due_date,
1637 l_unit_effectivity_rec.defer_from_ue_id,
1638 l_unit_effectivity_rec.cs_incident_id,
1639 l_unit_effectivity_rec.qa_collection_id,
1640 l_unit_effectivity_rec.orig_deferral_ue_id,
1641 l_unit_effectivity_rec.application_usg_code,
1642 l_unit_effectivity_rec.object_type,
1643 --l_ue_rec.counter_id,
1644 l_unit_effectivity_rec.ATTRIBUTE_CATEGORY ,
1645 l_unit_effectivity_rec.ATTRIBUTE1,
1646 l_unit_effectivity_rec.ATTRIBUTE2 ,
1647 l_unit_effectivity_rec.ATTRIBUTE3 ,
1648 l_unit_effectivity_rec.ATTRIBUTE4 ,
1649 l_unit_effectivity_rec.ATTRIBUTE5 ,
1650 l_unit_effectivity_rec.ATTRIBUTE6 ,
1651 l_unit_effectivity_rec.ATTRIBUTE7 ,
1652 l_unit_effectivity_rec.ATTRIBUTE8 ,
1653 l_unit_effectivity_rec.ATTRIBUTE9 ,
1654 l_unit_effectivity_rec.ATTRIBUTE10,
1655 l_unit_effectivity_rec.ATTRIBUTE11 ,
1656 l_unit_effectivity_rec.ATTRIBUTE12 ,
1657 l_unit_effectivity_rec.ATTRIBUTE13 ,
1658 l_unit_effectivity_rec.ATTRIBUTE14 ,
1659 l_unit_effectivity_rec.ATTRIBUTE15 ,
1660 l_unit_effectivity_rec.OBJECT_VERSION_NUMBER;
1661
1662 IF (ahl_unit_effectivity_csr%NOTFOUND) THEN
1663 FND_Message.Set_Name ('AHL','AHL_UMP_PUE_UE_NOTFOUND');
1664 FND_Message.set_token ('UE_ID',l_ue_grp_tbl(j).csi_item_instance_id);
1665 FND_MSG_PUB.ADD;
1666 CLOSE ahl_unit_effectivity_csr;
1667 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1668 ELSE
1669
1670 update_record (p_temp_mr_rec, l_unit_effectivity_rec);
1671 l_grp_match_flag := TRUE; -- matched record.
1672 CLOSE ahl_unit_effectivity_csr;
1673 EXIT;
1674 END IF;
1675
1676 END IF;
1677 END LOOP; -- for j (ue)
1678 IF (l_grp_match_flag = FALSE) THEN
1679 EXIT;
1680 END IF;
1681 END LOOP; -- for i (temp)
1682 END IF;
1683
1684 l_grp_match_found := l_grp_match_flag;
1685
1686 END LOOP; /* while */
1687
1688 END IF; -- l_visit_status
1689
1690 IF NOT(l_grp_match_found) THEN
1691 --rollback to save point
1692 ROLLBACK to update_group;
1693
1694 create_group (p_x_temp_grp_rec => l_temp_mr_rec);
1695 -- fix for bug# 7586838.
1696 IF (p_mr_rec.status_code = 'INIT-DUE') THEN
1697 -- update ahl_unit_deferrals_b.
1698 OPEN unit_deferral_csr(p_mr_rec.unit_effectivity_id);
1699 FETCH unit_deferral_csr INTO l_unit_deferral_id;
1700 IF (unit_deferral_csr%FOUND) THEN
1701 UPDATE ahl_unit_deferrals_b
1702 SET UNIT_EFFECTIVITY_ID = l_temp_mr_rec.unit_effectivity_id,
1703 last_update_date = sysdate,
1704 object_version_number = object_version_number + 1,
1705 LAST_UPDATED_BY = fnd_global.user_id,
1706 LAST_UPDATE_LOGIN = fnd_global.login_id
1707 WHERE unit_deferral_id = l_unit_deferral_id;
1708 END IF;
1709 CLOSE unit_deferral_csr;
1710
1711 -- update unit effectivity status on top node.
1712 UPDATE ahl_unit_effectivities_b
1713 SET status_code = 'INIT-DUE'
1714 WHERE unit_effectivity_id = l_temp_mr_rec.unit_effectivity_id;
1715
1716 END IF;
1717 ELSE
1718 -- Update top group node with p_mr_rec.unit_effectivity_id.
1719 update_record (p_temp_mr_rec,
1720 p_mr_rec);
1721
1722 END IF;
1723
1724 IF G_DEBUG = 'Y' THEN
1725 AHL_DEBUG_PUB.Debug('End Update Group');
1726 END IF;
1727
1728
1729 EXCEPTION
1730 -- If any error occurs, then, abort API.
1731 WHEN NO_DATA_FOUND THEN
1732 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1733 FND_MSG_PUB.ADD;
1734 RAISE FND_API.G_EXC_ERROR;
1735
1736 END update_group;
1737 ----------------------------------------------------------------------
1738 -- To update unit effectivities for Deferrals and Service Request UEs.
1739
1740 PROCEDURE Flush_Unit_SR_Deferrals IS
1741
1742 -- read all top group MRs from the temporary table.
1743 CURSOR ahl_unit_sr_def_csr IS
1744 SELECT
1745 unit_effectivity_id,
1746 object_type,
1747 csi_item_instance_id,
1748 mr_header_id,
1749 due_date,
1750 due_counter_value,
1751 tolerance_flag,
1752 message_code,
1753 parent_csi_item_instance_id,
1754 parent_mr_header_id,
1755 orig_csi_item_instance_id,
1756 orig_mr_header_id,
1757 orig_unit_effectivity_id,
1758 group_match_flag,
1759 counter_id
1760 FROM ahl_temp_unit_SR_deferrals
1761 WHERE orig_unit_effectivity_id IS NULL
1762 FOR UPDATE OF unit_effectivity_id;
1763
1764 -- Read group from ahl_ue_relationships.
1765 CURSOR ahl_ue_csr (p_orig_ue_id IN NUMBER) IS
1766 SELECT related_ue_id
1767 FROM ahl_ue_relationships
1768 WHERE originator_ue_id = p_orig_ue_id
1769 AND relationship_code = 'PARENT';
1770
1771 -- Read group elements.
1772 CURSOR ahl_temp_csr(p_csi_item_instance_id IN NUMBER,
1773 p_mr_header_id IN NUMBER,
1774 p_unit_effectivity_id IN NUMBER) IS
1775 SELECT unit_effectivity_id,
1776 csi_item_instance_id,
1777 MR_header_id,
1778 due_date,
1779 due_counter_value,
1780 parent_csi_item_instance_id,
1781 parent_mr_header_id,
1782 orig_csi_item_instance_id,
1783 orig_mr_header_id,
1784 tolerance_flag,
1785 message_code
1786 FROM ahl_temp_unit_SR_deferrals
1787 START WITH parent_csi_item_instance_id = p_csi_item_instance_id
1788 AND parent_mr_header_id = p_mr_header_id
1789 AND orig_csi_item_instance_id = p_csi_item_instance_id
1790 AND orig_mr_header_id = p_mr_header_id
1791 AND orig_unit_effectivity_id = p_unit_effectivity_id
1792 CONNECT BY PRIOR MR_header_id = parent_mr_header_id
1793 AND PRIOR csi_item_instance_id = parent_csi_item_instance_id
1794 AND orig_csi_item_instance_id = p_csi_item_instance_id
1795 AND orig_mr_header_id = p_mr_header_id
1796 AND orig_unit_effectivity_id = p_unit_effectivity_id
1797 FOR UPDATE OF due_date
1798 ORDER BY level;
1799
1800
1801 -- get parent unit effectivity id.
1802 CURSOR ahl_temp_parent_csr (p_parent_csi_item_instance_id IN NUMBER,
1803 p_parent_mr_header_id IN NUMBER,
1804 p_orig_csi_item_instance_id IN NUMBER,
1805 p_orig_mr_header_id IN NUMBER,
1806 p_unit_effectivity_id NUMBER) IS
1807 SELECT unit_effectivity_id
1808 FROM ahl_temp_unit_SR_deferrals
1809 WHERE csi_item_instance_id = p_parent_csi_item_instance_id
1810 AND MR_header_id = p_parent_mr_header_id
1811 AND orig_csi_item_instance_id = p_orig_csi_item_instance_id
1812 AND orig_mr_header_id = p_orig_mr_header_id
1813 AND orig_unit_effectivity_id = p_unit_effectivity_id;
1814
1815 -- Cursor to get all details of a unit effectivity record.
1816 CURSOR ahl_unit_effectivity_csr ( p_unit_effectivity_id IN NUMBER) IS
1817 SELECT
1818 UNIT_EFFECTIVITY_ID ,
1819 CSI_ITEM_INSTANCE_ID,
1820 MR_INTERVAL_ID,
1821 MR_EFFECTIVITY_ID ,
1822 MR_HEADER_ID,
1823 STATUS_CODE ,
1824 DUE_DATE ,
1825 DUE_COUNTER_VALUE ,
1826 FORECAST_SEQUENCE ,
1827 REPETITIVE_MR_FLAG ,
1828 TOLERANCE_FLAG ,
1829 REMARKS ,
1830 MESSAGE_CODE ,
1831 PRECEDING_UE_ID ,
1832 DATE_RUN ,
1833 SET_DUE_DATE ,
1834 ACCOMPLISHED_DATE ,
1835 CANCEL_REASON_CODE,
1836 EARLIEST_DUE_DATE,
1837 LATEST_DUE_DATE,
1838 SERVICE_LINE_ID,
1839 PROGRAM_MR_HEADER_ID,
1840 defer_from_ue_id,
1841 cs_incident_id,
1842 qa_collection_id,
1843 orig_deferral_ue_id,
1844 application_usg_code,
1845 object_type,
1846 counter_id,
1847 manually_planned_flag,
1848 LOG_SERIES_CODE,
1849 LOG_SERIES_NUMBER,
1850 FLIGHT_NUMBER,
1851 MEL_CDL_TYPE_CODE,
1852 POSITION_PATH_ID,
1853 ATA_CODE,
1854 UNIT_CONFIG_HEADER_ID,
1855 ATTRIBUTE_CATEGORY ,
1856 ATTRIBUTE1,
1857 ATTRIBUTE2 ,
1858 ATTRIBUTE3 ,
1859 ATTRIBUTE4 ,
1860 ATTRIBUTE5 ,
1861 ATTRIBUTE6 ,
1862 ATTRIBUTE7 ,
1863 ATTRIBUTE8 ,
1864 ATTRIBUTE9 ,
1865 ATTRIBUTE10,
1866 ATTRIBUTE11 ,
1867 ATTRIBUTE12 ,
1868 ATTRIBUTE13 ,
1869 ATTRIBUTE14 ,
1870 ATTRIBUTE15 ,
1871 OBJECT_VERSION_NUMBER
1872 FROM ahl_unit_effectivities_vl
1873 WHERE unit_effectivity_id = p_unit_effectivity_id;
1874 --FOR UPDATE OF due_date NOWAIT; -- not required as we locked the UEs before
1875
1876 -- Get all mr-terminated children under the SR-UE.
1877 CURSOR ahl_term_mr_csr (p_sr_ue_id IN NUMBER) IS
1878 SELECT unit_effectivity_id
1879 FROM ahl_unit_effectivities_b UE, ahl_ue_relationships UER
1880 WHERE UE.unit_effectivity_id = UER.related_ue_id
1881 AND UER.ue_id = p_sr_ue_id
1882 AND UE.status_code = 'MR-TERMINATE';
1883
1884 l_unit_effectivity_rec ahl_unit_effectivity_csr%ROWTYPE;
1885 l_unit_effectivity_id NUMBER;
1886 l_top_unit_effectivity_rec ahl_unit_effectivity_csr%ROWTYPE;
1887 l_parent_ue_id NUMBER;
1888 l_ue_relationship_id NUMBER;
1889 l_new_top_ue_id NUMBER;
1890 l_rowid VARCHAR2(30);
1891
1892 -- added for bug# 7586838
1893 TYPE nbr_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1894 l_delete_ue_id_tbl nbr_tbl_type;
1895 l_index number := 0;
1896
1897 l_visit_status ahl_visits_b.status_code%TYPE;
1898
1899 BEGIN
1900
1901 IF G_DEBUG = 'Y' THEN
1902 AHL_DEBUG_PUB.Debug('Start Unit Sr Deferrals');
1903 END IF;
1904
1905 FOR unit_sr_def_rec IN ahl_unit_sr_def_csr LOOP
1906 IF G_DEBUG = 'Y' THEN
1907 AHL_DEBUG_PUB.Debug('UE_ID:' || unit_sr_def_rec.unit_effectivity_id);
1908 END IF;
1909
1910 IF (unit_sr_def_rec.group_match_flag = 'Y') THEN
1911 IF G_DEBUG = 'Y' THEN
1912 AHL_DEBUG_PUB.Debug('Group flag match');
1913 END IF;
1914
1915
1916 -- update unit effectivities with due date and tolerance info.
1917 -- Update top node.
1918
1919 OPEN ahl_unit_effectivity_csr(unit_sr_def_rec.unit_effectivity_id);
1920 FETCH ahl_unit_effectivity_csr INTO l_unit_effectivity_rec;
1921 IF (ahl_unit_effectivity_csr%NOTFOUND) THEN
1922 FND_Message.Set_Name ('AHL','AHL_UMP_PUE_UE_NOTFOUND');
1923 FND_Message.set_token ('UE_ID',unit_sr_def_rec.unit_effectivity_id);
1924 FND_MSG_PUB.ADD;
1925 CLOSE ahl_unit_effectivity_csr;
1926 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1927 ELSE
1928 l_unit_effectivity_rec.tolerance_flag := unit_sr_def_rec.tolerance_flag;
1929 l_unit_effectivity_rec.message_code := unit_sr_def_rec.message_code;
1930 l_unit_effectivity_rec.due_date := unit_sr_def_rec.due_date;
1931 l_unit_effectivity_rec.due_counter_value := unit_sr_def_rec.due_counter_value;
1932 l_unit_effectivity_rec.counter_id := unit_sr_def_rec.counter_id;
1933
1934 -- update record.
1935 IF G_DEBUG = 'Y' THEN
1936 AHL_DEBUG_PUB.Debug('Update Record-' || l_unit_effectivity_rec.unit_effectivity_id);
1937 AHL_DEBUG_PUB.Debug('CSI:MR:' || l_unit_effectivity_rec.csi_item_instance_id || ',' || unit_sr_def_rec.MR_header_id);
1938 END IF;
1939
1940 AHL_UNIT_EFFECTIVITIES_PKG.Update_Row (
1941 X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_rec.unit_effectivity_id,
1942 X_CSI_ITEM_INSTANCE_ID => l_unit_effectivity_rec.csi_item_instance_id,
1943 X_MR_INTERVAL_ID => l_unit_effectivity_rec.mr_interval_id,
1944 X_MR_EFFECTIVITY_ID => l_unit_effectivity_rec.mr_effectivity_id,
1945 X_MR_HEADER_ID => l_unit_effectivity_rec.MR_header_id,
1946 X_STATUS_CODE => l_unit_effectivity_rec.status_code,
1947 X_DUE_DATE => l_unit_effectivity_rec.due_date,
1948 X_DUE_COUNTER_VALUE => l_unit_effectivity_rec.due_counter_value,
1949 X_FORECAST_SEQUENCE => l_unit_effectivity_rec.forecast_sequence,
1950 X_REPETITIVE_MR_FLAG => l_unit_effectivity_rec.repetitive_mr_flag,
1951 X_TOLERANCE_FLAG => l_unit_effectivity_rec.tolerance_flag,
1952 X_REMARKS => l_unit_effectivity_rec.remarks,
1953 X_MESSAGE_CODE => l_unit_effectivity_rec.message_code,
1954 X_PRECEDING_UE_ID => l_unit_effectivity_rec.preceding_ue_id,
1955 X_DATE_RUN => sysdate, /* date run */
1956 X_SET_DUE_DATE => l_unit_effectivity_rec.set_due_date,
1957 X_ACCOMPLISHED_DATE => l_unit_effectivity_rec.accomplished_date,
1958 X_SERVICE_LINE_ID => l_unit_effectivity_rec.service_line_id,
1959 X_PROGRAM_MR_HEADER_ID => l_unit_effectivity_rec.program_mr_header_id,
1960 X_CANCEL_REASON_CODE => l_unit_effectivity_rec.cancel_reason_code,
1961 X_EARLIEST_DUE_DATE => l_unit_effectivity_rec.earliest_due_date,
1962 X_LATEST_DUE_DATE => l_unit_effectivity_rec.latest_due_date,
1963 X_defer_from_ue_id => l_unit_effectivity_rec.defer_from_ue_id,
1964 X_cs_incident_id => l_unit_effectivity_rec.cs_incident_id,
1965 X_qa_collection_id => l_unit_effectivity_rec.qa_collection_id,
1966 X_orig_deferral_ue_id => l_unit_effectivity_rec.orig_deferral_ue_id,
1967 X_application_usg_code => l_unit_effectivity_rec.application_usg_code,
1968 X_object_type => l_unit_effectivity_rec.object_type,
1969 X_counter_id => l_unit_effectivity_rec.counter_id,
1970 X_MANUALLY_PLANNED_FLAG => l_unit_effectivity_rec.MANUALLY_PLANNED_FLAG,
1971 X_LOG_SERIES_CODE => l_unit_effectivity_rec.log_series_code,
1972 X_LOG_SERIES_NUMBER => l_unit_effectivity_rec.log_series_number,
1973 X_FLIGHT_NUMBER => l_unit_effectivity_rec.flight_number,
1974 X_MEL_CDL_TYPE_CODE => l_unit_effectivity_rec.mel_cdl_type_code,
1975 X_POSITION_PATH_ID => l_unit_effectivity_rec.position_path_id,
1976 X_ATA_CODE => l_unit_effectivity_rec.ATA_CODE,
1977 X_UNIT_CONFIG_HEADER_ID => l_unit_effectivity_rec.unit_config_header_id,
1978 X_ATTRIBUTE_CATEGORY => l_unit_effectivity_rec.ATTRIBUTE_CATEGORY,
1979 X_ATTRIBUTE1 => l_unit_effectivity_rec.ATTRIBUTE1,
1980 X_ATTRIBUTE2 => l_unit_effectivity_rec.ATTRIBUTE2,
1981 X_ATTRIBUTE3 => l_unit_effectivity_rec.ATTRIBUTE3,
1982 X_ATTRIBUTE4 => l_unit_effectivity_rec.ATTRIBUTE4,
1983 X_ATTRIBUTE5 => l_unit_effectivity_rec.ATTRIBUTE5,
1984 X_ATTRIBUTE6 => l_unit_effectivity_rec.ATTRIBUTE6,
1985 X_ATTRIBUTE7 => l_unit_effectivity_rec.ATTRIBUTE7,
1986 X_ATTRIBUTE8 => l_unit_effectivity_rec.ATTRIBUTE8,
1987 X_ATTRIBUTE9 => l_unit_effectivity_rec.ATTRIBUTE9,
1988 X_ATTRIBUTE10 => l_unit_effectivity_rec.ATTRIBUTE10,
1989 X_ATTRIBUTE11 => l_unit_effectivity_rec.ATTRIBUTE11,
1990 X_ATTRIBUTE12 => l_unit_effectivity_rec.ATTRIBUTE12,
1991 X_ATTRIBUTE13 => l_unit_effectivity_rec.ATTRIBUTE13,
1992 X_ATTRIBUTE14 => l_unit_effectivity_rec.ATTRIBUTE14,
1993 X_ATTRIBUTE15 => l_unit_effectivity_rec.ATTRIBUTE15,
1994 X_OBJECT_VERSION_NUMBER => l_unit_effectivity_rec.object_version_number+1,
1995 X_LAST_UPDATE_DATE => sysdate,
1996 X_LAST_UPDATED_BY => fnd_global.user_id,
1997 X_LAST_UPDATE_LOGIN => fnd_global.login_id );
1998 CLOSE ahl_unit_effectivity_csr;
1999
2000 IF (l_unit_effectivity_rec.due_date IS NULL
2001 AND l_unit_effectivity_rec.object_type = 'MR') THEN
2002 -- Delete the corresponding rows in ahl_schedule materials for this ue.
2003 Delete_Sch_Materials(l_unit_effectivity_rec.unit_effectivity_id);
2004 END IF;
2005
2006 -- Update all group children.
2007 FOR l_ue_rec IN ahl_ue_csr (unit_sr_def_rec.unit_effectivity_id) LOOP
2008 -- Read Unit Effectivity record.
2009 OPEN ahl_unit_effectivity_csr(l_ue_rec.related_ue_id);
2010 FETCH ahl_unit_effectivity_csr INTO l_unit_effectivity_rec;
2011 IF (ahl_unit_effectivity_csr%NOTFOUND) THEN
2012 FND_Message.Set_Name ('AHL','AHL_UMP_PUE_UE_NOTFOUND');
2013 FND_Message.set_token ('UE_ID',l_ue_rec.related_ue_id);
2014 FND_MSG_PUB.ADD;
2015 CLOSE ahl_unit_effectivity_csr;
2016 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2017 ELSE
2018 l_unit_effectivity_rec.tolerance_flag := unit_sr_def_rec.tolerance_flag;
2019 l_unit_effectivity_rec.message_code := unit_sr_def_rec.message_code;
2020 l_unit_effectivity_rec.due_date := unit_sr_def_rec.due_date;
2021 l_unit_effectivity_rec.due_counter_value := unit_sr_def_rec.due_counter_value;
2022
2023 l_unit_effectivity_rec.counter_id := unit_sr_def_rec.counter_id;
2024
2025 -- update record.
2026 IF G_DEBUG = 'Y' THEN
2027 AHL_DEBUG_PUB.Debug('Update Record-' || l_unit_effectivity_rec.unit_effectivity_id);
2028 AHL_DEBUG_PUB.Debug('CSI:MR:' || l_unit_effectivity_rec.csi_item_instance_id || ',' || l_unit_effectivity_rec.MR_header_id);
2029 END IF;
2030
2031 AHL_UNIT_EFFECTIVITIES_PKG.Update_Row (
2032 X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_rec.unit_effectivity_id,
2033 X_CSI_ITEM_INSTANCE_ID => l_unit_effectivity_rec.csi_item_instance_id,
2034 X_MR_INTERVAL_ID => l_unit_effectivity_rec.mr_interval_id,
2035 X_MR_EFFECTIVITY_ID => l_unit_effectivity_rec.mr_effectivity_id,
2036 X_MR_HEADER_ID => l_unit_effectivity_rec.MR_header_id,
2037 X_STATUS_CODE => l_unit_effectivity_rec.status_code,
2038 X_DUE_DATE => l_unit_effectivity_rec.due_date,
2039 X_DUE_COUNTER_VALUE => l_unit_effectivity_rec.due_counter_value,
2040 X_FORECAST_SEQUENCE => l_unit_effectivity_rec.forecast_sequence,
2041 X_REPETITIVE_MR_FLAG => l_unit_effectivity_rec.repetitive_mr_flag,
2042 X_TOLERANCE_FLAG => l_unit_effectivity_rec.tolerance_flag,
2043 X_REMARKS => l_unit_effectivity_rec.remarks,
2044 X_MESSAGE_CODE => l_unit_effectivity_rec.message_code,
2045 X_PRECEDING_UE_ID => l_unit_effectivity_rec.preceding_ue_id,
2046 X_DATE_RUN => sysdate, /* date run */
2047 X_SET_DUE_DATE => l_unit_effectivity_rec.set_due_date,
2048 X_ACCOMPLISHED_DATE => l_unit_effectivity_rec.accomplished_date,
2049 X_SERVICE_LINE_ID => l_unit_effectivity_rec.service_line_id,
2050 X_PROGRAM_MR_HEADER_ID => l_unit_effectivity_rec.program_mr_header_id,
2051 X_CANCEL_REASON_CODE => l_unit_effectivity_rec.cancel_reason_code,
2052 X_EARLIEST_DUE_DATE => l_unit_effectivity_rec.earliest_due_date,
2053 X_LATEST_DUE_DATE => l_unit_effectivity_rec.latest_due_date,
2054 X_defer_from_ue_id => l_unit_effectivity_rec.defer_from_ue_id,
2055 X_cs_incident_id => l_unit_effectivity_rec.cs_incident_id,
2056 X_qa_collection_id => l_unit_effectivity_rec.qa_collection_id,
2057 X_orig_deferral_ue_id => l_unit_effectivity_rec.orig_deferral_ue_id,
2058 X_application_usg_code => l_unit_effectivity_rec.application_usg_code,
2059 X_object_type => l_unit_effectivity_rec.object_type,
2060 X_counter_id => l_unit_effectivity_rec.counter_id,
2061 X_MANUALLY_PLANNED_FLAG => l_unit_effectivity_rec.MANUALLY_PLANNED_FLAG,
2062 X_LOG_SERIES_CODE => l_unit_effectivity_rec.log_series_code,
2063 X_LOG_SERIES_NUMBER => l_unit_effectivity_rec.log_series_number,
2064 X_FLIGHT_NUMBER => l_unit_effectivity_rec.flight_number,
2065 X_MEL_CDL_TYPE_CODE => l_unit_effectivity_rec.mel_cdl_type_code,
2066 X_POSITION_PATH_ID => l_unit_effectivity_rec.position_path_id,
2067 X_ATA_CODE => l_unit_effectivity_rec.ATA_CODE,
2068 X_UNIT_CONFIG_HEADER_ID => l_unit_effectivity_rec.unit_config_header_id,
2069 X_ATTRIBUTE_CATEGORY => l_unit_effectivity_rec.ATTRIBUTE_CATEGORY,
2070 X_ATTRIBUTE1 => l_unit_effectivity_rec.ATTRIBUTE1,
2071 X_ATTRIBUTE2 => l_unit_effectivity_rec.ATTRIBUTE2,
2072 X_ATTRIBUTE3 => l_unit_effectivity_rec.ATTRIBUTE3,
2073 X_ATTRIBUTE4 => l_unit_effectivity_rec.ATTRIBUTE4,
2074 X_ATTRIBUTE5 => l_unit_effectivity_rec.ATTRIBUTE5,
2075 X_ATTRIBUTE6 => l_unit_effectivity_rec.ATTRIBUTE6,
2076 X_ATTRIBUTE7 => l_unit_effectivity_rec.ATTRIBUTE7,
2077 X_ATTRIBUTE8 => l_unit_effectivity_rec.ATTRIBUTE8,
2078 X_ATTRIBUTE9 => l_unit_effectivity_rec.ATTRIBUTE9,
2079 X_ATTRIBUTE10 => l_unit_effectivity_rec.ATTRIBUTE10,
2080 X_ATTRIBUTE11 => l_unit_effectivity_rec.ATTRIBUTE11,
2081 X_ATTRIBUTE12 => l_unit_effectivity_rec.ATTRIBUTE12,
2082 X_ATTRIBUTE13 => l_unit_effectivity_rec.ATTRIBUTE13,
2083 X_ATTRIBUTE14 => l_unit_effectivity_rec.ATTRIBUTE14,
2084 X_ATTRIBUTE15 => l_unit_effectivity_rec.ATTRIBUTE15,
2085 X_OBJECT_VERSION_NUMBER => l_unit_effectivity_rec.object_version_number+1,
2086 X_LAST_UPDATE_DATE => sysdate,
2087 X_LAST_UPDATED_BY => fnd_global.user_id,
2088 X_LAST_UPDATE_LOGIN => fnd_global.login_id );
2089
2090
2091 -- Delete the corresponding rows in ahl_schedule materials for this ue.
2092 IF (l_unit_effectivity_rec.due_date IS NULL
2093 AND l_unit_effectivity_rec.object_type = 'MR') THEN
2094 Delete_Sch_Materials(l_unit_effectivity_rec.unit_effectivity_id);
2095 END IF;
2096
2097 END IF;
2098 CLOSE ahl_unit_effectivity_csr;
2099 END LOOP; -- l_ue_rec
2100
2101 END IF; -- ahl_unit_effectivity_csr
2102
2103 ELSE /* group match = 'N' */
2104 -- Create new group.
2105 OPEN ahl_unit_effectivity_csr(unit_sr_def_rec.unit_effectivity_id);
2106 FETCH ahl_unit_effectivity_csr INTO l_unit_effectivity_rec;
2107 IF (ahl_unit_effectivity_csr%NOTFOUND) THEN
2108 FND_Message.Set_Name ('AHL','AHL_UMP_PUE_UE_NOTFOUND');
2109 FND_Message.set_token ('UE_ID',unit_sr_def_rec.unit_effectivity_id);
2110 FND_MSG_PUB.ADD;
2111 CLOSE ahl_unit_effectivity_csr;
2112 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2113 ELSE
2114 l_unit_effectivity_rec.tolerance_flag := unit_sr_def_rec.tolerance_flag;
2115 l_unit_effectivity_rec.message_code := unit_sr_def_rec.message_code;
2116 l_unit_effectivity_rec.due_date := unit_sr_def_rec.due_date;
2117 l_unit_effectivity_rec.due_counter_value := unit_sr_def_rec.due_counter_value;
2118 l_unit_effectivity_rec.object_type := unit_sr_def_rec.object_type;
2119
2120 -- track old UE for deletion.
2121 l_index := l_index + 1;
2122 l_delete_ue_id_tbl(l_index) := unit_sr_def_rec.unit_effectivity_id;
2123
2124 IF G_DEBUG = 'Y' THEN
2125 AHL_DEBUG_PUB.Debug('Insert Record-');
2126 AHL_DEBUG_PUB.Debug('CSI:MR:' || l_unit_effectivity_rec.csi_item_instance_id || ',' || l_unit_effectivity_rec.MR_header_id);
2127 END IF;
2128
2129 AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row (
2130 X_ROWID => l_rowid,
2131 X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_id,
2132 X_CSI_ITEM_INSTANCE_ID => l_unit_effectivity_rec.csi_item_instance_id,
2133 X_MR_INTERVAL_ID => null,
2134 X_MR_EFFECTIVITY_ID => null,
2135 X_MR_HEADER_ID => l_unit_effectivity_rec.MR_header_id,
2136 X_STATUS_CODE => null, /* status_code */
2137 X_DUE_DATE => l_unit_effectivity_rec.due_date,
2138 X_DUE_COUNTER_VALUE => l_unit_effectivity_rec.due_counter_value,
2139 X_FORECAST_SEQUENCE => null,
2140 X_REPETITIVE_MR_FLAG => 'N',
2141 X_TOLERANCE_FLAG => l_unit_effectivity_rec.tolerance_flag,
2142 X_REMARKS => l_unit_effectivity_rec.remarks,
2143 X_MESSAGE_CODE => l_unit_effectivity_rec.message_code,
2144 X_PRECEDING_UE_ID => null, /* preceding_ue_id */
2145 X_DATE_RUN => sysdate, /* date_run */
2146 X_SET_DUE_DATE => null, /* set due date */
2147 X_ACCOMPLISHED_DATE => null, /* accomplished date */
2148 X_SERVICE_LINE_ID => l_unit_effectivity_rec.service_line_id,
2149 X_PROGRAM_MR_HEADER_ID => l_unit_effectivity_rec.program_mr_header_id,
2150 X_CANCEL_REASON_CODE => null, /* cancel_reason_code */
2151 X_EARLIEST_DUE_DATE => l_unit_effectivity_rec.earliest_due_date,
2152 X_LATEST_DUE_DATE => l_unit_effectivity_rec.latest_due_date,
2153 X_defer_from_ue_id => l_unit_effectivity_rec.defer_from_ue_id,
2154 X_cs_incident_id => l_unit_effectivity_rec.cs_incident_id,
2155 X_qa_collection_id => l_unit_effectivity_rec.qa_collection_id,
2156 X_orig_deferral_ue_id => l_unit_effectivity_rec.orig_deferral_ue_id,
2157 X_application_usg_code => l_unit_effectivity_rec.application_usg_code,
2158 X_object_type => l_unit_effectivity_rec.object_type,
2159 X_counter_id => l_unit_effectivity_rec.counter_id,
2160 X_MANUALLY_PLANNED_FLAG => l_unit_effectivity_rec.manually_planned_flag,
2161 X_LOG_SERIES_CODE => l_unit_effectivity_rec.log_series_code,
2162 X_LOG_SERIES_NUMBER => l_unit_effectivity_rec.log_series_number,
2163 X_FLIGHT_NUMBER => l_unit_effectivity_rec.flight_number,
2164 X_MEL_CDL_TYPE_CODE => l_unit_effectivity_rec.mel_cdl_type_code,
2165 X_POSITION_PATH_ID => l_unit_effectivity_rec.position_path_id,
2166 X_ATA_CODE => l_unit_effectivity_rec.ATA_CODE,
2167 X_UNIT_CONFIG_HEADER_ID => l_unit_effectivity_rec.unit_config_header_id,
2168 X_ATTRIBUTE_CATEGORY => null, /* ATTRIBUTE_CATEGORY */
2169 X_ATTRIBUTE1 => null, /* ATTRIBUTE1 */
2170 X_ATTRIBUTE2 => null, /* ATTRIBUTE2 */
2171 X_ATTRIBUTE3 => null, /* ATTRIBUTE3 */
2172 X_ATTRIBUTE4 => null, /* ATTRIBUTE4 */
2173 X_ATTRIBUTE5 => null, /* ATTRIBUTE5 */
2174 X_ATTRIBUTE6 => null, /* ATTRIBUTE6 */
2175 X_ATTRIBUTE7 => null, /* ATTRIBUTE7 */
2176 X_ATTRIBUTE8 => null, /* ATTRIBUTE8 */
2177 X_ATTRIBUTE9 => null, /* ATTRIBUTE9 */
2178 X_ATTRIBUTE10 => null, /* ATTRIBUTE10 */
2179 X_ATTRIBUTE11 => null, /* ATTRIBUTE11 */
2180 X_ATTRIBUTE12 => null, /* ATTRIBUTE12 */
2181 X_ATTRIBUTE13 => null, /* ATTRIBUTE13 */
2182 X_ATTRIBUTE14 => null, /* ATTRIBUTE14 */
2183 X_ATTRIBUTE15 => null, /* ATTRIBUTE15 */
2184 X_OBJECT_VERSION_NUMBER => 1, /* object version */
2185 X_CREATION_DATE => sysdate,
2186 X_CREATED_BY => fnd_global.user_id,
2187 X_LAST_UPDATE_DATE => sysdate,
2188 X_LAST_UPDATED_BY => fnd_global.user_id,
2189 X_LAST_UPDATE_LOGIN => fnd_global.login_id );
2190
2191 l_new_top_ue_id := l_unit_effectivity_id;
2192
2193 -- update new UE ID for top node.
2194 UPDATE ahl_temp_unit_SR_deferrals
2195 SET unit_effectivity_id = l_new_top_ue_id,
2196 object_type = l_unit_effectivity_rec.object_type
2197 WHERE CURRENT OF ahl_unit_sr_def_csr ;
2198
2199 CLOSE ahl_unit_effectivity_csr;
2200
2201 -- Associate deferral threshold to new UE if exists (deferral from UMP).
2202 UPDATE AHL_UNIT_DEFERRALS_B
2203 SET unit_effectivity_id = l_new_top_ue_id,
2204 last_update_date = sysdate,
2205 object_version_number = object_version_number + 1,
2206 LAST_UPDATED_BY = fnd_global.user_id,
2207 LAST_UPDATE_LOGIN = fnd_global.login_id
2208 WHERE unit_effectivity_id = unit_sr_def_rec.unit_effectivity_id;
2209
2210 -- Create group children.
2211 FOR ahl_temp_rec IN ahl_temp_csr (
2212 unit_sr_def_rec.csi_item_instance_id,
2213 unit_sr_def_rec.mr_header_id,
2214 unit_sr_def_rec.unit_effectivity_id)
2215 LOOP
2216 -- Initialize.
2217
2218 AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row (
2219 X_ROWID => l_rowid,
2220 X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_id,
2221 X_CSI_ITEM_INSTANCE_ID => ahl_temp_rec.csi_item_instance_id,
2222 X_MR_INTERVAL_ID => null,
2223 X_MR_EFFECTIVITY_ID => null,
2224 X_MR_HEADER_ID => ahl_temp_rec.mr_header_id,
2225 X_STATUS_CODE => null, /* status_code */
2226 X_DUE_DATE => l_unit_effectivity_rec.due_date,
2227 X_DUE_COUNTER_VALUE => l_unit_effectivity_rec.due_counter_value,
2228 X_FORECAST_SEQUENCE => null,
2229 X_REPETITIVE_MR_FLAG => 'N',
2230 X_TOLERANCE_FLAG => l_unit_effectivity_rec.tolerance_flag,
2231 X_REMARKS => l_unit_effectivity_rec.remarks,
2232 X_MESSAGE_CODE => l_unit_effectivity_rec.message_code,
2233 X_PRECEDING_UE_ID => null, /* preceding_ue_id */
2234 X_DATE_RUN => sysdate, /* date_run */
2235 X_SET_DUE_DATE => null, /* set due date */
2236 X_ACCOMPLISHED_DATE => null, /* accomplished date */
2237 X_SERVICE_LINE_ID => l_unit_effectivity_rec.service_line_id,
2238 X_PROGRAM_MR_HEADER_ID => l_unit_effectivity_rec.program_mr_header_id,
2239 X_CANCEL_REASON_CODE => null, /* cancel_reason_code */
2240 X_EARLIEST_DUE_DATE => l_unit_effectivity_rec.earliest_due_date,
2241 X_LATEST_DUE_DATE => l_unit_effectivity_rec.latest_due_date,
2242 X_defer_from_ue_id => l_unit_effectivity_rec.defer_from_ue_id,
2243 X_cs_incident_id => l_unit_effectivity_rec.cs_incident_id,
2244 X_qa_collection_id => l_unit_effectivity_rec.qa_collection_id,
2245 X_orig_deferral_ue_id => l_unit_effectivity_rec.orig_deferral_ue_id,
2246 X_application_usg_code => l_unit_effectivity_rec.application_usg_code,
2247 X_object_type => 'MR',
2248 X_counter_id => l_unit_effectivity_rec.counter_id,
2249 X_manually_planned_flag => l_unit_effectivity_rec.manually_planned_flag,
2250 X_LOG_SERIES_CODE => l_unit_effectivity_rec.log_series_code,
2251 X_LOG_SERIES_NUMBER => l_unit_effectivity_rec.log_series_number,
2252 X_FLIGHT_NUMBER => l_unit_effectivity_rec.flight_number,
2253 X_MEL_CDL_TYPE_CODE => l_unit_effectivity_rec.mel_cdl_type_code,
2254 X_POSITION_PATH_ID => l_unit_effectivity_rec.position_path_id,
2255 X_ATA_CODE => l_unit_effectivity_rec.ATA_CODE,
2256 X_UNIT_CONFIG_HEADER_ID => l_unit_effectivity_rec.unit_config_header_id,
2257 X_ATTRIBUTE_CATEGORY => null, /* ATTRIBUTE_CATEGORY */
2258 X_ATTRIBUTE1 => null, /* ATTRIBUTE1 */
2259 X_ATTRIBUTE2 => null, /* ATTRIBUTE2 */
2260 X_ATTRIBUTE3 => null, /* ATTRIBUTE3 */
2261 X_ATTRIBUTE4 => null, /* ATTRIBUTE4 */
2262 X_ATTRIBUTE5 => null, /* ATTRIBUTE5 */
2263 X_ATTRIBUTE6 => null, /* ATTRIBUTE6 */
2264 X_ATTRIBUTE7 => null, /* ATTRIBUTE7 */
2265 X_ATTRIBUTE8 => null, /* ATTRIBUTE8 */
2266 X_ATTRIBUTE9 => null, /* ATTRIBUTE9 */
2267 X_ATTRIBUTE10 => null, /* ATTRIBUTE10 */
2268 X_ATTRIBUTE11 => null, /* ATTRIBUTE11 */
2269 X_ATTRIBUTE12 => null, /* ATTRIBUTE12 */
2270 X_ATTRIBUTE13 => null, /* ATTRIBUTE13 */
2271 X_ATTRIBUTE14 => null, /* ATTRIBUTE14 */
2272 X_ATTRIBUTE15 => null, /* ATTRIBUTE15 */
2273 X_OBJECT_VERSION_NUMBER => 1, /* object version */
2274 X_CREATION_DATE => sysdate,
2275 X_CREATED_BY => fnd_global.user_id,
2276 X_LAST_UPDATE_DATE => sysdate,
2277 X_LAST_UPDATED_BY => fnd_global.user_id,
2278 X_LAST_UPDATE_LOGIN => fnd_global.login_id );
2279
2280 UPDATE ahl_temp_unit_SR_deferrals
2281 SET unit_effectivity_id = l_unit_effectivity_id,
2282 object_type = l_unit_effectivity_rec.object_type
2283 WHERE CURRENT OF ahl_temp_csr ;
2284
2285 END LOOP;
2286
2287 -- Build relationships.
2288 FOR ahl_temp_rec IN ahl_temp_csr (
2289 unit_sr_def_rec.csi_item_instance_id,
2290 unit_sr_def_rec.mr_header_id,
2291 unit_sr_def_rec.unit_effectivity_id)
2292 LOOP
2293
2294 OPEN ahl_temp_parent_csr(ahl_temp_rec.parent_csi_item_instance_id,
2295 ahl_temp_rec.parent_mr_header_id,
2296 unit_sr_def_rec.csi_item_instance_id,
2297 unit_sr_def_rec.mr_header_id,
2298 unit_sr_def_rec.unit_effectivity_id);
2299
2300 FETCH ahl_temp_parent_csr INTO l_parent_ue_id;
2301 IF (ahl_temp_parent_csr%NOTFOUND) THEN
2302 -- parent is root UE.
2303 l_parent_ue_id := l_new_top_ue_id;
2304 --FND_MESSAGE.Set_Name ('AHL','AHL_UMP_PUE_PARENT_NOTFOUND');
2305 --FND_MESSAGE.Set_Token ('INST_ID',ahl_temp_rec.csi_item_instance_id);
2306 --FND_MESSAGE.Set_Token ('MR_ID',ahl_temp_rec.mr_header_id);
2307 --FND_MSG_PUB.ADD;
2308 --CLOSE ahl_temp_parent_csr;
2309 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2310 END IF;
2311 CLOSE ahl_temp_parent_csr;
2312
2313 -- Insert into ahl_ue_relationships.
2314 AHL_UE_RELATIONSHIPS_PKG.Insert_Row(
2315 X_UE_RELATIONSHIP_ID => l_ue_relationship_id,
2316 X_UE_ID => l_parent_ue_id,
2317 X_RELATED_UE_ID => ahl_temp_rec.unit_effectivity_id,
2318 X_RELATIONSHIP_CODE => 'PARENT',
2319 X_ORIGINATOR_UE_ID => l_new_top_ue_id,
2320 X_ATTRIBUTE_CATEGORY => null, /* ATTRIBUTE_CATEGORY */
2321 X_ATTRIBUTE1 => null, /* ATTRIBUTE1 */
2322 X_ATTRIBUTE2 => null, /* ATTRIBUTE2 */
2323 X_ATTRIBUTE3 => null, /* ATTRIBUTE3 */
2324 X_ATTRIBUTE4 => null, /* ATTRIBUTE4 */
2325 X_ATTRIBUTE5 => null, /* ATTRIBUTE5 */
2326 X_ATTRIBUTE6 => null, /* ATTRIBUTE6 */
2327 X_ATTRIBUTE7 => null, /* ATTRIBUTE7 */
2328 X_ATTRIBUTE8 => null, /* ATTRIBUTE8 */
2329 X_ATTRIBUTE9 => null, /* ATTRIBUTE9 */
2330 X_ATTRIBUTE10 => null, /* ATTRIBUTE10 */
2331 X_ATTRIBUTE11 => null, /* ATTRIBUTE11 */
2332 X_ATTRIBUTE12 => null, /* ATTRIBUTE12 */
2333 X_ATTRIBUTE13 => null, /* ATTRIBUTE13 */
2334 X_ATTRIBUTE14 => null, /* ATTRIBUTE14 */
2335 X_ATTRIBUTE15 => null, /* ATTRIBUTE15 */
2336 X_OBJECT_VERSION_NUMBER => 1,
2337 X_LAST_UPDATE_DATE => sysdate,
2338 X_LAST_UPDATED_BY => fnd_global.user_id,
2339 X_CREATION_DATE => sysdate,
2340 X_CREATED_BY => fnd_global.user_id,
2341 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
2342
2343
2344 END LOOP;
2345
2346 -- If object_type = 'SR', check for any terminated MR's for immediate children of the SR-UE.
2347 -- If exists, add them to the new copied node.
2348
2349 IF (unit_sr_def_rec.object_type = 'SR') THEN
2350 FOR ahl_term_MR_rec IN ahl_term_MR_csr(unit_sr_def_rec.unit_effectivity_id) LOOP
2351 -- Get Unit effectivity details.
2352 OPEN ahl_unit_effectivity_csr(unit_sr_def_rec.unit_effectivity_id);
2353 FETCH ahl_unit_effectivity_csr INTO l_unit_effectivity_rec;
2354 IF (ahl_unit_effectivity_csr%NOTFOUND) THEN
2355 FND_Message.Set_Name ('AHL','AHL_UMP_PUE_UE_NOTFOUND');
2356 FND_Message.set_token ('UE_ID',unit_sr_def_rec.unit_effectivity_id);
2357 FND_MSG_PUB.ADD;
2358 CLOSE ahl_unit_effectivity_csr;
2359 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2360 ELSE
2361 AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row (
2362 X_ROWID => l_rowid,
2363 X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_id,
2364 X_CSI_ITEM_INSTANCE_ID => l_unit_effectivity_rec.csi_item_instance_id,
2365 X_MR_INTERVAL_ID => l_unit_effectivity_rec.MR_INTERVAL_ID,
2366 X_MR_EFFECTIVITY_ID => l_unit_effectivity_rec.MR_EFFECTIVITY_ID,
2367 X_MR_HEADER_ID => l_unit_effectivity_rec.MR_header_id,
2368 X_STATUS_CODE => l_unit_effectivity_rec.STATUS_CODE, /* status_code */
2369 X_DUE_DATE => l_unit_effectivity_rec.due_date,
2370 X_DUE_COUNTER_VALUE => l_unit_effectivity_rec.due_counter_value,
2371 X_FORECAST_SEQUENCE => l_unit_effectivity_rec.FORECAST_SEQUENCE,
2372 X_REPETITIVE_MR_FLAG => l_unit_effectivity_rec.REPETITIVE_MR_FLAG,
2373 X_TOLERANCE_FLAG => l_unit_effectivity_rec.tolerance_flag,
2374 X_REMARKS => l_unit_effectivity_rec.remarks,
2375 X_MESSAGE_CODE => l_unit_effectivity_rec.message_code,
2376 X_PRECEDING_UE_ID => l_unit_effectivity_rec.PRECEDING_UE_ID, /* preceding_ue_id */
2377 X_DATE_RUN => sysdate, /* date_run */
2378 X_SET_DUE_DATE => null, /* set due date */
2379 X_ACCOMPLISHED_DATE => l_unit_effectivity_rec.ACCOMPLISHED_DATE, /* accomplished date */
2380 X_SERVICE_LINE_ID => l_unit_effectivity_rec.service_line_id,
2381 X_PROGRAM_MR_HEADER_ID => l_unit_effectivity_rec.program_mr_header_id,
2382 X_CANCEL_REASON_CODE => l_unit_effectivity_rec.CANCEL_REASON_CODE, /* cancel_reason_code */
2383 X_EARLIEST_DUE_DATE => l_unit_effectivity_rec.earliest_due_date,
2384 X_LATEST_DUE_DATE => l_unit_effectivity_rec.latest_due_date,
2385 X_defer_from_ue_id => l_unit_effectivity_rec.defer_from_ue_id,
2386 X_cs_incident_id => l_unit_effectivity_rec.cs_incident_id,
2387 X_qa_collection_id => l_unit_effectivity_rec.qa_collection_id,
2388 X_orig_deferral_ue_id => l_unit_effectivity_rec.orig_deferral_ue_id,
2389 X_application_usg_code => l_unit_effectivity_rec.application_usg_code,
2390 X_object_type => l_unit_effectivity_rec.object_type,
2391 X_counter_id => l_unit_effectivity_rec.counter_id,
2392 x_manually_planned_flag => l_unit_effectivity_rec.manually_planned_flag,
2393 X_LOG_SERIES_CODE => l_unit_effectivity_rec.log_series_code,
2394 X_LOG_SERIES_NUMBER => l_unit_effectivity_rec.log_series_number,
2395 X_FLIGHT_NUMBER => l_unit_effectivity_rec.flight_number,
2396 X_MEL_CDL_TYPE_CODE => l_unit_effectivity_rec.mel_cdl_type_code,
2397 X_POSITION_PATH_ID => l_unit_effectivity_rec.position_path_id,
2398 X_ATA_CODE => l_unit_effectivity_rec.ATA_CODE,
2399 X_UNIT_CONFIG_HEADER_ID => l_unit_effectivity_rec.unit_config_header_id,
2400 X_ATTRIBUTE_CATEGORY => null, /* ATTRIBUTE_CATEGORY */
2401 X_ATTRIBUTE1 => null, /* ATTRIBUTE1 */
2402 X_ATTRIBUTE2 => null, /* ATTRIBUTE2 */
2403 X_ATTRIBUTE3 => null, /* ATTRIBUTE3 */
2404 X_ATTRIBUTE4 => null, /* ATTRIBUTE4 */
2405 X_ATTRIBUTE5 => null, /* ATTRIBUTE5 */
2406 X_ATTRIBUTE6 => null, /* ATTRIBUTE6 */
2407 X_ATTRIBUTE7 => null, /* ATTRIBUTE7 */
2408 X_ATTRIBUTE8 => null, /* ATTRIBUTE8 */
2409 X_ATTRIBUTE9 => null, /* ATTRIBUTE9 */
2410 X_ATTRIBUTE10 => null, /* ATTRIBUTE10 */
2411 X_ATTRIBUTE11 => null, /* ATTRIBUTE11 */
2412 X_ATTRIBUTE12 => null, /* ATTRIBUTE12 */
2413 X_ATTRIBUTE13 => null, /* ATTRIBUTE13 */
2414 X_ATTRIBUTE14 => null, /* ATTRIBUTE14 */
2415 X_ATTRIBUTE15 => null, /* ATTRIBUTE15 */
2416 X_OBJECT_VERSION_NUMBER => 1, /* object version */
2417 X_CREATION_DATE => sysdate,
2418 X_CREATED_BY => fnd_global.user_id,
2419 X_LAST_UPDATE_DATE => sysdate,
2420 X_LAST_UPDATED_BY => fnd_global.user_id,
2421 X_LAST_UPDATE_LOGIN => fnd_global.login_id );
2422
2423 CLOSE ahl_unit_effectivity_csr;
2424
2425 -- Now create the relationship record.
2426 AHL_UE_RELATIONSHIPS_PKG.Insert_Row(
2427 X_UE_RELATIONSHIP_ID => l_ue_relationship_id,
2428 X_UE_ID => l_parent_ue_id,
2429 X_RELATED_UE_ID => l_unit_effectivity_id,
2430 X_RELATIONSHIP_CODE => 'PARENT',
2431 X_ORIGINATOR_UE_ID => l_new_top_ue_id,
2432 X_ATTRIBUTE_CATEGORY => null, /* ATTRIBUTE_CATEGORY */
2433 X_ATTRIBUTE1 => null, /* ATTRIBUTE1 */
2434 X_ATTRIBUTE2 => null, /* ATTRIBUTE2 */
2435 X_ATTRIBUTE3 => null, /* ATTRIBUTE3 */
2436 X_ATTRIBUTE4 => null, /* ATTRIBUTE4 */
2437 X_ATTRIBUTE5 => null, /* ATTRIBUTE5 */
2438 X_ATTRIBUTE6 => null, /* ATTRIBUTE6 */
2439 X_ATTRIBUTE7 => null, /* ATTRIBUTE7 */
2440 X_ATTRIBUTE8 => null, /* ATTRIBUTE8 */
2441 X_ATTRIBUTE9 => null, /* ATTRIBUTE9 */
2442 X_ATTRIBUTE10 => null, /* ATTRIBUTE10 */
2443 X_ATTRIBUTE11 => null, /* ATTRIBUTE11 */
2444 X_ATTRIBUTE12 => null, /* ATTRIBUTE12 */
2445 X_ATTRIBUTE13 => null, /* ATTRIBUTE13 */
2446 X_ATTRIBUTE14 => null, /* ATTRIBUTE14 */
2447 X_ATTRIBUTE15 => null, /* ATTRIBUTE15 */
2448 X_OBJECT_VERSION_NUMBER => 1,
2449 X_LAST_UPDATE_DATE => sysdate,
2450 X_LAST_UPDATED_BY => fnd_global.user_id,
2451 X_CREATION_DATE => sysdate,
2452 X_CREATED_BY => fnd_global.user_id,
2453 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
2454
2455 END IF;
2456 END LOOP;
2457 END IF;
2458 END IF;
2459 END IF;
2460
2461 END LOOP;
2462
2463 -- process l_delete_ue_id_tbl for UE deletion.
2464 IF (l_delete_ue_id_tbl.count > 0) THEN
2465
2466 FOR i IN l_delete_ue_id_tbl.FIRST..l_delete_ue_id_tbl.LAST LOOP
2467
2468 -- check if UE assigned to a visit.
2469 l_visit_status := AHL_UMP_UTIL_PKG.get_Visit_Status (l_delete_ue_id_tbl(i));
2470
2471 -- only if visit is in planning status we must mark an exception.
2472 -- if visit is already on the floor, we do nothing.
2473 IF (l_visit_status = 'PLANNING') THEN
2474 FOR l_ue_rec IN ahl_ue_csr(l_delete_ue_id_tbl(i)) LOOP
2475 -- Delete the corresponding rows in ahl_schedule materials for this ue.
2476 Delete_Sch_Materials(l_ue_rec.related_ue_id);
2477 END LOOP;
2478
2479 -- update unit effectivity record to exception.
2480 UPDATE AHL_UNIT_EFFECTIVITIES_B
2481 SET status_code = 'EXCEPTION',
2482 message_code = 'VISIT-ASSIGN',
2483 object_version_number = object_version_number + 1,
2484 DATE_RUN = sysdate,
2485 LAST_UPDATE_DATE = sysdate,
2486 LAST_UPDATED_BY = fnd_global.user_id,
2487 LAST_UPDATE_LOGIN = fnd_global.login_id
2488 WHERE unit_effectivity_id IN (SELECT related_ue_id
2489 FROM ahl_ue_relationships
2490 WHERE originator_ue_id = l_delete_ue_id_tbl(i)
2491 AND relationship_code = 'PARENT');
2492
2493 -- Update originator UE ID.
2494 UPDATE AHL_UNIT_EFFECTIVITIES_B
2495 SET status_code = 'EXCEPTION',
2496 message_code = 'VISIT-ASSIGN',
2497 object_version_number = object_version_number + 1,
2498 DATE_RUN = sysdate,
2499 LAST_UPDATE_DATE = sysdate,
2500 LAST_UPDATED_BY = fnd_global.user_id,
2501 LAST_UPDATE_LOGIN = fnd_global.login_id
2502 WHERE unit_effectivity_id = l_delete_ue_id_tbl(i);
2503
2504 ELSIF (l_visit_status IS NULL) THEN
2505 -- delete ahl_ue_relationships
2506 FOR l_ue_rec IN ahl_ue_csr(l_delete_ue_id_tbl(i)) LOOP
2507
2508 -- delete unit effectivity record.
2509 AHL_UNIT_EFFECTIVITIES_PKG.Delete_Row(l_ue_rec.related_ue_id);
2510
2511 -- Delete the corresponding rows in ahl_schedule materials for this ue.
2512 Delete_Sch_Materials(l_ue_rec.related_ue_id);
2513
2514 END LOOP;
2515 DELETE FROM ahl_ue_relationships
2516 WHERE originator_ue_id = l_delete_ue_id_tbl(i);
2517
2518 -- delete top UE
2519 AHL_UNIT_EFFECTIVITIES_PKG.Delete_Row(l_delete_ue_id_tbl(i));
2520
2521 -- Delete the corresponding rows in ahl_schedule materials for this ue.
2522 Delete_Sch_Materials(l_delete_ue_id_tbl(i));
2523
2524 END IF; -- l_visit_status
2525 END LOOP;
2526 END IF;
2527
2528 IF G_DEBUG = 'Y' THEN
2529 AHL_DEBUG_PUB.Debug('End Unit Sr Deferrals');
2530 END IF;
2531
2532 END Flush_Unit_SR_Deferrals;
2533
2534
2535 -- Procedure to delete rows from ahl_schedule_materials.
2536 PROCEDURE Delete_Sch_Materials(p_unit_effectivity_id IN NUMBER) IS
2537
2538 CURSOR ahl_sch_material_csr (p_unit_effectivity_id IN NUMBER) IS
2539 SELECT scheduled_material_id
2540 FROM ahl_schedule_materials
2541 WHERE material_request_type = 'FORECAST'
2542 AND unit_effectivity_id = p_unit_effectivity_id
2543 FOR UPDATE NOWAIT;
2544
2545 BEGIN
2546
2547 IF G_DEBUG = 'Y' THEN
2548 AHL_DEBUG_PUB.Debug('Start Delete_Sch_Materials for UE:' || p_unit_effectivity_id,'UMP-ProcessUnit');
2549 END IF;
2550
2551 FOR sch_material_rec IN ahl_sch_material_csr(p_unit_effectivity_id) LOOP
2552 AHL_SCHEDULE_MATERIALS_PKG.delete_row(x_scheduled_material_id => sch_material_rec.scheduled_material_id);
2553
2554 END LOOP;
2555
2556 IF G_DEBUG = 'Y' THEN
2557 AHL_DEBUG_PUB.Debug('End Delete_Sch_Materials');
2558 END IF;
2559
2560 END Delete_Sch_Materials;
2561
2562
2563 END AHL_UMP_PROCESSUNIT_EXTN_PVT;