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