[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_phase := 'delete from FII_BUDGET_BASE';
DELETE from FII_BUDGET_BASE
WHERE plan_type_code = FIIBUUP_PURGE_PLAN_TYPE;
g_phase := 'Update FII_CHANGE_LOG';
UPDATE FII_CHANGE_LOG
SET item_value = 'N',
last_update_date = SYSDATE,
last_updated_by = FIIBUUP_USER_ID,
last_update_login = FIIBUUP_LOGIN_ID
WHERE log_item = 'TRUNCATE_BUDGET';
UPDATE FII_CHANGE_LOG
SET item_value = FIIBUUP_BUDGET_TIME_UNIT,
last_update_date = SYSDATE,
last_updated_by = FIIBUUP_USER_ID,
last_update_login = FIIBUUP_LOGIN_ID
WHERE log_item = 'BUDGET_TIME_UNIT';
UPDATE FII_CHANGE_LOG
SET item_value = 'N',
last_update_date = SYSDATE,
last_updated_by = FIIBUUP_USER_ID,
last_update_login = FIIBUUP_LOGIN_ID
WHERE log_item = 'TRUNCATE_FORECAST';
UPDATE FII_CHANGE_LOG
SET item_value = FIIBUUP_FORECAST_TIME_UNIT,
last_update_date = SYSDATE,
last_updated_by = FIIBUUP_USER_ID,
last_update_login = FIIBUUP_LOGIN_ID
WHERE log_item = 'FORECAST_TIME_UNIT';
SELECT report_date_julian
INTO l_purge_time_id
FROM FII_TIME_DAY
WHERE report_date = FIIBUUP_PURGE_DATE;
SELECT ent_period_id
INTO l_purge_time_id
FROM FII_TIME_ENT_PERIOD
WHERE name = FIIBUUP_PURGE_TIME_PERIOD;
SELECT ent_qtr_id
INTO l_purge_time_id
FROM FII_TIME_ENT_QTR
WHERE name = FIIBUUP_PURGE_TIME_PERIOD;
SELECT ent_year_id
INTO l_purge_time_id
FROM FII_TIME_ENT_YEAR
WHERE name = FIIBUUP_PURGE_TIME_PERIOD;
l_sqlstmt := 'UPDATE FII_BUDGET_BASE b ' ||
'SET (b.prim_amount_g, b.sec_amount_g, ' ||
'b.prim_amount_total, b.sec_amount_total, '||
'b.last_update_date, b.last_updated_by, ' ||
'b.last_update_login) = ' ||
'(SELECT (b.prim_amount_g-SUM(b2.prim_amount_g)), ' ||
'(b.sec_amount_g-SUM(b2.sec_amount_g)), '||
'(b.prim_amount_total-SUM(b2.prim_amount_total)), '||
'(b.sec_amount_total-SUM(b2.sec_amount_total)), '||
'SYSDATE, ' ||
':user_id, :login_id ' ||
'FROM FII_BUDGET_BASE b2 ' ||
'WHERE b2.plan_type_code = b.plan_type_code ' ||
'AND b2.ledger_id = b.ledger_id '||
'AND b2.company_id = b.company_id '||
'AND b2.cost_center_id = b.cost_center_id '||
'AND b2.fin_category_id = b.fin_category_id ' ||
'AND NVL(b2.category_id, -1) = NVL(b.category_id, -1) ' ||
'AND b2.user_dim1_id = b.user_dim1_id '||
'AND b2.time_id = :l_purge_time_id ' ||
'AND b2.period_type_id = :l_purge_period_type_id '||
'AND b2.plan_type_code = :plan_type_code '||
'AND NVL(b2.version_date, :global_start_date) = NVL(b.version_date, :global_start_date)) ' ||
'WHERE b.time_id IN ' ;
'(SELECT DECODE(glrm.multiplier, 1, d.week_id, ' ||
'2, d.ent_period_id, 3, d.ent_qtr_id, 4, d.ent_year_id) ' ||
'FROM GL_ROW_MULTIPLIERS glrm, FII_TIME_DAY d ' ||
'WHERE glrm.multiplier BETWEEN 1 AND 4 ' ||
'AND d.report_date_julian = :l_purge_time_id) ';
'(SELECT b3.plan_type_code, ' ||
'b3.ledger_id, '||
'b3.company_id, '||
'b3.cost_center_id, '||
'b3.fin_category_id, ' ||
'NVL(b3.category_id, -1), '||
'b3.user_dim1_id, ' ||
'NVL(b3.version_date, :global_start_date) ' ||
'FROM FII_BUDGET_BASE b3 ' ||
'WHERE b3.plan_type_code = :plan_type_code ' ||
'AND b3.time_id = :l_purge_time_id ' ||
'AND b3.period_type_id = :l_purge_period_type_id) ';
g_phase := 'Delete from FII_BUDGET_BASE';
DELETE from FII_BUDGET_BASE
WHERE plan_type_code = FIIBUUP_PURGE_PLAN_TYPE
AND ( (time_id = l_purge_time_id
AND period_type_id = l_purge_period_type_id)
OR (prim_amount_g = 0));
g_phase := 'delete from FII_BUDGET_BASE';
' DELETE from FII_BUDGET_BASE b'||
' WHERE b.plan_type_code = :plan_type '||
' AND (b.ledger_id, b.company_id, b.cost_center_id, '||
'b.fin_category_id, b.category_id, '||
' b.user_dim1_id) IN '||
' (SELECT b2.ledger_id, b2.company_id, b2.cost_center_id, '||
' b2.fin_category_id, b2.category_id, '||
' b2.user_dim1_id '||
' FROM FII_BUDGET_BASE b2 '||
' WHERE b2.version_date >= trunc(:version_date)) '||
' AND b.version_date >= trunc(:version_date) ';
g_phase := 'Update FII_CHANGE_LOG';
UPDATE FII_CHANGE_LOG
SET item_value = 'N',
last_update_date = SYSDATE,
last_updated_by = FIIBUUP_USER_ID,
last_update_login = FIIBUUP_LOGIN_ID
WHERE log_item = 'TRUNCATE_BUDGET';
UPDATE FII_CHANGE_LOG
SET item_value = FIIBUUP_BUDGET_TIME_UNIT,
last_update_date = SYSDATE,
last_updated_by = FIIBUUP_USER_ID,
last_update_login = FIIBUUP_LOGIN_ID
WHERE log_item = 'BUDGET_TIME_UNIT';
UPDATE FII_CHANGE_LOG
SET item_value = 'N',
last_update_date = SYSDATE,
last_updated_by = FIIBUUP_USER_ID,
last_update_login = FIIBUUP_LOGIN_ID
WHERE log_item = 'TRUNCATE_FORECAST';
UPDATE FII_CHANGE_LOG
SET item_value = FIIBUUP_FORECAST_TIME_UNIT,
last_update_date = SYSDATE,
last_updated_by = FIIBUUP_USER_ID,
last_update_login = FIIBUUP_LOGIN_ID
WHERE log_item = 'FORECAST_TIME_UNIT';
FUNCTION Psi_Insert_Stg RETURN BOOLEAN IS
FIIBUUP_fatal_err EXCEPTION;
FII_MESSAGE.Func_Ent('FII_BUDGET_FORECAST_C.Psi_Insert_Stg');
SELECT DBI_ENABLED_FLAG
INTO l_udd1_enabled_flag
FROM FII_FINANCIAL_DIMENSIONS
WHERE dimension_short_name = 'FII_USER_DEFINED_1';
g_phase := 'PSI Insert Staging';
' INSERT /*+ append parallel(fii_budget_stg)*/ INTO FII_BUDGET_STG'||
' ( plan_type_code, day, year,'||
' ledger_id, company_id, cost_center_id, fin_category_id,category_id, '||
' user_dim1_id, user_dim2_id, prim_amount_g, prim_amount_total, '||
' baseline_amount_prim, posted_date, budget_version_id, '||
' code_combination_id, last_update_date, '||
' last_updated_by, creation_date, created_by, last_update_login ) ' ||
' SELECT /*+ ORDERED use_nl(line) use_hash(fcta) parallel(v1) '||
' parallel(fin) parallel(slga2) '||
' parallel(fslg2) use_hash(v1,line,fin,slga2,fslg2)'||
' swap_join_inputs(fin) '||
' swap_join_inputs(slga2) '||
' swap_join_inputs(fslg2) pq_distribute(fin,none,broadcast)*/'||
' b2.plan_type_code, to_number(to_char(line.effective_date, ''J'')),999,'||
' line.ledger_id, fin.company_id, fin.cost_center_id, '||
' fin.natural_account_id, NVL(fin.prod_category_id, -1), ';
' SELECT /*+ no_merge ordered parallel(jeh) parallel(per) '||
' parallel(fset) use_hash(jeh,per,fset) */ '||
' distinct jeh.ledger_id, jeh.je_header_id, '||
' trunc(p2.start_date) posted_date, '||
' jeh.budget_version_id, jeh.default_effective_date '||
' FROM gl_je_headers jeh, gl_periods p2,'||
' (SELECT /*+ parallel(p) parallel(s) use_hash(s) use_hash(p) */ '||
' p.period_name, s.ledger_id, '||
' b.budget_version_id, '||
' b.base_budget_version_id, '||
' s.period_set_name, s.accounted_period_type '||
' FROM gl_periods p, gl_ledgers_public_v s, '||
' fii_slg_budget_asgns b, fii_source_ledger_groups slg '||
' WHERE slg.usage_code = ''DBI'' '||
' AND b.source_ledger_group_id = slg.source_ledger_group_id '||
' AND s.ledger_id = b.ledger_id '||
' AND p.start_date <= b.to_period_end_date '||
' AND p.end_date >= b.from_period_start_date '||
' AND p.period_set_name = s.period_set_name '||
' AND p.period_type = s.accounted_period_type) per, '||
' (SELECT /*+ full(fslg) parallel(sob) */ DISTINCT '||
' slga.ledger_id, '||
' DECODE(slga.je_rule_set_id, NULL, ''-1'', '||
' rule.JE_SOURCE_NAME) je_source_name, '||
' DECODE(slga.je_rule_set_id, NULL, ''-1'', '||
' rule.JE_CATEGORY_NAME) je_category_name, '||
' slba.budget_version_id, '||
' slba.base_budget_version_id '||
' FROM fii_slg_assignments slga, '||
' gl_je_inclusion_rules rule, '||
' fii_slg_budget_asgns slba, '||
' fii_source_ledger_groups fslg, '||
' gl_ledgers_public_v sob '||
' WHERE slga.je_rule_set_id = rule.je_rule_set_id (+) '||
' AND slga.source_ledger_group_id = fslg.source_ledger_group_id '||
' AND fslg.usage_code = ''DBI'' '||
' AND sob.ledger_id = slga.ledger_id '||
' AND sob.currency_code = :prim_curr '||
' AND slba.ledger_id = slga.ledger_id '||
' AND slba.source_ledger_group_id = slga.source_ledger_group_id) fset'||
' WHERE jeh.ledger_id = fset.ledger_id '||
' AND (jeh.je_source = fset.je_source_name OR fset.je_source_name = ''-1'') '||
' AND (jeh.je_category = fset.je_category_name OR fset.je_category_name = ''-1'') '||
' AND jeh.budget_version_id in (fset.budget_version_id, '||
' fset.base_budget_version_id)'||
' AND jeh.currency_code = :prim_curr '||
' AND jeh.period_name = per.period_name '||
' AND jeh.ledger_id = per.ledger_id '||
' AND jeh.budget_version_id in ( per.budget_version_id, '||
' per.base_budget_version_id) '||
' AND jeh.status = ''P'' '||
' AND jeh.actual_flag = ''B'' '||
' AND p2.period_set_name = per.period_set_name '||
' AND p2.period_type = per.accounted_period_type '||
' AND p2.adjustment_period_flag = ''N'' '||
' AND jeh.posted_date between p2.start_date and p2.end_date '||
' ) v1, '||
' gl_je_lines line, '||
' fii_gl_ccid_dimensions fin, '||
' fii_slg_assignments slga2, '||
' fii_source_ledger_groups fslg2, '||
' fii_slg_budget_asgns b2, '||
' fii_fin_cat_type_assgns fcta '||
' WHERE v1.je_header_id = line.je_header_id '||
' AND line.code_combination_id = fin.code_combination_id '||
' AND ( fin.company_id = slga2.bal_seg_value_id OR'||
' slga2.bal_seg_value_id = -1 ) '||
' AND fin.chart_of_accounts_id = slga2.chart_of_accounts_id '||
' AND line.ledger_id = slga2.ledger_id '||
' AND slga2.source_ledger_group_id = fslg2.source_ledger_group_id '||
' AND b2.source_ledger_group_id = fslg2.source_ledger_group_id '||
' AND v1.default_effective_date between b2.from_period_start_date '||
' and b2.to_period_end_date '||
' AND fslg2.usage_code = ''DBI'' '||
' AND v1.ledger_id = slga2.ledger_id '||
' AND fcta.fin_category_id = fin.natural_account_id '||
' AND fcta.fin_cat_type_code IN (''EXP'', ''R'') '||
' GROUP BY '||
' v1.posted_date, line.effective_date, '||
' fin.company_id, fin.cost_center_id, fin.natural_account_id, '||
' NVL(fin.prod_category_id, -1), ';
v1 => 'FII_BUDGET_FORECAST_C.Psi_Insert_Stg()',
t2 => 'VARIABLE',
v2 => 'LENGTH(l_sqlstmt)',
t3 => 'VALUE',
v3 => TO_CHAR(LENGTH(l_sqlstmt)));
fii_util.put_line('Inserted '||SQL%ROWCOUNT||
' rows into fii_budget_stg');
v1 => 'Psi_Insert_Stg()',
t2 => 'ACTION',
v2 => 'PSI Insert Stage completed successfully...');
FII_MESSAGE.Func_Succ('FII_BUDGET_FORECAST_C.Psi_Insert_Stg');
v1 => 'FII_BUDGET_FORECAST_C.Psi_Insert_Stg()');
(func_name =>'FII_BUDGET_FORECAST_C.Psi_Insert_Stg');
v1 => 'FII_BUDGET_FORECAST_C.Psi_Insert_Stg()',
t2 => 'SQLERRMC',
v2 => SQLERRM);
(func_name => 'FII_BUDGET_FORECAST_C.Psi_Insert_Stg');
END Psi_Insert_Stg;
SELECT DBI_ENABLED_FLAG
INTO l_udd1_enabled_flag
FROM FII_FINANCIAL_DIMENSIONS
WHERE dimension_short_name = 'FII_USER_DEFINED_1';
' INSERT /*+ append parallel(fii_budget_stg)*/ INTO FII_BUDGET_STG'||
' ( plan_type_code, day, year,'||
' ledger_id, company_id, cost_center_id, fin_category_id,category_id, '||
' user_dim1_id, user_dim2_id, prim_amount_g, prim_amount_total, '||
' baseline_amount_prim, posted_date, budget_version_id, '||
' code_combination_id, last_update_date, '||
' last_updated_by, creation_date, created_by, last_update_login ) ' ||
' SELECT /*+ parallel(sob) parallel(p) pq_distribute(p hash,hash) '||
' parallel(slga) use_hash(fslg,b,fin) parallel(b) '||
' pq_distribute(b hash,hash) '||
' parallel(fin) parallel(fcta) pq_distribute(fin hash,hash) */'||
' slba.plan_type_code, to_number(to_char(p.start_date, ''J'')), 999,'||
' b.ledger_id, fin.company_id, fin.cost_center_id, '||
' fin.natural_account_id, NVL(fin.prod_category_id, -1), ';
fii_util.put_line('Inserted '||SQL%ROWCOUNT||
' rows into fii_budget_stg');
' INSERT /*+ append parallel(fii_budget_stg) */ INTO fii_budget_stg '||
' ( plan_type_code, period, quarter, year, ledger_id, '||
' company_id, cost_center_id, fin_category_id, category_id, '||
' user_dim1_id, user_dim2_id, prim_amount_g, '||
' prim_amount_total, baseline_amount_prim, '||
' posted_date, code_combination_id, budget_version_id, last_update_date, '||
' last_updated_by, creation_date, created_by, last_update_login) '||
' SELECT * FROM ( '||
' SELECT /*+ parallel(b) parallel(fday) use_hash(fday) */ '||
' b.plan_type_code, '||
' fday.ent_period_id, fday.ent_qtr_id, fday.ent_year_id, '||
' b.ledger_id, b.company_id, b.cost_center_id, '||
' b.fin_category_id, b.category_id, b.user_dim1_id, '||
' b.user_dim2_id, '||
' SUM(b.prim_amount_g) prim_amount_g, '||
' SUM(b.prim_amount_total) prim_amount_total, '||
' SUM(b.baseline_amount_prim )baseline_amount_prim, '||
' b.posted_date, b.code_combination_id, b.budget_version_id, '||
' b.last_update_date, b.last_updated_by, '||
' b.creation_date, b.created_by, b.last_update_login '||
' FROM fii_budget_stg b, '||
' fii_time_day fday '||
' WHERE b.day = fday.report_date_julian '||
' GROUP BY '||
' b.plan_type_code, b.ledger_id, b.company_id, b.cost_center_id, '||
' b.fin_category_id, b.category_id, b.user_dim1_id, b.user_dim2_id,'||
' b.posted_date, b.code_combination_id, b.budget_version_id, '||
' b.last_update_date, b.last_updated_by, '||
' b.creation_date, b.created_by, b.last_update_login, '||
' ROLLUP (fday.ent_year_id, '||
' fday.ent_qtr_id, '||
' fday.ent_period_id )) '||
' WHERE ent_year_id IS NOT NULL ';
fii_util.put_line('Inserted '||SQL%ROWCOUNT||
' rows into fii_budget_stg');
FUNCTION Psi_DeleteDiff RETURN BOOLEAN IS
FIIBUUP_fatal_err EXCEPTION;
FII_MESSAGE.Func_Ent('FII_BUDGET_FORECAST_C.Psi_DeleteDiff');
g_phase := 'PSI DeleteDiff';
' DELETE FROM fii_budget_base '||
' WHERE ( plan_type_code, '||
' time_id, '||
' period_type_id, '||
' ledger_id, '||
' company_id, '||
' cost_center_id, '||
' NVL(company_cost_center_org_id, -1), '||
' fin_category_id, '||
' category_id, '||
' user_dim1_id, '||
' user_dim2_id, '||
' posted_date, '||
' prim_amount_g, '||
' baseline_amount_prim) '||
' IN (SELECT plan_type_code, '||
' time_id, '||
' period_type_id, '||
' ledger_id, '||
' company_id, '||
' cost_center_id, '||
' NVL(company_cost_center_org_id, -1), '||
' fin_category_id, '||
' category_id, '||
' user_dim1_id, '||
' user_dim2_id, '||
' posted_date, '||
' prim_amount_g, '||
' baseline_amount_prim '||
' FROM fii_budget_base '||
' MINUS '||
' SELECT plan_type_code, '||
' nvl(day, nvl(period, nvl(quarter, year))), '||
' decode(day, null, '||
' decode(period, null, '||
' decode(quarter, null, 128, 64), 32), 1), '||
' ledger_id, '||
' company_id, '||
' cost_center_id, '||
' NVL(company_cost_center_org_id, -1), '||
' fin_category_id, '||
' category_id, '||
' user_dim1_id, '||
' user_dim2_id, '||
' posted_date, '||
' sum(prim_amount_g), '||
' sum(baseline_amount_prim) '||
' FROM fii_budget_stg '||
' GROUP BY plan_type_code, '||
' nvl(day, nvl(period, nvl(quarter, year))), '||
' decode(day, null, '||
' decode(period, null, '||
' decode(quarter, null, 128, 64), 32), 1), '||
' ledger_id, '||
' company_id, '||
' cost_center_id, '||
' NVL(company_cost_center_org_id, -1), '||
' fin_category_id, '||
' category_id, '||
' user_dim1_id, '||
' user_dim2_id, '||
' posted_date) ';
v1 => 'FII_BUDGET_FORECAST_C.Psi_DeleteDiff()',
t2 => 'VARIABLE',
v2 => 'LENGTH(l_sqlstmt)',
t3 => 'VALUE',
v3 => TO_CHAR(LENGTH(l_sqlstmt)));
fii_util.put_line('Deleted '||SQL%ROWCOUNT||
' rows from fii_budget_base');
v1 => 'Psi_DeleteDiff()',
t2 => 'ACTION',
v2 => 'PSI DeleteDiff completed successfully...');
FII_MESSAGE.Func_Succ('FII_BUDGET_FORECAST_C.Psi_DeleteDiff');
v1 => 'FII_BUDGET_FORECAST_C.Psi_DeleteDiff()');
(func_name =>'FII_BUDGET_FORECAST_C.Psi_DeleteDiff');
v1 => 'FII_BUDGET_FORECAST_C.Psi_DeleteDiff()',
t2 => 'SQLERRMC',
v2 => SQLERRM);
(func_name => 'FII_BUDGET_FORECAST_C.Psi_DeleteDiff');
END Psi_DeleteDiff;
FUNCTION Psi_Insert_Base RETURN BOOLEAN IS
FIIBUUP_fatal_err EXCEPTION;
FII_MESSAGE.Func_Ent('FII_BUDGET_FORECAST_C.Psi_Insert_Base');
g_phase := 'PSI Insert Base';
' INSERT /*+ append parallel(b)*/ INTO fii_budget_base b '||
' ( plan_type_code, time_id, period_type_id, '||
' prim_amount_g, prim_amount_total, '||
' baseline_amount_prim, ledger_id, company_cost_center_org_id, '||
' company_id, cost_center_id, fin_category_id, category_id, '||
' user_dim1_id, user_dim2_id, posted_date, '||
' creation_date, created_by, last_update_date, '||
' last_updated_by, last_update_login, version_date ) '||
' SELECT /*+ parallel(stg) */ plan_type_code, '||
' nvl(day, nvl(period, nvl(quarter, year))), '||
' decode(day, null, '||
' decode(period, null, '||
' decode(quarter, null, 128, 64), 32), 1), '||
' sum(prim_amount_g), sum(prim_amount_total), '||
' sum(baseline_amount_prim), '||
' ledger_id, '||
' company_cost_center_org_id, '||
' company_id, '||
' cost_center_id, '||
' fin_category_id, '||
' category_id, '||
' user_dim1_id, '||
' user_dim2_id, '||
' trunc(posted_date), '||
' sysdate, :user_id, sysdate, :user_id, :login_id, '||
' :ver_date '||
' FROM fii_budget_stg '||
' GROUP BY plan_type_code, '||
' nvl(day, nvl(period, nvl(quarter, year))), '||
' decode(day, null, '||
' decode(period, null, '||
' decode(quarter, null, 128, 64), 32), 1), '||
' ledger_id, '||
' company_cost_center_org_id, '||
' company_id, '||
' cost_center_id, '||
' fin_category_id, '||
' category_id, '||
' user_dim1_id, '||
' user_dim2_id, '||
' posted_date '||
' MINUS '||
'SELECT /*+ parallel(b1) */ plan_type_code, '||
' time_id, '||
' period_type_id, '||
' prim_amount_g, '||
' prim_amount_total, '||
' baseline_amount_prim, '||
' ledger_id, '||
' company_cost_center_org_id, '||
' company_id, '||
' cost_center_id, '||
' fin_category_id, '||
' category_id, '||
' user_dim1_id, '||
' user_dim2_id, '||
' trunc(posted_date), '||
' sysdate, :user_id, sysdate, :user_id, :login_id, '||
' :ver_date '||
' FROM fii_budget_base ';
v1 => 'FII_BUDGET_FORECAST_C.Psi_Insert_Base()',
t2 => 'VARIABLE',
v2 => 'LENGTH(l_sqlstmt)',
t3 => 'VALUE',
v3 => TO_CHAR(LENGTH(l_sqlstmt)));
fii_util.put_line('Inserted '||SQL%ROWCOUNT||
' rows into fii_budget_base');
v1 => 'Psi_Insert_Base()',
t2 => 'ACTION',
v2 => 'PSI Merge completed successfully...');
FII_MESSAGE.Func_Succ('FII_BUDGET_FORECAST_C.Psi_Insert_Base');
v1 => 'FII_BUDGET_FORECAST_C.Psi_Insert_Base()');
(func_name =>'FII_BUDGET_FORECAST_C.Psi_Insert_Base');
v1 => 'FII_BUDGET_FORECAST_C.Psi_Insert_Base()',
t2 => 'SQLERRMC',
v2 => SQLERRM);
(func_name => 'FII_BUDGET_FORECAST_C.Psi_Insert_Base');
END Psi_Insert_Base;
SELECT DISTINCT sob.name, sob.currency_code
FROM fii_slg_assignments slga,
fii_source_ledger_groups fslg,
gl_ledgers_public_v sob
WHERE sob.ledger_id = slga.ledger_id
AND slga.source_ledger_group_id = fslg.source_ledger_group_id
AND fslg.usage_code = 'DBI'
AND sob.currency_code NOT IN (global_prim_curr);
SELECT /*+ parallel(bud) parallel(stg3) */
DISTINCT acct.concatenated_segments,
bud.budget_name
FROM gl_code_combinations_kfv acct,
gl_budget_versions bud,
(SELECT code_combination_id, count(*)
FROM (SELECT /*+ parallel(stg) */ stg.code_combination_id,
stg.budget_version_id
FROM fii_budget_stg stg
GROUP BY stg.code_combination_id, stg.budget_version_id)
GROUP BY code_combination_id
HAVING COUNT(*) > 1) stg2,
fii_budget_stg stg3
WHERE acct.code_combination_id = stg2.code_combination_id
AND stg3.code_combination_id = stg2.code_combination_id
AND bud.budget_version_id = stg3.budget_version_id
ORDER BY acct.concatenated_segments, bud.budget_name;
SELECT distinct sob.name from (
SELECT nvl(day, nvl(period, nvl(quarter, year))) time_id,
decode(day, null, decode(period, null,
decode(quarter, null, 128, 64), 32), 1) period_type_id,
ledger_id, company_id, cost_center_id, fin_category_id,
category_id, user_dim1_id, user_dim2_id, posted_date,
baseline_amount_prim
FROM fii_budget_stg
MINUS
SELECT time_id, period_type_id,
ledger_id, company_id, cost_center_id, fin_category_id,
category_id, user_dim1_id, user_dim2_id, posted_date,
baseline_amount_prim
FROM fii_budget_base) v,
gl_ledgers_public_v sob
WHERE sob.ledger_id = v.ledger_id;
SELECT DISTINCT NVL(cccorg.ccc_org_id, -1),
stg.company_id, stg.cost_center_id
FROM FII_CCC_MGR_GT cccorg,
FII_BUDGET_STG stg
WHERE cccorg.company_id (+) = stg.company_id
AND cccorg.cost_center_id (+) = stg.cost_center_id;
IF (NOT FII_BUDGET_FORECAST_C.Psi_Insert_Stg) THEN
raise FIIBUUP_fatal_err;
IF (NOT FII_BUDGET_FORECAST_C.Psi_DeleteDiff) THEN
raise FIIBUUP_fatal_err;
UPDATE fii_budget_stg stg
SET stg.company_cost_center_org_id = l_ccc_org_rec.l_ccc_org_id(i)
WHERE stg.company_id = l_ccc_org_rec.l_com_id(i)
AND stg.cost_center_id = l_ccc_org_rec.l_cc_id(i);
IF (NOT FII_BUDGET_FORECAST_C.Psi_Insert_Base) THEN
raise FIIBUUP_fatal_err;
SELECT 1
INTO l_row_exists
FROM fii_budget_base
WHERE rownum = 1;
SELECT DISTINCT nvl(f.ledger_id, i.ledger_id),
nvl(i.ledger, '-1')
FROM FII_BUDGET_INTERFACE i,
(SELECT DISTINCT sob.ledger_id, int.ledger
FROM FII_BUDGET_INTERFACE int,
GL_LEDGERS_PUBLIC_V sob
WHERE (sob.ledger_id = int.ledger_id OR
sob.name = int.ledger)) f
WHERE i.ledger = f.ledger(+);
SELECT DISTINCT nvl(f.flex_value_id, i.company_id),
nvl(i.company, '-1'), f.ledger_id
FROM FII_BUDGET_INTERFACE i,
(SELECT DISTINCT fv.flex_value_id, int.company, int.ledger_id
FROM FII_BUDGET_INTERFACE int,
FII_DIM_MAPPING_RULES r,
FND_FLEX_VALUES fv,
GL_LEDGERS_PUBLIC_V sob
WHERE r.dimension_short_name = 'FII_COMPANIES'
AND r.chart_of_accounts_id = sob.chart_of_accounts_id
AND (sob.ledger_id = int.ledger_id OR
sob.name = int.ledger)
AND fv.flex_value_set_id = r.FLEX_VALUE_SET_ID1
AND int.company = fv.flex_value) f
WHERE i.company = f.company(+);
SELECT DISTINCT nvl(f.flex_value_id, i.cost_center_id),
nvl(i.cost_center, '-1'), f.ledger_id
FROM FII_BUDGET_INTERFACE i,
(SELECT DISTINCT fv.flex_value_id, int.cost_center, int.ledger_id
FROM FII_BUDGET_INTERFACE int,
FII_DIM_MAPPING_RULES r,
FND_FLEX_VALUES fv,
GL_LEDGERS_PUBLIC_V sob
WHERE r.dimension_short_name = 'HRI_CL_ORGCC'
AND r.chart_of_accounts_id = sob.chart_of_accounts_id
AND (sob.ledger_id = int.ledger_id OR
sob.name = int.ledger)
AND fv.flex_value_set_id = r.FLEX_VALUE_SET_ID1
AND int.cost_center = fv.flex_value) f
WHERE i.cost_center = f.cost_center(+);
SELECT DISTINCT NVL(cccorg.ccc_org_id, -1),
int.company_id, int.cost_center_id
FROM FII_CCC_MGR_GT cccorg,
FII_BUDGET_INTERFACE int
WHERE cccorg.company_id (+) = int.company_id
AND cccorg.cost_center_id (+) = int.cost_center_id
AND int.company_cost_center_org_id IS NULL;
SELECT DISTINCT nvl(f.flex_value_id, i.fin_category_id),
nvl(i.fin_item, '-1'), f.ledger_id
FROM FII_BUDGET_INTERFACE i,
(SELECT DISTINCT fv.flex_value_id, int.fin_item, int.ledger_id
FROM FII_DIM_MAPPING_RULES r,
FII_BUDGET_INTERFACE int,
FND_FLEX_VALUES fv,
GL_LEDGERS_PUBLIC_V sob
WHERE r.dimension_short_name = 'GL_FII_FIN_ITEM'
AND r.chart_of_accounts_id = sob.chart_of_accounts_id
AND (sob.ledger_id = int.ledger_id OR
sob.name = int.ledger)
AND fv.flex_value_set_id = r.FLEX_VALUE_SET_ID1
AND int.fin_item = fv.flex_value) f
WHERE i.fin_item = f.fin_item(+);
SELECT DISTINCT nvl(f.flex_value_id, FIIBUUP_UNASSIGNED_UDD_ID),
nvl(i.user_dim1, 'UNASSIGNED'), f.ledger_id
FROM FII_BUDGET_INTERFACE i,
(SELECT DISTINCT fv.flex_value_id, int.user_dim1 , int.ledger_id
FROM FII_DIM_MAPPING_RULES r,
FII_BUDGET_INTERFACE int,
FND_FLEX_VALUES fv,
GL_LEDGERS_PUBLIC_V sob
WHERE r.dimension_short_name = 'FII_USER_DEFINED_1'
AND r.chart_of_accounts_id = sob.chart_of_accounts_id
AND (sob.ledger_id = int.ledger_id OR
sob.name = int.ledger)
AND fv.flex_value_set_id = r.flex_value_set_id1
AND int.user_dim1 = fv.flex_value) f
WHERE i.user_dim1 = f.user_dim1(+);
SELECT DISTINCT m.category_id, int.product_code
FROM mtl_categories_tl m, fii_budget_interface int
WHERE m.description = int.product_code
AND m.language = userenv('LANG');
UPDATE fii_budget_interface int
SET int.ledger_id = l_id_val_rec.l_id(i)
WHERE int.ledger = l_id_val_rec.l_value(i);
UPDATE fii_budget_interface int
SET int.company_id = l_id_val_rec.l_id(i)
WHERE int.company = l_id_val_rec.l_value(i)
AND int.ledger_id = l_id_val_rec.l_ledger_id(i);
UPDATE fii_budget_interface int
SET int.cost_center_id = l_id_val_rec.l_id(i)
WHERE int.cost_center = l_id_val_rec.l_value(i)
AND int.ledger_id = l_id_val_rec.l_ledger_id(i);
UPDATE fii_budget_interface int
SET int.company_cost_center_org_id = l_ccc_org_rec.l_ccc_org_id(i)
WHERE int.company_id = l_ccc_org_rec.l_com_id(i)
AND int.cost_center_id = l_ccc_org_rec.l_cc_id(i);
UPDATE fii_budget_interface int
SET int.fin_category_id = l_id_val_rec.l_id(i)
WHERE int.fin_item = l_id_val_rec.l_value(i)
AND int.ledger_id = l_id_val_rec.l_ledger_id(i);
SELECT DBI_ENABLED_FLAG
INTO l_udd1_enabled_flag
FROM FII_FINANCIAL_DIMENSIONS
WHERE dimension_short_name = 'FII_USER_DEFINED_1';
UPDATE fii_budget_interface int
SET (int.user_dim1_id, int.user_dim1,
int.user_dim2_id, int.user_dim2) =
(SELECT l_id_val_rec.l_id(i), l_id_val_rec.l_value(i),
FIIBUUP_UNASSIGNED_UDD_ID, 'UNASSIGNED' from dual)
WHERE NVL(int.user_dim1, 'UNASSIGNED') = l_id_val_rec.l_value(i)
AND int.ledger_id = l_id_val_rec.l_ledger_id(i);
UPDATE fii_budget_interface int
SET (int.user_dim1_id, int.user_dim1, int.user_dim2_id, int.user_dim2) =
(SELECT FIIBUUP_UNASSIGNED_UDD_ID, 'UNASSIGNED',
FIIBUUP_UNASSIGNED_UDD_ID, 'UNASSIGNED' from dual);
UPDATE fii_budget_interface int
SET int.prod_category_id = l_id_val_rec.l_id(i)
WHERE int.product_code = l_id_val_rec.l_value(i);
SELECT plan_type_code, prim_amount_g
FROM FII_BUDGET_INTERFACE
WHERE plan_type_code NOT IN ('B', 'F')
FOR UPDATE OF STATUS_CODE;
SELECT plan_type_code, prim_amount_g
FROM FII_BUDGET_INTERFACE
WHERE report_time_period IS NULL
FOR UPDATE OF STATUS_CODE;
SELECT plan_type_code, prim_amount_g
FROM FII_BUDGET_INTERFACE
WHERE plan_type_code = 'B'
AND report_time_period IS NULL
FOR UPDATE OF STATUS_CODE;
SELECT plan_type_code, prim_amount_g
FROM FII_BUDGET_INTERFACE
WHERE plan_type_code = 'F'
AND report_time_period IS NULL
FOR UPDATE OF STATUS_CODE;
SELECT plan_type_code, prim_amount_g
FROM FII_BUDGET_INTERFACE
WHERE ledger IS NULL
AND ledger_id IS NULL
FOR UPDATE OF STATUS_CODE;
SELECT b.ledger, b.ledger_id
FROM FII_BUDGET_INTERFACE b
WHERE (b.ledger IS NOT NULL AND b.ledger_id IS NULL) OR
(nvl(b.ledger_id, -1) NOT IN (
SELECT ledger_id
FROM FII_SLG_ASSIGNMENTS))
FOR UPDATE OF STATUS_CODE;
SELECT plan_type_code, prim_amount_g
FROM FII_BUDGET_INTERFACE
WHERE company IS NULL
AND company_id IS NULL
FOR UPDATE OF STATUS_CODE;
SELECT b.company, b.company_id
FROM FII_BUDGET_INTERFACE b
WHERE (b.company IS NOT NULL AND b.company_id IS NULL) OR
NOT EXISTS (
SELECT '1'
FROM FND_FLEX_VALUES fv,
FND_ID_FLEX_SEGMENTS fs,
FII_DIM_MAPPING_RULES mr,
FND_SEGMENT_ATTRIBUTE_VALUES b
WHERE fs.application_id = 101
AND fs.id_flex_code = 'GL#'
AND fs.id_flex_num = mr.chart_of_accounts_id
-- AND fs.application_column_name = mr.application_column_name1
-- AND mr.dimension_short_name = 'FII_COMPANIES'
AND fs.application_id = b.application_id
AND fs.id_flex_code = b.id_flex_code
AND fs.id_flex_num = b.id_flex_num
AND fs.application_column_name = b.application_column_name
AND b.attribute_value = 'Y'
AND b.segment_attribute_type = 'GL_BALANCING'
AND fv.flex_value_set_id = fs.flex_value_set_id
AND fv.summary_flag = 'N'
AND (nvl(b.company_id, -1) = fv.flex_value_id ))
FOR UPDATE OF STATUS_CODE;
SELECT plan_type_code, prim_amount_g
FROM FII_BUDGET_INTERFACE
WHERE cost_center IS NULL
AND cost_center_id IS NULL
FOR UPDATE OF STATUS_CODE;
SELECT b.cost_center, b.cost_center_id
FROM FII_BUDGET_INTERFACE b
WHERE (b.cost_center IS NOT NULL and b.cost_center_id IS NULL) OR
NOT EXISTS (
SELECT '1'
FROM FND_FLEX_VALUES fv,
FND_ID_FLEX_SEGMENTS fs,
FII_DIM_MAPPING_RULES mr,
FND_SEGMENT_ATTRIBUTE_VALUES b
WHERE fs.application_id = 101
AND fs.id_flex_code = 'GL#'
AND fs.id_flex_num = mr.chart_of_accounts_id
-- AND fs.application_column_name = mr.application_column_name1
-- AND mr.dimension_short_name = 'HRI_CL_ORGCC'
AND fs.application_id = b.application_id
AND fs.id_flex_code = b.id_flex_code
AND fs.id_flex_num = b.id_flex_num
AND fs.application_column_name = b.application_column_name
AND b.attribute_value = 'Y'
AND b.segment_attribute_type = 'FA_COST_CTR'
AND fv.flex_value_set_id = fs.flex_value_set_id
AND fv.summary_flag = 'N'
AND (nvl(b.cost_center_id, -1) = fv.flex_value_id ))
FOR UPDATE OF STATUS_CODE;
SELECT plan_type_code, prim_amount_g
FROM FII_BUDGET_INTERFACE
WHERE fin_item IS NULL
AND fin_category_id IS NULL
FOR UPDATE OF STATUS_CODE;
SELECT b.fin_item, b.fin_category_id
FROM FII_BUDGET_INTERFACE b
WHERE (b.fin_item IS NOT NULL AND b.fin_category_id IS NULL) OR
NOT EXISTS (
SELECT '1'
FROM fnd_flex_values fv,
fnd_id_flex_segments fs,
fnd_segment_attribute_values b,
fii_dim_mapping_rules mr
WHERE fs.application_id = 101
AND fs.id_flex_code = 'GL#'
AND fs.id_flex_num = mr.chart_of_accounts_id
-- AND fs.application_column_name = mr.application_column_name1
-- AND mr.dimension_short_name = 'GL_FII_FIN_ITEM'
AND fs.application_id = b.application_id
AND fs.id_flex_code = b.id_flex_code
AND fs.id_flex_num = b.id_flex_num
AND fs.application_column_name = b.application_column_name
AND b.attribute_value = 'Y'
AND b.segment_attribute_type = 'GL_ACCOUNT'
AND fv.flex_value_set_id = fs.flex_value_set_id
AND (nvl(b.fin_category_id, -1) = fv.flex_value_id))
FOR UPDATE OF b.STATUS_CODE;
SELECT b.product_code, b.prod_category_id
FROM FII_BUDGET_INTERFACE b
WHERE (b.prod_category_id IS NULL AND b.product_code IS NOT NULL) OR
(b.prod_category_id NOT IN
(SELECT mck.category_id
FROM MTL_CATEGORIES_KFV mck
WHERE mck.structure_id = p_mtc_structure_id))
FOR UPDATE OF b.STATUS_CODE;
SELECT b.user_dim1, b.user_dim1_id
FROM FII_BUDGET_INTERFACE b
WHERE (b.user_dim1 IS NOT NULL AND b.user_dim1_id IS NULL) OR
(nvl(b.user_dim1_id, -1) NOT IN (
SELECT fv.flex_value_id
FROM fnd_flex_values fv,
fnd_id_flex_segments fs,
fii_dim_mapping_rules mr
WHERE fs.application_id = 101
AND fs.id_flex_code = 'GL#'
AND fs.id_flex_num = mr.chart_of_accounts_id
AND fs.application_column_name = mr.application_column_name1
AND mr.dimension_short_name = 'FII_USER_DEFINED_1'
AND fv.flex_value_set_id = fs.flex_value_set_id
UNION
SELECT FIIBUUP_UNASSIGNED_UDD_ID from DUAL))
FOR UPDATE OF b.STATUS_CODE;
SELECT plan_type_code, prim_amount_g
FROM FII_BUDGET_INTERFACE
WHERE FIIBUUP_SEC_CURR_CODE is NOT NULL
AND sec_amount_g is NULL
AND (conversion_rate is NULL OR conversion_rate <= 0)
FOR UPDATE OF STATUS_CODE;
SELECT plan_type_code, trunc(version_date),
report_time_period,
ledger_id, company_id, cost_center_id, fin_category_id,
prod_category_id, user_dim1_id
FROM FII_BUDGET_INTERFACE
GROUP BY plan_type_code, trunc(version_date),
report_time_period,
ledger_id, company_id, cost_center_id, fin_category_id,
prod_category_id, user_dim1_id
HAVING count(prim_amount_g) > 1
ORDER BY 1;
SELECT i.plan_type_code, trunc(i.version_date),
i.report_time_period, i.ledger_id,
i.company_id, i.cost_center_id, i.fin_category_id,
i.prod_category_id, i.user_dim1_id
FROM FII_BUDGET_INTERFACE i
WHERE ((trunc(i.version_date) < FIIBUUP_GLOBAL_START_DATE) OR
(trunc(i.version_date) <
(SELECT MAX(b.version_date)
FROM FII_BUDGET_BASE b, FII_TIME_ENT_PERIOD p
WHERE p.name = i.report_time_period
AND b.time_id = p.ent_period_id
AND b.ledger_id = i.ledger_id
AND b.company_id = i.company_id
AND b.cost_center_id = i.cost_center_id
AND b.fin_category_id = i.fin_category_id
AND NVL(b.category_id, -1) = NVL(i.prod_category_id, -1)
AND b.user_dim1_id = i.user_dim1_id
AND trunc(b.upload_date) <> trunc(i.upload_date))))
GROUP BY i.plan_type_code, trunc(i.version_date), i.report_time_period,
i.ledger_id, i.company_id, i.cost_center_id, i.fin_category_id,
i.prod_category_id, i.user_dim1_id;
SELECT i.plan_type_code, trunc(i.version_date),
i.report_time_period, i.ledger_id,
i.company_id, i.cost_center_id, i.fin_category_id,
i.prod_category_id, i.user_dim1_id
FROM FII_BUDGET_INTERFACE i
WHERE ((trunc(i.version_date) < FIIBUUP_GLOBAL_START_DATE) OR
(trunc(i.version_date) <
(SELECT MAX(b.version_date)
FROM FII_BUDGET_BASE b, FII_TIME_ENT_QTR q
WHERE q.name = i.report_time_period
AND b.time_id = q.ent_qtr_id
AND b.ledger_id = i.ledger_id
AND b.company_id = i.company_id
AND b.cost_center_id = i.cost_center_id
AND b.fin_category_id = i.fin_category_id
AND NVL(b.category_id, -1) = NVL(i.prod_category_id, -1)
AND b.user_dim1_id = i.user_dim1_id
AND trunc(b.upload_date) <> trunc(i.upload_date))))
GROUP BY i.plan_type_code, trunc(i.version_date), i.report_time_period,
i.ledger_id, i.company_id, i.cost_center_id, i.fin_category_id,
i.prod_category_id, i.user_dim1_id;
SELECT i.plan_type_code, trunc(i.version_date),
i.report_time_period, i.ledger_id,
i.company_id, i.cost_center_id, i.fin_category_id,
i.prod_category_id, i.user_dim1_id
FROM FII_BUDGET_INTERFACE i
WHERE ((trunc(i.version_date) < FIIBUUP_GLOBAL_START_DATE) OR
(trunc(i.version_date) <
(SELECT MAX(b.version_date)
FROM FII_BUDGET_BASE b, FII_TIME_ENT_YEAR y
WHERE y.name = i.report_time_period
AND b.time_id = y.ent_year_id
AND b.ledger_id = i.ledger_id
AND b.company_id = i.company_id
AND b.cost_center_id = i.cost_center_id
AND b.fin_category_id = i.fin_category_id
AND NVL(b.category_id, -1) = NVL(i.prod_category_id, -1)
AND b.user_dim1_id = i.user_dim1_id
AND trunc(b.upload_date) <> trunc(i.upload_date))))
GROUP BY i.plan_type_code, trunc(i.version_date), i.report_time_period,
i.ledger_id, i.company_id, i.cost_center_id, i.fin_category_id,
i.prod_category_id, i.user_dim1_id;
g_phase := 'Delete from FII_BUDGET_INTERFACE for not-null status_code';
delete from FII_BUDGET_INTERFACE
where status_code is not NULL;
g_phase := 'Update all other records to status_code = VALIDATED and upload_date = sysdate';
update FII_BUDGET_INTERFACE
set (status_code, upload_date) = (SELECT 'VALIDATED', sysdate from dual);
UPDATE FII_BUDGET_INTERFACE
SET status_code = 'ERROR'
WHERE CURRENT OF planCursor;
UPDATE FII_BUDGET_INTERFACE
SET Status_Code = 'ERROR'
WHERE CURRENT OF csr_null_time_1;
UPDATE FII_BUDGET_INTERFACE
SET Status_Code = 'ERROR'
WHERE CURRENT OF csr_null_time_2;
UPDATE FII_BUDGET_INTERFACE
SET Status_Code = 'ERROR'
WHERE CURRENT OF csr_null_time_3;
l_sqlstmt := 'SELECT p1.report_time_period TIME ' ||
'FROM (SELECT distinct report_time_period ' ||
' FROM FII_BUDGET_INTERFACE' ||
' WHERE report_time_period is not NULL';
Update FII_BUDGET_INTERFACE
Set Status_Code = 'ERROR'
Where report_time_period = l_time_unit;
l_sqlstmt := 'SELECT p2.report_time_period TIME ' ||
'FROM (SELECT distinct report_time_period ' ||
' FROM FII_BUDGET_INTERFACE ' ||
' WHERE report_time_period is not NULL ' ||
' AND plan_type_code = ''F'') p2, ';
Update FII_BUDGET_INTERFACE
Set Status_Code = 'ERROR'
Where report_time_period = l_time_unit;
UPDATE FII_BUDGET_INTERFACE
SET status_code = 'ERROR'
WHERE CURRENT OF csr_null_ledger_id;
UPDATE FII_BUDGET_INTERFACE
SET status_code = 'ERROR'
WHERE CURRENT OF csr_null_com_id;
UPDATE FII_BUDGET_INTERFACE
SET status_code = 'ERROR'
WHERE CURRENT OF csr_null_cc_id;
UPDATE FII_BUDGET_INTERFACE
SET Status_Code = 'ERROR'
WHERE CURRENT OF csr_null_fin_cat_id;
SELECT 1
INTO l_err_count
FROM FII_BUDGET_INTERFACE
WHERE status_code = 'ERROR'
AND rownum = 1;
UPDATE FII_BUDGET_INTERFACE
SET status_code = 'ERROR'
WHERE CURRENT OF ledgerCursor;
UPDATE FII_BUDGET_INTERFACE
SET status_code = 'ERROR'
WHERE CURRENT OF comCursor;
UPDATE FII_BUDGET_INTERFACE
SET status_code = 'ERROR'
WHERE CURRENT OF ccCursor;
UPDATE FII_BUDGET_INTERFACE
SET status_code = 'ERROR'
WHERE CURRENT OF fincatCursor;
SELECT DBI_ENABLED_FLAG
INTO l_pcat_enabled_flag
FROM FII_FINANCIAL_DIMENSIONS
WHERE dimension_short_name = 'ENI_ITEM_VBH_CAT';
SELECT structure_id INTO p_mtc_structure_id
FROM MTL_CATEGORY_SETS_VL
WHERE category_set_id = ENI_DENORM_HRCHY.get_category_set_id;
UPDATE FII_BUDGET_INTERFACE
SET Status_Code = 'ERROR'
WHERE CURRENT OF prodCursor;
UPDATE FII_BUDGET_INTERFACE
SET status_code = 'ERROR'
WHERE CURRENT OF udd1Cursor;
UPDATE FII_BUDGET_INTERFACE
SET Status_Code = 'ERROR'
WHERE CURRENT OF rateCursor;
UPDATE FII_BUDGET_INTERFACE
SET status_code = 'ERROR'
WHERE plan_type_code = l_plan_code
AND ledger_id = l_ledger_id
AND company_id = l_com_id
AND cost_center_id = l_cc_id
AND fin_category_id = l_fin_cat_id
AND nvl(prod_category_id, -999) = nvl(l_prod_cat_id, -999)
AND user_dim1_id = l_udd1_id;
UPDATE FII_BUDGET_INTERFACE
SET status_code = 'ERROR'
WHERE plan_type_code = l_plan_code
AND trunc(version_date) = trunc(l_ver_date)
AND report_time_period = l_time_unit
AND ledger_id = l_ledger_id
AND company_id = l_com_id
AND cost_center_id = l_cc_id
AND fin_category_id = l_fin_cat_id
AND nvl(prod_category_id, -999) = nvl(l_prod_cat_id, -999)
AND user_dim1_id = l_udd1_id;
UPDATE FII_BUDGET_INTERFACE
SET status_code = 'ERROR'
WHERE plan_type_code = l_plan_code
AND trunc(version_date) = trunc(l_ver_date)
AND report_time_period = l_time_unit
AND ledger_id = l_ledger_id
AND company_id = l_com_id
AND cost_center_id = l_cc_id
AND fin_category_id = l_fin_cat_id
AND nvl(prod_category_id, -999) = nvl(l_prod_cat_id, -999)
AND user_dim1_id = l_udd1_id;
UPDATE FII_BUDGET_INTERFACE
SET status_code = 'ERROR'
WHERE plan_type_code = l_plan_code
AND trunc(version_date) = trunc(l_ver_date)
AND report_time_period = l_time_unit
AND ledger_id = l_ledger_id
AND company_id = l_com_id
AND cost_center_id = l_cc_id
AND fin_category_id = l_fin_cat_id
AND nvl(prod_category_id, -999) = nvl(l_prod_cat_id, -999)
AND user_dim1_id = l_udd1_id;
SELECT 1
INTO l_count
FROM FII_BUDGET_INTERFACE
WHERE status_code = 'ERROR'
AND rownum = 1;
l_tmpstmt := 'INSERT INTO FII_BUDGET_DELTAS ' ||
' (plan_type_code, version_date, time_id, '||
' ledger_id, company_id, '||
' cost_center_id, fin_category_id, prod_category_id, '||
' user_dim1_id, data_type, '||
' prior_version_date, orig_prim_amount_total, '||
' orig_prim_amount_g, orig_sec_amount_total, '||
' orig_sec_amount_g, last_update_date, last_updated_by, '||
' creation_date, created_by, last_update_login ) '||
----------------------------------------------------------------------------
-- Case 1: Version date is provided and time/dimension combination does not
-- exist in base table.
----------------------------------------------------------------------------
'SELECT '||
' bi.plan_type_code, trunc(bi.version_date), ';
'SELECT 1 '||
'FROM FII_BUDGET_BASE bb, ';
' SELECT bi.plan_type_code, trunc(bi.version_date), ';
'SELECT 1 '||
'FROM FII_BUDGET_BASE bb2, ';
' SELECT bi.plan_type_code, trunc(bi.version_date), ';
'SELECT 1 '||
'FROM ';
' SELECT bi.plan_type_code, trunc(bi.version_date), ';
',(SELECT v.name, v.plan_type_code, v.ledger_id, v.company_id, '||
' v.cost_center_id, v.fin_category_id, v.category_id, '||
' v.user_dim1_id, v.version_date '||
' FROM ( '||
' SELECT t.name, bb.plan_type_code, bb.ledger_id, bb.company_id, '||
' bb.cost_center_id, bb.fin_category_id, bb.category_id, '||
' bb.user_dim1_id, bb.version_date, '||
' rank() over (partition by t.name, bb.plan_type_code, '||
' bb.ledger_id, bb.company_id, '||
' bb.cost_center_id,bb.fin_category_id, '||
' bb.category_id, bb.user_dim1_id'||
' order by bb.version_date desc) Rank '||
' FROM FII_BUDGET_BASE bb, ';
fii_util.put_line('Inserted '||SQL%ROWCOUNT||
' budget rows into fii_budget_deltas');
fii_util.put_line('Inserted '||SQL%ROWCOUNT||
' forecast rows into fii_budget_deltas');
l_tmpstmt := 'INSERT INTO FII_BUDGET_DELTAS ' ||
' (plan_type_code, version_date, time_id, '||
' ledger_id, company_id, '||
' cost_center_id, fin_category_id, prod_category_id, '||
' user_dim1_id, data_type, '||
' prior_version_date, orig_prim_amount_total, '||
' orig_prim_amount_g, orig_sec_amount_total, '||
' orig_sec_amount_g, last_update_date, last_updated_by, '||
' creation_date, created_by, last_update_login ) '||
----------------------------------------------------------------------------
-- Case 5: Version date is NULL and time/dimension combination does not
-- exist in base table
----------------------------------------------------------------------------
'SELECT bi.plan_type_code, '||
' trunc(bi.version_date), ';
' SELECT 1 '||
' FROM FII_BUDGET_BASE bb, ';
'SELECT bi.plan_type_code, '||
'trunc(bi.version_date), ';
' (SELECT v.name, v.plan_type_code, v.ledger_id, v.company_id, '||
' v.cost_center_id, v.fin_category_id, v.category_id, '||
' v.user_dim1_id, v.version_date '||
' FROM ( '||
' SELECT t.name, bb.plan_type_code, bb.ledger_id, bb.company_id, '||
' bb.cost_center_id, bb.fin_category_id, bb.category_id, '||
' bb.user_dim1_id, bb.version_date, '||
' rank() over (partition by t.name, bb.plan_type_code, '||
' bb.ledger_id, bb.company_id, '||
' bb.cost_center_id,bb.fin_category_id, '||
' bb.category_id, bb.user_dim1_id'||
' order by bb.version_date desc) Rank '||
' FROM FII_BUDGET_BASE bb, ';
fii_util.put_line('Inserted '||SQL%ROWCOUNT||
' budget rows into fii_budget_deltas');
fii_util.put_line('Inserted '||SQL%ROWCOUNT||
' forecast rows into fii_budget_deltas');
l_bud_sqlstmt := ' INSERT INTO FII_BUDGET_STG ( ' ||
' version_date, ' || l_bud_stg_col_name || ', ';
l_fc_sqlstmt := ' INSERT INTO FII_BUDGET_STG ( ' ||
' version_date, ' || l_fc_stg_col_name || ', ';
' last_update_date, last_updated_by, last_update_login, '||
' ledger_id, company_cost_center_org_id, '||
' company_id, cost_center_id, fin_category_id, '||
' category_id, user_dim1_id, user_dim2_id, '||
' prim_amount_total, prim_amount_g, '||
' sec_amount_total, sec_amount_g, overwrite_version_date, '||
' data_type, no_version_flag ) '||
' SELECT ';
'INSERT INTO FII_BUDGET_BASE_T '||
' (plan_type_code, version_date, overwrite_version_date, '||
' no_version_flag, '||
' data_type, prim_amount_total, sec_amount_total, '||
' prim_amount_g, sec_amount_g, creation_date, created_by, '||
' last_update_date, last_updated_by, last_update_login, '||
' ledger_id, company_cost_center_org_id, '||
' company_id, cost_center_id, fin_category_id, '||
' category_id, user_dim1_id, user_dim2_id, '||
' time_id, period_type_id, day, week, period, quarter, year '||
' )'||
' SELECT '||
' b.plan_type_code, s.version_date, s.overwrite_version_date, '||
' s.no_version_flag, s.data_type, '||
' s.prim_amount_total - b.prim_amount_total, '||
' s.sec_amount_total - b.sec_amount_total, '||
' s.prim_amount_g - b.prim_amount_g, '||
' s.sec_amount_g - b.sec_amount_g, b.creation_date, '||
' b.created_by, '||
' b.last_update_date, b.last_updated_by, b.last_update_login, '||
' b.ledger_id, s.company_cost_center_org_id, '||
' b.company_id, b.cost_center_id, b.fin_category_id, '||
' b.category_id, b.user_dim1_id, b.user_dim2_id, '||
' b.time_id, b.period_type_id, '||
' s.day, s.week, s.period, s.quarter, s.year '||
' FROM FII_BUDGET_BASE b, FII_BUDGET_STG s '||
' WHERE b.plan_type_code = s. plan_type_code '||
' AND b.ledger_id = s.ledger_id '||
' AND b.company_id = s.company_id '||
' AND b.cost_center_id = s.cost_center_id '||
' AND b.fin_category_id = s.fin_category_id '||
' AND NVL(b.category_id, -1) = NVL(s.category_id, -1) '||
' AND b.user_dim1_id = s.user_dim1_id '||
' AND b.version_date = s.version_date '||
' AND s.data_type in (-3, -4, -6) '||
' AND b.time_id = NVL(s.day, NVL(s.week, '||
'NVL(s.period, NVL(s.quarter, s.year)))) '||
' AND b.period_type_id = '||
'DECODE(s.day, null, '||
' DECODE(s.week, null, DECODE(s.period, null, '||
' DECODE(s.quarter, null, 128, 64), 32), 16), 1) ';
' INSERT INTO FII_BUDGET_BASE_T ( '||
' version_date, overwrite_version_date, no_version_flag, '||
' data_type, period, quarter, year, plan_type_code, '||
' creation_date, created_by, last_update_date, last_updated_by, '||
' last_update_login, ledger_id, company_cost_center_org_id, '||
' company_id, cost_center_id, '||
' fin_category_id, '||
' category_id, user_dim1_id, user_dim2_id, prim_amount_total, '||
' prim_amount_g, sec_amount_total, sec_amount_g) '||
' SELECT '||
' s.version_date, s.overwrite_version_date, s.no_version_flag, '||
' s.data_type, to_number(NULL), ';
' INSERT INTO FII_BUDGET_STG ( '||
' version_date, period, quarter, year, plan_type_code, '||
' creation_date, created_by, last_update_date, '||
' last_updated_by, last_update_login, ledger_id, '||
' company_cost_center_org_id, company_id, '||
' cost_center_id, fin_category_id, category_id, '||
' user_dim1_id, user_dim2_id, no_version_flag, '||
' overwrite_version_date, data_type, prim_amount_total, '||
' prim_amount_g, sec_amount_total, sec_amount_g) '||
' SELECT '||
' s.version_date, to_number(NULL), ';
' INSERT INTO FII_BUDGET_STG ( '||
' version_date, period, quarter, year, plan_type_code, '||
' creation_date, created_by, last_update_date, '||
' last_updated_by, last_update_login, ledger_id, '||
' company_cost_center_org_id, company_id, '||
' cost_center_id, fin_category_id, category_id, '||
' user_dim1_id, user_dim2_id, prim_amount_total, '||
' prim_amount_g, sec_amount_total, sec_amount_g, '||
' data_type, overwrite_version_date, no_version_flag ) '||
' SELECT '||
' version_date, period, quarter, year, plan_type_code, '||
' creation_date, created_by, last_update_date, '||
' last_updated_by, last_update_login, ledger_id, '||
' company_cost_center_org_id, company_id, '||
' cost_center_id, fin_category_id, category_id, '||
' user_dim1_id, user_dim2_id, prim_amount_total, '||
' prim_amount_g, sec_amount_total, sec_amount_g, '||
' data_type, overwrite_version_date, no_version_flag '||
' FROM FII_BUDGET_BASE_T ';
' (SELECT /*+ PARALLEL(stg)*/ '||
' version_date, plan_type_code, '||
' NVL(period, NVL(quarter, year)) time_id, '||
' DECODE(period, '||
' null, DECODE(quarter, '||
' null, 128, 64), 32) PERIOD_TYPE_ID, '||
' ledger_id, company_cost_center_org_id, '||
' company_id, cost_center_id, fin_category_id, '||
' category_id, user_dim1_id, user_dim2_id, '||
' no_version_flag, sum(prim_amount_total) PRIM_AMOUNT_TOTAL, '||
' sum(sec_amount_total) SEC_AMOUNT_TOTAL, '||
' sum(prim_amount_g) PRIM_AMOUNT_G, '||
' sum(sec_amount_g) SEC_AMOUNT_G '||
' FROM FII_BUDGET_STG stg ' ||
' WHERE plan_type_code = :plan_type_code '||
' GROUP BY version_date, plan_type_code, '||
' NVL(period, NVL(quarter, year)), '||
' DECODE(period, '||
' null, DECODE(quarter, '||
' null, 128, 64), 32), '||
' ledger_id, company_cost_center_org_id, '||
' company_id, cost_center_id, fin_category_id, '||
' category_id, user_dim1_id, user_dim2_id, '||
' no_version_flag) s '||
' ON( b.plan_type_code = s.plan_type_code '||
' AND b.time_id = s.time_id '||
' AND b.period_type_id = s.period_type_id '||
' AND b.version_date = s.version_date '||
' AND b.ledger_id = s.ledger_id '||
' AND b.company_id = s.company_id '||
' AND b.cost_center_id = s.cost_center_id '||
' AND b.fin_category_id = s.fin_category_id '||
' AND NVL(b.category_id, -1) = NVL(s.category_id, -1) '||
' AND b.user_dim1_id = s.user_dim1_id '||
' AND b.user_dim2_id = s.user_dim2_id) '||
' WHEN MATCHED THEN UPDATE SET ' ||
' b.prim_amount_total = decode(b.period_type_id, '||
' :per_type_id, s.prim_amount_total, '||
' b.prim_amount_total + s.prim_amount_total), '||
' b.prim_amount_g = decode(b.period_type_id, '||
' :per_type_id, s.prim_amount_g, '||
' b.prim_amount_g + s.prim_amount_g), '||
' b.sec_amount_total = decode(b.period_type_id, '||
' :per_type_id, s.sec_amount_total, '||
' b.sec_amount_total + s.sec_amount_total), '||
' b.sec_amount_g = decode(b.period_type_id, '||
' :per_type_id, s.sec_amount_g, '||
' b.sec_amount_g + s.sec_amount_g), '||
' b.no_version_flag = s.no_version_flag, '||
' b.company_cost_center_org_id = s.company_cost_center_org_id, '||
' b.last_update_date = SYSDATE, '||
' b.last_updated_by = :user_id, '||
' b.last_update_login = :login_id, '||
' b.upload_date = SYSDATE '||
' WHEN NOT MATCHED THEN INSERT '||
' (b.version_date, b.plan_type_code, b.time_id, b.period_type_id,'||
' b.ledger_id, b.company_cost_center_org_id, '||
' b.company_id, b.cost_center_id, b.fin_category_id, '||
' b.category_id, '||
' b.user_dim1_id, b.user_dim2_id, '||
' b.prim_amount_total, b.prim_amount_g, '||
' b.sec_amount_total, b.sec_amount_g, b.no_version_flag, '||
' b.creation_date, b.created_by, '||
' b.last_update_date, b.last_updated_by, b.last_update_login, '||
' b.upload_date, b.posted_date) '||
' VALUES '||
' (s.version_date, s.plan_type_code, s.time_id, s.period_type_id, '||
' s.ledger_id, s.company_cost_center_org_id, '||
' s.company_id, s.cost_center_id, s.fin_category_id, '||
' s.category_id, '||
' s.user_dim1_id, s.user_dim2_id, '||
' s.prim_amount_total, s.prim_amount_g, '||
' s.sec_amount_total, s.sec_amount_g, s.no_version_flag, '||
' SYSDATE, :user_id, SYSDATE, '||
' :user_id, :login_id, SYSDATE, :default_posted_date) ';
' UPDATE FII_BUDGET_BASE b '||
' SET (b.version_date, b.no_version_flag) = '||
' (SELECT MAX(s.overwrite_version_date), MAX(s.no_version_flag) '||
' FROM FII_BUDGET_STG s '||
' WHERE ';
' SELECT s2.plan_type_code, '||
' NVL(s2.period, NVL(s2.quarter, s2.year)), '||
' DECODE(period, null, DECODE(quarter, null, 128, 64), 32), '||
' ledger_id, company_id, cost_center_id, fin_category_id, '||
' category_id, user_dim1_id, user_dim2_id, version_date '||
' FROM fii_budget_stg s2 '||
' WHERE data_type = -4) ';
SELECT DISTINCT trunc(version_date)
FROM fii_budget_interface
WHERE trunc(version_date) IS NOT NULL
ORDER BY trunc(version_date);
SELECT 'Y'
FROM fii_budget_interface
WHERE version_date IS NULL;
SELECT l.item_value
INTO l_prev_bud_time_unit
FROM FII_CHANGE_LOG l
WHERE l.log_item = 'BUDGET_TIME_UNIT';
INSERT INTO FII_CHANGE_LOG
(log_item, item_value, creation_date, created_by,
last_update_date, last_updated_by, last_update_login)
VALUES
('BUDGET_TIME_UNIT', FIIBUUP_BUDGET_TIME_UNIT, SYSDATE,
FIIBUUP_USER_ID, SYSDATE, FIIBUUP_USER_ID, FIIBUUP_LOGIN_ID);
SELECT l.item_value
INTO l_prev_fc_time_unit
FROM FII_CHANGE_LOG l
WHERE l.log_item = 'FORECAST_TIME_UNIT';
INSERT INTO FII_CHANGE_LOG
(log_item, item_value, creation_date, created_by,
last_update_date, last_updated_by, last_update_login)
VALUES
('FORECAST_TIME_UNIT', FIIBUUP_FORECAST_TIME_UNIT, SYSDATE,
FIIBUUP_USER_ID, SYSDATE, FIIBUUP_USER_ID, FIIBUUP_LOGIN_ID);
SELECT l1.item_value
INTO l_drop_bud_data
FROM FII_CHANGE_LOG l1
WHERE l1.log_item = 'TRUNCATE_BUDGET';
SELECT l1.item_value
INTO l_drop_fc_data
FROM FII_CHANGE_LOG l1
WHERE l1.log_item = 'TRUNCATE_FORECAST';
(SELECT 'TRUNCATE_BUDGET' log_item from DUAL) l2
ON (l1.log_item = l2.log_item)
WHEN MATCHED THEN UPDATE SET
item_value = 'Y',
last_update_date = SYSDATE,
last_updated_by = FIIBUUP_USER_ID,
last_update_login = FIIBUUP_LOGIN_ID
WHEN NOT MATCHED THEN INSERT
(l1.log_item, l1.item_value, l1.creation_date,
l1.created_by, l1.last_update_date, l1.last_updated_by,
l1.last_update_login)
VALUES
('TRUNCATE_BUDGET', 'Y', SYSDATE, FIIBUUP_USER_ID, SYSDATE,
FIIBUUP_USER_ID, FIIBUUP_LOGIN_ID);
UPDATE FII_CHANGE_LOG
SET item_value = 'N',
last_update_date = SYSDATE,
last_updated_by = FIIBUUP_USER_ID,
last_update_login = FIIBUUP_LOGIN_ID
WHERE log_item = 'TRUNCATE_BUDGET';
(SELECT 'TRUNCATE_FORECAST' log_item from DUAL) l2
ON (l1.log_item = l2.log_item)
WHEN MATCHED THEN UPDATE SET
item_value = 'Y',
last_update_date = SYSDATE,
last_updated_by = FIIBUUP_USER_ID,
last_update_login = FIIBUUP_LOGIN_ID
WHEN NOT MATCHED THEN INSERT
(l1.log_item, l1.item_value, l1.creation_date,
l1.created_by, l1.last_update_date, l1.last_updated_by,
l1.last_update_login)
VALUES
('TRUNCATE_FORECAST', 'Y', SYSDATE, FIIBUUP_USER_ID, SYSDATE,
FIIBUUP_USER_ID, FIIBUUP_LOGIN_ID);
UPDATE FII_CHANGE_LOG
SET item_value = 'N',
last_update_date = SYSDATE,
last_updated_by = FIIBUUP_USER_ID,
last_update_login = FIIBUUP_LOGIN_ID
WHERE log_item = 'TRUNCATE_BUDGET';
SELECT 1
INTO l_int_count
FROM FII_BUDGET_INTERFACE
WHERE rownum = 1;
/* Bug 4655730: Commented out this delete statement due to performance issue
and also deleting ledgers from FDS is a corner case. We'll have a long
term fix for this tracked in bug 4660166.
g_phase := 'Delete from FII_BUDGET_BASE when ledgers are no longer set up';
DELETE FROM fii_budget_base
WHERE ledger_id IN (
SELECT DISTINCT ledger_id
FROM fii_budget_base
WHERE ledger_id NOT IN (SELECT ledger_id
FROM fii_slg_assignments ));