1 PACKAGE BODY pay_in_exc AS
2 /* $Header: pyinexc.pkb 120.4.12020000.4 2013/02/07 12:48:06 pthummal ship $ */
3 g_package CONSTANT VARCHAR2(100) := 'pay_in_exc.';
4 g_debug BOOLEAN;
5
6 --------------------------------------------------------------------------
7 -- --
8 -- Name : NEXT_PERIOD --
12 -- date of the day after the end of the --
9 -- Type : FUNCTION --
10 -- Access : Private --
11 -- Description : Given a date and a payroll action id, returns the --
13 -- containing pay period. --
14 -- --
15 -- Parameters : --
16 -- IN : p_pactid NUMBER --
17 -- p_date DATE --
18 -- RETURN : date --
19 -- --
20 -- Change History : --
21 --------------------------------------------------------------------------
22 -- Rev# Date Userid Description --
23 --------------------------------------------------------------------------
24 -- 1.0 16-JUL-04 lnagaraj Created this function --
25 --------------------------------------------------------------------------
26 FUNCTION next_period ( p_pactid IN NUMBER
27 , p_date IN DATE
28 )
29 RETURN DATE
30 IS
31
32 l_return_val DATE := NULL;
33
34 CURSOR cur_end_date ( p_pactid NUMBER
35 , p_date DATE
36 ) IS
37 SELECT TP.end_date + 1
38 FROM per_time_periods TP
39 , pay_payroll_actions PACT
40 WHERE PACT.payroll_action_id = p_pactid
41 AND PACT.payroll_id = TP.payroll_id
42 AND p_date BETWEEN TP.start_date AND TP.end_date;
43
44 BEGIN
45
46
47 OPEN cur_end_date ( p_pactid
48 , p_date
49 );
50 FETCH cur_end_date INTO l_return_val;
51 CLOSE cur_end_date;
52
53 RETURN l_return_val;
54
55 EXCEPTION
56 WHEN OTHERS THEN
57 IF cur_end_date%ISOPEN THEN
58 CLOSE cur_end_date;
59 END IF;
60
61 RAISE;
62 END next_period;
63
64 --------------------------------------------------------------------------
65 -- --
66 -- Name : NEXT_PERIOD --
67 -- Type : FUNCTION --
68 -- Access : Private --
69 -- Description : Given a payroll action id, returns the --
70 -- date of the day after the end of the --
71 -- containing pay period. --
72 -- --
73 -- Parameters : --
74 -- IN : p_pactid NUMBER --
75 -- RETURN : date --
76 -- --
77 -- Change History : --
78 --------------------------------------------------------------------------
79 -- Rev# Date Userid Description --
80 --------------------------------------------------------------------------
81 -- 1.0 07-Feb-2013 pthummal Created this overloaded function --
82 --------------------------------------------------------------------------
83 FUNCTION next_period ( p_pactid IN NUMBER
84 )
85 RETURN DATE
86 IS
87
88 l_return_val DATE := NULL;
89
90 CURSOR cur_end_date ( p_pactid NUMBER
91 ) IS
92 SELECT TP.end_date + 1
93 FROM per_time_periods TP
94 , pay_payroll_actions PACT
95 WHERE PACT.payroll_action_id = p_pactid
96 AND PACT.payroll_id = TP.payroll_id
97 AND PACT.date_earned BETWEEN TP.start_date AND TP.end_date;
98
99 BEGIN
100
101
102 OPEN cur_end_date ( p_pactid
103 );
104 FETCH cur_end_date INTO l_return_val;
105 CLOSE cur_end_date;
106
107 RETURN l_return_val;
108
109 EXCEPTION
110 WHEN OTHERS THEN
111 IF cur_end_date%ISOPEN THEN
112 CLOSE cur_end_date;
113 END IF;
114
115 RAISE;
116 END next_period;
117
118 --------------------------------------------------------------------------
119 -- --
120 -- Name : NEXT_MONTH --
121 -- Type : FUNCTION --
122 -- Access : Private --
123 -- Description : Given a date, returns the date of the first day of --
124 -- the next month. --
125 -- --
126 -- Parameters : --
127 -- IN : p_date DATE --
128 -- RETURN : date --
129 -- --
130 -- Change History : --
131 --------------------------------------------------------------------------
135 --------------------------------------------------------------------------
132 -- Rev# Date Userid Description --
133 --------------------------------------------------------------------------
134 -- 1.0 16-JUL-2004 lnagaraj Created this function --
136 FUNCTION next_month ( p_date IN DATE
137 )
138 RETURN DATE
139 IS
140 l_procedure VARCHAR2(100);
141 BEGIN
142
143 RETURN TRUNC(add_months(p_date,1),'MM');
144
145 END next_month;
146
147 --------------------------------------------------------------------------
148 -- --
149 -- Name : NEXT_QUARTER --
150 -- Type : FUNCTION --
151 -- Access : Private --
152 -- Description : Given a date returns the first day of the next --
153 -- quarter. --
154 -- Parameters : --
155 -- IN : p_date DATE --
156 -- RETURN : date --
157 -- --
158 -- Change History : --
159 --------------------------------------------------------------------------
160 -- Rev# Date Userid Description --
161 --------------------------------------------------------------------------
162 -- 1.0 16-JUL-2004 lnagaraj Created this function --
163 --------------------------------------------------------------------------
164 FUNCTION next_quarter ( p_date IN DATE
165 )
166 RETURN DATE
167 IS
168
169 BEGIN
170
171 RETURN TRUNC(ADD_MONTHS(p_date,3),'Q');
172
173 END next_quarter;
174
175
176 --------------------------------------------------------------------------
177 -- --
178 -- Name : NEXT_TAX_YEAR --
179 -- Type : FUNCTION --
180 -- Access : Private --
181 -- Description : Given a date, returns the date of the first day of --
182 -- the next tax year. --
183 -- --
184 -- Parameters : --
185 -- IN : p_date DATE --
186 -- OUT : date --
187 -- --
188 -- Change History : --
189 --------------------------------------------------------------------------
190 -- Rev# Date Userid Description --
191 --------------------------------------------------------------------------
192 -- 1.0 16-JUL-2004 lnagaraj Created this function --
193 --------------------------------------------------------------------------
194 FUNCTION next_tax_year ( p_date IN DATE
195 )
196 RETURN DATE
197 IS
198 l_year number(4);
199 l_start DATE;
200 l_start_dd_mm VARCHAR2(6);
201 BEGIN
202 l_year := TO_NUMBER(TO_CHAR(p_date,'yyyy'));
203 l_start_dd_mm := '01-04-';
204
205 IF p_date >= TO_DATE(l_start_dd_mm||TO_CHAR(l_year),'dd-mm-yyyy') THEN
206 l_start := TO_DATE(l_start_dd_mm||TO_CHAR(l_year+1),'dd-mm-yyyy');
207 ELSE
208 l_start := TO_DATE(l_start_dd_mm||TO_CHAR(l_year),'dd-mm-yyyy');
209 END IF;
210 RETURN l_start;
211 END next_tax_year;
212
213 --------------------------------------------------------------------------
214 -- --
215 -- Name : NEXT_CAL_YEAR --
216 -- Type : FUNCTION --
217 -- Access : Private --
218 -- Description : Given a date, returns the date of the first day of --
219 -- the next calendar year. --
220 -- --
221 -- Parameters : --
222 -- IN : p_date DATE --
223 -- OUT : date --
224 -- --
225 -- Change History : --
226 --------------------------------------------------------------------------
227 -- Rev# Date Userid Description --
228 --------------------------------------------------------------------------
229 -- 1.0 16-JUL-2004 lnagaraj Created this function --
230 --------------------------------------------------------------------------
231 FUNCTION next_cal_year ( p_date IN DATE
232 )
233 RETURN DATE
234 IS
235 BEGIN
236 RETURN TRUNC(ADD_MONTHS(p_date,12),'Y');
237 END next_cal_year;
238
239 --------------------------------------------------------------------------
240 -- --
241 -- Name : NEXT_MAR_FEB_YEAR --
242 -- Type : FUNCTION --
243 -- Access : Private --
244 -- Description : Given a date, returns the date of the first day of --
245 -- the next Mar-Feb year. --
246 -- --
247 -- Parameters : --
248 -- IN : p_date DATE --
249 -- OUT : date --
250 -- --
251 -- Change History : --
252 --------------------------------------------------------------------------
253 -- Rev# Date Userid Description --
254 --------------------------------------------------------------------------
255 -- 1.0 16-JUL-2004 lnagaraj Created this function --
256 --------------------------------------------------------------------------
257 FUNCTION next_mar_feb_year ( p_date IN DATE
258 )
259 RETURN DATE
260 IS
261 l_year number(4);
262 l_start DATE;
263 l_start_dd_mm VARCHAR2(6);
264 BEGIN
265 l_year := TO_NUMBER(TO_CHAR(p_date,'yyyy'));
266 l_start_dd_mm := '01-03-';
267
268 IF p_date >= TO_DATE(l_start_dd_mm||TO_CHAR(l_year),'dd-mm-yyyy') THEN
269 l_start := TO_DATE(l_start_dd_mm||TO_CHAR(l_year+1),'dd-mm-yyyy');
270 ELSE
271 l_start := TO_DATE(l_start_dd_mm||TO_CHAR(l_year ),'dd-mm-yyyy');
272 END IF;
273 RETURN l_start;
274 END next_mar_feb_year;
275
276 --------------------------------------------------------------------------
277 -- --
278 -- Name : NEXT_HALF_YEAR --
279 -- Type : FUNCTION --
280 -- Access : Private --
281 -- Description : Given a date, returns the date of the first day of --
282 -- the NEXT Half Tax year. --
283 -- --
284 -- Parameters : --
285 -- IN : p_date DATE --
286 -- OUT : date --
287 -- --
288 -- Change History : --
289 --------------------------------------------------------------------------
290 -- Rev# Date Userid Description --
291 --------------------------------------------------------------------------
295 )
292 -- 1.0 16-JUL-2004 lnagaraj Created this function --
293 --------------------------------------------------------------------------
294 FUNCTION next_half_year ( p_date IN DATE
296 RETURN DATE
297 IS
298 l_year number(4);
299 l_month number(2);
300 l_start DATE;
301 l_half_start1 constant VARCHAR2(6) := '01-04-';
302 l_half_start2 constant VARCHAR2(6) := '01-10-';
303 BEGIN
304 l_year := TO_NUMBER(TO_CHAR(p_date,'yyyy'));
305 l_month:= TO_NUMBER(TO_CHAR(p_date,'mm'));
306
307 IF l_month BETWEEN 4 AND 9 THEN
308 l_start := TO_DATE(l_half_start2||TO_CHAR(l_year),'dd-mm-yyyy');
309 ELSIF l_month BETWEEN 10 and 12 THEN
310 l_start := TO_DATE(l_half_start1||TO_CHAR(l_year+1),'dd-mm-yyyy');
311 ELSE
312 l_start := TO_DATE(l_half_start1||TO_CHAR(l_year),'dd-mm-yyyy');
313 END IF;
314
315
316 RETURN l_start;
317 END next_half_year;
318
319 --------------------------------------------------------------------------
320 -- --
321 -- Name : NEXT_CAL_HALF_YEAR --
322 -- Type : FUNCTION --
323 -- Access : Private --
324 -- Description : Given a date, returns the date of the first day of --
325 -- the NEXT Half Tax year. --
326 -- --
327 -- Parameters : --
328 -- IN : p_date DATE --
329 -- OUT : date --
330 -- --
331 -- Change History : --
332 --------------------------------------------------------------------------
333 -- Rev# Date Userid Description --
334 --------------------------------------------------------------------------
335 -- 1.0 02-JUN-2008 rsaharay Created this function --
336 --------------------------------------------------------------------------
337 FUNCTION next_cal_half_year ( p_date IN DATE
338 )
339 RETURN DATE
340 IS
341 l_year number(4);
342 l_month number(2);
343 l_start DATE;
344 l_half_start1 constant VARCHAR2(6) := '01-01-';
345 l_half_start2 constant VARCHAR2(6) := '01-07-';
346 BEGIN
347 l_year := TO_NUMBER(TO_CHAR(p_date,'yyyy'));
348 l_month:= TO_NUMBER(TO_CHAR(p_date,'mm'));
349
350 IF l_month BETWEEN 1 AND 6 THEN
351 l_start := TO_DATE(l_half_start2||TO_CHAR(l_year),'dd-mm-yyyy');
352 ELSE
353 l_start := TO_DATE(l_half_start1||TO_CHAR(l_year+1),'dd-mm-yyyy');
354 END IF;
355
356
357 RETURN l_start;
358 END next_cal_half_year;
359
360 --------------------------------------------------------------------------
361
362 -- --
363 -- Name : DATE_EC --
364 -- Type : PROCEDURE --
365 -- Access : Public --
366 -- Description : This procedure assumes the date portion of the --
367 -- dimension name is always at the end to allow --
368 -- accurate identification since this is used for many --
369 -- dimensions. --
370 -- --
371 -- Parameters : --
372 -- IN : p_owner_payroll_action_id NUMBER --
373 -- p_user_payroll_action_id NUMBER --
374 -- p_owner_assignment_action_id NUMBER --
375 -- p_user_assignment_action_id NUMBER --
376 -- p_owner_effective_date DATE --
377 -- p_user_effective_date DATE --
378 -- p_dimension_name VARCHAR2 --
379 -- OUT : p_expiry_information NUMBER --
380 -- --
381 -- Change History : --
382 --------------------------------------------------------------------------
383 -- Rev# Date Userid Description --
384 --------------------------------------------------------------------------
385 -- 1.0 16-JUL-04 lnagaraj Created this procedure --
386 -- 1.1 07-OCT-04 lnagaraj Added Code for LTD dimensions --
387 --------------------------------------------------------------------------
388 PROCEDURE date_ec ( p_owner_payroll_action_id IN NUMBER
389 , p_user_payroll_action_id IN NUMBER
390 , p_owner_assignment_action_id IN NUMBER
391 , p_user_assignment_action_id IN NUMBER
392 , p_owner_effective_date IN DATE
393 , p_user_effective_date IN DATE
394 , p_dimension_name IN VARCHAR2
395 , p_expiry_information OUT NOCOPY NUMBER
396 )
397 IS
398
399 l_message VARCHAR2(255);
400 l_expiry_date DATE := NULL;
401 l_procedure VARCHAR2(100);
402
403
404 BEGIN
405
406 g_debug := hr_utility.debug_enabled;
410 IF g_debug THEN
407 l_procedure := g_package ||'date_ec1';
408 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
409
411 pay_in_utils.trace('**************************************************','********************');
412 pay_in_utils.trace('Owner Payroll Action id ',p_owner_payroll_action_id);
413 pay_in_utils.trace('User Payroll Action id ',p_user_payroll_action_id);
414 pay_in_utils.trace('Owner Assignment Action id ',p_owner_assignment_action_id);
415 pay_in_utils.trace('User Assignment Action id ',p_user_assignment_action_id);
416 pay_in_utils.trace('Owner Effective Date ',p_owner_effective_date);
417 pay_in_utils.trace('User Effective Date ',p_user_effective_date);
418 pay_in_utils.trace('Dimension Name ',p_dimension_name);
419 pay_in_utils.trace('**************************************************','********************');
420 END IF;
421
422 IF p_dimension_name LIKE '%RUN' THEN
423 -- must check for special case: if payroll action id's are the same,
424 -- then don't expire. This facilitates meaningful access of these
425 -- balances outside of runs.
426
427 IF p_owner_payroll_action_id <> p_user_payroll_action_id THEN
428 l_expiry_date := p_user_effective_date; -- always must expire.
429 ELSE
430 p_expiry_information := 0;
431 RETURN;
432 END IF;
433
434 ELSIF p_dimension_name LIKE '%PAYMENTS' THEN
435 -- must check for special case: if payroll action id's are the same,
436 -- then don't expire. This facilitates meaningful access of these
437 -- balances outside of runs.
438
439 IF p_owner_payroll_action_id <> p_user_payroll_action_id THEN
440 l_expiry_date := p_user_effective_date; -- always must expire.
441 ELSE
442 p_expiry_information := 0;
443 RETURN;
444 END IF;
445
446 ELSIF p_dimension_name LIKE '%DE_PTD' THEN
447 l_expiry_date := next_period ( p_owner_payroll_action_id
448 );
449
450 ELSIF p_dimension_name LIKE '%PTD' THEN
451 l_expiry_date := next_period ( p_owner_payroll_action_id
452 , p_owner_effective_date
453 );
454
455 ELSIF p_dimension_name LIKE '%MTD' THEN
456 l_expiry_date := next_month ( p_owner_effective_date);
457
458 ELSIF p_dimension_name LIKE '%QTD' THEN
459 l_expiry_date := next_quarter ( p_owner_effective_date);
460
461 ELSIF p_dimension_name LIKE '%CYTD' THEN
462
463 l_expiry_date := next_cal_year ( p_owner_effective_date);
464
465 ELSIF p_dimension_name LIKE '%MAR_FEB_YTD' THEN
466
467 l_expiry_date := next_mar_feb_year ( p_owner_effective_date);
468 ELSIF p_dimension_name LIKE '%CHYTD' THEN
469
470 l_expiry_date := next_cal_half_year ( p_owner_effective_date);
471
472 ELSIF p_dimension_name LIKE '%HYTD' THEN
473
474 l_expiry_date := next_half_year ( p_owner_effective_date);
475
476 ELSIF p_dimension_name LIKE '%YTD' THEN
477 l_expiry_date := next_tax_year ( p_owner_effective_date);
478 /* Changes for 3839878 Start */
479 ELSIF p_dimension_name LIKE '%LTD' THEN
480 p_expiry_information := 0;
481 RETURN;
482 /* Changes for 3839878 End */
483
484 ELSE
485 hr_utility.set_message(801,'NO_EXP_CHECK_FOR_DIMENSION');
486 hr_utility.raise_error;
487
488 END IF;
489
490 IF p_user_effective_date >= l_expiry_date THEN
491 p_expiry_information := 1;
492 ELSE
493 p_expiry_information := 0;
494 END IF;
495
496 IF g_debug THEN
497 pay_in_utils.trace('**************************************************','********************');
498 pay_in_utils.trace('Expiry Date ',l_expiry_date);
499 pay_in_utils.trace('Expiry Information ',p_expiry_information);
500 pay_in_utils.trace('**************************************************','********************');
501 END IF;
502 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
503
504 END date_ec;
505
506 --------------------------------------------------------------------------
507 -- --
508 -- Name : DATE_EC --
509 -- Type : PROCEDURE --
510 -- Access : Public --
511 -- Description : This procedure assumes the date portion of the --
512 -- dimension name is always at the end to allow --
513 -- accurate identification since this is used for many --
514 -- dimensions. --
515 -- --
516 -- Parameters : --
517 -- IN : p_owner_payroll_action_id NUMBER --
518 -- p_user_payroll_action_id NUMBER --
519 -- p_owner_assignment_action_id NUMBER --
520 -- p_user_assignment_action_id NUMBER --
521 -- p_owner_effective_date DATE --
522 -- p_user_effective_date DATE --
523 -- p_dimension_name VARCHAR2 --
524 -- OUT : p_expiry_information DATE --
525 -- --
529 --------------------------------------------------------------------------
526 -- Change History : --
527 --------------------------------------------------------------------------
528 -- Rev# Date Userid Description --
530 -- 1.0 16-JUL-04 lnagaraj Created this procedure --
531 -- 1.1 07-OCT-04 lnagaraj Added Code for LTD dimension --
532 --------------------------------------------------------------------------
533
534 PROCEDURE date_ec ( p_owner_payroll_action_id IN NUMBER
535 , p_user_payroll_action_id IN NUMBER
536 , p_owner_assignment_action_id IN NUMBER
537 , p_user_assignment_action_id IN NUMBER
538 , p_owner_effective_date IN DATE
539 , p_user_effective_date IN DATE
540 , p_dimension_name IN VARCHAR2
541 , p_expiry_information OUT NOCOPY DATE
542 )
543 IS
544
545
546 l_expiry_date DATE := NULL;
547 l_procedure VARCHAR2(100);
548 l_message VARCHAR2(255);
549
550
551 BEGIN
552
553 g_debug := hr_utility.debug_enabled;
554 l_procedure := g_package ||'date_ec2';
555 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
556
557 IF g_debug THEN
558 pay_in_utils.trace('**************************************************','********************');
559 pay_in_utils.trace('Owner Payroll Action id ',p_owner_payroll_action_id);
560 pay_in_utils.trace('User Payroll Action id ',p_user_payroll_action_id);
561 pay_in_utils.trace('Owner Assignment Action id ',p_owner_assignment_action_id);
562 pay_in_utils.trace('User Assignment Action id ',p_user_assignment_action_id);
563 pay_in_utils.trace('Owner Effective Date ',p_owner_effective_date);
564 pay_in_utils.trace('User Effective Date ',p_user_effective_date);
565 pay_in_utils.trace('Dimension Name ',p_dimension_name);
566 pay_in_utils.trace('**************************************************','********************');
567 END IF;
568
569
570 IF p_dimension_name LIKE '%RUN' THEN
571 -- must check for special case: if payroll action id's are the same,
572 -- then don't expire. This facilitates meaningful access of these
573 -- balances outside of runs.
574
575 p_expiry_information := p_owner_effective_date;
576
577 ELSIF p_dimension_name LIKE '%PAYMENTS' THEN
578
579 p_expiry_information := p_owner_effective_date;
580
581 ELSIF p_dimension_name LIKE '%DE_PTD' THEN
582 p_expiry_information := next_period ( p_owner_payroll_action_id
583 ) - 1 ;
584
585 ELSIF p_dimension_name LIKE '%PTD' THEN
586 p_expiry_information := next_period ( p_owner_payroll_action_id
587 , p_owner_effective_date
588 ) - 1 ;
589
590 ELSIF p_dimension_name LIKE '%MTD' THEN
591 p_expiry_information := next_month ( p_owner_effective_date) - 1 ;
592
593 ELSIF p_dimension_name LIKE '%QTD' THEN
594 p_expiry_information := next_quarter ( p_owner_effective_date) - 1 ;
595
596 ELSIF p_dimension_name LIKE '%CYTD' THEN
597
598 p_expiry_information := next_cal_year ( p_owner_effective_date)-1;
599
600 ELSIF p_dimension_name LIKE '%CHYTD' THEN
601
602 p_expiry_information := next_cal_half_year ( p_owner_effective_date)-1;
603
604
605 ELSIF p_dimension_name LIKE '%MAR_FEB_YTD' THEN
606
607 p_expiry_information := next_mar_feb_year ( p_owner_effective_date)-1;
608 ELSIF p_dimension_name LIKE '%HYTD' THEN
609
610 p_expiry_information := next_half_year ( p_owner_effective_date)-1;
611
612 ELSIF p_dimension_name LIKE '%YTD' THEN
613 p_expiry_information := next_tax_year ( p_owner_effective_date) - 1 ;
614 /* Changes for 3839878 Start */
615 ELSIF p_dimension_name LIKE '%LTD' THEN
616 p_expiry_information := fnd_date.canonical_to_date('4712/12/31');
617 /* Changes for 3839878 End */
618 ELSE
619 hr_utility.set_message(801,'NO_EXP_CHECK_FOR_DIMENSION');
620 hr_utility.raise_error;
621
622 END IF;
623
624 IF g_debug THEN
625 pay_in_utils.trace('**************************************************','********************');
626 pay_in_utils.trace('Expiry Information ',p_expiry_information);
627 pay_in_utils.trace('**************************************************','********************');
628 END IF;
629
630 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
631
632 END date_ec;
633
634 PROCEDURE cal_hy_start(p_effective_date IN DATE ,
635 p_start_date OUT NOCOPY DATE,
636 p_start_date_code IN VARCHAR2 DEFAULT NULL,
637 p_payroll_id IN NUMBER DEFAULT NULL,
638 p_bus_grp IN NUMBER DEFAULT NULL,
639 p_action_type IN VARCHAR2 DEFAULT NULL,
640 p_asg_action IN NUMBER DEFAULT NULL)
641 AS
642
643 l_year NUMBER(4);
644 l_month NUMBER(2);
645 l_half_start1 CONSTANT VARCHAR2(6):='01-01-';
646 l_half_start2 CONSTANT VARCHAR2(6):= '01-07-';
647 l_procedure VARCHAR2(100);
648 l_message VARCHAR2(255);
649
650 BEGIN
651 g_debug := hr_utility.debug_enabled;
652 l_procedure := g_package ||'cal_hy_start';
653 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
654
655 p_start_date :=NULL;
656 l_year := TO_NUMBER (TO_CHAR(p_effective_date,'yyyy'));
657 l_month := TO_NUMBER (TO_CHAR(p_effective_date,'mm'));
658
659 IF l_month BETWEEN 1 and 6 THEN
660 p_start_date:=TO_DATE(l_half_start1||TO_CHAR(l_year),'DD-MM-YYYY');
661 ELSE
662 p_start_date:=TO_DATE(l_half_start2||TO_CHAR(l_year),'DD-MM-YYYY');
663 END IF;
664
665 IF g_debug THEN
666 pay_in_utils.trace('**************************************************','********************');
667 pay_in_utils.trace('Effective Date ',p_effective_date);
668 pay_in_utils.trace('Start Date ',p_start_date);
669 pay_in_utils.trace('**************************************************','********************');
670 END IF;
671
672 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
673
674 END;
675
676
677
678
679 PROCEDURE prov_ytd_start(p_effective_date IN DATE ,
680 p_start_date OUT NOCOPY DATE,
681 p_start_date_code IN VARCHAR2 DEFAULT NULL,
682 p_payroll_id IN NUMBER DEFAULT NULL,
683 p_bus_grp IN NUMBER DEFAULT NULL,
684 p_action_type IN VARCHAR2 DEFAULT NULL,
685 p_asg_action IN NUMBER DEFAULT NULL)
686 AS
687
688 l_year NUMBER(4);
689 l_message VARCHAR2(255);
690 l_procedure VARCHAR2(100);
691
692 BEGIN
693 g_debug := hr_utility.debug_enabled;
694 l_procedure := g_package ||'prov_ytd_start';
695 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
696
697 p_start_date :=NULL;
698 l_year := TO_NUMBER (TO_CHAR(p_effective_date,'yyyy'));
699
700 IF p_effective_date>=TO_DATE('01-03-'||TO_CHAR(l_year),'DD-MM-YYYY') THEN
701 p_start_date := TO_DATE('01-03-'||TO_CHAR(l_year),'DD-MM-YYYY');
702 ELSE
703 p_start_date := TO_DATE('01-03-'||TO_CHAR(l_year-1),'DD-MM-YYYY');
704 END IF;
705
706 IF g_debug THEN
707 pay_in_utils.trace('**************************************************','********************');
708 pay_in_utils.trace('Effective Date ',p_effective_date);
709 pay_in_utils.trace('Start Date ',p_start_date);
710 END IF;
711
712 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
713
714 END;
715
716 PROCEDURE hytd_start(p_effective_date IN DATE ,
717 p_start_date OUT NOCOPY DATE,
718 p_start_date_code IN VARCHAR2 DEFAULT NULL,
719 p_payroll_id IN NUMBER DEFAULT NULL,
720 p_bus_grp IN NUMBER DEFAULT NULL,
721 p_action_type IN VARCHAR2 DEFAULT NULL,
722 p_asg_action IN NUMBER DEFAULT NULL)
723 AS
724
725 l_year NUMBER(4);
726 l_month NUMBER(2);
727 l_half_start1 CONSTANT VARCHAR2(6):='01-04-';
728 l_half_start2 CONSTANT VARCHAR2(6):= '01-10-';
729 l_procedure VARCHAR2(100);
730 l_message VARCHAR2(255);
731
732 BEGIN
733 g_debug := hr_utility.debug_enabled;
734 l_procedure := g_package ||'hytd_start';
735 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
736
737 p_start_date :=NULL;
738 l_year := TO_NUMBER (TO_CHAR(p_effective_date,'yyyy'));
739 l_month := TO_NUMBER (TO_CHAR(p_effective_date,'mm'));
740
741 IF l_month BETWEEN 4 and 9 THEN
742 p_start_date:=TO_DATE(l_half_start1||TO_CHAR(l_year),'DD-MM-YYYY');
743 ELSIF l_month BETWEEN 10 and 12 THEN
744 p_start_date:=TO_DATE(l_half_start2||TO_CHAR(l_year),'DD-MM-YYYY');
745 ELSE
746 p_start_date:=TO_DATE(l_half_start2||TO_CHAR(l_year-1),'DD-MM-YYYY');
747 END IF;
748
749 IF g_debug THEN
750 pay_in_utils.trace('**************************************************','********************');
751 pay_in_utils.trace('Effective Date ',p_effective_date);
752 pay_in_utils.trace('Start Date ',p_start_date);
753 pay_in_utils.trace('**************************************************','********************');
754 END IF;
755
756 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
757
758 END;
759
760 PROCEDURE get_td_start(p_effective_date in date,
761 p_start_date out nocopy date,
762 p_bus_grp in number default null,
763 p_payroll_id in number default null,
764 p_asg_action in number default null)
765 AS
766
767 l_year NUMBER(4);
768 l_month NUMBER(2);
769 l_half_start1 CONSTANT VARCHAR2(6):='01-04-';
770 l_half_start2 CONSTANT VARCHAR2(6):= '01-10-';
771 l_procedure VARCHAR2(100);
772 l_message VARCHAR2(255);
773 l_frequency NUMBER := 12;
774 l_assignment_id NUMBER;
775 l_start_date DATE;
776 l_end_date DATE;
777
778 cursor csr_freq IS
779 select target.org_information2
780 from per_all_assignments_f assign,
781 hr_soft_coding_keyflex hsc,
782 hr_organization_information target,
783 pay_assignment_actions paa
784 WHERE paa.assignment_action_id = p_asg_action
785 AND paa.assignment_id = assign.assignment_id
786 AND p_effective_date BETWEEN ASSIGN.effective_start_date AND ASSIGN.effective_end_date
787 AND assign.business_group_id = target.organization_id
788 AND target.org_information_context = 'PER_IN_PT_FREQUENCY'
789 AND assign.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
790 AND pay_in_prof_tax_pkg.get_state(hsc.segment3) = target.org_information1
791 AND p_effective_date BETWEEN fnd_date.canonical_to_date(target.org_information3)
792 AND fnd_date.canonical_to_date(nvl(target.org_information4,'4712/12/31 00:00:00'));
793
794 BEGIN
795 g_debug := hr_utility.debug_enabled;
796 l_procedure := g_package ||'get_td_start';
797 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
798
799 p_start_date :=NULL;
800 l_year := TO_NUMBER (TO_CHAR(p_effective_date,'yyyy'));
801 l_month := TO_NUMBER (TO_CHAR(p_effective_date,'mm'));
802
803 OPEN csr_freq;
804 FETCH csr_freq INTO l_frequency;
805 CLOSE csr_freq;
806
807 IF l_frequency = 2 THEN
808
809 IF l_month BETWEEN 4 and 9 THEN
810 p_start_date:=TO_DATE(l_half_start1||TO_CHAR(l_year),'DD-MM-YYYY');
811 ELSIF l_month BETWEEN 10 and 12 THEN
812 p_start_date:=TO_DATE(l_half_start2||TO_CHAR(l_year),'DD-MM-YYYY');
813 ELSE
814 p_start_date:=TO_DATE(l_half_start2||TO_CHAR(l_year-1),'DD-MM-YYYY');
815 END IF;
816
817 ELSIF l_frequency = 1 THEN
818
819 p_start_date := TO_DATE('01-'||to_char(l_month)||'-'||to_char(l_year),'DD-MM-YYYY');
820
821 ELSIF l_frequency = 12 THEN
822
823 IF l_month BETWEEN 4 and 12 THEN
824 p_start_date := TO_DATE('01-04-'||TO_CHAR(l_year),'DD-MM-YYYY');
825 ELSE
826 p_start_date := TO_DATE('01-04-'||TO_CHAR(l_year-1),'DD-MM-YYYY');
827 END IF;
828
829 END IF;
830 IF g_debug THEN
831 pay_in_utils.trace('**************************************************','********************');
832 pay_in_utils.trace('Frequency ',l_frequency );
833 pay_in_utils.trace('Effective Date ',p_effective_date);
834 pay_in_utils.trace('Start Date ',p_start_date);
835 pay_in_utils.trace('**************************************************','********************');
836 END IF;
837
838 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
839
840 END;
841
842 --------------------------------------------------------------------------
843 -- --
844 -- Name : START_CODE_PMTH --
845 -- Type : PROCEDURE --
846 -- Access : Public --
847 -- Description : This procedure finds the start date based on the --
848 -- effective date for the dimension name _ASG_PMTH --
849 -- --
850 -- Parameters : --
851 -- IN : p_effective_date DATE --
852 -- p_payroll_id NUMBER --
853 -- p_bus_grp NUMBER --
854 -- p_asg_action NUMBER --
855 -- OUT : p_start_date DATE --
856 -- RETURN : N/A --
857 -- --
858 -- Change History : --
859 --------------------------------------------------------------------------
860 -- Rev# Date Userid Description --
861 --------------------------------------------------------------------------
862 -- 1.0 15-Sep-2004 abhjain Created the procedure --
863 --------------------------------------------------------------------------
864 PROCEDURE start_code_pmth ( p_effective_date IN DATE
865 , p_start_date OUT NOCOPY DATE
866 , p_payroll_id IN NUMBER
867 , p_bus_grp IN NUMBER
868 , p_asg_action IN NUMBER
869 )
870 IS
871 l_procedure VARCHAR2(100);
872 l_message VARCHAR2(255);
873 BEGIN
874 g_debug := hr_utility.debug_enabled;
875 l_procedure := g_package ||'start_code_pmth';
876 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
877
878 p_start_date := last_day(add_months(p_effective_date, -2))+1;
879
880 IF g_debug THEN
881 pay_in_utils.trace('**************************************************','********************');
882 pay_in_utils.trace('Effective Date ',p_effective_date);
883 pay_in_utils.trace('Start Date ',p_start_date);
884 pay_in_utils.trace('**************************************************','********************');
885 END IF;
886
887 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
888
889
890 END start_code_pmth;
891
892 --------------------------------------------------------------------------
893 -- --
894 -- Name : START_CODE_P10MTH --
895 -- Type : PROCEDURE --
896 -- Access : Public --
897 -- Description : This procedure finds the start date based on the --
898 -- effective date for the dimension name _ASG_P10MTH --
899 -- --
900 -- Parameters : --
901 -- IN : p_effective_date DATE --
902 -- p_payroll_id NUMBER --
903 -- p_bus_grp NUMBER --
904 -- p_asg_action NUMBER --
905 -- OUT : p_start_date DATE --
906 -- RETURN : N/A --
907 -- --
908 -- Change History : --
909 --------------------------------------------------------------------------
910 -- Rev# Date Userid Description --
911 --------------------------------------------------------------------------
912 -- 1.0 14-Oct-2004 lnagaraj Created the procedure --
913 --------------------------------------------------------------------------
914 PROCEDURE start_code_p10mth ( p_effective_date IN DATE
915 , p_start_date OUT NOCOPY DATE
916 , p_payroll_id IN NUMBER
917 , p_bus_grp IN NUMBER
918 , p_asg_action IN NUMBER
919 )
920 IS
921 l_procedure VARCHAR2(100);
922 l_message VARCHAR2(255);
923 BEGIN
924 g_debug := hr_utility.debug_enabled;
925 l_procedure := g_package ||'start_code_p10mth';
926 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
927
928 p_start_date := last_day(add_months(p_effective_date, -11))+1;
929
930 IF g_debug THEN
931 pay_in_utils.trace('**************************************************','********************');
932 pay_in_utils.trace('Effective Date ',p_effective_date);
933 pay_in_utils.trace('Start Date ',p_start_date);
934 pay_in_utils.trace('**************************************************','********************');
935 END IF;
936 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
937
938 END start_code_p10mth;
939
940
941 END pay_in_exc;
[Home] [Help]