1 PACKAGE BODY pyusexc AS
2 /* $Header: pyusexc.pkb 120.0 2005/05/29 02:19:21 appldev noship $ */
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
49 */
50
51 /*---------------------------- next_period -----------------------------------
52 NAME
53 next_period
54 DESCRIPTION
55 Given a date and a payroll action id, returns the date of the day after
56 the end of the containing pay period.
57 NOTES
58 <none>
59 */
60 FUNCTION next_period
61 (
62 p_pactid IN NUMBER,
63 p_date IN DATE
64 ) RETURN DATE is
65 l_return_val DATE;
66 BEGIN
67
68 l_return_val := NULL;
69
70 select TP.end_date + 1
71 into l_return_val
72 from per_time_periods TP,
73 pay_payroll_actions PACT
74 where PACT.payroll_action_id = p_pactid
75 and PACT.payroll_id = TP.payroll_id
76 and p_date between TP.start_date and TP.end_date;
77
78 RETURN l_return_val;
79
80 END next_period;
81
82 /*---------------------------- next_month ------------------------------------
83 NAME
84 next_month
85 DESCRIPTION
86 Given a date, returns the date of the first day of the next month.
87 NOTES
88 <none>
89 */
90 FUNCTION next_month
91 (
92 p_date IN DATE
93 ) return DATE is
94 BEGIN
95
96 RETURN trunc(add_months(p_date,1),'MM');
97
98 END next_month;
99
100 /*--------------------------- next_quarter -----------------------------------
101 NAME
102 next_quarter
103 DESCRIPTION
104 Given a date, returns the date of the first day of the next calendar
105 quarter.
106 NOTES
107 <none>
108 */
109 FUNCTION next_quarter
110 (
111 p_date IN DATE
112 ) RETURN DATE is
113 BEGIN
114
115 RETURN trunc(add_months(p_date,3),'Q');
116
117 END next_quarter;
118
119 /*---------------------------- next_year ------------------------------------
120 NAME
121 next_year
122 DESCRIPTION
123 Given a date, returns the date of the first day of the next calendar
124 year.
125 NOTES
126 <none>
127 */
128 FUNCTION next_year
129 (
130 p_date IN DATE
131 ) RETURN DATE is
132 BEGIN
133
134 RETURN trunc(add_months(p_date,12),'Y');
135
136 END next_year;
137
138 /*------------------------- next_fiscal_quarter -----------------------------
139 NAME
140 next_fiscal_quarter
141 DESCRIPTION
142 Given a date, returns the date of the first day of the next fiscal
143 quarter.
144 NOTES
145 <none>
146 */
147 FUNCTION next_fiscal_quarter
148 (
149 p_beg_of_fiscal_year IN DATE,
150 p_date IN DATE
151 ) RETURN DATE is
152
153 -- get offset of fiscal year start in relative months and days
154 l_fy_rel_month NUMBER(2);
155 l_fy_rel_day NUMBER(2);
156
157 BEGIN
158
159 l_fy_rel_month := to_char(p_beg_of_fiscal_year, 'MM') - 1;
160 l_fy_rel_day := to_char(p_beg_of_fiscal_year, 'DD') - 1;
161
162 RETURN (add_months(next_quarter(add_months(p_date, -l_fy_rel_month)
163 - l_fy_rel_day),
164 l_fy_rel_month) + l_fy_rel_day);
165
166 END next_fiscal_quarter;
167
168 /*--------------------------- next_fiscal_year ------------------------------
169 NAME
170 next_fiscal_year
171 DESCRIPTION
172 Given a date, returns the date of the first day of the next fiscal year.
173 NOTES
174 <none>
175 */
176 FUNCTION next_fiscal_year
177 (
178 p_beg_of_fiscal_year IN DATE,
179 p_date IN DATE
180 ) RETURN DATE is
181
182 -- get offset of fiscal year start relative to calendar year
183 l_fiscal_year_offset NUMBER(3);
184 BEGIN
185
186 l_fiscal_year_offset := to_char(p_beg_of_fiscal_year, 'DDD') - 1;
187
188 -- Bug 4100637: Instead of adding the offset to get the next
189 -- fiscal year date, just concatenated the current year
190 -- with the fiscal start month and date. Adding offset gives
191 -- one day less in case of leap year.
192 RETURN fnd_date.canonical_to_date(
193 to_char(next_year(p_date - l_fiscal_year_offset),'RRRR')||
194 substr(fnd_date.date_to_canonical(p_beg_of_fiscal_year),5,6));
195 -- RETURN (next_year(p_date - l_fiscal_year_offset) + l_fiscal_year_offset);
196
197 END next_fiscal_year;
198
199 /*------------------------------ date_ec ------------------------------------
200 NAME
201 date_ec
202 DESCRIPTION
203 Expiry checking code for the following date-related dimensions:
204 Assignment/Person/neither and GRE/not GRE and
205 Run/Period TD/Month/Quarter TD/Year TD/Fiscal Quarter TD/
206 Fiscal Year TD
207 NOTES
208 This procedure assumes the date portion of the dimension name
209 is always at the end to allow accurate identification since
210 this is used for many dimensions.
211 */
212 PROCEDURE date_ec
213 (
214 p_owner_payroll_action_id in number, -- run created balance.
215 p_user_payroll_action_id in number, -- current run.
216 p_owner_assignment_action_id in number, -- assact created balance.
217 p_user_assignment_action_id in number, -- current assact..
218 p_owner_effective_date in date, -- eff date of balance.
219 p_user_effective_date in date, -- eff date of current run.
220 p_dimension_name in varchar2, -- balance dimension name.
221 p_expiry_information out nocopy number -- dimension expired flag.
222 ) is
223
224 l_beg_of_fiscal_year DATE;
225 l_expiry_date DATE;
226
227 BEGIN
228
229 l_beg_of_fiscal_year := NULL;
230 l_expiry_date := NULL;
231
232 IF p_dimension_name like '%Run' THEN
233 -- must check for special case: if payroll action id's are the same,
234 -- then don't expire. This facilitates meaningful access of these
235 -- balances outside of runs.
236 IF p_owner_payroll_action_id <> p_user_payroll_action_id THEN
237 l_expiry_date := p_user_effective_date; -- always must expire.
238 ELSE
239 p_expiry_information := 0;
240 RETURN;
241 END IF;
242
243 ELSIF p_dimension_name like '%Payments%' THEN
244 -- must check for special case: if payroll action id's are the same,
245 -- then don't expire. This facilitates meaningful access of these
246 -- balances outside of runs.
247 IF p_owner_payroll_action_id <> p_user_payroll_action_id THEN
248 l_expiry_date := p_user_effective_date; -- always must expire.
249 ELSE
250 p_expiry_information := 0; -- daj
251 RETURN;
252 END IF;
253
254 ELSIF p_dimension_name like '%Period to Date' THEN
255 l_expiry_date := next_period(p_owner_payroll_action_id,
256 p_owner_effective_date);
257
258 ELSIF p_dimension_name like '%Month' THEN
259 l_expiry_date := next_month(p_owner_effective_date);
260
261 ELSIF p_dimension_name like '%Fiscal Quarter to Date' THEN
262 SELECT fnd_date.canonical_to_date(org_information11)
263 INTO l_beg_of_fiscal_year
264 FROM pay_payroll_actions PACT,
265 hr_organization_information HOI
266 WHERE UPPER(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION'
267 AND HOI.organization_id = PACT.business_group_id
268 AND PACT.payroll_action_id = p_owner_payroll_action_id;
269
270 l_expiry_date := next_fiscal_quarter(l_beg_of_fiscal_year,
271 p_owner_effective_date);
272
273 ELSIF p_dimension_name like '%Fiscal Year to Date' THEN
274 SELECT fnd_date.canonical_to_date(org_information11)
275 INTO l_beg_of_fiscal_year
276 FROM pay_payroll_actions PACT,
277 hr_organization_information HOI
278 WHERE UPPER(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION'
279 AND HOI.organization_id = PACT.business_group_id
280 AND PACT.payroll_action_id = p_owner_payroll_action_id;
281
282 l_expiry_date := next_fiscal_year(l_beg_of_fiscal_year,
283 p_owner_effective_date);
284
285 ELSIF p_dimension_name like '%Quarter to Date' THEN
286 l_expiry_date := next_quarter(p_owner_effective_date);
287
288 ELSIF p_dimension_name like '%Year to Date' THEN
289 l_expiry_date := next_year(p_owner_effective_date);
290
291 ELSIF p_dimension_name like '%Lifetime to Date' THEN
292 p_expiry_information := 0;
293 RETURN;
294
295 ELSE
296 hr_utility.set_message(801, 'NO_EXP_CHECK_FOR_THIS_DIMENSION');
297 hr_utility.raise_error;
298
299 END IF;
300
301 IF p_user_effective_date >= l_expiry_date THEN
302 p_expiry_information := 1;
303 ELSE
304 p_expiry_information := 0;
305 END IF;
306
307 END date_ec;
308
309
310 /* This procedure is the overlaoded function that will take care of the
311 of the requirement of Balance adjustment process.*/
312 PROCEDURE date_ec
313 (
314 p_owner_payroll_action_id in number, -- run created balance.
315 p_user_payroll_action_id in number, -- current run.
316 p_owner_assignment_action_id in number, -- assact created balance.
317 p_user_assignment_action_id in number, -- current assact..
318 p_owner_effective_date in date, -- eff date of balance.
319 p_user_effective_date in date, -- eff date of current run.
320 p_dimension_name in varchar2, -- balance dimension name.
321 p_expiry_information out nocopy Date -- dimension expired date.
322 ) is
323
324 l_beg_of_fiscal_year DATE;
325 l_expiry_date DATE;
326
327 BEGIN
328
329 l_beg_of_fiscal_year := NULL;
330 l_expiry_date := NULL;
331
332 If p_dimension_name like '%Run' THEN
333
334 -- must check for special case: if payroll action id's are the same,
335 -- then don't expire. This facilitates meaningful access of these
336 -- balances outside of runs.
337
338 p_expiry_information := p_owner_effective_date;
339
340 ELSIF p_dimension_name like '%Payments%' THEN
341
342 p_expiry_information := p_owner_effective_date;
343
344
345 ELSIF p_dimension_name like '%Period to Date' THEN
346
347 p_expiry_information := next_period(p_owner_payroll_action_id,
348 p_owner_effective_date) - 1;
349
350 ELSIF p_dimension_name like '%Month' THEN
351
352 p_expiry_information := next_month(p_owner_effective_date) - 1;
353
354 ELSIF p_dimension_name like '%Fiscal Quarter to Date' THEN
355 SELECT fnd_date.canonical_to_date(org_information11)
356 INTO l_beg_of_fiscal_year
357 FROM pay_payroll_actions PACT,
358 hr_organization_information HOI
359 WHERE UPPER(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION'
360 AND HOI.organization_id = PACT.business_group_id
361 AND PACT.payroll_action_id = p_owner_payroll_action_id;
362
363 p_expiry_information := next_fiscal_quarter(l_beg_of_fiscal_year,
364 p_owner_effective_date) - 1;
365
366 ELSIF p_dimension_name like '%Fiscal Year to Date' THEN
367 SELECT fnd_date.canonical_to_date(org_information11)
368 INTO l_beg_of_fiscal_year
369 FROM pay_payroll_actions PACT,
370 hr_organization_information HOI
371 WHERE UPPER(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION'
372 AND HOI.organization_id = PACT.business_group_id
373 AND PACT.payroll_action_id = p_owner_payroll_action_id;
374
375 p_expiry_information := next_fiscal_year(l_beg_of_fiscal_year,
376 p_owner_effective_date) - 1;
377
378
379 ELSIF p_dimension_name like '%Quarter to Date' THEN
380
381 p_expiry_information := next_quarter(p_owner_effective_date) - 1;
382
383 ELSIF p_dimension_name like '%Year to Date' THEN
384
385 p_expiry_information := next_year(p_owner_effective_date) -1;
386
387 ELSIF p_dimension_name like '%Lifetime to Date' THEN
388
389 p_expiry_information := fnd_date.canonical_to_date('4712/12/31');
390
391 ELSE
392
393 hr_utility.set_message(801, 'NO_EXP_CHECK_FOR_THIS_DIMENSION');
394 hr_utility.raise_error;
395
396 END IF;
397
398 END date_ec; /* date p_expiry information procedure overloaded function */
399
400
401 /*************************************************************************
402 ** Description - Procedure returns the start date for Time Definition
403 ** Period to Date which is run for validation the Run
404 ** Balance validation date
405 ** Arguments -
406 ** p_start_date - effective date of payroll action
407 ** p_payroll_id - payroll_id
408 ** p_bus_grp - Business Group ID
409 ** p_asg_action - Assignment Action ID
410 *************************************************************************/
411 PROCEDURE start_tdptd_date(p_effective_date IN DATE
412 ,p_start_date OUT NOCOPY DATE
413 ,p_payroll_id IN NUMBER DEFAULT NULL
414 ,p_bus_grp IN NUMBER DEFAULT NULL
415 ,p_asg_action IN NUMBER DEFAULT NULL)
416 IS
417
418 cursor c_asg_data(cp_asg_action in number) is
419 select nvl(ppa.date_earned, ppa.effective_date)
420 ,paa.assignment_id
421 from pay_assignment_actions paa
422 ,pay_payroll_actions ppa
423 where paa.assignment_action_id = cp_asg_action
424 and ppa.payroll_action_id = paa.payroll_Action_id;
425
426 cursor c_td_start_date(cp_time_definition_id number
427 ,cp_date_earned date) is
428 select ptp.start_date
429 from per_time_periods ptp
430 where ptp.time_definition_id = cp_time_definition_id
431 and cp_date_earned between ptp.start_date
432 and ptp.end_date;
433
434 ln_time_definition_id NUMBER;
435 ln_assignment_id NUMBER;
436 ld_date_earned DATE;
437 ld_start_date DATE;
438
439 BEGIN
440 ld_date_earned := p_effective_date;
441
442 open c_asg_data(p_asg_action);
443 fetch c_asg_data into ld_date_earned, ln_assignment_id;
444 close c_asg_data;
445
446 pay_us_rules.get_time_def_for_entry (
447 p_element_entry_id => null
448 ,p_assignment_id => ln_assignment_id
449 ,p_assignment_action_id => p_asg_action
450 ,p_business_group_id => p_bus_grp
451 ,p_time_definition_id => ln_time_definition_id);
452
453 open c_td_start_date(ln_time_definition_id, ld_date_earned);
454 fetch c_td_start_date into ld_start_date;
455 close c_td_start_date;
456
457 p_start_date := ld_start_date;
458
459 END start_tdptd_date;
460
461 end pyusexc;
[Home] [Help]