APPS.JTF_LOC_AREAS_PVT SQL Statements

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

Line: 36

    SELECT JTF_LOC_AREAS_B_S.NEXTVAL
    FROM DUAL;

Line: 40

    SELECT COUNT(*)
    FROM JTF_LOC_AREAS_VL
    WHERE location_area_id = loc_area_id;

Line: 91

  JTF_Utility_PVT.debug_message(l_full_name || ': insert');

Line: 107

  INSERT INTO JTF_LOC_AREAS_B
  (
    location_area_id,
    last_update_date,
    last_updated_by,
    creation_date,
    created_by,
    object_version_number,
    last_update_login,
    request_id,
    program_application_id,
    program_id,
    program_update_date,
    location_type_code,
    start_date_active,
    end_date_active,
    location_area_code,
    orig_system_id,
    orig_system_ref,
    parent_location_area_id
  )
  VALUES
  (
    l_loc_area_rec.location_area_id,
    SYSDATE,
    FND_GLOBAL.user_id,
    SYSDATE,
    FND_GLOBAL.user_id,
    1,
    FND_GLOBAL.conc_login_id,
    l_loc_area_rec.request_id,
    l_loc_area_rec.program_application_id,
    l_loc_area_rec.program_id,
    l_loc_area_rec.program_update_date,
    l_loc_area_rec.location_type_code,
    l_loc_area_rec.start_date_active,
    l_loc_area_rec.end_date_active,
    l_loc_area_rec.location_area_code,
    l_loc_area_rec.orig_system_id,
    l_loc_area_rec.orig_system_ref,
    l_loc_area_rec.parent_location_area_id
  );

Line: 150

    INSERT INTO JTF_LOC_AREAS_TL
  (
    location_area_id,
    language,
    last_update_date,
    last_updated_by,
    creation_date,
    created_by,
    last_update_login,
    source_lang,
    location_area_name,
    location_area_description
  )
  SELECT
    l_loc_area_rec.location_area_id,
    l.language_code,
    SYSDATE,
    FND_GLOBAL.user_id,
    SYSDATE,
    FND_GLOBAL.user_id,
    FND_GLOBAL.conc_login_id,
    USERENV('LANG'),
    l_loc_area_rec.location_area_name,
    l_loc_area_rec.location_area_description
  FROM fnd_languages l
  WHERE l.installed_flag in ('I', 'B')
  AND NOT EXISTS
  (
    SELECT NULL
    FROM JTF_LOC_AREAS_TL t
    WHERE t.location_area_id = l_loc_area_rec.location_area_id
    AND t.language = l.language_code
  );

Line: 244

PROCEDURE update_loc_area
(
  p_api_version         IN      NUMBER,
  p_init_msg_list       IN      VARCHAR2 := FND_API.g_false,
  p_commit              IN      VARCHAR2 := FND_API.g_false,
  p_validation_level    IN      NUMBER   := FND_API.g_valid_level_full,

  x_return_status       OUT NOCOPY /* file.sql.39 change */     VARCHAR2,
  x_msg_count           OUT NOCOPY /* file.sql.39 change */     NUMBER,
  x_msg_data            OUT NOCOPY /* file.sql.39 change */     VARCHAR2,

  p_loc_area_rec        IN      loc_area_rec_type,
  p_remove_flag         IN      VARCHAR2 := 'N'
)
IS

  l_api_version      CONSTANT NUMBER := 1.0;

Line: 261

  l_api_name         CONSTANT VARCHAR2(30) := 'update_loc_area';

Line: 269

  SAVEPOINT update_loc_area;

Line: 305

        p_validation_mode => JTF_PLSQL_API.g_update,
        x_return_status   => l_return_status,
        p_loc_area_rec    => l_loc_area_rec
      );

Line: 336

  JTF_Utility_PVT.debug_message(l_full_name||': update');

Line: 342

  UPDATE JTF_LOC_AREAS_B SET
    last_update_date = SYSDATE,
    last_updated_by = FND_GLOBAL.user_id,
    object_version_number = l_loc_area_rec.object_version_number + 1,
    last_update_login = FND_GLOBAL.conc_login_id,
    request_id = l_loc_area_rec.request_id,
    program_application_id = l_loc_area_rec.program_application_id,
    program_id = l_loc_area_rec.program_id,
    program_update_date = l_loc_area_rec.program_update_date,
    location_type_code = l_loc_area_rec.location_type_code,
    start_date_active = l_loc_area_rec.start_date_active,
    end_date_active = l_loc_area_rec.end_date_active,
    location_area_code = l_loc_area_rec.location_area_code,
    orig_system_id = l_loc_area_rec.orig_system_id,
    orig_system_ref = l_loc_area_rec.orig_system_ref,
    parent_location_area_id = l_loc_area_rec.parent_location_area_id
  WHERE location_area_id = l_loc_area_rec.location_area_id
  AND object_version_number = l_loc_area_rec.object_version_number;

Line: 369

  UPDATE JTF_LOC_AREAS_TL SET
    last_update_date = SYSDATE,
    last_updated_by = FND_GLOBAL.user_id,
    last_update_login = FND_GLOBAL.conc_login_id,
    source_lang = USERENV('LANG'),
    location_area_name = l_loc_area_rec.location_area_name,
    location_area_description = l_loc_area_rec.location_area_description
  WHERE location_area_id = l_loc_area_rec.location_area_id
  AND USERENV('LANG') IN (language, source_lang);

Line: 404

      ROLLBACK TO update_loc_area;

Line: 414

      ROLLBACK TO update_loc_area;

Line: 424

      ROLLBACK TO update_loc_area;

Line: 436

END update_loc_area;

Line: 447

PROCEDURE delete_loc_area
(
  p_api_version         IN      NUMBER,
  P_init_msg_list       IN      VARCHAR2 := FND_API.g_false,
  p_commit              IN      VARCHAR2 := FND_API.g_false,

  x_return_status       OUT NOCOPY /* file.sql.39 change */     VARCHAR2,
  x_msg_count           OUT NOCOPY /* file.sql.39 change */     NUMBER,
  x_msg_data            OUT NOCOPY /* file.sql.39 change */     VARCHAR2,

  p_loc_area_id         IN      NUMBER,
  p_object_version      IN      NUMBER
)
IS

  l_api_version   CONSTANT NUMBER := 1.0;

Line: 463

  l_api_name      CONSTANT VARCHAR2(30) := 'delete_loc_area';

Line: 468

  SELECT 1
    FROM DUAL
   WHERE EXISTS(SELECT 1
                  FROM jtf_loc_areas_b
                 WHERE nvl(end_date_active,SYSDATE + 1) > SYSDATE
                   AND parent_location_area_id = l_id);

Line: 477

  SAVEPOINT delete_loc_area;

Line: 499

  JTF_Utility_PVT.debug_message(l_full_name || ': delete');

Line: 516

  DELETE FROM JTF_LOC_AREAS_TL
  WHERE location_area_id = p_loc_area_id;

Line: 527

  DELETE FROM JTF_LOC_AREAS_B
  WHERE location_area_id = p_loc_area_id
  AND object_version_number = p_object_version;

Line: 533

  UPDATE jtf_loc_areas_b
  SET    last_update_date = SYSDATE
        ,last_updated_by = FND_GLOBAL.user_id
        ,last_update_login = FND_GLOBAL.conc_login_id
        ,end_date_active = SYSDATE
        ,object_version_number = object_version_number + 1
  WHERE location_area_id = p_loc_area_id
  AND   object_version_number = p_object_version;

Line: 568

      ROLLBACK TO delete_loc_area;

Line: 578

      ROLLBACK TO delete_loc_area;

Line: 588

      ROLLBACK TO delete_loc_area;

Line: 600

END delete_loc_area;

Line: 629

    SELECT location_area_id
    FROM JTF_LOC_AREAS_B
    WHERE location_area_id = p_loc_area_id
    AND object_version_number = p_object_version
    FOR UPDATE OF location_area_id NOWAIT;

Line: 636

    SELECT location_area_id
    FROM JTF_LOC_AREAS_TL
    WHERE location_area_id = p_loc_area_id
    AND USERENV('LANG') IN (language, source_lang)
    FOR UPDATE OF location_area_id NOWAIT;

Line: 950

  SELECT 1
    FROM DUAL
   WHERE EXISTS(SELECT 1
                  FROM jtf_loc_areas_b
                 WHERE location_type_code = 'AREA1');

Line: 964

  AND p_validation_mode = JTF_PLSQL_API.g_update THEN
    IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
      FND_MESSAGE.set_name('JTF', 'JTF_LOC_AREA_NO_LOC_AREA_ID');

Line: 987

    AND p_validation_mode = JTF_PLSQL_API.g_update
  THEN
    IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
      FND_MESSAGE.set_name('JTF', 'JTF_API_NO_OBJ_VER_NUM');

Line: 1100

  SELECT 1
    FROM DUAL
   WHERE EXISTS(SELECT 1
                  FROM jtf_loc_areas_b b, jtf_loc_areas_tl t
                 WHERE t.location_area_name = l_name
                   AND t.language = USERENV('LANG')
                   AND t.location_area_id <> l_id
                   AND b.parent_location_area_id = l_parent_id
                   AND b.location_area_id =t.location_area_id);

Line: 1111

  SELECT 1
    FROM DUAL
   WHERE EXISTS(SELECT 1
                  FROM jtf_loc_areas_b b, jtf_loc_areas_tl t
                 WHERE t.location_area_name = l_name
                   AND t.language = USERENV('LANG')
                   AND b.parent_location_area_id = l_parent_id
                   AND b.location_area_id =t.location_area_id);

Line: 1328

    SELECT * FROM JTF_LOC_AREAS_VL
    WHERE location_area_id = p_loc_area_rec.location_area_id;

Line: 1361

  IF p_loc_area_rec.program_update_date = FND_API.g_miss_date THEN
    x_complete_rec.program_update_date := l_loc_area_rec.program_update_date;

Line: 1420

  x_loc_area_rec.last_update_date := FND_API.g_miss_date;

Line: 1421

  x_loc_area_rec.last_updated_by := FND_API.g_miss_num;

Line: 1424

  x_loc_area_rec.last_update_login := FND_API.g_miss_num;

Line: 1429

  x_loc_area_rec.program_update_date := FND_API.g_miss_date;