[Home] [Help]
PACKAGE BODY: APPS.PQH_PQIPED8_XMLP_PKG
Source
1 PACKAGE BODY PQH_PQIPED8_XMLP_PKG AS
2 /* $Header: PQIPED8B.pls 120.5 2011/05/02 07:48:26 nvelaga ship $ */
3
4 function BeforeReport return boolean is
5
6 l_fr varchar2(2000);
7 l_ft varchar2(2000);
8 l_pr varchar2(2000);
9 l_pt varchar2(2000);
10 LINE VARCHAR2(2000);
11 SC VARCHAR2(2000);
12 L_NR_MEN NUMBER(10) := 0;
13 L_NR_WMEN NUMBER(10) := 0;
14 L_BNH_MEN NUMBER(10) := 0;
15 L_BNH_WMEN NUMBER(10) := 0;
16 L_AMAI_MEN NUMBER(10) := 0;
17 L_AMAI_WMEN NUMBER(10) := 0;
18 L_A_MEN NUMBER(10) := 0; -- Changed varaible name from l_ap_men to l_a_men for IPED Enh #7033011
19 L_A_WMEN NUMBER(10) := 0; -- Changed varaible name from l_ap_men to l_a_men for IPED Enh #7033011
20 L_NHOP_MEN NUMBER(10) := 0; -- Added New Variables for IPED Enh #7033011
21 L_NHOP_WMEN NUMBER(10) := 0; -- Added New Variables for IPED Enh #7033011
22 L_H_MEN NUMBER(10) := 0;
23 L_H_WMEN NUMBER(10) := 0;
24 L_WNH_MEN NUMBER(10) := 0;
25 L_WNH_WMEN NUMBER(10) := 0;
26 L_UR_MEN NUMBER(10) := 0;
27 L_UR_WMEN NUMBER(10) := 0;
28 L_TOT_MEN NUMBER(10) := 0;
29 L_TOT_WMEN NUMBER(10) := 0;
30 L_TMR_MEN NUMBER(10) := 0; -- Added New Variables for IPED Enh #7033011
31 L_TMR_WMEN NUMBER(10) := 0; -- Added New Variables for IPED Enh #7033011
32
33 /* Commented for IPED Enh #7033011 Start
34 L_TMR_BNH_MEN NUMBER(10) := 0;
35 L_TMR_BNH_WMEN NUMBER(10) := 0;
36 L_TMR_AMAI_MEN NUMBER(10) := 0;
37 L_TMR_AMAI_WMEN NUMBER(10) := 0;
38 L_TMR_AP_MEN NUMBER(10) := 0;
39 L_TMR_AP_WMEN NUMBER(10) := 0;
40 L_TMR_H_MEN NUMBER(10) := 0;
41 L_TMR_H_WMEN NUMBER(10) := 0;
42 L_TMR_WNH_MEN NUMBER(10) := 0;
43 L_TMR_WNH_WMEN NUMBER(10) := 0;
44 L_TMR_UR_MEN NUMBER(10) := 0;
45 L_TMR_UR_WMEN NUMBER(10) := 0;
46 Commented for IPED Enh #7033011 End */
47
48 CURSOR GET_LINE1_COUNTS IS
49 SELECT
50 '01' TENINFO,
51 HLA.LOOKUP_CODE ARANK,
52 -- Added for bug#11736960
53 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
54 ,P_REPORT_DATE
55 ,'Y')
56 ,0
57 ,DECODE(PEO.SEX
58 ,'M'
59 ,1
60 ,NULL)
61 ,NULL)) NRMEN,
62 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
63 ,P_REPORT_DATE
64 ,'Y')
65 ,0
66 ,DECODE(PEO.SEX
67 ,'F'
68 ,1
69 ,NULL)
70 ,NULL)) NRWMEN,
71 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
72 ,P_REPORT_DATE
73 ,'Y')
74 ,2
75 ,DECODE(PEO.SEX
76 ,'M'
77 ,1
78 ,NULL)
79 ,NULL)) BNHMEN,
80 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
81 ,P_REPORT_DATE
82 ,'Y')
83 ,2
84 ,DECODE(PEO.SEX
85 ,'F'
86 ,1
87 ,NULL)
88 ,NULL)) BNHWMEN,
89 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
90 ,P_REPORT_DATE
91 ,'Y')
92 ,6
93 ,DECODE(PEO.SEX
94 ,'M'
95 ,1
96 ,NULL)
97 ,NULL)) AM_ALMEN,
98 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
99 ,P_REPORT_DATE
100 ,'Y')
101 ,6
102 ,DECODE(PEO.SEX
103 ,'F'
104 ,1
105 ,NULL)
106 ,NULL)) AM_ALWMEN,
107 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
108 ,P_REPORT_DATE
109 ,'Y')
110 ,4
111 ,DECODE(PEO.SEX
112 ,'M'
113 ,1
114 ,NULL)
115 ,NULL)) AMEN,
116 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
117 ,P_REPORT_DATE
118 ,'Y')
119 ,4
120 ,DECODE(PEO.SEX
121 ,'F'
122 ,1
123 ,NULL)
124 ,NULL)) AWMEN,
125 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
126 ,P_REPORT_DATE
127 ,'Y')
128 ,5
129 ,DECODE(PEO.SEX
130 ,'M'
131 ,1
132 ,NULL)
133 ,NULL)) NH_OPMEN,
134 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
135 ,P_REPORT_DATE
136 ,'Y')
137 ,5
138 ,DECODE(PEO.SEX
139 ,'F'
140 ,1
141 ,NULL)
142 ,NULL)) NH_OPWMEN,
143 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
144 ,P_REPORT_DATE
145 ,'Y')
146 ,3
147 ,DECODE(PEO.SEX
148 ,'M'
149 ,1
150 ,NULL)
151 ,NULL)) HMEN,
152 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
153 ,P_REPORT_DATE
154 ,'Y')
155 ,3
156 ,DECODE(PEO.SEX
157 ,'F'
158 ,1
159 ,NULL)
160 ,NULL)) HWMEN,
161 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
162 ,P_REPORT_DATE
163 ,'Y')
164 ,1
165 ,DECODE(PEO.SEX
166 ,'M'
167 ,1
168 ,NULL)
169 ,NULL)) WNHMEN,
170 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
171 ,P_REPORT_DATE
172 ,'Y')
173 ,1
174 ,DECODE(PEO.SEX
175 ,'F'
176 ,1
177 ,NULL)
178 ,NULL)) WNHWMEN,
179 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
180 ,P_REPORT_DATE
181 ,'Y')
182 ,99
183 ,DECODE(PEO.SEX
184 ,'M'
185 ,1
186 ,NULL)
187 ,NULL)) URMEN,
188 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
189 ,P_REPORT_DATE
190 ,'Y')
191 ,99
192 ,DECODE(PEO.SEX
193 ,'F'
194 ,1
195 ,NULL)
196 ,NULL)) URWMEN,
197 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
198 ,P_REPORT_DATE
199 ,'Y')
200 ,13
201 ,DECODE(PEO.SEX
202 ,'M'
203 ,1
204 ,NULL)
205 ,NULL)) TMRMEN,
206 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
207 ,P_REPORT_DATE
208 ,'Y')
209 ,13
210 ,DECODE(PEO.SEX
211 ,'F'
212 ,1
213 ,NULL)
214 ,NULL)) TMRWMEN
215 --
216 /* Commented for bug#11736960 starts
217 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
218 ,P_REPORT_DATE)
219 ,'1'
220 ,DECODE(PEO.SEX
221 ,'M'
222 ,1
223 ,NULL)
224 ,NULL)) NRMEN,
225 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
226 ,P_REPORT_DATE)
227 ,'1'
228 ,DECODE(PEO.SEX
229 ,'F'
230 ,1
231 ,NULL)
232 ,NULL)) NRWMEN,
233 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
234 ,P_REPORT_DATE)
235 ,NULL
236 ,(DECODE(PEO.PER_INFORMATION1
237 ,'2'
238 ,DECODE(PEO.SEX
239 ,'M'
240 ,1
241 ,NULL)
242 ,NULL)))) BNHMEN,
243 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
244 ,P_REPORT_DATE)
245 ,NULL
246 ,(DECODE(PEO.PER_INFORMATION1
247 ,'2'
248 ,DECODE(PEO.SEX
249 ,'F'
250 ,1
251 ,NULL)
252 ,NULL)))) BNHWMEN,
253 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
254 ,P_REPORT_DATE)
255 ,NULL
256 ,(DECODE(PEO.PER_INFORMATION1
257 ,'6'
258 ,DECODE(PEO.SEX
259 ,'M'
260 ,1
261 ,NULL)
262 ,NULL)))) AM_ALMEN,
263 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
264 ,P_REPORT_DATE)
265 ,NULL
266 ,(DECODE(PEO.PER_INFORMATION1
267 ,'6'
268 ,DECODE(PEO.SEX
269 ,'F'
270 ,1
271 ,NULL)
272 ,NULL)))) AM_ALWMEN,
273 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
274 ,P_REPORT_DATE)
275 ,NULL
276 ,(DECODE(PEO.PER_INFORMATION1
277 ,'4'
278 ,DECODE(PEO.SEX
279 ,'M'
280 ,1
281 ,NULL)
282 ,NULL)))) AMEN, -- Removed code of '5' for IPED Enh #7033011
283 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
284 ,P_REPORT_DATE)
285 ,NULL
286 ,(DECODE(PEO.PER_INFORMATION1
287 ,'4'
288 ,DECODE(PEO.SEX
289 ,'F'
290 ,1
291 ,NULL)
292 ,NULL)))) AWMEN, -- Removed code of '5' for IPED Enh #7033011
293 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
294 ,P_REPORT_DATE)
295 ,NULL
296 ,(DECODE(PEO.PER_INFORMATION1
297 ,'5'
298 ,DECODE(PEO.SEX
299 ,'M'
300 ,1
301 ,NULL)
302 ,NULL)))) NH_OPMEN, -- Added New column for IPED Enh #7033011
303 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
304 ,P_REPORT_DATE)
305 ,NULL
306 ,(DECODE(PEO.PER_INFORMATION1
307 ,'5'
308 ,DECODE(PEO.SEX
309 ,'F'
310 ,1
311 ,NULL)
312 ,NULL)))) NH_OPWMEN, -- Added New column for IPED Enh #7033011
313 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
314 ,P_REPORT_DATE)
315 ,NULL
316 ,(DECODE(PEO.PER_INFORMATION1
317 ,'3'
318 ,DECODE(PEO.SEX
319 ,'M'
320 ,1
321 ,NULL)
322 ,NULL)))) HMEN, -- Removed code of '9' for IPED Enh #7033011
323 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
324 ,P_REPORT_DATE)
325 ,NULL
326 ,(DECODE(PEO.PER_INFORMATION1
327 ,'3'
328 ,DECODE(PEO.SEX
329 ,'F'
330 ,1
331 ,NULL)
332 ,NULL)))) HWMEN, -- Removed code of '9' for IPED Enh #7033011
333 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
334 ,P_REPORT_DATE)
335 ,NULL
336 ,(DECODE(PEO.PER_INFORMATION1
337 ,'1'
338 ,DECODE(PEO.SEX
339 ,'M'
340 ,1
341 ,NULL)
342 ,NULL)))) WNHMEN,
343 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
344 ,P_REPORT_DATE)
345 ,NULL
346 ,(DECODE(PEO.PER_INFORMATION1
347 ,'1'
348 ,DECODE(PEO.SEX
349 ,'F'
350 ,1
351 ,NULL)
352 ,NULL)))) WNHWMEN,
353 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
354 ,P_REPORT_DATE)
355 ,NULL
356 ,(DECODE(PEO.PER_INFORMATION1
357 ,NULL
358 ,DECODE(PEO.SEX
359 ,'M'
360 ,1
361 ,NULL)
362 ,NULL)))) URMEN,
363 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
364 ,P_REPORT_DATE)
365 ,NULL
366 ,(DECODE(PEO.PER_INFORMATION1
367 ,NULL
368 ,DECODE(PEO.SEX
369 ,'F'
370 ,1
371 ,NULL)
372 ,NULL)))) URWMEN,
373 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
374 ,P_REPORT_DATE)
375 ,NULL
376 ,(DECODE(PEO.PER_INFORMATION1
377 ,'13'
378 ,DECODE(PEO.SEX
379 ,'M'
380 ,1
381 ,NULL)
382 ,NULL)))) TMRMEN, -- Added New column for IPED Enh #7033011
383 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
384 ,P_REPORT_DATE)
385 ,NULL
386 ,(DECODE(PEO.PER_INFORMATION1
387 ,'13'
388 ,DECODE(PEO.SEX
389 ,'F'
390 ,1
391 ,NULL)
392 ,NULL)))) TMRWMEN -- Added New column for IPED Enh #7033011
393 Commented for bug#11736960 ends */
394 --
395 FROM
396 HR_LOOKUPS HLA,
397 PER_ALL_PEOPLE_F PEO,
398 PER_ALL_ASSIGNMENTS_F PAF,
399 PER_ASSIGNMENT_STATUS_TYPES AST,
400 PER_PEOPLE_EXTRA_INFO PPEA,
401 PER_PEOPLE_EXTRA_INFO PPET,
402 HR_LOOKUPS HL,
403 PER_PAY_PROPOSALS PPP,
404 PER_PAY_BASES PPB,
405 PER_JOBS JOB
406 WHERE PAF.PERSON_ID = PPEA.PERSON_ID
407 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
408 AND PAF.PERSON_ID = PPET.PERSON_ID
409 AND PAF.PERSON_ID = PEO.PERSON_ID
410 AND PPEA.INFORMATION_TYPE = 'PQH_ACADEMIC_RANK'
411 AND PPET.INFORMATION_TYPE = 'PQH_TENURE_STATUS'
412 AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
413 AND PAF.EFFECTIVE_END_DATE
414 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
415 AND PEO.EFFECTIVE_END_DATE
416 AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
417 AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
418 AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
419 ,'4712/12/31 00:00:00'))
420 AND PPET.PEI_INFORMATION1 in ( '01' )
421 AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
422 AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE
423 AND PPEA.PEI_INFORMATION1 IS NOT NULL
424 AND PPEA.PEI_INFORMATION1 not in ( '01' )
425 AND PPET.PEI_INFORMATION1 IS NOT NULL
426 AND PAF.PRIMARY_FLAG = 'Y'
427 AND PAF.ASSIGNMENT_TYPE = 'E'
428 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
429 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
430 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
431 ,CP_FR
432 ,CP_FT
433 ,CP_PR
434 ,CP_PT) IN ( 'FR' )
435 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
436 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
437 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
438 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
439 AND PAF.JOB_ID = JOB.JOB_ID
440 AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
441 AND PAF.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
442 AND PPP.CHANGE_DATE = (
443 SELECT
444 MAX(CHANGE_DATE)
445 FROM
446 PER_PAY_PROPOSALS PRO
447 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
448 AND PRO.CHANGE_DATE <= P_REPORT_DATE
449 AND PRO.APPROVED = 'Y' )
450 AND NVL(PPP.PROPOSED_SALARY_N
451 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
452 AND PAF.ORGANIZATION_ID IN (
453 SELECT
454 ORGANIZATION_ID
455 FROM
456 HR_ALL_ORGANIZATION_UNITS
457 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
458 GROUP BY
459 '01',
460 HLA.LOOKUP_CODE;
461 /* Commented for IPED Enh #7033011 Start
462 CURSOR GET_LINE1_TMRACES_COUNTS(C_LOOKUP_CODE IN VARCHAR2) IS
463 SELECT
464 COUNT(DECODE(PEI.PEI_INFORMATION5
465 ,'2'
466 ,DECODE(PEO.SEX
467 ,'M'
468 ,1
469 ,NULL)
470 ,NULL)) BNHMEN,
471 COUNT(DECODE(PEI.PEI_INFORMATION5
472 ,'2'
473 ,DECODE(PEO.SEX
474 ,'F'
475 ,1
476 ,NULL)
477 ,NULL)) BNHWMEN,
478 COUNT(DECODE(PEI.PEI_INFORMATION5
479 ,'6'
480 ,DECODE(PEO.SEX
481 ,'M'
482 ,1
483 ,NULL)
484 ,NULL)) AM_ALMEN,
485 COUNT(DECODE(PEI.PEI_INFORMATION5
486 ,'6'
487 ,DECODE(PEO.SEX
488 ,'F'
489 ,1
490 ,NULL)
491 ,NULL)) AM_ALWMEN,
492 COUNT(DECODE(PEI.PEI_INFORMATION5
493 ,'4'
494 ,DECODE(PEO.SEX
495 ,'M'
496 ,1
497 ,NULL)
498 ,'5'
499 ,DECODE(PEO.SEX
500 ,'M'
501 ,1
502 ,NULL)
503 ,NULL)) A_PMEN,
504 COUNT(DECODE(PEI.PEI_INFORMATION5
505 ,'4'
506 ,DECODE(PEO.SEX
507 ,'F'
508 ,1
509 ,NULL)
510 ,'5'
511 ,DECODE(PEO.SEX
512 ,'F'
513 ,1
514 ,NULL)
515 ,NULL)) A_PWMEN,
516 COUNT(DECODE(PEI.PEI_INFORMATION5
517 ,'3'
518 ,DECODE(PEO.SEX
519 ,'M'
520 ,1
521 ,NULL)
522 ,'9'
523 ,DECODE(PEO.SEX
524 ,'M'
525 ,1
526 ,NULL)
527 ,NULL)) HMEN,
528 COUNT(DECODE(PEI.PEI_INFORMATION5
529 ,'3'
530 ,DECODE(PEO.SEX
531 ,'F'
532 ,1
533 ,NULL)
534 ,'9'
535 ,DECODE(PEO.SEX
536 ,'F'
537 ,1
538 ,NULL)
539 ,NULL)) HWMEN,
540 COUNT(DECODE(PEI.PEI_INFORMATION5
541 ,'1'
542 ,DECODE(PEO.SEX
543 ,'M'
544 ,1
545 ,NULL)
546 ,NULL)) WNHMEN,
547 COUNT(DECODE(PEI.PEI_INFORMATION5
548 ,'1'
549 ,DECODE(PEO.SEX
550 ,'F'
551 ,1
552 ,NULL)
553 ,NULL)) WNHWMEN,
554 COUNT(DECODE(PEI.PEI_INFORMATION5
555 ,NULL
556 ,DECODE(PEO.SEX
557 ,'M'
558 ,1
559 ,NULL)
560 ,NULL)) URMEN,
561 COUNT(DECODE(PEI.PEI_INFORMATION5
562 ,NULL
563 ,DECODE(PEO.SEX
564 ,'F'
565 ,1
566 ,NULL)
567 ,NULL)) URWMEN
568 FROM
569 HR_LOOKUPS HLA,
570 PER_ALL_PEOPLE_F PEO,
571 PER_ALL_ASSIGNMENTS_F PAF,
572 PER_ASSIGNMENT_STATUS_TYPES AST,
573 PER_PEOPLE_EXTRA_INFO PPEA,
574 PER_PEOPLE_EXTRA_INFO PPET,
575 HR_LOOKUPS HL,
576 PER_PAY_PROPOSALS PPP,
577 PER_PAY_BASES PPB,
578 PER_JOBS JOB,
579 PER_PEOPLE_EXTRA_INFO PEI
580 WHERE PAF.PERSON_ID = PPEA.PERSON_ID
581 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
582 AND PEO.PER_INFORMATION1 = '13'
583 AND PEO.PERSON_ID = pei.person_id (+)
584 AND ( PEI.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
585 OR ( PEI.INFORMATION_TYPE <> 'PER_US_ADDL_ETHNIC_CAT'
586 AND not exists (
587 SELECT
588 1
589 FROM
590 PER_PEOPLE_EXTRA_INFO PEI2
591 WHERE PEI2.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
592 AND PEI2.PERSON_ID = PEI.PERSON_ID )
593 AND PEI.PERSON_EXTRA_INFO_ID = (
594 SELECT
595 MAX(PEI1.PERSON_EXTRA_INFO_ID)
596 FROM
597 PER_PEOPLE_EXTRA_INFO PEI1
598 WHERE PEI1.PERSON_ID = PEI.PERSON_ID ) )
599 OR ( not exists (
600 SELECT
601 PERSON_EXTRA_INFO_ID
602 FROM
603 PER_PEOPLE_EXTRA_INFO PEI3
604 WHERE PEI3.PERSON_ID = PEI.PERSON_ID ) ) )
605 AND PAF.PERSON_ID = PPET.PERSON_ID
606 AND PAF.PERSON_ID = PEO.PERSON_ID
607 AND PPEA.INFORMATION_TYPE = 'PQH_ACADEMIC_RANK'
608 AND PPET.INFORMATION_TYPE = 'PQH_TENURE_STATUS'
609 AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
610 AND PAF.EFFECTIVE_END_DATE
611 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
612 AND PEO.EFFECTIVE_END_DATE
613 AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
614 AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
615 AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
616 ,'4712/12/31 00:00:00'))
617 AND PPET.PEI_INFORMATION1 in ( '01' )
618 AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
619 AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE
620 AND PPEA.PEI_INFORMATION1 IS NOT NULL
621 AND PPEA.PEI_INFORMATION1 not in ( '01' )
622 AND PPET.PEI_INFORMATION1 IS NOT NULL
623 AND PAF.PRIMARY_FLAG = 'Y'
624 AND PAF.ASSIGNMENT_TYPE = 'E'
625 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
626 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
627 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
628 ,CP_FR
629 ,CP_FT
630 ,CP_PR
631 ,CP_PT) IN ( 'FR' )
632 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
633 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
634 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
635 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
636 AND PAF.JOB_ID = JOB.JOB_ID
637 AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
638 AND PAF.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
639 AND PPP.CHANGE_DATE = (
640 SELECT
641 MAX(CHANGE_DATE)
642 FROM
643 PER_PAY_PROPOSALS PRO
644 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
645 AND PRO.CHANGE_DATE <= P_REPORT_DATE
646 AND PRO.APPROVED = 'Y' )
647 AND NVL(PPP.PROPOSED_SALARY_N
648 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
649 AND PAF.ORGANIZATION_ID IN (
650 SELECT
651 ORGANIZATION_ID
652 FROM
653 HR_ALL_ORGANIZATION_UNITS
654 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
655 AND HLA.LOOKUP_CODE = C_LOOKUP_CODE;
656 Commented for IPED Enh #7033011 End */
657
658 CURSOR GET_LINE2_COUNTS IS
659 SELECT
660 '01' TENINFO,
661 '01' ARANK,
662 -- Added for bug#11736960
663 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
664 ,P_REPORT_DATE
665 ,'Y')
666 ,0
667 ,DECODE(PEO.SEX
668 ,'M'
669 ,1
670 ,NULL)
671 ,NULL)) NRMEN,
672 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
673 ,P_REPORT_DATE
674 ,'Y')
675 ,0
676 ,DECODE(PEO.SEX
677 ,'F'
678 ,1
679 ,NULL)
680 ,NULL)) NRWMEN,
681 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
682 ,P_REPORT_DATE
683 ,'Y')
684 ,2
685 ,DECODE(PEO.SEX
686 ,'M'
687 ,1
688 ,NULL)
689 ,NULL)) BNHMEN,
690 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
691 ,P_REPORT_DATE
692 ,'Y')
693 ,2
694 ,DECODE(PEO.SEX
695 ,'F'
696 ,1
697 ,NULL)
698 ,NULL)) BNHWMEN,
699 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
700 ,P_REPORT_DATE
701 ,'Y')
702 ,6
703 ,DECODE(PEO.SEX
704 ,'M'
705 ,1
706 ,NULL)
707 ,NULL)) AM_ALMEN,
708 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
709 ,P_REPORT_DATE
710 ,'Y')
711 ,6
712 ,DECODE(PEO.SEX
713 ,'F'
714 ,1
715 ,NULL)
716 ,NULL)) AM_ALWMEN,
717 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
718 ,P_REPORT_DATE
719 ,'Y')
720 ,4
721 ,DECODE(PEO.SEX
722 ,'M'
723 ,1
724 ,NULL)
725 ,NULL)) AMEN,
726 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
727 ,P_REPORT_DATE
728 ,'Y')
729 ,4
730 ,DECODE(PEO.SEX
731 ,'F'
732 ,1
733 ,NULL)
734 ,NULL)) AWMEN,
735 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
736 ,P_REPORT_DATE
737 ,'Y')
738 ,5
739 ,DECODE(PEO.SEX
740 ,'M'
741 ,1
742 ,NULL)
743 ,NULL)) NH_OPMEN,
744 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
745 ,P_REPORT_DATE
746 ,'Y')
747 ,5
748 ,DECODE(PEO.SEX
749 ,'F'
750 ,1
751 ,NULL)
752 ,NULL)) NH_OPWMEN,
753 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
754 ,P_REPORT_DATE
755 ,'Y')
756 ,3
757 ,DECODE(PEO.SEX
758 ,'M'
759 ,1
760 ,NULL)
761 ,NULL)) HMEN,
762 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
763 ,P_REPORT_DATE
764 ,'Y')
765 ,3
766 ,DECODE(PEO.SEX
767 ,'F'
768 ,1
769 ,NULL)
770 ,NULL)) HWMEN,
771 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
772 ,P_REPORT_DATE
773 ,'Y')
774 ,1
775 ,DECODE(PEO.SEX
776 ,'M'
777 ,1
778 ,NULL)
779 ,NULL)) WNHMEN,
780 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
781 ,P_REPORT_DATE
782 ,'Y')
783 ,1
784 ,DECODE(PEO.SEX
785 ,'F'
786 ,1
787 ,NULL)
788 ,NULL)) WNHWMEN,
789 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
790 ,P_REPORT_DATE
791 ,'Y')
792 ,99
793 ,DECODE(PEO.SEX
794 ,'M'
795 ,1
796 ,NULL)
797 ,NULL)) URMEN,
798 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
799 ,P_REPORT_DATE
800 ,'Y')
801 ,99
802 ,DECODE(PEO.SEX
803 ,'F'
804 ,1
805 ,NULL)
806 ,NULL)) URWMEN,
807 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
808 ,P_REPORT_DATE
809 ,'Y')
810 ,13
811 ,DECODE(PEO.SEX
812 ,'M'
813 ,1
814 ,NULL)
815 ,NULL)) TMRMEN,
816 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
817 ,P_REPORT_DATE
818 ,'Y')
819 ,13
820 ,DECODE(PEO.SEX
821 ,'F'
822 ,1
823 ,NULL)
824 ,NULL)) TMRWMEN
825 --
826 /* Commented for bug#11736960 starts
827
828 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
829 ,P_REPORT_DATE)
830 ,'1'
831 ,DECODE(PEO.SEX
832 ,'M'
833 ,1
834 ,NULL)
835 ,NULL)) NRMEN,
836 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
837 ,P_REPORT_DATE)
838 ,'1'
839 ,DECODE(PEO.SEX
840 ,'F'
841 ,1
842 ,NULL)
843 ,NULL)) NRWMEN,
844 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
845 ,P_REPORT_DATE)
846 ,NULL
847 ,(DECODE(PEO.PER_INFORMATION1
848 ,'2'
849 ,DECODE(PEO.SEX
850 ,'M'
851 ,1
852 ,NULL)
853 ,NULL)))) BNHMEN,
854 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
855 ,P_REPORT_DATE)
856 ,NULL
857 ,(DECODE(PEO.PER_INFORMATION1
858 ,'2'
859 ,DECODE(PEO.SEX
860 ,'F'
861 ,1
862 ,NULL)
863 ,NULL)))) BNHWMEN,
864 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
865 ,P_REPORT_DATE)
866 ,NULL
867 ,(DECODE(PEO.PER_INFORMATION1
868 ,'6'
869 ,DECODE(PEO.SEX
870 ,'M'
871 ,1
872 ,NULL)
873 ,NULL)))) AM_ALMEN,
874 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
875 ,P_REPORT_DATE)
876 ,NULL
877 ,(DECODE(PEO.PER_INFORMATION1
878 ,'6'
879 ,DECODE(PEO.SEX
880 ,'F'
881 ,1
882 ,NULL)
883 ,NULL)))) AM_ALWMEN,
884 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
885 ,P_REPORT_DATE)
886 ,NULL
887 ,(DECODE(PEO.PER_INFORMATION1
888 ,'4'
889 ,DECODE(PEO.SEX
890 ,'M'
891 ,1
892 ,NULL)
893 ,NULL)))) AMEN, -- Removed code of '5' for IPED Enh #7033011
894 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
895 ,P_REPORT_DATE)
896 ,NULL
897 ,(DECODE(PEO.PER_INFORMATION1
898 ,'4'
899 ,DECODE(PEO.SEX
900 ,'F'
901 ,1
902 ,NULL)
903 ,NULL)))) AWMEN, -- Removed code of '5' for IPED Enh #7033011
904 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
905 ,P_REPORT_DATE)
906 ,NULL
907 ,(DECODE(PEO.PER_INFORMATION1
908 ,'5'
909 ,DECODE(PEO.SEX
910 ,'M'
911 ,1
912 ,NULL)
913 ,NULL)))) NH_OPMEN, -- Added New column for IPED Enh #7033011
914 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
915 ,P_REPORT_DATE)
916 ,NULL
917 ,(DECODE(PEO.PER_INFORMATION1
918 ,'5'
919 ,DECODE(PEO.SEX
920 ,'F'
921 ,1
922 ,NULL)
923 ,NULL)))) NH_OPWMEN, -- Added New column for IPED Enh #7033011
924 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
925 ,P_REPORT_DATE)
926 ,NULL
927 ,(DECODE(PEO.PER_INFORMATION1
928 ,'3'
929 ,DECODE(PEO.SEX
930 ,'M'
931 ,1
932 ,NULL)
933 ,NULL)))) HMEN, -- Removed code of '9' for IPED Enh #7033011
934 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
935 ,P_REPORT_DATE)
936 ,NULL
937 ,(DECODE(PEO.PER_INFORMATION1
938 ,'3'
939 ,DECODE(PEO.SEX
940 ,'F'
941 ,1
942 ,NULL)
943 ,NULL)))) HWMEN, -- Removed code of '9' for IPED Enh #7033011
944 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
945 ,P_REPORT_DATE)
946 ,NULL
947 ,(DECODE(PEO.PER_INFORMATION1
948 ,'1'
949 ,DECODE(PEO.SEX
950 ,'M'
951 ,1
952 ,NULL)
953 ,NULL)))) WNHMEN,
954 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
955 ,P_REPORT_DATE)
956 ,NULL
957 ,(DECODE(PEO.PER_INFORMATION1
958 ,'1'
959 ,DECODE(PEO.SEX
960 ,'F'
961 ,1
962 ,NULL)
963 ,NULL)))) WNHWMEN,
964 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
965 ,P_REPORT_DATE)
966 ,NULL
967 ,(DECODE(PEO.PER_INFORMATION1
968 ,NULL
969 ,DECODE(PEO.SEX
970 ,'M'
971 ,1
972 ,NULL)
973 ,NULL)))) URMEN,
974 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
975 ,P_REPORT_DATE)
976 ,NULL
977 ,(DECODE(PEO.PER_INFORMATION1
978 ,NULL
979 ,DECODE(PEO.SEX
980 ,'F'
981 ,1
982 ,NULL)
983 ,NULL)))) URWMEN,
984 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
985 ,P_REPORT_DATE)
986 ,NULL
987 ,(DECODE(PEO.PER_INFORMATION1
988 ,'13'
989 ,DECODE(PEO.SEX
990 ,'M'
991 ,1
992 ,NULL)
993 ,NULL)))) TMRMEN, -- Added New column for IPED Enh #7033011
994 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
995 ,P_REPORT_DATE)
996 ,NULL
997 ,(DECODE(PEO.PER_INFORMATION1
998 ,'13'
999 ,DECODE(PEO.SEX
1000 ,'F'
1001 ,1
1002 ,NULL)
1003 ,NULL)))) TMRWMEN -- Added New column for IPED Enh #7033011
1004 Commented for bug#11736960 ends */
1005 --
1006 FROM
1007 PER_ALL_PEOPLE_F PEO,
1008 PER_ALL_ASSIGNMENTS_F PAF,
1009 PER_ASSIGNMENT_STATUS_TYPES AST,
1010 PER_PEOPLE_EXTRA_INFO PPET,
1011 HR_LOOKUPS HL,
1012 PER_PAY_PROPOSALS PPP,
1013 PER_PAY_BASES PPB,
1014 PER_JOBS JOB
1015 WHERE PAF.PERSON_ID = PPET.PERSON_ID
1016 AND PAF.PERSON_ID = PEO.PERSON_ID
1017 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
1018 AND PPET.INFORMATION_TYPE = 'PQH_TENURE_STATUS'
1019 AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
1020 AND PAF.EFFECTIVE_END_DATE
1021 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
1022 AND PEO.EFFECTIVE_END_DATE
1023 AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
1024 AND PPET.PEI_INFORMATION1 in ( '01' )
1025 AND PPET.PEI_INFORMATION1 IS NOT NULL
1026 AND not exists (
1027 SELECT
1028 PEA.PERSON_ID
1029 FROM
1030 PER_PEOPLE_EXTRA_INFO PEA
1031 WHERE PEA.PERSON_ID = PEO.PERSON_ID
1032 AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
1033 AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
1034 AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
1035 AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
1036 ,'4712/12/31 00:00:00')) )
1037 AND PAF.PRIMARY_FLAG = 'Y'
1038 AND PAF.ASSIGNMENT_TYPE = 'E'
1039 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
1040 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
1041 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
1042 ,CP_FR
1043 ,CP_FT
1044 ,CP_PR
1045 ,CP_PT) IN ( 'FR' )
1046 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
1047 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
1048 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
1049 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
1050 AND PAF.JOB_ID = JOB.JOB_ID
1051 AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
1052 AND PAF.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
1053 AND PPP.CHANGE_DATE = (
1054 SELECT
1055 MAX(CHANGE_DATE)
1056 FROM
1057 PER_PAY_PROPOSALS PRO
1058 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
1059 AND PRO.CHANGE_DATE <= P_REPORT_DATE
1060 AND PRO.APPROVED = 'Y' )
1061 AND NVL(PPP.PROPOSED_SALARY_N
1062 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
1063 AND PAF.ORGANIZATION_ID IN (
1064 SELECT
1065 ORGANIZATION_ID
1066 FROM
1067 HR_ALL_ORGANIZATION_UNITS
1068 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
1069 GROUP BY
1070 '01',
1071 '01';
1072 /* Commented for IPED Enh #7033011 Start
1073 CURSOR GET_TMR_LINE2_COUNTS IS
1074 SELECT
1075 COUNT(DECODE(PEI.PEI_INFORMATION5
1076 ,'2'
1077 ,DECODE(PEO.SEX
1078 ,'M'
1079 ,1
1080 ,NULL)
1081 ,NULL)) BNHMEN,
1082 COUNT(DECODE(PEI.PEI_INFORMATION5
1083 ,'2'
1084 ,DECODE(PEO.SEX
1085 ,'F'
1086 ,1
1087 ,NULL)
1088 ,NULL)) BNHWMEN,
1089 COUNT(DECODE(PEI.PEI_INFORMATION5
1090 ,'6'
1091 ,DECODE(PEO.SEX
1092 ,'M'
1093 ,1
1094 ,NULL)
1095 ,NULL)) AM_ALMEN,
1096 COUNT(DECODE(PEI.PEI_INFORMATION5
1097 ,'6'
1098 ,DECODE(PEO.SEX
1099 ,'F'
1100 ,1
1101 ,NULL)
1102 ,NULL)) AM_ALWMEN,
1103 COUNT(DECODE(PEI.PEI_INFORMATION5
1104 ,'4'
1105 ,DECODE(PEO.SEX
1106 ,'M'
1107 ,1
1108 ,NULL)
1109 ,'5'
1110 ,DECODE(PEO.SEX
1111 ,'M'
1112 ,1
1113 ,NULL)
1114 ,NULL)) A_PMEN,
1115 COUNT(DECODE(PEI.PEI_INFORMATION5
1116 ,'4'
1117 ,DECODE(PEO.SEX
1118 ,'F'
1119 ,1
1120 ,NULL)
1121 ,'5'
1122 ,DECODE(PEO.SEX
1123 ,'F'
1124 ,1
1125 ,NULL)
1126 ,NULL)) A_PWMEN,
1127 COUNT(DECODE(PEI.PEI_INFORMATION5
1128 ,'3'
1129 ,DECODE(PEO.SEX
1130 ,'M'
1131 ,1
1132 ,NULL)
1133 ,'9'
1134 ,DECODE(PEO.SEX
1135 ,'M'
1136 ,1
1137 ,NULL)
1138 ,NULL)) HMEN,
1139 COUNT(DECODE(PEI.PEI_INFORMATION5
1140 ,'3'
1141 ,DECODE(PEO.SEX
1142 ,'F'
1143 ,1
1144 ,NULL)
1145 ,'9'
1146 ,DECODE(PEO.SEX
1147 ,'F'
1148 ,1
1149 ,NULL)
1150 ,NULL)) HWMEN,
1151 COUNT(DECODE(PEI.PEI_INFORMATION5
1152 ,'1'
1153 ,DECODE(PEO.SEX
1154 ,'M'
1155 ,1
1156 ,NULL)
1157 ,NULL)) WNHMEN,
1158 COUNT(DECODE(PEI.PEI_INFORMATION5
1159 ,'1'
1160 ,DECODE(PEO.SEX
1161 ,'F'
1162 ,1
1163 ,NULL)
1164 ,NULL)) WNHWMEN,
1165 COUNT(DECODE(PEI.PEI_INFORMATION5
1166 ,NULL
1167 ,DECODE(PEO.SEX
1168 ,'M'
1169 ,1
1170 ,NULL)
1171 ,NULL)) URMEN,
1172 COUNT(DECODE(PEI.PEI_INFORMATION5
1173 ,NULL
1174 ,DECODE(PEO.SEX
1175 ,'F'
1176 ,1
1177 ,NULL)
1178 ,NULL)) URWMEN
1179 FROM
1180 PER_ALL_PEOPLE_F PEO,
1181 PER_ALL_ASSIGNMENTS_F PAF,
1182 PER_ASSIGNMENT_STATUS_TYPES AST,
1183 PER_PEOPLE_EXTRA_INFO PPET,
1184 HR_LOOKUPS HL,
1185 PER_PAY_PROPOSALS PPP,
1186 PER_PAY_BASES PPB,
1187 PER_JOBS JOB,
1188 PER_PEOPLE_EXTRA_INFO PEI
1189 WHERE PAF.PERSON_ID = PPET.PERSON_ID
1190 AND PAF.PERSON_ID = PEO.PERSON_ID
1191 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
1192 AND PEO.PER_INFORMATION1 = '13'
1193 AND PEO.PERSON_ID = pei.person_id (+)
1194 AND ( PEI.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
1195 OR ( PEI.INFORMATION_TYPE <> 'PER_US_ADDL_ETHNIC_CAT'
1196 AND not exists (
1197 SELECT
1198 1
1199 FROM
1200 PER_PEOPLE_EXTRA_INFO PEI2
1201 WHERE PEI2.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
1202 AND PEI2.PERSON_ID = PEI.PERSON_ID )
1203 AND PEI.PERSON_EXTRA_INFO_ID = (
1204 SELECT
1205 MAX(PEI1.PERSON_EXTRA_INFO_ID)
1206 FROM
1207 PER_PEOPLE_EXTRA_INFO PEI1
1208 WHERE PEI1.PERSON_ID = PEI.PERSON_ID ) )
1209 OR ( not exists (
1210 SELECT
1211 PERSON_EXTRA_INFO_ID
1212 FROM
1213 PER_PEOPLE_EXTRA_INFO PEI3
1214 WHERE PEI3.PERSON_ID = PEI.PERSON_ID ) ) )
1215 AND PPET.INFORMATION_TYPE = 'PQH_TENURE_STATUS'
1216 AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
1217 AND PAF.EFFECTIVE_END_DATE
1218 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
1219 AND PEO.EFFECTIVE_END_DATE
1220 AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
1221 AND PPET.PEI_INFORMATION1 in ( '01' )
1222 AND PPET.PEI_INFORMATION1 IS NOT NULL
1223 AND not exists (
1224 SELECT
1225 PEA.PERSON_ID
1226 FROM
1227 PER_PEOPLE_EXTRA_INFO PEA
1228 WHERE PEA.PERSON_ID = PEO.PERSON_ID
1229 AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
1230 AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
1231 AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
1232 AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
1233 ,'4712/12/31 00:00:00')) )
1234 AND PAF.PRIMARY_FLAG = 'Y'
1235 AND PAF.ASSIGNMENT_TYPE = 'E'
1236 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
1237 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
1238 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
1239 ,CP_FR
1240 ,CP_FT
1241 ,CP_PR
1242 ,CP_PT) IN ( 'FR' )
1243 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
1244 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
1245 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
1246 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
1247 AND PAF.JOB_ID = JOB.JOB_ID
1248 AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
1249 AND PAF.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
1250 AND PPP.CHANGE_DATE = (
1251 SELECT
1252 MAX(CHANGE_DATE)
1253 FROM
1254 PER_PAY_PROPOSALS PRO
1255 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
1256 AND PRO.CHANGE_DATE <= P_REPORT_DATE
1257 AND PRO.APPROVED = 'Y' )
1258 AND NVL(PPP.PROPOSED_SALARY_N
1259 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
1260 AND PAF.ORGANIZATION_ID IN (
1261 SELECT
1262 ORGANIZATION_ID
1263 FROM
1264 HR_ALL_ORGANIZATION_UNITS
1265 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID );
1266 Commented for IPED Enh #7033011 End */
1267
1268 CURSOR GET_LINE3_COUNTS IS
1269 SELECT
1270 '02' TENINFO,
1271 HLA.LOOKUP_CODE ARANK,
1272 -- Added for bug#11736960
1273 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1274 ,P_REPORT_DATE
1275 ,'Y')
1276 ,0
1277 ,DECODE(PEO.SEX
1278 ,'M'
1279 ,1
1280 ,NULL)
1281 ,NULL)) NRMEN,
1282 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1283 ,P_REPORT_DATE
1284 ,'Y')
1285 ,0
1286 ,DECODE(PEO.SEX
1287 ,'F'
1288 ,1
1289 ,NULL)
1290 ,NULL)) NRWMEN,
1291 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1292 ,P_REPORT_DATE
1293 ,'Y')
1294 ,2
1295 ,DECODE(PEO.SEX
1296 ,'M'
1297 ,1
1298 ,NULL)
1299 ,NULL)) BNHMEN,
1300 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1301 ,P_REPORT_DATE
1302 ,'Y')
1303 ,2
1304 ,DECODE(PEO.SEX
1305 ,'F'
1306 ,1
1307 ,NULL)
1308 ,NULL)) BNHWMEN,
1309 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1310 ,P_REPORT_DATE
1311 ,'Y')
1312 ,6
1313 ,DECODE(PEO.SEX
1314 ,'M'
1315 ,1
1316 ,NULL)
1317 ,NULL)) AM_ALMEN,
1318 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1319 ,P_REPORT_DATE
1320 ,'Y')
1321 ,6
1322 ,DECODE(PEO.SEX
1323 ,'F'
1324 ,1
1325 ,NULL)
1326 ,NULL)) AM_ALWMEN,
1327 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1328 ,P_REPORT_DATE
1329 ,'Y')
1330 ,4
1331 ,DECODE(PEO.SEX
1332 ,'M'
1333 ,1
1334 ,NULL)
1335 ,NULL)) AMEN,
1336 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1337 ,P_REPORT_DATE
1338 ,'Y')
1339 ,4
1340 ,DECODE(PEO.SEX
1341 ,'F'
1342 ,1
1343 ,NULL)
1344 ,NULL)) AWMEN,
1345 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1346 ,P_REPORT_DATE
1347 ,'Y')
1348 ,5
1349 ,DECODE(PEO.SEX
1350 ,'M'
1351 ,1
1352 ,NULL)
1353 ,NULL)) NH_OPMEN,
1354 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1355 ,P_REPORT_DATE
1356 ,'Y')
1357 ,5
1358 ,DECODE(PEO.SEX
1359 ,'F'
1360 ,1
1361 ,NULL)
1362 ,NULL)) NH_OPWMEN,
1363 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1364 ,P_REPORT_DATE
1365 ,'Y')
1366 ,3
1367 ,DECODE(PEO.SEX
1368 ,'M'
1369 ,1
1370 ,NULL)
1371 ,NULL)) HMEN,
1372 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1373 ,P_REPORT_DATE
1374 ,'Y')
1375 ,3
1376 ,DECODE(PEO.SEX
1377 ,'F'
1378 ,1
1379 ,NULL)
1380 ,NULL)) HWMEN,
1381 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1382 ,P_REPORT_DATE
1383 ,'Y')
1384 ,1
1385 ,DECODE(PEO.SEX
1386 ,'M'
1387 ,1
1388 ,NULL)
1389 ,NULL)) WNHMEN,
1390 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1391 ,P_REPORT_DATE
1392 ,'Y')
1393 ,1
1394 ,DECODE(PEO.SEX
1395 ,'F'
1396 ,1
1397 ,NULL)
1398 ,NULL)) WNHWMEN,
1399 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1400 ,P_REPORT_DATE
1401 ,'Y')
1402 ,99
1403 ,DECODE(PEO.SEX
1404 ,'M'
1405 ,1
1406 ,NULL)
1407 ,NULL)) URMEN,
1408 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1409 ,P_REPORT_DATE
1410 ,'Y')
1411 ,99
1412 ,DECODE(PEO.SEX
1413 ,'F'
1414 ,1
1415 ,NULL)
1416 ,NULL)) URWMEN,
1417 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1418 ,P_REPORT_DATE
1419 ,'Y')
1420 ,13
1421 ,DECODE(PEO.SEX
1422 ,'M'
1423 ,1
1424 ,NULL)
1425 ,NULL)) TMRMEN,
1426 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1427 ,P_REPORT_DATE
1428 ,'Y')
1429 ,13
1430 ,DECODE(PEO.SEX
1431 ,'F'
1432 ,1
1433 ,NULL)
1434 ,NULL)) TMRWMEN
1435 --
1436 /* Commented for bug#11736960 starts
1437 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1438 ,P_REPORT_DATE)
1439 ,'1'
1440 ,DECODE(PEO.SEX
1441 ,'M'
1442 ,1
1443 ,NULL)
1444 ,NULL)) NRMEN,
1445 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1446 ,P_REPORT_DATE)
1447 ,'1'
1448 ,DECODE(PEO.SEX
1449 ,'F'
1450 ,1
1451 ,NULL)
1452 ,NULL)) NRWMEN,
1453 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1454 ,P_REPORT_DATE)
1455 ,NULL
1456 ,(DECODE(PEO.PER_INFORMATION1
1457 ,'2'
1458 ,DECODE(PEO.SEX
1459 ,'M'
1460 ,1
1461 ,NULL)
1462 ,NULL)))) BNHMEN,
1463 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1464 ,P_REPORT_DATE)
1465 ,NULL
1466 ,(DECODE(PEO.PER_INFORMATION1
1467 ,'2'
1468 ,DECODE(PEO.SEX
1469 ,'F'
1470 ,1
1471 ,NULL)
1472 ,NULL)))) BNHWMEN,
1473 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1474 ,P_REPORT_DATE)
1475 ,NULL
1476 ,(DECODE(PEO.PER_INFORMATION1
1477 ,'6'
1478 ,DECODE(PEO.SEX
1479 ,'M'
1480 ,1
1481 ,NULL)
1482 ,NULL)))) AM_ALMEN,
1483 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1484 ,P_REPORT_DATE)
1485 ,NULL
1486 ,(DECODE(PEO.PER_INFORMATION1
1487 ,'6'
1488 ,DECODE(PEO.SEX
1489 ,'F'
1490 ,1
1491 ,NULL)
1492 ,NULL)))) AM_ALWMEN,
1493 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1494 ,P_REPORT_DATE)
1495 ,NULL
1496 ,(DECODE(PEO.PER_INFORMATION1
1497 ,'4'
1498 ,DECODE(PEO.SEX
1499 ,'M'
1500 ,1
1501 ,NULL)
1502 ,NULL)))) AMEN, -- Removed code of '5' for IPED Enh #7033011
1503 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1504 ,P_REPORT_DATE)
1505 ,NULL
1506 ,(DECODE(PEO.PER_INFORMATION1
1507 ,'4'
1508 ,DECODE(PEO.SEX
1509 ,'F'
1510 ,1
1511 ,NULL)
1512 ,NULL)))) AWMEN, -- Removed code of '5' for IPED Enh #7033011
1513 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1514 ,P_REPORT_DATE)
1515 ,NULL
1516 ,(DECODE(PEO.PER_INFORMATION1
1517 ,'5'
1518 ,DECODE(PEO.SEX
1519 ,'M'
1520 ,1
1521 ,NULL)
1522 ,NULL)))) NH_OPMEN, -- Added New column for IPED Enh #7033011
1523 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1524 ,P_REPORT_DATE)
1525 ,NULL
1526 ,(DECODE(PEO.PER_INFORMATION1
1527 ,'5'
1528 ,DECODE(PEO.SEX
1529 ,'F'
1530 ,1
1531 ,NULL)
1532 ,NULL)))) NH_OPWMEN, -- Added New column for IPED Enh #7033011
1533 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1534 ,P_REPORT_DATE)
1535 ,NULL
1536 ,(DECODE(PEO.PER_INFORMATION1
1537 ,'3'
1538 ,DECODE(PEO.SEX
1539 ,'M'
1540 ,1
1541 ,NULL)
1542 ,NULL)))) HMEN, -- Removed code of '9' for IPED Enh #7033011
1543 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1544 ,P_REPORT_DATE)
1545 ,NULL
1546 ,(DECODE(PEO.PER_INFORMATION1
1547 ,'3'
1548 ,DECODE(PEO.SEX
1549 ,'F'
1550 ,1
1551 ,NULL)
1552 ,NULL)))) HWMEN, -- Removed code of '9' for IPED Enh #7033011
1553 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1554 ,P_REPORT_DATE)
1555 ,NULL
1556 ,(DECODE(PEO.PER_INFORMATION1
1557 ,'1'
1558 ,DECODE(PEO.SEX
1559 ,'M'
1560 ,1
1561 ,NULL)
1562 ,NULL)))) WNHMEN,
1563 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1564 ,P_REPORT_DATE)
1565 ,NULL
1566 ,(DECODE(PEO.PER_INFORMATION1
1567 ,'1'
1568 ,DECODE(PEO.SEX
1569 ,'F'
1570 ,1
1571 ,NULL)
1572 ,NULL)))) WNHWMEN,
1573 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1574 ,P_REPORT_DATE)
1575 ,NULL
1576 ,(DECODE(PEO.PER_INFORMATION1
1577 ,NULL
1578 ,DECODE(PEO.SEX
1579 ,'M'
1580 ,1
1581 ,NULL)
1582 ,NULL)))) URMEN,
1583 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1584 ,P_REPORT_DATE)
1585 ,NULL
1586 ,(DECODE(PEO.PER_INFORMATION1
1587 ,NULL
1588 ,DECODE(PEO.SEX
1589 ,'F'
1590 ,1
1591 ,NULL)
1592 ,NULL)))) URWMEN,
1593 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1594 ,P_REPORT_DATE)
1595 ,NULL
1596 ,(DECODE(PEO.PER_INFORMATION1
1597 ,'13'
1598 ,DECODE(PEO.SEX
1599 ,'M'
1600 ,1
1601 ,NULL)
1602 ,NULL)))) TMRMEN, -- Added New column for IPED Enh #7033011
1603 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1604 ,P_REPORT_DATE)
1605 ,NULL
1606 ,(DECODE(PEO.PER_INFORMATION1
1607 ,'13'
1608 ,DECODE(PEO.SEX
1609 ,'F'
1610 ,1
1611 ,NULL)
1612 ,NULL)))) TMRWMEN -- Added New column for IPED Enh #7033011
1613 Commented for bug#11736960 ends */
1614 --
1615 FROM
1616 HR_LOOKUPS HLA,
1617 PER_ALL_PEOPLE_F PEO,
1618 PER_ALL_ASSIGNMENTS_F PAF,
1619 PER_ASSIGNMENT_STATUS_TYPES AST,
1620 PER_PEOPLE_EXTRA_INFO PPEA,
1621 PER_PEOPLE_EXTRA_INFO PPET,
1622 HR_LOOKUPS HL,
1623 PER_PAY_PROPOSALS PPP,
1624 PER_PAY_BASES PPB,
1625 PER_JOBS JOB
1626 WHERE PAF.PERSON_ID = PPEA.PERSON_ID
1627 AND PAF.PERSON_ID = PPET.PERSON_ID
1628 AND PAF.PERSON_ID = PEO.PERSON_ID
1629 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
1630 AND PPEA.INFORMATION_TYPE = 'PQH_ACADEMIC_RANK'
1631 AND PPET.INFORMATION_TYPE = 'PQH_TENURE_STATUS'
1632 AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
1633 AND PAF.EFFECTIVE_END_DATE
1634 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
1635 AND PEO.EFFECTIVE_END_DATE
1636 AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
1637 AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
1638 AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
1639 ,'4712/12/31 00:00:00'))
1640 AND PPET.PEI_INFORMATION1 in ( '02' , '04' )
1641 AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
1642 AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE
1643 AND PPEA.PEI_INFORMATION1 IS NOT NULL
1644 AND PPET.PEI_INFORMATION1 IS NOT NULL
1645 AND PPEA.PEI_INFORMATION1 not in ( '01' )
1646 AND PAF.PRIMARY_FLAG = 'Y'
1647 AND PAF.ASSIGNMENT_TYPE = 'E'
1648 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
1649 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
1650 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
1651 ,CP_FR
1652 ,CP_FT
1653 ,CP_PR
1654 ,CP_PT) IN ( 'FR' )
1655 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
1656 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
1657 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
1658 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
1659 AND PAF.JOB_ID = JOB.JOB_ID
1660 AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
1661 AND PAF.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
1662 AND PPP.CHANGE_DATE = (
1663 SELECT
1664 MAX(CHANGE_DATE)
1665 FROM
1666 PER_PAY_PROPOSALS PRO
1667 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
1668 AND PRO.CHANGE_DATE <= P_REPORT_DATE
1669 AND PRO.APPROVED = 'Y' )
1670 AND NVL(PPP.PROPOSED_SALARY_N
1671 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
1672 AND PAF.ORGANIZATION_ID IN (
1673 SELECT
1674 ORGANIZATION_ID
1675 FROM
1676 HR_ALL_ORGANIZATION_UNITS
1677 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
1678 GROUP BY
1679 '02',
1680 HLA.LOOKUP_CODE;
1681 /* Commented for IPED Enh #7033011 Start
1682 CURSOR GET_LINE3_TMR_COUNTS(C_LOOKUP_CODE IN VARCHAR2) IS
1683 SELECT
1684 COUNT(DECODE(PEI.PEI_INFORMATION5
1685 ,'2'
1686 ,DECODE(PEO.SEX
1687 ,'M'
1688 ,1
1689 ,NULL)
1690 ,NULL)) BNHMEN,
1691 COUNT(DECODE(PEI.PEI_INFORMATION5
1692 ,'2'
1693 ,DECODE(PEO.SEX
1694 ,'F'
1695 ,1
1696 ,NULL)
1697 ,NULL)) BNHWMEN,
1698 COUNT(DECODE(PEI.PEI_INFORMATION5
1699 ,'6'
1700 ,DECODE(PEO.SEX
1701 ,'M'
1702 ,1
1703 ,NULL)
1704 ,NULL)) AM_ALMEN,
1705 COUNT(DECODE(PEI.PEI_INFORMATION5
1706 ,'6'
1707 ,DECODE(PEO.SEX
1708 ,'F'
1709 ,1
1710 ,NULL)
1711 ,NULL)) AM_ALWMEN,
1712 COUNT(DECODE(PEI.PEI_INFORMATION5
1713 ,'4'
1714 ,DECODE(PEO.SEX
1715 ,'M'
1716 ,1
1717 ,NULL)
1718 ,'5'
1719 ,DECODE(PEO.SEX
1720 ,'M'
1721 ,1
1722 ,NULL)
1723 ,NULL)) A_PMEN,
1724 COUNT(DECODE(PEI.PEI_INFORMATION5
1725 ,'4'
1726 ,DECODE(PEO.SEX
1727 ,'F'
1728 ,1
1729 ,NULL)
1730 ,'5'
1731 ,DECODE(PEO.SEX
1732 ,'F'
1733 ,1
1734 ,NULL)
1735 ,NULL)) A_PWMEN,
1736 COUNT(DECODE(PEI.PEI_INFORMATION5
1737 ,'3'
1738 ,DECODE(PEO.SEX
1739 ,'M'
1740 ,1
1741 ,NULL)
1742 ,'9'
1743 ,DECODE(PEO.SEX
1744 ,'M'
1745 ,1
1746 ,NULL)
1747 ,NULL)) HMEN,
1748 COUNT(DECODE(PEI.PEI_INFORMATION5
1749 ,'3'
1750 ,DECODE(PEO.SEX
1751 ,'F'
1752 ,1
1753 ,NULL)
1754 ,'9'
1755 ,DECODE(PEO.SEX
1756 ,'F'
1757 ,1
1758 ,NULL)
1759 ,NULL)) HWMEN,
1760 COUNT(DECODE(PEI.PEI_INFORMATION5
1761 ,'1'
1762 ,DECODE(PEO.SEX
1763 ,'M'
1764 ,1
1765 ,NULL)
1766 ,NULL)) WNHMEN,
1767 COUNT(DECODE(PEI.PEI_INFORMATION5
1768 ,'1'
1769 ,DECODE(PEO.SEX
1770 ,'F'
1771 ,1
1772 ,NULL)
1773 ,NULL)) WNHWMEN,
1774 COUNT(DECODE(PEI.PEI_INFORMATION5
1775 ,NULL
1776 ,DECODE(PEO.SEX
1777 ,'M'
1778 ,1
1779 ,NULL)
1780 ,NULL)) URMEN,
1781 COUNT(DECODE(PEI.PEI_INFORMATION5
1782 ,NULL
1783 ,DECODE(PEO.SEX
1784 ,'F'
1785 ,1
1786 ,NULL)
1787 ,NULL)) URWMEN
1788 FROM
1789 HR_LOOKUPS HLA,
1790 PER_ALL_PEOPLE_F PEO,
1791 PER_ALL_ASSIGNMENTS_F PAF,
1792 PER_ASSIGNMENT_STATUS_TYPES AST,
1793 PER_PEOPLE_EXTRA_INFO PPEA,
1794 PER_PEOPLE_EXTRA_INFO PPET,
1795 HR_LOOKUPS HL,
1796 PER_PAY_PROPOSALS PPP,
1797 PER_PAY_BASES PPB,
1798 PER_JOBS JOB,
1799 PER_PEOPLE_EXTRA_INFO PEI
1800 WHERE PAF.PERSON_ID = PPEA.PERSON_ID
1801 AND PAF.PERSON_ID = PPET.PERSON_ID
1802 AND PAF.PERSON_ID = PEO.PERSON_ID
1803 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
1804 AND PEO.PER_INFORMATION1 = '13'
1805 AND PEO.PERSON_ID = pei.person_id (+)
1806 AND ( PEI.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
1807 OR ( PEI.INFORMATION_TYPE <> 'PER_US_ADDL_ETHNIC_CAT'
1808 AND not exists (
1809 SELECT
1810 1
1811 FROM
1812 PER_PEOPLE_EXTRA_INFO PEI2
1813 WHERE PEI2.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
1814 AND PEI2.PERSON_ID = PEI.PERSON_ID )
1815 AND PEI.PERSON_EXTRA_INFO_ID = (
1816 SELECT
1817 MAX(PEI1.PERSON_EXTRA_INFO_ID)
1818 FROM
1819 PER_PEOPLE_EXTRA_INFO PEI1
1820 WHERE PEI1.PERSON_ID = PEI.PERSON_ID ) )
1821 OR ( not exists (
1822 SELECT
1823 PERSON_EXTRA_INFO_ID
1824 FROM
1825 PER_PEOPLE_EXTRA_INFO PEI3
1826 WHERE PEI3.PERSON_ID = PEI.PERSON_ID ) ) )
1827 AND PPEA.INFORMATION_TYPE = 'PQH_ACADEMIC_RANK'
1828 AND PPET.INFORMATION_TYPE = 'PQH_TENURE_STATUS'
1829 AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
1830 AND PAF.EFFECTIVE_END_DATE
1831 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
1832 AND PEO.EFFECTIVE_END_DATE
1833 AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
1834 AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
1835 AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
1836 ,'4712/12/31 00:00:00'))
1837 AND PPET.PEI_INFORMATION1 in ( '02' , '04' )
1838 AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
1839 AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE
1840 AND PPEA.PEI_INFORMATION1 IS NOT NULL
1841 AND PPET.PEI_INFORMATION1 IS NOT NULL
1842 AND PPEA.PEI_INFORMATION1 not in ( '01' )
1843 AND PAF.PRIMARY_FLAG = 'Y'
1844 AND PAF.ASSIGNMENT_TYPE = 'E'
1845 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
1846 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
1847 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
1848 ,CP_FR
1849 ,CP_FT
1850 ,CP_PR
1851 ,CP_PT) IN ( 'FR' )
1852 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
1853 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
1854 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
1855 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
1856 AND PAF.JOB_ID = JOB.JOB_ID
1857 AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
1858 AND PAF.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
1859 AND PPP.CHANGE_DATE = (
1860 SELECT
1861 MAX(CHANGE_DATE)
1862 FROM
1863 PER_PAY_PROPOSALS PRO
1864 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
1865 AND PRO.CHANGE_DATE <= P_REPORT_DATE
1866 AND PRO.APPROVED = 'Y' )
1867 AND NVL(PPP.PROPOSED_SALARY_N
1868 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
1869 AND PAF.ORGANIZATION_ID IN (
1870 SELECT
1871 ORGANIZATION_ID
1872 FROM
1873 HR_ALL_ORGANIZATION_UNITS
1874 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
1875 AND HLA.LOOKUP_CODE = C_LOOKUP_CODE;
1876 Commented for IPED Enh #7033011 End */
1877
1878 CURSOR GET_LINE4_COUNTS IS
1879 SELECT
1880 '02' TENINFO,
1881 '01' ARANK,
1882 -- Added for bug#11736960
1883 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1884 ,P_REPORT_DATE
1885 ,'Y')
1886 ,0
1887 ,DECODE(PEO.SEX
1888 ,'M'
1889 ,1
1890 ,NULL)
1891 ,NULL)) NRMEN,
1892 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1893 ,P_REPORT_DATE
1894 ,'Y')
1895 ,0
1896 ,DECODE(PEO.SEX
1897 ,'F'
1898 ,1
1899 ,NULL)
1900 ,NULL)) NRWMEN,
1901 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1902 ,P_REPORT_DATE
1903 ,'Y')
1904 ,2
1905 ,DECODE(PEO.SEX
1906 ,'M'
1907 ,1
1908 ,NULL)
1909 ,NULL)) BNHMEN,
1910 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1911 ,P_REPORT_DATE
1912 ,'Y')
1913 ,2
1914 ,DECODE(PEO.SEX
1915 ,'F'
1916 ,1
1917 ,NULL)
1918 ,NULL)) BNHWMEN,
1919 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1920 ,P_REPORT_DATE
1921 ,'Y')
1922 ,6
1923 ,DECODE(PEO.SEX
1924 ,'M'
1925 ,1
1926 ,NULL)
1927 ,NULL)) AM_ALMEN,
1928 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1929 ,P_REPORT_DATE
1930 ,'Y')
1931 ,6
1932 ,DECODE(PEO.SEX
1933 ,'F'
1934 ,1
1935 ,NULL)
1936 ,NULL)) AM_ALWMEN,
1937 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1938 ,P_REPORT_DATE
1939 ,'Y')
1940 ,4
1941 ,DECODE(PEO.SEX
1942 ,'M'
1943 ,1
1944 ,NULL)
1945 ,NULL)) AMEN,
1946 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1947 ,P_REPORT_DATE
1948 ,'Y')
1949 ,4
1950 ,DECODE(PEO.SEX
1951 ,'F'
1952 ,1
1953 ,NULL)
1954 ,NULL)) AWMEN,
1955 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1956 ,P_REPORT_DATE
1957 ,'Y')
1958 ,5
1959 ,DECODE(PEO.SEX
1960 ,'M'
1961 ,1
1962 ,NULL)
1963 ,NULL)) NH_OPMEN,
1964 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1965 ,P_REPORT_DATE
1966 ,'Y')
1967 ,5
1968 ,DECODE(PEO.SEX
1969 ,'F'
1970 ,1
1971 ,NULL)
1972 ,NULL)) NH_OPWMEN,
1973 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1974 ,P_REPORT_DATE
1975 ,'Y')
1976 ,3
1977 ,DECODE(PEO.SEX
1978 ,'M'
1979 ,1
1980 ,NULL)
1981 ,NULL)) HMEN,
1982 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1983 ,P_REPORT_DATE
1984 ,'Y')
1985 ,3
1986 ,DECODE(PEO.SEX
1987 ,'F'
1988 ,1
1989 ,NULL)
1990 ,NULL)) HWMEN,
1991 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
1992 ,P_REPORT_DATE
1993 ,'Y')
1994 ,1
1995 ,DECODE(PEO.SEX
1996 ,'M'
1997 ,1
1998 ,NULL)
1999 ,NULL)) WNHMEN,
2000 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
2001 ,P_REPORT_DATE
2002 ,'Y')
2003 ,1
2004 ,DECODE(PEO.SEX
2005 ,'F'
2006 ,1
2007 ,NULL)
2008 ,NULL)) WNHWMEN,
2009 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
2010 ,P_REPORT_DATE
2011 ,'Y')
2012 ,99
2013 ,DECODE(PEO.SEX
2014 ,'M'
2015 ,1
2016 ,NULL)
2017 ,NULL)) URMEN,
2018 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
2019 ,P_REPORT_DATE
2020 ,'Y')
2021 ,99
2022 ,DECODE(PEO.SEX
2023 ,'F'
2024 ,1
2025 ,NULL)
2026 ,NULL)) URWMEN,
2027 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
2028 ,P_REPORT_DATE
2029 ,'Y')
2030 ,13
2031 ,DECODE(PEO.SEX
2032 ,'M'
2033 ,1
2034 ,NULL)
2035 ,NULL)) TMRMEN,
2036 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
2037 ,P_REPORT_DATE
2038 ,'Y')
2039 ,13
2040 ,DECODE(PEO.SEX
2041 ,'F'
2042 ,1
2043 ,NULL)
2044 ,NULL)) TMRWMEN
2045 --
2046 /* Commented for bug#11736960 starts
2047 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2048 ,P_REPORT_DATE)
2049 ,'1'
2050 ,DECODE(PEO.SEX
2051 ,'M'
2052 ,1
2053 ,NULL)
2054 ,NULL)) NRMEN,
2055 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2056 ,P_REPORT_DATE)
2057 ,'1'
2058 ,DECODE(PEO.SEX
2059 ,'F'
2060 ,1
2061 ,NULL)
2062 ,NULL)) NRWMEN,
2063 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2064 ,P_REPORT_DATE)
2065 ,NULL
2066 ,(DECODE(PEO.PER_INFORMATION1
2067 ,'2'
2068 ,DECODE(PEO.SEX
2069 ,'M'
2070 ,1
2071 ,NULL)
2072 ,NULL)))) BNHMEN,
2073 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2074 ,P_REPORT_DATE)
2075 ,NULL
2076 ,(DECODE(PEO.PER_INFORMATION1
2077 ,'2'
2078 ,DECODE(PEO.SEX
2079 ,'F'
2080 ,1
2081 ,NULL)
2082 ,NULL)))) BNHWMEN,
2083 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2084 ,P_REPORT_DATE)
2085 ,NULL
2086 ,(DECODE(PEO.PER_INFORMATION1
2087 ,'6'
2088 ,DECODE(PEO.SEX
2089 ,'M'
2090 ,1
2091 ,NULL)
2092 ,NULL)))) AM_ALMEN,
2093 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2094 ,P_REPORT_DATE)
2095 ,NULL
2096 ,(DECODE(PEO.PER_INFORMATION1
2097 ,'6'
2098 ,DECODE(PEO.SEX
2099 ,'F'
2100 ,1
2101 ,NULL)
2102 ,NULL)))) AM_ALWMEN,
2103 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2104 ,P_REPORT_DATE)
2105 ,NULL
2106 ,(DECODE(PEO.PER_INFORMATION1
2107 ,'4'
2108 ,DECODE(PEO.SEX
2109 ,'M'
2110 ,1
2111 ,NULL)
2112 ,NULL)))) AMEN, -- Removed code of '5' for IPED Enh #7033011
2113 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2114 ,P_REPORT_DATE)
2115 ,NULL
2116 ,(DECODE(PEO.PER_INFORMATION1
2117 ,'4'
2118 ,DECODE(PEO.SEX
2119 ,'F'
2120 ,1
2121 ,NULL)
2122 ,NULL)))) AWMEN, -- Removed code of '5' for IPED Enh #7033011
2123 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2124 ,P_REPORT_DATE)
2125 ,NULL
2126 ,(DECODE(PEO.PER_INFORMATION1
2127 ,'5'
2128 ,DECODE(PEO.SEX
2129 ,'M'
2130 ,1
2131 ,NULL)
2132 ,NULL)))) NH_OPMEN, -- Added New column for IPED Enh #7033011
2133 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2134 ,P_REPORT_DATE)
2135 ,NULL
2136 ,(DECODE(PEO.PER_INFORMATION1
2137 ,'5'
2138 ,DECODE(PEO.SEX
2139 ,'F'
2140 ,1
2141 ,NULL)
2142 ,NULL)))) NH_OPWMEN, -- Added New column for IPED Enh #7033011
2143 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2144 ,P_REPORT_DATE)
2145 ,NULL
2146 ,(DECODE(PEO.PER_INFORMATION1
2147 ,'3'
2148 ,DECODE(PEO.SEX
2149 ,'M'
2150 ,1
2151 ,NULL)
2152 ,NULL)))) HMEN, -- Removed code of '9' for IPED Enh #7033011
2153 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2154 ,P_REPORT_DATE)
2155 ,NULL
2156 ,(DECODE(PEO.PER_INFORMATION1
2157 ,'3'
2158 ,DECODE(PEO.SEX
2159 ,'F'
2160 ,1
2161 ,NULL)
2162 ,NULL)))) HWMEN, -- Removed code of '9' for IPED Enh #7033011
2163 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2164 ,P_REPORT_DATE)
2165 ,NULL
2166 ,(DECODE(PEO.PER_INFORMATION1
2167 ,'1'
2168 ,DECODE(PEO.SEX
2169 ,'M'
2170 ,1
2171 ,NULL)
2172 ,NULL)))) WNHMEN,
2173 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2174 ,P_REPORT_DATE)
2175 ,NULL
2176 ,(DECODE(PEO.PER_INFORMATION1
2177 ,'1'
2178 ,DECODE(PEO.SEX
2179 ,'F'
2180 ,1
2181 ,NULL)
2182 ,NULL)))) WNHWMEN,
2183 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2184 ,P_REPORT_DATE)
2185 ,NULL
2186 ,(DECODE(PEO.PER_INFORMATION1
2187 ,NULL
2188 ,DECODE(PEO.SEX
2189 ,'M'
2190 ,1
2191 ,NULL)
2192 ,NULL)))) URMEN,
2193 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2194 ,P_REPORT_DATE)
2195 ,NULL
2196 ,(DECODE(PEO.PER_INFORMATION1
2197 ,NULL
2198 ,DECODE(PEO.SEX
2199 ,'F'
2200 ,1
2201 ,NULL)
2202 ,NULL)))) URWMEN,
2203 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2204 ,P_REPORT_DATE)
2205 ,NULL
2206 ,(DECODE(PEO.PER_INFORMATION1
2207 ,'13'
2208 ,DECODE(PEO.SEX
2209 ,'M'
2210 ,1
2211 ,NULL)
2212 ,NULL)))) TMRMEN, -- Added New column for IPED Enh #7033011
2213 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2214 ,P_REPORT_DATE)
2215 ,NULL
2216 ,(DECODE(PEO.PER_INFORMATION1
2217 ,'13'
2218 ,DECODE(PEO.SEX
2219 ,'F'
2220 ,1
2221 ,NULL)
2222 ,NULL)))) TMRWMEN -- Added New column for IPED Enh #7033011
2223 Commented for bug#11736960 ends */
2224 --
2225 FROM
2226 PER_ALL_PEOPLE_F PEO,
2227 PER_ALL_ASSIGNMENTS_F PAF,
2228 PER_ASSIGNMENT_STATUS_TYPES AST,
2229 PER_PEOPLE_EXTRA_INFO PPET,
2230 HR_LOOKUPS HL,
2231 PER_PAY_PROPOSALS PPP,
2232 PER_PAY_BASES PPB,
2233 PER_JOBS JOB
2234 WHERE PAF.PERSON_ID = PPET.PERSON_ID
2235 AND PAF.PERSON_ID = PEO.PERSON_ID
2236 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
2237 AND PPET.INFORMATION_TYPE = 'PQH_TENURE_STATUS'
2238 AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
2239 AND PAF.EFFECTIVE_END_DATE
2240 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
2241 AND PEO.EFFECTIVE_END_DATE
2242 AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
2243 AND PPET.PEI_INFORMATION1 in ( '02' , '04' )
2244 AND PPET.PEI_INFORMATION1 IS NOT NULL
2245 AND not exists (
2246 SELECT
2247 PEA.PERSON_ID
2248 FROM
2249 PER_PEOPLE_EXTRA_INFO PEA
2250 WHERE PEA.PERSON_ID = PEO.PERSON_ID
2251 AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
2252 AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
2253 AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
2254 AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
2255 ,'4712/12/31 00:00:00')) )
2256 AND PAF.PRIMARY_FLAG = 'Y'
2257 AND PAF.ASSIGNMENT_TYPE = 'E'
2258 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
2259 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
2260 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
2261 ,CP_FR
2262 ,CP_FT
2263 ,CP_PR
2264 ,CP_PT) IN ( 'FR' )
2265 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
2266 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
2267 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
2268 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
2269 AND PAF.JOB_ID = JOB.JOB_ID
2270 AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
2271 AND PAF.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
2272 AND PPP.CHANGE_DATE = (
2273 SELECT
2274 MAX(CHANGE_DATE)
2275 FROM
2276 PER_PAY_PROPOSALS PRO
2277 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
2278 AND PRO.CHANGE_DATE <= P_REPORT_DATE
2279 AND PRO.APPROVED = 'Y' )
2280 AND NVL(PPP.PROPOSED_SALARY_N
2281 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
2282 AND PAF.ORGANIZATION_ID IN (
2283 SELECT
2284 ORGANIZATION_ID
2285 FROM
2286 HR_ALL_ORGANIZATION_UNITS
2287 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
2288 GROUP BY
2289 '02',
2290 '01';
2291
2292 /* Commented for IPED Enh #7033011 Start
2293 CURSOR GET_LINE4_TMR_COUNTS IS
2294 SELECT
2295 COUNT(DECODE(PEI.PEI_INFORMATION5
2296 ,'2'
2297 ,DECODE(PEO.SEX
2298 ,'M'
2299 ,1
2300 ,NULL)
2301 ,NULL)) BNHMEN,
2302 COUNT(DECODE(PEI.PEI_INFORMATION5
2303 ,'2'
2304 ,DECODE(PEO.SEX
2305 ,'F'
2306 ,1
2307 ,NULL)
2308 ,NULL)) BNHWMEN,
2309 COUNT(DECODE(PEI.PEI_INFORMATION5
2310 ,'6'
2311 ,DECODE(PEO.SEX
2312 ,'M'
2313 ,1
2314 ,NULL)
2315 ,NULL)) AM_ALMEN,
2316 COUNT(DECODE(PEI.PEI_INFORMATION5
2317 ,'6'
2318 ,DECODE(PEO.SEX
2319 ,'F'
2320 ,1
2321 ,NULL)
2322 ,NULL)) AM_ALWMEN,
2323 COUNT(DECODE(PEI.PEI_INFORMATION5
2324 ,'4'
2325 ,DECODE(PEO.SEX
2326 ,'M'
2327 ,1
2328 ,NULL)
2329 ,'5'
2330 ,DECODE(PEO.SEX
2331 ,'M'
2332 ,1
2333 ,NULL)
2334 ,NULL)) A_PMEN,
2335 COUNT(DECODE(PEI.PEI_INFORMATION5
2336 ,'4'
2337 ,DECODE(PEO.SEX
2338 ,'F'
2339 ,1
2340 ,NULL)
2341 ,'5'
2342 ,DECODE(PEO.SEX
2343 ,'F'
2344 ,1
2345 ,NULL)
2346 ,NULL)) A_PWMEN,
2347 COUNT(DECODE(PEI.PEI_INFORMATION5
2348 ,'3'
2349 ,DECODE(PEO.SEX
2350 ,'M'
2351 ,1
2352 ,NULL)
2353 ,'9'
2354 ,DECODE(PEO.SEX
2355 ,'M'
2356 ,1
2357 ,NULL)
2358 ,NULL)) HMEN,
2359 COUNT(DECODE(PEI.PEI_INFORMATION5
2360 ,'3'
2361 ,DECODE(PEO.SEX
2362 ,'F'
2363 ,1
2364 ,NULL)
2365 ,'9'
2366 ,DECODE(PEO.SEX
2367 ,'F'
2368 ,1
2369 ,NULL)
2370 ,NULL)) HWMEN,
2371 COUNT(DECODE(PEI.PEI_INFORMATION5
2372 ,'1'
2373 ,DECODE(PEO.SEX
2374 ,'M'
2375 ,1
2376 ,NULL)
2377 ,NULL)) WNHMEN,
2378 COUNT(DECODE(PEI.PEI_INFORMATION5
2379 ,'1'
2380 ,DECODE(PEO.SEX
2381 ,'F'
2382 ,1
2383 ,NULL)
2384 ,NULL)) WNHWMEN,
2385 COUNT(DECODE(PEI.PEI_INFORMATION5
2386 ,NULL
2387 ,DECODE(PEO.SEX
2388 ,'M'
2389 ,1
2390 ,NULL)
2391 ,NULL)) URMEN,
2392 COUNT(DECODE(PEI.PEI_INFORMATION5
2393 ,NULL
2394 ,DECODE(PEO.SEX
2395 ,'F'
2396 ,1
2397 ,NULL)
2398 ,NULL)) URWMEN
2399 FROM
2400 PER_ALL_PEOPLE_F PEO,
2401 PER_ALL_ASSIGNMENTS_F PAF,
2402 PER_ASSIGNMENT_STATUS_TYPES AST,
2403 PER_PEOPLE_EXTRA_INFO PPET,
2404 HR_LOOKUPS HL,
2405 PER_PAY_PROPOSALS PPP,
2406 PER_PAY_BASES PPB,
2407 PER_JOBS JOB,
2408 PER_PEOPLE_EXTRA_INFO PEI
2409 WHERE PAF.PERSON_ID = PPET.PERSON_ID
2410 AND PAF.PERSON_ID = PEO.PERSON_ID
2411 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
2412 AND PEO.PER_INFORMATION1 = '13'
2413 AND PEO.PERSON_ID = pei.person_id (+)
2414 AND ( PEI.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
2415 OR ( PEI.INFORMATION_TYPE <> 'PER_US_ADDL_ETHNIC_CAT'
2416 AND not exists (
2417 SELECT
2418 1
2419 FROM
2420 PER_PEOPLE_EXTRA_INFO PEI2
2421 WHERE PEI2.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
2422 AND PEI2.PERSON_ID = PEI.PERSON_ID )
2423 AND PEI.PERSON_EXTRA_INFO_ID = (
2424 SELECT
2425 MAX(PEI1.PERSON_EXTRA_INFO_ID)
2426 FROM
2427 PER_PEOPLE_EXTRA_INFO PEI1
2428 WHERE PEI1.PERSON_ID = PEI.PERSON_ID ) )
2429 OR ( not exists (
2430 SELECT
2431 PERSON_EXTRA_INFO_ID
2432 FROM
2433 PER_PEOPLE_EXTRA_INFO PEI3
2434 WHERE PEI3.PERSON_ID = PEI.PERSON_ID ) ) )
2435 AND PPET.INFORMATION_TYPE = 'PQH_TENURE_STATUS'
2436 AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
2437 AND PAF.EFFECTIVE_END_DATE
2438 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
2439 AND PEO.EFFECTIVE_END_DATE
2440 AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
2441 AND PPET.PEI_INFORMATION1 in ( '02' , '04' )
2442 AND PPET.PEI_INFORMATION1 IS NOT NULL
2443 AND not exists (
2444 SELECT
2445 PEA.PERSON_ID
2446 FROM
2447 PER_PEOPLE_EXTRA_INFO PEA
2448 WHERE PEA.PERSON_ID = PEO.PERSON_ID
2449 AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
2450 AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
2451 AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
2452 AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
2453 ,'4712/12/31 00:00:00')) )
2454 AND PAF.PRIMARY_FLAG = 'Y'
2455 AND PAF.ASSIGNMENT_TYPE = 'E'
2456 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
2457 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
2458 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
2459 ,CP_FR
2460 ,CP_FT
2461 ,CP_PR
2462 ,CP_PT) IN ( 'FR' )
2463 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
2464 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
2465 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
2466 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
2467 AND PAF.JOB_ID = JOB.JOB_ID
2468 AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
2469 AND PAF.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
2470 AND PPP.CHANGE_DATE = (
2471 SELECT
2472 MAX(CHANGE_DATE)
2473 FROM
2474 PER_PAY_PROPOSALS PRO
2475 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
2476 AND PRO.CHANGE_DATE <= P_REPORT_DATE
2477 AND PRO.APPROVED = 'Y' )
2478 AND NVL(PPP.PROPOSED_SALARY_N
2479 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
2480 AND PAF.ORGANIZATION_ID IN (
2481 SELECT
2482 ORGANIZATION_ID
2483 FROM
2484 HR_ALL_ORGANIZATION_UNITS
2485 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID );
2486 Commented for IPED Enh #7033011 End */
2487
2488 CURSOR GET_LINE5_COUNTS IS
2489 SELECT
2490 '03' TENINFO,
2491 HLA.LOOKUP_CODE ARANK,
2492 -- Added for bug#11736960
2493 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
2494 ,P_REPORT_DATE
2495 ,'Y')
2496 ,0
2497 ,DECODE(PEO.SEX
2498 ,'M'
2499 ,1
2500 ,NULL)
2501 ,NULL)) NRMEN,
2502 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
2503 ,P_REPORT_DATE
2504 ,'Y')
2505 ,0
2506 ,DECODE(PEO.SEX
2507 ,'F'
2508 ,1
2509 ,NULL)
2510 ,NULL)) NRWMEN,
2511 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
2512 ,P_REPORT_DATE
2513 ,'Y')
2514 ,2
2515 ,DECODE(PEO.SEX
2516 ,'M'
2517 ,1
2518 ,NULL)
2519 ,NULL)) BNHMEN,
2520 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
2521 ,P_REPORT_DATE
2522 ,'Y')
2523 ,2
2524 ,DECODE(PEO.SEX
2525 ,'F'
2526 ,1
2527 ,NULL)
2528 ,NULL)) BNHWMEN,
2529 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
2530 ,P_REPORT_DATE
2531 ,'Y')
2532 ,6
2533 ,DECODE(PEO.SEX
2534 ,'M'
2535 ,1
2536 ,NULL)
2537 ,NULL)) AM_ALMEN,
2538 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
2539 ,P_REPORT_DATE
2540 ,'Y')
2541 ,6
2542 ,DECODE(PEO.SEX
2543 ,'F'
2544 ,1
2545 ,NULL)
2546 ,NULL)) AM_ALWMEN,
2547 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
2548 ,P_REPORT_DATE
2549 ,'Y')
2550 ,4
2551 ,DECODE(PEO.SEX
2552 ,'M'
2553 ,1
2554 ,NULL)
2555 ,NULL)) AMEN,
2556 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
2557 ,P_REPORT_DATE
2558 ,'Y')
2559 ,4
2560 ,DECODE(PEO.SEX
2561 ,'F'
2562 ,1
2563 ,NULL)
2564 ,NULL)) AWMEN,
2565 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
2566 ,P_REPORT_DATE
2567 ,'Y')
2568 ,5
2569 ,DECODE(PEO.SEX
2570 ,'M'
2571 ,1
2572 ,NULL)
2573 ,NULL)) NH_OPMEN,
2574 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
2575 ,P_REPORT_DATE
2576 ,'Y')
2577 ,5
2578 ,DECODE(PEO.SEX
2579 ,'F'
2580 ,1
2581 ,NULL)
2582 ,NULL)) NH_OPWMEN,
2583 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
2584 ,P_REPORT_DATE
2585 ,'Y')
2586 ,3
2587 ,DECODE(PEO.SEX
2588 ,'M'
2589 ,1
2590 ,NULL)
2591 ,NULL)) HMEN,
2592 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
2593 ,P_REPORT_DATE
2594 ,'Y')
2595 ,3
2596 ,DECODE(PEO.SEX
2597 ,'F'
2598 ,1
2599 ,NULL)
2600 ,NULL)) HWMEN,
2601 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
2602 ,P_REPORT_DATE
2603 ,'Y')
2604 ,1
2605 ,DECODE(PEO.SEX
2606 ,'M'
2607 ,1
2608 ,NULL)
2609 ,NULL)) WNHMEN,
2610 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
2611 ,P_REPORT_DATE
2612 ,'Y')
2613 ,1
2614 ,DECODE(PEO.SEX
2615 ,'F'
2616 ,1
2617 ,NULL)
2618 ,NULL)) WNHWMEN,
2619 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
2620 ,P_REPORT_DATE
2621 ,'Y')
2622 ,99
2623 ,DECODE(PEO.SEX
2624 ,'M'
2625 ,1
2626 ,NULL)
2627 ,NULL)) URMEN,
2628 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
2629 ,P_REPORT_DATE
2630 ,'Y')
2631 ,99
2632 ,DECODE(PEO.SEX
2633 ,'F'
2634 ,1
2635 ,NULL)
2636 ,NULL)) URWMEN,
2637 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
2638 ,P_REPORT_DATE
2639 ,'Y')
2640 ,13
2641 ,DECODE(PEO.SEX
2642 ,'M'
2643 ,1
2644 ,NULL)
2645 ,NULL)) TMRMEN,
2646 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
2647 ,P_REPORT_DATE
2648 ,'Y')
2649 ,13
2650 ,DECODE(PEO.SEX
2651 ,'F'
2652 ,1
2653 ,NULL)
2654 ,NULL)) TMRWMEN
2655 --
2656 /* Commented for bug#11736960 starts
2657 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2658 ,P_REPORT_DATE)
2659 ,'1'
2660 ,DECODE(PEO.SEX
2661 ,'M'
2662 ,1
2663 ,NULL)
2664 ,NULL)) NRMEN,
2665 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2666 ,P_REPORT_DATE)
2667 ,'1'
2668 ,DECODE(PEO.SEX
2669 ,'F'
2670 ,1
2671 ,NULL)
2672 ,NULL)) NRWMEN,
2673 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2674 ,P_REPORT_DATE)
2675 ,NULL
2676 ,(DECODE(PEO.PER_INFORMATION1
2677 ,'2'
2678 ,DECODE(PEO.SEX
2679 ,'M'
2680 ,1
2681 ,NULL)
2682 ,NULL)))) BNHMEN,
2683 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2684 ,P_REPORT_DATE)
2685 ,NULL
2686 ,(DECODE(PEO.PER_INFORMATION1
2687 ,'2'
2688 ,DECODE(PEO.SEX
2689 ,'F'
2690 ,1
2691 ,NULL)
2692 ,NULL)))) BNHWMEN,
2693 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2694 ,P_REPORT_DATE)
2695 ,NULL
2696 ,(DECODE(PEO.PER_INFORMATION1
2697 ,'6'
2698 ,DECODE(PEO.SEX
2699 ,'M'
2700 ,1
2701 ,NULL)
2702 ,NULL)))) AM_ALMEN,
2703 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2704 ,P_REPORT_DATE)
2705 ,NULL
2706 ,(DECODE(PEO.PER_INFORMATION1
2707 ,'6'
2708 ,DECODE(PEO.SEX
2709 ,'F'
2710 ,1
2711 ,NULL)
2712 ,NULL)))) AM_ALWMEN,
2713 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2714 ,P_REPORT_DATE)
2715 ,NULL
2716 ,(DECODE(PEO.PER_INFORMATION1
2717 ,'4'
2718 ,DECODE(PEO.SEX
2719 ,'M'
2720 ,1
2721 ,NULL)
2722 ,NULL)))) AMEN, -- Removed code of '5' for IPED Enh #7033011
2723 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2724 ,P_REPORT_DATE)
2725 ,NULL
2726 ,(DECODE(PEO.PER_INFORMATION1
2727 ,'4'
2728 ,DECODE(PEO.SEX
2729 ,'F'
2730 ,1
2731 ,NULL)
2732 ,NULL)))) AWMEN, -- Removed code of '5' for IPED Enh #7033011
2733 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2734 ,P_REPORT_DATE)
2735 ,NULL
2736 ,(DECODE(PEO.PER_INFORMATION1
2737 ,'5'
2738 ,DECODE(PEO.SEX
2739 ,'M'
2740 ,1
2741 ,NULL)
2742 ,NULL)))) NH_OPMEN, -- Added New column for IPED Enh #7033011
2743 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2744 ,P_REPORT_DATE)
2745 ,NULL
2746 ,(DECODE(PEO.PER_INFORMATION1
2747 ,'5'
2748 ,DECODE(PEO.SEX
2749 ,'F'
2750 ,1
2751 ,NULL)
2752 ,NULL)))) NH_OPWMEN, -- Added New column for IPED Enh #7033011
2753 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2754 ,P_REPORT_DATE)
2755 ,NULL
2756 ,(DECODE(PEO.PER_INFORMATION1
2757 ,'3'
2758 ,DECODE(PEO.SEX
2759 ,'M'
2760 ,1
2761 ,NULL)
2762 ,NULL)))) HMEN, -- Removed code of '9' for IPED Enh #7033011
2763 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2764 ,P_REPORT_DATE)
2765 ,NULL
2766 ,(DECODE(PEO.PER_INFORMATION1
2767 ,'3'
2768 ,DECODE(PEO.SEX
2769 ,'F'
2770 ,1
2771 ,NULL)
2772 ,NULL)))) HWMEN, -- Removed code of '9' for IPED Enh #7033011
2773 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2774 ,P_REPORT_DATE)
2775 ,NULL
2776 ,(DECODE(PEO.PER_INFORMATION1
2777 ,'1'
2778 ,DECODE(PEO.SEX
2779 ,'M'
2780 ,1
2781 ,NULL)
2782 ,NULL)))) WNHMEN,
2783 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2784 ,P_REPORT_DATE)
2785 ,NULL
2786 ,(DECODE(PEO.PER_INFORMATION1
2787 ,'1'
2788 ,DECODE(PEO.SEX
2789 ,'F'
2790 ,1
2791 ,NULL)
2792 ,NULL)))) WNHWMEN,
2793 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2794 ,P_REPORT_DATE)
2795 ,NULL
2796 ,(DECODE(PEO.PER_INFORMATION1
2797 ,NULL
2798 ,DECODE(PEO.SEX
2799 ,'M'
2800 ,1
2801 ,NULL)
2802 ,NULL)))) URMEN,
2803 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2804 ,P_REPORT_DATE)
2805 ,NULL
2806 ,(DECODE(PEO.PER_INFORMATION1
2807 ,NULL
2808 ,DECODE(PEO.SEX
2809 ,'F'
2810 ,1
2811 ,NULL)
2812 ,NULL)))) URWMEN,
2813 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2814 ,P_REPORT_DATE)
2815 ,NULL
2816 ,(DECODE(PEO.PER_INFORMATION1
2817 ,'13'
2818 ,DECODE(PEO.SEX
2819 ,'M'
2820 ,1
2821 ,NULL)
2822 ,NULL)))) TMRMEN, -- Added New column for IPED Enh #7033011
2823 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2824 ,P_REPORT_DATE)
2825 ,NULL
2826 ,(DECODE(PEO.PER_INFORMATION1
2827 ,'13'
2828 ,DECODE(PEO.SEX
2829 ,'F'
2830 ,1
2831 ,NULL)
2832 ,NULL)))) TMRWMEN -- Added New column for IPED Enh #7033011
2833 Commented for bug#11736960 ends */
2834 --
2835 FROM
2836 HR_LOOKUPS HLA,
2837 PER_ALL_PEOPLE_F PEO,
2838 PER_ALL_ASSIGNMENTS_F PAF,
2839 PER_ASSIGNMENT_STATUS_TYPES AST,
2840 PER_PEOPLE_EXTRA_INFO PPEA,
2841 HR_LOOKUPS HL,
2842 PER_PAY_PROPOSALS PPP,
2843 PER_PAY_BASES PPB,
2844 PER_JOBS JOB
2845 WHERE PAF.PERSON_ID = PEO.PERSON_ID
2846 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
2847 AND PAF.PERSON_ID = PPEA.PERSON_ID
2848 AND PPEA.INFORMATION_TYPE = 'PQH_ACADEMIC_RANK'
2849 AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
2850 AND PAF.EFFECTIVE_END_DATE
2851 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
2852 AND PEO.EFFECTIVE_END_DATE
2853 AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
2854 AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE
2855 AND PPEA.PEI_INFORMATION1 not in ( '01' )
2856 AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
2857 AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
2858 ,'4712/12/31 00:00:00'))
2859 AND PPEA.PEI_INFORMATION1 IS NOT NULL
2860 AND not exists (
2861 SELECT
2862 PET.PERSON_ID
2863 FROM
2864 PER_PEOPLE_EXTRA_INFO PET
2865 WHERE PET.PERSON_ID = PEO.PERSON_ID
2866 AND PET.INFORMATION_TYPE in ( 'PQH_TENURE_STATUS' )
2867 AND PET.PEI_INFORMATION1 in ( '01' , '02' , '04' )
2868 AND FND_DATE.CANONICAL_TO_DATE(PET.PEI_INFORMATION2) <= P_REPORT_DATE )
2869 AND PAF.PRIMARY_FLAG = 'Y'
2870 AND PAF.ASSIGNMENT_TYPE = 'E'
2871 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
2872 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
2873 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
2874 ,CP_FR
2875 ,CP_FT
2876 ,CP_PR
2877 ,CP_PT) IN ( 'FR' )
2878 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
2879 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
2880 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
2881 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
2882 AND PAF.JOB_ID = JOB.JOB_ID
2883 AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
2884 AND PAF.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
2885 AND PPP.CHANGE_DATE = (
2886 SELECT
2887 MAX(CHANGE_DATE)
2888 FROM
2889 PER_PAY_PROPOSALS PRO
2890 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
2891 AND PRO.CHANGE_DATE <= P_REPORT_DATE
2892 AND PRO.APPROVED = 'Y' )
2893 AND NVL(PPP.PROPOSED_SALARY_N
2894 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
2895 AND PAF.ORGANIZATION_ID IN (
2896 SELECT
2897 ORGANIZATION_ID
2898 FROM
2899 HR_ALL_ORGANIZATION_UNITS
2900 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
2901 GROUP BY
2902 '03',
2903 HLA.LOOKUP_CODE;
2904
2905 /* Commented for IPED Enh #7033011 Start
2906 CURSOR GET_LINE5_TMR_COUNTS(C_LOOKUP_CODE IN VARCHAR2) IS
2907 SELECT
2908 COUNT(DECODE(PEI.PEI_INFORMATION5
2909 ,'2'
2910 ,DECODE(PEO.SEX
2911 ,'M'
2912 ,1
2913 ,NULL)
2914 ,NULL)) BNHMEN,
2915 COUNT(DECODE(PEI.PEI_INFORMATION5
2916 ,'2'
2917 ,DECODE(PEO.SEX
2918 ,'F'
2919 ,1
2920 ,NULL)
2921 ,NULL)) BNHWMEN,
2922 COUNT(DECODE(PEI.PEI_INFORMATION5
2923 ,'6'
2924 ,DECODE(PEO.SEX
2925 ,'M'
2926 ,1
2927 ,NULL)
2928 ,NULL)) AM_ALMEN,
2929 COUNT(DECODE(PEI.PEI_INFORMATION5
2930 ,'6'
2931 ,DECODE(PEO.SEX
2932 ,'F'
2933 ,1
2934 ,NULL)
2935 ,NULL)) AM_ALWMEN,
2936 COUNT(DECODE(PEI.PEI_INFORMATION5
2937 ,'4'
2938 ,DECODE(PEO.SEX
2939 ,'M'
2940 ,1
2941 ,NULL)
2942 ,'5'
2943 ,DECODE(PEO.SEX
2944 ,'M'
2945 ,1
2946 ,NULL)
2947 ,NULL)) A_PMEN,
2948 COUNT(DECODE(PEI.PEI_INFORMATION5
2949 ,'4'
2950 ,DECODE(PEO.SEX
2951 ,'F'
2952 ,1
2953 ,NULL)
2954 ,'5'
2955 ,DECODE(PEO.SEX
2956 ,'F'
2957 ,1
2958 ,NULL)
2959 ,NULL)) A_PWMEN,
2960 COUNT(DECODE(PEI.PEI_INFORMATION5
2961 ,'3'
2962 ,DECODE(PEO.SEX
2963 ,'M'
2964 ,1
2965 ,NULL)
2966 ,'9'
2967 ,DECODE(PEO.SEX
2968 ,'M'
2969 ,1
2970 ,NULL)
2971 ,NULL)) HMEN,
2972 COUNT(DECODE(PEI.PEI_INFORMATION5
2973 ,'3'
2974 ,DECODE(PEO.SEX
2975 ,'F'
2976 ,1
2977 ,NULL)
2978 ,'9'
2979 ,DECODE(PEO.SEX
2980 ,'F'
2981 ,1
2982 ,NULL)
2983 ,NULL)) HWMEN,
2984 COUNT(DECODE(PEI.PEI_INFORMATION5
2985 ,'1'
2986 ,DECODE(PEO.SEX
2987 ,'M'
2988 ,1
2989 ,NULL)
2990 ,NULL)) WNHMEN,
2991 COUNT(DECODE(PEI.PEI_INFORMATION5
2992 ,'1'
2993 ,DECODE(PEO.SEX
2994 ,'F'
2995 ,1
2996 ,NULL)
2997 ,NULL)) WNHWMEN,
2998 COUNT(DECODE(PEI.PEI_INFORMATION5
2999 ,NULL
3000 ,DECODE(PEO.SEX
3001 ,'M'
3002 ,1
3003 ,NULL)
3004 ,NULL)) URMEN,
3005 COUNT(DECODE(PEI.PEI_INFORMATION5
3006 ,NULL
3007 ,DECODE(PEO.SEX
3008 ,'F'
3009 ,1
3010 ,NULL)
3011 ,NULL)) URWMEN
3012 FROM
3013 HR_LOOKUPS HLA,
3014 PER_ALL_PEOPLE_F PEO,
3015 PER_ALL_ASSIGNMENTS_F PAF,
3016 PER_ASSIGNMENT_STATUS_TYPES AST,
3017 PER_PEOPLE_EXTRA_INFO PPEA,
3018 HR_LOOKUPS HL,
3019 PER_PAY_PROPOSALS PPP,
3020 PER_PAY_BASES PPB,
3021 PER_JOBS JOB,
3022 PER_PEOPLE_EXTRA_INFO PEI
3023 WHERE PAF.PERSON_ID = PEO.PERSON_ID
3024 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
3025 AND PEO.PER_INFORMATION1 = '13'
3026 AND PEO.PERSON_ID = pei.person_id (+)
3027 AND ( PEI.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
3028 OR ( PEI.INFORMATION_TYPE <> 'PER_US_ADDL_ETHNIC_CAT'
3029 AND not exists (
3030 SELECT
3031 1
3032 FROM
3033 PER_PEOPLE_EXTRA_INFO PEI2
3034 WHERE PEI2.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
3035 AND PEI2.PERSON_ID = PEI.PERSON_ID )
3036 AND PEI.PERSON_EXTRA_INFO_ID = (
3037 SELECT
3038 MAX(PEI1.PERSON_EXTRA_INFO_ID)
3039 FROM
3040 PER_PEOPLE_EXTRA_INFO PEI1
3041 WHERE PEI1.PERSON_ID = PEI.PERSON_ID ) )
3042 OR ( not exists (
3043 SELECT
3044 PERSON_EXTRA_INFO_ID
3045 FROM
3046 PER_PEOPLE_EXTRA_INFO PEI3
3047 WHERE PEI3.PERSON_ID = PEI.PERSON_ID ) ) )
3048 AND PAF.PERSON_ID = PPEA.PERSON_ID
3049 AND PPEA.INFORMATION_TYPE = 'PQH_ACADEMIC_RANK'
3050 AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
3051 AND PAF.EFFECTIVE_END_DATE
3052 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
3053 AND PEO.EFFECTIVE_END_DATE
3054 AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
3055 AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE
3056 AND PPEA.PEI_INFORMATION1 not in ( '01' )
3057 AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
3058 AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
3059 ,'4712/12/31 00:00:00'))
3060 AND PPEA.PEI_INFORMATION1 IS NOT NULL
3061 AND not exists (
3062 SELECT
3063 PET.PERSON_ID
3064 FROM
3065 PER_PEOPLE_EXTRA_INFO PET
3066 WHERE PET.PERSON_ID = PEO.PERSON_ID
3067 AND PET.INFORMATION_TYPE in ( 'PQH_TENURE_STATUS' )
3068 AND PET.PEI_INFORMATION1 in ( '01' , '02' , '04' )
3069 AND FND_DATE.CANONICAL_TO_DATE(PET.PEI_INFORMATION2) <= P_REPORT_DATE )
3070 AND PAF.PRIMARY_FLAG = 'Y'
3071 AND PAF.ASSIGNMENT_TYPE = 'E'
3072 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
3073 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
3074 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
3075 ,CP_FR
3076 ,CP_FT
3077 ,CP_PR
3078 ,CP_PT) IN ( 'FR' )
3079 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
3080 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
3081 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
3082 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
3083 AND PAF.JOB_ID = JOB.JOB_ID
3084 AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
3085 AND PAF.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
3086 AND PPP.CHANGE_DATE = (
3087 SELECT
3088 MAX(CHANGE_DATE)
3089 FROM
3090 PER_PAY_PROPOSALS PRO
3091 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
3092 AND PRO.CHANGE_DATE <= P_REPORT_DATE
3093 AND PRO.APPROVED = 'Y' )
3094 AND NVL(PPP.PROPOSED_SALARY_N
3095 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
3096 AND PAF.ORGANIZATION_ID IN (
3097 SELECT
3098 ORGANIZATION_ID
3099 FROM
3100 HR_ALL_ORGANIZATION_UNITS
3101 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
3102 AND HLA.LOOKUP_CODE = C_LOOKUP_CODE;
3103 Commented for IPED Enh #7033011 End */
3104
3105 CURSOR GET_LINE6_COUNTS IS
3106 SELECT
3107 '03' TENINFO,
3108 '01' ARANK,
3109 -- Added for bug#11736960
3110 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
3111 ,P_REPORT_DATE
3112 ,'Y')
3113 ,0
3114 ,DECODE(PEO.SEX
3115 ,'M'
3116 ,1
3117 ,NULL)
3118 ,NULL)) NRMEN,
3119 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
3120 ,P_REPORT_DATE
3121 ,'Y')
3122 ,0
3123 ,DECODE(PEO.SEX
3124 ,'F'
3125 ,1
3126 ,NULL)
3127 ,NULL)) NRWMEN,
3128 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
3129 ,P_REPORT_DATE
3130 ,'Y')
3131 ,2
3132 ,DECODE(PEO.SEX
3133 ,'M'
3134 ,1
3135 ,NULL)
3136 ,NULL)) BNHMEN,
3137 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
3138 ,P_REPORT_DATE
3139 ,'Y')
3140 ,2
3141 ,DECODE(PEO.SEX
3142 ,'F'
3143 ,1
3144 ,NULL)
3145 ,NULL)) BNHWMEN,
3146 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
3147 ,P_REPORT_DATE
3148 ,'Y')
3149 ,6
3150 ,DECODE(PEO.SEX
3151 ,'M'
3152 ,1
3153 ,NULL)
3154 ,NULL)) AM_ALMEN,
3155 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
3156 ,P_REPORT_DATE
3157 ,'Y')
3158 ,6
3159 ,DECODE(PEO.SEX
3160 ,'F'
3161 ,1
3162 ,NULL)
3163 ,NULL)) AM_ALWMEN,
3164 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
3165 ,P_REPORT_DATE
3166 ,'Y')
3167 ,4
3168 ,DECODE(PEO.SEX
3169 ,'M'
3170 ,1
3171 ,NULL)
3172 ,NULL)) AMEN,
3173 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
3174 ,P_REPORT_DATE
3175 ,'Y')
3176 ,4
3177 ,DECODE(PEO.SEX
3178 ,'F'
3179 ,1
3180 ,NULL)
3181 ,NULL)) AWMEN,
3182 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
3183 ,P_REPORT_DATE
3184 ,'Y')
3185 ,5
3186 ,DECODE(PEO.SEX
3187 ,'M'
3188 ,1
3189 ,NULL)
3190 ,NULL)) NH_OPMEN,
3191 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
3192 ,P_REPORT_DATE
3193 ,'Y')
3194 ,5
3195 ,DECODE(PEO.SEX
3196 ,'F'
3197 ,1
3198 ,NULL)
3199 ,NULL)) NH_OPWMEN,
3200 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
3201 ,P_REPORT_DATE
3202 ,'Y')
3203 ,3
3204 ,DECODE(PEO.SEX
3205 ,'M'
3206 ,1
3207 ,NULL)
3208 ,NULL)) HMEN,
3209 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
3210 ,P_REPORT_DATE
3211 ,'Y')
3212 ,3
3213 ,DECODE(PEO.SEX
3214 ,'F'
3215 ,1
3216 ,NULL)
3217 ,NULL)) HWMEN,
3218 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
3219 ,P_REPORT_DATE
3220 ,'Y')
3221 ,1
3222 ,DECODE(PEO.SEX
3223 ,'M'
3224 ,1
3225 ,NULL)
3226 ,NULL)) WNHMEN,
3227 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
3228 ,P_REPORT_DATE
3229 ,'Y')
3230 ,1
3231 ,DECODE(PEO.SEX
3232 ,'F'
3233 ,1
3234 ,NULL)
3235 ,NULL)) WNHWMEN,
3236 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
3237 ,P_REPORT_DATE
3238 ,'Y')
3239 ,99
3240 ,DECODE(PEO.SEX
3241 ,'M'
3242 ,1
3243 ,NULL)
3244 ,NULL)) URMEN,
3245 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
3246 ,P_REPORT_DATE
3247 ,'Y')
3248 ,99
3249 ,DECODE(PEO.SEX
3250 ,'F'
3251 ,1
3252 ,NULL)
3253 ,NULL)) URWMEN,
3254 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
3255 ,P_REPORT_DATE
3256 ,'Y')
3257 ,13
3258 ,DECODE(PEO.SEX
3259 ,'M'
3260 ,1
3261 ,NULL)
3262 ,NULL)) TMRMEN,
3263 COUNT(DECODE(PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PEO.PERSON_ID
3264 ,P_REPORT_DATE
3265 ,'Y')
3266 ,13
3267 ,DECODE(PEO.SEX
3268 ,'F'
3269 ,1
3270 ,NULL)
3271 ,NULL)) TMRWMEN
3272 --
3273 /* Commented for bug#11736960 starts
3274 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
3275 ,P_REPORT_DATE)
3276 ,'1'
3277 ,DECODE(PEO.SEX
3278 ,'M'
3279 ,1
3280 ,NULL)
3281 ,NULL)) NRMEN,
3282 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
3283 ,P_REPORT_DATE)
3284 ,'1'
3285 ,DECODE(PEO.SEX
3286 ,'F'
3287 ,1
3288 ,NULL)
3289 ,NULL)) NRWMEN,
3290 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
3291 ,P_REPORT_DATE)
3292 ,NULL
3293 ,(DECODE(PEO.PER_INFORMATION1
3294 ,'2'
3295 ,DECODE(PEO.SEX
3296 ,'M'
3297 ,1
3298 ,NULL)
3299 ,NULL)))) BNHMEN,
3300 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
3301 ,P_REPORT_DATE)
3302 ,NULL
3303 ,(DECODE(PEO.PER_INFORMATION1
3304 ,'2'
3305 ,DECODE(PEO.SEX
3306 ,'F'
3307 ,1
3308 ,NULL)
3309 ,NULL)))) BNHWMEN,
3310 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
3311 ,P_REPORT_DATE)
3312 ,NULL
3313 ,(DECODE(PEO.PER_INFORMATION1
3314 ,'6'
3315 ,DECODE(PEO.SEX
3316 ,'M'
3317 ,1
3318 ,NULL)
3319 ,NULL)))) AM_ALMEN,
3320 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
3321 ,P_REPORT_DATE)
3322 ,NULL
3323 ,(DECODE(PEO.PER_INFORMATION1
3324 ,'6'
3325 ,DECODE(PEO.SEX
3326 ,'F'
3327 ,1
3328 ,NULL)
3329 ,NULL)))) AM_ALWMEN,
3330 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
3331 ,P_REPORT_DATE)
3332 ,NULL
3333 ,(DECODE(PEO.PER_INFORMATION1
3334 ,'4'
3335 ,DECODE(PEO.SEX
3336 ,'M'
3337 ,1
3338 ,NULL)
3339 ,NULL)))) AMEN, -- Removed code of '5' for IPED Enh #7033011
3340 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
3341 ,P_REPORT_DATE)
3342 ,NULL
3343 ,(DECODE(PEO.PER_INFORMATION1
3344 ,'4'
3345 ,DECODE(PEO.SEX
3346 ,'F'
3347 ,1
3348 ,NULL)
3349 ,NULL)))) AWMEN, -- Removed code of '5' for IPED Enh #7033011
3350 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
3351 ,P_REPORT_DATE)
3352 ,NULL
3353 ,(DECODE(PEO.PER_INFORMATION1
3354 ,'5'
3355 ,DECODE(PEO.SEX
3356 ,'M'
3357 ,1
3358 ,NULL)
3359 ,NULL)))) NH_OPMEN, -- Added New column for IPED Enh #7033011
3360 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
3361 ,P_REPORT_DATE)
3362 ,NULL
3363 ,(DECODE(PEO.PER_INFORMATION1
3364 ,'5'
3365 ,DECODE(PEO.SEX
3366 ,'F'
3367 ,1
3368 ,NULL)
3369 ,NULL)))) NH_OPWMEN, -- Added New column for IPED Enh #7033011
3370 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
3371 ,P_REPORT_DATE)
3372 ,NULL
3373 ,(DECODE(PEO.PER_INFORMATION1
3374 ,'3'
3375 ,DECODE(PEO.SEX
3376 ,'M'
3377 ,1
3378 ,NULL)
3379 ,NULL)))) HMEN, -- Removed code of '9' for IPED Enh #7033011
3380 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
3381 ,P_REPORT_DATE)
3382 ,NULL
3383 ,(DECODE(PEO.PER_INFORMATION1
3384 ,'3'
3385 ,DECODE(PEO.SEX
3386 ,'F'
3387 ,1
3388 ,NULL)
3389 ,NULL)))) HWMEN, -- Removed code of '9' for IPED Enh #7033011
3390 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
3391 ,P_REPORT_DATE)
3392 ,NULL
3393 ,(DECODE(PEO.PER_INFORMATION1
3394 ,'1'
3395 ,DECODE(PEO.SEX
3396 ,'M'
3397 ,1
3398 ,NULL)
3399 ,NULL)))) WNHMEN,
3400 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
3401 ,P_REPORT_DATE)
3402 ,NULL
3403 ,(DECODE(PEO.PER_INFORMATION1
3404 ,'1'
3405 ,DECODE(PEO.SEX
3406 ,'F'
3407 ,1
3408 ,NULL)
3409 ,NULL)))) WNHWMEN,
3410 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
3411 ,P_REPORT_DATE)
3412 ,NULL
3413 ,(DECODE(PEO.PER_INFORMATION1
3414 ,NULL
3415 ,DECODE(PEO.SEX
3416 ,'M'
3417 ,1
3418 ,NULL)
3419 ,NULL)))) URMEN,
3420 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
3421 ,P_REPORT_DATE)
3422 ,NULL
3423 ,(DECODE(PEO.PER_INFORMATION1
3424 ,NULL
3425 ,DECODE(PEO.SEX
3426 ,'F'
3427 ,1
3428 ,NULL)
3429 ,NULL)))) URWMEN,
3430 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
3431 ,P_REPORT_DATE)
3432 ,NULL
3433 ,(DECODE(PEO.PER_INFORMATION1
3434 ,'13'
3435 ,DECODE(PEO.SEX
3436 ,'M'
3437 ,1
3438 ,NULL)
3439 ,NULL)))) TMRMEN, -- Added New column for IPED Enh #7033011
3440 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
3441 ,P_REPORT_DATE)
3442 ,NULL
3443 ,(DECODE(PEO.PER_INFORMATION1
3444 ,'13'
3445 ,DECODE(PEO.SEX
3446 ,'F'
3447 ,1
3448 ,NULL)
3449 ,NULL)))) TMRWMEN -- Added New column for IPED Enh #7033011
3450 Commented for bug#11736960 ends */
3451 --
3452 FROM
3453 PER_ALL_PEOPLE_F PEO,
3454 PER_ALL_ASSIGNMENTS_F PAF,
3455 PER_ASSIGNMENT_STATUS_TYPES AST,
3456 HR_LOOKUPS HL,
3457 PER_PAY_PROPOSALS PPP,
3458 PER_PAY_BASES PPB,
3459 PER_JOBS JOB
3460 WHERE PAF.PERSON_ID = PEO.PERSON_ID
3461 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
3462 AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
3463 AND PAF.EFFECTIVE_END_DATE
3464 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
3465 AND PEO.EFFECTIVE_END_DATE
3466 AND not exists (
3467 SELECT
3468 PET.PERSON_ID
3469 FROM
3470 PER_PEOPLE_EXTRA_INFO PET
3471 WHERE PET.PERSON_ID = PEO.PERSON_ID
3472 AND PET.INFORMATION_TYPE in ( 'PQH_TENURE_STATUS' )
3473 AND PET.PEI_INFORMATION1 in ( '01' , '02' , '04' )
3474 AND FND_DATE.CANONICAL_TO_DATE(PET.PEI_INFORMATION2) <= P_REPORT_DATE )
3475 AND not exists (
3476 SELECT
3477 PEA.PERSON_ID
3478 FROM
3479 PER_PEOPLE_EXTRA_INFO PEA
3480 WHERE PEA.PERSON_ID = PEO.PERSON_ID
3481 AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
3482 AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
3483 AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
3484 AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
3485 ,'4712/12/31 00:00:00')) )
3486 AND PAF.PRIMARY_FLAG = 'Y'
3487 AND PAF.ASSIGNMENT_TYPE = 'E'
3488 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
3489 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
3490 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
3491 ,CP_FR
3492 ,CP_FT
3493 ,CP_PR
3494 ,CP_PT) IN ( 'FR' )
3495 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
3496 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
3497 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
3498 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
3499 AND PAF.JOB_ID = JOB.JOB_ID
3500 AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
3501 AND PAF.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
3502 AND PPP.CHANGE_DATE = (
3503 SELECT
3504 MAX(CHANGE_DATE)
3505 FROM
3506 PER_PAY_PROPOSALS PRO
3507 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
3508 AND PRO.CHANGE_DATE <= P_REPORT_DATE
3509 AND PRO.APPROVED = 'Y' )
3510 AND NVL(PPP.PROPOSED_SALARY_N
3511 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
3512 AND PAF.ORGANIZATION_ID IN (
3513 SELECT
3514 ORGANIZATION_ID
3515 FROM
3516 HR_ALL_ORGANIZATION_UNITS
3517 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
3518 GROUP BY
3519 '03',
3520 '01';
3521 /* Commented for IPED Enh #7033011 Start
3522 CURSOR GET_LINE6_TMR_COUNTS IS
3523 SELECT
3524 COUNT(DECODE(PEI.PEI_INFORMATION5
3525 ,'2'
3526 ,DECODE(PEO.SEX
3527 ,'M'
3528 ,1
3529 ,NULL)
3530 ,NULL)) BNHMEN,
3531 COUNT(DECODE(PEI.PEI_INFORMATION5
3532 ,'2'
3533 ,DECODE(PEO.SEX
3534 ,'F'
3535 ,1
3536 ,NULL)
3537 ,NULL)) BNHWMEN,
3538 COUNT(DECODE(PEI.PEI_INFORMATION5
3539 ,'6'
3540 ,DECODE(PEO.SEX
3541 ,'M'
3542 ,1
3543 ,NULL)
3544 ,NULL)) AM_ALMEN,
3545 COUNT(DECODE(PEI.PEI_INFORMATION5
3546 ,'6'
3547 ,DECODE(PEO.SEX
3548 ,'F'
3549 ,1
3550 ,NULL)
3551 ,NULL)) AM_ALWMEN,
3552 COUNT(DECODE(PEI.PEI_INFORMATION5
3553 ,'4'
3554 ,DECODE(PEO.SEX
3555 ,'M'
3556 ,1
3557 ,NULL)
3558 ,'5'
3559 ,DECODE(PEO.SEX
3560 ,'M'
3561 ,1
3562 ,NULL)
3563 ,NULL)) A_PMEN,
3564 COUNT(DECODE(PEI.PEI_INFORMATION5
3565 ,'4'
3566 ,DECODE(PEO.SEX
3567 ,'F'
3568 ,1
3569 ,NULL)
3570 ,'5'
3571 ,DECODE(PEO.SEX
3572 ,'F'
3573 ,1
3574 ,NULL)
3575 ,NULL)) A_PWMEN,
3576 COUNT(DECODE(PEI.PEI_INFORMATION5
3577 ,'3'
3578 ,DECODE(PEO.SEX
3579 ,'M'
3580 ,1
3581 ,NULL)
3582 ,'9'
3583 ,DECODE(PEO.SEX
3584 ,'M'
3585 ,1
3586 ,NULL)
3587 ,NULL)) HMEN,
3588 COUNT(DECODE(PEI.PEI_INFORMATION5
3589 ,'3'
3590 ,DECODE(PEO.SEX
3591 ,'F'
3592 ,1
3593 ,NULL)
3594 ,'9'
3595 ,DECODE(PEO.SEX
3596 ,'F'
3597 ,1
3598 ,NULL)
3599 ,NULL)) HWMEN,
3600 COUNT(DECODE(PEI.PEI_INFORMATION5
3601 ,'1'
3602 ,DECODE(PEO.SEX
3603 ,'M'
3604 ,1
3605 ,NULL)
3606 ,NULL)) WNHMEN,
3607 COUNT(DECODE(PEI.PEI_INFORMATION5
3608 ,'1'
3609 ,DECODE(PEO.SEX
3610 ,'F'
3611 ,1
3612 ,NULL)
3613 ,NULL)) WNHWMEN,
3614 COUNT(DECODE(PEI.PEI_INFORMATION5
3615 ,NULL
3616 ,DECODE(PEO.SEX
3617 ,'M'
3618 ,1
3619 ,NULL)
3620 ,NULL)) URMEN,
3621 COUNT(DECODE(PEI.PEI_INFORMATION5
3622 ,NULL
3623 ,DECODE(PEO.SEX
3624 ,'F'
3625 ,1
3626 ,NULL)
3627 ,NULL)) URWMEN
3628 FROM
3629 PER_ALL_PEOPLE_F PEO,
3630 PER_ALL_ASSIGNMENTS_F PAF,
3631 PER_ASSIGNMENT_STATUS_TYPES AST,
3632 HR_LOOKUPS HL,
3633 PER_PAY_PROPOSALS PPP,
3634 PER_PAY_BASES PPB,
3635 PER_JOBS JOB,
3636 PER_PEOPLE_EXTRA_INFO PEI
3637 WHERE PAF.PERSON_ID = PEO.PERSON_ID
3638 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
3639 AND PEO.PER_INFORMATION1 = '13'
3640 AND PEO.PERSON_ID = pei.person_id (+)
3641 AND ( PEI.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
3642 OR ( PEI.INFORMATION_TYPE <> 'PER_US_ADDL_ETHNIC_CAT'
3643 AND not exists (
3644 SELECT
3645 1
3646 FROM
3647 PER_PEOPLE_EXTRA_INFO PEI2
3648 WHERE PEI2.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
3649 AND PEI2.PERSON_ID = PEI.PERSON_ID )
3650 AND PEI.PERSON_EXTRA_INFO_ID = (
3651 SELECT
3652 MAX(PEI1.PERSON_EXTRA_INFO_ID)
3653 FROM
3654 PER_PEOPLE_EXTRA_INFO PEI1
3655 WHERE PEI1.PERSON_ID = PEI.PERSON_ID ) )
3656 OR ( not exists (
3657 SELECT
3658 PERSON_EXTRA_INFO_ID
3659 FROM
3660 PER_PEOPLE_EXTRA_INFO PEI3
3661 WHERE PEI3.PERSON_ID = PEI.PERSON_ID ) ) )
3662 AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
3663 AND PAF.EFFECTIVE_END_DATE
3664 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
3665 AND PEO.EFFECTIVE_END_DATE
3666 AND not exists (
3667 SELECT
3668 PET.PERSON_ID
3669 FROM
3670 PER_PEOPLE_EXTRA_INFO PET
3671 WHERE PET.PERSON_ID = PEO.PERSON_ID
3672 AND PET.INFORMATION_TYPE in ( 'PQH_TENURE_STATUS' )
3673 AND PET.PEI_INFORMATION1 in ( '01' , '02' , '04' )
3674 AND FND_DATE.CANONICAL_TO_DATE(PET.PEI_INFORMATION2) <= P_REPORT_DATE )
3675 AND not exists (
3676 SELECT
3677 PEA.PERSON_ID
3678 FROM
3679 PER_PEOPLE_EXTRA_INFO PEA
3680 WHERE PEA.PERSON_ID = PEO.PERSON_ID
3681 AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
3682 AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
3683 AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
3684 AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
3685 ,'4712/12/31 00:00:00')) )
3686 AND PAF.PRIMARY_FLAG = 'Y'
3687 AND PAF.ASSIGNMENT_TYPE = 'E'
3688 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
3689 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
3690 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
3691 ,CP_FR
3692 ,CP_FT
3693 ,CP_PR
3694 ,CP_PT) IN ( 'FR' )
3695 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
3696 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
3697 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
3698 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
3699 AND PAF.JOB_ID = JOB.JOB_ID
3700 AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
3701 AND PAF.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
3702 AND PPP.CHANGE_DATE = (
3703 SELECT
3704 MAX(CHANGE_DATE)
3705 FROM
3706 PER_PAY_PROPOSALS PRO
3707 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
3708 AND PRO.CHANGE_DATE <= P_REPORT_DATE
3709 AND PRO.APPROVED = 'Y' )
3710 AND NVL(PPP.PROPOSED_SALARY_N
3711 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
3712 AND PAF.ORGANIZATION_ID IN (
3713 SELECT
3714 ORGANIZATION_ID
3715 FROM
3716 HR_ALL_ORGANIZATION_UNITS
3717 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID );
3718 Commented for IPED Enh #7033011 End */
3719 BEGIN
3720 -- HR_STANDARD.EVENT('BEFORE REPORT');
3721 CP_REPORT_DATE := to_char(P_REPORT_DATE,'DD-MON-YYYY');
3722 PQH_EMPLOYMENT_CATEGORY.FETCH_EMPL_CATEGORIES(P_BUSINESS_GROUP_ID
3723 ,L_FR
3724 ,L_FT
3725 ,L_PR
3726 ,L_PT);
3727 CP_FR := L_FR;
3728 CP_FT := L_FT;
3729 CP_PR := L_PR;
3730 CP_PT := L_PT;
3731 FOR i IN GET_LINE1_COUNTS LOOP
3732 LINE := I.TENINFO;
3733 SC := I.ARANK;
3734 L_NR_MEN := I.NRMEN;
3735 L_NR_WMEN := I.NRWMEN;
3736 L_BNH_MEN := I.BNHMEN;
3737 L_BNH_WMEN := I.BNHWMEN;
3738 L_AMAI_MEN := I.AM_ALMEN;
3739 L_AMAI_WMEN := I.AM_ALWMEN;
3740 L_A_MEN := I.AMEN; -- change from l_ap_men to l_a_men for IPED Enh #7033011
3741 L_A_WMEN := I.AWMEN; -- change from l_ap_men to l_a_men for IPED Enh #7033011
3742 L_NHOP_MEN := I.NH_OPMEN; -- Added new column for IPED Enh #7033011
3743 L_NHOP_WMEN := I.NH_OPWMEN; -- Added new column for IPED Enh #7033011
3744 L_H_MEN := I.HMEN;
3745 L_H_WMEN := I.HWMEN;
3746 L_WNH_MEN := I.WNHMEN;
3747 L_WNH_WMEN := I.WNHWMEN;
3748 L_UR_MEN := I.URMEN;
3749 L_UR_WMEN := I.URWMEN;
3750 L_TMR_MEN := I.TMRMEN;
3751 L_TMR_WMEN := I.TMRWMEN;
3752 /* Added new variables l_nhop_men and l_tmr_men to l_tot_men for IPED Enh #7033011 */
3753 L_TOT_MEN := L_NR_MEN + L_BNH_MEN + L_AMAI_MEN + L_A_MEN + L_NHOP_MEN + L_H_MEN + L_WNH_MEN + L_UR_MEN + L_TMR_MEN;
3754 /* Added new variables l_nhop_wmen and l_tmr_wmen to l_tot_wmen for IPED Enh #7033011 */
3755 L_TOT_WMEN := L_NR_WMEN + L_BNH_WMEN + L_AMAI_WMEN + L_A_WMEN + L_NHOP_MEN + L_H_WMEN + L_WNH_WMEN + L_UR_WMEN + L_TMR_WMEN;
3756 /* Commented for IPED Enh #7033011 Start
3757 FOR j IN GET_LINE1_TMRACES_COUNTS(sc) LOOP
3758 L_BNH_MEN := L_BNH_MEN + J.BNHMEN;
3759 L_BNH_WMEN := L_BNH_WMEN + J.BNHWMEN;
3760 L_AMAI_MEN := L_AMAI_MEN + J.AM_ALMEN;
3761 L_AMAI_WMEN := L_AMAI_WMEN + J.AM_ALWMEN;
3762 L_AP_MEN := L_AP_MEN + J.A_PMEN;
3763 L_AP_WMEN := L_AP_WMEN + J.A_PWMEN;
3764 L_H_MEN := L_H_MEN + J.HMEN;
3765 L_H_WMEN := L_H_WMEN + J.HWMEN;
3766 L_WNH_MEN := L_WNH_MEN + J.WNHMEN;
3767 L_WNH_WMEN := L_WNH_WMEN + J.WNHWMEN;
3768 L_UR_MEN := L_UR_MEN + J.URMEN;
3769 L_UR_WMEN := L_UR_WMEN + J.URWMEN;
3770 L_TOT_MEN := L_TOT_MEN + J.BNHMEN + J.AM_ALMEN + J.A_PMEN + J.HMEN + J.WNHMEN + J.URMEN;
3771 L_TOT_WMEN := L_TOT_WMEN + J.BNHWMEN + J.AM_ALWMEN + J.A_PWMEN + J.HWMEN + J.WNHWMEN + J.URWMEN;
3772 END LOOP;
3773 Commented for IPED Enh #7033011 End */
3774 INSERT INTO PAY_US_RPT_TOTALS
3775 (SESSION_ID
3776 ,ATTRIBUTE1
3777 ,VALUE1
3778 ,VALUE2
3779 ,VALUE3
3780 ,VALUE4
3781 ,VALUE5
3782 ,VALUE6
3783 ,VALUE7
3784 ,VALUE8
3785 ,VALUE9
3786 ,VALUE10
3787 ,VALUE11
3788 ,VALUE12
3789 ,VALUE13
3790 ,VALUE14
3791 ,VALUE15
3792 ,VALUE16
3793 ,VALUE17
3794 ,VALUE18
3795 ,VALUE19 -- Added new column for IPED Enh #7033011
3796 ,VALUE20 -- Added new column for IPED Enh #7033011
3797 ,VALUE21 -- Added new column for IPED Enh #7033011
3798 ,VALUE22 -- Added new column for IPED Enh #7033011
3799 )
3800 VALUES (USERENV('sessionid')
3801 ,'IPED8'
3802 ,LINE
3803 ,SC
3804 ,L_NR_MEN
3805 ,L_NR_WMEN
3806 ,L_BNH_MEN
3807 ,L_BNH_WMEN
3808 ,L_AMAI_MEN
3809 ,L_AMAI_WMEN
3810 ,L_A_MEN
3811 ,L_A_WMEN
3812 ,L_H_MEN
3813 ,L_H_WMEN
3814 ,L_WNH_MEN
3815 ,L_WNH_WMEN
3816 ,L_UR_MEN
3817 ,L_UR_WMEN
3818 ,L_TOT_MEN
3819 ,L_TOT_WMEN
3820 ,L_NHOP_MEN -- Added new column for IPED Enh #7033011
3821 ,L_NHOP_WMEN -- Added new column for IPED Enh #7033011
3822 ,L_TMR_MEN -- Added new column for IPED Enh #7033011
3823 ,L_TMR_WMEN -- Added new column for IPED Enh #7033011
3824 );
3825 COMMIT;
3826 END LOOP;
3827 OPEN GET_LINE2_COUNTS;
3828 /* Added new variables l_nhop_men,l_nhop_Wmen,l_tmr_Wmen and l_tmr_men for IPED Enh #7033011 */
3829 FETCH GET_LINE2_COUNTS
3830 INTO LINE,SC,L_NR_MEN,L_NR_WMEN,L_BNH_MEN,L_BNH_WMEN,L_AMAI_MEN,L_AMAI_WMEN,L_A_MEN,L_A_WMEN,L_NHOP_MEN,L_NHOP_WMEN,L_H_MEN,L_H_WMEN,L_WNH_MEN,L_WNH_WMEN,L_UR_MEN,L_UR_WMEN,L_TMR_MEN,L_TMR_WMEN;
3831 CLOSE GET_LINE2_COUNTS;
3832 /* Added new variables l_nhop_men and l_tmr_men to l_tot_men for IPED Enh #7033011 */
3833 L_TOT_MEN := L_NR_MEN + L_BNH_MEN + L_AMAI_MEN + L_A_MEN + L_NHOP_MEN + L_H_MEN + L_WNH_MEN + L_UR_MEN + L_TMR_MEN;
3834 /* Added new variables l_nhop_Wmen and l_tmr_Wmen to l_tot_Wmen for IPED Enh #7033011 */
3835 L_TOT_WMEN := L_NR_WMEN + L_BNH_WMEN + L_AMAI_WMEN + L_A_WMEN + L_NHOP_WMEN + L_H_WMEN + L_WNH_WMEN + L_UR_WMEN + L_TMR_WMEN;
3836 /* Commented for IPED Enh #7033011 Start
3837 OPEN GET_TMR_LINE2_COUNTS;
3838 FETCH GET_TMR_LINE2_COUNTS
3839 INTO L_TMR_BNH_MEN,L_TMR_BNH_WMEN,L_TMR_AMAI_MEN,L_TMR_AMAI_WMEN,L_TMR_AP_MEN,L_TMR_AP_WMEN,L_TMR_H_MEN,L_TMR_H_WMEN,L_TMR_WNH_MEN,L_TMR_WNH_WMEN,L_TMR_UR_MEN,L_TMR_UR_WMEN;
3840 CLOSE GET_TMR_LINE2_COUNTS;
3841 L_BNH_MEN := L_BNH_MEN + L_TMR_BNH_MEN;
3842 L_BNH_WMEN := L_BNH_WMEN + L_TMR_BNH_WMEN;
3843 L_AMAI_MEN := L_AMAI_MEN + L_TMR_AMAI_MEN;
3844 L_AMAI_WMEN := L_AMAI_WMEN + L_TMR_AMAI_WMEN;
3845 L_AP_MEN := L_AP_MEN + L_TMR_AP_MEN;
3846 L_AP_WMEN := L_AP_WMEN + L_TMR_AP_WMEN;
3847 L_H_MEN := L_H_MEN + L_TMR_H_MEN;
3848 L_H_WMEN := L_H_WMEN + L_TMR_H_WMEN;
3849 L_WNH_MEN := L_WNH_MEN + L_TMR_WNH_MEN;
3850 L_WNH_WMEN := L_WNH_WMEN + L_TMR_WNH_WMEN;
3851 L_UR_MEN := L_UR_MEN + L_TMR_UR_MEN;
3852 L_UR_WMEN := L_UR_WMEN + L_TMR_UR_WMEN;
3853 L_TOT_MEN := L_TOT_MEN + L_TMR_BNH_MEN + L_TMR_AMAI_MEN + L_TMR_AP_MEN + L_TMR_H_MEN + L_TMR_WNH_MEN + L_TMR_UR_MEN;
3854 L_TOT_WMEN := L_TOT_WMEN + L_TMR_BNH_WMEN + L_TMR_AMAI_WMEN + L_TMR_AP_WMEN + L_TMR_H_WMEN + L_TMR_WNH_WMEN + L_TMR_UR_WMEN;
3855 Commented for IPED Enh #7033011 End */
3856 INSERT INTO PAY_US_RPT_TOTALS
3857 (SESSION_ID
3858 ,ATTRIBUTE1
3859 ,VALUE1
3860 ,VALUE2
3861 ,VALUE3
3862 ,VALUE4
3863 ,VALUE5
3864 ,VALUE6
3865 ,VALUE7
3866 ,VALUE8
3867 ,VALUE9
3868 ,VALUE10
3869 ,VALUE11
3870 ,VALUE12
3871 ,VALUE13
3872 ,VALUE14
3873 ,VALUE15
3874 ,VALUE16
3875 ,VALUE17
3876 ,VALUE18
3877 ,VALUE19 -- Added new column for IPED Enh #7033011
3878 ,VALUE20 -- Added new column for IPED Enh #7033011
3879 ,VALUE21 -- Added new column for IPED Enh #7033011
3880 ,VALUE22 -- Added new column for IPED Enh #7033011
3881 )
3882 VALUES (USERENV('sessionid')
3883 ,'IPED8'
3884 ,LINE
3885 ,SC
3886 ,L_NR_MEN
3887 ,L_NR_WMEN
3888 ,L_BNH_MEN
3889 ,L_BNH_WMEN
3890 ,L_AMAI_MEN
3891 ,L_AMAI_WMEN
3892 ,L_A_MEN
3893 ,L_A_WMEN
3894 ,L_H_MEN
3895 ,L_H_WMEN
3896 ,L_WNH_MEN
3897 ,L_WNH_WMEN
3898 ,L_UR_MEN
3899 ,L_UR_WMEN
3900 ,L_TOT_MEN
3901 ,L_TOT_WMEN
3902 ,L_NHOP_MEN -- Added new column for IPED Enh #7033011
3903 ,L_NHOP_WMEN -- Added new column for IPED Enh #7033011
3904 ,L_TMR_MEN -- Added new column for IPED Enh #7033011
3905 ,L_TMR_WMEN -- Added new column for IPED Enh #7033011
3906 );
3907 COMMIT;
3908 FOR i IN GET_LINE3_COUNTS LOOP
3909 LINE := I.TENINFO;
3910 SC := I.ARANK;
3911 L_NR_MEN := I.NRMEN;
3912 L_NR_WMEN := I.NRWMEN;
3913 L_BNH_MEN := I.BNHMEN;
3914 L_BNH_WMEN := I.BNHWMEN;
3915 L_AMAI_MEN := I.AM_ALMEN;
3916 L_AMAI_WMEN := I.AM_ALWMEN;
3917 L_A_MEN := I.AMEN;
3918 L_A_WMEN := I.AWMEN;
3919 L_NHOP_MEN := I.NH_OPMEN; -- Added new column for IPED Enh #7033011
3920 L_NHOP_WMEN := I.NH_OPWMEN; -- Added new column for IPED Enh #7033011
3921 L_H_MEN := I.HMEN;
3922 L_H_WMEN := I.HWMEN;
3923 L_WNH_MEN := I.WNHMEN;
3924 L_WNH_WMEN := I.WNHWMEN;
3925 L_UR_MEN := I.URMEN;
3926 L_UR_WMEN := I.URWMEN;
3927 L_TMR_MEN := I.TMRMEN; -- Added new column for IPED Enh #7033011
3928 L_TMR_WMEN := I.TMRWMEN; -- Added new column for IPED Enh #7033011
3929 /* Added new variables l_nhop_men and l_tmr_men to l_tot_men for IPED Enh #7033011 */
3930 L_TOT_MEN := L_NR_MEN + L_BNH_MEN + L_AMAI_MEN + L_A_MEN + L_NHOP_MEN + L_H_MEN + L_WNH_MEN + L_UR_MEN + L_TMR_MEN;
3931 /* Added new variables l_nhop_wmen and l_tmr_wmen to l_tot_wmen for IPED Enh #7033011 */
3932 L_TOT_WMEN := L_NR_WMEN + L_BNH_WMEN + L_AMAI_WMEN + L_A_WMEN + L_NHOP_WMEN + L_H_WMEN + L_WNH_WMEN + L_UR_WMEN + L_TMR_WMEN;
3933 /* Commented for IPED Enh #7033011 Start
3934 FOR j IN GET_LINE3_TMR_COUNTS(sc) LOOP
3935 L_BNH_MEN := L_BNH_MEN + J.BNHMEN;
3936 L_BNH_WMEN := L_BNH_WMEN + J.BNHWMEN;
3937 L_AMAI_MEN := L_AMAI_MEN + J.AM_ALMEN;
3938 L_AMAI_WMEN := L_AMAI_WMEN + J.AM_ALWMEN;
3939 L_AP_MEN := L_AP_MEN + J.A_PMEN;
3940 L_AP_WMEN := L_AP_WMEN + J.A_PWMEN;
3941 L_H_MEN := L_H_MEN + J.HMEN;
3942 L_H_WMEN := L_H_WMEN + J.HWMEN;
3943 L_WNH_MEN := L_WNH_MEN + J.WNHMEN;
3944 L_WNH_WMEN := L_WNH_WMEN + J.WNHWMEN;
3945 L_UR_MEN := L_UR_MEN + J.URMEN;
3946 L_UR_WMEN := L_UR_WMEN + J.URWMEN;
3947 L_TOT_MEN := L_TOT_MEN + J.BNHMEN + J.AM_ALMEN + J.A_PMEN + J.HMEN + J.WNHMEN + J.URMEN;
3948 L_TOT_WMEN := L_TOT_WMEN + J.BNHWMEN + J.AM_ALWMEN + J.A_PWMEN + J.HWMEN + J.WNHWMEN + J.URWMEN;
3949 END LOOP;
3950 Commented for IPED Enh #7033011 End */
3951 INSERT INTO PAY_US_RPT_TOTALS
3952 (SESSION_ID
3953 ,ATTRIBUTE1
3954 ,VALUE1
3955 ,VALUE2
3956 ,VALUE3
3957 ,VALUE4
3958 ,VALUE5
3959 ,VALUE6
3960 ,VALUE7
3961 ,VALUE8
3962 ,VALUE9
3963 ,VALUE10
3964 ,VALUE11
3965 ,VALUE12
3966 ,VALUE13
3967 ,VALUE14
3968 ,VALUE15
3969 ,VALUE16
3970 ,VALUE17
3971 ,VALUE18
3972 ,VALUE19 -- Added new column for IPED Enh #7033011
3973 ,VALUE20 -- Added new column for IPED Enh #7033011
3974 ,VALUE21 -- Added new column for IPED Enh #7033011
3975 ,VALUE22 -- Added new column for IPED Enh #7033011
3976 )
3977 VALUES (USERENV('sessionid')
3978 ,'IPED8'
3979 ,LINE
3980 ,SC
3981 ,L_NR_MEN
3982 ,L_NR_WMEN
3983 ,L_BNH_MEN
3984 ,L_BNH_WMEN
3985 ,L_AMAI_MEN
3986 ,L_AMAI_WMEN
3987 ,L_A_MEN
3988 ,L_A_WMEN
3989 ,L_H_MEN
3990 ,L_H_WMEN
3991 ,L_WNH_MEN
3992 ,L_WNH_WMEN
3993 ,L_UR_MEN
3994 ,L_UR_WMEN
3995 ,L_TOT_MEN
3996 ,L_TOT_WMEN
3997 ,L_NHOP_MEN -- Added new column for IPED Enh #7033011
3998 ,L_NHOP_WMEN -- Added new column for IPED Enh #7033011
3999 ,L_TMR_MEN -- Added new column for IPED Enh #7033011
4000 ,L_TMR_WMEN -- Added new column for IPED Enh #7033011
4001 );
4002 COMMIT;
4003 END LOOP;
4004 OPEN GET_LINE4_COUNTS;
4005 /* Added new variables l_nhop_men,l_nhop_Wmen,l_tmr_Wmen and l_tmr_men for IPED Enh #7033011 */
4006 FETCH GET_LINE4_COUNTS
4007 INTO LINE,SC,L_NR_MEN,L_NR_WMEN,L_BNH_MEN,L_BNH_WMEN,L_AMAI_MEN,L_AMAI_WMEN,L_A_MEN,L_A_WMEN,L_NHOP_MEN,L_NHOP_WMEN,L_H_MEN,L_H_WMEN,L_WNH_MEN,L_WNH_WMEN,L_UR_MEN,L_UR_WMEN,L_TMR_MEN,L_TMR_WMEN;
4008 CLOSE GET_LINE4_COUNTS;
4009 /* Added new variables l_nhop_men and l_tmr_men to l_tot_men for IPED Enh #7033011 */
4010 L_TOT_MEN := L_NR_MEN + L_BNH_MEN + L_AMAI_MEN + L_A_MEN + L_NHOP_MEN + L_H_MEN + L_WNH_MEN + L_UR_MEN + L_TMR_MEN;
4011 /* Added new variables l_nhop_wmen and l_tmr_wmen to l_tot_wmen for IPED Enh #7033011 */
4012 L_TOT_WMEN := L_NR_WMEN + L_BNH_WMEN + L_AMAI_WMEN + L_A_WMEN + L_NHOP_WMEN + L_H_WMEN + L_WNH_WMEN + L_UR_WMEN + L_TMR_WMEN;
4013 /* Commented for IPED Enh #7033011 Start
4014 OPEN GET_LINE4_TMR_COUNTS;
4015 FETCH GET_LINE4_TMR_COUNTS
4016 INTO L_TMR_BNH_MEN,L_TMR_BNH_WMEN,L_TMR_AMAI_MEN,L_TMR_AMAI_WMEN,L_TMR_AP_MEN,L_TMR_AP_WMEN,L_TMR_H_MEN,L_TMR_H_WMEN,L_TMR_WNH_MEN,L_TMR_WNH_WMEN,L_TMR_UR_MEN,L_TMR_UR_WMEN;
4017 CLOSE GET_LINE4_TMR_COUNTS;
4018 L_BNH_MEN := L_BNH_MEN + L_TMR_BNH_MEN;
4019 L_BNH_WMEN := L_BNH_WMEN + L_TMR_BNH_WMEN;
4020 L_AMAI_MEN := L_AMAI_MEN + L_TMR_AMAI_MEN;
4021 L_AMAI_WMEN := L_AMAI_WMEN + L_TMR_AMAI_WMEN;
4022 L_AP_MEN := L_AP_MEN + L_TMR_AP_MEN;
4023 L_AP_WMEN := L_AP_WMEN + L_TMR_AP_WMEN;
4024 L_H_MEN := L_H_MEN + L_TMR_H_MEN;
4025 L_H_WMEN := L_H_WMEN + L_TMR_H_WMEN;
4026 L_WNH_MEN := L_WNH_MEN + L_TMR_WNH_MEN;
4027 L_WNH_WMEN := L_WNH_WMEN + L_TMR_WNH_WMEN;
4028 L_UR_MEN := L_UR_MEN + L_TMR_UR_MEN;
4029 L_UR_WMEN := L_UR_WMEN + L_TMR_UR_WMEN;
4030 L_TOT_MEN := L_TOT_MEN + L_TMR_BNH_MEN + L_TMR_AMAI_MEN + L_TMR_AP_MEN + L_TMR_H_MEN + L_TMR_WNH_MEN + L_TMR_UR_MEN;
4031 L_TOT_WMEN := L_TOT_WMEN + L_TMR_BNH_WMEN + L_TMR_AMAI_WMEN + L_TMR_AP_WMEN + L_TMR_H_WMEN + L_TMR_WNH_WMEN + L_TMR_UR_WMEN;
4032 Commented for IPED Enh #7033011 End */
4033 INSERT INTO PAY_US_RPT_TOTALS
4034 (SESSION_ID
4035 ,ATTRIBUTE1
4036 ,VALUE1
4037 ,VALUE2
4038 ,VALUE3
4039 ,VALUE4
4040 ,VALUE5
4041 ,VALUE6
4042 ,VALUE7
4043 ,VALUE8
4044 ,VALUE9
4045 ,VALUE10
4046 ,VALUE11
4047 ,VALUE12
4048 ,VALUE13
4049 ,VALUE14
4050 ,VALUE15
4051 ,VALUE16
4052 ,VALUE17
4053 ,VALUE18
4054 ,VALUE19 -- Added new column for IPED Enh #7033011
4055 ,VALUE20 -- Added new column for IPED Enh #7033011
4056 ,VALUE21 -- Added new column for IPED Enh #7033011
4057 ,VALUE22 -- Added new column for IPED Enh #7033011
4058 )
4059 VALUES (USERENV('sessionid')
4060 ,'IPED8'
4061 ,LINE
4062 ,SC
4063 ,L_NR_MEN
4064 ,L_NR_WMEN
4065 ,L_BNH_MEN
4066 ,L_BNH_WMEN
4067 ,L_AMAI_MEN
4068 ,L_AMAI_WMEN
4069 ,L_A_MEN
4070 ,L_A_WMEN
4071 ,L_H_MEN
4072 ,L_H_WMEN
4073 ,L_WNH_MEN
4074 ,L_WNH_WMEN
4075 ,L_UR_MEN
4076 ,L_UR_WMEN
4077 ,L_TOT_MEN
4078 ,L_TOT_WMEN
4079 ,L_NHOP_MEN -- Added new column for IPED Enh #7033011
4080 ,L_NHOP_WMEN -- Added new column for IPED Enh #7033011
4081 ,L_TMR_MEN -- Added new column for IPED Enh #7033011
4082 ,L_TMR_WMEN -- Added new column for IPED Enh #7033011
4083 );
4084 COMMIT;
4085 FOR i IN GET_LINE5_COUNTS LOOP
4086 LINE := I.TENINFO;
4087 SC := I.ARANK;
4088 L_NR_MEN := I.NRMEN;
4089 L_NR_WMEN := I.NRWMEN;
4090 L_BNH_MEN := I.BNHMEN;
4091 L_BNH_WMEN := I.BNHWMEN;
4092 L_AMAI_MEN := I.AM_ALMEN;
4093 L_AMAI_WMEN := I.AM_ALWMEN;
4094 L_A_MEN := I.AMEN;
4095 L_A_WMEN := I.AWMEN;
4096 L_NHOP_MEN := I.NH_OPMEN; -- Added new column for IPED Enh #7033011
4097 L_NHOP_WMEN := I.NH_OPWMEN; -- Added new column for IPED Enh #7033011
4098 L_H_MEN := I.HMEN;
4099 L_H_WMEN := I.HWMEN;
4100 L_WNH_MEN := I.WNHMEN;
4101 L_WNH_WMEN := I.WNHWMEN;
4102 L_UR_MEN := I.URMEN;
4103 L_UR_WMEN := I.URWMEN;
4104 L_TMR_MEN := I.TMRMEN; -- Added new column for IPED Enh #7033011
4105 L_TMR_WMEN := I.TMRWMEN; -- Added new column for IPED Enh #7033011
4106 /* Added new variables l_nhop_men and l_tmr_men to l_tot_men for IPED Enh #7033011 */
4107 L_TOT_MEN := L_NR_MEN + L_BNH_MEN + L_AMAI_MEN + L_A_MEN + L_NHOP_MEN + L_H_MEN + L_WNH_MEN + L_UR_MEN + L_TMR_MEN;
4108 /* Added new variables l_nhop_wmen and l_tmr_wmen to l_tot_wmen for IPED Enh #7033011 */
4109 L_TOT_WMEN := L_NR_WMEN + L_BNH_WMEN + L_AMAI_WMEN + L_A_WMEN + L_NHOP_WMEN + L_H_WMEN + L_WNH_WMEN + L_UR_WMEN + L_TMR_WMEN;
4110 /* Commented for IPED Enh #7033011 Start
4111 FOR j IN GET_LINE5_TMR_COUNTS(sc) LOOP
4112 L_BNH_MEN := L_BNH_MEN + J.BNHMEN;
4113 L_BNH_WMEN := L_BNH_WMEN + J.BNHWMEN;
4114 L_AMAI_MEN := L_AMAI_MEN + J.AM_ALMEN;
4115 L_AMAI_WMEN := L_AMAI_WMEN + J.AM_ALWMEN;
4116 L_AP_MEN := L_AP_MEN + J.A_PMEN;
4117 L_AP_WMEN := L_AP_WMEN + J.A_PWMEN;
4118 L_H_MEN := L_H_MEN + J.HMEN;
4119 L_H_WMEN := L_H_WMEN + J.HWMEN;
4120 L_WNH_MEN := L_WNH_MEN + J.WNHMEN;
4121 L_WNH_WMEN := L_WNH_WMEN + J.WNHWMEN;
4122 L_UR_MEN := L_UR_MEN + J.URMEN;
4123 L_UR_WMEN := L_UR_WMEN + J.URWMEN;
4124 L_TOT_MEN := L_TOT_MEN + J.BNHMEN + J.AM_ALMEN + J.A_PMEN + J.HMEN + J.WNHMEN + J.URMEN;
4125 L_TOT_WMEN := L_TOT_WMEN + J.BNHWMEN + J.AM_ALWMEN + J.A_PWMEN + J.HWMEN + J.WNHWMEN + J.URWMEN;
4126 END LOOP;
4127 Commented for IPED Enh #7033011 End */
4128 INSERT INTO PAY_US_RPT_TOTALS
4129 (SESSION_ID
4130 ,ATTRIBUTE1
4131 ,VALUE1
4132 ,VALUE2
4133 ,VALUE3
4134 ,VALUE4
4135 ,VALUE5
4136 ,VALUE6
4137 ,VALUE7
4138 ,VALUE8
4139 ,VALUE9
4140 ,VALUE10
4141 ,VALUE11
4142 ,VALUE12
4143 ,VALUE13
4144 ,VALUE14
4145 ,VALUE15
4146 ,VALUE16
4147 ,VALUE17
4148 ,VALUE18
4149 ,VALUE19 -- Added new column for IPED Enh #7033011
4150 ,VALUE20 -- Added new column for IPED Enh #7033011
4151 ,VALUE21 -- Added new column for IPED Enh #7033011
4152 ,VALUE22 -- Added new column for IPED Enh #7033011
4153 )
4154 VALUES (USERENV('sessionid')
4155 ,'IPED8'
4156 ,LINE
4157 ,SC
4158 ,L_NR_MEN
4159 ,L_NR_WMEN
4160 ,L_BNH_MEN
4161 ,L_BNH_WMEN
4162 ,L_AMAI_MEN
4163 ,L_AMAI_WMEN
4164 ,L_A_MEN
4165 ,L_A_WMEN
4166 ,L_H_MEN
4167 ,L_H_WMEN
4168 ,L_WNH_MEN
4169 ,L_WNH_WMEN
4170 ,L_UR_MEN
4171 ,L_UR_WMEN
4172 ,L_TOT_MEN
4173 ,L_TOT_WMEN
4174 ,L_NHOP_MEN -- Added new column for IPED Enh #7033011
4175 ,L_NHOP_WMEN -- Added new column for IPED Enh #7033011
4176 ,L_TMR_MEN -- Added new column for IPED Enh #7033011
4177 ,L_TMR_WMEN -- Added new column for IPED Enh #7033011
4178 );
4179 COMMIT;
4180 END LOOP;
4181 OPEN GET_LINE6_COUNTS;
4182 /* Added new variables l_nhop_men,l_nhop_Wmen,l_tmr_Wmen and l_tmr_men for IPED Enh #7033011 */
4183 FETCH GET_LINE6_COUNTS
4184 INTO LINE,SC,L_NR_MEN,L_NR_WMEN,L_BNH_MEN,L_BNH_WMEN,L_AMAI_MEN,L_AMAI_WMEN,L_A_MEN,L_A_WMEN,L_NHOP_MEN,L_NHOP_WMEN,L_H_MEN,L_H_WMEN,L_WNH_MEN,L_WNH_WMEN,L_UR_MEN,L_UR_WMEN,L_TMR_MEN,L_TMR_WMEN;
4185 CLOSE GET_LINE6_COUNTS;
4186 /* Added new variables l_nhop_men and l_tmr_men to l_tot_men for IPED Enh #7033011 */
4187 L_TOT_MEN := L_NR_MEN + L_BNH_MEN + L_AMAI_MEN + L_A_MEN + L_NHOP_MEN + L_H_MEN + L_WNH_MEN + L_UR_MEN + L_TMR_MEN;
4188 /* Added new variables l_nhop_wmen and l_tmr_wmen to l_tot_wmen for IPED Enh #7033011 */
4189 L_TOT_WMEN := L_NR_WMEN + L_BNH_WMEN + L_AMAI_WMEN + L_A_WMEN + L_NHOP_WMEN + L_H_WMEN + L_WNH_WMEN + L_UR_WMEN + L_TMR_WMEN;
4190 /* Commented for IPED Enh #7033011 Start
4191 OPEN GET_LINE6_TMR_COUNTS;
4192 FETCH GET_LINE6_TMR_COUNTS
4193 INTO L_TMR_BNH_MEN,L_TMR_BNH_WMEN,L_TMR_AMAI_MEN,L_TMR_AMAI_WMEN,L_TMR_AP_MEN,L_TMR_AP_WMEN,L_TMR_H_MEN,L_TMR_H_WMEN,L_TMR_WNH_MEN,L_TMR_WNH_WMEN,L_TMR_UR_MEN,L_TMR_UR_WMEN;
4194 CLOSE GET_LINE6_TMR_COUNTS;
4195 L_BNH_MEN := L_BNH_MEN + L_TMR_BNH_MEN;
4196 L_BNH_WMEN := L_BNH_WMEN + L_TMR_BNH_WMEN;
4197 L_AMAI_MEN := L_AMAI_MEN + L_TMR_AMAI_MEN;
4198 L_AMAI_WMEN := L_AMAI_WMEN + L_TMR_AMAI_WMEN;
4199 L_AP_MEN := L_AP_MEN + L_TMR_AP_MEN;
4200 L_AP_WMEN := L_AP_WMEN + L_TMR_AP_WMEN;
4201 L_H_MEN := L_H_MEN + L_TMR_H_MEN;
4202 L_H_WMEN := L_H_WMEN + L_TMR_H_WMEN;
4203 L_WNH_MEN := L_WNH_MEN + L_TMR_WNH_MEN;
4204 L_WNH_WMEN := L_WNH_WMEN + L_TMR_WNH_WMEN;
4205 L_UR_MEN := L_UR_MEN + L_TMR_UR_MEN;
4206 L_UR_WMEN := L_UR_WMEN + L_TMR_UR_WMEN;
4207 L_TOT_MEN := L_TOT_MEN + L_TMR_BNH_MEN + L_TMR_AMAI_MEN + L_TMR_AP_MEN + L_TMR_H_MEN + L_TMR_WNH_MEN + L_TMR_UR_MEN;
4208 L_TOT_WMEN := L_TOT_WMEN + L_TMR_BNH_WMEN + L_TMR_AMAI_WMEN + L_TMR_AP_WMEN + L_TMR_H_WMEN + L_TMR_WNH_WMEN + L_TMR_UR_WMEN;
4209 Commented for IPED Enh #7033011 End */
4210 INSERT INTO PAY_US_RPT_TOTALS
4211 (SESSION_ID
4212 ,ATTRIBUTE1
4213 ,VALUE1
4214 ,VALUE2
4215 ,VALUE3
4216 ,VALUE4
4217 ,VALUE5
4218 ,VALUE6
4219 ,VALUE7
4220 ,VALUE8
4221 ,VALUE9
4222 ,VALUE10
4223 ,VALUE11
4224 ,VALUE12
4225 ,VALUE13
4226 ,VALUE14
4227 ,VALUE15
4228 ,VALUE16
4229 ,VALUE17
4230 ,VALUE18
4231 ,VALUE19 -- Added new column for IPED Enh #7033011
4232 ,VALUE20 -- Added new column for IPED Enh #7033011
4233 ,VALUE21 -- Added new column for IPED Enh #7033011
4234 ,VALUE22 -- Added new column for IPED Enh #7033011
4235 )
4236 VALUES (USERENV('sessionid')
4237 ,'IPED8'
4238 ,LINE
4239 ,SC
4240 ,L_NR_MEN
4241 ,L_NR_WMEN
4242 ,L_BNH_MEN
4243 ,L_BNH_WMEN
4244 ,L_AMAI_MEN
4245 ,L_AMAI_WMEN
4246 ,L_A_MEN
4247 ,L_A_WMEN
4248 ,L_H_MEN
4249 ,L_H_WMEN
4250 ,L_WNH_MEN
4251 ,L_WNH_WMEN
4252 ,L_UR_MEN
4253 ,L_UR_WMEN
4254 ,L_TOT_MEN
4255 ,L_TOT_WMEN
4256 ,L_NHOP_MEN -- Added new column for IPED Enh #7033011
4257 ,L_NHOP_WMEN -- Added new column for IPED Enh #7033011
4258 ,L_TMR_MEN -- Added new column for IPED Enh #7033011
4259 ,L_TMR_WMEN -- Added new column for IPED Enh #7033011
4260 );
4261 COMMIT;
4262
4263 return true;
4264 end;
4265
4266 function CF_1Formula return Number is
4267 temp_num number;
4268 begin
4269 temp_num := line_num;
4270 line_num := line_num + 1;
4271 if line_num = 84 then
4272 line_num := 85;
4273 elsif line_num = 91 then
4274 line_num := 92;
4275 end if;
4276 return temp_num;
4277 end;
4278
4279 function cf_grouplinenumformula(TenStat in varchar2) return number is
4280 temp_num number;
4281 p_contr_code varchar2(10) := TenStat;
4282 begin
4283 if p_contr_code = '01' then
4284 temp_num := 84;
4285 elsif p_contr_code = '02' then
4286 temp_num := 91;
4287 elsif p_contr_code = '03' then
4288 temp_num := 98;
4289 end if;
4290 return temp_num;
4291 end;
4292
4293 function CF_GroupTotTitleFormula(TenStat in varchar2) return Char is
4294 l_total_title VARCHAR2(200) := '';
4295 l_contr_code VARCHAR2(9) := TenStat;
4296 begin
4297 IF l_contr_code = '01' THEN
4298 l_total_title := 'Total Faculty with Tenure (sum of lines 78-83)';
4299 ELSIF l_contr_code = '02' THEN
4300 l_total_title := 'Total Non-Tenured Faculty (Those on tenure track) sum of lines 85-90';
4301 ELSIF l_contr_code = '03' THEN
4302 l_total_title := 'Total Non-Tenured Faculty (Those not on tenure track) sum of lines 92-97';
4303 END IF;
4304 return l_total_title;
4305 end;
4306
4307 function AfterReport return boolean is
4308 begin
4309 --hr_standard.event('AFTER REPORT');
4310 EXECUTE IMMEDIATE
4311 'DELETE FROM pay_us_rpt_totals
4312 WHERE attribute1 = ''IPED8''';
4313
4314 return (TRUE);
4315 end;
4316
4317 --Functions to refer Oracle report placeholders--
4318
4319 Function CP_FR_p return varchar2 is
4320 Begin
4321 return CP_FR;
4322 END;
4323 Function CP_FT_p return varchar2 is
4324 Begin
4325 return CP_FT;
4326 END;
4327 Function CP_PR_p return varchar2 is
4328 Begin
4329 return CP_PR;
4330 END;
4331 Function CP_PT_p return varchar2 is
4332 Begin
4333 return CP_PT;
4334 END;
4335 Function line_num_p return number is
4336 Begin
4337 return line_num;
4338 END;
4339 Function CP_LineNumRepTot_p return number is
4340 Begin
4341 return CP_LineNumRepTot;
4342 END;
4343 Function CP_RepTotTitle_p return varchar2 is
4344 Begin
4345 return CP_RepTotTitle;
4346 END;
4347 END PQH_PQIPED8_XMLP_PKG ;