[Home] [Help]
PACKAGE BODY: APPS.ZX_TRN_VALIDATION_PKG
Source
1 package body ZX_TRN_VALIDATION_PKG AS
2 /* $Header: zxctaxregnb.pls 120.32.12010000.5 2008/12/16 11:36:28 ssanka ship $ */
3
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) := 'ZX.PLSQL.ZX_TRN_VALIDATION_PKG.';
13 -- Logging Infra
14
15 FUNCTION common_check_numeric(p_check_value IN VARCHAR2,
16 p_from IN NUMBER,
17 p_for IN NUMBER) RETURN VARCHAR2
18 IS
19 num_check VARCHAR2(40);
20
21 BEGIN
22
23 num_check := '1';
24 num_check := nvl( rtrim(
25 translate( substr(p_check_value,p_from,p_for),
26 '1234567890',
27 ' ' ) ), '0' );
28
29 RETURN(num_check);
30 END common_check_numeric;
31
32 /**************** end of FUNCTION common_check_numeric *******************/
33
34 FUNCTION common_check_length(p_country_code IN VARCHAR2,
35 p_num_digits IN NUMBER,
36 p_trn IN VARCHAR2) RETURN VARCHAR2 IS
37
38 l_max_digits NUMBER(3);
39
40 BEGIN
41
42 l_max_digits:=lengthb(p_trn);
43 IF (p_country_code = 'AR' AND (l_max_digits = p_num_digits)) THEN
44 RETURN('TRUE');
45 ELSIF (p_country_code='CL' AND (l_max_digits <= p_num_digits)) THEN
46 RETURN('TRUE');
47 ELSIF (p_country_code='CO' AND (l_max_digits <= p_num_digits)) THEN
48 RETURN('TRUE');
49 ELSIF (p_country_code='TW' AND (l_max_digits = p_num_digits)) THEN
50 RETURN('TRUE');
51 ELSE
52 RETURN ('FALSE');
53 END IF;
54
55 END common_check_length;
56
57 /**************** end of FUNCTION common_check_length *******************/
58
59 PROCEDURE validate_trn(p_country_code IN VARCHAR2,
60 p_tax_reg_num IN VARCHAR2,
61 p_tax_regime_code IN VARCHAR2,
62 p_tax IN VARCHAR2,
63 p_tax_jurisdiction_code IN VARCHAR2,
64 p_ptp_id IN NUMBER,
65 p_party_type_code IN VARCHAR2,
66 p_trn_type IN VARCHAR2,
67 p_error_buffer OUT NOCOPY VARCHAR2,
68 p_return_status OUT NOCOPY VARCHAR2,
69 x_party_type_token OUT NOCOPY VARCHAR2,
70 x_party_name_token OUT NOCOPY VARCHAR2,
71 x_party_site_name_token OUT NOCOPY VARCHAR2 )
72 AS
73
74 l_registration_id NUMBER;
75 l_ptp_id NUMBER;
76 l_party_type_code VARCHAR2(30);
77 l_pass_unique_check VARCHAR2(1);
78 l_country_code VARCHAR2(4);
79 l_trn VARCHAR2(50);
80 l_trn_type VARCHAR2(30);
81 l_tax VARCHAR2(30);
82 l_tax_regime_code VARCHAR2(30);
83 l_tax_jurisdiction_code VARCHAR2(30);
84 l_allow_regn_num_flag VARCHAR2(1);
85 l_header_reg_num VARCHAR2(50);
86 l_result_message VARCHAR2(40);
87 l_header_check VARCHAR2(1);
88
89 l_party_id NUMBER;
90 l_party_type VARCHAR2(1000);
91 l_party_name VARCHAR2(5000);
92 l_party_site_name VARCHAR2(5000);
93 l_custom NUMBER;
94 l_total_count NUMBER;
95 l_specific_count NUMBER;
96
97 -- Logging Infra
98 l_procedure_name CONSTANT VARCHAR2(30) := 'validate_trn';
99 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
100
101 CURSOR tax_reg_num_csr(c_registration_number varchar2, c_party_type_code varchar2, c_tax_regime_code varchar2, c_tax varchar2, c_tax_jurisdiction_code varchar2) IS
102
103 SELECT distinct ptp.party_id
104 FROM zx_registrations reg,
105 zx_party_tax_profile ptp
106 WHERE ptp.party_tax_profile_id = reg.party_tax_profile_id
107 AND reg.registration_number = c_registration_number
108 AND sysdate >= reg.effective_from
109 AND (sysdate <= reg.effective_to OR reg.effective_to IS NULL)
110 AND ptp.party_type_code = c_party_type_code
111 AND ((c_tax_regime_code IS NULL)
112 OR
113 (reg.tax_regime_code IS NULL)
114 OR
115 (c_tax_regime_code IS NOT NULL
116 AND reg.tax_regime_code = c_tax_regime_code))
117 AND ((c_tax IS NULL)
118 OR
119 (reg.tax IS NULL)
120 OR
121 (c_tax IS NOT NULL
122 AND reg.tax = c_tax))
123 AND ((c_tax_jurisdiction_code IS NULL)
124 OR
125 (reg.tax_jurisdiction_code IS NULL)
126 OR
127 (c_tax_jurisdiction_code IS NOT NULL
128 AND reg.tax_jurisdiction_code = c_tax_jurisdiction_code));
129
130
131 CURSOR ptp_type_csr(c_ptp_id number) IS
132
133 SELECT distinct party_type_code
134 FROM zx_party_tax_profile
135 WHERE party_tax_profile_id = c_ptp_id;
136
137
138 CURSOR c_AllowDupRegnNum IS
139
140 SELECT allow_dup_regn_num_flag
141 FROM zx_taxes_b
142 WHERE tax_regime_code = p_tax_regime_code
143 AND tax = p_tax;
144
145
146 CURSOR c_third_party_reg_num (c_ptp_id number, c_tax_reg_num varchar2) IS
147 select registration_number
148 from zx_registrations
149 where party_tax_profile_id = (select s.party_tax_profile_id
150 from zx_party_tax_profile s,
151 zx_party_tax_profile ptp,
152 hz_party_sites hzps
153 where ptp.party_tax_profile_id = c_ptp_id
154 and ptp.party_id = hzps.party_site_id
155 and hzps.party_id = s.party_id
156 and s.party_type_code = 'THIRD_PARTY')
157 and registration_number = c_tax_reg_num;
158
159 CURSOR c_establishment_reg_num (c_ptp_id number, c_tax_reg_num varchar2) IS
160
161 select regt.registration_number
162 from zx_registrations regt
163 where regt.party_tax_profile_id = (select ptpp.party_tax_profile_id
164 from xle_fp_establishment_v est,
165 xle_fp_establishment_v estp,
166 zx_party_tax_profile ptp,
167 zx_party_tax_profile ptpp
168 where estp.party_id = ptpp.party_id
169 and estp.legal_entity_id = est.legal_entity_id
170 and est.party_id = ptp.party_id
171 and ptp.party_tax_profile_id = c_ptp_id
172 and estp.main_establishment_flag = 'Y'
173 and ptpp.party_type_code = 'LEGAL_ESTABLISHMENT')
174 and regt.registration_number = c_tax_reg_num;
175
176
177 /**************************/
178 /* SuB-Procedures Section */
179 /**************************/
180
181 ----------------------------------------------------------------
182 PROCEDURE unique_trn(p_tax_reg_num IN VARCHAR2,
183 p_party_type_code IN VARCHAR2,
184 p_tax_regime_code IN VARCHAR2,
185 p_tax IN VARCHAR2,
186 p_tax_jurisdiction_code IN VARCHAR2,
187 x_trn_unique_result OUT NOCOPY VARCHAR2) IS
188 ----------------------------------------------------------------
189 l_dummy varchar2(1);
190 BEGIN
191
192 OPEN tax_reg_num_csr(p_tax_reg_num, p_party_type_code, p_tax_regime_code, p_tax, p_tax_jurisdiction_code);
193 LOOP
194 FETCH tax_reg_num_csr into l_party_id ;
195
196 IF tax_reg_num_csr%NOTFOUND THEN
197 -- No data found. Therfore, new tax registration number will be unique.
198 x_trn_unique_result := FND_API.G_RET_STS_SUCCESS;
199 close tax_reg_num_csr;
200 EXIT;
201 ELSE
202 -- Data found. Therfore, new tax registration number will NOT be unique.
203 x_trn_unique_result := FND_API.G_RET_STS_ERROR;
204 close tax_reg_num_csr;
205 EXIT;
206 END IF;
207
208 END LOOP;
209
210 END unique_trn;
211
212 --------------------------------------------------------------------------
213 PROCEDURE display_msg(p_tax_reg_num IN VARCHAR2,
214 p_party_type_code IN VARCHAR2,
215 p_tax_regime_code IN VARCHAR2,
216 p_tax IN VARCHAR2,
217 p_tax_jurisdiction_code IN VARCHAR2,
218 x_party_type_token OUT NOCOPY VARCHAR2,
219 x_party_name_token OUT NOCOPY VARCHAR2,
220 x_party_site_name_token OUT NOCOPY VARCHAR2) IS
221 --------------------------------------------------------------------------
222 -- Logging Infra:
223 l_procedure_name CONSTANT VARCHAR2(30) := 'display_msg';
224 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
225
226 l_party_name VARCHAR2(5000) := NULL;
227 l_party_site_name VARCHAR2(5000) := NULL;
228
229 BEGIN
230
231 -- Logging Infra: Procedure level
232 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
233 l_log_msg := l_procedure_name||'(+)';
234 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
235 END IF;
236
237 -- Logging Infra: Statement level
238 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
239 l_log_msg := 'Parameters ';
240 l_log_msg := l_log_msg||'P_tax_erg_num: '||p_tax_reg_num;
241 l_log_msg := l_log_msg||'P_party_type_code: '||p_party_type_code;
242 l_log_msg := l_log_msg||'P_tax_regime_code: '||p_tax_regime_code;
243 l_log_msg := l_log_msg||'P_tax: '||p_tax;
244 l_log_msg := l_log_msg||'P_tax_jurisdiction_code: '||p_tax_jurisdiction_code;
245 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
246 END IF;
247 -- Logging Infra: Statement level
248
249 SELECT meaning
250 INTO x_party_type_token
251 FROM fnd_lookups
252 WHERE lookup_type = 'ZX_PTP_PARTY_TYPE'
253 AND lookup_code = p_party_type_code;
254
255 OPEN tax_reg_num_csr(p_tax_reg_num, p_party_type_code, p_tax_regime_code, p_tax, p_tax_jurisdiction_code);
256 LOOP
257 FETCH tax_reg_num_csr into l_party_id ;
258
259 -- Exit loop when there are no more rows to fetch
260 EXIT WHEN tax_reg_num_csr%NOTFOUND;
261
262 IF p_party_type_code = 'THIRD_PARTY' THEN
263
264 SELECT distinct party_name
265 INTO l_party_name
266 FROM hz_parties
267 WHERE party_id = l_party_id;
268
269 if x_party_name_token is null then
270 x_party_name_token := x_party_name_token ||' '|| l_party_name;
271 else
272 x_party_name_token := x_party_name_token ||'; '|| l_party_name;
273 end if;
274 x_party_site_name_token := NULL;
275
276 ELSIF p_party_type_code = 'THIRD_PARTY_SITE' THEN
277
278 SELECT distinct p.party_name, nvl(ps.party_site_name, p.address1||' '||p.address2||' '||p.address3||' '||p.address4||' '||p.city||' '||p.state) party_site_name
279 INTO l_party_name, l_party_site_name
280 FROM hz_parties p,
281 hz_party_sites ps
282 WHERE p.party_id = ps.party_id
283 AND ps.party_site_id = l_party_id;
284
285 if x_party_name_token is null then
286 x_party_name_token := x_party_name_token ||' '|| l_party_name;
287 else
288 x_party_name_token := x_party_name_token ||'; '|| l_party_name;
289 end if;
290 if x_party_name_token is null then
291 x_party_site_name_token := x_party_site_name_token ||' '|| l_party_site_name;
292 else
293 x_party_site_name_token := x_party_site_name_token ||'; '|| l_party_site_name;
294 end if;
295
296 ELSIF p_party_type_code = 'LEGAL_ESTABLISHMENT' THEN
297
298 SELECT distinct establishment_name
299 INTO l_party_name
300 FROM xle_fp_establishment_v
301 WHERE party_id = l_party_id;
302
303 x_party_name_token := x_party_name_token ||' '|| l_party_name;
304 x_party_site_name_token := NULL;
305 ELSE
306 x_party_name_token := NULL;
307 x_party_site_name_token := NULL;
308 END IF;
309
310 END LOOP;
311 CLOSE tax_reg_num_csr;
312
313 EXCEPTION
314 WHEN INVALID_CURSOR THEN
315 if tax_reg_num_csr%isopen then close tax_reg_num_csr; end if;
316
317 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
318 FND_LOG.STRING(G_LEVEL_STATEMENT,
319 G_MODULE_NAME || l_procedure_name,
320 SQLCODE || ': ' || SQLERRM);
321 END IF;
322
323 WHEN OTHERS THEN
324 if tax_reg_num_csr%isopen then close tax_reg_num_csr; end if;
325
326 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
327 FND_LOG.STRING(G_LEVEL_STATEMENT,
328 G_MODULE_NAME || l_procedure_name,
329 SQLCODE || ': ' || SQLERRM);
330 END IF;
331
332 END display_msg;
333
334 -- Bug 6774002
335 --------------------------------------------------------------------------
336 PROCEDURE display_cross_msg(p_tax_reg_num IN VARCHAR2,
337 p_party_type_code IN VARCHAR2,
338 p_ptp_id IN NUMBER,
339 p_tax_regime_code IN VARCHAR2,
340 p_tax IN VARCHAR2,
341 p_tax_jurisdiction_code IN VARCHAR2,
342 x_party_type_token OUT NOCOPY VARCHAR2,
343 x_party_name_token OUT NOCOPY VARCHAR2,
344 x_party_site_name_token OUT NOCOPY VARCHAR2) IS
345 --------------------------------------------------------------------------
346 l_procedure_name CONSTANT VARCHAR2(30) := 'display_cross_msg';
347 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
348 l_party_name VARCHAR2(5000) := NULL;
349 l_party_site_name VARCHAR2(5000) := NULL;
350
351 BEGIN
352
353 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
354 l_log_msg := l_procedure_name||'(+)';
355 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
356 END IF;
357
358 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
359 l_log_msg := 'Parameters ';
360 l_log_msg := l_log_msg||'P_tax_erg_num: '||p_tax_reg_num;
361 l_log_msg := l_log_msg||'p_ptp_id: '||p_ptp_id;
362 l_log_msg := l_log_msg||'P_party_type_code: '||p_party_type_code;
363 l_log_msg := l_log_msg||'P_tax_regime_code: '||p_tax_regime_code;
364 l_log_msg := l_log_msg||'P_tax: '||p_tax;
365 l_log_msg := l_log_msg||'P_tax_jurisdiction_code: '||p_tax_jurisdiction_code;
366 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
367 END IF;
368
369 IF p_party_type_code = 'THIRD_PARTY' THEN
370 SELECT meaning
371 INTO x_party_type_token
372 FROM fnd_lookups
376 SELECT HZP.PARTY_NAME,
373 WHERE lookup_type = 'ZX_PTP_PARTY_TYPE'
374 AND lookup_code = 'THIRD_PARTY_SITE';
375
377 NVL(HZS.PARTY_SITE_NAME, HZP.ADDRESS1||' '||
378 HZP.ADDRESS2||' '||
379 HZP.ADDRESS3||' '||
380 HZP.ADDRESS4||' '||
381 HZP.CITY||' '||
382 HZP.STATE) PARTY_SITE_NAME
383 INTO l_party_name, l_party_site_name
384 FROM ZX_REGISTRATIONS REG,
385 HZ_PARTY_SITES HZS,
386 HZ_PARTIES HZP,
387 ZX_PARTY_TAX_PROFILE ZXP
388 WHERE HZP.PARTY_ID = HZS.PARTY_ID
389 AND HZS.PARTY_SITE_ID = ZXP.PARTY_ID
390 AND ZXP.PARTY_TAX_PROFILE_ID = REG.PARTY_TAX_PROFILE_ID
391 AND ZXP.PARTY_TYPE_CODE = 'THIRD_PARTY_SITE'
392 AND REG.PARTY_TAX_PROFILE_ID NOT IN (SELECT S.PARTY_TAX_PROFILE_ID
393 FROM ZX_PARTY_TAX_PROFILE S,
394 ZX_PARTY_TAX_PROFILE PTP,
395 HZ_PARTY_SITES HZPS
396 WHERE PTP.PARTY_TAX_PROFILE_ID = p_ptp_id
397 AND PTP.PARTY_ID = HZPS.PARTY_ID
398 AND HZPS.PARTY_SITE_ID = S.PARTY_ID
399 AND S.PARTY_TYPE_CODE = 'THIRD_PARTY_SITE')
400 AND REG.REGISTRATION_NUMBER = p_tax_reg_num
401 AND SYSDATE >= REG.EFFECTIVE_FROM
402 AND (SYSDATE <= REG.EFFECTIVE_TO OR REG.EFFECTIVE_TO IS NULL)
403 AND ((p_tax_regime_code IS NULL) OR (REG.TAX_REGIME_CODE IS NULL)
404 OR (p_tax_regime_code IS NOT NULL AND REG.TAX_REGIME_CODE = p_tax_regime_code))
405 AND ((p_tax IS NULL) OR (REG.TAX IS NULL)
406 OR (p_tax IS NOT NULL AND REG.TAX = p_tax))
407 AND ((p_tax_jurisdiction_code IS NULL) OR (REG.TAX_JURISDICTION_CODE IS NULL)
408 OR (p_tax_jurisdiction_code IS NOT NULL AND REG.TAX_JURISDICTION_CODE = p_tax_jurisdiction_code))
409 AND ROWNUM = 1;
410
411 if x_party_name_token is null then
412 x_party_name_token := x_party_name_token ||' '|| l_party_name;
413 else
414 x_party_name_token := x_party_name_token ||'; '|| l_party_name;
415 end if;
416 if x_party_name_token is null then
417 x_party_site_name_token := x_party_site_name_token ||' '|| l_party_site_name;
418 else
419 x_party_site_name_token := x_party_site_name_token ||'; '|| l_party_site_name;
420 end if;
421 ELSIF p_party_type_code = 'THIRD_PARTY_SITE' THEN
422 SELECT meaning
423 INTO x_party_type_token
424 FROM fnd_lookups
425 WHERE lookup_type = 'ZX_PTP_PARTY_TYPE'
426 AND lookup_code = 'THIRD_PARTY';
427
428 SELECT HZP.PARTY_NAME
429 INTO l_party_name
430 FROM ZX_REGISTRATIONS REG,
431 HZ_PARTIES HZP,
432 ZX_PARTY_TAX_PROFILE ZXP
433 WHERE HZP.PARTY_ID = ZXP.PARTY_ID
434 AND ZXP.PARTY_TAX_PROFILE_ID = REG.PARTY_TAX_PROFILE_ID
435 AND ZXP.PARTY_TYPE_CODE = 'THIRD_PARTY'
436 AND REG.PARTY_TAX_PROFILE_ID NOT IN (SELECT S.PARTY_TAX_PROFILE_ID
437 FROM ZX_PARTY_TAX_PROFILE S,
438 ZX_PARTY_TAX_PROFILE PTP,
439 HZ_PARTY_SITES HZPS
440 WHERE PTP.PARTY_TAX_PROFILE_ID = p_ptp_id
441 AND PTP.PARTY_ID = HZPS.PARTY_SITE_ID
442 AND HZPS.PARTY_ID = S.PARTY_ID
443 AND S.PARTY_TYPE_CODE = 'THIRD_PARTY')
444 AND REG.REGISTRATION_NUMBER = p_tax_reg_num
445 AND SYSDATE >= REG.EFFECTIVE_FROM
446 AND (SYSDATE <= REG.EFFECTIVE_TO OR REG.EFFECTIVE_TO IS NULL)
447 AND ((p_tax_regime_code IS NULL) OR (REG.TAX_REGIME_CODE IS NULL)
448 OR (p_tax_regime_code IS NOT NULL AND REG.TAX_REGIME_CODE = p_tax_regime_code))
449 AND ((p_tax IS NULL) OR (REG.TAX IS NULL)
450 OR (p_tax IS NOT NULL AND REG.TAX = p_tax))
451 AND ((p_tax_jurisdiction_code IS NULL) OR (REG.TAX_JURISDICTION_CODE IS NULL)
452 OR (p_tax_jurisdiction_code IS NOT NULL AND REG.TAX_JURISDICTION_CODE = p_tax_jurisdiction_code))
453 AND ROWNUM = 1;
454
455 if x_party_name_token is null then
456 x_party_name_token := x_party_name_token ||' '|| l_party_name;
457 else
458 x_party_name_token := x_party_name_token ||'; '|| l_party_name;
459 end if;
460 x_party_site_name_token := NULL;
461 END IF;
462 EXCEPTION
463 WHEN OTHERS THEN
464 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
465 FND_LOG.STRING(G_LEVEL_STATEMENT,
466 G_MODULE_NAME || l_procedure_name,
467 SQLCODE || ': ' || SQLERRM);
468 END IF;
469 END display_cross_msg;
470 -- Bug 6774002
471
472 -- Bug 3650600
473 --------------------------------------------------------------------------
474 PROCEDURE validate_header_trn(p_party_type_code IN VARCHAR2,
475 p_ptp_id IN NUMBER,
479
476 p_tax_reg_num IN VARCHAR2,
477 x_return_status OUT NOCOPY VARCHAR2) IS
478 --------------------------------------------------------------------------
480 -- Logging Infra:
481 l_procedure_name CONSTANT VARCHAR2(30) := 'validate_header_trn';
482 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
483
484 BEGIN
485
486 -- Logging Infra: Procedure level
487 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
488 l_log_msg := l_procedure_name||'(+)';
489 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
490 END IF;
491
492 -- Logging Infra: Statement level
493 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
494 l_log_msg := 'Parameters ';
495 l_log_msg := l_log_msg||'P_Party_Type_Code: '||p_party_type_code;
496 l_log_msg := l_log_msg||'P_ptp_id: '||to_char(p_ptp_id);
497 l_log_msg := l_log_msg||'P_Tax_Reg_Num: '||p_tax_reg_num;
498 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
499 END IF;
500 -- Logging Infra: Statement level
501 -- Third party changes
502 IF p_party_type_code = 'THIRD_PARTY_SITE' THEN
503
504 OPEN c_third_party_reg_num(p_ptp_id, p_tax_reg_num);
505 LOOP
506 FETCH c_third_party_reg_num INTO l_header_reg_num;
507
508 IF c_third_party_reg_num%NOTFOUND THEN
509 x_return_status := FND_API.G_RET_STS_ERROR;
510 CLOSE c_third_party_reg_num;
511 EXIT;
512
513 -- Logging Infra: Statement level
514 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
515 l_log_msg := 'The Tax Registration Number does not exist in header level as Third Party.';
516 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
517 END IF;
518 -- Logging Infra: Statement level
519
520 ELSE
521 x_return_status := FND_API.G_RET_STS_SUCCESS;
522 CLOSE c_third_party_reg_num;
523 EXIT;
524 END IF;
525 END LOOP;
526 -- Third Party Changes
527 ELSIF p_party_type_code = 'LEGAL_ESTABLISHMENT' THEN
528
529 OPEN c_establishment_reg_num(p_ptp_id, p_tax_reg_num);
530 LOOP
531 FETCH c_establishment_reg_num INTO l_header_reg_num;
532
533 IF c_establishment_reg_num%NOTFOUND THEN
534 x_return_status := FND_API.G_RET_STS_ERROR;
535 CLOSE c_establishment_reg_num;
536 EXIT;
537
538 -- Logging Infra: Statement level
539 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
540 l_log_msg := 'The Tax Registration Number does not exist in header level as 1st Establishment.';
541 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
542 END IF;
543 -- Logging Infra: Statement level
544
545 ELSE
546 x_return_status := FND_API.G_RET_STS_SUCCESS;
547 CLOSE c_establishment_reg_num;
548 EXIT;
549 END IF;
550 END LOOP;
551
552 ELSE
553 x_return_status := FND_API.G_RET_STS_ERROR;
554 END IF;
555
556 -- Logging Infra: Procedure level
557 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
558 l_log_msg := l_procedure_name||'(-)';
559 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
560 END IF;
561
562 END validate_header_trn;
563
564 -- Bug 3650600
565
566 /****************/
567 /* Main Section */
568 /****************/
569
570 BEGIN
571
572 -- Logging Infra: Setting up runtime level
573 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
574
575 -- Logging Infra: Procedure level
576 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
577 l_log_msg := l_procedure_name||'(+)';
578 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin main', l_log_msg);
579 END IF;
580
581 -- Logging Infra: Statement level
582 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
583 l_log_msg := 'Parameters ';
584 l_log_msg := l_log_msg||'p_country_code: '||p_country_code;
585 l_log_msg := l_log_msg||' p_tax_reg_num: '||p_tax_reg_num;
586 l_log_msg := l_log_msg||' p_tax_regime_code: '||p_tax_regime_code;
587 l_log_msg := l_log_msg||' p_tax: '||p_tax;
588 l_log_msg := l_log_msg||' p_ptp_id: '||p_ptp_id;
589 l_log_msg := l_log_msg||' p_trn_type: '||p_trn_type;
590 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
591 END IF;
592 -- Logging Infra
593
594 /***** Tax Registration Number Validation ******/
595
596 l_trn := p_tax_reg_num;
597 l_tax_regime_code := p_tax_regime_code;
598 l_tax := p_tax;
599 l_trn_type := p_trn_type;
600 l_party_type_code := p_party_type_code;
601 l_tax_regime_code := p_tax_regime_code;
602 l_tax := p_tax;
603 l_tax_jurisdiction_code := p_tax_jurisdiction_code;
604 l_country_code := p_country_code;
605
609 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
606 IF l_trn is NULL THEN -- f1
607
608 -- Logging Infra: Statement level
610 l_log_msg := 'The Tax Registration Number is valid.';
611 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
612 END IF;
613 -- Logging Infra
614
615 l_trn := null;
616 p_return_status := FND_API.G_RET_STS_SUCCESS;
617 p_error_buffer := NULL;
618
619 ELSE -- f1
620
621 IF p_tax IS NOT NULL THEN
622 OPEN c_AllowDupRegnNum;
623 FETCH c_AllowDupRegnNum INTO l_allow_regn_num_flag;
624
625 IF l_allow_regn_num_flag = 'Y' THEN
626 l_pass_unique_check := FND_API.G_RET_STS_SUCCESS;
627 close c_AllowDupRegnNum;
628 ELSE
629 unique_trn(l_trn, l_party_type_code, l_tax_regime_code, l_tax, l_tax_jurisdiction_code, l_pass_unique_check);
630 close c_AllowDupRegnNum;
631 END IF;
632 ELSE
633 unique_trn(l_trn, l_party_type_code, l_tax_regime_code, l_tax, l_tax_jurisdiction_code, l_pass_unique_check);
634 END IF;
635
636 IF l_pass_unique_check = 'E' THEN -- f2
637
638 IF l_party_type_code = 'THIRD_PARTY' THEN -- f3
639
640 p_return_status := FND_API.G_RET_STS_ERROR;
641 display_msg(l_trn
642 ,'THIRD_PARTY'
643 ,l_tax_regime_code
644 ,l_tax
645 ,l_tax_jurisdiction_code
646 ,l_party_type
647 ,l_party_name
648 ,l_party_site_name);
649
650 x_party_type_token := l_party_type;
651 x_party_name_token := l_party_name;
652 x_party_site_name_token := NULL;
653
654 p_error_buffer := 'ZX_REG_NUM_DUPLICATE';
655
656 -- Bug 3650600
657
658 ELSIF l_party_type_code = 'THIRD_PARTY_SITE' THEN -- f3
659
660 validate_header_trn('THIRD_PARTY_SITE', p_ptp_id, p_tax_reg_num, l_header_check);
661
662 IF l_header_check = 'E' THEN
663 p_return_status := FND_API.G_RET_STS_ERROR;
664 display_msg(l_trn
665 ,'THIRD_PARTY_SITE'
666 ,l_tax_regime_code
667 ,l_tax
668 ,l_tax_jurisdiction_code
669 ,l_party_type
670 ,l_party_name
671 ,l_party_site_name);
672
673 x_party_type_token := l_party_type;
674 x_party_name_token := l_party_name;
675 x_party_site_name_token := l_party_site_name;
676
677 p_error_buffer := 'ZX_REG_NUM_DUPLICATE';
678
679 ELSE
680 p_return_status := FND_API.G_RET_STS_SUCCESS;
681 END IF;
682
683 ELSIF l_party_type_code = 'LEGAL_ESTABLISHMENT' THEN -- f3
684
685 validate_header_trn('LEGAL_ESTABLISHMENT',p_ptp_id, p_tax_reg_num, l_header_check);
686
687 IF l_header_check = 'E' THEN
688 p_return_status := FND_API.G_RET_STS_ERROR;
689 display_msg(l_trn
690 ,'LEGAL_ESTABLISHMENT'
691 ,l_tax_regime_code
692 ,l_tax
693 ,l_tax_jurisdiction_code
694 ,l_party_type
695 ,l_party_name
696 ,l_party_site_name);
697
698 x_party_type_token := l_party_type;
699 x_party_name_token := l_party_name;
700 x_party_site_name_token := NULL;
701
702 p_error_buffer := 'ZX_REG_NUM_DUPLICATE';
703
704 ELSE
705 p_return_status := FND_API.G_RET_STS_SUCCESS;
706 END IF;
707
708 -- Bug 3650600
709
710 ELSE -- f3
711
712 p_return_status := FND_API.G_RET_STS_ERROR;
713 p_error_buffer := 'ZX_REG_NUM_INVALID';
714
715 END IF; -- f3
716
717
718 ELSE -- f2
719
720 -- Added for Cross Registration number validation (6774002)
721 BEGIN
722 SELECT COUNT(REG.REGISTRATION_NUMBER)
723 INTO l_total_count
724 FROM ZX_REGISTRATIONS REG,
725 ZX_PARTY_TAX_PROFILE PTP
726 WHERE PTP.PARTY_TAX_PROFILE_ID = REG.PARTY_TAX_PROFILE_ID
727 AND REG.REGISTRATION_NUMBER = l_trn
728 AND SYSDATE >= REG.EFFECTIVE_FROM
729 AND (SYSDATE <= REG.EFFECTIVE_TO OR REG.EFFECTIVE_TO IS NULL)
730 AND ((l_tax_regime_code IS NULL) OR (REG.TAX_REGIME_CODE IS NULL)
731 OR (l_tax_regime_code IS NOT NULL AND REG.TAX_REGIME_CODE = l_tax_regime_code))
732 AND ((l_tax IS NULL) OR (REG.TAX IS NULL)
736 EXCEPTION
733 OR (l_tax IS NOT NULL AND REG.TAX = l_tax))
734 AND ((l_tax_jurisdiction_code IS NULL) OR (REG.TAX_JURISDICTION_CODE IS NULL)
735 OR (l_tax_jurisdiction_code IS NOT NULL AND REG.TAX_JURISDICTION_CODE = l_tax_jurisdiction_code));
737 WHEN OTHERS THEN
738 l_total_count := 0;
739 END;
740 IF l_total_count > 0 THEN
741 IF p_tax IS NOT NULL THEN
742 OPEN c_AllowDupRegnNum;
743 FETCH c_AllowDupRegnNum INTO l_allow_regn_num_flag;
744 END IF;
745 IF ( p_tax IS NULL OR nvl(l_allow_regn_num_flag,'N') <> 'Y' ) THEN
746 IF L_PARTY_TYPE_CODE = 'THIRD_PARTY' THEN
747 BEGIN
748 SELECT COUNT(REGISTRATION_NUMBER)
749 INTO l_specific_count
750 FROM ZX_REGISTRATIONS REG
751 WHERE REG.PARTY_TAX_PROFILE_ID IN (SELECT S.PARTY_TAX_PROFILE_ID
752 FROM ZX_PARTY_TAX_PROFILE S,
753 ZX_PARTY_TAX_PROFILE PTP,
754 HZ_PARTY_SITES HZPS
755 WHERE PTP.PARTY_TAX_PROFILE_ID = p_ptp_id
756 AND PTP.PARTY_ID = HZPS.PARTY_ID
757 AND HZPS.PARTY_SITE_ID = S.PARTY_ID
758 AND S.PARTY_TYPE_CODE = 'THIRD_PARTY_SITE')
759 AND REG.REGISTRATION_NUMBER = l_trn
760 AND SYSDATE >= REG.EFFECTIVE_FROM
761 AND (SYSDATE <= REG.EFFECTIVE_TO OR REG.EFFECTIVE_TO IS NULL)
762 AND ((l_tax_regime_code IS NULL) OR (REG.TAX_REGIME_CODE IS NULL)
763 OR (l_tax_regime_code IS NOT NULL AND REG.TAX_REGIME_CODE = l_tax_regime_code))
764 AND ((l_tax IS NULL) OR (REG.TAX IS NULL)
765 OR (l_tax IS NOT NULL AND REG.TAX = l_tax))
766 AND ((l_tax_jurisdiction_code IS NULL) OR (REG.TAX_JURISDICTION_CODE IS NULL)
767 OR (l_tax_jurisdiction_code IS NOT NULL AND REG.TAX_JURISDICTION_CODE = l_tax_jurisdiction_code));
768 EXCEPTION
769 WHEN OTHERS THEN
770 l_specific_count := 0;
771 END;
772 IF l_total_count <> l_specific_count THEN
773 display_cross_msg(l_trn
774 ,'THIRD_PARTY'
775 ,p_ptp_id
776 ,l_tax_regime_code
777 ,l_tax
778 ,l_tax_jurisdiction_code
779 ,l_party_type
780 ,l_party_name
781 ,l_party_site_name);
782
783 x_party_type_token := l_party_type;
784 x_party_name_token := l_party_name;
785 x_party_site_name_token := l_party_site_name;
786
787 p_return_status := FND_API.G_RET_STS_ERROR;
788 p_error_buffer := 'ZX_REG_NUM_DUPLICATE';
789 ELSE
790 p_return_status := FND_API.G_RET_STS_SUCCESS;
791 p_error_buffer := NULL;
792 END IF;
793 ELSIF L_PARTY_TYPE_CODE = 'THIRD_PARTY_SITE' THEN
794 validate_header_trn('THIRD_PARTY_SITE', p_ptp_id, p_tax_reg_num, l_header_check);
795 IF l_header_check = 'E' THEN
796 display_cross_msg(l_trn
797 ,'THIRD_PARTY_SITE'
798 ,p_ptp_id
799 ,l_tax_regime_code
800 ,l_tax
801 ,l_tax_jurisdiction_code
802 ,l_party_type
803 ,l_party_name
804 ,l_party_site_name);
805
806 x_party_type_token := l_party_type;
807 x_party_name_token := l_party_name;
808 x_party_site_name_token := l_party_site_name;
809
810 p_return_status := FND_API.G_RET_STS_ERROR;
811 p_error_buffer := 'ZX_REG_NUM_DUPLICATE';
812 ELSE
813 p_return_status := FND_API.G_RET_STS_SUCCESS;
814 p_error_buffer := NULL;
815 END IF;
816 END IF;
817 ELSE
818 p_return_status := FND_API.G_RET_STS_SUCCESS;
819 p_error_buffer := NULL;
820 END IF;
821 ELSE
822 p_return_status := FND_API.G_RET_STS_SUCCESS;
823 p_error_buffer := NULL;
824 l_custom := 0;
825 -- Custom Validation.
826 l_custom := ZX_TRN_CUSTOM_VAL_PKG.VALIDATE_TRN_CUSTOM(l_trn,
827 l_trn_type,
828 l_pass_unique_check,
829 p_country_code,
830 p_return_status,
834 -- Logging Infra: Statement level
831 p_error_buffer);
832
833 IF l_custom = 1 Then
835 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
836 l_log_msg := 'Customer is using custom rountine to validate tax registration number.';
837 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
838 END IF;
839 -- Logging Infra
840
841 return;
842 END IF;
843
844 --
845 IF (l_country_code = 'AT') THEN -- f3
846 /* if the country name is Austria check the Tax Registration Number */
847 --
848 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_AT(l_trn,
849 l_trn_type,
850 l_pass_unique_check,
851 p_return_status,
852 p_error_buffer);
853 --
854 ELSIF (l_country_code = 'BE') THEN
855 /* if the country name is Belgium check the Tax Registration Number */
856 --
857 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_BE(l_trn,
858 l_trn_type,
859 l_pass_unique_check,
860 p_return_status,
861 p_error_buffer);
862 --
863 ELSIF (l_country_code= 'DK') THEN
864 /* if the country name is Denmark check the Tax Registration Number */
865 --
866 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_DK(l_trn,
867 l_trn_type,
868 l_pass_unique_check,
869 p_return_status,
870 p_error_buffer);
871 --
872 ELSIF (l_country_code = 'EE') THEN
873 /* if the country name is Estonia check the Tax Registration Number */
874 --
875 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_EE(l_trn,
876 l_trn_type,
877 l_pass_unique_check,
878 p_return_status,
879 p_error_buffer);
880 --
881 ELSIF (l_country_code = 'FI') THEN
882 /* if the country name is Finland check the Tax Registration Number */
883 --
884 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_FI(l_trn,
885 l_trn_type,
886 l_pass_unique_check,
887 p_return_status,
888 p_error_buffer);
889 --
890 ELSIF (l_country_code = 'FR') THEN
891 /* if the country name is France check the Tax Registration Number */
892 --
893 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_FR(l_trn,
894 l_trn_type,
895 l_pass_unique_check,
896 p_return_status,
897 p_error_buffer);
898 --
899 ELSIF (l_country_code = 'DE') THEN
900 /* if the country name is Germany check the Tax Registration Number */
901 --
902 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_DE(l_trn,
903 l_trn_type,
904 l_pass_unique_check,
905 p_return_status,
906 p_error_buffer);
907 --
908 ELSIF (l_country_code = 'GR') THEN
909 /* if the country name is GREECE check the Tax Registration Number */
910 --
911 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_GR(l_trn,
912 l_trn_type,
913 l_pass_unique_check,
914 p_return_status,
915 p_error_buffer);
916 --
917 ELSIF (l_country_code = 'IE') THEN
918 /* if the country name is IRELAND check the Tax Registration Number */
919 --
920 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_IE(l_trn,
921 l_trn_type,
922 l_pass_unique_check,
923 p_return_status,
924 p_error_buffer);
925 --
926 ELSIF (l_country_code = 'IT') THEN
927 /* if the country name is Italy check the Tax Registration Number */
928 --
929 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_IT(l_trn,
930 l_trn_type,
931 l_pass_unique_check,
932 p_return_status,
933 p_error_buffer);
934 --
935 ELSIF (l_country_code = 'LU') THEN
936 /* if the country name is Luxembourg check the Tax Registration Number */
937 --
938 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_LU(l_trn,
939 l_trn_type,
940 l_pass_unique_check,
941 p_return_status,
942 p_error_buffer);
943 --
944 ELSIF (l_country_code = 'NL') THEN
945 /* if the country name is Netherlands check the Tax Registration Number */
946 --
947 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_NL(l_trn,
948 l_trn_type,
949 l_pass_unique_check,
950 p_return_status,
954 /* if the country name is Poland check the Tax Registration Number */
951 p_error_buffer);
952 --
953 ELSIF (l_country_code = 'PL') THEN
955 --
956 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_PL(l_trn,
957 l_trn_type,
958 l_pass_unique_check,
959 p_return_status,
960 p_error_buffer);
961 --
962 ELSIF (l_country_code = 'PT') THEN
963 /* if the country name is Portugal check the Tax Registration Number */
964 --
965 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_PT(l_trn,
966 l_trn_type,
967 l_pass_unique_check,
968 p_return_status,
969 p_error_buffer);
970 --
971 ELSIF (l_country_code = 'SK') THEN
972 /* if the country name is Slovakia check the Tax Registration Number */
973 --
974 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_SK(l_trn,
975 l_trn_type,
976 l_pass_unique_check,
977 p_return_status,
978 p_error_buffer);
979 --
980 ELSIF (l_country_code = 'ES') THEN
981 /* if the country name is Spain check the Tax Registration Number */
982 --
983 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_ES(l_trn,
984 l_trn_type,
985 l_pass_unique_check,
986 p_return_status,
987 p_error_buffer);
988 --
989 ELSIF (l_country_code = 'SE') THEN
990 /* if the country name is Sweden check the Tax Registration Number */
991 --
992 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_SE(l_trn,
993 l_trn_type,
994 l_pass_unique_check,
995 p_return_status,
996 p_error_buffer);
997 --
998 ELSIF (l_country_code = 'CH') THEN
999 /* if the country name is Swizerland check the Tax Registration Number */
1000 --
1001 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_CH(l_trn,
1002 l_trn_type,
1003 l_pass_unique_check,
1004 p_return_status,
1005 p_error_buffer);
1006 --
1007 ELSIF (l_country_code = 'GB') THEN
1008 /* if the country name is United Kingdom check the Tax Registration Number */
1009 --
1010 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_GB(l_trn,
1011 l_trn_type,
1012 l_pass_unique_check,
1013 p_return_status,
1014 p_error_buffer);
1015 --
1016 ELSIF (l_country_code = 'RU') THEN
1017 /* if the country name is Russia check the Tax Registration Number */
1018 --
1019 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_RU(l_trn,
1020 l_trn_type,
1021 l_pass_unique_check,
1022 p_return_status,
1023 p_error_buffer);
1024 --
1025 ELSIF (l_country_code = 'HU') THEN
1026 /* if the country name is Hungary check the Tax Registration Number */
1027 --
1028 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_HU(l_trn,
1029 l_trn_type,
1030 l_pass_unique_check,
1031 p_return_status,
1032 p_error_buffer);
1033 --
1034 ELSIF (l_country_code = 'AR') THEN
1035 /* if the country name is Argentina check the Tax Registration Number */
1036 --
1037 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_AR(l_trn,
1038 l_trn_type,
1039 p_return_status,
1040 p_error_buffer);
1041 --
1042 ELSIF (l_country_code = 'CL') THEN
1043 /* if the country name is Chile check the Tax Registration Number */
1044 --
1045 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_CL(l_trn,
1046 p_return_status,
1047 p_error_buffer);
1048 --
1049 ELSIF (l_country_code = 'CO') THEN
1050 /* if the country name is Colombia check the Tax Registration Number */
1051 --
1052 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_CO(l_trn,
1053 p_return_status,
1054 p_error_buffer);
1055 --
1056 ELSIF (l_country_code = 'TW') THEN
1057 /* if the country name is Taiwan check the Tax Registration Number */
1058 --
1059 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_TW(l_trn,
1060 p_return_status,
1061 p_error_buffer);
1062 --
1063 ELSIF (l_country_code = 'BR') THEN
1064 /* if the country name is Brazil check the Tax Registration Number */
1065 --
1066 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_BR(l_trn,
1067 l_trn_type,
1068 p_return_status,
1069 p_error_buffer);
1070 --
1071 ELSIF (l_country_code = 'MT') THEN
1075 l_trn_type,
1072 /* if the country name is Malta check the Tax Registration Number */
1073 --
1074 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_MT(l_trn,
1076 l_pass_unique_check,
1077 p_return_status,
1078 p_error_buffer);
1079 --
1080 ELSIF (l_country_code = 'CY') THEN
1081 /* if the country name is Cyprus check the Tax Registration Number */
1082 --
1083 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_CY(l_trn,
1084 l_trn_type,
1085 l_pass_unique_check,
1086 p_return_status,
1087 p_error_buffer);
1088 --
1089 ELSIF (l_country_code = 'LV') THEN
1090 /* if the country name is Latvia check the Tax Registration Number */
1091 --
1092 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_LV(l_trn,
1093 l_trn_type,
1094 l_pass_unique_check,
1095 p_return_status,
1096 p_error_buffer);
1097 --
1098 ELSIF (l_country_code = 'LT') THEN
1099 /* if the country name is Lithuania check the Tax Registration Number */
1100 --
1101 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_LT(l_trn,
1102 l_trn_type,
1103 l_pass_unique_check,
1104 p_return_status,
1105 p_error_buffer);
1106 --
1107 ELSIF (l_country_code = 'SI') THEN
1108 /* if the country name is Slovenia check the Tax Registration Number */
1109 --
1110 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_SI(l_trn,
1111 l_trn_type,
1112 l_pass_unique_check,
1113 p_return_status,
1114 p_error_buffer);
1115 --
1116 ELSE
1117 -- Logging Infra: Statement level
1118 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1119 l_log_msg := 'There is no validation rule of this country.';
1120 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1121 END IF;
1122 -- Logging Infra
1123
1124 p_return_status := FND_API.G_RET_STS_SUCCESS;
1125 p_error_buffer := NULL;
1126 --
1127 END IF; -- f3
1128 END IF;
1129 END IF; -- f2
1130
1131 END IF; -- f1
1132 -- Logging Infra: Procedure level
1133 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1134 l_log_msg := l_procedure_name||'(-)';
1135 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.end', l_log_msg);
1136 END IF;
1137
1138 EXCEPTION
1139 WHEN INVALID_CURSOR THEN
1140 p_return_status := FND_API.G_RET_STS_ERROR;
1141 p_error_buffer := SQLERRM;
1142
1143 -- Logging Infra:
1144 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1145 FND_LOG.STRING(G_LEVEL_STATEMENT,
1146 G_MODULE_NAME || l_procedure_name,
1147 SQLCODE || ': ' || SQLERRM);
1148 END IF;
1149
1150 WHEN OTHERS THEN
1151 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1152 p_error_buffer := SQLERRM;
1153
1154 -- Logging Infra:
1155 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1156 FND_LOG.STRING(G_LEVEL_STATEMENT,
1157 G_MODULE_NAME || l_procedure_name,
1158 SQLCODE || ': ' || SQLERRM);
1159 END IF;
1160
1161
1162 END VALIDATE_TRN;
1163
1164 /**************** End of PROCEDURE validate_trn ***********/
1165
1166
1167 procedure VALIDATE_TRN_AT (p_trn IN VARCHAR2,
1168 p_trn_type IN VARCHAR2,
1169 p_check_unique_flag IN VARCHAR2,
1170 p_return_status OUT NOCOPY VARCHAR2,
1171 p_error_buffer OUT NOCOPY VARCHAR2)
1172 AS
1173
1174 trn_value VARCHAR2(50);
1175 at_prefix VARCHAR2(3);
1176 check_digit VARCHAR2(1);
1177 position_5 VARCHAR2(2);
1178 position_7 VARCHAR2(2);
1179 position_9 VARCHAR2(2);
1180 sum_579 VARCHAR2(2);
1181 sum_46810 VARCHAR2(2);
1182 result_sum VARCHAR2(2);
1183 calc_ckd VARCHAR2(1);
1184 check_result_AT VARCHAR2(1);
1185 l_trn_type VARCHAR2(30);
1186
1187 -- Logging Infra
1188 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_AT';
1189 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1190
1191 /**************************/
1192 /* SUB-PROCEDURES SECTION */
1193 /**************************/
1194
1195 procedure fail_uniqueness is
1196 begin
1197
1198 -- Logging Infra: Statement level
1199 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1200 l_log_msg := 'The Tax Registration Number is already used.';
1204
1201 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1202 END IF;
1203 -- Logging Infra
1205 p_return_status := FND_API.G_RET_STS_ERROR;
1206 p_error_buffer := 'ZX_REG_NUM_INVALID';
1207
1208 end fail_uniqueness;
1209
1210 procedure fail_check is
1211 begin
1212
1213 -- Logging Infra: Statement level
1214 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1215 l_log_msg := 'Failed the validation of the Tax Registration Number.';
1216 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1217 END IF;
1218 -- Logging Infra
1219
1220 p_return_status := FND_API.G_RET_STS_ERROR;
1221 p_error_buffer := 'ZX_REG_NUM_INVALID';
1222 end fail_check;
1223
1224 procedure pass_check is
1225 begin
1226
1227 -- Logging Infra: Statement level
1228 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1229 l_log_msg := 'The Tax Registration Number is valid.';
1230 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1231 END IF;
1232 -- Logging Infra
1233
1234 p_return_status := FND_API.G_RET_STS_SUCCESS;
1235 p_error_buffer := NULL;
1236 end pass_check;
1237
1238 procedure check_numeric_AT (check_numeric_result OUT NOCOPY VARCHAR2) is
1239 num_check VARCHAR2(40);
1240 begin
1241 num_check := '';
1242 num_check := nvl(
1243 rtrim(
1244 translate(substr(trn_value,4,8),
1245 '1234567890',
1246 ' ')
1247 ), '0'
1248 );
1249 IF num_check <> '0' THEN
1250 check_numeric_result := FND_API.G_RET_STS_ERROR;
1251 ELSE
1252 check_numeric_result := FND_API.G_RET_STS_SUCCESS;
1253 END IF;
1254 end check_numeric_AT;
1255
1256
1257 /****************/
1258 /* MAIN SECTION */
1259 /****************/
1260
1261 BEGIN
1262
1263 trn_value := upper(p_trn);
1264 AT_PREFIX := substr(trn_value,1,3);
1265 check_digit := substr(trn_value,11,1);
1266 trn_value := replace(trn_value,' ','');
1267
1268 -- Logging Infra: Setting up runtime level
1269 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1270
1271 -- Logging Infra: Procedure level
1272 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1273 l_log_msg := l_procedure_name||'(+)';
1274 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1275 END IF;
1276 -- Logging Infra
1277
1278 -- Logging Infra: Statement level
1279 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1280 l_log_msg := 'Parameters ';
1281 l_log_msg := l_log_msg||'p_trn: '||p_trn;
1282 l_log_msg := l_log_msg||' p_trn_type: '||p_trn_type;
1283 l_log_msg := l_log_msg||' p_check_unique_flag: '||p_check_unique_flag;
1284 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1285 END IF;
1286 -- Logging Infra
1287
1288 IF p_check_unique_flag = 'E' THEN
1289 fail_uniqueness;
1290
1291 ELSIF p_check_unique_flag = 'S' THEN
1292
1293 --IF p_trn_type = 'VAT' THEN
1294
1295 check_numeric_AT(check_result_AT);
1296
1297 IF check_result_AT = 'S' then -- IF1
1298 position_5 := substr(trn_value,5,1)*2;
1299 position_7 := substr(trn_value,7,1)*2;
1300 position_9 := substr(trn_value,9,1)*2;
1301
1302 IF length(trn_value) = 11 THEN -- if2
1303
1304 IF AT_PREFIX = 'ATU' THEN -- if3
1305
1306 /* Calculate Check Digit for Austria */
1307
1308 sum_579 := substr(position_5,1,1) + nvl(substr(position_5,2,1),0) +
1309 substr(position_7,1,1) + nvl(substr(position_7,2,1),0) +
1310 substr(position_9,1,1) + nvl(substr(position_9,2,1),0);
1311
1312 sum_46810 := substr(trn_value,4,1) + substr(trn_value,6,1) +
1313 substr(trn_value,8,1) + substr(trn_value,10,1);
1314
1315 result_sum := (100-(sum_579+sum_46810+4));
1316
1317 calc_ckd := substr(result_sum,length(result_sum),1);
1318
1319 IF calc_ckd = check_digit THEN
1320 pass_check;
1321 ELSE
1322 -- Logging Infra: Statement level
1323 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1324 l_log_msg := 'Check digit is incorrect.';
1325 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1326 END IF;
1327 -- Logging Infra
1328
1329 p_return_status := FND_API.G_RET_STS_ERROR;
1330 p_error_buffer := 'ZX_REG_NUM_INVALID';
1331
1332 END IF;
1333
1334 ELSE -- if3
1335 -- Logging Infra: Statement level
1336 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1337 l_log_msg := 'The prefix of Tax Registration Number is incorrect.';
1338 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1339 END IF;
1343 p_error_buffer := 'ZX_REG_NUM_INVALID';
1340 -- Logging Infra
1341
1342 p_return_status := FND_API.G_RET_STS_ERROR;
1344
1345 END IF; -- if3
1346
1347 ELSE -- if2
1348
1349 -- Logging Infra: Statement level
1350 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1351 l_log_msg := 'The Tax Registration Number is too short.';
1352 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1353 END IF;
1354 -- Logging Infra
1355 IF length(trn_value) > 11 THEN
1356 p_return_status := FND_API.G_RET_STS_ERROR;
1357 p_error_buffer := 'ZX_REG_NUM_TOO_BIG';
1358 ELSE
1359 p_return_status := FND_API.G_RET_STS_ERROR;
1360 p_error_buffer := 'ZX_REG_NUM_INVALID';
1361 END IF;
1362
1363 END IF; -- if2
1364
1365 ELSE -- if1
1366
1367 -- Logging Infra: Statement level
1368 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1369 l_log_msg := 'The Tax Registration Number contains an alphanumeric character where a numeric character is expected.';
1370 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1371 END IF;
1372 -- Logging Infra
1373
1374 p_return_status := FND_API.G_RET_STS_ERROR;
1375 p_error_buffer := 'ZX_REG_NUM_INVALID_ALPHA';
1376
1377 END IF; -- if1
1378
1379 --ELSE
1380
1381 -- Logging Infra: Statement level
1382 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1383 -- l_log_msg := 'Tax Regegistration Type is not VAT.';
1384 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1385 -- END IF;
1386 -- Logging Infra
1387
1388 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
1389
1390 --END IF;
1391
1392 END IF;
1393
1394 END VALIDATE_TRN_AT;
1395
1396 /* *********** End VALIDATE_TRN_AT ****************** */
1397
1398
1399 procedure VALIDATE_TRN_BE (p_trn IN VARCHAR2,
1400 p_trn_type IN VARCHAR2,
1401 p_check_unique_flag IN VARCHAR2,
1402 p_return_status OUT NOCOPY VARCHAR2,
1403 p_error_buffer OUT NOCOPY VARCHAR2)
1404 AS
1405
1406 trn_value VARCHAR2(50);
1407 BE_PREFIX VARCHAR2(3);
1408 check_digit VARCHAR2(2);
1409 check_digit_1 VARCHAR2(2);
1410 check_digit_2 VARCHAR2(2);
1411 numeric_result VARCHAR2(40);
1412 l_trn_type VARCHAR2(30);
1413
1414 -- Logging Infra
1415 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_BE';
1416 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1417
1418 /**************************/
1419 /* SUB-PROCEDURES SECTION */
1420 /**************************/
1421
1422 procedure fail_uniqueness is
1423 begin
1424
1425 -- Logging Infra: Statement level
1426 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1427 l_log_msg := 'The Tax Registration Number is already used.';
1428 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1429 END IF;
1430 -- Logging Infra
1431
1432 p_return_status := FND_API.G_RET_STS_ERROR;
1433 p_error_buffer := 'ZX_REG_NUM_INVALID';
1434
1435 end fail_uniqueness;
1436
1437 procedure fail_check is
1438 begin
1439
1440 -- Logging Infra: Statement level
1441 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1442 l_log_msg := 'Failed the validation of the tax registration number.';
1443 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1444 END IF;
1445 -- Logging Infra
1446
1447 p_return_status := FND_API.G_RET_STS_ERROR;
1448 p_error_buffer := 'ZX_REG_NUM_INVALID';
1449 end fail_check;
1450
1451 procedure pass_check is
1452 begin
1453
1454 -- Logging Infra: Statement level
1455 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1456 l_log_msg := 'The Tax Registration Number is valid.';
1457 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1458 END IF;
1459 -- Logging Infra
1460
1461 p_return_status := FND_API.G_RET_STS_SUCCESS;
1462 p_error_buffer := NULL;
1463 end pass_check;
1464
1465 /****************/
1466 /* MAIN SECTION */
1467 /****************/
1468
1469 BEGIN
1470
1471 -- Logging Infra: Setting up runtime level
1472 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1473
1474 -- Logging Infra: Procedure level
1475 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1476 l_log_msg := l_procedure_name||'(+)';
1477 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1478 END IF;
1479
1480 -- Logging Infra: Statement level
1481 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1482 l_log_msg := 'Parameters ';
1486 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1483 l_log_msg := l_log_msg||'p_trn: '||p_trn;
1484 l_log_msg := l_log_msg||' p_trn_type: '||p_trn_type;
1485 l_log_msg := l_log_msg||' p_check_unique_flag: '||p_check_unique_flag;
1487 END IF;
1488 -- Logging Infra
1489
1490 trn_value := upper(p_trn);
1491 BE_PREFIX := substr(trn_value,1,2);
1492 check_digit := substr(trn_value,10,2);
1493 check_digit_1 := substr(trn_value,11,2);
1494 check_digit_2 := substr(trn_value,9,2);
1495
1496 trn_value := replace(trn_value,' ','');
1497
1498 IF p_check_unique_flag = 'E' THEN
1499 fail_uniqueness;
1500
1501 ELSIF p_check_unique_flag = 'S' THEN
1502
1503 --IF p_trn_type = 'VAT' THEN
1504
1505 IF BE_PREFIX = 'BE' THEN
1506
1507 numeric_result := common_check_numeric(trn_value,3,length(trn_value));
1508
1509 IF numeric_result = '0'
1510 then
1511 /* its numeric so continue */
1512 IF length(trn_value) = 11
1513 then
1514 IF check_digit = 97-mod(substr(trn_value,3,7),97)
1515 then
1516 pass_check;
1517 ELSE
1518 -- Logging Infra: Statement level
1519 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1520 l_log_msg := 'Check digit is not match.';
1521 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1522 END IF;
1523 -- Logging Infra
1524 p_return_status := FND_API.G_RET_STS_ERROR;
1525 p_error_buffer := 'ZX_REG_NUM_INVALID';
1526
1527 END IF;
1528
1529 ELSIF length(trn_value) = 12 THEN
1530 IF substr(trn_value,3,1) = 0 OR substr(trn_value,3,1) = 1
1531 then
1532 IF check_digit_1 = 97-mod(substr(trn_value,4,7),97)
1533 then
1534 pass_check;
1535 ELSE
1536
1537 -- Logging Infra: Statement level
1538 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1539 l_log_msg := 'Check digit is not match.';
1540 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1541 END IF;
1542 -- Logging Infra
1543 p_return_status := FND_API.G_RET_STS_ERROR;
1544 p_error_buffer := 'ZX_REG_NUM_INVALID';
1545
1546 END IF;
1547
1548 ELSE
1549
1550 -- Logging Infra: Statement level
1551 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1552 l_log_msg := 'The 3rd character should be 0 or 1.';
1553 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1554 END IF;
1555 -- Logging Infra
1556
1557 p_return_status := FND_API.G_RET_STS_ERROR;
1558 p_error_buffer := 'ZX_REG_NUM_INVALID';
1559
1560 END IF;
1561
1562 ELSE
1563
1564 -- Logging Infra: Statement level
1565 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1566 l_log_msg := 'The length of the Tax Registration Number is not correct.';
1567 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1568 END IF;
1569 -- Logging Infra
1570
1571 IF length(trn_value) > 12 THEN
1572 p_return_status := FND_API.G_RET_STS_ERROR;
1573 p_error_buffer := 'ZX_REG_NUM_TOO_BIG';
1574 ELSIF length(trn_value) < 8 THEN
1575 p_return_status := FND_API.G_RET_STS_ERROR;
1576 p_error_buffer := 'ZX_REG_NUM_INVALID';
1577 END IF;
1578
1579 END IF;
1580
1581 ELSE
1582
1583 -- Logging Infra: Statement level
1584 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1585 l_log_msg := 'The Tax Registration Number contains an alphanumeric character where a numeric character is expected.';
1586 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1587 END IF;
1588 -- Logging Infra
1589
1590 p_return_status := FND_API.G_RET_STS_ERROR;
1591 p_error_buffer := 'ZX_REG_NUM_INVALID_ALPHA';
1592
1593 END IF;
1594
1595 ELSE
1596
1597 numeric_result := common_check_numeric(trn_value,1,length(trn_value));
1598
1599 IF numeric_result = '0'
1600 then
1601 /* its numeric so continue */
1602 IF substr(trn_value,1,1) = 0 OR substr(trn_value,1,1) = 1
1603 then
1604 IF length(trn_value) = 10
1605 then
1606 IF check_digit_2 = 97-mod(substr(trn_value,2,7),97)
1607 then
1608 pass_check;
1609 ELSE
1610
1611 -- Logging Infra: Statement level
1612 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1613 l_log_msg := 'Check digit is not match.';
1614 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1618 p_return_status := FND_API.G_RET_STS_ERROR;
1615 END IF;
1616 -- Logging Infra
1617
1619 p_error_buffer := 'ZX_REG_NUM_INVALID';
1620
1621 END IF;
1622 ELSE
1623
1624 -- Logging Infra: Statement level
1625 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1626 l_log_msg := 'The length of the Tax Registration Number is not correct.';
1627 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1628 END IF;
1629 -- Logging Infra
1630 p_return_status := FND_API.G_RET_STS_ERROR;
1631 p_error_buffer := 'ZX_REG_NUM_INVALID';
1632
1633 END IF;
1634
1635 ELSE
1636 -- Logging Infra: Statement level
1637 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1638 l_log_msg := 'The first number of the Tax Registration Number is not 0 or 1.';
1639 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1640 END IF;
1641 -- Logging Infra
1642
1643 p_return_status := FND_API.G_RET_STS_ERROR;
1644 p_error_buffer := 'ZX_REG_NUM_INVALID';
1645 END IF;
1646
1647 ELSE
1648
1649 -- Logging Infra: Statement level
1650 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1651 l_log_msg := 'The Tax Registration Number contains an alphanumeric character where a numeric character is expected.';
1652 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1653 END IF;
1654 -- Logging Infra
1655
1656 p_return_status := FND_API.G_RET_STS_ERROR;
1657 p_error_buffer := 'ZX_REG_NUM_INVALID_ALPHA';
1658
1659 END IF;
1660
1661 END IF;
1662
1663 --ELSE
1664
1665 -- Logging Infra: Statement level
1666 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1667 -- l_log_msg := 'Tax Regegistration Type is not VAT.';
1668 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1669 -- END IF;
1670 -- Logging Infra
1671
1672 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
1673
1674 --END IF;
1675
1676 END IF;
1677
1678 END VALIDATE_TRN_BE;
1679
1680
1681 /* *********** End VALIDATE_TRN_BE ****************** */
1682
1683
1684 procedure VALIDATE_TRN_DK (p_trn_value IN VARCHAR2,
1685 p_trn_type IN VARCHAR2,
1686 p_check_unique_flag IN VARCHAR2,
1687 p_return_status OUT NOCOPY VARCHAR2,
1688 p_error_buffer OUT NOCOPY VARCHAR2)
1689 AS
1690
1691 -- Logging Infra
1692 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_DK';
1693 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1694
1695 BEGIN
1696
1697 -- Logging Infra: Setting up runtime level
1698 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1699
1700 -- Logging Infra: Procedure level
1701 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1702 l_log_msg := l_procedure_name||'(+)';
1703 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1704 END IF;
1705 -- Logging Infra
1706
1707 -- Logging Infra: Statement level
1708 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1709 l_log_msg := 'Parameters ';
1710 l_log_msg := l_log_msg||'p_trn_value: '||p_trn_value;
1711 l_log_msg := l_log_msg||' p_trn_type: '||p_trn_type;
1712 l_log_msg := l_log_msg||' p_check_unique_flag: '||p_check_unique_flag;
1713 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1714 END IF;
1715 -- Logging Infra
1716
1717 IF p_check_unique_flag = 'E' THEN
1718
1719 -- Logging Infra: Statement level
1720 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1721 l_log_msg := 'The Tax Registration Number is already used.';
1722 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1723 END IF;
1724 -- Logging Infra
1725
1726 p_return_status := FND_API.G_RET_STS_ERROR;
1727 p_error_buffer := 'ZX_REG_NUM_INVALID';
1728
1729 ELSIF p_check_unique_flag = 'S' THEN
1730
1731 --IF p_trn_type = 'VAT' THEN
1732
1733 /* check length = 10, prefix = 'DK' and the last eight digits are numeric */
1734 IF length(p_trn_value) = 10 and substr(p_trn_value,1,2) = 'DK' and
1735 common_check_numeric(p_trn_value,3,length(p_trn_value)) = '0' THEN
1736 --
1737
1738 -- Logging Infra: Statement level
1739 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1740 l_log_msg := 'Passed Validation: Length is 10, prefix is DK, and last eight digits are numeric.';
1741 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1742 END IF;
1743 -- Logging Infra
1744
1745 p_return_status := FND_API.G_RET_STS_SUCCESS;
1746 p_error_buffer := NULL;
1747 ELSE
1748
1749 -- Logging Infra: Statement level
1753 END IF;
1750 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1751 l_log_msg := 'Failed Validation: Length is 10, prefix is DK, and last eight digits are numeric.';
1752 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1754 -- Logging Infra
1755
1756 p_return_status := FND_API.G_RET_STS_ERROR;
1757 p_error_buffer := 'ZX_REG_NUM_INVALID';
1758 --
1759 END IF;
1760
1761 --ELSE
1762
1763 -- Logging Infra: Statement level
1764 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1765 -- l_log_msg := 'Tax Regegistration Type is not VAT.';
1766 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1767 -- END IF;
1768 -- Logging Infra
1769
1770 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
1771
1772 --END IF;
1773
1774 END IF;
1775
1776 END VALIDATE_TRN_DK;
1777
1778 /* *********** End VALIDATE_TRN_DK ****************** */
1779
1780
1781 procedure VALIDATE_TRN_EE (p_trn_value IN VARCHAR2,
1782 p_trn_type IN VARCHAR2,
1783 p_check_unique_flag IN VARCHAR2,
1784 p_return_status OUT NOCOPY VARCHAR2,
1785 p_error_buffer OUT NOCOPY VARCHAR2)
1786 AS
1787
1788 -- Logging Infra
1789 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_EE';
1790 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1791
1792 BEGIN
1793
1794 -- Logging Infra: Setting up runtime level
1795 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1796
1797 -- Logging Infra: Procedure level
1798 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1799 l_log_msg := l_procedure_name||'(+)';
1800 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1801 END IF;
1802 -- Logging Infra
1803
1804 -- Logging Infra: Statement level
1805 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1806 l_log_msg := 'Parameters ';
1807 l_log_msg := l_log_msg||'p_trn_value: '||p_trn_value;
1808 l_log_msg := l_log_msg||' p_trn_type: '||p_trn_type;
1809 l_log_msg := l_log_msg||' p_check_unique_flag: '||p_check_unique_flag;
1810 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1811 END IF;
1812 -- Logging Infra
1813
1814 IF p_check_unique_flag = 'E' THEN
1815
1816 -- Logging Infra: Statement level
1817 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1818 l_log_msg := 'The Tax Registration Number is already used.';
1819 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1820 END IF;
1821 -- Logging Infra
1822
1823 p_return_status := FND_API.G_RET_STS_ERROR;
1824 p_error_buffer := 'ZX_REG_NUM_INVALID';
1825
1826 ELSIF p_check_unique_flag = 'S' THEN
1827
1828 --IF p_trn_type = 'VAT' THEN
1829
1830 /* check length = 11, prefix = 'EE' and the last nine digits are numeric */
1831 IF length(p_trn_value) = 11 and substr(p_trn_value,1,2) = 'EE' AND
1832 common_check_numeric(p_trn_value,3,length(p_trn_value)) = '0' THEN
1833 --
1834
1835 -- Logging Infra: Statement level
1836 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1837 l_log_msg := 'Passed Validation: Length is 11, prefix is EE, and last nine digits are numeric.';
1838 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1839 END IF;
1840 -- Logging Infra
1841
1842 p_return_status := FND_API.G_RET_STS_SUCCESS;
1843 p_error_buffer := NULL;
1844 ELSE
1845
1846 -- Logging Infra: Statement level
1847 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1848 l_log_msg := 'Failed Validation: Length is 11, prefix is EE, and last nine digits are numeric.';
1849 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1850 END IF;
1851 -- Logging Infra
1852
1853 p_return_status := FND_API.G_RET_STS_ERROR;
1854 p_error_buffer := 'ZX_REG_NUM_INVALID';
1855 --
1856 END IF;
1857
1858 --ELSE
1859
1860 -- Logging Infra: Statement level
1861 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1862 -- l_log_msg := 'Tax Regegistration Type is not VAT.';
1863 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1864 -- END IF;
1865 -- Logging Infra
1866
1867 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
1868
1869 --END IF;
1870
1871 END IF;
1872
1873 END VALIDATE_TRN_EE;
1874
1875 /* *********** End VALIDATE_TRN_EE ****************** */
1876
1877 procedure VALIDATE_TRN_FI (p_trn_value IN VARCHAR2,
1878 p_trn_type IN VARCHAR2,
1879 p_check_unique_flag IN VARCHAR2,
1880 p_return_status OUT NOCOPY VARCHAR2,
1881 p_error_buffer OUT NOCOPY VARCHAR2)
1882 AS
1883
1884 -- Logging Infra
1885 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_FI';
1886 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1887
1888 BEGIN
1889
1893 -- Logging Infra: Procedure level
1890 -- Logging Infra: Setting up runtime level
1891 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1892
1894 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
1895 l_log_msg := l_procedure_name||'(+)';
1896 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
1897 END IF;
1898 -- Logging Infra
1899
1900 -- Logging Infra: Statement level
1901 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1902 l_log_msg := 'Parameters ';
1903 l_log_msg := l_log_msg||'p_trn_value: '||p_trn_value;
1904 l_log_msg := l_log_msg||' p_trn_type: '||p_trn_type;
1905 l_log_msg := l_log_msg||' p_check_unique_flag: '||p_check_unique_flag;
1906 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1907 END IF;
1908 -- Logging Infra
1909
1910 IF p_check_unique_flag = 'E' THEN
1911
1912 -- Logging Infra: Statement level
1913 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1914 l_log_msg := 'The Tax Registration Number is already used.';
1915 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1916 END IF;
1917 -- Logging Infra
1918
1919 p_return_status := FND_API.G_RET_STS_ERROR;
1920 p_error_buffer := 'ZX_REG_NUM_INVALID';
1921
1922 ELSIF p_check_unique_flag = 'S' THEN
1923
1924 --IF p_trn_type = 'VAT' THEN
1925
1926 /* check length = 10, prefix = 'FI' and the last eight digits are numeric */
1927
1928 IF length(p_trn_value) = 10 and substr(p_trn_value,1,2) = 'FI' and
1929 common_check_numeric(p_trn_value,3,length(p_trn_value)) = '0' THEN
1930 --
1931
1932 -- Logging Infra: Statement level
1933 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1934 l_log_msg := 'Passed Validation: Length is 10, prefix is FI, and last eight digits are numeric.';
1935 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1936 END IF;
1937 -- Logging Infra
1938
1939 p_return_status := FND_API.G_RET_STS_SUCCESS;
1940 p_error_buffer := NULL;
1941 ELSE
1942
1943 -- Logging Infra: Statement level
1944 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1945 l_log_msg := 'Failed Validation: Length is 10, prefix is FI, and last eight digits are numeric.';
1946 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1947 END IF;
1948 -- Logging Infra
1949
1950 p_return_status := FND_API.G_RET_STS_ERROR;
1951 p_error_buffer := 'ZX_REG_NUM_INVALID';
1952 --
1953 END IF;
1954
1955 --ELSE
1956
1957 -- Logging Infra: Statement level
1958 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1959 -- l_log_msg := 'Tax Regegistration Type is not VAT.';
1960 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
1961 -- END IF;
1962 -- Logging Infra
1963
1964 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
1965
1966 --END IF;
1967
1968 END IF;
1969
1970 END VALIDATE_TRN_FI;
1971
1972 /* *********** End VALIDATE_TRN_FI ****************** */
1973
1974 procedure VALIDATE_TRN_FR (p_trn_value IN VARCHAR2,
1975 p_trn_type IN VARCHAR2,
1976 p_check_unique_flag IN VARCHAR2,
1977 p_return_status OUT NOCOPY VARCHAR2,
1978 p_error_buffer OUT NOCOPY VARCHAR2)
1979 AS
1980
1981 -- Logging Infra
1982 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_FR';
1983 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1984
1985 function check_letter(check_value VARCHAR2,
1986 pos NUMBER)
1987 RETURN VARCHAR2
1988 IS
1989 letter_check VARCHAR2(2);
1990
1991 BEGIN
1992
1993 IF substr(check_value,pos,1) between 'A' and 'Z' and
1994 substr(check_value,pos,1) not in ('I','O') THEN
1995 --
1996 letter_check := '0';
1997 ELSE
1998 letter_check := '1';
1999 --
2000 END IF;
2001
2002 RETURN(letter_check);
2003 END check_letter;
2004 --
2005 BEGIN
2006
2007 -- Logging Infra: Setting up runtime level
2008 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2009
2010 -- Logging Infra: Procedure level
2011 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2012 l_log_msg := l_procedure_name||'(+)';
2013 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
2014 END IF;
2015 -- Logging Infra
2016
2017 -- Logging Infra: Statement level
2018 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2019 l_log_msg := 'Parameters ';
2020 l_log_msg := l_log_msg||'p_trn_value: '||p_trn_value;
2021 l_log_msg := l_log_msg||' p_trn_type: '||p_trn_type;
2022 l_log_msg := l_log_msg||' p_check_unique_flag: '||p_check_unique_flag;
2023 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2024 END IF;
2025 -- Logging Infra
2026
2027 IF p_check_unique_flag = 'E' THEN
2028
2029 -- Logging Infra: Statement level
2030 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2031 l_log_msg := 'The Tax Registration Number is already used.';
2032 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2036 p_return_status := FND_API.G_RET_STS_ERROR;
2033 END IF;
2034 -- Logging Infra
2035
2037 p_error_buffer := 'ZX_REG_NUM_INVALID';
2038
2039 ELSIF p_check_unique_flag = 'S' THEN
2040
2041 --IF p_trn_type = 'VAT' THEN
2042
2043 /* check length = 13, prefix = 'FR' and the last nine digits are numeric */
2044 IF length(p_trn_value) = 13 and substr(p_trn_value,1,2) = 'FR' and
2045 common_check_numeric(p_trn_value,5,length(p_trn_value)) = '0' THEN
2046 --
2047 IF (check_letter(p_trn_value,3) = '0' or common_check_numeric(p_trn_value,3,1) = '0') and
2048 (check_letter(p_trn_value,4) = '0' or common_check_numeric(p_trn_value,4,1) = '0') THEN
2049 --
2050
2051 -- Logging Infra: Statement level
2052 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2053 l_log_msg := 'Passed Validation: Length is 13, prefix is FR, and last nine digits are numeric.';
2054 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2055 END IF;
2056 -- Logging Infra
2057
2058 p_return_status := FND_API.G_RET_STS_SUCCESS;
2059 p_error_buffer := NULL;
2060 ELSE
2061
2062 -- Logging Infra: Statement level
2063 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2064 l_log_msg := 'Failed Validation: Length is 13, prefix is FR, and last nine digits are numeric.';
2065 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2066 END IF;
2067 -- Logging Infra
2068
2069 p_return_status := FND_API.G_RET_STS_ERROR;
2070 p_error_buffer := 'ZX_REG_NUM_INVALID';
2071 --
2072 END IF;
2073 ELSE
2074
2075 -- Logging Infra: Statement level
2076 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2077 l_log_msg := 'Failed Validation: Length is 13, prefix is FR, and last nine digits are numeric.';
2078 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2079 END IF;
2080 -- Logging Infra
2081
2082 p_return_status := FND_API.G_RET_STS_ERROR;
2083 p_error_buffer := 'ZX_REG_NUM_INVALID';
2084
2085 --
2086 END IF;
2087
2088 --ELSE
2089
2090 -- Logging Infra: Statement level
2091 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2092 -- l_log_msg := 'Tax Regegistration Type is not VAT.';
2093 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2094 -- END IF;
2095 -- Logging Infra
2096
2097 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
2098
2099 --END IF;
2100
2101 END IF;
2102
2103 END VALIDATE_TRN_FR;
2104
2105 /* *********** End VALIDATE_TRN_FR ****************** */
2106
2107
2108 procedure VALIDATE_TRN_DE (p_trn_value IN VARCHAR2,
2109 p_trn_type IN VARCHAR2,
2110 p_check_unique_flag IN VARCHAR2,
2111 p_return_status OUT NOCOPY VARCHAR2,
2112 p_error_buffer OUT NOCOPY VARCHAR2)
2113 AS
2114 -- Logging Infra
2115 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_DE';
2116 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2117
2118 BEGIN
2119
2120 -- Logging Infra: Setting up runtime level
2121 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2122
2123 -- Logging Infra: Procedure level
2124 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2125 l_log_msg := l_procedure_name||'(+)';
2126 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
2127 END IF;
2128 -- Logging Infra
2129
2130 -- Logging Infra: Statement level
2131 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2132 l_log_msg := 'Parameters ';
2133 l_log_msg := l_log_msg||'p_trn_value: '||p_trn_value;
2134 l_log_msg := l_log_msg||' p_trn_type: '||p_trn_type;
2135 l_log_msg := l_log_msg||' p_check_unique_flag: '||p_check_unique_flag;
2136 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2137 END IF;
2138 -- Logging Infra
2139
2140 IF p_check_unique_flag = 'E' THEN
2141
2142 -- Logging Infra: Statement level
2143 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2144 l_log_msg := 'The Tax Registration Number is already used.';
2145 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2146 END IF;
2147 -- Logging Infra
2148
2149 p_return_status := FND_API.G_RET_STS_ERROR;
2150 p_error_buffer := 'ZX_REG_NUM_INVALID';
2151
2152 ELSIF p_check_unique_flag = 'S' THEN
2153
2154 --IF p_trn_type = 'VAT' THEN
2155 /* check length = 11, prefix = 'DE' and the last nine digits are numeric */
2156 IF length(p_trn_value) = 11 and substr(p_trn_value,1,2) = 'DE' and
2157 common_check_numeric(p_trn_value,3,length(p_trn_value)) = '0' THEN
2158 --
2159
2160 -- Logging Infra: Statement level
2161 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2162 l_log_msg := 'Passed Validation: Length is 11, prefix is DE, and last nine digits are numeric.';
2163 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2164 END IF;
2165 -- Logging Infra
2166
2170
2167 p_return_status := FND_API.G_RET_STS_SUCCESS;
2168 p_error_buffer := NULL;
2169 ELSE
2171 -- Logging Infra: Statement level
2172 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2173 l_log_msg := 'Failed Validation: Length is 11, prefix is DE, and last nine digits are numeric.';
2174 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2175 END IF;
2176 -- Logging Infra
2177
2178 p_return_status := FND_API.G_RET_STS_ERROR;
2179 p_error_buffer := 'ZX_REG_NUM_INVALID';
2180 --
2181 END IF;
2182 --ELSE
2183
2184 -- Logging Infra: Statement level
2185 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2186 -- l_log_msg := 'Tax Regegistration Type is not VAT.';
2187 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2188 -- END IF;
2189 -- Logging Infra
2190
2191 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
2192
2193 --END IF;
2194
2195 END IF;
2196
2197 END VALIDATE_TRN_DE;
2198
2199 /* *********** End VALIDATE_TRN_DE ****************** */
2200
2201 procedure VALIDATE_TRN_GR (p_trn_value IN VARCHAR2,
2202 p_trn_type IN VARCHAR2,
2203 p_check_unique_flag IN VARCHAR2,
2204 p_return_status OUT NOCOPY VARCHAR2,
2205 p_error_buffer OUT NOCOPY VARCHAR2)
2206 AS
2207
2208 trn_string VARCHAR2(50);
2209 position_i number(2);
2210 integer_value number(1);
2211 multiplied_number number(38);
2212 multiplied_sum number(38) := 0;
2213 check_digit number(3);
2214
2215 -- Logging Infra
2216 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_GR';
2217 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2218
2219 BEGIN
2220
2221 -- Logging Infra: Setting up runtime level
2222 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2223
2224 -- Logging Infra: Procedure level
2225 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2226 l_log_msg := l_procedure_name||'(+)';
2227 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
2228 END IF;
2229 -- Logging Infra
2230
2231 -- Logging Infra: Statement level
2232 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2233 l_log_msg := 'Parameters ';
2234 l_log_msg := l_log_msg||'p_trn_value: '||p_trn_value;
2235 l_log_msg := l_log_msg||' p_trn_type: '||p_trn_type;
2236 l_log_msg := l_log_msg||' p_check_unique_flag: '||p_check_unique_flag;
2237 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2238 END IF;
2239 -- Logging Infra
2240
2241 trn_string := substr(p_trn_value,3,length(p_trn_value));
2242
2243 IF p_check_unique_flag = 'E' THEN
2244
2245 -- Logging Infra: Statement level
2246 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2247 l_log_msg := 'The Tax Registration Number is already used.';
2248 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2249 END IF;
2250 -- Logging Infra
2251
2252 p_return_status := FND_API.G_RET_STS_ERROR;
2253 p_error_buffer := 'ZX_REG_NUM_INVALID';
2254
2255 ELSIF p_check_unique_flag = 'S' THEN
2256
2257 --IF p_trn_type = 'VAT' THEN
2258
2259 /* check length = 11, prefix = 'GR' or 'EL' and the last nine digits are numeric */
2260
2261 IF length(p_trn_value) = 11 and
2262 (substr(p_trn_value,1,2) = 'GR' OR substr(p_trn_value,1,2) = 'EL') and
2263 common_check_numeric(trn_string,1,9) = '0' THEN
2264 --
2265 FOR position_i IN 1..8 loop
2266 --
2267 integer_value := substr(trn_string,position_i,1);
2268 multiplied_number := integer_value * power(2,(9-position_i));
2269 multiplied_sum := multiplied_sum + multiplied_number;
2270 --
2271 END LOOP;
2272 check_digit := mod(multiplied_sum,11);
2273
2274 IF check_digit = 10 then
2275 --
2276 check_digit := 0;
2277 --
2278 END IF;
2279 IF check_digit = substr(TRN_STRING,9,1) then
2280 --
2281
2282 -- Logging Infra: Statement level
2283 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2284 l_log_msg := 'Passed Validation: Length is 11, prefix is GR, and last nine digits are numeric.';
2285 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2286 END IF;
2287 -- Logging Infra
2288
2289 p_return_status := FND_API.G_RET_STS_SUCCESS;
2290 p_error_buffer := NULL;
2291 ELSE
2292
2293 -- Logging Infra: Statement level
2294 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2295 l_log_msg := 'Failed Validation: Length is 11, prefix is GR, and last nine digits are numeric.';
2296 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2297 END IF;
2298 -- Logging Infra
2299
2300 p_return_status := FND_API.G_RET_STS_ERROR;
2301 p_error_buffer := 'ZX_REG_NUM_INVALID';
2302 --
2303 END IF;
2304 ELSE
2308 l_log_msg := 'Failed Validation: Length is 11, prefix is GR, and last nine digits are numeric.';
2305
2306 -- Logging Infra: Statement level
2307 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2309 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2310 END IF;
2311 -- Logging Infra
2312
2313 p_return_status := FND_API.G_RET_STS_ERROR;
2314 p_error_buffer := 'ZX_REG_NUM_INVALID';
2315 --
2316 END IF;
2317
2318 --ELSE
2319
2320 -- Logging Infra: Statement level
2321 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2322 -- l_log_msg := 'Tax Regegistration Type is not VAT.';
2323 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2324 -- END IF;
2325 -- Logging Infra
2326
2327 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
2328
2329 --END IF;
2330
2331 END IF;
2332
2333 END VALIDATE_TRN_GR;
2334
2335 /* *********** End VALIDATE_TRN_GR ****************** */
2336
2337 procedure VALIDATE_TRN_IE (p_trn_value IN VARCHAR2,
2338 p_trn_type IN VARCHAR2,
2339 p_check_unique_flag IN VARCHAR2,
2340 p_return_status OUT NOCOPY VARCHAR2,
2341 p_error_buffer OUT NOCOPY VARCHAR2)
2342 AS
2343
2344 -- Logging Infra
2345 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_IE';
2346 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2347
2348 function check_letter(check_value VARCHAR2,
2349 pos NUMBER)
2350 RETURN VARCHAR2
2351 IS
2352 letter_check VARCHAR2(2);
2353
2354 BEGIN
2355
2356 IF substr(check_value,pos,1) between 'A' and 'Z' THEN
2357 --
2358 letter_check := '0';
2359 ELSE
2360 letter_check := '1';
2361 --
2362 END IF;
2363
2364 RETURN(letter_check);
2365 END check_letter;
2366 --
2367 /**************** end of FUNCTION check_letter *******************/
2368
2369 BEGIN
2370
2371 -- Logging Infra: Setting up runtime level
2372 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2373
2374 -- Logging Infra: Procedure level
2375 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2376 l_log_msg := l_procedure_name||'(+)';
2377 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
2378 END IF;
2379 -- Logging Infra
2380
2381 -- Logging Infra: Statement level
2382 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2383 l_log_msg := 'Parameters ';
2384 l_log_msg := l_log_msg||'p_trn_value: '||p_trn_value;
2385 l_log_msg := l_log_msg||' p_trn_type: '||p_trn_type;
2386 l_log_msg := l_log_msg||' p_check_unique_flag: '||p_check_unique_flag;
2387 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2388 END IF;
2389 -- Logging Infra
2390
2391 IF p_check_unique_flag = 'E' THEN
2392
2393 -- Logging Infra: Statement level
2394 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2395 l_log_msg := 'The Tax Registration Number is already used.';
2396 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2397 END IF;
2398 -- Logging Infra
2399
2400 p_return_status := FND_API.G_RET_STS_ERROR;
2401 p_error_buffer := 'ZX_REG_NUM_INVALID';
2402
2403 ELSIF p_check_unique_flag = 'S' THEN
2404
2405 --IF p_trn_type = 'VAT' THEN
2406
2407 IF length(p_trn_value) = 10 and substr(p_trn_value,1,2) = 'IE' and
2408 check_letter(p_trn_value,length(p_trn_value)) = '0' THEN
2409 --
2410 IF common_check_numeric(p_trn_value,3,length(p_trn_value)-4) = '0' THEN
2411 --
2412
2413 -- Logging Infra: Statement level
2414 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2415 l_log_msg := 'Passed Validation: Length is 10, prefix is IE, and last eight digits are numeric.';
2416 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2417 END IF;
2418 -- Logging Infra
2419
2420 p_return_status := FND_API.G_RET_STS_SUCCESS;
2421 p_error_buffer := NULL;
2422 ELSIF common_check_numeric(p_trn_value,4,length(p_trn_value)-5) = '0' and
2423 check_letter(p_trn_value,1) = '0' THEN
2424 --
2425
2426 -- Logging Infra: Statement level
2427 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2428 l_log_msg := 'Passed Validation: Length is 10, prefix is IE, and last eight digits are numeric.';
2429 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2430 END IF;
2431 -- Logging Infra
2432
2433 p_return_status := FND_API.G_RET_STS_SUCCESS;
2434 p_error_buffer := NULL;
2435
2436 ELSIF common_check_numeric(p_trn_value,5,length(p_trn_value)-5) = '0' and
2437 check_letter(p_trn_value,1) = '0' THEN
2438 --
2439 -- Logging Infra: Statement level
2440 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2441 l_log_msg := 'Passed Validation: Length is 10, prefix is IE, and last five digits are numeric.';
2442 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2443 END IF;
2444 -- Logging Infra
2445
2449 ELSE
2446 p_return_status := FND_API.G_RET_STS_SUCCESS;
2447 p_error_buffer := NULL;
2448
2450
2451 -- Logging Infra: Statement level
2452 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2453 l_log_msg := 'Failed Validation: Length is 10, prefix is IE, and last eight digits are numeric.';
2454 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2455 END IF;
2456 -- Logging Infra
2457
2458 p_return_status := FND_API.G_RET_STS_ERROR;
2459 p_error_buffer := 'ZX_REG_NUM_INVALID';
2460 --
2461 END IF;
2462 ELSE
2463
2464 -- Logging Infra: Statement level
2465 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2466 l_log_msg := 'Failed Validation: Length is 10, prefix is IE, and last eight digits are numeric.';
2467 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2468 END IF;
2469 -- Logging Infra
2470
2471 p_return_status := FND_API.G_RET_STS_ERROR;
2472 p_error_buffer := 'ZX_REG_NUM_INVALID';
2473 --
2474 END IF;
2475
2476 --ELSE
2477
2478 -- Logging Infra: Statement level
2479 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2480 -- l_log_msg := 'Tax Regegistration Type is not VAT.';
2481 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2482 -- END IF;
2483 -- Logging Infra
2484
2485 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
2486
2487 --END IF;
2488
2489 END IF;
2490
2491 END VALIDATE_TRN_IE;
2492
2493 /* *********** End VALIDATE_TRN_IE ****************** */
2494
2495 procedure VALIDATE_TRN_IT (p_trn_value IN VARCHAR2,
2496 p_trn_type IN VARCHAR2,
2497 p_check_unique_flag IN VARCHAR2,
2498 p_return_status OUT NOCOPY VARCHAR2,
2499 p_error_buffer OUT NOCOPY VARCHAR2)
2500 AS
2501
2502 trn_string VARCHAR2(50);
2503 tr_num VARCHAR2(50);
2504 check_digit NUMBER(1);
2505 position_i NUMBER(2);
2506 integer_value NUMBER(1);
2507 calc_check NUMBER(2);
2508 calc_cd VARCHAR2(1);
2509 indicator VARCHAR2(1);
2510 even_value NUMBER(2);
2511 even_sub_tot NUMBER(4);
2512 even_tot NUMBER(5);
2513 odd_tot NUMBER(5);
2514 check_tot NUMBER(6);
2515
2516 -- Logging Infra
2517 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_IT';
2518 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2519
2520 /**************************/
2521 /* SUB-PROCEDURES SECTION */
2522 /**************************/
2523
2524 procedure fail_uniqueness is
2525 begin
2526
2527 -- Logging Infra: Statement level
2528 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2529 l_log_msg := 'The Tax Registration Number is already used.';
2530 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2531 END IF;
2532 -- Logging Infra
2533
2534 p_return_status := FND_API.G_RET_STS_ERROR;
2535 p_error_buffer := 'ZX_REG_NUM_INVALID';
2536 end fail_uniqueness;
2537
2538 procedure fail_check is
2539 begin
2540
2541 -- Logging Infra: Statement level
2542 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2543 l_log_msg := 'Failed the validation of the tax registration number.';
2544 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2545 END IF;
2546 -- Logging Infra
2547
2548 p_return_status := FND_API.G_RET_STS_ERROR;
2549 p_error_buffer := 'ZX_REG_NUM_INVALID';
2550 end fail_check;
2551
2552 PROCEDURE pass_check IS
2553 BEGIN
2554
2555 -- Logging Infra: Statement level
2556 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2557 l_log_msg := 'The Tax Registration Number is valid.';
2558 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2559 END IF;
2560 -- Logging Infra
2561
2562 p_return_status := FND_API.G_RET_STS_SUCCESS;
2563 p_error_buffer := NULL;
2564 END pass_check;
2565
2566 /** procedure to check that the chars sent are numeric only **/
2567 /** if ok, then sends back the output as a number **/
2568
2569 PROCEDURE check_numeric(input_string IN VARCHAR2,
2570 output_val OUT NOCOPY VARCHAR2,
2571 flag1 OUT NOCOPY VARCHAR2) IS
2572
2573 num_check VARCHAR2(50);
2574 var1 VARCHAR2(50);
2575
2576 begin
2577 num_check := '';
2578 var1 := input_string;
2579 num_check := nvl(rtrim( translate(var1, '1234567890',
2580 ' ')
2581 ), '0'
2582 );
2583
2584 IF num_check <> '0' THEN
2585 flag1 := FND_API.G_RET_STS_ERROR;
2589 output_val := var1;
2586 output_val := '0';
2587 ELSE
2588 flag1 := FND_API.G_RET_STS_SUCCESS;
2590 END IF;
2591
2592 END check_numeric;
2593
2594 /****************/
2595 /* MAIN SECTION */
2596 /****************/
2597
2598 BEGIN
2599 indicator := '';
2600 odd_tot := 0;
2601 even_tot := 0;
2602
2603 -- Logging Infra: Setting up runtime level
2604 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2605
2606 -- Logging Infra: Procedure level
2607 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2608 l_log_msg := l_procedure_name||'(+)';
2609 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
2610 END IF;
2611 -- Logging Infra
2612
2613 -- Logging Infra: Statement level
2614 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2615 l_log_msg := 'Parameters ';
2616 l_log_msg := l_log_msg||'p_trn_value: '||p_trn_value;
2617 l_log_msg := l_log_msg||' p_trn_type: '||p_trn_type;
2618 l_log_msg := l_log_msg||' p_check_unique_flag: '||p_check_unique_flag;
2619 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2620 END IF;
2621 -- Logging Infra
2622
2623 IF common_check_numeric(p_trn_value,1,2) <> '0' THEN
2624 --
2625 IF substr(p_trn_value,1,2) <> 'IT' THEN
2626 --
2627 p_return_status := FND_API.G_RET_STS_ERROR;
2628 p_error_buffer := 'ZX_REG_NUM_INVALID';
2629 ELSE
2630 --
2631 trn_string := substr(p_trn_value,3, length(p_trn_value));
2632 --
2633 END IF;
2634 ELSE
2635 --
2636 trn_string := p_trn_value;
2637 --
2638 END IF;
2639
2640 /** ensure that p_trn_value passed in is only numeric **/
2641 check_numeric(trn_string, TR_NUM, indicator);
2642 check_digit := substr(TR_NUM, (length(TR_NUM)));
2643
2644 IF p_check_unique_flag = 'E' THEN
2645
2646 fail_uniqueness;
2647
2648 ELSIF p_check_unique_flag = 'S' THEN
2649
2650 --IF p_trn_type = 'VAT' THEN
2651
2652 /** make sure that TR Num code is only 11 chars - including Check digit **/
2653 IF (length(TR_NUM) = 11) AND (indicator = 'S')
2654 then
2655
2656 FOR position_i IN 1..10 LOOP
2657
2658 /** moves along length of Tax Registration Num and assigns weightings **/
2659 /** to each of the digits upto and including the 10th position **/
2660 /** all odd positioned integers are added together. All evenly **/
2661 /** postitioned integers are multiplied by 2, if greater than **/
2662 /** 10, the digits are added together. The last digit of the **/
2663 /** sum totals when added together is subtracted from 10 - unless **/
2664 /** already zero. This becomes the TR Num check digit **/
2665
2666 integer_value := substr(TR_NUM,position_i,1);
2667
2668 IF position_i in (2,4,6,8,10)
2669 then
2670 even_value := integer_value * 2;
2671 IF even_value > 9
2672 then
2673 even_sub_tot := substr(even_value,1,1) +
2674 substr(even_value,2,1);
2675 ELSE
2676 even_sub_tot := even_value;
2677 END IF;
2678 even_tot := even_tot + even_sub_tot;
2679 ELSE
2680 odd_tot := odd_tot + integer_value;
2681 END IF;
2682
2683 END LOOP; /** of the counter position_i **/
2684
2685 check_tot := odd_tot + even_tot;
2686
2687 IF substr(check_tot,length(check_tot),1) = 0
2688 then
2689 calc_cd := 0;
2690 ELSE
2691 calc_cd := 10 - substr(check_tot, length(check_tot),1);
2692 END IF;
2693
2694 /*** After having calculated what should be the Italian Tax Num ***/
2695 /*** Check digit compare to the actual and fail if not the same ***/
2696
2697 IF calc_cd <> check_digit THEN
2698 fail_check;
2699 ELSE
2700 pass_check;
2701 END IF;
2702
2703 ELSE
2704 fail_check; /** Tax registration number is incorrect length or is not numeric**/
2705 END IF;
2706
2707 --ELSE
2708
2709 -- Logging Infra: Statement level
2710 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2711 -- l_log_msg := 'Tax Regegistration Type is not VAT.';
2712 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2713 -- END IF;
2714 -- Logging Infra
2715
2716 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
2717
2718 --END IF;
2719
2720 ELSE
2721 pass_check;
2722
2723 END IF; /** of fail uniqueness check **/
2724
2725 END VALIDATE_TRN_IT;
2726
2727 /* *********** End of VALIDATE_TRN_IT ****************** */
2728
2729 --
2730 procedure VALIDATE_TRN_LU (p_trn_value IN VARCHAR2,
2731 p_trn_type IN VARCHAR2,
2732 p_check_unique_flag IN VARCHAR2,
2733 p_return_status OUT NOCOPY VARCHAR2,
2737 -- Logging Infra
2734 p_error_buffer OUT NOCOPY VARCHAR2)
2735 AS
2736
2738 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_LU';
2739 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2740
2741 BEGIN
2742
2743 -- Logging Infra: Setting up runtime level
2744 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2745
2746 -- Logging Infra: Procedure level
2747 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2748 l_log_msg := l_procedure_name||'(+)';
2749 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
2750 END IF;
2751 -- Logging Infra
2752
2753 -- Logging Infra: Statement level
2754 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2755 l_log_msg := 'Parameters ';
2756 l_log_msg := l_log_msg||'p_trn_value: '||p_trn_value;
2757 l_log_msg := l_log_msg||'p_trn_type: '||p_trn_type;
2758 l_log_msg := l_log_msg||' p_check_unique_flag: '||p_check_unique_flag;
2759 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2760 END IF;
2761 -- Logging Infra
2762
2763 IF p_check_unique_flag = 'E' THEN
2764
2765 -- Logging Infra: Statement level
2766 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2767 l_log_msg := 'The Tax Registration Number is already used.';
2768 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2769 END IF;
2770 -- Logging Infra
2771
2772 p_return_status := FND_API.G_RET_STS_ERROR;
2773 p_error_buffer := 'ZX_REG_NUM_INVALID';
2774
2775 ELSIF p_check_unique_flag = 'S' THEN
2776
2777 --IF p_trn_type = 'VAT' THEN
2778
2779 /* check length = 10, prefix = 'LU' and the last eight digits are numeric */
2780
2781 IF length(p_trn_value) = 10 and substr(p_trn_value,1,2) = 'LU' and
2782 common_check_numeric(p_trn_value,3,length(p_trn_value)) = '0' THEN
2783 --
2784
2785 -- Logging Infra: Statement level
2786 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2787 l_log_msg := 'Passed Validation: Length is 10, prefix is LU, and last eight digits are numeric.';
2788 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2789 END IF;
2790 -- Logging Infra
2791
2792 p_return_status := FND_API.G_RET_STS_SUCCESS;
2793 p_error_buffer := NULL;
2794 ELSE
2795
2796 -- Logging Infra: Statement level
2797 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2798 l_log_msg := 'Failed Validation: Length is 10, prefix is LU, and last eight digits are numeric.';
2799 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2800 END IF;
2801 -- Logging Infra
2802
2803 p_return_status := FND_API.G_RET_STS_ERROR;
2804 p_error_buffer := 'ZX_REG_NUM_INVALID';
2805 --
2806 END IF;
2807
2808 --ELSE
2809
2810 -- Logging Infra: Statement level
2811 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2812 -- l_log_msg := 'Tax Regegistration Type is not VAT.';
2813 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2814 -- END IF;
2815 -- Logging Infra
2816
2817 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
2818
2819 --END IF;
2820
2821 END IF;
2822
2823 END VALIDATE_TRN_LU;
2824
2825 /* *********** End VALIDATE_TRN_LU ****************** */
2826
2827 procedure VALIDATE_TRN_SK (p_trn_value IN VARCHAR2,
2828 p_trn_type IN VARCHAR2,
2829 p_check_unique_flag IN VARCHAR2,
2830 p_return_status OUT NOCOPY VARCHAR2,
2831 p_error_buffer OUT NOCOPY VARCHAR2)
2832 AS
2833
2834 -- Logging Infra
2835 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_SK';
2836 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2837 trn_value VARCHAR2(50);
2838
2839 BEGIN
2840
2841 -- Logging Infra: Setting up runtime level
2842 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2843
2844 -- Logging Infra: Procedure level
2845 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
2846 l_log_msg := l_procedure_name||'(+)';
2847 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
2848 END IF;
2849 -- Logging Infra
2850
2851 -- Logging Infra: Statement level
2852 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2853 l_log_msg := 'Parameters ';
2854 l_log_msg := l_log_msg||'p_trn_value: '||p_trn_value;
2855 l_log_msg := l_log_msg||' p_trn_type: '||p_trn_type;
2856 l_log_msg := l_log_msg||' p_check_unique_flag: '||p_check_unique_flag;
2857 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2858 END IF;
2859 -- Logging Infra
2860
2861 IF p_check_unique_flag = FND_API.G_RET_STS_ERROR THEN
2862
2863 -- Logging Infra: Statement level
2864 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2865 l_log_msg := 'The Tax Registration Number is already used.';
2866 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2867 END IF;
2871 p_error_buffer := 'ZX_REG_NUM_INVALID';
2868 -- Logging Infra
2869
2870 p_return_status := FND_API.G_RET_STS_ERROR;
2872
2873 ELSIF p_check_unique_flag = 'S' THEN
2874
2875 trn_value := upper(p_trn_value);
2876
2877 --IF p_trn_type = 'VAT' THEN
2878
2879 /* check length = 12 and they are numeric */
2880 IF (length(p_trn_value) = 12 and common_check_numeric(p_trn_value,1,12) = '0') OR
2881 (length(p_trn_value) = 9 and common_check_numeric(p_trn_value,1,9) = '0' ) OR
2882 (length(p_trn_value) = 10 and common_check_numeric(p_trn_value,1,10) = '0') THEN
2883 --
2884
2885 -- Logging Infra: Statement level
2886 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2887 l_log_msg := 'Passed Validation: Length is 12, and they are numeric.';
2888 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2889 END IF;
2890 -- Logging Infra
2891
2892 p_return_status := FND_API.G_RET_STS_SUCCESS;
2893 p_error_buffer := NULL;
2894
2895 ELSIF (substr(trn_value,1,2) = 'SK') and
2896 (common_check_numeric(p_trn_value,3,length(p_trn_value)) = '0') THEN
2897
2898 -- Logging Infra: Statement level
2899 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2900 l_log_msg := 'The Tax Registration Number is numeric.';
2901 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2902 END IF;
2903 -- Logging Infra
2904
2905 p_return_status := FND_API.G_RET_STS_SUCCESS;
2906 p_error_buffer := NULL;
2907 ELSE
2908
2909 -- Logging Infra: Statement level
2910 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2911 l_log_msg := 'Passed Validation: Length is 12, and they are numeric.';
2912 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2913 END IF;
2914 -- Logging Infra
2915
2916 p_return_status := FND_API.G_RET_STS_ERROR;
2917 p_error_buffer := 'ZX_REG_NUM_INVALID';
2918 --
2919 END IF;
2920
2921 --ELSE
2922
2923 -- Logging Infra: Statement level
2924 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2925 -- l_log_msg := 'Tax Regegistration Type is not VAT.';
2926 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2927 -- END IF;
2928 -- Logging Infra
2929
2930 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
2931
2932 --END IF;
2933
2934 END IF;
2935
2936 END VALIDATE_TRN_SK;
2937
2938 /* *********** End VALIDATE_TRN_SK ****************** */
2939
2940 procedure VALIDATE_TRN_NL(p_trn IN VARCHAR2,
2941 p_trn_type IN VARCHAR2,
2942 p_check_unique_flag IN VARCHAR2,
2943 p_return_status OUT NOCOPY VARCHAR2,
2944 p_error_buffer OUT NOCOPY VARCHAR2)
2945 AS
2946
2947 p_trn_value VARCHAR2(50);
2948 NL_PREFIX VARCHAR2(2);
2949 SUFFIX_VALUE VARCHAR2(2);
2950 check_digit VARCHAR2(1);
2951 B_value VARCHAR2(1);
2952 position_i NUMBER(2);
2953 integer_value NUMBER(1);
2954 multiplied_number NUMBER(2);
2955 multiplied_sum NUMBER(3);
2956 check_result VARCHAR2(1);
2957
2958 -- Logging Infra
2959 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_NL';
2960 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
2961
2962 /**************************/
2963 /* SUB-PROCEDURES SECTION */
2964 /**************************/
2965
2966 procedure fail_uniqueness is
2967 begin
2968 -- Logging Infra: Statement level
2969 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2970 l_log_msg := 'The Tax Registration Number is already used.';
2971 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2972 END IF;
2973 -- Logging Infra
2974
2975 p_return_status := FND_API.G_RET_STS_ERROR;
2976 p_error_buffer := 'ZX_REG_NUM_INVALID';
2977 end fail_uniqueness;
2978
2979 procedure fail_check is
2980 begin
2981 -- Logging Infra: Statement level
2982 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2983 l_log_msg := 'Failed the validation of the tax registration number.';
2984 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2985 END IF;
2986 -- Logging Infra
2987
2988 p_return_status := FND_API.G_RET_STS_ERROR;
2989 p_error_buffer := 'ZX_REG_NUM_INVALID';
2990 end fail_check;
2991
2992 procedure pass_check is
2993 begin
2994 -- Logging Infra: Statement level
2995 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2996 l_log_msg := 'The Tax Registration Number is valid.';
2997 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
2998 END IF;
2999 -- Logging Infra
3000
3001 p_return_status := FND_API.G_RET_STS_SUCCESS;
3005 procedure check_numeric (check_numeric_result OUT NOCOPY VARCHAR2) is
3002 p_error_buffer := NULL;
3003 end pass_check;
3004
3006 num_check VARCHAR2(50);
3007 begin
3008 num_check := '';
3009 num_check := nvl(
3010 rtrim(
3011 translate(substr(p_trn_value,3,9),
3012 '1234567890',
3013 ' ')
3014 ), '0'
3015 );
3016
3017 IF num_check <> '0' THEN
3018
3019 -- Logging Infra: Statement level
3020 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3021 l_log_msg := 'The Tax Registration Number without prefix and suffix must be numeric.';
3022 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3023 END IF;
3024 -- Logging Infra
3025
3026 check_numeric_result := FND_API.G_RET_STS_ERROR;
3027 p_error_buffer := 'ZX_REG_NUM_INVALID';
3028 ELSE
3029
3030 -- Logging Infra: Statement level
3031 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3032 l_log_msg := 'The Tax Registration Number is valid.';
3033 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3034 END IF;
3035 -- Logging Infra
3036
3037 check_numeric_result := FND_API.G_RET_STS_SUCCESS;
3038 p_error_buffer := NULL;
3039 END IF;
3040 end check_numeric;
3041
3042
3043 /****************/
3044 /* MAIN SECTION */
3045 /****************/
3046
3047 BEGIN
3048
3049 -- Logging Infra: Setting up runtime level
3050 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3051
3052 -- Logging Infra: Procedure level
3053 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3054 l_log_msg := l_procedure_name||'(+)';
3055 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
3056 END IF;
3057 -- Logging Infra
3058
3059 -- Logging Infra: Statement level
3060 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3061 l_log_msg := 'Parameters ';
3062 l_log_msg := l_log_msg||'p_trn_value: '||p_trn_value;
3063 l_log_msg := l_log_msg||' p_trn_type: '||p_trn_type;
3064 l_log_msg := l_log_msg||' p_check_unique_flag: '||p_check_unique_flag;
3065 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3066 END IF;
3067 -- Logging Infra
3068
3069 p_trn_value := upper(p_trn);
3070 NL_PREFIX := substr(p_trn_value,1,2);
3071 SUFFIX_VALUE := substr(p_trn_value,13,2);
3072 check_digit := substr(p_trn_value,11,1);
3073 B_value := substr(p_trn_value,12,1);
3074 multiplied_number := 0;
3075 multiplied_sum := 0;
3076
3077 IF p_check_unique_flag = 'E'
3078 then
3079 fail_uniqueness;
3080
3081 ELSIF p_check_unique_flag = 'S' THEN
3082
3083 --IF p_trn_type = 'VAT' THEN
3084
3085 check_numeric(check_result);
3086 IF check_result = 'S'
3087 then
3088 IF length(p_trn_value) = 14
3089 then
3090 IF NL_PREFIX = 'NL'
3091 then
3092 IF B_VALUE = 'B'
3093 then
3094 IF (substr(SUFFIX_VALUE,1,1)
3095 in ('0','1','2','3','4','5','6','7','8','9'))
3096 and (substr(SUFFIX_VALUE,2,1)
3097 in ('0','1','2','3','4','5','6','7','8','9'))
3098 then
3099
3100 /* Calculate Check Digit for Netherlands */
3101
3102 FOR position_i IN 3..10 LOOP
3103
3104 integer_value := substr(p_trn_value,position_i,1);
3105
3106 multiplied_number := integer_value * (12-position_i);
3107 multiplied_sum := multiplied_sum + multiplied_number;
3108
3109
3110 END LOOP;
3111
3112 IF mod(multiplied_sum,11) = check_digit
3113 then
3114 pass_check;
3115 ELSE
3116 -- Logging Infra: Statement level
3117 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3118 l_log_msg := 'Check digit is incorrect.';
3119 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3120 END IF;
3121 -- Logging Infra
3122
3123 p_return_status := FND_API.G_RET_STS_ERROR;
3124 p_error_buffer := 'ZX_REG_NUM_INVALID';
3125 END IF;
3126
3127 ELSE
3128 -- Logging Infra: Statement level
3129 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3130 l_log_msg := 'The prefix of the Tax Registration Number must be alphabetic character.';
3131 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3132 END IF;
3133 -- Logging Infra
3134
3135 p_return_status := FND_API.G_RET_STS_ERROR;
3136 p_error_buffer := 'ZX_REG_NUM_INVALID';
3137 END IF;
3138
3142 l_log_msg := 'The Tax Registration Number does not have required character B in the suffix.';
3139 ELSE
3140 -- Logging Infra: Statement level
3141 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3143 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3144 END IF;
3145 -- Logging Infra
3146
3147 p_return_status := FND_API.G_RET_STS_ERROR;
3148 p_error_buffer := 'ZX_REG_NUM_INVALID';
3149 END IF;
3150
3151 ELSE
3152 -- Logging Infra: Statement level
3153 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3154 l_log_msg := 'The prefix of the Tax Registration Number is incorrect.';
3155 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3156 END IF;
3157 -- Logging Infra
3158
3159 p_return_status := FND_API.G_RET_STS_ERROR;
3160 p_error_buffer := 'ZX_REG_NUM_INVALID';
3161 END IF;
3162
3163 ELSE
3164 -- Logging Infra: Statement level
3165 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3166 l_log_msg := 'The length of the Tax Registration Number is not 14.';
3167 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3168 END IF;
3169 -- Logging Infra
3170 IF length(p_trn_value) > 14 THEN
3171 p_return_status := FND_API.G_RET_STS_ERROR;
3172 p_error_buffer := 'ZX_REG_NUM_TOO_BIG';
3173 ELSE
3174 p_return_status := FND_API.G_RET_STS_ERROR;
3175 p_error_buffer := 'ZX_REG_NUM_INVALID';
3176 END IF;
3177 END IF;
3178
3179 ELSE
3180 -- Logging Infra: Statement level
3181 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3182 l_log_msg := 'The Tax Registration Number contains an alphanumeric character where a numeric character is expected.';
3183 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3184 END IF;
3185 -- Logging Infra
3186
3187 p_return_status := FND_API.G_RET_STS_ERROR;
3188 p_error_buffer := 'ZX_REG_NUM_INVALID_ALPHA';
3189 END IF;
3190
3191 --ELSE
3192
3193 -- Logging Infra: Statement level
3194 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3195 -- l_log_msg := 'Tax Regegistration Type is not VAT.';
3196 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3197 -- END IF;
3198 -- Logging Infra
3199
3200 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
3201
3202 --END IF;
3203
3204 END IF;
3205
3206 END VALIDATE_TRN_NL;
3207
3208
3209 /* *********** End VALIDATE_TRN_NL ****************** */
3210
3211
3212 procedure VALIDATE_TRN_PL (p_trn_value IN VARCHAR2,
3213 p_trn_type IN VARCHAR2,
3214 p_check_unique_flag IN VARCHAR2,
3215 p_return_status OUT NOCOPY VARCHAR2,
3216 p_error_buffer OUT NOCOPY VARCHAR2)
3217 AS
3218 num_check VARCHAR2(2);
3219 len number;
3220
3221 -- Logging Infra
3222 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_PL';
3223 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3224 trn_value VARCHAR2(50);
3225
3226 BEGIN
3227
3228 -- Logging Infra: Setting up runtime level
3229 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3230
3231 -- Logging Infra: Procedure level
3232 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3233 l_log_msg := l_procedure_name||'(+)';
3234 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
3235 END IF;
3236 -- Logging Infra
3237
3238 -- Logging Infra: Statement level
3239 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3240 l_log_msg := 'Parameters ';
3241 l_log_msg := l_log_msg||'p_trn_value: '||p_trn_value;
3242 l_log_msg := l_log_msg||' p_trn_type: '||p_trn_type;
3243 l_log_msg := l_log_msg||' p_check_unique_flag: '||p_check_unique_flag;
3244 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3245 END IF;
3246 -- Logging Infra
3247
3248 len := length(p_trn_value);
3249 trn_value := upper(p_trn_value);
3250
3251 IF p_check_unique_flag = 'E' THEN
3252
3253 -- Logging Infra: Statement level
3254 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3255 l_log_msg := 'The Tax Registration Number is already used.';
3256 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3257 END IF;
3258 -- Logging Infra
3259
3260 p_return_status := FND_API.G_RET_STS_ERROR;
3261 p_error_buffer := 'ZX_REG_NUM_INVALID';
3262
3263 ELSIF p_check_unique_flag = 'S' THEN
3264
3265 --IF p_trn_type = 'VAT' THEN
3266
3267 /* check length = 13 or 15 */
3268 IF len = 10 or len = 13 or len = 15 THEN
3269 --
3270 num_check := '1';
3271 num_check := nvl( rtrim(
3272 translate( substr(p_trn_value,1,len),
3273 '1234567890-',
3277 -- Logging Infra: Statement level
3274 ' ' ) ), '0' );
3275 IF num_check = '0' THEN
3276 --
3278 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3279 l_log_msg := 'The length of the Tax Registration Number is 13 or 15, and it is numeric.';
3280 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3281 END IF;
3282 -- Logging Infra
3283
3284 p_return_status := FND_API.G_RET_STS_SUCCESS;
3285 p_error_buffer := NULL;
3286 ELSE
3287
3288 -- Logging Infra: Statement level
3289 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3290 l_log_msg := 'Neither the length of the Tax Registration Number is 13 or 15 nor it is numeric.';
3291 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3292 END IF;
3293 -- Logging Infra
3294
3295 p_return_status := FND_API.G_RET_STS_ERROR;
3296 p_error_buffer := 'ZX_REG_NUM_INVALID';
3297 --
3298 END IF;
3299 ELSIF (substr(trn_value,1,2) = 'PL') and
3300 (common_check_numeric(p_trn_value,3,length(p_trn_value)) = '0') THEN
3301
3302 -- Logging Infra: Statement level
3303 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3304 l_log_msg := 'The Tax Registration Number is numeric.';
3305 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3306 END IF;
3307 -- Logging Infra
3308
3309 p_return_status := FND_API.G_RET_STS_SUCCESS;
3310 p_error_buffer := NULL;
3311
3312 ELSE
3313
3314 -- Logging Infra: Statement level
3315 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3316 l_log_msg := 'The length of the Tax Registration Number is not 10 or 13 or 15.';
3317 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3318 END IF;
3319 -- Logging Infra
3320
3321 p_return_status := FND_API.G_RET_STS_ERROR;
3322 p_error_buffer := 'ZX_REG_NUM_INVALID';
3323 --
3324 END IF;
3325
3326 --ELSE
3327
3328 -- Logging Infra: Statement level
3329 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3330 -- l_log_msg := 'Tax Regegistration Type is not VAT.';
3331 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3332 -- END IF;
3333 -- Logging Infra
3334
3335 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
3336
3337 --END IF;
3338
3339 END IF;
3340
3341 END VALIDATE_TRN_PL;
3342
3343 /* *********** End VALIDATE_TRN_PL ****************** */
3344
3345
3346 procedure VALIDATE_TRN_PT (p_trn_value IN VARCHAR2,
3347 p_trn_type IN VARCHAR2,
3348 p_check_unique_flag IN VARCHAR2,
3349 p_return_status OUT NOCOPY VARCHAR2,
3350 p_error_buffer OUT NOCOPY VARCHAR2)
3351 AS
3352
3353 check_digit VARCHAR2(2);
3354 position_i number(2);
3355 integer_value number(1);
3356 mod11 number(8);
3357 multiplied_number number(38);
3358 multiplied_sum number(38) := 0;
3359 cal_cd number(2);
3360 TRN_STRING VARCHAR2(50);
3361
3362 -- Logging Infra
3363 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_PT';
3364 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3365
3366 BEGIN
3367
3368 -- Logging Infra: Setting up runtime level
3369 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3370
3371 -- Logging Infra: Procedure level
3372 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3373 l_log_msg := l_procedure_name||'(+)';
3374 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
3375 END IF;
3376 -- Logging Infra
3377
3378 -- Logging Infra: Statement level
3379 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3380 l_log_msg := 'Parameters ';
3381 l_log_msg := l_log_msg||'p_trn_value: '||p_trn_value;
3382 l_log_msg := l_log_msg||' p_trn_type: '||p_trn_type;
3383 l_log_msg := l_log_msg||' p_check_unique_flag: '||p_check_unique_flag;
3384 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3385 END IF;
3386 -- Logging Infra
3387
3388 check_digit := substr(p_trn_value, length(p_trn_value));
3389 TRN_STRING := substr(p_trn_value,3,length(p_trn_value));
3390
3391 IF p_check_unique_flag = FND_API.G_RET_STS_ERROR THEN
3392 --
3393
3394 -- Logging Infra: Statement level
3395 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3396 l_log_msg := 'The Tax Registration Number is already used.';
3397 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3398 END IF;
3399 -- Logging Infra
3400
3401 p_return_status := FND_API.G_RET_STS_ERROR;
3402 p_error_buffer := 'ZX_REG_NUM_INVALID';
3403
3404 ELSIF p_check_unique_flag = 'S' THEN
3405 --
3406
3407 --IF p_trn_type = 'VAT' THEN
3408
3412 common_check_numeric(TRN_STRING,1,length(TRN_STRING)) = '0' THEN
3409 /* check length = 11, prefix = 'PT' and the last nine digits are numeric */
3410
3411 IF length(p_trn_value) = 11 and substr(p_trn_value,1,2) = 'PT' and
3413 --
3414 FOR position_i IN 1..8 LOOP
3415 --
3416 integer_value := substr(TRN_STRING,position_i,1);
3417
3418 multiplied_number := integer_value * (10-position_i);
3419 multiplied_sum := multiplied_sum + multiplied_number;
3420 --
3421 END LOOP;
3422 p_error_buffer := 'multiplied_sum '||to_char(multiplied_sum);
3423
3424 mod11 := (floor(multiplied_sum/11)+1)*11;
3425 p_error_buffer := 'mod11 '||to_char(mod11);
3426
3427 cal_cd := mod11-multiplied_sum;
3428 p_error_buffer := 'cal_cd '||to_char(cal_cd);
3429
3430 IF (mod(multiplied_sum,11) = 0) OR (cal_cd > 9) THEN
3431 --
3432 cal_cd := 0;
3433 p_error_buffer := 'cal_cd '||to_char(cal_cd);
3434 --
3435 END IF;
3436
3437 p_error_buffer := 'check_digit '||check_digit;
3438
3439 IF cal_cd = check_digit THEN
3440 --
3441
3442 -- Logging Infra: Statement level
3443 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3444 l_log_msg := 'The Tax Registration Number is valid.';
3445 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3446 END IF;
3447 -- Logging Infra
3448
3449 p_return_status := FND_API.G_RET_STS_SUCCESS;
3450 p_error_buffer := NULL;
3451 ELSE
3452
3453 -- Logging Infra: Statement level
3454 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3455 l_log_msg := 'The Tax Registration Number is invalid.';
3456 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3457 END IF;
3458 -- Logging Infra
3459
3460 p_return_status := FND_API.G_RET_STS_ERROR;
3461 p_error_buffer := 'ZX_REG_NUM_INVALID';
3462 --
3463 END IF;
3464 --
3465 ELSE
3466 --
3467
3468 -- Logging Infra: Statement level
3469 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3470 l_log_msg := 'The Tax Registration Number is invalid.';
3471 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3472 END IF;
3473 -- Logging Infra
3474
3475 p_return_status := FND_API.G_RET_STS_ERROR;
3476 p_error_buffer := 'ZX_REG_NUM_INVALID';
3477 --
3478 END IF;
3479
3480 --ELSE
3481
3482 -- Logging Infra: Statement level
3483 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3484 -- l_log_msg := 'Tax Regegistration Type is not VAT.';
3485 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3486 -- END IF;
3487 -- Logging Infra
3488
3489 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
3490
3491 --END IF;
3492
3493 --
3494 END IF;
3495 --
3496 END VALIDATE_TRN_PT;
3497
3498 /********** End of VALIDATE_TRN_PT **************************/
3499
3500 procedure VALIDATE_TRN_ES(p_trn IN VARCHAR2,
3501 p_trn_type IN VARCHAR2,
3502 p_check_unique_flag IN VARCHAR2,
3503 p_return_status OUT NOCOPY VARCHAR2,
3504 p_error_buffer OUT NOCOPY VARCHAR2)
3505 AS
3506
3507 trn_value VARCHAR2(50);
3508 work_trn VARCHAR2(50);
3509 check_digit VARCHAR2(1);
3510 numeric_result VARCHAR2(50);
3511 work_trn_d NUMBER(20);
3512 trn_prefix VARCHAR2(2);
3513 x_trn_number VARCHAR2(50);
3514 whole_value VARCHAR2(50);
3515
3516 -- Logging Infra
3517 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_ES';
3518 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3519 N_Check_Digit varchar2(26) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
3520 N_Check_Flag number := 0 ;
3521
3522 /**************************/
3523 /* SUB-PROCEDURES SECTION */
3524 /**************************/
3525
3526 procedure fail_uniqueness is
3527 begin
3528
3529 -- Logging Infra: Statement level
3530 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3531 l_log_msg := 'The Tax Registration Number is already used.';
3532 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3533 END IF;
3534 -- Logging Infra
3535
3536 p_return_status := FND_API.G_RET_STS_ERROR;
3537 p_error_buffer := 'ZX_REG_NUM_INVALID';
3538 end fail_uniqueness;
3539
3540 procedure fail_check is
3541 begin
3542
3543 -- Logging Infra: Statement level
3544 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3545 l_log_msg := 'The Tax Registration Number is invalid.';
3546 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3547 END IF;
3548 -- Logging Infra
3549
3550 p_return_status := FND_API.G_RET_STS_ERROR;
3554 procedure pass_check is
3551 p_error_buffer := 'ZX_REG_NUM_INVALID';
3552 end fail_check;
3553
3555 begin
3556
3557 -- Logging Infra: Statement level
3558 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3559 l_log_msg := 'The Tax Registration Number is valid.';
3560 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3561 END IF;
3562 -- Logging Infra
3563
3564 p_return_status := FND_API.G_RET_STS_SUCCESS;
3565 p_error_buffer := NULL;
3566 end pass_check;
3567
3568 /****************/
3569 /* MAIN SECTION */
3570 /****************/
3571
3572 BEGIN
3573
3574 -- Logging Infra: Setting up runtime level
3575 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3576
3577 WHOLE_VALUE := upper(p_trn);
3578 trn_value := upper(p_trn);
3579 check_digit := substr(trn_value, length(trn_value));
3580
3581 trn_value := substr(WHOLE_VALUE,3);
3582 trn_prefix := substr(WHOLE_VALUE,1,2);
3583
3584 IF p_check_unique_flag = 'E' THEN
3585 fail_uniqueness;
3586 ELSIF p_check_unique_flag = 'S' THEN
3587
3588 --IF p_trn_type = 'VAT' THEN
3589
3590 IF instr(trn_value,' ') = 0 THEN
3591 IF TRN_PREFIX = 'ES' THEN
3592 /** make sure that Fiscal Code is greater than 1 char **/
3593 IF length(trn_value) > 1
3594 then
3595
3596 /** make sure that Fiscal Code starts with one of the following characters **/
3597 /* Added TRN_VALUE 'Y','Z','J','U','V','R','W' as part of 7533946 */
3598 IF upper(substr(trn_value,1,1))
3599 in ('A','B','C','D','E','F','G','T','P','Q','S','H','J','U','V','R','W',
3600 'X','Y','Z','K','L','M','N','0','1','2','3','4','5','6','7','8','9')
3601 then
3602
3603 /** If the Fiscal Code starts with a T, then no futher **/
3604 /** validation is required **/
3605 IF substr(trn_value,1,1) = 'T'
3606 then
3607 pass_check;
3608
3609 /** Fiscal Code does not start with 'T' **/
3610 /* Added the validation for Code starting with N.
3611 Forward porting was missing in earlier enhancement (Bug 2996623).
3612 Added the validation as part of 7533946*/
3613 ELSIF substr(trn_value,1,1) = 'N'
3614 then
3615 numeric_result :=
3616 common_check_numeric(trn_value,2,length(trn_value)-2);
3617 IF numeric_result = '0'
3618 then
3619 /* its numeric so continue */
3620 select instr(N_Check_Digit,check_digit) into
3621 N_Check_Flag from dual;
3622 If N_Check_Flag > 0
3623 then
3624 pass_check;
3625 else
3626 fail_check;
3627 end if;
3628 ELSE
3629 fail_check;
3630 END IF;
3631 --end of 2996623
3632 /** IF the Fiscal Code begins with the following **/
3633 /** It's a physical person. The TRN has to end in a **/
3634 /** specific letter. Eg Valids = X1596399S,2601871L **/
3635 ELSIF substr(trn_value,1,1) in
3636 ('X','K','L','M','Y','Z','0','1','2','3','4','5','6','7','8','9')
3637 then
3638 IF substr(trn_value,1,1) in ('X','K','L','M','Y','Z')
3639 then
3640 numeric_result
3641 := common_check_numeric(trn_value,2,length(trn_value)-2);
3642 IF numeric_result = '0'
3643 then
3644 /* its numeric so continue */
3645 IF substr(trn_value,1,1) in ('Y','Z')
3646 then
3647 SELECT Decode(SubStr(trn_value,1,1),'Y',1,2)
3648 ||SubStr(trn_value,2,length(trn_value)-2)
3649 INTO work_trn
3650 FROM DUAL;
3651 ELSE
3652 work_trn := substr(trn_value,2,length(trn_value)-2);
3653 END IF;
3654 IF substr('TRWAGMYFPDXBNJZSQVHLCKE',mod
3655 (to_number(work_trn) ,23) + 1,1) = check_digit
3656 then
3657 pass_check;
3658 ELSE
3659 fail_check;
3660 END IF;
3661 ELSE
3662 -- Logging Infra: Statement level
3663 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3664 l_log_msg := 'The Tax Registration Number contains an alphanumeric character where a numeric character is expected.';
3668
3665 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3666 END IF;
3667 -- Logging Infra
3669 p_return_status := FND_API.G_RET_STS_ERROR;
3670 p_error_buffer := 'ZX_REG_NUM_INVALID_ALPHA';
3671 END IF; /* end of numeric check */
3672
3673 ELSE
3674 numeric_result
3675 := common_check_numeric(trn_value,1,length(trn_value)-1);
3676 IF numeric_result = '0'
3677 then
3678 /* its numeric so continue */
3679
3680 work_trn := substr(trn_value,1,length(trn_value)-1);
3681 IF substr('TRWAGMYFPDXBNJZSQVHLCKE',mod
3682 (to_number(work_trn) ,23) + 1,1) = check_digit
3683 then
3684 pass_check;
3685 ELSE
3686 fail_check;
3687 END IF;
3688 ELSE
3689 -- Logging Infra: Statement level
3690 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3691 l_log_msg := 'The Tax Registration Number contains an alphanumeric character where a numeric character is expected.';
3692 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3693 END IF;
3694 -- Logging Infra
3695
3696 p_return_status := FND_API.G_RET_STS_ERROR;
3697 p_error_buffer := 'ZX_REG_NUM_INVALID_ALPHA';
3698 END IF; /* end of numeric check */
3699
3700 END IF;
3701
3702 ELSIF substr(trn_value,1,1) in
3703 ('A','B','C','D','E','F','G','H','P','Q','S','J','U','V','R','W')
3704 then
3705 /** It's a company. Examples of valid company TRN is **/
3706 /** A78361482 A78211646 F2831001I Q0467001D P0801500J **/
3707 numeric_result
3708 := common_check_numeric(trn_value,2,length(trn_value)-2);
3709 IF numeric_result = '0'
3710 then
3711 /* its numeric so continue */
3712 work_trn := substr(trn_value,2,length(trn_value)-2);
3713 work_trn_d := to_number(substr(work_trn,2,1)) +
3714 to_number(substr(work_trn,4,1)) +
3715 to_number(substr(work_trn,6,1)) +
3716 to_number(substr(to_char(to_number(substr(work_trn,1,1)) * 2),1,1)) +
3717 to_number(nvl(substr(to_char(to_number(substr(work_trn,1,1))
3718 * 2),2,1),'0')) +
3719 to_number(substr(to_char(to_number(substr(work_trn,3,1)) * 2),1,1)) +
3720 to_number(nvl(substr(to_char(to_number(substr(work_trn,3,1))
3721 * 2),2,1),'0')) +
3722 to_number(substr(to_char(to_number(substr(work_trn,5,1)) * 2),1,1)) +
3723 to_number(nvl(substr(to_char(to_number(substr(work_trn,5,1))
3724 * 2),2,1),'0')) +
3725 to_number(substr(to_char(to_number(substr(work_trn,7,1)) * 2),1,1)) +
3726 to_number(nvl(substr(to_char(to_number(substr(work_trn,7,1))
3727 * 2),2,1),'0'))
3728 + nvl(to_number(substr(work_trn,8,1)),0)
3729 + nvl(to_number(substr(to_char(to_number(substr(work_trn,9,1)) * 2),1,1)),0) +
3730 to_number(nvl(substr(to_char(to_number(substr(work_trn,9,1))
3731 * 2),2,1),'0'));
3732
3733 IF check_digit in ('A','B','C','D','E','F','G','H','I','J')
3734 then
3735 IF substr('JABCDEFGHI',((ceil(work_trn_d/10) * 10)
3736 - work_trn_d) + 1, 1) = check_digit
3737 then
3738 pass_check;
3739 ELSE
3740 fail_check;
3741 END IF;
3742 ELSIF check_digit
3743 = to_char((ceil(work_trn_d/10) *10) - work_trn_d)
3744 then
3745
3746 pass_check;
3747 ELSE
3748 fail_check;
3749 END IF;
3750
3751 ELSE
3752 -- Logging Infra: Statement level
3753 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3754 l_log_msg := 'The Tax Registration Number contains an alphanumeric character where a numeric character is expected.';
3755 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3756 END IF;
3757 -- Logging Infra
3758
3759 p_return_status := FND_API.G_RET_STS_ERROR;
3760 p_error_buffer := 'ZX_REG_NUM_INVALID_ALPHA';
3761 END IF; /* end of numeric check */
3762 ELSE
3766 ELSE
3763 fail_check;
3764 END IF; /* End of person or company check */
3765
3767 fail_check;
3768 END IF; /* does not start with a valid character */
3769 ELSE
3770 -- Logging Infra: Statement level
3771 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3772 l_log_msg := 'The length of the Tax Registration Number is not correct.';
3773 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3774 END IF;
3775 -- Logging Infra
3776
3777 p_return_status := FND_API.G_RET_STS_ERROR;
3778 p_error_buffer := 'ZX_REG_NUM_TOO_BIG';
3779 END IF; /* end of length check */
3780
3781 ELSE
3782 fail_check; /* Not a Fiscal or a TAX code */
3783 END IF;
3784
3785 ELSE
3786 fail_check; /* Its got a space in it */
3787 END IF;
3788
3789 --ELSE
3790
3791 -- Logging Infra: Statement level
3792 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3793 -- l_log_msg := 'Tax Regegistration Type is not VAT.';
3794 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3795 -- END IF;
3796 -- Logging Infra
3797
3798 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
3799
3800 --END IF;
3801
3802 ELSE
3803 pass_check;
3804
3805 END IF; /** of fail uniqueness check **/
3806
3807 END VALIDATE_TRN_ES;
3808
3809
3810 /* *********** End VALIDATE_TRN_ES ****************** */
3811
3812 procedure VALIDATE_TRN_SE (p_trn_value IN VARCHAR2,
3813 p_trn_type IN VARCHAR2,
3814 p_check_unique_flag IN VARCHAR2,
3815 p_return_status OUT NOCOPY VARCHAR2,
3816 p_error_buffer OUT NOCOPY VARCHAR2)
3817 AS
3818
3819 -- Logging Infra
3820 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_SE';
3821 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3822
3823 BEGIN
3824
3825 -- Logging Infra: Setting up runtime level
3826 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3827
3828 -- Logging Infra: Procedure level
3829 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3830 l_log_msg := l_procedure_name||'(+)';
3831 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
3832 END IF;
3833 -- Logging Infra
3834
3835 -- Logging Infra: Statement level
3836 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3837 l_log_msg := 'Parameters ';
3838 l_log_msg := l_log_msg||'p_trn_value: '||p_trn_value;
3839 l_log_msg := l_log_msg||' p_trn_type: '||p_trn_type;
3840 l_log_msg := l_log_msg||' p_check_unique_flag: '||p_check_unique_flag;
3841 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3842 END IF;
3843 -- Logging Infra
3844
3845 IF p_check_unique_flag = 'E' THEN
3846 --
3847
3848 -- Logging Infra: Statement level
3849 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3850 l_log_msg := 'The Tax Registration Number is already used.';
3851 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3852 END IF;
3853 -- Logging Infra
3854
3855 p_return_status := FND_API.G_RET_STS_ERROR;
3856 p_error_buffer := 'ZX_REG_NUM_INVALID';
3857
3858 ELSIF p_check_unique_flag = 'S' THEN
3859 --
3860
3861 --IF p_trn_type = 'VAT' THEN
3862
3863 /* check length = 14, prefix = 'SE', the last twelve digits are numeric and
3864 the last two digits = '01' */
3865
3866 IF length(p_trn_value) = 14 and substr(p_trn_value,1,2) = 'SE' and
3867 common_check_numeric(p_trn_value,3,length(p_trn_value)) = '0' and
3868 substr(p_trn_value,length(p_trn_value)-1,2) = '01' THEN
3869 --
3870 -- Logging Infra: Statement level
3871 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3872 l_log_msg := 'Length is 14, Prefix is SE, and last 12 digits are numeric.';
3873 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3874 END IF;
3875 -- Logging Infra
3876
3877 p_return_status := FND_API.G_RET_STS_SUCCESS;
3878 p_error_buffer := NULL;
3879 ELSE
3880
3881 -- Logging Infra: Statement level
3882 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3883 l_log_msg := 'One of the following condition is incorrect.: Length is 14, Prefix is SE, or last 12 digits are numeric.';
3884 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3885 END IF;
3886 -- Logging Infra
3887
3888 p_return_status := FND_API.G_RET_STS_ERROR;
3889 p_error_buffer := 'ZX_REG_NUM_INVALID';
3890 --
3891 END IF;
3892 --
3893
3894 --ELSE
3895
3896 -- Logging Infra: Statement level
3897 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3898 -- l_log_msg := 'Tax Regegistration Type is not VAT.';
3899 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3903 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
3900 -- END IF;
3901 -- Logging Infra
3902
3904
3905 --END IF;
3906
3907 END IF;
3908
3909 END VALIDATE_TRN_SE;
3910
3911 /* *********** End VALIDATE_TRN_SE ****************** */
3912
3913 procedure VALIDATE_TRN_GB (p_trn_value IN VARCHAR2,
3914 p_trn_type IN VARCHAR2,
3915 p_check_unique_flag IN VARCHAR2,
3916 p_return_status OUT NOCOPY VARCHAR2,
3917 p_error_buffer OUT NOCOPY VARCHAR2)
3918 AS
3919
3920 TRN_STRING VARCHAR2(50);
3921 check_digit VARCHAR2(2);
3922 check_total NUMBER;
3923 integer_value number(2);
3924 position_i integer;
3925
3926 -- Logging Infra
3927 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_GB';
3928 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
3929
3930 BEGIN
3931
3932 -- Logging Infra: Setting up runtime level
3933 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3934
3935 -- Logging Infra: Procedure level
3936 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
3937 l_log_msg := l_procedure_name||'(+)';
3938 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
3939 END IF;
3940 -- Logging Infra
3941
3942 -- Logging Infra: Statement level
3943 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3944 l_log_msg := 'Parameters ';
3945 l_log_msg := l_log_msg||'p_trn_value: '||p_trn_value;
3946 l_log_msg := l_log_msg||' p_trn_type: '||p_trn_type;
3947 l_log_msg := l_log_msg||' p_check_unique_flag: '||p_check_unique_flag;
3948 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3949 END IF;
3950 -- Logging Infra
3951
3952 TRN_STRING := substr(p_trn_value,3,length(p_trn_value));
3953 check_digit := substr(TRN_STRING,8,2); /* The last two digits are the 'check digits' */
3954
3955 IF p_check_unique_flag = 'E' THEN
3956 --
3957
3958 -- Logging Infra: Statement level
3959 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3960 l_log_msg := 'The Tax Registration Number is already used.';
3961 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3962 END IF;
3963 -- Logging Infra
3964
3965 p_return_status := FND_API.G_RET_STS_ERROR;
3966 p_error_buffer := 'ZX_REG_NUM_INVALID';
3967
3968 ELSIF p_check_unique_flag = 'S' THEN
3969 --
3970
3971 --IF p_trn_type = 'VAT' THEN
3972
3973 /* check length = 5, 9 , 12, prefix = 'GB' and the last digits are numeric */
3974
3975 IF substr(p_trn_value,1,2) = 'GB' and length(TRN_STRING) in (5, 9, 12) and
3976 common_check_numeric(TRN_STRING,1,length(TRN_STRING)) = '0' THEN
3977 --
3978 IF length(TRN_STRING) = 9 THEN
3979 --
3980 check_total := 0;
3981 FOR position_i IN 1..7 LOOP
3982 --
3983 integer_value := substr(TRN_STRING,position_i,1);
3984 check_total := check_total + integer_value * ( 9 - position_i );
3985 --
3986 END LOOP;
3987 WHILE check_total > 0 LOOP
3988 /* until we get a 2-digit negative number */
3989 check_total := check_total - 97;
3990 END LOOP;
3991
3992 IF check_digit + check_total = 0 THEN
3993 --
3994
3995 -- Logging Infra: Statement level
3996 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3997 l_log_msg := 'The Tax Registration Number is valid.';
3998 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
3999 END IF;
4000 -- Logging Infra
4001
4002 p_return_status := FND_API.G_RET_STS_SUCCESS;
4003 p_error_buffer := NULL;
4004 ELSE
4005
4006 -- Logging Infra: Statement level
4007 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4008 l_log_msg := 'The Tax Registration Number is invalid.';
4009 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4010 END IF;
4011 -- Logging Infra
4012
4013 p_return_status := FND_API.G_RET_STS_ERROR;
4014 p_error_buffer := 'ZX_REG_NUM_INVALID';
4015 --
4016 END IF;
4017 ELSE
4018
4019 -- Logging Infra: Statement level
4020 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4021 l_log_msg := 'The Tax Registration Number is valid.';
4022 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4023 END IF;
4024 -- Logging Infra
4025
4026 p_return_status := FND_API.G_RET_STS_SUCCESS;
4027 p_error_buffer := NULL;
4028 --
4029 END IF;
4030 ELSE
4031
4032 -- Logging Infra: Statement level
4033 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4034 l_log_msg := 'Please enter a valid Tax Registration Number.';
4035 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4036 END IF;
4037 -- Logging Infra
4038
4039 p_return_status := FND_API.G_RET_STS_ERROR;
4043
4040 p_error_buffer := 'ZX_REG_NUM_INVALID';
4041 --
4042 END IF;
4044 --ELSE
4045
4046 -- Logging Infra: Statement level
4047 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4048 -- l_log_msg := 'Tax Regegistration Type is not VAT.';
4049 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4050 -- END IF;
4051 -- Logging Infra
4052
4053 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
4054
4055 --END IF;
4056
4057 END IF;
4058
4059 END VALIDATE_TRN_GB;
4060
4061 /* *********** End VALIDATE_TRN_GB ****************** */
4062
4063 procedure VALIDATE_TRN_CH (p_trn_value IN VARCHAR2,
4064 p_trn_type IN VARCHAR2,
4065 p_check_unique_flag IN VARCHAR2,
4066 p_return_status OUT NOCOPY VARCHAR2,
4067 p_error_buffer OUT NOCOPY VARCHAR2)
4068 AS
4069
4070 -- Logging Infra
4071 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_CH';
4072 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
4073
4074 BEGIN
4075
4076 -- Logging Infra: Setting up runtime level
4077 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4078
4079 -- Logging Infra: Procedure level
4080 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4081 l_log_msg := l_procedure_name||'(+)';
4082 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
4083 END IF;
4084 -- Logging Infra
4085
4086 -- Logging Infra: Statement level
4087 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4088 l_log_msg := 'Parameters ';
4089 l_log_msg := l_log_msg||'p_trn_value: '||p_trn_value;
4090 l_log_msg := l_log_msg||' p_trn_type: '||p_trn_type;
4091 l_log_msg := l_log_msg||' p_check_unique_flag: '||p_check_unique_flag;
4092 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4093 END IF;
4094 -- Logging Infra
4095
4096 IF p_check_unique_flag = 'E' THEN
4097
4098 -- Logging Infra: Statement level
4099 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4100 l_log_msg := 'The Tax Registration Number is already used.';
4101 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4102 END IF;
4103 -- Logging Infra
4104
4105 p_return_status := FND_API.G_RET_STS_ERROR;
4106 p_error_buffer := 'ZX_REG_NUM_INVALID';
4107
4108 ELSIF p_check_unique_flag = 'S' THEN
4109
4110 --IF p_trn_type = 'VAT' THEN
4111
4112 /* check length = 8, prefix = 'CH' and the last six digits are numeric */
4113
4114 IF length(p_trn_value) = 8 and substr(p_trn_value,1,2) = 'CH' and
4115 common_check_numeric(p_trn_value,3,length(p_trn_value)) = '0' THEN
4116 --
4117 -- Logging Infra: Statement level
4118 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4119 l_log_msg := 'Length is 8, and prefix is CH, and other digits are numeric.';
4120 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4121 END IF;
4122 -- Logging Infra
4123
4124 p_return_status := FND_API.G_RET_STS_SUCCESS;
4125 p_error_buffer := NULL;
4126 ELSE
4127
4128 -- Logging Infra: Statement level
4129 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4130 l_log_msg := 'One of the following condition is incorrect: Length is 8, and prefix is CH, and other digits are numeric.';
4131 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4132 END IF;
4133 -- Logging Infra
4134
4135 p_return_status := FND_API.G_RET_STS_ERROR;
4136 p_error_buffer := 'ZX_REG_NUM_INVALID';
4137 --
4138 END IF;
4139
4140 --ELSE
4141
4142 -- Logging Infra: Statement level
4143 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4144 -- l_log_msg := 'Tax Regegistration Type is not VAT.';
4145 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4146 -- END IF;
4147 -- Logging Infra
4148
4149 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
4150
4151 --END IF;
4152
4153 END IF;
4154
4155 END VALIDATE_TRN_CH;
4156
4157 /* *********** End VALIDATE_TRN_CH ****************** */
4158
4159
4160 procedure VALIDATE_TRN_RU (p_trn_value IN VARCHAR2,
4161 p_trn_type IN VARCHAR2,
4162 p_check_unique_flag IN VARCHAR2,
4163 p_return_status OUT NOCOPY VARCHAR2,
4164 p_error_buffer OUT NOCOPY VARCHAR2)
4165 AS
4166
4167 -- Logging Infra
4168 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_RU';
4169 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
4170
4171 BEGIN
4172
4173 -- Logging Infra: Setting up runtime level
4174 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4175
4176 -- Logging Infra: Procedure level
4177 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4178 l_log_msg := l_procedure_name||'(+)';
4182
4179 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
4180 END IF;
4181 -- Logging Infra
4183 -- Logging Infra: Statement level
4184 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4185 l_log_msg := 'Parameters ';
4186 l_log_msg := l_log_msg||'p_trn_value: '||p_trn_value;
4187 l_log_msg := l_log_msg||' p_trn_type: '||p_trn_type;
4188 l_log_msg := l_log_msg||' p_check_unique_flag: '||p_check_unique_flag;
4189 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4190 END IF;
4191 -- Logging Infra
4192
4193 IF p_check_unique_flag = 'E' THEN
4194
4195 -- Logging Infra: Statement level
4196 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4197 l_log_msg := 'The Tax Registration Number is already used.';
4198 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4199 END IF;
4200 -- Logging Infra
4201
4202 p_return_status := FND_API.G_RET_STS_ERROR;
4203 p_error_buffer := 'ZX_REG_NUM_INVALID';
4204
4205 ELSIF p_check_unique_flag = 'S' THEN
4206
4207 --IF p_trn_type = 'VAT' THEN
4208
4209 /* check length = 10 or 12 or 9 */
4210
4211 IF length(p_trn_value) = 10 OR
4212 length(p_trn_value) = 12 OR
4213 length(p_trn_value) = 9 THEN
4214 --
4215 -- Logging Infra: Statement level
4216 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4217 l_log_msg := 'The length of the Tax Registration Number is ' || length(p_trn_value);
4218 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4219 END IF;
4220 -- Logging Infra
4221
4222 p_return_status := FND_API.G_RET_STS_SUCCESS;
4223 p_error_buffer := NULL;
4224 ELSE
4225
4226 -- Logging Infra: Statement level
4227 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4228 l_log_msg := 'The length of the Tax Registration Number is ' || length(p_trn_value);
4229 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4230 END IF;
4231 -- Logging Infra
4232
4233 p_return_status := FND_API.G_RET_STS_ERROR;
4234 p_error_buffer := 'ZX_REG_NUM_INVALID';
4235 --
4236 END IF;
4237
4238 --ELSE
4239
4240 -- Logging Infra: Statement level
4241 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4242 -- l_log_msg := 'Tax Regegistration Type is not VAT.';
4243 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4244 -- END IF;
4245 -- Logging Infra
4246
4247 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
4248
4249 --END IF;
4250
4251 END IF;
4252
4253 END VALIDATE_TRN_RU;
4254
4255 /* *********** End VALIDATE_TRN_RU ****************** */
4256
4257 procedure VALIDATE_TRN_HU (p_trn_value IN VARCHAR2,
4258 p_trn_type IN VARCHAR2,
4259 p_check_unique_flag IN VARCHAR2,
4260 p_return_status OUT NOCOPY VARCHAR2,
4261 p_error_buffer OUT NOCOPY VARCHAR2)
4262 AS
4263
4264 l_control_digit NUMBER;
4265 l_trn_value VARCHAR2(50);
4266
4267 -- Logging Infra
4268 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_HU';
4269 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
4270
4271 BEGIN
4272
4273 -- Logging Infra: Setting up runtime level
4274 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4275
4276 -- Logging Infra: Procedure level
4277 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4278 l_log_msg := l_procedure_name||'(+)';
4279 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
4280 END IF;
4281 -- Logging Infra
4282
4283 -- Logging Infra: Statement level
4284 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4285 l_log_msg := 'Parameters ';
4286 l_log_msg := l_log_msg||'p_trn_value: '||p_trn_value;
4287 l_log_msg := l_log_msg||' p_check_unique_flag: '||p_check_unique_flag;
4288 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4289 END IF;
4290 -- Logging Infra
4291
4292 l_trn_value := substr(p_trn_value,3,8);
4293
4294 IF p_check_unique_flag = 'E' THEN
4295
4296 -- Logging Infra: Statement level
4297 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4298 l_log_msg := 'The Tax Registration Number is already used.';
4299 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4300 END IF;
4301 -- Logging Infra
4302
4303 p_return_status := FND_API.G_RET_STS_ERROR;
4304 p_error_buffer := 'ZX_REG_NUM_INVALID';
4305
4306 ELSIF p_check_unique_flag = 'S' THEN
4307
4308 --IF p_trn_type = 'VAT' THEN
4309
4310 l_control_digit := mod(
4311 (to_number(substr(l_trn_value,8,1)) * 1 +
4312 to_number(substr(l_trn_value,7,1)) * 3 +
4313 to_number(substr(l_trn_value,6,1)) * 7 +
4314 to_number(substr(l_trn_value,5,1)) * 9 +
4315 to_number(substr(l_trn_value,4,1)) * 1 +
4319
4316 to_number(substr(l_trn_value,3,1)) * 3 +
4317 to_number(substr(l_trn_value,2,1)) * 7 +
4318 to_number(substr(l_trn_value,1,1)) * 9),10);
4320 IF l_control_digit = 0 THEN
4321
4322 -- Logging Infra: Statement level
4323 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4324 l_log_msg := 'The Tax Registration Number is valid.';
4325 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4326 END IF;
4327 -- Logging Infra
4328
4329 p_return_status := FND_API.G_RET_STS_SUCCESS;
4330 p_error_buffer := NULL;
4331 ELSE
4332
4333 -- Logging Infra: Statement level
4334 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4335 l_log_msg := 'The Tax Registration Number is invalid.';
4336 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4337 END IF;
4338 -- Logging Infra
4339
4340 p_return_status := FND_API.G_RET_STS_ERROR;
4341 p_error_buffer := 'ZX_REG_NUM_INVALID';
4342 END IF;
4343
4344 --ELSE
4345
4346 -- Logging Infra: Statement level
4347 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4348 -- l_log_msg := 'Tax Regegistration Type is not VAT.';
4349 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4350 -- END IF;
4351 -- Logging Infra
4352
4353 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
4354
4355 --END IF;
4356
4357 END IF;
4358
4359 END VALIDATE_TRN_HU;
4360
4361 /* *********** End VALIDATE_TRN_HU ****************** */
4362
4363
4364 /*----------------------------------------------------------------------------*
4365 | PROCEDURE |
4366 | VALIDATE_TRN_BR |
4367 | |
4368 | DESCRIPTION |
4369 | Validatition of Brazil Tax Registration Number |
4370 | |
4371 | PARAMETERS |
4372 | INPUT |
4373 | p_trn_type VARCHAR2 -- Tax Registration Type: CPF |
4374 | CNPJ |
4375 | OTHERS |
4376 | p_trn VARCHAR2 -- Tax Registration Number + |
4377 | Tax Registration Branch |
4378 *----------------------------------------------------------------------------*/
4379 PROCEDURE VALIDATE_TRN_BR (
4380 p_trn IN VARCHAR2,
4381 p_trn_type IN VARCHAR2,
4382 p_return_status OUT NOCOPY VARCHAR2,
4383 p_error_buffer OUT NOCOPY VARCHAR2) IS
4384
4385 l_trn_branch VARCHAR2(4);
4386 l_trn_digit VARCHAR2(2);
4387 l_control_digit_1 NUMBER;
4388 l_control_digit_2 NUMBER;
4389 l_control_digit_XX VARCHAR2(2);
4390
4391 -- Logging Infra
4392 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_BR';
4393 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
4394
4395 BEGIN
4396
4397 -- Logging Infra: Setting up runtime level
4398 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4399
4400 -- Logging Infra: Procedure level
4401 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4402 l_log_msg := l_procedure_name||'(+)';
4403 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
4404 END IF;
4405 -- Logging Infra
4406
4407 -- Logging Infra: Statement level
4408 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4409 l_log_msg := 'Parameters ';
4410 l_log_msg := l_log_msg||'p_trn: '||p_trn;
4411 l_log_msg := l_log_msg||' p_trn_type: '||p_trn_type;
4412 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4413 END IF;
4414 -- Logging Infra
4415
4416 /* Tax Registration Branch */
4417 l_trn_branch := substr(p_trn,10,4);
4418 l_trn_digit := substr(p_trn,14,2);
4419
4420 IF p_trn_type = 'CPF'
4421 THEN
4422 /* Validate CPF */
4423 IF nvl(l_trn_branch,'0000') <> '0000'
4424 THEN
4425 /* Tax Registration Number branch for CPF type should be NULL or zero */
4426 -- Logging Infra: Statement level
4427 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4428 l_log_msg := 'CPF Tax Registration Number branch is not valid.';
4429 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4430 END IF;
4431 -- Logging Infra
4432
4433 p_return_status := FND_API.G_RET_STS_ERROR;
4434 p_error_buffer := 'ZX_REG_NUM_INVALID';
4435 ELSE
4436 /* Calculate two digit controls of tax registration number CPF type */
4437
4438 l_control_digit_1 := (11 - mod(
4439 (to_number(substr(p_trn,9,1)) * 2 +
4440 to_number(substr(p_trn,8,1)) * 3 +
4444 to_number(substr(p_trn,4,1)) * 7 +
4441 to_number(substr(p_trn,7,1)) * 4 +
4442 to_number(substr(p_trn,6,1)) * 5 +
4443 to_number(substr(p_trn,5,1)) * 6 +
4445 to_number(substr(p_trn,3,1)) * 8 +
4446 to_number(substr(p_trn,2,1)) * 9 +
4447 to_number(substr(p_trn,1,1)) * 10),11));
4448
4449 IF l_control_digit_1 in ('11','10')
4450 THEN
4451 l_control_digit_1 := 0;
4452 END IF;
4453
4454 l_control_digit_2 := (11 - mod((l_control_digit_1 * 2 +
4455 to_number(substr(p_trn,9,1)) * 3 +
4456 to_number(substr(p_trn,8,1)) * 4 +
4457 to_number(substr(p_trn,7,1)) * 5 +
4458 to_number(substr(p_trn,6,1)) * 6 +
4459 to_number(substr(p_trn,5,1)) * 7 +
4460 to_number(substr(p_trn,4,1)) * 8 +
4461 to_number(substr(p_trn,3,1)) * 9 +
4462 to_number(substr(p_trn,2,1)) * 10 +
4463 to_number(substr(p_trn,1,1)) * 11),11));
4464
4465 IF l_control_digit_2 in ('11','10')
4466 THEN
4467 l_control_digit_2 := 0;
4468 END IF;
4469
4470 l_control_digit_XX := substr(to_char(l_control_digit_1),1,1) ||
4471 substr(to_char(l_control_digit_2),1,1);
4472
4473 IF l_control_digit_XX <> l_trn_digit
4474 THEN
4475
4476 -- Logging Infra: Statement level
4477 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4478 l_log_msg := 'The CPF Inscription number is not valid.';
4479 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4480 END IF;
4481 -- Logging Infra
4482
4483 /* Digit controls do not match */
4484 p_return_status:= FND_API.G_RET_STS_ERROR;
4485 p_error_buffer := 'ZX_REG_NUM_INVALID';
4486 ELSE
4487
4488 -- Logging Infra: Statement level
4489 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4490 l_log_msg := 'The Tax Registration Number is valid.';
4491 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4492 END IF;
4493 -- Logging Infra
4494
4495 p_return_status:= FND_API.G_RET_STS_SUCCESS;
4496 p_error_buffer := NULL;
4497 END IF;
4498 END IF;
4499
4500 ELSIF p_trn_type = 'OTHERS'
4501 THEN
4502
4503 -- Logging Infra: Statement level
4504 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4505 l_log_msg := 'The Tax Registration Number is valid.';
4506 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4507 END IF;
4508 -- Logging Infra
4509
4510 p_return_status:= FND_API.G_RET_STS_SUCCESS;
4511 p_error_buffer := NULL;
4512
4513 --ELSIF p_trn_type = 'CNPJ'
4514 --THEN
4515 ELSE -- Bug 4299188 CNPJ validation is now default validation and would be used even when trn type is null etc
4516
4517 /* Calculate two digit controls of tax registration number CNPJ type */
4518
4519 l_control_digit_1 := (11 - mod(
4520 (to_number(substr(l_trn_branch,4,1)) * 2 +
4521 to_number(substr(l_trn_branch,3,1)) * 3 +
4522 to_number(substr(l_trn_branch,2,1)) * 4 +
4523 to_number(substr(l_trn_branch,1,1)) * 5 +
4524 to_number(substr(p_trn,9,1)) * 6 +
4525 to_number(substr(p_trn,8,1)) * 7 +
4526 to_number(substr(p_trn,7,1)) * 8 +
4527 to_number(substr(p_trn,6,1)) * 9 +
4528 to_number(substr(p_trn,5,1)) * 2 +
4529 to_number(substr(p_trn,4,1)) * 3 +
4530 to_number(substr(p_trn,3,1)) * 4 +
4531 to_number(substr(p_trn,2,1))* 5),11));
4532
4533 IF l_control_digit_1 in ('11','10')
4534 THEN
4535 l_control_digit_1 := 0;
4536 END IF;
4537
4538 l_control_digit_2 := (11 - mod(
4539 ( (l_control_digit_1 * 2) +
4540 to_number(substr(l_trn_branch,4,1)) * 3 +
4541 to_number(substr(l_trn_branch,3,1)) * 4 +
4542 to_number(substr(l_trn_branch,2,1)) * 5 +
4543 to_number(substr(l_trn_branch,1,1)) * 6 +
4544 to_number(substr(p_trn,9,1)) * 7 +
4545 to_number(substr(p_trn,8,1)) * 8 +
4546 to_number(substr(p_trn,7,1)) * 9 +
4547 to_number(substr(p_trn,6,1)) * 2 +
4548 to_number(substr(p_trn,5,1)) * 3 +
4549 to_number(substr(p_trn,4,1)) * 4 +
4550 to_number(substr(p_trn,3,1)) * 5 +
4551 to_number(substr(p_trn,2,1)) * 6),11));
4552
4553 IF l_control_digit_2 in ('11','10')
4554 THEN
4555 l_control_digit_2 := 0;
4556 END IF;
4557
4558 l_control_digit_XX := substr(to_char(l_control_digit_1),1,1) ||
4559 substr(to_char(l_control_digit_2),1,1);
4560
4561 IF l_trn_digit <> l_control_digit_XX
4562 THEN
4563
4564 -- Logging Infra: Statement level
4565 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4566 l_log_msg := 'The CGC Tax Registration Number is not valid.';
4567 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4568 END IF;
4569 -- Logging Infra
4570
4571 p_return_status:= FND_API.G_RET_STS_ERROR;
4572 p_error_buffer := 'ZX_REG_NUM_INVALID';
4573 ELSE
4574
4575 -- Logging Infra: Statement level
4579 END IF;
4576 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4577 l_log_msg := 'The Tax Registration Number is valid.';
4578 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4580 -- Logging Infra
4581
4582 p_return_status:= FND_API.G_RET_STS_SUCCESS;
4583 p_error_buffer := NULL;
4584 END IF;
4585 --ELSE
4586
4587 -- Logging Infra: Statement level
4588 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4589 -- l_log_msg := 'The Tax Registration Number is invalid.';
4590 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4591 -- END IF;
4592 -- Logging Infra
4593
4594 -- p_return_status:= FND_API.G_RET_STS_ERROR;
4595 -- p_error_buffer := 'ZX_REG_NUM_INVALID';
4596 END IF;
4597
4598 END VALIDATE_TRN_BR;
4599
4600 /* *********** End VALIDATE_TRN_BR ****************** */
4601
4602 PROCEDURE VALIDATE_TRN_AR (
4603 p_trn IN VARCHAR2,
4604 p_trn_type IN VARCHAR2,
4605 p_return_status OUT NOCOPY VARCHAR2,
4606 p_error_buffer OUT NOCOPY VARCHAR2 ) AS
4607
4608 trn_value VARCHAR2(50);
4609 l_length_result VARCHAR2(10);
4610 l_numeric_result VARCHAR2(10);
4611 l_val_digit VARCHAR2(2);
4612 l_trn_digit VARCHAR2(1);
4613
4614 -- Logging Infra
4615 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_AR';
4616 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
4617
4618 BEGIN
4619
4620 -- Logging Infra: Setting up runtime level
4621 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4622
4623 -- Logging Infra: Procedure level
4624 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4625 l_log_msg := l_procedure_name||'(+)';
4626 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
4627 END IF;
4628 -- Logging Infra
4629
4630 -- Logging Infra: Statement level
4631 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4632 l_log_msg := 'Parameters ';
4633 l_log_msg := l_log_msg||'p_trn: '||p_trn;
4634 l_log_msg := l_log_msg||' p_trn_type: '||p_trn_type;
4635 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4636 END IF;
4637 -- Logging Infra
4638
4639 trn_value := upper(p_trn);
4640 trn_value := replace(p_trn,' ','');
4641
4642 /* *** Check Length of Tax Registration Number *** */
4643
4644 IF p_trn_type = 'CUIL' THEN
4645 l_length_result := common_check_length('AR',11,trn_value);
4646 l_trn_digit := substr(trn_value,11,1);
4647 trn_value := substr(trn_value,1,10);
4648 ELSIF p_trn_type = 'DNI' THEN
4649 l_length_result := common_check_length('AR',9,trn_value);
4650 l_trn_digit := substr(trn_value,9,1);
4651 trn_value := substr(trn_value,1,8);
4652 ELSE -- Bug 4299188 CUIT logic is default logic now and will work when type is CUIT or Null or any other type (other than CUIL, DNI)
4653 l_length_result := common_check_length('AR',11,trn_value);
4654 l_trn_digit := substr(trn_value,11,1);
4655 trn_value := substr(trn_value,1,10);
4656 END IF;
4657
4658 IF l_length_result = 'TRUE' THEN
4659
4660 /* *** Check Numeric of Tax Registration Number *** */
4661
4662 l_numeric_result := common_check_numeric(trn_value,1,length(trn_value));
4663
4664 IF l_numeric_result = '0' THEN
4665
4666 /* *** Check Numeric of Tax Registration Number *** */
4667
4668 l_val_digit:=(11-MOD(((TO_NUMBER(SUBSTR(trn_value,10,1))) *2 +
4669 (TO_NUMBER(SUBSTR(trn_value,9,1))) *3 +
4670 (TO_NUMBER(SUBSTR(trn_value,8,1))) *4 +
4671 (TO_NUMBER(SUBSTR(trn_value,7,1))) *5 +
4672 (TO_NUMBER(SUBSTR(trn_value,6,1))) *6 +
4673 (TO_NUMBER(SUBSTR(trn_value,5,1))) *7 +
4674 (TO_NUMBER(SUBSTR(trn_value,4,1))) *2 +
4675 (TO_NUMBER(SUBSTR(trn_value,3,1))) *3 +
4676 (TO_NUMBER(SUBSTR(trn_value,2,1))) *4 +
4677 (TO_NUMBER(SUBSTR(trn_value,1,1))) *5),11));
4678
4679 IF l_val_digit ='10' THEN
4680 l_val_digit:='9';
4681 ELSIF l_val_digit='11' THEN
4682 l_val_digit:='0';
4683 END IF;
4684
4685 IF l_val_digit <> l_trn_digit THEN
4686
4687 -- Logging Infra: Statement level
4688 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4689 l_log_msg := 'The Tax Registration Number is invalid.';
4690 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4691 END IF;
4692 -- Logging Infra
4693
4694 p_return_status := FND_API.G_RET_STS_ERROR;
4695 p_error_buffer := 'ZX_REG_NUM_INVALID';
4696 ELSE
4697
4698 -- Logging Infra: Statement level
4699 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4700 l_log_msg := 'The Tax Registration Number is valid.';
4701 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4702 END IF;
4703 -- Logging Infra
4704
4708
4705 p_return_status := FND_API.G_RET_STS_SUCCESS;
4706 p_error_buffer := NULL;
4707 END IF;
4709 ELSE
4710
4711 -- Logging Infra: Statement level
4712 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4713 l_log_msg := 'The Tax Registration Number must be numeric.';
4714 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4715 END IF;
4716 -- Logging Infra
4717
4718 p_return_status := FND_API.G_RET_STS_ERROR;
4719 p_error_buffer := 'ZX_REG_NUM_MUST_BE_NUMERIC';
4720 END IF;
4721
4722 ELSE
4723
4724 -- Logging Infra: Statement level
4725 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4726 l_log_msg := 'The length of the Tax Registration Number is not correct.';
4727 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4728 END IF;
4729 -- Logging Infra
4730 IF length(trn_value) > 11 THEN
4731 p_return_status := FND_API.G_RET_STS_ERROR;
4732 p_error_buffer := 'ZX_REG_NUM_TOO_BIG';
4733 ELSE
4734 p_return_status := FND_API.G_RET_STS_ERROR;
4735 p_error_buffer := 'ZX_REG_NUM_INVALID';
4736 END IF;
4737 END IF;
4738
4739 END VALIDATE_TRN_AR;
4740
4741 /* *********** End VALIDATE_TRN_AR ****************** */
4742
4743 PROCEDURE VALIDATE_TRN_CL (
4744 p_trn IN VARCHAR2,
4745 p_return_status OUT NOCOPY VARCHAR2,
4746 p_error_buffer OUT NOCOPY VARCHAR2 ) AS
4747
4748 trn_value VARCHAR2(50);
4749 l_length_result VARCHAR2(10);
4750 l_numeric_result VARCHAR2(12);
4751 l_var1 VARCHAR2(50);
4752 l_val_digit VARCHAR2(2);
4753 l_trn_digit VARCHAR2(1);
4754
4755 -- Logging Infra
4756 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_CL';
4757 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
4758
4759 BEGIN
4760
4761 -- Logging Infra: Setting up runtime level
4762 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4763
4764 -- Logging Infra: Procedure level
4765 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4766 l_log_msg := l_procedure_name||'(+)';
4767 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
4768 END IF;
4769 -- Logging Infra
4770
4771 -- Logging Infra: Statement level
4772 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4773 l_log_msg := 'Parameters ';
4774 l_log_msg := l_log_msg||'p_trn: '||p_trn;
4775 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4776 END IF;
4777 -- Logging Infra
4778
4779 trn_value := upper(p_trn);
4780 trn_value := replace(p_trn,' ','');
4781
4782 /* *** Check Length of Tax Registration Number *** */
4783
4784 l_length_result := common_check_length('CL',13,trn_value); /* Bug 3192083 */
4785
4786 IF l_length_result = 'TRUE' THEN
4787
4788 /* *** Check Numeric of Tax Registration Number *** */
4789
4790 IF length(trn_value) < 13 THEN
4791 trn_value := LPAD(trn_value,13,'0');
4792 l_trn_digit := substr(trn_value,13,1);
4793 ELSE
4794 l_trn_digit := substr(trn_value,13,1);
4795 END IF;
4796
4797 trn_value := substr(trn_value,1,12);
4798 l_numeric_result := common_check_numeric(trn_value,1,length(trn_value));
4799
4800 IF l_numeric_result = '0' THEN
4801
4802 /* *** Check Algorithm of Tax Registration Number *** */
4803
4804 l_var1 := trn_value;
4805 l_val_digit:=(11-MOD(((TO_NUMBER(SUBSTR(l_var1,12,1))) *2 +
4806 (TO_NUMBER(SUBSTR(l_var1,11,1))) *3 +
4807 (TO_NUMBER(SUBSTR(l_var1,10,1))) *4 +
4808 (TO_NUMBER(SUBSTR(l_var1,9,1))) *5 +
4809 (TO_NUMBER(SUBSTR(l_var1,8,1))) *6 +
4810 (TO_NUMBER(SUBSTR(l_var1,7,1))) *7 +
4811 (TO_NUMBER(SUBSTR(l_var1,6,1))) *2 +
4812 (TO_NUMBER(SUBSTR(l_var1,5,1))) *3 +
4813 (TO_NUMBER(SUBSTR(l_var1,4,1))) *4 +
4814 (TO_NUMBER(SUBSTR(l_var1,3,1))) *5 +
4815 (TO_NUMBER(SUBSTR(l_var1,2,1))) *6 +
4816 (TO_NUMBER(SUBSTR(l_var1,1,1))) *7),11));
4817
4818 IF l_val_digit = '10'THEN
4819 l_val_digit := 'K';
4820 ELSIF l_val_digit = '11' THEN
4821 l_val_digit := '0';
4822 END IF;
4823
4824 IF l_val_digit <> l_trn_digit THEN
4825
4826 -- Logging Infra: Statement level
4827 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4828 l_log_msg := 'The validation digit and Tax Registration Number digit is different.';
4829 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4830 END IF;
4831 -- Logging Infra
4832
4833 p_return_status := FND_API.G_RET_STS_ERROR;
4834 p_error_buffer := 'ZX_REG_NUM_INVALID';
4835 ELSE
4836
4837 -- Logging Infra: Statement level
4838 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4839 l_log_msg := 'The Tax Registration Number is valid.';
4840 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4841 END IF;
4845 p_error_buffer := NULL;
4842 -- Logging Infra
4843
4844 p_return_status := FND_API.G_RET_STS_SUCCESS;
4846 END IF;
4847
4848 ELSE
4849
4850 -- Logging Infra: Statement level
4851 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4852 l_log_msg := 'The Tax Registration Number must be numeric.';
4853 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4854 END IF;
4855 -- Logging Infra
4856
4857 p_return_status := FND_API.G_RET_STS_ERROR;
4858 p_error_buffer := 'ZX_REG_NUM_MUST_BE_NUMERIC';
4859 END IF;
4860
4861 ELSE
4862
4863 -- Logging Infra: Statement level
4864 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4865 l_log_msg := 'The length of the Tax Registration Number is not correct.';
4866 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4867 END IF;
4868 -- Logging Infra
4869 IF length(trn_value) > 11 THEN
4870 p_return_status := FND_API.G_RET_STS_ERROR;
4871 p_error_buffer := 'ZX_REG_NUM_TOO_BIG';
4872 ELSE
4873 p_return_status := FND_API.G_RET_STS_ERROR;
4874 p_error_buffer := 'ZX_REG_NUM_INVALID';
4875 END IF;
4876 END IF;
4877
4878
4879 END VALIDATE_TRN_CL;
4880
4881 /* *********** End VALIDATE_TRN_CL ****************** */
4882
4883 PROCEDURE VALIDATE_TRN_CO (
4884 p_trn IN VARCHAR2,
4885 p_return_status OUT NOCOPY VARCHAR2,
4886 p_error_buffer OUT NOCOPY VARCHAR2 ) AS
4887
4888 trn_value VARCHAR2(50);
4889 l_length_result VARCHAR2(10);
4890 l_numeric_result VARCHAR2(15);
4891 l_var1 VARCHAR2(50);
4892 l_val_digit VARCHAR2(2);
4893 l_trn_digit VARCHAR2(1);
4894 l_mod_value NUMBER(2);
4895
4896 -- Logging Infra
4897 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_CO';
4898 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
4899
4900 BEGIN
4901
4902 -- Logging Infra: Setting up runtime level
4903 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4904
4905 -- Logging Infra: Procedure level
4906 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
4907 l_log_msg := l_procedure_name||'(+)';
4908 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
4909 END IF;
4910 -- Logging Infra
4911
4912 -- Logging Infra: Statement level
4913 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4914 l_log_msg := 'Parameters ';
4915 l_log_msg := l_log_msg||'p_trn: '||p_trn;
4916 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4917 END IF;
4918 -- Logging Infra
4919
4920 trn_value := upper(p_trn);
4921 trn_value := replace(p_trn,' ','');
4922
4923 /* *** Check Length of Tax Registration Number *** */
4924
4925 l_length_result := common_check_length('CO',16,trn_value);
4926
4927 IF l_length_result = 'TRUE' THEN
4928
4929 /* *** Check Numeric of Tax Registration Number *** */
4930
4931 IF length(trn_value) < 16 THEN
4932 trn_value := LPAD(trn_value,16,'0');
4933 l_trn_digit := substr(trn_value,16,1);
4934 ELSE
4935 l_trn_digit := substr(trn_value,16,1);
4936 END IF;
4937
4938 trn_value := substr(trn_value,1,15);
4939 l_numeric_result := common_check_numeric(trn_value,1,length(trn_value));
4940
4941 IF l_numeric_result = '0' THEN
4942
4943 /* *** Check Algorithm of Tax Registration Number *** */
4944
4945 l_var1:=trn_value;
4946 l_mod_value:=(MOD(((TO_NUMBER(SUBSTR(l_var1,15,1))) *3 +
4947 (TO_NUMBER(SUBSTR(l_var1,14,1))) *7 +
4948 (TO_NUMBER(SUBSTR(l_var1,13,1))) *13 +
4949 (TO_NUMBER(SUBSTR(l_var1,12,1))) *17 +
4950 (TO_NUMBER(SUBSTR(l_var1,11,1))) *19 +
4951 (TO_NUMBER(SUBSTR(l_var1,10,1))) *23 +
4952 (TO_NUMBER(SUBSTR(l_var1,9,1))) *29 +
4953 (TO_NUMBER(SUBSTR(l_var1,8,1))) *37 +
4954 (TO_NUMBER(SUBSTR(l_var1,7,1))) *41 +
4955 (TO_NUMBER(SUBSTR(l_var1,6,1))) *43 +
4956 (TO_NUMBER(SUBSTR(l_var1,5,1))) *47 +
4957 (TO_NUMBER(SUBSTR(l_var1,4,1))) *53 +
4958 (TO_NUMBER(SUBSTR(l_var1,3,1))) *59 +
4959 (TO_NUMBER(SUBSTR(l_var1,2,1))) *67 +
4960 (TO_NUMBER(SUBSTR(l_var1,1,1))) *71),11));
4961
4962 IF (l_mod_value IN (1,0)) THEN
4963 l_val_digit:=l_mod_value;
4964 ELSE
4965 l_val_digit:=11-l_mod_value;
4966 END IF;
4967
4968 IF l_val_digit <> l_trn_digit THEN
4969
4970 -- Logging Infra: Statement level
4971 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4972 l_log_msg := 'The Tax Registration Number is invalid.';
4973 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4974 END IF;
4975 -- Logging Infra
4976
4977 p_return_status := FND_API.G_RET_STS_ERROR;
4978 p_error_buffer := 'ZX_REG_NUM_INVALID';
4982 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4979 ELSE
4980
4981 -- Logging Infra: Statement level
4983 l_log_msg := 'The Tax Registration Number is valid.';
4984 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4985 END IF;
4986 -- Logging Infra
4987
4988 p_return_status := FND_API.G_RET_STS_SUCCESS;
4989 p_error_buffer := NULL;
4990 END IF;
4991
4992 ELSE
4993
4994 -- Logging Infra: Statement level
4995 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
4996 l_log_msg := 'The Tax Registration Number must be numeric.';
4997 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
4998 END IF;
4999 -- Logging Infra
5000
5001 IF length(trn_value) > 16 THEN
5002 p_return_status := FND_API.G_RET_STS_ERROR;
5003 p_error_buffer := 'ZX_REG_NUM_TOO_BIG';
5004 ELSE
5005 p_return_status := FND_API.G_RET_STS_ERROR;
5006 p_error_buffer := 'ZX_REG_NUM_INVALID';
5007 END IF;
5008
5009 END IF;
5010
5011 ELSE
5012
5013 -- Logging Infra: Statement level
5014 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5015 l_log_msg := 'The length of the Tax Registration Number is not correct.';
5016 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5017 END IF;
5018 -- Logging Infra
5019
5020 p_return_status := FND_API.G_RET_STS_ERROR;
5021 p_error_buffer := 'ZX_REG_NUM_TOO_BIG';
5022
5023 END IF;
5024
5025
5026 END VALIDATE_TRN_CO;
5027
5028 /* *********** End VALIDATE_TRN_CO ****************** */
5029
5030 PROCEDURE VALIDATE_TRN_TW (
5031 p_trn IN VARCHAR2,
5032 p_return_status OUT NOCOPY VARCHAR2,
5033 p_error_buffer OUT NOCOPY VARCHAR2) AS
5034
5035 trn_value VARCHAR2(50);
5036 l_length_result VARCHAR2(10);
5037 l_numeric_result VARCHAR2(10);
5038
5039 -- Logging Infra
5040 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_TW';
5041 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
5042
5043 BEGIN
5044
5045 -- Logging Infra: Setting up runtime level
5046 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5047
5048 -- Logging Infra: Procedure level
5049 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5050 l_log_msg := l_procedure_name||'(+)';
5051 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
5052 END IF;
5053 -- Logging Infra
5054
5055 -- Logging Infra: Statement level
5056 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5057 l_log_msg := 'Parameters ';
5058 l_log_msg := l_log_msg||'p_trn: '||p_trn;
5059 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5060 END IF;
5061 -- Logging Infra
5062
5063 trn_value := upper(p_trn);
5064 trn_value := replace(p_trn,' ','');
5065
5066 /* *** Check Length of Tax Registration Number *** */
5067
5068 l_length_result := common_check_length('TW',9,trn_value);
5069
5070 IF l_length_result = 'TRUE' THEN
5071
5072 /* *** Check Numeric of Tax Registration Number *** */
5073
5074 l_numeric_result := common_check_numeric(trn_value,1,length(trn_value));
5075
5076 IF l_numeric_result = '0' THEN
5077
5078 -- Logging Infra: Statement level
5079 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5080 l_log_msg := 'The Tax Registration Number is 9 and it is numeric only.';
5081 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5082 END IF;
5083 -- Logging Infra
5084
5085 p_return_status := FND_API.G_RET_STS_SUCCESS;
5086 p_error_buffer := NULL;
5087 ELSE
5088
5089 -- Logging Infra: Statement level
5090 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5091 l_log_msg := 'The length of Tax Registration Number is 9, but it is not Numeric.';
5092 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5093 END IF;
5094 -- Logging Infra
5095
5096 p_return_status := FND_API.G_RET_STS_ERROR;
5097 p_error_buffer := 'ZX_REG_NUM_MUST_BE_NUMERIC';
5098 END IF;
5099
5100 ELSE
5101
5102 -- Logging Infra: Statement level
5103 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5104 l_log_msg := 'The length of the Tax Registration Number is not 9.';
5105 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5106 END IF;
5107 -- Logging Infra
5108
5109 IF length(trn_value) > 10 THEN
5110 p_return_status := FND_API.G_RET_STS_ERROR;
5111 p_error_buffer := 'ZX_REG_NUM_TOO_BIG';
5112 ELSE
5113 p_return_status := FND_API.G_RET_STS_ERROR;
5114 p_error_buffer := 'ZX_REG_NUM_INVALID';
5115 END IF;
5116 END IF;
5117
5118
5119 END VALIDATE_TRN_TW;
5120
5121 /* *********** End VALIDATE_TRN_TW ****************** */
5122
5123
5124 procedure VALIDATE_TRN_MT (p_trn_value IN VARCHAR2,
5128 p_error_buffer OUT NOCOPY VARCHAR2)
5125 p_trn_type IN VARCHAR2,
5126 p_check_unique_flag IN VARCHAR2,
5127 p_return_status OUT NOCOPY VARCHAR2,
5129 AS
5130
5131 -- Logging Infra
5132 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_MT';
5133 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
5134
5135 trn_value VARCHAR2(50);
5136 mt_prefix VARCHAR2(2);
5137
5138 BEGIN
5139
5140 trn_value := upper(p_trn_value);
5141 mt_prefix := substr(trn_value,1,2);
5142
5143 -- Logging Infra: Setting up runtime level
5144 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5145
5146 -- Logging Infra: Procedure level
5147 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5148 l_log_msg := l_procedure_name||'(+)';
5149 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
5150 END IF;
5151 -- Logging Infra
5152
5153 -- Logging Infra: Statement level
5154 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5155 l_log_msg := 'Parameters ';
5156 l_log_msg := l_log_msg||'p_trn_value: '||p_trn_value;
5157 l_log_msg := l_log_msg||' p_trn_type: '||p_trn_type;
5158 l_log_msg := l_log_msg||' p_check_unique_flag: '||p_check_unique_flag;
5159 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5160 END IF;
5161 -- Logging Infra
5162
5163 IF p_check_unique_flag = 'E' THEN
5164
5165 -- Logging Infra: Statement level
5166 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5167 l_log_msg := 'The Tax Registration Number is already used.';
5168 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5169 END IF;
5170 -- Logging Infra
5171
5172 p_return_status := FND_API.G_RET_STS_ERROR;
5173 p_error_buffer := 'ZX_REG_NUM_INVALID';
5174
5175 ELSIF p_check_unique_flag = 'S' THEN
5176
5177 --IF p_trn_type = 'VAT' THEN
5178
5179 IF mt_prefix = 'MT' THEN
5180
5181 /* check length = 10 */
5182 IF length(trn_value) = 10 THEN
5183
5184 /* Check eight digits are numeric */
5185 IF common_check_numeric(trn_value,3,length(trn_value)) = '0' THEN
5186
5187 -- Logging Infra: Statement level
5188 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5189 l_log_msg := 'The Tax Registration Number is numeric.';
5190 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5191 END IF;
5192 -- Logging Infra
5193
5194 p_return_status := FND_API.G_RET_STS_SUCCESS;
5195 p_error_buffer := NULL;
5196 ELSE
5197
5198 -- Logging Infra: Statement level
5199 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5200 l_log_msg := 'The Tax Registration Number must be numeric.';
5201 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5202 END IF;
5203 -- Logging Infra
5204
5205 p_return_status := FND_API.G_RET_STS_ERROR;
5206 p_error_buffer := 'ZX_REG_NUM_MUST_BE_NUMERIC';
5207
5208 END IF;
5209
5210 ELSE
5211
5212 -- Logging Infra: Statement level
5213 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5214 l_log_msg := 'The length of the Tax Registration Number is not 10.';
5215 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5216 END IF;
5217 -- Logging Infra
5218
5219 IF length(trn_value) > 10 THEN
5220 p_return_status := FND_API.G_RET_STS_ERROR;
5221 p_error_buffer := 'ZX_REG_NUM_TOO_BIG';
5222 ELSE
5223 p_return_status := FND_API.G_RET_STS_ERROR;
5224 p_error_buffer := 'ZX_REG_NUM_INVALID';
5225 END IF;
5226 --
5227 END IF;
5228
5229 ELSE
5230
5231 /* check length = 8 */
5232 IF length(trn_value) = 8 THEN
5233
5234 /* Check eight digits are numeric */
5235 IF common_check_numeric(trn_value,1,length(trn_value)) = '0' THEN
5236
5237 -- Logging Infra: Statement level
5238 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5239 l_log_msg := 'The Tax Registration Number is numeric.';
5240 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5241 END IF;
5242 -- Logging Infra
5243
5244 p_return_status := FND_API.G_RET_STS_SUCCESS;
5245 p_error_buffer := NULL;
5246 ELSE
5247
5248 -- Logging Infra: Statement level
5249 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5250 l_log_msg := 'The Tax Registration Number must be numeric.';
5251 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5252 END IF;
5253 -- Logging Infra
5254
5255 p_return_status := FND_API.G_RET_STS_ERROR;
5259
5256 p_error_buffer := 'ZX_REG_NUM_MUST_BE_NUMERIC';
5257
5258 END IF;
5260 ELSE
5261
5262 -- Logging Infra: Statement level
5263 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5264 l_log_msg := 'The length of the Tax Registration Number is not 8.';
5265 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5266 END IF;
5267 -- Logging Infra
5268
5269 IF length(trn_value) > 8 THEN
5270 p_return_status := FND_API.G_RET_STS_ERROR;
5271 p_error_buffer := 'ZX_REG_NUM_TOO_BIG';
5272 ELSE
5273 p_return_status := FND_API.G_RET_STS_ERROR;
5274 p_error_buffer := 'ZX_REG_NUM_INVALID';
5275 END IF;
5276
5277 END IF;
5278
5279 END IF;
5280
5281 --ELSE
5282
5283 -- Logging Infra: Statement level
5284 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5285 -- l_log_msg := 'Tax Regegistration Type is not VAT.';
5286 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5287 -- END IF;
5288 -- Logging Infra
5289
5290 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
5291
5292 --END IF;
5293
5294 END IF;
5295
5296 END VALIDATE_TRN_MT;
5297
5298 /* *********** End VALIDATE_TRN_MT ****************** */
5299
5300
5301 procedure VALIDATE_TRN_LV (p_trn_value IN VARCHAR2,
5302 p_trn_type IN VARCHAR2,
5303 p_check_unique_flag IN VARCHAR2,
5304 p_return_status OUT NOCOPY VARCHAR2,
5305 p_error_buffer OUT NOCOPY VARCHAR2)
5306 AS
5307
5308 -- Logging Infra
5309 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_LV';
5310 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
5311
5312 trn_value VARCHAR2(50);
5313 lv_prefix VARCHAR2(2);
5314
5315 BEGIN
5316
5317 trn_value := upper(p_trn_value);
5318 lv_prefix := substr(trn_value,1,2);
5319
5320 -- Logging Infra: Setting up runtime level
5321 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5322
5323 -- Logging Infra: Procedure level
5324 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5325 l_log_msg := l_procedure_name||'(+)';
5326 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
5327 END IF;
5328 -- Logging Infra
5329
5330 -- Logging Infra: Statement level
5331 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5332 l_log_msg := 'Parameters ';
5333 l_log_msg := l_log_msg||'p_trn_value: '||p_trn_value;
5334 l_log_msg := l_log_msg||' p_trn_type: '||p_trn_type;
5335 l_log_msg := l_log_msg||' p_check_unique_flag: '||p_check_unique_flag;
5336 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5337 END IF;
5338 -- Logging Infra
5339
5340 IF p_check_unique_flag = 'E' THEN
5341
5342 -- Logging Infra: Statement level
5343 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5344 l_log_msg := 'The Tax Registration Number is already used.';
5345 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5346 END IF;
5347 -- Logging Infra
5348
5349 p_return_status := FND_API.G_RET_STS_ERROR;
5350 p_error_buffer := 'ZX_REG_NUM_INVALID';
5351
5352 ELSIF p_check_unique_flag = 'S' THEN
5353
5354 --IF p_trn_type = 'VAT' THEN
5355
5356 IF lv_prefix = 'LV' THEN
5357
5358 /* check length = 13 */
5359 IF length(trn_value) = 13 THEN
5360
5361 /* Check eight digits are numeric */
5362 IF common_check_numeric(trn_value,3,length(trn_value)) = '0' THEN
5363
5364 -- Logging Infra: Statement level
5365 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5366 l_log_msg := 'The Tax Registration Number is numeric.';
5367 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5368 END IF;
5369 -- Logging Infra
5370
5371 p_return_status := FND_API.G_RET_STS_SUCCESS;
5372 p_error_buffer := NULL;
5373 ELSE
5374
5375 -- Logging Infra: Statement level
5376 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5377 l_log_msg := 'The Tax Registration Number must be numeric.';
5378 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5379 END IF;
5380 -- Logging Infra
5381
5382 p_return_status := FND_API.G_RET_STS_ERROR;
5383 p_error_buffer := 'ZX_REG_NUM_MUST_BE_NUMERIC';
5384
5385 END IF;
5386
5387 ELSE
5388 -- Logging Infra: Statement level
5389 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5390 l_log_msg := 'The length of the Tax Registration Number is not 13.';
5391 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5392 END IF;
5393 -- Logging Infra
5394
5395 IF length(trn_value) > 13 THEN
5399 p_return_status := FND_API.G_RET_STS_ERROR;
5396 p_return_status := FND_API.G_RET_STS_ERROR;
5397 p_error_buffer := 'ZX_REG_NUM_TOO_BIG';
5398 ELSE
5400 p_error_buffer := 'ZX_REG_NUM_INVALID';
5401 END IF;
5402
5403 END IF;
5404
5405 ELSE
5406
5407 /* check length = 11 */
5408 IF length(trn_value) = 11 THEN
5409
5410 /* Check eight digits are numeric */
5411 IF common_check_numeric(trn_value,1,length(trn_value)) = '0' THEN
5412
5413 -- Logging Infra: Statement level
5414 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5415 l_log_msg := 'The Tax Registration Number is numeric.';
5416 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5417 END IF;
5418 -- Logging Infra
5419
5420 p_return_status := FND_API.G_RET_STS_SUCCESS;
5421 p_error_buffer := NULL;
5422 ELSE
5423
5424 -- Logging Infra: Statement level
5425 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5426 l_log_msg := 'The Tax Registration Number must be numeric.';
5427 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5428 END IF;
5429 -- Logging Infra
5430
5431 p_return_status := FND_API.G_RET_STS_ERROR;
5432 p_error_buffer := 'ZX_REG_NUM_MUST_BE_NUMERIC';
5433
5434 END IF;
5435
5436 ELSE
5437 -- Logging Infra: Statement level
5438 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5439 l_log_msg := 'The length of the Tax Registration Number is not 11.';
5440 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5441 END IF;
5442 -- Logging Infra
5443
5444 IF length(trn_value) > 11 THEN
5445 p_return_status := FND_API.G_RET_STS_ERROR;
5446 p_error_buffer := 'ZX_REG_NUM_TOO_BIG';
5447 ELSE
5448 p_return_status := FND_API.G_RET_STS_ERROR;
5449 p_error_buffer := 'ZX_REG_NUM_INVALID';
5450 END IF;
5451
5452
5453 END IF;
5454
5455 END IF;
5456
5457 --ELSE
5458
5459 -- Logging Infra: Statement level
5460 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5461 -- l_log_msg := 'Tax Regegistration Type is not VAT.';
5462 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5463 -- END IF;
5464 -- Logging Infra
5465
5466 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
5467
5468 --END IF;
5469
5470 END IF;
5471
5472 END VALIDATE_TRN_LV;
5473
5474 /* *********** End VALIDATE_TRN_LV ****************** */
5475
5476
5477 procedure VALIDATE_TRN_SI (p_trn_value IN VARCHAR2,
5478 p_trn_type IN VARCHAR2,
5479 p_check_unique_flag IN VARCHAR2,
5480 p_return_status OUT NOCOPY VARCHAR2,
5481 p_error_buffer OUT NOCOPY VARCHAR2)
5482 AS
5483
5484 -- Logging Infra
5485 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_MT';
5486 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
5487
5488 trn_value VARCHAR2(50);
5489 si_prefix VARCHAR2(2);
5490
5491 BEGIN
5492
5493 trn_value := upper(p_trn_value);
5494 si_prefix := substr(trn_value,1,2);
5495
5496 -- Logging Infra: Setting up runtime level
5497 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5498
5499 -- Logging Infra: Procedure level
5500 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5501 l_log_msg := l_procedure_name||'(+)';
5502 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
5503 END IF;
5504 -- Logging Infra
5505
5506 -- Logging Infra: Statement level
5507 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5508 l_log_msg := 'Parameters ';
5509 l_log_msg := l_log_msg||'p_trn_value: '||p_trn_value;
5510 l_log_msg := l_log_msg||' p_trn_type: '||p_trn_type;
5511 l_log_msg := l_log_msg||' p_check_unique_flag: '||p_check_unique_flag;
5512 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5513 END IF;
5514 -- Logging Infra
5515
5516 IF p_check_unique_flag = 'E' THEN
5517
5518 -- Logging Infra: Statement level
5519 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5520 l_log_msg := 'The Tax Registration Number is already used.';
5521 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5522 END IF;
5523 -- Logging Infra
5524
5525 p_return_status := FND_API.G_RET_STS_ERROR;
5526 p_error_buffer := 'ZX_REG_NUM_INVALID';
5527
5528 ELSIF p_check_unique_flag = 'S' THEN
5529
5530 --IF p_trn_type = 'VAT' THEN
5531
5532 IF si_prefix = 'SI' THEN
5533
5534 /* check length = 10 */
5535 IF length(trn_value) = 10 THEN
5536
5537 /* Check eight digits are numeric */
5538 IF common_check_numeric(trn_value,3,length(trn_value)) = '0' THEN
5539
5540 -- Logging Infra: Statement level
5541 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5542 l_log_msg := 'The Tax Registration Number is numeric.';
5543 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5544 END IF;
5545 -- Logging Infra
5546
5547 p_return_status := FND_API.G_RET_STS_SUCCESS;
5548 p_error_buffer := NULL;
5549 ELSE
5550
5551 -- Logging Infra: Statement level
5552 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5553 l_log_msg := 'The Tax Registration Number must be numeric.';
5554 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5555 END IF;
5556 -- Logging Infra
5557
5558 p_return_status := FND_API.G_RET_STS_ERROR;
5559 p_error_buffer := 'ZX_REG_NUM_MUST_BE_NUMERIC';
5560
5561 END IF;
5562
5563 ELSE
5564
5565 -- Logging Infra: Statement level
5566 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5567 l_log_msg := 'The length of the Tax Registration Number is not 10.';
5568 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5569 END IF;
5570 -- Logging Infra
5571
5572 IF length(trn_value) > 10 THEN
5573 p_return_status := FND_API.G_RET_STS_ERROR;
5574 p_error_buffer := 'ZX_REG_NUM_TOO_BIG';
5575 ELSE
5576 p_return_status := FND_API.G_RET_STS_ERROR;
5577 p_error_buffer := 'ZX_REG_NUM_INVALID';
5578 END IF;
5579
5580 END IF;
5581
5582 ELSE
5583
5584 /* check length = 8 */
5585 IF length(trn_value) = 8 THEN
5586
5587 /* Check eight digits are numeric */
5588 IF common_check_numeric(trn_value,1,length(trn_value)) = '0' THEN
5589
5590 -- Logging Infra: Statement level
5591 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5592 l_log_msg := 'The Tax Registration Number is numeric.';
5593 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5594 END IF;
5595 -- Logging Infra
5596
5597 p_return_status := FND_API.G_RET_STS_SUCCESS;
5598 p_error_buffer := NULL;
5599 ELSE
5600
5601 -- Logging Infra: Statement level
5602 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5603 l_log_msg := 'The Tax Registration Number must be numeric.';
5604 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5605 END IF;
5606 -- Logging Infra
5607
5608 p_return_status := FND_API.G_RET_STS_ERROR;
5609 p_error_buffer := 'ZX_REG_NUM_MUST_BE_NUMERIC';
5610
5611 END IF;
5612
5613 ELSE
5614
5615 -- Logging Infra: Statement level
5616 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5617 l_log_msg := 'The length of the Tax Registration Number is not 8.';
5618 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5619 END IF;
5620 -- Logging Infra
5621
5622 IF length(trn_value) > 8 THEN
5623 p_return_status := FND_API.G_RET_STS_ERROR;
5624 p_error_buffer := 'ZX_REG_NUM_TOO_BIG';
5625 ELSE
5626 p_return_status := FND_API.G_RET_STS_ERROR;
5627 p_error_buffer := 'ZX_REG_NUM_INVALID';
5628 END IF;
5629
5630 END IF;
5631
5632 END IF;
5633
5634 --ELSE
5635
5636 -- Logging Infra: Statement level
5637 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5638 -- l_log_msg := 'Tax Regegistration Type is not VAT.';
5639 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5640 -- END IF;
5641 -- Logging Infra
5642
5643 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
5644
5645 --END IF;
5646
5647 END IF;
5648
5649 END VALIDATE_TRN_SI;
5650
5651 /* *********** End VALIDATE_TRN_SI ****************** */
5652
5653 procedure VALIDATE_TRN_LT (p_trn_value IN VARCHAR2,
5654 p_trn_type IN VARCHAR2,
5655 p_check_unique_flag IN VARCHAR2,
5656 p_return_status OUT NOCOPY VARCHAR2,
5657 p_error_buffer OUT NOCOPY VARCHAR2)
5658 AS
5659
5660 -- Logging Infra
5661 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_LT';
5662 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
5663
5664 trn_value VARCHAR2(50);
5665 lt_prefix VARCHAR2(2);
5666
5667 BEGIN
5668
5669 trn_value := upper(p_trn_value);
5670 lt_prefix := substr(trn_value,1,2);
5671
5672 -- Logging Infra: Setting up runtime level
5676 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5673 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5674
5675 -- Logging Infra: Procedure level
5677 l_log_msg := l_procedure_name||'(+)';
5678 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
5679 END IF;
5680 -- Logging Infra
5681
5682 -- Logging Infra: Statement level
5683 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5684 l_log_msg := 'Parameters ';
5685 l_log_msg := l_log_msg||'p_trn_value: '||p_trn_value;
5686 l_log_msg := l_log_msg||' p_trn_type: '||p_trn_type;
5687 l_log_msg := l_log_msg||' p_check_unique_flag: '||p_check_unique_flag;
5688 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5689 END IF;
5690 -- Logging Infra
5691
5692 IF p_check_unique_flag = 'E' THEN
5693
5694 -- Logging Infra: Statement level
5695 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5696 l_log_msg := 'The Tax Registration Number is already used.';
5697 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5698 END IF;
5699 -- Logging Infra
5700
5701 p_return_status := FND_API.G_RET_STS_ERROR;
5702 p_error_buffer := 'ZX_REG_NUM_INVALID';
5703
5704 ELSIF p_check_unique_flag = 'S' THEN
5705
5706 --IF p_trn_type = 'VAT' THEN
5707
5708 IF lt_prefix = 'LT' THEN
5709
5710 /* check length = 11 or 14 */
5711 IF length(p_trn_value) = 11 OR length(p_trn_value) = 14 THEN
5712
5713 /* Check digits are numeric */
5714 IF common_check_numeric(p_trn_value,3,length(p_trn_value)) = '0' THEN
5715
5716 -- Logging Infra: Statement level
5717 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5718 l_log_msg := 'The Tax Registration Number is numeric.';
5719 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5720 END IF;
5721 -- Logging Infra
5722
5723 p_return_status := FND_API.G_RET_STS_SUCCESS;
5724 p_error_buffer := NULL;
5725 ELSE
5726
5727 -- Logging Infra: Statement level
5728 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5729 l_log_msg := 'The Tax Registration Number must be numeric.';
5730 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5731 END IF;
5732 -- Logging Infra
5733
5734 p_return_status := FND_API.G_RET_STS_ERROR;
5735 p_error_buffer := 'ZX_REG_NUM_MUST_BE_NUMERIC';
5736
5737 END IF;
5738
5739 ELSE
5740
5741 -- Logging Infra: Statement level
5742 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5743 l_log_msg := 'The length of the Tax Registration Number is not 9 or 12.';
5744 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5745 END IF;
5746 -- Logging Infra
5747
5748 IF length(trn_value) > 11 AND length(trn_value) < 14 THEN
5749 p_return_status := FND_API.G_RET_STS_ERROR;
5750 p_error_buffer := 'ZX_REG_NUM_TOO_BIG';
5751 ELSIF length(trn_value) > 14 THEN
5752 p_return_status := FND_API.G_RET_STS_ERROR;
5753 p_error_buffer := 'ZX_REG_NUM_TOO_BIG';
5754 ELSE
5755 p_return_status := FND_API.G_RET_STS_ERROR;
5756 p_error_buffer := 'ZX_REG_NUM_INVALID';
5757 END IF;
5758
5759
5760 END IF;
5761
5762 ELSE
5763
5764 /* check length = 9 or 12 */
5765 IF length(p_trn_value) = 9 OR length(p_trn_value) = 12 THEN
5766
5767 /* Check digits are numeric */
5768 IF common_check_numeric(p_trn_value,1,length(p_trn_value)) = '0' THEN
5769
5770 -- Logging Infra: Statement level
5771 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5772 l_log_msg := 'The Tax Registration Number is numeric.';
5773 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5774 END IF;
5775 -- Logging Infra
5776
5777 p_return_status := FND_API.G_RET_STS_SUCCESS;
5778 p_error_buffer := NULL;
5779 ELSE
5780
5781 -- Logging Infra: Statement level
5782 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5783 l_log_msg := 'The Tax Registration Number must be numeric.';
5784 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5785 END IF;
5786 -- Logging Infra
5787
5788 p_return_status := FND_API.G_RET_STS_ERROR;
5789 p_error_buffer := 'ZX_REG_NUM_MUST_BE_NUMERIC';
5790
5791 END IF;
5792
5793 ELSE
5794
5795 -- Logging Infra: Statement level
5796 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5797 l_log_msg := 'The length of the Tax Registration Number is not 9 or 12.';
5798 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5799 END IF;
5800 -- Logging Infra
5801
5802 IF length(trn_value) > 9 AND length(trn_value) < 12 THEN
5803 p_return_status := FND_API.G_RET_STS_ERROR;
5804 p_error_buffer := 'ZX_REG_NUM_TOO_BIG';
5805 ELSIF length(trn_value) > 12 THEN
5806 p_return_status := FND_API.G_RET_STS_ERROR;
5807 p_error_buffer := 'ZX_REG_NUM_TOO_BIG';
5808 ELSE
5812
5809 p_return_status := FND_API.G_RET_STS_ERROR;
5810 p_error_buffer := 'ZX_REG_NUM_INVALID';
5811 END IF;
5813 END IF;
5814
5815 END IF;
5816
5817 --ELSE
5818
5819 -- Logging Infra: Statement level
5820 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5821 -- l_log_msg := 'Tax Regegistration Type is not VAT.';
5822 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5823 -- END IF;
5824 -- Logging Infra
5825
5826 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
5827
5828 --END IF;
5829
5830 END IF;
5831
5832 END VALIDATE_TRN_LT;
5833
5834 /* *********** End VALIDATE_TRN_LT ****************** */
5835
5836
5837 procedure VALIDATE_TRN_CY (p_trn_value IN VARCHAR2,
5838 p_trn_type IN VARCHAR2,
5839 p_check_unique_flag IN VARCHAR2,
5840 p_return_status OUT NOCOPY VARCHAR2,
5841 p_error_buffer OUT NOCOPY VARCHAR2)
5842 AS
5843
5844 -- Logging Infra
5845 l_procedure_name CONSTANT VARCHAR2(30) := 'VALIDATE_TRN_CY';
5846 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
5847
5848
5849 trn_value VARCHAR2(50);
5850 cy_prefix VARCHAR2(2);
5851
5852 BEGIN
5853
5854 trn_value := upper(p_trn_value);
5855 cy_prefix := substr(trn_value,1,2);
5856
5857 -- Logging Infra: Setting up runtime level
5858 G_CURRENT_RUNTIME_LEVEL := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5859
5860 -- Logging Infra: Procedure level
5861 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
5862 l_log_msg := l_procedure_name||'(+)';
5863 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||l_procedure_name||'.begin', l_log_msg);
5864 END IF;
5865 -- Logging Infra
5866
5867 -- Logging Infra: Statement level
5868 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5869 l_log_msg := 'Parameters ';
5870 l_log_msg := l_log_msg||'p_trn_value: '||p_trn_value;
5871 l_log_msg := l_log_msg||' p_trn_type: '||p_trn_type;
5872 l_log_msg := l_log_msg||' p_check_unique_flag: '||p_check_unique_flag;
5873 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5874 END IF;
5875 -- Logging Infra
5876
5877 IF p_check_unique_flag = 'E' THEN
5878
5879 -- Logging Infra: Statement level
5880 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5881 l_log_msg := 'The Tax Registration Number is already used.';
5882 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5883 END IF;
5884 -- Logging Infra
5885
5886 p_return_status := FND_API.G_RET_STS_ERROR;
5887 p_error_buffer := 'ZX_REG_NUM_INVALID';
5888
5889 ELSIF p_check_unique_flag = 'S' THEN
5890
5891 --IF p_trn_type = 'VAT' THEN
5892
5893 IF substr(trn_value,1,2) = 'CY' THEN
5894
5895 /* check length = 11 */
5896 IF length(trn_value) = 11 THEN
5897
5898 /* Check first eight digits are numeric */
5899 IF common_check_numeric(trn_value,3,8) = '0' THEN
5900
5901 IF substr(trn_value,11,1) between 'A' and 'Z' THEN
5902
5903 -- Logging Infra: Statement level
5904 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5905 l_log_msg := 'The Tax Registration Number is numeric.';
5906 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5907 END IF;
5908 -- Logging Infra
5909
5910 p_return_status := FND_API.G_RET_STS_SUCCESS;
5911 p_error_buffer := NULL;
5912
5913 ELSE
5914
5915 -- Logging Infra: Statement level
5916 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5917 l_log_msg := 'The last character must be a letter.';
5918 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5919 END IF;
5920 -- Logging Infra
5921
5922 p_return_status := FND_API.G_RET_STS_ERROR;
5923 p_error_buffer := 'ZX_REG_NUM_INVALID';
5924
5925 END IF;
5926 ELSE
5927
5928 -- Logging Infra: Statement level
5929 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5930 l_log_msg := 'The Tax Registration Number must be numeric.';
5931 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5932 END IF;
5933 -- Logging Infra
5934
5935 p_return_status := FND_API.G_RET_STS_ERROR;
5936 p_error_buffer := 'ZX_REG_NUM_MUST_BE_NUMERIC';
5937
5938 END IF;
5939
5940 ELSE
5941
5942 -- Logging Infra: Statement level
5943 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5944 l_log_msg := 'The length of the Tax Registration Number is not 11.';
5945 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5946 END IF;
5947 -- Logging Infra
5948
5949 IF length(trn_value) > 11 THEN
5950 p_return_status := FND_API.G_RET_STS_ERROR;
5951 p_error_buffer := 'ZX_REG_NUM_TOO_BIG';
5952 ELSE
5953 p_return_status := FND_API.G_RET_STS_ERROR;
5954 p_error_buffer := 'ZX_REG_NUM_INVALID';
5955 END IF;
5956 END IF;
5957
5958 ELSE
5959
5960 /* check length = 9 */
5961 IF length(trn_value) = 9 THEN
5962
5963 /* Check first eight digits are numeric */
5964 IF common_check_numeric(trn_value,1,8) = '0' THEN
5965
5966 IF substr(trn_value,9,1) between 'A' and 'Z' THEN
5967
5968 -- Logging Infra: Statement level
5969 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5970 l_log_msg := 'The Tax Registration Number is numeric.';
5971 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5972 END IF;
5973 -- Logging Infra
5974
5975 p_return_status := FND_API.G_RET_STS_SUCCESS;
5976 p_error_buffer := NULL;
5977
5978 ELSE
5979
5980 -- Logging Infra: Statement level
5981 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5982 l_log_msg := 'The last character must be a letter.';
5983 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5984 END IF;
5985 -- Logging Infra
5986
5987 p_return_status := FND_API.G_RET_STS_ERROR;
5988 p_error_buffer := 'ZX_REG_NUM_INVALID';
5989
5990 END IF;
5991 ELSE
5992
5993 -- Logging Infra: Statement level
5994 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
5995 l_log_msg := 'The Tax Registration Number must be numeric.';
5996 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
5997 END IF;
5998 -- Logging Infra
5999
6000 p_return_status := FND_API.G_RET_STS_ERROR;
6001 p_error_buffer := 'ZX_REG_NUM_MUST_BE_NUMERIC';
6002
6003 END IF;
6004
6005 ELSE
6006
6007 -- Logging Infra: Statement level
6008 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
6009 l_log_msg := 'The length of the Tax Registration Number is not 9.';
6010 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
6011 END IF;
6012 -- Logging Infra
6013
6014 IF length(trn_value) > 9 THEN
6015 p_return_status := FND_API.G_RET_STS_ERROR;
6016 p_error_buffer := 'ZX_REG_NUM_TOO_BIG';
6017 ELSE
6018 p_return_status := FND_API.G_RET_STS_ERROR;
6019 p_error_buffer := 'ZX_REG_NUM_INVALID';
6020 END IF;
6021
6022 END IF;
6023
6024 END IF;
6025
6026 --ELSE
6027
6028 -- Logging Infra: Statement level
6029 -- IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
6030 -- l_log_msg := 'Tax Regegistration Type is not VAT.';
6031 -- FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME || l_procedure_name, l_log_msg);
6032 -- END IF;
6033 -- Logging Infra
6034
6035 -- p_return_status := FND_API.G_RET_STS_SUCCESS;
6036
6037 --END IF;
6038
6039 END IF;
6040
6041 END VALIDATE_TRN_CY;
6042
6043 /* *********** End VALIDATE_TRN_CY ****************** */
6044
6045
6046 END ZX_TRN_VALIDATION_PKG;