1 PACKAGE BODY PAY_NL_DIM_PKG AS
2 /* $Header: pynlexc.pkb 120.2 2005/09/30 07:57:23 gkhangar noship $ */
3 /*------------------------------ ASG_PTD_EC ----------------------------*/
4 /*
5 NAME
6 ASG_PTD_EC - Assignment-level Period to Date expiry check.
7 DESCRIPTION
8 Expiry checking code for the following:
9 NL Assignment-level Period To Date Balance Dimension
10 NOTES
11 The associated dimension is expiry checked at assignment action level
12 */
13 procedure ASG_PTD_EC
14 (
15 p_owner_payroll_action_id in number, -- run created balance.
16 p_user_payroll_action_id in number, -- current run.
17 p_owner_assignment_action_id in number, -- assact created balance.
18 p_user_assignment_action_id in number, -- current assact..
19 p_owner_effective_date in date, -- eff date of balance.
20 p_user_effective_date in date, -- eff date of current run.
21 p_dimension_name in varchar2, -- balance dimension name.
22 p_expiry_information out nocopy number -- dimension expired flag.
23 ) IS
24 l_period_start_date date;
25 BEGIN
26 select ptp.start_date
27 into l_period_start_date
28 from per_time_periods ptp, pay_payroll_actions ppa
29 where ppa.payroll_action_id = p_user_payroll_action_id
30 and ppa.payroll_id = ptp.payroll_id
31 and p_user_effective_date between ptp.start_date and ptp.end_date;
32 -- see if balance was written in this period. If not it is expired
33 IF p_owner_effective_date >= l_period_start_date THEN
34 p_expiry_information := 0;
35 ELSE
36 p_expiry_information := 1;
37 END IF;
38 END;
39 procedure ASG_PTD_EC
40 (
41 p_owner_payroll_action_id in number, -- run created balance.
42 p_user_payroll_action_id in number, -- current run.
43 p_owner_assignment_action_id in number, -- assact created balance.
44 p_user_assignment_action_id in number, -- current assact..
45 p_owner_effective_date in date, -- eff date of balance.
46 p_user_effective_date in date, -- eff date of current run.
47 p_dimension_name in varchar2, -- balance dimension name.
48 p_expiry_information out nocopy DATE -- dimension expired flag.
49 ) IS
50 BEGIN
51 SELECT TP.end_date
52 INTO p_expiry_information
53 FROM per_time_periods TP
54 ,pay_payroll_actions PACT
55 WHERE PACT.payroll_action_id = p_owner_payroll_action_id
56 AND PACT.payroll_id = TP.payroll_id
57 AND p_owner_effective_date BETWEEN TP.start_date AND TP.end_date;
58 END;
59 --
60 --3019423
61 --Expiry checking logic for _PER_PTD
62 --
63 /*------------------------------ PER_PTD_EC ----------------------------*/
64 /*
65 NAME
66 PER_PTD_EC - Person Level Period to Date expiry check.
67 DESCRIPTION
68 Expiry checking code for the following:
69 NL Person-level Period To Date Balance Dimension
70 NOTES
71 The associated dimension is expiry checked at Person level
72 */
73 procedure PER_PTD_EC
74 (
75 p_owner_payroll_action_id in number, -- run created balance.
76 p_user_payroll_action_id in number, -- current run.
77 p_owner_assignment_action_id in number, -- assact created balance.
78 p_user_assignment_action_id in number, -- current assact..
79 p_owner_effective_date in date, -- eff date of balance.
80 p_user_effective_date in date, -- eff date of current run.
81 p_dimension_name in varchar2, -- balance dimension name.
82 p_expiry_information out nocopy number -- dimension expired flag.
83 ) IS
84 l_period_start_date date;
85 BEGIN
86 select ptp.start_date
87 into l_period_start_date
88 from per_time_periods ptp, pay_payroll_actions ppa
89 where ppa.payroll_action_id = p_user_payroll_action_id
90 and ppa.payroll_id = ptp.payroll_id
91 and p_user_effective_date between ptp.start_date and ptp.end_date;
92 -- see if balance was written in this period. If not it is expired
93 IF p_owner_effective_date >= l_period_start_date THEN
94 p_expiry_information := 0;
95 ELSE
96 p_expiry_information := 1;
97 END IF;
98 END;
99 procedure PER_PTD_EC
100 (
101 p_owner_payroll_action_id in number, -- run created balance.
102 p_user_payroll_action_id in number, -- current run.
103 p_owner_assignment_action_id in number, -- assact created balance.
104 p_user_assignment_action_id in number, -- current assact..
105 p_owner_effective_date in date, -- eff date of balance.
106 p_user_effective_date in date, -- eff date of current run.
107 p_dimension_name in varchar2, -- balance dimension name.
108 p_expiry_information out nocopy DATE -- dimension expired flag.
109 ) IS
110 BEGIN
111 SELECT TP.end_date
112 INTO p_expiry_information
113 FROM per_time_periods TP
114 ,pay_payroll_actions PACT
115 WHERE PACT.payroll_action_id = p_owner_payroll_action_id
116 AND PACT.payroll_id = TP.payroll_id
117 AND p_owner_effective_date BETWEEN TP.start_date AND TP.end_date;
118 END;
119 /*------------------------------ ASG_YTD_EC -------------------------*/
120 /*
121 NAME
122 ASG_YTD_EC - Assignment Tax Year to Date expiry check
123 DESCRIPTION
124 Expiry checking code for the following:
125 NL Assignment-level Tax Year to Date dimension
126 NOTES
127 The associated dimension is expiry checked at assignment action level
128 */
129 procedure ASG_YTD_EC
130 (
131 p_owner_payroll_action_id in number, -- run created balance.
132 p_user_payroll_action_id in number, -- current run.
133 p_owner_assignment_action_id in number, -- assact created balance.
134 p_user_assignment_action_id in number, -- current assact..
135 p_owner_effective_date in date, -- eff date of balance.
136 p_user_effective_date in date, -- eff date of current run.
137 p_dimension_name in varchar2, -- balance dimension name.
138 p_expiry_information out nocopy number -- dimension expired flag.
139 ) IS
140 BEGIN
141 if p_owner_effective_date >= trunc(p_user_effective_date,'Y') then
142 p_expiry_information := 0;
143 else
144 p_expiry_information := 1;
145 end if;
146 END;
147 procedure ASG_YTD_EC
148 (
149 p_owner_payroll_action_id in number, -- run created balance.
150 p_user_payroll_action_id in number, -- current run.
151 p_owner_assignment_action_id in number, -- assact created balance.
152 p_user_assignment_action_id in number, -- current assact..
153 p_owner_effective_date in date, -- eff date of balance.
154 p_user_effective_date in date, -- eff date of current run.
155 p_dimension_name in varchar2, -- balance dimension name.
156 p_expiry_information out nocopy DATE -- dimension expired flag.
157 ) IS
158 BEGIN
159 p_expiry_information := TRUNC(ADD_MONTHS(p_owner_effective_date, 12), 'Y')-1;
160 END;
161 --
162 --3019423
163 --Expiry checking logic for _PER_YTD
164 --
165 /*------------------------------ PER_YTD_EC -------------------------*/
166 /*
167 NAME
168 PER_YTD_EC - Person Tax Year to Date expiry check
169 DESCRIPTION
170 Expiry checking code for the following:
171 NL Person-level Tax Year to Date dimension
172 NOTES
173 The associated dimension is expiry checked at person level
174 */
175 procedure PER_YTD_EC
176 (
177 p_owner_payroll_action_id in number, -- run created balance.
178 p_user_payroll_action_id in number, -- current run.
179 p_owner_assignment_action_id in number, -- assact created balance.
180 p_user_assignment_action_id in number, -- current assact..
181 p_owner_effective_date in date, -- eff date of balance.
182 p_user_effective_date in date, -- eff date of current run.
183 p_dimension_name in varchar2, -- balance dimension name.
184 p_expiry_information out nocopy number -- dimension expired flag.
185 ) IS
186 BEGIN
187 if p_owner_effective_date >= trunc(p_user_effective_date,'Y') then
188 p_expiry_information := 0;
189 else
190 p_expiry_information := 1;
191 end if;
192 END;
193 procedure PER_YTD_EC
194 (
195 p_owner_payroll_action_id in number, -- run created balance.
196 p_user_payroll_action_id in number, -- current run.
197 p_owner_assignment_action_id in number, -- assact created balance.
198 p_user_assignment_action_id in number, -- current assact..
199 p_owner_effective_date in date, -- eff date of balance.
200 p_user_effective_date in date, -- eff date of current run.
201 p_dimension_name in varchar2, -- balance dimension name.
202 p_expiry_information out nocopy DATE -- dimension expired flag.
203 ) IS
204 BEGIN
205 p_expiry_information := TRUNC(ADD_MONTHS(p_owner_effective_date, 12), 'Y')-1;
206 END;
207 /*------------------------------ ASG_QTD_EC -------------------------*/
208 /*
209 NAME
210 ASG_QTD_EC - Assignment Tax Year to Date expiry check
211 DESCRIPTION
212 Expiry checking code for the following:
213 NL Assignment-level Tax Quarter to Date dimension
214 NOTES
215 The associated dimension is expiry checked at assignment action level
216 */
217 procedure ASG_QTD_EC
218 (
219 p_owner_payroll_action_id in number, -- run created balance.
220 p_user_payroll_action_id in number, -- current run.
221 p_owner_assignment_action_id in number, -- assact created balance.
222 p_user_assignment_action_id in number, -- current assact..
223 p_owner_effective_date in date, -- eff date of balance.
224 p_user_effective_date in date, -- eff date of current run.
225 p_dimension_name in varchar2, -- balance dimension name.
226 p_expiry_information out nocopy number -- dimension expired flag.
227 ) IS
228 BEGIN
229 IF p_user_effective_date >= trunc(add_months(p_owner_effective_date,3),'Q') THEN
230 p_expiry_information := 1; -- Expired
231 ELSE
232 p_expiry_information := 0; -- Not Expired
233 END IF;
234 END;
235 procedure ASG_QTD_EC
236 (
237 p_owner_payroll_action_id in number, -- run created balance.
238 p_user_payroll_action_id in number, -- current run.
239 p_owner_assignment_action_id in number, -- assact created balance.
240 p_user_assignment_action_id in number, -- current assact..
241 p_owner_effective_date in date, -- eff date of balance.
242 p_user_effective_date in date, -- eff date of current run.
243 p_dimension_name in varchar2, -- balance dimension name.
244 p_expiry_information out nocopy DATE -- dimension expired flag.
245 ) IS
246 BEGIN
247 p_expiry_information := TRUNC(ADD_MONTHS(p_owner_effective_date, 3), 'Q')-1;
248 END;
249 --
250 --3019423
251 --Expiry checking logic for _PER_QTD
252 --
253 /*------------------------------ PER_QTD_EC -------------------------*/
254 /*
255 NAME
256 PER_QTD_EC - Person Quarter to Date expiry check
257 DESCRIPTION
258 Expiry checking code for the following:
259 NL Person-level Tax Quarter to Date dimension
260 NOTES
261 The associated dimension is expiry checked at Person level
262 */
263 procedure PER_QTD_EC
264 (
265 p_owner_payroll_action_id in number, -- run created balance.
266 p_user_payroll_action_id in number, -- current run.
267 p_owner_assignment_action_id in number, -- assact created balance.
268 p_user_assignment_action_id in number, -- current assact..
269 p_owner_effective_date in date, -- eff date of balance.
270 p_user_effective_date in date, -- eff date of current run.
271 p_dimension_name in varchar2, -- balance dimension name.
272 p_expiry_information out nocopy number -- dimension expired flag.
273 ) IS
274 BEGIN
275 IF p_user_effective_date >= trunc(add_months(p_owner_effective_date,3),'Q') THEN
276 p_expiry_information := 1; -- Expired
277 ELSE
278 p_expiry_information := 0; -- Not Expired
279 END IF;
280 END;
281 procedure PER_QTD_EC
282 (
283 p_owner_payroll_action_id in number, -- run created balance.
284 p_user_payroll_action_id in number, -- current run.
285 p_owner_assignment_action_id in number, -- assact created balance.
286 p_user_assignment_action_id in number, -- current assact..
287 p_owner_effective_date in date, -- eff date of balance.
288 p_user_effective_date in date, -- eff date of current run.
289 p_dimension_name in varchar2, -- balance dimension name.
290 p_expiry_information out nocopy DATE -- dimension expired flag.
291 ) IS
292 BEGIN
293 p_expiry_information := TRUNC(ADD_MONTHS(p_owner_effective_date, 3), 'Q')-1;
294 END;
295 /*------------------------------ ASG_MON_EC -------------------------*/
296 /*
297 NAME
298 ASG_MON_EC - Assignment Tax Year to Date expiry check
299 DESCRIPTION
300 Expiry checking code for the following:
301 NL Assignment-level Tax Month dimension
302 NOTES
303 The associated dimension is expiry checked at assignment action level
304 */
305 procedure ASG_MON_EC
306 (
307 p_owner_payroll_action_id in number, -- run created balance.
308 p_user_payroll_action_id in number, -- current run.
309 p_owner_assignment_action_id in number, -- assact created balance.
310 p_user_assignment_action_id in number, -- current assact..
311 p_owner_effective_date in date, -- eff date of balance.
312 p_user_effective_date in date, -- eff date of current run.
313 p_dimension_name in varchar2, -- balance dimension name.
314 p_expiry_information out nocopy number -- dimension expired flag.
315 ) IS
316 BEGIN
317 if p_owner_effective_date >= trunc(add_months(p_user_effective_date,1),'MM') then
318 p_expiry_information := 0;
319 else
320 p_expiry_information := 1;
321 end if;
322 END;
323 procedure ASG_MON_EC
324 (
325 p_owner_payroll_action_id in number, -- run created balance.
326 p_user_payroll_action_id in number, -- current run.
330 p_user_effective_date in date, -- eff date of current run.
327 p_owner_assignment_action_id in number, -- assact created balance.
328 p_user_assignment_action_id in number, -- current assact..
329 p_owner_effective_date in date, -- eff date of balance.
331 p_dimension_name in varchar2, -- balance dimension name.
332 p_expiry_information out nocopy DATE -- dimension expired flag.
333 ) IS
334 BEGIN
335 p_expiry_information := TRUNC(ADD_MONTHS(p_owner_effective_date, 1), 'MM')-1;
336 END;
337 /*------------------------------ ASG_PROC_PTD_EC ----------------------------*/
338 /*
339 NAME
340 ASG_PROC_PTD_EC - Assignment Processing Period to Date expiry check.
341 DESCRIPTION
342 Expiry checking code for the following:
343 NL Element-level Process Period To Date Balance Dimension
344 NOTES
345 The associtated dimension is expiry checked at payroll action level
346 */
347 procedure ASG_PROC_PTD_EC
348 (
349 p_owner_payroll_action_id in number, -- run created balance.
350 p_user_payroll_action_id in number, -- current run.
351 p_owner_assignment_action_id in number, -- assact created balance.
352 p_user_assignment_action_id in number, -- current assact..
353 p_owner_effective_date in date, -- eff date of balance.
354 p_user_effective_date in date, -- eff date of current run.
355 p_dimension_name in varchar2, -- balance dimension name.
356 p_expiry_information out nocopy number -- dimension expired flag.
357 ) IS
358 l_period_start_date date;
359 BEGIN
360 select ptp.start_date
361 into l_period_start_date
362 from per_time_periods ptp, pay_payroll_actions ppa
363 where ppa.payroll_action_id = p_user_payroll_action_id
364 and ppa.payroll_id = ptp.payroll_id
365 and p_user_effective_date between ptp.start_date and ptp.end_date;
366 -- see if balance was written in this period. If not it is expired
367 IF p_owner_effective_date >= l_period_start_date THEN
368 p_expiry_information := 0;
369 ELSE
370 p_expiry_information := 1;
371 END IF;
372 END;
373 procedure ASG_PROC_PTD_EC
374 (
375 p_owner_payroll_action_id in number, -- run created balance.
376 p_user_payroll_action_id in number, -- current run.
377 p_owner_assignment_action_id in number, -- assact created balance.
378 p_user_assignment_action_id in number, -- current assact..
379 p_owner_effective_date in date, -- eff date of balance.
380 p_user_effective_date in date, -- eff date of current run.
381 p_dimension_name in varchar2, -- balance dimension name.
382 p_expiry_information out nocopy DATE -- dimension expired flag.
383 ) IS
384 BEGIN
385 select ptp.END_DATE
386 into p_expiry_information
387 from per_time_periods ptp, pay_payroll_actions ppa
388 where ppa.payroll_action_id = p_user_payroll_action_id
389 and ppa.payroll_id = ptp.payroll_id
390 and p_owner_effective_date between ptp.start_date and ptp.end_date;
391 END;
392 /*------------------------------ ASG_SIT_PTD_EC ----------------------------*/
393 /*
394 NAME
395 ASG_SIT_PTD_EC - Assignment SI Type Period to Date expiry check.
396 DESCRIPTION
397 Expiry checking code for the following:
398 NL Element-level Process Period To Date Balance Dimension
399 NOTES
400 The associtated dimension is expiry checked at payroll action level
401 */
402 procedure ASG_SIT_PTD_EC
403 (
404 p_owner_payroll_action_id in number, -- run created balance.
405 p_user_payroll_action_id in number, -- current run.
406 p_owner_assignment_action_id in number, -- assact created balance.
407 p_user_assignment_action_id in number, -- current assact..
408 p_owner_effective_date in date, -- eff date of balance.
409 p_user_effective_date in date, -- eff date of current run.
410 p_dimension_name in varchar2, -- balance dimension name.
411 p_expiry_information out nocopy number -- dimension expired flag.
412 ) IS
413 l_period_start_date date;
414 BEGIN
415 select ptp.start_date
416 into l_period_start_date
417 from per_time_periods ptp, pay_payroll_actions ppa
418 where ppa.payroll_action_id = p_user_payroll_action_id
419 and ppa.payroll_id = ptp.payroll_id
420 and p_user_effective_date between ptp.start_date and ptp.end_date;
421 -- see if balance was written in this period. If not it is expired
422 IF p_owner_effective_date >= l_period_start_date THEN
423 p_expiry_information := 0;
424 ELSE
425 p_expiry_information := 1;
426 END IF;
427 END;
428 procedure ASG_SIT_PTD_EC
429 (
430 p_owner_payroll_action_id in number, -- run created balance.
431 p_user_payroll_action_id in number, -- current run.
432 p_owner_assignment_action_id in number, -- assact created balance.
433 p_user_assignment_action_id in number, -- current assact..
437 p_expiry_information out nocopy DATE -- dimension expired flag.
434 p_owner_effective_date in date, -- eff date of balance.
435 p_user_effective_date in date, -- eff date of current run.
436 p_dimension_name in varchar2, -- balance dimension name.
438 ) IS
439 BEGIN
440 SELECT TP.end_date
441 INTO p_expiry_information
442 FROM per_time_periods TP
443 ,pay_payroll_actions PACT
444 WHERE PACT.payroll_action_id = p_owner_payroll_action_id
445 AND PACT.payroll_id = TP.payroll_id
446 AND p_owner_effective_date BETWEEN TP.start_date AND TP.end_date;
447 END;
448 /*------------------------------ ASG_SIT_YTD_EC ----------------------------*/
449 /*
450 NAME
451 ASG_SIT_YTD_EC - Assignment SI Type Tax Year to Date expiry check.
452 DESCRIPTION
453 Expiry checking code for the following:
454 NL Element-level Process Period To Date Balance Dimension
455 NOTES
456 The associtated dimension is expiry checked at payroll action level
457 */
458 procedure ASG_SIT_YTD_EC
459 (
460 p_owner_payroll_action_id in number, -- run created balance.
461 p_user_payroll_action_id in number, -- current run.
462 p_owner_assignment_action_id in number, -- assact created balance.
463 p_user_assignment_action_id in number, -- current assact..
464 p_owner_effective_date in date, -- eff date of balance.
465 p_user_effective_date in date, -- eff date of current run.
466 p_dimension_name in varchar2, -- balance dimension name.
467 p_expiry_information out nocopy number -- dimension expired flag.
468 ) IS
469 BEGIN
470 if p_owner_effective_date >= trunc(p_user_effective_date,'Y') then
471 p_expiry_information := 0;
472 else
473 p_expiry_information := 1;
474 end if;
475 END;
476 procedure ASG_SIT_YTD_EC
477 (
478 p_owner_payroll_action_id in number, -- run created balance.
479 p_user_payroll_action_id in number, -- current run.
480 p_owner_assignment_action_id in number, -- assact created balance.
481 p_user_assignment_action_id in number, -- current assact..
482 p_owner_effective_date in date, -- eff date of balance.
483 p_user_effective_date in date, -- eff date of current run.
484 p_dimension_name in varchar2, -- balance dimension name.
485 p_expiry_information out nocopy DATE -- dimension expired flag.
486 ) IS
487 BEGIN
488 p_expiry_information := TRUNC(ADD_MONTHS(p_owner_effective_date, 12), 'Y')-1;
489 END;
490 /*------------------------------ ASG_SIT_QTD_EC ----------------------------*/
491 /*
492 NAME
493 ASG_SIT_QTD_EC - Assignment SI Type Tax Quarter to Date expiry check.
497 NOTES
494 DESCRIPTION
495 Expiry checking code for the following:
496 NL Element-level Process Quarter To Date Balance Dimension
498 The associtated dimension is expiry checked at payroll action level
499 */
500 procedure ASG_SIT_QTD_EC
501 (
502 p_owner_payroll_action_id in number, -- run created balance.
503 p_user_payroll_action_id in number, -- current run.
504 p_owner_assignment_action_id in number, -- assact created balance.
505 p_user_assignment_action_id in number, -- current assact..
506 p_owner_effective_date in date, -- eff date of balance.
507 p_user_effective_date in date, -- eff date of current run.
508 p_dimension_name in varchar2, -- balance dimension name.
509 p_expiry_information out nocopy number -- dimension expired flag.
510 ) IS
511 BEGIN
512 IF p_user_effective_date >= trunc(add_months(p_owner_effective_date,3),'Q') THEN
513 p_expiry_information := 1; -- Expired
514 ELSE
515 p_expiry_information := 0; -- Not Expired
516 END IF;
517 END;
518 procedure ASG_SIT_QTD_EC
519 (
520 p_owner_payroll_action_id in number, -- run created balance.
521 p_user_payroll_action_id in number, -- current run.
522 p_owner_assignment_action_id in number, -- assact created balance.
523 p_user_assignment_action_id in number, -- current assact..
524 p_owner_effective_date in date, -- eff date of balance.
525 p_user_effective_date in date, -- eff date of current run.
526 p_dimension_name in varchar2, -- balance dimension name.
527 p_expiry_information out nocopy DATE -- dimension expired flag.
528 ) IS
529 BEGIN
530 p_expiry_information := TRUNC(ADD_MONTHS(p_owner_effective_date, 3), 'Q')-1;
531 END;
532 /*------------------------------ ASG_SIT_MON_EC ----------------------------*/
533 /*
534 NAME
535 ASG_SIT_MON_EC - Assignment SI Type Tax Quarter to Date expiry check.
536 DESCRIPTION
537 Expiry checking code for the following:
538 NL Element-level Process Month Balance Dimension
539 NOTES
540 The associtated dimension is expiry checked at payroll action level
541 */
542 procedure ASG_SIT_MON_EC
543 (
544 p_owner_payroll_action_id in number, -- run created balance.
545 p_user_payroll_action_id in number, -- current run.
546 p_owner_assignment_action_id in number, -- assact created balance.
547 p_user_assignment_action_id in number, -- current assact..
548 p_owner_effective_date in date, -- eff date of balance.
549 p_user_effective_date in date, -- eff date of current run.
550 p_dimension_name in varchar2, -- balance dimension name.
551 p_expiry_information out nocopy number -- dimension expired flag.
552 ) IS
553 BEGIN
554 if p_owner_effective_date >= trunc(add_months(p_user_effective_date,1),'MM') then
555 p_expiry_information := 0;
556 else
557 p_expiry_information := 1;
558 end if;
559 END;
560 procedure ASG_SIT_MON_EC
561 (
562 p_owner_payroll_action_id in number, -- run created balance.
563 p_user_payroll_action_id in number, -- current run.
564 p_owner_assignment_action_id in number, -- assact created balance.
565 p_user_assignment_action_id in number, -- current assact..
566 p_owner_effective_date in date, -- eff date of balance.
567 p_user_effective_date in date, -- eff date of current run.
568 p_dimension_name in varchar2, -- balance dimension name.
569 p_expiry_information out nocopy DATE -- dimension expired flag.
570 ) IS
571 BEGIN
572 p_expiry_information := TRUNC(ADD_MONTHS(p_owner_effective_date, 1), 'MM')-1;
573 END;
574 /*------------------------------ ASG_ITD_EC ----------------------------*/
575 /*
576 NAME
577 ASG_ITD_EC - Assignment Inception To Date expiry check.
578 DESCRIPTION
579 Expiry checking code for the following:
580 NL Element-level Process Inception To Date Balance Dimension
581 NOTES
582 The associtated dimension is expiry checked at payroll action level
583 */
584 procedure ASG_ITD_EC
585 (
586 p_owner_payroll_action_id in number, -- run created balance.
587 p_user_payroll_action_id in number, -- current run.
588 p_owner_assignment_action_id in number, -- assact created balance.
589 p_user_assignment_action_id in number, -- current assact..
590 p_owner_effective_date in date, -- eff date of balance.
591 p_user_effective_date in date, -- eff date of current run.
592 p_dimension_name in varchar2, -- balance dimension name.
593 p_expiry_information out nocopy number -- dimension expired flag.
594 ) IS
595 BEGIN
596 p_expiry_information := 0;
597 END;
598 procedure ASG_ITD_EC
599 (
600 p_owner_payroll_action_id in number, -- run created balance.
601 p_user_payroll_action_id in number, -- current run.
602 p_owner_assignment_action_id in number, -- assact created balance.
603 p_user_assignment_action_id in number, -- current assact..
604 p_owner_effective_date in date, -- eff date of balance.
605 p_user_effective_date in date, -- eff date of current run.
606 p_dimension_name in varchar2, -- balance dimension name.
607 p_expiry_information out nocopy DATE -- dimension expired flag.
608 ) IS
609 BEGIN
610 p_expiry_information := HR_GENERAL.END_OF_TIME;
611 END;
612 --
613 --3019423
614 --Expiry checking logic for _PER_ITD
615 --
616 /*------------------------------PER_ITD_EC ----------------------------*/
617 /*
618 NAME
619 PER_ITD_EC - Person Inception To Date expiry check.
620 DESCRIPTION
621 Expiry checking code for the following:
622 NL Person Inception To Date Balance Dimension
623 NOTES
624 The associated dimension is expiry checked at Person level
625 */
626 procedure PER_ITD_EC
627 (
628 p_owner_payroll_action_id in number, -- run created balance.
629 p_user_payroll_action_id in number, -- current run.
630 p_owner_assignment_action_id in number, -- assact created balance.
631 p_user_assignment_action_id in number, -- current assact..
632 p_owner_effective_date in date, -- eff date of balance.
633 p_user_effective_date in date, -- eff date of current run.
634 p_dimension_name in varchar2, -- balance dimension name.
635 p_expiry_information out nocopy number -- dimension expired flag.
639 END;
636 ) IS
637 BEGIN
638 p_expiry_information := 0;
640 procedure PER_ITD_EC
641 (
642 p_owner_payroll_action_id in number, -- run created balance.
643 p_user_payroll_action_id in number, -- current run.
644 p_owner_assignment_action_id in number, -- assact created balance.
645 p_user_assignment_action_id in number, -- current assact..
646 p_owner_effective_date in date, -- eff date of balance.
647 p_user_effective_date in date, -- eff date of current run.
648 p_dimension_name in varchar2, -- balance dimension name.
649 p_expiry_information out nocopy DATE -- dimension expired flag.
650 ) IS
651 BEGIN
652 p_expiry_information := HR_GENERAL.END_OF_TIME;
653 END;
654 /*------------------------------ ASG_RUN_EC ----------------------------*/
655 /*
656 NAME
657 ASG_RUN_EC - Assignment Run expiry check.
658 DESCRIPTION
659 Expiry checking code for the following:
660 NL Element-level Process Period To Date Balance Dimension
661 NOTES
662 The associtated dimension is expiry checked at payroll action level
663 */
664 procedure ASG_RUN_EC
665 (
666 p_owner_payroll_action_id in number, -- run created balance.
667 p_user_payroll_action_id in number, -- current run.
668 p_owner_assignment_action_id in number, -- assact created balance.
669 p_user_assignment_action_id in number, -- current assact..
670 p_owner_effective_date in date, -- eff date of balance.
671 p_user_effective_date in date, -- eff date of current run.
672 p_dimension_name in varchar2, -- balance dimension name.
673 p_expiry_information out nocopy number -- dimension expired flag.
674 ) IS
675 BEGIN
676 if p_user_payroll_action_id = p_owner_payroll_action_id then
677 p_expiry_information := 0;
678 else
679 p_expiry_information := 1;
680 end if;
681 END;
682 /*------------------------------ ASG_SIT_RUN_EC ----------------------------*/
683 /*
684 NAME
685 ASG_SIT_RUN_EC - Assignment Run SI Type Date expiry check.
686 DESCRIPTION
687 Expiry checking code for the following:
688 NL Element-level Process Period To Date Balance Dimension
689 NOTES
690 The associtated dimension is expiry checked at payroll action level
691 */
692 procedure ASG_SIT_RUN_EC
693 (
694 p_owner_payroll_action_id in number, -- run created balance.
695 p_user_payroll_action_id in number, -- current run.
696 p_owner_assignment_action_id in number, -- assact created balance.
697 p_user_assignment_action_id in number, -- current assact..
698 p_owner_effective_date in date, -- eff date of balance.
699 p_user_effective_date in date, -- eff date of current run.
700 p_dimension_name in varchar2, -- balance dimension name.
701 p_expiry_information out nocopy number -- dimension expired flag.
702 ) IS
703 BEGIN
704 if p_user_payroll_action_id = p_owner_payroll_action_id then
705 p_expiry_information := 0;
706 else
707 p_expiry_information := 1;
708 end if;
709 END;
710 /*------------------------------ ASG_LQTD_EC ----------------------------*/
711 /*
712 NAME
713 ASG_LQTD_EC - Assignment Lunar Quarter To Date expiry check.
714 NOTES
715 The associtated dimension is expiry checked at payroll action level
716 */
717 PROCEDURE ASG_LQTD_EC
718 (
719 p_owner_payroll_action_id in number, -- run created balance.
720 p_user_payroll_action_id in number, -- current run.
721 p_owner_assignment_action_id in number, -- assact created balance.
722 p_user_assignment_action_id in number, -- current assact..
723 p_owner_effective_date in date, -- eff date of balance.
724 p_user_effective_date in date, -- eff date of current run.
725 p_dimension_name in varchar2, -- balance dimension name.
726 p_expiry_information out nocopy number -- dimension expired flag.
727 ) IS
728 --
729 l_owner_quarter NUMBER;
730 l_user_quarter NUMBER;
731 --
732 BEGIN
733 --
734 SELECT DECODE(trunc((to_number(to_char(p_owner_effective_date,'IW'))-1)/12)
735 ,4,3
736 ,trunc((to_number(to_char(p_owner_effective_date,'IW'))-1)/12))
737 ,DECODE(trunc((to_number(to_char(p_user_effective_date,'IW'))-1)/12)
738 ,4,3
739 ,trunc((to_number(to_char(p_user_effective_date,'IW'))-1)/12))
740 INTO l_owner_quarter, l_user_quarter
741 FROM dual;
742 --
743 IF (l_owner_quarter = l_user_quarter) THEN
744 p_expiry_information := 0; -- Not Expired
745 ELSE
746 p_expiry_information := 1; -- Expired
747 END IF;
748 --
749 END ASG_LQTD_EC;
750 PROCEDURE ASG_LQTD_EC
751 (
752 p_owner_payroll_action_id in number, -- run created balance.
753 p_user_payroll_action_id in number, -- current run.
754 p_owner_assignment_action_id in number, -- assact created balance.
755 p_user_assignment_action_id in number, -- current assact..
756 p_owner_effective_date in date, -- eff date of balance.
757 p_user_effective_date in date, -- eff date of current run.
758 p_dimension_name in varchar2, -- balance dimension name.
759 p_expiry_information out nocopy DATE -- dimension expired flag.
760 ) IS
761 l_max_period NUMBER;
762 l_curr_wkend DATE;
763 l_lastyr_wkend DATE;
764 l_week_no NUMBER;
765 l_no_days NUMBER;
766 BEGIN
770 FROM DUAL;
767 --determine week end of last week of the last year and week end of current week
768 select trunc(trunc(trunc(p_owner_effective_date,'IW')+6,'Y'),'IW')-1, trunc(p_owner_effective_date,'IW')+6
769 into l_lastyr_wkend ,l_curr_wkend
771 IF (l_lastyr_wkend+53*7) < trunc(add_months(p_owner_effective_date,12),'Y') THEN -- 53 Week year
772 l_max_period := 53;
773 ELSE
774 l_max_period := 52;
775 END IF;
776 --determine week number for p_owner_effective_date
777 select (l_curr_wkend-l_lastyr_wkend)/7 into l_week_no from dual;
778 select (decode(trunc((l_week_no-1)/12+1)*12 ,48,l_max_period,60,l_max_period ,trunc((l_week_no-1)/12+1)*12))*7 into l_no_days from dual;
779 p_expiry_information := l_lastyr_wkend + l_no_days;
780 END;
781 /*------------------------------ PER_PAY_SITP_PTD ----------------------------*/
782 /*
783 NAME
784 PER_PAY_SITP_PTD - Person Payroll SI Type Provider Period To Date .
785 NOTES
786 The associtated dimension is expiry checked at payroll action level
787 */
788 procedure PER_PAY_SITP_PTD
789 (
790 p_owner_payroll_action_id in number, -- run created balance.
791 p_user_payroll_action_id in number, -- current run.
792 p_owner_assignment_action_id in number, -- assact created balance.
793 p_user_assignment_action_id in number, -- current assact..
794 p_owner_effective_date in date, -- eff date of balance.
795 p_user_effective_date in date, -- eff date of current run.
796 p_dimension_name in varchar2, -- balance dimension name.
797 p_expiry_information out nocopy number -- dimension expired flag.
798 ) IS
799 BEGIN
800 ASG_SIT_PTD_EC(p_owner_payroll_action_id => p_owner_payroll_action_id,
801 p_user_payroll_action_id => p_user_payroll_action_id,
802 p_owner_assignment_action_id => p_owner_assignment_action_id,
803 p_user_assignment_action_id => p_user_assignment_action_id,
804 p_owner_effective_date => p_owner_effective_date,
805 p_user_effective_date => p_user_effective_date,
806 p_dimension_name => p_dimension_name,
807 p_expiry_information => p_expiry_information);
808 END;
809 procedure PER_PAY_SITP_PTD
810 (
811 p_owner_payroll_action_id in number, -- run created balance.
812 p_user_payroll_action_id in number, -- current run.
813 p_owner_assignment_action_id in number, -- assact created balance.
814 p_user_assignment_action_id in number, -- current assact..
815 p_owner_effective_date in date, -- eff date of balance.
816 p_user_effective_date in date, -- eff date of current run.
817 p_dimension_name in varchar2, -- balance dimension name.
818 p_expiry_information out nocopy DATE -- dimension expired flag.
819 ) IS
820 BEGIN
821 SELECT TP.end_date
822 INTO p_expiry_information
823 FROM per_time_periods TP
824 ,pay_payroll_actions PACT
825 WHERE PACT.payroll_action_id = p_owner_payroll_action_id
826 AND PACT.payroll_id = TP.payroll_id
827 AND p_owner_effective_date BETWEEN TP.start_date AND TP.end_date;
828 END;
829 /*------------------------------ PER_PAY_PTD_EC ----------------------------*/
830 /*
831 NAME
832 PER_PAY_PTD_EC - Person Payroll Period To Date Expiry Check.
833 NOTES
834 The associated dimension is expiry checked at payroll action level
835 */
836 procedure PER_PAY_PTD_EC
837 (
838 p_owner_payroll_action_id in number, -- run created balance.
839 p_user_payroll_action_id in number, -- current run.
840 p_owner_assignment_action_id in number, -- assact created balance.
841 p_user_assignment_action_id in number, -- current assact..
842 p_owner_effective_date in date, -- eff date of balance.
843 p_user_effective_date in date, -- eff date of current run.
844 p_dimension_name in varchar2, -- balance dimension name.
845 p_expiry_information out nocopy number -- dimension expired flag.
846 ) IS
847 l_period_start_date date;
848 BEGIN
849 --
850 SELECT ptp.start_date
851 INTO l_period_start_date
852 FROM per_time_periods ptp, pay_payroll_actions ppa
853 WHERE ppa.payroll_action_id = p_user_payroll_action_id
854 AND ppa.payroll_id = ptp.payroll_id
855 AND p_user_effective_date BETWEEN ptp.start_date AND ptp.end_date;
856 -- see if balance was written in this period. If not it is expired
857 IF p_owner_effective_date >= l_period_start_date THEN
858 p_expiry_information := 0; -- Not expired
859 ELSE
860 p_expiry_information := 1; -- Expired
861 END IF;
862 --
863 END PER_PAY_PTD_EC;
864 procedure PER_PAY_PTD_EC
865 (
866 p_owner_payroll_action_id in number, -- run created balance.
867 p_user_payroll_action_id in number, -- current run.
868 p_owner_assignment_action_id in number, -- assact created balance.
869 p_user_assignment_action_id in number, -- current assact..
870 p_owner_effective_date in date, -- eff date of balance.
871 p_user_effective_date in date, -- eff date of current run.
872 p_dimension_name in varchar2, -- balance dimension name.
873 p_expiry_information out nocopy DATE -- dimension expired flag.
874 ) IS
875 BEGIN
876 SELECT TP.end_date
877 INTO p_expiry_information
878 FROM per_time_periods TP
879 ,pay_payroll_actions PACT
880 WHERE PACT.payroll_action_id = p_owner_payroll_action_id
881 AND PACT.payroll_id = TP.payroll_id
885 /*
882 AND p_owner_effective_date BETWEEN TP.start_date AND TP.end_date;
883 END;
884 /*------------------------------ ASG_LMONTH_EC ----------------------------*/
886 NAME
887 ASG_LMONTH_EC - Assignment Lunar Month expiry check.
888 NOTES
889 The associtated dimension is expiry checked at payroll action level
890 */
891 PROCEDURE ASG_LMONTH_EC
892 (
893 p_owner_payroll_action_id in number, -- run created balance.
894 p_user_payroll_action_id in number, -- current run.
895 p_owner_assignment_action_id in number, -- assact created balance.
896 p_user_assignment_action_id in number, -- current assact..
897 p_owner_effective_date in date, -- eff date of balance.
898 p_user_effective_date in date, -- eff date of current run.
899 p_dimension_name in varchar2, -- balance dimension name.
900 p_expiry_information out nocopy number -- dimension expired flag.
901 ) IS
902 --
903 l_owner_month NUMBER;
904 l_user_month NUMBER;
905 --
906 BEGIN
907 --
908 SELECT DECODE(trunc((to_number(to_char(p_owner_effective_date,'IW'))-1)/4)
909 ,13,12
910 ,trunc((to_number(to_char(p_owner_effective_date,'IW'))-1)/4))
911 ,DECODE(trunc((to_number(to_char(p_user_effective_date,'IW'))-1)/4)
912 ,13,12
913 ,trunc((to_number(to_char(p_user_effective_date,'IW'))-1)/4))
914 INTO l_owner_month, l_user_month
915 FROM dual;
916 --
917 IF (l_owner_month = l_user_month) THEN
918 p_expiry_information := 0; -- Not Expired
919 ELSE
920 p_expiry_information := 1; -- Expired
921 END IF;
922 --
923 END ASG_LMONTH_EC;
924 PROCEDURE ASG_LMONTH_EC
925 (
926 p_owner_payroll_action_id in number, -- run created balance.
927 p_user_payroll_action_id in number, -- current run.
928 p_owner_assignment_action_id in number, -- assact created balance.
929 p_user_assignment_action_id in number, -- current assact..
930 p_owner_effective_date in date, -- eff date of balance.
931 p_user_effective_date in date, -- eff date of current run.
932 p_dimension_name in varchar2, -- balance dimension name.
933 p_expiry_information out nocopy DATE -- dimension expired flag.
934 ) IS
935 l_max_period NUMBER;
936 l_curr_wkend DATE;
937 l_lastyr_wkend DATE;
938 l_week_no NUMBER;
939 l_no_days NUMBER;
940 BEGIN
941 SELECT MAX(TP.period_num)
942 INTO l_max_period
943 FROM per_time_periods TP
944 ,pay_payroll_actions PACT
945 WHERE PACT.payroll_action_id = p_owner_payroll_action_id
946 AND PACT.payroll_id = TP.payroll_id
947 AND TP.end_date BETWEEN TRUNC(p_owner_effective_date,'Y')
948 AND (TRUNC(ADD_MONTHS(p_owner_effective_date,12),'Y')-1);
949
950 IF l_max_period = 13 then
951 SELECT TP.end_date
952 INTO p_expiry_information
953 FROM per_time_periods TP
954 ,pay_payroll_actions PACT
955 WHERE PACT.payroll_action_id = p_owner_payroll_action_id
956 AND PACT.payroll_id = TP.payroll_id
957 AND p_owner_effective_date BETWEEN TP.start_date AND TP.end_date;
958 ELSE
959 --determine week end of last week of the last year and week end of current week
960 select trunc(trunc(trunc(p_owner_effective_date,'IW')+6,'Y'),'IW')-1, trunc(p_owner_effective_date,'IW')+6
961 into l_lastyr_wkend ,l_curr_wkend
962 FROM DUAL;
963 IF (l_lastyr_wkend+53*7) < trunc(add_months(p_owner_effective_date,12),'Y') THEN -- 53 Week year
964 l_max_period := 53;
965 ELSE
966 l_max_period := 52;
967 END IF;
968 --determine week number for p_owner_effective_date
969 select (l_curr_wkend-l_lastyr_wkend)/7 into l_week_no from dual;
970 select (decode(trunc((l_week_no-1)/4+1)*4 ,52,l_max_period,56,l_max_period ,trunc((l_week_no-1)/4+1)*4))*7 into l_no_days from dual;
971 p_expiry_information := l_lastyr_wkend + l_no_days;
972 END IF;
973 END;
974 ------------------------------------------------------------------------------
975 /*------------------------------ ASG_SIT_LMON_EC ----------------------------*/
976 /*
977 NAME
978 ASG_SIT_MON_EC - Assignment SI Type Tax Quarter to Date expiry check.
979 DESCRIPTION
980 Expiry checking code for the following:
981 NL Element-level Process Month Balance Dimension
982 NOTES
983 The associtated dimension is expiry checked at payroll action level
984 */
985 procedure ASG_SIT_LMON_EC
986 (
987 p_owner_payroll_action_id in number, -- run created balance.
988 p_user_payroll_action_id in number, -- current run.
989 p_owner_assignment_action_id in number, -- assact created balance.
990 p_user_assignment_action_id in number, -- current assact..
991 p_owner_effective_date in date, -- eff date of balance.
992 p_user_effective_date in date, -- eff date of current run.
993 p_dimension_name in varchar2, -- balance dimension name.
994 p_expiry_information out nocopy number -- dimension expired flag.
995 ) IS
996 --
997 l_owner_month NUMBER;
998 l_user_month NUMBER;
999 --
1000 BEGIN
1001 --
1002 SELECT DECODE(trunc((to_number(to_char(p_owner_effective_date,'IW'))-1)/4)
1003 ,13,12
1004 ,trunc((to_number(to_char(p_owner_effective_date,'IW'))-1)/4))
1005 ,DECODE(trunc((to_number(to_char(p_user_effective_date,'IW'))-1)/4)
1006 ,13,12
1007 ,trunc((to_number(to_char(p_user_effective_date,'IW'))-1)/4))
1008 INTO l_owner_month, l_user_month
1009 FROM dual;
1010 --
1011 IF (l_owner_month = l_user_month) THEN
1012 p_expiry_information := 0; -- Not Expired
1013 ELSE
1014 p_expiry_information := 1; -- Expired
1015 END IF;
1016 --
1017 END ASG_SIT_LMON_EC ;
1018 procedure ASG_SIT_LMON_EC
1019 (
1020 p_owner_payroll_action_id in number, -- run created balance.
1021 p_user_payroll_action_id in number, -- current run.
1022 p_owner_assignment_action_id in number, -- assact created balance.
1023 p_user_assignment_action_id in number, -- current assact..
1024 p_owner_effective_date in date, -- eff date of balance.
1025 p_user_effective_date in date, -- eff date of current run.
1026 p_dimension_name in varchar2, -- balance dimension name.
1027 p_expiry_information out nocopy DATE -- dimension expired flag.
1028 ) IS
1029 l_max_period NUMBER;
1030 l_curr_wkend DATE;
1031 l_lastyr_wkend DATE;
1032 l_week_no NUMBER;
1033 l_no_days NUMBER;
1034 BEGIN
1035 SELECT MAX(TP.period_num)
1036 INTO l_max_period
1037 FROM per_time_periods TP
1038 ,pay_payroll_actions PACT
1039 WHERE PACT.payroll_action_id = p_owner_payroll_action_id
1040 AND PACT.payroll_id = TP.payroll_id
1041 AND TP.end_date BETWEEN TRUNC(p_owner_effective_date,'Y')
1042 AND (TRUNC(ADD_MONTHS(p_owner_effective_date,12),'Y')-1);
1043
1044 IF l_max_period = 13 then
1045 SELECT TP.end_date
1046 INTO p_expiry_information
1047 FROM per_time_periods TP
1048 ,pay_payroll_actions PACT
1049 WHERE PACT.payroll_action_id = p_owner_payroll_action_id
1050 AND PACT.payroll_id = TP.payroll_id
1051 AND p_owner_effective_date BETWEEN TP.start_date AND TP.end_date;
1052 ELSE
1053 --determine week end of last week of the last year and week end of current week
1054 select trunc(trunc(trunc(p_owner_effective_date,'IW')+6,'Y'),'IW')-1, trunc(p_owner_effective_date,'IW')+6
1055 into l_lastyr_wkend ,l_curr_wkend
1056 FROM DUAL;
1057 IF (l_lastyr_wkend+53*7) < trunc(add_months(p_owner_effective_date,12),'Y') THEN -- 53 Week year
1058 l_max_period := 53;
1059 ELSE
1060 l_max_period := 52;
1061 END IF;
1062 --determine week number for p_owner_effective_date
1063 select (l_curr_wkend-l_lastyr_wkend)/7 into l_week_no from dual;
1064 select (decode(trunc((l_week_no-1)/4+1)*4 ,52,l_max_period,56,l_max_period ,trunc((l_week_no-1)/4+1)*4))*7 into l_no_days from dual;
1065 p_expiry_information := l_lastyr_wkend + l_no_days;
1066 END IF;
1067 END;
1068 /*------------------------------ PER_PAY_YTD_EC ----------------------------*/
1069 /*
1070 NAME
1071 PER_PAY_YTD_EC - Person Payroll Year to Date expiry check.
1072 DESCRIPTION
1073 Expiry checking code for the following:
1074 NL Element-level Process Year To Date Balance Dimension
1075 NOTES
1076 The associtated dimension is expiry checked at payroll action level
1077 */
1078 procedure PER_PAY_YTD_EC
1079 (
1080 p_owner_payroll_action_id in number, -- run created balance.
1081 p_user_payroll_action_id in number, -- current run.
1082 p_owner_assignment_action_id in number, -- assact created balance.
1083 p_user_assignment_action_id in number, -- current assact..
1084 p_owner_effective_date in date, -- eff date of balance.
1085 p_user_effective_date in date, -- eff date of current run.
1086 p_dimension_name in varchar2, -- balance dimension name.
1087 p_expiry_information out nocopy number -- dimension expired flag.
1088 ) IS
1089 BEGIN
1090 if p_owner_effective_date >= trunc(p_user_effective_date,'Y') then
1091 p_expiry_information := 0;
1092 else
1093 p_expiry_information := 1;
1094 end if;
1095 END PER_PAY_YTD_EC;
1096 procedure PER_PAY_YTD_EC
1097 (
1098 p_owner_payroll_action_id in number, -- run created balance.
1099 p_user_payroll_action_id in number, -- current run.
1100 p_owner_assignment_action_id in number, -- assact created balance.
1101 p_user_assignment_action_id in number, -- current assact..
1102 p_owner_effective_date in date, -- eff date of balance.
1103 p_user_effective_date in date, -- eff date of current run.
1104 p_dimension_name in varchar2, -- balance dimension name.
1105 p_expiry_information out nocopy DATE -- dimension expired flag.
1106 ) IS
1107 BEGIN
1108 p_expiry_information := TRUNC(ADD_MONTHS(p_owner_effective_date, 12), 'Y')-1;
1109 END PER_PAY_YTD_EC;
1110 ---------------------------------------------------------------------------
1111 end PAY_NL_DIM_PKG;
[Home] [Help]