APPS.IBE_DSP_SECTION_ITEM_PVT SQL Statements

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

Line: 43

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_usage_name				IN VARCHAR2,
   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 NOCOPY VARCHAR2,
   x_section_item_id                    OUT NOCOPY NUMBER
  )
IS
  CURSOR c IS SELECT rowid FROM ibe_dsp_section_items
    WHERE section_item_id = x_section_item_id;

Line: 66

  CURSOR c2 IS SELECT ibe_dsp_section_items_s1.nextval FROM dual;

Line: 81

  INSERT INTO ibe_dsp_section_items
    (
    section_item_id,
    object_version_number,
    section_id,
    inventory_item_id,
    organization_id,
    start_date_active,
    end_date_active,
    usage_name,
    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_usage_name, FND_API.G_MISS_CHAR, NULL, p_usage_name),
    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: 133

END insert_row;

Line: 139

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_usage_name 				IN VARCHAR2,
  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 ibe_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),
    usage_name = decode(p_usage_name, FND_API.G_MISS_CHAR, usage_name, p_usage_name),
    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: 186

END update_row;

Line: 193

PROCEDURE delete_row
  (
   p_section_item_id IN NUMBER
  )
IS
BEGIN

  DELETE FROM ibe_dsp_section_items
    WHERE section_item_id = p_section_item_id;

Line: 207

END delete_row;

Line: 260

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

Line: 359

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

Line: 368

  IS SELECT mini_site_section_section_id FROM ibe_dsp_msite_sct_sects
    WHERE parent_section_id = l_c_section_id
	 AND mini_site_id = l_c_minisite_id;

Line: 443

    SELECT section_id INTO l_section_id FROM ibe_dsp_sections_b
      WHERE section_id = p_section_id;

Line: 467

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

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

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 NOCOPY  VARCHAR2,
   x_msg_count                      OUT NOCOPY  NUMBER,
   x_msg_data                       OUT NOCOPY  VARCHAR2
  )
IS
  l_api_name              CONSTANT VARCHAR2(30) := 'Validate_Update';

Line: 679

END Validate_Update;

Line: 772

    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,
      null,
      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: 794

       FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SI_INSERT_FAIL');

Line: 804

       FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SI_INSERT_FAIL');

Line: 812

  IBE_PROD_RELATION_PVT.Item_Section_Inserted
    (
    p_section_id        => p_section_id,
    p_inventory_item_id => p_inventory_item_id,
    p_organization_id   => p_organization_id  --Bug 2922902
    );

Line: 870

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_usage_name			    IN VARCHAR2 := FND_API.G_MISS_CHAR,
   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 NOCOPY  VARCHAR2,
   x_msg_count                      OUT NOCOPY  NUMBER,
   x_msg_data                       OUT NOCOPY  VARCHAR2
  )
IS
  l_api_name          CONSTANT VARCHAR2(30) := 'Update_Section_Item';

Line: 902

  SAVEPOINT  UPDATE_SECTION_ITEM_PVT;

Line: 946

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

Line: 986

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

    FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SI_INVALID_UPDATE');

Line: 1011

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

Line: 1026

       FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SI_UPDATE_FAIL');

Line: 1036

       FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SI_UPDATE_FAIL');

Line: 1057

     ROLLBACK TO UPDATE_SECTION_ITEM_PVT;

Line: 1064

     ROLLBACK TO UPDATE_SECTION_ITEM_PVT;

Line: 1071

     ROLLBACK TO UPDATE_SECTION_ITEM_PVT;

Line: 1090

END Update_Section_Item;

Line: 1092

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 NOCOPY  VARCHAR2,
   x_msg_count                    OUT NOCOPY  NUMBER,
   x_msg_data                     OUT NOCOPY  VARCHAR2
  )
IS


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

Line: 1119

  IS SELECT mini_site_section_item_id FROM ibe_dsp_msite_sct_items
    WHERE section_item_id = l_c_section_item_id;

Line: 1123

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

Line: 1131

    SAVEPOINT  DELETE_SECTION_ITEM_PVT;

Line: 1190

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

Line: 1229

    IBE_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: 1253

  IBE_PROD_RELATION_PVT.Item_Section_Deleted
    (
    p_section_id        => l_section_id,
    p_inventory_item_id => l_inventory_item_id ,
    p_organization_id   => l_organization_id  --Bug 2922902
    );

Line: 1261

  delete_row(l_section_item_id);

Line: 1267

       ROLLBACK TO DELETE_SECTION_ITEM_PVT;

Line: 1276

       ROLLBACK TO DELETE_SECTION_ITEM_PVT;

Line: 1285

       ROLLBACK TO DELETE_SECTION_ITEM_PVT;

Line: 1305

END Delete_Section_Item;

Line: 1310

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

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

Line: 1331

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

END Delete_Section_Items_For_Item;

Line: 1361

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_usage_names		    IN JTF_VARCHAR2_TABLE_300,
   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 NOCOPY  VARCHAR2,
   x_msg_count                      OUT NOCOPY  NUMBER,
   x_msg_data                       OUT NOCOPY  VARCHAR2
  )
IS
  l_api_name          CONSTANT VARCHAR2(30) := 'Update_Delete_Sct_Itms';

Line: 1391

  SAVEPOINT  UPDATE_DELETE_SCT_ITMS_PVT;

Line: 1415

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

      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_usage_name			 => p_usage_names(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: 1488

     ROLLBACK TO UPDATE_DELETE_SCT_ITMS_PVT;

Line: 1495

     ROLLBACK TO UPDATE_DELETE_SCT_ITMS_PVT;

Line: 1502

     ROLLBACK TO UPDATE_DELETE_SCT_ITMS_PVT;

Line: 1521

END Update_Delete_Sct_Itms;