[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT gdsd.load_id,
gdsd.load_name,
gdsd.entity_id,
gdsd.cal_period_id,
gdsd.currency_code,
gdsd.balance_type_code,
gdsd.load_method_code,
gdsd.currency_type_code,
gdsd.amount_type_code,
gdsd.measure_type_code,
gdsd.notify_options_code,
gea.ledger_id,
feb.entity_display_code,
flb.ledger_display_code,
gea.transform_rule_set_id,
gea.validation_rule_set_id,
gea.balances_rule_id,
gea.source_system_code,
gdtcb.source_dataset_code,
fda.dim_attribute_varchar_member
INTO p_datasub_info.load_id,
p_datasub_info.load_name,
p_datasub_info.entity_id,
p_datasub_info.cal_period_id,
p_datasub_info.currency_code,
p_datasub_info.balance_type_code,
p_datasub_info.load_method_code,
p_datasub_info.currency_type_code,
p_datasub_info.amount_type_code,
p_datasub_info.measure_type_code,
p_datasub_info.notify_options_code,
p_datasub_info.ledger_id,
p_datasub_info.entity_display_code,
p_datasub_info.ledger_display_code,
p_datasub_info.transform_rule_set_id,
p_datasub_info.validation_rule_set_id,
p_datasub_info.balances_rule_id,
p_datasub_info.source_system_code,
p_datasub_info.dataset_code,
p_datasub_info.ds_balance_type_code
FROM gcs_data_sub_dtls gdsd,
fem_entities_b feb,
fem_ledgers_b flb,
gcs_entities_attr gea,
gcs_data_type_codes_b gdtcb,
fem_datasets_attr fda,
fem_cal_periods_attr fcpa
WHERE gdsd.load_id = p_load_id
AND gdsd.entity_id = feb.entity_id
AND feb.entity_id = gea.entity_id
AND gea.data_type_code = gdsd.balance_type_code
AND gdsd.balance_type_code = gdtcb.data_type_code
AND flb.ledger_id = gea.ledger_id
AND fda.dataset_code = gdtcb.source_dataset_code
AND fda.attribute_id = l_balance_type_attr
AND fda.version_id = l_balance_type_version
AND fcpa.cal_period_id = gdsd.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 fb.budget_id,
fb.budget_display_code
INTO p_datasub_info.budget_id,
p_datasub_info.budget_display_code
FROM fem_datasets_attr fda,
fem_budgets_b fb
WHERE fda.dataset_code = p_datasub_info.dataset_code
AND fda.attribute_id = l_budget_attr
AND fda.version_id = l_budget_version
AND fb.budget_id = fda.dim_attribute_numeric_member;
SELECT fetb.encumbrance_type_id,
fetb.encumbrance_type_code
INTO p_datasub_info.encumbrance_type_id,
p_datasub_info.encumbrance_type_code
FROM fem_datasets_attr fda,
fem_encumbrance_types_b fetb
WHERE fda.dataset_code = p_datasub_info.dataset_code
AND fda.attribute_id = l_encumbrance_attr
AND fda.version_id = l_encumbrance_version
AND fetb.encumbrance_type_id = fda.dim_attribute_numeric_member;
SELECT status_code
INTO l_status_code
FROM gcs_data_sub_dtls
WHERE load_id = p_load_id;
SELECT 'I'
INTO l_execution_mode
FROM fem_data_locations fdl,
fem_ledgers_attr fla
WHERE fdl.ledger_id = p_datasub_info.ledger_id
AND fdl.cal_period_id = p_datasub_info.cal_period_id
AND fdl.dataset_code = p_datasub_info.dataset_code
AND fdl.source_system_code = p_datasub_info.source_system_code
AND ROWNUM < 2;
update_status(p_load_id => p_load_id);
program => 'FCH_UPDATE_DATA_STATUS',
sub_request => FALSE,
argument1 => p_load_id,
argument2 => NULL,
argument3 => NULL,
argument4 => NULL);
UPDATE gcs_data_sub_dtls
SET status_code = 'ERROR',
end_time = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_update_date = sysdate
WHERE load_id = p_load_id;
SELECT gdsd.load_id
FROM gcs_data_sub_dtls gdsd
WHERE gdsd.associated_request_id = FND_GLOBAL.CONC_REQUEST_ID;
SELECT DISTINCT translated_currency
FROM fem_dl_trans_curr
WHERE request_id >= p_request_id
AND object_id = p_object_id
AND ledger_id = p_ledger_id
AND cal_period_id = p_cal_period_id;
SELECT gsob.chart_of_accounts_id,
gsob.currency_code
INTO l_chart_of_accounts_id,
l_currency_code
FROM gl_sets_of_books gsob
WHERE gsob.set_of_books_id = p_datasub_info.ledger_id;
SELECT fibrd.bal_rule_obj_def_id,
fibrd.currency_option_code,
fibrd.xlated_bal_option_code,
fibr.include_avg_bal_flag,
fcpa.date_assign_value,
fcpv.cal_period_name
INTO l_bal_rule_obj_def_id,
l_currency_option_code,
l_xlated_bal_option_code,
l_enable_avg_bal_flag,
l_cal_period_end_date,
l_cal_period_name
FROM fem_intg_bal_rule_defs fibrd,
fem_intg_bal_rules fibr,
fem_object_definition_b fodb,
fem_cal_periods_attr fcpa,
fem_cal_periods_vl fcpv
WHERE fibrd.bal_rule_obj_def_id = fodb.object_definition_id
AND fibr.bal_rule_obj_id = fodb.object_id
AND fodb.object_id = p_datasub_info.balances_rule_id
AND fcpa.cal_period_id = p_datasub_info.cal_period_id
AND fcpa.cal_period_id = fcpv.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 fodb.effective_start_date AND
fodb.effective_end_date;
UPDATE gcs_data_sub_dtls
SET currency_code = l_currency_code
WHERE load_id = p_datasub_info.load_id;
INSERT INTO gcs_data_sub_dtls
(load_id,
load_name,
entity_id,
cal_period_id,
currency_code,
balance_type_code,
load_method_code,
currency_type_code,
amount_type_code,
measure_type_code,
notify_options_code,
notification_text,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
object_version_number,
start_time,
locked_flag,
most_recent_flag,
associated_request_id,
status_code,
balances_rule_id)
SELECT gcs_data_sub_dtls_s.nextval,
gcs_data_sub_dtls_s.nextval,
gdsd.entity_id,
gdsd.cal_period_id,
gdsd.currency_code,
'ADB',
gdsd.load_method_code,
gdsd.currency_type_code,
gdsd.amount_type_code,
gdsd.measure_type_code,
gdsd.notify_options_code,
gdsd.notification_text,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id,
1,
gdsd.start_time,
gdsd.locked_flag,
gdsd.most_recent_flag,
gdsd.associated_request_id,
gdsd.status_code,
gdsd.balances_rule_id
FROM gcs_data_sub_dtls gdsd
WHERE gdsd.load_id = p_datasub_info.load_id;
INSERT INTO gcs_data_sub_dtls
(load_id,
load_name,
entity_id,
cal_period_id,
currency_code,
balance_type_code,
load_method_code,
currency_type_code,
amount_type_code,
measure_type_code,
notify_options_code,
notification_text,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
object_version_number,
start_time,
locked_flag,
most_recent_flag,
associated_request_id,
status_code,
balances_rule_id)
SELECT gcs_data_sub_dtls_s.nextval,
gcs_data_sub_dtls_s.nextval,
gea.entity_id,
gdsd.cal_period_id,
gdsd.currency_code,
gdsd.balance_type_code,
gdsd.load_method_code,
gdsd.currency_type_code,
gdsd.amount_type_code,
gdsd.measure_type_code,
gdsd.notify_options_code,
gdsd.notification_text,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
1,
gdsd.start_time,
gdsd.locked_flag,
gdsd.most_recent_flag,
gdsd.associated_request_id,
gdsd.status_code,
gdsd.balances_rule_id
FROM gcs_data_sub_dtls gdsd,
gcs_entities_attr gea,
fem_cal_periods_attr fcpa
WHERE gdsd.associated_request_id = FND_GLOBAL.CONC_REQUEST_ID
AND gea.entity_id <> p_datasub_info.entity_id
AND gea.balances_rule_id = gdsd.balances_rule_id
AND gea.data_type_code = gdsd.balance_type_code
AND fcpa.cal_period_id = gdsd.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 fla.dim_attribute_numeric_member,
fgvcd_local_company.value_set_id,
fgvcd_local_org.value_set_id
INTO l_global_vs_combo_id,
l_company_vs_id,
l_org_vs_id
FROM fem_ledgers_attr fla,
fem_global_vs_combo_defs fgvcd_local_company,
fem_global_vs_combo_defs fgvcd_local_org
WHERE fla.ledger_id = p_datasub_info.ledger_id
AND fla.attribute_id = l_global_vs_combo_attr
AND fla.version_id = l_global_vs_combo_version
AND fla.dim_attribute_numeric_member =
fgvcd_local_company.global_vs_combo_id
AND fgvcd_local_company.dimension_id = 112
AND fla.dim_attribute_numeric_member =
fgvcd_local_org.global_vs_combo_id
AND fgvcd_local_org.dimension_id = 8;
SELECT fgvcd_fch_company.value_set_id,
fgvcd_fch_org.value_set_id
INTO l_fch_company_vs_id,
l_fch_org_vs_id
FROM fem_global_vs_combo_defs fgvcd_fch_company,
fem_global_vs_combo_defs fgvcd_fch_org
WHERE fgvcd_fch_company.global_vs_combo_id =
gcs_utility_pkg.g_fch_global_vs_combo_id
AND fgvcd_fch_org.global_vs_combo_id =
fgvcd_fch_company.global_vs_combo_id
AND fgvcd_fch_org.dimension_id = 8
AND fgvcd_fch_company.dimension_id = 112;
SELECT fxd.default_mvs_hierarchy_obj_id,
fodb.object_definition_id
INTO l_hier_obj_id,
l_hier_obj_definition_id
FROM fem_xdim_dimensions fxd,
fem_object_definition_b fodb
WHERE fxd.dimension_id = 8
AND fxd.default_mvs_hierarchy_obj_id = fodb.object_id
AND l_cal_period_end_date BETWEEN fodb.effective_start_date AND
fodb.effective_end_date;
SELECT min(fcmin.company_display_code),
min(fcmax.company_display_code)
INTO l_company_value_low,
l_company_value_high
FROM fem_companies_b fcmin,
fem_companies_b fcmax,
fem_cctr_orgs_hier fcoh,
fem_cctr_orgs_attr fcoa
WHERE fcoh.hierarchy_obj_def_id = l_hier_obj_definition_id
AND fcoh.parent_value_set_id = l_fch_org_vs_id
AND fcoh.child_value_set_id = l_org_vs_id
AND fcoh.child_id = fcoa.company_cost_center_org_id
AND fcoh.child_value_set_id = fcoa.value_set_id
AND fcoa.attribute_id = l_company_attr
AND fcoa.version_id = l_company_version
AND fcoa.dim_attribute_numeric_member = fcmin.company_id
AND fcoa.dim_attribute_numeric_member = fcmax.company_id
AND fcmin.value_set_id = l_fch_company_vs_id
AND fcmax.value_set_id = l_fch_company_vs_id;
SELECT min(fcmin.company_display_code),
min(fcmax.company_display_code)
INTO l_company_value_low,
l_company_value_high
FROM fem_companies_b fcmin,
fem_companies_b fcmax,
gcs_entity_organizations geo
WHERE geo.entity_id = p_datasub_info.entity_id
AND geo.company_cost_center_org_id = fcmin.company_id
AND geo.company_cost_center_org_id = fcmax.company_id;
INSERT INTO gcs_data_sub_dtls
(load_id,
load_name,
entity_id,
cal_period_id,
currency_code,
balance_type_code,
load_method_code,
currency_type_code,
amount_type_code,
measure_type_code,
notify_options_code,
notification_text,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
object_version_number,
start_time,
locked_flag,
most_recent_flag,
associated_request_id,
status_code,
balances_rule_id)
SELECT gcs_data_sub_dtls_s.nextval,
gcs_data_sub_dtls_s.nextval,
gdsd.entity_id,
gdsd.cal_period_id,
v_translated_balances.translated_currency,
gdsd.balance_type_code,
gdsd.load_method_code,
gdsd.currency_type_code,
gdsd.amount_type_code,
gdsd.measure_type_code,
gdsd.notify_options_code,
gdsd.notification_text,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
1,
gdsd.start_time,
gdsd.locked_flag,
gdsd.most_recent_flag,
gdsd.associated_request_id,
'IN_PROGRESS',
gdsd.balances_rule_id
FROM gcs_data_sub_dtls gdsd
WHERE gdsd.associated_request_id = FND_GLOBAL.CONC_REQUEST_ID;
SELECT status_code
INTO l_status_code
FROM fnd_concurrent_requests
WHERE request_id = FND_GLOBAL.conc_request_id;
UPDATE gcs_data_sub_dtls
SET status_code = DECODE(l_status_code,
'C',
'COMPLETED',
'E',
'ERROR',
'W',
'WARNING',
'WARNING'),
end_time = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_update_date = SYSDATE
WHERE associated_request_id = FND_GLOBAL.conc_request_id;
UPDATE gcs_data_sub_dtls prev_runs
SET most_recent_flag = 'N'
WHERE EXISTS (SELECT 'X'
FROM gcs_data_sub_dtls curr_run
WHERE curr_run.associated_request_id =
FND_GLOBAL.conc_request_id
AND curr_run.entity_id = prev_runs.entity_id
AND curr_run.cal_period_id = prev_runs.cal_period_id
AND curr_run.currency_code = prev_runs.currency_code
AND curr_run.balance_type_code =
prev_runs.balance_type_code
AND prev_runs.load_id < curr_run.load_id);
program => 'FCH_UPDATE_DATA_STATUS',
sub_request => FALSE,
argument1 => v_generated_loads.load_id,
argument2 => NULL,
argument3 => NULL,
argument4 => NULL);
UPDATE gcs_data_sub_dtls
SET status_code = DECODE(l_status_code,
'C',
'COMPLETED',
'E',
'ERROR',
'W',
'WARNING',
'WARNING'),
end_time = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_update_date = sysdate
WHERE associated_request_id = FND_GLOBAL.conc_request_id;
UPDATE gcs_data_sub_dtls
SET status_code = DECODE(l_status_code,
'C',
'COMPLETED',
'E',
'ERROR',
'W',
'WARNING',
'WARNING'),
end_time = SYSDATE,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_update_date = SYSDATE
WHERE associated_request_id = FND_GLOBAL.conc_request_id;
UPDATE gcs_data_sub_dtls prev_runs
SET most_recent_flag = 'N'
WHERE EXISTS (SELECT 'X'
FROM gcs_data_sub_dtls curr_run
WHERE curr_run.associated_request_id =
FND_GLOBAL.conc_request_id
AND curr_run.entity_id = prev_runs.entity_id
AND curr_run.cal_period_id = prev_runs.cal_period_id
AND curr_run.currency_code = prev_runs.currency_code
AND curr_run.balance_type_code = prev_runs.balance_type_code
AND prev_runs.load_id < curr_run.load_id);
SELECT gdsd.locked_flag
INTO l_locked_flag
FROM gcs_data_sub_dtls gdsd
WHERE gdsd.entity_id = l_datasub_info.entity_id
AND gdsd.cal_period_id = l_datasub_info.cal_period_id
AND gdsd.balance_type_code = l_datasub_info.balance_type_code
AND NVL(gdsd.currency_code, 'X') = l_datasub_info.currency_code
AND gdsd.most_recent_flag = 'Y';
UPDATE gcs_data_sub_dtls
SET most_recent_flag = 'N'
WHERE entity_id = l_datasub_info.entity_id
AND cal_period_id = l_datasub_info.cal_period_id
AND NVL(currency_code, 'X') = l_datasub_info.currency_code
AND balance_type_code = l_datasub_info.balance_type_code;
UPDATE gcs_data_sub_dtls
SET most_recent_flag = 'Y',
associated_request_id = FND_GLOBAL.conc_request_id
WHERE load_id = p_load_id;
DELETE gcs_data_sub_dtls
WHERE load_id = l_datasub_info.load_id;
PROCEDURE update_amounts_autonomous(p_datasub_info IN r_datasub_info,
p_first_ever_loaded IN VARCHAR2,
p_currency_type_code IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
g_api || '.UPDATE_AMOUNTS_AUTONOMOUS.begin',
'<>');
UPDATE gcs_bal_interface_t
SET ytd_debit_balance_e = DECODE(l_datasub_info.measure_type_code,
'BALANCE',
DECODE(SIGN(ytd_balance_e),
1,
ytd_balance_e,
0),
ytd_debit_balance_e),
ytd_credit_balance_e = DECODE(l_datasub_info.measure_type_code,
'BALANCE',
DECODE(SIGN(ytd_balance_e),
-1,
ABS(ytd_balance_e),
0),
ytd_credit_balance_e),
ytd_balance_e = DECODE(l_datasub_info.measure_type_code,
'DEBIT_CREDIT',
NVL(ytd_debit_balance_e, 0) -
NVL(ytd_credit_balance_e, 0),
ytd_balance_e),
ptd_debit_balance_e = DECODE(l_first_ever_loaded,
'Y',
DECODE(l_datasub_info.measure_type_code,
'BALANCE',
DECODE(SIGN(ytd_balance_e),
1,
ytd_balance_e,
0),
ytd_debit_balance_e),
0),
ptd_credit_balance_e = DECODE(l_first_ever_loaded,
'Y',
DECODE(l_datasub_info.measure_type_code,
'BALANCE',
DECODE(SIGN(ytd_balance_e),
-1,
ABS(ytd_balance_e),
0),
ytd_credit_balance_e),
0),
ptd_balance_e = DECODE(l_first_ever_loaded,
'Y',
DECODE(l_datasub_info.measure_type_code,
'DEBIT_CREDIT',
NVL(ytd_debit_balance_e, 0) -
NVL(ytd_credit_balance_e, 0),
ytd_balance_e),
0),
currency_code = DECODE(financial_elem_display_code,
'10000',
'STAT',
l_datasub_info.currency_code)
WHERE load_id = l_datasub_info.load_id;
UPDATE gcs_bal_interface_t
SET ytd_debit_balance_e = DECODE(l_datasub_info.measure_type_code,
'BALANCE',
DECODE(SIGN(ytd_balance_e),
1,
ytd_balance_e,
0),
ytd_debit_balance_e),
ytd_credit_balance_e = DECODE(l_datasub_info.measure_type_code,
'BALANCE',
DECODE(SIGN(ytd_balance_e),
-1,
ABS(ytd_balance_e),
0),
ytd_credit_balance_e),
ytd_balance_e = DECODE(l_datasub_info.measure_type_code,
'DEBIT_CREDIT',
NVL(ytd_debit_balance_e, 0) -
NVL(ytd_credit_balance_e, 0),
ytd_balance_e),
ytd_debit_balance_f = DECODE(l_datasub_info.measure_type_code,
'BALANCE',
DECODE(SIGN(ytd_balance_e),
1,
ytd_balance_e,
0),
ytd_debit_balance_e),
ytd_credit_balance_f = DECODE(l_datasub_info.measure_type_code,
'BALANCE',
DECODE(SIGN(ytd_balance_e),
-1,
ABS(ytd_balance_e),
0),
ytd_credit_balance_e),
ytd_balance_f = DECODE(l_datasub_info.measure_type_code,
'DEBIT_CREDIT',
NVL(ytd_debit_balance_e, 0) -
NVL(ytd_credit_balance_e, 0),
ytd_balance_e),
ptd_debit_balance_e = DECODE(l_datasub_info.measure_type_code,
'BALANCE',
DECODE(SIGN(ytd_balance_e),
1,
ytd_balance_e,
0),
ytd_debit_balance_e),
ptd_credit_balance_e = DECODE(l_datasub_info.measure_type_code,
'BALANCE',
DECODE(SIGN(ytd_balance_e),
-1,
ABS(ytd_balance_e),
0),
ytd_credit_balance_e),
ptd_balance_e = DECODE(l_datasub_info.measure_type_code,
'DEBIT_CREDIT',
NVL(ytd_debit_balance_e, 0) -
NVL(ytd_credit_balance_e, 0),
ytd_balance_e),
ptd_debit_balance_f = DECODE(l_datasub_info.measure_type_code,
'BALANCE',
DECODE(SIGN(ytd_balance_e),
1,
ytd_balance_e,
0),
ytd_debit_balance_e),
ptd_credit_balance_f = DECODE(l_datasub_info.measure_type_code,
'BALANCE',
DECODE(SIGN(ytd_balance_e),
-1,
ABS(ytd_balance_e),
0),
ytd_credit_balance_e),
ptd_balance_f = DECODE(l_datasub_info.measure_type_code,
'DEBIT_CREDIT',
NVL(ytd_debit_balance_e, 0) -
NVL(ytd_credit_balance_e, 0),
ytd_balance_e),
currency_code = DECODE(financial_elem_display_code,
'10000',
'STAT',
l_datasub_info.currency_code)
WHERE load_id = l_datasub_info.load_id;
UPDATE gcs_bal_interface_t
SET ytd_debit_balance_e = DECODE(l_datasub_info.measure_type_code,
'BALANCE',
DECODE(SIGN(ytd_balance_e),
1,
ytd_balance_e,
0),
ytd_debit_balance_e),
ytd_credit_balance_e = DECODE(l_datasub_info.measure_type_code,
'BALANCE',
DECODE(SIGN(ytd_balance_e),
-1,
ABS(ytd_balance_e),
0),
ytd_credit_balance_e),
ytd_balance_e = DECODE(l_datasub_info.measure_type_code,
'DEBIT_CREDIT',
NVL(ytd_debit_balance_e, 0) -
NVL(ytd_credit_balance_e, 0),
ytd_balance_e),
ytd_debit_balance_f = DECODE(l_datasub_info.measure_type_code,
'BALANCE',
DECODE(SIGN(ytd_balance_f),
1,
ytd_balance_f,
0),
ytd_debit_balance_f),
ytd_credit_balance_f = DECODE(l_datasub_info.measure_type_code,
'BALANCE',
DECODE(SIGN(ytd_balance_f),
-1,
ABS(ytd_balance_f),
0),
ytd_credit_balance_f),
ytd_balance_f = DECODE(l_datasub_info.measure_type_code,
'DEBIT_CREDIT',
NVL(ytd_debit_balance_f, 0) -
NVL(ytd_credit_balance_f, 0),
ytd_balance_f),
ptd_debit_balance_e = DECODE(l_datasub_info.measure_type_code,
'BALANCE',
DECODE(SIGN(ytd_balance_e),
1,
ytd_balance_e,
0),
ytd_debit_balance_e),
ptd_credit_balance_e = DECODE(l_datasub_info.measure_type_code,
'BALANCE',
DECODE(SIGN(ytd_balance_e),
-1,
ABS(ytd_balance_e),
0),
ytd_credit_balance_e),
ptd_balance_e = DECODE(l_datasub_info.measure_type_code,
'DEBIT_CREDIT',
NVL(ytd_debit_balance_e, 0) -
NVL(ytd_credit_balance_e, 0),
ytd_balance_e),
ptd_debit_balance_f = DECODE(l_datasub_info.measure_type_code,
'BALANCE',
DECODE(SIGN(ytd_balance_f),
1,
ytd_balance_f,
0),
ytd_debit_balance_f),
ptd_credit_balance_f = DECODE(l_datasub_info.measure_type_code,
'BALANCE',
DECODE(SIGN(ytd_balance_f),
-1,
ABS(ytd_balance_f),
0),
ytd_credit_balance_f),
ptd_balance_f = DECODE(l_datasub_info.measure_type_code,
'DEBIT_CREDIT',
NVL(ytd_debit_balance_f, 0) -
NVL(ytd_credit_balance_f, 0),
ytd_balance_f),
financial_elem_display_code = DECODE(currency_code,
'STAT',
'10000',
financial_elem_display_code),
currency_code = DECODE(financial_elem_display_code,
'10000',
'STAT',
currency_code)
WHERE load_id = l_datasub_info.load_id;
g_api || '.UPDATE_AMOUNTS_AUTONOMOUS.end',
'<>');
END update_amounts_autonomous;
INSERT INTO fem_bal_interface_t
(load_set_id,
load_method_code,
bal_post_type_code,
cal_per_dim_grp_display_code,
cal_period_number,
cal_period_end_date,
cctr_org_display_code,
currency_code,
currency_type_code,
ds_balance_type_code,
source_system_display_code,
ledger_display_code,
financial_elem_display_code,
product_display_code,
natural_account_display_code,
channel_display_code,
line_item_display_code,
project_display_code,
customer_display_code,
entity_display_code,
intercompany_display_code,
task_display_code,
user_dim1_display_code,
user_dim2_display_code,
user_dim3_display_code,
user_dim4_display_code,
user_dim5_display_code,
user_dim6_display_code,
user_dim7_display_code,
user_dim8_display_code,
user_dim9_display_code,
user_dim10_display_code,
xtd_balance_e,
xtd_balance_f,
ytd_balance_e,
ytd_balance_f,
ptd_debit_balance_e,
ptd_credit_balance_e,
ytd_debit_balance_e,
ytd_credit_balance_e,
--Bugfix 5066041: Added additional columns to support additional data types
budget_display_code,
encumbrance_type_code)
SELECT p_load_id,
p_load_method_code,
p_bal_post_type_code,
p_dim_grp_disp_code,
p_cal_period_number,
p_cal_period_end_date,
gbit.cctr_org_display_code,
gbit.currency_code,
p_currency_type_code,
p_ds_balance_type_code,
p_source_system_disp_code,
p_ledger_disp_code,
gbit.financial_elem_display_code,
gbit.product_display_code,
gbit.natural_account_display_code,
gbit.channel_display_code,
gbit.line_item_display_code,
gbit.project_display_code,
gbit.customer_display_code,
p_entity_display_code,
gbit.intercompany_display_code,
gbit.task_display_code,
gbit.user_dim1_display_code,
gbit.user_dim2_display_code,
gbit.user_dim3_display_code,
gbit.user_dim4_display_code,
gbit.user_dim5_display_code,
gbit.user_dim6_display_code,
gbit.user_dim7_display_code,
gbit.user_dim8_display_code,
gbit.user_dim9_display_code,
gbit.user_dim10_display_code,
DECODE(feata.dim_attribute_varchar_member,
'REVENUE',
NVL(gbit.ptd_balance_e, gbit.ytd_balance_e),
'EXPENSE',
NVL(gbit.ptd_balance_e, gbit.ytd_balance_e),
NVL(gbit.ytd_balance_e, gbit.ptd_balance_e)),
DECODE(feata.dim_attribute_varchar_member,
'REVENUE',
NVL(gbit.ptd_balance_f, gbit.ytd_balance_f),
'EXPENSE',
NVL(gbit.ptd_balance_f, gbit.ytd_balance_f),
NVL(gbit.ytd_balance_f, gbit.ptd_balance_f)),
NVL(gbit.ytd_balance_e, gbit.ptd_balance_e),
NVL(gbit.ytd_balance_f, gbit.ptd_balance_f),
DECODE(feata.ext_account_type_code,
'RETAINED_EARNINGS',
0,
NVL(gbit.ptd_debit_balance_e,
NVL(gbit.ytd_debit_balance_e, 0))),
DECODE(feata.ext_account_type_code,
'RETAINED_EARNINGS',
0,
NVL(gbit.ptd_credit_balance_e,
NVL(gbit.ytd_credit_balance_e, 0))),
NVL(gbit.ytd_debit_balance_e, NVL(gbit.ptd_debit_balance_e, 0)),
NVL(gbit.ytd_credit_balance_e,
NVL(gbit.ptd_credit_balance_e, 0)),
--Bugfix 5066041: Added additional columns to support new data types
p_budget_display_code,
p_encumbrance_type_code
FROM gcs_bal_interface_t gbit,
fem_ln_items_b flb,
fem_ln_items_attr flia,
fem_ext_acct_types_attr feata
WHERE gbit.load_id = p_load_id
AND gbit.line_item_display_code = flb.line_item_display_code
AND flb.line_item_id = flia.line_item_id
AND flb.value_set_id = p_line_item_vs_id
-- Attribute for Extended Account Type
-- Bugfix 4644576: Removed assigning the attributes using hardcoded literals
AND flia.attribute_id = l_line_item_type_attr
AND flia.version_id = l_line_item_type_version
AND flia.value_set_id = flb.value_set_id
AND flia.dim_attribute_varchar_member =
feata.ext_account_type_code
AND feata.attribute_id = l_acct_type_attr
AND feata.version_id = l_acct_type_version;
UPDATE gcs_data_sub_dtls
SET status_code = 'INVALID_FEM_INDEX'
WHERE load_id = p_load_id;
UPDATE gcs_data_sub_dtls
SET status_code = 'TRANSFER_ERROR'
WHERE load_id = p_load_id;
UPDATE gcs_data_sub_dtls
SET status_code = 'TRANSFORMATION_FAILED'
WHERE load_id = l_datasub_info.load_id;
UPDATE gcs_data_sub_dtls
SET status_code = 'VALIDATION_FAILED'
WHERE load_id = l_datasub_info.load_id;
SELECT fla.dim_attribute_varchar_member
INTO l_func_crncy_code
FROM fem_ledgers_attr fla
WHERE fla.ledger_id = l_datasub_info.ledger_id
AND fla.attribute_id = g_ledger_curr_attr
AND fla.version_id = g_ledger_curr_version;
SELECT 'N'
INTO l_first_ever_loaded
FROM gcs_data_sub_dtls gdsd
WHERE gdsd.entity_id = l_datasub_info.entity_id
AND gdsd.balance_type_code = l_datasub_info.balance_type_code
AND gdsd.cal_period_id < l_datasub_info.cal_period_id
AND NVL(gdsd.currency_code, l_func_crncy_code) =
NVL(l_datasub_info.currency_code, l_func_crncy_code)
AND ROWNUM < 2;
SELECT 'I'
INTO l_execution_mode
FROM fem_data_locations fdl
WHERE fdl.ledger_id = l_datasub_info.ledger_id
AND fdl.cal_period_id = l_datasub_info.cal_period_id
AND fdl.dataset_code = l_datasub_info.dataset_code
AND fdl.source_system_code = l_datasub_info.source_system_code
AND rownum < 2;
PROCEDURE update_amounts(p_itemtype IN VARCHAR2,
p_itemkey IN VARCHAR2,
p_actid IN NUMBER,
p_funcmode IN VARCHAR2,
p_result IN OUT NOCOPY VARCHAR2) IS
l_datasub_info r_datasub_info;
g_api || '.UPDATE_AMOUNTS.begin',
'<>');
update_amounts_autonomous(p_datasub_info => l_datasub_info,
p_currency_type_code => l_currency_type_code,
p_first_ever_loaded => l_first_ever_loaded);
gcs_datasub_utility_pkg.update_ytd_balances(p_load_id => l_datasub_info.load_id,
p_source_system_code => l_datasub_info.source_system_code,
p_dataset_code => l_datasub_info.dataset_code,
p_cal_period_id => l_cal_period_info.prev_cal_period_id,
p_ledger_id => l_datasub_info.ledger_id,
p_currency_type => l_currency_type_code,
p_currency_code => l_datasub_info.currency_code);
gcs_datasub_utility_pkg.update_ptd_balances(p_load_id => l_datasub_info.load_id,
p_source_system_code => l_datasub_info.source_system_code,
p_dataset_code => l_datasub_info.dataset_code,
p_cal_period_id => l_cal_period_info.prev_cal_period_id,
p_ledger_id => l_datasub_info.ledger_id,
p_currency_type => l_currency_type_code,
p_currency_code => l_datasub_info.currency_code);
gcs_datasub_utility_pkg.update_ptd_balance_sheet(p_load_id => l_datasub_info.load_id,
p_source_system_code => l_datasub_info.source_system_code,
p_dataset_code => l_datasub_info.dataset_code,
p_cal_period_id => l_cal_period_info.prev_cal_period_id,
p_ledger_id => l_datasub_info.ledger_id,
p_currency_type => l_currency_type_code,
p_currency_code => l_datasub_info.currency_code);
g_api || '.UPDATE_AMOUNTS.end',
'<>');
END update_amounts;
SELECT fdgb.dimension_group_display_code,
fcpa_end_date.date_assign_value,
fcpa_period_num.number_assign_value
INTO l_period_dim_grp_disp_code,
l_period_end_date,
l_period_num
FROM fem_cal_periods_b fcpb,
fem_dimension_grps_b fdgb,
fem_cal_periods_attr fcpa_end_date,
fem_cal_periods_attr fcpa_period_num
WHERE fcpb.cal_period_id = l_datasub_info.cal_period_id
AND fcpb.dimension_group_id = fdgb.dimension_group_id
AND fcpb.cal_period_id = fcpa_end_date.cal_period_id
AND fcpa_end_date.attribute_id = l_period_end_date_attr
AND fcpa_end_date.version_id = l_period_end_date_version
AND fcpb.cal_period_id = fcpa_period_num.cal_period_id
AND fcpa_period_num.attribute_id = l_period_num_attr
AND fcpa_period_num.version_id = l_period_num_version;
SELECT source_system_display_code
INTO l_source_system_disp_code
FROM fem_source_systems_b
WHERE source_system_code = l_datasub_info.source_system_code;
SELECT 'I'
INTO l_load_method_code
FROM fem_data_locations fdl,
fem_ledgers_attr fla
WHERE fdl.ledger_id = l_datasub_info.ledger_id
AND fdl.cal_period_id = l_datasub_info.cal_period_id
AND fdl.dataset_code = l_datasub_info.dataset_code
AND fdl.source_system_code = l_datasub_info.source_system_code
AND rownum < 2;
SELECT fgvcd.value_set_id
INTO l_line_item_vs_id
FROM fem_ledgers_attr fla,
fem_global_vs_combo_defs fgvcd
WHERE fla.ledger_id = l_datasub_info.ledger_id
AND fgvcd.global_vs_combo_id = fla.dim_attribute_numeric_member
AND fla.attribute_id = g_ledger_vs_combo_attr
AND fla.version_id = g_ledger_vs_combo_version
AND fgvcd.dimension_id = 14;
PROCEDURE update_status(p_load_id IN NUMBER)
IS
l_datasub_info r_datasub_info;
g_api || '.UPDATE_STATUS.begin',
'<>');
SELECT status_code
INTO l_status_code
FROM fnd_concurrent_requests
WHERE request_id = FND_GLOBAL.conc_request_id;
UPDATE gcs_data_sub_dtls
SET status_code = DECODE(l_status_code,
'C',
'COMPLETED',
'E',
'ERROR',
'W',
'WARNING',
'WARNING'),
end_time = sysdate,
last_updated_by = FND_GLOBAL.USER_ID,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_update_date = sysdate
WHERE load_id = l_datasub_info.load_id;
UPDATE gcs_bal_interface_t
SET error_message_code = 'Please refer to concurrent request : ' ||
FND_GLOBAL.conc_request_id ||
' for more details.'
WHERE load_id = l_datasub_info.load_id;
UPDATE gcs_bal_interface_t
SET error_message_code = 'Please refer to FEM_BAL_INTERFACE_T to see if any rows failed.'
WHERE load_id = l_datasub_info.load_id;
g_api || '.UPDATE_STATUS.end',
'<>');
END update_status;
SELECT error_message_code BULK COLLECT
INTO l_msg_info
FROM gcs_bal_interface_t
WHERE load_id = l_datasub_info.load_id;
UPDATE gcs_data_sub_dtls
SET status_code = 'VALIDATION_MEMBERS_FAILED'
WHERE load_id = l_datasub_info.load_id;
SELECT 'Y'
INTO l_data_sub_exists_via_ui
FROM gcs_data_sub_dtls
WHERE associated_request_id = l_base_request_id
AND cal_period_id = l_cal_period_id
AND ROWNUM < 2;
SELECT fodb.object_id balances_rule_id,
fibr.ledger_id,
fibr.ds_bal_type_code,
gsob.currency_code,
fibr.include_avg_bal_flag,
fcpa.date_assign_value
INTO l_bal_rule_id,
l_ledger_id,
l_ds_bal_type_code,
l_curr_code,
l_avg_bal_flag,
l_cal_period_end_date
FROM fem_intg_bal_rule_defs fibrd,
fem_intg_bal_rules fibr,
fem_object_definition_b fodb,
gl_sets_of_books gsob,
fem_cal_periods_attr fcpa
WHERE gsob.set_of_books_id = fibr.ledger_id
AND fibrd.bal_rule_obj_def_id = fodb.object_definition_id
AND fibr.bal_rule_obj_id = fodb.object_id
AND fibrd.bal_rule_obj_def_id = l_bal_rule_version_id
AND fcpa.cal_period_id = l_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 fodb.effective_start_date AND
fodb.effective_end_date;
SELECT requested_start_date
INTO l_start_date
FROM fnd_concurrent_requests
WHERE request_id = l_base_request_id;
SELECT gea.entity_id BULK COLLECT
INTO l_entity_list
FROM gcs_entities_attr gea,
fem_entities_b feb,
fem_cal_periods_attr fcpa
WHERE gea.balances_rule_id = l_bal_rule_id
AND gea.source_system_code = 10
AND gea.data_type_code = 'ACTUAL'
AND gea.entity_id = feb.entity_id
AND feb.enabled_flag = 'Y'
AND fcpa.cal_period_id = l_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);
UPDATE gcs_data_sub_dtls
SET most_recent_flag = 'N'
WHERE most_recent_flag = 'Y'
AND cal_period_id = l_cal_period_id
AND balance_type_code = 'ACTUAL'
AND entity_id = l_entity_list(i);
' Inserting into gcs_data_sub_dtls for ACTUAL');
INSERT INTO gcs_data_sub_dtls
(load_id,
load_name,
entity_id,
cal_period_id,
currency_code,
balance_type_code,
load_method_code,
currency_type_code,
amount_type_code,
measure_type_code,
notify_options_code,
notification_text,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
object_version_number,
start_time,
end_time,
locked_flag,
most_recent_flag,
associated_request_id,
status_code,
balances_rule_id)
VALUES
(gcs_data_sub_dtls_s.nextval,
gcs_data_sub_dtls_s.nextval,
l_entity_list(i),
l_cal_period_id,
l_curr_code,
'ACTUAL',
l_load_method,
'BASE_CURRENCY',
'ENDING_BALANCE',
'BALANCE',
'N',
NULL,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id,
1,
l_start_date,
sysdate,
'N',
'Y',
l_base_request_id,
l_status,
l_bal_rule_id);
'<< No Entities found for update/insert >>');
UPDATE gcs_data_sub_dtls
SET most_recent_flag = 'N'
WHERE most_recent_flag = 'Y'
AND cal_period_id = l_cal_period_id
AND balance_type_code = 'ADB'
AND entity_id = l_entity_list(i);
' Inserting into gcs_data_sub_dtls for ADB');
INSERT INTO gcs_data_sub_dtls
(load_id,
load_name,
entity_id,
cal_period_id,
currency_code,
balance_type_code,
load_method_code,
currency_type_code,
amount_type_code,
measure_type_code,
notify_options_code,
notification_text,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
object_version_number,
start_time,
end_time,
locked_flag,
most_recent_flag,
associated_request_id,
status_code,
balances_rule_id)
VALUES
(gcs_data_sub_dtls_s.nextval,
gcs_data_sub_dtls_s.nextval,
l_entity_list(i),
l_cal_period_id,
l_curr_code,
'ADB',
l_load_method,
'BASE_CURRENCY',
'ENDING_BALANCE',
'BALANCE',
'N',
NULL,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id,
1,
l_start_date,
sysdate,
'N',
'Y',
l_base_request_id,
l_status,
l_bal_rule_id);
'<< No Entities found for update/insert >>');
SELECT fla.dim_attribute_numeric_member,
fgvcd_local_company.value_set_id,
fgvcd_local_org.value_set_id
INTO l_global_vs_combo_id,
l_company_vs_id,
l_org_vs_id
FROM fem_ledgers_attr fla,
fem_global_vs_combo_defs fgvcd_local_company,
fem_global_vs_combo_defs fgvcd_local_org
WHERE fla.ledger_id = l_ledger_id
AND fla.attribute_id = l_global_vs_combo_attr
AND fla.version_id = l_global_vs_combo_version
AND fla.dim_attribute_numeric_member =
fgvcd_local_company.global_vs_combo_id
AND fgvcd_local_company.dimension_id = 112
AND fla.dim_attribute_numeric_member =
fgvcd_local_org.global_vs_combo_id
AND fgvcd_local_org.dimension_id = 8;
SELECT fgvcd_fch_company.value_set_id,
fgvcd_fch_org.value_set_id
INTO l_fch_company_vs_id,
l_fch_org_vs_id
FROM fem_global_vs_combo_defs fgvcd_fch_company,
fem_global_vs_combo_defs fgvcd_fch_org,
gcs_system_options gso
WHERE fgvcd_fch_company.global_vs_combo_id =
gso.fch_global_vs_combo_id
AND fgvcd_fch_org.global_vs_combo_id =
fgvcd_fch_company.global_vs_combo_id
AND fgvcd_fch_org.dimension_id = 8
AND fgvcd_fch_company.dimension_id = 112;
SELECT fodb.object_definition_id
INTO l_hier_obj_definition_id
FROM fem_xdim_dimensions fxd,
fem_object_definition_b fodb
WHERE fxd.dimension_id = 8
AND fxd.default_mvs_hierarchy_obj_id = fodb.object_id
AND l_cal_period_end_date BETWEEN fodb.effective_start_date AND
fodb.effective_end_date;
SELECT DISTINCT geco.entity_id BULK COLLECT
INTO l_entity_list
FROM fem_companies_b f,
fem_cctr_orgs_hier fcoh,
fem_cctr_orgs_attr fcoa,
gcs_entity_cctr_orgs geco,
gcs_entities_attr gea,
fem_entities_b feb,
fem_cal_periods_attr fcpa
WHERE feb.entity_id = gea.entity_id
AND geco.entity_id = gea.entity_id
AND geco.company_cost_center_org_id = fcoa.company_cost_center_org_id
AND fcoh.hierarchy_obj_def_id = l_hier_obj_definition_id
AND fcoh.parent_value_set_id = l_fch_company_vs_id
AND fcoh.child_value_set_id = l_company_vs_id
AND fcoh.child_id = fcoa.company_cost_center_org_id
AND fcoh.child_value_set_id = fcoa.value_set_id
AND fcoa.attribute_id = l_company_attr
AND fcoa.version_id = l_company_version
AND fcoa.dim_attribute_numeric_member = f.company_id
AND fcoa.value_set_id = f.value_set_id
AND gea.balances_rule_id = l_bal_rule_id
AND gea.source_system_code = 10
AND gea.data_type_code = 'ACTUAL'
AND fcpa.cal_period_id = l_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 feb.enabled_flag = 'Y'
AND f.company_display_code BETWEEN l_bsv_low AND l_bsv_high;
SELECT DISTINCT geo.entity_id BULK COLLECT
INTO l_entity_list
FROM fem_companies_b fcb,
gcs_entity_organizations geo,
gcs_entities_attr gea,
fem_entities_b feb
WHERE feb.entity_id = gea.entity_id
AND geo.entity_id = gea.entity_id
AND feb.enabled_flag = 'Y'
AND gea.balances_rule_id = l_bal_rule_id
AND gea.source_system_code = 10
AND gea.data_type_code = 'ACTUAL'
AND geo.company_cost_center_org_id = fcb.company_id
AND fcb.value_set_id = l_company_vs_id
AND fcb.company_display_code BETWEEN l_bsv_low AND l_bsv_high;
UPDATE gcs_data_sub_dtls
SET most_recent_flag = 'N'
WHERE most_recent_flag = 'Y'
AND cal_period_id = l_cal_period_id
AND balance_type_code = 'ACTUAL'
AND entity_id = l_entity_list(i);
' Inserting gcs_data_sub_dtls for ACTUAL');
INSERT INTO gcs_data_sub_dtls
(load_id,
load_name,
entity_id,
cal_period_id,
currency_code,
balance_type_code,
load_method_code,
currency_type_code,
amount_type_code,
measure_type_code,
notify_options_code,
notification_text,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
object_version_number,
start_time,
end_time,
locked_flag,
most_recent_flag,
associated_request_id,
status_code,
balances_rule_id)
VALUES
(gcs_data_sub_dtls_s.nextval,
gcs_data_sub_dtls_s.nextval,
l_entity_list(i),
l_cal_period_id,
l_curr_code,
'ACTUAL',
l_load_method,
'BASE_CURRENCY',
'ENDING_BALANCE',
'BALANCE',
'N',
NULL,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id,
1,
l_start_date,
sysdate,
'N',
'Y',
l_base_request_id,
l_status,
l_bal_rule_id);
'<< No entities found for update/insert >>');
UPDATE gcs_data_sub_dtls
SET most_recent_flag = 'N'
WHERE most_recent_flag = 'Y'
AND cal_period_id = l_cal_period_id
AND balance_type_code = 'ADB'
AND entity_id = l_entity_list(i);
' Inserting into gcs_data_sub_dtls for ADB');
INSERT INTO gcs_data_sub_dtls
(load_id,
load_name,
entity_id,
cal_period_id,
currency_code,
balance_type_code,
load_method_code,
currency_type_code,
amount_type_code,
measure_type_code,
notify_options_code,
notification_text,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
object_version_number,
start_time,
end_time,
locked_flag,
most_recent_flag,
associated_request_id,
status_code,
balances_rule_id)
VALUES
(gcs_data_sub_dtls_s.nextval,
gcs_data_sub_dtls_s.nextval,
l_entity_list(i),
l_cal_period_id,
l_curr_code,
'ADB',
l_load_method,
'BASE_CURRENCY',
'ENDING_BALANCE',
'BALANCE',
'N',
NULL,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id,
1,
l_start_date,
sysdate,
'N',
'Y',
l_base_request_id,
l_status,
l_bal_rule_id);
'<< No entities found for update/insert >>');
SELECT DISTINCT translated_currency BULK COLLECT
INTO l_xlated_curr_list
FROM fem_dl_trans_curr
WHERE request_id >= l_base_request_id
AND object_id = l_bal_rule_id
AND ledger_id = l_ledger_id
AND cal_period_id = l_cal_period_id;
INSERT INTO gcs_data_sub_dtls
(load_id,
load_name,
entity_id,
cal_period_id,
currency_code,
balance_type_code,
load_method_code,
currency_type_code,
amount_type_code,
measure_type_code,
notify_options_code,
notification_text,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
object_version_number,
start_time,
end_time,
locked_flag,
most_recent_flag,
associated_request_id,
status_code,
balances_rule_id)
SELECT gcs_data_sub_dtls_s.nextval,
gcs_data_sub_dtls_s.nextval,
gdsd.entity_id,
gdsd.cal_period_id,
l_xlated_curr_list(i),
gdsd.balance_type_code,
gdsd.load_method_code,
gdsd.currency_type_code,
gdsd.amount_type_code,
gdsd.measure_type_code,
gdsd.notify_options_code,
gdsd.notification_text,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id,
1,
gdsd.start_time,
sysdate,
gdsd.locked_flag,
gdsd.most_recent_flag,
gdsd.associated_request_id,
gdsd.status_code,
gdsd.balances_rule_id
FROM gcs_data_sub_dtls gdsd
WHERE gdsd.associated_request_id = l_base_request_id
AND gdsd.cal_period_id = l_cal_period_id;
SELECT gdsd.load_id BULK COLLECT
INTO l_generated_load_list
FROM gcs_data_sub_dtls gdsd
WHERE gdsd.associated_request_id = l_base_request_id
AND gdsd.cal_period_id = l_cal_period_id;
program => 'FCH_UPDATE_DATA_STATUS',
sub_request => FALSE,
argument1 => l_generated_load_list(k),
argument2 => NULL,
argument3 => NULL,
argument4 => NULL);
SELECT data_type_code
INTO l_data_type_code
FROM gcs_data_type_codes_b
WHERE source_dataset_code = l_dataset_code
AND rownum < 2;
SELECT source_dataset_code
INTO l_actual_ds_code
FROM gcs_data_type_codes_b
WHERE data_type_code = 'ACTUAL';
SELECT gdsd.load_id,
gdsd.most_recent_flag
BULK COLLECT INTO l_load_list
FROM gcs_entities_attr gea,
gcs_data_sub_dtls gdsd,
fem_cal_periods_attr fcpa
WHERE gea.ledger_id = l_ledger_id
AND gdsd.cal_period_id = l_cal_period_id
AND gdsd.balance_type_code IN ('ACTUAL', 'ADB')
AND gdsd.balance_type_code = gea.data_type_code
AND gdsd.entity_id = gea.entity_id
AND fcpa.cal_period_id = gdsd.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 gdsd.load_id,
gdsd.most_recent_flag
BULK COLLECT INTO l_load_list
FROM gcs_entities_attr gea,
gcs_data_sub_dtls gdsd,
fem_cal_periods_attr fcpa
WHERE gea.ledger_id = l_ledger_id
AND gdsd.cal_period_id = l_cal_period_id
AND gdsd.balance_type_code = l_data_type_code
AND gdsd.balance_type_code = gea.data_type_code
AND gdsd.entity_id = gea.entity_id
AND fcpa.cal_period_id = gdsd.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 ) ;
'<< Update gcs_data_sub_dtls status and >>');
UPDATE gcs_data_sub_dtls
SET status_code = 'UNDONE'
WHERE load_id = l_load_list(k).load_id;
program => 'FCH_UPDATE_DATA_STATUS',
sub_request => FALSE,
argument1 => l_load_list(k).load_id,
argument2 => NULL,
argument3 => NULL,
argument4 => NULL);