1 PACKAGE BODY pay_us_employee_payslip_web
2 /* $Header: pyusempw.pkb 120.41.12020000.3 2012/11/28 07:01:12 pkoduri 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 28-Nov-2012 PKODURI 115.84 14682114 Handled the behavior of SS prefs for a
32 Terminated and a future terminated employee
33 basing on the termination date relative
34 to the payroll cycle.
35 13-OCT-2011 PKODURI 115.82 13084713 Removed effective date in where clause
36 as it is could return no data exeption
37 for a terminated employee.
38 12-JUL-2011 RNESTOR 115.81 12724038 GSCC error hard coded date
39 11_jul-2011 RNESTOR 115.80 12724038 Comment issue
40 11-JUL-2011 RNESTOR 115.79 12724038 Changed comments using
41 did not like
42 01-JUL-2011 RNESTOR 115.78 11730301 add ceck for actual_termination_date
43 and get_person_info_t cursor
44 31-May-2011 vvijayku 115.77 10169891 g_job_label is set to null for US loc.
45 26-jan-2011 rnestor 115.76 10628755 Add the condition for both term and employee
46 19-Oct-2010 rnestor 115.74 9074420 Modified get_person_info
47 29-Jul-2010 asgugupt 115.68 9916821 Modified check_emp_personal_payment
48 24-Jun-2010 mikarthi 115.67 9745300 Added function get_display_list for Arabic Hijrah
49 calendar support in Choose Payslip list
50 22-Jun-2010 vaisriva 115.65 9804074 Added a procedure specific to JP legislation for
51 Payslip Performance Improvement
52 15-Jun-2010 vaisriva 115.64 9804074 Added code specific to the JP legislation for
53 Payslip Performance Improvement
54 03-May-2010 mikarthi 115.63 9555144 check_emp_personal_payment made generic
55 which can be used by all localizations
56 21-Feb-2010 mikarthi 115.62 9394861 Making sure cursor get_choose_payslip is closed before
57 opening for the first time
58 21-Feb-2010 mikarthi 115.61 9394861 Moved java VO Logic to cursor get_choose_payslip
59 21-Feb-2010 mikarthi 115.60 9394861 NOCOPY hint added
60 21-Feb-2010 mikarthi 115.59 9394861 Payslip Perf Enhancement
61 a) New function check_us_emp_personal_payment
62 b) New Function check_emp_personal_payment
63 23-Jul-2009 vijranga 115.58 8522324 Added a new function
64 check_ie_emp_personal_payment
65 and also modified the function
66 check_emp_personal_payment to
67 address purge-payslip issue for IE localisation.
68 01-Jul-2009 jvaradra 115.57 8643214 Added a new cursor c_get_business_group_id
69 and made use of get_legislation_code function.
70 28-May-2009 krreddy 115.56 7648285 Added a new function
71 check_sa_emp_personal_payment
72 and also modified the function
73 check_emp_personal_payment to
74 address purge-payslip issue.
75 27-May-2009 mikarthi 115.55 8550075 Modified check_emp_personal_payment
76 to fix payslip view issue for
77 supplemental run
78 23-Apr-2009 mikarthi 115.54 8245514 Modified get_netpaydistr_segment
79 26-Mar-2009 krreddy 115.53 8303577 Modified check_emp_personal_payment
80 to fix the payslip displaying before
81 process completion issue.
82 09-Feb-2009 npannamp 115.52 8197823 Modified get_netpaydistr_segment
83 function to include 'GB' localization
84 also.
85 05-Dec-2008 krreddy 115.51 7171712 Added a new cursor c_get_archived
86 to check whether archive data
87 exists for the current
88 assignment_action_id.
89 11-Nov-2008 krreddy 115.50 7171712 Modified the function
90 check_emp_personal_payment to update
91 the validations.
92 10-Nov-2008 krreddy 115.49 7171712 Added a new function
93 check_gb_emp_personal_payment
94 and also modified the function
95 check_emp_personal_payment
96 05-MAR-2008 sudedas 115.45 6739242 Added new Function
97 get_netpaydistr_segment.
98 22-SEP-2005 ahanda 115.17 4622911 Added code for SD for Function
99 "get_full_jurisdiction_name"
100 08-SEP-2005 ppanda 115.16 Function "get_full_jurisdiction_name"
101 added to derive the full jurisdiction
102 (State, County, City) using existing
103 function get_jurisdiction_nmame
104 07-JUN-2005 ahanda 115.42 4417645 Changed code to check for sysdate
105 to be greater then payslip offset
106 to show Payslip
107 05-MAY-2005 ahanda 115.41 4246280 Changed Payslip code to check for
108 View Payslip offset before showing
109 Payslip for an employee.
110 Added overloaded function -
111 check_emp_personal_payment with
112 parameter of p_time_period_id
113 22-FEB-2005 sodhingr 115.40 4186737 changed the get_term_info to use
114 format_to_date function to compare the
115 date.
116 17-FEB-2005 sodhingr 115.39 4186737 changed get_term_info to fix the error
117 due to date format.
118 28-JAN-2005 sodhingr 115.38 4132132 Changed get_term_info to stop the
119 payslip if the terminationdate = period
120 end date
121 19-JAN-2005 sodhingr 115.37 4132132 Changed the function get_term_info
122 24-AUG-2004 rsethupa 115.36 3722370 Changed cursor c2 in function
123 get_term_info. This will select 'Y' for
124 all pay periods upto actual termination
125 date.
126 27-MAY-2004 rsethupa 115.35 3487250 Changed cursor c_currency_code to
127 fetch by hou.organization_id instead
128 of hou.business_group_id
129 28-MAR-2004 ahanda 115.34 Changed check_emp_personal_payment
130 to check if archiver is locking
131 prepay or run action.
132 07-JAN-2004 kaverma 115.33 3350023 Modified cursor c_hourly_salary to remove
133 MERGE JOIN CARTESIAN
134 22-DEC-2003 ahanda 115.32 3331020 Changed cursor c_check_for_reversal.
135 06-Nov-2003 pganguly 115.31 Changed the procedure get_term_info
136 so that it caches the legislation
137 _code, legislation_rule.
138 18-Sep-2003 sdahiya 115.30 2976050 Modified the
139 check_emp_personal_payment procedure
140 so that it calls
141 get_payment_status_code
142 instead of get_payment_status.
143 02-Sep-2003 meshah 115.29 3124483 using actual_termination_date instead
144 of final_prcess_date.
145 02-Sep-2003 meshah 115.28 3124483 the cursor get_person_info in
146 get_doc_eit function has been changed.
147 Now joining to per_periods_of_service
148 to find out if the employee is
149 terminated.
150 19-JUL-2003 ahanda 115.27 Added function format_to_date.
151 23-May-03 ekim 115.26 2897743 Added c_get_lookup_for_paid.
152 30-APR-03 asasthan 115.25 2925411 Added to_char in c_check_number
153 cursor
154 07-Feb-03 ekim 115.24 2716253 Performance fix on c_regular_salary.
155 23-JAN-2002 ahanda 115.23 2764088 Changed cursor get_bg_eit in
156 function get_doc_eit for performance.
157 15-NOV-2002 ahanda 115.22 Modified function get_jurisdiction_name
158 Changed c_get_state to return
159 state_abbrev.
160 14-NOV-2002 tclewis 115.21 Modified the order of parameters
161 on the get_check_number function
162 now pass pp_ass_act , pre_pay_id.
163 21-OCT-2002 tclewis 115.19 changed get_check_no, to return a
164 deposit advice number. Either,
165 pre-payment assignment action id
166 for Master payment or Run AAID for
167 the sep payment AAID.
168 09-OCT-2002 ahanda 115.18 2474524 Changed check_emp_personal_payment
169 15-AUG-2002 ahanda 115.17 Changed get_proposed_emp_salary for
170 performance.
171 18-JUL-2002 ahanda 115.16 Changed the get_jurisdiction_name
172 function to return NULL is not a US
173 jurisdiction.
174 16-JUN-2002 sodhingr 115.15 Added a new function get_term_info
175 to check
176 the terminated employee based
177 on the legislation_field_info
178
179 13-MAY-2002 pganguly 115.13 2363857 Added a new function
180 get_legislation_code.
181 01-MAY-2002 ahanda 115.12 2352332 Changed get_check_number to check
182 for Void.
183 23-MAR-2002 ahanda 115.11 Fixed compilation errors
184 22-MAR-2002 ekim 115.10 Removed trace_on.
185 21-MAR-2002 ekim 115.9 Changed get_doc_eit function.
186 15-FEB-2002 ahanda 115.7 2229092 Changed get_check_number to check for
187 External Manual Payments.
188 24_JAN-2002 dgarg 115.6 Added get_jurisdiction_name
189 function.
190 05-OCT-2001 ekim 115.5 Added get_doc_eit function.
191 21-SEP-2001 ekim 115.4 Added get_format_value function.
192 17-SEP-2001 assathan 115.3 Added get_check_number for payslip
193 09-FEB-2001 ahanda 115.2 Changed the procedure
194 check_emp_personal_payment for
195 performance.
196 14-DEC-2000 ahanda 115.1 1343941/ Changed the procedure
197 1494453 check_emp_personal_payment to go of pre
198 payments instead of personal payment
199 methods. This will also fix issue of
200 Payslip not printing Zero net.
201 10-FEB-2000 ahanda 115.0 Changed proposed_salary to
202 proposed_salary_n for function
203 get_proposed_emp_salary.
204 ****************************************************************************
205 01-FEB-2000 ahanda 110.3 Changed function to get School Dst
206 Name from city if it is not there
207 in county dsts table.
208 01-FEB-2000 ahanda 110.2 Added function to get School Dst Name.
209 24-DEC-1999 ahanda 110.1 1117470 Changed get_proposed_emp_salary to get
210 1116604 proposed salary effective on period end
211 date. Changed the check_for_paid cursor
212 to check for if checkwriter has been
213 locked for of Void Pymt and Run in
214 case of Reversal.
215 01-JUL-1999 ahanda 110.0 Created.
216 ****************************************************************************/
217 AS
218
219 gv_package VARCHAR2(100);
220
221 -- Added for Testing
222 /*****************************************************************************
223 ** Name: FUNCTION check_emp_personal_payment
224 ** Arguments: p_assignment_id => Assignemnt ID
225 ** p_payroll_id => Payroll ID
226 ** p_time_period_id => Time Period ID
227 ** p_assignment_action_id => See below for details
228 ** p_effective_date => Payment Date
229 ** p_payment_category => Payment Category
230 ** p_legislation_code => Territory_code
231 ** Description: Overloaded function with the parameter for time_period_id
232 **
233 ** Function to find out if all the personal payment methods
234 ** for the employee have been processed.
235 **
236 ** The function returns 'Y' if the Payroll has been processed
237 ** completely i.e.
238 ** Payroll Run -> Quick Pay Pre-Payment -> Check Writer/BACS
239 ** Quick Payment -> Pre-Payment -> Nacha/ Ext. Manual Payment
240 **
241 ** If the Payroll has been revered the function returns 'N'
242 **
243 ** Assignment_action_id passed to it can be the action for
244 ** archive process or payroll run. Both both action, we get
245 ** the prepayment_action_id and use it to check payment methods.
246 **
247 *****************************************************************************/
248 FUNCTION check_emp_personal_payment(
249 p_assignment_id number
250 ,p_payroll_id number
251 ,p_time_period_id number
252 ,p_assignment_action_id number
253 ,p_effective_date date
254 ,p_payment_category varchar2
255 ,p_legislation_code varchar2
256 )
257 RETURN VARCHAR2 IS
258
259 /* Cursor to get Payslip offset date for a payroll */
260 cursor c_view_offset(cp_time_period_id in number) is
261 select payslip_view_date
262 from per_time_periods ptp
263 where time_period_id = cp_time_period_id;
264
265 /* Cursor to get the how employee is paid */
266 cursor c_pre_payment_method
267 (cp_assignment_action_id number
268 ,cp_payment_category varchar2
269 ,cp_legislation_code varchar2) is
270 select ppp.pre_payment_id
271 from pay_payment_types ppt,
272 pay_org_payment_methods_f popm,
273 pay_pre_payments ppp
274 where ppp.assignment_action_id = cp_assignment_action_id
275 and popm.org_payment_method_id = ppp.org_payment_method_id
276 and popm.defined_balance_id is not null
277 and ppt.payment_type_id = popm.payment_type_id
278 and ppt.category = cp_payment_category
279 and ppt.territory_code = cp_legislation_code;
280
281
282 cursor c_check_for_reversal(cp_assignment_action_id in number) is
283 select 1
284 from pay_action_interlocks pai_pre
285 where pai_pre.locking_action_id = cp_assignment_action_id
286 and exists (
287 select 1
288 from pay_payroll_actions ppa,
289 pay_assignment_actions paa,
290 pay_action_interlocks pai_run
291 /* Get the run assignment action id locked by pre-payment */
292 where pai_run.locked_action_id = pai_pre.locked_action_id
293 /* Check if the Run is being locked by Reversal */
294 and pai_run.locking_action_id = paa.assignment_action_id
295 and ppa.payroll_action_id = paa.payroll_action_id
296 and paa.action_status = 'C'
297 and ppa.action_type = 'V');
298
299 /****************************************************************
300 ** If archiver is locking the pre-payment assignment_action_id,
301 ** we get it from interlocks and use it to check if all payments
302 ** have been made fro the employee.
303 ****************************************************************/
304 cursor c_prepay_arch_action(cp_assignment_action_id in number) is
305 select paa.assignment_action_id
306 from pay_action_interlocks paci,
307 pay_assignment_actions paa,
308 pay_payroll_actions ppa
309 where paci.locking_action_id = cp_assignment_action_id
310 and paa.assignment_action_id = paci.locked_action_id
311 and ppa.payroll_action_id = paa.payroll_action_id
312 and ppa.action_type in ('P', 'U');
313
314 /****************************************************************
315 ** If archiver is locking the run assignment_action_id, we get
316 ** the corresponding run assignment_action_id and then get
317 ** the pre-payment assignemnt_action_id.
318 ** This cursor is only required when there are child action which
319 ** means there is a seperate check.
320 * ***************************************************************/
321 cursor c_prepay_run_arch_action(cp_assignment_action_id in number) is
322 select paa_pre.assignment_action_id
323 from pay_action_interlocks pai_run,
324 pay_action_interlocks pai_pre,
325 pay_assignment_actions paa_pre,
326 pay_payroll_actions ppa_pre
327 where pai_run.locking_action_id = cp_assignment_action_id
328 and pai_pre.locked_action_id = pai_run.locked_action_id
329 and paa_pre.assignment_Action_id = pai_pre.locking_action_id
330 and ppa_pre.payroll_action_id = paa_pre.payroll_action_id
331 and ppa_pre.action_type in ('P', 'U');
332
333 cursor c_get_date_earned(cp_assignment_action_id in number) is
334 select nvl(max(ppa.date_earned), max(ppa.effective_date))
335 from pay_payroll_actions ppa
336 ,pay_assignment_actions paa
337 ,pay_action_interlocks pai
338 where ppa.payroll_action_id = paa.payroll_action_id
339 and pai.locked_action_id = paa.assignment_action_id
340 and pai.locking_action_id = cp_assignment_action_id
341 and ppa.action_type in ('R', 'Q', 'B', 'V');
342
343 cursor c_time_period(cp_payroll_id in number
344 ,cp_date_earned in date) is
345 select ptp.time_period_id
346 from per_time_periods ptp
347 where cp_date_earned between ptp.start_date
348 and ptp.end_Date
349 and ptp.payroll_id = cp_payroll_id;
350
351 cursor c_no_prepayments (cp_prepayment_action_id in number) is
352 select 1
353 from dual
354 where not exists
355 (select 1
356 from pay_pre_payments ppp
357 where ppp.assignment_action_id = cp_prepayment_action_id
358 );
359
360 lv_reversal_exists VARCHAR2(1);
361 ln_prepay_action_id NUMBER;
362 ln_pre_payment_id NUMBER;
363 lv_payment_status VARCHAR2(50);
364 lv_paid_lookup_meaning VARCHAR2(10);
365 lv_return_flag VARCHAR2(1);
366 lc_no_prepayment_flag VARCHAR2(1);
367
368 ld_view_payslip_offset_date DATE;
369 ld_earned_date DATE;
370 ln_time_period_id NUMBER;
371
372 BEGIN
373
374 hr_utility.trace('Entering check_emp_personal_payment');
375 hr_utility.trace('p_effective_date='||p_effective_date);
376 hr_utility.trace('p_time_period_id='||p_time_period_id);
377
378 IF p_payment_category IS NOT NULL THEN
379
380 lv_return_flag := 'Y';
381 open c_prepay_arch_action(p_assignment_action_id);
382 fetch c_prepay_arch_action into ln_prepay_action_id;
383 if c_prepay_arch_action%notfound then
384 open c_prepay_run_arch_action(p_assignment_action_id);
385 fetch c_prepay_run_arch_action into ln_prepay_action_id;
386 if c_prepay_run_arch_action%notfound then
387 return('N');
388 end if;
389 close c_prepay_run_arch_action;
390 end if;
391 close c_prepay_arch_action;
392
393 ln_time_period_id := p_time_period_id;
394 if ln_time_period_id is null then
395 open c_get_date_earned(ln_prepay_action_id);
396 fetch c_get_date_earned into ld_earned_date;
397 if c_get_date_earned%found then
398 open c_time_period(p_payroll_id, ld_earned_date);
399 fetch c_time_period into ln_time_period_id;
400 close c_time_period;
401 end if;
402 close c_get_date_earned;
403 end if;
404
405 hr_utility.trace('ln_time_period_id='||ln_time_period_id);
406 open c_view_offset(ln_time_period_id);
407 fetch c_view_offset into ld_view_payslip_offset_date;
408 close c_view_offset;
409 hr_utility.trace('ld_view_payslip_offset_date='||trunc(ld_view_payslip_offset_date));
410 hr_utility.trace('p_effective_date='||trunc(p_effective_date));
411 hr_utility.trace('sysdate='||trunc(sysdate));
412
413 /* check if the Payslip view date is populated. If it is, check the value
414 and make sure it is > sysdate otherwise don't show payslip */
415 if ld_view_payslip_offset_date is not null and
416 trunc(ld_view_payslip_offset_date) > trunc(sysdate) then
417 hr_utility.trace('View offset return N');
418 return('N');
419 end if;
420
421 open c_check_for_reversal(ln_prepay_action_id);
422 fetch c_check_for_reversal into lv_reversal_exists;
423 if c_check_for_reversal%found then
424 lv_return_flag := 'N';
425 else
426 open c_pre_payment_method (ln_prepay_action_id
427 ,p_payment_category
428 ,p_legislation_code);
429 loop
430 /* fetch all the pre payment records for the asssignment
431 other than 3rd party payment */
432 fetch c_pre_payment_method into ln_pre_payment_id;
433
434 if c_pre_payment_method%notfound then
435 exit;
436 end if;
437
438 lv_payment_status := ltrim(rtrim(
439 pay_assignment_actions_pkg.get_payment_status_code
440 (ln_prepay_action_id,
441 ln_pre_payment_id)));
442
443 if lv_payment_status <> 'P' then
444 lv_return_flag := 'N';
445 exit;
446 else
447 lv_return_flag := 'Y';
448 end if;
449
450 end loop;
451 IF lv_payment_status IS NULL THEN
452 lv_return_flag := 'N' ;
453 END IF ;
454 close c_pre_payment_method;
455
456 end if;
457 close c_check_for_reversal;
458
459 IF p_payment_category = 'MT' and p_legislation_code = 'US' THEN
460 OPEN c_no_prepayments(ln_prepay_action_id);
461 FETCH c_no_prepayments INTO lc_no_prepayment_flag;
462 IF c_no_prepayments%found THEN
463 lv_return_flag := 'Y';
464 END IF;
465 CLOSE c_no_prepayments;
466 END IF;
467
468 hr_utility.trace('lv_return_flag='||lv_return_flag);
469 hr_utility.trace('Leaving check_emp_personal_payment');
470
471 return lv_return_flag;
472 ELSE
473 return(check_emp_personal_payment(
474 p_assignment_id => p_assignment_id
475 ,p_payroll_id => p_payroll_id
476 ,p_time_period_id => p_time_period_id
477 ,p_assignment_action_id => p_assignment_action_id
478 ,p_effective_date => p_effective_date));
479 END IF;
480 END check_emp_personal_payment;
481
482 --Start of changes
483 /****************************************************************
484 ** Below function is added for the bug# 7171712.
485 ** It returns 'N' if offset date exists and its greater than
486 ** sysdate.
487 *****************************************************************/
488
489 FUNCTION check_gb_emp_personal_payment(
490 p_assignment_id number
491 ,p_payroll_id number
492 ,p_time_period_id number
493 ,p_assignment_action_id number
494 ,p_effective_date date
495 )
496 RETURN VARCHAR2 IS
497
498 /* Cursor to get Payslip offset date for a payroll */
499 cursor c_view_offset(cp_time_period_id in number) is
500 select payslip_view_date
501 from per_time_periods ptp
502 where time_period_id = cp_time_period_id;
503
504 /* Cursor to get date earned for a payroll */
505 cursor c_get_date_earned(cp_assignment_action_id in number) is
506 select max(effective_date)
507 from pay_action_information
508 where action_context_id = cp_assignment_action_id
509 and action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION';
510
511 /* Cursor to get time period for a payroll */
512 cursor c_time_period(cp_payroll_id in number
513 ,cp_date_earned in date) is
514 select ptp.time_period_id
515 from per_time_periods ptp
516 where cp_date_earned between ptp.start_date
517 and ptp.end_Date
518 and ptp.payroll_id = cp_payroll_id;
519
520 /* Cursor to check whether archive data exists or not*/
521 cursor c_get_archived is
522 select count(*)
523 from pay_action_information
524 where action_context_id = p_assignment_action_id
525 and action_information_category ='GB ELEMENT PAYSLIP INFO';
526
527 lv_return_flag VARCHAR2(1);
528 ld_view_payslip_offset_date DATE;
529 ld_earned_date DATE;
530 ln_time_period_id NUMBER;
531 ln_count NUMBER;
532
533 BEGIN
534 hr_utility.trace('Entering check_gb_emp_personal_payment');
535 hr_utility.trace('p_assignment_id='||p_assignment_id);
536 hr_utility.trace('p_payroll_id='||p_payroll_id);
537 hr_utility.trace('p_assignment_action_id='||p_assignment_action_id);
538 hr_utility.trace('p_effective_date='||p_effective_date);
539 hr_utility.trace('p_time_period_id='||p_time_period_id);
540
541 lv_return_flag := 'Y';
542
543 ln_time_period_id := p_time_period_id;
544 if ln_time_period_id is null then
545 open c_get_date_earned(p_assignment_action_id);
546 fetch c_get_date_earned into ld_earned_date;
547 if c_get_date_earned%found then
548 open c_time_period(p_payroll_id, ld_earned_date);
549 fetch c_time_period into ln_time_period_id;
550 close c_time_period;
551 end if;
552 close c_get_date_earned;
553 end if;
554
555 hr_utility.trace('ln_time_period_id='||ln_time_period_id);
556 open c_view_offset(ln_time_period_id);
557 fetch c_view_offset into ld_view_payslip_offset_date;
558 close c_view_offset;
559 hr_utility.trace('ld_view_payslip_offset_date='||trunc(ld_view_payslip_offset_date));
560 hr_utility.trace('p_effective_date='||trunc(p_effective_date));
561 hr_utility.trace('sysdate='||trunc(sysdate));
562
563 /* check if the Payslip view date is populated. If it is, check the value
564 and make sure it is > sysdate otherwise don't show payslip */
565 if ld_view_payslip_offset_date is not null and
566 trunc(ld_view_payslip_offset_date) > trunc(sysdate) then
567 hr_utility.trace('View offset return N');
568 return('N');
569 end if;
570
571 ln_count := 0;
572 open c_get_archived;
573 fetch c_get_archived into ln_count;
574 /*Check whether archive data exists for this assignment_action_id*/
575 if (ln_count = 0)
576 then return('N');
577 end if;
578 close c_get_archived;
579
580 hr_utility.trace('lv_return_flag='||lv_return_flag);
581 hr_utility.trace('Leaving check_gb_emp_personal_payment');
582
583 return lv_return_flag;
584
585 END check_gb_emp_personal_payment;
586
587 --Start of changes
588 /****************************************************************
589 ** Below function is added for the bug# 7648285.
590 ** It returns 'N' if offset date exists and its greater than
591 ** sysdate.
592 *****************************************************************/
593
594 FUNCTION check_sa_emp_personal_payment(
595 p_assignment_id number
596 ,p_payroll_id number
597 ,p_time_period_id number
598 ,p_assignment_action_id number
599 ,p_effective_date date
600 )
601 RETURN VARCHAR2 IS
602
603 /* Cursor to get Payslip offset date for a payroll */
604 cursor c_view_offset(cp_time_period_id in number) is
605 select payslip_view_date
606 from per_time_periods ptp
607 where time_period_id = cp_time_period_id;
608
609 /* Cursor to get date earned for a payroll */
610 cursor c_get_date_earned(cp_assignment_action_id in number) is
611 select max(effective_date)
612 from pay_action_information
613 where action_context_id = cp_assignment_action_id
614 and action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION';
615
616 /* Cursor to get time period for a payroll */
617 cursor c_time_period(cp_payroll_id in number
618 ,cp_date_earned in date) is
619 select ptp.time_period_id
620 from per_time_periods ptp
621 where cp_date_earned between ptp.start_date
622 and ptp.end_Date
623 and ptp.payroll_id = cp_payroll_id;
624
625 /* Cursor to check whether archive data exists or not*/
626 cursor c_get_archived is
627 select count(*)
628 from pay_action_information
629 where action_context_id = p_assignment_action_id
630 and action_information_category = 'EMEA ELEMENT INFO'
631 and action_information3 in ('E','D');
632
633 lv_return_flag VARCHAR2(1);
634 ld_view_payslip_offset_date DATE;
635 ld_earned_date DATE;
636 ln_time_period_id NUMBER;
637 ln_count NUMBER;
638
639 BEGIN
640 hr_utility.trace('Entering check_sa_emp_personal_payment');
641 hr_utility.trace('p_assignment_id='||p_assignment_id);
642 hr_utility.trace('p_payroll_id='||p_payroll_id);
643 hr_utility.trace('p_assignment_action_id='||p_assignment_action_id);
644 hr_utility.trace('p_effective_date='||p_effective_date);
645 hr_utility.trace('p_time_period_id='||p_time_period_id);
646
647 lv_return_flag := 'Y';
648
649 ln_time_period_id := p_time_period_id;
650 if ln_time_period_id is null then
651 open c_get_date_earned(p_assignment_action_id);
652 fetch c_get_date_earned into ld_earned_date;
653 if c_get_date_earned%found then
654 open c_time_period(p_payroll_id, ld_earned_date);
655 fetch c_time_period into ln_time_period_id;
656 close c_time_period;
657 end if;
658 close c_get_date_earned;
659 end if;
660
661 hr_utility.trace('ln_time_period_id='||ln_time_period_id);
662 open c_view_offset(ln_time_period_id);
663 fetch c_view_offset into ld_view_payslip_offset_date;
664 close c_view_offset;
665 hr_utility.trace('ld_view_payslip_offset_date='||trunc(ld_view_payslip_offset_date));
666 hr_utility.trace('p_effective_date='||trunc(p_effective_date));
667 hr_utility.trace('sysdate='||trunc(sysdate));
668
669 /* check if the Payslip view date is populated. If it is, check the value
670 and make sure it is > sysdate otherwise don't show payslip */
671 if ld_view_payslip_offset_date is not null and
672 trunc(ld_view_payslip_offset_date) > trunc(sysdate) then
673 hr_utility.trace('View offset return N');
674 return('N');
675 end if;
676
677 ln_count := 0;
678 open c_get_archived;
679 fetch c_get_archived into ln_count;
680 /*Check whether archive data exists for this assignment_action_id*/
681 if (ln_count = 0)
682 then return('N');
683 end if;
684 close c_get_archived;
685
686 hr_utility.trace('lv_return_flag='||lv_return_flag);
687 hr_utility.trace('Leaving check_sa_emp_personal_payment');
688
689 return lv_return_flag;
690
691 END check_sa_emp_personal_payment;
692
693 --8522324 fix start
694 /****************************************************************
695 ** Below function is added for the bug# 8522324.
696 ** It returns 'N' if offset date exists and its greater than
697 ** sysdate.
698 *****************************************************************/
699
700 FUNCTION check_ie_emp_personal_payment(
701 p_assignment_id number
702 ,p_payroll_id number
703 ,p_time_period_id number
704 ,p_assignment_action_id number
705 ,p_effective_date date
706 )
707 RETURN VARCHAR2 IS
708
709 /* Cursor to get Payslip offset date for a payroll */
710 cursor c_view_offset(cp_time_period_id in number) is
711 select payslip_view_date
712 from per_time_periods ptp
713 where time_period_id = cp_time_period_id;
714
715 /* Cursor to get date earned for a payroll */
716 cursor c_get_date_earned(cp_assignment_action_id in number) is
717 select max(effective_date)
718 from pay_action_information
719 where action_context_id = cp_assignment_action_id
720 and action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION';
721
722 /* Cursor to get time period for a payroll */
723 cursor c_time_period(cp_payroll_id in number
724 ,cp_date_earned in date) is
725 select ptp.time_period_id
726 from per_time_periods ptp
727 where cp_date_earned between ptp.start_date
728 and ptp.end_Date
729 and ptp.payroll_id = cp_payroll_id;
730
731 /* Cursor to check whether archive data exists or not*/
732 cursor c_get_archived is
733 select count(*)
734 from pay_action_information
735 where action_context_id = p_assignment_action_id
736 and action_information_category = 'EMEA ELEMENT INFO'
737 and action_information3 in ('E','D');
738
739 lv_return_flag VARCHAR2(1);
740 ld_view_payslip_offset_date DATE;
741 ld_earned_date DATE;
742 ln_time_period_id NUMBER;
743 ln_count NUMBER;
744
745 BEGIN
746 hr_utility.trace('Entering check_ie_emp_personal_payment');
747 hr_utility.trace('p_assignment_id='||p_assignment_id);
748 hr_utility.trace('p_payroll_id='||p_payroll_id);
749 hr_utility.trace('p_assignment_action_id='||p_assignment_action_id);
750 hr_utility.trace('p_effective_date='||p_effective_date);
751 hr_utility.trace('p_time_period_id='||p_time_period_id);
752
753 lv_return_flag := 'Y';
754
755 ln_time_period_id := p_time_period_id;
756 if ln_time_period_id is null then
757 open c_get_date_earned(p_assignment_action_id);
758 fetch c_get_date_earned into ld_earned_date;
759 if c_get_date_earned%found then
760 open c_time_period(p_payroll_id, ld_earned_date);
761 fetch c_time_period into ln_time_period_id;
762 close c_time_period;
763 end if;
764 close c_get_date_earned;
765 end if;
766
767 hr_utility.trace('ln_time_period_id='||ln_time_period_id);
768 open c_view_offset(ln_time_period_id);
769 fetch c_view_offset into ld_view_payslip_offset_date;
770 close c_view_offset;
771 hr_utility.trace('ld_view_payslip_offset_date='||trunc(ld_view_payslip_offset_date));
772 hr_utility.trace('p_effective_date='||trunc(p_effective_date));
773 hr_utility.trace('sysdate='||trunc(sysdate));
774
775 /* check if the Payslip view date is populated. If it is, check the value
776 and make sure it is > sysdate otherwise don't show payslip */
777 if ld_view_payslip_offset_date is not null and
778 trunc(ld_view_payslip_offset_date) > trunc(sysdate) then
779 hr_utility.trace('View offset return N');
780 return('N');
781 end if;
782
783 ln_count := 0;
784 open c_get_archived;
785 fetch c_get_archived into ln_count;
786 /*Check whether archive data exists for this assignment_action_id*/
787 if (ln_count = 0)
788 then return('N');
789 end if;
790 close c_get_archived;
791
792 hr_utility.trace('lv_return_flag='||lv_return_flag);
793 hr_utility.trace('Leaving check_ie_emp_personal_payment');
794
795 return lv_return_flag;
796
797 END check_ie_emp_personal_payment;
798 --8522324 fix end
799
800 /*****************************************************************************
801 ** Name: FUNCTION check_emp_personal_payment
802 ** Arguments: p_assignment_id => Assignemnt ID
803 ** p_payroll_id => Payroll ID
804 ** p_time_period_id => Time Period ID
805 ** p_assignment_action_id => See below for details
806 ** p_effective_date => Payment Date
807 ** Description: Overloaded function with the parameter for time_period_id
808 **
809 ** Function to find out if all the personal payment methods
810 ** for the employee have been processed.
811 **
812 ** The function returns 'Y' if the Payroll has been processed
813 ** completely i.e.
814 ** Payroll Run -> Quick Pay Pre-Payment -> Check Writer/BACS
815 ** Quick Payment -> Pre-Payment -> Nacha/ Ext. Manual Payment
816 **
817 ** If the Payroll has been revered the function returns 'N'
818 **
819 ** Assignment_action_id passed to it can be the action for
820 ** archive process or payroll run. Both both action, we get
821 ** the prepayment_action_id and use it to check payment methods.
822 **
823 *****************************************************************************/
824 FUNCTION check_emp_personal_payment(
825 p_assignment_id number
826 ,p_payroll_id number
827 ,p_time_period_id number
828 ,p_assignment_action_id number
829 ,p_effective_date date
830 )
831 RETURN VARCHAR2 IS
832
833 /* Cursor to get Payslip offset date for a payroll */
834 --Added REGULAR_PAYMENT_DATE for bug 8550075
835 CURSOR c_view_offset(cp_time_period_id IN NUMBER) IS
836 SELECT payslip_view_date, REGULAR_PAYMENT_DATE
837 FROM per_time_periods ptp
838 WHERE time_period_id = cp_time_period_id;
839
840 /* Cursor to get the how employee is paid */
841 cursor c_pre_payment_method
842 (cp_assignment_action_id number) is
843 select ppp.pre_payment_id
844 from pay_payment_types ppt,
845 pay_org_payment_methods_f popm,
846 pay_pre_payments ppp
847 where ppp.assignment_action_id = cp_assignment_action_id
848 and popm.org_payment_method_id = ppp.org_payment_method_id
849 and popm.defined_balance_id is not null
850 and ppt.payment_type_id = popm.payment_type_id;
851
852
853 cursor c_check_for_reversal(cp_assignment_action_id in number) is
854 select 1
855 from pay_action_interlocks pai_pre
856 where pai_pre.locking_action_id = cp_assignment_action_id
857 and exists (
858 select 1
859 from pay_payroll_actions ppa,
860 pay_assignment_actions paa,
861 pay_action_interlocks pai_run
862 /* Get the run assignment action id locked by pre-payment */
863 where pai_run.locked_action_id = pai_pre.locked_action_id
864 /* Check if the Run is being locked by Reversal */
865 and pai_run.locking_action_id = paa.assignment_action_id
866 and ppa.payroll_action_id = paa.payroll_action_id
867 and paa.action_status = 'C'
868 and ppa.action_type = 'V');
869
870 /****************************************************************
871 ** If archiver is locking the pre-payment assignment_action_id,
872 ** we get it from interlocks and use it to check if all payments
873 ** have been made fro the employee.
874 ****************************************************************/
875 cursor c_prepay_arch_action(cp_assignment_action_id in number) is
876 select paa.assignment_action_id
877 from pay_action_interlocks paci,
878 pay_assignment_actions paa,
879 pay_payroll_actions ppa
880 where paci.locking_action_id = cp_assignment_action_id
881 and paa.assignment_action_id = paci.locked_action_id
882 and ppa.payroll_action_id = paa.payroll_action_id
883 and ppa.action_type in ('P', 'U');
884
885 /****************************************************************
886 ** If archiver is locking the run assignment_action_id, we get
887 ** the corresponding run assignment_action_id and then get
888 ** the pre-payment assignemnt_action_id.
889 ** This cursor is only required when there are child action which
890 ** means there is a seperate check.
891 * ***************************************************************/
892 cursor c_prepay_run_arch_action(cp_assignment_action_id in number) is
893 select paa_pre.assignment_action_id
894 from pay_action_interlocks pai_run,
895 pay_action_interlocks pai_pre,
896 pay_assignment_actions paa_pre,
897 pay_payroll_actions ppa_pre
898 where pai_run.locking_action_id = cp_assignment_action_id
899 and pai_pre.locked_action_id = pai_run.locked_action_id
900 and paa_pre.assignment_Action_id = pai_pre.locking_action_id
901 and ppa_pre.payroll_action_id = paa_pre.payroll_action_id
902 and ppa_pre.action_type in ('P', 'U');
903
904 --Modified Cursor for bug 8550075
905 CURSOR c_get_date_earned(cp_assignment_action_id IN NUMBER) IS
906 SELECT nvl(MAX(ppa.date_earned), MAX(ppa.effective_date)), MAX(ppa.effective_date), BUSINESS_GROUP_ID
907 FROM pay_payroll_actions ppa
908 , pay_assignment_actions paa
909 , pay_action_interlocks pai
910 WHERE ppa.payroll_action_id = paa.payroll_action_id
911 AND pai.locked_action_id = paa.assignment_action_id
912 AND pai.locking_action_id = cp_assignment_action_id
913 AND ppa.action_type IN ('R', 'Q', 'B', 'V')
914 GROUP BY BUSINESS_GROUP_ID;
915
916 cursor c_time_period(cp_payroll_id in number
917 ,cp_date_earned in date) is
918 select ptp.time_period_id
919 from per_time_periods ptp
920 where cp_date_earned between ptp.start_date
921 and ptp.end_Date
922 and ptp.payroll_id = cp_payroll_id;
923
924 /****************************************************************
925 ** Below cursor added for bug 7171712 returns true, if purge is run
926 ** corresponding to the current assignment id.
927 *****************************************************************/
928
929 cursor c_purge_run(cp_assignment_action_id in number) is
930 select 1
931 from pay_assignment_actions paa
932 where paa.assignment_action_id = cp_assignment_action_id
933 and not exists
934 (select 1
935 from pay_action_interlocks ai
936 where ai.locking_action_id = paa.assignment_action_id )
937 and exists
938 (select 1 from pay_assignment_actions paa2,
939 pay_payroll_actions ppa2
940 where paa2.assignment_id = paa.assignment_id
941 and paa2.payroll_action_id = ppa2.payroll_action_id
942 and ppa2.action_type = 'Z');
943
944 /****************************************************************
945 ** Below cursor added for bug 7171712 gets the localization code for the
946 ** corresponding assignment id.
947 *****************************************************************/
948
949 cursor c_get_localization(cp_assignment_id in number) is
950 select legislation_code
951 from per_business_groups
952 where business_group_id in
953 (select business_group_id
954 from per_all_people_f
955 where person_id in
956 (select person_id
957 from per_all_assignments_f
958 where assignment_id = cp_assignment_id));
959
960 /****************************************************************
961 ** Below cursor added for bug 8643214 gets the business group id
962 *****************************************************************/
963
964 cursor c_get_business_group_id (cp_payroll_id in number) is
965 select business_group_id
966 from pay_all_payrolls_f
967 where payroll_id = cp_payroll_id
968 and p_effective_date between effective_start_date and effective_end_date;
969
970 /****************************************************************
971 ** Below cursor added for bug 8303577 gets the status whether
972 ** payslip generation - self service process is completed or not.
973 *****************************************************************/
974
975 cursor c_get_completion_status(cp_assignment_action_id in number) is
976 select 1
977 from pay_assignment_actions paa,
978 pay_payroll_actions ppa
979 where paa.assignment_action_id = cp_assignment_action_id
980 and paa.payroll_action_id = ppa.payroll_action_id
981 and paa.assignment_id = p_assignment_id
982 and paa.action_status = 'C'
983 and ppa.action_status = 'C';
984
985 lv_reversal_exists VARCHAR2(1);
986 ln_prepay_action_id NUMBER;
987 ln_pre_payment_id NUMBER;
988 lv_payment_status VARCHAR2(50);
989 lv_paid_lookup_meaning VARCHAR2(10);
990 lv_return_flag VARCHAR2(1);
991 lv_gb_return_flag VARCHAR2(1);
992 lv_sa_return_flag VARCHAR2(1);
993 lv_ie_return_flag VARCHAR2(1);
994
995 ld_view_payslip_offset_date DATE;
996 ld_date_paid DATE;
997 ld_reg_payment_date DATE;
998 ld_earned_date DATE;
999 ln_time_period_id NUMBER;
1000 ln_localization_code VARCHAR2(10);
1001 ln_offset_value NUMBER;
1002
1003 ld_payslip_view_date DATE;
1004 ln_bg_id NUMBER;
1005 is_dynamic_view_date VARCHAR2(1);
1006 lv_purge_run VARCHAR2(1);
1007 lv_completion_status number; -- Added for the bug 8303577
1008
1009 ln_business_group_id NUMBER; -- Added for bug 8643214
1010
1011 --New cursor defined for bug 8550075
1012 CURSOR c_get_view_date_criteria (cp_bg_id IN NUMBER) IS
1013 SELECT 'Y' FROM hr_organization_information hoi
1014 WHERE hoi.organization_id = cp_bg_id
1015 AND hoi.org_information_context = 'HR_SELF_SERVICE_BG_PREFERENCE'
1016 AND ORG_INFORMATION12 = 'DATE_PAID'
1017 AND ORG_INFORMATION1 = 'PAYSLIP';
1018
1019 BEGIN
1020
1021 hr_utility.trace('Entering check_emp_personal_payment');
1022 hr_utility.trace('p_effective_date='||p_effective_date);
1023 hr_utility.trace('p_time_period_id='||p_time_period_id);
1024
1025 -- BEGIN For bug 8643214
1026 open c_get_business_group_id(p_payroll_id);
1027 fetch c_get_business_group_id into ln_business_group_id;
1028 close c_get_business_group_id;
1029
1030 hr_utility.trace('ln_business_group_id='||ln_business_group_id);
1031
1032 ln_localization_code := get_legislation_code(ln_business_group_id);
1033
1034 hr_utility.trace('ln_localization_code='||ln_localization_code);
1035
1036 -- END For bug 8643214
1037
1038 lv_return_flag := 'Y';
1039 lv_gb_return_flag := 'Y';
1040
1041 open c_prepay_arch_action(p_assignment_action_id);
1042 fetch c_prepay_arch_action into ln_prepay_action_id;
1043 if c_prepay_arch_action%notfound then
1044 open c_prepay_run_arch_action(p_assignment_action_id);
1045 fetch c_prepay_run_arch_action into ln_prepay_action_id;
1046 if c_prepay_run_arch_action%notfound then
1047 --Start of changes for bug# 7171712
1048 open c_purge_run(p_assignment_action_id);
1049 fetch c_purge_run into lv_purge_run;
1050 if c_purge_run%found then
1051 -- Commented for bug 8643214
1052 /*open c_get_localization(p_assignment_id);
1053 fetch c_get_localization into ln_localization_code; */
1054
1055 hr_utility.trace('c_purge_run%found');
1056
1057 if (ln_localization_code = 'GB') then
1058 lv_gb_return_flag :=
1059 check_gb_emp_personal_payment(
1060 p_assignment_id
1061 ,p_payroll_id
1062 ,p_time_period_id
1063 ,p_assignment_action_id
1064 ,p_effective_date
1065 );
1066 if (lv_gb_return_flag = 'Y') then
1067 hr_utility.trace('lv_gb_return_flag='||lv_gb_return_flag);
1068 return('Y');
1069 else
1070 hr_utility.trace('lv_gb_return_flag='||lv_gb_return_flag);
1071 return('N');
1072 end if;
1073 end if;
1074
1075 --Start modifications for the bug# 7648285
1076 if (ln_localization_code = 'SA') then
1077 lv_sa_return_flag :=
1078 check_sa_emp_personal_payment(
1079 p_assignment_id
1080 ,p_payroll_id
1081 ,p_time_period_id
1082 ,p_assignment_action_id
1083 ,p_effective_date
1084 );
1085 if (lv_sa_return_flag = 'Y') then
1086 hr_utility.trace('lv_sa_return_flag='||lv_sa_return_flag);
1087 return('Y');
1088 else
1089 hr_utility.trace('lv_sa_return_flag='||lv_sa_return_flag);
1090 return('N');
1091 end if;
1092 end if;
1093 --End modifications for the bug# 7648285
1094
1095 --Start modifications for the bug# 8522324
1096 if (ln_localization_code = 'IE') then
1097 lv_ie_return_flag :=
1098 check_ie_emp_personal_payment(
1099 p_assignment_id
1100 ,p_payroll_id
1101 ,p_time_period_id
1102 ,p_assignment_action_id
1103 ,p_effective_date
1104 );
1105 if (lv_ie_return_flag = 'Y') then
1106 hr_utility.trace('lv_ie_return_flag='||lv_ie_return_flag);
1107 return('Y');
1108 else
1109 hr_utility.trace('lv_ie_return_flag='||lv_ie_return_flag);
1110 return('N');
1111 end if;
1112 end if;
1113 --End modifications for the bug# 8522324
1114 -- Commented for bug 8643214
1115 /*close c_get_localization; */
1116 end if;
1117 close c_purge_run;
1118 return('N');
1119 --End of changes for bug# 7171712
1120 end if;
1121 close c_prepay_run_arch_action;
1122 end if;
1123 close c_prepay_arch_action;
1124
1125 ln_time_period_id := p_time_period_id;
1126 if ln_time_period_id is null then
1127 open c_get_date_earned(ln_prepay_action_id);
1128 FETCH c_get_date_earned INTO ld_earned_date, ld_date_paid, ln_bg_id;
1129 if c_get_date_earned%found then
1130 open c_time_period(p_payroll_id, ld_earned_date);
1131 fetch c_time_period into ln_time_period_id;
1132 close c_time_period;
1133 end if;
1134 close c_get_date_earned;
1135 end if;
1136
1137 hr_utility.trace('ln_time_period_id='||ln_time_period_id);
1138 open c_view_offset(ln_time_period_id);
1139 FETCH c_view_offset INTO ld_view_payslip_offset_date, ld_reg_payment_date;
1140 close c_view_offset;
1141 hr_utility.trace('ld_view_payslip_offset_date='||trunc(ld_view_payslip_offset_date));
1142 hr_utility.trace('p_effective_date='||trunc(p_effective_date));
1143 hr_utility.trace('sysdate='||trunc(sysdate));
1144
1145
1146 --Commented out for bug 8550075. This has been handled below
1147 /* check if the Payslip view date is populated. If it is, check the value
1148 and make sure it is > sysdate otherwise don't show payslip */
1149 /*if ld_view_payslip_offset_date is not null and
1150 trunc(ld_view_payslip_offset_date) > trunc(sysdate) then
1151 hr_utility.trace('View offset return N');
1152 return('N');
1153 end if;*/
1154
1155 --Starting Fix for bug 8550075
1156 /* If for US legislation, in the self service preference, offset_criteria
1157 is set as "Date Paid", then calclate the payslip view date using Date_paid
1158 of the payroll run. Else go with the default behaviour*/
1159 IF ld_date_paid IS NULL THEN
1160 OPEN c_get_date_earned(ln_prepay_action_id);
1161 FETCH c_get_date_earned INTO ld_earned_date, ld_date_paid, ln_bg_id;
1162 CLOSE c_get_date_earned;
1163 END IF;
1164
1165
1166 IF ld_view_payslip_offset_date IS NOT NULL THEN
1167
1168 IF ln_bg_id IS NOT NULL THEN
1169 OPEN c_get_view_date_criteria(ln_bg_id);
1170 FETCH c_get_view_date_criteria INTO is_dynamic_view_date;
1171 CLOSE c_get_view_date_criteria;
1172 END IF;
1173
1174 if g_legislation_code is null then
1175 g_legislation_code := get_legislation_code(ln_bg_id);
1176 end if;
1177
1178 hr_utility.TRACE('g_legislation_code=' || g_legislation_code);
1179 hr_utility.TRACE('is_dynamic_view_date=' || is_dynamic_view_date);
1180
1181 --If Date_paid preference is set and legislation_code is 'US'
1182 IF is_dynamic_view_date = 'Y' AND ld_date_paid IS NOT NULL and g_legislation_code = 'US' THEN
1183
1184 ln_offset_value := trunc(ld_view_payslip_offset_date - ld_reg_payment_date);
1185
1186 hr_utility.TRACE('ln_offset_value=' || ln_offset_value);
1187 hr_utility.TRACE('ld_view_payslip_offset_date=' || ld_view_payslip_offset_date);
1188
1189 ld_payslip_view_date := trunc(ld_date_paid) + ln_offset_value;
1190 hr_utility.TRACE('ld_payslip_view_date=' || ld_payslip_view_date);
1191
1192 IF trunc(ld_payslip_view_date) > trunc(SYSDATE) THEN
1193 hr_utility.TRACE('View offset return N');
1194 RETURN('N');
1195 END IF;
1196 --Default Behaviour
1197 ELSE
1198 hr_utility.TRACE('Offset Criteria is not Date_paid ');
1199 IF trunc(ld_view_payslip_offset_date) > trunc(SYSDATE) THEN
1200 hr_utility.TRACE('View offset return N');
1201 RETURN('N');
1202 END IF;
1203 END IF;
1204
1205 END IF;
1206 --End of Fix for bug 8550075
1207
1208 open c_check_for_reversal(ln_prepay_action_id);
1209 fetch c_check_for_reversal into lv_reversal_exists;
1210 if c_check_for_reversal%found then
1211 lv_return_flag := 'N';
1212 else
1213 open c_pre_payment_method (ln_prepay_action_id);
1214 loop
1215 /* fetch all the pre payment records for the asssignment
1216 other than 3rd party payment */
1217 fetch c_pre_payment_method into ln_pre_payment_id;
1218
1219 if c_pre_payment_method%notfound then
1220 exit;
1221 end if;
1222
1223 lv_payment_status := ltrim(rtrim(
1224 pay_assignment_actions_pkg.get_payment_status_code
1225 (ln_prepay_action_id,
1226 ln_pre_payment_id)));
1227
1228 if lv_payment_status <> 'P' then
1229 lv_return_flag := 'N';
1230 exit;
1231 else
1232 lv_return_flag := 'Y';
1233 end if;
1234
1235 end loop;
1236 close c_pre_payment_method;
1237
1238 end if;
1239 close c_check_for_reversal;
1240
1241 /* Modifications for the bug 8303577 starts */
1242 -- Commented for bug 8643214
1243 /* open c_get_localization(p_assignment_id);
1244 hr_utility.trace('Inside new get_localization ');
1245 fetch c_get_localization into ln_localization_code; */
1246 if (ln_localization_code = 'GB') then
1247 open c_get_completion_status(p_assignment_action_id);
1248 lv_completion_status := 0;
1249 fetch c_get_completion_status into lv_completion_status;
1250 hr_utility.trace('Inside c_get_completion_status ');
1251 hr_utility.trace('lv_completion_status '||lv_completion_status);
1252 if (lv_completion_status = 0) then
1253 hr_utility.trace('Inside c_get_completion_status if condition');
1254 return('N');
1255 end if;
1256 close c_get_completion_status;
1257 end if;
1258 -- Commented for bug 8643214
1259 -- close c_get_localization;
1260 /* Modifications for the bug 8303577 ends */
1261
1262 hr_utility.trace('lv_return_flag='||lv_return_flag);
1263 hr_utility.trace('Leaving check_emp_personal_payment');
1264
1265 return lv_return_flag;
1266
1267 END check_emp_personal_payment;
1268
1269
1270
1271 /*****************************************************************************
1272 ** Name: FUNCTION check_emp_personal_payment
1273 ** Arguments: p_assignment_id => Assignemnt ID
1274 ** p_payroll_id => Payroll ID
1275 ** p_assignment_action_id => Prepayment Action
1276 ** p_effective_date => Payment Date
1277 ** Description: Overloaded function without the parameter for
1278 ** time_period_id.
1279 **
1280 ** As time_period_id is not passed, the function expects the
1281 ** prepayment action_id to be passed to it. It then gets the
1282 ** max date_earned for the run locked by the prepayment process
1283 ** and then gets the corresponding time_period_id
1284 ** This is passed to the overloaded function which checks for
1285 ** Payslip view date
1286 ** If prepayment action is not passed but instead archive or
1287 ** run action is passed, the main function will handle it
1288 *****************************************************************************/
1289 FUNCTION check_emp_personal_payment(
1290 p_assignment_id number
1291 ,p_payroll_id number
1292 ,p_assignment_action_id number
1293 ,p_effective_date date
1294 )
1295 RETURN VARCHAR2 IS
1296
1297 cursor c_get_date_earned(cp_assignment_action_id in number) is
1298 select nvl(max(ppa.date_earned), max(ppa.effective_date))
1299 from pay_payroll_actions ppa
1300 ,pay_assignment_actions paa
1301 ,pay_action_interlocks pai
1302 where ppa.payroll_action_id = paa.payroll_action_id
1303 and pai.locked_action_id = paa.assignment_action_id
1304 and pai.locking_action_id = cp_assignment_action_id
1305 and ppa.action_type in ('R', 'Q', 'B', 'V');
1306
1307 cursor c_time_period(cp_payroll_id in number
1308 ,cp_date_earned in date) is
1309 select ptp.time_period_id
1310 from per_time_periods ptp
1311 where cp_date_earned between ptp.start_date
1312 and ptp.end_Date
1313 and ptp.payroll_id = cp_payroll_id;
1314
1315 ld_earned_date DATE;
1316 ln_time_period_id NUMBER;
1317
1318 BEGIN
1319
1320 hr_utility.trace('Entering check_emp_personal_payment without time period');
1321 open c_get_date_earned(p_assignment_action_id);
1322 fetch c_get_date_earned into ld_earned_date;
1323 if c_get_date_earned%found then
1324 open c_time_period(p_payroll_id, ld_earned_date);
1325 fetch c_time_period into ln_time_period_id;
1326 close c_time_period;
1327 end if;
1328 close c_get_date_earned;
1329
1330 hr_utility.trace('ln_time_period_id='||ln_time_period_id);
1331 return(check_emp_personal_payment(
1332 p_assignment_id => p_assignment_id
1333 ,p_payroll_id => p_payroll_id
1334 ,p_time_period_id => ln_time_period_id
1335 ,p_assignment_action_id => p_assignment_action_id
1336 ,p_effective_date => p_effective_date));
1337
1338 END check_emp_personal_payment;
1339
1340
1341 /************************************************************
1342 Function gets the proposed employee salary from
1343 per_pay_proposals. If the Salary Proposal is not specified
1344 then it checks the Salary Basis for the employee, find out
1345 the element associated with the Salary Basis and get the
1346 value from the run results for the given period.
1347 If the element associated with the Salary Basis is Regular
1348 wages, then we get the value for input value of 'Rate'
1349 ************************************************************/
1350 FUNCTION get_proposed_emp_salary (
1351 p_assignment_id in number
1352 ,p_pay_basis_id in number
1353 ,p_pay_bases_name in varchar2
1354 ,p_period_start_date in date
1355 ,p_period_end_date in date
1356 )
1357 RETURN VARCHAR2 IS
1358
1359 cursor c_salary_proposal (cp_assignment_id in number,
1360 cp_period_start_date in date,
1361 cp_period_end_date in date) is
1362 select ppp.proposed_salary_n
1363 from per_pay_proposals ppp
1364 where ppp.assignment_id = cp_assignment_id
1365 and ppp.change_date =
1366 (select max(change_date)
1367 from per_pay_proposals ppp1
1368 where ppp1.assignment_id = cp_assignment_id
1369 and ppp1.approved = 'Y'
1370 and ppp1.change_date <= cp_period_end_date);
1371
1372
1373 cursor c_bases_element (cp_pay_basis_id in number,
1374 cp_period_to_date in date) is
1375 select piv.element_type_id, piv.input_value_id
1376 from pay_input_values_f piv,
1377 per_pay_bases ppb
1378 where ppb.pay_basis_id = cp_pay_basis_id
1379 and ppb.input_value_id = piv.input_value_id
1380 and cp_period_to_date between piv.effective_start_date
1381 and piv.effective_end_date;
1382
1383 cursor c_regular_salary (cp_input_value_id in number,
1384 cp_assignment_id in number,
1385 cp_period_to_date in date ) is
1386 select prrv.result_value
1387 from pay_run_results prr,
1388 pay_run_result_values prrv,
1389 pay_input_values_f piv,
1390 pay_assignment_actions paa,
1391 pay_payroll_actions ppa
1392 where prr.element_type_id = piv.element_type_id
1393 and prr.run_result_id = prrv.run_result_id
1394 and prr.source_type = 'E'
1395 and piv.input_value_id = prrv.input_value_id
1396 and piv.input_value_id = cp_input_value_id
1397 and ppa.effective_date between piv.effective_start_date
1398 and piv.effective_end_date
1399 and paa.assignment_action_id = prr.assignment_action_id
1400 and paa.assignment_id = cp_assignment_id
1401 and ppa.payroll_action_id = paa.payroll_action_id
1402 and ppa.effective_date = cp_period_to_date;
1403
1404 cursor c_hourly_salary (cp_element_type_id in number,
1405 cp_input_value_name in varchar2,
1406 cp_assignment_id in number,
1407 cp_period_to_date in date ) is
1408 select prrv.result_value
1409 from pay_run_results prr,
1410 pay_run_result_values prrv,
1411 pay_input_values_f piv,
1412 pay_assignment_actions paa,
1413 pay_payroll_actions ppa
1414 where prr.element_type_id = piv.element_type_id
1415 and prr.run_result_id = prrv.run_result_id
1416 and prr.source_type = 'E'
1417 and piv.input_value_id = prrv.input_value_id
1418 and piv.element_type_id = cp_element_type_id
1419 and piv.name = cp_input_value_name
1420 and ppa.effective_date between piv.effective_start_date --Bug 3350023
1421 and piv.effective_end_date
1422 and paa.assignment_action_id = prr.assignment_action_id
1423 and paa.assignment_id = cp_assignment_id
1424 and ppa.payroll_action_id = paa.payroll_action_id
1425 and ppa.effective_date = cp_period_to_date;
1426
1427 ln_element_type_id number;
1428 ln_input_value_id number;
1429 ln_proposed_salary number;
1430
1431 BEGIN
1432
1433 open c_salary_proposal(p_assignment_id,
1434 p_period_start_date,
1435 p_period_end_date);
1436 fetch c_salary_proposal into ln_proposed_salary;
1437 if c_salary_proposal%notfound then
1438 open c_bases_element(p_pay_basis_id, p_period_end_date);
1439 fetch c_bases_element into ln_element_type_id, ln_input_value_id;
1440 if c_bases_element%found then
1441 if p_pay_bases_name <> 'HOURLY' then
1442 open c_regular_salary(ln_input_value_id,
1443 p_assignment_id,
1444 p_period_end_date);
1445 fetch c_regular_salary into ln_proposed_salary;
1446 if c_regular_salary%notfound then
1447 ln_proposed_salary := 0;
1448 end if;
1449 close c_regular_salary;
1450 else
1451 open c_hourly_salary(ln_element_type_id,
1452 'Rate',
1453 p_assignment_id,
1454 p_period_end_date);
1455 fetch c_hourly_salary into ln_proposed_salary;
1456 if c_hourly_salary%notfound then
1457 ln_proposed_salary := 0;
1458 end if;
1459 close c_hourly_salary;
1460 end if;
1461 end if;
1462 close c_bases_element;
1463
1464 end if;
1465 close c_salary_proposal;
1466
1467 return (ln_proposed_salary);
1468
1469 END get_proposed_emp_salary;
1470
1471
1472 /************************************************************
1473 Gets the Annualized factor for the Payroll
1474 i.e. frequency of the Payroll
1475 e.g. Week = 52
1476 Semi-Month = 24
1477 Month = 12
1478 Hourly = No of working hours/day * 365
1479 No of working hours/week * 52
1480 No of working hours/month * 12
1481 No of working hours/year * 1
1482 ************************************************************/
1483 FUNCTION get_emp_annualization_factor (
1484 p_pay_basis_id in number
1485 ,p_period_type in varchar2
1486 ,p_pay_bases_name in varchar2
1487 ,p_assignment_id in number
1488 ,p_period_end_date in date
1489 )
1490 return number is
1491
1492 cursor c_salary_details (cp_pay_basis_id in number) is
1493 select ppb.pay_annualization_factor
1494 from per_pay_bases ppb
1495 where ppb.pay_basis_id = cp_pay_basis_id;
1496
1497 cursor c_payroll (cp_period_type in varchar2) is
1498 select ptpt.number_per_fiscal_year
1499 from per_time_period_types ptpt
1500 where ptpt.period_type = cp_period_type;
1501
1502 ln_pay_annualization_factor number;
1503
1504 BEGIN
1505
1506 open c_salary_details(p_pay_basis_id);
1507 fetch c_salary_details into ln_pay_annualization_factor;
1508 if c_salary_details%found then
1509
1510 if p_pay_bases_name ='PERIOD' and
1511 ln_pay_annualization_factor is null then
1512
1513 open c_payroll(p_period_type);
1514 fetch c_payroll into ln_pay_annualization_factor;
1515 close c_payroll;
1516
1517 elsif p_pay_bases_name = 'HOURLY' and
1518 (p_assignment_id is not null and p_period_end_date is not null) then
1519
1520 ln_pay_annualization_factor :=
1521 pay_us_employee_payslip_web.get_asgn_annual_hours
1522 (p_assignment_id,
1523 p_period_end_date);
1524 end if;
1525 end if;
1526 close c_salary_details;
1527
1528 return (ln_pay_annualization_factor);
1529
1530 END get_emp_annualization_factor;
1531
1532
1533 /************************************************************
1534 The function gets the annual working hours for an assignment.
1535 The function looks for Standarg Working Conditions for an
1536 assignment. If is has not been specified then it gets the
1537 information for the assignment in the following order.
1538 Assignment
1539 Position
1540 Organization
1541 Business Group
1542 ************************************************************/
1543 FUNCTION get_asgn_annual_hours (
1544 p_assignment_id in number
1545 ,p_period_end_date in date
1546 )
1547 RETURN NUMBER IS
1548
1549 cursor c_get_asg_hours (cp_assignment_id in number,
1550 cp_period_end_date in date) is
1551 select paf.normal_hours,
1552 decode(paf.frequency,'Y', 1,
1553 'M', 12,
1554 'W', 52,
1555 'D', 365, 1)
1556 from per_assignments_f paf
1557 where paf.assignment_id = cp_assignment_id
1558 and cp_period_end_date between paf.effective_start_date
1559 and paf.effective_end_date;
1560
1561 cursor c_get_pos_hours (cp_assignment_id in number,
1562 cp_period_end_date in date) is
1563 select pos.working_hours,
1564 decode(pos.frequency, 'Y', 1,
1565 'M', 12,
1566 'W', 52,
1567 'D', 365, 1)
1568 from per_positions pos,
1569 per_assignments_f paf
1570 where paf.assignment_id = cp_assignment_id
1571 and cp_period_end_date between paf.effective_start_date
1572 and paf.effective_end_date
1573 and paf.position_id = pos.position_id;
1574
1575 cursor c_get_org_hours (cp_assignment_id in number,
1576 cp_period_end_date in date) is
1577 select pou.working_hours,
1578 decode(pou.frequency, 'Y', 1,
1579 'M', 12,
1580 'W', 52,
1581 'D', 365, 1)
1582 from per_organization_units pou,
1583 per_assignments_f paf
1584 where paf.assignment_id = cp_assignment_id
1585 and cp_period_end_date between paf.effective_start_date
1586 and paf.effective_end_date
1587 and paf.organization_id = pou.organization_id;
1588
1589 cursor c_get_bus_hours (cp_assignment_id in number,
1590 cp_period_end_date in date) is
1591 select pbg.working_hours,
1592 decode(pbg.frequency, 'Y', 1,
1593 'M', 12,
1594 'W', 52,
1595 'D', 365, 1)
1596 from per_business_groups pbg,
1597 per_assignments_f paf
1598 where paf.assignment_id = cp_assignment_id
1599 and cp_period_end_date between paf.effective_start_date
1600 and paf.effective_end_date
1601 and paf.business_group_id = pbg.business_group_id;
1602
1603 ln_hours number;
1604 ln_frequency number;
1605 ln_hours_per_year number;
1606
1607 BEGIN
1608
1609 open c_get_asg_hours (p_assignment_id,
1610 p_period_end_date);
1611 fetch c_get_asg_hours into ln_hours,ln_frequency;
1612
1613 if c_get_asg_hours%found and ln_hours is not null then
1614 close c_get_asg_hours;
1615 else
1616 close c_get_asg_hours;
1617 open c_get_pos_hours (p_assignment_id,
1618 p_period_end_date);
1619 fetch c_get_pos_hours into ln_hours, ln_frequency;
1620
1621 if c_get_pos_hours%found and ln_hours is not null then
1622 close c_get_pos_hours;
1623 else
1624 close c_get_pos_hours;
1625 open c_get_org_hours (p_assignment_id,
1626 p_period_end_date);
1627 fetch c_get_org_hours into ln_hours, ln_frequency;
1628
1629 if c_get_org_hours%found and ln_hours is not null then
1630 close c_get_org_hours;
1631 open c_get_bus_hours (p_assignment_id,
1632 p_period_end_date);
1633 fetch c_get_bus_hours into ln_hours, ln_frequency;
1634 close c_get_bus_hours;
1635 end if;
1636 end if;
1637
1638 end if;
1639
1640 ln_hours_per_year := nvl(ln_hours, 0) * ln_frequency;
1641
1642 return (ln_hours_per_year);
1643
1644 END get_asgn_annual_hours;
1645
1646 /************************************************************
1647 The function gets the School District Name for the passed
1648 Jurisdiction Code.
1649 The name is being reteived from the School Dsts table
1650 depending on the following :
1651 - get the School District Name from PAY_US_COUNTY_SCHOOL_DSTS.
1652 - If not found then get the School District Name from
1653 PAY_US_CITY_SCHOOL_DSTS.
1654 If the School Dsts Code passed is not in the table then
1655 NULL is passed.
1656 ************************************************************/
1657 Function get_school_dsts_name
1658 (p_jurisdiction_code in varchar2)
1659 RETURN varchar2 is
1660
1661 Cursor c_city_school_dsts
1662 (cp_jurisdiction_code in varchar2) is
1663 select initcap(pcisd.school_dst_name)
1664 from pay_us_city_school_dsts pcisd
1665 where pcisd.state_code = substr(cp_jurisdiction_code,1,2)
1666 and pcisd.school_dst_code = substr(cp_jurisdiction_code,4);
1667
1668 Cursor c_county_school_dsts
1669 (cp_jurisdiction_code in varchar2) is
1670 select initcap(pcosd.school_dst_name)
1671 from pay_us_county_school_dsts pcosd
1672 where pcosd.state_code = substr(cp_jurisdiction_code,1,2)
1673 and pcosd.school_dst_code = substr(cp_jurisdiction_code,4);
1674
1675 lv_school_dst_name varchar2(100);
1676
1677 BEGIN
1678
1679 open c_county_school_dsts (p_jurisdiction_code);
1680 fetch c_county_school_dsts into lv_school_dst_name;
1681 if c_county_school_dsts%notfound then
1682 open c_city_school_dsts (p_jurisdiction_code);
1683 fetch c_city_school_dsts into lv_school_dst_name;
1684 close c_city_school_dsts;
1685 end if;
1686 close c_county_school_dsts;
1687
1688 return (lv_school_dst_name);
1689
1690 END get_school_dsts_name;
1691
1692
1693 /************************************************************
1694
1695 Name : get_check_number
1696 Purpose : This returns the check number
1697 Arguments : Pre_payment_id and pre_payment assignment_action.
1698 Notes :
1699 *****************************************************************/
1700 FUNCTION get_check_number(p_pre_payment_assact in number
1701 ,p_pre_payment_id in number)
1702 RETURN varchar2 is
1703
1704 lv_check_number varchar2(60);
1705
1706 Cursor c_check_number(cp_pre_payment_action in number
1707 ,cp_pre_payment_id in number) is
1708 select decode(ppa_pymt.action_type,
1709 'M', to_char(NVL(ppp.source_action_id,cp_pre_payment_action)),
1710 paa_pymt.serial_number)
1711 from pay_pre_payments ppp,
1712 pay_assignment_actions paa_pymt,
1713 pay_payroll_actions ppa_pymt,
1714 pay_action_interlocks pai
1715 where pai.locked_action_id = cp_pre_payment_action
1716 and paa_pymt.assignment_action_id = pai.locking_action_id
1717 and ppa_pymt.payroll_action_id = paa_pymt.payroll_action_id
1718 and ppa_pymt.action_type in ('M','H', 'E')
1719 and paa_pymt.pre_payment_id = cp_pre_payment_id
1720 and ppp.pre_payment_id = paa_pymt.pre_payment_id
1721 and not exists (
1722 select 1
1723 from pay_payroll_actions ppa,
1724 pay_assignment_actions paa,
1725 pay_action_interlocks pai_void
1726 /* Assignment Action of Payment Type - NACHA/Check */
1727 where pai_void.locked_action_id = paa_pymt.assignment_action_id --Void
1728 /* Check if the locking is that of Void Pymt */
1729 and pai_void.locking_action_id = paa.assignment_action_id
1730 and ppa.payroll_action_id = paa.payroll_action_id
1731 and paa.action_status = 'C'
1732 and ppa.action_status = 'C'
1733 and ppa.action_type = 'D');
1734
1735 BEGIN
1736
1737 open c_check_number(p_pre_payment_assact, p_pre_payment_id);
1738 fetch c_check_number into lv_check_number;
1739 if c_check_number%notfound then
1740 lv_check_number := null;
1741 end if;
1742 close c_check_number;
1743
1744 RETURN lv_check_number;
1745
1746 END get_check_number;
1747
1748 /************************************************************
1749 Name : get_format_value
1750 purpuse : given a value, it formats the value to a given
1751 currency_code and precision.
1752 arguments : p_business_group_id, p_value
1753 notes :
1754 *************************************************************/
1755 FUNCTION get_format_value(p_business_group_id in number,
1756 p_value in number)
1757 RETURN varchar2 IS
1758
1759 lv_formatted_number varchar2(50);
1760
1761 CURSOR c_currency_code is
1762 select hoi.org_information10
1763 from hr_organization_units hou,
1764 hr_organization_information hoi
1765 where hou.organization_id = p_business_group_id /* Bug 3487250 */
1766 and hou.organization_id = hoi.organization_id
1767 and hoi.org_information_context = 'Business Group Information';
1768
1769 BEGIN
1770 IF g_currency_code is null THEN
1771 OPEN c_currency_code;
1772 FETCH c_currency_code into g_currency_code;
1773 CLOSE c_currency_code;
1774 END IF;
1775 IF g_currency_code is not null THEN
1776 lv_formatted_number := to_char(p_value,
1777 fnd_currency.get_format_mask(
1778 g_currency_code,40));
1779 ELSE
1780 lv_formatted_number := p_value;
1781 END IF;
1782
1783 return lv_formatted_number;
1784
1785 EXCEPTION
1786 when others then
1787 return p_value;
1788 END get_format_value;
1789
1790 /************************************************************
1791 Name : format_to_date
1792 Purpuse : The function formats the value in date format
1793 Arguments : p_value
1794 Notes :
1795 *************************************************************/
1796 FUNCTION format_to_date(p_char_date in varchar2)
1797 RETURN date IS
1798
1799 ld_return_date DATE;
1800
1801 BEGIN
1802 if length(p_char_date) = 19 then
1803 ld_return_date := fnd_date.canonical_to_date(p_char_date);
1804 else
1805 begin
1806 ld_return_date := fnd_date.chardate_to_date(p_char_date);
1807
1808 exception
1809 when others then
1810 ld_return_date := null;
1811 end;
1812
1813 end if;
1814
1815 return(ld_return_date);
1816
1817 END format_to_date;
1818
1819 /************************************************************
1820 Name : get_doc_eit
1821 Purpuse : returns whether any documents should be printed
1822 or viewed online.
1823 Arguments : p_doc_type = (i.e PAYSLIP, W4...)
1824 p_mode = PRINT or ONLINE
1825 p_level = PERSON, ORGANIZATION, BUSINESS GROUP,
1826 LOCATION
1827 p_id = appropriate id for p_level.
1828 person_id, organization_id, business_group_id,
1829 location_id
1830 Notes : Priority for levels (high to low)
1831 Person
1832 Location
1833 Organization
1834 Business Group
1835 *************************************************************/
1836 FUNCTION get_doc_eit(p_doc_type in varchar,
1837 p_mode in varchar,
1838 p_level in varchar,
1839 p_id in number,
1840 p_effective_date date)
1841 RETURN varchar2 IS
1842
1843 CURSOR get_person_eit (l_person_id Number) IS
1844 select pei_information2, pei_information3
1845 from per_people_extra_info
1846 where information_type = 'HR_SELF_SERVICE_PER_PREFERENCE'
1847 and person_id = l_person_id
1848 and pei_information1 = upper(p_doc_type);
1849
1850 CURSOR get_loc_eit (l_location_id number) IS
1851 select lei_information2, lei_information3
1852 from hr_location_extra_info
1853 where information_type = 'HR_SELF_SERVICE_LOC_PREFERENCE'
1854 and location_id = l_location_id
1855 and lei_information1 = upper(p_doc_type);
1856
1857 CURSOR get_org_eit (l_organization_id number) IS
1858 select org_information2,org_information3
1859 from hr_organization_information
1860 where org_information_context = 'HR_SELF_SERVICE_ORG_PREFERENCE'
1861 and org_information1 = upper(p_doc_type)
1862 and organization_id = l_organization_id;
1863
1864 CURSOR get_bg_eit (l_business_group_id number) IS
1865 select org_information2, org_information3
1866 from hr_organization_information hoi
1867 where hoi.organization_id = l_business_group_id
1868 and hoi.org_information_context = 'HR_SELF_SERVICE_BG_PREFERENCE'
1869 and hoi.org_information1 = upper(p_doc_type) ;
1870
1871 /* adding a join to per_periods_of_service. If the employee is terminated then
1872 the person does not have access to online doc, so we should always return a
1873 N.
1874
1875 -- RLN Bug 9074420 added or condition for bug 10628755 11730301 --*/
1876 CURSOR get_person_info(l_assignment_id number) IS
1877 select paf.business_group_id, paf.organization_id,
1878 paf.location_id, paf.person_id
1879 from per_assignments_f paf, per_periods_of_service pps
1880 where paf.assignment_id = l_assignment_id
1881 and p_effective_date between paf.effective_start_date
1882 and paf.effective_end_date
1883 and pps.period_of_service_id = paf.period_of_service_id
1884 and pps.actual_termination_date is null;
1885
1886 /* --TERM cursor RLN 11730301 -- */
1887 CURSOR get_person_info_t(l_assignment_id number) IS
1888 select paf.business_group_id, paf.organization_id,
1889 paf.location_id, paf.person_id
1890 from per_assignments_f paf, per_periods_of_service pps
1891 where paf.assignment_id = l_assignment_id --13084713 Removed eff. date from where clause
1892 and pps.period_of_service_id = paf.period_of_service_id
1893 and pps.actual_termination_date between paf.effective_start_date
1894 and paf.effective_end_date;
1895
1896 l_mesg varchar2(250);
1897
1898 l_online varchar2(1);
1899 l_print varchar2(1);
1900
1901 l_value varchar2(1);
1902 l_count number;
1903 l_rowcount number;
1904 l_bg_id number;
1905 l_org_id number;
1906 l_loc_id number;
1907 l_person_id number;
1908
1909 l_location_cache varchar2(10);
1910 l_org_cache varchar2(10);
1911 l_bg_cache varchar2(10);
1912 l_actual_termination_date date ;
1913 l_term_diff number ; --Bug# 14682114
1914 l_time_period_id number; --Bug# 14682114
1915
1916 BEGIN
1917 l_mesg := 'pay_us_employee_payslip_web.get_doc_eit';
1918 hr_utility.set_location(l_mesg,5);
1919 l_location_cache := 'NOT FOUND';
1920 l_org_cache := 'NOT FOUND';
1921 l_bg_cache := 'NOT FOUND';
1922 l_rowCount := pay_us_employee_payslip_web.eit_tab.count;
1923
1924 IF upper(p_level) = 'ASSIGNMENT' THEN
1925 hr_utility.set_location(l_mesg, 10);
1926
1927 /* --RLN Check actual_termination_date 11730301 --- */
1928 /*--13084713 Added Max for the termination date and Removed eff. date from where clause */
1929 Select nvl(max(pps.actual_termination_date), to_date('12/31/4712','mm/dd/yyyy'))
1930 into l_actual_termination_date
1931 from per_periods_of_service pps,
1932 per_assignments_f paf
1933 where paf.assignment_id = p_id
1934 and pps.period_of_service_id = paf.period_of_service_id;
1935
1936 IF l_actual_termination_date = to_date('12/31/4712','mm/dd/yyyy')
1937 THEN
1938 OPEN get_person_info(p_id);
1939 FETCH get_person_info INTO l_bg_id, l_org_id, l_loc_id, l_person_id;
1940 CLOSE get_person_info;
1941 ELSE
1942 /* Bug# 14682114 - Primarily incase ATD is not null, we need to satisfy two cases here
1943 a. In case of a terminated employee Process should not be caring the SS preferences.(IF Case)
1944 b. In case of future terminated employees process should care the Self Service Preferences.(Else case)
1945 c. Here termination is not as per the SYSDATE but relative to the payroll(cycle) run date.
1946 */
1947 select max(time_period_id)
1948 into l_time_period_id
1949 from per_time_periods ptp,
1950 per_all_assignments_f paf
1951 where ptp.payroll_id= paf.payroll_id
1952 and paf.assignment_id=p_id
1953 and p_effective_date between ptp.start_date and ptp.end_date;
1954
1955 select nvl((select 1
1956 from per_time_periods ptp
1957 where time_period_id = l_time_period_id
1958 and( l_actual_termination_date between ptp.start_date and ptp.end_date
1959 or l_actual_termination_date < ptp.start_date)),0) into l_term_diff from dual;
1960
1961 IF nvl(l_term_diff,0) = 0 THEN
1962 /* Calling this cursor to retain the existing behavior as that will return no recs and hence
1963 takes the default rout i.e Y*/
1964
1965 OPEN get_person_info_t(p_id);
1966 FETCH get_person_info_t INTO l_bg_id, l_org_id, l_loc_id, l_person_id;
1967 CLOSE get_person_info_t;
1968 ELSE
1969 OPEN get_person_info(p_id);
1970 FETCH get_person_info INTO l_bg_id, l_org_id, l_loc_id, l_person_id;
1971 CLOSE get_person_info;
1972 END IF;
1973 END IF;
1974
1975 OPEN get_person_eit(l_person_id);
1976 FETCH get_person_eit INTO l_online, l_print;
1977
1978 IF get_person_eit%FOUND THEN
1979 If p_mode = 'PRINT' THEN
1980 l_value := l_print;
1981 ELSIF p_mode = 'ONLINE' THEN
1982 l_value := l_online;
1983 END IF;
1984 ELSE /* Person Level EIT not found, look for location level */
1985 OPEN get_loc_eit(l_loc_id);
1986 FETCH get_loc_eit INTO l_online, l_print;
1987
1988 IF get_loc_eit%FOUND THEN
1989 IF p_mode = 'ONLINE' THEN
1990 l_value := l_online;
1991 ELSIF p_mode = 'PRINT' THEN
1992 l_value := l_print;
1993 END IF;
1994 ELSE /* Location Level EIT not found */
1995 OPEN get_org_eit(l_org_id);
1996 FETCH get_org_eit into l_online, l_print;
1997 IF get_org_eit%FOUND THEN
1998 IF p_mode = 'ONLINE' THEN
1999 l_value := l_online;
2000 ELSIF p_mode = 'PRINT' THEN
2001 l_value := l_print;
2002 END IF;
2003 ELSE /* Organization Level not found */
2004 OPEN get_bg_eit(l_bg_id);
2005 FETCH get_bg_eit into l_online,l_print;
2006 IF get_bg_eit%FOUND THEN
2007 IF p_mode = 'ONLINE' THEN
2008 l_value := l_online;
2009 ELSIF p_mode = 'PRINT' THEN
2010 l_value := l_print;
2011 END IF;
2012 ELSE
2013 l_value := 'Y';
2014 END IF; /* Bg not found */
2015 CLOSE get_bg_eit;
2016 END IF; /* Org not found */
2017 CLOSE get_org_eit;
2018 END IF; /* Loc not found */
2019 CLOSE get_loc_eit;
2020 END IF; /* Person not found */
2021 CLOSE get_person_eit;
2022
2023 return l_value;
2024
2025 END IF; /* p_level = assignment */
2026
2027 IF upper(p_level) = 'PERSON' THEN
2028 hr_utility.set_location(l_mesg,20);
2029 OPEN get_person_eit(p_id);
2030 FETCH get_person_eit INTO l_online, l_print;
2031
2032 IF get_person_eit%FOUND THEN
2033 If p_mode = 'PRINT' THEN
2034 l_value := l_print;
2035 ELSIF p_mode = 'ONLINE' THEN
2036 l_value := l_online;
2037 END IF;
2038 ELSE
2039 l_value := 'Y';
2040 END IF;
2041
2042 CLOSE get_person_eit;
2043 RETURN l_value;
2044 END IF;
2045
2046 IF upper(p_level) = 'LOCATION' THEN
2047 hr_utility.set_location(l_mesg,30);
2048 hr_utility.trace('Before LOOP l_location_cache = '||l_location_cache);
2049 IF (l_rowCount > 0) THEN
2050 FOR i in pay_us_employee_payslip_web.eit_tab.first ..
2051 pay_us_employee_payslip_web.eit_tab.last
2052 LOOP
2053 IF (pay_us_employee_payslip_web.eit_tab(i).t_id = p_id AND
2054 pay_us_employee_payslip_web.eit_tab(i).t_level = 'Location') THEN
2055
2056 l_location_cache := 'FOUND';
2057
2058 IF p_mode = 'ONLINE' THEN
2059 l_value := pay_us_employee_payslip_web.eit_tab(i).t_online;
2060 ELSIF p_mode = 'PRINT' THEN
2061 l_value := pay_us_employee_payslip_web.eit_tab(i).t_print;
2062 END IF;
2063 END IF;
2064 END LOOP;
2065 END IF; -- l_rowCount > 0
2066 hr_utility.trace('AFter LOOP l_location_cache = '||l_location_cache);
2067
2068 IF l_location_cache = 'NOT FOUND' THEN
2069 hr_utility.set_location(l_mesg,40);
2070 ---- Location Level is not cached so find it ----
2071
2072 OPEN get_loc_eit(p_id);
2073 FETCH get_loc_eit INTO l_online, l_print;
2074 hr_utility.trace('l_online = '||l_online);
2075 hr_utility.trace('l_print = '||l_print);
2076 l_count := pay_us_employee_payslip_web.eit_tab.count + 1 ;
2077 pay_us_employee_payslip_web.eit_tab(l_count).t_id := p_id;
2078 pay_us_employee_payslip_web.eit_tab(l_count).t_level := 'Location';
2079
2080 IF get_loc_eit%FOUND THEN
2081 pay_us_employee_payslip_web.eit_tab(l_count).t_online := l_online;
2082 pay_us_employee_payslip_web.eit_tab(l_count).t_print := l_print;
2083 ELSE
2084 pay_us_employee_payslip_web.eit_tab(l_count).t_online := 'Y';
2085 pay_us_employee_payslip_web.eit_tab(l_count).t_print := 'Y';
2086 END IF;
2087
2088 hr_utility.trace('eit_tab(l_count).t_online = '||
2089 pay_us_employee_payslip_web.eit_tab(l_count).t_online);
2090 hr_utility.trace('eit_tab(l_count).t_print = '||
2091 pay_us_employee_payslip_web.eit_tab(l_count).t_print);
2092 IF p_mode = 'ONLINE' THEN
2093 l_value := pay_us_employee_payslip_web.eit_tab(l_count).t_online;
2094 ELSIF p_mode = 'PRINT' THEN
2095 l_value := pay_us_employee_payslip_web.eit_tab(l_count).t_print;
2096 END IF;
2097 CLOSE get_loc_eit;
2098 END IF;
2099 return l_value;
2100 END IF; -- if p_level = Location
2101
2102 IF upper(p_level) = 'ORGANIZATION' THEN
2103 hr_utility.set_location(l_mesg,50);
2104 IF (l_rowCount > 0) THEN
2105 FOR i in pay_us_employee_payslip_web.eit_tab.first ..
2106 pay_us_employee_payslip_web.eit_tab.last
2107 LOOP
2108 IF (pay_us_employee_payslip_web.eit_tab(i).t_id = p_id AND
2109 pay_us_employee_payslip_web.eit_tab(i).t_level
2110 = 'Organization') THEN
2111 l_org_cache := 'FOUND';
2112 IF p_mode = 'ONLINE' THEN
2113 l_value := pay_us_employee_payslip_web.eit_tab(i).t_online;
2114 ELSIF p_mode = 'PRINT' THEN
2115 l_value := pay_us_employee_payslip_web.eit_tab(i).t_print;
2116 END IF;
2117 END IF;
2118 END LOOP;
2119 END IF;
2120
2121 ---- Organization Level is not cached so find it ----
2122
2123 IF l_org_cache = 'NOT FOUND' THEN
2124 hr_utility.trace('Org cache NOT FOUND');
2125 hr_utility.set_location(l_mesg,60);
2126 OPEN get_org_eit(p_id);
2127 FETCH get_org_eit INTO l_online, l_print;
2128 l_count := pay_us_employee_payslip_web.eit_tab.count + 1 ;
2129 pay_us_employee_payslip_web.eit_tab(l_count).t_id := p_id;
2130 pay_us_employee_payslip_web.eit_tab(l_count).t_level := 'Organization';
2131
2132 IF get_org_eit%FOUND THEN
2133 pay_us_employee_payslip_web.eit_tab(l_count).t_online := l_online;
2134 pay_us_employee_payslip_web.eit_tab(l_count).t_print := l_print;
2135 ELSE
2136 pay_us_employee_payslip_web.eit_tab(l_count).t_online := 'Y';
2137 pay_us_employee_payslip_web.eit_tab(l_count).t_print := 'Y';
2138 END IF;
2139
2140 IF p_mode = 'PRINT' THEN
2141 l_value := pay_us_employee_payslip_web.eit_tab(l_count).t_print;
2142 ELSIF p_mode = 'ONLINE' THEN
2143 l_value := pay_us_employee_payslip_web.eit_tab(l_count).t_online;
2144 END IF;
2145 CLOSE get_org_eit;
2146 END IF;
2147 return l_value;
2148 END IF; --if p_level = Organization
2149
2150 --- So look for Cached Business Group EIT ---
2151
2152 IF upper(p_level) = 'BUSINESS GROUP' THEN
2153 hr_utility.set_location(l_mesg,70);
2154 IF (l_rowCount > 0) THEN
2155 FOR i in pay_us_employee_payslip_web.eit_tab.first ..
2156 pay_us_employee_payslip_web.eit_tab.last LOOP
2157 IF (pay_us_employee_payslip_web.eit_tab(i).t_id = p_id AND
2158 pay_us_employee_payslip_web.eit_tab(i).t_level = 'Business Group')
2159 THEN
2160 l_bg_cache := 'FOUND';
2161 IF p_mode = 'ONLINE' THEN
2162 l_value := pay_us_employee_payslip_web.eit_tab(i).t_online;
2163 ELSIF p_mode = 'PRINT' THEN
2164 l_value := pay_us_employee_payslip_web.eit_tab(i).t_print;
2165 END IF;
2166 END IF;
2167 END LOOP;
2168 END IF;
2169
2170 --- business Group Level EIT not cached ----
2171
2172 IF l_bg_cache = 'NOT FOUND' THEN
2173 hr_utility.set_location(l_mesg,80);
2174
2175 OPEN get_bg_eit(p_id);
2176 FETCH get_bg_eit INTO l_online, l_print;
2177 l_count := pay_us_employee_payslip_web.eit_tab.count + 1;
2178 pay_us_employee_payslip_web.eit_tab(l_count).t_id := p_id;
2179 pay_us_employee_payslip_web.eit_tab(l_count).t_level := 'Business Group';
2180
2181 IF get_bg_eit%FOUND THEN
2182 pay_us_employee_payslip_web.eit_tab(l_count).t_online := l_online;
2183 pay_us_employee_payslip_web.eit_tab(l_count).t_print := l_print;
2184 ELSE
2185 pay_us_employee_payslip_web.eit_tab(l_count).t_online := 'Y';
2186 pay_us_employee_payslip_web.eit_tab(l_count).t_print := 'Y';
2187 END IF;
2188
2189 IF p_mode = 'ONLINE' THEN
2190 l_value := pay_us_employee_payslip_web.eit_tab(l_count).t_online;
2191 ELSIF p_mode = 'PRINT' THEN
2192 l_value := pay_us_employee_payslip_web.eit_tab(l_count).t_print;
2193 END IF;
2194
2195 CLOSE get_bg_eit;
2196 END IF; -- bg_cache not found
2197 return l_value;
2198 END IF; -- p_level = Business Group
2199
2200 END get_doc_eit;
2201
2202 /*********************************************************************
2203 Name : get_jurisdiction_name
2204 Purpose : This function returns the name of the jurisdiction
2205 If Jurisdiction_code is like 'XX-000-0000' then
2206 it returns State Name from py_us_states
2207 If Jurisdiction_code is like 'XX-XXX-0000' then
2208 it returns County Name from paY_us_counties
2209 If Jurisdiction_code is like 'XX-XXX-XXXX' then
2210 it returns City Name from pay_us_city_name
2211 If Jurisdiction_code is like 'XX-XXXXX' then
2212 it returns School Name from pay_us_school_dsts
2213 In case jurisdiction code could not be found relevent
2214 table then NULL is returned.
2215 Arguments : p_jurisdiction_code
2216 Notes :
2217 *********************************************************************/
2218 FUNCTION get_jurisdiction_name(p_jurisdiction_code in varchar2)
2219
2220 RETURN VARCHAR2
2221 IS
2222
2223 cursor c_get_state(cp_state_code in varchar2) is
2224 select state_abbrev
2225 from pay_us_states
2226 where state_code = cp_state_code;
2227
2228 cursor c_get_county( cp_state_code in varchar2
2229 ,cp_county_code in varchar2
2230 ) is
2231 select county_name
2232 from pay_us_counties
2233 where state_code = cp_state_code
2234 and county_code = cp_county_code;
2235
2236 cursor c_get_city( cp_state_code in varchar2
2237 ,cp_county_code in varchar2
2238 ,cp_city_code in varchar2
2239 ) is
2240 select city_name
2241 from pay_us_city_names
2242 where state_code = cp_state_code
2243 and county_code = cp_county_code
2244 and city_code = cp_city_code
2245 and primary_flag = 'Y';
2246
2247 lv_state_code VARCHAR2(2);
2248 lv_county_code VARCHAR2(3);
2249 lv_city_code VARCHAR2(4);
2250 lv_jurisdiction_name VARCHAR2(240);
2251
2252 lv_procedure_name VARCHAR2(50);
2253 BEGIN
2254 lv_procedure_name := '.get_jurisdiction_name' ;
2255 lv_state_code := substr(p_jurisdiction_code,1,2);
2256 lv_county_code := substr(p_jurisdiction_code,4,3);
2257 lv_city_code := substr(p_jurisdiction_code,8,4);
2258 lv_jurisdiction_name := null;
2259 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2260
2261 if p_jurisdiction_code like '__-000-0000' then
2262 open c_get_state(lv_state_code);
2263 fetch c_get_state into lv_jurisdiction_name;
2264 close c_get_state;
2265 elsif p_jurisdiction_code like '__-___-0000' then
2266 open c_get_county(lv_state_code
2267 ,lv_county_code);
2268 fetch c_get_county into lv_jurisdiction_name;
2269 close c_get_county;
2270 elsif p_jurisdiction_code like '__-___-____' then
2271 open c_get_city( lv_state_code
2272 ,lv_county_code
2273 ,lv_city_code);
2274 fetch c_get_city into lv_jurisdiction_name;
2275 close c_get_city;
2276 elsif p_jurisdiction_code like '__-_____' then
2277 -- this is school district make a function call
2278 lv_jurisdiction_name
2279 := pay_us_employee_payslip_web.get_school_dsts_name(p_jurisdiction_code);
2280 end if;
2281
2282 hr_utility.set_location(gv_package || lv_procedure_name, 30);
2283 return (lv_jurisdiction_name);
2284 END get_jurisdiction_name;
2285
2286
2287 FUNCTION get_legislation_code( p_business_group_id in number)
2288 RETURN VARCHAR2 is
2289
2290 CURSOR cur_legislation_code is
2291 select
2292 org_information9
2293 from
2294 hr_organization_information
2295 where
2296 org_information_context = 'Business Group Information'
2297 and organization_id = p_business_group_id;
2298
2299 l_legislation_code hr_organization_information.org_information9%TYPE;
2300
2301 BEGIN
2302
2303 OPEN cur_legislation_code;
2304 FETCH cur_legislation_code
2305 INTO l_legislation_code;
2306
2307 IF cur_legislation_code%NOTFOUND THEN
2308 l_legislation_code := ' ';
2309 END IF;
2310
2311 CLOSE cur_legislation_code;
2312
2313 RETURN l_legislation_code;
2314
2315 END get_legislation_code;
2316
2317
2318 FUNCTION get_term_info (p_business_group_id number,
2319 p_person_id number,
2320 p_action_context_id number)
2321 /* for bug 4132132
2322 p_effective_start_date date,
2323 p_effective_end_date date) */
2324 RETURN varchar2 IS
2325
2326 CURSOR c_get_legislation_rule(p_legislation_code varchar2) is
2327 select rule_mode
2328 from pay_legislative_field_info plf
2329 WHERE validation_name = 'ITEM_PROPERTY'
2330 and rule_type = 'PAYSLIP_STOP_TERM_EMP'
2331 and field_name = 'CHOOSE_PAYSLIP'
2332 and legislation_code = p_legislation_code;
2333 --get_legislation_code(p_business_group_id);
2334
2335 cursor c_get_terminate_date is
2336 select actual_termination_date, pai.action_information16
2337 from per_periods_of_service pps,
2338 pay_action_information pai
2339 where pps.person_id = p_person_id
2340 and pai.action_context_id = p_action_context_id
2341 and pai.action_information_category = 'EMPLOYEE DETAILS'
2342 /* and fnd_date.canonical_to_date(pai.action_information11) = pps.date_start;*/
2343 and format_to_date(pai.action_information11) = pps.date_start;
2344
2345 /* Bug 3722370 - Introduced decode statement in WHERE clause */
2346 cursor c_get_term_details(p_actual_termination_date varchar2,
2347 p_time_period_id number) is
2348 /* Changed this for bug 4132132
2349 select 'Y'
2350 from per_periods_of_service pps
2351 where person_id = p_person_id
2352 and decode(actual_termination_date,NULL,date_start,p_effective_start_date)
2353 between date_start
2354 and nvl(actual_termination_date,p_effective_end_date) ;
2355 */
2356 /* don't show the payslip if the employee is terminated in the given pay period
2357 or prior to the given pay period */
2358 select 'N' from per_time_periods ptp
2359 where ptp.time_period_id = p_time_period_id
2360 and ( p_actual_termination_date between ptp.start_date
2361 and ptp.end_date
2362 or
2363 p_actual_termination_date < ptp.start_date);
2364 l_rule_mode varchar2(10);
2365 l_val varchar2(10);
2366 l_terminate_date date;
2367 l_time_period_id number;
2368
2369 BEGIN
2370
2371 l_val := 'Y' ;
2372 if g_legislation_code is null then
2373 g_legislation_code := get_legislation_code(p_business_group_id);
2374 end if;
2375
2376 if g_legislation_rule is null then
2377 open c_get_legislation_rule(g_legislation_code);
2378 fetch c_get_legislation_rule into l_rule_mode;
2379 close c_get_legislation_rule;
2380 end if;
2381
2382 if l_rule_mode = 'Y' then
2383
2384 open c_get_terminate_date;
2385 fetch c_get_terminate_date
2386 into l_terminate_date, l_time_period_id;
2387 close c_get_terminate_date;
2388
2389 if l_terminate_date IS NULL then
2390 return 'Y';
2391 else
2392 open c_get_term_details(l_terminate_date,l_time_period_id) ;
2393 fetch c_get_term_details into l_val;
2394 close c_get_term_details;
2395 if l_val is null then
2396 l_val := 'Y';
2397 end if;
2398 end if;
2399 else
2400 return 'Y';
2401 end if;
2402
2403 return l_val;
2404
2405 END get_term_info;
2406
2407
2408 FUNCTION get_meaning_payslip_label(p_leg_code VARCHAR2,
2409 p_lookup_code VARCHAR2)
2410 RETURN Varchar2 IS
2411 CURSOR csr_hr_lookup
2412 ( p_lookup_type VARCHAR2
2413 , p_lookup_code VARCHAR2
2414 )
2415 IS
2416 SELECT hr_general_utilities.Get_lookup_Meaning(p_lookup_type,p_lookup_code)
2417 FROM DUAL;
2418
2419 l_meaning hr_lookups.meaning%TYPE;
2420
2421 BEGIN
2422 OPEN csr_hr_lookup(p_leg_code||'_PAYSLIP_LABEL',p_leg_code||'_'||p_lookup_code);
2423 FETCH csr_hr_lookup INTO l_meaning;
2424 CLOSE csr_hr_lookup;
2425
2426 IF l_meaning IS NULL THEN
2427 OPEN csr_hr_lookup('PAYSLIP_LABEL',p_leg_code||'_'||p_lookup_code);
2428 FETCH csr_hr_lookup INTO l_meaning;
2429 CLOSE csr_hr_lookup;
2430 END IF;
2431
2432 return l_meaning;
2433 END get_meaning_payslip_label;
2434
2435
2436 /*********************************************************************
2437 Name : get_full_jurisdiction_name
2438 Purpose : This function returns the name of the jurisdiction
2439 If Jurisdiction_code is like 'XX-000-0000' then
2440 it returns "State Name" using function get_jurisdiction_name
2441 If Jurisdiction_code is like 'XX-XXX-0000' then
2442 it returns "State Name, County Name"
2443 using function get_jurisdiction_name
2444 If Jurisdiction_code is like 'XX-XXX-XXXX' then
2445 it returns "State Name, County Name, City Name"
2446 using function get_jurisdiction_name
2447 In case jurisdiction code could not be found relevent
2448 table then NULL is returned.
2449 Arguments : p_jurisdiction_code
2450 Notes :
2451 *********************************************************************/
2452 FUNCTION get_full_jurisdiction_name(p_jurisdiction_code in varchar2)
2453
2454 RETURN VARCHAR2
2455 IS
2456 lv_state_code VARCHAR2(2);
2457 lv_county_code VARCHAR2(3);
2458 lv_city_code VARCHAR2(4);
2459 lv_jurisdiction_name VARCHAR2(240);
2460 lv_procedure_name VARCHAR2(50);
2461
2462 lv_state_abbrev VARCHAR2(240);
2463 lv_county_name VARCHAR2(240);
2464 lv_city_name VARCHAR2(240);
2465 lv_school_dst VARCHAR2(240);
2466
2467 BEGIN
2468 lv_procedure_name := '.get_jurisdiction_name' ;
2469 lv_state_code := substr(p_jurisdiction_code,1,2);
2470 lv_county_code := substr(p_jurisdiction_code,4,3);
2471 lv_city_code := substr(p_jurisdiction_code,8,4);
2472 lv_jurisdiction_name := null;
2473
2474 hr_utility.set_location(gv_package || lv_procedure_name, 10);
2475 if p_jurisdiction_code like '__-000-0000' then
2476 lv_jurisdiction_name := pay_us_employee_payslip_web.get_jurisdiction_name
2477 (lv_state_code || '-000-0000');
2478
2479 elsif p_jurisdiction_code like '__-___-0000' then
2480 lv_state_abbrev := pay_us_employee_payslip_web.get_jurisdiction_name
2481 (lv_state_code || '-000-0000');
2482 lv_county_name := pay_us_employee_payslip_web.get_jurisdiction_name
2483 (p_jurisdiction_code);
2484
2485 lv_jurisdiction_name := lv_state_abbrev ||', '||lv_county_name;
2486
2487 elsif p_jurisdiction_code like '__-___-____' then
2488 lv_state_abbrev := pay_us_employee_payslip_web.get_jurisdiction_name
2489 (lv_state_code || '-000-0000');
2490
2491 lv_county_name := pay_us_employee_payslip_web.get_jurisdiction_name
2492 (lv_state_code || '-' || lv_county_code || '-0000');
2493 lv_city_name := pay_us_employee_payslip_web.get_jurisdiction_name
2494 (p_jurisdiction_code);
2495
2496 hr_utility.set_location('p_jurisdiction_code -> '||p_jurisdiction_code, 30);
2497 hr_utility.set_location('lv_state_abbrev -> '|| lv_state_abbrev, 30);
2498 hr_utility.set_location('lv_county_name -> '|| lv_county_name, 30);
2499 hr_utility.set_location('lv_city_name -> '|| lv_city_name, 30);
2500
2501 lv_jurisdiction_name := lv_state_abbrev ||', '||
2502 lv_county_name ||', '||
2503 lv_city_name;
2504 elsif length(p_jurisdiction_code) = 8 then
2505 lv_state_abbrev := pay_us_employee_payslip_web.get_jurisdiction_name
2506 (lv_state_code || '-000-0000');
2507 lv_school_dst := pay_us_employee_payslip_web.get_jurisdiction_name
2508 (p_jurisdiction_code);
2509 lv_jurisdiction_name := lv_state_abbrev || ', ' ||
2510 lv_school_dst;
2511
2512 end if;
2513 hr_utility.set_location(gv_package || lv_procedure_name, 30);
2514 return (lv_jurisdiction_name);
2515 END get_full_jurisdiction_name;
2516
2517 -- This Function will be called from View Definition of
2518 -- PAY_EMP_NET_DIST_ACTION_INFO_V
2519 -- California OT Enhancement Started Populating pay_action_information
2520 -- Table with Account Details for Payment Method Check
2521 -- We need to hide those Details in Self Service Payslip
2522
2523 FUNCTION get_netpaydistr_segment(p_business_grp_id IN NUMBER
2524 ,p_org_pay_meth_id IN NUMBER)
2525 RETURN VARCHAR2 IS
2526
2527 cursor cur_legislation(p_business_grp_id in number) is
2528 select hoi.org_information9
2529 from hr_organization_information hoi
2530 where hoi.organization_id = p_business_grp_id
2531 and hoi.org_information_context = 'Business Group Information';
2532
2533 cursor cur_payment_typ(p_legislation_code in varchar2
2534 ,p_org_pay_meth_id IN NUMBER) is
2535 select '1'
2536 from pay_payment_types ppt
2537 ,pay_org_payment_methods_f popm
2538 where popm.org_payment_method_id = p_org_pay_meth_id
2539 and popm.payment_type_id = ppt.payment_type_id
2540 and ppt.territory_code = p_legislation_code
2541 and ppt.category = 'CH';
2542
2543 lv_legislation_code VARCHAR2(100);
2544 lv_exists VARCHAR2(10);
2545
2546 BEGIN
2547 hr_utility.trace('Entering into pay_us_employee_payslip_web.get_netpaydistr_segment');
2548 hr_utility.trace('p_business_grp_id := ' || p_business_grp_id);
2549 hr_utility.trace('p_org_pay_meth_id := ' || p_org_pay_meth_id);
2550
2551 open cur_legislation(p_business_grp_id);
2552 fetch cur_legislation into lv_legislation_code;
2553 close cur_legislation;
2554
2555 hr_utility.trace('lv_legislation_code := ' || lv_legislation_code);
2556
2557 -- Legislation Check Should not be needed here and Added for Safer Side
2558 -- Can be removed if it can be confirmed that for All Localizations
2559 -- Payment category is 'CH' for Check / Cheque
2560
2561 -- Bug Fix for 8197823 Begin
2562 -- IF lv_legislation_code = 'US' THEN
2563 --commenting the if condiaion as, irrespective of legislation code, account number should not be displayed
2564 --in online payslip when payment method is cheque, Bug 8245514
2565 --IF lv_legislation_code in ('US','GB') THEN
2566 -- Bug Fix for 8197823 End
2567
2568 OPEN cur_payment_typ(lv_legislation_code
2569 ,p_org_pay_meth_id);
2570 FETCH cur_payment_typ INTO lv_exists;
2571 CLOSE cur_payment_typ;
2572
2573 hr_utility.trace('lv_exists := ' || lv_exists);
2574
2575 IF lv_exists = '1' THEN
2576 RETURN 'TRUE';
2577 ELSE
2578 RETURN 'FALSE';
2579 END IF;
2580
2581 --ELSE
2582 -- return 'FALSE';
2583 --END IF;
2584 --End of Bug Fix 8245514
2585
2586 END get_netpaydistr_segment;
2587
2588 --------------------------------------------------------------------------------------------------
2589 -- Bug 9804074: This procedure contains code specific to the JP Legislation. It validates the
2590 -- payslip records and creates records to be displayed in the 'Choose a Payslip'
2591 -- field on the payslip.
2592 --------------------------------------------------------------------------------------------------
2593 PROCEDURE check_jp_emp_personal_payment(p_effective_date VARCHAR2,
2594 p_person_id VARCHAR2,
2595 p_first_call VARCHAR2 DEFAULT 'N',
2596 p_last_fetch OUT NOCOPY VARCHAR2,
2597 p_pay_ret_table OUT NOCOPY pay_payslip_list_table)
2598 IS
2599 --------------------------------------------------------------------------------------------------
2600 is_valid_payslip VARCHAR2(10) := NULL;
2601 row_count NUMBER := 1;
2602 numlistrows NUMBER := 10;
2603 action_context_id VARCHAR2(40);
2604 payroll_id VARCHAR2(50);
2605 assignment_id VARCHAR2(50);
2606 v_effective_date date;
2607 action_information2 VARCHAR2(255);
2608 --
2609 CURSOR get_locked_act_id (v_action_context_id pay_action_interlocks.locking_action_id%TYPE) IS
2610 SELECT
2611 paci.locked_action_id
2612 FROM pay_action_interlocks paci,
2613 pay_assignment_actions paa,
2614 pay_payroll_actions ppa
2615 WHERE paci.locking_action_id = v_action_context_id
2616 AND paa.assignment_action_id = paci.locked_action_id
2617 AND ppa.payroll_action_id = paa.payroll_action_id
2618 AND ppa.action_type IN ( 'P' , 'U' );
2619 --
2620 BEGIN
2621 --
2622 hr_utility.TRACE('Entering check_jp_emp_personal_payment procedure');
2623
2624 p_pay_ret_table := pay_payslip_list_table();
2625
2626 IF p_first_call = 'Y' THEN
2627
2628 hr_utility.TRACE('check_jp_emp_personal_payment - First Call');
2629
2630 numlistrows := numlistrows + 1;
2631
2632 -- check if the cursor was closed. If there was an exception of sort,
2633 -- then it may lead to cursor remaining in open status
2634
2635 IF get_jp_choose_payslip%ISOPEN THEN
2636 close get_jp_choose_payslip;
2637 END IF;
2638
2639 OPEN get_jp_choose_payslip(p_person_id, p_effective_date);
2640
2641 END IF;
2642
2643 hr_utility.TRACE('p_person_id ' || p_person_id);
2644 hr_utility.TRACE('p_effective_date ' || p_effective_date);
2645
2646 LOOP
2647
2648 FETCH get_jp_choose_payslip into action_context_id,
2649 v_effective_date,
2650 payroll_id,
2651 assignment_id,
2652 action_information2;
2653
2654 IF get_jp_choose_payslip%NOTFOUND THEN
2655 CLOSE get_jp_choose_payslip;
2656 p_last_fetch := 'Y';
2657 return;
2658 END IF;
2659
2660 p_last_fetch := 'N';
2661
2662 hr_utility.TRACE('v_effective_date ' || v_effective_date);
2663 hr_utility.TRACE('payroll_id ' || payroll_id);
2664 hr_utility.TRACE('action_context_id ' || action_context_id);
2665 hr_utility.TRACE('assignment_id ' || assignment_id);
2666 hr_utility.TRACE('action_information2 ' || action_information2);
2667
2668 FOR i IN get_locked_act_id(action_context_id) LOOP
2669
2670 hr_utility.TRACE('locked_action_id ' || i.locked_action_id);
2671
2672 is_valid_payslip := check_emp_personal_payment(
2673 p_assignment_id => to_number(assignment_id)
2674 ,p_payroll_id => to_number(payroll_id)
2675 ,p_assignment_action_id => to_number(i.locked_action_id)
2676 ,p_effective_date => v_effective_date
2677 );
2678 IF is_valid_payslip = 'Y' THEN
2679
2680 hr_utility.TRACE('check_emp_personal_payment - Valid Payslip found for t_assignment_action_id ' || action_context_id);
2681 --if row_count > 1 then
2682 p_pay_ret_table.EXTEND(1);
2683
2684 --insert the payslip in to the table to be returned.
2685 p_pay_ret_table(row_count) := pay_payslip_list_rec(action_information2,
2686 action_context_id,
2687 to_char(v_effective_date, 'YYYY-MM-DD'));
2688
2689
2690 --Return if 10 valid payslips are fetched
2691 IF numlistrows <= row_count THEN
2692 RETURN;
2693 END IF;
2694
2695 row_count := row_count + 1;
2696
2697 -- Exiting the cursor get_locked_act_id loop since a valid payslip
2698 -- has been found for the action_context_id
2699 EXIT;
2700
2701 END IF;
2702
2703 END LOOP;
2704
2705 END LOOP;
2706
2707 END check_jp_emp_personal_payment;
2708 ---------------------------------------------------------------------------------------------
2709 PROCEDURE check_emp_personal_payment(p_effective_date VARCHAR2,
2710 p_enable_term VARCHAR2,
2711 p_business_group_id VARCHAR2,
2712 p_person_id VARCHAR2,
2713 p_first_call VARCHAR2 DEFAULT 'N',
2714 p_last_fetch OUT NOCOPY VARCHAR2,
2715 pay_ret_table OUT NOCOPY pay_payslip_list_table)
2716 IS
2717
2718 is_valid_payslip VARCHAR2(10) := NULL;
2719 row_count NUMBER := 1;
2720 numlistrows NUMBER := 10;
2721 l_term_flag VARCHAR2(1) := 'N';
2722 action_context_id VARCHAR2(40);
2723 payroll_id VARCHAR2(50);
2724 time_period_id VARCHAR2(50);
2725 assignment_id VARCHAR2(50);
2726 action_information14 VARCHAR2(255);
2727 check_count VARCHAR2(5);
2728 v_effective_date date;
2729
2730 BEGIN
2731
2732 hr_utility.TRACE('Entering check_emp_personal_payment Wrapper');
2733
2734 pay_ret_table := pay_payslip_list_table();
2735
2736 IF g_legislation_code IS NULL THEN
2737 g_legislation_code := get_legislation_code (p_business_group_id);
2738 END IF;
2739
2740 -- Bug 9804074: Code specific to JP legislation
2741 IF g_legislation_code = 'JP' THEN
2742
2743 check_jp_emp_personal_payment(p_effective_date,
2744 p_person_id,
2745 p_first_call,
2746 p_last_fetch,
2747 pay_ret_table);
2748 -- Bug 9804074: Moved the code for JP legislation to
2749 -- the procedure check_jp_emp_personal_payment()
2750
2751 ELSE
2752 IF p_first_call = 'Y' THEN
2753 hr_utility.TRACE('check_emp_personal_payment - First Call');
2754 numlistrows := numlistrows + 1;
2755 g_job_label := pay_us_employee_payslip_web.get_meaning_payslip_label(g_legislation_code || '', 'JOB');
2756 g_check_label := pay_us_employee_payslip_web.get_meaning_payslip_label(g_legislation_code || '', 'CHECK');
2757
2758 --check if the cursor was closed. If there was an exception of sort, then it may lead to
2759 --cursor remaining in open status
2760 IF get_choose_payslip%ISOPEN THEN
2761 close get_choose_payslip;
2762 END IF;
2763 OPEN get_choose_payslip(p_person_id, p_effective_date);
2764
2765
2766 END IF;
2767
2768 hr_utility.TRACE('p_person_id ' || p_person_id);
2769 hr_utility.TRACE('p_effective_date ' || p_effective_date);
2770 hr_utility.TRACE('g_legislation_code ' || g_legislation_code);
2771
2772 loop
2773
2774 fetch get_choose_payslip into action_context_id,
2775 v_effective_date,
2776 payroll_id,
2777 time_period_id,
2778 assignment_id,
2779 action_information14,
2780 check_count;
2781
2782 if get_choose_payslip%notfound then
2783 CLOSE get_choose_payslip;
2784 p_last_fetch := 'Y';
2785 return;
2786 end if;
2787 p_last_fetch := 'N';
2788
2789 IF g_legislation_code = 'US' THEN
2790
2791 hr_utility.TRACE('check_emp_personal_payment - Legislation Code is US');
2792 hr_utility.TRACE('v_effective_date ' || v_effective_date);
2793 hr_utility.TRACE('payroll_id ' || payroll_id);
2794 hr_utility.TRACE('time_period_id ' || time_period_id);
2795 hr_utility.TRACE('action_context_id ' || action_context_id);
2796 hr_utility.TRACE('assignment_id ' || assignment_id);
2797 hr_utility.TRACE('action_information14 ' || action_information14);
2798 hr_utility.TRACE('check_count ' || check_count);
2799
2800 --Calling the US sepecific Validations
2801 is_valid_payslip := check_us_emp_personal_payment(
2802 p_assignment_id =>to_number(assignment_id)
2803 , p_payroll_id => to_number(payroll_id)
2804 , p_time_period_id => to_number(time_period_id)
2805 , p_assignment_action_id => to_number(action_context_id)
2806 , p_effective_date => v_effective_date
2807 );
2808 ELSE
2809 hr_utility.TRACE('check_emp_personal_payment - Calling Generic');
2810 hr_utility.TRACE('v_effective_date ' || v_effective_date);
2811 hr_utility.TRACE('payroll_id ' || payroll_id);
2812 hr_utility.TRACE('time_period_id ' || time_period_id);
2813 hr_utility.TRACE('action_context_id ' || action_context_id);
2814 hr_utility.TRACE('assignment_id ' || assignment_id);
2815 hr_utility.TRACE('action_information14 ' || action_information14);
2816 hr_utility.TRACE('check_count ' || check_count);
2817
2818 is_valid_payslip := check_emp_personal_payment(
2819 p_assignment_id =>to_number(assignment_id)
2820 ,p_payroll_id => to_number(payroll_id)
2821 ,p_time_period_id => to_number(time_period_id)
2822 ,p_assignment_action_id => to_number(action_context_id)
2823 ,p_effective_date => v_effective_date
2824 );
2825 END IF;
2826
2827 IF is_valid_payslip = 'Y' THEN
2828
2829 hr_utility.TRACE('Before Term Check');
2830 hr_utility.TRACE('check_emp_personal_payment - Valid Payslip found for t_assignment_action_id ' ||action_context_id);
2831
2832 --Now check for Termination
2833 IF p_enable_term = 'N' THEN
2834 l_term_flag := pay_us_employee_payslip_web.get_term_info(p_business_group_id, p_person_id, action_context_id);
2835 END IF;
2836
2837 IF (p_enable_term = 'N' AND l_term_flag = 'Y') OR (p_enable_term <> 'N') THEN
2838
2839 hr_utility.TRACE('After Term Check');
2840 hr_utility.TRACE('check_emp_personal_payment - Valid Payslip found for t_assignment_action_id ' || action_context_id);
2841 --if row_count > 1 then
2842 pay_ret_table.EXTEND(1);
2843
2844 --insert the payslip in to the table to be returned.
2845 pay_ret_table(row_count) := pay_payslip_list_rec( get_display_list( p_date => v_effective_date,
2846 p_legislation_code => g_legislation_code,
2847 p_legislation_code_1 => g_legislation_code,
2848 p_action_information14 => action_information14,
2849 p_check_count => check_count) ,
2850 action_context_id,
2851 to_char(v_effective_date, 'YYYY-MM-DD')
2852 );
2853
2854
2855 --Return if 10 valid payslips are fetched
2856 IF numlistrows <= row_count THEN
2857 RETURN;
2858 END IF;
2859 row_count := row_count + 1;
2860 ELSE
2861 hr_utility.TRACE('Terminated Employee Payslip');
2862 END IF;
2863
2864 END IF;
2865
2866 END LOOP;
2867
2868 END IF;
2869
2870 END;
2871
2872 /*********************************************************************
2873 Name : check_us_emp_personal_payment
2874 Purpose : US Specific Payslip Validations
2875
2876 Arguments : p_assignment_id, p_payroll_id, p_time_period_id,
2877 p_assignment_action_id, p_effective_date
2878 Notes :
2879 *********************************************************************/
2880 /*9394861 Payslip Validations specific to US Legislation. Earlier these validations
2881 were included in check_emp_personal_payment
2882 */
2883 FUNCTION check_us_emp_personal_payment(
2884 p_assignment_id NUMBER
2885 , p_payroll_id NUMBER
2886 , p_time_period_id NUMBER
2887 , p_assignment_action_id NUMBER
2888 , p_effective_date DATE
2889 )
2890 RETURN VARCHAR2 IS
2891
2892 /* Cursor to get Payslip offset date for a payroll */
2893 --Added REGULAR_PAYMENT_DATE for bug 8550075
2894 CURSOR c_view_offset(cp_time_period_id IN NUMBER) IS
2895 SELECT payslip_view_date, REGULAR_PAYMENT_DATE
2896 FROM per_time_periods ptp
2897 WHERE time_period_id = cp_time_period_id;
2898
2899 /* Cursor to get the how employee is paid */
2900 CURSOR c_pre_payment_method
2901 (cp_assignment_action_id NUMBER) IS
2902 SELECT ppp.pre_payment_id
2903 FROM pay_payment_types ppt,
2904 pay_org_payment_methods_f popm,
2905 pay_pre_payments ppp
2906 WHERE ppp.assignment_action_id = cp_assignment_action_id
2907 AND popm.org_payment_method_id = ppp.org_payment_method_id
2908 AND popm.defined_balance_id IS NOT NULL
2909 AND ppt.payment_type_id = popm.payment_type_id;
2910
2911
2912 CURSOR c_check_for_reversal(cp_assignment_action_id IN NUMBER) IS
2913 SELECT 1
2914 FROM pay_action_interlocks pai_pre
2915 WHERE pai_pre.locking_action_id = cp_assignment_action_id
2916 AND EXISTS (
2917 SELECT 1
2918 FROM pay_payroll_actions ppa,
2919 pay_assignment_actions paa,
2920 pay_action_interlocks pai_run
2921 /* Get the run assignment action id locked by pre-payment */
2922 WHERE pai_run.locked_action_id = pai_pre.locked_action_id
2923 /* Check if the Run is being locked by Reversal */
2924 AND pai_run.locking_action_id = paa.assignment_action_id
2925 AND ppa.payroll_action_id = paa.payroll_action_id
2926 AND paa.action_status = 'C'
2927 AND ppa.action_type = 'V');
2928
2929 /****************************************************************
2930 ** If archiver is locking the pre-payment assignment_action_id,
2931 ** we get it from interlocks and use it to check if all payments
2932 ** have been made fro the employee.
2933 ****************************************************************/
2934 CURSOR c_prepay_arch_action(cp_assignment_action_id IN NUMBER) IS
2935 SELECT paa.assignment_action_id
2936 FROM pay_action_interlocks paci,
2937 pay_assignment_actions paa,
2938 pay_payroll_actions ppa
2939 WHERE paci.locking_action_id = cp_assignment_action_id
2940 AND paa.assignment_action_id = paci.locked_action_id
2941 AND ppa.payroll_action_id = paa.payroll_action_id
2942 AND ppa.action_type IN ('P', 'U');
2943
2944 /****************************************************************
2945 ** If archiver is locking the run assignment_action_id, we get
2946 ** the corresponding run assignment_action_id and then get
2947 ** the pre-payment assignemnt_action_id.
2948 ** This cursor is only required when there are child action which
2949 ** means there is a seperate check.
2950 * ***************************************************************/
2951 CURSOR c_prepay_run_arch_action(cp_assignment_action_id IN NUMBER) IS
2952 SELECT paa_pre.assignment_action_id
2953 FROM pay_action_interlocks pai_run,
2954 pay_action_interlocks pai_pre,
2955 pay_assignment_actions paa_pre,
2956 pay_payroll_actions ppa_pre
2957 WHERE pai_run.locking_action_id = cp_assignment_action_id
2958 AND pai_pre.locked_action_id = pai_run.locked_action_id
2959 AND paa_pre.assignment_Action_id = pai_pre.locking_action_id
2960 AND ppa_pre.payroll_action_id = paa_pre.payroll_action_id
2961 AND ppa_pre.action_type IN ('P', 'U');
2962
2963 --Modified Cursor for bug 8550075
2964 CURSOR c_get_date_earned(cp_assignment_action_id IN NUMBER) IS
2965 SELECT nvl(MAX(ppa.date_earned), MAX(ppa.effective_date)), MAX(ppa.effective_date), BUSINESS_GROUP_ID
2966 FROM pay_payroll_actions ppa
2967 , pay_assignment_actions paa
2968 , pay_action_interlocks pai
2969 WHERE ppa.payroll_action_id = paa.payroll_action_id
2970 AND pai.locked_action_id = paa.assignment_action_id
2971 AND pai.locking_action_id = cp_assignment_action_id
2972 AND ppa.action_type IN ('R', 'Q', 'B', 'V')
2973 GROUP BY BUSINESS_GROUP_ID;
2974
2975 CURSOR c_time_period(cp_payroll_id IN NUMBER
2976 , cp_date_earned IN DATE) IS
2977 SELECT ptp.time_period_id
2978 FROM per_time_periods ptp
2979 WHERE cp_date_earned BETWEEN ptp.start_date
2980 AND ptp.end_Date
2981 AND ptp.payroll_id = cp_payroll_id;
2982
2983
2984 --New cursor defined for bug 8550075
2985 CURSOR c_get_view_date_criteria (cp_bg_id IN NUMBER) IS
2986 SELECT 'Y' FROM hr_organization_information hoi
2987 WHERE hoi.organization_id = cp_bg_id
2988 AND hoi.org_information_context = 'HR_SELF_SERVICE_BG_PREFERENCE'
2989 AND ORG_INFORMATION12 = 'DATE_PAID'
2990 AND ORG_INFORMATION1 = 'PAYSLIP';
2991
2992
2993 lv_reversal_exists VARCHAR2(1);
2994 ln_prepay_action_id NUMBER;
2995 ln_pre_payment_id NUMBER;
2996 lv_payment_status VARCHAR2(50);
2997 lv_return_flag VARCHAR2(1);
2998
2999 ld_view_payslip_offset_date DATE;
3000 ld_date_paid DATE;
3001 ld_reg_payment_date DATE;
3002 ld_earned_date DATE;
3003 ln_time_period_id NUMBER;
3004 ln_offset_value NUMBER;
3005
3006 ld_payslip_view_date DATE;
3007 ln_bg_id NUMBER;
3008 is_dynamic_view_date VARCHAR2(1);
3009
3010
3011 BEGIN
3012
3013 hr_utility.TRACE('Entering check_emp_personal_payment');
3014 hr_utility.TRACE('p_effective_date=' || p_effective_date);
3015 hr_utility.TRACE('p_time_period_id=' || p_time_period_id);
3016
3017
3018 -- END For bug 8643214
3019
3020 lv_return_flag := 'Y';
3021
3022 OPEN c_prepay_arch_action(p_assignment_action_id);
3023 FETCH c_prepay_arch_action INTO ln_prepay_action_id;
3024 IF c_prepay_arch_action%notfound THEN
3025 OPEN c_prepay_run_arch_action(p_assignment_action_id);
3026 FETCH c_prepay_run_arch_action INTO ln_prepay_action_id;
3027 if c_prepay_run_arch_action%notfound then
3028 return('N');
3029 end if;
3030 close c_prepay_run_arch_action;
3031 END IF;
3032 CLOSE c_prepay_arch_action;
3033
3034 ln_time_period_id := p_time_period_id;
3035 IF ln_time_period_id IS NULL THEN
3036 OPEN c_get_date_earned(ln_prepay_action_id);
3037 FETCH c_get_date_earned INTO ld_earned_date, ld_date_paid, ln_bg_id;
3038 IF c_get_date_earned%found THEN
3039 OPEN c_time_period(p_payroll_id, ld_earned_date);
3040 FETCH c_time_period INTO ln_time_period_id;
3041 CLOSE c_time_period;
3042 END IF;
3043 CLOSE c_get_date_earned;
3044 END IF;
3045
3046 hr_utility.TRACE('ln_time_period_id=' || ln_time_period_id);
3047 OPEN c_view_offset(ln_time_period_id);
3048 FETCH c_view_offset INTO ld_view_payslip_offset_date, ld_reg_payment_date;
3049 CLOSE c_view_offset;
3050
3051 hr_utility.TRACE('ld_view_payslip_offset_date=' || trunc(ld_view_payslip_offset_date));
3052 hr_utility.TRACE('p_effective_date=' || trunc(p_effective_date));
3053 hr_utility.TRACE('sysdate=' || trunc(SYSDATE));
3054
3055 IF ld_date_paid IS NULL THEN
3056 OPEN c_get_date_earned(ln_prepay_action_id);
3057 FETCH c_get_date_earned INTO ld_earned_date, ld_date_paid, ln_bg_id;
3058 CLOSE c_get_date_earned;
3059 END IF;
3060
3061 IF ld_view_payslip_offset_date IS NOT NULL THEN
3062
3063 IF ln_bg_id IS NOT NULL THEN
3064 OPEN c_get_view_date_criteria(ln_bg_id);
3065 FETCH c_get_view_date_criteria INTO is_dynamic_view_date;
3066 CLOSE c_get_view_date_criteria;
3067 END IF;
3068
3069 if g_legislation_code is null then
3070 g_legislation_code := get_legislation_code(ln_bg_id);
3071 end if;
3072
3073 hr_utility.TRACE('g_legislation_code=' || g_legislation_code);
3074 hr_utility.TRACE('is_dynamic_view_date=' || is_dynamic_view_date);
3075
3076 --If Date_paid preference is set and legislation_code is 'US'
3077 IF is_dynamic_view_date = 'Y' AND ld_date_paid IS NOT NULL AND g_legislation_code = 'US' THEN
3078
3079 ln_offset_value := trunc(ld_view_payslip_offset_date - ld_reg_payment_date);
3080
3081 hr_utility.TRACE('ln_offset_value=' || ln_offset_value);
3082 hr_utility.TRACE('ld_view_payslip_offset_date=' || ld_view_payslip_offset_date);
3083
3084 ld_payslip_view_date := trunc(ld_date_paid) + ln_offset_value;
3085 hr_utility.TRACE('ld_payslip_view_date=' || ld_payslip_view_date);
3086
3087 IF trunc(ld_payslip_view_date) > trunc(SYSDATE) THEN
3088 hr_utility.TRACE('View offset return N');
3089 RETURN('N');
3090 END IF;
3091 --Default Behaviour
3092 ELSE
3093 hr_utility.TRACE('Offset Criteria is not Date_paid ');
3094 IF trunc(ld_view_payslip_offset_date) > trunc(SYSDATE) THEN
3095 hr_utility.TRACE('View offset return N');
3096 RETURN('N');
3097 END IF;
3098 END IF;
3099
3100 END IF;
3101 --End of Fix for bug 8550075
3102
3103 OPEN c_check_for_reversal(ln_prepay_action_id);
3104 FETCH c_check_for_reversal INTO lv_reversal_exists;
3105 IF c_check_for_reversal%found THEN
3106 lv_return_flag := 'N';
3107 ELSE
3108 OPEN c_pre_payment_method (ln_prepay_action_id);
3109 LOOP
3110 /* fetch all the pre payment records for the asssignment
3111 other than 3rd party payment */
3112 FETCH c_pre_payment_method INTO ln_pre_payment_id;
3113
3114 IF c_pre_payment_method%notfound THEN
3115 EXIT;
3116 END IF;
3117
3118 lv_payment_status := ltrim(rtrim(
3119 pay_assignment_actions_pkg.get_payment_status_code
3120 (ln_prepay_action_id,
3121 ln_pre_payment_id)));
3122
3123 IF lv_payment_status <> 'P' THEN
3124 lv_return_flag := 'N';
3125 EXIT;
3126 ELSE
3127 lv_return_flag := 'Y';
3128 END IF;
3129
3130 END LOOP;
3131 CLOSE c_pre_payment_method;
3132
3133 END IF;
3134 CLOSE c_check_for_reversal;
3135
3136 hr_utility.TRACE('lv_return_flag=' || lv_return_flag);
3137 hr_utility.TRACE('Leaving check_emp_personal_payment');
3138
3139 RETURN lv_return_flag;
3140
3141 END check_us_emp_personal_payment;
3142
3143 /*********************************************************************
3144 Name : get_display_date
3145 Purpose : This function returns the choose payslip list with date in
3146 the calendar format specified as per profile
3147 FND: Forms User Calendar
3148
3149
3150 Arguments : p_date, p_legislation_code, p_legislation_code_1,
3151 p_action_information14, p_check_count
3152 Notes : 1. p_legislation_code_1 is a dummy input parameter. This
3153 has been introduced so that the bind variables in
3154 PayPayslipChossePayslipVO need not be reorganized.
3155 Any reorganization of the bind variables would require
3156 modifications in all CO files referencing this VO.
3157 2. Hijrah support has been provided at present only for
3158 releases post 12.1.1. Over loaded version of
3159 FND function fnd_date.date_to_displaydate is available
3160 at present only in 12.1.1 and later
3161 More details in note 807393.1
3162 3. Date formatting does not depend on the profile
3163 "FND: Date API Calendar Awareness Default"
3164 At the time this code was written, it has been
3165 observed that the date fields in OA framework is not
3166 dependent on this profile. Hence while calling
3167 fnd_date.date_to_displaydate, the calendar_aware
3168 parameter has been harcoded as 1
3169 *********************************************************************/
3170
3171 FUNCTION get_display_list (p_date DATE,
3172 p_legislation_code VARCHAR2,
3173 p_legislation_code_1 VARCHAR2,
3174 p_action_information14 VARCHAR2,
3175 p_check_count NUMBER)
3176 return VARCHAR2 is
3177
3178 v_list VARCHAR2(500);
3179
3180 begin
3181
3182 if g_job_label is null then
3183 g_job_label := pay_us_employee_payslip_web.get_meaning_payslip_label(p_legislation_code || '', 'JOB');
3184 end if;
3185
3186 if g_check_label is null then
3187 g_check_label := pay_us_employee_payslip_web.get_meaning_payslip_label(p_legislation_code || '', 'CHECK');
3188 end if;
3189 /*
3190 if fnd_release.release_name >= '12.1.1' then
3191
3192 hr_utility.TRACE('Release greater than or equal to 12.1.1 ' );
3193 --Calendar_aware parameter intentionally hard coded as 1, as date fields in OA framework does not depend
3194 --on the fnd profile "FND: Date API Calendar Awareness Default"
3195 v_list := fnd_date.date_to_displaydate(p_date,1)
3196 ||' - '
3197 || g_job_label
3198 ||' '
3199 || p_action_information14
3200 ||' - '
3201 ||g_check_label
3202 ||' '
3203 || p_check_count;
3204
3205
3206 else
3207 hr_utility.TRACE('Release less than 12.1.1 ' );
3208 v_list := p_date
3209 ||' - '
3210 || g_job_label
3211 ||' '
3212 || p_action_information14
3213 ||' - '
3214 ||g_check_label
3215 ||' '
3216 || p_check_count;
3217
3218 end if; */
3219
3220 if p_legislation_code = 'US' then
3221 g_job_label := null;
3222 end if;
3223
3224 v_list := pay_date_wrapper_pkg.fnd_date_date_to_displaydate(p_date)
3225 ||' - '
3226 || g_job_label
3227 ||' '
3228 || p_action_information14
3229 ||' - '
3230 ||g_check_label
3231 ||' '
3232 || p_check_count;
3233
3234 hr_utility.TRACE('v_list ' || v_list);
3235 return v_list;
3236
3237 end get_display_list;
3238
3239 BEGIN
3240 gv_package := 'pay_us_employee_payslip_web';
3241 -- hr_utility.trace_on(null, 'PAYSLIP');
3242 END pay_us_employee_payslip_web;
[Home] [Help]