[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
insert the number of records per thread in the table
pay_us_rpt_totals which is used to get Rpt Seq Id. If the
report is run for a specific action type this Id will not
show correct value.
Change List
-----------
Date Name Vers Description
----------- ---------- ----- ----------------------------
05-APR-1999 meshah 40.0/110.0 created
04-AUG-1999 rmonge 40.0/110.1 Made package body adchkdrv
compliant.
26-SEP-2000 sravuri 115.2 Added Assignment Set
functionality to the package.
13-APR-2001 ahanda 115.3 Changed HR_LOCATIONS to
HR_LOCATIONS_ALL.
26-apr-2001 tclewis 115.4 modified the cursor(s) in the
range_cursor and action creation
to use secure views. Modified
the sql query in the sort_code
routine to use base tables.
21-oct-2002 tclewis 115.5 commented out the "for update..."
in the action_creation cursor.
changed the locking on the sort_cursor
from paf.assignment_id to paa.
16-SEP-2003 sdahiya 115.7 modified the sort_action procedure
(Bug# 3037633).Added nocopy changes
16-OCT-2003 sdahiya 115.8 Modified sort_action procedure so that
it sorts data first on employee name
and later on date paid (Bug 3037633).
09-FEB-2004 ssmukher 115.9 11.5.10 Performance Fix (Bug 3372732)
in action_creation
23-AUG-2005 jgoswami 115.10 R12 Performance Fix (Bug 4347329)
in range_creation
16-Jun-2008 pannapur 115.11 Modified the cursor definitions of all
process types to generate proper sequence id
(6854964)
*/
-------------------------------- range_cursor ----------------------------------
PROCEDURE range_cursor (pactid in number,
sqlstr out nocopy varchar2) is
--
leg_param pay_payroll_actions.legislative_parameters%type;
select legislative_parameters
into leg_param
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
select ppa.legislative_parameters,
pay_payrg_pkg.get_parameter('P_P_TY', ppa.legislative_parameters),
pay_payrg_pkg.get_parameter('C_ST_ID', ppa.legislative_parameters),
pay_payrg_pkg.get_parameter('PY_ID', ppa.legislative_parameters),
pay_payrg_pkg.get_parameter('O_ID', ppa.legislative_parameters),
pay_payrg_pkg.get_parameter('L_ID', ppa.legislative_parameters),
pay_payrg_pkg.get_parameter('P_ID', ppa.legislative_parameters),
ppa.start_date,
ppa.effective_date,
ppa.business_group_id
into leg_param,
pay_process,
l_consolidation_set_id,
l_payroll_id,
l_organization_id,
l_location_id,
l_person_id,
l_leg_start_date,
l_leg_end_date,
l_business_group_id
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
'select distinct asg.person_id
from pay_payroll_actions ppa,
pay_payroll_actions pa1,
pay_assignment_actions act,
per_assignments_f asg
where ppa.payroll_action_id = :payroll_action_id
'||l_consolidation_set_text||'
'||l_payroll_text||'
and pa1.effective_date between ppa.start_date
and ppa.effective_date
and pa1.effective_date between asg.effective_start_date
and asg.effective_end_date
and pa1.action_type in (''B'',''D'',''I'',''R'',''Q'',''V'')
and pa1.payroll_action_id = act.payroll_action_id
and asg.assignment_id = act.assignment_id
and act.action_status = ''C''
and asg.organization_id = nvl('''||l_organization_id||''',
asg.organization_id)
and asg.location_id = nvl('''||l_location_id||''',
asg.location_id)
and asg.person_id = nvl('''||l_person_id||''',
asg.person_id)
and asg.business_group_id +0 = ppa.business_group_id
order by asg.person_id';
select pay_payrg_pkg.get_parameter('PY_ID',ppa.legislative_parameters) payroll_id,
pay_payrg_pkg.get_parameter('C_ST_ID',ppa.legislative_parameters) consolidation_set_id,
pay_payrg_pkg.get_parameter('T_U_ID',ppa.legislative_parameters) tax_unit_id,
pay_payrg_pkg.get_parameter('L_ID',ppa.legislative_parameters) location_id,
pay_payrg_pkg.get_parameter('O_ID',ppa.legislative_parameters) organization_id,
pay_payrg_pkg.get_parameter('P_ID',ppa.legislative_parameters) person_id,
pay_payrg_pkg.get_parameter('B_G_ID',ppa.legislative_parameters) business_group_id,
ppa.start_date start_date,
ppa.effective_date effective_date
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
select act.assignment_action_id,
act.assignment_id,
act.tax_unit_id
from pay_assignment_actions act,
per_assignments_f paf,
pay_payroll_actions ppa, /* pre-payments and reversals
payroll action id */
pay_payrolls_f ppf -- Bug 3372732
where ppa.payroll_id = nvl(c_payroll_id,ppa.payroll_id)
and ppa.consolidation_set_id + 0 = nvl(c_consolidation_set_id,
ppa.consolidation_set_id)
and ppa.effective_date between c_start_date
and c_effective_date
and act.tax_unit_id = nvl(c_tax_unit_id ,act.tax_unit_id)
and paf.organization_id = nvl(c_organization_id,paf.organization_id)
and paf.location_id = nvl(c_location_id,paf.location_id)
and paf.person_id = nvl(c_person_id,paf.person_id)
and paf.business_group_id + 0 = c_business_group_id
and ppa.action_type = 'B'
and act.action_status = 'C'
and act.payroll_action_id = ppa.payroll_action_id
and paf.assignment_id = act.assignment_id
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and paf.person_id between stperson and endperson
and ppa.payroll_id = ppf.payroll_id -- Bug 3372732
and ppa.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppf.payroll_id >= 0
--added for bug 6854964
AND ((nvl(act.run_type_id, ppa.run_type_id) is null and
act.source_action_id is null)
or (nvl(act.run_type_id, ppa.run_type_id) is not null and
act.source_action_id is not null )
or (ppa.action_type = 'V' and ppa.run_type_id is null and
act.run_type_id is not null and
act.source_action_id is null))
--end of addition
ORDER BY act.assignment_action_id;
select act.assignment_action_id,
act.assignment_id,
act.tax_unit_id
from pay_assignment_actions act,
per_assignments_f paf,
pay_payroll_actions ppa, /* pre-payments and reversals payroll action id */
pay_payrolls_f ppf -- Bug 3372732
where ppa.payroll_id = nvl(c_payroll_id, ppa.payroll_id)
and ppa.consolidation_set_id +0 =
nvl(c_consolidation_set_id, ppa.consolidation_set_id)
and ppa.effective_date between c_start_date and c_effective_date
and act.tax_unit_id = nvl(c_tax_unit_id,act.tax_unit_id)
and paf.organization_id = nvl(c_organization_id,
paf.organization_id)
and paf.location_id = nvl(c_location_id, paf.location_id)
and paf.person_id = nvl(c_person_id, paf.person_id)
and paf.business_group_id +0 = c_business_group_id
and ppa.action_type = 'I'
and act.action_status = 'C'
and act.payroll_action_id = ppa.payroll_action_id
and paf.assignment_id = act.assignment_id
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and paf.person_id between stperson and endperson
and ppa.payroll_id = ppf.payroll_id -- Bug 3372732
and ppa.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppf.payroll_id >= 0
--added for bug 6854964
AND ((nvl(act.run_type_id, ppa.run_type_id) is null and
act.source_action_id is null)
or (nvl(act.run_type_id, ppa.run_type_id) is not null and
act.source_action_id is not null )
or (ppa.action_type = 'V' and ppa.run_type_id is null and
act.run_type_id is not null and
act.source_action_id is null))
--end of addition
ORDER BY act.assignment_action_id;
select act.assignment_action_id,
act.assignment_id,
act.tax_unit_id
from pay_assignment_actions act,
per_assignments_f paf,
pay_payroll_actions ppa, /* pre-payments and reversals payroll action id */
pay_payrolls_f ppf -- Bug 3372732
where ppa.payroll_id =
nvl(c_payroll_id, ppa.payroll_id)
and ppa.consolidation_set_id +0 =
nvl(c_consolidation_set_id, ppa.consolidation_set_id)
and ppa.effective_date between c_start_date and c_effective_date
and act.tax_unit_id = nvl(c_tax_unit_id,act.tax_unit_id)
and paf.organization_id = nvl(c_organization_id, paf.organization_id)
and paf.location_id = nvl(c_location_id, paf.location_id)
and paf.person_id = nvl(c_person_id, paf.person_id)
and paf.business_group_id +0 = c_business_group_id
and ppa.action_type in ('B','I')
and act.action_status = 'C'
and act.payroll_action_id = ppa.payroll_action_id
and paf.assignment_id = act.assignment_id
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and paf.person_id between stperson and endperson
and ppa.payroll_id = ppf.payroll_id -- Bug 3372732
and ppa.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppf.payroll_id >= 0
--added for bug 6854964
AND ((nvl(act.run_type_id, ppa.run_type_id) is null and
act.source_action_id is null)
or (nvl(act.run_type_id, ppa.run_type_id) is not null and
act.source_action_id is not null )
or (ppa.action_type = 'V' and ppa.run_type_id is null and
act.run_type_id is not null and
act.source_action_id is null))
--end of addition
ORDER BY act.assignment_action_id;
select act.assignment_action_id,
act.assignment_id,
act.tax_unit_id
from pay_assignment_actions act,
per_assignments_f paf,
pay_payroll_actions ppa, /* pre-payments and reversals */
/* payroll action id */
pay_payrolls_f ppf -- Bug 3372732
where ppa.payroll_id = nvl(c_payroll_id, ppa.payroll_id)
and ppa.consolidation_set_id +0 = nvl(c_consolidation_set_id,
ppa.consolidation_set_id)
and ppa.effective_date between c_start_date and c_effective_date
and act.tax_unit_id = nvl(c_tax_unit_id,act.tax_unit_id)
and paf.organization_id= nvl(c_organization_id, paf.organization_id)
and paf.location_id = nvl(c_location_id, paf.location_id)
and paf.person_id = nvl(c_person_id, paf.person_id)
and paf.business_group_id +0 = c_business_group_id
and ppa.action_type = 'R'
and act.action_status = 'C'
and act.payroll_action_id = ppa.payroll_action_id
and paf.assignment_id = act.assignment_id
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and paf.person_id between stperson and endperson
and ppa.payroll_id = ppf.payroll_id -- Bug3372732
and ppa.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppf.payroll_id >= 0
--added for bug 6854964
AND ((nvl(act.run_type_id, ppa.run_type_id) is null and
act.source_action_id is null)
or (nvl(act.run_type_id, ppa.run_type_id) is not null and
act.source_action_id is not null )
or (ppa.action_type = 'V' and ppa.run_type_id is null and
act.run_type_id is not null and
act.source_action_id is null))
--end of addition
ORDER BY act.assignment_action_id;
select act.assignment_action_id,
act.assignment_id,
act.tax_unit_id
from pay_assignment_actions act,
per_assignments_f paf,
pay_payroll_actions ppa, /* pre-payments and */
/* reversals payroll action id */
pay_payrolls_f ppf -- Bug 3372732
where ppa.payroll_id = nvl(c_payroll_id, ppa.payroll_id)
and ppa.consolidation_set_id +0 = nvl(c_consolidation_set_id,
ppa.consolidation_set_id)
and ppa.effective_date between c_start_date and c_effective_date
and act.tax_unit_id = nvl(c_tax_unit_id,act.tax_unit_id)
and paf.organization_id = nvl(c_organization_id,
paf.organization_id)
and paf.location_id = nvl(c_location_id, paf.location_id)
and paf.person_id = nvl(c_person_id, paf.person_id)
and paf.business_group_id +0 = c_business_group_id
and ppa.action_type = 'Q'
and act.action_status = 'C'
and act.payroll_action_id = ppa.payroll_action_id
and paf.assignment_id = act.assignment_id
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and paf.person_id between stperson and endperson
and ppf.payroll_id = ppa.payroll_id -- Bug 3372732
and ppa.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppf.payroll_id >= 0
--added for bug 6854964
AND ((nvl(act.run_type_id, ppa.run_type_id) is null and
act.source_action_id is null)
or (nvl(act.run_type_id, ppa.run_type_id) is not null and
act.source_action_id is not null )
or (ppa.action_type = 'V' and ppa.run_type_id is null and
act.run_type_id is not null and
act.source_action_id is null))
--end of addition
ORDER BY act.assignment_action_id;
select act.assignment_action_id,
act.assignment_id,
act.tax_unit_id
from pay_assignment_actions act,
per_assignments_f paf,
pay_payroll_actions ppa, /* pre-payments and */
/* reversals payroll action id */
pay_payrolls_f ppf
where ppa.payroll_id = nvl(c_payroll_id, ppa.payroll_id)
and ppa.consolidation_set_id +0 = nvl(c_consolidation_set_id,
ppa.consolidation_set_id)
and ppa.effective_date between c_start_date and c_effective_date
and act.tax_unit_id = nvl(c_tax_unit_id,act.tax_unit_id)
and paf.organization_id = nvl(c_organization_id ,paf.organization_id)
and paf.location_id = nvl(c_location_id, paf.location_id)
and paf.person_id = nvl(c_person_id, paf.person_id)
and paf.business_group_id +0 = c_business_group_id
and ppa.action_type in ('R','Q')
and act.action_status = 'C'
and act.payroll_action_id = ppa.payroll_action_id
and paf.assignment_id = act.assignment_id
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and paf.person_id between stperson and endperson
and ppa.payroll_id = ppf.payroll_id -- Bug 3372732
and ppa.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppf.payroll_id >= 0
--added for bug 6854964
AND ((nvl(act.run_type_id, ppa.run_type_id) is null and
act.source_action_id is null)
or (nvl(act.run_type_id, ppa.run_type_id) is not null and
act.source_action_id is not null )
or (ppa.action_type = 'V' and ppa.run_type_id is null and
act.run_type_id is not null and
act.source_action_id is null))
--end of addition
ORDER BY act.assignment_action_id;
select act.assignment_action_id,
act.assignment_id,
act.tax_unit_id
from pay_assignment_actions act,
per_assignments_f paf,
pay_payroll_actions ppa, /* pre-payments and */
/* reversals payroll action id */
pay_payrolls_f ppf -- Bug 3372732
where ppa.payroll_id =
nvl(c_payroll_id, ppa.payroll_id)
and ppa.consolidation_set_id +0 = nvl(c_consolidation_set_id,
ppa.consolidation_set_id)
and ppa.effective_date between c_start_date and c_effective_date
and act.tax_unit_id = nvl(c_tax_unit_id,act.tax_unit_id)
and paf.organization_id = nvl(c_organization_id, paf.organization_id)
and paf.location_id = nvl(c_location_id, paf.location_id)
and paf.person_id = nvl(c_person_id, paf.person_id)
and paf.business_group_id +0 = c_business_group_id
and ppa.action_type = 'V'
and act.action_status = 'C'
and act.payroll_action_id = ppa.payroll_action_id
and paf.assignment_id = act.assignment_id
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and paf.person_id between stperson and endperson
and ppa.payroll_id = ppf.payroll_id -- Bug 3372732
and ppa.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppf.payroll_id >= 0
--added for bug 6854964
AND ((nvl(act.run_type_id, ppa.run_type_id) is null and
act.source_action_id is null)
or (nvl(act.run_type_id, ppa.run_type_id) is not null and
act.source_action_id is not null )
or (ppa.action_type = 'V' and ppa.run_type_id is null and
act.run_type_id is not null and
act.source_action_id is null))
--end of addition
ORDER BY act.assignment_action_id;
select act.assignment_action_id,
act.assignment_id,
act.tax_unit_id
from pay_assignment_actions act,
per_assignments_f paf,
pay_payroll_actions ppa, /* pre-payments and */
/* reversals payroll action id */
pay_payrolls_f ppf
where ppa.payroll_id = nvl(c_payroll_id, ppa.payroll_id)
and ppa.consolidation_set_id +0 = nvl(c_consolidation_set_id ,ppa.consolidation_set_id)
and ppa.effective_date between c_start_date and c_effective_date
and act.tax_unit_id = nvl(c_tax_unit_id,act.tax_unit_id)
and paf.organization_id = nvl(c_organization_id,
paf.organization_id)
and paf.location_id = nvl(c_location_id, paf.location_id)
and paf.person_id = nvl(c_person_id, paf.person_id)
and paf.business_group_id +0 = c_business_group_id
and ppa.action_type in ('B','D','I','R','Q','V')
and act.action_status = 'C'
and act.payroll_action_id = ppa.payroll_action_id
and paf.assignment_id = act.assignment_id
and ppa.effective_date between paf.effective_start_date
and paf.effective_end_date
and paf.person_id between stperson and endperson
and ppa.payroll_id = ppf.payroll_id -- Bug 3372732
and ppa.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and ppf.payroll_id >= 0
--added for bug 6854964
AND ((nvl(act.run_type_id, ppa.run_type_id) is null and
act.source_action_id is null)
or (nvl(act.run_type_id, ppa.run_type_id) is not null and
act.source_action_id is not null )
or (ppa.action_type = 'V' and ppa.run_type_id is null and
act.run_type_id is not null and
act.source_action_id is null))
--end of addition
ORDER BY act.assignment_action_id;
select legislative_parameters into leg_param
from pay_payroll_actions
where payroll_action_id = pactid;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select legislative_parameters
from pay_payroll_actions
where payroll_action_id = pactid;
sqlstr := 'select paa.rowid
/* we need the row id of the assignment actions
that are created by PYUGEN */
from hr_all_organization_units hou, /* Assignment Org */
hr_all_organization_units hou1,/* Tax Unit */
hr_locations_all loc,
per_all_people_f ppf,
per_all_assignments_f paf,
pay_assignment_actions paa, /* PYUGEN assignment action */
pay_payroll_actions ppa, /* PYUGEN payroll action id */
pay_assignment_actions paa1, /*For Sorting */
pay_action_interlocks pai,
pay_payroll_actions ppa1 /*For Sorting */
where ppa.payroll_action_id = :payactid
and paa.payroll_action_id = ppa.payroll_action_id
and paf.assignment_id = paa.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 <= ppa.effective_date
and paf1.effective_end_date >= ppa.start_date
)
and hou1.organization_id = paa.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 ppa.effective_date between ppf.effective_start_date
and ppf.effective_end_date
AND ppa1.effective_date BETWEEN ppa.start_date and ppa.effective_Date
AND ppa1.action_status = ''C''
AND ppa1.payroll_action_id = paa1.payroll_action_id
and paa1.action_status = ''C''
AND paa1.assignment_id = paa.assignment_id
and pai.locking_action_id = paa.assignment_action_id
and pai.locked_action_id = paa1.assignment_action_id
and ppa.business_group_id = ppa1.business_group_id';
for update of paa.assignment_id';