When Creating An Invoice An Error Is Received: java.sql.SQLException: ORA-12899: value too large for column "GLOGOWNER"."INVOICE"."SERVPROV_ALIAS_VALUE" (actual: 54, maximum: 50) (Doc ID 1401565.1)

Symptoms

On OTM version 6.1

Invoice creation fails due to the Service Provider Alias having a maximum character limit of 50 character's in the field. The Service Provider ID is 50 characters or under, but the Alias created is above 50 characters. This is because the Service Provider Alias is concatinated with the Domain forcing it above the 50 character limit.

Steps:

1 - Log onto an OTM 6.1 system.

2 - Create a Service Provider with ID: ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWX
(Note this is 50 characters)

3 - Create a Rate Offering and Rate Record for the Service Provider created in step 1.

4 - Create an Order Release, constrained to use Rate Record created above and planned it onto a Shipment.

5 - From the Shipment click: Actions > Financials > Invoice > Create Invoice.

6 - The following error is shown on the screen:

Unable to create Invoice for TInvoice: Invoice: InvoiceGid: <DOMAIN>.20120126-0001 InvoiceNumber: ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWX-000001 ConsolidationType: STANDARD NetAmountDue: 999.0 USD

insert into invoice (invoice_gid,invoice_xid,invoice_type,invoice_source,entered_by_gl_user_gid,invoice_number,servprov_alias_qual_gid,servprov_alias_value,correction_code_id,invoice_date,currency_gid,net_amount_due,net_due_date,payment_method_code_gid,start_date,end_date,invoice_service_code_gid,inco_term_gid,discount_amount,discount_percentage,discount_days_due,discount_due_date,origin_station_city,origin_station_fsac,origin_station_splc,origin_station_province_code,origin_station_postal_code,origin_country_code3_gid,dest_station_city,dest_station_fsac,dest_station_splc,dest_station_province_code,dest_station_postal_code,dest_country_code3_gid,letter_of_credit_exp_date,letter_of_credit_issue_date,letter_of_credit_number,vessel_code_qualifier,vessel_code,vessel_country_code3_gid,vessel_gid,ves_op_servprov_alias_qual_gid,ves_op_servprov_alias_value,voyage_number,parent_invoice_gid,date_received,supply_country_code3_gid,servprov_vat_reg_no_gid,customer_vat_reg_no_gid,vat_exempt_value,net_amt_due_with_tax,is_pass_through,consolidation_type,is_fixed_cost,base_charge,other_charge,is_credit_note,user_defined1_icon_gid,user_defined2_icon_gid,user_defined3_icon_gid,user_defined4_icon_gid,user_defined5_icon_gid,exchange_rate_date,exchange_rate_gid,servprov_gid,sail_date,sail_cutoff_date,rail_date,is_hazardous,is_temperature_control,invoicing_process,is_vat_analysis_fixed,original_invoice_gid,gl_date,domain_name,other_charge_currency_gid,base_charge_currency_gid,net_amt_due_w_tax_currency_gid,discount_amount_gid,net_amount_due_gid,other_charge_base,base_charge_base,net_amt_due_with_tax_base,discount_amount_base,net_amount_base,other_charge_fn_currency_gid,base_charge_fn_currency_gid,net_amt_w_tax_fn_currency_gid,discount_amt_fn_currency_gid,net_amount_due_fn_currency_gid,other_charge_fn,base_charge_fn,net_amt_w_tax_fn,discount_amt_fn,net_amount_due_fn) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?); [<DOMAIN>.20120126-0001, 20120126-0001, I, G, null, ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWX-000001, GLOG, <DOMAIN>.ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWX, null, 2012-01-26 12:43:12 UTC, USD, 999.0 USD, null, null, 2012-01-26 12:41:47 UTC, 2012-01-26 15:42:07 UTC, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, GLOG, <DOMAIN>.ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWX, null, null, 2012-01-26 12:43:12 UTC, null, null, null, NOT_EXEMPT, 999.0 USD, N, STANDARD, N, 999.0 USD, null, N, null, null, null, null, null, null, DEFAULT, <DOMAIN>.ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWX, null, null, null, N, N, S, N, null, null, <DOMAIN>]

java.sql.SQLException: ORA-12899: value too large for column "GLOGOWNER"."INVOICE"."SERVPROV_ALIAS_VALUE" (actual: 54, maximum: 50)



7 - From the above we see the following

servprov_alias_qual_gid = GLOG
servprov_alias_value = <DOMAIN>.ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWX

8 - The Service Provider Alias has had the domain name and a '.' added pushing it above the 50 character limit.

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.