[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_jp_input_names_tab.DELETE;
l_input_names_tab.DELETE;
UPDATE pay_input_values_f
SET name = 'LTX'
WHERE name LIKE hr_jp_standard_pkg.hextochar('E5BEB4E58F8EE7A88EE9A18D','AL32UTF8')
AND legislation_code = 'JP'
AND element_type_id IN (
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name LIKE hr_jp_standard_pkg.hextochar('E585B1EFBCBFE59FBAE69CACEFBCBFE4BD8FE6B091E7A88EEFBCBFE4B880E68BACE5BEB4E58F8E','AL32UTF8')
AND legislation_code = 'JP' );
UPDATE pay_input_values_f
SET name = 'INS_PREM_ER'
WHERE name LIKE hr_jp_standard_pkg.hextochar('E4BA8BE6A5ADE4B8BBE58886E4BF9DE999BAE69699','AL32UTF8')
AND legislation_code = 'JP'
AND element_type_id IN (
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name IN
(hr_jp_standard_pkg.hextochar('E7B5A6EFBCBFE59FBAE69CACEFBCBFE581A5E4BF9DEFBCBFE4BF9DE999BAE69699','AL32UTF8'),
hr_jp_standard_pkg.hextochar('E8B39EEFBCBFE59FBAE69CACEFBCBFE581A5E4BF9DEFBCBFE4BF9DE999BAE69699','AL32UTF8'),
hr_jp_standard_pkg.hextochar('E8B39EEFBCBFE59FBAE69CACEFBCBFE58E9AE5B9B4EFBCBFE4BF9DE999BAE69699','AL32UTF8'))
AND legislation_code = 'JP' );
UPDATE pay_input_values_f
SET name = 'INS_PREM_EE'
WHERE name LIKE hr_jp_standard_pkg.hextochar('E8A2ABE4BF9DE999BAE88085E58886E4BF9DE999BAE69699','AL32UTF8')
AND legislation_code = 'JP'
AND element_type_id IN (
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name IN
(hr_jp_standard_pkg.hextochar('E7B5A6EFBCBFE59FBAE69CACEFBCBFE581A5E4BF9DEFBCBFE4BF9DE999BAE69699','AL32UTF8'),
hr_jp_standard_pkg.hextochar('E8B39EEFBCBFE59FBAE69CACEFBCBFE581A5E4BF9DEFBCBFE4BF9DE999BAE69699','AL32UTF8'),
hr_jp_standard_pkg.hextochar('E8B39EEFBCBFE59FBAE69CACEFBCBFE58E9AE5B9B4EFBCBFE4BF9DE999BAE69699','AL32UTF8'))
AND legislation_code = 'JP' );
UPDATE pay_input_values_f
SET name = l_input_names_tab(l_tab_cnt)
WHERE name LIKE hr_jp_standard_pkg.hextochar(l_jp_input_names_tab(l_tab_cnt),'AL32UTF8')
AND legislation_code = 'JP';
hr_utility.trace('| Total Input Values Updated = ' || SQL%ROWCOUNT);
l_jp_element_names_tab.DELETE;
l_element_names_tab.DELETE;
l_element_desc_tab.DELETE;
l_element_rep_tab.DELETE;
UPDATE pay_element_types_f
SET element_name = l_element_names_tab(l_tab_cnt),
description = l_element_desc_tab(l_tab_cnt),
reporting_name = l_element_rep_tab(l_tab_cnt)
WHERE element_name LIKE hr_jp_standard_pkg.hextochar(l_jp_element_names_tab(l_tab_cnt),'AL32UTF8')
AND legislation_code = 'JP';
UPDATE pay_element_types_f
SET description = description || 'Obsoleted'
WHERE ASCII(description) > 127
AND legislation_code = 'JP';
hr_utility.trace('| Total Elements Updated = ' || SQL%ROWCOUNT);
l_jp_class_name.DELETE;
l_class_names_tab.DELETE;
l_class_desc_tab.DELETE;
UPDATE pay_element_classifications
SET classification_name = l_class_names_tab(l_tab_cnt),
description = l_class_desc_tab(l_tab_cnt)
WHERE classification_name LIKE l_jp_class_name(l_tab_cnt)
AND legislation_code = 'JP';
hr_utility.trace('| Total Classifications Updated = ' || SQL%ROWCOUNT);
l_jp_bal_names_tab.DELETE;
l_bal_names_tab.DELETE;
l_bal_rep_tab.DELETE;
UPDATE pay_balance_types
SET balance_name = l_bal_names_tab(l_tab_cnt),
reporting_name = l_bal_rep_tab(l_tab_cnt)
WHERE balance_name LIKE hr_jp_standard_pkg.hextochar(l_jp_bal_names_tab(l_tab_cnt),'AL32UTF8')
AND legislation_code = 'JP';
UPDATE pay_balance_types
SET reporting_name = reporting_name || 'Obsoleted'
WHERE ASCII(reporting_name) > 127
AND legislation_code = 'JP';
hr_utility.trace('| Total Balance Types Updated = ' || SQL%ROWCOUNT);
l_jp_dim_names_tab.DELETE;
l_dim_names_tab.DELETE;
l_dim_desc_tab.DELETE;
l_dim_suffix_tab.DELETE;
UPDATE pay_balance_dimensions
SET dimension_name = l_dim_names_tab(l_tab_cnt),
database_item_suffix = l_dim_suffix_tab(l_tab_cnt),
description = l_dim_desc_tab(l_tab_cnt)
WHERE dimension_name LIKE hr_jp_standard_pkg.hextochar(l_jp_dim_names_tab(l_tab_cnt),'AL32UTF8')
AND legislation_code = 'JP';
hr_utility.trace('| Total Balance Dimensions Updated = ' || SQL%ROWCOUNT);
l_jp_ele_set_names_tab.DELETE;
l_ele_set_names_tab.DELETE;
UPDATE pay_element_sets
SET element_set_name = l_ele_set_names_tab(l_tab_cnt)
WHERE element_set_name LIKE hr_jp_standard_pkg.hextochar(l_jp_ele_set_names_tab(l_tab_cnt),'AL32UTF8')
AND legislation_code = 'JP';
hr_utility.trace('| Total Element Sets Updated = ' || SQL%ROWCOUNT);
l_jp_global_names_tab.DELETE;
l_global_names_tab.DELETE;
l_global_desc_tab.DELETE;
UPDATE ff_globals_f
SET global_name = l_global_names_tab(l_tab_cnt),
global_description = l_global_desc_tab(l_tab_cnt)
WHERE global_name LIKE hr_jp_standard_pkg.hextochar(l_jp_global_names_tab(l_tab_cnt),'AL32UTF8')
AND legislation_code = 'JP';
UPDATE ff_globals_f
SET global_description = global_description || 'Obsoleted'
WHERE ASCII(global_description) > 127
AND legislation_code = 'JP';
hr_utility.trace('| Total Global Names Updated = ' || SQL%ROWCOUNT);
l_jp_formula_names_tab.DELETE;
l_formula_names_tab.DELETE;
l_formula_desc_tab.DELETE;
UPDATE ff_formulas_f
SET formula_name = l_formula_names_tab(l_tab_cnt),
description = l_formula_desc_tab(l_tab_cnt)
WHERE formula_name LIKE hr_jp_standard_pkg.hextochar(l_jp_formula_names_tab(l_tab_cnt),'AL32UTF8')
AND legislation_code = 'JP';
UPDATE ff_formulas_f
SET description = description || 'Obsoleted'
WHERE ASCII(description) > 127
AND legislation_code = 'JP';
hr_utility.trace('| Total Formulas Updated = ' || SQL%ROWCOUNT);
l_jp_monetary_name_tab.DELETE;
l_monetary_name_tab.DELETE;
UPDATE pay_monetary_units
SET monetary_unit_name = l_monetary_name_tab(l_tab_cnt)
WHERE monetary_unit_name LIKE hr_jp_standard_pkg.hextochar(l_jp_monetary_name_tab(l_tab_cnt),'AL32UTF8')
AND legislation_code = 'JP';
hr_utility.trace('| Total Monetary Units Updated = ' || SQL%ROWCOUNT);
l_jp_column_names_tab.DELETE;
l_column_names_tab.DELETE;
UPDATE pay_user_columns
SET user_column_name = 'RATE'
WHERE user_column_name LIKE hr_jp_standard_pkg.hextochar('E78E87','AL32UTF8')
AND legislation_code = 'JP'
AND user_table_id IN (
SELECT user_table_id
FROM pay_user_tables
WHERE user_table_name IN (
hr_jp_standard_pkg.hextochar('54EFBCBFE5B9B4EFBCBFE5808BE4BABAE5B9B4E98791E4BF9DE999BAE69699E68EA7E999A4E9A18DE8A1A8','AL32UTF8'),
hr_jp_standard_pkg.hextochar('54EFBCBFE5B9B4EFBCBFE7949FE591BDE4BF9DE999BAE69699E68EA7E999A4E9A18DE8A1A8','AL32UTF8'),
hr_jp_standard_pkg.hextochar('54EFBCBFE5B9B4EFBCBFE995B7E69C9FE6908DE5AEB3E4BF9DE999BAE69699E68EA7E999A4E9A18DE8A1A8','AL32UTF8'),
hr_jp_standard_pkg.hextochar('54EFBCBFE5B9B4EFBCBFE79FADE69C9FE6908DE5AEB3E4BF9DE999BAE69699E68EA7E999A4E9A18DE8A1A8','AL32UTF8'))
AND legislation_code = 'JP');
UPDATE pay_user_columns
SET user_column_name = 'EXM'
WHERE user_column_name LIKE hr_jp_standard_pkg.hextochar('E68EA7E999A4E9A18D','AL32UTF8')
AND legislation_code = 'JP'
AND user_table_id IN (
SELECT user_table_id
FROM pay_user_tables
WHERE user_table_name IN (
hr_jp_standard_pkg.hextochar('54EFBCBFE5B9B4EFBCBFE59084E7A8AEE68980E5BE97E68EA7E999A4E9A18DE8A1A8','AL32UTF8'),
hr_jp_standard_pkg.hextochar('54EFBCBFE5B9B4EFBCBFE9858DE581B6E88085E789B9E588A5E68EA7E999A4E9A18DE697A9E8A68BE8A1A8','AL32UTF8'))
AND legislation_code = 'JP');
UPDATE pay_user_columns
SET user_column_name = 'STD_DCT'
WHERE user_column_name LIKE hr_jp_standard_pkg.hextochar('E68EA7E999A4E9A18D','AL32UTF8')
AND legislation_code = 'JP'
AND user_table_id = (
SELECT user_table_id
FROM pay_user_tables
WHERE user_table_name LIKE hr_jp_standard_pkg.hextochar('54EFBCBFE5B9B4EFBCBFE7B5A6E4B88EE68980E5BE97E68EA7E999A4E5BE8CE381AEE7B5A6E4B88EE7AD89E381AEE98791E9A18DE8A1A832','AL32UTF8')
AND legislation_code = 'JP');
UPDATE pay_user_columns
SET user_column_name = l_column_names_tab(l_tab_cnt)
WHERE user_column_name LIKE hr_jp_standard_pkg.hextochar(l_jp_column_names_tab(l_tab_cnt),'AL32UTF8')
AND legislation_code = 'JP';
hr_utility.trace('| Total User Columns Updated = ' || SQL%ROWCOUNT);
l_jp_row_names_tab.DELETE;
l_row_names_tab.DELETE;
UPDATE pay_user_rows_f
SET row_low_range_or_name = l_row_names_tab(l_tab_cnt)
WHERE row_low_range_or_name LIKE hr_jp_standard_pkg.hextochar(l_jp_row_names_tab(l_tab_cnt),'AL32UTF8')
AND legislation_code = 'JP';
hr_utility.trace('| Total User Rows Updated = ' || SQL%ROWCOUNT);
l_jp_table_names_tab.DELETE;
l_table_names_tab.DELETE;
l_row_titles_tab.DELETE;
UPDATE pay_user_tables
SET user_table_name = l_table_names_tab(l_tab_cnt),
user_row_title = l_row_titles_tab(l_tab_cnt)
WHERE user_table_name LIKE hr_jp_standard_pkg.hextochar(l_jp_table_names_tab(l_tab_cnt),'AL32UTF8')
AND legislation_code = 'JP';
hr_utility.trace('| Total User Tables Updated = ' || SQL%ROWCOUNT);
procedure delete_dbi is
--
b_script_already_run BOOLEAN;
SELECT *
FROM pay_patch_status
WHERE patch_number = 5758299
AND patch_name = 'JP_UPGRADE_R12'
AND phase = 'C'
AND legislation_code = 'JP';
select g.global_name,
g.data_type,
g.global_id,
g.business_group_id,
g.legislation_code,
g.created_by,
g.creation_date
from ff_globals_f g
where g.legislation_code = 'JP'
and not exists(
select null
from ff_globals_f g2
where g2.global_id = g.global_id
and g2.effective_start_date < g.effective_start_date);
select fdit.rowid row_id
from ff_globals_f fg,
ff_globals_f_tl fgt,
ff_database_items_tl fdit
where fg.legislation_code = 'JP'
and fg.effective_start_date = (
select max(fg2.effective_start_date)
from ff_globals_f fg2
where fg2.global_id = fg.global_id)
and fgt.global_id = fg.global_id
and fgt.language in ('US','JA')
--there is translation bug so workaround
--and fdit.user_name = decode(fgt.language,'US',fg.global_name,'JA',fgt.global_name,null)
and fdit.user_name in (
decode(fgt.language,'US',fg.global_name,'JA',fgt.global_name,null),
decode(fgt.global_name,hr_jp_standard_pkg.sjhextochar('478151944E815192E897A68CB890C597A6'),
hr_jp_standard_pkg.sjhextochar('478151944E815192E897A68CB890C597BF97A6'),null))
and not exists(
select null
from ff_database_items fdi
where fdi.user_entity_id = fdit.user_entity_id
and fdi.user_name = fdit.user_name);
select fdi.user_entity_id,
fdi.user_name,
fdi.description
from ff_globals_f fg,
ff_user_entities fue,
ff_database_items fdi
where fg.legislation_code = 'JP'
and fg.effective_start_date = (
select max(fg2.effective_start_date)
from ff_globals_f fg2
where fg2.global_id = fg.global_id)
and fue.legislation_code = 'JP'
and fue.creator_id = fg.global_id
and fue.creator_type = 'S'
and fdi.user_name = fg.global_name
and fdi.user_entity_id = fue.user_entity_id
and not exists(
select null
from ff_database_items_tl fdit
where fdit.user_entity_id = fdi.user_entity_id
and fdit.user_name = fdit.user_name);
DELETE ff_compiled_info_f
WHERE formula_id in (
SELECT /*+ ORDERED USE_NL(BG FDU FDT FUE BG2) */
distinct f.formula_id
FROM ff_formulas_f f,
per_business_groups_perf bg,
ff_fdi_usages_f fdu,
ff_database_items fdt,
ff_user_entities fue,
per_business_groups_perf bg2
where (f.legislation_code = 'JP' or f.business_group_id is not null)
and bg.business_group_id(+) = f.business_group_id
and nvl(f.legislation_code, bg.legislation_code) = 'JP'
and fdu.formula_id = f.formula_id
and fdu.effective_start_date = f.effective_start_date
and fdu.effective_end_date = f.effective_end_date
and fdu.usage = 'D'
and fdt.user_name = fdu.item_name
and fue.user_entity_id = fdt.user_entity_id
-- bug.5758299
and (
(fue.legislation_code = 'JP' and fue.creator_type in ('E', 'I', 'CUST', 'S')
or (fue.creator_type = 'B'))
)
and bg2.business_group_id(+) = fue.business_group_id
and nvl(fue.legislation_code, bg2.legislation_code) = 'JP');
DELETE ff_fdi_usages_f
WHERE formula_id in (
SELECT /*+ ORDERED USE_NL(BG FDU FDT FUE BG2) */
distinct f.formula_id
FROM ff_formulas_f f,
per_business_groups_perf bg,
ff_fdi_usages_f fdu,
ff_database_items fdt,
ff_user_entities fue,
per_business_groups_perf bg2
where (f.legislation_code = 'JP' or f.business_group_id is not null)
and bg.business_group_id(+) = f.business_group_id
and nvl(f.legislation_code, bg.legislation_code) = 'JP'
and fdu.formula_id = f.formula_id
and fdu.effective_start_date = f.effective_start_date
and fdu.effective_end_date = f.effective_end_date
and fdu.usage = 'D'
and fdt.user_name = fdu.item_name
and fue.user_entity_id = fdt.user_entity_id
-- bug.5758299
and (
(fue.legislation_code = 'JP' and fue.creator_type in ('E', 'I', 'CUST', 'S')
or (fue.creator_type = 'B'))
)
and bg2.business_group_id(+) = fue.business_group_id
and nvl(fue.legislation_code, bg2.legislation_code) = 'JP');
DELETE ff_user_entities
WHERE ( legislation_code = 'JP'
and creator_type in ('E', 'I', 'B', 'RB', 'CUST', 'S'))
OR ( business_group_id IN (
SELECT business_group_id
FROM per_business_groups_perf
WHERE legislation_code = 'JP')
and creator_type in ('B', 'RB'));
INSERT INTO pay_patch_status
(id
,patch_number
,patch_name
,phase
,applied_date
,legislation_code
,process_type)
SELECT pay_patch_status_s.nextval
,5758299
,'JP_UPGRADE_R12'
,'C'
,sysdate
,'JP'
,'9737155,12566233'
FROM dual;
delete from ff_database_items_tl
where rowid = l_csr_corrupt_dbi_tl.row_id;
ff_database_items_pkg.insert_tl_rows(
x_user_name => l_csr_corrupt_dbi.user_name,
x_user_entity_id => l_csr_corrupt_dbi.user_entity_id,
x_language => userenv('LANG'),
x_translated_user_name => l_csr_corrupt_dbi.user_name,
x_description => l_csr_corrupt_dbi.description);
update pay_patch_status
set process_type = decode(nvl(rec_del_dtls.process_type,'X'),'X',null,rec_del_dtls.process_type||',')||'9737155'
where id = rec_del_dtls.id;
END delete_dbi;
select /*+ ORDERED */
hoi.rowid row_id
from per_business_groups_perf pbg,
hr_all_organization_units hou,
hr_organization_information hoi
where pbg.legislation_code = 'JP'
and hou.business_group_id = pbg.business_group_id
and hoi.organization_id = hou.organization_id
and hoi.org_information_context = p_org_information_context
and hoi.org_information3 = p_org_information3_o;
update hr_organization_information
set org_information3 = p_org_information3_n
where rowid = l_csr_org_df.row_id;
hr_utility.trace('update cnt : '||to_char(l_cnt));
select /*+ ORDERED */
count(hoi.org_information_id)
from per_business_groups_perf pbg,
hr_all_organization_units hou,
hr_organization_information hoi
where pbg.legislation_code = 'JP'
and hou.business_group_id = pbg.business_group_id
and hoi.organization_id = hou.organization_id
and hoi.org_information_context in (
'JP_LI_GIP_INFO',
'JP_LI_LINC_INFO',
'JP_ACCIDENT_INS_INFO')
and hoi.org_information3 in (
'JP_LI_GIP_PREM',
'JP_LI_LINC_PREM',
'JP_AI_PREM_CALC');
select 'Y'
from hr_legislation_installations hli
where hli.application_short_name = 'PER'
and hli.legislation_code = 'JP'
and nvl(hli.status,'X') = 'I'
and not exists(
select null
from hr_legislation_installations hli2
where hli2.application_short_name = 'PAY'
and hli2.legislation_code = 'JP'
and nvl(hli2.status,'X') = 'I');
select 'Y'
from pay_patch_status
where patch_number = 5758299
and patch_name = 'JP_UPGRADE_R12'
and phase = 'C'
and legislation_code = 'JP';
select count(pet.element_type_id)
from pay_element_types_f pet
where pet.legislation_code = 'JP'
and ascii(pet.element_name) <= 127
and pet.element_name not in (
'BON_HI_PREM_ROUNDING_ERROR',
'BON_HI_STD_BON_ACMLT',
'SAL_HI_PREM_ROUNDING_ERROR',
'YEA_ANNUAL_TAX_OVERRIDE',
'YEA_HOUSING_LOAN_INFO',
'Yea Insurance Deduction Form')
and exists(
select null
from pay_element_types_f pet2
where pet2.legislation_code = 'JP'
and ascii(pet2.element_name) > 127
and pet2.description not like '%Obsoleted');
select count(pec.classification_id)
from pay_element_classifications pec
where pec.legislation_code = 'JP'
and ascii(substr(pec.classification_name,8,1)) <= 127
and exists(
select null
from pay_element_classifications pec2
where pec2.legislation_code = 'JP'
and ascii(substr(pec2.classification_name,8,1)) > 127);
select count(pbt.balance_type_id)
from pay_balance_types pbt
where pbt.legislation_code = 'JP'
and ascii(substr(pbt.balance_name,3,1)) <= 127
and pbt.balance_name not in (
'B_BON_HI_STD_BON',
'B_BON_HI_STD_BON_ACMLT',
'B_COM_HI_PREM_EE_ROUNDING_ERROR',
'B_COM_HI_PREM_EE_TRM_ROUNDING_ERROR',
'B_COM_HI_PREM_ROUNDING_ERROR',
'B_COM_HI_PREM_TRM_ROUNDING_ERROR',
'B_PAYSLIP_NET_PAY',
'B_YEA_SAL_DCT_EARTHQUAKE_INS_PREM')
and exists(
select null
from pay_balance_types pbt2
where pbt2.legislation_code = 'JP'
and ascii(substr(pbt2.balance_name,3,1)) > 127
and pbt2.reporting_name not like '%Obsoleted');
select count(pbd.balance_dimension_id)
from pay_balance_dimensions pbd
where pbd.legislation_code = 'JP'
and ascii(substr(pbd.dimension_name,3,1)) <= 127
and pbd.dimension_name not in (
'_ASG_ADV_RETRO_RUN',
'_ASG_APRTD EFFECTIVE_DATE 01-04 RESET 01')
and exists(
select null
from pay_balance_dimensions pbd2
where pbd2.legislation_code = 'JP'
and ascii(substr(pbd2.dimension_name,3,1)) > 127);
select count(pes.element_set_id)
from pay_element_sets pes
where pes.legislation_code = 'JP'
and ascii(pes.element_set_name) <= 127
and exists(
select null
from pay_element_sets pes2
where pes2.legislation_code = 'JP'
and ascii(pes2.element_set_name) > 127);
select count(fg.global_id)
from ff_globals_f fg
where fg.legislation_code = 'JP'
and ascii(substr(fg.global_name,3,1)) <= 127
and fg.global_name not in (
'G_TRM_LTX_SP_WITHHOLD_DCTBL_RATE',
'G_TRM_LTX_SP_WITHHOLD_MUNICIPAL_TAX_RATE',
'G_TRM_LTX_SP_WITHHOLD_PREFECTURAL_TAX_RATE')
and exists(
select null
from ff_globals_f fg2
where fg2.legislation_code = 'JP'
and ascii(substr(fg2.global_name,3,1)) > 127
and fg2.global_description not like '%Obsoleted');
select count(ff.formula_id)
from ff_formulas_f ff
where ff.legislation_code = 'JP'
and ascii(substr(ff.formula_name,3,1)) <= 127
and ff.formula_name not in (
'HI_PREM_ROUNDING_TYPE_VALIDATION',
'JP_AI_PREM_CALC',
'JP_AI_PREM_CALC',
'JP_LI_GIP_PREM',
'JP_LI_LINC_PREM')
and exists(
select null
from ff_formulas_f ff2
where ff2.legislation_code = 'JP'
and ascii(substr(ff2.formula_name,3,1)) > 127
and ff2.description not like '%Obsoleted');
select count(pmu.monetary_unit_id)
from pay_monetary_units pmu
where pmu.legislation_code = 'JP'
and ascii(substr(pmu.monetary_unit_name,length(pmu.monetary_unit_name),1)) <= 127
and exists(
select null
from pay_monetary_units pmu2
where pmu2.legislation_code = 'JP'
and ascii(substr(pmu2.monetary_unit_name,length(pmu2.monetary_unit_name),1)) > 127);
select count(put.user_table_id)
from pay_user_tables put
where put.legislation_code = 'JP'
and ascii(substr(put.user_table_name,3,1)) <= 127
and exists(
select null
from pay_user_tables put2
where put2.legislation_code = 'JP'
and ascii(substr(put2.user_table_name,3,1)) > 127);
select fg.global_id,
fg.global_name,
fg.global_description
from ff_globals_f fg
where fg.global_name = substrb(p_glb_name,1,80-4)||'_OBS'
and fg.global_description like '% (Obsolete)'
and fg.legislation_code = 'JP'
and fg.effective_start_date = (
select max(fg2.effective_start_date)
from ff_globals_f fg2
where fg2.global_id = fg.global_id);
update pay_element_types_f
set element_name = substrb(element_name,1,80-4)||'_OBS',
description = substrb(description,1,240-11)||' (Obsolete)'
where element_name = l_elm_tbl(l_elm_ind)
and legislation_code = 'JP';
update pay_element_classifications
set classification_name = substrb(classification_name,1,80-4)||'_OBS',
description = substrb(description,1,80-11)||' (Obsolete)'
where classification_name = l_class_tbl(l_class_ind)
and legislation_code = 'JP';
update pay_balance_types
set balance_name = substrb(balance_name,1,80-4)||'_OBS',
reporting_name = substrb(reporting_name,1,80-11)||' (Obsolete)'
where balance_name = l_bal_tbl(l_bal_ind)
and legislation_code = 'JP';
update pay_balance_dimensions
set dimension_name = substrb(dimension_name,1,80-4)||'_OBS',
database_item_suffix = substrb(database_item_suffix,1,80-4)||'_OBS',
description = substrb(description,1,240-11)||' (Obsolete)'
where dimension_name = l_dim_tbl(l_dim_ind)
and legislation_code = 'JP';
update pay_element_sets
set element_set_name = substrb(element_set_name,1,80-4)||'_OBS'
where element_set_name = l_eset_tbl(l_eset_ind)
and legislation_code = 'JP';
update ff_globals_f
set global_name = substrb(global_name,1,80-4)||'_OBS',
global_description = substrb(global_description,1,240-11)||' (Obsolete)'
where global_name = l_glb_tbl(l_glb_ind)
and legislation_code = 'JP';
update ff_formulas_f
set formula_name = substrb(formula_name,1,80-4)||'_OBS',
description = substrb(description,1,240-11)||' (Obsolete)'
where formula_name = l_ff_tbl(l_ff_ind)
and legislation_code = 'JP';
update pay_monetary_units
set monetary_unit_name = substrb(monetary_unit_name,1,80-4)||'_OBS'
where monetary_unit_name = l_mon_tbl(l_mon_ind)
and legislation_code = 'JP';
update pay_user_tables
set user_table_name = substrb(user_table_name,1,80-4)||'_OBS',
user_row_title = substrb(user_row_title,1,80-4)||'_OBS'
where user_table_name = l_udt_tbl(l_udt_ind)
and legislation_code = 'JP';
select 'Y'
from hr_legislation_installations hli
where hli.application_short_name = 'PAY'
and hli.legislation_code = 'JP'
and nvl(hli.status,'X') = 'I';
select 'Y' run_status,
process_type
from pay_patch_status
where patch_number = 5758299
and patch_name = 'JP_UPGRADE_R12'
and phase = 'C'
and legislation_code = 'JP';
select /*+ ORDERED */
count(fdit.user_name)
from ff_user_entities fue,
ff_database_items fdi,
ff_database_items_tl fdit
where (fue.user_entity_name like 'T_SAL_ITX_DAY1_KOU%'
or fue.user_entity_name like 'T_SAL_ITX_DAY2_KOU%BASIC%')
and fue.legislation_code = 'JP'
and fdi.user_entity_id = fue.user_entity_id
and fdit.user_entity_id = fdi.user_entity_id
and fdit.user_name = fdi.user_name
and fdit.language = 'JA'
and ascii(substr(fdit.translated_user_name,3,1)) > 127
and nvl(to_single_byte(replace(replace(replace(replace(replace(fue.user_entity_name,
'T_SAL_ITX_DAY1_KOU',''),
'T_SAL_ITX_DAY2_KOU',''),
'_BASIC_ITX_E2',''),
'_ITX_E2',''),
'_BASIC_AMT_E2','')),-1)
<> nvl(to_single_byte(replace(replace(replace(replace(replace(replace(replace(replace(fdit.translated_user_name,
hr_jp_standard_pkg.sjhextochar('5481518B8B81518CB990F292A58EFB90C58A7A955C815193FA8A7A955C315F8D629793'),''),
hr_jp_standard_pkg.sjhextochar('5481518B8B81518CB990F292A58EFB90C58A7A955C815193FA8A7A955C325F8D629793'),''),
hr_jp_standard_pkg.sjhextochar('906C81518AEE8F8090C58A7A5F8DC58FAC'),''),
hr_jp_standard_pkg.sjhextochar('906C81518AEE8F8090C58A7A5F8DC591E5'),''),
hr_jp_standard_pkg.sjhextochar('906C815190C58A7A5F8DC58FAC'),''),
hr_jp_standard_pkg.sjhextochar('906C815190C58A7A5F8DC591E5'),''),
hr_jp_standard_pkg.sjhextochar('81518AEE8F808A7A5F8DC58FAC'),''),
hr_jp_standard_pkg.sjhextochar('81518AEE8F808A7A5F8DC591E5'),'')),-1);
update ff_database_items_tl ufdit
set ufdit.translated_user_name = ufdit.user_name,
ufdit.last_updated_by = -1,
ufdit.last_update_login = -1,
ufdit.last_update_date = sysdate
where ufdit.rowid in (
select /*+ ORDERD */
fdit.rowid
from ff_user_entities fue,
ff_database_items fdi,
ff_database_items_tl fdit
where (fue.user_entity_name like 'T_SAL_ITX_DAY1_KOU%'
or fue.user_entity_name like 'T_SAL_ITX_DAY2_KOU%BASIC%')
and fue.legislation_code = 'JP'
and fdi.user_entity_id = fue.user_entity_id
and fdit.user_entity_id = fdi.user_entity_id
and fdit.user_name = fdi.user_name
and fdit.language = 'JA'
and ascii(substr(fdit.translated_user_name,3,1)) > 127
and nvl(to_single_byte(replace(replace(replace(replace(replace(fue.user_entity_name,
'T_SAL_ITX_DAY1_KOU',''),
'T_SAL_ITX_DAY2_KOU',''),
'_BASIC_ITX_E2',''),
'_ITX_E2',''),
'_BASIC_AMT_E2','')),-1)
<> nvl(to_single_byte(replace(replace(replace(replace(replace(replace(replace(replace(fdit.translated_user_name,
hr_jp_standard_pkg.sjhextochar('5481518B8B81518CB990F292A58EFB90C58A7A955C815193FA8A7A955C315F8D629793'),''),
hr_jp_standard_pkg.sjhextochar('5481518B8B81518CB990F292A58EFB90C58A7A955C815193FA8A7A955C325F8D629793'),''),
hr_jp_standard_pkg.sjhextochar('906C81518AEE8F8090C58A7A5F8DC58FAC'),''),
hr_jp_standard_pkg.sjhextochar('906C81518AEE8F8090C58A7A5F8DC591E5'),''),
hr_jp_standard_pkg.sjhextochar('906C815190C58A7A5F8DC58FAC'),''),
hr_jp_standard_pkg.sjhextochar('906C815190C58A7A5F8DC591E5'),''),
hr_jp_standard_pkg.sjhextochar('81518AEE8F808A7A5F8DC58FAC'),''),
hr_jp_standard_pkg.sjhextochar('81518AEE8F808A7A5F8DC591E5'),'')),-1));
update pay_patch_status
set process_type = decode(nvl(process_type,'X'),'X',null,process_type||',')||'12566233'
where patch_number = 5758299
and patch_name = 'JP_UPGRADE_R12'
and phase = 'C'
and legislation_code = 'JP';
SELECT COUNT(1)
INTO l_count
FROM pay_input_values_f
WHERE legislation_code = 'JP'
AND ( ASCII(name) > 127 OR SUBSTR(name,1,1) = '2');
SELECT COUNT(1)
INTO l_count
FROM pay_element_types_f
WHERE legislation_code = 'JP'
AND ASCII(element_name) > 127
AND description NOT LIKE '%Obsoleted';
SELECT COUNT(1)
INTO l_count
FROM pay_element_classifications
WHERE legislation_code = 'JP'
AND ASCII(SUBSTR(classification_name,8,1)) > 127;
SELECT COUNT(1)
INTO l_count
FROM pay_balance_types
WHERE legislation_code = 'JP'
AND ASCII(SUBSTR(balance_name,3,1)) > 127
AND reporting_name NOT LIKE '%Obsoleted';
SELECT COUNT(1)
INTO l_count
FROM pay_balance_dimensions
WHERE legislation_code = 'JP'
AND ASCII(SUBSTR(dimension_name,3,1)) > 127;
SELECT COUNT(1)
INTO l_count
FROM pay_element_sets
WHERE legislation_code = 'JP'
AND ASCII(element_set_name) > 127;
SELECT COUNT(1)
INTO l_count
FROM ff_globals_f
WHERE legislation_code = 'JP'
AND ASCII(SUBSTR(global_name,3,1)) > 127
AND global_description NOT LIKE '%Obsoleted';
SELECT COUNT(1)
INTO l_count
FROM ff_formulas_f
WHERE legislation_code = 'JP'
AND ASCII(SUBSTR(formula_name,3,1)) > 127
AND description NOT LIKE '%Obsoleted';
SELECT COUNT(1)
INTO l_count
FROM pay_monetary_units
WHERE legislation_code = 'JP'
AND ASCII(SUBSTR(monetary_unit_name,LENGTH(monetary_unit_name),1)) > 127;
SELECT COUNT(1)
INTO l_count
FROM pay_user_columns
WHERE legislation_code = 'JP'
AND ASCII(user_column_name) > 127;
SELECT COUNT(1)
INTO l_count
FROM pay_user_rows_f
WHERE legislation_code = 'JP'
AND ASCII(row_low_range_or_name) > 127;
SELECT COUNT(1)
INTO l_count
FROM pay_user_tables
WHERE legislation_code = 'JP'
AND ASCII(SUBSTR(user_table_name,3,1)) > 127;
delete_dbi;