1 PACKAGE BODY pay_in_exc AS
2 /* $Header: pyinexc.pkb 120.4 2008/06/03 14:14:10 lnagaraj noship $ */
3 g_package CONSTANT VARCHAR2(100) := 'pay_in_exc.';
4 g_debug BOOLEAN;
5
6 --------------------------------------------------------------------------
7 -- --
8 -- Name : NEXT_PERIOD --
9 -- Type : FUNCTION --
10 -- Access : Private --
11 -- Description : Given a date and a payroll action id, returns the --
12 -- date of the day after the end of 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_MONTH --
67 -- Type : FUNCTION --
68 -- Access : Private --
69 -- Description : Given a date, returns the date of the first day of --
70 -- the next month. --
71 -- --
72 -- Parameters : --
73 -- IN : p_date DATE --
74 -- RETURN : date --
75 -- --
76 -- Change History : --
77 --------------------------------------------------------------------------
78 -- Rev# Date Userid Description --
79 --------------------------------------------------------------------------
80 -- 1.0 16-JUL-2004 lnagaraj Created this function --
81 --------------------------------------------------------------------------
82 FUNCTION next_month ( p_date IN DATE
83 )
84 RETURN DATE
85 IS
86 l_procedure VARCHAR2(100);
87 BEGIN
88
89 RETURN TRUNC(add_months(p_date,1),'MM');
90
91 END next_month;
92
93 --------------------------------------------------------------------------
94 -- --
95 -- Name : NEXT_QUARTER --
96 -- Type : FUNCTION --
97 -- Access : Private --
98 -- Description : Given a date returns the first day of the next --
99 -- quarter. --
100 -- Parameters : --
101 -- IN : p_date DATE --
102 -- RETURN : date --
103 -- --
104 -- Change History : --
105 --------------------------------------------------------------------------
106 -- Rev# Date Userid Description --
107 --------------------------------------------------------------------------
108 -- 1.0 16-JUL-2004 lnagaraj Created this function --
109 --------------------------------------------------------------------------
110 FUNCTION next_quarter ( p_date IN DATE
111 )
112 RETURN DATE
113 IS
114
115 BEGIN
116
117 RETURN TRUNC(ADD_MONTHS(p_date,3),'Q');
118
119 END next_quarter;
120
121
122 --------------------------------------------------------------------------
123 -- --
124 -- Name : NEXT_TAX_YEAR --
125 -- Type : FUNCTION --
126 -- Access : Private --
127 -- Description : Given a date, returns the date of the first day of --
128 -- the next tax year. --
129 -- --
130 -- Parameters : --
131 -- IN : p_date DATE --
132 -- OUT : date --
133 -- --
134 -- Change History : --
135 --------------------------------------------------------------------------
136 -- Rev# Date Userid Description --
137 --------------------------------------------------------------------------
138 -- 1.0 16-JUL-2004 lnagaraj Created this function --
139 --------------------------------------------------------------------------
140 FUNCTION next_tax_year ( p_date IN DATE
141 )
142 RETURN DATE
143 IS
144 l_year number(4);
145 l_start DATE;
146 l_start_dd_mm VARCHAR2(6);
147 BEGIN
148 l_year := TO_NUMBER(TO_CHAR(p_date,'yyyy'));
149 l_start_dd_mm := '01-04-';
150
151 IF p_date >= TO_DATE(l_start_dd_mm||TO_CHAR(l_year),'dd-mm-yyyy') THEN
152 l_start := TO_DATE(l_start_dd_mm||TO_CHAR(l_year+1),'dd-mm-yyyy');
153 ELSE
154 l_start := TO_DATE(l_start_dd_mm||TO_CHAR(l_year),'dd-mm-yyyy');
155 END IF;
156 RETURN l_start;
157 END next_tax_year;
158
159 --------------------------------------------------------------------------
160 -- --
161 -- Name : NEXT_CAL_YEAR --
162 -- Type : FUNCTION --
163 -- Access : Private --
164 -- Description : Given a date, returns the date of the first day of --
165 -- the next calendar year. --
166 -- --
167 -- Parameters : --
168 -- IN : p_date DATE --
169 -- OUT : date --
170 -- --
171 -- Change History : --
172 --------------------------------------------------------------------------
173 -- Rev# Date Userid Description --
174 --------------------------------------------------------------------------
175 -- 1.0 16-JUL-2004 lnagaraj Created this function --
176 --------------------------------------------------------------------------
177 FUNCTION next_cal_year ( p_date IN DATE
178 )
179 RETURN DATE
180 IS
181 BEGIN
182 RETURN TRUNC(ADD_MONTHS(p_date,12),'Y');
183 END next_cal_year;
184
185 --------------------------------------------------------------------------
186 -- --
187 -- Name : NEXT_MAR_FEB_YEAR --
188 -- Type : FUNCTION --
189 -- Access : Private --
190 -- Description : Given a date, returns the date of the first day of --
191 -- the next Mar-Feb year. --
192 -- --
193 -- Parameters : --
194 -- IN : p_date DATE --
195 -- OUT : date --
196 -- --
197 -- Change History : --
198 --------------------------------------------------------------------------
199 -- Rev# Date Userid Description --
200 --------------------------------------------------------------------------
201 -- 1.0 16-JUL-2004 lnagaraj Created this function --
202 --------------------------------------------------------------------------
203 FUNCTION next_mar_feb_year ( p_date IN DATE
204 )
205 RETURN DATE
206 IS
207 l_year number(4);
208 l_start DATE;
209 l_start_dd_mm VARCHAR2(6);
210 BEGIN
211 l_year := TO_NUMBER(TO_CHAR(p_date,'yyyy'));
212 l_start_dd_mm := '01-03-';
213
214 IF p_date >= TO_DATE(l_start_dd_mm||TO_CHAR(l_year),'dd-mm-yyyy') THEN
215 l_start := TO_DATE(l_start_dd_mm||TO_CHAR(l_year+1),'dd-mm-yyyy');
216 ELSE
217 l_start := TO_DATE(l_start_dd_mm||TO_CHAR(l_year ),'dd-mm-yyyy');
218 END IF;
219 RETURN l_start;
220 END next_mar_feb_year;
221
222 --------------------------------------------------------------------------
223 -- --
224 -- Name : NEXT_HALF_YEAR --
225 -- Type : FUNCTION --
226 -- Access : Private --
227 -- Description : Given a date, returns the date of the first day of --
228 -- the NEXT Half Tax year. --
229 -- --
230 -- Parameters : --
231 -- IN : p_date DATE --
232 -- OUT : date --
233 -- --
234 -- Change History : --
235 --------------------------------------------------------------------------
236 -- Rev# Date Userid Description --
237 --------------------------------------------------------------------------
238 -- 1.0 16-JUL-2004 lnagaraj Created this function --
239 --------------------------------------------------------------------------
240 FUNCTION next_half_year ( p_date IN DATE
241 )
242 RETURN DATE
243 IS
244 l_year number(4);
245 l_month number(2);
246 l_start DATE;
247 l_half_start1 constant VARCHAR2(6) := '01-04-';
248 l_half_start2 constant VARCHAR2(6) := '01-10-';
249 BEGIN
250 l_year := TO_NUMBER(TO_CHAR(p_date,'yyyy'));
251 l_month:= TO_NUMBER(TO_CHAR(p_date,'mm'));
252
253 IF l_month BETWEEN 4 AND 9 THEN
254 l_start := TO_DATE(l_half_start2||TO_CHAR(l_year),'dd-mm-yyyy');
255 ELSIF l_month BETWEEN 10 and 12 THEN
256 l_start := TO_DATE(l_half_start1||TO_CHAR(l_year+1),'dd-mm-yyyy');
257 ELSE
258 l_start := TO_DATE(l_half_start1||TO_CHAR(l_year),'dd-mm-yyyy');
259 END IF;
260
261
262 RETURN l_start;
263 END next_half_year;
264
265 --------------------------------------------------------------------------
266 -- --
267 -- Name : NEXT_CAL_HALF_YEAR --
268 -- Type : FUNCTION --
269 -- Access : Private --
270 -- Description : Given a date, returns the date of the first day of --
271 -- the NEXT Half Tax year. --
272 -- --
273 -- Parameters : --
274 -- IN : p_date DATE --
275 -- OUT : date --
276 -- --
277 -- Change History : --
278 --------------------------------------------------------------------------
279 -- Rev# Date Userid Description --
280 --------------------------------------------------------------------------
281 -- 1.0 02-JUN-2008 rsaharay Created this function --
282 --------------------------------------------------------------------------
283 FUNCTION next_cal_half_year ( p_date IN DATE
284 )
285 RETURN DATE
286 IS
287 l_year number(4);
288 l_month number(2);
289 l_start DATE;
290 l_half_start1 constant VARCHAR2(6) := '01-01-';
291 l_half_start2 constant VARCHAR2(6) := '01-07-';
292 BEGIN
293 l_year := TO_NUMBER(TO_CHAR(p_date,'yyyy'));
294 l_month:= TO_NUMBER(TO_CHAR(p_date,'mm'));
295
296 IF l_month BETWEEN 1 AND 6 THEN
297 l_start := TO_DATE(l_half_start2||TO_CHAR(l_year),'dd-mm-yyyy');
298 ELSE
299 l_start := TO_DATE(l_half_start1||TO_CHAR(l_year+1),'dd-mm-yyyy');
300 END IF;
301
302
303 RETURN l_start;
304 END next_cal_half_year;
305
306 --------------------------------------------------------------------------
307
308 -- --
309 -- Name : DATE_EC --
310 -- Type : PROCEDURE --
311 -- Access : Public --
312 -- Description : This procedure assumes the date portion of the --
313 -- dimension name is always at the end to allow --
314 -- accurate identification since this is used for many --
315 -- dimensions. --
316 -- --
317 -- Parameters : --
318 -- IN : p_owner_payroll_action_id NUMBER --
319 -- p_user_payroll_action_id NUMBER --
320 -- p_owner_assignment_action_id NUMBER --
321 -- p_user_assignment_action_id NUMBER --
322 -- p_owner_effective_date DATE --
323 -- p_user_effective_date DATE --
324 -- p_dimension_name VARCHAR2 --
325 -- OUT : p_expiry_information NUMBER --
326 -- --
327 -- Change History : --
328 --------------------------------------------------------------------------
329 -- Rev# Date Userid Description --
330 --------------------------------------------------------------------------
331 -- 1.0 16-JUL-04 lnagaraj Created this procedure --
332 -- 1.1 07-OCT-04 lnagaraj Added Code for LTD dimensions --
333 --------------------------------------------------------------------------
334 PROCEDURE date_ec ( p_owner_payroll_action_id IN NUMBER
335 , p_user_payroll_action_id IN NUMBER
336 , p_owner_assignment_action_id IN NUMBER
337 , p_user_assignment_action_id IN NUMBER
338 , p_owner_effective_date IN DATE
339 , p_user_effective_date IN DATE
340 , p_dimension_name IN VARCHAR2
341 , p_expiry_information OUT NOCOPY NUMBER
342 )
343 IS
344
345 l_message VARCHAR2(255);
346 l_expiry_date DATE := NULL;
347 l_procedure VARCHAR2(100);
348
349
350 BEGIN
351
352 g_debug := hr_utility.debug_enabled;
353 l_procedure := g_package ||'date_ec1';
354 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
355
356 IF g_debug THEN
357 pay_in_utils.trace('**************************************************','********************');
358 pay_in_utils.trace('Owner Payroll Action id ',p_owner_payroll_action_id);
359 pay_in_utils.trace('User Payroll Action id ',p_user_payroll_action_id);
360 pay_in_utils.trace('Owner Assignment Action id ',p_owner_assignment_action_id);
361 pay_in_utils.trace('User Assignment Action id ',p_user_assignment_action_id);
362 pay_in_utils.trace('Owner Effective Date ',p_owner_effective_date);
363 pay_in_utils.trace('User Effective Date ',p_user_effective_date);
364 pay_in_utils.trace('Dimension Name ',p_dimension_name);
365 pay_in_utils.trace('**************************************************','********************');
366 END IF;
367
368 IF p_dimension_name LIKE '%RUN' THEN
369 -- must check for special case: if payroll action id's are the same,
370 -- then don't expire. This facilitates meaningful access of these
371 -- balances outside of runs.
372
373 IF p_owner_payroll_action_id <> p_user_payroll_action_id THEN
374 l_expiry_date := p_user_effective_date; -- always must expire.
375 ELSE
376 p_expiry_information := 0;
377 RETURN;
378 END IF;
379
380 ELSIF p_dimension_name LIKE '%PAYMENTS' THEN
381 -- must check for special case: if payroll action id's are the same,
382 -- then don't expire. This facilitates meaningful access of these
383 -- balances outside of runs.
384
385 IF p_owner_payroll_action_id <> p_user_payroll_action_id THEN
386 l_expiry_date := p_user_effective_date; -- always must expire.
387 ELSE
388 p_expiry_information := 0;
389 RETURN;
390 END IF;
391
392 ELSIF p_dimension_name LIKE '%PTD' THEN
393 l_expiry_date := next_period ( p_owner_payroll_action_id
394 , p_owner_effective_date
395 );
396
397 ELSIF p_dimension_name LIKE '%MTD' THEN
398 l_expiry_date := next_month ( p_owner_effective_date);
399
400 ELSIF p_dimension_name LIKE '%QTD' THEN
401 l_expiry_date := next_quarter ( p_owner_effective_date);
402
403 ELSIF p_dimension_name LIKE '%CYTD' THEN
404
405 l_expiry_date := next_cal_year ( p_owner_effective_date);
406
407 ELSIF p_dimension_name LIKE '%MAR_FEB_YTD' THEN
408
409 l_expiry_date := next_mar_feb_year ( p_owner_effective_date);
410 ELSIF p_dimension_name LIKE '%CHYTD' THEN
411
412 l_expiry_date := next_cal_half_year ( p_owner_effective_date);
413
414 ELSIF p_dimension_name LIKE '%HYTD' THEN
415
416 l_expiry_date := next_half_year ( p_owner_effective_date);
417
418 ELSIF p_dimension_name LIKE '%YTD' THEN
419 l_expiry_date := next_tax_year ( p_owner_effective_date);
420 /* Changes for 3839878 Start */
421 ELSIF p_dimension_name LIKE '%LTD' THEN
422 p_expiry_information := 0;
423 RETURN;
424 /* Changes for 3839878 End */
425
426 ELSE
427 hr_utility.set_message(801,'NO_EXP_CHECK_FOR_DIMENSION');
428 hr_utility.raise_error;
429
430 END IF;
431
432 IF p_user_effective_date >= l_expiry_date THEN
433 p_expiry_information := 1;
434 ELSE
435 p_expiry_information := 0;
436 END IF;
437
438 IF g_debug THEN
439 pay_in_utils.trace('**************************************************','********************');
440 pay_in_utils.trace('Expiry Date ',l_expiry_date);
441 pay_in_utils.trace('Expiry Information ',p_expiry_information);
442 pay_in_utils.trace('**************************************************','********************');
443 END IF;
444 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
445
446 END date_ec;
447
448 --------------------------------------------------------------------------
449 -- --
450 -- Name : DATE_EC --
451 -- Type : PROCEDURE --
452 -- Access : Public --
453 -- Description : This procedure assumes the date portion of the --
454 -- dimension name is always at the end to allow --
455 -- accurate identification since this is used for many --
456 -- dimensions. --
457 -- --
458 -- Parameters : --
459 -- IN : p_owner_payroll_action_id NUMBER --
460 -- p_user_payroll_action_id NUMBER --
461 -- p_owner_assignment_action_id NUMBER --
462 -- p_user_assignment_action_id NUMBER --
463 -- p_owner_effective_date DATE --
464 -- p_user_effective_date DATE --
465 -- p_dimension_name VARCHAR2 --
469 --------------------------------------------------------------------------
466 -- OUT : p_expiry_information DATE --
467 -- --
468 -- Change History : --
470 -- Rev# Date Userid Description --
471 --------------------------------------------------------------------------
472 -- 1.0 16-JUL-04 lnagaraj Created this procedure --
473 -- 1.1 07-OCT-04 lnagaraj Added Code for LTD dimension --
474 --------------------------------------------------------------------------
475
476 PROCEDURE date_ec ( p_owner_payroll_action_id IN NUMBER
477 , p_user_payroll_action_id IN NUMBER
478 , p_owner_assignment_action_id IN NUMBER
479 , p_user_assignment_action_id IN NUMBER
480 , p_owner_effective_date IN DATE
481 , p_user_effective_date IN DATE
482 , p_dimension_name IN VARCHAR2
483 , p_expiry_information OUT NOCOPY DATE
484 )
485 IS
486
487
488 l_expiry_date DATE := NULL;
489 l_procedure VARCHAR2(100);
490 l_message VARCHAR2(255);
491
492
493 BEGIN
494
495 g_debug := hr_utility.debug_enabled;
496 l_procedure := g_package ||'date_ec2';
497 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
498
499 IF g_debug THEN
500 pay_in_utils.trace('**************************************************','********************');
501 pay_in_utils.trace('Owner Payroll Action id ',p_owner_payroll_action_id);
502 pay_in_utils.trace('User Payroll Action id ',p_user_payroll_action_id);
503 pay_in_utils.trace('Owner Assignment Action id ',p_owner_assignment_action_id);
504 pay_in_utils.trace('User Assignment Action id ',p_user_assignment_action_id);
505 pay_in_utils.trace('Owner Effective Date ',p_owner_effective_date);
506 pay_in_utils.trace('User Effective Date ',p_user_effective_date);
507 pay_in_utils.trace('Dimension Name ',p_dimension_name);
508 pay_in_utils.trace('**************************************************','********************');
509 END IF;
510
511
512 IF p_dimension_name LIKE '%RUN' THEN
513 -- must check for special case: if payroll action id's are the same,
514 -- then don't expire. This facilitates meaningful access of these
515 -- balances outside of runs.
516
517 p_expiry_information := p_owner_effective_date;
518
519 ELSIF p_dimension_name LIKE '%PAYMENTS' THEN
520
521 p_expiry_information := p_owner_effective_date;
522
523 ELSIF p_dimension_name LIKE '%PTD' THEN
524 p_expiry_information := next_period ( p_owner_payroll_action_id
525 , p_owner_effective_date
526 ) - 1 ;
527
528 ELSIF p_dimension_name LIKE '%MTD' THEN
529 p_expiry_information := next_month ( p_owner_effective_date) - 1 ;
530
531 ELSIF p_dimension_name LIKE '%QTD' THEN
532 p_expiry_information := next_quarter ( p_owner_effective_date) - 1 ;
533
534 ELSIF p_dimension_name LIKE '%CYTD' THEN
535
536 p_expiry_information := next_cal_year ( p_owner_effective_date)-1;
537
538 ELSIF p_dimension_name LIKE '%CHYTD' THEN
539
540 p_expiry_information := next_cal_half_year ( p_owner_effective_date)-1;
541
542
543 ELSIF p_dimension_name LIKE '%MAR_FEB_YTD' THEN
544
545 p_expiry_information := next_mar_feb_year ( p_owner_effective_date)-1;
546 ELSIF p_dimension_name LIKE '%HYTD' THEN
547
548 p_expiry_information := next_half_year ( p_owner_effective_date)-1;
549
550 ELSIF p_dimension_name LIKE '%YTD' THEN
551 p_expiry_information := next_tax_year ( p_owner_effective_date) - 1 ;
552 /* Changes for 3839878 Start */
553 ELSIF p_dimension_name LIKE '%LTD' THEN
554 p_expiry_information := fnd_date.canonical_to_date('4712/12/31');
555 /* Changes for 3839878 End */
556 ELSE
557 hr_utility.set_message(801,'NO_EXP_CHECK_FOR_DIMENSION');
558 hr_utility.raise_error;
559
560 END IF;
561
562 IF g_debug THEN
563 pay_in_utils.trace('**************************************************','********************');
564 pay_in_utils.trace('Expiry Information ',p_expiry_information);
565 pay_in_utils.trace('**************************************************','********************');
566 END IF;
567
568 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
569
570 END date_ec;
571
572 PROCEDURE cal_hy_start(p_effective_date IN DATE ,
573 p_start_date OUT NOCOPY DATE,
574 p_start_date_code IN VARCHAR2 DEFAULT NULL,
575 p_payroll_id IN NUMBER DEFAULT NULL,
576 p_bus_grp IN NUMBER DEFAULT NULL,
577 p_action_type IN VARCHAR2 DEFAULT NULL,
578 p_asg_action IN NUMBER DEFAULT NULL)
579 AS
580
581 l_year NUMBER(4);
582 l_month NUMBER(2);
583 l_half_start1 CONSTANT VARCHAR2(6):='01-01-';
584 l_half_start2 CONSTANT VARCHAR2(6):= '01-07-';
585 l_procedure VARCHAR2(100);
586 l_message VARCHAR2(255);
590 l_procedure := g_package ||'cal_hy_start';
587
588 BEGIN
589 g_debug := hr_utility.debug_enabled;
591 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
592
593 p_start_date :=NULL;
594 l_year := TO_NUMBER (TO_CHAR(p_effective_date,'yyyy'));
595 l_month := TO_NUMBER (TO_CHAR(p_effective_date,'mm'));
596
597 IF l_month BETWEEN 1 and 6 THEN
598 p_start_date:=TO_DATE(l_half_start1||TO_CHAR(l_year),'DD-MM-YYYY');
599 ELSE
600 p_start_date:=TO_DATE(l_half_start2||TO_CHAR(l_year),'DD-MM-YYYY');
601 END IF;
602
603 IF g_debug THEN
604 pay_in_utils.trace('**************************************************','********************');
605 pay_in_utils.trace('Effective Date ',p_effective_date);
606 pay_in_utils.trace('Start Date ',p_start_date);
607 pay_in_utils.trace('**************************************************','********************');
608 END IF;
609
610 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
611
612 END;
613
614
615
616
617 PROCEDURE prov_ytd_start(p_effective_date IN DATE ,
618 p_start_date OUT NOCOPY DATE,
619 p_start_date_code IN VARCHAR2 DEFAULT NULL,
620 p_payroll_id IN NUMBER DEFAULT NULL,
621 p_bus_grp IN NUMBER DEFAULT NULL,
622 p_action_type IN VARCHAR2 DEFAULT NULL,
623 p_asg_action IN NUMBER DEFAULT NULL)
624 AS
625
626 l_year NUMBER(4);
627 l_message VARCHAR2(255);
628 l_procedure VARCHAR2(100);
629
630 BEGIN
631 g_debug := hr_utility.debug_enabled;
632 l_procedure := g_package ||'prov_ytd_start';
633 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
634
635 p_start_date :=NULL;
636 l_year := TO_NUMBER (TO_CHAR(p_effective_date,'yyyy'));
637
638 IF p_effective_date>=TO_DATE('01-03-'||TO_CHAR(l_year),'DD-MM-YYYY') THEN
639 p_start_date := TO_DATE('01-03-'||TO_CHAR(l_year),'DD-MM-YYYY');
640 ELSE
641 p_start_date := TO_DATE('01-03-'||TO_CHAR(l_year-1),'DD-MM-YYYY');
642 END IF;
643
644 IF g_debug THEN
645 pay_in_utils.trace('**************************************************','********************');
646 pay_in_utils.trace('Effective Date ',p_effective_date);
647 pay_in_utils.trace('Start Date ',p_start_date);
648 END IF;
649
650 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
651
652 END;
653
654 PROCEDURE hytd_start(p_effective_date IN DATE ,
655 p_start_date OUT NOCOPY DATE,
656 p_start_date_code IN VARCHAR2 DEFAULT NULL,
657 p_payroll_id IN NUMBER DEFAULT NULL,
658 p_bus_grp IN NUMBER DEFAULT NULL,
659 p_action_type IN VARCHAR2 DEFAULT NULL,
660 p_asg_action IN NUMBER DEFAULT NULL)
661 AS
662
663 l_year NUMBER(4);
664 l_month NUMBER(2);
665 l_half_start1 CONSTANT VARCHAR2(6):='01-04-';
666 l_half_start2 CONSTANT VARCHAR2(6):= '01-10-';
667 l_procedure VARCHAR2(100);
668 l_message VARCHAR2(255);
669
670 BEGIN
671 g_debug := hr_utility.debug_enabled;
672 l_procedure := g_package ||'hytd_start';
673 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
674
675 p_start_date :=NULL;
676 l_year := TO_NUMBER (TO_CHAR(p_effective_date,'yyyy'));
677 l_month := TO_NUMBER (TO_CHAR(p_effective_date,'mm'));
678
679 IF l_month BETWEEN 4 and 9 THEN
680 p_start_date:=TO_DATE(l_half_start1||TO_CHAR(l_year),'DD-MM-YYYY');
681 ELSIF l_month BETWEEN 10 and 12 THEN
682 p_start_date:=TO_DATE(l_half_start2||TO_CHAR(l_year),'DD-MM-YYYY');
683 ELSE
684 p_start_date:=TO_DATE(l_half_start2||TO_CHAR(l_year-1),'DD-MM-YYYY');
685 END IF;
686
687 IF g_debug THEN
688 pay_in_utils.trace('**************************************************','********************');
689 pay_in_utils.trace('Effective Date ',p_effective_date);
690 pay_in_utils.trace('Start Date ',p_start_date);
691 pay_in_utils.trace('**************************************************','********************');
692 END IF;
693
694 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
695
696 END;
697
698
699
700 --------------------------------------------------------------------------
701 -- --
702 -- Name : START_CODE_PMTH --
703 -- Type : PROCEDURE --
704 -- Access : Public --
705 -- Description : This procedure finds the start date based on the --
706 -- effective date for the dimension name _ASG_PMTH --
707 -- --
708 -- Parameters : --
709 -- IN : p_effective_date DATE --
710 -- p_payroll_id NUMBER --
711 -- p_bus_grp NUMBER --
712 -- p_asg_action NUMBER --
716 -- Change History : --
713 -- OUT : p_start_date DATE --
714 -- RETURN : N/A --
715 -- --
717 --------------------------------------------------------------------------
718 -- Rev# Date Userid Description --
719 --------------------------------------------------------------------------
720 -- 1.0 15-Sep-2004 abhjain Created the procedure --
721 --------------------------------------------------------------------------
722 PROCEDURE start_code_pmth ( p_effective_date IN DATE
723 , p_start_date OUT NOCOPY DATE
724 , p_payroll_id IN NUMBER
725 , p_bus_grp IN NUMBER
726 , p_asg_action IN NUMBER
727 )
728 IS
729 l_procedure VARCHAR2(100);
730 l_message VARCHAR2(255);
731 BEGIN
732 g_debug := hr_utility.debug_enabled;
733 l_procedure := g_package ||'start_code_pmth';
734 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
735
736 p_start_date := last_day(add_months(p_effective_date, -2))+1;
737
738 IF g_debug THEN
739 pay_in_utils.trace('**************************************************','********************');
740 pay_in_utils.trace('Effective Date ',p_effective_date);
741 pay_in_utils.trace('Start Date ',p_start_date);
742 pay_in_utils.trace('**************************************************','********************');
743 END IF;
744
745 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
746
747
748 END start_code_pmth;
749
750 --------------------------------------------------------------------------
751 -- --
752 -- Name : START_CODE_P10MTH --
753 -- Type : PROCEDURE --
754 -- Access : Public --
755 -- Description : This procedure finds the start date based on the --
756 -- effective date for the dimension name _ASG_P10MTH --
757 -- --
758 -- Parameters : --
759 -- IN : p_effective_date DATE --
760 -- p_payroll_id NUMBER --
761 -- p_bus_grp NUMBER --
762 -- p_asg_action NUMBER --
763 -- OUT : p_start_date DATE --
764 -- RETURN : N/A --
765 -- --
766 -- Change History : --
767 --------------------------------------------------------------------------
768 -- Rev# Date Userid Description --
769 --------------------------------------------------------------------------
770 -- 1.0 14-Oct-2004 lnagaraj Created the procedure --
771 --------------------------------------------------------------------------
772 PROCEDURE start_code_p10mth ( p_effective_date IN DATE
773 , p_start_date OUT NOCOPY DATE
774 , p_payroll_id IN NUMBER
775 , p_bus_grp IN NUMBER
776 , p_asg_action IN NUMBER
777 )
778 IS
779 l_procedure VARCHAR2(100);
780 l_message VARCHAR2(255);
781 BEGIN
782 g_debug := hr_utility.debug_enabled;
783 l_procedure := g_package ||'start_code_p10mth';
784 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
785
786 p_start_date := last_day(add_months(p_effective_date, -11))+1;
787
788 IF g_debug THEN
789 pay_in_utils.trace('**************************************************','********************');
790 pay_in_utils.trace('Effective Date ',p_effective_date);
791 pay_in_utils.trace('Start Date ',p_start_date);
792 pay_in_utils.trace('**************************************************','********************');
793 END IF;
794 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 20);
795
796 END start_code_p10mth;
797
798
799 END pay_in_exc;
[Home] [Help]