How to fix Pending Sales on Smartcontroller after Upgrade to BEPOZ 4.6.1.22 and above

Overview

Problem / Symptoms:

If you have recently upgrade a client to 4.6.1.22 and above and the sales are no longer polling from various Remote Sites, the following process needs to be followed to amend and ensure the sales poll to the Headoffice (HO).

The Below PROCESS ONLY HAS TO BE DONE FOR A MULTIVENUE HO THAT HAVE BEEN UPDATE TO 4.6.1.22 WITH CORRUPTED REMOTETRANSID’S
So before updating if it is identified that there are Duplicated RemoteTransID all that is required is to simply do a Database.exe - Create Venue Data Recovery for Each venue which will then ensure that the next RemoteTransactionID will be the Highest Value in line.

Diagnosis:

SQL - Check Headoffice (HO) Database for Duplicated RemoteTransID

Solution / resolution:

--Step 1

--Check HO and find the Range of current RemoteTransID's

DECLARE @VID INT;

SET @VID = 2; -- Change for VenueID

SELECT DISTINCT

FIRST_VALUE(RemoteTransID) OVER (ORDER BY TransactionID) StartingRemoteTransID,

LAST_VALUE(RemoteTransID) OVER (ORDER BY TransactionID

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LastRemoteTRansID

FROM Transactions

where VenueID = @VID and RemoteTransID <> 0

--Step 2

--Check Venue and find the Range of current RemoteTransID's

DECLARE @VID INT;

SET @VID = 2; -- Change for VenueID

SELECT DISTINCT

FIRST_VALUE(TransactionID) OVER (ORDER BY TransactionID) StartingTransID,

LAST_VALUE(TransactionID) OVER (ORDER BY TransactionID

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LastTRansID

FROM Transactions

where VenueID = @VID and RemoteTransID <> 0

--Step 3

--HO Example - RemoteTransID’s range between 711556 and 785344

--Venue Transactions Example - Ranges between 1 and 700000

--Reseed RemoteTransID at HO to be outside the Range from the Venue (Change @VID to =VenueID and @RANGE)

--Example of Range Start = 800000 (This will reseed the RemoteTransID to Start from 800000+1 (outside the current Remote Venue Transaction Range)

DECLARE @VID INT;

DECLARE @RANGE INT;

SET @RANGE= 800000; --Change Range

SET @VID = 2; -- Change for VenueID

WITH CTE AS (

SELECT [RemoteTransID],

ROW_NUMBER() OVER (ORDER BY [TransactionID]) AS RN

FROM Transactions where Venueid = @VID)

UPDATE CTE

SET [RemoteTransID] = RN+@RANGE;

--Step 4

--You can then double check the Range again at HO

--Check HO and find the Range of current RemoteTransID's

DECLARE @VID INT;

SET @VID = 2; -- Change for VenueID

SELECT DISTINCT

FIRST_VALUE(RemoteTransID) OVER (ORDER BY TransactionID) StartingRemoteTransID,

LAST_VALUE(RemoteTransID) OVER (ORDER BY TransactionID

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LastRemoteTRansID

FROM Transactions

where VenueID = @VID and RemoteTransID <> 0

Example:

As shown below you can see that the RemoteTransID for Venue 2 now start from 800001 and End at 2424169 which is outside the range on the Remote Venue

TransactionID at the Remote Venue Ranges from 1 to 700000

Now Start Smartcontroller at HO and Start the Smartcontroller at the remote venue.
The Sales should now continue to Poll to HO.
Once all the sales have been Polled.
CREATE A Database.exe - Create Venue Data Recovery for said VenueID (THIS IS A MUST AND CANNOT BE SKIPPED).
Restore VenueDataRecovery at Remote Site.
ENSURE after DB Restore to Run UPDATE TABLES again via Database.exe (THIS IS A MUST AND CANNOT BE SKIPPED) as this will ensure the Stored Procedures are created.

How to fix Pending Sales on Smartcontroller after Upgrade to BEPOZ 4.6.1.22 and above

Overview

Problem / Symptoms:

Diagnosis:

Solution / resolution:

Related Articles: