[Home] [Help]
PACKAGE BODY: APPS.PER_JP_VALIDATIONS
Source
1 PACKAGE BODY per_jp_validations AS
2 /* $Header: pejpvald.pkb 120.1 2005/07/05 03:46:44 shisriva noship $ */
3 FUNCTION CHECK_FORMAT(
4 p_value IN VARCHAR2,
5 p_format IN VARCHAR2) RETURN VARCHAR2
6 IS
7 l_valid BOOLEAN := TRUE;
8 l_char VARCHAR2(2);
9 l_char_seq NUMBER;
10 l_mask VARCHAR2(2);
11 l_mask_seq NUMBER;
12 l_next_mask BOOLEAN := FALSE;
13
14 l_alphabet VARCHAR2(26) := 'abcdefghijklmnopqrstuvwxyz';
15 l_numeric VARCHAR2(10) := '0123456789';
16 l_dummy VARCHAR2(1) := '-';
17 BEGIN
18 l_char_seq := length(p_value);
19 l_mask_seq := nvl(length(p_format),0);
20
21 while l_mask_seq > 0 loop
22 l_mask := substr(p_format,l_mask_seq,1);
23 l_char := substr(p_value,l_char_seq,1);
24
25 -- Incase the character is ommitable.
26 if l_mask in ('9','P','p','C','c') then
27 if l_mask='9' then
28 if translate(l_char,l_dummy || l_numeric,l_dummy) is not NULL then
29 l_next_mask := TRUE;
30 end if;
31 elsif l_mask='P' then
32 if translate(l_char,l_dummy || upper(l_alphabet),l_dummy) is not NULL then
33 l_next_mask := TRUE;
34 end if;
35 elsif l_mask='p' then
36 if translate(l_char,l_dummy || l_alphabet,l_dummy) is not NULL then
37 l_next_mask := TRUE;
38 end if;
39 elsif l_mask='C' then
40 if translate(l_char,l_dummy || upper(l_alphabet) || l_numeric,l_dummy) is not NULL then
41 l_next_mask := TRUE;
42 end if;
43 elsif l_mask='c' then
44 if translate(l_char,l_dummy || l_alphabet || l_numeric,l_dummy) is not NULL then
45 l_next_mask := TRUE;
46 end if;
47 end if;
48 -- Incase the character is in-ommitable.
49 else
50 if l_char is NULL then
51 l_valid := FALSE;
52 exit;
53 end if;
54
55 if l_mask='0' then
56 if translate(l_char,l_dummy || l_numeric,l_dummy) is not NULL then
57 l_valid := FALSE;
58 exit;
59 end if;
60 elsif l_mask='A' then
61 if translate(l_char,l_dummy || upper(l_alphabet),l_dummy) is not NULL then
62 l_valid := FALSE;
63 exit;
64 end if;
65 elsif l_mask='a' then
66 if translate(l_char,l_dummy || l_alphabet,l_dummy) is not NULL then
67 l_valid := FALSE;
68 exit;
69 end if;
70 elsif l_mask='L' then
71 if translate(l_char,l_dummy || upper(l_alphabet) || l_numeric,l_dummy) is not NULL then
72 l_valid := FALSE;
73 exit;
74 end if;
75 elsif l_mask='l' then
76 if translate(l_char,l_dummy || l_alphabet || l_numeric,l_dummy) is not NULL then
77 l_valid := FALSE;
78 exit;
79 end if;
80 else
81 if l_char <> l_mask then
82 l_valid := FALSE;
83 exit;
84 end if;
85 end if;
86 end if;
87
88 l_mask_seq := l_mask_seq - 1;
89 if not l_next_mask then
90 if l_char_seq > 1 then
91 l_char_seq := l_char_seq - 1;
92 else
93 l_char_seq := NULL;
94 end if;
95 else
96 l_next_mask := FALSE;
97 end if;
98 end loop;
99
100 if l_valid then
101 if l_char_seq > 0 then
102 return 'FALSE';
103 else
104 return 'TRUE';
105 end if;
106 else
107 return 'FALSE';
108 end if;
109 END CHECK_FORMAT;
110 --
111 FUNCTION CHECK_DATE_FORMAT(
112 p_value IN VARCHAR2,
113 p_format IN VARCHAR2) RETURN VARCHAR2
114 IS
115 INVALID_FORMAT EXCEPTION;
116 l_message VARCHAR2(255);
117 l_dummy DATE;
118 BEGIN
119 if lengthb(p_value) <> lengthb(p_format) then
120 raise INVALID_FORMAT;
121 else
122 l_dummy := to_date(p_value,p_format);
123 end if;
124
125 l_message := 'TRUE';
126 return l_message;
127 EXCEPTION
128 WHEN INVALID_FORMAT THEN
129 fnd_message.set_name('PER','HR_JP_INVALID_FORMAT');
130 --Bug Fix:3153731, changed the token values of 'VALUE' and 'FORMAT'
131 fnd_message.set_token('VALUE',substr(p_value,1,lengthb(p_value)-2));
132 fnd_message.set_token('FORMAT',substr(p_format,1,6));
133 --
134 l_message := fnd_message.get;
135 return l_message;
136 WHEN OTHERS THEN
137 l_message := SQLERRM;
138 return l_message;
139 END CHECK_DATE_FORMAT;
140 --
141 FUNCTION DISTRICT_CODE_CHECK_DIGIT(
142 p_district_code IN VARCHAR2) RETURN NUMBER
143 IS
144 l_first NUMBER;
145 l_second NUMBER;
146 l_third NUMBER;
147 l_fourth NUMBER;
148 l_fifth NUMBER;
149 l_remainder NUMBER;
150 l_check_digit NUMBER;
151 BEGIN
152 l_first := to_number(substrb(p_district_code,1,1));
153 l_second := to_number(substrb(p_district_code,2,1));
154 l_third := to_number(substrb(p_district_code,3,1));
155 l_fourth := to_number(substrb(p_district_code,4,1));
156 l_fifth := to_number(substrb(p_district_code,5,1));
157
158 l_remainder := mod(l_first*6 + l_second*5 + l_third*4 + l_fourth*3 + l_fifth*2,11);
159
160 if l_remainder = 0 then
161 l_check_digit := 1;
162 elsif l_remainder = 1 then
163 l_check_digit := 0;
164 else
165 l_check_digit := 11 - l_remainder;
166 end if;
167
168 return l_check_digit;
169 END DISTRICT_CODE_CHECK_DIGIT;
170 --
171 FUNCTION DISTRICT_CODE_EXISTS(
172 p_district_code IN VARCHAR2,
173 p_check_digit IN VARCHAR2 DEFAULT 'TRUE') RETURN VARCHAR2
174 IS
175 INVALID_FORMAT EXCEPTION;
176 INVALID_DISTRICT_CODE EXCEPTION;
177 INVALID_CHECK_DIGIT EXCEPTION;
178
179 l_format VARCHAR2(6);
180 l_lengthb NUMBER;
181 l_district_code VARCHAR2(5);
182 l_check_digit NUMBER;
183 l_sixth NUMBER;
184 l_message VARCHAR2(255);
185
186 PROCEDURE CHECK_DISTRICT_CODE(
187 p_district_code IN VARCHAR2)
188 IS
189 l_dummy VARCHAR2(1);
190 CURSOR cur_district_code(p_district_code IN VARCHAR2) IS
191 select 'X'
192 from per_jp_address_lookups
193 where district_code = p_district_code;
194 BEGIN
195 open cur_district_code(p_district_code);
196 fetch cur_district_code into l_dummy;
197 if cur_district_code%NOTFOUND then
198 close cur_district_code;
199 raise INVALID_DISTRICT_CODE;
200 end if;
201 close cur_district_code;
202 END CHECK_DISTRICT_CODE;
203 BEGIN
204 if p_check_digit = 'TRUE' then
205 l_format := '000000';
206 if check_format(p_district_code,l_format)='FALSE' then
207 raise INVALID_FORMAT;
208 end if;
209
210 l_district_code := substrb(p_district_code,1,5);
211 check_district_code(l_district_code);
212
213 l_check_digit := district_code_check_digit(l_district_code);
214 l_sixth := to_number(substrb(p_district_code,6,1));
215 if l_sixth = l_check_digit then
216 NULL;
217 else
218 raise INVALID_CHECK_DIGIT;
219 end if;
220 else
221 l_format := '00000';
222 if check_format(p_district_code,l_format)='FALSE' then
223 raise INVALID_FORMAT;
224 end if;
225
226 l_district_code := p_district_code;
227 check_district_code(l_district_code);
228 end if;
229
230 l_message := 'TRUE';
231 return l_message;
232 EXCEPTION
233 WHEN INVALID_FORMAT THEN
234 fnd_message.set_name('PER','HR_JP_INVALID_FORMAT');
235 fnd_message.set_token('VALUE',p_district_code);
236 fnd_message.set_token('FORMAT',l_format);
237 l_message := fnd_message.get;
238 return l_message;
239 WHEN INVALID_DISTRICT_CODE THEN
240 fnd_message.set_name('PER','HR_JP_DISTRICT_NOT_REGISTERED');
241 fnd_message.set_token('VALUE',p_district_code);
242 l_message := fnd_message.get;
243 return l_message;
244 WHEN INVALID_CHECK_DIGIT THEN
245 fnd_message.set_name('PER','HR_JP_INVALID_CHECK_DIGIT');
246 l_message := fnd_message.get;
247 return l_message;
248 WHEN OTHERS THEN
249 l_message := SQLERRM;
250 return l_message;
251 END DISTRICT_CODE_EXISTS;
252 --
253 FUNCTION ORG_EXISTS(
254 p_business_group_id IN NUMBER,
255 p_effective_date IN DATE,
256 p_organization_id IN NUMBER,
257 p_org_class IN VARCHAR2) RETURN VARCHAR2
258 IS
259 l_found BOOLEAN := FALSE;
260 l_dummy VARCHAR2(1);
261 CURSOR cur_org_exists IS
262 select 'X'
263 from hr_organization_information hoi,
264 hr_all_organization_units hou
265 where hou.business_group_id=p_business_group_id
266 and hou.organization_id=p_organization_id
267 -- and to_date(p_effective_date,'DD-MON-YYYY')
268 --between hou.date_from and nvl(hou.date_to,to_date(p_effective_date,'DD-MON-YYYY'))
269 and p_effective_date
270 between hou.date_from and nvl(hou.date_to,p_effective_date)
271 and hoi.organization_id=hou.organization_id
272 and hoi.org_information_context='CLASS'
273 and hoi.org_information1=p_org_class
274 and hoi.org_information2='Y';
275 BEGIN
276 open cur_org_exists;
277 fetch cur_org_exists into l_dummy;
278 if cur_org_exists%FOUND then
279 l_found := TRUE;
280 else
281 l_found := FALSE;
282 end if;
283 close cur_org_exists;
284
285 if l_found then
286 return 'TRUE';
287 else
288 return 'FALSE';
289 end if;
290 END ORG_EXISTS;
291 --
292 FUNCTION CHECK_HALF_KANA(
293 p_value IN VARCHAR2) RETURN VARCHAR2
294 IS
295 l_output VARCHAR2(80);
296 l_rgeflg VARCHAR2(80);
297 l_correct VARCHAR2(10) := 'TRUE';
298 l_value VARCHAR2(80);
299 BEGIN
300 l_value := p_value;
301 hr_spec_pkg.checkformat(l_value,'K',l_output,null,null,'Y',l_rgeflg,null);
302 return l_correct;
303
304 EXCEPTION
305 WHEN OTHERS THEN
306 l_correct := 'FALSE';
307 return l_correct;
308 END CHECK_HALF_KANA;
309 --
310 FUNCTION VEHICLE_EXISTS(
311 p_business_group_id IN NUMBER,
312 p_assignment_id IN NUMBER,
313 p_effective_date IN DATE,
314 p_vehicle_allocation_id IN NUMBER) RETURN VARCHAR2
315 IS
316 l_found BOOLEAN := FALSE;
317 l_dummy VARCHAR2(1);
318 CURSOR cur_vehicle_exists IS
319 select 'X'
320 from pqp_vehicle_allocations_f pva,
321 pqp_vehicle_repository_f pvr
322 where pva.business_group_id=p_business_group_id
323 and pva.assignment_id=p_assignment_id
324 and pva.vehicle_allocation_id=p_vehicle_allocation_id
325 and p_effective_date
326 between pva.effective_start_date and nvl(pva.effective_end_date,p_effective_date)
327 and pvr.vehicle_repository_id = pva.vehicle_repository_id
328 and p_effective_date
329 between pvr.effective_start_date and nvl(pvr.effective_end_date,p_effective_date);
330 BEGIN
331 open cur_vehicle_exists;
332 fetch cur_vehicle_exists into l_dummy;
333 if cur_vehicle_exists%FOUND then
334 l_found := TRUE;
335 else
336 l_found := FALSE;
337 end if;
338 close cur_vehicle_exists;
339
340 if l_found then
341 return 'TRUE';
342 else
343 return 'FALSE';
344 end if;
345 END VEHICLE_EXISTS;
346 --
347 END per_jp_validations;