1 package body pyzaexc as
2 /* $Header: pyzaexc.pkb 120.4.12010000.2 2009/06/23 11:41:40 rbabla ship $ */
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 /* Commented for Bug 8624178
330 SELECT (trunc(add_months(p_owner_effective_date,12),'Y')-1)
331 INTO p_expiry_information
332 from dual; */
333
334 SELECT max(ptpp.pay_advice_date)
335 INTO p_expiry_information
336 FROM pay_payroll_actions ppa
337 ,per_time_periods ptp
338 ,per_time_periods ptpp
339 WHERE ppa.payroll_action_id = p_owner_payroll_action_id
340 AND ppa.payroll_id = ptp.payroll_id
341 and ptp.payroll_id = ptpp.payroll_id
342 AND p_owner_effective_date BETWEEN ptp.start_date AND ptp.end_date
343 and ptpp.PRD_INFORMATION3 = ptp.PRD_INFORMATION3;
344 END ASG_CAL_YTD_EC;
345 --
346 --------------------------------- ASG_TAX_QTD_EC --------------------------------------
347 -- NAME
348 -- ASG_TAX_QTD_EC - Assignment Tax Quarter to Date expiry check.
349 -- DESCRIPTION
350 -- Expiry checking code for the following:
351 -- ZA Assignment-level Quarter Year to Date Balance Dimension
352 -- NOTES
353 -- The associated dimension is expiry checked at payroll action level
354 -- 1 means expired
355 ---------------------------------------------------------------------------------------
356 procedure ASG_TAX_QTD_EC
357 (
358 p_owner_payroll_action_id in number, -- run created balance.
359 p_user_payroll_action_id in number, -- current run.
360 p_owner_assignment_action_id in number, -- assact created balance.
361 p_user_assignment_action_id in number, -- current assact..
362 p_owner_effective_date in date, -- eff date of balance.
363 p_user_effective_date in date, -- eff date of current run.
364 p_dimension_name in varchar2, -- balance dimension name.
365 p_expiry_information out nocopy number -- dimension expired flag.
366 )
367 is
368
369 l_owner_date_earned date;
370 l_user_date_earned date;
371 l_owner_payroll_id number;
372 l_user_payroll_id number;
373 l_owner_tax_quarter number;
374 l_user_tax_quarter number;
375 l_owner_tax_year number;
376 l_user_tax_year number;
377 l_expiry_date date;
378 begin
379
380 -- If the payroll actions have not changed, return the stored result
381 if (p_owner_payroll_action_id = g_za_tq_owner_pay_action_id) and
382 (p_user_payroll_action_id = g_za_tq_user_pay_action_id) then
383
384 p_expiry_information := g_za_tq_expiry_information;
385
386 else -- Check expiry
387 /*
388 -- Get the owner's date earned and payroll id
389 select
390 date_earned, payroll_id
391 into
392 l_owner_date_earned, l_owner_payroll_id
393 from
394 pay_payroll_actions
395 where
396 payroll_action_id = p_owner_payroll_action_id;
397
398 -- Get the user's date earned and payroll id
399 select
400 date_earned, payroll_id
401 into
402 l_user_date_earned, l_user_payroll_id
403 from
404 pay_payroll_actions
405 where
406 payroll_action_id = p_user_payroll_action_id;
407
408 -- Get the owner's tax quarter and tax year
409 select
410 prd_information1, prd_information2
411 into
412 l_owner_tax_year, l_owner_tax_quarter
413 from
414 per_time_periods
415 where
416 payroll_id = l_owner_payroll_id
417 and
418 l_owner_date_earned between start_date and end_date;
419
420 -- Get the user's tax quarter and tax year
421 select
422 prd_information1, prd_information2
423 into
424 l_user_tax_year, l_user_tax_quarter
425 from
426 per_time_periods
427 where
428 payroll_id = l_user_payroll_id
429 and
430 l_user_date_earned between start_date and end_date;
431
432 -- If the tax quarters and years are the same then the dimension has not expired
433 if (l_owner_tax_year = l_user_tax_year) and
434 (l_owner_tax_quarter = l_user_tax_quarter) then
435 */
436 --added for 5486039
437
438 ASG_TAX_QTD_EC(p_owner_payroll_action_id => p_owner_payroll_action_id
439 ,p_user_payroll_action_id => p_user_payroll_action_id
440 ,p_owner_assignment_action_id => p_owner_assignment_action_id
441 ,p_user_assignment_action_id => p_user_assignment_action_id
442 ,p_owner_effective_date => p_owner_effective_date
443 ,p_user_effective_date => p_user_effective_date
444 ,p_dimension_name => p_dimension_name
445 ,p_expiry_information => l_expiry_date
446 );
447
448 IF p_user_effective_date <= l_expiry_date THEN
449 -- Dimension has not expired
450 p_expiry_information := 0;
451 g_za_tq_expiry_information := 0;
452
453 else
454
455 -- Dimension has expired
456 p_expiry_information := 1;
457 g_za_tq_expiry_information := 1;
458
459 end if;
460
461 g_za_tq_owner_pay_action_id := p_owner_payroll_action_id;
462 g_za_tq_user_pay_action_id := p_user_payroll_action_id;
463
464 end if; -- End check expiry
465
466 end ASG_TAX_QTD_EC;
467 --
468 -- 5189195
469 --
470 PROCEDURE ASG_TAX_QTD_EC
471 (
472 p_owner_payroll_action_id in number, -- run created balance.
473 p_user_payroll_action_id in number, -- current run.
474 p_owner_assignment_action_id in number, -- assact created balance.
475 p_user_assignment_action_id in number, -- current assact..
476 p_owner_effective_date in date, -- eff date of balance.
477 p_user_effective_date in date, -- eff date of current run.
478 p_dimension_name in varchar2, -- balance dimension name.
479 p_expiry_information out nocopy date -- dimension expired flag.
480 )
481 is
482 l_tax_start_day varchar2(50);
483 l_tax_year varchar2(50);
484 l_tax_year_start_date date;
485 --
486 BEGIN
487 select rule_mode
488 into l_tax_start_day
489 from pay_legislation_rules
490 where legislation_code = 'ZA'
491 and rule_type = 'L';
492 --
493 select to_char(p_owner_effective_date,'YYYY')
494 into l_tax_year
495 from dual;
496 --
497 SELECT to_date(l_tax_start_day||'-'||l_tax_year,'DD/MM/YYYY')
498 INTO l_tax_year_start_date
499 FROM dual;
500 --
501 SELECT (add_months(l_tax_year_start_date,
502 (floor(months_between(p_owner_effective_date, l_tax_year_start_date) / 3) + 1) * 3) -1 )
503 INTO p_expiry_information
504 FROM dual;
505 --
506 end ASG_TAX_QTD_EC;
507 --
508 --------------------------------- ASG_MTD_EC --------------------------------------
509 -- NAME
510 -- ASG_MTD_EC - Assignment Month to Date expiry check.
511 -- DESCRIPTION
512 -- Expiry checking code for the following:
513 -- ZA Assignment-level Month to Date Balance Dimension
514 -- NOTES
515 -- The associated dimension is expiry checked at payroll action level
516 -- 1 means expired
517 ---------------------------------------------------------------------------------------
518 procedure ASG_MTD_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 number -- dimension expired flag.
528 )
529 is
530
531 l_owner_date_earned date;
532 l_user_date_earned date;
533 l_owner_payroll_id number;
534 l_user_payroll_id number;
535 l_owner_month date;
536 l_user_month date;
537 l_expiry_date date;
538 begin
539
540 -- If the payroll actions have not changed, return the stored result
541 if (p_owner_payroll_action_id = g_za_m_owner_pay_action_id) and
542 (p_user_payroll_action_id = g_za_m_user_pay_action_id) then
543
544 p_expiry_information := g_za_m_expiry_information;
545
546 else -- Check expiry
547 /*
548 -- Get the owner's date earned and payroll id
549 select
550 date_earned, payroll_id
551 into
552 l_owner_date_earned, l_owner_payroll_id
553 from
554 pay_payroll_actions
555 where
556 payroll_action_id = p_owner_payroll_action_id;
557
558 -- Get the user's date earned and payroll id
559 select
560 date_earned, payroll_id
561 into
562 l_user_date_earned, l_user_payroll_id
563 from
564 pay_payroll_actions
565 where
566 payroll_action_id = p_user_payroll_action_id;
567
568 -- Get the owner's month end
569 select
570 pay_advice_date
571 into
572 l_owner_month
573 from
574 per_time_periods
575 where
576 payroll_id = l_owner_payroll_id
577 and
578 l_owner_date_earned between start_date and end_date;
579
580 -- Get the user's month end
581 select
582 pay_advice_date
583 into
584 l_user_month
585 from
586 per_time_periods
587 where
588 payroll_id = l_user_payroll_id
589 and
590 l_user_date_earned between start_date and end_date;
591
592 -- If the month ends are the same then the dimension has not expired
593 if l_owner_month = l_user_month then
594 */
595 --added for 5486039
596
597 ASG_MTD_EC(p_owner_payroll_action_id => p_owner_payroll_action_id
598 ,p_user_payroll_action_id => p_user_payroll_action_id
599 ,p_owner_assignment_action_id => p_owner_assignment_action_id
600 ,p_user_assignment_action_id => p_user_assignment_action_id
601 ,p_owner_effective_date => p_owner_effective_date
602 ,p_user_effective_date => p_user_effective_date
603 ,p_dimension_name => p_dimension_name
604 ,p_expiry_information => l_expiry_date
605 );
606
607 IF p_user_effective_date <= l_expiry_date THEN
608 -- Dimension has not expired
609 p_expiry_information := 0;
610 g_za_m_expiry_information := 0;
611
612 else
613
614 -- Dimension has expired
615 p_expiry_information := 1;
616 g_za_m_expiry_information := 1;
617
618 end if;
619
620 g_za_m_owner_pay_action_id := p_owner_payroll_action_id;
621 g_za_m_user_pay_action_id := p_user_payroll_action_id;
622
623 end if; -- End check expiry
624
625 end ASG_MTD_EC;
626 --
627 -- 5189195
628 procedure ASG_MTD_EC
629 (
630 p_owner_payroll_action_id in number, -- run created balance.
631 p_user_payroll_action_id in number, -- current run.
632 p_owner_assignment_action_id in number, -- assact created balance.
633 p_user_assignment_action_id in number, -- current assact.
634 p_owner_effective_date in date, -- eff date of balance.
635 p_user_effective_date in date, -- eff date of current run.
636 p_dimension_name in varchar2, -- balance dimension name.
637 p_expiry_information out NOCOPY date) -- dimension expired date.
638 is
639 begin
640 --
641 /* Commented for Bug 8624178
642 SELECT ((trunc(add_months(p_owner_effective_date,1),'MM'))-1)
643 INTO p_expiry_information
644 FROM dual;
645 */
646
647 SELECT ptp.pay_advice_date
648 INTO p_expiry_information
649 FROM per_time_periods ptp
650 ,pay_payroll_actions ppa
651 WHERE ppa.payroll_action_id = p_owner_payroll_action_id
652 AND ppa.payroll_id = ptp.payroll_id
653 AND p_owner_effective_date BETWEEN ptp.start_date AND ptp.end_date;
654
655 --
656 end ASG_MTD_EC;
657 --
658 --------------------------------- ASG_PTD_EC --------------------------------------
659 -- NAME
660 -- ASG_PTD_EC - Assignment Period to Date expiry check.
661 -- DESCRIPTION
662 -- Expiry checking code for the following:
663 -- ZA Assignment-level Period to Date Balance Dimension
664 -- NOTES
665 -- The associated dimension is expiry checked at payroll action level
666 -- 1 means expired
667 ---------------------------------------------------------------------------------------
668 procedure ASG_PTD_EC
669 (
670 p_owner_payroll_action_id in number, -- run created balance.
671 p_user_payroll_action_id in number, -- current run.
672 p_owner_assignment_action_id in number, -- assact created balance.
673 p_user_assignment_action_id in number, -- current assact..
674 p_owner_effective_date in date, -- eff date of balance.
675 p_user_effective_date in date, -- eff date of current run.
676 p_dimension_name in varchar2, -- balance dimension name.
677 p_expiry_information out nocopy number -- dimension expired flag.
678 )
679 is
680
681 l_owner_time_period_id number;
682 l_user_time_period_id number;
683 l_expiry_date date;
684
685 begin
686
687 -- Select the period of the owning and using action and if they are the same then
688 -- the dimension has expired - either a prior period or a different payroll
689 /*
690 -- Get the owner's time_period_id
691 select
692 time_period_id
693 into
694 l_owner_time_period_id
695 from
696 pay_payroll_actions
697 where
698 payroll_action_id = p_owner_payroll_action_id;
699
700 -- Get the user's time_period_id
701 select
702 time_period_id
703 into
704 l_user_time_period_id
705 from
706 pay_payroll_actions
707 where
708 payroll_action_id = p_user_payroll_action_id;
709
710
711 -- If the time periods are the same then the dimension has not expired
712 if l_owner_time_period_id = l_user_time_period_id then
713 */
714 --added for 5486039
715
716 ASG_PTD_EC(p_owner_payroll_action_id => p_owner_payroll_action_id
717 ,p_user_payroll_action_id => p_user_payroll_action_id
718 ,p_owner_assignment_action_id => p_owner_assignment_action_id
719 ,p_user_assignment_action_id => p_user_assignment_action_id
720 ,p_owner_effective_date => p_owner_effective_date
721 ,p_user_effective_date => p_user_effective_date
722 ,p_dimension_name => p_dimension_name
723 ,p_expiry_information => l_expiry_date
724 );
725
726 IF p_user_effective_date <= l_expiry_date THEN
727 -- Dimension has not expired
728 p_expiry_information := 0;
729 else
730 -- Dimension has expired
731 p_expiry_information := 1;
732
733 end if;
734
735 end ASG_PTD_EC;
736 --
737 -- 5189195
738 --
739 procedure ASG_PTD_EC
740 (
741 p_owner_payroll_action_id in number, -- run created balance.
742 p_user_payroll_action_id in number, -- current run.
743 p_owner_assignment_action_id in number, -- assact created balance.
744 p_user_assignment_action_id in number, -- current assact..
745 p_owner_effective_date in date, -- eff date of balance.
746 p_user_effective_date in date, -- eff date of current run.
747 p_dimension_name in varchar2, -- balance dimension name.
748 p_expiry_information out nocopy DATE -- dimension expired flag.
749 ) IS
750 BEGIN
751 SELECT ptp.end_date
752 INTO p_expiry_information
753 FROM per_time_periods ptp
754 ,pay_payroll_actions ppa
755 WHERE ppa.payroll_action_id = p_owner_payroll_action_id
756 AND ppa.payroll_id = ptp.payroll_id
757 AND p_owner_effective_date BETWEEN ptp.start_date AND ptp.end_date;
758 END ASG_PTD_EC;
759 --
760 end pyzaexc;
[Home] [Help]