[Home] [Help]
PACKAGE BODY: APPS.HR_GCC_HIJRAH_FUNCTIONS
Source
1 package body hr_gcc_hijrah_functions as
2 /* $Header: pegcchjf.pkb 120.0 2005/05/31 09:16:13 appldev noship $ */
3
4 function hijrah_to_gregorian
5 (p_input_date in varchar2)
6 return varchar2 as
7 l_year number := 0;
8 l_month number := 0;
9 l_day number := 0;
10 l_fourtons number := 0;
11 l_tons number := 0;
12 l_leaps number := 0;
13 l_norm_yrs number := 0;
14 l_bal1 number := 0;
15 l_bal2 number := 0;
16 l_bal3 number := 0;
17 l_bal4 number := 0;
18 l_cnt1 number := 0;
19 l_cnt2 number := 0;
20 l_days_elapsed number := 0;
21 l_hij_year number := 0;
22 l_hij_month number := 0;
23 l_hij_day number := 0;
24 l_date date;
25 l_input_date varchar2(10);
26 begin
27 if p_input_date is not null then
28 hr_gcc_hijrah_functions.validate_date(p_input_date,l_input_date);
29 l_hij_year := to_number(substr(l_input_date,1,4)) - 1;
30 l_hij_month := to_number(substr(l_input_date,6,2)) - 1;
31 l_hij_day := to_number(substr(l_input_date,9,2)) ;
32 l_cnt1 := floor(l_hij_year * 354.36887);
33 l_cnt2 := floor(l_hij_month * 29.5305);
34 l_days_elapsed := 227011 + l_cnt1 + l_cnt2 + l_hij_day;
35 l_fourtons := floor(l_days_elapsed/146097);
36 l_bal1 := mod(l_days_elapsed,146097);
37 l_tons := floor(l_bal1/36524);
38 l_bal2 := mod(l_bal1,36524);
39 l_leaps := floor(l_bal2/1461);
40 l_bal3 := mod(l_bal2,1461);
41 l_norm_yrs := floor(l_bal3/365);
42 l_bal4 := floor(mod(l_bal3,365));
43 l_year := (l_fourtons * 400) + (l_tons * 100) + (l_leaps * 4) + l_norm_yrs + 1;
44 l_date := to_date(l_year||'/01/01','YYYY/MM/DD') + l_bal4;
45 return(to_char(l_date,'YYYY/MM/DD'));
46 else
47 return null;
48 end if;
49 end hijrah_to_gregorian;
50
51 function gregorian_to_hijrah
52 (p_input_date in date)
53 return varchar2 as
54 l_year number := 0;
55 l_month number := 0;
56 l_day number := 0;
57 l_fourtons number := 0;
58 l_tons number := 0;
59 l_leaps number := 0;
60 l_bal1 number := 0;
61 l_bal2 number := 0;
62 l_bal3 number := 0;
63 l_cnt1 number := 0;
64 l_cnt2 number := 0;
65 l_cnt3 number := 0;
66 l_cnt4 number := 0;
67 l_hij_count number := 0;
68 l_days_elapsed number := 0;
69 l_days_curr_year number := 0;
70 l_hij_year number := 0;
71 l_hij_month number := 0;
72 l_hij_day number := 0;
73 l_hij_bal1 number := 0;
74 l_hij_bal2 number := 0;
75 l_hij_date varchar2(30);
76 l_input_date varchar2(10);
77 begin
78 if p_input_date is not null then
79 l_input_date := to_char (p_input_date,'RRRR-MM-DD');
80 l_year := to_number(substr(l_input_date,1,4)) - 1;
81 l_month := to_number(substr(l_input_date,6,2)) - 1;
82 l_day := to_number(substr(l_input_date,9,2));
83 l_fourtons := floor(l_year/400);
84 l_bal1 := mod(l_year,400);
85 l_cnt1 := l_fourtons * 146097;
86 l_tons := floor(l_bal1/100);
87 l_bal2 := mod(l_bal1,100);
88 l_cnt2 := l_tons * 36524;
89 l_leaps := floor(l_bal2/4);
90 l_bal3 := mod(l_bal2,4);
91 l_cnt3 := l_leaps * 1461;
92 l_cnt4 := l_bal3 * 365;
93 l_days_curr_year := (to_date(l_input_date,'YYYY/MM/DD') - to_date(substr(l_input_date,1,4)||'/01/01','YYYY/MM/DD'));
94 l_days_elapsed := l_cnt1 + l_cnt2 + l_cnt3 + l_cnt4 + l_days_curr_year;
95 l_hij_count := l_days_elapsed - 227011;
96 l_hij_year := floor(l_hij_count/354.36887) + 1;
97 l_hij_bal1 := mod(l_hij_count,354.36887);
98 l_hij_month := floor(l_hij_bal1/29.5305) + 1;
99 /* Fix for bug 4024967 */
100 if(l_hij_month > 12) then
101 l_hij_year := l_hij_year + 1;
102 l_hij_month := 1;
103 end if;
104 /* End of fix for bug 4024967 */
105 l_hij_bal2 := mod(l_hij_bal1,29.5305);
106 l_hij_day := floor(l_hij_bal2) + 1;
107 l_hij_date := lpad(l_hij_year,4,'0')||'/'||lpad(l_hij_month,2,'0')||'/'||lpad(l_hij_day,2,'0');
108 return (l_hij_date);
109 else
110 return null;
111 end if;
112 end gregorian_to_hijrah;
113
114 function add_days
115 (p_input_date in varchar2
116 ,p_num in number)
117 return varchar2 as
118 l_greg_date date;
119 l_new_greg_date date;
120 l_new_hij_date varchar2(10);
121 l_input_date varchar2(10);
122 begin
123 hr_gcc_hijrah_functions.validate_date(p_input_date,l_input_date);
124 l_greg_date := to_date(hr_gcc_hijrah_functions.hijrah_to_gregorian(l_input_date),'YYYY/MM/DD');
125 l_new_greg_date := l_greg_date + p_num;
126 l_new_hij_date := hr_gcc_hijrah_functions.gregorian_to_hijrah(l_new_greg_date);
127 return (l_new_hij_date);
128 end add_days;
129
130 function days_between
131 (p_high_date in varchar2
132 ,p_low_date in varchar2)
133 return number as
134 l_high_greg_date date;
135 l_low_greg_date date;
136 l_diff number;
137 l_high_date varchar2(10);
138 l_low_date varchar2(10);
139 begin
140 hr_gcc_hijrah_functions.validate_date(p_high_date,l_high_date);
141 hr_gcc_hijrah_functions.validate_date(p_low_date,l_low_date);
142 l_high_greg_date := to_date(hr_gcc_hijrah_functions.hijrah_to_gregorian(l_high_date),'YYYY/MM/DD');
143 l_low_greg_date := to_date(hr_gcc_hijrah_functions.hijrah_to_gregorian(l_low_date),'YYYY/MM/DD');
144 l_diff := l_high_greg_date - l_low_greg_date;
145 return(l_diff);
146 end days_between;
147
148 function get_day
149 (p_input_date in varchar2)
150 return varchar2 as
151 l_day varchar2(30);
152 l_greg_date date;
153 l_input_date varchar2(10);
154 begin
155 hr_gcc_hijrah_functions.validate_date(p_input_date,l_input_date);
156 l_greg_date := to_date(hr_gcc_hijrah_functions.hijrah_to_gregorian(l_input_date),'YYYY/MM/DD');
157 select ltrim(rtrim(to_char(l_greg_date,'D')))
158 into l_day
159 from dual;
160 return(hr_general.decode_lookup('GCC_HIJRAH_DAYS',l_day));
161 end get_day;
162
163 function get_month
164 (p_input_date in varchar2)
165 return varchar2 as
166 l_month number;
167 l_greg_date date;
168 l_input_date varchar2(10);
169 begin
170 hr_gcc_hijrah_functions.validate_date(p_input_date,l_input_date);
171 l_month := to_number(substr(l_input_date,6,2));
172 return(hr_general.decode_lookup('GCC_HIJRAH_MONTHS',l_month));
173 end get_month;
174
175 function get_weekday
176 (p_input_date in varchar2)
177 return number as
178 l_day varchar2(10);
179 l_greg_date date;
180 l_input_date varchar2(10);
181 begin
182 hr_gcc_hijrah_functions.validate_date(p_input_date,l_input_date);
183 l_greg_date := to_date(hr_gcc_hijrah_functions.hijrah_to_gregorian(l_input_date),'YYYY/MM/DD');
184 select ltrim(rtrim(to_char(l_greg_date,'D')))
185 into l_day
186 from dual;
187 return(l_day);
188 end get_weekday;
189
190 function get_yearday
191 (p_input_date in varchar2)
192 return number as
193 l_first_date varchar2(10);
194 l_input_date varchar2(10);
195 begin
196 hr_gcc_hijrah_functions.validate_date(p_input_date,l_input_date);
197 l_first_date := substr(l_input_date,1,4)||'/01/01';
198 return(days_between(l_input_date,l_first_date));
199 end get_yearday;
200
201 procedure validate_date
202 (p_input_date in varchar2,
203 p_output_date out nocopy varchar2)
204 as
205 l_date_in varchar2(100);
206 l_field varchar2(100);
207 l_range varchar2(100);
208 l_length number;
209 l_position1 number default 0;
210 l_position2 number default 0;
211 l_error number;
212 l_year number;
213 l_month number;
214 l_day number;
215 l_year_date varchar2(10);
216 l_month_date varchar2(10);
217 l_day_date varchar2(10);
218 l_flag number := 0;
219
220
221 begin
222 l_length := length(p_input_date);
223
224 l_date_in := translate(p_input_date,'.-',
225 '//'); --change every special character to '/'
226 l_position1 := instr(l_date_in,'/',1,1);
227 l_position2 := instr(l_date_in,'/',1,2);
228 if( (instr(l_date_in,'/',1,3) <> 0) or (l_position1 = 0) )then
229 l_error :=1;
230 end if;
231 if(l_error = 1) then
232 l_field := hr_general.decode_lookup('KW_FORM_LABELS','HIJRAH_DATE');
233 l_range := hr_general.decode_lookup('KW_FORM_LABELS','INVALID_FORMAT');
234 hr_utility.set_message(800, 'HR_375412_KW_INVALID_DATE');
235 hr_utility.set_message_token('FIELD',l_field);
236 hr_utility.set_message_token('RANGE',l_range);
237 hr_utility.raise_error;
238 end if;
239 l_year := to_number(substr(l_date_in,0,l_position1-1));
240 l_month := to_number(substr(l_date_in,l_position1+1,l_position2-l_position1-1));
241 l_day := to_number(substr(l_date_in,l_position2+1,l_length-l_position2));
242
243 if(l_year<=0 or l_year >4089) then --check if year/month/day is negative
244 l_flag := 1;
245 l_field := hr_general.decode_lookup('KW_FORM_LABELS','HIJRAH_YEAR');
246 l_range := hr_general.decode_lookup('KW_FORM_LABELS','YEAR_RANGE');
247 hr_utility.set_message(800, 'HR_375412_KW_INVALID_DATE');
248 hr_utility.set_message_token('FIELD',l_field);
249 hr_utility.set_message_token('RANGE',l_range);
250 hr_utility.raise_error;
251 end if;
252 if(l_month <= 0 or l_month> 12)then
253 l_flag := 2;
254 l_field := hr_general.decode_lookup('KW_FORM_LABELS','HIJRAH_MONTH');
255 l_range := hr_general.decode_lookup('KW_FORM_LABELS','MONTH_RANGE');
256 hr_utility.set_message(800, 'HR_375412_KW_INVALID_DATE');
257 hr_utility.set_message_token('FIELD',l_field);
258 hr_utility.set_message_token('RANGE',l_range);
259 hr_utility.raise_error;
260 end if;
261 if(l_day <= 0 or l_day >30)then
262 l_flag := 3;
263 l_field := hr_general.decode_lookup('KW_FORM_LABELS','HIJRAH_DATE');
264 l_range := hr_general.decode_lookup('KW_FORM_LABELS','DATE_RANGE');
265 hr_utility.set_message(800, 'HR_375412_KW_INVALID_DATE');
266 hr_utility.set_message_token('FIELD',l_field);
267 hr_utility.set_message_token('RANGE',l_range);
268 hr_utility.raise_error;
269 end if;
270 if(l_year < 1000) then
271 l_year_date := lpad(to_char(l_year),4,'0');
272 else
273 l_year_date := to_char(l_year);
274 end if;
275 if(l_month < 10) then
276 l_month_date := lpad(to_char(l_month),2,'0');
277 else
278 l_month_date := to_char(l_month);
279 end if;
280 if(l_day < 10) then
281 l_day_date := lpad(to_char(l_day),2,'0');
282 else
283 l_day_date := to_char(l_day);
284 end if;
285
286 p_output_date := l_year_date || '/' || l_month_date || '/' || l_day_date;
287
288 EXCEPTION
289 WHEN OTHERS
290 then
291 if l_flag = 0 then
292 l_field := hr_general.decode_lookup('KW_FORM_LABELS','HIJRAH_DATE');
293 l_range := hr_general.decode_lookup('KW_FORM_LABELS','INVALID_FORMAT');
294 hr_utility.set_message(800, 'HR_375412_KW_INVALID_DATE');
295 hr_utility.set_message_token('FIELD',l_field);
296 hr_utility.set_message_token('RANGE',l_range);
297 hr_utility.raise_error;
298 elsif l_flag = 1 then
299 l_field := hr_general.decode_lookup('KW_FORM_LABELS','HIJRAH_YEAR');
300 l_range := hr_general.decode_lookup('KW_FORM_LABELS','YEAR_RANGE');
301 hr_utility.set_message(800, 'HR_375412_KW_INVALID_DATE');
302 hr_utility.set_message_token('FIELD',l_field);
303 hr_utility.set_message_token('RANGE',l_range);
304 hr_utility.raise_error;
305 elsif l_flag = 2 then
306 l_field := hr_general.decode_lookup('KW_FORM_LABELS','HIJRAH_MONTH');
307 l_range := hr_general.decode_lookup('KW_FORM_LABELS','MONTH_RANGE');
308 hr_utility.set_message(800, 'HR_375412_KW_INVALID_DATE');
309 hr_utility.set_message_token('FIELD',l_field);
310 hr_utility.set_message_token('RANGE',l_range);
311 hr_utility.raise_error;
312 elsif l_flag = 3 then
313 l_field := hr_general.decode_lookup('KW_FORM_LABELS','HIJRAH_DATE');
314 l_range := hr_general.decode_lookup('KW_FORM_LABELS','DATE_RANGE');
315 hr_utility.set_message(800, 'HR_375412_KW_INVALID_DATE');
316 hr_utility.set_message_token('FIELD',l_field);
317 hr_utility.set_message_token('RANGE',l_range);
318 hr_utility.raise_error;
319 end if;
320
321 end validate_date;
322
323 end hr_gcc_hijrah_functions;