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