4 type t_fiscal_year is record(
1 package body pay_kr_dim_pkg as
2 /* $Header: pykrdim.pkb 120.2 2007/09/04 05:49:18 pparate noship $ */
3 --
5 payroll_action_id number,
6 fiscal_year_start_date date);
7 g_fiscal_year t_fiscal_year;
8 --
9 type t_bonus is record(
10 payroll_action_id number,
11 assignment_action_id number,
12 bonus_period_start_date date);
13 g_bonus t_bonus;
14
15 g_bonus_payroll_action_id number;
16 g_bonus_pay_period_start_date date;
17 g_bonus_assignment_action_id number;
18 g_bonus_bon_period_start_date date;
19
20 -------------------------------- next_period ---------------------------------------------------
21 --
22 -- NAME : next_period
23 -- DESCRIPTION : Given a date and a payroll action id, returns the date after the
24 -- end of the containing payroll action id's pay period.
25
26 FUNCTION next_period ( p_payroll_action_id in number,
27 p_given_date in date )
31 /* Get the date next to the end date of the given period,
28 RETURN date is
29 l_next_to_end_date date := NULL;
30 BEGIN
32 having the payroll action id */
33 SELECT PTP.end_date+1
34 INTO l_next_to_end_date
35 FROM per_time_periods ptp,
36 pay_payroll_actions pact
37 WHERE pact.payroll_action_id = p_payroll_action_id
38 AND pact.payroll_id = ptp.payroll_id
39 AND p_given_date between ptp.start_date and ptp.end_date;
40
41 return l_next_to_end_date;
42
43 END next_period;
44
45 ------------------------------- next_month -----------------------------------------------------
46 --
47 -- NAME : next_month
48 -- DESCRIPTION : Given a date returns the next month's start date.
49
50 FUNCTION next_month (p_given_date in date )
51 RETURN date is
52 BEGIN
53 /* Return the next month's start date */
54 RETURN trunc(add_months(p_given_date,1),'MM');
55 END next_month;
56
57 ------------------------------- next_quarter --------------------------------------------------
58 --
59 -- NAME : next_quarter
60 -- DESCRIPTION : Given a date returns the next quarter's start date.
61
62 FUNCTION next_quarter (p_given_date in date)
63 RETURN date is
64 BEGIN
65 /* Return the next quarter's start date */
66 RETURN trunc(add_months(p_given_date,3),'Q');
67 END next_quarter;
68
69 ------------------------------ next_year -----------------------------------------------------
70 --
71 -- NAME : next_year
72 -- DESCRIPTION : Given a date returns the next year's start date.
73
74 FUNCTION next_year (p_given_date in date)
75 RETURN date is
76 BEGIN
77 /* Return the next year's start date */
78 RETURN trunc(add_months(p_given_date,12),'Y');
79 END next_year;
80
81 --------------------------------------------------------------------------------
82 procedure ptd_ec(
83 p_owner_payroll_action_id in number, -- run created balance.
84 p_user_payroll_action_id in number, -- current run.
85 p_owner_assignment_action_id in number, -- assact created balance.
86 p_user_assignment_action_id in number, -- current assact.
87 p_owner_effective_date in date, -- eff date of balance.
88 p_user_effective_date in date, -- eff date of current run.
89 p_dimension_name in varchar2, -- balance dimension name.
90 p_expiry_information out NOCOPY number) -- dimension expired flag.
91 --------------------------------------------------------------------------------
92 is
93 cursor csr_expiry_information is
94 select 1
95 from pay_payroll_actions ppa2,
96 pay_payroll_actions ppa1
97 where ppa1.payroll_action_id = p_owner_payroll_action_id
98 and ppa2.payroll_action_id = p_user_payroll_action_id
99 and ppa2.time_period_id <> ppa1.time_period_id;
100 begin
101 open csr_expiry_information;
102 fetch csr_expiry_information into p_expiry_information;
103 if csr_expiry_information%NOTFOUND then
104 p_expiry_information := 0;
105 end if;
106 close csr_expiry_information;
107 end ptd_ec;
108
109 --------------------------------------------------------------------------------
110
111 procedure ptd_ec(
112 p_owner_payroll_action_id in number, -- run created balance.
113 p_user_payroll_action_id in number, -- current run.
114 p_owner_assignment_action_id in number, -- assact created balance.
115 p_user_assignment_action_id in number, -- current assact.
116 p_owner_effective_date in date, -- eff date of balance.
117 p_user_effective_date in date, -- eff date of current run.
118 p_dimension_name in varchar2, -- balance dimension name.
119 p_expiry_information out NOCOPY date) -- dimension expired date.
120 is
121 begin
122 --
123 p_expiry_information := next_period(p_owner_payroll_action_id, p_owner_effective_date)-1;
124 --
125 end ptd_ec;
126
127 --------------------------------------------------------------------------------
128 procedure mtd_ec(
129 p_owner_payroll_action_id in number, -- run created balance.
130 p_user_payroll_action_id in number, -- current run.
131 p_owner_assignment_action_id in number, -- assact created balance.
132 p_user_assignment_action_id in number, -- current assact.
133 p_owner_effective_date in date, -- eff date of balance.
134 p_user_effective_date in date, -- eff date of current run.
135 p_dimension_name in varchar2, -- balance dimension name.
136 p_expiry_information out NOCOPY number) -- dimension expired flag.
137 --------------------------------------------------------------------------------
138 is
139 begin
140 if trunc(p_owner_effective_date, 'MM') = trunc(p_user_effective_date, 'MM') then
141 p_expiry_information := 0;
142 else
143 p_expiry_information := 1;
144 end if;
145 end mtd_ec;
146
147 --------------------------------------------------------------------------------
148 procedure mtd_ec(
149 p_owner_payroll_action_id in number, -- run created balance.
150 p_user_payroll_action_id in number, -- current run.
151 p_owner_assignment_action_id in number, -- assact created balance.
157 --------------------------------------------------------------------------------
152 p_user_assignment_action_id in number, -- current assact.
153 p_owner_effective_date in date, -- eff date of balance.
154 p_user_effective_date in date, -- eff date of current run.
155 p_dimension_name in varchar2, -- balance dimension name.
156 p_expiry_information out NOCOPY date) -- dimension expired date.
158 is
159 begin
160 --
161 p_expiry_information := next_month(p_owner_effective_date)-1;
162 --
163 end mtd_ec;
164 --------------------------------------------------------------------------------
165 procedure qtd_ec(
166 p_owner_payroll_action_id in number, -- run created balance.
167 p_user_payroll_action_id in number, -- current run.
168 p_owner_assignment_action_id in number, -- assact created balance.
169 p_user_assignment_action_id in number, -- current assact.
170 p_owner_effective_date in date, -- eff date of balance.
171 p_user_effective_date in date, -- eff date of current run.
172 p_dimension_name in varchar2, -- balance dimension name.
173 p_expiry_information out NOCOPY number) -- dimension expired flag.
174 --------------------------------------------------------------------------------
175 is
176 begin
177 if trunc(p_owner_effective_date, 'Q') = trunc(p_user_effective_date, 'Q') then
178 p_expiry_information := 0;
179 else
180 p_expiry_information := 1;
181 end if;
182 end qtd_ec;
183
184 --------------------------------------------------------------------------------
185 procedure qtd_ec(
186 p_owner_payroll_action_id in number, -- run created balance.
187 p_user_payroll_action_id in number, -- current run.
188 p_owner_assignment_action_id in number, -- assact created balance.
189 p_user_assignment_action_id in number, -- current assact.
190 p_owner_effective_date in date, -- eff date of balance.
191 p_user_effective_date in date, -- eff date of current run.
192 p_dimension_name in varchar2, -- balance dimension name.
193 p_expiry_information out NOCOPY date) -- dimension expired date.
194 --------------------------------------------------------------------------------
195 is
196 begin
197 --
198 p_expiry_information := next_quarter(p_owner_effective_date)-1;
199 --
200 end qtd_ec;
201 --------------------------------------------------------------------------------
202 procedure ytd_ec(
203 p_owner_payroll_action_id in number, -- run created balance.
204 p_user_payroll_action_id in number, -- current run.
205 p_owner_assignment_action_id in number, -- assact created balance.
206 p_user_assignment_action_id in number, -- current assact.
207 p_owner_effective_date in date, -- eff date of balance.
208 p_user_effective_date in date, -- eff date of current run.
209 p_dimension_name in varchar2, -- balance dimension name.
210 p_expiry_information out NOCOPY number) -- dimension expired flag.
211 --------------------------------------------------------------------------------
212 is
213 begin
214 if trunc(p_owner_effective_date, 'YYYY') = trunc(p_user_effective_date, 'YYYY') then
215 p_expiry_information := 0;
216 else
217 p_expiry_information := 1;
218 end if;
219 end ytd_ec;
220
221 --------------------------------------------------------------------------------
222 procedure ytd_ec(
223 p_owner_payroll_action_id in number, -- run created balance.
224 p_user_payroll_action_id in number, -- current run.
225 p_owner_assignment_action_id in number, -- assact created balance.
226 p_user_assignment_action_id in number, -- current assact.
227 p_owner_effective_date in date, -- eff date of balance.
228 p_user_effective_date in date, -- eff date of current run.
229 p_dimension_name in varchar2, -- balance dimension name.
230 p_expiry_information out NOCOPY date) -- dimension expired date.
231 --------------------------------------------------------------------------------
232 is
233 begin
234 --
235 p_expiry_information := next_year(p_owner_effective_date)-1;
236 --
237 end ytd_ec;
238
239 --------------------------------------------------------------------------------
240 function fiscal_year_start_date(p_payroll_action_id in number) return date
241 --------------------------------------------------------------------------------
242 is
243 l_fiscal_year_start_date date;
244 cursor csr_fiscal_year_start_date is
245 select fnd_date.canonical_to_date(hoi.org_information11)
246 from hr_organization_information hoi,
247 pay_payroll_actions ppa
248 where ppa.payroll_action_id = p_payroll_action_id
249 and hoi.organization_id = ppa.business_group_id
250 and hoi.org_information_context = 'Business Group Information';
251 begin
252 if g_fiscal_year.payroll_action_id = p_payroll_action_id then
253 null;
254 else
255 open csr_fiscal_year_start_date;
259 end if;
256 fetch csr_fiscal_year_start_date into l_fiscal_year_start_date;
257 if csr_fiscal_year_start_date%NOTFOUND then
258 l_fiscal_year_start_date := NULL;
260 close csr_fiscal_year_start_date;
261 --
262 g_fiscal_year.payroll_action_id := p_payroll_action_id;
263 g_fiscal_year.fiscal_year_start_date := nvl(l_fiscal_year_start_date, fnd_date.canonical_to_date('2000/01/01'));
264 end if;
265 --
266 return g_fiscal_year.fiscal_year_start_date;
267 end fiscal_year_start_date;
268 --------------------------------------------------------------------------------
269 procedure fqtd_ec(
270 p_owner_payroll_action_id in number, -- run created balance.
271 p_user_payroll_action_id in number, -- current run.
272 p_owner_assignment_action_id in number, -- assact created balance.
273 p_user_assignment_action_id in number, -- current assact.
274 p_owner_effective_date in date, -- eff date of balance.
275 p_user_effective_date in date, -- eff date of current run.
276 p_dimension_name in varchar2, -- balance dimension name.
277 p_expiry_information out NOCOPY number) -- dimension expired flag.
278 --------------------------------------------------------------------------------
279 is
280 l_expiry_date date;
281 l_fiscal_year_start_date date;
282 begin
283 l_fiscal_year_start_date := fiscal_year_start_date(p_owner_payroll_action_id);
284 l_expiry_date := add_months(l_fiscal_year_start_date,
285 (floor(months_between(p_owner_effective_date, l_fiscal_year_start_date) / 3) + 1) * 3);
286 if p_user_effective_date >= l_expiry_date then
287 p_expiry_information := 1;
288 else
289 p_expiry_information := 0;
290 end if;
291 end fqtd_ec;
292
293 --------------------------------------------------------------------------------
294 procedure fqtd_ec(
295 p_owner_payroll_action_id in number, -- run created balance.
296 p_user_payroll_action_id in number, -- current run.
297 p_owner_assignment_action_id in number, -- assact created balance.
298 p_user_assignment_action_id in number, -- current assact.
299 p_owner_effective_date in date, -- eff date of balance.
300 p_user_effective_date in date, -- eff date of current run.
301 p_dimension_name in varchar2, -- balance dimension name.
302 p_expiry_information out NOCOPY date) -- dimension expired date.
303 --------------------------------------------------------------------------------
304 is
305 l_expiry_date date;
306 l_fiscal_year_start_date date;
307 begin
308 --
309 l_fiscal_year_start_date := fiscal_year_start_date(p_owner_payroll_action_id);
310 p_expiry_information := add_months(l_fiscal_year_start_date,
311 (floor(months_between(p_owner_effective_date, l_fiscal_year_start_date) / 3) + 1) * 3) -1;
312 --
313 end fqtd_ec;
314
315 /* Bug 6263815 - Adding expiry checking code for _itd dimension */
316 --------------------------------------------------------------------------------
317 procedure itd_ec(
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 number) -- dimension expired flag.
326 --------------------------------------------------------------------------------
327 is
328 begin
329 p_expiry_information := 0;
330 end itd_ec;
331
332 --------------------------------------------------------------------------------
333 procedure itd_ec(
334 p_owner_payroll_action_id in number, -- run created balance.
335 p_user_payroll_action_id in number, -- current run.
336 p_owner_assignment_action_id in number, -- assact created balance.
337 p_user_assignment_action_id in number, -- current assact.
338 p_owner_effective_date in date, -- eff date of balance.
339 p_user_effective_date in date, -- eff date of current run.
340 p_dimension_name in varchar2, -- balance dimension name.
341 p_expiry_information out NOCOPY date) -- dimension expired date.
342 --------------------------------------------------------------------------------
343 is
344 begin
345 p_expiry_information := fnd_date.canonical_to_date('4712/12/31');
346 end itd_ec;
347
348 --------------------------------------------------------------------------------
349 procedure fytd_ec(
350 p_owner_payroll_action_id in number, -- run created balance.
351 p_user_payroll_action_id in number, -- current run.
352 p_owner_assignment_action_id in number, -- assact created balance.
353 p_user_assignment_action_id in number, -- current assact.
354 p_owner_effective_date in date, -- eff date of balance.
355 p_user_effective_date in date, -- eff date of current run.
356 p_dimension_name in varchar2, -- balance dimension name.
357 p_expiry_information out NOCOPY number) -- dimension expired flag.
358 --------------------------------------------------------------------------------
359 is
360 l_expiry_date date;
361 l_fiscal_year_start_date date;
362 begin
363 l_fiscal_year_start_date := fiscal_year_start_date(p_owner_payroll_action_id);
364 l_expiry_date := add_months(l_fiscal_year_start_date,
365 (floor(months_between(p_owner_effective_date, l_fiscal_year_start_date) / 12) + 1) * 12);
366 if p_user_effective_date >= l_expiry_date then
367 p_expiry_information := 1;
368 else
369 p_expiry_information := 0;
370 end if;
371 end fytd_ec;
372
373 --------------------------------------------------------------------------------
374 procedure fytd_ec(
375 p_owner_payroll_action_id in number, -- run created balance.
376 p_user_payroll_action_id in number, -- current run.
377 p_owner_assignment_action_id in number, -- assact created balance.
378 p_user_assignment_action_id in number, -- current assact.
379 p_owner_effective_date in date, -- eff date of balance.
380 p_user_effective_date in date, -- eff date of current run.
381 p_dimension_name in varchar2, -- balance dimension name.
382 p_expiry_information out NOCOPY date) -- dimension expired date.
383 --------------------------------------------------------------------------------
384 is
385 l_fiscal_year_start_date date;
386 begin
387 --
388 l_fiscal_year_start_date := fiscal_year_start_date(p_owner_payroll_action_id);
389 p_expiry_information := add_months(l_fiscal_year_start_date,
390 (floor(months_between(p_owner_effective_date, l_fiscal_year_start_date) / 12) + 1) * 12) -1;
391 --
392 end fytd_ec;
393 --------------------------------------------------------------------------------
394 procedure hdtd_ec(
395 p_owner_payroll_action_id in number, -- run created balance.
396 p_user_payroll_action_id in number, -- current run.
397 p_owner_assignment_action_id in number, -- assact created balance.
398 p_user_assignment_action_id in number, -- current assact.
399 p_owner_effective_date in date, -- eff date of balance.
400 p_user_effective_date in date, -- eff date of current run.
401 p_dimension_name in varchar2, -- balance dimension name.
402 p_balance_context_values in varchar2, -- list of context value
403 p_expiry_information out NOCOPY number) -- dimension expired flag.
404 --------------------------------------------------------------------------------
405 is
406 l_hire_date date;
407 l_expiry_date date;
408 cursor csr_hire_date is
409 select pps.date_start
410 from per_periods_of_service pps,
411 per_assignments_f pa,
412 pay_assignment_actions paa
413 where paa.assignment_action_id = p_owner_assignment_action_id
414 and pa.assignment_id = paa.assignment_id
415 and p_owner_effective_date
416 between pa.effective_start_date and pa.effective_end_date
417 and pps.period_of_service_id = pa.period_of_service_id;
418 begin
419 open csr_hire_date;
420 fetch csr_hire_date into l_hire_date;
421 close csr_hire_date;
422 --
423 l_expiry_date := add_months(l_hire_date,
424 (floor(months_between(p_owner_effective_date, l_hire_date) / 12) + 1) * 12);
425 if p_user_effective_date >= l_expiry_date then
426 p_expiry_information := 1;
427 else
428 p_expiry_information := 0;
429 end if;
430 end hdtd_ec;
431 ------------------------------------------------------------------------
432 PROCEDURE hdtd_start_date(p_effective_date IN DATE ,
433 p_start_date OUT NOCOPY DATE,
434 p_payroll_id IN NUMBER DEFAULT NULL,
435 p_bus_grp IN NUMBER DEFAULT NULL,
436 p_asg_action IN NUMBER DEFAULT NULL)
437 --------------------------------------------------------------------------
438 is
439 cursor csr_start_date is
440 select ppos.date_start
441 from per_periods_of_service ppos,
442 pay_assignment_actions pac,
443 per_assignments_f pa
444 Where pac.assignment_action_id = p_asg_action
445 and pac.assignment_id = pa.assignment_id
446 and ppos.period_of_service_id = pa.period_of_service_id
447 and p_effective_date
448 between pa.effective_start_date and pa.effective_end_date;
449 BEGIN
450 open csr_start_date;
451 fetch csr_start_date into p_start_date;
452 close csr_start_date;
453 END hdtd_start_date;
454
455 --------------------------------------------------------------------------------
456
457 --------------------------------------------------------------------------------
458 procedure hdtd_ec(
459 p_owner_payroll_action_id in number, -- run created balance.
460 p_user_payroll_action_id in number, -- current run.
461 p_owner_assignment_action_id in number, -- assact created balance.
462 p_user_assignment_action_id in number, -- current assact.
463 p_owner_effective_date in date, -- eff date of balance.
464 p_user_effective_date in date, -- eff date of current run.
465 p_dimension_name in varchar2, -- balance dimension name.
466 p_balance_context_values in varchar2, -- list of context value
467 p_expiry_information out NOCOPY date) -- dimension expired date.
468 --------------------------------------------------------------------------------
469 is
470 l_hire_date date;
471 cursor csr_hire_date is
472 select pps.date_start
473 from per_periods_of_service pps,
477 and pa.assignment_id = paa.assignment_id
474 per_assignments_f pa,
475 pay_assignment_actions paa
476 where paa.assignment_action_id = p_owner_assignment_action_id
478 and p_owner_effective_date
479 between pa.effective_start_date and pa.effective_end_date
480 and pps.period_of_service_id = pa.period_of_service_id;
481 begin
482 open csr_hire_date;
483 fetch csr_hire_date into l_hire_date;
484 close csr_hire_date;
485 --
486 p_expiry_information := add_months(l_hire_date,
487 (floor(months_between(p_owner_effective_date, l_hire_date) / 12) + 1) * 12) -1;
488 --
489 end hdtd_ec;
490 --------------------------------------------------------------------------------
491 function run_type_name(p_payroll_action_id IN NUMBER
492 ,p_assact_id IN NUMBER DEFAULT NULL) return VARCHAR2
493 --------------------------------------------------------------------------------
494 is
495 l_run_type_name pay_run_types_f.run_type_name%TYPE;
496 cursor csr_run_type_name is
497 select prt.run_type_name
498 from pay_run_types_f prt,
499 pay_payroll_actions ppa
500 where ppa.payroll_action_id = p_payroll_action_id
501 and prt.run_type_id = ppa.run_type_id
502 and ppa.effective_date
503 between prt.effective_start_date and prt.effective_end_date;
504
505 cursor csr_run_type_name_assact is
506 select prt.run_type_name
507 from pay_run_types_f prt,
508 pay_assignment_actions paa,
509 pay_payroll_actions ppa
510 where paa.assignment_action_id = p_assact_id
511 and ppa.payroll_action_id = p_payroll_action_id
512 and prt.run_type_id = paa.run_type_id
513 and ppa.effective_date
514 between prt.effective_start_date and prt.effective_end_date;
515
516 begin
517 open csr_run_type_name;
518 fetch csr_run_type_name into l_run_type_name;
519 if csr_run_type_name%NOTFOUND then
520 l_run_type_name := NULL;
521 end if;
522 close csr_run_type_name;
523 --
524 if l_run_type_name IS NULL then
525 open csr_run_type_name_assact;
526 fetch csr_run_type_name_assact into l_run_type_name;
527 if csr_run_type_name_assact%NOTFOUND then
528 l_run_type_name := NULL;
529 end if;
530 close csr_run_type_name_assact;
531 end if;
532 --
533 return l_run_type_name;
534 end run_type_name;
535 --------------------------------------------------------------------------------
536 /*
537 procedure gen_fc(
538 p_payroll_action_id in number,
539 p_assignment_action_id in number,
540 p_assignment_id in number,
541 p_effective_date in date,
542 p_dimension_name in varchar2,
543 p_balance_contexts in varchar2,
544 p_feed_flag in out NOCOPY number)
545 --------------------------------------------------------------------------------
546 IS
547 l_run_type_name pay_run_types_f.run_type_name%TYPE;
548 BEGIN
549 l_run_type_name := run_type_name(p_payroll_action_id,p_assignment_action_id);
550 --
551 -- Exclude Separation Payment
552 --
553 if l_run_type_name like 'SEP%' then
554 p_feed_flag := 0;
555 else
556 p_feed_flag := 1;
557 end if;
558 end gen_fc;
559 */
560 --------------------------------------------------------------------------------
561 procedure bptd_fc(
562 p_payroll_action_id in number,
563 p_assignment_action_id in number,
564 p_assignment_id in number,
565 p_effective_date in date,
566 p_dimension_name in varchar2,
567 p_balance_contexts in varchar2,
568 p_feed_flag in out NOCOPY number)
569 --------------------------------------------------------------------------------
570 IS
571 l_run_type_name pay_run_types_f.run_type_name%TYPE;
572 BEGIN
573 l_run_type_name := run_type_name(p_payroll_action_id,p_assignment_action_id);
574 --
575 -- Exclude Separation Payment
576 --
577 if l_run_type_name like 'SEP%' then
578 p_feed_flag := 0;
579 else
580 p_feed_flag := 1;
581 end if;
582 end bptd_fc;
583 --------------------------------------------------------------------------------
584 PROCEDURE mth_fc(
585 p_payroll_action_id in number,
586 p_assignment_action_id in number,
587 p_assignment_id in number,
588 p_effective_date in date,
589 p_dimension_name in varchar2,
590 p_balance_contexts in varchar2,
591 p_feed_flag in out NOCOPY number)
592 --------------------------------------------------------------------------------
593 IS
594 l_run_type_name pay_run_types_f.run_type_name%TYPE;
595 BEGIN
596 l_run_type_name := run_type_name(p_payroll_action_id,p_assignment_action_id);
597 --
598 -- If the run_type is not specified, it is regarded as Monthly Payroll.
599 -- e.g. Balance Adjustment etc.
600 --
601 -- if nvl(l_run_type_name, 'MTH') = 'MTH' then
602 if l_run_type_name = 'MTH' then
603 p_feed_flag := 1;
604 else
605 p_feed_flag := 0;
606 end if;
607 END mth_fc;
608 --------------------------------------------------------------------------------
609 PROCEDURE bon_fc(
610 p_payroll_action_id in number,
611 p_assignment_action_id in number,
612 p_assignment_id in number,
613 p_effective_date in date,
617 --------------------------------------------------------------------------------
614 p_dimension_name in varchar2,
615 p_balance_contexts in varchar2,
616 p_feed_flag in out NOCOPY number)
618 IS
619 l_run_type_name pay_run_types_f.run_type_name%TYPE;
620 BEGIN
621 l_run_type_name := run_type_name(p_payroll_action_id,p_assignment_action_id);
622 --
623 if l_run_type_name like 'BON\_%' escape '\' then
624 p_feed_flag := 1;
625 else
626 p_feed_flag := 0;
627 end if;
628 END bon_fc;
629 --------------------------------------------------------------------------------
630 PROCEDURE sep_fc(
631 p_payroll_action_id in number,
632 p_assignment_action_id in number,
633 p_assignment_id in number,
634 p_effective_date in date,
635 p_dimension_name in varchar2,
636 p_balance_contexts in varchar2,
637 p_feed_flag in out NOCOPY number)
638 --------------------------------------------------------------------------------
639 IS
640 l_run_type_name pay_run_types_f.run_type_name%TYPE;
641 BEGIN
642 l_run_type_name := run_type_name(p_payroll_action_id,p_assignment_action_id);
643 --
644 if l_run_type_name in ('SEP','SEP_I') then
645 p_feed_flag := 1;
646 else
647 p_feed_flag := 0;
648 end if;
649 END sep_fc;
650 --------------------------------------------------------------------------------
651 function bonus_period_start_date(
652 p_payroll_id in number,
653 p_effective_date in date,
654 p_assignment_set_id in number,
655 p_run_type_id in number) return date
656 --------------------------------------------------------------------------------
657 is
658 l_soy constant date := trunc(p_effective_date, 'YYYY');
659 l_run_type_name pay_run_types_f.run_type_name%TYPE;
660 type t_run_type_name_tbl is table of pay_run_types_f.run_type_name%TYPE index by binary_integer;
661 type t_assignment_set_id_tbl is table of number index by binary_integer;
662 type t_start_date_tbl is table of date index by binary_integer;
663 l_run_type_name_tbl t_run_type_name_tbl;
664 l_assignment_set_id_tbl t_assignment_set_id_tbl;
665 l_start_date_tbl t_start_date_tbl;
666 l_bonus_period_start_date date;
667 --
668 cursor csr_run_type_name is
669 select run_type_name
670 from pay_run_types_f
671 where run_type_id = p_run_type_id
672 and p_effective_date
673 between effective_start_date and effective_end_date;
674 --
675 cursor csr_pact is
676 select
677 prt.run_type_name,
678 ppa.assignment_set_id,
679 ptp.start_date
680 from pay_run_types_f prt,
681 per_time_periods ptp,
682 pay_payroll_actions ppa
683 where ppa.payroll_id = p_payroll_id
684 and ppa.action_type = 'R'
685 and ppa.effective_date
686 between l_soy and p_effective_date
687 and ptp.time_period_id = ppa.time_period_id
688 and p_effective_date
689 not between ptp.start_date and ptp.end_date
690 and prt.run_type_id = ppa.run_type_id
691 and ppa.effective_date
692 between prt.effective_start_date and prt.effective_end_date
693 and (
694 prt.run_type_name = 'MTH'
695 or
696 /* Bonuses within current payroll period are out of scope. */
697 (
698 prt.run_type_name like 'BON\_%' escape '\'
699 and p_effective_date
700 not between ptp.start_date and ptp.end_date
701 )
702 )
703 order by ppa.effective_date desc, ppa.action_sequence desc;
704 --
705 cursor csr_start_date is
706 select start_date
707 from per_time_periods
708 where payroll_id = p_payroll_id
709 and p_effective_date
710 between start_date and end_date;
711 begin
712 --
713 -- Derive run_type_name
714 --
715 open csr_run_type_name;
716 fetch csr_run_type_name into l_run_type_name;
717 if csr_run_type_name%NOTFOUND then
718 raise NO_DATA_FOUND;
719 end if;
720 close csr_run_type_name;
721 --
722 -- Derive all payroll action with the same payroll as current payroll
723 -- whose effective_date is between start date of this calendar year
724 -- and effective_date in descending order.
725 --
726 open csr_pact;
727 fetch csr_pact bulk collect into l_run_type_name_tbl, l_assignment_set_id_tbl, l_start_date_tbl;
728 close csr_pact;
729 --
730 -- Get the first monthly payroll action with the same payroll_id within bonus period.
731 -- Bonus period is from the latest bonus with the same run_type category as current bonus
732 -- within the same calendar year.
733 --
734 for i in 1..l_run_type_name_tbl.count loop
735 if (l_run_type_name_tbl(i) = l_run_type_name)
736 or (l_run_type_name_tbl(i) in ('BON_RWOP', 'BON_I') and l_run_type_name in ('BON_RWOP', 'BON_I')) then
737 if (l_assignment_set_id_tbl(i) is null) or (l_assignment_set_id_tbl(i) = p_assignment_set_id) then
738 exit;
739 end if;
740 end if;
741 --
742 if l_run_type_name_tbl(i) = 'MTH' then
743 l_bonus_period_start_date := l_start_date_tbl(i);
744 end if;
745 end loop;
746 --
747 -- If no monthly payroll action exists within bonus period,
748 -- set start date of current payroll period as bonus period start date.
749 --
750 if l_bonus_period_start_date is null then
751 open csr_start_date;
755 --
752 fetch csr_start_date into l_bonus_period_start_date;
753 close csr_start_date;
754 end if;
756 return greatest(l_bonus_period_start_date, l_soy);
757 end bonus_period_start_date;
758 --------------------------------------------------------------------------------
759 function bonus_period_start_date(
760 p_assignment_action_id in number,
761 p_payroll_action_id in number) return date
762 --------------------------------------------------------------------------------
763 is
764 l_bonus_period_start_date date;
765 --
766 cursor csr_ovrd_ass_start_date(p_assignment_action_id number) is
767 select
768 fnd_date.canonical_to_date(prrv.result_value)
769 from pay_run_result_values prrv,
770 pay_run_results prr,
771 pay_payroll_actions ppa,
772 pay_assignment_actions paa,
773 pay_element_types_f pet,
774 pay_input_values_f piv
775 where
776 paa.assignment_action_id = p_assignment_action_id
777 and ppa.payroll_action_id = paa.payroll_action_id
778 and prr.assignment_action_id = paa.assignment_action_id
779 and prr.element_type_id = pet.element_type_id
780 and pet.element_name = 'OVRD_BONUS_PERIOD'
781 and pet.legislation_code = 'KR'
782 and ppa.effective_date between pet.effective_start_date and pet.effective_end_date
783 and prrv.run_result_id = prr.run_result_id
784 and prrv.input_value_id = piv.input_value_id
785 and piv.name = 'BONUS_PERIOD_START_DATE'
786 and piv.legislation_code = 'KR'
787 and ppa.effective_date between piv.effective_start_date and piv.effective_end_date;
788 --
789 cursor csr_bonus_period_start_date is
790 select
791 min(greatest(ptp.start_date, trunc(ppa1.effective_date, 'YYYY')))
792 from per_time_periods ptp,
793 pay_run_types_f prt2,
794 pay_payroll_actions ppa2,
795 pay_assignment_actions paa2,
796 pay_run_types_f prt1,
797 pay_payroll_actions ppa1,
798 pay_assignment_actions paa1
799 where paa1.assignment_action_id = p_assignment_action_id
800 and ppa1.payroll_action_id = paa1.payroll_action_id
801 and prt1.run_type_id = paa1.run_type_id
802 and ppa1.effective_date
803 between prt1.effective_start_date and prt1.effective_end_date
804 and paa2.assignment_id = nvl(paa1.assignment_id, prt1.run_type_id)
805 /* Including current bonus assignment_action_id */
806 and paa2.action_sequence <= paa1.action_sequence
807 and ppa2.payroll_action_id = paa2.payroll_action_id
808 and ppa2.action_type in ('R', 'Q')
809 and ppa2.effective_date >= trunc(ppa1.effective_date, 'YYYY')
810 and prt2.run_type_id = paa2.run_type_id
811 and ppa2.effective_date
812 between prt2.effective_start_date and prt2.effective_end_date
813 /* Including current bonus assignment_action_id */
814 and decode(paa2.assignment_action_id, paa1.assignment_action_id, 'MTH', prt2.run_type_name) = 'MTH'
815 and ptp.time_period_id = nvl(ppa2.time_period_id, prt2.run_type_id)
816 and paa2.action_sequence > (
817 /* Latest bonus with same run_type category as corrent bonus. */
818 select nvl(max(paa3.action_sequence), 0)
819 from pay_run_types_f prt3,
820 pay_payroll_actions ppa3,
821 pay_assignment_actions paa3
822 where paa3.assignment_id = paa1.assignment_id
823 and paa3.action_sequence < paa1.action_sequence
824 and ppa3.payroll_action_id = paa3.payroll_action_id
825 and ppa3.action_type in ('R', 'Q')
826 and ppa3.effective_date >= trunc(ppa1.effective_date, 'YYYY')
827 /* Bonuses within current payroll period are out of scope. */
828 and ppa3.time_period_id <> ppa1.time_period_id
829 and prt3.run_type_id = paa3.run_type_id
830 and ppa3.effective_date
831 between prt3.effective_start_date and prt3.effective_end_date
832 and decode(prt3.run_type_name, 'BON_RWOP', -1, 'BON_I', -1, prt3.run_type_id) = decode(prt1.run_type_name, 'BON_RWOP', -1, 'BON_I', -1, prt1.run_type_id)
833 );
834 begin
835 --
836 -- Get the bonus period start date set at payroll level if not available in cache.
837 --
838 if g_bonus_payroll_action_id = p_payroll_action_id then
839 null;
840 else
841 g_bonus_pay_period_start_date := fnd_date.canonical_to_date(
842 pay_kr_ff_functions_pkg.get_legislative_parameter(
843 p_payroll_action_id,
844 'BONUS_PERIOD_START_DATE',
845 null));
846 g_bonus_payroll_action_id := p_payroll_action_id;
847 end if;
848
849
850 if g_bonus_assignment_action_id = p_assignment_action_id then
851 null;
852 else
853 --
854 -- 1. Get the Bonus Period Start date from the input value 'Bonus Period Start Date' of element
855 -- 'Overriding Bonus calculation'
856 --
857 open csr_ovrd_ass_start_date(p_assignment_action_id);
858 fetch csr_ovrd_ass_start_date into l_bonus_period_start_date;
859 close csr_ovrd_ass_start_date;
860
861 if l_bonus_period_start_date is not null then
862 g_bonus_assignment_action_id := p_assignment_action_id;
863 g_bonus_bon_period_start_date := l_bonus_period_start_date;
864 end if;
865 --
866 -- 2. If Bonus Period Start date is not set by element 'Overriding Bonus calculation' then
867 -- get Bonus Period Start date from the legislative parameter BONUS_PERIOD_START_DATE
868 --
869 if (l_bonus_period_start_date is null and
870 g_bonus_pay_period_start_date is not null and
871 p_payroll_action_id = g_bonus_payroll_action_id ) then
872 g_bonus_assignment_action_id := p_assignment_action_id;
873 g_bonus_bon_period_start_date := g_bonus_pay_period_start_date;
874 end if;
875 --
876 -- 3. If legislative parameter BONUS_PERIOD_START_DATE is not set,
877 -- derive this parameter from PAY_ASSIGNMENT_ACTIONS table
878 -- and stores it in cache.
879 --
880 if (l_bonus_period_start_date is null and
881 g_bonus_pay_period_start_date is null) then
882 open csr_bonus_period_start_date;
883 fetch csr_bonus_period_start_date into l_bonus_period_start_date;
884 close csr_bonus_period_start_date;
885 --
886 g_bonus_assignment_action_id := p_assignment_action_id;
887 g_bonus_bon_period_start_date := l_bonus_period_start_date;
888 end if;
889 --
890 end if;
891 --
892 return g_bonus_bon_period_start_date;
893 end bonus_period_start_date;
894 --
895 Function inc_or_exc_assact (p_bal_asact in pay_assignment_actions.assignment_action_id%type
896 ,p_asact in pay_assignment_actions.assignment_action_id%type
897 ,p_bal_asact_rtype_name in pay_run_types_f.run_type_name%type
898 ,p_asact_rtype_name in pay_run_types_f.run_type_name%type ) return varchar2 is
899
900
901 /* This function is used to arrive at
902 'Total Taxable Earnings Subj to Regular Tax' for bonus payroll runs .
903 If current run_type_name in ('BON_RWP' , 'BON_RWOP' ) Then .
904 Total Taxable Earnings Subj to Regular Tax =
905 Current Bonus + ( MTH + BON_I +BON_RWP + BON_RWOP
906 + BON_O + BON_ALR within Bonus Period )
907
908 If current run_type_name in ('BON_I') Then
909 Total Taxable Earnings Subj to Regular Tax =
910 Current Bonus + ( MTH + BON_I +BON_RWP + BON_O +
911 BON_ALR within Bonus Period )
912
913 -- Common business practices do not allow running 'regular bonus without period'
914 -- and 'irregular bonus' within the same period
915
916 If current run_type_name in ('BON_O','BON_ALR') Then
917 Total Taxable Earnings Subj to Regular Tax =
918 Current Bonus + ( MTH + within Bonus Period )
919 */
920
921
922 l_include constant Varchar2(10) :='INCLUDE';
923 l_exclude constant Varchar2(10) :='EXCLUDE';
924
925
926 begin
927 if p_bal_asact = p_asact then
928 return l_include ;
929 else
930 if (p_bal_asact_rtype_name in ('BON_RWOP','BON_RWP' ) ) then
931 if substr(p_asact_rtype_name ,1,3 ) in ('MTH','BON') then
932 return l_include;
933 else
934 return l_exclude;
935 end if;
936 elsif p_bal_asact_rtype_name='BON_I' then
937 if p_asact_rtype_name in ('MTH','BON_I','BON_RWP','BON_ALR') then
938 return l_include;
939 else
940 return l_exclude;
941 end if;
942 elsif p_bal_asact_rtype_name in ('BON_O','BON_ALR') then
943 if p_asact_rtype_name = 'MTH' then
944 return l_include;
945 else
946 return l_exclude;
947 end if;
948 else
949 return l_exclude ;
950 end if ;
951 end if;
952
953 -- in other cases
954
955 return l_exclude ;
956
957 end inc_or_exc_assact;
958
959
960 --
961 end pay_kr_dim_pkg;
[Home] [Help]