[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_YEPP_MISS_ASSIGN_PKG
Source
1 PACKAGE BODY pay_ca_yepp_miss_assign_pkg AS
2 /* $Header: pycayema.pkb 120.0 2005/05/29 03:55 appldev noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1996 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 Name : pay_ca_yepp_miss_assign_pkg
20 File : pycayema.pkb
21 Description : Package for the YEPP missing assignments report.
22 The package generates the output file in the specified
23 user format. The current formats supported are
24 - HTML
25 - CSV
26 Change List
27 -----------
28 Date Name Vers Bug No Description
29 ---- ---- ------ ------- -----------
30 11-OCT-2004 ssouresr 115.0 3562508 Created.
31 06-NOV-2004 ssouresr 115.1 Using tables instead of
32 restricted views
33
34 /************************************************************
35 ** Local Package Variables
36 ************************************************************/
37
38 gv_title VARCHAR2(100);
39 gv_package_name VARCHAR2(50) := 'pay_ca_yepp_miss_assign_pkg';
40
41 /**********************************************************************
42 Function to fetch the Parameter Value from Legislative Parameter
43 *********************************************************************/
44
45 function get_parameter(name in varchar2,
46 parameter_list varchar2) return varchar2
47 is
48 start_ptr number;
49 end_ptr number;
50 token_val pay_payroll_actions.legislative_parameters%type;
51 par_value pay_payroll_actions.legislative_parameters%type;
52 begin
53
54 token_val := name||'=';
55
56 start_ptr := instr(parameter_list, token_val) + length(token_val);
57 end_ptr := instr(parameter_list, ' ', start_ptr);
58
59 /* if there is no spaces use then length of the string */
60 if end_ptr = 0 then
61 end_ptr := length(parameter_list)+1;
62 end if;
63
64 /* Did we find the token */
65 if instr(parameter_list, token_val) = 0 then
66 par_value := NULL;
67 else
68 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
69 end if;
70
71 return par_value;
72
73 end get_parameter;
74
75 /**********************************************************************
76 Function to display the Titles of the columns of the employee details
77 **********************************************************************/
78 FUNCTION formated_header_string (p_output_file_type in VARCHAR2)
79 RETURN VARCHAR2
80 IS
81 lv_format1 VARCHAR2(32000);
82 lv_year_heading VARCHAR2(200);
83 lv_emp_sin_heading VARCHAR2(200);
84 lv_emp_name_heading VARCHAR2(200);
85 lv_emp_num_heading VARCHAR2(200);
86 lv_gre_heading VARCHAR2(200);
87
88 BEGIN
89
90 lv_year_heading := hr_general.decode_lookup('PAY_CA_MISSING_ASG','YEAR');
91 lv_emp_sin_heading := hr_general.decode_lookup('PAY_CA_MISSING_ASG','EMP_SIN');
92 lv_emp_num_heading := hr_general.decode_lookup('PAY_CA_MISSING_ASG','EMP_NUM');
93 lv_emp_name_heading := hr_general.decode_lookup('PAY_CA_MISSING_ASG','EMP_NAME');
94 lv_gre_heading := hr_general.decode_lookup('PAY_CA_MISSING_ASG','GRE');
95
96 lv_format1 :=
97 pay_us_payroll_utils.formated_data_string (p_input_string => lv_year_heading
98 ,p_bold => 'Y'
99 ,p_output_file_type => p_output_file_type) ||
100 pay_us_payroll_utils.formated_data_string (p_input_string => lv_gre_heading
101 ,p_bold => 'Y'
102 ,p_output_file_type => p_output_file_type) ||
103 pay_us_payroll_utils.formated_data_string (p_input_string => lv_emp_name_heading
104 ,p_bold => 'Y'
105 ,p_output_file_type => p_output_file_type) ||
106 pay_us_payroll_utils.formated_data_string (p_input_string => lv_emp_sin_heading
107 ,p_bold => 'Y'
108 ,p_output_file_type => p_output_file_type) ||
109 pay_us_payroll_utils.formated_data_string (p_input_string => lv_emp_num_heading
110 ,p_bold => 'Y'
111 ,p_output_file_type => p_output_file_type) ;
112
113 return lv_format1 ;
114
115 END formated_header_string;
116
117 /**********************************************************************
118 Function to display the Titles of the columns of the employee details
119 **********************************************************************/
120 FUNCTION formated_header_string_rl(p_output_file_type in VARCHAR2)
121 RETURN VARCHAR2
122 IS
123 lv_format1 VARCHAR2(32000);
124 lv_year_heading VARCHAR2(200);
125 lv_emp_sin_heading VARCHAR2(200);
126 lv_emp_name_heading VARCHAR2(200);
127 lv_emp_num_heading VARCHAR2(200);
128 lv_pre_heading VARCHAR2(200);
129
130 BEGIN
131
132 lv_year_heading := hr_general.decode_lookup('PAY_CA_MISSING_ASG','YEAR');
133 lv_emp_sin_heading := hr_general.decode_lookup('PAY_CA_MISSING_ASG','EMP_SIN');
134 lv_emp_num_heading := hr_general.decode_lookup('PAY_CA_MISSING_ASG','EMP_NUM');
135 lv_emp_name_heading := hr_general.decode_lookup('PAY_CA_MISSING_ASG','EMP_NAME');
136 lv_pre_heading := hr_general.decode_lookup('PAY_CA_MISSING_ASG','PRE');
137
138 lv_format1 :=
139 pay_us_payroll_utils.formated_data_string (p_input_string => lv_year_heading
140 ,p_bold => 'Y'
141 ,p_output_file_type => p_output_file_type) ||
142 pay_us_payroll_utils.formated_data_string (p_input_string => lv_pre_heading
143 ,p_bold => 'Y'
144 ,p_output_file_type => p_output_file_type) ||
145 pay_us_payroll_utils.formated_data_string (p_input_string => lv_emp_name_heading
146 ,p_bold => 'Y'
147 ,p_output_file_type => p_output_file_type) ||
148 pay_us_payroll_utils.formated_data_string (p_input_string => lv_emp_sin_heading
149 ,p_bold => 'Y'
150 ,p_output_file_type => p_output_file_type) ||
151 pay_us_payroll_utils.formated_data_string (p_input_string => lv_emp_num_heading
152 ,p_bold => 'Y'
153 ,p_output_file_type => p_output_file_type) ;
154
155 return lv_format1 ;
156
157 END formated_header_string_rl;
158
159 /*******************************************************************************
160 Function to display the details of the selected employee for T4/T4A Report Type
161 ********************************************************************************/
162 FUNCTION formated_detail_string(
163 p_output_file_type in VARCHAR2
164 ,p_year VARCHAR2
165 ,p_gre VARCHAR2
166 ,p_employee_name VARCHAR2
167 ,p_employee_sin VARCHAR2
168 ,p_employee_number VARCHAR2
169 ) RETURN VARCHAR2
170 IS
171 lv_format1 VARCHAR2(22000);
172 BEGIN
173
174 lv_format1 :=
175 pay_us_payroll_utils.formated_data_string (p_input_string => p_year
176 ,p_bold => 'N'
177 ,p_output_file_type => p_output_file_type) ||
178 pay_us_payroll_utils.formated_data_string (p_input_string => p_gre
179 ,p_bold => 'N'
180 ,p_output_file_type => p_output_file_type) ||
181 pay_us_payroll_utils.formated_data_string (p_input_string => p_employee_name
182 ,p_bold => 'N'
183 ,p_output_file_type => p_output_file_type) ||
184 pay_us_payroll_utils.formated_data_string (p_input_string => p_employee_sin
185 ,p_bold => 'N'
186 ,p_output_file_type => p_output_file_type) ||
187 pay_us_payroll_utils.formated_data_string (p_input_string => p_employee_number
188 ,p_bold => 'N'
189 ,p_output_file_type => p_output_file_type);
190
191 return lv_format1;
192
193 END formated_detail_string;
194
195 /*******************************************************************************
196 Function to display the details of the selected employee for RL1/RL2 Report Type
197 ********************************************************************************/
198 FUNCTION formated_detail_string_rl(
199 p_output_file_type in VARCHAR2
200 ,p_year VARCHAR2
201 ,p_pre VARCHAR2
202 ,p_employee_name VARCHAR2
203 ,p_employee_sin VARCHAR2
204 ,p_employee_number VARCHAR2
205 ) RETURN VARCHAR2
206 IS
207 lv_format1 VARCHAR2(22000);
208 BEGIN
209
210 lv_format1 :=
211 pay_us_payroll_utils.formated_data_string (p_input_string => p_year
212 ,p_bold => 'N'
213 ,p_output_file_type => p_output_file_type) ||
214 pay_us_payroll_utils.formated_data_string (p_input_string => p_pre
215 ,p_bold => 'N'
216 ,p_output_file_type => p_output_file_type) ||
217 pay_us_payroll_utils.formated_data_string (p_input_string => p_employee_name
218 ,p_bold => 'N'
219 ,p_output_file_type => p_output_file_type) ||
220 pay_us_payroll_utils.formated_data_string (p_input_string => p_employee_sin
221 ,p_bold => 'N'
222 ,p_output_file_type => p_output_file_type) ||
223 pay_us_payroll_utils.formated_data_string (p_input_string => p_employee_number
224 ,p_bold => 'N'
225 ,p_output_file_type => p_output_file_type);
226
227 return lv_format1;
228
229 END formated_detail_string_rl;
230
231 /**************************************************************************
232 Procedure to display message if no employees are selected
233 *************************************************************************/
234 PROCEDURE formated_zero_count(output_file_type VARCHAR2)
235 IS
236 lvc_message VARCHAR2(200);
237 BEGIN
238 -- lvc_message := 'The Year End Preprocess Archive has no missing assignments';
239 lvc_message := hr_general.decode_lookup ('PAY_CA_MISSING_ASG','NO_MISSING_ASG');
240
241 hr_utility.set_location(gv_package_name || '.formated_zero_count', 10);
242
243 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
244 pay_us_payroll_utils.formated_data_string (p_input_string => lvc_message
245 ,p_bold => 'N'
246 ,p_output_file_type => output_file_type));
247 END;
248
249 /**************************************************************************
250 Procedure to display the name of the assignment set to which the selected
251 assignments are added
252 ************************************************************************/
253 PROCEDURE formated_assign_count(assignment_set_name in varchar2,
254 assignment_set_id in number,
255 record_count in number,
256 assign_set_created in number,
257 output_file_type in varchar2)
258 IS
259
260 lvc_message1 VARCHAR2(400);
261 lvc_message2 VARCHAR2(400);
262 lvc_message3 VARCHAR2(400);
263
264 BEGIN
265 if assign_set_created = 1 then
266 lvc_message1 := hr_general.decode_lookup('PAY_CA_MISSING_ASG','ASG_SET_CREATED')||': '||assignment_set_name;
267 else
268 lvc_message1 := hr_general.decode_lookup('PAY_CA_MISSING_ASG','ASG_SET_NAME')||': '||assignment_set_name;
269 end if;
270
271 lvc_message2 := hr_general.decode_lookup('PAY_CA_MISSING_ASG','ASG_SET_ID')||': '||to_char(assignment_set_id);
272 lvc_message3 := hr_general.decode_lookup('PAY_CA_MISSING_ASG','NUMBER_OF_ASG')||': '||to_char(record_count);
273
274 if output_file_type ='HTML' then
275 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<br>');
276 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<br>');
277 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table align=center>');
278 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<tr>'||
279 pay_us_payroll_utils.formated_data_string (p_input_string =>lvc_message1,
280 p_bold => 'N',
281 p_output_file_type => output_file_type)||'</tr>');
282 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<tr>'||
283 pay_us_payroll_utils.formated_data_string (p_input_string =>lvc_message2,
284 p_bold => 'N',
285 p_output_file_type => output_file_type)||'</tr>');
286 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<tr>'||
287 pay_us_payroll_utils.formated_data_string (p_input_string =>lvc_message3,
288 p_bold => 'N',
289 p_output_file_type => output_file_type)||'</tr>');
290 else
291 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
292 pay_us_payroll_utils.formated_data_string (p_input_string =>lvc_message1,
293 p_bold => 'N',
294 p_output_file_type => output_file_type));
295 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
296 pay_us_payroll_utils.formated_data_string (p_input_string =>lvc_message2,
297 p_bold => 'N',
298 p_output_file_type => output_file_type));
299 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
300 pay_us_payroll_utils.formated_data_string (p_input_string =>lvc_message3,
301 p_bold => 'N',
302 p_output_file_type => output_file_type));
303 end if;
304
308 The PROCEDURE called FROM the concurrent program.
305 END;
306
307 /* ******************************************************
309 Name: select_employee
310 Description: The input parameters for the procedure are
311 Date,GRE/PRE,Assignment Set and output file type from
312 the concurrent program. The procedure identifies the
313 missing assignments , adds them to the assignment
314 set entered and generates the report in the specified
315 format.
316 *****************************************************/
317
318 PROCEDURE select_employee(errbuf OUT NOCOPY VARCHAR2,
319 retcode OUT NOCOPY NUMBER,
320 p_effective_date IN VARCHAR2,
321 p_bus_grp IN NUMBER,
322 p_report_type IN VARCHAR2,
323 p_dummy1 IN VARCHAR2,
324 p_gre_id IN NUMBER,
325 p_dummy2 IN VARCHAR2,
326 p_pre_id IN NUMBER,
327 p_assign_set IN VARCHAR2,
328 p_output_file_type IN VARCHAR2)
329 IS
330
331 /* Cursor to select primary assignments that are not archived by the
332 YEPP in the given year for the given GRE */
333
334 CURSOR c_missing_assignments(cp_effective_date date,
335 cp_bus_grp number,
336 cp_report_type varchar2,
337 cp_tax_unit_id number) is
338 SELECT DISTINCT asg.assignment_id ass_id
339 FROM per_all_assignments_f asg,
340 pay_assignment_actions paa,
341 pay_payroll_actions ppa,
342 per_all_people_f ppf
343 WHERE ppa.effective_date BETWEEN cp_effective_date
344 AND add_months(cp_effective_date, 12) - 1
345 AND ppa.action_type in ('R','Q','V','B','I')
346 AND ppa.payroll_action_id = paa.payroll_action_id
347 AND paa.tax_unit_id = cp_tax_unit_id
348 AND paa.assignment_id = asg.assignment_id
349 AND ppa.business_group_id+0 = cp_bus_grp
350 AND ppa.business_group_id = asg.business_group_id +0
351 AND asg.person_id = ppf.person_id
352 AND asg.assignment_type = 'E'
353 AND ppa.effective_date between asg.effective_start_date AND asg.effective_end_date
354 AND ppa.effective_date between ppf.effective_start_date AND ppf.effective_end_date
355 AND NOT EXISTS ( SELECT 1
356 FROM pay_payroll_actions ppa1,
357 pay_assignment_actions paa1
358 WHERE ppa1.report_type = cp_report_type
359 AND ppa1.report_qualifier = 'CAEOY'
360 AND ppa1.effective_date = add_months(cp_effective_date, 12) - 1
361 AND get_parameter('TRANSFER_GRE',ppa1.legislative_parameters) = to_char(cp_tax_unit_id)
362 AND ppa1.payroll_action_id = paa1.payroll_action_id
363 AND ppa1.business_group_id+0 = cp_bus_grp
364 AND paa1.serial_number = to_char(ppf.person_id))
365 ORDER BY asg.assignment_id DESC;
366
367 /* Cursor to select primary assignments that are not archived by the
368 YEPP in the given year for the given PRE of Report Type RL1*/
369
370 CURSOR c_missing_assignments_rl1(cp_effective_date date,
371 cp_bus_grp number,
372 cp_report_type varchar2,
373 cp_pre_id number) is
374 SELECT DISTINCT ASG.assignment_id ass_id
375 FROM per_all_assignments_f ASG,
376 pay_all_payrolls_f PPY,
377 hr_soft_coding_keyflex SCL
378 WHERE ASG.business_group_id + 0 = cp_bus_grp
379 AND ASG.assignment_type = 'E'
380 AND ASG.effective_start_date <= add_months(cp_effective_date, 12) - 1
381 AND ASG.effective_end_date >= cp_effective_date
382 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
383 AND (
384 (rtrim(ltrim(SCL.segment1)) in
385 (select to_char(hoi.organization_id)
386 from hr_organization_information hoi
387 where hoi.org_information_context = 'Canada Employer Identification'
388 and hoi.org_information2 = to_char(cp_pre_id) ))
389 or
390 (rtrim(ltrim(SCL.segment11)) in
391 (select to_char(hoi.organization_id)
392 from hr_organization_information hoi
393 where hoi.org_information_context = 'Canada Employer Identification'
394 and hoi.org_information2 = to_char(cp_pre_id) ))
395 )
396 AND PPY.payroll_id = ASG.payroll_id
397 AND EXISTS (select 'X'
398 from pay_action_contexts pac, ff_contexts fc
399 where pac.assignment_id = asg.assignment_id
400 and pac.context_id = fc.context_id
401 and fc.context_name = 'JURISDICTION_CODE'
402 and pac.context_value = 'QC')
403 AND NOT EXISTS (SELECT 1
404 FROM pay_payroll_actions ppa,
405 pay_assignment_actions paa
406 WHERE ppa.report_type = cp_report_type
407 AND ppa.report_qualifier = 'CAEOYRL1'
408 AND ppa.effective_date = add_months(cp_effective_date, 12) - 1
409 AND get_parameter('PRE_ORGANIZATION_ID',ppa.legislative_parameters) = to_char(cp_pre_id)
410 AND ppa.payroll_action_id = paa.payroll_action_id
411 AND ppa.business_group_id+0 = cp_bus_grp
412 AND paa.serial_number = to_char(ASG.person_id))
413 ORDER BY asg.assignment_id DESC;
414
415 /* Cursor to select primary assignments that are not archived by the
416 YEPP in the given year for the given PRE of Report Type RL2 */
417
418 CURSOR c_missing_assignments_rl2(cp_effective_date date,
419 cp_bus_grp number,
420 cp_report_type varchar2,
421 cp_pre_id number) is
422 SELECT DISTINCT ASG.assignment_id ass_id
423 FROM per_all_assignments_f ASG,
424 pay_all_payrolls_f PPY,
425 hr_soft_coding_keyflex SCL
426 WHERE ASG.business_group_id + 0 = cp_bus_grp
427 AND ASG.assignment_type = 'E'
428 AND ASG.effective_start_date <= add_months(cp_effective_date, 12) - 1
429 AND ASG.effective_end_date >= cp_effective_date
430 AND SCL.soft_coding_keyflex_id = ASG.soft_coding_keyflex_id
431 AND rtrim(ltrim(SCL.segment12)) in
432 (select to_char(hoi.organization_id)
433 from hr_organization_information hoi
434 where hoi.org_information_context = 'Canada Employer Identification'
435 and hoi.org_information2 = to_char(cp_pre_id)
436 and hoi.org_information5 = 'T4A/RL2')
437 AND PPY.payroll_id = ASG.payroll_id
438 AND EXISTS (select 'X' from pay_action_contexts pac, ff_contexts fc
439 where pac.assignment_id = asg.assignment_id
440 and pac.context_id = fc.context_id
441 and fc.context_name = 'JURISDICTION_CODE'
445 pay_assignment_actions paa
442 and pac.context_value = 'QC')
443 AND NOT EXISTS (SELECT 1
444 FROM pay_payroll_actions ppa,
446 WHERE ppa.report_type = cp_report_type
447 AND ppa.report_qualifier = 'CAEOYRL2'
448 AND ppa.effective_date = add_months(cp_effective_date, 12) - 1
449 AND get_parameter('PRE_ORGANIZATION_ID',ppa.legislative_parameters)= to_char(cp_pre_id)
450 AND ppa.payroll_action_id = paa.payroll_action_id
451 AND ppa.business_group_id+0 = cp_bus_grp
452 AND paa.serial_number = to_char(ASG.person_id))
453 ORDER BY asg.assignment_id DESC;
454
455 /* Cursor to check if the assignment selected has atleast a single
456 non zero run result value with an input value of Money in the
457 entered year */
458
459 CURSOR c_non_zero_run_result(cp_business_group number,
460 cp_assignment_id number,
461 cp_effective_date date,
462 cp_tax_unit_id number) is
463 SELECT 1 FROM dual
464 WHERE EXISTS (SELECT 1
465 FROM pay_run_results prr,
466 pay_run_result_values prrv,
467 pay_input_values_f piv,
468 pay_assignment_actions paa,
469 pay_payroll_actions ppa,
470 pay_all_payrolls_f ppf
471 WHERE ppa.business_group_id+0 = cp_business_group
472 AND paa.assignment_id = cp_assignment_id
473 AND paa.tax_unit_id = cp_tax_unit_id
474 AND prr.assignment_action_id = paa.assignment_action_id
475 AND ppa.payroll_action_id = paa.payroll_action_id
476 AND ppa.action_type in ('R','B','Q','V','I')
477 AND ppa.effective_date between cp_effective_date
478 AND add_months(cp_effective_date, 12) - 1
479 AND ppa.payroll_id = ppf.payroll_id
480 AND ppa.effective_date between ppf.effective_start_date
481 AND ppf.effective_end_date
482 AND ppf.payroll_id > 0
483 AND prrv.run_result_id = prr.run_result_id
484 AND prrv.result_value <> '0'
485 AND piv.input_value_id = prrv.input_value_id
486 AND ppa.effective_date between piv.effective_Start_date
487 AND piv.effective_end_date
488 AND piv.uom = 'M'
489 AND EXISTS (SELECT '1'
490 FROM pay_balance_feeds_f pbf
491 WHERE piv.input_value_id = pbf.input_value_id
492 AND ppa.effective_date BETWEEN pbf.effective_Start_date
493 AND pbf.effective_end_date));
494
495 CURSOR c_name(p_org_id number) IS
496 SELECT name
497 FROM hr_all_organization_units_tl
498 WHERE organization_id = p_org_id
499 AND language = userenv('LANG');
500
501 CURSOR c_person_id(c_assign_id number) IS
502 SELECT person_id
503 FROM per_all_assignments_f
504 WHERE assignment_id = c_assign_id
505 AND business_group_id+0 = p_bus_grp;
506
507 CURSOR c_assignment_no(c_assign_id number) IS
508 SELECT assignment_number
509 FROM per_all_assignments_f
510 WHERE assignment_id = c_assign_id;
511
512 CURSOR c_employee_details(c_person_id number) IS
513 SELECT full_name,national_identifier
514 FROM per_all_people_f
515 WHERE person_id = c_person_id;
516
517 CURSOR c_assignment_set_id IS
518 SELECT hr_assignment_sets_s.nextval
519 FROM dual;
520
521 CURSOR c_assignment_set_exists(assign_set_name VARCHAR2) IS
522 SELECT assignment_set_id
523 FROM hr_assignment_sets
524 WHERE assignment_set_name=assign_set_name;
525
526 CURSOR c_assignment_amd_exists(c_assignment_id number,
527 c_assignment_set_id number) IS
528 SELECT 1
529 FROM hr_assignment_set_amendments
530 WHERE assignment_set_id = c_assignment_set_id
531 AND assignment_id = c_assignment_id;
532
533 CURSOR c_all_gres(cp_pre_id number) IS
534 SELECT hoi.organization_id gre_id
535 FROM hr_organization_information hoi,
536 hr_all_organization_units hou
537 WHERE hoi.org_information_context = 'Canada Employer Identification'
538 AND hoi.org_information2 = to_char(cp_pre_id)
539 AND hou.business_group_id = p_bus_grp
540 AND hou.organization_id = hoi.organization_id;
541
542
543 /* Local variables */
544
545 lv_assn_id per_all_assignments_f.assignment_id%type;
546 lv_result_value number;
547 lv_person_id per_all_people_f.person_id%type;
548 lv_gre_name hr_all_organization_units_tl.name%type;
549 lv_pre_name hr_all_organization_units_tl.name%type;
550 lv_emp_name per_all_people_f.full_name%type;
551 lv_emp_no per_all_people_f.employee_number%type;
552 lv_emp_sin per_all_people_f.national_identifier%type;
553 lv_data_row varchar2(4000);
554 row_id varchar2(100);
555 lv_miss_assignments number;
556 lv_effective_date date;
557 lv_assignment_set_id number;
558 lv_payroll_id number;
559 lv_formula_id number;
560 lv_assign_set_created number ;
561 lv_assignment_amd_exists number;
562
563
564 BEGIN
565
566 lv_result_value :=0;
567 lv_miss_assignments :=0;
568 lv_assignment_set_id :=0;
569 lv_payroll_id :=NULL;
570 lv_formula_id :=NULL;
574
571 lv_assign_set_created :=0;
572 lv_assignment_amd_exists :=0;
573 lv_effective_date := FND_DATE.canonical_to_date(p_effective_date);
575 -- 'Year End Archive Missing Assignments Report'
576 gv_title := hr_general.decode_lookup('PAY_CA_MISSING_ASG','MISSING_REPORT_HEADING');
577
578 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
579 pay_us_payroll_utils.formated_header_string(gv_title || ' - '|| p_report_type ||' '||
580 to_char(lv_effective_date,'YYYY'), p_output_file_type ));
581
582 IF p_report_type IN ('T4','T4A') THEN
583 hr_utility.trace('The value of gre is '||p_gre_id);
584 OPEN c_missing_assignments(lv_effective_date,p_bus_grp,p_report_type,p_gre_id) ;
585
586 ELSIF p_report_type = 'RL1' THEN
587 hr_utility.trace('The value of pre is '||p_pre_id);
588 OPEN c_missing_assignments_rl1(lv_effective_date,p_bus_grp,p_report_type,p_pre_id) ;
589 ELSE
590 hr_utility.trace('The value of pre is '||p_pre_id);
591 OPEN c_missing_assignments_rl2(lv_effective_date,p_bus_grp,p_report_type,p_pre_id) ;
592
593 END IF;
594
595 LOOP
596 IF p_report_type IN ('T4','T4A') THEN
597
598 FETCH c_missing_assignments INTO lv_assn_id;
599 EXIT WHEN c_missing_assignments%notfound ;
600
601 ELSIF p_report_type = 'RL1' THEN
602
603 FETCH c_missing_assignments_rl1 INTO lv_assn_id;
604 EXIT WHEN c_missing_assignments_rl1%notfound ;
605
606 ELSE
607
608 FETCH c_missing_assignments_rl2 INTO lv_assn_id;
609 EXIT WHEN c_missing_assignments_rl2%notfound ;
610
611 END IF;
612
613 lv_result_value := 0;
614
615 /* Check for nonzero run_result_value for assignments picked up*/
616
617 IF p_report_type IN ('T4','T4A') THEN
618
619 OPEN c_non_zero_run_result(p_bus_grp,
620 lv_assn_id,
621 lv_effective_date,
622 p_gre_id);
623
624 FETCH c_non_zero_run_result into lv_result_value;
625 CLOSE c_non_zero_run_result;
626
627 ELSE
628
629 /* If the report type is RL1/RL2 we need to fetch the GRE's which are under this
630 PRE and then we need to check for the run result values of these GRE's */
631
632 FOR i IN c_all_gres(p_pre_id)
633 LOOP
634
635 OPEN c_non_zero_run_result(p_bus_grp,
636 lv_assn_id,
637 lv_effective_date,
638 i.gre_id);
639
640 FETCH c_non_zero_run_result into lv_result_value;
641 CLOSE c_non_zero_run_result;
642
643 IF lv_result_value = 1 THEN
644 EXIT;
645 END IF;
646
647 END LOOP;
648
649 END IF;
650
651 lv_assignment_amd_exists := 0;
652
653 IF lv_result_value = 1 THEN
654
655 IF p_report_type IN ('T4','T4A') THEN
656
657 OPEN c_name(p_gre_id);
658 FETCH c_name into lv_gre_name;
659 CLOSE c_name;
660
661 ELSE
662
663 OPEN c_name(p_pre_id);
664 FETCH c_name into lv_pre_name;
665 CLOSE c_name;
666
667 END IF;
668
669 OPEN c_person_id(lv_assn_id);
670 FETCH c_person_id into lv_person_id;
671 CLOSE c_person_id;
672
673 OPEN c_assignment_no(lv_assn_id);
674 FETCH c_assignment_no into lv_emp_no;
675 CLOSE c_assignment_no;
676
677 OPEN c_employee_details(lv_person_id);
678 FETCH c_employee_details into lv_emp_name,lv_emp_sin;
679 CLOSE c_employee_details;
680
681 /*create assignment set only when the first row is fetched*/
682
683 IF lv_miss_assignments=0 THEN
684
685 IF p_output_file_type ='HTML' THEN
686 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<body>');
687 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<table border=1 align=center>');
688 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '<tr>');
689 END IF;
690
691 IF p_report_type in ('T4','T4A') THEN
692
693 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,formated_header_string(p_output_file_type));
694 ELSE
695 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,formated_header_string_rl(p_output_file_type));
696 END IF;
697
698 IF p_output_file_type ='HTML' THEN
699 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</tr>');
700 END IF;
701
702 OPEN c_assignment_set_exists(p_assign_set);
703 FETCH c_assignment_set_exists into lv_assignment_set_id;
704 CLOSE c_assignment_set_exists;
705
706 /*if assignment set does not exist,create a new one*/
707
708 IF lv_assignment_set_id = 0 THEN
709
710 OPEN c_assignment_set_id;
711 FETCH c_assignment_set_id into lv_assignment_set_id;
712 CLOSE c_assignment_set_id;
713
714 /* Inserting the New Assignment set into hr_assignment_sets table */
715 hr_assignment_sets_pkg.insert_row(row_id,
716 lv_assignment_set_id,
717 p_bus_grp,
718 lv_payroll_id,
719 p_assign_set,
720 lv_formula_id);
724
721 lv_assign_set_created := 1;
722
723 END IF;
725 END IF;
726
727 IF lv_assign_set_created = 0 THEN
728
729 /*Checking for the Existence of the Assignment Set */
730
731 OPEN c_assignment_amd_exists(lv_assn_id,lv_assignment_set_id);
732 FETCH c_assignment_amd_exists into lv_assignment_amd_exists;
733 CLOSE c_assignment_amd_exists;
734
735 IF lv_assignment_amd_exists=0 THEN
736 hr_assignment_set_amds_pkg.insert_row(row_id,lv_assn_id,lv_assignment_set_id,'I');
737 END IF;
738
739 ELSE
740 hr_assignment_set_amds_pkg.insert_row(row_id,lv_assn_id,lv_assignment_set_id,'I');
741 END IF;
742
743 lv_miss_assignments := lv_miss_assignments + 1;
744
745 IF p_report_type IN ('T4','T4A') THEN
746 lv_data_row := formated_detail_string(p_output_file_type
747 ,to_char(lv_effective_date,'YYYY')
748 ,lv_gre_name
749 ,lv_emp_name
750 ,lv_emp_sin
751 ,lv_emp_no);
752 ELSE
753 lv_data_row := formated_detail_string_rl(p_output_file_type
754 ,to_char(lv_effective_date,'YYYY')
755 ,lv_pre_name
756 ,lv_emp_name
757 ,lv_emp_sin
758 ,lv_emp_no);
759 END IF;
760
761 IF p_output_file_type ='HTML' THEN
762 lv_data_row := '<tr>' || lv_data_row || '</tr>' ;
763 END IF;
764
765 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, lv_data_row);
766
767 END IF; /*non zero run result value for the assignment*/
768
769 END LOOP; /*loop for checking the nonzero run_result values for selected assignments*/
770
771 IF p_output_file_type='HTML' THEN
772 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table>');
773 END IF;
774
775 /* If No Assignments are fetched */
776 IF lv_miss_assignments = 0 THEN
777
778 formated_zero_count(p_output_file_type);
779 ELSE
780 formated_assign_count(p_assign_set,
781 lv_assignment_set_id,
782 lv_miss_assignments,
783 lv_assign_set_created,
784 p_output_file_type);
785 END IF;
786
787 IF p_output_file_type ='HTML' THEN
788
789 UPDATE fnd_concurrent_requests
790 SET output_file_type = 'HTML'
791 WHERE request_id = FND_GLOBAL.CONC_REQUEST_ID ;
792 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</table>');
793 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</body>');
794 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '</HTML>');
795
796 COMMIT;
797
798 END IF;
799
800 END select_employee;
801
802 END pay_ca_yepp_miss_assign_pkg;