APPS.JTF_LOC_POSTAL_CODES_PVT SQL Statements

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

Line: 36

    SELECT JTF_LOC_POSTAL_CODES_S.NEXTVAL
    FROM DUAL;

Line: 40

    SELECT COUNT(*)
    FROM JTF_LOC_POSTAL_CODES
    WHERE location_postal_code_id = postal_code_id;

Line: 103

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

Line: 105

  INSERT INTO JTF_LOC_POSTAL_CODES
  (
    location_postal_code_id,
    last_update_date,
    last_updated_by,
    creation_date,
    created_by,
    last_update_login,
    object_version_number,
    orig_system_ref,
    orig_system_id,
    location_area_id,
    start_date_active,
    end_date_active,
    postal_code_start,
    postal_code_end
  )
  VALUES
  (
    l_postal_code_rec.location_postal_code_id,
    SYSDATE,
    FND_GLOBAL.user_id,
    SYSDATE,
    FND_GLOBAL.user_id,
    FND_GLOBAL.conc_login_id,
    1,
    l_postal_code_rec.orig_system_ref,
    l_postal_code_rec.orig_system_id,
    l_postal_code_rec.location_area_id,
    l_postal_code_rec.start_date_active,
    l_postal_code_rec.end_date_active,
    l_postal_code_rec.postal_code_start,
    l_postal_code_rec.postal_code_end
  );

Line: 200

PROCEDURE update_postal_code
(
  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_postal_code_rec       IN      postal_code_rec_type,
  p_remove_flag           IN      VARCHAR2 := 'N'
)
IS

  l_api_version       CONSTANT NUMBER := 1.0;

Line: 217

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

Line: 225

  SAVEPOINT update_postal_code;

Line: 260

        p_validation_mode => JTF_PLSQL_API.g_update,
        x_return_status   => l_return_status,
        p_postal_code_rec => l_postal_code_rec
      );

Line: 291

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

Line: 294

    UPDATE JTF_LOC_POSTAL_CODES SET
      end_date_active = SYSDATE
    WHERE location_postal_code_id = l_postal_code_rec.location_postal_code_id
    AND object_version_number = l_postal_code_rec.object_version_number;

Line: 299

    UPDATE JTF_LOC_POSTAL_CODES SET
      last_update_date = SYSDATE,
      last_updated_by = FND_GLOBAL.user_id,
      object_version_number = l_postal_code_rec.object_version_number + 1,
      last_update_login = FND_GLOBAL.conc_login_id,
      orig_system_ref = l_postal_code_rec.orig_system_ref,
      orig_system_id = l_postal_code_rec.orig_system_id,
      location_area_id = l_postal_code_rec.location_area_id,
      start_date_active = l_postal_code_rec.start_date_active,
      end_date_active = l_postal_code_rec.end_date_active,
      postal_code_start = l_postal_code_rec.postal_code_start,
      postal_code_end = l_postal_code_rec.postal_code_end
    WHERE location_postal_code_id = l_postal_code_rec.location_postal_code_id
    AND object_version_number = l_postal_code_rec.object_version_number;

Line: 340

      ROLLBACK TO update_postal_code;

Line: 350

      ROLLBACK TO update_postal_code;

Line: 360

      ROLLBACK TO update_postal_code;

Line: 372

END update_postal_code;

Line: 381

PROCEDURE delete_postal_code
(
  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_postal_code_id    IN      NUMBER,
  p_object_version    IN      NUMBER
)
IS

  l_api_version    CONSTANT NUMBER := 1.0;

Line: 397

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

Line: 402

  SAVEPOINT delete_postal_code;

Line: 424

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

Line: 426

  DELETE FROM JTF_LOC_POSTAL_CODES
  WHERE location_postal_code_id = p_postal_code_id
  AND object_version_number = p_object_version;

Line: 455

      ROLLBACK TO delete_postal_code;

Line: 465

      ROLLBACK TO delete_postal_code;

Line: 475

      ROLLBACK TO delete_postal_code;

Line: 487

END delete_postal_code;

Line: 516

    SELECT location_postal_code_id
    FROM JTF_LOC_POSTAL_CODES
    WHERE location_postal_code_id = p_postal_code_id
    AND object_version_number = p_object_version
    FOR UPDATE OF location_postal_code_id NOWAIT;

Line: 813

    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_POS_NO_LOC_POS_CODE_ID');

Line: 826

    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: 960

    SELECT * FROM JTF_LOC_POSTAL_CODES
    WHERE location_postal_code_id = p_postal_code_rec.location_postal_code_id;

Line: 1028

  x_postal_code_rec.last_update_date := FND_API.g_miss_date;

Line: 1029

  x_postal_code_rec.last_updated_by := FND_API.g_miss_num;

Line: 1032

  x_postal_code_rec.last_update_login := FND_API.g_miss_num;