The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Attribute_Type(
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_Attribute_Type_Rec IN Ibc_Ctype_Pvt.Attribute_Type_Rec_Type,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
);
PROCEDURE Delete_Attribute_Type(
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_Attribute_Type_Rec IN Ibc_Ctype_Pvt.Attribute_Type_Rec_Type,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
);
SELECT
Content_Type_Code
FROM ibc_content_types_b
WHERE Content_Type_Code = p_Content_Type_Code;
SELECT '1'
FROM IBC_ATTRIBUTE_TYPES_B
WHERE content_type_code = p_content_type_code
AND attribute_type_code = p_attribute_type_code;
Ibc_Content_Types_Pkg.INSERT_ROW (
x_ROWID =>lx_rowid,
p_CONTENT_TYPE_CODE =>l_content_type_rec.CONTENT_TYPE_CODE,
p_CONTENT_TYPE_STATUS =>l_content_type_rec.CONTENT_TYPE_STATUS,
p_APPLICATION_ID =>l_content_type_rec.APPLICATION_ID,
p_REQUEST_ID =>l_content_type_rec.REQUEST_ID,
p_OBJECT_VERSION_NUMBER =>l_content_type_rec.oBJECT_VERSION_NUMBER,
p_CONTENT_TYPE_Name =>l_content_type_rec.CONTENT_TYPE_name,
p_DESCRIPTION =>l_content_type_rec.DESCRIPTION,
p_CREATION_DATE =>l_content_type_rec.CREATION_DATE,
p_CREATED_BY =>l_content_type_rec.CREATED_BY,
p_LAST_UPDATE_DATE =>l_content_type_rec.LAST_UPDATE_DATE,
p_LAST_UPDATED_BY =>l_content_type_rec.LAST_UPDATED_BY,
p_LAST_UPDATE_LOGIN =>l_content_type_rec.LAST_UPDATE_LOGIN);
l_ATTRIBUTE_TYPE_rec.UPDATEABLE_FLAG := 'T';
l_ATTRIBUTE_TYPE_TBL(1).UPDATEABLE_FLAG := 'T';
Fnd_Message.Set_name('IBC', 'IBC_INSERT_ERROR');
SELECT Content_Type_Code
FROM ibc_content_types_b
WHERE Content_Type_Code = p_Content_Type_Code;
SELECT '1'
FROM IBC_ATTRIBUTE_TYPES_B
WHERE content_type_code = p_content_type_code
AND attribute_type_code = p_attribute_type_code;
Ibc_Attribute_Types_Pkg.insert_row (
x_rowid =>lx_rowid,
p_attribute_type_code =>l_attribute_type_rec.attribute_type_code,
p_content_type_code =>l_attribute_type_rec.content_type_code,
p_data_type_code =>l_attribute_type_rec.data_type_code,
p_data_length =>l_attribute_type_rec.data_length,
p_min_instances =>l_attribute_type_rec.min_instances,
p_max_instances =>l_attribute_type_rec.max_instances,
p_reference_code =>l_attribute_type_rec.reference_code,
p_default_value =>l_attribute_type_rec.default_value,
p_updateable_flag =>l_attribute_type_rec.updateable_flag,
p_object_version_number =>l_attribute_type_rec.object_version_number,
p_attribute_type_name =>l_attribute_type_rec.attribute_type_name,
p_description =>l_attribute_type_rec.description,
p_creation_date =>l_attribute_type_rec.creation_date,
p_created_by =>l_attribute_type_rec.created_by,
p_last_update_date =>l_attribute_type_rec.last_update_date,
p_last_updated_by =>l_attribute_type_rec.last_updated_by,
p_last_update_login =>l_attribute_type_rec.last_update_login
);
PROCEDURE Update_Content_Type(
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_Content_Type_Rec IN Ibc_Ctype_Pvt.Content_Type_Rec_Type ,--:= Ibc_Ctype_Pvt.G_MISS_Content_Type_Rec,
P_Attribute_Type_Tbl IN Ibc_Ctype_Pvt.Attribute_Type_Tbl_Type ,--:= Ibc_Ctype_Pvt.G_Miss_Attribute_Type_Tbl,
x_Content_Type_Rec OUT NOCOPY Ibc_Ctype_Pvt.Content_Type_Rec_Type,
x_Attribute_Type_Tbl OUT NOCOPY Ibc_Ctype_Pvt.Attribute_Type_Tbl_Type,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
CURSOR C_Content_Type(p_Content_Type_Code IN VARCHAR2) IS
SELECT
Content_Type_Code
FROM ibc_content_types_b
WHERE Content_Type_Code = p_Content_Type_Code;
SELECT '1'
FROM IBC_ATTRIBUTE_TYPES_B
WHERE content_type_code = p_content_type_code
AND attribute_type_code = p_attribute_type_code;
l_api_name VARCHAR2(50) := 'Update_Content_Type';
Ibc_Content_Types_Pkg.UPDATE_ROW (
p_CONTENT_TYPE_CODE =>l_content_type_rec.CONTENT_TYPE_CODE,
p_CONTENT_TYPE_STATUS =>l_content_type_rec.CONTENT_TYPE_STATUS,
p_APPLICATION_ID =>l_content_type_rec.APPLICATION_ID,
p_REQUEST_ID =>l_content_type_rec.REQUEST_ID,
p_OBJECT_VERSION_NUMBER =>l_content_type_rec.oBJECT_VERSION_NUMBER,
p_CONTENT_TYPE_name =>l_content_type_rec.CONTENT_TYPE_name,
p_DESCRIPTION =>l_content_type_rec.DESCRIPTION,
p_LAST_UPDATE_DATE =>l_content_type_rec.LAST_UPDATE_DATE,
p_LAST_UPDATED_BY =>l_content_type_rec.LAST_UPDATED_BY,
p_LAST_UPDATE_LOGIN =>l_content_type_rec.LAST_UPDATE_LOGIN);
ELSIF l_Attribute_Type_Tbl(i).OPERATION_CODE = 'UPDATE' THEN
Update_Attribute_Type(
P_Api_Version_Number =>P_Api_Version_Number,
P_Init_Msg_List =>P_Init_Msg_List,
P_Commit =>P_Commit,
P_Validation_Level =>Fnd_Api.G_VALID_LEVEL_FULL,
P_Attribute_Type_Rec =>l_Attribute_Type_Tbl(i),
X_Return_Status =>X_Return_Status,
X_Msg_Count =>X_Msg_Count,
X_Msg_Data =>X_Msg_Data);
ELSIF l_Attribute_Type_Tbl(i).OPERATION_CODE = 'DELETE' THEN
Delete_Attribute_Type(
P_Api_Version_Number =>P_Api_Version_Number,
P_Init_Msg_List =>P_Init_Msg_List,
P_Commit =>P_Commit,
P_Validation_Level =>Fnd_Api.G_VALID_LEVEL_FULL,
P_Attribute_Type_Rec =>l_Attribute_Type_Tbl(i),
X_Return_Status =>X_Return_Status,
X_Msg_Count =>X_Msg_Count,
X_Msg_Data =>X_Msg_Data);
Fnd_Message.Set_name('IBC', 'IBC_UPDATE_ERROR');
END Update_Content_type;
PROCEDURE Delete_Content_Type(
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_Content_Type_Code IN VARCHAR2,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
CURSOR C_Content_Type IS
SELECT
Content_Type_Code
FROM ibc_content_types_b
WHERE Content_Type_Code = p_Content_Type_Code;
SELECT attribute_Type_code
FROM IBC_ATTRIBUTE_TYPES_B
WHERE Reference_code = p_content_type_code;
l_api_name VARCHAR2(50) := 'Delete_Content_Type';
Fnd_Message.Set_name('IBC', 'Cannot Find Record to be Deleted');
Ibc_Attribute_Types_Pkg.delete_rows (
p_content_type_code =>p_content_type_code
);
Ibc_Content_Types_Pkg.DELETE_ROW (
p_CONTENT_TYPE_CODE =>p_CONTENT_TYPE_CODE);
Fnd_Message.Set_name('IBC', 'IBC_DELETE_ERROR');
END Delete_Content_Type;
PROCEDURE Update_Attribute_Type(
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_Attribute_Type_Rec IN Ibc_Ctype_Pvt.Attribute_Type_Rec_Type,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
CURSOR C_Content_Type(p_Content_Type_Code IN VARCHAR2) IS
SELECT Content_Type_Code
FROM ibc_content_types_b
WHERE Content_Type_Code = p_Content_Type_Code;
SELECT '1'
FROM IBC_ATTRIBUTE_TYPES_B
WHERE content_type_code = p_content_type_code
AND attribute_type_code = p_attribute_type_code;
l_api_name VARCHAR2(50) := 'Update_Attribute_Type';
Ibc_Attribute_Types_Pkg.Update_row (
p_attribute_type_code =>l_attribute_type_rec.attribute_type_code,
p_content_type_code =>l_attribute_type_rec.content_type_code,
p_data_type_code =>l_attribute_type_rec.data_type_code,
p_data_length =>l_attribute_type_rec.data_length,
p_min_instances =>l_attribute_type_rec.min_instances,
p_max_instances =>l_attribute_type_rec.max_instances,
p_reference_code =>l_attribute_type_rec.reference_code,
p_default_value =>l_attribute_type_rec.default_value,
p_updateable_flag =>l_attribute_type_rec.updateable_flag,
p_object_version_number =>l_attribute_type_rec.object_version_number,
p_attribute_type_name =>l_attribute_type_rec.attribute_type_name,
p_description =>l_attribute_type_rec.description,
p_last_update_date =>l_attribute_type_rec.last_update_date,
p_last_updated_by =>l_attribute_type_rec.last_updated_by,
p_last_update_login =>l_attribute_type_rec.last_update_login
);
END Update_attribute_type;
PROCEDURE Delete_Attribute_Type(
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_Attribute_Type_Rec IN Ibc_Ctype_Pvt.Attribute_Type_Rec_Type,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
l_return_status VARCHAR2(1) := Fnd_Api.G_RET_STS_SUCCESS;
l_api_name VARCHAR2(50) := 'Delete_Attribute_Type';
Fnd_Message.Set_name('IBC', 'Name OR Description Cannot be Deleted');
Ibc_Attribute_Types_Pkg.delete_row (
p_attribute_type_code =>l_attribute_type_rec.attribute_type_code,
p_content_type_code =>l_attribute_type_rec.content_type_code
);
END Delete_Attribute_Type;
SELECT A.flex_value_set_id,validation_type
FROM IBC_ATTRIBUTE_TYPES_B A, fnd_flex_value_sets F
WHERE a.flex_value_set_id = F.flex_value_set_id
AND A.attribute_type_code = p_attribute_type_code
AND A.content_type_code = p_content_type_code;
lx_select VARCHAR2(32000);
Fnd_Flex_Val_Api.get_table_vset_select
(p_value_set_id => l_value_set_id,
x_select =>lx_select,
x_mapping_code =>lx_mapping_code,
x_success =>lx_success);
Fnd_Flex_Val_Api.get_independent_vset_select
(p_value_set_id => l_value_set_id,
x_select =>lx_select,
x_mapping_code =>lx_mapping_code,
x_success =>lx_success);
lx_select := REPLACE(UPPER(lx_select),'FROM','BULK COLLECT INTO :tab1,:tab2,:tab3 FROM ');
EXECUTE IMMEDIATE 'BEGIN ' || lx_select || '; END;'
,x_UPDATEABLE_FLAG OUT NOCOPY JTF_VARCHAR2_TABLE_100 -- 16 Varchar2(1)
,x_CREATED_BY OUT NOCOPY JTF_NUMBER_TABLE -- 17
,x_CREATION_DATE OUT NOCOPY JTF_DATE_TABLE -- 18
,x_LAST_UPDATED_BY OUT NOCOPY JTF_NUMBER_TABLE --19
,x_LAST_UPDATE_DATE OUT NOCOPY JTF_DATE_TABLE -- 20
,x_LAST_UPDATE_LOGIN OUT NOCOPY JTF_NUMBER_TABLE --21
,x_OBJECT_VERSION_NUMBER OUT NOCOPY JTF_NUMBER_TABLE --22
,x_return_status OUT NOCOPY VARCHAR2 -- 23
,x_msg_count OUT NOCOPY INTEGER --24
,x_msg_data OUT NOCOPY VARCHAR2 --25
,p_language IN VARCHAR2 --26
)
IS
CURSOR Cur_Content_Type(l_language IN VARCHAR2) IS
SELECT content_type_name
,description
,content_type_status
FROM
IBC_CONTENT_TYPES_TL T,
IBC_CONTENT_TYPES_B B
WHERE
B.CONTENT_TYPE_CODE = T.CONTENT_TYPE_CODE AND
B.CONTENT_TYPE_CODE = p_content_type_Code AND
T.LANGUAGE = l_language;
SELECT B.ATTRIBUTE_TYPE_CODE
,ATTRIBUTE_TYPE_name
,DESCRIPTION
,B.CONTENT_TYPE_CODE
,DATA_TYPE_CODE
,DATA_LENGTH
,MIN_INSTANCES
,MAX_INSTANCES
,Flex_value_set_id
,REFERENCE_CODE
,DEFAULT_VALUE
,UPDATEABLE_FLAG
,B.CREATED_BY
,B.CREATION_DATE
,B.LAST_UPDATED_BY
,B.LAST_UPDATE_DATE
,B.LAST_UPDATE_LOGIN
,B.OBJECT_VERSION_NUMBER
FROM IBC_ATTRIBUTE_TYPES_B B,IBC_ATTRIBUTE_TYPES_TL T
WHERE B.CONTENT_TYPE_CODE = p_Content_type_code
AND B.content_type_code = T.CONTENT_TYPE_CODE
AND B.ATTRIBUTE_TYPE_CODE = T.ATTRIBUTE_TYPE_CODE
AND LANGUAGE = l_language
ORDER BY DISPLAY_ORDER;
SELECT '1' FROM FND_LANGUAGES
WHERE LANGUAGE_CODE = p_language;
,x_UPDATEABLE_FLAG
,x_CREATED_BY
,x_CREATION_DATE
,x_LAST_UPDATED_BY
,x_LAST_UPDATE_DATE
,x_LAST_UPDATE_LOGIN
,x_OBJECT_VERSION_NUMBER;
SELECT
ATTRIBUTE_TYPE_CODE,
ATTRIBUTE_TYPE_name,
CONTENT_TYPE_CODE,
CREATED_BY,
CREATION_DATE,
DATA_LENGTH,
DATA_TYPE_CODE,
DEFAULT_VALUE,
DESCRIPTION,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
MAX_INSTANCES,
MIN_INSTANCES,
OBJECT_VERSION_NUMBER,
REFERENCE_CODE,
UPDATEABLE_FLAG
INTO
l_Attribute_Type_Rec.ATTRIBUTE_TYPE_CODE,
l_Attribute_Type_Rec.ATTRIBUTE_TYPE_name,
l_Attribute_Type_Rec.CONTENT_TYPE_CODE,
l_Attribute_Type_Rec.CREATED_BY,
l_Attribute_Type_Rec.CREATION_DATE,
l_Attribute_Type_Rec.DATA_LENGTH,
l_Attribute_Type_Rec.DATA_TYPE_CODE,
l_Attribute_Type_Rec.DEFAULT_VALUE,
l_Attribute_Type_Rec.DESCRIPTION,
l_Attribute_Type_Rec.LAST_UPDATED_BY,
l_Attribute_Type_Rec.LAST_UPDATE_DATE,
l_Attribute_Type_Rec.LAST_UPDATE_LOGIN,
l_Attribute_Type_Rec.MAX_INSTANCES,
l_Attribute_Type_Rec.MIN_INSTANCES,
l_Attribute_Type_Rec.OBJECT_VERSION_NUMBER,
l_Attribute_Type_Rec.REFERENCE_CODE,
l_Attribute_Type_Rec.UPDATEABLE_FLAG
FROM IBC_ATTRIBUTE_TYPES_VL
WHERE attribute_type_code = p_attribute_type_code
AND content_type_code = p_content_type_code;
SELECT flex_value_set_id,validation_type
FROM fnd_flex_value_sets
WHERE flex_value_set_id = p_flex_value_set_id;
lx_select VARCHAR2(32000);
SELECT '1'
FROM TABLE(CAST(l_code AS JTF_VARCHAR2_TABLE_300)) A
WHERE A.COLUMN_VALUE = p_flex_value_code;
SELECT id_column_name,meaning_column_name
FROM fnd_flex_validation_tables
WHERE flex_value_set_id=p_flex_value_set_id;
Fnd_Flex_Val_Api.get_table_vset_select
(p_value_set_id => l_value_set_id,
x_select =>lx_select,
x_mapping_code =>lx_mapping_code,
x_success =>lx_success);
lx_select := REPLACE(UPPER(lx_select),'FROM','BULK COLLECT INTO :tab1,:tab2,:tab3 FROM ');
EXECUTE IMMEDIATE 'BEGIN ' || lx_select || '; END;'
lx_select := REPLACE(UPPER(lx_select),'FROM','BULK COLLECT INTO :tab1 FROM ');
EXECUTE IMMEDIATE 'BEGIN ' || lx_select || '; END;'
lx_select := REPLACE(UPPER(lx_select),'FROM','BULK COLLECT INTO :tab1,:tab2 FROM ');
EXECUTE IMMEDIATE 'BEGIN ' || lx_select || '; END;'
Fnd_Flex_Val_Api.get_independent_vset_select
(p_value_set_id => l_value_set_id,
x_select =>lx_select,
x_mapping_code =>lx_mapping_code,
x_success =>lx_success);
lx_select := REPLACE(UPPER(lx_select),'FROM','BULK COLLECT INTO :tab1,:tab2,:tab3 FROM ');
EXECUTE IMMEDIATE 'BEGIN ' || lx_select || '; END;'
,x_select OUT NOCOPY VARCHAR2 --4
,X_Return_Status OUT NOCOPY VARCHAR2 --6
,X_Msg_Count OUT NOCOPY NUMBER -- 7
,X_Msg_Data OUT NOCOPY VARCHAR2 -- 8
) IS
CURSOR C_flex IS
SELECT flex_value_set_id,validation_type
FROM fnd_flex_value_sets F
WHERE F.flex_value_set_id = p_flex_value_set_id;
lx_select VARCHAR2(32000);
Fnd_Flex_Val_Api.get_table_vset_select
(p_value_set_id => l_value_set_id,
x_select =>lx_select,
x_mapping_code =>lx_mapping_code,
x_success =>lx_success);
Fnd_Flex_Val_Api.get_independent_vset_select
(p_value_set_id => l_value_set_id,
x_select =>lx_select,
x_mapping_code =>lx_mapping_code,
x_success =>lx_success);
x_select := lx_select;