Differences

This shows you the differences between two versions of the page.

--- cc:cc_token_upgrade [2023/02/01 16:45]
dtucker [Verification of the Card Gateway Data Migration Process]
+++ cc:cc_token_upgrade [2023/02/03 09:16] (current)
dtucker [Migration of In-Process Transaction Records]
@@ Line 138: / Line 138: @@
 <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>
-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>
-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}}

Wiki

User Tools

Site Tools

Differences

Page Tools