[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_INSERT_STMT_TYPE CONSTANT VARCHAR2(10) := 'INSERT';
SELECT interface_table_name
INTO l_interface_table_name
FROM fem_tables_b
WHERE table_name = l_table_name;
v_param_list.DELETE;
p_last_update_login => p_login_id,
p_program_id => p_program_id,
p_program_login_id => p_login_id,
p_program_application_id => p_program_application_id,
p_exec_mode_code => p_exec_mode,
p_table_name => p_table_name,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status);
p_last_update_login => p_login_id,
p_exec_mode_code => p_exec_mode,
x_exec_state => x_exec_state,
x_prev_request_id => x_prev_req_id,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status);
p_last_update_login => p_login_id,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status);
p_statement_type => G_INSERT_STMT_TYPE,
p_num_of_output_rows => 0,
p_user_id => p_user_id,
p_last_update_login => p_login_id,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status);
g_xdim_info_tbl.DELETE;
SELECT gv.value_set_id,
xd.member_b_table_name,
xd.member_col,
xd.member_display_code_col,
xd.member_data_type_code,
tc.column_name,
tc.interface_column_name
BULK COLLECT INTO g_xdim_info_tbl
FROM fem_tab_columns_v tc,
fem_xdim_dimensions xd,
fem_global_vs_combo_defs gv
WHERE tc.table_name = p_table_name
AND tc.fem_data_type_code = 'DIMENSION'
AND tc.column_name NOT IN ('CREATED_BY_OBJECT_ID','LAST_UPDATED_BY_OBJECT_ID','LEDGER_ID','CAL_PERIOD_ID','DATASET_CODE','SOURCE_SYSTEM_CODE')
AND xd.dimension_id = tc.dimension_id
AND xd.dimension_id = gv.dimension_id (+)
AND gv.global_vs_combo_id (+) = l_global_vs_combo_id;
x_insert_interim_sql OUT NOCOPY VARCHAR2,
x_update_interim_error_sql OUT NOCOPY VARCHAR2,
x_insert_target_sql OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2
) IS
C_MODULE CONSTANT FND_LOG_MESSAGES.module%TYPE :=
'fem.plsql.fem_source_data_loader_pkg.prepare_dynamic_sql';
'INSERT INTO fem_source_data_interim_gt (INTERFACE_ROWID';
'SELECT rowid';
x_update_interim_error_sql :=
'UPDATE fem_source_data_interim_gt g SET g.error_code = '
||'''FEM_SD_LDR_INV_DIM_MEMBER'' WHERE EXISTS'
||'(SELECT null FROM '||p_interface_table_name||' i'
||' WHERE i.rowid = g.interface_rowid AND ( ';
x_update_interim_error_sql := x_update_interim_error_sql||' OR ';
x_update_interim_error_sql := x_update_interim_error_sql
||'(i.'||g_xdim_info_tbl(i).int_disp_code_col
||' IS NOT NULL AND g.DIM'||to_char(i)||' IS NULL)';
x_insert_interim_sql := l_dummy1_sql || l_dummy2_sql;
x_update_interim_error_sql := x_update_interim_error_sql||' ) )';
||x_update_interim_error_sql);
SELECT tc.column_name, tc.interface_column_name
BULK COLLECT INTO l_nondim_target_col_tbl, l_nondim_int_col_tbl
FROM fem_tab_columns_v tc
WHERE tc.table_name = p_target_table_name
AND tc.fem_data_type_code <> 'DIMENSION'
AND tc.interface_column_name is not null
AND tc.column_name NOT IN ('CREATED_BY_REQUEST_ID','LAST_UPDATED_BY_REQUEST_ID');
x_insert_target_sql := null;
'INSERT INTO '||p_target_table_name
||' (CREATED_BY_OBJECT_ID,LAST_UPDATED_BY_OBJECT_ID,CREATED_BY_REQUEST_ID,LAST_UPDATED_BY_REQUEST_ID'
||',LEDGER_ID,CAL_PERIOD_ID,DATASET_CODE,SOURCE_SYSTEM_CODE';
'SELECT '||to_char(p_object_id)||','||to_char(p_object_id)||','
||to_char(p_request_id)||','||to_char(p_request_id)||','
||to_char(p_ledger_id)||','||to_char(p_cal_period_id)||','
||to_char(p_dataset_code)||','||to_char(p_source_system_code);
x_insert_target_sql := l_dummy1_sql || l_dummy2_sql;
p_msg_text => 'SQL to insert to target is:');
l_merge_stmt_part2 := ' SELECT ' ;
SELECT ftc.column_name, ftc.interface_column_name, atc.nullable
BULK COLLECT INTO g_proc_keys_tbl
FROM fem_tab_columns_b ftc, all_tab_columns atc, fem_tab_column_prop tcp
WHERE atc.table_name = l_db_tab_name
AND atc.owner = l_tab_owner
AND ftc.table_name = p_target_table_name
AND atc.column_name = ftc.column_name
AND ftc.table_name = tcp.table_name
AND ftc.column_name = tcp.column_name
AND tcp.column_property_code = 'PROCESSING_KEY';
l_merge_stmt_part4 := ' WHEN MATCHED THEN UPDATE SET ' ;
l_dummy4_sql := l_dummy4_sql || 'D.LAST_UPDATED_BY_OBJECT_ID = ' || TO_CHAR(p_object_id);
l_dummy4_sql := l_dummy4_sql || ', D.LAST_UPDATED_BY_REQUEST_ID = ' || TO_CHAR(p_request_id);
l_dummy5_sql := REPLACE (x_insert_target_sql, l_dummy5_sql);
l_dummy5_sql := REPLACE ( l_dummy5_sql, 'SELECT', 'VALUES (');
x_insert_target_sql := l_merge_stmt;
p_msg_text => 'SELECT clause - l_merge_stmt_part2 ');
g_proc_keys_tbl.DELETE;
l_nondim_target_col_tbl.DELETE;
l_nondim_int_col_tbl.DELETE;
l_insert_interim_sql VARCHAR2(30000);
l_update_interim_error_sql VARCHAR2(30000);
l_insert_target_sql VARCHAR2(30000);
x_insert_interim_sql => l_insert_interim_sql,
x_update_interim_error_sql => l_update_interim_error_sql,
x_insert_target_sql => l_insert_target_sql,
x_return_status => l_return_status);
l_insert_interim_sql := l_insert_interim_sql||' AND '||p_data_slice_predicate;
p_msg_text => 'SQL to insert into interim is '||l_insert_interim_sql);
EXECUTE IMMEDIATE l_insert_interim_sql
USING l_slc_val1,l_slc_val2,l_slc_val3,l_slc_val4;
EXECUTE IMMEDIATE l_insert_interim_sql
USING l_slc_val1,l_slc_val2,l_slc_val3;
EXECUTE IMMEDIATE l_insert_interim_sql
USING l_slc_val1,l_slc_val2;
EXECUTE IMMEDIATE l_insert_interim_sql
USING l_slc_val1;
l_dynamic_sql := 'UPDATE fem_source_data_interim_gt g SET (';
l_dummy1_sql := '(SELECT ';
p_msg_text => 'SQL to update interim errors is '||l_dynamic_sql);
EXECUTE IMMEDIATE l_update_interim_error_sql;
l_dynamic_sql := 'UPDATE '||p_interface_table_name||' i'
||' SET i.status='
||' (SELECT ''FEM_SD_LDR_INV_DIM_MEMBER: ''||t.error_code'
||' FROM fem_source_data_interim_gt t'
||' WHERE t.interface_rowid=i.rowid'
||' AND t.error_code IS NOT NULL)'
||' WHERE i.rowid IN'
||' (SELECT g.interface_rowid FROM fem_source_data_interim_gt g'
||' WHERE g.error_code IS NOT NULL)';
l_dynamic_sql := 'UPDATE '||p_interface_table_name||' i'
||' SET i.status = ''FEM_SD_LDR_INV_DIM_MEMBER'''
||' WHERE i.rowid IN'
||' (SELECT g.interface_rowid FROM fem_source_data_interim_gt g'
||' WHERE g.error_code IS NOT NULL)';
p_msg_text => 'Number of error rows updated in interface table is '
||' not the same as the number of error rows updated in the interim table.');
EXECUTE IMMEDIATE l_insert_target_sql;
p_msg_text => 'Number of rows inserted into the target table is '
||to_char(l_slc_num_rows_loaded));
p_msg_text => 'Unexpected error occured when inserting into'
||' the target table.');
l_dynamic_sql := 'UPDATE '||p_interface_table_name||' i'
||' SET i.status = ''FEM_SD_LDR_DUPLICATE_ROW'''
||' WHERE i.rowid IN'
||' (SELECT g.interface_rowid FROM fem_source_data_interim_gt g'
||' WHERE g.error_code IS NULL)';
l_dynamic_sql := 'DELETE FROM '||p_interface_table_name||' i'
||' WHERE i.rowid IN (SELECT g.interface_rowid'
||' FROM fem_source_data_interim_gt g WHERE g.error_code IS NULL)';
p_msg_text => 'Number of rows deleted from the interface table is '
||to_char(l_dummy_num));
p_msg_text => 'The number of rows deleted from the interface table'
||' is not the same as the number of rows inserted into the target table.');
SELECT nvl(SUM(rows_processed),0), nvl(SUM(rows_loaded),0), nvl(SUM(rows_rejected),0)
INTO l_num_errors_reprocessed, l_num_rows_loaded, l_num_rows_rejected
FROM fem_mp_process_ctl_t
WHERE req_id = p_request_id;
FEM_PL_PKG.Update_Num_of_Output_Rows(
p_api_version => G_API_VERSION,
p_commit => G_TRUE,
p_request_id => p_request_id,
p_object_id => p_object_id,
p_table_name => p_table_name,
p_statement_type => G_INSERT_STMT_TYPE,
p_num_of_output_rows => l_num_rows_loaded,
p_user_id => p_user_id,
p_last_update_login => p_login_id,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status);
p_msg_text => 'Call to FEM_PL_PKG.Update_Num_of_Output_Rows returned with status '
||l_return_status);
FEM_PL_PKG.Update_Obj_Exec_Status(
p_api_version => G_API_VERSION,
p_commit => G_TRUE,
p_request_id => p_request_id,
p_object_id => p_object_id,
p_exec_status_code => p_exec_status,
p_user_id => p_user_id,
p_last_update_login => p_login_id,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status);
FEM_PL_PKG.Update_Obj_Exec_Errors(
p_api_version => G_API_VERSION,
p_commit => G_TRUE,
p_request_id => p_request_id,
p_object_id => p_object_id,
p_errors_reported => l_num_rows_rejected,
p_errors_reprocessed => l_num_errors_reprocessed,
p_user_id => p_user_id,
p_last_update_login => p_login_id,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status);
p_msg_text => 'Call to FEM_PL_PKG.Update_Obj_Exec_Errors returned with status '
||l_return_status);
FEM_PL_PKG.Update_Request_Status(
p_api_version => G_API_VERSION,
p_commit => G_TRUE,
p_request_id => p_request_id,
p_exec_status_code => p_exec_status,
p_user_id => p_user_id,
p_last_update_login => p_login_id,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
x_return_status => l_return_status);
p_msg_text => 'Call to FEM_PL_PKG.Update_Request_Status returned with status '
||l_return_status);
p_msg_text => 'Updated Data Location status to COMPLETE');
FEM_MULTI_PROC_PKG.Delete_Data_Slices(
p_req_id => p_request_id);
g_xdim_info_tbl.DELETE;
SELECT o.object_id, t.table_name
INTO x_object_id, x_table_name
FROM fem_object_definition_b od, fem_object_catalog_b o,
fem_user_folders f, fem_data_loader_objects d,
fem_table_class_assignmt_v t
WHERE od.object_definition_id = p_obj_def_id
AND od.object_id = o.object_id
AND o.object_type_code = 'SOURCE_DATA_LOADER'
AND o.folder_id = f.folder_id
AND f.user_id = FND_GLOBAL.user_id
AND d.object_id = o.object_id
AND d.table_name = t.table_name
AND table_classification_code = 'SOURCE_DATA_TABLE'
AND old_approved_copy_flag = 'N';
SELECT object_type_code
INTO l_object_type_code
FROM fem_object_catalog_b
WHERE object_id = x_object_id
AND object_type_code = p_object_type;
SELECT COUNT(*)
INTO l_count
FROM fem_table_class_assignmt_v
WHERE table_name = p_table_name
AND table_classification_code = p_table_classification;
SELECT COUNT(*)
INTO l_count
FROM fnd_lookup_values
WHERE lookup_type = 'FEM_PL_EXEC_MODE_DSC'
AND lookup_code = p_exec_mode
AND language = USERENV('LANG')
AND view_application_id = 274
AND security_group_id =
fnd_global.lookup_security_group(lookup_type, view_application_id)
AND enabled_flag = 'Y'
AND lookup_code IN ('R','E','S');
SELECT ledger_display_code
INTO x_ledger_dc
FROM fem_ledgers_b
WHERE ledger_id = p_ledger_id
AND enabled_flag = 'Y'
AND personal_flag = 'N';
SELECT dimension_id
INTO l_ledger_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'LEDGER';
SELECT dim_attribute_numeric_member
INTO x_ledger_per_hier_obj_def_id
FROM fem_ledgers_attr
WHERE attribute_id = l_dim_attr_id
AND version_id = l_dim_attr_ver_id
AND ledger_id = p_ledger_id;
SELECT object_id
INTO l_ledger_per_hier_obj_id
FROM fem_object_definition_b
WHERE object_definition_id = x_ledger_per_hier_obj_def_id;
SELECT calendar_id
INTO x_ledger_calendar_id
FROM fem_hierarchies
WHERE hierarchy_obj_id = l_ledger_per_hier_obj_id;
SELECT calendar_id, dimension_group_id
INTO l_cal_per_calendar_id, l_cal_per_dim_grp_id
FROM fem_cal_periods_b
WHERE cal_period_id = p_cal_period_id
AND enabled_flag = 'Y'
AND personal_flag = 'N';
SELECT dimension_group_display_code
INTO x_calp_dim_grp_dc
FROM fem_dimension_grps_b
WHERE dimension_group_id = l_cal_per_dim_grp_id;
SELECT dimension_id
INTO l_cal_per_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'CAL_PERIOD';
SELECT date_assign_value
INTO x_cal_per_end_date
FROM fem_cal_periods_attr
WHERE attribute_id = l_dim_attr_id
AND version_id = l_dim_attr_ver_id
AND cal_period_id = p_cal_period_id;
SELECT number_assign_value
INTO x_cal_per_number
FROM fem_cal_periods_attr
WHERE attribute_id = l_dim_attr_id
AND version_id = l_dim_attr_ver_id
AND cal_period_id = p_cal_period_id;
SELECT dataset_display_code
INTO x_dataset_dc
FROM fem_datasets_b
WHERE dataset_code = p_dataset_code
AND enabled_flag = 'Y'
AND personal_flag = 'N';
SELECT dimension_id
INTO l_dataset_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'DATASET';
SELECT 0
INTO l_return_code
FROM fem_datasets_attr
WHERE attribute_id = l_dim_attr_id
AND version_id = l_dim_attr_ver_id
AND dataset_code = p_dataset_code;
SELECT source_system_display_code
INTO x_source_system_dc
FROM fem_source_systems_b
WHERE source_system_code = p_source_system_code
AND enabled_flag = 'Y'
AND personal_flag = 'N';