Script to Fix Posting Cycle Billing

04/29/2024 10:04 am EDT

Script below is to fix the error message received when posting cycle billing and either the Account name or address is too long.

Overview

These scripts should be used by a user who knows how to use SQL – Workaround was developed from Sales Force Case #00058346 / DEVOPS #31134

-- to find what banks have wrong lengths

select * from AR_Customer_Bank where 

len(Account_Number) > 20 or 

len(Bank_Account_Name) > 22 or 

len(Checking_Or_Savings) > 1 or 

len(Last_Four_Digits) > 4 or 

len(ACH_Direct_Token) > 50

-- to find what credit cards have wrong lengths

*** This script gives both the wrong number of characters as well as what it should be

SELECT customer_id, Card_Type, '4' as 'Card_Type Max', len(card_type)as 'Current_chars',Card_Number, '16' as 'Card_Number Max',len(Card_Number)as 'Current_chars', 

Expiration_Month, '2' as 'Exp_Mnth Max',len(Expiration_Month)as 'Current_chars', Expiration_Year, '2' as 'Exp_Year Max', len(Expiration_Year)as 'Current_chars',  

Account_Name, '22' as 'Account_Name Max',len(Account_Name)as 'Current_chars', Security_Code, '8' as 'Security_Code Max',len(Security_Code)as 'current_chars',  

Customer_Address, '35' as 'Cust_Address Max',len(Customer_Address)as 'Current_chars',Customer_PostalCode, '10' as 'PostCode Max',len(Customer_PostalCode)as 'Curent_chars', 

Last_Four_Digits, '4' as 'LastFour Max',len(Last_Four_Digits)as 'Current_chars', ACH_Direct_Token, '50' as 'Token Max' , len(ACH_Direct_Token)as 'Current_chars' 

 

FROM ar_customer_cc  

 

WHERE 

len(Card_Type) > 4 or 

len(Card_Number) > 16 or 

len(Expiration_Month) > 2 or 

len(Expiration_Year) > 2 or 

len(Account_Name) > 22 or 

len(Security_Code) > 8 or 

len(Customer_Address) > 35 or 

len(Customer_PostalCode) > 10 or 

len(Last_Four_Digits) > 4 or 

len(ACH_Direct_Token) > 50

-- find customer_cc_id

 select * from ar_customer_cc where customer_id in (2059317,2056289) 

--Customer_CC_id in (54655,654968)

-- backup data found prior to update

// select * into arcustcc_SV1234 from ar_customer_cc where customer_id in (2059317,2056289)

-- update data to correct overage

--//update ar_customer_cc set Account_Name = 'John Waymore' where customer_id = 2059317 and Customer_CC_id = 25468 

--//update ar_customer_cc set Customer_Address = 'Site 439 Box 2 Comp 8 RR 3 Stn Main' where customer_id = 2056289 and Customer_CC_id = 654968

Was this article helpful?