1 PACKAGE BODY pay_us_get_cumu_period_num AS
2 /* $Header: pyuscfun.pkb 120.0 2005/05/29 09:18:44 appldev noship $ */
3
4
5 /*************************************************************************
6
7 FUNCTION CUMU_PERIOD_NUM
8
9 This function first gets the heir date and payroll id for assignment_id
10 and date earned.It compares the heir date with the date earned if it is
11 in the same year then we need to get the period number as of the heir date
12 and as of the date earned and calculate the difference else we get the
13 first period number for the current year.
14
15 *************************************************************************/
16
17 FUNCTION cumulative_period_number(
18 p_pact_id number,
19 p_date_earned date,
20 p_assignment_id number)
21 RETURN NUMBER IS
22
23 l_hire_date date;
24 l_payroll_id number;
25 l_period_number number;
26 l_hire_period_number number;
27 l_earned_period_number number;
28
29 l_date_earned date;
30 l_start_date date;
31 l_end_date date;
32 l_date_paid date;
33 l_period_type per_time_periods.period_type%type;
34 l_days_since_hired number;
35 l_frequency number;
36
37 BEGIN -- main
38 begin --main 2
39 -- hr_utility.trace_on(null,'oracle');
40
41 hr_utility.trace('The Assignment Id is: '|| p_assignment_id);
42 hr_utility.trace('The Date Earned is: '|| p_date_earned);
43 hr_utility.trace('The PACT_ID is: '|| p_pact_id);
44
45 -- get the period number, payroll id, period start date , period end date,
46 -- date paid and period type
47 begin
48 select ptp.period_num, ppa.payroll_id, ptp.start_date,
49 ptp.end_date,ppa.effective_date date_paid, ptp.period_type
50 into l_period_number,l_payroll_id, l_start_date,
51 l_end_date,l_date_paid, l_period_type
52 from per_time_period_types pty,
53 per_time_periods ptp,
54 pay_payroll_actions ppa
55 where ppa.payroll_action_id = p_pact_id
56 and ptp.time_period_id = ppa.time_period_id
57 and pty.period_type = ptp.period_type;
58
59 hr_utility.trace('The Period number is: '|| l_period_number);
60 hr_utility.trace('The date paid is: '|| l_date_paid);
61
62 exception when others then
63
64 hr_utility.trace('In exception of start period number'||substr(SQLERRM,1,80));
65 -- returning 0 now, but will change later on to have a meaningful message here
66 return(0);
67 end;
68
69 -- get the minimum hire date
70 begin
71 select min(SERVICE.date_start) into l_hire_date
72 from per_periods_of_service SERVICE,
73 per_all_assignments_f ASS
74 where ASS.assignment_id = p_assignment_id
75 and ASS.person_id = SERVICE.person_id
76 and SERVICE.date_start <= l_date_paid;
77 exception when others then
78 hr_utility.trace('In exception of hire date'||substr(SQLERRM,1,80));
79 -- returning 0 now, but will change later on to have a meaningful message here
80 return(0);
81
82 end;
83 --is hire date and date paid in the same year
84
85 if trunc(l_date_paid,'YYYY') = trunc(l_hire_date,'YYYY') then
86 -- ie. the year of hire date and date paid is same
87 --
88 -- get the period number as of the hire date
89 begin
90 l_earned_period_number := l_period_number; /*Move the l_period_number found above*/
91
92
93 -- following query can raise an exception if the payroll is not defined as of the hire date
94 select ptp.period_num into l_period_number
95 from per_time_periods ptp
96 where ptp.payroll_id = l_payroll_id
97 and l_hire_date between ptp.start_date and ptp.end_date;
98
99 /* Return the difference in the paid period number and the hired period number */
100 l_period_number := l_earned_period_number;
101
102
103 hr_utility.trace('The Period number is: '|| l_period_number);
104 exception when others then
105 -- get the difference in number of day between hire date and date paid
106 -- we use trunc to remove the decimal places
107 --** need to confirm for the use of l_start_date or l_end_date
108 -- l_days_since_hired := trunc( l_start_date - l_hire_date );
109 -- think should use end date instead of start date to take into account the
110 -- current pay period.
111
112 l_days_since_hired := trunc( l_end_date - l_hire_date );
113 hr_utility.trace('Days Since Hired: '|| l_days_since_hired);
114
115 -- get the frequency of the payroll
116 IF l_period_type = 'Semi-Month' THEN
117 hr_utility.trace('Period type 1'||l_period_type);
118 l_frequency := 15;
119
120 ELSIF l_period_type = 'Calendar Month' THEN
121 hr_utility.trace('Period type 2'||l_period_type);
122 l_frequency := 30;
123 ELSIF l_period_type = 'Bi-Month' THEN
124 hr_utility.trace('Period type 3'||l_period_type);
125 l_frequency := 60;
126
127 ELSE
128 hr_utility.trace('Period type 4'||l_period_type);
129 l_frequency := trunc(l_end_date - l_start_date) + 1;
130 END IF;
131 hr_utility.trace('Frequency : '||l_frequency);
132 -- find the period number
133 l_period_number := trunc(l_days_since_hired/l_frequency);
134 end;
135 end if;
136 hr_utility.trace_off;
137 return(l_period_number);
138 --
139
140 END cumulative_period_number; -- main2
141
142 exception when others then
143
144 hr_utility.trace('final exception');
145 -- returning 0 now, but will change later on to have a meaningful message here
146 return(0);
147 -- null;
148 END;
149 END pay_us_get_cumu_period_num;
150
[Home] [Help]