[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 := '
INSERT INTO gcs_entry_lines_gt
(entry_id, cal_period_id,
ptd_debit_balance_e,
ptd_credit_balance_e,
ytd_debit_balance_e,
ytd_credit_balance_e,
xtd_balance_e,
ytd_balance_e)
'||'
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 || '
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
'||l_where_text;
g_insert_statement := g_insert_statement || '
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_insert_statement = '|| g_insert_statement
);
g_insert_statement := g_insert_statement
|| l_from_text
|| l_where_text;
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;
INSERT INTO gcs_entry_lines_gt
(entry_id, cal_period_id,
ptd_debit_balance_e,
ptd_credit_balance_e,
ytd_debit_balance_e,
ytd_credit_balance_e,
xtd_balance_e,
ytd_balance_e)
SELECT decode(fb.currency_code, 'STAT', l_stat_entry_id, l_entry_id), fb.cal_period_id,
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
FROM fem_balances fb,
gcs_entity_cctr_orgs geco
WHERE l_periods_list(counter) = fb.cal_period_id
AND fb.source_system_code = l_source_system_code
AND fb.ledger_id = p_source_ledger_id
AND fb.currency_type_code = p_currency_type_code
AND fb.company_cost_center_org_id = geco.company_cost_center_org_id
AND geco.entity_id = p_entity_id
AND fb.dataset_code = p_source_dataset_code
AND fb.currency_code IN (p_source_currency_code, 'STAT')
AND fb.financial_elem_id = 140;
INSERT INTO gcs_entry_lines_gt
(entry_id, cal_period_id,
ptd_debit_balance_e,
ptd_credit_balance_e,
ytd_debit_balance_e,
ytd_credit_balance_e,
xtd_balance_e,
ytd_balance_e)
SELECT decode(fb.currency_code, 'STAT', l_stat_entry_id, l_entry_id), fb.cal_period_id,
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
FROM fem_balances fb,
gcs_entity_cctr_orgs geco
WHERE l_periods_list(counter) = fb.cal_period_id
AND fb.source_system_code = l_source_system_code
AND fb.ledger_id = p_source_ledger_id
AND fb.currency_type_code = p_currency_type_code
AND fb.company_cost_center_org_id = geco.company_cost_center_org_id
AND geco.entity_id = p_entity_id
AND fb.dataset_code = p_source_dataset_code
AND fb.financial_elem_id = 140
AND fb.currency_code = p_source_currency_code;
INSERT INTO gcs_entry_lines_gt
(entry_id, cal_period_id,
ptd_debit_balance_e,
ptd_credit_balance_e,
ytd_debit_balance_e,
ytd_credit_balance_e,
xtd_balance_e,
ytd_balance_e)
SELECT decode(fb.currency_code, 'STAT', l_stat_entry_id, l_entry_id), fb.cal_period_id,
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
FROM fem_balances fb,
gcs_entity_cctr_orgs geco
WHERE l_periods_list(counter) = fb.cal_period_id
AND fb.source_system_code = l_source_system_code
AND fb.ledger_id = p_source_ledger_id
AND fb.currency_type_code = p_currency_type_code
AND fb.company_cost_center_org_id = geco.company_cost_center_org_id
AND geco.entity_id = p_entity_id
AND fb.dataset_code = p_source_dataset_code
AND fb.currency_code = p_source_currency_code;
INSERT INTO gcs_entry_lines_gt
(entry_id, cal_period_id,
ptd_debit_balance_e,
ptd_credit_balance_e,
ytd_debit_balance_e,
ytd_credit_balance_e,
xtd_balance_e,
ytd_balance_e)
SELECT decode(fb.currency_code, 'STAT', l_stat_entry_id, l_entry_id), fb.cal_period_id,
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
FROM fem_balances fb,
gcs_entity_cctr_orgs geco
WHERE l_periods_list(counter) = fb.cal_period_id
AND fb.source_system_code = l_source_system_code
AND fb.ledger_id = p_source_ledger_id
AND fb.currency_type_code = p_currency_type_code
AND fb.company_cost_center_org_id = geco.company_cost_center_org_id
AND geco.entity_id = p_entity_id
AND fb.dataset_code = p_source_dataset_code
AND fb.currency_code = p_source_currency_code;
' 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 || '
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 || '
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;
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;
INSERT /*+ APPEND */ INTO gcs_entry_lines
(entry_id,
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 /*+ PARALLEL (fb) */ fb.entry_id,
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 entry_id;'
INSERT /*+ APPEND */ INTO gcs_entry_lines
(entry_id,
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 /*+ PARALLEL (fb) */ fb.entry_id,
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 entry_id;
' 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||'
)
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
)
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,
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),
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;
INSERT /*+ APPEND */ INTO gcs_entry_lines
(entry_id,
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(fb.currency_code, ''STAT'', x_stat_entry_id, x_entry_id),
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
FROM fem_balances fb,
fem_ledgers_attr fla,
gcs_entity_cctr_orgs geco,
gcs_cons_impact_analyses gcia,
gcs_data_sub_dtls gdsd
WHERE fb.ledger_id = p_source_ledger_id
AND fb.source_system_code = fla.DIM_ATTRIBUTE_NUMERIC_MEMBER
AND fla.ledger_id = fb.ledger_id
AND fla.attribute_id = g_ledger_ssc_attr_id
AND fla.version_id = g_ledger_ssc_ver_id
AND fb.company_cost_center_org_id = geco.company_cost_center_org_id
AND geco.entity_id = p_entity_id
AND p_balance_type_code = DECODE(fb.financial_elem_id, 140, ''ADB'', ''ACTUAL'')
AND ((fb.currency_type_code = ''TRANSLATED'' AND
fb.currency_code IN (''STAT'', p_source_currency_code)) OR
(fb.currency_type_code = ''ENTERED''))
AND fb.currency_type_code = p_currency_type_code
AND fb.dataset_code = p_source_dataset_code
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 GROUP BY decode(fb.currency_code, ''STAT'', x_stat_entry_id, x_entry_id);'
INSERT /*+ APPEND */ INTO gcs_entry_lines
(entry_id,
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(fb.currency_code, 'STAT', x_stat_entry_id, x_entry_id),
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
FROM fem_balances fb,
fem_ledgers_attr fla,
gcs_entity_cctr_orgs geco,
gcs_cons_impact_analyses gcia,
gcs_data_sub_dtls gdsd
WHERE fb.ledger_id = p_source_ledger_id
AND fb.source_system_code = fla.DIM_ATTRIBUTE_NUMERIC_MEMBER
AND fla.ledger_id = fb.ledger_id
AND fla.attribute_id = g_ledger_ssc_attr_id
AND fla.version_id = g_ledger_ssc_ver_id
AND fb.company_cost_center_org_id = geco.company_cost_center_org_id
AND geco.entity_id = p_entity_id
AND p_balance_type_code = DECODE(fb.financial_elem_id, 140, 'ADB', 'ACTUAL')
AND ((fb.currency_type_code = 'TRANSLATED' AND
fb.currency_code IN ('STAT', p_source_currency_code)) OR
(fb.currency_type_code = 'ENTERED'))
AND fb.currency_type_code = p_currency_type_code
AND fb.dataset_code = p_source_dataset_code
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 GROUP BY decode(fb.currency_code, 'STAT', x_stat_entry_id, x_entry_id);
' 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)
)
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)
) ;
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,
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),
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
);