eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PER_PERUSHIR_XMLP_PKG

Source


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