This is an old revision of the document!
Table of Contents
TWBS Credit Card Token/Profile Version Primer
Authorize.Net Specific Information
- Customer Information Manager (CIM) Activation – Prior to adding new customer/payment profiles to the system either via entry or card migration for upgrades, an end-customer representative will be required to log onto the Authorize.Net account via the website and activate the Customer Information Manager (CIM) functionality. If this is not done, card entry/migration will fail.
- Payment Profile Limitation per Customer Profile – There is a maximum of 10 payment profiles (cards) that can be stored on each customer profile (business partner) in Authorize.Net. During card migration, the application will migrate the default card for the BP first, then up to the next 9 cards in order of unique ID assigned. Any cards above the 10 allowed will not be migrated. To add a new payment profile to a customer profile that has 10 payment profiles, one of the existing payment profiles would need to be removed.
CyberSource Specific Information
- Recurring Billing Function Activation – Prior to adding new customer/payment profiles to the system either via entry or card migration for upgrades, an end-customer representative will be required to contact CyberSource and request that the Recurring Billing function be enabled on their CyberSource account. If this is not done, card entry/migration will fail.
When contacting CyberSource to activate the Recurring Billing functionality, please specify that they use the 16-character token length option.
- Contact the Processing Center on the CyberSource Account to Determine Any Daily Transaction Limits Prior to Card Migration – Creation of a new payment profile, either by manually entry or card migration, counts as a $0 transaction. Once the daily transaction limit on the processing center account and the number of cards to be migrated are known, if the number of cards to be migrated exceeds the daily transaction limit, please contact TWBS via support ticket to discuss options. It is also advisable that the customer notify the processing center in advance before the profile migration to make them aware of the large number of transactions that will be executed against the gateway account in a short period of time to let them know the source of the activity.
- Review Card Address Info and Use of AVS Exemption from Prior TWBS CC Version if Upgrading – Address information passed into Recurring Billing profiles is AVS validated. If the address information saved and passed is not correct, but perhaps was not noticed because the Exclude from AVS Checking option had been selected for processing in the previous version of TWBS CC, the migration of that card will fail depending on the AVS acceptance settings on your CyberSource account. The Exclude from AVS Checking option isn't available in the latest version of TWBS CC.
Card Cleanup Prior to Upgrade to TWBS CC Tokenized Version – Highly Recommended
Expired cards will migrate to the gateway. Due to some of the gateway limitations discussed in the gateway specific sections, it is advised that prior to the card migration that the data be cleaned up. This can be identifying the cards that are already expired and providing the data to the customer to determine which cards need to be updated followed by removal of all cards that aren’t update and remain expired. Other customers may simply choose to remove all cards that have already expired.
Selection Query to determine how many cards have expired prior to the current month (SQL)
SELECT * FROM [@TWBS_CC_BPCC] WHERE CONVERT(INT,RIGHT(U_ExpirationDate,2)) < CONVERT(INT,RIGHT(YEAR(GETDATE()),2)) OR (CONVERT(INT,RIGHT(U_ExpirationDate,2)) = CONVERT(INT,RIGHT(YEAR(GETDATE()),2)) AND CONVERT(INT,LEFT(U_ExpirationDate,2)) < CONVERT(INT,LEFT(MONTH(GETDATE()),2)))
Query to remove cards that have expired prior to the current month (SQL)
DELETE FROM [@TWBS_CC_BPCC] WHERE CONVERT(INT,RIGHT(U_ExpirationDate,2)) < CONVERT(INT,RIGHT(YEAR(GETDATE()),2)) OR (CONVERT(INT,RIGHT(U_ExpirationDate,2)) = CONVERT(INT,RIGHT(YEAR(GETDATE()),2)) AND CONVERT(INT,LEFT(U_ExpirationDate,2)) < CONVERT(INT,LEFT(MONTH(GETDATE()),2)))
Selection Query to determine how many cards have expired prior to the current month (HANA)
SELECT * FROM “@TWBS_CC_BPCC” WHERE CAST(RIGHT(“U_ExpirationDate”,2) as INT) < CAST(RIGHT(YEAR(NOW()),2) as INT) OR (CAST(RIGHT(“U_ExpirationDate”,2) as INT) = CAST(RIGHT(YEAR(NOW()),2) as INT) AND CAST(LEFT(“U_ExpirationDate”,2) as INT) < CAST(LEFT(MONTH(NOW()),2) as INT))
Query to remove cards that have expired prior to the current month (HANA)
DELETE FROM “@TWBS_CC_BPCC” WHERE CAST(RIGHT(“U_ExpirationDate”,2) as INT) < CAST(RIGHT(YEAR(NOW()),2) as INT) OR (CAST(RIGHT(“U_ExpirationDate”,2) as INT) = CAST(RIGHT(YEAR(NOW()),2) as INT) AND CAST(LEFT(“U_ExpirationDate”,2) as INT) < CAST(LEFT(MONTH(NOW()),2) as INT))
BP Associated Card Gateway Data Migration Time Factors
- Authorize.Net profile conversion is approximately 2-3 seconds per card (approx.. 1200/hour)
- CyberSource profile conversion is approximately 1-2 seconds per card.
Please remember to factor that migration timeframe into any estimates provided for the time to complete the upgrade process.
Verification of the Card Gateway Data Migration Process
During the card migration process, the SAP status bar will provide an update on the progress of the migration (“Migrating credit card 2418 of 2600” for example). At the completion of this process, if any of the cards failed the migration to the gateway, the status bar will present the user with an error (red) message stating “Credit Card information migration failed.” Any cards that failed the migration process will have their encrypted card # replaced with a message starting with the text “Migration failed for…” Example: “Migration failed for C130000-C130000 VISA4-You cannot add more than 10 payment profiles.”
To determine how many cards failed the migration process you can run the following query:
SELECT * FROM [@TWBS_CC_BPCC] WHERE U_CardNumber NOT LIKE '%success%'
A list of the cards that failed migration can be provided to the customer as these cards will not be visible in the front end of the application because they will not be stored in the new user table for payment profile information
New UDTs in SAP B1 Company DB
- @TWBS_LICENSE – New UDT in all add-ons for local storage of license checking to prevent applications from verifying the license with TWBS web service on every start. Will only create if it does not already exist.
- @TWBS_CC_CCTOKEN – New UDT to store payment profile information (cards) that have been stored on the gateway.
- @TWBS_CC_CUSTPROFILE – New UDT to store customer profile information (BP) that have been stored on the gateway.
- @TWBS_CC_TRX – New UDT to store transaction table information. This table will replace the AM_CCAD table for existing customers.
The TWBS Credit Card application does not convert/migrate transaction records from the AM_CCAD table for authorizations and documents that are still open at the time of upgrade.
Removal of the Encrypted Card Number for Upgrading Customers
During the card migration process, the application will remove all of the encrypted card #’s from the UDT that stored the BP associated cards. However, the application will not remove the encrypted card numbers from the prior transaction table (AM_CCAD).
Query to remove encrypted card numbers from the old transaction table (SQL)
UPDATE AM_CCAD Set CrCardNum = NULL
Query to remove encrypted card numbers from the old transaction table (HANA)
UPDATE “AM_CCAD” Set “CrCardNum” = NULL
Migration of In-Process Transaction Records
While the application will migrate the business partner associated credit cards into payment profiles on the gateway, it will not migrate any transaction records from the old table to the new table if there are authorizations that are in-process and have not been settled prior to the upgrade to the latest version of the application. To migrate these in-process transactions you will need to run the following queries.
Each document type (Sales Order, Delivery Note, etc.) has their own separate query.
Query to migrate Sales Order transaction records to the new user table (SQL)
INSERT INTO [@TWBS_CC_TRX] (Code, Name, U_DocNum, U_DocType, U_LineID, U_OrigVoucherNum, U_OrigAuthCode, U_VoucherNum, U_AuthCode, U_AuthDate, U_CrCardName, U_CrCardNum, U_CrTypeCode, U_CardValid, U_CreditCur, U_CreditRate, U_OwnerIDNum, U_CreditSum, U_Amount, U_NameonCard, U_Street, U_Block, U_City, U_State, U_Zip, U_TransType, U_ParentDocNum, U_Result, U_Status, U_Deleted, U_EncryptVersion, U_ReAuthorized, U_CardCode, U_CardName, U_DisplayCardNum, U_Address1, U_Settle, U_FatherCard, U_M_Order_ID, U_Processor, U_Address2, U_Address3, U_Country, U_MarkType, U_MarkValue, U_InstlmntNum, U_DocOpenAmt, U_DocEntry) SELECT CONVERT(nvarchar,GETDATE(),101) + '-IMPORT-SO#' + CONVERT(nvarchar,A.DocNum) AS [Code], CONVERT(nvarchar,GETDATE(),101) + '-IMPORT-SO#' + CONVERT(nvarchar,A.DocNum) AS [Name], A.DocNum, A.DocType, A.LineID, A.OrigVoucherNum, A.OrigAuthCode, A.VoucherNum, A.AuthCode, A.AuthDate, A.CrCardName, Right(A.DisplayCardNum,4) AS [CrCardNum], A.CrTypeCode, A.CardValid, A.CreditCur, A.CreditRate, A.OwnerIDNum, A.CreditSum, A.Amount, A.NameonCard, A.Street, A.Block, A.City, A.State, A.Zip, A.TransType, A.ParentDocNum, A.Result, A.Status, A.Deleted, A.EncryptVersion, A.ReAuthorized, A.CardCode, A.CardName, ' ' AS [DisplayCardNum], A.Address1, A.Settle, A.FatherCard, A.M_Order_ID, A.Processor, A.Address2, A.Address3, A.Country, A.MarkType, A.MarkValue, A.InstlmntNum, A.DocOpenAmt, A.DocEntry FROM AM_CCAD A (NOLOCK) INNER JOIN ORDR O (NOLOCK) ON A.DocEntry = O.DocEntry WHERE O.DocStatus = 'O' AND A.DocType = '17' AND A.Status NOT IN ('D','V') AND A.Settle NOT IN ('V','X','I')
Query to migrate Delivery Note transaction records to the new user table (SQL)
INSERT INTO [@TWBS_CC_TRX] (Code, Name, U_DocNum, U_DocType, U_LineID, U_OrigVoucherNum, U_OrigAuthCode, U_VoucherNum, U_AuthCode, U_AuthDate, U_CrCardName, U_CrCardNum, U_CrTypeCode, U_CardValid, U_CreditCur, U_CreditRate, U_OwnerIDNum, U_CreditSum, U_Amount, U_NameonCard, U_Street, U_Block, U_City, U_State, U_Zip, U_TransType, U_ParentDocNum, U_Result, U_Status, U_Deleted, U_EncryptVersion, U_ReAuthorized, U_CardCode, U_CardName, U_DisplayCardNum, U_Address1, U_Settle, U_FatherCard, U_M_Order_ID, U_Processor, U_Address2, U_Address3, U_Country, U_MarkType, U_MarkValue, U_InstlmntNum, U_DocOpenAmt, U_DocEntry) SELECT CONVERT(nvarchar,GETDATE(),101) + '-IMPORT-DN#' + CONVERT(nvarchar,A.DocNum) AS [Code], CONVERT(nvarchar,GETDATE(),101) + '-IMPORT-DN#' + CONVERT(nvarchar,A.DocNum) AS [Name], A.DocNum, A.DocType, A.LineID, A.OrigVoucherNum, A.OrigAuthCode, A.VoucherNum, A.AuthCode, A.AuthDate, A.CrCardName, Right(A.DisplayCardNum,4) AS [CrCardNum], A.CrTypeCode, A.CardValid, A.CreditCur, A.CreditRate, A.OwnerIDNum, A.CreditSum, A.Amount, A.NameonCard, A.Street, A.Block, A.City, A.State, A.Zip, A.TransType, A.ParentDocNum, A.Result, A.Status, A.Deleted, A.EncryptVersion, A.ReAuthorized, A.CardCode, A.CardName, ' ' AS [DisplayCardNum], A.Address1, A.Settle, A.FatherCard, A.M_Order_ID, A.Processor, A.Address2, A.Address3, A.Country, A.MarkType, A.MarkValue, A.InstlmntNum, A.DocOpenAmt, A.DocEntry FROM AM_CCAD A (NOLOCK) INNER JOIN ODLN D (NOLOCK) ON A.DocEntry = D.DocEntry WHERE D.DocStatus = 'O' AND A.DocType = '15' AND A.Status NOT IN ('D','V') AND A.Settle NOT IN ('V','X','I')
Query to migrate A/R Invoices transaction records to the new user table (SQL)
INSERT INTO [@TWBS_CC_TRX] (Code, Name, U_DocNum, U_DocType, U_LineID, U_OrigVoucherNum, U_OrigAuthCode, U_VoucherNum, U_AuthCode, U_AuthDate, U_CrCardName, U_CrCardNum, U_CrTypeCode, U_CardValid, U_CreditCur, U_CreditRate, U_OwnerIDNum, U_CreditSum, U_Amount, U_NameonCard, U_Street, U_Block, U_City, U_State, U_Zip, U_TransType, U_ParentDocNum, U_Result, U_Status, U_Deleted, U_EncryptVersion, U_ReAuthorized, U_CardCode, U_CardName, U_DisplayCardNum, U_Address1, U_Settle, U_FatherCard, U_M_Order_ID, U_Processor, U_Address2, U_Address3, U_Country, U_MarkType, U_MarkValue, U_InstlmntNum, U_DocOpenAmt, U_DocEntry) SELECT CONVERT(nvarchar,GETDATE(),101) + '-IMPORT-IN#' + CONVERT(nvarchar,A.DocNum) AS [Code], CONVERT(nvarchar,GETDATE(),101) + '-IMPORT-IN#' + CONVERT(nvarchar,A.DocNum) AS [Name], A.DocNum, A.DocType, A.LineID, A.OrigVoucherNum, A.OrigAuthCode, A.VoucherNum, A.AuthCode, A.AuthDate, A.CrCardName, Right(A.DisplayCardNum,4) AS [CrCardNum], A.CrTypeCode, A.CardValid, A.CreditCur, A.CreditRate, A.OwnerIDNum, A.CreditSum, A.Amount, A.NameonCard, A.Street, A.Block, A.City, A.State, A.Zip, A.TransType, A.ParentDocNum, A.Result, A.Status, A.Deleted, A.EncryptVersion, A.ReAuthorized, A.CardCode, A.CardName, ' ' AS [DisplayCardNum], A.Address1, A.Settle, A.FatherCard, A.M_Order_ID, A.Processor, A.Address2, A.Address3, A.Country, A.MarkType, A.MarkValue, A.InstlmntNum, A.DocOpenAmt, A.DocEntry FROM AM_CCAD A (NOLOCK) INNER JOIN OINV I (NOLOCK) ON A.DocEntry = I.DocEntry WHERE I.DocStatus = 'O' AND A.DocType = '13' AND A.Status NOT IN ('D','V') AND A.Settle NOT IN ('V','X','I')
Query to migrate A/R Down Payment Invoice transaction records to the new user table (SQL)
INSERT INTO [@TWBS_CC_TRX] (Code, Name, U_DocNum, U_DocType, U_LineID, U_OrigVoucherNum, U_OrigAuthCode, U_VoucherNum, U_AuthCode, U_AuthDate, U_CrCardName, U_CrCardNum, U_CrTypeCode, U_CardValid, U_CreditCur, U_CreditRate, U_OwnerIDNum, U_CreditSum, U_Amount, U_NameonCard, U_Street, U_Block, U_City, U_State, U_Zip, U_TransType, U_ParentDocNum, U_Result, U_Status, U_Deleted, U_EncryptVersion, U_ReAuthorized, U_CardCode, U_CardName, U_DisplayCardNum, U_Address1, U_Settle, U_FatherCard, U_M_Order_ID, U_Processor, U_Address2, U_Address3, U_Country, U_MarkType, U_MarkValue, U_InstlmntNum, U_DocOpenAmt, U_DocEntry) SELECT CONVERT(nvarchar,GETDATE(),101) + '-IMPORT-DPI#' + CONVERT(nvarchar,A.DocNum) AS [Code], CONVERT(nvarchar,GETDATE(),101) + '-IMPORT-DPI#' + CONVERT(nvarchar,A.DocNum) AS [Name], A.DocNum, A.DocType, A.LineID, A.OrigVoucherNum, A.OrigAuthCode, A.VoucherNum, A.AuthCode, A.AuthDate, A.CrCardName, Right(A.DisplayCardNum,4) AS CrCardNum], A.CrTypeCode, A.CardValid, A.CreditCur, A.CreditRate, A.OwnerIDNum, A.CreditSum, A.Amount, A.NameonCard, A.Street, A.Block, A.City, A.State A.Zip, A.TransType, A.ParentDocNum, A.Result, A.Status, A.Deleted, A.EncryptVersion, A.ReAuthorized, A.CardCode, A.CardName, ' ' AS [DisplayCardNum], A.Address1, A.Settle, A.FatherCard, A.M_Order_ID, A.Processor, A.Address2, A.Address3, A.Country, A.MarkType, A.MarkValue, A.InstlmntNum, A.DocOpenAmt, A.DocEntry FROM AM_CCAD A (NOLOCK) INNER JOIN ODPI DPI (NOLOCK) ON A.DocEntry = DPI.DocEntry WHERE DPI.DocStatus = 'O' AND A.DocType = '203' AND A.Status NOT IN ('D','V') AND A.Settle NOT IN ('V','X','I')
Query to migrate Credit Memo transaction records to the new user table (SQL)
INSERT INTO [@TWBS_CC_TRX] (Code, Name, U_DocNum, U_DocType, U_LineID, U_OrigVoucherNum, U_OrigAuthCode, U_VoucherNum, U_AuthCode, U_AuthDate, U_CrCardName, U_CrCardNum, U_CrTypeCode, U_CardValid, U_CreditCur, U_CreditRate, U_OwnerIDNum, U_CreditSum, U_Amount, U_NameonCard, U_Street, U_Block, U_City, U_State, U_Zip, U_TransType, U_ParentDocNum, U_Result, U_Status, U_Deleted, U_EncryptVersion, U_ReAuthorized, U_CardCode, U_CardName, U_DisplayCardNum, U_Address1, U_Settle, U_FatherCard, U_M_Order_ID, U_Processor, U_Address2, U_Address3, U_Country, U_MarkType, U_MarkValue, U_InstlmntNum, U_DocOpenAmt, U_DocEntry) SELECT CONVERT(nvarchar,GETDATE(),101) + '-IMPORT-CN#' + CONVERT(nvarchar,A.DocNum) AS [Code], CONVERT(nvarchar,GETDATE(),101) + '-IMPORT-CN#' + CONVERT(nvarchar,A.DocNum) AS [Name], A.DocNum, A.DocType, A.LineID, A.OrigVoucherNum, A.OrigAuthCode, A.VoucherNum, A.AuthCode, A.AuthDate, A.CrCardName, Right(A.DisplayCardNum,4) AS [CrCardNum], A.CrTypeCode, A.CardValid, A.CreditCur, A.CreditRate, A.OwnerIDNum, A.CreditSum, A.Amount, A.NameonCard, A.Street, A.Block, A.City, A.State, A.Zip, A.TransType, A.ParentDocNum, A.Result, A.Status, A.Deleted, A.EncryptVersion, A.ReAuthorized, A.CardCode, A.CardName, '' AS [DisplayCardNum], A.Address1, A.Settle, A.FatherCard, A.M_Order_ID, A.Processor, A.Address2, A.Address3, A.Country, A.MarkType, A.MarkValue, A.InstlmntNum, A.DocOpenAmt, A.DocEntry FROM AM_CCAD A (NOLOCK) INNER JOIN ORIN CM (NOLOCK) ON A.DocEntry = CM.DocEntry WHERE CM.DocStatus = 'O' AND A.DocType = '14' AND A.Status NOT IN ('D','V') AND A.Settle NOT IN ('V','X','I')
