The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
Directory_Node_Code
FROM ibc_Directory_Nodes_b
WHERE Directory_Node_Code = P_Directory_Node_Rec.Directory_Node_Code
AND directory_node_id IN (SELECT child_dir_node_id
FROM IBC_DIRECTORY_NODE_RELS WHERE parent_dir_node_id = P_parent_dir_node_id);
Ibc_Directory_Nodes_Pkg.INSERT_ROW (
x_ROWID => lx_rowid,
px_DIRECTORY_NODE_ID => l_Directory_Node_rec.DIRECTORY_NODE_ID ,
p_NODE_TYPE => l_Directory_Node_rec.NODE_TYPE ,
p_NODE_STATUS => l_Directory_Node_rec.NODE_STATUS,
p_DIRECTORY_PATH => l_Directory_Node_rec.DIRECTORY_PATH,
p_AVAILABLE_DATE => NULL,
p_EXPIRATION_DATE => NULL,
p_HIDDEN_FLAG => NULL,
p_DIRECTORY_NODE_CODE => l_Directory_Node_rec.DIRECTORY_NODE_CODE ,
p_DIRECTORY_NODE_NAME => l_Directory_Node_rec.DIRECTORY_NODE_NAME ,
p_DESCRIPTION => l_Directory_Node_rec.DESCRIPTION ,
p_CREATED_BY => l_Directory_Node_rec.CREATED_BY ,
p_CREATION_DATE => l_Directory_Node_rec.CREATION_DATE ,
p_LAST_UPDATED_BY => l_Directory_Node_rec.LAST_UPDATED_BY ,
p_LAST_UPDATE_DATE => l_Directory_Node_rec.LAST_UPDATE_DATE ,
p_LAST_UPDATE_LOGIN => l_Directory_Node_rec.LAST_UPDATE_LOGIN,
p_OBJECT_VERSION_NUMBER => l_Directory_Node_rec.OBJECT_VERSION_NUMBER);
Ibc_Directory_Node_Rels_Pkg.INSERT_ROW (
x_ROWID => lx_rowid,
px_DIRECTORY_NODE_REL_ID => lx_DIRECTORY_NODE_REL_ID ,
p_CHILD_DIR_NODE_ID => l_Directory_Node_rec.DIRECTORY_NODE_ID ,
p_PARENT_DIR_NODE_ID => p_PARENT_DIR_NODE_ID ,
p_CREATED_BY => l_Directory_Node_rec.CREATED_BY ,
p_CREATION_DATE => l_Directory_Node_rec.CREATION_DATE ,
p_LAST_UPDATED_BY => l_Directory_Node_rec.LAST_UPDATED_BY ,
p_LAST_UPDATE_DATE => l_Directory_Node_rec.LAST_UPDATE_DATE ,
p_LAST_UPDATE_LOGIN => l_Directory_Node_rec.LAST_UPDATE_LOGIN ,
p_OBJECT_VERSION_NUMBER => l_Directory_Node_rec.OBJECT_VERSION_NUMBER);
FND_MESSAGE.Set_Name('IBC', 'IBC_INSERT_ERROR');
SELECT
Directory_Node_Code
FROM ibc_Directory_Nodes_b
WHERE Directory_Node_Code = l_Directory_Node_Rec.Directory_Node_Code
AND directory_node_id IN (SELECT child_dir_node_id
FROM IBC_DIRECTORY_NODE_RELS WHERE parent_dir_node_id= P_New_parent_node_id);
SELECT
DIRECTORY_NODE_REL_ID
INTO
l_DIRECTORY_NODE_REL_ID
FROM IBC_DIRECTORY_NODE_RELS
WHERE PARENT_DIR_NODE_ID = p_CURRENT_PARENT_NODE_ID
AND CHILD_DIR_NODE_ID = p_DIRECTORY_NODE_ID;
Ibc_Directory_Node_Rels_Pkg.UPDATE_ROW (
p_DIRECTORY_NODE_REL_ID => l_DIRECTORY_NODE_REL_ID ,
p_CHILD_DIR_NODE_ID => p_DIRECTORY_NODE_ID ,
p_PARENT_DIR_NODE_ID => p_New_PARENT_NODE_ID ,
p_LAST_UPDATED_BY => l_Directory_Node_rec.LAST_UPDATED_BY ,
p_LAST_UPDATE_DATE => l_Directory_Node_rec.LAST_UPDATE_DATE ,
p_LAST_UPDATE_LOGIN => l_Directory_Node_rec.LAST_UPDATE_LOGIN ,
p_OBJECT_VERSION_NUMBER => l_Directory_Node_rec.OBJECT_VERSION_NUMBER);
FND_MESSAGE.Set_Name('IBC', 'IBC_UPDATE_ERROR');
PROCEDURE Update_Directory_Node(
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_Directory_Node_Rec IN Ibc_Directory_Node_Grp.Directory_Node_Rec_Type := Ibc_Directory_Node_Grp.G_MISS_Directory_Node_Rec,
p_parent_dir_node_id IN NUMBER,
x_Directory_Node_Rec OUT NOCOPY Ibc_Directory_Node_Grp.Directory_Node_Rec_Type,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
CURSOR C_Directory_Node IS
SELECT
Directory_Node_Code
FROM ibc_Directory_Nodes_b
WHERE Directory_Node_Code = P_Directory_Node_Rec.Directory_Node_Code
AND directory_node_id IN (SELECT child_dir_node_id
FROM IBC_DIRECTORY_NODE_RELS WHERE parent_dir_node_id= P_parent_dir_node_id)
AND Directory_Node_id <> P_Directory_Node_Rec.directory_node_id;
l_api_name VARCHAR2(50) := 'Update_Directory_Node';
Ibc_Directory_Nodes_Pkg.UPDATE_ROW (
p_DIRECTORY_NODE_ID => l_Directory_Node_rec.DIRECTORY_NODE_ID ,
p_NODE_TYPE => l_Directory_Node_rec.NODE_TYPE ,
p_NODE_STATUS => l_Directory_Node_rec.NODE_STATUS,
p_DIRECTORY_PATH => l_Directory_Node_rec.DIRECTORY_PATH,
p_DIRECTORY_NODE_CODE => l_Directory_Node_rec.DIRECTORY_NODE_CODE ,
p_DIRECTORY_NODE_NAME => l_Directory_Node_rec.DIRECTORY_NODE_NAME ,
p_DESCRIPTION => l_Directory_Node_rec.DESCRIPTION ,
p_LAST_UPDATED_BY => l_Directory_Node_rec.LAST_UPDATED_BY ,
p_LAST_UPDATE_DATE => l_Directory_Node_rec.LAST_UPDATE_DATE ,
p_LAST_UPDATE_LOGIN => l_Directory_Node_rec.LAST_UPDATE_LOGIN,
p_OBJECT_VERSION_NUMBER => l_Directory_Node_rec.OBJECT_VERSION_NUMBER);
FND_MESSAGE.Set_Name('IBC', 'IBC_UPDATE_ERROR');
END Update_Directory_Node;
PROCEDURE delete_Directory_Node(
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_Directory_Node_id IN NUMBER,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2
)
IS
CURSOR C_Directory_Node IS
SELECT
Directory_Node_id
FROM ibc_Directory_Nodes_b
WHERE Directory_Node_id = p_Directory_Node_id;
SELECT LPAD(' ',3*(LEVEL-1)) || parent_dir_node_id,child_dir_node_id,directory_node_rel_id
FROM ibc_directory_node_rels
START WITH parent_dir_node_id = p_Directory_Node_id
CONNECT BY PRIOR child_dir_node_id = parent_dir_node_id;
SELECT directory_node_id FROM ibc_content_items
WHERE directory_node_id = p_ci_dir_node_id;
l_api_name VARCHAR2(50) := 'Delete_Directory_Node';
FND_MESSAGE.Set_Name('IBC', 'Cannot Find Record to be Deleted');
Ibc_Directory_Nodes_Pkg.DELETE_ROW (
p_Directory_Node_ID =>i_rec.child_dir_Node_id);
Ibc_Directory_Node_Rels_Pkg.delete_row(
p_directory_node_rel_id =>i_rec.directory_node_rel_id);
FND_MESSAGE.Set_Name('IBC', 'IBC_DELETE_ERROR');
END delete_Directory_Node;
SELECT
DIRECTORY_NODE_ID ,
DIRECTORY_NODE_CODE ,
NODE_TYPE ,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATE_LOGIN ,
OBJECT_VERSION_NUMBER ,
DIRECTORY_NODE_NAME ,
DESCRIPTION
INTO
l_directory_node_rec.DIRECTORY_NODE_ID ,
l_directory_node_rec.DIRECTORY_NODE_CODE ,
l_directory_node_rec.NODE_TYPE ,
l_directory_node_rec.CREATED_BY ,
l_directory_node_rec.CREATION_DATE ,
l_directory_node_rec.LAST_UPDATED_BY ,
l_directory_node_rec.LAST_UPDATE_DATE ,
l_directory_node_rec.LAST_UPDATE_LOGIN ,
l_directory_node_rec.OBJECT_VERSION_NUMBER ,
l_directory_node_rec.DIRECTORY_NODE_NAME ,
l_directory_node_rec.DESCRIPTION
FROM IBC_DIRECTORY_NODES_VL
WHERE directory_node_id = p_directory_node_id;