APPS.JTF_DSP_SECTION_ITEM_PVT SQL Statements

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

Line: 41

PROCEDURE insert_row
  (
   p_section_item_id                    IN NUMBER,
   p_object_version_number              IN NUMBER,
   p_section_id                         IN NUMBER,
   p_inventory_item_id                  IN NUMBER,
   p_organization_id                    IN NUMBER,
   p_start_date_active                  IN DATE,
   p_end_date_active                    IN DATE,
   p_sort_order                         IN NUMBER,
   p_association_reason_code            IN VARCHAR2,
   p_creation_date                      IN DATE,
   p_created_by                         IN NUMBER,
   p_last_update_date                   IN DATE,
   p_last_updated_by                    IN NUMBER,
   p_last_update_login                  IN NUMBER,
   x_rowid                              OUT VARCHAR2,
   x_section_item_id                    OUT NUMBER
  )
IS
  CURSOR c IS SELECT rowid FROM jtf_dsp_section_items
    WHERE section_item_id = x_section_item_id;

Line: 63

  CURSOR c2 IS SELECT jtf_dsp_section_items_s1.nextval FROM dual;

Line: 78

  INSERT INTO jtf_dsp_section_items
    (
    section_item_id,
    object_version_number,
    section_id,
    inventory_item_id,
    organization_id,
    start_date_active,
    end_date_active,
    sort_order,
    association_reason_code,
    creation_date,
    created_by,
    last_update_date,
    last_updated_by,
    last_update_login
    )
    VALUES
    (
    x_section_item_id,
    p_object_version_number,
    p_section_id,
    p_inventory_item_id,
    p_organization_id,
    p_start_date_active,
    decode(p_end_date_active, FND_API.G_MISS_DATE, NULL, p_end_date_active),
    decode(p_sort_order, FND_API.G_MISS_NUM, NULL, p_sort_order),
    decode(p_association_reason_code, FND_API.G_MISS_CHAR,
           NULL, p_association_reason_code),
    decode(p_creation_date, FND_API.G_MISS_DATE, sysdate, NULL, sysdate,
           p_creation_date),
    decode(p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
           NULL, FND_GLOBAL.user_id, p_created_by),
    decode(p_last_update_date, FND_API.G_MISS_DATE, sysdate, NULL, sysdate,
           p_last_update_date),
    decode(p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
           NULL, FND_GLOBAL.user_id, p_last_updated_by),
    decode(p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.login_id,
           NULL, FND_GLOBAL.login_id, p_last_update_login)
    );

Line: 128

END insert_row;

Line: 134

PROCEDURE update_row
  (
  p_section_item_id                     IN NUMBER,
  p_object_version_number               IN NUMBER   := FND_API.G_MISS_NUM,
  p_start_date_active                   IN DATE,
  p_end_date_active                     IN DATE,
  p_sort_order                          IN NUMBER,
  p_association_reason_code             IN VARCHAR2,
  p_last_update_date                    IN DATE,
  p_last_updated_by                     IN NUMBER,
  p_last_update_login                   IN NUMBER
  )
IS
BEGIN

  -- update base
  UPDATE jtf_dsp_section_items SET
    object_version_number = object_version_number + 1,
    sort_order = decode(p_sort_order, FND_API.G_MISS_NUM,
                        sort_order, p_sort_order),
    association_reason_code =
      decode(p_association_reason_code, FND_API.G_MISS_CHAR,
             association_reason_code, p_association_reason_code),
    start_date_active = decode(p_start_date_active, FND_API.G_MISS_DATE,
                               start_date_active, p_start_date_active),
    end_date_active = decode(p_end_date_active, FND_API.G_MISS_DATE,
                             end_date_active, p_end_date_active),
    last_update_date = decode(p_last_update_date, FND_API.G_MISS_DATE,
                              sysdate, NULL, sysdate, p_last_update_date),
    last_updated_by = decode(p_last_updated_by, FND_API.G_MISS_NUM,
                             FND_GLOBAL.user_id, NULL, FND_GLOBAL.user_id,
                             p_last_updated_by),
    last_update_login = decode(p_last_update_login, FND_API.G_MISS_NUM,
                               FND_GLOBAL.login_id, NULL,
                               FND_GLOBAL.login_id, p_last_update_login)
    WHERE section_item_id = p_section_item_id
      AND object_version_number = decode(p_object_version_number,
                                         FND_API.G_MISS_NUM,
                                         object_version_number,
                                         p_object_version_number);

Line: 179

END update_row;

Line: 186

PROCEDURE delete_row
  (
   p_section_item_id IN NUMBER
  )
IS
BEGIN

  DELETE FROM jtf_dsp_section_items
    WHERE section_item_id = p_section_item_id;

Line: 200

END delete_row;

Line: 253

      SELECT section_item_id INTO l_tmp_section_item_id
        FROM  jtf_dsp_section_items
        WHERE section_id = p_section_id
          AND inventory_item_id = p_inventory_item_id
          AND organization_id = p_organization_id;

Line: 340

  IS SELECT mini_site_section_section_id FROM jtf_dsp_msite_sct_sects
    WHERE parent_section_id = l_c_section_id
    AND EXISTS (SELECT msite_id FROM jtf_msites_b
    WHERE msite_id = mini_site_id
    AND master_msite_flag = 'Y');

Line: 418

    SELECT section_id INTO l_section_id FROM jtf_dsp_sections_b
      WHERE section_id = p_section_id;

Line: 436

    SELECT inventory_item_id INTO l_inventory_item_id
      FROM mtl_system_items
      WHERE inventory_item_id = p_inventory_item_id
        AND organization_id = p_organization_id;

Line: 441

    SELECT organization_id INTO l_organization_id
      FROM mtl_system_items
      WHERE inventory_item_id = p_inventory_item_id
        AND organization_id = p_organization_id;

Line: 530

PROCEDURE Validate_Update
  (
   p_init_msg_list                  IN VARCHAR2 := FND_API.G_FALSE,
   p_validation_level               IN NUMBER   := FND_API.G_VALID_LEVEL_FULL,
   p_section_item_id                IN NUMBER,
   p_object_version_number          IN NUMBER,
   p_start_date_active              IN DATE,
   p_end_date_active                IN DATE,
   p_sort_order                     IN NUMBER,
   p_association_reason_code        IN VARCHAR2,
   x_return_status                  OUT VARCHAR2,
   x_msg_count                      OUT NUMBER,
   x_msg_data                       OUT VARCHAR2
  )
IS
  l_api_name              CONSTANT VARCHAR2(30) := 'Validate_Update';

Line: 625

END Validate_Update;

Line: 718

    insert_row
      (
      FND_API.G_MISS_NUM,
      l_object_version_number,
      p_section_id,
      p_inventory_item_id,
      p_organization_id,
      p_start_date_active,
      p_end_date_active,
      p_sort_order,
      p_association_reason_code,
      SYSDATE,
      FND_GLOBAL.user_id,
      SYSDATE,
      FND_GLOBAL.user_id,
      FND_GLOBAL.login_id,
      l_rowid,
      x_section_item_id
      );

Line: 739

       FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_SI_INSERT_FAIL');

Line: 743

       FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_SI_INSERT_FAIL');

Line: 801

PROCEDURE Update_Section_Item
  (
   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,
   p_section_item_id                IN NUMBER   := FND_API.G_MISS_NUM,
   p_object_version_number          IN NUMBER,
   p_section_id                     IN NUMBER   := FND_API.G_MISS_NUM,
   p_inventory_item_id              IN NUMBER   := FND_API.G_MISS_NUM,
   p_organization_id                IN NUMBER   := FND_API.G_MISS_NUM,
   p_start_date_active              IN DATE     := FND_API.G_MISS_DATE,
   p_end_date_active                IN DATE     := FND_API.G_MISS_DATE,
   p_sort_order                     IN NUMBER   := FND_API.G_MISS_NUM,
   p_association_reason_code        IN VARCHAR2 := FND_API.G_MISS_CHAR,
   x_return_status                  OUT VARCHAR2,
   x_msg_count                      OUT NUMBER,
   x_msg_data                       OUT VARCHAR2
  )
IS
  l_api_name          CONSTANT VARCHAR2(30) := 'Update_Section_Item';

Line: 832

  SAVEPOINT  UPDATE_SECTION_ITEM_PVT;

Line: 876

        SELECT section_item_id INTO l_section_item_id
          FROM jtf_dsp_section_items
          WHERE section_id = p_section_id
            AND inventory_item_id = p_inventory_item_id
            AND organization_id = p_organization_id;

Line: 910

  Validate_Update
    (
    p_init_msg_list                  => FND_API.G_FALSE,
    p_validation_level               => p_validation_level,
    p_section_item_id                => l_section_item_id,
    p_object_version_number          => p_object_version_number,
    p_start_date_active              => p_start_date_active,
    p_end_date_active                => p_end_date_active,
    p_sort_order                     => p_sort_order,
    p_association_reason_code        => p_association_reason_code,
    x_return_status                  => l_return_status,
    x_msg_count                      => l_msg_count,
    x_msg_data                       => l_msg_data
    );

Line: 928

    FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_SI_INVALID_UPDATE');

Line: 935

    update_row
      (
      l_section_item_id,
      p_object_version_number,
      p_start_date_active,
      p_end_date_active,
      p_sort_order,
      p_association_reason_code,
      SYSDATE,
      FND_GLOBAL.user_id,
      FND_GLOBAL.login_id
      );

Line: 949

       FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_SI_UPDATE_FAIL');

Line: 953

       FND_MESSAGE.Set_Name('JTF', 'JTF_DSP_SI_UPDATE_FAIL');

Line: 974

     ROLLBACK TO UPDATE_SECTION_ITEM_PVT;

Line: 981

     ROLLBACK TO UPDATE_SECTION_ITEM_PVT;

Line: 988

     ROLLBACK TO UPDATE_SECTION_ITEM_PVT;

Line: 1000

END Update_Section_Item;

Line: 1002

PROCEDURE Delete_Section_Item
  (
   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,
   p_call_from_trigger            IN BOOLEAN     := FALSE,
   p_section_item_id              IN NUMBER      := FND_API.G_MISS_NUM,
   p_section_id                   IN NUMBER      := FND_API.G_MISS_NUM,
   p_inventory_item_id            IN NUMBER      := FND_API.G_MISS_NUM,
   p_organization_id              IN NUMBER      := FND_API.G_MISS_NUM,
   x_return_status                OUT VARCHAR2,
   x_msg_count                    OUT NUMBER,
   x_msg_data                     OUT VARCHAR2
  )
IS
  l_api_name          CONSTANT VARCHAR2(30)  := 'Delete_Section_Item';

Line: 1027

  IS SELECT mini_site_section_item_id FROM jtf_dsp_msite_sct_items
    WHERE section_item_id = l_c_section_item_id;

Line: 1031

  IS SELECT section_id, inventory_item_id, organization_id
    FROM jtf_dsp_section_items
    WHERE section_item_id = l_c_section_item_id;

Line: 1040

    SAVEPOINT  DELETE_SECTION_ITEM_PVT;

Line: 1099

      SELECT section_item_id INTO l_section_item_id
        FROM jtf_dsp_section_items
        WHERE section_id = p_section_id
        AND inventory_item_id = p_inventory_item_id
        AND organization_id = p_organization_id;

Line: 1133

    JTF_DSP_MSITE_SCT_ITEM_PVT.Delete_MSite_Section_Item
      (
      p_api_version                  => p_api_version,
      p_init_msg_list                => FND_API.G_FALSE,
      p_commit                       => FND_API.G_FALSE,
      p_validation_level             => p_validation_level,
      p_call_from_trigger            => p_call_from_trigger,
      p_mini_site_section_item_id    => r1.mini_site_section_item_id,
      p_mini_site_id                 => FND_API.G_MISS_NUM,
      p_section_item_id              => FND_API.G_MISS_NUM,
      x_return_status                => x_return_status,
      x_msg_count                    => x_msg_count,
      x_msg_data                     => x_msg_data
      );

Line: 1164

  delete_row(l_section_item_id);

Line: 1170

       ROLLBACK TO DELETE_SECTION_ITEM_PVT;

Line: 1179

       ROLLBACK TO DELETE_SECTION_ITEM_PVT;

Line: 1188

       ROLLBACK TO DELETE_SECTION_ITEM_PVT;

Line: 1202

END Delete_Section_Item;

Line: 1207

PROCEDURE Delete_Section_Items_For_Item
  (
   p_inventory_item_id            IN NUMBER      := FND_API.G_MISS_NUM,
   p_organization_id              IN NUMBER      := FND_API.G_MISS_NUM
  )
IS
  l_api_name          CONSTANT VARCHAR2(30)  :='Delete_Section_Items_For_Item';

Line: 1221

    SELECT section_item_id FROM jtf_dsp_section_items
      WHERE inventory_item_id = l_c_inventory_item_id AND
            organization_id = l_c_organization_id;

Line: 1228

    Delete_Section_Item
      (
      p_api_version                  => l_api_version,
      p_init_msg_list                => FND_API.G_FALSE,
      p_commit                       => FND_API.G_FALSE,
      p_validation_level             => FND_API.G_VALID_LEVEL_FULL,
      p_call_from_trigger            => TRUE,
      p_section_item_id              => r1.section_item_id,
      p_section_id                   => FND_API.G_MISS_NUM,
      p_inventory_item_id            => FND_API.G_MISS_NUM,
      p_organization_id              => FND_API.G_MISS_NUM,
      x_return_status                => l_return_status,
      x_msg_count                    => l_msg_count,
      x_msg_data                     => l_msg_data
      );

Line: 1246

END Delete_Section_Items_For_Item;

Line: 1252

PROCEDURE Update_Delete_Sct_Itms
  (
   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,
   p_section_item_ids               IN JTF_NUMBER_TABLE,
   p_object_version_numbers         IN JTF_NUMBER_TABLE,
   p_start_date_actives             IN JTF_DATE_TABLE,
   p_end_date_actives               IN JTF_DATE_TABLE,
   p_sort_orders                    IN JTF_NUMBER_TABLE,
   p_association_reason_codes       IN JTF_VARCHAR2_TABLE_300,
   p_delete_flags                   IN JTF_VARCHAR2_TABLE_300,
   x_return_status                  OUT VARCHAR2,
   x_msg_count                      OUT NUMBER,
   x_msg_data                       OUT VARCHAR2
  )
IS
  l_api_name          CONSTANT VARCHAR2(30) := 'Update_Delete_Sct_Itms';

Line: 1281

  SAVEPOINT  UPDATE_DELETE_SCT_ITMS_PVT;

Line: 1306

    IF (p_delete_flags(i) = 'Y') THEN

      Delete_Section_Item
        (
        p_api_version                  => p_api_version,
        p_init_msg_list                => FND_API.G_FALSE,
        p_commit                       => FND_API.G_FALSE,
        p_validation_level             => FND_API.G_VALID_LEVEL_FULL,
        p_section_item_id              => p_section_item_ids(i),
        p_section_id                   => FND_API.G_MISS_NUM,
        p_inventory_item_id            => FND_API.G_MISS_NUM,
        p_organization_id              => FND_API.G_MISS_NUM,
        x_return_status                => x_return_status,
        x_msg_count                    => x_msg_count,
        x_msg_data                     => x_msg_data
        );

Line: 1331

      Update_Section_Item
        (
        p_api_version                    => p_api_version,
        p_init_msg_list                  => FND_API.G_FALSE,
        p_commit                         => FND_API.G_FALSE,
        p_validation_level               => p_validation_level,
        p_section_item_id                => p_section_item_ids(i),
        p_object_version_number          => p_object_version_numbers(i),
        p_section_id                     => FND_API.G_MISS_NUM,
        p_inventory_item_id              => FND_API.G_MISS_NUM,
        p_organization_id                => FND_API.G_MISS_NUM,
        p_start_date_active              => p_start_date_actives(i),
        p_end_date_active                => p_end_date_actives(i),
        p_sort_order                     => p_sort_orders(i),
        p_association_reason_code        => p_association_reason_codes(i),
        x_return_status                  => l_return_status,
        x_msg_count                      => x_msg_count,
        x_msg_data                       => x_msg_data
        );

Line: 1378

     ROLLBACK TO UPDATE_DELETE_SCT_ITMS_PVT;

Line: 1385

     ROLLBACK TO UPDATE_DELETE_SCT_ITMS_PVT;

Line: 1392

     ROLLBACK TO UPDATE_DELETE_SCT_ITMS_PVT;

Line: 1404

END Update_Delete_Sct_Itms;