[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
15-AUG-2001 tmehra 110.3 Updated the above remark
07-DEC-2001 irgonzal 115.4 Same as 115.2
115.3 changes are not required
due to performance issues.
04-FEB-2002 meshah 115.5 2166701 added procedure load_data,
load_state_taxes and
load_federal_taxes. Also changed
some cursors for performance.
05-FEB-2002 meshah 115.6 Added checkfile entry to the file.
05-MAR-2002 meshah 115.7 removed to_char from 401, 403 and
457 in hr_utility.
06-MAR-2002 meshah 115.8 cursor c_federal_taxes was refered
at a wrong place in load_federal_balance.
this was causing report to error out
with invalid cursor error.
20-MAR-2002 meshah 115.9 changed the date checking in load_data
and removed per_assignments_f table
from load_state_data.
27-MAR-2002 meshah 115.10 2280318 the call to get_tax_balances has been
changed. Instead of calling it in the
sql statement we are calling it in a
loop. Also the get_value function has
been changed from pay_us_balance_view_pkg
to pay_balance_pkg for Tax Group balances
and setting the TAX GROUP context instead
of the TAX UNIT ID.
30-APR-2002 meshah 115.11 2345031 cursor sel_aaid has been changed in load_data
we are now selecting effective_end_date for
the assignment. This date is then compared
with as_of_date and the lowest date is then
passed to the other procedures/functions.
This is required to handle terminated employees.
07-JUN-2002 sshetty 115.12 Added qualifier to func get_pqp_limit
and get_457_annual_limit.
25-NOV-2002 irgonzal 115.13 2664340 Added logic to handle Catchup balances.
03-DEC-2002 irgonzal 115.14 2664340 Modified Load_Fed_Catchup_Balance
procedure and ensure the balance_name
does not concatenate "_".
03-DEC-2002 irgonzal 115.15 2664340 Modified Load_Fed_Catchup_Balance
procedure and added upper function
when getting balance name.
17-DEC-2002 irgonzal 115.16 2714501 Modified load_data procedure: initialized
l_as_of_date within the "sel_aaid" loop.
19-DEC-2002 irgonzal 115.17 2693022 Added logic to handle USERRA balances.
18-MAY-2003 vgunasek 115.18 2938556 report rewrite including support for
new balance reporting architecture (run
balances) and multi threading.
06-JUN-2003 vgunasek 115.19 2938556 Changed code to check changes in Tax group
removed chnkno = 1 check to insert dummy
assignment action. Some Spell changes in
comments.
06-JUN-2003 vgunasek 115.20 2938556 Changed comments and fixed gscc errors.
12-JUN-2003 vgunasek 115.21 3002767 Initialised g_inserted_asg_action_id_flag
for all assignments. Made state query as
rule based.
19-JUN-2003 kaverma 115.22 3015312 Corrected the declaration of l_leg_param in
load_data
24-JUN-2003 kaverma 115.23 3018606 Corrected call to load_federal_taxes and load_state_taxes
in load_data procedure.
07-AUG-2003 sshetty 115.24 Added a check for Defined
Contrib Plan over limit.
05-SEP-2003 sdahiya 115.25 3118107 Added code in load_data procedure for insertion
of assignment action id.
02-JAN-2004 sshetty 115.26 3349624 Changed the Dimension name referenced
for DCP from PER_YTD to PER_GRE_YTD.
02-SEP-2004 tmehra 115.27 3770316 Removed the 403b and 457 Catchup
limit checking for tax_type = null
option.
16-NOV-2006 ckesanap 115.28 4521358 Added the 'Roth 401k' and 'Roth 403b' over limit
check. The balance values of both deferred 401k and Roth 401k
are combined and then checked for exceeding the annual limit.
Similarly for 403b.
***************************************************************************/
AS
--------------------- GLOBAL variables ----------------------------------
l_start_date pay_payroll_actions.start_date%type;
g_inserted_asg_action_id_flag varchar2(1):= 'N';
g_inserted_asg_action_id number;
p_insert_done_flag varchar2(1):= 'N';
select org_information5
from hr_organization_information
where organization_id = cp_tax_unit_id
and org_information_context = 'Federal Tax Rules';
select creator_id
from ff_database_items fdi,
ff_user_entities fue
where fue.user_entity_id = fdi.user_entity_id
and fue.creator_type='B'
and fdi.user_name = cp_balance_name;
select creator_id
from ff_database_items fdi,
ff_user_entities fue
where fue.user_entity_id = fdi.user_entity_id
and fue.creator_type='B'
and fdi.user_name = cp_balance_name;
select creator_id
from ff_database_items fdi,
ff_user_entities fue
where fue.user_entity_id = fdi.user_entity_id
and fue.creator_type='B'
and fdi.user_name = cp_balance_name;
hr_utility.trace('Inserting fed_balance_list table with balance name ' || t_fed_balance_list(i).balance_name );
hr_utility.trace('Inserting fed_balance_list table with balance id ' || t_fed_balance_list(i).balance_id );
select creator_id
from ff_database_items fdi,
ff_user_entities fue
where fue.user_entity_id = fdi.user_entity_id
and fue.creator_type='B'
and fdi.user_name = cp_balance_name;
hr_utility.trace('Inserting state_balance_list table with balance name ' || t_state_balance_list(i).balance_name );
hr_utility.trace('Inserting state_balance_list table with balance id ' || t_state_balance_list(i).balance_id );
select /*+RULE */
pus.state_abbrev,
pest.state_code,
pest.jurisdiction_code
from
pay_us_emp_state_tax_rules_f pest,
pay_us_states pus
where
pest.assignment_id = p_asg_id
and p_as_of_date between pest.effective_start_date
and pest.effective_end_date
and pus.state_code = pest.state_code
order by state_abbrev;
if (g_inserted_asg_action_id_flag = 'N') then
select pay_assignment_actions_s.nextval
into new_asg_act_id
from dual;
g_inserted_asg_action_id_flag := 'Y';
g_inserted_asg_action_id_flag := 'N';
new_asg_act_id := g_inserted_asg_action_id;
hr_utility.trace('Inserted Assignment action id: '||to_char(new_asg_act_id));
select paf.assignment_number
,paf.person_id
,ppf.first_name
,ppf.middle_names
,ppf.last_name
,ppf.national_identifier
into l_assignment_number
,l_person_id
,l_first_name
,l_middle_name
,l_last_name
,l_ssn
from per_assignments_f paf
,per_people_f ppf
where paf.assignment_id = p_asg_id
and paf.effective_end_date = ( select max(paf1.effective_end_date)
from per_assignments_f paf1
where paf1.assignment_id = paf.assignment_id
and paf1.effective_start_date <= p_as_of_date)
and ppf.person_id = paf.person_id
and p_as_of_date between ppf.effective_start_date and ppf.effective_end_date;
insert into pay_us_rpt_totals
( attribute2, -- :PACTID
session_id,
tax_unit_id,
gre_name, organization_name,
location_name, state_code, state_abbrev
,value7 -- asg. id
,attribute7 -- tax type
,value1 -- state_taxable_value
,value2 -- state_over_limit
,value3 -- state_tax_limit
,attribute5 -- New Assignment Action id
,attribute10 -- Assignment number
,attribute11 -- Person id
,attribute12 -- First name
,attribute13 -- Middle name
,attribute14 -- Last name
,attribute15 -- SSN
)
values
(t_payroll_action_id
,p_ppa_finder
,p_tax_unit_id
,l_gre_name, l_org_name
,l_location_code, l_state_code, l_state_abbrev
,p_asg_id
,l_state_tax_type
,nvl(l_state_taxable_value,0)
,l_over_limit
,nvl(l_state_tax_limit,0)
,new_asg_act_id
,l_assignment_number
,l_person_id
,l_first_name
,l_middle_name
,l_last_name
,l_ssn
);
hr_utility.trace('Inserting a record in pay_us_rpt_totals for the employee ' ||l_last_name || ' ' ||
l_first_name );
hr_utility.trace('Inserted chunk number ' ||p_chnkno || ' for tax type ' || l_state_tax_type );
select bal.balance_type_id, bal.balance_name
from pay_balance_types bal
where bal.balance_name like cp_tax_type
and bal.business_group_id = cp_bg_id
order by bal.balance_name DESC;
select 'Y'
from pay_balance_feeds_f
where balance_type_id = cp_balance_type_id
and business_group_id = cp_bg_id;
select def.defined_balance_id
from pay_defined_balances def
,pay_balance_dimensions dim
where def.balance_type_id = cp_balance_type_id
and def.business_group_id = cp_bg_id
and def.balance_dimension_id = dim.balance_dimension_id
and dim.legislation_code = 'US'
and dim.dimension_name = cp_dimension;
select pay_assignment_actions_s.nextval
into new_asg_act_id
from dual;
-- insert the action record.
hr_utility.trace('New Assignment action id: '||to_char(new_asg_act_id));
g_inserted_asg_action_id := new_asg_act_id;
g_inserted_asg_action_id_flag := 'Y';
select paf.assignment_number
,paf.person_id
,ppf.first_name
,ppf.middle_names
,ppf.last_name
,ppf.national_identifier
into l_assignment_number
,l_person_id
,l_first_name
,l_middle_name
,l_last_name
,l_ssn
from per_assignments_f paf
,per_people_f ppf
where paf.assignment_id = p_asg_id
and paf.effective_end_date = ( select max(paf1.effective_end_date)
from per_assignments_f paf1
where paf1.assignment_id = paf.assignment_id
and paf1.effective_start_date <= p_as_of_date)
and ppf.person_id = paf.person_id
and p_as_of_date between ppf.effective_start_date and ppf.effective_end_date;
insert into pay_us_rpt_totals
( attribute2 -- :PACTID
,session_id
,tax_unit_id
,gre_name, organization_name, location_name
,value7 -- asg. id
,attribute7 -- tax type
,value1 -- fed_taxable_value
,value2 -- fed_over_limit
,value3 -- fed_tax_limit
,attribute5 -- New Assignment Action id
,attribute10 -- Assignment number
,attribute11 -- Person id
,attribute12 -- First name
,attribute13 -- Middle name
,attribute14 -- Last name
,attribute15 -- SSN
)
values
(t_payroll_action_id
,p_ppa_finder
,p_tax_unit_id
,l_gre_name, l_org_name, l_location_code
,p_asg_id
,l_fed_tax_type
,l_fed_taxable_value
,l_over_limit
,l_fed_tax_limit
,new_asg_act_id
,l_assignment_number
,l_person_id
,l_first_name
,l_middle_name
,l_last_name
,l_ssn
);
hr_utility.trace('Inserting a record in pay_us_rpt_totals for the employee ' ||l_last_name || ' ' || l_first_name
);
hr_utility.trace('Inserted chunk number ' ||p_chnkno || ' for tax type ' || l_fed_tax_type );
select
ppa_arch.start_date start_date,
ppa_arch.effective_date end_date,
ppa_arch.business_group_id business_group_id,
ppa_arch.payroll_action_id payroll_action_id,
-- to_number(paa.serial_number) assignment_action_id, -- max assignment_action_id
paa.assignment_id assignment_id,
paa.tax_unit_id tax_unit_id,
hou.name gre_name,
paf.organization_id organization_id,
hou1.name organization_name,
paf.location_id location_id,
hrl.location_code location_code,
paf.effective_end_date max_end_date
from hr_locations_all hrl,
hr_all_organization_units hou1,
hr_all_organization_units hou,
per_assignments_f paf,
pay_assignment_actions paa, -- PYUGEN
pay_payroll_actions ppa_arch -- PYUGEN
where
-- ppa_arch.payroll_action_id = l_pactid
paa.assignment_action_id = l_aaid
and paa.payroll_action_id = ppa_arch.payroll_action_id
-- and paa.chunk_number = l_chnkno
and paf.assignment_id = paa.assignment_id
and paf.effective_end_date = ( select max(effective_end_date)
from per_assignments_f paf1
where paf1.assignment_id = paf.assignment_id
and paf1.effective_start_date <=
ppa_arch.effective_date
)
and hrl.location_id = NVL(paf.location_id,hou.location_id)
and hou1.organization_id = nvl(paf.organization_id,paf.business_group_id)
and hou.organization_id = paa.tax_unit_id;
select ppa.legislative_parameters,
ppa.business_group_id,
ppa.start_date,
ppa.effective_date,
pay_us_over_limit_pkg.get_parameter('GRE',ppa.legislative_parameters),
pay_us_over_limit_pkg.get_parameter('AS_OF_DATE',ppa.legislative_parameters),
pay_us_over_limit_pkg.get_parameter('TAX_TYPE',ppa.legislative_parameters),
pay_us_over_limit_pkg.get_parameter('PPA_FINDER',ppa.legislative_parameters),
ppa.payroll_action_id
into l_leg_param,
l_business_group_id,
l_leg_start_date,
l_leg_end_date,
t_gre_id,
l_date_prm,
l_tax_type,
l_ppa_finder,
t_payroll_action_id
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
g_inserted_asg_action_id_flag := 'N';
hr_utility.trace('Inserting Limit Values using utilities package');
if p_insert_done_flag = 'N' then
l_prev_tg := l_tax_group;
if p_insert_done_flag = 'N' then
select pay_assignment_actions_s.nextval
into new_asg_act_id
from dual;
p_insert_done_flag := 'Y';