[Home] [Help]
PACKAGE BODY: APPS.HR_GENERIC_UTIL
Source
1 PACKAGE BODY hr_generic_util AS
2 /* $Header: pygenutl.pkb 120.1 2005/10/04 06:31:05 rchennur noship $ */
3
4 --
5 WEEKLY CONSTANT varchar2(1) := 'W';
6 MONTHLY CONSTANT varchar2(1) := 'M';
7 SEMIMONTHLY CONSTANT varchar2(1) := 'S';
8 --
9 --------------------------- get_period_details ---------------------------
10 --
11 PROCEDURE get_period_details (p_proc_period_type IN VARCHAR2,
12 p_base_period_type OUT NOCOPY VARCHAR2,
13 p_multiple OUT NOCOPY NUMBER) IS
14 --
15 l_no_periods per_time_period_types.number_per_fiscal_year%type;
16 --
17 l_proc VARCHAR2(100) := 'hr_generic_util.GET_PERIOD_DETAILS';
18 --
19 BEGIN
20 --
21 hr_utility.set_location(l_proc, 10);
22 --
23 SELECT number_per_fiscal_year
24 INTO l_no_periods
25 FROM per_time_period_types
26 WHERE period_type = p_proc_period_type;
27 --
28 hr_utility.set_location(l_proc, 20);
29 --
30 -- Use the number of periods in a fiscal year to deduce the base
31 -- period and multiple.
32 --
33 IF l_no_periods = 1 THEN -- Yearly
34 p_base_period_type := MONTHLY;
35 p_multiple := 12;
36 ELSIF l_no_periods = 2 THEN -- Semi yearly
37 p_base_period_type := MONTHLY;
38 p_multiple := 6;
39 ELSIF l_no_periods = 4 THEN -- Quarterly
40 p_base_period_type := MONTHLY;
41 p_multiple := 3;
42 ELSIF l_no_periods = 6 THEN -- Bi monthly
43 p_base_period_type := MONTHLY;
44 p_multiple := 2;
45 ELSIF l_no_periods = 12 THEN -- Monthly
46 p_base_period_type := MONTHLY;
47 p_multiple := 1;
48 ELSIF l_no_periods = 13 THEN -- Lunar monthly
49 p_base_period_type := WEEKLY;
50 p_multiple := 4;
51 ELSIF l_no_periods = 24 THEN -- Semi monthly
52 p_base_period_type := SEMIMONTHLY;
53 p_multiple := 1; -- Not used for semi-monthly
54 ELSIF l_no_periods = 26 THEN -- Fortnightly
55 p_base_period_type := WEEKLY;
56 p_multiple := 2;
57 ELSIF l_no_periods = 52 THEN -- Weekly
58 p_base_period_type := WEEKLY;
59 p_multiple := 1;
60 ELSE
61 -- Unknown period type.
62 hr_utility.set_message(801, 'PAY_6601_PAYROLL_INV_PERIOD_TP');
63 hr_utility.raise_error;
64 END IF;
65 --
66 hr_utility.set_location(l_proc, 30);
67 --
68 END get_period_details;
69 --
70 --
71 --------------------------- next_semi_month ----------------------------
72 --
73 -- Locally defined function that, given the end-date of a semi-month
74 -- period and the first period's end-date (p_fpe_date) returns
75 -- the end date of the following semi-monthly period.
76 --
77 FUNCTION next_semi_month(p_semi_month_date IN DATE,
78 p_fpe_date IN DATE) return DATE IS
79 --
80 day_of_month varchar2(2);
81 last_of_month date;
82 temp_day varchar2(2);
83 --
84 l_proc VARCHAR2(100) := 'hr_generic_util.next_semi_month';
85 --
86 BEGIN
87 --
88 hr_utility.set_location(l_proc, 1);
89 --
90 day_of_month := substr(to_char(p_fpe_date, 'DD-MM-YYYY'), 1, 2);
91 --
92 IF (day_of_month = '15') OR (last_day(p_fpe_date) = p_fpe_date) THEN
93 --
94 -- The first period's end-date is either the 15th or the end-of-month
95 --
96 IF last_day(p_semi_month_date) = p_semi_month_date THEN
97 -- End of month: add 15 days
98 return(p_semi_month_date + 15);
99 ELSE
100 -- 15th of month: return last day
101 return(last_day(p_semi_month_date));
102 END IF;
103 ELSE
104 -- The first period's end-date is neither the 15th nor the end-of-month
105 -- temp_day = smaller of the 2 day numbers used to calc period end-dates
106 --
107 temp_day := day_of_month ;
108 IF temp_day > '15' THEN
109 temp_day := substr(to_char(p_fpe_date - 15, 'DD-MM-YYYY'), 1, 2);
110 END IF;
111 --
112 day_of_month := substr(to_char(p_semi_month_date, 'DD-MON-YYYY'), 1, 2);
113 IF day_of_month between '01' AND '15' THEN
114 IF last_day(p_semi_month_date+15) = last_day(p_semi_month_date) THEN
115 return(p_semi_month_date + 15);
116 ELSE
117 -- for p_semi_month_date = Feb 14th, for example
118 return(last_day(p_semi_month_date));
119 END IF;
120 ELSE -- if on the 16th or later
121 return(to_date((temp_day ||
122 substr(to_char(add_months(p_semi_month_date,1),'DD-MM-YYYY'),3)
123 ), 'DD-MM-YYYY'));
124 END IF;
125 END IF;
126 --
127 END next_semi_month;
128 --
129 --------------------------- add_multiple_of_base ----------------------------
130 --
131 FUNCTION add_multiple_of_base (p_target_date IN DATE,
132 p_base_period_type IN VARCHAR2,
133 p_multiple IN NUMBER,
134 p_fpe_date IN DATE)
135 return DATE IS
136 --
137 rest_of_date VARCHAR2(9);
138 temp_date DATE;
139 --
140 l_proc VARCHAR2(100) := 'hr_generic_util.ADD_MULTIPLE_OF_BASE';
141 --
142 BEGIN
143 --
144 -- Errors can occur when performing date manipulation.
145 --
146 IF p_base_period_type = WEEKLY THEN
147 --
148 -- hr_utility.set_location(l_proc, 10);
149 --
150 return (p_target_date + (7 * p_multiple));
151 --
152 ELSIF p_base_period_type = MONTHLY THEN
153 --
154 -- hr_utility.set_location(l_proc, 20);
155 --
156 return (add_months(p_target_date, p_multiple));
157 --
158 ELSE
159 -- This is semi-monthly. A pair of semi-months always spand
160 -- a whole calendar month. Their start and end dates are either
161 -- 1st - 15th or 16th - last day of month. This makes the
162 -- addition/subtraction of a period reasonably straightforward,
163 -- if a little involved.
164 -- IF p_multiple > 0 THEN
165 -- Addition of one semi-month.
166 --
167 return(next_semi_month(p_target_date, p_fpe_date));
168 --
169 -- ELSE
170 -- Substraction of one semi-month.
171 -- return(prev_semi_month(p_target_date, p_fpe_date));
172 -- END IF;
173 --
174 END IF;
175 --
176 END add_multiple_of_base;
177 --
178 ------------------------- get_period_dates --------------------------------
179 --
180 PROCEDURE get_period_dates
181 (p_rec_period_start_date IN DATE
182 ,p_period_type IN VARCHAR2
183 ,p_current_date IN DATE
184 ,p_period_start_date OUT NOCOPY DATE
185 ,p_period_end_date OUT NOCOPY DATE) IS
186 --
187 l_base_period_type VARCHAR2(1);
188 l_multiple NUMBER;
189 l_period_start_date DATE;
190 l_period_end_date DATE;
191 --
192 l_proc VARCHAR2(100):= 'hr_generic_util.GET_PERIOD_DATES';
193 --
194 --
195 BEGIN
196 --
197
198 hxc_period_evaluation.period_start_stop(p_current_date => p_current_date,
199 p_rec_period_start_date => p_rec_period_start_date,
200 l_period_start => p_period_start_date,
201 l_period_end => p_period_end_date,
202 l_base_period_type => p_period_type);
203
204
205 return;
206
207 hr_utility.set_location(l_proc, 10);
208 --
209 IF p_rec_period_start_date > p_current_date THEN
210 hr_utility.set_message(809, 'HXC_APR_REC_DATE_LATER');
211 hr_utility.raise_error;
212 END IF;
213 --
214 get_period_details(p_period_type,
215 l_base_period_type,
216 l_multiple);
217 --
218 hr_utility.set_location(l_proc, 20);
219 --
220 l_period_start_date := p_rec_period_start_date;
221 l_period_end_date := add_multiple_of_base(l_period_start_date - 1,
222 l_base_period_type,
223 l_multiple,
224 l_period_start_date - 1);
225 --
226
227
228 LOOP
229 --
230 EXIT when p_current_date BETWEEN l_period_start_date AND
231 l_period_end_date;
232 --
233 l_period_start_date := l_period_end_date + 1;
234 l_period_end_date := add_multiple_of_base(l_period_start_date - 1,
235 l_base_period_type,
236 l_multiple,
237 l_period_start_date - 1);
238 END LOOP;
239
240 --
241 hr_utility.set_location(l_proc, 70);
242 --
243 p_period_start_date := l_period_start_date;
244 p_period_end_date := l_period_end_date;
245 --
246 hr_utility.set_location(l_proc, 110);
247 --
248 END get_period_dates;
249 --
250 END hr_generic_util;