[Home] [Help]
PACKAGE BODY: APPS.PER_US_IPEDS_PKG
Source
1 PACKAGE BODY per_us_ipeds_pkg AS
2 /* $Header: peusiped.pkb 120.0.12020000.6 2013/01/25 05:57:50 agarai noship $ */
3
4 /***************************************************************************1
5 Name : get_report_date
6 Type : Function
7 Return Type : VARCHAR2
8 Description : Returns the Report Run date in Month DD,YYYY format
9 *****************************************************************************/
10
11 FUNCTION get_report_date
12 RETURN VARCHAR2
13 IS
14 BEGIN
15
16 RETURN TRIM(TO_CHAR(p_report_date,'Month'))||' '||TO_CHAR(p_report_date,'DD,YYYY');
17
18 END get_report_date;
19
20 /***************************************************************************
21 Name : get_tenured
22 Type : Function
23 Return Type : VARCHAR2
24 Description : Returns the Tenured Flag Value
25 *****************************************************************************/
26 FUNCTION get_tenured
27 RETURN VARCHAR2
28 IS
29 BEGIN
30
31 RETURN hr_general.decode_lookup('YES_NO',p_tenured);
32
33 END get_tenured;
34
35 /***************************************************************************
36 Name : get_race_gender
37 Type : Function
38 Return Type : VARCHAR2
39 Description : Returns the Race/Ethnicity Value
40 *****************************************************************************/
41 FUNCTION get_race_gender
42 RETURN VARCHAR2
43 IS
44 BEGIN
45
46 RETURN hr_general.decode_lookup('YES_NO',p_race_gender);
47
48 END get_race_gender;
49
50 /***************************************************************************
51 Name : get_new_hire_start_date
52 Type : Function
53 Return Type : VARCHAR2
54 Description : Returns the Start Date for IPED Part H(New Hire) Report
55 in Month DD,YYYY format
56 *****************************************************************************/
57 FUNCTION get_new_hire_start_date
58 RETURN VARCHAR2
59 IS
60 BEGIN
61
62 RETURN TRIM(TO_CHAR(ADD_MONTHS(p_report_date,-4),'Month'))||' '||TO_CHAR(ADD_MONTHS(p_report_date,-4),'DD,YYYY');
63
64 END get_new_hire_start_date;
65
66 /***************************************************************************
67 Name : get_new_hire_end_date
68 Type : Function
69 Return Type : VARCHAR2
70 Description : Returns the End Date for IPED Part H(New Hire) Report
71 in Month DD,YYYY format
72 *****************************************************************************/
73 FUNCTION get_new_hire_end_date
74 RETURN VARCHAR2
75 IS
76 BEGIN
77
78 RETURN TRIM(TO_CHAR((p_report_date-1),'Month'))||' '||TO_CHAR((p_report_date-1),'DD,YYYY');
79
80 END get_new_hire_end_date;
81
82 /***************************************************************************
83 Name : get_report_type
84 Type : Function
85 Return Type : VARCHAR2
86 Description : Returns the Report Type description
87 *****************************************************************************/
88
89 FUNCTION get_report_type
90 RETURN VARCHAR2
91 IS
92 BEGIN
93
94 RETURN hr_general.decode_lookup('PER_US_INDEG_RPT_TYPE',p_report_type);
95
96 END get_report_type;
97
98 /***************************************************************************
99 Name : get_non_deg_gnt_label
100 Type : Function
101 Return Type : VARCHAR2
102 Description : Returns the Report Label depending on the report type.
103 *****************************************************************************/
104
105 FUNCTION get_non_deg_gnt_label
106 RETURN VARCHAR2
107 IS
108 l_label_type VARCHAR2(5);
109 BEGIN
110
111 IF p_report_type = 'A' THEN
112 l_label_type :='NDGA';
113 ELSE
114 l_label_type :='NDGB';
115 END IF;
116
117 RETURN hr_general.decode_lookup('PER_US_IPEDS_RPT_LBL',l_label_type);
118
119 END get_non_deg_gnt_label;
120
121 /***************************************************************************
122 Name : deg_gnt_parta_bef_rpt
123 Type : Function
124 Return Type : Boolean
125 Description : Populates the PER_US_RPT_TOTALS with information required to
126 generate the IPEDS Part A report
127 *****************************************************************************/
128
129 FUNCTION deg_gnt_parta_bef_rpt
130 RETURN BOOLEAN
131 IS
132 l_fr VARCHAR2(2000);
133 l_ft VARCHAR2(2000);
134 l_pr VARCHAR2(2000);
135 l_pt VARCHAR2(2000);
136 l_tot_men NUMBER;
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
146 --
147 -- SQL to count Full Time Instruction Staff With Tenure Status as "TENURED"
148 -- and "ON TENURE"
149 --
150 -- In Order to indexed columns we are inserting session id into tax unit id
151 --
152 INSERT INTO pay_us_rpt_totals(tax_unit_id
153 ,attribute1
154 ,attribute3
155 ,value1
156 ,value2
157 ,value3
158 ,value4
159 ,value5
160 ,value6
161 ,value7
162 ,value8
163 ,value9
164 ,value10
165 ,value11
166 ,value12
167 ,value13
168 ,value14
169 ,value15
170 ,value16
171 ,value17
172 ,value18
173 ,value19
174 ,value20
175 ,value21
176 ,value22
177 ,value30
178 )
179 SELECT userenv('sessionid')
180 ,'DGIPEDA'
181 ,org_med_type
182 ,DECODE(tenure_status,'01',tenure_status,'02') tenure
183 ,academic_rank
184 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
185 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
186 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
187 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
188 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
189 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
190 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
191 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
192 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
193 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
194 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
195 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
196 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
197 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
198 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
199 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
200 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
201 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
202 ,SUM(DECODE(gender,'M',1,0)) totmen
203 ,SUM(DECODE(gender,'F',1,0)) totwmen
204 ,ipeds_category
205 FROM(
206 SELECT peo.sex gender
207 ,job.job_information8 ipeds_category
208 ,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
209 ,ppet.pei_information1 tenure_status
210 ,ppea.pei_information1 academic_rank
211 ,peo.person_id
212 ,pqh_inst_type_pkg.get_inst_type(asg.organization_id) org_med_type
213 FROM per_all_people_f peo
214 ,per_all_assignments_f asg
215 ,per_assignment_status_types ast
216 ,per_jobs job
217 ,per_pay_proposals ppp
218 ,per_pay_bases ppb
219 ,per_people_extra_info ppea
220 ,per_people_extra_info ppet
221 WHERE peo.person_id = asg.person_id
222 AND peo.person_id = ppea.person_id
223 AND peo.person_id = ppet.person_id
224 AND ppea.information_type = 'PQH_ACADEMIC_RANK'
225 AND ppet.information_type = 'PQH_TENURE_STATUS'
226 AND p_report_date BETWEEN fnd_date.canonical_to_date(ppea.PEI_INFORMATION2)
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
236 AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
237 AND asg.primary_flag = 'Y'
238 AND asg.assignment_status_type_id = ast.assignment_status_type_id
239 AND ast.per_system_status <> 'TERM_ASSIGN'
240 AND asg.pay_basis_id = ppb.pay_basis_id
241 AND asg.assignment_id = ppp.assignment_id
242 AND ppp.change_date = (SELECT MAX(change_date)
243 FROM per_pay_proposals pro
244 WHERE ppp.assignment_id = pro.assignment_id
245 AND pro.change_date <= p_report_date
246 AND pro.approved = 'Y'
247 )
248 AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
249 AND asg.job_id = job.job_id
250 AND asg.assignment_type = 'E'
251 AND peo.business_group_id = p_business_group_id
252 AND EXISTS (SELECT 'Y'
253 FROM hr_all_organization_units hou
254 WHERE hou.business_group_id = p_business_group_id
255 AND hou.organization_id = asg.organization_id)
256 )
257 GROUP BY DECODE(tenure_status,'01',tenure_status,'02')
258 ,academic_rank
259 ,ipeds_category
260 ,org_med_type;
261 --
262 -- SQL to fetch Full Time Instruction Staff With Tenure Status as "NOT ON TENURE"
263 -- and on contract. Tenure codes generated for Employees with contract are
264 -- 030 - Multi Year Contract
265 -- 031 - Annual Contract
266 -- 032 - Less Than Annual Contract
267 --
268 -- In Order to indexed columns we are inserting session id into tax unit id
269 --
270 INSERT INTO pay_us_rpt_totals(tax_unit_id
271 ,attribute1
272 ,attribute3
273 ,value1
274 ,value2
275 ,value3
276 ,value4
277 ,value5
278 ,value6
279 ,value7
280 ,value8
281 ,value9
282 ,value10
283 ,value11
284 ,value12
285 ,value13
286 ,value14
287 ,value15
288 ,value16
289 ,value17
290 ,value18
291 ,value19
292 ,value20
293 ,value21
294 ,value22
295 ,value30
296 )
297 SELECT userenv('sessionid')
298 ,'DGIPEDA'
299 ,org_med_type
300 ,'03'||DECODE(SIGN(cont_dur_mon-12),1,0,0,1,2) tenure
301 ,academic_rank
302 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
303 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
304 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
305 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
306 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
307 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
308 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
309 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
310 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
311 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
312 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
313 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
314 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
315 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
316 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
317 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
318 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
319 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
320 ,SUM(DECODE(gender,'M',1,0)) TotMen
321 ,SUM(DECODE(gender,'F',1,0)) TotWmen
322 ,ipeds_category
323 FROM (
324 SELECT peo.sex gender
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
334 ,per_assignment_status_types ast
335 ,per_jobs job
336 ,per_pay_proposals ppp
337 ,per_pay_bases ppb
338 ,per_people_extra_info ppea
339 ,per_people_extra_info ppet
340 ,per_contracts_f pco
341 ,per_shared_types pst
342 ,per_shared_types pst1
343 WHERE peo.person_id = asg.person_id
344 AND peo.person_id = ppea.person_id
345 AND peo.person_id = ppet.person_id
346 AND ppea.information_type = 'PQH_ACADEMIC_RANK'
347 AND ppet.information_type = 'PQH_TENURE_STATUS'
348 AND p_report_date BETWEEN fnd_date.canonical_to_date(ppea.PEI_INFORMATION2)
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
358 AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
359 AND asg.primary_flag = 'Y'
360 AND asg.assignment_status_type_id = ast.assignment_status_type_id
361 AND ast.per_system_status <> 'TERM_ASSIGN'
362 AND asg.pay_basis_id = ppb.pay_basis_id
363 AND asg.assignment_id = ppp.assignment_id
364 AND ppp.change_date = (SELECT MAX(change_date)
365 FROM per_pay_proposals pro
366 WHERE ppp.assignment_id = pro.assignment_id
367 AND pro.change_date <= p_report_date
368 AND pro.approved = 'Y'
369 )
370 AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
371 AND asg.job_id = job.job_id
372 AND asg.assignment_type = 'E'
373 AND peo.business_group_id = p_business_group_id
374 AND pco.person_id = peo.person_id
375 AND pco.type = 'FULL_TIME'
376 AND pco.status = pst.system_type_cd
377 AND pst.lookup_type = 'CONTRACT_STATUS'
378 AND pst1.system_type_cd(+) = pst.system_type_cd
379 AND pst1.lookup_type(+) = pst.lookup_type
380 AND pst.business_group_id IS NULL
381 AND pst1.business_group_id(+) = p_business_group_id
382 AND NVL(pst1.information1,pst.information1) = 'Y'
383 AND EXISTS (SELECT 'Y'
384 FROM hr_all_organization_units hou
385 WHERE hou.business_group_id = p_business_group_id
386 AND hou.organization_id = asg.organization_id)
387 )
388 GROUP BY '03'||DECODE(SIGN(cont_dur_mon-12),1,0,0,1,2)
389 ,academic_rank
390 ,ipeds_category
391 ,org_med_type;
392
393
394
395 --
396 -- SQL to fetch Full Time Instruction Staff With No Tenure Status attached
397 -- This Cursor would be used only if establishment uses tenure system.
398 --In Order to indexed columns we are inserting session id into tax unit id
399 --
400 INSERT INTO pay_us_rpt_totals(tax_unit_id
401 ,attribute1
402 ,attribute3
403 ,value1
404 ,value2
405 ,value3
406 ,value4
407 ,value5
408 ,value6
409 ,value7
410 ,value8
411 ,value9
412 ,value10
413 ,value11
414 ,value12
415 ,value13
416 ,value14
417 ,value15
418 ,value16
419 ,value17
420 ,value18
421 ,value19
422 ,value20
423 ,value21
424 ,value22
425 ,value30
426 )
427 SELECT userenv('sessionid')
428 ,'DGIPEDA'
429 ,org_med_type
430 ,'4' tenure
431 ,NULL
432 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
433 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
434 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
435 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
436 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
437 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
438 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
439 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
440 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
441 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
442 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
443 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
444 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
445 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
446 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
447 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
448 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
449 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
450 ,SUM(DECODE(gender,'M',1,0)) TotMen
451 ,SUM(DECODE(gender,'F',1,0)) TotWmen
452 ,ipeds_category
453 FROM (
454 SELECT peo.sex gender
455 ,job.job_information8 ipeds_category
456 ,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
457 ,peo.person_id
458 ,pqh_inst_type_pkg.get_inst_type(asg.organization_id) org_med_type
459 FROM per_all_people_f peo
460 ,per_all_assignments_f asg
461 ,per_assignment_status_types ast
462 ,per_jobs job
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
472 AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
473 AND asg.primary_flag = 'Y'
474 AND asg.assignment_status_type_id = ast.assignment_status_type_id
475 AND ast.per_system_status <> 'TERM_ASSIGN'
476 AND asg.pay_basis_id = ppb.pay_basis_id
477 AND asg.assignment_id = ppp.assignment_id
478 AND ppp.change_date = (SELECT MAX(change_date)
479 FROM per_pay_proposals pro
480 WHERE ppp.assignment_id = pro.assignment_id
481 AND pro.change_date <= p_report_date
482 AND pro.approved = 'Y'
483 )
484 AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
485 AND asg.job_id = job.job_id
486 AND asg.assignment_type = 'E'
487 AND peo.business_group_id = p_business_group_id
488 AND EXISTS (SELECT 'Y'
489 FROM hr_all_organization_units hou
490 WHERE hou.business_group_id = p_business_group_id
491 AND hou.organization_id = asg.organization_id)
492 AND NOT EXISTS (SELECT 'Y'
493 FROM per_people_extra_info ppet
494 WHERE ppet.person_id = peo.person_id
495 AND ppet.information_type = 'PQH_TENURE_STATUS')
496 )
497 GROUP BY '4'
498 ,ipeds_category
499 ,org_med_type;
500
501 -- Check if Tenure system is not used by establishment
502 ELSIF p_tenured = 'N' THEN
503 --
504 -- SQL to fetch Full Time Instruction Staff With establishement without
505 -- tenure sytem and on contract. Tenure codes generated for Employees with
506 -- contract are
507 -- 030 - Multi Year Contract
508 -- 031 - Annual Contract
509 -- 032 - Less Than Annual Contract
510 -- In Order to indexed columns we are inserting session id into tax unit id
511 --
512 INSERT INTO pay_us_rpt_totals(tax_unit_id
513 ,attribute1
514 ,attribute3
515 ,value1
516 ,value2
517 ,value3
518 ,value4
519 ,value5
520 ,value6
521 ,value7
522 ,value8
523 ,value9
524 ,value10
525 ,value11
526 ,value12
527 ,value13
528 ,value14
529 ,value15
530 ,value16
531 ,value17
532 ,value18
533 ,value19
534 ,value20
535 ,value21
536 ,value22
537 ,value30
538 )
539 SELECT USERENV('sessionid')
540 ,'DGIPEDA'
541 ,org_med_type
542 ,'03'||DECODE(SIGN(cont_dur_mon-12),1,0,0,1,2) tenure
543 ,academic_rank
544 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
545 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
546 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
547 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
548 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
549 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
550 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
551 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
552 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
553 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
554 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
555 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
556 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
557 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
558 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
559 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
560 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
561 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
562 ,SUM(DECODE(gender,'M',1,0)) TotMen
563 ,SUM(DECODE(gender,'F',1,0)) TotWmen
564 ,ipeds_category
565 FROM (
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
575 ,per_assignment_status_types ast
576 ,per_jobs job
577 ,per_pay_proposals ppp
578 ,per_pay_bases ppb
579 ,per_people_extra_info ppea
580 ,per_contracts_f pco
581 ,per_shared_types pst
582 ,per_shared_types pst1
583 WHERE peo.person_id = asg.person_id
584 AND peo.person_id = ppea.person_id
585 AND ppea.information_type = 'PQH_ACADEMIC_RANK'
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
595 AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
596 AND asg.primary_flag = 'Y'
597 AND asg.assignment_status_type_id = ast.assignment_status_type_id
598 AND ast.per_system_status <> 'TERM_ASSIGN'
599 AND asg.pay_basis_id = ppb.pay_basis_id
600 AND asg.assignment_id = ppp.assignment_id
601 AND ppp.change_date = (SELECT MAX(change_date)
602 FROM per_pay_proposals pro
603 WHERE ppp.assignment_id = pro.assignment_id
604 AND pro.change_date <= p_report_date
605 AND pro.approved = 'Y'
606 )
607 AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
608 AND asg.job_id = job.job_id
609 AND asg.assignment_type = 'E'
610 AND peo.business_group_id = p_business_group_id
611 AND pco.person_id = peo.person_id
612 AND pco.type = 'FULL_TIME'
613 AND pco.status = pst.system_type_cd
614 AND pst.lookup_type = 'CONTRACT_STATUS'
615 AND pst1.system_type_cd(+) = pst.system_type_cd
616 AND pst1.lookup_type(+) = pst.lookup_type
617 AND pst.business_group_id IS NULL
618 AND pst1.business_group_id(+) = p_business_group_id
619 AND NVL(pst1.information1,pst.information1) = 'Y'
620 AND EXISTS (SELECT 'Y'
621 FROM hr_all_organization_units hou
622 WHERE hou.business_group_id = p_business_group_id
623 AND hou.organization_id = asg.organization_id)
624 )
625 GROUP BY '03'||DECODE(SIGN(cont_dur_mon-12),1,0,0,1,2)
626 ,academic_rank
627 ,ipeds_category
628 ,org_med_type;
629
630 --
631 -- Cursor to fetch Full Time Instruction Staff With No Contract attached.
632 -- This Cursor would be used only if establishment doesn't use tenure system.
633 --
634 -- In Order to indexed columns we are inserting session id into tax unit id
635 --
636 INSERT INTO pay_us_rpt_totals(tax_unit_id
637 ,attribute1
638 ,attribute3
639 ,value1
640 ,value2
641 ,value3
642 ,value4
643 ,value5
644 ,value6
645 ,value7
646 ,value8
647 ,value9
648 ,value10
649 ,value11
650 ,value12
651 ,value13
652 ,value14
653 ,value15
654 ,value16
655 ,value17
656 ,value18
657 ,value19
658 ,value20
659 ,value21
660 ,value22
661 ,value30
662 )
663 SELECT userenv('sessionid')
664 ,'DGIPEDA'
665 ,org_med_type
666 ,'4' tenure
667 ,NULL
668 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
669 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
670 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
671 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
672 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
673 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
674 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
675 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
676 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
677 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
678 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
679 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
680 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
681 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
682 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
683 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
684 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
685 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
686 ,SUM(DECODE(gender,'M',1,0)) TotMen
687 ,SUM(DECODE(gender,'F',1,0)) TotWmen
688 ,ipeds_category
689 FROM (
690 SELECT peo.sex gender
691 ,job.job_information8 ipeds_category
692 ,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
693 ,peo.person_id
694 ,pqh_inst_type_pkg.get_inst_type(asg.organization_id) org_med_type
695 FROM per_all_people_f peo
696 ,per_all_assignments_f asg
697 ,per_assignment_status_types ast
698 ,per_jobs job
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
708 AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
709 AND asg.primary_flag = 'Y'
710 AND asg.assignment_status_type_id = ast.assignment_status_type_id
711 AND ast.per_system_status <> 'TERM_ASSIGN'
712 AND asg.pay_basis_id = ppb.pay_basis_id
713 AND asg.assignment_id = ppp.assignment_id
714 AND ppp.change_date = (SELECT MAX(change_date)
715 FROM per_pay_proposals pro
716 WHERE ppp.assignment_id = pro.assignment_id
717 AND pro.change_date <= p_report_date
718 AND pro.approved = 'Y'
719 )
720 AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
721 AND asg.job_id = job.job_id
722 AND asg.assignment_type = 'E'
723 AND peo.business_group_id = p_business_group_id
724 AND EXISTS (SELECT 'Y'
725 FROM hr_all_organization_units hou
726 WHERE hou.business_group_id = p_business_group_id
727 AND hou.organization_id = asg.organization_id)
728 AND NOT EXISTS (SELECT 'Y'
729 FROM per_contracts_f pco
730 WHERE pco.person_id = peo.person_id
731 AND p_report_date BETWEEN pco.effective_start_date AND pco.effective_end_date)
732 )
733 GROUP BY '4'
734 ,ipeds_category
735 ,org_med_type;
736 END IF;
737
738 RETURN TRUE;
739 END deg_gnt_parta_bef_rpt;
740 --
741 /***************************************************************************
742 Name : deg_gnt_partb_e_bef_rpt
743 Type : Function
744 Return Type : Boolean
745 Description : Populates the PER_US_RPT_TOTALS with information required to
746 generate the IPEDS Part B and E based on report name. For
747 Part B employement category is 'Full Time' and for Part E
748 employement category is 'Part Time'
749 *****************************************************************************/
750 FUNCTION deg_gnt_partb_e_bef_rpt(p_report_name VARCHAR2)
751 RETURN boolean
752 IS
753
754 l_fr VARCHAR2(2000);
755 l_ft VARCHAR2(2000);
756 l_pr VARCHAR2(2000);
757 l_pt VARCHAR2(2000);
758 l_emp_category VARCHAR2(2);
759 l_ipeds_cat VARCHAR2(2000);
760 l_tot_men NUMBER;
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
770 -- categories except 'None'
771 IF p_report_name = 'DGIPEDB' THEN
772 l_emp_category := 'FR';
773 l_ipeds_cat := '12'||','||'21'||','|| '22'||','|| '23'||','||'24';
774 ELSE
775 l_emp_category := 'PR'; -- For part E
776 l_ipeds_cat := '12';
777 END IF;
778
779 -- Check if Tenure system is used by establishment
780 IF p_tenured = 'Y' THEN
781 --
782 -- SQL to fetch Staff With Tenure Status as "TENURED" and "ON TENURE".
783 -- Count is based on IPEDS job category and Employement Category.
784 --
785 -- In Order to indexed columns we are inserting session id into tax unit id
786 --
787 INSERT INTO pay_us_rpt_totals(tax_unit_id
788 ,attribute1
789 ,attribute3
790 ,value1
791 ,value3
792 ,value4
793 ,value5
794 ,value6
795 ,value7
796 ,value8
797 ,value9
798 ,value10
799 ,value11
800 ,value12
801 ,value13
802 ,value14
803 ,value15
804 ,value16
805 ,value17
806 ,value18
807 ,value19
808 ,value20
809 ,value21
810 ,value22
811 ,value30
812 )
813 SELECT USERENV('sessionid')
814 ,p_report_name
815 ,org_med_type
816 ,DECODE(tenure_status,'01',tenure_status,'02') tenure
817 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
818 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
819 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
820 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
821 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
822 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
823 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
824 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
825 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
826 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
827 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
828 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
829 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
830 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
831 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
832 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
833 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
834 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
835 ,SUM(DECODE(gender,'M',1,0)) TotMen
836 ,SUM(DECODE(gender,'F',1,0)) TotWmen
837 ,ipeds_category
838 FROM (
839 SELECT peo.sex gender
840 ,job.job_information8 ipeds_category
841 ,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
842 ,ppet.pei_information1 tenure_status
843 ,peo.person_id
844 ,pqh_inst_type_pkg.get_inst_type(asg.organization_id) org_med_type
845 FROM per_all_people_f peo
846 ,per_all_assignments_f asg
847 ,per_assignment_status_types ast
848 ,per_jobs job
849 ,per_pay_proposals ppp
850 ,per_pay_bases ppb
851 ,per_people_extra_info ppet
852 WHERE peo.person_id = asg.person_id
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
862 AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
863 AND asg.primary_flag = 'Y'
864 AND asg.assignment_status_type_id = ast.assignment_status_type_id
865 AND ast.per_system_status <> 'TERM_ASSIGN'
866 AND asg.pay_basis_id = ppb.pay_basis_id
867 AND asg.assignment_id = ppp.assignment_id
868 AND ppp.change_date = (SELECT MAX(change_date)
869 FROM per_pay_proposals pro
870 WHERE ppp.assignment_id = pro.assignment_id
871 AND pro.change_date <= p_report_date
872 AND pro.approved = 'Y'
873 )
874 AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
875 AND asg.job_id = job.job_id
876 AND asg.assignment_type = 'E'
877 AND peo.business_group_id = p_business_group_id
878 AND EXISTS (SELECT 'Y'
879 FROM hr_all_organization_units hou
880 WHERE hou.business_group_id = p_business_group_id
881 AND hou.organization_id = asg.organization_id)
882 )
883 GROUP BY DECODE(tenure_status,'01',tenure_status,'02')
884 ,ipeds_category
885 ,org_med_type;
886
887 --
888 -- SQL to fetch Staff With Tenure Status as "NOT ON TENURE" and on contract.
889 -- Tenure codes generated for Employees with contract are
890 -- 030 - Multi Year Contract
891 -- 031 - Annual Contract
892 -- 032 - Less Than Annual Contract
893 -- Count is based on IPEDS job category and Employement Category.
894 -- In Order to indexed columns we are inserting session id into tax unit id
895 --
896 INSERT INTO pay_us_rpt_totals(tax_unit_id
897 ,attribute1
898 ,attribute3
899 ,value1
900 ,value3
901 ,value4
902 ,value5
903 ,value6
904 ,value7
905 ,value8
906 ,value9
907 ,value10
908 ,value11
909 ,value12
910 ,value13
911 ,value14
912 ,value15
913 ,value16
914 ,value17
915 ,value18
916 ,value19
917 ,value20
918 ,value21
919 ,value22
920 ,value30
921 )
922 SELECT USERENV('sessionid')
923 ,p_report_name
924 ,org_med_type
925 ,'03'||DECODE(SIGN(cont_dur_mon-12),1,0,0,1,2) tenure
926 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
927 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
928 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
929 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
930 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
931 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
932 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
933 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
934 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
935 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
936 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
937 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
938 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
939 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
940 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
941 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
942 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
943 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
944 ,SUM(DECODE(gender,'M',1,0)) TotMen
945 ,SUM(DECODE(gender,'F',1,0)) TotWmen
946 ,ipeds_category
947 FROM (
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
957 ,per_assignment_status_types ast
958 ,per_jobs job
959 ,per_pay_proposals ppp
960 ,per_pay_bases ppb
961 ,per_people_extra_info ppet
962 ,per_contracts_f pco
963 ,per_shared_types pst
964 ,per_shared_types pst1
965 WHERE peo.person_id = asg.person_id
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
975 AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
976 AND asg.primary_flag = 'Y'
977 AND asg.assignment_status_type_id = ast.assignment_status_type_id
978 AND ast.per_system_status <> 'TERM_ASSIGN'
979 AND asg.pay_basis_id = ppb.pay_basis_id
980 AND asg.assignment_id = ppp.assignment_id
981 AND ppp.change_date = (SELECT MAX(change_date)
982 FROM per_pay_proposals pro
983 WHERE ppp.assignment_id = pro.assignment_id
984 AND pro.change_date <= p_report_date
985 AND pro.approved = 'Y'
986 )
987 AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
988 AND asg.job_id = job.job_id
989 AND asg.assignment_type = 'E'
990 AND peo.business_group_id = p_business_group_id
991 AND pco.person_id = peo.person_id
992 AND pco.type = 'FULL_TIME'
993 AND pco.status = pst.system_type_cd
994 AND pst.lookup_type = 'CONTRACT_STATUS'
995 AND pst1.system_type_cd(+) = pst.system_type_cd
996 AND pst1.lookup_type(+) = pst.lookup_type
997 AND pst.business_group_id is null
998 AND pst1.business_group_id(+) = p_business_group_id
999 AND nvl(pst1.information1,pst.information1) = 'Y'
1000 AND EXISTS (SELECT 'Y'
1001 FROM hr_all_organization_units hou
1002 WHERE hou.business_group_id = p_business_group_id
1003 AND hou.organization_id = asg.organization_id)
1004 )
1005 GROUP BY '03'||DECODE(SIGN(cont_dur_mon-12),1,0,0,1,2)
1006 ,ipeds_category
1007 ,org_med_type;
1008
1009 --
1010 -- SQL to fetch Staff With No Tenure(Without faculty) Status attached.
1011 -- This Cursor would be used only if establishment uses tenure system.
1012 -- Count is based on IPEDS job category and Employement Category.
1013 -- In Order to indexed columns we are inserting session id into tax unit id
1014 --
1015 INSERT INTO pay_us_rpt_totals(tax_unit_id
1016 ,attribute1
1017 ,attribute3
1018 ,value1
1019 ,value3
1020 ,value4
1021 ,value5
1022 ,value6
1023 ,value7
1024 ,value8
1025 ,value9
1026 ,value10
1027 ,value11
1028 ,value12
1029 ,value13
1030 ,value14
1031 ,value15
1032 ,value16
1033 ,value17
1034 ,value18
1035 ,value19
1036 ,value20
1037 ,value21
1038 ,value22
1039 ,value30
1040 )
1041 SELECT USERENV('sessionid')
1042 ,p_report_name
1043 ,org_med_type
1044 ,'04' tenure
1045 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
1046 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
1047 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
1048 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
1049 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
1050 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
1051 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
1052 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
1053 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
1054 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
1055 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
1056 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
1057 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
1058 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
1059 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
1060 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
1061 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
1062 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
1063 ,SUM(DECODE(gender,'M',1,0)) TotMen
1064 ,SUM(DECODE(gender,'F',1,0)) TotWmen
1065 ,ipeds_category
1066 FROM (
1067 SELECT peo.sex gender
1068 ,job.job_information8 ipeds_category
1069 ,peo.person_id person_id
1070 ,pqh_inst_type_pkg.get_inst_type(asg.organization_id) org_med_type
1071 ,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
1072 FROM per_all_people_f peo
1073 ,per_all_assignments_f asg
1074 ,per_assignment_status_types ast
1075 ,per_jobs job
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
1085 AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
1086 AND asg.primary_flag = 'Y'
1087 AND asg.assignment_status_type_id = ast.assignment_status_type_id
1088 AND ast.per_system_status <> 'TERM_ASSIGN'
1089 AND asg.pay_basis_id = ppb.pay_basis_id
1090 AND asg.assignment_id = ppp.assignment_id
1091 AND ppp.change_date = (SELECT MAX(change_date)
1092 FROM per_pay_proposals pro
1093 WHERE ppp.assignment_id = pro.assignment_id
1094 AND pro.change_date <= p_report_date
1095 AND pro.approved = 'Y'
1096 )
1097 AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
1098 AND asg.job_id = job.job_id
1099 AND asg.assignment_type = 'E'
1100 AND peo.business_group_id = p_business_group_id
1101 AND EXISTS (SELECT 'Y'
1102 FROM hr_all_organization_units hou
1103 WHERE hou.business_group_id = p_business_group_id
1104 AND hou.organization_id = asg.organization_id)
1105 AND NOT EXISTS( SELECT 'Y'
1106 FROM per_people_extra_info ppet
1107 WHERE ppet.person_id = peo.person_id
1108 AND ppet.information_type = 'PQH_TENURE_STATUS')
1109 )
1110 GROUP BY '04'
1111 ,ipeds_category
1112 ,org_med_type;
1113
1114 -- Check if Tenure system is not used by establishment
1115 ELSIF p_tenured = 'N' THEN
1116
1117 --
1118 -- Cursor to fetch With establishement without tenure sytem and on contract.
1119 -- Tenure codes generated for Employees with contract are
1120 -- 030 - Multi Year Contract
1121 -- 031 - Annual Contract
1122 -- 032 - Less Than Annual Contract
1123 -- Count is based on IPEDS job category and Employement Category.
1124 -- In Order to indexed columns we are inserting session id into tax unit id
1125 --
1126 INSERT INTO pay_us_rpt_totals(tax_unit_id
1127 ,attribute1
1128 ,attribute3
1129 ,value1
1130 ,value3
1131 ,value4
1132 ,value5
1133 ,value6
1134 ,value7
1135 ,value8
1136 ,value9
1137 ,value10
1138 ,value11
1139 ,value12
1140 ,value13
1141 ,value14
1142 ,value15
1143 ,value16
1144 ,value17
1145 ,value18
1146 ,value19
1147 ,value20
1148 ,value21
1149 ,value22
1150 ,value30
1151 )
1152 SELECT USERENV('sessionid')
1153 ,p_report_name
1154 ,org_med_type
1155 ,'03'||DECODE(SIGN(cont_dur_mon-12),1,0,0,1,2) tenure
1156 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
1157 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
1158 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
1159 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
1160 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
1161 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
1162 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
1163 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
1164 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
1165 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
1166 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
1167 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
1168 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
1169 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
1170 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
1171 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
1172 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
1173 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
1174 ,SUM(DECODE(gender,'M',1,0)) TotMen
1175 ,SUM(DECODE(gender,'F',1,0)) TotWmen
1176 ,ipeds_category
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
1186 ,per_assignment_status_types ast
1187 ,per_jobs job
1188 ,per_pay_proposals ppp
1189 ,per_pay_bases ppb
1190 ,per_contracts_f pco
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
1200 AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
1201 AND asg.primary_flag = 'Y'
1202 AND asg.assignment_status_type_id = ast.assignment_status_type_id
1203 AND ast.per_system_status <> 'TERM_ASSIGN'
1204 AND asg.pay_basis_id = ppb.pay_basis_id
1205 AND asg.assignment_id = ppp.assignment_id
1206 AND ppp.change_date = (SELECT MAX(change_date)
1207 FROM per_pay_proposals pro
1208 WHERE ppp.assignment_id = pro.assignment_id
1209 AND pro.change_date <= p_report_date
1210 AND pro.approved = 'Y'
1211 )
1212 AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
1213 AND asg.job_id = job.job_id
1214 AND asg.assignment_type = 'E'
1215 AND peo.business_group_id = p_business_group_id
1216 AND pco.person_id = peo.person_id
1217 AND pco.type = 'FULL_TIME'
1218 AND pco.status = pst.system_type_cd
1219 AND pst.lookup_type = 'CONTRACT_STATUS'
1220 AND pst1.system_type_cd(+) = pst.system_type_cd
1221 AND pst1.lookup_type(+) = pst.lookup_type
1222 AND pst.business_group_id is null
1223 AND pst1.business_group_id(+) = p_business_group_id
1224 AND nvl(pst1.information1,pst.information1) = 'Y'
1225 AND EXISTS (SELECT 'Y'
1226 FROM hr_all_organization_units hou
1227 WHERE hou.business_group_id = p_business_group_id
1228 AND hou.organization_id = asg.organization_id)
1229 )
1230 GROUP BY '03'||DECODE(SIGN(cont_dur_mon-12),1,0,0,1,2)
1231 ,ipeds_category
1232 ,org_med_type;
1233
1234 --
1235 -- Cursor to fetch Staff With No Contract(Without faculty) attached.
1236 -- This Cursor would be used only if establishment doesn't use
1237 -- tenure system. Count is based on IPEDS job category and Employement
1238 -- Category.
1239 -- In Order to indexed columns we are inserting session id into tax unit id
1240 --
1241 INSERT INTO pay_us_rpt_totals(tax_unit_id
1242 ,attribute1
1243 ,attribute3
1244 ,value1
1245 ,value3
1246 ,value4
1247 ,value5
1248 ,value6
1249 ,value7
1250 ,value8
1251 ,value9
1252 ,value10
1253 ,value11
1254 ,value12
1255 ,value13
1256 ,value14
1257 ,value15
1258 ,value16
1259 ,value17
1260 ,value18
1261 ,value19
1262 ,value20
1263 ,value21
1264 ,value22
1265 ,value30
1266 )
1267 SELECT USERENV('sessionid')
1268 ,p_report_name
1269 ,org_med_type
1270 ,'04' tenure
1271 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
1272 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
1273 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
1274 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
1275 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
1276 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
1277 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
1278 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
1279 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
1280 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
1281 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
1282 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
1283 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
1284 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
1285 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
1286 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
1287 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
1288 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
1289 ,SUM(DECODE(gender,'M',1,0)) TotMen
1290 ,SUM(DECODE(gender,'F',1,0)) TotWmen
1291 ,ipeds_category
1292 FROM (
1293 SELECT peo.sex gender
1294 ,job.job_information8 ipeds_category
1295 ,peo.person_id person_id
1296 ,pqh_inst_type_pkg.get_inst_type(asg.organization_id) org_med_type
1297 ,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
1298 FROM per_all_people_f peo
1299 ,per_all_assignments_f asg
1300 ,per_assignment_status_types ast
1301 ,per_jobs job
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
1311 AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
1312 AND asg.primary_flag = 'Y'
1313 AND asg.assignment_status_type_id = ast.assignment_status_type_id
1314 AND ast.per_system_status <> 'TERM_ASSIGN'
1315 AND asg.pay_basis_id = ppb.pay_basis_id
1316 AND asg.assignment_id = ppp.assignment_id
1317 AND ppp.change_date = (SELECT MAX(change_date)
1318 FROM per_pay_proposals pro
1319 WHERE ppp.assignment_id = pro.assignment_id
1320 AND pro.change_date <= p_report_date
1321 AND pro.approved = 'Y'
1322 )
1323 AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
1324 AND asg.job_id = job.job_id
1325 AND asg.assignment_type = 'E'
1326 AND peo.business_group_id = p_business_group_id
1327 AND EXISTS (SELECT 'Y'
1328 FROM hr_all_organization_units hou
1329 WHERE hou.business_group_id = p_business_group_id
1330 AND hou.organization_id = asg.organization_id)
1331 AND NOT EXISTS( SELECT 'Y'
1332 FROM per_contracts_f pco
1333 WHERE pco.person_id = peo.person_id
1334 AND p_report_date BETWEEN pco.effective_start_date AND pco.effective_end_date)
1335 )
1336 GROUP BY '04'
1337 ,ipeds_category
1338 ,org_med_type;
1339 END IF;
1340 RETURN TRUE;
1341 END deg_gnt_partb_e_bef_rpt;
1342 --
1343 /***************************************************************************
1344 Name : deg_gnt_partd_bef_rpt
1345 Type : Function
1346 Return Type : Boolean
1347 Description : Populates the PER_US_RPT_TOTALS with information required to
1348 generate the IPEDS Part D report
1349 *****************************************************************************/
1350 FUNCTION deg_gnt_partd_bef_rpt
1351 RETURN BOOLEAN
1352 IS
1353
1354 l_fr VARCHAR2(2000);
1355 l_ft VARCHAR2(2000);
1356 l_pr VARCHAR2(2000);
1357 l_pt VARCHAR2(2000);
1358 l_tot_men NUMBER;
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
1368 -- In Order to indexed columns we are inserting session id into tax unit id
1369 --
1370 INSERT INTO pay_us_rpt_totals(tax_unit_id
1371 ,attribute1
1372 ,value3
1373 ,value4
1374 ,value5
1375 ,value6
1376 ,value7
1377 ,value8
1378 ,value9
1379 ,value10
1380 ,value11
1381 ,value12
1382 ,value13
1383 ,value14
1384 ,value15
1385 ,value16
1386 ,value17
1387 ,value18
1388 ,value19
1389 ,value20
1390 ,value21
1391 ,value22
1392 ,value30
1393 )
1394 /* All the Instructional staff needs to be reported under one columns so
1395 IPEDS category codes 21 - 24 is converted into one code 21 and the label
1396 of column would be replaced accordingly in XML data definition*/
1397 SELECT USERENV('sessionid')
1398 ,'DGIPEDD'
1399 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
1400 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
1401 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
1402 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
1403 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
1404 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
1405 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
1406 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
1407 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
1408 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
1409 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
1410 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
1411 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
1412 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
1413 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
1414 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
1415 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
1416 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
1417 ,SUM(DECODE(gender,'M',1,0)) TotMen
1418 ,SUM(DECODE(gender,'F',1,0)) TotWmen
1419 ,DECODE(ipeds_code,21,21,22,21,23,21,24,21,ipeds_code) ipeds_category
1420 FROM (
1421 SELECT peo.sex gender
1422 ,job.job_information8 ipeds_code
1423 ,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
1424 ,peo.person_id
1425 FROM per_all_people_f peo
1426 ,per_all_assignments_f asg
1427 ,per_assignment_status_types ast
1428 ,per_jobs job
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
1438 AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
1439 AND asg.primary_flag = 'Y'
1440 AND asg.assignment_status_type_id = ast.assignment_status_type_id
1441 AND ast.per_system_status <> 'TERM_ASSIGN'
1442 AND asg.pay_basis_id = ppb.pay_basis_id
1443 AND asg.assignment_id = ppp.assignment_id
1444 AND ppp.change_date = (SELECT MAX(change_date)
1445 FROM per_pay_proposals pro
1446 WHERE ppp.assignment_id = pro.assignment_id
1447 AND pro.change_date <= p_report_date
1448 AND pro.approved = 'Y'
1449 )
1450 AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
1451 AND asg.job_id = job.job_id
1452 AND asg.assignment_type = 'E'
1453 AND peo.business_group_id = p_business_group_id
1454 AND EXISTS (SELECT 'Y'
1455 FROM hr_all_organization_units hou
1456 WHERE hou.business_group_id = p_business_group_id
1457 AND hou.organization_id = asg.organization_id)
1458 )
1459 GROUP BY DECODE(ipeds_code,21,21,22,21,23,21,24,21,ipeds_code);
1460
1461 RETURN TRUE;
1462 END deg_gnt_partd_bef_rpt;
1463
1464 /***************************************************************************
1465 Name : deg_gnt_partg_bef_rpt
1466 Type : Function
1467 Return Type : Boolean
1468 Description : Populates the PER_US_RPT_TOTALS with information required to
1469 generate the IPEDS Part G report
1470 *****************************************************************************/
1471 FUNCTION deg_gnt_partg_bef_rpt
1472 RETURN boolean
1473 IS
1474 l_fr varchar2(2000);
1475 l_ft varchar2(2000);
1476 l_pr varchar2(2000);
1477 l_pt varchar2(2000);
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
1487 INSERT INTO pay_us_rpt_totals(tax_unit_id
1488 ,attribute1
1489 ,attribute2
1490 ,value1
1491 ,value2
1492 ,value3
1493 ,value4
1494 ,value5
1495 ,value6
1496 ,value7
1497 ,value8
1498 ,value9
1499 ,value10
1500 ,value11
1501 ,value12
1502 ,value13
1503 )
1504 SELECT userenv('sessionid')
1505 ,'DGIPEDG'
1506 ,'PART1'
1507 ,academic_rank
1508 ,SUM(DECODE(cont_dur_mon, 9,DECODE(gender,'M',1,0),0))
1509 ,SUM(DECODE(cont_dur_mon,10,DECODE(gender,'M',1,0),0))
1510 ,SUM(DECODE(cont_dur_mon,11,DECODE(gender,'M',1,0),0))
1511 ,SUM(DECODE(cont_dur_mon,12,DECODE(gender,'M',1,0),0))
1512 ,SUM(DECODE(cont_dur_mon, 9,DECODE(gender,'F',1,0),0))
1513 ,SUM(DECODE(cont_dur_mon,10,DECODE(gender,'F',1,0),0))
1514 ,SUM(DECODE(cont_dur_mon,11,DECODE(gender,'F',1,0),0))
1515 ,SUM(DECODE(cont_dur_mon,12,DECODE(gender,'F',1,0),0))
1516 ,SUM(DECODE(gender,'M',1,0))
1517 ,SUM(DECODE(gender,'F',1,0))
1518 ,SUM(DECODE(gender,'M',annual_sal,0))
1519 ,SUM(DECODE(gender,'F',annual_sal,0))
1520 FROM
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
1530 ,per_jobs job
1531 ,per_pay_proposals ppp
1532 ,per_pay_bases ppb
1533 ,per_people_extra_info ppea
1534 ,per_contracts_f pco
1535 ,per_shared_types pst
1536 ,per_shared_types pst1
1537 WHERE peo.person_id = asg.person_id
1538 AND peo.person_id = ppea.person_id
1539 AND ppea.information_type = 'PQH_ACADEMIC_RANK'
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')
1549 AND p_report_date BETWEEN peo.effective_start_date AND peo.effective_end_date
1550 AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
1551 AND asg.primary_flag = 'Y'
1552 AND asg.assignment_status_type_id = ast.assignment_status_type_id
1553 AND ast.per_system_status <> 'TERM_ASSIGN'
1554 AND asg.pay_basis_id = ppb.pay_basis_id
1555 AND asg.assignment_id = ppp.assignment_id
1556 AND ppp.change_date = (SELECT MAX(change_date)
1557 FROM per_pay_proposals pro
1558 WHERE ppp.assignment_id = pro.assignment_id
1559 AND pro.change_date <= p_report_date
1560 AND pro.approved = 'Y'
1561 )
1562 AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
1563 AND asg.job_id = job.job_id
1564 AND asg.assignment_type = 'E'
1565 AND peo.business_group_id = p_business_group_id
1566 AND pco.person_id = peo.person_id
1567 AND pco.type = 'FULL_TIME'
1568 AND pco.status = pst.system_type_cd
1569 AND pst.lookup_type = 'CONTRACT_STATUS'
1570 AND pst1.system_type_cd(+) = pst.system_type_cd
1571 AND pst1.lookup_type(+) = pst.lookup_type
1572 AND pst.business_group_id IS NULL
1573 AND pst1.business_group_id(+) = p_business_group_id
1574 AND NVL(pst1.information1,pst.information1) = 'Y'
1575 AND EXISTS (SELECT 'Y'
1576 FROM hr_all_organization_units hou
1577 WHERE hou.business_group_id = p_business_group_id
1578 AND hou.organization_id = asg.organization_id)
1579 )
1580 GROUP BY academic_rank;
1581
1582 -- Query to insert count and total salary of non instructional staff with 9 to 12 contract
1583 INSERT INTO pay_us_rpt_totals(tax_unit_id
1584 ,attribute1
1585 ,attribute2
1586 ,value1
1587 ,value2
1588 ,value3
1589 )
1590 SELECT userenv('sessionid')
1591 ,'DGIPEDG'
1592 ,'PART2'
1593 /* As We have to report categories 27,28,29,30 into one so changed the codes to
1594 one code 27. In XML meaning would be changed accordingly*/
1595 ,DECODE(job.job_information8,28,27,29,27,30,27,job.job_information8) ipeds_category
1596 ,COUNT(peo.person_id)
1597 ,SUM((NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor)) annual_sal
1598 FROM per_all_people_f peo
1599 ,per_all_assignments_f asg
1600 ,per_assignment_status_types ast
1601 ,per_jobs job
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
1611 AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
1612 AND asg.primary_flag = 'Y'
1613 AND asg.assignment_status_type_id = ast.assignment_status_type_id
1614 AND ast.per_system_status <> 'TERM_ASSIGN'
1615 AND asg.pay_basis_id = ppb.pay_basis_id
1616 AND asg.assignment_id = ppp.assignment_id
1617 AND ppp.change_date = (SELECT MAX(change_date)
1618 FROM per_pay_proposals pro
1619 WHERE ppp.assignment_id = pro.assignment_id
1620 AND pro.change_date <= p_report_date
1621 AND pro.approved = 'Y'
1622 )
1623 AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
1624 AND asg.job_id = job.job_id
1625 AND asg.assignment_type = 'E'
1626 AND peo.business_group_id = p_business_group_id
1627 AND EXISTS (SELECT 'Y'
1628 FROM hr_all_organization_units hou
1629 WHERE hou.business_group_id = p_business_group_id
1630 AND hou.organization_id = asg.organization_id)
1631 GROUP BY DECODE(job.job_information8,28,27,29,27,30,27,job.job_information8);
1632
1633 RETURN TRUE;
1634
1635 END deg_gnt_partg_bef_rpt;
1636
1637 /***************************************************************************
1638 Name : deg_gnt_parth_bef_rpt
1639 Type : Function
1640 Return Type : Boolean
1641 Description : Populates the PER_US_RPT_TOTALS with information required to
1642 generate the IPEDS Part H report
1643 *****************************************************************************/
1644
1645 FUNCTION deg_gnt_parth_bef_rpt
1646 RETURN BOOLEAN
1647 IS
1648 l_fr VARCHAR2(2000);
1649 l_ft VARCHAR2(2000);
1650 l_pr VARCHAR2(2000);
1651 l_pt VARCHAR2(2000);
1652 l_tot_men NUMBER;
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
1662 -- SQL to fetch Full Time Instruction Staff
1663 -- and "ON TENURE" hired in last 3 months
1664 -- In Order to indexed columns we are inserting session id into tax unit id
1665
1666 INSERT INTO pay_us_rpt_totals(tax_unit_id
1667 ,attribute1
1668 ,attribute2
1669 ,value1
1670 ,value2
1671 ,value3
1672 ,value4
1673 ,value5
1674 ,value6
1675 ,value7
1676 ,value8
1677 ,value9
1678 ,value10
1679 ,value11
1680 ,value12
1681 ,value13
1682 ,value14
1683 ,value15
1684 ,value16
1685 ,value17
1686 ,value18
1687 ,value19
1688 ,value20
1689 ,value21
1690 ,value30
1691 )
1692 SELECT USERENV('sessionid')
1693 ,'DGIPEDH'
1694 ,'SECTION1'
1695 ,DECODE(tenure_status,'01',tenure_status,'02') tenure
1696 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
1697 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
1698 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
1699 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
1700 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
1701 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
1702 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
1703 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
1704 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
1705 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
1706 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
1707 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
1708 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
1709 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
1710 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
1711 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
1712 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
1713 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
1714 ,SUM(DECODE(gender,'M',1,0)) TotMen
1715 ,SUM(DECODE(gender,'F',1,0)) TotWmen
1716 ,ipeds_category
1717 FROM (
1718 SELECT peo.sex gender
1719 ,job.job_information8 ipeds_category
1720 ,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
1721 ,ppet.pei_information1 tenure_status
1722 ,peo.person_id
1723 FROM per_all_people_f peo
1724 ,per_all_assignments_f asg
1725 ,per_assignment_status_types ast
1726 ,per_jobs job
1727 ,per_pay_proposals ppp
1728 ,per_pay_bases ppb
1729 ,per_people_extra_info ppet
1730 ,per_periods_of_service pps
1731 WHERE peo.person_id = asg.person_id
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
1741 AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
1742 AND asg.primary_flag = 'Y'
1743 AND asg.assignment_status_type_id = ast.assignment_status_type_id
1744 AND ast.per_system_status <> 'TERM_ASSIGN'
1745 AND asg.pay_basis_id = ppb.pay_basis_id
1746 AND asg.assignment_id = ppp.assignment_id
1747 AND ppp.change_date = (SELECT MAX(change_date)
1748 FROM per_pay_proposals pro
1749 WHERE ppp.assignment_id = pro.assignment_id
1750 AND pro.change_date <= p_report_date
1751 AND pro.approved = 'Y'
1752 )
1753 AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
1754 AND asg.job_id = job.job_id
1755 AND asg.assignment_type = 'E'
1756 AND asg.period_of_service_id = pps.period_of_service_id
1757 AND pps.date_start BETWEEN ADD_MONTHS(p_report_date,-4) AND (p_report_date-1)
1758 AND peo.business_group_id = p_business_group_id
1759 AND EXISTS (SELECT 'Y'
1760 FROM hr_all_organization_units hou
1761 WHERE hou.business_group_id = p_business_group_id
1762 AND hou.organization_id = asg.organization_id)
1763 )
1764 GROUP BY DECODE(tenure_status,'01',tenure_status,'02')
1765 ,ipeds_category;
1766
1767 --
1768 -- SQL to fetch Full Time Instruction Staff hired in last 3 months With
1769 -- Tenure Status as "NOT ON TENURE" and on contract. Tenure codes generated
1770 -- for Employees with contract are
1771 -- 030 - Multi Year Contract
1772 -- 031 - Annual Contract
1773 -- 032 - Less Than Annual Contract
1774 --
1775 -- In Order to indexed columns we are inserting session id into tax unit id
1776 --
1777 INSERT INTO pay_us_rpt_totals(tax_unit_id
1778 ,attribute1
1779 ,attribute2
1780 ,value1
1781 ,value2
1782 ,value3
1783 ,value4
1784 ,value5
1785 ,value6
1786 ,value7
1787 ,value8
1788 ,value9
1789 ,value10
1790 ,value11
1791 ,value12
1792 ,value13
1793 ,value14
1794 ,value15
1795 ,value16
1796 ,value17
1797 ,value18
1798 ,value19
1799 ,value20
1800 ,value21
1801 ,value30
1802 )
1803 SELECT USERENV('sessionid')
1804 ,'DGIPEDH'
1805 ,'SECTION1'
1806 ,'03'||DECODE(SIGN(cont_dur_mon-12),1,0,0,1,2) tenure
1807 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
1808 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
1809 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
1810 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
1811 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
1812 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
1813 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
1814 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
1815 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
1816 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
1817 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
1818 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
1819 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
1820 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
1821 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
1822 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
1823 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
1824 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
1825 ,SUM(DECODE(gender,'M',1,0)) TotMen
1826 ,SUM(DECODE(gender,'F',1,0)) TotWmen
1827 ,ipeds_category
1828 FROM (
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
1838 ,per_jobs job
1839 ,per_pay_proposals ppp
1840 ,per_pay_bases ppb
1841 ,per_people_extra_info ppet
1842 ,per_contracts_f pco
1843 ,per_shared_types pst
1844 ,per_shared_types pst1
1845 ,per_periods_of_service pps
1846 WHERE peo.person_id = asg.person_id
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
1856 AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
1857 AND asg.primary_flag = 'Y'
1858 AND asg.assignment_status_type_id = ast.assignment_status_type_id
1859 AND ast.per_system_status <> 'TERM_ASSIGN'
1860 AND asg.pay_basis_id = ppb.pay_basis_id
1861 AND asg.assignment_id = ppp.assignment_id
1862 AND asg.period_of_service_id = pps.period_of_service_id
1863 AND pps.date_start BETWEEN ADD_MONTHS(p_report_date,-4) AND (p_report_date-1)
1864 AND ppp.change_date = (SELECT MAX(change_date)
1865 FROM per_pay_proposals pro
1866 WHERE ppp.assignment_id = pro.assignment_id
1867 AND pro.change_date <= p_report_date
1868 AND pro.approved = 'Y'
1869 )
1870 AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
1871 AND asg.job_id = job.job_id
1872 AND asg.assignment_type = 'E'
1873 AND peo.business_group_id = p_business_group_id
1874 AND pco.person_id = peo.person_id
1875 AND pco.type = 'FULL_TIME'
1876 AND pco.status = pst.system_type_cd
1877 AND pst.lookup_type = 'CONTRACT_STATUS'
1878 AND pst1.system_type_cd(+) = pst.system_type_cd
1879 AND pst1.lookup_type(+) = pst.lookup_type
1880 AND pst.business_group_id IS NULL
1881 AND pst1.business_group_id(+) = p_business_group_id
1882 AND NVL(pst1.information1,pst.information1) = 'Y'
1883 AND EXISTS (SELECT 'Y'
1884 FROM hr_all_organization_units hou
1885 WHERE hou.business_group_id = p_business_group_id
1886 AND hou.organization_id = asg.organization_id)
1887 )
1888 GROUP BY '03'||DECODE(SIGN(cont_dur_mon-12),1,0,0,1,2)
1889 ,ipeds_category;
1890
1891 --
1892 --
1893 -- SQL to fetch Full Time Instruction Staff hired with in last 3 months
1894 -- With No Tenure Status attached. This Cursor would be used only if
1895 -- establishment uses tenure system. These would be reported under
1896 -- without faculty status.
1897 -- In Order to indexed columns we are inserting session id into tax unit id
1898 --
1899 INSERT INTO pay_us_rpt_totals(tax_unit_id
1900 ,attribute1
1901 ,attribute2
1902 ,value1
1903 ,value2
1904 ,value3
1905 ,value4
1906 ,value5
1907 ,value6
1908 ,value7
1909 ,value8
1910 ,value9
1911 ,value10
1912 ,value11
1913 ,value12
1914 ,value13
1915 ,value14
1916 ,value15
1917 ,value16
1918 ,value17
1919 ,value18
1920 ,value19
1921 ,value20
1922 ,value21
1923 ,value30
1924 )
1925 SELECT userenv('sessionid')
1926 ,'DGIPEDH'
1927 ,'SECTION1'
1928 ,'4' tenure
1929 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
1930 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
1931 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
1932 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
1933 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
1934 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
1935 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
1936 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
1937 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
1938 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
1939 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
1940 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
1941 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
1942 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
1943 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
1944 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
1945 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
1946 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
1947 ,SUM(DECODE(gender,'M',1,0)) TotMen
1948 ,SUM(DECODE(gender,'F',1,0)) TotWmen
1949 ,ipeds_category
1950 FROM (
1951 SELECT peo.sex gender
1952 ,job.job_information8 ipeds_category
1953 ,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
1954 ,peo.person_id
1955 FROM per_all_people_f peo
1956 ,per_all_assignments_f asg
1957 ,per_assignment_status_types ast
1958 ,per_jobs job
1959 ,per_pay_proposals ppp
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
1969 AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
1970 AND asg.primary_flag = 'Y'
1971 AND asg.assignment_status_type_id = ast.assignment_status_type_id
1972 AND ast.per_system_status <> 'TERM_ASSIGN'
1973 AND asg.pay_basis_id = ppb.pay_basis_id
1974 AND asg.assignment_id = ppp.assignment_id
1975 AND ppp.change_date = (SELECT MAX(change_date)
1976 FROM per_pay_proposals pro
1977 WHERE ppp.assignment_id = pro.assignment_id
1978 AND pro.change_date <= p_report_date
1979 AND pro.approved = 'Y'
1980 )
1981 AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
1982 AND asg.job_id = job.job_id
1983 AND asg.assignment_type = 'E'
1984 AND peo.business_group_id = p_business_group_id
1985 AND asg.period_of_service_id = pps.period_of_service_id
1986 AND pps.date_start BETWEEN ADD_MONTHS(p_report_date,-4) AND (p_report_date-1)
1987 AND EXISTS (SELECT 'Y'
1988 FROM hr_all_organization_units hou
1989 WHERE hou.business_group_id = p_business_group_id
1990 AND hou.organization_id = asg.organization_id)
1991 AND NOT EXISTS (SELECT 'Y'
1992 FROM per_people_extra_info ppet
1993 WHERE ppet.person_id = peo.person_id
1994 AND ppet.information_type = 'PQH_TENURE_STATUS')
1995 )
1996 GROUP BY '4'
1997 ,ipeds_category;
1998
1999 -- Check if Tenure system is not used by establishment
2000 ELSIF p_tenured = 'N' THEN
2001
2002 --
2003 -- SQL to fetch Full Time Instruction Staff hired in last 3 months With
2004 -- Tenure Status as "NOT ON TENURE" and on contract. Tenure codes generated
2005 -- for Employees with contract are
2006 -- 030 - Multi Year Contract
2007 -- 031 - Annual Contract
2008 -- 032 - Less Than Annual Contract
2009 -- In Order to indexed columns we are inserting session id into tax unit id
2010 --
2011 INSERT INTO pay_us_rpt_totals(tax_unit_id
2012 ,attribute1
2013 ,attribute2
2014 ,value1
2015 ,value2
2016 ,value3
2017 ,value4
2018 ,value5
2019 ,value6
2020 ,value7
2021 ,value8
2022 ,value9
2023 ,value10
2024 ,value11
2025 ,value12
2026 ,value13
2027 ,value14
2028 ,value15
2029 ,value16
2030 ,value17
2031 ,value18
2032 ,value19
2033 ,value20
2034 ,value21
2035 ,value30
2036 )
2037 SELECT USERENV('sessionid')
2038 ,'DGIPEDH'
2039 ,'SECTION1'
2040 ,'03'||DECODE(SIGN(cont_dur_mon-12),1,0,0,1,2) tenure
2041 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
2042 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
2043 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
2044 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
2045 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
2046 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
2047 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
2048 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
2049 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
2050 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
2051 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
2052 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
2053 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
2054 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
2055 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
2056 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
2057 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
2058 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
2059 ,SUM(DECODE(gender,'M',1,0)) TotMen
2060 ,SUM(DECODE(gender,'F',1,0)) TotWmen
2061 ,ipeds_category
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
2071 ,per_jobs job
2072 ,per_pay_proposals ppp
2073 ,per_pay_bases ppb
2074 ,per_contracts_f pco
2075 ,per_shared_types pst
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
2085 AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
2086 AND asg.primary_flag = 'Y'
2087 AND asg.assignment_status_type_id = ast.assignment_status_type_id
2088 AND ast.per_system_status <> 'TERM_ASSIGN'
2089 AND asg.pay_basis_id = ppb.pay_basis_id
2090 AND asg.assignment_id = ppp.assignment_id
2091 AND ppp.change_date = (SELECT MAX(change_date)
2092 FROM per_pay_proposals pro
2093 WHERE ppp.assignment_id = pro.assignment_id
2094 AND pro.change_date <= p_report_date
2095 AND pro.approved = 'Y'
2096 )
2097 AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
2098 AND asg.job_id = job.job_id
2099 AND asg.assignment_type = 'E'
2100 AND peo.business_group_id = p_business_group_id
2101 AND asg.period_of_service_id = pps.period_of_service_id
2102 AND pps.date_start BETWEEN ADD_MONTHS(p_report_date,-4) AND (p_report_date-1)
2103 AND pco.person_id = peo.person_id
2104 AND pco.type = 'FULL_TIME'
2105 AND pco.status = pst.system_type_cd
2106 AND pst.lookup_type = 'CONTRACT_STATUS'
2107 AND pst1.system_type_cd(+) = pst.system_type_cd
2108 AND pst1.lookup_type(+) = pst.lookup_type
2109 AND pst.business_group_id IS NULL
2110 AND pst1.business_group_id(+) = p_business_group_id
2111 AND NVL(pst1.information1,pst.information1) = 'Y'
2112 AND EXISTS (SELECT 'Y'
2113 FROM hr_all_organization_units hou
2114 WHERE hou.business_group_id = p_business_group_id
2115 AND hou.organization_id = asg.organization_id)
2116 )
2117 GROUP BY '03'||DECODE(SIGN(cont_dur_mon-12),1,0,0,1,2)
2118 ,ipeds_category;
2119
2120 --
2121 -- SQL to fetch Full Time Instruction Staff hired with in last 3 months
2122 -- With No Contract attached. This Cursor would be used only if establishment
2123 -- doesn't use tenure system.
2124 --
2125 -- In Order to indexed columns we are inserting session id into tax unit id
2126 --
2127 INSERT INTO pay_us_rpt_totals(tax_unit_id
2128 ,attribute1
2129 ,attribute2
2130 ,value1
2131 ,value2
2132 ,value3
2133 ,value4
2134 ,value5
2135 ,value6
2136 ,value7
2137 ,value8
2138 ,value9
2139 ,value10
2140 ,value11
2141 ,value12
2142 ,value13
2143 ,value14
2144 ,value15
2145 ,value16
2146 ,value17
2147 ,value18
2148 ,value19
2149 ,value20
2150 ,value21
2151 ,value30
2152 )
2153 SELECT USERENV('sessionid')
2154 ,'DGIPEDH'
2155 ,'SECTION1'
2156 ,'4' tenure
2157 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
2158 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
2159 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
2160 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
2161 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
2162 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
2163 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
2164 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
2165 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
2166 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
2167 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
2168 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
2169 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
2170 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
2171 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
2172 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
2173 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
2174 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
2175 ,SUM(DECODE(gender,'M',1,0)) TotMen
2176 ,SUM(DECODE(gender,'F',1,0)) TotWmen
2177 ,ipeds_category
2178 FROM (
2179 SELECT peo.sex gender
2180 ,job.job_information8 ipeds_category
2181 ,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
2182 ,peo.person_id
2183 FROM per_all_people_f peo
2184 ,per_all_assignments_f asg
2185 ,per_assignment_status_types ast
2186 ,per_jobs job
2187 ,per_pay_proposals ppp
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
2197 AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
2198 AND asg.primary_flag = 'Y'
2199 AND asg.assignment_status_type_id = ast.assignment_status_type_id
2200 AND ast.per_system_status <> 'TERM_ASSIGN'
2201 AND asg.pay_basis_id = ppb.pay_basis_id
2202 AND asg.assignment_id = ppp.assignment_id
2203 AND ppp.change_date = (SELECT MAX(change_date)
2204 FROM per_pay_proposals pro
2205 WHERE ppp.assignment_id = pro.assignment_id
2206 AND pro.change_date <= p_report_date
2207 AND pro.approved = 'Y'
2208 )
2209 AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
2210 AND asg.job_id = job.job_id
2211 AND asg.assignment_type = 'E'
2212 AND peo.business_group_id = p_business_group_id
2213 AND asg.period_of_service_id = pps.period_of_service_id
2214 AND pps.date_start BETWEEN ADD_MONTHS(p_report_date,-4) AND (p_report_date-1)
2215 AND EXISTS (SELECT 'Y'
2216 FROM hr_all_organization_units hou
2217 WHERE hou.business_group_id = p_business_group_id
2218 AND hou.organization_id = asg.organization_id)
2219 AND NOT EXISTS (SELECT 'Y'
2220 FROM per_contracts_f pco
2221 WHERE pco.person_id = peo.person_id
2222 AND p_report_date BETWEEN pco.effective_start_date AND pco.effective_end_date)
2223 )
2224 GROUP BY '4'
2225 ,ipeds_category;
2226
2227 END IF;
2228 --
2229 --
2230 --
2231 -- SQL to fetch Full Time Non-Instructional Staff hired with in last 3
2232 -- months. Employees under following categories would be reported under
2233 -- In Order to indexed columns we are inserting session id into tax unit id
2234
2235 INSERT INTO pay_us_rpt_totals(tax_unit_id
2236 ,attribute1
2237 ,attribute2
2238 ,value1
2239 ,value2
2240 ,value3
2241 ,value4
2242 ,value5
2243 ,value6
2244 ,value7
2245 ,value8
2246 ,value9
2247 ,value10
2248 ,value11
2249 ,value12
2250 ,value13
2251 ,value14
2252 ,value15
2253 ,value16
2254 ,value17
2255 ,value18
2256 ,value19
2257 ,value20
2258 ,value21
2259 )
2260 SELECT USERENV('sessionid')
2261 ,'DGIPEDH'
2262 ,'SECTION2'
2263 ,ipeds_category
2264 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
2265 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
2266 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
2267 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
2268 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
2269 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
2270 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
2271 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
2272 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
2273 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
2274 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
2275 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
2276 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
2277 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
2278 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
2279 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
2280 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
2281 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
2282 ,SUM(DECODE(gender,'M',1,0)) TotMen
2283 ,SUM(DECODE(gender,'F',1,0)) TotWmen
2284 FROM (SELECT peo.sex gender
2285 /* As We have to report categories 27,28,29,30 into one so changed the codes to
2286 one code 27. In XML meaning would be changed accordingly*/
2287 ,DECODE(job.job_information8,27,27,28,27,29,27,30,27,job.job_information8) ipeds_category
2288 ,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
2289 ,peo.person_id
2290 FROM per_all_people_f peo
2291 ,per_all_assignments_f asg
2292 ,per_assignment_status_types ast
2293 ,per_jobs job
2294 ,per_pay_proposals ppp
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
2304 AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
2305 AND asg.primary_flag = 'Y'
2306 AND asg.assignment_status_type_id = ast.assignment_status_type_id
2307 AND ast.per_system_status <> 'TERM_ASSIGN'
2308 AND asg.pay_basis_id = ppb.pay_basis_id
2309 AND asg.assignment_id = ppp.assignment_id
2310 AND ppp.change_date = (SELECT MAX(change_date)
2311 FROM per_pay_proposals pro
2312 WHERE ppp.assignment_id = pro.assignment_id
2313 AND pro.change_date <= p_report_date
2314 AND pro.approved = 'Y'
2315 )
2316 AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
2317 AND asg.job_id = job.job_id
2318 AND asg.assignment_type = 'E'
2319 AND peo.business_group_id = p_business_group_id
2320 AND asg.period_of_service_id = pps.period_of_service_id
2321 AND pps.date_start BETWEEN ADD_MONTHS(p_report_date,-4) AND (p_report_date-1)
2322 AND EXISTS (SELECT 'Y'
2323 FROM hr_all_organization_units hou
2324 WHERE hou.business_group_id = p_business_group_id
2325 AND hou.organization_id = asg.organization_id)
2326 )
2327 GROUP BY ipeds_category;
2328
2329 RETURN TRUE;
2330
2331 END deg_gnt_parth_bef_rpt;
2332 --
2333
2334 /***************************************************************************
2335 Name : non_deg_gnt_part_bef_rpt
2336 Type : Function
2337 Return Type : Boolean
2338 Description : Populates the PER_US_RPT_TOTALS with information required to
2339 generate the Non Degree Granting institutions IPEDS report
2340 Part A or B based on Parameter selected by user
2341 *****************************************************************************/
2342
2343 FUNCTION non_deg_gnt_part_bef_rpt(p_report_type IN VARCHAR2)
2344 RETURN boolean
2345 IS
2346 l_fr varchar2(2000);
2347 l_ft varchar2(2000);
2348 l_pr varchar2(2000);
2349 l_pt varchar2(2000);
2350 l_tot_men NUMBER;
2351 l_tot_wmen NUMBER;
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
2361 l_employment_category :='FR';
2362 ELSE
2363 l_employment_category :='PR';
2364 END IF;
2365
2366 --
2367 -- SQL to fetch employees count based on employement category
2368 -- In Order to indexed columns we are inserting session id into tax unit id
2369 --
2370 INSERT INTO pay_us_rpt_totals(tax_unit_id
2371 ,attribute1
2372 ,value1
2373 ,value2
2374 ,value3
2375 ,value4
2376 ,value5
2377 ,value6
2378 ,value7
2379 ,value8
2380 ,value9
2381 ,value10
2382 ,value11
2383 ,value12
2384 ,value13
2385 ,value14
2386 ,value15
2387 ,value16
2388 ,value17
2389 ,value18
2390 ,value19
2391 ,value20
2392 ,attribute2
2393 )
2394 SELECT USERENV('sessionid')
2395 ,'NDGIPED'
2396 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'M',1,0),0)) NRMen
2397 ,SUM(DECODE(ethnic_code,0,DECODE(gender,'F',1,0),0)) NRWmen
2398 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'M',1,0),0)) BnHMen
2399 ,SUM(DECODE(ethnic_code,2,DECODE(gender,'F',1,0),0)) BnHWmen
2400 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'M',1,0),0)) Am_AlMen
2401 ,SUM(DECODE(ethnic_code,6,DECODE(gender,'F',1,0),0)) Am_AlWmen
2402 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'M',1,0),0)) AMen
2403 ,SUM(DECODE(ethnic_code,4,DECODE(gender,'F',1,0),0)) AWmen
2404 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'M',1,0),0)) NH_OPMen
2405 ,SUM(DECODE(ethnic_code,5,DECODE(gender,'F',1,0),0)) NH_OPWmen
2406 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'M',1,0),0)) HMen
2407 ,SUM(DECODE(ethnic_code,3,DECODE(gender,'F',1,0),0)) HWmen
2408 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'M',1,0),0)) WnHMen
2409 ,SUM(DECODE(ethnic_code,1,DECODE(gender,'F',1,0),0)) WnHWmen
2410 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'M',1,0),0)) URMen
2411 ,SUM(DECODE(ethnic_code,99,DECODE(gender,'F',1,0),0)) URWmen
2412 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'M',1,0),0)) TmrMen
2413 ,SUM(DECODE(ethnic_code,13,DECODE(gender,'F',1,0),0)) TmrWmen
2414 ,SUM(DECODE(gender,'M',1,0)) TotMen
2415 ,SUM(DECODE(gender,'F',1,0)) TotWmen
2416 ,ipeds_category
2417 FROM (SELECT peo.sex gender
2418 /* As We have to report categories 21,22,23,24,25,26 as one
2419 category and 27,28,29,30 into one so changed the codes to
2420 one code 21 and 27 respectively. In XML meaning would be
2421 changed accordingly */
2422 ,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
2423 ,per_us_hr_utility_pkg.derive_alien_ethnic_origin(peo.person_id,p_report_date,'Y') ethnic_code
2424 ,peo.person_id
2425 FROM per_all_people_f peo
2426 ,per_all_assignments_f asg
2427 ,per_assignment_status_types ast
2428 ,per_jobs job
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
2438 AND p_report_date BETWEEN asg.effective_start_date AND asg.effective_end_date
2439 AND asg.primary_flag = 'Y'
2440 AND asg.assignment_status_type_id = ast.assignment_status_type_id
2441 AND ast.per_system_status <> 'TERM_ASSIGN'
2442 AND asg.pay_basis_id = ppb.pay_basis_id
2443 AND asg.assignment_id = ppp.assignment_id
2444 AND ppp.change_date = (SELECT MAX(change_date)
2445 FROM per_pay_proposals pro
2446 WHERE ppp.assignment_id = pro.assignment_id
2447 AND pro.change_date <= p_report_date
2448 AND pro.approved = 'Y'
2449 )
2450 AND NVL(ppp.proposed_salary_n,0) * ppb.pay_annualization_factor > 0
2451 AND asg.job_id = job.job_id
2452 AND asg.assignment_type = 'E'
2453 AND peo.business_group_id = p_business_group_id
2454 AND EXISTS (SELECT 'Y'
2455 FROM hr_all_organization_units hou
2456 WHERE hou.business_group_id = p_business_group_id
2457 AND hou.organization_id = asg.organization_id))
2458 GROUP BY ipeds_category;
2459
2460 RETURN TRUE;
2461
2462 END non_deg_gnt_part_bef_rpt;
2463
2464 /***************************************************************************
2465 Name : BeforeReport
2466 Type : Function
2467 Return Type : Boolean
2468 Description : This is common procedure called from XML data definition
2469 Before Report trigger that inturn calls the repective report
2470 procedures to populate the PAY_US_RPT_TOTALS for generating
2471 the report.
2472 *****************************************************************************/
2473 FUNCTION BeforeReport(p_report_name VARCHAR2
2474 ,p_report_type IN VARCHAR2 DEFAULT NULL)
2475 RETURN BOOLEAN
2476 IS
2477 l_bool BOOLEAN;
2478 BEGIN
2479
2480 IF NVL(p_report_name,'X') = 'DGIPEDA' THEN
2481 l_bool := deg_gnt_parta_bef_rpt;
2482 END IF;
2483
2484 -- For Part B and E we have common procedure that populates the
2485 -- table PAY_US_RPT_TOTALS
2486 IF ((NVL(p_report_name,'X') = 'DGIPEDB') OR (NVL(p_report_name,'X') = 'DGIPEDE')) THEN
2487 l_bool := deg_gnt_partb_e_bef_rpt(p_report_name);
2488 END IF;
2489
2490 IF NVL(p_report_name,'X') = 'DGIPEDD' THEN
2491 l_bool := deg_gnt_partd_bef_rpt;
2492 END IF;
2493
2494 IF NVL(p_report_name,'X') = 'DGIPEDG' THEN
2495 l_bool := deg_gnt_partg_bef_rpt;
2496 END IF;
2497
2498 IF NVL(p_report_name,'X') = 'DGIPEDH' THEN
2499 l_bool := deg_gnt_parth_bef_rpt;
2500 END IF;
2501
2502 IF NVL(p_report_name,'X') = 'NDGIPED' THEN
2503 l_bool := non_deg_gnt_part_bef_rpt(p_report_type);
2504 END IF;
2505
2506 RETURN l_bool;
2507 END BeforeReport;
2508
2509 /*****************************************************************************
2510 Name : AfterReport
2511 Type : Function
2512 Return Type : Boolean
2513 Description : This is common procedure called from XML data definition
2514 After Report trigger to delete the data from PAY_US_RPT_TOTALS.
2515 *****************************************************************************/
2516 FUNCTION AfterReport(p_report_name VARCHAR2)
2517 RETURN BOOLEAN
2518 IS
2519 BEGIN
2520 EXECUTE IMMEDIATE
2521 'DELETE FROM pay_us_rpt_totals
2522 WHERE attribute1 ='''||p_report_name
2523 ||'''AND tax_unit_id = USERENV(''sessionid'') ';
2524 RETURN (TRUE);
2525 END AfterReport;
2526
2527 END per_us_ipeds_pkg;