[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 ptco.core_object_id
from pay_shadow_element_types psbt,
pay_template_core_objects ptco
where psbt.template_id = l_template_id
and psbt.element_name = c_element_name
and ptco.template_id = psbt.template_id
and ptco.shadow_object_id = psbt.element_type_id
and ptco.core_object_type = 'ET';
select input_value_id, name
from pay_input_values_f
where element_type_id = p_element_type_id
and name in ('Take Overlimit AT',
'AT Processing Order')
and business_group_id = p_bg_id ;
select input_value_id, name
from pay_input_values_f
where element_type_id = p_element_type_id
and name in ('Take Overlimit Roth',
'Roth Processing Order')
and business_group_id = p_bg_id ;
select 1
from ff_globals_f
where business_group_id = p_bg_id
and global_name = 'EMPLOYER_MATCH_PCT';
select 1
from ff_globals_f
where business_group_id = p_bg_id
and global_name = 'EMPLOYER_MATCH_LIMIT';
select benefit_classification_name
from ben_benefit_classifications
where benefit_classification_id = l_ben_class_id
and legislation_code = 'US';
select formula_id
from ff_formulas_f
where formula_name = 'US_ITERATIVE_PRETAX'
and legislation_code = 'US';
select pet.element_name
,pet.element_type_id
,piv.name
,piv.input_value_id
,piv.mandatory_flag
from pay_element_types_f pet
,pay_input_values_f piv
where (pet.element_name like c_ele_prefix||'% Special Inputs'
or
pet.element_name like c_ele_prefix||'% SI' )
and piv.element_type_id = pet.element_type_id
and piv.name ='Pay Value'
and pet.business_group_id = p_bg_id
and piv.business_group_id = p_bg_id
and c_effective_date between pet.effective_start_date
and pet.effective_end_date
and c_effective_date between piv.effective_start_date
and piv.effective_end_date;
select balance_dimension_id
from pay_balance_dimensions
where dimension_name = 'Assignment within Government Reporting Entity Run'
and legislation_code = 'US';
select balance_type_id
from pay_balance_types
where balance_name = p_jd_bal_name
and legislation_code = 'US';
select input_value_id
from pay_input_values_f
where element_type_id = p_element_type_id
and name = 'Catchup Processing'
and business_group_id = p_bg_id ;
select template_id,base_processing_priority
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 /*+ no_merge(BF) */
bc.classification_id
,bf.input_value_id
,bf.scale
,et.element_type_id
from pay_balance_classifications bc
, PAY_BALANCE_FEEDS_F BF
, PAY_BALANCE_TYPES BT
, PAY_INPUT_VALUES_F IV
, PAY_ELEMENT_TYPES_F ET
, PAY_ELEMENT_CLASSIFICATIONS EC
, HR_LOOKUPS HL
, HR_LOOKUPS HL2
, FND_SESSIONS SES
WHERE BT.BALANCE_TYPE_ID = BF.BALANCE_TYPE_ID
AND IV.INPUT_VALUE_ID = BF.INPUT_VALUE_ID
AND ET.ELEMENT_TYPE_ID = IV.ELEMENT_TYPE_ID
AND EC.CLASSIFICATION_ID = ET.CLASSIFICATION_ID
AND HL.LOOKUP_TYPE = 'ADD_SUBTRACT'
AND HL.LOOKUP_CODE = BF.SCALE
AND HL2.LOOKUP_TYPE = 'UNITS'
AND HL2.LOOKUP_CODE = IV.UOM
AND SES.SESSION_ID = USERENV('SESSIONID')
AND SES.EFFECTIVE_DATE BETWEEN BF.EFFECTIVE_START_DATE
AND BF.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN IV.EFFECTIVE_START_DATE
AND IV.EFFECTIVE_END_DATE
AND SES.EFFECTIVE_DATE BETWEEN ET.EFFECTIVE_START_DATE
AND ET.EFFECTIVE_END_DATE
AND BC.BALANCE_TYPE_ID = BF.BALANCE_TYPE_ID
AND nvl(EC.BALANCE_INITIALIZATION_FLAG , 'N') = 'N'
AND nvl(BF.BUSINESS_GROUP_ID, p_bg_id) = p_bg_id
AND nvl(BF.LEGISLATION_CODE, 'US') = 'US'
AND BT.BALANCE_NAME = 'Regular Earnings' ;
select balance_type_id
from pay_balance_types
where business_group_id = p_bg_id
and balance_name in (p_ele_name||' Eligible Comp',
p_ele_name||' Roth Eligible Comp',
p_ele_name||' AT Eligible Comp');
pay_balance_feeds_f_pkg.insert_row
(x_rowid => l_row_id,
x_balance_feed_id => l_balance_feed_id,
x_effective_start_date => p_ele_eff_start_date,
x_effective_end_date => hr_api.g_eot,
x_business_group_id => p_bg_id,
x_legislation_code => null,
x_balance_type_id => c2_rec.balance_type_id,
x_input_value_id => c1_rec.input_value_id,
x_scale => c1_rec.scale,
x_legislation_subgroup => null,
x_initial_balance_feed => false
);
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;
select input_value_id
from pay_input_values_f
where element_type_id = p_element_type_id
and name = 'Eligible Comp Option'
and business_group_id = p_bg_id ;
update pay_input_values_f
set default_value = 'S_EE'
,lookup_type = 'US_ELIGIBLE_COMP_OPTIONS_EE'
where input_value_id = l_eligicmp_ipv_id;
update pay_input_values_f
set default_value = substr(p_catchup_processing,1,1)
where input_value_id = l_cu_proc_inp_value_id;
update pay_input_values_f
set lookup_type = 'PQP_US_OVERLIMIT_AT1'
where input_value_id = temp_rec.input_value_id;
update pay_input_values_f
set lookup_type = 'PQP_US_AT_PROCESSING_ORDER1'
where input_value_id = temp_rec.input_value_id;
update pay_input_values_f
set default_value = 'S_EE'
,lookup_type = 'US_ELIGIBLE_COMP_OPTIONS_EE'
where input_value_id = l_roth_eligiCmp_ipv_id;
update pay_input_values_f
set lookup_type = 'PQP_US_OVERLIMIT_AT1'
where input_value_id = temp_rec.input_value_id;
update pay_input_values_f
set lookup_type = 'PQP_US_AT_PROCESSING_ORDER1'
where input_value_id = temp_rec.input_value_id;
procedure insert_iterative_rules
(p_iter_element_type_id pay_element_types_f.element_type_id%type
) is
l_proc varchar2(160);
l_proc := g_proc||'.insert_iterative_rules';
insert into pay_iterative_rules_f
(iterative_rule_id
,element_type_id
,effective_start_date
,effective_end_date
,result_name
,iterative_rule_type
,input_value_id
,severity_level
,business_group_id
,legislation_code
,object_version_number
,created_by
,creation_date
,last_update_date
,last_updated_by
,last_update_login
)
values
(pay_iterative_rules_s.nextval
,p_iter_element_type_id
,p_ele_eff_start_date
,to_date('31-12-4712','DD-MM-YYYY')
,'STOPPER'
,'S'
,null
,null
,p_bg_id
,'US'
,1
,-1
,p_ele_eff_start_date
,p_ele_eff_start_date
,-1
,-1
);
end insert_iterative_rules;
Config20 -- exclude Roth ER match element when ER component is not selected
Config21 -- rule to create Pre-tax USERRA element
Config22 -- rule to create Pre-tax USERRA element
Config23 -- rule to create Pre-tax USERRA element
Config24 -- rule to create After-Tax USERRA element
Config25 -- rule to create After-Tax USERRA element
Config26 -- rule to create After-Tax USERRA element
Config27 -- rule to create balance feeds for W2 Roth 403b
Config28 -- rule to create balance feeds for W2 Roth 401k
=============================================================================
*/
--
-- set the aftertax nonrecurring rule(config8)
--
if p_after_tax_component = 'N' then
l_aftertax_nonrecurring_rule := 'N';
ff_globals_f_pkg.insert_row(
x_rowid => l_glb_rowid,
x_global_id => l_glb_id,
x_effective_start_date => to_date('01/01/1900','dd/mm/yyyy'),
x_effective_end_date => to_date('31/12/4712','dd/mm/yyyy'),
x_business_group_id => p_bg_id,
x_legislation_code => null,
x_data_type => 'N',
x_global_name => l_glb_name,
x_global_description => 'The rate of the employer match',
x_global_value => .50);
ff_globals_f_pkg.insert_row(
x_rowid => l_glb_rowid1,
x_global_id => l_glb_id1,
x_effective_start_date => to_date('01/01/1900','dd/mm/yyyy'),
x_effective_end_date => to_date('31/12/4712','dd/mm/yyyy'),
x_business_group_id => p_bg_id,
x_legislation_code => null,
x_data_type => 'N',
x_global_name => l_glb_name1,
x_global_description => 'The rate of the employer match limit',
x_global_value => .06);
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_post_termination_rule => p_termination_rule
,p_benefit_classification_name => l_ben_class_name
,p_element_information_category => l_element_information_category
,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
,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_skip_formula => l_skip_formula
,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
,p_element_information2 => nvl(p_ele_partial_deduction, hr_api.g_varchar2)
,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
,p_element_information9 => 'D'
,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_srs_ERContr_type_id
,p_description => l_srs_erContr_desc_name
,p_reporting_name => l_srs_erContr_rep_name
,p_post_termination_rule => p_termination_rule
,p_processing_type => nvl(p_ele_processing_type, hr_api.g_varchar2)
,p_object_version_number => l_srs_erContr_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_bb_element_type_id
,p_description => 'Generated Element For:'
||p_ele_name||' Buy Back'
,p_reporting_name => nvl(p_ele_reporting_name,p_ele_name)
||' Buy Back'
,p_post_termination_rule => p_termination_rule
,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
,p_element_information_category => 'US_PRE-TAX DEDUCTIONS'
,p_skip_formula => l_skip_formula
,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_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
,p_element_information2 => nvl(p_ele_partial_deduction, hr_api.g_varchar2)
,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
,p_object_version_number => l_bb_ele_obj_ver_number
);
pay_shadow_element_api.update_shadow_element
(p_validate => false
,p_effective_date => p_ele_eff_start_date
,p_post_termination_rule => p_termination_rule
,p_element_type_id => l_cur_temp
,p_description => 'Generated Element For:'
||p_ele_name||l_temp_var(i).sub_name
,p_reporting_name => nvl(p_ele_reporting_name,p_ele_name)
||l_temp_var(i).sub_name
,p_element_information_category => 'US_PRE-TAX DEDUCTIONS'
,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
,p_object_version_number => l_bb_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_cu_element_type_id
,p_description => 'Generated Element For:'
||p_ele_name||' Catchup'
,p_reporting_name => nvl(p_ele_reporting_name,p_ele_name) ||' Catchup'
,p_post_termination_rule => p_termination_rule
,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
,p_element_information_category => 'US_PRE-TAX DEDUCTIONS'
,p_skip_formula => l_skip_formula
,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_element_information1 => nvl(p_ele_category||'C', hr_api.g_varchar2)
,p_element_information2 => nvl(p_ele_partial_deduction, hr_api.g_varchar2)
,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
,p_object_version_number => l_cu_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_cur_temp
,p_description => 'Generated Element For:'
||p_ele_name||l_temp_var(i).sub_name
,p_reporting_name => nvl(p_ele_reporting_name,p_ele_name)
||l_temp_var(i).sub_name
,p_post_termination_rule => p_termination_rule
,p_element_information_category => 'US_PRE-TAX DEDUCTIONS'
,p_element_information1 => nvl(p_ele_category||'C', hr_api.g_varchar2)
,p_object_version_number => l_cu_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_jd_core_element_type_id
,p_description => 'Generated Element For:'
||p_ele_name||' Taxable by JD'
,p_reporting_name => nvl(p_ele_reporting_name,p_ele_name)
||' Taxable by JD'
,p_post_termination_rule => p_termination_rule
,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
,p_element_information_category => 'US_PRE-TAX DEDUCTIONS'
,p_element_information1 => nvl(p_ele_category||'J', hr_api.g_varchar2)
,p_element_information2 => nvl(p_ele_partial_deduction, hr_api.g_varchar2)
,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
,p_object_version_number => l_jd_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_sf_element_type_id
,p_description => 'Generated results element for:'
||p_ele_name
,p_reporting_name => nvl(p_ele_reporting_name,p_ele_name) ||' Special Features'
,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
,p_post_termination_rule => p_termination_rule
,p_element_information_category => 'US_PRE-TAX DEDUCTIONS'
,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
,p_element_information2 => nvl(p_ele_partial_deduction, hr_api.g_varchar2)
,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
,p_object_version_number => l_sf_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_si_element_type_id
,p_description => 'Generated adjustments element for:'
||p_ele_name
,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
,p_reporting_name => nvl(p_ele_reporting_name,p_ele_name)||' Special Inputs'
,p_post_termination_rule => p_termination_rule
,p_element_information_category => l_element_information_category
,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
,p_element_information2 => nvl(p_ele_partial_deduction, hr_api.g_varchar2)
,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
,p_object_version_number => l_si_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_ca_element_type_id
,p_description => 'Generated calculation element for '
||p_ele_name
,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
,p_reporting_name => nvl(p_ele_reporting_name,p_ele_name)||' Calculator'
,p_post_termination_rule => p_termination_rule
,p_element_information_category => l_element_information_category
,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
,p_element_information2 => nvl(p_ele_partial_deduction, hr_api.g_varchar2)
,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
,p_object_version_number => l_ca_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_fee_element_type_id
,p_description => 'Generated Fee results element for '
||p_ele_name
,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
,p_reporting_name => nvl(p_ele_reporting_name,p_ele_name)||' Fees'
,p_post_termination_rule => p_termination_rule
,p_element_information_category => l_element_information_category
,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
,p_element_information2 => nvl(p_ele_partial_deduction, hr_api.g_varchar2)
,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
,p_object_version_number => l_fee_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_er_element_type_id
,p_reporting_name => nvl(p_ele_reporting_name,p_ele_name)||l_temp_var(1).sub_name
,p_post_termination_rule => p_termination_rule
,p_description => 'Employer Match element for:'||p_ele_name
,p_object_version_number => l_object_version_number
);
pay_shadow_element_api.update_shadow_element
(p_validate => false
,p_effective_date => p_ele_eff_start_date
,p_element_type_id => l_at_element_type_id
,p_reporting_name => nvl(p_ele_reporting_name,p_ele_name) ||' AT'
,p_post_termination_rule => p_termination_rule
,p_description => 'After Tax element for:'||p_ele_name
,p_element_information_category => 'US_VOLUNTARY DEDUCTIONS'
,p_skip_formula => l_skip_formula
,p_processing_type => nvl(p_ele_processing_type, hr_api.g_varchar2)
,p_element_information2 => nvl(p_ele_partial_deduction, hr_api.g_varchar2)
,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
,p_object_version_number => l_object_version_number
);
pay_shadow_element_api.update_shadow_element
(p_validate => false
,p_effective_date => p_ele_eff_start_date
,p_element_type_id => l_at_er_element_type_id
,p_reporting_name => nvl(p_ele_reporting_name,p_ele_name)||l_temp_var(1).sub_name
,p_post_termination_rule => p_termination_rule
,p_description => 'After Tax Employer Match element for:'||p_ele_name
,p_object_version_number => l_object_version_number
);
pay_shadow_element_api.update_shadow_element
(p_validate => false
,p_effective_date => p_ele_eff_start_date
,p_element_type_id => l_at_si_element_type_id
,p_description => 'Generated adjustments AT element for:'
||p_ele_name
,p_reporting_name => nvl(p_ele_reporting_name,p_ele_name)||' AT SI'
,p_post_termination_rule => p_termination_rule
,p_element_information_category => 'US_VOLUNTARY DEDUCTIONS'
,p_element_information2 => nvl(p_ele_partial_deduction, hr_api.g_varchar2)
,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
,p_object_version_number => l_object_version_number
);
pay_shadow_element_api.update_shadow_element
(p_validate => false
,p_effective_date => p_ele_eff_start_date
,p_element_type_id => l_at_sf_element_type_id
,p_description => 'Generated Special Features AT element for:'
||p_ele_name
,p_reporting_name => nvl(p_ele_reporting_name
,p_ele_name)||': AT SF'
,p_post_termination_rule => p_termination_rule
,p_element_information_category => 'US_VOLUNTARY DEDUCTIONS'
,p_element_information2 => nvl(p_ele_partial_deduction,hr_api.g_varchar2)
,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
,p_object_version_number => l_object_version_number
);
pay_shadow_element_api.update_shadow_element
(p_validate => false
,p_effective_date => p_ele_eff_start_date
,p_element_type_id => l_atr_element_type_id
,p_reporting_name => nvl(p_ele_reporting_name
,p_ele_name)||' Roth'
,p_post_termination_rule => p_termination_rule
,p_description => 'Roth Contribution element for:'||p_ele_name
,p_element_information_category => 'US_VOLUNTARY DEDUCTIONS'
,p_skip_formula => l_skip_formula
,p_processing_type => nvl(p_ele_processing_type, hr_api.g_varchar2)
,p_relative_processing_priority => l_relative_processing_priority
,p_element_information1 => l_ele_information1
,p_element_information2 => nvl(p_ele_partial_deduction, hr_api.g_varchar2)
,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
,p_object_version_number => l_object_version_number
);
pay_shadow_element_api.update_shadow_element
(p_validate => false
,p_effective_date => p_ele_eff_start_date
,p_description => 'Generated Roth ER element for:'||p_ele_name
,p_element_type_id => l_atr_er_element_type_id
,p_reporting_name => nvl(p_ele_reporting_name
,p_ele_name)||l_temp_var(1).sub_name
,p_post_termination_rule => p_termination_rule
,p_object_version_number => l_object_version_number
);
pay_shadow_element_api.update_shadow_element
(p_validate => false
,p_effective_date => p_ele_eff_start_date
,p_element_type_id => l_atr_si_element_type_id
,p_description => 'Generated adjustments Roth SI element for:'||p_ele_name
,p_reporting_name => nvl(p_ele_reporting_name
,p_ele_name)||' Roth SI'
,p_post_termination_rule => p_termination_rule
,p_element_information_category => 'US_VOLUNTARY DEDUCTIONS'
,p_relative_processing_priority => l_relative_processing_priority
,p_element_information2 => nvl(p_ele_partial_deduction, hr_api.g_varchar2)
,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
,p_object_version_number => l_object_version_number
);
pay_shadow_element_api.update_shadow_element
(p_validate => false
,p_effective_date => p_ele_eff_start_date
,p_element_type_id => l_atr_sf_element_type_id
,p_description => 'Generated Special Features Roth SF element for:'
||p_ele_name
,p_reporting_name => nvl(p_ele_reporting_name
,p_ele_name)||' Roth SF'
,p_post_termination_rule => p_termination_rule
,p_element_information_category => 'US_VOLUNTARY DEDUCTIONS'
,p_relative_processing_priority => l_relative_processing_priority
,p_element_information2 => nvl(p_ele_partial_deduction,hr_api.g_varchar2)
,p_element_information3 => nvl(p_ele_proc_runtype, hr_api.g_varchar2)
,p_object_version_number => l_object_version_number
);
update pay_element_types_f
set element_information5 = l_cal_core_element_type_id,
element_information8 = l_vol_ded_bal_id,
element_information10 = l_pri_bal_id,
element_information11 = l_accr_bal_id,
--element_information12 = l_arr_bal_id, Commented for Bug 2527761, 980683
--element_information13 = l_not_taken_bal_id,
element_information15 = l_fee_bal_id,
element_information16 = l_addl_bal_id,
element_information17 = l_repl_bal_id,
element_information18 = l_si_core_element_type_id
--element_information19 = l_sf_core_element_type_id,
--element_information20 = l_ver_core_element_type_id,
where element_type_id = l_base_element_type_id
and business_group_id = p_bg_id;
update pay_element_types_f
set element_information10 = l_fee_bal_id,
element_information11 = l_accr_fees_bal_id
where element_type_id = l_fees_core_element_type_id
and business_group_id = p_bg_id;
update pay_input_values_f
set mandatory_flag = 'X'
where name = 'Pay Value'
and element_type_id in (select element_type_id
from pay_element_types_f
where element_name like p_ele_name ||'%');
update pay_element_types_f
set element_information10 = l_pri_bal_id,
element_information11 = l_accr_bal_id,
element_information12 = l_arr_bal_id,
element_information13 = l_not_taken_bal_id,
element_information16 = l_addl_bal_id,
element_information17 = l_repl_bal_id,
element_information18 = l_si_core_element_type_id,
element_information19 = l_sf_core_element_type_id,
iterative_flag = 'Y',
iterative_formula_id = l_iter_formula_id,
iterative_priority = l_iter_priority
where element_type_id = l_base_element_type_id
and business_group_id = p_bg_id;
insert_iterative_rules(l_base_element_type_id);
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_at_pri_bal_id,
element_information11 = l_at_accr_bal_id,
element_information12 = l_at_arr_bal_id,
element_information13 = l_at_not_taken_bal_id,
element_information16 = l_at_addl_bal_id,
element_information17 = l_at_repl_bal_id,
element_information18 = l_at_si_core_element_type_id,
element_information19 = l_at_sf_core_element_type_id
where element_type_id = l_at_base_element_type_id
and business_group_id = p_bg_id;
update pay_defined_balances
set save_run_balance = 'Y'
where balance_type_id = l_at_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_atr_pri_bal_id,
element_information11 = l_atr_accr_bal_id,
element_information12 = l_atr_arr_bal_id,
element_information13 = l_atr_not_taken_bal_id,
element_information16 = l_atr_addl_bal_id,
element_information17 = l_atr_repl_bal_id,
element_information18 = l_roth_si_ele_type_id,
element_information19 = l_roth_sf_ele_type_id
where element_type_id = l_roth_ele_type_id
and business_group_id = p_bg_id;
update pay_defined_balances
set save_run_balance = 'Y'
where balance_type_id = l_atr_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_information1 = 'O',
element_information10 = l_atr_er_bal_id,
element_information_category = 'US_EMPLOYER LIABILITIES'
where element_type_id = l_roth_er_ele_type_id
and business_group_id = p_bg_id;
update pay_defined_balances
set save_run_balance = 'Y'
where balance_type_id = l_atr_er_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_cu_pri_bal_id,
element_information11 = l_cu_accrued_bal_id,
element_information12 = l_cu_arrears_bal_id,
element_information13 = l_cu_nottaken_bal_id,
element_information16 = l_cu_addl_bal_id,
element_information17 = l_cu_repl_bal_id,
element_information18 = l_cu_si_element_type_id,
element_information19 = l_cu_sf_element_type_id,
iterative_flag = 'Y',
iterative_formula_id = l_iter_formula_id,
iterative_priority = (l_iter_priority - 5)
where element_type_id = l_cu_element_type_id
and business_group_id = p_bg_id;
insert_iterative_rules(l_cu_element_type_id);
update pay_defined_balances
set save_run_balance = 'Y'
where balance_type_id = l_cu_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_bb_pri_bal_id,
element_information11 = l_bb_accrued_bal_id,
element_information12 = l_bb_arrears_bal_id,
element_information13 = l_bb_nottaken_bal_id,
element_information16 = l_bb_addl_bal_id,
element_information17 = l_bb_repl_bal_id,
element_information18 = l_bb_si_element_type_id,
element_information19 = l_bb_sf_element_type_id,
iterative_flag = 'Y',
iterative_formula_id = l_iter_formula_id,
iterative_priority = (l_iter_priority - 5)
where element_type_id = l_bb_element_type_id
and business_group_id = p_bg_id;
insert_iterative_rules(l_bb_element_type_id);
update pay_defined_balances
set save_run_balance = 'Y'
where balance_type_id = l_bb_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_er_contr_pri_bal_id,
element_information_category = 'US_EMPLOYER LIABILITIES'
where element_type_id = l_er_contr_element_type_id
and business_group_id = p_bg_id;
update pay_element_types_f
set element_information1 = 'O',
element_information10 = l_er_bal_id,
element_information_category = 'US_EMPLOYER LIABILITIES'
where element_type_id = l_er_element_type_id
and business_group_id = p_bg_id;
update pay_element_types_f
set element_information1 = 'O',
element_information10 = l_at_er_bal_id,
element_information_category = 'US_EMPLOYER LIABILITIES'
where element_type_id = l_at_er_element_type_id
and business_group_id = p_bg_id;
update pay_defined_balances
set save_run_balance = 'Y'
where balance_type_id = l_er_bal_id
and balance_dimension_id = l_asg_gre_run_dim_id
and business_group_id = p_bg_id;
update pay_defined_balances
set save_run_balance = 'Y'
where balance_type_id = l_at_er_bal_id
and balance_dimension_id = l_asg_gre_run_dim_id
and business_group_id = p_bg_id;
update pay_input_values_f piv
set piv.mandatory_flag = 'X'
where piv.input_value_id = i.input_value_id
and piv.element_type_id = i.element_type_id
and piv.business_group_id = p_bg_id;
procedure delete_user_init_template
(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);
select template_id
from pay_element_templates
where base_name = p_ele_name
and business_group_id = p_business_group_id
and template_type = 'U';
l_proc := g_proc||'.delete_user_init_template';
pay_element_template_api.delete_user_structure
(p_validate => false
,p_drop_formula_packages => true
,p_template_id => l_template_id
);
end delete_user_init_template;