[Home] [Help]
PACKAGE BODY: APPS.PQH_PQIPED5_XMLP_PKG
Source
1 PACKAGE BODY PQH_PQIPED5_XMLP_PKG AS
2 /* $Header: PQIPED5B.pls 120.2 2008/03/18 10:57:35 amakrish noship $ */
3
4 FUNCTION CF_1FORMULA RETURN NUMBER IS
5 TEMP_NUM NUMBER;
6 BEGIN
7 TEMP_NUM := LINE_NUM;
8 LINE_NUM := LINE_NUM + 1;
9 IF LINE_NUM = 9 THEN
10 LINE_NUM := 10;
11 END IF;
12 RETURN TEMP_NUM;
13 END CF_1FORMULA;
14
15 FUNCTION SUMNRMENPERREPORTFORMULA(SUM_GT9_NRMEN IN NUMBER
16 ,SUM_LT9_NRMEN IN NUMBER) RETURN NUMBER IS
17 BEGIN
18 RETURN (SUM_GT9_NRMEN + SUM_LT9_NRMEN);
19 END SUMNRMENPERREPORTFORMULA;
20
21 FUNCTION SUMNRWMENPERREPORTFORMULA(SUM_GT9_NRWMEN IN NUMBER
22 ,SUM_LT9_NRWMEN IN NUMBER) RETURN NUMBER IS
23 BEGIN
24 RETURN (SUM_GT9_NRWMEN + SUM_LT9_NRWMEN);
25 END SUMNRWMENPERREPORTFORMULA;
26
27 FUNCTION SUMBNHMENPERREPORTFORMULA(SUM_GT9_BNHMEN IN NUMBER
28 ,SUM_LT9_BNHMEN IN NUMBER) RETURN NUMBER IS
29 BEGIN
30 RETURN (SUM_GT9_BNHMEN + SUM_LT9_BNHMEN);
31 END SUMBNHMENPERREPORTFORMULA;
32
33 FUNCTION SUMBNHWMENPERREPORTFORMULA(SUM_GT9_BNHWMEN IN NUMBER
34 ,SUM_LT9_BNHWMEN IN NUMBER) RETURN NUMBER IS
35 BEGIN
36 RETURN (SUM_GT9_BNHWMEN + SUM_LT9_BNHWMEN);
37 END SUMBNHWMENPERREPORTFORMULA;
38
39 FUNCTION SUMAM_ALMENPERREPORTFORMULA(SUM_GT9_AM_ALMEN IN NUMBER
40 ,SUM_LT9_AM_ALMEN IN NUMBER) RETURN NUMBER IS
41 BEGIN
42 RETURN (SUM_GT9_AM_ALMEN + SUM_LT9_AM_ALMEN);
43 END SUMAM_ALMENPERREPORTFORMULA;
44
45 FUNCTION SUMAM_ALWMENPERREPORTFORMULA(SUM_GT9_AM_ALWMEN IN NUMBER
46 ,SUM_LT9_AM_ALWMEN IN NUMBER) RETURN NUMBER IS
47 BEGIN
48 RETURN (SUM_GT9_AM_ALWMEN + SUM_LT9_AM_ALWMEN);
49 END SUMAM_ALWMENPERREPORTFORMULA;
50
51 FUNCTION SUMAPMENPERREPORTFORMULA(SUM_GT9_A_PMEN IN NUMBER
52 ,SUM_LT9_APMEN IN NUMBER) RETURN NUMBER IS
53 BEGIN
54 RETURN (SUM_GT9_A_PMEN + SUM_LT9_APMEN);
55 END SUMAPMENPERREPORTFORMULA;
56
57 FUNCTION SUMAPWMENPERREPORTFORMULA(SUM_GT9_A_PWMEN IN NUMBER
58 ,SUM_LT9_APWMEN IN NUMBER) RETURN NUMBER IS
59 BEGIN
60 RETURN (SUM_GT9_A_PWMEN + SUM_LT9_APWMEN);
61 END SUMAPWMENPERREPORTFORMULA;
62
63 FUNCTION SUMHMENPERREPORTFORMULA(SUM_GT9_HMEN IN NUMBER
64 ,SUM_LT9_HMEN IN NUMBER) RETURN NUMBER IS
65 BEGIN
66 RETURN (SUM_GT9_HMEN + SUM_LT9_HMEN);
67 END SUMHMENPERREPORTFORMULA;
68
69 FUNCTION SUMHWMENPERREPORTFORMULA(SUM_GT9_HWMEN IN NUMBER
70 ,SUM_LT9_HWMEN IN NUMBER) RETURN NUMBER IS
71 BEGIN
72 RETURN (SUM_GT9_HWMEN + SUM_LT9_HWMEN);
73 END SUMHWMENPERREPORTFORMULA;
74
75 FUNCTION SUMWNHMENPERREPORTFORMULA(SUM_GT9_WNHMEN IN NUMBER
76 ,SUM_LT9_WNHMEN IN NUMBER) RETURN NUMBER IS
77 BEGIN
78 RETURN (SUM_GT9_WNHMEN + SUM_LT9_WNHMEN);
79 END SUMWNHMENPERREPORTFORMULA;
80
81 FUNCTION SUMWNHWMENPERREPORTFORMULA(SUM_GT9_WNHWMEN IN NUMBER
82 ,SUM_LT9_WNHWMEN IN NUMBER) RETURN NUMBER IS
83 BEGIN
84 RETURN (SUM_GT9_WNHWMEN + SUM_LT9_WNHWMEN);
85 END SUMWNHWMENPERREPORTFORMULA;
86
87 FUNCTION SUMURMENPERREPORTFORMULA(SUM_GT9_URMEN IN NUMBER
88 ,SUM_LT9_URMEN IN NUMBER) RETURN NUMBER IS
89 BEGIN
90 RETURN (SUM_GT9_URMEN + SUM_LT9_URMEN);
91 END SUMURMENPERREPORTFORMULA;
92
93 FUNCTION SUMURWMENPERREPORTFORMULA(SUM_GT9_URWMEN IN NUMBER
94 ,SUM_LT9_URWMEN IN NUMBER) RETURN NUMBER IS
95 BEGIN
96 RETURN (SUM_GT9_URWMEN + SUM_LT9_URWMEN);
97 END SUMURWMENPERREPORTFORMULA;
98
99 FUNCTION SUMTOTMENPERREPORTFORMULA(SUM_GT9_TOTMEN IN NUMBER
100 ,SUM_LT9_TOTMEN IN NUMBER) RETURN NUMBER IS
101 BEGIN
102 RETURN (SUM_GT9_TOTMEN + SUM_LT9_TOTMEN);
103 END SUMTOTMENPERREPORTFORMULA;
104
105 FUNCTION SUMTOTWMENPERREPORTFORMULA(SUM_GT9_TOTWMEN IN NUMBER
106 ,SUM_LT9_TOTWMEN IN NUMBER) RETURN NUMBER IS
107 BEGIN
108 RETURN (SUM_GT9_TOTWMEN + SUM_LT9_TOTWMEN);
109 END SUMTOTWMENPERREPORTFORMULA;
110
111 FUNCTION CF_GROUPTOTTITLEFORMULA(SC IN VARCHAR2) RETURN CHAR IS
112 L_TOTAL_TITLE VARCHAR2(200) := '';
113 L_CONTR_CODE VARCHAR2(9) := SC;
114 BEGIN
115 IF L_CONTR_CODE = '02' THEN
116 L_TOTAL_TITLE := 'Total 9/10 month salary contract (sum of Lines 2-8)';
117 ELSIF L_CONTR_CODE = '03' THEN
118 L_TOTAL_TITLE := 'Total 11/12 month salary contract (sum of Lines 10-16)';
119 END IF;
120 RETURN L_TOTAL_TITLE;
121 END CF_GROUPTOTTITLEFORMULA;
122
123 FUNCTION CF_LINENOGROUPFORMULA(SC IN VARCHAR2) RETURN NUMBER IS
124 TEMP_NUM NUMBER;
125 P_CONTR_CODE VARCHAR2(10) := SC;
126 BEGIN
127 IF P_CONTR_CODE = '02' THEN
128 TEMP_NUM := 9;
129 ELSIF P_CONTR_CODE = '03' THEN
130 TEMP_NUM := 17;
131 END IF;
132 RETURN TEMP_NUM;
133 END CF_LINENOGROUPFORMULA;
134
135 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
136 L_QUERY_TEXT VARCHAR2(2000);
137 L_FR VARCHAR2(2000);
138 L_FT VARCHAR2(2000);
139 L_PR VARCHAR2(2000);
140 L_PT VARCHAR2(2000);
141 LINE VARCHAR2(1);
142 SC VARCHAR2(2000);
143 SALARY_RANGE VARCHAR2(2000);
144 L_NR_MEN NUMBER(10) := 0;
145 L_NR_WMEN NUMBER(10) := 0;
146 L_BNH_MEN NUMBER(10) := 0;
147 L_BNH_WMEN NUMBER(10) := 0;
148 L_AMAI_MEN NUMBER(10) := 0;
149 L_AMAI_WMEN NUMBER(10) := 0;
150 L_AP_MEN NUMBER(10) := 0;
151 L_AP_WMEN NUMBER(10) := 0;
152 L_H_MEN NUMBER(10) := 0;
153 L_H_WMEN NUMBER(10) := 0;
154 L_WNH_MEN NUMBER(10) := 0;
155 L_WNH_WMEN NUMBER(10) := 0;
156 L_UR_MEN NUMBER(10) := 0;
157 L_UR_WMEN NUMBER(10) := 0;
158 L_TOT_MEN NUMBER(10) := 0;
159 L_TOT_WMEN NUMBER(10) := 0;
160 L_TMR_BNH_MEN NUMBER(10) := 0;
161 L_TMR_BNH_WMEN NUMBER(10) := 0;
162 L_TMR_AMAI_MEN NUMBER(10) := 0;
163 L_TMR_AMAI_WMEN NUMBER(10) := 0;
164 L_TMR_AP_MEN NUMBER(10) := 0;
165 L_TMR_AP_WMEN NUMBER(10) := 0;
166 L_TMR_H_MEN NUMBER(10) := 0;
167 L_TMR_H_WMEN NUMBER(10) := 0;
168 L_TMR_WNH_MEN NUMBER(10) := 0;
169 L_TMR_WNH_WMEN NUMBER(10) := 0;
170 L_TMR_UR_MEN NUMBER(10) := 0;
171 L_TMR_UR_WMEN NUMBER(10) := 0;
172 L_DURATION1 NUMBER(5) := 9;
173 L_DURATION2 NUMBER(5) := 10;
174 CURSOR GET_LINE1_COUNTS(C_DURATION1 IN NUMBER,C_DURATION2 IN NUMBER) IS
175 SELECT
176 1 LINE,
177 DECODE(C_DURATION1
178 ,9
179 ,'02'
180 ,11
181 ,'03') SC,
182 PQH_SALARY_CLASS_INTERVALS_PKG.GET_SALARY_INTERVAL(PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(ASS.EMPLOYMENT_CATEGORY
183 ,CP_FR
184 ,CP_FT
185 ,CP_PR
186 ,CP_PT)
187 ,NVL(PPP.PROPOSED_SALARY_N
188 ,0) * PPB.PAY_ANNUALIZATION_FACTOR) SALARY_RANGE,
189 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
190 ,P_REPORT_DATE)
191 ,'1'
192 ,DECODE(PEO.SEX
193 ,'M'
194 ,1
195 ,NULL)
196 ,NULL)) NRMEN,
197 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
198 ,P_REPORT_DATE)
199 ,'1'
200 ,DECODE(PEO.SEX
201 ,'F'
202 ,1
203 ,NULL)
204 ,NULL)) NRWMEN,
205 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
206 ,P_REPORT_DATE)
207 ,NULL
208 ,(DECODE(PEO.PER_INFORMATION1
209 ,'2'
210 ,DECODE(PEO.SEX
211 ,'M'
212 ,1
213 ,NULL)
214 ,NULL)))) BNHMEN,
215 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
216 ,P_REPORT_DATE)
217 ,NULL
218 ,(DECODE(PEO.PER_INFORMATION1
219 ,'2'
220 ,DECODE(PEO.SEX
221 ,'F'
222 ,1
223 ,NULL)
224 ,NULL)))) BNHWMEN,
225 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
226 ,P_REPORT_DATE)
227 ,NULL
228 ,(DECODE(PEO.PER_INFORMATION1
229 ,'6'
230 ,DECODE(PEO.SEX
231 ,'M'
232 ,1
233 ,NULL)
234 ,NULL)))) AM_ALMEN,
235 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
236 ,P_REPORT_DATE)
237 ,NULL
238 ,(DECODE(PEO.PER_INFORMATION1
239 ,'6'
240 ,DECODE(PEO.SEX
241 ,'F'
242 ,1
243 ,NULL)
244 ,NULL)))) AM_ALWMEN,
245 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
246 ,P_REPORT_DATE)
247 ,NULL
248 ,(DECODE(PEO.PER_INFORMATION1
249 ,'4'
250 ,DECODE(PEO.SEX
251 ,'M'
252 ,1
253 ,NULL)
254 ,'5'
255 ,DECODE(PEO.SEX
256 ,'M'
257 ,1
258 ,NULL)
259 ,NULL)))) A_PMEN,
260 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
261 ,P_REPORT_DATE)
262 ,NULL
263 ,(DECODE(PEO.PER_INFORMATION1
264 ,'4'
265 ,DECODE(PEO.SEX
266 ,'F'
267 ,1
268 ,NULL)
269 ,'5'
270 ,DECODE(PEO.SEX
271 ,'F'
272 ,1
273 ,NULL)
274 ,NULL)))) A_PWMEN,
275 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
276 ,P_REPORT_DATE)
277 ,NULL
278 ,(DECODE(PEO.PER_INFORMATION1
279 ,'3'
280 ,DECODE(PEO.SEX
281 ,'M'
282 ,1
283 ,NULL)
284 ,'9'
285 ,DECODE(PEO.SEX
286 ,'M'
287 ,1
288 ,NULL)
289 ,NULL)))) HMEN,
290 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
291 ,P_REPORT_DATE)
292 ,NULL
293 ,(DECODE(PEO.PER_INFORMATION1
294 ,'3'
295 ,DECODE(PEO.SEX
296 ,'F'
297 ,1
298 ,NULL)
299 ,'9'
300 ,DECODE(PEO.SEX
301 ,'F'
302 ,1
303 ,NULL)
304 ,NULL)))) HWMEN,
305 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
306 ,P_REPORT_DATE)
307 ,NULL
308 ,(DECODE(PEO.PER_INFORMATION1
309 ,'1'
310 ,DECODE(PEO.SEX
311 ,'M'
312 ,1
313 ,NULL)
314 ,NULL)))) WNHMEN,
315 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
316 ,P_REPORT_DATE)
317 ,NULL
318 ,(DECODE(PEO.PER_INFORMATION1
319 ,'1'
320 ,DECODE(PEO.SEX
321 ,'F'
322 ,1
323 ,NULL)
324 ,NULL)))) WNHWMEN,
325 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
326 ,P_REPORT_DATE)
327 ,NULL
328 ,(DECODE(PEO.PER_INFORMATION1
329 ,NULL
330 ,DECODE(PEO.SEX
331 ,'M'
332 ,1
333 ,NULL)
334 ,NULL))
335 ,NULL)) URMEN,
336 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
337 ,P_REPORT_DATE)
338 ,NULL
339 ,(DECODE(PEO.PER_INFORMATION1
340 ,NULL
341 ,DECODE(PEO.SEX
342 ,'F'
343 ,1
344 ,NULL)
345 ,NULL))
346 ,NULL)) URWMEN
347 FROM
348 PER_ALL_PEOPLE_F PEO,
349 PER_ALL_ASSIGNMENTS_F ASS,
350 PER_ASSIGNMENT_STATUS_TYPES AST,
351 PER_CONTRACTS_F PCO,
352 PER_PAY_PROPOSALS PPP,
353 PER_PAY_BASES PPB,
354 HR_LOOKUPS HL,
355 PER_JOBS JOB,
356 PER_SHARED_TYPES PST,
357 PER_SHARED_TYPES PST1
358 WHERE PEO.PERSON_ID = ASS.PERSON_ID
359 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
360 AND PCO.PERSON_ID = PEO.PERSON_ID
361 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(ASS.EMPLOYMENT_CATEGORY
362 ,CP_FR
363 ,CP_FT
364 ,CP_PR
365 ,CP_PT) IN ( 'FR' )
366 AND ASS.PAY_BASIS_ID = PPB.PAY_BASIS_ID
367 AND ASS.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
368 AND PPP.CHANGE_DATE = (
369 SELECT
370 MAX(CHANGE_DATE)
371 FROM
372 PER_PAY_PROPOSALS PRO
373 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
374 AND PRO.CHANGE_DATE <= P_REPORT_DATE
375 AND PRO.APPROVED = 'Y' )
376 AND NVL(PPP.PROPOSED_SALARY_N
377 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
378 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
379 AND PEO.EFFECTIVE_END_DATE
380 AND P_REPORT_DATE BETWEEN ASS.EFFECTIVE_START_DATE
381 AND ASS.EFFECTIVE_END_DATE
382 AND P_REPORT_DATE BETWEEN PCO.EFFECTIVE_START_DATE
383 AND PCO.EFFECTIVE_END_DATE
384 AND ASS.PRIMARY_FLAG = 'Y'
385 AND ASS.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
386 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
387 AND ASS.ASSIGNMENT_TYPE = 'E'
388 AND PCO.TYPE = 'FULL_TIME'
389 AND PCO.STATUS = PST.SYSTEM_TYPE_CD
390 AND PST.LOOKUP_TYPE = 'CONTRACT_STATUS'
391 AND pst1.system_type_cd (+) = PST.SYSTEM_TYPE_CD
392 AND pst1.lookup_type (+) = PST.LOOKUP_TYPE
393 AND PST.BUSINESS_GROUP_ID is null
394 AND pst1.business_group_id (+) = P_BUSINESS_GROUP_ID
395 AND NVL(PST1.INFORMATION1
396 ,PST.INFORMATION1) = 'Y'
397 AND PQH_EMPLOYMENT_CATEGORY.GET_DURATION_IN_MONTHS(PCO.DURATION
398 ,PCO.DURATION_UNITS
399 ,PCO.BUSINESS_GROUP_ID
400 ,P_REPORT_DATE) BETWEEN C_DURATION1
401 AND C_DURATION2
402 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
403 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
404 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
405 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
406 AND ASS.JOB_ID = JOB.JOB_ID
407 AND ASS.ORGANIZATION_ID in (
408 SELECT
409 ORG.ORGANIZATION_ID
410 FROM
411 HR_ALL_ORGANIZATION_UNITS ORG
412 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
413 AND PQH_INST_TYPE_PKG.GET_INST_TYPE(ORG.ORGANIZATION_ID) = 'NON-MED' )
414 GROUP BY
415 PQH_SALARY_CLASS_INTERVALS_PKG.GET_SALARY_INTERVAL(PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(ASS.EMPLOYMENT_CATEGORY
416 ,CP_FR
417 ,CP_FT
418 ,CP_PR
419 ,CP_PT)
420 ,NVL(PPP.PROPOSED_SALARY_N
421 ,0) * PPB.PAY_ANNUALIZATION_FACTOR);
422 CURSOR GET_LINE1_TMRACES_COUNTS(C_SALARY_RANGE IN VARCHAR2,C_DURATION1 IN NUMBER,C_DURATION2 IN NUMBER) IS
423 SELECT
424 COUNT(DECODE(PEI.PEI_INFORMATION5
425 ,'2'
426 ,DECODE(PEO.SEX
427 ,'M'
428 ,1
429 ,NULL)
430 ,NULL)) BNHMEN,
431 COUNT(DECODE(PEI.PEI_INFORMATION5
432 ,'2'
433 ,DECODE(PEO.SEX
434 ,'F'
435 ,1
436 ,NULL)
437 ,NULL)) BNHWMEN,
438 COUNT(DECODE(PEI.PEI_INFORMATION5
439 ,'6'
440 ,DECODE(PEO.SEX
441 ,'M'
442 ,1
443 ,NULL)
444 ,NULL)) AM_ALMEN,
445 COUNT(DECODE(PEI.PEI_INFORMATION5
446 ,'6'
447 ,DECODE(PEO.SEX
448 ,'F'
449 ,1
450 ,NULL)
451 ,NULL)) AM_ALWMEN,
452 COUNT(DECODE(PEI.PEI_INFORMATION5
453 ,'4'
454 ,DECODE(PEO.SEX
455 ,'M'
456 ,1
457 ,NULL)
458 ,'5'
459 ,DECODE(PEO.SEX
460 ,'M'
461 ,1
462 ,NULL)
463 ,NULL)) A_PMEN,
464 COUNT(DECODE(PEI.PEI_INFORMATION5
465 ,'4'
466 ,DECODE(PEO.SEX
467 ,'F'
468 ,1
469 ,NULL)
470 ,'5'
471 ,DECODE(PEO.SEX
472 ,'F'
473 ,1
474 ,NULL)
475 ,NULL)) A_PWMEN,
476 COUNT(DECODE(PEI.PEI_INFORMATION5
477 ,'3'
478 ,DECODE(PEO.SEX
479 ,'M'
480 ,1
481 ,NULL)
482 ,'9'
483 ,DECODE(PEO.SEX
484 ,'M'
485 ,1
486 ,NULL)
487 ,NULL)) HMEN,
488 COUNT(DECODE(PEI.PEI_INFORMATION5
489 ,'3'
490 ,DECODE(PEO.SEX
491 ,'F'
492 ,1
493 ,NULL)
494 ,'9'
495 ,DECODE(PEO.SEX
496 ,'F'
497 ,1
498 ,NULL)
499 ,NULL)) HWMEN,
500 COUNT(DECODE(PEI.PEI_INFORMATION5
501 ,'1'
502 ,DECODE(PEO.SEX
503 ,'M'
504 ,1
505 ,NULL)
506 ,NULL)) WNHMEN,
507 COUNT(DECODE(PEI.PEI_INFORMATION5
508 ,'1'
509 ,DECODE(PEO.SEX
510 ,'F'
511 ,1
512 ,NULL)
513 ,NULL)) WNHWMEN,
514 COUNT(DECODE(PEI.PEI_INFORMATION5
515 ,NULL
516 ,DECODE(PEO.SEX
517 ,'M'
518 ,1
519 ,NULL)
520 ,NULL)) URMEN,
521 COUNT(DECODE(PEI.PEI_INFORMATION5
522 ,NULL
523 ,DECODE(PEO.SEX
524 ,'F'
525 ,1
526 ,NULL)
527 ,NULL)) URWMEN
528 FROM
529 PER_ALL_PEOPLE_F PEO,
530 PER_ALL_ASSIGNMENTS_F ASS,
531 PER_ASSIGNMENT_STATUS_TYPES AST,
532 PER_CONTRACTS_F PCO,
533 PER_PAY_PROPOSALS PPP,
534 PER_PAY_BASES PPB,
535 HR_LOOKUPS HL,
536 PER_JOBS JOB,
537 PER_SHARED_TYPES PST,
538 PER_SHARED_TYPES PST1,
539 PER_PEOPLE_EXTRA_INFO PEI
540 WHERE PEO.PERSON_ID = ASS.PERSON_ID
541 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
542 AND PEO.PER_INFORMATION1 = '13'
543 AND PEO.PERSON_ID = pei.person_id (+)
544 AND ( PEI.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
545 OR ( PEI.INFORMATION_TYPE <> 'PER_US_ADDL_ETHNIC_CAT'
546 AND not exists (
547 SELECT
548 1
549 FROM
550 PER_PEOPLE_EXTRA_INFO PEI2
551 WHERE PEI2.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
552 AND PEI2.PERSON_ID = PEI.PERSON_ID )
553 AND PEI.PERSON_EXTRA_INFO_ID = (
554 SELECT
555 MAX(PEI1.PERSON_EXTRA_INFO_ID)
556 FROM
557 PER_PEOPLE_EXTRA_INFO PEI1
558 WHERE PEI1.PERSON_ID = PEI.PERSON_ID ) )
559 OR ( not exists (
560 SELECT
561 PERSON_EXTRA_INFO_ID
562 FROM
563 PER_PEOPLE_EXTRA_INFO PEI3
564 WHERE PEI3.PERSON_ID = PEI.PERSON_ID ) ) )
565 AND PCO.PERSON_ID = PEO.PERSON_ID
566 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(ASS.EMPLOYMENT_CATEGORY
567 ,CP_FR
568 ,CP_FT
569 ,CP_PR
570 ,CP_PT) IN ( 'FR' )
571 AND ASS.PAY_BASIS_ID = PPB.PAY_BASIS_ID
572 AND ASS.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
573 AND PPP.CHANGE_DATE = (
574 SELECT
575 MAX(CHANGE_DATE)
576 FROM
577 PER_PAY_PROPOSALS PRO
578 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
579 AND PRO.CHANGE_DATE <= P_REPORT_DATE
580 AND PRO.APPROVED = 'Y' )
581 AND NVL(PPP.PROPOSED_SALARY_N
582 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
583 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
584 AND PEO.EFFECTIVE_END_DATE
585 AND P_REPORT_DATE BETWEEN ASS.EFFECTIVE_START_DATE
586 AND ASS.EFFECTIVE_END_DATE
587 AND P_REPORT_DATE BETWEEN PCO.EFFECTIVE_START_DATE
588 AND PCO.EFFECTIVE_END_DATE
589 AND ASS.PRIMARY_FLAG = 'Y'
590 AND ASS.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
591 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
592 AND ASS.ASSIGNMENT_TYPE = 'E'
593 AND PCO.TYPE = 'FULL_TIME'
594 AND PCO.STATUS = PST.SYSTEM_TYPE_CD
595 AND PST.LOOKUP_TYPE = 'CONTRACT_STATUS'
596 AND pst1.system_type_cd (+) = PST.SYSTEM_TYPE_CD
597 AND pst1.lookup_type (+) = PST.LOOKUP_TYPE
598 AND PST.BUSINESS_GROUP_ID is null
599 AND pst1.business_group_id (+) = P_BUSINESS_GROUP_ID
600 AND NVL(PST1.INFORMATION1
601 ,PST.INFORMATION1) = 'Y'
602 AND PQH_EMPLOYMENT_CATEGORY.GET_DURATION_IN_MONTHS(PCO.DURATION
603 ,PCO.DURATION_UNITS
604 ,PCO.BUSINESS_GROUP_ID
605 ,P_REPORT_DATE) BETWEEN C_DURATION1
606 AND C_DURATION2
607 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
608 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
609 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
610 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
611 AND ASS.JOB_ID = JOB.JOB_ID
612 AND ASS.ORGANIZATION_ID in (
613 SELECT
614 ORG.ORGANIZATION_ID
615 FROM
616 HR_ALL_ORGANIZATION_UNITS ORG
617 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
618 AND PQH_INST_TYPE_PKG.GET_INST_TYPE(ORG.ORGANIZATION_ID) = 'NON-MED' )
619 AND PQH_SALARY_CLASS_INTERVALS_PKG.GET_SALARY_INTERVAL(PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(ASS.EMPLOYMENT_CATEGORY
620 ,CP_FR
621 ,CP_FT
622 ,CP_PR
623 ,CP_PT)
624 ,NVL(PPP.PROPOSED_SALARY_N
625 ,0) * PPB.PAY_ANNUALIZATION_FACTOR) = C_SALARY_RANGE;
626 CURSOR GET_LINE2_COUNTS IS
627 SELECT
628 1 LINE,
629 'Less than 9 months' DISP_NAME,
630 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
631 ,P_REPORT_DATE)
632 ,'1'
633 ,DECODE(PEO.SEX
634 ,'M'
635 ,1
636 ,NULL)
637 ,NULL)) NRMEN,
638 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
639 ,P_REPORT_DATE)
640 ,'1'
641 ,DECODE(PEO.SEX
642 ,'F'
643 ,1
644 ,NULL)
645 ,NULL)) NRWMEN,
646 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
647 ,P_REPORT_DATE)
648 ,NULL
649 ,(DECODE(PEO.PER_INFORMATION1
650 ,'2'
651 ,DECODE(PEO.SEX
652 ,'M'
653 ,1
654 ,NULL)
655 ,NULL)))) BNHMEN,
656 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
657 ,P_REPORT_DATE)
658 ,NULL
659 ,(DECODE(PEO.PER_INFORMATION1
660 ,'2'
661 ,DECODE(PEO.SEX
662 ,'F'
663 ,1
664 ,NULL)
665 ,NULL)))) BNHWMEN,
666 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
667 ,P_REPORT_DATE)
668 ,NULL
669 ,(DECODE(PEO.PER_INFORMATION1
670 ,'6'
671 ,DECODE(PEO.SEX
672 ,'M'
673 ,1
674 ,NULL)
675 ,NULL)))) AM_ALMEN,
676 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
677 ,P_REPORT_DATE)
678 ,NULL
679 ,(DECODE(PEO.PER_INFORMATION1
680 ,'6'
681 ,DECODE(PEO.SEX
682 ,'F'
683 ,1
684 ,NULL)
685 ,NULL)))) AM_ALWMEN,
686 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
687 ,P_REPORT_DATE)
688 ,NULL
689 ,(DECODE(PEO.PER_INFORMATION1
690 ,'4'
691 ,DECODE(PEO.SEX
692 ,'M'
693 ,1
694 ,NULL)
695 ,'5'
696 ,DECODE(PEO.SEX
697 ,'M'
698 ,1
699 ,NULL)
700 ,NULL)))) A_PMEN,
701 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
702 ,P_REPORT_DATE)
703 ,NULL
704 ,(DECODE(PEO.PER_INFORMATION1
705 ,'4'
706 ,DECODE(PEO.SEX
707 ,'F'
708 ,1
709 ,NULL)
710 ,'5'
711 ,DECODE(PEO.SEX
712 ,'F'
713 ,1
714 ,NULL)
715 ,NULL)))) A_PWMEN,
716 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
717 ,P_REPORT_DATE)
718 ,NULL
719 ,(DECODE(PEO.PER_INFORMATION1
720 ,'3'
721 ,DECODE(PEO.SEX
722 ,'M'
723 ,1
724 ,NULL)
725 ,'9'
726 ,DECODE(PEO.SEX
727 ,'M'
728 ,1
729 ,NULL)
730 ,NULL)))) HMEN,
731 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
732 ,P_REPORT_DATE)
733 ,NULL
734 ,(DECODE(PEO.PER_INFORMATION1
735 ,'3'
736 ,DECODE(PEO.SEX
737 ,'F'
738 ,1
739 ,NULL)
740 ,'9'
741 ,DECODE(PEO.SEX
742 ,'F'
743 ,1
744 ,NULL)
745 ,NULL)))) HWMEN,
746 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
747 ,P_REPORT_DATE)
748 ,NULL
749 ,(DECODE(PEO.PER_INFORMATION1
750 ,'1'
751 ,DECODE(PEO.SEX
752 ,'M'
753 ,1
754 ,NULL)
755 ,NULL)))) WNHMEN,
756 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
757 ,P_REPORT_DATE)
758 ,NULL
759 ,(DECODE(PEO.PER_INFORMATION1
760 ,'1'
761 ,DECODE(PEO.SEX
762 ,'F'
763 ,1
764 ,NULL)
765 ,NULL)))) WNHWMEN,
766 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
767 ,P_REPORT_DATE)
768 ,NULL
769 ,(DECODE(PEO.PER_INFORMATION1
770 ,NULL
771 ,DECODE(PEO.SEX
772 ,'M'
773 ,1
774 ,NULL)
775 ,NULL)))) URMEN,
776 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
777 ,P_REPORT_DATE)
778 ,NULL
779 ,(DECODE(PEO.PER_INFORMATION1
780 ,NULL
781 ,DECODE(PEO.SEX
782 ,'F'
783 ,1
784 ,NULL)
785 ,NULL)))) URWMEN
786 FROM
787 PER_ALL_PEOPLE_F PEO,
788 PER_ALL_ASSIGNMENTS_F ASS,
789 PER_ASSIGNMENT_STATUS_TYPES AST,
790 PER_CONTRACTS_F PCO,
791 PER_PAY_PROPOSALS PPP,
792 PER_PAY_BASES PPB,
793 HR_LOOKUPS HL,
794 PER_JOBS JOB,
795 PER_SHARED_TYPES PST,
796 PER_SHARED_TYPES PST1
797 WHERE PEO.PERSON_ID = ASS.PERSON_ID
798 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
799 AND PCO.PERSON_ID = PEO.PERSON_ID
800 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(ASS.EMPLOYMENT_CATEGORY
801 ,CP_FR
802 ,CP_FT
803 ,CP_PR
804 ,CP_PT) IN ( 'FR' )
805 AND ASS.PAY_BASIS_ID = PPB.PAY_BASIS_ID
806 AND ASS.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
807 AND PPP.CHANGE_DATE = (
808 SELECT
809 MAX(CHANGE_DATE)
810 FROM
811 PER_PAY_PROPOSALS PRO
812 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
813 AND PRO.CHANGE_DATE <= P_REPORT_DATE
814 AND PRO.APPROVED = 'Y' )
815 AND NVL(PPP.PROPOSED_SALARY_N
816 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
817 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
818 AND PEO.EFFECTIVE_END_DATE
819 AND P_REPORT_DATE BETWEEN ASS.EFFECTIVE_START_DATE
820 AND ASS.EFFECTIVE_END_DATE
821 AND P_REPORT_DATE BETWEEN PCO.EFFECTIVE_START_DATE
822 AND PCO.EFFECTIVE_END_DATE
823 AND ASS.PRIMARY_FLAG = 'Y'
824 AND ASS.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
825 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
826 AND ASS.ASSIGNMENT_TYPE = 'E'
827 AND PCO.TYPE = 'FULL_TIME'
828 AND PCO.STATUS = PST.SYSTEM_TYPE_CD
829 AND PST.LOOKUP_TYPE = 'CONTRACT_STATUS'
830 AND pst1.system_type_cd (+) = PST.SYSTEM_TYPE_CD
831 AND pst1.lookup_type (+) = PST.LOOKUP_TYPE
832 AND PST.BUSINESS_GROUP_ID is null
833 AND pst1.business_group_id (+) = P_BUSINESS_GROUP_ID
834 AND NVL(PST1.INFORMATION1
835 ,PST.INFORMATION1) = 'Y'
836 AND PQH_EMPLOYMENT_CATEGORY.GET_DURATION_IN_MONTHS(PCO.DURATION
837 ,PCO.DURATION_UNITS
838 ,PCO.BUSINESS_GROUP_ID
839 ,P_REPORT_DATE) < 9
840 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
841 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
842 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
843 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
844 AND ASS.JOB_ID = JOB.JOB_ID
845 AND ASS.ORGANIZATION_ID in (
846 SELECT
847 ORG.ORGANIZATION_ID
848 FROM
849 HR_ALL_ORGANIZATION_UNITS ORG
850 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
851 AND PQH_INST_TYPE_PKG.GET_INST_TYPE(ORG.ORGANIZATION_ID) = 'NON-MED' );
852 CURSOR GET_TMR_LINE2_COUNTS IS
853 SELECT
854 COUNT(DECODE(PEI.PEI_INFORMATION5
855 ,'2'
856 ,DECODE(PEO.SEX
857 ,'M'
858 ,1
859 ,NULL)
860 ,NULL)) BNHMEN,
861 COUNT(DECODE(PEI.PEI_INFORMATION5
862 ,'2'
863 ,DECODE(PEO.SEX
864 ,'F'
865 ,1
866 ,NULL)
867 ,NULL)) BNHWMEN,
868 COUNT(DECODE(PEI.PEI_INFORMATION5
869 ,'6'
870 ,DECODE(PEO.SEX
871 ,'M'
872 ,1
873 ,NULL)
874 ,NULL)) AM_ALMEN,
875 COUNT(DECODE(PEI.PEI_INFORMATION5
876 ,'6'
877 ,DECODE(PEO.SEX
878 ,'F'
879 ,1
880 ,NULL)
881 ,NULL)) AM_ALWMEN,
882 COUNT(DECODE(PEI.PEI_INFORMATION5
883 ,'4'
884 ,DECODE(PEO.SEX
885 ,'M'
886 ,1
887 ,NULL)
888 ,'5'
889 ,DECODE(PEO.SEX
890 ,'M'
891 ,1
892 ,NULL)
893 ,NULL)) A_PMEN,
894 COUNT(DECODE(PEI.PEI_INFORMATION5
895 ,'4'
896 ,DECODE(PEO.SEX
897 ,'F'
898 ,1
899 ,NULL)
900 ,'5'
901 ,DECODE(PEO.SEX
902 ,'F'
903 ,1
904 ,NULL)
905 ,NULL)) A_PWMEN,
906 COUNT(DECODE(PEI.PEI_INFORMATION5
907 ,'3'
908 ,DECODE(PEO.SEX
909 ,'M'
910 ,1
911 ,NULL)
912 ,'9'
913 ,DECODE(PEO.SEX
914 ,'M'
915 ,1
916 ,NULL)
917 ,NULL)) HMEN,
918 COUNT(DECODE(PEI.PEI_INFORMATION5
919 ,'3'
920 ,DECODE(PEO.SEX
921 ,'F'
922 ,1
923 ,NULL)
924 ,'9'
925 ,DECODE(PEO.SEX
926 ,'F'
927 ,1
928 ,NULL)
929 ,NULL)) HWMEN,
930 COUNT(DECODE(PEI.PEI_INFORMATION5
931 ,'1'
932 ,DECODE(PEO.SEX
933 ,'M'
934 ,1
935 ,NULL)
936 ,NULL)) WNHMEN,
937 COUNT(DECODE(PEI.PEI_INFORMATION5
938 ,'1'
939 ,DECODE(PEO.SEX
940 ,'F'
941 ,1
942 ,NULL)
943 ,NULL)) WNHWMEN,
944 COUNT(DECODE(PEI.PEI_INFORMATION5
945 ,NULL
946 ,DECODE(PEO.SEX
947 ,'M'
948 ,1
949 ,NULL)
950 ,NULL)) URMEN,
951 COUNT(DECODE(PEI.PEI_INFORMATION5
952 ,NULL
953 ,DECODE(PEO.SEX
954 ,'F'
955 ,1
956 ,NULL)
957 ,NULL)) URWMEN
958 FROM
959 PER_ALL_PEOPLE_F PEO,
960 PER_ALL_ASSIGNMENTS_F ASS,
961 PER_ASSIGNMENT_STATUS_TYPES AST,
962 PER_CONTRACTS_F PCO,
963 PER_PAY_PROPOSALS PPP,
964 PER_PAY_BASES PPB,
965 HR_LOOKUPS HL,
966 PER_JOBS JOB,
967 PER_SHARED_TYPES PST,
968 PER_SHARED_TYPES PST1,
969 PER_PEOPLE_EXTRA_INFO PEI
970 WHERE PEO.PERSON_ID = ASS.PERSON_ID
971 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
972 AND PEO.PER_INFORMATION1 = '13'
973 AND PEO.PERSON_ID = pei.person_id (+)
974 AND ( PEI.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
975 OR ( PEI.INFORMATION_TYPE <> 'PER_US_ADDL_ETHNIC_CAT'
976 AND not exists (
977 SELECT
978 1
979 FROM
980 PER_PEOPLE_EXTRA_INFO PEI2
981 WHERE PEI2.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
982 AND PEI2.PERSON_ID = PEI.PERSON_ID )
983 AND PEI.PERSON_EXTRA_INFO_ID = (
984 SELECT
985 MAX(PEI1.PERSON_EXTRA_INFO_ID)
986 FROM
987 PER_PEOPLE_EXTRA_INFO PEI1
988 WHERE PEI1.PERSON_ID = PEI.PERSON_ID ) )
989 OR ( not exists (
990 SELECT
991 PERSON_EXTRA_INFO_ID
992 FROM
993 PER_PEOPLE_EXTRA_INFO PEI3
994 WHERE PEI3.PERSON_ID = PEI.PERSON_ID ) ) )
995 AND PCO.PERSON_ID = PEO.PERSON_ID
996 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(ASS.EMPLOYMENT_CATEGORY
997 ,CP_FR
998 ,CP_FT
999 ,CP_PR
1000 ,CP_PT) IN ( 'FR' )
1001 AND ASS.PAY_BASIS_ID = PPB.PAY_BASIS_ID
1002 AND ASS.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
1003 AND PPP.CHANGE_DATE = (
1004 SELECT
1005 MAX(CHANGE_DATE)
1006 FROM
1007 PER_PAY_PROPOSALS PRO
1008 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
1009 AND PRO.CHANGE_DATE <= P_REPORT_DATE
1010 AND PRO.APPROVED = 'Y' )
1011 AND NVL(PPP.PROPOSED_SALARY_N
1012 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
1013 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
1014 AND PEO.EFFECTIVE_END_DATE
1015 AND P_REPORT_DATE BETWEEN ASS.EFFECTIVE_START_DATE
1016 AND ASS.EFFECTIVE_END_DATE
1017 AND P_REPORT_DATE BETWEEN PCO.EFFECTIVE_START_DATE
1018 AND PCO.EFFECTIVE_END_DATE
1019 AND ASS.PRIMARY_FLAG = 'Y'
1020 AND ASS.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
1021 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
1022 AND ASS.ASSIGNMENT_TYPE = 'E'
1023 AND PCO.TYPE = 'FULL_TIME'
1024 AND PCO.STATUS = PST.SYSTEM_TYPE_CD
1025 AND PST.LOOKUP_TYPE = 'CONTRACT_STATUS'
1026 AND pst1.system_type_cd (+) = PST.SYSTEM_TYPE_CD
1027 AND pst1.lookup_type (+) = PST.LOOKUP_TYPE
1028 AND PST.BUSINESS_GROUP_ID is null
1029 AND pst1.business_group_id (+) = P_BUSINESS_GROUP_ID
1030 AND NVL(PST1.INFORMATION1
1031 ,PST.INFORMATION1) = 'Y'
1032 AND PQH_EMPLOYMENT_CATEGORY.GET_DURATION_IN_MONTHS(PCO.DURATION
1033 ,PCO.DURATION_UNITS
1034 ,PCO.BUSINESS_GROUP_ID
1035 ,P_REPORT_DATE) < 9
1036 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
1037 AND JOB.JOB_INFORMATION8 in ( '1' , '2' , '3' )
1038 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
1039 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
1040 AND ASS.JOB_ID = JOB.JOB_ID
1041 AND ASS.ORGANIZATION_ID in (
1042 SELECT
1043 ORG.ORGANIZATION_ID
1044 FROM
1045 HR_ALL_ORGANIZATION_UNITS ORG
1046 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
1047 AND PQH_INST_TYPE_PKG.GET_INST_TYPE(ORG.ORGANIZATION_ID) = 'NON-MED' );
1048 BEGIN
1049 -- HR_STANDARD.EVENT('BEFORE REPORT');
1050 PQH_EMPLOYMENT_CATEGORY.FETCH_EMPL_CATEGORIES(P_BUSINESS_GROUP_ID
1051 ,L_FR
1052 ,L_FT
1053 ,L_PR
1054 ,L_PT);
1055 CP_FR := L_FR;
1056 CP_FT := L_FT;
1057 CP_PR := L_PR;
1058 CP_PT := L_PT;
1059 FOR i IN 1 .. 2 LOOP
1060 IF I = 1 THEN
1061 L_DURATION1 := 9;
1062 L_DURATION2 := 10;
1063 ELSIF I = 2 THEN
1064 L_DURATION1 := 11;
1065 L_DURATION2 := 12;
1066 END IF;
1067 FOR i IN GET_LINE1_COUNTS(l_duration1,l_duration2) LOOP
1068 LINE := I.LINE;
1069 SC := I.SC;
1070 SALARY_RANGE := I.SALARY_RANGE;
1071 L_NR_MEN := I.NRMEN;
1072 L_NR_WMEN := I.NRWMEN;
1073 L_BNH_MEN := I.BNHMEN;
1074 L_BNH_WMEN := I.BNHWMEN;
1075 L_AMAI_MEN := I.AM_ALMEN;
1076 L_AMAI_WMEN := I.AM_ALWMEN;
1077 L_AP_MEN := I.A_PMEN;
1078 L_AP_WMEN := I.A_PWMEN;
1079 L_H_MEN := I.HMEN;
1080 L_H_WMEN := I.HWMEN;
1081 L_WNH_MEN := I.WNHMEN;
1082 L_WNH_WMEN := I.WNHWMEN;
1083 L_UR_MEN := I.URMEN;
1084 L_UR_WMEN := I.URWMEN;
1085 L_TOT_MEN := L_NR_MEN + L_BNH_MEN + L_AMAI_MEN + L_AP_MEN + L_H_MEN + L_WNH_MEN + L_UR_MEN;
1086 L_TOT_WMEN := L_NR_WMEN + L_BNH_WMEN + L_AMAI_WMEN + L_AP_WMEN + L_H_WMEN + L_WNH_WMEN + L_UR_WMEN;
1087 FOR j IN GET_LINE1_TMRACES_COUNTS(salary_range,l_duration1,l_duration2) LOOP
1088 L_BNH_MEN := L_BNH_MEN + J.BNHMEN;
1089 L_BNH_WMEN := L_BNH_WMEN + J.BNHWMEN;
1090 L_AMAI_MEN := L_AMAI_MEN + J.AM_ALMEN;
1091 L_AMAI_WMEN := L_AMAI_WMEN + J.AM_ALWMEN;
1092 L_AP_MEN := L_AP_MEN + J.A_PMEN;
1093 L_AP_WMEN := L_AP_WMEN + J.A_PWMEN;
1094 L_H_MEN := L_H_MEN + J.HMEN;
1095 L_H_WMEN := L_H_WMEN + J.HWMEN;
1096 L_WNH_MEN := L_WNH_MEN + J.WNHMEN;
1097 L_WNH_WMEN := L_WNH_WMEN + J.WNHWMEN;
1098 L_UR_MEN := L_UR_MEN + J.URMEN;
1099 L_UR_WMEN := L_UR_WMEN + J.URWMEN;
1100 L_TOT_MEN := L_TOT_MEN + J.BNHMEN + J.AM_ALMEN + J.A_PMEN + J.HMEN + J.WNHMEN + J.URMEN;
1101 L_TOT_WMEN := L_TOT_WMEN + J.BNHWMEN + J.AM_ALWMEN + J.A_PWMEN + J.HWMEN + J.WNHWMEN + J.URWMEN;
1102 END LOOP;
1103 INSERT INTO PAY_US_RPT_TOTALS
1104 (SESSION_ID
1105 ,ATTRIBUTE1
1106 ,ATTRIBUTE2
1107 ,VALUE1
1108 ,VALUE2
1109 ,VALUE3
1110 ,VALUE4
1111 ,VALUE5
1112 ,VALUE6
1113 ,VALUE7
1114 ,VALUE8
1115 ,VALUE9
1116 ,VALUE10
1117 ,VALUE11
1118 ,VALUE12
1119 ,VALUE13
1120 ,VALUE14
1121 ,VALUE15
1122 ,VALUE16
1123 ,VALUE17
1124 ,VALUE18)
1125 VALUES (USERENV('sessionid')
1126 ,'IPED5'
1127 ,SALARY_RANGE
1128 ,LINE
1129 ,SC
1130 ,L_NR_MEN
1131 ,L_NR_WMEN
1132 ,L_BNH_MEN
1133 ,L_BNH_WMEN
1134 ,L_AMAI_MEN
1135 ,L_AMAI_WMEN
1136 ,L_AP_MEN
1137 ,L_AP_WMEN
1138 ,L_H_MEN
1139 ,L_H_WMEN
1140 ,L_WNH_MEN
1141 ,L_WNH_WMEN
1142 ,L_UR_MEN
1143 ,L_UR_WMEN
1144 ,L_TOT_MEN
1145 ,L_TOT_WMEN);
1146 COMMIT;
1147 END LOOP;
1148 END LOOP;
1149 OPEN GET_LINE2_COUNTS;
1150 FETCH GET_LINE2_COUNTS
1151 INTO LINE,SC,L_NR_MEN,L_NR_WMEN,L_BNH_MEN,L_BNH_WMEN,L_AMAI_MEN,L_AMAI_WMEN,L_AP_MEN,L_AP_WMEN,L_H_MEN,L_H_WMEN,L_WNH_MEN,L_WNH_WMEN,L_UR_MEN,L_UR_WMEN;
1152 CLOSE GET_LINE2_COUNTS;
1153 L_TOT_MEN := L_NR_MEN + L_BNH_MEN + L_AMAI_MEN + L_AP_MEN + L_H_MEN + L_WNH_MEN + L_UR_MEN;
1154 L_TOT_WMEN := L_NR_WMEN + L_BNH_WMEN + L_AMAI_WMEN + L_AP_WMEN + L_H_WMEN + L_WNH_WMEN + L_UR_WMEN;
1155 OPEN GET_TMR_LINE2_COUNTS;
1156 FETCH GET_TMR_LINE2_COUNTS
1157 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;
1158 CLOSE GET_TMR_LINE2_COUNTS;
1159 L_BNH_MEN := L_BNH_MEN + L_TMR_BNH_MEN;
1160 L_BNH_WMEN := L_BNH_WMEN + L_TMR_BNH_WMEN;
1161 L_AMAI_MEN := L_AMAI_MEN + L_TMR_AMAI_MEN;
1162 L_AMAI_WMEN := L_AMAI_WMEN + L_TMR_AMAI_WMEN;
1163 L_AP_MEN := L_AP_MEN + L_TMR_AP_MEN;
1164 L_AP_WMEN := L_AP_WMEN + L_TMR_AP_WMEN;
1165 L_H_MEN := L_H_MEN + L_TMR_H_MEN;
1166 L_H_WMEN := L_H_WMEN + L_TMR_H_WMEN;
1167 L_WNH_MEN := L_WNH_MEN + L_TMR_WNH_MEN;
1168 L_WNH_WMEN := L_WNH_WMEN + L_TMR_WNH_WMEN;
1169 L_UR_MEN := L_UR_MEN + L_TMR_UR_MEN;
1170 L_UR_WMEN := L_UR_WMEN + L_TMR_UR_WMEN;
1171 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;
1172 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;
1173 INSERT INTO PAY_US_RPT_TOTALS
1174 (SESSION_ID
1175 ,ATTRIBUTE1
1176 ,ATTRIBUTE2
1177 ,VALUE1
1178 ,VALUE2
1179 ,VALUE3
1180 ,VALUE4
1181 ,VALUE5
1182 ,VALUE6
1183 ,VALUE7
1184 ,VALUE8
1185 ,VALUE9
1186 ,VALUE10
1187 ,VALUE11
1188 ,VALUE12
1189 ,VALUE13
1190 ,VALUE14
1191 ,VALUE15
1192 ,VALUE16
1193 ,VALUE17)
1194 VALUES (USERENV('sessionid')
1195 ,'IPED5'
1196 ,SC
1197 ,LINE
1198 ,L_NR_MEN
1199 ,L_NR_WMEN
1200 ,L_BNH_MEN
1201 ,L_BNH_WMEN
1202 ,L_AMAI_MEN
1203 ,L_AMAI_WMEN
1204 ,L_AP_MEN
1205 ,L_AP_WMEN
1206 ,L_H_MEN
1207 ,L_H_WMEN
1208 ,L_WNH_MEN
1209 ,L_WNH_WMEN
1210 ,L_UR_MEN
1211 ,L_UR_WMEN
1212 ,L_TOT_MEN
1213 ,L_TOT_WMEN);
1214 COMMIT;
1215 RETURN (TRUE);
1216 END BEFOREREPORT;
1217
1218 FUNCTION AFTERREPORT RETURN BOOLEAN IS
1219 BEGIN
1220 -- HR_STANDARD.EVENT('AFTER REPORT');
1221 EXECUTE IMMEDIATE
1222 'DELETE FROM pay_us_rpt_totals
1223 WHERE attribute1 = ''IPED5''';
1224 RETURN (TRUE);
1225 END AFTERREPORT;
1226
1227 FUNCTION LINE_NUM_P RETURN NUMBER IS
1228 BEGIN
1229 RETURN LINE_NUM;
1230 END LINE_NUM_P;
1231
1232 FUNCTION CP_REPORTTOTTITLE_P RETURN VARCHAR2 IS
1233 BEGIN
1234 RETURN CP_REPORTTOTTITLE;
1235 END CP_REPORTTOTTITLE_P;
1236
1237 FUNCTION CP_LASTLINENUM_P RETURN NUMBER IS
1238 BEGIN
1239 RETURN CP_LASTLINENUM;
1240 END CP_LASTLINENUM_P;
1241
1242 FUNCTION CP_FT_P RETURN VARCHAR2 IS
1243 BEGIN
1244 RETURN CP_FT;
1245 END CP_FT_P;
1246
1247 FUNCTION CP_FR_P RETURN VARCHAR2 IS
1248 BEGIN
1249 RETURN CP_FR;
1250 END CP_FR_P;
1251
1252 FUNCTION CP_PT_P RETURN VARCHAR2 IS
1253 BEGIN
1254 RETURN CP_PT;
1255 END CP_PT_P;
1256
1257 FUNCTION CP_PR_P RETURN VARCHAR2 IS
1258 BEGIN
1259 RETURN CP_PR;
1260 END CP_PR_P;
1261 END PQH_PQIPED5_XMLP_PKG ;