[Home] [Help]
PACKAGE BODY: APPS.XLE_REGISTRATIONS_VAL_PVT
Source
1 PACKAGE BODY XLE_REGISTRATIONS_VAL_PVT AS
2 /* $Header: xleregvb.pls 120.1.12010000.5 2008/12/24 18:52:24 makansal ship $ */
3 G_PKG_NAME VARCHAR2(30) := 'XLE_REGISTRATIONS_VAL_PVT';
4 -- Logging Infra
5 G_CURRENT_RUNTIME_LEVEL NUMBER;
6 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
7 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
8 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
10 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
12 G_MODULE_NAME CONSTANT VARCHAR2(50) := 'XLE.PLSQL.XLE_REGISTRATIONS_VAL_PVT';
13 g_log_msg VARCHAR2(2000);
14 -- Logging Infra
15 /*-----------------------------------------------------------
16 This procedure is called from the Creae LE page, Create
17 Registration for LE page and from Create Registration for
18 Establishment page.
19 This procedure validates the Registration Number as per the
20 validation rules known for a few of the countries.
21 viz - Argentina, Brazil, Colombia, Chile, Spain,
22 Italy, Portugal
23 ------------------------------------------------------------*/
24 PROCEDURE Validate_Reg_Number(
25 p_jurisdiction_id IN NUMBER,
26 p_registration_id IN NUMBER,
27 p_registration_number IN VARCHAR2,
28 p_entity_type IN VARCHAR2,
29 p_init_msg_list IN VARCHAR2,
30 x_return_status IN OUT NOCOPY VARCHAR2,
31 x_msg_count IN OUT NOCOPY NUMBER ,
32 x_msg_data IN OUT NOCOPY VARCHAR2)
33 IS
34 CURSOR c_jur_dtls (p_jurisdiction_id NUMBER,
35 p_entity_type VARCHAR2)
36 IS
37 SELECT jur.legislative_cat_code,
38 DECODE(p_entity_type, 'LE' , jur.registration_code_le,
39 'ETB', jur.registration_code_etb) registration_code,
40 DECODE(p_entity_type, 'LE' , jur.required_le_flag,
41 'ETB', jur.required_etb_flag) required_flag,
42 geo.geography_code country_code
43 FROM xle_jurisdictions_b jur,
44 hz_geographies geo
45 WHERE jur.geography_id = geo.geography_id
46 AND jur.jurisdiction_id = p_jurisdiction_id
47 AND TRUNC(SYSDATE) BETWEEN TRUNC(Nvl(jur.effective_from, SYSDATE))
48 AND TRUNC(Nvl(jur.effective_from, SYSDATE))
49 AND TRUNC(SYSDATE) BETWEEN TRUNC(Nvl(geo.start_date, SYSDATE))
50 AND TRUNC(Nvl(geo.end_date, SYSDATE));
51 CURSOR c_check_unique (p_source_table VARCHAR2,
52 p_jurisdiction_id NUMBER,
53 p_registration_number VARCHAR2)
54 IS
55 SELECT registration_id
56 FROM xle_registrations
57 WHERE source_table = p_source_table
58 AND jurisdiction_id = p_jurisdiction_id
59 AND registration_number = p_registration_number
60 AND (effective_to is null or effective_to >= sysdate);
61 l_legislative_cat_code xle_jurisdictions_b.legislative_cat_code%TYPE;
62 l_registration_code xle_jurisdictions_b.registration_code_le%TYPE;
63 l_required_flag xle_jurisdictions_b.required_le_flag%TYPE;
64 l_country_code hz_geographies.geography_code%TYPE;
65 l_api_name VARCHAR2(50) := 'Validate_Reg_Number';
66 l_source_table VARCHAR2(30);
67 l_registration_id NUMBER;
68 BEGIN
69 x_return_status := FND_API.G_RET_STS_SUCCESS;
70 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
71 IF FND_API.to_boolean(p_init_msg_list)
72 THEN
73 FND_MSG_PUB.Initialize;
74 END IF;
75 IF p_jurisdiction_id IS NULL
76 THEN
77 -- this is a development error
78 x_return_status := FND_API.G_RET_STS_ERROR;
79 x_msg_data := 'Mandatory parameter jurisdiction_id is not passed';
80 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
81 THEN
82 g_log_msg := 'Mandatory parameter jurisdiction_id is not passed';
83 FND_LOG.STRING(G_LEVEL_STATEMENT,
84 G_MODULE_NAME || l_api_name, g_log_msg);
85 END IF;
86 END IF;
87 IF x_return_status = FND_API.G_RET_STS_SUCCESS
88 THEN
89 -- Check unique
90 IF p_entity_type = 'ETB'
91 THEN
92 l_source_table := 'XLE_ETB_PROFILES';
93 ELSE
94 l_source_table := 'XLE_ENTITY_PROFILES';
95 END IF;
96 OPEN c_check_unique (l_source_table,
97 p_jurisdiction_id,
98 p_registration_number);
99 FETCH c_check_unique INTO l_registration_id;
100 CLOSE c_check_unique;
101 IF l_registration_id IS NOT NULL
102 THEN
103 IF Nvl( p_registration_id, -99) <> l_registration_id
104 THEN
105 x_return_status := FND_API.G_RET_STS_ERROR;
106 FND_MESSAGE.SET_NAME('XLE', 'XLE_REG_NUM_DUPLICATE_WARN');
107 FND_MSG_PUB.Add;
108 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
109 THEN
110 g_log_msg := 'RegNumber is not unique ' || p_registration_number;
111 FND_LOG.STRING(G_LEVEL_STATEMENT,
112 G_MODULE_NAME || l_api_name, g_log_msg);
113 END IF;
114 END IF;
115 END IF;
116 END IF;
117 IF x_return_status = FND_API.G_RET_STS_SUCCESS
118 THEN
119 OPEN c_jur_dtls (p_jurisdiction_id,
120 p_entity_type);
121 FETCH c_jur_dtls INTO l_legislative_cat_code,
122 l_registration_code,
123 l_required_flag,
124 l_country_code;
125 CLOSE c_jur_dtls;
126 IF l_country_code IS NULL
127 THEN
128 -- this is a development error
129 x_return_status := FND_API.G_RET_STS_ERROR;
130 x_msg_data := 'Invalid jurisdiction_id passed';
131 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
132 THEN
133 g_log_msg := 'Invalid jurisdiction_id passed';
134 FND_LOG.STRING(G_LEVEL_STATEMENT,
135 G_MODULE_NAME || l_api_name, g_log_msg);
136 END IF;
137 END IF ;
138 END IF ;
139 IF p_registration_number IS NOT NULL
140 AND l_registration_code IS NOT NULL
141 AND x_return_status = FND_API.G_RET_STS_SUCCESS
142 THEN
143 IF l_country_code = 'AR'
144 THEN
145 -- Validations for Argentina
146 do_ar_regnum_validations(l_legislative_cat_code,
147 l_required_flag,
148 l_registration_code,
149 p_registration_number,
150 x_return_status,
151 x_msg_data,
152 x_msg_count);
153 ELSIF l_country_code = 'BR'
154 THEN
155 -- Validations for Brazil
156 do_br_regnum_validations(l_legislative_cat_code,
157 l_required_flag,
158 l_registration_code,
159 p_registration_number,
160 x_return_status,
161 x_msg_data,
162 x_msg_count);
163 ELSIF l_country_code = 'CL'
164 THEN
165 -- Validations for Chile
166 do_cl_regnum_validations(l_legislative_cat_code,
167 l_required_flag,
168 l_registration_code,
169 p_registration_number,
170 x_return_status,
171 x_msg_data,
172 x_msg_count);
173 ELSIF l_country_code = 'CO'
174 THEN
175 -- Validations for Colombia
176 do_co_regnum_validations(l_legislative_cat_code,
177 l_required_flag,
178 l_registration_code,
179 p_registration_number,
180 x_return_status,
181 x_msg_data,
182 x_msg_count);
183 ELSIF l_country_code = 'IT'
184 THEN
185 -- Validations for Italy
186 do_it_regnum_validations(l_legislative_cat_code,
187 l_required_flag,
188 l_registration_code,
189 p_registration_number,
190 x_return_status,
191 x_msg_data,
192 x_msg_count);
193 ELSIF l_country_code = 'PT'
194 THEN
195 -- Validations for Portugal
196 do_pt_regnum_validations(l_legislative_cat_code,
197 l_required_flag,
198 l_registration_code,
199 p_registration_number,
200 x_return_status,
201 x_msg_data,
202 x_msg_count);
203 ELSIF l_country_code = 'ES'
204 THEN
205 -- Validations for Spain
206 do_es_regnum_validations(l_legislative_cat_code,
207 l_required_flag,
208 l_registration_code,
209 p_registration_number,
210 x_return_status,
211 x_msg_data,
212 x_msg_count);
213 END IF; -- Check country code
214 END IF; -- Reg Code and Number provided
215 FND_MSG_PUB.Count_And_Get
216 (p_count => x_msg_count,
217 p_data => x_msg_data);
218 EXCEPTION
219 WHEN FND_API.G_EXC_ERROR THEN
220 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
221 THEN
222 g_log_msg := SQLERRM;
223 FND_LOG.STRING(G_LEVEL_STATEMENT,
224 G_MODULE_NAME || l_api_name, g_log_msg);
225 END IF;
226 x_return_status := FND_API.G_RET_STS_ERROR ;
227 FND_MSG_PUB.Count_And_Get
228 ( p_count => x_msg_count,
229 p_data => x_msg_data
230 );
231 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
232 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
233 THEN
234 g_log_msg := SQLERRM;
235 FND_LOG.STRING(G_LEVEL_STATEMENT,
236 G_MODULE_NAME || l_api_name, g_log_msg);
237 END IF;
238 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
239 FND_MSG_PUB.Count_And_Get
240 ( p_count => x_msg_count,
241 p_data => x_msg_data
242 );
243 WHEN OTHERS THEN
244 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
245 THEN
246 g_log_msg := SQLERRM;
247 FND_LOG.STRING(G_LEVEL_STATEMENT,
248 G_MODULE_NAME || l_api_name, g_log_msg);
249 END IF;
250 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
251 IF FND_MSG_PUB.Check_Msg_Level
252 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
253 THEN
254 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
255 l_api_name);
256 END IF;
257 FND_MSG_PUB.Count_And_Get
258 ( p_count => x_msg_count,
259 p_data => x_msg_data
260 );
261 END Validate_Reg_Number;
262 -- Check if a given pattern has only numbers
263 FUNCTION check_numeric(check_value VARCHAR2,
264 pos_from NUMBER,
265 pos_for NUMBER)
266 RETURN VARCHAR2
267 IS
268 num_check VARCHAR2(40);
269 BEGIN
270 num_check := '1';
271 num_check := nvl(
272 rtrim(
273 translate(substr(check_value,pos_from,pos_for),
274 '1234567890',
275 ' ')
276 ), '0'
277 );
278 RETURN(num_check);
279 END check_numeric;
280 -- Check if a given pattern has only numbers
281 -- For the Latin American codes, the reg number can also have
282 -- '-', '.', '/'
283 FUNCTION check_numeric_latin(check_value VARCHAR2)
284 RETURN VARCHAR2
285 IS
286 num_check VARCHAR2(40);
287 BEGIN
288 num_check := '1';
289 num_check := nvl(
290 rtrim(
291 translate(check_value,
292 '1234567890/-.',
293 ' ')
294 ), '0'
295 );
296 RETURN(num_check);
297 END check_numeric_latin;
298 -- Perform Spanish registration number validations
299 PROCEDURE do_es_regnum_validations(
300 p_legislative_cat_code IN VARCHAR2,
301 p_required_flag IN VARCHAR2,
302 p_registration_code IN VARCHAR2,
303 p_registration_number IN VARCHAR2,
304 x_return_status IN OUT NOCOPY VARCHAR2 ,
305 x_msg_data IN OUT NOCOPY VARCHAR2 ,
306 x_msg_count IN OUT NOCOPY NUMBER )
307 IS
308 l_nif_value xle_registrations.registration_number%TYPE;
309 l_check_digit VARCHAR2(2);
310 l_work_nif VARCHAR2(20);
311 l_numeric_result VARCHAR2(40);
312 l_work_nif_d NUMBER(20);
313 l_api_name VARCHAR2(50) := 'do_es_regnum_validations';
314 BEGIN
315 x_return_status := FND_API.G_RET_STS_SUCCESS;
316 IF p_registration_code NOT IN ('NIF', 'CIF')
317 THEN
318 RETURN;
319 END IF;
323 THEN
320 l_nif_value := p_registration_number;
321 l_check_digit := substr(l_nif_value, length(l_nif_value));
322 IF length(l_nif_value) > 1
324 /** make sure that Fiscal Code starts with one of the following characters **/
325 IF upper(substr(l_nif_value,1,1))
326 IN ('A','B','C','D','E','F','G','H','T','P','Q','S',
327 'X','K','L','M','N','Y','Z','J','R','U','V','W',
328 '0','1','2','3','4','5','6','7','8','9')
329 THEN
330 /** If the Fiscal Code starts with a T, then no futher **/
331 /** validation is required **/
332 IF substr(l_nif_value,1,1) = 'T'
333 THEN
334 x_return_status := FND_API.G_RET_STS_SUCCESS;
335 /* Bug: 7609077 Added the logic for taxpayer id starting with 'N' */
336 ELSIF substr(l_nif_value,1,1) = 'N'
337 THEN
338 l_numeric_result := check_numeric(l_nif_value,2,length(l_nif_value)-2);
339 IF l_numeric_result = '0'
340 THEN
341 /* It's Numeric Continue, Check if the last digit is a character */
342 IF (instr('ABCDEFGHIJKLMNOPQRSTUVWXYZ', l_check_digit) > 0)
343 THEN
344 x_return_status := FND_API.G_RET_STS_SUCCESS;
345 ELSE
346 x_return_status := FND_API.G_RET_STS_ERROR;
347 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
348 THEN
349 g_log_msg := 'Check Algorithm failed for ' ||
350 p_registration_number;
351 FND_LOG.STRING(G_LEVEL_STATEMENT,
352 G_MODULE_NAME || l_api_name, g_log_msg);
353 END IF;
354 END IF;
355 ELSE
356 x_return_status := FND_API.G_RET_STS_ERROR;
357 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
358 THEN
359 g_log_msg := 'Non numeric characters found in ' ||
360 p_registration_number;
361 FND_LOG.STRING(G_LEVEL_STATEMENT,
362 G_MODULE_NAME || l_api_name, g_log_msg);
363 END IF;
364 END IF;
365 /* Bug: 7609077 added the logic for taxpayer id starting with 'Y', 'Z' */
366 ELSIF substr(l_nif_value,1,1) in
367 ('X','K','L','M','Y','Z','0','1','2','3','4','5','6','7','8','9')
368 THEN
369 /** Fiscal Code does not start with 'T' **/
370 /** IF the Fiscal Code begins with the following **/
371 /** It's a physical person. The NIF has to end in a **/
372 /** specific letter. Eg VAlids = X1596399S,2601871L **/
373 IF substr(l_nif_value,1,1) in ('X','K','L','M','Y','Z')
374 THEN
375 l_numeric_result := check_numeric(l_nif_value,2,length(l_nif_value)-2);
376 IF l_numeric_result = '0'
377 THEN
378 /* its numeric so continue */
379 IF(substr(l_nif_value,1,1) = 'Y')
380 THEN
381 l_work_nif := '1' || substr(l_nif_value,2,length(l_nif_value)-2);
382 ELSIF(substr(l_nif_value,1,1) = 'Z')
383 THEN
384 l_work_nif := '2' || substr(l_nif_value,2,length(l_nif_value)-2);
385 ELSE
386 l_work_nif := substr(l_nif_value,2,length(l_nif_value)-2);
387 END IF;
388 IF substr('TRWAGMYFPDXBNJZSQVHLCKE',mod
389 (to_number(l_work_nif) ,23) + 1,1) = l_check_digit
390 THEN
391 x_return_status := FND_API.G_RET_STS_SUCCESS;
392 ELSE
393 x_return_status := FND_API.G_RET_STS_ERROR;
397 p_registration_number;
394 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
395 THEN
396 g_log_msg := 'Check Algorithm failed for ' ||
398 FND_LOG.STRING(G_LEVEL_STATEMENT,
399 G_MODULE_NAME || l_api_name, g_log_msg);
400 END IF;
401 END IF;
402 ELSE
403 x_return_status := FND_API.G_RET_STS_ERROR;
404 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
405 THEN
406 g_log_msg := 'Non numeric characters found in ' ||
407 p_registration_number;
408 FND_LOG.STRING(G_LEVEL_STATEMENT,
409 G_MODULE_NAME || l_api_name, g_log_msg);
410 END IF;
411 END IF; /* end of numeric check */
412 ELSE
413 l_numeric_result := check_numeric(l_nif_value,1,length(l_nif_value)-1);
414 IF l_numeric_result = '0'
415 THEN
416 /* its numeric so continue */
417 l_work_nif := substr(l_nif_value,1,length(l_nif_value)-1);
418 IF substr('TRWAGMYFPDXBNJZSQVHLCKE',mod
419 (to_number(l_work_nif) ,23) + 1,1) = l_check_digit
420 THEN
421 x_return_status := FND_API.G_RET_STS_SUCCESS;
422 ELSE
423 x_return_status := FND_API.G_RET_STS_ERROR;
424 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
425 THEN
426 g_log_msg := 'Check Algorithm failed for ' ||
427 p_registration_number;
428 FND_LOG.STRING(G_LEVEL_STATEMENT,
429 G_MODULE_NAME || l_api_name, g_log_msg);
430 END IF;
431 END IF;
432 ELSE
433 x_return_status := FND_API.G_RET_STS_ERROR;
434 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
435 THEN
436 g_log_msg := 'Non numeric characters found in ' ||
437 p_registration_number;
438 FND_LOG.STRING(G_LEVEL_STATEMENT,
439 G_MODULE_NAME || l_api_name, g_log_msg);
440 END IF;
441 END IF; /* end of numeric check */
442 END IF;
443 /* Bug: 7609077 added the logic for taxpayer id starting with 'J','R','U','V','W' */
444 ELSIF substr(l_nif_value,1,1) in
445 ('A','B','C','D','E','F','G','H','P','Q','S','J','R','U','V','W')
446 THEN
447 /** It's a company. Examples of valid company NIFs are **/
448 /** A78361482 A78211646 F2831001I Q0467001D P0801500J **/
449 l_numeric_result := check_numeric(l_nif_value,2,length(l_nif_value)-2);
450 IF l_numeric_result = '0'
451 THEN
452 /* its numeric so continue */
453 l_work_nif := substr(l_nif_value,2,length(l_nif_value)-2);
454 l_work_nif_d := to_number(substr(l_work_nif,2,1)) +
455 to_number(substr(l_work_nif,4,1)) +
456 to_number(substr(l_work_nif,6,1)) +
457 to_number(substr(to_char(to_number(substr(l_work_nif,1,1)) * 2),1,1)) +
458 to_number(nvl(substr(to_char(to_number(substr(l_work_nif,1,1))
459 * 2),2,1),'0')) +
460 to_number(substr(to_char(to_number(substr(l_work_nif,3,1)) * 2),1,1)) +
461 to_number(nvl(substr(to_char(to_number(substr(l_work_nif,3,1))
462 * 2),2,1),'0')) +
463 to_number(substr(to_char(to_number(substr(l_work_nif,5,1)) * 2),1,1)) +
464 to_number(nvl(substr(to_char(to_number(substr(l_work_nif,5,1))
465 * 2),2,1),'0')) +
466 to_number(substr(to_char(to_number(substr(l_work_nif,7,1)) * 2),1,1)) +
467 to_number(nvl(substr(to_char(to_number(substr(l_work_nif,7,1))
468 * 2),2,1),'0'))
469 + nvl(to_number(substr(l_work_nif,8,1)),0)
473 IF l_check_digit in ('A','B','C','D','E','F','G','H','I','J')
470 + nvl(to_number(substr(to_char(to_number(substr(l_work_nif,9,1)) * 2),1,1)),0) +
471 to_number(nvl(substr(to_char(to_number(substr(l_work_nif,9,1))
472 * 2),2,1),'0'));
474 THEN
475 IF substr('JABCDEFGHI',((ceil(l_work_nif_d/10) * 10)
476 - l_work_nif_d) + 1, 1) = l_check_digit
477 THEN
478 x_return_status := FND_API.G_RET_STS_SUCCESS;
479 ELSE
480 x_return_status := FND_API.G_RET_STS_ERROR;
481 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
482 THEN
483 g_log_msg := 'Check Algorithm failed for ' ||
484 p_registration_number;
485 FND_LOG.STRING(G_LEVEL_STATEMENT,
486 G_MODULE_NAME || l_api_name, g_log_msg);
487 END IF;
488 END IF;
489 ELSIF l_check_digit = to_char((ceil(l_work_nif_d/10) *10) - l_work_nif_d)
490 THEN
491 x_return_status := FND_API.G_RET_STS_SUCCESS;
492 ELSE
493 x_return_status := FND_API.G_RET_STS_ERROR;
494 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
495 THEN
496 g_log_msg := 'Check Algorithm failed for ' ||
497 p_registration_number;
498 FND_LOG.STRING(G_LEVEL_STATEMENT,
499 G_MODULE_NAME || l_api_name, g_log_msg);
500 END IF;
501 END IF;
502 ELSE
503 x_return_status := FND_API.G_RET_STS_ERROR;
504 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
505 THEN
506 g_log_msg := 'Non numeric characters found in ' ||
507 p_registration_number;
508 FND_LOG.STRING(G_LEVEL_STATEMENT,
509 G_MODULE_NAME || l_api_name, g_log_msg);
510 END IF;
511 END IF; /* end of numeric check */
512 ELSE
513 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
514 THEN
515 g_log_msg := 'Reg number is not person and not a company '||
516 p_registration_number;
517 FND_LOG.STRING(G_LEVEL_STATEMENT,
518 G_MODULE_NAME || l_api_name, g_log_msg);
519 END IF;
520 x_return_status := FND_API.G_RET_STS_ERROR;
521 END IF; /* End of person or company check */
522 ELSE
523 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
524 THEN
525 g_log_msg := 'Reg Num does not start with a valid character '||
526 p_registration_number;
527 FND_LOG.STRING(G_LEVEL_STATEMENT,
528 G_MODULE_NAME || l_api_name, g_log_msg);
529 END IF;
530 x_return_status := FND_API.G_RET_STS_ERROR;
531 END IF; /* does not start with a valid character */
532 ELSE
533 x_return_status := FND_API.G_RET_STS_ERROR;
534 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
535 THEN
536 g_log_msg := 'Reg Num failed length check '||
537 p_registration_number;
538 FND_LOG.STRING(G_LEVEL_STATEMENT,
539 G_MODULE_NAME || l_api_name, g_log_msg);
540 END IF;
541 END IF; /* end of length check */
542 IF x_return_status = FND_API.G_RET_STS_ERROR
543 THEN
544 FND_MESSAGE.SET_NAME('XLE', 'XLE_INVALID_REG_NUM_ERR');
545 FND_MESSAGE.SET_TOKEN('REG_CODE', p_registration_code);
546 FND_MESSAGE.SET_TOKEN('REG_NUM', p_registration_number);
547 FND_MSG_PUB.Add;
548 END IF;
549 FND_MSG_PUB.Count_And_Get
550 (p_count => x_msg_count,
551 p_data => x_msg_data);
552 EXCEPTION
553 WHEN FND_API.G_EXC_ERROR THEN
554 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
555 THEN
556 g_log_msg := SQLERRM;
557 FND_LOG.STRING(G_LEVEL_STATEMENT,
558 G_MODULE_NAME || l_api_name, g_log_msg);
559 END IF;
560 x_return_status := FND_API.G_RET_STS_ERROR ;
561 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
562 l_api_name);
563 FND_MSG_PUB.Count_And_Get
564 ( p_count => x_msg_count,
565 p_data => x_msg_data
566 );
567 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
571 FND_LOG.STRING(G_LEVEL_STATEMENT,
568 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
569 THEN
570 g_log_msg := SQLERRM;
572 G_MODULE_NAME || l_api_name, g_log_msg);
573 END IF;
574 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
575 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
576 l_api_name);
577 FND_MSG_PUB.Count_And_Get
578 ( p_count => x_msg_count,
579 p_data => x_msg_data
580 );
581 WHEN OTHERS THEN
582 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
583 THEN
584 g_log_msg := SQLERRM;
585 FND_LOG.STRING(G_LEVEL_STATEMENT,
586 G_MODULE_NAME || l_api_name, g_log_msg);
587 END IF;
588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
589 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
590 l_api_name);
591 FND_MSG_PUB.Count_And_Get
592 ( p_count => x_msg_count,
593 p_data => x_msg_data
594 );
595 END do_es_regnum_validations;
596
597
598 procedure PO_VALIDATE_VAT_IT(VAT_VALUE IN varchar2, -- Bug No : 6884561 Start
599 Xi_UNIQUE_FLAG IN varchar2,
600 RET_VAR OUT NOCOPY varchar2,
601 RET_MESSAGE OUT NOCOPY varchar2)
602 AS
603 VAT_NUM varchar2(30);
604 check_digit number(1);
605 position_i number(2);
606 integer_value number(1);
607 calc_check number(2);
608 calc_cd varchar2(1);
609 indicator varchar2(1);
610 even_value number(2);
611 even_sub_tot number(4);
612 even_tot number(5);
613 odd_tot number(5);
614 check_tot number(6);
615 /**************************/
616 /* SUB-PROCEDURES SECTION */
617 /**************************/
618 procedure fail_uniqueness is
619 begin
620 RET_VAR := 'F';
621 RET_MESSAGE := 'PO_VAT_DUPLICATE_VAT_NUM';
622 end fail_uniqueness;
623 procedure fail_check is
624 begin
625 RET_VAR := 'F';
626 RET_MESSAGE := 'PO_VAT_INVALID_VAT_NUM';
627 end fail_check;
628 procedure system_failure is
629 begin
630 RAISE_APPLICATION_ERROR(-20000, 'PROCEDURE PO_VALIDATE_VAT_IT');
631 end system_failure;
632 procedure pass_check is
633 begin
634 RET_VAR := 'P';
635 RET_MESSAGE := '';
636 end pass_check;
637 /** procedure to check that the chars sent are numeric only **/
638 /** if ok, then sends back the output as a number **/
639 procedure check_numeric(input_string IN varchar2,
640 output_val OUT NOCOPY varchar2,
641 flag1 OUT NOCOPY varchar2) is
642 num_check varchar2(30);
643 var1 varchar2(30);
644 begin
645 num_check := '';
646 var1 := input_string;
647 num_check := nvl(rtrim( translate(var1, '1234567890',
648 ' ')
649 ), '0'
650 );
651 IF num_check <> '0'
652 then
653 flag1 := 'F';
654 output_val := '0';
655 ELSE
656 flag1 := 'P';
657 output_val := var1;
658 END IF;
659 end check_numeric;
660 /****************/
661 /* MAIN SECTION */
662 /****************/
663 BEGIN
664 indicator := '';
665 odd_tot := 0;
666 even_tot := 0;
667 /** ensure that VAT_VALUE passed in is only numeric **/
668 check_numeric(VAT_VALUE, VAT_NUM, indicator);
669 check_digit := substr(VAT_NUM, (length(VAT_NUM)));
670
671 IF Xi_UNIQUE_FLAG = 'N'
672 then
673 fail_uniqueness;
674 ELSIF Xi_UNIQUE_FLAG = 'Y'
675 then
676 /** make sure that VAT Num code is only 11chars - including Check digit **/
677 IF (length(VAT_NUM) = 11) AND (indicator = 'P')
678 then
679 FOR position_i IN 1..10 LOOP
680 /** moves along length of VAT Num Code and assigns weightings **/
681 /** to each of the digits upto and including the 10th position **/
682 /** all odd positioned integers are added together. All evenly **/
683 /** postitioned integers are multiplied by 2, if greater than **/
684 /** 10, the digits are added together. The last digit of the **/
685 /** sum totals when added together is subtracted from 10 - unless **/
686 /** already zero. This becomes the VAT Num check digit **/
687 integer_value := substr(VAT_NUM,position_i,1);
688 IF position_i in (2,4,6,8,10)
689 then
693 even_sub_tot := substr(even_value,1,1) +
690 even_value := integer_value * 2;
691 IF even_value > 9
692 then
694 substr(even_value,2,1);
695 ELSE
696 even_sub_tot := even_value;
697 END IF;
698 even_tot := even_tot + even_sub_tot;
699 ELSE
700 odd_tot := odd_tot + integer_value;
701 END IF;
702 END LOOP; /** of the counter position_i **/
703 check_tot := odd_tot + even_tot;
704 IF substr(check_tot,length(check_tot),1) = 0
705 then
706 calc_cd := 0;
707 ELSE
708 calc_cd := 10 - substr(check_tot, length(check_tot),1);
709 END IF;
710 /*** After having calculated what should be the ITALIAN VAT Num ***/
711 /*** Check digit compare to the actual and fail if not the same ***/
712 IF calc_cd <> check_digit
713 then
714 fail_check;
715 ELSE
716 pass_check;
717 END IF;
718 ELSE
719 fail_check; /** VAT Num is incorrect length or is not numeric**/
720 END IF;
721 ELSE
722 pass_check;
723 END IF; /** of fail uniqueness check **/
724 END PO_VALIDATE_VAT_IT; -- Bug No : 6884561 End
725
726 /********** End of PO_VALIDATE_VAT_IT **************************/
727
728 -- Perform Portugeese registration number validations
729 --Ex valid number is - 502186771
730 PROCEDURE do_pt_regnum_validations(
731 p_legislative_cat_code IN VARCHAR2,
732 p_required_flag IN VARCHAR2,
733 p_registration_code IN VARCHAR2,
734 p_registration_number IN VARCHAR2,
735 x_return_status IN OUT NOCOPY VARCHAR2 ,
736 x_msg_data IN OUT NOCOPY VARCHAR2 ,
737 x_msg_count IN OUT NOCOPY NUMBER )
738 IS
739 l_nif_value xle_registrations.registration_number%TYPE;
740 l_check_digit VARCHAR2(2);
741 l_work_nif VARCHAR2(20);
742 l_numeric_result VARCHAR2(40);
743 l_work_nif_d NUMBER(20);
744 l_position_i NUMBER(2);
745 l_integer_value NUMBER(1);
746 l_multiplied_number NUMBER(2);
747 l_multiplied_sum NUMBER(3);
748 l_check_result VARCHAR2(1);
749 l_mod11 NUMBER(8);
750 l_cal_cd NUMBER(2);
751 l_api_name VARCHAR2(50) := 'do_pt_regnum_validations';
752 BEGIN
753 x_return_status := FND_API.G_RET_STS_SUCCESS;
754 IF p_registration_code <> 'NIPC'
755 THEN
756 RETURN;
757 END IF;
758 l_nif_value := p_registration_number;
759 l_check_digit := substr(l_nif_value, length(l_nif_value));
760 l_multiplied_number := 0;
761 l_multiplied_sum := 0;
762 IF length(l_nif_value) = 9
763 THEN
764 l_numeric_result := check_numeric(l_nif_value,1,length(l_nif_value));
765 IF l_numeric_result = '0'
766 THEN
767 /* its numeric so continue */
768 FOR l_position_i IN 2..length(l_nif_value)
769 LOOP
770 l_integer_value := substr(l_nif_value,length(l_nif_value)-(l_position_i-1),1);
771 l_multiplied_number := l_integer_value * l_position_i;
772 l_multiplied_sum := l_multiplied_sum + l_multiplied_number;
773 END LOOP;
774 l_mod11 := (floor(l_multiplied_sum/11)+1)*11;
775 l_cal_cd := l_mod11 - l_multiplied_sum;
776 IF (mod(l_multiplied_sum,11) = 0) OR (l_cal_cd > 9)
777 THEN
778 l_cal_cd := 0;
779 END IF;
780 IF l_cal_cd = l_check_digit
781 THEN
785 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
782 x_return_status := FND_API.G_RET_STS_SUCCESS;
783 ELSE
784 x_return_status := FND_API.G_RET_STS_ERROR;
786 THEN
787 g_log_msg := ' Check algorithm not successfull for '||
788 p_registration_number;
789 FND_LOG.STRING(G_LEVEL_STATEMENT,
790 G_MODULE_NAME || l_api_name, g_log_msg);
791 END IF;
792 END IF;
793 ELSE
794 x_return_status := FND_API.G_RET_STS_ERROR;
795 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
796 THEN
797 g_log_msg := 'Non numeric characters found ' ||
798 p_registration_number;
799 FND_LOG.STRING(G_LEVEL_STATEMENT,
800 G_MODULE_NAME || l_api_name, g_log_msg);
801 END IF;
802 END IF;
803 ELSE
804 x_return_status := FND_API.G_RET_STS_ERROR;
805 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
806 THEN
807 g_log_msg := 'Reg Num failed length check '||
808 p_registration_number;
809 FND_LOG.STRING(G_LEVEL_STATEMENT,
810 G_MODULE_NAME || l_api_name, g_log_msg);
811 END IF;
812 END IF; /* of fail length check */
813 IF x_return_status = FND_API.G_RET_STS_ERROR
814 THEN
815 FND_MESSAGE.SET_NAME('XLE', 'XLE_INVALID_REG_NUM_ERR');
816 FND_MESSAGE.SET_TOKEN('REG_CODE', p_registration_code);
817 FND_MESSAGE.SET_TOKEN('REG_NUM', p_registration_number);
818 FND_MSG_PUB.Add;
819 END IF;
820 FND_MSG_PUB.Count_And_Get
821 (p_count => x_msg_count,
822 p_data => x_msg_data);
823 EXCEPTION
824 WHEN FND_API.G_EXC_ERROR THEN
825 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
826 THEN
827 g_log_msg := SQLERRM;
828 FND_LOG.STRING(G_LEVEL_STATEMENT,
829 G_MODULE_NAME || l_api_name, g_log_msg);
830 END IF;
831 x_return_status := FND_API.G_RET_STS_ERROR ;
832 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
833 l_api_name);
834 FND_MSG_PUB.Count_And_Get
835 ( p_count => x_msg_count,
836 p_data => x_msg_data
837 );
838 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
839 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
840 THEN
841 g_log_msg := SQLERRM;
842 FND_LOG.STRING(G_LEVEL_STATEMENT,
843 G_MODULE_NAME || l_api_name, g_log_msg);
844 END IF;
845 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
846 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
847 l_api_name);
848 FND_MSG_PUB.Count_And_Get
849 ( p_count => x_msg_count,
850 p_data => x_msg_data
851 );
852 WHEN OTHERS THEN
853 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
854 THEN
855 g_log_msg := SQLERRM;
856 FND_LOG.STRING(G_LEVEL_STATEMENT,
857 G_MODULE_NAME || l_api_name, g_log_msg);
858 END IF;
859 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
860 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
861 l_api_name);
862 FND_MSG_PUB.Count_And_Get
863 ( p_count => x_msg_count,
864 p_data => x_msg_data
865 );
866 END do_pt_regnum_validations;
867 -- Perform Italian registration number validations
868 -- Eg of a valid number is 100000000000000H
869 PROCEDURE do_it_regnum_validations(
870 p_legislative_cat_code IN VARCHAR2,
871 p_required_flag IN VARCHAR2,
872 p_registration_code IN VARCHAR2,
873 p_registration_number IN VARCHAR2,
874 x_return_status IN OUT NOCOPY VARCHAR2 ,
875 x_msg_data IN OUT NOCOPY VARCHAR2 ,
876 x_msg_count IN OUT NOCOPY NUMBER )
877 IS
878 l_nif_value xle_registrations.registration_number%TYPE;
879 l_check_digit VARCHAR2(2);
880 l_position_i NUMBER(2);
881 l_position_weight NUMBER(2);
882 l_total_weighting NUMBER(3);
883 l_char_value VARCHAR2(1);
884 l_calc_check NUMBER(2);
885 l_calc_cd VARCHAR2(1);
886 vat_ret_code varchar2(1);
887 vat_ret_message varchar2(60);
888 l_api_name VARCHAR2(50) := 'do_it_regnum_validations';
889
890 procedure fail_uniqueness is
891 begin
892 x_return_status := FND_API.G_RET_STS_ERROR;
893 x_msg_data := 'PO_NIF_DUPLICATE_NIF_NUM';
894 end fail_uniqueness;
895
896 /** FUNCTION returns the calculated check digit **/
897 FUNCTION find_check_digit(l_remainder NUMBER)
898 RETURN VARCHAR2 IS
899 l_cd_result varchar2(1);
900 BEGIN
901 IF l_remainder = 0
902 THEN
903 l_cd_result := 'A';
904 ELSIF l_remainder = 1
905 THEN
906 l_cd_result := 'B';
907 ELSIF l_remainder = 2
908 THEN
909 l_cd_result := 'C';
910 ELSIF l_remainder = 3
911 THEN
912 l_cd_result := 'D';
913 ELSIF l_remainder = 4
914 THEN
915 l_cd_result := 'E';
916 ELSIF l_remainder = 5
917 THEN
918 l_cd_result := 'F';
919 ELSIF l_remainder = 6
920 THEN
921 l_cd_result := 'G';
922 ELSIF l_remainder = 7
923 THEN
924 l_cd_result := 'H';
925 ELSIF l_remainder = 8
926 THEN
927 l_cd_result := 'I';
928 ELSIF l_remainder = 9
929 THEN
930 l_cd_result := 'J';
931 ELSIF l_remainder = 10
932 THEN
933 l_cd_result := 'K';
934 ELSIF l_remainder = 11
935 THEN
936 l_cd_result := 'L';
937 ELSIF l_remainder = 12
938 THEN
939 l_cd_result := 'M';
940 ELSIF l_remainder = 13
941 THEN
942 l_cd_result := 'N';
943 ELSIF l_remainder = 14
944 THEN
945 l_cd_result := 'O';
946 ELSIF l_remainder = 15
947 THEN
948 l_cd_result := 'P';
949 ELSIF l_remainder = 16
950 THEN
951 l_cd_result := 'Q';
952 ELSIF l_remainder = 17
953 THEN
954 l_cd_result := 'R';
955 ELSIF l_remainder = 18
956 THEN
957 l_cd_result := 'S';
958 ELSIF l_remainder = 19
959 THEN
960 l_cd_result := 'T';
961 ELSIF l_remainder = 20
962 THEN
963 l_cd_result := 'U';
964 ELSIF l_remainder = 21
965 THEN
966 l_cd_result := 'V';
967 ELSIF l_remainder = 22
968 THEN
969 l_cd_result := 'W';
970 ELSIF l_remainder = 23
971 THEN
972 l_cd_result := 'X';
973 ELSIF l_remainder = 24
974 THEN
975 l_cd_result := 'Y';
976 ELSIF l_remainder = 25
977 THEN
978 l_cd_result := 'Z';
979 ELSE
980 l_cd_result := 'Error';
981 END IF;
982 RETURN l_cd_result;
983 end find_check_digit;
984 /** returns the weighting of the even-postitioned figures. **/
985 FUNCTION func_even_weighting(l_in_value VARCHAR2) RETURN NUMBER IS
986 l_even_result number(2);
987 BEGIN
988 IF l_in_value in ('A','0')
989 THEN
990 l_even_result := 0;
991 ELSIF l_in_value in ('B','1')
992 THEN
993 l_even_result := 1;
994 ELSIF l_in_value in ('C','2')
995 THEN
996 l_even_result := 2;
997 ELSIF l_in_value in ('D','3')
998 THEN
999 l_even_result := 3;
1000 ELSIF l_in_value in ('E','4')
1001 THEN
1002 l_even_result := 4;
1003 ELSIF l_in_value in ('F','5')
1004 THEN
1005 l_even_result := 5;
1006 ELSIF l_in_value in ('G','6')
1007 THEN
1008 l_even_result := 6;
1009 ELSIF l_in_value in ('H','7')
1010 THEN
1011 l_even_result := 7;
1012 ELSIF l_in_value in ('I','8')
1013 THEN
1014 l_even_result := 8;
1015 ELSIF l_in_value in ('J','9')
1016 THEN
1017 l_even_result := 9;
1018 ELSIF l_in_value = 'K'
1022 THEN
1019 THEN
1020 l_even_result := 10;
1021 ELSIF l_in_value = 'L'
1023 l_even_result := 11;
1024 ELSIF l_in_value = 'M'
1025 THEN
1026 l_even_result := 12;
1027 ELSIF l_in_value = 'N'
1028 THEN
1029 l_even_result := 13;
1030 ELSIF l_in_value = 'O'
1031 THEN
1032 l_even_result := 14;
1033 ELSIF l_in_value = 'P'
1034 THEN
1035 l_even_result := 15;
1036 ELSIF l_in_value = 'Q'
1037 THEN
1038 l_even_result := 16;
1039 ELSIF l_in_value = 'R'
1040 THEN
1041 l_even_result := 17;
1042 ELSIF l_in_value = 'S'
1043 THEN
1044 l_even_result := 18;
1045 ELSIF l_in_value = 'T'
1046 THEN
1047 l_even_result := 19;
1048 ELSIF l_in_value = 'U'
1049 THEN
1050 l_even_result := 20;
1051 ELSIF l_in_value = 'V'
1052 THEN
1053 l_even_result := 21;
1054 ELSIF l_in_value = 'W'
1055 THEN
1056 l_even_result := 22;
1057 ELSIF l_in_value = 'X'
1058 THEN
1059 l_even_result := 23;
1060 ELSIF l_in_value = 'Y'
1061 THEN
1062 l_even_result := 24;
1063 ELSIF l_in_value = 'Z'
1064 THEN
1065 l_even_result := 25;
1066 END IF;
1067 RETURN l_even_result;
1068 END func_even_weighting;
1069
1070 /** returns the weighting of the odd-postitioned figures. **/
1071 FUNCTION func_odd_weighting(l_odd_value VARCHAR2) RETURN NUMBER IS
1072 l_odd_result number(2);
1073 BEGIN
1074 IF l_odd_value in ('A','0')
1075 THEN
1076 l_odd_result := 1;
1077 ELSIF l_odd_value in ('B','1')
1078 THEN
1079 l_odd_result := 0;
1080 ELSIF l_odd_value in ('C','2')
1081 THEN
1082 l_odd_result := 5;
1083 ELSIF l_odd_value in ('D','3')
1084 THEN
1085 l_odd_result := 7;
1086 ELSIF l_odd_value in ('E','4')
1087 THEN
1088 l_odd_result := 9;
1089 ELSIF l_odd_value in ('F','5')
1090 THEN
1091 l_odd_result := 13;
1092 ELSIF l_odd_value in ('G','6')
1093 THEN
1094 l_odd_result := 15;
1095 ELSIF l_odd_value in ('H','7')
1096 THEN
1097 l_odd_result := 17;
1098 ELSIF l_odd_value in ('I','8')
1099 THEN
1100 l_odd_result := 19;
1101 ELSIF l_odd_value in ('J','9')
1102 THEN
1103 l_odd_result := 21;
1104 ELSIF l_odd_value = 'K'
1105 THEN
1106 l_odd_result := 2;
1107 ELSIF l_odd_value = 'L'
1108 THEN
1109 l_odd_result := 4;
1110 ELSIF l_odd_value = 'M'
1111 THEN
1112 l_odd_result := 18;
1113 ELSIF l_odd_value = 'N'
1114 THEN
1115 l_odd_result := 20;
1116 ELSIF l_odd_value = 'O'
1117 THEN
1118 l_odd_result := 11;
1119 ELSIF l_odd_value = 'P'
1123 THEN
1120 THEN
1121 l_odd_result := 15;
1122 ELSIF l_odd_value = 'Q'
1124 l_odd_result := 6;
1125 ELSIF l_odd_value = 'R'
1126 THEN
1127 l_odd_result := 8;
1128 ELSIF l_odd_value = 'S'
1129 THEN
1130 l_odd_result := 12;
1131 ELSIF l_odd_value = 'T'
1132 THEN
1133 l_odd_result := 14;
1134 ELSIF l_odd_value = 'U'
1135 THEN
1136 l_odd_result := 16;
1137 ELSIF l_odd_value = 'V'
1138 THEN
1139 l_odd_result := 10;
1140 ELSIF l_odd_value = 'W'
1141 THEN
1142 l_odd_result := 22;
1143 ELSIF l_odd_value = 'X'
1144 THEN
1145 l_odd_result := 25;
1146 ELSIF l_odd_value = 'Y'
1147 THEN
1148 l_odd_result := 24;
1149 ELSIF l_odd_value = 'Z'
1150 THEN
1151 l_odd_result := 23;
1152 END IF;
1153 RETURN l_odd_result;
1154 END func_odd_weighting;
1155 BEGIN
1156 x_return_status := FND_API.G_RET_STS_SUCCESS;
1157 IF p_registration_code <> 'FCIT'
1158 THEN
1159 RETURN;
1160 END IF;
1161 l_nif_value := p_registration_number;
1162 l_check_digit := substr(l_nif_value, length(l_nif_value));
1163 l_total_weighting := 0;
1164 l_position_weight := 0;
1165
1166 /** make sure that Fiscal code is only 16 chars - including Check digit **/
1167 IF length(l_nif_value) = 16
1168 THEN
1169 FOR l_position_i IN 1..15 LOOP
1170 /** moves along length of Fiscal Code and assigns weightings **/
1171 /** to each of the codes characters upto and including the 15th char **/
1172 /** on each loop the total of weightings is totalled **/
1173 l_char_value := substr(l_nif_value,l_position_i,1);
1174 IF l_position_i in (2,4,6,8,10,12,14)
1175 THEN
1176 l_position_weight := func_even_weighting(l_char_value);
1177 ELSE
1178 l_position_weight := func_odd_weighting(l_char_value);
1179 END IF;
1180 l_total_weighting := l_total_weighting + l_position_weight;
1181 END LOOP; /** of the counter position_i **/
1182 /** Divide the total by 23 and store the remainder into cal_check **/
1183 l_calc_check := MOD(l_total_weighting, 26);
1184 l_calc_cd := find_check_digit(l_calc_check);
1185 /*** After having calculated what should be the ITALIAN Fiscal ***/
1186 /*** Check digit compare to the actual and fail if not the same ***/
1187 IF l_calc_cd <> l_check_digit
1188 THEN
1189 x_return_status := FND_API.G_RET_STS_ERROR;
1190 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1191 THEN
1192 g_log_msg := 'Check Algorithm failed for '||
1193 p_registration_number;
1194 FND_LOG.STRING(G_LEVEL_STATEMENT,
1195 G_MODULE_NAME || l_api_name, g_log_msg);
1196 END IF;
1197 ELSE
1198 x_return_status := FND_API.G_RET_STS_SUCCESS;
1199 END IF;
1200 ELSIF length(l_nif_value) = 11 -- Bug No : 6884561 Start
1201 then
1202 /** Additional requirement of Italy BUG NO : 6884561 **/
1203 /** This is a new requirement. Italian Fiscal Codes may either be **/
1204 /** 16 OR 11 chars - if 11 then must pass the VAT Code validation **/
1205 /** routine - if 16 must be Fiscal Code for an individual which **/
1206 /** has this procedure to validate it **/
1207 PO_VALIDATE_VAT_IT(l_nif_value,
1208 p_required_flag,
1209 vat_ret_code,
1210 vat_ret_message);
1211 IF vat_ret_code = 'F'
1212 then
1213 x_return_status := FND_API.G_RET_STS_ERROR;
1214 ELSE
1215 x_return_status := FND_API.G_RET_STS_SUCCESS;
1216 x_msg_data := '';
1217 END IF;
1218 ELSE -- Bug No : 6884561 End
1219
1220 /** Fiscal code is incorrect length **/
1221 x_return_status := FND_API.G_RET_STS_ERROR;
1222 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1223 THEN
1224 g_log_msg := 'Registration number is of incorrect length '||
1225 p_registration_number;
1226 FND_LOG.STRING(G_LEVEL_STATEMENT,
1227 G_MODULE_NAME || l_api_name, g_log_msg);
1228 END IF;
1229 END IF;
1230
1231 IF x_return_status = FND_API.G_RET_STS_ERROR
1232 THEN
1233 FND_MESSAGE.SET_NAME('XLE', 'XLE_INVALID_REG_NUM_ERR');
1234 FND_MESSAGE.SET_TOKEN('REG_CODE', p_registration_code);
1235 FND_MESSAGE.SET_TOKEN('REG_NUM', p_registration_number);
1236 FND_MSG_PUB.Add;
1237 END IF;
1238 FND_MSG_PUB.Count_And_Get
1239 (p_count => x_msg_count,
1240 p_data => x_msg_data);
1241 EXCEPTION
1242 WHEN FND_API.G_EXC_ERROR THEN
1243 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1244 THEN
1245 g_log_msg := SQLERRM;
1246 FND_LOG.STRING(G_LEVEL_STATEMENT,
1247 G_MODULE_NAME || l_api_name, g_log_msg);
1248 END IF;
1249 x_return_status := FND_API.G_RET_STS_ERROR ;
1253 ( p_count => x_msg_count,
1250 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1251 l_api_name);
1252 FND_MSG_PUB.Count_And_Get
1254 p_data => x_msg_data
1255 );
1256 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1257 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1258 THEN
1259 g_log_msg := SQLERRM;
1260 FND_LOG.STRING(G_LEVEL_STATEMENT,
1261 G_MODULE_NAME || l_api_name, g_log_msg);
1262 END IF;
1263 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1264 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1265 l_api_name);
1266 FND_MSG_PUB.Count_And_Get
1267 ( p_count => x_msg_count,
1268 p_data => x_msg_data
1269 );
1270 WHEN OTHERS THEN
1271 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1272 THEN
1273 g_log_msg := SQLERRM;
1274 FND_LOG.STRING(G_LEVEL_STATEMENT,
1275 G_MODULE_NAME || l_api_name, g_log_msg);
1276 END IF;
1277 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1278 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1279 l_api_name);
1280 FND_MSG_PUB.Count_And_Get
1281 ( p_count => x_msg_count,
1282 p_data => x_msg_data
1283 );
1284 END do_it_regnum_validations;
1285 -- Perform Argentinian registration number validations
1286 -- Format usually is 99-99999999-9
1287 -- Ex valid number is - 10-00000000-6
1288 PROCEDURE do_ar_regnum_validations(
1289 p_legislative_cat_code IN VARCHAR2,
1290 p_required_flag IN VARCHAR2,
1291 p_registration_code IN VARCHAR2,
1292 p_registration_number IN VARCHAR2,
1293 x_return_status IN OUT NOCOPY VARCHAR2 ,
1294 x_msg_data IN OUT NOCOPY VARCHAR2 ,
1295 x_msg_count IN OUT NOCOPY NUMBER )
1296 IS
1297 l_nif_value xle_registrations.registration_number%TYPE;
1298 l_nif_num xle_registrations.registration_number%TYPE;
1299 l_check_digit VARCHAR2(2);
1300 l_val_digit VARCHAR2(2);
1301 l_api_name VARCHAR2(50) := 'do_ar_regnum_validations';
1302 BEGIN
1303 x_return_status := FND_API.G_RET_STS_SUCCESS;
1304 IF p_registration_code <> 'CUIT'
1305 THEN
1306 RETURN;
1307 END IF;
1308 l_nif_value := substr(p_registration_number, 1,
1309 (length(p_registration_number)-1));
1310 l_check_digit := substr(p_registration_number,
1311 length(p_registration_number));
1312 IF check_numeric_latin(l_nif_value) = '0'
1313 THEN
1314 -- Get only the digits, remove '/ ', '.' and '-'
1315 l_nif_num := TRANSLATE(l_nif_value, '0123456789/-.', '0123456789') ;
1316 IF LENGTH(l_nif_num) = 10 -- does not include check digit
1317 THEN
1321 (TO_NUMBER(SUBSTR(l_nif_num,7,1))) *5 +
1318 l_val_digit:=(11-MOD(((TO_NUMBER(SUBSTR(l_nif_num,10,1))) *2 +
1319 (TO_NUMBER(SUBSTR(l_nif_num,9,1))) *3 +
1320 (TO_NUMBER(SUBSTR(l_nif_num,8,1))) *4 +
1322 (TO_NUMBER(SUBSTR(l_nif_num,6,1))) *6 +
1323 (TO_NUMBER(SUBSTR(l_nif_num,5,1))) *7 +
1324 (TO_NUMBER(SUBSTR(l_nif_num,4,1))) *2 +
1325 (TO_NUMBER(SUBSTR(l_nif_num,3,1))) *3 +
1326 (TO_NUMBER(SUBSTR(l_nif_num,2,1))) *4 +
1327 (TO_NUMBER(SUBSTR(l_nif_num,1,1))) *5),11));
1328 IF l_val_digit ='10'
1329 THEN
1330 l_val_digit:='9';
1331 ELSIF l_val_digit = '11'
1332 THEN
1333 l_val_digit:='0';
1334 END IF;
1335 IF l_val_digit <> l_check_digit
1336 THEN
1337 x_return_status := FND_API.G_RET_STS_ERROR;
1338 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1339 THEN
1340 g_log_msg := 'Check Algorithm failed for '||
1341 p_registration_number;
1342 FND_LOG.STRING(G_LEVEL_STATEMENT,
1343 G_MODULE_NAME || l_api_name, g_log_msg);
1344 END IF;
1345 ELSE
1346 x_return_status := FND_API.G_RET_STS_SUCCESS;
1347 END IF;
1348 ELSE
1349 x_return_status := FND_API.G_RET_STS_ERROR;
1350 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1351 THEN
1352 g_log_msg := 'Incorrect length for reg number ' ||
1353 p_registration_number;
1354 FND_LOG.STRING(G_LEVEL_STATEMENT,
1355 G_MODULE_NAME || l_api_name, g_log_msg);
1356 END IF;
1357 END IF; -- Check length
1358 ELSE
1359 x_return_status := FND_API.G_RET_STS_ERROR;
1360 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1361 THEN
1362 g_log_msg := 'Non numeric characters found in reg num '||
1363 p_registration_number;
1364 FND_LOG.STRING(G_LEVEL_STATEMENT,
1365 G_MODULE_NAME || l_api_name, g_log_msg);
1366 END IF;
1367 END IF ; -- Numeric Value check
1368 IF x_return_status = FND_API.G_RET_STS_ERROR
1369 THEN
1370 FND_MESSAGE.SET_NAME('XLE', 'XLE_INVALID_REG_NUM_ERR');
1371 FND_MESSAGE.SET_TOKEN('REG_CODE', p_registration_code);
1372 FND_MESSAGE.SET_TOKEN('REG_NUM', p_registration_number);
1373 FND_MSG_PUB.Add;
1374 END IF;
1375 FND_MSG_PUB.Count_And_Get
1376 (p_count => x_msg_count,
1377 p_data => x_msg_data);
1378 EXCEPTION
1379 WHEN FND_API.G_EXC_ERROR THEN
1380 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1381 THEN
1382 g_log_msg := SQLERRM;
1383 FND_LOG.STRING(G_LEVEL_STATEMENT,
1384 G_MODULE_NAME || l_api_name, g_log_msg);
1385 END IF;
1386 x_return_status := FND_API.G_RET_STS_ERROR ;
1387 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1388 l_api_name);
1389 FND_MSG_PUB.Count_And_Get
1390 ( p_count => x_msg_count,
1391 p_data => x_msg_data
1392 );
1393 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1394 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1395 THEN
1396 g_log_msg := SQLERRM;
1397 FND_LOG.STRING(G_LEVEL_STATEMENT,
1398 G_MODULE_NAME || l_api_name, g_log_msg);
1399 END IF;
1400 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1401 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1402 l_api_name);
1403 FND_MSG_PUB.Count_And_Get
1404 ( p_count => x_msg_count,
1405 p_data => x_msg_data
1406 );
1407 WHEN OTHERS THEN
1408 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1409 THEN
1410 g_log_msg := SQLERRM;
1411 FND_LOG.STRING(G_LEVEL_STATEMENT,
1412 G_MODULE_NAME || l_api_name, g_log_msg);
1413 END IF;
1414 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1415 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1416 l_api_name);
1417 FND_MSG_PUB.Count_And_Get
1418 ( p_count => x_msg_count,
1419 p_data => x_msg_data
1420 );
1421 END do_ar_regnum_validations;
1422 -- Perform Chilean registration number validations
1423 -- Format usually is 99.999.999-X
1424 -- Eg valid number is - 000.000.000.001-9
1425 PROCEDURE do_cl_regnum_validations(
1426 p_legislative_cat_code IN VARCHAR2,
1427 p_required_flag IN VARCHAR2,
1428 p_registration_code IN VARCHAR2,
1429 p_registration_number IN VARCHAR2,
1430 x_return_status IN OUT NOCOPY VARCHAR2 ,
1431 x_msg_data IN OUT NOCOPY VARCHAR2 ,
1432 x_msg_count IN OUT NOCOPY NUMBER )
1433 IS
1434 l_nif_value xle_registrations.registration_number%TYPE;
1435 l_check_digit VARCHAR2(2);
1436 l_var1 xle_registrations.registration_number%TYPE;
1437 l_nif_num xle_registrations.registration_number%TYPE;
1438 l_val_digit VARCHAR2(2);
1439 l_api_name VARCHAR2(50) := 'do_cl_regnum_validations';
1440 BEGIN
1441 x_return_status := FND_API.G_RET_STS_SUCCESS;
1442 IF p_registration_code <> 'RUT'
1443 THEN
1444 RETURN;
1445 END IF;
1446 -- The last check digit for Chilean Reg Number can be 0-9 and K
1447 l_nif_value := substr(p_registration_number, 1,
1448 (length(p_registration_number)-1));
1449 l_check_digit := substr(p_registration_number,
1450 length(p_registration_number));
1451 IF check_numeric_latin(l_nif_value) = '0'
1452 THEN
1453 -- Get only the digits, remove '/ ', '.' and '-'
1454 l_nif_num := TRANSLATE(l_nif_value, '0123456789/-.', '0123456789') ;
1455 -- Number of digits should be <= 12
1456 IF LENGTH(l_nif_num) <= 12 -- l_nif_num does not include check digit
1457 THEN
1458 l_var1:=LPAD(l_nif_num,12,'0');
1459 l_val_digit:=(11-MOD(((TO_NUMBER(SUBSTR(l_var1,12,1))) *2 +
1460 (TO_NUMBER(SUBSTR(l_var1,11,1))) *3 +
1461 (TO_NUMBER(SUBSTR(l_var1,10,1))) *4 +
1462 (TO_NUMBER(SUBSTR(l_var1,9,1))) *5 +
1463 (TO_NUMBER(SUBSTR(l_var1,8,1))) *6 +
1464 (TO_NUMBER(SUBSTR(l_var1,7,1))) *7 +
1465 (TO_NUMBER(SUBSTR(l_var1,6,1))) *2 +
1466 (TO_NUMBER(SUBSTR(l_var1,5,1))) *3 +
1470 (TO_NUMBER(SUBSTR(l_var1,1,1))) *7),11));
1467 (TO_NUMBER(SUBSTR(l_var1,4,1))) *4 +
1468 (TO_NUMBER(SUBSTR(l_var1,3,1))) *5 +
1469 (TO_NUMBER(SUBSTR(l_var1,2,1))) *6 +
1471 IF l_val_digit = '10'
1472 THEN
1473 l_val_digit := 'K';
1474 ELSIF l_val_digit = '11'
1475 THEN
1476 l_val_digit := '0';
1477 END IF;
1478 IF l_val_digit <> l_check_digit
1479 THEN
1480 x_return_status := FND_API.G_RET_STS_ERROR;
1481 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1482 THEN
1483 g_log_msg := 'Check Algorithm failed for '||
1484 p_registration_number;
1485 FND_LOG.STRING(G_LEVEL_STATEMENT,
1486 G_MODULE_NAME || l_api_name, g_log_msg);
1487 END IF;
1488 ELSE
1489 x_return_status := FND_API.G_RET_STS_SUCCESS;
1490 END IF;
1491 ELSE
1492 x_return_status := FND_API.G_RET_STS_ERROR;
1493 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1494 THEN
1495 g_log_msg := 'Check length failed for '||
1496 p_registration_number;
1497 FND_LOG.STRING(G_LEVEL_STATEMENT,
1498 G_MODULE_NAME || l_api_name, g_log_msg);
1499 END IF;
1500 END IF; -- Check length
1501 ELSE
1502 x_return_status := FND_API.G_RET_STS_ERROR;
1503 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1504 THEN
1505 g_log_msg := 'Non numeric characters found in '||
1506 p_registration_number;
1507 FND_LOG.STRING(G_LEVEL_STATEMENT,
1508 G_MODULE_NAME || l_api_name, g_log_msg);
1509 END IF;
1510 END IF ; -- Numeric Value check
1511 IF x_return_status = FND_API.G_RET_STS_ERROR
1512 THEN
1513 FND_MESSAGE.SET_NAME('XLE', 'XLE_INVALID_REG_NUM_ERR');
1514 FND_MESSAGE.SET_TOKEN('REG_CODE', p_registration_code);
1515 FND_MESSAGE.SET_TOKEN('REG_NUM', p_registration_number);
1516 FND_MSG_PUB.Add;
1517 END IF;
1518 FND_MSG_PUB.Count_And_Get
1519 (p_count => x_msg_count,
1520 p_data => x_msg_data);
1521 EXCEPTION
1522 WHEN FND_API.G_EXC_ERROR THEN
1523 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1524 THEN
1525 g_log_msg := SQLERRM;
1526 FND_LOG.STRING(G_LEVEL_STATEMENT,
1527 G_MODULE_NAME || l_api_name, g_log_msg);
1528 END IF;
1529 x_return_status := FND_API.G_RET_STS_ERROR ;
1530 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1531 l_api_name);
1532 FND_MSG_PUB.Count_And_Get
1533 ( p_count => x_msg_count,
1534 p_data => x_msg_data
1535 );
1536 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1537 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1538 THEN
1539 g_log_msg := SQLERRM;
1540 FND_LOG.STRING(G_LEVEL_STATEMENT,
1541 G_MODULE_NAME || l_api_name, g_log_msg);
1542 END IF;
1543 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1544 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1545 l_api_name);
1546 FND_MSG_PUB.Count_And_Get
1547 ( p_count => x_msg_count,
1548 p_data => x_msg_data
1549 );
1550 WHEN OTHERS THEN
1551 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1552 THEN
1553 g_log_msg := SQLERRM;
1554 FND_LOG.STRING(G_LEVEL_STATEMENT,
1555 G_MODULE_NAME || l_api_name, g_log_msg);
1556 END IF;
1557 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1558 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1559 l_api_name);
1560 FND_MSG_PUB.Count_And_Get
1561 ( p_count => x_msg_count,
1562 p_data => x_msg_data
1563 );
1564 END do_cl_regnum_validations;
1565 -- Perform Colombian registration number validations
1566 -- Format usually is 99999999999999-9
1567 -- Eg valid number is - 0000000000001-8
1568 PROCEDURE do_co_regnum_validations(
1569 p_legislative_cat_code IN VARCHAR2,
1570 p_required_flag IN VARCHAR2,
1571 p_registration_code IN VARCHAR2,
1572 p_registration_number IN VARCHAR2,
1573 x_return_status IN OUT NOCOPY VARCHAR2 ,
1574 x_msg_data IN OUT NOCOPY VARCHAR2 ,
1575 x_msg_count IN OUT NOCOPY NUMBER )
1576 IS
1577 l_nif_value xle_registrations.registration_number%TYPE;
1578 l_check_digit VARCHAR2(2);
1579 l_var1 xle_registrations.registration_number%TYPE;
1580 l_nif_num xle_registrations.registration_number%TYPE;
1581 l_val_digit VARCHAR2(2);
1582 l_mod_value NUMBER(2);
1583 l_api_name VARCHAR2(50) := 'do_co_regnum_validations';
1584 BEGIN
1585 x_return_status := FND_API.G_RET_STS_SUCCESS;
1586 -- The last check digit for Colombian Reg Number can be 0-9
1587 l_nif_value := substr(p_registration_number, 1,
1588 (length(p_registration_number)-1));
1589 l_check_digit := substr(p_registration_number,
1590 length(p_registration_number));
1591 IF check_numeric_latin(l_nif_value) = '0'
1592 THEN
1593 -- Get only the digits, remove '/ ', '.' and '-'
1594 l_nif_num := TRANSLATE(l_nif_value, '0123456789/-.', '0123456789') ;
1595 -- Number of digits should be <= 14
1596 IF LENGTH(l_nif_num) <= 14 -- l_nif_num does not include check digit
1597 THEN
1598 l_var1 := LPAD(l_nif_num,15,'0');
1599 l_mod_value:=(MOD(((TO_NUMBER(SUBSTR(l_var1,15,1))) *3 +
1600 (TO_NUMBER(SUBSTR(l_var1,14,1))) *7 +
1601 (TO_NUMBER(SUBSTR(l_var1,13,1))) *13 +
1605 (TO_NUMBER(SUBSTR(l_var1,9,1))) *29 +
1602 (TO_NUMBER(SUBSTR(l_var1,12,1))) *17 +
1603 (TO_NUMBER(SUBSTR(l_var1,11,1))) *19 +
1604 (TO_NUMBER(SUBSTR(l_var1,10,1))) *23 +
1606 (TO_NUMBER(SUBSTR(l_var1,8,1))) *37 +
1607 (TO_NUMBER(SUBSTR(l_var1,7,1))) *41 +
1608 (TO_NUMBER(SUBSTR(l_var1,6,1))) *43 +
1609 (TO_NUMBER(SUBSTR(l_var1,5,1))) *47 +
1610 (TO_NUMBER(SUBSTR(l_var1,4,1))) *53 +
1611 (TO_NUMBER(SUBSTR(l_var1,3,1))) *59 +
1612 (TO_NUMBER(SUBSTR(l_var1,2,1))) *67 +
1613 (TO_NUMBER(SUBSTR(l_var1,1,1))) *71),11));
1614 IF (l_mod_value IN (1,0))
1615 THEN
1616 l_val_digit:=l_mod_value;
1617 ELSE
1618 l_val_digit:=11-l_mod_value;
1619 END IF;
1620 IF l_check_digit <> l_val_digit
1621 THEN
1622 x_return_status := FND_API.G_RET_STS_ERROR;
1623 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1624 THEN
1625 g_log_msg := 'Check Algorithm failed for '||
1626 p_registration_number;
1627 FND_LOG.STRING(G_LEVEL_STATEMENT,
1628 G_MODULE_NAME || l_api_name, g_log_msg);
1629 END IF;
1630 ELSE
1631 x_return_status := FND_API.G_RET_STS_SUCCESS;
1632 END IF;
1633 ELSE
1634 x_return_status := FND_API.G_RET_STS_ERROR;
1635 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1636 THEN
1637 g_log_msg := 'Check length failed for '||
1638 p_registration_number;
1639 FND_LOG.STRING(G_LEVEL_STATEMENT,
1640 G_MODULE_NAME || l_api_name, g_log_msg);
1641 END IF;
1642 END IF; -- Check length
1643 ELSE
1644 x_return_status := FND_API.G_RET_STS_ERROR;
1645 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1646 THEN
1647 g_log_msg := 'Non numeric characters found in ' ||
1648 p_registration_number;
1649 FND_LOG.STRING(G_LEVEL_STATEMENT,
1650 G_MODULE_NAME || l_api_name, g_log_msg);
1651 END IF;
1652 END IF;
1653 IF x_return_status = FND_API.G_RET_STS_ERROR
1654 THEN
1655 FND_MESSAGE.SET_NAME('XLE', 'XLE_INVALID_REG_NUM_ERR');
1656 FND_MESSAGE.SET_TOKEN('REG_CODE', p_registration_code);
1657 FND_MESSAGE.SET_TOKEN('REG_NUM', p_registration_number);
1658 FND_MSG_PUB.Add;
1659 END IF;
1660 FND_MSG_PUB.Count_And_Get
1661 (p_count => x_msg_count,
1662 p_data => x_msg_data);
1663 EXCEPTION
1664 WHEN FND_API.G_EXC_ERROR THEN
1665 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1666 THEN
1667 g_log_msg := SQLERRM;
1668 FND_LOG.STRING(G_LEVEL_STATEMENT,
1669 G_MODULE_NAME || l_api_name, g_log_msg);
1670 END IF;
1671 x_return_status := FND_API.G_RET_STS_ERROR ;
1672 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1673 l_api_name);
1674 FND_MSG_PUB.Count_And_Get
1675 ( p_count => x_msg_count,
1676 p_data => x_msg_data
1677 );
1678 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1679 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1680 THEN
1681 g_log_msg := SQLERRM;
1682 FND_LOG.STRING(G_LEVEL_STATEMENT,
1683 G_MODULE_NAME || l_api_name, g_log_msg);
1684 END IF;
1685 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1686 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1687 l_api_name);
1688 FND_MSG_PUB.Count_And_Get
1689 ( p_count => x_msg_count,
1690 p_data => x_msg_data
1691 );
1692 WHEN OTHERS THEN
1693 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1694 THEN
1695 g_log_msg := SQLERRM;
1696 FND_LOG.STRING(G_LEVEL_STATEMENT,
1697 G_MODULE_NAME || l_api_name, g_log_msg);
1698 END IF;
1699 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1700 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1701 l_api_name);
1702 FND_MSG_PUB.Count_And_Get
1703 ( p_count => x_msg_count,
1704 p_data => x_msg_data
1705 );
1706 END do_co_regnum_validations;
1707 -- Perform Brazilian registration number validations
1708 -- Example format is - 999.999.999/9999-99
1709 -- Eg valid number is - 000.000.001/0001-36
1710 PROCEDURE do_br_regnum_validations(
1711 p_legislative_cat_code IN VARCHAR2,
1712 p_required_flag IN VARCHAR2,
1713 p_registration_code IN VARCHAR2,
1714 p_registration_number IN VARCHAR2,
1715 x_return_status IN OUT NOCOPY VARCHAR2 ,
1716 x_msg_data IN OUT NOCOPY VARCHAR2 ,
1717 x_msg_count IN OUT NOCOPY NUMBER )
1718 IS
1719 l_trn_branch VARCHAR2(4);
1720 l_trn_digit VARCHAR2(2);
1721 l_control_digit_1 NUMBER;
1722 l_control_digit_2 NUMBER;
1723 l_control_digit_XX VARCHAR2(2);
1724 l_trn xle_registrations.registration_number%TYPE;
1725 l_api_name VARCHAR2(50) := 'do_br_regnum_validations';
1726 BEGIN
1727 x_return_status := FND_API.G_RET_STS_SUCCESS;
1728 IF p_registration_code IN ('CPF','CNPJ') THEN
1729 l_trn := TRANSLATE(p_registration_number, '0123456789/-.', '0123456789') ;
1733 ELSIF p_registration_code = 'CNPJ' THEN
1730 IF p_registration_code = 'CPF' THEN
1731 l_trn := lpad(l_trn,11,0);
1732 l_trn_digit := substr(l_trn,10,2);
1734 l_trn := lpad(l_trn,15,0);
1735 /* Tax Registration Branch */
1736 l_trn_branch := substr(l_trn,10,4);
1737 l_trn_digit := substr(l_trn,14,2);
1738 END IF;
1739 IF check_numeric(l_trn,1,length(l_trn)) <> '0' THEN
1740 x_return_status := FND_API.G_RET_STS_ERROR;
1741 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1742 THEN
1743 g_log_msg := 'Failed length check or non numeric ' ||
1744 ' characters found ' ||
1745 p_registration_number;
1746 FND_LOG.STRING(G_LEVEL_STATEMENT,
1747 G_MODULE_NAME || l_api_name, g_log_msg);
1748 END IF;
1749 ELSIF p_registration_code = 'CPF'
1750 THEN
1751 /* Validate CPF */
1752 --Calculate two digit controls of tax registration number CPF type
1753 l_control_digit_1 := (11 - mod(
1754 (to_number(substr(l_trn,9,1)) * 2 +
1755 to_number(substr(l_trn,8,1)) * 3 +
1756 to_number(substr(l_trn,7,1)) * 4 +
1757 to_number(substr(l_trn,6,1)) * 5 +
1758 to_number(substr(l_trn,5,1)) * 6 +
1759 to_number(substr(l_trn,4,1)) * 7 +
1760 to_number(substr(l_trn,3,1)) * 8 +
1761 to_number(substr(l_trn,2,1)) * 9 +
1762 to_number(substr(l_trn,1,1)) * 10),11));
1763 IF l_control_digit_1 in ('11','10')
1764 THEN
1765 l_control_digit_1 := 0;
1766 END IF;
1767 l_control_digit_2 := (11 - mod((l_control_digit_1 * 2 +
1768 to_number(substr(l_trn,9,1)) * 3 +
1769 to_number(substr(l_trn,8,1)) * 4 +
1770 to_number(substr(l_trn,7,1)) * 5 +
1771 to_number(substr(l_trn,6,1)) * 6 +
1772 to_number(substr(l_trn,5,1)) * 7 +
1773 to_number(substr(l_trn,4,1)) * 8 +
1774 to_number(substr(l_trn,3,1)) * 9 +
1775 to_number(substr(l_trn,2,1)) * 10 +
1776 to_number(substr(l_trn,1,1)) * 11),11));
1777 IF l_control_digit_2 in ('11','10')
1778 THEN
1779 l_control_digit_2 := 0;
1780 END IF;
1781 l_control_digit_XX := substr(to_char(l_control_digit_1),1,1) ||
1782 substr(to_char(l_control_digit_2),1,1);
1783 IF l_control_digit_XX <> l_trn_digit
1784 THEN
1785 /* Digit controls do not match */
1786 x_return_status:= FND_API.G_RET_STS_ERROR;
1787 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1788 THEN
1789 g_log_msg := 'Check Algorithm failed for '||
1790 p_registration_number;
1791 FND_LOG.STRING(G_LEVEL_STATEMENT,
1792 G_MODULE_NAME || l_api_name, g_log_msg);
1793 END IF;
1794 ELSE
1795 x_return_status:= FND_API.G_RET_STS_SUCCESS;
1796 END IF;
1797 ELSIF p_registration_code = 'CNPJ' THEN
1798 /* Calculate two digit controls of registration number CNPJ type */
1799 l_control_digit_1 := (11 - mod(
1800 (to_number(substr(l_trn_branch,4,1)) * 2 +
1801 to_number(substr(l_trn_branch,3,1)) * 3 +
1802 to_number(substr(l_trn_branch,2,1)) * 4 +
1803 to_number(substr(l_trn_branch,1,1)) * 5 +
1804 to_number(substr(l_trn,9,1)) * 6 +
1805 to_number(substr(l_trn,8,1)) * 7 +
1806 to_number(substr(l_trn,7,1)) * 8 +
1807 to_number(substr(l_trn,6,1)) * 9 +
1808 to_number(substr(l_trn,5,1)) * 2 +
1809 to_number(substr(l_trn,4,1)) * 3 +
1810 to_number(substr(l_trn,3,1)) * 4 +
1811 to_number(substr(l_trn,2,1))* 5),11));
1812 IF l_control_digit_1 in ('11','10')
1813 THEN
1814 l_control_digit_1 := 0;
1815 END IF;
1816 l_control_digit_2 := (11 - mod(
1817 ( (l_control_digit_1 * 2) +
1818 to_number(substr(l_trn_branch,4,1)) * 3 +
1819 to_number(substr(l_trn_branch,3,1)) * 4 +
1820 to_number(substr(l_trn_branch,2,1)) * 5 +
1821 to_number(substr(l_trn_branch,1,1)) * 6 +
1822 to_number(substr(l_trn,9,1)) * 7 +
1823 to_number(substr(l_trn,8,1)) * 8 +
1824 to_number(substr(l_trn,7,1)) * 9 +
1825 to_number(substr(l_trn,6,1)) * 2 +
1826 to_number(substr(l_trn,5,1)) * 3 +
1827 to_number(substr(l_trn,4,1)) * 4 +
1828 to_number(substr(l_trn,3,1)) * 5 +
1829 to_number(substr(l_trn,2,1)) * 6),11));
1830 IF l_control_digit_2 in ('11','10')
1831 THEN
1832 l_control_digit_2 := 0;
1833 END IF;
1834 l_control_digit_XX := substr(to_char(l_control_digit_1),1,1) ||
1835 substr(to_char(l_control_digit_2),1,1);
1836 IF l_trn_digit <> l_control_digit_XX
1837 THEN
1838 x_return_status:= FND_API.G_RET_STS_ERROR;
1842 p_registration_number;
1839 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1840 THEN
1841 g_log_msg := 'Check Algorithm failed for '||
1843 FND_LOG.STRING(G_LEVEL_STATEMENT,
1844 G_MODULE_NAME || l_api_name, g_log_msg);
1845 END IF;
1846 ELSE
1847 x_return_status:= FND_API.G_RET_STS_SUCCESS;
1848 END IF;
1849 END IF;
1850 IF x_return_status = FND_API.G_RET_STS_ERROR
1851 THEN
1852 FND_MESSAGE.SET_NAME('XLE', 'XLE_INVALID_REG_NUM_ERR');
1853 FND_MESSAGE.SET_TOKEN('REG_CODE', p_registration_code);
1854 FND_MESSAGE.SET_TOKEN('REG_NUM', p_registration_number);
1855 FND_MSG_PUB.Add;
1856 END IF;
1857 FND_MSG_PUB.Count_And_Get
1858 (p_count => x_msg_count,
1859 p_data => x_msg_data);
1860 END IF;
1861 EXCEPTION
1862 WHEN FND_API.G_EXC_ERROR THEN
1863 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1864 THEN
1865 g_log_msg := SQLERRM;
1866 FND_LOG.STRING(G_LEVEL_STATEMENT,
1867 G_MODULE_NAME || l_api_name, g_log_msg);
1868 END IF;
1869 x_return_status := FND_API.G_RET_STS_ERROR ;
1870 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1871 l_api_name);
1872 FND_MSG_PUB.Count_And_Get
1873 ( p_count => x_msg_count,
1874 p_data => x_msg_data
1875 );
1876 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1877 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1878 THEN
1879 g_log_msg := SQLERRM;
1880 FND_LOG.STRING(G_LEVEL_STATEMENT,
1881 G_MODULE_NAME || l_api_name, g_log_msg);
1882 END IF;
1883 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1884 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1885 l_api_name);
1886 FND_MSG_PUB.Count_And_Get
1887 ( p_count => x_msg_count,
1888 p_data => x_msg_data
1889 );
1890 WHEN OTHERS THEN
1891 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL)
1892 THEN
1893 g_log_msg := SQLERRM;
1894 FND_LOG.STRING(G_LEVEL_STATEMENT,
1895 G_MODULE_NAME || l_api_name, g_log_msg);
1896 END IF;
1897 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1898 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
1899 l_api_name);
1900 FND_MSG_PUB.Count_And_Get
1901 ( p_count => x_msg_count,
1902 p_data => x_msg_data
1903 );
1904 END do_br_regnum_validations;
1905 END XLE_REGISTRATIONS_VAL_PVT;