[Home] [Help]
PACKAGE BODY: APPS.PQH_PQIPED11_XMLP_PKG
Source
1 PACKAGE BODY PQH_PQIPED11_XMLP_PKG AS
2 /* $Header: PQIPED11B.pls 120.2 2008/04/16 11:17:22 amakrish noship $ */
3
4 function cf_1formula(SUMNRMenNonInstr in number, Sum_Instr_NRMen in number) return number is
5 begin
6 return (SUMNRMenNonInstr + Sum_Instr_NRMen);
7 end;
8
9 function cf_2formula(SUMNRWmenNonInstr in number, Sum_Instr_NRWMen in number) return number is
10 begin
11 return (SUMNRWmenNonInstr + Sum_Instr_NRWMen);
12 end;
13
14 function cf_3formula(SUMBnHMenNonInstr in number, Sum_Instr_BnHMen in number) return number is
15 begin
16 return(SUMBnHMenNonInstr + Sum_Instr_BnHMen);
17 end;
18
19 function cf_4formula(SUMBnHWMenNonInstr in number, Sum_Instr_BnHWMen in number) return number is
20 begin
21 return(SUMBnHWMenNonInstr + Sum_Instr_BnHWMen);
22 end;
23
24 function cf_4formula0004(SUMAm_AlMenNonInstr in number, Sum_Instr_Am_AlMen in number) return number is
25 begin
26 return(SUMAm_AlMenNonInstr + Sum_Instr_Am_AlMen);
27 end;
28
29 function cf_sumam_alwmenformula(SUMAm_AlWMenNonInstr in number, Sum_Instr_Am_AlWMen in number) return number is
30 begin
31 return(SUMAm_AlWMenNonInstr + Sum_Instr_Am_AlWMen);
32 end;
33
34 function cf_sumapmenformula(SUMAPMenNonInstr in number, Sum_Instr_APMen in number) return number is
35 begin
36 return(SUMAPMenNonInstr + Sum_Instr_APMen);
37 end;
38
39 function cf_sumapwmenformula(SUMAPWmenNonInstr in number, Sum_Instr_APWmen in number) return number is
40 begin
41 return(SUMAPWmenNonInstr + Sum_Instr_APWmen);
42 end;
43
44 function cf_sumhmenformula(SUMHMenNonInstr in number, Sum_Instr_HMen in number) return number is
45 begin
46 return(SUMHMenNonInstr + Sum_Instr_HMen);
47 end;
48
49 function cf_sumhwmenformula(SUMHWMenNonInstr in number, Sum_Instr_HWMen in number) return number is
50 begin
51 return(SUMHWMenNonInstr + Sum_Instr_HWMen);
52 end;
53
54 function cf_sumwnhmenformula(SUMWnHMenNonInstr in number, Sum_Instr_WnHMen in number) return number is
55 begin
56 return(SUMWnHMenNonInstr + Sum_Instr_WnHMen);
57 end;
58
59 function cf_sumwnhwmenformula(SUMWnHWmenNonInstr in number, Sum_Instr_WnHWMen in number) return number is
60 begin
61 return(SUMWnHWmenNonInstr + Sum_Instr_WnHWMen);
62 end;
63
64 function cf_sumurmenformula(SUMURMenNonInstr in number, Sum_Instr_URMen in number) return number is
65 begin
66 return(SUMURMenNonInstr + Sum_Instr_URMen);
67 end;
68
69 function cf_sumurwmenformula(SUMURWMenNonInstr in number, Sum_Instr_URWMen in number) return number is
70 begin
71 return(SUMURWMenNonInstr + Sum_Instr_URWMen);
72 end;
73
74 function cf_totmenformula(SUMTotMenNonInstr in number, Sum_Instr_TotMen in number) return number is
75 begin
76 return(SUMTotMenNonInstr + Sum_Instr_TotMen);
77 end;
78
79 function cf_11formula(SUMTotWMenNonInstr in number, Sum_Instr_TotWMen in number) return number is
80 begin
81 return(SUMTotWMenNonInstr + Sum_Instr_TotWMen);
82 end;
83
84 function BeforeReport return boolean is
85 l_query_text varchar2(2000);
86
87 l_fr varchar2(2000);
88 l_ft varchar2(2000);
89 l_pr varchar2(2000);
90 l_pt varchar2(2000);
91 LINE VARCHAR2(1);
92 SC VARCHAR2(2000);
93 L_NR_MEN NUMBER(10) := 0;
94 L_NR_WMEN NUMBER(10) := 0;
95 L_BNH_MEN NUMBER(10) := 0;
96 L_BNH_WMEN NUMBER(10) := 0;
97 L_AMAI_MEN NUMBER(10) := 0;
98 L_AMAI_WMEN NUMBER(10) := 0;
99 L_AP_MEN NUMBER(10) := 0;
100 L_AP_WMEN NUMBER(10) := 0;
101 L_H_MEN NUMBER(10) := 0;
102 L_H_WMEN NUMBER(10) := 0;
103 L_WNH_MEN NUMBER(10) := 0;
104 L_WNH_WMEN NUMBER(10) := 0;
105 L_UR_MEN NUMBER(10) := 0;
106 L_UR_WMEN NUMBER(10) := 0;
107 L_TOT_MEN NUMBER(38) := 0;
108 L_TOT_WMEN NUMBER(38) := 0;
109 L_TMR_BNH_MEN NUMBER(10) := 0;
110 L_TMR_BNH_WMEN NUMBER(10) := 0;
111 L_TMR_AMAI_MEN NUMBER(10) := 0;
112 L_TMR_AMAI_WMEN NUMBER(10) := 0;
113 L_TMR_AP_MEN NUMBER(10) := 0;
114 L_TMR_AP_WMEN NUMBER(10) := 0;
115 L_TMR_H_MEN NUMBER(10) := 0;
116 L_TMR_H_WMEN NUMBER(10) := 0;
117 L_TMR_WNH_MEN NUMBER(10) := 0;
118 L_TMR_WNH_WMEN NUMBER(10) := 0;
119 L_TMR_UR_MEN NUMBER(10) := 0;
120 L_TMR_UR_WMEN NUMBER(10) := 0;
121 CURSOR GET_LINE1_COUNTS IS
122 SELECT
123 2 LINE,
124 HL.LOOKUP_CODE JOBCODE,
125 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
126 ,P_REPORT_DATE)
127 ,'1'
128 ,DECODE(PEO.SEX
129 ,'M'
130 ,1
131 ,NULL)
132 ,NULL)) NRMEN,
133 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
134 ,P_REPORT_DATE)
135 ,'1'
136 ,DECODE(PEO.SEX
137 ,'F'
138 ,1
139 ,NULL)
140 ,NULL)) NRWMEN,
141 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
142 ,P_REPORT_DATE)
143 ,NULL
144 ,(DECODE(PEO.PER_INFORMATION1
145 ,'2'
146 ,DECODE(PEO.SEX
147 ,'M'
148 ,1
149 ,NULL)
150 ,NULL)))) BNHMEN,
151 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
152 ,P_REPORT_DATE)
153 ,NULL
154 ,(DECODE(PEO.PER_INFORMATION1
155 ,'2'
156 ,DECODE(PEO.SEX
157 ,'F'
158 ,1
159 ,NULL)
160 ,NULL)))) BNHWMEN,
161 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
162 ,P_REPORT_DATE)
163 ,NULL
164 ,(DECODE(PEO.PER_INFORMATION1
165 ,'6'
166 ,DECODE(PEO.SEX
167 ,'M'
168 ,1
169 ,NULL)
170 ,NULL)))) AM_ALMEN,
171 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
172 ,P_REPORT_DATE)
173 ,NULL
174 ,(DECODE(PEO.PER_INFORMATION1
175 ,'6'
176 ,DECODE(PEO.SEX
177 ,'F'
178 ,1
179 ,NULL)
180 ,NULL)))) AM_ALWMEN,
181 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
182 ,P_REPORT_DATE)
183 ,NULL
184 ,(DECODE(PEO.PER_INFORMATION1
185 ,'4'
186 ,DECODE(PEO.SEX
187 ,'M'
188 ,1
189 ,NULL)
190 ,'5'
191 ,DECODE(PEO.SEX
192 ,'M'
193 ,1
194 ,NULL)
195 ,NULL)))) APMEN,
196 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
197 ,P_REPORT_DATE)
198 ,NULL
199 ,(DECODE(PEO.PER_INFORMATION1
200 ,'4'
201 ,DECODE(PEO.SEX
202 ,'F'
203 ,1
204 ,NULL)
205 ,'5'
206 ,DECODE(PEO.SEX
207 ,'F'
208 ,1
209 ,NULL)
210 ,NULL)))) APWMEN,
211 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
212 ,P_REPORT_DATE)
213 ,NULL
214 ,(DECODE(PEO.PER_INFORMATION1
215 ,'3'
216 ,DECODE(PEO.SEX
217 ,'M'
218 ,1
219 ,NULL)
220 ,'9'
221 ,DECODE(PEO.SEX
222 ,'M'
223 ,1
224 ,NULL)
225 ,NULL)))) HMEN,
226 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
227 ,P_REPORT_DATE)
228 ,NULL
229 ,(DECODE(PEO.PER_INFORMATION1
230 ,'3'
231 ,DECODE(PEO.SEX
232 ,'F'
233 ,1
234 ,NULL)
235 ,'9'
236 ,DECODE(PEO.SEX
237 ,'F'
238 ,1
239 ,NULL)
240 ,NULL)))) HWMEN,
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 ,'1'
246 ,DECODE(PEO.SEX
247 ,'M'
248 ,1
249 ,NULL)
250 ,NULL)))) WNHMEN,
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 ,'1'
256 ,DECODE(PEO.SEX
257 ,'F'
258 ,1
259 ,NULL)
260 ,NULL)))) WNHWMEN,
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 ,NULL
266 ,DECODE(PEO.SEX
267 ,'M'
268 ,1
269 ,NULL)
270 ,NULL)))) URMEN,
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 ,NULL
276 ,DECODE(PEO.SEX
277 ,'F'
278 ,1
279 ,NULL)
280 ,NULL)))) URWMEN
281 FROM
282 PER_ALL_PEOPLE_F PEO,
283 PER_ALL_ASSIGNMENTS_F ASS,
284 PER_ASSIGNMENT_STATUS_TYPES AST,
285 PER_JOBS JOB,
286 PER_PAY_PROPOSALS PPP,
287 PER_PAY_BASES PPB,
288 HR_LOOKUPS HL
289 WHERE PEO.PERSON_ID = ASS.PERSON_ID
290 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
291 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
292 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(ASS.EMPLOYMENT_CATEGORY
293 ,CP_FR
294 ,CP_FT
295 ,CP_PR
296 ,CP_PT) IN ( 'PR' )
297 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
298 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
299 AND HL.LOOKUP_CODE not in ( '1' , '2' , '3' )
300 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
301 AND PEO.EFFECTIVE_END_DATE
302 AND P_REPORT_DATE BETWEEN ASS.EFFECTIVE_START_DATE
303 AND ASS.EFFECTIVE_END_DATE
304 AND ASS.PRIMARY_FLAG = 'Y'
305 AND ASS.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
306 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
307 AND ASS.PAY_BASIS_ID = PPB.PAY_BASIS_ID
308 AND ASS.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
309 AND PPP.CHANGE_DATE = (
310 SELECT
311 MAX(CHANGE_DATE)
312 FROM
313 PER_PAY_PROPOSALS PRO
314 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
315 AND PRO.CHANGE_DATE <= P_REPORT_DATE
316 AND PRO.APPROVED = 'Y' )
317 AND NVL(PPP.PROPOSED_SALARY_N
318 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
319 AND ASS.JOB_ID = JOB.JOB_ID
320 AND ASS.ASSIGNMENT_TYPE = 'E'
321 AND ASS.ORGANIZATION_ID in (
322 SELECT
323 ORGANIZATION_ID
324 FROM
325 HR_ALL_ORGANIZATION_UNITS
326 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
327 GROUP BY
328 HL.LOOKUP_CODE;
329 CURSOR GET_LINE1_TMRACES_COUNTS(C_LOOKUP_CODE IN VARCHAR2) IS
330 SELECT
331 COUNT(DECODE(PEI.PEI_INFORMATION5
332 ,'2'
333 ,DECODE(PEO.SEX
334 ,'M'
335 ,1
336 ,NULL)
337 ,NULL)) BNHMEN,
338 COUNT(DECODE(PEI.PEI_INFORMATION5
339 ,'2'
340 ,DECODE(PEO.SEX
341 ,'F'
342 ,1
343 ,NULL)
344 ,NULL)) BNHWMEN,
345 COUNT(DECODE(PEI.PEI_INFORMATION5
346 ,'6'
347 ,DECODE(PEO.SEX
348 ,'M'
349 ,1
350 ,NULL)
351 ,NULL)) AM_ALMEN,
352 COUNT(DECODE(PEI.PEI_INFORMATION5
353 ,'6'
354 ,DECODE(PEO.SEX
355 ,'F'
356 ,1
357 ,NULL)
358 ,NULL)) AM_ALWMEN,
359 COUNT(DECODE(PEI.PEI_INFORMATION5
360 ,'4'
361 ,DECODE(PEO.SEX
362 ,'M'
363 ,1
364 ,NULL)
365 ,'5'
366 ,DECODE(PEO.SEX
367 ,'M'
368 ,1
369 ,NULL)
370 ,NULL)) A_PMEN,
371 COUNT(DECODE(PEI.PEI_INFORMATION5
372 ,'4'
373 ,DECODE(PEO.SEX
374 ,'F'
375 ,1
376 ,NULL)
377 ,'5'
378 ,DECODE(PEO.SEX
379 ,'F'
380 ,1
381 ,NULL)
382 ,NULL)) A_PWMEN,
383 COUNT(DECODE(PEI.PEI_INFORMATION5
384 ,'3'
385 ,DECODE(PEO.SEX
386 ,'M'
387 ,1
388 ,NULL)
389 ,'9'
390 ,DECODE(PEO.SEX
391 ,'M'
392 ,1
393 ,NULL)
394 ,NULL)) HMEN,
395 COUNT(DECODE(PEI.PEI_INFORMATION5
396 ,'3'
397 ,DECODE(PEO.SEX
398 ,'F'
399 ,1
400 ,NULL)
401 ,'9'
402 ,DECODE(PEO.SEX
403 ,'F'
404 ,1
405 ,NULL)
406 ,NULL)) HWMEN,
407 COUNT(DECODE(PEI.PEI_INFORMATION5
408 ,'1'
409 ,DECODE(PEO.SEX
410 ,'M'
411 ,1
412 ,NULL)
413 ,NULL)) WNHMEN,
414 COUNT(DECODE(PEI.PEI_INFORMATION5
415 ,'1'
416 ,DECODE(PEO.SEX
417 ,'F'
418 ,1
419 ,NULL)
420 ,NULL)) WNHWMEN,
421 COUNT(DECODE(PEI.PEI_INFORMATION5
422 ,NULL
423 ,DECODE(PEO.SEX
424 ,'M'
425 ,1
426 ,NULL)
427 ,NULL)) URMEN,
428 COUNT(DECODE(PEI.PEI_INFORMATION5
429 ,NULL
430 ,DECODE(PEO.SEX
431 ,'F'
432 ,1
433 ,NULL)
434 ,NULL)) URWMEN
435 FROM
436 PER_ALL_PEOPLE_F PEO,
437 PER_ALL_ASSIGNMENTS_F ASS,
438 PER_ASSIGNMENT_STATUS_TYPES AST,
439 PER_JOBS JOB,
440 PER_PAY_PROPOSALS PPP,
441 PER_PAY_BASES PPB,
442 HR_LOOKUPS HL,
443 PER_PEOPLE_EXTRA_INFO PEI
444 WHERE PEO.PERSON_ID = ASS.PERSON_ID
445 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
446 AND PEO.PER_INFORMATION1 = '13'
447 AND PEO.PERSON_ID = pei.person_id (+)
448 AND ( PEI.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
449 OR ( PEI.INFORMATION_TYPE <> 'PER_US_ADDL_ETHNIC_CAT'
450 AND not exists (
451 SELECT
452 1
453 FROM
454 PER_PEOPLE_EXTRA_INFO PEI2
455 WHERE PEI2.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
456 AND PEI2.PERSON_ID = PEI.PERSON_ID )
457 AND PEI.PERSON_EXTRA_INFO_ID = (
458 SELECT
459 MAX(PEI1.PERSON_EXTRA_INFO_ID)
460 FROM
461 PER_PEOPLE_EXTRA_INFO PEI1
462 WHERE PEI1.PERSON_ID = PEI.PERSON_ID ) )
463 OR ( not exists (
464 SELECT
465 PERSON_EXTRA_INFO_ID
466 FROM
467 PER_PEOPLE_EXTRA_INFO PEI3
468 WHERE PEI3.PERSON_ID = PEI.PERSON_ID ) ) )
469 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
470 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(ASS.EMPLOYMENT_CATEGORY
471 ,CP_FR
472 ,CP_FT
473 ,CP_PR
474 ,CP_PT) IN ( 'PR' )
475 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
476 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
477 AND HL.LOOKUP_CODE not in ( '1' , '2' , '3' )
478 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
479 AND PEO.EFFECTIVE_END_DATE
480 AND P_REPORT_DATE BETWEEN ASS.EFFECTIVE_START_DATE
481 AND ASS.EFFECTIVE_END_DATE
482 AND ASS.PRIMARY_FLAG = 'Y'
483 AND ASS.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
484 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
485 AND ASS.PAY_BASIS_ID = PPB.PAY_BASIS_ID
486 AND ASS.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
487 AND PPP.CHANGE_DATE = (
488 SELECT
489 MAX(CHANGE_DATE)
490 FROM
491 PER_PAY_PROPOSALS PRO
492 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
493 AND PRO.CHANGE_DATE <= P_REPORT_DATE
494 AND PRO.APPROVED = 'Y' )
495 AND NVL(PPP.PROPOSED_SALARY_N
496 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
497 AND ASS.JOB_ID = JOB.JOB_ID
498 AND ASS.ASSIGNMENT_TYPE = 'E'
499 AND ASS.ORGANIZATION_ID in (
500 SELECT
501 ORGANIZATION_ID
502 FROM
503 HR_ALL_ORGANIZATION_UNITS
504 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID )
505 AND HL.LOOKUP_CODE = C_LOOKUP_CODE;
506 CURSOR GET_LINE2_COUNTS IS
507 SELECT
508 1 LINE,
509 'Faculty(Instruction/Research/Public Service)' DISP_NAME,
510 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
511 ,P_REPORT_DATE)
512 ,'1'
513 ,DECODE(PEO.SEX
514 ,'M'
515 ,1
516 ,NULL)
517 ,NULL)) NRMEN,
518 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
519 ,P_REPORT_DATE)
520 ,'1'
521 ,DECODE(PEO.SEX
522 ,'F'
523 ,1
524 ,NULL)
525 ,NULL)) NRWMEN,
526 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
527 ,P_REPORT_DATE)
528 ,NULL
529 ,(DECODE(PEO.PER_INFORMATION1
530 ,'2'
531 ,DECODE(PEO.SEX
532 ,'M'
533 ,1
534 ,NULL)
535 ,NULL)))) BNHMEN,
536 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
537 ,P_REPORT_DATE)
538 ,NULL
539 ,(DECODE(PEO.PER_INFORMATION1
540 ,'2'
541 ,DECODE(PEO.SEX
542 ,'F'
543 ,1
544 ,NULL)
545 ,NULL)))) BNHWMEN,
546 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
547 ,P_REPORT_DATE)
548 ,NULL
549 ,(DECODE(PEO.PER_INFORMATION1
550 ,'6'
551 ,DECODE(PEO.SEX
552 ,'M'
553 ,1
554 ,NULL)
555 ,NULL)))) AM_ALMEN,
556 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
557 ,P_REPORT_DATE)
558 ,NULL
559 ,(DECODE(PEO.PER_INFORMATION1
560 ,'6'
561 ,DECODE(PEO.SEX
562 ,'F'
563 ,1
564 ,NULL)
565 ,NULL)))) AM_ALWMEN,
566 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
567 ,P_REPORT_DATE)
568 ,NULL
569 ,(DECODE(PEO.PER_INFORMATION1
570 ,'4'
571 ,DECODE(PEO.SEX
572 ,'M'
573 ,1
574 ,NULL)
575 ,'5'
576 ,DECODE(PEO.SEX
577 ,'M'
578 ,1
579 ,NULL)
580 ,NULL)))) A_PMEN,
581 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
582 ,P_REPORT_DATE)
583 ,NULL
584 ,(DECODE(PEO.PER_INFORMATION1
585 ,'4'
586 ,DECODE(PEO.SEX
587 ,'F'
588 ,1
589 ,NULL)
590 ,'5'
591 ,DECODE(PEO.SEX
592 ,'F'
593 ,1
594 ,NULL)
595 ,NULL)))) A_PWMEN,
596 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
597 ,P_REPORT_DATE)
598 ,NULL
599 ,(DECODE(PEO.PER_INFORMATION1
600 ,'3'
601 ,DECODE(PEO.SEX
602 ,'M'
603 ,1
604 ,NULL)
605 ,'9'
606 ,DECODE(PEO.SEX
607 ,'M'
608 ,1
609 ,NULL)
610 ,NULL)))) HMEN,
611 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
612 ,P_REPORT_DATE)
613 ,NULL
614 ,(DECODE(PEO.PER_INFORMATION1
615 ,'3'
616 ,DECODE(PEO.SEX
617 ,'F'
618 ,1
619 ,NULL)
620 ,'9'
621 ,DECODE(PEO.SEX
622 ,'F'
623 ,1
624 ,NULL)
625 ,NULL)))) HWMEN,
626 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
627 ,P_REPORT_DATE)
628 ,NULL
629 ,(DECODE(PEO.PER_INFORMATION1
630 ,'1'
631 ,DECODE(PEO.SEX
632 ,'M'
633 ,1
634 ,NULL)
635 ,NULL)))) WNHMEN,
636 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID
637 ,P_REPORT_DATE)
638 ,NULL
639 ,(DECODE(PEO.PER_INFORMATION1
640 ,'1'
641 ,DECODE(PEO.SEX
642 ,'F'
643 ,1
644 ,NULL)
645 ,NULL)))) WNHWMEN,
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 ,NULL
651 ,DECODE(PEO.SEX
652 ,'M'
653 ,1
654 ,NULL)
655 ,NULL)))) URMEN,
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 ,NULL
661 ,DECODE(PEO.SEX
662 ,'F'
663 ,1
664 ,NULL)
665 ,NULL)))) URWMEN
666 FROM
667 PER_ALL_PEOPLE_F PEO,
668 PER_ALL_ASSIGNMENTS_F ASS,
669 PER_ASSIGNMENT_STATUS_TYPES AST,
670 PER_JOBS JOB,
671 PER_PAY_PROPOSALS PPP,
672 PER_PAY_BASES PPB,
673 HR_LOOKUPS HL
674 WHERE PEO.PERSON_ID = ASS.PERSON_ID
675 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
676 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
677 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(ASS.EMPLOYMENT_CATEGORY
678 ,CP_FR
679 ,CP_FT
680 ,CP_PR
681 ,CP_PT) IN ( 'PR' )
682 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
683 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
684 AND HL.LOOKUP_CODE IN ( '1' , '2' , '3' )
685 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
686 AND PEO.EFFECTIVE_END_DATE
687 AND P_REPORT_DATE BETWEEN ASS.EFFECTIVE_START_DATE
688 AND ASS.EFFECTIVE_END_DATE
689 AND ASS.PRIMARY_FLAG = 'Y'
690 AND ASS.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
691 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
692 AND ASS.PAY_BASIS_ID = PPB.PAY_BASIS_ID
693 AND ASS.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
694 AND PPP.CHANGE_DATE = (
695 SELECT
696 MAX(CHANGE_DATE)
697 FROM
698 PER_PAY_PROPOSALS PRO
699 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
700 AND PRO.CHANGE_DATE <= P_REPORT_DATE
701 AND PRO.APPROVED = 'Y' )
702 AND NVL(PPP.PROPOSED_SALARY_N
703 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
704 AND ASS.JOB_ID = JOB.JOB_ID
705 AND ASS.ASSIGNMENT_TYPE = 'E'
706 AND ASS.ORGANIZATION_ID in (
707 SELECT
708 ORGANIZATION_ID
709 FROM
710 HR_ALL_ORGANIZATION_UNITS
711 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID );
712 CURSOR GET_TMR_LINE2_COUNTS IS
713 SELECT
714 COUNT(DECODE(PEI.PEI_INFORMATION5
715 ,'2'
716 ,DECODE(PEO.SEX
717 ,'M'
718 ,1
719 ,NULL)
720 ,NULL)) BNHMEN,
721 COUNT(DECODE(PEI.PEI_INFORMATION5
722 ,'2'
723 ,DECODE(PEO.SEX
724 ,'F'
725 ,1
726 ,NULL)
727 ,NULL)) BNHWMEN,
728 COUNT(DECODE(PEI.PEI_INFORMATION5
729 ,'6'
730 ,DECODE(PEO.SEX
731 ,'M'
732 ,1
733 ,NULL)
734 ,NULL)) AM_ALMEN,
735 COUNT(DECODE(PEI.PEI_INFORMATION5
736 ,'6'
737 ,DECODE(PEO.SEX
738 ,'F'
739 ,1
740 ,NULL)
741 ,NULL)) AM_ALWMEN,
742 COUNT(DECODE(PEI.PEI_INFORMATION5
743 ,'4'
744 ,DECODE(PEO.SEX
745 ,'M'
746 ,1
747 ,NULL)
748 ,'5'
749 ,DECODE(PEO.SEX
750 ,'M'
751 ,1
752 ,NULL)
753 ,NULL)) A_PMEN,
754 COUNT(DECODE(PEI.PEI_INFORMATION5
755 ,'4'
756 ,DECODE(PEO.SEX
757 ,'F'
758 ,1
759 ,NULL)
760 ,'5'
761 ,DECODE(PEO.SEX
762 ,'F'
763 ,1
764 ,NULL)
765 ,NULL)) A_PWMEN,
766 COUNT(DECODE(PEI.PEI_INFORMATION5
767 ,'3'
768 ,DECODE(PEO.SEX
769 ,'M'
770 ,1
771 ,NULL)
772 ,'9'
773 ,DECODE(PEO.SEX
774 ,'M'
775 ,1
776 ,NULL)
777 ,NULL)) HMEN,
778 COUNT(DECODE(PEI.PEI_INFORMATION5
779 ,'3'
780 ,DECODE(PEO.SEX
781 ,'F'
782 ,1
783 ,NULL)
784 ,'9'
785 ,DECODE(PEO.SEX
786 ,'F'
787 ,1
788 ,NULL)
789 ,NULL)) HWMEN,
790 COUNT(DECODE(PEI.PEI_INFORMATION5
791 ,'1'
792 ,DECODE(PEO.SEX
793 ,'M'
794 ,1
795 ,NULL)
796 ,NULL)) WNHMEN,
797 COUNT(DECODE(PEI.PEI_INFORMATION5
798 ,'1'
799 ,DECODE(PEO.SEX
800 ,'F'
801 ,1
802 ,NULL)
803 ,NULL)) WNHWMEN,
804 COUNT(DECODE(PEI.PEI_INFORMATION5
805 ,NULL
806 ,DECODE(PEO.SEX
807 ,'M'
808 ,1
809 ,NULL)
810 ,NULL)) URMEN,
811 COUNT(DECODE(PEI.PEI_INFORMATION5
812 ,NULL
813 ,DECODE(PEO.SEX
814 ,'F'
815 ,1
816 ,NULL)
817 ,NULL)) URWMEN
818 FROM
819 PER_ALL_PEOPLE_F PEO,
820 PER_ALL_ASSIGNMENTS_F ASS,
821 PER_ASSIGNMENT_STATUS_TYPES AST,
822 PER_JOBS JOB,
823 PER_PAY_PROPOSALS PPP,
824 PER_PAY_BASES PPB,
825 HR_LOOKUPS HL,
826 PER_PEOPLE_EXTRA_INFO PEI
827 WHERE PEO.PERSON_ID = ASS.PERSON_ID
828 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
829 AND PEO.PER_INFORMATION1 = '13'
830 AND PEO.PERSON_ID = pei.person_id (+)
831 AND ( PEI.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
832 OR ( PEI.INFORMATION_TYPE <> 'PER_US_ADDL_ETHNIC_CAT'
833 AND not exists (
834 SELECT
835 1
836 FROM
837 PER_PEOPLE_EXTRA_INFO PEI2
838 WHERE PEI2.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
839 AND PEI2.PERSON_ID = PEI.PERSON_ID )
840 AND PEI.PERSON_EXTRA_INFO_ID = (
841 SELECT
842 MAX(PEI1.PERSON_EXTRA_INFO_ID)
843 FROM
844 PER_PEOPLE_EXTRA_INFO PEI1
845 WHERE PEI1.PERSON_ID = PEI.PERSON_ID ) )
846 OR ( not exists (
847 SELECT
848 PERSON_EXTRA_INFO_ID
849 FROM
850 PER_PEOPLE_EXTRA_INFO PEI3
851 WHERE PEI3.PERSON_ID = PEI.PERSON_ID ) ) )
852 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
853 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(ASS.EMPLOYMENT_CATEGORY
854 ,CP_FR
855 ,CP_FT
856 ,CP_PR
857 ,CP_PT) IN ( 'PR' )
858 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
859 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
860 AND HL.LOOKUP_CODE IN ( '1' , '2' , '3' )
861 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
862 AND PEO.EFFECTIVE_END_DATE
863 AND P_REPORT_DATE BETWEEN ASS.EFFECTIVE_START_DATE
864 AND ASS.EFFECTIVE_END_DATE
865 AND ASS.PRIMARY_FLAG = 'Y'
866 AND ASS.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
867 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
868 AND ASS.PAY_BASIS_ID = PPB.PAY_BASIS_ID
869 AND ASS.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
870 AND PPP.CHANGE_DATE = (
871 SELECT
872 MAX(CHANGE_DATE)
873 FROM
874 PER_PAY_PROPOSALS PRO
875 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
876 AND PRO.CHANGE_DATE <= P_REPORT_DATE
877 AND PRO.APPROVED = 'Y' )
878 AND NVL(PPP.PROPOSED_SALARY_N
879 ,0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
880 AND ASS.JOB_ID = JOB.JOB_ID
881 AND ASS.ASSIGNMENT_TYPE = 'E'
882 AND ASS.ORGANIZATION_ID in (
883 SELECT
884 ORGANIZATION_ID
885 FROM
886 HR_ALL_ORGANIZATION_UNITS
887 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID );
888
889 begin
890 --hr_standard.event('BEFORE REPORT');
891
892 pqh_employment_category.fetch_empl_categories(p_business_group_id,l_fr,l_ft,l_pr,l_pt);
893
894 cp_fr := l_fr;
895 cp_ft := l_ft;
896 cp_pr := l_pr;
897 cp_pt := l_pt;
898 FOR i IN GET_LINE1_COUNTS LOOP
899 LINE := I.LINE;
900 SC := I.JOBCODE;
901 L_NR_MEN := I.NRMEN;
902 L_NR_WMEN := I.NRWMEN;
903 L_BNH_MEN := I.BNHMEN;
904 L_BNH_WMEN := I.BNHWMEN;
905 L_AMAI_MEN := I.AM_ALMEN;
906 L_AMAI_WMEN := I.AM_ALWMEN;
907 L_AP_MEN := I.APMEN;
908 L_AP_WMEN := I.APWMEN;
909 L_H_MEN := I.HMEN;
910 L_H_WMEN := I.HWMEN;
911 L_WNH_MEN := I.WNHMEN;
912 L_WNH_WMEN := I.WNHWMEN;
913 L_UR_MEN := I.URMEN;
914 L_UR_WMEN := I.URWMEN;
915 L_TOT_MEN := L_NR_MEN + L_BNH_MEN + L_AMAI_MEN + L_AP_MEN + L_H_MEN + L_WNH_MEN + L_UR_MEN;
916 L_TOT_WMEN := L_NR_WMEN + L_BNH_WMEN + L_AMAI_WMEN + L_AP_WMEN + L_H_WMEN + L_WNH_WMEN + L_UR_WMEN;
917 FOR j IN GET_LINE1_TMRACES_COUNTS(sc) LOOP
918 L_BNH_MEN := L_BNH_MEN + J.BNHMEN;
919 L_BNH_WMEN := L_BNH_WMEN + J.BNHWMEN;
920 L_AMAI_MEN := L_AMAI_MEN + J.AM_ALMEN;
921 L_AMAI_WMEN := L_AMAI_WMEN + J.AM_ALWMEN;
922 L_AP_MEN := L_AP_MEN + J.A_PMEN;
923 L_AP_WMEN := L_AP_WMEN + J.A_PWMEN;
924 L_H_MEN := L_H_MEN + J.HMEN;
925 L_H_WMEN := L_H_WMEN + J.HWMEN;
926 L_WNH_MEN := L_WNH_MEN + J.WNHMEN;
927 L_WNH_WMEN := L_WNH_WMEN + J.WNHWMEN;
928 L_UR_MEN := L_UR_MEN + J.URMEN;
929 L_UR_WMEN := L_UR_WMEN + J.URWMEN;
930 L_TOT_MEN := L_TOT_MEN + J.BNHMEN + J.AM_ALMEN + J.A_PMEN + J.HMEN + J.WNHMEN + J.URMEN;
931 L_TOT_WMEN := L_TOT_WMEN + J.BNHWMEN + J.AM_ALWMEN + J.A_PWMEN + J.HWMEN + J.WNHWMEN + J.URWMEN;
932 END LOOP;
933 INSERT INTO PAY_US_RPT_TOTALS
934 (SESSION_ID
935 ,ATTRIBUTE1
936 ,VALUE1
937 ,VALUE2
938 ,VALUE3
939 ,VALUE4
940 ,VALUE5
941 ,VALUE6
942 ,VALUE7
943 ,VALUE8
944 ,VALUE9
945 ,VALUE10
946 ,VALUE11
947 ,VALUE12
948 ,VALUE13
949 ,VALUE14
950 ,VALUE15
951 ,VALUE16
952 ,VALUE17
953 ,VALUE18)
954 VALUES (USERENV('sessionid')
955 ,'IPED11'
956 ,LINE
957 ,SC
958 ,L_NR_MEN
959 ,L_NR_WMEN
960 ,L_BNH_MEN
961 ,L_BNH_WMEN
962 ,L_AMAI_MEN
963 ,L_AMAI_WMEN
964 ,L_AP_MEN
965 ,L_AP_WMEN
966 ,L_H_MEN
967 ,L_H_WMEN
968 ,L_WNH_MEN
969 ,L_WNH_WMEN
970 ,L_UR_MEN
971 ,L_UR_WMEN
972 ,L_TOT_MEN
973 ,L_TOT_WMEN);
974 COMMIT;
975 END LOOP;
976 OPEN GET_LINE2_COUNTS;
977 FETCH GET_LINE2_COUNTS
978 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;
979 CLOSE GET_LINE2_COUNTS;
980 L_TOT_MEN := L_NR_MEN + L_BNH_MEN + L_AMAI_MEN + L_AP_MEN + L_H_MEN + L_WNH_MEN + L_UR_MEN;
981 L_TOT_WMEN := L_NR_WMEN + L_BNH_WMEN + L_AMAI_WMEN + L_AP_WMEN + L_H_WMEN + L_WNH_WMEN + L_UR_WMEN;
982 OPEN GET_TMR_LINE2_COUNTS;
983 FETCH GET_TMR_LINE2_COUNTS
984 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;
985 CLOSE GET_TMR_LINE2_COUNTS;
986 L_BNH_MEN := L_BNH_MEN + L_TMR_BNH_MEN;
987 L_BNH_WMEN := L_BNH_WMEN + L_TMR_BNH_WMEN;
988 L_AMAI_MEN := L_AMAI_MEN + L_TMR_AMAI_MEN;
989 L_AMAI_WMEN := L_AMAI_WMEN + L_TMR_AMAI_WMEN;
990 L_AP_MEN := L_AP_MEN + L_TMR_AP_MEN;
991 L_AP_WMEN := L_AP_WMEN + L_TMR_AP_WMEN;
992 L_H_MEN := L_H_MEN + L_TMR_H_MEN;
993 L_H_WMEN := L_H_WMEN + L_TMR_H_WMEN;
994 L_WNH_MEN := L_WNH_MEN + L_TMR_WNH_MEN;
995 L_WNH_WMEN := L_WNH_WMEN + L_TMR_WNH_WMEN;
996 L_UR_MEN := L_UR_MEN + L_TMR_UR_MEN;
997 L_UR_WMEN := L_UR_WMEN + L_TMR_UR_WMEN;
998 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;
999 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;
1000 INSERT INTO PAY_US_RPT_TOTALS
1001 (SESSION_ID
1002 ,ATTRIBUTE1
1003 ,ATTRIBUTE2
1004 ,VALUE1
1005 ,VALUE2
1006 ,VALUE3
1007 ,VALUE4
1008 ,VALUE5
1009 ,VALUE6
1010 ,VALUE7
1011 ,VALUE8
1012 ,VALUE9
1013 ,VALUE10
1014 ,VALUE11
1015 ,VALUE12
1016 ,VALUE13
1017 ,VALUE14
1018 ,VALUE15
1019 ,VALUE16
1020 ,VALUE17)
1021 VALUES (USERENV('sessionid')
1022 ,'IPED11'
1023 ,SC
1024 ,LINE
1025 ,L_NR_MEN
1026 ,L_NR_WMEN
1027 ,L_BNH_MEN
1028 ,L_BNH_WMEN
1029 ,L_AMAI_MEN
1030 ,L_AMAI_WMEN
1031 ,L_AP_MEN
1032 ,L_AP_WMEN
1033 ,L_H_MEN
1034 ,L_H_WMEN
1035 ,L_WNH_MEN
1036 ,L_WNH_WMEN
1037 ,L_UR_MEN
1038 ,L_UR_WMEN
1039 ,L_TOT_MEN
1040 ,L_TOT_WMEN);
1041
1042 commit;
1043 return TRUE;
1044 end;
1045
1046 function line_noFormula return Number is
1047 temp_num number;
1048 begin
1049 temp_num := line_num;
1050 line_num:= line_num + 1;
1051
1052 return temp_num;
1053 end;
1054
1055 function AfterReport return boolean is
1056 begin
1057 --hr_standard.event('AFTER REPORT');
1058 EXECUTE IMMEDIATE
1059 'DELETE FROM pay_us_rpt_totals
1060 WHERE attribute1 = ''IPED11''';
1061
1062 return (TRUE);
1063 end;
1064
1065 --Functions to refer Oracle report placeholders--
1066
1067 Function CP_FR_p return varchar2 is
1068 Begin
1069 return CP_FR;
1070 END;
1071 Function CP_FT_p return varchar2 is
1072 Begin
1073 return CP_FT;
1074 END;
1075 Function CP_PR_p return varchar2 is
1076 Begin
1077 return CP_PR;
1078 END;
1079 Function CP_PT_p return varchar2 is
1080 Begin
1081 return CP_PT;
1082 END;
1083 Function line_num_p return number is
1084 Begin
1085 return line_num;
1086 END;
1087 Function LastLineNo_p return number is
1088 Begin
1089 return LastLineNo;
1090 END;
1091 Function totTitle_p return varchar2 is
1092 Begin
1093 return totTitle;
1094 END;
1095 END PQH_PQIPED11_XMLP_PKG ;