| Both sides previous revision
Previous revision
Next revision
|
Previous revision
|
cc:cc_token_upgrade [2020/01/28 11:10]
dtucker [TWBS Credit Card Token/Profile Version Primer] |
cc:cc_token_upgrade [2023/02/03 09:16] (current)
dtucker [Migration of In-Process Transaction Records] |
| 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 |
| |
| <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}} |
