[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
DELETE FROM fem_tab_class_status_gt
WHERE table_name = p_tab_name;
p_msg_text => 'Delete from FEM_TAB_CLASS_STATUS_GT failed with: '||SQLERRM);
DELETE FROM fem_tab_class_errors_gt
WHERE table_name = p_tab_name;
p_msg_text => 'Delete from FEM_TAB_CLASS_ERRORS_GT failed with: '||SQLERRM);
INSERT INTO fem_tab_class_errors_gt
(TABLE_NAME, TABLE_CLASSIFICATION_CODE, MESSAGE_TEXT)
VALUES
(p_tab_name, p_tab_class_cd, nvl(v_msg_data,'?'));
INSERT INTO fem_tab_class_errors_gt
(TABLE_NAME, TABLE_CLASSIFICATION_CODE, MESSAGE_TEXT)
VALUES
(p_tab_name, p_tab_class_cd, nvl(v_msg_data,'?'));
p_msg_text => 'Insert into FEM_TAB_CLASS_ERRORS_GT failed with: '||SQLERRM);
INSERT INTO fem_tab_class_status_gt
(TABLE_NAME, TABLE_CLASSIFICATION_CODE, VALID_FLAG)
VALUES
(p_tab_name, p_tab_class_cd, decode(p_passed_validation,c_true,'Y','N'));
p_msg_text => 'Insert into FEM_TAB_CLASS_STATUS_GT failed with: '||SQLERRM);
SELECT count(*)
INTO v_count
FROM fem_data_loader_objects
WHERE table_name = p_tab_name;
SELECT display_name
INTO v_tab_disp_name
FROM fem_tables_vl
WHERE table_name = p_tab_name;
INSERT INTO fem_mp_process_options (
OBJECT_TYPE_CODE,
STEP_NAME,
OBJECT_ID,
DATA_SLICE_TYPE_CODE,
PROCESS_DATA_SLICES_CD,
PROCESS_PARTITION_CD,
NUM_OF_PROCESSES,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER )
VALUES (
'SOURCE_DATA_LOADER',
'ALL',
v_obj_id,
2,
1000,
0,
1,
c_user_id,
sysdate,
c_user_id,
sysdate,
FND_GLOBAL.Login_Id,
1 );
INSERT INTO fem_data_loader_objects (
OBJECT_ID,
TABLE_NAME,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN )
VALUES (
v_obj_id,
p_tab_name,
c_user_id,
sysdate,
c_user_id,
sysdate,
FND_GLOBAL.Login_Id );
SELECT column_name
FROM fem_table_prop_col_req
WHERE table_property_code = p_tab_prop_cd;
SELECT meaning
INTO v_obj_name
FROM fnd_lookup_values_vl
WHERE lookup_type = 'FEM_TAB_CLASS_OBJECTS'
AND lookup_code = p_prop_type;
v_sql := 'SELECT column_name'
||' FROM fem_table_prop_col_req'
||' WHERE table_property_code = :1 ';
||'(SELECT column_name'
||' FROM fem_tab_columns_b'
||' WHERE table_name = :2'
||' AND enabled_flag = ''Y'')';
||'(SELECT column_name'
||' FROM fem_tab_column_prop'
||' WHERE table_name = :2'
||' AND column_property_code = '''||v_col_prop_cd||''')';
v_sql := 'SELECT count(*)';
v_sql := 'SELECT t.column_name, nvl(x.value_set_required_flag,''N'')'
||' FROM fem_tab_columns_b t, fem_xdim_dimensions x'
||' WHERE t.dimension_id = x.dimension_id(+)'
||' AND t.table_name = :1'
||' AND enabled_flag = ''Y'''
||' AND t.column_name NOT IN '
||'(SELECT column_name'
||' FROM fem_table_prop_col_req'
||' WHERE table_property_code = :2) ';
||'(SELECT column_name'
||' FROM fem_tab_column_prop'
||' WHERE table_name = '''||p_tab_name||''''
||' AND column_property_code = '''||v_col_prop_cd||''')';
SELECT a.column_name
FROM all_tab_columns a, fem_tab_column_prop p
WHERE a.table_name = p_tab_name
AND a.owner = p_owner
AND a.nullable = 'Y'
AND a.table_name = p.table_name
AND a.column_name = p.column_name
AND p.column_property_code = 'PROCESSING_KEY'
ORDER BY a.column_name;
SELECT object_type
INTO v_obj_type
FROM user_objects
WHERE object_name = p_tab_name;
SELECT count(*)
INTO v_count
FROM user_updatable_columns
WHERE table_name = p_tab_name
AND (updatable = 'NO'
OR insertable = 'NO'
OR deletable = 'NO');
SELECT index_name
FROM all_indexes
WHERE table_name = p_name
AND table_owner = p_owner
AND uniqueness = 'UNIQUE';
SELECT column_name, data_type, data_length, data_precision
FROM all_tab_columns
WHERE owner = p_owner
AND table_name = p_name
AND nullable = 'N'
AND column_name IN ('CAL_PERIOD_NUMBER',
'CALP_DIM_GRP_DISPLAY_CODE',
'CAL_PERIOD_END_DATE',
'STATUS')
ORDER BY column_name;
SELECT f.column_name
FROM fem_tab_columns_v f, all_tab_columns a
WHERE f.table_name = p_name
AND f.column_name NOT IN ('CREATED_BY_REQUEST_ID',
'LAST_UPDATED_BY_REQUEST_ID',
'CREATED_BY_OBJECT_ID',
'LAST_UPDATED_BY_OBJECT_ID',
'CAL_PERIOD_ID')
AND f.interface_column_name IS NULL
AND f.table_name = a.table_name
AND a.owner = p_owner
AND a.column_name = f.column_name
AND (f.fem_data_type_code = 'DIMENSION' OR a.nullable = 'N')
ORDER BY f.column_name;
SELECT F.column_name, F.interface_column_name, F.fem_data_type_code, F.dimension_id,
A.data_type, A.data_length, A.data_precision, A.data_scale
FROM fem_tab_columns_b F, all_tab_columns A
WHERE F.table_name = p_name
AND F.column_name NOT IN ('CREATED_BY_REQUEST_ID',
'LAST_UPDATED_BY_REQUEST_ID',
'CREATED_BY_OBJECT_ID',
'LAST_UPDATED_BY_OBJECT_ID',
'CAL_PERIOD_ID')
AND F.interface_column_name IS NOT NULL
AND F.table_name = A.table_name
AND F.column_name = A.column_name
AND A.owner = p_tab_owner;
SELECT interface_table_name
INTO v_int_tab_name
FROM fem_tables_b
WHERE table_name = p_tab_name
AND interface_table_name IS NOT NULL;
SELECT member_b_table_name, member_col, member_display_code_col, dimension_varchar_label
INTO v_dim_mbr_table, v_dim_mbr_col, v_dim_mbr_dc_col, v_dim_label
FROM fem_xdim_dimensions_vl
WHERE dimension_id = col.dimension_id;
SELECT data_type, data_length, data_precision, data_scale
INTO v_data_type, v_data_length, v_data_precision, v_data_scale
FROM all_tab_columns
WHERE owner = v_int_owner
AND table_name = v_int_db_tab_name
AND column_name = col.interface_column_name;
SELECT data_type,data_length
INTO v_dim_dc_data_type,v_dim_dc_data_length
FROM all_tab_columns
WHERE owner = v_mbr_b_owner
AND table_name = v_mbr_b_table_name
AND column_name = v_dim_mbr_dc_col;
SELECT count(*)
INTO v_count
FROM all_ind_columns i, fem_tab_columns_b c
WHERE i.index_name = idx.index_name
AND i.table_owner = v_int_owner
AND i.table_name = v_int_db_tab_name
AND c.table_name = p_tab_name
AND i.column_name = c.interface_column_name
AND i.column_name NOT IN ('CAL_PERIOD_NUMBER',
'CALP_DIM_GRP_DISPLAY_CODE',
'CAL_PERIOD_END_DATE')
AND c.column_name NOT IN
(SELECT column_name
FROM fem_tab_column_prop
WHERE table_name = p_tab_name
AND column_property_code = v_col_prop
AND column_name NOT IN ('CAL_PERIOD_ID',
'CREATED_BY_REQUEST_ID',
'LAST_UPDATED_BY_REQUEST_ID',
'CREATED_BY_OBJECT_ID',
'LAST_UPDATED_BY_OBJECT_ID'));
SELECT count(*)
INTO v_count
FROM fem_tab_column_prop
WHERE table_name = p_tab_name
AND column_property_code = v_col_prop
AND column_name NOT IN ('CAL_PERIOD_ID',
'CREATED_BY_REQUEST_ID',
'LAST_UPDATED_BY_REQUEST_ID',
'CREATED_BY_OBJECT_ID',
'LAST_UPDATED_BY_OBJECT_ID')
AND column_name NOT IN
(SELECT c.column_name
FROM all_ind_columns i, fem_tab_columns_b c
WHERE i.index_name = idx.index_name
AND i.table_owner = v_int_owner
AND i.table_name = v_int_db_tab_name
AND c.table_name = p_tab_name
AND i.column_name = c.interface_column_name
AND i.column_name NOT IN ('CAL_PERIOD_NUMBER',
'CALP_DIM_GRP_DISPLAY_CODE','CAL_PERIOD_END_DATE'));
SELECT count(*)
INTO v_count
FROM fem_tab_column_prop
WHERE table_name = p_tab_name
AND column_property_code = v_col_prop
AND column_name = 'CAL_PERIOD_ID';
SELECT count(*)
INTO v_count
FROM all_ind_columns i
WHERE i.index_name = idx.index_name
AND i.table_owner = v_int_owner
AND i.table_name = v_int_db_tab_name
AND i.column_name IN ('CAL_PERIOD_NUMBER',
'CALP_DIM_GRP_DISPLAY_CODE','CAL_PERIOD_END_DATE');
* Procedure: Delete_Obj_Tuning_Options
* ====================================
*
***************************************************************************
**************************************************************************/
PROCEDURE Delete_Obj_Tuning_Options (
p_api_version IN NUMBER DEFAULT c_api_version,
p_init_msg_list IN VARCHAR2 DEFAULT c_false,
p_commit IN VARCHAR2 DEFAULT c_false,
p_encoded IN VARCHAR2 DEFAULT c_true,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_object_id IN NUMBER
) IS
C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
'fem.plsql.fem_admin_util_pkg.delete_obj_tuning_options';
C_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Obj_Tuning_Options';
SELECT process_data_slices_cd
FROM fem_mp_process_options
WHERE object_id = cp_object_id
FOR UPDATE;
DELETE FROM fem_mp_process_options
WHERE CURRENT OF c1;
DELETE FROM fem_mp_data_slices
WHERE process_data_slices_cd = v_process_ds_cd
AND NOT EXISTS
(SELECT NULL
FROM fem_mp_process_options
WHERE process_data_slices_cd = v_process_ds_cd);
DELETE FROM fem_mp_data_slice_cols
WHERE process_data_slices_cd = v_process_ds_cd
AND NOT EXISTS
(SELECT NULL
FROM fem_mp_process_options
WHERE process_data_slices_cd = v_process_ds_cd);
DELETE FROM fem_pb_parameters p
WHERE object_id = p_object_id;
END Delete_Obj_Tuning_Options;
SELECT DISTINCT t.column_name,
t.fem_data_type_code col_fem_data_type_code,
r.fem_data_type_code req_fem_data_type_code,
a.data_length||', '||a.data_precision||', '||a.data_scale col_data_length,
r.data_length||', '||r.data_precision||', '||r.data_scale req_data_length,
a.data_type col_data_type, r.data_type req_data_type,
a.nullable col_nullable, tp.nullable_flag req_nullable
FROM fem_tab_columns_b t, fem_column_requiremnt_b r, all_tab_columns a,
fem_table_prop_col_req tp, fem_table_class_prop tc
WHERE t.table_name = p_syn_name
AND t.column_name = r.column_name
AND a.table_name = p_db_name
AND a.owner = p_tab_owner
AND t.column_name = a.column_name
AND t.column_name = tp.column_name
AND tp.table_property_code = tc.table_property_code
AND tc.table_classification_code = p_tab_class_cd;
SELECT fem_data_type_name
INTO v_fem_data_type_name
FROM fem_data_types_vl
WHERE fem_data_type_code = req_cols.req_fem_data_type_code;
SELECT proc_key_index_name, proc_key_index_owner
INTO v_idx_name, v_idx_owner
FROM fem_tables_b
WHERE table_name = p_tab_name;
SELECT count(*)
INTO v_count
FROM fem_tab_column_prop
WHERE table_name = p_tab_name
AND column_property_code = 'PROCESSING_KEY';
SELECT count(*)
INTO v_count
FROM all_indexes
WHERE index_name= v_idx_name
AND owner = v_idx_owner
AND table_name = p_db_tab_name
AND table_owner = p_tab_owner
AND uniqueness = 'UNIQUE';
SELECT count(*)
INTO v_count
FROM all_ind_columns
WHERE index_owner = v_idx_owner
AND index_name = v_idx_name
AND table_owner = p_tab_owner
AND table_name = p_db_tab_name
AND column_name NOT IN
(SELECT column_name
FROM fem_tab_column_prop
WHERE table_name = p_tab_name
AND column_property_code = 'PROCESSING_KEY');
SELECT count(*)
INTO v_count
FROM fem_tab_column_prop
WHERE table_name = p_tab_name
AND column_property_code = 'PROCESSING_KEY'
AND column_name NOT IN
(SELECT column_name
FROM all_ind_columns
WHERE index_owner = v_idx_owner
AND index_name = v_idx_name
AND table_owner = p_tab_owner
AND table_name = p_db_tab_name);
SELECT column_name, dimension_id
FROM fem_tab_columns_b
WHERE table_name = p_name
AND fem_data_type_code = 'DIMENSION'
AND column_name IN ('COMPANY_COST_CENTER_ORG_ID'
,'NATURAL_ACCOUNT_ID'
,'LINE_ITEM_ID'
,'PRODUCT_ID'
,'CHANNEL_ID'
,'PROJECT_ID'
,'CUSTOMER_ID'
,'ENTITY_ID'
,'INTERCOMPANY_ID'
,'TASK_ID'
,'USER_DIM1_ID'
,'USER_DIM2_ID'
,'USER_DIM3_ID'
,'USER_DIM4_ID'
,'USER_DIM5_ID'
,'USER_DIM6_ID'
,'USER_DIM7_ID'
,'USER_DIM8_ID'
,'USER_DIM9_ID'
,'USER_DIM10_ID')
ORDER BY column_name;
SELECT count(*)
INTO v_count
FROM fem_xdim_dimensions_vl
WHERE dimension_id = col.dimension_id
AND value_set_required_flag = 'Y'
AND dimension_varchar_label not in ('COMPANY','COST_CENTER');
SELECT p.table_property_code, p.property_type, p.column_req_type
FROM fem_table_class_prop c, fem_table_properties p
WHERE c.table_classification_code = p_class
AND c.table_property_code = p.table_property_code
AND p.property_type <> 'OTHER';
SELECT DISTINCT S.stored_procedure_name
FROM fem_table_class_prop C,
fem_table_prop_stp S
WHERE C.table_classification_code = p_class
AND C.table_property_code = S.table_property_code;
SELECT lookup_code
FROM fnd_lookup_values_vl
WHERE lookup_type = 'FEM_TABLE_CLASSIFICATION_DSC';
/* Select only those classifications for which properties do not need validation
EXCEPT(
'FEM_ADMIN_UTIL_PKG.VALIDATE_TABLE_NAME_RESTRICT',
'FEM_ADMIN_UTIL_PKG.VALIDATE_DATA_LOADER',
'FEM_ADMIN_UTIL_PKG.VALIDATE_EDITABLE')
*/
CURSOR c_table_classes IS
SELECT lookup_code
FROM fnd_lookup_values_vl flv
WHERE flv.lookup_type = 'FEM_TABLE_CLASSIFICATION_DSC'
AND not exists(
select ftcp.table_classification_code
from fem_table_class_prop ftcp,
fem_table_prop_stp ftps
where ftcp.table_classification_code = flv.lookup_code
and ftcp.table_property_code = ftps.table_property_code
and ftps.stored_procedure_name in(
'FEM_ADMIN_UTIL_PKG.VALIDATE_TABLE_NAME_RESTRICT',
'FEM_ADMIN_UTIL_PKG.VALIDATE_DATA_LOADER',
'FEM_ADMIN_UTIL_PKG.VALIDATE_EDITABLE')
);
SELECT tiig.table_name, tig.owner
INTO x_tab_name,x_tab_owner
FROM fem_tab_indx_info_gt tiig,
fem_tables_b ftb,
fem_tab_info_gt tig
WHERE ftb.table_name = p_view_name
AND ftb.PROC_KEY_INDEX_NAME = tiig.INDEX_NAME
AND tig.table_name = tiig.table_name
AND tig.owner is NOT NULL
AND ROWNUM = 1;
SELECT tiig.table_name, tig.owner
INTO x_tab_name,x_tab_owner
FROM fem_tab_indx_info_gt tiig,
fem_tab_info_gt tig
WHERE tig.table_name = tiig.table_name
AND tiig.INDEX_NAME = p_index_name
AND tig.owner is NOT NULL
AND ROWNUM = 1;
SELECT p.table_property_code, p.property_type, p.column_req_type
FROM fem_table_class_prop c, fem_table_properties p
WHERE c.table_classification_code = p_class
AND c.table_property_code = p.table_property_code
AND p.property_type <> 'OTHER';
SELECT DISTINCT S.stored_procedure_name
FROM fem_table_class_prop C,
fem_table_prop_stp S
WHERE C.table_classification_code = p_class
AND C.table_property_code = S.table_property_code;