1 PACKAGE BODY "AHL_SIMULATION_PVT" AS
2 /* $Header: AHLVSIMB.pls 120.0.12020000.2 2012/12/07 14:39:07 sareepar noship $ */
3 -- Global CONSTANTS
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_SIMULATION_PVT';
5 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
6 ------------------------------------
7 -- Common constants and variables --
8 ------------------------------------
9 l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
13 l_log_unexpected NUMBER := fnd_log.level_unexpected;
10 l_log_statement NUMBER := fnd_log.level_statement;
11 l_log_procedure NUMBER := fnd_log.level_procedure;
12 l_log_error NUMBER := fnd_log.level_error;
14 -----------------------------------------------------------------
15 ------------------ Locking Exception ----------------------------
16 record_locked EXCEPTION;
17 pragma exception_init (record_locked, -54);
18
19 /*
20 * The procedure Delete_Simulation_Plan is called from the UI to delete a simulation plan's fleet-unit association selected
21 */
22 PROCEDURE Delete_Simulation_Plan
23 (
24 p_api_version IN NUMBER,
25 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
26 p_commit IN VARCHAR2 := Fnd_Api.G_TRUE,
27 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
28 p_module_type IN VARCHAR2 := NULL,
29 p_simulation_plan_id IN NUMBER,
30 x_return_status OUT NOCOPY VARCHAR2,
31 x_msg_count OUT NOCOPY NUMBER,
32 x_msg_data OUT NOCOPY VARCHAR2)
33 IS
34 -- Cursor for checking lock in ue table
35 CURSOR check_lock_ue_tbl(p_simulation_plan_id NUMBER)
36 IS
37 SELECT 1
38 FROM AHL_UE_SIMULATIONS
39 WHERE simulation_plan_id = p_simulation_plan_id FOR UPDATE;
40
41 -- Cursor for checking lock in unit assocs table
42 CURSOR check_lock_unit_assoc_tbl(p_simulation_plan_id NUMBER)
43 IS
44 SELECT 1
45 FROM AHL_FLEET_UNIT_ASSOCS
46 WHERE simulation_plan_id = p_simulation_plan_id FOR UPDATE;
47
48 --Standard local variables
49 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Simulation_Plan';
50 L_API_VERSION CONSTANT NUMBER := 1.0;
51 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
52 l_msg_data VARCHAR2(2000);
53 l_return_status VARCHAR2(1);
54 l_msg_count NUMBER;
55 l_locked_rows VARCHAR2(1):= 'N';
56
57 ---------------------------------------- Start of the procedure Delete_Simulation_Plan ----------------------------------------------------------------------
58 BEGIN
59 -- initialize return status.
60 x_return_status := FND_API.G_RET_STS_SUCCESS;
61 -- Standard start of API savepoint
62 SAVEPOINT Delete_Simulation_Plan;
63 -- Initialize message list if p_init_msg_list is set to TRUE
64 IF FND_API.To_Boolean(p_init_msg_list) THEN
65 FND_MSG_PUB.Initialize;
66 END IF;
67 -- Standard call to check for call compatibility.
68 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name,G_PKG_NAME) THEN
69 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
70 END IF;
71
72 -- Check for Required Parameters
73 IF(p_simulation_plan_id IS NULL) THEN
74 FND_MESSAGE.Set_Name('AHL','AHL_SIM_NULL_SIM_PLAN_ID');
75 FND_MSG_PUB.ADD;
76 IF (l_log_unexpected >= l_log_current_level) THEN
77 fnd_log.string ( l_log_unexpected, 'ahl.plsql.', 'For this API flow, Simulation Plan Id is mandatory but found null in input ' );
78 END IF;
79 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
80 END IF;
81
82 --Logging
83 IF (l_log_statement >= l_log_current_level) THEN
84 fnd_log.string(l_log_statement,L_DEBUG_KEY,'p_simulation_plan_id sent from UI: '||p_simulation_plan_id);
85 END IF;
86
87 -- Check for locks of the ue records
88 FOR i IN 1..2
89 LOOP
90 BEGIN
91 OPEN check_lock_ue_tbl(p_simulation_plan_id);
92 l_locked_rows := 'N';
93 CLOSE check_lock_ue_tbl;
94 -- lock is not there at this moment. So exit and perform update
95 EXIT;
96 EXCEPTION
97 WHEN record_locked THEN
101 END;
98 l_locked_rows := 'Y';
99 -- lock exists. Wait for 20 seconds and try again
100 DBMS_LOCK.SLEEP(10);
102 END LOOP;
103
104 -- Check for locked rows in unit assocs table
105 IF (l_locked_rows = 'N')
106 THEN
107 FOR i IN 1..2
108 LOOP
109 BEGIN
110 OPEN check_lock_unit_assoc_tbl(p_simulation_plan_id);
111 l_locked_rows := 'N';
112 CLOSE check_lock_unit_assoc_tbl;
113 -- No locking. Exit and do update
114 EXIT;
115 EXCEPTION
116 WHEN record_locked THEN
117 l_locked_rows := 'Y';
118 DBMS_LOCK.SLEEP(10);--sleep for 20 seconds
119 END;
120 END LOOP;
121 END IF;
122
123 -- Remove UEs when lock is not there on the records
124 IF (l_locked_rows = 'N') THEN
125 DELETE
126 FROM AHL_UE_SIMULATIONS
127 WHERE simulation_plan_id = p_simulation_plan_id;
128 ELSE
129 ROLLBACK TO Delete_Simulation_Plan;
130 FND_MESSAGE.Set_Name('AHL','AHL_SIM_ROWS_LOCKED');
131 FND_MSG_PUB.ADD;
132 RAISE FND_API.G_EXC_ERROR;
133 END IF;
134 -- End of processing for Sim Ues
135
136 -- Start processing fleet unit assocs
137 -- Remove unit and simulation association
138 IF (l_locked_rows = 'N') THEN
139 DELETE
140 FROM AHL_FLEET_UNIT_ASSOCS
141 WHERE simulation_plan_id = p_simulation_plan_id;
142 ELSE
143 ROLLBACK TO Delete_Simulation_Plan;
144 FND_MESSAGE.Set_Name('AHL','AHL_SIM_ROWS_LOCKED');
145 FND_MSG_PUB.ADD;
146 RAISE FND_API.G_EXC_ERROR;
147 END IF;
148 -- End of processing for Fleet unit association
149
150 -- Deletion of simulation plan should also remove the UMP BOM entries for the plan
151 IF (l_locked_rows = 'N') THEN
152 DELETE
153 FROM ahl_ump_maint_reqmnts
154 WHERE simulation_plan_id = p_simulation_plan_id
155 AND object_type = 'SIM';
156
157 DELETE
158 FROM ahl_ump_resource_reqmnts
159 WHERE simulation_plan_id = p_simulation_plan_id
160 AND object_type = 'SIM';
161
162 DELETE
163 FROM ahl_ump_material_reqmnts
164 WHERE simulation_plan_id = p_simulation_plan_id
165 AND object_type = 'SIM';
166 ELSE
167 ROLLBACK TO Delete_Simulation_Plan;
168 FND_MESSAGE.Set_Name('AHL','AHL_SIM_ROWS_LOCKED');
169 FND_MSG_PUB.ADD;
170 RAISE FND_API.G_EXC_ERROR;
171 END IF;
172
173 -- Standard check of p_commit
174 IF FND_API.TO_BOOLEAN(p_commit) THEN
175 COMMIT;
176 END IF;
177
178 -- Logging
179 IF (l_log_procedure >= l_log_current_level) THEN
180 fnd_log.string ( l_log_procedure,L_DEBUG_KEY ||'.end','At the end of PLSQL procedure Delete_Simulation_Plan');
181 END IF;
182
183 EXCEPTION
184 WHEN FND_API.G_EXC_ERROR THEN
185 x_return_status := FND_API.G_RET_STS_ERROR;
186 ROLLBACK TO Delete_Simulation_Plan;
187 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
188 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
189 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
190 ROLLBACK TO Delete_Simulation_Plan;
191 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
192 WHEN OTHERS THEN
193 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
194 ROLLBACK TO Delete_Simulation_Plan;
195 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
196 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME, p_procedure_name => 'Delete_Simulation_Plan', p_error_text => SUBSTR(SQLERRM,1,500));
197 END IF;
198 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
199 END Delete_Simulation_Plan;
200
201 ----------------------------------------- End of the procedure Delete_Simulation_Plan---------------------------------------------------------------------
202
203
204 /*
205 * The procedure Implement_Simulation_Plan is called from the UI to implement a simulation plan selected
206 */
207 PROCEDURE Implement_Simulation_Plan
208 (
209 p_api_version IN NUMBER,
210 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
211 p_commit IN VARCHAR2 := Fnd_Api.G_TRUE,
212 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
213 p_module_type IN VARCHAR2 := NULL,
214 p_simulation_plan_id IN NUMBER,
215 p_run_bue_flag IN VARCHAR2 := 'N',
216 x_return_status OUT NOCOPY VARCHAR2,
217 x_msg_count OUT NOCOPY NUMBER,
218 x_msg_data OUT NOCOPY VARCHAR2)
219 IS
220 -- Cursor for checking lock in Ue table
221 CURSOR check_lock_ue_tbl(p_simulation_plan_id NUMBER)
222 IS
223 SELECT 1
224 FROM AHL_UE_SIMULATIONS
225 WHERE simulation_plan_id = p_simulation_plan_id FOR UPDATE;
226
227 -- Cursor for checking lock in Ue table
228 CURSOR check_lock_unit_assoc_tbl(p_simulation_plan_id NUMBER)
229 IS
230 SELECT 1
231 FROM AHL_FLEET_UNIT_ASSOCS
232 WHERE simulation_plan_id = p_simulation_plan_id FOR UPDATE;
233
234 -- Added by debadey for bug 13869885
235 -- Get the associations which needs to be end dated in primary plan
236 CURSOR primary_assoc_for_upd(p_simulation_plan_id NUMBER, p_pri_plan_id NUMBER)
237 IS
238 SELECT fleet_unit_assoc_id
239 FROM AHL_FLEET_UNIT_ASSOCS
240 WHERE NVL(trunc(association_end), trunc(SYSDATE)) >= trunc(SYSDATE)
241 AND simulation_plan_id = p_pri_plan_id
242 AND association_start IS NOT NULL
243 AND unit_config_header_id IN
244 (SELECT DISTINCT unit_config_header_id
245 FROM AHL_FLEET_UNIT_ASSOCS
249
246 WHERE SIMULATION_PLAN_ID= p_simulation_plan_id
247 AND ahl_util_uc_pkg.get_uc_status_code(unit_config_header_id) <>'EXPIRED'-- added to avoid expired units
248 );
250 -- Get the associations which needs to be start dated correctly for the simulation plans
251 CURSOR sim_assoc_for_upd(p_simulation_plan_id NUMBER)
252 IS
253 SELECT fleet_unit_assoc_id
254 FROM AHL_FLEET_UNIT_ASSOCS
255 WHERE trunc(association_start) < trunc(SYSDATE)
256 AND NVL(trunc(association_end), trunc(SYSDATE)) >= trunc(SYSDATE)
257 AND NOT (association_start IS NULL AND association_end IS NULL)
258 AND simulation_plan_id = p_simulation_plan_id
259 AND ahl_util_uc_pkg.get_uc_status_code(unit_config_header_id) <>'EXPIRED';
260
261 -- End of changes for bug 13869885
262
263 --Standard local variables
264 L_API_NAME CONSTANT VARCHAR2(30) := 'Implement_Simulation_Plan';
265 L_API_VERSION CONSTANT NUMBER := 1.0;
266 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
267 l_msg_data VARCHAR2(2000);
268 l_return_status VARCHAR2(1);
269 l_msg_count NUMBER;
270 l_req_id NUMBER;
271
272 -- Variables specific to the API
273 l_primary_plan_id NUMBER;
274 l_locked_rows VARCHAR2(1):= 'N';
275
276 TYPE vchr_table
277 IS
278 TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
279
280 TYPE nbr_table
281 IS
282 TABLE OF NUMBER INDEX BY BINARY_INTEGER;
283 unit_config_header_tbl nbr_table;
284
285 ------------------------------------------- Start of the procedure Implement_Simulation_Plan -------------------------------------------------------
286 BEGIN
287 -- initialize return status.
288 x_return_status := FND_API.G_RET_STS_SUCCESS;
289 -- Standard start of API savepoint
290 SAVEPOINT Implement_Simulation_Plan;
291 -- Initialize message list if p_init_msg_list is set to TRUE
292 IF FND_API.To_Boolean(p_init_msg_list) THEN
293 FND_MSG_PUB.Initialize;
294 END IF;
295 -- Standard call to check for call compatibility.
296 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name,G_PKG_NAME) THEN
297 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
298 END IF;
299
300 -- Check for Required Parameters
301 IF(p_simulation_plan_id IS NULL) THEN
302 FND_MESSAGE.Set_Name('AHL','AHL_SIM_NULL_SIM_PLAN_ID');
303 FND_MSG_PUB.ADD;
304
305 -- Logging
306 IF (l_log_unexpected >= l_log_current_level) THEN
307 fnd_log.string ( l_log_unexpected, 'ahl.plsql.Implement_Simulation_Plan', 'For this API flow, Simulation Plan Id is mandatory but found null in input ' );
308 END IF;
309 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
310 END IF;
311
312 -- Logging
313 IF (l_log_statement >= l_log_current_level) THEN
314 fnd_log.string(l_log_statement,L_DEBUG_KEY,'p_simulation_plan_id sent from UI: '||p_simulation_plan_id||' p_run_bue_flag: '||p_run_bue_flag);
315 END IF;
316
317 -- Logic
318 -- Reset variable
319 IF (unit_config_header_tbl IS NOT NULL)
320 THEN
321 unit_config_header_tbl.DELETE;
322 END IF;
323
324 BEGIN
325 SELECT simulation_plan_id
326 INTO l_primary_plan_id
327 FROM AHL_SIMULATION_PLANS_B
328 WHERE PRIMARY_PLAN_FLAG='Y'
329 AND simulation_type = 'UMP';
330 EXCEPTION
331 WHEN NO_DATA_FOUND THEN
332 IF (l_log_unexpected >= l_log_current_level) THEN
333 fnd_log.string ( l_log_unexpected, 'ahl.plsql.Implement_Simulation_Plan', 'No primary plan data' );
334 END IF;
335 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
336 END;
340 -- Check for locked rows in unit assocs table
337
338 --Remove the unit - plan association for the primary plan for the relevant units
339 IF (l_primary_plan_id IS NOT NULL) THEN
341 FOR i IN 1..2
342 LOOP
343 BEGIN
344 OPEN check_lock_unit_assoc_tbl(p_simulation_plan_id);
345 l_locked_rows := 'N';
346 CLOSE check_lock_unit_assoc_tbl;
347 EXIT;
348 EXCEPTION
349 WHEN record_locked THEN
350 l_locked_rows := 'Y';
351 DBMS_LOCK.SLEEP(10);--sleep for 20 seconds
352 END;
353 END LOOP;
354
355 -- Check for locks of the ue records
356 IF (l_locked_rows = 'N')
357 THEN
358 FOR i IN 1..2
359 LOOP
360 BEGIN
361 OPEN check_lock_ue_tbl(p_simulation_plan_id);
362 l_locked_rows := 'N';
363 CLOSE check_lock_ue_tbl;
364 EXIT;
365 EXCEPTION
366 WHEN record_locked THEN
370 END LOOP;
367 l_locked_rows := 'Y';
368 DBMS_LOCK.SLEEP(10);--sleep for 20 seconds
369 END;
371 END IF;
372
373 IF (l_locked_rows = 'N') THEN
374
375 -- Populate the units in temp variable for BUE
376 BEGIN
377 SELECT DISTINCT unit_config_header_id
378 BULK COLLECT
379 INTO unit_config_header_tbl
380 FROM AHL_FLEET_UNIT_ASSOCS ua
381 WHERE ua.simulation_plan_id = p_simulation_plan_id
382 AND ahl_util_uc_pkg.get_uc_status_code(ua.unit_config_header_id) <>'EXPIRED'-- added to avoid expired units;
383 AND NOT (association_start IS NULL AND association_end IS NULL);
384
385
386
387 EXCEPTION
388 WHEN OTHERS
389 THEN
390 IF (l_log_unexpected >= l_log_current_level) THEN
391 fnd_log.string ( l_log_unexpected,L_DEBUG_KEY, 'No Unit in the plan');
392 END IF;
393 END;
394
395 -- Remove unit and simulation association for pri plan for those associations where start date is greater than sysdate
396 DELETE
397 FROM AHL_FLEET_UNIT_ASSOCS
398 WHERE simulation_plan_id = l_primary_plan_id
399 AND UNIT_CONFIG_HEADER_ID IN
400 (SELECT DISTINCT unit_config_header_id
401 FROM AHL_FLEET_UNIT_ASSOCS
402 WHERE simulation_plan_id= p_simulation_plan_id
403 AND ahl_util_uc_pkg.get_uc_status_code(UNIT_CONFIG_HEADER_ID) <>'EXPIRED'
404 AND NOT (association_start IS NULL AND association_end IS NULL)-- added to avoid expired units
405 )
406 --Added by debadey for bug 13869885
407 AND trunc(association_start)>=trunc(SYSDATE);
408 IF (l_log_unexpected >= l_log_current_level) THEN
409 fnd_log.string ( l_log_unexpected, 'ahl.plsql.Implement_Simulation_Plan', 'Deleted applicable unit assoc from primary plan ');
410 END IF;
411
412 -- Update applicable rows for the primary plan
413 FOR rec in primary_assoc_for_upd(p_simulation_plan_id, l_primary_plan_id)
414 LOOP
415 UPDATE AHL_FLEET_UNIT_ASSOCS
416 SET association_end = SYSDATE-1,
417 last_update_date = SYSDATE,
418 last_updated_by = fnd_global.user_id,
419 last_update_login = fnd_global.login_id
420 WHERE fleet_unit_assoc_id = rec.fleet_unit_assoc_id
421 AND simulation_plan_id = l_primary_plan_id;
422 IF (l_log_unexpected >= l_log_current_level) THEN
423 fnd_log.string ( l_log_unexpected, 'ahl.plsql.Implement_Simulation_Plan', 'Updated end date for primary plan fleet assoc id '||rec.fleet_unit_assoc_id );
424 END IF;
425 END LOOP;
426
427 -- Delete simulation plan rows where end date is less than sysdate
428 DELETE FROM AHL_FLEET_UNIT_ASSOCS
429 WHERE simulation_plan_id = p_simulation_plan_id
430 AND NVL(trunc(association_end), trunc(SYSDATE)) < trunc(SYSDATE);
431 IF (l_log_unexpected >= l_log_current_level) THEN
432 fnd_log.string ( l_log_unexpected, 'ahl.plsql.Implement_Simulation_Plan', 'Deleted applicable unit assoc from sim plan ');
433 END IF;
434 -- Update sim plan assoc to syadte
435 FOR rec in sim_assoc_for_upd(p_simulation_plan_id)
436 LOOP
437 UPDATE AHL_FLEET_UNIT_ASSOCS
438 SET association_start = SYSDATE,
439 last_update_date = SYSDATE,
440 last_updated_by = fnd_global.user_id,
441 last_update_login = fnd_global.login_id
442 WHERE simulation_plan_id = p_simulation_plan_id
443 AND fleet_unit_assoc_id = rec.fleet_unit_assoc_id;
444 IF (l_log_unexpected >= l_log_current_level) THEN
445 fnd_log.string ( l_log_unexpected, 'ahl.plsql.Implement_Simulation_Plan', 'Updated assoc start for sim plan for assoc id '||rec.fleet_unit_assoc_id);
446 END IF;
447 END LOOP;
448
449 -- convert the sim plan unit rows into primary plan rows
450 UPDATE AHL_FLEET_UNIT_ASSOCS assoc
451 SET SIMULATION_PLAN_ID =l_primary_plan_id,
452 last_update_date = SYSDATE,
453 last_updated_by = fnd_global.login_id
454 WHERE SIMULATION_PLAN_ID= p_simulation_plan_id
455 AND ahl_util_uc_pkg.get_uc_status_code(assoc.unit_config_header_id) <>'EXPIRED'-- added to avoid expired units;
456 AND NOT (association_start IS NULL AND association_end IS NULL);
457 IF (l_log_statement >= l_log_current_level) THEN
458 fnd_log.string(l_log_statement,L_DEBUG_KEY,'Updates mades in assocs table from sim to pri');
459 END IF;
460
461 -- delete any remaining assocs
462 DELETE FROM AHL_FLEET_UNIT_ASSOCS
463 WHERE SIMULATION_PLAN_ID= p_simulation_plan_id;
464
465 IF (l_log_statement >= l_log_current_level) THEN
466 fnd_log.string(l_log_statement,L_DEBUG_KEY,'Deleted remaining records from assocs table');
467 END IF;
468 ELSE
469 -- Already lock exists on the rows
470 ROLLBACK TO Implement_Simulation_Plan;
471 FND_MESSAGE.Set_Name('AHL','AHL_SIM_ROWS_LOCKED');
472 FND_MSG_PUB.ADD;
473 RAISE FND_API.G_EXC_ERROR;
474 END IF;
475 -- End of processing fleet unit assocs rows
476
477 -- Start processing UEs
478
479 -- Remove Ues in case no lock is there
480 IF (l_locked_rows = 'N') THEN
481
482 -- Delete the simulation plan ues
483 DELETE
484 FROM AHL_UE_SIMULATIONS
485 WHERE simulation_plan_id = p_simulation_plan_id;
486
487 -- Logging
488 IF (l_log_statement >= l_log_current_level) THEN
489 fnd_log.string(l_log_statement,L_DEBUG_KEY,'Updates mades in ue table');
490 END IF;
491 ELSE
492 ROLLBACK TO Implement_Simulation_Plan;
493 FND_MESSAGE.Set_Name('AHL','AHL_SIM_ROWS_LOCKED');
494 FND_MSG_PUB.ADD;
495 RAISE FND_API.G_EXC_ERROR;
499
496 END IF;
497 END IF;
498 -- End of processing UE rows
500 -- Standard check of p_commit
501 IF FND_API.TO_BOOLEAN(p_commit) THEN
505 IF (l_log_statement >= l_log_current_level) THEN
502 COMMIT;
503 SAVEPOINT Implement_Simulation_Plan;
504 --Logging
506 fnd_log.string(l_log_statement,L_DEBUG_KEY,'Commit done');
507 END IF;
508 END IF;
509
510 -- Invoke BUE for primary plan if user has opted for the same
511 IF (UPPER(p_run_bue_flag) = 'Y') THEN
512 IF(unit_config_header_tbl IS NOT NULL AND unit_config_header_tbl.COUNT>0) THEN
513 -- Logging
514 IF (l_log_statement >= l_log_current_level) THEN
515 fnd_log.string(l_log_statement,L_DEBUG_KEY,'Invoke BUE for the units. Unit number ='||unit_config_header_tbl.COUNT);
516 END IF;
517 FOR i IN unit_config_header_tbl.FIRST..unit_config_header_tbl.LAST
518 LOOP
519 IF (l_log_statement >= l_log_current_level) THEN
520 fnd_log.string(l_log_statement,L_DEBUG_KEY,'Invoking BUE for unit '||unit_config_header_tbl(i));
521 END IF;
522 BEGIN
523 l_req_id := fnd_request.submit_request('AHL','AHLUEFF',NULL,NULL,FALSE, NULL, unit_config_header_tbl(i), NULL, NULL, NULL, NULL);
524 EXCEPTION
525 WHEN OTHERS THEN
526 -- Even if exception is there that is acceptable. BUE will be run for primary plan frequently
527 -- But log the issue
528 IF (l_log_unexpected >= l_log_current_level) THEN
529 fnd_log.string ( l_log_unexpected,L_DEBUG_KEY, 'Exception while invoking BUE for primary plan.');
530 END IF;
531 END;
532
533 -- Logging
534 IF (l_log_statement >= l_log_current_level) THEN
535 fnd_log.string(l_log_statement, L_DEBUG_KEY, 'Request id generated by concurrent program is '||l_req_id);
536 END IF;
537
538 END LOOP;
539 END IF;
540 END IF;
541
542 -- Logging
543 IF (l_log_procedure >= l_log_current_level) THEN
544 fnd_log.string ( l_log_procedure,L_DEBUG_KEY ||'.end','At the end of PLSQL procedure Implement_Simulation_Plan');
545 END IF;
546 EXCEPTION
547 WHEN FND_API.G_EXC_ERROR THEN
548 x_return_status := FND_API.G_RET_STS_ERROR;
549 ROLLBACK TO Implement_Simulation_Plan;
550 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
551 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
552 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
553 ROLLBACK TO Implement_Simulation_Plan;
554 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
555 WHEN OTHERS THEN
556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
557 ROLLBACK TO Implement_Simulation_Plan;
558 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
562 END Implement_Simulation_Plan;
559 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME, p_procedure_name => 'Implement_Simulation_Plan', p_error_text => SUBSTR(SQLERRM,1,500));
560 END IF;
561 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
563
564 -------------------------------------------------------- End of the procedure Implement_Simulation_Plan -----------------------------------------------------
565
566
567 /*
568 * The procedure Remove_Unit_From_Plan is called from the UI to remove a unit from a plan and to remove associated UEs
569 */
570 PROCEDURE Remove_Unit_From_Plan
571 (
572 p_api_version IN NUMBER,
573 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
574 p_commit IN VARCHAR2 := Fnd_Api.G_TRUE,
575 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
576 p_module_type IN VARCHAR2 := NULL,
577 p_simulation_ue_id IN NUMBER,
578 x_return_status OUT NOCOPY VARCHAR2,
579 x_msg_count OUT NOCOPY NUMBER,
580 x_msg_data OUT NOCOPY VARCHAR2)
581 IS
582 --Lock checking cursor for ue records
583 CURSOR check_lock_ue_tbl(p_simulation_plan_id NUMBER, p_unit_header_id NUMBER)
584 IS
585 SELECT 1
586 FROM AHL_UE_SIMULATIONS
587 WHERE simulation_plan_id = p_simulation_plan_id FOR UPDATE;
588
589 --Lock checking cursor for unit assocs records
590 CURSOR check_lock_unit_assoc_tbl(p_simulation_plan_id NUMBER, p_unit_header_id NUMBER)
591 IS
592 SELECT 1
593 FROM AHL_FLEET_UNIT_ASSOCS
594 WHERE simulation_plan_id = p_simulation_plan_id
595 AND unit_config_header_id = p_unit_header_id FOR UPDATE;
596
597 --Standard local variables
598 L_API_NAME CONSTANT VARCHAR2(30) := 'Remove_Unit_From_Plan';
599 L_API_VERSION CONSTANT NUMBER := 1.0;
600 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
601 l_msg_data VARCHAR2(2000);
602 l_return_status VARCHAR2(1);
603 l_msg_count NUMBER;
604
605 -- Variables specific to the API
606 l_sim_plan_id NUMBER;
607 l_unit_config_header_id NUMBER;
608 l_locked_rows VARCHAR2(1) := 'N';
609
610 --------------------------------------------------- Start of procedure Remove_Unit_From_Plan ------------------------------------------------------------------
611 BEGIN
612 -- initialize return status.
613 x_return_status := FND_API.G_RET_STS_SUCCESS;
614 -- Standard start of API savepoint
615 SAVEPOINT Remove_Unit_From_Plan;
616 -- Initialize message list if p_init_msg_list is set to TRUE
617 IF FND_API.To_Boolean(p_init_msg_list) THEN
618 FND_MSG_PUB.Initialize;
619 END IF;
620 -- Standard call to check for call compatibility.
621 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version, p_api_version, l_api_name,G_PKG_NAME) THEN
622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
623 END IF;
624
625 -- Check for Required Parameters
626 IF(p_simulation_ue_id IS NULL) THEN
627 FND_MESSAGE.Set_Name('AHL','AHL_SIM_NULL_SIM_PLAN_ID');
628 FND_MSG_PUB.ADD;
629 IF (l_log_unexpected >= l_log_current_level) THEN
630 fnd_log.string ( l_log_unexpected, 'ahl.plsql.Remove_Unit_From_Plan', 'For this API flow, Simulation Ue Id is mandatory but found null in input ' );
631 END IF;
632 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
633 END IF;
634
635 --Logging
636 IF (l_log_statement >= l_log_current_level) THEN
637 fnd_log.string(l_log_statement,L_DEBUG_KEY,'p_simulation_ue_id sent from UI: '||p_simulation_ue_id);
638 END IF;
639
640 -- Logic
641 BEGIN
642 SELECT simulation_plan_id,
643 unit_config_header_id
644 INTO l_sim_plan_id,
645 l_unit_config_header_id
646 FROM ahl_simulation_forecast_v
647 WHERE simulation_ue_id = p_simulation_ue_id;
648 EXCEPTION
649 WHEN NO_DATA_FOUND THEN
650 FND_MESSAGE.Set_Name('AHL','AHL_SIM_INVALID_SIM_UE_ID');
651 FND_MSG_PUB.ADD;
652 IF (l_log_unexpected >= l_log_current_level) THEN
656 END;
653 fnd_log.string ( l_log_unexpected, 'ahl.plsql.Remove_Unit_From_Plan', 'There are no rows for the selected simulation effectivity id' );
654 END IF;
655 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
657 IF (l_sim_plan_id IS NULL) THEN
658 FND_MESSAGE.Set_Name('AHL','AHL_SIM_INVALID_SIM_PLAN_ID');
659 FND_MSG_PUB.ADD;
660 IF (l_log_unexpected >= l_log_current_level)THEN
661 fnd_log.string ( l_log_unexpected, 'ahl.plsql.Remove_Unit_From_Plan', 'NULL Simulation Plan id ' );
662 END IF;
663 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
664 END IF;
665 IF (l_unit_config_header_id IS NULL) THEN
666 FND_MESSAGE.Set_Name('AHL','AHL_SIM_INVALID_UNIT_HEADER_ID');
667 FND_MSG_PUB.ADD;
668 IF (l_log_unexpected >= l_log_current_level) THEN
669 fnd_log.string ( l_log_unexpected, 'ahl.plsql.Remove_Unit_From_Plan', 'NULL Unit id ' );
670 END IF;
671 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
672 END IF;
673
674 -- Check for existing locks in assocs table records
675 FOR i IN 1..2
676 LOOP
677 BEGIN
678 OPEN check_lock_unit_assoc_tbl(l_sim_plan_id, l_unit_config_header_id);
679 l_locked_rows := 'N';
680 CLOSE check_lock_unit_assoc_tbl;
681 EXIT;
682 EXCEPTION
683 WHEN record_locked THEN
684 l_locked_rows := 'Y';
685 DBMS_LOCK.SLEEP(10);--sleep for 20 seconds
686 END;
687 END LOOP;
688
689 -- Check for locks of the ue records
690 IF (l_locked_rows = 'N')
691 THEN
692 FOR i IN 1..2
693 LOOP
694 BEGIN
695 OPEN check_lock_ue_tbl(l_sim_plan_id, l_unit_config_header_id);
696 l_locked_rows := 'N';
697 CLOSE check_lock_ue_tbl;
698 EXIT;
699 EXCEPTION
700 WHEN record_locked THEN
701 l_locked_rows := 'Y';
702 DBMS_LOCK.SLEEP(10);--sleep for 20 seconds
703 END;
704 END LOOP;
705 END IF;
706
707 -- Remove unit and simulation association
708 IF (l_locked_rows = 'N') THEN
709 DELETE
710 FROM AHL_FLEET_UNIT_ASSOCS
711 WHERE unit_config_header_id = l_unit_config_header_id
712 AND simulation_plan_id = l_sim_plan_id;
713 ELSE
714 ROLLBACK TO Remove_Unit_From_Plan;
715 FND_MESSAGE.Set_Name('AHL','AHL_SIM_ROWS_LOCKED');
716 FND_MSG_PUB.ADD;
717 RAISE FND_API.G_EXC_ERROR;
718 END IF;
719 -- Processed assocs table records
720 -- Remove ue records if no lock is there
721 IF (l_locked_rows = 'N') THEN
722 -- Remove UEs
723 DELETE
724 FROM AHL_UE_SIMULATIONS
725 WHERE unit_config_header_id = l_unit_config_header_id
726 AND simulation_plan_id = l_sim_plan_id;
727 ELSE
728 ROLLBACK TO Remove_Unit_From_Plan;
729 FND_MESSAGE.Set_Name('AHL','AHL_SIM_ROWS_LOCKED');
730 FND_MSG_PUB.ADD;
731 RAISE FND_API.G_EXC_ERROR;
732 END IF;
733 -- End of processing for ue records
734
735 -- Standard check of p_commit
736 IF FND_API.TO_BOOLEAN(p_commit) THEN
737 IF (l_log_procedure >= l_log_current_level) THEN
741 END IF;
738 fnd_log.string ( l_log_procedure,L_DEBUG_KEY,'Committing changes');
739 END IF;
740 COMMIT;
742 -- Logging
743 IF (l_log_procedure >= l_log_current_level) THEN
744 fnd_log.string ( l_log_procedure,L_DEBUG_KEY ||'.end','At the end of PLSQL procedure');
745 END IF;
746 EXCEPTION
747 WHEN FND_API.G_EXC_ERROR THEN
748 x_return_status := FND_API.G_RET_STS_ERROR;
749 ROLLBACK TO Remove_Unit_From_Plan;
750 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
751 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
752 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
753 ROLLBACK TO Remove_Unit_From_Plan;
754 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
755 WHEN OTHERS THEN
756 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
757 ROLLBACK TO Remove_Unit_From_Plan;
758 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
759 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME, p_procedure_name => 'Remove_Unit_From_Plan', p_error_text => SUBSTR(SQLERRM,1,500));
760 END IF;
761 FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
762 END Remove_Unit_From_Plan;
763 -------------------------------------------------------------- End of procedure Remove_Unit_From_Plan ----------------------------------------------------------
764
765 END Ahl_Simulation_Pvt;
766 -- End of package body Ahl_Simulation_Pvt
[Home] [Help]