[Home] [Help]
PACKAGE BODY: APPS.AHL_FLEET_FORECAST_PVT
Source
1 PACKAGE BODY AHL_FLEET_FORECAST_PVT AS
2 /*$Header: AHLVFLTB.pls 120.0.12020000.3 2012/12/15 04:15:40 viagrawa noship $*/
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_FLEET_FORECAST_PVT';
4 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
5 ------------------------------------
6 -- Common constants and variables --
7 ------------------------------------
8 l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
9 l_log_statement NUMBER := fnd_log.level_statement;
10 l_log_procedure NUMBER := fnd_log.level_procedure;
11 l_log_error NUMBER := fnd_log.level_error;
12 l_log_unexpected NUMBER := fnd_log.level_unexpected;
13 -----------------------------------------------------------------
14 ------------------ Locking Exception ----------------------------
15 record_locked EXCEPTION;
16 pragma exception_init (record_locked, -54);
17 /*
18 * The procedure Remove_Fleet will be called to hard/soft delete fleets as applicable
19 */
20 PROCEDURE Remove_Fleet
21 (
22 p_api_version IN NUMBER,
23 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
24 p_commit IN VARCHAR2 := Fnd_Api.G_TRUE,
25 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
26 p_module_type IN VARCHAR2 := NULL,
27 p_fleet_id IN NUMBER,
28 p_obj_version_no IN NUMBER,
29 x_return_status OUT NOCOPY VARCHAR2,
30 x_msg_count OUT NOCOPY NUMBER,
31 x_msg_data OUT NOCOPY VARCHAR2)
32 IS
33 -- Cursor for checking lock in ue table
34 CURSOR check_lock_ue_tbl(p_fleet_id NUMBER)
35 IS
36 SELECT 1
37 FROM AHL_UE_SIMULATIONS
38 WHERE fleet_header_id = p_fleet_id FOR UPDATE;
39 -- Cursor to pick fleets which are draft, has null completion date and has no mr associations
40 CURSOR applicable_draft_fleets(p_fleet_id NUMBER)
41 IS
42 SELECT object_version_number
43 FROM AHL_FLEET_HEADERS_B fhb
44 WHERE fhb.fleet_header_id = p_fleet_id
45 AND fhb.status_code = 'DRAFT'
46 AND fhb.completion_date IS NULL
47 AND NOT EXISTS
48 (SELECT 1
49 FROM AHL_MR_EFFECTIVITIES mef, AHL_MR_HEADERS_B mhb
50 WHERE fhb.fleet_header_id = mef.fleet_header_id
51 AND mhb.mr_header_id = mef.mr_header_id
52 AND mhb.mr_status_code NOT IN ('DRAFT')
53 );
54
55 CURSOR applicable_fleets_complete_mr(p_fleet_id NUMBER)
56 IS
57 SELECT object_version_number
58 FROM AHL_FLEET_HEADERS_B fhb
59 WHERE fhb.fleet_header_id = p_fleet_id
60 AND EXISTS
61 (SELECT 1
62 FROM AHL_MR_EFFECTIVITIES mef, AHL_MR_HEADERS_B mhb
63 WHERE fhb.fleet_header_id = mef.fleet_header_id
64 AND mhb.mr_header_id = mef.mr_header_id
65 AND mhb.mr_status_code = 'COMPLETE'
66 );
67
68 --Pekambar Added for Close SR
69 CURSOR get_fleet_unit_asss(c_fleet_id NUMBER)
70 IS
71 SELECT fleet_unit_assoc_id
72 FROM ahl_fleet_unit_assocs
73 WHERE fleet_header_id = c_fleet_id;
74
75 --Standard local variables
76 L_API_NAME CONSTANT VARCHAR2(30) := 'Remove_Fleet';
77 L_API_VERSION CONSTANT NUMBER := 1.0;
78 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
79 l_msg_data VARCHAR2(2000);
80 l_return_status VARCHAR2(1);
81 l_msg_count NUMBER;
82 l_locked_rows VARCHAR2(1):= 'N';
83 -- local variables
84 l_status VARCHAR2(30);
85 l_completion_date DATE;
86 l_ovn NUMBER;
87 l_temp NUMBER;
88 ---------------------------------------- Start of the procedure Remove_Fleet ----------------------------------------------------------------------
89 BEGIN
90 -- Logging
91 IF (l_log_procedure >= l_log_current_level) THEN
92 fnd_log.string ( l_log_procedure, 'ahl.plsql.Remove_Fleet', 'Start of procedure. Input params p_fleet_id ='|| p_fleet_id||' p_obj_version_no ='||p_obj_version_no);
93 END IF;
94 -- initialize return status.
95 x_return_status := FND_API.G_RET_STS_SUCCESS;
96 -- Standard start of API savepoint
97 SAVEPOINT Remove_Fleet;
98 -- Initialize message list if p_init_msg_list is set to TRUE
99 IF FND_API.To_Boolean(p_init_msg_list) THEN
100 FND_MSG_PUB.Initialize;
101 END IF;
102 -- Standard call to check for call compatibility.
103 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name,G_PKG_NAME) THEN
104 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
105 END IF;
106 -- Check for Required Parameters
107 IF(p_fleet_id IS NULL) THEN
108 FND_MESSAGE.Set_Name('AHL','AHL_FLEET_NULL_FLT_ID');
109 FND_MSG_PUB.ADD;
110 IF (l_log_unexpected >= l_log_current_level) THEN
111 fnd_log.string ( l_log_unexpected, 'ahl.plsql.', 'For this API flow, Fleet Id is mandatory but found null in input ' );
112 END IF;
113 RAISE FND_API.G_EXC_ERROR;
114 END IF;
115 --Logging
116 IF (l_log_statement >= l_log_current_level) THEN
117 fnd_log.string(l_log_statement,L_DEBUG_KEY,'p_fleet_id sent from UI: '||p_fleet_id);
118 END IF;
119 -- Check for locks of the ue records
120 FOR i IN 1..2
121 LOOP
122 BEGIN
123 OPEN check_lock_ue_tbl(p_fleet_id);
124 l_locked_rows := 'N';
125 CLOSE check_lock_ue_tbl;
126 -- lock is not there at this moment. So exit and perform update
127 EXIT;
128 EXCEPTION
129 WHEN record_locked THEN
130 l_locked_rows := 'Y';
131 -- lock exists. Wait for 5 seconds and try again
132 DBMS_LOCK.SLEEP(5);
133 END;
134 END LOOP;
135 -- Logging
136 IF (l_log_statement >= l_log_current_level) THEN
137 fnd_log.string ( l_log_statement, 'ahl.plsql.Remove_Fleet', 'All locks taken');
138 END IF;
139 -- All Locking complete
140 IF (l_locked_rows = 'N') THEN
141 -- Start processing
142 BEGIN
143 SELECT status_code,
144 object_version_number
145 INTO l_status,
146 l_ovn
147 FROM AHL_FLEET_HEADERS_B
148 WHERE fleet_header_id = p_fleet_id;
149 EXCEPTION
150 WHEN NO_DATA_FOUND THEN
151 FND_MESSAGE.Set_Name('AHL','AHL_FLEET_NULL_FLT_ID');
152 FND_MSG_PUB.ADD;
153 IF (l_log_unexpected >= l_log_current_level) THEN
154 fnd_log.string ( l_log_unexpected, 'ahl.plsql.', 'No matching fleet id in table' );
155 END IF;
156 RAISE FND_API.G_EXC_ERROR;
157 END;
158 IF (l_status = 'DELETED') THEN
159 -- The record is already deleted
160 FND_MESSAGE.set_name('AHL','AHL_FLEET_RECORD_CHANGED');
161 FND_MSG_PUB.ADD;
162 IF (l_log_unexpected >= l_log_current_level) THEN
163 fnd_log.string ( l_log_unexpected, 'ahl.plsql.Remove_Fleet', 'The fleet status is already DELETED' );
164 END IF;
165 RAISE FND_API.G_EXC_ERROR;
166 END IF;
167 -- Start processing
168 -- Open the cursor for draft fleet
169 OPEN applicable_draft_fleets(p_fleet_id);
170 FETCH applicable_draft_fleets INTO l_temp;
171 IF (applicable_draft_fleets%FOUND) THEN
172 -- Logging
173 IF (l_log_statement >= l_log_current_level) THEN
174 fnd_log.string ( l_log_statement, 'ahl.plsql.Remove_Fleet', 'Fleet to be hard deleted');
175 END IF;
176 -- MR association not there for the fleet. Can be hard deleted
177 DELETE
178 FROM AHL_FLEET_HEADERS_B
179 WHERE fleet_header_id = p_fleet_id;
180 -- Delete from TL table
181 DELETE
182 FROM AHL_FLEET_HEADERS_TL
183 WHERE fleet_header_id = p_fleet_id;
184 -- Delete fleet unit association
185 DELETE
186 FROM AHL_FLEET_UNIT_ASSOCS
187 WHERE fleet_header_id = p_fleet_id;
188 -- Delete UEs generated for the fleet
189 DELETE
190 FROM AHL_UE_SIMULATIONS
191 WHERE fleet_header_id = p_fleet_id;
192 -- Update fleet data in MR table
193 -- Note that we are not using OVN in where clause for this update
194 /* UPDATE AHL_MR_EFFECTIVITIES
195 SET fleet_header_id = NULL,
196 object_version_number = object_version_number + 1,
197 last_update_date = SYSDATE,
198 last_updated_by = FND_GLOBAL.user_id,
199 last_update_login = FND_GLOBAL.login_id
200 WHERE fleet_header_id = p_fleet_id;*/
201 -- Delete MR effectivities
202 DELETE
203 FROM AHL_MR_EFFECTIVITIES
204 WHERE fleet_header_id = p_fleet_id;
205 -- Unit Downtime
206 DELETE
207 FROM AHL_FLEET_DOWNTIMES
208 WHERE fleet_header_id = p_fleet_id;
209 -- Asscoiated Forecast
210 DELETE
211 FROM AHL_FLEET_UTILIZATION
212 WHERE fleet_header_id = p_fleet_id;
213 -- Reconfig MRs
214 DELETE
215 FROM AHL_FLEET_RECONFIG_MRS
216 WHERE fleet_header_id = p_fleet_id;
217 IF (l_log_statement >= l_log_current_level) THEN
218 fnd_log.string ( l_log_statement, 'ahl.plsql.Remove_Fleet', 'Updates complete in if');
219 END IF;
220 ELSE
221 -- Check if there exists association with complete MRs
222 IF (l_log_statement >= l_log_current_level) THEN
223 fnd_log.string ( l_log_statement, 'ahl.plsql.Remove_Fleet', 'Opening applicable_fleets_complete_mr');
224 END IF;
225 OPEN applicable_fleets_complete_mr(p_fleet_id);
226 FETCH applicable_fleets_complete_mr
227 INTO l_temp;
228
229 IF (applicable_fleets_complete_mr%FOUND) THEN
230 -- Fleet can not be deleted. Throw error
231 FND_MESSAGE.Set_Name('AHL','AHL_FLEET_COMPL_MR_ASSOC');
232 FND_MSG_PUB.ADD;
233 IF (l_log_statement >= l_log_current_level) THEN
234 fnd_log.string ( l_log_statement, 'ahl.plsql.', 'Fleet is associated to completed MR and hence can not be deleted ' );
235 END IF;
236 RAISE FND_API.G_EXC_ERROR;
237 ELSE
238 -- Fleet needs to be soft deleted
239 IF (l_log_statement >= l_log_current_level) THEN
240 fnd_log.string ( l_log_statement, 'ahl.plsql.Remove_Fleet', 'Fleet to be soft deleted');
241 END IF;
242
243 -- Pekambar Added to Close SR if Fleet is DELETED
244 -- Call the process_reconfig_mrs to Close the SRS which are already created
245 FOR l_close_sr IN get_fleet_unit_asss(p_fleet_id)
246 LOOP
247 IF G_DEBUG = 'Y' THEN
248 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Call to AHL_UMP_NONROUTINES_PVT.Process_SR_MR_Associations is Failed' );
249 END IF;
250 process_reconfig_mrs
251 (
252 p_api_version => 1.0,
253 p_init_msg_list => FND_API.G_TRUE,
254 p_commit => FND_API.G_TRUE,
255 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
256 p_module_type => 'API',
257 x_return_status => l_return_status,
258 x_msg_count => l_msg_count,
259 x_msg_data => l_msg_data,
260 p_fleet_id => NULL,
261 p_unit_config_id => NULL,
262 p_fleet_unit_asso_id => l_close_sr.fleet_unit_assoc_id,
263 p_delete_sr_flag => 'Y'
264 );
265
266 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
267 THEN
268 IF G_DEBUG = 'Y' THEN
269 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Call to AHL_UMP_NONROUTINES_PVT.Process_SR_MR_Associations is Failed' );
270 END IF;
271 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
272 END IF;
273 END LOOP;
274
275 -- fleet headers
276 UPDATE AHL_FLEET_HEADERS_B
277 SET object_version_number = object_version_number + 1,
278 status_code = 'DELETED',
279 last_update_date = SYSDATE,
280 last_updated_by = FND_GLOBAL.user_id,
281 last_update_login = FND_GLOBAL.login_id
282 WHERE fleet_header_id = p_fleet_id
283 AND object_version_number = p_obj_version_no;
284 -- rowcount will be 0 when ovn does not match
285 IF ( SQL%ROWCOUNT = 0 ) THEN
286 FND_MESSAGE.set_name('AHL','AHL_FLEET_RECORD_CHANGED');
287 FND_MSG_PUB.add;
288 IF (l_log_unexpected >= l_log_current_level) THEN
289 fnd_log.string ( l_log_unexpected, 'ahl.plsql.Remove_Fleet', 'The record has been already updated' );
290 END IF;
291 RAISE FND_API.G_EXC_ERROR;
292 END IF;
293 -- Delete UEs generated for the fleet
294 DELETE
295 FROM AHL_UE_SIMULATIONS
296 WHERE fleet_header_id = p_fleet_id;
297 -- Delete fleet unit association
298 DELETE
299 FROM AHL_FLEET_UNIT_ASSOCS
300 WHERE fleet_header_id = p_fleet_id;
301 -- Update fleet data in MRtable
302 -- Delete the effectivity
303 DELETE
304 FROM AHL_MR_EFFECTIVITIES
305 WHERE fleet_header_id = p_fleet_id;
306 -- Note that we are not using OVN in where clause for this update
307 /*UPDATE AHL_MR_EFFECTIVITIES
308 SET fleet_header_id = NULL,
309 object_version_number = object_version_number + 1,
310 last_update_date = SYSDATE,
311 last_updated_by = FND_GLOBAL.user_id,
312 last_update_login = FND_GLOBAL.login_id
313 WHERE fleet_header_id = p_fleet_id;*/
314
315 -- Unit Downtime
316 DELETE
317 FROM AHL_FLEET_DOWNTIMES
318 WHERE fleet_header_id = p_fleet_id;
319 -- Asscoiated Forecast
320 DELETE
321 FROM AHL_FLEET_UTILIZATION
322 WHERE fleet_header_id = p_fleet_id;
323 -- Reconfig MRs
324 /* Reconfig MR processing is done already
325 DELETE
326 FROM AHL_FLEET_RECONFIG_MRS
327 WHERE fleet_header_id = p_fleet_id;*/
328 IF (l_log_statement >= l_log_current_level) THEN
329 fnd_log.string ( l_log_statement, 'ahl.plsql.Remove_Fleet', 'Updates complete in else');
330 END IF;
331
332 IF (l_log_statement >= l_log_current_level) THEN
333 fnd_log.string ( l_log_statement, 'ahl.plsql.Remove_Fleet', 'Updates complete');
334 END IF;
335 END IF;
336 CLOSE applicable_fleets_complete_mr;
337
338 END IF;
339 CLOSE applicable_draft_fleets;
340 ELSE
341 ROLLBACK TO Remove_Fleet;
342 FND_MESSAGE.Set_Name('AHL','AHL_SIM_ROWS_LOCKED');
343 FND_MSG_PUB.ADD;
344 RAISE FND_API.G_EXC_ERROR;
345 END IF;
346 -- Standard check of p_commit
347 IF FND_API.TO_BOOLEAN(p_commit) THEN
348 COMMIT;
349 END IF;
350 -- Logging
351 IF (l_log_procedure >= l_log_current_level) THEN
352 fnd_log.string ( l_log_procedure,L_DEBUG_KEY ||'.end','At the end of PLSQL procedure Remove_Fleet');
353 END IF;
354 EXCEPTION
355 WHEN FND_API.G_EXC_ERROR THEN
356 IF (l_log_unexpected >= l_log_current_level) THEN
357 fnd_log.string ( l_log_unexpected, 'ahl.plsql.Remove_Fleet', 'G_EXC_ERROR');
358 END IF;
359 x_return_status := FND_API.G_RET_STS_ERROR;
360 ROLLBACK TO Remove_Fleet;
361 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
362 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
363 IF (l_log_unexpected >= l_log_current_level) THEN
364 fnd_log.string ( l_log_unexpected, 'ahl.plsql.Remove_Fleet', 'G_EXC_UNEXPECTED_ERROR');
365 END IF;
366 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
367 ROLLBACK TO Remove_Fleet;
368 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
369 WHEN OTHERS THEN
370 IF (l_log_unexpected >= l_log_current_level) THEN
371 fnd_log.string ( l_log_unexpected, 'ahl.plsql.Remove_Fleet', 'OTHERS');
372 END IF;
373 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
374 ROLLBACK TO Remove_Fleet;
375 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
376 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME, p_procedure_name => 'Remove_Fleet', p_error_text => SUBSTR(SQLERRM,1,500));
377 END IF;
378 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
379 END Remove_Fleet;
380
381
382 /*-----------------------------------------------------
383 Procedure Name : process_reconfig_mrs
384 Description : This API create/delete/update Service Request based on Reconguration MRs associated to Fleet
385 Input Parameters : p_fleet_id IN NUMBER,
386 p_unit_config_id IN NUMBER,
387 p_fleet_unit_asso_id IN NUMBER,
388 p_delete_sr_flag IN VARCHAR2 := 'N'
389 --------------------------------------------------------*/
390
391 PROCEDURE process_reconfig_mrs
392 (
393 p_api_version IN NUMBER,
394 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
395 p_commit IN VARCHAR2 := FND_API.G_FALSE,
396 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
397 p_module_type IN VARCHAR2,
398 x_return_status OUT NOCOPY VARCHAR2,
399 x_msg_count OUT NOCOPY NUMBER,
400 x_msg_data OUT NOCOPY VARCHAR2,
401 p_fleet_id IN NUMBER,
402 p_unit_config_id IN NUMBER,
403 p_fleet_unit_asso_id IN NUMBER,
404 p_delete_sr_flag IN VARCHAR2 := 'N'
405 )
406 AS
407
408 CURSOR is_fleet_valid(c_fleet_header_id NUMBER)
409 IS
410 SELECT 'X'
411 FROM ahl_fleet_headers_b
412 WHERE fleet_header_id = c_fleet_header_id;
413
414 CURSOR is_unit_valid(c_unit_config_header_id NUMBER)
415 IS
416 SELECT 'X'
417 FROM ahl_unit_config_headers
418 WHERE unit_config_header_id = c_unit_config_header_id;
419
420 CURSOR get_fleet_unit_asso(c_fleet_header_id NUMBER, c_unit_config_header_id NUMBER)
421 IS
422 SELECT flt.fleet_header_id, flt.name, flt.operations_type_code, fus.unit_config_header_id,
423 fus.fleet_unit_assoc_id, fus.object_version_number, fus.reconfig_sr_id,
424 fus.association_start, fus.association_end
425 FROM ahl_fleet_unit_assocs fus, ahl_simulation_plans_b sim, ahl_fleet_headers_b flt
426 WHERE sim.primary_plan_flag = 'Y'
427 AND sim.simulation_type = 'UMP'
428 AND flt.fleet_header_id = fus.fleet_header_id
429 AND flt.status_code = 'COMPLETE'
430 AND fus.simulation_plan_id = sim.simulation_plan_id
431 AND fus.association_start >= SYSDATE
432 AND flt.fleet_header_id = NVL(c_fleet_header_id,flt.fleet_header_id)
433 AND fus.unit_config_header_id = NVL(c_unit_config_header_id,fus.unit_config_header_id)
434 ORDER BY unit_config_header_id,ASSOCIATION_START;
435 /* arunjk Included Association_start in order by for Bug# 13364180 Issue# 2 */
436
437 CURSOR get_item_instance_det(c_unit_config_header_id NUMBER)
438 IS
439 SELECT uch.csi_item_instance_id, csi.instance_number, csi.serial_number,
440 csi.inventory_item_id , uch.name, csi.owner_party_id
441 FROM ahl_unit_config_headers uch, csi_item_instances csi
442 WHERE uch.unit_config_header_id = c_unit_config_header_id
443 AND uch.csi_item_instance_id = csi.instance_id;
444
445 CURSOR get_reconfig_mrs(c_fleet_header_id NUMBER, c_src_operations_type_code VARCHAR2)
446 IS
447 SELECT frm.fleet_reconfig_mr_id, mrh.mr_header_id, frm.mr_title,
448 frm.src_operations_type_code, frm.compliance_lead_time
449 FROM ahl_fleet_reconfig_mrs frm, ahl_mr_headers_v mrh
450 WHERE frm.fleet_header_id = c_fleet_header_id
451 AND frm.mr_title = mrh.title
452 AND trunc(nvl(mrh.effective_to, SYSDATE +1))>trunc(SYSDATE)
453 AND mrh.mr_status_code = 'COMPLETE'
454 AND NVL(frm.src_operations_type_code,c_src_operations_type_code) = c_src_operations_type_code;
455
456 CURSOR get_mr_header_id(c_mr_title VARCHAR2)
457 IS
458 SELECT mr_header_id
459 FROM ahl_mr_headers_v
460 WHERE trunc(nvl( effective_to, SYSDATE +1))>trunc(SYSDATE)
461 AND mr_status_code = 'COMPLETE'
462 AND title = c_mr_title;
463
464 CURSOR get_prev_opstype(c_unit_config_header_id NUMBER,c_association_start DATE )
465 IS
466 SELECT flt.operations_type_code
467 FROM ahl_fleet_unit_assocs fus, ahl_simulation_plans_b sim, ahl_fleet_headers_b flt
468 WHERE sim.primary_plan_flag = 'Y'
469 AND sim.simulation_type = 'UMP'
470 AND fus.simulation_plan_id = sim.simulation_plan_id
471 AND flt.fleet_header_id = fus.fleet_header_id
472 AND flt.completion_date IS NOT NULL
473 AND fus.association_end < c_association_start
474 AND fus.unit_config_header_id = c_unit_config_header_id
475 AND ROWNUM = 1
476 ORDER BY association_end DESC;
477
478 CURSOR is_sr_planned(c_cs_incident_id NUMBER)
479 IS
480 SELECT 'X'
481 FROM ahl_visit_tasks_b vt, ahl_unit_effectivities_b ue
482 WHERE vt.status_code <> 'DELETED'
483 AND vt.unit_effectivity_id = ue.unit_effectivity_id
484 AND ue.cs_incident_id = c_cs_incident_id;
485
486 CURSOR get_sr_details(p_cs_incident_id IN NUMBER)
487 IS
488 SELECT incident_id, object_version_number, incident_status_id, incident_severity_id, summary, incident_number, incident_date, expected_resolution_date, incident_type_id, customer_id, caller_type
489 FROM cs_incidents_all_vl
490 where incident_id = p_cs_incident_id;
491
492 CURSOR get_sr_mr_headers(c_cs_incident_id NUMBER)
493 IS
494 SELECT mr_header_id, object_version_number
495 FROM ahl_unit_effectivities_b
496 WHERE unit_effectivity_id
497 IN (
498 SELECT related_ue_id
499 FROM ahl_ue_relationships urs, ahl_unit_effectivities_b ueb
500 WHERE urs.ue_id = ueb.unit_effectivity_id
501 AND ueb.cs_incident_id = c_cs_incident_id
502 );
503
504 CURSOR get_sr_id(c_fleet_unit_asso_id NUMBER)
505 IS
506 SELECT reconfig_sr_id, unit_config_header_id
507 FROM ahl_fleet_unit_assocs
508 WHERE reconfig_sr_id IS NOT NULL
509 AND fleet_unit_assoc_id = c_fleet_unit_asso_id;
510
511
512
513
514 l_msg_count NUMBER;
515 l_msg_data VARCHAR2(2000);
516 l_return_status VARCHAR2(1);
517 l_api_version CONSTANT NUMBER := 1.0;
518 l_api_name VARCHAR2(80) := 'process_reconfig_mrs';
519 l_dummy VARCHAR2(1);
520
521 l_inventory_item_id NUMBER;
522 l_csi_item_instance_id NUMBER;
523 l_cs_object_version NUMBER;
524 l_max_compliance_lead_time NUMBER := 0;
525 l_temp_compliance_lead_time NUMBER := 0;
526 l_resoultion_date DATE;
527
528 l_temp_uc_header_id NUMBER := -1;
529 l_unit_config_header_id NUMBER := -1;
530
531 l_temp_recon_mr_header_id NUMBER := -1;
532
533 l_prev_opstype VARCHAR2(30);
534 l_temp_reconfig_sr_id NUMBER;
535
536 indx NUMBER;
537 j NUMBER;
538 i NUMBER;
539 l_size NUMBER;
540
541 l_cur_item_instance_det get_item_instance_det%ROWTYPE;
542 l_cur_sr_details get_sr_details%ROWTYPE;
543
544 l_applicable_mr_tbl AHL_FMP_PVT.APPLICABLE_MR_TBL_TYPE;
545 l_mr_sr_assoc_tbl AHL_UMP_SR_PVT.SR_MR_Association_Tbl_Type;
546 l_nr_task_rec AHL_UMP_NONROUTINES_PVT.NonRoutine_Rec_Type;
547
548 TYPE nbr_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
549 l_temp_reconfig_mr_id_tbl nbr_tbl_type;
550 l_temp_reconfig_mr_ue_ovn nbr_tbl_type;
551 l_reconfig_mr_ue_ovn nbr_tbl_type;
552 l_comp_lead_time_tbl nbr_tbl_type;
553 l_new_reconfig_mr_id_tbl nbr_tbl_type;
554
555 TYPE number_tbl_type IS TABLE OF NUMBER;
556 l_tmp_recon_mr_id_tbl number_tbl_type := number_tbl_type(1);
557 l_new_recon_mr_id_tbl number_tbl_type := number_tbl_type(1);
558 l_old_recon_mr_id_tbl number_tbl_type := number_tbl_type(1);
559
560 BEGIN
561
562 IF G_DEBUG = 'Y' THEN
563 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Begin private API:' );
564 END IF;
565
566 -- Initialize API return status to success
567 x_return_status := FND_API.G_RET_STS_SUCCESS;
568
569 -- Standard Start of API savepoint
570 SAVEPOINT process_reconfig_mrs_sp;
571
572 -- Standard call to check for call compatibility.
573 IF NOT FND_API.compatible_api_call
574 (
575 l_api_version,
576 p_api_version,
577 l_api_name,
578 G_PKG_NAME
579 )
580 THEN
581 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
582 END IF;
583
584 -- Initialize message list if p_init_msg_list is set to TRUE.
585 IF FND_API.to_boolean( p_init_msg_list ) THEN
586 FND_MSG_PUB.initialize;
587 END IF;
588
589 IF G_DEBUG = 'Y' THEN
590 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Input Parameters::' );
591 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::p_fleet_id::'||p_fleet_id||'::p_unit_config_id::'||p_unit_config_id );
592 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || 'p_fleet_unit_asso_id::'||p_fleet_unit_asso_id||'::p_delete_sr_flag::'||p_delete_sr_flag );
593 END IF;
594
595 -- This Condition will be executed if the API is called when fleet - unit association is removed
596 -- and p_delete_sr_flag should be passed as 'Y'
597 IF(p_delete_sr_flag = 'Y' AND p_fleet_unit_asso_id IS NOT NULL)
598 THEN
599
600 OPEN get_sr_id(p_fleet_unit_asso_id);
601 FETCH get_sr_id INTO l_temp_reconfig_sr_id, l_unit_config_header_id;
602
603 IF(get_sr_id%FOUND)
604 THEN
605 -- Check Whether SR is planned or not, If not then only Cancel/Close SR
606 OPEN is_sr_planned (l_temp_reconfig_sr_id);
607 FETCH is_sr_planned INTO l_dummy;
608
609 IF(is_sr_planned%NOTFOUND)
610 THEN
611 OPEN get_sr_details (l_temp_reconfig_sr_id);
612 FETCH get_sr_details INTO l_cur_sr_details;
613 IF (get_sr_details%NOTFOUND)
614 THEN
615 CLOSE get_sr_details;
616 FND_MESSAGE.set_name('AHL', 'AHL_UMP_NR_UE_ERROR');
617 FND_MESSAGE.set_token('INC_ID', l_cur_sr_details.incident_id);
618 FND_MSG_PUB.ADD;
619 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
620 END IF;
621 CLOSE get_sr_details;
622
623 -- Get the csi_item_instance_id for given uc_id
624 OPEN get_item_instance_det(l_unit_config_header_id);
625 FETCH get_item_instance_det INTO l_cur_item_instance_det;
626 CLOSE get_item_instance_det;
627
628 -- Consolidating Input Params to Cancel/Close SR
629 l_nr_task_rec.incident_id := l_cur_sr_details.incident_id ;
630 l_nr_task_rec.incident_object_version_number := l_cur_sr_details.object_version_number ;
631 l_nr_task_rec.severity_id := l_cur_sr_details.incident_severity_id;
632 l_nr_task_rec.problem_summary := l_cur_sr_details.summary;
633 l_nr_task_rec.instance_id := l_cur_item_instance_det.csi_item_instance_id;
634 l_nr_task_rec.customer_type := l_cur_sr_details.caller_type;
635 l_nr_task_rec.customer_id := l_cur_sr_details.customer_id;
636 --l_nr_task_rec.status_id := 1178; -- Status Id for Cancel SR
637 l_nr_task_rec.status_id := 2; -- Status Id for Close SR
638 l_nr_task_rec.type_id := l_cur_sr_details.incident_type_id;
639
640 -- Copy all MR Headers and Object Version numbers
641 -- Call process Associations and delete the associations for already created SR
642 -- and then call update_sr
643 i := 1;
644 FOR l_remove_mr_ass IN get_sr_mr_headers(l_nr_task_rec.incident_id)
645 LOOP
646 l_mr_sr_assoc_tbl(i).mr_header_id := l_remove_mr_ass.mr_header_id;
647 l_mr_sr_assoc_tbl(i).operation_flag := 'D';
648 l_mr_sr_assoc_tbl(i).object_version_number := l_remove_mr_ass.object_version_number;
649 l_mr_sr_assoc_tbl(i).csi_instance_id := l_nr_task_rec.instance_id;
650 i := i+1;
651 END LOOP;
652
653 IF G_DEBUG = 'Y' THEN
654 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::MRS for delete associations to Close SR Count for SR ::'|| l_nr_task_rec.incident_id||'-- is::'|| l_mr_sr_assoc_tbl.count);
655 END IF;
656
657 -- Call Process Associations
658 IF(l_mr_sr_assoc_tbl.count >0)
659 THEN
660 /** Input Table Params to Process -MR Associations */
661 IF G_DEBUG = 'Y' THEN
662 FOR i IN l_mr_sr_assoc_tbl.FIRST..l_mr_sr_assoc_tbl.LAST
663 LOOP
664 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || ':: Association Table Parameter Values:: ' );
665 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::l_mr_sr_assoc_tbl('||i||').mr_header_id::' || l_mr_sr_assoc_tbl(i).mr_header_id);
666 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::l_mr_sr_assoc_tbl('||i||').operation_flag::' || l_mr_sr_assoc_tbl(i).operation_flag);
667 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::l_mr_sr_assoc_tbl('||i||').object_version_number::' || l_mr_sr_assoc_tbl(i).object_version_number);
668 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::l_mr_sr_assoc_tbl('||i||').csi_instance_id::' || l_mr_sr_assoc_tbl(i).csi_instance_id);
669 END LOOP;
670 END IF;
671
672 AHL_UMP_SR_PVT.Process_SR_MR_Associations
673 (
674 p_api_version => 1.0,
675 p_init_msg_list => FND_API.G_FALSE,
676 p_commit => FND_API.G_FALSE,
677 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
678 x_return_status => l_return_status,
679 x_msg_count => l_msg_count,
680 x_msg_data => l_msg_data,
681 p_user_id => fnd_global.user_id,
682 p_login_id => fnd_global.login_id,
683 p_request_id => l_nr_task_rec.incident_id,
684 p_object_version_number => l_nr_task_rec.incident_object_version_number,
685 p_request_number => null,
686 p_x_sr_mr_association_tbl => l_mr_sr_assoc_tbl
687 );
688
689 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
690 THEN
691 IF G_DEBUG = 'Y' THEN
692 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Call to AHL_UMP_NONROUTINES_PVT.Process_SR_MR_Associations for Delete is Failed' );
693 END IF;
694 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
695 END IF;
696 END IF;
697
698 IF G_DEBUG = 'Y' THEN
699 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Before Call to AHL_UMP_NONROUTINES_PVT.Update_SR for Closing' );
700 END IF;
701
702 AHL_UMP_NONROUTINES_PVT.Update_SR
703 (
704 p_api_version => 1.0,
705 p_init_msg_list => FND_API.G_TRUE,
706 p_commit => FND_API.G_FALSE,
707 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
708 p_default => FND_API.G_FALSE,
709 p_module_type => p_module_type,
710 x_return_status => l_return_status,
711 x_msg_count => l_msg_count,
712 x_msg_data => l_msg_data,
713 p_x_nonroutine_rec => l_nr_task_rec
714 );
715
716 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
717 THEN
718 IF G_DEBUG = 'Y' THEN
719 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Call to AHL_UMP_NONROUTINES_PVT.Update_SR is Failed' );
720 END IF;
721 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
722 END IF;
723 END IF;
724 CLOSE is_sr_planned;
725 END IF;
726
727 CLOSE get_sr_id;
728
729 ELSE
730 --Validate Input params
731 IF(p_fleet_id IS NOT NULL)
732 THEN
733 OPEN is_fleet_valid(p_fleet_id);
734 FETCH is_fleet_valid INTO l_dummy;
735 IF(is_fleet_valid%NOTFOUND)
736 THEN
737 FND_MESSAGE.set_name( 'AHL', 'AHL_FLT_INVLD_FLEET_ID' );
738 FND_MESSAGE.set_token('FIELD', p_fleet_id );
739 RAISE FND_API.G_EXC_ERROR;
740 END IF;
741 CLOSE is_fleet_valid;
742 END IF;
743
744 IF(p_unit_config_id IS NOT NULL)
745 THEN
746 OPEN is_unit_valid(p_unit_config_id);
747 FETCH is_unit_valid INTO l_dummy;
748 IF(is_unit_valid%NOTFOUND)
749 THEN
750 FND_MESSAGE.set_name( 'AHL', 'AHL_FLT_INVLD_UC_ID' );
751 FND_MESSAGE.set_token('FIELD', p_unit_config_id );
752 RAISE FND_API.G_EXC_ERROR;
753 END IF;
754 CLOSE is_unit_valid;
755 END IF;
756
757 FOR l_fleet_unit_asso_rec IN get_fleet_unit_asso(p_fleet_id, p_unit_config_id)
758 LOOP
759
760 BEGIN
761 -- Get Item Instance Id once for UC Id
762 IF(l_temp_uc_header_id <> l_fleet_unit_asso_rec.unit_config_header_id)
763 THEN
764 l_temp_uc_header_id := l_fleet_unit_asso_rec.unit_config_header_id ;
765 -- Get the csi_item_instance_id for given uc_id
766 OPEN get_item_instance_det(l_fleet_unit_asso_rec.unit_config_header_id);
767 FETCH get_item_instance_det INTO l_cur_item_instance_det;
768 CLOSE get_item_instance_det;
769 END IF;
770
771 --Get the previous associated fleets Operatiosn Type
772 OPEN get_prev_opstype(l_fleet_unit_asso_rec.unit_config_header_id, l_fleet_unit_asso_rec.association_start);
773 FETCH get_prev_opstype INTO l_prev_opstype;
774 IF(get_prev_opstype%NOTFOUND)
775 THEN
776 l_prev_opstype := 'X'; -- If there is no record set 'X' (for NULL)
777 END IF;
778 CLOSE get_prev_opstype;
779
780 IF G_DEBUG = 'Y' THEN
781 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Uc_ID::'||l_fleet_unit_asso_rec.unit_config_header_id );
782 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Item Instance ID::'||l_cur_item_instance_det.csi_item_instance_id );
783 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Previous Operations Type::'||l_prev_opstype );
784 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Fleet Id::'|| l_fleet_unit_asso_rec.fleet_header_id );
785 END IF;
786
787 -- Strore mr_header_id as index for reconfig mr table(l_temp_reconfig_mr_id_tbl)
788 -- Compliance_lead_time as a value
789 i :=1 ;
790 -- Clear the temp Table
791 l_temp_reconfig_mr_id_tbl.delete;
792 l_comp_lead_time_tbl.delete;
793 -- Clear the MR association Table
794 l_mr_sr_assoc_tbl.delete;
795
796 l_max_compliance_lead_time := 0;
797 -- This Cursor returns Two records with same MR_Header_id
798 -- one with matched Operations Type (If Exists) and
799 -- second with Operations type is null
800 -- and order by mr_header_id, operations Type Nulls last
801 FOR l_reconfig_mr_rec IN get_reconfig_mrs(l_fleet_unit_asso_rec.fleet_header_id, l_prev_opstype)
802 LOOP
803 IF(l_temp_recon_mr_header_id <> l_reconfig_mr_rec.mr_header_id)
804 THEN
805 l_temp_recon_mr_header_id := l_reconfig_mr_rec.mr_header_id;
806 l_temp_reconfig_mr_id_tbl(i) := l_reconfig_mr_rec.mr_header_id;
807 l_comp_lead_time_tbl(i) := NVL(l_reconfig_mr_rec.compliance_lead_time,0);
808
809 i := i+1;
810 END IF;
811 END LOOP;
812
813 -- Clear ALL the Set Operations Tables and Re Intialilize
814 l_tmp_recon_mr_id_tbl.delete;
815 l_new_recon_mr_id_tbl.delete;
816 l_old_recon_mr_id_tbl.delete;
817
818 l_tmp_recon_mr_id_tbl := number_tbl_type(1);
819 l_new_recon_mr_id_tbl := number_tbl_type(1);
820 l_old_recon_mr_id_tbl := number_tbl_type(1);
821
822
823 l_nr_task_rec := NULL;
824 indx := 1;
825 -- Clear l_new_reconfig_mr_id_tbl table
826 l_new_reconfig_mr_id_tbl.delete;
827
828 IF(l_temp_reconfig_mr_id_tbl.count > 0)
829 THEN
830 FOR i IN l_temp_reconfig_mr_id_tbl.FIRST..l_temp_reconfig_mr_id_tbl.LAST
831 LOOP
832 IF G_DEBUG = 'Y' THEN
833 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::In For Loop of create ::'||l_temp_reconfig_mr_id_tbl(i) );
834 END IF;
835
836 --Clear l_applicable_mr_tbl before call GET_APPLICABLE_MRS
837 l_applicable_mr_tbl.delete;
838 -- Get all the applicable MRs for Each Filtered Reconfiguration MR
839 AHL_FMP_PVT.GET_APPLICABLE_MRS
840 (
841 p_api_version => 1.0,
842 p_init_msg_list => FND_API.G_FALSE,
843 p_commit => FND_API.G_FALSE,
844 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
845 x_return_status => l_return_status,
846 x_msg_count => l_msg_count,
847 x_msg_data => l_msg_data,
848 p_item_instance_id => l_cur_item_instance_det.csi_item_instance_id ,
849 p_mr_header_id => l_temp_reconfig_mr_id_tbl(i),
850 p_components_flag => 'Y',
851 p_include_doNotImplmt => 'Y',
852 p_visit_type_code => NULL,
853 x_applicable_mr_tbl => l_applicable_mr_tbl
854 );
855
856 -- Raise errors if exceptions occurs in get_applicable_mrs API
857 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
858 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
859 THEN
860 IF G_DEBUG = 'Y' THEN
861 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Call to AHL_FMP_PVT.GET_APPLICABLE_MRS is Failed' );
862 END IF;
863 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
864 END IF;
865 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
866 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
867 RAISE FND_API.G_EXC_ERROR;
868 END IF;
869
870 IF G_DEBUG = 'Y' THEN
871 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Effective Reconfig MR '||'::Reconfig MR::'||i||':::' || l_applicable_mr_tbl.count );
872 END IF;
873
874 IF(l_applicable_mr_tbl.count > 0)
875 THEN
876 IF G_DEBUG = 'Y' THEN
877 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Applicable MR ::'||i||':::'|| l_temp_reconfig_mr_id_tbl(i) );
878 END IF;
879 -- Copy to New Reconfig MRs Table
880 l_new_reconfig_mr_id_tbl(indx) := l_temp_reconfig_mr_id_tbl(i);
881
882 -- Copy Max Compliance Lead Time
883 IF( l_comp_lead_time_tbl(i) > l_max_compliance_lead_time )
884 THEN
885 l_max_compliance_lead_time := l_comp_lead_time_tbl(i) ;
886 END IF;
887
888 indx := indx +1;
889 END IF;
890
891 END LOOP;
892 END IF;
893
894 IF G_DEBUG = 'Y' THEN
895 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::After Filtering with GET_APPLICABLE_MRS New Reconfiguration MRs count is:: '|| l_new_reconfig_mr_id_tbl.COUNT );
896 END IF;
897
898 -- Resolution Date for SR is l_max_compliance_lead_time (days) ahead of unit association Start date
899 l_resoultion_date := l_fleet_unit_asso_rec.association_start - l_max_compliance_lead_time;
900
901 --Problem Summary
902 FND_MESSAGE.Set_Name('AHL','AHL_FLT_RECON_MR_SUMM');
903 FND_MESSAGE.Set_Token('UNIT',l_cur_item_instance_det.name);
904 FND_MESSAGE.Set_Token('FLEET',l_fleet_unit_asso_rec.name);
905
906 IF(l_fleet_unit_asso_rec.reconfig_sr_id IS NULL)
907 THEN
908 IF G_DEBUG = 'Y' THEN
909 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Reconfiguration MRS count is After Filtering '|| l_new_reconfig_mr_id_tbl.COUNT );
910 END IF;
911
912 -- If atleast one reconfiguration MR exist then create Service Request
913 IF(l_new_reconfig_mr_id_tbl.count > 0)
914 THEN
915 --Consolidating Input Params for creating SR (Madatory Params as per FDD)
916 l_nr_task_rec.inventory_item_id := l_cur_item_instance_det.inventory_item_id;
917 l_nr_task_rec.serial_number := l_cur_item_instance_det.serial_number;
918 l_nr_task_rec.instance_id := l_cur_item_instance_det.csi_item_instance_id;
919 l_nr_task_rec.instance_number := l_cur_item_instance_det.instance_number;
920 l_nr_task_rec.type_id := FND_PROFILE.VALUE('AHL_SER_REQ_TYP_FOR_RECON_MR');
921 l_nr_task_rec.customer_type := 'ORGANIZATION';
922 l_nr_task_rec.customer_id := NVL(l_cur_item_instance_det.owner_party_id, -1);
923 l_nr_task_rec.severity_id := FND_PROFILE.VALUE('AHL_PRD_SR_SEVERITY');
924 l_nr_task_rec.problem_summary := FND_MESSAGE.GET;
925 l_nr_task_rec.unit_config_header_id := l_fleet_unit_asso_rec.unit_config_header_id;
926 l_nr_task_rec.incident_date := SYSDATE;
927 l_nr_task_rec.expected_resolution_date := l_resoultion_date;
928
929 IF G_DEBUG = 'Y' THEN
930 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Create_SR Input Params::' );
931 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::inventory_item_id::' || l_nr_task_rec.inventory_item_id || '::serial_number::'|| l_nr_task_rec.serial_number);
932 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::csi_item_instance_id::' || l_nr_task_rec.instance_id ||'::instance_number::'|| l_nr_task_rec.instance_number);
933 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Service Type ::'||l_nr_task_rec.type_id||'::customer_type::' || l_nr_task_rec.customer_type);
934 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Customer_id ::'||l_nr_task_rec.customer_id||'::severity_id::' || l_nr_task_rec.severity_id);
935 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Summary ::' ||l_nr_task_rec.problem_summary||'::uc_id::'||l_nr_task_rec.unit_config_header_id );
936 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::incident_date ::' ||l_nr_task_rec.incident_date||'::expected_resolution_date::'||l_nr_task_rec.expected_resolution_date);
937 END IF;
938 -- Call Create SR
939 AHL_UMP_NONROUTINES_PVT.Create_SR
940 (
941 p_api_version => 1.0,
942 p_init_msg_list => FND_API.G_TRUE,
943 p_commit => FND_API.G_FALSE,
944 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
945 p_default => FND_API.G_FALSE,
946 p_module_type => p_module_type,
947 x_return_status => l_return_status,
948 x_msg_count => l_msg_count,
949 x_msg_data => l_msg_data,
950 p_x_nonroutine_rec => l_nr_task_rec
951 );
952
953 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
954 THEN
955 IF G_DEBUG = 'Y' THEN
956 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Call to AHL_UMP_NONROUTINES_PVT.Create_SR is Failed' );
957 END IF;
958 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
959 END IF;
960
961 -- get object version number for the service request and the new ue id.
962 OPEN get_sr_details (l_nr_task_rec.incident_id);
963 FETCH get_sr_details INTO l_cur_sr_details;
964 IF (get_sr_details%NOTFOUND) THEN
965 CLOSE get_sr_details;
966 FND_MESSAGE.set_name('AHL', 'AHL_UMP_NR_UE_ERROR');
967 FND_MESSAGE.set_token('INC_ID', l_nr_task_rec.incident_id);
968 FND_MSG_PUB.ADD;
969 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
970 END IF;
971 CLOSE get_sr_details;
972
973 -- Copy Inputs from l_new_reconfig_mr_id_tbl to l_mr_sr_assoc_tbl
974 IF G_DEBUG = 'Y' THEN
975 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || ':: Before Call to Process_SR_MR_Associations:: l_new_reconfig_mr_id_tbl Count:: ' || l_new_reconfig_mr_id_tbl.count );
976 END IF;
977 indx := 1 ;
978 FOR i IN l_new_reconfig_mr_id_tbl.FIRST..l_new_reconfig_mr_id_tbl.LAST
979 LOOP
980 l_mr_sr_assoc_tbl(indx).mr_header_id := l_new_reconfig_mr_id_tbl(i);
981 l_mr_sr_assoc_tbl(indx).operation_flag := 'C';
982 l_mr_sr_assoc_tbl(indx).csi_instance_id := l_nr_task_rec.instance_id;
983
984 IF G_DEBUG = 'Y' THEN
985 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || ':: Before Call to Process_SR_MR_Associations:: MR Header ID ' || l_mr_sr_assoc_tbl(i).mr_header_id );
986 END IF;
987
988 indx := indx +1;
989 END LOOP;
990
991 IF G_DEBUG = 'Y' THEN
992 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || ':: Before Call to Process_SR_MR_Associations ' );
993 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || ':: Before Call to l_mr_sr_assoc_tbl Count:: ' || l_mr_sr_assoc_tbl.count );
994 END IF;
995 -- Call Process Associations
996 AHL_UMP_SR_PVT.Process_SR_MR_Associations
997 (
998 p_api_version => 1.0,
999 p_init_msg_list => FND_API.G_FALSE,
1000 p_commit => FND_API.G_FALSE,
1001 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1002 x_return_status => l_return_status,
1003 x_msg_count => l_msg_count,
1004 x_msg_data => l_msg_data,
1005 p_user_id => fnd_global.user_id,
1006 p_login_id => fnd_global.login_id,
1007 p_request_id => l_nr_task_rec.incident_id, --populated by create_sr API
1008 p_object_version_number => l_cur_sr_details.object_version_number,
1009 p_request_number => null,
1010 p_x_sr_mr_association_tbl => l_mr_sr_assoc_tbl
1011 );
1012
1013 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1014 THEN
1015 IF G_DEBUG = 'Y' THEN
1016 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Call to AHL_UMP_NONROUTINES_PVT.Process_SR_MR_Associations is Failed' );
1017 END IF;
1018 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1019 END IF;
1020
1021 -- Update Fleet - Unit Associtiaon Table with New SR_Incident_Id
1022 UPDATE ahl_fleet_unit_assocs
1023 SET reconfig_sr_id = l_nr_task_rec.incident_id,
1024 object_version_number = l_fleet_unit_asso_rec.object_version_number + 1,
1025 last_update_date = SYSDATE,
1026 last_updated_by = FND_GLOBAL.user_id,
1027 last_update_login = FND_GLOBAL.login_id
1028 WHERE fleet_unit_assoc_id = l_fleet_unit_asso_rec.fleet_unit_assoc_id
1029 AND object_version_number = l_fleet_unit_asso_rec.object_version_number;
1030
1031 IF (SQL%NOTFOUND)
1032 THEN
1033 FND_MESSAGE.set_name('AHL', 'AHL_FLT_UNIT_ASS_MOD');
1034 FND_MESSAGE.set_token('ASS_ID', l_fleet_unit_asso_rec.fleet_unit_assoc_id);
1035 FND_MSG_PUB.ADD;
1036 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1037 END IF;
1038
1039 IF G_DEBUG = 'Y' THEN
1040 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Fleet - Unit Assocition is Updated with SR_ID::'||l_nr_task_rec.incident_id );
1041 END IF;
1042
1043 fnd_file.put_line(FND_FILE.LOG, 'SR created for Unit ::'||l_cur_item_instance_det.name);
1044 fnd_file.put_line(FND_FILE.LOG, 'SR created for Fleet ::'||l_fleet_unit_asso_rec.name);
1045
1046 END IF; -- end reconfig_mr count
1047
1048 ELSIF(l_fleet_unit_asso_rec.reconfig_sr_id IS NOT NULL)
1049 THEN
1050
1051 OPEN is_sr_planned (l_fleet_unit_asso_rec.reconfig_sr_id);
1052 FETCH is_sr_planned INTO l_dummy;
1053
1054 IF(is_sr_planned%NOTFOUND)
1055 THEN
1056 OPEN get_sr_details (l_fleet_unit_asso_rec.reconfig_sr_id);
1057 FETCH get_sr_details INTO l_cur_sr_details;
1058 IF (get_sr_details%NOTFOUND) THEN
1059 CLOSE get_sr_details;
1060 FND_MESSAGE.set_name('AHL', 'AHL_UMP_NR_UE_ERROR');
1061 FND_MESSAGE.set_token('INC_ID', l_nr_task_rec.incident_id);
1062 FND_MSG_PUB.ADD;
1063 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1064 END IF;
1065 CLOSE get_sr_details;
1066
1067 -- Consolidating Input Params to Update SR
1068 l_nr_task_rec.incident_id := l_cur_sr_details.incident_id ;
1069 l_nr_task_rec.incident_object_version_number := l_cur_sr_details.object_version_number ;
1070 l_nr_task_rec.status_id := l_cur_sr_details.incident_status_id;
1071 l_nr_task_rec.severity_id := l_cur_sr_details.incident_severity_id;
1072 l_nr_task_rec.problem_summary := l_cur_sr_details.summary;
1073 l_nr_task_rec.instance_id := l_cur_item_instance_det.csi_item_instance_id;
1074 l_nr_task_rec.customer_type := l_cur_sr_details.caller_type;
1075 l_nr_task_rec.customer_id := l_cur_sr_details.customer_id;
1076 -- get the Reconfiguration SR Type from profile
1077 l_nr_task_rec.type_id := FND_PROFILE.VALUE('AHL_SER_REQ_TYP_FOR_RECON_MR');
1078 l_nr_task_rec.expected_resolution_date := l_resoultion_date;
1079
1080 -- Clear l_temp_reconfig_mr_id_tbl and l_temp_reconfig_mr_ue_ovn
1081 l_temp_reconfig_mr_id_tbl.delete;
1082 l_temp_reconfig_mr_ue_ovn.delete;
1083
1084 IF(l_new_reconfig_mr_id_tbl.count > 0)
1085 THEN
1086 -- Get the resolution date for existing SR if it is not same as new resoultion date then update SR
1087 IF(l_cur_sr_details.expected_resolution_date <> l_resoultion_date OR l_nr_task_rec.type_id <> l_cur_sr_details.incident_type_id )
1088 THEN
1089 IF G_DEBUG = 'Y' THEN
1090 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Update_SR Input Params::' );
1091 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::incident_id::' || l_nr_task_rec.incident_id || '::incident_object_version_number::'|| l_nr_task_rec.incident_object_version_number);
1092 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::status_id ::'||l_nr_task_rec.status_id||'::severity_id::' || l_nr_task_rec.severity_id);
1093 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::problem_summary ::' ||l_nr_task_rec.problem_summary||'::instance_id::'||l_nr_task_rec.instance_id );
1094 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::expected_resolution_date ::' ||l_nr_task_rec.expected_resolution_date||'::instance_type_id::'||l_nr_task_rec.type_id );
1095 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Reconfig MRs Count ::' ||l_new_reconfig_mr_id_tbl.count);
1096 END IF;
1097 l_nr_task_rec.type_id := l_cur_sr_details.incident_type_id ;
1098
1099 AHL_UMP_NONROUTINES_PVT.Update_SR
1100 (
1101 p_api_version => 1.0,
1102 p_init_msg_list => FND_API.G_TRUE,
1103 p_commit => FND_API.G_FALSE,
1104 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1105 p_default => FND_API.G_FALSE,
1106 p_module_type => p_module_type,
1107 x_return_status => l_return_status,
1108 x_msg_count => l_msg_count,
1109 x_msg_data => l_msg_data,
1110 p_x_nonroutine_rec => l_nr_task_rec
1111 );
1112
1113 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1114 THEN
1115 IF G_DEBUG = 'Y' THEN
1116 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Call to AHL_UMP_NONROUTINES_PVT.Update_SR is Failed' );
1117 END IF;
1118 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1119 END IF;
1120 END IF;
1121
1122 -- Get ALL the Mr_headers for alreadycreated SR
1123 OPEN get_sr_mr_headers(l_nr_task_rec.incident_id);
1124 FETCH get_sr_mr_headers BULK COLLECT INTO l_temp_reconfig_mr_id_tbl, l_temp_reconfig_mr_ue_ovn;
1125 CLOSE get_sr_mr_headers;
1126
1127 IF G_DEBUG = 'Y' THEN
1128 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Already Added MRS Count to SR::'|| l_nr_task_rec.incident_id||':: is ::'|| l_temp_reconfig_mr_id_tbl.count);
1129 END IF;
1130 -- Copy all the existing Reconfig MRS in to l_old_recon_mr_id_tbl
1131 IF(l_temp_reconfig_mr_id_tbl.count > 0)
1132 THEN
1133 l_size := l_temp_reconfig_mr_id_tbl.count - 1;
1134 IF(l_size > 0)
1135 THEN
1136 l_old_recon_mr_id_tbl.EXTEND(l_size,1);
1137 END IF;
1138
1139 FOR i IN l_temp_reconfig_mr_id_tbl.FIRST..l_temp_reconfig_mr_id_tbl.LAST
1140 LOOP
1141 l_old_recon_mr_id_tbl(i) := l_temp_reconfig_mr_id_tbl(i);
1142 l_reconfig_mr_ue_ovn(l_temp_reconfig_mr_id_tbl(i)) := l_temp_reconfig_mr_ue_ovn(i);
1143 END LOOP;
1144 IF G_DEBUG = 'Y' THEN
1145 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Existing UES count to SR::'|| l_nr_task_rec.incident_id||':::'|| l_old_recon_mr_id_tbl.count);
1146 END IF;
1147 ELSE
1148 -- If There are no old UEs reset the table
1149 l_old_recon_mr_id_tbl.delete;
1150 END IF;
1151
1152 -- Copy all the New Reconfig MRS in to l_new_recon_mr_id_tbl
1153 j := 1;
1154 l_size := l_new_reconfig_mr_id_tbl.count - 1;
1155 l_new_recon_mr_id_tbl.EXTEND(l_size,1);
1156
1157 IF G_DEBUG = 'Y' THEN
1158 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::After extending size of reconfig mrs:::::'|| l_new_recon_mr_id_tbl.count);
1159 END IF;
1160
1161 FOR indx IN l_new_reconfig_mr_id_tbl.FIRST..l_new_reconfig_mr_id_tbl.LAST
1162 LOOP
1163 l_new_recon_mr_id_tbl(j) := l_new_reconfig_mr_id_tbl(indx);
1164 j := j +1;
1165 END LOOP;
1166
1167 IF G_DEBUG = 'Y' THEN
1168 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Before NEW MR - OLD MR::MRs count:::::'|| l_new_recon_mr_id_tbl.count);
1169 END IF;
1170
1171 -- NEW Reconfig MR LIST - OLD MR headers from UE table = 'C'
1172 l_tmp_recon_mr_id_tbl := l_new_recon_mr_id_tbl MULTISET EXCEPT l_old_recon_mr_id_tbl;
1173
1174 IF G_DEBUG = 'Y' THEN
1175 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::After NEW MR - OLD MR::MRs count::for Create::Before Copy to Asso Table ::::'|| l_tmp_recon_mr_id_tbl.count);
1176 END IF;
1177
1178 j := 1;
1179 IF(l_tmp_recon_mr_id_tbl.count >0 )
1180 THEN
1181 FOR i IN l_tmp_recon_mr_id_tbl.FIRST..l_tmp_recon_mr_id_tbl.LAST
1182 LOOP
1183 l_mr_sr_assoc_tbl(j).mr_header_id := l_tmp_recon_mr_id_tbl(i);
1184 l_mr_sr_assoc_tbl(j).operation_flag := 'C';
1185 l_mr_sr_assoc_tbl(j).csi_instance_id := l_nr_task_rec.instance_id;
1186
1187 j := j +1;
1188 END LOOP;
1189 END IF;
1190
1191 IF G_DEBUG = 'Y' THEN
1192 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::After NEW MR - OLD MR::MRs count::After Copy to Asso Table :::'|| l_mr_sr_assoc_tbl.count);
1193 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Before OLD MR - NEW MR::MRs count::l_old_recon_mr_id_tbl::'|| l_old_recon_mr_id_tbl.count);
1194 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::before OLD MR - NEW MR::MRs count:::l_new_recon_mr_id_tbl :::'|| l_new_recon_mr_id_tbl.count);
1195 END IF;
1196
1197 IF G_DEBUG = 'Y' THEN
1198 IF(l_old_recon_mr_id_tbl.count > 0)
1199 THEN
1200 FOR i IN l_old_recon_mr_id_tbl.FIRST..l_old_recon_mr_id_tbl.LAST
1201 LOOP
1202 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::l_old_recon_mr_id_tbl('||i||').mr_header_id::' || l_old_recon_mr_id_tbl(i));
1203 END LOOP;
1204 END IF;
1205
1206 IF(l_new_recon_mr_id_tbl.count > 0)
1207 THEN
1208 FOR i IN l_new_recon_mr_id_tbl.FIRST..l_new_recon_mr_id_tbl.LAST
1209 LOOP
1210 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::l_new_recon_mr_id_tbl('||i||').mr_header_id::' || l_new_recon_mr_id_tbl(i));
1211 END LOOP;
1212 END IF;
1213 END IF;
1214
1215 -- clear the temporary table
1216 l_tmp_recon_mr_id_tbl.delete;
1217 -- OLD MR headers from UE table - NEW Reconfig MR LIST = 'D'
1218 l_tmp_recon_mr_id_tbl := l_old_recon_mr_id_tbl MULTISET EXCEPT l_new_recon_mr_id_tbl;
1219
1220 IF G_DEBUG = 'Y' THEN
1221 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::After OLD MR - NEW MR::MRs count::for Delete::Before Copy to Asso Table ::::'|| l_tmp_recon_mr_id_tbl.count);
1222 END IF;
1223
1224 IF(l_tmp_recon_mr_id_tbl.count >0 )
1225 THEN
1226 FOR i IN l_tmp_recon_mr_id_tbl.FIRST..l_tmp_recon_mr_id_tbl.LAST
1227 LOOP
1228 l_mr_sr_assoc_tbl(j).mr_header_id := l_tmp_recon_mr_id_tbl(i);
1229 l_mr_sr_assoc_tbl(j).operation_flag := 'D';
1230 l_mr_sr_assoc_tbl(j).object_version_number := l_reconfig_mr_ue_ovn(l_tmp_recon_mr_id_tbl(i));
1231 l_mr_sr_assoc_tbl(j).csi_instance_id := l_nr_task_rec.instance_id;
1232
1233 IF G_DEBUG = 'Y' THEN
1234 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::After OLD MR - NEW MR::MRs count::After Copy to Asso Table :::'|| l_mr_sr_assoc_tbl(j).mr_header_id);
1235 END IF;
1236
1237 j := j +1;
1238 END LOOP;
1239 END IF;
1240
1241 IF G_DEBUG = 'Y' THEN
1242 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Final MRs count for both Create and Delete::After Copy to Asso Table :::'|| l_mr_sr_assoc_tbl.count);
1243 END IF;
1244
1245 IF G_DEBUG = 'Y' THEN
1246 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || ':: Before Call to Process_SR_MR_Associations ' );
1247 END IF;
1248
1249 -- Call Process Associations
1250 IF(l_mr_sr_assoc_tbl.count >0)
1251 THEN
1252 /** Input Table Params to Process -MR Associations */
1253 IF G_DEBUG = 'Y' THEN
1254 FOR i IN l_mr_sr_assoc_tbl.FIRST..l_mr_sr_assoc_tbl.LAST
1255 LOOP
1256 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || ':: Association Table Parameter Values:: ' );
1257 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::l_mr_sr_assoc_tbl('||i||').mr_header_id::' || l_mr_sr_assoc_tbl(i).mr_header_id);
1258 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::l_mr_sr_assoc_tbl('||i||').operation_flag::' || l_mr_sr_assoc_tbl(i).operation_flag);
1259 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::l_mr_sr_assoc_tbl('||i||').object_version_number::' || l_mr_sr_assoc_tbl(i).object_version_number);
1260 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::l_mr_sr_assoc_tbl('||i||').csi_instance_id::' || l_mr_sr_assoc_tbl(i).csi_instance_id);
1261 END LOOP;
1262 END IF;
1263
1264 AHL_UMP_SR_PVT.Process_SR_MR_Associations
1265 (
1266 p_api_version => 1.0,
1267 p_init_msg_list => FND_API.G_FALSE,
1268 p_commit => FND_API.G_FALSE,
1269 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1270 x_return_status => l_return_status,
1271 x_msg_count => l_msg_count,
1272 x_msg_data => l_msg_data,
1273 p_user_id => fnd_global.user_id,
1274 p_login_id => fnd_global.login_id,
1275 p_request_id => l_nr_task_rec.incident_id,
1276 p_object_version_number => l_nr_task_rec.incident_object_version_number,
1277 p_request_number => null,
1278 p_x_sr_mr_association_tbl => l_mr_sr_assoc_tbl
1279 );
1280
1281 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1282 THEN
1283 IF G_DEBUG = 'Y' THEN
1284 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Call to AHL_UMP_NONROUTINES_PVT.Process_SR_MR_Associations is Failed' );
1285 END IF;
1286 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1287 END IF;
1288 END IF;
1289
1290 fnd_file.put_line(FND_FILE.LOG, 'SR Updated for Unit ::'||l_cur_item_instance_det.name);
1291 fnd_file.put_line(FND_FILE.LOG, 'SR Updated for Fleet ::'||l_fleet_unit_asso_rec.name);
1292
1293 -- If all the reconfiguaration MRs deleted Close/Cancel the SR
1294 ELSE
1295 --l_nr_task_rec.status_id := 1178; -- Status Id for Cancelled
1296 l_nr_task_rec.status_id := 2; -- Status Id for Closed
1297 IF G_DEBUG = 'Y' THEN
1298 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Close/Cancel_SR Input Params::' );
1299 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::incident_id::' || l_nr_task_rec.instance_id || '::incident_object_version_number::'|| l_nr_task_rec.incident_object_version_number);
1300 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::status_id::' || l_nr_task_rec.status_id );
1301 END IF;
1302
1303 -- Call process Associations and delete the associations for already created SR
1304 -- and then call update_st with closed status
1305 -- Get ALL the Mr_headers for alreadycreated SR
1306
1307 -- Clear the Tables
1308 l_temp_reconfig_mr_id_tbl.delete;
1309 l_temp_reconfig_mr_ue_ovn.delete;
1310
1311 --Populate New Data
1312 OPEN get_sr_mr_headers(l_nr_task_rec.incident_id);
1313 FETCH get_sr_mr_headers BULK COLLECT INTO l_temp_reconfig_mr_id_tbl, l_temp_reconfig_mr_ue_ovn;
1314 CLOSE get_sr_mr_headers;
1315
1316 IF G_DEBUG = 'Y' THEN
1317 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Already Added MRS Count to SR::'|| l_nr_task_rec.incident_id||':::'|| l_temp_reconfig_mr_id_tbl.count);
1318 END IF;
1319 -- Copy all the Reconfig MRS for which already SR is created
1320 IF(l_temp_reconfig_mr_id_tbl.count > 0)
1321 THEN
1322 FOR i IN l_temp_reconfig_mr_id_tbl.FIRST..l_temp_reconfig_mr_id_tbl.LAST
1323 LOOP
1324 l_mr_sr_assoc_tbl(i).mr_header_id := l_temp_reconfig_mr_id_tbl(i);
1325 l_mr_sr_assoc_tbl(i).operation_flag := 'D';
1326 l_mr_sr_assoc_tbl(i).object_version_number := l_temp_reconfig_mr_ue_ovn(i);
1327 l_mr_sr_assoc_tbl(i).csi_instance_id := l_nr_task_rec.instance_id;
1328 END LOOP;
1329 IF G_DEBUG = 'Y' THEN
1330 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::MRS list for delete associations to Close SR::'|| l_nr_task_rec.incident_id||':::'|| l_mr_sr_assoc_tbl.count);
1331 END IF;
1332 END IF;
1333
1334 -- Call Process Associations
1335 IF(l_mr_sr_assoc_tbl.count >0)
1336 THEN
1337 AHL_UMP_SR_PVT.Process_SR_MR_Associations
1338 (
1339 p_api_version => 1.0,
1340 p_init_msg_list => FND_API.G_FALSE,
1341 p_commit => FND_API.G_FALSE,
1342 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1343 x_return_status => l_return_status,
1344 x_msg_count => l_msg_count,
1345 x_msg_data => l_msg_data,
1346 p_user_id => fnd_global.user_id,
1347 p_login_id => fnd_global.login_id,
1348 p_request_id => l_nr_task_rec.incident_id,
1349 p_object_version_number => l_nr_task_rec.incident_object_version_number,
1350 p_request_number => null,
1351 p_x_sr_mr_association_tbl => l_mr_sr_assoc_tbl
1352 );
1353
1354 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1355 THEN
1356 IF G_DEBUG = 'Y' THEN
1357 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Call to AHL_UMP_NONROUTINES_PVT.Process_SR_MR_Associations is Failed' );
1358 END IF;
1359 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1360 END IF;
1361 END IF;
1362
1363 AHL_UMP_NONROUTINES_PVT.Update_SR
1364 (
1365 p_api_version => 1.0,
1366 p_init_msg_list => FND_API.G_TRUE,
1367 p_commit => FND_API.G_FALSE,
1368 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1369 p_default => FND_API.G_FALSE,
1370 p_module_type => p_module_type,
1371 x_return_status => l_return_status,
1372 x_msg_count => l_msg_count,
1373 x_msg_data => l_msg_data,
1374 p_x_nonroutine_rec => l_nr_task_rec
1375 );
1376
1377 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1378 THEN
1379 IF G_DEBUG = 'Y' THEN
1380 AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Call to AHL_UMP_NONROUTINES_PVT.Update_SR is Failed' );
1381 END IF;
1382 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1383 END IF;
1384
1385 -- Nullify reconfig_sr_id Fleet - Unit Association table
1386 -- Whenever SR is closed
1387 UPDATE ahl_fleet_unit_assocs
1388 SET reconfig_sr_id = NULL,
1389 object_version_number = object_version_number + 1,
1390 last_update_date = SYSDATE,
1391 last_updated_by = FND_GLOBAL.user_id,
1392 last_update_login = FND_GLOBAL.login_id
1393 WHERE fleet_unit_assoc_id = l_fleet_unit_asso_rec.fleet_unit_assoc_id
1394 AND object_version_number = l_fleet_unit_asso_rec.object_version_number;
1395
1396 IF (SQL%NOTFOUND)
1397 THEN
1398 FND_MESSAGE.set_name('AHL', 'AHL_FLT_UNIT_ASS_MOD');
1399 FND_MESSAGE.set_token('ASS_ID', l_fleet_unit_asso_rec.fleet_unit_assoc_id);
1400 FND_MSG_PUB.ADD;
1401 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1402 END IF;
1403
1404 fnd_file.put_line(FND_FILE.LOG, 'SR Closed for Unit ::'||l_cur_item_instance_det.name);
1405 fnd_file.put_line(FND_FILE.LOG, 'SR Closed for Fleet ::'||l_fleet_unit_asso_rec.name);
1406 END IF;
1407
1408 END IF;
1409 CLOSE is_sr_planned;
1410 END IF;
1411 EXCEPTION
1412 WHEN OTHERS THEN
1413
1414 fnd_file.put_line(FND_FILE.LOG, '**************************Warning Messages**********************');
1415 fnd_file.put_line(FND_FILE.LOG, 'Message Count '||FND_MSG_PUB.Count_Msg);
1416 -- Standard call to get message count.
1417 l_msg_count := FND_MSG_PUB.Count_Msg;
1418
1419 fnd_file.put_line(FND_FILE.LOG, 'Exception occured for Unit ::'||l_cur_item_instance_det.name);
1420 fnd_file.put_line(FND_FILE.LOG, 'Exception occured for Fleet ::'||l_fleet_unit_asso_rec.name);
1421
1422 FOR i IN 1..l_msg_count LOOP
1423 FND_MSG_PUB.get (
1424 p_msg_index => i,
1425 p_encoded => FND_API.G_FALSE,
1426 p_data => l_msg_data,
1427 p_msg_index_out => j );
1428
1429 fnd_file.put_line(FND_FILE.LOG, 'Err message-'||j||':' || l_msg_data);
1430 IF G_DEBUG = 'Y' THEN
1431 AHL_DEBUG_PUB.Debug('Err message-'||j||':' || substr(l_msg_data,1,240));
1432 END IF;
1433 END LOOP;
1434 fnd_file.put_line(FND_FILE.LOG, 'End of Warning messages');
1435
1436 END;
1437
1438
1439
1440 END LOOP; -- End for get_fleet_unit_asso
1441
1442 END IF;
1443
1444 -- Standard check of p_commit
1445 IF FND_API.TO_BOOLEAN(p_commit) THEN
1446 COMMIT WORK;
1447 END IF;
1448
1449 IF (G_DEBUG = 'Y') THEN
1450 AHL_DEBUG_PUB.debug('END - Successfully completion of '||G_PKG_NAME||'.'||l_api_name||' API ');
1451 END IF;
1452
1453 -- Count and Get messages
1454 FND_MSG_PUB.count_and_get
1455 ( p_encoded => fnd_api.g_false,
1456 p_count => x_msg_count,
1457 p_data => x_msg_data
1458 );
1459
1460 -- Disable debug (if enabled)
1461 IF (G_DEBUG = 'Y') THEN
1462 AHL_DEBUG_PUB.disable_debug;
1463 END IF;
1464
1465 EXCEPTION
1466 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1467 ROLLBACK TO process_reconfig_mrs_sp;
1468 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1469 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1470 p_count => x_msg_count,
1471 p_data => x_msg_data);
1472
1473 WHEN FND_API.G_EXC_ERROR THEN
1474 ROLLBACK TO process_reconfig_mrs_sp;
1475 X_return_status := FND_API.G_RET_STS_ERROR;
1476 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1477 p_count => x_msg_count,
1478 p_data => X_msg_data);
1479 WHEN OTHERS THEN
1480 ROLLBACK TO process_reconfig_mrs_sp;
1481 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1482
1483 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1484 THEN
1485 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_FLEET_FORECAST_PVT',
1486 p_procedure_name => 'PROCESS_RECONFIG_MRS',
1487 p_error_text => SUBSTR(SQLERRM,1,240));
1488 END IF;
1489 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1490 p_count => x_msg_count,
1491 p_data => X_msg_data);
1492
1493 END process_reconfig_mrs;
1494
1495 /*-----------------------------------------------------
1496 Procedure Name : process_reconfiguration_mrs
1497 Description : Concurrent Program will call this API to create Reconfigurations MRS.
1498 Input Parameters : Fleet Header ID (optional) and UC ID (optional)
1499 --------------------------------------------------------*/
1500
1501 PROCEDURE process_reconfiguration_mrs
1502 (
1503 errbuf OUT NOCOPY VARCHAR2,
1504 retcode OUT NOCOPY NUMBER,
1505 p_fleet_id IN NUMBER,
1506 p_unit_config_id IN NUMBER
1507 )
1508 AS
1509
1510 l_return_status VARCHAR2(1);
1511 l_msg_count NUMBER := 0;
1512
1513 BEGIN
1514
1515 --set the operating unit.
1516 mo_global.init('AHL');
1517
1518 -- Initialize error message stack by default
1519 FND_MSG_PUB.Initialize;
1520
1521 fnd_file.put_line(fnd_file.log, '*************API input parameters**************');
1522 fnd_file.put_line(fnd_file.log, 'API inputs p_fleet_id -> '||p_fleet_id);
1523 fnd_file.put_line(fnd_file.log, 'API inputs p_unit_config_id -> '||p_unit_config_id);
1524 fnd_file.put_line(fnd_file.log, '*************API input parameters**************');
1525
1526 process_reconfig_mrs
1527 (
1528 p_api_version => 1.0,
1529 p_init_msg_list => FND_API.G_TRUE,
1530 p_commit => FND_API.G_TRUE,
1531 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1532 p_module_type => 'CCP',
1533 x_return_status => l_return_status,
1534 x_msg_count => l_msg_count,
1535 x_msg_data => errbuf,
1536 p_fleet_id => p_fleet_id,
1537 p_unit_config_id => p_unit_config_id,
1538 p_fleet_unit_asso_id => NULL,
1539 p_delete_sr_flag => 'N'
1540 );
1541
1542 l_msg_count := FND_MSG_PUB.Count_Msg;
1543 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1544 THEN
1545 retcode := 2; -- error based only on return status
1546 ELSIF (l_msg_count > 0 AND l_return_status = FND_API.G_RET_STS_SUCCESS)
1547 THEN
1548 retcode := 1; -- warning based on return status + msg count
1549 ELSE
1550 retcode := 0;
1551 fnd_file.put_line(fnd_file.log, 'Reconfiguration MRS API executed successfully');
1552 END IF;
1553
1554
1555 END process_reconfiguration_mrs;
1556
1557
1558 END AHL_FLEET_FORECAST_PVT;
1559 ------------------------------------------- End of package AHL_FLEET_FORECAST_PVT-----------------------------------------------------------------------