[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(*)
INTO v_promotions_count
FROM per_pay_proposals_v2
WHERE assignment_id = p_assignment_id
AND approved = 'Y'
AND change_date BETWEEN p_start_date
AND p_end_date
AND proposal_reason = 'PROM';
SELECT DISTINCT paf.person_id,
paf.soft_coding_keyflex_id,
hsck.segment1,
hsck.segment6
FROM per_assignments_f paf,
per_people_f ppf,
per_person_types ppt,
per_jobs pj,
hr_soft_coding_keyflex hsck
WHERE
p_date_all_emp BETWEEN
paf.effective_start_date AND
paf.effective_end_date
AND paf.business_group_id = p_business_group_id
AND paf.primary_flag = 'Y'
AND paf.job_id + 0 = pj.job_id
AND pj.job_information_category = 'CA'
AND paf.person_id =ppf.person_id
AND p_date_all_emp BETWEEN
ppf.effective_start_date AND
ppf.effective_end_date
AND ppf.person_type_id =ppt.person_type_id
AND ppt.system_person_type='EMP'
AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND EXISTS
(SELECT 'X'
FROM per_pay_proposals_v2 pppv
WHERE pppv.business_group_id = p_business_group_id
AND pppv.assignment_id = paf.assignment_id
AND pppv.approved = 'Y'
AND pppv.change_date <= v_year_end)
AND EXISTS
(SELECT 1
FROM hr_lookups hl
WHERE pj.job_information1 = hl.lookup_code
AND hl.lookup_type = 'EEOG' );
SELECT hoi.organization_id,
hoi.org_information8
FROM hr_organization_information hoi,
hr_organization_units hou
WHERE hou.business_group_id = p_business_group_id
AND hou.organization_id = hoi.organization_id
AND hoi.org_information_context = 'Canada Employer Identification'
AND hoi.org_information8 IS NOT NULL;
select
hou.name gre_name
from
hr_organization_information hoi,
hr_organization_units hou
where
hou.business_group_id = p_business_group_id and
hou.organization_id = hoi.organization_id and
hoi.org_information_context = 'Canada Employer Identification' and
hoi.org_information8 is null;
p_gre_name.delete;
select
houv.name,houv.address_line_1,
houv.address_line_2,houv.address_line_3,
houv.town_or_city, houv.region_1,
houv.postal_code,houv.country, houv.organization_id ,
hoi.org_information1
from
hr_organization_units_v houv,
hr_organization_information hoi
where
houv.organization_id=hoi.organization_id and
upper(ltrim(rtrim(hoi.org_information_context)))
= 'BUSINESS GROUP INFORMATION'
and houv.business_group_id = p_business_group_id;
select
org_information1 ceo_name,
org_information3 ceo_position,
org_information2 contact_name,
org_information4 contact_position,
org_information5 contact_phone
from
hr_organization_information
where
upper(ltrim(rtrim(org_information_context)))
= 'EMPLOYMENT EQUITY INFORMATION' and
organization_id = v_organization_id;
select
employment_category employment_category,
count(distinct l_person_id) count_category
from (
select
distinct(paf.person_id) l_person_id,
substr(paf.employment_category,1,2) employment_category
from
per_people_f ppf,
per_assignments_f paf,
per_person_types ppt,
hr_soft_coding_keyflex hsck,
per_jobs pj,
hr_lookups hl
where
ppf.person_type_id = ppt.person_type_id and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
ppf.effective_start_date and
ppf.effective_end_date and
ppf.person_id = paf.person_id and
decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
paf.effective_start_date and
paf.effective_end_date and
paf.business_group_id = p_business_group_id and
paf.primary_flag = 'Y' and
paf.employment_category is not null and
substr(paf.employment_category,1,2) in ('FR','PR','PT') and
paf.job_id = pj.job_id and
pj.job_information_category = 'CA' and
pj.job_information1 = hl.lookup_code and
hl.lookup_type = 'EEOG' and
(
(p_naic_code is not null and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = p_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = p_naic_code)
)
)
) -- End of p_naic_code is not null
OR
(p_naic_code is null and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC')
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC')
)
)
) -- End of p_naic_code is null
) and
exists
(
select 'X'
from per_pay_proposals_v2 pppv
where pppv.business_group_id = p_business_group_id and
pppv.assignment_id = paf.assignment_id and
pppv.approved = 'Y' and
pppv.change_date <=
decode(substr(paf.employment_category,1,2),
'PT',p_date_tmp_emp,l_year_end)
) -- End of exists
union all
select
distinct(paf.person_id) l_person_id,
'FR' employment_category
from
per_people_f ppf,
per_assignments_f paf,
per_person_types ppt,
hr_soft_coding_keyflex hsck,
per_jobs pj,
hr_lookups hl
where
ppf.person_type_id = ppt.person_type_id and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
l_year_end between
ppf.effective_start_date and
ppf.effective_end_date and
ppf.person_id = paf.person_id and
l_year_end between
paf.effective_start_date and
paf.effective_end_date and
paf.business_group_id = p_business_group_id and
paf.primary_flag = 'Y' and
paf.job_id = pj.job_id and
pj.job_information_category = 'CA' and
pj.job_information1 = hl.lookup_code and
hl.lookup_type = 'EEOG' and
(paf.employment_category is null OR
substr(paf.employment_category,1,2) not in ('FR','PR','PT')
) and
(
(p_naic_code is not null and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = p_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = p_naic_code)
)
)
) -- End of p_naic_code is not null
OR
(p_naic_code is null and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC')
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC')
)
)
) -- End od p_naic_code is null
) and
exists
(
select 'X'
from per_pay_proposals_v2 pppv
where pppv.business_group_id = p_business_group_id and
pppv.assignment_id = paf.assignment_id and
pppv.approved = 'Y' and
pppv.change_date <=
decode(substr(paf.employment_category,1,2),
'PT',p_date_tmp_emp,l_year_end)
) -- End of exists
) -- End of from
group by employment_category
order by employment_category;
select
count(distinct paf.person_id) count_province_cma,
hl1.meaning meaning
from
per_assignments_f paf,
hr_locations hl,
hr_lookups hl1,
per_people_f ppf ,
per_person_types ppt,
per_jobs pj,
hr_lookups hl2
where
upper(ltrim(rtrim(hl1.lookup_type)))=decode(pc,1,'CA_PROVINCE',
2,'CA_CMA')and
upper(ltrim(rtrim(hl1.lookup_code)))
= decode(pc,1,upper(ltrim(rtrim(hl.region_1))),
2,upper(ltrim(rtrim(hl.region_2)))) and
hl.location_id=paf.location_id and
p_date_all_emp between
paf.effective_start_date and
paf.effective_end_date and
paf.business_group_id=p_business_group_id and
paf.primary_flag = 'Y' and
paf.job_id = pj.job_id and
pj.job_information_category = 'CA' and
pj.job_information1 = hl2.lookup_code and
hl2.lookup_type = 'EEOG' and
paf.person_id=ppf.person_id and
p_date_all_emp between
ppf.effective_start_date and
ppf.effective_end_date and
ppf.person_type_id=ppt.person_type_id and
ppt.system_person_type='EMP' and
exists
(
select 'X'
from per_pay_proposals_v2 pppv
where pppv.business_group_id = p_business_group_id and
pppv.assignment_id = paf.assignment_id and
pppv.approved = 'Y' and
pppv.change_date <= l_year_end
) -- End of exists
group by hl1.meaning;
select
ltrim(rtrim(hl.meaning)) meaning
from
hr_lookups hl
where
hl.lookup_type='CA_CMA' and
upper(ltrim(rtrim(hl.meaning))) in
('CALGARY','EDMONTON','HALIFAX','MONTREAL','REGINA','TORONTO',
'VANCOUVER','WINNIPEG')
minus
select
ltrim(rtrim(segment2))
from
per_ca_ee_report_lines
where
request_id=p_request_id and
ltrim(rtrim(context))='FORM13' and
ltrim(rtrim(segment1))='CMA';
select
ltrim(rtrim(hl.meaning)) meaning
from
hr_lookups hl
where
hl.lookup_type='CA_PROVINCE'
minus
select
ltrim(rtrim(segment2))
from
per_ca_ee_report_lines
where
request_id=p_request_id and
ltrim(rtrim(context))='FORM14' and
ltrim(rtrim(segment1))='PROVINCE';
select
count(distinct paf.person_id) count_naic_person,
hl.lookup_code lcode
from
hr_lookups hl,
hr_soft_coding_keyflex hsck ,
hr_organization_information hoi,
per_assignments_f paf,
per_people_f ppf,
per_person_types ppt,
per_jobs pj,
hr_lookups hl1
where
(
(
p_naic_code is not null and
hl.lookup_type='NAIC' and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = p_naic_code and
hl.lookup_code = hsck.segment6
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hoi.org_information8 is not null and
hl.lookup_code=hoi.org_information8 and
hoi.org_information8 = p_naic_code and
hsck.segment1 = to_char(hoi.organization_id) and
hoi.org_information_context = 'Canada Employer Identification'
)
)
)
OR
(
p_naic_code is null and
hl.lookup_type='NAIC' and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hl.lookup_code = hsck.segment6
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
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'
)
)
)
) and
p_date_all_emp between
paf.effective_start_date and
paf.effective_end_date and
paf.business_group_id = p_business_group_id and
paf.primary_flag = 'Y' and
paf.job_id = pj.job_id and
pj.job_information_category = 'CA' and
pj.job_information1 = hl1.lookup_code and
hl1.lookup_type = 'EEOG' and
paf.person_id=ppf.person_id and
p_date_all_emp between
ppf.effective_start_date and
ppf.effective_end_date and
ppf.person_type_id=ppt.person_type_id and
ppt.system_person_type='EMP' and
exists
(
select 'X'
from per_pay_proposals_v2 pppv
where pppv.business_group_id = p_business_group_id and
pppv.assignment_id = paf.assignment_id and
pppv.approved = 'Y' and
pppv.change_date <= l_year_end
) -- End of exists
group by hl.lookup_code
order by 1 desc;
select
meaning
from
hr_lookups
where
lookup_type='NAIC' and
lookup_code=lc;
select
pcli.lookup_code
from
pay_ca_legislation_info pcli
where
pcli.lookup_type = p_lookup_type;
select hou.organization_id
from
hr_organization_units hou,
hr_organization_information hoi
where
hoi.organization_id = hou.organization_id and
hou.business_group_id = p_business_group_id and
hoi.org_information_context = 'Canada Employer Identification' and
hoi.org_information8 is not null and
hoi.org_information8 = p_naic;
insert into per_ca_ee_report_lines
( request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment16) values
( p_request_id,
per_ca_ee_extract_pkg.k,
'FORM11',
v_name,
v_address_line_1,
v_address_line_2,
v_address_line_3,
v_town_or_city,
v_region_1,
v_postal_code,
v_country,
v_short_name);
update per_ca_ee_report_lines set
segment9 = i.ceo_name, -- CEO name
segment10 = i.ceo_position, -- and his Position
segment11 = i.contact_name, -- EE relevant personnel
segment12 = i.contact_position, -- and his Position
segment17 = i.contact_phone -- and Phone Number
where request_id = p_request_id and
context = 'FORM11' and
line_number = per_ca_ee_extract_pkg.k;
insert into per_ca_ee_report_lines
(
request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5
)
values
(
p_request_id,
per_ca_ee_extract_pkg.k,
'FORM12',
'NAIC',
v_meaning,
-- i.count_naic_person,
-- i.lcode,
v_sorted_naic_count(i),
v_sorted_naic(i),
v_max_naic
);
insert into per_ca_ee_report_lines
(
request_id,
line_number,
context,
segment1,
segment2,
segment3
)
values
(
p_request_id,
per_ca_ee_extract_pkg.k,
'FORM13',
'NAIC',
-- i.lcode,
v_sorted_naic(i),
gre_id.organization_id
);
update per_ca_ee_report_lines set
segment13 = i.count_category
where request_id = p_request_id and
--line_number = per_ca_ee_extract_pkg.k and
context = 'FORM11';
update per_ca_ee_report_lines set
segment14 = i.count_category
where request_id = p_request_id and
--line_number = per_ca_ee_extract_pkg.k and
context = 'FORM11';
update per_ca_ee_report_lines set
segment15 = i.count_category
where request_id = p_request_id and
--line_number = per_ca_ee_extract_pkg.k and
context = 'FORM11';
insert into per_ca_ee_report_lines
(request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4) values
(p_request_id,
per_ca_ee_extract_pkg.k,
decode(i,1,'FORM14',2,'FORM13'),
decode(i,1,'PROVINCE',2,'CMA'),
l.meaning,
l.count_province_cma,
v_print);
insert into per_ca_ee_report_lines
(request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4) values
(p_request_id,
per_ca_ee_extract_pkg.k,
'FORM13',
'CMA',
i.meaning,
0,
'N');
insert into per_ca_ee_report_lines
(request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4) values
(p_request_id,
per_ca_ee_extract_pkg.k,
'FORM14',
'PROVINCE',
i.meaning,
0,
'N');
select lookup_code
from pay_ca_legislation_info
where lookup_type = p_lookup_type;
select
pert.segment3 tot_number_emp,
pert.segment4 naic_code,
pert.segment5 max_naic_flag
from
per_ca_ee_report_lines pert
where
pert.request_id = p_request_id and
--(pert.segment5 = 'Y' OR
-- to_number(pert.segment3) >= to_number(v_leg_info)) and
pert.context = 'FORM12' ;
cursor cur_min_max is select
max(max_salary) max_salary,
min(min_salary) min_salary,
meaning meaning,
employment_category employment_category
from
(
select
trunc(to_number(pppv.proposed_salary)) * ppb.pay_annualization_factor
max_salary,
trunc(to_number(pppv.proposed_salary)) * ppb.pay_annualization_factor
min_salary,
hl.meaning meaning,
substr(paf.employment_category,1,2) employment_category
from
hr_lookups hl,
per_jobs pj,
per_pay_proposals_v2 pppv,
per_people_f ppf,
per_assignments_f paf,
per_person_types ppt,
hr_soft_coding_keyflex hsck,
per_pay_bases ppb
where
hl.lookup_type='EEOG' and
hl.lookup_code=pj.job_information1 and
pj.job_information_category='CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
paf.effective_start_date and
paf.effective_end_date and
paf.pay_basis_id = ppb.pay_basis_id and
ppb.business_group_id = p_business_group_id and
paf.person_id=ppf.person_id and
paf.assignment_id=pppv.assignment_id 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 <=
decode(substr(paf.employment_category,1,2),
'PT',p_date_tmp_emp,l_year_end)
) and
ppf.person_type_id=ppt.person_type_id and
decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
ppf.effective_start_date and
ppf.effective_end_date and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
ppf.business_group_id=p_business_group_id and
paf.employment_category is not null and
paf.employment_category in ('FR','PR','PT') and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code OR
hsck.segment6 in ( select segment4
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM12' and
to_number(segment3) <= to_number(v_leg_info) and
v_max_naic_flag = 'Y')
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code OR
segment2 in
( select segment4
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM12' and
to_number(segment3)
<= to_number(v_leg_info) and
v_max_naic_flag = 'Y')
)
)
)
union all
select
trunc(to_number(pppv.proposed_salary)) * ppb.pay_annualization_factor
max_salary,
trunc(to_number(pppv.proposed_salary)) * ppb.pay_annualization_factor
min_salary,
hl.meaning meaning,
'FR' employment_category
from
hr_lookups hl,
per_jobs pj,
per_pay_proposals_v2 pppv,
per_people_f ppf,
per_assignments_f paf,
per_person_types ppt,
hr_soft_coding_keyflex hsck,
per_pay_bases ppb
where
hl.lookup_type='EEOG' and
hl.lookup_code=pj.job_information1 and
pj.job_information_category='CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
l_year_end between
paf.effective_start_date and
paf.effective_end_date and
paf.pay_basis_id = ppb.pay_basis_id and
ppb.business_group_id = p_business_group_id and
paf.person_id=ppf.person_id and
paf.assignment_id=pppv.assignment_id 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 <= l_year_end
) and
ppf.person_type_id=ppt.person_type_id and
l_year_end between
ppf.effective_start_date and
ppf.effective_end_date and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
ppf.business_group_id=p_business_group_id and
( paf.employment_category is null OR
paf.employment_category not in ('FR','PR','PT')
) and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code OR
hsck.segment6 in ( select segment4
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM12' and
to_number(segment3) <= to_number(v_leg_info) and
v_max_naic_flag = 'Y')
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code OR
segment2 in
( select segment4
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM12' and
to_number(segment3)
<= to_number(v_leg_info) and
v_max_naic_flag = 'Y')
)
)
)
)
group by meaning,employment_category
order by meaning,employment_category;
cursor cur_count_total(i_range number)is select
count(distinct paf.person_id) count_total,
ppf.sex
from
hr_lookups hl,
per_jobs pj,
per_assignments_f paf,
per_people_f ppf,
per_pay_proposals_v2 pppv,
per_person_types ppt,
hr_soft_coding_keyflex hsck,
per_pay_bases ppb
where
hl.lookup_type='EEOG' and
upper(ltrim(rtrim(hl.meaning)))=upper(ltrim(rtrim(v_meaning))) and
upper(ltrim(rtrim(hl.lookup_code)))
= upper(ltrim(rtrim(pj.job_information1))) and
upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
decode(substr(NVL(paf.employment_category,'FR'),1,2),
'FR','FR','PR','PR','PT','PT','FR')
= ltrim(rtrim(v_employment_category)) and
decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
paf.effective_start_date and
paf.effective_end_date and
paf.person_id=ppf.person_id and
ppf.person_type_id=ppt.person_type_id and
decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
ppf.effective_start_date and
ppf.effective_end_date and
paf.pay_basis_id = ppb.pay_basis_id and
ppb.business_group_id = p_business_group_id and
paf.person_id=ppf.person_id and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
ppf.business_group_id=p_business_group_id and
paf.assignment_id=pppv.assignment_id 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 <=
decode(substr(paf.employment_category,1,2),
'PT',p_date_tmp_emp,l_year_end)
) and
trunc(to_number(pppv.proposed_salary)) * ppb.pay_annualization_factor >=
decode(i_range,1,v_q1_min,
2,v_q2_min,
3,v_q3_min,
4,v_q4_min) and
trunc(to_number(pppv.proposed_salary)) * ppb.pay_annualization_factor <=
decode(i_range,1,v_q1_max,
2,v_q2_max,
3,v_q3_max,
4,v_q4_max) and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code OR
hsck.segment6 in ( select segment4
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM12' and
to_number(segment3) <= to_number(v_leg_info) and
v_max_naic_flag = 'Y')
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code OR
segment2 in
( select segment4
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM12' and
to_number(segment3)
<= to_number(v_leg_info) and
v_max_naic_flag = 'Y')
)
)
)
group by ppf.sex
order by ppf.sex;
select
count(distinct paf.person_id) count,
ppf.sex
from
hr_lookups hl,
per_jobs pj,
per_assignments_f paf,
per_people_f ppf,
per_pay_proposals_v2 pppv,
per_person_types ppt,
hr_soft_coding_keyflex hsck,
per_pay_bases ppb
where
hl.lookup_type='EEOG' and
upper(ltrim(rtrim(hl.meaning))) = upper(ltrim(rtrim(v_meaning))) and
upper(ltrim(rtrim(hl.lookup_code)))
= upper(ltrim(rtrim(pj.job_information1))) and
upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
paf.pay_basis_id = ppb.pay_basis_id and
decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
paf.effective_start_date and
paf.effective_end_date and
paf.person_id=ppf.person_id and
ppf.person_type_id=ppt.person_type_id and
decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
ppf.effective_start_date and
ppf.effective_end_date and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
ppf.business_group_id=p_business_group_id and
decode(desig,1,per_information5,
2,per_information6,
3,per_information7)='Y' and
--substr(NVL(paf.employment_category,'FR'),1,2)=v_employment_category and
decode(substr(NVL(paf.employment_category,'FR'),1,2),
'FR','FR','PR','PR','PT','PT','FR') = v_employment_category and
paf.assignment_id=pppv.assignment_id 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 <=
decode(substr(paf.employment_category,1,2),
'PT',p_date_tmp_emp,l_year_end)
) and
trunc(to_number(pppv.proposed_salary)) * ppb.pay_annualization_factor
>= decode(range,1,v_q1_min,
2,v_q2_min,
3,v_q3_min,
4,v_q4_min) and
trunc(to_number(pppv.proposed_salary)) * ppb.pay_annualization_factor
<= decode(range,1,v_q1_max,
2,v_q2_max,
3,v_q3_max,
4,v_q4_max) and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code OR
hsck.segment6 in ( select segment4
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM12' and
to_number(segment3) <= to_number(v_leg_info) and
v_max_naic_flag = 'Y')
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code OR
segment2 in
( select segment4
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM12' and
to_number(segment3)
<= to_number(v_leg_info) and
v_max_naic_flag = 'Y')
)
)
)
group by ppf.sex
order by ppf.sex;
insert into per_ca_ee_report_lines
( request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment21) values
(p_request_id,
per_ca_ee_extract_pkg.k,
'FORM2',
'NATIONAL',
v_meaning,
v_employment_category,
v_min_salary_range_min||'..'||
v_min_salary_range_max||' '||
v_max_salary_range_min||'..'||
v_max_salary_range_max,
to_char(j),
nvl(v_count,0),
decode(v_sex,'F',v_count,0),
decode(v_sex,'M',v_count,0),
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
v_naic_code);
hr_utility.trace('Update Male');
update per_ca_ee_report_lines set
segment6=segment6 + nvl(v_count,0),
segment8=nvl(v_count,0)
where request_id=p_request_id and
line_number=per_ca_ee_extract_pkg.k and
segment1='NATIONAL' and
segment21 = v_naic_code;
hr_utility.trace('Update Female');
update per_ca_ee_report_lines set
segment6=segment6 + nvl(v_count,0),
segment7=nvl(v_count,0)
where request_id=p_request_id and
line_number=per_ca_ee_extract_pkg.k and
segment1='NATIONAL' and
segment21 = v_naic_code;
insert into per_ca_ee_report_lines
( request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment21) values
(p_request_id,
per_ca_ee_extract_pkg.k,
'FORM2',
'NATIONAL',
v_meaning,
v_employment_category,
v_min_salary_range_min||'..'||
v_min_salary_range_max||' '||
v_max_salary_range_min||'..'||
v_max_salary_range_max,
to_char(j),
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
v_naic_code);
update per_ca_ee_report_lines set
segment9=nvl(segment9,0) + nvl(l.count,0),
segment10=nvl(l.count,0)
where
request_id=p_request_id and
context='FORM2' and
segment1='NATIONAL' and
upper(ltrim(rtrim(segment2)))=upper(ltrim(rtrim(v_meaning))) and
upper(ltrim(rtrim(segment3)))
=upper(ltrim(rtrim(v_employment_category))) and
segment5=to_char(j) and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment9=nvl(segment9,0) + nvl(l.count,0),
segment11=nvl(l.count,0)
where
request_id=p_request_id and
context='FORM2' and
segment1='NATIONAL' and
upper(ltrim(rtrim(segment2))) = upper(ltrim(rtrim(v_meaning))) and
upper(ltrim(rtrim(segment3))) =
upper(ltrim(rtrim(v_employment_category))) and
segment5=to_char(j) and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment12=nvl(segment12,0) + nvl(l.count,0),
segment13=nvl(l.count,0)
where
request_id=p_request_id and
context='FORM2' and
segment1='NATIONAL' and
upper(ltrim(rtrim(segment2)))
=upper(ltrim(rtrim(v_meaning))) and
upper(ltrim(rtrim(segment3)))
=upper(ltrim(rtrim(v_employment_category))) and
segment5=to_char(j) and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment12=nvl(segment12,0) + nvl(l.count,0),
segment14=nvl(l.count,0)
where
request_id=p_request_id and
context='FORM2' and
segment1='NATIONAL' and
upper(ltrim(rtrim(segment2)))=upper(ltrim(rtrim(v_meaning))) and
upper(ltrim(rtrim(segment3)))=upper(ltrim(rtrim(v_employment_category))) and
segment5 =to_char(j) and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment15=nvl(segment15,0) + nvl(l.count,0),
segment16=nvl(l.count,0)
where
request_id=p_request_id and
context = 'FORM2' and
segment1 = 'NATIONAL' and
upper(ltrim(rtrim(segment2))) = upper(ltrim(rtrim(v_meaning))) and
upper(ltrim(rtrim(segment3)))
= upper(ltrim(rtrim(v_employment_category))) and
segment5 = to_char(j) and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment15=nvl(segment15,0) + nvl(l.count,0),
segment17=nvl(l.count,0)
where
request_id=p_request_id and
context='FORM2' and
segment1='NATIONAL' and
upper(ltrim(rtrim(segment2))) =
upper(ltrim(rtrim(v_meaning))) and
upper(ltrim(rtrim(segment3)))
= upper(ltrim(rtrim(v_employment_category))) and
segment5=to_char(j) and
segment21 = v_naic_code;
select lookup_code
from pay_ca_legislation_info
where lookup_type = p_lookup_type;
select
pert.segment3 tot_number_emp,
pert.segment4 naic_code,
pert.segment5 max_naic_flag
from
per_ca_ee_report_lines pert
where
pert.request_id = p_request_id and
--(pert.segment5 = 'Y' OR
-- to_number(pert.segment3) >= to_number(v_leg_info)) and
pert.context = 'FORM12' ;
select
max(max_salary) max_salary,
min(min_salary) min_salary,
meaning meaning,
employment_category employment_category,
cma_province cma_province
from
(
select
trunc(to_number(pppv.proposed_salary)) * ppb.pay_annualization_factor
max_salary,
trunc(to_number(pppv.proposed_salary)) * ppb.pay_annualization_factor min_salary,
hl.meaning meaning,
substr(paf.employment_category,1,2) employment_category,
decode(CMA_PROVINCE_COUNT,1,hl1.region_1
,2,hl1.region_2) cma_province
from
hr_lookups hl,
per_jobs pj,
per_pay_proposals_v2 pppv,
per_people_f ppf,
per_assignments_f paf,
hr_locations hl1,
per_person_types ppt,
per_ca_ee_report_lines pert,
hr_lookups hl2,
hr_soft_coding_keyflex hsck,
per_pay_bases ppb
where
hl.lookup_type='EEOG' and
hl.lookup_code=pj.job_information1 and
pj.job_information_category='CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
paf.effective_start_date and
paf.effective_end_date and
paf.employment_category is not null and
paf.employment_category in ('FR','PR','PT') and
paf.pay_basis_id = ppb.pay_basis_id and
ppb.business_group_id = P_BUSINESS_GROUP_ID and
paf.person_id=ppf.person_id
and decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between ppf.effective_start_date and ppf.effective_end_date and
paf.assignment_id=pppv.assignment_id
and pppv.change_date = (select max(pppv2.change_date)
from per_pay_proposals_v2 pppv2
where pppv2.assignment_id = paf.assignment_id
and paf.person_id=ppf.person_id
and pppv2.change_date <=
decode(substr(paf.employment_category,1,2),
'PT',p_date_tmp_emp,l_year_end)
) and
paf.location_id=hl1.location_id and
ppf.person_type_id=ppt.person_type_id and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
ppf.business_group_id=p_business_group_id and
hl2.lookup_type=decode(cma_province_count,1,'CA_PROVINCE'
,2,'CA_CMA') and
hl2.lookup_code=decode(cma_province_count,1,hl1.region_1
,2,hl1.region_2) and
--pert.segment4 = 'Y' and
pert.request_id=p_request_id and
pert.context=decode(cma_province_count,1,'FORM14'
,2,'FORM13') and
pert.segment1=decode(cma_province_count,1,'PROVINCE'
,2,'CMA') and
hl2.lookup_type=decode(cma_province_count,1,'CA_PROVINCE'
,2,'CA_CMA') and
pert.segment2=hl2.meaning and
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
(
(
hsck.segment6 is not null and
(hsck.segment6 = v_naic_code
OR
hsck.segment6 in ( select segment4
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM12' and
to_number(segment3) < to_number(v_leg_info) and
v_max_naic_flag = 'Y')
)
)
OR
(
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
(
segment2 = v_naic_code
OR
segment2 in
( select segment4
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM12' and
to_number(segment3)
< to_number(v_leg_info) and
v_max_naic_flag = 'Y')
)
)
)--pass2
)
union all --pass2
select
trunc(to_number(pppv.proposed_salary)) * ppb.pay_annualization_factor
max_salary,
trunc(to_number(pppv.proposed_salary)) * ppb.pay_annualization_factor
min_salary,
hl.meaning meaning,
'FR' employment_category,
decode(cma_province_count,1,hl1.region_1
,2,hl1.region_2) cma_province
from
hr_lookups hl,
per_jobs pj,
per_pay_proposals_v2 pppv,
per_people_f ppf,
per_assignments_f paf,
hr_locations hl1,
per_person_types ppt,
per_ca_ee_report_lines pert,
hr_lookups hl2,
hr_soft_coding_keyflex hsck,
per_pay_bases ppb
where
hl.lookup_type='EEOG' and
hl.lookup_code=pj.job_information1 and
pj.job_information_category='CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
l_year_end between
paf.effective_start_date and
paf.effective_end_date and
(paf.employment_category is null OR
paf.employment_category not in ('FR','PR','PT')
) and
paf.pay_basis_id = ppb.pay_basis_id and
ppb.business_group_id = p_business_group_id and
paf.person_id=ppf.person_id and
paf.assignment_id=pppv.assignment_id and
pppv.change_date = (select max(pppv2.change_date)
from per_pay_proposals_v2 pppv2
where pppv2.assignment_id = paf.assignment_id
and paf.person_id=ppf.person_id
and pppv2.change_date <= l_year_end
) and
l_year_end between
ppf.effective_start_date and
ppf.effective_end_date and
paf.location_id=hl1.location_id and
ppf.person_type_id=ppt.person_type_id and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
ppf.business_group_id=p_business_group_id and
hl2.lookup_type=decode(cma_province_count,1,'CA_PROVINCE'
,2,'CA_CMA') and
hl2.lookup_code=decode(cma_province_count,1,hl1.region_1
,2,hl1.region_2) and
--pert.segment4 = 'Y' and
pert.request_id=p_request_id and
pert.context=decode(cma_province_count,1,'FORM14'
,2,'FORM13') and
pert.segment1=decode(cma_province_count,1,'PROVINCE'
,2,'CMA') and
hl2.lookup_type=decode(cma_province_count,1,'CA_PROVINCE'
,2,'CA_CMA') and
pert.segment2=hl2.meaning and
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
(
(
hsck.segment6 is not null and
(hsck.segment6 = v_naic_code
OR
hsck.segment6 in ( select segment4
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM12' and
to_number(segment3) < to_number(v_leg_info) and
v_max_naic_flag = 'Y')
)
)
OR
(
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
(
segment2 = v_naic_code
OR
segment2 in
( select segment4
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM12' and
to_number(segment3)
< to_number(v_leg_info) and
v_max_naic_flag = 'Y')
)
)
)
)
)
group by meaning,employment_category,cma_province
order by meaning,employment_category,cma_province;
select
meaning
from
hr_lookups
where
upper(ltrim(rtrim(lookup_type)))=decode(cp,1,'CA_PROVINCE'
,2,'CA_CMA') and
upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_cma_province)));
select
count(distinct paf.person_id) count_total,
ppf.sex --sex
from
hr_lookups hl,
per_jobs pj,
per_assignments_f paf,
per_people_f ppf,
per_pay_proposals_v2 pppv,
hr_locations hl1,
per_person_types ppt,
hr_soft_coding_keyflex hsck,
per_pay_bases ppb
where
hl.lookup_type='EEOG' and
upper(ltrim(rtrim(hl.meaning)))=upper(ltrim(rtrim(v_meaning))) and
upper(ltrim(rtrim(hl.lookup_code)))
=upper(ltrim(rtrim(pj.job_information1))) and
upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
paf.effective_start_date and
paf.effective_end_date and
decode(substr(NVL(paf.employment_category,'FR'),1,2),
'FR','FR','PR','PR','PT','PT','FR')
= ltrim(rtrim(v_employment_category)) and
/* substr(NVL(paf.employment_category,'FR'),1,2) =
ltrim(rtrim(v_employment_category)) and */
paf.person_id=ppf.person_id and
decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
ppf.effective_start_date and
ppf.effective_end_date and
paf.pay_basis_id = ppb.pay_basis_id and
ppb.business_group_id = p_business_group_id and
ppf.person_type_id=ppt.person_type_id and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
ppf.business_group_id=p_business_group_id and
paf.location_id=hl1.location_id and
decode(i_x,1,hl1.region_1,
2,hl1.region_2) = v_cma_province and
paf.assignment_id=pppv.assignment_id 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 <=
decode(substr(paf.employment_category,1,2),
'PT',p_date_tmp_emp,l_year_end)
) and
trunc(to_number(pppv.proposed_salary)) * ppb.pay_annualization_factor
>= decode(i_range,1,v_q1_min,
2,v_q2_min,
3,v_q3_min,
4,v_q4_min) and
trunc(to_number(pppv.proposed_salary)) * ppb.pay_annualization_factor
<= decode(i_range,1,v_q1_max,
2,v_q2_max,
3,v_q3_max,
4,v_q4_max) and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code OR
hsck.segment6 in ( select segment4
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM12' and
to_number(segment3) < to_number(v_leg_info) and
v_max_naic_flag = 'Y')
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code OR
segment2 in
( select segment4
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM12' and
to_number(segment3)
< to_number(v_leg_info) and
v_max_naic_flag = 'Y')
)
)
)
group by ppf.sex
order by ppf.sex;
select
count(distinct paf.person_id) count,
ppf.sex --sex
from
hr_lookups hl,
per_jobs pj,
per_assignments_f paf,
per_people_f ppf,
per_pay_proposals_v2 pppv,
hr_locations hl1,
per_person_types ppt,
hr_soft_coding_keyflex hsck,
per_pay_bases ppb
where
hl.lookup_type='EEOG' and
upper(ltrim(rtrim(hl.meaning)))=upper(ltrim(rtrim(v_meaning))) and
upper(ltrim(rtrim(hl.lookup_code)))
= upper(ltrim(rtrim(pj.job_information1))) and
upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
paf.effective_start_date and
paf.effective_end_date and
paf.pay_basis_id = ppb.pay_basis_id and
ppb.business_group_id = p_business_group_id and
paf.location_id=hl1.location_id and
decode(i_y,1,hl1.region_1,
2,hl1.region_2) = v_cma_province and
paf.person_id=ppf.person_id and
decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
ppf.effective_start_date and
ppf.effective_end_date and
ppf.person_type_id=ppt.person_type_id and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
ppf.business_group_id=p_business_group_id and
decode(desig,1,per_information5,
2,per_information6,
3,per_information7)='Y' and
--substr(NVL(paf.employment_category,'FR'),1,2)=v_employment_category and
decode(substr(NVL(paf.employment_category,'FR'),1,2),
'FR','FR','PR','PR','PT','PT','FR')
= ltrim(rtrim(v_employment_category)) and
paf.assignment_id=pppv.assignment_id 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 <=
decode(substr(paf.employment_category,1,2),
'PT',p_date_tmp_emp,l_year_end)
) and
trunc(to_number(pppv.proposed_salary)) * ppb.pay_annualization_factor
>= decode(range,1,v_q1_min,
2,v_q2_min,
3,v_q3_min,
4,v_q4_min) and
trunc(to_number(pppv.proposed_salary)) * ppb.pay_annualization_factor
<= decode(range,1,v_q1_max,
2,v_q2_max,
3,v_q3_max,
4,v_q4_max) and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code OR
hsck.segment6 in ( select segment4
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM12' and
to_number(segment3) < to_number(v_leg_info) and
v_max_naic_flag = 'Y')
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code OR
segment2 in
( select segment4
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM12' and
to_number(segment3)
< to_number(v_leg_info) and
v_max_naic_flag = 'Y')
)
)
)
group by ppf.sex
order by ppf.sex;
insert into per_ca_ee_report_lines
( request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment21) values
( p_request_id,
per_ca_ee_extract_pkg.k,
'FORM2',
decode(x,1,'PROVINCE'
,2,'CMA'),
v_meaning1,
v_meaning,
v_employment_category,
v_min_salary_range_min||'..'||
v_min_salary_range_max||' '||
v_max_salary_range_min||'..'||
v_max_salary_range_max,
to_char(j),
nvl(v_count,0),
decode(v_sex,'F',v_count,0),
decode(v_sex,'M',v_count,0),
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
v_naic_code) ;
update per_ca_ee_report_lines set
segment7=segment7 + nvl(v_count,0),
segment9=nvl(v_count,0)
where request_id=p_request_id and
line_number=per_ca_ee_extract_pkg.k and
segment1=decode(x,1,'PROVINCE',
2,'CMA') and
segment2=v_meaning1 and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment7=segment7 + nvl(v_count,0),
segment9=nvl(v_count,0)
where request_id=p_request_id and
line_number=per_ca_ee_extract_pkg.k and
segment1 = decode(x,1,'PROVINCE',
2,'CMA') and
segment2 = v_meaning1 and
segment21 = v_naic_code;
insert into per_ca_ee_report_lines
( request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment21) values
( p_request_id,
per_ca_ee_extract_pkg.k,
'FORM2',
decode(x,1,'PROVINCE'
,2,'CMA'),
v_meaning1,
v_meaning,
v_employment_category,
v_min_salary_range_min||'..'||
v_min_salary_range_max||' '||
v_max_salary_range_min||'..'||
v_max_salary_range_max,
to_char(j),
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
v_naic_code);
update per_ca_ee_report_lines set
segment10=nvl(segment10,0) + nvl(l.count,0),
segment11=nvl(l.count,0)
where
request_id=p_request_id and
context='FORM2' and
ltrim(rtrim(segment1))=decode(x,1,'PROVINCE'
,2,'CMA') and
ltrim(rtrim(segment2))=ltrim(rtrim(v_meaning1)) and
upper(ltrim(rtrim(segment3)))=upper(ltrim(rtrim(v_meaning))) and
upper(ltrim(rtrim(segment4)))
=upper(ltrim(rtrim(v_employment_category))) and
segment6=to_char(j) and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment10=nvl(segment10,0) + nvl(l.count,0),
segment12=nvl(l.count,0)
where
request_id=p_request_id and
context='FORM2' and
ltrim(rtrim(segment1))=decode(x,1,'PROVINCE'
,2,'CMA') and
ltrim(rtrim(segment2))=ltrim(rtrim(v_meaning1)) and
upper(ltrim(rtrim(segment3))) =
upper(ltrim(rtrim(v_meaning))) and
upper(ltrim(rtrim(segment4))) =
upper(ltrim(rtrim(v_employment_category))) and
segment6=to_char(j) and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment13=nvl(segment13,0) + nvl(l.count,0),
segment14=nvl(l.count,0)
where
request_id=p_request_id and
context='FORM2' and
ltrim(rtrim(segment1))=decode(x,1,'PROVINCE'
,2,'CMA') and
ltrim(rtrim(segment2))=ltrim(rtrim(v_meaning1)) and
upper(ltrim(rtrim(segment3)))=upper(ltrim(rtrim(v_meaning))) and
upper(ltrim(rtrim(segment4)))=
upper(ltrim(rtrim(v_employment_category))) and
segment6=to_char(j) and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment13=nvl(segment13,0) + nvl(l.count,0),
segment15=nvl(l.count,0)
where
request_id=p_request_id and
context='FORM2' and
ltrim(rtrim(segment1))=decode(x,1,'PROVINCE'
,2,'CMA') and
ltrim(rtrim(segment2))=ltrim(rtrim(v_meaning1)) and
upper(ltrim(rtrim(segment3))) = upper(ltrim(rtrim(v_meaning))) and
upper(ltrim(rtrim(segment4))) =
upper(ltrim(rtrim(v_employment_category))) and
segment6=to_char(j) and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment16=nvl(segment16,0) + nvl(l.count,0),
segment17=nvl(l.count,0)
where
request_id=p_request_id and
context='FORM2' and
ltrim(rtrim(segment1))=decode(x,1,'PROVINCE'
,2,'CMA') and
ltrim(rtrim(segment2))=ltrim(rtrim(v_meaning1)) and
upper(ltrim(rtrim(segment3)))=upper(ltrim(rtrim(v_meaning))) and
upper(ltrim(rtrim(segment4)))=
upper(ltrim(rtrim(v_employment_category))) and
segment6=to_char(j) and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment16=nvl(segment16,0) + nvl(l.count,0),
segment18=nvl(l.count,0)
where
request_id=p_request_id and
context='FORM2' and
ltrim(rtrim(segment1))=decode(x,1,'PROVINCE'
,2,'CMA') and
ltrim(rtrim(segment2))=ltrim(rtrim(v_meaning1)) and
upper(ltrim(rtrim(segment3)))=upper(ltrim(rtrim(v_meaning))) and
upper(ltrim(rtrim(segment4))) =
upper(ltrim(rtrim(v_employment_category))) and
segment6=to_char(j) and
segment21 = v_naic_code;
select
pert.segment4 naic_code
from
per_ca_ee_report_lines pert
where
pert.request_id = p_request_id and
pert.context = 'FORM12';
select
count(distinct count_total) count_total,
employment_category employment_category,
sex sex,
cma_province cma_province
from
(
select
paf.person_id count_total,
substr(paf.employment_category,1,2) employment_category,
ppf.sex sex,
decode(cma_province_count,1,hl1.region_1,2,hl1.region_2) cma_province
from
per_jobs pj,
per_assignments_f paf,
per_people_f ppf,
per_pay_proposals_v2 pppv,
per_person_types ppt,
hr_locations hl1,
per_ca_ee_report_lines pert,
hr_lookups hl2,
hr_soft_coding_keyflex hsck,
per_pay_bases ppb
where
upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
paf.effective_start_date and
paf.effective_end_date and
paf.employment_category is not null and
substr(paf.employment_category,1,2) in ('FR','PR','PT') and
paf.person_id=ppf.person_id and
decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
ppf.effective_start_date and
ppf.effective_end_date and
paf.pay_basis_id = ppb.pay_basis_id and
ppb.business_group_id = p_business_group_id and
ppf.person_type_id=ppt.person_type_id and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
ppf.business_group_id=p_business_group_id and
paf.location_id=hl1.location_id and
hl2.lookup_type=decode(cma_province_count,1,'CA_PROVINCE'
,2,'CA_CMA') and
hl2.lookup_code=decode(cma_province_count,1,hl1.region_1
,2,hl1.region_2) and
--pert.segment4 = 'Y' and
pert.request_id=p_request_id and
pert.context=decode(cma_province_count,1,'FORM14'
,2,'FORM13') and
pert.segment1=decode(cma_province_count,1,'PROVINCE'
,2,'CMA') and
pert.segment2=hl2.meaning and
paf.assignment_id=pppv.assignment_id and
pppv.change_date = (select max(pppv2.change_date)
from per_pay_proposals_v2 pppv2
where pppv2.assignment_id = paf.assignment_id
and pppv2.approved = 'Y'
and pppv2.change_date <=
decode(substr(paf.employment_category,1,2),
'PT',p_date_tmp_emp,l_year_end)
) and
trunc(to_number(pppv.proposed_salary)) * ppb.pay_annualization_factor >=
decode(substr(paf.employment_category,1,2),
'FR',v_fr_min_range,v_min_range) and
trunc(to_number(pppv.proposed_salary)) * ppb.pay_annualization_factor <=
decode(substr(paf.employment_category,1,2),
'FR',v_fr_max_range,v_max_range) and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code)
)
)
union all
select
paf.person_id count_total,
'FR' employment_category,
ppf.sex sex,
decode(cma_province_count,1,hl1.region_1,2,hl1.region_2) cma_province
from
per_jobs pj,
per_assignments_f paf,
per_people_f ppf,
per_pay_proposals_v2 pppv,
per_person_types ppt,
hr_locations hl1,
per_ca_ee_report_lines pert,
hr_lookups hl2,
hr_soft_coding_keyflex hsck,
per_pay_bases ppb
where
upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
l_year_end between
paf.effective_start_date and
paf.effective_end_date and
(paf.employment_category is null OR
substr(paf.employment_category,1,2) not in ('FR','PR','PT')) and
paf.pay_basis_id = ppb.pay_basis_id and
ppb.business_group_id = p_business_group_id and
paf.person_id=ppf.person_id and
l_year_end between
ppf.effective_start_date and
ppf.effective_end_date and
ppf.person_type_id=ppt.person_type_id and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
ppf.business_group_id=p_business_group_id and
paf.location_id=hl1.location_id and
hl2.lookup_type=decode(cma_province_count,1,'CA_PROVINCE'
,2,'CA_CMA') and
hl2.lookup_code=decode(cma_province_count,1,hl1.region_1
,2,hl1.region_2) and
--pert.segment4 = 'Y' and
pert.request_id=p_request_id and
pert.context=decode(cma_province_count,1,'FORM14'
,2,'FORM13') and
pert.segment1=decode(cma_province_count,1,'PROVINCE'
,2,'CMA') and
pert.segment2=hl2.meaning and
paf.assignment_id=pppv.assignment_id and
pppv.change_date = (select max(pppv2.change_date)
from per_pay_proposals_v2 pppv2
where pppv2.assignment_id = paf.assignment_id
and pppv2.approved = 'Y'
and pppv2.change_date <= l_year_end
) and
--to_number(pppv.proposed_salary) >= v_min_range and
--pppv.change_date <= l_year_end and
trunc(to_number(pppv.proposed_salary))* ppb.pay_annualization_factor
>= v_fr_min_range and
trunc(to_number(pppv.proposed_salary))* ppb.pay_annualization_factor
<= v_fr_max_range and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code)
)
)
)
group by employment_category,sex,cma_province
order by cma_province,employment_category,sex;
select
meaning from hr_lookups
where
upper(ltrim(rtrim(lookup_type)))=decode(cp,1,'CA_PROVINCE'
,2,'CA_CMA') and
upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_cma_province)));
select
count(distinct person_id) count,
employment_category employment_category,
sex sex,
cma_province cma_province
from
(
select
paf.person_id person_id,
substr(paf.employment_category,1,2) employment_category,
ppf.sex sex,
decode(cma_province_ct,1,hl1.region_1,2,hl1.region_2) cma_province
from
per_jobs pj,
per_assignments_f paf,
per_people_f ppf,
per_pay_proposals_v2 pppv,
per_person_types ppt,
hr_locations hl1,
per_ca_ee_report_lines pert,
hr_lookups hl2,
hr_soft_coding_keyflex hsck,
per_pay_bases ppb
where
upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
paf.effective_start_date and
paf.effective_end_date and
paf.employment_category is not null and
substr(paf.employment_category,1,2) in ('FR','PR','PT') and
paf.person_id=ppf.person_id and
decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
ppf.effective_start_date and
ppf.effective_end_date and
paf.pay_basis_id = ppb.pay_basis_id and
ppb.business_group_id = p_business_group_id and
ppf.person_type_id=ppt.person_type_id and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
ppf.business_group_id=p_business_group_id and
paf.location_id=hl1.location_id and
hl2.lookup_type=decode(cma_province_ct,1,'CA_PROVINCE'
,2,'CA_CMA') and
hl2.lookup_code=decode(cma_province_ct,1,hl1.region_1
,2,hl1.region_2) and
--pert.segment4 = 'Y' and
pert.request_id=p_request_id and
pert.context=decode(cma_province_ct,1,'FORM14'
,2,'FORM13') and
pert.segment1=decode(cma_province_ct,1,'PROVINCE'
,2,'CMA') and
pert.segment2=hl2.meaning and
decode(desig,1,per_information5,
2,per_information6,
3,per_information7)='Y' and
substr(NVL(paf.employment_category,'FR'),1,2)=v_employment_category and
paf.assignment_id=pppv.assignment_id and
pppv.change_date = (select max(pppv2.change_date)
from per_pay_proposals_v2 pppv2
where pppv2.assignment_id = paf.assignment_id
and pppv2.approved = 'Y'
and pppv2.change_date <=
decode(substr(paf.employment_category,1,2),
'PT',p_date_tmp_emp,l_year_end)
) and
trunc(to_number(pppv.proposed_salary)) * ppb.pay_annualization_factor >=
decode(substr(paf.employment_category,1,2),
'FR',v_fr_min_range,v_min_range) and
trunc(to_number(pppv.proposed_salary)) * ppb.pay_annualization_factor <=
decode(substr(paf.employment_category,1,2),
'FR',v_fr_max_range,v_max_range) and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code)
)
)
union all
select
paf.person_id person_id,
'FR' employment_category,
ppf.sex sex,
decode(cma_province_ct,1,hl1.region_1,2,hl1.region_2) cma_province
from
per_jobs pj,
per_assignments_f paf,
per_people_f ppf,
per_pay_proposals_v2 pppv,
per_person_types ppt,
hr_locations hl1,
per_ca_ee_report_lines pert,
hr_lookups hl2,
hr_soft_coding_keyflex hsck,
per_pay_bases ppb
where
upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
l_year_end between
paf.effective_start_date and
paf.effective_end_date and
(paf.employment_category is null OR
substr(paf.employment_category,1,2) not in ('FR','PR','PT')) and
paf.person_id=ppf.person_id and
l_year_end between
paf.effective_start_date and
paf.effective_end_date and
ppf.person_type_id=ppt.person_type_id and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
ppf.business_group_id=p_business_group_id and
paf.location_id=hl1.location_id and
hl2.lookup_type=decode(cma_province_ct,1,'CA_PROVINCE'
,2,'CA_CMA') and
hl2.lookup_code=decode(cma_province_ct,1,hl1.region_1
,2,hl1.region_2) and
--pert.segment4 = 'Y' and
pert.request_id=p_request_id and
pert.context=decode(cma_province_ct,1,'FORM14'
,2,'FORM13') and
pert.segment1=decode(cma_province_ct,1,'PROVINCE'
,2,'CMA') and
pert.segment2=hl2.meaning and
decode(desig,1,per_information5,
2,per_information6,
3,per_information7)='Y' and
--substr(NVL(paf.employment_category,'FR'),1,2)=v_employment_category and
paf.pay_basis_id = ppb.pay_basis_id and
ppb.business_group_id = p_business_group_id and
paf.assignment_id=pppv.assignment_id and
pppv.change_date = (select max(pppv2.change_date)
from per_pay_proposals_v2 pppv2
where pppv2.assignment_id = paf.assignment_id
and pppv2.approved = 'Y'
and pppv2.change_date <= l_year_end
) and
trunc(to_number(pppv.proposed_salary))
* ppb.pay_annualization_factor >= v_fr_min_range and
trunc(to_number(pppv.proposed_salary))
* ppb.pay_annualization_factor <= v_fr_max_range and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code)
)
)
)
group by employment_category,sex,cma_province
order by employment_category,sex,cma_province;
select
decode(pc,1,'PROVINCE','CMA') provcma,
segment2 provcma_name,
p_category emp_category,
decode(p_category,'FR',v_fr_min_range|| ' - ' || v_fr_max_range,
v_min_range || ' - ' || v_max_range)
max_min_range
from
per_ca_ee_report_lines
where
request_id = p_request_id and
context=decode(pc,1,'FORM14','FORM13') and
segment1 = decode(pc,1,'PROVINCE','CMA') and
segment3 <> '0'
minus
select
segment1,
segment2,
segment4,
segment3
from
per_ca_ee_report_lines
where
request_id = p_request_id and
context = 'FORM3' and
segment1 = decode(pc,1,'PROVINCE','CMA') and
segment21 = v_naic_code;
select
segment3,
segment4,
sum(to_number(segment5)) segment5,
sum(to_number(segment6)) segment6,
sum(to_number(segment7)) segment7,
sum(to_number(segment8)) segment8,
sum(to_number(segment9)) segment9,
sum(to_number(segment10)) segment10,
sum(to_number(segment11)) segment11,
sum(to_number(segment12)) segment12,
sum(to_number(segment13)) segment13,
sum(to_number(segment14)) segment14,
sum(to_number(segment15)) segment15,
sum(to_number(segment16)) segment16
from
per_ca_ee_report_lines
where
request_id = p_request_id and
context = 'FORM3' and
segment1 = 'PROVINCE' and
segment21 = v_naic_code
group by segment3,segment4;
insert into per_ca_ee_report_lines
( request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment21) values
( p_request_id,
per_ca_ee_extract_pkg.k,
'FORM3',
decode(i,1,'PROVINCE',
2,'CMA'),
v_meaning,
decode(v_employment_category,'FR',
v_fr_min_range||' - '||v_fr_max_range,
v_min_range||' - '||v_max_range),
v_employment_category,
nvl(v_count,0),
decode(v_sex,'F',v_count,0),
decode(v_sex,'M',v_count,0),
0,
0,
0,
0,
0,
0,
0,
0,
0,
v_naic_code) ;
update per_ca_ee_report_lines set
segment5 = segment5 + nvl(v_count,0),
segment7 = nvl(v_count,0)
where
request_id=p_request_id and
line_number=per_ca_ee_extract_pkg.k and
context = 'FORM3' and
segment1 = decode(i,1,'PROVINCE',2,'CMA') and
segment2 = v_meaning and
segment3 = decode(v_employment_category,'FR',
v_fr_min_range|| ' - '||v_fr_max_range,
v_min_range|| ' - '||v_max_range) and
segment4 = v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment5=segment5 + nvl(v_count,0),
segment6=nvl(v_count,0)
where
request_id=p_request_id and
line_number=per_ca_ee_extract_pkg.k and
context='FORM3' and
segment1=decode(i,1,'PROVINCE',2,'CMA') and
segment2=v_meaning and
segment3=decode(v_employment_category,'FR',
v_fr_min_range|| ' - '||v_fr_max_range ,
v_min_range|| ' - '||v_max_range) and
segment4=v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment8=nvl(segment8,0) + nvl(l.count,0),
segment9=nvl(l.count,0)
where
request_id = p_request_id and
context = 'FORM3' and
ltrim(rtrim(segment1)) = decode(i,1,'PROVINCE',2,'CMA') and
ltrim(rtrim(segment2)) = v_meaning and
ltrim(rtrim(segment3)) = decode(v_employment_category,'FR',
v_fr_min_range|| ' - '||v_fr_max_range,
v_min_range|| ' - ' ||v_max_range) and
upper(ltrim(rtrim(segment4))) =
upper(ltrim(rtrim(v_employment_category))) and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment8 = nvl(segment8,0) + nvl(l.count,0),
segment10 = nvl(l.count,0)
where
request_id = p_request_id and
context = 'FORM3' and
ltrim(rtrim(segment1)) = decode(i,1,'PROVINCE',2,'CMA') and
ltrim(rtrim(segment2)) = v_meaning and
rtrim(ltrim(segment3)) = decode(v_employment_category,'FR',
v_fr_min_range|| ' - '||v_fr_max_range,
v_min_range|| ' - ' ||v_max_range) and
upper(ltrim(rtrim(segment4)))
= upper(ltrim(rtrim(v_employment_category))) and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment11=nvl(segment11,0) + nvl(l.count,0),
segment12=nvl(l.count,0)
where
request_id=p_request_id and
context='FORM3' and
ltrim(rtrim(segment1))=decode(i,1,'PROVINCE',2,'CMA') and
ltrim(rtrim(segment2))=v_meaning and
ltrim(rtrim(segment3))=decode(v_employment_category,'FR',
v_fr_min_range|| ' - '||v_fr_max_range,
v_min_range|| ' - ' ||v_max_range) and
upper(ltrim(rtrim(segment4)))
= upper(ltrim(rtrim(v_employment_category))) and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment11=nvl(segment11,0) + nvl(l.count,0),
segment13=nvl(l.count,0)
where
request_id=p_request_id and
context='FORM3' and
ltrim(rtrim(segment1))=decode(i,1,'PROVINCE',2,'CMA') and
ltrim(rtrim(segment2))=v_meaning and
ltrim(rtrim(segment3))= decode(v_employment_category,'FR',
v_fr_min_range|| ' - '||v_fr_max_range,
v_min_range|| ' - ' ||v_max_range) and
upper(ltrim(rtrim(segment4)))
= upper(ltrim(rtrim(v_employment_category))) and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment14=nvl(segment14,0) + nvl(l.count,0),
segment16=nvl(l.count,0)
where
request_id=p_request_id and
context='FORM3' and
ltrim(rtrim(segment1))=decode(i,1,'PROVINCE',2,'CMA') and
ltrim(rtrim(segment2))=v_meaning and
ltrim(rtrim(segment3))= decode(v_employment_category,'FR',
v_fr_min_range|| ' - '||v_fr_max_range,
v_min_range|| ' - ' ||v_max_range) and
upper(ltrim(rtrim(segment4)))
=upper(ltrim(rtrim(v_employment_category))) and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment14=nvl(segment14,0) + nvl(l.count,0),
segment15=nvl(l.count,0)
where
request_id=p_request_id and
context='FORM3' and
ltrim(rtrim(segment1)) = decode(i,1,'PROVINCE',2,'CMA') and
ltrim(rtrim(segment2)) = v_meaning and
ltrim(rtrim(segment3)) = decode(v_employment_category,'FR',
v_fr_min_range|| ' - '||v_fr_max_range,
v_min_range|| ' - ' ||v_max_range) and
upper(ltrim(rtrim(segment4)))
=upper(ltrim(rtrim(v_employment_category))) and
segment21 = v_naic_code;
insert into per_ca_ee_report_lines
( request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment21) values
( p_request_id,
per_ca_ee_extract_pkg.k,
'FORM3',
decode(pc_count,1,'PROVINCE','CMA'),
l.provcma_name,
decode(emp_cat,'FR',v_fr_min_range||' - '||v_fr_max_range,
v_min_range||' - '||v_max_range),
emp_cat,
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
v_naic_code);
insert into per_ca_ee_report_lines
(request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment21) values
( p_request_id,
per_ca_ee_extract_pkg.k,
'FORM3',
'NATIONAL',
count_national.segment3,
count_national.segment4,
count_national.segment5,
count_national.segment6,
count_national.segment7,
count_national.segment8,
count_national.segment9,
count_national.segment10,
count_national.segment11,
count_national.segment12,
count_national.segment13,
count_national.segment14,
count_national.segment15,
count_national.segment16,
v_naic_code);
select
pert.segment4 naic_code
from
per_ca_ee_report_lines pert
where
pert.request_id = p_request_id and
pert.context = 'FORM12';
select
count(distinct count_total) count_total,
meaning meaning,
sex sex,
employment_category employment_category,
province province
from
(
select
paf.person_id count_total,
hl.meaning meaning,
ppf.sex sex,
substr(employment_category,1,2) employment_category,
hl1.region_1 province
from
hr_lookups hl,
per_jobs pj,
per_assignments_f paf,
per_people_f ppf,
per_person_types ppt,
hr_locations hl1,
per_ca_ee_report_lines pert,
hr_lookups hl2,
hr_soft_coding_keyflex hsck
where
hl.lookup_type='EEOG' and
upper(ltrim(rtrim(hl.lookup_code)))
= upper(ltrim(rtrim(pj.job_information1))) and
upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
--decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
-- paf.effective_start_date and
-- paf.effective_end_date and
--paf.effective_start_date < l_year_end and
--paf.effective_end_date > l_year_start and
ppf.start_date between
paf.effective_start_date and
paf.effective_end_date and
paf.employment_category is not null and
substr(employment_category,1,2) in ('FR','PR','PT') and
paf.person_id=ppf.person_id and
--decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
-- ppf.effective_start_date and
-- ppf.effective_end_date and
ppf.effective_start_date < l_year_end and
ppf.effective_end_date > l_year_start and
ppf.start_date between l_year_start and
l_year_end and
ppf.person_type_id=ppt.person_type_id and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
ppf.business_group_id=p_business_group_id and
paf.location_id=hl1.location_id and
hl1.region_1=hl2.lookup_code and
hl2.lookup_type='CA_PROVINCE' and
pert.request_id=p_request_id and
hl2.meaning=pert.segment2 and
--pert.segment4 = 'Y' and
pert.context='FORM14' and
pert.segment1='PROVINCE' and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code)
)
) and
exists
(
select 'X'
from per_pay_proposals_v2 pppv
where
pppv.assignment_id = paf.assignment_id and
pppv.approved = 'Y' and
pppv.change_date <= l_year_end
)
union all
select
paf.person_id count_total,
hl.meaning meaning,
ppf.sex sex,
'FR' employment_category,
hl1.region_1 province
from
hr_lookups hl,
per_jobs pj,
per_assignments_f paf,
per_people_f ppf,
per_person_types ppt,
hr_locations hl1,
per_ca_ee_report_lines pert,
hr_lookups hl2,
hr_soft_coding_keyflex hsck
where
hl.lookup_type='EEOG' and
upper(ltrim(rtrim(hl.lookup_code)))
=upper(ltrim(rtrim(pj.job_information1))) and
upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
--l_year_end between
-- paf.effective_start_date and
-- paf.effective_end_date and
--paf.effective_start_date < l_year_end and
--paf.effective_end_date > l_year_start and
ppf.start_date between
paf.effective_start_date and
paf.effective_end_date and
(paf.employment_category is null OR
substr(paf.employment_category,1,2) not in ('FR','PR','PT')) and
paf.person_id=ppf.person_id and
--l_year_end between
-- ppf.effective_start_date and
-- ppf.effective_end_date and
ppf.effective_start_date < l_year_end and
ppf.effective_end_date > l_year_start and
ppf.start_date between l_year_start and
l_year_end and
ppf.person_type_id=ppt.person_type_id and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
ppf.business_group_id=p_business_group_id and
paf.location_id=hl1.location_id and
hl1.region_1=hl2.lookup_code and
hl2.lookup_type='CA_PROVINCE' and
pert.request_id=p_request_id and
hl2.meaning=pert.segment2 and
--pert.segment4 = 'Y' and
pert.context='FORM14' and
pert.segment1='PROVINCE' and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code)
)
) and
exists
(
select 'X'
from per_pay_proposals_v2 pppv
where
pppv.assignment_id = paf.assignment_id and
pppv.approved = 'Y' and
pppv.change_date <= l_year_end
) -- End of Exists
)
group by province,meaning,employment_category,sex
order by province,meaning,employment_category,sex;
select
meaning
from
hr_lookups
where
upper(ltrim(rtrim(lookup_type)))='CA_PROVINCE' and
upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_province)));
select
count(distinct person_id) count,
meaning meaning,
employment_category employment_category,
sex sex,
province province
from
(
select
paf.person_id person_id,
hl.meaning meaning,
substr(paf.employment_category,1,2) employment_category,
ppf.sex sex,
hl1.region_1 province
from
hr_lookups hl,
per_jobs pj,
per_assignments_f paf,
per_people_f ppf,
per_person_types ppt,
hr_locations hl1,
per_ca_ee_report_lines pert,
hr_lookups hl2,
hr_soft_coding_keyflex hsck
where
upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and
upper(ltrim(rtrim(hl.lookup_code)))=
upper(ltrim(ltrim(pj.job_information1))) and
upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
--decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
-- paf.effective_start_date and
-- paf.effective_end_date and
--paf.effective_start_date < l_year_end and
--paf.effective_end_date > l_year_start and
ppf.start_date between
paf.effective_start_date and
paf.effective_end_date and
paf.employment_category is not null and
substr(paf.employment_category,1,2) in ('FR','PR','PT') and
paf.person_id=ppf.person_id and
--decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
-- ppf.effective_start_date and
-- ppf.effective_end_date and
ppf.effective_start_date < l_year_end and
ppf.effective_end_date > l_year_start and
ppf.start_date between l_year_start and
l_year_end and
ppf.person_type_id=ppt.person_type_id and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
ppf.business_group_id=p_business_group_id and
paf.location_id=hl1.location_id and
hl1.region_1=hl2.lookup_code and
hl2.lookup_type='CA_PROVINCE' and
pert.request_id=p_request_id and
hl2.meaning=pert.segment2 and
--pert.segment4 = 'Y' and
pert.context='FORM14' and
pert.segment1='PROVINCE' and
decode(desig,1,ppf.per_information5,
2,ppf.per_information6,
3,ppf.per_information7)='Y' and
/* Modified the condition for Bug 11651960
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code)
)
) and */
( hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
((
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code)
))
) and
exists
(
select 'X'
from per_pay_proposals_v2 pppv
where
pppv.assignment_id = paf.assignment_id and
pppv.approved = 'Y' and
pppv.change_date <= l_year_end
) -- End of Exists
union all
select
paf.person_id person_id,
hl.meaning meaning,
'FR' employment_category,
ppf.sex sex,
hl1.region_1 province
from
hr_lookups hl,
per_jobs pj,
per_assignments_f paf,
per_people_f ppf,
per_person_types ppt,
hr_locations hl1,
per_ca_ee_report_lines pert,
hr_lookups hl2,
hr_soft_coding_keyflex hsck
where
upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and
upper(ltrim(rtrim(hl.lookup_code)))
= upper(ltrim(ltrim(pj.job_information1))) and
upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
--l_year_end between
-- paf.effective_start_date and
-- paf.effective_end_date and
-- paf.effective_start_date < l_year_end and
-- paf.effective_end_date > l_year_start and
ppf.start_date between
paf.effective_start_date and
paf.effective_end_date and
(paf.employment_category is null OR
substr(paf.employment_category,1,2) not in ('FR','PR','PT')) and
paf.person_id=ppf.person_id and
--l_year_end between
-- ppf.effective_start_date and
-- ppf.effective_end_date and
ppf.effective_start_date < l_year_end and
ppf.effective_end_date > l_year_start and
ppf.start_date between l_year_start and
l_year_end and
ppf.person_type_id=ppt.person_type_id and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
ppf.business_group_id=p_business_group_id and
paf.location_id=hl1.location_id and
hl1.region_1=hl2.lookup_code and
hl2.lookup_type='CA_PROVINCE' and
pert.request_id=p_request_id and
hl2.meaning=pert.segment2 and
--pert.segment4 = 'Y' and
pert.context='FORM14' and
pert.segment1='PROVINCE' and
decode(desig,1,ppf.per_information5,
2,ppf.per_information6,
3,ppf.per_information7)='Y' and
/* Modified the condition for Bug 11651960
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code)
)
) and */
( hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
((
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code)
))
) and
exists
(
select 'X'
from per_pay_proposals_v2 pppv
where
pppv.assignment_id = paf.assignment_id and
pppv.approved = 'Y' and
pppv.change_date <= l_year_end
) -- End of Exists
)
group by province,meaning,employment_category,sex
order by province,meaning,employment_category,sex;
select
meaning
from
hr_lookups
where
lookup_type='EEOG';
select
segment2,
v_meaning,
decode(p_emp_cat,1,'FR',2,'PR',3,'PT') emp_category
from
per_ca_ee_report_lines where
request_id=p_request_id and
context='FORM14' and
segment1='PROVINCE' and
segment3 <> '0'
minus
select
segment2,
segment3,
segment4
from
per_ca_ee_report_lines
where
request_id=p_request_id and
context='FORM4' and
segment1='PROVINCE'and
segment21 = v_naic_code;
select
segment3,
segment4,
sum(to_number(segment5)) segment5,
sum(to_number(segment6)) segment6,
sum(to_number(segment7)) segment7,
sum(to_number(segment8)) segment8,
sum(to_number(segment9)) segment9,
sum(to_number(segment10)) segment10,
sum(to_number(segment11)) segment11,
sum(to_number(segment12)) segment12,
sum(to_number(segment13)) segment13,
sum(to_number(segment14)) segment14,
sum(to_number(segment15)) segment15,
sum(to_number(segment16)) segment16
from
per_ca_ee_report_lines
where
request_id = p_request_id and
context = 'FORM4' and
segment1 = 'PROVINCE' and
segment21 = v_naic_code
group by segment3,segment4;
insert into per_ca_ee_report_lines
(request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment21) values
( p_request_id,
per_ca_ee_extract_pkg.k,
'FORM4',
'PROVINCE',
v_province_name,
v_meaning,
v_employment_category,
nvl(v_count,0),
decode(v_sex,'F',v_count,0),
decode(v_sex,'M',v_count,0),
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
v_naic_code);
update per_ca_ee_report_lines set
segment7=nvl(v_count,0),
segment5=segment5 + nvl(v_count,0)
where request_id=p_request_id and
line_number=per_ca_ee_extract_pkg.k and
context='FORM4' and
segment1='PROVINCE' and
segment2=v_province_name and
segment3=v_meaning and
segment4=v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment6=nvl(v_count,0),
segment5=segment5 + nvl(v_count,0)
where request_id=p_request_id and
line_number=per_ca_ee_extract_pkg.k and
context='FORM4' and
segment1='PROVINCE' and
segment2=v_province_name and
segment3=v_meaning and
segment4=v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment8 = nvl(segment8,0) + nvl(v_count,0),
segment10 = nvl(v_count,0)
where
request_id = p_request_id and
context='FORM4' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_meaning and
segment4 = v_employment_category;
update per_ca_ee_report_lines set
segment8 = nvl(segment8,0) + nvl(v_count,0),
segment9 = nvl(v_count,0)
where
request_id = p_request_id and
context='FORM4' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_meaning and
segment4 = v_employment_category;
update per_ca_ee_report_lines set
segment11 = nvl(segment11,0) + nvl(v_count,0),
segment13 = nvl(v_count,0)
where
request_id = p_request_id and
context='FORM4' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_meaning and
segment4 = v_employment_category;
update per_ca_ee_report_lines set
segment11 = nvl(segment11,0) + nvl(v_count,0),
segment12 = nvl(v_count,0)
where
request_id = p_request_id and
context='FORM4' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_meaning and
segment4 = v_employment_category;
update per_ca_ee_report_lines set
segment14 = nvl(segment14,0) + nvl(v_count,0),
segment16 = nvl(v_count,0)
where
request_id = p_request_id and
context='FORM4' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_meaning and
segment4 = v_employment_category;
update per_ca_ee_report_lines set
segment14 = nvl(segment14,0) + nvl(v_count,0),
segment15 = nvl(v_count,0)
where
request_id = p_request_id and
context='FORM4' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_meaning and
segment4 = v_employment_category;
insert into per_ca_ee_report_lines
(request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment21) values
( p_request_id,
per_ca_ee_extract_pkg.k,
'FORM4',
'PROVINCE',
x.segment2,
v_meaning,
x.emp_category,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
v_naic_code);
insert into per_ca_ee_report_lines
(request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment21) values
( p_request_id,
per_ca_ee_extract_pkg.k,
'FORM4',
'NATIONAL',
count_national.segment3,
count_national.segment4,
count_national.segment5,
count_national.segment6,
count_national.segment7,
count_national.segment8,
count_national.segment9,
count_national.segment10,
count_national.segment11,
count_national.segment12,
count_national.segment13,
count_national.segment14,
count_national.segment15,
count_national.segment16,
v_naic_code);
select
pert.segment4 naic_code
from
per_ca_ee_report_lines pert
where
pert.request_id = p_request_id and
pert.context = 'FORM12';
select
count(distinct count_total) count_total,
meaning meaning,
sex sex,
employment_category employment_category,
province province
from
(
select
paf.person_id count_total,
hl.meaning meaning,
ppf.sex sex,
substr(paf.employment_category,1,2) employment_category,
hl1.region_1 province
from
hr_lookups hl,
per_jobs pj,
per_assignments_f paf,
per_people_f ppf,
per_person_types ppt,
hr_locations hl1,
per_ca_ee_report_lines pert,
hr_lookups hl2,
hr_soft_coding_keyflex hsck
where
hl.lookup_type='EEOG' and
upper(ltrim(rtrim(hl.lookup_code)))
=upper(ltrim(rtrim(pj.job_information1))) and
upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
--decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
-- paf.effective_start_date and
-- paf.effective_end_date and
ppf.start_date between
paf.effective_start_date and
paf.effective_end_date and
paf.employment_category is not null and
substr(paf.employment_category,1,2) in ('FR','PR','PT') and
paf.person_id=ppf.person_id and
--decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
-- ppf.effective_start_date and
-- ppf.effective_end_date and
ppf.effective_start_date < l_year_end and
ppf.effective_end_date > l_year_start and
ppf.person_type_id=ppt.person_type_id and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
ppf.business_group_id=p_business_group_id and
paf.location_id=hl1.location_id and
hl1.region_1=hl2.lookup_code and
hl2.lookup_type='CA_PROVINCE' and
pert.request_id=p_request_id and
hl2.meaning=pert.segment2 and
--pert.segment4 = 'Y' and
pert.context='FORM14' and
pert.segment1='PROVINCE' and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code)
)
) and
exists
(
select 'X'
from per_pay_proposals_v2 pppv
where
pppv.assignment_id = paf.assignment_id and
pppv.approved = 'Y' and
pppv.change_date between l_year_start and
l_year_end and
pppv.proposal_reason =
(
select lookup_code
from hr_lookups
where lookup_type = 'PROPOSAL_REASON' and
upper(meaning) = 'PROMOTION'
)
)
union all
select
paf.person_id count_total,
hl.meaning meaning,
ppf.sex sex,
'FR' employment_category,
hl1.region_1 province
from
hr_lookups hl,
per_jobs pj,
per_assignments_f paf,
per_people_f ppf,
per_person_types ppt,
hr_locations hl1,
per_ca_ee_report_lines pert,
hr_lookups hl2,
hr_soft_coding_keyflex hsck
where
hl.lookup_type='EEOG' and
upper(ltrim(rtrim(hl.lookup_code)))
=upper(ltrim(rtrim(pj.job_information1))) and
upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
--l_year_end between
-- ppf.effective_start_date and
-- ppf.effective_end_date and
ppf.start_date between
paf.effective_start_date and
paf.effective_end_date and
(paf.employment_category is null OR
substr(paf.employment_category,1,2) not in ('FR','PR','PT')) and
paf.person_id=ppf.person_id and
ppf.effective_start_date < l_year_end and
ppf.effective_end_date > l_year_start and
--l_year_end between
-- ppf.effective_start_date and
-- ppf.effective_end_date and
ppf.person_type_id=ppt.person_type_id and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
ppf.business_group_id=p_business_group_id and
paf.location_id=hl1.location_id and
hl1.region_1=hl2.lookup_code and
hl2.lookup_type='CA_PROVINCE' and
pert.request_id=p_request_id and
hl2.meaning=pert.segment2 and
--pert.segment4 = 'Y' and
pert.context='FORM14' and
pert.segment1='PROVINCE' and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code)
)
) and
exists
(
select 'X'
from per_pay_proposals_v2 pppv
where
pppv.assignment_id = paf.assignment_id and
pppv.approved = 'Y' and
pppv.change_date between l_year_start and
l_year_end and
pppv.proposal_reason =
(
select lookup_code
from hr_lookups
where lookup_type = 'PROPOSAL_REASON' and
upper(meaning) = 'PROMOTION'
)
)
)
group by province,meaning,employment_category,sex
order by province,meaning,employment_category,sex;
cursor cur_meaning is select
meaning
from
hr_lookups
where
upper(ltrim(rtrim(lookup_type)))='CA_PROVINCE' and
upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_province)));
select
count(distinct person_id) count,
meaning meaning,
employment_category employment_category,
sex sex,
province province
from
(
select
paf.person_id person_id,
hl.meaning meaning,
substr(paf.employment_category,1,2) employment_category,
ppf.sex sex,
hl1.region_1 province
from
hr_lookups hl,
per_jobs pj,
per_assignments_f paf,
per_people_f ppf,
per_person_types ppt,
hr_locations hl1,
per_ca_ee_report_lines pert,
hr_lookups hl2,
hr_soft_coding_keyflex hsck
where
upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and
upper(ltrim(rtrim(hl.lookup_code)))=upper(ltrim(ltrim(pj.job_information1))) and
upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
--decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
-- ppf.effective_start_date and
-- ppf.effective_end_date and
ppf.start_date between
paf.effective_start_date and
paf.effective_end_date and
paf.employment_category is not null and
substr(paf.employment_category,1,2) in ('FR','PR','PT')and
paf.person_id=ppf.person_id and
--decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
-- ppf.effective_start_date and
-- ppf.effective_end_date and
ppf.effective_start_date < l_year_end and
ppf.effective_end_date > l_year_start and
ppf.person_type_id=ppt.person_type_id and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
ppf.business_group_id=p_business_group_id and
paf.location_id=hl1.location_id and
hl1.region_1=hl2.lookup_code and
hl2.lookup_type='CA_PROVINCE' and
pert.request_id=p_request_id and
hl2.meaning=pert.segment2 and
--pert.segment4 = 'Y' and
pert.context='FORM14' and
pert.segment1='PROVINCE' and
decode(desig,1,per_information5,
2,per_information6,
3,per_information7)='Y' and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code)
)
) and
exists
(
select 'X'
from per_pay_proposals_v2 pppv
where
pppv.assignment_id = paf.assignment_id and
pppv.change_date between l_year_start and
l_year_end and
pppv.approved = 'Y' and
pppv.proposal_reason =
(
select lookup_code
from hr_lookups
where lookup_type = 'PROPOSAL_REASON' and
upper(meaning) = 'PROMOTION'
)
)
union all
select
paf.person_id person_id,
hl.meaning meaning,
'FR' employment_category,
ppf.sex sex,
hl1.region_1 province
from
hr_lookups hl,
per_jobs pj,
per_assignments_f paf,
per_people_f ppf,
per_person_types ppt,
hr_locations hl1,
per_ca_ee_report_lines pert,
hr_lookups hl2,
hr_soft_coding_keyflex hsck
where
upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and
upper(ltrim(rtrim(hl.lookup_code)))=upper(ltrim(ltrim(pj.job_information1))) and
upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
--l_year_end between
-- paf.effective_start_date and
-- paf.effective_end_date and
ppf.effective_start_date between
paf.effective_start_date and
paf.effective_end_date and
(paf.employment_category is null OR
substr(paf.employment_category,1,2) not in ('FR','PR','PT'))and
paf.person_id=ppf.person_id and
--l_year_end between
-- ppf.effective_start_date and
-- ppf.effective_end_date and
ppf.effective_start_date < l_year_end and
ppf.effective_end_date > l_year_start and
ppf.person_type_id=ppt.person_type_id and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
ppf.business_group_id=p_business_group_id and
paf.location_id=hl1.location_id and
hl1.region_1=hl2.lookup_code and
hl2.lookup_type='CA_PROVINCE' and
pert.request_id=p_request_id and
hl2.meaning=pert.segment2 and
--pert.segment4 = 'Y' and
pert.context='FORM14' and
pert.segment1='PROVINCE' and
decode(desig,1,per_information5,
2,per_information6,
3,per_information7)='Y' and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code)
)
) and
exists
(
select 'X'
from per_pay_proposals_v2 pppv
where
pppv.assignment_id = paf.assignment_id and
pppv.approved = 'Y'and
pppv.change_date between l_year_start and
l_year_end and
pppv.proposal_reason =
(
select lookup_code
from hr_lookups
where lookup_type = 'PROPOSAL_REASON' and
upper(meaning) = 'PROMOTION'
)
)
)
group by province,meaning,employment_category,sex
order by province,meaning,employment_category,sex;
select
meaning
from
hr_lookups
where
lookup_type='EEOG';
select
segment2,
v_meaning,
decode(p_emp_cat,1,'FR',2,'PR',3,'PT') emp_category
from
per_ca_ee_report_lines where
request_id=p_request_id and
context='FORM14' and
segment1='PROVINCE' and
segment3 <> '0'
minus
select
segment2,
segment3,
segment4
from
per_ca_ee_report_lines
where
request_id=p_request_id and
context='FORM5' and
segment1='PROVINCE'and
segment21 = v_naic_code;
select
segment3,
segment4,
sum(to_number(segment5)) segment5,
sum(to_number(segment6)) segment6,
sum(to_number(segment7)) segment7,
sum(to_number(segment8)) segment8,
sum(to_number(segment9)) segment9,
sum(to_number(segment10)) segment10,
sum(to_number(segment11)) segment11,
sum(to_number(segment12)) segment12,
sum(to_number(segment13)) segment13,
sum(to_number(segment14)) segment14,
sum(to_number(segment15)) segment15,
sum(to_number(segment16)) segment16
from
per_ca_ee_report_lines
where
request_id = p_request_id and
context = 'FORM5' and
segment1 = 'PROVINCE' and
segment21 = v_naic_code
group by segment3,segment4;
select
count(count_total) count_total,
sex sex,
employment_category employment_category,
province province
from
(
select
paf.person_id count_total,
ppf.sex sex,
substr(paf.employment_category,1,2) employment_category,
hl1.region_1 province
from
hr_lookups hl,
per_jobs pj,
per_assignments_f paf,
per_people_f ppf,
per_person_types ppt,
hr_locations hl1,
per_ca_ee_report_lines pert,
hr_lookups hl2,
hr_soft_coding_keyflex hsck,
per_pay_proposals_v2 pppv
where
hl.lookup_type='EEOG' and
upper(ltrim(rtrim(hl.lookup_code)))
=upper(ltrim(rtrim(pj.job_information1))) and
upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
--decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
-- paf.effective_start_date and
-- paf.effective_end_date and
ppf.start_date between
paf.effective_start_date and
paf.effective_end_date and
paf.employment_category is not null and
substr(paf.employment_category,1,2) in ('FR','PR','PT') and
paf.person_id=ppf.person_id and
--decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
-- ppf.effective_start_date and
-- ppf.effective_end_date and
ppf.effective_start_date < l_year_end and
ppf.effective_end_date > l_year_start and
ppf.person_type_id=ppt.person_type_id and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
ppf.business_group_id=p_business_group_id and
paf.location_id=hl1.location_id and
hl1.region_1=hl2.lookup_code and
hl2.lookup_type='CA_PROVINCE' and
pert.request_id=p_request_id and
hl2.meaning=pert.segment2 and
--pert.segment4 = 'Y' and
pert.context='FORM14' and
pert.segment1='PROVINCE' and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code)
)
) and
pppv.assignment_id = paf.assignment_id and
pppv.approved = 'Y' and
pppv.change_date between l_year_start and
l_year_end and
pppv.proposal_reason =
(
select lookup_code
from hr_lookups
where lookup_type = 'PROPOSAL_REASON' and
upper(meaning) = 'PROMOTION'
)
union all
select
paf.person_id count_total,
ppf.sex sex,
'FR' employment_category,
hl1.region_1 province
from
hr_lookups hl,
per_jobs pj,
per_assignments_f paf,
per_people_f ppf,
per_person_types ppt,
hr_locations hl1,
per_ca_ee_report_lines pert,
hr_lookups hl2,
hr_soft_coding_keyflex hsck,
per_pay_proposals_v2 pppv
where
hl.lookup_type='EEOG' and
upper(ltrim(rtrim(hl.lookup_code)))
=upper(ltrim(rtrim(pj.job_information1))) and
upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
--l_year_end between
-- ppf.effective_start_date and
-- ppf.effective_end_date and
ppf.start_date between
paf.effective_start_date and
paf.effective_end_date and
(paf.employment_category is null OR
substr(paf.employment_category,1,2) not in ('FR','PR','PT')) and
paf.person_id=ppf.person_id and
ppf.effective_start_date < l_year_end and
ppf.effective_end_date > l_year_start and
--l_year_end between
-- ppf.effective_start_date and
-- ppf.effective_end_date and
ppf.person_type_id=ppt.person_type_id and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
ppf.business_group_id=p_business_group_id and
paf.location_id=hl1.location_id and
hl1.region_1=hl2.lookup_code and
hl2.lookup_type='CA_PROVINCE' and
pert.request_id=p_request_id and
hl2.meaning=pert.segment2 and
--pert.segment4 = 'Y' and
pert.context='FORM14' and
pert.segment1='PROVINCE' and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code)
)
) and
pppv.assignment_id = paf.assignment_id and
pppv.approved = 'Y' and
pppv.change_date between l_year_start and
l_year_end and
pppv.proposal_reason =
(
select lookup_code
from hr_lookups
where lookup_type = 'PROPOSAL_REASON' and
upper(meaning) = 'PROMOTION'
)
)
group by province,employment_category,sex
order by province,employment_category,sex;
select
count(count_total) count_total,
sex sex,
employment_category employment_category,
province province
from
(
select
paf.person_id count_total,
ppf.sex sex,
substr(paf.employment_category,1,2) employment_category,
hl1.region_1 province
from
hr_lookups hl,
per_jobs pj,
per_assignments_f paf,
per_people_f ppf,
per_person_types ppt,
hr_locations hl1,
per_ca_ee_report_lines pert,
hr_lookups hl2,
hr_soft_coding_keyflex hsck,
per_pay_proposals_v2 pppv
where
hl.lookup_type='EEOG' and
upper(ltrim(rtrim(hl.lookup_code)))
=upper(ltrim(rtrim(pj.job_information1))) and
upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
--decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
-- paf.effective_start_date and
-- paf.effective_end_date and
ppf.start_date between
paf.effective_start_date and
paf.effective_end_date and
paf.employment_category is not null and
substr(paf.employment_category,1,2) in ('FR','PR','PT') and
paf.person_id=ppf.person_id and
--decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
-- ppf.effective_start_date and
-- ppf.effective_end_date and
ppf.effective_start_date < l_year_end and
ppf.effective_end_date > l_year_start and
ppf.person_type_id=ppt.person_type_id and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
ppf.business_group_id=p_business_group_id and
paf.location_id=hl1.location_id and
hl1.region_1=hl2.lookup_code and
hl2.lookup_type='CA_PROVINCE' and
pert.request_id=p_request_id and
hl2.meaning=pert.segment2 and
--pert.segment4 = 'Y' and
pert.context='FORM14' and
pert.segment1='PROVINCE' and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code)
)
) and
pppv.assignment_id = paf.assignment_id and
pppv.approved = 'Y' and
pppv.change_date between l_year_start and
l_year_end and
pppv.proposal_reason =
(
select lookup_code
from hr_lookups
where lookup_type = 'PROPOSAL_REASON' and
upper(meaning) = 'PROMOTION'
)
union all
select
paf.person_id count_total,
ppf.sex sex,
'FR' employment_category,
hl1.region_1 province
from
hr_lookups hl,
per_jobs pj,
per_assignments_f paf,
per_people_f ppf,
per_person_types ppt,
hr_locations hl1,
per_ca_ee_report_lines pert,
hr_lookups hl2,
hr_soft_coding_keyflex hsck,
per_pay_proposals_v2 pppv
where
hl.lookup_type='EEOG' and
upper(ltrim(rtrim(hl.lookup_code)))
=upper(ltrim(rtrim(pj.job_information1))) and
upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
--l_year_end between
-- ppf.effective_start_date and
-- ppf.effective_end_date and
ppf.start_date between
paf.effective_start_date and
paf.effective_end_date and
(paf.employment_category is null OR
substr(paf.employment_category,1,2) not in ('FR','PR','PT')) and
paf.person_id=ppf.person_id and
ppf.effective_start_date < l_year_end and
ppf.effective_end_date > l_year_start and
--l_year_end between
-- ppf.effective_start_date and
-- ppf.effective_end_date and
ppf.person_type_id=ppt.person_type_id and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
ppf.business_group_id=p_business_group_id and
paf.location_id=hl1.location_id and
hl1.region_1=hl2.lookup_code and
hl2.lookup_type='CA_PROVINCE' and
pert.request_id=p_request_id and
hl2.meaning=pert.segment2 and
--pert.segment4 = 'Y' and
pert.context='FORM14' and
pert.segment1='PROVINCE' and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code)
)
) and
pppv.assignment_id = paf.assignment_id and
pppv.approved = 'Y' and
pppv.change_date between l_year_start and
l_year_end and
pppv.proposal_reason =
(
select lookup_code
from hr_lookups
where lookup_type = 'PROPOSAL_REASON' and
upper(meaning) = 'PROMOTION'
)
)
group by province,employment_category,sex
order by province,employment_category,sex;
select
count(person_id) count,
employment_category employment_category,
sex sex,
province province
from
(
select
paf.person_id person_id,
substr(paf.employment_category,1,2) employment_category,
ppf.sex sex,
hl1.region_1 province
from
hr_lookups hl,
per_jobs pj,
per_assignments_f paf,
per_people_f ppf,
per_person_types ppt,
hr_locations hl1,
per_ca_ee_report_lines pert,
hr_lookups hl2,
hr_soft_coding_keyflex hsck,
per_pay_proposals_v2 pppv
where
upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and
upper(ltrim(rtrim(hl.lookup_code)))
= upper(ltrim(ltrim(pj.job_information1))) and
upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
--decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
-- ppf.effective_start_date and
-- ppf.effective_end_date and
ppf.start_date between
paf.effective_start_date and
paf.effective_end_date and
paf.employment_category is not null and
substr(paf.employment_category,1,2) in ('FR','PR','PT')and
paf.person_id=ppf.person_id and
--decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
-- ppf.effective_start_date and
-- ppf.effective_end_date and
ppf.effective_start_date < l_year_end and
ppf.effective_end_date > l_year_start and
ppf.person_type_id=ppt.person_type_id and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
ppf.business_group_id=p_business_group_id and
paf.location_id=hl1.location_id and
hl1.region_1=hl2.lookup_code and
hl2.lookup_type='CA_PROVINCE' and
pert.request_id=p_request_id and
hl2.meaning=pert.segment2 and
--pert.segment4 = 'Y' and
pert.context='FORM14' and
pert.segment1='PROVINCE' and
decode(desig,1,per_information5,
2,per_information6,
3,per_information7)='Y' and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code)
)
) and
pppv.assignment_id = paf.assignment_id and
pppv.change_date between l_year_start and
l_year_end and
pppv.approved = 'Y' and
pppv.proposal_reason =
(
select lookup_code
from hr_lookups
where lookup_type = 'PROPOSAL_REASON' and
upper(meaning) = 'PROMOTION'
)
union all
select
paf.person_id person_id,
'FR' employment_category,
ppf.sex sex,
hl1.region_1 province
from
hr_lookups hl,
per_jobs pj,
per_assignments_f paf,
per_people_f ppf,
per_person_types ppt,
hr_locations hl1,
per_ca_ee_report_lines pert,
hr_lookups hl2,
hr_soft_coding_keyflex hsck,
per_pay_proposals_v2 pppv
where
upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and
upper(ltrim(rtrim(hl.lookup_code)))
= upper(ltrim(ltrim(pj.job_information1))) and
upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
--l_year_end between
-- paf.effective_start_date and
-- paf.effective_end_date and
ppf.effective_start_date between
paf.effective_start_date and
paf.effective_end_date and
(paf.employment_category is null OR
substr(paf.employment_category,1,2) not in ('FR','PR','PT'))and
paf.person_id=ppf.person_id and
--l_year_end between
-- ppf.effective_start_date and
-- ppf.effective_end_date and
ppf.effective_start_date < l_year_end and
ppf.effective_end_date > l_year_start and
ppf.person_type_id=ppt.person_type_id and
upper(ltrim(rtrim(ppt.system_person_type)))='EMP' and
ppf.business_group_id=p_business_group_id and
paf.location_id=hl1.location_id and
hl1.region_1=hl2.lookup_code and
hl2.lookup_type='CA_PROVINCE' and
pert.request_id=p_request_id and
hl2.meaning=pert.segment2 and
--pert.segment4 = 'Y' and
pert.context='FORM14' and
pert.segment1='PROVINCE' and
decode(desig,1,per_information5,
2,per_information6,
3,per_information7)='Y' and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code)
)
) and
pppv.assignment_id = paf.assignment_id and
pppv.approved = 'Y'and
pppv.change_date between l_year_start and
l_year_end and
pppv.proposal_reason =
(
select lookup_code
from hr_lookups
where lookup_type = 'PROPOSAL_REASON' and
upper(meaning) = 'PROMOTION'
)
)
group by province,employment_category,sex
order by province,employment_category,sex;
select
segment3,
sum(to_number(segment4)) segment4,
sum(to_number(segment5)) segment5,
sum(to_number(segment6)) segment6,
sum(to_number(segment7)) segment7,
sum(to_number(segment8)) segment8,
sum(to_number(segment9)) segment9,
sum(to_number(segment10)) segment10,
sum(to_number(segment11)) segment11,
sum(to_number(segment12)) segment12,
sum(to_number(segment13)) segment13,
sum(to_number(segment14)) segment14,
sum(to_number(segment15)) segment15
from
per_ca_ee_report_lines
where
request_id = p_request_id and
context = 'FORM5P' and
segment1 = 'PROVINCE' and
segment21 = v_naic_code
group by segment3;
select
segment2,
decode(emp_cat,1,'FR','2','PR',3,'PT') emp_category
from
per_ca_ee_report_lines
where
request_id = p_request_id and
segment3 <> '0' and
context = 'FORM14'
minus
select
segment2,
segment3
from
per_ca_ee_report_lines
where
request_id = p_request_id and
context = 'FORM5P' and
segment1 = 'PROVINCE' ;
insert into per_ca_ee_report_lines
(request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment21) values
( p_request_id,
per_ca_ee_extract_pkg.k,
'FORM5',
'PROVINCE',
v_province_name,
v_meaning,
v_employment_category,
nvl(v_count,0),
decode(v_sex,'F',v_count,0),
decode(v_sex,'M',v_count,0),
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
v_naic_code);
update per_ca_ee_report_lines set
segment7=nvl(v_count,0),
segment5=segment5 + nvl(v_count,0)
where request_id=p_request_id and
line_number = per_ca_ee_extract_pkg.k and
context='FORM5' and
segment1='PROVINCE' and
segment2=v_province_name and
segment3=v_meaning and
segment4=v_employment_category and
segment21=v_naic_code;
update per_ca_ee_report_lines set
segment6=nvl(v_count,0),
segment5=segment5 + nvl(v_count,0)
where request_id=p_request_id and
line_number = per_ca_ee_extract_pkg.k and
context='FORM5' and
segment1='PROVINCE' and
segment2=v_province_name and
segment3=v_meaning and
segment4=v_employment_category and
segment21=v_naic_code;
update per_ca_ee_report_lines set
segment8 = nvl(segment8,0) + nvl(v_count,0),
segment10 = nvl(v_count,0)
where
request_id = p_request_id and
context = 'FORM5' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_meaning and
segment4 = v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment8 = nvl(segment8,0) + nvl(v_count,0),
segment9 = nvl(v_count,0)
where
request_id = p_request_id and
context = 'FORM5' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_meaning and
segment4 = v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment11 = nvl(segment11,0) + nvl(v_count,0),
segment13 = nvl(v_count,0)
where
request_id = p_request_id and
context = 'FORM5' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_meaning and
segment4 = v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment11 = nvl(segment11,0) + nvl(v_count,0),
segment12 = nvl(v_count,0)
where
request_id = p_request_id and
context = 'FORM5' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_meaning and
segment4 = v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment14 = nvl(segment14,0) + nvl(v_count,0),
segment16 = nvl(v_count,0)
where
request_id = p_request_id and
context = 'FORM5' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_meaning and
segment4 = v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment14 = nvl(segment14,0) + nvl(v_count,0),
segment15 = nvl(v_count,0)
where
request_id = p_request_id and
context = 'FORM5' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_meaning and
segment4 = v_employment_category and
segment21 = v_naic_code;
insert into per_ca_ee_report_lines
(request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment21) values
( p_request_id,
per_ca_ee_extract_pkg.k,
'FORM5',
'PROVINCE',
x.segment2,
v_meaning,
x.emp_category,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
v_naic_code);
insert into per_ca_ee_report_lines
(request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment21) values
(p_request_id,
per_ca_ee_extract_pkg.k,
'FORM5P',
'PROVINCE',
v_province_name,
v_employment_category,
nvl(v_count,0),
decode(v_sex,'M',v_count,0),
decode(v_sex,'F',v_count,0),
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
v_naic_code);
update per_ca_ee_report_lines set
segment5=nvl(v_count,0),
segment4=segment4 + nvl(v_count,0)
where request_id=p_request_id and
line_number = per_ca_ee_extract_pkg.k and
context='FORM5P' and
segment1='PROVINCE' and
segment2=v_province_name and
segment3=v_employment_category and
segment21=v_naic_code;
update per_ca_ee_report_lines set
segment6=nvl(v_count,0),
segment4=segment4 + nvl(v_count,0)
where request_id=p_request_id and
line_number = per_ca_ee_extract_pkg.k and
context='FORM5P' and
segment1='PROVINCE' and
segment2=v_province_name and
segment3=v_employment_category and
segment21=v_naic_code;
update per_ca_ee_report_lines set
segment7 = nvl(segment7,0) + nvl(v_count,0),
segment8 = nvl(v_count,0)
where
request_id = p_request_id and
context = 'FORM5P' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment7 = nvl(segment7,0) + nvl(v_count,0),
segment9 = nvl(v_count,0)
where
request_id = p_request_id and
context = 'FORM5P' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment10 = nvl(segment10,0) + nvl(v_count,0),
segment11 = nvl(v_count,0)
where
request_id = p_request_id and
context = 'FORM5P' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment10 = nvl(segment10,0) + nvl(v_count,0),
segment12 = nvl(v_count,0)
where
request_id = p_request_id and
context = 'FORM5P' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment13 = nvl(segment13,0) + nvl(v_count,0),
segment14 = nvl(v_count,0)
where
request_id = p_request_id and
context = 'FORM5P' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment13 = nvl(segment13,0) + nvl(v_count,0),
segment15 = nvl(v_count,0)
where
request_id = p_request_id and
context = 'FORM5P' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_employment_category and
segment21 = v_naic_code;
insert into per_ca_ee_report_lines
(request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment21) values
(p_request_id,
per_ca_ee_extract_pkg.k,
'FORM5P',
'PROVINCE',
j.segment2,
j.emp_category,
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
v_naic_code);
insert into per_ca_ee_report_lines
(request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment21) values
(p_request_id,
per_ca_ee_extract_pkg.k,
'FORM5P',
'NATIONAL',
i.segment3,
i.segment4,
i.segment5,
i.segment6,
i.segment7,
i.segment8,
i.segment9,
i.segment10,
i.segment11,
i.segment12,
i.segment13,
i.segment14,
i.segment15,
-- i.segment16,
v_naic_code);
insert into per_ca_ee_report_lines
(request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment21) values
( p_request_id,
per_ca_ee_extract_pkg.k,
'FORM5',
'NATIONAL',
count_national.segment3,
count_national.segment4,
count_national.segment5,
count_national.segment6,
count_national.segment7,
count_national.segment8,
count_national.segment9,
count_national.segment10,
count_national.segment11,
count_national.segment12,
count_national.segment13,
count_national.segment14,
count_national.segment15,
count_national.segment16,
v_naic_code);
select pert.segment4 naic_code
from per_ca_ee_report_lines pert
where pert.request_id = p_request_id
and pert.context = 'FORM12';
select job_id,
meaning
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
sum(employee_total) employee_total,
meaning meaning,
sex sex,
employment_category employment_category,
province province
from
(
select
employee_promotions(paf.assignment_id,
paf.person_id,
p_business_group_id,
l_year_start,
l_year_end,
'Y') employee_total,
job_exists(paf.job_id) meaning,
ppf.sex sex,
substr(paf.employment_category,1,2) employment_category,
hl1.region_1 province
from
per_assignments_f paf,
per_people_f ppf,
hr_locations hl1,
per_ca_ee_report_lines pert,
hr_lookups hl2,
hr_soft_coding_keyflex hsck
where job_exists(paf.job_id) is not null
and paf.primary_flag = 'Y'
and paf.assignment_id =
(select max(pafm.assignment_id) -- This select ensures that
from per_assignments_f pafm -- for rehires only the last
where pafm.person_id = ppf.person_id -- assignment is used
and pafm.primary_flag = 'Y'
and pafm.business_group_id = p_business_group_id)
-- and ppf.start_date between
-- paf.effective_start_date and
-- paf.effective_end_date
and paf.effective_start_date <= l_year_end
and paf.effective_end_date >= l_year_start
and paf.effective_start_date =
(select max(paf_max.effective_start_date) -- The last assignment
from per_assignments_f paf_max -- in the year
where paf_max.assignment_id = paf.assignment_id
and paf_max.primary_flag = 'Y'
and paf_max.effective_start_date <= l_year_end
and paf_max.effective_end_date >= l_year_start
and paf_max.business_group_id = p_business_group_id)
and paf.employment_category is not null
and substr(paf.employment_category,1,2) in ('FR','PR','PT')
and paf.person_id = ppf.person_id
and ppf.effective_start_date <= l_year_end
and ppf.effective_end_date >= l_year_start
and ppf.effective_start_date =
(select max(ppf_max.effective_start_date) -- The last person
from per_people_f ppf_max -- record in the year
where ppf_max.person_id = ppf.person_id
and ppf_max.effective_start_date <= l_year_end
and ppf_max.effective_end_date >= l_year_start
and ppf_max.business_group_id = p_business_group_id
and person_type_exists(ppf_max.person_type_id) is not null)
and person_type_exists(ppf.person_type_id) is not null
and ppf.business_group_id = p_business_group_id
and paf.location_id = hl1.location_id
and hl1.region_1 = hl2.lookup_code
and hl2.lookup_type = 'CA_PROVINCE'
and pert.request_id = p_request_id
and hl2.meaning = pert.segment2
and pert.context = 'FORM14'
and pert.segment1 = 'PROVINCE'
and decode (desig, 0, 'Y',
1, per_information5,
2, per_information6,
3, per_information7) = 'Y'
and (
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id
and context = 'FORM13'
and segment1 = 'NAIC'
and segment2 = v_naic_code)
)
)
union all
select
employee_promotions(paf.assignment_id,
paf.person_id,
p_business_group_id,
l_year_start,
l_year_end,
'Y') employee_total,
job_exists(paf.job_id) meaning,
ppf.sex sex,
'FR' employment_category,
hl1.region_1 province
from
per_assignments_f paf,
per_people_f ppf,
hr_locations hl1,
per_ca_ee_report_lines pert,
hr_lookups hl2,
hr_soft_coding_keyflex hsck
where
job_exists(paf.job_id) is not null
and paf.primary_flag = 'Y'
and paf.assignment_id =
(select max(pafm.assignment_id) -- This select ensures that
from per_assignments_f pafm -- for rehires only the last
where pafm.person_id = ppf.person_id -- assignment is used
and pafm.primary_flag = 'Y'
and pafm.business_group_id = p_business_group_id)
-- and ppf.start_date between
-- paf.effective_start_date and
-- paf.effective_end_date
and paf.effective_start_date <= l_year_end
and paf.effective_end_date >= l_year_start
and paf.effective_start_date =
(select max(paf_max.effective_start_date)
from per_assignments_f paf_max
where paf_max.assignment_id = paf.assignment_id
and paf_max.primary_flag = 'Y'
and paf_max.effective_start_date <= l_year_end
and paf_max.effective_end_date >= l_year_start
and paf_max.business_group_id = p_business_group_id)
and (paf.employment_category is null OR
substr(paf.employment_category,1,2) not in ('FR','PR','PT'))
and paf.person_id = ppf.person_id
and ppf.effective_start_date <= l_year_end
and ppf.effective_end_date >= l_year_start
and ppf.effective_start_date =
(select max(ppf_max.effective_start_date) -- The last person
from per_people_f ppf_max -- record in the year
where ppf_max.person_id = ppf.person_id
and ppf_max.effective_start_date <= l_year_end
and ppf_max.effective_end_date >= l_year_start
and ppf_max.business_group_id = p_business_group_id
and person_type_exists(ppf_max.person_type_id) is not null)
and person_type_exists(ppf.person_type_id) is not null
and ppf.business_group_id = p_business_group_id
and paf.location_id = hl1.location_id
and hl1.region_1 = hl2.lookup_code
and hl2.lookup_type = 'CA_PROVINCE'
and pert.request_id = p_request_id
and hl2.meaning = pert.segment2
and pert.context = 'FORM14'
and pert.segment1 = 'PROVINCE'
and decode (desig, 0, 'Y',
1, per_information5,
2, per_information6,
3, per_information7) = 'Y'
and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in
(select segment3
from per_ca_ee_report_lines
where request_id = p_request_id
and context = 'FORM13'
and segment1 = 'NAIC'
and segment2 = v_naic_code)
)
)
)
group by province,meaning,employment_category,sex
order by province,meaning,employment_category,sex;
select meaning
from hr_lookups
where upper(ltrim(rtrim(lookup_type)))='CA_PROVINCE'
and upper(ltrim(rtrim(lookup_code)))=upper(ltrim(rtrim(v_province)));
select meaning
from hr_lookups
where lookup_type='EEOG';
select
segment2,
v_meaning,
decode(p_emp_cat,1,'FR',2,'PR',3,'PT') emp_category
from
per_ca_ee_report_lines where
request_id=p_request_id and
context='FORM14' and
segment1='PROVINCE' and
segment3 <> '0'
minus
select
segment2,
segment3,
segment4
from
per_ca_ee_report_lines
where
request_id=p_request_id and
context='FORM5' and
segment1='PROVINCE'and
segment21 = v_naic_code;
select
segment3,
segment4,
sum(to_number(segment5)) segment5,
sum(to_number(segment6)) segment6,
sum(to_number(segment7)) segment7,
sum(to_number(segment8)) segment8,
sum(to_number(segment9)) segment9,
sum(to_number(segment10)) segment10,
sum(to_number(segment11)) segment11,
sum(to_number(segment12)) segment12,
sum(to_number(segment13)) segment13,
sum(to_number(segment14)) segment14,
sum(to_number(segment15)) segment15,
sum(to_number(segment16)) segment16
from
per_ca_ee_report_lines
where
request_id = p_request_id and
context = 'FORM5' and
segment1 = 'PROVINCE' and
segment21 = v_naic_code
group by segment3,segment4;
select
sum(promotion_total) promotion_total,
sex sex,
employment_category employment_category,
province province
from
(
select
employee_promotions(paf.assignment_id,
paf.person_id,
p_business_group_id,
l_year_start,
l_year_end,
'N') promotion_total,
ppf.sex sex,
substr(paf.employment_category,1,2) employment_category,
hl1.region_1 province
from
per_assignments_f paf,
per_people_f ppf,
hr_locations hl1,
per_ca_ee_report_lines pert,
hr_lookups hl2,
hr_soft_coding_keyflex hsck
where
job_exists(paf.job_id) is not null
and paf.primary_flag = 'Y'
and paf.assignment_id =
(select max(pafm.assignment_id) -- This select ensures that
from per_assignments_f pafm -- for rehires only the last
where pafm.person_id = ppf.person_id -- assignment is used
and pafm.primary_flag = 'Y'
and pafm.business_group_id = p_business_group_id)
-- ppf.start_date between
-- paf.effective_start_date and
-- paf.effective_end_date and
and paf.effective_start_date <= l_year_end
and paf.effective_end_date >= l_year_start
and paf.effective_start_date =
(select max(paf_max.effective_start_date)
from per_assignments_f paf_max
where paf_max.assignment_id = paf.assignment_id
and paf_max.primary_flag = 'Y'
and paf_max.effective_start_date <= l_year_end
and paf_max.effective_end_date >= l_year_start
and paf_max.business_group_id = p_business_group_id)
and paf.employment_category is not null
and substr(paf.employment_category,1,2) in ('FR','PR','PT')
and paf.person_id=ppf.person_id
and ppf.effective_start_date <= l_year_end
and ppf.effective_end_date >= l_year_start
and ppf.effective_start_date =
(select max(ppf_max.effective_start_date) -- The last person
from per_people_f ppf_max -- record in the year
where ppf_max.person_id = ppf.person_id
and ppf_max.effective_start_date <= l_year_end
and ppf_max.effective_end_date >= l_year_start
and ppf_max.business_group_id = p_business_group_id
and person_type_exists(ppf_max.person_type_id) is not null)
and person_type_exists(ppf.person_type_id) is not null
and ppf.business_group_id=p_business_group_id
and paf.location_id=hl1.location_id
and hl1.region_1=hl2.lookup_code
and hl2.lookup_type='CA_PROVINCE'
and pert.request_id=p_request_id
and hl2.meaning=pert.segment2
and pert.context='FORM14'
and pert.segment1='PROVINCE'
and decode (desig, 0, 'Y',
1, per_information5,
2, per_information6,
3, per_information7) = 'Y'
and (
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code)
)
)
union all
select
employee_promotions(paf.assignment_id,
paf.person_id,
p_business_group_id,
l_year_start,
l_year_end,
'N') promotion_total,
ppf.sex sex,
'FR' employment_category,
hl1.region_1 province
from
per_assignments_f paf,
per_people_f ppf,
hr_locations hl1,
per_ca_ee_report_lines pert,
hr_lookups hl2,
hr_soft_coding_keyflex hsck
where
job_exists(paf.job_id) is not null
and paf.primary_flag = 'Y'
and paf.assignment_id =
(select max(pafm.assignment_id) -- This select ensures that
from per_assignments_f pafm -- for rehires only the last
where pafm.person_id = ppf.person_id -- assignment is used
and pafm.primary_flag = 'Y'
and pafm.business_group_id = p_business_group_id)
-- ppf.start_date between
-- paf.effective_start_date and
-- paf.effective_end_date and
and paf.effective_start_date <= l_year_end
and paf.effective_end_date >= l_year_start
and paf.effective_start_date =
(select max(paf_max.effective_start_date)
from per_assignments_f paf_max
where paf_max.assignment_id = paf.assignment_id
and paf_max.primary_flag = 'Y'
and paf_max.effective_start_date <= l_year_end
and paf_max.effective_end_date >= l_year_start
and paf_max.business_group_id = p_business_group_id)
and (paf.employment_category is null OR
substr(paf.employment_category,1,2) not in ('FR','PR','PT'))
and paf.person_id=ppf.person_id
and ppf.effective_start_date <= l_year_end
and ppf.effective_end_date >= l_year_start
and ppf.effective_start_date =
(select max(ppf_max.effective_start_date) -- The last person
from per_people_f ppf_max -- record in the year
where ppf_max.person_id = ppf.person_id
and ppf_max.effective_start_date <= l_year_end
and ppf_max.effective_end_date >= l_year_start
and ppf_max.business_group_id = p_business_group_id
and person_type_exists(ppf_max.person_type_id) is not null)
and person_type_exists(ppf.person_type_id) is not null
and ppf.business_group_id=p_business_group_id
and paf.location_id=hl1.location_id
and hl1.region_1=hl2.lookup_code
and hl2.lookup_type='CA_PROVINCE'
and pert.request_id=p_request_id
and hl2.meaning=pert.segment2
and pert.context='FORM14'
and pert.segment1='PROVINCE'
and decode (desig, 0, 'Y',
1, per_information5,
2, per_information6,
3, per_information7) = 'Y'
and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in
(select segment3
from per_ca_ee_report_lines
where request_id = p_request_id
and context = 'FORM13'
and segment1 = 'NAIC'
and segment2 = v_naic_code)
)
)
)
group by province,employment_category,sex
order by province,employment_category,sex;
select
segment3,
sum(to_number(segment4)) segment4,
sum(to_number(segment5)) segment5,
sum(to_number(segment6)) segment6,
sum(to_number(segment7)) segment7,
sum(to_number(segment8)) segment8,
sum(to_number(segment9)) segment9,
sum(to_number(segment10)) segment10,
sum(to_number(segment11)) segment11,
sum(to_number(segment12)) segment12,
sum(to_number(segment13)) segment13,
sum(to_number(segment14)) segment14,
sum(to_number(segment15)) segment15
from
per_ca_ee_report_lines
where
request_id = p_request_id and
context = 'FORM5P' and
segment1 = 'PROVINCE' and
segment21 = v_naic_code
group by segment3;
select
segment2,
decode(emp_cat,1,'FR','2','PR',3,'PT') emp_category
from
per_ca_ee_report_lines
where
request_id = p_request_id and
segment3 <> '0' and
context = 'FORM14'
minus
select
segment2,
segment3
from
per_ca_ee_report_lines
where
request_id = p_request_id and
context = 'FORM5P' and
segment1 = 'PROVINCE' ;
insert into per_ca_ee_report_lines
(request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment21) values
( p_request_id,
per_ca_ee_extract_pkg.k,
'FORM5',
'PROVINCE',
v_province_name,
v_meaning,
v_employment_category,
nvl(v_count,0),
decode(v_sex,'F',v_count,0),
decode(v_sex,'M',v_count,0),
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
v_naic_code);
update per_ca_ee_report_lines set
segment7=nvl(v_count,0),
segment5=segment5 + nvl(v_count,0)
where request_id=p_request_id and
line_number = per_ca_ee_extract_pkg.k and
context='FORM5' and
segment1='PROVINCE' and
segment2=v_province_name and
segment3=v_meaning and
segment4=v_employment_category and
segment21=v_naic_code;
update per_ca_ee_report_lines set
segment6=nvl(v_count,0),
segment5=segment5 + nvl(v_count,0)
where request_id=p_request_id and
line_number = per_ca_ee_extract_pkg.k and
context='FORM5' and
segment1='PROVINCE' and
segment2=v_province_name and
segment3=v_meaning and
segment4=v_employment_category and
segment21=v_naic_code;
update per_ca_ee_report_lines set
segment8 = nvl(segment8,0) + nvl(v_count,0),
segment10 = nvl(v_count,0)
where
request_id = p_request_id and
context = 'FORM5' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_meaning and
segment4 = v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment8 = nvl(segment8,0) + nvl(v_count,0),
segment9 = nvl(v_count,0)
where
request_id = p_request_id and
context = 'FORM5' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_meaning and
segment4 = v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment11 = nvl(segment11,0) + nvl(v_count,0),
segment13 = nvl(v_count,0)
where
request_id = p_request_id and
context = 'FORM5' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_meaning and
segment4 = v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment11 = nvl(segment11,0) + nvl(v_count,0),
segment12 = nvl(v_count,0)
where
request_id = p_request_id and
context = 'FORM5' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_meaning and
segment4 = v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment14 = nvl(segment14,0) + nvl(v_count,0),
segment16 = nvl(v_count,0)
where
request_id = p_request_id and
context = 'FORM5' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_meaning and
segment4 = v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment14 = nvl(segment14,0) + nvl(v_count,0),
segment15 = nvl(v_count,0)
where
request_id = p_request_id and
context = 'FORM5' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_meaning and
segment4 = v_employment_category and
segment21 = v_naic_code;
insert into per_ca_ee_report_lines
(request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment21) values
( p_request_id,
per_ca_ee_extract_pkg.k,
'FORM5',
'PROVINCE',
x.segment2,
v_meaning,
x.emp_category,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
v_naic_code);
insert into per_ca_ee_report_lines
(request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment21) values
(p_request_id,
per_ca_ee_extract_pkg.k,
'FORM5P',
'PROVINCE',
v_province_name,
v_employment_category,
nvl(v_count,0),
decode(v_sex,'M',v_count,0),
decode(v_sex,'F',v_count,0),
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
v_naic_code);
update per_ca_ee_report_lines set
segment5=nvl(v_count,0),
segment4=segment4 + nvl(v_count,0)
where request_id=p_request_id and
line_number = per_ca_ee_extract_pkg.k and
context='FORM5P' and
segment1='PROVINCE' and
segment2=v_province_name and
segment3=v_employment_category and
segment21=v_naic_code;
update per_ca_ee_report_lines set
segment6=nvl(v_count,0),
segment4=segment4 + nvl(v_count,0)
where request_id=p_request_id and
line_number = per_ca_ee_extract_pkg.k and
context='FORM5P' and
segment1='PROVINCE' and
segment2=v_province_name and
segment3=v_employment_category and
segment21=v_naic_code;
update per_ca_ee_report_lines set
segment7 = nvl(segment7,0) + nvl(v_count,0),
segment8 = nvl(v_count,0)
where
request_id = p_request_id and
context = 'FORM5P' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment7 = nvl(segment7,0) + nvl(v_count,0),
segment9 = nvl(v_count,0)
where
request_id = p_request_id and
context = 'FORM5P' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment10 = nvl(segment10,0) + nvl(v_count,0),
segment11 = nvl(v_count,0)
where
request_id = p_request_id and
context = 'FORM5P' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment10 = nvl(segment10,0) + nvl(v_count,0),
segment12 = nvl(v_count,0)
where
request_id = p_request_id and
context = 'FORM5P' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment13 = nvl(segment13,0) + nvl(v_count,0),
segment14 = nvl(v_count,0)
where
request_id = p_request_id and
context = 'FORM5P' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment13 = nvl(segment13,0) + nvl(v_count,0),
segment15 = nvl(v_count,0)
where
request_id = p_request_id and
context = 'FORM5P' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_employment_category and
segment21 = v_naic_code;
insert into per_ca_ee_report_lines
(request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment21) values
(p_request_id,
per_ca_ee_extract_pkg.k,
'FORM5P',
'PROVINCE',
j.segment2,
j.emp_category,
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
v_naic_code);
insert into per_ca_ee_report_lines
(request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment21) values
(p_request_id,
per_ca_ee_extract_pkg.k,
'FORM5P',
'NATIONAL',
i.segment3,
i.segment4,
i.segment5,
i.segment6,
i.segment7,
i.segment8,
i.segment9,
i.segment10,
i.segment11,
i.segment12,
i.segment13,
i.segment14,
i.segment15,
v_naic_code);
insert into per_ca_ee_report_lines
(request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment21) values
( p_request_id,
per_ca_ee_extract_pkg.k,
'FORM5',
'NATIONAL',
count_national.segment3,
count_national.segment4,
count_national.segment5,
count_national.segment6,
count_national.segment7,
count_national.segment8,
count_national.segment9,
count_national.segment10,
count_national.segment11,
count_national.segment12,
count_national.segment13,
count_national.segment14,
count_national.segment15,
count_national.segment16,
v_naic_code);
select
pert.segment4 naic_code
from
per_ca_ee_report_lines pert
where
pert.request_id = p_request_id and
pert.context = 'FORM12';
select
count(distinct count_total) count_total,
meaning meaning,
sex sex,
employment_category employment_category,
region_1 region_1
from
(
select
paf.person_id count_total,
hl.meaning meaning,
ppf.sex sex,
substr(paf.employment_category,1,2) employment_category,
hl1.region_1 region_1
from
hr_lookups hl,
per_jobs pj,
per_assignments_f paf,
per_people_f ppf,
per_person_types ppt,
per_periods_of_service ppos,
hr_locations hl1,
hr_soft_coding_keyflex hsck
where
hl.lookup_type='EEOG' and
upper(ltrim(rtrim(hl.lookup_code)))
= upper(ltrim(rtrim(pj.job_information1))) and
upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
paf.employment_category is not null and
substr(paf.employment_category,1,2) in ('FR','PR','PT') and
--decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
ppos.actual_termination_date between
paf.effective_start_date and
paf.effective_end_date and
paf.location_id=hl1.location_id and
paf.person_id=ppf.person_id and
--decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
ppos.actual_termination_date between
ppf.effective_start_date and
ppf.effective_end_date and
ppf.person_type_id = ppt.person_type_id and
--upper(ltrim(rtrim(ppt.system_person_type)))='EX_EMP' and
ppf.business_group_id=p_business_group_id and
ppf.person_id=ppos.person_id and
ppos.actual_termination_date is not null and
ppos.actual_termination_date >= l_year_start and
ppos.actual_termination_date <= l_year_end and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code)
)
) and
exists
(
select 'X'
from per_pay_proposals_v2 pppv
where
pppv.assignment_id = paf.assignment_id and
pppv.approved = 'Y' and
pppv.change_date <= l_year_end
)
union all
select
paf.person_id count_total,
hl.meaning meaning,
ppf.sex sex,
'FR' employment_category,
hl1.region_1 region_1
from
hr_lookups hl,
per_jobs pj,
per_assignments_f paf,
per_people_f ppf,
per_person_types ppt,
per_periods_of_service ppos,
hr_locations hl1,
hr_soft_coding_keyflex hsck
where
hl.lookup_type='EEOG' and
upper(ltrim(rtrim(hl.lookup_code)))
= upper(ltrim(rtrim(pj.job_information1))) and
upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
--l_year_end between
ppos.actual_termination_date between
paf.effective_start_date and
paf.effective_end_date and
(paf.employment_category is null OR
substr(paf.employment_category,1,2) in ('FR','PR','PT')) and
paf.location_id=hl1.location_id and
paf.person_id=ppf.person_id and
--l_year_end between
ppos.actual_termination_date between
ppf.effective_start_date and
ppf.effective_end_date and
ppf.person_type_id = ppt.person_type_id and
--upper(ltrim(rtrim(ppt.system_person_type)))='EX_EMP' and
ppf.business_group_id=p_business_group_id and
ppf.person_id=ppos.person_id and
ppos.actual_termination_date is not null and
ppos.actual_termination_date >= l_year_start and
ppos.actual_termination_date <= l_year_end and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code)
)
) and
exists
(
select 'X'
from per_pay_proposals_v2 pppv
where
pppv.assignment_id = paf.assignment_id and
pppv.approved = 'Y' and
pppv.change_date <= l_year_end
)
)
group by region_1,meaning,employment_category,sex
order by region_1,meaning,employment_category,sex;
select
meaning
from
hr_lookups
where
lookup_type='CA_PROVINCE' and
lookup_code=v_region_1;
select
count(distinct person_id) count,
meaning meaning,
employment_category employment_category,
sex sex,
region_1 region_1
from
(
select
paf.person_id person_id,
hl.meaning meaning,
substr(paf.employment_category,1,2) employment_category,
ppf.sex sex,
hl1.region_1 region_1
from
hr_lookups hl,
per_jobs pj,
per_assignments_f paf,
per_people_f ppf,
per_person_types ppt,
per_periods_of_service ppos ,
hr_locations hl1,
hr_soft_coding_keyflex hsck
where
upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and
upper(ltrim(rtrim(hl.lookup_code)))
= upper(ltrim(ltrim(pj.job_information1))) and
upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
--decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
ppos.actual_termination_date between
paf.effective_start_date and
paf.effective_end_date and
paf.employment_category is not null and
substr(paf.employment_category,1,2) in ('FR','PR','PT') and
paf.location_id=hl1.location_id and
paf.person_id=ppf.person_id and
--decode(paf.employment_category,'PT',p_date_tmp_emp,l_year_end) between
ppos.actual_termination_date between
ppf.effective_start_date and
ppf.effective_end_date and
ppf.person_type_id = ppt.person_type_id and
--UPPER(LTRIM(RTRIM(ppt.system_person_type)))='EX_EMP' and
ppf.business_group_id=p_business_group_id and
ppf.person_id=ppos.person_id and
ppos.actual_termination_date is not null and
ppos.actual_termination_date >= l_year_start and
ppos.actual_termination_date <= l_year_end and
decode(desig,1,per_information5,
2,per_information6,
3,per_information7)='Y' and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code)
)
) and
exists
(
select 'X'
from per_pay_proposals_v2 pppv
where
pppv.assignment_id = paf.assignment_id and
pppv.approved = 'Y' and
pppv.change_date <= l_year_end
)
union all
select
paf.person_id person_id,
hl.meaning meaning,
'FR' employment_category,
ppf.sex sex,
hl1.region_1 region_1
from
hr_lookups hl,
per_jobs pj,
per_assignments_f paf,
per_people_f ppf,
per_person_types ppt,
per_periods_of_service ppos ,
hr_locations hl1,
hr_soft_coding_keyflex hsck
where
upper(ltrim(rtrim(hl.lookup_type)))='EEOG' and
upper(ltrim(rtrim(hl.lookup_code)))
= upper(ltrim(ltrim(pj.job_information1))) and
upper(ltrim(rtrim(pj.job_information_category))) = 'CA' and
pj.job_id=paf.job_id and
paf.primary_flag = 'Y' and
--l_year_end between
ppos.actual_termination_date between
paf.effective_start_date and
paf.effective_end_date and
(paf.employment_category is null OR
substr(paf.employment_category,1,2) in ('FR','PR','PT')) and
paf.location_id=hl1.location_id and
paf.person_id=ppf.person_id and
--l_year_end between
ppos.actual_termination_date between
ppf.effective_start_date and
ppf.effective_end_date and
ppf.person_type_id = ppt.person_type_id and
--UPPER(LTRIM(RTRIM(ppt.system_person_type)))='EX_EMP' and
ppf.business_group_id=p_business_group_id and
ppf.person_id=ppos.person_id and
ppos.actual_termination_date is not null and
ppos.actual_termination_date >= l_year_start and
ppos.actual_termination_date <= l_year_end and
decode(desig,1,per_information5,
2,per_information6,
3,per_information7)='Y' and
(
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is not null and
hsck.segment6 = v_naic_code
)
OR
(
hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id and
hsck.segment6 is null and
hsck.segment1 in (select segment3
from per_ca_ee_report_lines
where request_id = p_request_id and
context = 'FORM13' and
segment1 = 'NAIC' and
segment2 = v_naic_code)
)
) and
exists
(
select 'X'
from per_pay_proposals_v2 pppv
where
pppv.assignment_id = paf.assignment_id and
pppv.approved = 'Y' and
pppv.change_date <= l_year_end
)
)
group by region_1,meaning,employment_category,sex
order by region_1,meaning,employment_category,sex;
select
meaning
from
hr_lookups
where
lookup_type='EEOG';
select
segment2,
v_meaning,
decode(p_emp_cat,1,'FR',2,'PR',3,'PT') emp_category
from
per_ca_ee_report_lines where
request_id=p_request_id and
context='FORM14' and
segment1='PROVINCE' and
segment3 <> '0'
minus
select
segment2,
segment3,
segment4
from
per_ca_ee_report_lines
where
request_id=p_request_id and
context='FORM6' and
segment1='PROVINCE'and
segment21 = v_naic_code;
select
segment3,
segment4,
sum(to_number(segment5)) segment5,
sum(to_number(segment6)) segment6,
sum(to_number(segment7)) segment7,
sum(to_number(segment8)) segment8,
sum(to_number(segment9)) segment9,
sum(to_number(segment10)) segment10,
sum(to_number(segment11)) segment11,
sum(to_number(segment12)) segment12,
sum(to_number(segment13)) segment13,
sum(to_number(segment14)) segment14,
sum(to_number(segment15)) segment15,
sum(to_number(segment16)) segment16
from
per_ca_ee_report_lines
where
request_id = p_request_id and
context = 'FORM6' and
segment1 = 'PROVINCE' and
segment21 = v_naic_code
group by segment3,segment4;
insert into per_ca_ee_report_lines
(request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment21
) values
(p_request_id,
k,
'FORM6',
'PROVINCE',
v_province_name,
v_meaning,
v_employment_category,
nvl(v_count,0),
decode(v_sex,'F',v_count,0),
decode(v_sex,'M',v_count,0),
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
'0',
v_naic_code
);
update per_ca_ee_report_lines set
segment7=nvl(v_count,0),
segment5=segment5 + nvl(v_count,0)
where request_id=p_request_id and
line_number = per_ca_ee_extract_pkg.k and
context='FORM5' and
segment1='PROVINCE' and
segment2=v_province_name and
segment3=v_meaning and
segment4=v_employment_category and
segment21=v_naic_code;
update per_ca_ee_report_lines set
segment6=nvl(v_count,0),
segment5=segment5 + nvl(v_count,0)
where request_id=p_request_id and
line_number = per_ca_ee_extract_pkg.k and
context='FORM5' and
segment1='PROVINCE' and
segment2=v_province_name and
segment3=v_meaning and
segment4=v_employment_category and
segment21=v_naic_code;
update per_ca_ee_report_lines set
segment8 = nvl(segment8,0) + nvl(v_count,0),
segment9 = 0,
segment10 = nvl(v_count,0)
where
request_id = p_request_id and
context = 'FORM6' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_meaning and
segment3 = v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment8 = nvl(segment8,0) + nvl(v_count,0),
segment9 = nvl(v_count,0),
segment10 = 0
where
request_id = p_request_id and
context='FORM6' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_meaning and
segment3 = v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment11 = nvl(segment11,0) + nvl(v_count,0),
segment12 = 0,
segment13 = nvl(v_count,0)
where
request_id = p_request_id and
context='FORM6' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_meaning and
segment3 = v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment11 = nvl(segment11,0) + nvl(v_count,0),
segment12 = nvl(v_count,0),
segment13 = 0
where
request_id = p_request_id and
context='FORM6' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_meaning and
segment3 = v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment14 = nvl(segment14,0) + nvl(v_count,0),
segment15 = 0,
segment16 = nvl(v_count,0)
where
request_id = p_request_id and
context='FORM6' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_meaning and
segment3 = v_employment_category and
segment21 = v_naic_code;
update per_ca_ee_report_lines set
segment14 = nvl(segment14,0) + nvl(v_count,0),
segment15 = nvl(v_count,0),
segment16 = 0
where
request_id = p_request_id and
context='FORM6' and
segment1 = 'PROVINCE' and
segment2 = v_province_name and
segment3 = v_meaning and
segment3 = v_employment_category and
segment21 = v_naic_code;
insert into per_ca_ee_report_lines
(request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment21) values
( p_request_id,
per_ca_ee_extract_pkg.k,
'FORM6',
'PROVINCE',
x.segment2,
v_meaning,
x.emp_category,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
v_naic_code);
insert into per_ca_ee_report_lines
(request_id,
line_number,
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment21) values
( p_request_id,
per_ca_ee_extract_pkg.k,
'FORM6',
'NATIONAL',
count_national.segment3,
count_national.segment4,
count_national.segment5,
count_national.segment6,
count_national.segment7,
count_national.segment8,
count_national.segment9,
count_national.segment10,
count_national.segment11,
count_national.segment12,
count_national.segment13,
count_national.segment14,
count_national.segment15,
count_national.segment16,
v_naic_code);
function update_rec(p_request_id number) return number is
begin
declare
cursor cur_temp_count is select
segment13,
segment14,
segment15
from
per_ca_ee_report_lines
where
request_id=p_request_id and
context='FORM11';
select
segment4 max_naic_code,
max(to_number(segment3))
from
per_ca_ee_report_lines
where
request_id = p_request_id and
context = 'FORM12' and
segment1 = 'NAIC'
group by segment4;
select
segment4 naic_code
from
per_ca_ee_report_lines
where request_id = p_request_id
and to_number(segment3) < (select to_number(lookup_code)
from pay_ca_legislation_info
where lookup_type = 'EER1')
and context = 'FORM12' and
segment4 <> v_max_naic_code;
select
context,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20
segment21
from per_ca_ee_report_lines
where
request_id = p_request_id and
context in ('FORM3','FORM4','FORM5','FORM6') and
segment1 = 'NATIONAL' and
segment21 = v_not_max_naic;
hr_utility.trace('Function update_rec starts here !!!!');
update per_ca_ee_report_lines set
segment20 = 'Y'
WHERE
request_id=p_request_id and
context in ('FORM2','FORM3','FORM4','FORM5','FORM6');
update per_ca_ee_report_lines set
segment20 = decode(segment3,'PT','N','Y')
WHERE
request_id=p_request_id and
context in ('FORM2','FORM3','FORM4','FORM5','FORM6') and
segment1 = 'NATIONAL';
update per_ca_ee_report_lines set
segment20 = decode(segment4,'PT','N','Y')
WHERE
request_id=p_request_id and
context in ('FORM2','FORM3','FORM4','FORM5','FORM6') and
segment1 in ('CMA','PROVINCE');
hr_utility.trace('UPDATE_REC: v_max_naic_code: ' || v_max_naic_code);
hr_utility.trace('UPDATE_REC: v_not_max_naic: ' || v_not_max_naic);
hr_utility.trace('UPDATE_REC: Form3 - 6' );
update per_ca_ee_report_lines
set
segment4 = segment4 + i.segment4,
segment5 = segment5 + i.segment5,
segment6 = segment6 + i.segment6,
segment7 = segment7 + i.segment7,
segment8 = segment8 + i.segment8,
segment9 = segment9 + i.segment9,
segment10 = segment10 + i.segment10,
segment11 = segment11 + i.segment11,
segment12 = segment12 + i.segment12,
segment13 = segment13 + i.segment13,
segment14 = segment14 + i.segment14,
segment15 = segment15 + i.segment15
where
request_id = p_request_id and
context = i.context and
segment1 = i.segment1 and
segment2 = i.segment2 and
segment3 = i.segment3 and
segment21 = v_max_naic_code;
hr_utility.trace('UPDATE_REC: Form3 - 6 End' );
delete from per_ca_ee_report_lines
where request_id = p_request_id and
segment21 = v_not_max_naic;
end update_rec;