[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_RULES
Source
1 package body pay_ca_rules as
2 /* $Header: pycarule.pkb 120.14.12000000.1 2007/01/17 17:24:58 appldev noship $ */
3 /*
4 Copyright (c) Oracle Corporation 1991,1992,1993,1994. All rights reserved
5 --
6 Name : pay_ca_rules
7 --
8 Change List
9 -----------
10 Date Name Vers Description
11 ----------- ---------- ----- -------------------------------------------
12 21-SEP-2006 pganguly 115.18 Changed the add_custom_xml procedure.
13 08-SEP-2006 ydevi 115.16 Added code in add_custom_xml to support
14 CIBC Direct Deposit Bank Format
15 30-AUG_2006 ssmukher 115.15 Added code in add_custom_xml to support
16 CPA 005 Direct Deposit Format.
17 17-AUG-2006 pganguly 115.14 Added code in add_custom_xml to support TD
18 Direct Deposit Format.
19 10-AUG-2006 pganguly 115.13 Added nocopy in FILE_NO out parameter.
20 10-AUG-2006 pganguly 115.12 Fixed bug# 5234705. Added a new procedure
21 get_file_creation_no. Also changed the
22 signature of add_custom_xml procedure.
23 03-MAR-2006 pganguly 115.11 Fixed bug# 5104801. Changed the
24 legislation_code to 'CA' in the function
25 work_schedule_total_hours.
26 27-OCT-2005 mmukherj 115.10 Added the function
27 work_schedule_total_hours used by new
28 work schedule functionality
29 21-OCT-2005 115.9 Changed the format of payment_date in
30 add_custom_xml procedure.
31 20-OCT-2005 115.8 Added archiving of Payment_date in the
32 add_custom_xml procedure.
33 03-OCT-2005 115.7 Added add_custom_xml procedure to this
34 package. This procedure served as a
35 legislation hook for the Direct Deposit
36 process which uses XMl Publisher Utility.
37 #4650317.
38 13-SEP-2005 ssouresr 115.6 The application_id for the error messages
39 introduced in the previous update should
40 have been 801 and not 800
41 08-AUG-2005 saurgupt 115.5 Modified the proc get_dynamic_tax_unit.
42 Raised the error if tax_unit_id is not
43 present for the element being processed.
44 10-APR-2002 vpandya 115.4 Added get_multi_tax_unit_pay_flag procedure
45 to get 'Payroll Archiver Level' of the
46 business group for prepayment.
47 GRE - Separate Cheque by GRE
48 TAXGRP - Consolidated Cheque for all GREs.
49 04-SEP-2002 vpandya 115.3 Added get_dynamic_tax_unit procedure for
50 Multi GRE functionality.
51 14-Apr-2000 SSattini 115.1 Changed pay_ca_emp_all_fedtax_info to
52 pay_ca_emp_all_fedtax_info_v.
53 07-May-1999 Lwthomps Modified to use the allfed info view.
54 16-APr-1999 mmukherj 110.0 Created.
55 */
56 --
57 --
58 PROCEDURE get_default_jurisdiction(p_asg_act_id number,
59 p_ee_id number,
60 p_jurisdiction in out nocopy varchar2)
61 IS
62
63 l_geocode varchar2(15);
64
65 cursor csr_get_jd is
66 Select employment_province, geocode
67 from pay_ca_emp_all_fedtax_info_v cft,
68 pay_assignment_actions paa
69 where cft.assignment_id = paa.assignment_id
70 and paa.assignment_action_id = p_asg_act_id;
71
72 BEGIN
73
74 open csr_get_jd;
75 fetch csr_get_jd into p_jurisdiction, l_geocode;
76 close csr_get_jd;
77
78 END get_default_jurisdiction;
79
80 PROCEDURE get_dynamic_tax_unit(p_asg_act_id in number,
81 p_run_type_id in number,
82 p_tax_unit_id in out nocopy number) IS
83
84 cursor cur_run_type(cp_run_type_id in number) is
85 select substr(run_type_name,1,instr(run_type_name,' ')-1)
86 from pay_run_types_f
87 where run_type_id = cp_run_type_id;
88
89 cursor cur_tax_unit(cp_asg_act_id in number) is
90 select segment1 T4_RL1_GRE
91 ,segment11 T4A_RL1_GRE
92 ,segment12 T4A_RL2_GRE
93 from hr_soft_coding_keyflex hsck
94 ,per_all_assignments_f paf
95 ,pay_assignment_actions paa
96 ,pay_payroll_actions ppa
97 where paa.assignment_action_id = cp_asg_act_id
98 and ppa.payroll_action_id = paa.payroll_action_id
99 and paf.assignment_id = paa.assignment_id
100 and ppa.effective_date between paf.effective_start_date
101 and paf.effective_end_date
102 and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id;
103
104 cursor cur_check_gre_type(cp_tax_unit_id in number) is
105 select hoi.org_information5
106 from hr_organization_information hoi
107 where hoi.organization_id = cp_tax_unit_id
108 and hoi.org_information_context = 'Canada Employer Identification';
109
110 cursor cur_tu_for_old_run(cp_asg_act_id in number) is
111 select decode(segment1, NULL, 0, 1 ) +
112 decode(segment11, NULL, 0, 1 ) +
113 decode(segment12, NULL, 0, 1 ) tot_no_of_tu
114 ,nvl(segment1, nvl(segment11,segment12) ) tax_unit_id
115 from hr_soft_coding_keyflex hsck
116 ,per_all_assignments_f paf
117 ,pay_assignment_actions paa
118 ,pay_payroll_actions ppa
119 where paa.assignment_action_id = cp_asg_act_id
120 and ppa.payroll_action_id = paa.payroll_action_id
121 and paf.assignment_id = paa.assignment_id
122 and ppa.effective_date between paf.effective_start_date
123 and paf.effective_end_date
124 and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id;
125
126 ln_t4_rl1_gre number;
127 ln_t4a_rl1_gre number;
128 ln_t4a_rl2_gre number;
129
130 lv_run_type_gre varchar2(240);
131 lv_gre_type varchar2(240);
132
133 ln_tax_unit_id number;
134 ln_tot_no_of_tu number;
135 BEGIN
136
137
138 p_tax_unit_id := null;
139
140 open cur_run_type(p_run_type_id);
141 fetch cur_run_type into lv_run_type_gre;
142 close cur_run_type;
143
144 open cur_tax_unit(p_asg_act_id);
145 fetch cur_tax_unit into ln_t4_rl1_gre
146 ,ln_t4a_rl1_gre
147 ,ln_t4a_rl2_gre;
148 close cur_tax_unit;
149
150 if lv_run_type_gre = 'T4/RL1' then
151
152 open cur_check_gre_type(ln_t4_rl1_gre);
153 fetch cur_check_gre_type into lv_gre_type;
154 close cur_check_gre_type;
155
156 if lv_gre_type = 'T4/RL1' then
157 p_tax_unit_id := ln_t4_rl1_gre;
158 else
159 p_tax_unit_id := null;
160 hr_utility.set_message(801,'PAY_74161_MISSING_GRE');
161 pay_core_utils.push_message(801,'PAY_74161_MISSING_GRE','P');
162 hr_utility.raise_error;
163 end if;
164
165 elsif lv_run_type_gre = 'T4A/RL1' then
166
167 open cur_check_gre_type(ln_t4a_rl1_gre);
168 fetch cur_check_gre_type into lv_gre_type;
169 close cur_check_gre_type;
170
171 if lv_gre_type = 'T4A/RL1' then
172 p_tax_unit_id := ln_t4a_rl1_gre;
173 else
174 p_tax_unit_id := null;
175 hr_utility.set_message(801,'PAY_74161_MISSING_GRE');
176 pay_core_utils.push_message(801,'PAY_74161_MISSING_GRE','P');
177 hr_utility.raise_error;
178 end if;
179
180 elsif lv_run_type_gre = 'T4A/RL2' then
181
182 open cur_check_gre_type(ln_t4a_rl2_gre);
183 fetch cur_check_gre_type into lv_gre_type;
184 close cur_check_gre_type;
185
186 if lv_gre_type = 'T4A/RL2' then
187 hr_utility.trace('in lv_gre_type = T4A/RL2');
188 p_tax_unit_id := ln_t4a_rl2_gre;
189 else
190 p_tax_unit_id := null;
191 hr_utility.set_message(801,'PAY_74161_MISSING_GRE');
192 pay_core_utils.push_message(801,'PAY_74161_MISSING_GRE','P');
193 hr_utility.raise_error;
194 end if;
195
196 else
197
198 open cur_tu_for_old_run(p_asg_act_id);
199 fetch cur_tu_for_old_run into ln_tot_no_of_tu
200 ,ln_tax_unit_id;
201 close cur_tu_for_old_run;
202
203 if ln_tot_no_of_tu > 1 then
204 -- error
205 null;
206 else
207 p_tax_unit_id := ln_tax_unit_id;
208 end if;
209
210 end if;
211
212 END get_dynamic_tax_unit;
213
214 PROCEDURE get_multi_tax_unit_pay_flag
215 (p_bus_grp in number,
216 p_mtup_flag in out nocopy varchar2) IS
217
218 l_reporting_level hr_organization_information.org_information1%type;
219
220 BEGIN
221 --
222 select org_information1
223 into l_reporting_level
224 from hr_organization_information
225 where org_information_context = 'Payroll Archiver Level'
226 and organization_id = p_bus_grp;
227 --
228 --
229 if l_reporting_level is null then
230 null;
231 elsif l_reporting_level = 'TAXGRP' then
232 p_mtup_flag := 'Y';
233 else
234 p_mtup_flag := 'N';
235 end if;
236 --
237 exception
238 when no_data_found then
239 p_mtup_flag := 'N';
240 --
241 END get_multi_tax_unit_pay_flag;
242
243 PROCEDURE add_custom_xml as
244
245 /* CURSOR get_assignment_number(p_asg_action_id number) IS
246 SELECT assignment_number
247 FROM per_assignments_f paf, pay_assignment_actions paa
248 WHERE paa.assignment_action_id = p_asg_action_id
249 and paa.assignment_id = paf.assignment_id; */
250
251 TYPE char_tab IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
252
253
254 CURSOR cur_ppa(p_payroll_action_id NUMBER) IS
255 SELECT
256 SYSDATE,
257 NVL(overriding_dd_date,effective_date)
258 FROM
259 pay_payroll_actions
260 WHERE
261 payroll_action_id = p_payroll_action_id;
262
263 l_direct_deposit_date DATE;
264 l_dd_date VARCHAR2(30);
265 l_dd_type VARCHAR2(20);
266 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
267 l_text VARCHAR(900);
268 l_override_cpa_code VARCHAR2(100);
269 l_payment_date DATE;
270 l_payment_date1 VARCHAR2(30);
271
272
273 BEGIN
274
275 hr_utility.trace('Add Custom XML starts here .... ');
276
277 l_payroll_action_id
278 := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
279 l_override_cpa_code
280 := pay_magtape_generic.get_parameter_value('OVERRIDE_CPA_CODE');
281 l_dd_type
282 := pay_magtape_generic.get_parameter_value('MAGTAPE_REPORT_ID');
283
284 hr_utility.trace('l_payroll_action_id = ' ||
285 to_char(l_payroll_action_id));
286 hr_utility.trace('l_override_cpa_code = ' || l_override_cpa_code);
287
288 OPEN cur_ppa(l_payroll_action_id);
289 FETCH cur_ppa
290 INTO l_payment_date,
291 l_direct_deposit_date;
292 CLOSE cur_ppa;
293
294 hr_utility.trace('l_payment_date = ' || to_char(l_payment_date));
295 hr_utility.trace('l_direct_deposit_date = ' ||
296 to_char(l_direct_deposit_date));
297 SELECT
298 decode(l_dd_type, 'NOVA_SCOT',to_char(l_direct_deposit_date,'YYDDD'),
299 'TD', to_char(l_direct_deposit_date,'DDMMYY'),
300 'CPA','0'||to_char(l_direct_deposit_date,'YYDDD'),
301 'CIBC',to_char(l_direct_deposit_date,'YYMMDD'))
302 INTO
303 l_dd_date
304 FROM
305 DUAL;
306
307 SELECT
308 decode(l_dd_type, 'NOVA_SCOT',to_char(l_payment_date,'YYDDD'),
309 'TD', to_char(l_payment_date,'DDMMYY'),
310 'CPA','0'||to_char(l_payment_date,'YYDDD'),
311 'CIBC',to_char(l_payment_date,'YYMMDD'))
312 INTO
313 l_payment_date1
314 FROM
315 DUAL;
316 l_text :=
317 '<DEPOSIT_DATE_CA>' || l_payment_date1 || '</DEPOSIT_DATE_CA>' ||
318 '<FILE_CREATION_DATE_CA>'|| l_dd_date || '</FILE_CREATION_DATE_CA>' ||
319 '<OVERRIDE_CPA_CODE>' || l_override_cpa_code || '</OVERRIDE_CPA_CODE>';
320
321 pay_core_files.write_to_magtape_lob(l_text);
322 hr_utility.trace('Add Custom XML ends here .......');
323
324 END add_custom_xml;
325
326 FUNCTION work_schedule_total_hours(
327 assignment_action_id IN number --Context
328 ,assignment_id IN number --Context
329 ,p_bg_id IN NUMBER -- Context
330 ,element_entry_id IN number --Context
331 ,date_earned IN DATE --Context
332 ,p_range_start IN DATE
333 ,p_range_end IN DATE)
334 RETURN NUMBER IS
335
336 -- local constants
337 c_ws_tab_name VARCHAR2(80);
338
339 -- local variables
340 v_total_hours NUMBER(15,7);
341 v_range_start DATE;
342 v_range_end DATE;
343 v_curr_date DATE;
344 v_curr_day VARCHAR2(3); -- 3 char abbrev for day of wk.
345 v_ws_name VARCHAR2(80); -- Work Schedule Name.
346 v_gtv_hours VARCHAR2(80); -- get_table_value returns varchar2
347 v_fnd_sess_row VARCHAR2(1);
348 l_exists VARCHAR2(1);
349 v_day_no NUMBER;
350 p_ws_name VARCHAR2(80); -- Work Schedule Name from SCL
351 l_id_flex_num NUMBER;
352
353 CURSOR get_id_flex_num IS
357 and rule_type = 'S';
354 SELECT rule_mode
355 FROM pay_legislation_rules
356 WHERE legislation_code = 'CA'
358
359 Cursor get_ws_name (p_id_flex_num number,
360 p_date_earned date,
361 p_assignment_id number) IS
362 SELECT target.SEGMENT4
363 FROM /* route for SCL keyflex - assignment level */
364 hr_soft_coding_keyflex target,
365 per_all_assignments_f ASSIGN
366 WHERE p_date_earned BETWEEN ASSIGN.effective_start_date
367 AND ASSIGN.effective_end_date
368 AND ASSIGN.assignment_id = p_assignment_id
369 AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
370 AND target.enabled_flag = 'Y'
371 AND target.id_flex_num = p_id_flex_num;
372
373
374 BEGIN -- work_schedule_total_hours
375 /* Init */
376 v_total_hours := 0;
377 c_ws_tab_name := 'COMPANY WORK SCHEDULES';
378
379 /* get ID FLEX NUM */
380 --IF pay_us_rules.g_id_flex_num IS NULL THEN
381 hr_utility.trace('Getting ID_FLEX_NUM for CA legislation ');
382 OPEN get_id_flex_num;
383 FETCH get_id_flex_num INTO l_id_flex_num;
384 -- pay_us_rules.g_id_flex_num := l_id_flex_num;
385 CLOSE get_id_flex_num;
386 --END IF;
387
388 -- hr_utility.trace('pay_us_rules.g_id_flex_num '||pay_us_rules.g_id_flex_num);
389 hr_utility.trace('l_id_flex_num '||l_id_flex_num);
390 hr_utility.trace('assignment_action_id=' || assignment_action_id);
391 hr_utility.trace('assignment_id=' || assignment_id);
392 hr_utility.trace('business_group_id=' || p_bg_id);
393 hr_utility.trace('p_range_start=' || p_range_start);
394 hr_utility.trace('p_range_end=' || p_range_end);
395 hr_utility.trace('element_entry_id=' || element_entry_id);
396 hr_utility.trace('date_earned ' || date_earned);
397
398 /* get work schedule_name */
399 --IF pay_us_rules.g_id_flex_num IS NOT NULL THEN
400 IF l_id_flex_num IS NOT NULL THEN
401 hr_utility.trace('getting work schedule name ');
402 OPEN get_ws_name (l_id_flex_num,--pay_ca_rules.g_id_flex_num,
403 date_earned,
404 assignment_id);
405 FETCH get_ws_name INTO p_ws_name;
406 CLOSE get_ws_name;
407 END IF;
408
409 IF p_ws_name IS NULL THEN
410 hr_utility.trace('Work Schedule not found ');
411 return 0;
412 END IF;
413
414 hr_utility.trace('Work Schedule '||p_ws_name);
415
416 --changed to select the work schedule defined
417 --at the business group level instead of
418 --hardcoding the default work schedule
419 --(COMPANY WORK SCHEDULES ) to the
420 --variable c_ws_tab_name
421
422 begin
423 select put.user_table_name
424 into c_ws_tab_name
425 from hr_organization_information hoi
426 ,pay_user_tables put
427 where hoi.organization_id = p_bg_id
428 and hoi.org_information_context ='Work Schedule'
429 and hoi.org_information1 = put.user_table_id ;
430
431 EXCEPTION WHEN NO_DATA_FOUND THEN
432 null;
433 end;
434
435 -- Set range to a single week if no dates are entered:
436 -- IF (p_range_start IS NULL) AND (p_range_end IS NULL) THEN
437 --
438 v_range_start := NVL(p_range_start, sysdate);
439 v_range_end := NVL(p_range_end, sysdate + 6);
440 --
441 -- END IF;
442
443 -- Check for valid range
444 IF v_range_start > v_range_end THEN
445 --
446 RETURN v_total_hours;
447 -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
448 -- hr_utility.raise_error;
449 --
450 END IF;
451
452 -- Get_Table_Value requires row in FND_SESSIONS. We must insert this
453 -- record if one doe not already exist.
454 SELECT DECODE(COUNT(session_id), 0, 'N', 'Y')
455 INTO v_fnd_sess_row
456 FROM fnd_sessions
457 WHERE session_id = userenv('sessionid');
458
459 IF v_fnd_sess_row = 'N' THEN
460 dt_fndate.set_effective_date(trunc(sysdate));
461 END IF;
462
463 --
464 -- Track range dates:
465 --
466 -- Check if the work schedule is an id or a name. If the work
467 -- schedule does not exist, then return 0.
468 --
469 BEGIN
470 select 'Y'
471 into l_exists
472 from pay_user_tables PUT,
473 pay_user_columns PUC
474 where PUC.USER_COLUMN_NAME = p_ws_name
475 and NVL(PUC.business_group_id, p_bg_id) = p_bg_id
476 and NVL(PUC.legislation_code,'CA') = 'CA'
477 and PUC.user_table_id = PUT.user_table_id
478 and PUT.user_table_name = c_ws_tab_name;
479
480
481 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
482 END;
483
484 if l_exists = 'Y' then
485 v_ws_name := p_ws_name;
486 else
487 BEGIN
488 select PUC.USER_COLUMN_NAME
489 into v_ws_name
490 from pay_user_tables PUT,
491 pay_user_columns PUC
492 where PUC.USER_COLUMN_ID = p_ws_name
493 and NVL(PUC.business_group_id, p_bg_id) = p_bg_id
494 and NVL(PUC.legislation_code,'CA') = 'CA'
495 and PUC.user_table_id = PUT.user_table_id
496 and PUT.user_table_name = c_ws_tab_name;
497
498 EXCEPTION WHEN NO_DATA_FOUND THEN
499 RETURN v_total_hours;
500 END;
501 end if;
502
503 v_curr_date := v_range_start;
504
505 LOOP
506
507 v_day_no := TO_CHAR(v_curr_date, 'D');
508
509
510 SELECT decode(v_day_no,1,'SUN',2,'MON',3,'TUE',
514
511 4,'WED',5,'THU',6,'FRI',7,'SAT')
512 INTO v_curr_day
513 FROM DUAL;
515 v_total_hours := v_total_hours +
516 FND_NUMBER.CANONICAL_TO_NUMBER(
517 hruserdt.get_table_value(p_bg_id,
518 c_ws_tab_name,
519 v_ws_name,
520 v_curr_day));
521 v_curr_date := v_curr_date + 1;
522
523
524 EXIT WHEN v_curr_date > v_range_end;
525
526 END LOOP;
527
528 RETURN v_total_hours;
529
530 END work_schedule_total_hours;
531
532 PROCEDURE get_file_creation_no(
533 pactid IN NUMBER,
534 file_no OUT NOCOPY NUMBER) AS
535
536 l_override_file_no VARCHAR2(20);
537
538 CURSOR cur_paid IS
539 SELECT
540 legislative_parameters,
541 business_group_id,
542 org_payment_method_id
543 FROM
544 pay_payroll_actions
545 WHERE
546 payroll_action_id = pactid;
547
548 l_legislative_parameter pay_payroll_actions.legislative_parameters%TYPE;
549 l_bg_id pay_payroll_actions.business_group_id%TYPE;
550 l_org_pm_id pay_payroll_actions.org_payment_method_id%TYPE;
551 l_dd_format VARCHAR2(30);
552
553 BEGIN
554
555 hr_utility.trace('Starting pay_ca_rules.get_file_creation_number !!!!');
556
557 OPEN cur_paid;
558 FETCH cur_paid
559 INTO l_legislative_parameter,
560 l_bg_id,
561 l_org_pm_id;
562 CLOSE cur_paid;
563
564
565 l_override_file_no :=
566 pay_core_utils.get_parameter('FILE_CREATION_NUMBER_OVERRIDE',
567 l_legislative_parameter);
568 l_dd_format := pay_core_utils.get_parameter('MAGTAPE_REPORT_ID',
569 l_legislative_parameter);
570
571 hr_utility.trace('payroll_action_id = ' || to_char(pactid));
572 hr_utility.trace('l_org_pm_id = ' || to_char(l_org_pm_id));
573 hr_utility.trace('l_bg_id = ' || to_char(l_bg_id));
574 hr_utility.trace('l_legislative_parameter = ' || l_legislative_parameter);
575 hr_utility.trace('l_override_file_no = ' || l_override_file_no);
576 hr_utility.trace('l_dd_format = ' || l_dd_format);
577
578 IF l_override_file_no IS NOT NULL THEN
579 file_no := l_override_file_no;
580 ELSE
581 file_no := pay_ca_direct_deposit_pkg.get_dd_file_creation_number(
582 l_org_pm_id,
583 l_dd_format,
584 l_override_file_no,
585 pactid ,
586 l_bg_id) ;
587 END IF;
588
589 hr_utility.trace('file_no = ' || file_no);
590 hr_utility.trace('Ending pay_ca_rules.get_file_creation_number !!!!');
591
592 END get_file_creation_no;
593
594 end pay_ca_rules;