[Home] [Help]
PACKAGE BODY: APPS.JE_IT_XML_LISTING_PKG
Source
1 PACKAGE BODY JE_IT_XML_LISTING_PKG AS
2 /*$Header: jeitxlstb.pls 120.2 2008/01/02 14:03:50 spasupun noship $*/
3 gv_row_val VARCHAR2(1680);
4 gn_row_inserted NUMBER := 0;
5 g_vat_reg_error VARCHAR2(100);
6
7 --------------------------------------------------------------------------------
8 --Private Methods Declaration
9 --------------------------------------------------------------------------------
10
11 procedure VALIDATE_NIF_IT(NIF in varchar2,
12 Xi_UNIQUE_FLAG in varchar2,
13 RET_VAR OUT NOCOPY varchar2,
14 RET_MESSAGE OUT NOCOPY varchar2);
15
16 FUNCTION generate_dynamic_string RETURN BOOLEAN;
17
18 FUNCTION beforeReport RETURN BOOLEAN
19 AS
20 BEGIN
21 IF P_PROG_NUM IS NULL THEN
22 RETURN TRUE;
23 ELSE
24 RETURN generate_dynamic_string;
25 END IF;
26 RETURN TRUE;
27 END beforeReport;
28
29
30 FUNCTION validate_vat_reg_num (p_vat_reg_num VARCHAR2, p_party_type_code VARCHAR2) RETURN VARCHAR2
31 IS
32 CURSOR tax_reg_num_csr(c_registration_number varchar2, c_party_type_code varchar2) IS
33 SELECT ptp.party_id
34 FROM zx_registrations reg,
35 zx_party_tax_profile ptp
36 WHERE ptp.party_tax_profile_id = reg.party_tax_profile_id
37 AND reg.registration_number = c_registration_number
38 AND sysdate >= reg.effective_from
39 AND (sysdate <= reg.effective_to OR reg.effective_to IS NULL)
40 AND ptp.party_type_code = c_party_type_code; --'THIRD_PARTY'
41
42 l_count number :=0;
43 error_status VARCHAR2(10);
44 eror_buffer VARCHAR2(100);
45 l_party_id VARCHAR2(100);
46
47 BEGIN
48 l_count := 0;
49 eror_buffer := NULL;
50 error_status := NULL;
51 IF p_vat_reg_num IS NOT NULL THEN
52
53 OPEN tax_reg_num_csr(p_vat_reg_num, p_party_type_code);
54 LOOP
55
56 EXIT WHEN tax_reg_num_csr%NOTFOUND;
57 FETCH tax_reg_num_csr into l_party_id ;
58
59 IF tax_reg_num_csr%FOUND THEN
60 l_count := l_count+ 1;
61 END IF;
62 END LOOP;
63
64 IF l_count > 1 THEN
65
66 FND_MESSAGE.SET_NAME('JE','JE_IT_VAT_DUPLICATE');
67 g_vat_reg_error := FND_MESSAGE.get;
68 RETURN g_vat_reg_error;
69
70 END IF;
71
72 IF l_count < 2 THEN
73
74 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_IT( p_trn_value => p_vat_reg_num
75 ,p_trn_type => NULL
76 ,p_check_unique_flag => 'S'
77 ,p_return_status => error_status
78 ,p_error_buffer => eror_buffer);
79
80 IF error_status = 'E' THEN
81 FND_MESSAGE.SET_NAME('JE','JE_IT_VAT_INVALID');
82 g_vat_reg_error := FND_MESSAGE.get;
83 RETURN g_vat_reg_error;
84 END IF;
85 END IF;
86
87 RETURN 'TRUE';
88
89 END IF;
90 RETURN 'TRUE';
91
92 EXCEPTION
93 WHEN others THEN
94 fnd_file.put_line(fnd_file.log,'Error Message:'||SQLERRM||'Error Code:'||SQLCODE);
95 FND_MESSAGE.SET_NAME('JE','JE_IT_VAT_INVALID');
96 g_vat_reg_error := FND_MESSAGE.get;
97 RETURN g_vat_reg_error;
98
99 END validate_vat_reg_num;
100
101 PROCEDURE insert_into_table (col_data VARCHAR2, appl_id NUMBER)
102 AS
103 BEGIN
104 INSERT INTO je_it_list_trx_gt
105 (je_info_n1,
106 je_info_v1)
107 VALUES
108 (appl_id,
109 col_data);
110 gn_row_inserted := gn_row_inserted + 1;
111 fnd_file.put_line(fnd_file.log,'Rows Inserted:'||gn_row_inserted);
112 EXCEPTION
113 WHEN others THEN
114 fnd_file.put_line(fnd_file.log,'Error Message:'||SQLERRM||'Error Code:'||SQLCODE);
115 END insert_into_table;
116
117 PROCEDURE concatenate(pv_code VARCHAR2, pv_field VARCHAR2, pn_appl_id NUMBER)
118 AS
119 BEGIN
120 IF(pv_field IS NOT NULL AND (TRIM(pv_field) <> '0')) THEN
121 IF (LENGTH(gv_row_val) <1680) THEN
122 gv_row_val := gv_row_val||pv_code||pv_field;
123 ELSE
124 -- Intitialize a new row....
125 IF (gv_row_val IS NOT NULL) THEN
126 insert_into_table(gv_row_val,pn_appl_id);
127 gv_row_val := NULL;
128 END IF;
129 gv_row_val := pv_code||pv_field;
130 END IF;
131 END IF;
132 END concatenate;
133
134 FUNCTION generate_dynamic_string RETURN BOOLEAN
135 AS
136 CURSOR cur_q_customer
137 IS
138 SELECT je.party_id CUST_PARTY_ID
139 ,je.transmission_num TRANSMISSION_NUM
140 ,hzp.party_name PARTY_NAME
141 ,hzp.party_type PARTY_TYPE
142 ,je.fiscal_id_num TAX_PAYERID
143 ,je.vat_registration_num VAT_REGISTRATION_NUM
144 ,ROUND(je.taxable_amt) TAXABLE_AMT
145 ,ROUND(je.vat_amt) VAT_AMT
146 ,ROUND(je.non_taxable_amt) NON_TAXABLE_AMT
147 ,ROUND(je.exempt_amt) EXEMPT_AMT
148 ,ROUND(je.taxable_vat_inv_amt) TAXABLE_VAT_INV_AMT
149 ,ROUND(je.cm_taxable_amt) TAXABLE_AMT_CM
150 ,ROUND(je.cm_vat_amt) VAT_AMT_CM
151 ,ROUND(je.cm_non_taxable_amt) NON_TAXABLE_AMT_CM
152 ,ROUND(je.cm_exempt_amt) EXEMPT_AMT_CM
153 ,ROUND(je.cm_taxable_vat_inv_amt) TAXABLE_VAT_INV_AMT_CM
154 FROM je_it_list_parties_all je
155 ,hz_cust_accounts hzca
156 ,hz_parties hzp
157 WHERE je.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
158 AND je.year_of_declaration = P_YEAR_OF_DECLARATION
159 AND je.application_id = 222
160 AND je.transmission_num = P_PROG_NUM
161 AND je.party_id = hzca.cust_account_id
162 AND hzca.party_id = hzp.party_id
163 ORDER BY je.party_sequence_num;
164
165 CURSOR cur_q_supplier
166 IS
167 SELECT je.party_id SUP_PARTY_ID
168 ,je.transmission_num TRANSMISSION_NUM
169 ,pv.vendor_name PARTY_NAME
170 ,pv.vendor_type_lookup_code PARTY_TYPE
171 ,je.fiscal_id_num TAX_PAYERID
172 ,je.vat_registration_num VAT_REGISTRATION_NUM
173 ,ROUND(je.taxable_amt) TAXABLE_AMT
174 ,ROUND(je.vat_amt) VAT_AMT
175 ,ROUND(je.non_taxable_amt) NON_TAXABLE_AMT
176 ,ROUND(je.exempt_amt) EXEMPT_AMT
177 ,ROUND(je.taxable_vat_amt) TAXABLE_VAT_AMT
178 ,ROUND(je.taxable_vat_inv_amt) TAXABLE_VAT_INV_AMT
179 ,ROUND(je.cm_taxable_amt) TAXABLE_AMT_CM
180 ,ROUND(je.cm_vat_amt) VAT_AMT_CM
181 ,ROUND(je.cm_non_taxable_amt) NON_TAXABLE_AMT_CM
182 ,ROUND(je.cm_exempt_amt) EXEMPT_AMT_CM
183 ,ROUND(je.cm_taxable_vat_amt) TAXABLE_VAT_AMT_CM
184 ,ROUND(je.cm_taxable_vat_inv_amt) TAXABLE_VAT_INV_AMT_CM
185 FROM je_it_list_parties_all je
186 ,ap_suppliers pv
187 WHERE je.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
188 AND je.year_of_declaration = P_YEAR_OF_DECLARATION
189 AND je.transmission_num = P_PROG_NUM
190 AND je.application_id = 200
191 AND je.party_id = pv.vendor_id
192 ORDER BY je.party_sequence_num;
193
194 string_build VARCHAR2(1680);
195 counter NUMBER := 1;
196 code VARCHAR2(8);
197 field VARCHAR2(16);
198
199 BEGIN
200 FOR C_CUST_REC IN cur_q_customer
201 LOOP
202 field := counter;
203 counter := counter + 1;
204 field := LPAD(field,16,' ');
205 code := 'CL001001';
206 concatenate(code,field,222);
207
208 field := C_CUST_REC.tax_payerid;
209 field := RPAD(SUBSTRB(field,1,16),16,' ');
210 code := 'CL002001';
211 concatenate(code,field,222);
212
213 field := C_CUST_REC.VAT_REGISTRATION_NUM;
214 field := RPAD(SUBSTRB(field,1,16),16,' ');
215 code := 'CL003001';
216 concatenate(code,field,222);
217
218 field := C_CUST_REC.TAXABLE_AMT;
219 field := LPAD(SUBSTRB(field,1,16),16,' ');
220 code := 'CL004001';
221 concatenate(code,field,222);
222
223 field := C_CUST_REC.VAT_AMT;
224 field := LPAD(SUBSTRB(field,1,16),16,' ');
225 code := 'CL004002';
226 concatenate(code,field,222);
227
228 field := C_CUST_REC.NON_TAXABLE_AMT;
229 field := LPAD(SUBSTRB(field,1,16),16,' ');
230 code := 'CL005001';
231 concatenate(code,field,222);
232
233 field := C_CUST_REC.EXEMPT_AMT;
234 field := LPAD(SUBSTRB(field,1,16),16,' ');
235 code := 'CL006001';
236 concatenate(code,field,222);
237
238 field := C_CUST_REC.TAXABLE_VAT_INV_AMT;
239 field := LPAD(SUBSTRB(field,1,16),16,' ');
240 code := 'CL007001';
241 concatenate(code,field,222);
242
243 --Credit memo amounts
244
245 field := C_CUST_REC.TAXABLE_AMT_CM;
246 field := LPAD(SUBSTRB(field,1,16),16,' ');
247 code := 'CL008001';
248 concatenate(code,field,222);
249
250 field := C_CUST_REC.VAT_AMT_CM;
251 field := LPAD(SUBSTRB(field,1,16),16,' ');
252 code := 'CL008002';
253 concatenate(code,field,222);
254
255 field := C_CUST_REC.NON_TAXABLE_AMT_CM;
256 field := LPAD(SUBSTRB(field,1,16),16,' ');
257 code := 'CL009001';
258 concatenate(code,field,222);
259
260 field := C_CUST_REC.EXEMPT_AMT_CM;
261 field := LPAD(SUBSTRB(field,1,16),16,' ');
262 code := 'CL010001';
263 concatenate(code,field,222);
264
265 field := C_CUST_REC.TAXABLE_VAT_INV_AMT_CM;
266 field := LPAD(SUBSTRB(field,1,16),16,' ');
267 code := 'CL011001';
268 concatenate(code,field,222);
269 END LOOP;
270
271 IF (gv_row_val IS NOT NULL) THEN
272 insert_into_table(gv_row_val,222);
273 gv_row_val := NULL;
274 END IF;
275
276 counter := 1;
277
278 FOR C_SUP_REC IN cur_q_supplier
279 LOOP
280 field := counter;
281 counter := counter + 1;
282 code := 'FR001001';
283 field := LPAD(field,16,' ');
284 concatenate(code,field,200);
285
286 field := C_SUP_REC.tax_payerid;
287 code := 'FR002001';
288 field := RPAD(SUBSTRB(field,1,16),16,' ');
289 concatenate(code,field,200);
290
291 field := C_SUP_REC.VAT_REGISTRATION_NUM;
292 code := 'FR003001';
293 field := RPAD(SUBSTRB(field,1,16),16,' ');
294 concatenate(code,field,200);
295
296 field := C_SUP_REC.TAXABLE_AMT;
297 code := 'FR004001';
298 field := LPAD(SUBSTRB(field,1,16),16,' ');
299 concatenate(code,field,200);
300
304 concatenate(code,field,200);
301 field := C_SUP_REC.VAT_AMT;
302 code := 'FR004002';
303 field := LPAD(SUBSTRB(field,1,16),16,' ');
305
306 field := C_SUP_REC.TAXABLE_VAT_AMT;
307 code := 'FR005001';
308 field := LPAD(SUBSTRB(field,1,16),16,' ');
309 concatenate(code,field,200);
310
311 field := C_SUP_REC.NON_TAXABLE_AMT;
312 code := 'FR006001';
313 field := LPAD(SUBSTRB(field,1,16),16,' ');
314 concatenate(code,field,200);
315
316 field := C_SUP_REC.EXEMPT_AMT;
317 code := 'FR007001';
318 field := LPAD(SUBSTRB(field,1,16),16,' ');
319 concatenate(code,field,200);
320
321 field := C_SUP_REC.TAXABLE_VAT_INV_AMT;
322 code := 'FR008001';
323 field := LPAD(SUBSTRB(field,1,16),16,' ');
324 concatenate(code,field,200);
325
326 -- Credit Memo Lines
327
328 field := C_SUP_REC.TAXABLE_AMT_CM;
329 code := 'FR009001';
330 field := LPAD(SUBSTRB(field,1,16),16,' ');
331 concatenate(code,field,200);
332
333 field := C_SUP_REC.VAT_AMT_CM;
334 code := 'FR009002';
335 field := LPAD(SUBSTRB(field,1,16),16,' ');
336 concatenate(code,field,200);
337
338 field := C_SUP_REC.TAXABLE_VAT_AMT_CM;
339 code := 'FR010001';
340 field := LPAD(SUBSTRB(field,1,16),16,' ');
341 concatenate(code,field,200);
342
343 field := C_SUP_REC.NON_TAXABLE_AMT_CM;
344 code := 'FR011001';
345 field := LPAD(SUBSTRB(field,1,16),16,' ');
346 concatenate(code,field,200);
347
348 field := C_SUP_REC.EXEMPT_AMT_CM;
349 code := 'FR012001';
350 field := LPAD(SUBSTRB(field,1,16),16,' ');
351 concatenate(code,field,200);
352
353 field := C_SUP_REC.TAXABLE_VAT_INV_AMT_CM;
354 code := 'FR013001';
355 field := LPAD(SUBSTRB(field,1,16),16,' ');
356 concatenate(code,field,200);
357
358 END LOOP;
359 IF (gv_row_val IS NOT NULL) THEN
360 insert_into_table(gv_row_val,200);
361 END IF;
362 RETURN TRUE;
363 END generate_dynamic_string;
364
365
366 procedure VALIDATE_NIF_IT (NIF in varchar2,
367 Xi_UNIQUE_FLAG in varchar2,
368 RET_VAR OUT NOCOPY varchar2,
369 RET_MESSAGE OUT NOCOPY varchar2)
370 AS
371 nif_value varchar2(20);
372 check_digit varchar2(1);
373 position_i number(2);
374 position_weight number(2);
375 total_weighting number(3);
376 char_value varchar2(1);
377 calc_check number(2);
378 calc_cd varchar2(1);
379 vat_ret_code varchar2(1);
380 vat_ret_message varchar2(60);
381
382 /**************************/
383 /* SUB-PROCEDURES SECTION */
384 /**************************/
385
386 procedure fail_uniqueness is
387 begin
388 RET_VAR := 'F';
389 RET_MESSAGE := 'NIF_DUPLICATE_NIF_NUM';
390 end fail_uniqueness;
391
392 procedure fail_check is
393 begin
394 RET_VAR := 'F';
395 RET_MESSAGE := 'NIF_INVALID_NIF_NUM';
396 end fail_check;
397
398 procedure system_failure is
399 begin
400 RET_VAR := 'F';
401 RET_MESSAGE := 'NIF_INVALID_NIF_NUM';
402 end system_failure;
403
404 procedure pass_check is
405 begin
406 RET_VAR := 'P';
407 RET_MESSAGE := '';
408 end pass_check;
409
410 /**** weighting assignment functions ****/
411
412 /** function returns the calculated check digit **/
413 function find_check_digit(REMAINDER NUMBER) RETURN VARCHAR2 IS
414 cd_result varchar2(1);
415 begin
416 IF REMAINDER = 0
417 then
418 cd_result := 'A';
419 ELSIF REMAINDER = 1
420 then
421 cd_result := 'B';
422 ELSIF REMAINDER = 2
423 then
424 cd_result := 'C';
425 ELSIF REMAINDER = 3
426 then
427 cd_result := 'D';
428 ELSIF REMAINDER = 4
429 then
430 cd_result := 'E';
431 ELSIF REMAINDER = 5
432 then
433 cd_result := 'F';
434 ELSIF REMAINDER = 6
435 then
436 cd_result := 'G';
437 ELSIF REMAINDER = 7
438 then
439 cd_result := 'H';
440 ELSIF REMAINDER = 8
441 then
442 cd_result := 'I';
443 ELSIF REMAINDER = 9
444 then
445 cd_result := 'J';
446 ELSIF REMAINDER = 10
447 then
448 cd_result := 'K';
449 ELSIF REMAINDER = 11
450 then
451 cd_result := 'L';
452 ELSIF REMAINDER = 12
453 then
454 cd_result := 'M';
455 ELSIF REMAINDER = 13
456 then
457 cd_result := 'N';
458 ELSIF REMAINDER = 14
459 then
460 cd_result := 'O';
461 ELSIF REMAINDER = 15
462 then
463 cd_result := 'P';
464 ELSIF REMAINDER = 16
465 then
466 cd_result := 'Q';
467 ELSIF REMAINDER = 17
471 then
468 then
469 cd_result := 'R';
470 ELSIF REMAINDER = 18
472 cd_result := 'S';
473 ELSIF REMAINDER = 19
474 then
475 cd_result := 'T';
476 ELSIF REMAINDER = 20
477 then
478 cd_result := 'U';
479 ELSIF REMAINDER = 21
480 then
481 cd_result := 'V';
482 ELSIF REMAINDER = 22
483 then
484 cd_result := 'W';
485 ELSIF REMAINDER = 23
486 then
487 cd_result := 'X';
488 ELSIF REMAINDER = 24
489 then
490 cd_result := 'Y';
491 ELSIF REMAINDER = 25
492 then
493 cd_result := 'Z';
494 ELSE
495 system_failure;
496 END IF;
497
498 RETURN cd_result;
499
500 end find_check_digit;
501
502 /** returns the weighting of the even-postitioned figures. **/
503 function func_even_weighting(IN_VALUE VARCHAR2) RETURN NUMBER IS
504 even_result number(2);
505 begin
506 IF IN_VALUE in ('A','0')
507 then
508 even_result := 0;
509 ELSIF IN_VALUE in ('B','1')
510 then
511 even_result := 1;
512 ELSIF IN_VALUE in ('C','2')
513 then
514 even_result := 2;
515 ELSIF IN_VALUE in ('D','3')
516 then
517 even_result := 3;
518 ELSIF IN_VALUE in ('E','4')
519 then
520 even_result := 4;
521 ELSIF IN_VALUE in ('F','5')
522 then
523 even_result := 5;
524 ELSIF IN_VALUE in ('G','6')
525 then
526 even_result := 6;
527 ELSIF IN_VALUE in ('H','7')
528 then
529 even_result := 7;
530 ELSIF IN_VALUE in ('I','8')
531 then
532 even_result := 8;
533 ELSIF IN_VALUE in ('J','9')
534 then
535 even_result := 9;
536 ELSIF IN_VALUE = 'K'
537 then
538 even_result := 10;
539 ELSIF IN_VALUE = 'L'
540 then
541 even_result := 11;
542 ELSIF IN_VALUE = 'M'
543 then
544 even_result := 12;
545 ELSIF IN_VALUE = 'N'
546 then
547 even_result := 13;
548 ELSIF IN_VALUE = 'O'
549 then
550 even_result := 14;
551 ELSIF IN_VALUE = 'P'
552 then
553 even_result := 15;
554 ELSIF IN_VALUE = 'Q'
555 then
556 even_result := 16;
557 ELSIF IN_VALUE = 'R'
558 then
559 even_result := 17;
560 ELSIF IN_VALUE = 'S'
561 then
562 even_result := 18;
563 ELSIF IN_VALUE = 'T'
564 then
565 even_result := 19;
566 ELSIF IN_VALUE = 'U'
567 then
568 even_result := 20;
569 ELSIF IN_VALUE = 'V'
570 then
571 even_result := 21;
572 ELSIF IN_VALUE = 'W'
573 then
574 even_result := 22;
575 ELSIF IN_VALUE = 'X'
576 then
577 even_result := 23;
578 ELSIF IN_VALUE = 'Y'
579 then
580 even_result := 24;
581 ELSIF IN_VALUE = 'Z'
582 then
583 even_result := 25;
584 END IF;
585
586 RETURN even_result;
587
588 end func_even_weighting;
589
590 /** returns the weighting of the odd-postitioned figures. **/
591 function func_odd_weighting(ODD_VALUE VARCHAR2) RETURN NUMBER IS
592 /* Bug 758931. Changed the odd_result to 3 for ODD_VALUE 'P' so that
593 Italian fiscal code validation is correct */
594 odd_result number(2);
595 begin
596 IF ODD_VALUE in ('A','0')
597 then
598 odd_result := 1;
599 ELSIF ODD_VALUE in ('B','1')
600 then
601 odd_result := 0;
602 ELSIF ODD_VALUE in ('C','2')
603 then
604 odd_result := 5;
605 ELSIF ODD_VALUE in ('D','3')
606 then
607 odd_result := 7;
608 ELSIF ODD_VALUE in ('E','4')
609 then
610 odd_result := 9;
611 ELSIF ODD_VALUE in ('F','5')
612 then
613 odd_result := 13;
614 ELSIF ODD_VALUE in ('G','6')
615 then
616 odd_result := 15;
617 ELSIF ODD_VALUE in ('H','7')
618 then
619 odd_result := 17;
620 ELSIF ODD_VALUE in ('I','8')
621 then
622 odd_result := 19;
623 ELSIF ODD_VALUE in ('J','9')
624 then
625 odd_result := 21;
626 ELSIF ODD_VALUE = 'K'
627 then
628 odd_result := 2;
629 ELSIF ODD_VALUE = 'L'
630 then
631 odd_result := 4;
632 ELSIF ODD_VALUE = 'M'
633 then
634 odd_result := 18;
635 ELSIF ODD_VALUE = 'N'
636 then
637 odd_result := 20;
638 ELSIF ODD_VALUE = 'O'
639 then
640 odd_result := 11;
641 ELSIF ODD_VALUE = 'P'
642 then
643 odd_result := 3;
644 ELSIF ODD_VALUE = 'Q'
645 then
646 odd_result := 6;
647 ELSIF ODD_VALUE = 'R'
648 then
649 odd_result := 8;
650 ELSIF ODD_VALUE = 'S'
651 then
652 odd_result := 12;
653 ELSIF ODD_VALUE = 'T'
654 then
655 odd_result := 14;
656 ELSIF ODD_VALUE = 'U'
657 then
658 odd_result := 16;
659 ELSIF ODD_VALUE = 'V'
660 then
661 odd_result := 10;
662 ELSIF ODD_VALUE = 'W'
663 then
664 odd_result := 22;
665 ELSIF ODD_VALUE = 'X'
666 then
667 odd_result := 25;
671 ELSIF ODD_VALUE = 'Z'
668 ELSIF ODD_VALUE = 'Y'
669 then
670 odd_result := 24;
672 then
673 odd_result := 23;
674 END IF;
675
676 RETURN odd_result;
677
678 end func_odd_weighting;
679
680 /****************/
681 /* MAIN SECTION */
682 /****************/
683
684 BEGIN
685
686 nif_value := NIF;
687 check_digit := substr(NIF_VALUE, length(NIF_VALUE));
688 total_weighting := 0;
689 position_weight := 0;
690
691 IF Xi_UNIQUE_FLAG = 'F'
692 then
693 fail_uniqueness;
694
695 ELSIF Xi_UNIQUE_FLAG = 'P'
696 then
697
698 /** make sure that Fiscal code is only 16 chars - including Check digit **/
699 IF length(NIF) = 16
700 then
701
702 FOR position_i IN 1..15 LOOP
703
704 /** moves along length of Fiscal Code and assigns weightings **/
705 /** to each of the codes characters upto and including the 15th char **/
706 /** on each loop the total of weightings is totalled **/
707
708 char_value := substr(NIF_VALUE,position_i,1);
709 IF position_i in (2,4,6,8,10,12,14)
710 then
711 position_weight := func_even_weighting(char_value);
712 ELSE
713 position_weight := func_odd_weighting(char_value);
714 END IF;
715
716 total_weighting := total_weighting + position_weight;
717
718 END LOOP; /** of the counter position_i **/
719
720 /** Divide the total by 23 and store the remainder into cal_check **/
721 calc_check := MOD(total_weighting, 26);
722
723 calc_cd := find_check_digit(calc_check);
724
725 /*** After having calculated what should be the ITALIAN Fiscal ***/
726 /*** Check digit compare to the actual and fail if not the same ***/
727
728 IF calc_cd <> check_digit
729 then
730 fail_check;
731 ELSE
732 pass_check;
733 END IF;
734
735 ELSIF length(NIF) = 11
736 then
737
738 /** RW 21-FEB-95 Additional requirement of Italy **/
739 /** This is a new requirement. Italian Fiscal Codes may either be **/
740 /** 16 OR 11 chars - if 11 then must pass the VAT Code validation **/
741 /** routine - if 16 must be Fiscal Code for an individual which **/
742 /** has this procedure to validate it **/
743
744 ZX_TRN_VALIDATION_PKG.VALIDATE_TRN_IT( p_trn_value => nif_value
745 ,p_trn_type => NULL
746 ,p_check_unique_flag => 'S'
747 ,p_return_status => vat_ret_code
748 ,p_error_buffer => vat_ret_message);
749
750 IF vat_ret_code = 'E'
751 then
752 fail_check;
753 ELSE
754 pass_check;
755 END IF;
756
757
758 ELSE
759 fail_check; /** Fiscal code is incorrect length **/
760
761 END IF;
762
763 ELSE
764 pass_check;
765 END IF; /** of fail uniqueness check **/
766
767 END VALIDATE_NIF_IT;
768
769 FUNCTION validate_taxpayer_id (pv_taxpayer_id VARCHAR2, p_party_type_code VARCHAR2) RETURN VARCHAR2
770 IS
771
772 CURSOR supplier_taxpayer_id_csr(c_taxpayer_id varchar2) IS
773 SELECT 1
774 FROM ap_suppliers pv,
775 (SELECT distinct person_id
776 ,national_identifier
777 FROM per_all_people_f
778 WHERE nvl(effective_end_date,sysdate) >= sysdate ) papf
779 WHERE pv.employee_id = papf.person_id (+)
780 AND NVL(papf.national_identifier,NVL(pv.individual_1099,pv.num_1099)) = c_taxpayer_id;
781
782 CURSOR customer_taxpayer_id_csr(c_taxpayer_id varchar2) IS
783 SELECT 1 FROM hz_parties
784 WHERE jgzz_fiscal_code = c_taxpayer_id;
785
786
787 l_count number :=0;
788 error_status VARCHAR2(10);
789 eror_buffer VARCHAR2(100);
790 l_number number;
791
792 BEGIN
793 l_count := 0;
794 eror_buffer := NULL;
795 error_status := NULL;
796
797 IF pv_taxpayer_id IS NOT NULL THEN --if1
798
799 IF p_party_type_code = 'SUPPLIER' THEN --if2
800
801 OPEN supplier_taxpayer_id_csr(pv_taxpayer_id);
802 LOOP
803
804 EXIT WHEN supplier_taxpayer_id_csr%NOTFOUND;
805
806 FETCH supplier_taxpayer_id_csr into l_number ;
807
808 IF supplier_taxpayer_id_csr%FOUND THEN --if3
809 l_count := l_count+ 1;
810 END IF; --if3
811
812 END LOOP;
813
814 IF l_count > 1 THEN --if4
815 FND_MESSAGE.SET_NAME('JE','JE_IT_NIF_DUPLICATE');
816 g_vat_reg_error := FND_MESSAGE.get;
817 RETURN g_vat_reg_error;
818 END IF; --if4
819
820 IF l_count < 2 THEN --if5
821
822 VALIDATE_NIF_IT (pv_taxpayer_id,'P',error_status,g_vat_reg_error);
823
824 IF error_status = 'F' THEN
825 FND_MESSAGE.SET_NAME('JE','JE_IT_NIF_INVALID');
826 g_vat_reg_error := FND_MESSAGE.get;
827 RETURN g_vat_reg_error;
828 END IF;
829
830 END IF; --if5
831
832 ELSIF p_party_type_code = 'CUSTOMER' THEN --if2
833
834 l_count := 0;
835
836 OPEN customer_taxpayer_id_csr(pv_taxpayer_id);
837 LOOP
838
842
839 EXIT WHEN customer_taxpayer_id_csr%NOTFOUND;
840
841 FETCH customer_taxpayer_id_csr into l_number ;
843 IF customer_taxpayer_id_csr%FOUND THEN --if6
844 l_count := l_count+ 1;
845 END IF; --if6
846
847 END LOOP;
848
849 IF l_count > 1 THEN --if7
850 FND_MESSAGE.SET_NAME('JE','JE_IT_NIF_DUPLICATE');
851 g_vat_reg_error := FND_MESSAGE.get;
852 RETURN g_vat_reg_error;
853 END IF; --if7
854
855 IF l_count < 2 THEN --if8
856
857 VALIDATE_NIF_IT (pv_taxpayer_id,'P',error_status,g_vat_reg_error);
858 IF error_status = 'F' THEN
859 FND_MESSAGE.SET_NAME('JE','JE_IT_NIF_INVALID');
860 g_vat_reg_error := FND_MESSAGE.get;
861 RETURN g_vat_reg_error;
862 END IF;
863
864 END IF; --if8
865
866 END IF; ----if2
867
868 END IF; --if1
869
870 RETURN 'TRUE';
871
872 EXCEPTION
873 WHEN others THEN
874 fnd_file.put_line(fnd_file.log,'Error in Taxpayer ID validation');
875 fnd_file.put_line(fnd_file.log,'Error Message:'||SQLERRM||'Error Code:'||SQLCODE);
876 FND_MESSAGE.SET_NAME('JE','JE_IT_NIF_INVALID');
877 g_vat_reg_error := FND_MESSAGE.get;
878 RETURN g_vat_reg_error;
879
880 END validate_taxpayer_id;
881
882 END JE_IT_XML_LISTING_PKG;