[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
10 Jul 1997 mmukherj 40.15 502307 Updated do_defined_balances
procedure.Included business_group_id
in where condition while checking
pay_defined_balnce has already exist
for that balance_name for that busines_
group_id or not
21 Jul 1997 mmukherj 40.16 Added some comments in do_defined_balances
procedure related to Bug no 502307. No
other change in the code. Changed the
select statement of the same proceduer
to avoid using index on
business_group_id.
30 Apr 1998 pmadore 40.17 Added additonal input values, formula
result rules, elements, and balances to
support the Employer match components
of a pretax deduction in category of
Deferred Comp 401k.
The logic to create these objects
depends upon the values of a new
parameter to the main package function:
p_ele_er_match
10 Mar 1999 ahanda 40.19 Changed GRE_ITD to ASG_ITD as GRE_ITD
is already there in suffixes(26) and
ASG_ITD is missing in the
pretax_deduction_template. Bug 820068
16-jun-1999 achauhan 110.10 Replaced dbms_output with hr_utility
09-jul-1999 vmehta 110.11 Added check for legislation_code
while retrieving classification for
employer match BUG 912994
27-oct-1999 dscully Added check for legislation_code while
looking up skip rules
12-Jul-2000 kthirmiy 110.14 Added ELEMENT_INFORMATION_CATEGORY=
'US_EMPLOYER LIABILITIES'
while updating PAY_ELEMENT_TYPES_F for
pretax ER element to show the desc flex
field in the element description screen
for pretax ER element
*******************************************************************************
22-JAN-2002 ahanda 115.13 Added call to create defined bal for
Assignment Payments.
23-DEC-2002 tclewis 115.15 11.5.9 performance fixes and inspected
file to add nocopy directive. I found
no procedures requireing it.
27-DEC-2002 meshah 115.16 fixed gscc warnings/errors.
*******************************************************************************/
/*
This package contains calls to core API used to insert records comprising an
entire pretax deduction template. Migration to published (ie. supported) api
from core is an essential move when these become available.
The procedures responsible for creating
appropriate records based on data entered on the Deductions form
must perform simple logic to determine the exact attributes required for the
deductions template. We do this to keep extraneous information
to a minimum - especially regarding input values and formula
result rules. Attributes (and their determining factors) are:
- skip rules (Classification)
- status processing rules (Calculation Method)
- input values (Classification/Category, Calculation Method)
- formula result rules (Calculation Method)
*/
--
------------------------- upgrade_deduction_template ------------------------
--
FUNCTION pretax_deduction_template (
p_ele_name in varchar2,
p_ele_reporting_name in varchar2,
p_ele_description in varchar2 default NULL,
p_ele_classification in varchar2,
p_ben_class_id in number,
p_ele_category in varchar2 default NULL,
p_ele_processing_type in varchar2,
p_ele_priority in number default NULL,
p_ele_standard_link in varchar2 default 'N',
p_ele_proc_runtype in varchar2,
p_ele_start_rule in varchar2,
p_ele_stop_rule in varchar2,
p_ele_ee_bond in varchar2 default 'N',
p_ele_amount_rule in varchar2,
p_ele_paytab_name in varchar2 default NULL,
p_ele_paytab_col in varchar2 default NULL,
p_ele_paytab_row_type in varchar2 default NULL,
p_ele_arrearage in varchar2 default 'N',
p_ele_partial_dedn in varchar2 default 'N',
p_mix_flag in varchar2 default NULL,
p_ele_er_match 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) RETURN NUMBER IS
-- global vars
TYPE text_table IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
v_ele_type_id NUMBER(9); -- insertion of element type.
v_shadow_ele_type_id NUMBER(9); -- Populated by insertion of element type.
v_inputs_ele_type_id NUMBER(9); -- Populated by insertion of element type.
v_bal_type_id NUMBER(9); -- Pop'd by insertion of balance type.
v_payval_id NUMBER(9); -- ID of payval for bal feed insert.
SELECT /*+ no_merge(pbf) */
bc.CLASSIFICATION_ID, pbf.BUSINESS_GROUP_ID,
pbf.LEGISLATION_CODE, pbf.BALANCE_TYPE_ID,
pbf.INPUT_VALUE_ID, pbf.SCALE, pbf.ELEMENT_TYPE_ID
FROM PAY_BALANCE_FEEDS_V pbf,
pay_balance_classifications bc
WHERE NVL(pbf.BALANCE_INITIALIZATION_FLAG,'N') = 'N'
AND ((pbf.BUSINESS_GROUP_ID IS NULL OR pbf.BUSINESS_GROUP_ID = p_bg_id)
AND (pbf.LEGISLATION_CODE IS NULL OR pbf.LEGISLATION_CODE = 'US'))
and (pbf.BALANCE_NAME = 'Regular Earnings')
and bc.balance_type_id = pbf.balance_type_id
order by pbf.element_name;
select business_group_id
into v_business_group_id
from per_business_groups
where upper(name) = upper(p_bg_name);
select dimension_name, balance_dimension_id
into dim_name, dim_id
from pay_balance_dimensions
where database_item_suffix = suffixes(i)
and legislation_code = g_template_leg_code
and business_group_id IS NULL;
/* added line to include business_group_id in the where clause of the select
statement below. So that it checkes the existence of data for a the given
business_group_id Bug No: 502307.
*/
SELECT count(0)
INTO already_exists
FROM pay_defined_balances db,
pay_balance_types bt
WHERE db.balance_type_id = bt.balance_type_id
AND upper(bt.balance_name) = upper(p_bal_name)
AND bt.business_group_id + 0 = v_business_group_id
AND db.balance_dimension_id = dim_id;
SELECT FF.formula_text,
FF.formula_type_id
INTO v_skeleton_formula_text,
v_skeleton_formula_type_id
FROM ff_formulas_f FF
WHERE FF.formula_name = 'PRETAX_WITHHOLDING_FORMULA'
AND FF.business_group_id IS NULL
AND FF.legislation_code = 'US'
AND g_eff_start_date >= FF.effective_start_date
AND g_eff_start_date <= FF.effective_end_date;
SELECT ff_formulas_s.nextval
INTO v_new_ele_formula_id
FROM sys.dual;
INSERT INTO ff_formulas_f ( FORMULA_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
FORMULA_TYPE_ID,
FORMULA_NAME,
DESCRIPTION,
FORMULA_TEXT,
STICKY_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE)
values (
v_new_ele_formula_id,
g_eff_start_date,
g_eff_end_date,
p_ff_bg_id,
NULL,
v_skeleton_formula_type_id,
v_new_ele_formula_name,
p_ff_desc,
v_new_ele_formula_text,
'N',
NULL,
NULL,
NULL,
-1,
g_eff_start_date);
UPDATE ff_formulas_f
SET formula_name = v_new_ele_formula_name,
formula_text = v_new_ele_formula_text
WHERE formula_id = v_new_ele_formula_id
AND business_group_id = p_ff_bg_id
AND g_eff_start_date BETWEEN effective_start_date
AND effective_end_date;
UPDATE ff_formulas_f
SET formula_text = v_new_ele_formula_text
WHERE formula_id = v_new_ele_formula_id
AND business_group_id = p_ff_bg_id
AND g_eff_start_date BETWEEN effective_start_date
AND effective_end_date;
select count(0)
into l_count_already
from ff_formulas_f
where upper(formula_name) like upper('%'||l_placehold_ele_name||'%');
SELECT ff_formulas_s.nextval
INTO v_orig_ele_formula_id
FROM sys.dual;
INSERT INTO ff_formulas_f ( FORMULA_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
FORMULA_TYPE_ID,
FORMULA_NAME,
DESCRIPTION,
FORMULA_TEXT,
STICKY_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE)
values (
v_orig_ele_formula_id,
g_eff_start_date,
g_eff_end_date,
p_ff_bg_id,
NULL,
v_skeleton_formula_type_id,
v_orig_ele_formula_name,
p_ff_desc,
v_orig_ele_formula_text,
'N',
NULL,
NULL,
NULL,
-1,
g_eff_start_date);
SELECT FF.formula_text,
FF.formula_type_id
INTO v_skeleton_formula_text,
v_skeleton_formula_type_id
FROM ff_formulas_f FF
WHERE FF.formula_name = 'PRETAX_CALCULATION_FORMULA'
AND FF.business_group_id IS NULL
AND FF.legislation_code = 'US'
AND g_eff_start_date >= FF.effective_start_date
AND g_eff_start_date <= FF.effective_end_date;
SELECT ff_formulas_s.nextval
INTO v_new_ele_formula_id
FROM sys.dual;
INSERT INTO ff_formulas_f ( FORMULA_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
FORMULA_TYPE_ID,
FORMULA_NAME,
DESCRIPTION,
FORMULA_TEXT,
STICKY_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE)
values (
v_new_ele_formula_id,
g_eff_start_date,
g_eff_end_date,
p_ff_bg_id,
NULL,
v_skeleton_formula_type_id,
v_new_ele_formula_name,
p_ff_desc,
v_new_ele_formula_text,
'N',
NULL,
NULL,
NULL,
-1,
g_eff_start_date);
UPDATE ff_formulas_f
SET formula_name = v_new_ele_formula_name,
formula_text = v_new_ele_formula_text
WHERE formula_id = v_new_ele_formula_id
AND business_group_id = p_ff_bg_id
AND g_eff_start_date BETWEEN effective_start_date
AND effective_end_date;
UPDATE ff_formulas_f
SET formula_text = v_new_ele_formula_text
WHERE formula_id = v_new_ele_formula_id
AND business_group_id = p_ff_bg_id
AND g_eff_start_date BETWEEN effective_start_date
AND effective_end_date;
select count(0)
into l_count_already
from ff_formulas_f
where upper(formula_name) like upper('%'||l_placehold_ele_name||'%');
SELECT ff_formulas_s.nextval
INTO v_orig_ele_formula_id
FROM sys.dual;
INSERT INTO ff_formulas_f ( FORMULA_ID,
EFFECTIVE_START_DATE,
EFFECTIVE_END_DATE,
BUSINESS_GROUP_ID,
LEGISLATION_CODE,
FORMULA_TYPE_ID,
FORMULA_NAME,
DESCRIPTION,
FORMULA_TEXT,
STICKY_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE)
values (
v_orig_ele_formula_id,
g_eff_start_date,
g_eff_end_date,
p_ff_bg_id,
NULL,
v_skeleton_formula_type_id,
v_orig_ele_formula_name,
p_ff_desc,
v_orig_ele_formula_text,
'N',
NULL,
NULL,
NULL,
-1,
g_eff_start_date);
. Manually update DDF segments on various associated element types.
Make direct calls to CORE_API packaged procedures to:
. Insert status proc rule of 'PROCESS' for Asst status type 'ACTIVE_ASSIGN'
and appropriate formula according to calculation method
. Setup calculation formula and status proc rule on calculator element.
. Setup withholding formula and status proc rule on base (withholding) element.
. Insert input values according to calculation method
. Insert formula result rules as appropriate for formula and amount rule.
-- In the case of deductions elements, the formulae are fully defined in advance
-- based on calculation rule only. These pre-packaged formulae are seeded
-- as startup data - such that bg_id is NULL, in the appropriate legislation.
-- The formula_name will closely resemble the calc rule.
-- For deductions, formula is "pieced together" according to calc_rule
-- and other attributes.
-- To copy a formula from seed data to the customer business group, we can
-- select the formula_text LONG field into a VARCHAR2; the LONG field
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype,
element_information9 = p_mix_category,
benefit_classification_id = p_ben_class_id
WHERE element_type_id = dedn_ele_ids(1)
AND business_group_id + 0 = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype,
benefit_classification_id = NULL
WHERE element_type_id = dedn_ele_ids(2)
AND business_group_id + 0 = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype
WHERE element_type_id = dedn_ele_ids(4)
AND business_group_id + 0 = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype
WHERE element_type_id = dedn_ele_ids(3)
AND business_group_id + 0 = p_bg_id;
SELECT user_table_id
INTO v_paytab_id
FROM pay_user_tables
WHERE UPPER(user_table_name) = UPPER(p_paytab_name)
AND NVL(business_group_id, p_bg_id) = p_bg_id;
SELECT lookup_code
INTO v_row_code
FROM hr_lookups
WHERE UPPER(meaning) = UPPER(p_paytab_row_type)
AND lookup_type = 'US_TABLE_ROW_TYPES';
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype,
element_information6 = p_paytab_name,
element_information7 = v_row_code,
element_information9 = p_mix_category,
benefit_classification_id = p_ben_class_id
WHERE element_type_id = dedn_ele_ids(1)
AND business_group_id + 0 = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype,
element_information6 = p_paytab_name,
element_information7 = v_row_code,
benefit_classification_id = NULL
WHERE element_type_id = dedn_ele_ids(2)
AND business_group_id + 0 = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype,
element_information6 = p_paytab_name,
element_information7 = v_row_code
WHERE element_type_id = dedn_ele_ids(4)
AND business_group_id + 0 = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype,
element_information6 = p_paytab_name,
element_information7 = v_row_code
WHERE element_type_id = dedn_ele_ids(3)
AND business_group_id + 0 = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype,
element_information9 = p_mix_category,
benefit_classification_id = p_ben_class_id
WHERE element_type_id = dedn_ele_ids(1)
AND business_group_id + 0 = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype,
benefit_classification_id = NULL
WHERE element_type_id = dedn_ele_ids(2)
AND business_group_id + 0 = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype
WHERE element_type_id = dedn_ele_ids(4)
AND business_group_id + 0 = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype
WHERE element_type_id = dedn_ele_ids(3)
AND business_group_id + 0 = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype,
element_information9 = p_mix_category,
benefit_classification_id = p_ben_class_id
WHERE element_type_id = dedn_ele_ids(1)
AND business_group_id + 0 = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype,
benefit_classification_id = NULL
WHERE element_type_id = dedn_ele_ids(2)
AND business_group_id + 0 = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype
WHERE element_type_id = dedn_ele_ids(4)
AND business_group_id + 0 = p_bg_id;
UPDATE pay_element_types_f
SET element_information_category = g_ele_info_cat,
element_information1 = p_ele_cat,
element_information2 = p_partial_dedn,
element_information3 = p_proc_runtype
WHERE element_type_id = dedn_ele_ids(3)
AND business_group_id + 0 = p_bg_id;
END IF; -- Amount rule checks for formula insertion...
Now create calc formula for element by selecting "skeleton" calculation formula
and performing string substitutions for element name in proper placeholders.
The formula is then inserted into the current business group.
Other placeholders will be substituted based on other attributes (ie.
balances and arrears). When finished, the formula can be compiled.
*/
l_placeholder_ele_name := REPLACE(LTRIM(RTRIM(UPPER(p_ele_name))),' ','_');
select ff.formula_text
into l_calc_dedn_ff_text
from ff_formulas_f ff
where ff.formula_name = 'PRETAX_PERCENTAGE_FF_TEXT'
and ff.business_group_id is null
and ff.legislation_code = 'US'
and sysdate between ff.effective_start_date
and ff.effective_end_date;
select ff.formula_text
into l_calc_dedn_ff_text
from ff_formulas_f ff
where ff.formula_name = 'PRETAX_PAYROLL_TABLE_FF_TEXT'
and ff.business_group_id is null
and ff.legislation_code = 'US'
and sysdate between ff.effective_start_date
and ff.effective_end_date;
select ff.formula_text
into l_calc_dedn_ff_text
from ff_formulas_f ff
where ff.formula_name = 'PRETAX_BENEFIT_FF_TEXT'
and ff.business_group_id is null
and ff.legislation_code = 'US'
and sysdate between ff.effective_start_date
and ff.effective_end_date;
select ff.formula_text
into l_calc_dedn_ff_text
from ff_formulas_f ff
where ff.formula_name = 'PRETAX_FLAT_AMOUNT_FF_TEXT'
and ff.business_group_id is null
and ff.legislation_code = 'US'
and sysdate between ff.effective_start_date
and ff.effective_end_date;
UPDATE pay_status_processing_rules_f
SET formula_id = v_calc_rule_formula_id
WHERE status_processing_rule_id = already_exists
AND p_eff_start_date BETWEEN effective_start_date
AND effective_end_date;
UPDATE pay_status_processing_rules_f
SET formula_id = v_wh_formula_id
WHERE status_processing_rule_id = already_exists
AND p_eff_start_date BETWEEN effective_start_date
AND effective_end_date;
select max(display_sequence)
into dedn_base_seq
from pay_input_values_f
where element_type_id = dedn_ele_ids(1)
and g_eff_start_date between effective_start_date
and effective_end_date;
p_insert_update_flag => 'UPDATE',
p_input_value_id => dedn_base_iv_ids(h),
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => dedn_base_iv_mand(h),
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => dedn_base_iv_names(h),
p_uom => dedn_base_iv_uom(h),
p_min_value => NULL,
p_max_value => NULL,
p_default_value => dedn_base_iv_dflt(h),
p_lookup_type => dedn_base_iv_lkp(h),
p_formula_id => NULL,
p_generate_db_items_flag => dedn_base_iv_dbi(h),
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => dedn_base_iv_ids(h),
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => dedn_base_iv_mand(h),
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => dedn_base_iv_names(h),
p_uom => dedn_base_iv_uom(h),
p_min_value => NULL,
p_max_value => NULL,
p_default_value => dedn_base_iv_dflt(h),
p_lookup_type => dedn_base_iv_lkp(h),
p_formula_id => NULL,
p_generate_db_items_flag => dedn_base_iv_dbi(h),
p_warning_or_error => NULL);
select max(display_sequence)
into dedn_wh_seq
from pay_input_values_f
where element_type_id = dedn_ele_ids(2)
and g_eff_start_date between effective_start_date
and effective_end_date;
p_insert_update_flag => 'UPDATE',
p_input_value_id => dedn_wh_iv_ids(k),
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => dedn_wh_iv_mand(k),
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => dedn_wh_iv_names(k),
p_uom => dedn_wh_iv_uom(k),
p_min_value => NULL,
p_max_value => NULL,
p_default_value => dedn_wh_iv_dflt(k),
p_lookup_type => dedn_wh_iv_lkp(k),
p_formula_id => NULL,
p_generate_db_items_flag => dedn_wh_iv_dbi(k),
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => dedn_wh_iv_ids(k),
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => dedn_wh_iv_mand(k),
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => dedn_wh_iv_names(k),
p_uom => dedn_wh_iv_uom(k),
p_min_value => NULL,
p_max_value => NULL,
p_default_value => dedn_wh_iv_dflt(k),
p_lookup_type => dedn_wh_iv_lkp(k),
p_formula_id => NULL,
p_generate_db_items_flag => dedn_wh_iv_dbi(k),
p_warning_or_error => NULL);
select max(display_sequence)
into dedn_iv_seq
from pay_input_values_f
where element_type_id = dedn_ele_ids(3)
and g_eff_start_date between effective_start_date
and effective_end_date;
p_insert_update_flag => 'UPDATE',
p_input_value_id => dedn_si_iv_ids(siv),
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => dedn_si_iv_mand(siv),
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => dedn_si_iv_names(siv),
p_uom => dedn_si_iv_uom(siv),
p_min_value => NULL,
p_max_value => NULL,
p_default_value => dedn_si_iv_dflt(siv),
p_lookup_type => dedn_si_iv_lkp(siv),
p_formula_id => NULL,
p_generate_db_items_flag => dedn_si_iv_dbi(siv),
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => dedn_si_iv_ids(siv),
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => dedn_si_iv_mand(siv),
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => dedn_si_iv_names(siv),
p_uom => dedn_si_iv_uom(siv),
p_min_value => NULL,
p_max_value => NULL,
p_default_value => dedn_si_iv_dflt(siv),
p_lookup_type => dedn_si_iv_lkp(siv),
p_formula_id => NULL,
p_generate_db_items_flag => dedn_si_iv_dbi(siv),
p_warning_or_error => NULL);
select max(display_sequence)
into dedn_iv_seq
from pay_input_values_f
where element_type_id = dedn_ele_ids(4)
and g_eff_start_date between effective_start_date
and effective_end_date;
p_insert_update_flag => 'UPDATE',
p_input_value_id => dedn_sf_iv_ids(sfv),
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => dedn_sf_iv_mand(sfv),
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => dedn_sf_iv_names(sfv),
p_uom => dedn_sf_iv_uom(sfv),
p_min_value => NULL,
p_max_value => NULL,
p_default_value => dedn_sf_iv_dflt(sfv),
p_lookup_type => dedn_sf_iv_lkp(sfv),
p_formula_id => NULL,
p_generate_db_items_flag => dedn_sf_iv_dbi(sfv),
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => dedn_sf_iv_ids(sfv),
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => dedn_sf_iv_mand(sfv),
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => dedn_sf_iv_names(sfv),
p_uom => dedn_sf_iv_uom(sfv),
p_min_value => NULL,
p_max_value => NULL,
p_default_value => dedn_sf_iv_dflt(sfv),
p_lookup_type => dedn_sf_iv_lkp(sfv),
p_formula_id => NULL,
p_generate_db_items_flag => dedn_sf_iv_dbi(sfv),
p_warning_or_error => NULL);
select max(display_sequence)
into dedn_er_seq
from pay_input_values_f
where element_type_id = dedn_ele_ids(5)
and g_eff_start_date between effective_start_date
and effective_end_date;
p_insert_update_flag => 'UPDATE',
p_input_value_id => dedn_er_iv_ids(m),
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => dedn_er_iv_mand(m),
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => dedn_er_iv_names(m),
p_uom => dedn_er_iv_uom(m),
p_min_value => NULL,
p_max_value => NULL,
p_default_value => dedn_er_iv_dflt(m),
p_lookup_type => dedn_er_iv_lkp(m),
p_formula_id => NULL,
p_generate_db_items_flag => dedn_er_iv_dbi(m),
p_warning_or_error => NULL);
p_insert_update_flag => 'UPDATE',
p_input_value_id => dedn_er_iv_ids(m),
p_rowid => NULL,
p_recurring_flag => 'N',
p_mandatory_flag => dedn_er_iv_mand(m),
p_hot_default_flag => 'N',
p_standard_link_flag => 'N',
p_classification_type => 'N',
p_name => dedn_er_iv_names(m),
p_uom => dedn_er_iv_uom(m),
p_min_value => NULL,
p_max_value => NULL,
p_default_value => dedn_er_iv_dflt(m),
p_lookup_type => dedn_er_iv_lkp(m),
p_formula_id => NULL,
p_generate_db_items_flag => dedn_er_iv_dbi(m),
p_warning_or_error => NULL);
SELECT pbc.balance_type_id
FROM pay_balance_classifications pbc,
pay_element_classifications pec,
pay_balance_types pbt
WHERE nvl(pbc.business_group_id, p_busgrp_id) = p_busgrp_id
AND nvl(pbc.legislation_code, 'US') = 'US'
AND pbc.classification_id = pec.classification_id
AND pec.classification_name = 'Pre-Tax Deductions'
AND pec.business_group_id is null
AND pec.legislation_code = 'US'
AND pbc.balance_type_id = pbt.balance_type_id
AND pbt.balance_name not in ('Net', 'Payments');
SELECT pbf.balance_type_id
FROM pay_balance_feeds_f pbf,
pay_balance_types pbt
WHERE pbf.input_value_id = p_src_iv_id
AND nvl(pbf.business_group_id, p_busgrp_id) = p_busgrp_id
AND nvl(pbf.legislation_code, 'US') = 'US'
AND pbt.balance_type_id = pbf.balance_type_id
AND pbt.balance_name not in ('Net', 'Payments');
select name
into v_bg_name
from per_business_groups
where business_group_id = p_bg_id;
SELECT FF.formula_id
INTO v_skip_formula_id
FROM ff_formulas_f FF
WHERE FF.formula_name = 'CHAINED_SKIP_FORMULA'
AND FF.business_group_id IS NULL
AND FF.legislation_code = g_template_leg_code
AND p_ele_eff_start_date >= FF.effective_start_date
AND p_ele_eff_start_date <= FF.effective_end_date;
SELECT FF.formula_id
INTO v_skip_formula_id
FROM ff_formulas_f FF
WHERE FF.formula_name = 'THRESHOLD_SKIP_FORMULA'
AND FF.business_group_id IS NULL
AND FF.legislation_code = g_template_leg_code
AND p_ele_eff_start_date >= FF.effective_start_date
AND p_ele_eff_start_date <= FF.effective_end_date;
SELECT FF.formula_id
INTO v_skip_formula_id
FROM ff_formulas_f FF
WHERE FF.formula_name = 'FREQ_RULE_SKIP_FORMULA'
AND FF.business_group_id IS NULL
AND FF.legislation_code = g_template_leg_code
AND p_ele_eff_start_date >= FF.effective_start_date
AND p_ele_eff_start_date <= FF.effective_end_date;
select distinct(classification_id)
into v_primary_class_id
from pay_element_classifications
where upper(classification_name) = upper(p_ele_classification)
and business_group_id is null
and legislation_code = 'US';
SELECT default_low_priority,
default_high_priority
INTO v_class_lo_priority,
v_class_hi_priority
FROM pay_element_classifications
WHERE classification_id = v_primary_class_id
AND business_group_id is null
AND legislation_code = 'US';
select default_high_priority
into l_invol_dflt_prio
from pay_element_classifications
where UPPER(classification_name) = UPPER(g_invol_class_name)
and business_group_id is null
and legislation_code = 'US';
select default_priority
into v_emp_liab_dflt_prio
from pay_element_classifications
where classification_name = 'Employer Liabilities'
/* added check for legislation_code BUG 912994 */
and legislation_code = g_template_leg_code;
UPDATE pay_input_values_f
SET mandatory_flag = 'X'
WHERE element_type_id = v_ele_type_id
AND name = v_pay_value_name;
SELECT input_value_id
INTO v_payval_id
FROM pay_input_values_f
WHERE element_type_id = v_ele_type_id
AND name = v_pay_value_name;
update pay_element_types_f
set ELEMENT_INFORMATION_CATEGORY='US_EMPLOYER LIABILITIES' ,
element_information10 = v_er_charge_baltype_id
where element_type_id = dedn_ele_ids(5)
and g_eff_start_date between effective_start_date and effective_end_date;
UPDATE pay_element_types_f
SET element_information10 = dedn_assoc_bal_ids(1), -- primary bal
element_information11 = dedn_assoc_bal_ids(5), -- accrued bal
element_information12 = dedn_assoc_bal_ids(6), -- arrears bal
element_information13 = dedn_assoc_bal_ids(4), -- not taken bal
element_information15 = dedn_assoc_bal_ids(7), -- able amount bal
element_information16 = dedn_assoc_bal_ids(2), -- addl amount bal
element_information17 = dedn_assoc_bal_ids(3), -- repl amount bal
element_information18 = dedn_ele_ids(3), -- Special Inputs
element_information19 = dedn_ele_ids(4), -- Special Features
element_information20 = dedn_ele_ids(2) -- Withholding ele
WHERE element_type_id = dedn_ele_ids(1)
AND business_group_id + 0 = p_bg_id;