[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
04-NOV-04 ynegoro 115.6 Deleted previous change for BUG3958260
3958260 is not a bug.
16-JUL-07 rpasumar 115.7 To report a person whose ethnic origin is blank.
*/
--------------------------- range_cursor ---------------------------------
PROCEDURE range_cursor (pactid in number,
sqlstr out nocopy varchar2) is
l_payroll_id number;
'select distinct paf.person_id
from pay_payroll_actions ppa, -- pyugen
per_gen_hierarchy_nodes pghn,
per_assignments_f paf,
per_assignment_status_types past,
per_jobs pj,
fnd_common_lookups fcl
where ppa.payroll_action_id = :pactid
AND pghn.hierarchy_version_id = pay_eosy_ac_pkg.get_parameter
(''HI_VER_ID'',ppa.legislative_parameters)
AND (
(
entity_id = nvl(pay_eosy_ac_pkg.get_parameter
(''EST_ID'',ppa.legislative_parameters),pghn.entity_id)
AND node_type =''EST''
)
OR
(
parent_hierarchy_node_id in(SELECT hierarchy_node_id
FROM per_gen_hierarchy_nodes
WHERE hierarchy_version_id =pay_eosy_ac_pkg.get_parameter
(''HI_VER_ID'',ppa.legislative_parameters)
AND entity_id = nvl(pay_eosy_ac_pkg.get_parameter
(''EST_ID'',ppa.legislative_parameters),entity_id)
AND node_type = ''EST'')
AND node_type = ''LOC''
)
)
and paf.location_id = pghn.entity_id
and paf.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_Status = ''ACTIVE_ASSIGN''
and paf.effective_start_Date = (select max(effective_Start_date)
from per_assignments_f paf1
where paf1.assignment_id = paf.assignment_id
and paf1.effective_start_Date <=ppa.start_Date
-- and paf1.effective_end_date >=ppa.start_date
and paf1.effective_end_date >=trunc(ppa.start_date,''Y'') -- BUG3886008
and paf1.assignment_status_type_id =
paf.assignment_Status_type_id
and paf1.primary_flag = ''Y''
-- and paf1.location_id = paf.location_id -- BUG3958260
)
and paf.assignment_type = ''E''
and paf.primary_flag=''Y''
AND paf.job_id = pj.job_id
AND pj.job_information1= fcl.lookup_code
AND fcl.lookup_type = ''US_EEO1_JOB_CATEGORIES''
/*AND exists
(SELECT ''x'' from per_people_f
WHERE person_id = paf.person_id
AND per_information1 is not null)*/
order by paf.person_id';
SELECT paa.assignment_action_id,
paf.assignment_id,
paf.person_id,
paa.tax_unit_id,
paf.location_id
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_assignments_f paf,
per_jobs pj,
per_gen_hierarchy_nodes pghn,
-- per_assignment_status_types past, -- BUG3886008
fnd_common_lookups fcl
WHERE ppa.effective_date between l_start_date and l_end_date
AND ppa.action_type in ('R','Q','I')
AND ppa.action_status = 'C'
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'C'
AND paa.action_sequence IN (
SELECT MAX(paa2.action_sequence)
FROM pay_action_classifications pac,
pay_payroll_actions ppa2,
pay_assignment_actions paa2,
per_assignments_f paf1
WHERE paf1.person_id = paf.person_id
AND paa2.assignment_id = paf1.assignment_id
AND paf1.primary_flag = 'Y' -- BUG3941460
AND paa2.tax_unit_id = paa.tax_unit_id
AND ppa2.payroll_action_id = paa2.payroll_action_id
AND ppa2.action_type = pac.action_type
AND pac.classification_name = 'SEQUENCED'
AND paa2.action_status = 'C' -- BUG3886008
AND ppa2.effective_date <= l_end_Date -- BUG3964366
--AND ppa2.effective_date between paf1.effective_start_date
-- and paf1.effective_end_date -- BUG3958260
--AND paf1.location_id = paf.location_id -- BUG3958260
)
AND paf.assignment_id = paa.assignment_id
AND paf.person_id between stperson and endperson
AND paf.location_id = pghn.entity_id
ANd pghn.hierarchy_version_id = l_version_id
AND (
(
pghn.entity_id = nvl(l_est_id,pghn.entity_id)
AND pghn.node_type ='EST'
)
OR
(
pghn.parent_hierarchy_node_id
in(select pghn2.hierarchy_node_id
from per_gen_hierarchy_nodes pghn2
where pghn2.hierarchy_version_id =l_version_id
and pghn2.entity_id = nvl(l_est_id,pghn2.entity_id)
and pghn2.node_type = 'EST')
AND pghn.node_type = 'LOC'
)
)
-- AND paf.assignment_status_type_id = past.assignment_status_type_id
-- AND past.per_system_Status = 'ACTIVE_ASSIGN' -- BUG3886008
AND paf.effective_start_Date = (select max(effective_Start_date)
from per_assignments_f paf1
where paf1.assignment_id = paf.assignment_id
and paf1.effective_start_Date <=l_end_date
and paf1.effective_end_date >= l_start_date
-- and paf1.assignment_status_type_id =
-- paf.assignment_Status_type_id
and paf1.primary_flag = 'Y'
and paf1.location_id = paf.location_id --BUG3958260
)
-- AND ppa.effective_date between paf.effective_start_Date and paf.effective_end_Date -- BUG3886008
AND paf.assignment_type = 'E'
AND paf.primary_flag='Y'
AND paf.job_id = pj.job_id
AND pj.job_information1= fcl.lookup_code
AND fcl.lookup_type = 'US_EEO1_JOB_CATEGORIES';
(SELECT 'x' from per_people_f ppf2
WHERE ppf2.person_id = paf.person_id
AND ppf2.per_information1 is not null)*/
CURSOR c_report_parameters(pactid number)
IS
SELECT start_date,
effective_date,
pay_eosy_ac_pkg.get_parameter('HI_VER_ID',legislative_parameters),
pay_eosy_ac_pkg.get_parameter('EST_ID',legislative_parameters)
FROM pay_payroll_actions
WHERE payroll_action_id=pactid;
SELECT pay_assignment_actions_s.nextval
INTO lockingactid
FROM dual;
UPDATE pay_assignment_actions
SET serial_number = l_person_id,
source_action_id = l_location_id
WHERE assignment_action_id = lockingactid;
hr_utility.trace('After inserting into pay_assignment_actions, before pay_action_interlock');
'select paa.rowid
from pay_assignment_actions paa /* PYUGEN assignment action */
where paa.payroll_action_id = :payactid
for update of paa.assignment_id';