[Home] [Help]
PACKAGE BODY: APPS.PER_PERUSHIR_XMLP_PKG
Source
1 PACKAGE BODY PER_PERUSHIR_XMLP_PKG AS
2 /* $Header: PERUSHIRB.pls 120.3.12020000.5 2013/02/20 06:55:07 agarai ship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 L_COUNT NUMBER :=0; -- #13411503
5 BEGIN
6 -- HR_STANDARD.EVENT('BEFORE REPORT');
7 C_END_OF_TIME := END_OF_TIME;
8 C_END_OF_TIME_V := to_char(C_END_OF_TIME,'DD-MON-YYYY'); --bug 16304382
9 C_BUSINESS_GROUP_NAME := GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
10 IF P_MEDICAL_AVAIL = 'Y' THEN
11 C_MEDICAL_AVAIL := 'Yes';
12 ELSIF P_MEDICAL_AVAIL = 'N' THEN
13 C_MEDICAL_AVAIL := 'No';
14 -- Start of bug 14532612
15 ELSIF P_MEDICAL_AVAIL = 'E' THEN
16 C_MEDICAL_AVAIL := 'Employee';
17 -- End of bug 14532612
18 ELSE
19 C_MEDICAL_AVAIL := NULL;
20 END IF;
21 IF P_TAX_UNIT_ID IS NOT NULL THEN
22 C_TAX_UNIT := GET_ORG_NAME(P_TAX_UNIT_ID
23 ,P_BUSINESS_GROUP_ID);
24 END IF;
25 IF P_STATE_CODE IS NOT NULL THEN
26 C_STATE_NAME := GET_STATE_NAME(P_STATE_CODE);
27 END IF;
28
29 --P_REPORT_DATE_T := to_char(to_date(P_REPORT_DATE,'YYYY/MM/DD HH24:MI:SS'),'DD-MON-YYYY');
30 P_REPORT_DATE_T := to_char(to_date(P_REPORT_DATE,'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD');
31 --
32 -- 9912897 Default value for the report more is set to 'D' when the value is null
33 --
34 IF P_REPORT_MODE Is null THEN
35 --P_REPORT_MODE:='F';
36 P_REPORT_MODE:='D';
37 End IF;
38
39 --
40 --#10014495 Added state 'NY' for making flag1 as 'Y' for New York state
41 --
42 /** COMMENTED FOR BUG 13411503
43 IF P_STATE_CODE IN ('HI','IA','KY','MT','NM','OR','RI','TX','MD','NY') THEN
44 Flag1 := 1;
45 ELSE
46 Flag1 := 0;
47 END IF;
48 ** COMMENT FOR 13411503 ENDS **/
49
50 --ADDED FOR BUG 13411503
51 L_COUNT :=0;
52
53 SELECT
54 COUNT(LOOKUP_CODE) INTO L_COUNT
55 FROM
56 HR_LOOKUPS
57 WHERE LOOKUP_TYPE='PER_NEW_HIRE_DEP_STATE'
58 AND LOOKUP_CODE=NVL(P_STATE_CODE,'XX');
59
60 IF L_COUNT <> 0 THEN
61 FLAG1 := 1;
62 ELSE
63 FLAG1 := 0;
64 END IF;
65
66 IF P_STATE_CODE IN ('TX','OR','MD') THEN
67 Flag2 := 1;
68 ELSE
69 Flag2 := 0;
70 END IF;
71
72
73
74 RETURN (TRUE);
75
76 EXCEPTION
77 WHEN NO_DATA_FOUND THEN
78 RETURN NULL;
79 WHEN OTHERS THEN
80 RETURN NULL;
81
82 END BEFOREREPORT;
83
84 FUNCTION C_EMPLOYEE_ADDRESSFORMULA(PERSON_ID IN NUMBER) RETURN VARCHAR2 IS
85 BEGIN
86 DECLARE
87 L_EMPLOYEE_ADDRESS VARCHAR2(2000) := NULL;
88 L_PERSON_ID NUMBER(15);
89 BEGIN
90 L_PERSON_ID := PERSON_ID;
91 GET_EMPLOYEE_ADDRESS(L_PERSON_ID
92 ,L_EMPLOYEE_ADDRESS);
93 RETURN (L_EMPLOYEE_ADDRESS);
94 EXCEPTION
95 WHEN OTHERS THEN
96 NULL;
97 END;
98 RETURN NULL;
99 END C_EMPLOYEE_ADDRESSFORMULA;
100
101 FUNCTION C_CONTACT_NAMEFORMULA(NEW_HIRE_CONTACT_ID IN VARCHAR2) RETURN VARCHAR2 IS
102 BEGIN
103 DECLARE
104 L_PERSON_ID NUMBER(15);
105 L_BUSINESS_GROUP_ID NUMBER(15);
106 L_REPORT_DATE DATE;
107 L_CONTACT_NAME VARCHAR2(240);
108 L_CONTACT_TITLE VARCHAR2(160);
109 L_CONTACT_PHONE VARCHAR2(60);
110 BEGIN
111 L_PERSON_ID := NEW_HIRE_CONTACT_ID;
112 L_REPORT_DATE := FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE);
113 L_BUSINESS_GROUP_ID := P_BUSINESS_GROUP_ID;
114 PER_NEW_HIRE_PKG.GET_NEW_HIRE_CONTACT(L_PERSON_ID
115 ,L_BUSINESS_GROUP_ID
116 ,L_REPORT_DATE
117 ,L_CONTACT_NAME
118 ,L_CONTACT_TITLE
119 ,L_CONTACT_PHONE);
120 SET_LOCATION('Entered c_person_dets'
121 ,5);
122 TRACE('Contact name => ' || L_CONTACT_NAME);
123 SET_LOCATION('Leaving c_contact_name'
127 WHEN NO_DATA_FOUND THEN
124 ,10);
125 RETURN (L_CONTACT_NAME);
126 EXCEPTION
128 TRACE('Error is found in c_contact_name');
129 END;
130 RETURN NULL;
131 END C_CONTACT_NAMEFORMULA;
132
133 FUNCTION C_SALARYFORMULA(ASSIGNMENT_ID IN NUMBER) RETURN NUMBER IS
134 BEGIN
135 DECLARE
136 L_BUSINESS_GROUP_ID NUMBER(15);
137 L_REPORT_DATE DATE;
138 L_SALARY NUMBER;
139 BEGIN
140 SET_LOCATION('Entered c_salary formula'
141 ,5);
142 IF P_STATE_CODE = 'TX' OR P_STATE_CODE = 'OR' OR P_STATE_CODE = 'MD' THEN
143 L_BUSINESS_GROUP_ID := P_BUSINESS_GROUP_ID;
144 L_REPORT_DATE := FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE);
145 L_SALARY := GET_SALARY(L_BUSINESS_GROUP_ID
146 ,ASSIGNMENT_ID
147 ,L_REPORT_DATE);
148 SET_LOCATION('Leaving c_salary formula'
149 ,10);
150 IF P_STATE_CODE = 'TX' OR P_STATE_CODE = 'MD' THEN
151 RETURN (L_SALARY);
152 ELSE
153 RETURN (L_SALARY / 12);
154 END IF;
155 ELSE
156 SET_LOCATION('Leaving c_salary formula'
157 ,15);
158 RETURN (NULL);
159 END IF;
160 EXCEPTION
161 WHEN NO_DATA_FOUND THEN
162 NULL;
163 WHEN OTHERS THEN
164 NULL;
165 END;
166 RETURN NULL;
167 END C_SALARYFORMULA;
168
169 FUNCTION AFTERREPORT RETURN BOOLEAN IS
170 BEGIN
171 -- HR_STANDARD.EVENT('AFTER REPORT');
172
173
174 SET_LOCATION('Entered after report trigger'
175 ,5);
176 P_OUTPUT_NEW_HIRE_NULL;
177 SET_LOCATION('Entered after report trigger'
178 ,10);
179
180 IF P_REPORT_MODE = 'F' THEN
181
182 P_UPDATE_STATUS;
183 END IF;
184
185
186 SET_LOCATION('Leaving after report trigger'
187 ,15);
188 RETURN (TRUE);
189 RETURN NULL;
190 EXCEPTION
191 WHEN OTHERS THEN
192 SET_LOCATION('Error found in after report trigger'
193 ,20);
194 TRACE('The error message is ' || SQLERRM);
195 RETURN NULL;
196 END AFTERREPORT;
197
198 FUNCTION G_TAX_UNIT_HEADERGROUPFILTER RETURN BOOLEAN IS
199 BEGIN
200 RETURN (TRUE);
201 END G_TAX_UNIT_HEADERGROUPFILTER;
202
203 FUNCTION G_NEW_HIRESGROUPFILTER RETURN BOOLEAN IS
204 BEGIN
205 RETURN (TRUE);
206 END G_NEW_HIRESGROUPFILTER;
207
208 FUNCTION C_TAX_UNIT_ADDRESSFORMULA(LOCATION_ID IN NUMBER) RETURN VARCHAR2 IS
209 BEGIN
210 DECLARE
211 L_TAX_UNIT_ADDRESS VARCHAR2(2000);
212 L_LOCATION_ID NUMBER(15);
213 BEGIN
214 L_LOCATION_ID := LOCATION_ID;
215 GET_ADDRESS(L_LOCATION_ID
216 ,L_TAX_UNIT_ADDRESS);
217 RETURN (L_TAX_UNIT_ADDRESS);
218 EXCEPTION
219 WHEN OTHERS THEN
220 TRACE('the error is ' || TO_CHAR(SQLCODE) || SQLERRM);
221 END;
222 RETURN NULL;
223 END C_TAX_UNIT_ADDRESSFORMULA;
224
225 PROCEDURE P_UPDATE_STATUS IS
226 BEGIN
227 DECLARE
228 CURSOR C_PERSON_ID IS
229 SELECT
230 PPF.PERSON_ID,
231 PPF.LAST_NAME,
232 PPF.FIRST_NAME
233 FROM
234 PER_ALL_PEOPLE_F PPF,
235 PER_ALL_ASSIGNMENTS_F PAF,
236 HR_SOFT_CODING_KEYFLEX HSCF,
237 HR_LOCATIONS_ALL HL,
238 PER_JOBS JOB,
239 PER_PERIODS_OF_SERVICE PPS
240 WHERE PPS.PERSON_ID = PPF.PERSON_ID
241 AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between PPS.DATE_START
242 AND NVL(PPS.ACTUAL_TERMINATION_DATE
243 ,C_END_OF_TIME)
244 AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between PPF.EFFECTIVE_START_DATE
245 AND PPF.EFFECTIVE_END_DATE
246 AND PPF.PERSON_ID = PAF.PERSON_ID
247 AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between PAF.EFFECTIVE_START_DATE
248 AND PAF.EFFECTIVE_END_DATE
249 AND HSCF.SEGMENT1 = TO_CHAR(P_TAX_UNIT_ID)
250 AND PAF.SOFT_CODING_KEYFLEX_ID = HSCF.SOFT_CODING_KEYFLEX_ID
251 AND PAF.ASSIGNMENT_TYPE = 'E'
252 AND PAF.PRIMARY_FLAG = 'Y'
253 AND PAF.LOCATION_ID = HL.LOCATION_ID
254 AND HL.REGION_2 = NVL(P_STATE_CODE
255 ,HL.REGION_2)
256 AND PAF.JOB_ID = job.job_id (+)
257 AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between NVL(JOB.DATE_FROM
258 ,FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE))
259 AND NVL(JOB.DATE_TO
260 ,C_END_OF_TIME)
261 AND PPF.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
262 AND PPF.PER_INFORMATION_CATEGORY = 'US'
263 AND PPS.DATE_START <= FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE)
264 --AND PPF.PER_INFORMATION7 = 'INCL'
265 AND PPF.PER_INFORMATION7 IN ('INCL','INCLWH') --bug 14532612
266 UNION
267 SELECT
268 PPF.PERSON_ID,
269 PPF.LAST_NAME,
270 PPF.FIRST_NAME
271 FROM
272 PER_ALL_PEOPLE_F PPF,
273 PER_ALL_ASSIGNMENTS_F PAF,
274 HR_SOFT_CODING_KEYFLEX HSCF,
275 HR_LOCATIONS_ALL HL,
276 PER_JOBS JOB,
277 PER_PERIODS_OF_SERVICE PPS
278 WHERE PPS.PERSON_ID = PPF.PERSON_ID
282 AND PPF.PERSON_ID = PAF.PERSON_ID
279 AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between PPF.EFFECTIVE_START_DATE
280 AND PPF.EFFECTIVE_END_DATE
281 AND PPS.ACTUAL_TERMINATION_DATE IS NOT NULL
283 AND not exists (
284 SELECT
285 1
286 FROM
287 PER_PERIODS_OF_SERVICE PPS2
288 WHERE PPF.PERSON_ID = PPS2.PERSON_ID
289 AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between PPS2.DATE_START
290 AND NVL(PPS2.ACTUAL_TERMINATION_DATE
291 ,C_END_OF_TIME) )
292 AND PPS.DATE_START = PAF.EFFECTIVE_START_DATE
293 AND HSCF.SEGMENT1 = TO_CHAR(P_TAX_UNIT_ID)
294 AND PAF.SOFT_CODING_KEYFLEX_ID = HSCF.SOFT_CODING_KEYFLEX_ID
295 AND PAF.ASSIGNMENT_TYPE = 'E'
296 AND PAF.PRIMARY_FLAG = 'Y'
297 AND PAF.LOCATION_ID = HL.LOCATION_ID
298 AND HL.REGION_2 = NVL(P_STATE_CODE
299 ,HL.REGION_2)
300 AND PAF.JOB_ID = job.job_id (+)
301 AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between NVL(JOB.DATE_FROM
302 ,FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE))
303 AND NVL(JOB.DATE_TO
304 ,C_END_OF_TIME)
305 AND PPF.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
306 AND PPF.PER_INFORMATION_CATEGORY = 'US'
307 AND PPS.DATE_START <= FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE)
308 --AND PPF.PER_INFORMATION7 = 'INCL'
309 AND PPF.PER_INFORMATION7 IN ('INCL','INCLWH') --bug 14532612
310 ORDER BY
311 2,
312 3;
313 V_PERSON_ID PER_PEOPLE_F.PERSON_ID%TYPE;
314 V_LAST_NAME PER_PEOPLE_F.LAST_NAME%TYPE;
315 V_FIRST_NAME PER_PEOPLE_F.FIRST_NAME%TYPE;
316 BEGIN
317
318 SET_LOCATION('Entered p_update_status'
319 ,5);
320 IF C_PERSON_ID%ISOPEN THEN
321 CLOSE C_PERSON_ID;
322 END IF;
323 OPEN C_PERSON_ID;
324 LOOP
325 FETCH C_PERSON_ID
326 INTO
327 V_PERSON_ID
328 ,V_LAST_NAME
329 ,V_FIRST_NAME;
330 UPDATE
331 PER_PEOPLE_F
332 SET
333 PER_INFORMATION7 = 'DONE'
334 WHERE PERSON_ID = V_PERSON_ID
335 AND PER_INFORMATION7 IN ('INCL','INCLWH'); --bug 14532612
336 EXIT WHEN C_PERSON_ID%NOTFOUND;
337 END LOOP;
338 SET_LOCATION('p_update_status'
339 ,10);
340 CLOSE C_PERSON_ID;
341 COMMIT;
342 SET_LOCATION('Leaving p_update_status'
343 ,15);
344
345
346
347 EXCEPTION
348 WHEN OTHERS THEN
349 ROLLBACK;
350 END;
351 END P_UPDATE_STATUS;
352
353 FUNCTION C_CONTACT_TITLEFORMULA(NEW_HIRE_CONTACT_ID IN VARCHAR2) RETURN VARCHAR2 IS
354 BEGIN
355 DECLARE
356 L_PERSON_ID NUMBER(15);
357 L_BUSINESS_GROUP_ID NUMBER(15);
358 L_REPORT_DATE DATE;
359 L_CONTACT_NAME VARCHAR2(240);
360 L_CONTACT_TITLE VARCHAR2(160);
361 L_CONTACT_PHONE VARCHAR2(60);
362 BEGIN
363 L_PERSON_ID := NEW_HIRE_CONTACT_ID;
364 L_REPORT_DATE := FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE);
365 L_BUSINESS_GROUP_ID := P_BUSINESS_GROUP_ID;
366 PER_NEW_HIRE_PKG.GET_NEW_HIRE_CONTACT(L_PERSON_ID
367 ,L_BUSINESS_GROUP_ID
368 ,L_REPORT_DATE
369 ,L_CONTACT_NAME
370 ,L_CONTACT_TITLE
371 ,L_CONTACT_PHONE);
372 SET_LOCATION('Entered c_contact_title'
373 ,5);
374 TRACE('Contact title => ' || L_CONTACT_TITLE);
375 SET_LOCATION('Leaving c_contact_title'
376 ,10);
377 RETURN (L_CONTACT_TITLE);
378 EXCEPTION
379 WHEN NO_DATA_FOUND THEN
380 TRACE('no data found error in c_contact_title');
381 END;
382 RETURN NULL;
383 END C_CONTACT_TITLEFORMULA;
384
385 FUNCTION C_CONTACT_PHONEFORMULA(NEW_HIRE_CONTACT_ID IN VARCHAR2) RETURN VARCHAR2 IS
386 BEGIN
387 DECLARE
388 L_PERSON_ID NUMBER(15);
389 L_BUSINESS_GROUP_ID NUMBER(15);
390 L_REPORT_DATE DATE;
391 L_CONTACT_NAME VARCHAR2(240);
392 L_CONTACT_TITLE VARCHAR2(160);
393 L_CONTACT_PHONE VARCHAR2(60);
394 BEGIN
395 L_PERSON_ID := NEW_HIRE_CONTACT_ID;
396 L_REPORT_DATE := FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE);
397 L_BUSINESS_GROUP_ID := P_BUSINESS_GROUP_ID;
398 PER_NEW_HIRE_PKG.GET_NEW_HIRE_CONTACT(L_PERSON_ID
399 ,L_BUSINESS_GROUP_ID
400 ,L_REPORT_DATE
401 ,L_CONTACT_NAME
402 ,L_CONTACT_TITLE
403 ,L_CONTACT_PHONE);
404 SET_LOCATION('Entered c_contact_phone'
405 ,5);
406 TRACE('Contact phone => ' || L_CONTACT_PHONE);
407 SET_LOCATION('Leaving c_contact_phone'
408 ,10);
409 RETURN (L_CONTACT_PHONE);
410 EXCEPTION
411 WHEN NO_DATA_FOUND THEN
412 TRACE('no data found error in c_contact_phone');
413 END;
414 RETURN NULL;
415 END C_CONTACT_PHONEFORMULA;
416
417 FUNCTION BETWEENPAGE RETURN BOOLEAN IS
418 BEGIN
419 SET_LOCATION('Entered between page trigger'
420 ,5);
421 RETURN (TRUE);
422 END BETWEENPAGE;
423
424 PROCEDURE P_OUTPUT_NEW_HIRE_NULL IS
425 BEGIN
426 DECLARE
427 CURSOR C_PERSON_ID IS
428 SELECT
429 PPF.PERSON_ID,
430 PPF.LAST_NAME,
431 PPF.FIRST_NAME,
432 SUBSTR(PPF.MIDDLE_NAMES
433 ,1
434 ,1) MIDDLE_NAME,
435 PPF.NATIONAL_IDENTIFIER,
436 PPF.DATE_OF_BIRTH,
437 PPS.DATE_START
438 FROM
439 PER_ALL_PEOPLE_F PPF,
440 PER_ALL_ASSIGNMENTS_F PAF,
441 HR_SOFT_CODING_KEYFLEX HSCF,
442 HR_LOCATIONS_ALL HL,
443 PER_JOBS JOB,
444 PER_PERIODS_OF_SERVICE PPS
445 WHERE PPS.PERSON_ID = PPF.PERSON_ID
446 AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between PPS.DATE_START
447 AND NVL(PPS.ACTUAL_TERMINATION_DATE
448 ,C_END_OF_TIME)
449 AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between PPF.EFFECTIVE_START_DATE
450 AND PPF.EFFECTIVE_END_DATE
451 AND PPF.PERSON_ID = PAF.PERSON_ID
452 AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between PAF.EFFECTIVE_START_DATE
453 AND PAF.EFFECTIVE_END_DATE
454 AND HSCF.SEGMENT1 = TO_CHAR(P_TAX_UNIT_ID)
455 AND PAF.SOFT_CODING_KEYFLEX_ID = HSCF.SOFT_CODING_KEYFLEX_ID
456 AND PAF.ASSIGNMENT_TYPE = 'E'
457 AND PAF.PRIMARY_FLAG = 'Y'
458 AND PAF.LOCATION_ID = HL.LOCATION_ID
459 AND HL.REGION_2 = NVL(P_STATE_CODE
460 ,HL.REGION_2)
461 AND PAF.JOB_ID = job.job_id (+)
462 AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between NVL(JOB.DATE_FROM
463 ,FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE))
464 AND NVL(JOB.DATE_TO
465 ,C_END_OF_TIME)
466 AND PPF.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
467 AND PPF.PER_INFORMATION_CATEGORY = 'US'
468 AND PPS.DATE_START <= FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE)
469 AND PPF.PER_INFORMATION7 is NULL
470 UNION
471 SELECT
472 PPF.PERSON_ID,
473 PPF.LAST_NAME,
474 PPF.FIRST_NAME,
475 SUBSTR(PPF.MIDDLE_NAMES
476 ,1
477 ,1) MIDDLE_NAME,
478 PPF.NATIONAL_IDENTIFIER,
479 PPF.DATE_OF_BIRTH,
480 PPS.DATE_START
481 FROM
482 PER_ALL_PEOPLE_F PPF,
483 PER_ALL_ASSIGNMENTS_F PAF,
484 HR_SOFT_CODING_KEYFLEX HSCF,
485 HR_LOCATIONS_ALL HL,
486 PER_JOBS JOB,
487 PER_PERIODS_OF_SERVICE PPS
488 WHERE PPS.PERSON_ID = PPF.PERSON_ID
489 AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between PPF.EFFECTIVE_START_DATE
490 AND PPF.EFFECTIVE_END_DATE
491 AND PPS.ACTUAL_TERMINATION_DATE IS NOT NULL
492 AND PPF.PERSON_ID = PAF.PERSON_ID
493 AND not exists (
494 SELECT
495 1
496 FROM
497 PER_PERIODS_OF_SERVICE PPS2
498 WHERE PPF.PERSON_ID = PPS2.PERSON_ID
499 AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between PPS2.DATE_START
500 AND NVL(PPS2.ACTUAL_TERMINATION_DATE
501 ,C_END_OF_TIME) )
502 AND PPS.DATE_START = PAF.EFFECTIVE_START_DATE
503 AND HSCF.SEGMENT1 = TO_CHAR(P_TAX_UNIT_ID)
504 AND PAF.SOFT_CODING_KEYFLEX_ID = HSCF.SOFT_CODING_KEYFLEX_ID
505 AND PAF.ASSIGNMENT_TYPE = 'E'
506 AND PAF.PRIMARY_FLAG = 'Y'
507 AND PAF.LOCATION_ID = HL.LOCATION_ID
508 AND HL.REGION_2 = NVL(P_STATE_CODE
509 ,HL.REGION_2)
510 AND PAF.JOB_ID = job.job_id (+)
511 AND FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE) between NVL(JOB.DATE_FROM
512 ,FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE))
513 AND NVL(JOB.DATE_TO
514 ,C_END_OF_TIME)
515 AND PPF.BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID
516 AND PPF.PER_INFORMATION_CATEGORY = 'US'
517 AND PPS.DATE_START <= FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE)
518 AND PPF.PER_INFORMATION7 is NULL
519 ORDER BY
520 2,
521 3;
522 V_PERSON_ID PER_ALL_PEOPLE_F.PERSON_ID%TYPE;
523 V_LAST_NAME PER_ALL_PEOPLE_F.LAST_NAME%TYPE;
524 V_FIRST_NAME PER_ALL_PEOPLE_F.FIRST_NAME%TYPE;
525 V_MIDDLE_NAME PER_ALL_PEOPLE_F.MIDDLE_NAMES%TYPE;
526 V_SSN PER_ALL_PEOPLE_F.NATIONAL_IDENTIFIER%TYPE;
527 V_DOB PER_ALL_PEOPLE_F.DATE_OF_BIRTH%TYPE;
528 V_DATE_START PER_PERIODS_OF_SERVICE.DATE_START%TYPE;
529 V_HEADER NUMBER;
530 V_BUFFER VARCHAR2(120);
531 V_BOOLEAN BOOLEAN;
532 BEGIN
533 SET_LOCATION('Entered p_output_new_hire_null'
534 ,10);
535 V_HEADER := 0;
536 IF C_PERSON_ID%ISOPEN THEN
537 CLOSE C_PERSON_ID;
538 END IF;
539 OPEN C_PERSON_ID;
540 FETCH C_PERSON_ID
541 INTO
542 V_PERSON_ID
543 ,V_LAST_NAME
547 ,V_DOB
544 ,V_FIRST_NAME
545 ,V_MIDDLE_NAME
546 ,V_SSN
548 ,V_DATE_START;
549 WHILE C_PERSON_ID%FOUND LOOP
550
551 IF V_HEADER = 0 THEN
552 V_BOOLEAN := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING'
553 ,'');
554 FND_FILE.PUT_LINE(1
555 ,'Warning : The New Hire field of the following employees on people form is blank.');
556 FND_FILE.PUT_LINE(1
557 ,'Warning : Please update the New Hire field.');
558 FND_FILE.PUT_LINE(1
559 ,' ');
560 FND_FILE.PUT_LINE(1
561 ,'Last Name First Name MI SSN Hire Date DOB ');
562 FND_FILE.PUT_LINE(1
563 ,'------------------------ -------------------- -- ----------- --------- ---------');
564 V_BUFFER := RPAD(V_LAST_NAME
565 ,24
566 ,' ') || RPAD(' '
567 ,1
568 ,' ') || RPAD(NVL(V_FIRST_NAME
569 ,' ')
570 ,20
571 ,' ') || RPAD(' '
572 ,1
573 ,' ') || RPAD(NVL(V_MIDDLE_NAME
574 ,' ')
575 ,2
576 ,' ') || RPAD(' '
577 ,1
578 ,' ') || RPAD(NVL(V_SSN
579 ,' ')
580 ,11
581 ,' ') || RPAD(' '
582 ,1
583 ,' ') || RPAD(TO_DATE(V_DATE_START
584 ,'DD-MON-RRRR')
585 ,9
586 ,' ') || RPAD(' '
587 ,1
588 ,' ') || RPAD(TO_DATE(V_DOB
589 ,'DD-MON-RRRR')
590 ,9
591 ,' ');
592 FND_FILE.PUT_LINE(1
593 ,V_BUFFER);
594 V_HEADER := 1;
595 ELSE
596 V_BUFFER := RPAD(V_LAST_NAME
597 ,24
598 ,' ') || RPAD(' '
599 ,1
600 ,' ') || RPAD(NVL(V_FIRST_NAME
601 ,' ')
602 ,20
603 ,' ') || RPAD(' '
604 ,1
605 ,' ') || RPAD(NVL(V_MIDDLE_NAME
606 ,' ')
607 ,2
608 ,' ') || RPAD(' '
609 ,1
610 ,' ') || RPAD(NVL(V_SSN
611 ,' ')
612 ,11
613 ,' ') || RPAD(' '
614 ,1
615 ,' ') || RPAD(TO_DATE(V_DATE_START
616 ,'DD-MON-RRRR')
617 ,9
618 ,' ') || RPAD(' '
619 ,1
620 ,' ') || RPAD(TO_DATE(V_DOB
621 ,'DD-MON-RRRR')
622 ,9
623 ,' ');
624 FND_FILE.PUT_LINE(1
625 ,V_BUFFER);
626 END IF;
627 FETCH C_PERSON_ID
628 INTO
629 V_PERSON_ID
630 ,V_LAST_NAME
631 ,V_FIRST_NAME
632 ,V_MIDDLE_NAME
633 ,V_SSN
634 ,V_DOB
635 ,V_DATE_START;
636 END LOOP;
637 FND_FILE.PUT_LINE(1
638 ,' ');
639 SET_LOCATION('p_output_new_hire_null'
640 ,100);
641 CLOSE C_PERSON_ID;
642 EXCEPTION
643 WHEN OTHERS THEN
644 ROLLBACK;
645 END;
646 END P_OUTPUT_NEW_HIRE_NULL;
647
648 FUNCTION AFTERPFORM RETURN BOOLEAN IS
649 BEGIN
650 RETURN (TRUE);
651 END AFTERPFORM;
652
653 FUNCTION C_BUSINESS_GROUP_NAME_P RETURN VARCHAR2 IS
654 BEGIN
655 RETURN C_BUSINESS_GROUP_NAME;
656 END C_BUSINESS_GROUP_NAME_P;
657
658 FUNCTION C_REPORT_SUBTITLE_P RETURN VARCHAR2 IS
659 BEGIN
660 RETURN C_REPORT_SUBTITLE;
661 END C_REPORT_SUBTITLE_P;
662
663 FUNCTION C_TAX_UNIT_P RETURN VARCHAR2 IS
664 BEGIN
665 RETURN C_TAX_UNIT;
666 END C_TAX_UNIT_P;
667
668 FUNCTION C_STATE_NAME_P RETURN VARCHAR2 IS
669 BEGIN
670 RETURN C_STATE_NAME;
671 END C_STATE_NAME_P;
672
673 FUNCTION C_MEDICAL_AVAIL_P RETURN VARCHAR2 IS
674 BEGIN
675 RETURN C_MEDICAL_AVAIL;
676 END C_MEDICAL_AVAIL_P;
677
678 FUNCTION C_END_OF_TIME_P RETURN VARCHAR2 IS
679 BEGIN
680 RETURN C_END_OF_TIME_V; --bug 16304382
681 END C_END_OF_TIME_P;
682 FUNCTION GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
683 X0 VARCHAR2(2000);
684 BEGIN
685 X0 := HR_REPORTS.GET_BUSINESS_GROUP(P_BUSINESS_GROUP_ID);
686 RETURN X0;
687 END GET_BUSINESS_GROUP;
688
689
690
691 FUNCTION END_OF_TIME RETURN DATE IS
692 X0 DATE;
693 BEGIN
694 X0 := HR_GENERAL.END_OF_TIME;
695 RETURN X0;
696 END END_OF_TIME;
697
698 FUNCTION GET_ORG_NAME(P_ORGANIZATION_ID IN NUMBER
699 ,P_BUSINESS_GROUP_ID IN NUMBER) RETURN VARCHAR2 IS
700 X0 VARCHAR2(2000);
701 BEGIN
702 X0 := HR_US_REPORTS.GET_ORG_NAME(P_ORGANIZATION_ID,P_BUSINESS_GROUP_ID);
703 RETURN X0;
704 END GET_ORG_NAME;
705
706 FUNCTION GET_STATE_NAME(P_STATE_CODE IN VARCHAR2) RETURN VARCHAR2 IS
707 X0 VARCHAR2(2000);
708 BEGIN
709 X0 := HR_US_REPORTS.GET_STATE_NAME(P_STATE_CODE);
710 RETURN X0;
711 END GET_STATE_NAME;
712
713 PROCEDURE GET_EMPLOYEE_ADDRESS(P_PERSON_ID IN NUMBER
714 ,P_ADDRESS OUT NOCOPY VARCHAR2) IS
715 BEGIN
716 HR_US_REPORTS.GET_EMPLOYEE_ADDRESS(P_PERSON_ID, P_ADDRESS);
717
718 END GET_EMPLOYEE_ADDRESS;
719
720 PROCEDURE SET_LOCATION(PROCEDURE_NAME IN VARCHAR2
721 ,STAGE IN NUMBER) IS
722 BEGIN
723 HR_UTILITY.SET_LOCATION(PROCEDURE_NAME, STAGE);
724 END SET_LOCATION;
725
726 PROCEDURE TRACE(TRACE_DATA IN VARCHAR2) IS
727 BEGIN
728 HR_UTILITY.TRACE(TRACE_DATA);
729 END TRACE;
730
731 FUNCTION GET_SALARY(P_PAY_BASIS_ID IN NUMBER
732 ,P_ASSIGNMENT_ID IN NUMBER) RETURN VARCHAR2 IS
733 X0 VARCHAR2(2000);
734 BEGIN
735 X0 := HR_GENERAL.GET_SALARY(P_PAY_BASIS_ID, P_ASSIGNMENT_ID);
736 RETURN X0;
737 END GET_SALARY;
738
739 FUNCTION GET_SALARY(P_BUSINESS_GROUP_ID IN NUMBER
740 ,P_ASSIGNMENT_ID IN NUMBER
741 ,P_REPORT_DATE IN DATE) RETURN NUMBER IS
742 X0 NUMBER;
743 BEGIN
744 X0 := HR_US_REPORTS.GET_SALARY(P_BUSINESS_GROUP_ID, P_ASSIGNMENT_ID, P_REPORT_DATE);
745 RETURN X0;
746 END GET_SALARY;
747
748 PROCEDURE GET_ADDRESS(P_LOCATION_ID IN NUMBER
749 ,P_ADDRESS OUT NOCOPY VARCHAR2) IS
750 BEGIN
751 HR_US_REPORTS.GET_ADDRESS(P_LOCATION_ID, P_ADDRESS);
752 END GET_ADDRESS;
753
754 END PER_PERUSHIR_XMLP_PKG;