[Home] [Help]
PACKAGE BODY: APPS.PER_PERUSE1L_XMLP_PKG
Source
1 PACKAGE BODY PER_PERUSE1L_XMLP_PKG AS
2 /* $Header: PERUSE1LB.pls 120.3 2011/06/01 10:39:50 nvelaga ship $ */
3 --G_FILE_TYPE TEXT_IO.FILE_TYPE;
4
5 --G_IL_FEIN VARCHAR2(10);
6
7 --G_FILE_NAME VARCHAR2(30);
8
9 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
13 ORG_INFORMATION9,
10 CURSOR C_DEFAULTS IS
11 SELECT
12 ORG_INFORMATION8,
14 ORG_INFORMATION11,
15 ORG_INFORMATION12
16 FROM
17 HR_ORGANIZATION_INFORMATION
18 WHERE ORGANIZATION_ID = C_PARENT_ORG_ID
19 AND ORG_INFORMATION_CONTEXT = 'VETS_EEO_Dup';
20 L_DEFAULT C_DEFAULTS%ROWTYPE;
21 L_BUFFER VARCHAR2(1000);
22 G_DELIMITER VARCHAR2(1) := ',';
23 G_EOL VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(10);
24 L_DUMMY BOOLEAN;
25 BEGIN
26 --HR_STANDARD.EVENT('BEFORE REPORT');
27 L_DUMMY:=P_REPORT_YEARVALIDTRIGGER;
28 C_END_OF_TIME := hr_general.END_OF_TIME;
29 C_BUSINESS_GROUP_NAME := hr_reports.GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
30 C_MAXIMUM_NO_OF_EMPLOYEES := P_MAXIMUM_NO_OF_EMPLOYEES;
31 C_PAYROLL_PERIOD_DATE_START := FND_DATE.CANONICAL_TO_DATE(P_PAYROLL_PERIOD_DATE_START);
32 C_PAYROLL_PERIOD_DATE_END := FND_DATE.CANONICAL_TO_DATE(P_PAYROLL_PERIOD_DATE_END);
33 C_REPORT_YEAR := P_REPORT_YEAR;
34 C_REPORT_DATE := TRUNC(SYSDATE);
35 SELECT
36 PGH.NAME,
37 PGV.VERSION_NUMBER,
38 PGN.ENTITY_ID,
39 PGN.HIERARCHY_NODE_ID
40 INTO C_HIERARCHY_NAME,C_HIERARCHY_VERSION_NUM,C_PARENT_ORG_ID,C_PARENT_NODE_ID
41 FROM
42 PER_GEN_HIERARCHY PGH,
43 PER_GEN_HIERARCHY_VERSIONS PGV,
44 PER_GEN_HIERARCHY_NODES PGN
45 WHERE PGH.HIERARCHY_ID = P_HIERARCHY_ID
46 AND PGH.HIERARCHY_ID = PGV.HIERARCHY_ID
47 AND PGV.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
48 AND PGN.HIERARCHY_VERSION_ID = PGV.HIERARCHY_VERSION_ID
49 AND PGN.NODE_TYPE = 'PAR';
50 SELECT
51 COUNT(PGHN.HIERARCHY_NODE_ID)
52 INTO C_NO_OF_ESTABLISHMENTS
53 FROM
54 PER_GEN_HIERARCHY_NODES PGHN
55 WHERE PGHN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
56 AND PGHN.NODE_TYPE = 'EST';
57 OPEN C_DEFAULTS;
58 FETCH C_DEFAULTS
59 INTO L_DEFAULT;
60 IF C_DEFAULTS%NOTFOUND THEN
61 NULL;
62 ELSE
63 DEFAULT_ACTIVITIES_LINE1 := UPPER(LTRIM(RTRIM(L_DEFAULT.ORG_INFORMATION8)));
64 DEFAULT_ACTIVITIES_LINE2 := UPPER(LTRIM(RTRIM(L_DEFAULT.ORG_INFORMATION9)));
65 DEFAULT_ACTIVITIES_LINE3 := UPPER(LTRIM(RTRIM(L_DEFAULT.ORG_INFORMATION11)));
66 DEFAULT_ACTIVITIES_LINE4 := UPPER(LTRIM(RTRIM(L_DEFAULT.ORG_INFORMATION12)));
67 END IF;
68 CLOSE C_DEFAULTS;
69 IF P_AUDIT_REPORT = 'Y' THEN
70 --OPEN;
71 NULL;
72 L_BUFFER := 'Person Id' || G_DELIMITER || 'Last Name' || G_DELIMITER || 'First Name' || G_DELIMITER || 'Employee Number' || G_DELIMITER || 'Gender' || G_DELIMITER || 'Ethnic Origin' || G_DELIMITER
73 || 'Assignment Id' || G_DELIMITER || 'Job Id' || G_DELIMITER || 'Job Name' || G_DELIMITER || 'Location Id' || G_DELIMITER || 'Location Code' || G_DELIMITER || G_EOL;
74 --PUT(L_BUFFER);
75 FND_FILE.PUT_LINE(FND_FILE.LOG,L_BUFFER);
76 END IF;
77 RETURN TRUE;
78 END BEFOREREPORT;
79
80 FUNCTION P_REPORT_YEARVALIDTRIGGER RETURN BOOLEAN IS
81 BEGIN
82 IF P_PAYROLL_PERIOD_DATE_END IS NOT NULL THEN
83 P_REPORT_YEAR := TO_CHAR(FND_DATE.CANONICAL_TO_DATE(P_PAYROLL_PERIOD_DATE_END)
84 ,'YYYY');
85 ELSE
86 P_REPORT_YEAR := TO_CHAR(SYSDATE
87 ,'YYYY');
88 END IF;
89 RETURN (TRUE);
90 END P_REPORT_YEARVALIDTRIGGER;
91
92 FUNCTION AFTERREPORT RETURN BOOLEAN IS
93 BEGIN
94 --HR_STANDARD.EVENT('AFTER REPORT');
95 IF P_AUDIT_REPORT = 'Y' THEN
96 --CLOSE;
97 NULL;
98 END IF;
99 RETURN (TRUE);
100 END AFTERREPORT;
101
102 FUNCTION C_TOT_ACTFORMULA(EST_NODE_ID IN NUMBER
103 ,HEADQUARTERS IN VARCHAR2) RETURN NUMBER IS
104 L_E_ACT1 VARCHAR2(150) := NULL;
105 L_E_ACT2 VARCHAR2(150) := NULL;
106 L_E_ACT3 VARCHAR2(150) := NULL;
107 L_E_ACT4 VARCHAR2(150) := NULL;
108 L_ACTIVITY VARCHAR2(150) := NULL;
109 L_DATE_END DATE := FND_DATE.CANONICAL_TO_DATE(P_PAYROLL_PERIOD_DATE_END);
110 L_DATE_START DATE := FND_DATE.CANONICAL_TO_DATE(P_PAYROLL_PERIOD_DATE_START);
111 L_COUNT_EMPS NUMBER := 0;
112 L_REPORT_EMPS NUMBER := 0;
113 L_BUFFER VARCHAR2(2000);
114 G_DELIMITER VARCHAR2(1) := ',';
115 G_EOL VARCHAR2(1) := FND_GLOBAL.LOCAL_CHR(10);
116 CURSOR C_EST IS
117 SELECT
118 NVL(HLEI1.LEI_INFORMATION5
119 ,DEFAULT_ACTIVITIES_LINE1),
120 NVL(HLEI1.LEI_INFORMATION6
121 ,DEFAULT_ACTIVITIES_LINE2),
122 NVL(HLEI1.LEI_INFORMATION7
123 ,DEFAULT_ACTIVITIES_LINE3),
124 NVL(HLEI1.LEI_INFORMATION8
125 ,DEFAULT_ACTIVITIES_LINE4)
126 FROM
127 HR_LOCATION_EXTRA_INFO HLEI1,
128 PER_GEN_HIERARCHY_NODES PGHN
129 WHERE ( HLEI1.INFORMATION_TYPE = 'EEO-1 Specific Information'
130 AND HLEI1.LEI_INFORMATION_CATEGORY = 'EEO-1 Specific Information' )
131 AND HLEI1.LOCATION_ID = PGHN.ENTITY_ID
132 AND PGHN.PARENT_HIERARCHY_NODE_ID = C_PARENT_NODE_ID
133 AND PGHN.HIERARCHY_NODE_ID = C_TOT_ACTFORMULA.EST_NODE_ID
134 AND PGHN.NODE_TYPE = 'EST';
135 CURSOR C_AUDIT_REPORT IS
136 SELECT
137 PEO.PERSON_ID,
138 PEO.LAST_NAME,
139 PEO.FIRST_NAME,
140 PEO.EMPLOYEE_NUMBER,
141 PEO.SEX,
142 --PEO.PER_INFORMATION1 ETHNIC, /* Commented for bug#11736960 */
143 ASS.ASSIGNMENT_ID,
144 ASS.JOB_ID,
145 JOB.NAME JOB_NAME,
146 ASS.LOCATION_ID,
147 HL.LOCATION_CODE
148 FROM
149 /* Replaced PER_PEOPLE_F with PER_ALL_PEOPLE_F and PER_JOBS_VL
150 with PER_JOBS - bug#11736960 */
151 --PER_PEOPLE_F PEO
152 PER_ALL_PEOPLE_F PEO,
153 PER_ASSIGNMENTS_F ASS,
154 --PER_JOBS_VL JOB,
155 PER_JOBS JOB,
156 HR_LOCATIONS_ALL HL
157 WHERE PEO.PERSON_ID = ASS.PERSON_ID
158 --
159 /* Commented for bug#11736960 starts
160 AND PEO.PER_INFORMATION1 is not null
161 Commented for bug#11736960 ends */
162 --
163 AND PEO.PER_INFORMATION_CATEGORY = 'US'
164 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
165 AND L_DATE_START <= NVL(JOB.DATE_TO
166 ,L_DATE_START)
167 AND L_DATE_END >= JOB.DATE_FROM
168 AND JOB.JOB_INFORMATION1 is not NULL
169 AND ASS.JOB_ID = JOB.JOB_ID
170 AND PEO.EFFECTIVE_START_DATE = (
171 SELECT
172 MAX(PEO1.EFFECTIVE_START_DATE)
173 FROM
174 -- Replaced PER_PEOPLE_F with PER_ALL_PEOPLE_F -bug#11736960
175 --PER_PEOPLE_F PEO1
176 PER_ALL_PEOPLE_F PEO1
177 WHERE L_DATE_START <= PEO1.EFFECTIVE_END_DATE
178 AND L_DATE_END >= PEO1.EFFECTIVE_START_DATE
179 AND PEO.PERSON_ID = PEO1.PERSON_ID
180 AND PEO1.CURRENT_EMPLOYEE_FLAG = 'Y' )
181 AND ASS.EFFECTIVE_START_DATE = (
182 SELECT
183 MAX(ASS1.EFFECTIVE_START_DATE)
184 FROM
185 PER_ASSIGNMENTS_F ASS1,
186 PER_ASSIGNMENT_STATUS_TYPES AST --Added for bug#12594720
187 WHERE L_DATE_START <= ASS1.EFFECTIVE_END_DATE
188 AND L_DATE_END >= ASS1.EFFECTIVE_START_DATE
189 AND ASS.PERSON_ID = ASS1.PERSON_ID
190 AND ASS1.ASSIGNMENT_TYPE = 'E'
191 AND ASS1.PRIMARY_FLAG = 'Y'
192 AND ASS1.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
193 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN')
194 AND ASS.ASSIGNMENT_TYPE = 'E'
195 AND ASS.PRIMARY_FLAG = 'Y'
196 AND ASS.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
197 AND PEO.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
198 AND JOB.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
199 AND EXISTS (
200 SELECT
201 'X'
202 FROM
203 HR_ORGANIZATION_INFORMATION HOI1
204 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
205 AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
206 AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
207 )
208 AND EXISTS (
209 SELECT
210 'X'
211 FROM HR_ORGANIZATION_INFORMATION HOI2
212 where ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
213 AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
214 AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories'
215 )
216 /* Replaced this exists clasue with two seperate exists condition inorder to
217 avoid Merge Join Cartesian.
218 AND EXISTS (
219 SELECT
220 'X'
221 FROM
222 HR_ORGANIZATION_INFORMATION HOI1,
223 HR_ORGANIZATION_INFORMATION HOI2
224 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
225 AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
226 AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
227 AND ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
228 AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
229 AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories' )
230 */
231 AND ASS.LOCATION_ID = HL.LOCATION_ID
232 AND EXISTS (
233 SELECT 1
234 FROM
235 PER_GEN_HIERARCHY_NODES PGN
236 WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
237 AND ( PGN.HIERARCHY_NODE_ID = EST_NODE_ID
238 OR PGN.PARENT_HIERARCHY_NODE_ID = EST_NODE_ID )
239 AND PGN.NODE_TYPE in ( 'EST' , 'LOC' )
240 AND ASS.LOCATION_ID=PGN.ENTITY_ID
241 )
242 /* Replaced this in clause with the above exists clause.
243 AND ASS.LOCATION_ID in (
244 SELECT
245 DISTINCT
246 PGN.ENTITY_ID
247 FROM
248 PER_GEN_HIERARCHY_NODES PGN
249 WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
250 AND ( PGN.HIERARCHY_NODE_ID = EST_NODE_ID
251 OR PGN.PARENT_HIERARCHY_NODE_ID = EST_NODE_ID )
252 AND PGN.NODE_TYPE in ( 'EST' , 'LOC' ) )
253 */
254 AND HEADQUARTERS = 'N'
255 -- Added for bug#11736960
256 AND EXISTS (SELECT 'X'
257 FROM PER_PEOPLE_EXTRA_INFO PEI
258 WHERE PEI.PERSON_ID = PEO.PERSON_ID
259 AND PEI.INFORMATION_TYPE = 'US_ETHNIC_ORIGIN'
260 AND (PEI.PEI_INFORMATION1 = 'Y'
261 OR PEI.PEI_INFORMATION2 = 'Y'
262 OR PEI.PEI_INFORMATION3 = 'Y'
263 OR PEI.PEI_INFORMATION4 = 'Y'
264 OR PEI.PEI_INFORMATION5 = 'Y'
265 OR PEI.PEI_INFORMATION6 = 'Y'
266 OR PEI.PEI_INFORMATION7 = 'Y'
267 )
268 );
269 BEGIN
270 OPEN C_EST;
271 FETCH C_EST
272 INTO L_E_ACT1,L_E_ACT2,L_E_ACT3,L_E_ACT4;
273 IF L_E_ACT1 IS NOT NULL THEN
274 L_ACTIVITY := RPAD(LTRIM(RTRIM(UPPER(L_E_ACT1)))
275 ,40);
276 END IF;
277 IF L_E_ACT2 IS NOT NULL THEN
278 L_ACTIVITY := RPAD(L_ACTIVITY || ' ' || (LTRIM(RTRIM(UPPER(L_E_ACT2))))
279 ,40);
280 END IF;
281 IF L_E_ACT3 IS NOT NULL THEN
282 L_ACTIVITY := RPAD(L_ACTIVITY || ' ' || (LTRIM(RTRIM(UPPER(L_E_ACT3))))
283 ,40);
284 END IF;
285 IF L_E_ACT4 IS NOT NULL THEN
286 L_ACTIVITY := RPAD(L_ACTIVITY || ' ' || (LTRIM(RTRIM(UPPER(L_E_ACT4))))
287 ,40);
288 END IF;
289 C_MAIN_ACTIVITY := L_ACTIVITY;
290 SELECT
291 COUNT('X')
292 INTO L_REPORT_EMPS
293 FROM
294 --Replaced PER_PEOPLE_F with PER_ALL_PEOPLE_F and PER_JOBS_VL with PER_JOBS
295 --PER_PEOPLE_F PEO,
296 PER_ALL_PEOPLE_F PEO,
297 PER_ASSIGNMENTS_F ASS,
298 --PER_JOBS_VL JOB
299 PER_JOBS JOB
300 WHERE PEO.PERSON_ID = ASS.PERSON_ID
301 --
302 /* Commented for bug#11736960 starts
303 AND PEO.PER_INFORMATION1 is not null
304 Commented for bug#11736960 ends */
305 --
306 AND PEO.PER_INFORMATION_CATEGORY = 'US'
307 AND JOB.JOB_INFORMATION_CATEGORY = 'US'
308 AND L_DATE_START <= NVL(JOB.DATE_TO
309 ,L_DATE_START)
310 AND L_DATE_END >= JOB.DATE_FROM
311 AND JOB.JOB_INFORMATION1 is not NULL
312 AND ASS.JOB_ID = JOB.JOB_ID
313 AND PEO.EFFECTIVE_START_DATE = (
314 SELECT
315 MAX(PEO1.EFFECTIVE_START_DATE)
316 FROM
317 --PER_PEOPLE_F PEO1 - Replaced PER_PEOPLE_F with PER_ALL_PEOPLE_F
318 PER_ALL_PEOPLE_F PEO1
319 WHERE L_DATE_START <= PEO1.EFFECTIVE_END_DATE
320 AND L_DATE_END >= PEO1.EFFECTIVE_START_DATE
321 AND PEO.PERSON_ID = PEO1.PERSON_ID
322 AND PEO1.CURRENT_EMPLOYEE_FLAG = 'Y' )
323 AND ASS.EFFECTIVE_START_DATE = (
324 SELECT
325 MAX(ASS1.EFFECTIVE_START_DATE)
326 FROM
327 PER_ASSIGNMENTS_F ASS1,
328 PER_ASSIGNMENT_STATUS_TYPES AST --Added for bug#12594720
329 WHERE L_DATE_START <= ASS1.EFFECTIVE_END_DATE
330 AND L_DATE_END >= ASS1.EFFECTIVE_START_DATE
331 AND ASS.PERSON_ID = ASS1.PERSON_ID
332 AND ASS1.ASSIGNMENT_TYPE = 'E'
333 AND ASS1.PRIMARY_FLAG = 'Y'
334 AND ASS1.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
335 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN')
336 AND ASS.ASSIGNMENT_TYPE = 'E'
337 AND ASS.PRIMARY_FLAG = 'Y'
338 AND ASS.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
339 AND PEO.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
340 AND JOB.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
341 AND EXISTS (
342 SELECT
343 'X'
344 FROM
345 HR_ORGANIZATION_INFORMATION HOI1
346 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
347 AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
348 AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
349 )
350 AND EXISTS (
351 SELECT
352 'X'
353 FROM HR_ORGANIZATION_INFORMATION HOI2
354 where ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
355 AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
356 AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories'
357 )
358 /* Replaced this exists clasue with two seperate exists condition inorder to
359 avoid Merge Join Cartesian.
360 AND EXISTS (
361 SELECT
362 'X'
363 FROM
364 HR_ORGANIZATION_INFORMATION HOI1,
365 HR_ORGANIZATION_INFORMATION HOI2
366 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
367 AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
368 AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
369 AND ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
370 AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
371 AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories' )
372 */
373 AND EXISTS (
374 SELECT 1
375 FROM
376 PER_GEN_HIERARCHY_NODES PGN
377 WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
378 AND ( PGN.HIERARCHY_NODE_ID = EST_NODE_ID
379 OR PGN.PARENT_HIERARCHY_NODE_ID = EST_NODE_ID )
380 AND PGN.NODE_TYPE in ( 'EST' , 'LOC' )
381 AND ASS.LOCATION_ID = PGN.ENTITY_ID
382 )
383 /* Replaced this in clause with the above exists clause.
384 AND ASS.LOCATION_ID in (
385 SELECT
386 DISTINCT
387 PGN.ENTITY_ID
388 FROM
392 OR PGN.PARENT_HIERARCHY_NODE_ID = EST_NODE_ID )
389 PER_GEN_HIERARCHY_NODES PGN
390 WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
391 AND ( PGN.HIERARCHY_NODE_ID = EST_NODE_ID
393 AND PGN.NODE_TYPE in ( 'EST' , 'LOC' ) )
394 */
395 AND HEADQUARTERS = 'N'
396 -- Added for bug#11736960
397 AND EXISTS (SELECT 'X'
398 FROM PER_PEOPLE_EXTRA_INFO PEI
399 WHERE PEI.PERSON_ID = PEO.PERSON_ID
400 AND PEI.INFORMATION_TYPE = 'US_ETHNIC_ORIGIN'
401 AND (PEI.PEI_INFORMATION1 = 'Y'
402 OR PEI.PEI_INFORMATION2 = 'Y'
403 OR PEI.PEI_INFORMATION3 = 'Y'
404 OR PEI.PEI_INFORMATION4 = 'Y'
405 OR PEI.PEI_INFORMATION5 = 'Y'
406 OR PEI.PEI_INFORMATION6 = 'Y'
407 OR PEI.PEI_INFORMATION7 = 'Y'
408 )
409 );
410 C_REPORT_EMPS := NVL(L_REPORT_EMPS
411 ,0);
412 SELECT
413 COUNT('X')
414 INTO L_COUNT_EMPS
415 FROM
416 PER_ASSIGNMENTS_F ASS,
417 --Replaced PER_JOBS_VL with PER_JOBS -bug#11736960
418 --PER_JOBS_VL JOB
419 PER_JOBS JOB
420 WHERE JOB.JOB_INFORMATION_CATEGORY = 'US'
421 AND L_DATE_START <= NVL(JOB.DATE_TO
422 ,L_DATE_END)
423 AND L_DATE_END >= JOB.DATE_FROM
424 AND JOB.JOB_INFORMATION1 is not null
425 AND JOB.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
426 AND ASS.JOB_ID = JOB.JOB_ID
427 AND ASS.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
428 AND ASS.ASSIGNMENT_TYPE = 'E'
429 AND ASS.PRIMARY_FLAG = 'Y'
430 AND EXISTS (
431 SELECT
432 'X'
433 FROM
434 HR_ORGANIZATION_INFORMATION HOI1
435 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
436 AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
437 AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
438 )
439 AND EXISTS (
440 SELECT
441 'X'
442 FROM HR_ORGANIZATION_INFORMATION HOI2
443 where ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
444 AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
445 AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories'
446 )
447 /* Replaced this exists clasue with two seperate exists condition inorder to
448 avoid Merge Join Cartesian.
449 AND EXISTS (
450 SELECT
451 'X'
452 FROM
453 HR_ORGANIZATION_INFORMATION HOI1,
454 HR_ORGANIZATION_INFORMATION HOI2
455 WHERE TO_CHAR(ASS.ASSIGNMENT_STATUS_TYPE_ID) = HOI1.ORG_INFORMATION1
456 AND HOI1.ORG_INFORMATION_CONTEXT = 'Reporting Statuses'
457 AND HOI1.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
458 AND ASS.EMPLOYMENT_CATEGORY = HOI2.ORG_INFORMATION1
459 AND HOI2.ORGANIZATION_ID = P_BUSINESS_GROUP_ID
460 AND HOI2.ORG_INFORMATION_CONTEXT = 'Reporting Categories' )
461 */
462 AND ASS.EFFECTIVE_START_DATE = (
463 SELECT
464 MAX(ASS1.EFFECTIVE_START_DATE)
465 FROM
466 PER_ASSIGNMENTS_F ASS1,
467 PER_ASSIGNMENT_STATUS_TYPES AST --Added for bug#12594720
468 WHERE L_DATE_START <= ASS1.EFFECTIVE_END_DATE
469 AND L_DATE_END >= ASS1.EFFECTIVE_START_DATE
470 AND ASS.PERSON_ID = ASS1.PERSON_ID
471 AND ASS1.ASSIGNMENT_TYPE = 'E'
472 AND ASS1.PRIMARY_FLAG = 'Y'
473 AND ASS1.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
474 AND AST.PER_SYSTEM_STATUS <> 'TERM_ASSIGN')
475 AND L_DATE_START <= ASS.EFFECTIVE_END_DATE
476 AND L_DATE_END >= ASS.EFFECTIVE_START_DATE
477 AND HEADQUARTERS = 'N'
478 AND EXISTS (
479 SELECT 1
480 FROM
481 PER_GEN_HIERARCHY_NODES PGN
482 WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
483 AND ( PGN.HIERARCHY_NODE_ID = EST_NODE_ID
484 OR PGN.PARENT_HIERARCHY_NODE_ID = EST_NODE_ID )
485 AND PGN.NODE_TYPE in ( 'EST' , 'LOC' )
486 AND ASS.LOCATION_ID = PGN.ENTITY_ID
487 )
488 /* Replaced this in clause with the above exists clause.
489 AND ASS.LOCATION_ID in (
490 SELECT
491 DISTINCT
492 PGN.ENTITY_ID
493 FROM
494 PER_GEN_HIERARCHY_NODES PGN
495 WHERE PGN.HIERARCHY_VERSION_ID = P_HIERARCHY_VERSION_ID
496 AND ( PGN.HIERARCHY_NODE_ID = EST_NODE_ID
497 OR PGN.PARENT_HIERARCHY_NODE_ID = EST_NODE_ID )
498 AND PGN.NODE_TYPE in ( 'EST' , 'LOC' ) )
499 */
500 ;
501 C_TOT_EMPS := NVL(L_COUNT_EMPS
502 ,0);
503 /*SRW.MESSAGE(222
504 ,'est_node_id : ' || EST_NODE_ID)*/NULL;
505 /*SRW.MESSAGE(222
506 ,'c_tot_emps : ' || C_TOT_EMPS)*/NULL;
507 IF L_REPORT_EMPS BETWEEN 1 AND P_MAXIMUM_NO_OF_EMPLOYEES THEN
508 TOT_COUNT_EMPS := NVL(TOT_COUNT_EMPS
509 ,0) + L_REPORT_EMPS;
510 IF P_AUDIT_REPORT = 'Y' THEN
511 FOR per IN C_AUDIT_REPORT LOOP
512 L_BUFFER := PER.PERSON_ID || G_DELIMITER || PER.LAST_NAME || G_DELIMITER || NVL(PER.FIRST_NAME
513 ,' ') || G_DELIMITER || NVL(PER.EMPLOYEE_NUMBER
514 ,' ') || G_DELIMITER || NVL(PER.SEX
518 ,' ') || G_DELIMITER || NVL(TO_CHAR(PER.LOCATION_ID)
515 ,' ') || G_DELIMITER || PER_US_HR_UTILITY_PKG.DERIVE_ALIEN_ETHNIC_ORIGIN(PER.PERSON_ID)
516 || G_DELIMITER || PER.ASSIGNMENT_ID || G_DELIMITER || NVL(TO_CHAR(PER.JOB_ID)
517 ,' ') || G_DELIMITER || NVL(PER.JOB_NAME
519 ,' ') || G_DELIMITER || NVL(PER.LOCATION_CODE
520 ,' ') || G_DELIMITER || G_EOL;
521 --PUT(L_BUFFER);
522 FND_FILE.PUT_LINE(FND_FILE.LOG,L_BUFFER);
523 END LOOP;
524 END IF;
525 END IF;
526 RETURN NULL;
527 END C_TOT_ACTFORMULA;
528
529 FUNCTION BEFOREPFORM RETURN BOOLEAN IS
530 BEGIN
531 RETURN (TRUE);
532 END BEFOREPFORM;
533
534 FUNCTION C_REPORT_EMPS_P RETURN NUMBER IS
535 BEGIN
536 RETURN C_REPORT_EMPS;
537 END C_REPORT_EMPS_P;
538
539 FUNCTION C_MAIN_ACTIVITY_P RETURN VARCHAR2 IS
540 BEGIN
541 RETURN C_MAIN_ACTIVITY;
542 END C_MAIN_ACTIVITY_P;
543
544 FUNCTION C_TOT_EMPS_P RETURN NUMBER IS
545 BEGIN
546 RETURN C_TOT_EMPS;
547 END C_TOT_EMPS_P;
548
549 FUNCTION C_END_OF_TIME_P RETURN DATE IS
550 BEGIN
551 RETURN C_END_OF_TIME;
552 END C_END_OF_TIME_P;
553
554 FUNCTION TOT_COUNT_EMPS_P RETURN NUMBER IS
555 BEGIN
556 RETURN TOT_COUNT_EMPS;
557 END TOT_COUNT_EMPS_P;
558
559 FUNCTION C_BUSINESS_GROUP_NAME_P RETURN VARCHAR2 IS
560 BEGIN
561 RETURN C_BUSINESS_GROUP_NAME;
562 END C_BUSINESS_GROUP_NAME_P;
563
564 FUNCTION C_HIERARCHY_VERSION_NUM_P RETURN NUMBER IS
565 BEGIN
566 RETURN C_HIERARCHY_VERSION_NUM;
567 END C_HIERARCHY_VERSION_NUM_P;
568
569 FUNCTION C_PARENT_NODE_ID_P RETURN NUMBER IS
570 BEGIN
571 RETURN C_PARENT_NODE_ID;
572 END C_PARENT_NODE_ID_P;
573
574 FUNCTION C_PARENT_ORG_ID_P RETURN NUMBER IS
575 BEGIN
576 RETURN C_PARENT_ORG_ID;
577 END C_PARENT_ORG_ID_P;
578
579 FUNCTION DEFAULT_ACTIVITIES_LINE1_P RETURN VARCHAR2 IS
580 BEGIN
581 RETURN DEFAULT_ACTIVITIES_LINE1;
582 END DEFAULT_ACTIVITIES_LINE1_P;
583
584 FUNCTION DEFAULT_ACTIVITIES_LINE2_P RETURN VARCHAR2 IS
585 BEGIN
586 RETURN DEFAULT_ACTIVITIES_LINE2;
587 END DEFAULT_ACTIVITIES_LINE2_P;
588
589 FUNCTION DEFAULT_ACTIVITIES_LINE3_P RETURN VARCHAR2 IS
590 BEGIN
591 RETURN DEFAULT_ACTIVITIES_LINE3;
592 END DEFAULT_ACTIVITIES_LINE3_P;
593
594 FUNCTION DEFAULT_ACTIVITIES_LINE4_P RETURN VARCHAR2 IS
595 BEGIN
596 RETURN DEFAULT_ACTIVITIES_LINE4;
597 END DEFAULT_ACTIVITIES_LINE4_P;
598
599 FUNCTION C_HIERARCHY_NAME_P RETURN VARCHAR2 IS
600 BEGIN
601 RETURN C_HIERARCHY_NAME;
602 END C_HIERARCHY_NAME_P;
603
604 FUNCTION C_NO_OF_ESTABLISHMENTS_P RETURN NUMBER IS
605 BEGIN
606 RETURN C_NO_OF_ESTABLISHMENTS;
607 END C_NO_OF_ESTABLISHMENTS_P;
608
609 FUNCTION C_REPORT_YEAR_P RETURN VARCHAR2 IS
610 BEGIN
611 RETURN C_REPORT_YEAR;
612 END C_REPORT_YEAR_P;
613
614 FUNCTION C_REPORT_DATE_P RETURN DATE IS
615 BEGIN
616 RETURN C_REPORT_DATE;
617 END C_REPORT_DATE_P;
618
619 FUNCTION C_MAXIMUM_NO_OF_EMPLOYEES_P RETURN NUMBER IS
620 BEGIN
621 RETURN C_MAXIMUM_NO_OF_EMPLOYEES;
622 END C_MAXIMUM_NO_OF_EMPLOYEES_P;
623
624 FUNCTION C_PAYROLL_PERIOD_DATE_START_P RETURN DATE IS
625 BEGIN
626 RETURN C_PAYROLL_PERIOD_DATE_START;
627 END C_PAYROLL_PERIOD_DATE_START_P;
628
629 FUNCTION C_PAYROLL_PERIOD_DATE_END_P RETURN DATE IS
630 BEGIN
631 RETURN C_PAYROLL_PERIOD_DATE_END;
632 END C_PAYROLL_PERIOD_DATE_END_P;
633
634 /*PROCEDURE OPEN IS
635 L_APPLCSF VARCHAR2(2000);
636 L_PER_TOP VARCHAR2(2000);
637 L_APPLOUT VARCHAR2(2000);
638 L_PATH VARCHAR2(2000);
639 L_FILE_NAME VARCHAR2(100);
640 L_NAME VARCHAR2(60) := 'file_io.open';
641 CURSOR C_CONCURRENT_ID IS
642 SELECT
643 MAX(FCR.REQUEST_ID)
644 FROM
645 FND_CONCURRENT_REQUESTS FCR,
646 FND_CONCURRENT_PROGRAMS FCP
647 WHERE FCP.APPLICATION_ID = 800
648 AND FCP.CONCURRENT_PROGRAM_NAME = 'PERRPE1L'
649 AND FCR.CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
650 AND FCR.PROGRAM_APPLICATION_ID = FCP.APPLICATION_ID;
651 L_REQUEST_ID NUMBER;
652 BEGIN
653 TOOL_ENV.GETVAR('APPLCSF'
654 ,L_APPLCSF);
655 TOOL_ENV.GETVAR('PER_TOP'
656 ,L_PER_TOP);
657 L_PATH := NVL(L_APPLCSF
658 ,L_PER_TOP);
659 OPEN C_CONCURRENT_ID;
660 FETCH C_CONCURRENT_ID
661 INTO L_REQUEST_ID;
662 CLOSE C_CONCURRENT_ID;
663 G_FILE_NAME := 'o' || TO_CHAR(L_REQUEST_ID);
664 L_FILE_NAME := G_FILE_NAME || '.a03';
665 IF L_PATH IS NOT NULL THEN
666 TOOL_ENV.GETVAR('APPLOUT'
667 ,L_APPLOUT);
668 L_PATH := L_PATH || '/' || L_APPLOUT || '/' || L_FILE_NAME;
669 ELSE
670 L_PATH := L_FILE_NAME;
671 END IF;
672 G_FILE_TYPE := TEXT_IO.FOPEN(L_PATH
673 ,'W');
674 HR_UTILITY.TRACE('file_io.open :' || L_PATH);
675 EXCEPTION
676 WHEN OTHERS THEN
677 HR_UTILITY.SET_LOCATION('Leaving....' || L_NAME
678 ,999);
679 NULL;
680 FND_MESSAGE.RAISE_ERROR;
681 END OPEN;*/
682
683 /*PROCEDURE PUT(P_CHAR IN VARCHAR2) IS
684 L_NAME VARCHAR2(60) := 'file_io.put';
685 BEGIN
686 TEXT_IO.PUT(G_FILE_TYPE
687 ,P_CHAR);
688 HR_UTILITY.TRACE('file_io .put :' || P_CHAR);
689 EXCEPTION
690 WHEN OTHERS THEN
691 HR_UTILITY.SET_LOCATION('Leaving....' || L_NAME
692 ,999);
693 NULL;
694 FND_MESSAGE.RAISE_ERROR;
695 END PUT;*/
696
697 /*PROCEDURE CLOSE IS
698 L_NAME VARCHAR2(60) := 'file_io.close';
699 BEGIN
700 TEXT_IO.FCLOSE(G_FILE_TYPE);
701 HR_UTILITY.TRACE('file_io.close state mag file closed ');
702 EXCEPTION
703 WHEN OTHERS THEN
704 HR_UTILITY.SET_LOCATION('Leaving....' || L_NAME
705 ,999);
706 NULL;
707 FND_MESSAGE.RAISE_ERROR;
708 END CLOSE;*/
709
710 END PER_PERUSE1L_XMLP_PKG;