[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 meaning
FROM hr_lookups hrl
WHERE lookup_type = 'PQP_GAP_ENTITLEMENT_BANDS'
AND NVL(enabled_flag,'Y') = 'Y'
AND lookup_code like 'BAND%'
AND c_effective_date BETWEEN hrl.start_date_active
AND nvl(hrl.end_date_active,hr_api.g_eot);
SELECT 'X'
FROM pay_element_type_extra_info
WHERE eei_information1 = fnd_number.number_to_canonical(p_plan_id)
AND eei_information17 = 'Y'
AND information_type = 'PQP_GB_OMP_ABSENCE_PLAN_INFO'
AND rownum = 1;
SELECT template_id
FROM PAY_ELEMENT_TEMPLATES
WHERE template_name = p_template_name
AND legislation_code = p_leg_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_bg_id;
SELECT ptco.core_object_id
FROM pay_shadow_balance_types psbt,
pay_template_core_objects ptco
WHERE psbt.template_id = l_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
) 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;
SELECT formula_id
FROM pay_shadow_formulas
WHERE formula_name = p_formula_name
AND template_type = 'T';
PROCEDURE update_ipval_defval(p_ele_name IN VARCHAR2
,p_ip_name IN VARCHAR2
,p_def_value IN VARCHAR2)
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 user_table_id
FROM pay_user_tables
WHERE user_table_name = p_udt_name
AND (business_group_id = p_bg_id OR
business_group_id IS NULL);
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_bg_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_bg_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 '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;
SELECT 'X'
FROM fnd_lookup_values_vl
WHERE lookup_type = p_lookup_type
AND lookup_code = c_lookup_code
AND security_group_id = p_security_group_id
AND view_application_id = 3;
SELECT 'X'
FROM fnd_lookup_values_vl
WHERE lookup_type = p_lookup_type
AND meaning = c_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' --'S'
,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
);
pay_shadow_element_api.update_shadow_element
(p_validate => false
,p_effective_date => p_effective_start_date
,p_element_type_id => l_element_type_id
,p_element_name => l_ele_name(i)
,p_reporting_name => l_ele_reporting_name(i)
,p_description => l_ele_description(i)
,p_relative_processing_priority => l_ele_pp(i)
,p_object_version_number => l_ele_obj_ver_number
);
pay_shadow_element_api.update_shadow_element
(p_validate => false
,p_effective_date => p_effective_start_date
,p_element_type_id => l_element_type_id
,p_element_name => l_ele_new_name(i)
,p_relative_processing_priority => l_ele_pp(i)
,p_object_version_number => l_ele_obj_ver_number
);
update_ele_retro_info (p_main_ele_name => l_main_ele_name(i)
,p_retro_ele_name => l_retro_ele_name(i)
);
update_ipval_defval (p_ele_name => l_main_ele_name(1)
,p_ip_name => 'Pay Component Rate Type'
,p_def_value => p_pay_src_pay_component
);
l_udt_cols.DELETE;
l_udt_rows.DELETE;
l_udt_cols.DELETE;
l_udt_rows.DELETE;
PROCEDURE delete_user_template
(p_plan_id IN NUMBER
,p_business_group_id IN NUMBER
,p_pri_ele_name IN VARCHAR2
,p_abse_ele_type_id IN NUMBER
,p_abse_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_proc_name || 'delete_user_template';
SELECT substr(pee.eei_information9,2,1) entitlements_uom
,pee.eei_information13 daily_rate_uom
FROM pay_element_type_extra_info pee
WHERE element_type_id = p_ele_type_id
AND information_type = 'PQP_GB_OMP_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_information11) entitlement_udt
,fnd_number.canonical_to_number(eei_information12) calendar_udt
,eei_information20 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_information11 = fnd_number.number_to_canonical(c_udt_id)
AND information_type = 'PQP_GB_OMP_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_information12 = fnd_number.number_to_canonical(c_udt_id)
AND information_type = 'PQP_GB_OMP_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_information20 = c_lookup_type
AND information_type = 'PQP_GB_OMP_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 = c_template_name --'PQP OMP Template'
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 ||c_days_hours|| ' OMP 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_OMP_ABSENCE_PLAN_INFO';
SELECT template_id
FROM pay_element_templates
WHERE base_name = p_pri_ele_name
AND template_name = p_template_name --'PQP OMP Template'
AND business_group_id = p_business_group_id
AND template_type = 'U';
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;
SELECT DISTINCT(pete.eei_information20) 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_OMP_ABSENCE_PLAN_INFO'
AND pete.eei_information17 = 'Y'
AND petf.element_name = c_base_name||p_days_hours|| ' OMP Absence'
-- pet.base_name ||p_days_hours|| ' OMP Absence'
AND petf.business_group_id = p_business_group_id
--AND pet.template_name = p_template_name --'PQP OMP Template'
--AND pet.template_type = 'U'
--AND pet.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 ;
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_proc_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) 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_proc_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;
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_entudt_id);
delete_udt (p_udt_id => l_caludt_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
);
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_base_name => p_pri_ele_name
--,p_abs_type => ' OMP '||l_days_hours
,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;
pay_element_links_pkg.insert_row(
p_rowid => l_rowid,
p_element_link_id => l_element_link_id ,
p_effective_start_date => p_effective_start_date,
p_effective_end_date => l_effective_end_date ,
p_payroll_id => NULL,
p_job_id => NULL,
p_position_id => NULL,
p_people_group_id => NULL,
p_cost_allocation_keyflex_id => NULL,
p_organization_id => NULL,
p_element_type_id => i.element_type_id ,
p_location_id => NULL,
p_grade_id => NULL,
p_balancing_keyflex_id => NULL,
p_business_group_id => p_business_group_id,
p_legislation_code => NULL, --p_legislation_code,
p_element_set_id => NULL,
p_pay_basis_id => NULL,
p_costable_type => 'N',
p_link_to_all_payrolls_flag => 'N',
p_multiply_value_flag => 'N',
p_standard_link_flag => 'N',
p_transfer_to_gl_flag => 'N',
p_comment_id => NULL,
p_employment_category => NULL,
p_qualifying_age => NULL,
p_qualifying_length_of_service => NULL,
p_qualifying_units => NULL,
p_attribute_category => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute16 => NULL,
p_attribute17 => NULL,
p_attribute18 => NULL,
p_attribute19 => NULL,
p_attribute20 => NULL ) ;
PROCEDURE delete_element_links
( p_business_group_id IN NUMBER
,p_effective_start_date IN DATE
,p_effective_end_date IN DATE
--,p_base_name IN VARCHAR2
--,p_abs_type IN VARCHAR2
,p_template_id IN NUMBER
) IS
--l_link_ele_name t_ele_name ;
SELECT rowid, element_link_id, people_group_id
FROM pay_element_links_f
WHERE element_type_id = p_element_type_id ;
pay_element_links_pkg.delete_row
(
p_rowid => l_rowid
,p_element_link_id => l_element_link_id
,p_delete_mode => 'ZAP'
,p_session_date => p_effective_start_date
,p_validation_start_date => p_effective_start_date
,p_validation_end_date => p_effective_end_date
,p_effective_start_date => p_effective_start_date
,p_business_group_id => p_business_group_id
,p_people_group_id => l_people_group_id
) ;
END delete_element_links ;
SELECT 'X'
FROM ben_acty_base_rt_f
WHERE pl_id = p_plan_id
AND element_type_id = p_element_type_id
AND business_group_id = p_business_group_id ;