[Home] [Help]
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';
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');
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
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
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
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: );
249:
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
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:
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
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: )
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
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
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
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');
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
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;