[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DECODE(data_type,'NUMBER','TO_NUMBER','')
FROM fem_column_requiremnt_vl
WHERE column_name = p_column_name;
p_insert_list OUT NOCOPY LONG,
p_select_list OUT NOCOPY LONG,
p_from_clause OUT NOCOPY LONG,
p_where_clause OUT NOCOPY LONG,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
CURSOR get_table_list_cur IS
SELECT c.source_table_name,
c.source_column_name,
c.target_table_name,
c.target_column_name,
c.dimension_id,
c.attribute_id,
c.attribute_version_id,
c.aggregation_method,
c.constant_numeric_value,
c.constant_alphanumeric_value,
c.constant_date_value,
c.data_population_method_code,
DECODE( c.aggregation_method,
'AVERAGE', 'AVG',
'SUM', 'SUM',
'AVERAGE_BY_DAYS_WEIGHTED', 'AVG',
'BEGINNING','MIN',
'LAST','MAX',
NULL) agg_method,
c.eng_proc_param,
c.parameter_flag
FROM fem_col_population_tmplt_b c
WHERE c.col_pop_templt_obj_def_id = p_object_def_id
AND ( c.data_population_method_code <> 'UNASSIGNED' OR
(c.data_population_method_code = 'UNASSIGNED' AND parameter_flag = 'Y')
)
ORDER BY data_population_method_code;
l_select_col VARCHAR2(1000);
SELECT dimension_id,
attribute_dimension_id,
attribute_value_column_name,
attribute_data_type_code
FROM fem_dim_attributes_vl
WHERE attribute_id = p_attr_id;
SELECT member_col,
member_data_type_code,
member_vl_object_name,
attribute_table_name
FROM fem_xdim_dimensions
WHERE dimension_id = p_dim_id;
SELECT usage_code
FROM fem_table_class_assignmt a,
fem_table_class_usages b
WHERE a.table_classification_code = b.table_classification_code
AND a.table_name = g_src_tab_name;
SELECT table_id
FROM fem_tables_b
WHERE table_name = c_src_tab_name;
SELECT fem_data_type_code
FROM fem_tab_columns_vl
WHERE table_name = g_src_tab_name
AND column_name = c_source_col_name;
FOR dim_rec IN (SELECT dimension_id,
dimension_varchar_label
FROM fem_xdim_dimensions_vl
WHERE dimension_varchar_label IN ('CAL_PERIOD','LEDGER'))
LOOP
IF dim_rec.dimension_varchar_label = 'CAL_PERIOD' THEN
l_attribute_varchar_label := 'CAL_PERIOD_END_DATE';
SELECT att.attribute_id
,ver.version_id
INTO l_attribute_id
,l_attr_version_id
FROM fem_dim_attributes_b att
,fem_dim_attr_versions_b ver
WHERE att.dimension_id = dim_rec.dimension_id
AND att.attribute_varchar_label = l_attribute_varchar_label
AND ver.attribute_id = att.attribute_id
AND ver.default_version_flag = 'Y';
SELECT date_assign_value
INTO g_exch_rate_date
FROM Fem_Cal_Periods_Attr
WHERE attribute_id = l_attribute_id
AND version_id = l_attr_version_id
AND cal_period_id = l_member_id;
SELECT dim_attribute_varchar_member
INTO g_func_curr_code
FROM Fem_Ledgers_Attr
WHERE attribute_id = l_attribute_id
AND version_id = l_attr_version_id
AND ledger_id = l_member_id;
SELECT output_dataset_code
INTO l_op_dataset_code
FROM fem_ds_input_output_defs
WHERE dataset_io_obj_def_id = p_dataset_grp_obj_def_id;
SELECT acct_ownership_id
INTO l_acct_ownership_id
FROM fem_acct_ownshp_b
WHERE acct_ownership_display_code = l_disp_code;
l_select_col := l_alias_mem || '.' || l_member_col;
l_select_col := l_alias_attr || '.' || l_attr_val_col;
l_select_col := l_agg_method || '(' || l_select_col || ')';
l_select_col := get_param_value(l_source_col_name, l_op_dataset_code);
l_select_col := get_param_value(l_source_col_name, p_cal_period_id);
l_select_col := get_param_value(l_source_col_name, p_ledger_id);
l_select_col := g_src_alias || '.' || l_source_col_name;
l_select_col := 'DECODE(' || g_src_alias || '.currency_code,' || '''' || g_func_curr_code
|| '''' || ',' || g_src_alias ||'.'|| l_source_col_name || ',' ||
l_convert_condition || ')';
l_select_col := g_src_alias || '.' || l_source_col_name;
l_select_col := l_agg_method || '(' || l_select_col || ')';
l_select_col := get_table_list_rec.constant_numeric_value ||
get_table_list_rec.constant_alphanumeric_value ||
get_table_list_rec.constant_date_value ;
l_select_col := '''' || l_select_col || '''';
l_select_col := 'TO_NUMBER(' || '''' || l_acct_ownership_id || '''' || ')';
l_select_col := 'TO_NUMBER(' || '''' || p_created_by_object_id || '''' || ')';
l_select_col := 'TO_NUMBER(' || '''' || p_created_by_request_id || '''' || ')';
l_select_col := '''' || 'ACCOUNT_RELATIONSHIP' || '''';
l_select_col := '''' || 'CUSTOMER_AGGREGATION' || '''';
WHEN 'LAST_UPDATED_BY_OBJECT_ID' THEN
l_select_col := 'TO_NUMBER(' || '''' || p_created_by_object_id || '''' || ')';
WHEN 'LAST_UPDATED_BY_REQUEST_ID' THEN
l_select_col := 'TO_NUMBER(' || '''' || p_created_by_request_id || '''' || ')';
l_select_col := 'TO_NUMBER(''0'')';
l_select_col := 'TO_NUMBER(''1'')';
l_select_col := 'TO_NUMBER(''1'')';
l_select_col := 'TO_NUMBER(''-987654321'')';
l_select_col := 'TO_NUMBER(''1'')';
WHEN 'SOURCE_SYSTEM_CODE' THEN l_select_col := 'TO_NUMBER(' || '''' || p_source_system_code || '''' || ')';
WHEN 'TABLE_ID' THEN l_select_col := 'TO_NUMBER(' || '''' || g_table_id || '''' || ')';
ELSE l_select_col := l_source_col_name;
l_select_col := '''' || g_func_curr_code || '''';
l_select_col := '{{{CUSTOMER_ID}}}';
p_insert_list := p_insert_list || ', ' || get_table_list_rec.target_column_name;
p_select_list := p_select_list || ', ' || l_select_col;
p_insert_list := LTRIM(p_insert_list,',');
p_select_list := LTRIM(p_select_list,',');
p_insert_list := NULL;
p_select_list := NULL;
,p_msg_text => 'p_insert_list ' || p_insert_list);
,p_msg_text => 'p_select_list ' || p_select_list);
SELECT 1
FROM dual
WHERE EXISTS
(SELECT aggregation_method
FROM fem_col_population_tmplt_vl
WHERE col_pop_templt_obj_def_id = p_object_def_id
AND aggregation_method <> 'NOAGG');
p_selection_param IN NUMBER,
p_effective_date IN VARCHAR2,
p_condition_obj_id IN NUMBER,
p_condition_sel_param IN VARCHAR2,
p_load_sec_relns IN VARCHAR2,
p_dataset_grp_obj_def_id IN NUMBER,
p_cal_period_id IN NUMBER,
p_ledger_id IN NUMBER,
p_source_system_code IN NUMBER,
p_created_by_object_id IN NUMBER,
p_created_by_request_id IN NUMBER,
p_insert_list OUT NOCOPY LONG,
p_select_list OUT NOCOPY LONG,
p_from_clause OUT NOCOPY LONG,
p_where_clause OUT NOCOPY LONG,
p_con_where_clause OUT NOCOPY LONG,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_err_code NUMBER;
l_select_list LONG;
l_insert_list LONG;
l_insert_list,
l_select_list,
p_from_clause,
p_where_clause,
x_msg_count,
x_msg_data,
x_return_status);
p_insert_list := 'INSERT INTO ' || g_tgt_tab_name || '(' || l_insert_list || ')';
p_select_list := 'SELECT ' || l_select_list;
IF p_selection_param = 0 THEN
fem_engines_pkg.tech_message (p_severity => g_log_level_1
,p_module => g_block||'.'||l_api_name
,p_msg_text => 'Step 2: Condition Predicate Preparation');