1 PACKAGE BODY pay_cn_exc AS
2 /* $Header: pycnexc.pkb 120.0 2005/05/29 01:58:35 appldev noship $ */
3
4 --------------------------------------------------------------------------
5 -- --
6 -- Name : NEXT_PERIOD --
7 -- Type : FUNCTION --
8 -- Access : Private --
9 -- Description : Given a date and a payroll action id, returns the --
10 -- date of the day after the end of the --
11 -- containing pay period. --
12 -- --
13 -- Parameters : --
14 -- IN : p_pactid NUMBER --
15 -- p_date DATE --
16 -- OUT : date --
17 -- --
18 -- Change History : --
19 --------------------------------------------------------------------------
20 -- Rev# Date Userid Description --
21 --------------------------------------------------------------------------
22 -- 1.0 18-MAR-03 saikrish Created this function --
23 --------------------------------------------------------------------------
24 FUNCTION next_period ( p_pactid IN NUMBER
25 , p_date IN DATE
26 )
27 RETURN DATE
28 IS
29
30 l_return_val DATE := NULL;
31
32 CURSOR cur_end_date ( p_pactid NUMBER
33 , p_date DATE
34 ) IS
35 SELECT TP.end_date + 1
36 FROM per_time_periods TP
37 , pay_payroll_actions PACT
38 WHERE PACT.payroll_action_id = p_pactid
39 AND PACT.payroll_id = TP.payroll_id
40 AND p_date BETWEEN TP.start_date AND TP.end_date;
41
42 BEGIN
43
44 OPEN cur_end_date ( p_pactid
45 , p_date
46 );
47 FETCH cur_end_date INTO l_return_val;
48 CLOSE cur_end_date;
49
50 RETURN l_return_val;
51
52 EXCEPTION
53 WHEN OTHERS THEN
54 IF cur_end_date%ISOPEN THEN
55 CLOSE cur_end_date;
56 END IF;
57
58 RAISE;
59 END next_period;
60
61 --------------------------------------------------------------------------
62 -- --
63 -- Name : NEXT_MONTH --
64 -- Type : FUNCTION --
65 -- Access : Private --
66 -- Description : Given a date, returns the date of the first day of --
67 -- the next month. --
68 -- --
69 -- Parameters : --
70 -- IN : p_date DATE --
71 -- OUT : date --
72 -- --
73 -- Change History : --
74 --------------------------------------------------------------------------
75 -- Rev# Date Userid Description --
76 --------------------------------------------------------------------------
77 -- 1.0 18-MAR-03 saikrish Created this function --
78 --------------------------------------------------------------------------
79 FUNCTION next_month ( p_date IN DATE
80 )
81 RETURN DATE
82 IS
83
84 BEGIN
85
86 RETURN TRUNC(add_months(p_date,1),'MM');
87
88 END next_month;
89
90 --------------------------------------------------------------------------
91 -- --
92 -- Name : NEXT_FISCAL_QUARTER --
93 -- Type : FUNCTION --
94 -- Access : Private --
95 -- Description : Given a date and fiscal year start returns the date --
96 -- of the first day of the next fiscal quarter. --
97 -- --
98 -- Parameters : --
99 -- IN : p_beg_of_fiscal_year DATE --
100 -- : p_date DATE --
101 -- OUT : date --
102 -- --
103 -- Change History : --
104 --------------------------------------------------------------------------
105 -- Rev# Date Userid Description --
106 --------------------------------------------------------------------------
107 -- 1.0 18-MAR-03 saikrish Created this function --
108 --------------------------------------------------------------------------
109 FUNCTION next_fiscal_quarter ( p_beg_of_fiscal_year IN DATE
110 , p_date IN DATE
111 )
112 RETURN DATE
113 IS
114
115 BEGIN
116
117 RETURN (ADD_MONTHS( p_beg_of_fiscal_year
118 , 3*(CEIL(MONTHS_BETWEEN(p_date+1,p_beg_of_fiscal_year)/3))));
119
120 END next_fiscal_quarter;
121
122 --------------------------------------------------------------------------
123 -- --
124 -- Name : NEXT_QUARTER --
125 -- Type : FUNCTION --
126 -- Access : Private --
127 -- Description : Given a date and fiscal year start returns the date --
128 -- of the first day of the next fiscal year. --
129 -- --
130 -- Parameters : --
131 -- IN : p_beg_of_fiscal_year DATE --
132 -- : p_date DATE --
133 -- OUT : date --
134 -- --
135 -- Change History : --
136 --------------------------------------------------------------------------
137 -- Rev# Date Userid Description --
138 --------------------------------------------------------------------------
139 -- 1.0 18-MAR-03 saikrish Created this function --
140 --------------------------------------------------------------------------
141 FUNCTION next_quarter ( p_date IN DATE
142 )
143 RETURN DATE
144 IS
145
146 BEGIN
147
148 RETURN TRUNC(ADD_MONTHS(p_date,3),'Q');
149
150 END next_quarter;
151
152 --------------------------------------------------------------------------
153 -- --
154 -- Name : NEXT_FISCAL_YEAR --
155 -- Type : FUNCTION --
156 -- Access : Private --
157 -- Description : Given a date and fiscal year start returns the date --
158 -- of the first day of the next fiscal year. --
159 -- --
160 -- Parameters : --
161 -- IN : p_beg_of_fiscal_year DATE --
162 -- : p_date DATE --
163 -- OUT : date --
164 -- --
165 -- Change History : --
166 --------------------------------------------------------------------------
167 -- Rev# Date Userid Description --
168 --------------------------------------------------------------------------
169 -- 1.0 18-MAR-03 saikrish Created this function --
170 --------------------------------------------------------------------------
171 FUNCTION next_fiscal_year ( p_beg_of_fiscal_year IN DATE
172 , p_date IN DATE
173 )
174 RETURN DATE
175 IS
176
177 BEGIN
178
179 RETURN (ADD_MONTHS( p_beg_of_fiscal_year
180 , 12*(CEIL(MONTHS_BETWEEN( p_date+1
181 , p_beg_of_fiscal_year)/12))));
182
183 END next_fiscal_year;
184
185 --------------------------------------------------------------------------
186 -- --
187 -- Name : NEXT_CALENDAR_YEAR --
188 -- Type : FUNCTION --
189 -- Access : Private --
190 -- Description : Given a date, returns the date of the first day of --
191 -- the next calendar year. --
192 -- --
193 -- Parameters : --
194 -- IN : p_date DATE --
195 -- OUT : date --
196 -- --
197 -- Change History : --
198 --------------------------------------------------------------------------
199 -- Rev# Date Userid Description --
200 --------------------------------------------------------------------------
201 -- 1.0 18-MAR-03 saikrish Created this function --
202 --------------------------------------------------------------------------
203 FUNCTION next_calendar_year ( p_date IN DATE
204 )
205 RETURN DATE
206 IS
207
208 BEGIN
209
210 RETURN TRUNC(ADD_MONTHS(p_date,12),'Y');
211
212 END next_calendar_year;
213
214 --------------------------------------------------------------------------
215 -- --
216 -- Name : DATE_EC --
217 -- Type : PROCEDURE --
218 -- Access : Public --
219 -- Description : This procedure assumes the date portion of the --
220 -- dimension name is always at the end to allow --
221 -- accurate identification since this is used for many --
222 -- dimensions. --
223 -- --
224 -- Parameters : --
225 -- IN : p_owner_payroll_action_id NUMBER --
226 -- p_user_payroll_action_id NUMBER --
227 -- p_owner_assignment_action_id NUMBER --
228 -- p_user_assignment_action_id NUMBER --
229 -- p_owner_effective_date DATE --
230 -- p_user_effective_date DATE --
231 -- p_dimension_name VARCHAR2 --
232 -- OUT : p_expiry_information NUMBER --
233 -- --
234 -- Change History : --
235 --------------------------------------------------------------------------
236 -- Rev# Date Userid Description --
237 --------------------------------------------------------------------------
238 -- 1.0 18-MAR-03 saikrish Created this procedure --
239 --------------------------------------------------------------------------
240 PROCEDURE date_ec ( p_owner_payroll_action_id IN NUMBER
241 , p_user_payroll_action_id IN NUMBER
242 , p_owner_assignment_action_id IN NUMBER
243 , p_user_assignment_action_id IN NUMBER
244 , p_owner_effective_date IN DATE
245 , p_user_effective_date IN DATE
246 , p_dimension_name IN VARCHAR2
247 , p_expiry_information OUT NOCOPY NUMBER
248 )
249 IS
250
251 l_beg_of_fiscal_year DATE := NULL;
252 l_expiry_date DATE := NULL;
253
254 CURSOR cur_beg_of_fiscal_year ( p_owner_payroll_action_id NUMBER ) IS
255 SELECT fnd_date.canonical_to_date(org_information11)
256 FROM pay_payroll_actions PACT
257 , hr_organization_information HOI
258 WHERE UPPER(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION'
259 AND HOI.organization_id = PACT.business_group_id
260 AND PACT.payroll_action_id = p_owner_payroll_action_id;
261
262 BEGIN
263
264 IF p_dimension_name LIKE '%RUN' THEN
265 -- must check for special case: if payroll action id's are the same,
266 -- then don't expire. This facilitates meaningful access of these
267 -- balances outside of runs.
268
269 IF p_owner_payroll_action_id <> p_user_payroll_action_id THEN
270 l_expiry_date := p_user_effective_date; -- always must expire.
271 ELSE
272 p_expiry_information := 0;
273 RETURN;
274 END IF;
275
276 ELSIF p_dimension_name LIKE '%PAYMENTS' THEN
277 -- must check for special case: if payroll action id's are the same,
278 -- then don't expire. This facilitates meaningful access of these
279 -- balances outside of runs.
280
281 IF p_owner_payroll_action_id <> p_user_payroll_action_id THEN
282 l_expiry_date := p_user_effective_date; -- always must expire.
283 ELSE
284 p_expiry_information := 0;
285 RETURN;
286 END IF;
287
288 ELSIF p_dimension_name LIKE '%PTD' THEN
289 l_expiry_date := next_period ( p_owner_payroll_action_id
290 , p_owner_effective_date
291 );
292
293 ELSIF p_dimension_name LIKE '%MTD' THEN
294 l_expiry_date := next_month ( p_owner_effective_date);
295
296 ELSIF p_dimension_name LIKE '%FY_QTD' THEN
297 OPEN cur_beg_of_fiscal_year ( p_owner_payroll_action_id);
298 FETCH cur_beg_of_fiscal_year
299 INTO l_beg_of_fiscal_year;
300 CLOSE cur_beg_of_fiscal_year;
301
302 l_expiry_date := next_fiscal_quarter ( l_beg_of_fiscal_year
303 , p_owner_effective_date
304 );
305
306 ELSIF p_dimension_name LIKE '%QTD' THEN
307 l_expiry_date := next_quarter ( p_owner_effective_date);
308
309 ELSIF p_dimension_name LIKE '%FY_YTD' THEN
310 OPEN cur_beg_of_fiscal_year ( p_owner_payroll_action_id);
311 FETCH cur_beg_of_fiscal_year
312 INTO l_beg_of_fiscal_year;
313 CLOSE cur_beg_of_fiscal_year;
314
315 l_expiry_date := next_fiscal_year ( l_beg_of_fiscal_year
316 , p_owner_effective_date
317 );
318
319 ELSIF p_dimension_name LIKE '%YTD' THEN
320 l_expiry_date := next_calendar_year ( p_owner_effective_date);
321
322 ELSIF p_dimension_name LIKE '%LTD' THEN
323 p_expiry_information := 0;
324 RETURN;
325
326 ELSE
327 hr_utility.set_message(801,'NO_EXP_CHECK_FOR_DIMENSION');
328 hr_utility.raise_error;
329
330 END IF;
331
332 IF p_user_effective_date >= l_expiry_date THEN
333 p_expiry_information := 1;
334 ELSE
335 p_expiry_information := 0;
336 END IF;
337
338 EXCEPTION
339 WHEN OTHERS THEN
340 IF cur_beg_of_fiscal_year%ISOPEN THEN
341 CLOSE cur_beg_of_fiscal_year;
342 END IF;
343
344 RAISE;
345 END date_ec;
346
347 --------------------------------------------------------------------------
348 -- --
349 -- Name : DATE_EC --
350 -- Type : PROCEDURE --
351 -- Access : Public --
352 -- Description : This procedure assumes the date portion of the --
353 -- dimension name is always at the end to allow --
354 -- accurate identification since this is used for many --
355 -- dimensions. --
356 -- --
357 -- Parameters : --
358 -- IN : p_owner_payroll_action_id NUMBER --
359 -- p_user_payroll_action_id NUMBER --
360 -- p_owner_assignment_action_id NUMBER --
361 -- p_user_assignment_action_id NUMBER --
362 -- p_owner_effective_date DATE --
363 -- p_user_effective_date DATE --
364 -- p_dimension_name VARCHAR2 --
365 -- OUT : p_expiry_information DATE --
366 -- --
367 -- Change History : --
368 --------------------------------------------------------------------------
369 -- Rev# Date Userid Description --
370 --------------------------------------------------------------------------
371 -- 1.0 18-MAY-03 bramajey Created this procedure --
372 --------------------------------------------------------------------------
373
374 PROCEDURE date_ec ( p_owner_payroll_action_id IN NUMBER
375 , p_user_payroll_action_id IN NUMBER
376 , p_owner_assignment_action_id IN NUMBER
377 , p_user_assignment_action_id IN NUMBER
378 , p_owner_effective_date IN DATE
379 , p_user_effective_date IN DATE
380 , p_dimension_name IN VARCHAR2
381 , p_expiry_information OUT NOCOPY DATE
382 )
383 IS
384
385 l_beg_of_fiscal_year DATE := NULL;
386 l_expiry_date DATE := NULL;
387
388 CURSOR cur_beg_of_fiscal_year ( p_owner_payroll_action_id NUMBER ) IS
389 SELECT fnd_date.canonical_to_date(org_information11)
390 FROM pay_payroll_actions PACT
391 , hr_organization_information HOI
392 WHERE UPPER(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION'
393 AND HOI.organization_id = PACT.business_group_id
394 AND PACT.payroll_action_id = p_owner_payroll_action_id;
395
396 BEGIN
397
398 IF p_dimension_name LIKE '%RUN' THEN
399 -- must check for special case: if payroll action id's are the same,
400 -- then don't expire. This facilitates meaningful access of these
401 -- balances outside of runs.
402
403 p_expiry_information := p_owner_effective_date;
404
405 ELSIF p_dimension_name LIKE '%PAYMENTS' THEN
406
407 p_expiry_information := p_owner_effective_date;
408
409 ELSIF p_dimension_name LIKE '%PTD' THEN
410 p_expiry_information := next_period ( p_owner_payroll_action_id
411 , p_owner_effective_date
412 ) - 1 ;
413
414 ELSIF p_dimension_name LIKE '%MTD' THEN
415 p_expiry_information := next_month ( p_owner_effective_date) - 1 ;
416
417 ELSIF p_dimension_name LIKE '%FY_QTD' THEN
418 OPEN cur_beg_of_fiscal_year ( p_owner_payroll_action_id);
419 FETCH cur_beg_of_fiscal_year
420 INTO l_beg_of_fiscal_year;
421 CLOSE cur_beg_of_fiscal_year;
422
423 p_expiry_information := next_fiscal_quarter ( l_beg_of_fiscal_year
424 , p_owner_effective_date
425 ) - 1 ;
426
427 ELSIF p_dimension_name LIKE '%QTD' THEN
428 p_expiry_information := next_quarter ( p_owner_effective_date) - 1 ;
429
430 ELSIF p_dimension_name LIKE '%FY_YTD' THEN
431 OPEN cur_beg_of_fiscal_year ( p_owner_payroll_action_id);
432 FETCH cur_beg_of_fiscal_year
433 INTO l_beg_of_fiscal_year;
434 CLOSE cur_beg_of_fiscal_year;
435
436 p_expiry_information := next_fiscal_year ( l_beg_of_fiscal_year
437 , p_owner_effective_date
438 ) - 1 ;
439
440 ELSIF p_dimension_name LIKE '%YTD' THEN
441 p_expiry_information := next_calendar_year ( p_owner_effective_date) - 1 ;
442
443 ELSIF p_dimension_name LIKE '%LTD' THEN
444 p_expiry_information := fnd_date.canonical_to_date('4712/12/31');
445
446 ELSE
447 hr_utility.set_message(801,'NO_EXP_CHECK_FOR_DIMENSION');
448 hr_utility.raise_error;
449
450 END IF;
451
452 EXCEPTION
453 WHEN OTHERS THEN
454 IF cur_beg_of_fiscal_year%ISOPEN THEN
455 CLOSE cur_beg_of_fiscal_year;
456 END IF;
457
458 RAISE;
459 END date_ec;
460
461 --------------------------------------------------------------------------
462 -- --
463 -- Name : START_CODE_P12MTH --
464 -- Type : PROCEDURE --
465 -- Access : Public --
466 -- Description : This procedure finds the start date based on the --
467 -- effective date for the dimension name _ASG_P12MTH --
468 -- --
469 -- Parameters : --
470 -- IN : p_effective_date DATE --
471 -- p_payroll_id NUMBER --
472 -- p_bus_grp NUMBER --
473 -- p_asg_action NUMBER --
474 -- OUT : p_start_date DATE --
475 -- RETURN : N/A --
476 -- --
477 -- Change History : --
478 --------------------------------------------------------------------------
479 -- Rev# Date Userid Description --
480 --------------------------------------------------------------------------
481 -- 1.0 23-Jul-2004 snekkala Created the procedure --
482 --------------------------------------------------------------------------
483 PROCEDURE start_code_p12mth ( p_effective_date IN DATE
484 , p_start_date OUT NOCOPY DATE
485 , p_payroll_id IN NUMBER
486 , p_bus_grp IN NUMBER
487 , p_asg_action IN NUMBER
488 )
489 IS
490 BEGIN
491 p_start_date := last_day(add_months(p_effective_date, -13))+1;
492 END start_code_p12mth;
493
494 --------------------------------------------------------------------------
495 -- --
496 -- Name : START_CODE_PMTH --
497 -- Type : PROCEDURE --
498 -- Access : Public --
499 -- Description : This procedure finds the start date based on the --
500 -- effective date for the dimension name _ASG_PMTH --
501 -- --
502 -- Parameters : --
503 -- IN : p_effective_date DATE --
504 -- p_payroll_id NUMBER --
505 -- p_bus_grp NUMBER --
506 -- p_asg_action NUMBER --
507 -- OUT : p_start_date DATE --
508 -- RETURN : N/A --
509 -- --
510 -- Change History : --
511 --------------------------------------------------------------------------
512 -- Rev# Date Userid Description --
513 --------------------------------------------------------------------------
514 -- 1.0 23-Jul-2004 snekkala Created the procedure --
515 --------------------------------------------------------------------------
516 PROCEDURE start_code_pmth ( p_effective_date IN DATE
517 , p_start_date OUT NOCOPY DATE
518 , p_payroll_id IN NUMBER
519 , p_bus_grp IN NUMBER
520 , p_asg_action IN NUMBER
521 )
522 IS
523 BEGIN
524 p_start_date := last_day(add_months(p_effective_date, -2))+1;
525 END start_code_pmth;
526
527 --------------------------------------------------------------------------
528 -- --
529 -- Name : START_CODE_PYEAR --
530 -- Type : PROCEDURE --
531 -- Access : Public --
532 -- Description : This procedure finds the start date based on the --
533 -- effective date for the dimension name _ASG_PYEAR --
534 -- --
535 -- Parameters : --
536 -- IN : p_effective_date DATE --
537 -- p_payroll_id NUMBER --
538 -- p_bus_grp NUMBER --
539 -- p_asg_action NUMBER --
540 -- OUT : p_start_date DATE --
541 -- RETURN : N/A --
542 -- --
543 -- Change History : --
544 --------------------------------------------------------------------------
545 -- Rev# Date Userid Description --
546 --------------------------------------------------------------------------
547 -- 1.0 23-Jul-2004 snekkala Created the procedure --
548 --------------------------------------------------------------------------
549 PROCEDURE start_code_pyear ( p_effective_date IN DATE
550 , p_start_date OUT NOCOPY DATE
551 , p_payroll_id IN NUMBER
552 , p_bus_grp IN NUMBER
553 , p_asg_action IN NUMBER
554 )
555 IS
556 BEGIN
557 p_start_date := trunc(add_months(p_effective_date, -12), 'Y');
558 END start_code_pyear;
559
560 END pay_cn_exc;
[Home] [Help]