1 PACKAGE BODY pay_us_employee_payslip_web
2 /* $Header: pyusempw.pkb 120.3.12010000.4 2008/08/06 08:29:17 ubhat ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_employee_payslip_web
21
22 Description : Package contains functions and procedures used
23 by the Online Payslip Views.
24
25 Uses :
26
27 Change List
28 -----------
29 Date Name Vers Bug No Description
30 ---- ---- ---- ------ -----------
31 01-MAY-2008 asgugupt 115.47 6840881 Modified check_emp_personal_payment
32 05-MAR-2008 sudedas 115.45 6739242 Added new Function
33 get_netpaydistr_segment.
34 22-SEP-2005 ahanda 115.17 4622911 Added code for SD for Function
35 "get_full_jurisdiction_name"
36 08-SEP-2005 ppanda 115.16 Function "get_full_jurisdiction_name"
37 added to derive the full jurisdiction
38 (State, County, City) using existing
39 function get_jurisdiction_nmame
40 07-JUN-2005 ahanda 115.42 4417645 Changed code to check for sysdate
41 to be greater then payslip offset
42 to show Payslip
43 05-MAY-2005 ahanda 115.41 4246280 Changed Payslip code to check for
44 View Payslip offset before showing
45 Payslip for an employee.
46 Added overloaded function -
47 check_emp_personal_payment with
48 parameter of p_time_period_id
49 22-FEB-2005 sodhingr 115.40 4186737 changed the get_term_info to use
50 format_to_date function to compare the
51 date.
52 17-FEB-2005 sodhingr 115.39 4186737 changed get_term_info to fix the error
53 due to date format.
54 28-JAN-2005 sodhingr 115.38 4132132 Changed get_term_info to stop the
55 payslip if the terminationdate = period
56 end date
57 19-JAN-2005 sodhingr 115.37 4132132 Changed the function get_term_info
58 24-AUG-2004 rsethupa 115.36 3722370 Changed cursor c2 in function
59 get_term_info. This will select 'Y' for
60 all pay periods upto actual termination
61 date.
62 27-MAY-2004 rsethupa 115.35 3487250 Changed cursor c_currency_code to
63 fetch by hou.organization_id instead
64 of hou.business_group_id
65 28-MAR-2004 ahanda 115.34 Changed check_emp_personal_payment
66 to check if archiver is locking
67 prepay or run action.
68 07-JAN-2004 kaverma 115.33 3350023 Modified cursor c_hourly_salary to remove
69 MERGE JOIN CARTESIAN
70 22-DEC-2003 ahanda 115.32 3331020 Changed cursor c_check_for_reversal.
71 06-Nov-2003 pganguly 115.31 Changed the procedure get_term_info
72 so that it caches the legislation
73 _code, legislation_rule.
74 18-Sep-2003 sdahiya 115.30 2976050 Modified the
75 check_emp_personal_payment procedure
76 so that it calls
77 get_payment_status_code
78 instead of get_payment_status.
79 02-Sep-2003 meshah 115.29 3124483 using actual_termination_date instead
80 of final_prcess_date.
81 02-Sep-2003 meshah 115.28 3124483 the cursor get_person_info in
82 get_doc_eit function has been changed.
83 Now joining to per_periods_of_service
84 to find out if the employee is
85 terminated.
86 19-JUL-2003 ahanda 115.27 Added function format_to_date.
87 23-May-03 ekim 115.26 2897743 Added c_get_lookup_for_paid.
88 30-APR-03 asasthan 115.25 2925411 Added to_char in c_check_number
89 cursor
90 07-Feb-03 ekim 115.24 2716253 Performance fix on c_regular_salary.
91 23-JAN-2002 ahanda 115.23 2764088 Changed cursor get_bg_eit in
92 function get_doc_eit for performance.
93 15-NOV-2002 ahanda 115.22 Modified function get_jurisdiction_name
94 Changed c_get_state to return
95 state_abbrev.
96 14-NOV-2002 tclewis 115.21 Modified the order of parameters
97 on the get_check_number function
98 now pass pp_ass_act , pre_pay_id.
99 21-OCT-2002 tclewis 115.19 changed get_check_no, to return a
100 deposit advice number. Either,
101 pre-payment assignment action id
102 for Master payment or Run AAID for
103 the sep payment AAID.
104 09-OCT-2002 ahanda 115.18 2474524 Changed check_emp_personal_payment
105 15-AUG-2002 ahanda 115.17 Changed get_proposed_emp_salary for
106 performance.
107 18-JUL-2002 ahanda 115.16 Changed the get_jurisdiction_name
108 function to return NULL is not a US
109 jurisdiction.
110 16-JUN-2002 sodhingr 115.15 Added a new function get_term_info
111 to check
112 the terminated employee based
113 on the legislation_field_info
114
115 13-MAY-2002 pganguly 115.13 2363857 Added a new function
116 get_legislation_code.
117 01-MAY-2002 ahanda 115.12 2352332 Changed get_check_number to check
118 for Void.
119 23-MAR-2002 ahanda 115.11 Fixed compilation errors
120 22-MAR-2002 ekim 115.10 Removed trace_on.
121 21-MAR-2002 ekim 115.9 Changed get_doc_eit function.
122 15-FEB-2002 ahanda 115.7 2229092 Changed get_check_number to check for
123 External Manual Payments.
124 24_JAN-2002 dgarg 115.6 Added get_jurisdiction_name
125 function.
126 05-OCT-2001 ekim 115.5 Added get_doc_eit function.
127 21-SEP-2001 ekim 115.4 Added get_format_value function.
128 17-SEP-2001 assathan 115.3 Added get_check_number for payslip
129 09-FEB-2001 ahanda 115.2 Changed the procedure
130 check_emp_personal_payment for
131 performance.
132 14-DEC-2000 ahanda 115.1 1343941/ Changed the procedure
133 1494453 check_emp_personal_payment to go of pre
134 payments instead of personal payment
135 methods. This will also fix issue of
136 Payslip not printing Zero net.
137 10-FEB-2000 ahanda 115.0 Changed proposed_salary to
138 proposed_salary_n for function
139 get_proposed_emp_salary.
140 ****************************************************************************
141 01-FEB-2000 ahanda 110.3 Changed function to get School Dst
142 Name from city if it is not there
143 in county dsts table.
144 01-FEB-2000 ahanda 110.2 Added function to get School Dst Name.
145 24-DEC-1999 ahanda 110.1 1117470 Changed get_proposed_emp_salary to get
146 1116604 proposed salary effective on period end
147 date. Changed the check_for_paid cursor
148 to check for if checkwriter has been
149 locked for of Void Pymt and Run in
150 case of Reversal.
151 01-JUL-1999 ahanda 110.0 Created.
152 ****************************************************************************/
153 AS
154
155 gv_package VARCHAR2(100);
156
157 -- Added for Testing
158 /*****************************************************************************
159 ** Name: FUNCTION check_emp_personal_payment
160 ** Arguments: p_assignment_id => Assignemnt ID
161 ** p_payroll_id => Payroll ID
162 ** p_time_period_id => Time Period ID
163 ** p_assignment_action_id => See below for details
164 ** p_effective_date => Payment Date
165 ** p_payment_category => Payment Category
166 ** p_legislation_code => Territory_code
167 ** Description: Overloaded function with the parameter for time_period_id
168 **
169 ** Function to find out if all the personal payment methods
170 ** for the employee have been processed.
171 **
172 ** The function returns 'Y' if the Payroll has been processed
173 ** completely i.e.
174 ** Payroll Run -> Quick Pay Pre-Payment -> Check Writer/BACS
175 ** Quick Payment -> Pre-Payment -> Nacha/ Ext. Manual Payment
176 **
177 ** If the Payroll has been revered the function returns 'N'
178 **
179 ** Assignment_action_id passed to it can be the action for
180 ** archive process or payroll run. Both both action, we get
181 ** the prepayment_action_id and use it to check payment methods.
182 **
183 *****************************************************************************/
184 FUNCTION check_emp_personal_payment(
185 p_assignment_id number
186 ,p_payroll_id number
187 ,p_time_period_id number
188 ,p_assignment_action_id number
189 ,p_effective_date date
190 ,p_payment_category varchar2
191 ,p_legislation_code varchar2
192 )
193 RETURN VARCHAR2 IS
194
195 /* Cursor to get Payslip offset date for a payroll */
196 cursor c_view_offset(cp_time_period_id in number) is
197 select payslip_view_date
198 from per_time_periods ptp
199 where time_period_id = cp_time_period_id;
200
201 /* Cursor to get the how employee is paid */
202 cursor c_pre_payment_method
203 (cp_assignment_action_id number
204 ,cp_payment_category varchar2
205 ,cp_legislation_code varchar2) is
206 select ppp.pre_payment_id
207 from pay_payment_types ppt,
208 pay_org_payment_methods_f popm,
209 pay_pre_payments ppp
210 where ppp.assignment_action_id = cp_assignment_action_id
211 and popm.org_payment_method_id = ppp.org_payment_method_id
212 and popm.defined_balance_id is not null
213 and ppt.payment_type_id = popm.payment_type_id
214 and ppt.category = cp_payment_category
215 and ppt.territory_code = cp_legislation_code;
216
217
218 cursor c_check_for_reversal(cp_assignment_action_id in number) is
219 select 1
220 from pay_action_interlocks pai_pre
221 where pai_pre.locking_action_id = cp_assignment_action_id
222 and exists (
223 select 1
224 from pay_payroll_actions ppa,
225 pay_assignment_actions paa,
226 pay_action_interlocks pai_run
227 /* Get the run assignment action id locked by pre-payment */
228 where pai_run.locked_action_id = pai_pre.locked_action_id
229 /* Check if the Run is being locked by Reversal */
230 and pai_run.locking_action_id = paa.assignment_action_id
231 and ppa.payroll_action_id = paa.payroll_action_id
232 and paa.action_status = 'C'
233 and ppa.action_type = 'V');
234
235 /****************************************************************
236 ** If archiver is locking the pre-payment assignment_action_id,
237 ** we get it from interlocks and use it to check if all payments
238 ** have been made fro the employee.
239 ****************************************************************/
240 cursor c_prepay_arch_action(cp_assignment_action_id in number) is
241 select paa.assignment_action_id
242 from pay_action_interlocks paci,
243 pay_assignment_actions paa,
244 pay_payroll_actions ppa
245 where paci.locking_action_id = cp_assignment_action_id
246 and paa.assignment_action_id = paci.locked_action_id
247 and ppa.payroll_action_id = paa.payroll_action_id
248 and ppa.action_type in ('P', 'U');
249
250 /****************************************************************
251 ** If archiver is locking the run assignment_action_id, we get
252 ** the corresponding run assignment_action_id and then get
253 ** the pre-payment assignemnt_action_id.
254 ** This cursor is only required when there are child action which
255 ** means there is a seperate check.
256 * ***************************************************************/
257 cursor c_prepay_run_arch_action(cp_assignment_action_id in number) is
258 select paa_pre.assignment_action_id
259 from pay_action_interlocks pai_run,
260 pay_action_interlocks pai_pre,
261 pay_assignment_actions paa_pre,
262 pay_payroll_actions ppa_pre
263 where pai_run.locking_action_id = cp_assignment_action_id
264 and pai_pre.locked_action_id = pai_run.locked_action_id
265 and paa_pre.assignment_Action_id = pai_pre.locking_action_id
266 and ppa_pre.payroll_action_id = paa_pre.payroll_action_id
267 and ppa_pre.action_type in ('P', 'U');
268
269 cursor c_get_date_earned(cp_assignment_action_id in number) is
270 select nvl(max(ppa.date_earned), max(ppa.effective_date))
271 from pay_payroll_actions ppa
272 ,pay_assignment_actions paa
273 ,pay_action_interlocks pai
274 where ppa.payroll_action_id = paa.payroll_action_id
275 and pai.locked_action_id = paa.assignment_action_id
276 and pai.locking_action_id = cp_assignment_action_id
277 and ppa.action_type in ('R', 'Q', 'B', 'V');
278
279 cursor c_time_period(cp_payroll_id in number
280 ,cp_date_earned in date) is
281 select ptp.time_period_id
282 from per_time_periods ptp
283 where cp_date_earned between ptp.start_date
284 and ptp.end_Date
285 and ptp.payroll_id = cp_payroll_id;
286
287 cursor c_no_prepayments (cp_prepayment_action_id in number) is
288 select 1
289 from dual
290 where not exists
291 (select 1
292 from pay_pre_payments ppp
293 where ppp.assignment_action_id = cp_prepayment_action_id
294 );
295
296 lv_reversal_exists VARCHAR2(1);
297 ln_prepay_action_id NUMBER;
298 ln_pre_payment_id NUMBER;
299 lv_payment_status VARCHAR2(50);
300 lv_paid_lookup_meaning VARCHAR2(10);
301 lv_return_flag VARCHAR2(1);
302 lc_no_prepayment_flag VARCHAR2(1);
303
304 ld_view_payslip_offset_date DATE;
305 ld_earned_date DATE;
306 ln_time_period_id NUMBER;
307
308 BEGIN
309
310 hr_utility.trace('Entering check_emp_personal_payment');
311 hr_utility.trace('p_effective_date='||p_effective_date);
312 hr_utility.trace('p_time_period_id='||p_time_period_id);
313
314 IF p_payment_category IS NOT NULL THEN
315
316 lv_return_flag := 'Y';
317 open c_prepay_arch_action(p_assignment_action_id);
318 fetch c_prepay_arch_action into ln_prepay_action_id;
319 if c_prepay_arch_action%notfound then
320 open c_prepay_run_arch_action(p_assignment_action_id);
321 fetch c_prepay_run_arch_action into ln_prepay_action_id;
322 if c_prepay_run_arch_action%notfound then
323 return('N');
324 end if;
325 close c_prepay_run_arch_action;
326 end if;
327 close c_prepay_arch_action;
328
329 ln_time_period_id := p_time_period_id;
330 if ln_time_period_id is null then
331 open c_get_date_earned(ln_prepay_action_id);
332 fetch c_get_date_earned into ld_earned_date;
333 if c_get_date_earned%found then
334 open c_time_period(p_payroll_id, ld_earned_date);
335 fetch c_time_period into ln_time_period_id;
336 close c_time_period;
337 end if;
338 close c_get_date_earned;
339 end if;
340
341 hr_utility.trace('ln_time_period_id='||ln_time_period_id);
342 open c_view_offset(ln_time_period_id);
343 fetch c_view_offset into ld_view_payslip_offset_date;
344 close c_view_offset;
345 hr_utility.trace('ld_view_payslip_offset_date='||trunc(ld_view_payslip_offset_date));
346 hr_utility.trace('p_effective_date='||trunc(p_effective_date));
347 hr_utility.trace('sysdate='||trunc(sysdate));
348
349 /* check if the Payslip view date is populated. If it is, check the value
350 and make sure it is > sysdate otherwise don't show payslip */
351 if ld_view_payslip_offset_date is not null and
352 trunc(ld_view_payslip_offset_date) > trunc(sysdate) then
353 hr_utility.trace('View offset return N');
354 return('N');
355 end if;
356
357 open c_check_for_reversal(ln_prepay_action_id);
358 fetch c_check_for_reversal into lv_reversal_exists;
359 if c_check_for_reversal%found then
360 lv_return_flag := 'N';
361 else
362 open c_pre_payment_method (ln_prepay_action_id
363 ,p_payment_category
364 ,p_legislation_code);
365 loop
366 /* fetch all the pre payment records for the asssignment
367 other than 3rd party payment */
368 fetch c_pre_payment_method into ln_pre_payment_id;
369
370 if c_pre_payment_method%notfound then
371 exit;
372 end if;
373
374 lv_payment_status := ltrim(rtrim(
375 pay_assignment_actions_pkg.get_payment_status_code
376 (ln_prepay_action_id,
377 ln_pre_payment_id)));
378
379 if lv_payment_status <> 'P' then
380 lv_return_flag := 'N';
381 exit;
382 else
383 lv_return_flag := 'Y';
384 end if;
385
386 end loop;
387 IF lv_payment_status IS NULL THEN
388 lv_return_flag := 'N' ;
389 END IF ;
390 close c_pre_payment_method;
391
392 end if;
393 close c_check_for_reversal;
394
395 IF p_payment_category = 'MT' and p_legislation_code = 'US' THEN
396 OPEN c_no_prepayments(ln_prepay_action_id);
397 FETCH c_no_prepayments INTO lc_no_prepayment_flag;
398 IF c_no_prepayments%found THEN
399 lv_return_flag := 'Y';
400 END IF;
401 CLOSE c_no_prepayments;
402 END IF;
403
404 hr_utility.trace('lv_return_flag='||lv_return_flag);
405 hr_utility.trace('Leaving check_emp_personal_payment');
406
407 return lv_return_flag;
408 ELSE
409 return(check_emp_personal_payment(
410 p_assignment_id => p_assignment_id
411 ,p_payroll_id => p_payroll_id
412 ,p_time_period_id => p_time_period_id
413 ,p_assignment_action_id => p_assignment_action_id
414 ,p_effective_date => p_effective_date));
415 END IF;
416 END check_emp_personal_payment;
417
418 /*****************************************************************************
419 ** Name: FUNCTION check_emp_personal_payment
420 ** Arguments: p_assignment_id => Assignemnt ID
421 ** p_payroll_id => Payroll ID
422 ** p_time_period_id => Time Period ID
423 ** p_assignment_action_id => See below for details
424 ** p_effective_date => Payment Date
425 ** Description: Overloaded function with the parameter for time_period_id
426 **
427 ** Function to find out if all the personal payment methods
428 ** for the employee have been processed.
429 **
430 ** The function returns 'Y' if the Payroll has been processed
431 ** completely i.e.
432 ** Payroll Run -> Quick Pay Pre-Payment -> Check Writer/BACS
433 ** Quick Payment -> Pre-Payment -> Nacha/ Ext. Manual Payment
434 **
435 ** If the Payroll has been revered the function returns 'N'
436 **
437 ** Assignment_action_id passed to it can be the action for
438 ** archive process or payroll run. Both both action, we get
439 ** the prepayment_action_id and use it to check payment methods.
440 **
441 *****************************************************************************/
442 FUNCTION check_emp_personal_payment(
443 p_assignment_id number
444 ,p_payroll_id number
445 ,p_time_period_id number
446 ,p_assignment_action_id number
447 ,p_effective_date date
448 )
449 RETURN VARCHAR2 IS
450
451 /* Cursor to get Payslip offset date for a payroll */
452 cursor c_view_offset(cp_time_period_id in number) is
453 select payslip_view_date
454 from per_time_periods ptp
455 where time_period_id = cp_time_period_id;
456
457 /* Cursor to get the how employee is paid */
458 cursor c_pre_payment_method
459 (cp_assignment_action_id number) is
460 select ppp.pre_payment_id
461 from pay_payment_types ppt,
462 pay_org_payment_methods_f popm,
463 pay_pre_payments ppp
464 where ppp.assignment_action_id = cp_assignment_action_id
465 and popm.org_payment_method_id = ppp.org_payment_method_id
466 and popm.defined_balance_id is not null
467 and ppt.payment_type_id = popm.payment_type_id;
468 --bug 6840881 starts here
469 /*
470 cursor c_check_for_reversal(cp_assignment_action_id in number) is
471 select 1
472 from pay_action_interlocks pai_pre
473 where pai_pre.locking_action_id = cp_assignment_action_id
474 and exists (
475 select 1
476 from pay_payroll_actions ppa,
477 pay_assignment_actions paa,
478 pay_action_interlocks pai_run
479 where pai_run.locked_action_id = pai_pre.locked_action_id
480 and pai_run.locking_action_id = paa.assignment_action_id
481 and ppa.payroll_action_id = paa.payroll_action_id
482 and paa.action_status = 'C'
483 and ppa.action_type = 'V');
484 */
485 cursor c_check_for_reversal(cp_assignment_action_id in number) is
486 select 1
487 from pay_payroll_actions ppa,
488 pay_assignment_actions paa,
489 pay_action_interlocks pai_run
490 where pai_run.locked_action_id = cp_assignment_action_id
491 and pai_run.locking_action_id = paa.assignment_action_id
492 and ppa.payroll_action_id = paa.payroll_action_id
493 and paa.action_status = 'C'
494 and ppa.action_type = 'V';
495 --bug 6840881 ends here
496 /****************************************************************
497 ** If archiver is locking the pre-payment assignment_action_id,
498 ** we get it from interlocks and use it to check if all payments
499 ** have been made fro the employee.
500 ****************************************************************/
501 cursor c_prepay_arch_action(cp_assignment_action_id in number) is
502 select paa.assignment_action_id
503 from pay_action_interlocks paci,
504 pay_assignment_actions paa,
505 pay_payroll_actions ppa
506 where paci.locking_action_id = cp_assignment_action_id
507 and paa.assignment_action_id = paci.locked_action_id
508 and ppa.payroll_action_id = paa.payroll_action_id
509 and ppa.action_type in ('P', 'U');
510
511 /****************************************************************
512 ** If archiver is locking the run assignment_action_id, we get
513 ** the corresponding run assignment_action_id and then get
514 ** the pre-payment assignemnt_action_id.
515 ** This cursor is only required when there are child action which
516 ** means there is a seperate check.
517 * ***************************************************************/
518 cursor c_prepay_run_arch_action(cp_assignment_action_id in number) is
519 select paa_pre.assignment_action_id
520 from pay_action_interlocks pai_run,
521 pay_action_interlocks pai_pre,
522 pay_assignment_actions paa_pre,
523 pay_payroll_actions ppa_pre
524 where pai_run.locking_action_id = cp_assignment_action_id
525 and pai_pre.locked_action_id = pai_run.locked_action_id
526 and paa_pre.assignment_Action_id = pai_pre.locking_action_id
527 and ppa_pre.payroll_action_id = paa_pre.payroll_action_id
528 and ppa_pre.action_type in ('P', 'U');
529
530 cursor c_get_date_earned(cp_assignment_action_id in number) is
531 select nvl(max(ppa.date_earned), max(ppa.effective_date))
532 from pay_payroll_actions ppa
533 ,pay_assignment_actions paa
534 ,pay_action_interlocks pai
535 where ppa.payroll_action_id = paa.payroll_action_id
536 and pai.locked_action_id = paa.assignment_action_id
537 and pai.locking_action_id = cp_assignment_action_id
538 and ppa.action_type in ('R', 'Q', 'B', 'V');
539
540 cursor c_time_period(cp_payroll_id in number
541 ,cp_date_earned in date) is
542 select ptp.time_period_id
543 from per_time_periods ptp
544 where cp_date_earned between ptp.start_date
545 and ptp.end_Date
546 and ptp.payroll_id = cp_payroll_id;
547 --bug 6840881 starts here
548 cursor get_sprt_pymnt_dtls(p_assignment_action in number) is
549 select to_number(substr(SERIAL_NUMBER,3)) sprt_pymnt_actn_id,
550 substr(SERIAL_NUMBER,2,1) sprt_pymnt_flag
551 from pay_assignment_actions where assignment_action_id=p_assignment_action;
552
553 cursor get_regular_action(p_action_id in number,xfr_action_id in number)is
554 select locked_action_id from
555 pay_action_interlocks pai ,pay_assignment_actions paa
556 where pai.locking_action_id = p_action_id
557 and pai.locked_action_id not in (
558 select to_number(substr(SERIAL_NUMBER,3)) from pay_assignment_actions
559 where payroll_action_id in (select distinct payroll_action_id
560 from pay_assignment_actions where assignment_action_id = xfr_action_id)
561 and substr(SERIAL_NUMBER,2,1) = 'Y') and
562 pai.locked_action_id=paa.assignment_action_id
563 and source_action_id is not null;
564
565 lv_sprt_pymnt_actn_id pay_assignment_actions.assignment_action_id%type;
566 lv_sprt_pymnt_flag varchar(1);
567 lv_action_chkng_rvrsl pay_assignment_actions.assignment_action_id%type;
568 --bug 6840881 ends here
569
570 lv_reversal_exists VARCHAR2(1);
571 ln_prepay_action_id NUMBER;
572 ln_pre_payment_id NUMBER;
573 lv_payment_status VARCHAR2(50);
574 lv_paid_lookup_meaning VARCHAR2(10);
575 lv_return_flag VARCHAR2(1);
576
577 ld_view_payslip_offset_date DATE;
578 ld_earned_date DATE;
579 ln_time_period_id NUMBER;
580
581 BEGIN
582
583 hr_utility.trace('Entering check_emp_personal_payment');
584 hr_utility.trace('p_effective_date='||p_effective_date);
585 hr_utility.trace('p_time_period_id='||p_time_period_id);
586
587 lv_return_flag := 'Y';
588 open c_prepay_arch_action(p_assignment_action_id);
589 fetch c_prepay_arch_action into ln_prepay_action_id;
590 if c_prepay_arch_action%notfound then
591 open c_prepay_run_arch_action(p_assignment_action_id);
592 fetch c_prepay_run_arch_action into ln_prepay_action_id;
593 if c_prepay_run_arch_action%notfound then
594 return('N');
595 end if;
596 close c_prepay_run_arch_action;
597 end if;
598 close c_prepay_arch_action;
599
600 ln_time_period_id := p_time_period_id;
601 if ln_time_period_id is null then
602 open c_get_date_earned(ln_prepay_action_id);
603 fetch c_get_date_earned into ld_earned_date;
604 if c_get_date_earned%found then
605 open c_time_period(p_payroll_id, ld_earned_date);
606 fetch c_time_period into ln_time_period_id;
607 close c_time_period;
608 end if;
609 close c_get_date_earned;
610 end if;
611
612 hr_utility.trace('ln_time_period_id='||ln_time_period_id);
613 open c_view_offset(ln_time_period_id);
614 fetch c_view_offset into ld_view_payslip_offset_date;
615 close c_view_offset;
616 hr_utility.trace('ld_view_payslip_offset_date='||trunc(ld_view_payslip_offset_date));
617 hr_utility.trace('p_effective_date='||trunc(p_effective_date));
618 hr_utility.trace('sysdate='||trunc(sysdate));
619
620 /* check if the Payslip view date is populated. If it is, check the value
621 and make sure it is > sysdate otherwise don't show payslip */
622 if ld_view_payslip_offset_date is not null and
623 trunc(ld_view_payslip_offset_date) > trunc(sysdate) then
624 hr_utility.trace('View offset return N');
625 return('N');
626 end if;
627 --bug 6840881 starts here
628 open get_sprt_pymnt_dtls(p_assignment_action_id);
629 fetch get_sprt_pymnt_dtls into lv_sprt_pymnt_actn_id,lv_sprt_pymnt_flag;
630 if(lv_sprt_pymnt_flag= 'Y') then
631 lv_action_chkng_rvrsl:=lv_sprt_pymnt_actn_id;
632 hr_utility.trace('lv_action_chkng_rvrsl Y'||lv_action_chkng_rvrsl);
633 else
634 open get_regular_action(lv_sprt_pymnt_actn_id,p_assignment_action_id);
635 fetch get_regular_action into lv_action_chkng_rvrsl;
636 hr_utility.trace('lv_action_chkng_rvrsl NY'||lv_action_chkng_rvrsl);
637 close get_regular_action;
638 end if;
639 hr_utility.trace('lv_action_chkng_rvrsl' ||lv_action_chkng_rvrsl);
640 close get_sprt_pymnt_dtls;
641 -- open c_check_for_reversal(ln_prepay_action_id);
642 open c_check_for_reversal(lv_action_chkng_rvrsl);
643 --bug 6840881 ends here
644 fetch c_check_for_reversal into lv_reversal_exists;
645 if c_check_for_reversal%found then
646 lv_return_flag := 'N';
647 else
648 open c_pre_payment_method (ln_prepay_action_id);
649 loop
650 /* fetch all the pre payment records for the asssignment
651 other than 3rd party payment */
652 fetch c_pre_payment_method into ln_pre_payment_id;
653
654 if c_pre_payment_method%notfound then
655 exit;
656 end if;
657
658 lv_payment_status := ltrim(rtrim(
659 pay_assignment_actions_pkg.get_payment_status_code
660 (ln_prepay_action_id,
661 ln_pre_payment_id)));
662
663 if lv_payment_status <> 'P' then
664 lv_return_flag := 'N';
665 exit;
666 else
667 lv_return_flag := 'Y';
668 end if;
669
670 end loop;
671 close c_pre_payment_method;
672
673 end if;
674 close c_check_for_reversal;
675
676 hr_utility.trace('lv_return_flag='||lv_return_flag);
677 hr_utility.trace('Leaving check_emp_personal_payment');
678
679 return lv_return_flag;
680
681 END check_emp_personal_payment;
682
683 /*****************************************************************************
684 ** Name: FUNCTION check_emp_personal_payment
685 ** Arguments: p_assignment_id => Assignemnt ID
686 ** p_payroll_id => Payroll ID
687 ** p_assignment_action_id => Prepayment Action
688 ** p_effective_date => Payment Date
689 ** Description: Overloaded function without the parameter for
690 ** time_period_id.
691 **
692 ** As time_period_id is not passed, the function expects the
693 ** prepayment action_id to be passed to it. It then gets the
694 ** max date_earned for the run locked by the prepayment process
695 ** and then gets the corresponding time_period_id
696 ** This is passed to the overloaded function which checks for
697 ** Payslip view date
698 ** If prepayment action is not passed but instead archive or
699 ** run action is passed, the main function will handle it
700 *****************************************************************************/
701 FUNCTION check_emp_personal_payment(
702 p_assignment_id number
703 ,p_payroll_id number
704 ,p_assignment_action_id number
705 ,p_effective_date date
706 )
707 RETURN VARCHAR2 IS
708
709 cursor c_get_date_earned(cp_assignment_action_id in number) is
710 select nvl(max(ppa.date_earned), max(ppa.effective_date))
711 from pay_payroll_actions ppa
712 ,pay_assignment_actions paa
713 ,pay_action_interlocks pai
714 where ppa.payroll_action_id = paa.payroll_action_id
715 and pai.locked_action_id = paa.assignment_action_id
716 and pai.locking_action_id = cp_assignment_action_id
717 and ppa.action_type in ('R', 'Q', 'B', 'V');
718
719 cursor c_time_period(cp_payroll_id in number
720 ,cp_date_earned in date) is
721 select ptp.time_period_id
722 from per_time_periods ptp
723 where cp_date_earned between ptp.start_date
724 and ptp.end_Date
725 and ptp.payroll_id = cp_payroll_id;
726
727 ld_earned_date DATE;
728 ln_time_period_id NUMBER;
729
730 BEGIN
731
732 hr_utility.trace('Entering check_emp_personal_payment without time period');
733 open c_get_date_earned(p_assignment_action_id);
734 fetch c_get_date_earned into ld_earned_date;
735 if c_get_date_earned%found then
736 open c_time_period(p_payroll_id, ld_earned_date);
737 fetch c_time_period into ln_time_period_id;
738 close c_time_period;
739 end if;
740 close c_get_date_earned;
741
742 hr_utility.trace('ln_time_period_id='||ln_time_period_id);
743 return(check_emp_personal_payment(
744 p_assignment_id => p_assignment_id
745 ,p_payroll_id => p_payroll_id
746 ,p_time_period_id => ln_time_period_id
747 ,p_assignment_action_id => p_assignment_action_id
748 ,p_effective_date => p_effective_date));
749
750 END check_emp_personal_payment;
751
752
753 /************************************************************
754 Function gets the proposed employee salary from
755 per_pay_proposals. If the Salary Proposal is not specified
756 then it checks the Salary Basis for the employee, find out
757 the element associated with the Salary Basis and get the
758 value from the run results for the given period.
759 If the element associated with the Salary Basis is Regular
760 wages, then we get the value for input value of 'Rate'
761 ************************************************************/
762 FUNCTION get_proposed_emp_salary (
763 p_assignment_id in number
764 ,p_pay_basis_id in number
765 ,p_pay_bases_name in varchar2
766 ,p_period_start_date in date
767 ,p_period_end_date in date
768 )
769 RETURN VARCHAR2 IS
770
771 cursor c_salary_proposal (cp_assignment_id in number,
772 cp_period_start_date in date,
773 cp_period_end_date in date) is
774 select ppp.proposed_salary_n
775 from per_pay_proposals ppp
776 where ppp.assignment_id = cp_assignment_id
777 and ppp.change_date =
778 (select max(change_date)
779 from per_pay_proposals ppp1
780 where ppp1.assignment_id = cp_assignment_id
781 and ppp1.approved = 'Y'
782 and ppp1.change_date <= cp_period_end_date);
783
784
785 cursor c_bases_element (cp_pay_basis_id in number,
786 cp_period_to_date in date) is
787 select piv.element_type_id, piv.input_value_id
788 from pay_input_values_f piv,
789 per_pay_bases ppb
790 where ppb.pay_basis_id = cp_pay_basis_id
791 and ppb.input_value_id = piv.input_value_id
792 and cp_period_to_date between piv.effective_start_date
793 and piv.effective_end_date;
794
795 cursor c_regular_salary (cp_input_value_id in number,
796 cp_assignment_id in number,
797 cp_period_to_date in date ) is
798 select prrv.result_value
799 from pay_run_results prr,
800 pay_run_result_values prrv,
801 pay_input_values_f piv,
802 pay_assignment_actions paa,
803 pay_payroll_actions ppa
804 where prr.element_type_id = piv.element_type_id
805 and prr.run_result_id = prrv.run_result_id
806 and prr.source_type = 'E'
807 and piv.input_value_id = prrv.input_value_id
808 and piv.input_value_id = cp_input_value_id
809 and ppa.effective_date between piv.effective_start_date
810 and piv.effective_end_date
811 and paa.assignment_action_id = prr.assignment_action_id
812 and paa.assignment_id = cp_assignment_id
813 and ppa.payroll_action_id = paa.payroll_action_id
814 and ppa.effective_date = cp_period_to_date;
815
816 cursor c_hourly_salary (cp_element_type_id in number,
817 cp_input_value_name in varchar2,
818 cp_assignment_id in number,
819 cp_period_to_date in date ) is
820 select prrv.result_value
821 from pay_run_results prr,
822 pay_run_result_values prrv,
823 pay_input_values_f piv,
824 pay_assignment_actions paa,
825 pay_payroll_actions ppa
826 where prr.element_type_id = piv.element_type_id
827 and prr.run_result_id = prrv.run_result_id
828 and prr.source_type = 'E'
829 and piv.input_value_id = prrv.input_value_id
830 and piv.element_type_id = cp_element_type_id
831 and piv.name = cp_input_value_name
832 and ppa.effective_date between piv.effective_start_date --Bug 3350023
833 and piv.effective_end_date
834 and paa.assignment_action_id = prr.assignment_action_id
835 and paa.assignment_id = cp_assignment_id
836 and ppa.payroll_action_id = paa.payroll_action_id
837 and ppa.effective_date = cp_period_to_date;
838
839 ln_element_type_id number;
840 ln_input_value_id number;
841 ln_proposed_salary number;
842
843 BEGIN
844
845 open c_salary_proposal(p_assignment_id,
846 p_period_start_date,
847 p_period_end_date);
848 fetch c_salary_proposal into ln_proposed_salary;
849 if c_salary_proposal%notfound then
850 open c_bases_element(p_pay_basis_id, p_period_end_date);
851 fetch c_bases_element into ln_element_type_id, ln_input_value_id;
852 if c_bases_element%found then
853 if p_pay_bases_name <> 'HOURLY' then
854 open c_regular_salary(ln_input_value_id,
855 p_assignment_id,
856 p_period_end_date);
857 fetch c_regular_salary into ln_proposed_salary;
858 if c_regular_salary%notfound then
859 ln_proposed_salary := 0;
860 end if;
861 close c_regular_salary;
862 else
863 open c_hourly_salary(ln_element_type_id,
864 'Rate',
865 p_assignment_id,
866 p_period_end_date);
867 fetch c_hourly_salary into ln_proposed_salary;
868 if c_hourly_salary%notfound then
869 ln_proposed_salary := 0;
870 end if;
871 close c_hourly_salary;
872 end if;
873 end if;
874 close c_bases_element;
875
876 end if;
877 close c_salary_proposal;
878
879 return (ln_proposed_salary);
880
881 END get_proposed_emp_salary;
882
883
884 /************************************************************
885 Gets the Annualized factor for the Payroll
886 i.e. frequency of the Payroll
887 e.g. Week = 52
888 Semi-Month = 24
889 Month = 12
890 Hourly = No of working hours/day * 365
891 No of working hours/week * 52
892 No of working hours/month * 12
893 No of working hours/year * 1
894 ************************************************************/
895 FUNCTION get_emp_annualization_factor (
896 p_pay_basis_id in number
897 ,p_period_type in varchar2
898 ,p_pay_bases_name in varchar2
899 ,p_assignment_id in number
900 ,p_period_end_date in date
901 )
902 return number is
903
904 cursor c_salary_details (cp_pay_basis_id in number) is
905 select ppb.pay_annualization_factor
906 from per_pay_bases ppb
907 where ppb.pay_basis_id = cp_pay_basis_id;
908
909 cursor c_payroll (cp_period_type in varchar2) is
910 select ptpt.number_per_fiscal_year
911 from per_time_period_types ptpt
912 where ptpt.period_type = cp_period_type;
913
914 ln_pay_annualization_factor number;
915
916 BEGIN
917
918 open c_salary_details(p_pay_basis_id);
919 fetch c_salary_details into ln_pay_annualization_factor;
920 if c_salary_details%found then
921
922 if p_pay_bases_name ='PERIOD' and
923 ln_pay_annualization_factor is null then
924
925 open c_payroll(p_period_type);
926 fetch c_payroll into ln_pay_annualization_factor;
927 close c_payroll;
928
929 elsif p_pay_bases_name = 'HOURLY' and
930 (p_assignment_id is not null and p_period_end_date is not null) then
931
932 ln_pay_annualization_factor :=
933 pay_us_employee_payslip_web.get_asgn_annual_hours
934 (p_assignment_id,
935 p_period_end_date);
936 end if;
937 end if;
938 close c_salary_details;
939
940 return (ln_pay_annualization_factor);
941
942 END get_emp_annualization_factor;
943
944
945 /************************************************************
946 The function gets the annual working hours for an assignment.
947 The function looks for Standarg Working Conditions for an
948 assignment. If is has not been specified then it gets the
949 information for the assignment in the following order.
950 Assignment
951 Position
952 Organization
953 Business Group
954 ************************************************************/
955 FUNCTION get_asgn_annual_hours (
956 p_assignment_id in number
957 ,p_period_end_date in date
958 )
959 RETURN NUMBER IS
960
961 cursor c_get_asg_hours (cp_assignment_id in number,
962 cp_period_end_date in date) is
963 select paf.normal_hours,
964 decode(paf.frequency,'Y', 1,
965 'M', 12,
966 'W', 52,
967 'D', 365, 1)
968 from per_assignments_f paf
969 where paf.assignment_id = cp_assignment_id
970 and cp_period_end_date between paf.effective_start_date
971 and paf.effective_end_date;
972
973 cursor c_get_pos_hours (cp_assignment_id in number,
974 cp_period_end_date in date) is
975 select pos.working_hours,
976 decode(pos.frequency, 'Y', 1,
977 'M', 12,
978 'W', 52,
979 'D', 365, 1)
980 from per_positions pos,
981 per_assignments_f paf
982 where paf.assignment_id = cp_assignment_id
983 and cp_period_end_date between paf.effective_start_date
984 and paf.effective_end_date
985 and paf.position_id = pos.position_id;
986
987 cursor c_get_org_hours (cp_assignment_id in number,
988 cp_period_end_date in date) is
989 select pou.working_hours,
990 decode(pou.frequency, 'Y', 1,
991 'M', 12,
992 'W', 52,
993 'D', 365, 1)
994 from per_organization_units pou,
995 per_assignments_f paf
996 where paf.assignment_id = cp_assignment_id
997 and cp_period_end_date between paf.effective_start_date
998 and paf.effective_end_date
999 and paf.organization_id = pou.organization_id;
1000
1001 cursor c_get_bus_hours (cp_assignment_id in number,
1002 cp_period_end_date in date) is
1003 select pbg.working_hours,
1004 decode(pbg.frequency, 'Y', 1,
1005 'M', 12,
1006 'W', 52,
1007 'D', 365, 1)
1008 from per_business_groups pbg,
1009 per_assignments_f paf
1010 where paf.assignment_id = cp_assignment_id
1011 and cp_period_end_date between paf.effective_start_date
1012 and paf.effective_end_date
1013 and paf.business_group_id = pbg.business_group_id;
1014
1015 ln_hours number;
1016 ln_frequency number;
1017 ln_hours_per_year number;
1018
1019 BEGIN
1020
1021 open c_get_asg_hours (p_assignment_id,
1022 p_period_end_date);
1023 fetch c_get_asg_hours into ln_hours,ln_frequency;
1024
1025 if c_get_asg_hours%found and ln_hours is not null then
1026 close c_get_asg_hours;
1027 else
1028 close c_get_asg_hours;
1029 open c_get_pos_hours (p_assignment_id,
1030 p_period_end_date);
1031 fetch c_get_pos_hours into ln_hours, ln_frequency;
1032
1033 if c_get_pos_hours%found and ln_hours is not null then
1034 close c_get_pos_hours;
1035 else
1036 close c_get_pos_hours;
1037 open c_get_org_hours (p_assignment_id,
1038 p_period_end_date);
1039 fetch c_get_org_hours into ln_hours, ln_frequency;
1040
1041 if c_get_org_hours%found and ln_hours is not null then
1042 close c_get_org_hours;
1043 open c_get_bus_hours (p_assignment_id,
1044 p_period_end_date);
1045 fetch c_get_bus_hours into ln_hours, ln_frequency;
1046 close c_get_bus_hours;
1047 end if;
1048 end if;
1049
1050 end if;
1051
1052 ln_hours_per_year := nvl(ln_hours, 0) * ln_frequency;
1053
1054 return (ln_hours_per_year);
1055
1056 END get_asgn_annual_hours;
1057
1058 /************************************************************
1059 The function gets the School District Name for the passed
1060 Jurisdiction Code.
1061 The name is being reteived from the School Dsts table
1062 depending on the following :
1063 - get the School District Name from PAY_US_COUNTY_SCHOOL_DSTS.
1064 - If not found then get the School District Name from
1065 PAY_US_CITY_SCHOOL_DSTS.
1066 If the School Dsts Code passed is not in the table then
1067 NULL is passed.
1068 ************************************************************/
1069 Function get_school_dsts_name
1070 (p_jurisdiction_code in varchar2)
1071 RETURN varchar2 is
1072
1073 Cursor c_city_school_dsts
1074 (cp_jurisdiction_code in varchar2) is
1075 select initcap(pcisd.school_dst_name)
1076 from pay_us_city_school_dsts pcisd
1077 where pcisd.state_code = substr(cp_jurisdiction_code,1,2)
1078 and pcisd.school_dst_code = substr(cp_jurisdiction_code,4);
1079
1080 Cursor c_county_school_dsts
1081 (cp_jurisdiction_code in varchar2) is
1082 select initcap(pcosd.school_dst_name)
1083 from pay_us_county_school_dsts pcosd
1084 where pcosd.state_code = substr(cp_jurisdiction_code,1,2)
1085 and pcosd.school_dst_code = substr(cp_jurisdiction_code,4);
1086
1087 lv_school_dst_name varchar2(100);
1088
1089 BEGIN
1090
1091 open c_county_school_dsts (p_jurisdiction_code);
1092 fetch c_county_school_dsts into lv_school_dst_name;
1093 if c_county_school_dsts%notfound then
1094 open c_city_school_dsts (p_jurisdiction_code);
1095 fetch c_city_school_dsts into lv_school_dst_name;
1096 close c_city_school_dsts;
1097 end if;
1098 close c_county_school_dsts;
1099
1100 return (lv_school_dst_name);
1101
1102 END get_school_dsts_name;
1103
1104
1105 /************************************************************
1106
1107 Name : get_check_number
1108 Purpose : This returns the check number
1109 Arguments : Pre_payment_id and pre_payment assignment_action.
1110 Notes :
1111 *****************************************************************/
1112 FUNCTION get_check_number(p_pre_payment_assact in number
1113 ,p_pre_payment_id in number)
1114 RETURN varchar2 is
1115
1116 lv_check_number varchar2(60);
1117
1118 Cursor c_check_number(cp_pre_payment_action in number
1119 ,cp_pre_payment_id in number) is
1120 select decode(ppa_pymt.action_type,
1121 'M', to_char(NVL(ppp.source_action_id,cp_pre_payment_action)),
1122 paa_pymt.serial_number)
1123 from pay_pre_payments ppp,
1124 pay_assignment_actions paa_pymt,
1125 pay_payroll_actions ppa_pymt,
1126 pay_action_interlocks pai
1127 where pai.locked_action_id = cp_pre_payment_action
1128 and paa_pymt.assignment_action_id = pai.locking_action_id
1129 and ppa_pymt.payroll_action_id = paa_pymt.payroll_action_id
1130 and ppa_pymt.action_type in ('M','H', 'E')
1131 and paa_pymt.pre_payment_id = cp_pre_payment_id
1132 and ppp.pre_payment_id = paa_pymt.pre_payment_id
1133 and not exists (
1134 select 1
1135 from pay_payroll_actions ppa,
1136 pay_assignment_actions paa,
1137 pay_action_interlocks pai_void
1138 /* Assignment Action of Payment Type - NACHA/Check */
1139 where pai_void.locked_action_id = paa_pymt.assignment_action_id --Void
1140 /* Check if the locking is that of Void Pymt */
1141 and pai_void.locking_action_id = paa.assignment_action_id
1142 and ppa.payroll_action_id = paa.payroll_action_id
1143 and paa.action_status = 'C'
1144 and ppa.action_status = 'C'
1145 and ppa.action_type = 'D');
1146
1147 BEGIN
1148
1149 open c_check_number(p_pre_payment_assact, p_pre_payment_id);
1150 fetch c_check_number into lv_check_number;
1151 if c_check_number%notfound then
1152 lv_check_number := null;
1153 end if;
1154 close c_check_number;
1155
1156 RETURN lv_check_number;
1157
1158 END get_check_number;
1159
1160 /************************************************************
1161 Name : get_format_value
1162 purpuse : given a value, it formats the value to a given
1163 currency_code and precision.
1164 arguments : p_business_group_id, p_value
1165 notes :
1166 *************************************************************/
1167 FUNCTION get_format_value(p_business_group_id in number,
1168 p_value in number)
1169 RETURN varchar2 IS
1170
1171 lv_formatted_number varchar2(50);
1172
1173 CURSOR c_currency_code is
1174 select hoi.org_information10
1175 from hr_organization_units hou,
1176 hr_organization_information hoi
1177 where hou.organization_id = p_business_group_id /* Bug 3487250 */
1178 and hou.organization_id = hoi.organization_id
1179 and hoi.org_information_context = 'Business Group Information';
1180
1181 BEGIN
1182 IF g_currency_code is null THEN
1183 OPEN c_currency_code;
1184 FETCH c_currency_code into g_currency_code;
1185 CLOSE c_currency_code;
1186 END IF;
1187 IF g_currency_code is not null THEN
1188 lv_formatted_number := to_char(p_value,
1189 fnd_currency.get_format_mask(
1190 g_currency_code,40));
1191 ELSE
1192 lv_formatted_number := p_value;
1193 END IF;
1194
1195 return lv_formatted_number;
1196
1197 EXCEPTION
1198 when others then
1199 return p_value;
1200 END get_format_value;
1201
1202 /************************************************************
1203 Name : format_to_date
1204 Purpuse : The function formats the value in date format
1205 Arguments : p_value
1206 Notes :
1207 *************************************************************/
1208 FUNCTION format_to_date(p_char_date in varchar2)
1209 RETURN date IS
1210
1211 ld_return_date DATE;
1212
1213 BEGIN
1214 if length(p_char_date) = 19 then
1215 ld_return_date := fnd_date.canonical_to_date(p_char_date);
1216 else
1217 begin
1218 ld_return_date := fnd_date.chardate_to_date(p_char_date);
1219
1220 exception
1221 when others then
1222 ld_return_date := null;
1223 end;
1224
1225 end if;
1226
1227 return(ld_return_date);
1228
1229 END format_to_date;
1230
1231 /************************************************************
1232 Name : get_doc_eit
1233 Purpuse : returns whether any documents should be printed
1234 or viewed online.
1235 Arguments : p_doc_type = (i.e PAYSLIP, W4...)
1236 p_mode = PRINT or ONLINE
1237 p_level = PERSON, ORGANIZATION, BUSINESS GROUP,
1238 LOCATION
1239 p_id = appropriate id for p_level.
1240 person_id, organization_id, business_group_id,
1241 location_id
1242 Notes : Priority for levels (high to low)
1243 Person
1244 Location
1245 Organization
1246 Business Group
1247 *************************************************************/
1248 FUNCTION get_doc_eit(p_doc_type in varchar,
1249 p_mode in varchar,
1250 p_level in varchar,
1251 p_id in number,
1252 p_effective_date date)
1253 RETURN varchar2 IS
1254
1255 CURSOR get_person_eit (l_person_id Number) IS
1256 select pei_information2, pei_information3
1257 from per_people_extra_info
1258 where information_type = 'HR_SELF_SERVICE_PER_PREFERENCE'
1259 and person_id = l_person_id
1260 and pei_information1 = upper(p_doc_type);
1261
1262 CURSOR get_loc_eit (l_location_id number) IS
1263 select lei_information2, lei_information3
1264 from hr_location_extra_info
1265 where information_type = 'HR_SELF_SERVICE_LOC_PREFERENCE'
1266 and location_id = l_location_id
1267 and lei_information1 = upper(p_doc_type);
1268
1269 CURSOR get_org_eit (l_organization_id number) IS
1270 select org_information2,org_information3
1271 from hr_organization_information
1272 where org_information_context = 'HR_SELF_SERVICE_ORG_PREFERENCE'
1273 and org_information1 = upper(p_doc_type)
1274 and organization_id = l_organization_id;
1275
1276 CURSOR get_bg_eit (l_business_group_id number) IS
1277 select org_information2, org_information3
1278 from hr_organization_information hoi
1279 where hoi.organization_id = l_business_group_id
1280 and hoi.org_information_context = 'HR_SELF_SERVICE_BG_PREFERENCE'
1281 and hoi.org_information1 = upper(p_doc_type) ;
1282
1283 /* adding a join to per_periods_of_service. If the employee is terminated then
1284 the person does not have access to online doc, so we should always return a
1285 N.
1286 */
1287
1288 CURSOR get_person_info(l_assignment_id number) IS
1289 select paf.business_group_id, paf.organization_id,
1290 paf.location_id, paf.person_id
1291 from per_assignments_f paf, per_periods_of_service pps
1292 where paf.assignment_id = l_assignment_id
1293 and p_effective_date between paf.effective_start_date
1294 and paf.effective_end_date
1295 and pps.period_of_service_id = paf.period_of_service_id
1296 and pps.actual_termination_date is null;
1297
1298 l_mesg varchar2(250);
1299
1300 l_online varchar2(1);
1301 l_print varchar2(1);
1302
1303 l_value varchar2(1);
1304 l_count number;
1305 l_rowcount number;
1306 l_bg_id number;
1307 l_org_id number;
1308 l_loc_id number;
1309 l_person_id number;
1310
1311 l_location_cache varchar2(10);
1312 l_org_cache varchar2(10);
1313 l_bg_cache varchar2(10);
1314
1315 BEGIN
1316 l_mesg := 'pay_us_employee_payslip_web.get_doc_eit';
1317 hr_utility.set_location(l_mesg,5);
1318 l_location_cache := 'NOT FOUND';
1319 l_org_cache := 'NOT FOUND';
1320 l_bg_cache := 'NOT FOUND';
1321 l_rowCount := pay_us_employee_payslip_web.eit_tab.count;
1322
1323 IF upper(p_level) = 'ASSIGNMENT' THEN
1324 hr_utility.set_location(l_mesg, 10);
1325 OPEN get_person_info(p_id);
1326 FETCH get_person_info INTO l_bg_id, l_org_id, l_loc_id, l_person_id;
1327 CLOSE get_person_info;
1328
1329 OPEN get_person_eit(l_person_id);
1330 FETCH get_person_eit INTO l_online, l_print;
1331
1332 IF get_person_eit%FOUND THEN
1333 If p_mode = 'PRINT' THEN
1334 l_value := l_print;
1335 ELSIF p_mode = 'ONLINE' THEN
1336 l_value := l_online;
1337 END IF;
1338 ELSE /* Person Level EIT not found, look for location level */
1339 OPEN get_loc_eit(l_loc_id);
1340 FETCH get_loc_eit INTO l_online, l_print;
1341
1342 IF get_loc_eit%FOUND THEN
1343 IF p_mode = 'ONLINE' THEN
1344 l_value := l_online;
1345 ELSIF p_mode = 'PRINT' THEN
1346 l_value := l_print;
1347 END IF;
1348 ELSE /* Location Level EIT not found */
1349 OPEN get_org_eit(l_org_id);
1350 FETCH get_org_eit into l_online, l_print;
1351 IF get_org_eit%FOUND THEN
1352 IF p_mode = 'ONLINE' THEN
1353 l_value := l_online;
1354 ELSIF p_mode = 'PRINT' THEN
1355 l_value := l_print;
1356 END IF;
1357 ELSE /* Organization Level not found */
1358 OPEN get_bg_eit(l_bg_id);
1359 FETCH get_bg_eit into l_online,l_print;
1360 IF get_bg_eit%FOUND THEN
1361 IF p_mode = 'ONLINE' THEN
1362 l_value := l_online;
1363 ELSIF p_mode = 'PRINT' THEN
1364 l_value := l_print;
1365 END IF;
1366 ELSE
1367 l_value := 'Y';
1368 END IF; /* Bg not found */
1369 CLOSE get_bg_eit;
1370 END IF; /* Org not found */
1371 CLOSE get_org_eit;
1372 END IF; /* Loc not found */
1373 CLOSE get_loc_eit;
1374 END IF; /* Person not found */
1375 CLOSE get_person_eit;
1376
1377 return l_value;
1378
1379 END IF; /* p_level = assignment */
1380
1381 IF upper(p_level) = 'PERSON' THEN
1382 hr_utility.set_location(l_mesg,20);
1383 OPEN get_person_eit(p_id);
1384 FETCH get_person_eit INTO l_online, l_print;
1385
1386 IF get_person_eit%FOUND THEN
1387 If p_mode = 'PRINT' THEN
1388 l_value := l_print;
1389 ELSIF p_mode = 'ONLINE' THEN
1390 l_value := l_online;
1391 END IF;
1392 ELSE
1393 l_value := 'Y';
1394 END IF;
1395
1396 CLOSE get_person_eit;
1397 RETURN l_value;
1398 END IF;
1399
1400 IF upper(p_level) = 'LOCATION' THEN
1401 hr_utility.set_location(l_mesg,30);
1402 hr_utility.trace('Before LOOP l_location_cache = '||l_location_cache);
1403 IF (l_rowCount > 0) THEN
1404 FOR i in pay_us_employee_payslip_web.eit_tab.first ..
1405 pay_us_employee_payslip_web.eit_tab.last
1406 LOOP
1407 IF (pay_us_employee_payslip_web.eit_tab(i).t_id = p_id AND
1408 pay_us_employee_payslip_web.eit_tab(i).t_level = 'Location') THEN
1409
1410 l_location_cache := 'FOUND';
1411
1412 IF p_mode = 'ONLINE' THEN
1413 l_value := pay_us_employee_payslip_web.eit_tab(i).t_online;
1414 ELSIF p_mode = 'PRINT' THEN
1415 l_value := pay_us_employee_payslip_web.eit_tab(i).t_print;
1416 END IF;
1417 END IF;
1418 END LOOP;
1419 END IF; -- l_rowCount > 0
1420 hr_utility.trace('AFter LOOP l_location_cache = '||l_location_cache);
1421
1422 IF l_location_cache = 'NOT FOUND' THEN
1423 hr_utility.set_location(l_mesg,40);
1424 ---- Location Level is not cached so find it ----
1425
1426 OPEN get_loc_eit(p_id);
1427 FETCH get_loc_eit INTO l_online, l_print;
1428 hr_utility.trace('l_online = '||l_online);
1429 hr_utility.trace('l_print = '||l_print);
1430 l_count := pay_us_employee_payslip_web.eit_tab.count + 1 ;
1431 pay_us_employee_payslip_web.eit_tab(l_count).t_id := p_id;
1432 pay_us_employee_payslip_web.eit_tab(l_count).t_level := 'Location';
1433
1434 IF get_loc_eit%FOUND THEN
1435 pay_us_employee_payslip_web.eit_tab(l_count).t_online := l_online;
1436 pay_us_employee_payslip_web.eit_tab(l_count).t_print := l_print;
1437 ELSE
1438 pay_us_employee_payslip_web.eit_tab(l_count).t_online := 'Y';
1439 pay_us_employee_payslip_web.eit_tab(l_count).t_print := 'Y';
1440 END IF;
1441
1442 hr_utility.trace('eit_tab(l_count).t_online = '||
1443 pay_us_employee_payslip_web.eit_tab(l_count).t_online);
1444 hr_utility.trace('eit_tab(l_count).t_print = '||
1445 pay_us_employee_payslip_web.eit_tab(l_count).t_print);
1446 IF p_mode = 'ONLINE' THEN
1447 l_value := pay_us_employee_payslip_web.eit_tab(l_count).t_online;
1448 ELSIF p_mode = 'PRINT' THEN
1449 l_value := pay_us_employee_payslip_web.eit_tab(l_count).t_print;
1450 END IF;
1451 CLOSE get_loc_eit;
1452 END IF;
1453 return l_value;
1454 END IF; -- if p_level = Location
1455
1456 IF upper(p_level) = 'ORGANIZATION' THEN
1457 hr_utility.set_location(l_mesg,50);
1458 IF (l_rowCount > 0) THEN
1459 FOR i in pay_us_employee_payslip_web.eit_tab.first ..
1460 pay_us_employee_payslip_web.eit_tab.last
1461 LOOP
1462 IF (pay_us_employee_payslip_web.eit_tab(i).t_id = p_id AND
1463 pay_us_employee_payslip_web.eit_tab(i).t_level
1464 = 'Organization') THEN
1465 l_org_cache := 'FOUND';
1466 IF p_mode = 'ONLINE' THEN
1467 l_value := pay_us_employee_payslip_web.eit_tab(i).t_online;
1468 ELSIF p_mode = 'PRINT' THEN
1469 l_value := pay_us_employee_payslip_web.eit_tab(i).t_print;
1470 END IF;
1471 END IF;
1472 END LOOP;
1473 END IF;
1474
1475 ---- Organization Level is not cached so find it ----
1476
1477 IF l_org_cache = 'NOT FOUND' THEN
1478 hr_utility.trace('Org cache NOT FOUND');
1479 hr_utility.set_location(l_mesg,60);
1480 OPEN get_org_eit(p_id);
1481 FETCH get_org_eit INTO l_online, l_print;
1482 l_count := pay_us_employee_payslip_web.eit_tab.count + 1 ;
1483 pay_us_employee_payslip_web.eit_tab(l_count).t_id := p_id;
1484 pay_us_employee_payslip_web.eit_tab(l_count).t_level := 'Organization';
1485
1486 IF get_org_eit%FOUND THEN
1487 pay_us_employee_payslip_web.eit_tab(l_count).t_online := l_online;
1488 pay_us_employee_payslip_web.eit_tab(l_count).t_print := l_print;
1489 ELSE
1490 pay_us_employee_payslip_web.eit_tab(l_count).t_online := 'Y';
1491 pay_us_employee_payslip_web.eit_tab(l_count).t_print := 'Y';
1492 END IF;
1493
1494 IF p_mode = 'PRINT' THEN
1495 l_value := pay_us_employee_payslip_web.eit_tab(l_count).t_print;
1496 ELSIF p_mode = 'ONLINE' THEN
1497 l_value := pay_us_employee_payslip_web.eit_tab(l_count).t_online;
1498 END IF;
1499 CLOSE get_org_eit;
1500 END IF;
1501 return l_value;
1502 END IF; --if p_level = Organization
1503
1504 --- So look for Cached Business Group EIT ---
1505
1506 IF upper(p_level) = 'BUSINESS GROUP' THEN
1507 hr_utility.set_location(l_mesg,70);
1508 IF (l_rowCount > 0) THEN
1509 FOR i in pay_us_employee_payslip_web.eit_tab.first ..
1510 pay_us_employee_payslip_web.eit_tab.last LOOP
1511 IF (pay_us_employee_payslip_web.eit_tab(i).t_id = p_id AND
1512 pay_us_employee_payslip_web.eit_tab(i).t_level = 'Business Group')
1513 THEN
1514 l_bg_cache := 'FOUND';
1515 IF p_mode = 'ONLINE' THEN
1516 l_value := pay_us_employee_payslip_web.eit_tab(i).t_online;
1517 ELSIF p_mode = 'PRINT' THEN
1518 l_value := pay_us_employee_payslip_web.eit_tab(i).t_print;
1519 END IF;
1520 END IF;
1521 END LOOP;
1522 END IF;
1523
1524 --- business Group Level EIT not cached ----
1525
1526 IF l_bg_cache = 'NOT FOUND' THEN
1527 hr_utility.set_location(l_mesg,80);
1528
1529 OPEN get_bg_eit(p_id);
1530 FETCH get_bg_eit INTO l_online, l_print;
1531 l_count := pay_us_employee_payslip_web.eit_tab.count + 1;
1532 pay_us_employee_payslip_web.eit_tab(l_count).t_id := p_id;
1533 pay_us_employee_payslip_web.eit_tab(l_count).t_level := 'Business Group';
1534
1535 IF get_bg_eit%FOUND THEN
1536 pay_us_employee_payslip_web.eit_tab(l_count).t_online := l_online;
1537 pay_us_employee_payslip_web.eit_tab(l_count).t_print := l_print;
1538 ELSE
1539 pay_us_employee_payslip_web.eit_tab(l_count).t_online := 'Y';
1540 pay_us_employee_payslip_web.eit_tab(l_count).t_print := 'Y';
1541 END IF;
1542
1543 IF p_mode = 'ONLINE' THEN
1544 l_value := pay_us_employee_payslip_web.eit_tab(l_count).t_online;
1545 ELSIF p_mode = 'PRINT' THEN
1546 l_value := pay_us_employee_payslip_web.eit_tab(l_count).t_print;
1547 END IF;
1548
1549 CLOSE get_bg_eit;
1550 END IF; -- bg_cache not found
1551 return l_value;
1552 END IF; -- p_level = Business Group
1553
1554 END get_doc_eit;
1555
1556 /*********************************************************************
1557 Name : get_jurisdiction_name
1558 Purpose : This function returns the name of the jurisdiction
1559 If Jurisdiction_code is like 'XX-000-0000' then
1560 it returns State Name from py_us_states
1561 If Jurisdiction_code is like 'XX-XXX-0000' then
1562 it returns County Name from paY_us_counties
1563 If Jurisdiction_code is like 'XX-XXX-XXXX' then
1564 it returns City Name from pay_us_city_name
1565 If Jurisdiction_code is like 'XX-XXXXX' then
1566 it returns School Name from pay_us_school_dsts
1567 In case jurisdiction code could not be found relevent
1568 table then NULL is returned.
1569 Arguments : p_jurisdiction_code
1570 Notes :
1571 *********************************************************************/
1572 FUNCTION get_jurisdiction_name(p_jurisdiction_code in varchar2)
1573
1574 RETURN VARCHAR2
1575 IS
1576
1577 cursor c_get_state(cp_state_code in varchar2) is
1578 select state_abbrev
1579 from pay_us_states
1580 where state_code = cp_state_code;
1581
1582 cursor c_get_county( cp_state_code in varchar2
1583 ,cp_county_code in varchar2
1584 ) is
1585 select county_name
1586 from pay_us_counties
1587 where state_code = cp_state_code
1588 and county_code = cp_county_code;
1589
1590 cursor c_get_city( cp_state_code in varchar2
1591 ,cp_county_code in varchar2
1592 ,cp_city_code in varchar2
1593 ) is
1594 select city_name
1595 from pay_us_city_names
1596 where state_code = cp_state_code
1597 and county_code = cp_county_code
1598 and city_code = cp_city_code
1599 and primary_flag = 'Y';
1600
1601 lv_state_code VARCHAR2(2);
1602 lv_county_code VARCHAR2(3);
1603 lv_city_code VARCHAR2(4);
1604 lv_jurisdiction_name VARCHAR2(240);
1605
1606 lv_procedure_name VARCHAR2(50);
1607 BEGIN
1608 lv_procedure_name := '.get_jurisdiction_name' ;
1609 lv_state_code := substr(p_jurisdiction_code,1,2);
1610 lv_county_code := substr(p_jurisdiction_code,4,3);
1611 lv_city_code := substr(p_jurisdiction_code,8,4);
1612 lv_jurisdiction_name := null;
1613 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1614
1615 if p_jurisdiction_code like '__-000-0000' then
1616 open c_get_state(lv_state_code);
1617 fetch c_get_state into lv_jurisdiction_name;
1618 close c_get_state;
1619 elsif p_jurisdiction_code like '__-___-0000' then
1620 open c_get_county(lv_state_code
1621 ,lv_county_code);
1622 fetch c_get_county into lv_jurisdiction_name;
1623 close c_get_county;
1624 elsif p_jurisdiction_code like '__-___-____' then
1625 open c_get_city( lv_state_code
1626 ,lv_county_code
1627 ,lv_city_code);
1628 fetch c_get_city into lv_jurisdiction_name;
1629 close c_get_city;
1630 elsif p_jurisdiction_code like '__-_____' then
1631 -- this is school district make a function call
1632 lv_jurisdiction_name
1633 := pay_us_employee_payslip_web.get_school_dsts_name(p_jurisdiction_code);
1634 end if;
1635
1636 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1637 return (lv_jurisdiction_name);
1638 END get_jurisdiction_name;
1639
1640
1641 FUNCTION get_legislation_code( p_business_group_id in number)
1642 RETURN VARCHAR2 is
1643
1644 CURSOR cur_legislation_code is
1645 select
1646 org_information9
1647 from
1648 hr_organization_information
1649 where
1650 org_information_context = 'Business Group Information'
1651 and organization_id = p_business_group_id;
1652
1653 l_legislation_code hr_organization_information.org_information9%TYPE;
1654
1655 BEGIN
1656
1657 OPEN cur_legislation_code;
1658 FETCH cur_legislation_code
1659 INTO l_legislation_code;
1660
1661 IF cur_legislation_code%NOTFOUND THEN
1662 l_legislation_code := ' ';
1663 END IF;
1664
1665 CLOSE cur_legislation_code;
1666
1667 RETURN l_legislation_code;
1668
1669 END get_legislation_code;
1670
1671
1672 FUNCTION get_term_info (p_business_group_id number,
1673 p_person_id number,
1674 p_action_context_id number)
1675 /* for bug 4132132
1676 p_effective_start_date date,
1677 p_effective_end_date date) */
1678 RETURN varchar2 IS
1679
1680 CURSOR c_get_legislation_rule(p_legislation_code varchar2) is
1681 select rule_mode
1682 from pay_legislative_field_info plf
1683 WHERE validation_name = 'ITEM_PROPERTY'
1684 and rule_type = 'PAYSLIP_STOP_TERM_EMP'
1685 and field_name = 'CHOOSE_PAYSLIP'
1686 and legislation_code = p_legislation_code;
1687 --get_legislation_code(p_business_group_id);
1688
1689 cursor c_get_terminate_date is
1690 select actual_termination_date, pai.action_information16
1691 from per_periods_of_service pps,
1692 pay_action_information pai
1693 where pps.person_id = p_person_id
1694 and pai.action_context_id = p_action_context_id
1695 and pai.action_information_category = 'EMPLOYEE DETAILS'
1696 /* and fnd_date.canonical_to_date(pai.action_information11) = pps.date_start;*/
1697 and format_to_date(pai.action_information11) = pps.date_start;
1698
1699 /* Bug 3722370 - Introduced decode statement in WHERE clause */
1700 cursor c_get_term_details(p_actual_termination_date varchar2,
1701 p_time_period_id number) is
1702 /* Changed this for bug 4132132
1703 select 'Y'
1704 from per_periods_of_service pps
1705 where person_id = p_person_id
1706 and decode(actual_termination_date,NULL,date_start,p_effective_start_date)
1707 between date_start
1708 and nvl(actual_termination_date,p_effective_end_date) ;
1709 */
1710 /* don't show the payslip if the employee is terminated in the given pay period
1711 or prior to the given pay period */
1712 select 'N' from per_time_periods ptp
1713 where ptp.time_period_id = p_time_period_id
1714 and ( p_actual_termination_date between ptp.start_date
1715 and ptp.end_date
1716 or
1717 p_actual_termination_date < ptp.start_date);
1718 l_rule_mode varchar2(10);
1719 l_val varchar2(10);
1720 l_terminate_date date;
1721 l_time_period_id number;
1722
1723 BEGIN
1724
1725 l_val := 'Y' ;
1726 if g_legislation_code is null then
1727 g_legislation_code := get_legislation_code(p_business_group_id);
1728 end if;
1729
1730 if g_legislation_rule is null then
1731 open c_get_legislation_rule(g_legislation_code);
1732 fetch c_get_legislation_rule into l_rule_mode;
1733 close c_get_legislation_rule;
1734 end if;
1735
1736 if l_rule_mode = 'Y' then
1737
1738 open c_get_terminate_date;
1739 fetch c_get_terminate_date
1740 into l_terminate_date, l_time_period_id;
1741 close c_get_terminate_date;
1742
1743 if l_terminate_date IS NULL then
1744 return 'Y';
1745 else
1746 open c_get_term_details(l_terminate_date,l_time_period_id) ;
1747 fetch c_get_term_details into l_val;
1748 close c_get_term_details;
1749 if l_val is null then
1750 l_val := 'Y';
1751 end if;
1752 end if;
1753 else
1754 return 'Y';
1755 end if;
1756
1757 return l_val;
1758
1759 END get_term_info;
1760
1761
1762 FUNCTION get_meaning_payslip_label(p_leg_code VARCHAR2,
1763 p_lookup_code VARCHAR2)
1764 RETURN Varchar2 IS
1765 CURSOR csr_hr_lookup
1766 ( p_lookup_type VARCHAR2
1767 , p_lookup_code VARCHAR2
1768 )
1769 IS
1770 SELECT hr_general_utilities.Get_lookup_Meaning(p_lookup_type,p_lookup_code)
1771 FROM DUAL;
1772
1773 l_meaning hr_lookups.meaning%TYPE;
1774
1775 BEGIN
1776 OPEN csr_hr_lookup(p_leg_code||'_PAYSLIP_LABEL',p_leg_code||'_'||p_lookup_code);
1777 FETCH csr_hr_lookup INTO l_meaning;
1778 CLOSE csr_hr_lookup;
1779
1780 IF l_meaning IS NULL THEN
1781 OPEN csr_hr_lookup('PAYSLIP_LABEL',p_leg_code||'_'||p_lookup_code);
1782 FETCH csr_hr_lookup INTO l_meaning;
1783 CLOSE csr_hr_lookup;
1784 END IF;
1785
1786 return l_meaning;
1787 END get_meaning_payslip_label;
1788
1789
1790 /*********************************************************************
1791 Name : get_full_jurisdiction_name
1792 Purpose : This function returns the name of the jurisdiction
1793 If Jurisdiction_code is like 'XX-000-0000' then
1794 it returns "State Name" using function get_jurisdiction_name
1795 If Jurisdiction_code is like 'XX-XXX-0000' then
1796 it returns "State Name, County Name"
1797 using function get_jurisdiction_name
1798 If Jurisdiction_code is like 'XX-XXX-XXXX' then
1799 it returns "State Name, County Name, City Name"
1800 using function get_jurisdiction_name
1801 In case jurisdiction code could not be found relevent
1802 table then NULL is returned.
1803 Arguments : p_jurisdiction_code
1804 Notes :
1805 *********************************************************************/
1806 FUNCTION get_full_jurisdiction_name(p_jurisdiction_code in varchar2)
1807
1808 RETURN VARCHAR2
1809 IS
1810 lv_state_code VARCHAR2(2);
1811 lv_county_code VARCHAR2(3);
1812 lv_city_code VARCHAR2(4);
1813 lv_jurisdiction_name VARCHAR2(240);
1814 lv_procedure_name VARCHAR2(50);
1815
1816 lv_state_abbrev VARCHAR2(240);
1817 lv_county_name VARCHAR2(240);
1818 lv_city_name VARCHAR2(240);
1819 lv_school_dst VARCHAR2(240);
1820
1821 BEGIN
1822 lv_procedure_name := '.get_jurisdiction_name' ;
1823 lv_state_code := substr(p_jurisdiction_code,1,2);
1824 lv_county_code := substr(p_jurisdiction_code,4,3);
1825 lv_city_code := substr(p_jurisdiction_code,8,4);
1826 lv_jurisdiction_name := null;
1827
1828 hr_utility.set_location(gv_package || lv_procedure_name, 10);
1829 if p_jurisdiction_code like '__-000-0000' then
1830 lv_jurisdiction_name := pay_us_employee_payslip_web.get_jurisdiction_name
1831 (lv_state_code || '-000-0000');
1832
1833 elsif p_jurisdiction_code like '__-___-0000' then
1834 lv_state_abbrev := pay_us_employee_payslip_web.get_jurisdiction_name
1835 (lv_state_code || '-000-0000');
1836 lv_county_name := pay_us_employee_payslip_web.get_jurisdiction_name
1837 (p_jurisdiction_code);
1838
1839 lv_jurisdiction_name := lv_state_abbrev ||', '||lv_county_name;
1840
1841 elsif p_jurisdiction_code like '__-___-____' then
1842 lv_state_abbrev := pay_us_employee_payslip_web.get_jurisdiction_name
1843 (lv_state_code || '-000-0000');
1844
1845 lv_county_name := pay_us_employee_payslip_web.get_jurisdiction_name
1846 (lv_state_code || '-' || lv_county_code || '-0000');
1847 lv_city_name := pay_us_employee_payslip_web.get_jurisdiction_name
1848 (p_jurisdiction_code);
1849
1850 hr_utility.set_location('p_jurisdiction_code -> '||p_jurisdiction_code, 30);
1851 hr_utility.set_location('lv_state_abbrev -> '|| lv_state_abbrev, 30);
1852 hr_utility.set_location('lv_county_name -> '|| lv_county_name, 30);
1853 hr_utility.set_location('lv_city_name -> '|| lv_city_name, 30);
1854
1855 lv_jurisdiction_name := lv_state_abbrev ||', '||
1856 lv_county_name ||', '||
1857 lv_city_name;
1858 elsif length(p_jurisdiction_code) = 8 then
1859 lv_state_abbrev := pay_us_employee_payslip_web.get_jurisdiction_name
1860 (lv_state_code || '-000-0000');
1861 lv_school_dst := pay_us_employee_payslip_web.get_jurisdiction_name
1862 (p_jurisdiction_code);
1863 lv_jurisdiction_name := lv_state_abbrev || ', ' ||
1864 lv_school_dst;
1865
1866 end if;
1867 hr_utility.set_location(gv_package || lv_procedure_name, 30);
1868 return (lv_jurisdiction_name);
1869 END get_full_jurisdiction_name;
1870
1871 -- This Function will be called from View Definition of
1872 -- PAY_EMP_NET_DIST_ACTION_INFO_V
1873 -- California OT Enhancement Started Populating pay_action_information
1874 -- Table with Account Details for Payment Method Check
1875 -- We need to hide those Details in Self Service Payslip
1876
1877 FUNCTION get_netpaydistr_segment(p_business_grp_id IN NUMBER
1878 ,p_org_pay_meth_id IN NUMBER)
1879 RETURN VARCHAR2 IS
1880
1881 cursor cur_legislation(p_business_grp_id in number) is
1882 select hoi.org_information9
1883 from hr_organization_information hoi
1884 where hoi.organization_id = p_business_grp_id
1885 and hoi.org_information_context = 'Business Group Information';
1886
1887 cursor cur_payment_typ(p_legislation_code in varchar2
1888 ,p_org_pay_meth_id IN NUMBER) is
1889 select '1'
1890 from pay_payment_types ppt
1891 ,pay_org_payment_methods_f popm
1892 where popm.org_payment_method_id = p_org_pay_meth_id
1893 and popm.payment_type_id = ppt.payment_type_id
1894 and ppt.territory_code = p_legislation_code
1895 and ppt.category = 'CH';
1896
1897 lv_legislation_code VARCHAR2(100);
1898 lv_exists VARCHAR2(10);
1899
1900 BEGIN
1901 hr_utility.trace('Entering into pay_us_employee_payslip_web.get_netpaydistr_segment');
1902 hr_utility.trace('p_business_grp_id := ' || p_business_grp_id);
1903 hr_utility.trace('p_org_pay_meth_id := ' || p_org_pay_meth_id);
1904
1905 open cur_legislation(p_business_grp_id);
1906 fetch cur_legislation into lv_legislation_code;
1907 close cur_legislation;
1908
1909 hr_utility.trace('lv_legislation_code := ' || lv_legislation_code);
1910
1911 -- Legislation Check Should not be needed here and Added for Safer Side
1912 -- Can be removed if it can be confirmed that for All Localizations
1913 -- Payment category is 'CH' for Check / Cheque
1914
1915 IF lv_legislation_code = 'US' THEN
1916
1917 OPEN cur_payment_typ(lv_legislation_code
1918 ,p_org_pay_meth_id);
1919 FETCH cur_payment_typ INTO lv_exists;
1920 CLOSE cur_payment_typ;
1921
1922 hr_utility.trace('lv_exists := ' || lv_exists);
1923
1924 IF lv_exists = '1' THEN
1925 RETURN 'TRUE';
1926 ELSE
1927 RETURN 'FALSE';
1928 END IF;
1929
1930 ELSE
1931 return 'FALSE';
1932 END IF;
1933
1934 END get_netpaydistr_segment;
1935
1936
1937 BEGIN
1938 gv_package := 'pay_us_employee_payslip_web';
1939 -- hr_utility.trace_on(null, 'PAYSLIP');
1940 END pay_us_employee_payslip_web;
[Home] [Help]