Customer Interface Validation Error - ORA-01722: Invalid Number Importing Customer Tax Information with an Italian Tax Registration Number - ZX_PTP_IMPORT

(Doc ID 2018251.1)

Last updated on AUGUST 18, 2016

Applies to:

Oracle Trading Community - Version 12.2.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.2.3 version, Customer Interface

You are attempting to run Customer Interface and the The Party Tax Profile import is failing, when validating the Italian Tax Registration Number.

The following error is raised in the 'Exception Records in ZX_PARTY_TAX_PROFILE_INT' region of Customer Interface Transfer Report:

Import_tax_attributes +VALIDATE req_id - 8016936
l_sql_select_insert_ptp_intf :
l_sql_from_insert_ptp_intf :
22-APR-15 Call ZX_PTP_IMPORT.IMPORT_WRAPPER for VALIDATE with request_id
:8016936
[22-04-2015 10:23:06]  UPDATE ZX_PARTY_TAX_PROFILE_INT  SET PROG_INT_NUM1 =
:G_PROG_INT_VAL
   WHERE request_id = :G_REQUEST_ID
   AND record_status = :G_RS_IN_PROCESS
   AND registration_number IS NOT NULL
   AND country_code = :P_COUNTRY_CODE
   AND BITAND(error_number,:G_ES_PTP_COUNTRY_INVALID) = 0
   AND prog_int_num1 <> :G_PROG_INT_VAL  AND (CASE WHEN
REGEXP_LIKE(registration_number,'^(IT)?[[:digit:]]{11}$') THEN
       substr(substr(registration_number,2,1)*2,1,1) +
nvl(substr(substr(registration_number,2,1)*2,2,1),0) +
       substr(substr(registration_number,4,1)*2,1,1) +
nvl(substr(substr(registration_number,4,1)*2,2,1),0) +
       substr(substr(registration_number,6,1)*2,1,1) +
nvl(substr(substr(registration_number,6,1)*2,2,1),0) +
       substr(substr(registration_number,8,1)*2,1,1) +
nvl(substr(substr(registration_number,8,1)*2,2,1),0) +
       substr(substr(registration_number,10,1)*2,1,1) +
nvl(substr(substr(registration_number,10,1)*2,2,1),0) +
       substr(registration_number,1,1) + substr(registration_number,3,1) +
       substr(registration_number,5,1) + substr(registration_number,7,1) +
substr(registration_number,9,1)
       ELSE :G_MISS_NUM END) =
Decode(substr(registration_number,11,1),0,0,10-substr(registration_number,11,1
))
[22-04-2015 10:23:06] TRNValidation : ZX_PARTY_TAX_PROFILE_INT : IT :
ORA-01722: invalid number
[22-04-2015 10:23:06] ProcessPtyTxProfileRecord : ORA-01722: invalid number
[22-04-2015 10:23:06] IMPORT_PTP : ORA-01722: invalid number
22-APR-15 End Call ZX_PTP_IMPORT.IMPORT_WRAPPER for VALIDATE with status :U
22-APR-15 Error in Call ZX_PTP_IMPORT.IMPORT_WRAPPER for VALIDATE :ORA-01722:invalid number

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms