APPS.AHL_SIMULATION_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 20

  * The procedure Delete_Simulation_Plan is called from the UI to delete a simulation plan's fleet-unit association selected
  */
PROCEDURE Delete_Simulation_Plan
  (
    p_api_version        IN NUMBER,
    p_init_msg_list      IN VARCHAR2 := Fnd_Api.G_FALSE,
    p_commit             IN VARCHAR2 := Fnd_Api.G_TRUE,
    p_validation_level   IN NUMBER 	 := Fnd_Api.G_VALID_LEVEL_FULL,
    p_module_type        IN VARCHAR2 := NULL,
    p_simulation_plan_id IN NUMBER,
    x_return_status      OUT NOCOPY VARCHAR2,
    x_msg_count          OUT NOCOPY     NUMBER,
    x_msg_data           OUT NOCOPY      VARCHAR2)
IS
  -- Cursor for checking lock in ue table
  CURSOR check_lock_ue_tbl(p_simulation_plan_id NUMBER)
  IS
    SELECT 1
    FROM AHL_UE_SIMULATIONS
    WHERE simulation_plan_id = p_simulation_plan_id FOR UPDATE;

Line: 44

    SELECT 1
    FROM AHL_FLEET_UNIT_ASSOCS
    WHERE simulation_plan_id = p_simulation_plan_id FOR UPDATE;

Line: 49

  L_API_NAME      CONSTANT VARCHAR2(30)  := 'Delete_Simulation_Plan';

Line: 62

  SAVEPOINT Delete_Simulation_Plan;

Line: 113

		  -- No locking. Exit and do update
		  EXIT;

Line: 125

    DELETE
    FROM AHL_UE_SIMULATIONS
    WHERE simulation_plan_id = p_simulation_plan_id;

Line: 129

    ROLLBACK TO Delete_Simulation_Plan;

Line: 139

    DELETE
    FROM AHL_FLEET_UNIT_ASSOCS
    WHERE simulation_plan_id = p_simulation_plan_id;

Line: 143

    ROLLBACK TO Delete_Simulation_Plan;

Line: 152

    DELETE
    FROM ahl_ump_maint_reqmnts
    WHERE simulation_plan_id = p_simulation_plan_id
	AND object_type = 'SIM';

Line: 157

	DELETE
	FROM ahl_ump_resource_reqmnts
	WHERE simulation_plan_id = p_simulation_plan_id
	AND object_type = 'SIM';

Line: 162

	DELETE
	FROM ahl_ump_material_reqmnts
	WHERE simulation_plan_id = p_simulation_plan_id
	AND object_type = 'SIM';

Line: 167

    ROLLBACK TO Delete_Simulation_Plan;

Line: 180

    fnd_log.string ( l_log_procedure,L_DEBUG_KEY ||'.end','At the end of PLSQL procedure Delete_Simulation_Plan');

Line: 186

  ROLLBACK TO Delete_Simulation_Plan;

Line: 190

  ROLLBACK TO Delete_Simulation_Plan;

Line: 194

  ROLLBACK TO Delete_Simulation_Plan;

Line: 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));

Line: 199

END Delete_Simulation_Plan;

Line: 205

* The procedure Implement_Simulation_Plan is called from the UI to implement a simulation plan selected
*/
PROCEDURE Implement_Simulation_Plan
  (
    p_api_version        IN NUMBER,
    p_init_msg_list      IN VARCHAR2 := Fnd_Api.G_FALSE,
    p_commit             IN VARCHAR2 := Fnd_Api.G_TRUE,
    p_validation_level   IN NUMBER 	 := Fnd_Api.G_VALID_LEVEL_FULL,
    p_module_type        IN VARCHAR2 := NULL,
    p_simulation_plan_id IN NUMBER,
	p_run_bue_flag		 IN VARCHAR2 := 'N',
    x_return_status 	 OUT NOCOPY VARCHAR2,
    x_msg_count 	     OUT NOCOPY     NUMBER,
    x_msg_data           OUT NOCOPY      VARCHAR2)
IS
  -- Cursor for checking lock in Ue table
  CURSOR check_lock_ue_tbl(p_simulation_plan_id NUMBER)
  IS
    SELECT 1
    FROM AHL_UE_SIMULATIONS
    WHERE simulation_plan_id = p_simulation_plan_id FOR UPDATE;

Line: 230

    SELECT 1
    FROM AHL_FLEET_UNIT_ASSOCS
    WHERE simulation_plan_id = p_simulation_plan_id FOR UPDATE;

Line: 238

    SELECT fleet_unit_assoc_id
	FROM AHL_FLEET_UNIT_ASSOCS
	WHERE NVL(trunc(association_end), trunc(SYSDATE)) >= trunc(SYSDATE)
	AND simulation_plan_id = p_pri_plan_id
	AND association_start IS NOT NULL
	AND unit_config_header_id IN
        (SELECT DISTINCT unit_config_header_id
        FROM AHL_FLEET_UNIT_ASSOCS
        WHERE SIMULATION_PLAN_ID= p_simulation_plan_id
		AND ahl_util_uc_pkg.get_uc_status_code(unit_config_header_id) <>'EXPIRED'-- added to avoid expired units
        );

Line: 253

    SELECT fleet_unit_assoc_id
	FROM AHL_FLEET_UNIT_ASSOCS
	WHERE trunc(association_start) < trunc(SYSDATE)
	AND NVL(trunc(association_end), trunc(SYSDATE)) >= trunc(SYSDATE)
	AND NOT (association_start IS NULL AND association_end IS NULL)
	AND simulation_plan_id = p_simulation_plan_id
	AND ahl_util_uc_pkg.get_uc_status_code(unit_config_header_id) <>'EXPIRED';

Line: 321

	unit_config_header_tbl.DELETE;

Line: 325

    SELECT simulation_plan_id
    INTO l_primary_plan_id
    FROM AHL_SIMULATION_PLANS_B
    WHERE PRIMARY_PLAN_FLAG='Y'
	AND simulation_type = 'UMP';

Line: 377

		SELECT DISTINCT unit_config_header_id
		BULK COLLECT
		INTO unit_config_header_tbl
		FROM AHL_FLEET_UNIT_ASSOCS ua
		WHERE ua.simulation_plan_id = p_simulation_plan_id
		AND ahl_util_uc_pkg.get_uc_status_code(ua.unit_config_header_id) <>'EXPIRED'-- added to avoid expired units;

Line: 396

      DELETE
      FROM AHL_FLEET_UNIT_ASSOCS
      WHERE simulation_plan_id   = l_primary_plan_id
      AND UNIT_CONFIG_HEADER_ID IN
        (SELECT DISTINCT unit_config_header_id
        FROM AHL_FLEET_UNIT_ASSOCS
        WHERE simulation_plan_id= p_simulation_plan_id
		AND ahl_util_uc_pkg.get_uc_status_code(UNIT_CONFIG_HEADER_ID) <>'EXPIRED'
		AND NOT (association_start IS NULL AND association_end IS NULL)-- added to avoid expired units
        )
		--Added by debadey for bug 13869885
	  AND trunc(association_start)>=trunc(SYSDATE);

Line: 409

      fnd_log.string ( l_log_unexpected, 'ahl.plsql.Implement_Simulation_Plan', 'Deleted applicable unit assoc from primary plan ');

Line: 415

	  UPDATE AHL_FLEET_UNIT_ASSOCS
	  SET association_end = SYSDATE-1,
    last_update_date = SYSDATE,
    last_updated_by = fnd_global.user_id,
    last_update_login = fnd_global.login_id
    WHERE fleet_unit_assoc_id = rec.fleet_unit_assoc_id
	  AND simulation_plan_id = l_primary_plan_id;

Line: 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 );

Line: 427

	-- Delete simulation plan rows where end date is less than sysdate
	DELETE FROM AHL_FLEET_UNIT_ASSOCS
    WHERE simulation_plan_id = p_simulation_plan_id
	AND NVL(trunc(association_end), trunc(SYSDATE)) < trunc(SYSDATE);

Line: 432

      fnd_log.string ( l_log_unexpected, 'ahl.plsql.Implement_Simulation_Plan', 'Deleted applicable unit assoc from sim plan ');

Line: 437

	   UPDATE AHL_FLEET_UNIT_ASSOCS
	   SET association_start = SYSDATE,
     last_update_date = SYSDATE,
     last_updated_by = fnd_global.user_id,
     last_update_login = fnd_global.login_id
	   WHERE simulation_plan_id = p_simulation_plan_id
	   AND fleet_unit_assoc_id = rec.fleet_unit_assoc_id;

Line: 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);

Line: 450

      UPDATE AHL_FLEET_UNIT_ASSOCS assoc
      SET SIMULATION_PLAN_ID  =l_primary_plan_id,
          last_update_date = SYSDATE,
          last_updated_by = fnd_global.login_id
      WHERE SIMULATION_PLAN_ID= p_simulation_plan_id
	  AND ahl_util_uc_pkg.get_uc_status_code(assoc.unit_config_header_id) <>'EXPIRED'-- added to avoid expired units;

Line: 458

        fnd_log.string(l_log_statement,L_DEBUG_KEY,'Updates mades in assocs table from sim to pri');

Line: 461

	  -- delete any remaining assocs
	  DELETE FROM AHL_FLEET_UNIT_ASSOCS
	  WHERE SIMULATION_PLAN_ID= p_simulation_plan_id;

Line: 466

        fnd_log.string(l_log_statement,L_DEBUG_KEY,'Deleted remaining records from assocs table');

Line: 482

	  -- Delete the simulation plan ues
      DELETE
      FROM AHL_UE_SIMULATIONS
      WHERE simulation_plan_id = p_simulation_plan_id;

Line: 489

        fnd_log.string(l_log_statement,L_DEBUG_KEY,'Updates mades in ue table');

Line: 585

    SELECT 1
    FROM AHL_UE_SIMULATIONS
    WHERE simulation_plan_id = p_simulation_plan_id FOR UPDATE;

Line: 592

    SELECT 1
    FROM AHL_FLEET_UNIT_ASSOCS
    WHERE simulation_plan_id  = p_simulation_plan_id
    AND unit_config_header_id = p_unit_header_id FOR UPDATE;

Line: 642

    SELECT simulation_plan_id,
      unit_config_header_id
    INTO l_sim_plan_id,
      l_unit_config_header_id
    FROM ahl_simulation_forecast_v
    WHERE simulation_ue_id = p_simulation_ue_id;

Line: 653

      fnd_log.string ( l_log_unexpected, 'ahl.plsql.Remove_Unit_From_Plan', 'There are no rows for the selected simulation effectivity id' );

Line: 709

    DELETE
    FROM AHL_FLEET_UNIT_ASSOCS
    WHERE unit_config_header_id = l_unit_config_header_id
    AND simulation_plan_id      = l_sim_plan_id;

Line: 723

    DELETE
    FROM AHL_UE_SIMULATIONS
    WHERE unit_config_header_id = l_unit_config_header_id
    AND simulation_plan_id      = l_sim_plan_id;