[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_YEPP_ADD_ACTIONS_PKG
Source
1 PACKAGE BODY pay_ca_yepp_add_actions_pkg AS
2 /* $Header: pycayeaa.pkb 120.0.12010000.2 2008/09/16 10:09:27 sapalani ship $ */
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_ca_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 Year End Preprocess'
26
27 Change List
28 -----------
29 Date Name Vers Bug No Description
30 ----------- ---------- ------ ------- --------------------------
31 18-Oct-2004 ssouresr 115.0 Created.
32 06-Nov-2004 ssouresr 115.1 Corrected the cursor c_all_gres
33 16-Sep-2008 sapalani 115.2 7392645 Replaced correct report type in
34 cursors c_rl1_magtape_run_exists
35 and c_rl2_magtape_run_exists
36 ********************************************************************/
37
38 gv_title VARCHAR2(100);
39 gv_package_name VARCHAR2(50) := 'pay_ca_yepp_add_actions_pkg';
40 gv_sec_asg_reported VARCHAR2(1) := 'N';
41
42
43 /********************************************************************
44 Function to display the Titles of the columns of the employee details
45 ********************************************************************/
46
47 FUNCTION formated_header_string (p_report_type in varchar2,
48 p_output_file_type in varchar2)
49 RETURN varchar2
50 IS
51
52 lv_format1 varchar2(32000);
53 lv_year_heading varchar2(200);
54 lv_gre_heading varchar2(200);
55 lv_pre_heading varchar2(200);
56 lv_emp_name_heading varchar2(200);
57 lv_emp_sin_heading varchar2(200);
58 lv_emp_num_heading varchar2(200);
59
60 BEGIN
61
62 lv_year_heading := hr_general.decode_lookup('PAY_CA_MISSING_ASG','YEAR');
63 lv_emp_name_heading := hr_general.decode_lookup('PAY_CA_MISSING_ASG','EMP_NAME');
64 lv_emp_sin_heading := hr_general.decode_lookup('PAY_CA_MISSING_ASG','EMP_SIN');
65 lv_emp_num_heading := hr_general.decode_lookup('PAY_CA_MISSING_ASG','EMP_NUM');
66
67 if p_report_type in ('T4','T4A') then
68
69 lv_gre_heading := hr_general.decode_lookup('PAY_CA_MISSING_ASG','GRE');
70
71 lv_format1 :=
72 pay_us_payroll_utils.formated_data_string
73 (p_input_string => lv_year_heading
74 ,p_bold => 'Y'
75 ,p_output_file_type => p_output_file_type) ||
76 pay_us_payroll_utils.formated_data_string
77 (p_input_string => lv_gre_heading
78 ,p_bold => 'Y'
79 ,p_output_file_type => p_output_file_type) ||
80 pay_us_payroll_utils.formated_data_string
81 (p_input_string => lv_emp_name_heading
82 ,p_bold => 'Y'
83 ,p_output_file_type => p_output_file_type) ||
84 pay_us_payroll_utils.formated_data_string
85 (p_input_string => lv_emp_sin_heading
86 ,p_bold => 'Y'
87 ,p_output_file_type => p_output_file_type) ||
88 pay_us_payroll_utils.formated_data_string
89 (p_input_string => lv_emp_num_heading
90 ,p_bold => 'Y'
91 ,p_output_file_type => p_output_file_type);
92
93 elsif p_report_type in ('RL1','RL2') then
94
95 lv_pre_heading := hr_general.decode_lookup('PAY_CA_MISSING_ASG','PRE');
96
97 lv_format1 :=
98 pay_us_payroll_utils.formated_data_string
99 (p_input_string => lv_year_heading
100 ,p_bold => 'Y'
101 ,p_output_file_type => p_output_file_type) ||
102 pay_us_payroll_utils.formated_data_string
103 (p_input_string => lv_pre_heading
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 => lv_emp_name_heading
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 => lv_emp_sin_heading
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 => lv_emp_num_heading
116 ,p_bold => 'Y'
117 ,p_output_file_type => p_output_file_type);
118 end if;
119
120 return lv_format1 ;
121
122 EXCEPTION
123 WHEN OTHERS THEN
124 hr_utility.trace('Error in gv_package_name' || '.formated_header_string');
125 RAISE;
126
127 END formated_header_string;
128
129
130
131 /********************************************************************
132 Function to display the details of the selected employee
133 ********************************************************************/
134
135 FUNCTION formated_detail_string(
136 p_output_file_type in varchar2
137 ,p_year varchar2
138 ,p_gre_name varchar2
139 ,p_pre_name varchar2
140 ,p_employee_name varchar2
141 ,p_employee_sin varchar2
142 ,p_employee_number varchar2
143 ,p_report_type varchar2
144 ) RETURN varchar2
145 IS
146
147 lv_format1 varchar2(22000);
148
149 BEGIN
150
151 if p_report_type in ('T4','T4A') then
152
153 lv_format1 :=
154 pay_us_payroll_utils.formated_data_string
155 (p_input_string => p_year
156 ,p_bold => 'N'
157 ,p_output_file_type => p_output_file_type) ||
158 pay_us_payroll_utils.formated_data_string
159 (p_input_string => p_gre_name
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_employee_name
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_sin
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_number
172 ,p_bold => 'N'
173 ,p_output_file_type => p_output_file_type);
174
175 elsif p_report_type in ('RL1','RL2') then
176
177 lv_format1 :=
178 pay_us_payroll_utils.formated_data_string
179 (p_input_string => p_year
180 ,p_bold => 'N'
181 ,p_output_file_type => p_output_file_type) ||
182 pay_us_payroll_utils.formated_data_string
183 (p_input_string => p_pre_name
184 ,p_bold => 'N'
185 ,p_output_file_type => p_output_file_type) ||
186 pay_us_payroll_utils.formated_data_string
187 (p_input_string => p_employee_name
188 ,p_bold => 'N'
189 ,p_output_file_type => p_output_file_type) ||
190 pay_us_payroll_utils.formated_data_string
191 (p_input_string => p_employee_sin
192 ,p_bold => 'N'
193 ,p_output_file_type => p_output_file_type) ||
194 pay_us_payroll_utils.formated_data_string
195 (p_input_string => p_employee_number
196 ,p_bold => 'N'
197 ,p_output_file_type => p_output_file_type);
198
199 end if;
200
201 return lv_format1;
202
203 EXCEPTION
204 WHEN OTHERS THEN
205 hr_utility.trace('Error in '|| gv_package_name || '.formated_detail_string');
206 raise;
207
208 END formated_detail_string;
209
210
211 /********************************************************************
212 Procedure to display message if no employees are selected for
213 any of the four sections -
214 - Processed Assignments
215 - Eligible Assignments
216 - Not Eligible Assignments
217 ********************************************************************/
218
219 PROCEDURE formated_zero_count(output_file_type varchar2,
220 p_flag varchar2)
221 IS
222 lvc_message1 varchar2(200);
223 lvc_message2 varchar2(200);
224 lvc_message3 varchar2(200);
225
226 BEGIN
227
228 lvc_message1 := '1. '|| hr_general.decode_lookup('PAY_CA_MISSING_ASG','PROCESSED_NONE');
229 lvc_message2 := '2. '|| hr_general.decode_lookup('PAY_CA_MISSING_ASG','ELIGIBLE_NONE');
230 lvc_message3 := '3. '|| hr_general.decode_lookup('PAY_CA_MISSING_ASG','NONELIGIBLE_NONE');
231
232 if output_file_type = 'HTML' then
233 lvc_message1 := '<H4> '||lvc_message1||' </H4>';
234 lvc_message2 := '<H4> '||lvc_message2||' </H4>';
235 lvc_message3 := '<H4> '||lvc_message3||' </H4>';
236 end if;
237
238 if p_flag='PROCESSED' then
239 fnd_file.put_line(fnd_file.output,lvc_message1);
240 end if;
241
242 hr_utility.set_location(gv_package_name || '.formated_zero_count', 20);
243
244 if p_flag='ELGBLE' then
245 fnd_file.put_line(fnd_file.output, lvc_message2);
246 end if;
247
248 hr_utility.set_location(gv_package_name || '.formated_zero_count', 30);
249
250 if p_flag='NOTELGBLE' then
251 fnd_file.put_line(fnd_file.output, lvc_message3);
252 end if;
253
254 hr_utility.set_location(gv_package_name || '.formated_zero_count', 40);
255
256 EXCEPTION
257 WHEN OTHERS THEN
258 hr_utility.trace('Error in '|| gv_package_name || '.formated_zero_count');
259 RAISE;
260
261 END formated_zero_count;
262
263
264
265 /********************************************************************
266 Procedure to print the table in HTML format
267 ********************************************************************/
268
269 PROCEDURE print_table_header (p_header_text in varchar2,
270 p_report_type in varchar2,
271 p_output_file_type in varchar2)
272 IS
273 l_header_text varchar2(200);
274 BEGIN
275 hr_utility.set_location(gv_package_name || '.print_table_header', 10);
276 l_header_text := p_header_text ;
277
278 if p_output_file_type = 'HTML' then
279 l_header_text := '<H4> '||l_header_text||' </H4>';
280 end if;
281
282 fnd_file.put_line(fnd_file.output,l_header_text);
283
284 if p_output_file_type ='HTML' then
285 fnd_file.put_line(fnd_file.output, '<table border=1 align=center>');
286 fnd_file.put_line(fnd_file.output, '<tr>');
287 end if;
288
289 hr_utility.set_location(gv_package_name || '.print_table_header', 20);
290
291 fnd_file.put_line(fnd_file.output,formated_header_string(p_report_type, p_output_file_type));
292
293 if p_output_file_type ='HTML' then
294 fnd_file.put_line(fnd_file.output, '</tr>');
295 end if;
296
297 EXCEPTION
298 WHEN OTHERS THEN
299 hr_utility.trace('Error in '|| gv_package_name || '.print_table_header');
300 RAISE;
301 END print_table_header;
302
303
304 FUNCTION get_parameter(name in varchar2,
305 parameter_list varchar2)
306 RETURN varchar2
307 IS
308 start_ptr number;
309 end_ptr number;
310 token_val pay_payroll_actions.legislative_parameters%type;
311 par_value pay_payroll_actions.legislative_parameters%type;
312
313 BEGIN
314
315 token_val := name||'=';
316
317 start_ptr := instr(parameter_list, token_val) + length(token_val);
318 end_ptr := instr(parameter_list, ' ', start_ptr);
319
320 /* if there is no spaces use then length of the string */
321 if end_ptr = 0 then
322 end_ptr := length(parameter_list)+1;
323 end if;
324
325 /* Did we find the token */
326 if instr(parameter_list, token_val) = 0 then
327 par_value := NULL;
328 else
329 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
330 end if;
331
332 return par_value;
333
334 END get_parameter;
335
336
337 /********************************************************************
338 Main procedure called from the concurrent program.
339 Name: add_actions_to_yepp
340
341 Description: The input parameters for the procedure are Date,GRE,PRE,
342 Assignment Set and output file type from the concurrent
343 program. The procedure identifies the eligible/processed
344 /not eligible and secondary assignments from the
345 Assignment set and report them as the output in the
346 specified format.
347
348 ********************************************************************/
349
350 PROCEDURE add_actions_to_yepp(errbuf out nocopy varchar2,
351 retcode out nocopy number,
352 p_effective_date in varchar2,
353 p_bus_grp in number,
354 p_report_type in varchar2,
355 p_dummy1 in varchar2,
356 p_gre_id in number,
357 p_dummy2 in varchar2,
358 p_pre_id in number,
359 p_assign_set in varchar2,
360 p_output_file_type in varchar2)
361 IS
362
363 cursor c_t4_magtape_run_exists(cp_effective_date date,
364 cp_business_group_id number,
365 cp_gre_id number) is
366 select 1 from dual
367 where exists
368 (select 'X'
369 from hr_organization_information mag,
370 hr_organization_information gre,
371 hr_all_organization_units hou,
372 pay_payroll_actions ppa
373 where hou.business_group_id = cp_business_group_id
374 and hou.organization_id = gre.organization_id
375 and gre.org_information_context = 'Canada Employer Identification'
376 and gre.organization_id = cp_gre_id
377 and gre.org_information11 = get_parameter('TRANSMITTER_GRE',ppa.legislative_parameters)
378 and ppa.business_group_id+0 = cp_business_group_id
379 and ppa.effective_date + 0 = add_months(cp_effective_date,12) - 1
380 and ppa.action_status = 'C'
381 and ppa.report_type = 'PYT4MAG'
382 and mag.org_information_context = 'Fed Magnetic Reporting'
383 and mag.organization_id = to_number(gre.org_information11) );
384
385
386 cursor c_t4a_magtape_run_exists(cp_effective_date date,
387 cp_business_group_id number,
388 cp_gre_id number) is
389 select 1 from dual
390 where exists
391 (select 'X'
392 from hr_organization_information mag,
393 hr_organization_information gre,
394 hr_all_organization_units hou,
395 pay_payroll_actions ppa
396 where hou.business_group_id = cp_business_group_id
397 and hou.organization_id = gre.organization_id
398 and gre.org_information_context = 'Canada Employer Identification'
399 and gre.organization_id = cp_gre_id
400 and gre.org_information11 = get_parameter('TRANSMITTER_GRE',ppa.legislative_parameters)
401 and ppa.business_group_id+0 = cp_business_group_id
402 and ppa.effective_date + 0 = add_months(cp_effective_date,12) - 1
403 and ppa.action_status = 'C'
404 and ppa.report_type = 'MAG_T4A'
405 and mag.org_information_context = 'Fed Magnetic Reporting'
406 and mag.organization_id = to_number(gre.org_information11) );
407
408
409 cursor c_rl1_magtape_run_exists(cp_effective_date date,
410 cp_business_group_id number,
411 cp_pre_id number) is
412 select 1 from dual
413 where exists
414 (select 'X'
415 from hr_organization_information pre,
416 hr_all_organization_units hou,
417 pay_payroll_actions ppa
418 where hou.business_group_id = cp_business_group_id
419 and hou.organization_id = pre.organization_id
420 and pre.org_information4 = 'P01'
421 and pre.org_information_context = 'Prov Reporting Est'
422 and pre.organization_id = cp_pre_id
423 and decode(pre.org_information3, 'Y', to_char(pre.organization_id), pre.org_information20) =
424 get_parameter('TRANSMITTER_PRE',ppa.legislative_parameters)
425 and ppa.business_group_id+0 = cp_business_group_id
426 and ppa.effective_date + 0 = add_months(cp_effective_date,12) - 1
427 and ppa.action_status = 'C'
428 and ppa.report_type = 'RL1_XML_MAG'); --Bug 7392645
429
430
431 cursor c_rl2_magtape_run_exists(cp_effective_date date,
432 cp_business_group_id number,
433 cp_pre_id number) is
434 select 1 from dual
435 where exists
436 (select 'X'
437 from hr_organization_information pre,
438 hr_all_organization_units hou,
439 pay_payroll_actions ppa
440 where hou.business_group_id = cp_business_group_id
441 and hou.organization_id = pre.organization_id
442 and pre.org_information4 = 'P02'
443 and pre.org_information_context = 'Prov Reporting Est'
444 and pre.organization_id = cp_pre_id
445 and decode(pre.org_information3, 'Y', to_char(pre.organization_id), pre.org_information20) =
446 get_parameter('TRANSMITTER_PRE',ppa.legislative_parameters)
447 and ppa.business_group_id+0 = cp_business_group_id
448 and ppa.effective_date + 0 = add_months(cp_effective_date,12) - 1
449 and ppa.action_status = 'C'
450 and ppa.report_type = 'RL2_XML_MAG'); ----Bug 7392645
451
452
453 -- Cursor to get the GRE or PRE Name
454
455 cursor c_name (cp_org_id number) is
456 select name
457 from hr_all_organization_units_tl
458 where organization_id = cp_org_id
459 and language = userenv('LANG');
460
461 -- Cursor to get person_id of the assignments selected
462
463 cursor c_person_id (cp_assign_id number) is
464 select person_id
465 from per_all_assignments_f
466 where assignment_id = cp_assign_id;
467
468 -- Cursor to get Employee details
469
470 cursor c_employee_details (cp_person_id number ) is
471 select full_name,national_identifier
472 from per_all_people_f
473 where person_id = cp_person_id;
474
475 cursor c_assignment_no (cp_assign_id number ) is
476 select assignment_number
477 from per_all_assignments_f
478 where assignment_id = cp_assign_id;
479
480 lv_person_id per_all_people_f.person_id%type;
481 lv_gre_name hr_all_organization_units_tl.name%type;
482 lv_pre_name hr_all_organization_units_tl.name%type;
483 lv_emp_name per_all_people_f.full_name%type;
484 lv_emp_no per_all_people_f.employee_number%type;
485 lv_emp_sin per_all_people_f.national_identifier%type;
486 lv_data_row varchar2(4000);
487 l_assignment_inserted number :=0;
488 l_effective_date date;
489 l_temp number;
490 l_mag_exists number := 0;
491
492 l_elgbl_table_header varchar2(200);
493 l_prced_table_header varchar2(200);
494 l_nonelgbl_table_header varchar2(200);
495 l_secasg_table_header varchar2(200);
496 l_othasg_table_header varchar2(200);
497
498 /******************************************************************
499 Procedure get_eligible_assignments_fed
500 Description : Gets the list of all assignments eligible for
501 the archive by year end process and mark them for retry.
502 ******************************************************************/
503
504 PROCEDURE get_eligible_assignments_fed(p_effective_date in date,
505 p_gre_id in number,
506 p_assignment_set_id in number,
507 p_report_type in varchar2)
508 IS
509
510 cursor c_get_person_id (cp_assign_set_id number) is
511 select distinct paf.person_id
512 from hr_assignment_set_amendments has,
513 per_all_assignments_f paf
514 where has.assignment_set_id = cp_assign_set_id
515 and has.include_or_exclude = 'I'
516 and paf.assignment_id = has.assignment_id
517 and paf.assignment_type = 'E'
518 and paf.business_group_id+ 0 = p_bus_grp;
519
520 /* Cursor to get the latest assignment_action_id based
521 on person_id */
522 cursor c_get_latest_asg (cp_person_id number,
523 cp_gre_id number,
524 cp_effective_date date) is
525 select paa.assignment_action_id
526 from pay_assignment_actions paa,
527 per_all_assignments_f paf,
528 pay_payroll_actions ppa,
529 pay_action_classifications pac
530 where paf.person_id = cp_person_id
531 and paa.assignment_id = paf.assignment_id
532 and paa.tax_unit_id = cp_gre_id
533 and paa.payroll_action_id = ppa.payroll_action_id
534 and ppa.business_group_id+0 = p_bus_grp
535 and ppa.action_type = pac.action_type
536 and pac.classification_name = 'SEQUENCED'
537 and ppa.effective_date +0 between paf.effective_start_date
538 and paf.effective_end_date
539 and ppa.effective_date +0 between cp_effective_date
540 and add_months(cp_effective_date, 12) - 1
541 and ((nvl(paa.run_type_id, ppa.run_type_id) is null
542 and paa.source_action_id is null)
543 or (nvl(paa.run_type_id, ppa.run_type_id) is not null
544 and paa.source_action_id is not null)
545 or (ppa.action_type = 'V'
546 and ppa.run_type_id is null
547 and paa.run_type_id is not null
548 and paa.source_action_id is null))
549 and not exists (select 1
550 from pay_payroll_actions ppa1,
551 pay_assignment_actions paa1
552 where ppa1.report_type = p_report_type
553 and ppa1.business_group_id+0 = p_bus_grp
554 and ppa1.effective_date = add_months(cp_effective_date, 12) - 1
555 and to_number(get_parameter('TRANSFER_GRE',
556 ppa1.legislative_parameters)) = cp_gre_id
557 and ppa1.payroll_action_id = paa1.payroll_action_id
558 and paa1.serial_number = to_char(paf.person_id))
559 order by paa.action_sequence desc;
560
561
562 /* Cursor to get details of payroll action of the Year End Pre-Process */
563 cursor get_yepp_payroll_action(cp_effective_date date,
564 cp_gre_id number) is
565 select payroll_action_id
566 from pay_payroll_actions
567 where action_type = 'X'
568 and action_status = 'C'
569 and report_type = p_report_type
570 and business_group_id+0 = p_bus_grp
571 and to_number(get_parameter('TRANSFER_GRE',legislative_parameters)) = cp_gre_id
572 and effective_date = add_months(cp_effective_date, 12) - 1;
573
574 /* we should always be stamping the primary assignment_id, even
575 if the assignment selected in the assignment set is secondary
576 Get the primary assignment for the given person_id */
577
578 cursor c_get_asg_id (cp_person_id number) is
579 select assignment_id
580 from per_all_assignments_f paf
581 where person_id = cp_person_id
582 and primary_flag = 'Y'
583 and assignment_type = 'E'
584 and paf.effective_start_date <= add_months(p_effective_date, 12) - 1
585 and paf.effective_end_date >= p_effective_date
586 order by assignment_id desc;
587
588 l_bal_aaid pay_assignment_actions.assignment_action_id%type;
589 ln_non_taxable_earnings number(30);
590 ln_gross_earnings number(30);
591 ln_no_gross_earnings number(30);
592 l_assignment_id per_all_assignments_f.assignment_id%type;
593 l_person_id per_all_assignments_f.person_id%type;
594 l_yepp_payroll_action_id pay_payroll_actions.payroll_action_id%type;
595 lockingactid pay_assignment_actions.assignment_action_id%type;
596 l_prev_person_id per_all_assignments_f.person_id%type;
597
598 BEGIN
599
600 hr_utility.set_location(gv_package_name || '.get_eligible_assignments_fed', 10);
601
602 open get_yepp_payroll_action(p_effective_date,
603 p_gre_id);
604 fetch get_yepp_payroll_action into l_yepp_payroll_action_id;
605 close get_yepp_payroll_action ;
606
607 open c_get_person_id (p_assignment_set_id);
608 loop
609
610 fetch c_get_person_id into l_person_id;
611 exit when c_get_person_id%NOTFOUND;
612
613 l_bal_aaid := 0;
614 ln_non_taxable_earnings := 0;
615 ln_gross_earnings := 0;
616 ln_no_gross_earnings := 0;
617
618 /* Get the latest assignment action of selected person */
619
620 open c_get_latest_asg(l_person_id,
621 p_gre_id,
622 p_effective_date);
623 fetch c_get_latest_asg into l_bal_aaid;
624
625 if c_get_latest_asg%NOTFOUND then
626 l_bal_aaid := -9999;
627 end if;
628
629 close c_get_latest_asg;
630
631
632 hr_utility.set_location(gv_package_name || '.get_eligible_assignments_fed', 40);
633
634 if (l_prev_person_id <> l_person_id) or
635 (l_prev_person_id is null) then
636
637 l_prev_person_id := l_person_id;
638
639 if l_bal_aaid <> -9999 then
640
641 hr_utility.trace('bal_aaid = '||to_char(l_bal_aaid));
642
643 hr_utility.trace('Setting context');
644
645 pay_ca_balance_view_pkg.set_context('TAX_UNIT_ID',p_gre_id);
646 pay_ca_balance_view_pkg.set_context ('ASSIGNMENT_ACTION_ID',l_bal_aaid);
647
648
649 hr_utility.trace('person_id = '||to_char(l_person_id));
650
651 /* Get the primary assignment */
652
653 open c_get_asg_id(l_person_id);
654 fetch c_get_asg_id into l_assignment_id;
655 if c_get_asg_id%NOTFOUND then
656 close c_get_asg_id;
657 hr_utility.trace('Primary asg not found');
658 hr_utility.raise_error;
659 else
660 close c_get_asg_id;
661 end if;
662
663 if p_report_type = 'T4' then
664
665 ln_non_taxable_earnings :=
666 nvl(pay_ca_balance_pkg.call_ca_balance_get_value('T4 Non Taxable Earnings',
667 'YTD',l_bal_aaid, l_assignment_id,NULL,'PER',p_gre_id,p_bus_grp,NULL),
668 0);
669
670 hr_utility.trace('T4 Non Taxable Earnings :'||
671 to_char(ln_non_taxable_earnings));
672
673 ln_gross_earnings :=
674 nvl(pay_ca_balance_pkg.call_ca_balance_get_value('Gross Earnings',
675 'YTD',l_bal_aaid, l_assignment_id,NULL,'PER',p_gre_id,p_bus_grp,NULL),
676 0);
677
678 hr_utility.trace('Gross Earnings :'||
679 to_char(ln_gross_earnings));
680
681 ln_no_gross_earnings :=
682 nvl(pay_ca_balance_pkg.call_ca_balance_get_value('T4 No Gross Earnings',
683 'YTD',l_bal_aaid, l_assignment_id,NULL,'PER',p_gre_id,p_bus_grp,NULL),
684 0);
685
686 hr_utility.trace('T4 No Gross Earnings :'||
687 to_char(ln_no_gross_earnings));
688
689
690 if (((ln_gross_earnings <> 0) and
691 (ln_non_taxable_earnings <> ln_gross_earnings)) or
692 (ln_no_gross_earnings <> 0)) then
693
694 hr_utility.set_location(gv_package_name || '.get_eligible_assignments_fed', 100);
695
696 select pay_assignment_actions_s.nextval
697 into lockingactid
698 from dual;
699
700 hr_utility.trace('creating asg action');
701
702 hr_nonrun_asact.insact(lockingactid => lockingactid,
703 assignid => l_assignment_id,
704 pactid => l_yepp_payroll_action_id,
705 chunk => '1',
706 greid => p_gre_id,
707 status => 'M');
708
709 hr_utility.set_location(gv_package_name || '.get_eligible_assignments_fed', 110);
710
711 update pay_assignment_actions aa
712 set aa.serial_number = to_char(l_person_id)
713 where aa.assignment_action_id = lockingactid;
714
715 l_yepp_elgble_asg_table(l_assignment_id).c_assignment_id := l_assignment_id;
716 l_all_reported_asg_table(l_assignment_id).c_assignment_id := l_assignment_id;
717
718 end if;
719
720 elsif p_report_type = 'T4A' then
721
722 ln_gross_earnings :=
723 nvl(pay_ca_balance_pkg.call_ca_balance_get_value('Gross Earnings',
724 'YTD',l_bal_aaid, l_assignment_id,NULL,'PER',p_gre_id,p_bus_grp,NULL),
725 0);
726
727 hr_utility.trace('Gross Earnings :'||
728 to_char(ln_gross_earnings));
729
730 ln_no_gross_earnings :=
731 nvl(pay_ca_balance_pkg.call_ca_balance_get_value('T4A No Gross Earnings',
732 'YTD',l_bal_aaid, l_assignment_id,NULL,'PER',p_gre_id,p_bus_grp,NULL),
733 0);
734
735 hr_utility.trace('T4A No Gross Earnings :'||
736 to_char(ln_no_gross_earnings));
737
738 if ((ln_gross_earnings <> 0) or
739 (ln_no_gross_earnings <> 0)) then
740
741 hr_utility.set_location(gv_package_name || '.get_eligible_assignments_fed', 130);
742
743 select pay_assignment_actions_s.nextval
744 into lockingactid
745 from dual;
746
747 hr_utility.trace('creating asg action');
748
749 hr_nonrun_asact.insact(lockingactid => lockingactid,
750 assignid => l_assignment_id,
751 pactid => l_yepp_payroll_action_id,
752 chunk => '1',
753 greid => p_gre_id,
754 status => 'M');
755
756 hr_utility.set_location(gv_package_name || '.get_eligible_assignments_fed', 140);
757
758 update pay_assignment_actions aa
759 set aa.serial_number = to_char(l_person_id)
760 where aa.assignment_action_id = lockingactid;
761
762 l_yepp_elgble_asg_table(l_assignment_id).c_assignment_id := l_assignment_id;
763 l_all_reported_asg_table(l_assignment_id).c_assignment_id := l_assignment_id;
764
765 end if;
766
767 end if; /* p_report_type */
768
769 end if; /* l_bal_aaid <> -9999 */
770
771 end if; /* l_prev_person_id <> l_person_id */
772
773 end loop;
774 close c_get_person_id;
775
776 EXCEPTION
777 WHEN OTHERS THEN
778 hr_utility.trace('Error in '|| gv_package_name || '.get_eligible_assignments_fed');
779 raise;
780
781 END get_eligible_assignments_fed;
782
783
784 /******************************************************************
785 Procedure get_eligible_assignments_prov
786 Description : Gets the list of all assignments eligible for the provincial
787 archivers by year end process and mark them for retry.
788 ******************************************************************/
789
790 PROCEDURE get_eligible_assignments_prov (p_effective_date in date,
791 p_pre_id in number,
792 p_assignment_set_id in number,
793 p_report_type in varchar2)
794 IS
795
796 cursor c_get_person_id (cp_assign_set_id number) is
797 select distinct paf.person_id
798 from hr_assignment_set_amendments has,
799 per_all_assignments_f paf
800 where has.assignment_set_id = cp_assign_set_id
801 and has.include_or_exclude = 'I'
802 and paf.assignment_id = has.assignment_id
803 and paf.assignment_type = 'E'
804 and paf.business_group_id+0 = p_bus_grp;
805
806 cursor c_all_gres is
807 select hoi.organization_id
808 from hr_organization_information hoi,
809 hr_all_organization_units hou
810 where hoi.org_information_context = 'Canada Employer Identification'
811 and hoi.org_information2 = to_char(p_pre_id)
812 and hou.business_group_id = p_bus_grp
813 and hou.organization_id = hoi.organization_id;
814
815 /* Cursor to get the latest assignment_action_id based
816 on person_id */
817 cursor c_get_latest_asg (cp_person_id number,
818 cp_gre_id number,
819 cp_effective_date date) is
820 select paa.assignment_action_id
821 from pay_assignment_actions paa,
822 per_all_assignments_f paf,
823 per_all_people_f ppf,
824 pay_payroll_actions ppa,
825 pay_action_classifications pac
826 where ppf.person_id = cp_person_id
827 and paf.person_id = ppf.person_id
828 and paa.assignment_id = paf.assignment_id
829 and paa.tax_unit_id = cp_gre_id
830 and ppa.business_group_id+0 = p_bus_grp
831 and ppa.payroll_action_id = paa.payroll_action_id
832 and ppa.effective_date between ppf.effective_start_date
833 and ppf.effective_end_date
834 and ppa.effective_date between paf.effective_start_date
835 and paf.effective_end_date
836 and ppa.effective_date between cp_effective_date
837 and add_months(cp_effective_date, 12) - 1
838 and ppa.action_type = pac.action_type
839 and pac.classification_name = 'SEQUENCED'
840 and not exists (select 1
841 from pay_payroll_actions ppa1,
842 pay_assignment_actions paa1
843 where ppa1.report_type = p_report_type
844 and ppa1.business_group_id+0 = p_bus_grp
845 and ppa1.effective_date = add_months(cp_effective_date, 12) - 1
846 and to_number(get_parameter('PRE_ORGANIZATION_ID',
847 ppa1.legislative_parameters)) = p_pre_id
848 and ppa1.payroll_action_id = paa1.payroll_action_id
849 and paa1.serial_number = to_char(paf.person_id))
850 order by paa.action_sequence desc;
851
852
853 cursor get_yepp_payroll_action(cp_effective_date date,
854 cp_pre_id number) is
855 select payroll_action_id
856 from pay_payroll_actions
857 where action_type = 'X'
858 and action_status = 'C'
859 and report_type = p_report_type
860 and business_group_id+0 = p_bus_grp
861 and to_number(get_parameter('PRE_ORGANIZATION_ID',legislative_parameters)) = cp_pre_id
862 and effective_date = add_months(cp_effective_date, 12) - 1;
863
864 /* we should always be stamping the primary assignment_id, even
865 if the assignment selected in the assignment set is secondary
866 Get the primary assignment for the given person_id */
867
868 cursor c_get_asg_id (cp_person_id number) is
869 select assignment_id
870 from per_all_assignments_f paf
871 where person_id = cp_person_id
872 and primary_flag = 'Y'
873 and assignment_type = 'E'
874 and paf.effective_start_date <= add_months(p_effective_date, 12) - 1
875 and paf.effective_end_date >= p_effective_date
876 order by assignment_id desc;
877
878 l_bal_aaid pay_assignment_actions.assignment_action_id%type;
879 ln_non_taxable_earnings number(30);
880 ln_gross_earnings number(30);
881 ln_no_gross_earnings number(30);
882 l_assignment_id per_all_assignments_f.assignment_id%type;
883 l_person_id per_all_assignments_f.person_id%type;
884 l_prev_person_id per_all_assignments_f.person_id%type;
885 l_yepp_payroll_action_id pay_payroll_actions.payroll_action_id%type;
886 lockingactid pay_assignment_actions.assignment_action_id%type;
887 l_tax_unit_id pay_assignment_actions.tax_unit_id%type;
888
889 BEGIN
890
891 hr_utility.set_location(gv_package_name || '.get_eligible_assignments_prov', 10);
892
893 open get_yepp_payroll_action(p_effective_date,
894 p_pre_id);
895 fetch get_yepp_payroll_action into l_yepp_payroll_action_id;
896 close get_yepp_payroll_action ;
897
898 open c_get_person_id (p_assignment_set_id);
899 loop
900
901 fetch c_get_person_id into l_person_id;
902 exit when c_get_person_id%NOTFOUND;
903
904 if (l_prev_person_id <> l_person_id) or
905 (l_prev_person_id is null) then
906
907 l_prev_person_id := l_person_id;
908
909 l_bal_aaid := 0;
910 ln_non_taxable_earnings := 0;
911 ln_gross_earnings := 0;
912 ln_no_gross_earnings := 0;
913
914 open c_get_asg_id (l_person_id);
915 fetch c_get_asg_id into l_assignment_id;
916 if c_get_asg_id%NOTFOUND then
917 close c_get_asg_id;
918 hr_utility.trace('Primary asg not found');
919 hr_utility.raise_error;
920 else
921 close c_get_asg_id;
922 end if;
923
924 open c_all_gres;
925 loop
926
927 fetch c_all_gres into l_tax_unit_id;
928 exit when c_all_gres%NOTFOUND;
929
930 /* Get the latest assignment action of selected person */
931
932 open c_get_latest_asg(l_person_id,
933 l_tax_unit_id,
934 p_effective_date);
935 fetch c_get_latest_asg into l_bal_aaid;
936
937 if c_get_latest_asg%NOTFOUND then
938 l_bal_aaid := -9999;
939 end if;
940
941 close c_get_latest_asg;
942
943 if l_bal_aaid <> -9999 then
944
945 hr_utility.trace('Setting context');
946
947 pay_ca_balance_view_pkg.set_context('TAX_UNIT_ID',l_tax_unit_id);
948 pay_ca_balance_view_pkg.set_context('ASSIGNMENT_ACTION_ID',l_bal_aaid);
949
950 if p_report_type = 'RL1' then
951
952 ln_gross_earnings := ln_gross_earnings +
953 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
954 ('Gross Earnings','YTD',l_bal_aaid,l_assignment_id,
955 NULL,'PER',l_tax_unit_id,p_bus_grp,'QC'),0);
956
957 ln_no_gross_earnings := ln_no_gross_earnings +
958 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
959 ('RL1 No Gross Earnings','YTD',l_bal_aaid,l_assignment_id,
960 NULL,'PER',l_tax_unit_id,p_bus_grp,'QC'),0);
961
962 ln_non_taxable_earnings := ln_non_taxable_earnings +
963 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
964 ('RL1 Non Taxable Earnings','YTD',l_bal_aaid,l_assignment_id,
965 NULL,'PER',l_tax_unit_id,p_bus_grp,'QC'),0);
966
967 elsif p_report_type = 'RL2' then
968
969 ln_gross_earnings := ln_gross_earnings +
970 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
971 ('Gross Earnings','YTD',l_bal_aaid,l_assignment_id,
972 NULL,'PER',l_tax_unit_id,p_bus_grp,'QC'),0);
973
974 ln_no_gross_earnings := ln_no_gross_earnings +
975 nvl(pay_ca_balance_pkg.call_ca_balance_get_value
976 ('RL2 No Gross Earnings','YTD',l_bal_aaid,l_assignment_id,
977 NULL,'PER',l_tax_unit_id,p_bus_grp,'QC'),0);
978
979 end if; /* p_report_type */
980
981 end if; /* l_bal_aaid <> -9999 */
982
983 end loop;
984 close c_all_gres;
985
986 if ( ((p_report_type = 'RL1') and
987 (
988 ((ln_gross_earnings <> 0) and
989 (ln_non_taxable_earnings <> ln_gross_earnings))
990 or
991 (ln_no_gross_earnings <> 0)
992 )
993 )
994 or ((p_report_type = 'RL2') and
995 ((ln_gross_earnings <> 0) or
996 (ln_no_gross_earnings <> 0)
997 )
998 )
999 ) then
1000
1001 hr_utility.set_location(gv_package_name || '.get_eligible_assignments_prov', 100);
1002
1003 select pay_assignment_actions_s.nextval
1004 into lockingactid
1005 from dual;
1006
1007 hr_utility.trace('creating asg action');
1008
1009 hr_nonrun_asact.insact(lockingactid => lockingactid,
1010 assignid => l_assignment_id,
1011 pactid => l_yepp_payroll_action_id,
1012 chunk => '1',
1013 greid => null,
1014 status => 'M');
1015
1016 hr_utility.set_location(gv_package_name || '.get_eligible_assignments_prov', 110);
1017
1018 update pay_assignment_actions aa
1019 set aa.serial_number = to_char(l_person_id)
1020 where aa.assignment_action_id = lockingactid;
1021
1022
1023 l_yepp_elgble_asg_table(l_assignment_id).c_assignment_id := l_assignment_id;
1024 l_all_reported_asg_table(l_assignment_id).c_assignment_id := l_assignment_id;
1025
1026 hr_utility.set_location(gv_package_name || '.get_eligible_assignments_prov', 150);
1027
1028 end if;
1029
1030 end if; /* l_prev_person_id <> l_person_id */
1031
1032 end loop;
1033 close c_get_person_id;
1034
1035 EXCEPTION
1036 WHEN OTHERS THEN
1037 hr_utility.trace('Error in '|| gv_package_name || '.get_eligible_assignments_prov');
1038 raise;
1039
1040 END get_eligible_assignments_prov;
1041
1042 /******************************************************************
1043 Procedure get_processed_assignments
1044 Description : Gets the list of all assignments from the
1045 assignment set which are processed by the year end process
1046 ******************************************************************/
1047
1048 PROCEDURE get_processed_assignments(p_effective_date in date,
1049 p_gre_id in number,
1050 p_pre_id in number,
1051 p_assignment_set_id in number,
1052 p_report_type in varchar2)
1053 IS
1054
1055 cursor c_get_processed_asg_fed(cp_effective_date date,
1056 cp_gre_id number,
1057 cp_assignment_set_id number) is
1058 select distinct has.assignment_id
1059 from hr_assignment_set_amendments has,
1060 per_all_assignments_f paf
1061 where has.assignment_set_id = cp_assignment_set_id
1062 and paf.assignment_id = has.assignment_id
1063 and paf.assignment_type = 'E'
1064 and paf.primary_flag = 'Y'
1065 and paf.business_group_id+0 = p_bus_grp
1066 and paf.effective_start_date <= add_months(cp_effective_date, 12) - 1
1067 and paf.effective_end_date >= cp_effective_date
1068 and exists (select 1
1069 from pay_payroll_actions ppa1,
1070 pay_assignment_actions paa1
1071 where ppa1.report_type = p_report_type
1072 and ppa1.business_group_id+0 = p_bus_grp
1073 and ppa1.effective_date = add_months(cp_effective_date, 12) - 1
1074 and to_number(get_parameter('TRANSFER_GRE', ppa1.legislative_parameters))
1075 = cp_gre_id
1076 and ppa1.payroll_action_id = paa1.payroll_action_id
1077 and paa1.serial_number = to_char(paf.person_id));
1078
1079
1080 cursor c_get_processed_asg_prov(cp_effective_date date,
1081 cp_pre_id number,
1082 cp_assignment_set_id number) is
1083 select distinct has.assignment_id
1084 from hr_assignment_set_amendments has,
1085 per_all_assignments_f paf
1086 where has.assignment_set_id = cp_assignment_set_id
1087 and paf.assignment_id = has.assignment_id
1088 and paf.assignment_type = 'E'
1089 and paf.primary_flag = 'Y'
1090 and paf.business_group_id+0 = p_bus_grp
1091 and paf.effective_start_date <= add_months(cp_effective_date, 12) - 1
1092 and paf.effective_end_date >= cp_effective_date
1093 and exists (select 1
1094 from pay_payroll_actions ppa1,
1095 pay_assignment_actions paa1
1096 where ppa1.report_type = p_report_type
1097 and ppa1.business_group_id+0 = p_bus_grp
1098 and ppa1.effective_date = add_months(cp_effective_date, 12) - 1
1099 and to_number(get_parameter('PRE_ORGANIZATION_ID', ppa1.legislative_parameters))
1100 = cp_pre_id
1101 and ppa1.payroll_action_id = paa1.payroll_action_id
1102 and paa1.serial_number = to_char(paf.person_id));
1103
1104 l_processed_assignment_id per_all_assignments_f.assignment_id%type;
1105
1106 BEGIN
1107
1108 if p_report_type in ('T4','T4A') then
1109
1110 open c_get_processed_asg_fed(p_effective_date ,
1111 p_gre_id,
1112 p_assignment_set_id);
1113 loop
1114
1115 fetch c_get_processed_asg_fed into l_processed_assignment_id;
1116 exit when c_get_processed_asg_fed%notfound;
1117
1118 hr_utility.set_location(gv_package_name || '.get_processed_assignments', 20);
1119
1120 if l_yepp_elgble_asg_table.exists(l_processed_assignment_id) then
1121
1122 hr_utility.trace('Assignment Exists');
1123
1124 else
1125
1126 l_yepp_prc_asg_table(l_processed_assignment_id).c_assignment_id := l_processed_assignment_id;
1127 l_all_reported_asg_table(l_processed_assignment_id).c_assignment_id := l_processed_assignment_id;
1128
1129 end if;
1130
1131 end loop;
1132 close c_get_processed_asg_fed;
1133
1134 elsif p_report_type in ('RL1','RL2') then
1135
1136 open c_get_processed_asg_prov(p_effective_date ,
1137 p_pre_id,
1138 p_assignment_set_id);
1139 loop
1140
1141 fetch c_get_processed_asg_prov into l_processed_assignment_id;
1142 exit when c_get_processed_asg_prov%notfound;
1143
1144 hr_utility.set_location(gv_package_name || '.get_processed_assignments', 20);
1145
1146 if l_yepp_elgble_asg_table.exists(l_processed_assignment_id) then
1147
1148 hr_utility.trace('Assignment Exists');
1149
1150 else
1151
1152 l_yepp_prc_asg_table(l_processed_assignment_id).c_assignment_id := l_processed_assignment_id;
1153 l_all_reported_asg_table(l_processed_assignment_id).c_assignment_id := l_processed_assignment_id;
1154
1155 end if;
1156
1157 end loop;
1158 close c_get_processed_asg_prov;
1159
1160 end if;
1161
1162 EXCEPTION
1163 WHEN OTHERS THEN
1164 hr_utility.trace('Error in '|| gv_package_name || '.get_processed_assignments');
1165 raise;
1166 END get_processed_assignments;
1167
1168
1169 /******************************************************************
1170 Procedure get_non_elgble_assignments
1171 Description : Gets the list of all primary assignments from the
1172 assignment set which are not eligible for the year
1173 end process.
1174 ******************************************************************/
1175
1176 PROCEDURE get_non_elgble_assignments(p_assignment_set_id in number,
1177 p_gre_id in number,
1178 p_pre_id in number,
1179 p_effective_date in date,
1180 p_report_type in varchar2)
1181 IS
1182
1183 cursor c_get_assignments(cp_assignment_set_id number,
1184 cp_gre_id number,
1185 cp_effective_date date) is
1186 select distinct has.assignment_id
1187 from hr_assignment_set_amendments has,
1188 per_all_assignments_f paf,
1189 pay_assignment_actions paa,
1190 pay_payroll_actions ppa
1191 where has.assignment_set_id = cp_assignment_set_id
1192 and paf.assignment_id = has.assignment_id
1193 and nvl(has.include_or_exclude,'I') = 'I'
1194 and paf.effective_start_date <= add_months(cp_effective_date, 12) - 1
1195 and paf.effective_end_date >= cp_effective_date
1196 and paf.business_group_id+0 = p_bus_grp
1197 and paa.assignment_id = paf.assignment_id
1198 and paa.tax_unit_id = cp_gre_id
1199 and ppa.business_group_id+0 = p_bus_grp
1200 and ppa.payroll_action_id = paa.payroll_action_id
1201 and ppa.action_type in ('R','Q','V','B','I')
1202 and ppa.effective_date between cp_effective_date
1203 and add_months(cp_effective_date, 12) - 1
1204 and paf.assignment_type = 'E'
1205 and paf.primary_flag = 'Y';
1206
1207 cursor c_all_gres is
1208 select hoi.organization_id
1209 from hr_organization_information hoi,
1210 hr_all_organization_units hou
1211 where hoi.org_information_context = 'Canada Employer Identification'
1212 and hoi.org_information2 = to_char(p_pre_id)
1213 and hou.business_group_id = p_bus_grp
1214 and hou.organization_id = hoi.organization_id;
1215
1216 l_assignment_id per_all_assignments_f.assignment_id%type;
1217 l_gre pay_assignment_actions.tax_unit_id%type;
1218
1219 BEGIN
1220
1221 if p_report_type in ('T4','T4A') then
1222
1223 open c_get_assignments(p_assignment_set_id, p_gre_id, p_effective_date);
1224
1225 loop
1226
1227 fetch c_get_assignments into l_assignment_id;
1228 exit when c_get_assignments%notfound;
1229
1230 if l_yepp_elgble_asg_table.exists(l_assignment_id) then
1231
1232 hr_utility.set_location(gv_package_name || '.get_non_elgble_assignments', 30);
1233 hr_utility.trace('Assignment Exists');
1234
1235 elsif l_yepp_prc_asg_table.exists(l_assignment_id) then
1236
1237 hr_utility.trace('Assignment Exists');
1238 hr_utility.set_location(gv_package_name || '.get_non_elgble_assignments', 40);
1239
1240 else
1241
1242 l_yepp_not_elgble_asg_table(l_assignment_id).c_assignment_id := l_assignment_id;
1243 l_all_reported_asg_table(l_assignment_id).c_assignment_id := l_assignment_id;
1244
1245 end if;
1246
1247 end loop;
1248 close c_get_assignments;
1249
1250 elsif p_report_type in ('RL1','RL2') then
1251
1252 open c_all_gres;
1253 loop
1254
1255 fetch c_all_gres into l_gre;
1256 exit when c_all_gres%notfound;
1257
1258 open c_get_assignments(p_assignment_set_id, l_gre, p_effective_date);
1259 loop
1260 fetch c_get_assignments into l_assignment_id;
1261 exit when c_get_assignments%notfound;
1262
1263 if l_yepp_elgble_asg_table.exists(l_assignment_id) then
1264
1265 hr_utility.set_location(gv_package_name || '.get_non_elgble_assignments', 30);
1266 hr_utility.trace('Assignment Exists');
1267
1268 elsif l_yepp_prc_asg_table.exists(l_assignment_id) then
1269
1270 hr_utility.trace('Assignment Exists');
1271 hr_utility.set_location(gv_package_name || '.get_non_elgble_assignments', 40);
1272
1273 else
1274
1275 l_yepp_not_elgble_asg_table(l_assignment_id).c_assignment_id := l_assignment_id;
1276 l_all_reported_asg_table(l_assignment_id).c_assignment_id := l_assignment_id;
1277
1278 end if;
1279
1280 end loop;
1281 close c_get_assignments;
1282
1283 end loop;
1284 close c_all_gres;
1285
1286 end if;
1287
1288 EXCEPTION
1289 WHEN OTHERS THEN
1290 hr_utility.trace('Error in '|| gv_package_name || '.get_non_elgble_assignments');
1291 raise;
1292
1293 END get_non_elgble_assignments;
1294
1295
1296 /******************************************************************
1297 Procedure print_table_details
1298 Description : prints the table details in HTML format
1299 ******************************************************************/
1300 PROCEDURE print_table_details(p_assignment_id in number,
1301 p_report_type in varchar2)
1302 IS
1303 BEGIN
1304
1305 -- Get person_id of the employee
1306 open c_person_id(p_assignment_id);
1307 fetch c_person_id into lv_person_id;
1308 close c_person_id;
1309
1310 -- Get Assignment Number
1311 open c_assignment_no(p_assignment_id);
1312 fetch c_assignment_no into lv_emp_no;
1313 close c_assignment_no;
1314
1315 -- Get Employee Details
1316 open c_employee_details(lv_person_id);
1317 fetch c_employee_details into lv_emp_name,lv_emp_sin;
1318 close c_employee_details;
1319
1320 l_assignment_inserted := l_assignment_inserted + 1;
1321 lv_data_row := formated_detail_string(
1322 p_output_file_type
1323 ,to_char(l_effective_date,'YYYY')
1324 ,lv_gre_name
1325 ,lv_pre_name
1326 ,lv_emp_name
1327 ,lv_emp_sin
1328 ,lv_emp_no
1329 ,p_report_type);
1330 if p_output_file_type ='HTML' then
1331 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
1332 end if;
1333
1334 fnd_file.put_line(fnd_file.output, lv_data_row);
1335
1336 END print_table_details;
1337
1338 /******************************************************************
1339 Procedure report_secondary_assignments
1340 Description : Gets the list of secondary assignments from the
1341 assignment set and report them
1342 ******************************************************************/
1343 PROCEDURE report_secondary_assignments(p_assignment_set_id in number,
1344 p_gre_id in number,
1345 p_pre_id in number,
1346 p_effective_date in date,
1347 p_report_type in varchar2)
1348 IS
1349
1350 cursor c_secondary_asg_fed(cp_assignment_set_id number,
1351 cp_gre_id number,
1352 cp_effective_date date) is
1353 select distinct has.assignment_id
1354 from hr_assignment_set_amendments has,
1355 per_all_assignments_f paf,
1356 pay_assignment_actions paa,
1357 pay_payroll_actions ppa
1358 where has.assignment_set_id = cp_assignment_set_id
1359 and paf.assignment_id = has.assignment_id
1360 and nvl(has.include_or_exclude,'I') = 'I'
1361 and paf.effective_start_date <= add_months(cp_effective_date, 12) - 1
1362 and paf.effective_end_date >= cp_effective_date
1363 and paf.business_group_id+0 = p_bus_grp
1364 and paa.assignment_id = paf.assignment_id
1365 and ppa.business_group_id+0 = p_bus_grp
1366 and ppa.payroll_action_id = paa.payroll_action_id
1367 and ppa.action_type in ('R','Q','V','B','I')
1368 and ppa.effective_date between cp_effective_date
1369 and add_months(cp_effective_date, 12) - 1
1370 and paa.tax_unit_id = cp_gre_id
1371 and paf.assignment_type = 'E'
1372 and paf.primary_flag <> 'Y';
1373
1374 cursor c_secondary_asg_prov(cp_assignment_set_id number,
1375 cp_pre_id number,
1376 cp_effective_date date) is
1377 select distinct has.assignment_id
1378 from hr_assignment_set_amendments has,
1379 per_all_assignments_f paf,
1380 pay_assignment_actions paa,
1381 pay_payroll_actions ppa
1382 where has.assignment_set_id = cp_assignment_set_id
1383 and paf.assignment_id = has.assignment_id
1384 and nvl(has.include_or_exclude,'I') = 'I'
1385 and paf.effective_start_date <= add_months(cp_effective_date, 12) - 1
1386 and paf.effective_end_date >= cp_effective_date
1387 and paf.business_group_id+0 = p_bus_grp
1388 and paa.assignment_id = paf.assignment_id
1389 and ppa.business_group_id+0 = p_bus_grp
1390 and ppa.payroll_action_id = paa.payroll_action_id
1391 and ppa.action_type in ('R','Q','V','B','I')
1392 and ppa.effective_date between cp_effective_date
1393 and add_months(cp_effective_date, 12) - 1
1394 and paf.assignment_type = 'E'
1395 and paf.primary_flag <> 'Y'
1396 and paa.tax_unit_id in (select hoi.organization_id
1397 from hr_organization_information hoi,
1398 hr_all_organization_units hou
1399 where hoi.org_information_context = 'Canada Employer Identification'
1400 and hoi.org_information2 = to_char(cp_pre_id)
1401 and hou.business_group_id = p_bus_grp
1402 and hou.organization_id = hoi.organization_id);
1403
1404 l_assignment_id per_all_assignments_f.assignment_id%type;
1405 l_count number;
1406 l_header_printed varchar2(1);
1407
1408 BEGIN
1409
1410 l_count := 0;
1411 l_header_printed := 'N';
1412
1413 if p_report_type in ('T4','T4A') then
1414
1415 open c_secondary_asg_fed(p_assignment_set_id,
1416 p_gre_id,
1417 p_effective_date);
1418 loop
1419
1420 fetch c_secondary_asg_fed into l_assignment_id;
1421 exit when c_secondary_asg_fed%notfound;
1422
1423 if l_yepp_elgble_asg_table.exists(l_assignment_id) then
1424
1425 hr_utility.set_location(gv_package_name || '.report_secondary_assignments', 20);
1426 hr_utility.trace('Assignment Exists');
1427
1428 elsif l_yepp_prc_asg_table.exists(l_assignment_id) then
1429
1430 hr_utility.trace('Assignment Exists');
1431 hr_utility.set_location(gv_package_name || '.report_secondary_assignments', 30);
1432
1433 elsif l_yepp_not_elgble_asg_table.exists(l_assignment_id) then
1434
1435 hr_utility.trace('Assignment Exists');
1436 hr_utility.set_location(gv_package_name || '.report_secondary_assignments', 40);
1437
1438 else
1439
1440 l_count := l_count + 1 ;
1441
1442 if l_header_printed = 'N' then
1443
1444 print_table_header('4. '||l_secasg_table_header, p_report_type, p_output_file_type);
1445 l_header_printed := 'Y';
1446
1447 end if;
1448
1449 print_table_details(l_assignment_id, p_report_type);
1450 l_all_reported_asg_table(l_assignment_id).c_assignment_id := l_assignment_id;
1451
1452 end if;
1453
1454 end loop;
1455 close c_secondary_asg_fed;
1456
1457 if p_output_file_type ='HTML' then
1458 fnd_file.put_line(fnd_file.output,'</table>') ;
1459 end if;
1460
1461
1462 elsif p_report_type in ('RL1','RL2') then
1463
1464 open c_secondary_asg_prov(p_assignment_set_id,
1465 p_pre_id,
1466 p_effective_date);
1467 loop
1468
1469 fetch c_secondary_asg_prov into l_assignment_id;
1470 exit when c_secondary_asg_prov%notfound;
1471
1472 if l_yepp_elgble_asg_table.exists(l_assignment_id) then
1473
1474 hr_utility.set_location(gv_package_name || '.report_secondary_assignments', 20);
1475 hr_utility.trace('Assignment Exists');
1476
1477 elsif l_yepp_prc_asg_table.exists(l_assignment_id) then
1478
1479 hr_utility.trace('Assignment Exists');
1480 hr_utility.set_location(gv_package_name || '.report_secondary_assignments', 30);
1481
1482 elsif l_yepp_not_elgble_asg_table.exists(l_assignment_id) then
1483
1484 hr_utility.trace('Assignment Exists');
1485 hr_utility.set_location(gv_package_name || '.report_secondary_assignments', 40);
1486
1487 else
1488
1489 l_count := l_count + 1 ;
1490
1491 if l_header_printed = 'N' then
1492
1493 print_table_header('4. '||l_secasg_table_header, p_report_type, p_output_file_type);
1494 l_header_printed := 'Y';
1495
1496 end if;
1497
1498 print_table_details(l_assignment_id, p_report_type);
1499 l_all_reported_asg_table(l_assignment_id).c_assignment_id := l_assignment_id;
1500
1501 end if;
1502
1503 end loop;
1504 close c_secondary_asg_prov;
1505
1506 if p_output_file_type ='HTML' then
1507 fnd_file.put_line(fnd_file.output,'</table>') ;
1508 end if;
1509
1510 end if;
1511
1512 if l_count > 0 then
1513 gv_sec_asg_reported := 'Y';
1514 end if;
1515
1516 EXCEPTION
1517 WHEN OTHERS THEN
1518 hr_utility.trace('Error in '|| gv_package_name || '.report_secondary_assignments');
1519 raise;
1520
1521 END report_secondary_assignments;
1522
1523
1524 /******************************************************************
1525 Procedure report_other_assignments
1526 Description : Gets the list of assignments in the assignment set
1527 but in different GRE/PRE than entered as parameter
1528 ******************************************************************/
1529 PROCEDURE report_other_assignments(p_assignment_set_id in number,
1530 p_gre_id in number,
1531 p_pre_id in number,
1532 p_effective_date in date,
1533 p_report_type in varchar2)
1534
1535 IS
1536 cursor c_other_assignments_fed(cp_assignment_set_id number,
1537 cp_gre_id number,
1538 cp_effective_date date) is
1539 select distinct has.assignment_id,
1540 paa.tax_unit_id
1541 from hr_assignment_set_amendments has,
1542 per_all_assignments_f paf,
1543 pay_assignment_actions paa,
1544 pay_payroll_actions ppa
1545 where has.assignment_set_id = cp_assignment_set_id
1546 and paf.assignment_id = has.assignment_id
1547 and nvl(has.include_or_exclude,'I') = 'I'
1548 and paf.effective_start_date <= add_months(cp_effective_date, 12) - 1
1549 and paf.effective_end_date >= cp_effective_date
1550 and paf.business_group_id+0 = p_bus_grp
1551 and paf.assignment_type = 'E'
1552 and paa.assignment_id = paf.assignment_id
1553 and ppa.business_group_id+0 = p_bus_grp
1554 and ppa.payroll_action_id = paa.payroll_action_id
1555 and ppa.action_type in ('R','Q','V','B','I')
1556 and ppa.effective_date between cp_effective_date
1557 and add_months(cp_effective_date, 12) - 1
1558 and nvl(paa.tax_unit_id, cp_gre_id) <> cp_gre_id;
1559
1560 cursor c_other_assignments_prov(cp_assignment_set_id number,
1561 cp_pre_id number,
1562 cp_effective_date date) is
1563 select distinct has.assignment_id,
1564 hoi.org_information2
1565 from hr_assignment_set_amendments has,
1566 per_all_assignments_f paf,
1567 pay_assignment_actions paa,
1568 pay_payroll_actions ppa,
1569 hr_organization_information hoi
1570 where has.assignment_set_id = cp_assignment_set_id
1571 and paf.assignment_id = has.assignment_id
1572 and nvl(has.include_or_exclude,'I') = 'I'
1573 and paf.effective_start_date <= add_months(cp_effective_date, 12) - 1
1574 and paf.effective_end_date >= cp_effective_date
1575 and paf.business_group_id+0 = p_bus_grp
1576 and paf.assignment_type = 'E'
1577 and paa.assignment_id = paf.assignment_id
1578 and ppa.business_group_id+0 = p_bus_grp
1579 and ppa.payroll_action_id = paa.payroll_action_id
1580 and ppa.action_type in ('R','Q','V','B','I')
1581 and ppa.effective_date between cp_effective_date
1582 and add_months(cp_effective_date, 12) - 1
1583 and paa.tax_unit_id = hoi.organization_id
1584 and hoi.org_information_context = 'Canada Employer Identification'
1585 and paa.tax_unit_id not in (select hoi1.organization_id
1586 from hr_organization_information hoi1,
1587 hr_all_organization_units hou1
1588 where hoi1.org_information_context = 'Canada Employer Identification'
1589 and hoi1.org_information2 = to_char(cp_pre_id)
1590 and hou1.business_group_id = p_bus_grp
1591 and hou1.organization_id = hoi1.organization_id);
1592
1593 cursor c_get_name(cp_org_id number) is
1594 select name
1595 from hr_all_organization_units_tl
1596 where organization_id = cp_org_id
1597 and language = userenv('LANG');
1598
1599 l_oth_assignment_id per_all_assignments_f.assignment_id%type;
1600 l_gre_id pay_assignment_actions.tax_unit_id%type;
1601 l_pre_id pay_assignment_actions.tax_unit_id%type;
1602 l_header_printed varchar2(1) := 'N';
1603
1604 BEGIN
1605
1606 if gv_sec_asg_reported = 'Y' then
1607 l_othasg_table_header := '5. '||l_othasg_table_header;
1608 else
1609 l_othasg_table_header := '4. '||l_othasg_table_header;
1610 end if;
1611
1612 if p_report_type in ('T4A','T4') then
1613
1614 open c_other_assignments_fed(p_assignment_set_id,
1615 p_gre_id,
1616 p_effective_date);
1617 loop
1618
1619 fetch c_other_assignments_fed into l_oth_assignment_id, l_gre_id;
1620 exit when c_other_assignments_fed%notfound;
1621
1622 if l_all_reported_asg_table.exists(l_oth_assignment_id) then
1623
1624 hr_utility.trace('The assignment already reported above');
1625
1626 else
1627 open c_get_name(l_gre_id);
1628 fetch c_get_name into lv_gre_name;
1629 close c_get_name;
1630
1631 if l_header_printed = 'N' then
1632
1633 print_table_header(l_othasg_table_header, p_report_type, p_output_file_type);
1634 l_header_printed := 'Y';
1635
1636 end if;
1637
1638 print_table_details(l_oth_assignment_id, p_report_type);
1639 hr_utility.set_location(gv_package_name || '.report_other_assignments', 50);
1640
1641 end if;
1642
1643 end loop;
1644 close c_other_assignments_fed;
1645
1646 if p_output_file_type ='HTML' then
1647 fnd_file.put_line(fnd_file.output,'</table>') ;
1648 end if;
1649
1650 elsif p_report_type in ('RL1','RL2') then
1651
1652 open c_other_assignments_prov(p_assignment_set_id,
1653 p_pre_id,
1654 p_effective_date);
1655 loop
1656
1657 fetch c_other_assignments_prov into l_oth_assignment_id, l_pre_id;
1658 exit when c_other_assignments_prov%notfound;
1659
1660 if l_all_reported_asg_table.exists(l_oth_assignment_id) then
1661
1662 hr_utility.trace('The assignment already reported above');
1663
1664 else
1665
1666 if l_pre_id is null then
1667 lv_pre_name := hr_general.decode_lookup('PAY_CA_MISSING_ASG','NO_PRE');
1668 else
1669 open c_get_name(l_pre_id);
1670 fetch c_get_name into lv_pre_name;
1671 close c_get_name;
1672 end if;
1673
1674 if l_header_printed = 'N' then
1675
1676 print_table_header(l_othasg_table_header, p_report_type, p_output_file_type);
1677 l_header_printed := 'Y';
1678
1679 end if;
1680
1681 print_table_details(l_oth_assignment_id, p_report_type);
1682 hr_utility.set_location(gv_package_name || '.report_other_assignments', 50);
1683
1684 end if;
1685
1686 end loop;
1687 close c_other_assignments_prov;
1688
1689 if p_output_file_type ='HTML' then
1690 fnd_file.put_line(fnd_file.output,'</table>') ;
1691 end if;
1692
1693 end if;
1694
1695 EXCEPTION
1696 WHEN OTHERS THEN
1697 hr_utility.trace('Error in '|| gv_package_name || '.report_other_assignments');
1698 raise;
1699
1700 END report_other_assignments;
1701
1702 --------------------------------------------------------------------
1703 -- The Main Procedure Begins Here
1704 --------------------------------------------------------------------
1705 BEGIN
1706
1707 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
1708
1709 -- Add Assignment Actions Report
1710 gv_title := hr_general.decode_lookup('PAY_CA_MISSING_ASG','ADD_ASG_HEADING');
1711
1712 l_elgbl_table_header := '2. '||hr_general.decode_lookup('PAY_CA_MISSING_ASG','ELIGIBLE')||':';
1713 l_prced_table_header := '1. '||hr_general.decode_lookup('PAY_CA_MISSING_ASG','PROCESSED')||':';
1714 l_nonelgbl_table_header := '3. '||hr_general.decode_lookup('PAY_CA_MISSING_ASG','NONELIGIBLE')||':';
1715 l_secasg_table_header := hr_general.decode_lookup('PAY_CA_MISSING_ASG','SECONDARY')||':';
1716
1717 if p_report_type in ('T4','T4A') then
1718
1719 l_othasg_table_header := hr_general.decode_lookup('PAY_CA_MISSING_ASG','OTHER')||' '||
1720 hr_general.decode_lookup('PAY_CA_MISSING_ASG','OTHER_GRE')||':';
1721
1722 open c_name(p_gre_id);
1723 fetch c_name into lv_gre_name;
1724 close c_name;
1725
1726 elsif p_report_type in ('RL1','RL2') then
1727
1728 l_othasg_table_header := hr_general.decode_lookup('PAY_CA_MISSING_ASG','OTHER')||' '||
1729 hr_general.decode_lookup('PAY_CA_MISSING_ASG','OTHER_PRE')||':';
1730
1731 open c_name(p_pre_id);
1732 fetch c_name into lv_pre_name;
1733 close c_name;
1734
1735 end if;
1736
1737 if p_report_type = 'T4' then
1738
1739 open c_t4_magtape_run_exists(l_effective_date,
1740 p_bus_grp,
1741 p_gre_id);
1742 fetch c_t4_magtape_run_exists into l_temp;
1743
1744 if c_t4_magtape_run_exists%found then
1745 l_mag_exists := 1;
1746 end if;
1747
1748 close c_t4_magtape_run_exists;
1749
1750 elsif p_report_type = 'T4A' then
1751
1752 open c_t4a_magtape_run_exists(l_effective_date,
1753 p_bus_grp,
1754 p_gre_id);
1755 fetch c_t4a_magtape_run_exists into l_temp;
1756
1757 if c_t4a_magtape_run_exists%found then
1758 l_mag_exists := 1;
1759 end if;
1760
1761 close c_t4a_magtape_run_exists;
1762
1763 elsif p_report_type = 'RL1' then
1764
1765 open c_rl1_magtape_run_exists(l_effective_date,
1766 p_bus_grp,
1767 p_pre_id);
1768 fetch c_rl1_magtape_run_exists into l_temp;
1769
1770 if c_rl1_magtape_run_exists%found then
1771 l_mag_exists := 1;
1772 end if;
1773
1774 close c_rl1_magtape_run_exists;
1775
1776 elsif p_report_type = 'RL2' then
1777
1778 open c_rl2_magtape_run_exists(l_effective_date,
1779 p_bus_grp,
1780 p_pre_id);
1781 fetch c_rl2_magtape_run_exists into l_temp;
1782
1783 if c_rl2_magtape_run_exists%found then
1784 l_mag_exists := 1;
1785 end if;
1786
1787 close c_rl2_magtape_run_exists;
1788
1789 end if;
1790
1791 fnd_file.put_line(fnd_file.output,
1792 pay_us_payroll_utils.formated_header_string(gv_title || ' - ' || p_report_type || ' ' ||
1793 to_char(l_effective_date,'YYYY'),p_output_file_type));
1794
1795 if p_output_file_type ='HTML' then
1796 fnd_file.put_line(fnd_file.output, '<body>');
1797 end if;
1798
1799 if l_mag_exists = 1 then -- Magnetic tape processed
1800
1801 if p_output_file_type ='HTML' then
1802 fnd_file.put_line(fnd_file.output, '<br><br><table align=center>');
1803 end if;
1804
1805 if p_output_file_type ='HTML' then
1806 fnd_file.put_line(fnd_file.output, '<tr>');
1807 end if;
1808
1809 fnd_file.put_line(fnd_file.output,
1810 pay_us_payroll_utils.formated_data_string
1811 (p_input_string => hr_general.decode_lookup('PAY_CA_MISSING_ASG','MAG_RUN')
1812 ,p_bold => 'Y'
1813 ,p_output_file_type => p_output_file_type));
1814
1815 if p_output_file_type ='HTML' then
1816 fnd_file.put_line(fnd_file.output, '</tr>');
1817 fnd_file.put_line(fnd_file.output, '<tr>');
1818 end if;
1819
1820 fnd_file.put_line(fnd_file.output,
1821 pay_us_payroll_utils.formated_data_string
1822 (p_input_string => hr_general.decode_lookup('PAY_CA_MISSING_ASG','MAG_ROLLBACK')
1823 ,p_bold => 'Y'
1824 ,p_output_file_type => p_output_file_type));
1825
1826 if p_output_file_type ='HTML' then
1827 fnd_file.put_line(fnd_file.output, '</tr> </table> </body> </HTML>');
1828 end if;
1829
1830
1831 else -- Magnetic tape not processed
1832
1833 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
1834
1835 if p_report_type in ('T4','T4A') then
1836
1837 get_eligible_assignments_fed(l_effective_date,
1838 p_gre_id,
1839 p_assign_set,
1840 p_report_type);
1841
1842 elsif p_report_type in ('RL1','RL2') then
1843
1844 get_eligible_assignments_prov(l_effective_date,
1845 p_pre_id,
1846 p_assign_set,
1847 p_report_type);
1848 end if;
1849
1850 get_processed_assignments(l_effective_date,
1851 p_gre_id,
1852 p_pre_id,
1853 p_assign_set,
1854 p_report_type);
1855
1856 get_non_elgble_assignments(p_assign_set,
1857 p_gre_id,
1858 p_pre_id,
1859 l_effective_date,
1860 p_report_type);
1861
1862 /***Start Formating of the output for already Processed Assignments****/
1863
1864 if l_yepp_prc_asg_table.count > 0 then
1865
1866 print_table_header(l_prced_table_header, p_report_type, p_output_file_type);
1867
1868 for l_assignment_id in l_yepp_prc_asg_table.first..l_yepp_prc_asg_table.last
1869 loop
1870 if l_yepp_prc_asg_table.exists(l_assignment_id) then
1871
1872 print_table_details(l_yepp_prc_asg_table(l_assignment_id).c_assignment_id, p_report_type);
1873 end if;
1874 end loop;
1875
1876 if p_output_file_type ='HTML' then
1877 fnd_file.put_line(fnd_file.output,'</table>') ;
1878 end if;
1879
1880 end if;
1881
1882 if l_assignment_inserted = 0 then
1883 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 110);
1884 formated_zero_count(p_output_file_type,'PROCESSED');
1885 end if;
1886
1887
1888 /***Start Formating of the out put for all Eligible Assignments****/
1889
1890 l_assignment_inserted := 0;
1891 if l_yepp_elgble_asg_table.count > 0 then
1892
1893 print_table_header(l_elgbl_table_header, p_report_type, p_output_file_type);
1894
1895 for l_assignment_id in l_yepp_elgble_asg_table.first..l_yepp_elgble_asg_table.last
1896 loop
1897
1898 if l_yepp_elgble_asg_table.exists(l_assignment_id) then
1899
1900 print_table_details(l_yepp_elgble_asg_table(l_assignment_id).c_assignment_id, p_report_type);
1901
1902 end if;
1903 end loop;
1904
1905 if p_output_file_type ='HTML' then
1906 fnd_file.put_line(fnd_file.output,'</table>') ;
1907 end if;
1908 end if;
1909
1910 hr_utility.set_location(gv_package_name || '.add_actions_to_yepp', 150);
1911
1912 if l_assignment_inserted = 0 then
1913 formated_zero_count(p_output_file_type,'ELGBLE');
1914 end if;
1915
1916
1917 /***Start Formating of the output for Non Eligible Assignments*****/
1918
1919 l_assignment_inserted := 0;
1920 if l_yepp_not_elgble_asg_table.count > 0 then
1921
1922 print_table_header(l_nonelgbl_table_header, p_report_type, p_output_file_type);
1923
1924 for l_assignment_id in l_yepp_not_elgble_asg_table.first..l_yepp_not_elgble_asg_table.last
1925 loop
1926
1927 if l_yepp_not_elgble_asg_table.exists(l_assignment_id) then
1928
1929 print_table_details(l_yepp_not_elgble_asg_table(l_assignment_id).c_assignment_id, p_report_type);
1930
1931 end if;
1932 end loop;
1933
1934 if p_output_file_type ='HTML' then
1935 fnd_file.put_line(fnd_file.output,'</table>') ;
1936 end if;
1937
1938 end if;
1939
1940 if l_assignment_inserted = 0 then
1941 formated_zero_count(p_output_file_type,'NOTELGBLE');
1942 end if;
1943
1944 report_secondary_assignments(p_assign_set,
1945 p_gre_id,
1946 p_pre_id,
1947 l_effective_date,
1948 p_report_type);
1949
1950 report_other_assignments(p_assign_set,
1951 p_gre_id,
1952 p_pre_id,
1953 l_effective_date,
1954 p_report_type);
1955
1956 if p_output_file_type ='HTML' then
1957 fnd_file.put_line(fnd_file.output, '</body> </HTML>');
1958 end if;
1959
1960 end if; -- Magnetic tape not processed
1961
1962 if p_output_file_type ='HTML' then
1963
1964 update fnd_concurrent_requests
1965 set output_file_type = 'HTML'
1966 where request_id = FND_GLOBAL.CONC_REQUEST_ID ;
1967
1968 commit;
1969
1970 end if;
1971
1972 EXCEPTION
1973 WHEN OTHERS THEN
1974 hr_utility.trace('Error in '|| gv_package_name || '.add_actions_to_yepp');
1975 raise;
1976
1977 END add_actions_to_yepp;
1978
1979 END pay_ca_yepp_add_actions_pkg;