1 package body pyzaexc as
2 /* $Header: pyzaexc.pkb 120.4.12000000.1 2007/01/18 03:29:30 appldev noship $ */
3 ---------------------------------------------------------------------------------------
4 -- Global variables
5 ---------------------------------------------------------------------------------------
6
7 -- Cache result of tax year expiry check
8 g_za_ty_owner_pay_action_id number; -- run created balance.
9 g_za_ty_user_pay_action_id number; -- current run.
10 g_za_ty_expiry_information number; -- dimension expired flag.
11 -- Cache result of calendar year expiry check
12 g_za_cy_owner_pay_action_id number; -- run created balance.
13 g_za_cy_user_pay_action_id number; -- current run.
14 g_za_cy_expiry_information number; -- dimension expired flag.
15 -- Cache result of tax quarter expiry check
16 g_za_tq_owner_pay_action_id number; -- run created balance.
17 g_za_tq_user_pay_action_id number; -- current run.
18 g_za_tq_expiry_information number; -- dimension expired flag.
19 -- Cache result of month expiry check
20 g_za_m_owner_pay_action_id number; -- run created balance.
21 g_za_m_user_pay_action_id number; -- current run.
22 g_za_m_expiry_information number; -- dimension expired flag.
23
24 --------------------------------- ASG_TAX_YTD_EC --------------------------------------
25 -- NAME
26 -- ASG_TAX_YTD_EC - Assignment Tax Year to Date expiry check.
27 -- DESCRIPTION
28 -- Expiry checking code for the following:
29 -- ZA Assignment-level Tax Year to Date Balance Dimension
30 -- NOTES
31 -- The associated dimension is expiry checked at payroll action level
32 -- 1 means expired
33 ---------------------------------------------------------------------------------------
34 procedure ASG_TAX_YTD_EC
35 (
36 p_owner_payroll_action_id in number, -- run created balance.
37 p_user_payroll_action_id in number, -- current run.
38 p_owner_assignment_action_id in number, -- assact created balance.
39 p_user_assignment_action_id in number, -- current assact..
40 p_owner_effective_date in date, -- eff date of balance.
41 p_user_effective_date in date, -- eff date of current run.
42 p_dimension_name in varchar2, -- balance dimension name.
43 p_expiry_information out nocopy number -- dimension expired flag.
44 )
45 is
46
47 l_owner_date_earned date;
48 l_user_date_earned date;
49 l_owner_payroll_id number;
50 l_user_payroll_id number;
51 l_owner_tax_year number;
52 l_user_tax_year number;
53 l_expiry_date date;
54
55 begin
56
57 -- If the payroll actions have not changed, return the stored result
58 if (p_owner_payroll_action_id = g_za_ty_owner_pay_action_id) and
59 (p_user_payroll_action_id = g_za_ty_user_pay_action_id) then
60
61 p_expiry_information := g_za_ty_expiry_information;
62
63 else -- Check expiry
64 /*
65 -- Get the owner's date earned and payroll id
66 select
67 date_earned, payroll_id
68 into
69 l_owner_date_earned, l_owner_payroll_id
70 from
71 pay_payroll_actions
72 where
73 payroll_action_id = p_owner_payroll_action_id;
74
75 -- Get the user's date earned and payroll id
76 select
77 date_earned, payroll_id
78 into
79 l_user_date_earned, l_user_payroll_id
80 from
81 pay_payroll_actions
82 where
83 payroll_action_id = p_user_payroll_action_id;
84
85 -- Get the owner's tax year
86 select
87 prd_information1
88 into
89 l_owner_tax_year
90 from
91 per_time_periods
92 where
93 payroll_id = l_owner_payroll_id
94 and
95 l_owner_date_earned between start_date and end_date;
96
97 -- Get the user's tax year
98 select
99 prd_information1
100 into
101 l_user_tax_year
102 from
103 per_time_periods
104 where
105 payroll_id = l_user_payroll_id
106 and
107 l_user_date_earned between start_date and end_date;
108
109 -- If the tax years are the same then the dimension has not expired
110 if l_owner_tax_year = l_user_tax_year then
111 */
112 --added for 5486039
113 ASG_TAX_YTD_EC(p_owner_payroll_action_id => p_owner_payroll_action_id
114 , p_user_payroll_action_id => p_user_payroll_action_id
115 , p_owner_assignment_action_id => p_owner_assignment_action_id
116 , p_user_assignment_action_id => p_user_assignment_action_id
117 , p_owner_effective_date => p_owner_effective_date
118 , p_user_effective_date => p_user_effective_date
119 , p_dimension_name => p_dimension_name
120 , p_expiry_information => l_expiry_date
121 );
122
123 -- Dimension has not expired
124 -- bug 5629444 . When p_user_effective_date = l_expiry_date the dimension
125 -- is not expired.
126 IF p_user_effective_date <= l_expiry_date THEN
127 p_expiry_information := 0;
128 g_za_ty_expiry_information := 0;
129 else
130 -- Dimension has expired
131 p_expiry_information := 1;
132 g_za_ty_expiry_information := 1;
133
134 end if;
135
136 g_za_ty_owner_pay_action_id := p_owner_payroll_action_id;
137 g_za_ty_user_pay_action_id := p_user_payroll_action_id;
138
139 end if; -- End check expiry
140
141 end ASG_TAX_YTD_EC;
142 --
143 --5189195
144 procedure ASG_TAX_YTD_EC
145 (
146 p_owner_payroll_action_id in number, -- run created balance.
147 p_user_payroll_action_id in number, -- current run.
148 p_owner_assignment_action_id in number, -- assact created balance.
149 p_user_assignment_action_id in number, -- current assact..
150 p_owner_effective_date in date, -- eff date of balance.
151 p_user_effective_date in date, -- eff date of current run.
152 p_dimension_name in varchar2, -- balance dimension name.
153 p_expiry_information out nocopy date -- dimension expired flag.
154 )
155 is
156 l_tax_start_day varchar2(50);
157 l_tax_year varchar2(50);
158 l_tax_year_start_date date;
159 --
160 BEGIN
161 --hr_utility.trace_on(null,'ZAEXC');
162 hr_utility.set_location('p_owner_payroll_action_id: '||to_char(p_owner_payroll_action_id),10);
163 hr_utility.set_location('p_user_payroll_action_id: '||to_char(p_user_payroll_action_id),10);
164 hr_utility.set_location('p_owner_assignment_action_id: '||to_char(p_owner_assignment_action_id),10);
165 hr_utility.set_location('p_user_assignment_action_id: '||to_char(p_user_assignment_action_id),10);
166 hr_utility.set_location('p_owner_effective_date: '||to_char(p_owner_effective_date),10);
167 hr_utility.set_location('p_user_effective_date: '||to_char(p_user_effective_date),10);
168 hr_utility.set_location('p_dimension_name: '||to_char(p_dimension_name),10);
169
170 select rule_mode
171 into l_tax_start_day
172 from pay_legislation_rules
173 where legislation_code = 'ZA'
174 and rule_type = 'L';
175
176 hr_utility.set_location('l_tax_start_day: '||to_char(l_tax_start_day),10);
177 --
178 select to_char(p_owner_effective_date,'YYYY')
179 into l_tax_year
180 from dual;
181 hr_utility.set_location('l_tax_year: '||to_char(l_tax_year),20);
182
183 --
184 SELECT to_date(l_tax_start_day||'-'||l_tax_year,'DD/MM/YYYY')
185 INTO l_tax_year_start_date
186 FROM dual;
187 hr_utility.set_location('l_tax_year_start_date: '||to_char(l_tax_year_start_date),30);
188 --
189 SELECT (add_months(l_tax_year_start_date,
190 (floor(months_between(p_owner_effective_date, l_tax_year_start_date) / 12) + 1) * 12) -1)
191 INTO p_expiry_information
192 from dual;
193
194 hr_utility.set_location('p_expiry_information: '||to_char(p_expiry_information),40);
195 --hr_utility.trace_off;
196 --
197 end ASG_TAX_YTD_EC;
198 --------------------------------- ASG_CAL_YTD_EC --------------------------------------
199 -- NAME
200 -- ASG_CAL_YTD_EC - Assignment Calendar Year to Date expiry check.
201 -- DESCRIPTION
202 -- Expiry checking code for the following:
203 -- ZA Assignment-level Calendar Year to Date Balance Dimension
204 -- NOTES
205 -- The associated dimension is expiry checked at payroll action level
206 -- 1 means expired
207 ---------------------------------------------------------------------------------------
208 procedure ASG_CAL_YTD_EC
209 (
210 p_owner_payroll_action_id in number, -- run created balance.
211 p_user_payroll_action_id in number, -- current run.
212 p_owner_assignment_action_id in number, -- assact created balance.
213 p_user_assignment_action_id in number, -- current assact..
214 p_owner_effective_date in date, -- eff date of balance.
215 p_user_effective_date in date, -- eff date of current run.
216 p_dimension_name in varchar2, -- balance dimension name.
217 p_expiry_information out nocopy number -- dimension expired flag.
218 )
219 is
220
221 l_owner_date_earned date;
222 l_user_date_earned date;
223 l_owner_payroll_id number;
224 l_user_payroll_id number;
225 l_owner_cal_year number;
226 l_user_cal_year number;
227 l_expiry_date date;
228 begin
229
230 -- If the payroll actions have not changed, return the stored result
231 if (p_owner_payroll_action_id = g_za_cy_owner_pay_action_id) and
232 (p_user_payroll_action_id = g_za_cy_user_pay_action_id) then
233
234 p_expiry_information := g_za_cy_expiry_information;
235
236 else -- Check expiry
237 /*
238 -- Get the owner's date earned and payroll id
239 select
240 date_earned, payroll_id
241 into
242 l_owner_date_earned, l_owner_payroll_id
243 from
244 pay_payroll_actions
245 where
246 payroll_action_id = p_owner_payroll_action_id;
247
248 -- Get the user's date earned and payroll id
249 select
250 date_earned, payroll_id
251 into
252 l_user_date_earned, l_user_payroll_id
253 from
254 pay_payroll_actions
255 where
256 payroll_action_id = p_user_payroll_action_id;
257
258 -- Get the owner's calendar year
259 select
260 prd_information3
261 into
262 l_owner_cal_year
263 from
264 per_time_periods
265 where
266 payroll_id = l_owner_payroll_id
267 and
268 l_owner_date_earned between start_date and end_date;
269
270 -- Get the user's calendar year
271 select
272 prd_information3
273 into
274 l_user_cal_year
275 from
276 per_time_periods
277 where
278 payroll_id = l_user_payroll_id
279 and
280 l_user_date_earned between start_date and end_date;
281
282 -- If the calendar years are the same then the dimension has not expired
283 if l_owner_cal_year = l_user_cal_year then
284 */
285 --added for 5486039
286
287 ASG_CAL_YTD_EC(p_owner_payroll_action_id => p_owner_payroll_action_id
288 ,p_user_payroll_action_id => p_user_payroll_action_id
289 ,p_owner_assignment_action_id => p_owner_assignment_action_id
290 ,p_user_assignment_action_id => p_user_assignment_action_id
291 ,p_owner_effective_date => p_owner_effective_date
292 ,p_user_effective_date => p_user_effective_date
293 ,p_dimension_name => p_dimension_name
294 ,p_expiry_information => l_expiry_date
295 );
296
297 IF p_user_effective_date <= l_expiry_date THEN
298 -- Dimension has not expired
299 p_expiry_information := 0;
300 g_za_cy_expiry_information := 0;
301 else
302 -- Dimension has expired
303 p_expiry_information := 1;
304 g_za_cy_expiry_information := 1;
305
306 end if;
307
308 g_za_cy_owner_pay_action_id := p_owner_payroll_action_id;
309 g_za_cy_user_pay_action_id := p_user_payroll_action_id;
310
311 end if; -- End check expiry
312
313 end ASG_CAL_YTD_EC;
314
315 -- 5189195
316 procedure ASG_CAL_YTD_EC
317 (
318 p_owner_payroll_action_id in number, -- run created balance.
319 p_user_payroll_action_id in number, -- current run.
320 p_owner_assignment_action_id in number, -- assact created balance.
321 p_user_assignment_action_id in number, -- current assact..
322 p_owner_effective_date in date, -- eff date of balance.
323 p_user_effective_date in date, -- eff date of current run.
324 p_dimension_name in varchar2, -- balance dimension name.
325 p_expiry_information out nocopy date -- dimension expired flag.
326 )
327 is
328 begin
329 SELECT (trunc(add_months(p_owner_effective_date,12),'Y')-1)
330 INTO p_expiry_information
331 from dual;
332 END ASG_CAL_YTD_EC;
333 --
334 --------------------------------- ASG_TAX_QTD_EC --------------------------------------
335 -- NAME
336 -- ASG_TAX_QTD_EC - Assignment Tax Quarter to Date expiry check.
337 -- DESCRIPTION
338 -- Expiry checking code for the following:
339 -- ZA Assignment-level Quarter Year to Date Balance Dimension
340 -- NOTES
341 -- The associated dimension is expiry checked at payroll action level
342 -- 1 means expired
343 ---------------------------------------------------------------------------------------
344 procedure ASG_TAX_QTD_EC
345 (
346 p_owner_payroll_action_id in number, -- run created balance.
347 p_user_payroll_action_id in number, -- current run.
348 p_owner_assignment_action_id in number, -- assact created balance.
349 p_user_assignment_action_id in number, -- current assact..
350 p_owner_effective_date in date, -- eff date of balance.
351 p_user_effective_date in date, -- eff date of current run.
352 p_dimension_name in varchar2, -- balance dimension name.
353 p_expiry_information out nocopy number -- dimension expired flag.
354 )
355 is
356
357 l_owner_date_earned date;
358 l_user_date_earned date;
359 l_owner_payroll_id number;
360 l_user_payroll_id number;
361 l_owner_tax_quarter number;
362 l_user_tax_quarter number;
363 l_owner_tax_year number;
364 l_user_tax_year number;
365 l_expiry_date date;
366 begin
367
368 -- If the payroll actions have not changed, return the stored result
369 if (p_owner_payroll_action_id = g_za_tq_owner_pay_action_id) and
370 (p_user_payroll_action_id = g_za_tq_user_pay_action_id) then
371
372 p_expiry_information := g_za_tq_expiry_information;
373
374 else -- Check expiry
375 /*
376 -- Get the owner's date earned and payroll id
377 select
378 date_earned, payroll_id
379 into
380 l_owner_date_earned, l_owner_payroll_id
381 from
382 pay_payroll_actions
383 where
384 payroll_action_id = p_owner_payroll_action_id;
385
386 -- Get the user's date earned and payroll id
387 select
388 date_earned, payroll_id
389 into
390 l_user_date_earned, l_user_payroll_id
391 from
392 pay_payroll_actions
393 where
394 payroll_action_id = p_user_payroll_action_id;
395
396 -- Get the owner's tax quarter and tax year
397 select
398 prd_information1, prd_information2
399 into
400 l_owner_tax_year, l_owner_tax_quarter
401 from
402 per_time_periods
403 where
404 payroll_id = l_owner_payroll_id
405 and
406 l_owner_date_earned between start_date and end_date;
407
408 -- Get the user's tax quarter and tax year
409 select
410 prd_information1, prd_information2
411 into
412 l_user_tax_year, l_user_tax_quarter
413 from
414 per_time_periods
415 where
416 payroll_id = l_user_payroll_id
417 and
418 l_user_date_earned between start_date and end_date;
419
420 -- If the tax quarters and years are the same then the dimension has not expired
421 if (l_owner_tax_year = l_user_tax_year) and
422 (l_owner_tax_quarter = l_user_tax_quarter) then
423 */
424 --added for 5486039
425
426 ASG_TAX_QTD_EC(p_owner_payroll_action_id => p_owner_payroll_action_id
427 ,p_user_payroll_action_id => p_user_payroll_action_id
428 ,p_owner_assignment_action_id => p_owner_assignment_action_id
429 ,p_user_assignment_action_id => p_user_assignment_action_id
430 ,p_owner_effective_date => p_owner_effective_date
431 ,p_user_effective_date => p_user_effective_date
432 ,p_dimension_name => p_dimension_name
433 ,p_expiry_information => l_expiry_date
434 );
435
436 IF p_user_effective_date <= l_expiry_date THEN
437 -- Dimension has not expired
438 p_expiry_information := 0;
439 g_za_tq_expiry_information := 0;
440
441 else
442
443 -- Dimension has expired
444 p_expiry_information := 1;
445 g_za_tq_expiry_information := 1;
446
447 end if;
448
449 g_za_tq_owner_pay_action_id := p_owner_payroll_action_id;
450 g_za_tq_user_pay_action_id := p_user_payroll_action_id;
451
452 end if; -- End check expiry
453
454 end ASG_TAX_QTD_EC;
455 --
456 -- 5189195
457 --
458 PROCEDURE ASG_TAX_QTD_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 date -- dimension expired flag.
468 )
469 is
470 l_tax_start_day varchar2(50);
471 l_tax_year varchar2(50);
472 l_tax_year_start_date date;
473 --
474 BEGIN
475 select rule_mode
476 into l_tax_start_day
477 from pay_legislation_rules
478 where legislation_code = 'ZA'
479 and rule_type = 'L';
480 --
481 select to_char(p_owner_effective_date,'YYYY')
482 into l_tax_year
483 from dual;
484 --
485 SELECT to_date(l_tax_start_day||'-'||l_tax_year,'DD/MM/YYYY')
486 INTO l_tax_year_start_date
487 FROM dual;
488 --
489 SELECT (add_months(l_tax_year_start_date,
490 (floor(months_between(p_owner_effective_date, l_tax_year_start_date) / 3) + 1) * 3) -1 )
491 INTO p_expiry_information
492 FROM dual;
493 --
494 end ASG_TAX_QTD_EC;
495 --
496 --------------------------------- ASG_MTD_EC --------------------------------------
497 -- NAME
498 -- ASG_MTD_EC - Assignment Month to Date expiry check.
499 -- DESCRIPTION
500 -- Expiry checking code for the following:
501 -- ZA Assignment-level Month to Date Balance Dimension
502 -- NOTES
503 -- The associated dimension is expiry checked at payroll action level
504 -- 1 means expired
505 ---------------------------------------------------------------------------------------
506 procedure ASG_MTD_EC
507 (
508 p_owner_payroll_action_id in number, -- run created balance.
509 p_user_payroll_action_id in number, -- current run.
510 p_owner_assignment_action_id in number, -- assact created balance.
511 p_user_assignment_action_id in number, -- current assact..
512 p_owner_effective_date in date, -- eff date of balance.
513 p_user_effective_date in date, -- eff date of current run.
514 p_dimension_name in varchar2, -- balance dimension name.
515 p_expiry_information out nocopy number -- dimension expired flag.
516 )
517 is
518
519 l_owner_date_earned date;
520 l_user_date_earned date;
521 l_owner_payroll_id number;
522 l_user_payroll_id number;
523 l_owner_month date;
524 l_user_month date;
525 l_expiry_date date;
526 begin
527
528 -- If the payroll actions have not changed, return the stored result
529 if (p_owner_payroll_action_id = g_za_m_owner_pay_action_id) and
530 (p_user_payroll_action_id = g_za_m_user_pay_action_id) then
531
532 p_expiry_information := g_za_m_expiry_information;
533
534 else -- Check expiry
535 /*
536 -- Get the owner's date earned and payroll id
537 select
538 date_earned, payroll_id
539 into
540 l_owner_date_earned, l_owner_payroll_id
541 from
542 pay_payroll_actions
543 where
544 payroll_action_id = p_owner_payroll_action_id;
545
546 -- Get the user's date earned and payroll id
547 select
548 date_earned, payroll_id
549 into
550 l_user_date_earned, l_user_payroll_id
551 from
552 pay_payroll_actions
553 where
554 payroll_action_id = p_user_payroll_action_id;
555
556 -- Get the owner's month end
557 select
558 pay_advice_date
559 into
560 l_owner_month
561 from
562 per_time_periods
563 where
564 payroll_id = l_owner_payroll_id
565 and
566 l_owner_date_earned between start_date and end_date;
567
568 -- Get the user's month end
569 select
570 pay_advice_date
571 into
572 l_user_month
573 from
574 per_time_periods
575 where
576 payroll_id = l_user_payroll_id
577 and
578 l_user_date_earned between start_date and end_date;
579
580 -- If the month ends are the same then the dimension has not expired
581 if l_owner_month = l_user_month then
582 */
583 --added for 5486039
584
585 ASG_MTD_EC(p_owner_payroll_action_id => p_owner_payroll_action_id
586 ,p_user_payroll_action_id => p_user_payroll_action_id
587 ,p_owner_assignment_action_id => p_owner_assignment_action_id
588 ,p_user_assignment_action_id => p_user_assignment_action_id
589 ,p_owner_effective_date => p_owner_effective_date
590 ,p_user_effective_date => p_user_effective_date
591 ,p_dimension_name => p_dimension_name
592 ,p_expiry_information => l_expiry_date
593 );
594
595 IF p_user_effective_date <= l_expiry_date THEN
596 -- Dimension has not expired
597 p_expiry_information := 0;
598 g_za_m_expiry_information := 0;
599
600 else
601
602 -- Dimension has expired
603 p_expiry_information := 1;
604 g_za_m_expiry_information := 1;
605
606 end if;
607
608 g_za_m_owner_pay_action_id := p_owner_payroll_action_id;
609 g_za_m_user_pay_action_id := p_user_payroll_action_id;
610
611 end if; -- End check expiry
612
613 end ASG_MTD_EC;
614 --
615 -- 5189195
616 procedure ASG_MTD_EC
617 (
618 p_owner_payroll_action_id in number, -- run created balance.
619 p_user_payroll_action_id in number, -- current run.
620 p_owner_assignment_action_id in number, -- assact created balance.
621 p_user_assignment_action_id in number, -- current assact.
622 p_owner_effective_date in date, -- eff date of balance.
623 p_user_effective_date in date, -- eff date of current run.
624 p_dimension_name in varchar2, -- balance dimension name.
625 p_expiry_information out NOCOPY date) -- dimension expired date.
626 is
627 begin
628 --
629 SELECT ((trunc(add_months(p_owner_effective_date,1),'MM'))-1)
630 INTO p_expiry_information
631 FROM dual;
632 --
633 end ASG_MTD_EC;
634 --
635 --------------------------------- ASG_PTD_EC --------------------------------------
636 -- NAME
637 -- ASG_PTD_EC - Assignment Period to Date expiry check.
638 -- DESCRIPTION
639 -- Expiry checking code for the following:
640 -- ZA Assignment-level Period to Date Balance Dimension
641 -- NOTES
642 -- The associated dimension is expiry checked at payroll action level
643 -- 1 means expired
644 ---------------------------------------------------------------------------------------
645 procedure ASG_PTD_EC
646 (
647 p_owner_payroll_action_id in number, -- run created balance.
648 p_user_payroll_action_id in number, -- current run.
649 p_owner_assignment_action_id in number, -- assact created balance.
650 p_user_assignment_action_id in number, -- current assact..
651 p_owner_effective_date in date, -- eff date of balance.
652 p_user_effective_date in date, -- eff date of current run.
653 p_dimension_name in varchar2, -- balance dimension name.
654 p_expiry_information out nocopy number -- dimension expired flag.
655 )
656 is
657
658 l_owner_time_period_id number;
659 l_user_time_period_id number;
660 l_expiry_date date;
661
662 begin
663
664 -- Select the period of the owning and using action and if they are the same then
665 -- the dimension has expired - either a prior period or a different payroll
666 /*
667 -- Get the owner's time_period_id
668 select
669 time_period_id
670 into
671 l_owner_time_period_id
672 from
673 pay_payroll_actions
674 where
675 payroll_action_id = p_owner_payroll_action_id;
676
677 -- Get the user's time_period_id
678 select
679 time_period_id
680 into
681 l_user_time_period_id
682 from
683 pay_payroll_actions
684 where
685 payroll_action_id = p_user_payroll_action_id;
686
687
688 -- If the time periods are the same then the dimension has not expired
689 if l_owner_time_period_id = l_user_time_period_id then
690 */
691 --added for 5486039
692
693 ASG_PTD_EC(p_owner_payroll_action_id => p_owner_payroll_action_id
694 ,p_user_payroll_action_id => p_user_payroll_action_id
695 ,p_owner_assignment_action_id => p_owner_assignment_action_id
696 ,p_user_assignment_action_id => p_user_assignment_action_id
697 ,p_owner_effective_date => p_owner_effective_date
698 ,p_user_effective_date => p_user_effective_date
699 ,p_dimension_name => p_dimension_name
700 ,p_expiry_information => l_expiry_date
701 );
702
703 IF p_user_effective_date <= l_expiry_date THEN
704 -- Dimension has not expired
705 p_expiry_information := 0;
706 else
707 -- Dimension has expired
708 p_expiry_information := 1;
709
710 end if;
711
712 end ASG_PTD_EC;
713 --
714 -- 5189195
715 --
716 procedure ASG_PTD_EC
717 (
718 p_owner_payroll_action_id in number, -- run created balance.
719 p_user_payroll_action_id in number, -- current run.
720 p_owner_assignment_action_id in number, -- assact created balance.
721 p_user_assignment_action_id in number, -- current assact..
722 p_owner_effective_date in date, -- eff date of balance.
723 p_user_effective_date in date, -- eff date of current run.
724 p_dimension_name in varchar2, -- balance dimension name.
725 p_expiry_information out nocopy DATE -- dimension expired flag.
726 ) IS
727 BEGIN
728 SELECT ptp.end_date
729 INTO p_expiry_information
730 FROM per_time_periods ptp
731 ,pay_payroll_actions ppa
732 WHERE ppa.payroll_action_id = p_owner_payroll_action_id
733 AND ppa.payroll_id = ptp.payroll_id
734 AND p_owner_effective_date BETWEEN ptp.start_date AND ptp.end_date;
735 END ASG_PTD_EC;
736 --
737 end pyzaexc;
[Home] [Help]