eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AHL_FLEET_FORECAST_PVT SQL Statements

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

Line: 18


  * The procedure Remove_Fleet will be called to hard/soft delete fleets as applicable
  */
PROCEDURE Remove_Fleet
  (
    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_fleet_id         IN NUMBER,
    p_obj_version_no   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_fleet_id NUMBER)
  IS
    SELECT 1
    FROM AHL_UE_SIMULATIONS
    WHERE fleet_header_id = p_fleet_id FOR UPDATE;

Line: 42


    SELECT object_version_number
    FROM AHL_FLEET_HEADERS_B fhb
    WHERE fhb.fleet_header_id = p_fleet_id
    AND fhb.status_code       = 'DRAFT'
    AND fhb.completion_date  IS NULL
    AND NOT EXISTS
      (SELECT 1
      FROM AHL_MR_EFFECTIVITIES mef, AHL_MR_HEADERS_B mhb
      WHERE fhb.fleet_header_id = mef.fleet_header_id
	  AND mhb.mr_header_id = mef.mr_header_id
	  AND mhb.mr_status_code  NOT IN ('DRAFT')
      );

Line: 57


    SELECT object_version_number
	FROM AHL_FLEET_HEADERS_B fhb
    WHERE fhb.fleet_header_id = p_fleet_id
    AND EXISTS
      (SELECT 1
	   FROM AHL_MR_EFFECTIVITIES mef, AHL_MR_HEADERS_B mhb
	   WHERE fhb.fleet_header_id = mef.fleet_header_id
	   AND mhb.mr_header_id = mef.mr_header_id
	   AND mhb.mr_status_code = 'COMPLETE'
	  );

Line: 71


  SELECT fleet_unit_assoc_id
  FROM ahl_fleet_unit_assocs
  WHERE fleet_header_id = c_fleet_id;

Line: 143


      SELECT status_code,
        object_version_number
      INTO l_status,
        l_ovn
      FROM AHL_FLEET_HEADERS_B
      WHERE fleet_header_id = p_fleet_id;

Line: 158


    IF (l_status = 'DELETED') THEN
      -- The record is already deleted
      FND_MESSAGE.set_name('AHL','AHL_FLEET_RECORD_CHANGED');

Line: 163


        fnd_log.string ( l_log_unexpected, 'ahl.plsql.Remove_Fleet', 'The fleet status is already DELETED' );

Line: 174


        fnd_log.string ( l_log_statement, 'ahl.plsql.Remove_Fleet', 'Fleet to be hard deleted');

Line: 177


      DELETE
      FROM AHL_FLEET_HEADERS_B
      WHERE fleet_header_id = p_fleet_id;

Line: 181


      DELETE
      FROM AHL_FLEET_HEADERS_TL
      WHERE fleet_header_id = p_fleet_id;

Line: 185


      DELETE
      FROM AHL_FLEET_UNIT_ASSOCS
      WHERE fleet_header_id = p_fleet_id;

Line: 189


      DELETE
      FROM AHL_UE_SIMULATIONS
      WHERE fleet_header_id = p_fleet_id;

Line: 194


     /* UPDATE AHL_MR_EFFECTIVITIES
      SET fleet_header_id     = NULL,
        object_version_number = object_version_number + 1,
        last_update_date      = SYSDATE,
        last_updated_by       = FND_GLOBAL.user_id,
        last_update_login     = FND_GLOBAL.login_id
      WHERE fleet_header_id   = p_fleet_id;*/

Line: 201


	  -- Delete MR effectivities
	  DELETE
	  FROM AHL_MR_EFFECTIVITIES
	  WHERE fleet_header_id = p_fleet_id;

Line: 206


      DELETE
      FROM AHL_FLEET_DOWNTIMES
      WHERE fleet_header_id = p_fleet_id;

Line: 210


      DELETE
      FROM AHL_FLEET_UTILIZATION
      WHERE fleet_header_id = p_fleet_id;

Line: 214


      DELETE
      FROM AHL_FLEET_RECONFIG_MRS
      WHERE fleet_header_id = p_fleet_id;

Line: 218


        fnd_log.string ( l_log_statement, 'ahl.plsql.Remove_Fleet', 'Updates complete in if');

Line: 230


	  -- Fleet can not be deleted. Throw error
		FND_MESSAGE.Set_Name('AHL','AHL_FLEET_COMPL_MR_ASSOC');

Line: 234


		  fnd_log.string ( l_log_statement, 'ahl.plsql.', 'Fleet is associated to completed MR and hence can not be deleted ' );

Line: 238


	  -- Fleet needs to be soft deleted
      IF (l_log_statement >= l_log_current_level) THEN
        fnd_log.string ( l_log_statement, 'ahl.plsql.Remove_Fleet', 'Fleet to be soft deleted');

Line: 263


          p_delete_sr_flag    => 'Y'
        );

Line: 276


      UPDATE AHL_FLEET_HEADERS_B
      SET object_version_number = object_version_number + 1,
        status_code             = 'DELETED',
        last_update_date        = SYSDATE,
        last_updated_by         = FND_GLOBAL.user_id,
        last_update_login       = FND_GLOBAL.login_id
      WHERE fleet_header_id     = p_fleet_id
      AND object_version_number = p_obj_version_no;

Line: 289


          fnd_log.string ( l_log_unexpected, 'ahl.plsql.Remove_Fleet', 'The record has been already updated' );

Line: 294


      DELETE
      FROM AHL_UE_SIMULATIONS
      WHERE fleet_header_id = p_fleet_id;

Line: 298


      DELETE
      FROM AHL_FLEET_UNIT_ASSOCS
      WHERE fleet_header_id = p_fleet_id;

Line: 302


	  -- Delete the effectivity
	  DELETE
      FROM AHL_MR_EFFECTIVITIES
      WHERE fleet_header_id = p_fleet_id;

Line: 307


      /*UPDATE AHL_MR_EFFECTIVITIES
      SET fleet_header_id     = NULL,
        object_version_number = object_version_number + 1,
        last_update_date      = SYSDATE,
        last_updated_by       = FND_GLOBAL.user_id,
        last_update_login     = FND_GLOBAL.login_id
      WHERE fleet_header_id   = p_fleet_id;*/

Line: 316


      DELETE
      FROM AHL_FLEET_DOWNTIMES
      WHERE fleet_header_id = p_fleet_id;

Line: 320


      DELETE
      FROM AHL_FLEET_UTILIZATION
      WHERE fleet_header_id = p_fleet_id;

Line: 325


	  DELETE
      FROM AHL_FLEET_RECONFIG_MRS
      WHERE fleet_header_id = p_fleet_id;*/

Line: 329


        fnd_log.string ( l_log_statement, 'ahl.plsql.Remove_Fleet', 'Updates complete in else');

Line: 333


        fnd_log.string ( l_log_statement, 'ahl.plsql.Remove_Fleet', 'Updates complete');

Line: 384


Description        : This API create/delete/update Service Request based on Reconguration MRs associated to Fleet
Input Parameters : p_fleet_id             IN    NUMBER,
                            p_unit_config_id    IN    NUMBER,
                           p_fleet_unit_asso_id IN    NUMBER,
                           p_delete_sr_flag    IN   VARCHAR2 := 'N'
--------------------------------------------------------*/

PROCEDURE process_reconfig_mrs
(
  p_api_version        IN    NUMBER,
  p_init_msg_list      IN     VARCHAR2  := FND_API.G_TRUE,
  p_commit              IN     VARCHAR2  := FND_API.G_FALSE,
  p_validation_level  IN     NUMBER    := FND_API.G_VALID_LEVEL_FULL,
  p_module_type      IN     VARCHAR2,
  x_return_status     OUT   NOCOPY  VARCHAR2,
  x_msg_count         OUT   NOCOPY  NUMBER,
  x_msg_data          OUT   NOCOPY  VARCHAR2,
  p_fleet_id             IN    NUMBER,
  p_unit_config_id    IN    NUMBER,
  p_fleet_unit_asso_id IN    NUMBER,
  p_delete_sr_flag    IN   VARCHAR2 := 'N'
)
AS

  CURSOR is_fleet_valid(c_fleet_header_id NUMBER)
  IS
  SELECT 'X'
  FROM ahl_fleet_headers_b
  WHERE fleet_header_id = c_fleet_header_id;

Line: 416


  SELECT 'X'
  FROM  ahl_unit_config_headers
  WHERE unit_config_header_id = c_unit_config_header_id;

Line: 422


  SELECT flt.fleet_header_id, flt.name, flt.operations_type_code, fus.unit_config_header_id,
             fus.fleet_unit_assoc_id, fus.object_version_number, fus.reconfig_sr_id,
             fus.association_start, fus.association_end
  FROM ahl_fleet_unit_assocs fus, ahl_simulation_plans_b sim, ahl_fleet_headers_b flt
  WHERE sim.primary_plan_flag = 'Y'
  AND sim.simulation_type = 'UMP'
  AND flt.fleet_header_id = fus.fleet_header_id
  AND flt.status_code = 'COMPLETE'
  AND fus.simulation_plan_id = sim.simulation_plan_id
  AND fus.association_start >= SYSDATE
  AND flt.fleet_header_id = NVL(c_fleet_header_id,flt.fleet_header_id)
  AND fus.unit_config_header_id = NVL(c_unit_config_header_id,fus.unit_config_header_id)
  ORDER BY unit_config_header_id,ASSOCIATION_START;

Line: 439


  SELECT uch.csi_item_instance_id, csi.instance_number, csi.serial_number,
             csi.inventory_item_id , uch.name, csi.owner_party_id
  FROM ahl_unit_config_headers uch, csi_item_instances csi
  WHERE uch.unit_config_header_id = c_unit_config_header_id
  AND uch.csi_item_instance_id = csi.instance_id;

Line: 447


  SELECT frm.fleet_reconfig_mr_id, mrh.mr_header_id, frm.mr_title,
             frm.src_operations_type_code, frm.compliance_lead_time
  FROM ahl_fleet_reconfig_mrs frm, ahl_mr_headers_v mrh
  WHERE frm.fleet_header_id = c_fleet_header_id
  AND frm.mr_title = mrh.title
  AND trunc(nvl(mrh.effective_to, SYSDATE +1))>trunc(SYSDATE)
  AND mrh.mr_status_code = 'COMPLETE'
  AND NVL(frm.src_operations_type_code,c_src_operations_type_code)  = c_src_operations_type_code;

Line: 458


  SELECT mr_header_id
  FROM ahl_mr_headers_v
  WHERE trunc(nvl( effective_to, SYSDATE +1))>trunc(SYSDATE)
  AND mr_status_code = 'COMPLETE'
  AND title = c_mr_title;

Line: 466


  SELECT flt.operations_type_code
  FROM ahl_fleet_unit_assocs fus, ahl_simulation_plans_b sim, ahl_fleet_headers_b flt
  WHERE sim.primary_plan_flag = 'Y'
  AND sim.simulation_type = 'UMP'
  AND fus.simulation_plan_id = sim.simulation_plan_id
  AND flt.fleet_header_id = fus.fleet_header_id
  AND flt.completion_date IS NOT NULL
  AND fus.association_end < c_association_start
  AND fus.unit_config_header_id = c_unit_config_header_id
  AND ROWNUM = 1
  ORDER BY association_end DESC;

Line: 480


  SELECT 'X'
  FROM ahl_visit_tasks_b vt, ahl_unit_effectivities_b ue
  WHERE vt.status_code <> 'DELETED'
  AND vt.unit_effectivity_id = ue.unit_effectivity_id
  AND ue.cs_incident_id = c_cs_incident_id;

Line: 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
  FROM    cs_incidents_all_vl
  where incident_id = p_cs_incident_id;

Line: 494


  SELECT mr_header_id, object_version_number
  FROM  ahl_unit_effectivities_b
  WHERE unit_effectivity_id
         IN (
               SELECT related_ue_id
               FROM ahl_ue_relationships urs, ahl_unit_effectivities_b ueb
               WHERE urs.ue_id = ueb.unit_effectivity_id
               AND ueb.cs_incident_id = c_cs_incident_id
              );

Line: 506


  SELECT reconfig_sr_id, unit_config_header_id
  FROM ahl_fleet_unit_assocs
  WHERE reconfig_sr_id IS NOT NULL
  AND fleet_unit_assoc_id = c_fleet_unit_asso_id;

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

Line: 597


  IF(p_delete_sr_flag = 'Y' AND p_fleet_unit_asso_id IS NOT NULL)
  THEN

    OPEN get_sr_id(p_fleet_unit_asso_id);

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

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

Line: 699


          AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Before Call to AHL_UMP_NONROUTINES_PVT.Update_SR for Closing' );

Line: 702


        AHL_UMP_NONROUTINES_PVT.Update_SR
        (
          p_api_version          => 1.0,
          p_init_msg_list        => FND_API.G_TRUE,
          p_commit                => FND_API.G_FALSE,
          p_validation_level    => FND_API.G_VALID_LEVEL_FULL,
          p_default                => FND_API.G_FALSE,
          p_module_type        => p_module_type,
          x_return_status       => l_return_status,
          x_msg_count           => l_msg_count,
          x_msg_data            => l_msg_data,
          p_x_nonroutine_rec          => l_nr_task_rec
        );

Line: 719


            AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Call to AHL_UMP_NONROUTINES_PVT.Update_SR is Failed' );

Line: 791


        l_temp_reconfig_mr_id_tbl.delete;

Line: 792


        l_comp_lead_time_tbl.delete;

Line: 794


        l_mr_sr_assoc_tbl.delete;

Line: 814


        l_tmp_recon_mr_id_tbl.delete;

Line: 815


        l_new_recon_mr_id_tbl.delete;

Line: 816


        l_old_recon_mr_id_tbl.delete;

Line: 826


        l_new_reconfig_mr_id_tbl.delete;

Line: 837


            l_applicable_mr_tbl.delete;

Line: 1022


            UPDATE ahl_fleet_unit_assocs
            SET reconfig_sr_id = l_nr_task_rec.incident_id,
                  object_version_number = l_fleet_unit_asso_rec.object_version_number + 1,
                  last_update_date = SYSDATE,
                  last_updated_by = FND_GLOBAL.user_id,
                  last_update_login = FND_GLOBAL.login_id
            WHERE fleet_unit_assoc_id = l_fleet_unit_asso_rec.fleet_unit_assoc_id
            AND     object_version_number = l_fleet_unit_asso_rec.object_version_number;

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

Line: 1081


            l_temp_reconfig_mr_id_tbl.delete;

Line: 1082


            l_temp_reconfig_mr_ue_ovn.delete;

Line: 1090


                  AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Update_SR Input Params::' );

Line: 1099


                AHL_UMP_NONROUTINES_PVT.Update_SR
                (
                  p_api_version          => 1.0,
                  p_init_msg_list        => FND_API.G_TRUE,
                  p_commit                => FND_API.G_FALSE,
                  p_validation_level    => FND_API.G_VALID_LEVEL_FULL,
                  p_default                => FND_API.G_FALSE,
                  p_module_type        => p_module_type,
                  x_return_status       => l_return_status,
                  x_msg_count           => l_msg_count,
                  x_msg_data            => l_msg_data,
                  p_x_nonroutine_rec  => l_nr_task_rec
                );

Line: 1116


                    AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Call to AHL_UMP_NONROUTINES_PVT.Update_SR is Failed' );

Line: 1149


                l_old_recon_mr_id_tbl.delete;

Line: 1216


              l_tmp_recon_mr_id_tbl.delete;

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

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

Line: 1290


              fnd_file.put_line(FND_FILE.LOG, 'SR Updated for Unit ::'||l_cur_item_instance_det.name);

Line: 1291


              fnd_file.put_line(FND_FILE.LOG, 'SR Updated for Fleet ::'||l_fleet_unit_asso_rec.name);

Line: 1308


              l_temp_reconfig_mr_id_tbl.delete;

Line: 1309


              l_temp_reconfig_mr_ue_ovn.delete;

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

Line: 1363


              AHL_UMP_NONROUTINES_PVT.Update_SR
              (
                p_api_version          => 1.0,
                p_init_msg_list        => FND_API.G_TRUE,
                p_commit                => FND_API.G_FALSE,
                p_validation_level    => FND_API.G_VALID_LEVEL_FULL,
                p_default                => FND_API.G_FALSE,
                p_module_type        => p_module_type,
                x_return_status       => l_return_status,
                x_msg_count           => l_msg_count,
                x_msg_data            => l_msg_data,
                p_x_nonroutine_rec  => l_nr_task_rec
              );

Line: 1380


                  AHL_DEBUG_PUB.debug(G_PKG_NAME || '.' || l_api_name || '::Call to AHL_UMP_NONROUTINES_PVT.Update_SR is Failed' );

Line: 1387


              UPDATE ahl_fleet_unit_assocs
              SET reconfig_sr_id = NULL,
                    object_version_number = object_version_number + 1,
                    last_update_date = SYSDATE,
                    last_updated_by = FND_GLOBAL.user_id,
                    last_update_login = FND_GLOBAL.login_id
              WHERE fleet_unit_assoc_id = l_fleet_unit_asso_rec.fleet_unit_assoc_id
              AND     object_version_number = l_fleet_unit_asso_rec.object_version_number;

Line: 1539


    p_delete_sr_flag    => 'N'
  );