[Home] [Help]
137: l_tot_wmen NUMBER;
138:
139: BEGIN
140: -- Fetch the Employement Categories defined as per the fast formula
141: pqh_employment_category.fetch_empl_categories(p_business_group_id,l_fr,l_ft,l_pr,l_pt);
142:
143: -- Check if Tenure system is used by establishment
144: IF p_tenured = 'Y' THEN
145:
227: and nvl(fnd_date.canonical_to_date(ppea.PEI_INFORMATION3),p_report_date) --16208130
228: AND ppea.pei_information1 IS NOT NULL
229: AND ppet.pei_information1 IN ('01','02','04')
230: AND peo.current_employee_flag = 'Y'
231: AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
232: AND job.business_group_id = p_business_group_id
233: AND job.job_information_category = 'US'
234: AND job.job_information8 IN ('21', '22', '23','24')
235: AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
325: ,job.job_information8 ipeds_category
326: ,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
327: ,ppet.pei_information1 tenure_status
328: ,ppea.pei_information1 academic_rank
329: ,pqh_employment_category.get_duration_in_months(pco.duration,pco.duration_units,pco.business_group_id,p_report_date) cont_dur_mon
330: ,peo.person_id
331: ,pqh_inst_type_pkg.get_inst_type(asg.organization_id) org_med_type
332: FROM per_all_people_f peo
333: ,per_all_assignments_f asg
349: and nvl(fnd_date.canonical_to_date(ppea.PEI_INFORMATION3),p_report_date) --16208130
350: AND ppea.pei_information1 IS NOT NULL
351: AND ppet.pei_information1 IN ('03','05')
352: AND peo.current_employee_flag = 'Y'
353: AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
354: AND job.business_group_id = p_business_group_id
355: AND job.job_information_category = 'US'
356: AND job.job_information8 IN ('21', '22', '23','24')
357: AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
463: ,per_pay_proposals ppp
464: ,per_pay_bases ppb
465: WHERE peo.person_id = asg.person_id
466: AND peo.current_employee_flag = 'Y'
467: AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
468: AND job.business_group_id = p_business_group_id
469: AND job.job_information_category = 'US'
470: AND job.job_information8 IN ('21', '22', '23','24')
471: AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
566: SELECT peo.sex gender
567: ,job.job_information8 ipeds_category
568: ,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
569: ,ppea.pei_information1 academic_rank
570: ,pqh_employment_category.get_duration_in_months(pco.duration,pco.duration_units,pco.business_group_id,p_report_date) cont_dur_mon
571: ,peo.person_id
572: ,pqh_inst_type_pkg.get_inst_type(asg.organization_id) org_med_type
573: FROM per_all_people_f peo
574: ,per_all_assignments_f asg
586: AND p_report_date BETWEEN fnd_date.canonical_to_date(ppea.PEI_INFORMATION2)
587: and nvl(fnd_date.canonical_to_date(ppea.PEI_INFORMATION3),p_report_date) --16208130
588: AND ppea.pei_information1 IS NOT NULL
589: AND peo.current_employee_flag = 'Y'
590: AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
591: AND job.business_group_id = p_business_group_id
592: AND job.job_information_category = 'US'
593: AND job.job_information8 IN ('21', '22', '23','24')
594: AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
699: ,per_pay_proposals ppp
700: ,per_pay_bases ppb
701: WHERE peo.person_id = asg.person_id
702: AND peo.current_employee_flag = 'Y'
703: AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
704: AND job.business_group_id = p_business_group_id
705: AND job.job_information_category = 'US'
706: AND job.job_information8 IN ('21', '22', '23','24')
707: AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
761: l_tot_wmen NUMBER;
762:
763: BEGIN
764: -- Fetch the Employement Categories defined as per the fast formula
765: pqh_employment_category.fetch_empl_categories(p_business_group_id,l_fr,l_ft,l_pr,l_pt);
766:
767: -- Employement Category and IPEDS categories are modified as per report type.
768: -- For Part B it is Full Time and Part E is Part Time. For Part B
769: -- Non-Instructional staff categories are assigned. For Part E it is all
853: AND peo.person_id = ppet.person_id
854: AND ppet.information_type = 'PQH_TENURE_STATUS'
855: AND ppet.pei_information1 IN ('01','02','04')
856: AND peo.current_employee_flag = 'Y'
857: AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = l_emp_category
858: AND job.job_information_category = 'US'
859: AND job.job_information8 NOT IN (l_ipeds_cat)
860: AND job.business_group_id = p_business_group_id
861: AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
948: SELECT peo.sex gender
949: ,job.job_information8 ipeds_category
950: ,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
951: ,ppet.pei_information1 tenure_status
952: ,pqh_employment_category.get_duration_in_months(pco.duration,pco.duration_units,pco.business_group_id,p_report_date) cont_dur_mon
953: ,peo.person_id
954: ,pqh_inst_type_pkg.get_inst_type(asg.organization_id) org_med_type
955: FROM per_all_people_f peo
956: ,per_all_assignments_f asg
966: AND peo.person_id = ppet.person_id
967: AND ppet.information_type = 'PQH_TENURE_STATUS'
968: AND ppet.pei_information1 IN ('03','05')
969: AND peo.current_employee_flag = 'Y'
970: AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = l_emp_category
971: AND job.job_information_category = 'US'
972: AND job.job_information8 NOT IN (l_ipeds_cat)
973: AND job.business_group_id = p_business_group_id
974: AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
1076: ,per_pay_proposals ppp
1077: ,per_pay_bases ppb
1078: WHERE peo.person_id = asg.person_id
1079: AND peo.current_employee_flag = 'Y'
1080: AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = l_emp_category
1081: AND job.job_information_category = 'US'
1082: AND job.job_information8 NOT IN (l_ipeds_cat)
1083: AND job.business_group_id = p_business_group_id
1084: AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
1177: FROM (
1178: SELECT peo.sex gender
1179: ,job.job_information8 ipeds_category
1180: ,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
1181: ,pqh_employment_category.get_duration_in_months(pco.duration,pco.duration_units,pco.business_group_id,p_report_date) cont_dur_mon
1182: ,peo.person_id
1183: ,pqh_inst_type_pkg.get_inst_type(asg.organization_id) org_med_type
1184: FROM per_all_people_f peo
1185: ,per_all_assignments_f asg
1191: ,per_shared_types pst
1192: ,per_shared_types pst1
1193: WHERE peo.person_id = asg.person_id
1194: AND peo.current_employee_flag = 'Y'
1195: AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = l_emp_category
1196: AND job.job_information_category = 'US'
1197: AND job.job_information8 NOT IN (l_ipeds_cat)
1198: AND job.business_group_id = p_business_group_id
1199: AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
1302: ,per_pay_proposals ppp
1303: ,per_pay_bases ppb
1304: WHERE peo.person_id = asg.person_id
1305: AND peo.current_employee_flag = 'Y'
1306: AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = l_emp_category
1307: AND job.job_information_category = 'US'
1308: AND job.job_information8 NOT IN (l_ipeds_cat)
1309: AND job.business_group_id = p_business_group_id
1310: AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
1359: l_tot_wmen NUMBER;
1360:
1361: BEGIN
1362: -- Fetch the Employement Categories defined as per the fast formula
1363: pqh_employment_category.fetch_empl_categories(p_business_group_id,l_fr,l_ft,l_pr,l_pt);
1364:
1365: --
1366: -- SQL to fetch counts Part Time Instruction Staff based on IPEDS job
1367: -- category
1429: ,per_pay_proposals ppp
1430: ,per_pay_bases ppb
1431: WHERE peo.person_id = asg.person_id
1432: AND peo.current_employee_flag = 'Y'
1433: AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'PR'
1434: AND job.job_information_category = 'US'
1435: AND job.job_information8 NOT IN ('12')
1436: AND job.business_group_id = p_business_group_id
1437: AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
1478: l_duration1 NUMBER;
1479: l_duration2 NUMBER;
1480:
1481: BEGIN
1482: pqh_employment_category.fetch_empl_categories(p_business_group_id,l_fr,l_ft,l_pr,l_pt);
1483: l_duration1 := 9;
1484: l_duration2 := 12;
1485:
1486: -- Query to insert count and total salary of instructional staff with 9 to 12 contract
1521: (
1522: SELECT peo.sex gender
1523: ,ppea.pei_information1 academic_rank
1524: ,peo.person_id
1525: ,CEIL(pqh_employment_category.get_duration_in_months(pco.duration,pco.duration_units,pco.business_group_id,p_report_date)) cont_dur_mon
1526: ,(NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor) annual_sal
1527: FROM per_all_people_f peo
1528: ,per_all_assignments_f asg
1529: ,per_assignment_status_types ast
1540: AND p_report_date BETWEEN fnd_date.canonical_to_date(ppea.PEI_INFORMATION2)
1541: and nvl(fnd_date.canonical_to_date(ppea.PEI_INFORMATION3),p_report_date) --16208130
1542: AND ppea.pei_information1 IS NOT NULL
1543: AND peo.current_employee_flag = 'Y'
1544: AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
1545: 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
1546: AND job.business_group_id = p_business_group_id
1547: AND job.job_information_category = 'US'
1548: AND job.job_information8 IN ('21', '22', '23','24')
1541: and nvl(fnd_date.canonical_to_date(ppea.PEI_INFORMATION3),p_report_date) --16208130
1542: AND ppea.pei_information1 IS NOT NULL
1543: AND peo.current_employee_flag = 'Y'
1544: AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
1545: 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
1546: AND job.business_group_id = p_business_group_id
1547: AND job.job_information_category = 'US'
1548: AND job.job_information8 IN ('21', '22', '23','24')
1549: AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
1602: ,per_pay_proposals ppp
1603: ,per_pay_bases ppb
1604: WHERE peo.person_id = asg.person_id
1605: AND peo.current_employee_flag = 'Y'
1606: AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
1607: AND job.business_group_id = p_business_group_id
1608: AND job.job_information_category = 'US'
1609: AND job.job_information8 NOT IN ('12','21', '22', '23','24')
1610: AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
1653: l_tot_wmen NUMBER;
1654:
1655: BEGIN
1656: -- Fetch the Employement Categories defined as per the fast formula
1657: pqh_employment_category.fetch_empl_categories(p_business_group_id,l_fr,l_ft,l_pr,l_pt);
1658:
1659: -- Check if Tenure system is used by establishment
1660: IF p_tenured = 'Y' THEN
1661:
1732: AND peo.person_id = ppet.person_id
1733: AND ppet.information_type = 'PQH_TENURE_STATUS'
1734: AND ppet.pei_information1 IN ('01','02','04')
1735: AND peo.current_employee_flag = 'Y'
1736: AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
1737: AND job.business_group_id = p_business_group_id
1738: AND job.job_information_category = 'US'
1739: AND job.job_information8 IN ('21', '22', '23','24')
1740: AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
1829: SELECT peo.sex gender
1830: ,job.job_information8 ipeds_category
1831: ,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
1832: ,ppet.pei_information1 tenure_status
1833: ,pqh_employment_category.get_duration_in_months(pco.duration,pco.duration_units,pco.business_group_id,p_report_date) cont_dur_mon
1834: ,peo.person_id
1835: FROM per_all_people_f peo
1836: ,per_all_assignments_f asg
1837: ,per_assignment_status_types ast
1847: AND peo.person_id = ppet.person_id
1848: AND ppet.information_type = 'PQH_TENURE_STATUS'
1849: AND ppet.pei_information1 IN ('03','05')
1850: AND peo.current_employee_flag = 'Y'
1851: AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
1852: AND job.business_group_id = p_business_group_id
1853: AND job.job_information_category = 'US'
1854: AND job.job_information8 IN ('21', '22', '23','24')
1855: AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
1960: ,per_pay_bases ppb
1961: ,per_periods_of_service pps
1962: WHERE peo.person_id = asg.person_id
1963: AND peo.current_employee_flag = 'Y'
1964: AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
1965: AND job.business_group_id = p_business_group_id
1966: AND job.job_information_category = 'US'
1967: AND job.job_information8 IN ('21', '22', '23','24')
1968: AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
2062: FROM (
2063: SELECT peo.sex gender
2064: ,job.job_information8 ipeds_category
2065: ,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
2066: ,pqh_employment_category.get_duration_in_months(pco.duration,pco.duration_units,pco.business_group_id,p_report_date) cont_dur_mon
2067: ,peo.person_id
2068: FROM per_all_people_f peo
2069: ,per_all_assignments_f asg
2070: ,per_assignment_status_types ast
2076: ,per_shared_types pst1
2077: ,per_periods_of_service pps
2078: WHERE peo.person_id = asg.person_id
2079: AND peo.current_employee_flag = 'Y'
2080: AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
2081: AND job.business_group_id = p_business_group_id
2082: AND job.job_information_category = 'US'
2083: AND job.job_information8 IN ('21', '22', '23','24')
2084: AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
2188: ,per_pay_bases ppb
2189: ,per_periods_of_service pps
2190: WHERE peo.person_id = asg.person_id
2191: AND peo.current_employee_flag = 'Y'
2192: AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
2193: AND job.business_group_id = p_business_group_id
2194: AND job.job_information_category = 'US'
2195: AND job.job_information8 IN ('21', '22', '23','24')
2196: AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
2295: ,per_pay_bases ppb
2296: ,per_periods_of_service pps
2297: WHERE peo.person_id = asg.person_id
2298: AND peo.current_employee_flag = 'Y'
2299: AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = 'FR'
2300: AND job.business_group_id = p_business_group_id
2301: AND job.job_information_category = 'US'
2302: AND job.job_information8 NOT IN ('12','21','22','23','24')
2303: AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
2352: l_employment_category VARCHAR2(2);
2353:
2354: BEGIN
2355: -- Fetch the Employement Categories defined as per the fast formula
2356: pqh_employment_category.fetch_empl_categories(p_business_group_id,l_fr,l_ft,l_pr,l_pt);
2357:
2358: -- If Report type is 'A' then employement category full time is assigned
2359: -- else then employement category part time is assigned
2360: IF p_report_type = 'A' THEN
2429: ,per_pay_proposals ppp
2430: ,per_pay_bases ppb
2431: WHERE peo.person_id = asg.person_id
2432: AND peo.current_employee_flag = 'Y'
2433: AND pqh_employment_category.identify_empl_category(asg.employment_category,l_fr,l_ft,l_pr,l_pt) = l_employment_category
2434: AND job.business_group_id = p_business_group_id
2435: AND job.job_information_category = 'US'
2436: AND job.job_information8 NOT IN ('12')
2437: AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date