The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
label_code
FROM IBC_CITEM_VERSION_LABELS
WHERE Label_Code = P_CV_Label_Rec.Label_Code
AND content_item_id = P_CV_Label_Rec.content_item_id;
Ibc_Citem_Version_Labels_Pkg.INSERT_ROW (
x_ROWID => lx_rowid
,p_content_item_id => l_CV_Label_rec.content_item_id
,p_Label_code => l_CV_Label_rec.Label_code
,p_citem_version_id => l_CV_Label_rec.citem_version_id
,p_CREATED_BY => l_CV_Label_rec.CREATED_BY
,p_CREATION_DATE => l_CV_Label_rec.CREATION_DATE
,p_LAST_UPDATED_BY => l_CV_Label_rec.LAST_UPDATED_BY
,p_LAST_UPDATE_DATE => l_CV_Label_rec.LAST_UPDATE_DATE
,p_LAST_UPDATE_LOGIN => l_CV_Label_rec.LAST_UPDATE_LOGIN
,p_OBJECT_VERSION_NUMBER => l_CV_Label_rec.OBJECT_VERSION_NUMBER);
Fnd_Message.Set_Name('IBC', 'IBC_INSERT_ERROR');
PROCEDURE Update_CV_Label(
P_Api_Version_Number 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_CV_Label_Rec IN Ibc_Cv_Label_Grp.CV_Label_Rec_Type ,--:= Ibc_Cv_Label_Grp.G_MISS_CV_Label_Rec,
x_CV_Label_Rec OUT NOCOPY Ibc_Cv_Label_Grp.CV_Label_Rec_Type,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
CURSOR C_CV_Label IS
SELECT
label_code
FROM IBC_CITEM_VERSION_LABELS
WHERE Label_Code = P_CV_Label_Rec.Label_Code
AND content_item_id = P_CV_Label_Rec.content_item_id;
l_api_name VARCHAR2(50) := 'Update_CV_Label';
Ibc_Citem_Version_Labels_Pkg.UPDATE_ROW (
p_content_item_id => l_CV_Label_rec.content_item_id
,p_Label_code => l_CV_Label_rec.Label_code
,p_citem_version_id => l_CV_Label_rec.citem_version_id
,p_LAST_UPDATED_BY => l_CV_Label_rec.LAST_UPDATED_BY
,p_LAST_UPDATE_DATE => l_CV_Label_rec.LAST_UPDATE_DATE
,p_LAST_UPDATE_LOGIN => l_CV_Label_rec.LAST_UPDATE_LOGIN
,p_OBJECT_VERSION_NUMBER => l_CV_Label_rec.OBJECT_VERSION_NUMBER);
Fnd_Message.Set_Name('IBC', 'IBC_UPDATE_ERROR');
END Update_CV_Label;
PROCEDURE delete_CV_Label(
P_Api_Version_Number 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_Label_Code IN VARCHAR2,
P_content_item_id IN NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
CURSOR C_CV_Label IS
SELECT
label_code
FROM IBC_CITEM_VERSION_LABELS
WHERE Label_Code = P_Label_Code
AND content_item_id = P_content_item_id;
l_api_name VARCHAR2(50) := 'Delete_CV_Label';
Ibc_Citem_Version_Labels_Pkg.DELETE_ROW (
p_content_item_id => p_content_item_id
,p_Label_code => p_Label_code);
Fnd_Message.Set_Name('IBC', 'IBC_DELETE_ERROR');
END delete_CV_Label;
SELECT
CONTENT_ITEM_ID
,CITEM_VERSION_ID
,LABEL_CODE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,OBJECT_VERSION_NUMBER
INTO
l_CV_Label_Rec.CONTENT_ITEM_ID
,l_CV_Label_Rec.CITEM_VERSION_ID
,l_CV_Label_Rec.LABEL_CODE
,l_CV_Label_Rec.CREATED_BY
,l_CV_Label_Rec.CREATION_DATE
,l_CV_Label_Rec.LAST_UPDATED_BY
,l_CV_Label_Rec.LAST_UPDATE_DATE
,l_CV_Label_Rec.LAST_UPDATE_LOGIN
,l_CV_Label_Rec.OBJECT_VERSION_NUMBER
FROM IBC_CITEM_VERSION_LABELS
WHERE CONTENT_ITEM_ID = p_content_item_id
AND LABEL_CODE = p_label_code;
SELECT '1' INTO l_temp FROM ibc_citem_versions_b
WHERE content_item_id = p_content_item_ids(i)
AND citem_version_id = l_citem_version_ids(i);
SELECT citem_version_id INTO l_citem_version_ids(i)
FROM ibc_citem_versions_b
WHERE content_item_id = p_content_item_ids(i)
AND version_number = p_version_number(i);
SELECT '1' INTO l_temp FROM IBC_LABELS_B
WHERE label_code = p_label_code;
-- Update
FORALL i IN p_content_item_ids.FIRST..p_content_item_ids.LAST
UPDATE IBC_CITEM_VERSION_LABELS SET
CITEM_VERSION_ID = l_citem_version_ids(i),
OBJECT_VERSION_NUMBER = 1,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = Fnd_Global.user_id,
LAST_UPDATE_LOGIN = Fnd_Global.login_id
WHERE label_code = p_label_code
AND content_item_id = p_content_item_ids(i);
--DBMS_OUTPUT.put_line('Update Successful....');
-- Collect all those that could not be updated
-- Will insert them.
FOR i IN p_content_item_ids.FIRST..p_content_item_ids.LAST
LOOP
IF SQL%BULK_ROWCOUNT(i) = 0 THEN
l_ins_content_item_ids.extend;
-- Insert
--
IF l_ins_content_item_ids.EXISTS(l_ins_content_item_ids.FIRST) THEN
FORALL i IN l_ins_content_item_ids.FIRST..l_ins_content_item_ids.LAST
INSERT INTO IBC_CITEM_VERSION_LABELS (
CONTENT_ITEM_ID,
LABEL_CODE,
CITEM_VERSION_ID,
OBJECT_VERSION_NUMBER,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
) VALUES (
l_ins_content_item_ids(i),
p_LABEL_CODE,
l_ins_citem_version_ids(i),
1,
SYSDATE,
Fnd_Global.user_id,
SYSDATE,
Fnd_Global.user_id,
Fnd_Global.login_id
);
PROCEDURE Delete_Cv_Labels(
p_label_code IN VARCHAR2
,p_content_item_ids IN JTF_NUMBER_TABLE
,p_commit IN VARCHAR2
,p_api_version_number IN NUMBER
,p_init_msg_list IN VARCHAR2 --DEFAULT Fnd_Api.G_FALSE
,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_CV_LABELS';
SAVEPOINT SVPT_DELETE_CV_LABELS;
-- Delete
FORALL i IN p_content_item_ids.FIRST..p_content_item_ids.LAST
DELETE FROM IBC_CITEM_VERSION_LABELS
WHERE label_code = p_label_code
AND content_item_id = p_content_item_ids(i);
--DBMS_OUTPUT.put_line('Delete Successful....');
END Delete_Cv_Labels;