1 PACKAGE BODY PAY_US_YEPP_ADD_ACTIONS_PKG AS
2 /* $Header: pyusyeaa.pkb 120.3 2006/08/30 00:11:18 sodhingr noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_yepp_add_actions_pkg
21
22 Description : Package used to report the Employees which are not
23 picked up by the Year End Process and mark them for
24 retry. It is used by the concurrent request -
25 'Add Assignment Actions To The Year End Pre-Process'
26
27
28 Change List
29 -----------
30 Date Name Vers Bug No Description
31 ----------- ---------- ------ ------- --------------------------
32 01-Sep-2003 kaverma 115.0 2222748 Created.
33 03-Sep-2003 kaverma 115.1 2222748 Modified cursor c_w2_magtape_run_exists
34 to check run of Federal Magtapes
35 12-Sep-2003 kaverma 115.2 3137858 Correct the layout and insertion of
36 person_id when creating actions.
37 20-Nov-2003 sdahiya 115.3 3263078 Modified cursor c_w2_magtape_run_exists
38 to correctly identify whether state 1099R/W2 magtape
39 processes have been run for GREs in a given
40 business group. Removed tax_unit_id parameter
41 and modified statement opening this cursor.
42 21-Nov-2003 sdahiya 115.4 3263078 The cursor c_w2_magtape_run_exists is modified to
43 check existence of 1099R magtape runs for 1099R GRE and
44 W2 magtape runs for W2 GRE.
45 12-Dec-2003 kaverma 115.5 3228332 Report should not pick up rehired employee
46 if terminated employee is alreday archived in YEPP.
47 modified c_get_latest_asg cursor.
48 13-Dec-2003 sodhingr 115.6 3228332 changed the cursor c_get_latest_asg to check for
49 assignment_type = 'E' and also added the condition
50 to check if an assignment action is already created for the
51 same person
52 20-Aug-2004 meshah 115.7 3440806 changed the following
53 cursor c_get_latest_asg,
54 cursor c_get_processed_assignments,
55 PROCEDURE
56 report_secondary_assignments and
57 added CURSOR c_get_asg_id
58 26-Aug-2004 meshah 115.8 fixed gscc error.
59 01-Sep-2004 meshah 115.9 disabled the index on
60 pay_action_classification
61 in cursor c_get_latest_asg
62 04-Nov-2004 meshah 115.10 3984539 changed the sequence of
63 get_eligible_assignments and
64 get_processed_assignments in the
65 Main of add_actions_to_yepp.
66 commented the cursor
67 get_already_marked_assignments.
68 changed the date join conditions
69 for CURSOR c_get_asg_id.
70 18-Apr-2006 alikhar 115.12 5120818 Performance fix for cursor c_get_latest_asg.
71 Added Ordered hint.
72 25-Aug-2006 saurgupt 115.13 3829668 Added the procedure create_archive to insert record into
73 ff_archive_items while creating assignment actions.
74 29-AUG-2006 sodhingr 115.14 3829668 archive A_ADD_ARCHIVE= Y when an assigment
75 is added to archive
76 ********************************************************************/
77
78
79 /********************************************************************
80 ** Local Package Variables
81 ********************************************************************/
82 gv_title VARCHAR2(100) := 'Add Assignment Actions Report';
83 gv_package_name VARCHAR2(50) := 'pay_us_yepp_add_actions_pkg';
84 gv_sec_asg_reported VARCHAR2(1) := 'N';
85
86
87 /********************************************************************
88 Function to display the Titles of the columns of the employee details
89 ********************************************************************/
90
91 FUNCTION formated_header_string(
92 p_output_file_type in varchar2
93 )RETURN varchar2
94 IS
95
96 lv_format1 varchar2(32000);
97
98 BEGIN
99
100 hr_utility.set_location(gv_package_name || '.formated_header_string', 10);
101 lv_format1 :=
102 pay_us_payroll_utils.formated_data_string
103 (p_input_string => 'Year '
104 ,p_bold => 'Y'
105 ,p_output_file_type => p_output_file_type) ||
106 pay_us_payroll_utils.formated_data_string
107 (p_input_string => 'GRE '
108 ,p_bold => 'Y'
109 ,p_output_file_type => p_output_file_type) ||
110 pay_us_payroll_utils.formated_data_string
111 (p_input_string => 'Employee Name '
112 ,p_bold => 'Y'
113 ,p_output_file_type => p_output_file_type) ||
114 pay_us_payroll_utils.formated_data_string
115 (p_input_string => 'Employee SS # '
116 ,p_bold => 'Y'
117 ,p_output_file_type => p_output_file_type) ||
118 pay_us_payroll_utils.formated_data_string
119 (p_input_string => 'Employee #'
120 ,p_bold => 'Y'
121 ,p_output_file_type => p_output_file_type) ;
122
123 hr_utility.trace('Static Label1 = ' || lv_format1);
124
125 hr_utility.set_location(gv_package_name || '.formated_header_string', 40);
126 return lv_format1 ;
127
128 EXCEPTION
129 WHEN OTHERS THEN
130 hr_utility.trace('Error in gv_package_name' || '.formated_header_string');
131 RAISE;
132
133 END formated_header_string;
134
135
136
137 /********************************************************************
138 Function to display the details of the selected employee
139 ********************************************************************/
140
141 FUNCTION formated_detail_string(
142 p_output_file_type in varchar2
143 ,p_year varchar2
144 ,p_gre varchar2
145 ,p_Employee_name varchar2
146 ,p_employee_ssn varchar2
147 ,p_emplyee_number varchar2
148
149 ) RETURN varchar2
150 IS
151
152 lv_format1 varchar2(22000);
153
154 BEGIN
155
156 hr_utility.set_location(gv_package_name || '.formated_detail_string', 10);
157 lv_format1 :=
158 pay_us_payroll_utils.formated_data_string
159 (p_input_string => p_year
160 ,p_bold => 'N'
161 ,p_output_file_type => p_output_file_type) ||
162 pay_us_payroll_utils.formated_data_string
163 (p_input_string => p_gre
164 ,p_bold => 'N'
165 ,p_output_file_type => p_output_file_type) ||
166 pay_us_payroll_utils.formated_data_string
167 (p_input_string => p_employee_name
168 ,p_bold => 'N'
169 ,p_output_file_type => p_output_file_type) ||
170 pay_us_payroll_utils.formated_data_string
171 (p_input_string => P_employee_ssn
172 ,p_bold => 'N'
173 ,p_output_file_type => p_output_file_type) ||
174 pay_us_payroll_utils.formated_data_string
175 (p_input_string => p_emplyee_number
176 ,p_bold => 'N'
177 ,p_output_file_type => p_output_file_type);
178
179 hr_utility.set_location(gv_package_name || '.formated_detail_string', 20);
180 hr_utility.trace('Static Label1 = ' || lv_format1);
181 hr_utility.set_location(gv_package_name || '.formated_detail_string', 30);
182
183 return lv_format1;
184
185 EXCEPTION
186 WHEN OTHERS THEN
187 hr_utility.trace('Error in '|| gv_package_name || '.formated_detail_string');
188 RAISE;
189
190 END formated_detail_string;
191
192
193
194 /********************************************************************
195 Procedure to display message if no employees are selected for
196 any of the four sections -
197 - Processed Assignments
198 - Eligible Assignments
199 - Not Eligible Assignments
200 - Secondary Assignments
201 ********************************************************************/
202
203 PROCEDURE formated_zero_count(output_file_type varchar2,
204 p_flag varchar2)
205 IS
206 lvc_message1 varchar2(200);
207 lvc_message2 varchar2(200);
208 lvc_message3 varchar2(200);
209
210 lvc_return_message varchar2(400);
211 BEGIN
212
213 hr_utility.set_location(gv_package_name || '.formated_zero_count', 10);
214
215 lvc_message1 := '1. No employee from assignment set is already processed by'
216 || ' Year End Pre-Process.';
217 lvc_message2 := '2. No employee from assignment set is eligible for Year'
218 || ' End Pre-Prcocess.';
219 lvc_message3 := '3. Following employees are not eligible for Year End Pre-'
220 ||'Process: None';
221
222 if output_file_type = 'HTML' then
223 lvc_message1 := '<H4> '||lvc_message1||' </H4>';
224 lvc_message2 := '<H4> '||lvc_message2||' </H4>';
225 lvc_message3 := '<H4> '||lvc_message3||' </H4>';
226 end if;
227
228 if p_flag='PROCESSED' then
229 fnd_file.put_line(fnd_file.output,lvc_message1);
230 end if;
231
232 hr_utility.set_location(gv_package_name || '.formated_zero_count', 20);
233
234 if p_flag='ELGBLE' then
235 fnd_file.put_line(fnd_file.output, lvc_message2);
236 end if;
237
238 hr_utility.set_location(gv_package_name || '.formated_zero_count', 30);
239
240 if p_flag='NOTELGBLE' then
241 fnd_file.put_line(fnd_file.output, lvc_message3);
242 end if;
243
244 hr_utility.set_location(gv_package_name || '.formated_zero_count', 40);
245
246 EXCEPTION
247 WHEN OTHERS THEN
248 hr_utility.trace('Error in '|| gv_package_name || '.formated_zero_count');
249 RAISE;
250
251 END formated_zero_count;
252
253
254
255 /********************************************************************
256 Procedure to print the table in HTML format
257 ********************************************************************/
258
259 PROCEDURE print_table_header (p_header_text in varchar2,
260 p_output_file_type in varchar2)
261 IS
262 l_header_text varchar2(200);
263 BEGIN
264 hr_utility.set_location(gv_package_name || '.print_table_header', 10);
265 l_header_text := p_header_text ;
266
267 if p_output_file_type = 'HTML' then
268 l_header_text := '<H4> '||l_header_text||' </H4>';
269 end if;
270
271 fnd_file.put_line(fnd_file.output,l_header_text);
272
273 if p_output_file_type ='HTML' then
274 fnd_file.put_line(fnd_file.output, '<table border=1 align=center>');
275 fnd_file.put_line(fnd_file.output, '<tr>');
276 end if;
277
278 hr_utility.set_location(gv_package_name || '.print_table_header', 20);
279
280 fnd_file.put_line(fnd_file.output,formated_header_string(p_output_file_type));
281
282 if p_output_file_type ='HTML' then
283 fnd_file.put_line(fnd_file.output, '</tr>');
284 end if;
285
286 hr_utility.set_location(gv_package_name || '.print_table_header', 30);
287
288 EXCEPTION
289 WHEN OTHERS THEN
290 hr_utility.trace('Error in '|| gv_package_name || '.print_table_header');
291 RAISE;
292 END print_table_header;
293
294
295
296 /********************************************************************
297 Name : bal_db_item
298 Purpose : Given the name of a balance DB item as would be seen in a
299 fast formula it returns the defined_balance_id of the
300 balance it represents.
301 Notes : A defined balance_id is required by the PLSQL balance
302 function.
303 /*******************************************************************/
304
305 FUNCTION bal_db_item (p_db_item_name varchar2)
306 RETURN number
307 IS
308
309 /* Get the defined_balance_id for the specified balance DB item. */
310
311 cursor csr_defined_balance
312 is
313 select to_number(ue.creator_id)
314 from ff_user_entities ue,
315 ff_database_items di
316 where di.user_name = p_db_item_name
317 and ue.user_entity_id = di.user_entity_id
318 and ue.creator_type = 'B'
319 and ue.legislation_code = 'US';
320
321 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
322
323 BEGIN
324 hr_utility.set_location(gv_package_name || '.bal_db_item', 10);
325 hr_utility.trace('p_db_item_name is '||p_db_item_name);
326
327 open csr_defined_balance;
328 fetch csr_defined_balance into l_defined_balance_id;
329
330 if csr_defined_balance%notfound then
331 close csr_defined_balance;
332 raise hr_utility.hr_error;
333 else
334 close csr_defined_balance;
335 end if;
336
337 hr_utility.trace('l_defined_balance_id is '||to_char(l_defined_balance_id));
338 hr_utility.set_location(gv_package_name || '.bal_db_item', 20);
339
340 return (l_defined_balance_id);
341
342 EXCEPTION
343 WHEN OTHERS THEN
344 hr_utility.trace('Error in '|| gv_package_name || '.bal_db_item');
345 RAISE;
346
347 END bal_db_item;
348
349
350
351 /********************************************************************
352 Main procedure called from the concurrent program.
353 Name: add_actions_to_yepp
354
355 Description: The input parameters for the procedure are Date,GRE_ID,
356 Assignment Set and output file type fromthe concurrent
357 program. The procedure identifies the eligible/processed
358 /not eligible and secondary assignments from the
359 Assignment set and report them as the output in the
360 specified format.
361
362 ********************************************************************/
363
364 PROCEDURE add_actions_to_yepp(errbuf out nocopy varchar2,
365 retcode out nocopy number,
366 p_effective_date in varchar2,
367 p_gre_id in number,
368 p_assign_set in number,
369 p_output_file_type in varchar2)
370
371 IS
372
373 --Cursor to check if there is a W2 Mag Tape run for the business group and
374 --if there are actions which have been picked up for the GRE for which
375 --this process is run in the mag tape run
376
377 /*---- Cursor modified as per bug 3263078. Removed join with pay_assignment_actions. ----------*/
378 -- This cursor checks existence of 1099R magtape runs for 1099R GRE and W2 magtape runs for W2 GRE.
379 cursor c_w2_magtape_run_exists(cp_effective_date date,
380 cp_business_group_id number,
381 cp_gre_type varchar2)
382 is
383 select 1 from dual
384 where exists (
385 select 1
386 from pay_payroll_actions ppa
387 where ppa.business_group_id = cp_business_group_id
388 and ppa.action_type = 'X'
389 and ppa.report_type = cp_gre_type
390 and ppa.report_category in ('RM', 'RT')
391 and ppa.effective_date + 0 = add_months(cp_effective_date,12) - 1
392 and ppa.action_status = 'C'
393 ) ;
394 /*---------------------------------------------------------------------------------------------*/
395 -- Cursor to get the type of GRE (W2/1099R)
396 cursor c_gre_type(cp_tax_unit_id number)
397 is
398 select decode(org_information_context,'1099R Magnetic Report Rules','1099R','W2') gre_type
399 from hr_organization_information
400 where organization_id = cp_tax_unit_id
401 and org_information_context in ('1099R Magnetic Report Rules','W2 Reporting Rules');
402
403
404
405 -- Cursor to get the GRE Name
406 cursor c_gre_name(cp_tax_unit_id number)
407 is
408 select name,business_group_id
409 from hr_organization_units
410 where organization_id = cp_tax_unit_id;
411
412 -- Cursor to get person_id of the assignments selected
413 cursor c_person_id (cp_assign_id number)
414 is
415 select person_id
416 from per_assignments_f
417 where assignment_id=cp_assign_id;
418
419 -- Cursor to get Employee details
420 cursor c_employee_details (cp_person_id number )
421 is
422 select employee_number,full_name,national_identifier
423 from per_people_f
424 where person_id = cp_person_id;
425
426 lv_result_value number:=0;
427 lv_person_id per_all_people_f.person_id%type;
428 lv_gre_name hr_organization_units.name%type;
429 lv_gre_type varchar2(10);
430 lv_emp_name per_people_f.full_name%type;
431 lv_emp_no per_people_f.employee_number%type;
432 lv_emp_ssn per_people_f.national_identifier%type;
433 lv_data_row varchar2(4000);
434 l_assignment_inserted number :=0;
435 l_effective_date date;
436 l_temp number;
437 lv_business_group_id hr_organization_units.business_group_id%type;
438
439 l_elgbl_table_header varchar2(200):= '2. Following employees are eligible for the Year End '||
440 'Pre-Process archive and marked for Retry:';
441 l_prced_table_header varchar2(200):= '1. Following employees are already processed by the '||
442 'Year End Pre-Process:';
443 l_nonelgbl_table_header varchar2(200):= '3. Following employees are not eligible for the Year '||
444 'End Pre-Process:';
445 l_secasg_table_header varchar2(200):= 'Following employees have secondary assignment included '||
446 'in the assignment set:';
447 l_othasg_table_header varchar2(200):= 'Following employees are included in the assignment set '||
448 'but belong to different GRE: ';
449
450 -- Bug 3829668
451 /******************************************************************
452 Procedure create_archive
453 Description : Creates an entry into ff_archive_items for user_entity A_W2_CORRECTED. This is needed to
454 print/noprint 'CORRECTED' on Online Employee W2.
455 ******************************************************************/
456 PROCEDURE create_archive(cp_asg_action_id in number,
457 cp_gre_id in number)
458 IS
459 Cursor c_get_dbi_id(cp_dbi_name in varchar2) is
460 select fdi.user_entity_id
461 from ff_database_items fdi,
462 ff_user_entities fue
463 where fdi.user_name = cp_dbi_name
464 and fue.user_entity_id = fdi.user_entity_id
465 and fue.legislation_code = 'US';
466
467 l_user_entity_id number;
468 l_context_id number;
469 BEGIN
470
471 hr_utility.set_location(gv_package_name || '.create_archive', 10);
472 select context_id
473 into l_context_id
474 from ff_contexts
475 where context_name = 'TAX_UNIT_ID';
476
477 open c_get_dbi_id('A_ADD_ARCHIVE');
478 fetch c_get_dbi_id into l_user_entity_id;
479 if c_get_dbi_id%notfound then
480 raise_application_error(-20001,'Error getting user_entity_id for DBI : '
481 ||'A_ADD_ARCHIVE'||' - '||to_char(sqlcode) || '-' || sqlerrm);
482 end if;
483 close c_get_dbi_id;
484
485 hr_utility.set_location(gv_package_name || '.create_archive', 20);
486
487 -- Inserting into ff_archive_items
488
489 insert into ff_archive_items
490 (ARCHIVE_ITEM_ID,
491 USER_ENTITY_ID,
492 CONTEXT1,
493 VALUE)
494 values
495 (ff_archive_items_s.nextval,
496 l_user_entity_id,
497 cp_asg_action_id,
498 'Y');
499
500 -- Inserting into ff_archive_item_contexts
501
502 insert into ff_archive_item_contexts
503 (ARCHIVE_ITEM_ID,
504 SEQUENCE_NO,
505 CONTEXT,
506 CONTEXT_ID)
507 values
508 (ff_archive_items_s.currval,
509 1,
510 cp_gre_id,
511 l_context_id);
512 hr_utility.set_location(gv_package_name || '.create_archive', 30);
513 end create_archive;
514 /******************************************************************
515 Procedure get_eligible_assignments
516 Description : Gets the list of all primary assignments eligible for
517 the archive by year end process and mark them for retry.
518 ******************************************************************/
519
520 PROCEDURE get_eligible_assignments(p_effective_date in date,
521 p_gre_id in number,
522 p_assignment_set_id in number
523 )
524 IS
525 -- Curosr to get latest assignment action for the primary assignments
526 -- from the assignment set of the payroll process in the given year
527 -- and gre
528
529 cursor c_get_latest_asg(cp_effective_date date,
530 cp_gre_id number,
531 cp_assign_set_id number)
532 is
533 select /*+ ORDERED */max(paa.assignment_action_id),
534 paf1.assignment_id,
535 paf.person_id
536 from hr_assignment_set_amendments has,
537 per_assignments_f paf,
538 per_assignments_f paf1,
539 pay_assignment_actions paa,
540 pay_payroll_actions ppa,
541 pay_action_classifications pac
542 where has.assignment_set_id = cp_assign_set_id
543 and has.include_or_exclude = 'I'
544 and paf.assignment_id = has.assignment_id
545 and paf.assignment_type = 'E'
546 and paf.person_id = paf1.person_id
547 /* we cannot check for primary assignment. Bug 3440806 */
548 -- and paf.primary_flag = 'Y'
549 -- and paa.assignment_id = has.assignment_id
550 and paa.assignment_id = paf1.assignment_id
551 and paa.tax_unit_id = cp_gre_id
552 and paa.payroll_action_id = ppa.payroll_action_id
553 and ppa.action_type = pac.action_type
554 and pac.classification_name||'' = 'SEQUENCED'
555 and ppa.effective_date between paf.effective_start_date
556 and paf.effective_end_date
557 and ppa.effective_date between paf1.effective_start_date
558 and paf1.effective_end_date
559 and ppa.effective_date between cp_effective_date
560 and add_months(cp_effective_date, 12) - 1
561 and ((nvl(paa.run_type_id, ppa.run_type_id) is null
562 and paa.source_action_id is null)
563 or (nvl(paa.run_type_id, ppa.run_type_id) is not null
564 and paa.source_action_id is not null )
565 or (ppa.action_type = 'V' and ppa.run_type_id is null
566 and paa.run_type_id is not null
567 and paa.source_action_id is null))
568 and not exists( SELECT 1
569 FROM pay_payroll_actions ppa1, -- Year End
570 pay_assignment_actions paa1 -- Year End
571 WHERE ppa1.report_type = 'YREND'
572 AND ppa1.action_status = 'C'
573 AND ppa1.effective_date = add_months(cp_effective_date, 12) - 1
574 AND to_number(substr(legislative_parameters,
575 instr(legislative_parameters,'TRANSFER_GRE=') +
576 length('TRANSFER_GRE='))) = cp_gre_id -- Bug 3228332
577 AND ppa1.payroll_action_id = paa1.payroll_action_id
578 /* we should be checking for existance, irrespective of the action type. If we check for
579 action status of C and M then the ones marked for retry will be selected and duplicate
580 actions will be created */
581 -- AND (paa1.action_status = 'C' or paa1.action_status = 'M')
582 AND paa1.serial_number = to_char(paf.person_id)) -- Bug 3228332
583 group by paf1.assignment_id,paf.person_id
584 order by paf1.assignment_id desc;
585
586
587 -- Cursor to get details of payroll action of the Year End Pre-Process
588 cursor get_yepp_payroll_action(cp_effective_date date,
589 cp_gre_id number)
590 is
591 select payroll_action_id
592 from pay_payroll_actions
593 where action_type = 'X'
594 and action_status = 'C'
595 and report_type = 'YREND'
596 and pay_core_utils.get_parameter('TRANSFER_GRE',legislative_parameters) = cp_gre_id
597 and effective_date = add_months(cp_effective_date, 12) - 1;
598
599
600 -- Cursor to get already marked for retry assignment for the Year End Process
601 cursor get_already_marked_assignments(cp_payroll_action_id number,
602 cp_assignment_set_id number,
603 cp_gre_id number)
604 is
605 select paa.assignment_id,
606 paa.assignment_action_id
607 from pay_assignment_actions paa,
608 hr_assignment_set_amendments has
609 where paa.payroll_action_id = cp_payroll_action_id
610 and paa.action_status = 'M'
611 and paa.tax_unit_id = cp_gre_id
612 and has.assignment_set_id = cp_assignment_set_id
613 and paa.assignment_id = has.assignment_id
614 and nvl(has.include_or_exclude,'I') = 'I';
615
616 l_bal_aaid pay_assignment_actions.assignment_action_id%type;
617 l_assignment_id per_all_assignments_f.assignment_id%type;
618 l_person_id per_all_assignments_f.person_id%type;
619 l_yepp_payroll_action_id pay_payroll_actions.payroll_action_id%type;
620 l_value number;
621 lockingactid pay_assignment_actions.assignment_action_id%type;
622 l_prev_person_id per_all_assignments_f.person_id%type; -- bug 3315082
623
624 /* we should always be stamping the primary assignment_id, even if the
625 assignment selected in the assignment set is secondary */
626
627 /* Get the primary assignment for the given person_id */
628
629 CURSOR c_get_asg_id (p_person_id number) IS
630 SELECT assignment_id
631 from per_all_assignments_f paf
632 where person_id = p_person_id
633 and primary_flag = 'Y'
634 and assignment_type = 'E'
635 and paf.effective_start_date <= add_months(p_effective_date, 12) - 1
636 and paf.effective_end_date >= p_effective_date
637 ORDER BY assignment_id desc;
638
639 BEGIN
640
641 hr_utility.set_location(gv_package_name || '.get_eligible_assignments', 10);
642
643 -- Get the Payroll Action of Year End Archive Pre-Process Run
644 -- For given GRE and Year.
645
646 open get_yepp_payroll_action(p_effective_date,
647 p_gre_id);
648 fetch get_yepp_payroll_action into l_yepp_payroll_action_id;
649 close get_yepp_payroll_action ;
650
651
652 hr_utility.set_location(gv_package_name || '.get_eligible_assignments', 20);
653
654 /* Set up the context of tax unit id */
655 pay_balance_pkg.set_context('TAX_UNIT_ID',p_gre_id);
656
657 hr_utility.set_location(gv_package_name || '.get_eligible_assignments', 30);
658
659
660 -- Get the latest assignment actions of all the primary assignments from the
661 -- assignment set
662
663 open c_get_latest_asg(p_effective_date,
664 p_gre_id,
665 p_assignment_set_id
666 );
667 LOOP
668 fetch c_get_latest_asg into l_bal_aaid, l_assignment_id, l_person_id;
669 exit when c_get_latest_asg%NOTFOUND;
670 hr_utility.set_location(gv_package_name || '.get_eligible_assignments', 40);
671
672 if (l_prev_person_id <> l_person_id) or
673 (l_prev_person_id is null) then -- bug 3315082
674 l_prev_person_id := l_person_id;
675 if l_bal_aaid <> -9999 then /* Assignment action in year */
676
677 hr_utility.trace('bal_aaid = '||to_char(l_bal_aaid));
678 hr_utility.trace('defined_balance_id = '||to_char(bal_db_item('GROSS_EARNINGS_PER_GRE_YTD')));
679 hr_utility.set_location(gv_package_name || '.get_eligible_assignments', 50);
680 l_value := nvl(pay_balance_pkg.get_value
681 (p_defined_balance_id => bal_db_item('GROSS_EARNINGS_PER_GRE_YTD'),
682 p_assignment_action_id => l_bal_aaid),0);
683
684 if l_value = 0 then
685 hr_utility.trace('bal_aaid = '||to_char(l_bal_aaid));
686 hr_utility.trace('defined_balance_id = '||to_char(bal_db_item('W2_NONTAX_SICK_PER_GRE_YTD')));
687 hr_utility.set_location(gv_package_name || '.get_eligible_assignments', 60);
688 l_value := nvl(pay_balance_pkg.get_value
689 (p_defined_balance_id => bal_db_item('W2_NONTAX_SICK_PER_GRE_YTD'),
690 p_assignment_action_id => l_bal_aaid),0);
691
692 if l_value = 0 then
693 hr_utility.trace('bal_aaid = '||to_char(l_bal_aaid));
694 hr_utility.trace('defined_balance_id = '||to_char(bal_db_item('W2_EXPENSE_REIMB_PER_GRE_YTD')));
695 hr_utility.set_location(gv_package_name || '.get_eligible_assignments', 70);
696 l_value := nvl(pay_balance_pkg.get_value
697 (p_defined_balance_id => bal_db_item('W2_EXPENSE_REIMB_PER_GRE_YTD'),
698 p_assignment_action_id => l_bal_aaid),0);
699
700 if l_value = 0 then
701 hr_utility.trace('bal_aaid = '||to_char(l_bal_aaid));
702 hr_utility.trace('defined_balance_id = '||to_char(bal_db_item('W2_QUAL_MOVE_PER_GRE_YTD')));
703 hr_utility.set_location(gv_package_name || '.get_eligible_assignments', 80);
704 l_value := nvl(pay_balance_pkg.get_value
705 (p_defined_balance_id => bal_db_item('W2_QUAL_MOVE_PER_GRE_YTD'),
706 p_assignment_action_id => l_bal_aaid),0);
707 if l_value = 0 then
708 hr_utility.trace('bal_aaid = '||to_char(l_bal_aaid));
709 hr_utility.trace('defined_balance_id = '||to_char(bal_db_item('W2_NO_GROSS_EARNINGS_PER_GRE_YTD')));
710 hr_utility.set_location(gv_package_name || '.get_eligible_assignments', 90);
711 l_value := nvl(pay_balance_pkg.get_value
712 (p_defined_balance_id => bal_db_item('W2_NO_GROSS_EARNINGS_PER_GRE_YTD'),
713 p_assignment_action_id => l_bal_aaid),0);
714 end if;
715 end if;
716 end if;
717 end if;
718
719 -- Check if the assignment has got a value for any of the above five balances
720 hr_utility.set_location(gv_package_name || '.get_eligible_assignments', 100);
721 if l_value <> 0 then
722
723 /* Create the assignment action to represnt the person / tax unit
724 combination. */
725 hr_utility.set_location(gv_package_name || '.get_eligible_assignments', 110);
726
727 select pay_assignment_actions_s.nextval
728 into lockingactid
729 from dual;
730
731 open c_get_asg_id(l_person_id);
732 fetch c_get_asg_id into l_assignment_id;
733 if c_get_asg_id%NOTFOUND then
734 close c_get_asg_id;
735 raise hr_utility.hr_error;
736 else
737 close c_get_asg_id;
738 end if;
739
740 /* Insert into pay_assignment_actions. */
741 hr_utility.trace('creating asg action');
742
743 hr_nonrun_asact.insact(lockingactid => lockingactid,
744 assignid => l_assignment_id,
745 pactid => l_yepp_payroll_action_id,
746 chunk => '1',
747 greid => p_gre_id,
748 status => 'M' );
749
750 hr_utility.set_location(gv_package_name || '.get_eligible_assignments', 120);
751
752 /* insert into ff_archive_items */
753 -- Bug 3829668
754 hr_utility.trace('creating ff_archive_items entry');
755 create_archive(lockingactid ,
756 p_gre_id);
757
758 hr_utility.set_location(gv_package_name || '.get_eligible_assignments', 121);
759
760 /* Bug No : 3137858 Update the serial number column with the person id
761 So that retry us payroll process archives balance values*/
762 hr_utility.trace('updating asg action');
763
764 update pay_assignment_actions aa
765 set aa.serial_number = to_char(l_person_id)
766 where aa.assignment_action_id = lockingactid;
767
768
769 -- Pupulate the plsql table for eligible assignments
770 l_yepp_elgble_asg_table(l_assignment_id).c_assignment_id := l_assignment_id;
771
772 -- Populate plsql table of all reported assignments. It will be used to
773 -- Identify all assignments in different GRE and in the assignment set.
774 l_gre_reported_asg_table(l_assignment_id).c_assignment_id := l_assignment_id;
775
776 hr_utility.set_location(gv_package_name || '.get_eligible_assignments', 130);
777
778 end if; /* l_value <> 0 */
779 end if; /* l_prev_person_id <> l_person_id */
780 end if; /* l_bal_aaid <> -9999 */
781
782 END LOOP;
783 close c_get_latest_asg;
784
785 /* We should not be reporting the actions that were not marked for retry outside of this process in the report */
786
787 /*
788 -- Populate alread marked for retry assignments to the plsql table
789 for ma_rec in get_already_marked_assignments(l_yepp_payroll_action_id,
790 p_assignment_set_id,p_gre_id)
791 loop
792 -- Pupulate Pupulate the plsql table for eligible assignments
793 l_yepp_elgble_asg_table(ma_rec.assignment_id).c_assignment_id
794 := ma_rec.assignment_id;
795
796 -- Populate plsql table of all reported assignments
797 l_gre_reported_asg_table(ma_rec.assignment_id).c_assignment_id
798 := ma_rec.assignment_id;
799 end loop;
800
801 */
802 hr_utility.set_location(gv_package_name || '.get_eligible_assignments', 140);
803
804 EXCEPTION
805 WHEN OTHERS THEN
806 hr_utility.trace('Error in '|| gv_package_name || '.get_eligible_assignments');
807 RAISE;
808
809 END get_eligible_assignments;
810
811
812 /******************************************************************
813 Procedure get_processed_assignments
814 Description : Gets the list of all primary assignments from the
815 assignment set which are processed by the year end
816 process.
817 ******************************************************************/
818
819 PROCEDURE get_processed_assignments(p_effective_date in date,
820 p_gre_id in number,
821 p_assignment_set_id in number )
822 IS
823
824 -- Cursor to get primary assignments from the assignment set which
825 -- are processed in the Year End Pre-Process
826 /*
827 cursor c_get_processed_assignments(cp_effective_date date,
828 cp_gre_id number,
829 cp_assignment_set_id number)
830 is
831 select distinct has.assignment_id
832 from hr_assignment_set_amendments has,
833 per_assignments_f paf
834 where has.assignment_set_id = cp_assignment_set_id
835 and paf.assignment_id = has.assignment_id
836 and paf.primary_flag = 'Y'
837 and exists
838 ( select '1'
839 FROM pay_payroll_actions ppa, -- Year End
840 pay_assignment_actions paa -- Year End
841 WHERE ppa.report_type = 'YREND'
842 AND ppa.action_status = 'C'
843 AND ppa.effective_date = add_months(cp_effective_date, 12) - 1
844 AND instr(ppa.legislative_parameters, cp_gre_id)>0
845 AND ppa.payroll_action_id = paa.payroll_action_id
846 AND paa.action_status = 'C'
847 AND paa.assignment_id = has.assignment_id);
848 */
849
850 cursor c_get_processed_assignments(cp_effective_date date,
851 cp_gre_id number,
852 cp_assignment_set_id number)
853 is
854 select distinct has.assignment_id
855 from hr_assignment_set_amendments has,
856 per_assignments_f paf
857 where has.assignment_set_id = cp_assignment_set_id
858 and paf.assignment_id = has.assignment_id
859 and exists( SELECT 1
860 FROM pay_payroll_actions ppa1, -- Year End
861 pay_assignment_actions paa1 -- Year End
862 WHERE ppa1.report_type = 'YREND'
863 AND ppa1.action_status = 'C'
864 AND ppa1.effective_date = add_months(cp_effective_date, 12) - 1
865 AND to_number(substr(legislative_parameters,
866 instr(legislative_parameters,'TRANSFER_GRE=') +
867 length('TRANSFER_GRE='))) = cp_gre_id -- Bug 3228332
868 AND ppa1.payroll_action_id = paa1.payroll_action_id
869 AND paa1.serial_number = to_char(paf.person_id)); -- Bug 3228332
870
871 l_processed_assignment_id per_assignments_f.assignment_id%type;
872
873 BEGIN
874
875 hr_utility.set_location(gv_package_name || '.get_processed_assignments', 10);
876
877 -- Open Curosr c_get_processed_assignments
878 open c_get_processed_assignments(p_effective_date ,
879 p_gre_id,
880 p_assignment_set_id);
881 LOOP
882
883 fetch c_get_processed_assignments into l_processed_assignment_id;
884 exit when c_get_processed_assignments%notfound;
885 hr_utility.set_location(gv_package_name || '.get_processed_assignments', 20);
886 -- Populate plsql tbales
887 l_yepp_prc_asg_table(l_processed_assignment_id).c_assignment_id := l_processed_assignment_id;
888 l_gre_reported_asg_table(l_processed_assignment_id).c_assignment_id := l_processed_assignment_id;
889
890 END LOOP;
891 close c_get_processed_assignments;
892
893 hr_utility.set_location(gv_package_name || '.get_processed_assignments', 30);
894
895 EXCEPTION
896 WHEN OTHERS THEN
897 hr_utility.trace('Error in '|| gv_package_name || '.get_processed_assignments');
898 RAISE;
899 END get_processed_assignments;
900
901
902 /******************************************************************
903 Procedure get_non_elgble_assignments
904 Description : Gets the list of all primary assignments from the
905 assignment set which are not eligible for the year
906 end process.
907 ******************************************************************/
908
909 PROCEDURE get_non_elgble_assignments(p_assignment_set_id in number,
910 p_gre_id in number,
911 p_effective_date in date)
912
913 IS
914 -- Cursor to get all primary assignments from the assignment set.
915 cursor c_get_assignments(cp_assignment_set_id number,
916 cp_gre_id number,
917 cp_effective_date date)
918 is
919 select distinct has.assignment_id
920 from hr_assignment_set_amendments has,
921 per_assignments_f paf,
922 pay_us_asg_reporting puar
923 where has.assignment_set_id = cp_assignment_set_id
924 and paf.assignment_id = has.assignment_id
925 and nvl(has.include_or_exclude,'I') = 'I'
926 and paf.effective_start_date <= add_months(cp_effective_date, 12) - 1
927 and paf.effective_end_date >= cp_effective_date
928 and puar.assignment_id = paf.assignment_id
929 and puar.tax_unit_id = cp_gre_id
930 and paf.primary_flag = 'Y';
931
932 l_assignment_id per_assignments_f.assignment_id%type;
933
934 BEGIN
935
936 hr_utility.set_location(gv_package_name || '.get_non_elgble_assignments', 10);
937 hr_utility.trace('Assign Set ID'||p_assignment_set_id);
938
939 -- Open Cursor c_get_assignments
940 open c_get_assignments(p_assignment_set_id,p_gre_id,p_effective_date);
941
942 LOOP
943
944 fetch c_get_assignments into l_assignment_id;
945 exit when c_get_assignments%notfound;
946 hr_utility.set_location(gv_package_name || '.get_non_elgble_assignments', 20);
947
948 if l_yepp_elgble_asg_table.exists(l_assignment_id) then
949 hr_utility.set_location(gv_package_name || '.get_non_elgble_assignments', 30);
950 hr_utility.trace('Assignment Exists');
951
952 elsif l_yepp_prc_asg_table.exists(l_assignment_id) then
953 hr_utility.trace('Assignment Exists');
954 hr_utility.set_location(gv_package_name || '.get_non_elgble_assignments', 40);
955
956 else
957 -- populate not eligible assignments table
958 l_yepp_not_elgble_asg_table(l_assignment_id).c_assignment_id := l_assignment_id;
959 l_gre_reported_asg_table(l_assignment_id).c_assignment_id := l_assignment_id;
960 hr_utility.set_location(gv_package_name || '.get_non_elgble_assignments', 50);
961
962 end if;
963
964 hr_utility.set_location(gv_package_name || '.get_non_elgble_assignments', 60);
965 END LOOP;
966
967 close c_get_assignments;
968
969 hr_utility.set_location(gv_package_name || '.get_non_elgble_assignments', 70);
970
971 EXCEPTION
972 WHEN OTHERS THEN
973 hr_utility.trace('Error in '|| gv_package_name || '.get_non_elgble_assignments');
974 RAISE;
975
976 END get_non_elgble_assignments;
977
978
979 /******************************************************************
980 Procedure print_table_details
981 Description : prints the table details in HTML format
982 ******************************************************************/
983 PROCEDURE print_table_details(p_assignment_id in number)
984 IS
985 BEGIN
986 hr_utility.set_location(gv_package_name || 'print_table_details', 10);
987
988 -- Get person_id of the employee
989 open c_person_id(p_assignment_id);
990 fetch c_person_id into lv_person_id;
991 close c_person_id;
992
993 -- Get Employee Details
994 hr_utility.set_location(gv_package_name || 'print_table_details', 20);
995 open c_employee_details(lv_person_id);
996 fetch c_employee_details into lv_emp_no,lv_emp_name,lv_emp_ssn;
997 close c_employee_details;
998
999 hr_utility.set_location(gv_package_name || 'print_table_details', 30);
1000 l_assignment_inserted := l_assignment_inserted + 1;
1001 lv_data_row := formated_detail_string(
1002 p_output_file_type
1003 ,to_char(l_effective_date,'YYYY')
1004 ,lv_gre_name
1005 ,lv_emp_name
1006 ,lv_emp_ssn
1007 ,lv_emp_no);
1008 if p_output_file_type ='HTML' then
1009 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1010 end if;
1011
1012 fnd_file.put_line(fnd_file.output, lv_data_row);
1013 hr_utility.set_location(gv_package_name || 'print_table_details', 40);
1014
1015 END print_table_details;
1016
1017
1018
1019 /******************************************************************
1020 Procedure report_secondary_assignments
1021 Description : Gets the list of secondary assignments from the
1022 assignment set and report them
1023 ******************************************************************/
1024 PROCEDURE report_secondary_assignments(p_assignment_set_id in number,
1025 p_gre_id in number,
1026 p_effective_date in date)
1027
1028 IS
1029 -- Cursor to get all primary assignments from the assignment set.
1030 cursor c_get_secondary_assignments(cp_assignment_set_id number,
1031 cp_gre_id number,
1032 cp_effective_date date)
1033 is
1034 select distinct has.assignment_id
1035 from hr_assignment_set_amendments has,
1036 per_assignments_f paf,
1037 pay_us_asg_reporting puar
1038 where assignment_set_id = cp_assignment_set_id
1039 and paf.assignment_id = has.assignment_id
1040 and nvl(has.include_or_exclude,'I') = 'I'
1041 and paf.effective_start_date <= add_months(cp_effective_date, 12) - 1
1042 and paf.effective_end_date >= cp_effective_date
1043 and puar.assignment_id = paf.assignment_id
1044 and puar.tax_unit_id = cp_gre_id
1045 and paf.primary_flag <> 'Y';
1046
1047 l_assignment_id per_assignments_f.assignment_id%type;
1048 l_count number := 0;
1049 l_header_printed varchar2(1) := 'N';
1050 BEGIN
1051
1052 hr_utility.set_location(gv_package_name || '.report_secondary_Assignments', 10);
1053 hr_utility.trace('Assign Set ID'||p_assignment_set_id);
1054
1055 -- Open Cursor c_get_secondary_assignments
1056 open c_get_secondary_assignments(p_assignment_set_id,
1057 p_gre_id,
1058 p_effective_date);
1059 LOOP
1060
1061 fetch c_get_secondary_assignments into l_assignment_id;
1062 exit when c_get_secondary_assignments%notfound;
1063
1064 /* we should be displaying the secondary assignments only if it does not
1065 exists in any other tables. Bug 3440806 */
1066
1067 if l_yepp_elgble_asg_table.exists(l_assignment_id) then
1068 hr_utility.set_location(gv_package_name || '.report_secondary_assignments', 20);
1069 hr_utility.trace('Assignment Exists');
1070
1071 elsif l_yepp_prc_asg_table.exists(l_assignment_id) then
1072 hr_utility.trace('Assignment Exists');
1073 hr_utility.set_location(gv_package_name || '.report_secondary_assignments', 30);
1074
1075 elsif l_yepp_not_elgble_asg_table.exists(l_assignment_id) then
1076 hr_utility.trace('Assignment Exists');
1077 hr_utility.set_location(gv_package_name || '.report_secondary_assignments', 40);
1078
1079 else
1080
1081 l_count := l_count + 1 ;
1082 hr_utility.set_location(gv_package_name || '.report_secondary_Assignments', 20);
1083
1084 if l_header_printed = 'N' then
1085 -- Print the Table Header
1086 print_table_header('4. '||l_secasg_table_header,p_output_file_type);
1087 l_header_printed := 'Y';
1088 end if;
1089
1090 -- Print Employee Details
1091 print_table_details(l_assignment_id);
1092 l_gre_reported_asg_table(l_assignment_id).c_assignment_id := l_assignment_id;
1093
1094 end if;
1095
1096 END LOOP;
1097 if p_output_file_type ='HTML' then
1098 fnd_file.put_line(fnd_file.output,'</table>') ;
1099 end if;
1100
1101 close c_get_secondary_assignments;
1102 --
1103 if l_count > 0 then
1104 gv_sec_asg_reported := 'Y';
1105 end if;
1106 --
1107 hr_utility.set_location(gv_package_name || '.report_secondary_assignments', 30);
1108
1109 EXCEPTION
1110 WHEN OTHERS THEN
1111 hr_utility.trace('Error in '|| gv_package_name || '.report_secondary_assignments');
1112 RAISE;
1113
1114 END report_secondary_assignments;
1115
1116
1117
1118 /******************************************************************
1119 Procedure get_other_assignments
1120 Description : Gets the list of assignments in the assignment set
1121 but in different GRE then entered as parameter
1122 ******************************************************************/
1123 PROCEDURE get_other_assignments(p_assignment_set_id in number,
1124 p_gre_id in number,
1125 p_effective_date in date)
1126
1127 IS
1128 -- Cursor to get all primary assignments from the assignment set.
1129 cursor c_other_assignments(cp_assignment_set_id number,
1130 cp_gre_id number,
1131 cp_effective_date date)
1132 is
1133 select distinct has.assignment_id, puar.tax_unit_id
1134 from hr_assignment_set_amendments has,
1135 per_assignments_f paf,
1136 pay_us_asg_reporting puar
1137 where assignment_set_id = cp_assignment_set_id
1138 and paf.assignment_id = has.assignment_id
1139 and nvl(has.include_or_exclude,'I') = 'I'
1140 and paf.effective_start_date <= add_months(cp_effective_date, 12) - 1
1141 and paf.effective_end_date >= cp_effective_date
1142 and puar.assignment_id = paf.assignment_id
1143 and puar.tax_unit_id <> cp_gre_id;
1144
1145 -- Cursor to get gre name
1146 cursor c_get_gre_name(cp_gre_id number)
1147 is
1148 select name
1149 from hr_organization_units
1150 where organization_id = cp_gre_id;
1151
1152 l_oth_assignment_id per_assignments_f.assignment_id%type;
1153 l_gre_id pay_us_asg_reporting.tax_unit_id%type;
1154 l_header_printed varchar2(1) := 'N';
1155
1156 BEGIN
1157
1158 hr_utility.set_location(gv_package_name || '.get_other_assignments', 10);
1159 hr_utility.trace('Assign Set ID'||p_assignment_set_id);
1160
1161 -- Check of secondary assignment is reported.
1162 -- Used for formating of squence number
1163 if gv_sec_asg_reported = 'Y' then
1164 l_othasg_table_header := '5. '||l_othasg_table_header;
1165 else
1166 l_othasg_table_header := '4. '||l_othasg_table_header;
1167 end if;
1168 --
1169
1170 -- Open Cursor c_other_assignments
1171 open c_other_assignments(p_assignment_set_id,
1172 p_gre_id,
1173 p_effective_date);
1174 LOOP
1175
1176 fetch c_other_assignments into l_oth_assignment_id,l_gre_id;
1177 exit when c_other_assignments%notfound;
1178 hr_utility.set_location(gv_package_name || '.get_other_assignments', 20);
1179
1180 if l_gre_reported_asg_table.exists(l_oth_assignment_id) then
1181 hr_utility.trace('The assignment already reported above');
1182 else
1183 -- Get other GRE Names
1184 open c_get_gre_name(l_gre_id);
1185 fetch c_get_gre_name into lv_gre_name;
1186 close c_get_gre_name;
1187 hr_utility.set_location(gv_package_name || '.get_other_assignments', 30);
1188 if l_header_printed = 'N' then
1189 -- Print the Table Header
1190 print_table_header(l_othasg_table_header,p_output_file_type);
1191 l_header_printed := 'Y';
1192 end if;
1193 hr_utility.set_location(gv_package_name || '.get_other_assignments', 40);
1194
1195 -- Print the details of the employee
1196 print_table_details(l_oth_assignment_id);
1197 hr_utility.set_location(gv_package_name || '.get_other_assignments', 50);
1198 end if;
1199
1200 END LOOP;
1201 hr_utility.set_location(gv_package_name || '.get_other_assignments', 60);
1202 if p_output_file_type ='HTML' then
1203 fnd_file.put_line(fnd_file.output,'</table>') ;
1204 end if;
1205
1206 close c_other_assignments;
1207 hr_utility.set_location(gv_package_name || '.get_other_assignments', 90);
1208
1209 EXCEPTION
1210 WHEN OTHERS THEN
1211 hr_utility.trace('Error in '|| gv_package_name || '.get_other_assignments');
1212 RAISE;
1213
1214 END get_other_assignments;
1215
1216
1217 --------------------------------------------------------------------
1218 -- The Main Procedure Begins Here
1219 --------------------------------------------------------------------
1220
1221 BEGIN
1222
1223 -- hr_utility.trace_on(null, 'pyusyeaa');
1224 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 10);
1225
1226 -- Get the date in canonical format
1227 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
1228
1229 --Get GRE Name
1230 open c_gre_name(p_gre_id);
1231 fetch c_gre_name into lv_gre_name,lv_business_group_id;
1232 close c_gre_name;
1233
1234 -- Get GRE type
1235 hr_utility.trace('Fetching GRE type (1099R/W2)');
1236 open c_gre_type(p_gre_id);
1237 fetch c_gre_type into lv_gre_type;
1238 if c_gre_type%notfound then
1239 lv_gre_type := 'W2';
1240 end if;
1241 close c_gre_type;
1242
1243 hr_utility.trace('Checking if Federal Magtape is run...');
1244 -- Check if the Federal Magtape is already processed in the year
1245 open c_w2_magtape_run_exists(l_effective_date,
1246 lv_business_group_id,
1247 lv_gre_type);
1248 fetch c_w2_magtape_run_exists into l_temp;
1249
1250 -- Format and print the heading of the output page(Bug 3137858)
1251
1252 fnd_file.put_line(fnd_file.output,
1253 pay_us_payroll_utils.formated_header_string(gv_title || ': Tax Year: ' ||
1254 to_char(l_effective_date,'YYYY')||', GRE: '||lv_gre_name,p_output_file_type ));
1255
1256 if p_output_file_type ='HTML' then
1257 fnd_file.put_line(fnd_file.output, '<body>');
1258 end if;
1259
1260 if c_w2_magtape_run_exists%found then -- Magnetic tape processed
1261
1262 if p_output_file_type ='HTML' then
1263 fnd_file.put_line(fnd_file.output, '<br><br><table align=center>');
1264 end if;
1265
1266 if p_output_file_type ='HTML' then
1267 fnd_file.put_line(fnd_file.output, '<tr>');
1268 end if;
1269
1270 fnd_file.put_line(fnd_file.output,
1271 pay_us_payroll_utils.formated_data_string
1272 (p_input_string => 'The request cannot process the assignments' ||
1273 ' since one of the Magnetic tapes is already processed in the tax year.'
1274 ,p_bold => 'Y'
1275 ,p_output_file_type => p_output_file_type));
1276
1277 if p_output_file_type ='HTML' then
1278 fnd_file.put_line(fnd_file.output, '</tr>');
1279 end if;
1280
1281 if p_output_file_type ='HTML' then
1282 fnd_file.put_line(fnd_file.output, '<tr>');
1283 end if;
1284
1285 fnd_file.put_line(fnd_file.output,
1286 pay_us_payroll_utils.formated_data_string
1287 (p_input_string => 'Please rollback the magnetic tape and try again.'
1288 ,p_bold => 'Y'
1289 ,p_output_file_type => p_output_file_type));
1290
1291 if p_output_file_type ='HTML' then
1292 fnd_file.put_line(fnd_file.output, '</tr> </table> </body> </HTML>');
1293 end if;
1294
1295 close c_w2_magtape_run_exists;
1296
1297 else -- Magnetic tape not processed
1298
1299 -- Get the date in canonical format
1300 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
1301
1302 /*
1303 for bug 3984539 we have changed the sequence. get_eligible_assignments
1304 looks for assignments for whom YEPP is not run and inserts a action with
1305 M and get_processed_assignments looks for assignment in YEPP. If we have
1306 get_eligible_assignments before get_processed_assignments we will always
1307 have an action in YEPP.
1308 */
1309
1310 -- Call get_processed_assignments
1311 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 30);
1312 get_processed_assignments(l_effective_Date,
1313 p_gre_id,
1314 p_assign_set);
1315
1316 -- Call get_eligible_assignments
1317 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 20);
1318 get_eligible_assignments(l_effective_Date,
1319 p_gre_id,
1320 p_assign_set);
1321
1322 -- Call get_non_elgble_assignments
1323 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 40);
1324 get_non_elgble_assignments(p_assign_set,
1325 p_gre_id,
1326 l_effective_date);
1327
1328 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 50);
1329
1330
1331 /***Start Formating of the out put for all Processed Assignments***/
1332
1333 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 70);
1334 if l_yepp_prc_asg_table.count>0 Then
1335
1336 -- Print the Table Header
1337 print_table_header(l_prced_table_header,p_output_file_type);
1338
1339 -- Report the Employees
1340
1341 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 80);
1342
1343 for l_assignment_id in l_yepp_prc_asg_table.first..l_yepp_prc_asg_table.last
1344 LOOP
1345 if l_yepp_prc_asg_table.exists(l_assignment_id) Then
1346 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 90);
1347 print_table_details(l_yepp_prc_asg_table(l_assignment_id).c_assignment_id);
1348 end if;
1349 END LOOP;
1350
1351 if p_output_file_type ='HTML' then
1352 fnd_file.put_line(fnd_file.output,'</table>') ;
1353 end if;
1354
1355 end if;
1356
1357 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 100);
1358 -- If not processed assignment found from the assignment set
1359
1360 if l_assignment_inserted=0 then
1361 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 110);
1362 formated_zero_count(p_output_file_type,'PROCESSED');
1363 end if;
1364
1365 /***End Formating of the out put for all Processed Assignments*****/
1366
1367
1368 /***Start Formating of the out put for all Eligible Assignments****/
1369
1370 l_assignment_inserted := 0;
1371 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 120);
1372 if l_yepp_elgble_asg_table.count >0 Then
1373
1374 -- Print the Table Header
1375 print_table_header(l_elgbl_table_header,p_output_file_type);
1376
1377 -- Report all Eligible Assignments
1378 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 130);
1379
1380 for l_assignment_id in l_yepp_elgble_asg_table.first..l_yepp_elgble_asg_table.last
1381
1382 LOOP
1383
1384 if l_yepp_elgble_asg_table.exists(l_assignment_id) then
1385 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 140);
1386 print_table_details(l_yepp_elgble_asg_table(l_assignment_id).c_assignment_id);
1387
1388 end if;
1389 END LOOP;
1390 if p_output_file_type ='HTML' then
1391 fnd_file.put_line(fnd_file.output,'</table>') ;
1392 end if;
1393 end if;
1394
1395 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 150);
1396
1397 -- If no elgible assignments found
1398 if l_assignment_inserted=0 then
1399 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 260);
1400 formated_zero_count(p_output_file_type,'ELGBLE');
1401 end if;
1402
1403 /***End Formating of the out put for all Eligible Assignments******/
1404
1405
1406 /***Start Formating of the output for Non Eligible Assignments*****/
1407 --Print Non Eligible Employees
1408 l_assignment_inserted := 0;
1409 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 170);
1410 if l_yepp_not_elgble_asg_table.count >0 then
1411
1412 -- Print the Table Header
1413 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 180);
1414 print_table_header(l_nonelgbl_table_header,p_output_file_type);
1415
1416 --Report Non Elgible Employees Output
1417
1418 for l_assignment_id in l_yepp_not_elgble_asg_table.first..l_yepp_not_elgble_asg_table.last
1419
1420 LOOP
1421 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 190);
1422 if l_yepp_not_elgble_asg_table.exists(l_assignment_id) then
1423 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 200);
1424 print_table_details(l_yepp_not_elgble_asg_table(l_assignment_id).c_assignment_id);
1425
1426 end if;
1427 END LOOP;
1428
1429 if p_output_file_type ='HTML' then
1430 fnd_file.put_line(fnd_file.output,'</table>') ;
1431 end if;
1432
1433 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 210);
1434 end if;
1435
1436 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 220);
1437
1438 -- When no employee found who is not elogible
1439 if l_assignment_inserted=0 then
1440
1441 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 230);
1442 formated_zero_count(p_output_file_type,'NOTELGBLE');
1443 end if;
1444
1445 /***End Formating of the output for Non Eligible Assignments*******/
1446
1447
1448 -- Call report_secondary_assignments
1449 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 240);
1450 report_secondary_assignments(p_assign_set,
1451 p_gre_id,
1452 l_effective_date);
1453
1454 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 250);
1455 -- Call get_other_assignments
1456 get_other_assignments(p_assign_set,
1457 p_gre_id,
1458 l_effective_date);
1459
1460 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 260);
1461
1462 close c_w2_magtape_run_exists;
1463
1464 if p_output_file_type ='HTML' then
1465 fnd_file.put_line(fnd_file.output, '</body> </HTML>');
1466 end if;
1467
1468 end if; -- Magnetic tape not processed
1469
1470 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 270);
1471
1472 -- Update the concurrent program request if the output type is HTML
1473 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 280);
1474 if p_output_file_type ='HTML' then
1475 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 290);
1476 UPDATE fnd_concurrent_requests
1477 SET output_file_type = 'HTML'
1478 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID ;
1479
1480 commit;
1481 end if;
1482 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 300);
1483
1484 EXCEPTION
1485 WHEN OTHERS THEN
1486 hr_utility.trace('Error in '|| gv_package_name || '.add_actions_to_yepp');
1487 RAISE;
1488
1489 END add_actions_to_yepp;
1490
1491 END pay_us_yepp_add_actions_pkg;
[Home] [Help]