[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
cursor cur_emp_cma, added another delete
stmt at the start of the procedure to
remove records from per_ca_ee_report_lines
table,added an additional check before
inserting temporary employees as well as
Promoted employee details.
05-Jul-2005 ssmukher 115.2 Modified the cursor c_promo.
Modified the effective end date condition for
per_all_people_f and per_all_assignments_f
table
05-Jul-2005 ssmukher 115.3 Modified the code so that if the designated
code is not specified for an employee then
record is not inserted into the exception
report
07-Jul-2005 ssmukher Added check for secure user.
08-Jul-2005 ssmukher 115.4 Fix for Bug 4480102
13-Jul-2005 ssmukher 115.5 Bug 4488375 :Modified the length of Last name
and First name in cur_emp_categ_person cursor
to 20 and 15 respectively.
14-Jul-2005 ssmukher 115.6 Bug 4490792 : Modified the cursor c_total_salary
Bug 4493278 fix is also included in it.
20-Jul-2005 ssmukher 115.7 4501549 Modified the cursor cur_emp_categ_naic_asg and
cur_emp_categ_asg to include the maximum effective
start date check.
27-Jul-2005 ssmukher 115.8 4500929 Modified the cursor c_total_salary.
******************************************************************************/
/********* Procedure to create the employee.txt file ***************/
/**************** Start of Procedure ******************************/
v_person_type_temp person_type_tab;
select job_id,
job_information7
from per_jobs,
hr_lookups
where lookup_type = 'EEOG'
and upper(ltrim(rtrim(lookup_code)))
=upper(ltrim(rtrim(job_information1)))
and upper(ltrim(rtrim(job_information_category))) = 'CA'
and business_group_id = p_business_group_id;
select person_type_id
from per_person_types
where upper(ltrim(rtrim(system_person_type)))='EMP'
and business_group_id = p_business_group_id;
select hl.lookup_code
from hr_lookups hl,
hr_soft_coding_keyflex hsck
where hsck.soft_coding_keyflex_id = p_keyflex_id and
hl.lookup_type = 'NAIC' and
(
(
hsck.segment6 is not null and
hl.lookup_code = hsck.segment6
)
OR
(
hsck.segment6 is null and
exists
( select 1
from hr_organization_information hoi
where hoi.org_information8 is not null and
hl.lookup_code=hoi.org_information8 and
hsck.segment1 = to_char(hoi.organization_id) and
hoi.org_information_context = 'Canada Employer Identification'
)
)
);
select
distinct(ppf.person_id) l_person_id,
ppf.employee_number emp_no,
substr(ppf.first_name,1,15) first_name,
substr(ppf.last_name,1,20) last_name,
ppf.sex gender,
nvl(ppf.per_information5,'N') desg_abor,
nvl(ppf.per_information6,'N') desg_vminor,
nvl(ppf.per_information7,'N') desg_disab,
trunc(ppf.original_date_of_hire) date_of_hire
from
per_all_people_f ppf
where person_type_exists(ppf.person_type_id) is not null and
ppf.effective_end_date >= p_start_date and
ppf.business_group_id = p_business_group_id
order by l_person_id,emp_no;
select paf.assignment_id asg_id,
job_exists(paf.job_id) noc_code,
decode (substr(NVL(paf.employment_category,'-1'),1,2),'FR','01','PR','02','PT','03','FT','03','-1','-1','04') employment_category,
paf.location_id loc_id,
paf.soft_coding_keyflex_id flex_id,
paf.effective_start_date st_dt
from
per_all_assignments_f paf
where paf.person_id = p_person_id and
paf.effective_end_date >= p_start_date and
paf.business_group_id = p_business_group_id and
paf.primary_flag = 'Y' and
job_exists(paf.job_id) is not null and
paf.effective_start_date = (select max(effective_start_date)
from per_all_assignments_f paf1
where paf1.business_group_id = p_business_group_id
and paf1.assignment_id = paf.assignment_id
and paf1.person_id = p_person_id);
select paf.assignment_id asg_id,
job_exists(paf.job_id) noc_code,
decode (substr(NVL(paf.employment_category,'-1'),1,2),'FR','01','PR','02','PT','03','FT','03','-1','-1','04') employment_category,
paf.location_id loc_id,
paf.effective_start_date st_dt
from
per_all_assignments_f paf,
hr_soft_coding_keyflex hsck,
hr_lookups hl
where paf.person_id = p_person_id and
paf.business_group_id = p_business_group_id and
paf.effective_end_date >= p_start_date and
paf.primary_flag = 'Y' and
job_exists(paf.job_id) is not null and
hl.lookup_type = 'NAIC' and
paf.effective_start_date = (select max(effective_start_date)
from per_all_assignments_f paf1
where paf1.business_group_id = p_business_group_id
and paf1.assignment_id = paf.assignment_id
and paf1.person_id = p_person_id) and
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
(
(
hsck.segment6 is not null and
hl.lookup_code = hsck.segment6 and
hl.lookup_code = p_naic_code
)
OR
(
hsck.segment6 is null and
exists
( select 1
from hr_organization_information hoi
where hoi.org_information8 is not null and
hl.lookup_code=hoi.org_information8 and
hsck.segment1 = to_char(hoi.organization_id) and
hoi.org_information8 = p_naic_code and
hoi.org_information_context = 'Canada Employer Identification'
)
)
);
select trunc(ppos.actual_termination_date) term_date,
trunc(ppos.projected_termination_date) end_dt
from
per_all_people_f ppf,
per_periods_of_service ppos
where person_type_exists(ppf.person_type_id) is not null and
ppos.actual_termination_date between
ppf.effective_start_date and
ppf.effective_end_date and
ppf.business_group_id=p_business_group_id and
ppf.person_id = p_person_id and
ppf.person_id=ppos.person_id and
ppos.actual_termination_date is not null and
ppos.actual_termination_date >= p_start_date and
ppos.actual_termination_date <= p_end_date;
select max(paf.effective_start_date) start_dt,
trunc(ppos.actual_termination_date) term_date,
trunc(ppos.projected_termination_date) end_dt
from
per_all_people_f ppf,
per_all_assignments_f paf,
per_periods_of_service ppos
where person_type_exists(ppf.person_type_id) is not null and
paf.person_id = ppf.person_id and
ppos.actual_termination_date between
ppf.effective_start_date and
ppf.effective_end_date and
ppos.actual_termination_date between
paf.effective_start_date and
paf.effective_end_date and
paf.effective_end_date = ppos.actual_termination_date and
ppf.business_group_id=p_business_group_id and
ppf.person_id = p_person_id and
ppf.person_id=ppos.person_id and
ppos.actual_termination_date is not null and
ppos.actual_termination_date >= p_start_date and
ppos.actual_termination_date <= p_end_date
group by trunc(ppos.actual_termination_date) ,
trunc(ppos.projected_termination_date);
select sum(trunc(to_number(pppv.proposed_salary)) * ppb.pay_annualization_factor) salary
from per_pay_bases ppb,
per_all_assignments_f paf,
per_all_people_f ppf,
per_pay_proposals_v2 pppv,
per_assignment_status_types past,
hr_lookups hrl
where paf.pay_basis_id = ppb.pay_basis_id and
paf.person_id=ppf.person_id and
past.assignment_status_type_id = paf.assignment_status_type_id and
pppv.assignment_id = paf.assignment_id and
hrl.lookup_type ='PER_ASS_SYS_STATUS' and
hrl.lookup_code = past.per_system_status and
hrl.lookup_code = 'ACTIVE_ASSIGN' and
ppf.current_emp_or_apl_flag = 'Y' and
ppb.business_group_id = p_business_group_id and
paf.person_id = p_person_id and
paf.assignment_id=pppv.assignment_id and
paf.effective_start_date = ( select max(effective_start_date)
from per_all_assignments_f paf1,
per_assignment_status_types past1,
hr_lookups hrl1
where paf1.business_group_id = p_business_group_id
and paf1.assignment_id = pppv.assignment_id
and paf1.person_id = p_person_id
and past1.assignment_status_type_id = paf1.assignment_status_type_id
and hrl1.lookup_type ='PER_ASS_SYS_STATUS'
and hrl1.lookup_code = past1.per_system_status
and hrl1.lookup_code = 'ACTIVE_ASSIGN'
) and
ppf.effective_start_date = ( select max(effective_start_date)
from per_all_people_f ppf1
where ppf1.person_id = ppf.person_id
and ppf1.business_group_id = p_business_group_id
and ppf1.current_emp_or_apl_flag = 'Y'
) and
pppv.change_date = (select max(pppv2.change_date)
from per_pay_proposals_v2 pppv2
where pppv2.assignment_id = paf.assignment_id
and pppv2.change_date <= v_year_end
)
group by ppf.person_id;
select decode( hl1.lookup_code,'ON','10',
'QC','11',
'NS','12',
'NB','13',
'MB','14',
'BC','15',
'PE','16',
'SK','17',
'AB','18',
'NF','19',
'YT','20',
'NT','21',
'NU','22',
'98')
from hr_locations_all hloc,
hr_lookups hl1
where hloc.location_id = p_location_code
and hl1.lookup_code = hloc.region_1
and hl1.lookup_type = 'CA_PROVINCE';
SELECT decode(ltrim(rtrim(hl1.lookup_code)),
'CALGARY' ,'01', 'EDMONTON','02','HALIFAX','03',
'MONTREAL','04', 'REGINA','05', 'TORONTO','06',
'VANCOUVER','07', 'WINNEPEG','08' ,'ST JOHNS','50',
'SAINT JOHN','52','CHICOUTIMI','53',
'QUEBEC','54','SHERBROOKE','55','TROIS RIVIERES','56',
'KINGSTON NEW','57','OTTOWA HULL','58','OSHAWA','59',
'HAMILTON','61','ST CATHARINES NIAGARA','62',
'KITCHENER','63','LONDON','64','WINDSOR','65','SUDBURY','66',
'THUNDER BAY','67','SASKATOON','70','ABBOTSFORD NEW','72',
'VICTORIA','74','ALBERTA LESS CMA','85',
'BRITISH COLUMBIA LESS CMA','86','MANITOBA LESS CMA','87',
'NEW BRUNSWICK LESS CMA','88','NOVA SCOTIA LESS CMA','89',
'NORTH WEST TERRITORIES','90','NEWFOUNDLAND LESS CMA','91',
'NUNAVUT','92','ONTORIA LESS CMA','93','PRINCE EDWARD ISLAND','94',
'QUEBEC LESS CMA','95','SASKATCHEWAN LESS CMA','96','YUKON TERRITORY','97')
FROM hr_lookups hl1,
hr_locations_all hloc
WHERE hloc.location_id = p_location_code
AND hloc.region_2 = hl1.lookup_code
AND hl1.lookup_type = 'CA_CMA';
select distinct ppp.assignment_id asg_id,
TRUNC(ppp.change_date) promo_date
from per_pay_proposals_v2 ppp,
per_all_people_f ppf,
per_all_assignments_f paf
where ppf.person_id = paf.person_id and
ppf.person_id = p_person_id and
paf.assignment_id = ppp.assignment_id and
ppf.effective_end_date > p_start_date and
paf.effective_end_date > p_start_date and
ppp.business_group_id = p_business_group_id and
ppp.proposal_reason = 'PROM' and
ppp.change_date BETWEEN p_start_date AND p_end_date and
ppp.approved = 'Y';
select 'Y'
from per_people_f
where person_id = p_person_id;
delete
from per_ca_ee_report_lines
where request_id in (10,20,30);
insert into
per_ca_ee_report_lines (REQUEST_ID ,
LINE_NUMBER,
CONTEXT,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
SEGMENT6)
select 30,
l_excep_cnt,
'EXCEP',
l_emp_no,
decode(l_province,'-99','Province Code'),
decode(l_naics_no ,'-999','NAIC Code'),
decode (l_cma_code,'-99','CMA Code'),
decode (l_employment_category,'-1','Employment Category'),
decode(l_salary,0,'Salary')
from dual;
/* Inserting the Temporary Employee record into the table */
/* Checking firstly if the employee details are correct */
/* Added by ssmukher in v115.1 */
if l_flag = 'Y' then
if l_employment_category = '03' then
l_tmp_cnt := 0;
insert into
per_ca_ee_report_lines (REQUEST_ID ,
LINE_NUMBER,
CONTEXT,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4)
values (10,
l_tmp_cnt,
'TMP',
l_emp_no,
to_char(l_start_date,'YYYY/MM/DD'),
to_char(l_end_dt,'YYYY/MM/DD'),
to_char(l_term_date,'YYYY/MM/DD'));
/* Inserting the Promotion details in the table */
begin
l_promo_cnt := 0;
insert into
per_ca_ee_report_lines (REQUEST_ID ,
LINE_NUMBER,
CONTEXT,
SEGMENT1,
SEGMENT2)
values (20,
l_promo_cnt,
'PROMO',
l_emp_no,
to_char(l_promo_date,'YYYY/MM/DD'));
insert into
per_ca_ee_report_lines (REQUEST_ID ,
LINE_NUMBER,
CONTEXT,
SEGMENT1,
SEGMENT2)
values (20,
l_promo_cnt,
'PROMO',
l_emp_no,
to_char(table_date(i),'YYYY/MM/DD'));
select to_number(segment1) emp_no,
segment2 promo_dt
from per_ca_ee_report_lines pcer
where pcer.context = 'PROMO' and
pcer.request_id = 20
order by emp_no,promo_dt;
/* Deleteing the promotion records from per_ca_ee_report_lines table */
delete from
per_ca_ee_report_lines
where request_id = 20 and context = 'PROMO';
select to_number(segment1) emp_no,
segment2 start_dt,
segment3 end_dt,
segment4 term_dt
from per_ca_ee_report_lines pcer
where pcer.context = 'TMP' and
pcer.request_id = 10
order by emp_no;
delete from
per_ca_ee_report_lines
where request_id = 10 and context ='TMP';
select to_number(segment1) emp_no,
segment2 prov,
segment3 naic,
segment4 cma,
segment5 emp_catg,
segment6 sal
from per_ca_ee_report_lines pcer
where pcer.request_id = 30 and
pcer.context = 'EXCEP'
order by emp_no;
/* Delete the Exception employee records from the Temporary tables */
delete from
per_ca_ee_report_lines
where request_id = 30 and
context = 'EXCEP';