1 package body pygbexc as
2 /* $Header: pygbexc.pkb 120.3 2011/03/23 13:59:02 pbalu noship $ */
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 -- For 115.12
87
88 procedure ASG_PROC_PTD_EC
89 (
90 p_owner_payroll_action_id in number, -- run created balance.
91 p_user_payroll_action_id in number, -- current run.
92 p_owner_assignment_action_id in number, -- assact created balance.
93 p_user_assignment_action_id in number, -- current assact..
94 p_owner_effective_date in date, -- eff date of balance.
95 p_user_effective_date in date, -- eff date of current run.
96 p_dimension_name in varchar2, -- balance dimension name.
97 p_expiry_information out nocopy date -- dimension expired flag.
98 ) is
99
100 begin
101
102 hr_utility.set_location ('In Overloaded ASG_PROC_PTD_EC',10);
103 hr_utility.set_location ('Namish:p_owner_payroll_action_id:'||p_owner_payroll_action_id,10);
104 hr_utility.set_location ('Namish:p_owner_effective_date:'||p_owner_effective_date,10);
105 hr_utility.set_location ('Namish:p_user_payroll_action_id:'||p_user_payroll_action_id,10);
106 hr_utility.set_location ('Namish:p_user_effective_date:'||p_user_effective_date,10);
107 hr_utility.set_location ('Nam - Asg Action ID: '||p_owner_assignment_action_id||' - '||p_user_assignment_action_id||' - '||p_dimension_name,11);
108
109 /* 11887149 begin
110 SELECT ptp.end_date
111 INTO p_expiry_information
112 FROM per_time_periods ptp
113 ,pay_payroll_actions ppa
114 WHERE ppa.payroll_action_id = p_owner_payroll_action_id
115 AND ppa.time_period_id = ptp.time_period_id; */
116
117 --To take care of offset payrolls
118 SELECT ptp.end_date
119 INTO p_expiry_information
120 FROM per_time_periods ptp
121 ,pay_payroll_actions ppa
122 WHERE ppa.payroll_action_id = p_owner_payroll_action_id
123 AND ppa.payroll_id = ptp.payroll_id
124 AND ppa.effective_date between ptp.START_DATE and ptp.END_DATE;
125 --11887149 end;
126
127
128
129 hr_utility.set_location ('p_expiry_information:'||p_expiry_information,10);
130 end ASG_PROC_PTD_EC;
131
132
133 /*------------------------------ ASG_PROC_YTD_EC ----------------------------*/
134 /*
135 NAME
136 ASG_PROC_YTD_EC - Assignment Processing Year to Date expiry check.
137 DESCRIPTION
138 Expiry checking code for the following:
139 GB Assignment-level Process Year To Date Balance Dimension
140 NOTES
141 The associated dimension is expiry checked at payroll action level
142 */
143 procedure ASG_PROC_YTD_EC
144 (
145 p_owner_payroll_action_id in number, -- run created balance.
146 p_user_payroll_action_id in number, -- current run.
147 p_owner_assignment_action_id in number, -- assact created balance.
148 p_user_assignment_action_id in number, -- current assact..
149 p_owner_effective_date in date, -- eff date of balance.
150 p_user_effective_date in date, -- eff date of current run.
151 p_dimension_name in varchar2, -- balance dimension name.
152 p_expiry_information out nocopy number -- dimension expired flag.
153 ) is
154 l_tax_year_start date;
155 l_pay_year_start date;
156 l_user_payroll_id number;
157 l_owning_regular_payment_date date;
158 begin
159 -- if the payroll actions have not changed return stored result
160 if (p_owner_payroll_action_id = g_gb_owner_payroll_action_id)
161 and (p_user_payroll_action_id = g_gb_user_payroll_action_id) then
162 p_expiry_information := g_gb_expiry_information;
163 else -- [ check expiry
164 /* select the start of the financial year - if the owning action is
165 * before this or for a different payroll then its expired
166 */
167 Select to_date('06-04-' || to_char( fnd_number.canonical_to_number(
168 to_char( PTP.regular_payment_date,'YYYY'))
169 + decode(sign( PTP.regular_payment_date - to_date('06-04-'
170 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
171 -1,-1,0)),'DD-MM-YYYY') finyear, BACT.payroll_id
172 into l_tax_year_start, l_user_payroll_id
173 from per_time_periods PTP,
174 pay_payroll_actions BACT
175 where BACT.payroll_action_id = p_user_payroll_action_id
176 and PTP.time_period_id = BACT.time_period_id;
177 --
178 -- find the regular payment date for the owning action
179 --
180 select regular_payment_date
181 into l_owning_regular_payment_date
182 from pay_payroll_actions PACT,
183 per_time_periods PTP
184 where PACT.payroll_action_id = p_owner_payroll_action_id
185 and PTP.time_period_id = PACT.time_period_id;
186 --
187 if l_owning_regular_payment_date < l_tax_year_start then
188 p_expiry_information := 1;
189 g_gb_expiry_information := 1;
190 else
191 p_expiry_information := 0;
192 g_gb_expiry_information := 0;
193 end if;
194 g_gb_owner_payroll_action_id := p_owner_payroll_action_id;
195 g_gb_user_payroll_action_id := p_user_payroll_action_id;
196 end if; -- ] end check expiry
197 --
198
199 end ASG_PROC_YTD_EC;
200
201
202 --For 115.12
203
204 procedure ASG_PROC_YTD_EC
205 (
206 p_owner_payroll_action_id in number, -- run created balance.
207 p_user_payroll_action_id in number, -- current run.
208 p_owner_assignment_action_id in number, -- assact created balance.
209 p_user_assignment_action_id in number, -- current assact..
210 p_owner_effective_date in date, -- eff date of balance.
211 p_user_effective_date in date, -- eff date of current run.
212 p_dimension_name in varchar2, -- balance dimension name.
213 p_expiry_information out nocopy date -- dimension expired flag.
214 ) is
215 begin
216
217 hr_utility.set_location ('In Overloaded ASG_PROC_YTD',10);
218 hr_utility.set_location ('p_owner_payroll_action_id:'||p_owner_payroll_action_id,10);
219 hr_utility.set_location ('p_owner_effective_date:'||p_owner_effective_date,10);
220 hr_utility.set_location ('p_dimension_name:'||p_dimension_name,10);
221
222 SELECT to_date('05-04-' ||to_char(fnd_number.canonical_to_number(
223 to_char( PTP.regular_payment_date,'YYYY'))
224 + decode(sign( PTP.regular_payment_date -
225 to_date('06-04-'|| to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
226 -1,0,1)),'DD-MM-YYYY') finyear_end
227 INTO p_expiry_information
228 FROM per_time_periods PTP,
229 pay_payroll_actions BACT
230 WHERE BACT.payroll_action_id = p_owner_payroll_action_id
231 AND PTP.time_period_id = BACT.time_period_id;
232
233 hr_utility.set_location ('p_expiry_information:'||p_expiry_information,10);
234
235 end ASG_PROC_YTD_EC;
236
237
238
239 -- For 115.11
240
241 /*------------------------------ ASG_PEN_YTD_EC ----------------------------*/
242 /*
243 NAME
244 ASG_PEN_YTD_EC - Assignment Processing Pension Year to Date expiry check.
245 DESCRIPTION
246 Expiry checking code for the following:
247 GB Assignment-level Process Pension Year To Date Balance Dimension
248 NOTES
249 The associated dimension is expiry checked at payroll action level
250 */
251 procedure ASG_PEN_YTD_EC
252 (
253 p_owner_payroll_action_id in number, -- run created balance.
254 p_user_payroll_action_id in number, -- current run.
255 p_owner_assignment_action_id in number, -- assact created balance.
256 p_user_assignment_action_id in number, -- current assact..
257 p_owner_effective_date in date, -- eff date of balance.
258 p_user_effective_date in date, -- eff date of current run.
259 p_dimension_name in varchar2, -- balance dimension name.
260 p_expiry_information out nocopy number -- dimension expired flag.
261 ) is
262 l_tax_year_start date;
263 l_pay_year_start date;
264 l_user_payroll_id number;
265 l_owning_regular_payment_date date;
266 begin
267 -- if the payroll actions have not changed return stored result
268 if (p_owner_payroll_action_id = g_gb_owner_payroll_action_id)
269 and (p_user_payroll_action_id = g_gb_user_payroll_action_id) then
270 p_expiry_information := g_gb_expiry_information;
271 else -- [ check expiry
272 /* select the start of the financial year - if the owning action is
273 * before this or for a different payroll then its expired
274 */
275 Select to_date('01-04-' || to_char( fnd_number.canonical_to_number(
276 to_char( PTP.regular_payment_date,'YYYY'))
277 + decode(sign( PTP.regular_payment_date - to_date('01-04-'
278 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
279 -1,-1,0)),'DD-MM-YYYY') finyear, BACT.payroll_id
280 into l_tax_year_start, l_user_payroll_id
281 from per_time_periods PTP,
282 pay_payroll_actions BACT
283 where BACT.payroll_action_id = p_user_payroll_action_id
284 and PTP.time_period_id = BACT.time_period_id;
285 --
286 -- find the regular payment date for the owning action
287 --
288 select regular_payment_date
289 into l_owning_regular_payment_date
290 from pay_payroll_actions PACT,
291 per_time_periods PTP
292 where PACT.payroll_action_id = p_owner_payroll_action_id
293 and PTP.time_period_id = PACT.time_period_id;
294 --
295 if l_owning_regular_payment_date < l_tax_year_start then
296 p_expiry_information := 1;
297 g_gb_expiry_information := 1;
298 else
299 p_expiry_information := 0;
300 g_gb_expiry_information := 0;
301 end if;
302 g_gb_owner_payroll_action_id := p_owner_payroll_action_id;
303 g_gb_user_payroll_action_id := p_user_payroll_action_id;
304 end if; -- ] end check expiry
305 --
306
307 end ASG_PEN_YTD_EC;
308
309 -- For 115.12
310 -- Returns the Periods End date for the owner assignment_action_id
311
312 procedure ASG_PEN_YTD_EC
313 (
314 p_owner_payroll_action_id in number, -- run created balance.
315 p_user_payroll_action_id in number, -- current run.
316 p_owner_assignment_action_id in number, -- assact created balance.
317 p_user_assignment_action_id in number, -- current assact..
318 p_owner_effective_date in date, -- eff date of balance.
319 p_user_effective_date in date, -- eff date of current run.
320 p_dimension_name in varchar2, -- balance dimension name.
321 p_expiry_information out nocopy date -- dimension expired date.
322 ) is
323 begin
324
325 hr_utility.set_location ('In Overloaded ASG_PEN_YTD_EC',10);
326 hr_utility.set_location ('p_owner_payroll_action_id:'||p_owner_payroll_action_id,10);
327 hr_utility.set_location ('p_owner_effective_date:'||p_owner_effective_date,10);
328
329 SELECT to_date('31-03-' ||to_char(fnd_number.canonical_to_number(
330 to_char( PTP.regular_payment_date,'YYYY'))
331 + decode(sign( PTP.regular_payment_date -
332 to_date('01-04-'|| to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
333 -1,0,1)),'DD-MM-YYYY') finyear_end
334 INTO p_expiry_information
335 FROM per_time_periods PTP,
336 pay_payroll_actions BACT
337 WHERE BACT.payroll_action_id = p_owner_payroll_action_id
338 AND PTP.time_period_id = BACT.time_period_id;
339
340 hr_utility.set_location ('p_expiry_information:'||p_expiry_information,10);
341
342 end ASG_PEN_YTD_EC;
343
344
345 /*------------------------------ ASG_STAT_YTD_EC ----------------------------*/
346 /*
347 NAME
348 ASG_STAT_YTD_EC - Assignment Statutory Year to DAte expiry check
349 DESCRIPTION
350 Expiry checking code for the following:
351 GB Assignment-level Statutory Year to Date dimension
352 NOTES
353 The associated dimension is expiry checked at payroll action level
354 */
355 procedure ASG_STAT_YTD_EC
356 (
357 p_owner_payroll_action_id in number, -- run created balance.
358 p_user_payroll_action_id in number, -- current run.
359 p_owner_assignment_action_id in number, -- assact created balance.
360 p_user_assignment_action_id in number, -- current assact..
361 p_owner_effective_date in date, -- eff date of balance.
362 p_user_effective_date in date, -- eff date of current run.
363 p_dimension_name in varchar2, -- balance dimension name.
364 p_expiry_information out nocopy number -- dimension expired flag.
365 ) is
366 l_tax_year_start date;
367 begin
368 select to_date('06-04-' || to_char( fnd_number.canonical_to_number(
369 to_char( p_user_effective_date,'YYYY'))
370 + decode(sign( p_user_effective_date - to_date('06-04-'
371 || to_char( p_user_effective_date,'YYYY'),'DD-MM-YYYY')),
372 -1,-1,0)),'DD-MM-YYYY')
373 into l_tax_year_start
374 from pay_payroll_actions BACT
375 where BACT.payroll_action_id = p_user_payroll_action_id;
376 --
377 if p_owner_effective_date < l_tax_year_start then
378 p_expiry_information := 1;
379 else
380 p_expiry_information := 0;
381 end if;
382
383 end ASG_STAT_YTD_EC;
384
385 -- For 115.12
386
387 procedure ASG_STAT_YTD_EC
388 (
389 p_owner_payroll_action_id in number, -- run created balance.
390 p_user_payroll_action_id in number, -- current run.
391 p_owner_assignment_action_id in number, -- assact created balance.
392 p_user_assignment_action_id in number, -- current assact..
393 p_owner_effective_date in date, -- eff date of balance.
394 p_user_effective_date in date, -- eff date of current run.
395 p_dimension_name in varchar2, -- balance dimension name.
396 p_expiry_information out nocopy date -- dimension expired flag.
397 ) is
398 begin
399
400 hr_utility.set_location ('In Overloaded ASG_STAT_YTD_EC',10);
401 hr_utility.set_location ('Namish:p_owner_assignment_action_id:'||p_owner_assignment_action_id,10);
402 hr_utility.set_location ('Namish:p_owner_effective_date:'||p_owner_effective_date,10);
403
404 SELECT to_date('05-04-' ||to_char(fnd_number.canonical_to_number(
405 to_char( p_owner_effective_date,'YYYY'))
406 + decode(sign(p_owner_effective_date -
407 to_date('06-04-'|| to_char(p_owner_effective_date,'YYYY'),'DD-MM-YYYY')),
408 -1,0,1)),'DD-MM-YYYY') finyear_end
409 INTO p_expiry_information
410 FROM dual;
411
412 hr_utility.set_location ('p_expiry_information'||p_expiry_information,10);
413
414 end ASG_STAT_YTD_EC;
415
416 /*------------------------------ ASG_USER_EC ----------------------------*/
417 /*
418 NAME
419 ASG_USER_EC - Assignment user dimension expiry check.
420 DESCRIPTION
421 Expiry checking code for the following:
422 GB Assignment-level user dimension
423 NOTES
424 The associated dimension is expiry checked at payroll action level
425 */
426 procedure ASG_USER_EC
427 (
428 p_owner_payroll_action_id in number, -- run created balance.
429 p_user_payroll_action_id in number, -- current run.
430 p_owner_assignment_action_id in number, -- assact created balance.
431 p_user_assignment_action_id in number, -- current assact..
432 p_owner_effective_date in date, -- eff date of balance.
433 p_user_effective_date in date, -- eff date of current run.
434 p_dimension_name in varchar2, -- balance dimension name.
435 p_expiry_information out nocopy number -- dimension expired flag.
436 ) is
437 l_tax_year_start date;
438 l_pay_year_start date;
439 l_user_payroll_id number;
440
441 l_user_regular_payment_date date;
442 l_business_group_id number;
443 l_owning_regular_payment_date date;
444 l_span_start date;
445
446
447 begin
448
449 -- find the regular payment date for the using action
450 select regular_payment_date, BACT.business_group_id
451 into l_user_regular_payment_date, l_business_group_id
452 from pay_payroll_actions BACT,
453 per_time_periods PTP
454 where BACT.payroll_action_id = p_user_payroll_action_id
455 and PTP.time_period_id = BACT.time_period_id;
456
457 -- find the regular payment date for the owning action
458 select regular_payment_date
459 into l_owning_regular_payment_date
460 from pay_payroll_actions PACT,
461 per_time_periods PTP
462 where PACT.payroll_action_id = p_owner_payroll_action_id
463 and PTP.time_period_id = PACT.time_period_id;
464
465 -- find when the dimension last cleared down
466 l_span_start := hr_gbbal.dimension_reset_date( p_dimension_name,
467 l_user_regular_payment_date,
468 l_business_group_id);
469
470
471 -- is the user action since this date
472 --
473 --
474 if l_owning_regular_payment_date < l_span_start then
475 p_expiry_information := 1;
476 else
477 p_expiry_information := 0;
478 end if;
479 --
480
481 end ASG_USER_EC;
482
483 --For 115.12
484
485 procedure ASG_USER_EC
486 (
487 p_owner_payroll_action_id in number, -- run created balance.
488 p_user_payroll_action_id in number, -- current run.
489 p_owner_assignment_action_id in number, -- assact created balance.
490 p_user_assignment_action_id in number, -- current assact..
491 p_owner_effective_date in date, -- eff date of balance.
492 p_user_effective_date in date, -- eff date of current run.
493 p_dimension_name in varchar2, -- balance dimension name.
494 p_expiry_information out nocopy date -- dimension expired flag.
495 ) is
496
497
498 l_owning_regular_payment_date date;
499
500 l_start_dd_mon VARCHAR2(7);
501 l_frequency NUMBER;
502 l_start_reset NUMBER;
503
504 begin
505
506 hr_utility.set_location ('In Overloaded ASG_USER_EC',10);
507 hr_utility.set_location ('p_owner_payroll_action_id:'||p_owner_payroll_action_id,10);
508 hr_utility.set_location ('p_owner_effective_date:'||p_owner_effective_date,10);
509
510 -- find the regular payment date for the owning action
511 select regular_payment_date
512 into l_owning_regular_payment_date
513 from pay_payroll_actions PACT,
514 per_time_periods PTP
515 where PACT.payroll_action_id = p_owner_payroll_action_id
516 and PTP.time_period_id = PACT.time_period_id;
517
518 IF SUBSTR(p_dimension_name,31,8) = 'USER-REG' THEN
519
520 l_start_reset := INSTR(p_dimension_name,'RESET',30);
521
522 l_start_dd_mon := SUBSTR(p_dimension_name, l_start_reset - 6, 5);
523
524 l_frequency := FND_NUMBER.CANONICAL_TO_NUMBER(SUBSTR
525 (p_dimension_name, l_start_reset + 6, 2));
526
527 p_expiry_information := hr_gbbal.span_end(l_owning_regular_payment_date
528 ,l_frequency
529 ,l_start_dd_mon);
530 END IF;
531
532 hr_utility.set_location ('p_expiry_information:'||p_expiry_information,10);
533
534
535 end ASG_USER_EC;
536
537 /*------------------------------ ASG_PROC_TWO_YTD_EC ----------------------------*/
538 /*
539 NAME
540 ASG_PROC_TWO_YTD_EC - Assignment Processing Year to Date expiry check
541 for 2 yearly balance.
542 DESCRIPTION
543 Expiry checking code for the following:
544 GB Assignment level Last Two Years to Date
545 NOTES
546 The associated dimension is expiry checked at payroll action level
547 */
548 procedure ASG_PROC_TWO_YTD_EC
549 (
550 p_owner_payroll_action_id in number, -- run created balance.
551 p_user_payroll_action_id in number, -- current run.
552 p_owner_assignment_action_id in number, -- assact created balance.
553 p_user_assignment_action_id in number, -- current assact..
554 p_owner_effective_date in date, -- eff date of balance.
555 p_user_effective_date in date, -- eff date of current run.
556 p_dimension_name in varchar2, -- balance dimension name.
557 p_expiry_information out nocopy number -- dimension expired flag.
558 ) is
559 l_tax_year_start date;
560 l_regular_payment_date date;
561 l_pay_year_start date;
562 l_tax_yyyy_start number;
563 l_user_payroll_id number;
564 begin
565 --
566 -- select the start of the financial year - if the owning action is
567 -- before this or for a different payroll then its expired
568 --
569 -- If the tax year is even the the even dimension should expire
570 -- else if the tax year is odd then the odd dimension should expire.
571 -- Hence get the start of the tax year for this year or last year based
572 -- on the logic given below
573 --
574 -- skutteti added _PER_TD_ODD_TWO_YTD into the if clause below as the
575 -- same procedure is used for the expiry checking of the person level
576 -- latest balances on 11/mar/02.
577 --
578 if p_dimension_name IN ('_ASG_TD_ODD_TWO_YTD','_PER_TD_ODD_TWO_YTD') then
579 --
580 Select to_date('06-04-' || to_char( fnd_number.canonical_to_number(
581 to_char( PTP.regular_payment_date,'YYYY'))
582 + decode(sign( PTP.regular_payment_date - to_date('06-04-'
583 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
584 -1,-1,0) -
585 mod(
586 fnd_number.canonical_to_number(
587 to_char( PTP.regular_payment_date,'YYYY'))
588 + decode(sign( PTP.regular_payment_date - to_date('06-04-'
589 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
590 -1,-1,0),2)
591 ),'DD-MM-YYYY') finyear, BACT.payroll_id
592 into l_tax_year_start, l_user_payroll_id
593 from per_time_periods PTP,
594 pay_payroll_actions BACT
595 where BACT.payroll_action_id = p_user_payroll_action_id
596 and PTP.time_period_id = BACT.time_period_id;
597 --
598 elsif p_dimension_name in ('_ASG_TD_EVEN_TWO_YTD','_PER_TD_EVEN_TWO_YTD') then
599 --
600 Select to_date('06-04-' || to_char( fnd_number.canonical_to_number(
601 to_char( PTP.regular_payment_date,'YYYY'))
602 + decode(sign( PTP.regular_payment_date - to_date('06-04-'
603 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
604 -1,-1,0) -
605 mod(
606 fnd_number.canonical_to_number(
607 to_char( PTP.regular_payment_date,'YYYY'))
608 + decode(sign( PTP.regular_payment_date - to_date('06-04-'
609 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
610 -1,0,-1),2)
611 ),'DD-MM-YYYY') finyear, BACT.payroll_id
612 into l_tax_year_start, l_user_payroll_id
613 from per_time_periods PTP,
614 pay_payroll_actions BACT
615 where BACT.payroll_action_id = p_user_payroll_action_id
616 and PTP.time_period_id = BACT.time_period_id;
617
618 end if;
619 --
620 Select min(TP.start_date)
621 into l_pay_year_start
622 from per_time_periods TP
623 where TP.payroll_id = l_user_payroll_id
624 and TP.regular_payment_date >= l_tax_year_start;
625 --
626 --
627 if p_owner_effective_date < l_pay_year_start then
628 p_expiry_information := 1;
629 else
630 p_expiry_information := 0;
631 end if;
632 --
633 --
634 end ASG_PROC_TWO_YTD_EC;
635 --
636 --For 115.12
637
638 procedure ASG_PROC_TWO_YTD_EC
639 (
640 p_owner_payroll_action_id in number, -- run created balance.
641 p_user_payroll_action_id in number, -- current run.
642 p_owner_assignment_action_id in number, -- assact created balance.
643 p_user_assignment_action_id in number, -- current assact..
644 p_owner_effective_date in date, -- eff date of balance.
645 p_user_effective_date in date, -- eff date of current run.
646 p_dimension_name in varchar2, -- balance dimension name.
647 p_expiry_information out nocopy date -- dimension expired flag.
648 ) is
649
650 l_tax_year_start date;
651 l_adjust number;
652
653 begin
654
655 hr_utility.set_location ('IN ASG_PROC_TWO_YTD_EC',10);
656 hr_utility.set_location ('p_dimension_name'||p_dimension_name,10);
657 hr_utility.set_location ('p_owner_payroll_action_id'||p_owner_payroll_action_id,10);
658
659 Select to_date('06-04-' || to_char( fnd_number.canonical_to_number(
660 to_char( PTP.regular_payment_date,'YYYY'))
661 + decode(sign( PTP.regular_payment_date - to_date('06-04-'
662 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
663 -1,-1,0)),'DD-MM-YYYY') finyear
664 into l_tax_year_start
665 from per_time_periods PTP,
666 pay_payroll_actions BACT
667 where BACT.payroll_action_id = p_owner_payroll_action_id
668 and PTP.time_period_id = BACT.time_period_id;
669
670
671 IF p_dimension_name IN ('_ASG_TD_EVEN_TWO_YTD','_PER_TD_EVEN_TWO_YTD')
672 THEN
673 --
674 IF mod(to_number(to_char(l_tax_year_start,'yyyy')),2) = 1 THEN
675 -- The start of tax year is ODD, so add 2 to get the no action.
676 l_adjust := 2;
677 ELSE
678 -- The start of tax year is in an EVEN year, must add 1
679 l_adjust := 1;
680 END IF;
681
682 ELSIF p_dimension_name IN ('_ASG_TD_ODD_TWO_YTD','_PER_TD_ODD_TWO_YTD')
683 THEN
684
685 IF mod(to_number(to_char(l_tax_year_start,'yyyy')),2) = 0 THEN
686 -- The start of tax year is EVEN, so add 2 to get the no action.
687 l_adjust := 2;
688 ELSE
689 -- The start of tax year is in an ODD year, must add 1
690 l_adjust := 1;
691 END IF;
692 --
693 END IF;
694
695 p_expiry_information := to_date('06-04-' || to_char(fnd_number.canonical_to_number(
696 to_char(l_tax_year_start,'yyyy')) + l_adjust)
697 ,'DD-MM-YYYY') - 1;
698
699 hr_utility.set_location ('p_expiry_information:'||p_expiry_information,10);
700
701 end ASG_PROC_TWO_YTD_EC;
702
703
704
705 /*---------------------------- PER_TD_STAT_PTD_EC ----------------------------*/
706 /*
707 NAME
708 PER_TD_STAT_PTD_EC Person level TD Stat Expiry Checking
709 DESCRIPTION
710 Expiry checking code for the following:
711 GB PERSON level TD Statutory Period Dimension
712 NOTES
713 The associated dimension is expiry checked at ASSIGNMENT Action level
714 hence extra parameter.
715 */
716 procedure PER_TD_STAT_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_balance_context_values in varchar2, -- list of context values
726 p_expiry_information out nocopy number -- dimension expired flag.
727 ) is
728 l_span_start date;
729 l_owning_regular_payment_date date;
730 --
731 begin
732 --
733 -- find the regular payment date for the owning action
734 --
735 select regular_payment_date
736 into l_owning_regular_payment_date
737 from pay_payroll_actions PACT,
738 per_time_periods PTP
739 where PACT.payroll_action_id = p_owner_payroll_action_id
740 and PTP.time_period_id = PACT.time_period_id;
741 --
742 -- check that the beginning of the Person Level Period is before the
743 -- using action. This could be a different period size so call the
744 -- period span start with the using action id.
745 --
746 l_span_start :=
747 hr_gbnidir.PAYE_STAT_PERIOD_START_DATE(p_user_assignment_action_id);
748 --
749 IF l_owning_regular_payment_date < l_span_start then
750 p_expiry_information := 1;
751 ELSE
752 p_expiry_information := 0;
753 END IF;
754 --
755 end PER_TD_STAT_PTD_EC;
756 -----------------------------------------------------------------------
757 -- Procedure: PROC_YTD_START
758 -- Description: used by YTD Dimensions for Run Level Balances only.
759 -- This procedure accepts a date and assignment action and other
760 -- params, and returns the start date of that Tax Year, depending
761 -- on the regular payment date of the payroll action (similar to
762 -- above expiry checks).
763 -----------------------------------------------------------------------
764 --
765 procedure proc_ytd_start(p_period_type in varchar2 default null,
766 p_effective_date in date default null,
767 p_start_date out nocopy date,
768 p_start_date_code in varchar2 default null,
769 p_payroll_id in number,
770 p_bus_grp in number default null,
771 p_action_type in varchar2 default null,
772 p_asg_action in number)
773 is
774 l_tax_year_start date;
775 begin
776 select to_date('06-04-' || to_char( fnd_number.canonical_to_number(
777 to_char( PTP.regular_payment_date,'YYYY'))
778 + decode(sign( PTP.regular_payment_date - to_date('06-04-'
779 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
780 -1,-1,0)),'DD-MM-YYYY') finyear
781 into l_tax_year_start
782 from per_time_periods PTP,
783 pay_payroll_actions ppa,
784 pay_assignment_actions paa
785 where ppa.payroll_action_id = paa.payroll_action_id
786 and paa.assignment_action_id = p_asg_action
787 and ppa.payroll_id = p_payroll_id
788 and PTP.time_period_id = ppa.time_period_id;
789 --
790 p_start_date := l_tax_year_start;
791 --
792 end proc_ytd_start;
793 ----------------------------------------------------------------------
794 -- Procedure: PROC_ODD_YTD_START
795 -- Description: used by ODD_YTD Dimensions for Run Level Balances only.
796 -- This procedure accepts a date and assignment action and other
797 -- params, and returns the start date of the Last previous ODD
798 -- Tax Year, depending
799 -- on the regular payment date of the payroll action (similar to
800 -- above expiry checks). For 2 year balance dimensions.
801 -----------------------------------------------------------------------
802 --
803 procedure proc_odd_ytd_start(p_period_type in varchar2 default null,
804 p_effective_date in date default null,
805 p_start_date out nocopy date,
806 p_start_date_code in varchar2 default null,
807 p_payroll_id in number,
808 p_bus_grp in number default null,
809 p_action_type in varchar2 default null,
810 p_asg_action in number)
811 is
812 l_tax_year_start date;
813 l_odd_tax_year_start date;
814 l_odd_adjust number;
815 begin
816 select to_date('06-04-' || to_char( fnd_number.canonical_to_number(
817 to_char( PTP.regular_payment_date,'YYYY'))
818 + decode(sign( PTP.regular_payment_date - to_date('06-04-'
819 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
820 -1,-1,0)),'DD-MM-YYYY') finyear
821 into l_tax_year_start
822 from per_time_periods PTP,
823 pay_payroll_actions ppa,
824 pay_assignment_actions paa
825 where ppa.payroll_action_id = paa.payroll_action_id
826 and paa.assignment_action_id = p_asg_action
827 and ppa.payroll_id = p_payroll_id
828 and PTP.time_period_id = ppa.time_period_id;
829 --
830 IF mod(to_number(to_char(l_tax_year_start,'yyyy')),2) = 1 THEN
831 -- The start of tax year is ODD, no action.
832 l_odd_adjust := 0;
833 ELSE
834 -- The start of tax year is in an EVEN year, must subtract 1
835 l_odd_adjust := 1;
836 END IF;
837 --
838 l_odd_tax_year_start := to_date('06-04-' || to_char(fnd_number.canonical_to_number(
839 to_char(l_tax_year_start,'yyyy')) - l_odd_adjust)
840 ,'DD-MM-YYYY');
841 p_start_date := l_odd_tax_year_start;
842 --
843 end proc_odd_ytd_start;
844 ----------------------------------------------------------------------
845 -- Procedure: PROC_EVEN_YTD_START
846 -- Description: used by EVEN_YTD Dimensions for Run Level Balances only.
847 -- This procedure accepts a date and assignment action and other
848 -- params, and returns the start date of the Last previous EVEN
849 -- Tax Year, depending
850 -- on the regular payment date of the payroll action (similar to
851 -- above expiry checks). For 2 year balance dimensions.
852 -----------------------------------------------------------------------
853 --
854 procedure proc_even_ytd_start(p_period_type in varchar2 default null,
855 p_effective_date in date default null,
856 p_start_date out nocopy date,
857 p_start_date_code in varchar2 default null,
858 p_payroll_id in number,
859 p_bus_grp in number default null,
860 p_action_type in varchar2 default null,
861 p_asg_action in number)
862 is
863 l_tax_year_start date;
864 l_even_tax_year_start date;
865 l_even_adjust number;
866 begin
867 select to_date('06-04-' || to_char( fnd_number.canonical_to_number(
868 to_char( PTP.regular_payment_date,'YYYY'))
869 + decode(sign( PTP.regular_payment_date - to_date('06-04-'
870 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
871 -1,-1,0)),'DD-MM-YYYY') finyear
872 into l_tax_year_start
873 from per_time_periods PTP,
874 pay_payroll_actions ppa,
875 pay_assignment_actions paa
876 where ppa.payroll_action_id = paa.payroll_action_id
877 and paa.assignment_action_id = p_asg_action
878 and ppa.payroll_id = p_payroll_id
879 and PTP.time_period_id = ppa.time_period_id;
880 --
881 IF mod(to_number(to_char(l_tax_year_start,'yyyy')),2) = 0 THEN
882 -- The start of tax year is EVEN, no action.
883 l_even_adjust := 0;
884 ELSE
885 -- The start of tax year is in an ODD year, must subtract 1
886 l_even_adjust := 1;
887 END IF;
888 --
889 l_even_tax_year_start := to_date('06-04-' || to_char(fnd_number.canonical_to_number(
890 to_char(l_tax_year_start,'yyyy')) - l_even_adjust)
891 ,'DD-MM-YYYY');
892 p_start_date := l_even_tax_year_start;
893 --
894 end proc_even_ytd_start;
895
896 --For 115.11
897 -----------------------------------------------------------------------
898 -- Procedure: PROC_PEN_YTD_START
899 -- Description: used by YTD Dimensions for Run Level Balances only.
900 -- This procedure accepts a date and assignment action and other
901 -- params, and returns the start date of that Pension Year, depending
902 -- on the regular payment date of the payroll action (similar to
903 -- above expiry checks).
904 -----------------------------------------------------------------------
905 --
906 procedure proc_pen_ytd_start(p_period_type in varchar2 default null,
907 p_effective_date in date default null,
908 p_start_date out nocopy date,
909 p_start_date_code in varchar2 default null,
910 p_payroll_id in number,
911 p_bus_grp in number default null,
912 p_action_type in varchar2 default null,
913 p_asg_action in number)
914 is
915 l_tax_year_start date;
916 begin
917 select to_date('01-04-' || to_char( fnd_number.canonical_to_number(
918 to_char( PTP.regular_payment_date,'YYYY'))
919 + decode(sign( PTP.regular_payment_date - to_date('01-04-'
920 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
921 -1,-1,0)),'DD-MM-YYYY') finyear
922 into l_tax_year_start
923 from per_time_periods PTP,
924 pay_payroll_actions ppa,
925 pay_assignment_actions paa
926 where ppa.payroll_action_id = paa.payroll_action_id
927 and paa.assignment_action_id = p_asg_action
928 and ppa.payroll_id = p_payroll_id
929 and PTP.time_period_id = ppa.time_period_id;
930 --
931 p_start_date := l_tax_year_start;
932 --
933 end proc_pen_ytd_start;
934
935 -----------------------------------------------------------------------
936 end pygbexc;
[Home] [Help]