[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_entry_headers(p_run_name IN VARCHAR2,
p_entry_id IN NUMBER)
IS PRAGMA AUTONOMOUS_TRANSACTION;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.UPDATE_ENTRY_HEADERS.begin', '<>');
UPDATE gcs_entry_headers
SET processed_run_name = p_run_name,
disabled_flag = 'Y',
last_update_date = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID
WHERE entry_id = p_entry_id;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.UPDATE_ENTRY_HEADERS.end', '<>');
END update_entry_headers;
gcs_cons_eng_run_dtls_pkg.update_entry_headers(
p_run_detail_id => p_parameter_list.run_detail_id,
p_entry_id => p_parameter_list.xlate_entry_id,
p_request_error_code => NVL(p_parameter_list.errbuf, 'COMPLETED'),
p_bp_request_error_code => NVL(p_parameter_list.errbuf, 'COMPLETED'));
gcs_cons_eng_run_dtls_pkg.update_entry_headers(
p_run_detail_id => p_parameter_list.run_detail_id,
p_request_error_code => NVL(p_parameter_list.errbuf, 'COMPLETED'),
p_bp_request_error_code => NVL(p_parameter_list.errbuf, 'COMPLETED'));
gcs_dyn_fem_posting_pkg.gcs_fem_delete(
errbuf => p_parameter_list.errbuf,
retcode => p_parameter_list.retcode,
p_cal_period_id => p_parameter_list.cal_period_id,
p_hierarchy_id => p_parameter_list.hierarchy_id,
p_balance_type_code => p_parameter_list.balance_type_code,
p_entity_type => p_parameter_list.undo_entity_type,
p_entity_id => p_parameter_list.undo_entity_id,
p_hier_dataset_code => p_parameter_list.hierarchy_dataset_code);
SELECT DECODE(COUNT(*), 0, 'N', 'Y')
INTO l_rows_to_process
FROM gcs_cons_eng_run_dtls
WHERE consolidation_entity_id = cons_entity_wf_info.consolidation_entity
AND run_name = cons_entity_wf_info.run_identifier
AND child_entity_id = oper_entity_wf_info.operating_entity
AND category_code = l_parameter_list.category_code;
SELECT DECODE(COUNT(*), 0, 'N', 'Y')
INTO l_rows_to_process
FROM gcs_cons_eng_run_dtls
WHERE consolidation_entity_id = cons_entity_wf_info.consolidation_entity
AND run_name = cons_entity_wf_info.run_identifier
AND child_entity_id IS NOT NULL
AND category_code = l_parameter_list.category_code;
SELECT 'N'
INTO l_change_in_data
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
AND rownum < 2;
SELECT 'N'
INTO l_change_in_data
FROM gcs_cons_eng_runs
WHERE run_name = cons_entity_wf_info.prior_run_identifier
AND run_entity_id = cons_entity_wf_info.consolidation_entity
AND impacted_flag = 'Y';
SELECT geh.entry_id,
geh.currency_code,
geh.process_code,
geh.period_init_entry_flag,
gcr.child_entity_id,
DECODE(geh.suspense_exceeded_flag, 'Y',
'WARNING',
'COMPLETED') request_status
FROM gcs_entry_headers geh,
gcs_ad_transactions gad,
gcs_cons_relationships gcr
WHERE geh.entry_id = gad.assoc_entry_id
AND gcr.cons_relationship_id = NVL(gad.post_cons_relationship_id, gad.pre_cons_relationship_id)
AND geh.category_code = 'ACQ_DISP'
AND geh.hierarchy_id = p_hierarchy_id
AND geh.entity_id = p_entity_id
AND ( geh.approval_status_code = 'APPROVED' or geh.approval_status_code is NULL)
AND geh.balance_type_code = p_balance_type_code
AND p_cal_period_id BETWEEN geh.start_cal_period_id
AND nvl(disabled_cal_period_id, NVL(geh.end_cal_period_id, p_cal_period_id))
AND 'N' = DECODE(geh.start_cal_period_id, geh.end_cal_period_id, geh.disabled_flag, 'N');
SELECT geh.entry_id,
geh.currency_code,
geh.process_code,
geh.period_init_entry_flag
FROM gcs_entry_headers geh
WHERE 'MANUAL' = DECODE(geh.period_init_entry_flag, 'Y', 'MANUAL', geh.entry_type_code)
-- Bugfix 3718501 : Modified the Join Condition to Extract Automatically Generated A&D Entries
-- AND geh.entry_type_code = 'MANUAL' -- Bugfix 3704972 : Extract Only Manual Adjustments
-- Bugfix 3827087 : Modifed the Join Condition to Extract Period Initialization Entries as well
AND geh.category_code = p_category_code
AND geh.hierarchy_id = p_hierarchy_id
AND geh.entity_id = p_entity_id
AND geh.balance_type_code = p_balance_type_code
AND geh.processed_run_name IS NULL
AND ( geh.approval_status_code = 'APPROVED' or geh.approval_status_code is NULL)
AND p_cal_period_id BETWEEN geh.start_cal_period_id
AND NVL(disabled_cal_period_id - 1, NVL(geh.end_cal_period_id, p_cal_period_id))
AND 'N' = DECODE(geh.start_cal_period_id, geh.end_cal_period_id, geh.disabled_flag, 'N');
SELECT fea.dim_attribute_numeric_member
INTO l_entity_id
FROM fem_entities_attr fea
WHERE fea.entity_id = cons_entity_wf_info.consolidation_entity
AND fea.attribute_id = gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ELIMINATION_ENTITY').attribute_id
AND fea.version_id = gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ELIMINATION_ENTITY').version_id;
SELECT fea.dim_attribute_numeric_member
INTO l_entity_id
FROM fem_entities_attr fea
WHERE fea.entity_id = cons_entity_wf_info.consolidation_entity
AND fea.attribute_id = gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-OPERATING_ENTITY').attribute_id
AND fea.version_id = gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-OPERATING_ENTITY').version_id;
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 => 'ACQ_DISP',
p_child_entity_id => v_acqdisp_headers.child_entity_id,
p_entry_id => v_acqdisp_headers.entry_id,
p_stat_entry_id => NULL,
p_cons_relationship_id => -1,
p_request_error_code => v_acqdisp_headers.request_status,
p_bp_request_error_code => v_acqdisp_headers.request_status,
p_run_detail_id => l_run_detail_id);
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 => l_category_code,
p_child_entity_id => l_entity_id,
p_entry_id => l_entry_id,
p_stat_entry_id => l_stat_entry_id,
p_cons_relationship_id => -1,
p_request_error_code => 'COMPLETED',
p_bp_request_error_code => 'COMPLETED',
p_run_detail_id => l_run_detail_id);
GCS_INTERCO_DYNAMIC_PKG.Insert_Interco_Trx(p_entry_id => NVL(l_entry_id, -1),
p_stat_entry_id => NVL(l_stat_entry_id, -1),
p_hierarchy_id => cons_entity_wf_info.consolidation_hierarchy,
-- Bugfix 3800083 : Added Period End Date as a parameter
p_period_end_date => cons_entity_wf_info.cal_period_end_date,
x_errbuf => x_errbuf,
x_retcode => x_retcode);
update_entry_headers(cons_entity_wf_info.run_identifier,
v_adjustment_headers.entry_id);
SELECT DECODE(count(run_detail_id), 0, 'N', 'Y')
INTO l_translation_req_flag
FROM gcs_cons_eng_run_dtls
WHERE run_name = cons_entity_wf_info.run_identifier
AND child_entity_id = oper_entity_wf_info.operating_entity
AND consolidation_entity_id = cons_entity_wf_info.consolidation_entity
AND entry_id IS NOT NULL;
SELECT cons_relationship_id
INTO l_parameter_list.cons_relationship_id
FROM gcs_cons_eng_run_dtls
WHERE run_detail_id = l_parameter_list.run_detail_id;
SELECT gcerd.run_detail_id,
gcs_entry_headers_s.nextval
INTO l_parameter_list.run_detail_id,
l_parameter_list.xlate_entry_id
FROM gcs_cons_eng_run_dtls gcerd
WHERE gcerd.run_name = cons_entity_wf_info.run_identifier
AND gcerd.category_code = 'TRANSLATION'
AND gcerd.child_entity_id = l_child_entity_id;
gcs_cons_eng_run_dtls_pkg.update_entry_headers_async
( p_run_detail_id => l_parameter_list.run_detail_id,
p_request_error_code => 'NOT_APPLICABLE',
p_bp_request_error_code => 'NOT_APPLICABLE');
PROCEDURE update_process_status( itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN varchar2,
result IN OUT NOCOPY varchar2)
IS
l_category_code VARCHAR2(30);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.UPDATE_PROCESS_STATUS.begin', '<>');
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_PROCESS_STATUS', 'Category Code : ' || l_category_code);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_PROCESS_STATUS', 'Item Type : ' || itemtype);
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 => l_category_code,
p_status => l_status_code);
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, g_api || '.UPDATE_PROCESS_STATUS', 'Actual Category Code : ' || l_category_code);
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 => l_category_code,
p_status => l_status_code);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.UPDATE_PROCESS_STATUS', '<>');
END update_process_status;
SELECT gcr.parent_entity_id,
gcr.child_entity_id,
gcr.cons_relationship_id,
gerr.rule_id,
--Bugfix 5017120: Removing gdc.dataset_code from this query to support additional data types
-1 elimination_entity_id,
geca.currency_code,
gcr.ownership_percent / 100 ownership_percent,
gcatb.target_entity_code
FROM gcs_cons_relationships gcr,
gcs_entity_cons_attrs geca,
gcs_elim_rule_rels gerr,
gcs_dataset_codes gdc,
gcs_elim_rules_b grb,
gcs_categories_b gcatb
WHERE gcr.hierarchy_id = p_hierarchy_id
AND gcr.dominant_parent_flag = 'Y'
AND gcr.child_entity_id = p_child_entity_id
AND gcatb.category_code = p_category_code
AND grb.transaction_type_code = gcatb.category_code
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 = geca.entity_id
AND gcr.hierarchy_id = geca.hierarchy_id
AND gcr.treatment_id = gerr.treatment_id
AND gcr.hierarchy_id = gdc.hierarchy_id
AND gdc.balance_type_code = p_balance_type_code
AND gerr.rule_id = grb.rule_id
AND grb.enabled_flag = 'Y';
SELECT gcr.parent_entity_id,
gcr.child_entity_id,
gcr.cons_relationship_id,
gerr.rule_id,
--Bugfix 5017120: Removing gcs.dataset_code from this query to support additional data types
fea.dim_attribute_numeric_member elimination_entity_id,
geca.currency_code,
gcr.ownership_percent / 100 ownership_percent,
gcatb.target_entity_code
FROM gcs_cons_relationships gcr,
gcs_entity_cons_attrs geca,
gcs_elim_rule_rels gerr,
gcs_dataset_codes gdc,
fem_entities_attr fea,
gcs_elim_rules_b grb,
gcs_categories_b gcatb
WHERE gcr.hierarchy_id = p_hierarchy_id
AND gcr.parent_entity_id = p_cons_entity_id
AND gcr.dominant_parent_flag = 'Y'
AND gcatb.category_code = p_category_code
AND grb.transaction_type_code = gcatb.category_code
AND p_cal_period_end_date BETWEEN gcr.start_date AND NVL(gcr.end_date, p_cal_period_end_date)
AND gcr.parent_entity_id = geca.entity_id
AND gcr.hierarchy_id = geca.hierarchy_id
AND gcr.treatment_id = gerr.treatment_id
AND gcr.hierarchy_id = gdc.hierarchy_id
AND gdc.balance_type_code = p_balance_type_code
AND fea.entity_id = gcr.parent_entity_id
AND gerr.rule_id = grb.rule_id
AND grb.enabled_flag = 'Y'
AND fea.attribute_id = l_elim_entity_attr
AND fea.version_id = l_elim_entity_version;
SELECT gcerd.child_entity_id,
gcerd.rule_id,
geh.entry_id
FROM gcs_cons_eng_run_dtls gcerd,
gcs_entry_headers geh,
gcs_cons_eng_runs gcer
WHERE geh.hierarchy_id = p_hierarchy_id
AND geh.entity_id = p_target_entity_id
AND geh.category_code = p_category_code
AND geh.entry_type_code = 'MULTIPLE_PARENTS'
AND geh.start_cal_period_id = p_cal_period_id
AND geh.end_cal_period_id = p_cal_period_id
AND geh.balance_type_code = p_balance_type_code
AND geh.assoc_entry_id = gcerd.entry_id
AND gcerd.category_code = p_category_code
AND gcerd.consolidation_entity_id = gcer.run_entity_id
AND gcerd.run_name = gcer.run_name
AND gcer.hierarchy_id = p_hierarchy_id
AND gcer.cal_period_id = p_cal_period_id
AND gcer.most_recent_flag = 'Y';
SELECT apply_elim_rules_flag,
apply_cons_rules_flag
INTO l_apply_elim_rules_flag,
l_apply_cons_rules_flag
FROM gcs_data_type_codes_b
WHERE data_type_code = cons_entity_wf_info.balance_type_code;
SELECT currency_code
INTO l_currency_code
FROM gcs_entity_cons_attrs
WHERE hierarchy_id = cons_entity_wf_info.consolidation_hierarchy
AND entity_id = l_entity_id;
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 => l_category_code,
p_child_entity_id => v_oper_entity_rules.child_entity_id,
p_rule_id => v_oper_entity_rules.rule_id,
p_cons_relationship_id => v_oper_entity_rules.cons_relationship_id,
p_run_detail_id => l_run_detail_id);
SELECT dim_attribute_numeric_member
INTO l_parameter_list.rp_parameters.parentEntity
FROM fem_entities_attr
WHERE entity_id = v_cons_entity_rules.parent_entity_id
AND attribute_id = l_oper_entity_attr
AND version_id = l_oper_entity_version;
SELECT dim_attribute_numeric_member
INTO l_parameter_list.rp_parameters.parentEntity
FROM fem_entities_attr
WHERE entity_id = v_cons_entity_rules.parent_entity_id
AND attribute_id = l_oper_entity_attr
AND version_id = l_oper_entity_version;
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 => l_category_code,
p_child_entity_id => v_cons_entity_rules.child_entity_id,
p_rule_id => v_cons_entity_rules.rule_id,
p_cons_relationship_id => v_cons_entity_rules.cons_relationship_id,
p_run_detail_id => l_run_detail_id);
SELECT dim_attribute_numeric_member
INTO l_tgt_entity_id
FROM fem_entities_attr
WHERE entity_id = cons_entity_wf_info.consolidation_entity
AND attribute_id = l_elim_entity_attr
AND version_id = l_elim_entity_version;
SELECT dim_attribute_numeric_member
INTO l_tgt_entity_id
FROM fem_entities_attr
WHERE entity_id = cons_entity_wf_info.consolidation_entity
AND attribute_id = l_oper_entity_attr
AND version_id = l_oper_entity_version;
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 => l_category_code,
p_child_entity_id => v_mp_entries.child_entity_id,
p_rule_id => v_mp_entries.rule_id,
p_request_error_code => 'COMPLETED',
p_bp_request_error_code => 'COMPLETED',
p_entry_id => v_mp_entries.entry_id,
p_run_detail_id => l_run_detail_id);
SELECT 'Y'
INTO l_init_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 child_entity_id = l_entity_id
AND category_code NOT IN ('DATAPREPARATION')
AND ROWNUM < 2;
SELECT dim_attribute_numeric_member
INTO l_entity_id
FROM fem_entities_attr
WHERE entity_id = cons_entity_wf_info.consolidation_entity
AND attribute_id = gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ELIMINATION_ENTITY').attribute_id
AND version_id = gcs_utility_pkg.g_dimension_attr_info('ENTITY_ID-ELIMINATION_ENTITY').version_id;
SELECT 'Y'
INTO l_init_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 child_entity_id IS NOT NULL
AND category_code NOT IN ('AGGREGATION')
AND ROWNUM < 2;
SELECT cons_relationship_id
INTO l_parameter_list.cons_relationship_id
FROM gcs_cons_relationships
WHERE hierarchy_id = cons_entity_wf_info.consolidation_hierarchy
AND dominant_parent_flag = 'Y'
AND child_entity_id = l_entity_id
AND cons_entity_wf_info.cal_period_end_date BETWEEN start_date AND NVL(end_date, cons_entity_wf_info.cal_period_end_date);