1 package body pygbexc as
2 /* $Header: pygbexc.pkb 120.0.12010000.2 2008/08/06 07:17:02 ubhat ship $ */
3 -- cache result of expiry check
4 g_gb_owner_payroll_action_id number; -- run created balance.
5 g_gb_user_payroll_action_id number; -- current run.
6 g_gb_expiry_information number; -- dimension expired flag.
7 /*------------------------------ ASG_RUN_EC ----------------------------*/
8 /*
9 NAME
10 ASG_RUN_EC - Assignment Run to Date expiry check.
11 DESCRIPTION
12 Expiry checking code for the following:
13 GB Assignment-level Run To Date Balance Dimension
14 NOTES
15 The associated dimension is expiry checked at payroll action level
16 */
17 procedure ASG_RUN_EC
18 (
19 p_owner_payroll_action_id in number, -- run created balance.
20 p_user_payroll_action_id in number, -- current run.
21 p_owner_assignment_action_id in number, -- assact created balance.
22 p_user_assignment_action_id in number, -- current assact..
23 p_owner_effective_date in date, -- eff date of balance.
24 p_user_effective_date in date, -- eff date of current run.
25 p_dimension_name in varchar2, -- balance dimension name.
26 p_expiry_information out nocopy number -- dimension expired flag.
27 ) is
28
29 begin
30 if p_user_payroll_action_id = p_owner_payroll_action_id then
31 p_expiry_information := 0;
32 else
33 p_expiry_information := 1;
34 end if;
35
36 end ASG_RUN_EC;
37
38 /*------------------------------ ASG_PROC_PTD_EC ----------------------------*/
39 /*
40 NAME
41 ASG_PROC_PTD_EC - Assignment Processing Period to Date expiry check.
42 DESCRIPTION
43 Expiry checking code for the following:
44 GB Assignment-level Process Period To Date Balance Dimension
45 NOTES
46 The associated dimension is expiry checked at payroll action level
47 */
48 procedure ASG_PROC_PTD_EC
49 (
50 p_owner_payroll_action_id in number, -- run created balance.
51 p_user_payroll_action_id in number, -- current run.
52 p_owner_assignment_action_id in number, -- assact created balance.
53 p_user_assignment_action_id in number, -- current assact..
54 p_owner_effective_date in date, -- eff date of balance.
55 p_user_effective_date in date, -- eff date of current run.
56 p_dimension_name in varchar2, -- balance dimension name.
57 p_expiry_information out nocopy number -- dimension expired flag.
58 ) is
59 l_user_time_period_id number;
60 l_owner_time_period_id number;
61 begin
62 /*
63 * Select the period of the owning and using action and if they are
64 * the same then the dimension has expired - either a prior period
65 * or a different payroll
66 */
67
68 select time_period_id
69 into l_user_time_period_id
70 from pay_payroll_actions
71 where payroll_action_id = p_user_payroll_action_id;
72
73 select time_period_id
74 into l_owner_time_period_id
75 from pay_payroll_actions
76 where payroll_action_id = p_owner_payroll_action_id;
77
78 if l_user_time_period_id = l_owner_time_period_id then
79 p_expiry_information := 0;
80 else
81 p_expiry_information := 1;
82 end if;
83
84 end ASG_PROC_PTD_EC;
85
86 /*------------------------------ ASG_PROC_YTD_EC ----------------------------*/
87 /*
88 NAME
89 ASG_PROC_YTD_EC - Assignment Processing Year to Date expiry check.
90 DESCRIPTION
91 Expiry checking code for the following:
92 GB Assignment-level Process Year To Date Balance Dimension
93 NOTES
94 The associated dimension is expiry checked at payroll action level
95 */
96 procedure ASG_PROC_YTD_EC
97 (
98 p_owner_payroll_action_id in number, -- run created balance.
99 p_user_payroll_action_id in number, -- current run.
100 p_owner_assignment_action_id in number, -- assact created balance.
101 p_user_assignment_action_id in number, -- current assact..
102 p_owner_effective_date in date, -- eff date of balance.
103 p_user_effective_date in date, -- eff date of current run.
104 p_dimension_name in varchar2, -- balance dimension name.
105 p_expiry_information out nocopy number -- dimension expired flag.
106 ) is
107 l_tax_year_start date;
108 l_pay_year_start date;
109 l_user_payroll_id number;
110 l_owning_regular_payment_date date;
111 begin
112 -- if the payroll actions have not changed return stored result
113 if (p_owner_payroll_action_id = g_gb_owner_payroll_action_id)
114 and (p_user_payroll_action_id = g_gb_user_payroll_action_id) then
115 p_expiry_information := g_gb_expiry_information;
116 else -- [ check expiry
117 /* select the start of the financial year - if the owning action is
118 * before this or for a different payroll then its expired
119 */
120 Select to_date('06-04-' || to_char( fnd_number.canonical_to_number(
121 to_char( PTP.regular_payment_date,'YYYY'))
122 + decode(sign( PTP.regular_payment_date - to_date('06-04-'
123 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
124 -1,-1,0)),'DD-MM-YYYY') finyear, BACT.payroll_id
125 into l_tax_year_start, l_user_payroll_id
126 from per_time_periods PTP,
127 pay_payroll_actions BACT
128 where BACT.payroll_action_id = p_user_payroll_action_id
129 and PTP.time_period_id = BACT.time_period_id;
130 --
131 -- find the regular payment date for the owning action
132 --
133 select regular_payment_date
134 into l_owning_regular_payment_date
135 from pay_payroll_actions PACT,
136 per_time_periods PTP
137 where PACT.payroll_action_id = p_owner_payroll_action_id
138 and PTP.time_period_id = PACT.time_period_id;
139 --
140 if l_owning_regular_payment_date < l_tax_year_start then
141 p_expiry_information := 1;
142 g_gb_expiry_information := 1;
143 else
144 p_expiry_information := 0;
145 g_gb_expiry_information := 0;
146 end if;
147 g_gb_owner_payroll_action_id := p_owner_payroll_action_id;
148 g_gb_user_payroll_action_id := p_user_payroll_action_id;
149 end if; -- ] end check expiry
150 --
151
152 end ASG_PROC_YTD_EC;
153
154 -- For 115.11
155
156 /*------------------------------ ASG_PEN_YTD_EC ----------------------------*/
157 /*
158 NAME
159 ASG_PEN_YTD_EC - Assignment Processing Pension Year to Date expiry check.
160 DESCRIPTION
161 Expiry checking code for the following:
162 GB Assignment-level Process Pension Year To Date Balance Dimension
163 NOTES
164 The associated dimension is expiry checked at payroll action level
165 */
166 procedure ASG_PEN_YTD_EC
167 (
168 p_owner_payroll_action_id in number, -- run created balance.
169 p_user_payroll_action_id in number, -- current run.
170 p_owner_assignment_action_id in number, -- assact created balance.
171 p_user_assignment_action_id in number, -- current assact..
172 p_owner_effective_date in date, -- eff date of balance.
173 p_user_effective_date in date, -- eff date of current run.
174 p_dimension_name in varchar2, -- balance dimension name.
175 p_expiry_information out nocopy number -- dimension expired flag.
176 ) is
177 l_tax_year_start date;
178 l_pay_year_start date;
179 l_user_payroll_id number;
180 l_owning_regular_payment_date date;
181 begin
182 -- if the payroll actions have not changed return stored result
183 if (p_owner_payroll_action_id = g_gb_owner_payroll_action_id)
184 and (p_user_payroll_action_id = g_gb_user_payroll_action_id) then
185 p_expiry_information := g_gb_expiry_information;
186 else -- [ check expiry
187 /* select the start of the financial year - if the owning action is
188 * before this or for a different payroll then its expired
189 */
190 Select to_date('01-04-' || to_char( fnd_number.canonical_to_number(
191 to_char( PTP.regular_payment_date,'YYYY'))
192 + decode(sign( PTP.regular_payment_date - to_date('01-04-'
193 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
194 -1,-1,0)),'DD-MM-YYYY') finyear, BACT.payroll_id
195 into l_tax_year_start, l_user_payroll_id
196 from per_time_periods PTP,
197 pay_payroll_actions BACT
198 where BACT.payroll_action_id = p_user_payroll_action_id
199 and PTP.time_period_id = BACT.time_period_id;
200 --
201 -- find the regular payment date for the owning action
202 --
203 select regular_payment_date
204 into l_owning_regular_payment_date
205 from pay_payroll_actions PACT,
206 per_time_periods PTP
207 where PACT.payroll_action_id = p_owner_payroll_action_id
208 and PTP.time_period_id = PACT.time_period_id;
209 --
210 if l_owning_regular_payment_date < l_tax_year_start then
211 p_expiry_information := 1;
212 g_gb_expiry_information := 1;
213 else
214 p_expiry_information := 0;
215 g_gb_expiry_information := 0;
216 end if;
217 g_gb_owner_payroll_action_id := p_owner_payroll_action_id;
218 g_gb_user_payroll_action_id := p_user_payroll_action_id;
219 end if; -- ] end check expiry
220 --
221
222 end ASG_PEN_YTD_EC;
223
224 /*------------------------------ ASG_STAT_YTD_EC ----------------------------*/
225 /*
226 NAME
227 ASG_STAT_YTD_EC - Assignment Statutory Year to DAte expiry check
228 DESCRIPTION
229 Expiry checking code for the following:
230 GB Assignment-level Statutory Year to Date dimension
231 NOTES
232 The associated dimension is expiry checked at payroll action level
233 */
234 procedure ASG_STAT_YTD_EC
235 (
236 p_owner_payroll_action_id in number, -- run created balance.
237 p_user_payroll_action_id in number, -- current run.
238 p_owner_assignment_action_id in number, -- assact created balance.
239 p_user_assignment_action_id in number, -- current assact..
240 p_owner_effective_date in date, -- eff date of balance.
241 p_user_effective_date in date, -- eff date of current run.
242 p_dimension_name in varchar2, -- balance dimension name.
243 p_expiry_information out nocopy number -- dimension expired flag.
244 ) is
245 l_tax_year_start date;
246 begin
247 select to_date('06-04-' || to_char( fnd_number.canonical_to_number(
248 to_char( p_user_effective_date,'YYYY'))
249 + decode(sign( p_user_effective_date - to_date('06-04-'
250 || to_char( p_user_effective_date,'YYYY'),'DD-MM-YYYY')),
251 -1,-1,0)),'DD-MM-YYYY')
252 into l_tax_year_start
253 from pay_payroll_actions BACT
254 where BACT.payroll_action_id = p_user_payroll_action_id;
255 --
256 if p_owner_effective_date < l_tax_year_start then
257 p_expiry_information := 1;
258 else
259 p_expiry_information := 0;
260 end if;
261
262 end ASG_STAT_YTD_EC;
263 /*------------------------------ ASG_USER_EC ----------------------------*/
264 /*
265 NAME
266 ASG_USER_EC - Assignment user dimension expiry check.
267 DESCRIPTION
268 Expiry checking code for the following:
269 GB Assignment-level user dimension
270 NOTES
271 The associated dimension is expiry checked at payroll action level
272 */
273 procedure ASG_USER_EC
274 (
275 p_owner_payroll_action_id in number, -- run created balance.
276 p_user_payroll_action_id in number, -- current run.
277 p_owner_assignment_action_id in number, -- assact created balance.
278 p_user_assignment_action_id in number, -- current assact..
279 p_owner_effective_date in date, -- eff date of balance.
280 p_user_effective_date in date, -- eff date of current run.
281 p_dimension_name in varchar2, -- balance dimension name.
282 p_expiry_information out nocopy number -- dimension expired flag.
283 ) is
284 l_tax_year_start date;
285 l_pay_year_start date;
286 l_user_payroll_id number;
287
288 l_user_regular_payment_date date;
289 l_business_group_id number;
290 l_owning_regular_payment_date date;
291 l_span_start date;
292
293
294 begin
295
296 -- find the regular payment date for the using action
297 select regular_payment_date, BACT.business_group_id
298 into l_user_regular_payment_date, l_business_group_id
299 from pay_payroll_actions BACT,
300 per_time_periods PTP
301 where BACT.payroll_action_id = p_user_payroll_action_id
302 and PTP.time_period_id = BACT.time_period_id;
303
304 -- find the regular payment date for the owning action
305 select regular_payment_date
306 into l_owning_regular_payment_date
307 from pay_payroll_actions PACT,
308 per_time_periods PTP
309 where PACT.payroll_action_id = p_owner_payroll_action_id
310 and PTP.time_period_id = PACT.time_period_id;
311
312 -- find when the dimension last cleared down
313 l_span_start := hr_gbbal.dimension_reset_date( p_dimension_name,
314 l_user_regular_payment_date,
315 l_business_group_id);
316
317
318 -- is the user action since this date
319 --
320 --
321 if l_owning_regular_payment_date < l_span_start then
322 p_expiry_information := 1;
323 else
324 p_expiry_information := 0;
325 end if;
326 --
327
328 end ASG_USER_EC;
329
330 /*------------------------------ ASG_PROC_TWO_YTD_EC ----------------------------*/
331 /*
332 NAME
333 ASG_PROC_TWO_YTD_EC - Assignment Processing Year to Date expiry check
334 for 2 yearly balance.
335 DESCRIPTION
336 Expiry checking code for the following:
337 GB Assignment level Last Two Years to Date
338 NOTES
339 The associated dimension is expiry checked at payroll action level
340 */
341 procedure ASG_PROC_TWO_YTD_EC
342 (
343 p_owner_payroll_action_id in number, -- run created balance.
344 p_user_payroll_action_id in number, -- current run.
345 p_owner_assignment_action_id in number, -- assact created balance.
346 p_user_assignment_action_id in number, -- current assact..
347 p_owner_effective_date in date, -- eff date of balance.
348 p_user_effective_date in date, -- eff date of current run.
349 p_dimension_name in varchar2, -- balance dimension name.
350 p_expiry_information out nocopy number -- dimension expired flag.
351 ) is
352 l_tax_year_start date;
353 l_regular_payment_date date;
354 l_pay_year_start date;
355 l_tax_yyyy_start number;
356 l_user_payroll_id number;
357 begin
358 --
359 -- select the start of the financial year - if the owning action is
360 -- before this or for a different payroll then its expired
361 --
362 -- If the tax year is even the the even dimension should expire
363 -- else if the tax year is odd then the odd dimension should expire.
364 -- Hence get the start of the tax year for this year or last year based
365 -- on the logic given below
366 --
367 -- skutteti added _PER_TD_ODD_TWO_YTD into the if clause below as the
368 -- same procedure is used for the expiry checking of the person level
369 -- latest balances on 11/mar/02.
370 --
371 if p_dimension_name IN ('_ASG_TD_ODD_TWO_YTD','_PER_TD_ODD_TWO_YTD') then
372 --
373 Select to_date('06-04-' || to_char( fnd_number.canonical_to_number(
374 to_char( PTP.regular_payment_date,'YYYY'))
375 + decode(sign( PTP.regular_payment_date - to_date('06-04-'
376 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
377 -1,-1,0) -
378 mod(
379 fnd_number.canonical_to_number(
380 to_char( PTP.regular_payment_date,'YYYY'))
381 + decode(sign( PTP.regular_payment_date - to_date('06-04-'
382 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
383 -1,-1,0),2)
384 ),'DD-MM-YYYY') finyear, BACT.payroll_id
385 into l_tax_year_start, l_user_payroll_id
386 from per_time_periods PTP,
387 pay_payroll_actions BACT
388 where BACT.payroll_action_id = p_user_payroll_action_id
389 and PTP.time_period_id = BACT.time_period_id;
390 --
391 elsif p_dimension_name in ('_ASG_TD_EVEN_TWO_YTD','_PER_TD_EVEN_TWO_YTD') then
392 --
393 Select to_date('06-04-' || to_char( fnd_number.canonical_to_number(
394 to_char( PTP.regular_payment_date,'YYYY'))
395 + decode(sign( PTP.regular_payment_date - to_date('06-04-'
396 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
397 -1,-1,0) -
398 mod(
399 fnd_number.canonical_to_number(
400 to_char( PTP.regular_payment_date,'YYYY'))
401 + decode(sign( PTP.regular_payment_date - to_date('06-04-'
402 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
403 -1,0,-1),2)
404 ),'DD-MM-YYYY') finyear, BACT.payroll_id
405 into l_tax_year_start, l_user_payroll_id
406 from per_time_periods PTP,
407 pay_payroll_actions BACT
408 where BACT.payroll_action_id = p_user_payroll_action_id
409 and PTP.time_period_id = BACT.time_period_id;
410
411 end if;
412 --
413 Select min(TP.start_date)
414 into l_pay_year_start
415 from per_time_periods TP
416 where TP.payroll_id = l_user_payroll_id
417 and TP.regular_payment_date >= l_tax_year_start;
418 --
419 --
420 if p_owner_effective_date < l_pay_year_start then
421 p_expiry_information := 1;
422 else
423 p_expiry_information := 0;
424 end if;
425 --
426 --
427 end ASG_PROC_TWO_YTD_EC;
428 --
429 /*---------------------------- PER_TD_STAT_PTD_EC ----------------------------*/
430 /*
431 NAME
432 PER_TD_STAT_PTD_EC Person level TD Stat Expiry Checking
433 DESCRIPTION
434 Expiry checking code for the following:
435 GB PERSON level TD Statutory Period Dimension
436 NOTES
437 The associated dimension is expiry checked at ASSIGNMENT Action level
438 hence extra parameter.
439 */
440 procedure PER_TD_STAT_PTD_EC
441 (
442 p_owner_payroll_action_id in number, -- run created balance.
443 p_user_payroll_action_id in number, -- current run.
444 p_owner_assignment_action_id in number, -- assact created balance.
445 p_user_assignment_action_id in number, -- current assact..
446 p_owner_effective_date in date, -- eff date of balance.
447 p_user_effective_date in date, -- eff date of current run.
448 p_dimension_name in varchar2, -- balance dimension name.
449 p_balance_context_values in varchar2, -- list of context values
450 p_expiry_information out nocopy number -- dimension expired flag.
451 ) is
452 l_span_start date;
453 l_owning_regular_payment_date date;
454 --
455 begin
456 --
457 -- find the regular payment date for the owning action
458 --
459 select regular_payment_date
460 into l_owning_regular_payment_date
461 from pay_payroll_actions PACT,
462 per_time_periods PTP
463 where PACT.payroll_action_id = p_owner_payroll_action_id
464 and PTP.time_period_id = PACT.time_period_id;
465 --
466 -- check that the beginning of the Person Level Period is before the
467 -- using action. This could be a different period size so call the
468 -- period span start with the using action id.
469 --
470 l_span_start :=
471 hr_gbnidir.PAYE_STAT_PERIOD_START_DATE(p_user_assignment_action_id);
472 --
473 IF l_owning_regular_payment_date < l_span_start then
474 p_expiry_information := 1;
475 ELSE
476 p_expiry_information := 0;
477 END IF;
478 --
479 end PER_TD_STAT_PTD_EC;
480 -----------------------------------------------------------------------
481 -- Procedure: PROC_YTD_START
482 -- Description: used by YTD Dimensions for Run Level Balances only.
483 -- This procedure accepts a date and assignment action and other
484 -- params, and returns the start date of that Tax Year, depending
485 -- on the regular payment date of the payroll action (similar to
486 -- above expiry checks).
487 -----------------------------------------------------------------------
488 --
489 procedure proc_ytd_start(p_period_type in varchar2 default null,
490 p_effective_date in date default null,
491 p_start_date out nocopy date,
492 p_start_date_code in varchar2 default null,
493 p_payroll_id in number,
494 p_bus_grp in number default null,
495 p_action_type in varchar2 default null,
496 p_asg_action in number)
497 is
498 l_tax_year_start date;
499 begin
500 select to_date('06-04-' || to_char( fnd_number.canonical_to_number(
501 to_char( PTP.regular_payment_date,'YYYY'))
502 + decode(sign( PTP.regular_payment_date - to_date('06-04-'
503 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
504 -1,-1,0)),'DD-MM-YYYY') finyear
505 into l_tax_year_start
506 from per_time_periods PTP,
507 pay_payroll_actions ppa,
508 pay_assignment_actions paa
509 where ppa.payroll_action_id = paa.payroll_action_id
510 and paa.assignment_action_id = p_asg_action
511 and ppa.payroll_id = p_payroll_id
512 and PTP.time_period_id = ppa.time_period_id;
513 --
514 p_start_date := l_tax_year_start;
515 --
516 end proc_ytd_start;
517 ----------------------------------------------------------------------
518 -- Procedure: PROC_ODD_YTD_START
519 -- Description: used by ODD_YTD Dimensions for Run Level Balances only.
520 -- This procedure accepts a date and assignment action and other
521 -- params, and returns the start date of the Last previous ODD
522 -- Tax Year, depending
523 -- on the regular payment date of the payroll action (similar to
524 -- above expiry checks). For 2 year balance dimensions.
525 -----------------------------------------------------------------------
526 --
527 procedure proc_odd_ytd_start(p_period_type in varchar2 default null,
528 p_effective_date in date default null,
529 p_start_date out nocopy date,
530 p_start_date_code in varchar2 default null,
531 p_payroll_id in number,
532 p_bus_grp in number default null,
533 p_action_type in varchar2 default null,
534 p_asg_action in number)
535 is
536 l_tax_year_start date;
537 l_odd_tax_year_start date;
538 l_odd_adjust number;
539 begin
540 select to_date('06-04-' || to_char( fnd_number.canonical_to_number(
541 to_char( PTP.regular_payment_date,'YYYY'))
542 + decode(sign( PTP.regular_payment_date - to_date('06-04-'
543 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
544 -1,-1,0)),'DD-MM-YYYY') finyear
545 into l_tax_year_start
546 from per_time_periods PTP,
547 pay_payroll_actions ppa,
548 pay_assignment_actions paa
549 where ppa.payroll_action_id = paa.payroll_action_id
550 and paa.assignment_action_id = p_asg_action
551 and ppa.payroll_id = p_payroll_id
552 and PTP.time_period_id = ppa.time_period_id;
553 --
554 IF mod(to_number(to_char(l_tax_year_start,'yyyy')),2) = 1 THEN
555 -- The start of tax year is ODD, no action.
556 l_odd_adjust := 0;
557 ELSE
558 -- The start of tax year is in an EVEN year, must subtract 1
559 l_odd_adjust := 1;
560 END IF;
561 --
562 l_odd_tax_year_start := to_date('06-04-' || to_char(fnd_number.canonical_to_number(
563 to_char(l_tax_year_start,'yyyy')) - l_odd_adjust)
564 ,'DD-MM-YYYY');
565 p_start_date := l_odd_tax_year_start;
566 --
567 end proc_odd_ytd_start;
568 ----------------------------------------------------------------------
569 -- Procedure: PROC_EVEN_YTD_START
570 -- Description: used by EVEN_YTD Dimensions for Run Level Balances only.
571 -- This procedure accepts a date and assignment action and other
572 -- params, and returns the start date of the Last previous EVEN
573 -- Tax Year, depending
574 -- on the regular payment date of the payroll action (similar to
575 -- above expiry checks). For 2 year balance dimensions.
576 -----------------------------------------------------------------------
577 --
578 procedure proc_even_ytd_start(p_period_type in varchar2 default null,
579 p_effective_date in date default null,
580 p_start_date out nocopy date,
581 p_start_date_code in varchar2 default null,
582 p_payroll_id in number,
583 p_bus_grp in number default null,
584 p_action_type in varchar2 default null,
585 p_asg_action in number)
586 is
587 l_tax_year_start date;
588 l_even_tax_year_start date;
589 l_even_adjust number;
590 begin
591 select to_date('06-04-' || to_char( fnd_number.canonical_to_number(
592 to_char( PTP.regular_payment_date,'YYYY'))
593 + decode(sign( PTP.regular_payment_date - to_date('06-04-'
594 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
595 -1,-1,0)),'DD-MM-YYYY') finyear
596 into l_tax_year_start
597 from per_time_periods PTP,
598 pay_payroll_actions ppa,
599 pay_assignment_actions paa
600 where ppa.payroll_action_id = paa.payroll_action_id
601 and paa.assignment_action_id = p_asg_action
602 and ppa.payroll_id = p_payroll_id
603 and PTP.time_period_id = ppa.time_period_id;
604 --
605 IF mod(to_number(to_char(l_tax_year_start,'yyyy')),2) = 0 THEN
606 -- The start of tax year is EVEN, no action.
607 l_even_adjust := 0;
608 ELSE
609 -- The start of tax year is in an ODD year, must subtract 1
610 l_even_adjust := 1;
611 END IF;
612 --
613 l_even_tax_year_start := to_date('06-04-' || to_char(fnd_number.canonical_to_number(
614 to_char(l_tax_year_start,'yyyy')) - l_even_adjust)
615 ,'DD-MM-YYYY');
616 p_start_date := l_even_tax_year_start;
617 --
618 end proc_even_ytd_start;
619
620 --For 115.11
621 -----------------------------------------------------------------------
622 -- Procedure: PROC_PEN_YTD_START
623 -- Description: used by YTD Dimensions for Run Level Balances only.
624 -- This procedure accepts a date and assignment action and other
625 -- params, and returns the start date of that Pension Year, depending
626 -- on the regular payment date of the payroll action (similar to
627 -- above expiry checks).
628 -----------------------------------------------------------------------
629 --
630 procedure proc_pen_ytd_start(p_period_type in varchar2 default null,
631 p_effective_date in date default null,
632 p_start_date out nocopy date,
633 p_start_date_code in varchar2 default null,
634 p_payroll_id in number,
635 p_bus_grp in number default null,
636 p_action_type in varchar2 default null,
637 p_asg_action in number)
638 is
639 l_tax_year_start date;
640 begin
641 select to_date('01-04-' || to_char( fnd_number.canonical_to_number(
642 to_char( PTP.regular_payment_date,'YYYY'))
643 + decode(sign( PTP.regular_payment_date - to_date('01-04-'
644 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
645 -1,-1,0)),'DD-MM-YYYY') finyear
646 into l_tax_year_start
647 from per_time_periods PTP,
648 pay_payroll_actions ppa,
649 pay_assignment_actions paa
650 where ppa.payroll_action_id = paa.payroll_action_id
651 and paa.assignment_action_id = p_asg_action
652 and ppa.payroll_id = p_payroll_id
653 and PTP.time_period_id = ppa.time_period_id;
654 --
655 p_start_date := l_tax_year_start;
656 --
657 end proc_pen_ytd_start;
658
659 -----------------------------------------------------------------------
660 end pygbexc;
[Home] [Help]