[Home] [Help]
3448: procedure migrate_formulas is
3449: --
3450: type t_jp_formula_names_tab is table of VARCHAR2(200) index by binary_integer;
3451:
3452: type t_formula_names_tab is table of ff_formulas_f.formula_name%TYPE index by binary_integer;
3453:
3454: type t_formula_desc_tab is table of ff_formulas_f.description%TYPE index by binary_integer;
3455:
3456: l_jp_formula_names_tab t_jp_formula_names_tab;
3450: type t_jp_formula_names_tab is table of VARCHAR2(200) index by binary_integer;
3451:
3452: type t_formula_names_tab is table of ff_formulas_f.formula_name%TYPE index by binary_integer;
3453:
3454: type t_formula_desc_tab is table of ff_formulas_f.description%TYPE index by binary_integer;
3455:
3456: l_jp_formula_names_tab t_jp_formula_names_tab;
3457: l_formula_names_tab t_formula_names_tab;
3458: l_formula_desc_tab t_formula_desc_tab;
3814: --
3815:
3816: FORALL l_tab_cnt IN 1..l_jp_formula_names_tab.COUNT
3817:
3818: UPDATE ff_formulas_f
3819: SET formula_name = l_formula_names_tab(l_tab_cnt),
3820: description = l_formula_desc_tab(l_tab_cnt)
3821: WHERE formula_name LIKE hr_jp_standard_pkg.hextochar(l_jp_formula_names_tab(l_tab_cnt),'AL32UTF8')
3822: AND legislation_code = 'JP';
3820: description = l_formula_desc_tab(l_tab_cnt)
3821: WHERE formula_name LIKE hr_jp_standard_pkg.hextochar(l_jp_formula_names_tab(l_tab_cnt),'AL32UTF8')
3822: AND legislation_code = 'JP';
3823:
3824: UPDATE ff_formulas_f
3825: SET description = description || 'Obsoleted'
3826: WHERE ASCII(description) > 127
3827: AND legislation_code = 'JP';
3828:
4745: DELETE ff_compiled_info_f
4746: WHERE formula_id in (
4747: SELECT /*+ ORDERED USE_NL(BG FDU FDT FUE BG2) */
4748: distinct f.formula_id
4749: FROM ff_formulas_f f,
4750: per_business_groups_perf bg,
4751: ff_fdi_usages_f fdu,
4752: ff_database_items fdt,
4753: ff_user_entities fue,
4773: DELETE ff_fdi_usages_f
4774: WHERE formula_id in (
4775: SELECT /*+ ORDERED USE_NL(BG FDU FDT FUE BG2) */
4776: distinct f.formula_id
4777: FROM ff_formulas_f f,
4778: per_business_groups_perf bg,
4779: ff_fdi_usages_f fdu,
4780: ff_database_items fdt,
4781: ff_user_entities fue,
5250: --
5251: cursor csr_ff_cnt
5252: is
5253: select count(ff.formula_id)
5254: from ff_formulas_f ff
5255: where ff.legislation_code = 'JP'
5256: and ascii(substr(ff.formula_name,3,1)) <= 127
5257: and ff.formula_name not in (
5258: 'HI_PREM_ROUNDING_TYPE_VALIDATION',
5261: 'JP_LI_GIP_PREM',
5262: 'JP_LI_LINC_PREM')
5263: and exists(
5264: select null
5265: from ff_formulas_f ff2
5266: where ff2.legislation_code = 'JP'
5267: and ascii(substr(ff2.formula_name,3,1)) > 127
5268: and ff2.description not like '%Obsoleted');
5269: --
6375: -- l_dummy := null;
6376: -- --
6377: -- select ff.formula_name
6378: -- into l_dummy
6379: -- from ff_formulas_f ff
6380: -- where ff.formula_name = l_ff_tbl(l_ff_ind)
6381: -- and ff.legislation_code = 'JP'
6382: -- and ff.effective_start_date = (
6383: -- select max(ff2.effective_start_date)
6380: -- where ff.formula_name = l_ff_tbl(l_ff_ind)
6381: -- and ff.legislation_code = 'JP'
6382: -- and ff.effective_start_date = (
6383: -- select max(ff2.effective_start_date)
6384: -- from ff_formulas_f ff2
6385: -- where ff2.formula_id = ff.formula_id);
6386: -- --
6387: -- if (g_dbg) then
6388: -- hr_utility.trace('fix_hr_corrupt, update ff : '||l_dummy);
6416: -- end if;
6417: --
6418: -- use bulk collect
6419: forall l_ff_ind in 1..l_ff_tbl.count
6420: update ff_formulas_f
6421: set formula_name = substrb(formula_name,1,80-4)||'_OBS',
6422: description = substrb(description,1,240-11)||' (Obsolete)'
6423: where formula_name = l_ff_tbl(l_ff_ind)
6424: and legislation_code = 'JP';
6962: hr_utility.set_location(l_proc, 80);
6963:
6964: SELECT COUNT(1)
6965: INTO l_count
6966: FROM ff_formulas_f
6967: WHERE legislation_code = 'JP'
6968: AND ASCII(SUBSTR(formula_name,3,1)) > 127
6969: AND description NOT LIKE '%Obsoleted';
6970: