[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT gdsd.cal_period_id, fcpb.calendar_id, fcpb.dimension_group_id
INTO l_src_cal_period_id, l_src_calendar_id, l_src_dimension_grp_id
FROM gcs_data_sub_dtls gdsd, fem_cal_periods_b fcpb
WHERE gdsd.entity_id = p_entity_id
AND gdsd.balance_type_code = p_balance_type_code
AND gdsd.cal_period_id = fcpb.cal_period_id
AND ROWNUM < 2;
SELECT fcpb.calendar_id, fcpb.dimension_group_id
INTO l_tgt_calendar_id, l_tgt_dimension_grp_id
FROM fem_cal_periods_b fcpb
WHERE fcpb.cal_period_id = p_target_cal_period_id;
'INSERT INTO gcs_cal_period_maps_gt (source_cal_period_id, target_cal_period_id)
SELECT fdl.cal_period_id, ' ||
p_target_cal_period_id || '
FROM fem_data_locations fdl, ' || '
fem_ledgers_attr fla, ' || '
fem_cal_periods_attr fcpa_number, ' || '
fem_cal_periods_attr fcpa_year, ' || '
gcs_cal_period_map_dtls gcpmd, ' || '
gcs_cal_period_maps gcpm, ' || '
fem_cal_periods_b fcpb_src, ' || '
fem_cal_periods_b fcpb_tgt ' || '
WHERE fdl.ledger_id = ' ||
p_source_ledger_id || '
AND fdl.table_name = ''FEM_BALANCES'' ' || '
AND fdl.load_status = ''COMPLETE''
AND fdl.balance_type_code = ''' ||
p_balance_type_code || '''
AND fdl.source_system_code = fla.DIM_ATTRIBUTE_NUMERIC_MEMBER ' || '
AND fla.ledger_id = fdl.ledger_id' || '
AND fla.attribute_id = ' ||
gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-SOURCE_SYSTEM_CODE')
.attribute_id || '
AND fla.version_id = ' ||
gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-SOURCE_SYSTEM_CODE')
.version_id || '
AND gcpmd.cal_period_map_id = gcpm.cal_period_map_id
AND fcpb_src.cal_period_id = fdl.cal_period_id
AND gcpm.source_calendar_id = fcpb_src.calendar_id
AND gcpm.source_dimension_group_id = fcpb_src.dimension_group_id
AND fcpb_tgt.cal_period_id = ' ||
p_target_cal_period_id || '
AND gcpm.target_calendar_id = fcpb_tgt.calendar_id
AND gcpm.target_dimension_group_id = fcpb_tgt.dimension_group_id
AND fdl.cal_period_id = fcpa_number.cal_period_id
AND fdl.cal_period_id = fcpa_year.cal_period_id
AND fcpa_number.attribute_id = ' ||
gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM')
.attribute_id || '
AND fcpa_year.attribute_id = ' ||
gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR')
.attribute_id || '
AND fcpa_number.version_id = ' ||
gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM')
.version_id || '
AND fcpa_year.version_id = ' ||
gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR')
.version_id || '
AND fcpa_number.number_assign_value = gcpmd.source_period_number
AND gcpmd.target_period_number = ' ||
p_cal_period_record.cal_period_number || '
AND fcpa_year.number_assign_value = DECODE (gcpmd.target_relative_year_code,
''CURRENT'', ' ||
p_cal_period_record.cal_period_year || ',
''PRIOR'', ' ||
p_cal_period_record.cal_period_year ||
' + 1,
''FOLLOWING'', ' ||
p_cal_period_record.cal_period_year || ' - 1 )');
INSERT INTO gcs_cal_period_maps_gt
(source_cal_period_id, target_cal_period_id)
SELECT fdl.cal_period_id, p_target_cal_period_id
FROM fem_data_locations fdl,
fem_ledgers_attr fla,
fem_cal_periods_attr fcpa_number,
fem_cal_periods_attr fcpa_year,
gcs_cal_period_map_dtls gcpmd,
gcs_cal_period_maps gcpm,
fem_cal_periods_b fcpb_src,
fem_cal_periods_b fcpb_tgt
WHERE fdl.ledger_id = p_source_ledger_id
AND fdl.table_name = 'FEM_BALANCES'
AND fdl.load_status = 'COMPLETE'
AND fdl.balance_type_code = p_balance_type_code
AND fdl.source_system_code = fla.DIM_ATTRIBUTE_NUMERIC_MEMBER
AND fla.ledger_id = fdl.ledger_id
AND fla.attribute_id =
gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-SOURCE_SYSTEM_CODE')
.attribute_id
AND fla.version_id =
gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-SOURCE_SYSTEM_CODE')
.version_id
AND gcpmd.cal_period_map_id = gcpm.cal_period_map_id
AND fcpb_src.cal_period_id = fdl.cal_period_id
AND gcpm.source_calendar_id = fcpb_src.calendar_id
AND gcpm.source_dimension_group_id = fcpb_src.dimension_group_id
AND fcpb_tgt.cal_period_id = p_target_cal_period_id
AND gcpm.target_calendar_id = fcpb_tgt.calendar_id
AND gcpm.target_dimension_group_id = fcpb_tgt.dimension_group_id
AND fdl.cal_period_id = fcpa_number.cal_period_id
AND fdl.cal_period_id = fcpa_year.cal_period_id
AND fcpa_number.attribute_id =
gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM')
.attribute_id
AND fcpa_year.attribute_id =
gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR')
.attribute_id
AND fcpa_number.version_id =
gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM')
.version_id
AND fcpa_year.version_id =
gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR')
.version_id
AND fcpa_number.number_assign_value = gcpmd.source_period_number
AND gcpmd.target_period_number =
p_cal_period_record.cal_period_number
AND fcpa_year.number_assign_value =
DECODE(gcpmd.target_relative_year_code,
'CURRENT',
p_cal_period_record.cal_period_year,
'PRIOR',
p_cal_period_record.cal_period_year + 1,
'FOLLOWING',
p_cal_period_record.cal_period_year - 1);
SELECT count(*) INTO l_cnt FROM gcs_cal_period_maps_gt;
'SELECT decode(COUNT (gcpmd.cal_period_map_id), 0, ''Y'', ''N'')
INTO p_year_end_values_match
FROM gcs_cal_period_map_dtls gcpmd,
gcs_cal_period_maps_gt gcpmg,
gcs_cal_period_maps gcpm,
fem_cal_periods_b fcpb_src,
fem_cal_periods_b fcpb_tgt
WHERE gcpmd.cal_period_map_id = gcpm.cal_period_map_id
AND gcpmd.target_relative_year_code <> ''CURRENT''
AND fcpb_src.cal_period_id = gcpmg.source_cal_period_id
AND gcpm.source_calendar_id = fcpb_src.calendar_id
AND gcpm.source_dimension_group_id = fcpb_src.dimension_group_id
AND fcpb_tgt.cal_period_id = gcpmg.target_cal_period_id
AND gcpm.target_calendar_id = fcpb_tgt.calendar_id
AND gcpm.target_dimension_group_id = fcpb_tgt.dimension_group_id');
SELECT decode(COUNT(gcpmd.cal_period_map_id), 0, 'Y', 'N')
INTO p_year_end_values_match
FROM gcs_cal_period_map_dtls gcpmd,
gcs_cal_period_maps_gt gcpmg,
gcs_cal_period_maps gcpm,
fem_cal_periods_b fcpb_src,
fem_cal_periods_b fcpb_tgt
WHERE gcpmd.cal_period_map_id = gcpm.cal_period_map_id
AND gcpmd.target_relative_year_code <> 'CURRENT'
AND fcpb_src.cal_period_id = gcpmg.source_cal_period_id
AND gcpm.source_calendar_id = fcpb_src.calendar_id
AND gcpm.source_dimension_group_id = fcpb_src.dimension_group_id
AND fcpb_tgt.cal_period_id = gcpmg.target_cal_period_id
AND gcpm.target_calendar_id = fcpb_tgt.calendar_id
AND gcpm.target_dimension_group_id = fcpb_tgt.dimension_group_id;
INSERT INTO gcs_cal_period_maps_gt
(source_cal_period_id, target_cal_period_id)
VALUES
(p_target_cal_period_id, p_target_cal_period_id);
'INSERT INTO gcs_cal_period_maps_gt (source_cal_period_id, target_cal_period_id )
VALUES (' ||
p_target_cal_period_id || ', ' ||
p_target_cal_period_id || '
)');
INSERT INTO gcs_cal_period_maps_gt
(source_cal_period_id, target_cal_period_id)
VALUES
(p_target_cal_period_id, p_target_cal_period_id);
'SELECT ghb.calendar_id, ghb.dimension_group_id
INTO l_calendar_id, l_dimension_grp_id
FROM gcs_hierarchies_b ghb
WHERE ghb.hierarchy_id = ' ||
p_hierarchy_id);
SELECT ghb.calendar_id, ghb.dimension_group_id
INTO l_calendar_id, l_dimension_grp_id
FROM gcs_hierarchies_b ghb
WHERE ghb.hierarchy_id = p_hierarchy_id;
' SELECT gcpm.cal_period_map_id
INTO l_cal_period_map_id
FROM gcs_cal_period_maps gcpm,
fem_cal_periods_b fcpb
WHERE gcpm.target_calendar_id = ' ||
l_calendar_id || '
AND gcpm.target_dimension_group_id = ' ||
l_dimension_grp_id || '
AND fcpb.cal_period_id = ' ||
p_source_cal_period_id || '
AND gcpm.source_calendar_id = fcpb.calendar_id
AND gcpm.source_dimension_group_id = fcpb.dimension_group_id');
SELECT gcpm.cal_period_map_id
INTO l_cal_period_map_id
FROM gcs_cal_period_maps gcpm, fem_cal_periods_b fcpb
WHERE gcpm.target_calendar_id = l_calendar_id
AND gcpm.target_dimension_group_id = l_dimension_grp_id
AND fcpb.cal_period_id = p_source_cal_period_id
AND gcpm.source_calendar_id = fcpb.calendar_id
AND gcpm.source_dimension_group_id = fcpb.dimension_group_id;
'INSERT INTO gcs_cal_period_maps_gt (source_cal_period_id, target_cal_period_id)
SELECT fcpb.cal_period_id, p_source_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_calendar_id || '
AND fcpb.dimension_group_id = ' ||
l_dimension_grp_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 =' ||
gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM')
.attribute_id || '
AND fcpa_year.attribute_id =' ||
gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR')
.attribute_id || '
AND fcpa_number.version_id =' ||
gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM')
.version_id || '
AND fcpa_year.version_id =' ||
gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_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 )');
INSERT INTO gcs_cal_period_maps_gt
(source_cal_period_id, target_cal_period_id)
SELECT p_source_cal_period_id, fcpb.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_calendar_id
AND fcpb.dimension_group_id = l_dimension_grp_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 =
gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM')
.attribute_id
AND fcpa_year.attribute_id =
gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_YEAR')
.attribute_id
AND fcpa_number.version_id =
gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-GL_PERIOD_NUM')
.version_id
AND fcpa_year.version_id =
gcs_utility_pkg.g_dimension_attr_info('CAL_PERIOD_ID-ACCOUNTING_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);
INSERT INTO gcs_cal_period_maps_gt
(source_cal_period_id, target_cal_period_id)
VALUES
(p_source_cal_period_id, p_source_cal_period_id);
SELECT gcerd.entry_id, gcerd.stat_entry_id, gcerd.request_error_code
INTO l_prior_entry_id, l_prior_stat_entry_id, l_request_error_code
FROM gcs_cons_eng_run_dtls gcerd
WHERE gcerd.child_entity_id = p_entity_id
AND gcerd.category_code = 'DATAPREPARATION'
AND EXISTS
(SELECT 'X'
FROM gcs_cons_eng_runs gcer
WHERE gcer.run_name = gcerd.run_name
AND gcer.run_entity_id = gcerd.consolidation_entity_id
AND gcer.hierarchy_id = p_hierarchy_id
AND gcer.cal_period_id = p_prior_cal_period_id
AND gcer.balance_type_code = p_balance_type_code
AND gcer.most_recent_flag = 'Y');
SELECT gcs_entry_headers_s.nextval INTO l_entry_id FROM dual;
SELECT gcs_entry_headers_s.nextval INTO l_stat_entry_id FROM dual;
INSERT INTO gcs_entry_lines
(entry_line_number,
product_id,
natural_account_id,
channel_id,
line_item_id,
project_id,
customer_id,
intercompany_id,
task_id,
user_dim1_id,
user_dim2_id,
user_dim3_id,
user_dim4_id,
user_dim5_id,
user_dim6_id,
user_dim7_id,
user_dim8_id,
user_dim9_id,
user_dim10_id,
xtd_balance_e,
ytd_balance_e,
ptd_debit_balance_e,
ptd_credit_balance_e,
ytd_debit_balance_e,
ytd_credit_balance_e,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
entry_id,
line_type_code,
description,
company_cost_center_org_id,
financial_elem_id)
SELECT gel.entry_line_number,
gel.product_id,
gel.natural_account_id,
gel.channel_id,
gel.line_item_id,
gel.project_id,
gel.customer_id,
gel.intercompany_id,
gel.task_id,
gel.user_dim1_id,
gel.user_dim2_id,
gel.user_dim3_id,
gel.user_dim4_id,
gel.user_dim5_id,
gel.user_dim6_id,
gel.user_dim7_id,
gel.user_dim8_id,
gel.user_dim9_id,
gel.user_dim10_id,
DECODE(fea_attr.dim_attribute_varchar_member,
'REVENUE',
0,
'EXPENSE',
0,
gel.xtd_balance_e),
gel.ytd_balance_e,
0,
0,
gel.ytd_debit_balance_e,
gel.ytd_credit_balance_e,
gel.creation_date,
gel.created_by,
gel.last_update_date,
gel.last_updated_by,
gel.last_update_login,
DECODE(gel.entry_id,
l_prior_entry_id,
l_entry_id,
l_stat_entry_id),
gel.line_type_code,
gel.description,
gel.company_cost_center_org_id,
gel.financial_elem_id
FROM gcs_entry_lines gel,
fem_ln_items_attr flia,
fem_ext_acct_types_attr fea_attr
WHERE gel.entry_id IN (l_prior_entry_id, l_prior_stat_entry_id)
AND gel.line_item_id = flia.line_item_id
AND flia.attribute_id = l_line_item_type_attr
AND flia.version_id = l_line_item_type_version
AND flia.dim_attribute_varchar_member =
fea_attr.ext_account_type_code
AND fea_attr.attribute_id = l_acct_type_attr
AND fea_attr.version_id = l_acct_type_version;
gcs_cons_eng_run_dtls_pkg.update_entry_headers(p_run_detail_id => p_run_detail_id,
p_entry_id => l_entry_id,
p_stat_entry_id => l_stat_entry_id,
p_pre_prop_entry_id => NULL,
p_pre_prop_stat_entry_id => NULL,
p_request_error_code => l_request_error_code,
p_bp_request_error_code => l_request_error_code);
gcs_cons_eng_run_dtls_pkg.update_entry_headers(p_run_detail_id => p_run_detail_id,
p_entry_id => NULL,
p_stat_entry_id => NULL,
p_pre_prop_entry_id => NULL,
p_pre_prop_stat_entry_id => NULL,
p_request_error_code => 'NOT_APPLICABLE',
p_bp_request_error_code => 'NOT_APPLICABLE');
' SELECT gea.ledger_id,
geca.currency_code,
DECODE (fla.dim_attribute_varchar_member,
geca.currency_code,
''ENTERED'',
''TRANSLATED''
)
INTO l_source_ledger_id,
l_source_currency_code,
l_source_currency_type_code
FROM gcs_entity_cons_attrs geca, fem_ledgers_attr fla, gcs_entities_attr gea
WHERE geca.entity_id = ' ||
p_entity_id || '
AND geca.hierarchy_id = ' ||
p_hierarchy_id || '
AND fea.entity_id = geca.entity_id
AND gea.data_type_code = ' ||
p_balance_type_code || '
AND fcpa.cal_period_id = ' ||
p_target_cal_period_id || '
AND fcpa.attribute_id = ' ||
l_period_end_date_attr || '
AND fcpa.version_id = ' ||
l_period_end_date_version || '
AND fcpa.date_assign_value BETWEEN gea.effective_start_date
AND NVL(gea.effective_end_date, fcpa.date_assign_value )
AND gea.ledger_id = fla.ledger_id
AND fla.version_id = ' ||
gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-LEDGER_FUNCTIONAL_CRNCY_CODE')
.version_id || '
AND fla.attribute_id = ' ||
gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-LEDGER_FUNCTIONAL_CRNCY_CODE')
.attribute_id);
SELECT gea.ledger_id,
geca.currency_code,
DECODE(fla.dim_attribute_varchar_member,
geca.currency_code,
'ENTERED',
'TRANSLATED')
INTO l_source_ledger_id,
l_source_currency_code,
l_source_currency_type_code
FROM gcs_entity_cons_attrs geca,
fem_ledgers_attr fla,
gcs_entities_attr gea,
fem_cal_periods_attr fcpa
WHERE geca.entity_id = p_entity_id
AND geca.hierarchy_id = p_hierarchy_id
AND gea.entity_id = geca.entity_id
AND gea.data_type_code = p_balance_type_code
AND fcpa.cal_period_id = p_target_cal_period_id
AND fcpa.attribute_id = l_period_end_date_attr
AND fcpa.version_id = l_period_end_date_version
AND fcpa.date_assign_value BETWEEN gea.effective_start_date AND
NVL(gea.effective_end_date, fcpa.date_assign_value)
AND gea.ledger_id = fla.ledger_id
AND fla.version_id =
gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-LEDGER_FUNCTIONAL_CRNCY_CODE')
.version_id
AND fla.attribute_id =
gcs_utility_pkg.g_dimension_attr_info('LEDGER_ID-LEDGER_FUNCTIONAL_CRNCY_CODE')
.attribute_id;
' SELECT MAX (source_cal_period_id)
INTO l_max_period
FROM gcs_cal_period_maps_gt');
SELECT MAX(source_cal_period_id)
INTO l_max_period
FROM gcs_cal_period_maps_gt;
'SELECT nvl(ownership_percent, 100) * 0.01
INTO l_owner_percentage
FROM gcs_cons_relationships gcr,
gcs_treatments_b gtb
WHERE cons_relationship_id =' ||
p_cons_rel_id || '
AND gcr.treatment_id = gtb.treatment_id' || '
AND gtb.consolidation_type_code = ''PARTIAL''');
SELECT NVL(ownership_percent, 100) * 0.01
INTO l_owner_percentage
FROM gcs_cons_relationships gcr, gcs_treatments_b gtb
WHERE cons_relationship_id = p_cons_rel_id
AND gcr.treatment_id = gtb.treatment_id
AND gtb.consolidation_type_code = 'PARTIAL';
gcs_cons_eng_run_dtls_pkg.update_entry_headers(p_run_detail_id => p_run_detail_id,
p_entry_id => NULL,
p_stat_entry_id => NULL,
p_pre_prop_entry_id => NULL,
p_pre_prop_stat_entry_id => NULL,
p_request_error_code => x_errbuf);
gcs_cons_eng_run_dtls_pkg.update_entry_headers(p_run_detail_id => p_run_detail_id,
p_entry_id => NULL,
p_stat_entry_id => NULL,
p_pre_prop_entry_id => NULL,
p_pre_prop_stat_entry_id => NULL,
p_request_error_code => x_errbuf);
gcs_cons_eng_run_dtls_pkg.update_entry_headers(p_run_detail_id => p_run_detail_id,
p_entry_id => NULL,
p_stat_entry_id => NULL,
p_pre_prop_entry_id => NULL,
p_pre_prop_stat_entry_id => NULL,
p_request_error_code => x_errbuf);
gcs_cons_eng_run_dtls_pkg.update_entry_headers(p_run_detail_id => p_run_detail_id,
p_entry_id => NULL,
p_stat_entry_id => NULL,
p_pre_prop_entry_id => NULL,
p_pre_prop_stat_entry_id => NULL,
p_request_error_code => x_errbuf);
l_select_gt_clause VARCHAR2(5000); -- vars to form a cursor
l_inc_select_clause VARCHAR2(5000); -- vars to form a cursor
l_insert_clause VARCHAR2(5000); -- vars to form a cursor
l_insert_gt_clause VARCHAR2(5000); -- vars to form a cursor
l_insert_statement VARCHAR2(32767); -- vars to form a cursor
l_insert_from_gt_statement VARCHAR2(32767); -- vars to form a cursor
l_inc_insert_statement VARCHAR2(32767); -- vars to form a cursor
l_insert_clause := '
INSERT /*+ APPEND */ INTO gcs_entry_lines
(entry_id,' || l_dims || '
ptd_debit_balance_e,
ptd_credit_balance_e,
ytd_debit_balance_e,
ytd_credit_balance_e,
xtd_balance_e,
ytd_balance_e,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login
)' || g_nl;
l_insert_gt_clause := '
INSERT INTO gcs_entry_lines_gt
(entry_id, cal_period_id, ' ||
l_dims || '
ptd_debit_balance_e,
ptd_credit_balance_e,
ytd_debit_balance_e,
ytd_credit_balance_e,
xtd_balance_e,
ytd_balance_e)' || g_nl;
l_select_gt_clause := '
fb.ptd_debit_balance_e,
fb.ptd_credit_balance_e,
DECODE (fb.cal_period_id,
p_max_period, ytd_debit_balance_e, 0
) ytd_debit_balance_e,
DECODE (fb.cal_period_id,
p_max_period, ytd_credit_balance_e, 0
) ytd_credit_balance_e,
NVL(fb.ptd_debit_balance_e,0) - NVL(fb.ptd_credit_balance_e,0),
DECODE (fb.cal_period_id,
p_max_period, NVL(fb.ytd_debit_balance_e,0) - NVL (fb.ytd_credit_balance_e,0), 0
) ytd_balance_e ' || g_nl;
l_inc_select_clause := '
SUM(fb.ptd_debit_balance_e) PTD_DEBIT_BALANCE_E,
SUM(fb.ptd_credit_balance_e) PTD_CREDIT_BALANCE_E,
SUM(fb.ytd_debit_balance_e) YTD_DEBIT_BALANCE_E,
SUM(fb.ytd_credit_balance_e) YTD_CREDIT_BALANCE_E,
SUM(NVL(fb.xtd_balance_f, fb.xtd_balance_e)) XTD_BALANCE_E,
SUM(NVL(fb.ytd_balance_f, fb.ytd_balance_e)) YTD_BALANCE_E,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID ';
AND fb.last_updated_by_request_id = gdsd.associated_request_id
AND gcia.run_name = p_run_name
AND gcia.child_entity_id = p_entity_id
AND gcia.load_id = gdsd.load_id ';
g_insert_statement VARCHAR2(32000);
'' SELECT fla.dim_attribute_numeric_member
INTO l_source_global_vs_combo
FROM fem_ledgers_attr fla,
fem_dim_attributes_b fdab,
fem_dim_attr_versions_b fdavb
WHERE fla.ledger_id = '' || p_source_ledger_id || ''
AND fla.attribute_id = fdab.attribute_id
AND fdab.attribute_varchar_label = ''''GLOBAL_VS_COMBO''''
AND fla.version_id = fdavb.version_id
AND fdavb.attribute_id = fla.attribute_id
AND fdavb.default_version_flag = ''''Y'''' '');
SELECT fla.dim_attribute_numeric_member
INTO l_source_global_vs_combo
FROM fem_ledgers_attr fla,
fem_dim_attributes_b fdab,
fem_dim_attr_versions_b fdavb
WHERE fla.ledger_id = p_source_ledger_id
AND fla.attribute_id = fdab.attribute_id
AND fdab.attribute_varchar_label = ''GLOBAL_VS_COMBO''
AND fla.version_id = fdavb.version_id
AND fdavb.attribute_id = fla.attribute_id
AND fdavb.default_version_flag = ''Y'';
g_insert_statement := ''' || l_insert_gt_clause ||
'''||''
SELECT decode(fb.currency_code,
''''STAT'''',
:l_stat_entry_id,
:l_entry_id), fb.cal_period_id, '';
'' SELECT value_set_id
INTO l_source_value_set_id
FROM fem_global_vs_combo_defs
WHERE global_vs_combo_id = ''||l_source_global_vs_combo||''
AND dimension_id = ''||gcs_utility_pkg.g_gcs_dimension_info (l_index_column_name).dimension_id);
SELECT value_set_id
INTO l_source_value_set_id
FROM fem_global_vs_combo_defs
WHERE global_vs_combo_id = l_source_global_vs_combo
AND dimension_id = gcs_utility_pkg.g_gcs_dimension_info (l_index_column_name).dimension_id;
g_insert_statement := g_insert_statement || ''fb.'' || l_index_column_name || '', '';
SELECT fod.object_definition_id
INTO l_hierarchy_obj_def_id
FROM fem_xdim_dimensions fxd,
fem_object_definition_b fod,
fem_cal_periods_attr fcpa
WHERE fxd.dimension_id = gcs_utility_pkg.g_gcs_dimension_info (l_index_column_name).dimension_id
AND fxd.default_mvs_hierarchy_obj_id = fod.object_id
AND fcpa.cal_period_id = p_cal_period_id
AND fcpa.attribute_id = l_cal_attribute_id
AND fcpa.version_id = l_cal_version_id
AND fcpa.date_assign_value BETWEEN fod.effective_start_date AND fod.effective_end_date;
g_insert_statement := g_insert_statement
|| ''fnah.parent_id, '';
g_insert_statement := g_insert_statement
|| ''fcoh.parent_id, '';
g_insert_statement := g_insert_statement
|| ''fcoh_inter.parent_id, '';
g_insert_statement := g_insert_statement
|| ''flih.parent_id, '';
g_insert_statement := g_insert_statement
|| ''fpdh.parent_id, '';
g_insert_statement := g_insert_statement
|| ''fpjh.parent_id, '';
g_insert_statement := g_insert_statement
|| ''fchh.parent_id, '';
g_insert_statement := g_insert_statement
|| ''fcuh.parent_id, '';
g_insert_statement := g_insert_statement
|| ''fud1h.parent_id, '';
g_insert_statement := g_insert_statement
|| ''fud2h.parent_id, '';
g_insert_statement := g_insert_statement
|| ''fud3h.parent_id, '';
g_insert_statement := g_insert_statement
|| ''fud4h.parent_id, '';
g_insert_statement := g_insert_statement
|| ''fud5h.parent_id, '';
g_insert_statement := g_insert_statement
|| ''fud6h.parent_id, '';
g_insert_statement := g_insert_statement
|| ''fud7h.parent_id, '';
g_insert_statement := g_insert_statement
|| ''fud8h.parent_id, '';
g_insert_statement := g_insert_statement
|| ''fud9h.parent_id, '';
g_insert_statement := g_insert_statement
|| ''fud10h.parent_id, '';
g_insert_statement := g_insert_statement || ''' ||
l_inc_select_clause || ''';
AND fb.last_updated_by_request_id = gdsd.associated_request_id
AND gcia.run_name = :p_run_name
AND gcia.child_entity_id = :p_entity_id
AND gcia.load_id = gdsd.load_id
''||l_where_text;
g_insert_statement := g_insert_statement || ''' ||
replace(replace(l_select_gt_clause, '''', ''''''),
'p_max_period',
':p_max_period') || ''';
'' g_insert_statement = ''|| g_insert_statement
);
g_insert_statement := g_insert_statement
|| l_from_text
|| l_where_text;
l_insert_statement := l_insert_gt_clause ||
' SELECT decode(fb.currency_code, ''STAT'', l_stat_entry_id, l_entry_id), ' ||
' fb.cal_period_id, ' || l_fb_dims ||
l_select_gt_clause || l_from_gt_clause ||
l_where_gt_clause;
l_insert_from_gt_statement := l_insert_clause || '
SELECT /*+ PARALLEL (fb) */ fb.entry_id, ' ||
l_fb_dims || '
SUM (NVL (fb.ptd_debit_balance_e, 0)),
SUM (NVL (fb.ptd_credit_balance_e, 0)),
SUM (NVL (ytd_debit_balance_e, 0)),
SUM (NVL (ytd_credit_balance_e, 0)),
SUM(DECODE(fea_attr.dim_attribute_varchar_member, ''REVENUE'', NVL(xtd_balance_e,0),
''EXPENSE'', NVL(xtd_balance_e,0),
NVL(ytd_balance_e,0))),
SUM (NVL (ytd_balance_e, 0)),
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
FROM gcs_entry_lines_gt fb,
fem_ln_items_attr flia,
fem_ext_acct_types_attr fea_attr
WHERE fb.line_item_id = flia.line_item_id
AND flia.attribute_id = l_line_item_type_attr
AND flia.version_id = l_line_item_type_version
AND flia.dim_attribute_varchar_member = fea_attr.ext_account_type_code
AND fea_attr.attribute_id = l_acct_type_attr
AND fea_attr.version_id = l_acct_type_version
GROUP BY ' || l_fb_dims ||
' entry_id;';
l_inc_insert_statement := l_insert_clause ||
' SELECT decode(fb.currency_code, ''STAT'', x_stat_entry_id, x_entry_id), ' ||
l_fb_dims || l_inc_select_clause ||
l_inc_from_clause || l_inc_where_clause ||
' GROUP BY ' || l_fb_dims ||
' decode(fb.currency_code, ''STAT'', x_stat_entry_id, x_entry_id);';
SELECT gea.source_system_code
INTO l_source_system_code
FROM gcs_entities_attr gea,
fem_cal_periods_attr fcpa
WHERE gea.entity_id = p_entity_id
AND gea.data_type_code = p_balance_type_code
AND fcpa.cal_period_id = p_target_cal_period_id
AND fcpa.attribute_id = l_period_end_date_attr
AND fcpa.version_id = l_period_end_date_version
AND fcpa.date_assign_value BETWEEN gea.effective_start_date
AND NVL(gea.effective_end_date, fcpa.date_assign_value ) ;
''SELECT cpmgt.source_cal_period_id cal_period_id
BULK COLLECT INTO l_periods_list
FROM fem_data_locations fdl,
gcs_cal_period_maps_gt cpmgt
WHERE fdl.ledger_id = '' ||p_source_ledger_id||''
AND fdl.cal_period_id = cpmgt.source_cal_period_id
AND fdl.source_system_code = ''||l_source_system_code||''
AND fdl.dataset_code = '' ||p_source_dataset_code||''
AND fdl.table_name = ''''FEM_BALANCES'''''');
SELECT cpmgt.source_cal_period_id cal_period_id
BULK COLLECT INTO l_periods_list
FROM fem_data_locations fdl,
gcs_cal_period_maps_gt cpmgt
WHERE fdl.ledger_id = p_source_ledger_id
AND fdl.cal_period_id = cpmgt.source_cal_period_id
AND fdl.source_system_code = l_source_system_code
AND fdl.dataset_code = p_source_dataset_code
AND fdl.table_name = ''FEM_BALANCES'';
SELECT gcs_entry_headers_s.NEXTVAL
INTO l_entry_id
FROM DUAL;
SELECT gcs_entry_headers_s.NEXTVAL
INTO l_stat_entry_id
FROM DUAL;
' || l_insert_statement || '
AND fb.currency_code IN (p_source_currency_code, ''STAT'')
AND fb.financial_elem_id = 140;
' || l_insert_statement || '
AND fb.financial_elem_id = 140
AND fb.currency_code = p_source_currency_code;
' || l_insert_statement || l_curr_where_clause || ';
' || l_insert_statement || l_curr_where_clause || ';
'' g_insert_statement = ''
|| g_insert_statement
);
|| '' EXECUTE IMMEDIATE g_insert_statement ''
|| g_nl
|| '' USING ''
|| l_stat_entry_id || '', '' || l_entry_id ||'', ''
|| p_max_period ||'', ''|| p_max_period ||'', '' || p_max_period ||'', ''
|| '' l_periods_list(counter), '' || p_source_ledger_id ||'', ''
|| '' l_source_system_code, ''
|| p_currency_type_code ||'', ''
|| p_entity_id ||'', ''|| p_balance_type_code ||'', ''
|| p_source_currency_code ||'', ''
|| '' p_source_dataset_code ''
|| '' p_source_currency_code ''
);
g_insert_statement := g_insert_statement || ''
AND fb.financial_elem_id = 140
AND fb.currency_code IN (:p_source_currency_code, ''''STAT'''') '';
EXECUTE IMMEDIATE g_insert_statement
USING l_stat_entry_id, l_entry_id,
p_max_period, p_max_period, p_max_period,
l_periods_list(counter), p_source_ledger_id,
l_source_system_code,
p_currency_type_code, p_entity_id,
p_source_dataset_code, p_source_currency_code;
g_insert_statement := g_insert_statement || ''
AND fb.financial_elem_id = 140
AND fb.currency_code = :p_source_currency_code '';
EXECUTE IMMEDIATE g_insert_statement
USING l_stat_entry_id, l_entry_id,
p_max_period, p_max_period, p_max_period,
l_periods_list(counter), p_source_ledger_id,
l_source_system_code,
p_currency_type_code, p_entity_id, p_source_dataset_code,
p_source_currency_code;
g_insert_statement := g_insert_statement || ''' ||
l_curr_vs_map_where_clause || ''';
EXECUTE IMMEDIATE g_insert_statement
USING l_stat_entry_id, l_entry_id,
p_max_period, p_max_period, p_max_period,
l_periods_list(counter), p_source_ledger_id,
l_source_system_code,
p_currency_type_code, p_entity_id,
p_source_dataset_code, p_source_currency_code;
g_insert_statement := g_insert_statement || ''' ||
l_curr_vs_map_where_clause ||
''';
EXECUTE IMMEDIATE g_insert_statement
USING l_stat_entry_id, l_entry_id,
p_max_period, p_max_period, p_max_period,
l_periods_list(counter), p_source_ledger_id,
l_source_system_code,
p_currency_type_code, p_entity_id, p_source_dataset_code,
p_source_currency_code;
SELECT ''Y''
INTO l_has_row_flag
FROM DUAL
WHERE EXISTS (SELECT 1 FROM gcs_entry_lines_gt WHERE entry_id = l_entry_id);
SELECT gcs_entry_headers_s.NEXTVAL
INTO l_proportional_entry_id
FROM DUAL;
SELECT ''Y''
INTO l_has_stat_row_flag
FROM DUAL
WHERE EXISTS (SELECT 1 FROM gcs_entry_lines_gt WHERE entry_id = l_stat_entry_id);
SELECT gcs_entry_headers_s.NEXTVAL
INTO l_stat_proportional_entry_id
FROM DUAL;
replace(l_insert_from_gt_statement, '''', '''''') || '''
);
' || l_insert_from_gt_statement;
'' SELECT decode(count(run_name), 0, ''''Y'''', ''''N'''')
INTO l_first_ever_data_prepped
FROM gcs_cons_eng_runs
WHERE hierarchy_id = ''||p_hierarchy_id||''
AND run_entity_id = ''||p_entity_id||''
AND balance_type_code = ''||p_balance_type_code||''
AND (cal_period_id = ''||p_cal_period_record.prev_cal_period_id||''
OR (cal_period_id = ''||p_cal_period_record.cal_period_id||''
AND status_code NOT IN (''''NOT_STARTED'''', ''''IN_PROGRESS'''')))'');
SELECT decode(count(run_name), 0, ''Y'', ''N'')
INTO l_first_ever_data_prepped
FROM gcs_cons_eng_runs
WHERE hierarchy_id = p_hierarchy_id
AND run_entity_id = p_entity_id
AND balance_type_code = p_balance_type_code
AND ( cal_period_id = p_cal_period_record.prev_cal_period_id
OR (cal_period_id = p_cal_period_record.cal_period_id
AND status_code NOT IN (''NOT_STARTED'', ''IN_PROGRESS'')));
'' UPDATE gcs_entry_lines gel
SET gel.ytd_balance_e = gel.xtd_balance_e,
gel.ytd_debit_balance_e = gel.ptd_debit_balance_e,
gel.ytd_credit_balance_e = gel.ptd_credit_balance_e
WHERE gel.entry_id = ''||l_entry_id ||''
AND EXISTS ( SELECT ''''X''''
FROM fem_ln_items_attr flia,
fem_ext_acct_types_attr feata
WHERE feata.dim_attribute_varchar_member IN (''''REVENUE'''', ''''EXPENSE'''')
AND flia.attribute_id =''|| g_li_eat_attr_id ||''
AND flia.version_id =''|| g_li_eat_ver_id ||''
AND flia.value_set_id =''|| g_li_vs_id ||''
AND feata.attribute_id = '' || g_eatc_batc_attr_id || ''
AND feata.version_id = '' || g_eatc_batc_ver_id || ''
AND feata.ext_account_type_code = flia.dim_attribute_varchar_member
AND gel.line_item_id = flia.line_item_id)'');
UPDATE gcs_entry_lines gel
SET gel.ytd_balance_e = gel.xtd_balance_e,
gel.ytd_debit_balance_e = gel.ptd_debit_balance_e,
gel.ytd_credit_balance_e = gel.ptd_credit_balance_e
WHERE gel.entry_id = l_entry_id
AND EXISTS ( SELECT ''X''
FROM fem_ln_items_attr flia,
fem_ext_acct_types_attr feata
WHERE feata.dim_attribute_varchar_member IN (''REVENUE'', ''EXPENSE'')
AND flia.attribute_id = g_li_eat_attr_id
AND flia.version_id = g_li_eat_ver_id
AND flia.value_set_id = g_li_vs_id
AND feata.attribute_id = g_eatc_batc_attr_id
AND feata.version_id = g_eatc_batc_ver_id
AND feata.ext_account_type_code = flia.dim_attribute_varchar_member
AND gel.line_item_id = flia.line_item_id);
'' UPDATE gcs_entry_lines gel
SET (gel.ytd_balance_e, gel.ytd_credit_balance_e,gel.ytd_debit_balance_e) =
(SELECT NVL (fb.ytd_balance_e, 0)
+ NVL (gel.xtd_balance_e, 0),
NVL (fb.ytd_credit_balance_e, 0)
+ NVL (gel.ptd_credit_balance_e, 0),
NVL (fb.ytd_debit_balance_e, 0)
+ NVL (gel.ptd_debit_balance_e, 0)
FROM fem_balances fb,
fem_ln_items_attr flia,
fem_ext_acct_types_attr feata
WHERE feata.dim_attribute_varchar_member IN
(''''REVENUE'''', ''''EXPENSE'''')
AND flia.attribute_id =''|| g_li_eat_attr_id ||''
AND flia.version_id =''|| g_li_eat_ver_id ||''
AND flia.value_set_id =''|| g_li_vs_id ||''
AND feata.attribute_id = '' || g_eatc_batc_attr_id || ''
AND feata.version_id = '' || g_eatc_batc_ver_id || ''
AND feata.ext_account_type_code = flia.dim_attribute_varchar_member
AND fb.cal_period_id =''|| p_cal_period_record.prev_cal_period_id||''
AND fb.line_item_id = flia.line_item_id
AND fb.source_system_code = '' || l_source_system_code||''' || g_nl ||
get_dimension_text('AND fb.', '= gel.', 'Y') || ')
WHERE gel.entry_id = ''||l_entry_id);
UPDATE gcs_entry_lines gel
SET (gel.ytd_balance_e, gel.ytd_credit_balance_e,gel.ytd_debit_balance_e) =
(SELECT NVL (fb.ytd_balance_e, 0)
+ NVL (gel.xtd_balance_e, 0),
NVL (fb.ytd_credit_balance_e, 0)
+ NVL (gel.ptd_credit_balance_e, 0),
NVL (fb.ytd_debit_balance_e, 0)
+ NVL (gel.ptd_debit_balance_e, 0)
FROM fem_balances fb,
fem_ln_items_attr flia,
fem_ext_acct_types_attr feata
WHERE feata.dim_attribute_varchar_member IN (''REVENUE'', ''EXPENSE'')
AND flia.attribute_id = g_li_eat_attr_id
AND flia.version_id = g_li_eat_ver_id
AND flia.value_set_id = g_li_vs_id
AND feata.attribute_id = g_eatc_batc_attr_id
AND feata.version_id = g_eatc_batc_ver_id
AND feata.ext_account_type_code = flia.dim_attribute_varchar_member
AND fb.cal_period_id = p_cal_period_record.prev_cal_period_id
AND fb.line_item_id = flia.line_item_id
AND fb.source_system_code = l_source_system_code' || g_nl ||
get_dimension_text('AND fb.', '= gel.', 'Y') || ')
WHERE gel.entry_id = l_entry_id;
'' SELECT threshold_amount,
threshold_currency
INTO l_threshold,
l_threshold_currency
FROM gcs_hierarchies_b
WHERE hierarchy_id = ''||p_hierarchy_id);
SELECT threshold_amount, threshold_currency
INTO l_threshold, l_threshold_currency
FROM gcs_hierarchies_b
WHERE hierarchy_id = p_hierarchy_id;
SELECT DECODE(SUSPENSE_EXCEEDED_FLAG, ''Y'', ''WARNING'', ''COMPLETED'')
INTO retcode
FROM gcs_entry_headers
WHERE entry_id = l_entry_id;
'' SELECT NVL (fc_1.minimum_accountable_unit, POWER (10, -fc_1.PRECISION)),
NVL (fc_stat.minimum_accountable_unit, POWER (10, -fc_stat.PRECISION))
INTO l_precision,
l_stat_precision
FROM fnd_currencies fc_1, fnd_currencies fc_stat
WHERE fc_1.currency_code = '' ||p_source_currency_code||''
AND fc_stat.currency_code = ''''STAT'''''');
SELECT NVL (fc_1.minimum_accountable_unit, POWER (10, -fc_1.PRECISION)),
NVL (fc_stat.minimum_accountable_unit, POWER (10, -fc_stat.PRECISION))
INTO l_precision, l_stat_precision
FROM fnd_currencies fc_1, fnd_currencies fc_stat
WHERE fc_1.currency_code = p_source_currency_code
AND fc_stat.currency_code = ''STAT'';
INSERT INTO gcs_entry_lines
(entry_id,' || l_dims || '
ptd_debit_balance_e,
ptd_credit_balance_e,
ytd_debit_balance_e,
ytd_credit_balance_e,
xtd_balance_e,
ytd_balance_e,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login
)
SELECT decode(entry_id, l_entry_id, l_proportional_entry_id, l_stat_proportional_entry_id),
' || l_dims || '
ROUND(ptd_debit_balance_e * p_owner_percentage/decode(entry_id, l_entry_id, l_precision, l_stat_precision))*decode(entry_id, l_entry_id, l_precision, l_stat_precision),
ROUND(ptd_credit_balance_e * p_owner_percentage/decode(entry_id, l_entry_id, l_precision, l_stat_precision))*decode(entry_id, l_entry_id, l_precision, l_stat_precision),
ROUND(ytd_debit_balance_e * p_owner_percentage/decode(entry_id, l_entry_id, l_precision, l_stat_precision))*decode(entry_id, l_entry_id, l_precision, l_stat_precision),
ROUND(ytd_credit_balance_e * p_owner_percentage/decode(entry_id, l_entry_id, l_precision, l_stat_precision))*decode(entry_id, l_entry_id, l_precision, l_stat_precision),
ROUND(ptd_debit_balance_e * p_owner_percentage/decode(entry_id, l_entry_id, l_precision, l_stat_precision))*decode(entry_id, l_entry_id, l_precision, l_stat_precision)
- ROUND(ptd_credit_balance_e * p_owner_percentage/decode(entry_id, l_entry_id, l_precision, l_stat_precision))*decode(entry_id, l_entry_id, l_precision, l_stat_precision),
ROUND(ytd_debit_balance_e * p_owner_percentage/decode(entry_id, l_entry_id, l_precision, l_stat_precision))*decode(entry_id, l_entry_id, l_precision, l_stat_precision)
- ROUND(ytd_credit_balance_e * p_owner_percentage/decode(entry_id, l_entry_id, l_precision, l_stat_precision))*decode(entry_id, l_entry_id, l_precision, l_stat_precision),
creation_date, created_by, last_update_date,
last_updated_by, last_update_login
FROM gcs_entry_lines
WHERE entry_id in ( l_entry_id, l_stat_entry_id);
SELECT decode(SUSPENSE_EXCEEDED_FLAG, ''Y'', ''WARNING'', ''COMPLETED'')
INTO retcode
FROM gcs_entry_headers
WHERE entry_id = l_proportional_entry_id;
gcs_cons_eng_run_dtls_pkg.update_entry_headers
(p_run_detail_id => p_run_detail_id,
p_entry_id => l_proportional_entry_id,
p_stat_entry_id => l_stat_proportional_entry_id,
p_pre_prop_entry_id => l_entry_id,
p_pre_prop_stat_entry_id => l_stat_entry_id,
p_request_error_code => retcode,
p_bp_request_error_code => retcode
);
gcs_cons_eng_run_dtls_pkg.update_entry_headers
(p_run_detail_id => p_run_detail_id,
p_entry_id => l_entry_id,
p_stat_entry_id => l_stat_entry_id,
p_pre_prop_entry_id => l_proportional_entry_id,
p_pre_prop_stat_entry_id => l_stat_proportional_entry_id,
p_request_error_code => retcode,
p_bp_request_error_code => retcode
);
SELECT NVL(interco_map_enabled_flag,''N'')
INTO l_imap_enabled_flag
FROM gcs_system_options;
'' UPDATE gcs_entry_lines gel
SET gel.intercompany_id = ( SELECT intercompany_id
FROM gcs_interco_map_dtls gimd
WHERE gimd.line_item_id = gel.line_item_id
)
WHERE gel.entry_id IN( l_entry_id, l_stat_entry_id, l_proportional_entry_id, l_stat_proportional_entry_id)
AND EXISTS ( SELECT intercompany_id
FROM gcs_interco_map_dtls gimd
WHERE gimd.line_item_id = gel.line_item_id
);'');
UPDATE gcs_entry_lines gel
SET gel.intercompany_id = ( SELECT intercompany_id
FROM gcs_interco_map_dtls gimd
WHERE gimd.line_item_id = gel.line_item_id
)
WHERE gel.entry_id IN( l_entry_id, l_stat_entry_id, l_proportional_entry_id, l_stat_proportional_entry_id)
AND EXISTS ( SELECT intercompany_id
FROM gcs_interco_map_dtls gimd
WHERE gimd.line_item_id = gel.line_item_id
);
DELETE FROM gcs_entry_headers
WHERE entry_id IN (l_entry_id, l_stat_entry_id,
l_proportional_entry_id, l_stat_proportional_entry_id);
DELETE FROM gcs_entry_lines
WHERE entry_id IN (l_entry_id, l_stat_entry_id,
l_proportional_entry_id, l_stat_proportional_entry_id);
DELETE FROM gcs_entry_headers
WHERE entry_id IN (l_entry_id, l_stat_entry_id,
l_proportional_entry_id, l_stat_proportional_entry_id);
DELETE FROM gcs_entry_lines
WHERE entry_id IN (l_entry_id, l_stat_entry_id,
l_proportional_entry_id, l_stat_proportional_entry_id);
gcs_cons_eng_run_dtls_pkg.update_entry_headers
(p_run_detail_id => p_run_detail_id,
p_entry_id => NULL,
p_stat_entry_id => NULL,
p_pre_prop_entry_id => NULL,
p_pre_prop_stat_entry_id => NULL,
p_request_error_code => ''NOT_APPLICABLE'',
p_bp_request_error_code => ''NOT_APPLICABLE''
);
DELETE FROM gcs_entry_headers
WHERE entry_id IN (l_entry_id, l_stat_entry_id,
l_proportional_entry_id, l_stat_proportional_entry_id);
DELETE FROM gcs_entry_lines
WHERE entry_id IN (l_entry_id, l_stat_entry_id,
l_proportional_entry_id, l_stat_proportional_entry_id);
SELECT gcs_entry_headers_s.NEXTVAL
INTO x_entry_id
FROM DUAL;
SELECT gcs_entry_headers_s.NEXTVAL
INTO x_stat_entry_id
FROM DUAL;
replace(l_inc_insert_statement, '''', '''''') || '''
);
' || l_inc_insert_statement || '
ELSE
IF fnd_log.g_current_runtime_level <= fnd_log.level_statement
THEN
fnd_log.STRING (fnd_log.level_statement,
g_pkg_name || ''.'' || l_api_name,
'' g_insert_statement = ''
|| g_insert_statement
);
''EXECUTE IMMEDIATE g_insert_statement ''
|| g_nl
|| '' USING ''
|| x_stat_entry_id || '', '' || x_entry_id ||'', ''
|| fnd_global.user_id ||'', ''|| fnd_global.login_id ||'', ''|| p_source_ledger_id ||'', ''
|| g_ledger_ssc_attr_id ||'', ''
|| g_ledger_ssc_ver_id ||'', ''
|| p_currency_type_code ||'', ''|| p_hierarchy_id ||'', ''
|| p_entity_id ||'', ''|| p_balance_type_code ||'', ''
|| g_ln_item_vs_id ||'', ''
|| g_li_eat_attr_id ||'', ''
|| g_eatc_batc_attr_id ||'', ''
|| g_li_eat_ver_id ||'', ''
|| g_eatc_batc_ver_id ||'', ''
|| p_source_currency_code ||'', ''
|| p_source_dataset_code ||'', ''|| p_run_name ||'', ''|| p_entity_id ||'', ''
|| x_stat_entry_id || '', '' || x_entry_id
);
EXECUTE IMMEDIATE g_insert_statement
USING x_stat_entry_id, x_entry_id,
fnd_global.user_id, fnd_global.user_id, fnd_global.login_id, p_source_ledger_id,
g_ledger_ssc_attr_id, g_ledger_ssc_ver_id, p_currency_type_code,
p_entity_id, p_balance_type_code, g_ln_item_vs_id, g_li_eat_attr_id,
g_eatc_batc_attr_id, g_li_eat_ver_id, g_eatc_batc_ver_id,
p_source_currency_code, p_source_dataset_code, p_run_name,
p_entity_id, x_stat_entry_id, x_entry_id;
SELECT ''Y''
INTO l_has_row_flag
FROM DUAL
WHERE EXISTS (SELECT 1 FROM gcs_entry_lines WHERE entry_id = x_entry_id);
SELECT ''Y''
INTO l_has_stat_row_flag
FROM DUAL
WHERE EXISTS (SELECT 1 FROM gcs_entry_lines WHERE entry_id = x_stat_entry_id);
SELECT decode (p_owner_percentage, 1, entry_id, pre_prop_entry_id),
decode (p_owner_percentage, 1, stat_entry_id, pre_prop_stat_entry_id)
INTO l_pre_entry_id, l_pre_stat_entry_id
FROM gcs_cons_eng_run_dtls
WHERE child_entity_id = p_entity_id
AND category_code = ''DATAPREPARATION''
AND run_name in (
SELECT nvl(associated_run_name, run_name)
FROM gcs_cons_eng_runs
WHERE hierarchy_id = p_hierarchy_id
AND cal_period_id = p_target_cal_period_id
AND balance_type_code = p_balance_type_code
AND most_recent_flag = ''Y''
);
UPDATE gcs_entry_lines gel
SET (ptd_debit_balance_e, ptd_credit_balance_e, xtd_balance_e,
ytd_debit_balance_e, ytd_credit_balance_e, ytd_balance_e) =
(SELECT gel.ptd_debit_balance_e - gel_pre.ptd_debit_balance_e,
gel.ptd_credit_balance_e - gel_pre.ptd_credit_balance_e,
gel.xtd_balance_e - gel_pre.xtd_balance_e,
gel.ytd_debit_balance_e - gel_pre.ytd_debit_balance_e,
gel.ytd_credit_balance_e - gel_pre.ytd_credit_balance_e,
gel.ytd_balance_e - gel_pre.ytd_balance_e
FROM gcs_entry_lines gel_pre
WHERE gel_pre.entry_id = decode(gel.entry_id,
x_entry_id,
l_pre_entry_id,
l_pre_stat_entry_id) ' ||
get_dimension_text('AND gel.', '= gel_pre.', 'Y') || '
)
WHERE gel.entry_id in (x_entry_id, x_stat_entry_id)
AND EXISTS (SELECT 1
FROM gcs_entry_lines gel_pre
WHERE gel_pre.entry_id = decode(gel.entry_id,
x_entry_id,
l_pre_entry_id,
l_pre_stat_entry_id) ' ||
get_dimension_text('AND gel.', '= gel_pre.', 'Y') || '
) ;
SELECT gcs_entry_headers_s.NEXTVAL
INTO x_stat_prop_entry_id
FROM DUAL;
SELECT gcs_entry_headers_s.NEXTVAL
INTO x_prop_entry_id
FROM DUAL;
'' SELECT threshold_amount, threshold_currency
INTO l_threshold, l_threshold_currency
FROM gcs_hierarchies_b
WHERE hierarchy_id = ''||p_hierarchy_id);
SELECT threshold_amount, threshold_currency
INTO l_threshold, l_threshold_currency
FROM gcs_hierarchies_b
WHERE hierarchy_id = p_hierarchy_id;
SELECT DECODE(SUSPENSE_EXCEEDED_FLAG, ''Y'', ''WARNING'', gcs_utility_pkg.g_ret_sts_success)
INTO retcode
FROM gcs_entry_headers
WHERE entry_id = x_entry_id;
SELECT NVL (fc_1.minimum_accountable_unit, POWER (10, -fc_1.PRECISION)),
NVL (fc_stat.minimum_accountable_unit, POWER (10, -fc_stat.PRECISION))
INTO l_precision, l_stat_precision
FROM fnd_currencies fc_1, fnd_currencies fc_stat
WHERE fc_1.currency_code = p_source_currency_code
AND fc_stat.currency_code = ''STAT'';
INSERT INTO gcs_entry_lines
(entry_id,' || l_dims || '
ptd_debit_balance_e,
ptd_credit_balance_e,
ytd_debit_balance_e,
ytd_credit_balance_e,
xtd_balance_e,
ytd_balance_e,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login
)
SELECT decode(entry_id, x_entry_id, x_prop_entry_id, x_stat_prop_entry_id),
' || l_dims || '
ROUND(ptd_debit_balance_e * p_owner_percentage/decode(entry_id, x_entry_id, l_precision, l_stat_precision))*decode(entry_id, x_entry_id, l_precision, l_stat_precision),
ROUND(ptd_credit_balance_e * p_owner_percentage/decode(entry_id, x_entry_id, l_precision, l_stat_precision))*decode(entry_id, x_entry_id, l_precision, l_stat_precision),
ROUND(ytd_debit_balance_e * p_owner_percentage/decode(entry_id, x_entry_id, l_precision, l_stat_precision))*decode(entry_id, x_entry_id, l_precision, l_stat_precision),
ROUND(ytd_credit_balance_e * p_owner_percentage/decode(entry_id, x_entry_id, l_precision, l_stat_precision))*decode(entry_id, x_entry_id, l_precision, l_stat_precision),
ROUND(ptd_debit_balance_e * p_owner_percentage/decode(entry_id, x_entry_id, l_precision, l_stat_precision))*decode(entry_id, x_entry_id, l_precision, l_stat_precision)
- ROUND(ptd_credit_balance_e * p_owner_percentage/decode(entry_id, x_entry_id, l_precision, l_stat_precision))*decode(entry_id, x_entry_id, l_precision, l_stat_precision),
ROUND(ytd_debit_balance_e * p_owner_percentage/decode(entry_id, x_entry_id, l_precision, l_stat_precision))*decode(entry_id, x_entry_id, l_precision, l_stat_precision)
- ROUND(ytd_credit_balance_e * p_owner_percentage/decode(entry_id, x_entry_id, l_precision, l_stat_precision))*decode(entry_id, x_entry_id, l_precision, l_stat_precision),
creation_date, created_by, last_update_date,
last_updated_by, last_update_login
FROM gcs_entry_lines
WHERE entry_id in ( x_entry_id, x_stat_entry_id);
SELECT decode(SUSPENSE_EXCEEDED_FLAG, ''Y'', ''WARNING'', gcs_utility_pkg.g_ret_sts_success)
INTO retcode
FROM gcs_entry_headers
WHERE entry_id = x_prop_entry_id;
SELECT NVL(interco_map_enabled_flag,''N'')
INTO l_imap_enabled_flag
FROM gcs_system_options;
'' UPDATE gcs_entry_lines gel
SET gel.intercompany_id = ( SELECT intercompany_id
FROM gcs_interco_map_dtls gimd
WHERE gimd.line_item_id = gel.line_item_id
)
WHERE gel.entry_id IN( x_entry_id, x_stat_entry_id, x_prop_entry_id, x_stat_prop_entry_id)
AND EXISTS ( SELECT intercompany_id
FROM gcs_interco_map_dtls gimd
WHERE gimd.line_item_id = gel.line_item_id
);'');
UPDATE gcs_entry_lines gel
SET gel.intercompany_id = ( SELECT intercompany_id
FROM gcs_interco_map_dtls gimd
WHERE gimd.line_item_id = gel.line_item_id
)
WHERE gel.entry_id IN( x_entry_id, x_stat_entry_id, x_prop_entry_id, x_stat_prop_entry_id)
AND EXISTS ( SELECT intercompany_id
FROM gcs_interco_map_dtls gimd
WHERE gimd.line_item_id = gel.line_item_id
);
SELECT gcr.child_entity_id,
gcr.hierarchy_id,
nvl(ownership_percent, 100) * 0.01 ownership_percent
FROM gcs_cons_relationships gcr, gcs_treatments_b gtb
WHERE cons_relationship_id = p_cons_relationship_id
AND gcr.treatment_id = gtb.treatment_id(+)
AND gtb.consolidation_type_code(+) = 'PARTIAL';