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