[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
-- In Order to indexed columns we are inserting session id into tax unit id
--
INSERT INTO pay_us_rpt_totals(tax_unit_id
,attribute1
,attribute3
,value1
,value2
,value3
,value4
,value5
,value6
,value7
,value8
,value9
,value10
,value11
,value12
,value13
,value14
,value15
,value16
,value17
,value18
,value19
,value20
,value21
,value22
,value30
)
SELECT userenv('sessionid')
,'DGIPEDA'
,org_med_type
,DECODE(tenure_status,'01',tenure_status,'02') tenure
,academic_rank
,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
,SUM(DECODE(gender,'M',1,0)) totmen
,SUM(DECODE(gender,'F',1,0)) totwmen
,ipeds_category
FROM(
SELECT peo.sex gender
,job.job_information8 ipeds_category
,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
,ppet.pei_information1 tenure_status
,ppea.pei_information1 academic_rank
,peo.person_id
,pqh_inst_type_pkg.get_inst_type(asg.organization_id) org_med_type
FROM per_all_people_f peo
,per_all_assignments_f asg
,per_assignment_status_types ast
,per_jobs job
,per_pay_proposals ppp
,per_pay_bases ppb
,per_people_extra_info ppea
,per_people_extra_info ppet
WHERE peo.person_id = asg.person_id
AND peo.person_id = ppea.person_id
AND peo.person_id = ppet.person_id
AND ppea.information_type = 'PQH_ACADEMIC_RANK'
AND ppet.information_type = 'PQH_TENURE_STATUS'
AND p_report_date BETWEEN fnd_date.canonical_to_date(ppea.PEI_INFORMATION2)
and nvl(fnd_date.canonical_to_date(ppea.PEI_INFORMATION3),p_report_date) --16208130
AND ppea.pei_information1 IS NOT NULL
AND ppet.pei_information1 IN ('01','02','04')
AND peo.current_employee_flag = 'Y'
AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
AND job.business_group_id = p_business_group_id
AND job.job_information_category = 'US'
AND job.job_information8 IN ('21', '22', '23','24')
AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.primary_flag = 'Y'
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status <> 'TERM_ASSIGN'
AND asg.pay_basis_id = ppb.pay_basis_id
AND asg.assignment_id = ppp.assignment_id
AND ppp.change_date = (SELECT MAX(change_date)
FROM per_pay_proposals pro
WHERE ppp.assignment_id = pro.assignment_id
AND pro.change_date <= p_report_date
AND pro.approved = 'Y'
)
AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
AND asg.job_id = job.job_id
AND asg.assignment_type = 'E'
AND peo.business_group_id = p_business_group_id
AND EXISTS (SELECT 'Y'
FROM hr_all_organization_units hou
WHERE hou.business_group_id = p_business_group_id
AND hou.organization_id = asg.organization_id)
)
GROUP BY DECODE(tenure_status,'01',tenure_status,'02')
,academic_rank
,ipeds_category
,org_med_type;
-- In Order to indexed columns we are inserting session id into tax unit id
--
INSERT INTO pay_us_rpt_totals(tax_unit_id
,attribute1
,attribute3
,value1
,value2
,value3
,value4
,value5
,value6
,value7
,value8
,value9
,value10
,value11
,value12
,value13
,value14
,value15
,value16
,value17
,value18
,value19
,value20
,value21
,value22
,value30
)
SELECT userenv('sessionid')
,'DGIPEDA'
,org_med_type
,'03'||DECODE(SIGN(cont_dur_mon-12),1,0,0,1,2) tenure
,academic_rank
,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
,SUM(DECODE(gender,'M',1,0)) TotMen
,SUM(DECODE(gender,'F',1,0)) TotWmen
,ipeds_category
FROM (
SELECT peo.sex gender
,job.job_information8 ipeds_category
,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
,ppet.pei_information1 tenure_status
,ppea.pei_information1 academic_rank
,pqh_employment_category.get_duration_in_months(pco.duration,pco.duration_units,pco.business_group_id,p_report_date) cont_dur_mon
,peo.person_id
,pqh_inst_type_pkg.get_inst_type(asg.organization_id) org_med_type
FROM per_all_people_f peo
,per_all_assignments_f asg
,per_assignment_status_types ast
,per_jobs job
,per_pay_proposals ppp
,per_pay_bases ppb
,per_people_extra_info ppea
,per_people_extra_info ppet
,per_contracts_f pco
,per_shared_types pst
,per_shared_types pst1
WHERE peo.person_id = asg.person_id
AND peo.person_id = ppea.person_id
AND peo.person_id = ppet.person_id
AND ppea.information_type = 'PQH_ACADEMIC_RANK'
AND ppet.information_type = 'PQH_TENURE_STATUS'
AND p_report_date BETWEEN fnd_date.canonical_to_date(ppea.PEI_INFORMATION2)
and nvl(fnd_date.canonical_to_date(ppea.PEI_INFORMATION3),p_report_date) --16208130
AND ppea.pei_information1 IS NOT NULL
AND ppet.pei_information1 IN ('03','05')
AND peo.current_employee_flag = 'Y'
AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
AND job.business_group_id = p_business_group_id
AND job.job_information_category = 'US'
AND job.job_information8 IN ('21', '22', '23','24')
AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.primary_flag = 'Y'
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status <> 'TERM_ASSIGN'
AND asg.pay_basis_id = ppb.pay_basis_id
AND asg.assignment_id = ppp.assignment_id
AND ppp.change_date = (SELECT MAX(change_date)
FROM per_pay_proposals pro
WHERE ppp.assignment_id = pro.assignment_id
AND pro.change_date <= p_report_date
AND pro.approved = 'Y'
)
AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
AND asg.job_id = job.job_id
AND asg.assignment_type = 'E'
AND peo.business_group_id = p_business_group_id
AND pco.person_id = peo.person_id
AND pco.type = 'FULL_TIME'
AND pco.status = pst.system_type_cd
AND pst.lookup_type = 'CONTRACT_STATUS'
AND pst1.system_type_cd(+) = pst.system_type_cd
AND pst1.lookup_type(+) = pst.lookup_type
AND pst.business_group_id IS NULL
AND pst1.business_group_id(+) = p_business_group_id
AND NVL(pst1.information1,pst.information1) = 'Y'
AND EXISTS (SELECT 'Y'
FROM hr_all_organization_units hou
WHERE hou.business_group_id = p_business_group_id
AND hou.organization_id = asg.organization_id)
)
GROUP BY '03'||DECODE(SIGN(cont_dur_mon-12),1,0,0,1,2)
,academic_rank
,ipeds_category
,org_med_type;
--In Order to indexed columns we are inserting session id into tax unit id
--
INSERT INTO pay_us_rpt_totals(tax_unit_id
,attribute1
,attribute3
,value1
,value2
,value3
,value4
,value5
,value6
,value7
,value8
,value9
,value10
,value11
,value12
,value13
,value14
,value15
,value16
,value17
,value18
,value19
,value20
,value21
,value22
,value30
)
SELECT userenv('sessionid')
,'DGIPEDA'
,org_med_type
,'4' tenure
,NULL
,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
,SUM(DECODE(gender,'M',1,0)) TotMen
,SUM(DECODE(gender,'F',1,0)) TotWmen
,ipeds_category
FROM (
SELECT peo.sex gender
,job.job_information8 ipeds_category
,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
,peo.person_id
,pqh_inst_type_pkg.get_inst_type(asg.organization_id) org_med_type
FROM per_all_people_f peo
,per_all_assignments_f asg
,per_assignment_status_types ast
,per_jobs job
,per_pay_proposals ppp
,per_pay_bases ppb
WHERE peo.person_id = asg.person_id
AND peo.current_employee_flag = 'Y'
AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
AND job.business_group_id = p_business_group_id
AND job.job_information_category = 'US'
AND job.job_information8 IN ('21', '22', '23','24')
AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.primary_flag = 'Y'
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status <> 'TERM_ASSIGN'
AND asg.pay_basis_id = ppb.pay_basis_id
AND asg.assignment_id = ppp.assignment_id
AND ppp.change_date = (SELECT MAX(change_date)
FROM per_pay_proposals pro
WHERE ppp.assignment_id = pro.assignment_id
AND pro.change_date <= p_report_date
AND pro.approved = 'Y'
)
AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
AND asg.job_id = job.job_id
AND asg.assignment_type = 'E'
AND peo.business_group_id = p_business_group_id
AND EXISTS (SELECT 'Y'
FROM hr_all_organization_units hou
WHERE hou.business_group_id = p_business_group_id
AND hou.organization_id = asg.organization_id)
AND NOT EXISTS (SELECT 'Y'
FROM per_people_extra_info ppet
WHERE ppet.person_id = peo.person_id
AND ppet.information_type = 'PQH_TENURE_STATUS')
)
GROUP BY '4'
,ipeds_category
,org_med_type;
-- In Order to indexed columns we are inserting session id into tax unit id
--
INSERT INTO pay_us_rpt_totals(tax_unit_id
,attribute1
,attribute3
,value1
,value2
,value3
,value4
,value5
,value6
,value7
,value8
,value9
,value10
,value11
,value12
,value13
,value14
,value15
,value16
,value17
,value18
,value19
,value20
,value21
,value22
,value30
)
SELECT USERENV('sessionid')
,'DGIPEDA'
,org_med_type
,'03'||DECODE(SIGN(cont_dur_mon-12),1,0,0,1,2) tenure
,academic_rank
,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
,SUM(DECODE(gender,'M',1,0)) TotMen
,SUM(DECODE(gender,'F',1,0)) TotWmen
,ipeds_category
FROM (
SELECT peo.sex gender
,job.job_information8 ipeds_category
,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
,ppea.pei_information1 academic_rank
,pqh_employment_category.get_duration_in_months(pco.duration,pco.duration_units,pco.business_group_id,p_report_date) cont_dur_mon
,peo.person_id
,pqh_inst_type_pkg.get_inst_type(asg.organization_id) org_med_type
FROM per_all_people_f peo
,per_all_assignments_f asg
,per_assignment_status_types ast
,per_jobs job
,per_pay_proposals ppp
,per_pay_bases ppb
,per_people_extra_info ppea
,per_contracts_f pco
,per_shared_types pst
,per_shared_types pst1
WHERE peo.person_id = asg.person_id
AND peo.person_id = ppea.person_id
AND ppea.information_type = 'PQH_ACADEMIC_RANK'
AND p_report_date BETWEEN fnd_date.canonical_to_date(ppea.PEI_INFORMATION2)
and nvl(fnd_date.canonical_to_date(ppea.PEI_INFORMATION3),p_report_date) --16208130
AND ppea.pei_information1 IS NOT NULL
AND peo.current_employee_flag = 'Y'
AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
AND job.business_group_id = p_business_group_id
AND job.job_information_category = 'US'
AND job.job_information8 IN ('21', '22', '23','24')
AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.primary_flag = 'Y'
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status <> 'TERM_ASSIGN'
AND asg.pay_basis_id = ppb.pay_basis_id
AND asg.assignment_id = ppp.assignment_id
AND ppp.change_date = (SELECT MAX(change_date)
FROM per_pay_proposals pro
WHERE ppp.assignment_id = pro.assignment_id
AND pro.change_date <= p_report_date
AND pro.approved = 'Y'
)
AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
AND asg.job_id = job.job_id
AND asg.assignment_type = 'E'
AND peo.business_group_id = p_business_group_id
AND pco.person_id = peo.person_id
AND pco.type = 'FULL_TIME'
AND pco.status = pst.system_type_cd
AND pst.lookup_type = 'CONTRACT_STATUS'
AND pst1.system_type_cd(+) = pst.system_type_cd
AND pst1.lookup_type(+) = pst.lookup_type
AND pst.business_group_id IS NULL
AND pst1.business_group_id(+) = p_business_group_id
AND NVL(pst1.information1,pst.information1) = 'Y'
AND EXISTS (SELECT 'Y'
FROM hr_all_organization_units hou
WHERE hou.business_group_id = p_business_group_id
AND hou.organization_id = asg.organization_id)
)
GROUP BY '03'||DECODE(SIGN(cont_dur_mon-12),1,0,0,1,2)
,academic_rank
,ipeds_category
,org_med_type;
-- In Order to indexed columns we are inserting session id into tax unit id
--
INSERT INTO pay_us_rpt_totals(tax_unit_id
,attribute1
,attribute3
,value1
,value2
,value3
,value4
,value5
,value6
,value7
,value8
,value9
,value10
,value11
,value12
,value13
,value14
,value15
,value16
,value17
,value18
,value19
,value20
,value21
,value22
,value30
)
SELECT userenv('sessionid')
,'DGIPEDA'
,org_med_type
,'4' tenure
,NULL
,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
,SUM(DECODE(gender,'M',1,0)) TotMen
,SUM(DECODE(gender,'F',1,0)) TotWmen
,ipeds_category
FROM (
SELECT peo.sex gender
,job.job_information8 ipeds_category
,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
,peo.person_id
,pqh_inst_type_pkg.get_inst_type(asg.organization_id) org_med_type
FROM per_all_people_f peo
,per_all_assignments_f asg
,per_assignment_status_types ast
,per_jobs job
,per_pay_proposals ppp
,per_pay_bases ppb
WHERE peo.person_id = asg.person_id
AND peo.current_employee_flag = 'Y'
AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
AND job.business_group_id = p_business_group_id
AND job.job_information_category = 'US'
AND job.job_information8 IN ('21', '22', '23','24')
AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.primary_flag = 'Y'
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status <> 'TERM_ASSIGN'
AND asg.pay_basis_id = ppb.pay_basis_id
AND asg.assignment_id = ppp.assignment_id
AND ppp.change_date = (SELECT MAX(change_date)
FROM per_pay_proposals pro
WHERE ppp.assignment_id = pro.assignment_id
AND pro.change_date <= p_report_date
AND pro.approved = 'Y'
)
AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
AND asg.job_id = job.job_id
AND asg.assignment_type = 'E'
AND peo.business_group_id = p_business_group_id
AND EXISTS (SELECT 'Y'
FROM hr_all_organization_units hou
WHERE hou.business_group_id = p_business_group_id
AND hou.organization_id = asg.organization_id)
AND NOT EXISTS (SELECT 'Y'
FROM per_contracts_f pco
WHERE pco.person_id = peo.person_id
AND p_report_date BETWEEN pco.effective_start_date AND pco.effective_end_date)
)
GROUP BY '4'
,ipeds_category
,org_med_type;
-- In Order to indexed columns we are inserting session id into tax unit id
--
INSERT INTO pay_us_rpt_totals(tax_unit_id
,attribute1
,attribute3
,value1
,value3
,value4
,value5
,value6
,value7
,value8
,value9
,value10
,value11
,value12
,value13
,value14
,value15
,value16
,value17
,value18
,value19
,value20
,value21
,value22
,value30
)
SELECT USERENV('sessionid')
,p_report_name
,org_med_type
,DECODE(tenure_status,'01',tenure_status,'02') tenure
,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
,SUM(DECODE(gender,'M',1,0)) TotMen
,SUM(DECODE(gender,'F',1,0)) TotWmen
,ipeds_category
FROM (
SELECT peo.sex gender
,job.job_information8 ipeds_category
,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
,ppet.pei_information1 tenure_status
,peo.person_id
,pqh_inst_type_pkg.get_inst_type(asg.organization_id) org_med_type
FROM per_all_people_f peo
,per_all_assignments_f asg
,per_assignment_status_types ast
,per_jobs job
,per_pay_proposals ppp
,per_pay_bases ppb
,per_people_extra_info ppet
WHERE peo.person_id = asg.person_id
AND peo.person_id = ppet.person_id
AND ppet.information_type = 'PQH_TENURE_STATUS'
AND ppet.pei_information1 IN ('01','02','04')
AND peo.current_employee_flag = 'Y'
AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = l_emp_category
AND job.job_information_category = 'US'
AND job.job_information8 NOT IN (l_ipeds_cat)
AND job.business_group_id = p_business_group_id
AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.primary_flag = 'Y'
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status <> 'TERM_ASSIGN'
AND asg.pay_basis_id = ppb.pay_basis_id
AND asg.assignment_id = ppp.assignment_id
AND ppp.change_date = (SELECT MAX(change_date)
FROM per_pay_proposals pro
WHERE ppp.assignment_id = pro.assignment_id
AND pro.change_date <= p_report_date
AND pro.approved = 'Y'
)
AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
AND asg.job_id = job.job_id
AND asg.assignment_type = 'E'
AND peo.business_group_id = p_business_group_id
AND EXISTS (SELECT 'Y'
FROM hr_all_organization_units hou
WHERE hou.business_group_id = p_business_group_id
AND hou.organization_id = asg.organization_id)
)
GROUP BY DECODE(tenure_status,'01',tenure_status,'02')
,ipeds_category
,org_med_type;
-- In Order to indexed columns we are inserting session id into tax unit id
--
INSERT INTO pay_us_rpt_totals(tax_unit_id
,attribute1
,attribute3
,value1
,value3
,value4
,value5
,value6
,value7
,value8
,value9
,value10
,value11
,value12
,value13
,value14
,value15
,value16
,value17
,value18
,value19
,value20
,value21
,value22
,value30
)
SELECT USERENV('sessionid')
,p_report_name
,org_med_type
,'03'||DECODE(SIGN(cont_dur_mon-12),1,0,0,1,2) tenure
,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
,SUM(DECODE(gender,'M',1,0)) TotMen
,SUM(DECODE(gender,'F',1,0)) TotWmen
,ipeds_category
FROM (
SELECT peo.sex gender
,job.job_information8 ipeds_category
,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
,ppet.pei_information1 tenure_status
,pqh_employment_category.get_duration_in_months(pco.duration,pco.duration_units,pco.business_group_id,p_report_date) cont_dur_mon
,peo.person_id
,pqh_inst_type_pkg.get_inst_type(asg.organization_id) org_med_type
FROM per_all_people_f peo
,per_all_assignments_f asg
,per_assignment_status_types ast
,per_jobs job
,per_pay_proposals ppp
,per_pay_bases ppb
,per_people_extra_info ppet
,per_contracts_f pco
,per_shared_types pst
,per_shared_types pst1
WHERE peo.person_id = asg.person_id
AND peo.person_id = ppet.person_id
AND ppet.information_type = 'PQH_TENURE_STATUS'
AND ppet.pei_information1 IN ('03','05')
AND peo.current_employee_flag = 'Y'
AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = l_emp_category
AND job.job_information_category = 'US'
AND job.job_information8 NOT IN (l_ipeds_cat)
AND job.business_group_id = p_business_group_id
AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.primary_flag = 'Y'
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status <> 'TERM_ASSIGN'
AND asg.pay_basis_id = ppb.pay_basis_id
AND asg.assignment_id = ppp.assignment_id
AND ppp.change_date = (SELECT MAX(change_date)
FROM per_pay_proposals pro
WHERE ppp.assignment_id = pro.assignment_id
AND pro.change_date <= p_report_date
AND pro.approved = 'Y'
)
AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
AND asg.job_id = job.job_id
AND asg.assignment_type = 'E'
AND peo.business_group_id = p_business_group_id
AND pco.person_id = peo.person_id
AND pco.type = 'FULL_TIME'
AND pco.status = pst.system_type_cd
AND pst.lookup_type = 'CONTRACT_STATUS'
AND pst1.system_type_cd(+) = pst.system_type_cd
AND pst1.lookup_type(+) = pst.lookup_type
AND pst.business_group_id is null
AND pst1.business_group_id(+) = p_business_group_id
AND nvl(pst1.information1,pst.information1) = 'Y'
AND EXISTS (SELECT 'Y'
FROM hr_all_organization_units hou
WHERE hou.business_group_id = p_business_group_id
AND hou.organization_id = asg.organization_id)
)
GROUP BY '03'||DECODE(SIGN(cont_dur_mon-12),1,0,0,1,2)
,ipeds_category
,org_med_type;
-- In Order to indexed columns we are inserting session id into tax unit id
--
INSERT INTO pay_us_rpt_totals(tax_unit_id
,attribute1
,attribute3
,value1
,value3
,value4
,value5
,value6
,value7
,value8
,value9
,value10
,value11
,value12
,value13
,value14
,value15
,value16
,value17
,value18
,value19
,value20
,value21
,value22
,value30
)
SELECT USERENV('sessionid')
,p_report_name
,org_med_type
,'04' tenure
,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
,SUM(DECODE(gender,'M',1,0)) TotMen
,SUM(DECODE(gender,'F',1,0)) TotWmen
,ipeds_category
FROM (
SELECT peo.sex gender
,job.job_information8 ipeds_category
,peo.person_id person_id
,pqh_inst_type_pkg.get_inst_type(asg.organization_id) org_med_type
,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
FROM per_all_people_f peo
,per_all_assignments_f asg
,per_assignment_status_types ast
,per_jobs job
,per_pay_proposals ppp
,per_pay_bases ppb
WHERE peo.person_id = asg.person_id
AND peo.current_employee_flag = 'Y'
AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = l_emp_category
AND job.job_information_category = 'US'
AND job.job_information8 NOT IN (l_ipeds_cat)
AND job.business_group_id = p_business_group_id
AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.primary_flag = 'Y'
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status <> 'TERM_ASSIGN'
AND asg.pay_basis_id = ppb.pay_basis_id
AND asg.assignment_id = ppp.assignment_id
AND ppp.change_date = (SELECT MAX(change_date)
FROM per_pay_proposals pro
WHERE ppp.assignment_id = pro.assignment_id
AND pro.change_date <= p_report_date
AND pro.approved = 'Y'
)
AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
AND asg.job_id = job.job_id
AND asg.assignment_type = 'E'
AND peo.business_group_id = p_business_group_id
AND EXISTS (SELECT 'Y'
FROM hr_all_organization_units hou
WHERE hou.business_group_id = p_business_group_id
AND hou.organization_id = asg.organization_id)
AND NOT EXISTS( SELECT 'Y'
FROM per_people_extra_info ppet
WHERE ppet.person_id = peo.person_id
AND ppet.information_type = 'PQH_TENURE_STATUS')
)
GROUP BY '04'
,ipeds_category
,org_med_type;
-- In Order to indexed columns we are inserting session id into tax unit id
--
INSERT INTO pay_us_rpt_totals(tax_unit_id
,attribute1
,attribute3
,value1
,value3
,value4
,value5
,value6
,value7
,value8
,value9
,value10
,value11
,value12
,value13
,value14
,value15
,value16
,value17
,value18
,value19
,value20
,value21
,value22
,value30
)
SELECT USERENV('sessionid')
,p_report_name
,org_med_type
,'03'||DECODE(SIGN(cont_dur_mon-12),1,0,0,1,2) tenure
,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
,SUM(DECODE(gender,'M',1,0)) TotMen
,SUM(DECODE(gender,'F',1,0)) TotWmen
,ipeds_category
FROM (
SELECT peo.sex gender
,job.job_information8 ipeds_category
,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
,pqh_employment_category.get_duration_in_months(pco.duration,pco.duration_units,pco.business_group_id,p_report_date) cont_dur_mon
,peo.person_id
,pqh_inst_type_pkg.get_inst_type(asg.organization_id) org_med_type
FROM per_all_people_f peo
,per_all_assignments_f asg
,per_assignment_status_types ast
,per_jobs job
,per_pay_proposals ppp
,per_pay_bases ppb
,per_contracts_f pco
,per_shared_types pst
,per_shared_types pst1
WHERE peo.person_id = asg.person_id
AND peo.current_employee_flag = 'Y'
AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = l_emp_category
AND job.job_information_category = 'US'
AND job.job_information8 NOT IN (l_ipeds_cat)
AND job.business_group_id = p_business_group_id
AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.primary_flag = 'Y'
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status <> 'TERM_ASSIGN'
AND asg.pay_basis_id = ppb.pay_basis_id
AND asg.assignment_id = ppp.assignment_id
AND ppp.change_date = (SELECT MAX(change_date)
FROM per_pay_proposals pro
WHERE ppp.assignment_id = pro.assignment_id
AND pro.change_date <= p_report_date
AND pro.approved = 'Y'
)
AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
AND asg.job_id = job.job_id
AND asg.assignment_type = 'E'
AND peo.business_group_id = p_business_group_id
AND pco.person_id = peo.person_id
AND pco.type = 'FULL_TIME'
AND pco.status = pst.system_type_cd
AND pst.lookup_type = 'CONTRACT_STATUS'
AND pst1.system_type_cd(+) = pst.system_type_cd
AND pst1.lookup_type(+) = pst.lookup_type
AND pst.business_group_id is null
AND pst1.business_group_id(+) = p_business_group_id
AND nvl(pst1.information1,pst.information1) = 'Y'
AND EXISTS (SELECT 'Y'
FROM hr_all_organization_units hou
WHERE hou.business_group_id = p_business_group_id
AND hou.organization_id = asg.organization_id)
)
GROUP BY '03'||DECODE(SIGN(cont_dur_mon-12),1,0,0,1,2)
,ipeds_category
,org_med_type;
-- In Order to indexed columns we are inserting session id into tax unit id
--
INSERT INTO pay_us_rpt_totals(tax_unit_id
,attribute1
,attribute3
,value1
,value3
,value4
,value5
,value6
,value7
,value8
,value9
,value10
,value11
,value12
,value13
,value14
,value15
,value16
,value17
,value18
,value19
,value20
,value21
,value22
,value30
)
SELECT USERENV('sessionid')
,p_report_name
,org_med_type
,'04' tenure
,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
,SUM(DECODE(gender,'M',1,0)) TotMen
,SUM(DECODE(gender,'F',1,0)) TotWmen
,ipeds_category
FROM (
SELECT peo.sex gender
,job.job_information8 ipeds_category
,peo.person_id person_id
,pqh_inst_type_pkg.get_inst_type(asg.organization_id) org_med_type
,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
FROM per_all_people_f peo
,per_all_assignments_f asg
,per_assignment_status_types ast
,per_jobs job
,per_pay_proposals ppp
,per_pay_bases ppb
WHERE peo.person_id = asg.person_id
AND peo.current_employee_flag = 'Y'
AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = l_emp_category
AND job.job_information_category = 'US'
AND job.job_information8 NOT IN (l_ipeds_cat)
AND job.business_group_id = p_business_group_id
AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.primary_flag = 'Y'
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status <> 'TERM_ASSIGN'
AND asg.pay_basis_id = ppb.pay_basis_id
AND asg.assignment_id = ppp.assignment_id
AND ppp.change_date = (SELECT MAX(change_date)
FROM per_pay_proposals pro
WHERE ppp.assignment_id = pro.assignment_id
AND pro.change_date <= p_report_date
AND pro.approved = 'Y'
)
AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
AND asg.job_id = job.job_id
AND asg.assignment_type = 'E'
AND peo.business_group_id = p_business_group_id
AND EXISTS (SELECT 'Y'
FROM hr_all_organization_units hou
WHERE hou.business_group_id = p_business_group_id
AND hou.organization_id = asg.organization_id)
AND NOT EXISTS( SELECT 'Y'
FROM per_contracts_f pco
WHERE pco.person_id = peo.person_id
AND p_report_date BETWEEN pco.effective_start_date AND pco.effective_end_date)
)
GROUP BY '04'
,ipeds_category
,org_med_type;
-- In Order to indexed columns we are inserting session id into tax unit id
--
INSERT INTO pay_us_rpt_totals(tax_unit_id
,attribute1
,value3
,value4
,value5
,value6
,value7
,value8
,value9
,value10
,value11
,value12
,value13
,value14
,value15
,value16
,value17
,value18
,value19
,value20
,value21
,value22
,value30
)
/* All the Instructional staff needs to be reported under one columns so
IPEDS category codes 21 - 24 is converted into one code 21 and the label
of column would be replaced accordingly in XML data definition*/
SELECT USERENV('sessionid')
,'DGIPEDD'
,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
,SUM(DECODE(gender,'M',1,0)) TotMen
,SUM(DECODE(gender,'F',1,0)) TotWmen
,DECODE(ipeds_code,21,21,22,21,23,21,24,21,ipeds_code) ipeds_category
FROM (
SELECT peo.sex gender
,job.job_information8 ipeds_code
,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
,peo.person_id
FROM per_all_people_f peo
,per_all_assignments_f asg
,per_assignment_status_types ast
,per_jobs job
,per_pay_proposals ppp
,per_pay_bases ppb
WHERE peo.person_id = asg.person_id
AND peo.current_employee_flag = 'Y'
AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'PR'
AND job.job_information_category = 'US'
AND job.job_information8 NOT IN ('12')
AND job.business_group_id = p_business_group_id
AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.primary_flag = 'Y'
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status <> 'TERM_ASSIGN'
AND asg.pay_basis_id = ppb.pay_basis_id
AND asg.assignment_id = ppp.assignment_id
AND ppp.change_date = (SELECT MAX(change_date)
FROM per_pay_proposals pro
WHERE ppp.assignment_id = pro.assignment_id
AND pro.change_date <= p_report_date
AND pro.approved = 'Y'
)
AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
AND asg.job_id = job.job_id
AND asg.assignment_type = 'E'
AND peo.business_group_id = p_business_group_id
AND EXISTS (SELECT 'Y'
FROM hr_all_organization_units hou
WHERE hou.business_group_id = p_business_group_id
AND hou.organization_id = asg.organization_id)
)
GROUP BY DECODE(ipeds_code,21,21,22,21,23,21,24,21,ipeds_code);
INSERT INTO pay_us_rpt_totals(tax_unit_id
,attribute1
,attribute2
,value1
,value2
,value3
,value4
,value5
,value6
,value7
,value8
,value9
,value10
,value11
,value12
,value13
)
SELECT userenv('sessionid')
,'DGIPEDG'
,'PART1'
,academic_rank
,SUM(DECODE(cont_dur_mon, 9,DECODE(gender,'M',1,0),0))
,SUM(DECODE(cont_dur_mon,10,DECODE(gender,'M',1,0),0))
,SUM(DECODE(cont_dur_mon,11,DECODE(gender,'M',1,0),0))
,SUM(DECODE(cont_dur_mon,12,DECODE(gender,'M',1,0),0))
,SUM(DECODE(cont_dur_mon, 9,DECODE(gender,'F',1,0),0))
,SUM(DECODE(cont_dur_mon,10,DECODE(gender,'F',1,0),0))
,SUM(DECODE(cont_dur_mon,11,DECODE(gender,'F',1,0),0))
,SUM(DECODE(cont_dur_mon,12,DECODE(gender,'F',1,0),0))
,SUM(DECODE(gender,'M',1,0))
,SUM(DECODE(gender,'F',1,0))
,SUM(DECODE(gender,'M',annual_sal,0))
,SUM(DECODE(gender,'F',annual_sal,0))
FROM
(
SELECT peo.sex gender
,ppea.pei_information1 academic_rank
,peo.person_id
,CEIL(pqh_employment_category.get_duration_in_months(pco.duration,pco.duration_units,pco.business_group_id,p_report_date)) cont_dur_mon
,(NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor) annual_sal
FROM per_all_people_f peo
,per_all_assignments_f asg
,per_assignment_status_types ast
,per_jobs job
,per_pay_proposals ppp
,per_pay_bases ppb
,per_people_extra_info ppea
,per_contracts_f pco
,per_shared_types pst
,per_shared_types pst1
WHERE peo.person_id = asg.person_id
AND peo.person_id = ppea.person_id
AND ppea.information_type = 'PQH_ACADEMIC_RANK'
AND p_report_date BETWEEN fnd_date.canonical_to_date(ppea.PEI_INFORMATION2)
and nvl(fnd_date.canonical_to_date(ppea.PEI_INFORMATION3),p_report_date) --16208130
AND ppea.pei_information1 IS NOT NULL
AND peo.current_employee_flag = 'Y'
AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
AND pqh_employment_category.get_duration_in_months(pco.duration,pco.duration_units,pco.business_group_id,p_report_date) BETWEEN l_duration1 AND l_duration2
AND job.business_group_id = p_business_group_id
AND job.job_information_category = 'US'
AND job.job_information8 IN ('21', '22', '23','24')
AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.primary_flag = 'Y'
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status <> 'TERM_ASSIGN'
AND asg.pay_basis_id = ppb.pay_basis_id
AND asg.assignment_id = ppp.assignment_id
AND ppp.change_date = (SELECT MAX(change_date)
FROM per_pay_proposals pro
WHERE ppp.assignment_id = pro.assignment_id
AND pro.change_date <= p_report_date
AND pro.approved = 'Y'
)
AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
AND asg.job_id = job.job_id
AND asg.assignment_type = 'E'
AND peo.business_group_id = p_business_group_id
AND pco.person_id = peo.person_id
AND pco.type = 'FULL_TIME'
AND pco.status = pst.system_type_cd
AND pst.lookup_type = 'CONTRACT_STATUS'
AND pst1.system_type_cd(+) = pst.system_type_cd
AND pst1.lookup_type(+) = pst.lookup_type
AND pst.business_group_id IS NULL
AND pst1.business_group_id(+) = p_business_group_id
AND NVL(pst1.information1,pst.information1) = 'Y'
AND EXISTS (SELECT 'Y'
FROM hr_all_organization_units hou
WHERE hou.business_group_id = p_business_group_id
AND hou.organization_id = asg.organization_id)
)
GROUP BY academic_rank;
INSERT INTO pay_us_rpt_totals(tax_unit_id
,attribute1
,attribute2
,value1
,value2
,value3
)
SELECT userenv('sessionid')
,'DGIPEDG'
,'PART2'
/* As We have to report categories 27,28,29,30 into one so changed the codes to
one code 27. In XML meaning would be changed accordingly*/
,DECODE(job.job_information8,28,27,29,27,30,27,job.job_information8) ipeds_category
,COUNT(peo.person_id)
,SUM((NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor)) annual_sal
FROM per_all_people_f peo
,per_all_assignments_f asg
,per_assignment_status_types ast
,per_jobs job
,per_pay_proposals ppp
,per_pay_bases ppb
WHERE peo.person_id = asg.person_id
AND peo.current_employee_flag = 'Y'
AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
AND job.business_group_id = p_business_group_id
AND job.job_information_category = 'US'
AND job.job_information8 NOT IN ('12','21', '22', '23','24')
AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.primary_flag = 'Y'
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status <> 'TERM_ASSIGN'
AND asg.pay_basis_id = ppb.pay_basis_id
AND asg.assignment_id = ppp.assignment_id
AND ppp.change_date = (SELECT MAX(change_date)
FROM per_pay_proposals pro
WHERE ppp.assignment_id = pro.assignment_id
AND pro.change_date <= p_report_date
AND pro.approved = 'Y'
)
AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
AND asg.job_id = job.job_id
AND asg.assignment_type = 'E'
AND peo.business_group_id = p_business_group_id
AND EXISTS (SELECT 'Y'
FROM hr_all_organization_units hou
WHERE hou.business_group_id = p_business_group_id
AND hou.organization_id = asg.organization_id)
GROUP BY DECODE(job.job_information8,28,27,29,27,30,27,job.job_information8);
-- In Order to indexed columns we are inserting session id into tax unit id
INSERT INTO pay_us_rpt_totals(tax_unit_id
,attribute1
,attribute2
,value1
,value2
,value3
,value4
,value5
,value6
,value7
,value8
,value9
,value10
,value11
,value12
,value13
,value14
,value15
,value16
,value17
,value18
,value19
,value20
,value21
,value30
)
SELECT USERENV('sessionid')
,'DGIPEDH'
,'SECTION1'
,DECODE(tenure_status,'01',tenure_status,'02') tenure
,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
,SUM(DECODE(gender,'M',1,0)) TotMen
,SUM(DECODE(gender,'F',1,0)) TotWmen
,ipeds_category
FROM (
SELECT peo.sex gender
,job.job_information8 ipeds_category
,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
,ppet.pei_information1 tenure_status
,peo.person_id
FROM per_all_people_f peo
,per_all_assignments_f asg
,per_assignment_status_types ast
,per_jobs job
,per_pay_proposals ppp
,per_pay_bases ppb
,per_people_extra_info ppet
,per_periods_of_service pps
WHERE peo.person_id = asg.person_id
AND peo.person_id = ppet.person_id
AND ppet.information_type = 'PQH_TENURE_STATUS'
AND ppet.pei_information1 IN ('01','02','04')
AND peo.current_employee_flag = 'Y'
AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
AND job.business_group_id = p_business_group_id
AND job.job_information_category = 'US'
AND job.job_information8 IN ('21', '22', '23','24')
AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.primary_flag = 'Y'
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status <> 'TERM_ASSIGN'
AND asg.pay_basis_id = ppb.pay_basis_id
AND asg.assignment_id = ppp.assignment_id
AND ppp.change_date = (SELECT MAX(change_date)
FROM per_pay_proposals pro
WHERE ppp.assignment_id = pro.assignment_id
AND pro.change_date <= p_report_date
AND pro.approved = 'Y'
)
AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
AND asg.job_id = job.job_id
AND asg.assignment_type = 'E'
AND asg.period_of_service_id = pps.period_of_service_id
AND pps.date_start BETWEEN ADD_MONTHS(p_report_date,-4) AND (p_report_date-1)
AND peo.business_group_id = p_business_group_id
AND EXISTS (SELECT 'Y'
FROM hr_all_organization_units hou
WHERE hou.business_group_id = p_business_group_id
AND hou.organization_id = asg.organization_id)
)
GROUP BY DECODE(tenure_status,'01',tenure_status,'02')
,ipeds_category;
-- In Order to indexed columns we are inserting session id into tax unit id
--
INSERT INTO pay_us_rpt_totals(tax_unit_id
,attribute1
,attribute2
,value1
,value2
,value3
,value4
,value5
,value6
,value7
,value8
,value9
,value10
,value11
,value12
,value13
,value14
,value15
,value16
,value17
,value18
,value19
,value20
,value21
,value30
)
SELECT USERENV('sessionid')
,'DGIPEDH'
,'SECTION1'
,'03'||DECODE(SIGN(cont_dur_mon-12),1,0,0,1,2) tenure
,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
,SUM(DECODE(gender,'M',1,0)) TotMen
,SUM(DECODE(gender,'F',1,0)) TotWmen
,ipeds_category
FROM (
SELECT peo.sex gender
,job.job_information8 ipeds_category
,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
,ppet.pei_information1 tenure_status
,pqh_employment_category.get_duration_in_months(pco.duration,pco.duration_units,pco.business_group_id,p_report_date) cont_dur_mon
,peo.person_id
FROM per_all_people_f peo
,per_all_assignments_f asg
,per_assignment_status_types ast
,per_jobs job
,per_pay_proposals ppp
,per_pay_bases ppb
,per_people_extra_info ppet
,per_contracts_f pco
,per_shared_types pst
,per_shared_types pst1
,per_periods_of_service pps
WHERE peo.person_id = asg.person_id
AND peo.person_id = ppet.person_id
AND ppet.information_type = 'PQH_TENURE_STATUS'
AND ppet.pei_information1 IN ('03','05')
AND peo.current_employee_flag = 'Y'
AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
AND job.business_group_id = p_business_group_id
AND job.job_information_category = 'US'
AND job.job_information8 IN ('21', '22', '23','24')
AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.primary_flag = 'Y'
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status <> 'TERM_ASSIGN'
AND asg.pay_basis_id = ppb.pay_basis_id
AND asg.assignment_id = ppp.assignment_id
AND asg.period_of_service_id = pps.period_of_service_id
AND pps.date_start BETWEEN ADD_MONTHS(p_report_date,-4) AND (p_report_date-1)
AND ppp.change_date = (SELECT MAX(change_date)
FROM per_pay_proposals pro
WHERE ppp.assignment_id = pro.assignment_id
AND pro.change_date <= p_report_date
AND pro.approved = 'Y'
)
AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
AND asg.job_id = job.job_id
AND asg.assignment_type = 'E'
AND peo.business_group_id = p_business_group_id
AND pco.person_id = peo.person_id
AND pco.type = 'FULL_TIME'
AND pco.status = pst.system_type_cd
AND pst.lookup_type = 'CONTRACT_STATUS'
AND pst1.system_type_cd(+) = pst.system_type_cd
AND pst1.lookup_type(+) = pst.lookup_type
AND pst.business_group_id IS NULL
AND pst1.business_group_id(+) = p_business_group_id
AND NVL(pst1.information1,pst.information1) = 'Y'
AND EXISTS (SELECT 'Y'
FROM hr_all_organization_units hou
WHERE hou.business_group_id = p_business_group_id
AND hou.organization_id = asg.organization_id)
)
GROUP BY '03'||DECODE(SIGN(cont_dur_mon-12),1,0,0,1,2)
,ipeds_category;
INSERT INTO pay_us_rpt_totals(tax_unit_id
,attribute1
,attribute2
,value1
,value2
,value3
,value4
,value5
,value6
,value7
,value8
,value9
,value10
,value11
,value12
,value13
,value14
,value15
,value16
,value17
,value18
,value19
,value20
,value21
,value30
)
SELECT userenv('sessionid')
,'DGIPEDH'
,'SECTION1'
,'4' tenure
,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
,SUM(DECODE(gender,'M',1,0)) TotMen
,SUM(DECODE(gender,'F',1,0)) TotWmen
,ipeds_category
FROM (
SELECT peo.sex gender
,job.job_information8 ipeds_category
,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
,peo.person_id
FROM per_all_people_f peo
,per_all_assignments_f asg
,per_assignment_status_types ast
,per_jobs job
,per_pay_proposals ppp
,per_pay_bases ppb
,per_periods_of_service pps
WHERE peo.person_id = asg.person_id
AND peo.current_employee_flag = 'Y'
AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
AND job.business_group_id = p_business_group_id
AND job.job_information_category = 'US'
AND job.job_information8 IN ('21', '22', '23','24')
AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.primary_flag = 'Y'
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status <> 'TERM_ASSIGN'
AND asg.pay_basis_id = ppb.pay_basis_id
AND asg.assignment_id = ppp.assignment_id
AND ppp.change_date = (SELECT MAX(change_date)
FROM per_pay_proposals pro
WHERE ppp.assignment_id = pro.assignment_id
AND pro.change_date <= p_report_date
AND pro.approved = 'Y'
)
AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
AND asg.job_id = job.job_id
AND asg.assignment_type = 'E'
AND peo.business_group_id = p_business_group_id
AND asg.period_of_service_id = pps.period_of_service_id
AND pps.date_start BETWEEN ADD_MONTHS(p_report_date,-4) AND (p_report_date-1)
AND EXISTS (SELECT 'Y'
FROM hr_all_organization_units hou
WHERE hou.business_group_id = p_business_group_id
AND hou.organization_id = asg.organization_id)
AND NOT EXISTS (SELECT 'Y'
FROM per_people_extra_info ppet
WHERE ppet.person_id = peo.person_id
AND ppet.information_type = 'PQH_TENURE_STATUS')
)
GROUP BY '4'
,ipeds_category;
-- In Order to indexed columns we are inserting session id into tax unit id
--
INSERT INTO pay_us_rpt_totals(tax_unit_id
,attribute1
,attribute2
,value1
,value2
,value3
,value4
,value5
,value6
,value7
,value8
,value9
,value10
,value11
,value12
,value13
,value14
,value15
,value16
,value17
,value18
,value19
,value20
,value21
,value30
)
SELECT USERENV('sessionid')
,'DGIPEDH'
,'SECTION1'
,'03'||DECODE(SIGN(cont_dur_mon-12),1,0,0,1,2) tenure
,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
,SUM(DECODE(gender,'M',1,0)) TotMen
,SUM(DECODE(gender,'F',1,0)) TotWmen
,ipeds_category
FROM (
SELECT peo.sex gender
,job.job_information8 ipeds_category
,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
,pqh_employment_category.get_duration_in_months(pco.duration,pco.duration_units,pco.business_group_id,p_report_date) cont_dur_mon
,peo.person_id
FROM per_all_people_f peo
,per_all_assignments_f asg
,per_assignment_status_types ast
,per_jobs job
,per_pay_proposals ppp
,per_pay_bases ppb
,per_contracts_f pco
,per_shared_types pst
,per_shared_types pst1
,per_periods_of_service pps
WHERE peo.person_id = asg.person_id
AND peo.current_employee_flag = 'Y'
AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
AND job.business_group_id = p_business_group_id
AND job.job_information_category = 'US'
AND job.job_information8 IN ('21', '22', '23','24')
AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.primary_flag = 'Y'
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status <> 'TERM_ASSIGN'
AND asg.pay_basis_id = ppb.pay_basis_id
AND asg.assignment_id = ppp.assignment_id
AND ppp.change_date = (SELECT MAX(change_date)
FROM per_pay_proposals pro
WHERE ppp.assignment_id = pro.assignment_id
AND pro.change_date <= p_report_date
AND pro.approved = 'Y'
)
AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
AND asg.job_id = job.job_id
AND asg.assignment_type = 'E'
AND peo.business_group_id = p_business_group_id
AND asg.period_of_service_id = pps.period_of_service_id
AND pps.date_start BETWEEN ADD_MONTHS(p_report_date,-4) AND (p_report_date-1)
AND pco.person_id = peo.person_id
AND pco.type = 'FULL_TIME'
AND pco.status = pst.system_type_cd
AND pst.lookup_type = 'CONTRACT_STATUS'
AND pst1.system_type_cd(+) = pst.system_type_cd
AND pst1.lookup_type(+) = pst.lookup_type
AND pst.business_group_id IS NULL
AND pst1.business_group_id(+) = p_business_group_id
AND NVL(pst1.information1,pst.information1) = 'Y'
AND EXISTS (SELECT 'Y'
FROM hr_all_organization_units hou
WHERE hou.business_group_id = p_business_group_id
AND hou.organization_id = asg.organization_id)
)
GROUP BY '03'||DECODE(SIGN(cont_dur_mon-12),1,0,0,1,2)
,ipeds_category;
INSERT INTO pay_us_rpt_totals(tax_unit_id
,attribute1
,attribute2
,value1
,value2
,value3
,value4
,value5
,value6
,value7
,value8
,value9
,value10
,value11
,value12
,value13
,value14
,value15
,value16
,value17
,value18
,value19
,value20
,value21
,value30
)
SELECT USERENV('sessionid')
,'DGIPEDH'
,'SECTION1'
,'4' tenure
,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
,SUM(DECODE(gender,'M',1,0)) TotMen
,SUM(DECODE(gender,'F',1,0)) TotWmen
,ipeds_category
FROM (
SELECT peo.sex gender
,job.job_information8 ipeds_category
,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
,peo.person_id
FROM per_all_people_f peo
,per_all_assignments_f asg
,per_assignment_status_types ast
,per_jobs job
,per_pay_proposals ppp
,per_pay_bases ppb
,per_periods_of_service pps
WHERE peo.person_id = asg.person_id
AND peo.current_employee_flag = 'Y'
AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
AND job.business_group_id = p_business_group_id
AND job.job_information_category = 'US'
AND job.job_information8 IN ('21', '22', '23','24')
AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.primary_flag = 'Y'
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status <> 'TERM_ASSIGN'
AND asg.pay_basis_id = ppb.pay_basis_id
AND asg.assignment_id = ppp.assignment_id
AND ppp.change_date = (SELECT MAX(change_date)
FROM per_pay_proposals pro
WHERE ppp.assignment_id = pro.assignment_id
AND pro.change_date <= p_report_date
AND pro.approved = 'Y'
)
AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
AND asg.job_id = job.job_id
AND asg.assignment_type = 'E'
AND peo.business_group_id = p_business_group_id
AND asg.period_of_service_id = pps.period_of_service_id
AND pps.date_start BETWEEN ADD_MONTHS(p_report_date,-4) AND (p_report_date-1)
AND EXISTS (SELECT 'Y'
FROM hr_all_organization_units hou
WHERE hou.business_group_id = p_business_group_id
AND hou.organization_id = asg.organization_id)
AND NOT EXISTS (SELECT 'Y'
FROM per_contracts_f pco
WHERE pco.person_id = peo.person_id
AND p_report_date BETWEEN pco.effective_start_date AND pco.effective_end_date)
)
GROUP BY '4'
,ipeds_category;
INSERT INTO pay_us_rpt_totals(tax_unit_id
,attribute1
,attribute2
,value1
,value2
,value3
,value4
,value5
,value6
,value7
,value8
,value9
,value10
,value11
,value12
,value13
,value14
,value15
,value16
,value17
,value18
,value19
,value20
,value21
)
SELECT USERENV('sessionid')
,'DGIPEDH'
,'SECTION2'
,ipeds_category
,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
,SUM(DECODE(gender,'M',1,0)) TotMen
,SUM(DECODE(gender,'F',1,0)) TotWmen
FROM (SELECT peo.sex gender
/* As We have to report categories 27,28,29,30 into one so changed the codes to
one code 27. In XML meaning would be changed accordingly*/
,DECODE(job.job_information8,27,27,28,27,29,27,30,27,job.job_information8) ipeds_category
,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
,peo.person_id
FROM per_all_people_f peo
,per_all_assignments_f asg
,per_assignment_status_types ast
,per_jobs job
,per_pay_proposals ppp
,per_pay_bases ppb
,per_periods_of_service pps
WHERE peo.person_id = asg.person_id
AND peo.current_employee_flag = 'Y'
AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
AND job.business_group_id = p_business_group_id
AND job.job_information_category = 'US'
AND job.job_information8 NOT IN ('12','21','22','23','24')
AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.primary_flag = 'Y'
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status <> 'TERM_ASSIGN'
AND asg.pay_basis_id = ppb.pay_basis_id
AND asg.assignment_id = ppp.assignment_id
AND ppp.change_date = (SELECT MAX(change_date)
FROM per_pay_proposals pro
WHERE ppp.assignment_id = pro.assignment_id
AND pro.change_date <= p_report_date
AND pro.approved = 'Y'
)
AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
AND asg.job_id = job.job_id
AND asg.assignment_type = 'E'
AND peo.business_group_id = p_business_group_id
AND asg.period_of_service_id = pps.period_of_service_id
AND pps.date_start BETWEEN ADD_MONTHS(p_report_date,-4) AND (p_report_date-1)
AND EXISTS (SELECT 'Y'
FROM hr_all_organization_units hou
WHERE hou.business_group_id = p_business_group_id
AND hou.organization_id = asg.organization_id)
)
GROUP BY ipeds_category;
Part A or B based on Parameter selected by user
*****************************************************************************/
FUNCTION non_deg_gnt_part_bef_rpt(p_report_type IN VARCHAR2)
RETURN boolean
IS
l_fr varchar2(2000);
-- In Order to indexed columns we are inserting session id into tax unit id
--
INSERT INTO pay_us_rpt_totals(tax_unit_id
,attribute1
,value1
,value2
,value3
,value4
,value5
,value6
,value7
,value8
,value9
,value10
,value11
,value12
,value13
,value14
,value15
,value16
,value17
,value18
,value19
,value20
,attribute2
)
SELECT USERENV('sessionid')
,'NDGIPED'
,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
,SUM(DECODE(gender,'M',1,0)) TotMen
,SUM(DECODE(gender,'F',1,0)) TotWmen
,ipeds_category
FROM (SELECT peo.sex gender
/* As We have to report categories 21,22,23,24,25,26 as one
category and 27,28,29,30 into one so changed the codes to
one code 21 and 27 respectively. In XML meaning would be
changed accordingly */
,DECODE(job.job_information8,21,21,22,21,23,21,24,21,25,21,26,21,27,27,28,27,29,27,job.job_information8) ipeds_category
,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
,peo.person_id
FROM per_all_people_f peo
,per_all_assignments_f asg
,per_assignment_status_types ast
,per_jobs job
,per_pay_proposals ppp
,per_pay_bases ppb
WHERE peo.person_id = asg.person_id
AND peo.current_employee_flag = 'Y'
AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = l_employment_category
AND job.business_group_id = p_business_group_id
AND job.job_information_category = 'US'
AND job.job_information8 NOT IN ('12')
AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.primary_flag = 'Y'
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status <> 'TERM_ASSIGN'
AND asg.pay_basis_id = ppb.pay_basis_id
AND asg.assignment_id = ppp.assignment_id
AND ppp.change_date = (SELECT MAX(change_date)
FROM per_pay_proposals pro
WHERE ppp.assignment_id = pro.assignment_id
AND pro.change_date <= p_report_date
AND pro.approved = 'Y'
)
AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
AND asg.job_id = job.job_id
AND asg.assignment_type = 'E'
AND peo.business_group_id = p_business_group_id
AND EXISTS (SELECT 'Y'
FROM hr_all_organization_units hou
WHERE hou.business_group_id = p_business_group_id
AND hou.organization_id = asg.organization_id))
GROUP BY ipeds_category;
After Report trigger to delete the data from PAY_US_RPT_TOTALS.
*****************************************************************************/
FUNCTION AfterReport(p_report_name VARCHAR2)
RETURN BOOLEAN
IS
BEGIN
EXECUTE IMMEDIATE
'DELETE FROM pay_us_rpt_totals
WHERE attribute1 ='''||p_report_name
||'''AND tax_unit_id = USERENV(''sessionid'') ';