[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
REM to insert a record into
REM pay_us_rpt_totals
REM Changed promotion procedure
REM ynegoro 28-SEP-04 115.9 3894120 Changed the length of fein
REM in TYPE establishment
REM ynegoro 05-OCT-04 115.10 3886008 Added l_hours_worked is NULL
REM in hire_of_fte procedure
REM ynegoro 14-OCT-04 115.12 3940867 Changed parameters to open
REM assignment_details cursor in
REM find_persons procuedure
REM ynegoro 14-OCT-04 115.13 3940867 Changed c_person_infm cursor
REM comment out max(ppf1.effective_start_date)
REM ynegoro 15-OCT-04 115.14 Added new parameters to
REM hire_or_fte procedure
REM 3941606 Added minority procedure call
REM when applicant's racecode is NULL
REM 3954458 Added c_check_future_termination
REM cursor for FTE count
REM ynegoro 18-OCT-04 115.15 Changed assignment_details
REM ynegoro 19-OCT-04 115.16 3941606 Added c_get_updated_racecode cursor
REM ynegoro 19-OCT-04 115.17 3954458 Changed paremeter from p_period_start
REM to p_eff_start_date to open
REM c_check_future_termination cursor
REM 3878442 Added 'EMP_APL' to include
REM APPLICANT cont in app_fire_count
REM ynegoro 20-OCT-04 115.18 3954458 Changed c_app_term_assignment
REM to pick up terminated employees
REM who are rehired.
REM ynegoro 21-OCT-04 115.19 3963090 Changed promotion procedure to
REM pick up multiple promotions
REM ynegoro 22-OCT-04 115.20 3878442 Defined the following variables
REM as local variables
REM m_app_count
REM f_app_count
REM m_terminate_count
REM f_terminate_count
REM m_hire_count
REM f_hire_count
REM m_fte_count
REM f_fte_count
REM m_promotion_count
REM f_promotion_count
REM ynegoro 03-NOV-04 115.21 3993335 Added p_eff_start_date and
REM p_eff_end_date parameters to
REM promotion procedure
REM ynegoro 15-JUN-05 115.22 4434130 Updated c_app_term_assignments,
REM c_persons,c_person_infm cursors
REM to pick up rehired employees in
REM different job group
REM ynegoro 20-JUN-05 115.23 4445250 Updated c_persons cursor to
REM pick up correct terminated
REM employees
REM rpasumar 15-JUL-2007 115.24 5982927 Modified the report so that
REM it won't consider the changes to eeo1 job categories
REM and US ethnic group lookup changes.
REM ========================================================================
--------------------Global variables-------------------------------------------
To store the establishment information and fein number this table is
declared globally. */
TYPE establishment IS RECORD(
entity_id per_gen_hierarchy_nodes.entity_id%TYPE,
hierarchy_node_id per_gen_hierarchy_nodes.hierarchy_node_id%TYPE,
fein varchar2(100), -- BUG3894120
location_name varchar2(1000),
est_flag varchar2(1));
SELECT entity_id,parent_hierarchy_node_id
FROM per_gen_hierarchy_nodes
WHERE hierarchy_version_id = l_hierarchy_version_id
AND (
(
entity_id = nvl(l_est_id,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 =l_hierarchy_version_id
and entity_id = nvl(l_est_id,entity_id)
and node_type = 'EST')
AND node_type = 'LOC'
)
);
SELECT paf.assignment_id,
paf.person_id,
pj.job_information1 job_category,
paf.assignment_status_type_id
,'APPLICANT'
FROM per_assignments_f paf,
per_jobs pj,
per_assignment_status_types past,
fnd_common_lookups fcl
WHERE paf.assignment_status_type_id = past.assignment_status_type_id
AND --((
paf.assignment_type = 'A'
and paf.effective_end_date >= l_period_start
and paf.effective_start_date <= l_period_end
--)
-- or (paf.assignment_type = 'E'
-- and paf.primary_flag = 'Y'
-- and paf.effective_start_date between
-- l_period_start and l_period_end)
-- )
AND paf.effective_start_Date = (select max(paf1.effective_Start_date)
from per_assignments_f paf1
where paf1.assignment_id = paf.assignment_id
and paf1.effective_start_Date <=l_period_end
and paf1.assignment_status_type_id =
paf.assignment_Status_type_id)
--AND past.per_system_status in ('ACTIVE_APL','ACCEPTED','TERM_ASSIGN','SUSP_ASSIGN')
AND past.per_system_status in ('ACTIVE_APL','ACCEPTED','SUSP_ASSIGN')
AND pj.job_id = paf.job_id
AND pj.job_information1 = fcl.lookup_code
AND fcl.lookup_type = 'US_EEO1_JOB_CATEGORIES'
AND fcl.lookup_code <> '10'
AND paf.location_id = l_location_id
UNION
/* Retrieve terminated employees only */
SELECT paf.assignment_id
,paf.person_id
,pj.job_information1 job_category
,paf.assignment_status_type_id
,ppt.system_person_type
FROM per_people_f ppf
,per_assignments_f paf
,per_periods_of_service pps
,per_person_types ppt
,per_jobs pj
,fnd_common_lookups fcl
WHERE pps.person_id = paf.person_id
and pps.actual_termination_date is not null
and pps.actual_termination_date
between l_period_start and l_period_end
/* BUG4434130
and ppf.effective_start_date =
(select max(ppf2.effective_start_date)
from per_people_f ppf2
where ppf2.person_id = ppf.person_id
and ppf2.current_employee_flag is null
)
*/
and pps.date_start = ppf.effective_start_date
and pps.actual_termination_date between
ppf.effective_start_date and ppf.effective_end_date
-- End of BUG4434130
and ppf.person_id = paf.person_id
and pps.actual_termination_date between
paf.effective_start_date and paf.effective_end_date
and ppf.person_type_id = ppt.person_type_id
And paf.assignment_type = 'E'
And paf.primary_flag = 'Y'
AND pj.job_id = paf.job_id
AND pj.job_information1 = fcl.lookup_code
AND fcl.lookup_type = 'US_EEO1_JOB_CATEGORIES'
AND fcl.lookup_code <> '10'
AND paf.location_id = l_location_id
order by 2;
select ppf.per_information1 race_code
,ppf.sex sex
,ppt.system_person_type person_type
,pj.job_information1 job_category -- BUG4434130
from per_people_f ppf
,per_person_types ppt
,per_assignments_f paf -- BUG4434130
,per_jobs pj -- BUG4434130
where ppf.effective_start_date <= l_period_end
and ppf.effective_end_date >= l_period_start
and ppf.person_type_id = ppt.person_type_id
-- and ppt.system_person_type not in ( 'EMP_APL','EMP')
and ppt.system_person_type <> 'EMP' -- BUG3878442
and ppf.effective_start_Date
= (select max(effective_Start_date)
from per_people_f ppf1
where ppf1.person_type_id = ppf.person_type_id
and ppf1.effective_start_Date <=l_period_end
and ppf1.person_id =ppf.person_id
)
and ppt.system_person_type in ('APL','APL_EX_APL','EX_APL','EX_EMP_APL','EMP_APL') -- BUG3878442
and ppf.person_id = l_person_id
-- BUG4434130
and paf.person_id = ppf.person_id
and paf.effective_start_date = ppf.effective_start_date
and paf.job_id = pj.job_id
-- End of BUG4434130
-- BUG4434130
/* Retrieve terminated employees */
UNION
select ppf.per_information1 race_code
,ppf.sex sex
,'EX_EMP' person_type
,pj.job_information1 job_category
from per_people_f ppf
,per_periods_of_service pps
,per_assignments_f paf
,per_jobs pj
where ppf.person_id = l_person_id
and pps.person_id = ppf.person_id
and pps.actual_termination_date is not null
and pps.actual_termination_date between
l_period_start and l_period_end
and paf.person_id = ppf.person_id
and pps.date_start = ppf.effective_start_date -- BUG4445250
and paf.effective_start_date = ppf.effective_start_date
and paf.job_id = pj.job_id;
select 'x'
from per_assignment_status_types
where per_system_status = 'ACCEPTED'
and assignment_status_type_id = l_asgn_status_id;
select 'x'
from per_assignments_f paf,
per_assignment_Status_types past
where paf.assignment_id = l_asgn_id
and paf.assignment_type = 'A'
and paf.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status = 'ACTIVE_APL'
and paf.effective_start_date >= l_period_start
and paf.effective_end_date <= l_period_end;
select ppf.per_information1 race_code,
ppt.system_person_type person_type
from per_people_f ppf
,per_person_types ppt
,per_periods_of_service pps
where ppf.effective_start_date <= l_period_end
and ppf.effective_end_date >= l_period_start
and ppf.per_information1 is not NULL
and ppf.person_type_id = ppt.person_type_id
and ppt.system_person_type = 'EMP'
and pps.person_id = ppf.person_id
and ppf.effective_start_date = pps.date_start
and ppf.person_id = l_person_id;
/* For the selected location assignments are picked up.*/
FOR app_term in c_app_term_assignments( est.entity_id
,p_period_end,p_period_start)
LOOP
hr_utility.trace('Inside Loop2.assignment id = '|| to_char(app_term.assignment_id));
hr_utility.trace('After calling minority before inserting');
p_insert(
l_est_id,
p_seq_num,
est.entity_id,
l_est_name,
l_est_fein,
app_term.assignment_id,
app_term.person_id,
per.job_category, -- app_term.job_category, BUG4434130
per.race_code,
per.person_type,
m_app_count,
f_app_count,
m_hire_count,
f_hire_count,
m_terminate_count,
f_terminate_count,
m_promotion_count,
f_promotion_count,
m_fte_count,
f_fte_count,
monetary_comp,
tenure_years,
tenure_months,
minority_code,
ethnic_group_code,
l_est_flag,
fte_flag);
SELECT ppa.legislative_parameters,ppa.start_date,ppa.effective_date
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id =l_pact_id;
SELECT pdb.defined_balance_id
FROM pay_defined_balances pdb,
pay_balance_dimensions pbd,
pay_balance_types pbt
WHERE pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbd.database_item_suffix = '_ASG_YTD'
AND pbd.legislation_code = 'US'
AND pdb.balance_type_id = pbt.balance_type_id
AND pbt.balance_name = 'EO Regular Salary Year to Date'
AND pbt.legislation_code = 'US'
AND pdb.legislation_code = 'US';
SELECT entity_id,hierarchy_node_id
FROM per_gen_hierarchy_nodes
WHERE node_type = 'EST'
AND entity_id =nvl(l_entity_id,entity_id)
AND hierarchy_version_id = l_version_id;
inserted in pay_assignment_Actions for a specific chunk and pactid.
It also picks up the person_id stored in serial_number. */
CURSOR c_fte_asgn(l_pactid pay_payroll_actions.payroll_action_id%type,
l_chunk pay_assignment_actions.chunk_number%type)
IS
SELECT assignment_action_id,
assignment_id,
serial_number,
source_action_id location_id
FROM pay_assignment_actions
WHERE payroll_action_id = l_pactid
AND chunk_number = l_chunk;
this was selected during action creation. */
CURSOR c_max_asact_id(l_locking_asact_id pay_assignment_actions.assignment_action_id%type)
IS
SELECT locked_action_id
FROM pay_action_interlocks
WHERE locking_action_id = l_locking_asact_id;
/* This cursor selects the person information like race, job_category only for employees
and EMP_APL. */
CURSOR c_person_infm( l_person_id per_assignments_f.person_id%type,
l_period_start date,
l_period_end date)
IS
SELECT ppf.sex,
ppf.person_id,
ppt.system_person_type person_type,
ppf.effective_start_Date eff_Start,
ppf.effective_end_date eff_end,
ppf.per_information1 race,
ppf.person_type_id,pps.date_start service_start
FROM per_people_f ppf,
per_person_types ppt,
per_periods_of_service pps
WHERE ( ( ppt.system_person_type = 'EMP'
/* BUG4434130
and ppf.effective_start_date
= (select max(ppf1.effective_start_date)
from per_people_f ppf1
where ppf1.person_type_id = ppf.person_type_id
and ppf1.person_id = ppf.person_id
and ppf1.effective_start_date<=l_period_end
)
*/
and ppf.effective_start_Date <=l_period_end
and ppf.effective_end_date >= l_period_start
and pps.date_start = ppf.effective_start_date -- BUG4434130
)
OR
( ppt.system_person_type = 'EMP_APL'
and ppf.effective_start_date
= (select max(ppf2.effective_Start_date)
from per_people_f ppf2
where ppf2.person_id = ppf.person_id
and ppf2.person_type_id = ppf.person_type_id
and ppf2.effective_start_Date <=l_period_end
and ppf2.effective_end_date >= l_period_start
)
)
)
and ppf.person_id =l_person_id
and ppt.person_type_id = ppf.person_type_id
and pps.person_id = ppf.person_id;
SELECT distinct paf.person_id,
pj.job_information1 job,
paf.assignment_type
,paf.assignment_id
,paf.effective_start_date
,paf.effective_end_date
FROM per_assignments_f paf,
per_jobs pj,
fnd_common_lookups fcl
WHERE paf.person_id =l_person_id
AND pj.job_id = paf.job_id
AND ( ( paf.assignment_type = 'A'
and l_person_type = 'EMP_APL'
)
or (paf.assignment_type = 'E'
and l_person_type = 'EMP'
)
)
AND paf.effective_start_Date <= l_period_end
AND paf.effective_end_Date >= l_period_start
AND paf.location_id = l_location_id
AND pj.job_information1=fcl.lookup_code
AND fcl.lookup_code <> '10'
AND fcl.lookup_type = 'US_EEO1_JOB_CATEGORIES';
job_race_insert(est_id.entity_id,
l_seq_num);
update pay_us_rpt_totals
set gre_name = p_fein,
location_name = p_location_name
where session_id = est_id.entity_id
and business_group_id = l_seq_num;
hr_utility.trace('After inserting the values.entity_id = '|| est_id.entity_id);
PROCEDURE p_insert (
p_entity_id in number,
p_seq_num in number,
p_location_id in number,
p_location_name in varchar2,
fein in varchar2 ,
p_assignment_id in number ,
p_person_id in number ,
p_job_category in varchar2,
p_race_code in varchar2 ,
p_person_type in varchar2,
p_m_app_count in number ,
p_f_app_count in number ,
p_m_hire_count in number ,
p_f_hire_count in number ,
p_m_terminate_count in number ,
p_f_terminate_count in number ,
p_m_promotion_count in number ,
p_f_promotion_count in number ,
p_m_fte_count in number ,
p_f_fte_count in number ,
p_monetary_comp in number ,
p_tenure_years in number ,
p_tenure_months in number ,
p_minority_code in varchar2,
p_ethnic_group_code in varchar2,
p_est_flag in varchar2,
p_fte_flag in varchar2)
IS
n number;
hr_utility.trace('=============================== p_insert==================================');
/*Inserting the records twice if the person is in
either once of the following race:
Hispanic or Latino (White race only) Hispanic or Latino (all other races) */
IF p_ethnic_group_code in ('7','8') THEN
n:=2;
INSERT INTO pay_us_rpt_totals
( session_id,
business_group_id,
location_id,
location_name,
gre_name,
tax_unit_id,
organization_id,
attribute1,
attribute2,
attribute3,
value1,
value2,
value3,
value4,
value5,
value6,
value7,
value8,
value9,
value10,
value11,
value12,
value13,
value14,
attribute4,
attribute5,
attribute6)
Values
(p_entity_id,
p_seq_num,
p_location_id ,
substr(p_location_name,1,80) ,
fein ,
p_assignment_id ,
p_person_id ,
p_job_category ,
p_race_code ,
p_person_type ,
nvl(p_m_app_count,0) ,
nvl(p_f_app_count,0) ,
nvl(p_m_hire_count,0) ,
nvl(p_f_hire_count,0) ,
nvl(p_m_terminate_count,0) ,
nvl(p_f_terminate_count,0) ,
nvl(p_m_promotion_count,0) ,
nvl(p_f_promotion_count,0) ,
nvl(p_m_fte_count,0) ,
nvl(p_f_fte_count,0) ,
nvl(p_monetary_comp,0) ,
p_tenure_years ,
p_tenure_months ,
p_minority_code ,
l_ethnic_group_code ,
p_est_flag,
p_fte_flag
);
hr_utility.trace('After Inserting. Resetting the counts. ');
hr_utility.trace('=============================== end p_insert==================================');
end p_insert;
/*This cursor selects the parent_hierarchy_node_id for the selected
entity_id. */
CURSOR c_loc_hierarchy_id (l_location_id per_gen_hierarchy_nodes.entity_id%type,
l_hierarchy_version_id per_gen_hierarchy_nodes.hierarchy_version_id%type)
IS
SELECT parent_hierarchy_node_id
FROM per_gen_hierarchy_nodes
WHERE entity_id = l_location_id
AND hierarchy_version_id = l_hierarchy_version_id;
calendar year which is selected here.*/
CURSOR asact_id(c_assignment_id per_assignments_f.assignment_id%type,
c_period_end date)
IS
SELECT to_number(substr(max(lpad(paa.action_sequence,15,'0')||
paa.assignment_action_id),16))
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE paa.assignment_id = c_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.effective_date <= c_period_end
AND ppa.action_type in ('R', 'Q', 'I');
select 1
from per_people_f ppf
,per_periods_of_service pps
where ppf.person_id = p_person_id
and ppf.effective_start_date < p_end_date
and ppf.effective_end_date > p_start_date
and pps.person_id = ppf.person_id
and pps.actual_termination_date between
p_start_date and p_end_date
and ppf.effective_start_date =
(select max(ppf2.effective_start_date)
from per_people_f ppf2
where ppf2.person_id = ppf.person_id
and ppf2.effective_start_date < p_end_date
and ppf2.effective_end_date > p_start_date
);
CURSOR c_get_updated_racecode( l_person_id per_assignments_f.person_id%type,
l_period_start date,
l_period_end date)
IS
SELECT ppt.system_person_type person_type,
ppf.effective_start_Date eff_Start,
ppf.effective_end_date eff_end,
ppf.per_information1 race
FROM per_people_f ppf,
per_person_types ppt,
per_periods_of_service pps
WHERE ( ( ppt.system_person_type = 'EMP'
and ppf.effective_start_date
= (select max(effective_start_date)
from per_people_f
where person_type_id = ppf.person_type_id
and person_id = ppf.person_id
and effective_start_date<=l_period_end
)
)
OR
( ppt.system_person_type = 'EMP_APL'
and ppf.effective_start_date = (select max(effective_Start_date)
from per_people_f
where person_id = ppf.person_id
and person_type_id = ppf.person_type_id
and effective_start_Date <=l_period_end
and effective_end_date >= l_period_start
)
)
)
and ppf.person_id =l_person_id
and ppt.person_type_id = ppf.person_type_id
and pps.person_id = ppf.person_id;
open c_get_updated_racecode(p_person_id -- BUG3941606
,p_period_start
,p_period_end
);
fetch c_get_updated_racecode into l_person_type
,l_effective_start_date
,l_effective_end_date
,l_race;
if c_get_updated_racecode%NOTFOUND then
hr_utility.set_location(l_package||':p_assignment_id = '||p_assignment_id,165);
close c_get_updated_racecode;
hr_utility.trace('hire_or_fte. before calling p_insert procedure');
p_insert(
l_est_id,
p_seq_num,
p_location_id,
l_est_name,
l_est_fein,
p_assignment_id,
p_person_id,
p_job,
p_race,
p_person_type,
m_app_count,
f_app_count,
m_hire_count,
f_hire_count,
m_terminate_count,
f_terminate_count,
m_promotion_count,
f_promotion_count,
m_fte_count,
f_fte_count,
monetary_comp,
tenure_years,
tenure_months,
minority_code,
ethnic_group_code,
l_est_flag,
fte_flag);
select hlei.lei_information6 fein
from hr_location_extra_info hlei
where hlei.location_id = c_entity_id
and hlei.information_type = 'Establishment Information';
SELECT lei_information1 rpt_name
from hr_location_extra_info
where location_id = c_entity_id
and information_type = 'EEO-1 Specific Information';
select hoi.org_information3 fein
from hr_organization_information hoi,
per_gen_hierarchy_nodes pghn
where hoi.organization_id = pghn.entity_id
and pghn.node_type = 'PAR'
and pghn.hierarchy_version_id = c_version_id
and hoi.org_information_context = 'VETS_EEO_Dup';
select rtrim(address_line_1)||' '||
rtrim(address_line_2)||' '||
rtrim(address_line_3)||' '||
town_or_city||','||
country||'-'||
postal_code
from hr_locations
where location_id = c_entity_id;
select decode(lookup_code,'6','American Indian or Alaskan Native',
'4','Asian',
'5','Native Hawaiian or Other Pacific Islander',
'2','Black or African American',
'8','Black or African American',
'1','White',
'9','Hispanic or Latino (White race only)',
'3','Hispanic or Latino (all other races)',
'10','Hispanic or Latino (all other races)',null)
from fnd_common_lookups
where lookup_code = c_race_code
and lookup_type = 'US_ETHNIC_GROUP';
select decode(l_ethnic_category,'American Indian or Alaskan Native','1',
'Asian','2',
'Native Hawaiian or Other Pacific Islander','3',
'Black or African American','4',
'White','5',
'Hispanic or Latino (White race only)','7',
'Hispanic or Latino (all other races)','8',
null,'0')
into ethnic_group_code
from dual ;
select 'Y'
from per_assignment_extra_info
where aei_information_category = 'Promotion'
and to_date(aei_information1,'dd-mm-yyyy') between
p_period_start and p_period_end
and assignment_id = l_assignment_id;
select business_group_id
,person_id
,effective_start_date -- BUG3963090
,effective_end_date -- BUG3963090
from per_all_assignments_f
where assignment_id = p_assignment_id
and effective_start_date <= p_eff_end_date
and effective_end_date >= p_eff_start_date;
procedure job_race_insert(p_entity_id in number,
p_seq_num in number)
is
CURSOR eeo1_job_code
IS
SELECT lookup_code
FROM fnd_common_lookups
WHERE lookup_type = 'US_EEO1_JOB_CATEGORIES'
AND lookup_code <> '10';
hr_utility.trace('=============================== job_race_insert==================================');
INSERT INTO pay_us_rpt_totals
(session_id,
business_group_id,
attribute1,
attribute4,
attribute6)
Values
(p_entity_id,
p_seq_num,
job_code.lookup_code,
i,
'Y');
INSERT INTO pay_us_rpt_totals
(session_id,
business_group_id,
attribute1,
value14,
attribute6)
Values
(p_entity_id,
p_seq_num,
job_code.lookup_code,
i,
'Y');
hr_utility.trace('===============================END job_race_insert==================================');
END job_race_insert;