[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 template_id
FROM pay_element_templates
WHERE template_name = l_template_name
AND legislation_code = p_legislation_code
AND template_type = 'T'
AND business_group_id is NULL;
SELECT meaning
FROM hr_lookups
WHERE lookup_type = 'PQP_US_ALIEN_INCOME_BALANCE'
AND lookup_code = p_ele_category;
SELECT balance_dimension_id
FROM pay_balance_dimensions
WHERE dimension_name = 'Assignment within Government Reporting Entity Run'
AND legislation_code = 'US';
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_description => p_ele_description
,p_reporting_name => p_ele_reporting_name
,p_processing_type => nvl(p_ele_processing_type, hr_api.g_varchar2)
,p_standard_link_flag => nvl(p_ele_standard_link, hr_api.g_varchar2)
,p_multiple_entries_allowed_fla => l_multiple_entries
,p_post_termination_rule => p_termination_rule
,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
,p_element_information8 => p_ele_ot_base
,p_element_information11 => p_flsa_hours
,p_object_version_number => l_ovn
);
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_description => 'Supp. SF element for:'||p_ele_name
,p_reporting_name => NVL(p_ele_reporting_name,p_ele_name)||':Supp SF' -- bug 5470399
,p_post_termination_rule => p_termination_rule
,p_element_information1 => nvl(p_supp_category, hr_api.g_varchar2)
,p_element_information8 => p_ele_ot_base
,p_object_version_number => l_ovn
);
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_description => 'Alien SF element for:'||p_ele_name
,p_post_termination_rule => p_termination_rule
,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
,p_element_information8 => p_ele_ot_base
,p_object_version_number => l_ovn
);
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_description => 'Generated Special Inputs element for:'
||p_ele_name
,p_post_termination_rule => p_termination_rule
,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
,p_element_information8 => p_ele_ot_base
,p_object_version_number => l_ovn
);
UPDATE pay_element_types_f
SET element_information10 = l_pri_bal_id
,element_information12 = l_hours_bal_id
,element_information13 = p_reduce_regular
,element_information14 = p_supp_category
,element_information16 = l_addl_bal_id
,element_information17 = l_repl_bal_id
,element_information18 = l_si_ele_type_id
,element_information19 = l_ssf_ele_type_id
,element_information20 = l_calc_type
WHERE element_type_id = l_pri_ele_type_id
AND business_group_id = p_bg_id;
/* The following update statement has been added by tmehra
for the balance architecture changes as per the US Payroll Team
*/
/*This statement is commented as per US Payroll team advice
due to performance issue */
/* Bug 3651755 : This update is not required. The category def will take
care of creating balance with save run balances to 'Yes'*/
/*UPDATE pay_defined_balances
SET save_run_balance = 'Y'
WHERE balance_type_id = l_pri_bal_id
AND balance_dimension_id = l_asg_gre_run_dim_id
AND business_group_id = p_bg_id;*/
UPDATE pay_element_types_f
SET element_information10 = l_supp_bal_id
WHERE element_type_id = l_ssf_ele_type_id
AND business_group_id = p_bg_id;
/* The following update statement has been added by tmehra
for the balance architecture changes as per the US Payroll Team
*/
/*This statement is commented as per US Payroll team advice
due to performance issue */
/* Bug 3651755 : This update is not required. The category def will take
care of creating balance with save run balances to 'Yes'*/
/* UPDATE pay_defined_balances
SET save_run_balance = 'Y'
WHERE balance_type_id = l_supp_bal_id
AND balance_dimension_id = l_asg_gre_run_dim_id
AND business_group_id = p_bg_id;*/
UPDATE pay_input_values_f
SET formula_id = t_form_id(i)
,warning_or_error = t_we_flag(i)
,default_value = t_def_val(i)
WHERE input_value_id = t_ipv_id(i);
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name = p_object_name
AND business_group_id = g_bg_id;
SELECT piv.input_value_id
FROM pay_input_values_f piv
WHERE piv.name = p_object_name
AND piv.element_type_id = p_object_id
AND piv.business_group_id = g_bg_id;
SELECT balance_type_id
FROM pay_balance_types pbt
WHERE pbt.balance_name = p_object_name
AND NVL(pbt.business_group_id, g_bg_id) = g_bg_id
AND NVL(pbt.legislation_code, g_legislation_code) = g_legislation_code;
SELECT distinct pbf.balance_type_id
FROM pay_balance_feeds_f pbf,
pay_balance_types pbt
WHERE p_ele_eff_start_date BETWEEN pbf.effective_start_date
AND pbf.effective_end_date
AND ((pbt.business_group_id is NULL AND pbt.legislation_code = 'US')
OR (pbt.business_group_id = g_bg_id AND pbt.legislation_code is NULL))
AND pbt.balance_name not in ('FLSA Earnings', 'FLSA Hours')
AND pbt.balance_type_id = pbf.balance_type_id
AND pbf.input_value_id IN
(SELECT piv.input_value_id
FROM pay_element_types_f pet,
pay_input_values_f piv
WHERE pet.element_name IN ('Regular Salary', 'Regular Wages')
AND p_ele_eff_start_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND pet.business_group_id is NULL
AND pet.legislation_code = 'US'
AND piv.element_type_id = pet.element_type_id
AND piv.name = 'Pay Value'
AND p_ele_eff_start_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND piv.business_group_id is NULL
AND piv.legislation_code = 'US') ;
PROCEDURE delete_ele_template_objects
(p_business_group_id in number
,p_ele_type_id in number
,p_ele_name in varchar2
,p_effective_date in date
) IS
--
l_template_id NUMBER(9);
l_proc VARCHAR2(60) := 'pqp_earnings_template.delete_ele_template_objects';
SELECT template_id
FROM pay_template_core_objects
WHERE core_object_type = 'ET'
AND core_object_id = p_ele_type_id;
pay_element_template_api.delete_user_structure
(p_validate => false
,p_drop_formula_packages => true
,p_template_id => l_template_id);
END delete_ele_template_objects;