APPS.JTF_AMV_ITEM_PUB SQL Statements

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

Line: 40

   l_sql := 'SELECT COUNT(*) FROM ' || p_lookup_table_name;

Line: 60

Select application_id
From   fnd_application
where  application_id = p_application_id;

Line: 84

Select item_id
From   jtf_amv_items_b
where  item_id = p_item_id;

Line: 122

select
      JTF_AMV_ITEMS_B_S.nextval, sysdate
from dual;

Line: 294

    JTF_AMV_ITEMS_PKG.INSERT_ROW
    (
        X_ROWID => l_row_id,
        X_ITEM_ID =>l_item_rec.item_id,
        X_OBJECT_VERSION_NUMBER => 1,
        X_CREATION_DATE => l_current_date,
        X_CREATED_BY    => CURRENT_USER_ID,
        X_LAST_UPDATE_DATE => l_current_date,
        X_LAST_UPDATED_BY => CURRENT_USER_ID,
        X_LAST_UPDATE_LOGIN => CURRENT_LOGIN_ID,
        X_APPLICATION_ID => l_item_rec.application_id,
        X_EXTERNAL_ACCESS_FLAG => l_item_rec.external_access_flag,
        X_ITEM_NAME => l_item_rec.item_name,
        X_DESCRIPTION => l_item_rec.description,
        X_TEXT_STRING => l_item_rec.text_string,
        X_LANGUAGE_CODE => l_item_rec.language_code,
        X_STATUS_CODE => l_item_rec.status_code,
        X_EFFECTIVE_START_DATE => l_item_rec.effective_start_date,
        X_EXPIRATION_DATE => l_item_rec.expiration_date,
        X_ITEM_TYPE => l_item_rec.item_type,
        X_URL_STRING => l_item_rec.url_string,
        X_PUBLICATION_DATE => l_item_rec.publication_date,
        X_PRIORITY => l_item_rec.priority,
        X_CONTENT_TYPE_ID => l_item_rec.content_type_id,
        X_OWNER_ID => l_item_rec.owner_id,
        X_DEFAULT_APPROVER_ID => l_item_rec.default_approver_id,
        X_ITEM_DESTINATION_TYPE => l_item_rec.item_destination_type,
        X_ACCESS_NAME => l_item_rec.access_name,
        X_DELIVERABLE_TYPE_CODE => l_item_rec.deliverable_type_code,
        X_APPLICABLE_TO_CODE => l_item_rec.applicable_to_code,
        X_ATTRIBUTE_CATEGORY => null,
        X_ATTRIBUTE1 => null,
        X_ATTRIBUTE2 => null,
        X_ATTRIBUTE3 => null,
        X_ATTRIBUTE4 => null,
        X_ATTRIBUTE5 => null,
        X_ATTRIBUTE6 => null,
        X_ATTRIBUTE7 => null,
        X_ATTRIBUTE8 => null,
        X_ATTRIBUTE9 => null,
        X_ATTRIBUTE10 => null,
        X_ATTRIBUTE11 => null,
        X_ATTRIBUTE12 => null,
        X_ATTRIBUTE13 => null,
        X_ATTRIBUTE14 => null,
        X_ATTRIBUTE15 => null
    );

Line: 386

PROCEDURE Delete_Item
(
    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  VARCHAR2,
    x_msg_count         OUT NOCOPY  NUMBER,
    x_msg_data          OUT NOCOPY  VARCHAR2,
    p_item_id           IN  NUMBER
) AS
l_api_name             CONSTANT VARCHAR2(30) := 'Delete_Item';

Line: 401

    SAVEPOINT  Delete_Item_Pub;

Line: 425

    Delete from jtf_amv_item_authors
    where item_id = p_item_id;

Line: 428

    Delete from jtf_amv_item_keywords
    where item_id = p_item_id;

Line: 431

    Delete from jtf_amv_attachments
    where attachment_used_by_id = p_item_id
    and   attachment_used_by = G_USED_BY_ITEM;

Line: 435

    JTF_AMV_ITEMS_PKG.DELETE_ROW ( p_item_id );

Line: 448

       ROLLBACK TO  Delete_Item_Pub;

Line: 457

       ROLLBACK TO  Delete_Item_Pub;

Line: 466

       ROLLBACK TO  Delete_Item_Pub;

Line: 477

END Delete_Item;

Line: 479

PROCEDURE Update_Item
(
    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  VARCHAR2,
    x_msg_count         OUT NOCOPY  NUMBER,
    x_msg_data          OUT NOCOPY  VARCHAR2,
    p_item_rec          IN  ITEM_REC_TYPE
) AS
l_api_name             CONSTANT VARCHAR2(30) := 'Update_Item';

Line: 500

    SAVEPOINT  Update_Item_Pub;

Line: 797

        JTF_AMV_ITEMS_PKG.UPDATE_ROW
        (
            X_ITEM_ID =>l_new_item_rec.item_id,
            X_OBJECT_VERSION_NUMBER => l_new_item_rec.object_version_number,
            X_LAST_UPDATE_DATE => sysdate,
            X_LAST_UPDATED_BY => CURRENT_USER_ID,
            X_LAST_UPDATE_LOGIN => CURRENT_LOGIN_ID,
            X_APPLICATION_ID => l_new_item_rec.application_id,
            X_EXTERNAL_ACCESS_FLAG => l_new_item_rec.external_access_flag,
            X_ITEM_NAME => l_new_item_rec.item_name,
            X_DESCRIPTION => l_new_item_rec.description,
            X_TEXT_STRING => l_new_item_rec.text_string,
            X_LANGUAGE_CODE => l_new_item_rec.language_code,
            X_STATUS_CODE => l_new_item_rec.status_code,
            X_EFFECTIVE_START_DATE => l_new_item_rec.effective_start_date,
            X_EXPIRATION_DATE => l_new_item_rec.expiration_date,
            X_ITEM_TYPE => l_new_item_rec.item_type,
            X_URL_STRING => l_new_item_rec.url_string,
            X_PUBLICATION_DATE => l_new_item_rec.publication_date,
            X_PRIORITY => l_new_item_rec.priority,
            X_CONTENT_TYPE_ID => l_new_item_rec.content_type_id,
            X_OWNER_ID => l_new_item_rec.owner_id,
            X_DEFAULT_APPROVER_ID => l_new_item_rec.default_approver_id,
            X_ITEM_DESTINATION_TYPE => l_new_item_rec.item_destination_type,
            X_ACCESS_NAME => l_new_item_rec.access_name,
            X_DELIVERABLE_TYPE_CODE => l_new_item_rec.deliverable_type_code,
            X_APPLICABLE_TO_CODE => l_new_item_rec.applicable_to_code,

            X_ATTRIBUTE_CATEGORY => FND_API.G_MISS_CHAR,
            X_ATTRIBUTE1 => FND_API.G_MISS_CHAR,
            X_ATTRIBUTE2 => FND_API.G_MISS_CHAR,
            X_ATTRIBUTE3 => FND_API.G_MISS_CHAR,
            X_ATTRIBUTE4 => FND_API.G_MISS_CHAR,
            X_ATTRIBUTE5 => FND_API.G_MISS_CHAR,
            X_ATTRIBUTE6 => FND_API.G_MISS_CHAR,
            X_ATTRIBUTE7 => FND_API.G_MISS_CHAR,
            X_ATTRIBUTE8 => FND_API.G_MISS_CHAR,
            X_ATTRIBUTE9 => FND_API.G_MISS_CHAR,
            X_ATTRIBUTE10 => FND_API.G_MISS_CHAR,
            X_ATTRIBUTE11 => FND_API.G_MISS_CHAR,
            X_ATTRIBUTE12 => FND_API.G_MISS_CHAR,
            X_ATTRIBUTE13 => FND_API.G_MISS_CHAR,
            X_ATTRIBUTE14 => FND_API.G_MISS_CHAR,
            X_ATTRIBUTE15 => FND_API.G_MISS_CHAR
        );

Line: 855

       ROLLBACK TO  Update_Item_Pub;

Line: 864

       ROLLBACK TO  Update_Item_Pub;

Line: 873

       ROLLBACK TO  Update_Item_Pub;

Line: 884

END Update_Item;

Line: 903

Select
     item_id,
     CREATION_DATE,
     CREATED_BY,
     LAST_UPDATE_DATE,
     LAST_UPDATED_BY,
     LAST_UPDATE_LOGIN,
     OBJECT_VERSION_NUMBER,
     APPLICATION_ID,
     EXTERNAL_ACCESS_FLAG,
     ITEM_NAME,
     DESCRIPTION,
     TEXT_STRING,
     LANGUAGE_CODE,
     STATUS_CODE,
     effective_start_date,
     expiration_date,
     ITEM_TYPE,
     URL_STRING,
     PUBLICATION_DATE,
     PRIORITY,
     CONTENT_TYPE_ID,
     OWNER_ID,
     DEFAULT_APPROVER_ID,
     ITEM_DESTINATION_TYPE,
     access_name,
     deliverable_type_code,
     applicable_to_code
From  jtf_amv_items_vl
Where item_id = p_item_id;

Line: 1020

Select
     item_keyword_id
From jtf_amv_item_keywords
Where keyword = p_kword
And   item_id = p_item_id;

Line: 1027

Select jtf_amv_item_keywords_s.nextval, sysdate
From  Dual;

Line: 1077

           Insert Into jtf_amv_item_keywords
             (
                ITEM_KEYWORD_ID,
                OBJECT_VERSION_NUMBER,
                LAST_UPDATE_DATE,
                LAST_UPDATED_BY,
                CREATION_DATE,
                CREATED_BY,
                LAST_UPDATE_LOGIN,
                ITEM_ID,
                KEYWORD
             ) VALUES
             (
                l_temp_number,
                1,
                l_date,
                l_current_user_id,
                l_date,
                l_current_user_id,
                l_current_login_id,
                p_item_id,
                p_keyword_tab(i)
             );

Line: 1175

PROCEDURE Delete_ItemKeyword
(
    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  VARCHAR2,
    x_msg_count         OUT NOCOPY  NUMBER,
    x_msg_data          OUT NOCOPY  VARCHAR2,
    p_item_id           IN  NUMBER,
    p_keyword_tab       IN  CHAR_TAB_TYPE
) AS
l_api_name             CONSTANT VARCHAR2(30) := 'Delete_ItemKeyword';

Line: 1194

Select
     item_keyword_id
From jtf_amv_item_keywords
Where keyword = p_kword
And   item_id = p_item_id;

Line: 1201

    SAVEPOINT  Delete_ItemKeyword_Pub;

Line: 1241

              Delete from jtf_amv_item_keywords
              Where  item_keyword_id = l_temp_number;

Line: 1247

       Delete from jtf_amv_item_keywords
       Where  item_id = p_item_id;

Line: 1262

       ROLLBACK TO  Delete_ItemKeyword_Pub;

Line: 1271

       ROLLBACK TO  Delete_ItemKeyword_Pub;

Line: 1280

       ROLLBACK TO  Delete_ItemKeyword_Pub;

Line: 1291

END Delete_ItemKeyword;

Line: 1293

PROCEDURE Delete_ItemKeyword
(
    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  VARCHAR2,
    x_msg_count         OUT NOCOPY  NUMBER,
    x_msg_data          OUT NOCOPY  VARCHAR2,
    p_item_id           IN  NUMBER,
    p_keyword           IN  VARCHAR2
) AS
l_char_tab           CHAR_TAB_TYPE;

Line: 1307

    Delete_ItemKeyword
    (
        p_api_version       => p_api_version,
        p_init_msg_list     => p_init_msg_list,
        p_commit            => p_commit,
        x_return_status     => x_return_status,
        x_msg_count         => x_msg_count,
        x_msg_data          => x_msg_data,
        p_item_id           => p_item_id,
        p_keyword_tab       => l_char_tab
    );

Line: 1318

end Delete_ItemKeyword;

Line: 1367

    Delete from jtf_amv_item_keywords
    Where  item_id = p_item_id;

Line: 1440

Select
    KEYWORD
from  JTF_AMV_ITEM_KEYWORDS
Where item_id = p_item_id;

Line: 1534

Select
     item_author_id
From jtf_amv_item_authors
Where author = p_author
And   item_id = p_item_id;

Line: 1541

Select jtf_amv_item_authors_s.nextval, sysdate
From  Dual;

Line: 1591

           Insert Into jtf_amv_item_authors
             (
                ITEM_AUTHOR_ID,
                OBJECT_VERSION_NUMBER,
                LAST_UPDATE_DATE,
                LAST_UPDATED_BY,
                CREATION_DATE,
                CREATED_BY,
                LAST_UPDATE_LOGIN,
                ITEM_ID,
                AUTHOR
             ) VALUES
             (
                l_temp_number,
                1,
                l_date,
                l_current_user_id,
                l_date,
                l_current_user_id,
                l_current_login_id,
                p_item_id,
                p_author_tab(i)
             );

Line: 1687

PROCEDURE Delete_ItemAuthor
(
    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  VARCHAR2,
    x_msg_count         OUT NOCOPY  NUMBER,
    x_msg_data          OUT NOCOPY  VARCHAR2,
    p_item_id           IN  NUMBER,
    p_author_tab        IN  CHAR_TAB_TYPE
) AS
l_api_name             CONSTANT VARCHAR2(30) := 'Delete_ItemAuthor';

Line: 1706

Select
     item_author_id
From jtf_amv_item_authors
Where author = p_author
And   item_id = p_item_id;

Line: 1713

    SAVEPOINT  Delete_ItemAuthor_Pub;

Line: 1753

              Delete from jtf_amv_item_authors
              Where  item_author_id = l_temp_number;

Line: 1759

       Delete from jtf_amv_item_authors
       Where  item_id = p_item_id;

Line: 1774

       ROLLBACK TO  Delete_ItemAuthor_Pub;

Line: 1783

       ROLLBACK TO  Delete_ItemAuthor_Pub;

Line: 1792

       ROLLBACK TO  Delete_ItemAuthor_Pub;

Line: 1803

END Delete_ItemAuthor;

Line: 1805

PROCEDURE Delete_ItemAuthor
(
    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  VARCHAR2,
    x_msg_count         OUT NOCOPY  NUMBER,
    x_msg_data          OUT NOCOPY  VARCHAR2,
    p_item_id           IN  NUMBER,
    p_author            IN  VARCHAR2
) AS
l_char_tab           CHAR_TAB_TYPE;

Line: 1819

    Delete_ItemAuthor
    (
        p_api_version       => p_api_version,
        p_init_msg_list     => p_init_msg_list,
        p_commit            => p_commit,
        x_return_status     => x_return_status,
        x_msg_count         => x_msg_count,
        x_msg_data          => x_msg_data,
        p_item_id           => p_item_id,
        p_author_tab       => l_char_tab
    );

Line: 1830

end Delete_ItemAuthor;

Line: 1879

    Delete from jtf_amv_item_authors
    Where  item_id = p_item_id;

Line: 1947

Select
    AUTHOR
from  JTF_AMV_ITEM_AUTHORS
Where item_id = p_item_id;