[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT gcr.parent_entity_id
FROM gcs_cons_relationships gcr, gcs_cons_eng_runs gcer
WHERE gcr.hierarchy_id = p_hierarchy_id
AND gcr.child_entity_id = p_child_entity_id
AND gcr.dominant_parent_flag = 'Y'
AND gcr.hierarchy_id = gcer.hierarchy_id
AND gcr.parent_entity_id = gcer.run_entity_id
AND gcer.cal_period_id = p_cal_period_id
AND gcer.most_recent_flag = 'Y'
AND gcer.impacted_flag = 'N'
AND p_end_date BETWEEN gcr.start_date AND
NVL(gcr.end_date, p_end_date);
l_rows_updated NUMBER(1);
SELECT gcpm.cal_period_map_id,
gcpm.SOURCE_CALENDAR_ID,
gcpm.SOURCE_DIMENSION_GROUP_ID,
gcpm.TARGET_CALENDAR_ID,
gcpm.TARGET_DIMENSION_GROUP_ID
INTO l_cal_period_map_id,
l_src_calendar_id,
l_src_dimension_group_id,
l_tgt_calendar_id,
l_tgt_dimension_group_id
FROM GCS_CAL_PERIOD_MAPS gcpm,
gcs_hierarchies_b ghb,
fem_cal_periods_b fcpb
WHERE gcpm.SOURCE_CALENDAR_ID = fcpb.CALENDAR_ID
AND gcpm.SOURCE_DIMENSION_GROUP_ID = fcpb.DIMENSION_GROUP_ID
AND gcpm.TARGET_CALENDAR_ID = ghb.CALENDAR_ID
AND gcpm.TARGET_DIMENSION_GROUP_ID = ghb.DIMENSION_GROUP_ID
AND ghb.HIERARCHY_ID = p_hierarchy_id
AND fcpb.cal_period_id = p_cal_period_id;
SELECT fcpb.cal_period_id
INTO l_target_cal_period_id
FROM fem_cal_periods_b fcpb,
fem_cal_periods_attr fcpa_number,
fem_cal_periods_attr fcpa_year,
gcs_cal_period_map_dtls gcpmd
WHERE gcpmd.cal_period_map_id = l_cal_period_map_id
AND fcpb.calendar_id = l_tgt_calendar_id
AND fcpb.dimension_group_id = l_tgt_dimension_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 = l_period_num_attribute_id
AND fcpa_year.attribute_id = l_acct_year_attribute_id
AND fcpa_number.version_id = l_period_num_version_id
AND fcpa_year.version_id = l_acct_year_version_id
AND fcpa_number.number_assign_value = gcpmd.target_period_number
AND gcpmd.source_period_number =
l_cal_period_record.cal_period_number
AND fcpa_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 date_assign_value
INTO l_end_date
FROM fem_cal_periods_attr
WHERE cal_period_id = l_cal_period_id
AND attribute_id = l_end_date_attribute_id
AND version_id = l_end_date_version_id;
l_rows_updated := 0;
UPDATE gcs_cons_eng_runs
SET impacted_flag = 'Y'
WHERE hierarchy_id = p_hierarchy_id
AND run_entity_id = v_parent_entity.parent_entity_id
AND cal_period_id = l_cal_period_id
AND most_recent_flag = 'Y';
l_rows_updated := 0;
l_rows_updated := 1;
'SQL%ROWCOUNT = ' || l_rows_updated);
IF (l_rows_updated = 0) THEN
EXIT;
PROCEDURE insert_impact_analysis(p_run_name IN VARCHAR2,
p_consolidation_entity_id IN NUMBER,
p_child_entity_id IN NUMBER,
p_message_name IN VARCHAR2,
p_pre_relationship_id IN NUMBER DEFAULT NULL,
p_post_relationship_id IN NUMBER DEFAULT NULL,
p_date_token IN DATE DEFAULT NULL,
p_stat_entry_id IN NUMBER DEFAULT NULL,
p_entry_id IN NUMBER DEFAULT NULL,
p_orig_entry_id IN NUMBER DEFAULT NULL,
p_pre_prop_entry_id IN NUMBER DEFAULT NULL,
p_pre_prop_stat_entry_id IN NUMBER DEFAULT NULL,
p_load_id IN NUMBER DEFAULT NULL)
IS
BEGIN
IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
g_api || '.INSERT_IMPACT_ANALYSIS.begin',
'<>');
INSERT INTO gcs_cons_impact_analyses
(impact_analysis_id,
run_name,
consolidation_entity_id,
child_entity_id,
message_name,
pre_relationship_id,
post_relationship_id,
date_token,
stat_entry_id,
entry_id,
original_entry_id,
pre_prop_entry_id,
pre_prop_stat_entry_id,
creation_date,
created_by,
last_updated_by,
last_update_date,
last_update_login,
object_version_number,
load_id)
VALUES
(gcs_cons_impact_analyses_s.nextval,
p_run_name,
p_consolidation_entity_id,
p_child_entity_id,
p_message_name,
p_pre_relationship_id,
p_post_relationship_id,
p_date_token,
p_stat_entry_id,
p_entry_id,
p_orig_entry_id,
p_pre_prop_entry_id,
p_pre_prop_stat_entry_id,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.LOGIN_ID,
1, -- Bugfix 3718098 : Added OBJECT_VERSION_NUMBER per Release Standards,
p_load_id);
g_api || '.INSERT_IMPACT_ANALYSIS.end',
'<>');
END insert_impact_analysis;
CURSOR c_select_hier_info(p_pre_cons_relationship_id IN NUMBER, p_post_cons_relationship_id IN NUMBER) IS
SELECT gcr.hierarchy_id,
gcr.parent_entity_id,
gcr.child_entity_id,
gcr.cons_relationship_id,
gcr.curr_treatment_id,
gcr.treatment_id,
gcr.ownership_percent
FROM gcs_cons_relationships gcr
WHERE gcr.cons_relationship_id IN
(p_post_cons_relationship_id, p_pre_cons_relationship_id);
FOR v_select_hier_info IN c_select_hier_info(l_pre_cons_relationship_id,
l_post_cons_relationship_id) LOOP
IF (l_hierarchy_id IS NULL) THEN
l_hierarchy_id := v_select_hier_info.hierarchy_id;
l_parent_entity_id := v_select_hier_info.parent_entity_id;
l_child_entity_id := v_select_hier_info.child_entity_id;
IF (v_select_hier_info.cons_relationship_id =
l_pre_cons_relationship_id) THEN
l_orig_treatment_id := v_select_hier_info.treatment_id;
l_orig_pct_owned := v_select_hier_info.ownership_percent;
l_orig_curr_trtmnt_id := v_select_hier_info.curr_treatment_id;
l_new_treatment_id := v_select_hier_info.treatment_id;
l_new_pct_owned := v_select_hier_info.ownership_percent;
l_new_curr_trtmnt_id := v_select_hier_info.curr_treatment_id;
gcs_cons_monitor_pkg.update_data_status(p_load_id => NULL,
p_cons_rel_id => l_post_cons_relationship_id,
p_hierarchy_id => l_hierarchy_id,
p_transaction_type => 'ACQ');
gcs_cons_monitor_pkg.update_data_status(p_load_id => NULL,
p_cons_rel_id => l_pre_cons_relationship_id,
p_hierarchy_id => l_hierarchy_id,
p_transaction_type => 'DIS');
SELECT fcb.cal_period_id
INTO l_cal_period_id
FROM fem_cal_periods_b fcb,
gcs_hierarchies_b fhb,
fem_cal_periods_attr fcpa_start_date,
fem_cal_periods_attr fcpa_end_date
WHERE fhb.hierarchy_id = l_hierarchy_id
AND fcb.calendar_id = fhb.calendar_id
AND fcb.dimension_group_id = fhb.dimension_group_id
AND fcb.cal_period_id = fcpa_start_date.cal_period_id
AND fcb.cal_period_id = fcpa_end_date.cal_period_id
AND fcpa_start_date.attribute_id = l_cp_start_date_attr_id
AND fcpa_start_date.version_id = l_cp_start_date_version_id
AND fcpa_end_date.attribute_id = l_cp_end_date_attr_id
AND fcpa_end_date.version_id = l_cp_end_date_version_id
AND l_trx_date BETWEEN fcpa_start_date.date_assign_value AND
fcpa_end_date.date_assign_value;
UPDATE gcs_ad_transactions gat
SET gat.enabled_flag = 'N', gat.hidden_flag = 'N'
WHERE gat.transaction_date >= l_trx_date
AND (EXISTS
(SELECT 'X'
FROM gcs_cons_relationships pre
WHERE pre.hierarchy_id = l_hierarchy_id
AND pre.cons_relationship_id = gat.pre_cons_relationship_id
AND pre.parent_entity_id = l_parent_entity_id
AND pre.child_entity_id = l_child_entity_id) OR EXISTS
(SELECT 'X'
FROM gcs_cons_relationships post
WHERE post.hierarchy_id = l_hierarchy_id
AND post.cons_relationship_id = gat.post_cons_relationship_id
AND post.parent_entity_id = l_parent_entity_id
AND post.child_entity_id = l_child_entity_id));
INSERT INTO GCS_AD_TRANSACTIONS
(ad_transaction_id,
pre_cons_relationship_id,
post_cons_relationship_id,
transaction_type_code,
hidden_flag,
enabled_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
intermediate_treatment_id,
intermediate_percent_owned,
cal_period_id,
transaction_date)
VALUES
(GCS_AD_TRANSACTIONS_S.NEXTVAL,
DECODE(l_pre_cons_relationship_id,
-1,
NULL,
l_pre_cons_relationship_id),
DECODE(l_post_cons_relationship_id,
-1,
NULL,
l_post_cons_relationship_id),
l_trx_type_code,
'N',
l_enabled_flag,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID,
l_intermediate_trtmnt_id,
l_intermediate_pct_owned,
l_cal_period_id,
l_trx_date);
SELECT run_name
INTO l_run_name
FROM gcs_cons_eng_runs
WHERE run_entity_id = l_parent_entity_id
AND hierarchy_id = l_hierarchy_id
AND most_recent_flag = 'Y'
AND cal_period_id = l_cal_period_id;
SELECT gcer.run_name, gcer.cal_period_id
INTO l_run_name, l_cal_period_id
FROM gcs_cons_eng_runs gcer
WHERE gcer.run_entity_id = l_parent_entity_id
AND gcer.hierarchy_id = l_hierarchy_id
AND most_recent_flag = 'Y'
AND cal_period_id =
(SELECT min(gcer_inner.cal_period_id)
FROM gcs_cons_eng_runs gcer_inner
WHERE gcer_inner.run_entity_id = l_parent_entity_id
AND gcer_inner.hierarchy_id = l_hierarchy_id
AND gcer_inner.most_recent_flag = 'Y'
AND gcer_inner.cal_period_id > l_cal_period_id);
SELECT fev_parent.entity_name, fev_child.entity_name
INTO l_parent_entity_name, l_child_entity_name
FROM fem_entities_vl fev_parent, fem_entities_vl fev_child
WHERE fev_parent.entity_id = l_parent_entity_id
AND fev_child.entity_id = l_child_entity_id;
insert_impact_analysis(p_run_name => l_run_name,
p_consolidation_entity_id => l_parent_entity_id,
p_child_entity_id => l_child_entity_id,
p_message_name => 'GCS_ENTITY_ACQUIRED',
p_post_relationship_id => l_post_cons_relationship_id,
p_date_token => l_trx_date);
insert_impact_analysis(p_run_name => l_run_name,
p_consolidation_entity_id => l_parent_entity_id,
p_child_entity_id => l_child_entity_id,
p_message_name => 'GCS_ENTITY_DISPOSED',
p_pre_relationship_id => l_pre_cons_relationship_id,
p_date_token => l_trx_date);
insert_impact_analysis(p_run_name => l_run_name,
p_consolidation_entity_id => l_parent_entity_id,
p_child_entity_id => l_child_entity_id,
p_message_name => 'GCS_CONS_TREATMENT_ALTERED',
p_pre_relationship_id => l_pre_cons_relationship_id,
p_post_relationship_id => l_post_cons_relationship_id,
p_date_token => l_trx_date);
SELECT gcs_orig.treatment_name, gcs_new.treatment_name
INTO l_orig_treatment, l_new_treatment
FROM gcs_treatments_tl gcs_orig, gcs_treatments_tl gcs_new
WHERE gcs_orig.treatment_id = l_orig_treatment_id
AND gcs_new.treatment_id = l_new_treatment_id
AND gcs_orig.language = USERENV('LANG')
AND gcs_new.language = USERENV('LANG');
insert_impact_analysis(p_run_name => l_run_name,
p_consolidation_entity_id => l_parent_entity_id,
p_child_entity_id => l_child_entity_id,
p_message_name => 'GCS_PCT_OWNERSHIP_ALTERED',
p_pre_relationship_id => l_pre_cons_relationship_id,
p_post_relationship_id => l_post_cons_relationship_id,
p_date_token => l_trx_date);
insert_impact_analysis(p_run_name => l_run_name,
p_consolidation_entity_id => l_parent_entity_id,
p_child_entity_id => l_child_entity_id,
p_message_name => 'GCS_CURR_TREATMENT_ALTERED',
p_pre_relationship_id => l_pre_cons_relationship_id,
p_post_relationship_id => l_post_cons_relationship_id,
p_date_token => l_trx_date);
SELECT gcs_orig.curr_treatment_name, gcs_new.curr_treatment_name
INTO l_orig_treatment, l_new_treatment
FROM gcs_curr_treatments_tl gcs_orig,
gcs_curr_treatments_tl gcs_new
WHERE gcs_orig.curr_treatment_id = l_orig_curr_trtmnt_id
AND gcs_new.curr_treatment_id = l_new_curr_trtmnt_id
AND gcs_orig.language = USERENV('LANG')
AND gcs_new.language = USERENV('LANG');
UPDATE gcs_cons_eng_runs
SET impacted_flag = 'Y'
WHERE run_name = l_run_name
AND run_entity_id = l_parent_entity_id
AND most_recent_flag = 'Y';
SELECT gcer.run_name,
gcer.run_entity_id,
gcer.hierarchy_id,
gcr.cons_relationship_id
FROM gcs_cons_eng_runs gcer,
gcs_cons_relationships gcr,
fem_cal_periods_attr fcpa,
gcs_entity_cons_attrs geca,
gcs_cal_period_maps_gt gcpmt,
fem_cal_periods_b fcpb,
gcs_hierarchies_b ghb
WHERE gcer.most_recent_flag = 'Y'
AND gcer.balance_type_code = p_balance_type_code
AND gcer.cal_period_id = gcpmt.target_cal_period_id
AND gcer.hierarchy_id = gcr.hierarchy_id
AND gcer.run_entity_id = gcr.parent_entity_id
AND gcr.child_entity_id = p_entity_id
AND gcr.dominant_parent_flag = 'Y'
AND geca.hierarchy_id = gcr.hierarchy_id
AND gcer.hierarchy_id = ghb.hierarchy_id
AND gcpmt.source_cal_period_id = p_cal_period_id
AND gcpmt.target_cal_period_id = fcpb.cal_period_id
AND ghb.calendar_id = fcpb.calendar_id
AND ghb.dimension_group_id = fcpb.dimension_group_id
AND geca.entity_id = gcr.child_entity_id
AND geca.currency_code = p_currency_code
AND fcpa.cal_period_id = fcpb.cal_period_id
AND fcpa.attribute_id = l_cal_period_end_date_attr
AND fcpa.version_id = l_cal_period_end_date_ver
AND fcpa.date_assign_value BETWEEN gcr.start_date AND
NVL(gcr.end_date, fcpa.date_assign_value);
SELECT gdsd.entity_id,
gdsd.cal_period_id,
NVL(gdsd.currency_code, fla.dim_attribute_varchar_member),
gdsd.balance_type_code,
gdsd.load_method_code,
gdsd.currency_type_code,
gdsd.load_id,
DECODE(status_code,
-- Bug Fix: 5647099
'UNDONE',
'GCS_PRISTINE_DATA_UNDO_LOAD',
DECODE(load_method_code,
'INITIAL_LOAD',
'GCS_PRISTINE_DATA_FULL_LOAD',
'GCS_PRISTINE_DATA_INC_LOAD')),
DECODE(gdsd.currency_code,
NULL,
'ENTERED',
fla.dim_attribute_varchar_member,
'ENTERED',
'TRANSLATED')
INTO l_data_sub_info.entity_id,
l_data_sub_info.cal_period_id,
l_data_sub_info.currency_code,
l_data_sub_info.balance_type_code,
l_data_sub_info.load_method_code,
l_data_sub_info.currency_type_code,
l_data_sub_info.load_id,
l_message_name,
l_currency_type_code
FROM gcs_data_sub_dtls gdsd, fem_ledgers_attr fla
WHERE gdsd.load_id = l_load_id
AND fla.ledger_id = l_ledger_id
AND fla.attribute_id = l_func_currency_attr
AND fla.version_id = l_func_currency_ver;
insert_impact_analysis(p_run_name => v_impacted_runs.run_name,
p_consolidation_entity_id => v_impacted_runs.run_entity_id,
p_child_entity_id => l_data_sub_info.entity_id,
p_message_name => l_message_name,
p_date_token => sysdate,
p_load_id => l_data_sub_info.load_id);
UPDATE gcs_cons_eng_runs
SET impacted_flag = 'Y'
WHERE run_name = v_impacted_runs.run_name
AND run_entity_id = v_impacted_runs.run_entity_id
AND most_recent_flag = 'Y';
SELECT geh.start_cal_period_id,
geh.end_cal_period_id,
geh.hierarchy_id,
geh.entity_id,
geh.balance_type_code,
fcpa_start.date_assign_value
INTO l_start_cal_period_id,
l_end_cal_period_id,
l_hierarchy_id,
l_entity_id,
l_bal_type_code,
l_start_cp_end_date
FROM gcs_entry_headers geh, fem_cal_periods_attr fcpa_start
WHERE geh.entry_id = l_entry_id
AND geh.start_cal_period_id = fcpa_start.cal_period_id
AND fcpa_start.attribute_id = l_cal_period_attr
AND fcpa_start.version_id = l_cal_period_version;
SELECT parent_entity_id
INTO l_cons_entity_id
FROM gcs_cons_relationships
WHERE hierarchy_id = l_hierarchy_id
AND child_entity_id = l_entity_id
AND dominant_parent_flag = 'Y'
AND l_start_cp_end_date BETWEEN start_date AND
NVL(end_date, l_start_cp_end_date);
SELECT run_name
INTO l_run_name
FROM gcs_cons_eng_runs
WHERE most_recent_flag = 'Y'
AND hierarchy_id = l_hierarchy_id
AND balance_type_code = l_bal_type_code
AND cal_period_id = l_start_cal_period_id
AND run_entity_id = l_cons_entity_id;
SELECT gcer.run_name, gcer.cal_period_id
INTO l_run_name, l_start_cal_period_id
FROM gcs_cons_eng_runs gcer
WHERE gcer.run_entity_id = l_cons_entity_id
AND gcer.hierarchy_id = l_hierarchy_id
AND gcer.most_recent_flag = 'Y'
AND gcer.balance_type_code = l_bal_type_code
AND gcer.cal_period_id =
(SELECT min(gcer_inner.cal_period_id)
FROM gcs_cons_eng_runs gcer_inner
WHERE gcer_inner.run_entity_id = l_cons_entity_id
AND gcer_inner.hierarchy_id = l_hierarchy_id
AND gcer_inner.most_recent_flag = 'Y'
AND gcer_inner.balance_type_code = l_bal_type_code
AND gcer_inner.cal_period_id > l_start_cal_period_id);
insert_impact_analysis(p_run_name => l_run_name,
p_consolidation_entity_id => l_cons_entity_id,
p_child_entity_id => l_entity_id,
p_message_name => 'GCS_ACQDISP_CREATED',
p_pre_relationship_id => null,
p_post_relationship_id => null,
p_date_token => sysdate,
p_entry_id => l_entry_id);
insert_impact_analysis(p_run_name => l_run_name,
p_consolidation_entity_id => l_cons_entity_id,
p_child_entity_id => l_entity_id,
p_message_name => 'GCS_ACQDISP_MODIFIED',
p_pre_relationship_id => null,
p_post_relationship_id => null,
p_date_token => sysdate,
p_entry_id => l_entry_id,
p_orig_entry_id => l_orig_entry_id);
insert_impact_analysis(p_run_name => l_run_name,
p_consolidation_entity_id => l_cons_entity_id,
p_child_entity_id => l_entity_id,
p_message_name => 'GCS_ACQDISP_UNDO',
p_pre_relationship_id => null,
p_post_relationship_id => null,
p_date_token => sysdate,
p_entry_id => l_entry_id,
p_orig_entry_id => l_orig_entry_id);
UPDATE gcs_cons_eng_runs
SET impacted_flag = 'Y'
WHERE run_name = l_run_name
AND run_entity_id = l_cons_entity_id
AND most_recent_flag = 'Y';
SELECT geh.start_cal_period_id,
geh.end_cal_period_id,
geh.hierarchy_id,
geh.entity_id,
geh.balance_type_code,
fcpa_start.date_assign_value
INTO l_start_cal_period_id,
l_end_cal_period_id,
l_hierarchy_id,
l_entity_id,
l_bal_type_code,
l_start_cp_end_date
FROM gcs_entry_headers geh, fem_cal_periods_attr fcpa_start
WHERE geh.entry_id = l_entry_id
AND geh.start_cal_period_id = fcpa_start.cal_period_id
AND fcpa_start.attribute_id =
gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
.attribute_id
AND fcpa_start.version_id =
gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
.version_id;
SELECT parent_entity_id
INTO l_cons_entity_id
FROM gcs_cons_relationships
WHERE hierarchy_id = l_hierarchy_id
AND child_entity_id = l_entity_id
AND dominant_parent_flag = 'Y'
AND l_start_cp_end_date BETWEEN start_date AND
NVL(end_date, l_start_cp_end_date);
SELECT run_name
INTO l_run_name
FROM gcs_cons_eng_runs
WHERE most_recent_flag = 'Y'
AND hierarchy_id = l_hierarchy_id
AND cal_period_id = l_start_cal_period_id
AND balance_type_code = l_bal_type_code
AND run_entity_id = l_cons_entity_id;
insert_impact_analysis(p_run_name => l_run_name,
p_consolidation_entity_id => l_cons_entity_id,
p_child_entity_id => l_entity_id,
p_message_name => 'GCS_ADJUSTMENT_CREATED',
p_pre_relationship_id => null,
p_post_relationship_id => null,
p_date_token => sysdate,
p_entry_id => l_entry_id);
insert_impact_analysis(p_run_name => l_run_name,
p_consolidation_entity_id => l_cons_entity_id,
p_child_entity_id => l_entity_id,
p_message_name => 'GCS_ADJUSTMENT_MODIFIED',
p_pre_relationship_id => null,
p_post_relationship_id => null,
p_date_token => sysdate,
p_entry_id => l_entry_id,
p_orig_entry_id => l_orig_entry_id);
UPDATE gcs_cons_eng_runs
SET impacted_flag = 'Y'
WHERE run_name = l_run_name
AND run_entity_id = l_cons_entity_id
AND most_recent_flag = 'Y';
SELECT gcer.run_name,
gcer.run_entity_id,
gcerd.child_entity_id,
gcer.hierarchy_id,
gcer.cal_period_id
FROM gcs_cons_eng_runs gcer,
gcs_cons_eng_run_dtls gcerd,
gcs_curr_treatments_b gctb,
fem_cal_periods_b fcpb,
fem_cal_periods_attr fcpa_end,
gcs_cons_relationships gcr,
gcs_entity_cons_attrs geca_parent,
gcs_entity_cons_attrs geca_child
WHERE gcer.cal_period_id = fcpb.cal_period_id
AND fcpb.cal_period_id = fcpa_end.cal_period_id
AND fcpa_end.attribute_id = l_cp_end_date_attr_id
AND fcpa_end.version_id = l_cp_end_date_version_id
AND fcpa_end.date_assign_value BETWEEN p_from_conv_date AND
p_to_conv_date
AND gcer.most_recent_flag = 'Y'
AND gcer.run_name = gcerd.run_name
AND gcer.run_entity_id = gcerd.consolidation_entity_id
AND gcerd.category_code = 'TRANSLATION'
AND gcerd.cons_relationship_id = gcr.cons_relationship_id
AND gcr.curr_treatment_id = gctb.curr_treatment_id
AND p_conversion_type IN
(gctb.ending_rate_type, gctb.average_rate_type)
AND gcr.parent_entity_id = geca_parent.entity_id
AND gcr.hierarchy_id = geca_parent.hierarchy_id
AND gcr.dominant_parent_flag = 'Y'
AND gcr.child_entity_id = geca_child.entity_id
AND gcr.hierarchy_id = geca_child.hierarchy_id
AND geca_parent.currency_code = p_to_currency
AND geca_child.currency_code = p_from_currency;
insert_impact_analysis(p_run_name => v_impacted_runs.run_name,
p_consolidation_entity_id => v_impacted_runs.run_entity_id,
p_child_entity_id => v_impacted_runs.child_entity_id,
p_message_name => 'GCS_TRANSLATION_RATES_ALTERED',
p_date_token => sysdate);
UPDATE gcs_cons_eng_runs
SET impacted_flag = 'Y'
WHERE run_name = v_impacted_runs.run_name
ANd run_entity_id = v_impacted_runs.run_entity_id;
SELECT gcer.run_name, gcer.run_entity_id
INTO l_run_name, l_run_entity_id
FROM gcs_cons_eng_runs gcer, gcs_cons_eng_run_dtls gcerd
WHERE gcer.hierarchy_id = l_hierarchy_id
AND gcer.cal_period_id = l_cal_period_id
AND gcer.most_recent_flag = 'Y'
AND gcer.run_entity_id = gcerd.consolidation_entity_id
AND gcer.run_name = gcerd.run_name
AND gcerd.category_code = 'TRANSLATION'
AND gcerd.child_entity_id = l_entity_id;
insert_impact_analysis(p_run_name => l_run_name,
p_consolidation_entity_id => l_run_entity_id,
p_child_entity_id => l_entity_id,
p_message_name => 'GCS_HISTORICAL_RATES_ALTERED',
p_date_token => sysdate);
UPDATE gcs_cons_eng_runs
SET impacted_flag = 'Y'
WHERE run_name = l_run_name
ANd run_entity_id = l_run_entity_id;
SELECT parent_entity_id
INTO l_run_parent_entity_id
FROM gcs_cons_eng_runs
WHERE run_name = p_run_name
AND run_entity_id = p_run_entity_id;
SELECT parent_entity_id
INTO l_parent_entity_id
FROM gcs_cons_relationships
WHERE hierarchy_id = p_hierarchy_id
AND child_entity_id = p_run_entity_id
AND dominant_parent_flag = 'Y'
AND p_cal_period_end_date BETWEEN start_date AND
NVL(end_date, p_cal_period_end_date);
SELECT run_name
INTO l_run_name
FROM gcs_cons_eng_runs
WHERE run_entity_id = l_parent_entity_id
AND cal_period_id = p_cal_period_id
AND hierarchy_id = p_hierarchy_id
AND balance_type_code = p_balance_type_code
AND most_recent_flag = 'Y';
insert_impact_analysis(p_run_name => l_run_name,
p_consolidation_entity_id => l_parent_entity_id,
p_child_entity_id => p_run_entity_id,
p_message_name => 'GCS_SUB_RECONSOLIDATED',
p_date_token => sysdate);
UPDATE gcs_cons_eng_runs
SET impacted_flag = 'Y'
WHERE run_name = l_run_name
AND run_entity_id = l_parent_entity_id;
SELECT run_name
INTO l_run_name
FROM gcs_cons_eng_runs
WHERE hierarchy_id = p_hierarchy_id
AND run_entity_id = p_run_entity_id
AND balance_type_code = p_balance_type_code
AND cal_period_id = l_cal_period_info.next_cal_period_id
AND most_recent_flag = 'Y';
insert_impact_analysis(p_run_name => l_run_name,
p_consolidation_entity_id => p_run_entity_id,
p_child_entity_id => p_run_entity_id,
p_message_name => 'GCS_PRIOR_PD_RECONSOLIDATED',
p_date_token => sysdate);
UPDATE gcs_cons_eng_runs
SET impacted_flag = 'Y'
WHERE run_name = l_run_name
AND run_entity_id = p_run_entity_id;
PROCEDURE value_set_map_updated(p_dimension_id IN NUMBER,
p_eff_start_date IN DATE,
p_eff_end_date IN DATE,
p_consolidation_vs_id IN NUMBER) IS
TYPE r_run_entity_info IS RECORD(
run_name VARCHAR2(240),
run_entity_id NUMBER,
cal_period_id NUMBER,
hierarchy_id NUMBER(15));
SELECT min(gcer.cal_period_id) cal_period_id,
gcer.hierarchy_id hierarchy_id,
min(fcpa_end.date_assign_value) end_date
FROM gcs_cons_eng_runs gcer,
fem_cal_periods_attr fcpa_start,
fem_cal_periods_attr fcpa_end
WHERE gcer.cal_period_id = fcpa_start.cal_period_id
AND gcer.cal_period_id = fcpa_end.cal_period_id
AND fcpa_start.date_assign_value >= p_eff_start_date
AND fcpa_end.date_assign_value <= p_eff_end_date
AND fcpa_start.attribute_id = l_cp_start_date_attr_id
AND fcpa_start.version_id = l_cp_start_date_version_id
AND fcpa_end.attribute_id = l_cp_end_date_attr_id
AND fcpa_end.version_id = l_cp_end_date_version_id
GROUP BY hierarchy_id;
SELECT gcr.parent_entity_id, gcr.child_entity_id, gcer.run_name
FROM gcs_cons_relationships gcr,
gcs_cons_eng_runs gcer,
fem_global_vs_combo_defs fgvcd,
gcs_entities_attr gea,
fem_ledgers_attr fla
WHERE gcr.hierarchy_id = p_hierarchy_id
AND gcr.hierarchy_id = gcer.hierarchy_id
AND gcer.most_recent_flag = 'Y'
AND gcer.cal_period_id = p_cal_period_id
AND gcr.parent_entity_id = gcer.run_entity_id
AND gcr.dominant_parent_flag = 'Y'
AND p_cal_period_end_date BETWEEN gcr.start_date AND
NVL(gcr.end_date, p_cal_period_end_date)
AND gcr.child_entity_id = gea.entity_id
AND gea.data_type_code = gcer.balance_type_code
AND p_cal_period_end_date BETWEEN gea.effective_start_date AND
NVL(gea.effective_end_date, p_cal_period_end_date)
AND gea.ledger_id = fla.ledger_id
AND fla.attribute_id = l_gvsc_attr_id
AND fla.version_id = l_gvsc_version_id
AND fla.dim_attribute_numeric_member = fgvcd.global_vs_combo_id
AND fgvcd.dimension_id = p_dimension_id
AND fgvcd.value_set_id <> p_consolidation_vs_id;
g_api || '.VALUE_SET_MAP_UPDATED.begin',
'<>');
g_api || '.VALUE_SET_MAP_UPDATED',
'Dimension Id : ' || p_dimension_id);
g_api || '.VALUE_SET_MAP_UPDATED',
'Effective Start Date : ' || p_eff_start_date);
g_api || '.VALUE_SET_MAP_UPDATED',
'Effective End Date : ' || p_eff_end_date);
g_api || '.VALUE_SET_MAP_UPDATED',
'Consolidation VS Id : ' || p_consolidation_vs_id);
g_api || '.VALUE_SET_MAP_UPDATED',
'Hierarchy Id : ' ||
v_impacted_hierarchies.hierarchy_id);
g_api || '.VALUE_SET_MAP_UPDATED',
'Cal Period Id : ' ||
v_impacted_hierarchies.cal_period_id);
g_api || '.VALUE_SET_MAP_UPDATED',
'Cal Period End Date : ' ||
v_impacted_hierarchies.end_date);
g_api || '.VALUE_SET_MAP_UPDATED',
'Parent Entity Id : ' ||
v_impacted_entities.parent_entity_id);
g_api || '.VALUE_SET_MAP_UPDATED',
'Child Entity Id : ' ||
v_impacted_entities.child_entity_id);
g_api || '.VALUE_SET_MAP_UPDATED',
'Run Name : ' || v_impacted_entities.run_name);
insert_impact_analysis(p_run_name => v_impacted_entities.run_name,
p_consolidation_entity_id => v_impacted_entities.parent_entity_id,
p_child_entity_id => v_impacted_entities.child_entity_id,
p_message_name => 'GCS_VS_MAP_UPDATED',
p_date_token => sysdate);
UPDATE gcs_cons_eng_runs
SET impacted_flag = 'Y'
WHERE run_name = l_tab_run_entity_info(l_character_index)
.run_name
AND run_entity_id = l_tab_run_entity_info(l_character_index)
.run_entity_id;
g_api || '.VALUE_SET_MAP_UPDATED.end',
'<>');
SELECT geh.start_cal_period_id,
geh.end_cal_period_id,
geh.hierarchy_id,
geh.entity_id,
geh.balance_type_code,
fcpa_start.date_assign_value
INTO l_start_cal_period_id,
l_end_cal_period_id,
l_hierarchy_id,
l_entity_id,
l_bal_type_code,
l_start_cp_end_date
FROM gcs_entry_headers geh, fem_cal_periods_attr fcpa_start
WHERE geh.entry_id = l_entry_id
AND geh.start_cal_period_id = fcpa_start.cal_period_id
AND fcpa_start.attribute_id =
gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
.attribute_id
AND fcpa_start.version_id =
gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
.version_id;
SELECT parent_entity_id
INTO l_cons_entity_id
FROM gcs_cons_relationships
WHERE hierarchy_id = l_hierarchy_id
AND child_entity_id = l_entity_id
AND dominant_parent_flag = 'Y'
AND l_start_cp_end_date BETWEEN start_date AND
NVL(end_date, l_start_cp_end_date);
SELECT run_name
INTO l_run_name
FROM gcs_cons_eng_runs
WHERE most_recent_flag = 'Y'
AND hierarchy_id = l_hierarchy_id
AND cal_period_id = l_start_cal_period_id
AND balance_type_code = l_bal_type_code
AND run_entity_id = l_cons_entity_id;
insert_impact_analysis(p_run_name => l_run_name,
p_consolidation_entity_id => l_cons_entity_id,
p_child_entity_id => l_entity_id,
p_message_name => 'GCS_ADJUSTMENT_DISABLED',
p_pre_relationship_id => null,
p_post_relationship_id => null,
p_date_token => sysdate,
p_entry_id => l_entry_id);
UPDATE gcs_cons_eng_runs
SET impacted_flag = 'Y'
WHERE run_name = l_run_name
AND run_entity_id = l_cons_entity_id
AND most_recent_flag = 'Y';