[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Insert statement for FEM_GLOBAL_VS_COMBOS_PKG
| -- this fix will preserve the original signature
| for this API so that it is backward compatible
| with OGL by employing a default for the
| global combo display_code. If the user passes
| null for the display code, it set it = global combo name.
| 03-MAY-05 tmoore Bug 4036498 - Added Get_Dim_Member_ID.
| This function returns a dimension
| member ID
| 13-MAY-05 Rflippo Bug4367375 - some required attributes missing
| a default assignment for the
| generate_default_load_member proc
| 23-MAY-05 Rflippo Bug4316406 modify generate_default_load_member
| for new req attribute security_enabled_flag
| 15-JUN-05 gcheng 4417618. Created the Get_Default_Dim_Member procedures.
| 30-JUN-05 gcheng 4143586. Added another version to the overloaded
| Generate_Default_Load_Member procedure.
| Also extensively modified the existing versions.
| Also modified Get_Default_Dim_Member to make sure
| it exits when OA Param validation fails.
| 26-JUL-05 ghall 4503014. Added user messages for Attribute errors
| in Relative_Cal_Period; changed WHEN OTHERS exception
SELECT application_group_id
INTO v_app_grp_id
FROM fem_applications
WHERE application_id = c_resp_app_id;
SELECT ledger_id
INTO v_ledger_id
FROM fem_ledgers_b
WHERE ledger_id = p_ledger_id;
SELECT dimension_id
INTO v_dimension_id
FROM fem_xdim_dimensions
WHERE dimension_id = p_dimension_id;
SELECT dim_attribute_numeric_member
INTO v_global_vs_id
FROM fem_ledgers_attr f
WHERE f.ledger_id = v_ledger_id
AND f.attribute_id =
(SELECT attribute_id
FROM fem_dim_attributes_b
WHERE attribute_varchar_label = 'GLOBAL_VS_COMBO')
AND f.version_id =
(SELECT version_id
FROM fem_dim_attr_versions_b
WHERE attribute_id = f.attribute_id
AND default_version_flag = 'Y');
SELECT dim_attribute_numeric_member
INTO v_global_vs_id
FROM fem_ledgers_attr f
WHERE f.ledger_id = v_ledger_id
AND f.attribute_id =
(SELECT attribute_id
FROM fem_dim_attributes_b
WHERE attribute_varchar_label = 'GLOBAL_VS_COMBO')
AND f.version_id =
(SELECT version_id
FROM fem_dim_attr_versions_b
WHERE attribute_id = f.attribute_id
AND default_version_flag = 'Y');
SELECT count(*)
INTO v_count
FROM fem_xdim_dimensions
WHERE dimension_id = p_dimension_id;
SELECT dim_attribute_numeric_member
INTO v_global_vs_id
FROM fem_ledgers_attr f
WHERE f.ledger_id = v_current_session_ledger_id
AND f.attribute_id =
(SELECT attribute_id
FROM fem_dim_attributes_b
WHERE attribute_varchar_label = 'GLOBAL_VS_COMBO')
AND f.version_id =
(SELECT version_id
FROM fem_dim_attr_versions_b
WHERE attribute_id = f.attribute_id
AND default_version_flag = 'Y');
SELECT value_set_id
INTO v_dim_vs_id
FROM fem_global_vs_combo_defs
WHERE global_vs_combo_id = v_global_vs_id
AND dimension_id = p_dimension_id;
SELECT value_set_id
INTO v_vs_id
FROM fem_global_vs_combo_defs
WHERE global_vs_combo_id = v_global_vs_id
AND dimension_id = p_dimension_id;
SELECT value_set_id
INTO v_vs_id
FROM fem_global_vs_combo_defs
WHERE global_vs_combo_id = v_global_vs_id
AND dimension_id = p_dimension_id;
SELECT local_vs_combo_id
INTO v_rule_vs_id
FROM fem_object_catalog_b
WHERE object_id = p_object_id;
SELECT application_group_id
INTO v_app_grp
FROM fem_applications;
SELECT global_vs_combo_id
INTO v_global_vs_id
FROM fem_global_vs_combos_b;
SELECT calendar_id, dimension_group_id
INTO v_base_calendar_id, v_base_dimgrp_id
FROM fem_cal_periods_b
WHERE cal_period_id = p_base_cal_period_id;
SELECT dimension_id, dimension_name
INTO v_dim_id, v_dim_name
FROM fem_dimensions_vl
WHERE dimension_varchar_label = 'CALENDAR';
SELECT dim_attribute_varchar_member
INTO v_incl_adj_period
FROM fem_calendars_attr
WHERE attribute_id = v_cal_attr_id
AND version_id = v_cal_attr_ver_id
AND calendar_id = v_base_calendar_id;
SELECT attribute_name
INTO v_cal_attr_name
FROM fem_dim_attributes_vl
WHERE attribute_id = v_cal_attr_id;
SELECT dimension_id, dimension_name
INTO v_dim_id, v_dim_name
FROM fem_dimensions_vl
WHERE dimension_varchar_label = 'CAL_PERIOD';
v_sql := 'SELECT CP.cal_period_id FROM fem_cal_periods_b CP';
SELECT attribute_name
INTO v_cal_attr_name
FROM fem_dim_attributes_vl
WHERE attribute_id = v_cal_attr_id;
SELECT dimension_id
INTO v_calp_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label='CAL_PERIOD';
SELECT dimension_id
INTO v_ledger_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label='LEDGER';
SELECT attribute_id
INTO v_ledgerobj_attr_id
FROM fem_dim_attributes_b
WHERE attribute_varchar_label='CAL_PERIOD_HIER_OBJ_DEF_ID'
AND dimension_id = v_ledger_dim_id;
SELECT version_id
INTO v_ledgerobj_vers_id
FROM fem_dim_attr_versions_b
WHERE attribute_id = v_ledgerobj_attr_id
AND default_version_flag='Y';
SELECT dim_attribute_numeric_member
INTO v_ledger_calphier_id
FROM fem_ledgers_attr
WHERE ledger_id = p_ledger_id
AND attribute_id = v_ledgerobj_attr_id
AND version_id = v_ledgerobj_vers_id;
SELECT object_id
INTO v_ledger_calphier_obj_id
FROM fem_object_definition_b
WHERE object_definition_id = v_ledger_calphier_id;
SELECT calendar_id
INTO v_calendar_id
FROM fem_hierarchies
WHERE hierarchy_obj_id = v_ledger_calphier_obj_id;
SELECT attribute_id
INTO v_periodnum_attr_id
FROM fem_dim_attributes_b
WHERE attribute_varchar_label='GL_PERIOD_NUM'
AND dimension_id = v_calp_dim_id;
SELECT attribute_id
INTO v_acctyear_attr_id
FROM fem_dim_attributes_b
WHERE attribute_varchar_label='ACCOUNTING_YEAR'
AND dimension_id = v_calp_dim_id;
SELECT version_id
INTO v_periodnum_vers_id
FROM fem_dim_attr_versions_b
WHERE attribute_id = v_periodnum_attr_id
AND default_version_flag = 'Y';
SELECT version_id
INTO v_acctyear_vers_id
FROM fem_dim_attr_versions_b
WHERE attribute_id = v_acctyear_attr_id
AND default_version_flag = 'Y';
SELECT B.cal_period_id
INTO v_cal_period_id
FROM fem_cal_periods_b B,
fem_cal_periods_attr N,
fem_cal_periods_attr D,
fem_dimension_grps_b G
WHERE N.attribute_id = v_periodnum_attr_id
AND N.version_id = v_periodnum_vers_id
AND N.cal_period_id = B.cal_period_id
AND N.number_assign_value = p_cal_per_num
AND D.attribute_id = v_acctyear_attr_id
AND D.version_id = v_acctyear_vers_id
AND D.cal_period_id = B.cal_period_id
AND D.number_assign_value = p_fiscal_year
AND B.calendar_id = v_calendar_id
AND G.dimension_group_id = B.dimension_group_id
AND G.dimension_group_display_code = p_dim_grp_dc;
SELECT dimension_id
INTO v_calp_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label='CAL_PERIOD';
SELECT dimension_id
INTO v_ledger_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label='LEDGER';
SELECT attribute_id
INTO v_ledgerobj_attr_id
FROM fem_dim_attributes_b
WHERE attribute_varchar_label='CAL_PERIOD_HIER_OBJ_DEF_ID'
AND dimension_id = v_ledger_dim_id;
SELECT version_id
INTO v_ledgerobj_vers_id
FROM fem_dim_attr_versions_b
WHERE attribute_id = v_ledgerobj_attr_id
AND default_version_flag='Y';
SELECT dim_attribute_numeric_member
INTO v_ledger_calphier_id
FROM fem_ledgers_attr
WHERE ledger_id = p_ledger_id
AND attribute_id = v_ledgerobj_attr_id
AND version_id = v_ledgerobj_vers_id;
SELECT object_id
INTO v_ledger_calphier_obj_id
FROM fem_object_definition_b
WHERE object_definition_id = v_ledger_calphier_id;
SELECT calendar_id
INTO v_calendar_id
FROM fem_hierarchies
WHERE hierarchy_obj_id = v_ledger_calphier_obj_id;
SELECT attribute_id
INTO v_periodnum_attr_id
FROM fem_dim_attributes_b
WHERE attribute_varchar_label='GL_PERIOD_NUM'
AND dimension_id = v_calp_dim_id;
SELECT attribute_id
INTO v_enddate_attr_id
FROM fem_dim_attributes_b
WHERE attribute_varchar_label='CAL_PERIOD_END_DATE'
AND dimension_id = v_calp_dim_id;
SELECT version_id
INTO v_periodnum_vers_id
FROM fem_dim_attr_versions_b
WHERE attribute_id = v_periodnum_attr_id
AND default_version_flag = 'Y';
SELECT version_id
INTO v_enddate_vers_id
FROM fem_dim_attr_versions_b
WHERE attribute_id = v_enddate_attr_id
AND default_version_flag = 'Y';
SELECT B.cal_period_id
INTO v_cal_period_id
FROM fem_cal_periods_b B,
fem_cal_periods_attr N,
fem_cal_periods_attr D,
fem_dimension_grps_b G
WHERE N.attribute_id = v_periodnum_attr_id
AND N.version_id = v_periodnum_vers_id
AND N.cal_period_id = B.cal_period_id
AND N.number_assign_value = p_cal_per_num
AND D.attribute_id = v_enddate_attr_id
AND D.version_id = v_enddate_vers_id
AND D.cal_period_id = B.cal_period_id
AND D.date_assign_value = p_cal_per_end_date
AND B.calendar_id = v_calendar_id
AND G.dimension_group_id = B.dimension_group_id
AND G.dimension_group_display_code = p_dim_grp_dc;
SELECT column_name
INTO v_ledger_col
FROM fem_tab_columns_b
WHERE table_name = p_table_name
AND dimension_id =
(SELECT dimension_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'LEDGER');
SELECT column_name
INTO v_cal_per_col
FROM fem_tab_columns_b
WHERE table_name = p_table_name
AND dimension_id =
(SELECT dimension_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'CAL_PERIOD');
SELECT column_name
INTO v_dataset_col
FROM fem_tab_columns_b
WHERE table_name = p_table_name
AND dimension_id =
(SELECT dimension_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'DATASET');
SELECT column_name
INTO v_source_col
FROM fem_tab_columns_b
WHERE table_name = p_table_name
AND dimension_id =
(SELECT dimension_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'SOURCE_SYSTEM');
'SELECT MIN(ledger_id)'||
' FROM '||p_table_name||
' WHERE '||v_ledger_col||' = :b_ledger_id'||
' AND '||v_cal_per_col||' = :b_cal_per_id'||
' AND '||v_dataset_col||' = :b_dataset_cd'||
' AND '||v_source_col||' = :b_source_cd';
SELECT attribute_value_column_name
INTO v_bal_type_col
FROM fem_dim_attributes_b
WHERE dimension_id =
(SELECT dimension_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'DATASET')
AND attribute_varchar_label = 'DATASET_BALANCE_TYPE_CODE';
'SELECT '||v_bal_type_col||
' FROM fem_datasets_attr A,fem_dim_attr_versions_b V'||
' WHERE dataset_code = :b_dataset_cd'||
' AND A.attribute_id = '||
' (SELECT attribute_id'||
' FROM fem_dim_attributes_b'||
' WHERE dimension_id ='||
' (SELECT dimension_id'||
' FROM fem_dimensions_b'||
' WHERE dimension_varchar_label = ''DATASET'')'||
' AND attribute_varchar_label = ''DATASET_BALANCE_TYPE_CODE'')'||
' AND A.attribute_id = V.attribute_id'||
' AND A.version_id = V.version_id'||
' AND V.default_version_flag = ''Y''';
INSERT INTO fem_dl_dimensions
(request_id,
object_id,
ledger_id,
cal_period_id,
dataset_code,
source_system_code,
balance_type_code,
table_name,
avg_balances,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
load_status,
reprocess_errors_flag,
object_version_number)
VALUES
(p_request_id,
p_object_id,
p_ledger_id,
p_cal_per_id,
p_dataset_cd,
p_source_cd,
v_bal_type_cd,
p_table_name,
v_avg_balances,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
p_load_status,
'N',
1);
UPDATE fem_dl_dimensions
SET load_status = p_load_status,
avg_balances = v_avg_balances,
last_updated_by = c_user_id,
last_update_date = sysdate
WHERE request_id = p_request_id
AND object_id = p_object_id
AND ledger_id = p_ledger_id
AND cal_period_id = p_cal_per_id
AND dataset_code = p_dataset_cd
AND source_system_code = p_source_cd
AND table_name = p_table_name;
INSERT INTO fem_dl_trans_curr
(request_id,
object_id,
ledger_id,
cal_period_id,
dataset_code,
source_system_code,
table_name,
translated_currency,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
object_version_number)
VALUES
(p_request_id,
p_object_id,
p_ledger_id,
p_cal_per_id,
p_dataset_cd,
p_source_cd,
p_table_name,
p_trans_curr,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
1);
DELETE FROM fem_dl_dimensions
WHERE request_id = p_request_id
AND object_id = p_object_id;
DELETE FROM fem_dl_trans_curr
WHERE request_id = p_request_id
AND object_id = p_object_id;
DELETE FROM fem_dl_dimensions
WHERE request_id = p_request_id
AND object_id = p_object_id
AND table_name = p_table_name;
DELETE FROM fem_dl_trans_curr
WHERE request_id = p_request_id
AND object_id = p_object_id
AND table_name = p_table_name;
SELECT dimension_varchar_label
INTO v_dim_label
FROM fem_dimensions_b
WHERE dimension_id = p_dim_id;
SELECT member_id_source_code
INTO v_source_cd
FROM fem_xdim_dimensions
WHERE dimension_id = p_dim_id
AND member_id_method_code = 'FUNCTION';
SELECT fem_datasets_b_s.NEXTVAL
INTO v_mem_id FROM dual;
SELECT fem_source_systems_b_s.NEXTVAL
INTO v_mem_id FROM dual;
SELECT fem_calendars_b_s.NEXTVAL
INTO v_mem_id FROM dual;
SELECT fnd_flex_values_s.NEXTVAL
INTO v_mem_id FROM dual;
SELECT gl_sets_of_books_s.NEXTVAL
INTO v_mem_id FROM dual;
SELECT fem_cctr_orgs_b_s.NEXTVAL
INTO v_mem_id FROM dual;
SELECT gl_budget_versions_s.NEXTVAL
INTO v_mem_id FROM dual;
SELECT gl_encumbrance_types_s.NEXTVAL
INTO v_mem_id FROM dual;
SELECT dimension_name
INTO v_dim_name
FROM fem_dimensions_tl
WHERE dimension_id = p_dim_id
AND language = userenv('LANG');
SELECT member_id_source_code
INTO v_source_cd
FROM fem_xdim_dimensions
WHERE dimension_id = p_dim_id
AND member_id_method_code = 'FUNCTION';
SELECT fem_datasets_b_s.NEXTVAL
INTO v_mem_id FROM dual;
SELECT fem_source_systems_b_s.NEXTVAL
INTO v_mem_id FROM dual;
SELECT fem_calendars_b_s.NEXTVAL
INTO v_mem_id FROM dual;
SELECT fnd_flex_values_s.NEXTVAL
INTO v_mem_id FROM dual;
SELECT gl_sets_of_books_s.NEXTVAL
INTO v_mem_id FROM dual;
SELECT fem_cctr_orgs_b_s.NEXTVAL
INTO v_mem_id FROM dual;
SELECT gl_budget_versions_s.NEXTVAL
INTO v_mem_id FROM dual;
SELECT gl_encumbrance_types_s.NEXTVAL
INTO v_mem_id FROM dual;
SELECT dimension_name
INTO v_dim_name
FROM fem_dimensions_tl
WHERE dimension_id = p_dim_id
AND language = userenv('LANG');
SELECT member_id_source_code
INTO v_source_cd
FROM fem_xdim_dimensions
WHERE dimension_id = p_dim_id
AND member_id_method_code = 'FUNCTION';
SELECT fem_datasets_b_s.NEXTVAL
INTO v_mem_id FROM dual;
SELECT fem_source_systems_b_s.NEXTVAL
INTO v_mem_id FROM dual;
SELECT fem_calendars_b_s.NEXTVAL
INTO v_mem_id FROM dual;
SELECT fnd_flex_values_s.NEXTVAL
INTO v_mem_id FROM dual;
SELECT gl_sets_of_books_s.NEXTVAL
INTO v_mem_id FROM dual;
SELECT fem_cctr_orgs_b_s.NEXTVAL
INTO v_mem_id FROM dual;
SELECT gl_budget_versions_s.NEXTVAL
INTO v_mem_id FROM dual;
SELECT gl_encumbrance_types_s.NEXTVAL
INTO v_mem_id FROM dual;
SELECT TO_CHAR(p_end_date,'J')
INTO v_julian_date
FROM dual;
SELECT T.number_assign_value
INTO v_num_of_periods
FROM fem_time_grp_types_attr T,
fem_dimension_grps_b G,
fem_dim_attributes_b A,
fem_dim_attr_versions_b V
WHERE G.dimension_group_id = p_dim_grp_id
AND A.attribute_varchar_label = 'PERIODS_IN_YEAR'
AND A.attribute_id = T.attribute_id
AND G.time_group_type_code = T.time_group_type_code
AND V.attribute_id = T.attribute_id
AND V.version_id = T.version_id
AND V.default_version_flag = 'Y';
SELECT LPAD(TO_CHAR(p_period_num),15,'0')
INTO v_period_chr
FROM dual;
SELECT LPAD(TO_CHAR(calendar_id),5,'0')
INTO v_calendar_chr
FROM fem_calendars_b
WHERE calendar_id = p_calendar_id;
SELECT LPAD(TO_CHAR(time_dimension_group_key),5,'0')
INTO v_dim_grp_key
FROM fem_dimension_grps_b
WHERE dimension_group_id = p_dim_grp_id;
SELECT TO_NUMBER(v_julian_date||v_period_chr||v_calendar_chr||v_dim_grp_key)
INTO v_cal_per_id
FROM dual;
SELECT TO_CHAR(p_end_date,'J')
INTO v_julian_date
FROM dual;
SELECT T.number_assign_value
INTO v_num_of_periods
FROM fem_time_grp_types_attr T,
fem_dimension_grps_b G,
fem_dim_attributes_b A,
fem_dim_attr_versions_b V
WHERE G.dimension_group_display_code = p_dim_grp_dc
AND A.attribute_varchar_label = 'PERIODS_IN_YEAR'
AND A.attribute_id = T.attribute_id
AND G.time_group_type_code = T.time_group_type_code
AND V.attribute_id = T.attribute_id
AND V.version_id = T.version_id
AND V.default_version_flag = 'Y';
SELECT LPAD(TO_CHAR(p_period_num),15,'0')
INTO v_period_chr
FROM dual;
SELECT LPAD(TO_CHAR(calendar_id),5,'0')
INTO v_calendar_chr
FROM fem_calendars_b
WHERE calendar_display_code = p_calendar_dc;
SELECT LPAD(TO_CHAR(time_dimension_group_key),5,'0')
INTO v_dim_grp_key
FROM fem_dimension_grps_b
WHERE dimension_group_display_code = p_dim_grp_dc;
SELECT TO_NUMBER(v_julian_date||v_period_chr||v_calendar_chr||v_dim_grp_key)
INTO v_cal_per_id
FROM dual;
SELECT TO_CHAR(p_end_date,'J')
INTO v_julian_date
FROM dual;
SELECT T.number_assign_value
INTO v_num_of_periods
FROM fem_time_grp_types_attr T,
fem_dimension_grps_b G,
fem_dim_attributes_b A,
fem_dim_attr_versions_b V
WHERE G.dimension_group_id = p_dim_grp_id
AND A.attribute_varchar_label = 'PERIODS_IN_YEAR'
AND A.attribute_id = T.attribute_id
AND G.time_group_type_code = T.time_group_type_code
AND V.attribute_id = T.attribute_id
AND V.version_id = T.version_id
AND V.default_version_flag = 'Y';
SELECT LPAD(TO_CHAR(p_period_num),15,'0')
INTO v_period_chr
FROM dual;
SELECT LPAD(TO_CHAR(calendar_id),5,'0')
INTO v_calendar_chr
FROM fem_calendars_b
WHERE calendar_id = p_calendar_id;
SELECT LPAD(TO_CHAR(time_dimension_group_key),5,'0')
INTO v_dim_grp_key
FROM fem_dimension_grps_b
WHERE dimension_group_id = p_dim_grp_id;
SELECT TO_NUMBER(v_julian_date||v_period_chr||v_calendar_chr||v_dim_grp_key)
INTO v_cal_per_id
FROM dual;
SELECT TO_CHAR(p_end_date,'J')
INTO v_julian_date
FROM dual;
SELECT T.number_assign_value
INTO v_num_of_periods
FROM fem_time_grp_types_attr T,
fem_dimension_grps_b G,
fem_dim_attributes_b A,
fem_dim_attr_versions_b V
WHERE G.dimension_group_id = p_dim_grp_id
AND A.attribute_varchar_label = 'PERIODS_IN_YEAR'
AND A.attribute_id = T.attribute_id
AND G.time_group_type_code = T.time_group_type_code
AND V.attribute_id = T.attribute_id
AND V.version_id = T.version_id
AND V.default_version_flag = 'Y';
SELECT LPAD(TO_CHAR(p_period_num),15,'0')
INTO v_period_chr
FROM dual;
SELECT LPAD(TO_CHAR(calendar_id),5,'0')
INTO v_calendar_chr
FROM fem_calendars_b
WHERE calendar_id = p_calendar_id;
SELECT LPAD(TO_CHAR(time_dimension_group_key),5,'0')
INTO v_dim_grp_key
FROM fem_dimension_grps_b
WHERE dimension_group_id = p_dim_grp_id;
SELECT TO_NUMBER(v_julian_date||v_period_chr||v_calendar_chr||v_dim_grp_key)
INTO v_cal_per_id
FROM dual;
SELECT A.attribute_id,
A.attribute_varchar_label,
A.attribute_value_column_name,
A.attribute_data_type_code,
A.default_assignment,
A.default_assignment_vs_id,
V.version_id
FROM fem_dim_attributes_b A, fem_dim_attr_versions_b V
WHERE A.attribute_required_flag = 'Y'
AND A.dimension_id = p_dimension_id
AND V.version_id =
(SELECT min(version_id)
FROM fem_dim_attr_versions_b V2
WHERE V2.attribute_id = A.attribute_id
AND V2.default_version_flag = 'Y');
SELECT d.dimension_id,
x.member_b_table_name,
x.member_col,
x.member_display_code_col,
x.member_name_col,
x.member_description_col,
x.member_data_type_code,
x.group_use_code,
x.attribute_table_name,
x.value_set_required_flag,
x.member_id_method_code,
x.default_member_display_code,
REPLACE(x.member_b_table_name||'XYZ','_BXYZ','_PKG')
INTO v_dim_id,
v_member_b_tab,
v_member_col,
v_member_dc_col,
v_member_name_col,
v_member_desc_col,
v_member_data_type_code,
v_group_use_code,
v_attr_tab,
v_vsr_flag,
v_member_id_method_code,
v_default_member_dc,
v_member_pkg
FROM fem_xdim_dimensions x, fem_dimensions_b d
WHERE d.dimension_varchar_label = p_dim_label
AND x.read_only_flag = 'N'
AND x.composite_dimension_flag = 'N'
AND x.hier_editor_managed_flag = 'Y'
AND x.dimension_id = d.dimension_id;
SELECT default_load_member_id
INTO v_default_load_member_id
FROM fem_value_sets_b
WHERE value_set_id = p_vs_id
AND dimension_id = v_dim_id;
v_sql := 'SELECT '||v_member_col||', '||v_member_dc_col
||' FROM '||v_member_b_tab;
v_sql := 'SELECT MAX('||v_member_col||') KEEP (DENSE_RANK FIRST ORDER BY creation_date),'
||' MAX('||v_member_dc_col||') KEEP (DENSE_RANK FIRST ORDER BY creation_date)'
||' FROM '||v_member_b_tab;
v_member_pkg||'.INSERT_ROW('||
'x_rowid => v_rowid,';
'x_last_update_date => sysdate,'||
'x_last_updated_by => '||c_user_id||','||
'x_last_update_login => null);'||
v_sql := 'INSERT INTO '||v_attr_tab||
' (ATTRIBUTE_ID'||
',VERSION_ID'||
','||v_member_col;
',LAST_UPDATED_BY'||
',LAST_UPDATE_DATE'||
',LAST_UPDATE_LOGIN'||
',OBJECT_VERSION_NUMBER'||
',AW_SNAPSHOT_FLAG)'||
' select '||attr.attribute_id||
','||attr.version_id||
','||v_member_code;
UPDATE fem_value_sets_b
SET default_load_member_id = to_number(v_member_code),
last_update_date = sysdate,
last_updated_by = c_user_id
WHERE value_set_id = p_vs_id;
p_msg_text => 'Updated FEM_VALUE_SETS_B.default_load_member_id = '
||v_member_code);
UPDATE fem_xdim_dimensions
SET default_member_display_code = (v_member_dc),
last_update_date = sysdate,
last_updated_by = c_user_id
WHERE dimension_id = v_dim_id;
p_msg_text => 'Updated FEM_XDIM_DIMENSIONS.default_member_display_code = '
||v_member_dc);
SELECT D.dimension_varchar_label,
V.default_load_member_id
INTO v_dim_label,
v_def_load_id
FROM fem_value_sets_b V, fem_dimensions_b D
WHERE V.value_set_id = p_vs_id
AND V.dimension_id = D.dimension_id;
SELECT d.dimension_id,
d.dimension_varchar_label,
x.value_set_required_flag
FROM fem_xdim_dimensions x, fem_dimensions_b d
WHERE x.dimension_id = d.dimension_id
AND x.read_only_flag = 'N'
AND x.composite_dimension_flag = 'N'
AND x.hier_editor_managed_flag = 'Y'
AND (x.default_member_display_code IS NULL OR
x.value_set_required_flag = 'Y');
SELECT value_set_id
FROM fem_value_sets_b
WHERE dimension_id = p_dim_id
AND default_load_member_id IS NULL;
SELECT attribute_id,
attribute_varchar_label,
attribute_dimension_id,
attribute_value_column_name,
attribute_required_flag
FROM fem_dim_attributes_b
WHERE dimension_id =
(SELECT dimension_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = c_dim_label);
SELECT dimension_id
INTO v_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = c_dim_label;
FEM_DATASETS_PKG.INSERT_ROW(
x_rowid => v_row_id,
x_dataset_code => v_ds_cd,
x_enabled_flag => c_enbld_flg,
x_dataset_display_code => p_display_code,
x_read_only_flag => c_ro_flg,
x_personal_flag => c_pers_flg,
x_object_version_number => c_obj_ver_no,
x_dataset_name => p_dataset_name,
x_description => p_dataset_desc,
x_creation_date => sysdate,
x_created_by => c_user_id,
x_last_update_date => sysdate,
x_last_updated_by => c_user_id,
x_last_update_login => null);
SELECT MIN(version_id)
INTO v_ver_id
FROM fem_dim_attr_versions_b
WHERE attribute_id = v_attr_id
AND default_version_flag = 'Y';
SELECT fem_dim_attr_versions_b_s.NEXTVAL
INTO v_ver_id FROM dual;
FEM_DIM_ATTR_VERSIONS_PKG.INSERT_ROW(
x_rowid => v_row_id,
x_version_id => v_ver_id,
x_aw_snapshot_flag => c_aw_flg,
x_version_display_code => p_ver_disp_cd,
x_object_version_number => c_obj_ver_no,
x_default_version_flag => 'Y',
x_personal_flag => c_pers_flg,
x_attribute_id => v_attr_id,
x_version_name => p_ver_name,
x_description => null,
x_creation_date => sysdate,
x_created_by => c_user_id,
x_last_update_date => sysdate,
x_last_updated_by => c_user_id,
x_last_update_login => null);
SELECT member_b_table_name,
member_col
INTO v_xdim_tab,
v_xdim_col
FROM fem_xdim_dimensions
WHERE dimension_id = v_xdim_id;
'SELECT '||v_xdim_col||
' FROM '||v_xdim_tab||
' WHERE '||v_xdim_col||' = :b_attr_value';
INSERT INTO fem_datasets_attr(
attribute_id,
version_id,
dataset_code,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
aw_snapshot_flag,
object_version_number)
VALUES(
v_attr_id,
v_ver_id,
v_ds_cd,
v_attr_num,
v_attr_vch,
null,
null,
null,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_aw_flg,
c_obj_ver_no);
INSERT INTO fem_datasets_attr(
attribute_id,
version_id,
dataset_code,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
aw_snapshot_flag,
object_version_number)
VALUES(
v_attr_id,
v_ver_id,
v_ds_cd,
null,
null,
v_attr_value,
null,
null,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_aw_flg,
c_obj_ver_no);
INSERT INTO fem_datasets_attr(
attribute_id,
version_id,
dataset_code,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
aw_snapshot_flag,
object_version_number)
VALUES(
v_attr_id,
v_ver_id,
v_ds_cd,
null,
null,
null,
v_attr_value,
null,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_aw_flg,
c_obj_ver_no);
INSERT INTO fem_datasets_attr(
attribute_id,
version_id,
dataset_code,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
aw_snapshot_flag,
object_version_number)
VALUES(
v_attr_id,
v_ver_id,
v_ds_cd,
null,
null,
null,
null,
v_attr_date,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_aw_flg,
c_obj_ver_no);
SELECT attribute_id,
attribute_varchar_label,
attribute_dimension_id,
attribute_value_column_name,
attribute_required_flag
FROM fem_dim_attributes_b
WHERE dimension_id =
(SELECT dimension_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = c_dim_label);
SELECT dimension_id
INTO v_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = c_dim_label;
FEM_DATASETS_PKG.INSERT_ROW(
x_rowid => v_row_id,
x_dataset_code => v_ds_cd,
x_enabled_flag => c_enbld_flg,
x_dataset_display_code => p_display_code,
x_read_only_flag => c_ro_flg,
x_personal_flag => c_pers_flg,
x_object_version_number => c_obj_ver_no,
x_dataset_name => p_dataset_name,
x_description => p_dataset_desc,
x_creation_date => sysdate,
x_created_by => c_user_id,
x_last_update_date => sysdate,
x_last_updated_by => c_user_id,
x_last_update_login => null);
SELECT MIN(version_id)
INTO v_ver_id
FROM fem_dim_attr_versions_b
WHERE attribute_id = v_attr_id
AND default_version_flag = 'Y';
SELECT fem_dim_attr_versions_b_s.NEXTVAL
INTO v_ver_id FROM dual;
FEM_DIM_ATTR_VERSIONS_PKG.INSERT_ROW(
x_rowid => v_row_id,
x_version_id => v_ver_id,
x_aw_snapshot_flag => c_aw_flg,
x_version_display_code => p_ver_disp_cd,
x_object_version_number => c_obj_ver_no,
x_default_version_flag => 'Y',
x_personal_flag => c_pers_flg,
x_attribute_id => v_attr_id,
x_version_name => p_ver_name,
x_description => null,
x_creation_date => sysdate,
x_created_by => c_user_id,
x_last_update_date => sysdate,
x_last_updated_by => c_user_id,
x_last_update_login => null);
SELECT member_b_table_name,
member_col
INTO v_xdim_tab,
v_xdim_col
FROM fem_xdim_dimensions
WHERE dimension_id = v_xdim_id;
'SELECT '||v_xdim_col||
' FROM '||v_xdim_tab||
' WHERE '||v_xdim_col||' = :b_attr_value';
INSERT INTO fem_datasets_attr(
attribute_id,
version_id,
dataset_code,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
aw_snapshot_flag,
object_version_number)
VALUES(
v_attr_id,
v_ver_id,
v_ds_cd,
v_attr_num,
v_attr_vch,
null,
null,
null,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_aw_flg,
c_obj_ver_no);
INSERT INTO fem_datasets_attr(
attribute_id,
version_id,
dataset_code,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
aw_snapshot_flag,
object_version_number)
VALUES(
v_attr_id,
v_ver_id,
v_ds_cd,
null,
null,
v_attr_value,
null,
null,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_aw_flg,
c_obj_ver_no);
INSERT INTO fem_datasets_attr(
attribute_id,
version_id,
dataset_code,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
aw_snapshot_flag,
object_version_number)
VALUES(
v_attr_id,
v_ver_id,
v_ds_cd,
null,
null,
null,
v_attr_value,
null,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_aw_flg,
c_obj_ver_no);
INSERT INTO fem_datasets_attr(
attribute_id,
version_id,
dataset_code,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
aw_snapshot_flag,
object_version_number)
VALUES(
v_attr_id,
v_ver_id,
v_ds_cd,
null,
null,
null,
null,
v_attr_date,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_aw_flg,
c_obj_ver_no);
SELECT attribute_id,
attribute_varchar_label,
attribute_dimension_id,
attribute_value_column_name,
attribute_required_flag
FROM fem_dim_attributes_b
WHERE dimension_id =
(SELECT dimension_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = c_dim_label);
SELECT dimension_id
INTO v_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = c_dim_label;
FEM_LEDGERS_PKG.INSERT_ROW(
x_rowid => v_row_id,
x_ledger_id => v_lg_id,
x_personal_flag => c_pers_flg,
x_read_only_flag => c_ro_flg,
x_object_version_number => c_obj_ver_no,
x_enabled_flag => c_enbld_flg,
x_ledger_display_code => p_display_code,
x_ledger_name => p_ledger_name,
x_description => p_ledger_desc,
x_creation_date => sysdate,
x_created_by => c_user_id,
x_last_update_date => sysdate,
x_last_updated_by => c_user_id,
x_last_update_login => null);
SELECT MIN(version_id)
INTO v_ver_id
FROM fem_dim_attr_versions_b
WHERE attribute_id = v_attr_id
AND default_version_flag = 'Y';
SELECT fem_dim_attr_versions_b_s.NEXTVAL
INTO v_ver_id FROM dual;
FEM_DIM_ATTR_VERSIONS_PKG.INSERT_ROW(
x_rowid => v_row_id,
x_version_id => v_ver_id,
x_aw_snapshot_flag => c_aw_flg,
x_version_display_code => p_ver_disp_cd,
x_object_version_number => c_obj_ver_no,
x_default_version_flag => 'Y',
x_personal_flag => c_pers_flg,
x_attribute_id => v_attr_id,
x_version_name => p_ver_name,
x_description => null,
x_creation_date => sysdate,
x_created_by => c_user_id,
x_last_update_date => sysdate,
x_last_updated_by => c_user_id,
x_last_update_login => null);
SELECT member_b_table_name,
member_col
INTO v_xdim_tab,
v_xdim_col
FROM fem_xdim_dimensions
WHERE dimension_id = v_xdim_id;
'SELECT '||v_xdim_col||
' FROM '||v_xdim_tab||
' WHERE '||v_xdim_col||' = :b_attr_value';
INSERT INTO fem_ledgers_attr(
attribute_id,
version_id,
ledger_id,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
object_version_number,
aw_snapshot_flag)
VALUES(
v_attr_id,
v_ver_id,
v_lg_id,
v_attr_num,
v_attr_vch,
null,
null,
null,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_obj_ver_no,
c_aw_flg);
INSERT INTO fem_ledgers_attr(
attribute_id,
version_id,
ledger_id,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
object_version_number,
aw_snapshot_flag)
VALUES(
v_attr_id,
v_ver_id,
v_lg_id,
null,
null,
v_attr_value,
null,
null,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_obj_ver_no,
c_aw_flg);
INSERT INTO fem_ledgers_attr(
attribute_id,
version_id,
ledger_id,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
object_version_number,
aw_snapshot_flag)
VALUES(
v_attr_id,
v_ver_id,
v_lg_id,
null,
null,
null,
v_attr_value,
null,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_obj_ver_no,
c_aw_flg);
INSERT INTO fem_ledgers_attr(
attribute_id,
version_id,
ledger_id,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
object_version_number,
aw_snapshot_flag)
VALUES(
v_attr_id,
v_ver_id,
v_lg_id,
null,
null,
null,
null,
v_attr_date,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_obj_ver_no,
c_aw_flg);
SELECT attribute_id,
attribute_varchar_label,
attribute_dimension_id,
attribute_value_column_name,
attribute_required_flag
FROM fem_dim_attributes_b
WHERE dimension_id =
(SELECT dimension_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = c_dim_label);
SELECT dimension_id
INTO v_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = c_dim_label;
FEM_LEDGERS_PKG.INSERT_ROW(
x_rowid => v_row_id,
x_ledger_id => v_lg_id,
x_personal_flag => c_pers_flg,
x_read_only_flag => c_ro_flg,
x_object_version_number => c_obj_ver_no,
x_enabled_flag => c_enbld_flg,
x_ledger_display_code => p_display_code,
x_ledger_name => p_ledger_name,
x_description => p_ledger_desc,
x_creation_date => sysdate,
x_created_by => c_user_id,
x_last_update_date => sysdate,
x_last_updated_by => c_user_id,
x_last_update_login => null);
SELECT MIN(version_id)
INTO v_ver_id
FROM fem_dim_attr_versions_b
WHERE attribute_id = v_attr_id
AND default_version_flag = 'Y';
SELECT fem_dim_attr_versions_b_s.NEXTVAL
INTO v_ver_id FROM dual;
FEM_DIM_ATTR_VERSIONS_PKG.INSERT_ROW(
x_rowid => v_row_id,
x_version_id => v_ver_id,
x_aw_snapshot_flag => c_aw_flg,
x_version_display_code => p_ver_disp_cd,
x_object_version_number => c_obj_ver_no,
x_default_version_flag => 'Y',
x_personal_flag => c_pers_flg,
x_attribute_id => v_attr_id,
x_version_name => p_ver_name,
x_description => null,
x_creation_date => sysdate,
x_created_by => c_user_id,
x_last_update_date => sysdate,
x_last_updated_by => c_user_id,
x_last_update_login => null);
SELECT member_b_table_name,
member_col
INTO v_xdim_tab,
v_xdim_col
FROM fem_xdim_dimensions
WHERE dimension_id = v_xdim_id;
'SELECT '||v_xdim_col||
' FROM '||v_xdim_tab||
' WHERE '||v_xdim_col||' = :b_attr_value';
INSERT INTO fem_ledgers_attr(
attribute_id,
version_id,
ledger_id,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
object_version_number,
aw_snapshot_flag)
VALUES(
v_attr_id,
v_ver_id,
v_lg_id,
v_attr_num,
v_attr_vch,
null,
null,
null,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_obj_ver_no,
c_aw_flg);
INSERT INTO fem_ledgers_attr(
attribute_id,
version_id,
ledger_id,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
object_version_number,
aw_snapshot_flag)
VALUES(
v_attr_id,
v_ver_id,
v_lg_id,
null,
null,
v_attr_value,
null,
null,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_obj_ver_no,
c_aw_flg);
INSERT INTO fem_ledgers_attr(
attribute_id,
version_id,
ledger_id,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
object_version_number,
aw_snapshot_flag)
VALUES(
v_attr_id,
v_ver_id,
v_lg_id,
null,
null,
null,
v_attr_value,
null,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_obj_ver_no,
c_aw_flg);
INSERT INTO fem_ledgers_attr(
attribute_id,
version_id,
ledger_id,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
object_version_number,
aw_snapshot_flag)
VALUES(
v_attr_id,
v_ver_id,
v_lg_id,
null,
null,
null,
null,
v_attr_date,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_obj_ver_no,
c_aw_flg);
SELECT attribute_id,
attribute_varchar_label,
attribute_dimension_id,
attribute_value_column_name
FROM fem_dim_attributes_b
WHERE dimension_id =
(SELECT dimension_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'BUDGET')
AND attribute_required_flag = 'Y';
SELECT dimension_id
INTO v_budget_dimension_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = c_budget_label;
FEM_BUDGETS_PKG.INSERT_ROW(
x_rowid => v_row_id,
x_budget_id => v_budget_id,
x_enabled_flag => c_enabled_flag,
x_budget_display_code => p_budget_display_code,
x_read_only_flag => c_read_only_flag,
x_personal_flag => c_personal_flag,
x_object_version_number => c_object_version_number,
x_budget_name => p_budget_name,
x_description => p_budget_desc,
x_creation_date => sysdate,
x_created_by => c_user_id,
x_last_update_date => sysdate,
x_last_updated_by => c_user_id,
x_last_update_login => null);
SELECT MIN(version_id)
INTO v_version_id
FROM fem_dim_attr_versions_b
WHERE attribute_id = v_attribute_id
AND default_version_flag = 'Y';
SELECT fem_dim_attr_versions_b_s.NEXTVAL
INTO v_version_id FROM dual;
FEM_DIM_ATTR_VERSIONS_PKG.INSERT_ROW(
x_rowid => v_row_id,
x_version_id => v_version_id,
x_aw_snapshot_flag => c_aw_snapshot_flag,
x_version_display_code => p_ver_disp_cd,
x_object_version_number => c_object_version_number,
x_default_version_flag => 'Y',
x_personal_flag => c_personal_flag,
x_attribute_id => v_attribute_id,
x_version_name => p_ver_name,
x_description => null,
x_creation_date => sysdate,
x_created_by => c_user_id,
x_last_update_date => sysdate,
x_last_updated_by => c_user_id,
x_last_update_login => null);
select calendar_id
into v_first_period_calendar_id
from fem_calendars_b
where calendar_display_code = p_first_period_calendar;
select time_dimension_group_key
into v_first_period_time_dimgrp_key
from fem_dimension_grps_b D, fem_dimensions_b B
where D.dimension_group_display_code = p_first_period_dimgrp
and D.dimension_id = B.dimension_id
and B.dimension_varchar_label = 'CAL_PERIOD';
select LPAD(to_char(to_number(to_char(p_first_period_end_date,'j'))),7,'0')||
LPAD(TO_CHAR(p_first_period_number),15,'0')||
LPAD(to_char(v_first_period_calendar_id),5,'0')||
LPAD(to_char(v_first_period_time_dimgrp_key),5,'0')
into v_attr_assign_value
from dual;
select calendar_id
into v_last_period_calendar_id
from fem_calendars_b
where calendar_display_code = p_last_period_calendar;
select time_dimension_group_key
into v_last_period_time_dimgrp_key
from fem_dimension_grps_b D, fem_dimensions_b B
where D.dimension_group_display_code = p_last_period_dimgrp
and D.dimension_id = B.dimension_id
and B.dimension_varchar_label = 'CAL_PERIOD';
select LPAD(to_char(to_number(to_char(p_last_period_end_date,'j'))),7,'0')||
LPAD(TO_CHAR(p_last_period_number),15,'0')||
LPAD(to_char(v_last_period_calendar_id),5,'0')||
LPAD(to_char(v_last_period_time_dimgrp_key),5,'0')
into v_attr_assign_value
from dual;
SELECT member_b_table_name,
member_col,
member_display_code_col
INTO v_attr_member_tab,
v_attr_member_col,
v_attr_member_dc_col
FROM fem_xdim_dimensions
WHERE dimension_id = attr.attribute_dimension_id;
'SELECT '||v_attr_member_col||
' FROM '||v_attr_member_tab||
' WHERE to_char('||v_attr_member_dc_col||') = '''||v_attr_assign_value||'''';
INSERT INTO fem_budgets_attr(
attribute_id,
version_id,
budget_id,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
aw_snapshot_flag,
object_version_number)
VALUES(
v_attribute_id,
v_version_id,
v_budget_id,
v_attr_numeric_member,
v_attr_varchar_member,
v_attr_number_assign,
null,
null,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_aw_snapshot_flag,
c_object_version_number);
SELECT attribute_id,
attribute_varchar_label,
attribute_dimension_id,
attribute_value_column_name
FROM fem_dim_attributes_b
WHERE dimension_id =
(SELECT dimension_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = 'BUDGET')
AND attribute_required_flag = 'Y';
SELECT dimension_id
INTO v_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = c_budget_label;
SELECT budget_id
INTO v_budget_id
FROM fem_budgets_b
WHERE budget_id = p_budget_id;
FEM_BUDGETS_PKG.INSERT_ROW(
x_rowid => v_row_id,
x_budget_id => v_budget_id,
x_enabled_flag => c_enabled_flag,
x_budget_display_code => p_budget_display_code,
x_read_only_flag => c_read_only_flag,
x_personal_flag => c_personal_flag,
x_object_version_number => c_object_version_number,
x_budget_name => p_budget_name,
x_description => p_budget_desc,
x_creation_date => sysdate,
x_created_by => c_user_id,
x_last_update_date => sysdate,
x_last_updated_by => c_user_id,
x_last_update_login => null);
SELECT MIN(version_id)
INTO v_version_id
FROM fem_dim_attr_versions_b
WHERE attribute_id = v_attribute_id
AND default_version_flag = 'Y';
SELECT fem_dim_attr_versions_b_s.NEXTVAL
INTO v_version_id FROM dual;
FEM_DIM_ATTR_VERSIONS_PKG.INSERT_ROW(
x_rowid => v_row_id,
x_version_id => v_version_id,
x_aw_snapshot_flag => c_aw_snapshot_flag,
x_version_display_code => p_ver_disp_cd,
x_object_version_number => c_object_version_number,
x_default_version_flag => 'Y',
x_personal_flag => c_personal_flag,
x_attribute_id => v_attribute_id,
x_version_name => p_ver_name,
x_description => null,
x_creation_date => sysdate,
x_created_by => c_user_id,
x_last_update_date => sysdate,
x_last_updated_by => c_user_id,
x_last_update_login => null);
select calendar_id
into v_first_period_calendar_id
from fem_calendars_b
where calendar_display_code = p_first_period_calendar;
select time_dimension_group_key
into v_first_period_time_dimgrp_key
from fem_dimension_grps_b D, fem_dimensions_b B
where D.dimension_group_display_code = p_first_period_dimgrp
and D.dimension_id = B.dimension_id
and B.dimension_varchar_label = 'CAL_PERIOD';
select LPAD(to_char(to_number(to_char(p_first_period_end_date,'j'))),7,'0')||
LPAD(TO_CHAR(p_first_period_number),15,'0')||
LPAD(to_char(v_first_period_calendar_id),5,'0')||
LPAD(to_char(v_first_period_time_dimgrp_key),5,'0')
into v_attr_assign_value
from dual;
select calendar_id
into v_last_period_calendar_id
from fem_calendars_b
where calendar_display_code = p_last_period_calendar;
select time_dimension_group_key
into v_last_period_time_dimgrp_key
from fem_dimension_grps_b D, fem_dimensions_b B
where D.dimension_group_display_code = p_last_period_dimgrp
and D.dimension_id = B.dimension_id
and B.dimension_varchar_label = 'CAL_PERIOD';
select LPAD(to_char(to_number(to_char(p_last_period_end_date,'j'))),7,'0')||
LPAD(TO_CHAR(p_last_period_number),15,'0')||
LPAD(to_char(v_last_period_calendar_id),5,'0')||
LPAD(to_char(v_last_period_time_dimgrp_key),5,'0')
into v_attr_assign_value
from dual;
SELECT member_b_table_name,
member_col,
member_display_code_col
INTO v_attr_member_tab,
v_attr_member_col,
v_attr_member_dc_col
FROM fem_xdim_dimensions
WHERE dimension_id = attr.attribute_dimension_id;
'SELECT '||v_attr_member_col||
' FROM '||v_attr_member_tab||
' WHERE to_char('||v_attr_member_dc_col||') = '''||v_attr_assign_value||'''';
INSERT INTO fem_budgets_attr(
attribute_id,
version_id,
budget_id,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
aw_snapshot_flag,
object_version_number)
VALUES(
v_attribute_id,
v_version_id,
v_budget_id,
v_attr_numeric_member,
v_attr_varchar_member,
v_attr_number_assign,
null,
null,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_aw_snapshot_flag,
c_object_version_number);
SELECT attribute_id,
attribute_varchar_label,
attribute_dimension_id,
attribute_value_column_name,
attribute_required_flag
FROM fem_dim_attributes_b
WHERE dimension_id =
(SELECT dimension_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = c_dim_label);
SELECT dimension_id
INTO v_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = c_dim_label;
SELECT ledger_id
INTO v_lg_id
FROM fem_ledgers_b
WHERE ledger_id = p_ledger_id;
FEM_LEDGERS_PKG.INSERT_ROW(
x_rowid => v_row_id,
x_ledger_id => v_lg_id,
x_personal_flag => c_pers_flg,
x_read_only_flag => c_ro_flg,
x_object_version_number => c_obj_ver_no,
x_enabled_flag => c_enbld_flg,
x_ledger_display_code => p_display_code,
x_ledger_name => p_ledger_name,
x_description => p_ledger_desc,
x_creation_date => sysdate,
x_created_by => c_user_id,
x_last_update_date => sysdate,
x_last_updated_by => c_user_id,
x_last_update_login => null);
SELECT MIN(version_id)
INTO v_ver_id
FROM fem_dim_attr_versions_b
WHERE attribute_id = v_attr_id
AND default_version_flag = 'Y';
SELECT fem_dim_attr_versions_b_s.NEXTVAL
INTO v_ver_id FROM dual;
FEM_DIM_ATTR_VERSIONS_PKG.INSERT_ROW(
x_rowid => v_row_id,
x_version_id => v_ver_id,
x_aw_snapshot_flag => c_aw_flg,
x_version_display_code => p_ver_disp_cd,
x_object_version_number => c_obj_ver_no,
x_default_version_flag => 'Y',
x_personal_flag => c_pers_flg,
x_attribute_id => v_attr_id,
x_version_name => p_ver_name,
x_description => null,
x_creation_date => sysdate,
x_created_by => c_user_id,
x_last_update_date => sysdate,
x_last_updated_by => c_user_id,
x_last_update_login => null);
SELECT member_b_table_name,
member_col
INTO v_xdim_tab,
v_xdim_col
FROM fem_xdim_dimensions
WHERE dimension_id = v_xdim_id;
'SELECT '||v_xdim_col||
' FROM '||v_xdim_tab||
' WHERE '||v_xdim_col||' = :b_attr_value';
INSERT INTO fem_ledgers_attr(
attribute_id,
version_id,
ledger_id,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
object_version_number,
aw_snapshot_flag)
VALUES(
v_attr_id,
v_ver_id,
v_lg_id,
v_attr_num,
v_attr_vch,
null,
null,
null,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_obj_ver_no,
c_aw_flg);
INSERT INTO fem_ledgers_attr(
attribute_id,
version_id,
ledger_id,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
object_version_number,
aw_snapshot_flag)
VALUES(
v_attr_id,
v_ver_id,
v_lg_id,
null,
null,
v_attr_value,
null,
null,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_obj_ver_no,
c_aw_flg);
INSERT INTO fem_ledgers_attr(
attribute_id,
version_id,
ledger_id,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
object_version_number,
aw_snapshot_flag)
VALUES(
v_attr_id,
v_ver_id,
v_lg_id,
null,
null,
null,
v_attr_value,
null,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_obj_ver_no,
c_aw_flg);
INSERT INTO fem_ledgers_attr(
attribute_id,
version_id,
ledger_id,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
object_version_number,
aw_snapshot_flag)
VALUES(
v_attr_id,
v_ver_id,
v_lg_id,
null,
null,
null,
null,
v_attr_date,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_obj_ver_no,
c_aw_flg);
SELECT attribute_id,
attribute_varchar_label,
attribute_dimension_id,
attribute_value_column_name,
attribute_required_flag
FROM fem_dim_attributes_b
WHERE dimension_id =
(SELECT dimension_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = c_dim_label);
SELECT dimension_id
INTO v_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = c_dim_label;
FEM_ENCUMBRANCE_TYPES_PKG.INSERT_ROW(
x_rowid => v_row_id,
x_encumbrance_type_id => v_enc_type_id,
x_personal_flag => c_pers_flg,
x_encumbrance_type_code => p_enc_type_code,
x_enabled_flag => c_enbld_flg,
x_object_version_number => c_obj_ver_no,
x_read_only_flag => c_ro_flg,
x_encumbrance_type_name => p_enc_type_name,
x_description => p_enc_type_desc,
x_creation_date => sysdate,
x_created_by => c_user_id,
x_last_update_date => sysdate,
x_last_updated_by => c_user_id,
x_last_update_login => null);
SELECT MIN(version_id)
INTO v_ver_id
FROM fem_dim_attr_versions_b
WHERE attribute_id = v_attr_id
AND default_version_flag = 'Y';
SELECT fem_dim_attr_versions_b_s.NEXTVAL
INTO v_ver_id FROM dual;
FEM_DIM_ATTR_VERSIONS_PKG.INSERT_ROW(
x_rowid => v_row_id,
x_version_id => v_ver_id,
x_aw_snapshot_flag => c_aw_flg,
x_version_display_code => p_ver_disp_cd,
x_object_version_number => c_obj_ver_no,
x_default_version_flag => 'Y',
x_personal_flag => c_pers_flg,
x_attribute_id => v_attr_id,
x_version_name => p_ver_name,
x_description => null,
x_creation_date => sysdate,
x_created_by => c_user_id,
x_last_update_date => sysdate,
x_last_updated_by => c_user_id,
x_last_update_login => null);
SELECT member_b_table_name,
member_col
INTO v_xdim_tab,
v_xdim_col
FROM fem_xdim_dimensions
WHERE dimension_id = v_xdim_id;
'SELECT '||v_xdim_col||
' FROM '||v_xdim_tab||
' WHERE '||v_xdim_col||' = :b_attr_value';
INSERT INTO fem_enc_types_attr(
attribute_id,
version_id,
encumbrance_type_id,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
aw_snapshot_flag,
object_version_number)
VALUES(
v_attr_id,
v_ver_id,
v_enc_type_id,
v_attr_num,
v_attr_vch,
null,
null,
null,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_aw_flg,
c_obj_ver_no);
INSERT INTO fem_enc_types_attr(
attribute_id,
version_id,
encumbrance_type_id,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
aw_snapshot_flag,
object_version_number)
VALUES(
v_attr_id,
v_ver_id,
v_enc_type_id,
null,
null,
v_attr_value,
null,
null,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_aw_flg,
c_obj_ver_no);
INSERT INTO fem_enc_types_attr(
attribute_id,
version_id,
encumbrance_type_id,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
aw_snapshot_flag,
object_version_number)
VALUES(
v_attr_id,
v_ver_id,
v_enc_type_id,
null,
null,
null,
v_attr_value,
null,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_aw_flg,
c_obj_ver_no);
INSERT INTO fem_enc_types_attr(
attribute_id,
version_id,
encumbrance_type_id,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
aw_snapshot_flag,
object_version_number)
VALUES(
v_attr_id,
v_ver_id,
v_enc_type_id,
null,
null,
null,
null,
v_attr_date,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_aw_flg,
c_obj_ver_no);
SELECT attribute_id,
attribute_varchar_label,
attribute_dimension_id,
attribute_value_column_name,
attribute_required_flag
FROM fem_dim_attributes_b
WHERE dimension_id =
(SELECT dimension_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = c_dim_label);
SELECT dimension_id
INTO v_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = c_dim_label;
SELECT encumbrance_type_id
INTO v_enc_type_id
FROM fem_encumbrance_types_b
WHERE encumbrance_type_id = p_enc_type_id;
FEM_ENCUMBRANCE_TYPES_PKG.INSERT_ROW(
x_rowid => v_row_id,
x_encumbrance_type_id => v_enc_type_id,
x_personal_flag => c_pers_flg,
x_encumbrance_type_code => p_enc_type_code,
x_enabled_flag => c_enbld_flg,
x_object_version_number => c_obj_ver_no,
x_read_only_flag => c_ro_flg,
x_encumbrance_type_name => p_enc_type_name,
x_description => p_enc_type_desc,
x_creation_date => sysdate,
x_created_by => c_user_id,
x_last_update_date => sysdate,
x_last_updated_by => c_user_id,
x_last_update_login => null);
SELECT MIN(version_id)
INTO v_ver_id
FROM fem_dim_attr_versions_b
WHERE attribute_id = v_attr_id
AND default_version_flag = 'Y';
SELECT fem_dim_attr_versions_b_s.NEXTVAL
INTO v_ver_id FROM dual;
FEM_DIM_ATTR_VERSIONS_PKG.INSERT_ROW(
x_rowid => v_row_id,
x_version_id => v_ver_id,
x_aw_snapshot_flag => c_aw_flg,
x_version_display_code => p_ver_disp_cd,
x_object_version_number => c_obj_ver_no,
x_default_version_flag => 'Y',
x_personal_flag => c_pers_flg,
x_attribute_id => v_attr_id,
x_version_name => p_ver_name,
x_description => null,
x_creation_date => sysdate,
x_created_by => c_user_id,
x_last_update_date => sysdate,
x_last_updated_by => c_user_id,
x_last_update_login => null);
SELECT member_b_table_name,
member_col
INTO v_xdim_tab,
v_xdim_col
FROM fem_xdim_dimensions
WHERE dimension_id = v_xdim_id;
'SELECT '||v_xdim_col||
' FROM '||v_xdim_tab||
' WHERE '||v_xdim_col||' = :b_attr_value';
INSERT INTO fem_enc_types_attr(
attribute_id,
version_id,
encumbrance_type_id,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
aw_snapshot_flag,
object_version_number)
VALUES(
v_attr_id,
v_ver_id,
v_enc_type_id,
v_attr_num,
v_attr_vch,
null,
null,
null,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_aw_flg,
c_obj_ver_no);
INSERT INTO fem_enc_types_attr(
attribute_id,
version_id,
encumbrance_type_id,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
aw_snapshot_flag,
object_version_number)
VALUES(
v_attr_id,
v_ver_id,
v_enc_type_id,
null,
null,
v_attr_value,
null,
null,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_aw_flg,
c_obj_ver_no);
INSERT INTO fem_enc_types_attr(
attribute_id,
version_id,
encumbrance_type_id,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
aw_snapshot_flag,
object_version_number)
VALUES(
v_attr_id,
v_ver_id,
v_enc_type_id,
null,
null,
null,
v_attr_value,
null,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_aw_flg,
c_obj_ver_no);
INSERT INTO fem_enc_types_attr(
attribute_id,
version_id,
encumbrance_type_id,
dim_attribute_numeric_member,
dim_attribute_varchar_member,
number_assign_value,
varchar_assign_value,
date_assign_value,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
aw_snapshot_flag,
object_version_number)
VALUES(
v_attr_id,
v_ver_id,
v_enc_type_id,
null,
null,
null,
null,
v_attr_date,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_aw_flg,
c_obj_ver_no);
Insert statement for FEM_GLOBAL_VS_COMBOS_PKG
-- this fix will preserve the original signature
for this API so that it is backward compatible
with OGL by employing a default for the
global combo display_code. If the user passes
null for the display code, it set it = global combo name.
*************************************************************************/
PROCEDURE New_Global_VS_Combo (
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,
x_global_vs_combo_id OUT NOCOPY NUMBER,
p_global_vs_combo_name IN VARCHAR2,
p_global_vs_combo_desc IN VARCHAR2 DEFAULT NULL,
p_read_only_flag IN VARCHAR2 DEFAULT 'N',
p_enabled_flag IN VARCHAR2 DEFAULT 'Y',
p_global_vs_combo_dc IN VARCHAR2 DEFAULT NULL
)
IS
c_obj_ver_no CONSTANT NUMBER := 1;
SELECT dimension_id, dimension_varchar_label
FROM fem_xdim_dimensions_vl
WHERE value_set_required_flag = 'Y'
ORDER BY dimension_id;
SELECT fem_global_vs_combos_b_s.NEXTVAL
INTO v_gvsc_id
FROM dual;
FEM_GLOBAL_VS_COMBOS_PKG.INSERT_ROW(
x_rowid => v_row_id,
x_global_vs_combo_id => v_gvsc_id,
x_global_vs_combo_display_code => v_global_vs_combo_dc,
x_enabled_flag => p_enabled_flag,
x_read_only_flag => p_read_only_flag,
x_personal_flag => c_pers_flg,
x_object_version_number => c_obj_ver_no,
x_global_vs_combo_name => p_global_vs_combo_name,
x_description => p_global_vs_combo_desc,
x_creation_date => sysdate,
x_created_by => c_user_id,
x_last_update_date => sysdate,
x_last_updated_by => c_user_id,
x_last_update_login => null);
SELECT value_set_id
INTO v_vs_id
FROM fem_value_sets_b
WHERE dimension_id = r_vs_dim_id.dimension_id
AND value_set_display_code = r_vs_dim_id.dimension_varchar_label;
INSERT INTO fem_global_vs_combo_defs
(global_vs_combo_id,
dimension_id,
value_set_id,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
object_version_number)
VALUES
(v_gvsc_id,
r_vs_dim_id.dimension_id,
v_vs_id,
sysdate,
c_user_id,
c_user_id,
sysdate,
null,
c_obj_ver_no);
SELECT A.attribute_id,
V.version_id
INTO x_attr_id,
x_ver_id
FROM fem_dim_attributes_b A,
fem_dim_attr_versions_b V
WHERE attribute_varchar_label = p_attr_label
AND dimension_id = p_dim_id
AND A.attribute_id = V.attribute_id
AND V.default_version_flag = 'Y';
SELECT dimension_id
INTO v_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = p_dimension_varchar_label;
SELECT member_b_table_name,
member_col,
member_display_code_col,
value_set_required_flag,
composite_dimension_flag
INTO v_mem_b_tab,
v_mem_col,
v_mem_dc_col,
v_vs_req_flg,
v_comp_dim_flg
FROM fem_xdim_dimensions
WHERE dimension_id = v_dim_id;
SELECT value_set_id
INTO v_vs_id
FROM fem_value_sets_b
WHERE value_set_display_code = p_member_vs_display_code
AND dimension_id = v_dim_id;
'SELECT '||v_mem_col||
' FROM '||v_mem_b_tab||
' WHERE '||v_mem_dc_col||' = :b_member_display_code'||
' AND local_vs_combo_id = :b_gvsc_id';
'SELECT '||v_mem_col||
' FROM '||v_mem_b_tab||
' WHERE '||v_mem_dc_col||' = :b_member_display_code'||
' AND value_set_id = :b_vs_id';
'SELECT '||v_mem_col||
' FROM '||v_mem_b_tab||
' WHERE '||v_mem_dc_col||' = :b_member_display_code';
SELECT attribute_name
INTO v_attr_name
FROM fem_dim_attributes_tl
WHERE attribute_id = p_attr_id
AND language = userenv('LANG');
SELECT T.attribute_name
INTO v_attr_name
FROM fem_dim_attributes_b B,
fem_dim_attributes_tl T
WHERE B.attribute_varchar_label = p_attr_label
AND B.dimension_id = p_dim_id
AND T.attribute_id = B.attribute_id
AND T.language = userenv('LANG');
SELECT AT.attribute_name
INTO v_attr_name
FROM fem_dim_attributes_b AB,
fem_dim_attributes_tl AT,
fem_dimensions_b DB
WHERE AB.attribute_varchar_label = p_attr_label
AND DB.dimension_varchar_label = p_dim_label
AND AB.dimension_id = DB.dimension_id
AND AT.attribute_id = AB.attribute_id
AND AT.language = userenv('LANG');
SELECT dimension_name
INTO v_dim_name
FROM fem_dimensions_tl
WHERE dimension_id = p_dim_id
AND language = userenv('LANG');
SELECT T.dimension_name
INTO v_dim_name
FROM fem_dimensions_b B,
fem_dimensions_tl T
WHERE B.dimension_varchar_label = p_dim_label
AND T.dimension_id = B.dimension_id
AND T.language = userenv('LANG');
SELECT dimension_id
INTO v_dim_id
FROM fem_dimensions_b
WHERE dimension_varchar_label = p_dimension_varchar_label;
SELECT member_b_table_name,
member_col,
member_display_code_col,
member_data_type_code,
value_set_required_flag,
default_member_display_code
INTO v_member_table,
v_member_col,
v_member_dc_col,
x_member_data_type,
v_vsr_flag,
v_default_member_dc
FROM fem_xdim_dimensions
WHERE dimension_id = v_dim_id;
SELECT default_load_member_id
INTO v_default_member
FROM fem_value_sets_b
WHERE value_set_id = v_vs_id;
v_sql := 'SELECT '||v_member_dc_col
||' FROM '||v_member_table
||' WHERE '||v_member_col||' = :v_default_member'
||' AND value_set_id = :v_vs_id';
v_sql := 'SELECT '||v_member_col
||' FROM '||v_member_table
||' WHERE '||v_member_dc_col||' = :v_default_member_dc';
SELECT dimension_id
INTO v_dim_id
FROM fem_tab_columns_b
WHERE table_name = Upper(p_table_name)
AND column_name = Upper(p_column_name);
SELECT dimension_id
INTO v_dim_id
FROM fem_dimensions_b
WHERE dimension_id = p_dimension_id;
SELECT member_b_table_name,
member_col,
member_display_code_col,
value_set_required_flag
INTO v_mem_b_tab,
v_mem_col,
v_mem_dc_col,
v_vs_req_flg
FROM fem_xdim_dimensions
WHERE dimension_id = v_dim_id;
SELECT value_set_id
INTO v_vs_id
FROM fem_value_sets_b
WHERE value_set_id = p_dimension_member_vs_id
AND dimension_id = v_dim_id;
'SELECT '||v_mem_dc_col||
' FROM '||v_mem_b_tab||
' WHERE '||v_mem_col||' = :b_dimension_member_id'||
' AND value_set_id = :b_vs_id';
'SELECT '||v_mem_dc_col||
' FROM '||v_mem_b_tab||
' WHERE '||v_mem_col||' = :b_dimension_member_id';
SELECT dimension_id
INTO v_dim_id
FROM fem_dimensions_b
WHERE dimension_id = p_dimension_id;
SELECT member_vl_object_name,
member_col,
member_name_col,
value_set_required_flag
INTO v_mem_vl_tab,
v_mem_col,
v_mem_name_col,
v_vs_req_flg
FROM fem_xdim_dimensions
WHERE dimension_id = v_dim_id;
SELECT value_set_id
INTO v_vs_id
FROM fem_value_sets_b
WHERE value_set_id = p_dimension_member_vs_id
AND dimension_id = v_dim_id;
'SELECT '||v_mem_name_col||
' FROM '||v_mem_vl_tab||
' WHERE '||v_mem_col||' = :b_dimension_member_id'||
' AND value_set_id = :b_vs_id';
'SELECT '||v_mem_name_col||
' FROM '||v_mem_vl_tab||
' WHERE '||v_mem_col||' = :b_dimension_member_id';