[Home] [Help]
PACKAGE BODY: APPS.PQH_PQIPED8_XMLP_PKG
Source
1 PACKAGE BODY PQH_PQIPED8_XMLP_PKG AS
2 /* $Header: PQIPED8B.pls 120.3 2008/04/16 11:16:58 amakrish noship $ */
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_AP_MEN NUMBER(10) := 0;
19 L_AP_WMEN NUMBER(10) := 0;
20 L_H_MEN NUMBER(10) := 0;
21 L_H_WMEN NUMBER(10) := 0;
22 L_WNH_MEN NUMBER(10) := 0;
23 L_WNH_WMEN NUMBER(10) := 0;
24 L_UR_MEN NUMBER(10) := 0;
25 L_UR_WMEN NUMBER(10) := 0;
26 L_TOT_MEN NUMBER(10) := 0;
27 L_TOT_WMEN NUMBER(10) := 0;
28 L_TMR_BNH_MEN NUMBER(10) := 0;
29 L_TMR_BNH_WMEN NUMBER(10) := 0;
30 L_TMR_AMAI_MEN NUMBER(10) := 0;
31 L_TMR_AMAI_WMEN NUMBER(10) := 0;
32 L_TMR_AP_MEN NUMBER(10) := 0;
33 L_TMR_AP_WMEN NUMBER(10) := 0;
34 L_TMR_H_MEN NUMBER(10) := 0;
35 L_TMR_H_WMEN NUMBER(10) := 0;
36 L_TMR_WNH_MEN NUMBER(10) := 0;
37 L_TMR_WNH_WMEN NUMBER(10) := 0;
38 L_TMR_UR_MEN NUMBER(10) := 0;
39 L_TMR_UR_WMEN NUMBER(10) := 0;
40 CURSOR GET_LINE1_COUNTS IS
41 SELECT
42 '01' TENINFO,
43 HLA.LOOKUP_CODE ARANK,
44 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
45 ,P_REPORT_DATE)
46 ,'1'
47 ,DECODE(PEO.SEX
48 ,'M'
49 ,1
50 ,NULL)
51 ,NULL)) NRMEN,
52 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
53 ,P_REPORT_DATE)
54 ,'1'
55 ,DECODE(PEO.SEX
56 ,'F'
57 ,1
58 ,NULL)
59 ,NULL)) NRWMEN,
60 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
61 ,P_REPORT_DATE)
62 ,NULL
63 ,(DECODE(PEO.PER_INFORMATION1
64 ,'2'
65 ,DECODE(PEO.SEX
66 ,'M'
67 ,1
68 ,NULL)
69 ,NULL)))) BNHMEN,
70 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
71 ,P_REPORT_DATE)
72 ,NULL
73 ,(DECODE(PEO.PER_INFORMATION1
74 ,'2'
75 ,DECODE(PEO.SEX
76 ,'F'
77 ,1
78 ,NULL)
79 ,NULL)))) BNHWMEN,
80 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
81 ,P_REPORT_DATE)
82 ,NULL
83 ,(DECODE(PEO.PER_INFORMATION1
84 ,'6'
85 ,DECODE(PEO.SEX
86 ,'M'
87 ,1
88 ,NULL)
89 ,NULL)))) AM_ALMEN,
90 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
91 ,P_REPORT_DATE)
92 ,NULL
93 ,(DECODE(PEO.PER_INFORMATION1
94 ,'6'
95 ,DECODE(PEO.SEX
96 ,'F'
97 ,1
98 ,NULL)
99 ,NULL)))) AM_ALWMEN,
100 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
101 ,P_REPORT_DATE)
102 ,NULL
103 ,(DECODE(PEO.PER_INFORMATION1
104 ,'4'
105 ,DECODE(PEO.SEX
106 ,'M'
107 ,1
108 ,NULL)
109 ,'5'
110 ,DECODE(PEO.SEX
111 ,'M'
112 ,1
113 ,NULL)
114 ,NULL)))) APMEN,
115 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
116 ,P_REPORT_DATE)
117 ,NULL
118 ,(DECODE(PEO.PER_INFORMATION1
119 ,'4'
120 ,DECODE(PEO.SEX
121 ,'F'
122 ,1
123 ,NULL)
124 ,'5'
125 ,DECODE(PEO.SEX
126 ,'F'
127 ,1
128 ,NULL)
129 ,NULL)))) APWMEN,
130 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
131 ,P_REPORT_DATE)
132 ,NULL
133 ,(DECODE(PEO.PER_INFORMATION1
134 ,'3'
135 ,DECODE(PEO.SEX
136 ,'M'
137 ,1
138 ,NULL)
139 ,'9'
140 ,DECODE(PEO.SEX
141 ,'M'
142 ,1
143 ,NULL)
144 ,NULL)))) HMEN,
145 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
146 ,P_REPORT_DATE)
147 ,NULL
148 ,(DECODE(PEO.PER_INFORMATION1
149 ,'3'
150 ,DECODE(PEO.SEX
151 ,'F'
152 ,1
153 ,NULL)
154 ,'9'
155 ,DECODE(PEO.SEX
156 ,'F'
157 ,1
158 ,NULL)
159 ,NULL)))) HWMEN,
160 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
161 ,P_REPORT_DATE)
162 ,NULL
163 ,(DECODE(PEO.PER_INFORMATION1
164 ,'1'
165 ,DECODE(PEO.SEX
166 ,'M'
167 ,1
168 ,NULL)
169 ,NULL)))) WNHMEN,
170 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
171 ,P_REPORT_DATE)
172 ,NULL
173 ,(DECODE(PEO.PER_INFORMATION1
174 ,'1'
175 ,DECODE(PEO.SEX
176 ,'F'
177 ,1
178 ,NULL)
179 ,NULL)))) WNHWMEN,
180 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
181 ,P_REPORT_DATE)
182 ,NULL
183 ,(DECODE(PEO.PER_INFORMATION1
184 ,NULL
185 ,DECODE(PEO.SEX
186 ,'M'
187 ,1
188 ,NULL)
189 ,NULL)))) URMEN,
190 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
191 ,P_REPORT_DATE)
192 ,NULL
193 ,(DECODE(PEO.PER_INFORMATION1
194 ,NULL
195 ,DECODE(PEO.SEX
196 ,'F'
197 ,1
198 ,NULL)
199 ,NULL)))) URWMEN
200 FROM
201 HR_LOOKUPS HLA,
202 PER_ALL_PEOPLE_F PEO,
203 PER_ALL_ASSIGNMENTS_F PAF,
204 PER_ASSIGNMENT_STATUS_TYPES AST,
205 PER_PEOPLE_EXTRA_INFO PPEA,
206 PER_PEOPLE_EXTRA_INFO PPET,
207 HR_LOOKUPS HL,
208 PER_PAY_PROPOSALS PPP,
209 PER_PAY_BASES PPB,
210 PER_JOBS JOB
211 WHERE PAF.PERSON_ID = PPEA.PERSON_ID
212 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
213 AND PAF.PERSON_ID = PPET.PERSON_ID
214 AND PAF.PERSON_ID = PEO.PERSON_ID
215 AND PPEA.INFORMATION_TYPE = 'PQH_ACADEMIC_RANK'
216 AND PPET.INFORMATION_TYPE = 'PQH_TENURE_STATUS'
217 AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
218 AND PAF.EFFECTIVE_END_DATE
219 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
220 AND PEO.EFFECTIVE_END_DATE
221 AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
222 AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
223 AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
224 ,'4712/12/31 00:00:00'))
225 AND PPET.PEI_INFORMATION1 in ( '01' )
226 AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
227 AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE
228 AND PPEA.PEI_INFORMATION1 IS NOT NULL
229 AND PPEA.PEI_INFORMATION1 not in ( '01' )
230 AND PPET.PEI_INFORMATION1 IS NOT NULL
231 AND PAF.PRIMARY_FLAG = 'Y'
232 AND PAF.ASSIGNMENT_TYPE = 'E'
233 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
234 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
235 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
236 ,CP_FR
237 ,CP_FT
238 ,CP_PR
239 ,CP_PT) IN ( 'FR' )
240 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
241 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
242 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
243 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
244 AND PAF.JOB_ID = JOB.JOB_ID
245 AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
246 AND PAF.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
247 AND PPP.CHANGE_DATE = (
248 SELECT
249 MAX(CHANGE_DATE)
250 FROM
251 PER_PAY_PROPOSALS PRO
252 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
253 AND PRO.CHANGE_DATE <= P_REPORT_DATE
254 AND PRO.APPROVED = 'Y' )
255 AND NVL(PPP.PROPOSED_SALARY_N
256 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
257 AND PAF.ORGANIZATION_ID IN (
258 SELECT
259 ORGANIZATION_ID
260 FROM
261 HR_ALL_ORGANIZATION_UNITS
262 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
263 GROUP BY
264 '01',
265 HLA.LOOKUP_CODE;
266 CURSOR GET_LINE1_TMRACES_COUNTS(C_LOOKUP_CODE IN VARCHAR2) IS
267 SELECT
268 COUNT(DECODE(PEI.PEI_INFORMATION5
269 ,'2'
270 ,DECODE(PEO.SEX
271 ,'M'
272 ,1
273 ,NULL)
274 ,NULL)) BNHMEN,
275 COUNT(DECODE(PEI.PEI_INFORMATION5
276 ,'2'
277 ,DECODE(PEO.SEX
278 ,'F'
279 ,1
280 ,NULL)
281 ,NULL)) BNHWMEN,
282 COUNT(DECODE(PEI.PEI_INFORMATION5
283 ,'6'
284 ,DECODE(PEO.SEX
285 ,'M'
286 ,1
287 ,NULL)
288 ,NULL)) AM_ALMEN,
289 COUNT(DECODE(PEI.PEI_INFORMATION5
290 ,'6'
291 ,DECODE(PEO.SEX
292 ,'F'
293 ,1
294 ,NULL)
295 ,NULL)) AM_ALWMEN,
296 COUNT(DECODE(PEI.PEI_INFORMATION5
297 ,'4'
298 ,DECODE(PEO.SEX
299 ,'M'
300 ,1
301 ,NULL)
302 ,'5'
303 ,DECODE(PEO.SEX
304 ,'M'
305 ,1
306 ,NULL)
307 ,NULL)) A_PMEN,
308 COUNT(DECODE(PEI.PEI_INFORMATION5
309 ,'4'
310 ,DECODE(PEO.SEX
311 ,'F'
312 ,1
313 ,NULL)
314 ,'5'
315 ,DECODE(PEO.SEX
316 ,'F'
317 ,1
318 ,NULL)
319 ,NULL)) A_PWMEN,
320 COUNT(DECODE(PEI.PEI_INFORMATION5
321 ,'3'
322 ,DECODE(PEO.SEX
323 ,'M'
324 ,1
325 ,NULL)
326 ,'9'
327 ,DECODE(PEO.SEX
328 ,'M'
329 ,1
330 ,NULL)
331 ,NULL)) HMEN,
332 COUNT(DECODE(PEI.PEI_INFORMATION5
333 ,'3'
334 ,DECODE(PEO.SEX
335 ,'F'
336 ,1
337 ,NULL)
338 ,'9'
339 ,DECODE(PEO.SEX
340 ,'F'
341 ,1
342 ,NULL)
343 ,NULL)) HWMEN,
344 COUNT(DECODE(PEI.PEI_INFORMATION5
345 ,'1'
346 ,DECODE(PEO.SEX
347 ,'M'
348 ,1
349 ,NULL)
350 ,NULL)) WNHMEN,
351 COUNT(DECODE(PEI.PEI_INFORMATION5
352 ,'1'
353 ,DECODE(PEO.SEX
354 ,'F'
355 ,1
356 ,NULL)
357 ,NULL)) WNHWMEN,
358 COUNT(DECODE(PEI.PEI_INFORMATION5
359 ,NULL
360 ,DECODE(PEO.SEX
361 ,'M'
362 ,1
363 ,NULL)
364 ,NULL)) URMEN,
365 COUNT(DECODE(PEI.PEI_INFORMATION5
366 ,NULL
367 ,DECODE(PEO.SEX
368 ,'F'
369 ,1
370 ,NULL)
371 ,NULL)) URWMEN
372 FROM
373 HR_LOOKUPS HLA,
374 PER_ALL_PEOPLE_F PEO,
375 PER_ALL_ASSIGNMENTS_F PAF,
376 PER_ASSIGNMENT_STATUS_TYPES AST,
377 PER_PEOPLE_EXTRA_INFO PPEA,
378 PER_PEOPLE_EXTRA_INFO PPET,
379 HR_LOOKUPS HL,
380 PER_PAY_PROPOSALS PPP,
381 PER_PAY_BASES PPB,
382 PER_JOBS JOB,
383 PER_PEOPLE_EXTRA_INFO PEI
384 WHERE PAF.PERSON_ID = PPEA.PERSON_ID
385 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
386 AND PEO.PER_INFORMATION1 = '13'
387 AND PEO.PERSON_ID = pei.person_id (+)
388 AND ( PEI.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
389 OR ( PEI.INFORMATION_TYPE <> 'PER_US_ADDL_ETHNIC_CAT'
390 AND not exists (
391 SELECT
392 1
393 FROM
394 PER_PEOPLE_EXTRA_INFO PEI2
395 WHERE PEI2.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
396 AND PEI2.PERSON_ID = PEI.PERSON_ID )
397 AND PEI.PERSON_EXTRA_INFO_ID = (
398 SELECT
399 MAX(PEI1.PERSON_EXTRA_INFO_ID)
400 FROM
401 PER_PEOPLE_EXTRA_INFO PEI1
402 WHERE PEI1.PERSON_ID = PEI.PERSON_ID ) )
403 OR ( not exists (
404 SELECT
405 PERSON_EXTRA_INFO_ID
406 FROM
407 PER_PEOPLE_EXTRA_INFO PEI3
408 WHERE PEI3.PERSON_ID = PEI.PERSON_ID ) ) )
409 AND PAF.PERSON_ID = PPET.PERSON_ID
410 AND PAF.PERSON_ID = PEO.PERSON_ID
411 AND PPEA.INFORMATION_TYPE = 'PQH_ACADEMIC_RANK'
412 AND PPET.INFORMATION_TYPE = 'PQH_TENURE_STATUS'
413 AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
414 AND PAF.EFFECTIVE_END_DATE
415 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
416 AND PEO.EFFECTIVE_END_DATE
417 AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
418 AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
419 AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
420 ,'4712/12/31 00:00:00'))
421 AND PPET.PEI_INFORMATION1 in ( '01' )
422 AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
423 AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE
424 AND PPEA.PEI_INFORMATION1 IS NOT NULL
425 AND PPEA.PEI_INFORMATION1 not in ( '01' )
426 AND PPET.PEI_INFORMATION1 IS NOT NULL
427 AND PAF.PRIMARY_FLAG = 'Y'
428 AND PAF.ASSIGNMENT_TYPE = 'E'
429 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
430 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
431 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
432 ,CP_FR
433 ,CP_FT
434 ,CP_PR
435 ,CP_PT) IN ( 'FR' )
436 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
437 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
438 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
439 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
440 AND PAF.JOB_ID = JOB.JOB_ID
441 AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
442 AND PAF.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
443 AND PPP.CHANGE_DATE = (
444 SELECT
445 MAX(CHANGE_DATE)
446 FROM
447 PER_PAY_PROPOSALS PRO
448 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
449 AND PRO.CHANGE_DATE <= P_REPORT_DATE
450 AND PRO.APPROVED = 'Y' )
451 AND NVL(PPP.PROPOSED_SALARY_N
452 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
453 AND PAF.ORGANIZATION_ID IN (
454 SELECT
455 ORGANIZATION_ID
456 FROM
457 HR_ALL_ORGANIZATION_UNITS
458 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
459 AND HLA.LOOKUP_CODE = C_LOOKUP_CODE;
460 CURSOR GET_LINE2_COUNTS IS
461 SELECT
462 '01' TENINFO,
463 '01' ARANK,
464 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
465 ,P_REPORT_DATE)
466 ,'1'
467 ,DECODE(PEO.SEX
468 ,'M'
469 ,1
470 ,NULL)
471 ,NULL)) NRMEN,
472 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
473 ,P_REPORT_DATE)
474 ,'1'
475 ,DECODE(PEO.SEX
476 ,'F'
477 ,1
478 ,NULL)
479 ,NULL)) NRWMEN,
480 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
481 ,P_REPORT_DATE)
482 ,NULL
483 ,(DECODE(PEO.PER_INFORMATION1
484 ,'2'
485 ,DECODE(PEO.SEX
486 ,'M'
487 ,1
488 ,NULL)
489 ,NULL)))) BNHMEN,
490 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
491 ,P_REPORT_DATE)
492 ,NULL
493 ,(DECODE(PEO.PER_INFORMATION1
494 ,'2'
495 ,DECODE(PEO.SEX
496 ,'F'
497 ,1
498 ,NULL)
499 ,NULL)))) BNHWMEN,
500 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
501 ,P_REPORT_DATE)
502 ,NULL
503 ,(DECODE(PEO.PER_INFORMATION1
504 ,'6'
505 ,DECODE(PEO.SEX
506 ,'M'
507 ,1
508 ,NULL)
509 ,NULL)))) AM_ALMEN,
510 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
511 ,P_REPORT_DATE)
512 ,NULL
513 ,(DECODE(PEO.PER_INFORMATION1
514 ,'6'
515 ,DECODE(PEO.SEX
516 ,'F'
517 ,1
518 ,NULL)
519 ,NULL)))) AM_ALWMEN,
520 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
521 ,P_REPORT_DATE)
522 ,NULL
523 ,(DECODE(PEO.PER_INFORMATION1
524 ,'4'
525 ,DECODE(PEO.SEX
526 ,'M'
527 ,1
528 ,NULL)
529 ,'5'
530 ,DECODE(PEO.SEX
531 ,'M'
532 ,1
533 ,NULL)
534 ,NULL)))) APMEN,
535 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
536 ,P_REPORT_DATE)
537 ,NULL
538 ,(DECODE(PEO.PER_INFORMATION1
539 ,'4'
540 ,DECODE(PEO.SEX
541 ,'F'
542 ,1
543 ,NULL)
544 ,'5'
545 ,DECODE(PEO.SEX
546 ,'F'
547 ,1
548 ,NULL)
549 ,NULL)))) APWMEN,
550 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
551 ,P_REPORT_DATE)
552 ,NULL
553 ,(DECODE(PEO.PER_INFORMATION1
554 ,'3'
555 ,DECODE(PEO.SEX
556 ,'M'
557 ,1
558 ,NULL)
559 ,'9'
560 ,DECODE(PEO.SEX
561 ,'M'
562 ,1
563 ,NULL)
564 ,NULL)))) HMEN,
565 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
566 ,P_REPORT_DATE)
567 ,NULL
568 ,(DECODE(PEO.PER_INFORMATION1
569 ,'3'
570 ,DECODE(PEO.SEX
571 ,'F'
572 ,1
573 ,NULL)
574 ,'9'
575 ,DECODE(PEO.SEX
576 ,'F'
577 ,1
578 ,NULL)
579 ,NULL)))) HWMEN,
580 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
581 ,P_REPORT_DATE)
582 ,NULL
583 ,(DECODE(PEO.PER_INFORMATION1
584 ,'1'
585 ,DECODE(PEO.SEX
586 ,'M'
587 ,1
588 ,NULL)
589 ,NULL)))) WNHMEN,
590 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
591 ,P_REPORT_DATE)
592 ,NULL
593 ,(DECODE(PEO.PER_INFORMATION1
594 ,'1'
595 ,DECODE(PEO.SEX
596 ,'F'
597 ,1
598 ,NULL)
599 ,NULL)))) WNHWMEN,
600 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
601 ,P_REPORT_DATE)
602 ,NULL
603 ,(DECODE(PEO.PER_INFORMATION1
604 ,NULL
605 ,DECODE(PEO.SEX
606 ,'M'
607 ,1
608 ,NULL)
609 ,NULL)))) URMEN,
610 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
611 ,P_REPORT_DATE)
612 ,NULL
613 ,(DECODE(PEO.PER_INFORMATION1
614 ,NULL
615 ,DECODE(PEO.SEX
616 ,'F'
617 ,1
618 ,NULL)
619 ,NULL)))) URWMEN
620 FROM
621 PER_ALL_PEOPLE_F PEO,
622 PER_ALL_ASSIGNMENTS_F PAF,
623 PER_ASSIGNMENT_STATUS_TYPES AST,
624 PER_PEOPLE_EXTRA_INFO PPET,
625 HR_LOOKUPS HL,
626 PER_PAY_PROPOSALS PPP,
627 PER_PAY_BASES PPB,
628 PER_JOBS JOB
629 WHERE PAF.PERSON_ID = PPET.PERSON_ID
630 AND PAF.PERSON_ID = PEO.PERSON_ID
631 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
632 AND PPET.INFORMATION_TYPE = 'PQH_TENURE_STATUS'
633 AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
634 AND PAF.EFFECTIVE_END_DATE
635 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
636 AND PEO.EFFECTIVE_END_DATE
637 AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
638 AND PPET.PEI_INFORMATION1 in ( '01' )
639 AND PPET.PEI_INFORMATION1 IS NOT NULL
640 AND not exists (
641 SELECT
642 PEA.PERSON_ID
643 FROM
644 PER_PEOPLE_EXTRA_INFO PEA
645 WHERE PEA.PERSON_ID = PEO.PERSON_ID
646 AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
647 AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
648 AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
649 AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
650 ,'4712/12/31 00:00:00')) )
651 AND PAF.PRIMARY_FLAG = 'Y'
652 AND PAF.ASSIGNMENT_TYPE = 'E'
653 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
654 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
655 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
656 ,CP_FR
657 ,CP_FT
658 ,CP_PR
659 ,CP_PT) IN ( 'FR' )
660 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
661 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
662 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
663 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
664 AND PAF.JOB_ID = JOB.JOB_ID
665 AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
666 AND PAF.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
667 AND PPP.CHANGE_DATE = (
668 SELECT
669 MAX(CHANGE_DATE)
670 FROM
671 PER_PAY_PROPOSALS PRO
672 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
673 AND PRO.CHANGE_DATE <= P_REPORT_DATE
674 AND PRO.APPROVED = 'Y' )
675 AND NVL(PPP.PROPOSED_SALARY_N
676 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
677 AND PAF.ORGANIZATION_ID IN (
678 SELECT
679 ORGANIZATION_ID
680 FROM
681 HR_ALL_ORGANIZATION_UNITS
682 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
683 GROUP BY
684 '01',
685 '01';
686 CURSOR GET_TMR_LINE2_COUNTS IS
687 SELECT
688 COUNT(DECODE(PEI.PEI_INFORMATION5
689 ,'2'
690 ,DECODE(PEO.SEX
691 ,'M'
692 ,1
693 ,NULL)
694 ,NULL)) BNHMEN,
695 COUNT(DECODE(PEI.PEI_INFORMATION5
696 ,'2'
697 ,DECODE(PEO.SEX
698 ,'F'
699 ,1
700 ,NULL)
701 ,NULL)) BNHWMEN,
702 COUNT(DECODE(PEI.PEI_INFORMATION5
703 ,'6'
704 ,DECODE(PEO.SEX
705 ,'M'
706 ,1
707 ,NULL)
708 ,NULL)) AM_ALMEN,
709 COUNT(DECODE(PEI.PEI_INFORMATION5
710 ,'6'
711 ,DECODE(PEO.SEX
712 ,'F'
713 ,1
714 ,NULL)
715 ,NULL)) AM_ALWMEN,
716 COUNT(DECODE(PEI.PEI_INFORMATION5
717 ,'4'
718 ,DECODE(PEO.SEX
719 ,'M'
720 ,1
721 ,NULL)
722 ,'5'
723 ,DECODE(PEO.SEX
724 ,'M'
725 ,1
726 ,NULL)
727 ,NULL)) A_PMEN,
728 COUNT(DECODE(PEI.PEI_INFORMATION5
729 ,'4'
730 ,DECODE(PEO.SEX
731 ,'F'
732 ,1
733 ,NULL)
734 ,'5'
735 ,DECODE(PEO.SEX
736 ,'F'
737 ,1
738 ,NULL)
739 ,NULL)) A_PWMEN,
740 COUNT(DECODE(PEI.PEI_INFORMATION5
741 ,'3'
742 ,DECODE(PEO.SEX
743 ,'M'
744 ,1
745 ,NULL)
746 ,'9'
747 ,DECODE(PEO.SEX
748 ,'M'
749 ,1
750 ,NULL)
751 ,NULL)) HMEN,
752 COUNT(DECODE(PEI.PEI_INFORMATION5
753 ,'3'
754 ,DECODE(PEO.SEX
755 ,'F'
756 ,1
757 ,NULL)
758 ,'9'
759 ,DECODE(PEO.SEX
760 ,'F'
761 ,1
762 ,NULL)
763 ,NULL)) HWMEN,
764 COUNT(DECODE(PEI.PEI_INFORMATION5
765 ,'1'
766 ,DECODE(PEO.SEX
767 ,'M'
768 ,1
769 ,NULL)
770 ,NULL)) WNHMEN,
771 COUNT(DECODE(PEI.PEI_INFORMATION5
772 ,'1'
773 ,DECODE(PEO.SEX
774 ,'F'
775 ,1
776 ,NULL)
777 ,NULL)) WNHWMEN,
778 COUNT(DECODE(PEI.PEI_INFORMATION5
779 ,NULL
780 ,DECODE(PEO.SEX
781 ,'M'
782 ,1
783 ,NULL)
784 ,NULL)) URMEN,
785 COUNT(DECODE(PEI.PEI_INFORMATION5
786 ,NULL
787 ,DECODE(PEO.SEX
788 ,'F'
789 ,1
790 ,NULL)
791 ,NULL)) URWMEN
792 FROM
793 PER_ALL_PEOPLE_F PEO,
794 PER_ALL_ASSIGNMENTS_F PAF,
795 PER_ASSIGNMENT_STATUS_TYPES AST,
796 PER_PEOPLE_EXTRA_INFO PPET,
797 HR_LOOKUPS HL,
798 PER_PAY_PROPOSALS PPP,
799 PER_PAY_BASES PPB,
800 PER_JOBS JOB,
801 PER_PEOPLE_EXTRA_INFO PEI
802 WHERE PAF.PERSON_ID = PPET.PERSON_ID
803 AND PAF.PERSON_ID = PEO.PERSON_ID
804 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
805 AND PEO.PER_INFORMATION1 = '13'
806 AND PEO.PERSON_ID = pei.person_id (+)
807 AND ( PEI.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
808 OR ( PEI.INFORMATION_TYPE <> 'PER_US_ADDL_ETHNIC_CAT'
809 AND not exists (
810 SELECT
811 1
812 FROM
813 PER_PEOPLE_EXTRA_INFO PEI2
814 WHERE PEI2.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
815 AND PEI2.PERSON_ID = PEI.PERSON_ID )
816 AND PEI.PERSON_EXTRA_INFO_ID = (
817 SELECT
818 MAX(PEI1.PERSON_EXTRA_INFO_ID)
819 FROM
820 PER_PEOPLE_EXTRA_INFO PEI1
821 WHERE PEI1.PERSON_ID = PEI.PERSON_ID ) )
822 OR ( not exists (
823 SELECT
824 PERSON_EXTRA_INFO_ID
825 FROM
826 PER_PEOPLE_EXTRA_INFO PEI3
827 WHERE PEI3.PERSON_ID = PEI.PERSON_ID ) ) )
828 AND PPET.INFORMATION_TYPE = 'PQH_TENURE_STATUS'
829 AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
830 AND PAF.EFFECTIVE_END_DATE
831 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
832 AND PEO.EFFECTIVE_END_DATE
833 AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
834 AND PPET.PEI_INFORMATION1 in ( '01' )
835 AND PPET.PEI_INFORMATION1 IS NOT NULL
836 AND not exists (
837 SELECT
838 PEA.PERSON_ID
839 FROM
840 PER_PEOPLE_EXTRA_INFO PEA
841 WHERE PEA.PERSON_ID = PEO.PERSON_ID
842 AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
843 AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
844 AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
845 AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
846 ,'4712/12/31 00:00:00')) )
847 AND PAF.PRIMARY_FLAG = 'Y'
848 AND PAF.ASSIGNMENT_TYPE = 'E'
849 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
850 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
851 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
852 ,CP_FR
853 ,CP_FT
854 ,CP_PR
855 ,CP_PT) IN ( 'FR' )
856 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
857 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
858 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
859 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
860 AND PAF.JOB_ID = JOB.JOB_ID
861 AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
862 AND PAF.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
863 AND PPP.CHANGE_DATE = (
864 SELECT
865 MAX(CHANGE_DATE)
866 FROM
867 PER_PAY_PROPOSALS PRO
868 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
869 AND PRO.CHANGE_DATE <= P_REPORT_DATE
870 AND PRO.APPROVED = 'Y' )
871 AND NVL(PPP.PROPOSED_SALARY_N
872 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
873 AND PAF.ORGANIZATION_ID IN (
874 SELECT
875 ORGANIZATION_ID
876 FROM
877 HR_ALL_ORGANIZATION_UNITS
878 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID );
879 CURSOR GET_LINE3_COUNTS IS
880 SELECT
881 '02' TENINFO,
882 HLA.LOOKUP_CODE ARANK,
883 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
884 ,P_REPORT_DATE)
885 ,'1'
886 ,DECODE(PEO.SEX
887 ,'M'
888 ,1
889 ,NULL)
890 ,NULL)) NRMEN,
891 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
892 ,P_REPORT_DATE)
893 ,'1'
894 ,DECODE(PEO.SEX
895 ,'F'
896 ,1
897 ,NULL)
898 ,NULL)) NRWMEN,
899 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
900 ,P_REPORT_DATE)
901 ,NULL
902 ,(DECODE(PEO.PER_INFORMATION1
903 ,'2'
904 ,DECODE(PEO.SEX
905 ,'M'
906 ,1
907 ,NULL)
908 ,NULL)))) BNHMEN,
909 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
910 ,P_REPORT_DATE)
911 ,NULL
912 ,(DECODE(PEO.PER_INFORMATION1
913 ,'2'
914 ,DECODE(PEO.SEX
915 ,'F'
916 ,1
917 ,NULL)
918 ,NULL)))) BNHWMEN,
919 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
920 ,P_REPORT_DATE)
921 ,NULL
922 ,(DECODE(PEO.PER_INFORMATION1
923 ,'6'
924 ,DECODE(PEO.SEX
925 ,'M'
926 ,1
927 ,NULL)
928 ,NULL)))) AM_ALMEN,
929 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
930 ,P_REPORT_DATE)
931 ,NULL
932 ,(DECODE(PEO.PER_INFORMATION1
933 ,'6'
934 ,DECODE(PEO.SEX
935 ,'F'
936 ,1
937 ,NULL)
938 ,NULL)))) AM_ALWMEN,
939 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
940 ,P_REPORT_DATE)
941 ,NULL
942 ,(DECODE(PEO.PER_INFORMATION1
943 ,'4'
944 ,DECODE(PEO.SEX
945 ,'M'
946 ,1
947 ,NULL)
948 ,'5'
949 ,DECODE(PEO.SEX
950 ,'M'
951 ,1
952 ,NULL)
953 ,NULL)))) APMEN,
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 ,'4'
959 ,DECODE(PEO.SEX
960 ,'F'
961 ,1
962 ,NULL)
963 ,'5'
964 ,DECODE(PEO.SEX
965 ,'F'
966 ,1
967 ,NULL)
968 ,NULL)))) APWMEN,
969 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
970 ,P_REPORT_DATE)
971 ,NULL
972 ,(DECODE(PEO.PER_INFORMATION1
973 ,'3'
974 ,DECODE(PEO.SEX
975 ,'M'
976 ,1
977 ,NULL)
978 ,'9'
979 ,DECODE(PEO.SEX
980 ,'M'
981 ,1
982 ,NULL)
983 ,NULL)))) HMEN,
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 ,'3'
989 ,DECODE(PEO.SEX
990 ,'F'
991 ,1
992 ,NULL)
993 ,'9'
994 ,DECODE(PEO.SEX
995 ,'F'
996 ,1
997 ,NULL)
998 ,NULL)))) HWMEN,
999 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1000 ,P_REPORT_DATE)
1001 ,NULL
1002 ,(DECODE(PEO.PER_INFORMATION1
1003 ,'1'
1004 ,DECODE(PEO.SEX
1005 ,'M'
1006 ,1
1007 ,NULL)
1008 ,NULL)))) WNHMEN,
1009 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1010 ,P_REPORT_DATE)
1011 ,NULL
1012 ,(DECODE(PEO.PER_INFORMATION1
1013 ,'1'
1014 ,DECODE(PEO.SEX
1015 ,'F'
1016 ,1
1017 ,NULL)
1018 ,NULL)))) WNHWMEN,
1019 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1020 ,P_REPORT_DATE)
1021 ,NULL
1022 ,(DECODE(PEO.PER_INFORMATION1
1023 ,NULL
1024 ,DECODE(PEO.SEX
1025 ,'M'
1026 ,1
1027 ,NULL)
1028 ,NULL)))) URMEN,
1029 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1030 ,P_REPORT_DATE)
1031 ,NULL
1032 ,(DECODE(PEO.PER_INFORMATION1
1033 ,NULL
1034 ,DECODE(PEO.SEX
1035 ,'F'
1036 ,1
1037 ,NULL)
1038 ,NULL)))) URWMEN
1039 FROM
1040 HR_LOOKUPS HLA,
1041 PER_ALL_PEOPLE_F PEO,
1042 PER_ALL_ASSIGNMENTS_F PAF,
1043 PER_ASSIGNMENT_STATUS_TYPES AST,
1044 PER_PEOPLE_EXTRA_INFO PPEA,
1045 PER_PEOPLE_EXTRA_INFO PPET,
1046 HR_LOOKUPS HL,
1047 PER_PAY_PROPOSALS PPP,
1048 PER_PAY_BASES PPB,
1049 PER_JOBS JOB
1050 WHERE PAF.PERSON_ID = PPEA.PERSON_ID
1051 AND PAF.PERSON_ID = PPET.PERSON_ID
1052 AND PAF.PERSON_ID = PEO.PERSON_ID
1053 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
1054 AND PPEA.INFORMATION_TYPE = 'PQH_ACADEMIC_RANK'
1055 AND PPET.INFORMATION_TYPE = 'PQH_TENURE_STATUS'
1056 AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
1057 AND PAF.EFFECTIVE_END_DATE
1058 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
1059 AND PEO.EFFECTIVE_END_DATE
1060 AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
1061 AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
1062 AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
1063 ,'4712/12/31 00:00:00'))
1064 AND PPET.PEI_INFORMATION1 in ( '02' , '04' )
1065 AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
1066 AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE
1067 AND PPEA.PEI_INFORMATION1 IS NOT NULL
1068 AND PPET.PEI_INFORMATION1 IS NOT NULL
1069 AND PPEA.PEI_INFORMATION1 not in ( '01' )
1070 AND PAF.PRIMARY_FLAG = 'Y'
1071 AND PAF.ASSIGNMENT_TYPE = 'E'
1072 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
1073 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
1074 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
1075 ,CP_FR
1076 ,CP_FT
1077 ,CP_PR
1078 ,CP_PT) IN ( 'FR' )
1079 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
1080 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
1081 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
1082 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
1083 AND PAF.JOB_ID = JOB.JOB_ID
1084 AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
1085 AND PAF.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
1086 AND PPP.CHANGE_DATE = (
1087 SELECT
1088 MAX(CHANGE_DATE)
1089 FROM
1090 PER_PAY_PROPOSALS PRO
1091 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
1092 AND PRO.CHANGE_DATE <= P_REPORT_DATE
1093 AND PRO.APPROVED = 'Y' )
1094 AND NVL(PPP.PROPOSED_SALARY_N
1095 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
1096 AND PAF.ORGANIZATION_ID IN (
1097 SELECT
1098 ORGANIZATION_ID
1099 FROM
1100 HR_ALL_ORGANIZATION_UNITS
1101 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
1102 GROUP BY
1103 '02',
1104 HLA.LOOKUP_CODE;
1105 CURSOR GET_LINE3_TMR_COUNTS(C_LOOKUP_CODE IN VARCHAR2) IS
1106 SELECT
1107 COUNT(DECODE(PEI.PEI_INFORMATION5
1108 ,'2'
1109 ,DECODE(PEO.SEX
1110 ,'M'
1111 ,1
1112 ,NULL)
1113 ,NULL)) BNHMEN,
1114 COUNT(DECODE(PEI.PEI_INFORMATION5
1115 ,'2'
1116 ,DECODE(PEO.SEX
1117 ,'F'
1118 ,1
1119 ,NULL)
1120 ,NULL)) BNHWMEN,
1121 COUNT(DECODE(PEI.PEI_INFORMATION5
1122 ,'6'
1123 ,DECODE(PEO.SEX
1124 ,'M'
1125 ,1
1126 ,NULL)
1127 ,NULL)) AM_ALMEN,
1128 COUNT(DECODE(PEI.PEI_INFORMATION5
1129 ,'6'
1130 ,DECODE(PEO.SEX
1131 ,'F'
1132 ,1
1133 ,NULL)
1134 ,NULL)) AM_ALWMEN,
1135 COUNT(DECODE(PEI.PEI_INFORMATION5
1136 ,'4'
1137 ,DECODE(PEO.SEX
1138 ,'M'
1139 ,1
1140 ,NULL)
1141 ,'5'
1142 ,DECODE(PEO.SEX
1143 ,'M'
1144 ,1
1145 ,NULL)
1146 ,NULL)) A_PMEN,
1147 COUNT(DECODE(PEI.PEI_INFORMATION5
1148 ,'4'
1149 ,DECODE(PEO.SEX
1150 ,'F'
1151 ,1
1152 ,NULL)
1153 ,'5'
1154 ,DECODE(PEO.SEX
1155 ,'F'
1156 ,1
1157 ,NULL)
1158 ,NULL)) A_PWMEN,
1159 COUNT(DECODE(PEI.PEI_INFORMATION5
1160 ,'3'
1161 ,DECODE(PEO.SEX
1162 ,'M'
1163 ,1
1164 ,NULL)
1165 ,'9'
1166 ,DECODE(PEO.SEX
1167 ,'M'
1168 ,1
1169 ,NULL)
1170 ,NULL)) HMEN,
1171 COUNT(DECODE(PEI.PEI_INFORMATION5
1172 ,'3'
1173 ,DECODE(PEO.SEX
1174 ,'F'
1175 ,1
1176 ,NULL)
1177 ,'9'
1178 ,DECODE(PEO.SEX
1179 ,'F'
1180 ,1
1181 ,NULL)
1182 ,NULL)) HWMEN,
1183 COUNT(DECODE(PEI.PEI_INFORMATION5
1184 ,'1'
1185 ,DECODE(PEO.SEX
1186 ,'M'
1187 ,1
1188 ,NULL)
1189 ,NULL)) WNHMEN,
1190 COUNT(DECODE(PEI.PEI_INFORMATION5
1191 ,'1'
1192 ,DECODE(PEO.SEX
1193 ,'F'
1194 ,1
1195 ,NULL)
1196 ,NULL)) WNHWMEN,
1197 COUNT(DECODE(PEI.PEI_INFORMATION5
1198 ,NULL
1199 ,DECODE(PEO.SEX
1200 ,'M'
1201 ,1
1202 ,NULL)
1203 ,NULL)) URMEN,
1204 COUNT(DECODE(PEI.PEI_INFORMATION5
1205 ,NULL
1206 ,DECODE(PEO.SEX
1207 ,'F'
1208 ,1
1209 ,NULL)
1210 ,NULL)) URWMEN
1211 FROM
1212 HR_LOOKUPS HLA,
1213 PER_ALL_PEOPLE_F PEO,
1214 PER_ALL_ASSIGNMENTS_F PAF,
1215 PER_ASSIGNMENT_STATUS_TYPES AST,
1216 PER_PEOPLE_EXTRA_INFO PPEA,
1217 PER_PEOPLE_EXTRA_INFO PPET,
1218 HR_LOOKUPS HL,
1219 PER_PAY_PROPOSALS PPP,
1220 PER_PAY_BASES PPB,
1221 PER_JOBS JOB,
1222 PER_PEOPLE_EXTRA_INFO PEI
1223 WHERE PAF.PERSON_ID = PPEA.PERSON_ID
1224 AND PAF.PERSON_ID = PPET.PERSON_ID
1225 AND PAF.PERSON_ID = PEO.PERSON_ID
1226 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
1227 AND PEO.PER_INFORMATION1 = '13'
1228 AND PEO.PERSON_ID = pei.person_id (+)
1229 AND ( PEI.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
1230 OR ( PEI.INFORMATION_TYPE <> 'PER_US_ADDL_ETHNIC_CAT'
1231 AND not exists (
1232 SELECT
1233 1
1234 FROM
1235 PER_PEOPLE_EXTRA_INFO PEI2
1236 WHERE PEI2.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
1237 AND PEI2.PERSON_ID = PEI.PERSON_ID )
1238 AND PEI.PERSON_EXTRA_INFO_ID = (
1239 SELECT
1240 MAX(PEI1.PERSON_EXTRA_INFO_ID)
1241 FROM
1242 PER_PEOPLE_EXTRA_INFO PEI1
1243 WHERE PEI1.PERSON_ID = PEI.PERSON_ID ) )
1244 OR ( not exists (
1245 SELECT
1246 PERSON_EXTRA_INFO_ID
1247 FROM
1248 PER_PEOPLE_EXTRA_INFO PEI3
1249 WHERE PEI3.PERSON_ID = PEI.PERSON_ID ) ) )
1250 AND PPEA.INFORMATION_TYPE = 'PQH_ACADEMIC_RANK'
1251 AND PPET.INFORMATION_TYPE = 'PQH_TENURE_STATUS'
1252 AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
1253 AND PAF.EFFECTIVE_END_DATE
1254 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
1255 AND PEO.EFFECTIVE_END_DATE
1256 AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
1257 AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
1258 AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
1259 ,'4712/12/31 00:00:00'))
1260 AND PPET.PEI_INFORMATION1 in ( '02' , '04' )
1261 AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
1262 AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE
1263 AND PPEA.PEI_INFORMATION1 IS NOT NULL
1264 AND PPET.PEI_INFORMATION1 IS NOT NULL
1265 AND PPEA.PEI_INFORMATION1 not in ( '01' )
1266 AND PAF.PRIMARY_FLAG = 'Y'
1267 AND PAF.ASSIGNMENT_TYPE = 'E'
1268 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
1269 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
1270 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
1271 ,CP_FR
1272 ,CP_FT
1273 ,CP_PR
1274 ,CP_PT) IN ( 'FR' )
1275 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
1276 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
1277 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
1278 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
1279 AND PAF.JOB_ID = JOB.JOB_ID
1280 AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
1281 AND PAF.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
1282 AND PPP.CHANGE_DATE = (
1283 SELECT
1284 MAX(CHANGE_DATE)
1285 FROM
1286 PER_PAY_PROPOSALS PRO
1287 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
1288 AND PRO.CHANGE_DATE <= P_REPORT_DATE
1289 AND PRO.APPROVED = 'Y' )
1290 AND NVL(PPP.PROPOSED_SALARY_N
1291 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
1292 AND PAF.ORGANIZATION_ID IN (
1293 SELECT
1294 ORGANIZATION_ID
1295 FROM
1296 HR_ALL_ORGANIZATION_UNITS
1297 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
1298 AND HLA.LOOKUP_CODE = C_LOOKUP_CODE;
1299 CURSOR GET_LINE4_COUNTS IS
1300 SELECT
1301 '02' TENINFO,
1302 '01' ARANK,
1303 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1304 ,P_REPORT_DATE)
1305 ,'1'
1306 ,DECODE(PEO.SEX
1307 ,'M'
1308 ,1
1309 ,NULL)
1310 ,NULL)) NRMEN,
1311 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1312 ,P_REPORT_DATE)
1313 ,'1'
1314 ,DECODE(PEO.SEX
1315 ,'F'
1316 ,1
1317 ,NULL)
1318 ,NULL)) NRWMEN,
1319 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1320 ,P_REPORT_DATE)
1321 ,NULL
1322 ,(DECODE(PEO.PER_INFORMATION1
1323 ,'2'
1324 ,DECODE(PEO.SEX
1325 ,'M'
1326 ,1
1327 ,NULL)
1328 ,NULL)))) BNHMEN,
1329 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1330 ,P_REPORT_DATE)
1331 ,NULL
1332 ,(DECODE(PEO.PER_INFORMATION1
1333 ,'2'
1334 ,DECODE(PEO.SEX
1335 ,'F'
1336 ,1
1337 ,NULL)
1338 ,NULL)))) BNHWMEN,
1339 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1340 ,P_REPORT_DATE)
1341 ,NULL
1342 ,(DECODE(PEO.PER_INFORMATION1
1343 ,'6'
1344 ,DECODE(PEO.SEX
1345 ,'M'
1346 ,1
1347 ,NULL)
1348 ,NULL)))) AM_ALMEN,
1349 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1350 ,P_REPORT_DATE)
1351 ,NULL
1352 ,(DECODE(PEO.PER_INFORMATION1
1353 ,'6'
1354 ,DECODE(PEO.SEX
1355 ,'F'
1356 ,1
1357 ,NULL)
1358 ,NULL)))) AM_ALWMEN,
1359 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1360 ,P_REPORT_DATE)
1361 ,NULL
1362 ,(DECODE(PEO.PER_INFORMATION1
1363 ,'4'
1364 ,DECODE(PEO.SEX
1365 ,'M'
1366 ,1
1367 ,NULL)
1368 ,'5'
1369 ,DECODE(PEO.SEX
1370 ,'M'
1371 ,1
1372 ,NULL)
1373 ,NULL)))) APMEN,
1374 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1375 ,P_REPORT_DATE)
1376 ,NULL
1377 ,(DECODE(PEO.PER_INFORMATION1
1378 ,'4'
1379 ,DECODE(PEO.SEX
1380 ,'F'
1381 ,1
1382 ,NULL)
1383 ,'5'
1384 ,DECODE(PEO.SEX
1385 ,'F'
1386 ,1
1387 ,NULL)
1388 ,NULL)))) APWMEN,
1389 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1390 ,P_REPORT_DATE)
1391 ,NULL
1392 ,(DECODE(PEO.PER_INFORMATION1
1393 ,'3'
1394 ,DECODE(PEO.SEX
1395 ,'M'
1396 ,1
1397 ,NULL)
1398 ,'9'
1399 ,DECODE(PEO.SEX
1400 ,'M'
1401 ,1
1402 ,NULL)
1403 ,NULL)))) HMEN,
1404 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1405 ,P_REPORT_DATE)
1406 ,NULL
1407 ,(DECODE(PEO.PER_INFORMATION1
1408 ,'3'
1409 ,DECODE(PEO.SEX
1410 ,'F'
1411 ,1
1412 ,NULL)
1413 ,'9'
1414 ,DECODE(PEO.SEX
1415 ,'F'
1416 ,1
1417 ,NULL)
1418 ,NULL)))) HWMEN,
1419 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1420 ,P_REPORT_DATE)
1421 ,NULL
1422 ,(DECODE(PEO.PER_INFORMATION1
1423 ,'1'
1424 ,DECODE(PEO.SEX
1425 ,'M'
1426 ,1
1427 ,NULL)
1428 ,NULL)))) WNHMEN,
1429 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1430 ,P_REPORT_DATE)
1431 ,NULL
1432 ,(DECODE(PEO.PER_INFORMATION1
1433 ,'1'
1434 ,DECODE(PEO.SEX
1435 ,'F'
1436 ,1
1437 ,NULL)
1438 ,NULL)))) WNHWMEN,
1439 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1440 ,P_REPORT_DATE)
1441 ,NULL
1442 ,(DECODE(PEO.PER_INFORMATION1
1443 ,NULL
1444 ,DECODE(PEO.SEX
1445 ,'M'
1446 ,1
1447 ,NULL)
1448 ,NULL)))) URMEN,
1449 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1450 ,P_REPORT_DATE)
1451 ,NULL
1452 ,(DECODE(PEO.PER_INFORMATION1
1453 ,NULL
1454 ,DECODE(PEO.SEX
1455 ,'F'
1456 ,1
1457 ,NULL)
1458 ,NULL)))) URWMEN
1459 FROM
1460 PER_ALL_PEOPLE_F PEO,
1461 PER_ALL_ASSIGNMENTS_F PAF,
1462 PER_ASSIGNMENT_STATUS_TYPES AST,
1463 PER_PEOPLE_EXTRA_INFO PPET,
1464 HR_LOOKUPS HL,
1465 PER_PAY_PROPOSALS PPP,
1466 PER_PAY_BASES PPB,
1467 PER_JOBS JOB
1468 WHERE PAF.PERSON_ID = PPET.PERSON_ID
1469 AND PAF.PERSON_ID = PEO.PERSON_ID
1470 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
1471 AND PPET.INFORMATION_TYPE = 'PQH_TENURE_STATUS'
1472 AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
1473 AND PAF.EFFECTIVE_END_DATE
1474 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
1475 AND PEO.EFFECTIVE_END_DATE
1476 AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
1477 AND PPET.PEI_INFORMATION1 in ( '02' , '04' )
1478 AND PPET.PEI_INFORMATION1 IS NOT NULL
1479 AND not exists (
1480 SELECT
1481 PEA.PERSON_ID
1482 FROM
1483 PER_PEOPLE_EXTRA_INFO PEA
1484 WHERE PEA.PERSON_ID = PEO.PERSON_ID
1485 AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
1486 AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
1487 AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
1488 AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
1489 ,'4712/12/31 00:00:00')) )
1490 AND PAF.PRIMARY_FLAG = 'Y'
1491 AND PAF.ASSIGNMENT_TYPE = 'E'
1492 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
1493 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
1494 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
1495 ,CP_FR
1496 ,CP_FT
1497 ,CP_PR
1498 ,CP_PT) IN ( 'FR' )
1499 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
1500 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
1501 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
1502 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
1503 AND PAF.JOB_ID = JOB.JOB_ID
1504 AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
1505 AND PAF.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
1506 AND PPP.CHANGE_DATE = (
1507 SELECT
1508 MAX(CHANGE_DATE)
1509 FROM
1510 PER_PAY_PROPOSALS PRO
1511 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
1512 AND PRO.CHANGE_DATE <= P_REPORT_DATE
1513 AND PRO.APPROVED = 'Y' )
1514 AND NVL(PPP.PROPOSED_SALARY_N
1515 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
1516 AND PAF.ORGANIZATION_ID IN (
1517 SELECT
1518 ORGANIZATION_ID
1519 FROM
1520 HR_ALL_ORGANIZATION_UNITS
1521 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
1522 GROUP BY
1523 '02',
1524 '01';
1525 CURSOR GET_LINE4_TMR_COUNTS IS
1526 SELECT
1527 COUNT(DECODE(PEI.PEI_INFORMATION5
1528 ,'2'
1529 ,DECODE(PEO.SEX
1530 ,'M'
1531 ,1
1532 ,NULL)
1533 ,NULL)) BNHMEN,
1534 COUNT(DECODE(PEI.PEI_INFORMATION5
1535 ,'2'
1536 ,DECODE(PEO.SEX
1537 ,'F'
1538 ,1
1539 ,NULL)
1540 ,NULL)) BNHWMEN,
1541 COUNT(DECODE(PEI.PEI_INFORMATION5
1542 ,'6'
1543 ,DECODE(PEO.SEX
1544 ,'M'
1545 ,1
1546 ,NULL)
1547 ,NULL)) AM_ALMEN,
1548 COUNT(DECODE(PEI.PEI_INFORMATION5
1549 ,'6'
1550 ,DECODE(PEO.SEX
1551 ,'F'
1552 ,1
1553 ,NULL)
1554 ,NULL)) AM_ALWMEN,
1555 COUNT(DECODE(PEI.PEI_INFORMATION5
1556 ,'4'
1557 ,DECODE(PEO.SEX
1558 ,'M'
1559 ,1
1560 ,NULL)
1561 ,'5'
1562 ,DECODE(PEO.SEX
1563 ,'M'
1564 ,1
1565 ,NULL)
1566 ,NULL)) A_PMEN,
1567 COUNT(DECODE(PEI.PEI_INFORMATION5
1568 ,'4'
1569 ,DECODE(PEO.SEX
1570 ,'F'
1571 ,1
1572 ,NULL)
1573 ,'5'
1574 ,DECODE(PEO.SEX
1575 ,'F'
1576 ,1
1577 ,NULL)
1578 ,NULL)) A_PWMEN,
1579 COUNT(DECODE(PEI.PEI_INFORMATION5
1580 ,'3'
1581 ,DECODE(PEO.SEX
1582 ,'M'
1583 ,1
1584 ,NULL)
1585 ,'9'
1586 ,DECODE(PEO.SEX
1587 ,'M'
1588 ,1
1589 ,NULL)
1590 ,NULL)) HMEN,
1591 COUNT(DECODE(PEI.PEI_INFORMATION5
1592 ,'3'
1593 ,DECODE(PEO.SEX
1594 ,'F'
1595 ,1
1596 ,NULL)
1597 ,'9'
1598 ,DECODE(PEO.SEX
1599 ,'F'
1600 ,1
1601 ,NULL)
1602 ,NULL)) HWMEN,
1603 COUNT(DECODE(PEI.PEI_INFORMATION5
1604 ,'1'
1605 ,DECODE(PEO.SEX
1606 ,'M'
1607 ,1
1608 ,NULL)
1609 ,NULL)) WNHMEN,
1610 COUNT(DECODE(PEI.PEI_INFORMATION5
1611 ,'1'
1612 ,DECODE(PEO.SEX
1613 ,'F'
1614 ,1
1615 ,NULL)
1616 ,NULL)) WNHWMEN,
1617 COUNT(DECODE(PEI.PEI_INFORMATION5
1618 ,NULL
1619 ,DECODE(PEO.SEX
1620 ,'M'
1621 ,1
1622 ,NULL)
1623 ,NULL)) URMEN,
1624 COUNT(DECODE(PEI.PEI_INFORMATION5
1625 ,NULL
1626 ,DECODE(PEO.SEX
1627 ,'F'
1628 ,1
1629 ,NULL)
1630 ,NULL)) URWMEN
1631 FROM
1632 PER_ALL_PEOPLE_F PEO,
1633 PER_ALL_ASSIGNMENTS_F PAF,
1634 PER_ASSIGNMENT_STATUS_TYPES AST,
1635 PER_PEOPLE_EXTRA_INFO PPET,
1636 HR_LOOKUPS HL,
1637 PER_PAY_PROPOSALS PPP,
1638 PER_PAY_BASES PPB,
1639 PER_JOBS JOB,
1640 PER_PEOPLE_EXTRA_INFO PEI
1641 WHERE PAF.PERSON_ID = PPET.PERSON_ID
1642 AND PAF.PERSON_ID = PEO.PERSON_ID
1643 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
1644 AND PEO.PER_INFORMATION1 = '13'
1645 AND PEO.PERSON_ID = pei.person_id (+)
1646 AND ( PEI.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
1647 OR ( PEI.INFORMATION_TYPE <> 'PER_US_ADDL_ETHNIC_CAT'
1648 AND not exists (
1649 SELECT
1650 1
1651 FROM
1652 PER_PEOPLE_EXTRA_INFO PEI2
1653 WHERE PEI2.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
1654 AND PEI2.PERSON_ID = PEI.PERSON_ID )
1655 AND PEI.PERSON_EXTRA_INFO_ID = (
1656 SELECT
1657 MAX(PEI1.PERSON_EXTRA_INFO_ID)
1658 FROM
1659 PER_PEOPLE_EXTRA_INFO PEI1
1660 WHERE PEI1.PERSON_ID = PEI.PERSON_ID ) )
1661 OR ( not exists (
1662 SELECT
1663 PERSON_EXTRA_INFO_ID
1664 FROM
1665 PER_PEOPLE_EXTRA_INFO PEI3
1666 WHERE PEI3.PERSON_ID = PEI.PERSON_ID ) ) )
1667 AND PPET.INFORMATION_TYPE = 'PQH_TENURE_STATUS'
1668 AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
1669 AND PAF.EFFECTIVE_END_DATE
1670 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
1671 AND PEO.EFFECTIVE_END_DATE
1672 AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
1673 AND PPET.PEI_INFORMATION1 in ( '02' , '04' )
1674 AND PPET.PEI_INFORMATION1 IS NOT NULL
1675 AND not exists (
1676 SELECT
1677 PEA.PERSON_ID
1678 FROM
1679 PER_PEOPLE_EXTRA_INFO PEA
1680 WHERE PEA.PERSON_ID = PEO.PERSON_ID
1681 AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
1682 AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
1683 AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
1684 AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
1685 ,'4712/12/31 00:00:00')) )
1686 AND PAF.PRIMARY_FLAG = 'Y'
1687 AND PAF.ASSIGNMENT_TYPE = 'E'
1688 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
1689 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
1690 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
1691 ,CP_FR
1692 ,CP_FT
1693 ,CP_PR
1694 ,CP_PT) IN ( 'FR' )
1695 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
1696 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
1697 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
1698 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
1699 AND PAF.JOB_ID = JOB.JOB_ID
1700 AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
1701 AND PAF.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
1702 AND PPP.CHANGE_DATE = (
1703 SELECT
1704 MAX(CHANGE_DATE)
1705 FROM
1706 PER_PAY_PROPOSALS PRO
1707 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
1708 AND PRO.CHANGE_DATE <= P_REPORT_DATE
1709 AND PRO.APPROVED = 'Y' )
1710 AND NVL(PPP.PROPOSED_SALARY_N
1711 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
1712 AND PAF.ORGANIZATION_ID IN (
1713 SELECT
1714 ORGANIZATION_ID
1715 FROM
1716 HR_ALL_ORGANIZATION_UNITS
1717 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID );
1718 CURSOR GET_LINE5_COUNTS IS
1719 SELECT
1720 '03' TENINFO,
1721 HLA.LOOKUP_CODE ARANK,
1722 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1723 ,P_REPORT_DATE)
1724 ,'1'
1725 ,DECODE(PEO.SEX
1726 ,'M'
1727 ,1
1728 ,NULL)
1729 ,NULL)) NRMEN,
1730 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1731 ,P_REPORT_DATE)
1732 ,'1'
1733 ,DECODE(PEO.SEX
1734 ,'F'
1735 ,1
1736 ,NULL)
1737 ,NULL)) NRWMEN,
1738 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1739 ,P_REPORT_DATE)
1740 ,NULL
1741 ,(DECODE(PEO.PER_INFORMATION1
1742 ,'2'
1743 ,DECODE(PEO.SEX
1744 ,'M'
1745 ,1
1746 ,NULL)
1747 ,NULL)))) BNHMEN,
1748 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1749 ,P_REPORT_DATE)
1750 ,NULL
1751 ,(DECODE(PEO.PER_INFORMATION1
1752 ,'2'
1753 ,DECODE(PEO.SEX
1754 ,'F'
1755 ,1
1756 ,NULL)
1757 ,NULL)))) BNHWMEN,
1758 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1759 ,P_REPORT_DATE)
1760 ,NULL
1761 ,(DECODE(PEO.PER_INFORMATION1
1762 ,'6'
1763 ,DECODE(PEO.SEX
1764 ,'M'
1765 ,1
1766 ,NULL)
1767 ,NULL)))) AM_ALMEN,
1768 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1769 ,P_REPORT_DATE)
1770 ,NULL
1771 ,(DECODE(PEO.PER_INFORMATION1
1772 ,'6'
1773 ,DECODE(PEO.SEX
1774 ,'F'
1775 ,1
1776 ,NULL)
1777 ,NULL)))) AM_ALWMEN,
1778 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1779 ,P_REPORT_DATE)
1780 ,NULL
1781 ,(DECODE(PEO.PER_INFORMATION1
1782 ,'4'
1783 ,DECODE(PEO.SEX
1784 ,'M'
1785 ,1
1786 ,NULL)
1787 ,'5'
1788 ,DECODE(PEO.SEX
1789 ,'M'
1790 ,1
1791 ,NULL)
1792 ,NULL)))) APMEN,
1793 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1794 ,P_REPORT_DATE)
1795 ,NULL
1796 ,(DECODE(PEO.PER_INFORMATION1
1797 ,'4'
1798 ,DECODE(PEO.SEX
1799 ,'F'
1800 ,1
1801 ,NULL)
1802 ,'5'
1803 ,DECODE(PEO.SEX
1804 ,'F'
1805 ,1
1806 ,NULL)
1807 ,NULL)))) APWMEN,
1808 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1809 ,P_REPORT_DATE)
1810 ,NULL
1811 ,(DECODE(PEO.PER_INFORMATION1
1812 ,'3'
1813 ,DECODE(PEO.SEX
1814 ,'M'
1815 ,1
1816 ,NULL)
1817 ,'9'
1818 ,DECODE(PEO.SEX
1819 ,'M'
1820 ,1
1821 ,NULL)
1822 ,NULL)))) HMEN,
1823 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1824 ,P_REPORT_DATE)
1825 ,NULL
1826 ,(DECODE(PEO.PER_INFORMATION1
1827 ,'3'
1828 ,DECODE(PEO.SEX
1829 ,'F'
1830 ,1
1831 ,NULL)
1832 ,'9'
1833 ,DECODE(PEO.SEX
1834 ,'F'
1835 ,1
1836 ,NULL)
1837 ,NULL)))) HWMEN,
1838 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1839 ,P_REPORT_DATE)
1840 ,NULL
1841 ,(DECODE(PEO.PER_INFORMATION1
1842 ,'1'
1843 ,DECODE(PEO.SEX
1844 ,'M'
1845 ,1
1846 ,NULL)
1847 ,NULL)))) WNHMEN,
1848 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1849 ,P_REPORT_DATE)
1850 ,NULL
1851 ,(DECODE(PEO.PER_INFORMATION1
1852 ,'1'
1853 ,DECODE(PEO.SEX
1854 ,'F'
1855 ,1
1856 ,NULL)
1857 ,NULL)))) WNHWMEN,
1858 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1859 ,P_REPORT_DATE)
1860 ,NULL
1861 ,(DECODE(PEO.PER_INFORMATION1
1862 ,NULL
1863 ,DECODE(PEO.SEX
1864 ,'M'
1865 ,1
1866 ,NULL)
1867 ,NULL)))) URMEN,
1868 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1869 ,P_REPORT_DATE)
1870 ,NULL
1871 ,(DECODE(PEO.PER_INFORMATION1
1872 ,NULL
1873 ,DECODE(PEO.SEX
1874 ,'F'
1875 ,1
1876 ,NULL)
1877 ,NULL)))) URWMEN
1878 FROM
1879 HR_LOOKUPS HLA,
1880 PER_ALL_PEOPLE_F PEO,
1881 PER_ALL_ASSIGNMENTS_F PAF,
1882 PER_ASSIGNMENT_STATUS_TYPES AST,
1883 PER_PEOPLE_EXTRA_INFO PPEA,
1884 HR_LOOKUPS HL,
1885 PER_PAY_PROPOSALS PPP,
1886 PER_PAY_BASES PPB,
1887 PER_JOBS JOB
1888 WHERE PAF.PERSON_ID = PEO.PERSON_ID
1889 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
1890 AND PAF.PERSON_ID = PPEA.PERSON_ID
1891 AND PPEA.INFORMATION_TYPE = 'PQH_ACADEMIC_RANK'
1892 AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
1893 AND PAF.EFFECTIVE_END_DATE
1894 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
1895 AND PEO.EFFECTIVE_END_DATE
1896 AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
1897 AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE
1898 AND PPEA.PEI_INFORMATION1 not in ( '01' )
1899 AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
1900 AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
1901 ,'4712/12/31 00:00:00'))
1902 AND PPEA.PEI_INFORMATION1 IS NOT NULL
1903 AND not exists (
1904 SELECT
1905 PET.PERSON_ID
1906 FROM
1907 PER_PEOPLE_EXTRA_INFO PET
1908 WHERE PET.PERSON_ID = PEO.PERSON_ID
1909 AND PET.INFORMATION_TYPE in ( 'PQH_TENURE_STATUS' )
1910 AND PET.PEI_INFORMATION1 in ( '01' , '02' , '04' )
1911 AND FND_DATE.CANONICAL_TO_DATE(PET.PEI_INFORMATION2) <= P_REPORT_DATE )
1912 AND PAF.PRIMARY_FLAG = 'Y'
1913 AND PAF.ASSIGNMENT_TYPE = 'E'
1914 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
1915 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
1916 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
1917 ,CP_FR
1918 ,CP_FT
1919 ,CP_PR
1920 ,CP_PT) IN ( 'FR' )
1921 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
1922 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
1923 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
1924 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
1925 AND PAF.JOB_ID = JOB.JOB_ID
1926 AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
1927 AND PAF.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
1928 AND PPP.CHANGE_DATE = (
1929 SELECT
1930 MAX(CHANGE_DATE)
1931 FROM
1932 PER_PAY_PROPOSALS PRO
1933 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
1934 AND PRO.CHANGE_DATE <= P_REPORT_DATE
1935 AND PRO.APPROVED = 'Y' )
1936 AND NVL(PPP.PROPOSED_SALARY_N
1937 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
1938 AND PAF.ORGANIZATION_ID IN (
1939 SELECT
1940 ORGANIZATION_ID
1941 FROM
1942 HR_ALL_ORGANIZATION_UNITS
1943 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
1944 GROUP BY
1945 '03',
1946 HLA.LOOKUP_CODE;
1947 CURSOR GET_LINE5_TMR_COUNTS(C_LOOKUP_CODE IN VARCHAR2) IS
1948 SELECT
1949 COUNT(DECODE(PEI.PEI_INFORMATION5
1950 ,'2'
1951 ,DECODE(PEO.SEX
1952 ,'M'
1953 ,1
1954 ,NULL)
1955 ,NULL)) BNHMEN,
1956 COUNT(DECODE(PEI.PEI_INFORMATION5
1957 ,'2'
1958 ,DECODE(PEO.SEX
1959 ,'F'
1960 ,1
1961 ,NULL)
1962 ,NULL)) BNHWMEN,
1963 COUNT(DECODE(PEI.PEI_INFORMATION5
1964 ,'6'
1965 ,DECODE(PEO.SEX
1966 ,'M'
1967 ,1
1968 ,NULL)
1969 ,NULL)) AM_ALMEN,
1970 COUNT(DECODE(PEI.PEI_INFORMATION5
1971 ,'6'
1972 ,DECODE(PEO.SEX
1973 ,'F'
1974 ,1
1975 ,NULL)
1976 ,NULL)) AM_ALWMEN,
1977 COUNT(DECODE(PEI.PEI_INFORMATION5
1978 ,'4'
1979 ,DECODE(PEO.SEX
1980 ,'M'
1981 ,1
1982 ,NULL)
1983 ,'5'
1984 ,DECODE(PEO.SEX
1985 ,'M'
1986 ,1
1987 ,NULL)
1988 ,NULL)) A_PMEN,
1989 COUNT(DECODE(PEI.PEI_INFORMATION5
1990 ,'4'
1991 ,DECODE(PEO.SEX
1992 ,'F'
1993 ,1
1994 ,NULL)
1995 ,'5'
1996 ,DECODE(PEO.SEX
1997 ,'F'
1998 ,1
1999 ,NULL)
2000 ,NULL)) A_PWMEN,
2001 COUNT(DECODE(PEI.PEI_INFORMATION5
2002 ,'3'
2003 ,DECODE(PEO.SEX
2004 ,'M'
2005 ,1
2006 ,NULL)
2007 ,'9'
2008 ,DECODE(PEO.SEX
2009 ,'M'
2010 ,1
2011 ,NULL)
2012 ,NULL)) HMEN,
2013 COUNT(DECODE(PEI.PEI_INFORMATION5
2014 ,'3'
2015 ,DECODE(PEO.SEX
2016 ,'F'
2017 ,1
2018 ,NULL)
2019 ,'9'
2020 ,DECODE(PEO.SEX
2021 ,'F'
2022 ,1
2023 ,NULL)
2024 ,NULL)) HWMEN,
2025 COUNT(DECODE(PEI.PEI_INFORMATION5
2026 ,'1'
2027 ,DECODE(PEO.SEX
2028 ,'M'
2029 ,1
2030 ,NULL)
2031 ,NULL)) WNHMEN,
2032 COUNT(DECODE(PEI.PEI_INFORMATION5
2033 ,'1'
2034 ,DECODE(PEO.SEX
2035 ,'F'
2036 ,1
2037 ,NULL)
2038 ,NULL)) WNHWMEN,
2039 COUNT(DECODE(PEI.PEI_INFORMATION5
2040 ,NULL
2041 ,DECODE(PEO.SEX
2042 ,'M'
2043 ,1
2044 ,NULL)
2045 ,NULL)) URMEN,
2046 COUNT(DECODE(PEI.PEI_INFORMATION5
2047 ,NULL
2048 ,DECODE(PEO.SEX
2049 ,'F'
2050 ,1
2051 ,NULL)
2052 ,NULL)) URWMEN
2053 FROM
2054 HR_LOOKUPS HLA,
2055 PER_ALL_PEOPLE_F PEO,
2056 PER_ALL_ASSIGNMENTS_F PAF,
2057 PER_ASSIGNMENT_STATUS_TYPES AST,
2058 PER_PEOPLE_EXTRA_INFO PPEA,
2059 HR_LOOKUPS HL,
2060 PER_PAY_PROPOSALS PPP,
2061 PER_PAY_BASES PPB,
2062 PER_JOBS JOB,
2063 PER_PEOPLE_EXTRA_INFO PEI
2064 WHERE PAF.PERSON_ID = PEO.PERSON_ID
2065 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
2066 AND PEO.PER_INFORMATION1 = '13'
2067 AND PEO.PERSON_ID = pei.person_id (+)
2068 AND ( PEI.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
2069 OR ( PEI.INFORMATION_TYPE <> 'PER_US_ADDL_ETHNIC_CAT'
2070 AND not exists (
2071 SELECT
2072 1
2073 FROM
2074 PER_PEOPLE_EXTRA_INFO PEI2
2075 WHERE PEI2.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
2076 AND PEI2.PERSON_ID = PEI.PERSON_ID )
2077 AND PEI.PERSON_EXTRA_INFO_ID = (
2078 SELECT
2079 MAX(PEI1.PERSON_EXTRA_INFO_ID)
2080 FROM
2081 PER_PEOPLE_EXTRA_INFO PEI1
2082 WHERE PEI1.PERSON_ID = PEI.PERSON_ID ) )
2083 OR ( not exists (
2084 SELECT
2085 PERSON_EXTRA_INFO_ID
2086 FROM
2087 PER_PEOPLE_EXTRA_INFO PEI3
2088 WHERE PEI3.PERSON_ID = PEI.PERSON_ID ) ) )
2089 AND PAF.PERSON_ID = PPEA.PERSON_ID
2090 AND PPEA.INFORMATION_TYPE = 'PQH_ACADEMIC_RANK'
2091 AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
2092 AND PAF.EFFECTIVE_END_DATE
2093 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
2094 AND PEO.EFFECTIVE_END_DATE
2095 AND HLA.LOOKUP_TYPE = PPEA.INFORMATION_TYPE
2096 AND PPEA.PEI_INFORMATION1 = HLA.LOOKUP_CODE
2097 AND PPEA.PEI_INFORMATION1 not in ( '01' )
2098 AND P_REPORT_DATE BETWEEN FND_DATE.CANONICAL_TO_DATE(PPEA.PEI_INFORMATION2)
2099 AND FND_DATE.CANONICAL_TO_DATE(NVL(PPEA.PEI_INFORMATION3
2100 ,'4712/12/31 00:00:00'))
2101 AND PPEA.PEI_INFORMATION1 IS NOT NULL
2102 AND not exists (
2103 SELECT
2104 PET.PERSON_ID
2105 FROM
2106 PER_PEOPLE_EXTRA_INFO PET
2107 WHERE PET.PERSON_ID = PEO.PERSON_ID
2108 AND PET.INFORMATION_TYPE in ( 'PQH_TENURE_STATUS' )
2109 AND PET.PEI_INFORMATION1 in ( '01' , '02' , '04' )
2110 AND FND_DATE.CANONICAL_TO_DATE(PET.PEI_INFORMATION2) <= P_REPORT_DATE )
2111 AND PAF.PRIMARY_FLAG = 'Y'
2112 AND PAF.ASSIGNMENT_TYPE = 'E'
2113 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
2114 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
2115 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
2116 ,CP_FR
2117 ,CP_FT
2118 ,CP_PR
2119 ,CP_PT) IN ( 'FR' )
2120 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
2121 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
2122 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
2123 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
2124 AND PAF.JOB_ID = JOB.JOB_ID
2125 AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
2126 AND PAF.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
2127 AND PPP.CHANGE_DATE = (
2128 SELECT
2129 MAX(CHANGE_DATE)
2130 FROM
2131 PER_PAY_PROPOSALS PRO
2132 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
2133 AND PRO.CHANGE_DATE <= P_REPORT_DATE
2134 AND PRO.APPROVED = 'Y' )
2135 AND NVL(PPP.PROPOSED_SALARY_N
2136 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
2137 AND PAF.ORGANIZATION_ID IN (
2138 SELECT
2139 ORGANIZATION_ID
2140 FROM
2141 HR_ALL_ORGANIZATION_UNITS
2142 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
2143 AND HLA.LOOKUP_CODE = C_LOOKUP_CODE;
2144 CURSOR GET_LINE6_COUNTS IS
2145 SELECT
2146 '03' TENINFO,
2147 '01' ARANK,
2148 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2149 ,P_REPORT_DATE)
2150 ,'1'
2151 ,DECODE(PEO.SEX
2152 ,'M'
2153 ,1
2154 ,NULL)
2155 ,NULL)) NRMEN,
2156 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2157 ,P_REPORT_DATE)
2158 ,'1'
2159 ,DECODE(PEO.SEX
2160 ,'F'
2161 ,1
2162 ,NULL)
2163 ,NULL)) NRWMEN,
2164 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2165 ,P_REPORT_DATE)
2166 ,NULL
2167 ,(DECODE(PEO.PER_INFORMATION1
2168 ,'2'
2169 ,DECODE(PEO.SEX
2170 ,'M'
2171 ,1
2172 ,NULL)
2173 ,NULL)))) BNHMEN,
2174 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2175 ,P_REPORT_DATE)
2176 ,NULL
2177 ,(DECODE(PEO.PER_INFORMATION1
2178 ,'2'
2179 ,DECODE(PEO.SEX
2180 ,'F'
2181 ,1
2182 ,NULL)
2183 ,NULL)))) BNHWMEN,
2184 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2185 ,P_REPORT_DATE)
2186 ,NULL
2187 ,(DECODE(PEO.PER_INFORMATION1
2188 ,'6'
2189 ,DECODE(PEO.SEX
2190 ,'M'
2191 ,1
2192 ,NULL)
2193 ,NULL)))) AM_ALMEN,
2194 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2195 ,P_REPORT_DATE)
2196 ,NULL
2197 ,(DECODE(PEO.PER_INFORMATION1
2198 ,'6'
2199 ,DECODE(PEO.SEX
2200 ,'F'
2201 ,1
2202 ,NULL)
2203 ,NULL)))) AM_ALWMEN,
2204 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2205 ,P_REPORT_DATE)
2206 ,NULL
2207 ,(DECODE(PEO.PER_INFORMATION1
2208 ,'4'
2209 ,DECODE(PEO.SEX
2210 ,'M'
2211 ,1
2212 ,NULL)
2213 ,'5'
2214 ,DECODE(PEO.SEX
2215 ,'M'
2216 ,1
2217 ,NULL)
2218 ,NULL)))) APMEN,
2219 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2220 ,P_REPORT_DATE)
2221 ,NULL
2222 ,(DECODE(PEO.PER_INFORMATION1
2223 ,'4'
2224 ,DECODE(PEO.SEX
2225 ,'F'
2226 ,1
2227 ,NULL)
2228 ,'5'
2229 ,DECODE(PEO.SEX
2230 ,'F'
2231 ,1
2232 ,NULL)
2233 ,NULL)))) APWMEN,
2234 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2235 ,P_REPORT_DATE)
2236 ,NULL
2237 ,(DECODE(PEO.PER_INFORMATION1
2238 ,'3'
2239 ,DECODE(PEO.SEX
2240 ,'M'
2241 ,1
2242 ,NULL)
2243 ,'9'
2244 ,DECODE(PEO.SEX
2245 ,'M'
2246 ,1
2247 ,NULL)
2248 ,NULL)))) HMEN,
2249 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2250 ,P_REPORT_DATE)
2251 ,NULL
2252 ,(DECODE(PEO.PER_INFORMATION1
2253 ,'3'
2254 ,DECODE(PEO.SEX
2255 ,'F'
2256 ,1
2257 ,NULL)
2258 ,'9'
2259 ,DECODE(PEO.SEX
2260 ,'F'
2261 ,1
2262 ,NULL)
2263 ,NULL)))) HWMEN,
2264 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2265 ,P_REPORT_DATE)
2266 ,NULL
2267 ,(DECODE(PEO.PER_INFORMATION1
2268 ,'1'
2269 ,DECODE(PEO.SEX
2270 ,'M'
2271 ,1
2272 ,NULL)
2273 ,NULL)))) WNHMEN,
2274 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2275 ,P_REPORT_DATE)
2276 ,NULL
2277 ,(DECODE(PEO.PER_INFORMATION1
2278 ,'1'
2279 ,DECODE(PEO.SEX
2280 ,'F'
2281 ,1
2282 ,NULL)
2283 ,NULL)))) WNHWMEN,
2284 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2285 ,P_REPORT_DATE)
2286 ,NULL
2287 ,(DECODE(PEO.PER_INFORMATION1
2288 ,NULL
2289 ,DECODE(PEO.SEX
2290 ,'M'
2291 ,1
2292 ,NULL)
2293 ,NULL)))) URMEN,
2294 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
2295 ,P_REPORT_DATE)
2296 ,NULL
2297 ,(DECODE(PEO.PER_INFORMATION1
2298 ,NULL
2299 ,DECODE(PEO.SEX
2300 ,'F'
2301 ,1
2302 ,NULL)
2303 ,NULL)))) URWMEN
2304 FROM
2305 PER_ALL_PEOPLE_F PEO,
2306 PER_ALL_ASSIGNMENTS_F PAF,
2307 PER_ASSIGNMENT_STATUS_TYPES AST,
2308 HR_LOOKUPS HL,
2309 PER_PAY_PROPOSALS PPP,
2310 PER_PAY_BASES PPB,
2311 PER_JOBS JOB
2312 WHERE PAF.PERSON_ID = PEO.PERSON_ID
2313 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
2314 AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
2315 AND PAF.EFFECTIVE_END_DATE
2316 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
2317 AND PEO.EFFECTIVE_END_DATE
2318 AND not exists (
2319 SELECT
2320 PET.PERSON_ID
2321 FROM
2322 PER_PEOPLE_EXTRA_INFO PET
2323 WHERE PET.PERSON_ID = PEO.PERSON_ID
2324 AND PET.INFORMATION_TYPE in ( 'PQH_TENURE_STATUS' )
2325 AND PET.PEI_INFORMATION1 in ( '01' , '02' , '04' )
2326 AND FND_DATE.CANONICAL_TO_DATE(PET.PEI_INFORMATION2) <= P_REPORT_DATE )
2327 AND not exists (
2328 SELECT
2329 PEA.PERSON_ID
2330 FROM
2331 PER_PEOPLE_EXTRA_INFO PEA
2332 WHERE PEA.PERSON_ID = PEO.PERSON_ID
2333 AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
2334 AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
2335 AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
2336 AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
2337 ,'4712/12/31 00:00:00')) )
2338 AND PAF.PRIMARY_FLAG = 'Y'
2339 AND PAF.ASSIGNMENT_TYPE = 'E'
2340 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
2341 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
2342 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
2343 ,CP_FR
2344 ,CP_FT
2345 ,CP_PR
2346 ,CP_PT) IN ( 'FR' )
2347 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
2348 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
2349 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
2350 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
2351 AND PAF.JOB_ID = JOB.JOB_ID
2352 AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
2353 AND PAF.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
2354 AND PPP.CHANGE_DATE = (
2355 SELECT
2356 MAX(CHANGE_DATE)
2357 FROM
2358 PER_PAY_PROPOSALS PRO
2359 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
2360 AND PRO.CHANGE_DATE <= P_REPORT_DATE
2361 AND PRO.APPROVED = 'Y' )
2362 AND NVL(PPP.PROPOSED_SALARY_N
2363 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
2364 AND PAF.ORGANIZATION_ID IN (
2365 SELECT
2366 ORGANIZATION_ID
2367 FROM
2368 HR_ALL_ORGANIZATION_UNITS
2369 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
2370 GROUP BY
2371 '03',
2372 '01';
2373 CURSOR GET_LINE6_TMR_COUNTS IS
2374 SELECT
2375 COUNT(DECODE(PEI.PEI_INFORMATION5
2376 ,'2'
2377 ,DECODE(PEO.SEX
2378 ,'M'
2379 ,1
2380 ,NULL)
2381 ,NULL)) BNHMEN,
2382 COUNT(DECODE(PEI.PEI_INFORMATION5
2383 ,'2'
2384 ,DECODE(PEO.SEX
2385 ,'F'
2386 ,1
2387 ,NULL)
2388 ,NULL)) BNHWMEN,
2389 COUNT(DECODE(PEI.PEI_INFORMATION5
2390 ,'6'
2391 ,DECODE(PEO.SEX
2392 ,'M'
2393 ,1
2394 ,NULL)
2395 ,NULL)) AM_ALMEN,
2396 COUNT(DECODE(PEI.PEI_INFORMATION5
2397 ,'6'
2398 ,DECODE(PEO.SEX
2399 ,'F'
2400 ,1
2401 ,NULL)
2402 ,NULL)) AM_ALWMEN,
2403 COUNT(DECODE(PEI.PEI_INFORMATION5
2404 ,'4'
2405 ,DECODE(PEO.SEX
2406 ,'M'
2407 ,1
2408 ,NULL)
2409 ,'5'
2410 ,DECODE(PEO.SEX
2411 ,'M'
2412 ,1
2413 ,NULL)
2414 ,NULL)) A_PMEN,
2415 COUNT(DECODE(PEI.PEI_INFORMATION5
2416 ,'4'
2417 ,DECODE(PEO.SEX
2418 ,'F'
2419 ,1
2420 ,NULL)
2421 ,'5'
2422 ,DECODE(PEO.SEX
2423 ,'F'
2424 ,1
2425 ,NULL)
2426 ,NULL)) A_PWMEN,
2427 COUNT(DECODE(PEI.PEI_INFORMATION5
2428 ,'3'
2429 ,DECODE(PEO.SEX
2430 ,'M'
2431 ,1
2432 ,NULL)
2433 ,'9'
2434 ,DECODE(PEO.SEX
2435 ,'M'
2436 ,1
2437 ,NULL)
2438 ,NULL)) HMEN,
2439 COUNT(DECODE(PEI.PEI_INFORMATION5
2440 ,'3'
2441 ,DECODE(PEO.SEX
2442 ,'F'
2443 ,1
2444 ,NULL)
2445 ,'9'
2446 ,DECODE(PEO.SEX
2447 ,'F'
2448 ,1
2449 ,NULL)
2450 ,NULL)) HWMEN,
2451 COUNT(DECODE(PEI.PEI_INFORMATION5
2452 ,'1'
2453 ,DECODE(PEO.SEX
2454 ,'M'
2455 ,1
2456 ,NULL)
2457 ,NULL)) WNHMEN,
2458 COUNT(DECODE(PEI.PEI_INFORMATION5
2459 ,'1'
2460 ,DECODE(PEO.SEX
2461 ,'F'
2462 ,1
2463 ,NULL)
2464 ,NULL)) WNHWMEN,
2465 COUNT(DECODE(PEI.PEI_INFORMATION5
2466 ,NULL
2467 ,DECODE(PEO.SEX
2468 ,'M'
2469 ,1
2470 ,NULL)
2471 ,NULL)) URMEN,
2472 COUNT(DECODE(PEI.PEI_INFORMATION5
2473 ,NULL
2474 ,DECODE(PEO.SEX
2475 ,'F'
2476 ,1
2477 ,NULL)
2478 ,NULL)) URWMEN
2479 FROM
2480 PER_ALL_PEOPLE_F PEO,
2481 PER_ALL_ASSIGNMENTS_F PAF,
2482 PER_ASSIGNMENT_STATUS_TYPES AST,
2483 HR_LOOKUPS HL,
2484 PER_PAY_PROPOSALS PPP,
2485 PER_PAY_BASES PPB,
2486 PER_JOBS JOB,
2487 PER_PEOPLE_EXTRA_INFO PEI
2488 WHERE PAF.PERSON_ID = PEO.PERSON_ID
2489 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
2490 AND PEO.PER_INFORMATION1 = '13'
2491 AND PEO.PERSON_ID = pei.person_id (+)
2492 AND ( PEI.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
2493 OR ( PEI.INFORMATION_TYPE <> 'PER_US_ADDL_ETHNIC_CAT'
2494 AND not exists (
2495 SELECT
2496 1
2497 FROM
2498 PER_PEOPLE_EXTRA_INFO PEI2
2499 WHERE PEI2.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
2500 AND PEI2.PERSON_ID = PEI.PERSON_ID )
2501 AND PEI.PERSON_EXTRA_INFO_ID = (
2502 SELECT
2503 MAX(PEI1.PERSON_EXTRA_INFO_ID)
2504 FROM
2505 PER_PEOPLE_EXTRA_INFO PEI1
2506 WHERE PEI1.PERSON_ID = PEI.PERSON_ID ) )
2507 OR ( not exists (
2508 SELECT
2509 PERSON_EXTRA_INFO_ID
2510 FROM
2511 PER_PEOPLE_EXTRA_INFO PEI3
2512 WHERE PEI3.PERSON_ID = PEI.PERSON_ID ) ) )
2513 AND P_REPORT_DATE BETWEEN PAF.EFFECTIVE_START_DATE
2514 AND PAF.EFFECTIVE_END_DATE
2515 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
2516 AND PEO.EFFECTIVE_END_DATE
2517 AND not exists (
2518 SELECT
2519 PET.PERSON_ID
2520 FROM
2521 PER_PEOPLE_EXTRA_INFO PET
2522 WHERE PET.PERSON_ID = PEO.PERSON_ID
2523 AND PET.INFORMATION_TYPE in ( 'PQH_TENURE_STATUS' )
2524 AND PET.PEI_INFORMATION1 in ( '01' , '02' , '04' )
2525 AND FND_DATE.CANONICAL_TO_DATE(PET.PEI_INFORMATION2) <= P_REPORT_DATE )
2526 AND not exists (
2527 SELECT
2528 PEA.PERSON_ID
2529 FROM
2530 PER_PEOPLE_EXTRA_INFO PEA
2531 WHERE PEA.PERSON_ID = PEO.PERSON_ID
2532 AND PEA.INFORMATION_TYPE in ( 'PQH_ACADEMIC_RANK' )
2533 AND PEA.PEI_INFORMATION1 in ( '02' , '03' , '04' , '05' , '06' )
2534 AND P_REPORT_DATE between FND_DATE.CANONICAL_TO_DATE(PEA.PEI_INFORMATION2)
2535 AND FND_DATE.CANONICAL_TO_DATE(NVL(PEA.PEI_INFORMATION3
2536 ,'4712/12/31 00:00:00')) )
2537 AND PAF.PRIMARY_FLAG = 'Y'
2538 AND PAF.ASSIGNMENT_TYPE = 'E'
2539 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
2540 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
2541 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
2542 ,CP_FR
2543 ,CP_FT
2544 ,CP_PR
2545 ,CP_PT) IN ( 'FR' )
2546 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
2547 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
2548 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
2549 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
2550 AND PAF.JOB_ID = JOB.JOB_ID
2551 AND PAF.PAY_BASIS_ID = PPB.PAY_BASIS_ID
2552 AND PAF.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
2553 AND PPP.CHANGE_DATE = (
2554 SELECT
2555 MAX(CHANGE_DATE)
2556 FROM
2557 PER_PAY_PROPOSALS PRO
2558 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
2559 AND PRO.CHANGE_DATE <= P_REPORT_DATE
2560 AND PRO.APPROVED = 'Y' )
2561 AND NVL(PPP.PROPOSED_SALARY_N
2562 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
2563 AND PAF.ORGANIZATION_ID IN (
2564 SELECT
2565 ORGANIZATION_ID
2566 FROM
2567 HR_ALL_ORGANIZATION_UNITS
2568 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID );
2569 BEGIN
2570 -- HR_STANDARD.EVENT('BEFORE REPORT');
2571 CP_REPORT_DATE := to_char(P_REPORT_DATE,'DD-MON-YYYY');
2572 PQH_EMPLOYMENT_CATEGORY.FETCH_EMPL_CATEGORIES(P_BUSINESS_GROUP_ID
2573 ,L_FR
2574 ,L_FT
2575 ,L_PR
2576 ,L_PT);
2577 CP_FR := L_FR;
2578 CP_FT := L_FT;
2579 CP_PR := L_PR;
2580 CP_PT := L_PT;
2581 FOR i IN GET_LINE1_COUNTS LOOP
2582 LINE := I.TENINFO;
2583 SC := I.ARANK;
2584 L_NR_MEN := I.NRMEN;
2585 L_NR_WMEN := I.NRWMEN;
2586 L_BNH_MEN := I.BNHMEN;
2587 L_BNH_WMEN := I.BNHWMEN;
2588 L_AMAI_MEN := I.AM_ALMEN;
2589 L_AMAI_WMEN := I.AM_ALWMEN;
2590 L_AP_MEN := I.APMEN;
2591 L_AP_WMEN := I.APWMEN;
2592 L_H_MEN := I.HMEN;
2593 L_H_WMEN := I.HWMEN;
2594 L_WNH_MEN := I.WNHMEN;
2595 L_WNH_WMEN := I.WNHWMEN;
2596 L_UR_MEN := I.URMEN;
2597 L_UR_WMEN := I.URWMEN;
2598 L_TOT_MEN := L_NR_MEN + L_BNH_MEN + L_AMAI_MEN + L_AP_MEN + L_H_MEN + L_WNH_MEN + L_UR_MEN;
2599 L_TOT_WMEN := L_NR_WMEN + L_BNH_WMEN + L_AMAI_WMEN + L_AP_WMEN + L_H_WMEN + L_WNH_WMEN + L_UR_WMEN;
2600 FOR j IN GET_LINE1_TMRACES_COUNTS(sc) LOOP
2601 L_BNH_MEN := L_BNH_MEN + J.BNHMEN;
2602 L_BNH_WMEN := L_BNH_WMEN + J.BNHWMEN;
2603 L_AMAI_MEN := L_AMAI_MEN + J.AM_ALMEN;
2604 L_AMAI_WMEN := L_AMAI_WMEN + J.AM_ALWMEN;
2605 L_AP_MEN := L_AP_MEN + J.A_PMEN;
2606 L_AP_WMEN := L_AP_WMEN + J.A_PWMEN;
2607 L_H_MEN := L_H_MEN + J.HMEN;
2608 L_H_WMEN := L_H_WMEN + J.HWMEN;
2609 L_WNH_MEN := L_WNH_MEN + J.WNHMEN;
2610 L_WNH_WMEN := L_WNH_WMEN + J.WNHWMEN;
2611 L_UR_MEN := L_UR_MEN + J.URMEN;
2612 L_UR_WMEN := L_UR_WMEN + J.URWMEN;
2613 L_TOT_MEN := L_TOT_MEN + J.BNHMEN + J.AM_ALMEN + J.A_PMEN + J.HMEN + J.WNHMEN + J.URMEN;
2614 L_TOT_WMEN := L_TOT_WMEN + J.BNHWMEN + J.AM_ALWMEN + J.A_PWMEN + J.HWMEN + J.WNHWMEN + J.URWMEN;
2615 END LOOP;
2616 INSERT INTO PAY_US_RPT_TOTALS
2617 (SESSION_ID
2618 ,ATTRIBUTE1
2619 ,VALUE1
2620 ,VALUE2
2621 ,VALUE3
2622 ,VALUE4
2623 ,VALUE5
2624 ,VALUE6
2625 ,VALUE7
2626 ,VALUE8
2627 ,VALUE9
2628 ,VALUE10
2629 ,VALUE11
2630 ,VALUE12
2631 ,VALUE13
2632 ,VALUE14
2633 ,VALUE15
2634 ,VALUE16
2635 ,VALUE17
2636 ,VALUE18)
2637 VALUES (USERENV('sessionid')
2638 ,'IPED8'
2639 ,LINE
2640 ,SC
2641 ,L_NR_MEN
2642 ,L_NR_WMEN
2643 ,L_BNH_MEN
2644 ,L_BNH_WMEN
2645 ,L_AMAI_MEN
2646 ,L_AMAI_WMEN
2647 ,L_AP_MEN
2648 ,L_AP_WMEN
2649 ,L_H_MEN
2650 ,L_H_WMEN
2651 ,L_WNH_MEN
2652 ,L_WNH_WMEN
2653 ,L_UR_MEN
2654 ,L_UR_WMEN
2655 ,L_TOT_MEN
2656 ,L_TOT_WMEN);
2657 COMMIT;
2658 END LOOP;
2659 OPEN GET_LINE2_COUNTS;
2660 FETCH GET_LINE2_COUNTS
2661 INTO LINE,SC,L_NR_MEN,L_NR_WMEN,L_BNH_MEN,L_BNH_WMEN,L_AMAI_MEN,L_AMAI_WMEN,L_AP_MEN,L_AP_WMEN,L_H_MEN,L_H_WMEN,L_WNH_MEN,L_WNH_WMEN,L_UR_MEN,L_UR_WMEN;
2662 CLOSE GET_LINE2_COUNTS;
2663 L_TOT_MEN := L_NR_MEN + L_BNH_MEN + L_AMAI_MEN + L_AP_MEN + L_H_MEN + L_WNH_MEN + L_UR_MEN;
2664 L_TOT_WMEN := L_NR_WMEN + L_BNH_WMEN + L_AMAI_WMEN + L_AP_WMEN + L_H_WMEN + L_WNH_WMEN + L_UR_WMEN;
2665 OPEN GET_TMR_LINE2_COUNTS;
2666 FETCH GET_TMR_LINE2_COUNTS
2667 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;
2668 CLOSE GET_TMR_LINE2_COUNTS;
2669 L_BNH_MEN := L_BNH_MEN + L_TMR_BNH_MEN;
2670 L_BNH_WMEN := L_BNH_WMEN + L_TMR_BNH_WMEN;
2671 L_AMAI_MEN := L_AMAI_MEN + L_TMR_AMAI_MEN;
2672 L_AMAI_WMEN := L_AMAI_WMEN + L_TMR_AMAI_WMEN;
2673 L_AP_MEN := L_AP_MEN + L_TMR_AP_MEN;
2674 L_AP_WMEN := L_AP_WMEN + L_TMR_AP_WMEN;
2675 L_H_MEN := L_H_MEN + L_TMR_H_MEN;
2676 L_H_WMEN := L_H_WMEN + L_TMR_H_WMEN;
2677 L_WNH_MEN := L_WNH_MEN + L_TMR_WNH_MEN;
2678 L_WNH_WMEN := L_WNH_WMEN + L_TMR_WNH_WMEN;
2679 L_UR_MEN := L_UR_MEN + L_TMR_UR_MEN;
2680 L_UR_WMEN := L_UR_WMEN + L_TMR_UR_WMEN;
2681 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;
2682 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;
2683 INSERT INTO PAY_US_RPT_TOTALS
2684 (SESSION_ID
2685 ,ATTRIBUTE1
2686 ,VALUE1
2687 ,VALUE2
2688 ,VALUE3
2689 ,VALUE4
2690 ,VALUE5
2691 ,VALUE6
2692 ,VALUE7
2693 ,VALUE8
2694 ,VALUE9
2695 ,VALUE10
2696 ,VALUE11
2697 ,VALUE12
2698 ,VALUE13
2699 ,VALUE14
2700 ,VALUE15
2701 ,VALUE16
2702 ,VALUE17
2703 ,VALUE18)
2704 VALUES (USERENV('sessionid')
2705 ,'IPED8'
2706 ,LINE
2707 ,SC
2708 ,L_NR_MEN
2709 ,L_NR_WMEN
2710 ,L_BNH_MEN
2711 ,L_BNH_WMEN
2712 ,L_AMAI_MEN
2713 ,L_AMAI_WMEN
2714 ,L_AP_MEN
2715 ,L_AP_WMEN
2716 ,L_H_MEN
2717 ,L_H_WMEN
2718 ,L_WNH_MEN
2719 ,L_WNH_WMEN
2720 ,L_UR_MEN
2721 ,L_UR_WMEN
2722 ,L_TOT_MEN
2723 ,L_TOT_WMEN);
2724 COMMIT;
2725 FOR i IN GET_LINE3_COUNTS LOOP
2726 LINE := I.TENINFO;
2727 SC := I.ARANK;
2728 L_NR_MEN := I.NRMEN;
2729 L_NR_WMEN := I.NRWMEN;
2730 L_BNH_MEN := I.BNHMEN;
2731 L_BNH_WMEN := I.BNHWMEN;
2732 L_AMAI_MEN := I.AM_ALMEN;
2733 L_AMAI_WMEN := I.AM_ALWMEN;
2734 L_AP_MEN := I.APMEN;
2735 L_AP_WMEN := I.APWMEN;
2736 L_H_MEN := I.HMEN;
2737 L_H_WMEN := I.HWMEN;
2738 L_WNH_MEN := I.WNHMEN;
2739 L_WNH_WMEN := I.WNHWMEN;
2740 L_UR_MEN := I.URMEN;
2741 L_UR_WMEN := I.URWMEN;
2742 L_TOT_MEN := L_NR_MEN + L_BNH_MEN + L_AMAI_MEN + L_AP_MEN + L_H_MEN + L_WNH_MEN + L_UR_MEN;
2743 L_TOT_WMEN := L_NR_WMEN + L_BNH_WMEN + L_AMAI_WMEN + L_AP_WMEN + L_H_WMEN + L_WNH_WMEN + L_UR_WMEN;
2744 FOR j IN GET_LINE3_TMR_COUNTS(sc) LOOP
2745 L_BNH_MEN := L_BNH_MEN + J.BNHMEN;
2746 L_BNH_WMEN := L_BNH_WMEN + J.BNHWMEN;
2747 L_AMAI_MEN := L_AMAI_MEN + J.AM_ALMEN;
2748 L_AMAI_WMEN := L_AMAI_WMEN + J.AM_ALWMEN;
2749 L_AP_MEN := L_AP_MEN + J.A_PMEN;
2750 L_AP_WMEN := L_AP_WMEN + J.A_PWMEN;
2751 L_H_MEN := L_H_MEN + J.HMEN;
2752 L_H_WMEN := L_H_WMEN + J.HWMEN;
2753 L_WNH_MEN := L_WNH_MEN + J.WNHMEN;
2754 L_WNH_WMEN := L_WNH_WMEN + J.WNHWMEN;
2755 L_UR_MEN := L_UR_MEN + J.URMEN;
2756 L_UR_WMEN := L_UR_WMEN + J.URWMEN;
2757 L_TOT_MEN := L_TOT_MEN + J.BNHMEN + J.AM_ALMEN + J.A_PMEN + J.HMEN + J.WNHMEN + J.URMEN;
2758 L_TOT_WMEN := L_TOT_WMEN + J.BNHWMEN + J.AM_ALWMEN + J.A_PWMEN + J.HWMEN + J.WNHWMEN + J.URWMEN;
2759 END LOOP;
2760 INSERT INTO PAY_US_RPT_TOTALS
2761 (SESSION_ID
2762 ,ATTRIBUTE1
2763 ,VALUE1
2764 ,VALUE2
2765 ,VALUE3
2766 ,VALUE4
2767 ,VALUE5
2768 ,VALUE6
2769 ,VALUE7
2770 ,VALUE8
2771 ,VALUE9
2772 ,VALUE10
2773 ,VALUE11
2774 ,VALUE12
2775 ,VALUE13
2776 ,VALUE14
2777 ,VALUE15
2778 ,VALUE16
2779 ,VALUE17
2780 ,VALUE18)
2781 VALUES (USERENV('sessionid')
2782 ,'IPED8'
2783 ,LINE
2784 ,SC
2785 ,L_NR_MEN
2786 ,L_NR_WMEN
2787 ,L_BNH_MEN
2788 ,L_BNH_WMEN
2789 ,L_AMAI_MEN
2790 ,L_AMAI_WMEN
2791 ,L_AP_MEN
2792 ,L_AP_WMEN
2793 ,L_H_MEN
2794 ,L_H_WMEN
2795 ,L_WNH_MEN
2796 ,L_WNH_WMEN
2797 ,L_UR_MEN
2798 ,L_UR_WMEN
2799 ,L_TOT_MEN
2800 ,L_TOT_WMEN);
2801 COMMIT;
2802 END LOOP;
2803 OPEN GET_LINE4_COUNTS;
2804 FETCH GET_LINE4_COUNTS
2805 INTO LINE,SC,L_NR_MEN,L_NR_WMEN,L_BNH_MEN,L_BNH_WMEN,L_AMAI_MEN,L_AMAI_WMEN,L_AP_MEN,L_AP_WMEN,L_H_MEN,L_H_WMEN,L_WNH_MEN,L_WNH_WMEN,L_UR_MEN,L_UR_WMEN;
2806 CLOSE GET_LINE4_COUNTS;
2807 L_TOT_MEN := L_NR_MEN + L_BNH_MEN + L_AMAI_MEN + L_AP_MEN + L_H_MEN + L_WNH_MEN + L_UR_MEN;
2808 L_TOT_WMEN := L_NR_WMEN + L_BNH_WMEN + L_AMAI_WMEN + L_AP_WMEN + L_H_WMEN + L_WNH_WMEN + L_UR_WMEN;
2809 OPEN GET_LINE4_TMR_COUNTS;
2810 FETCH GET_LINE4_TMR_COUNTS
2811 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;
2812 CLOSE GET_LINE4_TMR_COUNTS;
2813 L_BNH_MEN := L_BNH_MEN + L_TMR_BNH_MEN;
2814 L_BNH_WMEN := L_BNH_WMEN + L_TMR_BNH_WMEN;
2815 L_AMAI_MEN := L_AMAI_MEN + L_TMR_AMAI_MEN;
2816 L_AMAI_WMEN := L_AMAI_WMEN + L_TMR_AMAI_WMEN;
2817 L_AP_MEN := L_AP_MEN + L_TMR_AP_MEN;
2818 L_AP_WMEN := L_AP_WMEN + L_TMR_AP_WMEN;
2819 L_H_MEN := L_H_MEN + L_TMR_H_MEN;
2820 L_H_WMEN := L_H_WMEN + L_TMR_H_WMEN;
2821 L_WNH_MEN := L_WNH_MEN + L_TMR_WNH_MEN;
2822 L_WNH_WMEN := L_WNH_WMEN + L_TMR_WNH_WMEN;
2823 L_UR_MEN := L_UR_MEN + L_TMR_UR_MEN;
2824 L_UR_WMEN := L_UR_WMEN + L_TMR_UR_WMEN;
2825 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;
2826 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;
2827 INSERT INTO PAY_US_RPT_TOTALS
2828 (SESSION_ID
2829 ,ATTRIBUTE1
2830 ,VALUE1
2831 ,VALUE2
2832 ,VALUE3
2833 ,VALUE4
2834 ,VALUE5
2835 ,VALUE6
2836 ,VALUE7
2837 ,VALUE8
2838 ,VALUE9
2839 ,VALUE10
2840 ,VALUE11
2841 ,VALUE12
2842 ,VALUE13
2843 ,VALUE14
2844 ,VALUE15
2845 ,VALUE16
2846 ,VALUE17
2847 ,VALUE18)
2848 VALUES (USERENV('sessionid')
2849 ,'IPED8'
2850 ,LINE
2851 ,SC
2852 ,L_NR_MEN
2853 ,L_NR_WMEN
2854 ,L_BNH_MEN
2855 ,L_BNH_WMEN
2856 ,L_AMAI_MEN
2857 ,L_AMAI_WMEN
2858 ,L_AP_MEN
2859 ,L_AP_WMEN
2860 ,L_H_MEN
2861 ,L_H_WMEN
2862 ,L_WNH_MEN
2863 ,L_WNH_WMEN
2864 ,L_UR_MEN
2865 ,L_UR_WMEN
2866 ,L_TOT_MEN
2867 ,L_TOT_WMEN);
2868 COMMIT;
2869 FOR i IN GET_LINE5_COUNTS LOOP
2870 LINE := I.TENINFO;
2871 SC := I.ARANK;
2872 L_NR_MEN := I.NRMEN;
2873 L_NR_WMEN := I.NRWMEN;
2874 L_BNH_MEN := I.BNHMEN;
2875 L_BNH_WMEN := I.BNHWMEN;
2876 L_AMAI_MEN := I.AM_ALMEN;
2877 L_AMAI_WMEN := I.AM_ALWMEN;
2878 L_AP_MEN := I.APMEN;
2879 L_AP_WMEN := I.APWMEN;
2880 L_H_MEN := I.HMEN;
2881 L_H_WMEN := I.HWMEN;
2882 L_WNH_MEN := I.WNHMEN;
2883 L_WNH_WMEN := I.WNHWMEN;
2884 L_UR_MEN := I.URMEN;
2885 L_UR_WMEN := I.URWMEN;
2886 L_TOT_MEN := L_NR_MEN + L_BNH_MEN + L_AMAI_MEN + L_AP_MEN + L_H_MEN + L_WNH_MEN + L_UR_MEN;
2887 L_TOT_WMEN := L_NR_WMEN + L_BNH_WMEN + L_AMAI_WMEN + L_AP_WMEN + L_H_WMEN + L_WNH_WMEN + L_UR_WMEN;
2888 FOR j IN GET_LINE5_TMR_COUNTS(sc) LOOP
2889 L_BNH_MEN := L_BNH_MEN + J.BNHMEN;
2890 L_BNH_WMEN := L_BNH_WMEN + J.BNHWMEN;
2891 L_AMAI_MEN := L_AMAI_MEN + J.AM_ALMEN;
2892 L_AMAI_WMEN := L_AMAI_WMEN + J.AM_ALWMEN;
2893 L_AP_MEN := L_AP_MEN + J.A_PMEN;
2894 L_AP_WMEN := L_AP_WMEN + J.A_PWMEN;
2895 L_H_MEN := L_H_MEN + J.HMEN;
2896 L_H_WMEN := L_H_WMEN + J.HWMEN;
2897 L_WNH_MEN := L_WNH_MEN + J.WNHMEN;
2898 L_WNH_WMEN := L_WNH_WMEN + J.WNHWMEN;
2899 L_UR_MEN := L_UR_MEN + J.URMEN;
2900 L_UR_WMEN := L_UR_WMEN + J.URWMEN;
2901 L_TOT_MEN := L_TOT_MEN + J.BNHMEN + J.AM_ALMEN + J.A_PMEN + J.HMEN + J.WNHMEN + J.URMEN;
2902 L_TOT_WMEN := L_TOT_WMEN + J.BNHWMEN + J.AM_ALWMEN + J.A_PWMEN + J.HWMEN + J.WNHWMEN + J.URWMEN;
2903 END LOOP;
2904 INSERT INTO PAY_US_RPT_TOTALS
2905 (SESSION_ID
2906 ,ATTRIBUTE1
2907 ,VALUE1
2908 ,VALUE2
2909 ,VALUE3
2910 ,VALUE4
2911 ,VALUE5
2912 ,VALUE6
2913 ,VALUE7
2914 ,VALUE8
2915 ,VALUE9
2916 ,VALUE10
2917 ,VALUE11
2918 ,VALUE12
2919 ,VALUE13
2920 ,VALUE14
2921 ,VALUE15
2922 ,VALUE16
2923 ,VALUE17
2924 ,VALUE18)
2925 VALUES (USERENV('sessionid')
2926 ,'IPED8'
2927 ,LINE
2928 ,SC
2929 ,L_NR_MEN
2930 ,L_NR_WMEN
2931 ,L_BNH_MEN
2932 ,L_BNH_WMEN
2933 ,L_AMAI_MEN
2934 ,L_AMAI_WMEN
2935 ,L_AP_MEN
2936 ,L_AP_WMEN
2937 ,L_H_MEN
2938 ,L_H_WMEN
2939 ,L_WNH_MEN
2940 ,L_WNH_WMEN
2941 ,L_UR_MEN
2942 ,L_UR_WMEN
2943 ,L_TOT_MEN
2944 ,L_TOT_WMEN);
2945 COMMIT;
2946 END LOOP;
2947 OPEN GET_LINE6_COUNTS;
2948 FETCH GET_LINE6_COUNTS
2949 INTO LINE,SC,L_NR_MEN,L_NR_WMEN,L_BNH_MEN,L_BNH_WMEN,L_AMAI_MEN,L_AMAI_WMEN,L_AP_MEN,L_AP_WMEN,L_H_MEN,L_H_WMEN,L_WNH_MEN,L_WNH_WMEN,L_UR_MEN,L_UR_WMEN;
2950 CLOSE GET_LINE6_COUNTS;
2951 L_TOT_MEN := L_NR_MEN + L_BNH_MEN + L_AMAI_MEN + L_AP_MEN + L_H_MEN + L_WNH_MEN + L_UR_MEN;
2952 L_TOT_WMEN := L_NR_WMEN + L_BNH_WMEN + L_AMAI_WMEN + L_AP_WMEN + L_H_WMEN + L_WNH_WMEN + L_UR_WMEN;
2953 OPEN GET_LINE6_TMR_COUNTS;
2954 FETCH GET_LINE6_TMR_COUNTS
2955 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;
2956 CLOSE GET_LINE6_TMR_COUNTS;
2957 L_BNH_MEN := L_BNH_MEN + L_TMR_BNH_MEN;
2958 L_BNH_WMEN := L_BNH_WMEN + L_TMR_BNH_WMEN;
2959 L_AMAI_MEN := L_AMAI_MEN + L_TMR_AMAI_MEN;
2960 L_AMAI_WMEN := L_AMAI_WMEN + L_TMR_AMAI_WMEN;
2961 L_AP_MEN := L_AP_MEN + L_TMR_AP_MEN;
2962 L_AP_WMEN := L_AP_WMEN + L_TMR_AP_WMEN;
2963 L_H_MEN := L_H_MEN + L_TMR_H_MEN;
2964 L_H_WMEN := L_H_WMEN + L_TMR_H_WMEN;
2965 L_WNH_MEN := L_WNH_MEN + L_TMR_WNH_MEN;
2966 L_WNH_WMEN := L_WNH_WMEN + L_TMR_WNH_WMEN;
2967 L_UR_MEN := L_UR_MEN + L_TMR_UR_MEN;
2968 L_UR_WMEN := L_UR_WMEN + L_TMR_UR_WMEN;
2969 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;
2970 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;
2971 INSERT INTO PAY_US_RPT_TOTALS
2972 (SESSION_ID
2973 ,ATTRIBUTE1
2974 ,VALUE1
2975 ,VALUE2
2976 ,VALUE3
2977 ,VALUE4
2978 ,VALUE5
2979 ,VALUE6
2980 ,VALUE7
2981 ,VALUE8
2982 ,VALUE9
2983 ,VALUE10
2984 ,VALUE11
2985 ,VALUE12
2986 ,VALUE13
2987 ,VALUE14
2988 ,VALUE15
2989 ,VALUE16
2990 ,VALUE17
2991 ,VALUE18)
2992 VALUES (USERENV('sessionid')
2993 ,'IPED8'
2994 ,LINE
2995 ,SC
2996 ,L_NR_MEN
2997 ,L_NR_WMEN
2998 ,L_BNH_MEN
2999 ,L_BNH_WMEN
3000 ,L_AMAI_MEN
3001 ,L_AMAI_WMEN
3002 ,L_AP_MEN
3003 ,L_AP_WMEN
3004 ,L_H_MEN
3005 ,L_H_WMEN
3006 ,L_WNH_MEN
3007 ,L_WNH_WMEN
3008 ,L_UR_MEN
3009 ,L_UR_WMEN
3010 ,L_TOT_MEN
3011 ,L_TOT_WMEN);
3012 COMMIT;
3013
3014 return true;
3015 end;
3016
3017 function CF_1Formula return Number is
3018 temp_num number;
3019 begin
3020 temp_num := line_num;
3021 line_num := line_num + 1;
3022 if line_num = 84 then
3023 line_num := 85;
3024 elsif line_num = 91 then
3025 line_num := 92;
3026 end if;
3027 return temp_num;
3028 end;
3029
3030 function cf_grouplinenumformula(TenStat in varchar2) return number is
3031 temp_num number;
3032 p_contr_code varchar2(10) := TenStat;
3033 begin
3034 if p_contr_code = '01' then
3035 temp_num := 84;
3036 elsif p_contr_code = '02' then
3037 temp_num := 91;
3038 elsif p_contr_code = '03' then
3039 temp_num := 98;
3040 end if;
3041 return temp_num;
3042 end;
3043
3044 function CF_GroupTotTitleFormula(TenStat in varchar2) return Char is
3045 l_total_title VARCHAR2(200) := '';
3046 l_contr_code VARCHAR2(9) := TenStat;
3047 begin
3048 IF l_contr_code = '01' THEN
3049 l_total_title := 'Total Faculty with Tenure (sum of lines 78-83)';
3050 ELSIF l_contr_code = '02' THEN
3051 l_total_title := 'Total Non-Tenured Faculty (Those on tenure track) sum of lines 85-90';
3052 ELSIF l_contr_code = '03' THEN
3053 l_total_title := 'Total Non-Tenured Faculty (Those not on tenure track) sum of lines 92-97';
3054 END IF;
3055 return l_total_title;
3056 end;
3057
3058 function AfterReport return boolean is
3059 begin
3060 --hr_standard.event('AFTER REPORT');
3061 EXECUTE IMMEDIATE
3062 'DELETE FROM pay_us_rpt_totals
3063 WHERE attribute1 = ''IPED8''';
3064
3065 return (TRUE);
3066 end;
3067
3068 --Functions to refer Oracle report placeholders--
3069
3070 Function CP_FR_p return varchar2 is
3071 Begin
3072 return CP_FR;
3073 END;
3074 Function CP_FT_p return varchar2 is
3075 Begin
3076 return CP_FT;
3077 END;
3078 Function CP_PR_p return varchar2 is
3079 Begin
3080 return CP_PR;
3081 END;
3082 Function CP_PT_p return varchar2 is
3083 Begin
3084 return CP_PT;
3085 END;
3086 Function line_num_p return number is
3087 Begin
3088 return line_num;
3089 END;
3090 Function CP_LineNumRepTot_p return number is
3091 Begin
3092 return CP_LineNumRepTot;
3093 END;
3094 Function CP_RepTotTitle_p return varchar2 is
3095 Begin
3096 return CP_RepTotTitle;
3097 END;
3098 END PQH_PQIPED8_XMLP_PKG ;