[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT template_id
FROM pay_element_templates
WHERE template_name = p_template_name
AND legislation_code = p_legislation_code
AND template_type = 'T'
AND business_group_id is NULL;
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name = c_object_name
AND business_group_id = p_business_group_id;
SELECT ptco.core_object_id
FROM pay_shadow_balance_types psbt,
pay_template_core_objects ptco
WHERE psbt.template_id = p_template_id
AND psbt.balance_name = c_object_name
AND ptco.template_id = psbt.template_id
AND ptco.shadow_object_id = psbt.balance_type_id;
PROCEDURE update_ele_retro_info (p_main_ele_name in varchar2
,p_retro_ele_name in varchar2
,p_business_group_id in number
,p_template_id in number
) IS
--
l_main_ele_type_id pay_element_types_f.element_type_id%TYPE;
'update_ele_retro_info';
UPDATE pay_element_types_f
SET retro_summ_ele_id = l_retro_ele_type_id
WHERE element_type_id = l_main_ele_type_id;
END update_ele_retro_info;
PROCEDURE update_ipval_defval(p_ele_name IN VARCHAR2
,p_ip_name IN VARCHAR2
,p_def_value IN VARCHAR2
,p_bg_id IN NUMBER)
IS
CURSOR csr_getinput(c_ele_name varchar2
,c_iv_name varchar2)
IS
SELECT input_value_id
,piv.name
,piv.element_type_id
FROM pay_input_values_f piv
,pay_element_types_f pet
WHERE element_name = c_ele_name
AND piv.element_type_id = pet.element_type_id
AND (piv.business_group_id = p_bg_id OR piv.business_group_id IS NULL)
AND piv.name = c_iv_name
AND (piv.legislation_code = 'GB' OR piv.legislation_code IS NULL);
SELECT rowid
FROM pay_input_values_f
WHERE input_value_id = c_ip_id
AND element_type_id = c_element_type_id
FOR UPDATE NOWAIT;
'update_ipval_defval';
UPDATE pay_input_values_f
SET default_value = p_def_value
WHERE rowid = csr_updinput_rec.rowid;
END update_ipval_defval;
SELECT meaning
FROM hr_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code like p_lookup_code
AND enabled_flag = 'Y'
ORDER BY lookup_code;
p_udt_cols.delete;
SELECT pay_user_rows_s.NEXTVAL
FROM dual;
pay_user_tables_pkg.insert_row
(p_rowid => l_udt_rowid
,p_user_table_id => l_user_table_id
,p_business_group_id => p_business_group_id
,p_legislation_code => NULL
,p_legislation_subgroup => NULL
,p_range_or_match => p_udt_type.range_or_match
,p_user_key_units => p_udt_type.user_key_units
,p_user_table_name => p_udt_type.user_table_name
,p_user_row_title => p_udt_type.user_row_title
);
pay_user_columns_pkg.insert_row
(p_rowid => l_udt_cols_rowid
,p_user_column_id => l_user_column_id
,p_user_table_id => l_user_table_id
,p_business_group_id => p_udt_cols(i).business_group_id
,p_legislation_code => p_udt_cols(i).legislation_code
,p_legislation_subgroup => NULL
,p_user_column_name => p_udt_cols(i).user_column_name
,p_formula_id => p_udt_cols(i).formula_id
);
pay_user_rows_pkg.pre_insert
(p_rowid => l_udt_rows_rowid
,p_user_table_id => l_user_table_id
,p_row_low_range_or_name => p_udt_rows(i).row_low_range_or_name
,p_user_row_id => l_user_row_id
,p_business_group_id => p_business_group_id
);
INSERT INTO pay_user_rows_f
(user_row_id
,effective_start_date
,effective_end_date
,business_group_id
,legislation_code
,user_table_id
,row_low_range_or_name
,display_sequence
,legislation_subgroup
,row_high_range
)
VALUES
(l_user_row_id
,p_effective_start_date
,nvl(p_effective_end_date, hr_api.g_eot)
,p_udt_rows(i).business_group_id
,p_udt_rows(i).legislation_code
,l_user_table_id
,p_udt_rows(i).row_low_range_or_name
,p_udt_rows(i).display_sequence
,NULL
,p_udt_rows(i).row_high_range
);
SELECT 'X'
FROM fnd_lookup_types_vl
WHERE lookup_type = p_lookup_type
AND security_group_id = p_security_group_id
AND view_application_id = 3;
SELECT 'X'
FROM fnd_lookup_types_vl
WHERE meaning = p_lookup_meaning
AND security_group_id = p_security_group_id
AND view_application_id = 3;
fnd_lookup_types_pkg.insert_row
(
x_rowid => l_rowid
,x_lookup_type => p_lookup_type
,x_security_group_id => p_security_group_id
,x_view_application_id => 3
,x_application_id => 800
,x_customization_level => 'U'
,x_meaning => p_lookup_meaning
,x_description => NULL
,x_creation_date => SYSDATE
,x_created_by => l_user_id
,x_last_update_date => SYSDATE
,x_last_updated_by => l_user_id
,x_last_update_login => l_login_id
);
fnd_lookup_values_pkg.insert_row
(
x_rowid => l_rowid
,x_lookup_type => p_lookup_type
,x_security_group_id => p_security_group_id
,x_view_application_id => 3
,x_lookup_code => fnd_number.number_to_canonical(
p_lookup_values(i).abs_type_id)
,x_tag => NULL
,x_attribute_category => NULL
,x_attribute1 => NULL
,x_attribute2 => NULL
,x_attribute3 => NULL
,x_attribute4 => NULL
,x_attribute5 => NULL
,x_attribute6 => NULL
,x_attribute7 => NULL
,x_attribute8 => NULL
,x_attribute9 => NULL
,x_attribute10 => NULL
,x_attribute11 => NULL
,x_attribute12 => NULL
,x_attribute13 => NULL
,x_attribute14 => NULL
,x_attribute15 => NULL
,x_enabled_flag => 'Y'
,x_start_date_active => p_effective_start_date
,x_end_date_active => NULL
,x_territory_code => NULL
,x_meaning => p_lookup_values(i).abs_type_name
,x_description => NULL
,x_creation_date => SYSDATE
,x_created_by => l_user_id
,x_last_update_date => SYSDATE
,x_last_updated_by => l_user_id
,x_last_update_login => l_login_id
);
SELECT element_type_id
,object_version_number
FROM pay_shadow_element_types
WHERE template_id = l_template_id
AND element_name = c_ele_name;
SELECT balance_type_id
,object_version_number
FROM pay_shadow_balance_types
WHERE template_id = l_template_id
AND balance_name = c_bal_name;
SELECT 'X'
FROM pay_element_type_extra_info
WHERE eei_information1 = fnd_number.number_to_canonical(p_plan_id)
AND eei_information16 = 'Y'
AND information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
AND rownum = 1;
pay_shadow_element_api.update_shadow_element
(p_validate => false
,p_effective_date => p_ele_eff_start_date
,p_element_type_id => l_element_type_id
,p_element_name => l_ele_name(l_idx)
,p_reporting_name => l_ele_reporting_name(l_idx)
,p_description => l_ele_description(l_idx)
,p_relative_processing_priority => l_ele_pp(l_idx)
,p_object_version_number => l_ele_obj_ver_number
);
pay_shadow_element_api.update_shadow_element
(p_validate => false
,p_effective_date => p_ele_eff_start_date
,p_element_type_id => l_element_type_id
,p_element_name => l_ele_new_name(l_idx)
,p_relative_processing_priority => l_ele_pp(l_idx)
,p_object_version_number => l_ele_obj_ver_number
);
update_ele_retro_info
(p_main_ele_name => l_main_ele_name(l_idx)
,p_retro_ele_name => l_retro_ele_name(l_idx)
,p_business_group_id => p_bg_id
,p_template_id => l_template_id
);
update_ipval_defval (p_ele_name => l_main_ele_name(l_main_ele_name.FIRST)
,p_ip_name => 'Pay Component Rate Type'
,p_def_value => p_pay_src_pay_component
,p_bg_id => p_bg_id
);
l_udt_cols.DELETE;
l_udt_rows.DELETE;
SELECT DISTINCT(pete.eei_information18) lookup_type
FROM pay_element_type_extra_info pete
,pay_element_types_f petf
-- ,pay_element_templates pet
WHERE pete.element_type_id = petf.element_type_id
AND pete.information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
AND pete.eei_information16 = 'Y'
AND petf.element_name = c_base_name || ' Unpaid Absence'
AND petf.business_group_id = p_business_group_id
;
SELECT pet.base_name
FROM pay_element_templates pet
WHERE pet.template_name = p_template_name
AND pet.template_type = 'U'
AND pet.business_group_id = p_business_group_id ;
SELECT lookup_code
FROM fnd_lookup_values_vl
WHERE lookup_type = c_lookup_type
AND security_group_id = p_security_group_id
AND view_application_id = 3;
p_lookup_collection.delete;
PROCEDURE delete_lookup (p_lookup_type in varchar2
,p_security_group_id in number
,p_view_application_id in number
,p_lookup_collection in t_number)
IS
--
CURSOR csr_get_lkt_info
IS
SELECT 'X'
FROM fnd_lookup_types_vl
WHERE lookup_type = p_lookup_type
AND security_group_id = p_security_group_id
AND view_application_id = p_view_application_id;
SELECT lookup_code
FROM fnd_lookup_values_vl
WHERE lookup_type = p_lookup_type
AND security_group_id = p_security_group_id
AND view_application_id = p_view_application_id;
l_proc_name VARCHAR2(72) := g_package_name || 'delete_lookup';
fnd_lookup_values_pkg.delete_row
(x_lookup_type => 'PQP_GAP_ABSENCE_TYPES_LIST'
,x_security_group_id => p_security_group_id
,x_view_application_id => p_view_application_id
,x_lookup_code => l_lookup_code
);
fnd_lookup_values_pkg.delete_row
(x_lookup_type => p_lookup_type
,x_security_group_id => p_security_group_id
,x_view_application_id => p_view_application_id
,x_lookup_code => l_lookup_code
);
fnd_lookup_types_pkg.delete_row
(x_lookup_type => p_lookup_type
,x_security_group_id => p_security_group_id
,x_view_application_id => p_view_application_id
);
END delete_lookup;
PROCEDURE delete_udt (p_udt_id in number
,p_business_group_id in number)
IS
--
CURSOR csr_get_usr_table_id
IS
SELECT rowid
FROM pay_user_tables
WHERE user_table_id = p_udt_id
AND business_group_id = p_business_group_id;
SELECT user_column_id
FROM pay_user_columns
WHERE user_table_id = p_udt_id;
SELECT user_row_id
FROM pay_user_rows_f
WHERE user_table_id = p_udt_id;
l_proc_name VARCHAR(72) := g_package_name || 'delete_udt';
DELETE pay_user_column_instances_f
WHERE user_column_id = l_usr_col_id;
DELETE pay_user_columns
WHERE user_table_id = p_udt_id;
pay_user_rows_pkg.check_delete_row
(p_user_row_id => l_usr_row_id
,p_validation_start_date => NULL
,p_dt_delete_mode => 'ZAP'
);
DELETE pay_user_rows_f
WHERE user_row_id = l_usr_row_id;
pay_user_tables_pkg.delete_row
(p_rowid => l_rowid
,p_user_table_id => p_udt_id
);
END delete_udt;
PROCEDURE delete_user_template
(p_plan_id in number
,p_business_group_id in number
,p_abs_ele_name in varchar2
,p_abs_ele_type_id in number
,p_abs_primary_yn in varchar2
,p_security_group_id in number
,p_effective_date in date
) IS
--
l_template_id NUMBER(9);
l_proc_name varchar2(72) := g_package_name || 'delete_user_template';
SELECT pee.eei_information8 entitlements_uom
,pee.eei_information11 daily_rate_uom
FROM pay_element_type_extra_info pee
WHERE element_type_id = p_ele_type_id
AND information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO' ;
SELECT element_type_id
FROM pay_template_core_objects pet
,pay_element_types_f petf
WHERE pet.template_id = c_template_id
AND petf.element_type_id = pet.core_object_id
AND pet.core_object_type = 'ET';
SELECT element_type_extra_info_id
,fnd_number.canonical_to_number(eei_information9) entitlement_udt
,fnd_number.canonical_to_number(eei_information10) calendar_udt
,eei_information18 lookup_type
FROM pay_element_type_extra_info petei
WHERE element_type_id = c_element_type_id ;
SELECT 'X'
FROM pay_element_type_extra_info
WHERE eei_information1 <> fnd_number.number_to_canonical(p_plan_id)
AND eei_information9 = fnd_number.number_to_canonical(c_udt_id)
AND information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
AND rownum = 1;
SELECT 'X'
FROM pay_element_type_extra_info
WHERE eei_information1 <> fnd_number.number_to_canonical(p_plan_id)
AND eei_information10 = fnd_number.number_to_canonical(c_udt_id)
AND information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
AND rownum = 1;
SELECT 'X'
FROM pay_element_type_extra_info
WHERE eei_information1 <> fnd_number.number_to_canonical(p_plan_id)
AND eei_information18 = c_lookup_type
AND information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO'
AND rownum = 1;
SELECT 'X'
FROM pay_element_templates pets
,pay_shadow_element_types pset
,pay_template_core_objects ptco
,pay_element_type_extra_info peei
WHERE pets.template_id <> c_te_usrstr_id
-- For the given user structure
AND pets.template_name = p_template_name -- 'PQP OSP'
AND pets.template_type = 'U'
AND pets.business_group_id = p_business_group_id
AND pset.template_id = pets.template_id -- find the base element
AND pset.element_name = pets.base_name || ' Unpaid Absence'
AND ptco.template_id = pset.template_id -- For the base element
AND ptco.shadow_object_id = pset.element_type_id -- find the core element
AND ptco.core_object_type = 'ET'
AND ptco.core_object_id = peei.element_type_id -- For the core element
AND peei.eei_information1 = fnd_number.number_to_canonical(p_plan_id)
AND peei.information_type = 'PQP_GB_OSP_ABSENCE_PLAN_INFO';
SELECT template_id
FROM pay_element_templates
WHERE base_name = p_abs_ele_name
AND template_name = p_template_name --'PQP OSP'
AND business_group_id = p_business_group_id
AND template_type = 'U';
SELECT 'X'
FROM ben_acty_base_rt_f
WHERE pl_id = p_plan_id
AND element_type_id = c_element_type_id
AND business_group_id = p_business_group_id;
SELECT lookup_code
FROM fnd_lookup_values_vl
WHERE lookup_type = c_lookup_type
AND security_group_id = p_security_group_id
AND view_application_id = 3;
BEGIN -- delete_user_template
-- for Multi Messages
hr_multi_message.enable_message_list;
IF csr_get_eei_info%FOUND -- if an EIT exists only then delete else ignore
THEN
-- Delete the EEI row
l_proc_step := 50;
pay_element_extra_info_api.delete_element_extra_info
(p_validate => FALSE
,p_element_type_extra_info_id => l_eei_info_id
,p_object_version_number => l_ovn_eei);
delete_udt (p_udt_id => l_caludt_id
,p_business_group_id => p_business_group_id);
delete_lookup (p_lookup_type => l_lookup_type
,p_security_group_id => p_security_group_id
,p_view_application_id => 3
,p_lookup_collection => l_lookup_collection
);
fnd_lookup_types_pkg.delete_row
(x_lookup_type => 'PQP_GAP_ABSENCE_TYPES_LIST'
,x_security_group_id => p_security_group_id
,x_view_application_id => 3
);
pqp_gb_omp_template.delete_element_links
(p_business_group_id => p_business_group_id
,p_effective_start_date => p_effective_date
,p_effective_end_date => p_effective_date
,p_template_id => l_template_id
) ;
pay_element_template_api.delete_user_structure
(p_validate => false
,p_drop_formula_packages => true
,p_template_id => l_template_id);
END delete_user_template;