1 PACKAGE BODY JG_TAXID_VAL_PKG AS
2 /* $Header: jgzzgtib.pls 120.15.12010000.1 2008/07/28 07:57:02 appldev ship $ */
3 -- Check Tax ID is numeric
4
5 FUNCTION check_numeric(p_taxpayer_id IN VARCHAR2
6 ) RETURN VARCHAR2 IS
7 l_length number(3);
8 BEGIN
9
10 l_length:=lengthb(p_taxpayer_id);
11 FOR i IN 1..l_length LOOP
12 IF substrb(p_taxpayer_id,i,1)=' ' THEN
13 RETURN('FALSE');
14 END IF;
15 END LOOP;
16
17 IF (NVL(RTRIM(TRANSLATE(p_taxpayer_id,'1234567890',' ')),'0') <> '0') THEN
18 RETURN('FALSE');
19 ELSE
20 RETURN('TRUE');
21 END IF;
22 END check_numeric;
23
24 -- Check the length for Tax ID
25
26 FUNCTION check_length(p_country_code IN VARCHAR2,
27 p_num_digits IN NUMBER,
28 p_taxpayer_id IN VARCHAR2
29 ) RETURN VARCHAR2 IS
30 l_max_digits NUMBER(3);
31 BEGIN
32 l_max_digits:=lengthb(p_taxpayer_id);
33 IF (p_country_code = 'AR' AND (l_max_digits = p_num_digits)) THEN
34 RETURN('TRUE');
35 ELSIF (p_country_code='CL' AND (l_max_digits <= p_num_digits)) THEN
36 RETURN('TRUE');
37 ELSIF (p_country_code='CO' AND (l_max_digits <= p_num_digits)) THEN
38 RETURN('TRUE');
39 ELSIF (p_country_code='TW' AND (l_max_digits = p_num_digits)) THEN
40 RETURN('TRUE');
41 ELSE
42 RETURN ('FALSE');
43 END IF;
44 END check_length;
45
46 -- Check Duplicate Primary Bank Branch in AP_BANK_BRANCHES
47
48 FUNCTION check_primary_bank_branch(p_bank_branch_id IN NUMBER,
49 p_bank_name IN VARCHAR2,
50 p_bank_branch_name IN VARCHAR2
51 )RETURN VARCHAR2 IS
52
53 l_bank_branch_id number(15);
54 l_bank_name varchar2(60);
55 l_bank_branch_name varchar2(60);
56
57 BEGIN
58 -- Stubbed out this function since ap_bank_branches is obsolete
59 RETURN('TRUE');
60
61 END check_primary_bank_branch;
62
63
64 -- Check Details Bank Branch
65
66 FUNCTION check_detail_bank_branch(p_bank_branch_id IN NUMBER
67 )RETURN VARCHAR2 IS
68
69 l_bank_branch_id number(15);
70
71 BEGIN
72
73 -- Stubbed out this function since ap_bank_branches is obsolete
74 RETURN('TRUE');
75
76 END check_detail_bank_branch;
77
78 -- Check uniqueness for Tax ID
79
80 FUNCTION check_uniqueness (p_country_code IN VARCHAR2,
81 p_taxpayer_id IN VARCHAR2,
82 p_record_id IN NUMBER,
83 p_calling_program IN VARCHAR2,
84 p_orig_system_ref IN VARCHAR2,
85 p_entity_name IN VARCHAR2,
86 p_request_id IN NUMBER
87 ) RETURN VARCHAR2 IS
88
89 CURSOR CHECK_UNIQUE_TAXID_AR IS -- Customers for Argentina, Chile, and Colombia
90 SELECT hzp.JGZZ_FISCAL_CODE
91 FROM hz_parties hzp, hz_cust_accounts hzc
92 WHERE hzp.JGZZ_FISCAL_CODE = p_taxpayer_id
93 AND hzc.party_id = hzp.party_id
94 AND hzc.CUST_ACCOUNT_ID <> nvl(p_record_id,0)
95 AND substrb(nvl(HZC.GLOBAL_ATTRIBUTE_CATEGORY,'XX.XX'),4,2) = p_country_code;
96
97 CURSOR CHECK_UNIQUE_TAXID_AR_TW IS -- Customers for Taiwan
98 SELECT hzp.JGZZ_FISCAL_CODE
99 FROM hz_parties hzp, hz_cust_accounts hzc
100 WHERE hzp.JGZZ_FISCAL_CODE = p_taxpayer_id
101 AND hzc.party_id = hzp.party_id
102 AND hzc.CUST_ACCOUNT_ID <> nvl(p_record_id,0);
103
104 CURSOR CHECK_UNIQUE_TAXID_AP IS -- Suppliers
105
106 -- TIN Project bug6063219
107 /*
108 SELECT AP.NUM_1099 FROM ap_suppliers AP
109 WHERE AP.NUM_1099 = p_taxpayer_id
110 AND AP.VENDOR_ID<>nvl(p_record_id,0)
111 AND substrb(nvl(AP.GLOBAL_ATTRIBUTE_CATEGORY,'XX.XX'),4,2) = p_country_code;
112 */
113 SELECT decode(ap.vendor_type_lookup_code,'Contractor',
114 decode(ap.organization_type_lookup_code,'INDIVIDUAL',AP.INDIVIDUAL_1099,
115 'FOREIGN INDIVIDUAL',AP.INDIVIDUAL_1099,
116 'PARTNERSHIP',AP.INDIVIDUAL_1099,
117 'FOREIGN PARTNERSHIP',AP.INDIVIDUAL_1099,
118 AP.NUM_1099),
119 AP.NUM_1099) NUM_1099
120 FROM PO_VENDORS AP
121 WHERE decode(ap.vendor_type_lookup_code,'Contractor',
122 decode(ap.organization_type_lookup_code,'INDIVIDUAL',AP.INDIVIDUAL_1099,
123 'FOREIGN INDIVIDUAL',AP.INDIVIDUAL_1099,
124 'PARTNERSHIP',AP.INDIVIDUAL_1099,
125 'FOREIGN PARTNERSHIP',AP.INDIVIDUAL_1099,
126 AP.NUM_1099),
127 AP.NUM_1099) = p_taxpayer_id
128 AND AP.VENDOR_ID <> nvl(p_record_id,0)
129 AND substrb(nvl(AP.GLOBAL_ATTRIBUTE_CATEGORY,'XX.XX'),4,2) = p_country_code;
130
131 -- Company Uniqueness of tax payer id is done by XLE.
132 -- Removed cursor for Company on HR Locations
133 -- Removed cursor to check on ap_bank_branches
134
135 CURSOR CHECK_UNIQUE_TAXID_AR1 IS -- CUSTOMERS
136 SELECT hzp.JGZZ_FISCAL_CODE
137 FROM hz_parties hzp, hz_cust_accounts hzc
138 WHERE hzp.JGZZ_FISCAL_CODE = p_taxpayer_id
139 AND hzc.CUST_ACCOUNT_ID <> nvl(p_record_id,0)
140 AND jg_zz_shared_pkg.get_country(fnd_profile.value('ORG_ID'), NULL) = p_country_code
141 -- Modified for Countries w/o GDF
142 UNION
143 SELECT I.JGZZ_FISCAL_CODE -- RA_CUSTOMERS_INTERFACE
144 FROM RA_CUSTOMERS_INTERFACE I
145 WHERE ( I.CUSTOMER_NAME <> p_entity_name
146 OR I.ORIG_SYSTEM_CUSTOMER_REF <> p_orig_system_ref)
147 AND I.JGZZ_FISCAL_CODE = p_taxpayer_id
148 AND jg_zz_shared_pkg.get_country(fnd_profile.value('ORG_ID'), NULL) = p_country_code
149 -- Modified for Countries w/o GDF
150 AND I.REQUEST_ID = p_request_id
151 AND NVL(I.VALIDATED_FLAG,'N') <> 'Y'
152 AND ROWNUM = 1;
153
154 l_taxid VARCHAR2(30);
155
156 BEGIN
157
158 -- Checking for Tax ID used by a different Customer
159
160 IF p_calling_program = 'ARXCUDCI' THEN
161 IF p_country_code = 'TW' THEN
162 OPEN CHECK_UNIQUE_TAXID_AR_TW;
163 FETCH CHECK_UNIQUE_TAXID_AR_TW INTO l_taxid;
164 IF (CHECK_UNIQUE_TAXID_AR_TW%NOTFOUND) THEN
165 RETURN('TRUE');
166 ELSE
167 RETURN('FALSE');
168 END IF;
169 CLOSE CHECK_UNIQUE_TAXID_AR_TW;
170 ELSE
171 OPEN CHECK_UNIQUE_TAXID_AR;
172 FETCH CHECK_UNIQUE_TAXID_AR INTO l_taxid;
173 IF (CHECK_UNIQUE_TAXID_AR%NOTFOUND) THEN
174 RETURN('TRUE');
175 ELSE
176 RETURN('FALSE');
177 END IF;
178 CLOSE CHECK_UNIQUE_TAXID_AR;
179 END IF;
180
181 -- Checking for Tax ID used by a different Supplier
182
183 ELSIF p_calling_program ='APXVDMVD' THEN
184 OPEN CHECK_UNIQUE_TAXID_AP;
185 FETCH CHECK_UNIQUE_TAXID_AP INTO l_taxid;
186
187 IF (CHECK_UNIQUE_TAXID_AP%NOTFOUND) THEN
188 RETURN('TRUE');
189 ELSE
190 RETURN('FALSE');
191 END IF;
192 CLOSE CHECK_UNIQUE_TAXID_AP;
193
194 -- Checking for Tax ID used by a different Company
195 -- Tax payer id for Company is done by XLE
196 -- stubbed out because of ap_bank_branches
197
198 -- Checking for Duplicate Tax ID in RA_CUSTOMERS and RA_CUSTOMERS_INTERFACE Tables
199
200 ELSIF p_calling_program = 'RACUST' THEN
201 OPEN CHECK_UNIQUE_TAXID_AR1;
202 FETCH CHECK_UNIQUE_TAXID_AR1 INTO l_taxid;
203 IF (CHECK_UNIQUE_TAXID_AR1%NOTFOUND) THEN
204 RETURN('TRUE');
205 ELSE
206 RETURN('FALSE');
207 END IF;
208 CLOSE CHECK_UNIQUE_TAXID_AR1;
209 END IF;
210
211 END check_uniqueness;
212
213 FUNCTION check_unique_tax_reg_num (p_country_code IN VARCHAR2,
214 p_tax_reg_num IN VARCHAR2,
215 p_record_id IN NUMBER,
216 p_calling_program IN VARCHAR2,
217 p_orig_system_ref IN VARCHAR2,
218 p_entity_name IN VARCHAR2,
219 p_request_id IN NUMBER
220 ) RETURN VARCHAR2 IS
221 /*
222 CURSOR CHECK_UNIQUE_TAX_REG_NUM_AR IS -- Customers
223 SELECT AR.TAX_REFERENCE
224 FROM RA_CUSTOMERS AR
225 WHERE AR.TAX_REFERENCE = p_tax_reg_num
226 AND AR.CUSTOMER_ID <>nvl(p_record_id,0)
227 AND substrb(nvl(AR.GLOBAL_ATTRIBUTE_CATEGORY,'XX.XX'),4,2) = p_country_code;
228
229 CURSOR CHECK_UNIQUE_TAX_REG_NUM_AP IS -- Suppliers
230 SELECT AP.VAT_REGISTRATION_NUM FROM PO_VENDORS AP
231 WHERE AP.VAT_REGISTRATION_NUM = p_tax_reg_num
232 AND AP.VENDOR_ID<>nvl(p_record_id,0)
233 AND substrb(nvl(AP.GLOBAL_ATTRIBUTE_CATEGORY,'XX.XX'),4,2) = p_country_code;
234
235 CURSOR CHECK_UNIQUE_TAX_REG_NUM_AR1 IS -- RA_CUSTOMERS
236 SELECT AR.TAX_REFERENCE
237 FROM RA_CUSTOMERS AR
238 WHERE AR.TAX_REFERENCE = p_tax_reg_num
239 AND AR.CUSTOMER_ID <> nvl(p_record_id,0)
240 AND jg_zz_shared_pkg.get_country(fnd_profile.value('ORG_ID'), NULL) = p_country_code -- Modified for Countries w/o GDF
241 UNION
242
243 SELECT I.CUST_TAX_REFERENCE -- RA_CUSTOMERS_INTERFACE
244 FROM RA_CUSTOMERS_INTERFACE I
245 WHERE ( I.CUSTOMER_NAME <> p_entity_name
246 OR I.ORIG_SYSTEM_CUSTOMER_REF <> p_orig_system_ref)
247 AND I.CUST_TAX_REFERENCE = p_tax_reg_num
248 AND jg_zz_shared_pkg.get_country(fnd_profile.value('ORG_ID'), NULL) = p_country_code -- Modified for Countries w/o GDF
249 AND I.REQUEST_ID = p_request_id
250 AND NVL(I.VALIDATED_FLAG,'N') <> 'Y'
251 AND ROWNUM = 1;
252
253 l_tax_reg_num VARCHAR2(50);
254 */
255
256 BEGIN
257
258 RETURN('TRUE'); -- Bug 4474699: Stub out
259
260 /*
261 -- Checking for Tax Registration Number used by a different Customer
262
263 IF p_calling_program = 'ARXCUDCI' THEN
264 OPEN CHECK_UNIQUE_TAX_REG_NUM_AR;
265 FETCH CHECK_UNIQUE_TAX_REG_NUM_AR INTO l_tax_reg_num;
266 IF (CHECK_UNIQUE_TAX_REG_NUM_AR%NOTFOUND) THEN
267 RETURN('TRUE');
268 ELSE
269 RETURN('FALSE');
270 END IF;
271 CLOSE CHECK_UNIQUE_TAX_REG_NUM_AR;
272
273 -- Checking for Tax Registration Number used by a different Supplier
274
275 ELSIF p_calling_program ='APXVDMVD' THEN
276 OPEN CHECK_UNIQUE_TAX_REG_NUM_AP;
277 FETCH CHECK_UNIQUE_TAX_REG_NUM_AP INTO l_tax_reg_num;
278
279 IF (CHECK_UNIQUE_TAX_REG_NUM_AP%NOTFOUND) THEN
280 RETURN('TRUE');
281 ELSE
282 RETURN('FALSE');
283 END IF;
284 CLOSE CHECK_UNIQUE_TAX_REG_NUM_AP;
285
286 -- Checking for Duplicate Tax Registration Number in RA_CUSTOMERS
287 -- and RA_CUSTOMERS_INTERFACE Tables
288
289 ELSIF p_calling_program = 'RACUST' THEN
290 OPEN CHECK_UNIQUE_TAX_REG_NUM_AR1;
291 FETCH CHECK_UNIQUE_TAX_REG_NUM_AR1 INTO l_tax_reg_num;
292 IF (CHECK_UNIQUE_TAX_REG_NUM_AR1%NOTFOUND) THEN
293 RETURN('TRUE');
294 ELSE
295 RETURN('FALSE');
296 END IF;
297 CLOSE CHECK_UNIQUE_TAX_REG_NUM_AR1;
298
299 END IF;
300 */
301
302 END;
303
304 -- Check the cross validation
305 -- This procedure depending upon the current module (say for example if
306 -- current module is AP) checks in AR and HR to see if the TAX ID entered
307 -- for the Supplier is used by a Customer or a Company.
308 -- If it is used then the Customer name or the Company name should match
309 -- with the Supplier name and also the Tax ID Type should match.
310 -- Depending upon the different combinations different error codes are
311 -- returned to the calling program.
312 -- The messages codes that send the procedure are:
313 -- ap1 Tax ID is used by a different Bank
314 -- ap2 Tax ID is used by a different Customer
315 -- ap3 Tax ID is used by a different Company
316 -- ap4 Supplier exists as a Customer with different Tax ID or Tax ID Type
317 -- ap5 Supplier exists as a Company with different Tax ID or Tax ID Type
318 -- ap6 Supplier exists as a Bank with different Tax ID or Tax ID Type
319 -- k6 Tax ID is used by a different Supplier
320 -- k7 Tax ID is used by a different Company
321 -- k8 Customer exists as a Supplier with different Tax ID or Tax ID Type
322 -- k9 Customer exists as a Company with different Tax ID or Tax ID Type
323 -- l1 Tax ID is used by a different Bank
324 -- l2 Customer exists as a Bank with different Tax ID or Tax ID Type
325 -- hr1 Tax ID is used by a different Bank
326 -- hr2 Tax ID is used by a different Supplier
327 -- hr3 Tax ID is used by a different Customer
328 -- hr4 Company exists as a Supplier with different Tax ID or Tax ID Type
329 -- hr5 Company exists as a Customer with different Tax ID or Tax ID Type
330 -- hr6 Company exists as a Bank with different Tax ID or Tax ID Type
331 -- bk1 Tax ID is used by a different Company
332 -- bk2 Tax ID is used by a different Customer
333 -- bk3 Tax ID is used by a different Supplier
334 -- bk4 Bank exists as a Customer with different Tax ID or Tax ID Type
335 -- bk5 Bank exists as a Supplier with different Tax ID or Tax ID Type
336 -- bk6 Bank exists as a Company with different Tax ID or Tax ID Type
337
338 PROCEDURE check_cross_module(p_country_code IN VARCHAR2,
339 p_entity_name IN VARCHAR2,
340 p_taxpayer_id IN VARCHAR2,
341 p_origin IN VARCHAR2,
342 p_taxid_type IN VARCHAR2,
343 p_calling_program IN VARCHAR2,
344 p_return_ar OUT NOCOPY VARCHAR2,
345 p_return_ap OUT NOCOPY VARCHAR2,
346 p_return_hr OUT NOCOPY VARCHAR2,
347 p_return_bk OUT NOCOPY VARCHAR2
348 ) IS
349
350 CURSOR CHECK_CROSS_AP IS --Suppliers
351
352 -- TIN Project bug6063219
353 /*
354 SELECT AP.VENDOR_NAME, AP.NUM_1099,AP.GLOBAL_ATTRIBUTE9,
355 AP.GLOBAL_ATTRIBUTE10 FROM AP_SUPPLIERS AP
356 WHERE (AP.VENDOR_NAME=p_entity_name
357 OR AP.NUM_1099= p_taxpayer_id)
358 AND substrb(nvl(AP.GLOBAL_ATTRIBUTE_CATEGORY,'XX.XX'),4,2) = p_country_code;
359 */
360 SELECT AP.VENDOR_NAME,
361 decode(ap.vendor_type_lookup_code,'Contractor',
362 decode(ap.organization_type_lookup_code,'INDIVIDUAL',AP.INDIVIDUAL_1099,
363 'FOREIGN INDIVIDUAL',AP.INDIVIDUAL_1099,
364 'PARTNERSHIP',AP.INDIVIDUAL_1099,
365 'FOREIGN PARTNERSHIP',AP.INDIVIDUAL_1099,
366 AP.NUM_1099),
367 AP.NUM_1099) NUM_1099,
368 AP.GLOBAL_ATTRIBUTE9,
369 AP.GLOBAL_ATTRIBUTE10 FROM PO_VENDORS AP
370 WHERE (AP.VENDOR_NAME=p_entity_name
371 OR decode(ap.vendor_type_lookup_code,'Contractor',
372 decode(ap.organization_type_lookup_code,'INDIVIDUAL',AP.INDIVIDUAL_1099,
373 'FOREIGN INDIVIDUAL',AP.INDIVIDUAL_1099,
374 'PARTNERSHIP',AP.INDIVIDUAL_1099,
375 'FOREIGN PARTNERSHIP',AP.INDIVIDUAL_1099,
376 AP.NUM_1099),
377 AP.NUM_1099) = p_taxpayer_id)
378 AND substrb(nvl(AP.GLOBAL_ATTRIBUTE_CATEGORY,'XX.XX'),4,2) = p_country_code;
379
380 CURSOR CHECK_CROSS_AR IS --Customers
381 SELECT HZP.PARTY_NAME, HZP.JGZZ_FISCAL_CODE,
382 HZC.GLOBAL_ATTRIBUTE9, HZC.GLOBAL_ATTRIBUTE10
383 FROM HZ_PARTIES HZP, HZ_CUST_ACCOUNTS HZC
384 WHERE (HZP.PARTY_NAME=p_entity_name
385 OR HZP.JGZZ_FISCAL_CODE=p_taxpayer_id)
386 AND substrb(nvl(HZC.GLOBAL_ATTRIBUTE_CATEGORY,'XX.XX'),4,2)=p_country_code
387 AND HZP.PARTY_ID = HZC.PARTY_ID;
388
389 CURSOR CHECK_CROSS_HR IS --Companies
390 SELECT LEGAL_ENTITY_NAME, REGISTRATION_NUMBER
391 FROM XLE_REGISTRATIONS_V -- Coud use this view XLE_LE_FROM_REGISTRATIONS_V
392 WHERE (LEGAL_ENTITY_NAME = p_entity_name
393 OR REGISTRATION_NUMBER = p_taxpayer_id)
394 AND Legislative_Category = 'INCOME_TAX'
395 AND COUNTRY = p_country_code;
396
397 /*
398 Remove cursor for AP_BANK_BRANCHES because it is obsolete
399 */
400
401
402 l_taxid VARCHAR2(30);
403 l_origin VARCHAR2(150);
404 l_taxid_type VARCHAR2(150);
405 l_entity_name VARCHAR2(240); -- utf8 changes bug # 2598519
406
407 BEGIN
408
409 -- Initialize p_return_bk
410
411 p_return_bk:='NA';
412
413 -- Checking the cross module for Suppliers
414 IF p_calling_program='APXVDMVD' THEN
415
416 -- Checking cross module Suppliers/Customers
417
418 OPEN CHECK_CROSS_AR;
419 FETCH CHECK_CROSS_AR INTO l_entity_name, l_taxid, l_origin, l_taxid_type;
420
421 IF CHECK_CROSS_AR%NOTFOUND THEN
422 p_return_ar:='SUCCESS';
423
424 ELSIF (l_taxid IS NULL AND l_entity_name = p_entity_name) THEN
425 p_return_ar:='SUCCESS';
426
427 ELSIF (l_taxid IS NOT NULL) THEN
428
429 IF (p_country_code = 'AR' AND l_entity_name = p_entity_name
430 AND l_taxid = p_taxpayer_id AND l_taxid_type=p_taxid_type AND l_origin = p_origin )
431 OR (p_country_code in ('CL','CO') AND l_entity_name = p_entity_name
432 AND l_taxid=p_taxpayer_id AND l_taxid_type = p_taxid_type) THEN
433
434 p_return_ar:='SUCCESS';
435
436 -- Check if Tax ID is used by a different Customer
437
438 ELSIF (l_entity_name<>p_entity_name AND l_taxid=p_taxpayer_id) THEN
439
440 p_return_ar:='ap2';
441
442 -- Check if Supplier exists as Customer with different TAX ID or Tax ID
443 -- Type
444
445 ELSIF (p_country_code = 'AR' AND l_entity_name=p_entity_name
446 AND (l_taxid<>p_taxpayer_id or l_taxid_type <>p_taxid_type or l_origin <> p_origin))
447 OR (p_country_code in ('CL','CO') AND l_entity_name=p_entity_name
448 AND (l_taxid<>p_taxpayer_id or l_taxid_type <>p_taxid_type ))THEN
449
450 p_return_ar:='ap4';
451
452 END IF;
453
454 END IF;
455
456 CLOSE CHECK_CROSS_AR;
457
458 -- Checking cross module Suppliers/Companies
459
460 IF p_country_code='AR' THEN
461 l_taxid_type:='80';
462 l_origin:='DOMESTIC_ORIGIN';
463 ELSIF p_country_code ='CL' THEN
464 l_taxid_type:='DOMESTIC_ORIGIN';
465 ELSIF p_country_code='CO' THEN
466 l_taxid_type:='LEGAL_ENTITY';
467 END IF;
468
469 OPEN CHECK_CROSS_HR;
470 FETCH CHECK_CROSS_HR INTO l_entity_name, l_taxid;
471
472 IF CHECK_CROSS_HR%NOTFOUND THEN
473 p_return_hr:='SUCCESS';
474
475 ELSIF (l_taxid IS NULL AND l_entity_name=p_entity_name) THEN
476 p_return_ar:='SUCCESS';
477
478 ELSIF (l_taxid IS NOT NULL) THEN
479
480 IF (p_country_code = 'AR' AND l_entity_name=p_entity_name
481 AND l_taxid=p_taxpayer_id AND l_taxid_type=p_taxid_type AND l_origin = p_origin )
482 OR (p_country_code in ('CL','CO') AND l_entity_name=p_entity_name
483 AND l_taxid=p_taxpayer_id AND l_taxid_type=p_taxid_type) THEN
484
485 p_return_hr:='SUCCESS';
486
487 -- Check if Tax ID is used by a different Company
488 ELSIF (l_entity_name<>p_entity_name AND l_taxid=p_taxpayer_id) THEN
489
490 p_return_hr:='ap3';
491
492 -- Check if Supplier exists as Company with different Tax ID or Tax ID
493 -- Type
494
495 ELSIF (p_country_code = 'AR' AND l_entity_name=p_entity_name
496 AND (l_taxid<>p_taxpayer_id or l_taxid_type <>p_taxid_type or l_origin <> p_origin))
497 OR (p_country_code in ('CL','CO') AND l_entity_name=p_entity_name
498 AND (l_taxid<>p_taxpayer_id or l_taxid_type <>p_taxid_type ))THEN
499
500 p_return_hr:='ap5';
501
502 END IF;
503
504 END IF;
505
506 CLOSE CHECK_CROSS_HR;
507
508 -- Commented out because of AP_BANK_BRANCHES
509
510 /*
511 Removed the logic for AP_BANK_BRANCHES since table is obsolete
512 */
513
514 -- Since the current module is AP, there is no validation for AP.
515 -- So returning the OUT variable as 'NA'(Not Applicable).
516 p_return_ap:='NA';
517
518 --Checking the cross module for Customers
519
520 ELSIF (p_calling_program IN ('RACUST','ARXCUDCI')) THEN
521
522 -- Checking cross module Customers/Suppliers
523 OPEN CHECK_CROSS_AP;
524 FETCH CHECK_CROSS_AP INTO l_entity_name, l_taxid, l_origin, l_taxid_type;
525
526 IF CHECK_CROSS_AP%NOTFOUND THEN
527 p_return_ap:='SUCCESS';
528
529 ELSIF (l_taxid IS NULL AND l_entity_name = p_entity_name) THEN
530 p_return_ap:='SUCCESS';
531
532 ELSIF (l_taxid IS NOT NULL) THEN
533
534 IF (p_country_code = 'AR' AND l_entity_name=p_entity_name
535 AND l_taxid=p_taxpayer_id AND l_taxid_type=p_taxid_type AND l_origin = p_origin )
536 OR (p_country_code in ('CL','CO') AND l_entity_name=p_entity_name
537 AND l_taxid=p_taxpayer_id AND l_taxid_type=p_taxid_type) THEN
538
539 p_return_ap:='SUCCESS';
540
541 -- Check if Tax ID is used by a different Supplier
542
543 ELSIF (l_entity_name<>p_entity_name AND l_taxid=p_taxpayer_id) THEN
544
545 p_return_ap:='k6';
546
547 -- Check if Customer exists as Supplier with different Tax ID or Tax ID
548 -- Type
549
550 ELSIF (p_country_code = 'AR' AND l_entity_name=p_entity_name
551 AND (l_taxid<>p_taxpayer_id or l_taxid_type <>p_taxid_type or l_origin <> p_origin))
552 OR (p_country_code in ('CL','CO') AND l_entity_name=p_entity_name
553 AND (l_taxid<>p_taxpayer_id or l_taxid_type <>p_taxid_type ))THEN
554
555 p_return_ap:='k8';
556
557 END IF;
558
559 END IF;
560
561 CLOSE CHECK_CROSS_AP;
562
563 -- Checking cross module Customers/Companies
564
565 IF p_country_code='AR' THEN
566 l_taxid_type:='80';
567 l_origin :='DOMESTIC_ORIGIN';
568 ELSIF p_country_code ='CL' THEN
569 l_taxid_type:='DOMESTIC_ORIGIN';
570 ELSIF p_country_code='CO' THEN
571 l_taxid_type:='LEGAL_ENTITY';
572 END IF;
573
574 OPEN CHECK_CROSS_HR;
575 FETCH CHECK_CROSS_HR INTO l_entity_name, l_taxid;
576
577 IF CHECK_CROSS_HR%NOTFOUND THEN
578 p_return_hr:='SUCCESS';
579
580 ELSIF (l_taxid IS NULL AND l_entity_name=p_entity_name) THEN
581
582 p_return_hr:='SUCCESS';
583
584 ELSIF (l_taxid IS NOT NULL) THEN
585
586 IF (p_country_code = 'AR' AND l_entity_name=p_entity_name
587 AND l_taxid=p_taxpayer_id AND l_taxid_type=p_taxid_type AND l_origin = p_origin )
588 OR (p_country_code in ('CL','CO') AND l_entity_name=p_entity_name
589 AND l_taxid=p_taxpayer_id AND l_taxid_type=p_taxid_type) THEN
590
591 p_return_hr:='SUCCESS';
592
593 -- Check if Tax ID is used by a different Company
594
595 ELSIF (l_entity_name<>p_entity_name AND l_taxid=p_taxpayer_id) THEN
596
597 p_return_hr:='k7';
598
599 -- Check if Customer exists as Company with different Tax ID or Tax ID
600 -- Type
601
602 ELSIF (p_country_code = 'AR' AND l_entity_name=p_entity_name
603 AND (l_taxid<>p_taxpayer_id or l_taxid_type <>p_taxid_type or l_origin <> p_origin))
604 OR (p_country_code in ('CL','CO') AND l_entity_name=p_entity_name
605 AND (l_taxid<>p_taxpayer_id or l_taxid_type <>p_taxid_type ))THEN
606
607 p_return_hr:='k9';
608
609 END IF;
610
611 END IF;
612
613 CLOSE CHECK_CROSS_HR;
614
615 -- Remove code on ap_bank_branches
616 -- Since the current module is AR, there is no validation for AR.
617 -- So returning the OUT variable as 'NA'(Not Applicable).
618 p_return_ar:='NA';
619
620 --Checking the cross module for Companies
621
622 ELSIF (p_calling_program ='PERWSLOC') THEN
623
624 p_return_hr:='NA';
625
626 -- Checking the cross module for Banks
627 -- This is applicable for Colombia only
628
629 ELSIF p_calling_program='APXSUMBA' THEN
630
631 p_return_bk:='NA';
632
633 ELSE
634
635 -- Since the current module is not in Colombia, there is no validation for BK.
636 -- So returning the OUT variable as 'NA'(Not Applicable).
637 p_return_bk:='NA';
638 END IF;
639
640 END check_cross_module;
641
642
643 -- Taxpayer ID Validation
644
645 FUNCTION check_algorithm(p_taxpayer_id IN VARCHAR2,
646 p_country IN VARCHAR2,
647 p_global_attribute12 IN VARCHAR2
648 ) RETURN VARCHAR2 IS
649 l_var1 VARCHAR2(20);
650 l_val_digit VARCHAR2(2);
651 l_mod_value NUMBER(2);
652 BEGIN
653
654 -- Check the Taxpayer ID Validation digit for Chile
655
656 IF p_country='CL' THEN
657
658
659 l_var1:=LPAD(p_taxpayer_id,12,'0');
660 l_val_digit:=(11-MOD(((TO_NUMBER(SUBSTR(l_var1,12,1))) *2 +
661 (TO_NUMBER(SUBSTR(l_var1,11,1))) *3 +
662 (TO_NUMBER(SUBSTR(l_var1,10,1))) *4 +
663 (TO_NUMBER(SUBSTR(l_var1,9,1))) *5 +
664 (TO_NUMBER(SUBSTR(l_var1,8,1))) *6 +
665 (TO_NUMBER(SUBSTR(l_var1,7,1))) *7 +
666 (TO_NUMBER(SUBSTR(l_var1,6,1))) *2 +
667 (TO_NUMBER(SUBSTR(l_var1,5,1))) *3 +
668 (TO_NUMBER(SUBSTR(l_var1,4,1))) *4 +
669 (TO_NUMBER(SUBSTR(l_var1,3,1))) *5 +
670 (TO_NUMBER(SUBSTR(l_var1,2,1))) *6 +
671 (TO_NUMBER(SUBSTR(l_var1,1,1))) *7),11));
672 IF l_val_digit='10'THEN
673 l_val_digit:='K';
674 ELSIF l_val_digit = '11' THEN
675 l_val_digit:='0';
676 END IF;
677
678 IF l_val_digit<> p_global_attribute12 THEN
679 RETURN('FALSE');
680 ELSE
681 RETURN('TRUE');
682 END IF;
683
684 -- Check the Taxpayer ID Valdiation digit for Colombia
685
686 ELSIF p_country='CO' THEN
687
688 l_var1:=LPAD(p_taxpayer_id,15,'0');
689 l_mod_value:=(MOD(((TO_NUMBER(SUBSTR(l_var1,15,1))) *3 +
690 (TO_NUMBER(SUBSTR(l_var1,14,1))) *7 +
691 (TO_NUMBER(SUBSTR(l_var1,13,1))) *13 +
692 (TO_NUMBER(SUBSTR(l_var1,12,1))) *17 +
693 (TO_NUMBER(SUBSTR(l_var1,11,1))) *19 +
694 (TO_NUMBER(SUBSTR(l_var1,10,1))) *23 +
695 (TO_NUMBER(SUBSTR(l_var1,9,1))) *29 +
696 (TO_NUMBER(SUBSTR(l_var1,8,1))) *37 +
697 (TO_NUMBER(SUBSTR(l_var1,7,1))) *41 +
698 (TO_NUMBER(SUBSTR(l_var1,6,1))) *43 +
699 (TO_NUMBER(SUBSTR(l_var1,5,1))) *47 +
700 (TO_NUMBER(SUBSTR(l_var1,4,1))) *53 +
701 (TO_NUMBER(SUBSTR(l_var1,3,1))) *59 +
702 (TO_NUMBER(SUBSTR(l_var1,2,1))) *67 +
703 (TO_NUMBER(SUBSTR(l_var1,1,1))) *71),11));
704
705 IF (l_mod_value IN (1,0)) THEN
706 l_val_digit:=l_mod_value;
707 ELSE
708 l_val_digit:=11-l_mod_value;
709 END IF;
710
711 IF l_val_digit<> p_global_attribute12 THEN
712 RETURN('FALSE');
713 ELSE
714 RETURN('TRUE');
715 END IF;
716
717 -- Check the Taxpayer ID Validation digit for Argentina
718
719 ELSIF p_country='AR' THEN
720
721 l_val_digit:=(11-MOD(((TO_NUMBER(SUBSTR(p_taxpayer_id,10,1))) *2 +
722 (TO_NUMBER(SUBSTR(p_taxpayer_id,9,1))) *3 +
723 (TO_NUMBER(SUBSTR(p_taxpayer_id,8,1))) *4 +
724 (TO_NUMBER(SUBSTR(p_taxpayer_id,7,1))) *5 +
725 (TO_NUMBER(SUBSTR(p_taxpayer_id,6,1))) *6 +
726 (TO_NUMBER(SUBSTR(p_taxpayer_id,5,1))) *7 +
727 (TO_NUMBER(SUBSTR(p_taxpayer_id,4,1))) *2 +
728 (TO_NUMBER(SUBSTR(p_taxpayer_id,3,1))) *3 +
729 (TO_NUMBER(SUBSTR(p_taxpayer_id,2,1))) *4 +
730 (TO_NUMBER(SUBSTR(p_taxpayer_id,1,1))) *5),11));
731
732 IF l_val_digit ='10' THEN
733 l_val_digit:='9';
734 ELSIF l_val_digit='11' THEN
735 l_val_digit:='0';
736 END IF;
737
738 IF l_val_digit<> p_global_attribute12 THEN
739 RETURN('FALSE');
740 ELSE
741 RETURN('TRUE');
742 END IF;
743
744 END IF;
745
746 END check_algorithm;
747
748 END JG_TAXID_VAL_PKG;
[Home] [Help]