[Home] [Help]
PACKAGE BODY: APPS.PAY_US_INDIANA
Source
1 package body pay_us_indiana as
2 /* $Header: pyusinyb.pkb 115.8 2002/12/30 22:09:39 tclewis noship $*/
3
4
5 /* Script to get the vaule of person's address detail
6 insert the values into temporary table and delete the values from
7 temporary table once the data is printed.
8 */
9
10 PROCEDURE print_report_address(errbuf OUT NOCOPY VARCHAR2,
11 retcode OUT NOCOPY NUMBER)
12 IS
13 l6_wait BOOLEAN;
14 l6_phase VARCHAR2(30);
15 l6_status VARCHAR2(30);
16 l6_dev_phase VARCHAR2(30);
17 l6_dev_status VARCHAR2(30);
18 l6_message VARCHAR2(255);
19 l_rep_req_id NUMBER;
20 copies_buffer varchar2(80) := null;
21 print_buffer varchar2(80) := null;
22 printer_buffer varchar2(80) := null;
23 style_buffer varchar2(80) := null;
24 save_buffer boolean := null;
25 save_result varchar2(1) := null;
26 req_id VARCHAR2(80) := NULL; /* Request Id of the main request */
27 x BOOLEAN;
28
29 l_session_id number;
30 BEGIN
31 -- initialise variables - 0 is SRS Success, 1 is SRS Warning, 2 is SRS Error
32 retcode := 0;
33
34 select userenv('sessionid')
35 into l_session_id
36 from dual;
37
38 hr_utility.trace('Entering the print_report_address');
39
40 req_id:=fnd_profile.value('CONC_REQUEST_ID');
41 print_buffer:= 'N'; /*can be retrieved from fnd_profile.value('CONC_PRINT_TOGETHER'), if
42 set to 'Y' then will print the report only if all the processes are
43 complete */
44
45 select number_of_copies,
46 printer,
47 print_style,
48 save_output_flag
49 into copies_buffer,
50 printer_buffer,
51 style_buffer,
52 save_result
53 from fnd_concurrent_requests
54 where request_id = to_number(req_id);
55
56 if (save_result='Y') then
57 save_buffer:=true;
58 elsif (save_result='N') then
59 save_buffer:=false;
60 else
61 save_buffer:=NULL;
62 end if;
63
64 x := FND_REQUEST.set_print_options(
65 printer => printer_buffer,
66 style => style_buffer,
67 copies => copies_buffer,
68 save_output => save_buffer,
69 print_together => print_buffer);
70
71 l_rep_req_id := fnd_request.submit_request(application => 'PAY',
72 program => 'PYUSINRP');
73
74 IF(l_rep_req_id = 0) THEN
75 hr_utility.trace(' Error While Indiana Year Begin Address Report' );
76 hr_utility.raise_error;
77 ELSE
78 hr_utility.trace(' Concurrent Request Id (Report Spool Request) : '
79 ||to_char(l_rep_req_id));
80 END IF; /* if l_rep_req_id */
81
82 COMMIT;
83
84 /* Wait for report request completion */
85 hr_utility.trace('Waiting for the application to get completed ');
86
87 /* Check for Report Request Status */
88
89 l6_wait := fnd_concurrent.wait_for_request
90 (request_id => l_rep_req_id
91 ,interval => 1
92 ,phase => l6_phase
93 ,status => l6_status
94 ,dev_phase => l6_dev_phase
95 ,dev_status => l6_dev_status
96 ,message => l6_message);
97
98 hr_utility.trace('Wait completed,Printing output based on the result');
99
100 IF NOT (l6_dev_phase = 'COMPLETE' and l6_dev_status = 'NORMAL') THEN
101 hr_utility.trace('SQL Report - Indiana Address Exited with error') ;
102 retcode := 2;
103 ELSE
104 hr_utility.trace('SQL Report - Indiana Address Successful');
105 END IF; /* l6_dev_phase */
106
107
108 DELETE
109 FROM pay_us_rpt_totals
110 WHERE attribute20 = 'INDIANA_YEAR_BEGIN_ADDRESS'
111 AND organization_id = l_session_id;
112
113 COMMIT;
114
115 EXCEPTION
116 WHEN NO_DATA_FOUND THEN
117 hr_utility.trace('Exception : No data Found');
118 --
119 -- Set up error message and error return code.
120 --
121
122 errbuf := hr_utility.get_message;
123 retcode := 2;
124 WHEN OTHERS THEN
125 hr_utility.trace('Excption : When Others');
126 --
127 -- Set up error message and error return code.
128 --
129
130 errbuf := hr_utility.get_message;
131 retcode := 2;
132 END print_report_address;
133
134
135 PROCEDURE print_override_location(errbuf OUT NOCOPY VARCHAR2,
136 retcode OUT NOCOPY NUMBER,
137 p_business_group IN VARCHAR2,
138 p_curr_year IN VARCHAR2) IS
139
140 l6_wait BOOLEAN;
141 l6_phase VARCHAR2(30);
142 l6_status VARCHAR2(30);
143 l6_dev_phase VARCHAR2(30);
144 l6_dev_status VARCHAR2(30);
145 l6_message VARCHAR2(255);
146 l_rep_req_id NUMBER;
147 copies_buffer varchar2(80) := null;
148 print_buffer varchar2(80) := null;
149 printer_buffer varchar2(80) := null;
150 style_buffer varchar2(80) := null;
151 save_buffer boolean := null;
152 save_result varchar2(1) := null;
153 req_id VARCHAR2(80) := NULL; /* Request Id of the main request */
154 x BOOLEAN;
155 BEGIN
156 -- initialise variables - 0 is SRS Success, 1 is SRS Warning, 2 is SRS Error
157 retcode := 0;
158
159 hr_utility.trace('Entering the print_report_address');
160
161 req_id:=fnd_profile.value('CONC_REQUEST_ID');
162 print_buffer:= 'N'; /*can be retrieved from fnd_profile.value('CONC_PRINT_TOGETHER'), if
163 set to 'Y' then will print the report only if all the processes are
164 complete */
165
166 select number_of_copies,
167 printer,
168 print_style,
169 save_output_flag
170 into copies_buffer,
171 printer_buffer,
172 style_buffer,
173 save_result
174 from fnd_concurrent_requests
175 where request_id = to_number(req_id);
176
177 if (save_result='Y') then
178 save_buffer:=true;
179 elsif (save_result='N') then
180 save_buffer:=false;
181 else
182 save_buffer:=NULL;
183 end if;
184
185 x := FND_REQUEST.set_print_options(
186 printer => printer_buffer,
187 style => style_buffer,
188 copies => copies_buffer,
189 save_output => save_buffer,
190 print_together => print_buffer);
191
192 l_rep_req_id := fnd_request.submit_request(application => 'PAY',
193 program => 'pyusolrp',
194 argument1 => p_business_group,
195 argument2 => p_curr_year);
196
197 IF(l_rep_req_id = 0) THEN
198 hr_utility.trace(' Error While Indiana Year Begin Address Report' );
199 hr_utility.raise_error;
200 ELSE
201 hr_utility.trace(' Concurrent Request Id (Report Spool Request) : '
202 ||to_char(l_rep_req_id));
203 END IF; /* if l_rep_req_id */
204
205 COMMIT;
206
207 /* Wait for report request completion */
208 hr_utility.trace('Waiting for the application to get completed ');
209
210 /* Check for Report Request Status */
211
212 l6_wait := fnd_concurrent.wait_for_request
213 (request_id => l_rep_req_id
214 ,interval => 1
215 ,phase => l6_phase
216 ,status => l6_status
217 ,dev_phase => l6_dev_phase
218 ,dev_status => l6_dev_status
219 ,message => l6_message);
220
221 hr_utility.trace('Wait completed,Printing output based on the result');
222
223 IF NOT (l6_dev_phase = 'COMPLETE' and l6_dev_status = 'NORMAL') THEN
224 hr_utility.trace('SQL Report Override Location Exited with error') ;
225 retcode := 2;
226 ELSE
227 hr_utility.trace('SQL Report Override Loaction Successful');
228 END IF; /* l6_dev_phase */
229
230
231 EXCEPTION
232 WHEN NO_DATA_FOUND THEN
233 hr_utility.trace('Exception : No data Found');
234 --
235 -- Set up error message and error return code.
236 --
237
238 errbuf := hr_utility.get_message;
239 retcode := 2;
240 WHEN OTHERS THEN
241 hr_utility.trace('Exception : When Others');
242 --
243 -- Set up error message and error return code.
244 --
245
246 errbuf := hr_utility.get_message;
247 retcode := 2;
248 END print_override_location;
249
250
251
252 PROCEDURE get_insert_values ( p_proc_name VARCHAR2,
253 p_BUSINESS_GROUP_ID VARCHAR2,
254 p_person_id VARCHAR2,
255 p_curr_year VARCHAR2,
256 p_gre_name IN OUT NOCOPY VARCHAR2,
257 p_full_name IN OUT NOCOPY VARCHAR2,
258 p_employee_number IN OUT NOCOPY VARCHAR2)
259 IS
260
261 /* cursor to get the person's assignment detail */
262
263 CURSOR csr_person_details
264 IS
265 SELECT ppf.full_name,
266 ppf.employee_number
267 FROM
268 per_all_people_f ppf
269 WHERE
270 ppf.person_id = p_person_id
271 AND to_date('01-JAN-'||p_curr_year,'DD/MM/YYYY')
272 BETWEEN (ppf.effective_start_date + 1)
273 AND ppf.effective_end_date;
274
275 CURSOR cst_get_gre_name IS
276 SELECT hou.name
277 FROM hr_all_organization_units hou
278 WHERE hou.organization_id = hou.business_group_id
279 and hou.business_group_id = p_business_group_id ;
280
281 l_proc_name varchar2(50) := 'get_insert_value';
282
283 BEGIN
284 hr_utility.trace('In proc : get_insert_values');
285 -- hr_utility.trace('Person Id : ' || to_char(p_person_id));
286 --hr_utility.trace('Location Id : ' || to_char(p_location_id));
287
288 IF p_proc_name = 'INDIANA_YEAR_BEGIN_ADDRESS' THEN
289
290 hr_utility.trace('Entering : ' || l_proc_name);
291
292 /* get the detail of the person */
293
294 OPEN csr_person_details;
295
296 FETCH csr_person_details
297 INTO p_full_name,
298 p_employee_number;
299
300 IF csr_person_details%NOTFOUND THEN
301 hr_utility.trace('csr_person_details failed');
302 END IF;
303
304
305 CLOSE csr_person_details;
306
307
308 /* get the Business Group name for p_business_group_id*/
309
310 OPEN cst_get_gre_name;
311
312 FETCH cst_get_gre_name
313 INTO p_gre_name;
314
315 CLOSE cst_get_gre_name;
316
317 END IF;
318 hr_utility.trace('GRE NAME : ' || p_gre_name);
319 hr_utility.trace('Full NAME : ' || p_full_name);
320 hr_utility.trace('Employee Number: ' || p_employee_number);
321
322 hr_utility.trace('Leaving : ' || l_proc_name);
323 EXCEPTION
324 WHEN OTHERS THEN
325 hr_utility.trace('Leaving : ' || l_proc_name || ' With Erorr' );
326
327 END get_insert_values ;
328
329
330 /* Mapping of the Temp. table and value are as follows
331 tax_unit_name - GRE_NAME
332 full_name - Attribute1
333 Employee_no - ATTRUBITE2
334 effective_start_date- ATTRIBUTE3
335 town_or_city ATTRIBUTE4
336 region_1 ATTRIBUTE5
337 region_2 ATTRIBUTE6
338 postal_code ATTRIBUTE7
339 add_information17 ATTRIBUTE8
340 add_information18 ATTRIBUTE9
341 add_information19 ATTRIBUTE10
342 add_information20 ATTRIBUTE11
343 error ATTRIBUTE12
344 */
345
346 procedure put_into_temp_table(
347 p_tax_unit_name in varchar2,
348 p_emp_full_name in varchar2,
349 p_employee_number in varchar2,
350 p_effective_start_date in date,
351 p_town_or_city in varchar2,
352 p_region_1 in varchar2,
353 p_region_2 in varchar2,
354 p_postal_code in varchar2,
355 p_add_information17 in varchar2,
356 p_add_information18 in varchar2,
357 p_add_information19 in varchar2,
358 p_add_information20 in varchar2,
359 p_error in varchar2
360 )
361 IS
362 l_success_failure_indicator number;
363 l_proc_name varchar2(50) := 'put_into_temp_table';
364 l_session_id number;
365
366 BEGIN
367 hr_utility.trace('Entering :' || l_proc_name);
368
369 select userenv('sessionid')
370 into l_session_id
371 from dual;
372
373 INSERT
374 INTO pay_us_rpt_totals
375 (
376 organization_id,
377 gre_name,
378 attribute1,
379 attribute2,
380 attribute3,
381 attribute4,
382 attribute5,
383 attribute6,
384 attribute7,
385 attribute8,
386 attribute9,
387 attribute10,
388 attribute11,
389 attribute12,
390 attribute20
391 )
392 VALUES (
393 l_session_id,
394 p_tax_unit_name,
395 p_emp_full_name,
396 p_employee_number,
397 to_char(p_effective_start_date,'mm/dd/yyyy'),
398 p_town_or_city ,
399 p_region_1 ,
400 p_region_2 ,
401 p_postal_code ,
402 p_add_information17,
403 p_add_information18,
404 p_add_information19,
405 p_add_information20,
406 p_error,
407 'INDIANA_YEAR_BEGIN_ADDRESS');
408
409
410 hr_utility.trace('Leaving : ' || l_proc_name);
411 EXCEPTION
412
413 WHEN others THEN
414 hr_utility.trace('Error in the Instertion into temporary Table');
415 END put_into_temp_table;
416
417
418 procedure update_address(errbuf OUT NOCOPY VARCHAR2,
419 retcode OUT NOCOPY NUMBER,
420 p_business_group IN VARCHAR2,
421 p_curr_year IN VARCHAR2) is
422
423 l_ret_code number;
427 l_employee_number per_people_f.employee_number%type;
424 l_ret_text varchar2(1000);
425 l_gre_name hr_tax_units_v.name%type;
426 l_full_name per_people_f.full_name%type;
428 l_error varchar2(1000);
429 l_last_day DATE;
430 l_last_year VARCHAR2(4);
431
432
433 cursor csr_get_add is
434 SELECT *
435 FROM per_addresses pa
436 WHERE
437 pa.add_information17 = 'IN'
438 and pa.date_from < to_date('01-JAN-'||p_curr_year,'DD/MM/YYYY')
439 and pa.date_to is null
440 and pa.primary_flag = 'Y'
441 and pa.business_group_id = p_business_group;
442
443 l_add_rec csr_get_add%rowtype;
444 begin
445
446 -- hr_utility.trace_on(null,'oracle');
447 /* Get the addresses which has an tax override address of Indiana prior to
448 or as of 1-JAN-2001 */
449
450 l_last_year := p_curr_year - 1;
451 l_last_day := to_date('31-DEC-'||l_last_year,'DD/MM/YYYY');
452
453 open csr_get_add ;
454
455 hr_utility.trace('Updating the Per_Addresses for the esisting records...');
456
457 loop
458
459 fetch csr_get_add into l_add_rec;
460
461 exit when csr_get_add%NOTFOUND;
462
463 /* End date the address record as of /12/31/2000 */
464
465
466 update PER_ADDRESSES
467 set date_to = l_last_day
468 where address_id = l_add_rec.address_id;
469
470 hr_utility.trace('Updated Addresses : ' || to_char(l_add_rec.address_id));
471
472 insert into PER_ADDRESSES
473 (ADDRESS_ID,
474 BUSINESS_GROUP_ID,
475 PERSON_ID,
476 DATE_FROM,
477 PRIMARY_FLAG,
478 STYLE,
479 ADDRESS_LINE1,
480 ADDRESS_LINE2,
481 ADDRESS_LINE3,
482 ADDRESS_TYPE,
483 COMMENTS,
484 COUNTRY,
485 DATE_TO,
486 POSTAL_CODE,
487 REGION_1,
488 REGION_2,
489 REGION_3,
490 TELEPHONE_NUMBER_1,
491 TELEPHONE_NUMBER_2,
492 TELEPHONE_NUMBER_3,
493 TOWN_OR_CITY,
494 REQUEST_ID,
495 PROGRAM_APPLICATION_ID,
496 PROGRAM_ID,
497 PROGRAM_UPDATE_DATE,
498 ADDR_ATTRIBUTE_CATEGORY,
499 ADDR_ATTRIBUTE1,
500 ADDR_ATTRIBUTE2,
501 ADDR_ATTRIBUTE3,
502 ADDR_ATTRIBUTE4,
503 ADDR_ATTRIBUTE5,
504 ADDR_ATTRIBUTE6,
505 ADDR_ATTRIBUTE7,
506 ADDR_ATTRIBUTE8,
507 ADDR_ATTRIBUTE9,
508 ADDR_ATTRIBUTE10,
509 ADDR_ATTRIBUTE11,
510 ADDR_ATTRIBUTE12,
511 ADDR_ATTRIBUTE13,
512 ADDR_ATTRIBUTE14,
513 ADDR_ATTRIBUTE15,
514 ADDR_ATTRIBUTE16,
515 ADDR_ATTRIBUTE17,
516 ADDR_ATTRIBUTE18,
517 ADDR_ATTRIBUTE19,
518 ADDR_ATTRIBUTE20,
519 LAST_UPDATE_DATE,
520 LAST_UPDATED_BY,
521 LAST_UPDATE_LOGIN,
522 CREATED_BY,
523 CREATION_DATE,
524 OBJECT_VERSION_NUMBER,
525 ADD_INFORMATION17,
526 ADD_INFORMATION18,
527 ADD_INFORMATION19,
528 ADD_INFORMATION20,
529 ADD_INFORMATION13,
530 ADD_INFORMATION14,
531 ADD_INFORMATION15,
532 ADD_INFORMATION16
533 )
534 values
535 (per_addresses_s.NEXTVAL,
536 l_add_rec.BUSINESS_GROUP_ID,
537 l_add_rec.PERSON_ID,
538 to_date('01-JAN-'||p_curr_year,'DD/MM/YYYY'),
539 --to_date('01-JAN-2001', 'DD-MON-YYYY'),
540 l_add_rec.PRIMARY_FLAG,
541 l_add_rec.STYLE,
542 l_add_rec.ADDRESS_LINE1,
543 l_add_rec.ADDRESS_LINE2,
544 l_add_rec.ADDRESS_LINE3,
545 l_add_rec.ADDRESS_TYPE,
546 l_add_rec.COMMENTS,
547 l_add_rec.COUNTRY,
548 NULL,
549 l_add_rec.POSTAL_CODE,
550 l_add_rec.REGION_1,
551 l_add_rec.REGION_2,
552 l_add_rec.REGION_3,
553 l_add_rec.TELEPHONE_NUMBER_1,
554 l_add_rec.TELEPHONE_NUMBER_2,
555 l_add_rec.TELEPHONE_NUMBER_3,
556 l_add_rec.TOWN_OR_CITY,
557 l_add_rec.REQUEST_ID,
558 l_add_rec.PROGRAM_APPLICATION_ID,
559 l_add_rec.PROGRAM_ID,
560 l_add_rec.PROGRAM_UPDATE_DATE,
561 l_add_rec.ADDR_ATTRIBUTE_CATEGORY,
562 l_add_rec.ADDR_ATTRIBUTE1,
563 l_add_rec.ADDR_ATTRIBUTE2,
564 l_add_rec.ADDR_ATTRIBUTE3,
565 l_add_rec.ADDR_ATTRIBUTE4,
566 l_add_rec.ADDR_ATTRIBUTE5,
567 l_add_rec.ADDR_ATTRIBUTE6,
568 l_add_rec.ADDR_ATTRIBUTE7,
569 l_add_rec.ADDR_ATTRIBUTE8,
570 l_add_rec.ADDR_ATTRIBUTE9,
571 l_add_rec.ADDR_ATTRIBUTE10,
572 l_add_rec.ADDR_ATTRIBUTE11,
573 l_add_rec.ADDR_ATTRIBUTE12,
574 l_add_rec.ADDR_ATTRIBUTE13,
575 l_add_rec.ADDR_ATTRIBUTE14,
576 l_add_rec.ADDR_ATTRIBUTE15,
577 l_add_rec.ADDR_ATTRIBUTE16,
578 l_add_rec.ADDR_ATTRIBUTE17,
579 l_add_rec.ADDR_ATTRIBUTE18,
583 l_add_rec.LAST_UPDATED_BY,
580 l_add_rec.ADDR_ATTRIBUTE19,
581 l_add_rec.ADDR_ATTRIBUTE20,
582 l_add_rec.LAST_UPDATE_DATE,
584 l_add_rec.LAST_UPDATE_LOGIN,
585 l_add_rec.CREATED_BY,
586 l_add_rec.CREATION_DATE,
587 l_add_rec.OBJECT_VERSION_NUMBER,
588 NULL,
589 NULL,
590 NULL,
591 NULL,
592 l_add_rec.ADD_INFORMATION13,
593 l_add_rec.ADD_INFORMATION14,
594 l_add_rec.ADD_INFORMATION15,
595 l_add_rec.ADD_INFORMATION16
596 ) ;
597
598 -- hr_utility.trace('Updated Addresses : ' ||
599 -- to_char(l_add_rec.address_id));
600
601 get_insert_values(
602 'INDIANA_YEAR_BEGIN_ADDRESS',
603 l_add_rec.BUSINESS_GROUP_ID,
604 l_add_rec.PERSON_ID,
605 p_curr_year,
606 l_gre_name,
607 l_full_name,
608 l_employee_number
609 );
610
611 put_into_temp_table(p_tax_unit_name => l_gre_name ,
612 p_emp_full_name => l_full_name ,
613 p_employee_number => l_employee_number,
614 p_effective_start_date => l_add_rec.DATE_FROM ,
615 p_town_or_city => l_add_rec.town_or_city,
616 p_region_1 => l_add_rec.region_1,
617 p_region_2 => l_add_rec.region_2,
618 p_postal_code => l_add_rec.postal_code,
619 p_add_information17 =>l_add_rec.add_information17,
620 p_add_information18 =>l_add_rec.add_information18,
621 p_add_information19 =>l_add_rec.add_information19,
622 p_add_information20 =>l_add_rec.add_information20,
623 p_error => l_error);
624 COMMIT;
625 end loop;
626 close csr_get_add;
627 /* Print the Indiana Address Report ' */
628
629 print_report_address(errbuf, retcode);
630
631 /* print_override_location(errbuf, retcode, p_curr_year, p_business_group); */
632
633 print_override_location(errbuf, retcode, p_business_group,p_curr_year);
634
635 end;
636 end pay_us_indiana;