[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_RULES
Source
1 package body pay_ca_rules as
2 /* $Header: pycarule.pkb 120.18.12020000.8 2013/04/19 13:27:53 sbachu ship $ */
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 19-Apr-2013 sbachu 115.29 Bug 16686045. Added functions
13 get_payslip_sort_order1,2,3 to sort
14 CA Dep Adv(XML) properly.
15 06-Dec-2012 schowl 115.28 Bug 15952728, Added a condition in procedure
16 get_custom_xml_routine to check profile option
17 value to disable/enable custom xml code.
18 10-May-2012 sgotlasw 115.27 Bug - 14052902, modified the cursor to display
19 Bonus amount in Net Pay distribution Section.
20 17-Apr-2012 vvijayku 115.64 Bug - 13969858 Added a new procedure
21 get_custom_xml_routine which will fetch the
22 custom xml code name defined by the user as
23 the description for the respective lookup_codes
24 under the lookup type PAY_CUSTOM_XML_CODE
25 11-APR-2012 sbachu 115.25 Added transit code, account number for
26 third party cheque writer for bug 13849708
27 03-APR-2012 sbachu 115.24 Modified cursor to fetch only paid details
28 for deposit advice. Modified CF_word_amountFormula
29 to display only 4 asterisks for amount in words
30 for bugs 13849662, 13849708, and 13870178
31 05-MAR-2012 sbachu 115.23 Added functions convert_number,get_word_value,
32 and CF_word_amountFormula and procedure
33 payslip_range_cursor and get_token_name.
34 Modified add_custom_xml procedure for
35 bugs 13773808,13773821 and 13773865.
36 27-OCT-2011 sneelapa 115.22 Modified add_custom_xml procedure for bug 13024522
37 23-MAR-2010 aneghosh 115.21 Added the code to display employee and employer
38 country name on pdf payslip
39 05-MAR-2010 aneghosh 115.20 For Bug9445414. Online PDF Payslip enhancement.
40 Modified add_custom_xml procedure.
41 30-APR-2009 sapalani 115.19 For bug 8459792, Added new IN OUT parameter
42 p_wk_sch_found to function
43 work_schedule_total_hours.
44 21-SEP-2006 pganguly 115.18 Changed the add_custom_xml procedure.
45 08-SEP-2006 ydevi 115.16 Added code in add_custom_xml to support
46 CIBC Direct Deposit Bank Format
47 30-AUG_2006 ssmukher 115.15 Added code in add_custom_xml to support
48 CPA 005 Direct Deposit Format.
49 17-AUG-2006 pganguly 115.14 Added code in add_custom_xml to support TD
50 Direct Deposit Format.
51 10-AUG-2006 pganguly 115.13 Added nocopy in FILE_NO out parameter.
52 10-AUG-2006 pganguly 115.12 Fixed bug# 5234705. Added a new procedure
53 get_file_creation_no. Also changed the
54 signature of add_custom_xml procedure.
55 03-MAR-2006 pganguly 115.11 Fixed bug# 5104801. Changed the
56 legislation_code to 'CA' in the function
57 work_schedule_total_hours.
58 27-OCT-2005 mmukherj 115.10 Added the function
59 work_schedule_total_hours used by new
60 work schedule functionality
61 21-OCT-2005 115.9 Changed the format of payment_date in
62 add_custom_xml procedure.
63 20-OCT-2005 115.8 Added archiving of Payment_date in the
64 add_custom_xml procedure.
65 03-OCT-2005 115.7 Added add_custom_xml procedure to this
66 package. This procedure served as a
67 legislation hook for the Direct Deposit
68 process which uses XMl Publisher Utility.
69 #4650317.
70 13-SEP-2005 ssouresr 115.6 The application_id for the error messages
71 introduced in the previous update should
72 have been 801 and not 800
73 08-AUG-2005 saurgupt 115.5 Modified the proc get_dynamic_tax_unit.
74 Raised the error if tax_unit_id is not
75 present for the element being processed.
76 10-APR-2002 vpandya 115.4 Added get_multi_tax_unit_pay_flag procedure
77 to get 'Payroll Archiver Level' of the
78 business group for prepayment.
79 GRE - Separate Cheque by GRE
80 TAXGRP - Consolidated Cheque for all GREs.
81 04-SEP-2002 vpandya 115.3 Added get_dynamic_tax_unit procedure for
82 Multi GRE functionality.
83 14-Apr-2000 SSattini 115.1 Changed pay_ca_emp_all_fedtax_info to
84 pay_ca_emp_all_fedtax_info_v.
85 07-May-1999 Lwthomps Modified to use the allfed info view.
86 16-APr-1999 mmukherj 110.0 Created.
87 */
88 --
89 --
90 PROCEDURE get_default_jurisdiction(p_asg_act_id number,
91 p_ee_id number,
92 p_jurisdiction in out nocopy varchar2)
93 IS
94
95 l_geocode varchar2(15);
96
97 cursor csr_get_jd is
98 Select employment_province, geocode
99 from pay_ca_emp_all_fedtax_info_v cft,
100 pay_assignment_actions paa
101 where cft.assignment_id = paa.assignment_id
102 and paa.assignment_action_id = p_asg_act_id;
103
104 BEGIN
105
106 open csr_get_jd;
107 fetch csr_get_jd into p_jurisdiction, l_geocode;
108 close csr_get_jd;
109
110 END get_default_jurisdiction;
111
112 PROCEDURE get_dynamic_tax_unit(p_asg_act_id in number,
113 p_run_type_id in number,
114 p_tax_unit_id in out nocopy number) IS
115
116 cursor cur_run_type(cp_run_type_id in number) is
117 select substr(run_type_name,1,instr(run_type_name,' ')-1)
118 from pay_run_types_f
119 where run_type_id = cp_run_type_id;
120
121 cursor cur_tax_unit(cp_asg_act_id in number) is
122 select segment1 T4_RL1_GRE
123 ,segment11 T4A_RL1_GRE
124 ,segment12 T4A_RL2_GRE
125 from hr_soft_coding_keyflex hsck
126 ,per_all_assignments_f paf
127 ,pay_assignment_actions paa
128 ,pay_payroll_actions ppa
129 where paa.assignment_action_id = cp_asg_act_id
130 and ppa.payroll_action_id = paa.payroll_action_id
131 and paf.assignment_id = paa.assignment_id
132 and ppa.effective_date between paf.effective_start_date
133 and paf.effective_end_date
134 and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id;
135
136 cursor cur_check_gre_type(cp_tax_unit_id in number) is
137 select hoi.org_information5
138 from hr_organization_information hoi
139 where hoi.organization_id = cp_tax_unit_id
140 and hoi.org_information_context = 'Canada Employer Identification';
141
142 cursor cur_tu_for_old_run(cp_asg_act_id in number) is
143 select decode(segment1, NULL, 0, 1 ) +
144 decode(segment11, NULL, 0, 1 ) +
145 decode(segment12, NULL, 0, 1 ) tot_no_of_tu
146 ,nvl(segment1, nvl(segment11,segment12) ) tax_unit_id
147 from hr_soft_coding_keyflex hsck
148 ,per_all_assignments_f paf
149 ,pay_assignment_actions paa
150 ,pay_payroll_actions ppa
151 where paa.assignment_action_id = cp_asg_act_id
152 and ppa.payroll_action_id = paa.payroll_action_id
153 and paf.assignment_id = paa.assignment_id
154 and ppa.effective_date between paf.effective_start_date
155 and paf.effective_end_date
156 and hsck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id;
157
158 ln_t4_rl1_gre number;
159 ln_t4a_rl1_gre number;
160 ln_t4a_rl2_gre number;
161
162 lv_run_type_gre varchar2(240);
163 lv_gre_type varchar2(240);
164
165 ln_tax_unit_id number;
166 ln_tot_no_of_tu number;
167 BEGIN
168
169
170 p_tax_unit_id := null;
171
172 open cur_run_type(p_run_type_id);
173 fetch cur_run_type into lv_run_type_gre;
174 close cur_run_type;
175
176 open cur_tax_unit(p_asg_act_id);
177 fetch cur_tax_unit into ln_t4_rl1_gre
178 ,ln_t4a_rl1_gre
179 ,ln_t4a_rl2_gre;
180 close cur_tax_unit;
181
182 if lv_run_type_gre = 'T4/RL1' then
183
184 open cur_check_gre_type(ln_t4_rl1_gre);
185 fetch cur_check_gre_type into lv_gre_type;
186 close cur_check_gre_type;
187
188 if lv_gre_type = 'T4/RL1' then
189 p_tax_unit_id := ln_t4_rl1_gre;
190 else
191 p_tax_unit_id := null;
192 hr_utility.set_message(801,'PAY_74161_MISSING_GRE');
193 pay_core_utils.push_message(801,'PAY_74161_MISSING_GRE','P');
194 hr_utility.raise_error;
195 end if;
196
197 elsif lv_run_type_gre = 'T4A/RL1' then
198
199 open cur_check_gre_type(ln_t4a_rl1_gre);
200 fetch cur_check_gre_type into lv_gre_type;
201 close cur_check_gre_type;
202
203 if lv_gre_type = 'T4A/RL1' then
204 p_tax_unit_id := ln_t4a_rl1_gre;
205 else
206 p_tax_unit_id := null;
207 hr_utility.set_message(801,'PAY_74161_MISSING_GRE');
208 pay_core_utils.push_message(801,'PAY_74161_MISSING_GRE','P');
209 hr_utility.raise_error;
210 end if;
211
212 elsif lv_run_type_gre = 'T4A/RL2' then
213
214 open cur_check_gre_type(ln_t4a_rl2_gre);
215 fetch cur_check_gre_type into lv_gre_type;
216 close cur_check_gre_type;
217
218 if lv_gre_type = 'T4A/RL2' then
219 hr_utility.trace('in lv_gre_type = T4A/RL2');
220 p_tax_unit_id := ln_t4a_rl2_gre;
221 else
222 p_tax_unit_id := null;
223 hr_utility.set_message(801,'PAY_74161_MISSING_GRE');
224 pay_core_utils.push_message(801,'PAY_74161_MISSING_GRE','P');
225 hr_utility.raise_error;
226 end if;
227
228 else
229
230 open cur_tu_for_old_run(p_asg_act_id);
231 fetch cur_tu_for_old_run into ln_tot_no_of_tu
232 ,ln_tax_unit_id;
233 close cur_tu_for_old_run;
234
235 if ln_tot_no_of_tu > 1 then
236 -- error
237 null;
238 else
239 p_tax_unit_id := ln_tax_unit_id;
240 end if;
241
242 end if;
243
244 END get_dynamic_tax_unit;
245
246 PROCEDURE get_multi_tax_unit_pay_flag
247 (p_bus_grp in number,
248 p_mtup_flag in out nocopy varchar2) IS
249
250 l_reporting_level hr_organization_information.org_information1%type;
251
252 BEGIN
253 --
254 select org_information1
255 into l_reporting_level
256 from hr_organization_information
257 where org_information_context = 'Payroll Archiver Level'
258 and organization_id = p_bus_grp;
259 --
260 --
261 if l_reporting_level is null then
262 null;
263 elsif l_reporting_level = 'TAXGRP' then
264 p_mtup_flag := 'Y';
265 else
266 p_mtup_flag := 'N';
267 end if;
268 --
269 exception
270 when no_data_found then
271 p_mtup_flag := 'N';
272 --
273 END get_multi_tax_unit_pay_flag;
274
275 PROCEDURE add_custom_xml as
276
277 /* CURSOR get_assignment_number(p_asg_action_id number) IS
278 SELECT assignment_number
279 FROM per_assignments_f paf, pay_assignment_actions paa
280 WHERE paa.assignment_action_id = p_asg_action_id
281 and paa.assignment_id = paf.assignment_id; */
282
283 TYPE char_tab IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
284
285
286 CURSOR cur_ppa(p_payroll_action_id NUMBER) IS
287 SELECT
288 SYSDATE,
289 NVL(overriding_dd_date,effective_date)
290 FROM
291 pay_payroll_actions
292 WHERE
293 payroll_action_id = p_payroll_action_id;
294
295 l_direct_deposit_date DATE;
296 l_dd_date VARCHAR2(30);
297 l_dd_type VARCHAR2(20);
298 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
299 l_text VARCHAR(900);
300 l_override_cpa_code VARCHAR2(100);
301 l_payment_date DATE;
302 l_payment_date1 VARCHAR2(30);
303
304
305 BEGIN
306
307 hr_utility.trace('Add Custom XML starts here .... ');
308
309 l_payroll_action_id
310 := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
311 l_override_cpa_code
312 := pay_magtape_generic.get_parameter_value('OVERRIDE_CPA_CODE');
313 l_dd_type
314 := pay_magtape_generic.get_parameter_value('MAGTAPE_REPORT_ID');
315
316 hr_utility.trace('l_payroll_action_id = ' ||
317 to_char(l_payroll_action_id));
318 hr_utility.trace('l_override_cpa_code = ' || l_override_cpa_code);
319
320 OPEN cur_ppa(l_payroll_action_id);
321 FETCH cur_ppa
322 INTO l_payment_date,
323 l_direct_deposit_date;
324 CLOSE cur_ppa;
325
326 hr_utility.trace('l_payment_date = ' || to_char(l_payment_date));
327 hr_utility.trace('l_direct_deposit_date = ' ||
328 to_char(l_direct_deposit_date));
329 SELECT
330 decode(l_dd_type, 'NOVA_SCOT',to_char(l_direct_deposit_date,'YYDDD'),
331 'TD', to_char(l_direct_deposit_date,'DDMMYY'),
332 'CPA','0'||to_char(l_direct_deposit_date,'YYDDD'),
333 'CIBC',to_char(l_direct_deposit_date,'YYMMDD'))
334 INTO
335 l_dd_date
336 FROM
337 DUAL;
338
339 SELECT
340 decode(l_dd_type, 'NOVA_SCOT',to_char(l_payment_date,'YYDDD'),
341 'TD', to_char(l_payment_date,'DDMMYY'),
342 'CPA','0'||to_char(l_payment_date,'YYDDD'),
343 'CIBC',to_char(l_payment_date,'YYMMDD'))
344 INTO
345 l_payment_date1
346 FROM
347 DUAL;
348 l_text :=
349 '<DEPOSIT_DATE_CA>' || l_payment_date1 || '</DEPOSIT_DATE_CA>' ||
350 '<FILE_CREATION_DATE_CA>'|| l_dd_date || '</FILE_CREATION_DATE_CA>' ||
351 '<OVERRIDE_CPA_CODE>' || l_override_cpa_code || '</OVERRIDE_CPA_CODE>';
352
353 pay_core_files.write_to_magtape_lob(l_text);
354 hr_utility.trace('Add Custom XML ends here .......');
355
356 END add_custom_xml;
357
358 ---- Procedures / Functions Added for CA PDF Payslip enhancement. (Bug 9445414)
359
360 PROCEDURE add_custom_xml (P_ASSIGNMENT_ACTION_ID IN NUMBER ,
361 P_ACTION_INFORMATION_CATEGORY IN VARCHAR2,
362 P_DOCUMENT_TYPE IN VARCHAR2) AS
363
364 TYPE char_tab IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
365
366
367 CURSOR cur_ppa(p_payroll_action_id NUMBER) IS
368 SELECT
369 SYSDATE,
370 NVL(overriding_dd_date,effective_date)
371 FROM
372 pay_payroll_actions
373 WHERE
374 payroll_action_id = p_payroll_action_id;
375
376 CURSOR get_employee_country (CP_ASSIGNMENT_ACTION_ID IN NUMBER) IS
377 select ft.nls_territory
378 from pay_action_information pai,fnd_territories ft
379 where pai.action_context_id = cp_assignment_action_id
380 and pai.action_information_category='ADDRESS DETAILS'
381 and ft.territory_code=pai.action_information13;
382
383 CURSOR get_employer_country (CP_ASSIGNMENT_ACTION_ID IN NUMBER) IS
384 select DISTINCT ft.nls_territory
385 from pay_action_information pai,fnd_territories ft
386 where pai.action_context_id =
387 (SELECT payroll_action_id
388 FROM pay_assignment_actions
389 WHERE assignment_action_id = cp_assignment_action_id)
390 and pai.action_information_category='ADDRESS DETAILS'
391 and ft.territory_code=pai.action_information13;
392
393 CURSOR get_net_pay(CP_ASSIGNMENT_ACTION_ID IN NUMBER) IS
394 SELECT net_pay
395 FROM PAY_AC_EMP_SUM_ACTION_INFO_V
396 WHERE action_context_id = cp_assignment_action_id
397 AND action_information_category = 'AC SUMMARY CURRENT';
398
399 CURSOR get_net_pay_ytd(CP_ASSIGNMENT_ACTION_ID IN NUMBER) is
400 SELECT net_pay
401 FROM PAY_AC_EMP_SUM_ACTION_INFO_V
402 WHERE action_context_id = cp_assignment_action_id
403 AND ACTION_INFORMATION_CATEGORY = 'AC SUMMARY YTD';
404
405 CURSOR get_proposed_salary(CP_ASSIGNMENT_ACTION_ID IN NUMBER) is
406 SELECT nvl(ACTION_INFORMATION28,0)
407 FROM PAY_ACTION_INFORMATION
408 WHERE action_context_id = cp_assignment_action_id
409 AND action_information_category = 'EMPLOYEE DETAILS';
410
411 CURSOR get_net_pay_dstr_details ( cp_assignment_action_id in number) IS
412 SELECT check_deposit_number,
413 -- org_payment_method_id added for bug 13024522
414 org_payment_method_id,
415 segment5,
416 segment2,
417 segment3,
418 value,segment4,segment7 from
419 pay_emp_net_dist_action_info_v
420 WHERE action_context_id=cp_assignment_action_id;
421
422 /* Changes for Chequewriter (XML) Third party Chequewriter (XML) and Deposit Advice (XML) Enhancements starts here*/
423 CURSOR get_check_depoad_details ( arch_assact_id in number ,
424 chk_assact_id in number) IS
425
426 SELECT pai.action_information16, ppt.CATEGORY, pai.action_information5,
427 pai.action_information6, pai.action_information7,
428 pai.action_information8, pai.action_information9,
429 pai.action_information10, paa.serial_number
430 FROM pay_action_information pai,
431 pay_org_payment_methods_f popmf,
432 pay_payment_types ppt,
433 pay_assignment_actions paa
434 WHERE pai.action_context_id = arch_assact_id
435 AND pai.action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
436 AND paa.assignment_action_id = chk_assact_id
437 AND popmf.org_payment_method_id = pai.action_information1
438 AND popmf.payment_type_id = ppt.payment_type_id
439 AND (paa.pre_payment_id = pai.action_information15 or ppt.CATEGORY = 'CH')
440 AND pai.effective_date BETWEEN popmf.effective_start_date AND popmf.effective_end_date;
441
442 CURSOR get_preassact_id ( arch_assact_id in number) IS
443 /* SELECT locked_action_id
444 FROM pay_action_interlocks
445 WHERE locking_action_id = arch_assact_id; */
446 /* Bug 14052902 - Modified the cursor to display appropriate advice number
447 in Emloyee details section when seperate payemnts are made */
448 SELECT fnd_number.canonical_to_number (substr (serial_number,3))
449 FROM pay_assignment_actions
450 WHERE assignment_action_id = arch_assact_id;
451
452 /*Changes for bug 13870178 starts here */
453 /* Bug 14052902 - Modified following cursor to display Bonus amount in Net Pay Distribution Section */
454 CURSOR get_depoadvice_deatils ( arch_assact_id in number) IS
455 SELECT pai.action_information5
456 , decode (pai.action_information6, 'C'
457 , 'Checking Account', 'Savings Account')
458 , pai.action_information7
459 , pai.action_information8
460 , pai.action_information9
461 , pai.action_information10
462 , fnd_number.canonical_to_number (substr (paa2.serial_number,3))
463 , pai.action_information16
464 , pai.action_information2
465 , pai.action_information11
466 , ppt.category
467 , pay_assignment_actions_pkg.get_payment_status (pail.locked_action_id, pai.action_information15) status
468 FROM pay_action_information pai
469 , pay_org_payment_methods_f popmf
470 , pay_payment_types ppt
471 , pay_action_interlocks pail
472 , pay_payroll_actions ppa
473 , pay_assignment_actions paa
474 , pay_assignment_actions paa2
475 WHERE pai.action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
476 AND pai.action_context_id = arch_assact_id
477 AND paa2.assignment_action_id = pai.action_context_id
478 AND pai.action_context_id = pail.locking_action_id
479 AND paa.assignment_action_id = pail.locked_action_id
480 AND ppa.payroll_action_id = paa.payroll_action_id
481 AND ppa.action_type IN ('U', 'P')
482 AND pai.action_information1 = popmf.org_payment_method_id
483 AND popmf.payment_type_id = ppt.payment_type_id
484 AND ppt.category = 'MT'
485 AND pai.effective_date
486 BETWEEN popmf.effective_start_date
487 AND popmf.effective_end_date;
488 /*Changes for bug 13870178 starts here */
489
490 CURSOR get_check_num_for_depad ( cp_assignment_action_id in number ) IS
491 SELECT paa.serial_number, pain.action_information16 ,
492 pain.action_information9 ,
493 DECODE (pain.action_information6,
494 'C', 'Checking Account',
495 'Savings Account'
496 ),
497 pain.action_information7
498 FROM pay_action_interlocks pai,
499 pay_assignment_actions paa,
500 pay_payroll_actions ppa,
501 pay_action_interlocks pai1,
502 pay_action_information pain
503 WHERE pai.locking_action_id = cp_assignment_action_id
504 AND pai.locked_action_id = pai1.locked_action_id
505 AND pai.locking_action_id <> pai1.locking_action_id
506 AND pai1.locking_action_id = paa.assignment_action_id
507 AND paa.payroll_action_id = ppa.payroll_action_id
508 AND ppa.action_type = 'H'
509 AND pain.action_information15 = paa.pre_payment_id
510 AND pain.action_context_id = pai.locking_action_id
511 AND pain.action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION' ;
512
513 CURSOR get_third_party_check_info (cp_assactid in number, cp_chk_assactid in number) IS
514 SELECT paa.serial_number
515 , pai.action_information3 amount
516 , ltrim (initcap (rtrim (ppf.title)) || ' ' || rtrim (ppf.first_name) || ' ' || rtrim (ppf.last_name)) employee_name
517 , pea.segment4 er_transit_code /*13849708*/
518 , pea.segment3 er_account_number /*13849708*/
519 FROM pay_assignment_actions paa
520 , pay_action_information pai
521 , per_all_assignments_f paf
522 , per_all_people_f ppf
523 , pay_external_accounts pea
524 , pay_org_payment_methods_f popm
525 WHERE paa.assignment_action_id = cp_chk_assactid
526 AND pai.action_context_id = cp_assactid
527 AND paa.pre_payment_id = pai.action_information2
528 AND paf.assignment_id = pai.assignment_id
529 AND ppf.person_id = paf.person_id
530 AND pai.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
531 AND pai.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
532 AND popm.org_payment_method_id = pai.action_information5
533 AND pai.effective_date BETWEEN popm.effective_start_date AND popm.effective_end_date
534 AND pea.external_account_id = popm.external_account_id;
535 /* Changes for Chequewriter (XML) Third party Chequewriter (XML) and Deposit Advice (XML) Enhancements ends here*/
536
537 l_direct_deposit_date DATE;
538 l_dd_date VARCHAR2(30);
539 l_dd_type VARCHAR2(20);
540 l_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
541 l_text VARCHAR(900);
542 l_override_cpa_code VARCHAR2(100);
543 l_payment_date DATE;
544 l_payment_date1 VARCHAR2(30);
545
546 ln_amount number;
547 ln_net_pay_ytd number;
548 ln_proposed_salary number;
549
550 lv_check_number varchar2(200);
551 -- org_payment_method_id added for bug 13024522
552 lv_org_payment_method_id varchar2(240);
553 ln_check_value number ;
554 lv_account_name varchar2(200);
555 lv_account_type varchar2(200);
556 ln_account_number varchar2(200);
557 lv_transit_code varchar2(200);
558 lv_bank_name varchar2(200);
559 lv_bank_number varchar2(200);
560 ln_employee_country varchar2(200); --For displaying country code as country
561 ln_employer_country varchar2(200); --name on PDF Payslip.
562
563 /* Changes for Chequewriter (XML) Third party Chequewriter (XML) and Deposit Advice (XML) Enhancements starts here*/
564 lv_category varchar2(200) := 'DA';
565 lv_bank_branch varchar2(200);
566 lv_amount_in_word varchar2(200);
567 ln_depoad_num number ;
568 ln_paymethod_id number;
569 ln_deposit_advice_number number ;
570 ln_account_number1 number ;
571 ln_paymethod_type varchar2(20);
572 lv_employee_name varchar2(200);
573 lv_payment_status varchar2(20); /*bug 13870178*/
574 lv_ER_Transit_code varchar2(20); /*bug 13849708*/
575 lv_ER_Account_Number varchar2(20); /*bug 13849708*/
576 /* Changes for Chequewriter (XML) Third party Chequewriter (XML) and Deposit Advice (XML) Enhancements ends here*/
577
578 BEGIN
579
580 hr_utility.trace('Add Custom XML starts here .... ');
581 hr_utility.trace('DETAILS-------'||P_ASSIGNMENT_ACTION_ID||'---'||P_ACTION_INFORMATION_CATEGORY||'---'||P_DOCUMENT_TYPE);
582
583 l_payroll_action_id
584 := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
585 l_override_cpa_code
586 := pay_magtape_generic.get_parameter_value('OVERRIDE_CPA_CODE');
587 l_dd_type
588 := pay_magtape_generic.get_parameter_value('MAGTAPE_REPORT_ID');
589
590 /* Added the code for CA pdf payslip enhancement bug:9445414 */
591
592 IF p_document_type = 'PAYSLIP'
593 AND p_action_information_category IS NOT NULL THEN
594
595 IF p_action_information_category = 'EMPLOYEE DETAILS' THEN
596 OPEN get_proposed_salary(p_assignment_action_id);
597 FETCH get_proposed_salary INTO ln_proposed_salary;
598
599 /* Takes care of NULL value for proposed salary. Custom tag
600 PROPOSED_SALARY_CUSTOM is populated*/
601
602 pay_payroll_xml_extract_pkg.load_xml_data('D','PROPOSED_SALARY_CUSTOM',ln_proposed_salary );
603 CLOSE get_proposed_salary;
604
605 /* Changes for Deposit Advice (XML) Enhancements starts here*/
606 OPEN get_preassact_id (P_ASSIGNMENT_ACTION_ID);
607 FETCH get_preassact_id INTO ln_deposit_advice_number ;
608 CLOSE get_preassact_id ;
609 pay_payroll_xml_extract_pkg.load_xml_data('D','DEPOSIT_FINAL_ADNUM',ln_deposit_advice_number);
610 OPEN get_depoadvice_deatils(P_ASSIGNMENT_ACTION_ID);
611 LOOP
612 FETCH get_depoadvice_deatils INTO lv_account_name,
613 lv_account_type,
614 ln_account_number,
615 lv_transit_code,
616 lv_bank_name ,
617 lv_bank_branch,
618 ln_depoad_num ,
619 ln_check_value,
620 ln_paymethod_id, --Added for the bug#9541448
621 lv_bank_number,
622 ln_paymethod_type, --Added for the bug#9541448
623 lv_payment_status ; /* bug 13870178 */
624 IF get_depoadvice_deatils%NOTFOUND THEN
625 close get_depoadvice_deatils ;
626 exit;
627 ELSE
628 IF lv_payment_status = 'Paid' THEN /*bug 13870178 */
629 pay_payroll_xml_extract_pkg.load_xml_data('CS','DEPOSIT_ADVICE_DETAILS',null);
630 pay_payroll_xml_extract_pkg.load_xml_data('D','ACCOUNT_NAME',lv_account_name);
631 pay_payroll_xml_extract_pkg.load_xml_data('D','ACCOUNT_TYPE',lv_account_type);
632 pay_payroll_xml_extract_pkg.load_xml_data('D','ACCOUNT_NUMBER',ln_account_number);
633 pay_payroll_xml_extract_pkg.load_xml_data('D','TRANSIT_CODE',lv_transit_code);
634 pay_payroll_xml_extract_pkg.load_xml_data('D','BANK_NAME',lv_bank_name);
635 pay_payroll_xml_extract_pkg.load_xml_data('D','BANK_NUMBER',lv_bank_number);
636 pay_payroll_xml_extract_pkg.load_xml_data('D','BANK_BRANCH',lv_bank_branch);
637 pay_payroll_xml_extract_pkg.load_xml_data('D','DEPOSIT_ADVICE_NUMBER',ln_depoad_num);
638 pay_payroll_xml_extract_pkg.load_xml_data('D','DEPOSIT_ADVICE_VALUE',ln_check_value);
639 pay_payroll_xml_extract_pkg.load_xml_data('D','PAYMETHOD_ID',ln_paymethod_id);
640 pay_payroll_xml_extract_pkg.load_xml_data('D','PAYMETHOD_TYPE',ln_paymethod_type);
641 pay_payroll_xml_extract_pkg.load_xml_data('CE','DEPOSIT_ADVICE_DETAILS',null);
642 END IF;
643 END IF;
644 END LOOP;
645
646 /* Changes for Deposit Advice (XML) Enhancements ends here*/
647
648 /*Added the code to display net pay distribution section on pdf payslip
649 it appends employee details with new context CA_EMPLOYEE_NET_PAY_DISTRIBUTION */
650
651 OPEN get_net_pay_dstr_details (P_ASSIGNMENT_ACTION_ID);
652 LOOP
653 FETCH get_net_pay_dstr_details INTO lv_check_number,
654 -- org_payment_method_id added for bug 13024522
655 lv_org_payment_method_id,
656 lv_bank_name,
657 lv_account_type,
658 ln_account_number,
659 ln_check_value,
660 lv_transit_code,
661 lv_bank_number;
662 IF get_net_pay_dstr_details%NOTFOUND THEN
663 close get_net_pay_dstr_details;
664 EXIT;
665 ELSE
666 pay_payroll_xml_extract_pkg.load_xml_data('CS','CA_EMPLOYEE_NET_PAY_DISTRIBUTION',null);
667 pay_payroll_xml_extract_pkg.load_xml_data('D','CHECK_DEPOSIT_NUMBER',lv_check_number);
668
669 -- org_payment_method_id added for bug 13024522
670 pay_payroll_xml_extract_pkg.load_xml_data('D','ORG_PAYMENT_METHOD_ID',lv_org_payment_method_id);
671
672 pay_payroll_xml_extract_pkg.load_xml_data('D','VALUE',ln_check_value);
673 pay_payroll_xml_extract_pkg.load_xml_data('D','ACCOUNT_TYPE',lv_account_type);
674 pay_payroll_xml_extract_pkg.load_xml_data('D','BANK_NAME',lv_bank_name);
675 pay_payroll_xml_extract_pkg.load_xml_data('D','BANK_NUMBER',lv_bank_number);
676 pay_payroll_xml_extract_pkg.load_xml_data('D','ACCOUNT_NUMBER',
677 HR_GENERAL2.mask_characters(ln_account_number));
678 pay_payroll_xml_extract_pkg.load_xml_data('D','TRANSIT_CODE',lv_transit_code);
679 pay_payroll_xml_extract_pkg.load_xml_data('CE','CA_EMPLOYEE_NET_PAY_DISTRIBUTION',null);
680
681 END IF;
682 END LOOP;
683
684 END IF;
685
686 /*Added the code to display net pay current and ytd values on pdf payslip */
687
688 IF p_action_information_category = 'AC SUMMARY YTD' THEN
689
690 OPEN get_net_pay_ytd(p_assignment_action_id);
691 FETCH get_net_pay_ytd INTO ln_net_pay_ytd;
692 pay_payroll_xml_extract_pkg.load_xml_data('D','NET_PAY_YTD',ln_net_pay_ytd );
693 CLOSE get_net_pay_ytd;
694
695 END IF;
696
697 IF p_action_information_category = 'AC SUMMARY CURRENT' THEN
698
699 OPEN get_net_pay(p_assignment_action_id);
700 FETCH get_net_pay into ln_amount;
701 CLOSE get_net_pay;
702
703 pay_payroll_xml_extract_pkg.load_xml_data('D','NET_PAY',ln_amount);
704
705 END IF;
706
707 /*Added the code to display employee and employer country name on pdf payslip */
708
709 IF p_action_information_category = 'ADDRESS DETAILS' THEN
710 OPEN get_employee_country(p_assignment_action_id);
711 FETCH get_employee_country INTO ln_employee_country;
712 pay_payroll_xml_extract_pkg.load_xml_data('D','EE_COUNTRY',ln_employee_country );
713 CLOSE get_employee_country;
714
715 OPEN get_employer_country(p_assignment_action_id);
716 FETCH get_employer_country INTO ln_employer_country;
717 pay_payroll_xml_extract_pkg.load_xml_data('D','ER_COUNTRY',ln_employer_country );
718 CLOSE get_employer_country;
719
720 END IF;
721 END IF;
722
723 /* Changes for Chequewriter (XML) Third party Chequewriter (XML) and Deposit Advice (XML) Enhancements starts here*/
724 IF p_document_type = 'ARCHIVE_CHEQUE_WRITER'
725 AND p_action_information_category IS NOT NULL THEN
726
727
728 IF p_action_information_category = 'EMPLOYEE DETAILS' THEN
729 hr_utility.trace('sbachu employee details.......');
730 OPEN get_check_depoad_details(P_ASSIGNMENT_ACTION_ID,
731 pay_archive_chequewriter.g_chq_asg_action_id);
732 FETCH get_check_depoad_details INTO ln_check_value,
733 lv_category,
734 lv_account_name,
735 lv_account_type,
736 ln_account_number,
737 lv_transit_code,
738 lv_bank_name ,
739 lv_bank_branch,
740 lv_check_number;
741 CLOSE get_check_depoad_details;
742 IF lv_category = 'CH' THEN
743 hr_utility.trace('sbachu employee details2.......');
744 pay_payroll_xml_extract_pkg.load_xml_data('D','CHECK_NUMBER',lv_check_number);
745 lv_amount_in_word := CF_word_amountFormula(ln_check_value);
746 pay_payroll_xml_extract_pkg.load_xml_data('D','AMOUNT_IN_WORDS',lv_amount_in_word);
747 pay_payroll_xml_extract_pkg.load_xml_data('D','CHECK_AMOUNT',ln_check_value);
748 pay_payroll_xml_extract_pkg.load_xml_data('D','ACCOUNT_NAME',lv_account_name);
749 pay_payroll_xml_extract_pkg.load_xml_data('D','ACCOUNT_TYPE',lv_account_type);
750 pay_payroll_xml_extract_pkg.load_xml_data('D','ACCOUNT_NUMBER',ln_account_number);
751 pay_payroll_xml_extract_pkg.load_xml_data('D','TRANSIT_CODE',lv_transit_code);
752 pay_payroll_xml_extract_pkg.load_xml_data('D','BANK_NAME',lv_bank_name);
753 pay_payroll_xml_extract_pkg.load_xml_data('D','BANK_BRANCH',lv_bank_branch);
754 END IF;
755
756 IF lv_category <> 'CH' THEN
757 OPEN get_check_num_for_depad (P_ASSIGNMENT_ACTION_ID);
758 LOOP
759 FETCH get_check_num_for_depad INTO ln_account_number,
760 ln_check_value,
761 lv_bank_name,
762 lv_account_type,
763 ln_account_number1;
764 IF get_check_num_for_depad%NOTFOUND THEN
765 close get_check_num_for_depad;
766 EXIT;
767 ELSE
768 pay_payroll_xml_extract_pkg.load_xml_data('CS','CHECK_DETAILS',null);
769 pay_payroll_xml_extract_pkg.load_xml_data('D','DEPOSIT_ADVICE_NUMBER',ln_account_number);
770 pay_payroll_xml_extract_pkg.load_xml_data('D','DEPOSIT_ADVICE_VALUE',ln_check_value);
771 pay_payroll_xml_extract_pkg.load_xml_data('D','ACCOUNT_TYPE',lv_account_type);
772 pay_payroll_xml_extract_pkg.load_xml_data('D','BANK_NAME',lv_bank_name);
773 pay_payroll_xml_extract_pkg.load_xml_data('D','ACCOUNT_NUMBER',ln_account_number1);
774 pay_payroll_xml_extract_pkg.load_xml_data('CE','CHECK_DETAILS',null);
775
776
777 END IF;
778 END LOOP;
779 END IF;
780 END IF;
781
782 IF p_action_information_category = 'AC SUMMARY YTD' THEN
783 hr_utility.trace('sbachu AC SUMMARY YTD.......');
784 OPEN get_net_pay_ytd(p_assignment_action_id);
785 FETCH get_net_pay_ytd INTO ln_net_pay_ytd;
786 pay_payroll_xml_extract_pkg.load_xml_data('D','NET_PAY_YTD',ln_net_pay_ytd );
787 CLOSE get_net_pay_ytd;
788
789 END IF;
790
791 IF p_action_information_category = 'AC SUMMARY CURRENT' THEN
792 hr_utility.trace('sbachu AC SUMMARY current.......');
793 OPEN get_net_pay(p_assignment_action_id);
794 FETCH get_net_pay into ln_amount;
795 CLOSE get_net_pay;
796
797 pay_payroll_xml_extract_pkg.load_xml_data('D','NET_PAY',ln_amount);
798
799 END IF;
800
801 END IF;
802
803 IF P_DOCUMENT_TYPE like '%THIRD%PARTY%CHEQUE%WRITER' AND
804 (P_ACTION_INFORMATION_CATEGORY like '%THIRD%PARTY%CHEQUES')
805 THEN
806 /*changes for bug 13849708 starts here*/
807 OPEN get_third_party_check_info(P_ASSIGNMENT_ACTION_ID, pay_archive_chequewriter.g_chq_asg_action_id);
808 FETCH get_third_party_check_info into ln_depoad_num, ln_check_value, lv_employee_name,lv_ER_Transit_code, lv_ER_Account_Number;
809 CLOSE get_third_party_check_info;
810 lv_amount_in_word := CF_word_amountFormula(ln_check_value);
811 pay_payroll_xml_extract_pkg.load_xml_data('D','CHECK_NUMBER',ln_depoad_num);
812 pay_payroll_xml_extract_pkg.load_xml_data('D','AMOUNT_IN_WORDS',lv_amount_in_word);
813 pay_payroll_xml_extract_pkg.load_xml_data('D','EMPLOYEE_NAME',lv_employee_name);
814 pay_payroll_xml_extract_pkg.load_xml_data('D','TRANSIT_CODE',lv_ER_Transit_code);
815 pay_payroll_xml_extract_pkg.load_xml_data('D','ACCOUNT_NUMBER',lv_ER_Account_Number);
816 /*changes for bug 13849708 ends here*/
817 END IF;
818
819 IF P_DOCUMENT_TYPE like '%THIRD%PARTY%CHEQUE%WRITER' AND
820 (P_ACTION_INFORMATION_CATEGORY IS NULL)
821 THEN
822 hr_utility.trace('Null Action Information Category');
823 END IF;
824
825 IF p_document_type <> 'PAYSLIP' and p_document_type <> 'ARCHIVE_CHEQUE_WRITER' and P_DOCUMENT_TYPE not like '%THIRD%PARTY%CHEQUE%WRITER' THEN
826
827
828 hr_utility.trace('l_payroll_action_id = ' ||
829 to_char(l_payroll_action_id));
830 hr_utility.trace('l_override_cpa_code = ' || l_override_cpa_code);
831
832 OPEN cur_ppa(l_payroll_action_id);
833 FETCH cur_ppa
834 INTO l_payment_date,
835 l_direct_deposit_date;
836 CLOSE cur_ppa;
837
838 hr_utility.trace('l_payment_date = ' || to_char(l_payment_date));
839 hr_utility.trace('l_direct_deposit_date = ' ||
840 to_char(l_direct_deposit_date));
841 SELECT
842 decode(l_dd_type, 'NOVA_SCOT',to_char(l_direct_deposit_date,'YYDDD'),
843 'TD', to_char(l_direct_deposit_date,'DDMMYY'),
844 'CPA','0'||to_char(l_direct_deposit_date,'YYDDD'),
845 'CIBC',to_char(l_direct_deposit_date,'YYMMDD'))
846 INTO
847 l_dd_date
848 FROM
849 DUAL;
850
851 SELECT
852 decode(l_dd_type, 'NOVA_SCOT',to_char(l_payment_date,'YYDDD'),
853 'TD', to_char(l_payment_date,'DDMMYY'),
854 'CPA','0'||to_char(l_payment_date,'YYDDD'),
855 'CIBC',to_char(l_payment_date,'YYMMDD'))
856 INTO
857 l_payment_date1
858 FROM
859 DUAL;
860 l_text :=
861 '<DEPOSIT_DATE_CA>' || l_payment_date1 || '</DEPOSIT_DATE_CA>' ||
862 '<FILE_CREATION_DATE_CA>'|| l_dd_date || '</FILE_CREATION_DATE_CA>' ||
863 '<OVERRIDE_CPA_CODE>' || l_override_cpa_code || '</OVERRIDE_CPA_CODE>';
864
865 pay_core_files.write_to_magtape_lob(l_text);
866
867 END IF;
868 hr_utility.trace('Add Custom XML ends here .......');
869 /* Changes for Chequewriter (XML) Third party Chequewriter (XML) and Deposit Advice (XML) Enhancements ends here*/
870
871 END add_custom_xml;
872
873 /* Changes for Chequewriter (XML) Third party Chequewriter (XML) Enhancements starts here*/
874 /*Changes for bugs 13849662, 13849708 starts here */
875 FUNCTION CF_word_amountFormula(CP_LN_AMOUNT IN NUMBER) RETURN VARCHAR2 IS
876
877 l_word_text varchar2(250);
878 l_width number := 100; -- Width of word amount field
879 l_asterisk_length number :=4 ;
880 BEGIN
881 l_word_text := get_word_value(cp_ln_amount);
882
883 -- Format the output to have asterisks on left-hand side
884 IF NVL(LENGTH(l_word_text), 0) <= l_width THEN
885
886 IF NVL(LENGTH(l_word_text),0) <= (l_width - l_asterisk_length) THEN
887 l_word_text := lpad(l_word_text,NVL(length(l_word_text),0)+l_asterisk_length,'*');
888 ELSE
889 l_word_text := lpad(l_word_text,l_width,'*');
890 END IF;
891
892 ELSIF NVL(LENGTH(l_word_text), 0) <= l_width*2 THEN
893 -- Allow for word wrapping
894 l_word_text := lpad(l_word_text,l_width*2 -
895 (l_width-instr(substr(l_word_text,1,l_width+1),' ',-1)),'*');
896 ELSIF NVL(LENGTH(l_word_text), 0) <= l_width*3 THEN
897
898 l_word_text := lpad(l_word_text,l_width*3,'*');
899 END IF;
900 RETURN(l_word_text);
901 END CF_word_amountFormula ;
902 /*Changes for bugs 13849662, 13849708 ends here */
903
904
905
906 FUNCTION get_word_value (P_AMOUNT NUMBER) RETURN VARCHAR2 IS
907
908 l_word_amount varchar2(240) := convert_number(trunc(p_amount));
909 l_currency_word varchar2(240);
910 l_log integer;
911 l_unit_ratio number := 100; --ie. the number of subunits(cents) in a unit(dollar)
912 l_unit_singular varchar2(6) := 'Dollar';
913 l_unit_plural varchar2(7) := 'Dollars';
914 l_sub_unit_singular varchar2(4) := 'Cent';
915 l_sub_unit_plural varchar2(5) := 'Cents';
916
917 /* This is a workaround until bug #165793 is fixed */
918 FUNCTION my_log (a integer, b integer) RETURN NUMBER IS
919 BEGIN
920 IF a <> 10 THEN RETURN(NULL);
921 ELSIF b > 0 AND b <= 10 THEN RETURN(1);
922 ELSIF b > 10 AND b <= 100 THEN RETURN(2);
923 ELSIF b > 100 AND b <= 1000 THEN RETURN(3);
924 ELSE RETURN(NULL);
925 END IF;
926 RETURN NULL;
927 END my_log;
928
929 BEGIN
930 l_log := my_log(10,l_unit_ratio);
931
932 select initcap(lower(
933 l_word_amount||' '||
934 decode(trunc(p_amount),
935 1,l_unit_singular,
936 l_unit_plural)||' And '||
937 lpad(to_char(trunc((p_amount-trunc(p_amount))*l_unit_ratio)),
938 ceil(l_log),'0')||' '||
939 decode(trunc((p_amount-trunc(p_amount))*l_unit_ratio),
940 1,l_sub_unit_singular,
941 l_sub_unit_plural)
942 ))
943 into l_currency_word
944 from dual;
945
946 RETURN(l_currency_word);
947 END get_word_value;
948
949
950 FUNCTION convert_number(IN_NUMERAL INTEGER := 0) RETURN VARCHAR2 IS
951
952 number_too_large exception;
953 numeral integer := abs(in_numeral);
954 max_digit integer := 9; -- for numbers less than a (US) billion
955 number_text varchar2(240) := '';
956 current_segment varchar2(80);
957 b_zero varchar2(25) := 'Zero';
958 b_thousand varchar2(25) := ' Thousand ';
959 thousand number := power(10,3);
960 b_million varchar2(25) := ' Million ';
961 million number := power(10,6);
962
963 FUNCTION convert_number (segment number) RETURN VARCHAR2 IS
964 value_text varchar2(80);
965 BEGIN
966 value_text := to_char( to_date(segment,'YYYY'),'Yyyysp');
967 RETURN(value_text);
968 END;
969
970 BEGIN
971
972 IF numeral >= power(10,max_digit) THEN
973 RAISE number_too_large;
974 END IF;
975
976 IF numeral = 0 THEN
977 RETURN(b_zero);
978 END IF;
979
980 current_segment := trunc(numeral/million);
981 numeral := numeral - (current_segment * million);
982 IF current_segment <> 0 THEN
983 number_text := number_text||convert_number(current_segment)||b_million;
984 END IF;
985
986 current_segment := trunc(numeral/thousand);
987 numeral := numeral - (current_segment * thousand);
988 IF current_segment <> 0 THEN
989 number_text := number_text||convert_number(current_segment)||b_thousand;
990 END IF;
991
992 IF numeral <> 0 THEN
993 number_text := number_text||convert_number(numeral);
994 END IF;
995
996 number_text := substr(number_text,1,1) ||
997 rtrim(lower(substr(number_text,2,NVL(length(number_text), 0))));
998
999 RETURN(number_text);
1000
1001 EXCEPTION
1002 WHEN number_too_large THEN
1003 RETURN(null);
1004 WHEN OTHERS THEN
1005 RETURN(null);
1006 END convert_number ;
1007
1008 /* Changes for Chequewriter (XML) Third party Chequewriter (XML) and Deposit Advice (XML) Enhancements ends here*/
1009
1010 -- modification for CA PDF Payslip enhancement ends here (Bug 9445414)
1011
1012 FUNCTION work_schedule_total_hours(
1013 assignment_action_id IN number --Context
1014 ,assignment_id IN number --Context
1015 ,p_bg_id IN NUMBER -- Context
1016 ,element_entry_id IN number --Context
1017 ,date_earned IN DATE --Context
1018 ,p_range_start IN DATE
1019 ,p_range_end IN DATE
1020 ,p_wk_sch_found IN OUT NOCOPY VARCHAR2 )
1021 RETURN NUMBER IS
1022
1023 -- local constants
1024 c_ws_tab_name VARCHAR2(80);
1025
1026 -- local variables
1027 v_total_hours NUMBER(15,7);
1028 v_range_start DATE;
1029 v_range_end DATE;
1030 v_curr_date DATE;
1031 v_curr_day VARCHAR2(3); -- 3 char abbrev for day of wk.
1032 v_ws_name VARCHAR2(80); -- Work Schedule Name.
1033 v_gtv_hours VARCHAR2(80); -- get_table_value returns varchar2
1034 v_fnd_sess_row VARCHAR2(1);
1035 l_exists VARCHAR2(1);
1036 v_day_no NUMBER;
1037 p_ws_name VARCHAR2(80); -- Work Schedule Name from SCL
1038 l_id_flex_num NUMBER;
1039
1040 CURSOR get_id_flex_num IS
1041 SELECT rule_mode
1042 FROM pay_legislation_rules
1043 WHERE legislation_code = 'CA'
1044 and rule_type = 'S';
1045
1046 Cursor get_ws_name (p_id_flex_num number,
1047 p_date_earned date,
1048 p_assignment_id number) IS
1049 SELECT target.SEGMENT4
1050 FROM /* route for SCL keyflex - assignment level */
1051 hr_soft_coding_keyflex target,
1052 per_all_assignments_f ASSIGN
1053 WHERE p_date_earned BETWEEN ASSIGN.effective_start_date
1054 AND ASSIGN.effective_end_date
1055 AND ASSIGN.assignment_id = p_assignment_id
1056 AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
1057 AND target.enabled_flag = 'Y'
1058 AND target.id_flex_num = p_id_flex_num;
1059
1060
1061 BEGIN -- work_schedule_total_hours
1062 /* Init */
1063 v_total_hours := 0;
1064 c_ws_tab_name := 'COMPANY WORK SCHEDULES';
1065
1066 /* get ID FLEX NUM */
1067 --IF pay_us_rules.g_id_flex_num IS NULL THEN
1068 hr_utility.trace('Getting ID_FLEX_NUM for CA legislation ');
1069 OPEN get_id_flex_num;
1070 FETCH get_id_flex_num INTO l_id_flex_num;
1071 -- pay_us_rules.g_id_flex_num := l_id_flex_num;
1072 CLOSE get_id_flex_num;
1073 --END IF;
1074
1075 -- hr_utility.trace('pay_us_rules.g_id_flex_num '||pay_us_rules.g_id_flex_num);
1076 hr_utility.trace('l_id_flex_num '||l_id_flex_num);
1077 hr_utility.trace('assignment_action_id=' || assignment_action_id);
1078 hr_utility.trace('assignment_id=' || assignment_id);
1079 hr_utility.trace('business_group_id=' || p_bg_id);
1080 hr_utility.trace('p_range_start=' || p_range_start);
1081 hr_utility.trace('p_range_end=' || p_range_end);
1082 hr_utility.trace('element_entry_id=' || element_entry_id);
1083 hr_utility.trace('date_earned ' || date_earned);
1084
1085 /* get work schedule_name */
1086 --IF pay_us_rules.g_id_flex_num IS NOT NULL THEN
1087 IF l_id_flex_num IS NOT NULL THEN
1088 hr_utility.trace('getting work schedule name ');
1089 OPEN get_ws_name (l_id_flex_num,--pay_ca_rules.g_id_flex_num,
1090 date_earned,
1091 assignment_id);
1092 FETCH get_ws_name INTO p_ws_name;
1093 CLOSE get_ws_name;
1094 END IF;
1095
1096 IF p_ws_name IS NULL THEN
1097 hr_utility.trace('Work Schedule not found ');
1098 return 0;
1099 END IF;
1100
1101 hr_utility.trace('Work Schedule '||p_ws_name);
1102
1103 --changed to select the work schedule defined
1104 --at the business group level instead of
1105 --hardcoding the default work schedule
1106 --(COMPANY WORK SCHEDULES ) to the
1107 --variable c_ws_tab_name
1108
1109 begin
1110 select put.user_table_name
1111 into c_ws_tab_name
1112 from hr_organization_information hoi
1113 ,pay_user_tables put
1114 where hoi.organization_id = p_bg_id
1115 and hoi.org_information_context ='Work Schedule'
1116 and hoi.org_information1 = put.user_table_id ;
1117
1118 EXCEPTION WHEN NO_DATA_FOUND THEN
1119 null;
1120 end;
1121
1122 -- Set range to a single week if no dates are entered:
1123 -- IF (p_range_start IS NULL) AND (p_range_end IS NULL) THEN
1124 --
1125 v_range_start := NVL(p_range_start, sysdate);
1126 v_range_end := NVL(p_range_end, sysdate + 6);
1127 --
1128 -- END IF;
1129
1130 -- Check for valid range
1131 IF v_range_start > v_range_end THEN
1132 --
1133 RETURN v_total_hours;
1134 -- hr_utility.set_message(801,'PAY_xxxx_INVALID_DATE_RANGE');
1135 -- hr_utility.raise_error;
1136 --
1137 END IF;
1138
1139 -- Get_Table_Value requires row in FND_SESSIONS. We must insert this
1140 -- record if one doe not already exist.
1141 SELECT DECODE(COUNT(session_id), 0, 'N', 'Y')
1142 INTO v_fnd_sess_row
1143 FROM fnd_sessions
1144 WHERE session_id = userenv('sessionid');
1145
1146 IF v_fnd_sess_row = 'N' THEN
1147 dt_fndate.set_effective_date(trunc(sysdate));
1148 END IF;
1149
1150 --
1151 -- Track range dates:
1152 --
1153 -- Check if the work schedule is an id or a name. If the work
1154 -- schedule does not exist, then return 0.
1155 --
1156 BEGIN
1157 select 'Y'
1158 into l_exists
1159 from pay_user_tables PUT,
1160 pay_user_columns PUC
1161 where PUC.USER_COLUMN_NAME = p_ws_name
1162 and NVL(PUC.business_group_id, p_bg_id) = p_bg_id
1163 and NVL(PUC.legislation_code,'CA') = 'CA'
1164 and PUC.user_table_id = PUT.user_table_id
1165 and PUT.user_table_name = c_ws_tab_name;
1166
1167
1168 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
1169 END;
1170
1171 if l_exists = 'Y' then
1172 v_ws_name := p_ws_name;
1173 else
1174 BEGIN
1175 select PUC.USER_COLUMN_NAME
1176 into v_ws_name
1177 from pay_user_tables PUT,
1178 pay_user_columns PUC
1179 where PUC.USER_COLUMN_ID = p_ws_name
1180 and NVL(PUC.business_group_id, p_bg_id) = p_bg_id
1181 and NVL(PUC.legislation_code,'CA') = 'CA'
1182 and PUC.user_table_id = PUT.user_table_id
1183 and PUT.user_table_name = c_ws_tab_name;
1184
1185 EXCEPTION WHEN NO_DATA_FOUND THEN
1186 RETURN v_total_hours;
1187 END;
1188 end if;
1189
1190 v_curr_date := v_range_start;
1191
1192 LOOP
1193
1194 v_day_no := TO_CHAR(v_curr_date, 'D');
1195
1196
1197 SELECT decode(v_day_no,1,'SUN',2,'MON',3,'TUE',
1198 4,'WED',5,'THU',6,'FRI',7,'SAT')
1199 INTO v_curr_day
1200 FROM DUAL;
1201
1202 v_total_hours := v_total_hours +
1203 FND_NUMBER.CANONICAL_TO_NUMBER(
1204 hruserdt.get_table_value(p_bg_id,
1205 c_ws_tab_name,
1206 v_ws_name,
1207 v_curr_day));
1208 v_curr_date := v_curr_date + 1;
1209
1210
1211 EXIT WHEN v_curr_date > v_range_end;
1212
1213 END LOOP;
1214
1215 p_wk_sch_found := 'TRUE'; -- For bug 8459792
1216
1217 RETURN v_total_hours;
1218
1219 END work_schedule_total_hours;
1220
1221 PROCEDURE get_file_creation_no(
1222 pactid IN NUMBER,
1223 file_no OUT NOCOPY NUMBER) AS
1224
1225 l_override_file_no VARCHAR2(20);
1226
1227 CURSOR cur_paid IS
1228 SELECT
1229 legislative_parameters,
1230 business_group_id,
1231 org_payment_method_id
1232 FROM
1233 pay_payroll_actions
1234 WHERE
1235 payroll_action_id = pactid;
1236
1237 l_legislative_parameter pay_payroll_actions.legislative_parameters%TYPE;
1238 l_bg_id pay_payroll_actions.business_group_id%TYPE;
1239 l_org_pm_id pay_payroll_actions.org_payment_method_id%TYPE;
1240 l_dd_format VARCHAR2(30);
1241
1242 BEGIN
1243
1244 hr_utility.trace('Starting pay_ca_rules.get_file_creation_number !!!!');
1245
1246 OPEN cur_paid;
1247 FETCH cur_paid
1248 INTO l_legislative_parameter,
1249 l_bg_id,
1250 l_org_pm_id;
1251 CLOSE cur_paid;
1252
1253
1254 l_override_file_no :=
1255 pay_core_utils.get_parameter('FILE_CREATION_NUMBER_OVERRIDE',
1256 l_legislative_parameter);
1257 l_dd_format := pay_core_utils.get_parameter('MAGTAPE_REPORT_ID',
1258 l_legislative_parameter);
1259
1260 hr_utility.trace('payroll_action_id = ' || to_char(pactid));
1261 hr_utility.trace('l_org_pm_id = ' || to_char(l_org_pm_id));
1262 hr_utility.trace('l_bg_id = ' || to_char(l_bg_id));
1263 hr_utility.trace('l_legislative_parameter = ' || l_legislative_parameter);
1264 hr_utility.trace('l_override_file_no = ' || l_override_file_no);
1265 hr_utility.trace('l_dd_format = ' || l_dd_format);
1266
1267 IF l_override_file_no IS NOT NULL THEN
1268 file_no := l_override_file_no;
1269 ELSE
1270 file_no := pay_ca_direct_deposit_pkg.get_dd_file_creation_number(
1271 l_org_pm_id,
1272 l_dd_format,
1273 l_override_file_no,
1274 pactid ,
1275 l_bg_id) ;
1276 END IF;
1277
1278 hr_utility.trace('file_no = ' || file_no);
1279 hr_utility.trace('Ending pay_ca_rules.get_file_creation_number !!!!');
1280
1281 END get_file_creation_no;
1282 /* Changes for Deposit Advice (XML) Enhancements starts here*/
1283
1284 PROCEDURE payslip_range_cursor(p_pactid in number
1285 ,p_sqlstr out NOCOPY varchar2) IS
1286
1287 lv_sqlstr VARCHAR2(32000);
1288
1289 BEGIN
1290 hr_utility.trace('Entering pay_ca_rules.payslip_range_cursor');
1291 lv_sqlstr := NULL;
1292 pycadar_pkg.range_cursor (pactid => p_pactid, sqlstr => lv_sqlstr);
1293 hr_utility.trace('Returning lv_sqlstr := ' || lv_sqlstr);
1294
1295 p_sqlstr := lv_sqlstr;
1296
1297 END payslip_range_cursor;
1298
1299 PROCEDURE get_token_names(p_pa_token OUT NOCOPY varchar2
1300 ,p_cs_token OUT NOCOPY varchar2) IS
1301 BEGIN
1302
1303 p_pa_token := 'TRANSFER_PAYROLL_ID';
1304 p_cs_token := 'TRANSFER_CONSOLIDATION_SET_ID';
1305
1306 END get_token_names;
1307 /* Changes for Deposit Advice (XML) Enhancements ends here*/
1308
1309 /****************************************************************************/
1310 /* Name : get_custom_xml_routine */
1311 /* Description : This procedure will fetch the custom xml code that is */
1312 /* defined by the user in the respective lookup_code for the */
1313 /* lookup_type 'PAY_CUSTOM_XML_CODE' for the CA loc. */
1314 /****************************************************************************/
1315 -- Added for bug 13969858
1316 PROCEDURE get_custom_xml_routine(p_document_type in varchar2
1317 ,p_xml_routine out NOCOPY varchar2) IS
1318
1319 CURSOR get_dadv_custom_xml_code IS
1320 SELECT hl.description
1321 FROM hr_lookups hl
1322 WHERE hl.lookup_type = 'PAY_CUSTOM_XML_CODE'
1323 AND hl.lookup_code = 'CA_DEPOSIT_ADVICE_XML'
1324 AND hl.enabled_flag = 'Y';
1325
1326 CURSOR get_arch_che_custom_xml_code IS
1327 SELECT hl.description
1328 FROM hr_lookups hl
1329 WHERE hl.lookup_type = 'PAY_CUSTOM_XML_CODE'
1330 AND hl.lookup_code = 'CA_ARCHIVE_CHEQUE'
1331 AND hl.enabled_flag = 'Y';
1332
1333 CURSOR get_thpty_che_custom_xml_code IS
1334 SELECT hl.description
1335 FROM hr_lookups hl
1336 WHERE hl.lookup_type = 'PAY_CUSTOM_XML_CODE'
1337 AND hl.lookup_code = 'CA_THIRD_PARTY_CHEQUE'
1338 AND hl.enabled_flag = 'Y';
1339
1340 BEGIN
1341
1342 IF NVL(fnd_profile.value('PAY_US_DISABLE_CUSTOM_XML_CODE'),'N') = 'Y' THEN /* Condition added for Bug 15952728 */
1343 p_xml_routine := NULL;
1344 ELSE
1345 IF p_document_type = 'PAYSLIP' THEN
1346 OPEN get_dadv_custom_xml_code;
1347 FETCH get_dadv_custom_xml_code INTO p_xml_routine;
1348 CLOSE get_dadv_custom_xml_code;
1349
1350 ELSIF p_document_type = 'ARCHIVE_CHEQUE_WRITER' THEN
1351 OPEN get_arch_che_custom_xml_code;
1352 FETCH get_arch_che_custom_xml_code INTO p_xml_routine;
1353 CLOSE get_arch_che_custom_xml_code;
1354
1355 ELSIF p_document_type = 'THIRD_PARTY_CHEQUE_WRITER' THEN
1356 OPEN get_thpty_che_custom_xml_code;
1357 FETCH get_thpty_che_custom_xml_code INTO p_xml_routine;
1358 CLOSE get_thpty_che_custom_xml_code;
1359 END IF;
1360 END IF;
1361
1362 END;
1363
1364 /*Bug 16686045 starts here*/
1365 FUNCTION get_payslip_sort_order1 RETURN VARCHAR2 IS
1366 BEGIN
1367 return NULL;
1368 END get_payslip_sort_order1;
1369 --
1370 FUNCTION get_payslip_sort_order2 RETURN VARCHAR2 IS
1371 lv_sort_order2 varchar2(50);
1372 BEGIN
1373 lv_sort_order2 := 'ORGANIZATION_NAME';
1374 return lv_sort_order2;
1375 END get_payslip_sort_order2;
1376 --
1377 FUNCTION get_payslip_sort_order3 RETURN VARCHAR2 IS
1378 lv_sort_order3 varchar2(50);
1379 BEGIN
1380 lv_sort_order3 := 'LAST_NAME';
1381 return lv_sort_order3;
1382 END get_payslip_sort_order3;
1383 /*Bug 16686045 ends here*/
1384
1385 end pay_ca_rules;