| Both sides previous revision
Previous revision
Next revision
|
Previous revision
|
cc:cc_token_upgrade [2020/01/28 10:18]
dtucker [Card Cleanup Prior to Upgrade to TWBS CC Tokenized Version – Highly Recommended] |
cc:cc_token_upgrade [2023/02/03 09:16] (current)
dtucker [Migration of In-Process Transaction Records] |
| ====== TWBS Credit Card Token/Profile Version Primer ====== | ====== TWBS Credit Card Token/Profile Version Primer ====== |
| | |
| | This section contains important information for Partners and Consultants that should be reviewed prior to the implementation of the fully tokenized version of the TWBS CC Processing application. This applies to new customer installations and the upgrade of existing TWBS CC customers. Also, please remember that this is a new major release of the application and should always be fully tested in a separate Test environment prior to implementation. |
| | |
| | <WRAP center round important> |
| | For customers upgrading from a previous version of TWBS Credit Card who need to migrate existing cards to the gateway, a copy of the TWBS_SBO-Common **MUST** be restored in the environment. The TWBS CC application will access a key piece of information during the migration process. This is also required even if just performing a full or partial test migration. |
| | </WRAP> |
| | |
| | <WRAP center round info> |
| | The latest version of TWBS CC can **only** be used with the Authorize.Net, CyberSource, or PayEezy gateways. Support for Pay Flow Pro and Moneris is still available in the 92.1.0 version of the application. |
| | </WRAP> |
| | |
| | <WRAP center round info> |
| | In this version of TWBS CC, the profiles are gateway account dependent, **not** database dependent. All testing, including test card migrations, should be done on gateway sandbox/test accounts, not on customer live gateway accounts. Any profiles created on the gateway on a live account in a test database will not be accessible in the production database without additional assistance. Any profiles created on the gateway on a live account in a test database should be removed at the completion of testing. |
| | </WRAP> |
| | |
| | <WRAP center round info> |
| | While latest version of TWBS CC is compliant with the use of TLS 1.2 for data transactions, the 92.1.0 version of the application has patches that are compliant with TLS 1.2 and work for all of the gateways supported by TWBS CC including the Authorize.Net gateway. This version retains locally stored card numbers that are encrypted. |
| | </WRAP> |
| |
| |
| Selection Query to determine how many cards have expired prior to the current month (SQL) | 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)) < | 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))) </WRAP> |
| 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))) </WRAP> | |
| |
| <WRAP center round info> | <WRAP center round info> |
| Query to remove cards that have expired prior to the current month (SQL) | 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)) < | 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))) </WRAP> |
| 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))) </WRAP> | |
| |
| <WRAP center round info> | <WRAP center round info> |
| Selection Query to determine how many cards have expired prior to the current month (HANA) | 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) < | 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)) </WRAP> |
| 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)) </WRAP> | |
| |
| <WRAP center round info> | <WRAP center round info> |
| Query to remove cards that have expired prior to the current month (HANA) | 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) < | 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)) </WRAP> |
| 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)) </WRAP> | |
| |
| {{:app/image44.jpeg?786}} | {{:app/image44.jpeg?786}} |
| To determine how many cards failed the migration process you can run the following query: | 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%' </WRAP> | SELECT * FROM [@TWBS_CC_BPCC] WHERE U_CardNumber LIKE '%failed%' </WRAP> |
| |
| A list of the cards that failed migration can be exported from the SQL results into a spreadsheet and 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 | A list of the cards that failed migration can be exported from the SQL results into a spreadsheet and 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 |
| ===== Removal of the Encrypted Card Number for Upgrading Customers ===== | ===== 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 | 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). |
| stored the BP associated cards. However, the application will not remove the encrypted card numbers from the | |
| prior transaction table (AM_CCAD). | |
| |
| <WRAP center round info> | <WRAP center round info> |
| |
| <WRAP center round info> | <WRAP center round info> |
| Each document type (Sales Order, Delivery Note, etc.) has their own separate query. | Long-term TWBS Credit Card Processing customers may not want to migrate all of the old transaction data into the new table. Usually the last year or so is sufficient to cover all the customer's needs to have transaction data to issue refunds against. </WRAP> |
| </WRAP> | |
| |
| <WRAP center round info> | <WRAP center round info> |
| 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) |
| |
| 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, | SELECT |
| 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') </WRAP> | CONVERT(nvarchar,GETDATE(),101) + '-IMP-' + CONVERT(nvarchar,A.DocType) + '-' + CONVERT(nvarchar,A.DocNum) + '-' + CONVERT(nvarchar,A.LineID) AS [Code], |
| | CONVERT(nvarchar,GETDATE(),101) + '-IMP-' + CONVERT(nvarchar,A.DocType) + '-' + CONVERT(nvarchar,A.DocNum) + '-' + CONVERT(nvarchar,A.LineID) 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 |
| |
| <WRAP center round info> | FROM AM_CCAD A (NOLOCK) |
| 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 | WHERE CONVERT(int,Right(A.AuthDate,2)) IN ('23','22') |
| [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') </WRAP> | </WRAP> |
| |
| <WRAP center round info> | <WRAP center round info> |
| Query to migrate A/R Invoices transaction records to the new user table (SQL) | Additional years can be added to the WHERE clause at your discretion to migrate larger volumes of data. </WRAP> |
| | |
| 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') </WRAP> | |
| | |
| <WRAP center round info> | |
| 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') </WRAP> | |
| | |
| <WRAP center round info> | |
| 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') </WRAP> | |
| |
| {{:app/image44.jpeg?786}} | {{:app/image44.jpeg?786}} |
