[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.8.12020000.3 2013/04/01 02:57:44 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 -- number table.
9 TYPE nbr_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
10
11 -- varchar2 table.
12 TYPE vchar_tbl_type IS TABLE OF VARCHAR2(400) INDEX BY BINARY_INTEGER;
13 -- JKJain, NR Analysis and Forecasting
14 G_SIMULATION_PLAN_ID NUMBER := NULL;
15 -- This variable holds the SIMULATION_PLAN_ID if SIMULATION_PLAN_ID is passed to Process (ALL) Unit API.
16 G_UC_HEADER_ID NUMBER ;
17 -- This variable holds the Unit config header ID if configuration has a unit Configuration.
18 TYPE number_table_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
19 TYPE Deferral_UE_Cols_Type IS RECORD(
20 UNIT_EFFECTIVITY_ID NUMBER,
21 TOLERANCE_FLAG VARCHAR2(1),
22 MESSAGE_CODE VARCHAR2(30),
23 DUE_DATE DATE,
24 DUE_COUNTER_VALUE NUMBER,
25 COUNTER_ID NUMBER );
26
27 G_SIM_UE_ID_TBL number_table_type;
28 G_SIM_UE_ID_INDEX NUMBER;
29
30 ------------------------------
31 -- Declare Local Procedures --
32 ------------------------------
33
34 -- To create ahl_unit_effectivities record.
35 PROCEDURE create_record (p_x_temp_mr_rec IN OUT NOCOPY ahl_temp_unit_effectivities%ROWTYPE);
36
37
38 -- To update ahl_unit_effectivities record.
39 PROCEDURE update_record (p_temp_mr_rec IN ahl_temp_unit_effectivities%ROWTYPE,
40 p_mr_rec IN ahl_unit_effectivities_app_v%ROWTYPE);
41
42 -- To create decendent records and build relationships if the MR is a group MR.
43 PROCEDURE create_group (p_x_temp_grp_rec IN OUT NOCOPY ahl_temp_unit_effectivities%ROWTYPE);
44
45 -- To update decendent records and update relationships if the MR is a group MR.
46 PROCEDURE update_group (p_temp_mr_rec IN ahl_temp_unit_effectivities%ROWTYPE,
47 p_mr_rec IN ahl_unit_effectivities_app_v%ROWTYPE);
48
49 -- Process deferral and SR records.
50 -- Added for 11.5.10.
51 PROCEDURE Flush_Unit_SR_Deferrals;
52
53
54 -- Procedure to delete rows from ahl_schedule_materials.
55 PROCEDURE Delete_Sch_Materials(p_unit_effectivity_id IN NUMBER);
56
57 -- Added for SB Enh
58 -- procedure to handle updates to UEs based on accomplish trigger type LOOP.
59 PROCEDURE Flush_Loop_Effectivities;
60
61 PROCEDURE Flush_Chain_Effectivities;
62 -- JKJain, NR Analysis and Forecasting
63 PROCEDURE Create_sim_ue_record (p_x_temp_sim_ue_rec IN OUT NOCOPY ahl_ue_simulations%ROWTYPE);
64 PROCEDURE Create_group_for_sim_ue (p_x_temp_sim_ue_rec IN OUT NOCOPY ahl_ue_simulations%ROWTYPE);
65 PROCEDURE create_simue_from_ue_record (p_unit_effectivity_id IN NUMBER,
66 p_originator_ue_id IN NUMBER,
67 p_parent_ue_id IN NUMBER,
68 p_deferral_ue_cols IN Deferral_UE_Cols_Type := NULL,
69 x_simulation_ue_id OUT NOCOPY NUMBER
70 );
71 ------------------------------
72 -- Define Procedures --
73 ------------------------------
74
75 -- To flush the unit effectivities created in the temporary table (ahl_temp_unit_effectivities)
76 -- by AHL_UMP_PROCESSUNIT_PVT.Process_Unit into ahl_unit_effectivities and ahl_ue_relationships.
77 -- JKJain, NR Analysis and Forecasting
78 PROCEDURE Flush_From_Temp_table (p_config_node_tbl IN AHL_UMP_PROCESSUNIT_PVT.config_node_tbl_type,
79 p_uc_header_id IN NUMBER)
80
81 IS
82
83 -- get individual MRs for item instance and top group records
84 -- from temporary table.
85 CURSOR temp_individual_mrs_csr IS
86 SELECT unit_effectivity_id,
87 csi_item_instance_id,
88 MR_header_id,
89 due_date,
90 mr_interval_id,
91 mr_effectivity_id,
92 due_counter_value,
93 parent_csi_item_instance_id,
94 parent_mr_header_id,
95 orig_csi_item_instance_id,
96 orig_mr_header_id,
97 forecast_sequence,
98 repetitive_mr_flag,
99 tolerance_flag,
100 message_code,
101 service_line_id,
102 program_mr_header_id,
103 earliest_due_date,
104 latest_due_date,
105 counter_id,
106 -- JKJain, NR Analysis and Forecasting
107 fleet_header_id,
108 -- added for SB Enh
109 accomplish_trigger_type,
110 loop_chain_seq_num,
111 start_lc_ue_id
112 FROM ahl_temp_unit_effectivities
113 WHERE MR_header_id = nvl(orig_mr_header_id, mr_header_id) AND
114 csi_item_instance_id = nvl(orig_csi_item_instance_id,csi_item_instance_id)
115 AND nvl(accomplish_trigger_type,'X') NOT IN ('LOOP', 'CHAIN')
116 ORDER by forecast_sequence ASC
117 FOR UPDATE OF unit_effectivity_id;
118
119 -- Cursor for getting all temp unit effectivities that have preceding MRs.
120 CURSOR dependent_mr_csr IS
121 SELECT preceding_mr_header_id, preceding_csi_item_instance_id,
122 preceding_forecast_seq, unit_effectivity_id
123 FROM ahl_temp_unit_effectivities tmp_ue
124 WHERE preceding_mr_header_id IS NOT NULL AND
125 preceding_csi_item_instance_id IS NOT NULL AND
126 preceding_forecast_seq IS NOT NULL
127 -- added for SB Enh: last accomplishment of MR is available in applicable mrs table.
128 AND NOT EXISTS (select 'x' from ahl_applicable_mrs appl
129 where appl.mr_header_id = tmp_ue.preceding_mr_header_id
130 and appl.csi_item_instance_id = tmp_ue.csi_item_instance_id
131 and appl.ACCOMPLISHED_UE_ID is not null);
132
133 -- Cursor for getting the preceding MR unit effectivity from temporary table.
134 -- modified for bug# 9263774 - calc due date for prior MR revisions
135 -- fix for bug# 9673770:check deferrals temp table for preceding UE id.
136 CURSOR preceding_mr_csr (p_preceding_mr_header_id IN NUMBER,
137 p_preceding_item_instance IN NUMBER,
138 p_preceding_forecast_seq IN NUMBER) IS
139 SELECT unit_effectivity_id FROM (
140 SELECT atu.unit_effectivity_id unit_effectivity_id, atu.due_date
141 FROM ahl_temp_unit_effectivities atu
142 --WHERE atu.mr_header_id = p_preceding_mr_header_id AND
143 WHERE atu.mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1
144 where mr1.title = (select mr2.title from ahl_mr_headers_b mr2
145 where mr2.mr_header_id = p_preceding_mr_header_id)
146 ) AND
147 atu.csi_item_instance_id = p_preceding_item_instance
148 --forecast_sequence = p_preceding_forecast_seq;
149 UNION
150 SELECT atu.unit_effectivity_id unit_effectivity_id, atu.due_date
151 FROM ahl_temp_unit_SR_deferrals atu
152 --WHERE atu.mr_header_id = p_preceding_mr_header_id AND
153 WHERE atu.mr_header_id IN (select mr1.mr_header_id from ahl_mr_headers_b mr1
154 where mr1.title = (select mr2.title from ahl_mr_headers_b mr2
155 where mr2.mr_header_id = p_preceding_mr_header_id)
156 ) AND
157 atu.csi_item_instance_id = p_preceding_item_instance
158 ORDER BY DUE_DATE ASC)
159 WHERE ROWNUM < 2;
160
161 -- Cursor to get all details of a unit effectivity record.
162 CURSOR ahl_unit_effectivity_csr ( p_unit_effectivity_id IN NUMBER) IS
163 SELECT
164 UNIT_EFFECTIVITY_ID ,
165 CSI_ITEM_INSTANCE_ID,
166 MR_INTERVAL_ID,
167 MR_EFFECTIVITY_ID ,
168 MR_HEADER_ID,
169 STATUS_CODE ,
170 DUE_DATE ,
171 DUE_COUNTER_VALUE ,
172 FORECAST_SEQUENCE ,
173 REPETITIVE_MR_FLAG ,
174 TOLERANCE_FLAG ,
175 REMARKS ,
176 MESSAGE_CODE ,
177 PRECEDING_UE_ID ,
178 DATE_RUN ,
179 SET_DUE_DATE ,
180 ACCOMPLISHED_DATE ,
181 SERVICE_LINE_ID,
182 PROGRAM_MR_HEADER_ID,
183 CANCEL_REASON_CODE,
184 EARLIEST_DUE_DATE,
185 LATEST_DUE_DATE,
186 defer_from_ue_id,
187 cs_incident_id,
188 qa_collection_id,
189 orig_deferral_ue_id,
190 application_usg_code,
191 object_type,
192 counter_id,
193 manually_planned_flag,
194 LOG_SERIES_CODE,
195 LOG_SERIES_NUMBER,
196 FLIGHT_NUMBER,
197 MEL_CDL_TYPE_CODE,
198 POSITION_PATH_ID,
199 ATA_CODE,
200 UNIT_CONFIG_HEADER_ID,
201 ATTRIBUTE_CATEGORY ,
202 ATTRIBUTE1,
203 ATTRIBUTE2 ,
204 ATTRIBUTE3 ,
205 ATTRIBUTE4 ,
206 ATTRIBUTE5 ,
207 ATTRIBUTE6 ,
208 ATTRIBUTE7 ,
209 ATTRIBUTE8 ,
210 ATTRIBUTE9 ,
211 ATTRIBUTE10,
212 ATTRIBUTE11 ,
213 ATTRIBUTE12 ,
214 ATTRIBUTE13 ,
215 ATTRIBUTE14 ,
216 ATTRIBUTE15 ,
217 OBJECT_VERSION_NUMBER ,
218 LAST_UPDATE_DATE ,
219 LAST_UPDATED_BY ,
220 LAST_UPDATE_LOGIN
221
222 --FROM ahl_unit_effectivities_app_v
223 FROM ahl_unit_effectivities_vl
224 WHERE unit_effectivity_id = p_unit_effectivity_id;
225 --FOR UPDATE OF preceding_ue_id, status_code NOWAIT; -- not required as these rows were locked before.
226
227 -- Cursor for reading unit effectivity records(top nodes) that have not been updated.
228 CURSOR ahl_exception_csr (p_csi_item_instance_id IN NUMBER,
229 p_date_run IN DATE) IS
230 SELECT
231 UNIT_EFFECTIVITY_ID,
232 CSI_ITEM_INSTANCE_ID,
233 MR_HEADER_ID,
234 STATUS_CODE,
235 MR_INTERVAL_ID,
236 MR_EFFECTIVITY_ID ,
237 DUE_DATE ,
238 DUE_COUNTER_VALUE ,
239 FORECAST_SEQUENCE ,
240 REPETITIVE_MR_FLAG ,
241 TOLERANCE_FLAG ,
242 REMARKS ,
243 MESSAGE_CODE ,
244 PRECEDING_UE_ID ,
245 DATE_RUN ,
246 SET_DUE_DATE ,
247 ACCOMPLISHED_DATE ,
248 SERVICE_LINE_ID,
249 PROGRAM_MR_HEADER_ID,
250 CANCEL_REASON_CODE,
251 EARLIEST_DUE_DATE,
252 LATEST_DUE_DATE,
253 defer_from_ue_id,
254 cs_incident_id,
255 qa_collection_id,
256 orig_deferral_ue_id,
257 application_usg_code,
258 object_type,
259 counter_id,
260 manually_planned_flag,
261 LOG_SERIES_CODE,
262 LOG_SERIES_NUMBER,
263 FLIGHT_NUMBER,
264 MEL_CDL_TYPE_CODE,
265 POSITION_PATH_ID,
266 ATA_CODE,
267 UNIT_CONFIG_HEADER_ID,
268 ATTRIBUTE_CATEGORY ,
269 ATTRIBUTE1,
270 ATTRIBUTE2 ,
271 ATTRIBUTE3 ,
272 ATTRIBUTE4 ,
273 ATTRIBUTE5 ,
274 ATTRIBUTE6 ,
275 ATTRIBUTE7 ,
276 ATTRIBUTE8 ,
277 ATTRIBUTE9 ,
278 ATTRIBUTE10,
279 ATTRIBUTE11 ,
280 ATTRIBUTE12 ,
281 ATTRIBUTE13 ,
282 ATTRIBUTE14 ,
283 ATTRIBUTE15 ,
284 OBJECT_VERSION_NUMBER
285
286 FROM ahl_unit_effectivities_app_v ue
287 WHERE csi_item_instance_id = p_csi_item_instance_id AND
288 ( status_code IS NULL OR
289 status_code NOT IN ('ACCOMPLISHED','TERMINATED',
290 'MR-TERMINATE', 'INIT-ACCOMPLISHED',
291 'DEFERRED', 'SR-CLOSED','CANCELLED'))
292 AND date_run < p_date_run
293 --AND defer_from_ue_id IS NULL
294 AND nvl(manually_planned_flag,'N') = 'N'
295 AND NOT EXISTS (SELECT 'x'
296 FROM ahl_ue_relationships
297 WHERE related_ue_id = ue.unit_effectivity_id);
298 -- FOR UPDATE OF message_code NOWAIT;
299 -- UMP rows are already locked by ahl_ump_processunit_pvt.lock_effectivity
300 -- proc.
301
302 -- Cursor to get all decendents for a UE.
303 CURSOR decendent_csr (p_unit_effectivity_id IN NUMBER) IS
304 SELECT ue_relationship_id, related_ue_id
305 FROM ahl_ue_relationships
306 WHERE relationship_code = 'PARENT' AND
307 originator_ue_id = p_unit_effectivity_id;
308
309 -- Cursor to check if init-due excepion still valid.
310 CURSOR exception_init_due_csr (p_unit_effectivity_id IN NUMBER) IS
311 SELECT unit_deferral_id
312 --FROM ahl_unit_thresholds
313 FROM ahl_unit_deferrals_b
314 WHERE unit_effectivity_id = p_unit_effectivity_id
315 AND unit_deferral_type = 'INIT-DUE';
316
317 -- to check if ue has child ue records.
318 CURSOR chk_child_ue_csr (p_unit_effectivity_id IN NUMBER) IS
319 SELECT 'x'
320 FROM ahl_ue_relationships
321 WHERE ue_id = p_unit_effectivity_id;
322
323 l_visit_status_code VARCHAR2(40);
324
325 l_csi_item_instance_id NUMBER;
326 l_start_time DATE;
327
328 l_temp_individual_mr_rec temp_individual_mrs_csr%ROWTYPE;
329
330 l_temp_mr_rec ahl_temp_unit_effectivities%ROWTYPE;
331 l_mr_rec ahl_unit_effectivities_app_v%ROWTYPE;
332
333 l_temp_mr_found BOOLEAN;
334 l_mr_found BOOLEAN;
335
336 l_unit_effectivity_id NUMBER;
337 l_ue_rec ahl_unit_effectivity_csr%ROWTYPE;
338
339 l_last_accomplishment_date DATE;
340 l_acc_unit_effectivity_id NUMBER;
341 l_acc_status_code ahl_unit_effectivities_app_v.status_code%TYPE;
342 l_return_val BOOLEAN;
343
344 l_exception_upd_flag BOOLEAN;
345 l_delete_flag BOOLEAN;
346 l_exception_code fnd_lookups.lookup_code%TYPE;
347 l_unit_effectivity_rec ahl_unit_effectivity_csr%ROWTYPE;
348 l_junk VARCHAR2(1);
349
350 l_incident_id NUMBER;
351 l_incident_number CS_INCIDENTS_ALL_VL.incident_number%TYPE;
352 l_scheduled_date DATE;
353
354 -- Cursor to check whether contract for exiting UE is valid(bug#4692366)
355 CURSOR valid_contract_csr (p_unit_effectivity_id IN NUMBER) IS
356 SELECT 'x' from okc_k_lines_b OKCL, ahl_unit_effectivities_b UE
357 --WHERE NVL(NVL(DATE_TERMINATED,END_DATE),SYSDATE) >= SYSDATE
358 -- Fix for bug# 5517930
359 WHERE NVL(NVL(DATE_TERMINATED,END_DATE),SYSDATE+1) >= trunc(SYSDATE)
360 AND OKCL.id = UE.service_line_id
361 AND UE.unit_effectivity_id = p_unit_effectivity_id;
362
363 -- Added for bug fix 5764351).
364 -- delete UMP rows only if due date > termination date/end date.
365 CURSOR valid_due_date_csr (p_unit_effectivity_id IN NUMBER,
366 p_due_date IN DATE) IS
367 SELECT 'x'
368 FROM okc_k_lines_b OKCL, ahl_unit_effectivities_b UE
369 WHERE trunc(p_due_date) <= NVL(DATE_TERMINATED,END_DATE)
370 AND OKCL.id = UE.service_line_id
371 AND UE.unit_effectivity_id = p_unit_effectivity_id;
372
373 -- JKJain, NR Analysis and Forecasting
374 l_fleet_header_id NUMBER;
375 -- Fix for bug# 6711228.
376 l_acc_deferral_flag BOOLEAN;
377
378 l_unit_deferral_id NUMBER;
379
380 -- added to fix bug# 9078331
381 l_min_due_date DATE;
382 l_min_ue_id NUMBER;
383 l_min_mr_due_date DATE;
384 l_min_mr_ue_id NUMBER;
385 l_min_def_flag BOOLEAN;
386 l_min_mr_flag BOOLEAN;
387
388 -- added for SB Enh
389 l_ue_id_tbl nbr_tbl_type;
390 l_mr_id_tbl nbr_tbl_type;
391 l_ii_id_tbl nbr_tbl_type;
392 l_term_mr_id_tbl nbr_tbl_type;
393 l_terminating_ue_id number;
394 l_upd_terminate_flag BOOLEAN;
395
396 -- CURSOR terminating_mr_csr (p_
397 /*
398 SELECT unit_effectivity_id INTO l_terminating_mr_header_id
399 FROM (
400 SELECT ue.unit_effectivity_id
401 BULK COLLECT INTO l_ue_id_tbl
402 FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b mr
403 WHERE ue.mr_header_id = mr.mr_header_id
404 AND (ue.status_code IS NULL OR ue.status_code = 'INIT-DUE')
405 AND mr.title IN (select title from ahl_mr_headers_b where mr_header_id = l_term_mr_id_tbl(i))
406 ORDER BY ue.due_date asc)
407 where rownum < 2;
408 */
409 BEGIN
410
411 IF G_DEBUG = 'Y' THEN
412 AHL_DEBUG_PUB.Debug('Start Flush from Temporary Table, p_uc_header_id = '||p_uc_header_id);
413 END IF;
414
415 -- JKJain, NR Analysis and Forecasting
416 G_UC_HEADER_ID := p_uc_header_id;
417
418 -- record start time.
419 l_start_time := sysdate;
420
421
422 IF (AHL_UTIL_PKG.IS_PM_INSTALLED = 'N') THEN
423 IF G_DEBUG = 'Y' THEN
424 AHL_DEBUG_PUB.Debug('Start Deferral-SR Processing');
425 END IF;
426
427 -- Process deferral and SR records.
428 Flush_Unit_SR_Deferrals;
429
430 IF G_DEBUG = 'Y' THEN
431 AHL_DEBUG_PUB.Debug('End Deferral-SR Processing');
432 END IF;
433 END IF;
434
435 -- Read temporary table.
436 FOR temp_individual_mr_rec IN temp_individual_mrs_csr LOOP
437
438 -- initialize
439 l_min_due_date := NULL;
440 l_min_ue_id := NULL;
441 l_min_mr_due_date := NULL;
442 l_min_mr_ue_id := NULL;
443 l_min_def_flag := FALSE;
444 l_min_mr_flag := FALSE;
445
446 l_temp_individual_mr_rec := temp_individual_mr_rec;
447 IF (l_temp_individual_mr_rec.unit_effectivity_id is null) THEN /* no previous record */
448
449 l_mr_found := FALSE;
450 ELSE
451 OPEN ahl_unit_effectivity_csr (l_temp_individual_mr_rec.unit_effectivity_id);
452 -- get unit effectivity record details as we need to update it.
453 FETCH ahl_unit_effectivity_csr INTO l_ue_rec;
454 IF (ahl_unit_effectivity_csr%FOUND) THEN
455 l_mr_found := TRUE;
456 -- fix for bug number 4692366
457 IF (l_ue_rec.service_line_id IS NOT NULL) THEN
458 OPEN valid_contract_csr(l_temp_individual_mr_rec.unit_effectivity_id);
459 FETCH valid_contract_csr INTO l_junk;
460 IF(valid_contract_csr%NOTFOUND)THEN
461 l_mr_found := FALSE;
462 END IF;
463 CLOSE valid_contract_csr;
464 -- end of fix for bug number 4692366
465 ELSE
466 -- this check is not need for PM flow.
467 -- added this check for case where applicable MR is not a group but associated UE is a group MR.
468 -- in this case we create a new ue.
469 IF (l_temp_individual_mr_rec.orig_mr_header_id IS NULL) THEN
470 OPEN chk_child_ue_csr(l_temp_individual_mr_rec.unit_effectivity_id);
471 FETCH chk_child_ue_csr INTO l_junk;
472 IF (chk_child_ue_csr%FOUND) THEN
473 l_mr_found := FALSE;
474 END IF;
475 CLOSE chk_child_ue_csr;
476 END IF; -- l_temp_individual_mr_rec.orig_mr_header_id
477 END IF; -- l_ue_rec.service_line_id
478 ELSE
479 l_mr_found := FALSE;
480 END IF;
481 CLOSE ahl_unit_effectivity_csr;
482
483 END IF;
484
485 -- convert cursor rowtype to table rowtype.
486 l_temp_mr_rec.unit_effectivity_id := l_temp_individual_mr_rec.unit_effectivity_id;
487 l_temp_mr_rec.csi_item_instance_id := l_temp_individual_mr_rec.csi_item_instance_id;
488 l_temp_mr_rec.MR_HEADER_ID := l_temp_individual_mr_rec.MR_header_id;
489 l_temp_mr_rec.due_date := l_temp_individual_mr_rec.due_date;
490 l_temp_mr_rec.mr_interval_id := l_temp_individual_mr_rec.mr_interval_id;
491 l_temp_mr_rec.mr_effectivity_id := l_temp_individual_mr_rec.mr_effectivity_id;
492 l_temp_mr_rec.due_counter_value := l_temp_individual_mr_rec.due_counter_value;
493 l_temp_mr_rec.parent_csi_item_instance_id := l_temp_individual_mr_rec.parent_csi_item_instance_id;
494 l_temp_mr_rec.parent_mr_header_id := l_temp_individual_mr_rec.parent_mr_header_id;
495 l_temp_mr_rec.orig_csi_item_instance_id := l_temp_individual_mr_rec.orig_csi_item_instance_id;
496 l_temp_mr_rec.orig_mr_header_id := l_temp_individual_mr_rec.orig_mr_header_id;
497 l_temp_mr_rec.forecast_sequence := l_temp_individual_mr_rec.forecast_sequence;
498 l_temp_mr_rec.repetitive_mr_flag := l_temp_individual_mr_rec.repetitive_mr_flag;
499 l_temp_mr_rec.tolerance_flag := l_temp_individual_mr_rec.tolerance_flag;
500 l_temp_mr_rec.message_code := l_temp_individual_mr_rec.message_code;
501 l_temp_mr_rec.service_line_id := l_temp_individual_mr_rec.service_line_id;
502 l_temp_mr_rec.program_mr_header_id := l_temp_individual_mr_rec.program_mr_header_id;
503 l_temp_mr_rec.earliest_due_date := l_temp_individual_mr_rec.earliest_due_date;
504 l_temp_mr_rec.latest_due_date := l_temp_individual_mr_rec.latest_due_date;
505 l_temp_mr_rec.counter_id := l_temp_individual_mr_rec.counter_id;
506
507 -- added for SB Enh.
508 -- refresh attributes used by loop / chain in case the UE is no longer associated to a loop / chain
509 l_temp_mr_rec.loop_chain_seq_num := l_temp_individual_mr_rec.loop_chain_seq_num;
510 l_temp_mr_rec.accomplish_trigger_type := l_temp_individual_mr_rec.accomplish_trigger_type;
511 l_temp_mr_rec.start_lc_ue_id := l_temp_individual_mr_rec.start_lc_ue_id;
512
513 -- JKJain, NR Analysis and Forecasting
514 l_fleet_header_id := null;
515 IF(l_temp_individual_mr_rec.fleet_header_id IS NOT NULL) THEN
516 l_temp_mr_rec.fleet_header_id := l_temp_individual_mr_rec.fleet_header_id;
517 ELSIF (G_UC_HEADER_ID IS NOT NULL) THEN
518 l_fleet_header_id := AHL_UMP_ProcessUnit_PVT.get_fleet_from_unit_asso(G_UC_HEADER_ID,l_temp_individual_mr_rec.due_date,null);
519 IF(l_fleet_header_id IS NULL) THEN
520 -- Check if Fleet Association exists with earliest due date.
521 l_fleet_header_id := AHL_UMP_ProcessUnit_PVT.get_fleet_from_unit_asso(G_UC_HEADER_ID,l_temp_individual_mr_rec.earliest_due_date,null);
522 END IF;
523 l_temp_mr_rec.fleet_header_id := l_fleet_header_id;
524 END IF;
525 -- fix for bug#9078331
526 -- find least due date and set repetitive_mr_flag
527 IF (l_temp_mr_rec.repetitive_mr_flag = 'N' AND l_temp_mr_rec.due_date IS NOT NULL) THEN
528 -- check if there is any deferral date less than due date.
529 BEGIN
530 SELECT due_date, unit_effectivity_id INTO l_min_due_date, l_min_ue_id
531 FROM (
532 SELECT def.due_date, def.unit_effectivity_id
533 FROM ahl_temp_unit_SR_deferrals def, ahl_mr_headers_b mr1
534 WHERE def.csi_item_instance_id = l_temp_mr_rec.csi_item_instance_id
535 AND def.mr_header_id = mr1.mr_header_id
536 AND mr1.title = (select mr2.title from ahl_mr_headers_b mr2 where mr2.mr_header_id = l_temp_mr_rec.MR_header_id)
537 AND def.due_date IS NOT NULL
538 AND def.deferral_effective_on IS NOT NULL
539 AND def.orig_unit_effectivity_id IS NULL
540 ORDER BY due_date asc )
541 WHERE ROWNUM < 2;
542
543 -- deferral row exists.
544 IF (l_min_due_date <= l_temp_mr_rec.due_date) THEN
545 l_temp_mr_rec.repetitive_mr_flag := 'Y';
546 l_min_def_flag := TRUE;
547 END IF;
548
549 EXCEPTION
550 WHEN NO_DATA_FOUND THEN
551 null;
552 END;
553
554 -- Check if old MR revision exists on shop floor.
555 BEGIN
556 SELECT due_date, unit_effectivity_id INTO l_min_mr_due_date, l_min_mr_ue_id
557 FROM (
558 SELECT ue.due_date, ue.unit_effectivity_id
559 FROM ahl_unit_effectivities_b ue, ahl_visit_tasks_b vts,
560 ahl_mr_headers_b mr1, ahl_mr_headers_b mr2
561 WHERE ue.csi_item_instance_id = l_temp_mr_rec.csi_item_instance_id
562 AND ue.mr_header_id = mr1.mr_header_id
563 AND mr1.title = mr2.title
564 AND mr1.version_number < mr2.version_number
565 AND mr2.mr_header_id = l_temp_mr_rec.MR_header_id
566 AND (ue.status_code IS NULL OR ue.status_code = 'INIT-DUE')
567 AND ue.due_date is not null
568 AND ue.defer_from_ue_id IS NULL -- do not pick deferrals
569 AND ue.unit_effectivity_id = vts.unit_effectivity_id
570 AND vts.status_code IN ('RELEASED', 'CLOSED')
571 AND vts.task_type_code = 'SUMMARY'
572 AND NOT EXISTS (select 'x' from ahl_ue_relationships
573 where related_ue_id = ue.unit_effectivity_id)
574 ORDER BY due_date asc )
575 WHERE ROWNUM < 2;
576
577 -- old mr revsion exists
578 IF (l_min_mr_due_date <= l_temp_mr_rec.due_date) THEN
579 l_temp_mr_rec.repetitive_mr_flag := 'Y';
580 l_min_mr_flag := TRUE;
581 END IF;
582
583 EXCEPTION
584 WHEN NO_DATA_FOUND THEN
585 null;
586 END;
587
588 IF (l_min_mr_flag = TRUE AND l_min_def_flag = TRUE) THEN
589 IF (l_min_mr_due_date <= l_min_due_date) THEN
590 -- update l_min_mr_ue_id
591 UPDATE AHL_UNIT_EFFECTIVITIES_B
592 SET REPETITIVE_MR_FLAG = 'N',
593 DATE_RUN = SYSDATE,
594 LAST_UPDATE_DATE = SYSDATE,
595 LAST_UPDATED_BY = fnd_global.user_id,
596 LAST_UPDATE_LOGIN = fnd_global.login_id
597 WHERE unit_effectivity_id = l_min_mr_ue_id;
598 -- update l_min_ue_id
599 UPDATE AHL_UNIT_EFFECTIVITIES_B
600 SET REPETITIVE_MR_FLAG = 'Y',
601 DATE_RUN = SYSDATE,
602 LAST_UPDATE_DATE = SYSDATE
603 WHERE unit_effectivity_id = l_min_ue_id;
604 ELSE
605 -- update l_min_ue_id
606 UPDATE AHL_UNIT_EFFECTIVITIES_B
607 SET REPETITIVE_MR_FLAG = 'N',
608 DATE_RUN = SYSDATE,
609 LAST_UPDATE_DATE = SYSDATE
610 WHERE unit_effectivity_id = l_min_ue_id;
611
612 -- update l_min_mr_ue_id
613 UPDATE AHL_UNIT_EFFECTIVITIES_B
614 SET REPETITIVE_MR_FLAG = 'Y',
615 DATE_RUN = SYSDATE,
616 LAST_UPDATE_DATE = SYSDATE,
617 LAST_UPDATED_BY = fnd_global.user_id,
618 LAST_UPDATE_LOGIN = fnd_global.login_id
619 WHERE unit_effectivity_id = l_min_mr_ue_id;
620 END IF;
621 ELSIF (l_min_mr_flag = FALSE AND l_min_def_flag = TRUE) THEN
622 -- update l_min_ue_id
623 UPDATE AHL_UNIT_EFFECTIVITIES_B
624 SET REPETITIVE_MR_FLAG = 'N',
625 DATE_RUN = SYSDATE,
626 LAST_UPDATE_DATE = SYSDATE
627 WHERE unit_effectivity_id = l_min_ue_id;
628 ELSIF (l_min_mr_flag = TRUE AND l_min_def_flag = FALSE) THEN
629 -- update l_min_mr_ue_id
630 UPDATE AHL_UNIT_EFFECTIVITIES_B
631 SET REPETITIVE_MR_FLAG = 'N',
632 DATE_RUN = SYSDATE,
633 LAST_UPDATE_DATE = SYSDATE,
634 LAST_UPDATED_BY = fnd_global.user_id,
635 LAST_UPDATE_LOGIN = fnd_global.login_id
636 WHERE unit_effectivity_id = l_min_mr_ue_id;
637 ELSIF (l_min_mr_flag = FALSE AND l_min_def_flag = FALSE) THEN
638 IF (l_min_ue_id IS NOT NULL) THEN
639 -- update l_min_ue_id
640 UPDATE AHL_UNIT_EFFECTIVITIES_B
641 SET REPETITIVE_MR_FLAG = 'Y',
642 DATE_RUN = SYSDATE,
643 LAST_UPDATE_DATE = SYSDATE
644 WHERE unit_effectivity_id = l_min_ue_id;
645 END IF;
646 IF (l_min_mr_ue_id IS NOT NULL) THEN
647 -- update l_min_mr_ue_id
648 UPDATE AHL_UNIT_EFFECTIVITIES_B
649 SET REPETITIVE_MR_FLAG = 'Y',
650 DATE_RUN = SYSDATE,
651 LAST_UPDATE_DATE = SYSDATE,
652 LAST_UPDATED_BY = fnd_global.user_id,
653 LAST_UPDATE_LOGIN = fnd_global.login_id
654 WHERE unit_effectivity_id = l_min_mr_ue_id;
655 END IF;
656 END IF;
657
658 END IF;
659
660 IF (l_mr_found) THEN
661 -- convert cursor rowtype to table rowtype.
662 l_mr_rec.unit_effectivity_id := l_ue_rec.unit_effectivity_id;
663 l_mr_rec.csi_item_instance_id := l_ue_rec.csi_item_instance_id;
664 l_mr_rec.MR_header_id := l_ue_rec.MR_header_id ;
665 l_mr_rec.STATUS_CODE := l_ue_rec.STATUS_CODE ;
666 l_mr_rec.SET_DUE_DATE := l_ue_rec.SET_DUE_DATE;
667 l_mr_rec.ACCOMPLISHED_DATE := l_ue_rec.ACCOMPLISHED_DATE;
668 l_mr_rec.CANCEL_REASON_CODE := l_ue_rec.CANCEL_REASON_CODE;
669 l_mr_rec.defer_from_ue_id := l_ue_rec.defer_from_ue_id;
670 l_mr_rec.cs_incident_id := l_ue_rec.cs_incident_id;
671 l_mr_rec.qa_collection_id := l_ue_rec.qa_collection_id;
672 l_mr_rec.orig_deferral_ue_id := l_ue_rec.orig_deferral_ue_id;
673 l_mr_rec.application_usg_code := l_ue_rec.application_usg_code;
674 l_mr_rec.object_type := l_ue_rec.object_type;
675 --l_mr_rec.counter_id := l_ue_rec.counter_id;
676 l_mr_rec.ATTRIBUTE_CATEGORY := l_ue_rec.ATTRIBUTE_CATEGORY;
677 l_mr_rec.ATTRIBUTE1 := l_ue_rec.ATTRIBUTE1;
678 l_mr_rec.ATTRIBUTE2 := l_ue_rec.ATTRIBUTE2;
679 l_mr_rec.ATTRIBUTE3 := l_ue_rec.ATTRIBUTE3;
680 l_mr_rec.ATTRIBUTE4 := l_ue_rec.ATTRIBUTE4;
681 l_mr_rec.ATTRIBUTE5 := l_ue_rec.ATTRIBUTE5;
682 l_mr_rec.ATTRIBUTE6 := l_ue_rec.ATTRIBUTE6;
683 l_mr_rec.ATTRIBUTE7 := l_ue_rec.ATTRIBUTE7;
684 l_mr_rec.ATTRIBUTE8 := l_ue_rec.ATTRIBUTE8;
685 l_mr_rec.ATTRIBUTE9 := l_ue_rec.ATTRIBUTE9;
686 l_mr_rec.ATTRIBUTE10 := l_ue_rec.ATTRIBUTE10;
687 l_mr_rec.ATTRIBUTE11 := l_ue_rec.ATTRIBUTE11;
688 l_mr_rec.ATTRIBUTE12 := l_ue_rec.ATTRIBUTE12;
689 l_mr_rec.ATTRIBUTE13 := l_ue_rec.ATTRIBUTE13;
690 l_mr_rec.ATTRIBUTE14 := l_ue_rec.ATTRIBUTE14;
691 l_mr_rec.ATTRIBUTE15 := l_ue_rec.ATTRIBUTE15;
692 l_mr_rec.OBJECT_VERSION_NUMBER := l_ue_rec.OBJECT_VERSION_NUMBER;
693 l_mr_rec.REMARKS := l_ue_rec.REMARKS;
694 END IF;
695
696 -- Check if l_temp_individual_mr_rec is a group mr.
697
698 IF (l_temp_individual_mr_rec.orig_mr_header_id IS NOT NULL) THEN
699
700 IF (l_mr_found) THEN
701
702 Update_group(l_temp_mr_rec,
703 l_mr_rec);
704 ELSE
705
706 Create_group(l_temp_mr_rec);
707
708 END IF;
709 ELSE /* not a group */
710 IF (l_mr_found) THEN
711 -- update unit_effectivity record with temp details.
712
713 update_record(l_temp_mr_rec,
714 l_mr_rec);
715
716 ELSE
717
718 create_record(l_temp_mr_rec);
719 -- update unit effectivity ID.
720 UPDATE ahl_temp_unit_effectivities
721 SET unit_effectivity_id = l_temp_mr_rec.unit_effectivity_id
722 WHERE CURRENT OF temp_individual_mrs_csr;
723 END IF;
724 END IF;
725
726 END LOOP; /* for temp_individual_mrs_csr */
727
728 IF G_DEBUG = 'Y' THEN
729 AHL_DEBUG_PUB.Debug('End Process of all MRs from temp table');
730 END IF;
731
732 -- bypass processing preceding_ue_ids and deferrals for Preventive Maintenance.
733 IF (AHL_UTIL_PKG.IS_PM_INSTALLED = 'N') THEN
734 -- Now read ahl_unit_effectivities and update preceding_ue_id.
735 FOR dependent_rec IN dependent_mr_csr LOOP
736 -- fix for bug# 9673770.
737 OPEN preceding_mr_csr (dependent_rec.preceding_mr_header_id,
738 dependent_rec.preceding_csi_item_instance_id,
739 dependent_rec.preceding_forecast_seq);
740 FETCH preceding_mr_csr INTO l_unit_effectivity_id;
741
742 /* ignore if not found. Accomplishment check for preceding ID has been
743 * added to cursor dependent_mr_csr
744 IF (preceding_mr_csr%NOTFOUND) THEN
745 -- check if accomplishment exists for preceding MR to get UE Id.
746 -- Fix for bug# 6711228.
747 AHL_UMP_UTIL_PKG.get_first_accomplishment(
748 dependent_rec.preceding_csi_item_instance_id,
749 dependent_rec.preceding_mr_header_id,
750 l_last_accomplishment_date,
751 l_acc_unit_effectivity_id,
752 l_acc_deferral_flag,
753 l_acc_status_code,
754 l_return_val);
755
756 IF (l_acc_unit_effectivity_id IS NOT NULL) THEN
757 l_unit_effectivity_id := l_acc_unit_effectivity_id;
758 ELSE
759 FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_PRECID_NOTFOUND');
760 FND_MESSAGE.Set_Token('UE_ID',dependent_rec.unit_effectivity_id);
761 FND_MESSAGE.Set_Token('PREC_MR',dependent_rec.preceding_mr_header_id);
762 FND_MESSAGE.Set_Token('CSI_II',dependent_rec.preceding_csi_item_instance_id);
763 FND_MSG_PUB.ADD;
764 -- dbms_output.put_line('preceding mr not found for dependent ue id in temporary table');
765 CLOSE preceding_mr_csr;
766 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
767 END IF;
768 END IF; -- preceding_mr_csr%NOTFOUND
769 */
770
771 IF (preceding_mr_csr%FOUND) THEN
772
773 OPEN ahl_unit_effectivity_csr (dependent_rec.unit_effectivity_id);
774 FETCH ahl_unit_effectivity_csr INTO l_ue_rec;
775 IF (ahl_unit_effectivity_csr%FOUND) THEN
776
777 AHL_UNIT_EFFECTIVITIES_PKG.Update_Row(
778 X_UNIT_EFFECTIVITY_ID => l_ue_rec.unit_effectivity_id,
779 X_CSI_ITEM_INSTANCE_ID => l_ue_rec.csi_item_instance_id,
780 X_MR_INTERVAL_ID => l_ue_rec.mr_interval_id,
781 X_MR_EFFECTIVITY_ID => l_ue_rec.mr_effectivity_id,
782 X_MR_HEADER_ID => l_ue_rec.mr_header_id,
783 X_STATUS_CODE => l_ue_rec.status_code,
784 X_DUE_DATE => l_ue_rec.due_date,
785 X_DUE_COUNTER_VALUE => l_ue_rec.due_counter_value,
786 X_FORECAST_SEQUENCE => l_ue_rec.forecast_sequence,
787 X_REPETITIVE_MR_FLAG => l_ue_rec.repetitive_mr_flag,
788 X_TOLERANCE_FLAG => l_ue_rec.tolerance_flag,
789 X_REMARKS => l_ue_rec.remarks,
790 X_MESSAGE_CODE => l_ue_rec.message_code,
791 X_PRECEDING_UE_ID => l_unit_effectivity_id,
792 X_DATE_RUN => l_ue_rec.date_run,
793 X_SET_DUE_DATE => l_ue_rec.set_due_date,
794 X_ACCOMPLISHED_DATE => l_ue_rec.accomplished_date,
795 X_SERVICE_LINE_ID => l_ue_rec.service_line_id,
796 X_PROGRAM_MR_HEADER_ID => l_ue_rec.program_mr_header_id,
797 X_CANCEL_REASON_CODE => l_ue_rec.cancel_reason_code,
798 X_EARLIEST_DUE_DATE => l_ue_rec.earliest_due_date,
799 X_LATEST_DUE_DATE => l_ue_rec.latest_due_date,
800 X_defer_from_ue_id => l_ue_rec.defer_from_ue_id,
801 X_cs_incident_id => l_ue_rec.cs_incident_id,
802 X_qa_collection_id => l_ue_rec.qa_collection_id,
803 X_orig_deferral_ue_id => l_ue_rec.orig_deferral_ue_id,
804 X_application_usg_code => l_ue_rec.application_usg_code,
805 X_object_type => l_ue_rec.object_type,
806 X_counter_id => l_ue_rec.counter_id,
807 X_MANUALLY_PLANNED_FLAG => l_ue_rec.MANUALLY_PLANNED_FLAG,
808 X_LOG_SERIES_CODE => l_ue_rec.log_series_code,
809 X_LOG_SERIES_NUMBER => l_ue_rec.log_series_number,
810 X_FLIGHT_NUMBER => l_ue_rec.flight_number,
811 X_MEL_CDL_TYPE_CODE => l_ue_rec.mel_cdl_type_code,
812 X_POSITION_PATH_ID => l_ue_rec.position_path_id,
813 X_ATA_CODE => l_ue_rec.ATA_CODE,
814 X_UNIT_CONFIG_HEADER_ID => G_UC_HEADER_ID,--l_ue_rec.unit_config_header_id, -- JKJain, NR Analysis and Forecasting
815 X_ATTRIBUTE_CATEGORY => l_ue_rec.attribute_category,
816 X_ATTRIBUTE1 => l_ue_rec.attribute1,
817 X_ATTRIBUTE2 => l_ue_rec.attribute2,
818 X_ATTRIBUTE3 => l_ue_rec.attribute3,
819 X_ATTRIBUTE4 => l_ue_rec.attribute4,
820 X_ATTRIBUTE5 => l_ue_rec.attribute5,
821 X_ATTRIBUTE6 => l_ue_rec.attribute6,
822 X_ATTRIBUTE7 => l_ue_rec.attribute7,
823 X_ATTRIBUTE8 => l_ue_rec.attribute8,
824 X_ATTRIBUTE9 => l_ue_rec.attribute9,
825 X_ATTRIBUTE10 => l_ue_rec.attribute10,
826 X_ATTRIBUTE11 => l_ue_rec.attribute11,
827 X_ATTRIBUTE12 => l_ue_rec.attribute12,
828 X_ATTRIBUTE13 => l_ue_rec.attribute13,
829 X_ATTRIBUTE14 => l_ue_rec.attribute14,
830 X_ATTRIBUTE15 => l_ue_rec.attribute15,
831 X_OBJECT_VERSION_NUMBER => l_ue_rec.object_version_number, -- no change to this needed.
832 X_LAST_UPDATE_DATE => sysdate,
833 X_LAST_UPDATED_BY => fnd_global.user_id,
834 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
835 ELSE
836
837 FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_UE_NOTFOUND');
838 FND_MESSAGE.Set_Token('UE_ID',l_ue_rec.unit_effectivity_id);
839 FND_MSG_PUB.ADD;
840 -- dbms_output.put_line('preceding mr not found for dependent ue id');
841 ClOSE ahl_unit_effectivity_csr;
842 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
843 END IF;
844 ClOSE ahl_unit_effectivity_csr;
845 END IF; -- preceding_mr_csr%FOUND
846 CLOSE preceding_mr_csr;
847 END LOOP;
848
849 IF G_DEBUG = 'Y' THEN
850 AHL_DEBUG_PUB.Debug('End Preceding MR processing');
851 --AHL_DEBUG_PUB.Debug('Start Deferral-SR Processing');
852 AHL_DEBUG_PUB.Debug('Start Update of Terminating MR..');
853 END IF;
854
855 -- Added for SB Enhancements for accomplishment trigger type = TERMINATED_BY
856 BEGIN
857 SELECT DISTINCT mr_header_id, csi_item_instance_id,terminating_mr_header_id
858 BULK COLLECT INTO l_mr_id_tbl, l_ii_id_tbl, l_term_mr_id_tbl
859 FROM AHL_APPLICABLE_MRS appl_mr
860 WHERE appl_mr.terminating_mr_header_id IS NOT NULL;
861 EXCEPTION
862 WHEN OTHERS THEN
863 null;
864 END;
865
866 IF (l_term_mr_id_tbl.count > 0) THEN
867 FOR i IN l_term_mr_id_tbl.first..l_term_mr_id_tbl.last LOOP
868 IF G_DEBUG = 'Y' THEN
869 AHL_DEBUG_PUB.Debug('Updating for MR:CSI:TERM_MR:' || l_mr_id_tbl(i) || ':' || l_ii_id_tbl(i) || ':' || l_term_mr_id_tbl(i));
870 END IF;
871
872 BEGIN
873
874 SELECT unit_effectivity_id INTO l_terminating_ue_id
875 FROM (
876 SELECT ue.unit_effectivity_id
877 FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b mr
878 WHERE ue.mr_header_id = mr.mr_header_id
879 AND ue.csi_item_instance_id = l_ii_id_tbl(i)
880 AND (ue.status_code IS NULL OR ue.status_code = 'INIT-DUE')
881 AND mr.title IN (select title from ahl_mr_headers_b where mr_header_id = l_term_mr_id_tbl(i))
882 AND date_run >= l_start_time
883 ORDER BY ue.due_date asc)
884 where rownum < 2;
885
886 UPDATE ahl_unit_effectivities_b
887 SET terminating_ue_id = l_terminating_ue_id
888 WHERE csi_item_instance_id = l_ii_id_tbl(i)
889 AND mr_header_id IN (select mr_header_id from ahl_mr_headers_b
890 where title IN (select title from ahl_mr_headers_b where mr_header_id = l_mr_id_tbl(i))
891 )
892 AND (status_code IS NULL OR status_code = 'INIT-DUE');
893
894 EXCEPTION
895 WHEN OTHERS THEN
896 null;
897 END;
898 END LOOP;
899 END IF;
900
901 /* moved processing to before ahl_temp_unit_effectivities to fix bug# 9078331
902 * to calculate repetitive_mr_flag.
903 -- Process deferral and SR records.
904 Flush_Unit_SR_Deferrals;
905 */
906
907 IF G_DEBUG = 'Y' THEN
908 AHL_DEBUG_PUB.Debug('End Update of Terminating MR..');
909 AHL_DEBUG_PUB.Debug('Starting Flush_Loop_Effectivities ...');
910 END IF;
911
912 -- process trigger type = 'LOOP'
913 Flush_Loop_Effectivities;
914
915 -- process trigger type = 'CHAIN'
916 Flush_Chain_Effectivities;
917
918
919 END IF; -- IF (AHL_UTIL_PKG.IS_PM_INSTALLED = 'N')
920
921 IF G_DEBUG = 'Y' THEN
922 --AHL_DEBUG_PUB.Debug('End Deferral-SR processing');
923
924 AHL_DEBUG_PUB.Debug('Start Exception Processing');
925 END IF;
926
927 -- Now read ahl_unit_effectivities
928 -- for records with date_run < l_startTime.
929
930 -- Read configuration table.
931 IF (p_config_node_tbl.COUNT > 0 ) THEN
932 FOR i IN p_config_node_tbl.FIRST..p_config_node_tbl.LAST LOOP
933 l_csi_item_instance_id := p_config_node_tbl(i).csi_item_instance_id;
934
935 FOR exception_rec IN ahl_exception_csr(l_csi_item_instance_id, l_start_time) LOOP
936
937 IF G_DEBUG = 'Y' THEN
938 AHL_DEBUG_PUB.Debug('Exception for unit effectivity ID:' || exception_rec.unit_effectivity_id);
939 END IF;
940
941 -- Initialize.
942 l_exception_upd_flag := FALSE;
943 l_delete_flag := TRUE;
944 l_upd_terminate_flag := FALSE;
945
946 /*
947 -- If status code is INIT-DUE mark as exception.
948 IF (exception_rec.status_code = 'INIT-DUE') THEN
949 l_visit_status_code := AHL_UMP_UTIL_PKG.get_visit_status (exception_rec.unit_effectivity_id);
950 IF G_DEBUG = 'Y' THEN
951 AHL_DEBUG_PUB.Debug('Visit Status Code from VWP is:' || l_visit_status_code);
952 END IF;
953
954 -- If visit is in released and closed status then, do not mark exception or deletion.
955 IF (l_visit_status_code IS NOT NULL AND l_visit_status_code = 'PLANNING') THEN
956 l_exception_upd_flag := TRUE;
957 l_exception_code := 'INIT-DUE';
958
959 IF G_DEBUG = 'Y' THEN
960 AHL_DEBUG_PUB.Debug('Exception - Init-Due');
961 END IF;
962 ELSIF (l_visit_status_code IS NULL) THEN -- not assigned to visit
963 l_delete_flag := TRUE;
964 ELSE -- on shop floor
965 -- do not update to exception status or delete.
966 l_exception_upd_flag := FALSE;
967 l_delete_flag := FALSE;
968 END IF;
969
970 ELSIF (exception_rec.status_code = 'INIT-ACCOMPLISHED') THEN
971 -- Check if accomplishments exist after init-accomplished.
972 AHL_UMP_UTIL_PKG.get_last_accomplishment(l_csi_item_instance_id,
973 exception_rec.mr_header_id,
974 l_last_accomplishment_date,
975 l_acc_unit_effectivity_id,
976 l_acc_status_code,
977 l_return_val);
978
979 IF (l_acc_unit_effectivity_id IS NULL OR
980 l_acc_unit_effectivity_id = exception_rec.unit_effectivity_id) THEN
981 l_exception_upd_flag := TRUE;
982 l_exception_code := 'INIT-ACCOMPLISHED';
983
984 IF G_DEBUG = 'Y' THEN
985 AHL_DEBUG_PUB.Debug('Exception - Init-Accomplished');
986 END IF;
987 END IF;
988
989 ELSIF (exception_rec.status_code = 'DEFERRED') THEN
990 l_exception_upd_flag := TRUE;
991 l_exception_code := 'DEFERRED';
992
993 IF G_DEBUG = 'Y' THEN
994 AHL_DEBUG_PUB.Debug('Exception - Deferred');
995 END IF;
996
997 ELSIF (exception_rec.status_code = 'EXCEPTION' AND
998 exception_rec.message_code = 'INIT-DUE'
999 -- OR exception_rec.message_code = 'DEFERRED')
1000 ) THEN
1001 -- Check if exception corrected.
1002 IF (exception_rec.set_due_date IS NOT NULL) THEN
1003 l_exception_upd_flag := TRUE;
1004 l_exception_code := exception_rec.message_code;
1005 ELSE
1006 OPEN exception_init_due_csr (exception_rec.unit_effectivity_id);
1007 FETCH exception_init_due_csr INTO l_junk;
1008 IF (exception_init_due_csr%FOUND) THEN
1009 l_exception_upd_flag := TRUE;
1010 l_exception_code := exception_rec.message_code;
1011 IF G_DEBUG = 'Y' THEN
1012 AHL_DEBUG_PUB.Debug('Exception - Init-Due');
1013 END IF;
1014 END IF;
1015 CLOSE exception_init_due_csr;
1016 END IF;
1017 END IF;
1018 */
1019
1020 -- check if assigned to visit if not flagged for exception.
1021 IF (AHL_UTIL_PKG.IS_PM_INSTALLED = 'N') THEN -- AHL Installation.
1022 l_visit_status_code := AHL_UMP_UTIL_PKG.get_visit_status (exception_rec.unit_effectivity_id);
1023 IF G_DEBUG = 'Y' THEN
1024 AHL_DEBUG_PUB.Debug('Visit Status Code from VWP is:' || l_visit_status_code);
1025 END IF;
1026
1027 -- If visit is in released and closed status then, do not mark exception or deletion.
1028 IF (l_visit_status_code IS NOT NULL) THEN
1029 IF (l_visit_status_code = 'PLANNING') THEN
1030 IF (exception_rec.status_code = 'INIT-DUE') THEN
1031 l_exception_upd_flag := TRUE;
1032 l_exception_code := 'INIT-DUE';
1033
1034 IF G_DEBUG = 'Y' THEN
1035 AHL_DEBUG_PUB.Debug('Exception - Init-Due');
1036 END IF;
1037
1038 ELSE
1039 l_exception_upd_flag := TRUE;
1040 l_exception_code := 'VISIT-ASSIGN';
1041 IF G_DEBUG = 'Y' THEN
1042 AHL_DEBUG_PUB.Debug('Exception - Visit Assign');
1043 END IF;
1044 END IF;
1045
1046 ELSE
1047 -- set no deletion if visit in released/closed status.
1048 l_delete_flag := FALSE;
1049
1050 -- fix for bug# 9078331
1051 -- set repetitive mr flag for row with min due date.
1052 -- Needed when MR new revision is not applicable.
1053 -- update all old revisions with repetitive_mr_flag = 'N' for now
1054 UPDATE ahl_unit_effectivities_b
1055 SET repetitive_mr_flag = 'N',
1056 date_run = sysdate,
1057 last_update_date = sysdate,
1058 object_version_number = object_version_number + 1,
1059 LAST_UPDATED_BY = fnd_global.user_id,
1060 LAST_UPDATE_LOGIN = fnd_global.login_id
1061 WHERE unit_effectivity_id = exception_rec.unit_effectivity_id;
1062 END IF;
1063 ELSE
1064 IF (exception_rec.defer_from_ue_id IS NULL) OR
1065 (exception_rec.status_code = 'EXCEPTION' AND exception_rec.defer_from_ue_id IS NOT NULL) THEN
1066 -- not assigned to any visit.
1067 l_delete_flag := TRUE;
1068 ELSE
1069 -- do not delete deferrals not in exception status.
1070 l_delete_flag := FALSE;
1071 l_exception_upd_flag := TRUE;
1072 -- SB Enh. Deferrals can be MR-TERMINATED
1073 l_upd_terminate_flag := TRUE;
1074 END IF;
1075 END IF;
1076 ELSE
1077 -- PM Installation.
1078 AHL_UMP_UTIL_PKG.get_ServiceRequest_Details(exception_rec.unit_effectivity_id,
1079 l_incident_id,
1080 l_incident_number,
1081 l_scheduled_date);
1082 IF G_DEBUG = 'Y' THEN
1083 AHL_DEBUG_PUB.Debug('Service request ID-NUM is:' || l_incident_id || '-' || l_incident_number);
1084 END IF;
1085
1086 IF (l_incident_id IS NOT NULL) THEN
1087 l_delete_flag := FALSE;
1088 ELSE
1089 IF (exception_rec.due_date IS NOT NULL) THEN
1090 -- check if associated service line ID has expired. If expired, do
1091 -- not delete UMP row if due date <= contract termination date.
1092 OPEN valid_due_date_csr(exception_rec.unit_effectivity_id,
1093 exception_rec.due_date);
1094 FETCH valid_due_date_csr INTO l_junk;
1095 IF(valid_due_date_csr%FOUND)THEN
1096 l_delete_flag := FALSE;
1097 IF AHL_DEBUG_PUB.G_FILE_DEBUG THEN
1098 AHL_DEBUG_PUB.Debug('Service Line Expired for UE with due date < termination/end date. Will not delete');
1099 END IF;
1100 END IF;
1101 CLOSE valid_due_date_csr;
1102 END IF; -- exception_rec.due_date IS NOT NULL
1103 END IF; -- l_incident_id IS NOT NULL
1104 END IF; -- AHL Installation.
1105
1106 IF (l_exception_upd_flag) THEN
1107 IF G_DEBUG = 'Y' THEN
1108 AHL_DEBUG_PUB.Debug('Updating exception code..');
1109 END IF;
1110
1111 -- added for SB Enh
1112 IF (l_upd_terminate_flag) THEN
1113 exception_rec.status_code := 'MR-TERMINATE';
1114 --exception_rec.message_code :=
1115 exception_rec.ACCOMPLISHED_DATE := SYSDATE;
1116 ELSE
1117 -- update unit effectivity.
1118 exception_rec.message_code := l_exception_code;
1119 exception_rec.status_code := 'EXCEPTION';
1120 END IF;
1121
1122 AHL_UNIT_EFFECTIVITIES_PKG.Update_Row(
1123 X_UNIT_EFFECTIVITY_ID => exception_rec.unit_effectivity_id,
1124 X_CSI_ITEM_INSTANCE_ID => exception_rec.csi_item_instance_id,
1125 X_MR_INTERVAL_ID => exception_rec.mr_interval_id,
1126 X_MR_EFFECTIVITY_ID => exception_rec.mr_effectivity_id,
1127 X_MR_HEADER_ID => exception_rec.mr_header_id,
1128 X_STATUS_CODE => exception_rec.status_code,
1129 X_DUE_DATE => exception_rec.due_date,
1130 X_DUE_COUNTER_VALUE => exception_rec.due_counter_value,
1131 X_FORECAST_SEQUENCE => exception_rec.forecast_sequence,
1132 X_REPETITIVE_MR_FLAG => exception_rec.repetitive_mr_flag,
1133 X_TOLERANCE_FLAG => exception_rec.tolerance_flag,
1134 X_REMARKS => exception_rec.remarks,
1135 X_MESSAGE_CODE => exception_rec.message_code,
1136 X_PRECEDING_UE_ID => exception_rec.preceding_ue_id,
1137 X_DATE_RUN => sysdate,
1138 X_SET_DUE_DATE => exception_rec.set_due_date,
1139 X_ACCOMPLISHED_DATE => exception_rec.accomplished_date,
1140 X_SERVICE_LINE_ID => exception_rec.service_line_id,
1141 X_PROGRAM_MR_HEADER_ID => exception_rec.program_mr_header_id,
1142 X_CANCEL_REASON_CODE => exception_rec.cancel_reason_code,
1143 X_EARLIEST_DUE_DATE => exception_rec.earliest_due_date,
1144 X_LATEST_DUE_DATE => exception_rec.latest_due_date,
1145 X_defer_from_ue_id => exception_rec.defer_from_ue_id,
1146 X_cs_incident_id => exception_rec.cs_incident_id,
1147 X_qa_collection_id => exception_rec.qa_collection_id,
1148 X_orig_deferral_ue_id => exception_rec.orig_deferral_ue_id,
1149 X_application_usg_code => exception_rec.application_usg_code,
1150 X_object_type => exception_rec.object_type,
1151 X_counter_id => exception_rec.counter_id,
1152 X_MANUALLY_PLANNED_FLAG => exception_rec.MANUALLY_PLANNED_FLAG,
1153 X_LOG_SERIES_CODE => exception_rec.log_series_code,
1154 X_LOG_SERIES_NUMBER => exception_rec.log_series_number,
1155 X_FLIGHT_NUMBER => exception_rec.flight_number,
1156 X_MEL_CDL_TYPE_CODE => exception_rec.mel_cdl_type_code,
1157 X_POSITION_PATH_ID => exception_rec.position_path_id,
1158 X_ATA_CODE => exception_rec.ATA_CODE,
1159 X_UNIT_CONFIG_HEADER_ID => G_UC_HEADER_ID,--exception_rec.unit_config_header_id,-- JKJain, NR Analysis and Forecasting
1160 X_ATTRIBUTE_CATEGORY => exception_rec.attribute_category,
1161 X_ATTRIBUTE1 => exception_rec.attribute1,
1162 X_ATTRIBUTE2 => exception_rec.attribute2,
1163 X_ATTRIBUTE3 => exception_rec.attribute3,
1164 X_ATTRIBUTE4 => exception_rec.attribute4,
1165 X_ATTRIBUTE5 => exception_rec.attribute5,
1166 X_ATTRIBUTE6 => exception_rec.attribute6,
1167 X_ATTRIBUTE7 => exception_rec.attribute7,
1168 X_ATTRIBUTE8 => exception_rec.attribute8,
1169 X_ATTRIBUTE9 => exception_rec.attribute9,
1170 X_ATTRIBUTE10 => exception_rec.attribute10,
1171 X_ATTRIBUTE11 => exception_rec.attribute11,
1172 X_ATTRIBUTE12 => exception_rec.attribute12,
1173 X_ATTRIBUTE13 => exception_rec.attribute13,
1174 X_ATTRIBUTE14 => exception_rec.attribute14,
1175 X_ATTRIBUTE15 => exception_rec.attribute15,
1176 X_OBJECT_VERSION_NUMBER => exception_rec.object_version_number + 1,
1177 X_LAST_UPDATE_DATE => sysdate,
1178 X_LAST_UPDATED_BY => fnd_global.user_id,
1179 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
1180
1181 -- Delete the corresponding rows in ahl_schedule materials for this ue.
1182 Delete_Sch_Materials(exception_rec.unit_effectivity_id);
1183
1184 -- update all group element's status too.
1185 FOR ue_reln_rec IN decendent_csr(exception_rec.unit_effectivity_id) LOOP
1186
1187 OPEN ahl_unit_effectivity_csr (ue_reln_rec.related_ue_id);
1188 FETCH ahl_unit_effectivity_csr INTO l_ue_rec;
1189 IF (ahl_unit_effectivity_csr%FOUND) THEN
1190 AHL_UNIT_EFFECTIVITIES_PKG.Update_Row(
1191 X_UNIT_EFFECTIVITY_ID => l_ue_rec.unit_effectivity_id,
1192 X_CSI_ITEM_INSTANCE_ID => l_ue_rec.csi_item_instance_id,
1193 X_MR_INTERVAL_ID => l_ue_rec.mr_interval_id,
1194 X_MR_EFFECTIVITY_ID => l_ue_rec.mr_effectivity_id,
1195 X_MR_HEADER_ID => l_ue_rec.mr_header_id,
1196 X_STATUS_CODE => exception_rec.status_code,
1197 X_DUE_DATE => l_ue_rec.due_date,
1198 X_DUE_COUNTER_VALUE => l_ue_rec.due_counter_value,
1199 X_FORECAST_SEQUENCE => l_ue_rec.forecast_sequence,
1200 X_REPETITIVE_MR_FLAG => l_ue_rec.repetitive_mr_flag,
1201 X_TOLERANCE_FLAG => l_ue_rec.tolerance_flag,
1202 X_REMARKS => l_ue_rec.remarks,
1203 X_MESSAGE_CODE => l_ue_rec.message_code,
1204 X_PRECEDING_UE_ID => l_ue_rec.preceding_ue_id,
1205 X_DATE_RUN => sysdate,
1206 X_SET_DUE_DATE => l_ue_rec.set_due_date,
1207 X_ACCOMPLISHED_DATE => l_ue_rec.accomplished_date,
1208 X_SERVICE_LINE_ID => l_ue_rec.service_line_id,
1209 X_PROGRAM_MR_HEADER_ID => l_ue_rec.program_mr_header_id,
1210 X_CANCEL_REASON_CODE => l_ue_rec.cancel_reason_code,
1211 X_EARLIEST_DUE_DATE => l_ue_rec.earliest_due_date,
1212 X_LATEST_DUE_DATE => l_ue_rec.latest_due_date,
1213 X_defer_from_ue_id => l_ue_rec.defer_from_ue_id,
1214 X_cs_incident_id => l_ue_rec.cs_incident_id,
1215 X_qa_collection_id => l_ue_rec.qa_collection_id,
1216 X_orig_deferral_ue_id => l_ue_rec.orig_deferral_ue_id,
1217 X_application_usg_code => l_ue_rec.application_usg_code,
1218 X_object_type => l_ue_rec.object_type,
1219 X_counter_id => l_ue_rec.counter_id,
1220 X_MANUALLY_PLANNED_FLAG => l_ue_rec.MANUALLY_PLANNED_FLAG,
1221 X_LOG_SERIES_CODE => l_ue_rec.log_series_code,
1222 X_LOG_SERIES_NUMBER => l_ue_rec.log_series_number,
1223 X_FLIGHT_NUMBER => l_ue_rec.flight_number,
1224 X_MEL_CDL_TYPE_CODE => l_ue_rec.mel_cdl_type_code,
1225 X_POSITION_PATH_ID => l_ue_rec.position_path_id,
1226 X_ATA_CODE => l_ue_rec.ATA_CODE,
1227 X_UNIT_CONFIG_HEADER_ID => G_UC_HEADER_ID, --l_ue_rec.unit_config_header_id,
1228 X_ATTRIBUTE_CATEGORY => l_ue_rec.attribute_category,
1229 X_ATTRIBUTE1 => l_ue_rec.attribute1,
1230 X_ATTRIBUTE2 => l_ue_rec.attribute2,
1231 X_ATTRIBUTE3 => l_ue_rec.attribute3,
1232 X_ATTRIBUTE4 => l_ue_rec.attribute4,
1233 X_ATTRIBUTE5 => l_ue_rec.attribute5,
1234 X_ATTRIBUTE6 => l_ue_rec.attribute6,
1235 X_ATTRIBUTE7 => l_ue_rec.attribute7,
1236 X_ATTRIBUTE8 => l_ue_rec.attribute8,
1237 X_ATTRIBUTE9 => l_ue_rec.attribute9,
1238 X_ATTRIBUTE10 => l_ue_rec.attribute10,
1239 X_ATTRIBUTE11 => l_ue_rec.attribute11,
1240 X_ATTRIBUTE12 => l_ue_rec.attribute12,
1241 X_ATTRIBUTE13 => l_ue_rec.attribute13,
1242 X_ATTRIBUTE14 => l_ue_rec.attribute14,
1243 X_ATTRIBUTE15 => l_ue_rec.attribute15,
1244 X_OBJECT_VERSION_NUMBER => l_ue_rec.object_version_number + 1,
1245 X_LAST_UPDATE_DATE => sysdate,
1246 X_LAST_UPDATED_BY => fnd_global.user_id,
1247 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
1248
1249 -- Delete the corresponding rows in ahl_schedule materials for this ue.
1250 Delete_Sch_Materials(l_ue_rec.unit_effectivity_id);
1251
1252 ELSE
1253 FND_MESSAGE.Set_Name('AHL','AHL_UMP_PUE_UE_NOTFOUND');
1254 FND_MESSAGE.Set_Token('UE_ID',l_ue_rec.unit_effectivity_id);
1255 FND_MSG_PUB.ADD;
1256 -- dbms_output.put_line('preceding mr not found for dependent ue id');
1257 ClOSE ahl_unit_effectivity_csr;
1258 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1259 END IF;
1260 ClOSE ahl_unit_effectivity_csr;
1261 END LOOP;
1262
1263 ELSIF (l_delete_flag) THEN
1264
1265 IF G_DEBUG = 'Y' THEN
1266 AHL_DEBUG_PUB.Debug('Deleting exception code..');
1267 END IF;
1268
1269 FOR ue_reln_rec IN decendent_csr(exception_rec.unit_effectivity_id) LOOP
1270
1271 -- delete relationship.
1272 AHL_UE_RELATIONSHIPS_PKG.Delete_Row (ue_reln_rec.ue_relationship_id);
1273 -- delete unit effectivity record.
1274 AHL_UNIT_EFFECTIVITIES_PKG.Delete_Row(ue_reln_rec.related_ue_id);
1275
1276 -- Delete the corresponding rows in ahl_schedule materials for this ue.
1277 Delete_Sch_Materials(ue_reln_rec.related_ue_id);
1278
1279
1280 END LOOP;
1281
1282 -- Delete the corresponding rows in ahl_schedule materials for this ue.
1283 Delete_Sch_Materials(exception_rec.unit_effectivity_id);
1284
1285 IF (exception_rec.status_code = 'INIT-DUE' OR
1286 exception_rec.message_code = 'INIT-DUE') THEN
1287 IF G_DEBUG = 'Y' THEN
1288 AHL_DEBUG_PUB.Debug('Exception - Init-Due');
1289 END IF;
1290
1291 OPEN exception_init_due_csr (exception_rec.unit_effectivity_id);
1292 FETCH exception_init_due_csr INTO l_unit_deferral_id;
1293 IF (exception_init_due_csr%FOUND) THEN
1294 DELETE from ahl_unit_thresholds
1295 WHERE unit_deferral_id = l_unit_deferral_id;
1296
1297 AHL_UNIT_DEFERRALS_PKG.Delete_Row(l_unit_deferral_id);
1298 END IF;
1299 CLOSE exception_init_due_csr;
1300
1301 END IF; -- exception_rec.status_code
1302
1303 -- delete ue.
1304 AHL_UNIT_EFFECTIVITIES_PKG.Delete_Row(exception_rec.unit_effectivity_id);
1305 END IF;
1306
1307 END LOOP; /* for exception rec */
1308 END LOOP; /* for node */
1309 END IF;
1310
1311 --AJPRASAN:: Fix for bug# 13085043
1312 UPDATE ahl_simulation_plans_b
1313 SET last_bue_run = SYSDATE, object_version_number = object_version_number + 1
1314 WHERE primary_plan_flag = 'Y'
1315 AND status_code = 'ACTIVE'
1316 AND simulation_type = 'UMP';
1317
1318 IF G_DEBUG = 'Y' THEN
1319 AHL_DEBUG_PUB.Debug('End Flush from temporary table.');
1320 END IF;
1321
1322
1323 END Flush_From_Temp_table;
1324
1325 -----------------------------------------------------------------------------
1326 -- To create ahl_unit_effectivities record.
1327
1328 PROCEDURE create_record (p_x_temp_mr_rec IN OUT NOCOPY ahl_temp_unit_effectivities%ROWTYPE)
1329
1330 IS
1331 l_unit_effectivity_id NUMBER;
1332 l_rowid VARCHAR2(30);
1333
1334 BEGIN
1335
1336 IF G_DEBUG = 'Y' THEN
1337 AHL_DEBUG_PUB.Debug('Start Create Record');
1338 AHL_DEBUG_PUB.Debug('CSI:MR:' || p_x_temp_mr_rec.csi_item_instance_id || ',' || p_x_temp_mr_rec.MR_header_id);
1339 END IF;
1340
1341 -- Default object type and application usage values.
1342
1343 AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row (
1344 X_ROWID => l_rowid,
1345 X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_id,
1346 X_CSI_ITEM_INSTANCE_ID => p_x_temp_mr_rec.csi_item_instance_id,
1347 X_MR_INTERVAL_ID => p_x_temp_mr_rec.mr_interval_id,
1348 X_MR_EFFECTIVITY_ID => p_x_temp_mr_rec.mr_effectivity_id,
1349 X_MR_HEADER_ID => p_x_temp_mr_rec.MR_header_id,
1350 X_STATUS_CODE => null, /* status_code */
1351 X_DUE_DATE => p_x_temp_mr_rec.due_date,
1352 X_DUE_COUNTER_VALUE => p_x_temp_mr_rec.due_counter_value,
1353 X_FORECAST_SEQUENCE => p_x_temp_mr_rec.forecast_sequence,
1354 X_REPETITIVE_MR_FLAG => p_x_temp_mr_rec.repetitive_mr_flag,
1355 X_TOLERANCE_FLAG => p_x_temp_mr_rec.tolerance_flag,
1356 X_REMARKS => null, /* remarks */
1357 X_MESSAGE_CODE => p_x_temp_mr_rec.message_code,
1358 X_PRECEDING_UE_ID => null, /* p_x_temp_mr_rec.preceding_ue_id */
1359 X_DATE_RUN => sysdate, /* date_run */
1360 X_SET_DUE_DATE => null, /* set due date */
1361 X_ACCOMPLISHED_DATE => null, /* accomplished date */
1362 X_SERVICE_LINE_ID => p_x_temp_mr_rec.service_line_id,
1363 X_PROGRAM_MR_HEADER_ID => p_x_temp_mr_rec.program_mr_header_id,
1364 X_CANCEL_REASON_CODE => null, /* cancel_reason_code */
1365 X_EARLIEST_DUE_DATE => p_x_temp_mr_rec.earliest_due_date,
1366 X_LATEST_DUE_DATE => p_x_temp_mr_rec.latest_due_date,
1367 X_defer_from_ue_id => null,
1368 X_cs_incident_id => null,
1369 X_qa_collection_id => null,
1370 X_orig_deferral_ue_id => null,
1371 X_application_usg_code => G_APPLN_USAGE_CODE,
1372 X_object_type => 'MR',
1373 X_counter_id => p_x_temp_mr_rec.counter_id,
1374 X_MANUALLY_PLANNED_FLAG => 'N',
1375 X_LOG_SERIES_CODE => NULL,
1376 X_LOG_SERIES_NUMBER => NULL,
1377 X_FLIGHT_NUMBER => NULL,
1378 X_MEL_CDL_TYPE_CODE => NULL,
1379 X_POSITION_PATH_ID => NULL,
1380 X_ATA_CODE => NULL,
1381 X_UNIT_CONFIG_HEADER_ID => G_UC_HEADER_ID, -- JKJain, NR Analysis and Forecasting
1382 X_ATTRIBUTE_CATEGORY => null, /* ATTRIBUTE_CATEGORY */
1383 X_ATTRIBUTE1 => null, /* ATTRIBUTE1 */
1384 X_ATTRIBUTE2 => null, /* ATTRIBUTE2 */
1385 X_ATTRIBUTE3 => null, /* ATTRIBUTE3 */
1386 X_ATTRIBUTE4 => null, /* ATTRIBUTE4 */
1387 X_ATTRIBUTE5 => null, /* ATTRIBUTE5 */
1388 X_ATTRIBUTE6 => null, /* ATTRIBUTE6 */
1389 X_ATTRIBUTE7 => null, /* ATTRIBUTE7 */
1390 X_ATTRIBUTE8 => null, /* ATTRIBUTE8 */
1391 X_ATTRIBUTE9 => null, /* ATTRIBUTE9 */
1392 X_ATTRIBUTE10 => null, /* ATTRIBUTE10 */
1393 X_ATTRIBUTE11 => null, /* ATTRIBUTE11 */
1394 X_ATTRIBUTE12 => null, /* ATTRIBUTE12 */
1395 X_ATTRIBUTE13 => null, /* ATTRIBUTE13 */
1396 X_ATTRIBUTE14 => null, /* ATTRIBUTE14 */
1397 X_ATTRIBUTE15 => null, /* ATTRIBUTE15 */
1398 X_OBJECT_VERSION_NUMBER => 1, /* object version */
1399 X_CREATION_DATE => sysdate,
1400 X_CREATED_BY => fnd_global.user_id,
1401 X_LAST_UPDATE_DATE => sysdate,
1402 X_LAST_UPDATED_BY => fnd_global.user_id,
1403 X_LAST_UPDATE_LOGIN => fnd_global.login_id,
1404 -- JKJain, NR Analysis and Forecasting
1405 X_FLEET_HEADER_ID => p_x_temp_mr_rec.fleet_header_id);
1406
1407 p_x_temp_mr_rec.unit_effectivity_id := l_unit_effectivity_id;
1408
1409 -- added for SB Enh - only for top node.
1410 UPDATE AHL_UNIT_EFFECTIVITIES_B
1411 SET accomplish_trigger_type = p_x_temp_mr_rec.accomplish_trigger_type,
1412 loop_chain_seq_num = p_x_temp_mr_rec.loop_chain_seq_num,
1413 start_lc_ue_id = p_x_temp_mr_rec.start_lc_ue_id
1414 WHERE rowid = l_rowid;
1415
1416 IF G_DEBUG = 'Y' THEN
1417 AHL_DEBUG_PUB.Debug('End Create Record');
1418 END IF;
1419
1420
1421 EXCEPTION
1422 -- If any error occurs, then, abort API.
1423 WHEN NO_DATA_FOUND THEN
1424 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1425 FND_MSG_PUB.ADD;
1426 RAISE FND_API.G_EXC_ERROR;
1427
1428 END create_record;
1429
1430
1431 -----------------------------------------------------------------------------
1432 -- To update ahl_unit_effectivities record.
1433
1434 PROCEDURE update_record (p_temp_mr_rec IN ahl_temp_unit_effectivities%ROWTYPE,
1435 p_mr_rec IN ahl_unit_effectivities_app_v%ROWTYPE)
1436 IS
1437
1438 BEGIN
1439
1440 IF G_DEBUG = 'Y' THEN
1441 AHL_DEBUG_PUB.Debug('Start Update Record-' || p_mr_rec.unit_effectivity_id);
1442 AHL_DEBUG_PUB.Debug('CSI:MR:' || p_mr_rec.csi_item_instance_id || ',' || p_mr_rec.MR_header_id);
1443 END IF;
1444
1445 AHL_UNIT_EFFECTIVITIES_PKG.Update_Row (
1446 X_UNIT_EFFECTIVITY_ID => p_mr_rec.unit_effectivity_id,
1447 X_CSI_ITEM_INSTANCE_ID => p_mr_rec.csi_item_instance_id,
1448 X_MR_INTERVAL_ID => p_temp_mr_rec.mr_interval_id,
1449 X_MR_EFFECTIVITY_ID => p_temp_mr_rec.mr_effectivity_id,
1450 X_MR_HEADER_ID => p_mr_rec.MR_header_id,
1451 X_STATUS_CODE => p_mr_rec.status_code,
1452 X_DUE_DATE => p_temp_mr_rec.due_date,
1453 X_DUE_COUNTER_VALUE => p_temp_mr_rec.due_counter_value,
1454 X_FORECAST_SEQUENCE => p_temp_mr_rec.forecast_sequence,
1455 X_REPETITIVE_MR_FLAG => p_temp_mr_rec.repetitive_mr_flag,
1456 X_TOLERANCE_FLAG => p_temp_mr_rec.tolerance_flag,
1457 X_REMARKS => p_mr_rec.remarks,
1458 X_MESSAGE_CODE => p_temp_mr_rec.message_code,
1459 X_PRECEDING_UE_ID => null, /* preceding_ue_id */
1460 X_DATE_RUN => sysdate, /* date run */
1461 X_SET_DUE_DATE => p_mr_rec.set_due_date,
1462 X_ACCOMPLISHED_DATE => p_mr_rec.accomplished_date,
1463 X_SERVICE_LINE_ID => p_temp_mr_rec.service_line_id,
1464 X_PROGRAM_MR_HEADER_ID => p_temp_mr_rec.program_mr_header_id,
1465 X_CANCEL_REASON_CODE => p_mr_rec.cancel_reason_code,
1466 X_EARLIEST_DUE_DATE => p_temp_mr_rec.earliest_due_date,
1467 X_LATEST_DUE_DATE => p_temp_mr_rec.latest_due_date,
1468 X_defer_from_ue_id => p_mr_rec.defer_from_ue_id,
1469 X_cs_incident_id => p_mr_rec.cs_incident_id,
1470 X_qa_collection_id => p_mr_rec.qa_collection_id,
1471 X_orig_deferral_ue_id => p_mr_rec.orig_deferral_ue_id,
1472 X_application_usg_code => p_mr_rec.application_usg_code,
1473 X_object_type => p_mr_rec.object_type,
1474 X_counter_id => p_temp_mr_rec.counter_id,
1475 X_MANUALLY_PLANNED_FLAG => p_mr_rec.MANUALLY_PLANNED_FLAG,
1476 X_LOG_SERIES_CODE => p_mr_rec.log_series_code,
1477 X_LOG_SERIES_NUMBER => p_mr_rec.log_series_number,
1478 X_FLIGHT_NUMBER => p_mr_rec.flight_number,
1479 X_MEL_CDL_TYPE_CODE => p_mr_rec.mel_cdl_type_code,
1480 X_POSITION_PATH_ID => p_mr_rec.position_path_id,
1481 X_ATA_CODE => p_mr_rec.ATA_CODE,
1482 X_UNIT_CONFIG_HEADER_ID => G_UC_HEADER_ID, --p_mr_rec.unit_config_header_id,
1483 X_ATTRIBUTE_CATEGORY => p_mr_rec.ATTRIBUTE_CATEGORY,
1484 X_ATTRIBUTE1 => p_mr_rec.ATTRIBUTE1,
1485 X_ATTRIBUTE2 => p_mr_rec.ATTRIBUTE2,
1486 X_ATTRIBUTE3 => p_mr_rec.ATTRIBUTE3,
1487 X_ATTRIBUTE4 => p_mr_rec.ATTRIBUTE4,
1488 X_ATTRIBUTE5 => p_mr_rec.ATTRIBUTE5,
1489 X_ATTRIBUTE6 => p_mr_rec.ATTRIBUTE6,
1490 X_ATTRIBUTE7 => p_mr_rec.ATTRIBUTE7,
1491 X_ATTRIBUTE8 => p_mr_rec.ATTRIBUTE8,
1492 X_ATTRIBUTE9 => p_mr_rec.ATTRIBUTE9,
1493 X_ATTRIBUTE10 => p_mr_rec.ATTRIBUTE10,
1494 X_ATTRIBUTE11 => p_mr_rec.ATTRIBUTE11,
1495 X_ATTRIBUTE12 => p_mr_rec.ATTRIBUTE12,
1496 X_ATTRIBUTE13 => p_mr_rec.ATTRIBUTE13,
1497 X_ATTRIBUTE14 => p_mr_rec.ATTRIBUTE14,
1498 X_ATTRIBUTE15 => p_mr_rec.ATTRIBUTE15,
1499 X_OBJECT_VERSION_NUMBER => p_mr_rec.object_version_number+1,
1500 X_LAST_UPDATE_DATE => sysdate,
1501 X_LAST_UPDATED_BY => fnd_global.user_id,
1502 X_LAST_UPDATE_LOGIN => fnd_global.login_id ,
1503 -- JKJain, NR Analysis and Forecasting
1504 X_FLEET_HEADER_ID => p_temp_mr_rec.fleet_header_id);
1505
1506 -- added for SB Enh
1507 UPDATE ahl_unit_effectivities_b
1508 SET accomplish_trigger_type = p_temp_mr_rec.accomplish_trigger_type,
1509 loop_chain_seq_num = p_temp_mr_rec.loop_chain_seq_num,
1510 start_lc_ue_id = p_temp_mr_rec.start_lc_ue_id,
1511 terminating_ue_id = null -- fix for bug# 16567016; reset this value.
1512 WHERE unit_effectivity_id = p_mr_rec.unit_effectivity_id;
1513
1514 IF (p_temp_mr_rec.due_date IS NULL AND p_mr_rec.object_type = 'MR') THEN
1515 -- Delete the corresponding rows in ahl_schedule materials for this ue.
1516 Delete_Sch_Materials(p_mr_rec.unit_effectivity_id);
1517 END IF;
1518
1519 IF G_DEBUG = 'Y' THEN
1520 AHL_DEBUG_PUB.Debug('End Update Record');
1521 END IF;
1522
1523 EXCEPTION
1524 -- If any error occurs, then, abort API.
1525 WHEN NO_DATA_FOUND THEN
1526 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1527 FND_MSG_PUB.ADD;
1528 RAISE FND_API.G_EXC_ERROR;
1529
1530 END update_record;
1531
1532
1533 -----------------------------------------------------------------------------
1534 -- To create decendent records and build relationships if the MR is a group MR.
1535
1536 PROCEDURE create_group (p_x_temp_grp_rec IN OUT NOCOPY ahl_temp_unit_effectivities%ROWTYPE)
1537
1538 IS
1539 -- Read group elements.
1540 CURSOR ahl_temp_grp_csr(p_csi_item_instance_id IN NUMBER,
1541 p_mr_header_id IN NUMBER,
1542 p_forecast_sequence IN NUMBER) IS
1543 SELECT unit_effectivity_id,
1544 csi_item_instance_id,
1545 MR_header_id,
1546 due_date,
1547 mr_interval_id,
1548 mr_effectivity_id,
1549 due_counter_value,
1550 parent_csi_item_instance_id,
1551 parent_mr_header_id,
1552 orig_csi_item_instance_id,
1553 orig_mr_header_id,
1554 forecast_sequence,
1555 repetitive_mr_flag,
1556 tolerance_flag,
1557 message_code,
1558 earliest_due_date,
1559 latest_due_date,
1560 counter_id,
1561 rowid
1562 FROM ahl_temp_unit_effectivities
1563 START WITH parent_csi_item_instance_id = p_csi_item_instance_id
1564 AND parent_mr_header_id = p_mr_header_id
1565 AND orig_csi_item_instance_id = p_csi_item_instance_id
1566 AND orig_mr_header_id = p_mr_header_id
1567 AND orig_forecast_sequence = p_forecast_sequence
1568 AND nvl(preceding_check_flag,'N') = 'N'
1569 CONNECT BY PRIOR MR_header_id = parent_mr_header_id
1570 AND PRIOR csi_item_instance_id = parent_csi_item_instance_id
1571 AND orig_csi_item_instance_id = p_csi_item_instance_id
1572 AND orig_mr_header_id = p_mr_header_id
1573 AND orig_forecast_sequence = p_forecast_sequence
1574 AND nvl(preceding_check_flag,'N') = 'N'
1575 FOR UPDATE OF due_date;
1576
1577 -- get parent unit effectivity id.
1578 CURSOR ahl_temp_parent_csr (p_parent_csi_item_instance_id IN NUMBER,
1579 p_parent_mr_header_id IN NUMBER,
1580 p_orig_csi_item_instance_id IN NUMBER,
1581 p_orig_mr_header_id IN NUMBER,
1582 p_forecast_sequence IN NUMBER) IS
1583 SELECT unit_effectivity_id
1584 FROM ahl_temp_unit_effectivities
1585 WHERE csi_item_instance_id = p_parent_csi_item_instance_id
1586 AND MR_header_id = p_parent_mr_header_id
1587 AND orig_csi_item_instance_id = p_orig_csi_item_instance_id
1588 AND orig_mr_header_id = p_orig_mr_header_id
1589 AND orig_forecast_sequence = p_forecast_sequence;
1590
1591 l_ue_relationship_id NUMBER;
1592
1593 l_originator_ue_id NUMBER;
1594 l_orig_csi_item_instance_id NUMBER;
1595 l_orig_mr_header_id NUMBER;
1596
1597 l_parent_ue_id NUMBER;
1598
1599 l_temp_child_rec ahl_temp_unit_effectivities%ROWTYPE;
1600
1601 BEGIN
1602
1603 IF G_DEBUG = 'Y' THEN
1604 AHL_DEBUG_PUB.Debug('Start Create Group');
1605 AHL_DEBUG_PUB.Debug('CSI:MR:' || p_x_temp_grp_rec.csi_item_instance_id || ',' || p_x_temp_grp_rec.MR_header_id);
1606 END IF;
1607
1608 -- For top node.
1609 Create_Record (p_x_temp_grp_rec);
1610
1611 -- Update ahl_temp_unit_effectivities with the unit effectivity id.
1612 UPDATE ahl_temp_unit_effectivities
1613 SET unit_effectivity_id = p_x_temp_grp_rec.unit_effectivity_id
1614 WHERE csi_item_instance_id = p_x_temp_grp_rec.csi_item_instance_id AND
1615 mr_header_id = p_x_temp_grp_rec.mr_header_id AND
1616 forecast_sequence = p_x_temp_grp_rec.forecast_sequence;
1617
1618 -- Read all elements.
1619 FOR l_temp_grp_rec IN ahl_temp_grp_csr(p_x_temp_grp_rec.csi_item_instance_id,
1620 p_x_temp_grp_rec.mr_header_id,
1621 p_x_temp_grp_rec.forecast_sequence)
1622 LOOP
1623
1624 -- set record values.
1625 l_temp_child_rec.unit_effectivity_id := null;
1626 l_temp_child_rec.csi_item_instance_id := l_temp_grp_rec.csi_item_instance_id;
1627 l_temp_child_rec.mr_interval_id := l_temp_grp_rec.mr_interval_id;
1628 l_temp_child_rec.mr_effectivity_id := l_temp_grp_rec.mr_effectivity_id;
1629 l_temp_child_rec.MR_header_id := l_temp_grp_rec.mr_header_id;
1630 l_temp_child_rec.due_date := l_temp_grp_rec.due_date;
1631 l_temp_child_rec.due_counter_value := l_temp_grp_rec.due_counter_value;
1632 l_temp_child_rec.forecast_sequence := l_temp_grp_rec.forecast_sequence;
1633 l_temp_child_rec.repetitive_mr_flag := l_temp_grp_rec.repetitive_mr_flag;
1634 l_temp_child_rec.tolerance_flag := l_temp_grp_rec.tolerance_flag;
1635 l_temp_child_rec.message_code := l_temp_grp_rec.message_code;
1636 l_temp_child_rec.earliest_due_date := l_temp_grp_rec.earliest_due_date;
1637 l_temp_child_rec.latest_due_date := l_temp_grp_rec.latest_due_date;
1638 l_temp_child_rec.counter_id := l_temp_grp_rec.counter_id;
1639 -- JKJain, NR Analysis and Forecasting
1640 l_temp_child_rec.fleet_header_id := p_x_temp_grp_rec.fleet_header_id;
1641 -- Insert into ahl_unit_effectivities.
1642 Create_Record (l_temp_child_rec);
1643
1644 -- Update ahl_temp_unit_effectivities with the unit effectivity id.
1645 UPDATE ahl_temp_unit_effectivities
1646 SET unit_effectivity_id = l_temp_child_rec.unit_effectivity_id
1647 WHERE rowid = l_temp_grp_rec.rowid;
1648 --WHERE CURRENT OF ahl_temp_grp_csr;
1649
1650 --dbms_output.put_line ('generated unit effectivity id' || l_unit_effectivity_id);
1651
1652 END LOOP;
1653
1654 -- Read from the top group node and build relationships by inserting
1655 -- into the relationships table.
1656
1657 l_originator_ue_id := p_x_temp_grp_rec.unit_effectivity_id;
1658 l_orig_csi_item_instance_id := p_x_temp_grp_rec.csi_item_instance_id;
1659 l_orig_mr_header_id := p_x_temp_grp_rec.mr_header_id;
1660
1661 --dbms_output.put_line ('before relationships built');
1662
1663 FOR l_temp_grp_rec IN ahl_temp_grp_csr(p_x_temp_grp_rec.csi_item_instance_id,
1664 p_x_temp_grp_rec.mr_header_id,
1665 p_x_temp_grp_rec.forecast_sequence)
1666 LOOP
1667
1668 OPEN ahl_temp_parent_csr(l_temp_grp_rec.parent_csi_item_instance_id,
1669 l_temp_grp_rec.parent_mr_header_id,
1670 l_orig_csi_item_instance_id,
1671 l_orig_mr_header_id,
1672 p_x_temp_grp_rec.forecast_sequence);
1673 FETCH ahl_temp_parent_csr INTO l_parent_ue_id;
1674 IF (ahl_temp_parent_csr%NOTFOUND) THEN
1675 FND_MESSAGE.Set_Name ('AHL','AHL_UMP_PUE_PARENT_NOTFOUND');
1676 FND_MESSAGE.Set_Token ('INST_ID',l_temp_grp_rec.csi_item_instance_id);
1677 FND_MESSAGE.Set_Token ('MR_ID',l_temp_grp_rec.mr_header_id);
1678 FND_MSG_PUB.ADD;
1679 CLOSE ahl_temp_parent_csr;
1680 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1681 END IF;
1682 CLOSE ahl_temp_parent_csr;
1683
1684 -- Insert into ahl_ue_relationships.
1685 AHL_UE_RELATIONSHIPS_PKG.Insert_Row(
1686 X_UE_RELATIONSHIP_ID => l_ue_relationship_id,
1687 X_UE_ID => l_parent_ue_id,
1688 X_RELATED_UE_ID => l_temp_grp_rec.unit_effectivity_id,
1689 X_RELATIONSHIP_CODE => 'PARENT',
1690 X_ORIGINATOR_UE_ID => l_originator_ue_id,
1691 X_ATTRIBUTE_CATEGORY => null, /* ATTRIBUTE_CATEGORY */
1692 X_ATTRIBUTE1 => null, /* ATTRIBUTE1 */
1693 X_ATTRIBUTE2 => null, /* ATTRIBUTE2 */
1694 X_ATTRIBUTE3 => null, /* ATTRIBUTE3 */
1695 X_ATTRIBUTE4 => null, /* ATTRIBUTE4 */
1696 X_ATTRIBUTE5 => null, /* ATTRIBUTE5 */
1697 X_ATTRIBUTE6 => null, /* ATTRIBUTE6 */
1698 X_ATTRIBUTE7 => null, /* ATTRIBUTE7 */
1699 X_ATTRIBUTE8 => null, /* ATTRIBUTE8 */
1700 X_ATTRIBUTE9 => null, /* ATTRIBUTE9 */
1701 X_ATTRIBUTE10 => null, /* ATTRIBUTE10 */
1702 X_ATTRIBUTE11 => null, /* ATTRIBUTE11 */
1703 X_ATTRIBUTE12 => null, /* ATTRIBUTE12 */
1704 X_ATTRIBUTE13 => null, /* ATTRIBUTE13 */
1705 X_ATTRIBUTE14 => null, /* ATTRIBUTE14 */
1706 X_ATTRIBUTE15 => null, /* ATTRIBUTE15 */
1707 X_OBJECT_VERSION_NUMBER => 1,
1708 X_LAST_UPDATE_DATE => sysdate,
1709 X_LAST_UPDATED_BY => fnd_global.user_id,
1710 X_CREATION_DATE => sysdate,
1711 X_CREATED_BY => fnd_global.user_id,
1712 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
1713
1714 END LOOP;
1715
1716 IF G_DEBUG = 'Y' THEN
1717 AHL_DEBUG_PUB.Debug('End Create Group');
1718 END IF;
1719
1720 EXCEPTION
1721 -- If any error occurs, then, abort API.
1722 WHEN NO_DATA_FOUND THEN
1723 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1724 FND_MSG_PUB.ADD;
1725 RAISE FND_API.G_EXC_ERROR;
1726
1727 END create_group;
1728
1729 --------------------------------------------------------------------------------
1730 -- To update decendent records and update relationships if the MR is a group MR.
1731
1732 PROCEDURE update_group (p_temp_mr_rec IN ahl_temp_unit_effectivities%ROWTYPE,
1733 p_mr_rec IN ahl_unit_effectivities_app_v%ROWTYPE)
1734 IS
1735
1736 -- Read group elements.
1737 CURSOR ahl_temp_grp_csr(p_csi_item_instance_id IN NUMBER,
1738 p_mr_header_id IN NUMBER,
1739 p_forecast_sequence IN NUMBER,
1740 p_level IN NUMBER) IS
1741 SELECT csi_item_instance_id,
1742 MR_header_id,
1743 parent_csi_item_instance_id,
1744 parent_mr_header_id,
1745 orig_csi_item_instance_id,
1746 orig_mr_header_id,
1747 forecast_sequence
1748 FROM ahl_temp_unit_effectivities
1749 WHERE level = p_level
1750 START WITH parent_csi_item_instance_id = p_csi_item_instance_id
1751 AND parent_mr_header_id = p_mr_header_id
1752 AND orig_csi_item_instance_id = p_csi_item_instance_id
1753 AND orig_mr_header_id = p_mr_header_id
1754 AND orig_forecast_sequence = p_forecast_sequence
1755 AND nvl(preceding_check_flag,'N') = 'N'
1756 CONNECT BY PRIOR MR_header_id = parent_mr_header_id
1757 AND PRIOR csi_item_instance_id = parent_csi_item_instance_id
1758 AND orig_csi_item_instance_id = p_csi_item_instance_id
1759 AND orig_mr_header_id = p_mr_header_id
1760 AND orig_forecast_sequence = p_forecast_sequence
1761 AND nvl(preceding_check_flag,'N') = 'N';
1762
1763 -- Read group elements from ue relationships.
1764 CURSOR ahl_ue_reln_csr(p_unit_effectivity_id IN NUMBER,
1765 p_level IN NUMBER) IS
1766 SELECT UE_ID parent_ue_id,
1767 RELATED_UE_ID ue_id
1768 FROM ahl_ue_relationships
1769 WHERE level = p_level
1770 START WITH ue_id = p_unit_effectivity_id AND
1771 relationship_code = 'PARENT'
1772 CONNECT BY PRIOR related_ue_id = ue_id AND
1773 relationship_code = 'PARENT';
1774
1775 -- get related unit effectivities details.
1776 CURSOR ahl_ue_grp_csr ( p_ue_id IN NUMBER,
1777 p_parent_ue_id IN NUMBER ) IS
1778 SELECT ue1.mr_header_id, ue1.csi_item_instance_id, ue1.unit_effectivity_id,
1779 ue2.mr_header_id parent_mr_header_id,
1780 ue2.csi_item_instance_id parent_csi_item_instance_id
1781 --FROM ahl_unit_effectivities_app_v ue1, ahl_unit_effectivities_app_v ue2
1782 FROM ahl_unit_effectivities_b ue1, ahl_unit_effectivities_b ue2
1783 WHERE ue1.unit_effectivity_id = p_ue_id AND
1784 ue2.unit_effectivity_id = p_parent_ue_id;
1785
1786 -- Cursor to get all details of a unit effectivity record.
1787 CURSOR ahl_unit_effectivity_csr ( p_unit_effectivity_id IN NUMBER) IS
1788 SELECT
1789 UNIT_EFFECTIVITY_ID ,
1790 CSI_ITEM_INSTANCE_ID,
1791 MR_INTERVAL_ID,
1792 MR_EFFECTIVITY_ID ,
1793 MR_HEADER_ID,
1794 STATUS_CODE ,
1795 DUE_DATE ,
1796 DUE_COUNTER_VALUE ,
1797 FORECAST_SEQUENCE ,
1798 --REPETITIVE_MR_FLAG ,
1799 TOLERANCE_FLAG ,
1800 REMARKS ,
1801 MESSAGE_CODE ,
1802 PRECEDING_UE_ID ,
1803 DATE_RUN ,
1804 SET_DUE_DATE ,
1805 ACCOMPLISHED_DATE ,
1806 CANCEL_REASON_CODE,
1807 --EARLIEST_DUE_DATE,
1808 --LATEST_DUE_DATE,
1809 defer_from_ue_id,
1810 cs_incident_id,
1811 qa_collection_id,
1812 orig_deferral_ue_id,
1813 application_usg_code,
1814 object_type,
1815 --counter_id,
1816 ATTRIBUTE_CATEGORY ,
1817 ATTRIBUTE1,
1818 ATTRIBUTE2 ,
1819 ATTRIBUTE3 ,
1820 ATTRIBUTE4 ,
1821 ATTRIBUTE5 ,
1822 ATTRIBUTE6 ,
1823 ATTRIBUTE7 ,
1824 ATTRIBUTE8 ,
1825 ATTRIBUTE9 ,
1826 ATTRIBUTE10,
1827 ATTRIBUTE11 ,
1828 ATTRIBUTE12 ,
1829 ATTRIBUTE13 ,
1830 ATTRIBUTE14 ,
1831 ATTRIBUTE15 ,
1832 OBJECT_VERSION_NUMBER
1833 --FROM ahl_unit_effectivities_app_v
1834 FROM ahl_unit_effectivities_vl
1835 WHERE unit_effectivity_id = p_unit_effectivity_id;
1836 --FOR UPDATE OF due_date NOWAIT;
1837
1838 TYPE temp_grp_rec_type IS RECORD (
1839 csi_item_instance_id NUMBER,
1840 MR_header_id NUMBER,
1841 parent_csi_item_instance_id NUMBER,
1842 parent_mr_header_id NUMBER,
1843 orig_csi_item_instance_id NUMBER,
1844 orig_mr_header_id NUMBER,
1845 forecast_sequence NUMBER );
1846
1847
1848 TYPE temp_grp_tbl_type IS TABLE OF temp_grp_rec_type INDEX BY BINARY_INTEGER;
1849
1850 l_temp_grp_rec temp_grp_rec_type;
1851 l_temp_grp_tbl temp_grp_tbl_type;
1852
1853
1854 TYPE ue_grp_rec_type IS RECORD (
1855 mr_header_id NUMBER,
1856 csi_item_instance_id NUMBER,
1857 unit_effectivity_id NUMBER,
1858 parent_mr_header_id NUMBER,
1859 parent_csi_item_instance_id NUMBER);
1860
1861 TYPE ue_grp_tbl_type IS TABLE OF ue_grp_rec_type INDEX BY BINARY_INTEGER;
1862
1863 l_ue_grp_tbl ue_grp_tbl_type;
1864 l_ue_grp_rec ue_grp_rec_type;
1865
1866 l_level NUMBER;
1867 l_grp_match_found BOOLEAN;
1868 l_temp_grp_found BOOLEAN;
1869 l_ue_grp_found BOOLEAN;
1870
1871 i NUMBER;
1872 l_grp_match_flag BOOLEAN;
1873
1874 l_temp_mr_rec ahl_temp_unit_effectivities%ROWTYPE := p_temp_mr_rec;
1875 l_unit_effectivity_rec ahl_unit_effectivities_app_v%ROWTYPE;
1876
1877 -- added for bug# 7586838
1878 CURSOR unit_deferral_csr(p_ue_id IN NUMBER) IS
1879 SELECT unit_deferral_id
1880 FROM ahl_unit_deferrals_b
1881 WHERE UNIT_EFFECTIVITY_ID = p_ue_id
1882 AND UNIT_DEFERRAL_TYPE = 'INIT-DUE';
1883
1884 l_unit_deferral_id NUMBER;
1885 l_visit_status ahl_visits_b.status_code%TYPE;
1886
1887 BEGIN
1888
1889 IF G_DEBUG = 'Y' THEN
1890 AHL_DEBUG_PUB.Debug('Start Update Group');
1891 AHL_DEBUG_PUB.Debug('CSI:MR:' || p_mr_rec.csi_item_instance_id || ',' || p_mr_rec.MR_header_id);
1892 END IF;
1893
1894 -- Set savepoint.
1895 SAVEPOINT update_group;
1896
1897 l_visit_status := AHL_UMP_UTIL_PKG.get_visit_status (p_temp_mr_rec.unit_effectivity_id);
1898
1899 IF (l_visit_status IN ('RELEASED','CLOSED')) THEN
1900 l_grp_match_found := TRUE; -- ignore matching group as UE on shop floor.
1901 -- update existing UE children.
1902 UPDATE AHL_UNIT_EFFECTIVITIES_B
1903 SET mr_interval_id = p_temp_mr_rec.mr_interval_id,
1904 mr_effectivity_id = p_temp_mr_rec.mr_effectivity_id,
1905 due_date = p_temp_mr_rec.due_date,
1906 due_counter_value = p_temp_mr_rec.due_counter_value,
1907 forecast_sequence = p_temp_mr_rec.forecast_sequence,
1908 repetitive_mr_flag = p_temp_mr_rec.repetitive_mr_flag,
1909 tolerance_flag = p_temp_mr_rec.tolerance_flag,
1910 message_code = p_temp_mr_rec.message_code,
1911 date_run = sysdate,
1912 earliest_due_date = p_temp_mr_rec.earliest_due_date,
1913 latest_due_date = p_temp_mr_rec.latest_due_date,
1914 counter_id = p_temp_mr_rec.counter_id,
1915 object_version_number = object_version_number+1,
1916 LAST_UPDATE_DATE = sysdate,
1917 LAST_UPDATED_BY = fnd_global.user_id,
1918 LAST_UPDATE_LOGIN = fnd_global.login_id,
1919 -- JKJain, NR Analysis and Forecasting
1920 FLEET_HEADER_ID = p_temp_mr_rec.fleet_header_id
1921 WHERE unit_effectivity_id IN (SELECT related_ue_id
1922 FROM ahl_ue_relationships
1923 WHERE originator_ue_id = p_temp_mr_rec.unit_effectivity_id
1924 AND relationship_code = 'PARENT');
1925
1926 ELSE
1927 -- For each tree level compare and update the effectivity details.
1928 l_level := 0;
1929 l_grp_match_found := TRUE;
1930 l_temp_grp_found := TRUE; /* temp group record found */
1931 l_ue_grp_found := TRUE; /* ue grp found */
1932
1933
1934 WHILE ((l_temp_grp_found) AND (l_ue_grp_found) AND (l_grp_match_found)) LOOP
1935
1936 l_level := l_level + 1;
1937
1938 -- initialize tables.
1939 l_temp_grp_tbl.DELETE;
1940 l_ue_grp_tbl.DELETE;
1941
1942 -- Build table from temp unit effectivities.
1943 i := 1;
1944 FOR temp_rec IN ahl_temp_grp_csr(p_temp_mr_rec.orig_csi_item_instance_id,
1945 p_temp_mr_rec.orig_mr_header_id,
1946 p_temp_mr_rec.forecast_sequence,
1947 l_level)
1948 LOOP
1949 l_temp_grp_tbl(i) := temp_rec;
1950 i := i + 1;
1951 END LOOP;
1952
1953 -- Build table from unit effectivities.
1954 i := 1;
1955 FOR reln_rec IN ahl_ue_reln_csr (p_temp_mr_rec.unit_effectivity_id,
1956 l_level)
1957 LOOP
1958 OPEN ahl_ue_grp_csr(reln_rec.ue_id,
1959 reln_rec.parent_ue_id);
1960 FETCH ahl_ue_grp_csr INTO l_ue_grp_rec;
1961 IF ahl_ue_grp_csr%NOTFOUND THEN
1962 FND_Message.Set_Name ('AHL','AHL_UMP_PUE_RELN_NOTFOUND');
1963 FND_Message.set_token ('UE_ID',reln_rec.parent_ue_id);
1964 FND_Message.set_token ('RELATED_UE_ID',reln_rec.ue_id);
1965 FND_MSG_PUB.ADD;
1966 CLOSE ahl_ue_grp_csr;
1967 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1968 END IF;
1969 CLOSE ahl_ue_grp_csr;
1970 l_ue_grp_tbl(i) := l_ue_grp_rec;
1971 i := i + 1;
1972 END LOOP;
1973
1974 IF (l_temp_grp_tbl.COUNT > 0) THEN
1975 l_temp_grp_found := TRUE;
1976 ELSE
1977 l_temp_grp_found := FALSE;
1978 END IF;
1979
1980 IF (l_ue_grp_tbl.COUNT > 0) THEN
1981 l_ue_grp_found := TRUE;
1982 ELSE
1983 l_ue_grp_found := FALSE;
1984 END IF;
1985
1986 l_grp_match_flag := TRUE;
1987
1988 -- match table count.
1989 IF (l_temp_grp_found) THEN
1990 IF (l_ue_grp_found) THEN
1991 IF (l_temp_grp_tbl.COUNT <> l_ue_grp_tbl.COUNT) THEN
1992 l_grp_match_flag := FALSE;
1993 END IF;
1994 ELSE
1995 l_grp_match_flag := FALSE;
1996 END IF;
1997 ELSE
1998 IF (l_ue_grp_found) THEN
1999 l_grp_match_flag := FALSE;
2000 END IF;
2001 END IF;
2002
2003 -- update.
2004 IF ((l_grp_match_flag) AND (l_ue_grp_found) AND (l_temp_grp_found)) THEN
2005 -- update.
2006 FOR i IN l_temp_grp_tbl.FIRST..l_temp_grp_tbl.LAST LOOP
2007 -- Find the matching entry in l_ue_grp_tbl.
2008 l_grp_match_flag := FALSE; -- this will be set to true when record gets updated.
2009 FOR j IN l_ue_grp_tbl.FIRST..l_ue_grp_tbl.LAST LOOP
2010 IF (l_ue_grp_tbl(j).csi_item_instance_id = l_temp_grp_tbl(i).csi_item_instance_id
2011 AND l_ue_grp_tbl(j).mr_header_id = l_temp_grp_tbl(i).mr_header_id
2012 AND l_ue_grp_tbl(j).parent_csi_item_instance_id = l_temp_grp_tbl(i).parent_csi_item_instance_id
2013 AND l_ue_grp_tbl(j).parent_mr_header_id = l_temp_grp_tbl(i).parent_mr_header_id)
2014 THEN
2015 --dbms_output.put_line ('matched');
2016
2017 -- Read Unit Effectivity record.
2018 OPEN ahl_unit_effectivity_csr(l_ue_grp_tbl(j).unit_effectivity_id);
2019 FETCH ahl_unit_effectivity_csr INTO l_unit_effectivity_rec.UNIT_EFFECTIVITY_ID ,
2020 l_unit_effectivity_rec.CSI_ITEM_INSTANCE_ID,
2021 l_unit_effectivity_rec.MR_INTERVAL_ID,
2022 l_unit_effectivity_rec.MR_EFFECTIVITY_ID ,
2023 l_unit_effectivity_rec.MR_HEADER_ID,
2024 l_unit_effectivity_rec.STATUS_CODE ,
2025 l_unit_effectivity_rec.DUE_DATE ,
2026 l_unit_effectivity_rec.DUE_COUNTER_VALUE ,
2027 l_unit_effectivity_rec.FORECAST_SEQUENCE ,
2028 --l_unit_effectivity_rec.REPETITIVE_MR_FLAG ,
2029 l_unit_effectivity_rec.TOLERANCE_FLAG ,
2030 l_unit_effectivity_rec.REMARKS ,
2031 l_unit_effectivity_rec.MESSAGE_CODE ,
2032 l_unit_effectivity_rec.PRECEDING_UE_ID ,
2033 l_unit_effectivity_rec.DATE_RUN ,
2034 l_unit_effectivity_rec.SET_DUE_DATE ,
2035 l_unit_effectivity_rec.ACCOMPLISHED_DATE ,
2036 l_unit_effectivity_rec.CANCEL_REASON_CODE,
2037 --l_unit_effectivity_rec.earliest_due_date,
2038 --l_unit_effectivity_rec.latest_due_date,
2039 l_unit_effectivity_rec.defer_from_ue_id,
2040 l_unit_effectivity_rec.cs_incident_id,
2041 l_unit_effectivity_rec.qa_collection_id,
2042 l_unit_effectivity_rec.orig_deferral_ue_id,
2043 l_unit_effectivity_rec.application_usg_code,
2044 l_unit_effectivity_rec.object_type,
2045 --l_ue_rec.counter_id,
2046 l_unit_effectivity_rec.ATTRIBUTE_CATEGORY ,
2047 l_unit_effectivity_rec.ATTRIBUTE1,
2048 l_unit_effectivity_rec.ATTRIBUTE2 ,
2049 l_unit_effectivity_rec.ATTRIBUTE3 ,
2050 l_unit_effectivity_rec.ATTRIBUTE4 ,
2051 l_unit_effectivity_rec.ATTRIBUTE5 ,
2052 l_unit_effectivity_rec.ATTRIBUTE6 ,
2053 l_unit_effectivity_rec.ATTRIBUTE7 ,
2054 l_unit_effectivity_rec.ATTRIBUTE8 ,
2055 l_unit_effectivity_rec.ATTRIBUTE9 ,
2056 l_unit_effectivity_rec.ATTRIBUTE10,
2057 l_unit_effectivity_rec.ATTRIBUTE11 ,
2058 l_unit_effectivity_rec.ATTRIBUTE12 ,
2059 l_unit_effectivity_rec.ATTRIBUTE13 ,
2060 l_unit_effectivity_rec.ATTRIBUTE14 ,
2061 l_unit_effectivity_rec.ATTRIBUTE15 ,
2062 l_unit_effectivity_rec.OBJECT_VERSION_NUMBER;
2063
2064 IF (ahl_unit_effectivity_csr%NOTFOUND) THEN
2065 FND_Message.Set_Name ('AHL','AHL_UMP_PUE_UE_NOTFOUND');
2066 FND_Message.set_token ('UE_ID',l_ue_grp_tbl(j).csi_item_instance_id);
2067 FND_MSG_PUB.ADD;
2068 CLOSE ahl_unit_effectivity_csr;
2069 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2070 ELSE
2071
2072 update_record (p_temp_mr_rec, l_unit_effectivity_rec);
2073 l_grp_match_flag := TRUE; -- matched record.
2074 CLOSE ahl_unit_effectivity_csr;
2075 EXIT;
2076 END IF;
2077
2078 END IF;
2079 END LOOP; -- for j (ue)
2080 IF (l_grp_match_flag = FALSE) THEN
2081 EXIT;
2082 END IF;
2083 END LOOP; -- for i (temp)
2084 END IF;
2085
2086 l_grp_match_found := l_grp_match_flag;
2087
2088 END LOOP; /* while */
2089
2090 END IF; -- l_visit_status
2091
2092 IF NOT(l_grp_match_found) THEN
2093 --rollback to save point
2094 ROLLBACK to update_group;
2095
2096 create_group (p_x_temp_grp_rec => l_temp_mr_rec);
2097 -- fix for bug# 7586838.
2098 IF (p_mr_rec.status_code = 'INIT-DUE') THEN
2099 -- update ahl_unit_deferrals_b.
2100 OPEN unit_deferral_csr(p_mr_rec.unit_effectivity_id);
2101 FETCH unit_deferral_csr INTO l_unit_deferral_id;
2102 IF (unit_deferral_csr%FOUND) THEN
2103 UPDATE ahl_unit_deferrals_b
2104 SET UNIT_EFFECTIVITY_ID = l_temp_mr_rec.unit_effectivity_id,
2105 last_update_date = sysdate,
2106 object_version_number = object_version_number + 1,
2107 LAST_UPDATED_BY = fnd_global.user_id,
2108 LAST_UPDATE_LOGIN = fnd_global.login_id
2109 WHERE unit_deferral_id = l_unit_deferral_id;
2110 END IF;
2111 CLOSE unit_deferral_csr;
2112
2113 -- update unit effectivity status on top node.
2114 UPDATE ahl_unit_effectivities_b
2115 SET status_code = 'INIT-DUE'
2116 WHERE unit_effectivity_id = l_temp_mr_rec.unit_effectivity_id;
2117
2118 END IF;
2119 ELSE
2120 -- Update top group node with p_mr_rec.unit_effectivity_id.
2121 update_record (p_temp_mr_rec,
2122 p_mr_rec);
2123
2124 END IF;
2125
2126 IF G_DEBUG = 'Y' THEN
2127 AHL_DEBUG_PUB.Debug('End Update Group');
2128 END IF;
2129
2130
2131 EXCEPTION
2132 -- If any error occurs, then, abort API.
2133 WHEN NO_DATA_FOUND THEN
2134 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
2135 FND_MSG_PUB.ADD;
2136 RAISE FND_API.G_EXC_ERROR;
2137
2138 END update_group;
2139 ----------------------------------------------------------------------
2140 -- To update unit effectivities for Deferrals and Service Request UEs.
2141
2142 PROCEDURE Flush_Unit_SR_Deferrals IS
2143
2144 -- read all top group MRs from the temporary table.
2145 CURSOR ahl_unit_sr_def_csr IS
2146 SELECT
2147 unit_effectivity_id,
2148 object_type,
2149 csi_item_instance_id,
2150 mr_header_id,
2151 due_date,
2152 due_counter_value,
2153 tolerance_flag,
2154 message_code,
2155 parent_csi_item_instance_id,
2156 parent_mr_header_id,
2157 orig_csi_item_instance_id,
2158 orig_mr_header_id,
2159 orig_unit_effectivity_id,
2160 group_match_flag,
2161 counter_id
2162 FROM ahl_temp_unit_SR_deferrals
2163 WHERE orig_unit_effectivity_id IS NULL
2164 ORDER BY csi_item_instance_id, mr_header_id
2165 FOR UPDATE OF unit_effectivity_id;
2166
2167 -- Read group from ahl_ue_relationships.
2168 CURSOR ahl_ue_csr (p_orig_ue_id IN NUMBER) IS
2169 SELECT related_ue_id
2170 FROM ahl_ue_relationships
2171 WHERE originator_ue_id = p_orig_ue_id
2172 AND relationship_code = 'PARENT';
2173
2174 -- Read group elements.
2175 CURSOR ahl_temp_csr(p_csi_item_instance_id IN NUMBER,
2176 p_mr_header_id IN NUMBER,
2177 p_unit_effectivity_id IN NUMBER) IS
2178 SELECT unit_effectivity_id,
2179 csi_item_instance_id,
2180 MR_header_id,
2181 due_date,
2182 due_counter_value,
2183 parent_csi_item_instance_id,
2184 parent_mr_header_id,
2185 orig_csi_item_instance_id,
2186 orig_mr_header_id,
2187 tolerance_flag,
2188 message_code
2189 FROM ahl_temp_unit_SR_deferrals
2190 START WITH parent_csi_item_instance_id = p_csi_item_instance_id
2191 AND parent_mr_header_id = p_mr_header_id
2192 AND orig_csi_item_instance_id = p_csi_item_instance_id
2193 AND orig_mr_header_id = p_mr_header_id
2194 AND orig_unit_effectivity_id = p_unit_effectivity_id
2195 CONNECT BY PRIOR MR_header_id = parent_mr_header_id
2196 AND PRIOR csi_item_instance_id = parent_csi_item_instance_id
2197 AND orig_csi_item_instance_id = p_csi_item_instance_id
2198 AND orig_mr_header_id = p_mr_header_id
2199 AND orig_unit_effectivity_id = p_unit_effectivity_id
2200 FOR UPDATE OF due_date
2201 ORDER BY level;
2202
2203
2204 -- get parent unit effectivity id.
2205 CURSOR ahl_temp_parent_csr (p_parent_csi_item_instance_id IN NUMBER,
2206 p_parent_mr_header_id IN NUMBER,
2207 p_orig_csi_item_instance_id IN NUMBER,
2208 p_orig_mr_header_id IN NUMBER,
2209 p_unit_effectivity_id NUMBER) IS
2210 SELECT unit_effectivity_id
2211 FROM ahl_temp_unit_SR_deferrals
2212 WHERE csi_item_instance_id = p_parent_csi_item_instance_id
2213 AND MR_header_id = p_parent_mr_header_id
2214 AND orig_csi_item_instance_id = p_orig_csi_item_instance_id
2215 AND orig_mr_header_id = p_orig_mr_header_id
2216 AND orig_unit_effectivity_id = p_unit_effectivity_id;
2217
2218 -- Cursor to get all details of a unit effectivity record.
2219 CURSOR ahl_unit_effectivity_csr ( p_unit_effectivity_id IN NUMBER) IS
2220 SELECT
2221 UNIT_EFFECTIVITY_ID ,
2222 CSI_ITEM_INSTANCE_ID,
2223 MR_INTERVAL_ID,
2224 MR_EFFECTIVITY_ID ,
2225 MR_HEADER_ID,
2226 STATUS_CODE ,
2227 DUE_DATE ,
2228 DUE_COUNTER_VALUE ,
2229 FORECAST_SEQUENCE ,
2230 REPETITIVE_MR_FLAG ,
2231 TOLERANCE_FLAG ,
2232 REMARKS ,
2233 MESSAGE_CODE ,
2234 PRECEDING_UE_ID ,
2235 DATE_RUN ,
2236 SET_DUE_DATE ,
2237 ACCOMPLISHED_DATE ,
2238 CANCEL_REASON_CODE,
2239 EARLIEST_DUE_DATE,
2240 LATEST_DUE_DATE,
2241 SERVICE_LINE_ID,
2242 PROGRAM_MR_HEADER_ID,
2243 defer_from_ue_id,
2244 cs_incident_id,
2245 qa_collection_id,
2246 orig_deferral_ue_id,
2247 application_usg_code,
2248 object_type,
2249 counter_id,
2250 manually_planned_flag,
2251 LOG_SERIES_CODE,
2252 LOG_SERIES_NUMBER,
2253 FLIGHT_NUMBER,
2254 MEL_CDL_TYPE_CODE,
2255 POSITION_PATH_ID,
2256 ATA_CODE,
2257 UNIT_CONFIG_HEADER_ID,
2258 ATTRIBUTE_CATEGORY ,
2259 ATTRIBUTE1,
2260 ATTRIBUTE2 ,
2261 ATTRIBUTE3 ,
2262 ATTRIBUTE4 ,
2263 ATTRIBUTE5 ,
2264 ATTRIBUTE6 ,
2265 ATTRIBUTE7 ,
2266 ATTRIBUTE8 ,
2267 ATTRIBUTE9 ,
2268 ATTRIBUTE10,
2269 ATTRIBUTE11 ,
2270 ATTRIBUTE12 ,
2271 ATTRIBUTE13 ,
2272 ATTRIBUTE14 ,
2273 ATTRIBUTE15 ,
2274 OBJECT_VERSION_NUMBER,
2275 -- added for SB Enh
2276 accomplish_trigger_type,
2277 start_lc_ue_id,
2278 loop_chain_seq_num,
2279 -- JKJain, NR Analysis and Forecasting
2280 fleet_header_id
2281 FROM ahl_unit_effectivities_vl
2282 WHERE unit_effectivity_id = p_unit_effectivity_id;
2283 --FOR UPDATE OF due_date NOWAIT; -- not required as we locked the UEs before
2284
2285 -- Get all mr-terminated children under the SR-UE.
2286 CURSOR ahl_term_mr_csr (p_sr_ue_id IN NUMBER) IS
2287 SELECT unit_effectivity_id
2288 FROM ahl_unit_effectivities_b UE, ahl_ue_relationships UER
2289 WHERE UE.unit_effectivity_id = UER.related_ue_id
2290 AND UER.ue_id = p_sr_ue_id
2291 AND UE.status_code = 'MR-TERMINATE';
2292
2293 -- JKJain, NR Analysis and Forecasting
2294 l_fleet_header_id NUMBER;
2295 l_unit_effectivity_rec ahl_unit_effectivity_csr%ROWTYPE;
2296 l_unit_effectivity_id NUMBER;
2297 l_top_unit_effectivity_rec ahl_unit_effectivity_csr%ROWTYPE;
2298 l_parent_ue_id NUMBER;
2299 l_ue_relationship_id NUMBER;
2300 l_new_top_ue_id NUMBER;
2301 l_rowid VARCHAR2(30);
2302
2303 -- added for bug# 7586838
2304 TYPE nbr_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2305 l_delete_ue_id_tbl nbr_tbl_type;
2306 l_index number := 0;
2307
2308 l_visit_status ahl_visits_b.status_code%TYPE;
2309
2310 BEGIN
2311
2312 IF G_DEBUG = 'Y' THEN
2313 AHL_DEBUG_PUB.Debug('Start Unit Sr Deferrals');
2314 END IF;
2315
2316 FOR unit_sr_def_rec IN ahl_unit_sr_def_csr LOOP
2317 IF G_DEBUG = 'Y' THEN
2318 AHL_DEBUG_PUB.Debug('UE_ID:' || unit_sr_def_rec.unit_effectivity_id);
2319 END IF;
2320
2321 -- JKJain, NR Analysis and Forecasting
2322 l_fleet_header_id := null;
2323 IF (G_UC_HEADER_ID IS NOT NULL) THEN
2324 l_fleet_header_id := AHL_UMP_ProcessUnit_PVT.get_fleet_from_unit_asso(G_UC_HEADER_ID,unit_sr_def_rec.due_date,null);
2325 l_unit_effectivity_rec.fleet_header_id := l_fleet_header_id;
2326 END IF;
2327 IF (unit_sr_def_rec.group_match_flag = 'Y') THEN
2328 IF G_DEBUG = 'Y' THEN
2329 AHL_DEBUG_PUB.Debug('Group flag match');
2330 END IF;
2331
2332
2333 -- update unit effectivities with due date and tolerance info.
2334 -- Update top node.
2335
2336 OPEN ahl_unit_effectivity_csr(unit_sr_def_rec.unit_effectivity_id);
2337 FETCH ahl_unit_effectivity_csr INTO l_unit_effectivity_rec;
2338 IF (ahl_unit_effectivity_csr%NOTFOUND) THEN
2339 FND_Message.Set_Name ('AHL','AHL_UMP_PUE_UE_NOTFOUND');
2340 FND_Message.set_token ('UE_ID',unit_sr_def_rec.unit_effectivity_id);
2341 FND_MSG_PUB.ADD;
2342 CLOSE ahl_unit_effectivity_csr;
2343 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2344 ELSE
2345 l_unit_effectivity_rec.tolerance_flag := unit_sr_def_rec.tolerance_flag;
2346 l_unit_effectivity_rec.message_code := unit_sr_def_rec.message_code;
2347 l_unit_effectivity_rec.due_date := unit_sr_def_rec.due_date;
2348 l_unit_effectivity_rec.due_counter_value := unit_sr_def_rec.due_counter_value;
2349 l_unit_effectivity_rec.counter_id := unit_sr_def_rec.counter_id;
2350
2351 -- update record.
2352 IF G_DEBUG = 'Y' THEN
2353 AHL_DEBUG_PUB.Debug('Update Record-' || l_unit_effectivity_rec.unit_effectivity_id);
2354 AHL_DEBUG_PUB.Debug('CSI:MR:' || l_unit_effectivity_rec.csi_item_instance_id || ',' || unit_sr_def_rec.MR_header_id);
2355 END IF;
2356
2357 AHL_UNIT_EFFECTIVITIES_PKG.Update_Row (
2358 X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_rec.unit_effectivity_id,
2359 X_CSI_ITEM_INSTANCE_ID => l_unit_effectivity_rec.csi_item_instance_id,
2360 X_MR_INTERVAL_ID => l_unit_effectivity_rec.mr_interval_id,
2361 X_MR_EFFECTIVITY_ID => l_unit_effectivity_rec.mr_effectivity_id,
2362 X_MR_HEADER_ID => l_unit_effectivity_rec.MR_header_id,
2363 X_STATUS_CODE => l_unit_effectivity_rec.status_code,
2364 X_DUE_DATE => l_unit_effectivity_rec.due_date,
2365 X_DUE_COUNTER_VALUE => l_unit_effectivity_rec.due_counter_value,
2366 X_FORECAST_SEQUENCE => l_unit_effectivity_rec.forecast_sequence,
2367 X_REPETITIVE_MR_FLAG => l_unit_effectivity_rec.repetitive_mr_flag,
2368 X_TOLERANCE_FLAG => l_unit_effectivity_rec.tolerance_flag,
2369 X_REMARKS => l_unit_effectivity_rec.remarks,
2370 X_MESSAGE_CODE => l_unit_effectivity_rec.message_code,
2371 X_PRECEDING_UE_ID => l_unit_effectivity_rec.preceding_ue_id,
2372 X_DATE_RUN => sysdate, /* date run */
2373 X_SET_DUE_DATE => l_unit_effectivity_rec.set_due_date,
2374 X_ACCOMPLISHED_DATE => l_unit_effectivity_rec.accomplished_date,
2375 X_SERVICE_LINE_ID => l_unit_effectivity_rec.service_line_id,
2376 X_PROGRAM_MR_HEADER_ID => l_unit_effectivity_rec.program_mr_header_id,
2377 X_CANCEL_REASON_CODE => l_unit_effectivity_rec.cancel_reason_code,
2378 X_EARLIEST_DUE_DATE => l_unit_effectivity_rec.earliest_due_date,
2379 X_LATEST_DUE_DATE => l_unit_effectivity_rec.latest_due_date,
2380 X_defer_from_ue_id => l_unit_effectivity_rec.defer_from_ue_id,
2381 X_cs_incident_id => l_unit_effectivity_rec.cs_incident_id,
2382 X_qa_collection_id => l_unit_effectivity_rec.qa_collection_id,
2383 X_orig_deferral_ue_id => l_unit_effectivity_rec.orig_deferral_ue_id,
2384 X_application_usg_code => l_unit_effectivity_rec.application_usg_code,
2385 X_object_type => l_unit_effectivity_rec.object_type,
2386 X_counter_id => l_unit_effectivity_rec.counter_id,
2387 X_MANUALLY_PLANNED_FLAG => l_unit_effectivity_rec.MANUALLY_PLANNED_FLAG,
2388 X_LOG_SERIES_CODE => l_unit_effectivity_rec.log_series_code,
2389 X_LOG_SERIES_NUMBER => l_unit_effectivity_rec.log_series_number,
2390 X_FLIGHT_NUMBER => l_unit_effectivity_rec.flight_number,
2391 X_MEL_CDL_TYPE_CODE => l_unit_effectivity_rec.mel_cdl_type_code,
2392 X_POSITION_PATH_ID => l_unit_effectivity_rec.position_path_id,
2393 X_ATA_CODE => l_unit_effectivity_rec.ATA_CODE,
2394 X_UNIT_CONFIG_HEADER_ID => G_UC_HEADER_ID, --l_unit_effectivity_rec.unit_config_header_id,
2395 X_ATTRIBUTE_CATEGORY => l_unit_effectivity_rec.ATTRIBUTE_CATEGORY,
2396 X_ATTRIBUTE1 => l_unit_effectivity_rec.ATTRIBUTE1,
2397 X_ATTRIBUTE2 => l_unit_effectivity_rec.ATTRIBUTE2,
2398 X_ATTRIBUTE3 => l_unit_effectivity_rec.ATTRIBUTE3,
2399 X_ATTRIBUTE4 => l_unit_effectivity_rec.ATTRIBUTE4,
2400 X_ATTRIBUTE5 => l_unit_effectivity_rec.ATTRIBUTE5,
2401 X_ATTRIBUTE6 => l_unit_effectivity_rec.ATTRIBUTE6,
2402 X_ATTRIBUTE7 => l_unit_effectivity_rec.ATTRIBUTE7,
2403 X_ATTRIBUTE8 => l_unit_effectivity_rec.ATTRIBUTE8,
2404 X_ATTRIBUTE9 => l_unit_effectivity_rec.ATTRIBUTE9,
2405 X_ATTRIBUTE10 => l_unit_effectivity_rec.ATTRIBUTE10,
2406 X_ATTRIBUTE11 => l_unit_effectivity_rec.ATTRIBUTE11,
2407 X_ATTRIBUTE12 => l_unit_effectivity_rec.ATTRIBUTE12,
2408 X_ATTRIBUTE13 => l_unit_effectivity_rec.ATTRIBUTE13,
2409 X_ATTRIBUTE14 => l_unit_effectivity_rec.ATTRIBUTE14,
2410 X_ATTRIBUTE15 => l_unit_effectivity_rec.ATTRIBUTE15,
2411 X_OBJECT_VERSION_NUMBER => l_unit_effectivity_rec.object_version_number+1,
2412 X_LAST_UPDATE_DATE => sysdate,
2413 X_LAST_UPDATED_BY => fnd_global.user_id,
2414 X_LAST_UPDATE_LOGIN => fnd_global.login_id,
2415 -- JKJain, NR Analysis and Forecasting
2416 X_FLEET_HEADER_ID => l_unit_effectivity_rec.fleet_header_id );
2417 CLOSE ahl_unit_effectivity_csr;
2418
2419 IF (l_unit_effectivity_rec.due_date IS NULL
2420 AND l_unit_effectivity_rec.object_type = 'MR') THEN
2421 -- Delete the corresponding rows in ahl_schedule materials for this ue.
2422 Delete_Sch_Materials(l_unit_effectivity_rec.unit_effectivity_id);
2423 END IF;
2424
2425 -- Update all group children.
2426 FOR l_ue_rec IN ahl_ue_csr (unit_sr_def_rec.unit_effectivity_id) LOOP
2427 -- Read Unit Effectivity record.
2428 OPEN ahl_unit_effectivity_csr(l_ue_rec.related_ue_id);
2429 FETCH ahl_unit_effectivity_csr INTO l_unit_effectivity_rec;
2430 IF (ahl_unit_effectivity_csr%NOTFOUND) THEN
2431 FND_Message.Set_Name ('AHL','AHL_UMP_PUE_UE_NOTFOUND');
2432 FND_Message.set_token ('UE_ID',l_ue_rec.related_ue_id);
2433 FND_MSG_PUB.ADD;
2434 CLOSE ahl_unit_effectivity_csr;
2435 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2436 ELSE
2437 l_unit_effectivity_rec.tolerance_flag := unit_sr_def_rec.tolerance_flag;
2438 l_unit_effectivity_rec.message_code := unit_sr_def_rec.message_code;
2439 l_unit_effectivity_rec.due_date := unit_sr_def_rec.due_date;
2440 l_unit_effectivity_rec.due_counter_value := unit_sr_def_rec.due_counter_value;
2441
2442 l_unit_effectivity_rec.counter_id := unit_sr_def_rec.counter_id;
2443
2444 -- update record.
2445 IF G_DEBUG = 'Y' THEN
2446 AHL_DEBUG_PUB.Debug('Update Record-' || l_unit_effectivity_rec.unit_effectivity_id);
2447 AHL_DEBUG_PUB.Debug('CSI:MR:' || l_unit_effectivity_rec.csi_item_instance_id || ',' || l_unit_effectivity_rec.MR_header_id);
2448 END IF;
2449
2450 AHL_UNIT_EFFECTIVITIES_PKG.Update_Row (
2451 X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_rec.unit_effectivity_id,
2452 X_CSI_ITEM_INSTANCE_ID => l_unit_effectivity_rec.csi_item_instance_id,
2453 X_MR_INTERVAL_ID => l_unit_effectivity_rec.mr_interval_id,
2454 X_MR_EFFECTIVITY_ID => l_unit_effectivity_rec.mr_effectivity_id,
2455 X_MR_HEADER_ID => l_unit_effectivity_rec.MR_header_id,
2456 X_STATUS_CODE => l_unit_effectivity_rec.status_code,
2457 X_DUE_DATE => l_unit_effectivity_rec.due_date,
2458 X_DUE_COUNTER_VALUE => l_unit_effectivity_rec.due_counter_value,
2459 X_FORECAST_SEQUENCE => l_unit_effectivity_rec.forecast_sequence,
2460 X_REPETITIVE_MR_FLAG => l_unit_effectivity_rec.repetitive_mr_flag,
2461 X_TOLERANCE_FLAG => l_unit_effectivity_rec.tolerance_flag,
2462 X_REMARKS => l_unit_effectivity_rec.remarks,
2463 X_MESSAGE_CODE => l_unit_effectivity_rec.message_code,
2464 X_PRECEDING_UE_ID => l_unit_effectivity_rec.preceding_ue_id,
2465 X_DATE_RUN => sysdate, /* date run */
2466 X_SET_DUE_DATE => l_unit_effectivity_rec.set_due_date,
2467 X_ACCOMPLISHED_DATE => l_unit_effectivity_rec.accomplished_date,
2468 X_SERVICE_LINE_ID => l_unit_effectivity_rec.service_line_id,
2469 X_PROGRAM_MR_HEADER_ID => l_unit_effectivity_rec.program_mr_header_id,
2470 X_CANCEL_REASON_CODE => l_unit_effectivity_rec.cancel_reason_code,
2471 X_EARLIEST_DUE_DATE => l_unit_effectivity_rec.earliest_due_date,
2472 X_LATEST_DUE_DATE => l_unit_effectivity_rec.latest_due_date,
2473 X_defer_from_ue_id => l_unit_effectivity_rec.defer_from_ue_id,
2474 X_cs_incident_id => l_unit_effectivity_rec.cs_incident_id,
2475 X_qa_collection_id => l_unit_effectivity_rec.qa_collection_id,
2476 X_orig_deferral_ue_id => l_unit_effectivity_rec.orig_deferral_ue_id,
2477 X_application_usg_code => l_unit_effectivity_rec.application_usg_code,
2478 X_object_type => l_unit_effectivity_rec.object_type,
2479 X_counter_id => l_unit_effectivity_rec.counter_id,
2480 X_MANUALLY_PLANNED_FLAG => l_unit_effectivity_rec.MANUALLY_PLANNED_FLAG,
2481 X_LOG_SERIES_CODE => l_unit_effectivity_rec.log_series_code,
2482 X_LOG_SERIES_NUMBER => l_unit_effectivity_rec.log_series_number,
2483 X_FLIGHT_NUMBER => l_unit_effectivity_rec.flight_number,
2484 X_MEL_CDL_TYPE_CODE => l_unit_effectivity_rec.mel_cdl_type_code,
2485 X_POSITION_PATH_ID => l_unit_effectivity_rec.position_path_id,
2486 X_ATA_CODE => l_unit_effectivity_rec.ATA_CODE,
2487 X_UNIT_CONFIG_HEADER_ID => G_UC_HEADER_ID, --l_unit_effectivity_rec.unit_config_header_id,
2488 X_ATTRIBUTE_CATEGORY => l_unit_effectivity_rec.ATTRIBUTE_CATEGORY,
2489 X_ATTRIBUTE1 => l_unit_effectivity_rec.ATTRIBUTE1,
2490 X_ATTRIBUTE2 => l_unit_effectivity_rec.ATTRIBUTE2,
2491 X_ATTRIBUTE3 => l_unit_effectivity_rec.ATTRIBUTE3,
2492 X_ATTRIBUTE4 => l_unit_effectivity_rec.ATTRIBUTE4,
2493 X_ATTRIBUTE5 => l_unit_effectivity_rec.ATTRIBUTE5,
2494 X_ATTRIBUTE6 => l_unit_effectivity_rec.ATTRIBUTE6,
2495 X_ATTRIBUTE7 => l_unit_effectivity_rec.ATTRIBUTE7,
2496 X_ATTRIBUTE8 => l_unit_effectivity_rec.ATTRIBUTE8,
2497 X_ATTRIBUTE9 => l_unit_effectivity_rec.ATTRIBUTE9,
2498 X_ATTRIBUTE10 => l_unit_effectivity_rec.ATTRIBUTE10,
2499 X_ATTRIBUTE11 => l_unit_effectivity_rec.ATTRIBUTE11,
2500 X_ATTRIBUTE12 => l_unit_effectivity_rec.ATTRIBUTE12,
2501 X_ATTRIBUTE13 => l_unit_effectivity_rec.ATTRIBUTE13,
2502 X_ATTRIBUTE14 => l_unit_effectivity_rec.ATTRIBUTE14,
2503 X_ATTRIBUTE15 => l_unit_effectivity_rec.ATTRIBUTE15,
2504 X_OBJECT_VERSION_NUMBER => l_unit_effectivity_rec.object_version_number+1,
2505 X_LAST_UPDATE_DATE => sysdate,
2506 X_LAST_UPDATED_BY => fnd_global.user_id,
2507 X_LAST_UPDATE_LOGIN => fnd_global.login_id );
2508
2509
2510 -- Delete the corresponding rows in ahl_schedule materials for this ue.
2511 IF (l_unit_effectivity_rec.due_date IS NULL
2512 AND l_unit_effectivity_rec.object_type = 'MR') THEN
2513 Delete_Sch_Materials(l_unit_effectivity_rec.unit_effectivity_id);
2514 END IF;
2515
2516 END IF;
2517 CLOSE ahl_unit_effectivity_csr;
2518 END LOOP; -- l_ue_rec
2519
2520 END IF; -- ahl_unit_effectivity_csr
2521
2522 ELSE /* group match = 'N' */
2523 -- Create new group.
2524 OPEN ahl_unit_effectivity_csr(unit_sr_def_rec.unit_effectivity_id);
2525 FETCH ahl_unit_effectivity_csr INTO l_unit_effectivity_rec;
2526 IF (ahl_unit_effectivity_csr%NOTFOUND) THEN
2527 FND_Message.Set_Name ('AHL','AHL_UMP_PUE_UE_NOTFOUND');
2528 FND_Message.set_token ('UE_ID',unit_sr_def_rec.unit_effectivity_id);
2529 FND_MSG_PUB.ADD;
2530 CLOSE ahl_unit_effectivity_csr;
2531 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2532 ELSE
2533 l_unit_effectivity_rec.tolerance_flag := unit_sr_def_rec.tolerance_flag;
2534 l_unit_effectivity_rec.message_code := unit_sr_def_rec.message_code;
2535 l_unit_effectivity_rec.due_date := unit_sr_def_rec.due_date;
2536 l_unit_effectivity_rec.due_counter_value := unit_sr_def_rec.due_counter_value;
2537 l_unit_effectivity_rec.object_type := unit_sr_def_rec.object_type;
2538
2539 -- track old UE for deletion.
2540 l_index := l_index + 1;
2541 l_delete_ue_id_tbl(l_index) := unit_sr_def_rec.unit_effectivity_id;
2542
2543 IF G_DEBUG = 'Y' THEN
2544 AHL_DEBUG_PUB.Debug('Insert Record-');
2545 AHL_DEBUG_PUB.Debug('CSI:MR:' || l_unit_effectivity_rec.csi_item_instance_id || ',' || l_unit_effectivity_rec.MR_header_id);
2546 END IF;
2547
2548 AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row (
2549 X_ROWID => l_rowid,
2550 X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_id,
2551 X_CSI_ITEM_INSTANCE_ID => l_unit_effectivity_rec.csi_item_instance_id,
2552 X_MR_INTERVAL_ID => null,
2553 X_MR_EFFECTIVITY_ID => null,
2554 X_MR_HEADER_ID => l_unit_effectivity_rec.MR_header_id,
2555 X_STATUS_CODE => null, /* status_code */
2556 X_DUE_DATE => l_unit_effectivity_rec.due_date,
2557 X_DUE_COUNTER_VALUE => l_unit_effectivity_rec.due_counter_value,
2558 X_FORECAST_SEQUENCE => null,
2559 X_REPETITIVE_MR_FLAG => 'Y', -- modified to fix bug# 9078331
2560 X_TOLERANCE_FLAG => l_unit_effectivity_rec.tolerance_flag,
2561 X_REMARKS => l_unit_effectivity_rec.remarks,
2562 X_MESSAGE_CODE => l_unit_effectivity_rec.message_code,
2563 X_PRECEDING_UE_ID => null, /* preceding_ue_id */
2564 X_DATE_RUN => sysdate, /* date_run */
2565 X_SET_DUE_DATE => null, /* set due date */
2566 X_ACCOMPLISHED_DATE => null, /* accomplished date */
2567 X_SERVICE_LINE_ID => l_unit_effectivity_rec.service_line_id,
2568 X_PROGRAM_MR_HEADER_ID => l_unit_effectivity_rec.program_mr_header_id,
2569 X_CANCEL_REASON_CODE => null, /* cancel_reason_code */
2570 X_EARLIEST_DUE_DATE => l_unit_effectivity_rec.earliest_due_date,
2571 X_LATEST_DUE_DATE => l_unit_effectivity_rec.latest_due_date,
2572 X_defer_from_ue_id => l_unit_effectivity_rec.defer_from_ue_id,
2573 X_cs_incident_id => l_unit_effectivity_rec.cs_incident_id,
2574 X_qa_collection_id => l_unit_effectivity_rec.qa_collection_id,
2575 X_orig_deferral_ue_id => l_unit_effectivity_rec.orig_deferral_ue_id,
2576 X_application_usg_code => l_unit_effectivity_rec.application_usg_code,
2577 X_object_type => l_unit_effectivity_rec.object_type,
2578 X_counter_id => l_unit_effectivity_rec.counter_id,
2579 X_MANUALLY_PLANNED_FLAG => l_unit_effectivity_rec.manually_planned_flag,
2580 X_LOG_SERIES_CODE => l_unit_effectivity_rec.log_series_code,
2581 X_LOG_SERIES_NUMBER => l_unit_effectivity_rec.log_series_number,
2582 X_FLIGHT_NUMBER => l_unit_effectivity_rec.flight_number,
2583 X_MEL_CDL_TYPE_CODE => l_unit_effectivity_rec.mel_cdl_type_code,
2584 X_POSITION_PATH_ID => l_unit_effectivity_rec.position_path_id,
2585 X_ATA_CODE => l_unit_effectivity_rec.ATA_CODE,
2586 X_UNIT_CONFIG_HEADER_ID => G_UC_HEADER_ID,--l_unit_effectivity_rec.unit_config_header_id, -- JKJain, NR Analysis and Forecasting
2587 X_ATTRIBUTE_CATEGORY => null, /* ATTRIBUTE_CATEGORY */
2588 X_ATTRIBUTE1 => null, /* ATTRIBUTE1 */
2589 X_ATTRIBUTE2 => null, /* ATTRIBUTE2 */
2590 X_ATTRIBUTE3 => null, /* ATTRIBUTE3 */
2591 X_ATTRIBUTE4 => null, /* ATTRIBUTE4 */
2592 X_ATTRIBUTE5 => null, /* ATTRIBUTE5 */
2593 X_ATTRIBUTE6 => null, /* ATTRIBUTE6 */
2594 X_ATTRIBUTE7 => null, /* ATTRIBUTE7 */
2595 X_ATTRIBUTE8 => null, /* ATTRIBUTE8 */
2596 X_ATTRIBUTE9 => null, /* ATTRIBUTE9 */
2597 X_ATTRIBUTE10 => null, /* ATTRIBUTE10 */
2598 X_ATTRIBUTE11 => null, /* ATTRIBUTE11 */
2599 X_ATTRIBUTE12 => null, /* ATTRIBUTE12 */
2600 X_ATTRIBUTE13 => null, /* ATTRIBUTE13 */
2601 X_ATTRIBUTE14 => null, /* ATTRIBUTE14 */
2602 X_ATTRIBUTE15 => null, /* ATTRIBUTE15 */
2603 X_OBJECT_VERSION_NUMBER => 1, /* object version */
2604 X_CREATION_DATE => sysdate,
2605 X_CREATED_BY => fnd_global.user_id,
2606 X_LAST_UPDATE_DATE => sysdate,
2607 X_LAST_UPDATED_BY => fnd_global.user_id,
2608 X_LAST_UPDATE_LOGIN => fnd_global.login_id );
2609
2610 l_new_top_ue_id := l_unit_effectivity_id;
2611
2612 -- update new UE ID for top node.
2613 UPDATE ahl_temp_unit_SR_deferrals
2614 SET unit_effectivity_id = l_new_top_ue_id,
2615 object_type = l_unit_effectivity_rec.object_type
2616 WHERE CURRENT OF ahl_unit_sr_def_csr ;
2617
2618 CLOSE ahl_unit_effectivity_csr;
2619
2620 -- Associate deferral threshold to new UE if exists (deferral from UMP).
2621 UPDATE AHL_UNIT_DEFERRALS_B
2622 SET unit_effectivity_id = l_new_top_ue_id,
2623 last_update_date = sysdate,
2624 object_version_number = object_version_number + 1,
2625 LAST_UPDATED_BY = fnd_global.user_id,
2626 LAST_UPDATE_LOGIN = fnd_global.login_id
2627 WHERE unit_effectivity_id = unit_sr_def_rec.unit_effectivity_id;
2628
2629 -- added for SB Enh - only for top node.
2630 IF (unit_sr_def_rec.object_type = 'MR') THEN
2631 UPDATE AHL_UNIT_EFFECTIVITIES_B
2632 SET accomplish_trigger_type = l_unit_effectivity_rec.accomplish_trigger_type,
2633 loop_chain_seq_num = l_unit_effectivity_rec.loop_chain_seq_num,
2634 start_lc_ue_id = l_unit_effectivity_rec.start_lc_ue_id
2635 WHERE unit_effectivity_id = l_new_top_ue_id;
2636 END IF;
2637
2638 -- Create group children.
2639 FOR ahl_temp_rec IN ahl_temp_csr (
2640 unit_sr_def_rec.csi_item_instance_id,
2641 unit_sr_def_rec.mr_header_id,
2642 unit_sr_def_rec.unit_effectivity_id)
2643 LOOP
2644 -- Initialize.
2645
2646 AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row (
2647 X_ROWID => l_rowid,
2648 X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_id,
2649 X_CSI_ITEM_INSTANCE_ID => ahl_temp_rec.csi_item_instance_id,
2650 X_MR_INTERVAL_ID => null,
2651 X_MR_EFFECTIVITY_ID => null,
2652 X_MR_HEADER_ID => ahl_temp_rec.mr_header_id,
2653 X_STATUS_CODE => null, /* status_code */
2654 X_DUE_DATE => l_unit_effectivity_rec.due_date,
2655 X_DUE_COUNTER_VALUE => l_unit_effectivity_rec.due_counter_value,
2656 X_FORECAST_SEQUENCE => null,
2657 X_REPETITIVE_MR_FLAG => 'Y', -- modified to fix bug# 9078331
2658 X_TOLERANCE_FLAG => l_unit_effectivity_rec.tolerance_flag,
2659 X_REMARKS => l_unit_effectivity_rec.remarks,
2660 X_MESSAGE_CODE => l_unit_effectivity_rec.message_code,
2661 X_PRECEDING_UE_ID => null, /* preceding_ue_id */
2662 X_DATE_RUN => sysdate, /* date_run */
2663 X_SET_DUE_DATE => null, /* set due date */
2664 X_ACCOMPLISHED_DATE => null, /* accomplished date */
2665 X_SERVICE_LINE_ID => l_unit_effectivity_rec.service_line_id,
2666 X_PROGRAM_MR_HEADER_ID => l_unit_effectivity_rec.program_mr_header_id,
2667 X_CANCEL_REASON_CODE => null, /* cancel_reason_code */
2668 X_EARLIEST_DUE_DATE => l_unit_effectivity_rec.earliest_due_date,
2669 X_LATEST_DUE_DATE => l_unit_effectivity_rec.latest_due_date,
2670 X_defer_from_ue_id => l_unit_effectivity_rec.defer_from_ue_id,
2671 X_cs_incident_id => l_unit_effectivity_rec.cs_incident_id,
2672 X_qa_collection_id => l_unit_effectivity_rec.qa_collection_id,
2673 X_orig_deferral_ue_id => l_unit_effectivity_rec.orig_deferral_ue_id,
2674 X_application_usg_code => l_unit_effectivity_rec.application_usg_code,
2675 X_object_type => 'MR',
2676 X_counter_id => l_unit_effectivity_rec.counter_id,
2677 X_manually_planned_flag => l_unit_effectivity_rec.manually_planned_flag,
2678 X_LOG_SERIES_CODE => l_unit_effectivity_rec.log_series_code,
2679 X_LOG_SERIES_NUMBER => l_unit_effectivity_rec.log_series_number,
2680 X_FLIGHT_NUMBER => l_unit_effectivity_rec.flight_number,
2681 X_MEL_CDL_TYPE_CODE => l_unit_effectivity_rec.mel_cdl_type_code,
2682 X_POSITION_PATH_ID => l_unit_effectivity_rec.position_path_id,
2683 X_ATA_CODE => l_unit_effectivity_rec.ATA_CODE,
2684 X_UNIT_CONFIG_HEADER_ID => G_UC_HEADER_ID,--l_unit_effectivity_rec.unit_config_header_id, -- JKJain, NR Analysis and Forecasting
2685 X_ATTRIBUTE_CATEGORY => null, /* ATTRIBUTE_CATEGORY */
2686 X_ATTRIBUTE1 => null, /* ATTRIBUTE1 */
2687 X_ATTRIBUTE2 => null, /* ATTRIBUTE2 */
2688 X_ATTRIBUTE3 => null, /* ATTRIBUTE3 */
2689 X_ATTRIBUTE4 => null, /* ATTRIBUTE4 */
2690 X_ATTRIBUTE5 => null, /* ATTRIBUTE5 */
2691 X_ATTRIBUTE6 => null, /* ATTRIBUTE6 */
2692 X_ATTRIBUTE7 => null, /* ATTRIBUTE7 */
2693 X_ATTRIBUTE8 => null, /* ATTRIBUTE8 */
2694 X_ATTRIBUTE9 => null, /* ATTRIBUTE9 */
2695 X_ATTRIBUTE10 => null, /* ATTRIBUTE10 */
2696 X_ATTRIBUTE11 => null, /* ATTRIBUTE11 */
2697 X_ATTRIBUTE12 => null, /* ATTRIBUTE12 */
2698 X_ATTRIBUTE13 => null, /* ATTRIBUTE13 */
2699 X_ATTRIBUTE14 => null, /* ATTRIBUTE14 */
2700 X_ATTRIBUTE15 => null, /* ATTRIBUTE15 */
2701 X_OBJECT_VERSION_NUMBER => 1, /* object version */
2702 X_CREATION_DATE => sysdate,
2703 X_CREATED_BY => fnd_global.user_id,
2704 X_LAST_UPDATE_DATE => sysdate,
2705 X_LAST_UPDATED_BY => fnd_global.user_id,
2706 X_LAST_UPDATE_LOGIN => fnd_global.login_id );
2707
2708 UPDATE ahl_temp_unit_SR_deferrals
2709 SET unit_effectivity_id = l_unit_effectivity_id,
2710 object_type = l_unit_effectivity_rec.object_type
2711 WHERE CURRENT OF ahl_temp_csr ;
2712
2713 END LOOP;
2714
2715 -- Build relationships.
2716 FOR ahl_temp_rec IN ahl_temp_csr (
2717 unit_sr_def_rec.csi_item_instance_id,
2718 unit_sr_def_rec.mr_header_id,
2719 unit_sr_def_rec.unit_effectivity_id)
2720 LOOP
2721
2722 OPEN ahl_temp_parent_csr(ahl_temp_rec.parent_csi_item_instance_id,
2723 ahl_temp_rec.parent_mr_header_id,
2724 unit_sr_def_rec.csi_item_instance_id,
2725 unit_sr_def_rec.mr_header_id,
2726 unit_sr_def_rec.unit_effectivity_id);
2727
2728 FETCH ahl_temp_parent_csr INTO l_parent_ue_id;
2729 IF (ahl_temp_parent_csr%NOTFOUND) THEN
2730 -- parent is root UE.
2731 l_parent_ue_id := l_new_top_ue_id;
2732 --FND_MESSAGE.Set_Name ('AHL','AHL_UMP_PUE_PARENT_NOTFOUND');
2733 --FND_MESSAGE.Set_Token ('INST_ID',ahl_temp_rec.csi_item_instance_id);
2734 --FND_MESSAGE.Set_Token ('MR_ID',ahl_temp_rec.mr_header_id);
2735 --FND_MSG_PUB.ADD;
2736 --CLOSE ahl_temp_parent_csr;
2737 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2738 END IF;
2739 CLOSE ahl_temp_parent_csr;
2740
2741 -- Insert into ahl_ue_relationships.
2742 AHL_UE_RELATIONSHIPS_PKG.Insert_Row(
2743 X_UE_RELATIONSHIP_ID => l_ue_relationship_id,
2744 X_UE_ID => l_parent_ue_id,
2745 X_RELATED_UE_ID => ahl_temp_rec.unit_effectivity_id,
2746 X_RELATIONSHIP_CODE => 'PARENT',
2747 X_ORIGINATOR_UE_ID => l_new_top_ue_id,
2748 X_ATTRIBUTE_CATEGORY => null, /* ATTRIBUTE_CATEGORY */
2749 X_ATTRIBUTE1 => null, /* ATTRIBUTE1 */
2750 X_ATTRIBUTE2 => null, /* ATTRIBUTE2 */
2751 X_ATTRIBUTE3 => null, /* ATTRIBUTE3 */
2752 X_ATTRIBUTE4 => null, /* ATTRIBUTE4 */
2753 X_ATTRIBUTE5 => null, /* ATTRIBUTE5 */
2754 X_ATTRIBUTE6 => null, /* ATTRIBUTE6 */
2755 X_ATTRIBUTE7 => null, /* ATTRIBUTE7 */
2756 X_ATTRIBUTE8 => null, /* ATTRIBUTE8 */
2757 X_ATTRIBUTE9 => null, /* ATTRIBUTE9 */
2758 X_ATTRIBUTE10 => null, /* ATTRIBUTE10 */
2759 X_ATTRIBUTE11 => null, /* ATTRIBUTE11 */
2760 X_ATTRIBUTE12 => null, /* ATTRIBUTE12 */
2761 X_ATTRIBUTE13 => null, /* ATTRIBUTE13 */
2762 X_ATTRIBUTE14 => null, /* ATTRIBUTE14 */
2763 X_ATTRIBUTE15 => null, /* ATTRIBUTE15 */
2764 X_OBJECT_VERSION_NUMBER => 1,
2765 X_LAST_UPDATE_DATE => sysdate,
2766 X_LAST_UPDATED_BY => fnd_global.user_id,
2767 X_CREATION_DATE => sysdate,
2768 X_CREATED_BY => fnd_global.user_id,
2769 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
2770
2771 END LOOP;
2772
2773 -- If object_type = 'SR', check for any terminated MR's for immediate children of the SR-UE.
2774 -- If exists, add them to the new copied node.
2775
2776 IF (unit_sr_def_rec.object_type = 'SR') THEN
2777 FOR ahl_term_MR_rec IN ahl_term_MR_csr(unit_sr_def_rec.unit_effectivity_id) LOOP
2778 -- Get Unit effectivity details.
2779 OPEN ahl_unit_effectivity_csr(unit_sr_def_rec.unit_effectivity_id);
2780 FETCH ahl_unit_effectivity_csr INTO l_unit_effectivity_rec;
2781 IF (ahl_unit_effectivity_csr%NOTFOUND) THEN
2782 FND_Message.Set_Name ('AHL','AHL_UMP_PUE_UE_NOTFOUND');
2783 FND_Message.set_token ('UE_ID',unit_sr_def_rec.unit_effectivity_id);
2784 FND_MSG_PUB.ADD;
2785 CLOSE ahl_unit_effectivity_csr;
2786 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2787 ELSE
2788 AHL_UNIT_EFFECTIVITIES_PKG.Insert_Row (
2789 X_ROWID => l_rowid,
2790 X_UNIT_EFFECTIVITY_ID => l_unit_effectivity_id,
2791 X_CSI_ITEM_INSTANCE_ID => l_unit_effectivity_rec.csi_item_instance_id,
2792 X_MR_INTERVAL_ID => l_unit_effectivity_rec.MR_INTERVAL_ID,
2793 X_MR_EFFECTIVITY_ID => l_unit_effectivity_rec.MR_EFFECTIVITY_ID,
2794 X_MR_HEADER_ID => l_unit_effectivity_rec.MR_header_id,
2795 X_STATUS_CODE => l_unit_effectivity_rec.STATUS_CODE, /* status_code */
2796 X_DUE_DATE => l_unit_effectivity_rec.due_date,
2797 X_DUE_COUNTER_VALUE => l_unit_effectivity_rec.due_counter_value,
2798 X_FORECAST_SEQUENCE => l_unit_effectivity_rec.FORECAST_SEQUENCE,
2799 X_REPETITIVE_MR_FLAG => l_unit_effectivity_rec.REPETITIVE_MR_FLAG,
2800 X_TOLERANCE_FLAG => l_unit_effectivity_rec.tolerance_flag,
2801 X_REMARKS => l_unit_effectivity_rec.remarks,
2802 X_MESSAGE_CODE => l_unit_effectivity_rec.message_code,
2803 X_PRECEDING_UE_ID => l_unit_effectivity_rec.PRECEDING_UE_ID, /* preceding_ue_id */
2804 X_DATE_RUN => sysdate, /* date_run */
2805 X_SET_DUE_DATE => null, /* set due date */
2806 X_ACCOMPLISHED_DATE => l_unit_effectivity_rec.ACCOMPLISHED_DATE, /* accomplished date */
2807 X_SERVICE_LINE_ID => l_unit_effectivity_rec.service_line_id,
2808 X_PROGRAM_MR_HEADER_ID => l_unit_effectivity_rec.program_mr_header_id,
2809 X_CANCEL_REASON_CODE => l_unit_effectivity_rec.CANCEL_REASON_CODE, /* cancel_reason_code */
2810 X_EARLIEST_DUE_DATE => l_unit_effectivity_rec.earliest_due_date,
2811 X_LATEST_DUE_DATE => l_unit_effectivity_rec.latest_due_date,
2812 X_defer_from_ue_id => l_unit_effectivity_rec.defer_from_ue_id,
2813 X_cs_incident_id => l_unit_effectivity_rec.cs_incident_id,
2814 X_qa_collection_id => l_unit_effectivity_rec.qa_collection_id,
2815 X_orig_deferral_ue_id => l_unit_effectivity_rec.orig_deferral_ue_id,
2816 X_application_usg_code => l_unit_effectivity_rec.application_usg_code,
2817 X_object_type => l_unit_effectivity_rec.object_type,
2818 X_counter_id => l_unit_effectivity_rec.counter_id,
2819 x_manually_planned_flag => l_unit_effectivity_rec.manually_planned_flag,
2820 X_LOG_SERIES_CODE => l_unit_effectivity_rec.log_series_code,
2821 X_LOG_SERIES_NUMBER => l_unit_effectivity_rec.log_series_number,
2822 X_FLIGHT_NUMBER => l_unit_effectivity_rec.flight_number,
2823 X_MEL_CDL_TYPE_CODE => l_unit_effectivity_rec.mel_cdl_type_code,
2824 X_POSITION_PATH_ID => l_unit_effectivity_rec.position_path_id,
2825 X_ATA_CODE => l_unit_effectivity_rec.ATA_CODE,
2826 X_UNIT_CONFIG_HEADER_ID => l_unit_effectivity_rec.unit_config_header_id,
2827 X_ATTRIBUTE_CATEGORY => null, /* ATTRIBUTE_CATEGORY */
2828 X_ATTRIBUTE1 => null, /* ATTRIBUTE1 */
2829 X_ATTRIBUTE2 => null, /* ATTRIBUTE2 */
2830 X_ATTRIBUTE3 => null, /* ATTRIBUTE3 */
2831 X_ATTRIBUTE4 => null, /* ATTRIBUTE4 */
2832 X_ATTRIBUTE5 => null, /* ATTRIBUTE5 */
2833 X_ATTRIBUTE6 => null, /* ATTRIBUTE6 */
2834 X_ATTRIBUTE7 => null, /* ATTRIBUTE7 */
2835 X_ATTRIBUTE8 => null, /* ATTRIBUTE8 */
2836 X_ATTRIBUTE9 => null, /* ATTRIBUTE9 */
2837 X_ATTRIBUTE10 => null, /* ATTRIBUTE10 */
2838 X_ATTRIBUTE11 => null, /* ATTRIBUTE11 */
2839 X_ATTRIBUTE12 => null, /* ATTRIBUTE12 */
2840 X_ATTRIBUTE13 => null, /* ATTRIBUTE13 */
2841 X_ATTRIBUTE14 => null, /* ATTRIBUTE14 */
2842 X_ATTRIBUTE15 => null, /* ATTRIBUTE15 */
2843 X_OBJECT_VERSION_NUMBER => 1, /* object version */
2844 X_CREATION_DATE => sysdate,
2845 X_CREATED_BY => fnd_global.user_id,
2846 X_LAST_UPDATE_DATE => sysdate,
2847 X_LAST_UPDATED_BY => fnd_global.user_id,
2848 X_LAST_UPDATE_LOGIN => fnd_global.login_id );
2849
2850 CLOSE ahl_unit_effectivity_csr;
2851
2852 -- Now create the relationship record.
2853 AHL_UE_RELATIONSHIPS_PKG.Insert_Row(
2854 X_UE_RELATIONSHIP_ID => l_ue_relationship_id,
2855 X_UE_ID => l_parent_ue_id,
2856 X_RELATED_UE_ID => l_unit_effectivity_id,
2857 X_RELATIONSHIP_CODE => 'PARENT',
2858 X_ORIGINATOR_UE_ID => l_new_top_ue_id,
2859 X_ATTRIBUTE_CATEGORY => null, /* ATTRIBUTE_CATEGORY */
2860 X_ATTRIBUTE1 => null, /* ATTRIBUTE1 */
2861 X_ATTRIBUTE2 => null, /* ATTRIBUTE2 */
2862 X_ATTRIBUTE3 => null, /* ATTRIBUTE3 */
2863 X_ATTRIBUTE4 => null, /* ATTRIBUTE4 */
2864 X_ATTRIBUTE5 => null, /* ATTRIBUTE5 */
2865 X_ATTRIBUTE6 => null, /* ATTRIBUTE6 */
2866 X_ATTRIBUTE7 => null, /* ATTRIBUTE7 */
2867 X_ATTRIBUTE8 => null, /* ATTRIBUTE8 */
2868 X_ATTRIBUTE9 => null, /* ATTRIBUTE9 */
2869 X_ATTRIBUTE10 => null, /* ATTRIBUTE10 */
2870 X_ATTRIBUTE11 => null, /* ATTRIBUTE11 */
2871 X_ATTRIBUTE12 => null, /* ATTRIBUTE12 */
2872 X_ATTRIBUTE13 => null, /* ATTRIBUTE13 */
2873 X_ATTRIBUTE14 => null, /* ATTRIBUTE14 */
2874 X_ATTRIBUTE15 => null, /* ATTRIBUTE15 */
2875 X_OBJECT_VERSION_NUMBER => 1,
2876 X_LAST_UPDATE_DATE => sysdate,
2877 X_LAST_UPDATED_BY => fnd_global.user_id,
2878 X_CREATION_DATE => sysdate,
2879 X_CREATED_BY => fnd_global.user_id,
2880 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
2881
2882 END IF;
2883 END LOOP;
2884 END IF;
2885 END IF;
2886 END IF;
2887
2888 END LOOP;
2889
2890 -- process l_delete_ue_id_tbl for UE deletion.
2891 IF (l_delete_ue_id_tbl.count > 0) THEN
2892
2893 FOR i IN l_delete_ue_id_tbl.FIRST..l_delete_ue_id_tbl.LAST LOOP
2894
2895 -- check if UE assigned to a visit.
2896 l_visit_status := AHL_UMP_UTIL_PKG.get_Visit_Status (l_delete_ue_id_tbl(i));
2897
2898 -- only if visit is in planning status we must mark an exception.
2899 -- if visit is already on the floor, we do nothing.
2900 IF (l_visit_status = 'PLANNING') THEN
2901 FOR l_ue_rec IN ahl_ue_csr(l_delete_ue_id_tbl(i)) LOOP
2902 -- Delete the corresponding rows in ahl_schedule materials for this ue.
2903 Delete_Sch_Materials(l_ue_rec.related_ue_id);
2904 END LOOP;
2905
2906 -- update unit effectivity record to exception.
2907 UPDATE AHL_UNIT_EFFECTIVITIES_B
2908 SET status_code = 'EXCEPTION',
2909 message_code = 'VISIT-ASSIGN',
2910 object_version_number = object_version_number + 1,
2911 DATE_RUN = sysdate,
2912 LAST_UPDATE_DATE = sysdate,
2913 LAST_UPDATED_BY = fnd_global.user_id,
2914 LAST_UPDATE_LOGIN = fnd_global.login_id
2915 WHERE unit_effectivity_id IN (SELECT related_ue_id
2916 FROM ahl_ue_relationships
2917 WHERE originator_ue_id = l_delete_ue_id_tbl(i)
2918 AND relationship_code = 'PARENT');
2919
2920 -- Update originator UE ID.
2921 UPDATE AHL_UNIT_EFFECTIVITIES_B
2922 SET status_code = 'EXCEPTION',
2923 message_code = 'VISIT-ASSIGN',
2924 object_version_number = object_version_number + 1,
2925 DATE_RUN = sysdate,
2926 LAST_UPDATE_DATE = sysdate,
2927 LAST_UPDATED_BY = fnd_global.user_id,
2928 LAST_UPDATE_LOGIN = fnd_global.login_id
2929 WHERE unit_effectivity_id = l_delete_ue_id_tbl(i);
2930
2931 ELSIF (l_visit_status IS NULL) THEN
2932 -- delete ahl_ue_relationships
2933 FOR l_ue_rec IN ahl_ue_csr(l_delete_ue_id_tbl(i)) LOOP
2934
2935 -- delete unit effectivity record.
2936 AHL_UNIT_EFFECTIVITIES_PKG.Delete_Row(l_ue_rec.related_ue_id);
2937
2938 -- Delete the corresponding rows in ahl_schedule materials for this ue.
2939 Delete_Sch_Materials(l_ue_rec.related_ue_id);
2940
2941 END LOOP;
2942 DELETE FROM ahl_ue_relationships
2943 WHERE originator_ue_id = l_delete_ue_id_tbl(i);
2944
2945 -- delete top UE
2946 AHL_UNIT_EFFECTIVITIES_PKG.Delete_Row(l_delete_ue_id_tbl(i));
2947
2948 -- Delete the corresponding rows in ahl_schedule materials for this ue.
2949 Delete_Sch_Materials(l_delete_ue_id_tbl(i));
2950
2951 END IF; -- l_visit_status
2952 END LOOP;
2953 END IF;
2954
2955 IF G_DEBUG = 'Y' THEN
2956 AHL_DEBUG_PUB.Debug('End Unit Sr Deferrals');
2957 END IF;
2958
2959 END Flush_Unit_SR_Deferrals;
2960
2961
2962 -- Procedure to delete rows from ahl_schedule_materials.
2963 PROCEDURE Delete_Sch_Materials(p_unit_effectivity_id IN NUMBER) IS
2964
2965 CURSOR ahl_sch_material_csr (p_unit_effectivity_id IN NUMBER) IS
2966 SELECT scheduled_material_id
2967 FROM ahl_schedule_materials
2968 WHERE material_request_type = 'FORECAST'
2969 AND unit_effectivity_id = p_unit_effectivity_id
2970 FOR UPDATE NOWAIT;
2971
2972 BEGIN
2973
2974 IF G_DEBUG = 'Y' THEN
2975 AHL_DEBUG_PUB.Debug('Start Delete_Sch_Materials for UE:' || p_unit_effectivity_id,'UMP-ProcessUnit');
2976 END IF;
2977
2978 FOR sch_material_rec IN ahl_sch_material_csr(p_unit_effectivity_id) LOOP
2979 AHL_SCHEDULE_MATERIALS_PKG.delete_row(x_scheduled_material_id => sch_material_rec.scheduled_material_id);
2980
2981 END LOOP;
2982
2983 IF G_DEBUG = 'Y' THEN
2984 AHL_DEBUG_PUB.Debug('End Delete_Sch_Materials');
2985 END IF;
2986
2987 END Delete_Sch_Materials;
2988 ------------------------------------------------------------
2989
2990 PROCEDURE Flush_Loop_Effectivities
2991 IS
2992
2993 CURSOR get_loop_ue_csr (p_mr_title IN VARCHAR2,
2994 p_csi_ii_id IN NUMBER)
2995 IS
2996 SELECT tmp_ue.unit_effectivity_id,
2997 tmp_ue.csi_item_instance_id,
2998 tmp_ue.MR_header_id,
2999 tmp_ue.due_date,
3000 tmp_ue.mr_interval_id,
3001 tmp_ue.mr_effectivity_id,
3002 tmp_ue.due_counter_value,
3003 tmp_ue.forecast_sequence,
3004 tmp_ue.repetitive_mr_flag,
3005 tmp_ue.tolerance_flag,
3006 tmp_ue.message_code,
3007 tmp_ue.earliest_due_date,
3008 tmp_ue.latest_due_date,
3009 tmp_ue.counter_id,
3010 tmp_ue.accomplish_trigger_type,
3011 tmp_ue.loop_chain_seq_num,
3012 tmp_ue.start_mr_header_id,
3013 tmp_ue.start_lc_ue_id,
3014 tmp_ue.rowid,
3015 (select title from ahl_mr_headers_b mr where mr_header_id = tmp_ue.MR_header_id) mr_title
3016 FROM ahl_temp_unit_effectivities tmp_ue, ahl_mr_headers_b mr
3017 WHERE tmp_ue.start_mr_header_id = mr.mr_header_id
3018 AND mr.title = p_mr_title
3019 AND tmp_ue.csi_item_instance_id = p_csi_ii_id
3020 AND tmp_ue.accomplish_trigger_type IN ('LOOP')
3021 ORDER BY csi_item_instance_id, start_mr_header_id, forecast_sequence, loop_chain_seq_num;
3022
3023 TYPE tempUeTab IS TABLE OF get_loop_ue_csr%ROWTYPE;
3024 l_temp_ue_records tempUeTab;
3025
3026
3027 -- get start MRs
3028 CURSOR get_loop_start_mrs_csr IS
3029 SELECT distinct mr.title, csi_item_instance_id
3030 FROM ahl_applicable_mrs appl, ahl_mr_headers_b mr
3031 where appl.accomplish_trigger_type = 'LOOP'
3032 and appl.start_mr_header_id = appl.mr_header_id
3033 and appl.mr_header_id = mr.mr_header_id;
3034
3035 -- get open child UEs for start_lc_ue_id
3036 CURSOR get_start_ue_csr (p_start_lc_ue_id IN NUMBER) IS
3037 SELECT ue.unit_effectivity_Id, ue.loop_chain_seq_num, mr.title, 'N' match_flag
3038 FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b mr
3039 WHERE ue.mr_header_id = mr.mr_header_id
3040 AND ue.start_lc_ue_id = p_start_lc_ue_id
3041 AND ue.accomplish_trigger_type = 'LOOP'
3042 AND (ue.status_code IS NULL OR ue.status_code = 'INIT-DUE')
3043 AND loop_chain_seq_num > 1
3044 AND ue.defer_from_ue_id IS NULL
3045 ORDER BY loop_chain_seq_num;
3046
3047 CURSOR get_only_ue_csr (p_mr_title IN VARCHAR2,
3048 p_csi_ii_id IN NUMBER)
3049
3050 IS
3051 SELECT ue.unit_effectivity_id
3052 FROM ahl_temp_unit_effectivities ue, ahl_mr_headers_b mr
3053 WHERE ue.start_mr_header_id = mr.mr_header_id
3054 AND mr.title = p_mr_title
3055 AND ue.csi_item_instance_id = p_csi_ii_id
3056 AND ue.accomplish_trigger_type IN ('LOOP')
3057 --AND (ue.status_code IS NULL OR ue.status_code = 'INIT-DUE')
3058 ORDER BY csi_item_instance_id, due_date, forecast_sequence, loop_chain_seq_num;
3059
3060
3061 TYPE unitEffectivityTab IS TABLE OF ahl_unit_effectivities_b%ROWTYPE;
3062 l_upd_UE_tbl_records unitEffectivityTab;
3063
3064 l_temp_mr_rec ahl_temp_unit_effectivities%ROWTYPE;
3065
3066 l_upd_ue_cnt NUMBER;
3067 l_add_ue_cnt NUMBER;
3068
3069 l_start_mr_tbl dbms_sql.varchar2_table;
3070 l_csi_ii_tbl dbms_sql.number_table;
3071
3072 l_old_start_lc_ue_tbl dbms_sql.number_table;
3073 l_old_loop_seq_tbl dbms_sql.number_table;
3074 l_old_mr_title dbms_sql.varchar2_table;
3075 l_match_tbl dbms_sql.varchar2_table;
3076
3077 --l_start_lc_ue_tbl nbr_tbl_type;
3078
3079 l_old_start_lc_ue_seq NUMBER;
3080 l_old_start_lc_ue_id NUMBER;
3081
3082 l_min_due_date DATE;
3083 l_min_ue_id NUMBER;
3084
3085 l_ue_id_tbl dbms_sql.number_table;
3086 l_ue_id1_tbl dbms_sql.number_table;
3087
3088 type rowidArray IS TABLE OF rowid index by binary_integer;
3089 l_rowid rowidArray;
3090 l_index number;
3091 l_loop_1_start_lc_ue_id number;
3092
3093
3094 BEGIN
3095 l_upd_ue_cnt := 0;
3096 l_add_ue_cnt := 0;
3097
3098
3099 OPEN get_loop_start_mrs_csr;
3100 FETCH get_loop_start_mrs_csr BULK COLLECT INTO l_start_mr_tbl, l_csi_ii_tbl;
3101 CLOSE get_loop_start_mrs_csr;
3102
3103 IF (l_start_mr_tbl.count = 0) THEN
3104 RETURN;
3105 END IF;
3106
3107 FOR k IN l_start_mr_tbl.FIRST..l_start_mr_tbl.LAST LOOP
3108
3109 IF G_DEBUG = 'Y' THEN
3110 AHL_DEBUG_PUB.Debug('Starting Flush_Loop_Effectivities ...for:' || l_start_mr_tbl(k));
3111 END IF;
3112
3113 l_upd_ue_cnt := 0;
3114 l_add_ue_cnt := 0;
3115 l_upd_UE_tbl_records := unitEffectivityTab();
3116 l_old_start_lc_ue_seq := 0;
3117 l_old_start_lc_ue_tbl.delete;
3118
3119 OPEN get_loop_ue_csr(l_start_mr_tbl(k), l_csi_ii_tbl(k));
3120 LOOP
3121 FETCH get_loop_ue_csr BULK COLLECT INTO l_temp_ue_records LIMIT 1000;
3122 EXIT WHEN (l_temp_ue_records.count = 0);
3123
3124
3125 FOR i IN 1..l_temp_ue_records.count LOOP
3126
3127 -- find least due date and set repetitive_mr_flag between deferrals and temp due dates.
3128 IF (l_temp_ue_records(i).repetitive_mr_flag = 'N' AND l_temp_ue_records(i).due_date IS NOT NULL) THEN
3129 -- check if there is any deferral date less than due date.
3130 BEGIN
3131 SELECT due_date, unit_effectivity_id INTO l_min_due_date, l_min_ue_id
3132 FROM (
3133 SELECT def.due_date, def.unit_effectivity_id
3134 FROM ahl_temp_unit_SR_deferrals def, ahl_mr_headers_b mr1
3135 WHERE def.csi_item_instance_id = l_temp_ue_records(i).csi_item_instance_id
3136 AND def.mr_header_id = mr1.mr_header_id
3137 AND mr1.title = (select mr2.title from ahl_mr_headers_b mr2 where mr2.mr_header_id = l_temp_ue_records(i).MR_header_id)
3138 AND def.due_date IS NOT NULL
3139 AND def.deferral_effective_on IS NOT NULL
3140 AND def.orig_unit_effectivity_id IS NULL
3141 ORDER BY due_date asc )
3142 WHERE ROWNUM < 2;
3143
3144 -- deferral row exists.
3145 IF (l_min_due_date <= l_temp_ue_records(i).due_date) THEN
3146 l_temp_ue_records(i).repetitive_mr_flag := 'Y';
3147
3148 --- update deferral row (deferrals dates are processed before this procedure is called.
3149 UPDATE AHL_UNIT_EFFECTIVITIES_B
3150 SET repetitive_mr_flag = 'N',
3151 preceding_ue_id = null
3152 WHERE UNIT_EFFECTIVITY_ID = l_min_ue_id;
3153
3154 END IF;
3155
3156 EXCEPTION
3157 WHEN NO_DATA_FOUND THEN
3158 null;
3159 END;
3160 END IF;
3161
3162 --- assign unit effectivity Id for loop set children if start_lc_ue_id is assigned.
3163 IF (l_temp_ue_records(i).start_lc_ue_id IS NOT NULL) THEN
3164
3165 IF (l_temp_ue_records(i).loop_chain_seq_num > 1 AND l_temp_ue_records(i).UNIT_EFFECTIVITY_ID IS NULL) THEN
3166 IF (l_old_start_lc_ue_id IS NULL OR l_old_start_lc_ue_id <> l_temp_ue_records(i).start_lc_ue_id) THEN
3167 -- refresh UE_ID table
3168 OPEN get_start_ue_csr(l_temp_ue_records(i).start_lc_ue_id);
3169 FETCH get_start_ue_csr BULK COLLECT INTO l_old_start_lc_ue_tbl, l_old_loop_seq_tbl, l_old_mr_title, l_match_tbl;
3170 CLOSE get_start_ue_csr;
3171
3172 l_old_start_lc_ue_seq := 0;
3173 l_old_start_lc_ue_id := l_temp_ue_records(i).start_lc_ue_id;
3174
3175 END IF;
3176
3177 -- match next UE if exists.
3178 IF (l_old_start_lc_ue_tbl.EXISTS(l_old_start_lc_ue_seq+1)) THEN
3179 -- match if the UE details match to temp UE row.
3180 IF (l_temp_ue_records(i).loop_chain_seq_num = l_old_loop_seq_tbl(l_old_start_lc_ue_seq+1)
3181 AND l_old_mr_title(l_old_start_lc_ue_seq+1) = l_temp_ue_records(i).mr_title
3182 AND l_match_tbl(l_old_start_lc_ue_seq+1) = 'N' )
3183 THEN
3184 l_old_start_lc_ue_seq := l_old_start_lc_ue_seq + 1;
3185 l_match_tbl(l_old_start_lc_ue_seq) := 'Y';
3186
3187 l_temp_ue_records(i).UNIT_EFFECTIVITY_ID := l_old_start_lc_ue_tbl(l_old_start_lc_ue_seq);
3188
3189 UPDATE ahl_temp_unit_effectivities
3190 SET UNIT_EFFECTIVITY_ID = l_temp_ue_records(i).UNIT_EFFECTIVITY_ID
3191 WHERE rowid = l_temp_ue_records(i).rowid;
3192 END IF;
3193
3194 END IF;
3195 ELSIF (l_temp_ue_records(i).loop_chain_seq_num = 1) THEN
3196 IF (l_old_start_lc_ue_id IS NULL OR l_old_start_lc_ue_id <> l_temp_ue_records(i).start_lc_ue_id) THEN
3197 -- refresh UE_ID table
3198 OPEN get_start_ue_csr(l_temp_ue_records(i).start_lc_ue_id);
3199 FETCH get_start_ue_csr BULK COLLECT INTO l_old_start_lc_ue_tbl, l_old_loop_seq_tbl, l_old_mr_title, l_match_tbl;
3200 CLOSE get_start_ue_csr;
3201 l_old_start_lc_ue_seq := 0;
3202 l_old_start_lc_ue_id := l_temp_ue_records(i).start_lc_ue_id;
3203 END IF;
3204
3205 END IF; -- l_temp_ue_records(i).loop_chain_seq_num > 1
3206 END IF; -- l_temp_ue_records(i).start_lc_ue_id IS NOT NULL
3207
3208 -- depending on UE assignment, either create or update UE.
3209 IF (l_temp_ue_records(i).UNIT_EFFECTIVITY_ID IS NOT NULL) THEN
3210
3211 l_upd_ue_cnt := l_upd_ue_cnt + 1;
3212 l_upd_UE_tbl_records.extend(1);
3213
3214 l_upd_UE_tbl_records(l_upd_ue_cnt).UNIT_EFFECTIVITY_ID := l_temp_ue_records(i).UNIT_EFFECTIVITY_ID;
3215 l_upd_UE_tbl_records(l_upd_ue_cnt).csi_item_instance_id := l_temp_ue_records(i).csi_item_instance_id;
3216 l_upd_UE_tbl_records(l_upd_ue_cnt).MR_HEADER_ID := l_temp_ue_records(i).MR_header_id;
3217 l_upd_UE_tbl_records(l_upd_ue_cnt).due_date := l_temp_ue_records(i).due_date;
3218 l_upd_UE_tbl_records(l_upd_ue_cnt).mr_interval_id := l_temp_ue_records(i).mr_interval_id;
3219 l_upd_UE_tbl_records(l_upd_ue_cnt).mr_effectivity_id := l_temp_ue_records(i).mr_effectivity_id;
3220 l_upd_UE_tbl_records(l_upd_ue_cnt).due_counter_value := l_temp_ue_records(i).due_counter_value;
3221 l_upd_UE_tbl_records(l_upd_ue_cnt).forecast_sequence := l_temp_ue_records(i).forecast_sequence;
3222 l_upd_UE_tbl_records(l_upd_ue_cnt).repetitive_mr_flag := l_temp_ue_records(i).repetitive_mr_flag;
3223 l_upd_UE_tbl_records(l_upd_ue_cnt).tolerance_flag := l_temp_ue_records(i).tolerance_flag;
3224 l_upd_UE_tbl_records(l_upd_ue_cnt).message_code := l_temp_ue_records(i).message_code;
3225 l_upd_UE_tbl_records(l_upd_ue_cnt).earliest_due_date := l_temp_ue_records(i).earliest_due_date;
3226 l_upd_UE_tbl_records(l_upd_ue_cnt).latest_due_date:= l_temp_ue_records(i).latest_due_date;
3227 l_upd_UE_tbl_records(l_upd_ue_cnt).counter_id:= l_temp_ue_records(i).counter_id;
3228 l_upd_UE_tbl_records(l_upd_ue_cnt).accomplish_trigger_type := l_temp_ue_records(i).accomplish_trigger_type;
3229 l_upd_UE_tbl_records(l_upd_ue_cnt).loop_chain_seq_num := l_temp_ue_records(i).loop_chain_seq_num;
3230 l_upd_UE_tbl_records(l_upd_ue_cnt).start_lc_ue_id := l_temp_ue_records(i).start_lc_ue_id;
3231
3232
3233 -- Reset start_lc_ue_id for loop seq 1.
3234 -- forecast sequence is same for one loop set.
3235 IF (l_temp_ue_records(i).loop_chain_seq_num = 1) THEN
3236 --l_start_lc_ue_tbl(l_temp_ue_records(i).forecast_sequence) := l_temp_ue_records(i).unit_effectivity_id;
3237 l_loop_1_start_lc_ue_id := l_temp_ue_records(i).unit_effectivity_id;
3238 END IF;
3239
3240 ELSE
3241
3242 -- convert cursor rowtype to table rowtype.
3243 l_temp_mr_rec.UNIT_EFFECTIVITY_ID := l_temp_ue_records(i).UNIT_EFFECTIVITY_ID;
3244 l_temp_mr_rec.csi_item_instance_id := l_temp_ue_records(i).csi_item_instance_id;
3245 l_temp_mr_rec.MR_HEADER_ID := l_temp_ue_records(i).MR_header_id;
3246 l_temp_mr_rec.due_date := l_temp_ue_records(i).due_date;
3247 l_temp_mr_rec.mr_interval_id := l_temp_ue_records(i).mr_interval_id;
3248 l_temp_mr_rec.mr_effectivity_id := l_temp_ue_records(i).mr_effectivity_id;
3249 l_temp_mr_rec.due_counter_value := l_temp_ue_records(i).due_counter_value;
3250 l_temp_mr_rec.forecast_sequence := l_temp_ue_records(i).forecast_sequence;
3251 l_temp_mr_rec.repetitive_mr_flag := l_temp_ue_records(i).repetitive_mr_flag;
3252 l_temp_mr_rec.tolerance_flag := l_temp_ue_records(i).tolerance_flag;
3253 l_temp_mr_rec.message_code := l_temp_ue_records(i).message_code;
3254 l_temp_mr_rec.earliest_due_date := l_temp_ue_records(i).earliest_due_date;
3255 l_temp_mr_rec.latest_due_date:= l_temp_ue_records(i).latest_due_date;
3256 l_temp_mr_rec.counter_id:= l_temp_ue_records(i).counter_id;
3257 l_temp_mr_rec.accomplish_trigger_type := l_temp_ue_records(i).accomplish_trigger_type;
3258 l_temp_mr_rec.loop_chain_seq_num := l_temp_ue_records(i).loop_chain_seq_num;
3259
3260 Create_Record(l_temp_mr_rec);
3261
3262 -- update unit effectivity ID.
3263 UPDATE ahl_temp_unit_effectivities
3264 SET unit_effectivity_id = l_temp_mr_rec.unit_effectivity_id
3265 WHERE rowid = l_temp_ue_records(i).rowid;
3266
3267 -- update start_ic_ue_id based on the generated unit_effectivity_id
3268 -- forecast sequence is same for one loop set.
3269 IF (l_temp_mr_rec.loop_chain_seq_num = 1) THEN
3270 l_loop_1_start_lc_ue_id := l_temp_mr_rec.unit_effectivity_id;
3271 l_temp_ue_records(i).start_lc_ue_id := l_temp_mr_rec.unit_effectivity_id;
3272
3273 ELSIF (l_temp_mr_rec.loop_chain_seq_num > 1) THEN
3274 -- when MR is revised, we want to retain the carry forward ID.
3275 IF (l_temp_ue_records(i).start_lc_ue_id IS NULL) THEN
3276 l_temp_ue_records(i).start_lc_ue_id := l_loop_1_start_lc_ue_id;
3277 END IF;
3278 END IF;
3279
3280 UPDATE ahl_unit_effectivities_b
3281 SET start_lc_ue_id = l_temp_ue_records(i).start_lc_ue_id
3282 WHERE unit_effectivity_id = l_temp_mr_rec.unit_effectivity_id;
3283
3284 END IF;
3285 -- set UE associated to non-repetitive MR occurrence (will be used for setting preceding_ue_id later).
3286 IF (l_temp_ue_records(i).repetitive_mr_flag = 'N') THEN
3287 l_min_ue_id := l_temp_ue_records(i).unit_effectivity_id;
3288 END IF;
3289
3290 END LOOP; -- for i ..
3291
3292 -- update
3293 FORALL i IN 1..l_upd_UE_tbl_records.count
3294 UPDATE AHL_UNIT_EFFECTIVITIES_B
3295 SET
3296 due_date = l_upd_UE_tbl_records(i).due_date,
3297 mr_interval_id = l_upd_UE_tbl_records(i).mr_interval_id,
3298 mr_effectivity_id = l_upd_UE_tbl_records(i).mr_effectivity_id,
3299 due_counter_value = l_upd_UE_tbl_records(i).due_counter_value,
3300 forecast_sequence = l_upd_UE_tbl_records(i).forecast_sequence,
3301 repetitive_mr_flag = l_upd_UE_tbl_records(i).repetitive_mr_flag,
3302 tolerance_flag = l_upd_UE_tbl_records(i).tolerance_flag,
3303 message_code = l_upd_UE_tbl_records(i).message_code,
3304 earliest_due_date = l_upd_UE_tbl_records(i).earliest_due_date,
3305 latest_due_date = l_upd_UE_tbl_records(i).latest_due_date,
3306 counter_id = l_upd_UE_tbl_records(i).counter_id,
3307 accomplish_trigger_type = l_upd_UE_tbl_records(i).accomplish_trigger_type,
3308 loop_chain_seq_num = l_upd_UE_tbl_records(i).loop_chain_seq_num,
3309 start_lc_ue_id = l_upd_UE_tbl_records(i).start_lc_ue_id,
3310 date_run = sysdate,
3311 last_update_date = sysdate,
3312 last_updated_by = fnd_global.user_id,
3313 last_update_login = fnd_global.login_id,
3314 object_version_number = OBJECT_VERSION_NUMBER + 1
3315 WHERE
3316 unit_effectivity_id = l_upd_UE_tbl_records(i).UNIT_EFFECTIVITY_ID;
3317
3318
3319 l_upd_UE_tbl_records.delete;
3320 l_temp_ue_records.delete;
3321
3322 END LOOP; -- next fetch for start_mr
3323 CLOSE get_loop_ue_csr;
3324
3325 -- all UEs for mr title and instance are now processed.
3326 -- update preceding_ue_id
3327
3328 OPEN get_only_ue_csr(l_start_mr_tbl(k), l_csi_ii_tbl(k));
3329 FETCH get_only_ue_csr BULK COLLECT INTO l_ue_id_tbl;
3330 CLOSE get_only_ue_csr;
3331
3332 IF (l_ue_id_tbl.count > 0) THEN
3333 l_index := l_ue_id_tbl.FIRST;
3334
3335 IF (l_ue_id_tbl(l_index) = l_min_ue_id) THEN
3336 UPDATE AHL_UNIT_EFFECTIVITIES_B
3337 SET preceding_ue_id = null
3338 where unit_effectivity_id = l_ue_id_tbl(l_index);
3339 ELSE
3340 -- deferral exists
3341 UPDATE AHL_UNIT_EFFECTIVITIES_B
3342 SET preceding_ue_id = null
3343 where unit_effectivity_id = l_ue_id_tbl(l_index);
3344 END IF;
3345
3346 l_index := l_index + 1;
3347
3348 FOR i IN l_index..l_ue_id_tbl.count LOOP
3349 UPDATE AHL_UNIT_EFFECTIVITIES_B
3350 SET preceding_ue_id = l_ue_id_tbl(i-1)
3351 WHERE unit_effectivity_id = l_ue_id_tbl(i);
3352 END LOOP;
3353 END IF;
3354
3355 -- reset variables.
3356 --l_start_lc_ue_tbl.delete;
3357 l_min_ue_id := null;
3358
3359 END LOOP; -- next start mr.(k loop)
3360
3361 IF G_DEBUG = 'Y' THEN
3362 AHL_DEBUG_PUB.Debug('End Flush_Loop_Effectivities');
3363 END IF;
3364
3365 END Flush_Loop_Effectivities;
3366 -------------------------------------
3367
3368 PROCEDURE Flush_Chain_Effectivities
3369 IS
3370
3371 CURSOR get_loop_ue_csr (p_mr_title IN VARCHAR2,
3372 p_csi_ii_id IN NUMBER)
3373 IS
3374 SELECT tmp_ue.unit_effectivity_id,
3375 tmp_ue.csi_item_instance_id,
3376 tmp_ue.MR_header_id,
3377 tmp_ue.due_date,
3378 tmp_ue.mr_interval_id,
3379 tmp_ue.mr_effectivity_id,
3380 tmp_ue.due_counter_value,
3381 tmp_ue.forecast_sequence,
3382 tmp_ue.repetitive_mr_flag,
3383 tmp_ue.tolerance_flag,
3384 tmp_ue.message_code,
3385 tmp_ue.earliest_due_date,
3386 tmp_ue.latest_due_date,
3387 tmp_ue.counter_id,
3388 tmp_ue.accomplish_trigger_type,
3389 tmp_ue.loop_chain_seq_num,
3390 tmp_ue.start_mr_header_id,
3391 tmp_ue.start_lc_ue_id,
3392 tmp_ue.rowid,
3393 (select title from ahl_mr_headers_b mr where mr_header_id = tmp_ue.MR_header_id) mr_title
3394 FROM ahl_temp_unit_effectivities tmp_ue, ahl_mr_headers_b mr
3395 WHERE tmp_ue.start_mr_header_id = mr.mr_header_id
3396 AND mr.title = p_mr_title
3397 AND tmp_ue.csi_item_instance_id = p_csi_ii_id
3398 AND tmp_ue.accomplish_trigger_type IN ('CHAIN')
3399 ORDER BY csi_item_instance_id, start_mr_header_id, forecast_sequence, loop_chain_seq_num;
3400
3401 TYPE tempUeTab IS TABLE OF get_loop_ue_csr%ROWTYPE;
3402 l_temp_ue_records tempUeTab;
3403
3404
3405 -- get start MRs
3406 CURSOR get_loop_start_mrs_csr IS
3407 SELECT distinct mr.title, csi_item_instance_id
3408 FROM ahl_applicable_mrs appl, ahl_mr_headers_b mr
3409 where appl.accomplish_trigger_type = 'CHAIN'
3410 and appl.start_mr_header_id = appl.mr_header_id
3411 and appl.mr_header_id = mr.mr_header_id;
3412
3413
3414 -- get open child UEs for start_lc_ue_id
3415 CURSOR get_start_ue_csr (p_start_lc_ue_id IN NUMBER) IS
3416 SELECT ue.unit_effectivity_Id, ue.loop_chain_seq_num, mr.title, 'N' match_flag
3417 FROM ahl_unit_effectivities_b ue, ahl_mr_headers_b mr
3418 WHERE ue.mr_header_id = mr.mr_header_id
3419 AND ue.start_lc_ue_id = p_start_lc_ue_id
3420 AND ue.accomplish_trigger_type = 'CHAIN'
3421 AND (ue.status_code IS NULL OR ue.status_code = 'INIT-DUE')
3422 AND loop_chain_seq_num > 1
3423 AND ue.defer_from_ue_id IS NULL
3424 ORDER BY loop_chain_seq_num;
3425
3426
3427 CURSOR get_only_ue_csr (p_mr_title IN VARCHAR2,
3428 p_csi_ii_id IN NUMBER,
3429 p_start_lc_ue_id in number)
3430
3431 IS
3432 /*
3433 SELECT ue.unit_effectivity_id
3434 FROM ahl_temp_unit_effectivities ue, ahl_mr_headers_b mr
3435 WHERE ue.start_mr_header_id = mr.mr_header_id
3436 AND mr.title = p_mr_title
3437 AND ue.csi_item_instance_id = p_csi_ii_id
3438 AND ue.start_lc_ue_id = p_start_lc_ue_id
3439 AND ue.accomplish_trigger_type IN ('CHAIN')
3440 --AND (ue.status_code IS NULL OR ue.status_code = 'INIT-DUE')
3441 ORDER BY csi_item_instance_id, due_date, forecast_sequence, loop_chain_seq_num;
3442 */
3443
3444 SELECT ue.unit_effectivity_id
3445 FROM ahl_unit_effectivities_b ue
3446 WHERE ue.start_lc_ue_id = p_start_lc_ue_id
3447 AND ue.accomplish_trigger_type IN ('CHAIN')
3448 AND (ue.status_code IS NULL OR ue.status_code = 'INIT-DUE')
3449 ORDER BY loop_chain_seq_num;
3450
3451 CURSOR get_start_ue_id (p_mr_title IN VARCHAR2,
3452 p_csi_ii_id IN NUMBER) IS
3453 select distinct start_lc_ue_id
3454 from ahl_temp_unit_effectivities ue,ahl_mr_headers_b mr
3455 WHERE ue.start_mr_header_id = mr.mr_header_id
3456 AND mr.title = p_mr_title
3457 AND ue.csi_item_instance_id = p_csi_ii_id
3458 AND ue.accomplish_trigger_type IN ('CHAIN');
3459
3460
3461 TYPE unitEffectivityTab IS TABLE OF ahl_unit_effectivities_b%ROWTYPE;
3462 l_upd_UE_tbl_records unitEffectivityTab;
3463
3464 l_temp_mr_rec ahl_temp_unit_effectivities%ROWTYPE;
3465
3466 l_upd_ue_cnt NUMBER;
3467 l_add_ue_cnt NUMBER;
3468
3469 l_start_mr_tbl dbms_sql.varchar2_table;
3470 l_csi_ii_tbl dbms_sql.number_table;
3471
3472 l_old_start_lc_ue_tbl dbms_sql.number_table;
3473 l_old_loop_seq_tbl dbms_sql.number_table;
3474 l_old_mr_title dbms_sql.varchar2_table;
3475 l_match_tbl dbms_sql.varchar2_table;
3476
3477 --l_start_lc_ue_tbl nbr_tbl_type;
3478
3479 l_old_start_lc_ue_seq NUMBER;
3480 l_old_start_lc_ue_id NUMBER;
3481
3482 l_min_due_date DATE;
3483 l_min_ue_id NUMBER;
3484
3485 l_ue_id_tbl dbms_sql.number_table;
3486 l_ue_id1_tbl dbms_sql.number_table;
3487
3488 type rowidArray IS TABLE OF rowid index by binary_integer;
3489 l_rowid rowidArray;
3490 l_index number;
3491 l_loop_1_start_lc_ue_id number;
3492 l_chain_start_ue_id number;
3493 l_get_start_ue_tbl dbms_sql.number_table;
3494
3495 BEGIN
3496 l_upd_ue_cnt := 0;
3497 l_add_ue_cnt := 0;
3498
3499
3500 OPEN get_loop_start_mrs_csr;
3501 FETCH get_loop_start_mrs_csr BULK COLLECT INTO l_start_mr_tbl, l_csi_ii_tbl;
3502 CLOSE get_loop_start_mrs_csr;
3503
3504 IF (l_start_mr_tbl.count = 0) THEN
3505 RETURN;
3506 END IF;
3507
3508 FOR k IN l_start_mr_tbl.FIRST..l_start_mr_tbl.LAST LOOP
3509
3510 IF G_DEBUG = 'Y' THEN
3511 AHL_DEBUG_PUB.Debug('Starting Flush_Chain_Effectivities ...for:' || l_start_mr_tbl(k));
3512 END IF;
3513
3514 l_upd_ue_cnt := 0;
3515 l_add_ue_cnt := 0;
3516 l_upd_UE_tbl_records := unitEffectivityTab();
3517 l_old_start_lc_ue_seq := 0;
3518 l_old_start_lc_ue_tbl.delete;
3519
3520 OPEN get_loop_ue_csr(l_start_mr_tbl(k), l_csi_ii_tbl(k));
3521 LOOP
3522 FETCH get_loop_ue_csr BULK COLLECT INTO l_temp_ue_records LIMIT 1000;
3523 EXIT WHEN (l_temp_ue_records.count = 0);
3524
3525
3526 FOR i IN 1..l_temp_ue_records.count LOOP
3527
3528 -- find least due date and set repetitive_mr_flag between deferrals and temp due dates.
3529 IF (l_temp_ue_records(i).repetitive_mr_flag = 'N' AND l_temp_ue_records(i).due_date IS NOT NULL) THEN
3530 -- check if there is any deferral date less than due date.
3531 BEGIN
3532 SELECT due_date, unit_effectivity_id INTO l_min_due_date, l_min_ue_id
3533 FROM (
3534 SELECT def.due_date, def.unit_effectivity_id
3535 FROM ahl_temp_unit_SR_deferrals def, ahl_mr_headers_b mr1
3536 WHERE def.csi_item_instance_id = l_temp_ue_records(i).csi_item_instance_id
3537 AND def.mr_header_id = mr1.mr_header_id
3538 AND mr1.title = (select mr2.title from ahl_mr_headers_b mr2 where mr2.mr_header_id = l_temp_ue_records(i).MR_header_id)
3539 AND def.due_date IS NOT NULL
3540 AND def.deferral_effective_on IS NOT NULL
3541 AND def.orig_unit_effectivity_id IS NULL
3542 ORDER BY due_date asc )
3543 WHERE ROWNUM < 2;
3544
3545 -- deferral row exists.
3546 IF (l_min_due_date <= l_temp_ue_records(i).due_date) THEN
3547 l_temp_ue_records(i).repetitive_mr_flag := 'Y';
3548
3549 --- update deferral row (deferrals dates are processed before this procedure is called.
3550 UPDATE AHL_UNIT_EFFECTIVITIES_B
3551 SET repetitive_mr_flag = 'N',
3552 preceding_ue_id = null
3553 WHERE UNIT_EFFECTIVITY_ID = l_min_ue_id;
3554
3555 END IF;
3556
3557 EXCEPTION
3558 WHEN NO_DATA_FOUND THEN
3559 null;
3560 END;
3561 END IF;
3562
3563 --- assign unit effectivity Id for loop set children if start_lc_ue_id is assigned.
3564 IF (l_temp_ue_records(i).start_lc_ue_id IS NOT NULL) THEN
3565
3566 IF (l_temp_ue_records(i).loop_chain_seq_num > 1 AND l_temp_ue_records(i).UNIT_EFFECTIVITY_ID IS NULL) THEN
3567 IF (l_old_start_lc_ue_id IS NULL OR l_old_start_lc_ue_id <> l_temp_ue_records(i).start_lc_ue_id) THEN
3568 -- refresh UE_ID table
3569 OPEN get_start_ue_csr(l_temp_ue_records(i).start_lc_ue_id);
3570 FETCH get_start_ue_csr BULK COLLECT INTO l_old_start_lc_ue_tbl, l_old_loop_seq_tbl, l_old_mr_title, l_match_tbl;
3571 CLOSE get_start_ue_csr;
3572
3573 l_old_start_lc_ue_seq := 0;
3574 l_old_start_lc_ue_id := l_temp_ue_records(i).start_lc_ue_id;
3575
3576 END IF;
3577
3578 -- match next UE if exists.
3579 IF (l_old_start_lc_ue_tbl.EXISTS(l_old_start_lc_ue_seq+1)) THEN
3580 -- match if the UE details match to temp UE row.
3581 IF (l_temp_ue_records(i).loop_chain_seq_num = l_old_loop_seq_tbl(l_old_start_lc_ue_seq+1)
3582 AND l_old_mr_title(l_old_start_lc_ue_seq+1) = l_temp_ue_records(i).mr_title
3583 AND l_match_tbl(l_old_start_lc_ue_seq+1) = 'N' )
3584 THEN
3585 l_old_start_lc_ue_seq := l_old_start_lc_ue_seq + 1;
3586 l_match_tbl(l_old_start_lc_ue_seq) := 'Y';
3587
3588 l_temp_ue_records(i).UNIT_EFFECTIVITY_ID := l_old_start_lc_ue_tbl(l_old_start_lc_ue_seq);
3589
3590 UPDATE ahl_temp_unit_effectivities
3591 SET UNIT_EFFECTIVITY_ID = l_temp_ue_records(i).UNIT_EFFECTIVITY_ID
3592 WHERE rowid = l_temp_ue_records(i).rowid;
3593 END IF;
3594
3595 END IF;
3596 ELSIF (l_temp_ue_records(i).loop_chain_seq_num = 1) THEN
3597 IF (l_old_start_lc_ue_id IS NULL OR l_old_start_lc_ue_id <> l_temp_ue_records(i).start_lc_ue_id) THEN
3598 -- refresh UE_ID table
3599 OPEN get_start_ue_csr(l_temp_ue_records(i).start_lc_ue_id);
3600 FETCH get_start_ue_csr BULK COLLECT INTO l_old_start_lc_ue_tbl, l_old_loop_seq_tbl, l_old_mr_title, l_match_tbl;
3601 CLOSE get_start_ue_csr;
3602 l_old_start_lc_ue_seq := 0;
3603 l_old_start_lc_ue_id := l_temp_ue_records(i).start_lc_ue_id;
3604 END IF;
3605
3606 END IF; -- l_temp_ue_records(i).loop_chain_seq_num > 1
3607 END IF; -- l_temp_ue_records(i).start_lc_ue_id IS NOT NULL
3608
3609 -- depending on UE assignment, either create or update UE.
3610 IF (l_temp_ue_records(i).UNIT_EFFECTIVITY_ID IS NOT NULL) THEN
3611
3612 l_upd_ue_cnt := l_upd_ue_cnt + 1;
3613 l_upd_UE_tbl_records.extend(1);
3614
3615 l_upd_UE_tbl_records(l_upd_ue_cnt).UNIT_EFFECTIVITY_ID := l_temp_ue_records(i).UNIT_EFFECTIVITY_ID;
3616 l_upd_UE_tbl_records(l_upd_ue_cnt).csi_item_instance_id := l_temp_ue_records(i).csi_item_instance_id;
3617 l_upd_UE_tbl_records(l_upd_ue_cnt).MR_HEADER_ID := l_temp_ue_records(i).MR_header_id;
3618 l_upd_UE_tbl_records(l_upd_ue_cnt).due_date := l_temp_ue_records(i).due_date;
3619 l_upd_UE_tbl_records(l_upd_ue_cnt).mr_interval_id := l_temp_ue_records(i).mr_interval_id;
3620 l_upd_UE_tbl_records(l_upd_ue_cnt).mr_effectivity_id := l_temp_ue_records(i).mr_effectivity_id;
3621 l_upd_UE_tbl_records(l_upd_ue_cnt).due_counter_value := l_temp_ue_records(i).due_counter_value;
3622 l_upd_UE_tbl_records(l_upd_ue_cnt).forecast_sequence := l_temp_ue_records(i).forecast_sequence;
3623 l_upd_UE_tbl_records(l_upd_ue_cnt).repetitive_mr_flag := l_temp_ue_records(i).repetitive_mr_flag;
3624 l_upd_UE_tbl_records(l_upd_ue_cnt).tolerance_flag := l_temp_ue_records(i).tolerance_flag;
3625 l_upd_UE_tbl_records(l_upd_ue_cnt).message_code := l_temp_ue_records(i).message_code;
3626 l_upd_UE_tbl_records(l_upd_ue_cnt).earliest_due_date := l_temp_ue_records(i).earliest_due_date;
3627 l_upd_UE_tbl_records(l_upd_ue_cnt).latest_due_date:= l_temp_ue_records(i).latest_due_date;
3628 l_upd_UE_tbl_records(l_upd_ue_cnt).counter_id:= l_temp_ue_records(i).counter_id;
3629 l_upd_UE_tbl_records(l_upd_ue_cnt).accomplish_trigger_type := l_temp_ue_records(i).accomplish_trigger_type;
3630 l_upd_UE_tbl_records(l_upd_ue_cnt).loop_chain_seq_num := l_temp_ue_records(i).loop_chain_seq_num;
3631 l_upd_UE_tbl_records(l_upd_ue_cnt).start_lc_ue_id := l_temp_ue_records(i).start_lc_ue_id;
3632
3633
3634 -- Reset start_lc_ue_id for loop seq 1.
3635 -- forecast sequence is same for one loop set.
3636 IF (l_temp_ue_records(i).loop_chain_seq_num = 1) THEN
3637 --l_start_lc_ue_tbl(l_temp_ue_records(i).forecast_sequence) := l_temp_ue_records(i).unit_effectivity_id;
3638 l_loop_1_start_lc_ue_id := l_temp_ue_records(i).unit_effectivity_id;
3639 END IF;
3640
3641 ELSE
3642
3643 -- convert cursor rowtype to table rowtype.
3644 l_temp_mr_rec.UNIT_EFFECTIVITY_ID := l_temp_ue_records(i).UNIT_EFFECTIVITY_ID;
3645 l_temp_mr_rec.csi_item_instance_id := l_temp_ue_records(i).csi_item_instance_id;
3646 l_temp_mr_rec.MR_HEADER_ID := l_temp_ue_records(i).MR_header_id;
3647 l_temp_mr_rec.due_date := l_temp_ue_records(i).due_date;
3648 l_temp_mr_rec.mr_interval_id := l_temp_ue_records(i).mr_interval_id;
3649 l_temp_mr_rec.mr_effectivity_id := l_temp_ue_records(i).mr_effectivity_id;
3650 l_temp_mr_rec.due_counter_value := l_temp_ue_records(i).due_counter_value;
3651 l_temp_mr_rec.forecast_sequence := l_temp_ue_records(i).forecast_sequence;
3652 l_temp_mr_rec.repetitive_mr_flag := l_temp_ue_records(i).repetitive_mr_flag;
3653 l_temp_mr_rec.tolerance_flag := l_temp_ue_records(i).tolerance_flag;
3654 l_temp_mr_rec.message_code := l_temp_ue_records(i).message_code;
3655 l_temp_mr_rec.earliest_due_date := l_temp_ue_records(i).earliest_due_date;
3656 l_temp_mr_rec.latest_due_date:= l_temp_ue_records(i).latest_due_date;
3657 l_temp_mr_rec.counter_id:= l_temp_ue_records(i).counter_id;
3658 l_temp_mr_rec.accomplish_trigger_type := l_temp_ue_records(i).accomplish_trigger_type;
3659 l_temp_mr_rec.loop_chain_seq_num := l_temp_ue_records(i).loop_chain_seq_num;
3660
3661 Create_Record(l_temp_mr_rec);
3662
3663 -- update unit effectivity ID.
3664 UPDATE ahl_temp_unit_effectivities
3665 SET unit_effectivity_id = l_temp_mr_rec.unit_effectivity_id
3666 WHERE rowid = l_temp_ue_records(i).rowid;
3667
3668 -- update start_ic_ue_id based on the generated unit_effectivity_id
3669 -- forecast sequence is same for one loop set.
3670 IF (l_temp_mr_rec.loop_chain_seq_num = 1) THEN
3671 l_loop_1_start_lc_ue_id := l_temp_mr_rec.unit_effectivity_id;
3672 l_temp_ue_records(i).start_lc_ue_id := l_temp_mr_rec.unit_effectivity_id;
3673
3674 ELSIF (l_temp_mr_rec.loop_chain_seq_num > 1) THEN
3675 -- when MR is revised, we want to retain the carry forward ID.
3676 IF (l_temp_ue_records(i).start_lc_ue_id IS NULL) THEN
3677 l_temp_ue_records(i).start_lc_ue_id := l_loop_1_start_lc_ue_id;
3678 END IF;
3679 END IF;
3680
3681 UPDATE ahl_unit_effectivities_b
3682 SET start_lc_ue_id = l_temp_ue_records(i).start_lc_ue_id
3683 WHERE unit_effectivity_id = l_temp_mr_rec.unit_effectivity_id;
3684
3685 END IF;
3686 -- set UE associated to non-repetitive MR occurrence (will be used for setting preceding_ue_id later).
3687 IF (l_temp_ue_records(i).repetitive_mr_flag = 'N') THEN
3688 l_min_ue_id := l_temp_ue_records(i).unit_effectivity_id;
3689 END IF;
3690
3691 END LOOP; -- for i ..
3692
3693 -- update
3694 FORALL i IN 1..l_upd_UE_tbl_records.count
3695 UPDATE AHL_UNIT_EFFECTIVITIES_B
3696 SET
3697 due_date = l_upd_UE_tbl_records(i).due_date,
3698 mr_interval_id = l_upd_UE_tbl_records(i).mr_interval_id,
3699 mr_effectivity_id = l_upd_UE_tbl_records(i).mr_effectivity_id,
3700 due_counter_value = l_upd_UE_tbl_records(i).due_counter_value,
3701 forecast_sequence = l_upd_UE_tbl_records(i).forecast_sequence,
3702 repetitive_mr_flag = l_upd_UE_tbl_records(i).repetitive_mr_flag,
3703 tolerance_flag = l_upd_UE_tbl_records(i).tolerance_flag,
3704 message_code = l_upd_UE_tbl_records(i).message_code,
3705 earliest_due_date = l_upd_UE_tbl_records(i).earliest_due_date,
3706 latest_due_date = l_upd_UE_tbl_records(i).latest_due_date,
3707 counter_id = l_upd_UE_tbl_records(i).counter_id,
3708 accomplish_trigger_type = l_upd_UE_tbl_records(i).accomplish_trigger_type,
3709 loop_chain_seq_num = l_upd_UE_tbl_records(i).loop_chain_seq_num,
3710 start_lc_ue_id = l_upd_UE_tbl_records(i).start_lc_ue_id,
3711 date_run = sysdate,
3712 last_update_date = sysdate,
3713 last_updated_by = fnd_global.user_id,
3714 last_update_login = fnd_global.login_id,
3715 object_version_number = OBJECT_VERSION_NUMBER + 1
3716 WHERE
3717 unit_effectivity_id = l_upd_UE_tbl_records(i).UNIT_EFFECTIVITY_ID;
3718
3719
3720 l_upd_UE_tbl_records.delete;
3721 l_temp_ue_records.delete;
3722
3723 END LOOP; -- next fetch for start_mr
3724 CLOSE get_loop_ue_csr;
3725
3726 open get_start_ue_id(l_start_mr_tbl(k), l_csi_ii_tbl(k));
3727 fetch get_start_ue_id bulk collect into l_get_start_ue_tbl;
3728 close get_start_ue_id;
3729
3730 if (l_get_start_ue_tbl.count) > 0 then
3731 for ll in l_get_start_ue_tbl.first..l_get_start_ue_tbl.count loop
3732 OPEN get_only_ue_csr(l_start_mr_tbl(k), l_csi_ii_tbl(k), l_get_start_ue_tbl(ll));
3733 FETCH get_only_ue_csr BULK COLLECT INTO l_ue_id_tbl;
3734 CLOSE get_only_ue_csr;
3735
3736 -- all UEs for mr title and instance are now processed.
3737 -- update preceding_ue_id
3738
3739
3740 IF (l_ue_id_tbl.count > 0) THEN
3741 l_index := l_ue_id_tbl.FIRST;
3742
3743 -- for chain, first UE to be updated with preceding_ue_id as null
3744 UPDATE AHL_UNIT_EFFECTIVITIES_B
3745 SET preceding_ue_id = null
3746 where unit_effectivity_id = l_ue_id_tbl(l_index);
3747 /*
3748 ELSE
3749 -- deferral exists
3750 UPDATE AHL_UNIT_EFFECTIVITIES_B
3751 SET preceding_ue_id = null
3752 where unit_effectivity_id = l_ue_id_tbl(l_index);
3753 END IF;
3754 */
3755 l_index := l_index + 1;
3756
3757 FOR i IN l_index..l_ue_id_tbl.count LOOP
3758 UPDATE AHL_UNIT_EFFECTIVITIES_B
3759 SET preceding_ue_id = l_ue_id_tbl(i-1)
3760 WHERE unit_effectivity_id = l_ue_id_tbl(i);
3761 END LOOP;
3762 END IF;
3763
3764 end loop;
3765
3766 end if;
3767
3768 -- reset variables.
3769 --l_start_lc_ue_tbl.delete;
3770 l_min_ue_id := null;
3771
3772 END LOOP; -- next start mr.(k loop)
3773
3774 IF G_DEBUG = 'Y' THEN
3775 AHL_DEBUG_PUB.Debug('End Flush_Chain_Effectivities');
3776 END IF;
3777
3778 END Flush_Chain_Effectivities;
3779
3780 -- JKJain, NR Analysis and Forecasting
3781 PROCEDURE Flush_To_Sim_Ue_Table(p_config_node_tbl IN AHL_UMP_PROCESSUNIT_PVT.config_node_tbl_type,
3782 p_simulation_plan_id IN NUMBER,
3783 p_uc_header_id IN NUMBER) IS
3784
3785 -- Select all the simulation UEs for the given sim plan.
3786 CURSOR get_sim_ue_ids_csr(c_simulation_plan_id NUMBER,c_uc_header_id NUMBER) IS
3787 SELECT SIMULATION_UE_ID
3788 FROM AHL_UE_SIMULATIONS
3789 WHERE SIMULATION_PLAN_ID = c_simulation_plan_id
3790 AND UNIT_CONFIG_HEADER_ID = c_uc_header_id;
3791
3792 -- get individual MRs for item instance and top group records from temporary table.
3793 CURSOR temp_individual_mrs_csr IS
3794 SELECT unit_effectivity_id,
3795 csi_item_instance_id,
3796 mr_header_id,
3797 due_date,
3798 mr_interval_id,
3799 mr_effectivity_id,
3800 due_counter_value,
3801 parent_csi_item_instance_id,
3802 parent_mr_header_id,
3803 orig_csi_item_instance_id,
3804 orig_mr_header_id,
3805 forecast_sequence,
3806 repetitive_mr_flag,
3807 tolerance_flag,
3808 message_code,
3809 earliest_due_date,
3810 latest_due_date,
3811 counter_id,
3812 fleet_header_id
3813 FROM ahl_temp_unit_effectivities
3814 WHERE mr_header_id = nvl(orig_mr_header_id, mr_header_id) AND
3815 csi_item_instance_id = nvl(orig_csi_item_instance_id,csi_item_instance_id)
3816 ORDER by forecast_sequence ASC
3817 FOR UPDATE OF unit_effectivity_id;
3818
3819 /*
3820 -- get individual Non-Routine UEs for item instance and top group records from temporary table.
3821 CURSOR temp_non_routine_ue_csr(c_csi_item_instance_id NUMBER) IS
3822 SELECT unit_effectivity_id,
3823 csi_item_instance_id,
3824 mr_header_id,
3825 due_date,
3826 status_code,
3827 mr_interval_id,
3828 mr_effectivity_id,
3829 due_counter_value,
3830 forecast_sequence,
3831 repetitive_mr_flag,
3832 tolerance_flag,
3833 message_code,
3834 earliest_due_date,
3835 latest_due_date,
3836 counter_id,
3837 fleet_header_id,
3838 cs_incident_id,
3839 manually_planned_flag,
3840 defer_from_ue_id,
3841 counter_id
3842 from ahl_unit_effectivities_b
3843 WHERE csi_item_instance_id = c_csi_item_instance_id and
3844 defer_from_ue_id is null and
3845 (status_code is null or status_code = 'INIT-DUE') and
3846 cs_incident_id IS NOT NULL and
3847 object_type = 'SR'
3848 ;
3849 */
3850 -- get individual Unplanned UEs and NRs for item instance and top group records from temporary table.
3851 CURSOR temp_nr_unplanned_ue_csr(c_csi_item_instance_id NUMBER) IS
3852 SELECT unit_effectivity_id
3853 FROM ahl_unit_effectivities_b
3854 WHERE csi_item_instance_id = c_csi_item_instance_id and
3855 defer_from_ue_id is null and
3856 ((status_code is null and manually_planned_flag = 'Y') OR status_code = 'INIT-DUE') and
3857 NOT EXISTS ( SELECT 'X'
3858 FROM ahl_ue_relationships
3859 where related_ue_id = unit_effectivity_id )
3860 ;
3861
3862
3863 -- get individual Deferred UEs for item instance and top group records from temporary table.
3864 CURSOR temp_deferred_ue_csr(c_csi_item_instance_id NUMBER) IS
3865 SELECT ue.unit_effectivity_id,
3866 temp.due_date,
3867 temp.tolerance_flag,
3868 temp.message_code,
3869 temp.counter_id
3870 from ahl_temp_unit_sr_deferrals temp, ahl_unit_effectivities_b ue
3871 WHERE ue.unit_effectivity_id = temp.unit_effectivity_id and
3872 temp.csi_item_instance_id = c_csi_item_instance_id and
3873 temp.orig_unit_effectivity_id IS NULL and
3874 temp.deferral_effective_on IS NOT NULL
3875 ORDER BY DEFERRAL_EFFECTIVE_ON ASC;
3876
3877 -- Get all child UE's for a given unit effectivity.
3878 CURSOR ahl_ue_relns_csr (c_unit_effectivity_id IN NUMBER) IS
3879 SELECT related_ue_id child_ue_id, ue_id parent_ue_id
3880 FROM ahl_ue_relationships
3881 START WITH ue_id = c_unit_effectivity_id
3882 AND relationship_code = 'PARENT'
3883 CONNECT BY PRIOR related_ue_id = ue_id
3884 AND relationship_code = 'PARENT'
3885 ORDER BY level;
3886 -- Get all child UE's for a given unit effectivity.
3887 CURSOR ahl_all_child_ue_csr (c_unit_effectivity_id IN NUMBER) IS
3888 SELECT related_ue_id
3889 FROM ahl_ue_relationships
3890 WHERE ORIGINATOR_UE_ID = c_unit_effectivity_id
3891 AND relationship_code = 'PARENT';
3892
3893
3894 l_user_id NUMBER := to_number(fnd_global.USER_ID);
3895 l_temp_sim_ue_rec ahl_ue_simulations%ROWTYPE;
3896 l_temp_individual_mr_rec temp_individual_mrs_csr%ROWTYPE;
3897 l_simulation_ue_id NUMBER;
3898 l_originator_ue_id NUMBER;
3899 l_unit_effectivity_id NUMBER;
3900 l_parent_ue_id NUMBER;
3901 l_index NUMBER;
3902 l_start_time DATE;
3903 l_fleet_header_id NUMBER;
3904
3905 l_deferral_ue_cols Deferral_UE_Cols_Type;
3906 l_ue_simue_id_table number_table_type;
3907
3908 BEGIN
3909
3910 IF G_DEBUG = 'Y' THEN
3911 AHL_DEBUG_PUB.Debug('Start Flush_To_Sim_Ue_Table Simulation = ' || p_simulation_plan_id || ' and Unit = '||p_uc_header_id,'Flush_To_Sim_Ue_Table');
3912 END IF;
3913 G_UC_HEADER_ID := p_uc_header_id;
3914 G_SIMULATION_PLAN_ID := p_simulation_plan_id;
3915 -- record start time.
3916 l_start_time := sysdate;
3917
3918 -- Bulk collect sim_ue_id from sim UE's for the given sim plan and unit combination.
3919 -- check for max limit
3920 OPEN get_sim_ue_ids_csr(G_SIMULATION_PLAN_ID,G_UC_HEADER_ID);
3921 FETCH get_sim_ue_ids_csr BULK COLLECT INTO G_SIM_UE_ID_TBL;
3922 CLOSE get_sim_ue_ids_csr;
3923 G_SIM_UE_ID_INDEX := 1;
3924
3925 --Delete all the sim UE's for the given sim plan unit combination.
3926 DELETE FROM AHL_UE_SIMULATIONS WHERE SIMULATION_PLAN_ID = G_SIMULATION_PLAN_ID AND UNIT_CONFIG_HEADER_ID = G_UC_HEADER_ID;
3927
3928 -- Read temporary table.
3929 FOR temp_individual_mr_rec IN temp_individual_mrs_csr LOOP
3930
3931 l_temp_individual_mr_rec := temp_individual_mr_rec;
3932 -- initialize the record will null values
3933 l_temp_sim_ue_rec := null;
3934 l_fleet_header_id := null;
3935
3936 IF(G_SIM_UE_ID_TBL.EXISTS(G_SIM_UE_ID_INDEX)) THEN
3937 l_temp_sim_ue_rec.simulation_ue_id := G_SIM_UE_ID_TBL(G_SIM_UE_ID_INDEX);
3938 G_SIM_UE_ID_INDEX := G_SIM_UE_ID_INDEX +1;
3939 ELSE
3940 l_temp_sim_ue_rec.simulation_ue_id := AHL_UE_SIMULATIONS_S.NEXtVAL;
3941 END IF;
3942
3943 l_temp_sim_ue_rec.simulation_plan_id := G_SIMULATION_PLAN_ID;
3944 l_temp_sim_ue_rec.unit_effectivity_id := null;
3945 l_temp_sim_ue_rec.csi_item_instance_id := l_temp_individual_mr_rec.csi_item_instance_id;
3946 IF(l_temp_individual_mr_rec.fleet_header_id IS NOT NULL) THEN
3947 l_temp_sim_ue_rec.fleet_header_id := l_temp_individual_mr_rec.fleet_header_id;
3948 ELSE
3949 l_fleet_header_id := AHL_UMP_ProcessUnit_PVT.get_fleet_from_unit_asso(G_UC_HEADER_ID,l_temp_individual_mr_rec.due_date,G_SIMULATION_PLAN_ID);
3950 IF(l_fleet_header_id IS NULL) THEN
3951 -- Check if Fleet Association exists with earliest due date.
3952 l_fleet_header_id := AHL_UMP_ProcessUnit_PVT.get_fleet_from_unit_asso(G_UC_HEADER_ID,l_temp_individual_mr_rec.earliest_due_date,G_SIMULATION_PLAN_ID);
3953 END IF;
3954 l_temp_sim_ue_rec.fleet_header_id := l_fleet_header_id;
3955 END IF;
3956 l_temp_sim_ue_rec.mr_header_id := l_temp_individual_mr_rec.mr_header_id;
3957 l_temp_sim_ue_rec.unit_config_header_id := G_UC_HEADER_ID;
3958 l_temp_sim_ue_rec.due_date := l_temp_individual_mr_rec.due_date;
3959 l_temp_sim_ue_rec.STATUS_CODE := null ;
3960 l_temp_sim_ue_rec.mr_interval_id := l_temp_individual_mr_rec.mr_interval_id;
3961 l_temp_sim_ue_rec.mr_effectivity_id := l_temp_individual_mr_rec.mr_effectivity_id;
3962 l_temp_sim_ue_rec.due_counter_value := l_temp_individual_mr_rec.due_counter_value;
3963 l_temp_sim_ue_rec.forecast_sequence := l_temp_individual_mr_rec.forecast_sequence;
3964 l_temp_sim_ue_rec.repetitive_mr_flag := l_temp_individual_mr_rec.repetitive_mr_flag;
3965 l_temp_sim_ue_rec.tolerance_flag := l_temp_individual_mr_rec.tolerance_flag;
3966 l_temp_sim_ue_rec.message_code := l_temp_individual_mr_rec.message_code;
3967 l_temp_sim_ue_rec.date_run := sysdate;
3968 l_temp_sim_ue_rec.preceding_ue_id := null;
3969 l_temp_sim_ue_rec.earliest_due_date := l_temp_individual_mr_rec.earliest_due_date;
3970 l_temp_sim_ue_rec.latest_due_date := l_temp_individual_mr_rec.latest_due_date;
3971 l_temp_sim_ue_rec.defer_from_ue_id := null;
3972 l_temp_sim_ue_rec.counter_id := l_temp_individual_mr_rec.counter_id;
3973 l_temp_sim_ue_rec.cs_incident_id := null;
3974 l_temp_sim_ue_rec.object_version_number := 1;
3975 l_temp_sim_ue_rec.creation_date := sysdate;
3976 l_temp_sim_ue_rec.last_update_date := sysdate;
3977 l_temp_sim_ue_rec.last_updated_by := l_user_id;
3978 l_temp_sim_ue_rec.created_by := l_user_id;
3979 l_temp_sim_ue_rec.last_update_login := fnd_global.login_id;
3980 l_temp_sim_ue_rec.security_group_id := NULL;
3981 l_temp_sim_ue_rec.originator_ue_id := null;
3982 l_temp_sim_ue_rec.parent_ue_id := null;
3983
3984 /*
3985 l_temp_sim_ue_rec.parent_csi_item_instance_id := l_temp_individual_mr_rec.parent_csi_item_instance_id;
3986 l_temp_sim_ue_rec.parent_mr_header_id := l_temp_individual_mr_rec.parent_mr_header_id;
3987 l_temp_sim_ue_rec.orig_csi_item_instance_id := l_temp_individual_mr_rec.orig_csi_item_instance_id;
3988 l_temp_sim_ue_rec.orig_mr_header_id := l_temp_individual_mr_rec.orig_mr_header_id;
3989 */
3990 -- Check if l_temp_individual_mr_rec is a group mr.
3991
3992 IF (l_temp_individual_mr_rec.orig_mr_header_id IS NOT NULL) THEN
3993
3994 l_temp_sim_ue_rec.originator_ue_id := l_temp_sim_ue_rec.simulation_ue_id;
3995 Create_group_for_sim_ue(p_x_temp_sim_ue_rec => l_temp_sim_ue_rec);
3996
3997 ELSE /* not a group */
3998 --Insert new record in sim ue table
3999 Create_sim_ue_record(p_x_temp_sim_ue_rec => l_temp_sim_ue_rec);
4000
4001 UPDATE ahl_temp_unit_effectivities
4002 SET unit_effectivity_id = l_temp_sim_ue_rec.SIMULATION_UE_ID
4003 WHERE CURRENT OF temp_individual_mrs_csr;
4004
4005 END IF;
4006
4007 END LOOP; /* for temp_individual_mrs_csr */
4008
4009 -- Iterate through p_config_node_tbl table and copy NR, Unplanned MR, Deferrals for each item instance.
4010 FOR i IN p_config_node_tbl.FIRST..p_config_node_tbl.LAST LOOP
4011
4012 -- copy nonroutines and unplanned MRs
4013 l_ue_simue_id_table.DELETE;
4014 FOR nr_unplanned_ue_rec IN temp_nr_unplanned_ue_csr(p_config_node_tbl(i).csi_item_instance_id) LOOP
4015
4016 --Insert new record in sim ue table
4017 create_simue_from_ue_record (p_unit_effectivity_id => nr_unplanned_ue_rec.unit_effectivity_id,
4018 p_originator_ue_id => null,
4019 p_parent_ue_id => null,
4020 p_deferral_ue_cols => null,
4021 x_simulation_ue_id => l_simulation_ue_id) ;
4022
4023 l_ue_simue_id_table(nr_unplanned_ue_rec.unit_effectivity_id) := l_simulation_ue_id;
4024 l_index := 0;
4025 l_originator_ue_id := l_ue_simue_id_table(nr_unplanned_ue_rec.unit_effectivity_id);
4026
4027 -- Check for group mr.
4028 FOR child_nr_up_ue_rec IN ahl_ue_relns_csr(nr_unplanned_ue_rec.unit_effectivity_id) LOOP
4029
4030 IF (l_index <1)THEN
4031 UPDATE AHL_UE_SIMULATIONS
4032 SET originator_ue_id = l_simulation_ue_id
4033 WHERE simulation_ue_id = l_simulation_ue_id;
4034 l_index := 1;
4035 END IF;
4036
4037 l_parent_ue_id := l_ue_simue_id_table(child_nr_up_ue_rec.parent_ue_id);
4038 l_unit_effectivity_id := child_nr_up_ue_rec.child_ue_id;
4039 create_simue_from_ue_record (p_unit_effectivity_id =>l_unit_effectivity_id,
4040 p_originator_ue_id => l_originator_ue_id,
4041 p_parent_ue_id => l_parent_ue_id,
4042 p_deferral_ue_cols => null,
4043 x_simulation_ue_id => l_simulation_ue_id) ;
4044
4045 l_ue_simue_id_table(l_unit_effectivity_id) := l_simulation_ue_id;
4046 END LOOP;
4047
4048 END LOOP; -- non-routines unplanned MRs
4049
4050
4051
4052
4053 -- copy deferred UEs
4054 l_ue_simue_id_table.DELETE;
4055
4056 FOR deferred_ue_rec IN temp_deferred_ue_csr(p_config_node_tbl(i).csi_item_instance_id) LOOP
4057 l_deferral_ue_cols := null;
4058 --Insert new record in sim ue table
4059 l_deferral_ue_cols.unit_effectivity_id := deferred_ue_rec.unit_effectivity_id;
4060 l_deferral_ue_cols.due_date := deferred_ue_rec.due_date;
4061 l_deferral_ue_cols.tolerance_flag := deferred_ue_rec.tolerance_flag;
4062 l_deferral_ue_cols.message_code := deferred_ue_rec.message_code;
4063 l_deferral_ue_cols.counter_id := deferred_ue_rec.counter_id;
4064 create_simue_from_ue_record (p_unit_effectivity_id => deferred_ue_rec.unit_effectivity_id,
4065 p_originator_ue_id => null,
4066 p_parent_ue_id => null,
4067 p_deferral_ue_cols => l_deferral_ue_cols,
4068 x_simulation_ue_id => l_simulation_ue_id) ;
4069 l_ue_simue_id_table(deferred_ue_rec.unit_effectivity_id) := l_simulation_ue_id;
4070 l_originator_ue_id := l_ue_simue_id_table(deferred_ue_rec.unit_effectivity_id);
4071 l_index := 0;
4072 -- Check for group mr.
4073 FOR child_deferral_ue_rec IN ahl_ue_relns_csr(deferred_ue_rec.unit_effectivity_id) LOOP
4074 IF (l_index <1)THEN
4075 UPDATE AHL_UE_SIMULATIONS
4076 SET originator_ue_id = l_simulation_ue_id
4077 WHERE simulation_ue_id = l_simulation_ue_id;
4078 l_index := 1;
4079 END IF;
4080 l_parent_ue_id := l_ue_simue_id_table(child_deferral_ue_rec.parent_ue_id);
4081 l_unit_effectivity_id := child_deferral_ue_rec.child_ue_id;
4082 create_simue_from_ue_record (p_unit_effectivity_id =>l_unit_effectivity_id,
4083 p_originator_ue_id => l_originator_ue_id,
4084 p_parent_ue_id => l_parent_ue_id,
4085 p_deferral_ue_cols => l_deferral_ue_cols,
4086 x_simulation_ue_id => l_simulation_ue_id) ;
4087
4088 l_ue_simue_id_table(l_unit_effectivity_id) := l_simulation_ue_id;
4089 END LOOP;
4090
4091 END LOOP; -- Deferred UEs
4092
4093 END LOOP; -- p_config_node_tbl
4094
4095
4096
4097
4098 -- Delete temp table data.
4099 G_SIM_UE_ID_TBL.DELETE;
4100
4101 --Update sim table for last bue run date
4102 UPDATE AHL_SIMULATION_PLANS_B
4103 SET LAST_BUE_RUN = sysdate
4104 WHERE simulation_plan_id = p_simulation_plan_id;
4105
4106 IF G_DEBUG = 'Y' THEN
4107 AHL_DEBUG_PUB.Debug('End Process Flush_To_Sim_Ue_Table');
4108 END IF;
4109
4110
4111 END Flush_To_Sim_Ue_Table;
4112
4113 -- To create ahl_unit_effectivities record.
4114 PROCEDURE Create_sim_ue_record (p_x_temp_sim_ue_rec IN OUT NOCOPY ahl_ue_simulations%ROWTYPE)
4115
4116 IS
4117
4118 BEGIN
4119
4120
4121 IF G_DEBUG = 'Y' THEN
4122 AHL_DEBUG_PUB.Debug('Start Create Record in Sim UE Table');
4123 END IF;
4124
4125
4126 INSERT INTO AHL_UE_SIMULATIONS (
4127 SIMULATION_UE_ID ,
4128 SIMULATION_PLAN_ID ,
4129 CSI_ITEM_INSTANCE_ID ,
4130 MR_HEADER_ID ,
4131 FLEET_HEADER_ID ,
4132 UNIT_CONFIG_HEADER_ID ,
4133 DUE_DATE ,
4134 STATUS_CODE ,
4135 OBJECT_VERSION_NUMBER ,
4136 LAST_UPDATE_DATE ,
4137 LAST_UPDATED_BY ,
4138 CREATION_DATE ,
4139 CREATED_BY ,
4140 LAST_UPDATE_LOGIN ,
4141 SECURITY_GROUP_ID ,
4142 MR_EFFECTIVITY_ID ,
4143 MR_INTERVAL_ID ,
4144 DUE_COUNTER_VALUE ,
4145 FORECAST_SEQUENCE ,
4146 REPETITIVE_MR_FLAG ,
4147 TOLERANCE_FLAG ,
4148 MESSAGE_CODE ,
4149 DATE_RUN ,
4150 PRECEDING_UE_ID ,
4151 EARLIEST_DUE_DATE ,
4152 LATEST_DUE_DATE ,
4153 DEFER_FROM_UE_ID ,
4154 CS_INCIDENT_ID ,
4155 COUNTER_ID ,
4156 UNIT_EFFECTIVITY_ID ,
4157 ORIGINATOR_UE_ID ,
4158 PARENT_UE_ID
4159 ) VALUES (
4160 p_x_temp_sim_ue_rec.SIMULATION_UE_ID ,
4161 p_x_temp_sim_ue_rec.SIMULATION_PLAN_ID ,
4162 p_x_temp_sim_ue_rec.CSI_ITEM_INSTANCE_ID ,
4163 p_x_temp_sim_ue_rec.MR_HEADER_ID ,
4164 p_x_temp_sim_ue_rec.FLEET_HEADER_ID ,
4165 p_x_temp_sim_ue_rec.UNIT_CONFIG_HEADER_ID ,
4166 p_x_temp_sim_ue_rec.DUE_DATE ,
4167 p_x_temp_sim_ue_rec.STATUS_CODE ,
4168 p_x_temp_sim_ue_rec.OBJECT_VERSION_NUMBER ,
4169 p_x_temp_sim_ue_rec.LAST_UPDATE_DATE ,
4170 p_x_temp_sim_ue_rec.LAST_UPDATED_BY ,
4171 p_x_temp_sim_ue_rec.CREATION_DATE ,
4172 p_x_temp_sim_ue_rec.CREATED_BY ,
4173 p_x_temp_sim_ue_rec.LAST_UPDATE_LOGIN ,
4174 p_x_temp_sim_ue_rec.SECURITY_GROUP_ID ,
4175 p_x_temp_sim_ue_rec.MR_EFFECTIVITY_ID ,
4176 p_x_temp_sim_ue_rec.MR_INTERVAL_ID ,
4177 p_x_temp_sim_ue_rec.DUE_COUNTER_VALUE ,
4178 p_x_temp_sim_ue_rec.FORECAST_SEQUENCE ,
4179 p_x_temp_sim_ue_rec.REPETITIVE_MR_FLAG ,
4180 p_x_temp_sim_ue_rec.TOLERANCE_FLAG ,
4181 p_x_temp_sim_ue_rec.MESSAGE_CODE ,
4182 p_x_temp_sim_ue_rec.DATE_RUN ,
4183 p_x_temp_sim_ue_rec.PRECEDING_UE_ID ,
4184 p_x_temp_sim_ue_rec.EARLIEST_DUE_DATE ,
4185 p_x_temp_sim_ue_rec.LATEST_DUE_DATE ,
4186 p_x_temp_sim_ue_rec.DEFER_FROM_UE_ID ,
4187 p_x_temp_sim_ue_rec.CS_INCIDENT_ID ,
4188 p_x_temp_sim_ue_rec.COUNTER_ID ,
4189 p_x_temp_sim_ue_rec.UNIT_EFFECTIVITY_ID ,
4190 p_x_temp_sim_ue_rec.ORIGINATOR_UE_ID ,
4191 p_x_temp_sim_ue_rec.PARENT_UE_ID
4192 );
4193
4194 IF G_DEBUG = 'Y' THEN
4195 AHL_DEBUG_PUB.Debug('End Create Record');
4196 END IF;
4197
4198
4199
4200
4201
4202 EXCEPTION
4203 -- If any error occurs, then, abort API.
4204 WHEN NO_DATA_FOUND THEN
4205 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
4206 FND_MSG_PUB.ADD;
4207 RAISE FND_API.G_EXC_ERROR;
4208
4209 END create_sim_ue_record;
4210
4211 -- To create decendent records if the MR is a group MR for Simulated UEs
4212
4213 PROCEDURE Create_group_for_sim_ue (p_x_temp_sim_ue_rec IN OUT NOCOPY ahl_ue_simulations%ROWTYPE)
4214
4215 IS
4216 -- Read group elements.
4217 CURSOR ahl_temp_grp_csr(p_csi_item_instance_id IN NUMBER,
4218 p_mr_header_id IN NUMBER,
4219 p_forecast_sequence IN NUMBER) IS
4220 SELECT unit_effectivity_id,
4221 csi_item_instance_id,
4222 MR_header_id,
4223 due_date,
4224 mr_interval_id,
4225 mr_effectivity_id,
4226 due_counter_value,
4227 parent_csi_item_instance_id,
4228 parent_mr_header_id,
4229 orig_csi_item_instance_id,
4230 orig_mr_header_id,
4231 forecast_sequence,
4232 repetitive_mr_flag,
4233 tolerance_flag,
4234 message_code,
4235 earliest_due_date,
4236 latest_due_date,
4237 counter_id
4238 FROM ahl_temp_unit_effectivities
4239 START WITH parent_csi_item_instance_id = p_csi_item_instance_id
4240 AND parent_mr_header_id = p_mr_header_id
4241 AND orig_csi_item_instance_id = p_csi_item_instance_id
4242 AND orig_mr_header_id = p_mr_header_id
4243 AND orig_forecast_sequence = p_forecast_sequence
4244 AND nvl(preceding_check_flag,'N') = 'N'
4245 CONNECT BY PRIOR MR_header_id = parent_mr_header_id
4246 AND PRIOR csi_item_instance_id = parent_csi_item_instance_id
4247 AND orig_csi_item_instance_id = p_csi_item_instance_id
4248 AND orig_mr_header_id = p_mr_header_id
4249 AND orig_forecast_sequence = p_forecast_sequence
4250 AND nvl(preceding_check_flag,'N') = 'N'
4251 FOR UPDATE OF due_date;
4252
4253 -- get parent unit effectivity id.
4254 CURSOR ahl_temp_parent_csr (p_parent_csi_item_instance_id IN NUMBER,
4255 p_parent_mr_header_id IN NUMBER,
4256 p_orig_csi_item_instance_id IN NUMBER,
4257 p_orig_mr_header_id IN NUMBER,
4258 p_forecast_sequence IN NUMBER) IS
4259 SELECT unit_effectivity_id
4260 FROM ahl_temp_unit_effectivities
4261 WHERE csi_item_instance_id = p_parent_csi_item_instance_id
4262 AND MR_header_id = p_parent_mr_header_id
4263 AND orig_csi_item_instance_id = p_orig_csi_item_instance_id
4264 AND orig_mr_header_id = p_orig_mr_header_id
4265 AND orig_forecast_sequence = p_forecast_sequence;
4266
4267 l_ue_relationship_id NUMBER;
4268
4269 l_originator_ue_id NUMBER;
4270 l_orig_csi_item_instance_id NUMBER;
4271 l_orig_mr_header_id NUMBER;
4272
4273 l_parent_ue_id NUMBER;
4274
4275
4276 l_temp_child_rec ahl_ue_simulations%ROWTYPE;
4277
4278 BEGIN
4279
4280
4281 IF G_DEBUG = 'Y' THEN
4282 AHL_DEBUG_PUB.Debug('Start Create Group For Simulation UEs');
4283 AHL_DEBUG_PUB.Debug('CSI:MR:' || p_x_temp_sim_ue_rec.csi_item_instance_id || ',' || p_x_temp_sim_ue_rec.MR_header_id);
4284 END IF;
4285
4286 -- For top node.
4287 create_sim_ue_record (p_x_temp_sim_ue_rec);
4288
4289 -- Update ahl_temp_unit_effectivities with the unit effectivity id.
4290 UPDATE ahl_temp_unit_effectivities
4291 SET unit_effectivity_id = p_x_temp_sim_ue_rec.simulation_ue_id
4292 WHERE csi_item_instance_id = p_x_temp_sim_ue_rec.csi_item_instance_id AND
4293 mr_header_id = p_x_temp_sim_ue_rec.mr_header_id AND
4294 forecast_sequence = p_x_temp_sim_ue_rec.forecast_sequence;
4295
4296 l_originator_ue_id := p_x_temp_sim_ue_rec.simulation_ue_id;
4297 l_orig_csi_item_instance_id := p_x_temp_sim_ue_rec.csi_item_instance_id;
4298 l_orig_mr_header_id := p_x_temp_sim_ue_rec.mr_header_id;
4299
4300 -- Read all elements.
4301 FOR l_temp_grp_rec IN ahl_temp_grp_csr(p_x_temp_sim_ue_rec.csi_item_instance_id,
4302 p_x_temp_sim_ue_rec.mr_header_id,
4303 p_x_temp_sim_ue_rec.forecast_sequence)
4304 LOOP
4305
4306 -- set record values.
4307 IF(G_SIM_UE_ID_TBL.EXISTS(G_SIM_UE_ID_INDEX)) THEN
4308 l_temp_child_rec.simulation_ue_id := G_SIM_UE_ID_TBL(G_SIM_UE_ID_INDEX);
4309 G_SIM_UE_ID_INDEX := G_SIM_UE_ID_INDEX +1;
4310 ELSE
4311 l_temp_child_rec.simulation_ue_id := AHL_UE_SIMULATIONS_S.NEXtVAL;
4312 END IF;
4313
4314 l_temp_child_rec.simulation_plan_id := p_x_temp_sim_ue_rec.simulation_plan_id;
4315 l_temp_child_rec.csi_item_instance_id := l_temp_grp_rec.csi_item_instance_id;
4316 -- Considering child UEs will have same Fleet ID as parent UEs.
4317 l_temp_child_rec.fleet_header_id := p_x_temp_sim_ue_rec.fleet_header_id;
4318 l_temp_child_rec.mr_interval_id := l_temp_grp_rec.mr_interval_id;
4319 l_temp_child_rec.mr_effectivity_id := l_temp_grp_rec.mr_effectivity_id;
4320 l_temp_child_rec.MR_header_id := l_temp_grp_rec.mr_header_id;
4321 l_temp_child_rec.unit_config_header_id := p_x_temp_sim_ue_rec.unit_config_header_id;
4322 l_temp_child_rec.due_date := l_temp_grp_rec.due_date;
4323 l_temp_child_rec.due_counter_value := l_temp_grp_rec.due_counter_value;
4324 l_temp_child_rec.forecast_sequence := l_temp_grp_rec.forecast_sequence;
4325 l_temp_child_rec.repetitive_mr_flag := l_temp_grp_rec.repetitive_mr_flag;
4326 l_temp_child_rec.tolerance_flag := l_temp_grp_rec.tolerance_flag;
4327 l_temp_child_rec.message_code := l_temp_grp_rec.message_code;
4328 l_temp_child_rec.earliest_due_date := l_temp_grp_rec.earliest_due_date;
4329 l_temp_child_rec.latest_due_date := l_temp_grp_rec.latest_due_date;
4330 l_temp_child_rec.counter_id := l_temp_grp_rec.counter_id;
4331 l_temp_child_rec.originator_ue_id := l_originator_ue_id;
4332 l_temp_child_rec.object_version_number := 1;
4333 l_temp_child_rec.creation_date := sysdate;
4334 l_temp_child_rec.last_update_date := sysdate;
4335 l_temp_child_rec.last_updated_by := p_x_temp_sim_ue_rec.last_updated_by;
4336 l_temp_child_rec.created_by := p_x_temp_sim_ue_rec.created_by;
4337 l_temp_child_rec.last_update_login := p_x_temp_sim_ue_rec.last_update_login;
4338 l_temp_child_rec.security_group_id := NULL;
4339
4340 OPEN ahl_temp_parent_csr(l_temp_grp_rec.parent_csi_item_instance_id,
4341 l_temp_grp_rec.parent_mr_header_id,
4342 l_orig_csi_item_instance_id,
4343 l_orig_mr_header_id,
4344 p_x_temp_sim_ue_rec.forecast_sequence);
4345 FETCH ahl_temp_parent_csr INTO l_parent_ue_id;
4346 IF (ahl_temp_parent_csr%NOTFOUND) THEN
4347 FND_MESSAGE.Set_Name ('AHL','AHL_UMP_PUE_PARENT_NOTFOUND');
4348 FND_MESSAGE.Set_Token ('INST_ID',l_temp_grp_rec.csi_item_instance_id);
4349 FND_MESSAGE.Set_Token ('MR_ID',l_temp_grp_rec.mr_header_id);
4350 FND_MSG_PUB.ADD;
4351 CLOSE ahl_temp_parent_csr;
4352 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4353 END IF;
4354 CLOSE ahl_temp_parent_csr;
4355 l_temp_child_rec.parent_ue_id:= l_parent_ue_id;
4356
4357 -- Insert into AHL_UE_SIMULATIONS
4358 create_sim_ue_record (l_temp_child_rec);
4359
4360 -- Update ahl_temp_unit_effectivities with the sim unit effectivity id.
4361 UPDATE ahl_temp_unit_effectivities
4362 SET unit_effectivity_id = l_temp_child_rec.simulation_ue_id
4363 WHERE CURRENT OF ahl_temp_grp_csr;
4364
4365
4366
4367 END LOOP;
4368
4369 IF G_DEBUG = 'Y' THEN
4370 AHL_DEBUG_PUB.Debug('End Create Group');
4371 END IF;
4372
4373 EXCEPTION
4374 -- If any error occurs, then, abort API.
4375 WHEN NO_DATA_FOUND THEN
4376 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
4377 FND_MSG_PUB.ADD;
4378 RAISE FND_API.G_EXC_ERROR;
4379
4380 END Create_group_for_sim_ue;
4381
4382 -- To create simulation ue rec from ahl_unit_effectivities record.
4383 -- IF p_deferral_ue_cols IS NOT NULL, deferral related cols will be picked up from this record.
4384 PROCEDURE create_simue_from_ue_record (p_unit_effectivity_id IN NUMBER,
4385 p_originator_ue_id IN NUMBER,
4386 p_parent_ue_id IN NUMBER,
4387 p_deferral_ue_cols IN Deferral_UE_Cols_Type := NULL,
4388 x_simulation_ue_id OUT NOCOPY NUMBER
4389 )
4390
4391 IS
4392 -- get UE detail record from effectivity table
4393 CURSOR get_ue_details_csr(c_unit_effectivity_id NUMBER) IS
4394 SELECT unit_effectivity_id,
4395 csi_item_instance_id,
4396 mr_header_id,
4397 due_date,
4398 status_code,
4399 mr_interval_id,
4400 mr_effectivity_id,
4401 due_counter_value,
4402 forecast_sequence,
4403 repetitive_mr_flag,
4404 tolerance_flag,
4405 message_code,
4406 earliest_due_date,
4407 latest_due_date,
4408 counter_id,
4409 fleet_header_id,
4410 cs_incident_id,
4411 manually_planned_flag,
4412 defer_from_ue_id
4413 from ahl_unit_effectivities_b
4414 where unit_effectivity_id = c_unit_effectivity_id;
4415
4416 l_temp_ue_rec get_ue_details_csr%ROWTYPE;
4417 l_temp_sim_ue_rec ahl_ue_simulations%ROWTYPE;
4418 BEGIN
4419
4420 IF G_DEBUG = 'Y' THEN
4421 AHL_DEBUG_PUB.Debug('Start Create_simue_from_ue_record');
4422 END IF;
4423
4424 OPEN get_ue_details_csr(p_unit_effectivity_id);
4425 FETCH get_ue_details_csr INTO l_temp_ue_rec;
4426 IF (get_ue_details_csr%FOUND) THEN
4427 l_temp_sim_ue_rec := NULL;
4428 -- set record values.
4429 IF(G_SIM_UE_ID_TBL.EXISTS(G_SIM_UE_ID_INDEX)) THEN
4430 l_temp_sim_ue_rec.simulation_ue_id := G_SIM_UE_ID_TBL(G_SIM_UE_ID_INDEX);
4431 G_SIM_UE_ID_INDEX := G_SIM_UE_ID_INDEX +1;
4432 ELSE
4433 l_temp_sim_ue_rec.simulation_ue_id := AHL_UE_SIMULATIONS_S.NEXtVAL;
4434 END IF;
4435 l_temp_sim_ue_rec.simulation_plan_id := G_SIMULATION_PLAN_ID;
4436 l_temp_sim_ue_rec.unit_effectivity_id := p_unit_effectivity_id;
4437 l_temp_sim_ue_rec.csi_item_instance_id := l_temp_ue_rec.csi_item_instance_id;
4438 l_temp_sim_ue_rec.fleet_header_id := l_temp_ue_rec.fleet_header_id;
4439 l_temp_sim_ue_rec.mr_header_id := l_temp_ue_rec.mr_header_id;
4440 l_temp_sim_ue_rec.unit_config_header_id := G_UC_HEADER_ID;
4441 l_temp_sim_ue_rec.status_code := l_temp_ue_rec.status_code ;
4442 l_temp_sim_ue_rec.mr_interval_id := l_temp_ue_rec.mr_interval_id;
4443 l_temp_sim_ue_rec.mr_effectivity_id := l_temp_ue_rec.mr_effectivity_id;
4444 l_temp_sim_ue_rec.due_counter_value := l_temp_ue_rec.due_counter_value;
4445 l_temp_sim_ue_rec.forecast_sequence := l_temp_ue_rec.forecast_sequence;
4446 l_temp_sim_ue_rec.repetitive_mr_flag := l_temp_ue_rec.repetitive_mr_flag;
4447 l_temp_sim_ue_rec.date_run := sysdate;
4448 l_temp_sim_ue_rec.preceding_ue_id := null;
4449 l_temp_sim_ue_rec.earliest_due_date := l_temp_ue_rec.earliest_due_date;
4450 l_temp_sim_ue_rec.latest_due_date := l_temp_ue_rec.latest_due_date;
4451 l_temp_sim_ue_rec.defer_from_ue_id := l_temp_ue_rec.defer_from_ue_id;
4452 l_temp_sim_ue_rec.cs_incident_id := l_temp_ue_rec.cs_incident_id;
4453 l_temp_sim_ue_rec.object_version_number := 1;
4454 l_temp_sim_ue_rec.creation_date := sysdate;
4455 l_temp_sim_ue_rec.last_update_date := sysdate;
4456 l_temp_sim_ue_rec.last_updated_by := fnd_global.user_id;
4457 l_temp_sim_ue_rec.created_by := fnd_global.user_id;
4458 l_temp_sim_ue_rec.last_update_login := fnd_global.login_id;
4459 l_temp_sim_ue_rec.security_group_id := NULL;
4460 l_temp_sim_ue_rec.originator_ue_id := p_originator_ue_id;
4461 l_temp_sim_ue_rec.parent_ue_id := p_parent_ue_id;
4462
4463 IF (p_deferral_ue_cols.unit_effectivity_id IS NOT NULL) THEN
4464 l_temp_sim_ue_rec.due_date := p_deferral_ue_cols.due_date;
4465 l_temp_sim_ue_rec.tolerance_flag := p_deferral_ue_cols.tolerance_flag;
4466 l_temp_sim_ue_rec.message_code := p_deferral_ue_cols.message_code;
4467 l_temp_sim_ue_rec.counter_id := p_deferral_ue_cols.counter_id;
4468 ELSE
4469 l_temp_sim_ue_rec.due_date := l_temp_ue_rec.due_date;
4470 l_temp_sim_ue_rec.tolerance_flag := l_temp_ue_rec.tolerance_flag;
4471 l_temp_sim_ue_rec.message_code := l_temp_ue_rec.message_code;
4472 l_temp_sim_ue_rec.counter_id := l_temp_ue_rec.counter_id;
4473 END IF;
4474 --Insert new record in sim ue table
4475 Create_sim_ue_record(p_x_temp_sim_ue_rec => l_temp_sim_ue_rec);
4476
4477 x_simulation_ue_id := l_temp_sim_ue_rec.simulation_ue_id;
4478
4479 END IF;
4480 CLOSE get_ue_details_csr;
4481
4482 IF G_DEBUG = 'Y' THEN
4483 AHL_DEBUG_PUB.Debug('End Create Record');
4484 END IF;
4485
4486
4487
4488 EXCEPTION
4489 -- If any error occurs, then, abort API.
4490 WHEN NO_DATA_FOUND THEN
4491 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
4492 FND_MSG_PUB.ADD;
4493 RAISE FND_API.G_EXC_ERROR;
4494
4495 END create_simue_from_ue_record;
4496
4497
4498
4499 END AHL_UMP_PROCESSUNIT_EXTN_PVT;