[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);
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)
SELECT pay_patch_status_s.nextval
,5758299
,'JP_UPGRADE_R12'
,'C'
,sysdate
,'JP'
FROM dual;
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 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;