[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
pc_last_update_login CONSTANT NUMBER := FND_GLOBAL.Login_Id;
e_cannot_delete_object EXCEPTION;
SELECT count(*)
INTO v_count
FROM fem_object_types_b
WHERE object_type_code = p_object_type_code;
SELECT count(*)
INTO v_count
FROM fnd_lookup_values
WHERE lookup_type = 'FEM_OBJECT_ORIGIN_DSC'
AND lookup_code = p_object_origin_code;
SELECT count(*)
INTO v_count
FROM fnd_lookup_values
WHERE lookup_type = 'FEM_OBJECT_ACCESS_DSC'
AND lookup_code = p_object_access_code;
SELECT folder_name
INTO v_folder_name
FROM fem_folders_vl
WHERE folder_id = p_folder_id;
SELECT count(*)
INTO v_count
FROM fem_user_folders
WHERE user_id = pc_user_id
AND folder_id = p_folder_id
AND write_flag = 'Y';
SELECT count(*)
INTO v_count
FROM fem_global_vs_combos_vl
WHERE global_vs_combo_id = p_local_vs_combo_id;
SELECT fem_object_id_seq.nextval
INTO x_object_id
FROM dual;
FEM_OBJECT_CATALOG_PKG.INSERT_ROW (
X_ROWID => v_rowid,
X_OBJECT_ID => x_object_id,
X_OBJECT_TYPE_CODE => p_object_type_code,
X_FOLDER_ID => p_folder_id,
X_LOCAL_VS_COMBO_ID => p_local_vs_combo_id,
X_OBJECT_ACCESS_CODE => p_object_access_code,
X_OBJECT_ORIGIN_CODE => p_object_origin_code,
X_OBJECT_VERSION_NUMBER => pc_object_version_number,
X_OBJECT_NAME => p_object_name,
X_DESCRIPTION => p_description,
X_CREATION_DATE => sysdate,
X_CREATED_BY => pc_user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => pc_user_id,
X_LAST_UPDATE_LOGIN => pc_last_update_login);
SELECT count(*)
INTO v_count
FROM fnd_lookup_values
WHERE lookup_type = 'FEM_OBJECT_ORIGIN_DSC'
AND lookup_code = p_object_origin_code;
SELECT object_type_code
INTO v_object_type_code
FROM fem_object_catalog_b
WHERE object_id = p_object_id;
SELECT count(*)
INTO v_count
FROM fem_object_catalog_b o, fem_user_folders f
WHERE o.object_id = p_object_id
AND (o.object_access_code = 'W' OR o.created_by = pc_user_id)
AND o.folder_id = f.folder_id
AND f.user_id = pc_user_id
AND f.write_flag = 'Y';
SELECT fem_object_definition_id_seq.nextval
INTO x_object_definition_id
FROM dual;
SELECT DECODE(t.workflow_enabled_flag,'Y','NEW','NOT_APPLICABLE')
INTO v_approval_status_code
FROM fem_object_types t, fem_object_catalog_b o
WHERE o.object_id = p_object_id
AND o.object_type_code = t.object_type_code;
FEM_OBJECT_DEFINITION_PKG.INSERT_ROW (
X_ROWID => v_rowid,
X_OBJECT_DEFINITION_ID => x_object_definition_id,
X_OBJECT_VERSION_NUMBER => pc_object_version_number,
X_OBJECT_ID => p_object_id,
X_EFFECTIVE_START_DATE => p_effective_start_date,
X_EFFECTIVE_END_DATE => p_effective_end_date,
X_OBJECT_ORIGIN_CODE => p_object_origin_code,
X_APPROVAL_STATUS_CODE => v_approval_status_code,
X_OLD_APPROVED_COPY_FLAG => 'N',
X_OLD_APPROVED_COPY_OBJ_DEF_ID => null,
X_APPROVED_BY => null,
X_APPROVAL_DATE => null,
X_DISPLAY_NAME => p_obj_def_name,
X_DESCRIPTION => p_obj_def_name,
X_CREATION_DATE => sysdate,
X_CREATED_BY => pc_user_id,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => pc_user_id,
X_LAST_UPDATE_LOGIN => null);
SELECT object_definition_id, display_name, effective_start_date, effective_end_date
FROM fem_object_definition_vl
WHERE object_id = p_object_id;
PROCEDURE delete_object (x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
p_api_version IN NUMBER,
p_commit IN VARCHAR2,
p_object_id IN NUMBER)
IS
c_api_name CONSTANT VARCHAR2(30) := 'delete_object';
v_can_delete_object VARCHAR2(1);
SAVEPOINT delete_object_pub;
SELECT object_type_code
INTO v_object_type_code
FROM fem_object_catalog_b
WHERE object_id = p_object_id;
SELECT count(*)
INTO v_count
FROM fem_object_catalog_b o, fem_user_folders f
WHERE o.object_id = p_object_id
AND (o.object_access_code = 'W' OR o.created_by = pc_user_id)
AND o.folder_id = f.folder_id
AND f.user_id = pc_user_id
AND f.write_flag = 'Y';
/* Check if can delete object. */
fem_pl_pkg.can_delete_object (
p_object_id => p_object_id,
x_can_delete_obj => v_can_delete_object,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
IF v_can_delete_object = 'T' THEN
/* get the object name to display in the message */
SELECT object_name
INTO v_object_name
FROM fem_object_catalog_vl
WHERE object_id = p_object_id;
DELETE fem_object_dependencies
WHERE object_definition_id IN (
SELECT object_definition_id
FROM fem_object_definition_b
WHERE object_id = p_object_id);
DELETE fem_object_definition_tl
WHERE object_id = p_object_id;
DELETE fem_object_definition_b
WHERE object_id = p_object_id;
DELETE fem_object_catalog_tl
WHERE object_id = p_object_id;
DELETE fem_object_catalog_b
WHERE object_id = p_object_id;
p_msg_name => 'FEM_DELETED_OBJ_TXT',p_token1 => 'OBJECT_NAME',
p_value1 => v_object_name, p_trans1 => 'N');
/* (v_can_delete_object = 'F') */
RAISE e_cannot_delete_object;
WHEN e_cannot_delete_object THEN
x_return_status := pc_ret_sts_error;
p_msg_name => 'FEM_CANNOT_DELETE_OBJ_ERR'
,p_token1 => 'OBJECT'
,p_value1 => p_object_id
,p_trans1 => 'N');
ROLLBACK TO delete_object_pub;
ROLLBACK TO delete_object_pub;
ROLLBACK TO delete_object_pub;
END delete_object;