[Home] [Help]
PACKAGE BODY: APPS.PQH_PQIPED7_XMLP_PKG
Source
1 PACKAGE BODY PQH_PQIPED7_XMLP_PKG AS
2 /* $Header: PQIPED7B.pls 120.3 2008/04/16 11:16:39 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(2);
92 SC VARCHAR2(2000);
93 SALARY_RANGE VARCHAR2(2000);
94 L_NR_MEN NUMBER(10) := 0;
95 L_NR_WMEN NUMBER(10) := 0;
96 L_BNH_MEN NUMBER(10) := 0;
97 L_BNH_WMEN NUMBER(10) := 0;
98 L_AMAI_MEN NUMBER(10) := 0;
99 L_AMAI_WMEN NUMBER(10) := 0;
100 L_AP_MEN NUMBER(10) := 0;
101 L_AP_WMEN NUMBER(10) := 0;
102 L_H_MEN NUMBER(10) := 0;
103 L_H_WMEN NUMBER(10) := 0;
104 L_WNH_MEN NUMBER(10) := 0;
105 L_WNH_WMEN NUMBER(10) := 0;
106 L_UR_MEN NUMBER(10) := 0;
107 L_UR_WMEN NUMBER(10) := 0;
108 L_TOT_MEN NUMBER(10) := 0;
109 L_TOT_WMEN NUMBER(10) := 0;
110 L_TMR_BNH_MEN NUMBER(10) := 0;
111 L_TMR_BNH_WMEN NUMBER(10) := 0;
112 L_TMR_AMAI_MEN NUMBER(10) := 0;
113 L_TMR_AMAI_WMEN NUMBER(10) := 0;
114 L_TMR_AP_MEN NUMBER(10) := 0;
115 L_TMR_AP_WMEN NUMBER(10) := 0;
116 L_TMR_H_MEN NUMBER(10) := 0;
117 L_TMR_H_WMEN NUMBER(10) := 0;
118 L_TMR_WNH_MEN NUMBER(10) := 0;
119 L_TMR_WNH_WMEN NUMBER(10) := 0;
120 L_TMR_UR_MEN NUMBER(10) := 0;
121 L_TMR_UR_WMEN NUMBER(10) := 0;
122 CURSOR GET_LINE1_COUNTS IS
123 SELECT '68' LINE,
124 'FACULTY(INSTRUCTION/RESEARCH/PUBLIC SERVICE)' DISP_NAME,
125 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), '1', DECODE(PEO.SEX, 'M', 1, NULL), NULL)) NRMEN,
126 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), '1', DECODE(PEO.SEX, 'F', 1, NULL), NULL)) NRWMEN,
127 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), NULL, (DECODE(PEO.PER_INFORMATION1, '2', DECODE(PEO.SEX, 'M', 1, NULL), NULL)))) BNHMEN,
128 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), NULL, (DECODE(PEO.PER_INFORMATION1, '2', DECODE(PEO.SEX, 'F', 1, NULL), NULL)))) BNHWMEN,
129 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), NULL, (DECODE(PEO.PER_INFORMATION1, '6', DECODE(PEO.SEX, 'M', 1, NULL), NULL)))) AM_ALMEN,
130 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), NULL, (DECODE(PEO.PER_INFORMATION1, '6', DECODE(PEO.SEX, 'F', 1, NULL), NULL)))) AM_ALWMEN,
131 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), NULL, (DECODE(PEO.PER_INFORMATION1, '4', DECODE(PEO.SEX, 'M', 1, NULL), '5', DECODE(PEO.SEX, 'M', 1, NULL), NULL)))) A_PMEN,
132 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), NULL, (DECODE(PEO.PER_INFORMATION1, '4', DECODE(PEO.SEX, 'F', 1, NULL), '5', DECODE(PEO.SEX, 'F', 1, NULL), NULL)))) A_PWMEN,
133 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), NULL, (DECODE(PEO.PER_INFORMATION1, '3', DECODE(PEO.SEX, 'M', 1, NULL), '9', DECODE(PEO.SEX, 'M', 1, NULL), NULL)))) HMEN,
134 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), NULL, (DECODE(PEO.PER_INFORMATION1, '3', DECODE(PEO.SEX, 'F', 1, NULL), '9', DECODE(PEO.SEX, 'F', 1, NULL), NULL)))) HWMEN,
135 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), NULL, (DECODE(PEO.PER_INFORMATION1, '1', DECODE(PEO.SEX, 'M', 1, NULL), NULL)))) WNHMEN,
136 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), NULL, (DECODE(PEO.PER_INFORMATION1, '1', DECODE(PEO.SEX, 'F', 1, NULL), NULL)))) WNHWMEN,
137 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), NULL, (DECODE(PEO.PER_INFORMATION1, NULL, DECODE(PEO.SEX, 'M', 1, NULL), NULL)))) URMEN,
138 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), NULL, (DECODE(PEO.PER_INFORMATION1, NULL, DECODE(PEO.SEX, 'F', 1, NULL), NULL)))) URWMEN
139 FROM PER_ALL_PEOPLE_F PEO,
140 PER_ALL_ASSIGNMENTS_F ASS,
141 PER_ASSIGNMENT_STATUS_TYPES AST,
142 PER_JOBS JOB,
143 PER_PAY_PROPOSALS PPP,
144 PER_PAY_BASES PPB,
145 HR_LOOKUPS HL
146 WHERE PEO.PERSON_ID = ASS.PERSON_ID
147 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
148 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
149 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(ASS.EMPLOYMENT_CATEGORY, CP_FR, CP_FT, CP_PR, CP_PT) IN('PR')
150 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
151 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
152 AND HL.LOOKUP_CODE IN('1', '2', '3')
153 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
154 AND PEO.EFFECTIVE_END_DATE
155 AND P_REPORT_DATE BETWEEN ASS.EFFECTIVE_START_DATE
156 AND ASS.EFFECTIVE_END_DATE
157 AND ASS.PRIMARY_FLAG = 'Y'
158 AND ASS.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
159 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
160 AND ASS.PAY_BASIS_ID = PPB.PAY_BASIS_ID
161 AND ASS.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
162 AND PPP.CHANGE_DATE =
163 (SELECT MAX(CHANGE_DATE)
164 FROM PER_PAY_PROPOSALS PRO
165 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
166 AND PRO.CHANGE_DATE <= P_REPORT_DATE
167 AND PRO.APPROVED = 'Y' )
168 AND NVL(PPP.PROPOSED_SALARY_N, 0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
169 AND ASS.JOB_ID = JOB.JOB_ID
170 AND ASS.ASSIGNMENT_TYPE = 'E'
171 AND ASS.ORGANIZATION_ID IN
172 (SELECT ORGANIZATION_ID
173 FROM HR_ALL_ORGANIZATION_UNITS
174 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID)
175 ;
176 CURSOR GET_LINE1_TMRACES_COUNTS IS
177 SELECT COUNT(DECODE(PEI.PEI_INFORMATION5, '2', DECODE(PEO.SEX, 'M', 1, NULL), NULL)) BNHMEN,
178 COUNT(DECODE(PEI.PEI_INFORMATION5, '2', DECODE(PEO.SEX, 'F', 1, NULL), NULL)) BNHWMEN,
179 COUNT(DECODE(PEI.PEI_INFORMATION5, '6', DECODE(PEO.SEX, 'M', 1, NULL), NULL)) AM_ALMEN,
180 COUNT(DECODE(PEI.PEI_INFORMATION5, '6', DECODE(PEO.SEX, 'F', 1, NULL), NULL)) AM_ALWMEN,
181 COUNT(DECODE(PEI.PEI_INFORMATION5, '4', DECODE(PEO.SEX, 'M', 1, NULL), '5', DECODE(PEO.SEX, 'M', 1, NULL), NULL)) A_PMEN,
182 COUNT(DECODE(PEI.PEI_INFORMATION5, '4', DECODE(PEO.SEX, 'F', 1, NULL), '5', DECODE(PEO.SEX, 'F', 1, NULL), NULL)) A_PWMEN,
183 COUNT(DECODE(PEI.PEI_INFORMATION5, '3', DECODE(PEO.SEX, 'M', 1, NULL), '9', DECODE(PEO.SEX, 'M', 1, NULL), NULL)) HMEN,
184 COUNT(DECODE(PEI.PEI_INFORMATION5, '3', DECODE(PEO.SEX, 'F', 1, NULL), '9', DECODE(PEO.SEX, 'F', 1, NULL), NULL)) HWMEN,
185 COUNT(DECODE(PEI.PEI_INFORMATION5, '1', DECODE(PEO.SEX, 'M', 1, NULL), NULL)) WNHMEN,
186 COUNT(DECODE(PEI.PEI_INFORMATION5, '1', DECODE(PEO.SEX, 'F', 1, NULL), NULL)) WNHWMEN,
187 COUNT(DECODE(PEI.PEI_INFORMATION5, NULL, DECODE(PEO.SEX, 'M', 1, NULL), NULL)) URMEN,
188 COUNT(DECODE(PEI.PEI_INFORMATION5, NULL, DECODE(PEO.SEX, 'F', 1, NULL), NULL)) URWMEN
189 FROM PER_ALL_PEOPLE_F PEO,
190 PER_ALL_ASSIGNMENTS_F ASS,
191 PER_ASSIGNMENT_STATUS_TYPES AST,
192 PER_JOBS JOB,
193 PER_PAY_PROPOSALS PPP,
194 PER_PAY_BASES PPB,
195 HR_LOOKUPS HL,
196 PER_PEOPLE_EXTRA_INFO PEI
197 WHERE PEO.PERSON_ID = ASS.PERSON_ID
198 AND PEO.PER_INFORMATION1 = '13'
199 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
200 AND PEO.PERSON_ID = PEI.PERSON_ID(+)
201 AND(PEI.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT' OR(PEI.INFORMATION_TYPE <> 'PER_US_ADDL_ETHNIC_CAT'
202 AND NOT EXISTS
203 (SELECT 1
204 FROM PER_PEOPLE_EXTRA_INFO PEI2
205 WHERE PEI2.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
206 AND PEI2.PERSON_ID = PEI.PERSON_ID)
207 AND PEI.PERSON_EXTRA_INFO_ID =
208 (SELECT MAX(PEI1.PERSON_EXTRA_INFO_ID)
209 FROM PER_PEOPLE_EXTRA_INFO PEI1
210 WHERE PEI1.PERSON_ID = PEI.PERSON_ID))
211 OR(NOT EXISTS
212 (SELECT PERSON_EXTRA_INFO_ID
213 FROM PER_PEOPLE_EXTRA_INFO PEI3
214 WHERE PEI3.PERSON_ID = PEI.PERSON_ID))
215 )
216 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
217 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(ASS.EMPLOYMENT_CATEGORY, CP_FR, CP_FT, CP_PR, CP_PT) IN('PR')
218 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
219 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
220 AND HL.LOOKUP_CODE IN('1', '2', '3')
221 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
222 AND PEO.EFFECTIVE_END_DATE
223 AND P_REPORT_DATE BETWEEN ASS.EFFECTIVE_START_DATE
224 AND ASS.EFFECTIVE_END_DATE
225 AND ASS.PRIMARY_FLAG = 'Y'
226 AND ASS.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
227 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
228 AND ASS.PAY_BASIS_ID = PPB.PAY_BASIS_ID
229 AND ASS.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
230 AND PPP.CHANGE_DATE =
231 (SELECT MAX(CHANGE_DATE)
232 FROM PER_PAY_PROPOSALS PRO
233 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
234 AND PRO.CHANGE_DATE <= P_REPORT_DATE
235 AND PRO.APPROVED = 'Y' )
236 AND NVL(PPP.PROPOSED_SALARY_N, 0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
237 AND ASS.JOB_ID = JOB.JOB_ID
238 AND ASS.ASSIGNMENT_TYPE = 'E'
239 AND ASS.ORGANIZATION_ID IN
240 (SELECT ORGANIZATION_ID
241 FROM HR_ALL_ORGANIZATION_UNITS
242 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID)
243 ;
244 CURSOR GET_LINE2_COUNTS IS
245 SELECT '2' LINE,
246 HL.LOOKUP_CODE JOBCODE,
247 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), '1', DECODE(PEO.SEX, 'M', 1, NULL), NULL)) NRMEN,
248 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), '1', DECODE(PEO.SEX, 'F', 1, NULL), NULL)) NRWMEN,
249 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), NULL, (DECODE(PEO.PER_INFORMATION1, '2', DECODE(PEO.SEX, 'M', 1, NULL), NULL)))) BNHMEN,
250 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), NULL, (DECODE(PEO.PER_INFORMATION1, '2', DECODE(PEO.SEX, 'F', 1, NULL), NULL)))) BNHWMEN,
251 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), NULL, (DECODE(PEO.PER_INFORMATION1, '6', DECODE(PEO.SEX, 'M', 1, NULL), NULL)))) AM_ALMEN,
252 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), NULL, (DECODE(PEO.PER_INFORMATION1, '6', DECODE(PEO.SEX, 'F', 1, NULL), NULL)))) AM_ALWMEN,
253 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), NULL, (DECODE(PEO.PER_INFORMATION1, '4', DECODE(PEO.SEX, 'M', 1, NULL), '5', DECODE(PEO.SEX, 'M', 1, NULL), NULL)))) APMEN,
254 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), NULL, (DECODE(PEO.PER_INFORMATION1, '4', DECODE(PEO.SEX, 'F', 1, NULL), '5', DECODE(PEO.SEX, 'F', 1, NULL), NULL)))) APWMEN,
255 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), NULL, (DECODE(PEO.PER_INFORMATION1, '3', DECODE(PEO.SEX, 'M', 1, NULL), '9', DECODE(PEO.SEX, 'M', 1, NULL), NULL)))) HMEN,
256 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), NULL, (DECODE(PEO.PER_INFORMATION1, '3', DECODE(PEO.SEX, 'F', 1, NULL), '9', DECODE(PEO.SEX, 'F', 1, NULL), NULL)))) HWMEN,
257 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), NULL, (DECODE(PEO.PER_INFORMATION1, '1', DECODE(PEO.SEX, 'M', 1, NULL), NULL)))) WNHMEN,
258 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), NULL, (DECODE(PEO.PER_INFORMATION1, '1', DECODE(PEO.SEX, 'F', 1, NULL), NULL)))) WNHWMEN,
259 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), NULL, (DECODE(PEO.PER_INFORMATION1, NULL, DECODE(PEO.SEX, 'M', 1, NULL), NULL)))) URMEN,
260 COUNT(DECODE(PQH_NR_ALIEN_PKG.GET_COUNT_NR_ALIEN(PEO.PERSON_ID, P_REPORT_DATE), NULL, (DECODE(PEO.PER_INFORMATION1, NULL, DECODE(PEO.SEX, 'F', 1, NULL), NULL)))) URWMEN
261 FROM PER_ALL_PEOPLE_F PEO,
262 PER_ALL_ASSIGNMENTS_F ASS,
263 PER_ASSIGNMENT_STATUS_TYPES AST,
264 PER_JOBS JOB,
265 PER_PAY_PROPOSALS PPP,
266 PER_PAY_BASES PPB,
267 HR_LOOKUPS HL
268 WHERE PEO.PERSON_ID = ASS.PERSON_ID
269 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
270 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
271 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(ASS.EMPLOYMENT_CATEGORY, CP_FR, CP_FT, CP_PR, CP_PT) IN('PR')
272 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
273 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
274 AND HL.LOOKUP_CODE NOT IN('1', '2', '3')
275 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
276 AND PEO.EFFECTIVE_END_DATE
277 AND P_REPORT_DATE BETWEEN ASS.EFFECTIVE_START_DATE
278 AND ASS.EFFECTIVE_END_DATE
279 AND ASS.PRIMARY_FLAG = 'Y'
280 AND ASS.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
281 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
282 AND ASS.PAY_BASIS_ID = PPB.PAY_BASIS_ID
283 AND ASS.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
284 AND PPP.CHANGE_DATE =
285 (SELECT MAX(CHANGE_DATE)
286 FROM PER_PAY_PROPOSALS PRO
287 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
288 AND PRO.CHANGE_DATE <= P_REPORT_DATE
289 AND PRO.APPROVED = 'Y' )
290 AND NVL(PPP.PROPOSED_SALARY_N, 0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
291 AND ASS.JOB_ID = JOB.JOB_ID
292 AND ASS.ASSIGNMENT_TYPE = 'E'
293 AND ASS.ORGANIZATION_ID IN
294 (SELECT ORGANIZATION_ID
295 FROM HR_ALL_ORGANIZATION_UNITS
296 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID)
297 GROUP BY HL.LOOKUP_CODE;
298 CURSOR GET_TMR_LINE2_COUNTS(C_LOOKUP_CODE IN VARCHAR2) IS
299 SELECT COUNT(DECODE(PEI.PEI_INFORMATION5, '2', DECODE(PEO.SEX, 'M', 1, NULL), NULL)) BNHMEN,
300 COUNT(DECODE(PEI.PEI_INFORMATION5, '2', DECODE(PEO.SEX, 'F', 1, NULL), NULL)) BNHWMEN,
301 COUNT(DECODE(PEI.PEI_INFORMATION5, '6', DECODE(PEO.SEX, 'M', 1, NULL), NULL)) AM_ALMEN,
302 COUNT(DECODE(PEI.PEI_INFORMATION5, '6', DECODE(PEO.SEX, 'F', 1, NULL), NULL)) AM_ALWMEN,
303 COUNT(DECODE(PEI.PEI_INFORMATION5, '4', DECODE(PEO.SEX, 'M', 1, NULL), '5', DECODE(PEO.SEX, 'M', 1, NULL), NULL)) A_PMEN,
304 COUNT(DECODE(PEI.PEI_INFORMATION5, '4', DECODE(PEO.SEX, 'F', 1, NULL), '5', DECODE(PEO.SEX, 'F', 1, NULL), NULL)) A_PWMEN,
305 COUNT(DECODE(PEI.PEI_INFORMATION5, '3', DECODE(PEO.SEX, 'M', 1, NULL), '9', DECODE(PEO.SEX, 'M', 1, NULL), NULL)) HMEN,
306 COUNT(DECODE(PEI.PEI_INFORMATION5, '3', DECODE(PEO.SEX, 'F', 1, NULL), '9', DECODE(PEO.SEX, 'F', 1, NULL), NULL)) HWMEN,
307 COUNT(DECODE(PEI.PEI_INFORMATION5, '1', DECODE(PEO.SEX, 'M', 1, NULL), NULL)) WNHMEN,
308 COUNT(DECODE(PEI.PEI_INFORMATION5, '1', DECODE(PEO.SEX, 'F', 1, NULL), NULL)) WNHWMEN,
309 COUNT(DECODE(PEI.PEI_INFORMATION5, NULL, DECODE(PEO.SEX, 'M', 1, NULL), NULL)) URMEN,
310 COUNT(DECODE(PEI.PEI_INFORMATION5, NULL, DECODE(PEO.SEX, 'F', 1, NULL), NULL)) URWMEN
311 FROM PER_ALL_PEOPLE_F PEO,
312 PER_ALL_ASSIGNMENTS_F ASS,
313 PER_ASSIGNMENT_STATUS_TYPES AST,
314 PER_JOBS JOB,
315 PER_PAY_PROPOSALS PPP,
316 PER_PAY_BASES PPB,
317 HR_LOOKUPS HL,
318 PER_PEOPLE_EXTRA_INFO PEI
319 WHERE PEO.PERSON_ID = ASS.PERSON_ID
320 AND PEO.PER_INFORMATION1 = '13'
321 AND PEO.CURRENT_EMPLOYEE_FLAG = 'Y'
322 AND PEO.PERSON_ID = PEI.PERSON_ID(+)
323 AND(PEI.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT' OR(PEI.INFORMATION_TYPE <> 'PER_US_ADDL_ETHNIC_CAT'
324 AND NOT EXISTS
325 (SELECT 1
326 FROM PER_PEOPLE_EXTRA_INFO PEI2
327 WHERE PEI2.INFORMATION_TYPE = 'PER_US_ADDL_ETHNIC_CAT'
328 AND PEI2.PERSON_ID = PEI.PERSON_ID)
329 AND PEI.PERSON_EXTRA_INFO_ID =
330 (SELECT MAX(PEI1.PERSON_EXTRA_INFO_ID)
331 FROM PER_PEOPLE_EXTRA_INFO PEI1
332 WHERE PEI1.PERSON_ID = PEI.PERSON_ID))
333 OR(NOT EXISTS
334 (SELECT PERSON_EXTRA_INFO_ID
335 FROM PER_PEOPLE_EXTRA_INFO PEI3
336 WHERE PEI3.PERSON_ID = PEI.PERSON_ID))
337 )
338 AND HL.LOOKUP_CODE = JOB.JOB_INFORMATION8
339 AND PQH_EMPLOYMENT_CATEGORY.IDENTIFY_EMPL_CATEGORY(ASS.EMPLOYMENT_CATEGORY, CP_FR, CP_FT, CP_PR, CP_PT) IN('PR')
340 AND HL.LOOKUP_TYPE = 'US_IPEDS_JOB_CATEGORIES'
341 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
342 AND HL.LOOKUP_CODE NOT IN('1', '2', '3')
343 AND P_REPORT_DATE BETWEEN PEO.EFFECTIVE_START_DATE
344 AND PEO.EFFECTIVE_END_DATE
345 AND P_REPORT_DATE BETWEEN ASS.EFFECTIVE_START_DATE
346 AND ASS.EFFECTIVE_END_DATE
347 AND ASS.PRIMARY_FLAG = 'Y'
348 AND ASS.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
349 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN'
350 AND ASS.PAY_BASIS_ID = PPB.PAY_BASIS_ID
351 AND ASS.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
352 AND PPP.CHANGE_DATE =
353 (SELECT MAX(CHANGE_DATE)
354 FROM PER_PAY_PROPOSALS PRO
355 WHERE PPP.ASSIGNMENT_ID = PRO.ASSIGNMENT_ID
356 AND PRO.CHANGE_DATE <= P_REPORT_DATE
357 AND PRO.APPROVED = 'Y' )
358 AND NVL(PPP.PROPOSED_SALARY_N, 0) * PPB.PAY_ANNUALIZATION_FACTOR > 0
359 AND ASS.JOB_ID = JOB.JOB_ID
360 AND ASS.ASSIGNMENT_TYPE = 'E'
361 AND ASS.ORGANIZATION_ID IN
362 (SELECT ORGANIZATION_ID
363 FROM HR_ALL_ORGANIZATION_UNITS
364 WHERE BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID)
365 AND HL.LOOKUP_CODE = C_LOOKUP_CODE;
366
367 begin
368 --hr_standard.event('BEFORE REPORT');
369 LP_REPORT_DATE := to_char(P_REPORT_DATE,'DD-MON-YYYY');
370 pqh_employment_category.fetch_empl_categories(p_business_group_id,l_fr,l_ft,l_pr,l_pt);
371
372 cp_fr := l_fr;
373 cp_ft := l_ft;
374 cp_pr := l_pr;
375 cp_pt := l_pt;
376 FOR I IN GET_LINE2_COUNTS
377 LOOP
378 LINE := I.LINE;
379 SC := I.JOBCODE;
380 L_NR_MEN := I.NRMEN;
381 L_NR_WMEN := I.NRWMEN;
382 L_BNH_MEN := I.BNHMEN;
383 L_BNH_WMEN := I.BNHWMEN;
384 L_AMAI_MEN := I.AM_ALMEN;
385 L_AMAI_WMEN := I.AM_ALWMEN;
386 L_AP_MEN := I.APMEN;
387 L_AP_WMEN := I.APWMEN;
388 L_H_MEN := I.HMEN;
389 L_H_WMEN := I.HWMEN;
390 L_WNH_MEN := I.WNHMEN;
391 L_WNH_WMEN := I.WNHWMEN;
392 L_UR_MEN := I.URMEN;
393 L_UR_WMEN := I.URWMEN;
394 L_TOT_MEN := L_NR_MEN + L_BNH_MEN + L_AMAI_MEN + L_AP_MEN + L_H_MEN + L_WNH_MEN + L_UR_MEN;
395 L_TOT_WMEN := L_NR_WMEN + L_BNH_WMEN + L_AMAI_WMEN + L_AP_WMEN + L_H_WMEN + L_WNH_WMEN + L_UR_WMEN;
396 FOR J IN GET_TMR_LINE2_COUNTS(sc)
397 LOOP
398 L_BNH_MEN := L_BNH_MEN + J.BNHMEN;
399 L_BNH_WMEN := L_BNH_WMEN + J.BNHWMEN;
400 L_AMAI_MEN := L_AMAI_MEN + J.AM_ALMEN;
401 L_AMAI_WMEN := L_AMAI_WMEN + J.AM_ALWMEN;
402 L_AP_MEN := L_AP_MEN + J.A_PMEN;
403 L_AP_WMEN := L_AP_WMEN + J.A_PWMEN;
404 L_H_MEN := L_H_MEN + J.HMEN;
405 L_H_WMEN := L_H_WMEN + J.HWMEN;
406 L_WNH_MEN := L_WNH_MEN + J.WNHMEN;
407 L_WNH_WMEN := L_WNH_WMEN + J.WNHWMEN;
408 L_UR_MEN := L_UR_MEN + J.URMEN;
409 L_UR_WMEN := L_UR_WMEN + J.URWMEN;
410 L_TOT_MEN := L_TOT_MEN + J.BNHMEN + J.AM_ALMEN + J.A_PMEN + J.HMEN + J.WNHMEN + J.URMEN;
411 L_TOT_WMEN := L_TOT_WMEN + J.BNHWMEN + J.AM_ALWMEN + J.A_PWMEN + J.HWMEN + J.WNHWMEN + J.URWMEN;
412 END LOOP;
413
414 INSERT
415 INTO PAY_US_RPT_TOTALS(SESSION_ID,
416 ATTRIBUTE1, ATTRIBUTE2, VALUE1, VALUE2, VALUE3, VALUE4, VALUE5, VALUE6, VALUE7,
417 VALUE8, VALUE9, VALUE10, VALUE11, VALUE12, VALUE13, VALUE14, VALUE15, VALUE16, VALUE17)
418 VALUES(USERENV('SESSIONID'), 'IPED7', SC, LINE, L_NR_MEN, L_NR_WMEN, L_BNH_MEN,
419 L_BNH_WMEN, L_AMAI_MEN, L_AMAI_WMEN, L_AP_MEN, L_AP_WMEN, L_H_MEN, L_H_WMEN,
420 L_WNH_MEN, L_WNH_WMEN, L_UR_MEN, L_UR_WMEN, L_TOT_MEN, L_TOT_WMEN);
421 COMMIT;
422 END LOOP;
423
424 OPEN GET_LINE1_COUNTS;
425 FETCH GET_LINE1_COUNTS
426 INTO LINE,
427 SC,
428 L_NR_MEN,
429 L_NR_WMEN,
430 L_BNH_MEN,
431 L_BNH_WMEN,
432 L_AMAI_MEN,
433 L_AMAI_WMEN,
434 L_AP_MEN,
435 L_AP_WMEN,
436 L_H_MEN,
437 L_H_WMEN,
438 L_WNH_MEN,
439 L_WNH_WMEN,
440 L_UR_MEN,
441 L_UR_WMEN;
442 CLOSE GET_LINE1_COUNTS;
443 L_TOT_MEN := L_NR_MEN + L_BNH_MEN + L_AMAI_MEN + L_AP_MEN + L_H_MEN + L_WNH_MEN + L_UR_MEN;
444 L_TOT_WMEN := L_NR_WMEN + L_BNH_WMEN + L_AMAI_WMEN + L_AP_WMEN + L_H_WMEN + L_WNH_WMEN + L_UR_WMEN;
445
446 OPEN GET_LINE1_TMRACES_COUNTS;
447 FETCH GET_LINE1_TMRACES_COUNTS
448 INTO L_TMR_BNH_MEN,
449 L_TMR_BNH_WMEN,
450 L_TMR_AMAI_MEN,
451 L_TMR_AMAI_WMEN,
452 L_TMR_AP_MEN,
453 L_TMR_AP_WMEN,
454 L_TMR_H_MEN,
455 L_TMR_H_WMEN,
456 L_TMR_WNH_MEN,
457 L_TMR_WNH_WMEN,
458 L_TMR_UR_MEN,
459 L_TMR_UR_WMEN;
460 CLOSE GET_LINE1_TMRACES_COUNTS;
461 L_BNH_MEN := L_BNH_MEN + L_TMR_BNH_MEN;
462 L_BNH_WMEN := L_BNH_WMEN + L_TMR_BNH_WMEN;
463 L_AMAI_MEN := L_AMAI_MEN + L_TMR_AMAI_MEN;
464 L_AMAI_WMEN := L_AMAI_WMEN + L_TMR_AMAI_WMEN;
465 L_AP_MEN := L_AP_MEN + L_TMR_AP_MEN;
466 L_AP_WMEN := L_AP_WMEN + L_TMR_AP_WMEN;
467 L_H_MEN := L_H_MEN + L_TMR_H_MEN;
468 L_H_WMEN := L_H_WMEN + L_TMR_H_WMEN;
469 L_WNH_MEN := L_WNH_MEN + L_TMR_WNH_MEN;
470 L_WNH_WMEN := L_WNH_WMEN + L_TMR_WNH_WMEN;
471 L_UR_MEN := L_UR_MEN + L_TMR_UR_MEN;
472 L_UR_WMEN := L_UR_WMEN + L_TMR_UR_WMEN;
473 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;
474 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;
475 INSERT
476 INTO PAY_US_RPT_TOTALS(SESSION_ID, ATTRIBUTE1,
477 ATTRIBUTE2, VALUE1, VALUE2, VALUE3, VALUE4, VALUE5,
478 VALUE6, VALUE7, VALUE8, VALUE9, VALUE10, VALUE11, VALUE12, VALUE13,
479 VALUE14, VALUE15, VALUE16, VALUE17)
480 VALUES(USERENV('SESSIONID'), 'IPED7', SC, LINE, L_NR_MEN, L_NR_WMEN, L_BNH_MEN,
481 L_BNH_WMEN, L_AMAI_MEN, L_AMAI_WMEN, L_AP_MEN, L_AP_WMEN, L_H_MEN, L_H_WMEN,
482 L_WNH_MEN, L_WNH_WMEN, L_UR_MEN, L_UR_WMEN, L_TOT_MEN, L_TOT_WMEN);
483 COMMIT;
484 return true;
485 end;
486
487 function line_noFormula return Number is
488 temp_num number;
489 begin
490 temp_num := line_num;
491 line_num:= line_num + 1;
492
493 return temp_num;
494 end;
495
496 function AfterReport return boolean is
497 begin
498 --hr_standard.event('AFTER REPORT');
499 EXECUTE IMMEDIATE 'DELETE FROM PAY_US_RPT_TOTALS
500 WHERE ATTRIBUTE1 = ''IPED7''';
501 return (TRUE);
502 end;
503
504 --Functions to refer Oracle report placeholders--
505
506 Function CP_FR_p return varchar2 is
507 Begin
508 return CP_FR;
509 END;
510 Function CP_FT_p return varchar2 is
511 Begin
512 return CP_FT;
513 END;
514 Function CP_PR_p return varchar2 is
515 Begin
516 return CP_PR;
517 END;
518 Function CP_PT_p return varchar2 is
519 Begin
520 return CP_PT;
521 END;
522 Function line_num_p return number is
523 Begin
524 return line_num;
525 END;
526 Function LastLineNo_p return number is
527 Begin
528 return LastLineNo;
529 END;
530 Function totTitle_p return varchar2 is
531 Begin
532 return totTitle;
533 END;
534 END PQH_PQIPED7_XMLP_PKG ;