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