OnLoyalty Migration (5644)

These SQL scripts can be used to upgrade POS version 0.15.0 to version 0.16.0.

It only needs to be manually run for cloud tenants that have enabled the Loyalty feature, which has been removed from version 0.16.0. The scripts transfer the data from the OnLoyalty format into specific POS modules, such as:

Members
Purchases
Loyalty Points
Envoys

These scripts should be run after POS 0.16.0 has been upgraded, and the automatic database migrations have already run. Those migrations create several new tables that are referenced in these scripts.

a) Members SQL

This script should be run for all clients that have previously enabled the Loyalty feature.

Before running this script, manually verify that the PosId field of the OL_CardType table contains values:

The OL_CardType table stores the same data as POS Membership Levels.
The PosId field of each OL_CardType row should be the same as its corresponding Membership Level Code value.
If not, correct the PosId field value manually.

/*
 * Copy OL_Customer to ME_Member. 
 */
 
INSERT INTO [dbo].[ME_Member]
  ([Id]
  ,[FirstName]
  ,[LastName]
  ,[FullName]
  ,[CustomerId]
  ,[Email]
  ,[Status]
  ,[LevelCode]
  ,[Phone]
  ,[PhoneAlt]
  ,[PhoneRegion]
  ,[AddressLine1]
  ,[AddressLine2]
  ,[AddressLine3]
  ,[City]
  ,[State]
  ,[Country]
  ,[Zip]
  ,[BirthDate]
  ,[BirthMonth]
  ,[BirthDay]
  ,[ReportDay]
  ,[CreatedOn]
  ,[UpdatedOn])
SELECT
  CardNumber, -- Id
  LEFT(FirstName,60) AS FirstName,
  LEFT(LastName,60) AS LastName,
  LEFT(FullName,120) AS FullName,
  c.PosId,
  Email,
  c.Status,
  ct.PosId, -- LevelCode from CardType
  Phone,
  AltPhone,
  PhoneRegion,
  LEFT(AddressLine1,100) AS AddressLine1,
  LEFT(AddressLine2,100) AS AddressLine2,
  null, -- Address3
  LEFT(City,80) AS City,
  LEFT(State,80) AS State,
  LEFT(Country,80) AS Country,
  PostalCode,
  BirthDate,
  BirthMonth,
  BirthDay,
  ReportDay,
  c.CreatedOn,
  c.UpdatedOn
FROM OL_Customer c
INNER JOIN OL_CardType ct ON c.CardTypeId = ct.Id
GO

b) Member Purchases SQL

This script should be run for all clients that have previously enabled the Loyalty feature.

Before running the script, ensure that the following tables have correct data:

OL_CardType (particularly PosId)

/*
  Copy OL_Customer to PU_MemberPurchase.
 */

DECLARE @StartOfYear DateTime;
DECLARE @StartOfMonth DateTime;
SET @StartOfYear = DATETIMEFROMPARTS(YEAR(GETUTCDATE()), 1, 1, 0, 0, 0, 0)
SET @StartOfMonth = DATETIMEFROMPARTS(YEAR(GETUTCDATE()), MONTH(GETUTCDATE()), 1, 0, 0, 0, 0)

INSERT INTO [dbo].[PU_MemberPurchase]
  ([Id]
  ,[AmountPaidYtd]
  ,[AmountPaidTotal]
  ,[LastPurchaseOn]
  ,[PointsBalance]
  ,[EnvoyPointsBalance]
  ,[AmountRedeemedMtd]
  ,[AmountRedeemedYtd]
  ,[AmountRedeemedTotal]
  ,[PointsRedeemedMtd]
  ,[PointsRedeemedYtd]
  ,[PointsRedeemedTotal]
  ,PointsEarnedMtd
  ,PointsEarnedYtd
  ,PointsEarnedTotal
  ,[EnvoyAmountRedeemedMtd]
  ,[EnvoyAmountRedeemedYtd]
  ,[EnvoyAmountRedeemedTotal]
  ,[EnvoyPointsRedeemedMtd]
  ,[EnvoyPointsRedeemedYtd]
  ,[EnvoyPointsRedeemedTotal]
  ,EnvoyPointsEarnedMtd
  ,EnvoyPointsEarnedYtd
  ,EnvoyPointsEarnedTotal
  ,[AnniversaryOn]
  ,[CreatedOn]
  ,[UpdatedOn])
SELECT
  c.CardNumber, -- Id
  c.AmountPaidYtd,
  c.AmountPaidTotal,
  c.LastPurchaseOn,

  -- PointsBalance
	(SELECT COALESCE(SUM(t.Points), 0)
	FROM OL_PointTrans t
	WHERE t.CustomerId = c.Id
	AND t.Bucket = 0
	) AS PointsBalance,
  
  -- EnvoyPointsBalance
  (SELECT COALESCE(SUM(t.Points), 0)
	FROM OL_PointTrans t
	WHERE t.CustomerId = c.Id
	AND t.Bucket = 1
	) AS EnvoyPointsBalance,

  -- AmountRedeemedMtd,
	(SELECT COALESCE(SUM(t.Amount), 0)
	FROM OL_PointTrans t
	WHERE t.CustomerId = c.Id
	AND t.Bucket = 0
	AND t.Type IN (0, 12)
	AND t.Date >= @StartOfMonth
	) * -1 AS AmountRedeemedMtd,

  --   AmountRedeemedYtd,
	(SELECT COALESCE(SUM(t.Amount), 0)
	FROM OL_PointTrans t
	WHERE t.CustomerId = c.Id
	AND t.Bucket = 0
	AND t.Type IN (0, 12)
	AND t.Date >= @StartOfYear
	) * -1 AS AmountRedeemedYtd,

  -- AmountRedeemedTotal:
	(SELECT COALESCE(SUM(t.Amount), 0)
	FROM OL_PointTrans t
	WHERE t.CustomerId = c.Id
	AND t.Bucket = 0
	AND t.Type IN (0, 12)
	) * -1 AS AmountRedeemedTotal,

  -- PointsRedeemedMtd,
	(SELECT COALESCE(SUM(t.Points), 0)
	FROM OL_PointTrans t
	WHERE t.CustomerId = c.Id
	AND t.Bucket = 0
	AND t.Type IN (0, 12)
	AND t.Date >= @StartOfMonth
	) * -1 AS PointsRedeemedMtd,

  -- PointsRedeemedYtd,
	(SELECT COALESCE(SUM(t.Points), 0)
	FROM OL_PointTrans t
	WHERE t.CustomerId = c.Id
	AND t.Bucket = 0
	AND t.Type IN (0, 12)
	AND t.Date >= @StartOfYear
	) * -1 AS PointsRedeemedYtd,

  -- PointsRedeemedTotal:
	(SELECT COALESCE(SUM(t.Points), 0)
	FROM OL_PointTrans t
	WHERE t.CustomerId = c.Id
	AND t.Bucket = 0
	AND t.Type IN (0, 12)
	) * -1 AS PointsRedeemedTotal,

  -- PointsEarnedMtd,
	(SELECT COALESCE(SUM(t.Points), 0)
	FROM OL_PointTrans t
	WHERE t.CustomerId = c.Id
	AND t.Bucket = 0
	AND t.Type NOT IN (0, 12)
	AND t.Date >= @StartOfMonth
	) AS PointsEarnedMtd,

  -- PointsEarnedYtd,
	(SELECT COALESCE(SUM(t.Points), 0)
	FROM OL_PointTrans t
	WHERE t.CustomerId = c.Id
	AND t.Bucket = 0
	AND t.Type NOT IN (0, 12)
	AND t.Date >= @StartOfYear
	) AS PointsEarnedYtd,

  -- PointsEarnedTotal:
	(SELECT COALESCE(SUM(t.Points), 0)
	FROM OL_PointTrans t
	WHERE t.CustomerId = c.Id
	AND t.Bucket = 0
	AND t.Type NOT IN (0, 12)
	) AS PointsEarnedTotal,

  -- EnvoyAmountRedeemedMtd,
	(SELECT COALESCE(SUM(t.Amount), 0)
	FROM OL_PointTrans t
	WHERE t.CustomerId = c.Id
	AND t.Bucket = 1
	AND t.Type IN (0, 12)
	AND t.Date >= @StartOfMonth
	) * -1 AS EnvoyAmountRedeemedMtd,

  -- EnvoyAmountRedeemedYtd,
	(SELECT COALESCE(SUM(t.Amount), 0)
	FROM OL_PointTrans t
	WHERE t.CustomerId = c.Id
	AND t.Bucket = 1
	AND t.Type IN (0, 12)
	AND t.Date >= @StartOfYear
	) * -1 AS EnvoyAmountRedeemedYtd,

  -- EnvoyAmountRedeemedTotal:
	(SELECT COALESCE(SUM(t.Amount), 0)
	FROM OL_PointTrans t
	WHERE t.CustomerId = c.Id
	AND t.Bucket = 1
	AND t.Type IN (0, 12)
	) * -1 AS EnvoyAmountRedeemedTotal,

  -- EnvoyPointsRedeemedMtd,
	(SELECT COALESCE(SUM(t.Points), 0)
	FROM OL_PointTrans t
	WHERE t.CustomerId = c.Id
	AND t.Bucket = 1
	AND t.Type IN (0, 12)
	AND t.Date >= @StartOfMonth
	) * -1 AS EnvoyPointsRedeemedMtd,

  -- EnvoyPointsRedeemedYtd,
	(SELECT COALESCE(SUM(t.Points), 0)
	FROM OL_PointTrans t
	WHERE t.CustomerId = c.Id
	AND t.Bucket = 1
	AND t.Type IN (0, 12)
	AND t.Date >= @StartOfYear
	) * -1 AS EnvoyPointsRedeemedYtd,

  -- PointsRedeemedTotal:
	(SELECT COALESCE(SUM(t.Points), 0)
	FROM OL_PointTrans t
	WHERE t.CustomerId = c.Id
	AND t.Bucket = 1
	AND t.Type IN (0, 12)
	) * -1 AS EnvoyPointsRedeemedTotal,

  -- EnvoyPointsEarnedMtd,
	(SELECT COALESCE(SUM(t.Points), 0)
	FROM OL_PointTrans t
	WHERE t.CustomerId = c.Id
	AND t.Bucket = 1
	AND t.Type NOT IN (0, 12)
	AND t.Date >= @StartOfMonth
	) AS EnvoyPointsEarnedMtd,

  -- EnvoyPointsEarnedYtd,
	(SELECT COALESCE(SUM(t.Points), 0)
	FROM OL_PointTrans t
	WHERE t.CustomerId = c.Id
	AND t.Bucket = 1
	AND t.Type NOT IN (0, 12)
	AND t.Date >= @StartOfYear
	) AS EnvoyPointsEarnedYtd,

  -- EnvoyPointsEarnedTotal:
	(SELECT COALESCE(SUM(t.Points), 0)
	FROM OL_PointTrans t
	WHERE t.CustomerId = c.Id
	AND t.Bucket = 1
	AND t.Type NOT IN (0, 12)
	) AS EnvoyPointsEarnedTotal,

  c.AnniversaryOn,
  c.CreatedOn,
  c.UpdatedOn
FROM OL_Customer c
INNER JOIN OL_CardType ct ON c.CardTypeId = ct.Id

Note that the script above recalculates the member’s points-related balances, based on the raw points transaction data contained in OL_PointTrans. You can run the following script to check whether there are any differences between the calculated and previous totals:


/*
 * Detect any rows that don't match point balances.
 */

SELECT
  p.Id,
  p.PointsBalance,
  c.LoyaltyPointsBalance AS PointsBalance_OLD,
  p.EnvoyPointsBalance,
  c.EnvoyPointsBalance AS EnvoyPointsBalance_OLD
FROM OL_Customer c
INNER JOIN PU_MemberPurchase p ON c.CardNumber = p.Id
WHERE c.AmountRedeemedTotal <> p.AmountRedeemedTotal
OR c.EnvoyPointsBalance <> p.EnvoyPointsBalance
OR c.LoyaltyPointsBalance <> p.PointsBalance
;

/*
 * Detect any rows that don't match redeem amounts.
 */

SELECT
  p.Id,
  p.AmountRedeemedTotal,
  c.AmountRedeemedTotal AS AmountRedeemedTotal_OLD,
  p.AmountRedeemedMtd,
  c.AmountRedeemedMtd AS AmountRedeemedMtd_OLD,
  p.AmountRedeemedYtd,
  c.AmountRedeemedYtd AS AmountRedeemedYtd_OLD
FROM OL_Customer c
INNER JOIN PU_MemberPurchase p ON c.CardNumber = p.Id
WHERE c.AmountRedeemedTotal <> p.AmountRedeemedTotal
OR c.AmountRedeemedYtd <> p.AmountRedeemedYtd
OR c.AmountRedeemedMtd <> p.AmountRedeemedMtd
;

/*
 * Detect any rows that don't match envoy redeem amounts.
 */

SELECT
  p.Id,
  p.EnvoyAmountRedeemedTotal,
  c.EnvoyAmountRedeemedTotal AS EnvoyAmountRedeemedTotal_OLD,
  p.EnvoyAmountRedeemedMtd,
  c.EnvoyAmountRedeemedMtd AS EnvoyAmountRedeemedMtd_OLD,
  p.EnvoyAmountRedeemedYtd,
  c.EnvoyAmountRedeemedYtd AS EnvoyAmountRedeemedYtd_OLD
FROM OL_Customer c
INNER JOIN PU_MemberPurchase p ON c.CardNumber = p.Id
WHERE c.EnvoyAmountRedeemedTotal <> p.EnvoyAmountRedeemedTotal
OR c.EnvoyAmountRedeemedYtd <> p.EnvoyAmountRedeemedYtd
OR c.EnvoyAmountRedeemedMtd <> p.EnvoyAmountRedeemedMtd
;

If any of the statements above return records, you will likely need to manually adjust balances and totals for each affected member.

Any adjustments are discretionary. You may need to consult the client to determine how to best correct the member records.

c) Purchases SQL

This script should be run for all clients that have previously enabled the Loyalty and Purchases features.

Before running the script, ensure that the following tables have correct data:

OL_Store (particularly PosId)

/*
 * Copy OL_Purchase to PU_Purchase
 */

SET IDENTITY_INSERT [dbo].[PU_Purchase] ON

INSERT INTO [dbo].[PU_Purchase]
  (Id
  ,[TransactionId]
  ,[PurchasedOn]
  ,[ReportDay]
  ,[LocationId]
  ,[CustomerId]
  ,[MemberId]
  ,[PayMethod]
  ,[Channel]
  ,[OrderType]
  ,[PayAmount]
  ,[DiscountAmount]
  ,[RefundAmount]
  ,[SubTotal]
  ,[TaxAmount]
  ,[LoyaltyPoints]
  ,[EnvoyPoints]
  ,[CreatedOn]
  ,[UpdatedOn])
SELECT
  p.Id,
  p.TransactionId,
  p.PurchasedOn,
  p.ReportDay,
  s.PosId, -- LocationId
  p.CustomerIdStr, -- CustomerId
  c.CardNumber, -- MemberId
  p.PayMethod,
  p.Channel,
  p.OrderType,
  p.PayAmount,
  p.DiscountAmount,
  p.RefundAmount,
  p.SubTotal,
  p.TaxAmount,
  p.LoyaltyPoints,
  p.EnvoyPoints,  -- TODO ensure that this is updated in EnvoyPurchaseHandler !
  p.CreatedOn,
  p.UpdatedOn
FROM
  OL_Purchase p
  INNER JOIN OL_Store s ON (p.StoreId = s.Id)
  LEFT JOIN OL_Customer c ON (p.CustomerId = c.Id)

SET IDENTITY_INSERT [dbo].[PU_Purchase] OFF

/*
 * Copy OL_PurchaseLineItem to PU_PurchaseLine
 */

SET IDENTITY_INSERT [dbo].[PU_PurchaseLine] ON

INSERT INTO [dbo].[PU_PurchaseLine]
  ([Id]
  ,[PurchaseId]
  ,[LineNumber]
  ,[IsRefund]
  ,[RefundTransactionId]
  ,[RefundLineNumber]
  ,[Sku]
  ,[Name]
  ,[Vendor]
  ,[ProductPriceClass]
  ,[UnitCost]
  ,[UnitPrice]
  ,[ExtPrice]
  ,[ItemDiscountAmount]
  ,[SaleDiscountAmount]
  ,[Quantity]
  ,[UOM]
  ,[LoyaltyPoints])
SELECT
  Id,
  PurchaseId,
  LineNumber,
  IsRefund,
  RefundTransactionId,
  RefundLineNumber,
  Sku,
  Name,
  Vendor,
  ProductPriceClass,
  UnitCost,
  UnitPrice,
  ExtPrice,
  ItemDiscountAmount,
  SaleDiscountAmount,
  Quantity,
  UOM,
  LoyaltyPoints
FROM
  OL_PurchaseLineItem

SET IDENTITY_INSERT [dbo].[PU_PurchaseLine] OFF

GO

d) Gift Cards SQL

This script should be run for all clients that have previously enabled the Loyalty and Gift Cards features.

/*
 * Copy OL_GiftCard to GC_GiftCard
 */
 
SET IDENTITY_INSERT [dbo].[GC_GiftCard] ON

INSERT INTO [dbo].[GC_GiftCard]
  ([Id]
  ,[Status]
  ,[Balance]
  ,[MemberId]
  ,[LastUsedOn]
  ,[CreatedOn]
  ,[UpdatedOn])
SELECT
  g.Id,
  g.Status,
  g.Balance,
  c.CardNumber,
  g.LastUsedOn,
  g.CreatedOn,
  g.UpdatedOn
FROM OL_GiftCard g
LEFT JOIN OL_Customer c ON g.CustomerId = c.Id

SET IDENTITY_INSERT [dbo].[GC_GiftCard] OFF

/*
 * Copy OL_GiftCardCode to GC_GiftCardCode
 */

SET IDENTITY_INSERT [dbo].[GC_GiftCardCode] ON

INSERT INTO [dbo].[GC_GiftCardCode]
  ([Id]
  ,[GiftCardId]
  ,[CodeType]
  ,[Code]
  ,[Pin]
  ,[ExpiresOn]
  ,[CreatedOn]
  ,[UpdatedOn])
SELECT
  Id,
  GiftCardId,
  CodeType,
  Code,
  LEFT(Pin,40) AS Pin,
  ExpiresOn,
  CreatedOn,
  UpdatedOn
FROM OL_GiftCardCode

SET IDENTITY_INSERT [dbo].[GC_GiftCardCode] OFF

/*
 * Copy OL_GiftCardTrans to GC_GiftCardTrans
 */

SET IDENTITY_INSERT [dbo].[GC_GiftCardTrans] ON

INSERT INTO [dbo].[GC_GiftCardTrans]
  ([Id]
  ,[GiftCardId]
  ,[Type]
  ,[Status]
  ,[AuthCode]
  ,[Amount]
  ,[Date]
  ,[ReportDay]
  ,[SourceId]
  ,[RefNo]
  ,[CreatedOn]
  ,[UpdatedOn])
SELECT
  Id,
  GiftCardId,
  Type,
  Status,
  AuthCode,
  Amount,
  Date,
  ReportDay,
  LEFT(SourceId,100) AS SourceId,
  LEFT(RefNo,100) AS RefNo,
  CreatedOn,
  UpdatedOn
FROM OL_GiftCardTrans

SET IDENTITY_INSERT [dbo].[GC_GiftCardTrans] OFF

e) Loyalty Points SQL

This script should be run for all clients that have previously enabled the Loyalty and Loyalty Points features.

Before running the script, ensure the following:

OL_Store rows have valid PosId values – these should be the location code for each store.
Note any rules that are defined against location groups. These must be manually edited in the POS UI after the script is run, as the script does not copy the location group settings.

/*
 * Copy OL_PointTrans to LY_PointTrans.
 */
 
SET IDENTITY_INSERT [dbo].[LY_PointTrans] ON

INSERT INTO [dbo].[LY_PointTrans]
  ([Id]
  ,[MemberId]
  ,[Date]
  ,[ReportDay]
  ,[Bucket]
  ,[Type]
  ,[Points]
  ,[Amount]
  ,[SourceId]
  ,[SourceRef]
  ,[Notes]
  ,[ClerkCode]
  ,[CreatedOn]
  ,[UpdatedOn])
SELECT
  t.Id,
  c.CardNumber, -- MemberId
  t.Date,
  t.ReportDay,
  t.Bucket,
  t.Type,
  t.Points,
  t.Amount,
  t.SourceId,
  null, -- SourceRef
  t.Notes,
  null, -- ClerkCode
  t.CreatedOn,
  t.UpdatedOn
FROM OL_PointTrans t
INNER JOIN OL_Customer c ON t.CustomerId = c.Id

SET IDENTITY_INSERT [dbo].[LY_PointTrans] OFF

/*
 * Copy OL_ProductPointRule to LY_ProductPointRule
 */

SET IDENTITY_INSERT [dbo].[LY_ProductPointRule] ON

INSERT INTO [dbo].[LY_ProductPointRule]
  ([Id]
  ,[Name]
  ,[Priority]
  ,[Amount]
  ,[Action]
  ,[Notes]
  ,[LocationCode]
  ,[LocationGroupCode]
  ,[OrderType]
  ,[Vendor]
  ,[ProductPriceClass]
  ,[ItemSku]
  ,[BreakQuantity]
  ,[UOM]
  ,[StartDate]
  ,[EndDate]
  ,[Status]
  ,[CreatedOn]
  ,[UpdatedOn])
SELECT
  r.Id,
  r.Name,
  r.Priority,
  r.Amount,
  r.Action,
  r.Notes,
  s.PosId, -- LocationCode
  null, -- LocationGroupCode <<NOTE must edit in UI >>
  r.OrderType,
  r.Vendor,
  r.ProductPriceClass,
  r.ItemSku,
  r.BreakQuantity,
  r.UOM,
  r.StartDate,
  r.EndDate,
  r.Status,
  r.CreatedOn,
  r.UpdatedOn
FROM OL_ProductPointRule r
LEFT JOIN OL_Store s ON r.StoreId = s.Id
WHERE r.Id <> 1 -- exclude the default rule

SET IDENTITY_INSERT [dbo].[LY_ProductPointRule] OFF

Note the the WHERE clause in the last query. Both POS and OnLoyalty create a default product point rule – which typically should have an ID of 1 – which the query should ignore. If the IDs of these default OnLoyalty or POS records are anything other than 1, you will need to manually adjust the query.

f) Delete Tables SQL

This script will delete all OnLoyalty tables. It should be run only after you have run the appropriate SQL scripts above without error.

Warning: this script will delete all OnLoyalty data. Ensure that you have copied all data using the scripts above before running this script.

/*
 * Drop all OnLoyalty tables.
 * The statement order respects foreign key reference dependencies
 * between the tables.
 */

IF OBJECT_ID ('dbo.OL_CouponRedemption', 'table') IS NOT NULL
   DROP TABLE dbo.OL_CouponRedemption
GO

IF OBJECT_ID ('dbo.OL_PointTrans', 'table') IS NOT NULL
   DROP TABLE dbo.OL_PointTrans
GO

IF OBJECT_ID ('dbo.OL_PurchaseLineItem', 'table') IS NOT NULL
   DROP TABLE dbo.OL_PurchaseLineItem
GO

IF OBJECT_ID ('dbo.OL_Purchase', 'table') IS NOT NULL
   DROP TABLE dbo.OL_Purchase
GO

IF OBJECT_ID ('dbo.OL_PosCouponBalance', 'table') IS NOT NULL
   DROP TABLE dbo.OL_PosCouponBalance
GO

IF OBJECT_ID ('dbo.OL_CampaignStore', 'table') IS NOT NULL
   DROP TABLE dbo.OL_CampaignStore
GO

IF OBJECT_ID ('dbo.OL_Campaign', 'table') IS NOT NULL
   DROP TABLE dbo.OL_Campaign
GO

IF OBJECT_ID ('dbo.OL_CouponCustomer', 'table') IS NOT NULL
   DROP TABLE dbo.OL_CouponCustomer
GO

IF OBJECT_ID ('dbo.OL_CouponIssue', 'table') IS NOT NULL
   DROP TABLE dbo.OL_CouponIssue
GO

IF OBJECT_ID ('dbo.OL_CouponStore', 'table') IS NOT NULL
   DROP TABLE dbo.OL_CouponStore
GO

IF OBJECT_ID ('dbo.OL_Coupon', 'table') IS NOT NULL
   DROP TABLE dbo.OL_Coupon
GO

IF OBJECT_ID ('dbo.OL_EnvoyInviteCustomer', 'table') IS NOT NULL
   DROP TABLE dbo.OL_EnvoyInviteCustomer
GO

IF OBJECT_ID ('dbo.OL_EnvoyInvite', 'table') IS NOT NULL
   DROP TABLE dbo.OL_EnvoyInvite
GO

IF OBJECT_ID ('dbo.OL_EnvoySetup', 'table') IS NOT NULL
   DROP TABLE dbo.OL_EnvoySetup
GO

IF OBJECT_ID ('dbo.OL_CouponSetupStore', 'table') IS NOT NULL
   DROP TABLE dbo.OL_CouponSetupStore
GO

IF OBJECT_ID ('dbo.OL_CouponSetup', 'table') IS NOT NULL
   DROP TABLE dbo.OL_CouponSetup
GO

IF OBJECT_ID ('dbo.OL_GiftCardTrans', 'table') IS NOT NULL
   DROP TABLE dbo.OL_GiftCardTrans
GO

IF OBJECT_ID ('dbo.OL_GiftCardCode', 'table') IS NOT NULL
   DROP TABLE dbo.OL_GiftCardCode
GO

IF OBJECT_ID ('dbo.OL_GiftCard', 'table') IS NOT NULL
   DROP TABLE dbo.OL_GiftCard
GO

IF OBJECT_ID ('dbo.OL_EventCategory', 'table') IS NOT NULL
   DROP TABLE dbo.OL_EventCategory
GO

IF OBJECT_ID ('dbo.OL_Event', 'table') IS NOT NULL
   DROP TABLE dbo.OL_Event
GO

IF OBJECT_ID ('dbo.OL_AppAction', 'table') IS NOT NULL
   DROP TABLE dbo.OL_AppAction
GO

IF OBJECT_ID ('dbo.OL_PosCustomerBalance', 'table') IS NOT NULL
   DROP TABLE dbo.OL_PosCustomerBalance
GO

IF OBJECT_ID ('dbo.OL_PosConfig', 'table') IS NOT NULL
   DROP TABLE dbo.OL_PosConfig
GO

IF OBJECT_ID ('dbo.OL_AppConfig', 'table') IS NOT NULL
   DROP TABLE dbo.OL_AppConfig
GO

IF OBJECT_ID ('dbo.OL_OperatorRole', 'table') IS NOT NULL
   DROP TABLE dbo.OL_OperatorRole
GO

IF OBJECT_ID ('dbo.OL_OperatorDetail', 'table') IS NOT NULL
   DROP TABLE dbo.OL_OperatorDetail
GO

IF OBJECT_ID ('dbo.OL_ApiKey', 'table') IS NOT NULL
   DROP TABLE dbo.OL_ApiKey
GO

IF OBJECT_ID ('dbo.OL_ProductPointRule', 'table') IS NOT NULL
   DROP TABLE dbo.OL_ProductPointRule
GO

IF OBJECT_ID ('dbo.OL_Operator', 'table') IS NOT NULL
   DROP TABLE dbo.OL_Operator
GO

IF OBJECT_ID ('dbo.OL_StoreCategory', 'table') IS NOT NULL
   DROP TABLE dbo.OL_StoreCategory
GO

IF OBJECT_ID ('dbo.OL_Store', 'table') IS NOT NULL
   DROP TABLE dbo.OL_Store
GO

IF OBJECT_ID ('dbo.OL_Device', 'table') IS NOT NULL
   DROP TABLE dbo.OL_Device
GO

IF OBJECT_ID ('dbo.OL_DeviceCheck', 'table') IS NOT NULL
   DROP TABLE dbo.OL_DeviceCheck
GO

IF OBJECT_ID ('dbo.OL_AuthContactHistory', 'table') IS NOT NULL
   DROP TABLE dbo.OL_AuthContactHistory
GO

IF OBJECT_ID ('dbo.OL_AuthContact', 'table') IS NOT NULL
   DROP TABLE dbo.OL_AuthContact
GO

IF OBJECT_ID ('dbo.OL_CustomerPreferenceRegion', 'table') IS NOT NULL
   DROP TABLE dbo.OL_CustomerPreferenceRegion
GO

IF OBJECT_ID ('dbo.OL_CustomerPreference', 'table') IS NOT NULL
   DROP TABLE dbo.OL_CustomerPreference
GO

IF OBJECT_ID ('dbo.OL_Customer', 'table') IS NOT NULL
   DROP TABLE dbo.OL_Customer
GO

IF OBJECT_ID ('dbo.OL_ProductFeature', 'table') IS NOT NULL
   DROP TABLE dbo.OL_ProductFeature
GO

IF OBJECT_ID ('dbo.OL_CardType', 'table') IS NOT NULL
   DROP TABLE dbo.OL_CardType
GO

IF OBJECT_ID ('dbo.OL_QrCode', 'table') IS NOT NULL
   DROP TABLE dbo.OL_QrCode
GO

IF OBJECT_ID ('dbo.OL_StoreGroup', 'table') IS NOT NULL
   DROP TABLE dbo.OL_StoreGroup
GO

IF OBJECT_ID ('dbo.OL_Category', 'table') IS NOT NULL
   DROP TABLE dbo.OL_Category
GO

IF OBJECT_ID ('dbo.OL_Region', 'table') IS NOT NULL
   DROP TABLE dbo.OL_Region
GO

IF OBJECT_ID ('dbo.OL_StateLookup', 'table') IS NOT NULL
   DROP TABLE dbo.OL_StateLookup
GO

IF OBJECT_ID ('dbo.OL_Country', 'table') IS NOT NULL
   DROP TABLE dbo.OL_Country
GO

IF OBJECT_ID ('dbo.OL_Image', 'table') IS NOT NULL
   DROP TABLE dbo.OL_Image
GO

Retail Solutions

Warehouse Solutions

Ecommerce-Acumatica Connectors

Acumatica extensions

Other Solutions

OnLoyalty Migration (5644)

OnLoyalty Migration (5644)

a) Members SQL

b) Member Purchases SQL

c) Purchases SQL

d) Gift Cards SQL

e) Loyalty Points SQL

f) Delete Tables SQL