[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
22-JUN-2005 ssouresr 115.19 Changed the cursor c_actions to not select
assignment actions with blank tax_unit_ids
also changed c_actions to break dependency
between payroll and consolidation set
01-SEP-2004 mmukherj 115.18 Added action_status check when joining
to pay_payment_information_v. This is done
due to changes to view for bug 3826732.
13-APR-2004 ssouresr 115.17 Corrected version 115.15 by changing the
cursors.
13-APR-2004 ssouresr 115.15 The function action_creation is changed
so that assignment actions are not created
twice for any reversals locked by any
prepayments.
25-MAR-2004 ssattini 115.14 Changed c_actions cursor to fix
11510 bug#3534182, to validate the
parameter values correctly.
12-JAN-2004 ssattini 115.13 Changed c_actions cursor to fix
11510 performance fix bug#3356268.
23-MAY-2003 vpandya 115.10 Changed for Multi GRE functionality:
action_creation is changed. Please do diff
with previous version to see changes.
06-MAR-2003 ssattini 115.7 Changed Sort Action query to consider
the terminated employees. Fix#2780747.
20-NOV-2002 ssouresr 115.6 Changed Organization and Location to caps,
because the these two parameters will not
be in lower case anymore.
29-OCT-2002 tclewis 115.4 Modified the action_creation procedure
specifically modifing c_payroll_run cursor
to return the max master assignment action id.
18-OCT-2002 tclewis 115.3 Modified the action_creation cursor removing
the for update of . . . added a for update
on the lock the created assignment_action_id.
28-AUG-2002 tclewis 115.2 Modified the action creation cursor
for the umbrella process and for
multiple assignment processing.
30-MAR-2001 jgoswami 115.1 Changed package name from
pay_payrg_pkg to pay_ca_payrg_pkg
as it was conflicting with pypayreg.pkb
29-OCT-1999 jgoswami 110.0 Created based on pypayreg.pkb 110.1 99/08/04 rthakur
Original file pypayreg.pkb info
09-MAR-1999 meshah 40.0 created
04-AUG-1999 rmonge 110.1 Made package body adchkdrv compliant.
--
*/
----------------------------------- range_cursor ----------------------------------
--
procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
l_payroll_id number;
select legislative_parameters
into leg_param
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
sqlstr := 'select distinct asg.person_id
from pay_assignment_actions act,
per_assignments_f asg,
pay_payroll_actions ppa2, /* run and quickpay payroll actions */
pay_assignment_actions act2, /* run and quickpay assignment actions */
pay_action_interlocks pai, /* interlocks table */
pay_payroll_actions ppa, /* PYUGEN information */
pay_payroll_actions pa1 /* Payroll Register information */
where ppa.payroll_action_id = :payroll_action_id
and pa1.consolidation_set_id =
nvl(pay_payrg_pkg.get_parameter(''C_ST_ID'',ppa.legislative_parameters),pa1.consolidation_set_id)
and pa1.payroll_id =
nvl(pay_payrg_pkg.get_parameter(''PY_ID'',ppa.legislative_parameters),pa1.payroll_id)
and pa1.effective_date between /* date join btwn payreg and pyugen ppa */
ppa.start_date and ppa.effective_date
and pa1.payroll_action_id = act.payroll_action_id
and asg.assignment_id = act.assignment_id
and pa1.effective_date between /* date join btwn payreg and asg */
asg.effective_start_date and asg.effective_end_date
and pa1.action_type in (''P'',''U'',''V'')
and act.action_status = ''C''
and act.assignment_action_id = pai.locking_action_id
and act2.assignment_action_id = pai.locked_action_id
and act2.payroll_action_id = ppa2.payroll_action_id
and ppa2.action_type in (''R'',''Q'')
and act2.action_status = ''C''
and act2.tax_unit_id =
nvl(pay_payrg_pkg.get_parameter(''T_U_ID'',ppa.legislative_parameters), act2.tax_unit_id)
and asg.organization_id =
nvl(pay_payrg_pkg.get_parameter(''O_ID'',ppa.legislative_parameters), asg.organization_id)
and asg.location_id =
nvl(pay_payrg_pkg.get_parameter(''L_ID'',ppa.legislative_parameters), asg.location_id)
and asg.person_id =
nvl(pay_payrg_pkg.get_parameter(''P_ID'',ppa.legislative_parameters), asg.person_id)
and asg.business_group_id +0 =
pay_payrg_pkg.get_parameter(''B_G_ID'',ppa.legislative_parameters)
order by asg.person_id';
select act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
ppa.action_type,
ppa.effective_date,
act.source_action_id
from pay_assignment_actions act,
per_assignments_f paf,
pay_payroll_actions ppa, /* pre-payments and reversals
payroll action id */
pay_payroll_actions ppa1, /* PYUGEN payroll action id */
pay_all_payrolls_f ppf
where ppa1.payroll_action_id = pactid
and ((ppf.payroll_id = cp_payroll_id) OR
(cp_payroll_id is null))
and paf.business_group_id = cp_bg_id
and paf.payroll_id = ppf.payroll_id
and paf.person_id between stperson and endperson
and ((paf.organization_id = cp_org_id) OR
(cp_org_id is null))
and ((paf.location_id = cp_loc_id) OR
(cp_loc_id is null ))
and ((paf.person_id = cp_person_id) OR
(cp_person_id is null))
and ppa.payroll_id = ppf.payroll_id
and ppa.consolidation_set_id = cp_cons_set_id
and ppa.effective_date between
ppa1.start_date and ppa1.effective_date
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and ppa.business_group_id = ppa1.business_group_id
and ppa.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and act.payroll_action_id = ppa.payroll_action_id
and paf.assignment_id = act.assignment_id
and act.action_status = 'C'
and act.source_action_id is null
--and ((act.tax_unit_id = cp_tax_unit_id) OR
-- (cp_tax_unit_id is null) OR
-- (act.tax_unit_id is null))
and ( ((act.tax_unit_id = cp_tax_unit_id) and (cp_tax_unit_id is not null))
or ((act.tax_unit_id is not null) and (cp_tax_unit_id is null))
--changes started for bug 5152897
or (act.tax_unit_id is null)
-- changes ended for bug 5152897
)
and ( ( ppa.action_type in ('P','U') and
( exists ( select 1
from pay_action_interlocks pai1
,pay_assignment_actions paa1
,pay_payroll_actions ppa2
where pai1.locking_action_id = act.assignment_action_id
and paa1.assignment_action_id = pai1.locked_action_id
and ppa2.payroll_action_id = paa1.payroll_action_id
and ppa2.action_type <> 'V' ))) OR
( ppa.action_type = 'V' ) )
order by act.assignment_id;
select org_information1
from hr_organization_information
where organization_id = cp_busi_grp_id
and org_information_context = 'Payroll Archiver Level';
select assignment_id,
tax_unit_id,
nvl(source_action_id,-999)
from pay_payment_information_v
where assignment_action_id = cp_prepay_action_id
and action_status = 'C'
order by 3,1,2;
select run_type_id
from pay_run_types_f
where legislation_code = 'CA'
and run_method = 'S'
and shortname = 'SEP_PAY';
select ppf.multi_assignments_flag
from pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_all_payrolls_f ppf
where assignment_action_id = cp_prepay_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppf.payroll_id = ppa.payroll_id
and ppa.effective_date between ppf.effective_start_date
and ppf.effective_end_date;
select paa.assignment_action_id
from pay_assignment_actions paa
where paa.source_action_id = cp_prepay_action_id
and paa.assignment_id = cp_assignment_id
and paa.tax_unit_id = cp_tax_unit_id;
select paa.assignment_action_id
from pay_action_interlocks pai
,pay_assignment_actions paa
,pay_payroll_actions ppa
where pai.locked_action_id = cp_source_action_id
and paa.assignment_action_id = pai.locking_action_id
and paa.source_action_id is not null
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ( 'P', 'U' );
select max(paa.assignment_action_id)
from pay_assignment_actions paa,
pay_action_interlocks pai,
pay_run_types_f prt,
pay_payroll_actions ppa
where pai.locking_action_id = cp_prepay_action_id
and paa.assignment_action_id = pai.locked_action_id
and paa.assignment_id = cp_assignment_id
and paa.tax_unit_id = cp_tax_unit_id
and paa.run_type_id <> cp_sepchk_run_tp_id
and prt.legislation_code = 'CA'
and prt.run_type_id = paa.run_type_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type <> 'V'
and prt.run_method <> 'C';
select max(paa.assignment_action_id)
from pay_assignment_actions paa,
pay_action_interlocks pai,
pay_run_types_f prt,
pay_payroll_actions ppa
where pai.locking_action_id = cp_prepay_action_id
and paa.assignment_action_id = pai.locked_action_id
and paa.assignment_id = cp_assignment_id
and paa.run_type_id <> cp_sepchk_run_tp_id
and prt.legislation_code = 'CA'
and prt.run_type_id = paa.run_type_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type <> 'V'
and prt.run_method <> 'C';
select paa.action_sequence
from pay_assignment_actions paa
where paa.assignment_action_id = cp_assignment_action_id;
select distinct ppi.assignment_id
from pay_payment_information_v ppi
where ppi.assignment_action_id = cp_prepay_asg_act_id
and ppi.assignment_id <> cp_assignment_id
and ppi.action_status = 'C'
and ppi.source_action_id is null;
select paa_run.action_sequence, paa_run.assignment_action_id
from pay_action_interlocks pai,
pay_assignment_actions paa_run,
pay_payroll_actions ppa_run,
pay_run_types_f prt
where pai.locking_action_id = cp_prepay_asg_act_id
and paa_run.assignment_action_id = pai.locked_action_id
and paa_run.assignment_id = cp_assignment_id
and ppa_run.payroll_action_id = paa_run.payroll_action_id
and ppa_run.action_type in ( 'R', 'Q' )
and prt.legislation_code = 'CA'
and prt.run_type_id = paa_run.run_type_id
and prt.run_method <> 'C'
and ( ( prt.shortname <> 'SEP_PAY' ) OR
( prt.shortname = 'SEP_PAY' and
paa_run.action_sequence <= cp_action_sequence )
)
order by paa_run.action_sequence desc;
l_action_insert varchar2(1);
select legislative_parameters, business_group_id,
to_number(pay_payrg_pkg.get_parameter('PY_ID', ppa.legislative_parameters)),
to_number(pay_payrg_pkg.get_parameter('C_ST_ID', ppa.legislative_parameters)),
to_number(pay_payrg_pkg.get_parameter('T_U_ID', ppa.legislative_parameters)),
to_number(pay_payrg_pkg.get_parameter('O_ID', ppa.legislative_parameters)),
to_number(pay_payrg_pkg.get_parameter('L_ID', ppa.legislative_parameters)),
to_number(pay_payrg_pkg.get_parameter('P_ID', ppa.legislative_parameters)),
to_number(pay_payrg_pkg.get_parameter('B_G_ID', ppa.legislative_parameters))
into l_leg_param, ln_busi_grp_id,
ln_leg_payroll_id,
ln_leg_cons_set_id,
ln_leg_tax_unit_id,
ln_leg_org_id,
ln_leg_loc_id,
ln_leg_person_id,
ln_leg_bg_id
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
l_action_insert := 'N';
insert into pay_us_rpt_totals
(session_id,
tax_unit_id,
location_id,
value1,
value2)
values(pactid,
pactid,
lockingactid,
ln_max_run_aa_id,
ln_assignment_id);
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
update pay_assignment_actions
set serial_number = serialno
where assignment_action_id = lockingactid
and tax_unit_id = ln_tax_unit_id;
insert into pay_us_rpt_totals
(session_id,
tax_unit_id,
location_id,
value1,
value2)
values(pactid,
pactid,
lockingactid,
ln_max_run_aa_id,
ln_assignment_id);
insert into pay_us_rpt_totals
(session_id,
tax_unit_id,
location_id,
value1,
value2)
values(pactid,
pactid,
lockingactid,
ln_map_max_aaid,
c_asg.assignment_id);
insert into pay_us_rpt_totals
(session_id,
tax_unit_id,
location_id,
value1,
value2)
values(pactid,
pactid,
lockingactid,
ln_max_run_aa_id,
ln_assignment_id);
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
update pay_assignment_actions
set serial_number = serialno
where assignment_action_id = lockingactid
and tax_unit_id = greid;
insert into pay_us_rpt_totals
(session_id,
tax_unit_id,
location_id,
value1)
values(pactid,
pactid,
lockingactid,
lockedactid);
sqlstr := 'select paa1.rowid /* we need the row id of the assignment actions that are created by PYUGEN */
from hr_all_organization_units hou,
hr_all_organization_units hou1,
hr_locations_all loc,
per_all_people_f ppf,
per_all_assignments_f paf,
pay_assignment_actions paa1, /* PYUGEN assignment action */
pay_payroll_actions ppa1 /* PYUGEN payroll action id */
where ppa1.payroll_action_id = :pactid
and paa1.payroll_action_id = ppa1.payroll_action_id
and paa1.assignment_id = paf.assignment_id
and paf.effective_start_date =
( select max(paf1.effective_start_date)
from per_all_assignments_f paf1
where paf1.assignment_id = paf.assignment_id
and paf1.effective_start_date <= ppa1.effective_date
and paf1.effective_end_date >= ppa1.start_date
)
and hou1.organization_id = paa1.tax_unit_id
and hou.organization_id = paf.organization_id
and loc.location_id = paf.location_id
and ppf.person_id = paf.person_id
and ppa1.effective_date between
ppf.effective_start_date and ppf.effective_end_date
order by
decode(pay_payrg_pkg.get_parameter(''P_S1'',ppa1.legislative_parameters),
''GRE'',hou1.name,
''ORGANIZATION'',hou.name,
''LOCATION'',loc.location_code,null),
decode(pay_payrg_pkg.get_parameter(''P_S2'',ppa1.legislative_parameters),
''GRE'',hou1.name,
''ORGANIZATION'',hou.name,
''LOCATION'',loc.location_code,null),
decode(pay_payrg_pkg.get_parameter(''P_S3'',ppa1.legislative_parameters),
''GRE'',hou1.name,
''ORGANIZATION'',hou.name,
''LOCATION'',loc.location_code,null),
hou.name,ppf.full_name
for update of paa1.assignment_action_id';
SELECT balance_name
INTO v_balance_name
FROM pay_balance_types
WHERE balance_type_id = p_hours_balance;