[Home] [Help]
PACKAGE BODY: APPS.PQH_PQIPED9_XMLP_PKG
Source
1 PACKAGE BODY PQH_PQIPED9_XMLP_PKG AS
2 /* $Header: PQIPED9B.pls 120.3 2008/03/28 12:13:39 amakrish noship $ */
3 FUNCTION LINEFORMULA RETURN NUMBER IS
4 TEMP_NUM NUMBER;
5 BEGIN
6 TEMP_NUM := LINE_NUM;
7 LINE_NUM := LINE_NUM + 1;
8 RETURN TEMP_NUM;
9 END LINEFORMULA;
10
11 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
12 L_QUERY_TEXT VARCHAR2(2000);
13 L_FR VARCHAR2(2000);
14 L_FT VARCHAR2(2000);
15 L_PR VARCHAR2(2000);
16 L_PT VARCHAR2(2000);
17 L_YEAR VARCHAR2(4);
18 L_MONTH VARCHAR2(3);
19 L_DAY VARCHAR2(2);
20 L_YEAR_AGO VARCHAR2(4);
21 LINE VARCHAR2(1);
22 SC VARCHAR2(2000);
23 L_NR_MEN NUMBER(10) := 0;
24 L_NR_WMEN NUMBER(10) := 0;
25 L_BNH_MEN NUMBER(10) := 0;
26 L_BNH_WMEN NUMBER(10) := 0;
27 L_AMAI_MEN NUMBER(10) := 0;
28 L_AMAI_WMEN NUMBER(10) := 0;
29 L_AP_MEN NUMBER(10) := 0;
30 L_AP_WMEN NUMBER(10) := 0;
31 L_H_MEN NUMBER(10) := 0;
32 L_H_WMEN NUMBER(10) := 0;
33 L_WNH_MEN NUMBER(10) := 0;
34 L_WNH_WMEN NUMBER(10) := 0;
35 L_UR_MEN NUMBER(10) := 0;
36 L_UR_WMEN NUMBER(10) := 0;
37 L_TOT_MEN NUMBER(10) := 0;
38 L_TOT_WMEN NUMBER(10) := 0;
39 L_TMR_BNH_MEN NUMBER(10) := 0;
40 L_TMR_BNH_WMEN NUMBER(10) := 0;
41 L_TMR_AMAI_MEN NUMBER(10) := 0;
42 L_TMR_AMAI_WMEN NUMBER(10) := 0;
43 L_TMR_AP_MEN NUMBER(10) := 0;
44 L_TMR_AP_WMEN NUMBER(10) := 0;
45 L_TMR_H_MEN NUMBER(10) := 0;
46 L_TMR_H_WMEN NUMBER(10) := 0;
47 L_TMR_WNH_MEN NUMBER(10) := 0;
48 L_TMR_WNH_WMEN NUMBER(10) := 0;
49 L_TMR_UR_MEN NUMBER(10) := 0;
50 L_TMR_UR_WMEN NUMBER(10) := 0;
51 CURSOR GET_LINE1_COUNTS IS
52 SELECT
53 '1' JCODE,
54 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
55 ,P_REPORT_DATE)
56 ,'1'
57 ,DECODE(PEO.SEX
58 ,'M'
59 ,1
60 ,NULL)
61 ,NULL)) NRMEN,
62 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
63 ,P_REPORT_DATE)
64 ,'1'
65 ,DECODE(PEO.SEX
66 ,'F'
67 ,1
68 ,NULL)
69 ,NULL)) NRWMEN,
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 ,'M'
77 ,1
78 ,NULL)
79 ,NULL)))) BNHMEN,
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 ,'2'
85 ,DECODE(PEO.SEX
86 ,'F'
87 ,1
88 ,NULL)
89 ,NULL)))) BNHWMEN,
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 ,'M'
97 ,1
98 ,NULL)
99 ,NULL)))) AM_ALMEN,
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 ,'6'
105 ,DECODE(PEO.SEX
106 ,'F'
107 ,1
108 ,NULL)
109 ,NULL)))) AM_ALWMEN,
110 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
111 ,P_REPORT_DATE)
112 ,NULL
113 ,(DECODE(PEO.PER_INFORMATION1
114 ,'4'
115 ,DECODE(PEO.SEX
116 ,'M'
117 ,1
118 ,NULL)
119 ,'5'
120 ,DECODE(PEO.SEX
121 ,'M'
122 ,1
123 ,NULL)
124 ,NULL)))) APMEN,
125 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
126 ,P_REPORT_DATE)
127 ,NULL
128 ,(DECODE(PEO.PER_INFORMATION1
129 ,'4'
130 ,DECODE(PEO.SEX
131 ,'F'
132 ,1
133 ,NULL)
134 ,'5'
135 ,DECODE(PEO.SEX
136 ,'F'
137 ,1
138 ,NULL)
139 ,NULL)))) APWMEN,
140 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
141 ,P_REPORT_DATE)
142 ,NULL
143 ,(DECODE(PEO.PER_INFORMATION1
144 ,'3'
145 ,DECODE(PEO.SEX
146 ,'M'
147 ,1
148 ,NULL)
149 ,'9'
150 ,DECODE(PEO.SEX
151 ,'M'
152 ,1
153 ,NULL)
154 ,NULL)))) HMEN,
155 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
156 ,P_REPORT_DATE)
157 ,NULL
158 ,(DECODE(PEO.PER_INFORMATION1
159 ,'3'
160 ,DECODE(PEO.SEX
161 ,'F'
162 ,1
163 ,NULL)
164 ,'9'
165 ,DECODE(PEO.SEX
166 ,'F'
167 ,1
168 ,NULL)
169 ,NULL)))) HWMEN,
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 ,'M'
177 ,1
178 ,NULL)
179 ,NULL)))) WNHMEN,
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 ,'1'
185 ,DECODE(PEO.SEX
186 ,'F'
187 ,1
188 ,NULL)
189 ,NULL)))) WNHWMEN,
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 ,'M'
197 ,1
198 ,NULL)
199 ,NULL)))) URMEN,
200 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
201 ,P_REPORT_DATE)
202 ,NULL
203 ,(DECODE(PEO.PER_INFORMATION1
204 ,NULL
205 ,DECODE(PEO.SEX
206 ,'F'
207 ,1
208 ,NULL)
209 ,NULL)))) URWMEN
210 FROM
211 PER_ALL_PEOPLE_F PEO,
212 PER_ALL_ASSIGNMENTS_F PAF,
213 PER_ASSIGNMENT_STATUS_TYPES AST,
214 PER_PEOPLE_EXTRA_INFO PPET,
215 PER_JOBS JOB,
216 HR_LOOKUPS HL
217 WHERE PAF.PERSON_ID = PPET.PERSON_ID
218 AND PAF.PERSON_ID = PEO.PERSON_ID
219 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
220 AND PPET.PEI_INFORMATION_CATEGORY = 'PQH_TENURE_STATUS'
221 AND PQH_EMPLOYMENT_CATEGORY.GET_SERVICE_START_DATE(PAF.PERIOD_OF_SERVICE_ID) BETWEEN CP_HIRE_DATE
222 AND P_REPORT_DATE
223 AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
224 AND PPET.PEI_INFORMATION1 = '01'
225 AND PPET.PEI_INFORMATION1 IS NOT NULL
226 AND PAF.PRIMARY_FLAG = 'Y'
227 AND PAF.ASSIGNMENT_TYPE = 'E'
228 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
229 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
230 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
231 ,CP_FR
232 ,CP_FT
233 ,CP_PR
234 ,CP_PT) = 'FR'
235 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
236 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
237 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
238 AND HL.LOOKUP_CODE in ( '1' , '2' , '3' )
239 AND PAF.JOB_ID = JOB.JOB_ID
240 AND PAF.ORGANIZATION_ID IN (
241 SELECT
242 ORGANIZATION_ID
243 FROM
244 HR_ALL_ORGANIZATION_UNITS
245 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
246 GROUP BY
247 '1';
248 CURSOR GET_LINE1_TMRACES_COUNTS IS
249 SELECT
250 COUNT(DECODE(PEI.PEI_INFORMATION5
251 ,'2'
252 ,DECODE(PEO.SEX
253 ,'M'
254 ,1
255 ,NULL)
256 ,NULL)) BNHMEN,
257 COUNT(DECODE(PEI.PEI_INFORMATION5
258 ,'2'
259 ,DECODE(PEO.SEX
260 ,'F'
261 ,1
262 ,NULL)
263 ,NULL)) BNHWMEN,
264 COUNT(DECODE(PEI.PEI_INFORMATION5
265 ,'6'
266 ,DECODE(PEO.SEX
267 ,'M'
268 ,1
269 ,NULL)
270 ,NULL)) AM_ALMEN,
271 COUNT(DECODE(PEI.PEI_INFORMATION5
272 ,'6'
273 ,DECODE(PEO.SEX
274 ,'F'
275 ,1
276 ,NULL)
277 ,NULL)) AM_ALWMEN,
278 COUNT(DECODE(PEI.PEI_INFORMATION5
279 ,'4'
280 ,DECODE(PEO.SEX
281 ,'M'
282 ,1
283 ,NULL)
284 ,'5'
285 ,DECODE(PEO.SEX
286 ,'M'
287 ,1
288 ,NULL)
289 ,NULL)) A_PMEN,
290 COUNT(DECODE(PEI.PEI_INFORMATION5
291 ,'4'
292 ,DECODE(PEO.SEX
293 ,'F'
294 ,1
295 ,NULL)
296 ,'5'
297 ,DECODE(PEO.SEX
298 ,'F'
299 ,1
300 ,NULL)
301 ,NULL)) A_PWMEN,
302 COUNT(DECODE(PEI.PEI_INFORMATION5
303 ,'3'
304 ,DECODE(PEO.SEX
305 ,'M'
306 ,1
307 ,NULL)
308 ,'9'
309 ,DECODE(PEO.SEX
310 ,'M'
311 ,1
312 ,NULL)
313 ,NULL)) HMEN,
314 COUNT(DECODE(PEI.PEI_INFORMATION5
315 ,'3'
316 ,DECODE(PEO.SEX
317 ,'F'
318 ,1
319 ,NULL)
320 ,'9'
321 ,DECODE(PEO.SEX
322 ,'F'
323 ,1
324 ,NULL)
325 ,NULL)) HWMEN,
326 COUNT(DECODE(PEI.PEI_INFORMATION5
327 ,'1'
328 ,DECODE(PEO.SEX
329 ,'M'
330 ,1
331 ,NULL)
332 ,NULL)) WNHMEN,
333 COUNT(DECODE(PEI.PEI_INFORMATION5
334 ,'1'
335 ,DECODE(PEO.SEX
336 ,'F'
337 ,1
338 ,NULL)
339 ,NULL)) WNHWMEN,
340 COUNT(DECODE(PEI.PEI_INFORMATION5
341 ,NULL
342 ,DECODE(PEO.SEX
343 ,'M'
344 ,1
345 ,NULL)
346 ,NULL)) URMEN,
347 COUNT(DECODE(PEI.PEI_INFORMATION5
348 ,NULL
349 ,DECODE(PEO.SEX
350 ,'F'
351 ,1
352 ,NULL)
353 ,NULL)) URWMEN
354 FROM
355 PER_ALL_PEOPLE_F PEO,
356 PER_ALL_ASSIGNMENTS_F PAF,
357 PER_ASSIGNMENT_STATUS_TYPES AST,
358 PER_PEOPLE_EXTRA_INFO PPET,
359 PER_JOBS JOB,
360 HR_LOOKUPS HL,
361 PER_PEOPLE_EXTRA_INFO PEI
362 WHERE PAF.PERSON_ID = PPET.PERSON_ID
363 AND PAF.PERSON_ID = PEO.PERSON_ID
364 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
365 AND PEO.PER_INFORMATION1 = '13'
366 AND PEO.PERSON_ID = pei.person_id (+)
367 AND ( PEI.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
368 OR ( PEI.INFORMATION_TYPE <> 'PER_US_ADDL_ETHNIC_CAT'
369 AND not exists (
370 SELECT
371 1
372 FROM
373 PER_PEOPLE_EXTRA_INFO PEI2
374 WHERE PEI2.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
375 AND PEI2.PERSON_ID = PEI.PERSON_ID )
376 AND PEI.PERSON_EXTRA_INFO_ID = (
377 SELECT
378 MAX(PEI1.PERSON_EXTRA_INFO_ID)
379 FROM
380 PER_PEOPLE_EXTRA_INFO PEI1
381 WHERE PEI1.PERSON_ID = PEI.PERSON_ID ) )
382 OR ( not exists (
383 SELECT
384 PERSON_EXTRA_INFO_ID
385 FROM
386 PER_PEOPLE_EXTRA_INFO PEI3
387 WHERE PEI3.PERSON_ID = PEI.PERSON_ID ) ) )
388 AND PPET.PEI_INFORMATION_CATEGORY = 'PQH_TENURE_STATUS'
389 AND PQH_EMPLOYMENT_CATEGORY.GET_SERVICE_START_DATE(PAF.PERIOD_OF_SERVICE_ID) BETWEEN CP_HIRE_DATE
390 AND P_REPORT_DATE
391 AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
392 AND PPET.PEI_INFORMATION1 = '01'
393 AND PPET.PEI_INFORMATION1 IS NOT NULL
394 AND PAF.PRIMARY_FLAG = 'Y'
395 AND PAF.ASSIGNMENT_TYPE = 'E'
396 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
397 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
398 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
399 ,CP_FR
400 ,CP_FT
401 ,CP_PR
402 ,CP_PT) = 'FR'
403 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
404 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
405 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
406 AND HL.LOOKUP_CODE in ( '1' , '2' , '3' )
407 AND PAF.JOB_ID = JOB.JOB_ID
408 AND PAF.ORGANIZATION_ID IN (
409 SELECT
410 ORGANIZATION_ID
411 FROM
412 HR_ALL_ORGANIZATION_UNITS
413 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID );
414 CURSOR GET_LINE2_COUNTS IS
415 SELECT
416 '2' JCODE,
417 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
418 ,P_REPORT_DATE)
419 ,'1'
420 ,DECODE(PEO.SEX
421 ,'M'
422 ,1
423 ,NULL)
424 ,NULL)) NRMEN,
425 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
426 ,P_REPORT_DATE)
427 ,'1'
428 ,DECODE(PEO.SEX
429 ,'F'
430 ,1
431 ,NULL)
432 ,NULL)) NRWMEN,
433 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
434 ,P_REPORT_DATE)
435 ,NULL
436 ,(DECODE(PEO.PER_INFORMATION1
437 ,'2'
438 ,DECODE(PEO.SEX
439 ,'M'
440 ,1
441 ,NULL)
442 ,NULL)))) BNHMEN,
443 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
444 ,P_REPORT_DATE)
445 ,NULL
446 ,(DECODE(PEO.PER_INFORMATION1
447 ,'2'
448 ,DECODE(PEO.SEX
449 ,'F'
450 ,1
451 ,NULL)
452 ,NULL)))) BNHWMEN,
453 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
454 ,P_REPORT_DATE)
455 ,NULL
456 ,(DECODE(PEO.PER_INFORMATION1
457 ,'6'
458 ,DECODE(PEO.SEX
459 ,'M'
460 ,1
461 ,NULL)
462 ,NULL)))) AM_ALMEN,
463 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
464 ,P_REPORT_DATE)
465 ,NULL
466 ,(DECODE(PEO.PER_INFORMATION1
467 ,'6'
468 ,DECODE(PEO.SEX
469 ,'F'
470 ,1
471 ,NULL)
472 ,NULL)))) AM_ALWMEN,
473 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
474 ,P_REPORT_DATE)
475 ,NULL
476 ,(DECODE(PEO.PER_INFORMATION1
477 ,'4'
478 ,DECODE(PEO.SEX
479 ,'M'
480 ,1
481 ,NULL)
482 ,'5'
483 ,DECODE(PEO.SEX
484 ,'M'
485 ,1
486 ,NULL)
487 ,NULL)))) APMEN,
488 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
489 ,P_REPORT_DATE)
490 ,NULL
491 ,(DECODE(PEO.PER_INFORMATION1
492 ,'4'
493 ,DECODE(PEO.SEX
494 ,'F'
495 ,1
496 ,NULL)
497 ,'5'
498 ,DECODE(PEO.SEX
499 ,'F'
500 ,1
501 ,NULL)
502 ,NULL)))) APWMEN,
503 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
504 ,P_REPORT_DATE)
505 ,NULL
506 ,(DECODE(PEO.PER_INFORMATION1
507 ,'3'
508 ,DECODE(PEO.SEX
509 ,'M'
510 ,1
511 ,NULL)
512 ,'9'
513 ,DECODE(PEO.SEX
514 ,'M'
515 ,1
516 ,NULL)
517 ,NULL)))) HMEN,
518 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
519 ,P_REPORT_DATE)
520 ,NULL
521 ,(DECODE(PEO.PER_INFORMATION1
522 ,'3'
523 ,DECODE(PEO.SEX
524 ,'F'
525 ,1
526 ,NULL)
527 ,'9'
528 ,DECODE(PEO.SEX
529 ,'F'
530 ,1
531 ,NULL)
532 ,NULL)))) HWMEN,
533 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
534 ,P_REPORT_DATE)
535 ,NULL
536 ,(DECODE(PEO.PER_INFORMATION1
537 ,'1'
538 ,DECODE(PEO.SEX
539 ,'M'
540 ,1
541 ,NULL)
542 ,NULL)))) WNHMEN,
543 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
544 ,P_REPORT_DATE)
545 ,NULL
546 ,(DECODE(PEO.PER_INFORMATION1
547 ,'1'
548 ,DECODE(PEO.SEX
549 ,'F'
550 ,1
551 ,NULL)
552 ,NULL)))) WNHWMEN,
553 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
554 ,P_REPORT_DATE)
555 ,NULL
556 ,(DECODE(PEO.PER_INFORMATION1
557 ,NULL
558 ,DECODE(PEO.SEX
559 ,'M'
560 ,1
561 ,NULL)
562 ,NULL)))) URMEN,
563 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
564 ,P_REPORT_DATE)
565 ,NULL
566 ,(DECODE(PEO.PER_INFORMATION1
567 ,NULL
568 ,DECODE(PEO.SEX
569 ,'F'
570 ,1
571 ,NULL)
572 ,NULL)))) URWMEN
573 FROM
574 PER_ALL_PEOPLE_F PEO,
575 PER_ALL_ASSIGNMENTS_F PAF,
576 PER_ASSIGNMENT_STATUS_TYPES AST,
577 PER_PEOPLE_EXTRA_INFO PPET,
578 PER_JOBS JOB,
579 HR_LOOKUPS HL
580 WHERE PAF.PERSON_ID = PPET.PERSON_ID
581 AND PAF.PERSON_ID = PEO.PERSON_ID
582 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
583 AND PPET.PEI_INFORMATION_CATEGORY = 'PQH_TENURE_STATUS'
584 AND PQH_EMPLOYMENT_CATEGORY.GET_SERVICE_START_DATE(PAF.PERIOD_OF_SERVICE_ID) BETWEEN CP_HIRE_DATE
585 AND P_REPORT_DATE
586 AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
587 AND PPET.PEI_INFORMATION1 in ( '02' , '04' )
588 AND PPET.PEI_INFORMATION1 IS NOT NULL
589 AND PAF.PRIMARY_FLAG = 'Y'
590 AND PAF.ASSIGNMENT_TYPE = 'E'
591 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
592 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
593 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
594 ,CP_FR
595 ,CP_FT
596 ,CP_PR
597 ,CP_PT) = 'FR'
598 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
599 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
600 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
601 AND HL.LOOKUP_CODE in ( '1' , '2' , '3' )
602 AND PAF.JOB_ID = JOB.JOB_ID
603 AND PAF.ORGANIZATION_ID IN (
604 SELECT
605 ORGANIZATION_ID
606 FROM
607 HR_ALL_ORGANIZATION_UNITS
608 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
609 GROUP BY
610 '2';
611 CURSOR GET_TMR_LINE2_COUNTS IS
612 SELECT
613 COUNT(DECODE(PEI.PEI_INFORMATION5
614 ,'2'
615 ,DECODE(PEO.SEX
616 ,'M'
617 ,1
618 ,NULL)
619 ,NULL)) BNHMEN,
620 COUNT(DECODE(PEI.PEI_INFORMATION5
621 ,'2'
622 ,DECODE(PEO.SEX
623 ,'F'
624 ,1
625 ,NULL)
626 ,NULL)) BNHWMEN,
627 COUNT(DECODE(PEI.PEI_INFORMATION5
628 ,'6'
629 ,DECODE(PEO.SEX
630 ,'M'
631 ,1
632 ,NULL)
633 ,NULL)) AM_ALMEN,
634 COUNT(DECODE(PEI.PEI_INFORMATION5
635 ,'6'
636 ,DECODE(PEO.SEX
637 ,'F'
638 ,1
639 ,NULL)
640 ,NULL)) AM_ALWMEN,
641 COUNT(DECODE(PEI.PEI_INFORMATION5
642 ,'4'
643 ,DECODE(PEO.SEX
644 ,'M'
645 ,1
646 ,NULL)
647 ,'5'
648 ,DECODE(PEO.SEX
649 ,'M'
650 ,1
651 ,NULL)
652 ,NULL)) A_PMEN,
653 COUNT(DECODE(PEI.PEI_INFORMATION5
654 ,'4'
655 ,DECODE(PEO.SEX
656 ,'F'
657 ,1
658 ,NULL)
659 ,'5'
660 ,DECODE(PEO.SEX
661 ,'F'
662 ,1
663 ,NULL)
664 ,NULL)) A_PWMEN,
665 COUNT(DECODE(PEI.PEI_INFORMATION5
666 ,'3'
667 ,DECODE(PEO.SEX
668 ,'M'
669 ,1
670 ,NULL)
671 ,'9'
672 ,DECODE(PEO.SEX
673 ,'M'
674 ,1
675 ,NULL)
676 ,NULL)) HMEN,
677 COUNT(DECODE(PEI.PEI_INFORMATION5
678 ,'3'
679 ,DECODE(PEO.SEX
680 ,'F'
681 ,1
682 ,NULL)
683 ,'9'
684 ,DECODE(PEO.SEX
685 ,'F'
686 ,1
687 ,NULL)
688 ,NULL)) HWMEN,
689 COUNT(DECODE(PEI.PEI_INFORMATION5
690 ,'1'
691 ,DECODE(PEO.SEX
692 ,'M'
693 ,1
694 ,NULL)
695 ,NULL)) WNHMEN,
696 COUNT(DECODE(PEI.PEI_INFORMATION5
697 ,'1'
698 ,DECODE(PEO.SEX
699 ,'F'
700 ,1
701 ,NULL)
702 ,NULL)) WNHWMEN,
703 COUNT(DECODE(PEI.PEI_INFORMATION5
704 ,NULL
705 ,DECODE(PEO.SEX
706 ,'M'
707 ,1
708 ,NULL)
709 ,NULL)) URMEN,
710 COUNT(DECODE(PEI.PEI_INFORMATION5
711 ,NULL
712 ,DECODE(PEO.SEX
713 ,'F'
714 ,1
715 ,NULL)
716 ,NULL)) URWMEN
717 FROM
718 PER_ALL_PEOPLE_F PEO,
719 PER_ALL_ASSIGNMENTS_F PAF,
720 PER_ASSIGNMENT_STATUS_TYPES AST,
721 PER_PEOPLE_EXTRA_INFO PPET,
722 PER_JOBS JOB,
723 HR_LOOKUPS HL,
724 PER_PEOPLE_EXTRA_INFO PEI
725 WHERE PAF.PERSON_ID = PPET.PERSON_ID
726 AND PAF.PERSON_ID = PEO.PERSON_ID
727 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
728 AND PEO.PER_INFORMATION1 = '13'
729 AND PEO.PERSON_ID = pei.person_id (+)
730 AND ( PEI.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
731 OR ( PEI.INFORMATION_TYPE <> 'PER_US_ADDL_ETHNIC_CAT'
732 AND not exists (
733 SELECT
734 1
735 FROM
736 PER_PEOPLE_EXTRA_INFO PEI2
737 WHERE PEI2.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
738 AND PEI2.PERSON_ID = PEI.PERSON_ID )
739 AND PEI.PERSON_EXTRA_INFO_ID = (
740 SELECT
741 MAX(PEI1.PERSON_EXTRA_INFO_ID)
742 FROM
743 PER_PEOPLE_EXTRA_INFO PEI1
744 WHERE PEI1.PERSON_ID = PEI.PERSON_ID ) )
745 OR ( not exists (
746 SELECT
747 PERSON_EXTRA_INFO_ID
748 FROM
749 PER_PEOPLE_EXTRA_INFO PEI3
750 WHERE PEI3.PERSON_ID = PEI.PERSON_ID ) ) )
751 AND PPET.PEI_INFORMATION_CATEGORY = 'PQH_TENURE_STATUS'
752 AND PQH_EMPLOYMENT_CATEGORY.GET_SERVICE_START_DATE(PAF.PERIOD_OF_SERVICE_ID) BETWEEN CP_HIRE_DATE
753 AND P_REPORT_DATE
754 AND FND_DATE.CANONICAL_TO_DATE(PPET.PEI_INFORMATION2) <= P_REPORT_DATE
755 AND PPET.PEI_INFORMATION1 in ( '02' , '04' )
756 AND PPET.PEI_INFORMATION1 IS NOT NULL
757 AND PAF.PRIMARY_FLAG = 'Y'
758 AND PAF.ASSIGNMENT_TYPE = 'E'
759 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
760 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
761 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
762 ,CP_FR
763 ,CP_FT
764 ,CP_PR
765 ,CP_PT) = 'FR'
766 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
767 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
768 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
769 AND HL.LOOKUP_CODE in ( '1' , '2' , '3' )
770 AND PAF.JOB_ID = JOB.JOB_ID
771 AND PAF.ORGANIZATION_ID IN (
772 SELECT
773 ORGANIZATION_ID
774 FROM
775 HR_ALL_ORGANIZATION_UNITS
776 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID );
777 CURSOR GET_LINE3_COUNTS IS
778 SELECT
779 '3' JCODE,
780 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
781 ,P_REPORT_DATE)
782 ,'1'
783 ,DECODE(PEO.SEX
784 ,'M'
785 ,1
786 ,NULL)
787 ,NULL)) NRMEN,
788 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
789 ,P_REPORT_DATE)
790 ,'1'
791 ,DECODE(PEO.SEX
792 ,'F'
793 ,1
794 ,NULL)
795 ,NULL)) NRWMEN,
796 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
797 ,P_REPORT_DATE)
798 ,NULL
799 ,(DECODE(PEO.PER_INFORMATION1
800 ,'2'
801 ,DECODE(PEO.SEX
802 ,'M'
803 ,1
804 ,NULL)
805 ,NULL)))) BNHMEN,
806 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
807 ,P_REPORT_DATE)
808 ,NULL
809 ,(DECODE(PEO.PER_INFORMATION1
810 ,'2'
811 ,DECODE(PEO.SEX
812 ,'F'
813 ,1
814 ,NULL)
815 ,NULL)))) BNHWMEN,
816 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
817 ,P_REPORT_DATE)
818 ,NULL
819 ,(DECODE(PEO.PER_INFORMATION1
820 ,'6'
821 ,DECODE(PEO.SEX
822 ,'M'
823 ,1
824 ,NULL)
825 ,NULL)))) AM_ALMEN,
826 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
827 ,P_REPORT_DATE)
828 ,NULL
829 ,(DECODE(PEO.PER_INFORMATION1
830 ,'6'
831 ,DECODE(PEO.SEX
832 ,'F'
833 ,1
834 ,NULL)
835 ,NULL)))) AM_ALWMEN,
836 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
837 ,P_REPORT_DATE)
838 ,NULL
839 ,(DECODE(PEO.PER_INFORMATION1
840 ,'4'
841 ,DECODE(PEO.SEX
842 ,'M'
843 ,1
844 ,NULL)
845 ,'5'
846 ,DECODE(PEO.SEX
847 ,'M'
848 ,1
849 ,NULL)
850 ,NULL)))) APMEN,
851 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
852 ,P_REPORT_DATE)
853 ,NULL
854 ,(DECODE(PEO.PER_INFORMATION1
855 ,'4'
856 ,DECODE(PEO.SEX
857 ,'F'
858 ,1
859 ,NULL)
860 ,'5'
861 ,DECODE(PEO.SEX
862 ,'F'
863 ,1
864 ,NULL)
865 ,NULL)))) APWMEN,
866 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
867 ,P_REPORT_DATE)
868 ,NULL
869 ,(DECODE(PEO.PER_INFORMATION1
870 ,'3'
871 ,DECODE(PEO.SEX
872 ,'M'
873 ,1
874 ,NULL)
875 ,'9'
876 ,DECODE(PEO.SEX
877 ,'M'
878 ,1
879 ,NULL)
880 ,NULL)))) HMEN,
881 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
882 ,P_REPORT_DATE)
883 ,NULL
884 ,(DECODE(PEO.PER_INFORMATION1
885 ,'3'
886 ,DECODE(PEO.SEX
887 ,'F'
888 ,1
889 ,NULL)
890 ,'9'
891 ,DECODE(PEO.SEX
892 ,'F'
893 ,1
894 ,NULL)
895 ,NULL)))) HWMEN,
896 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
897 ,P_REPORT_DATE)
898 ,NULL
899 ,(DECODE(PEO.PER_INFORMATION1
900 ,'1'
901 ,DECODE(PEO.SEX
902 ,'M'
903 ,1
904 ,NULL)
905 ,NULL)))) WNHMEN,
906 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
907 ,P_REPORT_DATE)
908 ,NULL
909 ,(DECODE(PEO.PER_INFORMATION1
910 ,'1'
911 ,DECODE(PEO.SEX
912 ,'F'
913 ,1
914 ,NULL)
915 ,NULL)))) WNHWMEN,
916 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
917 ,P_REPORT_DATE)
918 ,NULL
919 ,(DECODE(PEO.PER_INFORMATION1
920 ,NULL
921 ,DECODE(PEO.SEX
922 ,'M'
923 ,1
924 ,NULL)
925 ,NULL)))) URMEN,
926 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
927 ,P_REPORT_DATE)
928 ,NULL
929 ,(DECODE(PEO.PER_INFORMATION1
930 ,NULL
931 ,DECODE(PEO.SEX
932 ,'F'
933 ,1
934 ,NULL)
935 ,NULL)))) URWMEN
936 FROM
937 PER_ALL_PEOPLE_F PEO,
938 PER_ALL_ASSIGNMENTS_F PAF,
939 PER_ASSIGNMENT_STATUS_TYPES AST,
940 PER_JOBS JOB,
941 HR_LOOKUPS HL
942 WHERE PAF.PERSON_ID = PEO.PERSON_ID
943 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
944 AND not exists (
945 SELECT
946 PET.PERSON_ID
947 FROM
948 PER_PEOPLE_EXTRA_INFO PET
949 WHERE PET.PERSON_ID = PEO.PERSON_ID
950 AND PET.PEI_INFORMATION_CATEGORY in ( 'PQH_TENURE_STATUS' )
951 AND PET.PEI_INFORMATION1 in ( '01' , '02' , '04' )
952 AND FND_DATE.CANONICAL_TO_DATE(PET.PEI_INFORMATION2) <= P_REPORT_DATE )
953 AND PQH_EMPLOYMENT_CATEGORY.GET_SERVICE_START_DATE(PAF.PERIOD_OF_SERVICE_ID) BETWEEN CP_HIRE_DATE
954 AND P_REPORT_DATE
955 AND PAF.PRIMARY_FLAG = 'Y'
956 AND PAF.ASSIGNMENT_TYPE = 'E'
957 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
958 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
959 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
960 ,CP_FR
961 ,CP_FT
962 ,CP_PR
963 ,CP_PT) = 'FR'
964 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
965 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
966 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
967 AND HL.LOOKUP_CODE in ( '1' , '2' , '3' )
968 AND PAF.JOB_ID = JOB.JOB_ID
969 AND PAF.ORGANIZATION_ID IN (
970 SELECT
971 ORGANIZATION_ID
972 FROM
973 HR_ALL_ORGANIZATION_UNITS
974 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
975 GROUP BY
976 '3';
977 CURSOR GET_LINE3_TMR_COUNTS IS
978 SELECT
979 COUNT(DECODE(PEI.PEI_INFORMATION5
980 ,'2'
981 ,DECODE(PEO.SEX
982 ,'M'
983 ,1
984 ,NULL)
985 ,NULL)) BNHMEN,
986 COUNT(DECODE(PEI.PEI_INFORMATION5
987 ,'2'
988 ,DECODE(PEO.SEX
989 ,'F'
990 ,1
991 ,NULL)
992 ,NULL)) BNHWMEN,
993 COUNT(DECODE(PEI.PEI_INFORMATION5
994 ,'6'
995 ,DECODE(PEO.SEX
996 ,'M'
997 ,1
998 ,NULL)
999 ,NULL)) AM_ALMEN,
1000 COUNT(DECODE(PEI.PEI_INFORMATION5
1001 ,'6'
1002 ,DECODE(PEO.SEX
1003 ,'F'
1004 ,1
1005 ,NULL)
1006 ,NULL)) AM_ALWMEN,
1007 COUNT(DECODE(PEI.PEI_INFORMATION5
1008 ,'4'
1009 ,DECODE(PEO.SEX
1010 ,'M'
1011 ,1
1012 ,NULL)
1013 ,'5'
1014 ,DECODE(PEO.SEX
1015 ,'M'
1016 ,1
1017 ,NULL)
1018 ,NULL)) A_PMEN,
1019 COUNT(DECODE(PEI.PEI_INFORMATION5
1020 ,'4'
1021 ,DECODE(PEO.SEX
1022 ,'F'
1023 ,1
1024 ,NULL)
1025 ,'5'
1026 ,DECODE(PEO.SEX
1027 ,'F'
1028 ,1
1029 ,NULL)
1030 ,NULL)) A_PWMEN,
1031 COUNT(DECODE(PEI.PEI_INFORMATION5
1032 ,'3'
1033 ,DECODE(PEO.SEX
1034 ,'M'
1035 ,1
1036 ,NULL)
1037 ,'9'
1038 ,DECODE(PEO.SEX
1039 ,'M'
1040 ,1
1041 ,NULL)
1042 ,NULL)) HMEN,
1043 COUNT(DECODE(PEI.PEI_INFORMATION5
1044 ,'3'
1045 ,DECODE(PEO.SEX
1046 ,'F'
1047 ,1
1048 ,NULL)
1049 ,'9'
1050 ,DECODE(PEO.SEX
1051 ,'F'
1052 ,1
1053 ,NULL)
1054 ,NULL)) HWMEN,
1055 COUNT(DECODE(PEI.PEI_INFORMATION5
1056 ,'1'
1057 ,DECODE(PEO.SEX
1058 ,'M'
1059 ,1
1060 ,NULL)
1061 ,NULL)) WNHMEN,
1062 COUNT(DECODE(PEI.PEI_INFORMATION5
1063 ,'1'
1064 ,DECODE(PEO.SEX
1065 ,'F'
1066 ,1
1067 ,NULL)
1068 ,NULL)) WNHWMEN,
1069 COUNT(DECODE(PEI.PEI_INFORMATION5
1070 ,NULL
1071 ,DECODE(PEO.SEX
1072 ,'M'
1073 ,1
1074 ,NULL)
1075 ,NULL)) URMEN,
1076 COUNT(DECODE(PEI.PEI_INFORMATION5
1077 ,NULL
1078 ,DECODE(PEO.SEX
1079 ,'F'
1080 ,1
1081 ,NULL)
1082 ,NULL)) URWMEN
1083 FROM
1084 PER_ALL_PEOPLE_F PEO,
1085 PER_ALL_ASSIGNMENTS_F PAF,
1086 PER_ASSIGNMENT_STATUS_TYPES AST,
1087 PER_JOBS JOB,
1088 HR_LOOKUPS HL,
1089 PER_PEOPLE_EXTRA_INFO PEI
1090 WHERE PAF.PERSON_ID = PEO.PERSON_ID
1091 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
1092 AND PEO.PER_INFORMATION1 = '13'
1093 AND PEO.PERSON_ID = pei.person_id (+)
1094 AND ( PEI.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
1095 OR ( PEI.INFORMATION_TYPE <> 'PER_US_ADDL_ETHNIC_CAT'
1096 AND not exists (
1097 SELECT
1098 1
1099 FROM
1100 PER_PEOPLE_EXTRA_INFO PEI2
1101 WHERE PEI2.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
1102 AND PEI2.PERSON_ID = PEI.PERSON_ID )
1103 AND PEI.PERSON_EXTRA_INFO_ID = (
1104 SELECT
1105 MAX(PEI1.PERSON_EXTRA_INFO_ID)
1106 FROM
1107 PER_PEOPLE_EXTRA_INFO PEI1
1108 WHERE PEI1.PERSON_ID = PEI.PERSON_ID ) )
1109 OR ( not exists (
1110 SELECT
1111 PERSON_EXTRA_INFO_ID
1112 FROM
1113 PER_PEOPLE_EXTRA_INFO PEI3
1114 WHERE PEI3.PERSON_ID = PEI.PERSON_ID ) ) )
1115 AND not exists (
1116 SELECT
1117 PET.PERSON_ID
1118 FROM
1119 PER_PEOPLE_EXTRA_INFO PET
1120 WHERE PET.PERSON_ID = PEO.PERSON_ID
1121 AND PET.PEI_INFORMATION_CATEGORY in ( 'PQH_TENURE_STATUS' )
1122 AND PET.PEI_INFORMATION1 in ( '01' , '02' , '04' )
1123 AND FND_DATE.CANONICAL_TO_DATE(PET.PEI_INFORMATION2) <= P_REPORT_DATE )
1124 AND PQH_EMPLOYMENT_CATEGORY.GET_SERVICE_START_DATE(PAF.PERIOD_OF_SERVICE_ID) BETWEEN CP_HIRE_DATE
1125 AND P_REPORT_DATE
1126 AND PAF.PRIMARY_FLAG = 'Y'
1127 AND PAF.ASSIGNMENT_TYPE = 'E'
1128 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
1129 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
1130 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
1131 ,CP_FR
1132 ,CP_FT
1133 ,CP_PR
1134 ,CP_PT) = 'FR'
1135 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
1136 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
1137 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
1138 AND HL.LOOKUP_CODE in ( '1' , '2' , '3' )
1139 AND PAF.JOB_ID = JOB.JOB_ID
1140 AND PAF.ORGANIZATION_ID IN (
1141 SELECT
1142 ORGANIZATION_ID
1143 FROM
1144 HR_ALL_ORGANIZATION_UNITS
1145 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID );
1146 CURSOR GET_LINE4_COUNTS IS
1147 SELECT
1148 HL.LOOKUP_CODE JCODE,
1149 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1150 ,P_REPORT_DATE)
1151 ,'1'
1152 ,DECODE(PEO.SEX
1153 ,'M'
1154 ,1
1155 ,NULL)
1156 ,NULL)) NRMEN,
1157 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1158 ,P_REPORT_DATE)
1159 ,'1'
1160 ,DECODE(PEO.SEX
1161 ,'F'
1162 ,1
1163 ,NULL)
1164 ,NULL)) NRWMEN,
1165 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1166 ,P_REPORT_DATE)
1167 ,NULL
1168 ,(DECODE(PEO.PER_INFORMATION1
1169 ,'2'
1170 ,DECODE(PEO.SEX
1171 ,'M'
1172 ,1
1173 ,NULL)
1174 ,NULL)))) BNHMEN,
1175 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1176 ,P_REPORT_DATE)
1177 ,NULL
1178 ,(DECODE(PEO.PER_INFORMATION1
1179 ,'2'
1180 ,DECODE(PEO.SEX
1181 ,'F'
1182 ,1
1183 ,NULL)
1184 ,NULL)))) BNHWMEN,
1185 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1186 ,P_REPORT_DATE)
1187 ,NULL
1188 ,(DECODE(PEO.PER_INFORMATION1
1189 ,'6'
1190 ,DECODE(PEO.SEX
1191 ,'M'
1192 ,1
1193 ,NULL)
1194 ,NULL)))) AM_ALMEN,
1195 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1196 ,P_REPORT_DATE)
1197 ,NULL
1198 ,(DECODE(PEO.PER_INFORMATION1
1199 ,'6'
1200 ,DECODE(PEO.SEX
1201 ,'F'
1202 ,1
1203 ,NULL)
1204 ,NULL)))) AM_ALWMEN,
1205 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1206 ,P_REPORT_DATE)
1207 ,NULL
1208 ,(DECODE(PEO.PER_INFORMATION1
1209 ,'4'
1210 ,DECODE(PEO.SEX
1211 ,'M'
1212 ,1
1213 ,NULL)
1214 ,'5'
1215 ,DECODE(PEO.SEX
1216 ,'M'
1217 ,1
1218 ,NULL)
1219 ,NULL)))) APMEN,
1220 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1221 ,P_REPORT_DATE)
1222 ,NULL
1223 ,(DECODE(PEO.PER_INFORMATION1
1224 ,'4'
1225 ,DECODE(PEO.SEX
1226 ,'F'
1227 ,1
1228 ,NULL)
1229 ,'5'
1230 ,DECODE(PEO.SEX
1231 ,'F'
1232 ,1
1233 ,NULL)
1234 ,NULL)))) APWMEN,
1235 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1236 ,P_REPORT_DATE)
1237 ,NULL
1238 ,(DECODE(PEO.PER_INFORMATION1
1239 ,'3'
1240 ,DECODE(PEO.SEX
1241 ,'M'
1242 ,1
1243 ,NULL)
1244 ,'9'
1245 ,DECODE(PEO.SEX
1246 ,'M'
1247 ,1
1248 ,NULL)
1249 ,NULL)))) HMEN,
1250 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1251 ,P_REPORT_DATE)
1252 ,NULL
1253 ,(DECODE(PEO.PER_INFORMATION1
1254 ,'3'
1255 ,DECODE(PEO.SEX
1256 ,'F'
1257 ,1
1258 ,NULL)
1259 ,'9'
1260 ,DECODE(PEO.SEX
1261 ,'F'
1262 ,1
1263 ,NULL)
1264 ,NULL)))) HWMEN,
1265 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1266 ,P_REPORT_DATE)
1267 ,NULL
1268 ,(DECODE(PEO.PER_INFORMATION1
1269 ,'1'
1270 ,DECODE(PEO.SEX
1271 ,'M'
1272 ,1
1273 ,NULL)
1274 ,NULL)))) WNHMEN,
1275 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1276 ,P_REPORT_DATE)
1277 ,NULL
1278 ,(DECODE(PEO.PER_INFORMATION1
1279 ,'1'
1280 ,DECODE(PEO.SEX
1281 ,'F'
1282 ,1
1283 ,NULL)
1284 ,NULL)))) WNHWMEN,
1285 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1286 ,P_REPORT_DATE)
1287 ,NULL
1288 ,(DECODE(PEO.PER_INFORMATION1
1289 ,NULL
1290 ,DECODE(PEO.SEX
1291 ,'M'
1292 ,1
1293 ,NULL)
1294 ,NULL)))) URMEN,
1295 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
1296 ,P_REPORT_DATE)
1297 ,NULL
1298 ,(DECODE(PEO.PER_INFORMATION1
1299 ,NULL
1300 ,DECODE(PEO.SEX
1301 ,'F'
1302 ,1
1303 ,NULL)
1304 ,NULL)))) URWMEN,
1305 COUNT(DECODE(PEO.SEX
1306 ,'M'
1307 ,PEO.PERSON_ID
1308 ,NULL)) TOTMEN,
1309 COUNT(DECODE(PEO.SEX
1310 ,'F'
1311 ,PEO.PERSON_ID
1312 ,NULL)) TOTWMEN
1313 FROM
1314 PER_ALL_PEOPLE_F PEO,
1315 PER_ALL_ASSIGNMENTS_F PAF,
1316 PER_ASSIGNMENT_STATUS_TYPES AST,
1317 PER_JOBS JOB,
1318 HR_LOOKUPS HL
1319 WHERE PEO.PERSON_ID = PAF.PERSON_ID
1320 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
1321 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
1322 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
1323 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
1324 AND HL.LOOKUP_CODE not in ( '1' , '2' , '3' , '4' , '12' )
1325 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
1326 ,CP_FR
1327 ,CP_FT
1328 ,CP_PR
1329 ,CP_PT) = 'FR'
1330 AND PQH_EMPLOYMENT_CATEGORY.GET_SERVICE_START_DATE(PAF.PERIOD_OF_SERVICE_ID) BETWEEN CP_HIRE_DATE
1331 AND P_REPORT_DATE
1332 AND PAF.PRIMARY_FLAG = 'Y'
1333 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
1334 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
1335 AND PAF.JOB_ID = JOB.JOB_ID
1336 AND PAF.ASSIGNMENT_TYPE = 'E'
1337 AND PAF.ORGANIZATION_ID in (
1338 SELECT
1339 ORGANIZATION_ID
1340 FROM
1341 HR_ALL_ORGANIZATION_UNITS
1342 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
1343 GROUP BY
1344 HL.LOOKUP_CODE;
1345 CURSOR GET_LINE4_TMR_COUNTS(C_LOOKUP_CODE IN VARCHAR2) IS
1346 SELECT
1347 COUNT(DECODE(PEI.PEI_INFORMATION5
1348 ,'2'
1349 ,DECODE(PEO.SEX
1350 ,'M'
1351 ,1
1352 ,NULL)
1353 ,NULL)) BNHMEN,
1354 COUNT(DECODE(PEI.PEI_INFORMATION5
1355 ,'2'
1356 ,DECODE(PEO.SEX
1357 ,'F'
1358 ,1
1359 ,NULL)
1360 ,NULL)) BNHWMEN,
1361 COUNT(DECODE(PEI.PEI_INFORMATION5
1362 ,'6'
1363 ,DECODE(PEO.SEX
1364 ,'M'
1365 ,1
1366 ,NULL)
1367 ,NULL)) AM_ALMEN,
1368 COUNT(DECODE(PEI.PEI_INFORMATION5
1369 ,'6'
1370 ,DECODE(PEO.SEX
1371 ,'F'
1372 ,1
1373 ,NULL)
1374 ,NULL)) AM_ALWMEN,
1375 COUNT(DECODE(PEI.PEI_INFORMATION5
1376 ,'4'
1377 ,DECODE(PEO.SEX
1378 ,'M'
1379 ,1
1380 ,NULL)
1381 ,'5'
1382 ,DECODE(PEO.SEX
1383 ,'M'
1384 ,1
1385 ,NULL)
1386 ,NULL)) A_PMEN,
1387 COUNT(DECODE(PEI.PEI_INFORMATION5
1388 ,'4'
1389 ,DECODE(PEO.SEX
1390 ,'F'
1391 ,1
1392 ,NULL)
1393 ,'5'
1394 ,DECODE(PEO.SEX
1395 ,'F'
1396 ,1
1397 ,NULL)
1398 ,NULL)) A_PWMEN,
1399 COUNT(DECODE(PEI.PEI_INFORMATION5
1400 ,'3'
1401 ,DECODE(PEO.SEX
1402 ,'M'
1403 ,1
1404 ,NULL)
1405 ,'9'
1406 ,DECODE(PEO.SEX
1407 ,'M'
1408 ,1
1409 ,NULL)
1410 ,NULL)) HMEN,
1411 COUNT(DECODE(PEI.PEI_INFORMATION5
1412 ,'3'
1413 ,DECODE(PEO.SEX
1414 ,'F'
1415 ,1
1416 ,NULL)
1417 ,'9'
1418 ,DECODE(PEO.SEX
1419 ,'F'
1420 ,1
1421 ,NULL)
1422 ,NULL)) HWMEN,
1423 COUNT(DECODE(PEI.PEI_INFORMATION5
1424 ,'1'
1425 ,DECODE(PEO.SEX
1426 ,'M'
1427 ,1
1428 ,NULL)
1429 ,NULL)) WNHMEN,
1430 COUNT(DECODE(PEI.PEI_INFORMATION5
1431 ,'1'
1432 ,DECODE(PEO.SEX
1433 ,'F'
1434 ,1
1435 ,NULL)
1436 ,NULL)) WNHWMEN,
1437 COUNT(DECODE(PEI.PEI_INFORMATION5
1438 ,NULL
1439 ,DECODE(PEO.SEX
1440 ,'M'
1441 ,1
1442 ,NULL)
1443 ,NULL)) URMEN,
1444 COUNT(DECODE(PEI.PEI_INFORMATION5
1445 ,NULL
1446 ,DECODE(PEO.SEX
1447 ,'F'
1448 ,1
1449 ,NULL)
1450 ,NULL)) URWMEN
1451 FROM
1452 PER_ALL_PEOPLE_F PEO,
1453 PER_ALL_ASSIGNMENTS_F PAF,
1454 PER_ASSIGNMENT_STATUS_TYPES AST,
1455 PER_JOBS JOB,
1456 HR_LOOKUPS HL,
1457 PER_PEOPLE_EXTRA_INFO PEI
1458 WHERE PEO.PERSON_ID = PAF.PERSON_ID
1459 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
1460 AND PEO.PER_INFORMATION1 = '13'
1461 AND PEO.PERSON_ID = pei.person_id (+)
1462 AND ( PEI.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
1463 OR ( PEI.INFORMATION_TYPE <> 'PER_US_ADDL_ETHNIC_CAT'
1464 AND not exists (
1465 SELECT
1466 1
1467 FROM
1468 PER_PEOPLE_EXTRA_INFO PEI2
1469 WHERE PEI2.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
1470 AND PEI2.PERSON_ID = PEI.PERSON_ID )
1471 AND PEI.PERSON_EXTRA_INFO_ID = (
1472 SELECT
1473 MAX(PEI1.PERSON_EXTRA_INFO_ID)
1474 FROM
1475 PER_PEOPLE_EXTRA_INFO PEI1
1476 WHERE PEI1.PERSON_ID = PEI.PERSON_ID ) )
1477 OR ( not exists (
1478 SELECT
1479 PERSON_EXTRA_INFO_ID
1480 FROM
1481 PER_PEOPLE_EXTRA_INFO PEI3
1482 WHERE PEI3.PERSON_ID = PEI.PERSON_ID ) ) )
1483 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
1484 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
1485 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
1486 AND HL.LOOKUP_CODE not in ( '1' , '2' , '3' , '4' , '12' )
1487 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(PAF.EMPLOYMENT_CATEGORY
1488 ,CP_FR
1489 ,CP_FT
1490 ,CP_PR
1491 ,CP_PT) = 'FR'
1492 AND PQH_EMPLOYMENT_CATEGORY.GET_SERVICE_START_DATE(PAF.PERIOD_OF_SERVICE_ID) BETWEEN CP_HIRE_DATE
1493 AND P_REPORT_DATE
1494 AND PAF.PRIMARY_FLAG = 'Y'
1495 AND PAF.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
1496 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
1497 AND PAF.JOB_ID = JOB.JOB_ID
1498 AND PAF.ASSIGNMENT_TYPE = 'E'
1499 AND PAF.ORGANIZATION_ID in (
1500 SELECT
1501 ORGANIZATION_ID
1502 FROM
1503 HR_ALL_ORGANIZATION_UNITS
1504 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
1505 AND HL.LOOKUP_CODE = C_LOOKUP_CODE;
1506 BEGIN
1507 --HR_STANDARD.EVENT('BEFORE REPORT');
1508 PQH_EMPLOYMENT_CATEGORY.FETCH_EMPL_CATEGORIES(P_BUSINESS_GROUP_ID
1509 ,L_FR
1510 ,L_FT
1511 ,L_PR
1512 ,L_PT);
1513 CP_FR := L_FR;
1514 CP_FT := L_FT;
1515 CP_PR := L_PR;
1516 CP_PT := L_PT;
1517 L_YEAR := TO_CHAR(P_REPORT_DATE
1518 ,'RRRR');
1519 L_MONTH := SUBSTR(P_REPORT_DATE
1520 ,4
1521 ,3);
1522 L_DAY := SUBSTR(P_REPORT_DATE
1523 ,1
1524 ,2);
1525 IF L_YEAR = 0 THEN
1526 L_YEAR := 2000;
1527 END IF;
1528 IF L_MONTH in ('JAN','FEB','MAR','APR','MAY','JUN') THEN
1529 L_YEAR_AGO := (TO_CHAR(P_REPORT_DATE
1530 ,'RRRR')) - 1;
1531 CP_HIRE_DATE := TO_DATE('01-07-' || L_YEAR_AGO
1532 ,'dd-mm-rrrr');
1533 ELSE
1534 CP_HIRE_DATE := TO_DATE('01-07-' || L_YEAR
1535 ,'dd-mm-rrrr');
1536 END IF;
1537
1538 LP_HIRE_DATE := to_char(CP_HIRE_DATE,'DD-MON-YYYY');
1539
1540 P_REPORT_DATE_T := to_char(P_REPORT_DATE,'DD-MON-YYYY');
1541
1542 --RETURN TRUE;
1543 OPEN GET_LINE1_COUNTS;
1544 FETCH GET_LINE1_COUNTS
1545 INTO 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;
1546 CLOSE GET_LINE1_COUNTS;
1547 L_TOT_MEN := L_NR_MEN + L_BNH_MEN + L_AMAI_MEN + L_AP_MEN + L_H_MEN + L_WNH_MEN + L_UR_MEN;
1548 L_TOT_WMEN := L_NR_WMEN + L_BNH_WMEN + L_AMAI_WMEN + L_AP_WMEN + L_H_WMEN + L_WNH_WMEN + L_UR_WMEN;
1549 OPEN GET_LINE1_TMRACES_COUNTS;
1550 FETCH GET_LINE1_TMRACES_COUNTS
1551 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;
1552 CLOSE GET_LINE1_TMRACES_COUNTS;
1553 L_BNH_MEN := L_BNH_MEN + L_TMR_BNH_MEN;
1554 L_BNH_WMEN := L_BNH_WMEN + L_TMR_BNH_WMEN;
1555 L_AMAI_MEN := L_AMAI_MEN + L_TMR_AMAI_MEN;
1556 L_AMAI_WMEN := L_AMAI_WMEN + L_TMR_AMAI_WMEN;
1557 L_AP_MEN := L_AP_MEN + L_TMR_AP_MEN;
1558 L_AP_WMEN := L_AP_WMEN + L_TMR_AP_WMEN;
1559 L_H_MEN := L_H_MEN + L_TMR_H_MEN;
1560 L_H_WMEN := L_H_WMEN + L_TMR_H_WMEN;
1561 L_WNH_MEN := L_WNH_MEN + L_TMR_WNH_MEN;
1562 L_WNH_WMEN := L_WNH_WMEN + L_TMR_WNH_WMEN;
1563 L_UR_MEN := L_UR_MEN + L_TMR_UR_MEN;
1564 L_UR_WMEN := L_UR_WMEN + L_TMR_UR_WMEN;
1565 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;
1566 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;
1567 INSERT INTO PAY_US_RPT_TOTALS
1568 (SESSION_ID
1569 ,ATTRIBUTE1
1570 ,VALUE1
1571 ,VALUE2
1572 ,VALUE3
1573 ,VALUE4
1574 ,VALUE5
1575 ,VALUE6
1576 ,VALUE7
1577 ,VALUE8
1578 ,VALUE9
1579 ,VALUE10
1580 ,VALUE11
1581 ,VALUE12
1582 ,VALUE13
1583 ,VALUE14
1584 ,VALUE15
1585 ,VALUE16
1586 ,VALUE17)
1587 VALUES (USERENV('sessionid')
1588 ,'IPED9'
1589 ,SC
1590 ,L_NR_MEN
1591 ,L_NR_WMEN
1592 ,L_BNH_MEN
1593 ,L_BNH_WMEN
1594 ,L_AMAI_MEN
1595 ,L_AMAI_WMEN
1596 ,L_AP_MEN
1597 ,L_AP_WMEN
1598 ,L_H_MEN
1599 ,L_H_WMEN
1600 ,L_WNH_MEN
1601 ,L_WNH_WMEN
1602 ,L_UR_MEN
1603 ,L_UR_WMEN
1604 ,L_TOT_MEN
1605 ,L_TOT_WMEN);
1606 COMMIT;
1607 OPEN GET_LINE2_COUNTS;
1608 FETCH GET_LINE2_COUNTS
1609 INTO 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;
1610 CLOSE GET_LINE2_COUNTS;
1611 L_TOT_MEN := L_NR_MEN + L_BNH_MEN + L_AMAI_MEN + L_AP_MEN + L_H_MEN + L_WNH_MEN + L_UR_MEN;
1612 L_TOT_WMEN := L_NR_WMEN + L_BNH_WMEN + L_AMAI_WMEN + L_AP_WMEN + L_H_WMEN + L_WNH_WMEN + L_UR_WMEN;
1613 OPEN GET_TMR_LINE2_COUNTS;
1614 FETCH GET_TMR_LINE2_COUNTS
1615 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;
1616 CLOSE GET_TMR_LINE2_COUNTS;
1617 L_BNH_MEN := L_BNH_MEN + L_TMR_BNH_MEN;
1618 L_BNH_WMEN := L_BNH_WMEN + L_TMR_BNH_WMEN;
1619 L_AMAI_MEN := L_AMAI_MEN + L_TMR_AMAI_MEN;
1620 L_AMAI_WMEN := L_AMAI_WMEN + L_TMR_AMAI_WMEN;
1621 L_AP_MEN := L_AP_MEN + L_TMR_AP_MEN;
1622 L_AP_WMEN := L_AP_WMEN + L_TMR_AP_WMEN;
1623 L_H_MEN := L_H_MEN + L_TMR_H_MEN;
1624 L_H_WMEN := L_H_WMEN + L_TMR_H_WMEN;
1625 L_WNH_MEN := L_WNH_MEN + L_TMR_WNH_MEN;
1626 L_WNH_WMEN := L_WNH_WMEN + L_TMR_WNH_WMEN;
1627 L_UR_MEN := L_UR_MEN + L_TMR_UR_MEN;
1628 L_UR_WMEN := L_UR_WMEN + L_TMR_UR_WMEN;
1629 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;
1630 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;
1631 INSERT INTO PAY_US_RPT_TOTALS
1632 (SESSION_ID
1633 ,ATTRIBUTE1
1634 ,VALUE1
1635 ,VALUE2
1636 ,VALUE3
1637 ,VALUE4
1638 ,VALUE5
1639 ,VALUE6
1640 ,VALUE7
1641 ,VALUE8
1642 ,VALUE9
1643 ,VALUE10
1644 ,VALUE11
1645 ,VALUE12
1646 ,VALUE13
1647 ,VALUE14
1648 ,VALUE15
1649 ,VALUE16
1650 ,VALUE17)
1651 VALUES (USERENV('sessionid')
1652 ,'IPED9'
1653 ,SC
1654 ,L_NR_MEN
1655 ,L_NR_WMEN
1656 ,L_BNH_MEN
1657 ,L_BNH_WMEN
1658 ,L_AMAI_MEN
1659 ,L_AMAI_WMEN
1660 ,L_AP_MEN
1661 ,L_AP_WMEN
1662 ,L_H_MEN
1663 ,L_H_WMEN
1664 ,L_WNH_MEN
1665 ,L_WNH_WMEN
1666 ,L_UR_MEN
1667 ,L_UR_WMEN
1668 ,L_TOT_MEN
1669 ,L_TOT_WMEN);
1670 COMMIT;
1671 OPEN GET_LINE3_COUNTS;
1672 FETCH GET_LINE3_COUNTS
1673 INTO 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;
1674 CLOSE GET_LINE3_COUNTS;
1675 L_TOT_MEN := L_NR_MEN + L_BNH_MEN + L_AMAI_MEN + L_AP_MEN + L_H_MEN + L_WNH_MEN + L_UR_MEN;
1676 L_TOT_WMEN := L_NR_WMEN + L_BNH_WMEN + L_AMAI_WMEN + L_AP_WMEN + L_H_WMEN + L_WNH_WMEN + L_UR_WMEN;
1677 OPEN GET_LINE3_TMR_COUNTS;
1678 FETCH GET_LINE3_TMR_COUNTS
1679 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;
1680 CLOSE GET_LINE3_TMR_COUNTS;
1681 L_BNH_MEN := L_BNH_MEN + L_TMR_BNH_MEN;
1682 L_BNH_WMEN := L_BNH_WMEN + L_TMR_BNH_WMEN;
1683 L_AMAI_MEN := L_AMAI_MEN + L_TMR_AMAI_MEN;
1684 L_AMAI_WMEN := L_AMAI_WMEN + L_TMR_AMAI_WMEN;
1685 L_AP_MEN := L_AP_MEN + L_TMR_AP_MEN;
1686 L_AP_WMEN := L_AP_WMEN + L_TMR_AP_WMEN;
1687 L_H_MEN := L_H_MEN + L_TMR_H_MEN;
1688 L_H_WMEN := L_H_WMEN + L_TMR_H_WMEN;
1689 L_WNH_MEN := L_WNH_MEN + L_TMR_WNH_MEN;
1690 L_WNH_WMEN := L_WNH_WMEN + L_TMR_WNH_WMEN;
1691 L_UR_MEN := L_UR_MEN + L_TMR_UR_MEN;
1692 L_UR_WMEN := L_UR_WMEN + L_TMR_UR_WMEN;
1693 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;
1694 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;
1695 INSERT INTO PAY_US_RPT_TOTALS
1696 (SESSION_ID
1697 ,ATTRIBUTE1
1698 ,VALUE1
1699 ,VALUE2
1700 ,VALUE3
1701 ,VALUE4
1702 ,VALUE5
1703 ,VALUE6
1704 ,VALUE7
1705 ,VALUE8
1706 ,VALUE9
1707 ,VALUE10
1708 ,VALUE11
1709 ,VALUE12
1710 ,VALUE13
1711 ,VALUE14
1712 ,VALUE15
1713 ,VALUE16
1714 ,VALUE17)
1715 VALUES (USERENV('sessionid')
1716 ,'IPED9'
1717 ,SC
1718 ,L_NR_MEN
1719 ,L_NR_WMEN
1720 ,L_BNH_MEN
1721 ,L_BNH_WMEN
1722 ,L_AMAI_MEN
1723 ,L_AMAI_WMEN
1724 ,L_AP_MEN
1725 ,L_AP_WMEN
1726 ,L_H_MEN
1727 ,L_H_WMEN
1728 ,L_WNH_MEN
1729 ,L_WNH_WMEN
1730 ,L_UR_MEN
1731 ,L_UR_WMEN
1732 ,L_TOT_MEN
1733 ,L_TOT_WMEN);
1734 COMMIT;
1735 FOR i IN GET_LINE4_COUNTS LOOP
1736 SC := I.JCODE;
1737 L_NR_MEN := I.NRMEN;
1738 L_NR_WMEN := I.NRWMEN;
1739 L_BNH_MEN := I.BNHMEN;
1740 L_BNH_WMEN := I.BNHWMEN;
1741 L_AMAI_MEN := I.AM_ALMEN;
1742 L_AMAI_WMEN := I.AM_ALWMEN;
1743 L_AP_MEN := I.APMEN;
1744 L_AP_WMEN := I.APWMEN;
1745 L_H_MEN := I.HMEN;
1746 L_H_WMEN := I.HWMEN;
1747 L_WNH_MEN := I.WNHMEN;
1748 L_WNH_WMEN := I.WNHWMEN;
1749 L_UR_MEN := I.URMEN;
1750 L_UR_WMEN := I.URWMEN;
1751 L_TOT_MEN := L_NR_MEN + L_BNH_MEN + L_AMAI_MEN + L_AP_MEN + L_H_MEN + L_WNH_MEN + L_UR_MEN;
1752 L_TOT_WMEN := L_NR_WMEN + L_BNH_WMEN + L_AMAI_WMEN + L_AP_WMEN + L_H_WMEN + L_WNH_WMEN + L_UR_WMEN;
1753 FOR j IN GET_LINE4_TMR_COUNTS(sc) LOOP
1754 L_BNH_MEN := L_BNH_MEN + J.BNHMEN;
1755 L_BNH_WMEN := L_BNH_WMEN + J.BNHWMEN;
1756 L_AMAI_MEN := L_AMAI_MEN + J.AM_ALMEN;
1757 L_AMAI_WMEN := L_AMAI_WMEN + J.AM_ALWMEN;
1758 L_AP_MEN := L_AP_MEN + J.A_PMEN;
1759 L_AP_WMEN := L_AP_WMEN + J.A_PWMEN;
1760 L_H_MEN := L_H_MEN + J.HMEN;
1761 L_H_WMEN := L_H_WMEN + J.HWMEN;
1762 L_WNH_MEN := L_WNH_MEN + J.WNHMEN;
1763 L_WNH_WMEN := L_WNH_WMEN + J.WNHWMEN;
1764 L_UR_MEN := L_UR_MEN + J.URMEN;
1765 L_UR_WMEN := L_UR_WMEN + J.URWMEN;
1766 L_TOT_MEN := L_TOT_MEN + J.BNHMEN + J.AM_ALMEN + J.A_PMEN + J.HMEN + J.WNHMEN + J.URMEN;
1767 L_TOT_WMEN := L_TOT_WMEN + J.BNHWMEN + J.AM_ALWMEN + J.A_PWMEN + J.HWMEN + J.WNHWMEN + J.URWMEN;
1768 END LOOP;
1769 INSERT INTO PAY_US_RPT_TOTALS
1770 (SESSION_ID
1771 ,ATTRIBUTE1
1772 ,VALUE1
1773 ,VALUE2
1774 ,VALUE3
1775 ,VALUE4
1776 ,VALUE5
1777 ,VALUE6
1778 ,VALUE7
1779 ,VALUE8
1780 ,VALUE9
1781 ,VALUE10
1782 ,VALUE11
1783 ,VALUE12
1784 ,VALUE13
1785 ,VALUE14
1786 ,VALUE15
1787 ,VALUE16
1788 ,VALUE17)
1789 VALUES (USERENV('sessionid')
1790 ,'IPED9'
1791 ,SC
1792 ,L_NR_MEN
1793 ,L_NR_WMEN
1794 ,L_BNH_MEN
1795 ,L_BNH_WMEN
1796 ,L_AMAI_MEN
1797 ,L_AMAI_WMEN
1798 ,L_AP_MEN
1799 ,L_AP_WMEN
1800 ,L_H_MEN
1801 ,L_H_WMEN
1802 ,L_WNH_MEN
1803 ,L_WNH_WMEN
1804 ,L_UR_MEN
1805 ,L_UR_WMEN
1806 ,L_TOT_MEN
1807 ,L_TOT_WMEN);
1808 COMMIT;
1809 END LOOP;
1810 RETURN TRUE;
1811 END BEFOREREPORT;
1812
1813 FUNCTION AFTERREPORT RETURN BOOLEAN IS
1814 temp number(15);
1815 BEGIN
1816 --HR_STANDARD.EVENT('AFTER REPORT');
1817 EXECUTE IMMEDIATE
1818 'DELETE FROM pay_us_rpt_totals
1819 WHERE attribute1 = ''IPED9''';
1820 RETURN (TRUE);
1821 END AFTERREPORT;
1822
1823 FUNCTION LINE_NUM_P RETURN NUMBER IS
1824 BEGIN
1825 RETURN LINE_NUM;
1826 END LINE_NUM_P;
1827
1828 FUNCTION LAST_LINENO_P RETURN NUMBER IS
1829 BEGIN
1830 RETURN LAST_LINENO;
1831 END LAST_LINENO_P;
1832
1833 FUNCTION TOTREPORTTITLE_P RETURN VARCHAR2 IS
1834 BEGIN
1835 RETURN TOTREPORTTITLE;
1836 END TOTREPORTTITLE_P;
1837
1838 FUNCTION CP_FR_P RETURN VARCHAR2 IS
1839 BEGIN
1840 RETURN CP_FR;
1841 END CP_FR_P;
1842
1843 FUNCTION CP_FT_P RETURN VARCHAR2 IS
1844 BEGIN
1845 RETURN CP_FT;
1846 END CP_FT_P;
1847
1848 FUNCTION CP_PR_P RETURN VARCHAR2 IS
1849 BEGIN
1850 RETURN CP_PR;
1851 END CP_PR_P;
1852
1853 FUNCTION CP_PT_P RETURN VARCHAR2 IS
1854 BEGIN
1855 RETURN CP_PT;
1856 END CP_PT_P;
1857
1858 FUNCTION CP_HIRE_DATE_P RETURN DATE IS
1859 BEGIN
1860 RETURN CP_HIRE_DATE;
1861 END CP_HIRE_DATE_P;
1862
1863 END PQH_PQIPED9_XMLP_PKG;