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