[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT epb_table_name
INTO l_table_name
FROM gcs_system_options;
SELECT gcr.child_entity_id,
gcr.cons_relationship_id,
geca_child.currency_code child_currency_code,
geca_parent.currency_code parent_currency_code,
fea.dim_attribute_varchar_member entity_type_code
FROM gcs_cons_relationships gcr,
fem_entities_attr fea,
gcs_entity_cons_attrs geca_child,
gcs_entity_cons_attrs geca_parent
WHERE gcr.hierarchy_id = p_hierarchy_id
AND gcr.parent_entity_id = p_entity_id
AND geca_child.hierarchy_id = p_hierarchy_id
AND geca_child.entity_id = gcr.child_entity_id
AND gcr.dominant_parent_flag = 'Y'
AND geca_parent.hierarchy_id = p_hierarchy_id
AND geca_parent.entity_id = gcr.parent_entity_id
AND p_cal_period_end_date BETWEEN gcr.start_date and NVL(end_date,to_date(p_cal_period_end_date,'DD-MM-RR'))
AND gcr.child_entity_id = fea.entity_id
AND fea.attribute_id = g_entity_type_attr
AND fea.version_id = g_entity_type_version
AND fea.dim_attribute_varchar_member IN ('O','X','C');
SELECT gcr.child_entity_id,
gcr.cons_relationship_id,
geca_child.currency_code child_currency_code,
geca_parent.currency_code parent_currency_code
FROM gcs_cons_relationships gcr,
gcs_entity_cons_attrs geca_child,
gcs_entity_cons_attrs geca_parent,
fem_entities_attr fea
WHERE gcr.hierarchy_id = p_hierarchy_id
AND gcr.parent_entity_id = p_entity_id
AND geca_child.hierarchy_id = p_hierarchy_id
AND geca_child.entity_id = gcr.child_entity_id
AND gcr.dominant_parent_flag = 'Y'
AND geca_parent.hierarchy_id = p_hierarchy_id
AND geca_parent.entity_id = gcr.parent_entity_id
AND gcr.child_entity_id = fea.entity_id
AND fea.attribute_id = g_entity_type_attr
AND fea.version_id = g_entity_type_version
AND fea.dim_attribute_varchar_member IN ('O','X')
AND p_cal_period_end_date BETWEEN gcr.start_date and NVL(end_date,to_date(p_cal_period_end_date,'DD-MM-RR'));
gcs_cons_eng_run_dtls_pkg.insert_row(p_run_name => cons_entity_wf_info.run_identifier,
p_consolidation_entity_id => cons_entity_wf_info.consolidation_entity,
p_category_code => 'DATAPREPARATION',
p_child_entity_id => v_immediate_children.child_entity_id,
p_entry_id => NULL,
p_stat_entry_id => null,
p_cons_relationship_id => v_immediate_children.cons_relationship_id,
p_run_detail_id => l_run_detail_id,
p_request_error_code => 'NOT_STARTED',
p_bp_request_error_code => 'NOT_STARTED');
gcs_cons_eng_run_dtls_pkg.insert_row(p_run_name => cons_entity_wf_info.run_identifier,
p_consolidation_entity_id => cons_entity_wf_info.consolidation_entity,
p_category_code => 'TRANSLATION',
p_child_entity_id => v_immediate_children.child_entity_id,
p_entry_id => NULL,
p_stat_entry_id => null,
p_cons_relationship_id => v_immediate_children.cons_relationship_id,
p_run_detail_id => l_run_detail_id,
p_request_error_code => l_xlate_request_error_code,
p_bp_request_error_code => l_xlate_request_error_code);
gcs_cons_eng_run_dtls_pkg.insert_row(p_run_name => cons_entity_wf_info.run_identifier,
p_consolidation_entity_id => cons_entity_wf_info.consolidation_entity,
p_category_code => 'DATAPREPARATION',
p_child_entity_id => v_oper_entity.child_entity_id,
p_cons_relationship_id => v_oper_entity.cons_relationship_id,
p_run_detail_id => l_run_detail_id,
p_request_error_code => 'NOT_STARTED',
p_bp_request_error_code => 'NOT_STARTED');
gcs_cons_eng_run_dtls_pkg.insert_row(p_run_name => cons_entity_wf_info.run_identifier,
p_consolidation_entity_id => cons_entity_wf_info.consolidation_entity,
p_category_code => 'TRANSLATION',
p_child_entity_id => v_oper_entity.child_entity_id,
p_cons_relationship_id => v_oper_entity.cons_relationship_id,
p_run_detail_id => l_run_detail_id,
p_request_error_code => l_xlate_request_error_code,
p_bp_request_error_code => l_xlate_request_error_code);
SELECT gcerd.child_entity_id,
gcerd.cons_relationship_id,
gcerd.run_detail_id,
fev.entity_name,
DECODE(geca_parent.currency_code, geca_child.currency_code, 'N', 'Y') translation_required
FROM gcs_cons_eng_run_dtls gcerd,
fem_entities_attr fea,
fem_entities_vl fev,
gcs_entity_cons_attrs geca_parent,
gcs_entity_cons_attrs geca_child,
gcs_cons_eng_runs gcer
WHERE gcerd.run_name = p_run_name
AND gcerd.consolidation_entity_id = p_cons_entity_id
AND gcerd.entry_id IS NULL
AND gcerd.category_code = 'DATAPREPARATION'
AND gcerd.child_entity_id = fev.entity_id
AND gcerd.child_entity_id = fea.entity_id
AND fea.attribute_id = g_entity_type_attr
AND fea.version_id = g_entity_type_version
AND fea.dim_attribute_varchar_member IN ('O','X')
AND geca_parent.entity_id = p_cons_entity_id
AND geca_child.entity_id = fev.entity_id
AND geca_parent.hierarchy_id = geca_child.hierarchy_id
AND gcer.hierarchy_id = geca_child.hierarchy_id
AND gcer.run_name = gcerd.run_name
ANd gcer.run_entity_id = gcerd.consolidation_entity_id;
SELECT entry_id, stat_entry_id
INTO l_entry_id, l_stat_entry_id
FROM gcs_cons_eng_run_dtls
WHERE run_detail_id = oper_entity_wf_info.run_detail_id;
gcs_interco_dynamic_pkg.insert_interco_trx( p_entry_id => l_entry_id,
p_stat_entry_id => NVL(l_stat_entry_id, -1),
p_hierarchy_id => l_parameter_list.hierarchy_id,
p_period_end_date => cons_entity_wf_info.cal_period_end_date,
x_errbuf => l_parameter_list.errbuf,
x_retcode => l_parameter_list.retcode);
SELECT gcia.entry_id,
gcia.stat_entry_id
INTO l_entry_id,
l_stat_entry_id
FROM gcs_cons_impact_analyses gcia,
gcs_entry_headers geh,
gcs_data_sub_dtls gdsd
WHERE gcia.run_name = cons_entity_wf_info.prior_run_identifier
AND gcia.consolidation_entity_id = cons_entity_wf_info.consolidation_entity
AND gcia.child_entity_id = oper_entity_wf_info.operating_entity
AND gdsd.load_id = gcia.load_id
AND gdsd.most_recent_flag = 'Y'
AND gcia.message_name IN ('GCS_PRISTINE_DATA_INC_LOAD','GCS_PRISTINE_DATA_FULL_LOAD')
AND gcia.entry_id = geh.entry_id
AND geh.disabled_flag = 'N';
gcs_interco_dynamic_pkg.insert_interco_trx( p_entry_id => l_entry_id,
p_stat_entry_id => NVL(l_stat_entry_id, -1),
p_hierarchy_id => l_parameter_list.hierarchy_id,
p_period_end_date => cons_entity_wf_info.cal_period_end_date,
x_errbuf => l_parameter_list.errbuf,
x_retcode => l_parameter_list.retcode);
gcs_cons_eng_run_dtls_pkg.update_entry_headers_async( p_run_detail_id => oper_entity_wf_info.run_detail_id,
p_entry_id => l_entry_id,
p_stat_entry_id => l_stat_entry_id,
p_request_error_code => 'COMPLETED',
p_bp_request_error_code => 'COMPLETED');
SELECT DECODE(gcerd.entry_id, NULL, 'COMPLETE:FULL', -1, 'COMPLETE:FULL', 'COMPLETE:INCREMENTAL'),
gcerd.entry_id,
gcerd.stat_entry_id,
gcerd.request_error_code
INTO result,
l_entry_id,
l_stat_entry_id,
l_request_error_code
FROM gcs_cons_eng_run_dtls gcerd,
gcs_cons_eng_runs gcer
WHERE gcer.run_entity_id = cons_entity_wf_info.consolidation_entity
AND gcerd.category_code = 'DATAPREPARATION'
AND gcer.run_name = gcerd.run_name
AND gcerd.request_error_code IN ('COMPLETED', 'WARNING')
AND gcer.run_name = cons_entity_wf_info.prior_run_identifier
AND gcer.balance_type_code = cons_entity_wf_info.balance_type_code
AND gcer.run_entity_id = gcerd.consolidation_entity_id
AND gcerd.child_entity_id = oper_entity_wf_info.operating_entity;
SELECT 'COMPLETE:FULL'
INTO result
FROM gcs_cons_impact_analyses
WHERE run_name = cons_entity_wf_info.prior_run_identifier
AND consolidation_entity_id = cons_entity_wf_info.consolidation_entity
AND child_entity_id = oper_entity_wf_info.operating_entity
--Bugfix 4665921: Added GCS_VS_MAP_UPDATED check to support impact for value set assignments
AND message_name IN ('GCS_VS_MAP_UPDATED', 'GCS_PRISTINE_DATA_INC_LOAD', 'GCS_PRISTINE_DATA_FULL_LOAD')
AND ROWNUM < 2;
gcs_cons_eng_run_dtls_pkg.update_entry_headers_async( p_run_detail_id => oper_entity_wf_info.run_detail_id,
p_entry_id => l_entry_id,
p_stat_entry_id => l_stat_entry_id,
p_request_error_code => l_request_error_code,
p_bp_request_error_code => l_request_error_code
);
SELECT COUNT(entry_id)
INTO l_entry_count
FROM gcs_cons_eng_run_dtls
WHERE run_name = l_run_identifier
AND NVL(entry_id,-1) > 0
AND consolidation_entity_id = l_entity_id
AND category_code <> 'AGGREGATION';
GCS_CONS_ENG_RUN_DTLS_PKG.insert_row(p_run_name => cons_entity_wf_info.run_identifier,
p_consolidation_entity_id => cons_entity_wf_info.consolidation_entity,
p_category_code => 'AGGREGATION',
p_child_entity_id => cons_entity_wf_info.consolidation_entity,
p_cons_relationship_id => -1,
p_request_error_code => 'NOT_APPLICABLE',
p_run_detail_id => l_run_detail_id);
SELECT cons_relationship_id
INTO l_cons_relationship_id
FROM gcs_cons_relationships
WHERE child_entity_id = cons_entity_wf_info.consolidation_entity
AND dominant_parent_flag = 'Y'
AND hierarchy_id = cons_entity_wf_info.consolidation_hierarchy
AND cons_entity_wf_info.cal_period_end_date BETWEEN start_date AND NVL(end_date, cons_entity_wf_info.cal_period_end_date)
AND ROWNUM < 2;
GCS_CONS_ENG_RUN_DTLS_PKG.insert_row(p_run_name => cons_entity_wf_info.run_identifier,
p_consolidation_entity_id => cons_entity_wf_info.consolidation_entity,
p_category_code => 'AGGREGATION',
p_child_entity_id => cons_entity_wf_info.consolidation_entity,
p_cons_relationship_id => l_cons_relationship_id,
p_request_error_code => 'IN_PROGRESS',
p_run_detail_id => l_run_detail_id);
SELECT DECODE(COUNT(*), 0, 'N', 'Y')
INTO l_parameter_list.stat_required
FROM gcs_cons_eng_run_dtls
WHERE run_name = cons_entity_wf_info.run_identifier
AND consolidation_entity_id = cons_entity_wf_info.consolidation_entity
AND NVL(stat_entry_id,0) > 0;
PROCEDURE delete_flattened_relns(cons_entity_wf_info IN gcs_cons_eng_utility_pkg.r_cons_entity_wf_info)
IS PRAGMA AUTONOMOUS_TRANSACTION;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.DELETE_FLATTENED_RELNS', '<>');
DELETE FROM gcs_flattened_relns
WHERE run_name = cons_entity_wf_info.run_identifier;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.DELETE_FLATTENED_RELNS', '<>');
SELECT 'Y'
INTO l_warning_exists
FROM gcs_cons_eng_run_dtls
WHERE run_name = cons_entity_wf_info.run_identifier
AND consolidation_entity_id = cons_entity_wf_info.consolidation_entity
AND child_entity_id IS NULL
AND request_error_code NOT IN ('COMPLETED', 'NOT_APPLICABLE')
--STK: Added Condition for Excluding Category Code of Aggregation 5/6/03
AND category_code <> 'AGGREGATION'
AND ROWNUM < 2;
gcs_cons_eng_runs_pkg.update_status(
p_run_name => cons_entity_wf_info.run_identifier,
p_most_recent_flag => 'Y',
p_status_code => l_status_code,
p_run_entity_id => cons_entity_wf_info.consolidation_entity,
p_end_time => sysdate);
SELECT top_entity_id
INTO l_top_entity_id
FROM gcs_hierarchies_b
WHERE hierarchy_id = cons_entity_wf_info.consolidation_hierarchy;
gcs_cons_eng_run_dtls_pkg.update_category_status(
p_run_name => cons_entity_wf_info.run_identifier,
p_consolidation_entity_id => cons_entity_wf_info.consolidation_entity,
p_category_code => 'AGGREGATION',
p_status => l_status_code);
SELECT parent_entity_id
INTO l_top_entity_id
FROM gcs_cons_eng_runs
WHERE run_name = cons_entity_wf_info.run_identifier
AND run_entity_id = cons_entity_wf_info.consolidation_entity;
delete_flattened_relns(cons_entity_wf_info);
SELECT entry_id,
stat_entry_id
INTO l_entry_id,
l_stat_entry_id
FROM gcs_cons_eng_run_dtls
WHERE run_detail_id = cons_entity_wf_info.run_detail_id;
SELECT gcerd.run_detail_id
INTO l_run_detail_id
FROM gcs_cons_eng_run_dtls gcerd
WHERE gcerd.category_code = 'DATAPREPARATION'
AND gcerd.child_entity_id = cons_entity_wf_info.consolidation_entity
AND gcerd.run_name = cons_entity_wf_info.run_identifier;
gcs_cons_eng_run_dtls_pkg.update_entry_headers_async( p_run_detail_id => l_run_detail_id,
p_entry_id => l_entry_id,
p_stat_entry_id => l_stat_entry_id,
p_request_error_code => l_status_code,
p_bp_request_error_code => l_status_code);
INSERT INTO gcs_flattened_relns
(run_name,
parent_entity_id,
child_entity_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
object_version_number,
--Bugfix 5091093: Added consolidation type code
consolidation_type_code
)
SELECT cons_entity_wf_info.run_identifier,
cons_entity_id,
gcr.child_entity_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
1,
--Bugfix 5091093: Added consolidation type code
gtb.consolidation_type_code
FROM gcs_cons_relationships gcr,
gcs_treatments_b gtb
WHERE gtb.treatment_id(+) = gcr.treatment_id
START WITH gcr.parent_entity_id = cons_entity_id
AND gcr.hierarchy_id = cons_entity_wf_info.consolidation_hierarchy
--Bugfix 5192720: Added dominant parent flag join condition
AND gcr.dominant_parent_flag = 'Y'
AND cons_entity_wf_info.cal_period_end_date
BETWEEN gcr.start_date AND NVL(gcr.end_date, cons_entity_wf_info.cal_period_end_date)
CONNECT BY PRIOR gcr.child_entity_id = gcr.parent_entity_id
AND gcr.hierarchy_id = cons_entity_wf_info.consolidation_hierarchy
AND gcr.dominant_parent_flag = 'Y'
AND cons_entity_wf_info.cal_period_end_date
BETWEEN gcr.start_date AND NVL(gcr.end_date, cons_entity_wf_info.cal_period_end_date);
SELECT gcerd.child_entity_id,
gcerd.cons_relationship_id,
gcerd.run_detail_id,
fev.entity_name,
DECODE(geca_parent.currency_code, geca_child.currency_code, 'N', 'Y') translation_required
FROM gcs_cons_eng_run_dtls gcerd,
fem_entities_attr fea,
fem_entities_vl fev,
gcs_entity_cons_attrs geca_parent,
gcs_entity_cons_attrs geca_child,
gcs_cons_eng_runs gcer
WHERE gcerd.run_name = p_run_name
AND gcerd.consolidation_entity_id = p_cons_entity_id
AND gcerd.entry_id IS NULL
AND category_code = 'DATAPREPARATION'
AND gcerd.child_entity_id = fev.entity_id
AND gcerd.child_entity_id = fea.entity_id
AND fea.attribute_id = g_entity_type_attr
AND fea.version_id = g_entity_type_version
AND fea.dim_attribute_varchar_member IN ('C')
AND geca_parent.entity_id = p_cons_entity_id
AND geca_child.entity_id = fev.entity_id
AND geca_parent.hierarchy_id = geca_child.hierarchy_id
AND gcer.run_name = gcerd.run_name
AND gcer.run_entity_id = gcerd.consolidation_entity_id
AND gcer.hierarchy_id = geca_child.hierarchy_id;
SELECT to_char(sysdate,'DD-MM-RR HH:MI:SS')
INTO l_child_key
FROM dual;
GCS_CONS_ENG_RUNS_PKG.insert_row
(
p_run_name => cons_entity_wf_info.run_identifier,
p_hierarchy_id => cons_entity_wf_info.consolidation_hierarchy,
p_process_method_code => cons_entity_wf_info.process_method,
p_run_entity_id => v_cons_entity.child_entity_id,
p_cal_period_id => cons_entity_wf_info.cal_period_id,
p_balance_type_code => cons_entity_wf_info.balance_type_code,
p_parent_entity_id => cons_entity_wf_info.consolidation_entity,
p_item_key => l_child_key,
p_request_id => cons_entity_wf_info.request_id
);
SELECT cons_entity_wf_info.run_identifier,
cons_entity_wf_info.consolidation_entity,
DECODE(category_code, 'DATAPREPARATION', 'IN_PROGRESS', 'NOT_STARTED') request_error_code,
category_code
FROM gcs_categories_b
WHERE category_number > 0
AND enabled_flag = 'Y';
gcs_cons_eng_run_dtls_pkg.insert_row(
p_run_name => cons_entity_wf_info.run_identifier,
p_consolidation_entity_id => cons_entity_wf_info.consolidation_entity,
p_category_code => v_categories_to_process.category_code,
p_request_error_code => v_categories_to_process.request_error_code,
p_run_detail_id => l_run_detail_id);
SELECT run_name,
status_code,
locked_flag,
impacted_flag
INTO l_prior_run_name,
l_status_code,
l_locked_flag,
l_impacted_flag
FROM gcs_cons_eng_runs
WHERE run_entity_id = cons_entity_wf_info.consolidation_entity
-- Bugfix 3659810 : Added condition for hierarchy_id
AND hierarchy_id = cons_entity_wf_info.consolidation_hierarchy
AND most_recent_flag = 'X'
AND cal_period_id = cons_entity_wf_info.cal_period_id;
gcs_cons_eng_runs_pkg.update_status ( p_run_name => l_prior_run_name,
p_most_recent_flag => 'N',
p_status_code => NULL,
p_run_entity_id => cons_entity_wf_info.consolidation_entity);
gcs_cons_eng_runs_pkg.update_status ( p_run_name => cons_entity_wf_info.run_identifier,
p_most_recent_flag => 'Y',
p_status_code => 'IN_PROGRESS',
p_run_entity_id => cons_entity_wf_info.consolidation_entity);
gcs_cons_eng_runs_pkg.update_status ( p_run_name => cons_entity_wf_info.run_identifier,
p_most_recent_flag => 'Y',
p_status_code => 'IN_PROGRESS',
p_run_entity_id => cons_entity_wf_info.consolidation_entity);
PROCEDURE update_run_information( cons_entity_wf_info IN gcs_cons_eng_utility_pkg.r_cons_entity_wf_info,
p_run_detail_id IN OUT NOCOPY NUMBER)
IS PRAGMA AUTONOMOUS_TRANSACTION;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.UPDATE_RUN_INFORMATION.begin', '<>');
UPDATE gcs_cons_eng_runs
SET most_recent_flag = 'Y'
WHERE run_name = cons_entity_wf_info.prior_run_identifier
AND run_entity_id = cons_entity_wf_info.consolidation_entity
RETURNING status_code, impacted_flag, locked_flag INTO l_status_code, l_impacted_flag, l_locked_flag;
UPDATE gcs_cons_eng_runs
SET most_recent_flag = 'N',
associated_run_name = cons_entity_wf_info.prior_run_identifier,
status_code = l_status_code,
impacted_flag = l_impacted_flag,
locked_flag = l_locked_flag,
end_time = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_date = sysdate,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE run_name = cons_entity_wf_info.run_identifier
AND run_entity_id = cons_entity_wf_info.consolidation_entity;
UPDATE gcs_cons_eng_runs gcer
SET gcer.most_recent_flag = 'Y'
WHERE gcer.most_recent_flag = 'X'
AND gcer.hierarchy_id = cons_entity_wf_info.consolidation_hierarchy
AND gcer.cal_period_id = cons_entity_wf_info.cal_period_id
AND gcer.balance_type_code = cons_entity_wf_info.balance_type_code
AND EXISTS ( SELECT 'X'
FROM gcs_cons_relationships gcr
WHERE gcr.child_entity_id = gcer.run_entity_id
START WITH gcr.parent_entity_id = cons_entity_wf_info.consolidation_entity
AND gcr.hierarchy_id = cons_entity_wf_info.consolidation_hierarchy
AND cons_entity_wf_info.cal_period_end_date
BETWEEN gcr.start_date AND NVL(gcr.end_date,
cons_entity_wf_info.cal_period_end_date)
CONNECT BY PRIOR gcr.child_entity_id = gcr.parent_entity_id
AND gcr.hierarchy_id = cons_entity_wf_info.consolidation_hierarchy
AND gcr.dominant_parent_flag = 'Y'
AND cons_entity_wf_info.cal_period_end_date
BETWEEN gcr.start_date AND NVL(gcr.end_date,
cons_entity_wf_info.cal_period_end_date));
UPDATE gcs_cons_eng_run_dtls gcerd
SET (entry_id,
stat_entry_id,
request_error_code,
bp_request_error_code
) =
(SELECT gcerd_inner.entry_id,
gcerd_inner.stat_entry_id,
gcerd_inner.request_error_code,
gcerd_inner.bp_request_error_code
FROM gcs_cons_eng_run_dtls gcerd_inner
WHERE gcerd_inner.run_name = cons_entity_wf_info.prior_run_identifier
AND gcerd_inner.consolidation_entity_id = cons_entity_wf_info.consolidation_entity
AND gcerd_inner.category_code = 'AGGREGATION'
AND gcerd_inner.child_entity_id = cons_entity_wf_info.consolidation_entity)
WHERE gcerd.run_name = cons_entity_wf_info.run_identifier
AND gcerd.category_code = 'DATAPREPARATION'
AND gcerd.child_entity_id = cons_entity_wf_info.consolidation_entity
RETURN run_detail_id INTO p_run_detail_id;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.UPDATE_RUN_INFORMATION.end', '<>');
update_run_information(cons_entity_wf_info,
l_run_detail_id);