[Home] [Help]
PACKAGE BODY: APPS.PER_PERUSHRM_XMLP_PKG
Source
1 PACKAGE BODY PER_PERUSHRM_XMLP_PKG AS
2 /* $Header: PERUSHRMB.pls 120.1.12020000.3 2013/02/11 05:45:43 nkjaladi ship $ */
3
4 function BeforeReport return boolean is
5 l_name varchar2(61) := 'BEFORE_REPORT';
6
7 begin
8 -- hr_standard.event('BEFORE REPORT');
9 hr_utility.set_location('Entering.... :' || l_name,10);
10 p_multi_state_1 := p_multi_state;
11 c_end_of_time := hr_general.end_of_time;
12
13 c_business_group_name := hr_reports.get_business_group(p_business_group_id);
14
15 c_no_of_gre := 0;
16 c_no_of_newhire := 0;
17 c_a03_header_flag := 0;
18 c_fatal_error_flag := null;
19 if p_multi_state_1 is null then
20 p_multi_state_1 := 'N';
21 end if;
22
23 if p_tax_unit_id is not null then
24 c_tax_unit := hr_us_reports.get_org_name
25 (p_tax_unit_id,p_business_group_id);
26 end if;
27
28 if p_state_code is not null then
29 c_state_name := hr_us_reports.get_state_name
30 (p_state_code);
31 end if;
32
33 hr_utility.set_location(l_name,20);
34
35 --file_io.open;
36 hr_utility.set_location(l_name,30);
37 --file_io.open_a01;
38
39
40 hr_utility.set_location(l_name,40);
41 char_set_init('US7ASCII') ;
42
43 if p_audit_report = 'Y' then
44 hr_utility.set_location(l_name,50);
45 --file_io.open_a03;
46 end if;
47
48 hr_utility.set_location('Leaving.....:' || l_name,100);
49 return (TRUE);
50
51 RETURN NULL; exception when no_data_found then null;
52 RETURN NULL; when others then
53 /*srw.message(288, 'Error found in before report trigger');*/null;
54
55
56 RETURN NULL; end;
57
58 function c_employee_addressformula(person_id in number) return varchar2 is
59 begin
60
61 DECLARE l_employee_address VARCHAR2(2000) := NULL;
62 l_person_id NUMBER(15);
63
64 begin
65
66 l_person_id := person_id;
67
71
68 hr_us_reports.get_employee_address
69 (l_person_id
70 ,l_employee_address);
72
73 return(l_employee_address);
74
75 exception
76 when others then
77 /*srw.message('88','Error found in c_employee_address is '||sqlerrm);*/null;
78
79
80 end;
81
82 RETURN NULL; end;
83
84 function c_salaryformula(assignment_id in number) return number is
85 begin
86
87 declare
88
89 l_business_group_id number(15);
90 l_report_date date;
91 l_salary number;
92
93 begin
94
95 hr_utility.set_location('Entered c_salary formula', 5);
96
97 if p_state_code = 'TX' or p_state_code = 'OR'
98 or p_state_code = 'MD' then
99 l_business_group_id := P_BUSINESS_GROUP_ID;
100 l_report_date := fnd_date.canonical_to_date(P_REPORT_DATE);
101
102 l_salary := hr_us_reports.get_salary(l_business_group_id,
103 assignment_id,
104 l_report_date
105 );
106 hr_utility.set_location('Leaving c_salary formula', 10);
107 if p_state_code = 'TX' then
108 return(l_salary);
109 else
110 return(l_salary/12);
111 end if;
112 else
113 hr_utility.set_location('Leaving c_salary formula', 15);
114 return(NULL);
115 end if;
116
117
118 exception when NO_DATA_FOUND then
119 /*srw.message(1,'Error is found in c_salary');*/null;
120
121 when others then
122 /*srw.message(1,'Error is found in c_salary');*/null;
123
124
125 end;
126
127 RETURN NULL; end;
128
129 function AfterReport (CS_NO_OF_NEW_HIRE in number)return boolean is
130 begin
131
132 --hr_standard.event('AFTER REPORT');
133
134 hr_utility.set_location('Entered after report trigger',1);
135
136
137 if c_fatal_error_flag is null and C_NO_OF_NEWHIRE > 0 then
138 total_record() ;
139 hr_utility.set_location('after report trigger',2);
140 end if;
141 hr_utility.set_location('after report trigger',3);
142
143 -- file_io.close;
144
145 if c_fatal_error_flag is null then
146 hr_utility.set_location('after report trigger',4);
147 p_output_new_hire_null() ;
148 hr_utility.set_location('after report trigger',5);
149 p_mag_update_status() ;
150 hr_utility.set_location('after report trigger',6);
151 end if;
152
153
154 if c_fatal_error_flag is null then
155 a01_total_record(CS_NO_OF_NEW_HIRE) ;
156 end if;
157 -- file_io.close_a01;
158
159
160 if p_audit_report = 'Y' then
161 hr_utility.set_location('after report trigger',8);
162 -- file_io.close_a03;
163 end if;
164 hr_utility.set_location('Leaving after report trigger',10);
165 return(TRUE);
166 RETURN NULL; exception when others then
167 hr_utility.set_location('Error found in after report trigger',20);
168 hr_utility.trace('The error message is '||sqlerrm);
169
170 RETURN NULL; end;
171
172 function G_new_hiresGroupFilter return boolean is
173 begin
174 /*srw.message(1,'Tax Unit ID1 => '||to_char(tax_unit_id));*/null;
175
176 /*srw.message(2,'Finish g_new_hires and person_id => '||to_char(person_id));*/null;
177
178 return (TRUE);
179 end;
180
181 function BetweenPage return boolean is
182 begin
183 hr_utility.set_location('Entered between page trigger',5);
184 return (TRUE);
185 end;
186
187 function CF_new_hireFormula (SUI_COMPANY_STATE_ID in varchar2,DATE_START in date,FEDERAL_ID in varchar2,
188 NATIONAL_IDENTIFIER in varchar2,MIDDLE_NAME in varchar2,gre_location_id in number,HIRE_STATE in varchar2,
189 person_id in number,LAST_NAME in varchar2,FIRST_NAME in varchar2,DATE_OF_BIRTH in date,
190 TAX_UNIT_NAME in varchar2,FULL_MIDDLE_NAME in varchar2,SIT_COMPANY_STATE_ID in varchar2,
191 c_contact_name in varchar2,c_contact_phone in varchar2)return Number is
192 l_name varchar2(60) := 'CF_new_hireFormula';
193 begin
194
195 hr_utility.set_location('Entering... :' || l_name,10);
196
197 new_hire_record(person_id ,NATIONAL_IDENTIFIER ,FIRST_NAME,MIDDLE_NAME ,LAST_NAME,DATE_START,
198 FULL_MIDDLE_NAME ,gre_location_id ,DATE_OF_BIRTH ,HIRE_STATE ,FEDERAL_ID,SUI_COMPANY_STATE_ID,
199 TAX_UNIT_NAME ,c_contact_phone ,c_contact_name ,SIT_COMPANY_STATE_ID );
200
204 end;
201 hr_utility.set_location('Leaving... :' || l_name,20);
202
203 return(0);
205
206 PROCEDURE char_set_init
207 (
208 p_character_set in varchar2
209 ) IS
210 BEGIN
211
212 per_new_hire_pkg.char_set_init( p_character_set => p_character_set);
213
214
215 END;
216 procedure new_hire_record(person_id in number,NATIONAL_IDENTIFIER in varchar2,FIRST_NAME in varchar2,
217 MIDDLE_NAME in varchar2,LAST_NAME in varchar2,DATE_START in date,FULL_MIDDLE_NAME in varchar2,
218 gre_location_id in number,DATE_OF_BIRTH in date,HIRE_STATE in varchar2,FEDERAL_ID in number,
219 SUI_COMPANY_STATE_ID in varchar2,TAX_UNIT_NAME in varchar2,c_contact_phone in number,
220 c_contact_name in varchar2,SIT_COMPANY_STATE_ID in varchar2) is
221 l_buffer varchar2(2000);
222 l_address varchar2(1000);
223 l_city varchar2(50);
224 l_state varchar2(50);
225 l_zip varchar2(10);
226 l_zip_extension varchar2(10);
227 l_loc_address_line1 varchar2(240);
228 l_loc_address_line2 varchar2(240);
229 l_loc_address_line3 varchar2(240);
230 l_loc_city varchar2(50);
231 l_loc_state varchar2(10);
232 l_loc_zip varchar2(10);
233 l_loc_zip_extension varchar2(10);
234 l_loc_country varchar2(10);
235 l_emp_address_line1 varchar2(240);
236 l_emp_address_line2 varchar2(240);
237 l_emp_address_line3 varchar2(240);
238 l_emp_city varchar2(50);
239 l_emp_state varchar2(50);
240 l_emp_zip varchar2(10);
241 l_emp_zip_extension varchar2(10);
242 l_emp_country varchar2(10);
243 l_person_id number(15);
244 l_location_id number(15);
245 l_name varchar(60) := 'New_Hire_Record';
246 g_delimiter varchar2(1) := fnd_global.local_chr(10);
247 l_date_start varchar2(10);
248 l_date_of_birth varchar2(10);
249
250
251 BEGIN
252
253 hr_utility.set_location('Entering....' || l_name,10);
254 l_person_id := person_id;
255
256
257 if p_state_code = 'CA' then
258 hr_utility.set_location(l_name,20);
259
260 per_new_hire_pkg.get_employee_address
261 (l_person_id,l_address,l_city,l_state,l_zip,l_zip_extension);
262 l_buffer := per_new_hire_pkg.ca_w4_record(
263 p_record_identifier => 'W4'
264 ,p_national_identifier => NATIONAL_IDENTIFIER
265 ,p_first_name => FIRST_NAME
266 ,p_middle_name => MIDDLE_NAME
267 ,p_last_name => LAST_NAME
268 ,p_street_address => l_address
269 ,p_city => l_city
270 ,p_state => l_state
271 ,p_zip => l_zip
272 ,p_zip_extension => l_zip_extension
273 ,p_date_of_hire => DATE_START
274 );
275 hr_utility.set_location(l_name,30);
276 c_no_of_newhire := c_no_of_newhire + 1;
277 -- file_io.put(l_buffer);
278 if p_audit_report = 'Y' then
279
280
281 l_buffer := per_new_hire_pkg.a03_ca_new_hire_record(
282 p_national_identifier => NATIONAL_IDENTIFIER
283 ,p_first_name => FIRST_NAME
284 ,p_middle_name => FULL_MIDDLE_NAME
285 ,p_last_name => LAST_NAME
286 ,p_emp_address_line => l_address
287 ,p_emp_city => l_city
288 ,p_emp_state => l_state
289 ,p_emp_zip => l_zip
290 ,p_emp_zip_extension => l_zip_extension
291 ,p_date_of_hire => DATE_START
292
293 );
294 hr_utility.set_location(l_name,35);
295 -- file_io.put_a03(l_buffer);
296 end if;
297 elsif p_state_code = 'NY' then
298 hr_utility.set_location(l_name,40);
299
300 per_new_hire_pkg.get_employee_address
301 (l_person_id,l_address,l_city,l_state,l_zip,l_zip_extension);
302 hr_utility.trace('date_start = ' ||to_char(DATE_START));
303
304 l_buffer := per_new_hire_pkg.ny_1h_record(
305 p_record_identifier => '1H'
306 ,p_last_name => LAST_NAME
307 ,p_middle_name => MIDDLE_NAME
308 ,p_first_name => FIRST_NAME
309 ,p_national_identifier => NATIONAL_IDENTIFIER
310 ,p_street_address => l_address
311 ,p_city => l_city
312 ,p_state => l_state
313 ,p_zip => l_zip
314 ,p_date_of_hire => DATE_START
315 ,p_medical_avail => NULL --#10014495
316 ,p_health_ins_date => NULL --#10014495
317 ,p_new_hire_indicator => 'INCL' --#16305543/14532612
318 );
319 c_no_of_newhire := c_no_of_newhire + 1;
320 -- file_io.put(l_buffer);
321 if p_audit_report = 'Y' then
322
323 l_buffer := per_new_hire_pkg.a03_ny_new_hire_record(
324 p_national_identifier => NATIONAL_IDENTIFIER
325 ,p_first_name => FIRST_NAME
326 ,p_middle_name => FULL_MIDDLE_NAME
327 ,p_last_name => LAST_NAME
328 ,p_emp_address_line => l_address
329 ,p_emp_city => l_city
330 ,p_emp_state => l_state
331 ,p_emp_zip => l_zip
332 ,p_date_of_hire => DATE_START
333 ,p_medical_avail => NULL --#10014495
334 ,p_health_ins_date => NULL --#10014495
335 ,p_new_hire_indicator => 'INCL' --#16305543/14532612
336 );
337 --file_io.put_a03(l_buffer);
338 end if;
339
340 hr_utility.set_location(l_name,50);
344 per_new_hire_pkg.get_location_address_3lines
341 elsif p_state_code = 'FL' then
342 hr_utility.trace('p_state_code = FL');
343 l_location_id := gre_location_id;
345 (l_location_id,l_loc_address_line1,l_loc_address_line2,l_loc_address_line3
346 ,l_loc_city,l_loc_state,l_loc_zip,l_loc_zip_extension,l_loc_country);
347 per_new_hire_pkg.get_employee_address_3lines
348 (l_person_id,l_emp_address_line1,l_emp_address_line2,l_emp_address_line3
349 ,l_emp_city,l_emp_state,l_emp_zip,l_emp_zip_extension,l_emp_country);
350 hr_utility.set_location(l_name,51);
351 hr_utility.trace('p_mult_state = ' || p_multi_state_1);
352 hr_utility.trace('l_loc_state = ' || l_loc_state);
353 l_buffer := per_new_hire_pkg.fl_new_hire_record(
354 p_record_identifier => 'FL Newhire Record'
355 ,p_national_identifier => NATIONAL_IDENTIFIER
356 ,p_first_name => FIRST_NAME
357 ,p_middle_name => FULL_MIDDLE_NAME
358 ,p_last_name => LAST_NAME
359 ,p_emp_address_line1 => l_emp_address_line1
360 ,p_emp_address_line2 => l_emp_address_line2
361 ,p_emp_address_line3 => l_emp_address_line3
362 ,p_emp_city => l_emp_city
363 ,p_emp_state => l_emp_state
364 ,p_emp_zip => l_emp_zip
365 ,p_emp_zip_extension => l_emp_zip_extension
366 ,p_emp_country_code => l_emp_country
367 ,p_date_of_birth => DATE_OF_BIRTH
368 ,p_date_of_hire => DATE_START
369 ,p_state_of_hire => HIRE_STATE
370 ,p_medical_insr_avail => NULL --#13082528
371 ,p_federal_id => FEDERAL_ID
372 ,p_sit_company_state_id => SUI_COMPANY_STATE_ID ,p_tax_unit_name => TAX_UNIT_NAME
373 ,p_loc_address_line1 => l_loc_address_line1
374 ,p_loc_address_line2 => l_loc_address_line2
375 ,p_loc_address_line3 => l_loc_address_line3
376 ,p_loc_city => l_loc_city
377 ,p_loc_state => l_loc_state
378 ,p_loc_zip => l_loc_zip
379 ,p_loc_zip_extension => l_loc_zip_extension
380 ,p_loc_country_code => l_loc_country
381 ,p_loc_phone => c_contact_phone
382 ,p_loc_phone_extension => ' '
383 ,p_loc_contact => c_contact_name
384 ,p_opt_address_line1 => ' '
385 ,p_opt_address_line2 => ' '
386 ,p_opt_address_line3 => ' '
387 ,p_opt_city => ' '
388 ,p_opt_state => ' '
389 ,p_opt_zip => ' '
390 ,p_opt_zip_extension => ' '
391 ,p_opt_country_code => ' '
392 ,p_opt_phone => ' '
393 ,p_opt_phone_extension => ' '
394 ,p_opt_contact => ' '
395 ,p_multi_state => p_multi_state_1
396 );
397
398 -- file_io.put(l_buffer);
399 if p_audit_report = 'Y' then
400
401 l_buffer := per_new_hire_pkg.a03_fl_new_hire_record(
402 p_national_identifier => NATIONAL_IDENTIFIER
403 ,p_first_name => FIRST_NAME
404 ,p_middle_name => FULL_MIDDLE_NAME
405 ,p_last_name => LAST_NAME
406 ,p_emp_address_line1 => l_emp_address_line1
407 ,p_emp_address_line2 => l_emp_address_line2
408 ,p_emp_address_line3 => l_emp_address_line3
409 ,p_emp_city => l_emp_city
410 ,p_emp_state => l_emp_state
411 ,p_emp_zip => l_emp_zip
412 ,p_emp_zip_extension => l_emp_zip_extension
413 ,p_emp_country_code => l_emp_country
414 ,p_date_of_birth => DATE_OF_BIRTH
415 ,p_date_of_hire => DATE_START
416 ,p_state_of_hire => HIRE_STATE
417 ,p_medical_insr_avail => NULL --#13082528
418 ,p_federal_id => FEDERAL_ID
419 ,p_state_ein => SUI_COMPANY_STATE_ID ,p_tax_unit_name => TAX_UNIT_NAME
420 ,p_loc_address_line1 => l_loc_address_line1
421 ,p_loc_address_line2 => l_loc_address_line2
422 ,p_loc_address_line3 => l_loc_address_line3
423 ,p_loc_city => l_loc_city
424 ,p_loc_state => l_loc_state
425 ,p_loc_zip => l_loc_zip
426 ,p_loc_zip_extension => l_loc_zip_extension
427 ,p_loc_country_code => l_loc_country
428 ,p_contact_phone => c_contact_phone
429 ,p_contact_phone_ext => ' '
430 ,p_contact_name => c_contact_name
431 ,p_multi_state => p_multi_state_1
432 );
433 -- file_io.put_a03(l_buffer);
434 end if;
435 elsif p_state_code = 'IL' then
436 l_location_id := gre_location_id;
437 per_new_hire_pkg.get_location_address_3lines
438 (l_location_id,l_loc_address_line1,l_loc_address_line2,l_loc_address_line3
439 ,l_loc_city,l_loc_state,l_loc_zip,l_loc_zip_extension,l_loc_country);
440 per_new_hire_pkg.get_employee_address_3lines
441 (l_person_id,l_emp_address_line1,l_emp_address_line2,l_emp_address_line3
442 ,l_emp_city,l_emp_state,l_emp_zip,l_emp_zip_extension,l_emp_country);
443 l_buffer := per_new_hire_pkg.il_new_hire_record(
444 p_record_identifier => 'W4'
445 ,p_national_identifier => NATIONAL_IDENTIFIER
446 ,p_first_name => FIRST_NAME
447 ,p_middle_name => FULL_MIDDLE_NAME
448 ,p_last_name => LAST_NAME
449 ,p_emp_address_line1 => l_emp_address_line1
450 ,p_emp_address_line2 => l_emp_address_line2
451 ,p_emp_city => l_emp_city
452 ,p_emp_state => l_emp_state
453 ,p_emp_zip => l_emp_zip
454 ,p_emp_zip_extension => l_emp_zip_extension
455 ,p_date_of_hire => DATE_START
456 ,p_federal_id => FEDERAL_ID
457 ,p_tax_unit_name => TAX_UNIT_NAME
458 ,p_loc_address_line1 => l_loc_address_line1
459 ,p_loc_address_line2 => l_loc_address_line2
460 ,p_loc_city => l_loc_city
461 ,p_loc_state => l_loc_state
462 ,p_loc_zip => l_loc_zip
463 ,p_loc_zip_extension => l_loc_zip_extension
464 ,p_opt_address_line1 => ' '
465 ,p_opt_address_line2 => ' '
466 ,p_opt_city => ' '
467 ,p_opt_state => ' '
468 ,p_opt_zip => ' '
469 ,p_opt_zip_extension => ' '
470 );
471 -- file_io.put(l_buffer);
472 if p_audit_report = 'Y' then
473
474 l_buffer := per_new_hire_pkg.a03_il_new_hire_record(
475 p_national_identifier => NATIONAL_IDENTIFIER
476 ,p_first_name => FIRST_NAME
477 ,p_middle_name => FULL_MIDDLE_NAME
478 ,p_last_name => LAST_NAME
479 ,p_emp_address_line1 => l_emp_address_line1
480 ,p_emp_address_line2 => l_emp_address_line2 || ' ' || l_emp_address_line3
481 ,p_emp_city => l_emp_city
482 ,p_emp_state => l_emp_state
483 ,p_emp_zip => l_emp_zip
484 ,p_emp_zip_extension => l_emp_zip_extension
485 ,p_date_of_hire => DATE_START
486 ,p_federal_id => FEDERAL_ID
487 ,p_tax_unit_name => TAX_UNIT_NAME
488 ,p_loc_address_line1 => l_loc_address_line1
489 ,p_loc_address_line2 => l_loc_address_line2 || ' ' || l_loc_address_line3
490 ,p_loc_city => l_loc_city
491 ,p_loc_state => l_loc_state
492 ,p_loc_zip => l_loc_zip
493 ,p_loc_zip_extension => l_loc_zip_extension
494 );
495 -- file_io.put_a03(l_buffer);
496 end if;
497 elsif p_state_code = 'TX' then
498 l_location_id := gre_location_id;
499 hr_utility.trace('gre_location_id = ' || l_location_id);
500 per_new_hire_pkg.get_location_address_3lines
501 (l_location_id,l_loc_address_line1,l_loc_address_line2,l_loc_address_line3
502 ,l_loc_city,l_loc_state,l_loc_zip,l_loc_zip_extension,l_loc_country);
503 per_new_hire_pkg.get_employee_address_3lines
504 (l_person_id,l_emp_address_line1,l_emp_address_line2,l_emp_address_line3
505 ,l_emp_city,l_emp_state,l_emp_zip,l_emp_zip_extension,l_emp_country);
506 l_buffer := per_new_hire_pkg.tx_new_hire_record(
507 p_record_identifier => 'W4'
508 ,p_national_identifier => NATIONAL_IDENTIFIER
509 ,p_first_name => FIRST_NAME
510 ,p_middle_name => FULL_MIDDLE_NAME
511 ,p_last_name => LAST_NAME
512 ,p_emp_address_line1 => l_emp_address_line1
513 ,p_emp_address_line2 => l_emp_address_line2 ||' ' || l_emp_address_line3
514 ,p_emp_city => l_emp_city
515 ,p_emp_state => l_emp_state
516 ,p_emp_zip => l_emp_zip
517 ,p_emp_zip_extension => l_emp_zip_extension
518 ,p_emp_country_code => l_emp_country
519 ,p_emp_country_name => ' '
523 ,p_state_of_hire => HIRE_STATE
520 ,p_emp_country_zip => ' '
521 ,p_date_of_birth => DATE_OF_BIRTH
522 ,p_date_of_hire => DATE_START
524 ,p_federal_id => FEDERAL_ID
525 ,p_state_ein => SIT_COMPANY_STATE_ID
526 ,p_tax_unit_name => TAX_UNIT_NAME
527 ,p_loc_address_line1 => l_loc_address_line1
528 ,p_loc_address_line2 => l_loc_address_line2
529 ,p_loc_address_line3 => l_loc_address_line3
530 ,p_loc_city => l_loc_city
531 ,p_loc_state => l_loc_state
532 ,p_loc_zip => l_loc_zip
533 ,p_loc_zip_extension => l_loc_zip_extension
534 ,p_loc_country_code => l_loc_country
535 ,p_loc_country_name => ' '
536 ,p_loc_country_zip => ' '
537 ,p_opt_address_line1 => ' '
538 ,p_opt_address_line2 => ' '
539 ,p_opt_address_line3 => ' '
540 ,p_opt_city => ' '
541 ,p_opt_state => ' '
542 ,p_opt_zip => ' '
543 ,p_opt_zip_extension => ' '
544 ,p_opt_country_code => ' '
545 ,p_opt_country_name => ' '
546 ,p_opt_country_zip => ' '
547 ,p_salary => ' ' ,p_frequency => ' ' );
548 -- file_io.put(l_buffer);
549 if p_audit_report = 'Y' then
550
551 l_buffer := per_new_hire_pkg.a03_tx_new_hire_record(
552 p_national_identifier => NATIONAL_IDENTIFIER
553 ,p_first_name => FIRST_NAME
554 ,p_middle_name => FULL_MIDDLE_NAME
555 ,p_last_name => LAST_NAME
556 ,p_emp_address_line1 => l_emp_address_line1
557 ,p_emp_address_line2 => l_emp_address_line2
558 ,p_emp_address_line3 => l_emp_address_line3
559 ,p_emp_city => l_emp_city
560 ,p_emp_state => l_emp_state
561 ,p_emp_zip => l_emp_zip
562 ,p_emp_zip_extension => l_emp_zip_extension
563 ,p_emp_country_code => l_emp_country
564 ,p_emp_country_name => ' '
565 ,p_emp_country_zip => ' '
566 ,p_date_of_birth => DATE_OF_BIRTH
567 ,p_date_of_hire => DATE_START
568 ,p_state_of_hire => HIRE_STATE
569 ,p_federal_id => FEDERAL_ID
570 ,p_state_ein => SIT_COMPANY_STATE_ID
571 ,p_tax_unit_name => TAX_UNIT_NAME
572 ,p_loc_address_line1 => l_loc_address_line1
573 ,p_loc_address_line2 => l_loc_address_line2
574 ,p_loc_address_line3 => l_loc_address_line3
575 ,p_loc_city => l_loc_city
576 ,p_loc_state => l_loc_state
577 ,p_loc_zip => l_loc_zip
578 ,p_loc_zip_extension => l_loc_zip_extension
579 ,p_loc_country_code => l_loc_country
580 ,p_loc_country_name => ' '
581 ,p_loc_country_zip => ' '
582 );
583 -- file_io.put_a03(l_buffer);
584 end if;
585 end if;
586 hr_utility.set_location('Leaving...' || l_name,100);
587 exception when others then
588 hr_utility.set_location('Leaving....' || l_name,999);
589 c_fatal_error_flag := 7;
590 /*srw.message(288, 'Error found in ' || l_name);*/null;
591
592 fnd_message.raise_error;
593 END;
594
595 PROCEDURE TOTAL_RECORD IS
596 l_buffer varchar2(2000);
597 l_name varchar(60) := 'TOTAL_RECORD';
598 BEGIN
599
600 hr_utility.set_location('Entering.....:' || l_name,10);
601 if p_state_code = 'CA' then
602 l_buffer := per_new_hire_pkg.ca_t4_record(
603 p_record_identifier => 'T4'
604 ,p_number_of_employee => C_NO_OF_NEWHIRE
605 );
606
607 hr_utility.trace('l_buffer = ' || l_buffer);
608 --file_io.put(l_buffer);
609 elsif p_state_code = 'NY' then
610 l_buffer := per_new_hire_pkg.ny_1t_record(
611 p_record_identifier => '1T'
612 ,p_number_of_employee => C_NO_OF_NEWHIRE
613 );
614
615 hr_utility.trace('l_buffer = ' || l_buffer);
616 --file_io.put(l_buffer);
617 l_buffer := per_new_hire_pkg.ny_1f_record(
618 p_record_identifier => '1F'
619 ,p_number_of_employer => c_no_of_gre
620 );
621
622 hr_utility.trace('l_buffer = ' || l_buffer);
623 --file_io.put(l_buffer);
624
625 end if;
626 hr_utility.set_location('Leaving.....:' || l_name,100);
627 exception when others then
628 hr_utility.set_location('Leaving....' || l_name,999);
629 c_fatal_error_flag := 9;
630 /*srw.message(288, 'Error found in ' || l_name);*/null;
631
632
633
634 END;
635
636 procedure a01_header_record(TAX_UNIT_ID in number,federal_id in varchar2) is
637 l_buffer varchar2(2000);
638 l_loc_address_line1 varchar2(240);
639 l_loc_address_line2 varchar2(240);
640 l_loc_address_line3 varchar2(240);
641 l_loc_city varchar2(50);
642 l_loc_state varchar2(50);
643 l_loc_zip varchar2(10);
644 l_loc_zip_extension varchar2(10);
645 l_loc_country varchar2(10);
646 l_location_id number(15);
647 l_tax_unit_name varchar2(240);
648 l_name varchar2(60) := 'a01_header_record';
649 l_zip varchar2(10);
650 g_delimiter varchar2(1) := fnd_global.local_chr(10);
651
652 CURSOR c_tax_unit_name IS
653 select hou.name
654 ,hou.location_id
655 from hr_organization_units hou
656 where hou.business_group_id = P_BUSINESS_GROUP_ID
657 and hou.organization_id = TAX_UNIT_ID;
658
659 BEGIN
660
661 hr_utility.set_location('Entering....' || l_name,10);
662 hr_utility.trace('p_tax_unit_id = ' || to_char(P_TAX_UNIT_ID));
663 hr_utility.trace('p_business_group_id = ' || to_char(P_BUSINESS_GROUP_ID));
667 close c_tax_unit_name;
664
665 open c_tax_unit_name;
666 fetch c_tax_unit_name into l_tax_unit_name,l_location_id;
668
669 hr_utility.trace('location_id = ' || to_char(l_location_id));
670 per_new_hire_pkg.get_location_address_3lines
671 (l_location_id,l_loc_address_line1,l_loc_address_line2,l_loc_address_line3
672 ,l_loc_city,l_loc_state,l_loc_zip,l_loc_zip_extension,l_loc_country);
673
674 l_tax_unit_name := upper(l_tax_unit_name);
675 l_loc_address_line1 := upper(l_loc_address_line1);
676 l_loc_address_line2 := upper(l_loc_address_line2);
677 l_loc_address_line3 := upper(l_loc_address_line3);
678 l_loc_city := upper(l_loc_city);
679 l_loc_state := upper(l_loc_state);
680
681 /*file_io.put_a01('Employer Record' || g_delimiter);
682 file_io.put_a01(g_delimiter);
683 file_io.put_a01('Name : ' || l_tax_unit_name || g_delimiter);
684 file_io.put_a01('FEIN : ' || federal_id || g_delimiter);
685 file_io.put_a01('Address : ' || l_loc_address_line1 || ' ' || l_loc_address_line2 || ' ' || l_loc_address_line3 || g_delimiter);
686 file_io.put_a01('City : ' || l_loc_city || g_delimiter);
687 file_io.put_a01('State : ' || l_loc_state || g_delimiter);
688 if l_loc_zip_extension is null then
689 file_io.put_a01('zip : ' || l_loc_zip || g_delimiter);
690 else
691 file_io.put_a01('zip : ' || l_loc_zip || '-' || l_loc_zip_extension || g_delimiter);
692 end if;
693 file_io.put_a01(g_delimiter); */
694 hr_utility.set_location('Leaving....' || l_name,100);
695
696 exception when others then
697 hr_utility.set_location('Leaving....' || l_name,999);
698 c_fatal_error_flag := 3;
699 /*srw.message(288, 'Error found in ' || l_name);*/null;
700
701
702 END;
703
704 procedure a01_total_record(CS_NO_OF_NEW_HIRE in number) is
705 l_name varchar(60) := 'a01_total_record';
706 g_delimiter varchar2(1) := fnd_global.local_chr(10);
707 l_total_number number(10);
708
709
710 BEGIN
711
712 hr_utility.set_location('Entering....' || l_name,10);
713 l_total_number := CS_NO_OF_NEW_HIRE ;
714 -- file_io.put_a01('All state new hire reported Totals : ' || l_total_number);
715 hr_utility.set_location('Leaving....' || l_name,100);
716 END;
717
718 PROCEDURE A03_HEADER_RECORD IS
719 l_name varchar(60) := 'a03_header_record';
720 l_buffer varchar2(2000);
721
722 BEGIN
723
724 hr_utility.set_location('Entering....' || l_name,10);
725 if p_state_code = 'CA' then
726 l_buffer := per_new_hire_pkg.a03_ca_new_hire_header;
727 elsif p_state_code = 'NY' then
728 l_buffer := per_new_hire_pkg.a03_ny_new_hire_header;
729 elsif p_state_code = 'IL' then
730 l_buffer := per_new_hire_pkg.a03_il_new_hire_header;
731 elsif p_state_code = 'FL' then
732 l_buffer := per_new_hire_pkg.a03_fl_new_hire_header;
733 elsif p_state_code = 'TX' then
734 l_buffer := per_new_hire_pkg.a03_tx_new_hire_header;
735 end if;
736 hr_utility.set_location('Entering....' || l_name,20);
737
738 -- file_io.put_a03(l_buffer);
739
740 hr_utility.set_location('Leaving....' || l_name,100);
741 exception when others then
742 hr_utility.set_location('Leaving....' || l_name,999);
743 c_fatal_error_flag := 4;
744 /*srw.message(288, 'Error found in ' || l_name);*/null;
745
746 END;
747
748 PROCEDURE P_MAG_UPDATE_STATUS IS
749 BEGIN
750 DECLARE
751 CURSOR c_person_id is
752 select
753 ppf.PERSON_ID
754 ,ppf.LAST_NAME LAST_NAME
755 ,ppf.FIRST_NAME FIRST_NAME
756 ,hl.region_2 STATE
757 From
758 per_all_people_f ppf
759
760 ,hr_locations_all hl
761 ,hr_soft_coding_keyflex hscf
762 ,per_all_assignments_f paf
763 ,per_periods_of_service pps
764 ,hr_organization_information hoi4
765 ,hr_organization_information hoi3
766 ,hr_organization_information hoi2
767 ,hr_organization_information hoi1
768 ,hr_organization_units hou
769 Where
770 pps.person_id = ppf.person_id
771
772 And fnd_date.canonical_to_date(P_REPORT_DATE)
773 between pps.date_start and NVL(pps.actual_termination_date,C_END_OF_TIME)
774 And fnd_date.canonical_to_date(P_REPORT_DATE)
775 between ppf.effective_start_date and ppf.effective_end_date
776 And ppf.person_id = paf.person_id
777 And fnd_date.canonical_to_date(P_REPORT_DATE)
778 between paf.effective_start_date and paf.effective_end_date
779
780 and hscf.segment1 = to_char(hou.organization_id)
781 and hou.business_group_id = p_business_group_id
782 and hou.organization_id = NVL(p_tax_unit_id,hou.organization_id)
783 and hl.region_2 = DECODE(p_multi_state_1,'N',p_state_code,hl.region_2)
784 And paf.soft_coding_keyflex_id = hscf.soft_coding_keyflex_id
785 And paf.assignment_type = 'E'
786 And paf.primary_flag = 'Y'
787 And paf.location_id = hl.location_id
788
789
790 And ppf.business_group_id +0 = P_BUSINESS_GROUP_ID
791 And ppf.per_information_category = 'US'
792 And pps.date_start <= fnd_date.canonical_to_date(P_REPORT_DATE)
793 And ppf.per_information7 = 'INCL'
794 and hou.business_group_id = ppf.business_group_id
795 and hoi1.organization_id = hou.organization_id
796 and hoi1.org_information_context = 'CLASS'
797 and hoi1.org_information1 = 'HR_LEGAL'
798 and hoi1.org_information2 ='Y'
799 and hoi2.organization_id(+) = hou.organization_id
803 and hoi4.organization_id(+) = hou.organization_id
800 and hoi2.org_information_context ='Employer Identification'
801 and hoi3.organization_id(+) = hou.organization_id
802 and hoi3.org_information_context(+) = 'New Hire Reporting'
804 and hoi4.org_information_context(+) = 'State Tax Rules'
805 and hoi4.org_information1(+) = nvl(P_STATE_CODE,hoi4.org_information1(+))
806 UNION
807
808 select ppf.PERSON_ID
809 ,ppf.LAST_NAME LAST_NAME
810 ,ppf.FIRST_NAME FIRST_NAME
811 ,hl.region_2 STATE
812 From
813 per_all_people_f ppf
814
815 ,hr_locations_all hl
816 ,hr_soft_coding_keyflex hscf
817 ,per_all_assignments_f paf
818 ,per_periods_of_service pps
819 ,hr_organization_information hoi4
820 ,hr_organization_information hoi3
821 ,hr_organization_information hoi2
822 ,hr_organization_information hoi1
823 ,hr_organization_units hou
824 Where
825 pps.person_id = ppf.person_id
826
827 And fnd_date.canonical_to_date(P_REPORT_DATE)
828 between ppf.effective_start_date and ppf.effective_end_date
829 And pps.actual_termination_date IS NOT NULL
830 And ppf.person_id = paf.person_id
831 And not exists (select 1 from per_all_assignments_f paf2
832 where ppf.person_id = paf2.person_id
833 and fnd_date.canonical_to_date(P_REPORT_DATE)
834 between paf2.effective_start_date and paf2.effective_end_date
835 )
836 And pps.date_start = paf.effective_start_date
837
838 and hscf.segment1 = to_char(hou.organization_id)
839 and hou.business_group_id = p_business_group_id
840 and hou.organization_id = NVL(p_tax_unit_id,hou.organization_id)
841 and hl.region_2 = DECODE(p_multi_state_1,'N',p_state_code,hl.region_2)
842 And paf.soft_coding_keyflex_id = hscf.soft_coding_keyflex_id
843 And paf.assignment_type = 'E'
844 And paf.primary_flag = 'Y'
845 And paf.location_id = hl.location_id
846
847
848 And ppf.business_group_id +0 = P_BUSINESS_GROUP_ID
849 And ppf.per_information_category = 'US'
850 And pps.date_start <= fnd_date.canonical_to_date(P_REPORT_DATE)
851 And ppf.per_information7 = 'INCL'
852 and hou.business_group_id = ppf.business_group_id
853 and hoi1.organization_id = hou.organization_id
854 and hoi1.org_information_context = 'CLASS'
855 and hoi1.org_information1 = 'HR_LEGAL'
856 and hoi1.org_information2 ='Y'
857 and hoi2.organization_id(+) = hou.organization_id
858 and hoi2.org_information_context ='Employer Identification'
859 and hoi3.organization_id(+) = hou.organization_id
860 and hoi3.org_information_context(+) = 'New Hire Reporting'
861 and hoi4.organization_id(+) = hou.organization_id
862 and hoi4.org_information_context(+) = 'State Tax Rules'
863 and hoi4.org_information1(+) = nvl(P_STATE_CODE,hoi4.org_information1(+))
864 order by 4,2,3;
865
866
867 v_person_id per_people_f.person_id%TYPE;
868 v_first_name per_people_f.first_name%TYPE;
869 v_last_name per_people_f.last_name%TYPE;
870 v_state hr_locations_all.region_2%TYPE;
871 l_name varchar(60) := 'P_MAG_UPDATE_STATUS';
872 g_delimiter varchar2(1) := fnd_global.local_chr(10);
873
874 BEGIN
875 hr_utility.set_location('Entered ......:' || l_name,10);
876 if c_person_id%ISOPEN then
877 close c_person_id;
878 end if;
879 OPEN c_person_id;
880 LOOP
881
882 FETCH c_person_id INTO v_person_id, v_last_name, v_first_name, v_state;
883
884 hr_utility.trace('v_person_id = ' || to_char(v_person_id));
885 hr_utility.trace('v_state = ' || v_state);
886
887 if p_report_mode = 'F' then
888 UPDATE per_people_f
889 SET per_information7 = 'DONE'
890 WHERE person_id = v_person_id
891 AND per_information7 = 'INCL';
892 end if;
893
894
895 hr_utility.trace('c_old_state = ' || c_old_state);
896 if c_old_state is NULL then
897 hr_utility.set_location(l_name,20);
898 c_old_state := v_state;
899 c_state_count := 1;
900 elsif c_old_state <> v_state then
901 hr_utility.set_location(l_name,30);
902 -- file_io.put_a01(c_old_state || ' state new hire reported Totals : ' || c_state_count || g_delimiter);
903 c_state_count := 1;
904 c_old_state := v_state;
905 else
906 hr_utility.set_location(l_name,40);
907 c_state_count := c_state_count + 1;
908 hr_utility.trace('c_state_count =' || c_state_count);
909 end if;
910
911 EXIT WHEN c_person_id%NOTFOUND;
912
913 END LOOP;
914
915 hr_utility.set_location(l_name,50);
916
917 c_state_count := c_state_count - 1;
918 if c_state_count <> 0 then
919 -- file_io.put_a01(c_old_state || ' state new hire reported Totals : ' || c_state_count || g_delimiter);
920 null;
921 end if;
922 hr_utility.set_location(l_name,60);
923 CLOSE c_person_id;
924 COMMIT;
925 hr_utility.set_location(l_name,90);
926
927 /*srw.message('101', 'Report Mode : ' || p_report_mode);*/null;
928
929 if p_report_mode = 'F' then
930 /*srw.message('100', 'The New Hire Status has changed to ''Already Reported'' if there is/are any new employee(s).');*/null;
931
932 end if;
933 /*srw.message('102', 'Called Status Update');*/null;
934
935 hr_utility.set_location('Leaving....' || l_name,100);
936 exception
937 when others then
938 /*srw.message(290, 'The error message is '||sqlerrm);*/null;
939
940 rollback;
941
942 END;
943
944
945 END;
946
947 function CF_GREFormula (FEDERAL_ID in varchar2,gre_location_id in number,TAX_UNIT_ID in number,TAX_UNIT_NAME in varchar2,SIT_COMPANY_STATE_ID in varchar2)return Number is
948 l_name varchar2(60) := 'CF_GREFormula';
949 l_buffer varchar2(1000);
950 g_delimiter varchar2(1) := fnd_global.local_chr(10);
951 begin
952
953 hr_utility.set_location('Entering...' || l_name,0);
954 hr_utility.trace('p_audit_report = ' || p_audit_report);
955 hr_utility.trace('p_state_code = ' || p_state_code);
956
957 hr_utility.set_location(l_name,20);
958 gre_record(gre_location_id,tax_unit_id,FEDERAL_ID,SIT_COMPANY_STATE_ID,TAX_UNIT_NAME) ;
959 hr_utility.set_location(l_name,30);
960
961
962 hr_utility.set_location('Leaving....:' || l_name,100);
963 return(0);
964
965 end;
966
967 function c_contact_nameformula(new_hire_contact_id in varchar2) return varchar2 is
968 begin
969
970 declare
971
972 l_person_id number(15);
973 l_business_group_id number(15);
974 l_report_date date;
975 l_contact_name varchar2(240);
976 l_contact_title varchar2(60);
977 l_contact_phone varchar2(60);
978
979 begin
980
981 l_person_id := new_hire_contact_id;
982 l_report_date := fnd_date.canonical_to_date(P_REPORT_DATE);
983 l_business_group_id := P_BUSINESS_GROUP_ID;
984
985 per_new_hire_pkg.get_new_hire_contact
986 (l_person_id,
987 l_business_group_id,
988 l_report_date,
989 l_contact_name,
990 l_contact_title,
991 l_contact_phone
992 );
993
994 hr_utility.set_location('Entered c_person_dets',5);
995
996
997 hr_utility.trace('Contact name => '||l_contact_name);
998 hr_utility.set_location('Leaving c_contact_name', 10);
999
1000 return(l_contact_name);
1001
1002 exception when NO_DATA_FOUND then
1003 hr_utility.trace('Error is found in c_contact_name');
1004 null;
1005
1006 end;
1007
1008
1009 RETURN NULL; end;
1010
1011 function c_contact_phoneformula(new_hire_contact_id in varchar2) return varchar2 is
1012 begin
1013
1014 declare
1015
1016 l_person_id number(15);
1017 l_business_group_id number(15);
1018 l_report_date date;
1019 l_contact_name varchar2(240);
1020 l_contact_title varchar2(60);
1021 l_contact_phone varchar2(60);
1022
1023 begin
1024
1025 l_person_id := new_hire_contact_id;
1026 l_report_date := fnd_date.canonical_to_date(P_REPORT_DATE);
1027 l_business_group_id := P_BUSINESS_GROUP_ID;
1028
1029 per_new_hire_pkg.get_new_hire_contact
1030 (l_person_id,
1031 l_business_group_id,
1032 l_report_date,
1033 l_contact_name,
1034 l_contact_title,
1035 l_contact_phone
1036 );
1037
1038 hr_utility.set_location('Entered c_contact_phone',5);
1039
1040
1041 hr_utility.trace('Contact phone => '||l_contact_phone);
1042 hr_utility.set_location('Leaving c_contact_phone', 10);
1043
1044 return(l_contact_phone);
1045
1046 exception when NO_DATA_FOUND then
1047 hr_utility.trace('Error is found in c_contact_phone');
1048 null;
1049
1050 end;
1051
1052 RETURN NULL; end;
1053
1054 function c_contact_titleformula(new_hire_contact_id in varchar2) return varchar2 is
1055 begin
1056
1057 declare
1058
1059 l_person_id number(15);
1060 l_business_group_id number(15);
1061 l_report_date date;
1062 l_contact_name varchar2(240);
1063 l_contact_title varchar2(60);
1064 l_contact_phone varchar2(60);
1065
1066 begin
1067
1071
1068 l_person_id := new_hire_contact_id;
1069 l_report_date := fnd_date.canonical_to_date(P_REPORT_DATE);
1070 l_business_group_id := P_BUSINESS_GROUP_ID;
1072 per_new_hire_pkg.get_new_hire_contact
1073 (l_person_id,
1074 l_business_group_id,
1075 l_report_date,
1076 l_contact_name,
1077 l_contact_title,
1078 l_contact_phone
1079 );
1080
1081 hr_utility.set_location('Entered c_contact_title',5);
1082
1083
1084 hr_utility.trace('Contact title => '||l_contact_title);
1085 hr_utility.set_location('Leaving c_contact_title',10);
1086
1087 return(l_contact_title);
1088
1089 exception when NO_DATA_FOUND then
1090 hr_utility.trace('Error is found in c_contact_title');
1091 null;
1092
1093 end;
1094
1095 RETURN NULL; end;
1096
1097 function c_tax_unit_addressformula(location_id in number) return varchar2 is
1098
1099 begin
1100
1101 DECLARE l_tax_unit_address VARCHAR2(2000);
1102 l_location_id NUMBER(15);
1103
1104 begin
1105
1106 l_location_id := location_id;
1107 hr_us_reports.get_address(l_location_id, l_tax_unit_address);
1108 return(l_tax_unit_address);
1109
1110 exception
1111 when others then
1112 hr_utility.trace('the error is '|| to_char(sqlcode)||sqlerrm);
1113 /*srw.message('1','Error is found in tax_unit_address formula');*/null;
1114
1115 /*srw.message('10','sqlcode is : '||to_char(sqlcode)||sqlerrm);*/null;
1116
1117 end;
1118
1119 RETURN NULL;
1120 end;
1121
1122 procedure gre_record(gre_location_id in number, federal_id in varchar2,TAX_UNIT_ID in number,SIT_COMPANY_STATE_ID in varchar2, TAX_UNIT_NAME in varchar2) is
1123
1124 l_buffer varchar2(2000);
1125 l_address varchar2(1000);
1126 l_city varchar2(50);
1127 l_state varchar2(50);
1128 l_zip varchar2(10);
1129 l_zip_extension varchar2(10);
1130 l_location_id number(15);
1131 l_transmitter_count number(15);
1132 l_trans_tax_unit_name varchar2(240);
1133 l_trans_federal_id varchar2(15);
1134 l_trans_location_id number(15);
1135 l_trans_tax_unit_id number(15);
1136 l_trans_address varchar2(1000);
1137 l_trans_city varchar2(50);
1138 l_trans_state varchar2(50);
1139 l_trans_zip varchar2(10);
1140 l_trans_zip_extension varchar2(10);
1141 l_branch_code varchar2(3) := ' '; l_name varchar2(61) := 'GRE_RECORD';
1142 g_delimiter varchar2(1) := fnd_global.local_chr(10);
1143 l_tx_emp_num number := 0;
1144 l_tx_term_num number := 0;
1145
1146 CURSOR c_transmitter IS
1147 SELECT
1148 distinct
1149 hou.name transmitter_name
1150 , replace(hoi2.org_information1 ,'-',null) trans_federal_id
1151 , hou.organization_id trans_tax_unit_id
1152 , hou.location_id trans_location_id
1153 FROM
1154 hr_organization_information hoi3
1155 , hr_organization_information hoi2
1156 , hr_organization_information hoi1
1157 , hr_organization_units hou
1158 WHERE
1159 hou.business_group_id = P_BUSINESS_GROUP_ID
1160 AND hoi1.organization_id = hou.organization_id
1161 AND hoi1.org_information_context = 'CLASS'
1162 AND hoi1.org_information1 = 'HR_LEGAL'
1163 AND hoi1.org_information2 = 'Y'
1164 AND hoi2.organization_id(+) = hou.organization_id
1165 AND hoi2.org_information_context = 'Employer Identification'
1166 AND hoi3.organization_id = hou.organization_id
1167 AND hoi3.org_information_context = 'New Hire Reporting'
1168 AND hoi3.org_information2 = 'Y'
1169 ;
1170
1171 CURSOR c_transmitter_count IS
1172 SELECT
1173 count(hou.organization_id)
1174 FROM
1175 hr_organization_information hoi3
1176 , hr_organization_units hou
1177 WHERE
1178 hou.business_group_id = P_BUSINESS_GROUP_ID
1179 AND hoi3.organization_id = hou.organization_id
1180 AND hoi3.org_information_context = 'New Hire Reporting'
1181 AND hoi3.org_information2 = 'Y'
1182 ;
1183
1184 CURSOR c_tx_emp_number IS
1185 select
1186 count(ppf.person_id)
1187
1188 From
1189 per_all_people_f ppf
1190 ,per_all_assignments_f paf
1191 ,hr_soft_coding_keyflex hscf
1192 ,hr_locations_all hl ,per_jobs job
1193 ,per_periods_of_service pps
1194 ,hr_organization_information hoi4
1195 ,hr_organization_information hoi3
1196 ,hr_organization_information hoi2
1197 ,hr_organization_information hoi1
1198 ,hr_organization_units hou
1199
1200 Where
1201 pps.person_id = ppf.person_id
1202 And fnd_date.canonical_to_date(P_REPORT_DATE)
1203 between pps.date_start and NVL(pps.actual_termination_date, C_END_OF_TIME)
1204 And fnd_date.canonical_to_date(P_REPORT_DATE)
1205 between ppf.effective_start_date and ppf.effective_end_date
1206 And ppf.person_id = paf.person_id
1207 And fnd_date.canonical_to_date(P_REPORT_DATE)
1208 between paf.effective_start_date and paf.effective_end_date
1209
1210 And hscf.segment1 = to_char(hou.organization_id)
1211 and hou.business_group_id = P_BUSINESS_GROUP_ID
1212 and hou.organization_id = NVL(P_TAX_UNIT_ID,hou.organization_id)
1213 and hl.region_2 = DECODE(P_MULTI_STATE_1,'N',P_STATE_CODE,hl.region_2)
1214 And paf.soft_coding_keyflex_id = hscf.soft_coding_keyflex_id
1215 And paf.assignment_type = 'E'
1216 And paf.primary_flag = 'Y'
1217 And paf.location_id = hl.location_id
1218 And paf.job_id = job.job_id(+)
1219 And fnd_date.canonical_to_date(P_REPORT_DATE) between job.date_from(+)
1220 and nvl(job.date_to, C_END_OF_TIME)
1221 And ppf.business_group_id = P_BUSINESS_GROUP_ID
1222 And ppf.per_information_category = 'US'
1223 And ppf.start_date <= fnd_date.canonical_to_date(P_REPORT_DATE)
1224 And ppf.per_information7 = 'INCL' And hou.business_group_id = ppf.business_group_id
1225 AND hoi1.organization_id = hou.organization_id
1226 AND hoi1.org_information_context = 'CLASS'
1227 AND hoi1.org_information1 = 'HR_LEGAL'
1228 AND hoi1.org_information2 = 'Y'
1229 AND hoi2.organization_id(+) = hou.organization_id
1230 AND hoi2.org_information_context = 'Employer Identification'
1231 AND hoi3.organization_id(+) = hou.organization_id
1232 AND hoi3.org_information_context(+) = 'New Hire Reporting'
1233 AND hoi4.organization_id(+) = hou.organization_id
1234 AND hoi4.org_information_context(+) = 'State Tax Rules'
1235 AND hoi4.org_information1(+) = NVL(P_STATE_CODE,hoi4.org_information4(+))
1236 ;
1237
1238 CURSOR c_tx_term_number IS
1239 select
1240 count(ppf.person_id)
1241
1242 From
1243 per_all_people_f ppf
1244 ,per_all_assignments_f paf
1245 ,hr_soft_coding_keyflex hscf
1246 ,hr_locations_all hl ,per_jobs job
1247 ,per_periods_of_service pps
1248 ,hr_organization_information hoi4
1249 ,hr_organization_information hoi3
1250 ,hr_organization_information hoi2
1251 ,hr_organization_information hoi1
1252 ,hr_organization_units hou
1253
1254 Where
1255 pps.person_id = ppf.person_id
1256
1257 And fnd_date.canonical_to_date(P_REPORT_DATE)
1258 between ppf.effective_start_date and ppf.effective_end_date
1259 And ppf.person_id = paf.person_id
1260 And not exists (select 1 from per_all_assignments_f paf2
1261 where ppf.person_id = paf2.person_id
1262 and fnd_date.canonical_to_date(P_REPORT_DATE)
1263 between paf2.effective_start_date and paf2.effective_end_date
1264 )
1265 And pps.date_start = paf.effective_start_date
1266
1267 And hscf.segment1 = to_char(hou.organization_id)
1271 And paf.soft_coding_keyflex_id = hscf.soft_coding_keyflex_id
1268 and hou.business_group_id = P_BUSINESS_GROUP_ID
1269 and hou.organization_id = NVL(P_TAX_UNIT_ID,hou.organization_id)
1270 and hl.region_2 = DECODE(P_MULTI_STATE_1,'N',P_STATE_CODE,hl.region_2)
1272 And paf.assignment_type = 'E'
1273 And paf.primary_flag = 'Y'
1274 And paf.location_id = hl.location_id
1275 And paf.job_id = job.job_id(+)
1276 And fnd_date.canonical_to_date(P_REPORT_DATE) between job.date_from(+)
1277 and nvl(job.date_to, C_END_OF_TIME)
1278 And ppf.business_group_id = P_BUSINESS_GROUP_ID
1279 And ppf.per_information_category = 'US'
1280 And ppf.start_date <= fnd_date.canonical_to_date(P_REPORT_DATE)
1281 And ppf.per_information7 = 'INCL' And hou.business_group_id = ppf.business_group_id
1282 AND hoi1.organization_id = hou.organization_id
1283 AND hoi1.org_information_context = 'CLASS'
1284 AND hoi1.org_information1 = 'HR_LEGAL'
1285 AND hoi1.org_information2 = 'Y'
1286 AND hoi2.organization_id(+) = hou.organization_id
1287 AND hoi2.org_information_context = 'Employer Identification'
1288 AND hoi3.organization_id(+) = hou.organization_id
1289 AND hoi3.org_information_context(+) = 'New Hire Reporting'
1290 AND hoi4.organization_id(+) = hou.organization_id
1291 AND hoi4.org_information_context(+) = 'State Tax Rules'
1292 AND hoi4.org_information1(+) = NVL(P_STATE_CODE,hoi4.org_information4(+))
1293 ;
1294
1295
1296
1297 BEGIN
1298 hr_utility.set_location('Entering.. ' || l_name,10);
1299 l_location_id := gre_location_id;
1300 hr_utility.trace('l_location_id = ' || l_location_id);
1301 hr_utility.set_location(l_name,20);
1302 hr_utility.trace('p_state_code = ' || p_state_code);
1303
1304 if p_state_code = 'CA' then
1305 hr_utility.set_location(l_name,30);
1306 hr_utility.trace('cp_pre_tax_unit_id = ' || cp_pre_tax_unit_id);
1307 hr_utility.trace('tax_unit_id = ' || tax_unit_id);
1308
1309 per_new_hire_pkg.get_location_address
1310 (l_location_id,l_address,l_city,l_state,l_zip,l_zip_extension);
1311
1312
1313 if cp_pre_tax_unit_id is NULL then
1314 a01_header_record(TAX_UNIT_ID,federal_id) ;
1315 l_buffer := per_new_hire_pkg.ca_e4_record(
1316 p_record_identifier => 'E4'
1317 ,p_federal_id => FEDERAL_ID
1318 ,p_sit_company_state_id => SIT_COMPANY_STATE_ID
1319 ,p_branch_code => l_branch_code
1320 ,p_tax_unit_name => TAX_UNIT_NAME
1321 ,p_street_address => l_address
1322 ,p_city => l_city
1323 ,p_state => l_state
1324 ,p_zip => l_zip
1325 ,p_zip_extension => l_zip_extension
1326 );
1327 hr_utility.trace('l_buffer = ' || l_buffer);
1328 --file_io.put(l_buffer);
1329 cp_pre_tax_unit_id := tax_unit_id;
1330 c_no_of_newhire := 0;
1331 c_no_of_gre := c_no_of_gre + 1;
1332 elsif cp_pre_tax_unit_id <> tax_unit_id then
1333 l_buffer := per_new_hire_pkg.ca_t4_record(
1334 p_record_identifier => 'T4'
1335 ,p_number_of_employee => C_NO_OF_NEWHIRE
1336 );
1337
1338 hr_utility.trace('l_buffer = ' || l_buffer);
1339 --file_io.put(l_buffer);
1340
1341 a01_header_record(TAX_UNIT_ID,federal_id) ;
1342
1343 l_buffer := per_new_hire_pkg.ca_e4_record(
1344 p_record_identifier => 'E4'
1345 ,p_federal_id => FEDERAL_ID
1346 ,p_sit_company_state_id => SIT_COMPANY_STATE_ID
1347 ,p_branch_code => l_branch_code
1348 ,p_tax_unit_name => TAX_UNIT_NAME
1349 ,p_street_address => l_address
1350 ,p_city => l_city
1351 ,p_state => l_state
1352 ,p_zip => l_zip
1353 ,p_zip_extension => l_zip_extension
1354 );
1355 hr_utility.trace('l_buffer = ' || l_buffer);
1356 --file_io.put(l_buffer);
1357 cp_pre_tax_unit_id := tax_unit_id;
1358 c_no_of_newhire := 0;
1359 c_no_of_gre := c_no_of_gre + 1;
1360 end if;
1361
1362 elsif p_state_code = 'NY' then
1363 hr_utility.set_location(l_name,40);
1364 hr_utility.trace('p_report_date = ' || p_report_date);
1365 per_new_hire_pkg.get_location_address
1366 (l_location_id,l_address,l_city,l_state,l_zip,l_zip_extension);
1367
1368 if cp_pre_tax_unit_id is NULL then
1369 hr_utility.set_location(l_name,41);
1370 open c_transmitter;
1371 fetch c_transmitter into l_trans_tax_unit_name,l_trans_federal_id,
1372 l_trans_tax_unit_id,l_trans_location_id;
1373 if c_transmitter%NOTFOUND then
1374 hr_utility.set_location(l_name,42);
1375 close c_transmitter;
1376 /*srw.message('100', 'You have selected New York to be your reporting state, but have not identified a GRE as the transmitter for the New Hire report.');*/null;
1377
1378 /*srw.message('100', 'Please select one of your GREs as the transmitter for this report in the New Hire Reporting organization information type.');*/null;
1379
1380 c_fatal_error_flag := 1;
1381 fnd_message.raise_error;
1382 else
1383 open c_transmitter_count;
1384 fetch c_transmitter_count into l_transmitter_count;
1385 hr_utility.trace('transmitter count = ' || to_char(l_transmitter_count));
1386 if l_transmitter_count > 1 then
1387 hr_utility.set_location(l_name,43);
1388 close c_transmitter_count;
1389 close c_transmitter;
1390 c_fatal_error_flag := 2;
1394
1391 /*srw.message('100', 'You have selected New York to be your reporting state, and have identified two GREs as the transmitter for the New Hire report.');*/null;
1392
1393 /*srw.message('100', 'Please select only one of your GREs as the transmitter for this report in the New Hire Reporting organization information type. ');*/null;
1395 fnd_message.raise_error;
1396 end if;
1397 close c_transmitter_count;
1398 end if;
1399 close c_transmitter;
1400 hr_utility.trace('transmitter_tax_unit_name = ' || l_trans_tax_unit_name);
1401 hr_utility.trace('transmitter_federal_id = ' || l_trans_federal_id);
1402 hr_utility.trace('transmitter_location_id = ' || to_char(l_trans_location_id));
1403
1404 per_new_hire_pkg.get_location_address
1405 (l_trans_location_id,l_trans_address
1406 ,l_trans_city,l_trans_state,l_trans_zip,l_trans_zip_extension);
1407
1408
1409 l_buffer := per_new_hire_pkg.ny_1a_record(
1410 p_record_identifier => '1A'
1411 ,p_creation_date => to_char(SYSDATE,'MMDDRR')
1412 ,p_federal_id => l_trans_federal_id
1413 ,p_tax_unit_name => l_trans_tax_unit_name
1414 ,p_street_address => l_trans_address
1415 ,p_city => l_trans_city
1416 ,p_state => l_trans_state
1417 ,p_zip => l_trans_zip
1418 );
1419
1420 hr_utility.set_location(l_name,42);
1421 /*file_io.put(l_buffer);
1422
1423 file_io.put_a01('Transmitter Record' || g_delimiter);
1424 file_io.put_a01(g_delimiter);
1425 file_io.put_a01('Name : ' || upper(l_trans_tax_unit_name) || g_delimiter);
1426 file_io.put_a01('FEIN : ' || upper(l_trans_federal_id) || g_delimiter);
1427 file_io.put_a01('Address : ' || upper(l_trans_address) || g_delimiter);
1428 file_io.put_a01('City : ' || upper(l_trans_city) || g_delimiter);
1429 file_io.put_a01('State : ' || upper(l_trans_state) || g_delimiter);
1430 if l_trans_zip_extension is null then
1431 file_io.put_a01('zip : ' || l_trans_zip || g_delimiter);
1432 else
1433 file_io.put_a01('zip : ' || l_trans_zip || '-' || l_trans_zip_extension || g_delimiter);
1434 end if;
1435 file_io.put_a01(g_delimiter); */
1436 a01_header_record(TAX_UNIT_ID,federal_id) ;
1437
1438
1439 l_buffer := per_new_hire_pkg.ny_1e_record(
1440 p_record_identifier => '1E'
1441 ,p_federal_id => FEDERAL_ID
1442 ,p_tax_unit_name => TAX_UNIT_NAME
1443 ,p_street_address => l_address
1444 ,p_city => l_city
1445 ,p_state => l_state
1446 ,p_zip => l_zip
1447 );
1448 hr_utility.set_location(l_name,45);
1449 hr_utility.trace('l_buffer = ' || l_buffer);
1450 -- file_io.put(l_buffer);
1451 cp_pre_tax_unit_id := tax_unit_id;
1452 c_no_of_newhire := 0;
1453 c_no_of_gre := 1;
1454
1455 elsif cp_pre_tax_unit_id <> tax_unit_id then
1456 hr_utility.set_location(l_name,50);
1457
1458 a01_header_record(TAX_UNIT_ID,federal_id) ;
1459
1460 l_buffer := per_new_hire_pkg.ny_1t_record(
1461 p_record_identifier => '1T'
1462 ,p_number_of_employee => C_NO_OF_NEWHIRE
1463 );
1464
1465 hr_utility.trace('l_buffer = ' || l_buffer);
1466 -- file_io.put(l_buffer);
1467 l_buffer := per_new_hire_pkg.ny_1e_record(
1468 p_record_identifier => '1E'
1469 ,p_federal_id => FEDERAL_ID
1470 ,p_tax_unit_name => TAX_UNIT_NAME
1471 ,p_street_address => l_address
1472 ,p_city => l_city
1473 ,p_state => l_state
1474 ,p_zip => l_zip
1475 );
1476 hr_utility.set_location(l_name,60);
1477 hr_utility.trace('l_buffer = ' || l_buffer);
1478 -- file_io.put(l_buffer);
1479 cp_pre_tax_unit_id := tax_unit_id;
1480 c_no_of_newhire := 0;
1481 C_no_of_gre := c_no_of_gre + 1;
1482 end if;
1483 elsif p_state_code = 'TX' then
1484 hr_utility.set_location(l_name,70);
1485 if cp_pre_tax_unit_id is NULL then
1486 hr_utility.set_location(l_name,71);
1487 a01_header_record(TAX_UNIT_ID,federal_id) ;
1488 cp_pre_tax_unit_id := tax_unit_id;
1489 open c_tx_emp_number;
1490 fetch c_tx_emp_number into l_tx_emp_num;
1491 if c_tx_emp_number%NOTFOUND then
1492 l_tx_emp_num := 0;
1493 end if;
1494 close c_tx_emp_number;
1495
1496 hr_utility.trace('l_tx_emp_num = ' || l_tx_emp_num);
1497 open c_tx_term_number;
1498 fetch c_tx_term_number into l_tx_term_num;
1499 if c_tx_term_number%NOTFOUND then
1500 l_tx_term_num := 0;
1501 end if;
1502 close c_tx_term_number;
1503 hr_utility.trace('l_tx_term_num = ' || l_tx_term_num);
1504
1505 l_buffer := per_new_hire_pkg.tx_t4_record(
1506 p_record_identifier => 'T4'
1507 ,p_number_of_employee => l_tx_emp_num + l_tx_term_num
1508 );
1509
1510 hr_utility.trace('l_buffer = ' || l_buffer);
1511 --file_io.put(l_buffer);
1512 elsif cp_pre_tax_unit_id <> tax_unit_id then
1513 hr_utility.set_location(l_name,72);
1514 a01_header_record(TAX_UNIT_ID,federal_id) ;
1515 cp_pre_tax_unit_id := tax_unit_id;
1516 end if;
1517 else hr_utility.set_location(l_name,80);
1518 if cp_pre_tax_unit_id is NULL then
1519 hr_utility.set_location(l_name,81);
1520 a01_header_record(TAX_UNIT_ID,federal_id) ;
1524 a01_header_record(TAX_UNIT_ID,federal_id) ;
1521 cp_pre_tax_unit_id := tax_unit_id;
1522 elsif cp_pre_tax_unit_id <> tax_unit_id then
1523 hr_utility.set_location(l_name,82);
1525 cp_pre_tax_unit_id := tax_unit_id;
1526 end if;
1527 end if;
1528 hr_utility.set_location('Leaving... ' || l_name,100);
1529 exception when others then
1530 hr_utility.set_location('Leaving....' || l_name,999);
1531 c_fatal_error_flag := 6;
1532 fnd_message.raise_error;
1533
1534 END;
1535
1536 PROCEDURE P_OUTPUT_NEW_HIRE_NULL IS
1537 BEGIN
1538
1539 DECLARE
1540 CURSOR c_person_id IS
1541 select
1542 ppf.person_id
1543 ,ppf.last_name LAST_NAME
1544 ,ppf.first_name FIRST_NAME
1545 ,substr(ppf.middle_names,1,1) middle_name
1546 ,ppf.national_identifier
1547 ,ppf.date_of_birth
1548 ,pps.date_start
1549 ,hl.region_2 STATE
1550
1551 From
1552 per_all_people_f ppf
1553 ,per_all_assignments_f paf
1554 ,hr_soft_coding_keyflex hscf
1555 ,hr_locations_all hl ,per_jobs job
1556 ,per_periods_of_service pps
1557
1558 Where
1559 pps.person_id = ppf.person_id
1560 And fnd_date.canonical_to_date(P_REPORT_DATE)
1561 between pps.date_start and NVL(pps.actual_termination_date, C_END_OF_TIME)
1562 And fnd_date.canonical_to_date(P_REPORT_DATE)
1563 between ppf.effective_start_date and ppf.effective_end_date
1564 And ppf.person_id = paf.person_id
1565 And fnd_date.canonical_to_date(P_REPORT_DATE)
1566 between paf.effective_start_date and paf.effective_end_date
1567 And hscf.segment1 in
1568 (SELECT to_char(hou.organization_id )
1569 FROM hr_organization_units hou
1570 WHERE hou.business_group_id = P_BUSINESS_GROUP_ID
1571 )
1572 And paf.soft_coding_keyflex_id = hscf.soft_coding_keyflex_id
1573 And paf.assignment_type = 'E'
1574 And paf.primary_flag = 'Y'
1575 And paf.location_id = hl.location_id
1576 And ((P_STATE_CODE = 'FL' and P_MULTI_STATE_1 <> 'Y' and hl.region_2 = 'FL')
1577 or (P_STATE_CODE = 'FL' and P_MULTI_STATE_1 = 'Y')
1578 or (P_STATE_CODE <> 'FL')
1579 )
1580 And paf.job_id = job.job_id(+)
1581 And fnd_date.canonical_to_date(P_REPORT_DATE) between job.date_from(+)
1582 and nvl(job.date_to, C_END_OF_TIME)
1583 And ppf.business_group_id = P_BUSINESS_GROUP_ID
1584 And ppf.per_information_category = 'US'
1585 And ppf.start_date <= fnd_date.canonical_to_date(P_REPORT_DATE)
1586 And ppf.per_information7 is NULL
1587 UNION
1588 select
1589 ppf.person_id
1590 ,ppf.last_name LAST_NAME
1591 ,ppf.first_name FIRST_NAME
1592 ,substr(ppf.middle_names,1,1) middle_name
1593 ,ppf.national_identifier
1594 ,ppf.date_of_birth
1595 ,pps.date_start
1596 ,hl.region_2 STATE
1597
1598 From
1599 per_all_people_f ppf
1600 ,per_all_assignments_f paf
1601 ,hr_soft_coding_keyflex hscf
1602 ,hr_locations_all hl ,per_jobs job
1603 ,per_periods_of_service pps
1604
1605 Where
1606 pps.person_id = ppf.person_id
1607
1608 And fnd_date.canonical_to_date(P_REPORT_DATE)
1609 between ppf.effective_start_date and ppf.effective_end_date
1610 And ppf.person_id = paf.person_id
1611 And not exists (select 1 from per_all_assignments_f paf2
1612 where ppf.person_id = paf2.person_id
1613 and fnd_date.canonical_to_date(P_REPORT_DATE)
1614 between paf2.effective_start_date and paf2.effective_end_date
1615 )
1616 And pps.date_start = paf.effective_start_date
1617 And hscf.segment1 in
1618 (SELECT to_char(hou.organization_id )
1619 FROM hr_organization_units hou
1620 WHERE hou.business_group_id = P_BUSINESS_GROUP_ID
1621 )
1622 And paf.soft_coding_keyflex_id = hscf.soft_coding_keyflex_id
1623 And paf.assignment_type = 'E'
1624 And paf.primary_flag = 'Y'
1625 And paf.location_id = hl.location_id
1626 And ((P_STATE_CODE = 'FL' and P_MULTI_STATE_1 <> 'Y' and hl.region_2 = 'FL')
1627 or (P_STATE_CODE = 'FL' and P_MULTI_STATE_1 = 'Y')
1628 or (P_STATE_CODE <> 'FL')
1629 )
1630 And paf.job_id = job.job_id(+)
1631 And fnd_date.canonical_to_date(P_REPORT_DATE) between job.date_from(+)
1632 and nvl(job.date_to, C_END_OF_TIME)
1633 And ppf.business_group_id = P_BUSINESS_GROUP_ID
1634 And ppf.per_information_category = 'US'
1635 And ppf.start_date <= fnd_date.canonical_to_date(P_REPORT_DATE)
1636 And ppf.per_information7 is NULL
1637 Order by 2,3;
1638
1639
1640 v_person_id per_all_people_f.person_id%TYPE;
1641 v_last_name per_all_people_f.last_name%TYPE;
1642 v_first_name per_all_people_f.first_name%TYPE;
1643 v_middle_name per_all_people_f.middle_names%TYPE;
1644 v_ssn per_all_people_f.national_identifier%TYPE;
1645 v_dob per_all_people_f.date_of_birth%TYPE;
1646 v_date_start per_periods_of_service.date_start%TYPE;
1647 v_header number;
1648 v_buffer varchar2(120);
1649 v_boolean boolean;
1650 v_state hr_locations_all.region_2%TYPE;
1651
1652
1653 BEGIN
1654 hr_utility.set_location('Entered p_output_new_hire_null',10);
1658 end if;
1655 v_header := 0;
1656 if c_person_id%ISOPEN then
1657 close c_person_id;
1659 OPEN c_person_id;
1660 FETCH c_person_id INTO v_person_id,v_last_name,v_first_name,v_middle_name,v_ssn,v_dob,v_date_start,v_state;
1661 WHILE c_person_id%FOUND LOOP
1662 if v_header = 0 then
1663 v_boolean := fnd_concurrent.set_completion_status('WARNING','');
1664 fnd_file.put_line(1,'Warning : The New Hire field of the following employees on people form is blank.');
1665 fnd_file.put_line(1,'Warning : Please update the New Hire field.');
1666 fnd_file.put_line(1,' ');
1667 fnd_file.put_line(1,'Last Name First Name MI SSN Hire Date DOB ');
1668 fnd_file.put_line(1,' ') ;
1669 v_buffer := rpad(v_last_name,24,' ') ||
1670 rpad(' ',1,' ') ||
1671 rpad(nvl(v_first_name,' '),20,' ') ||
1672 rpad(' ',1,' ') ||
1673 rpad(nvl(v_middle_name,' '),2,' ') ||
1674 rpad(' ',1,' ') ||
1675 rpad(nvl(v_ssn,' '),11,' ') ||
1676 rpad(' ',1,' ') ||
1677 rpad(to_date(v_date_start,'DD-MM-RRRR'),9,' ') ||
1678 rpad(' ',1,' ') ||
1679 rpad(to_date(v_dob,'DD-MM-RRRR'),9,' ');
1680 fnd_file.put_line(1,v_buffer);
1681 v_header := 1;
1682 else
1683 v_buffer := rpad(v_last_name,24,' ') ||
1684 rpad(' ',1,' ') ||
1685 rpad(nvl(v_first_name,' '),20,' ') ||
1686 rpad(' ',1,' ') ||
1687 rpad(nvl(v_middle_name,' '),2,' ') ||
1688 rpad(' ',1,' ') ||
1689 rpad(nvl(v_ssn,' '),11,' ') ||
1690 rpad(' ',1,' ') ||
1691 rpad(to_date(v_date_start,'DD-MM-RRRR'),9,' ') ||
1692 rpad(' ',1,' ') ||
1693 rpad(to_date(v_dob,'DD-MM-RRRR'),9,' ') ;
1694 fnd_file.put_line(1,v_buffer);
1695 end if;
1696 FETCH c_person_id INTO v_person_id,v_last_name,v_first_name,v_middle_name,v_ssn,v_dob,v_date_start,v_state;
1697
1698 END LOOP;
1699 fnd_file.put_line(1,' ');
1700
1701 hr_utility.set_location('p_output_new_hire_null',100);
1702
1703 CLOSE c_person_id;
1704 exception
1705 when others then
1706 /*srw.message(290, 'The error message is '||sqlerrm);*/null;
1707
1708 rollback;
1709 END;
1710
1711 END;
1712
1713 --Functions to refer Oracle report placeholders--
1714
1715 Function CP_pre_tax_unit_id_p return number is
1716 Begin
1717 return CP_pre_tax_unit_id;
1718 END;
1719 Function C_BUSINESS_GROUP_NAME_p return varchar2 is
1720 Begin
1721 return C_BUSINESS_GROUP_NAME;
1722 END;
1723 Function C_REPORT_SUBTITLE_p return varchar2 is
1724 Begin
1725 return C_REPORT_SUBTITLE;
1726 END;
1727 Function C_TAX_UNIT_p return varchar2 is
1728 Begin
1729 return C_TAX_UNIT;
1730 END;
1731 Function C_STATE_NAME_p return varchar2 is
1732 Begin
1733 return C_STATE_NAME;
1734 END;
1735 Function C_MEDICAL_AVAIL_p return varchar2 is
1736 Begin
1737 return C_MEDICAL_AVAIL;
1738 END;
1739 Function C_END_OF_TIME_p return date is
1740 Begin
1741 return C_END_OF_TIME;
1742 END;
1743 Function C_STATE_COUNT_p return number is
1744 Begin
1745 return C_STATE_COUNT;
1746 END;
1747 Function C_OLD_STATE_p return varchar2 is
1748 Begin
1749 return C_OLD_STATE;
1750 END;
1751 Function C_no_of_newhire_p return number is
1752 Begin
1753 return C_no_of_newhire;
1754 END;
1755 Function C_no_of_gre_p return number is
1756 Begin
1757 return C_no_of_gre;
1758 END;
1759 Function C_no_of_multi_state_p return number is
1760 Begin
1761 return C_no_of_multi_state;
1762 END;
1763 Function C_Fatal_error_flag_p return number is
1764 Begin
1765 return C_Fatal_error_flag;
1766 END;
1767 Function C_a03_header_flag_p return number is
1768 Begin
1769 return C_a03_header_flag;
1770 END;
1771 END PER_PERUSHRM_XMLP_PKG ;