1 PACKAGE BODY pyusexc AS
2 /* $Header: pyusexc.pkb 120.0.12010000.2 2009/01/09 12:15:10 sudedas 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 : pyusexc.pkb
21
22 Description : PaYroll US legislation EXpiry Checking code.
23 Contains the expiry checking code associated with the US
24 balance dimensions. Following the change
25 to latest balance functionality, these need to be contained
26 as packaged procedures.
27
28 Change List
29 -----------
30 Date Name Vers Bug No Description
31 ----------- ---------- ------ ------- --------------------------------------
32 23-SEP-1994 spanwar First created.
33 10-JUL-1995 spanwar Changed RUN level check to not expire
34 if the user and owner payroll action
35 id's are the same.
36 21-NOV-1995 hparicha Now handles "Lifetime to Date" dim
37 without failure.
38 27-FEB-1996 ssdesai 333439 Date format was dd-mon-yy.
39 30-JUL-1996 jalloun Added error handling.
40 19-SEP-2000 djoshi Added overloded date_ec funtion
41 and modified the file to pass
42 check_sql tests.
43 14-JUN-2001 mreid 1808057 Changed date in LTD check to canonical
44 14-MAR-2005 saurgupt 4100637 Changed the function next_fiscal_year
45 Modified the code to work for leap
46 year. Also, made the gscc changes.
47 18-MAY-2005 ahanda 115.6 Added procedure start_tdptd_date.
48 09-Jan-2009 sudedas 115.7 7029830 Modified next_fiscal_year to
49 consider both Leap Year and Non Leap
50 Year scenarios.
51
52 */
53
54 /*---------------------------- next_period -----------------------------------
55 NAME
56 next_period
57 DESCRIPTION
58 Given a date and a payroll action id, returns the date of the day after
59 the end of the containing pay period.
60 NOTES
61 <none>
62 */
63 FUNCTION next_period
64 (
65 p_pactid IN NUMBER,
66 p_date IN DATE
67 ) RETURN DATE is
68 l_return_val DATE;
69 BEGIN
70
71 l_return_val := NULL;
72
73 select TP.end_date + 1
74 into l_return_val
75 from per_time_periods TP,
76 pay_payroll_actions PACT
77 where PACT.payroll_action_id = p_pactid
78 and PACT.payroll_id = TP.payroll_id
79 and p_date between TP.start_date and TP.end_date;
80
81 RETURN l_return_val;
82
83 END next_period;
84
85 /*---------------------------- next_month ------------------------------------
86 NAME
87 next_month
88 DESCRIPTION
89 Given a date, returns the date of the first day of the next month.
90 NOTES
91 <none>
92 */
93 FUNCTION next_month
94 (
95 p_date IN DATE
96 ) return DATE is
97 BEGIN
98
99 RETURN trunc(add_months(p_date,1),'MM');
100
101 END next_month;
102
103 /*--------------------------- next_quarter -----------------------------------
104 NAME
105 next_quarter
106 DESCRIPTION
107 Given a date, returns the date of the first day of the next calendar
108 quarter.
109 NOTES
110 <none>
111 */
112 FUNCTION next_quarter
113 (
114 p_date IN DATE
115 ) RETURN DATE is
116 BEGIN
117
118 RETURN trunc(add_months(p_date,3),'Q');
119
120 END next_quarter;
121
122 /*---------------------------- next_year ------------------------------------
123 NAME
124 next_year
125 DESCRIPTION
126 Given a date, returns the date of the first day of the next calendar
127 year.
128 NOTES
129 <none>
130 */
131 FUNCTION next_year
132 (
133 p_date IN DATE
134 ) RETURN DATE is
135 BEGIN
136
137 RETURN trunc(add_months(p_date,12),'Y');
138
139 END next_year;
140
141 /*------------------------- next_fiscal_quarter -----------------------------
142 NAME
143 next_fiscal_quarter
144 DESCRIPTION
145 Given a date, returns the date of the first day of the next fiscal
146 quarter.
147 NOTES
148 <none>
149 */
150 FUNCTION next_fiscal_quarter
151 (
152 p_beg_of_fiscal_year IN DATE,
153 p_date IN DATE
154 ) RETURN DATE is
155
156 -- get offset of fiscal year start in relative months and days
157 l_fy_rel_month NUMBER(2);
158 l_fy_rel_day NUMBER(2);
159
160 BEGIN
161
162 l_fy_rel_month := to_char(p_beg_of_fiscal_year, 'MM') - 1;
163 l_fy_rel_day := to_char(p_beg_of_fiscal_year, 'DD') - 1;
164
165 RETURN (add_months(next_quarter(add_months(p_date, -l_fy_rel_month)
166 - l_fy_rel_day),
167 l_fy_rel_month) + l_fy_rel_day);
168
169 END next_fiscal_quarter;
170
171 /*--------------------------- next_fiscal_year ------------------------------
172 NAME
173 next_fiscal_year
174 DESCRIPTION
175 Given a date, returns the date of the first day of the next fiscal year.
176 NOTES
177 <none>
178 */
179 FUNCTION next_fiscal_year
180 (
181 p_beg_of_fiscal_year IN DATE,
182 p_date IN DATE
183 ) RETURN DATE is
184
185 -- get offset of fiscal year start relative to calendar year
186 l_fiscal_year_offset NUMBER(3);
187 ln_bal_yr PLS_INTEGER;
188 ln_bg_fiscal_yr PLS_INTEGER;
189 lb_bal_yr_leapyr BOOLEAN DEFAULT FALSE;
190 lb_bg_fiscal_yr_leapyr BOOLEAN DEFAULT FALSE;
191
192 BEGIN
193 ln_bal_yr := fnd_number.canonical_to_number(TO_CHAR(p_date, 'YYYY'));
194 ln_bg_fiscal_yr := fnd_number.canonical_to_number(TO_CHAR(p_beg_of_fiscal_year, 'YYYY'));
195
196 -- Checking whether year of balance is Leap Year
197
198 if mod(ln_bal_yr, 100) = 0 then
199 if mod(ln_bal_yr, 400) = 0 then
200 lb_bal_yr_leapyr := TRUE;
201 else
202 lb_bal_yr_leapyr := FALSE;
203 end if;
204 else
205 if mod(ln_bal_yr, 4) = 0 then
206 lb_bal_yr_leapyr := TRUE;
207 else
208 lb_bal_yr_leapyr := FALSE;
209 end if;
210 end if;
211
212 -- Checking whether business group fiscal year is Leap Year
213
214 if mod(ln_bg_fiscal_yr, 100) = 0 then
215 if mod(ln_bg_fiscal_yr, 400) = 0 then
216 lb_bg_fiscal_yr_leapyr := TRUE;
217 else
218 lb_bg_fiscal_yr_leapyr := FALSE;
219 end if;
220 else
221 if mod(ln_bg_fiscal_yr, 4) = 0 then
222 lb_bg_fiscal_yr_leapyr := TRUE;
223 else
224 lb_bg_fiscal_yr_leapyr := FALSE;
225 end if;
226 end if;
227
228 --l_fiscal_year_offset := to_char(p_beg_of_fiscal_year, 'DDD') - 1;
229
230 /* Four possible scenarios
231 Balance year Leap Yr + BG Fiscal Year Leap Yr
232 Balance year NOT Leap Yr + BG Fiscal Year NOT Leap Yr
233 Balance year NOT Leap Yr + BG Fiscal Year Leap Yr
234 Balance year Leap Yr + BG Fiscal Year NOT Leap Yr
235 */
236
237 if (lb_bal_yr_leapyr and lb_bg_fiscal_yr_leapyr) or
238 (NOT(lb_bal_yr_leapyr) and NOT(lb_bg_fiscal_yr_leapyr)) or
239 (NOT(lb_bal_yr_leapyr) and lb_bg_fiscal_yr_leapyr) then
240
241 l_fiscal_year_offset := to_char(p_beg_of_fiscal_year, 'DDD') - 1;
242
243 elsif (lb_bal_yr_leapyr and NOT(lb_bg_fiscal_yr_leapyr)) then
244 l_fiscal_year_offset := to_char(p_beg_of_fiscal_year, 'DDD');
245 end if;
246
247 -- Bug 4100637: Instead of adding the offset to get the next
248 -- fiscal year date, just concatenated the current year
249 -- with the fiscal start month and date. Adding offset gives
250 -- one day less in case of leap year.
251
252 RETURN fnd_date.canonical_to_date(
253 to_char(next_year(p_date - l_fiscal_year_offset),'RRRR')||
254 substr(fnd_date.date_to_canonical(p_beg_of_fiscal_year),5,6));
255 -- RETURN (next_year(p_date - l_fiscal_year_offset) + l_fiscal_year_offset);
256
257 END next_fiscal_year;
258
259 /*------------------------------ date_ec ------------------------------------
260 NAME
261 date_ec
262 DESCRIPTION
263 Expiry checking code for the following date-related dimensions:
264 Assignment/Person/neither and GRE/not GRE and
265 Run/Period TD/Month/Quarter TD/Year TD/Fiscal Quarter TD/
266 Fiscal Year TD
267 NOTES
268 This procedure assumes the date portion of the dimension name
269 is always at the end to allow accurate identification since
270 this is used for many dimensions.
271 */
272 PROCEDURE date_ec
273 (
274 p_owner_payroll_action_id in number, -- run created balance.
275 p_user_payroll_action_id in number, -- current run.
276 p_owner_assignment_action_id in number, -- assact created balance.
277 p_user_assignment_action_id in number, -- current assact..
278 p_owner_effective_date in date, -- eff date of balance.
279 p_user_effective_date in date, -- eff date of current run.
280 p_dimension_name in varchar2, -- balance dimension name.
281 p_expiry_information out nocopy number -- dimension expired flag.
282 ) is
283
284 l_beg_of_fiscal_year DATE;
285 l_expiry_date DATE;
286
287 BEGIN
288
289 l_beg_of_fiscal_year := NULL;
290 l_expiry_date := NULL;
291
292 IF p_dimension_name like '%Run' THEN
293 -- must check for special case: if payroll action id's are the same,
294 -- then don't expire. This facilitates meaningful access of these
295 -- balances outside of runs.
296 IF p_owner_payroll_action_id <> p_user_payroll_action_id THEN
297 l_expiry_date := p_user_effective_date; -- always must expire.
298 ELSE
299 p_expiry_information := 0;
300 RETURN;
301 END IF;
302
303 ELSIF p_dimension_name like '%Payments%' THEN
304 -- must check for special case: if payroll action id's are the same,
305 -- then don't expire. This facilitates meaningful access of these
306 -- balances outside of runs.
307 IF p_owner_payroll_action_id <> p_user_payroll_action_id THEN
308 l_expiry_date := p_user_effective_date; -- always must expire.
309 ELSE
310 p_expiry_information := 0; -- daj
311 RETURN;
312 END IF;
313
314 ELSIF p_dimension_name like '%Period to Date' THEN
315 l_expiry_date := next_period(p_owner_payroll_action_id,
316 p_owner_effective_date);
317
318 ELSIF p_dimension_name like '%Month' THEN
319 l_expiry_date := next_month(p_owner_effective_date);
320
321 ELSIF p_dimension_name like '%Fiscal Quarter to Date' THEN
322 SELECT fnd_date.canonical_to_date(org_information11)
323 INTO l_beg_of_fiscal_year
324 FROM pay_payroll_actions PACT,
325 hr_organization_information HOI
326 WHERE UPPER(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION'
327 AND HOI.organization_id = PACT.business_group_id
328 AND PACT.payroll_action_id = p_owner_payroll_action_id;
329
330 l_expiry_date := next_fiscal_quarter(l_beg_of_fiscal_year,
331 p_owner_effective_date);
332
333 ELSIF p_dimension_name like '%Fiscal Year to Date' THEN
334 SELECT fnd_date.canonical_to_date(org_information11)
335 INTO l_beg_of_fiscal_year
336 FROM pay_payroll_actions PACT,
337 hr_organization_information HOI
338 WHERE UPPER(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION'
339 AND HOI.organization_id = PACT.business_group_id
340 AND PACT.payroll_action_id = p_owner_payroll_action_id;
341
342 l_expiry_date := next_fiscal_year(l_beg_of_fiscal_year,
343 p_owner_effective_date);
344
345 ELSIF p_dimension_name like '%Quarter to Date' THEN
346 l_expiry_date := next_quarter(p_owner_effective_date);
347
348 ELSIF p_dimension_name like '%Year to Date' THEN
349 l_expiry_date := next_year(p_owner_effective_date);
350
351 ELSIF p_dimension_name like '%Lifetime to Date' THEN
352 p_expiry_information := 0;
353 RETURN;
354
355 ELSE
356 hr_utility.set_message(801, 'NO_EXP_CHECK_FOR_THIS_DIMENSION');
357 hr_utility.raise_error;
358
359 END IF;
360
361 IF p_user_effective_date >= l_expiry_date THEN
362 p_expiry_information := 1;
363 ELSE
364 p_expiry_information := 0;
365 END IF;
366
367 END date_ec;
368
369
370 /* This procedure is the overlaoded function that will take care of the
371 of the requirement of Balance adjustment process.*/
372 PROCEDURE date_ec
373 (
374 p_owner_payroll_action_id in number, -- run created balance.
375 p_user_payroll_action_id in number, -- current run.
376 p_owner_assignment_action_id in number, -- assact created balance.
377 p_user_assignment_action_id in number, -- current assact..
378 p_owner_effective_date in date, -- eff date of balance.
379 p_user_effective_date in date, -- eff date of current run.
380 p_dimension_name in varchar2, -- balance dimension name.
381 p_expiry_information out nocopy Date -- dimension expired date.
382 ) is
383
384 l_beg_of_fiscal_year DATE;
385 l_expiry_date DATE;
386
387 BEGIN
388
389 l_beg_of_fiscal_year := NULL;
390 l_expiry_date := NULL;
391
392 If p_dimension_name like '%Run' THEN
393
394 -- must check for special case: if payroll action id's are the same,
395 -- then don't expire. This facilitates meaningful access of these
396 -- balances outside of runs.
397
398 p_expiry_information := p_owner_effective_date;
399
400 ELSIF p_dimension_name like '%Payments%' THEN
401
402 p_expiry_information := p_owner_effective_date;
403
404
405 ELSIF p_dimension_name like '%Period to Date' THEN
406
407 p_expiry_information := next_period(p_owner_payroll_action_id,
408 p_owner_effective_date) - 1;
409
410 ELSIF p_dimension_name like '%Month' THEN
411
412 p_expiry_information := next_month(p_owner_effective_date) - 1;
413
414 ELSIF p_dimension_name like '%Fiscal Quarter to Date' THEN
415 SELECT fnd_date.canonical_to_date(org_information11)
416 INTO l_beg_of_fiscal_year
417 FROM pay_payroll_actions PACT,
418 hr_organization_information HOI
419 WHERE UPPER(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION'
420 AND HOI.organization_id = PACT.business_group_id
421 AND PACT.payroll_action_id = p_owner_payroll_action_id;
422
423 p_expiry_information := next_fiscal_quarter(l_beg_of_fiscal_year,
424 p_owner_effective_date) - 1;
425
426 ELSIF p_dimension_name like '%Fiscal Year to Date' THEN
427 SELECT fnd_date.canonical_to_date(org_information11)
428 INTO l_beg_of_fiscal_year
429 FROM pay_payroll_actions PACT,
430 hr_organization_information HOI
431 WHERE UPPER(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION'
432 AND HOI.organization_id = PACT.business_group_id
433 AND PACT.payroll_action_id = p_owner_payroll_action_id;
434
435 p_expiry_information := next_fiscal_year(l_beg_of_fiscal_year,
436 p_owner_effective_date) - 1;
437
438
439 ELSIF p_dimension_name like '%Quarter to Date' THEN
440
441 p_expiry_information := next_quarter(p_owner_effective_date) - 1;
442
443 ELSIF p_dimension_name like '%Year to Date' THEN
444
445 p_expiry_information := next_year(p_owner_effective_date) -1;
446
447 ELSIF p_dimension_name like '%Lifetime to Date' THEN
448
449 p_expiry_information := fnd_date.canonical_to_date('4712/12/31');
450
451 ELSE
452
453 hr_utility.set_message(801, 'NO_EXP_CHECK_FOR_THIS_DIMENSION');
454 hr_utility.raise_error;
455
456 END IF;
457
458 END date_ec; /* date p_expiry information procedure overloaded function */
459
460
461 /*************************************************************************
462 ** Description - Procedure returns the start date for Time Definition
463 ** Period to Date which is run for validation the Run
464 ** Balance validation date
465 ** Arguments -
466 ** p_start_date - effective date of payroll action
467 ** p_payroll_id - payroll_id
468 ** p_bus_grp - Business Group ID
469 ** p_asg_action - Assignment Action ID
470 *************************************************************************/
471 PROCEDURE start_tdptd_date(p_effective_date IN DATE
472 ,p_start_date OUT NOCOPY DATE
473 ,p_payroll_id IN NUMBER DEFAULT NULL
474 ,p_bus_grp IN NUMBER DEFAULT NULL
475 ,p_asg_action IN NUMBER DEFAULT NULL)
476 IS
477
478 cursor c_asg_data(cp_asg_action in number) is
479 select nvl(ppa.date_earned, ppa.effective_date)
480 ,paa.assignment_id
481 from pay_assignment_actions paa
482 ,pay_payroll_actions ppa
483 where paa.assignment_action_id = cp_asg_action
484 and ppa.payroll_action_id = paa.payroll_Action_id;
485
486 cursor c_td_start_date(cp_time_definition_id number
487 ,cp_date_earned date) is
488 select ptp.start_date
489 from per_time_periods ptp
490 where ptp.time_definition_id = cp_time_definition_id
491 and cp_date_earned between ptp.start_date
492 and ptp.end_date;
493
494 ln_time_definition_id NUMBER;
495 ln_assignment_id NUMBER;
496 ld_date_earned DATE;
497 ld_start_date DATE;
498
499 BEGIN
500 ld_date_earned := p_effective_date;
501
502 open c_asg_data(p_asg_action);
503 fetch c_asg_data into ld_date_earned, ln_assignment_id;
504 close c_asg_data;
505
506 pay_us_rules.get_time_def_for_entry (
507 p_element_entry_id => null
508 ,p_assignment_id => ln_assignment_id
509 ,p_assignment_action_id => p_asg_action
510 ,p_business_group_id => p_bus_grp
511 ,p_time_definition_id => ln_time_definition_id);
512
513 open c_td_start_date(ln_time_definition_id, ld_date_earned);
514 fetch c_td_start_date into ld_start_date;
515 close c_td_start_date;
516
517 p_start_date := ld_start_date;
518
519 END start_tdptd_date;
520
521 end pyusexc;
[Home] [Help]