[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
03-JUN-99 R. Murthy 115.4 901531 Update shadow structure
also updates the user-entered
element description and element
reporting name.
Added a check to see if the
element being created has the
same name as that of an
existing balance (seeded or
otherwise). If yes, an error
is raised. This prevents
users from creating elements
with reserved words as names.
17-FEB-2000 RThirlby 115.5 Added p_ele_calc_method to
procedure create_user_init_
earning. Used in update_shadow_
element.
17-FEB-2000 RThirlby Added new procedure
update_jd_level_on_balance.
This updates jurisdiction_level
on pay_balance_types for all
balances.
Changes for Flexi date too.
29-FEB-2000 RThirlby Added p_ele_eoy_type to
procedure create_user_init_
earning. Used in update_shadow_
element. This parameter inserts
the Year End Form for an
earning.
21-MAR-2000 ACai Update date mask for 11i.
31-OCT-2000 JARTHURT 115.9 Update t4a footnote,rl1 footnote
and registration number for
new reference in element DFF.
20-FEB-2001 Ekim 115.10 Added Procedure update_ntg_element
to enable 'Net to Gross'
functionality and also corrected
process_mode.
27-FEB-2001 SSattini 115.11 Removed extra comment symbol
31-May-2001 VPandya 115.12 Added Hours by Rate functionality
01-OCT-2001 mmukherj 115.15,16 Added the functionality of defaul
ting element_information9 to 'T'
or 'A' for HoursXRate elements.
11-APR-2002 SSattini 115.17 Fixed the bug#2304888
and also added dbdrv.
11-APR-2002 SSattini 115.18 Corrected GSCC complaint.
06-JUN-2002 mmukherj 115.19 Changed the defaulting of
Regular Earnings Adjustment Rule
for 'Earnings' elements
to A from T,
bugfix #2402284
20-Jan-2003 vpandya 115.20,21 Creating skip records in
element type usages table. If
Tax processing type and Year End
form (gre type) is matches with
run type name of pay_run_types_f
table then one record will be
created with inclusion_flag 'Y'
and usage_type 'T', and 8 records
created with inclusion_flag 'N'
and usage_type NULL for other run
types. For Non Payroll Payments
element, there will be 3 records
with 'Y' and 'T', and 6 records
with 'N' and NULL. New skip rules
will be assigned to new elements
are REG_EARNINGS_SKIP and
SUPP_EARNINGS_SKIP.
18-Feb-2003 vpandya 115.22 Using API for element type usage.
20-Mar-2003 vpandya 115.23 For Non-Payroll Payment element,
defaulting T4/RL1 Regular trigger
for element type usage if form
type is blank otherwise it
it defaults to form type +
Regulae (e.g. T4A/RL2 Regular)
01-MAY-2003 mmukherj 115.24 The process mode for non sepcheck
element has been changed from 'N'
to 'S'. Bugfix: 2811154,2802065
23-MAY-2003 pganguly 115.25 2924151 For Base/Special Feature Element
the element_information3 is
updated to 'DE'(Date Earned).
This is only done for Elements
with classification 'Earnings',
'Supplemental Earnings',
'Taxable Benefits'. Also for Sp
Feature elements element_infor
mation_category, element_infor
mation1 will be populated with
the value of the Base element
for those classifications.
20-JUN-2003 vpandya 115.26 The process mode for non sepcheck
element has been changed from 'N'
to 'S' only for 'Supplemental
Earnings'. Using existing
variable l_sep_check_create to
set process mode.
05-AUG-2003 ssouresr 115.28 Saving run balances for _GRE_RUN
_GRE_JD_RUN, _ASG_GRE_RUN and
_ASG_JD_GRE_RUN on
pay_defined_balances
05-AUG-2003 ssouresr 115.29 Removed _GRE_RUN and _GRE_JD_RUN from previous change as these
dimensions are not required for
saving nonseeded balances
25-SEP-2003 mmukherj 115.30 Bugfix : 2851568.
Feed Taxable Benefits for Quebec for all Taxable Benefits Element with Category PHSP.In
create_user_init earning a
section has been added to feed
Taxable Benefits for Quebec
balance for PHSP.
26-SEP-2003 ssattini 115.31 Added update to set the
post_termination_rule to 'Last
Standard Process Date' for all
recurring elements. Fix for
bug#2219028.
22-MAR-2004 ssmukher 115.32 Bug#2646705 Enhancement for
adding the termination rule
27-APR-2004 ssmukher 115.33 Bug#2646705 Replaced the skip rule from
REG_EARNINGS_SKIP to REGULAR_EARNINGS_SKIP
13-APR-2006 ahanda 115.34 Modfied package ot create a formula result
rule to Hours by Rate element
EARNINGS_AMOUNT > Pay Value
*/
--
--
------------------------- create_user_init_earning ----------------------------
--
FUNCTION create_user_init_earning (
p_ele_name in varchar2,
p_ele_reporting_name in varchar2,
p_ele_description in varchar2 default NULL,
p_ele_classification in varchar2,
p_ele_category in varchar2 default NULL,
p_ele_calc_method in varchar2,
p_ele_eoy_type in varchar2,
p_ele_t4a_footnote in varchar2,
p_ele_rl1_footnote in varchar2,
p_ele_registration_number in varchar2,
p_ele_ot_earnings in varchar2 default 'N',
p_ele_ot_hours in varchar2 default 'N',
p_ele_ei_hours in varchar2 default 'N',
p_ele_processing_type in varchar2,
p_ele_priority in number default NULL,
p_ele_standard_link in varchar2 default 'N',
p_ele_calc_rule in varchar2,
p_ele_calc_rule_code in varchar2 default NULL,
p_sep_check_option in varchar2 default 'N',
p_reduce_regular in varchar2 default 'N',
p_ele_eff_start_date in date default NULL,
p_ele_eff_end_date in date default NULL,
p_bg_id in number ,
p_termination_rule in varchar2 default 'F')--Bug 2646705
RETURN NUMBER IS
--
-- cursor to retrieve the element id from element name
--
CURSOR cur_element_type_id(p_element_name VARCHAR2) IS
SELECT element_type_id
FROM pay_element_types_f
WHERE upper(element_name) = upper(p_element_name)
AND legislation_code = 'CA';
SELECT piv.input_value_id
FROM pay_input_values_f piv, pay_element_types_f pet
WHERE upper(pet.element_name) = upper(p_element_name)
AND pet.element_type_id = piv.element_type_id
AND upper(pet.legislation_code) = upper('CA')
AND upper(piv.name) = upper(p_input_value_name);
SELECT piv.input_value_id
FROM pay_input_values_f piv, pay_element_types_f pet
WHERE pet.element_type_id = p_element_type_id
AND pet.element_type_id = piv.element_type_id
/* AND upper(pet.legislation_code) = upper('CA') */
AND upper(piv.name) = upper(p_input_value_name);
SELECT status_processing_rule_id
FROM pay_status_processing_rules_f pspfr,
pay_element_types_f petf
WHERE pspfr.element_type_id = petf.element_type_id
AND petf.element_type_id = p_element_type_id;
select pet.element_type_id
,pet.element_name
,pet.element_information2
,pet.element_information4
,pet.effective_start_date
,pet.effective_end_date
,pet.legislation_code
,pet.business_group_id
from pay_element_types_f pet
where ( pet.element_name = cp_ele_name or
pet.element_name = cp_ele_name || ' Special Inputs' )
and pet.business_group_id = cp_busi_grp_id
order by pet.element_name;
select prt.*
from pay_run_types_f prt
where prt.legislation_code = 'CA'
and ( prt.shortname like 'REG_T4%' or
prt.shortname like 'NP_T4%' or
prt.shortname like 'LS_T4%' );
SELECT balance_dimension_id
FROM pay_balance_dimensions
WHERE dimension_name = 'Assignment within Government Reporting Entity Run'
AND legislation_code = 'CA';
SELECT balance_dimension_id
FROM pay_balance_dimensions
WHERE dimension_name = 'Assignment in JD within GRE Run'
AND legislation_code = 'CA';
l_base_element_type_id NUMBER(9); -- Populated by insertion of element type.
select template_id
into l_template_id
from pay_element_templates
where template_name = l_template_name
and legislation_code = p_legislation_code
and business_group_id is NULL
and template_type = 'T';
select 'Y'
into l_installed
from pay_balance_types
where upper(balance_name) = 'FED SUBJECT'
and legislation_code = 'CA';
select 'Y'
into l_reserved
from pay_balance_types
where upper(p_ele_name) = upper(balance_name)
and nvl(legislation_code, 'CA') = 'CA'
and nvl(business_group_id, p_bg_id) = p_bg_id;
select element_type_id, object_version_number
into l_element_type_id, l_ele_obj_ver_number
from pay_shadow_element_types
where template_id = l_template_id
and element_name = p_ele_name;
select element_type_id, object_version_number
into l_sf_element_type_id, l_sf_ele_obj_ver_number
from pay_shadow_element_types
where template_id = l_template_id
and element_name = p_ele_name||' Special Features';
select element_type_id, object_version_number
into l_si_element_type_id, l_si_ele_obj_ver_number
from pay_shadow_element_types
where template_id = l_template_id
and element_name = p_ele_name||' Special Inputs';
SELECT
DECODE(p_ele_classification,'Earnings','DE',
'Supplemental Earnings','DE','Taxable Benefits','DE','')
INTO l_roe_allocation_by
FROM dual;
SELECT
DECODE(p_ele_classification,
'Earnings',nvl(p_ele_category, hr_api.g_varchar2),
'Supplemental Earnings',nvl(p_ele_category, hr_api.g_varchar2),
'Taxable Benefits',nvl(p_ele_category, hr_api.g_varchar2),
'')
INTO l_sf_ele_category
FROM dual;
SELECT
DECODE(p_ele_classification,
'Earnings', nvl(upper(g_template_leg_code||'_'||p_ele_classification), hr_api.g_varchar2),
'Supplemental Earnings', nvl(upper(g_template_leg_code||'_'||p_ele_classification), hr_api.g_varchar2),
'Taxable Benefits', nvl(upper(g_template_leg_code||'_'||p_ele_classification), hr_api.g_varchar2),
'')
INTO l_sf_ele_info_category
FROM dual;
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_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_description => p_ele_description
,p_reporting_name => p_ele_reporting_name
,p_element_information_category => nvl(upper(g_template_leg_code||'_'||p_ele_classification), hr_api.g_varchar2)
,p_element_information1 => nvl(p_ele_category, hr_api.g_varchar2)
,p_element_information2 => p_ele_calc_method
,p_element_information4 => p_ele_eoy_type
,p_element_information18 => p_ele_t4a_footnote
,p_element_information19 => p_ele_rl1_footnote
,p_element_information20 => p_ele_registration_number
-- ,p_element_information10 => l_pri_bal_id
-- ,p_element_information12 => l_hrs_bal_id
,p_skip_formula => l_skip_formula
,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_sf_element_type_id
,p_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
,p_element_information_category => l_sf_ele_info_category
,p_reporting_name => p_ele_reporting_name||' SF'
,p_element_information1 => l_sf_ele_category
,p_element_information3 => l_roe_allocation_by
,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_classification_name => nvl(p_ele_classification, hr_api.g_varchar2)
,p_reporting_name => p_ele_reporting_name||' SI'
,p_object_version_number => l_si_ele_obj_ver_number);
select element_type_id
into l_base_element_type_id
from pay_element_types_f
where element_name = p_ele_name
and business_group_id + 0 = p_bg_id;
select ptco.core_object_id
into l_pri_bal_id
from pay_shadow_balance_types psbt,
pay_template_core_objects ptco
where psbt.template_id = l_template_id
and psbt.balance_name = p_ele_name
and ptco.template_id = psbt.template_id
and ptco.shadow_object_id = psbt.balance_type_id;
select balance_type_id
into l_pri_bal_id
from pay_shadow_balance_types
where template_id = l_template_id
and balance_name = p_ele_name;
select ptco.core_object_id
into l_hrs_bal_id
from pay_shadow_balance_types psbt,
pay_template_core_objects ptco
where psbt.template_id = l_template_id
and psbt.balance_name = p_ele_name||' Hours'
and ptco.template_id = psbt.template_id
and ptco.shadow_object_id = psbt.balance_type_id;
update pay_element_types_f
set element_information10 = l_pri_bal_id,
element_information12 = l_hrs_bal_id,
process_mode = decode(l_sep_check_create,'Y','S','N')
where element_type_id = l_base_element_type_id
and business_group_id + 0 = p_bg_id;
update pay_element_types_f
set element_information10 = l_pri_bal_id,
element_information12 = l_hrs_bal_id,
process_mode = 'P'
where element_type_id = l_base_element_type_id
and business_group_id + 0 = p_bg_id;
update pay_element_types_f
set element_information10 = l_pri_bal_id,
element_information12 = l_hrs_bal_id,
process_mode = 'S'
where element_type_id = l_base_element_type_id
and business_group_id + 0 = p_bg_id;
UPDATE pay_defined_balances
SET save_run_balance = 'Y'
WHERE balance_type_id = l_pri_bal_id
AND balance_dimension_id IN
(l_asg_gre_run_dim_id,
l_asg_jd_gre_run_dim_id)
AND business_group_id = p_bg_id;
update pay_element_types_f
set post_termination_rule = p_termination_rule -- Bug 2646705
where element_type_id = l_base_element_type_id
and business_group_id + 0 = p_bg_id;
update_jd_level_on_balance(l_template_id);
update_ntg_element(l_base_element_type_id,
p_ele_eff_start_date,
p_bg_id);
update pay_element_types_f
set element_information9 = 'A'
where element_type_id = l_base_element_type_id
and business_group_id + 0 = p_bg_id;
update pay_element_types_f
set element_information9 = 'A'
where element_type_id = l_base_element_type_id
and business_group_id + 0 = p_bg_id;
update pay_element_types_f
set element_information9 = 'R'
where element_type_id = l_base_element_type_id
and business_group_id + 0 = p_bg_id;
UPDATE pay_element_types_f
SET element_information3 = 'DE'
WHERE element_type_id = l_base_element_type_id
AND business_group_id + 0 = p_bg_id;
select 'REG_' ||
replace(etu.element_information4,'/','_') earn_shortname
into lv_earn_shortname
from dual;
select decode(etu.element_information2, 'R','REG_',
'N','NP_',
'L','LS_', NULL)||
replace(etu.element_information4,'/','_') earn_shortname
into lv_earn_shortname
from dual;
select element_type_id
into l_element_type_id
from pay_element_types_f
where business_group_id = p_bg_id
and element_name = p_ele_name;
select balance_type_id
into l_balance_type_id
from pay_balance_types
where balance_name = 'Taxable Benefits for Quebec';
pay_balance_feeds_f_pkg.insert_row (l_balance_row_id,
l_balance_feed_id,
p_ele_eff_start_date,
p_ele_eff_end_date,
p_bg_id,
'CA',
l_balance_type_id,
l_pay_value_iv_id,
'1',
NULL);
PROCEDURE delete_user_init_earning (
p_business_group_id in number,
p_ele_type_id in number,
p_ele_name in varchar2,
p_del_sess_date in date,
p_del_val_start_date in date,
p_del_val_end_date in date) IS
-- local constants
c_end_of_time CONSTANT DATE := TO_DATE('4712/12/31','YYYY/MM/DD');
hr_utility.set_location('pay_ca_user_init_earn.delete_user_init_earning',1);
select template_id
into l_template_id
from pay_element_templates
where base_name = p_ele_name
and business_group_id = p_business_group_id
and template_type = 'U';
hr_utility.set_location('pay_ca_user_init_earn.delete_user_init_earning',2);
delete from pay_element_type_usages_f
where element_type_id in ( select element_type_id
from pay_element_types_f
where ( element_name = p_ele_name or
element_name =
p_ele_name ||' Special Inputs' )
and business_group_id = p_business_group_id );
hr_utility.set_location('pay_ca_user_init_earn.delete_user_init_earning',3);
pay_element_template_api.delete_user_structure
(p_validate => false
,p_drop_formula_packages => true
,p_template_id => l_template_id);
hr_utility.set_location('pay_ca_user_init_earn.delete_user_init_earning',4);
END delete_user_init_earning;
PROCEDURE UPDATE_JD_LEVEL_ON_BALANCE(p_template_id in number) is
--
CURSOR get_balance_type_ids(p_template_id number) IS
select ptco.core_object_id
from pay_template_core_objects ptco
where ptco.template_id = p_template_id
and ptco.core_object_type = 'BT';
UPDATE pay_balance_types
SET jurisdiction_level = 2
WHERE balance_type_id = each_balance.core_object_id;
END UPDATE_JD_LEVEL_ON_BALANCE;
PROCEDURE update_ntg_element(p_base_element_type_id in NUMBER,
p_ele_eff_start_date in DATE,
p_bg_id in NUMBER) IS
--
CURSOR c_iter_formula_id IS
SELECT formula_id
FROM ff_formulas_f
WHERE formula_name = 'CA_ITER_GROSSUP'
and legislation_code = 'CA';
SELECT input_value_id, name
FROM pay_input_values_f
WHERE element_type_id = p_base_element_type_id;
SELECT element_type_id
FROM pay_element_types_f
WHERE upper(element_name) = 'FED_GROSSUP_ADJUSTMENT'
AND legislation_code = 'CA';
SELECT input_value_id
FROM pay_input_values_f
WHERE element_type_id = p_seed_ele_type_id
AND upper(name) = 'AMOUNT';
SELECT status_processing_rule_id
FROM pay_status_processing_rules_f
WHERE element_type_id = p_base_element_type_id;
l_insert VARCHAR2(1) := 'N';
l_proc VARCHAR2(50) := 'pay_ca_user_init_earn.update_ntg_element';
UPDATE pay_element_types_f
SET iterative_formula_id = l_iter_formula_id,
iterative_flag = 'Y',
grossup_flag = 'Y'
WHERE element_type_id = p_base_element_type_id
AND business_group_id + 0 = p_bg_id;
l_insert := 'Y';
l_insert := 'Y';
l_insert := 'Y';
l_insert := 'Y';
l_insert := 'Y';
IF l_insert = 'Y' THEN
hr_utility.set_location(l_proc,30);
l_insert := 'N';
SELECT pay_formula_result_rules_s.nextval
INTO l_nextval
FROM dual;
INSERT INTO PAY_FORMULA_RESULT_RULES_F
(formula_result_rule_id,
effective_start_date,
effective_end_date,
business_group_id,
legislation_code,
element_type_id,
status_processing_rule_id,
result_name,
result_rule_type,
input_value_id,
last_update_date,
last_updated_by,
last_update_login,
created_by,
creation_date)
VALUES
(l_nextval,
trunc(TO_DATE('0001/01/01', 'YYYY/MM/DD')),
trunc(TO_DATE('4712/12/31', 'YYYY/MM/DD')),
p_bg_id,
'CA',
l_seeded_ele_type_id,
l_status_pro_rule_id,
'AMOUNT',
'I',
l_seeded_input_val_id,
sysdate,
-1,
-1,
-1,
sysdate);
END update_ntg_element;