DBA Data[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 ;