[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ftcb.dimension_id,
fda.attribute_id,
ftcb.column_name,
fda.attribute_varchar_label,
fda.attribute_value_column_name,
-- STK 3/29/04 Added Default Version ID for performance purposes
fdavb.version_id
BULK COLLECT INTO l_index_dimension_attr_info
FROM fem_tab_columns_b ftcb,
fem_dim_attributes_b fda,
fem_tab_column_prop ftcp,
fem_dim_attr_versions_b fdavb
WHERE ftcb.table_name = 'FEM_BALANCES'
AND ftcb.dimension_id = fda.dimension_id
AND ftcp.table_name = 'FEM_BALANCES'
AND ftcp.column_property_code = 'PROCESSING_KEY'
AND fdavb.attribute_id = fda.attribute_id
AND fdavb.default_version_flag = 'Y'
AND ftcp.column_name = ftcb.column_name;
SELECT fdb.dimension_id,
fda.attribute_id,
DECODE(fdb.dimension_varchar_label,
'COST_CENTER', 'COST_CENTER','EXT_ACCOUNT_TYPE_CODE'),
fda.attribute_varchar_label,
fda.attribute_value_column_name,
-- STK 3/29/04 Added Default Version ID for performance purposes
fdavb.version_id
BULK COLLECT INTO l_index_dimension_attr_info
FROM fem_dim_attributes_b fda,
fem_dimensions_b fdb,
fem_dim_attr_versions_b fdavb
WHERE fdb.dimension_id = fda.dimension_id
AND fdavb.attribute_id = fda.attribute_id
AND fdavb.default_version_flag = 'Y'
AND fdb.dimension_varchar_label IN ('EXTENDED_ACCOUNT_TYPE','COST_CENTER');
SELECT gedm.gcs_column,
nvl(gedm.hrate_enabled_flag, 'Y')
BULK COLLECT INTO l_index_hrate_dim_info
FROM gcs_epb_dim_maps gedm;
SELECT ftcb.column_name,
fvsb.dimension_id,
fvsb.value_set_id,
'N' gcs_required,
'Y' fem_required,
fvsb.default_member_id,
-1,
fxd.member_b_table_name,
fxd.member_vl_object_name,
fxd.member_col,
fxd.member_display_code_col
BULK COLLECT INTO l_index_gcs_dimension_info
FROM fem_value_sets_b fvsb,
fem_global_vs_combo_defs fgvcd,
fem_tab_columns_b ftcb,
fem_tab_column_prop ftcp,
fem_xdim_dimensions fxd,
gcs_system_options gso
WHERE fvsb.dimension_id = DECODE(fgvcd.dimension_id, 17, 8, fgvcd.dimension_id)
AND fvsb.value_set_id = fgvcd.value_set_id
AND fgvcd.global_vs_combo_id = gso.fch_global_vs_combo_id
AND ftcb.dimension_id = fgvcd.dimension_id
AND ftcb.table_name = 'FEM_BALANCES'
AND ftcp.table_name = 'FEM_BALANCES'
AND ftcp.column_property_code = 'PROCESSING_KEY'
AND ftcb.dimension_id = fxd.dimension_id
AND ftcp.column_name = ftcb.column_name;
SELECT 'N'
INTO l_required_for_gcs
FROM DUAL
WHERE EXISTS
(SELECT 'X'
FROM fem_app_grp_dim_exclsns fagde
WHERE fagde.application_group_id = l_application_group_id
AND fagde.dimension_id = l_index_gcs_dimension_info(l_counter).dimension_id)
OR EXISTS
(SELECT 'X'
FROM fem_app_grp_col_exclsns fagce
WHERE fagce.application_group_id = l_application_group_id
AND fagce.column_name = l_index_gcs_dimension_info(l_counter).column_name);
SELECT fcpb.calendar_id, fcpb.dimension_group_id,
fcpa_number.number_assign_value, fcpa_year.number_assign_value,
ftgta.number_assign_value
INTO l_calendar_id, l_calendar_group_id,
l_curr_cal_period_number, l_curr_cal_period_year,
l_periods_per_year
FROM fem_cal_periods_b fcpb,
fem_cal_periods_attr fcpa_number,
fem_cal_periods_attr fcpa_year,
fem_dimension_grps_b fdgb,
fem_time_grp_types_attr ftgta,
fem_dim_attributes_b feab,
fem_dim_attr_versions_b fdavb
WHERE fcpb.cal_period_id = p_cal_period_id
AND fcpb.dimension_group_id = fdgb.dimension_group_id
AND fdgb.time_group_type_code = ftgta.time_group_type_code
AND ftgta.attribute_id = feab.attribute_id
AND ftgta.version_id = fdavb.version_id
AND feab.attribute_varchar_label = 'PERIODS_IN_YEAR'
AND feab.attribute_id = fdavb.attribute_id
AND fdavb.default_version_flag = 'Y'
AND fcpa_number.cal_period_id = fcpb.cal_period_id
AND fcpa_number.attribute_id =
g_dimension_attr_info ('CAL_PERIOD_ID-GL_PERIOD_NUM').attribute_id
AND fcpa_number.version_id =
g_dimension_attr_info ('CAL_PERIOD_ID-GL_PERIOD_NUM').version_id
AND fcpa_year.attribute_id =
g_dimension_attr_info ('CAL_PERIOD_ID-ACCOUNTING_YEAR').attribute_id
AND fcpa_year.version_id =
g_dimension_attr_info ('CAL_PERIOD_ID-ACCOUNTING_YEAR').version_id
AND fcpa_year.cal_period_id = fcpb.cal_period_id;
SELECT fcpb.cal_period_id
INTO l_prev_cal_period_id
FROM fem_cal_periods_b fcpb,
fem_cal_periods_attr fcpa_number,
fem_cal_periods_attr fcpa_year
WHERE fcpb.calendar_id = l_calendar_id
AND fcpb.dimension_group_id = l_calendar_group_id
AND fcpb.cal_period_id = fcpa_number.cal_period_id
AND fcpb.cal_period_id = fcpa_year.cal_period_id
AND fcpa_number.attribute_id = g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM').attribute_id
AND fcpa_number.version_id = g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM').version_id
AND fcpa_number.number_assign_value = l_prev_cal_period_number
AND fcpa_year.attribute_id = g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR').attribute_id
AND fcpa_year.version_id = g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR').version_id
AND fcpa_year.number_assign_value = l_prev_cal_period_year;
SELECT fcpb.cal_period_id
INTO l_next_cal_period_id
FROM fem_cal_periods_b fcpb,
fem_cal_periods_attr fcpa_number,
fem_cal_periods_attr fcpa_year
WHERE fcpb.calendar_id = l_calendar_id
AND fcpb.dimension_group_id = l_calendar_group_id
AND fcpb.cal_period_id = fcpa_number.cal_period_id
AND fcpb.cal_period_id = fcpa_year.cal_period_id
AND fcpa_number.attribute_id = g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM').attribute_id
AND fcpa_number.version_id = g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM').version_id
AND fcpa_number.number_assign_value = l_next_cal_period_number
AND fcpa_year.attribute_id = g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR').attribute_id
AND fcpa_year.version_id = g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR').version_id
AND fcpa_year.number_assign_value = l_next_cal_period_year;
SELECT entity_name
INTO l_entity_name
FROM fem_entities_tl
WHERE entity_id = p_entity_id
AND language = USERENV('LANG');
SELECT rule_name
INTO l_rule_name
FROM gcs_elim_rules_tl
WHERE rule_id = p_rule_id
AND language = USERENV('LANG');
SELECT 'X'
INTO l_temp
FROM gcs_entry_headers
WHERE entry_name = l_entry_header;
SELECT DIM_ATTRIBUTE_NUMERIC_MEMBER
FROM FEM_ENTITIES_ATTR
WHERE ENTITY_ID = cEntityId
AND VERSION_ID = g_dimension_attr_info('ENTITY_ID-BASE_ORGANIZATION').version_id
AND ATTRIBUTE_ID = g_dimension_attr_info('ENTITY_ID-BASE_ORGANIZATION').attribute_id;
SELECT DIM_ATTRIBUTE_NUMERIC_MEMBER
FROM FEM_ENTITIES_ATTR
WHERE ENTITY_ID = cEntityId
AND VERSION_ID = g_dimension_attr_info('ENTITY_ID-OPERATING_ENTITY').version_id
AND ATTRIBUTE_ID = g_dimension_attr_info('ENTITY_ID-OPERATING_ENTITY').attribute_id;
SELECT r.child_entity_id
FROM GCS_CONS_RELATIONSHIPS r
START WITH r.parent_entity_id = cEntityId
AND r.hierarchy_id = cHierarchyId
AND ( sysdate BETWEEN r.start_date
AND NVL(r.end_date, sysdate))
AND r.actual_ownership_flag='Y'
CONNECT BY prior r.child_entity_id = r.parent_entity_id
AND r.hierarchy_id = cHierarchyId
AND ( sysdate BETWEEN r.start_date
AND NVL(r.end_date, sysdate))
AND r.actual_ownership_flag='Y';
SELECT dim_attribute_varchar_member
INTO l_entity_type
FROM FEM_ENTITIES_ATTR
WHERE entity_id = l_entity_id
AND attribute_id = g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE').attribute_id
AND version_id = g_dimension_attr_info('ENTITY_ID-ENTITY_TYPE_CODE').version_id
AND value_set_id = g_gcs_dimension_info('ENTITY_ID').associated_value_set_id;
SELECT R.PARENT_ENTITY_ID
INTO c_entity_id
FROM GCS_CONS_RELATIONSHIPS R
WHERE R.CHILD_ENTITY_ID = l_entity_id
AND R.actual_ownership_flag='Y'
AND R.HIERARCHY_ID = l_hierarchy_id;
SELECT fea.entity_id
INTO c_entity_id
FROM fem_entities_attr fea
WHERE fea.attribute_id = g_dimension_attr_info('ENTITY_ID-ELIMINATION_ENTITY').attribute_id
AND fea.version_id = g_dimension_attr_info('ENTITY_ID-ELIMINATION_ENTITY').version_id
AND fea.dim_attribute_numeric_member = l_entity_id;
SELECT DATE_ASSIGN_VALUE
INTO l_period_end_date
FROM fem_cal_periods_attr fcpa
WHERE fcpa.cal_period_id = p_cal_period_id
AND fcpa.attribute_id =
gcs_utility_pkg.g_dimension_attr_info ('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').attribute_id
AND fcpa.version_id =
gcs_utility_pkg.g_dimension_attr_info ('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').version_id;
SELECT Ending_Rate_Type
INTO l_rate_type
FROM GCS_CURR_TREATMENTS_B
WHERE ENABLED_FLAG = 'Y'
AND DEFAULT_FLAG = 'Y';
SELECT conversion_rate
INTO l_conv_rate
FROM GL_DAILY_RATES
WHERE From_Currency = p_Source_Currency
AND TO_Currency = P_Target_Currency
AND Conversion_type = l_rate_type
AND Conversion_date = l_period_end_date;
SELECT conversion_rate
INTO l_conv_rate
FROM GL_DAILY_RATES
WHERE From_Currency = p_Source_Currency
AND TO_Currency = P_Target_Currency
AND Conversion_type = 'Corporate'
AND Conversion_date = l_period_end_date;
delete from gcs_cal_period_maps_gt;
INSERT INTO gcs_cal_period_maps_gt
( source_cal_period_id,
target_cal_period_id)
VALUES
( p_cal_period_id,
p_cal_period_id);
INSERT INTO gcs_cal_period_maps_gt
( source_cal_period_id,
target_cal_period_id)
SELECT fcpb_source.cal_period_id,
p_cal_period_id
FROM fem_cal_periods_b fcpb_source,
fem_cal_periods_b fcpb_target,
gcs_cal_period_maps gcpm,
fem_cal_periods_attr fcpb_source_num,
fem_cal_periods_attr fcpb_source_year,
gcs_cal_period_map_dtls gcpmd
WHERE fcpb_target.cal_period_id = p_cal_period_id
AND fcpb_target.calendar_id = gcpm.target_calendar_id
AND fcpb_target.dimension_group_id = gcpm.target_dimension_group_id
AND gcpm.cal_period_map_id = gcpmd.cal_period_map_id
AND gcpmd.target_period_number = l_cal_period_record.cal_period_number
AND fcpb_source_num.attribute_id = l_period_num_attr
AND fcpb_source_num.version_id = l_period_num_version
AND fcpb_source_year.attribute_id = l_period_year_attr
AND fcpb_source_year.version_id = l_period_year_version
AND fcpb_source.cal_period_id = fcpb_source_num.cal_period_id
AND fcpb_source.cal_period_id = fcpb_source_year.cal_period_id
AND fcpb_source.calendar_id = gcpm.source_calendar_id
AND fcpb_source.dimension_group_id = gcpm.source_dimension_group_id
AND fcpb_source_num.number_assign_value = gcpmd.source_period_number
AND fcpb_source_year.number_assign_value = DECODE(gcpmd.target_relative_year_code,
'CURRENT', l_cal_period_record.cal_period_year,
'PRIOR', l_cal_period_record.cal_period_year + 1,
'FOLLOWING', l_cal_period_record.cal_period_year - 1);
INSERT INTO gcs_cal_period_maps_gt
( source_cal_period_id,
target_cal_period_id)
SELECT fcpb_source.cal_period_id,
fcpb_target.cal_period_id
FROM fem_cal_periods_b fcpb_source,
fem_cal_periods_b fcpb_target,
gcs_cal_period_maps gcpm,
fem_cal_periods_attr fcpb_source_num,
fem_cal_periods_attr fcpb_source_year,
gcs_cal_period_map_dtls gcpmd
WHERE fcpb_target.cal_period_id >= p_cal_period_id
AND fcpb_target.calendar_id = gcpm.target_calendar_id
AND fcpb_target.dimension_group_id = gcpm.target_dimension_group_id
AND gcpm.cal_period_map_id = gcpmd.cal_period_map_id
AND gcpmd.target_period_number = l_cal_period_record.cal_period_number
AND fcpb_source_num.attribute_id = l_period_num_attr
AND fcpb_source_num.version_id = l_period_num_version
AND fcpb_source_year.attribute_id = l_period_year_attr
AND fcpb_source_year.version_id = l_period_year_version
AND fcpb_source.cal_period_id = fcpb_source_num.cal_period_id
AND fcpb_source.cal_period_id = fcpb_source_year.cal_period_id
AND fcpb_source.calendar_id = gcpm.source_calendar_id
AND fcpb_source.dimension_group_id = gcpm.source_dimension_group_id
AND fcpb_source_num.number_assign_value = gcpmd.source_period_number
AND fcpb_source_year.number_assign_value = DECODE(gcpmd.target_relative_year_code,
'CURRENT', l_cal_period_record.cal_period_year,
'PRIOR', l_cal_period_record.cal_period_year + 1,
'FOLLOWING', l_cal_period_record.cal_period_year - 1);
INSERT INTO gcs_cal_period_maps_gt
( source_cal_period_id,
target_cal_period_id)
SELECT distinct target_cal_period_id,
target_cal_period_id
FROM gcs_cal_period_maps_gt
WHERE target_cal_period_id <> p_cal_period_id;
INSERT INTO gcs_cal_period_maps_gt
( source_cal_period_id,
target_cal_period_id)
SELECT p_cal_period_id,
fcpb_target.cal_period_id
FROM fem_cal_periods_b fcpb_source,
fem_cal_periods_b fcpb_target,
gcs_cal_period_maps gcpm,
fem_cal_periods_attr fcpb_target_num,
fem_cal_periods_attr fcpb_target_year,
gcs_cal_period_map_dtls gcpmd
WHERE fcpb_source.cal_period_id = p_cal_period_id
AND fcpb_source.calendar_id = gcpm.source_calendar_id
AND fcpb_source.dimension_group_id = gcpm.source_dimension_group_id
AND gcpm.cal_period_map_id = gcpmd.cal_period_map_id
AND gcpmd.source_period_number = l_cal_period_record.cal_period_number
AND fcpb_target_num.attribute_id = l_period_num_attr
AND fcpb_target_num.version_id = l_period_num_version
AND fcpb_target_year.attribute_id = l_period_year_attr
AND fcpb_target_year.version_id = l_period_year_version
AND fcpb_target.cal_period_id = fcpb_target_num.cal_period_id
AND fcpb_target.cal_period_id = fcpb_target_year.cal_period_id
AND fcpb_target.calendar_id = gcpm.target_calendar_id
AND fcpb_target.dimension_group_id = gcpm.target_dimension_group_id
AND fcpb_target_num.number_assign_value = gcpmd.target_period_number
AND fcpb_target_year.number_assign_value = DECODE(gcpmd.target_relative_year_code,
'CURRENT', l_cal_period_record.cal_period_year,
'PRIOR', l_cal_period_record.cal_period_year - 1,
'FOLLOWING', l_cal_period_record.cal_period_year + 1);
SELECT fch_global_vs_combo_id
INTO g_fch_global_vs_combo_id
FROM gcs_system_options;