Skip to main content

Enrich to DDA Database

Stage 1: DB migration

  1. restore enrich system as "Enrich_DB" at your machine

  2. Create ziipos retail DB as "ZiiPOS_DB" at your machine

  3. Go do ZiiPOS Retail Classic, Systems --> Warehouse Location Seetup --> and create a new warehouse "0002"

  4. run following sql

Alter Table [Enrich_DB].[dbo].[StockItems] Add Barcode nvarchar(30) Null;
Alter Table [Enrich_DB].[dbo].[StockItems] Add Barcode1 nvarchar(30) Null;
Alter Table [Enrich_DB].[dbo].[StockItems] Add Barcode2 nvarchar(30) Null;
Alter Table [Enrich_DB].[dbo].[StockItems] Add Barcode3 nvarchar(30) Null;
Alter Table [Enrich_DB].[dbo].[StockItems] Add Barcode4 nvarchar(30) Null;
Alter Table [Enrich_DB].[dbo].[StockItems] Add Barcode5 nvarchar(30) Null;
GO
UPDATE [Enrich_DB].[dbo].[StockItems] SET Barcode = (SELECT Barcode FROM [Enrich_DB].[dbo].[Barcodes] WHERE StockItems.StockId = Barcodes.StockId);
UPDATE [Enrich_DB].[dbo].[StockItems] SET Barcode1 = (SELECT Barcode1 FROM [Enrich_DB].[dbo].[Barcodes] WHERE StockItems.StockId = Barcodes.StockId);
UPDATE [Enrich_DB].[dbo].[StockItems] SET Barcode2 = (SELECT Barcode2 FROM [Enrich_DB].[dbo].[Barcodes] WHERE StockItems.StockId = Barcodes.StockId);
UPDATE [Enrich_DB].[dbo].[StockItems] SET Barcode3 = (SELECT Barcode3 FROM [Enrich_DB].[dbo].[Barcodes] WHERE StockItems.StockId = Barcodes.StockId);
UPDATE [Enrich_DB].[dbo].[StockItems] SET Barcode4 = (SELECT Barcode4 FROM [Enrich_DB].[dbo].[Barcodes] WHERE StockItems.StockId = Barcodes.StockId);
UPDATE [Enrich_DB].[dbo].[StockItems] SET Barcode5 = (SELECT Barcode5 FROM [Enrich_DB].[dbo].[Barcodes] WHERE StockItems.StockId = Barcodes.StockId);
GO

-- 迁移 Category
INSERT INTO [ZIIPOS_DB].[dbo].[Category]
([Category],[ShowOnTouchPanel],[ImageLoadPath],[ShowImage],[ButtonColor],[FontName],[FontColor],[FontSize],[FontBold],[FontItalic],[FontUnderline],[FontStrikeout],[SuggestedMarkupRate],[CateID],[MetcashCategoryId],[IsGroup],[OnlineSales],[LastUpdatedAt])
SELECT [Category],[ShowOnTouchPanel],[ImageLoadPath],[ShowImage],[ButtonColor],[FontName],[FontColor],[FontSize],[FontBold],[FontItalic],[FontUnderline],[FontStrikeout],0,[CateID],[MetcashCategoryId],0,[OnlineOrderCategory],GETDATE()
  FROM [Enrich_DB].[dbo].[Category];
GO
-- 迁移Product数据
-- 注意:修正库存字段 StockQty, StockValue
INSERT INTO [ZIIPOS_DB].[dbo].[Product] 
([ProductCode], [Category], [MetCashCode], [Description], [Description1], [Description2], [Description3], [SubDescription], [SubDescription1], [SubDescription2], [SubDescription3], [SubDescription4], [SubDescription5], [SalesPrice1], [SalesPrice2], [SalesPrice3], [SalesPrice4], [SalesPrice5], [SalesPrice6], [SpecialPrice], [SpecialPrice1], [SpecialPrice2], [SpecialPrice3], [SpecialPrice4], [SpecialPrice5], [SpecialDate1], [SpecialDate2], [SpecialDate3], [SpecialDate4], [SpecialDate5], [SpecialDate6], [SpecialDate7], [SpecialDate8], [SpecialDate9], [SpecialDate10], [SpecialDate11], [SpecialDate12], [DeductStockQty], [DeductStockQty1], [DeductStockQty2], [DeductStockQty3], [DeductStockQty4], [DeductStockQty5], [MultiplePrice], [ManualSelectPrice], [DefaultSalesPrice], [Live], [StockControl], [GSTStatus], [LastOrderPrice], [AllowDiscount], [Scalable], [OpenPrice], [LimitStockQty], [GSTRate], [Notes], [Memorandum], [ImageLoadPath], [ShowImage], [ButtonColor], [FontName], [FontColor], [FontSize], [FontBold], [FontItalic], [FontUnderline], [FontStrikeout], [Measurement], [MeasureQty], [DefaultSalesQty], [Location], [SpecialPriceLinks], [ModifyDate], [ItemDiscountRate], [ShowOnSelectForm], [DefaultSupplier], [MaxStockQty], [SpecialPriceWithoutTerm], [LastEditor], [DepartmentCode], [SpecialKind], [PackMeasurement], [UnitMeasurement], [Metric], [TareWeight], [OnlyPrice1CanBeDiscount], [BestBefore], [CalcSalesCostKind], [PrintJobListItem], [OnlineOrderItem], [ExpectedCostIncludeGST], [StockOnOrder], [StockCommitted], [RewardPointsMode], [StockQty0001], [StockValue0001], [StockQty0002], [StockValue0002], [BarCode], [Barcode1], [Barcode2], [Barcode3], [Barcode4], [Barcode5], [PackHeight], [PackWidth], [PackLength], [Weight], [WholesalePrice], [WholesalePrice1], [WholesalePrice2], [WholesalePrice3], [WholesalePrice4], [WholesalePrice5], [FamilyCode], [OnlinePrice1], [OnlinePrice2], [OnlinePrice3], [OnlinePrice4], [OnlinePrice5], [OnlinePrice6], [ConsolidateMode]) 
SELECT [StockId], [Category], [MetCashCode], [Description], [Description1], [Description2], [Description3], [SubDescription], [SubDescription1], [SubDescription2], [SubDescription3], [SubDescription4], [SubDescription5], [SalesPrice1], [SalesPrice2], [SalesPrice3], [SalesPrice4], [SalesPrice5], [SalesPrice6], [SpecialPrice], [SpecialPrice1], [SpecialPrice2], [SpecialPrice3], [SpecialPrice4], [SpecialPrice5], [SpecialDate1], [SpecialDate2], [SpecialDate3], [SpecialDate4], [SpecialDate5], [SpecialDate6], [SpecialDate7], [SpecialDate8], [SpecialDate9], [SpecialDate10], [SpecialDate11], [SpecialDate12], [DeductStockQty], [DeductStockQty1], [DeductStockQty2], [DeductStockQty3], [DeductStockQty4], [DeductStockQty5], [MultiplePrice], [ManualSelectPrice], [DefaultSalesPrice], [Live], [StockControl], [PriceIncludeGST], [LastOrderPrice], [AllowDiscount], [Scalable], [OpenPrice], [LimitStockQty], [GSTRate], [Notes], [Memorandum], [ImageLoadPath], [ShowImage], [ButtonColor], [FontName], [FontColor], [FontSize], [FontBold], [FontItalic], [FontUnderline], [FontStrikeout], [Measurement], [InnerUnits], [DefaultSalesQty], [ShelfNumber], [SpecialPriceLinks], [ModifyDate], [ItemDiscountRate], [ShowOnSelectForm], [DefaultVendor], [MaxStockQty], [SpecialPriceWithoutTerm], [LastEditor], [DepartmentCode], [SpecialKind], [PackMeasurement], [UnitMeasurement], [Metric], [TareWeight], [OnlyPrice1CanBeDiscount], [BestBefore], [CalcSalesCostKind], [PrintJobListItem], [OnlineOrderItem], [ExpectedCostIncludeGST], [StockOrdered], [StockWithhold], [RewardPointsItem], [StockQty0001], [StockValue0001], [StockQty0002], [StockValue0002], [BarCode], [Barcode1], [Barcode2], [Barcode3], [Barcode4], [Barcode5], 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, [FamilyCode], 0, 0, 0, 0, 0, 0, 0 
  FROM [Enrich_DB].[dbo].[StockItems];
GO
-- 补偿ProductExtend数据
-- 注意:修正库存字段 StockQty, StockValue
INSERT INTO [ZIIPOS_DB].[dbo].[ProductExtend] 
([ProductCode] ,[StockQty] ,[StockValue] ,[LastStockUpdatedAt] ,[SalesPriceMargin1] ,[SalesPriceMargin2] ,[SalesPriceMargin3] ,[SalesPriceMargin4] ,[SalesPriceMargin5] ,[SalesPriceMargin6] ,[WholesalePriceMargin1] ,[WholesalePriceMargin2] ,[WholesalePriceMargin3] ,[WholesalePriceMargin4] ,[WholesalePriceMargin5] ,[WholesalePriceMargin6] ,[OnlineStockLimit] ,[OnlineStockQty])
SELECT [ProductCode], [StockQty0001] + [StockQty0002], [StockValue0001] + [StockValue0002], GETDATE(), 0, 0, 0, 0 ,0 ,0, 0, 0, 0, 0 ,0 ,0, 0, 0 
  FROM [ZIIPOS_DB].[dbo].[Product];
GO
-- 迁移Product Family
INSERT INTO [ZIIPOS_DB].[dbo].[ProductFamily] 
([FamilyCode], [FamilyName], [SalesQty], [SpecialPrice], [AlwaysSpecialFlag], [MixMatchPriceKind], [DiscountItemCode], [DiscountDescription])
SELECT [FamilyCode], [FamilyName], [SalesQty], [SpecialPrice], [AlwaysSpecialFlag], [MixMatchPriceKind], [DiscountItemCode], [DiscountDescription] 
FROM [Enrich_DB].[dbo].[StockFamily];
GO
-- 迁移Consolidate Price
INSERT INTO [ZIIPOS_DB].[dbo].[ConsolidatePriceTable] 
([ProductCode], [Qty], [Price])
SELECT [StockId], [Qty], [Price] 
FROM [Enrich_DB].[dbo].[ConsolidateSalesPrice];
GO
-- 迁移 Supplier
INSERT INTO [ZIIPOS_DB].[dbo].[Supplier]
([SupplierCode],[SupplierName],[Contact],[Telephone],[Fax],[Mobile],[EMail],[CreditLimit],[CreditTerm],[Address],[Suburb],[State],[PostCode],[Country],[Live],[ABN],[Notes],[SpecialSupplier])
  SELECT [VendorId],[VendorName],[Contact],[Telephone],[Fax],[Mobile],[EMail],0,0,[Address],[Suburb],[State],[PostCode],[Country],[Live],[ABN],[Notes],0
  FROM [Enrich_DB].[dbo].[Vendor];
GO-- 迁移 AccessMenu
delete from [ZIIPOS_DB].[dbo].[AccessMenu]

INSERT INTO [ZIIPOS_DB].[dbo].[AccessMenu] ([AccountPaymentsReportMenu],[AccountReceiptsReportMenu],[AccountsPayableMenu],[AccountsReceivableMenu],[AllowCopyDataForStockTransferOut],[AttendanceReportMenu],[AuthorisedChangeCustomerCode],[AuthorisedDoItemGroupSetting],[BackOrderReportMenu],[BAStatementsMenu],[BrueBoxAdmin],[CancelSales],[CashDeclarationListMenu],[CashFloatMenu],[CashFloatReportMenu],[CategorySetupMenu],[ChangeDateAuthorised],[ChangeInvoicePrice],[ChangeProductOpenPriceStatus],[ChangeWarrantyStatus],[ChartOfAccountMenu],[CheckDailyReportAuthorise],[CheckPrice1],[CheckPrice2],[CheckPrice3],[CheckPrice4],[CheckPrice5],[CheckPrice6],[ClosePOSWindowAuthorise],[CompanyProfileSetupMenu],[CreateNewProductItems],[CreditNotesInquiryMenu],[CreditNotesMenu],[CreditNotesPaymentList],[CreditorsReportMenu],[CrossStoreSalesRefundReportMenu],[CustomerInquiryMenu],[CustomerSetupMenu],[DailySalesAnalysisReportMenu],[DatabaseBackupMenu],[DatabaseRestoreMenu],[DebitNotesInquiryMenu],[DebitNotesMenu],[DebitNotesPaymentList],[DebtorsReportMenu],[DiscountAuthorise],[DiscountReportMenu],[DispatchActivityStatement],[EditAccountPayment],[EditAssemblyStatus],[EditDispatchStatus],[EditInternalOrder],[EditInvoiceAuthorised],[EditProductItemSettings],[EditSalesPrice],[EditServiceStatus],[EditStockReceive],[EditStockTransferOut],[EFTPOSUtilityMenu],[ElectronicSettingsMenu],[EndOfDayReportMenu],[ExpensesDataEntryMenu],[ExpensesPaymentReportMenu],[ExpensesSummaryReportMenu],[InquiryLayBySalesReportMenu],[InquiryOrderProcessMenu],[InquirySalesRelatedReportDays],[InquirySerialNumberMenu],[InquiryWarrantyBackFromSupplierHistoryMenu],[InquiryWarrantyReceiveHistoryMenu],[InquiryWarrantyReturnToCustomerHistoryMenu],[InquiryWarrantySendToSupplierHistoryMenu],[InternalStockTransferInquiryMenu],[InternalStockTransferMenu],[InvoiceInquiryMenu],[InvoiceLabelSetup],[InvoiceMenu],[IssureCustomerCredit],[LayBySalesAuthorise],[ManuallyWeightItemList],[MetcashWeeklyUpdate],[MinusSalesAuthorise],[OnlyViewUnpaidInvoice],[OpenCashDrawerMenu],[OrderProcessReportMenu],[OrderProcessStatusMenu],[PaymentMethodsSetupMenu],[PointOfSalesMenu],[PrintEndOfDayReport],[PrintReportAuthorised],[ProductInquiryMenu],[PurchaseOrderInquiryMenu],[PurchaseOrderMenu],[PurchaseReportMenu],[PurchaseSummaryReportMenu],[QuotationInquiryMenu],[QuotationMenu],[ReprintInvoice],[SalesReportMenu],[SalesStatisticsMenu],[SalesSummaryReportMenu],[ScaleLabelGenerationMenu],[SecureCode],[SerialNumberStatisticsMenu],[SerialNumberStockInMenu],[SerialNumberStockOutMenu],[ShelfLabelSetup],[SpecialPriceItemsListMenu],[StaffName],[StockAdjustmentInquiryMenu],[StockAdjustmentMenu],[StockAdjustmentReportMenu],[StockAnalysisReportMenu],[StockDamageAndWasteHistoryInquiryMenu],[StockDamageAndWasteMenu],[StockDamageAndWasteReportMenu],[StockOnOrderReportMenu],[StockOutToWarrantyMenu],[StockOutToWarrantyReportMenu],[StockReceiveInquiryMenu],[StockReceiveMenu],[StockReportMenu],[StockTakeMenu],[StockTakeReportMenu],[StockTransferInMenu],[StockTransferInquiryMenu],[StockTransferOutMenu],[Supervisor],[SupplierInquiryMenu],[SupplierSetupMenu],[SystemConfiguration],[TransferReportMenu],[TransferSummaryReportMenu],[UploadMemberPointsToWebServerMenu],[UserGroupSetupAuthorized],[UserManagerMenu],[ViewCost],[ViewSalesProfit],[VoidSalesItem],[VoucherDiscountAllowed],[WarehouseSetupMenu],[WarrantyBackFromSupplierMenu],[WarrantyBackToStockMenu],[WarrantyBackToStockReportMenu],[WarrantyControlMenu],[WarrantyReceiveFromCustomerMenu],[WarrantyReceiveOutstandingReportMenu],[WarrantyReturnToCustomerMenu],[WarrantySendToSupplierMenu],[WarrantySentOutstandingReportMenu],[WarrantyStockBalanceReportMenu],[WeeklySalesAnalysisReportMenu],[WeeklySalesGPAnalysisReportMenu])

SELECT [AccountPaymentsReport],[AccountReceiptsReport],[AccountsPayable],[AccountsReceivable],[AllowCopyDataForStockTransferOut],[AttendanceReport],[AuthorisedDoItemGroupSetting],[AuthorisedChangeCustomerId],1,0,[BrueBoxAdmin],[CancelSales],[CashDeclarationList],[CashFloat],[CashFloatReport],[CategorySetup],[ChangeDateAuthorised],[ChangeInvoicePrice],[ChangeProductOpenPriceStatus],0,[ChartOfAccount],[CheckDailyReportAuthorise],1,1,1,1,1,1,[ClosePOSWindowAuthorise],[GlobalSettings],[CreateNewStockItems],[CreditNotesInquiry],[CreditNotes],[CreditNotesPaymentList],[CreditorsReport],[CrossStoreSalesRefundReport],[CustomerInquiry],[CustomerSetup],[DailySalesAnalysisReport],[DatabaseBackup],[DatabaseRestore],[DebitNotesInquiry],[DebitNotes],[DebitNotesPaymentList],[DebtorsReport],[DiscountAuthorise],[DiscountReport],[DispatchActivityStatement],[EditAccountPayment],[EditAssemblyStatus],[EditDispatchStatus],[EditInternalOrder],[EditInvoiceAuthorised],[EditStockItemSettings],[EditSalesPrice],[EditServiceStatus],[EditStockReceive],0,[EFTPOSUtility],[ElectronicSettings],[EndOfDayReport],[ExpensesDataEntry],[ExpensesPaymentReport],[ExpensesSummaryReport],[InquiryLayBySalesReport],[InquiryOrderProcess],[InquirySalesRelatedReportDays],0,0,0,0,0,[InternalStockTransfer],0,[InvoiceInquiry],[InvoiceLabelSetup],[Invoice],[IssureCustomerCredit],0,[ManuallyWeightItemList],[MetcashWeeklyUpdate],[MinusSalesAuthorise],[OnlyViewUnpaidInvoice],[OpenCashDrawer],[OrderProcessReport],[OrderProcessStatus],[PaymentMethodsSetup],[PointOfSales],[PrintEndOfDayReport],[PrintReportAuthorised],[StockItemInquiry],[PurchaseOrderInquiry],[PurchaseOrder],[PurchaseReport],[PurchaseSummaryReport],[CustomerOrderInquiry],[CustomerOrder],[ReprintInvoice],[SalesReport],[SalesStatistics],[SalesSummaryReport],[ScaleLabelGeneration],[SecureCode],0,0,0,[ShelfLabelSetup],[SpecialPriceItemsList],[StaffName],[StockAdjustmentInquiry],[StockAdjustment],[StockAdjustmentReport],[StockAnalysisReport],[StockDamageAndWasteHistoryInquiry],[StockDamageAndWaste],[StockDamageAndWasteReport],[StockOnOrderReport],0,0,[StockReceiveInquiry],[StockReceive],[StockReport],[StockTake],[StockTakeReport],[StockTransferIn],[StockTransferInquiry],[StockTransferOut],[Administrator],[VendorInquiry],[VendorSetup],[SystemConfiguration],[TransferReport],[TransferSummaryReport],[UploadMemberPointsToWebServer],[UserGroupSetupAuthorized],[UserManager],[ViewCost],[ViewSalesProfit],[VoidSalesItem],[VoucherDiscountAllowed],[WarehouseSetup],0,0,0,0,0,0,0,0,0,0,[WeeklySalesAnalysisReport],[WeeklySalesGPAnalysisReport]
  FROM [Enrich_DB].[dbo].[UserList];



=======================ignor this===============================================================

Enrich Homes (out of date)

-- combine product and barcode table.
Alter Table StockItems Add Barcode nvarchar(30) Null;
Alter Table StockItems Add Barcode1 nvarchar(30) Null;
Alter Table StockItems Add Barcode2 nvarchar(30) Null;
Alter Table StockItems Add Barcode3 nvarchar(30) Null;
Alter Table StockItems Add Barcode4 nvarchar(30) Null;
Alter Table StockItems Add Barcode5 nvarchar(30) Null;
GO
UPDATE StockItems SET Barcode = (SELECT Barcode FROM Barcodes WHERE StockItems.StockId = Barcodes.StockId);
UPDATE StockItems SET Barcode1 = (SELECT Barcode1 FROM Barcodes WHERE StockItems.StockId = Barcodes.StockId);
UPDATE StockItems SET Barcode2 = (SELECT Barcode2 FROM Barcodes WHERE StockItems.StockId = Barcodes.StockId);
UPDATE StockItems SET Barcode3 = (SELECT Barcode3 FROM Barcodes WHERE StockItems.StockId = Barcodes.StockId);
UPDATE StockItems SET Barcode4 = (SELECT Barcode4 FROM Barcodes WHERE StockItems.StockId = Barcodes.StockId);
UPDATE StockItems SET Barcode5 = (SELECT Barcode5 FROM Barcodes WHERE StockItems.StockId = Barcodes.StockId);
GO
drop table barcodes;
GO
-- rename same busniess tables.
exec sp_rename 'Bundle', 'PackageNameTable';
exec sp_rename 'BundleItems', 'PackageTable';
exec sp_rename 'CashFloat', 'CashFloatTable';
exec sp_rename 'ChartOfAccount', 'Chart';
exec sp_rename 'ConsolidateSalesPrice', 'ConsolidatePriceTable';
exec sp_rename 'CustomizedSalesPrice', 'IndividualCustomerPrice';
exec sp_rename 'CustOrder', 'CustomerOrderHead';
exec sp_rename 'CustOrderDetail', 'CustomerOrderItem';
exec sp_rename 'ExpenseAccount', 'ExpAcct';
exec sp_rename 'ExpenseDetail', 'ExpDetail';
exec sp_rename 'GlobalSettings', 'Profile';
exec sp_rename 'HoldSales', 'HoldHead';
exec sp_rename 'HoldSalesItem', 'HoldItem';
exec sp_rename 'Inventory', 'StockReceiveHead';
exec sp_rename 'InventoryDetail', 'StockReceiveItem';
exec sp_rename 'LabelRequest', 'LabelOrderTable';
exec sp_rename 'PaidAccount', 'PayAcct';
exec sp_rename 'PaymentReceived', 'RecvAcct';
exec sp_rename 'Purchase', 'PurchaseHead';
exec sp_rename 'PurchaseDetail', 'PurchaseItem';
exec sp_rename 'RulesList', 'Menu';
exec sp_rename 'Sales', 'InvoiceHead';
exec sp_rename 'SalesDetail', 'InvoiceItem';
exec sp_rename 'StockFamily', 'ProductFamily';
exec sp_rename 'StockItems', 'Product';
exec sp_rename 'StockItemLinks', 'PluLinks';
exec sp_rename 'StockLocation', 'Warehouse';
exec sp_rename 'StockTake', 'StockTakeHead';
exec sp_rename 'StockTakeDetail', 'StockTakeItem';
exec sp_rename 'TillOpensCount', 'DrawerOpenRecordTable';
exec sp_rename 'UserList', 'AccessMenu';
exec sp_rename 'UserRules', 'UserGroupTable';
exec sp_rename 'WorkStationId', 'MachineID';
exec sp_rename 'Vendor', 'Supplier';
GO
-- backup current gift card sales record, will create new table for future.
exec sp_rename 'GiftCardSales', 'GiftCardSalesBak';
GO
-- rename same busniess columns.
exec sp_rename 'AccessMenu.Administrator', 'Supervisor', 'column';
exec sp_rename 'AccessMenu.AccountPaymentsReport', 'AccountPaymentsReportMenu', 'column';
exec sp_rename 'AccessMenu.AccountReceiptsReport', 'AccountReceiptsReportMenu', 'column';
exec sp_rename 'AccessMenu.AccountsPayable', 'AccountsPayableMenu', 'column';
exec sp_rename 'AccessMenu.AccountsReceivable', 'AccountsReceivableMenu', 'column';
exec sp_rename 'AccessMenu.AllowCopyDataForStockTransferOut', 'AllowCopyDataForStockTransferOut', 'column';
exec sp_rename 'AccessMenu.AttendanceReport', 'AttendanceReportMenu', 'column';
exec sp_rename 'AccessMenu.AuthorisedChangeCustomerId', 'AuthorisedChangeCustomerCode', 'column';
exec sp_rename 'AccessMenu.AuthorisedDoItemGroupSetting', 'AuthorisedDoItemGroupSetting', 'column';
exec sp_rename 'AccessMenu.BrueBoxAdmin', 'BrueBoxAdmin', 'column';
exec sp_rename 'AccessMenu.CancelSales', 'CancelSales', 'column';
exec sp_rename 'AccessMenu.CashDeclarationList', 'CashDeclarationListMenu', 'column';
exec sp_rename 'AccessMenu.CashFloat', 'CashFloatMenu', 'column';
exec sp_rename 'AccessMenu.CashFloatReport', 'CashFloatReportMenu', 'column';
exec sp_rename 'AccessMenu.CategorySetup', 'CategorySetupMenu', 'column';
exec sp_rename 'AccessMenu.ChangeDateAuthorised', 'ChangeDateAuthorised', 'column';
exec sp_rename 'AccessMenu.ChangeInvoicePrice', 'ChangeInvoicePrice', 'column';
exec sp_rename 'AccessMenu.ChangeProductOpenPriceStatus', 'ChangeProductOpenPriceStatus', 'column';
exec sp_rename 'AccessMenu.ChartOfAccount', 'ChartOfAccountMenu', 'column';
exec sp_rename 'AccessMenu.CheckDailyReportAuthorise', 'CheckDailyReportAuthorise', 'column';
exec sp_rename 'AccessMenu.ClosePOSWindowAuthorise', 'ClosePOSWindowAuthorise', 'column';
exec sp_rename 'AccessMenu.CreateNewStockItems', 'CreateNewProductItems', 'column';
exec sp_rename 'AccessMenu.CreditNotes', 'CreditNotesMenu', 'column';
exec sp_rename 'AccessMenu.CreditNotesInquiry', 'CreditNotesInquiryMenu', 'column';
exec sp_rename 'AccessMenu.CreditNotesPaymentList', 'CreditNotesPaymentList', 'column';
exec sp_rename 'AccessMenu.CreditorsReport', 'CreditorsReportMenu', 'column';
exec sp_rename 'AccessMenu.CrossStoreSalesRefundReport', 'CrossStoreSalesRefundReportMenu', 'column';
exec sp_rename 'AccessMenu.CustomerInquiry', 'CustomerInquiryMenu', 'column';
exec sp_rename 'AccessMenu.CustomerSetup', 'CustomerSetupMenu', 'column';
exec sp_rename 'AccessMenu.DailySalesAnalysisReport', 'DailySalesAnalysisReportMenu', 'column';
exec sp_rename 'AccessMenu.DatabaseBackup', 'DatabaseBackupMenu', 'column';
exec sp_rename 'AccessMenu.DatabaseRestore', 'DatabaseRestoreMenu', 'column';
exec sp_rename 'AccessMenu.DebitNotesInquiry', 'DebitNotesInquiryMenu', 'column';
exec sp_rename 'AccessMenu.DebitNotes', 'DebitNotesMenu', 'column';
exec sp_rename 'AccessMenu.DebitNotesPaymentList', 'DebitNotesPaymentList', 'column';
exec sp_rename 'AccessMenu.DebtorsReport', 'DebtorsReportMenu', 'column';
exec sp_rename 'AccessMenu.DiscountAuthorise', 'DiscountAuthorise', 'column';
exec sp_rename 'AccessMenu.DiscountReport', 'DiscountReportMenu', 'column';
exec sp_rename 'AccessMenu.DispatchActivityStatement', 'DispatchActivityStatement', 'column';
exec sp_rename 'AccessMenu.EditAccountPayment', 'EditAccountPayment', 'column';
exec sp_rename 'AccessMenu.EditAssemblyStatus', 'EditAssemblyStatus', 'column';
exec sp_rename 'AccessMenu.EditDispatchStatus', 'EditDispatchStatus', 'column';
exec sp_rename 'AccessMenu.EditInternalOrder', 'EditInternalOrder', 'column';
exec sp_rename 'AccessMenu.EditInvoiceAuthorised', 'EditInvoiceAuthorised', 'column';
exec sp_rename 'AccessMenu.EditSalesPrice', 'EditSalesPrice', 'column';
exec sp_rename 'AccessMenu.EditServiceStatus', 'EditServiceStatus', 'column';
exec sp_rename 'AccessMenu.EditStockItemSettings', 'EditProductItemSettings', 'column';
exec sp_rename 'AccessMenu.EditStockReceive', 'EditStockReceive', 'column';
exec sp_rename 'AccessMenu.EFTPOSUtility', 'EFTPOSUtilityMenu', 'column';
exec sp_rename 'AccessMenu.ElectronicSettings', 'ElectronicSettingsMenu', 'column';
exec sp_rename 'AccessMenu.EndOfDayReport', 'EndOfDayReportMenu', 'column';
exec sp_rename 'AccessMenu.ExpensesDataEntry', 'ExpensesDataEntryMenu', 'column';
exec sp_rename 'AccessMenu.ExpensesPaymentReport', 'ExpensesPaymentReportMenu', 'column';
exec sp_rename 'AccessMenu.ExpensesSummaryReport', 'ExpensesSummaryReportMenu', 'column';
exec sp_rename 'AccessMenu.GlobalSettings', 'CompanyProfileSetupMenu', 'column';
exec sp_rename 'AccessMenu.InquiryLayBySalesReport', 'InquiryLayBySalesReportMenu', 'column';
exec sp_rename 'AccessMenu.InquiryOrderProcess', 'InquiryOrderProcessMenu', 'column';
exec sp_rename 'AccessMenu.InquirySalesRelatedReportDays', 'InquirySalesRelatedReportDays', 'column';
exec sp_rename 'AccessMenu.InternalStockTransfer', 'InternalStockTransferMenu', 'column';
exec sp_rename 'AccessMenu.InternalStockTransferInquiry', 'InternalStockTransferInquiryMenu', 'column';
exec sp_rename 'AccessMenu.Invoice', 'InvoiceMenu', 'column';
exec sp_rename 'AccessMenu.InvoiceInquiry', 'InvoiceInquiryMenu', 'column';
exec sp_rename 'AccessMenu.InvoiceLabelSetup', 'InvoiceLabelSetup', 'column';
exec sp_rename 'AccessMenu.IssureCustomerCredit', 'IssureCustomerCredit', 'column';
exec sp_rename 'AccessMenu.ManuallyWeightItemList', 'ManuallyWeightItemList', 'column';
exec sp_rename 'AccessMenu.MetcashWeeklyUpdate', 'MetcashWeeklyUpdate', 'column';
exec sp_rename 'AccessMenu.MinusSalesAuthorise', 'MinusSalesAuthorise', 'column';
exec sp_rename 'AccessMenu.OnlyViewUnpaidInvoice', 'OnlyViewUnpaidInvoice', 'column';
exec sp_rename 'AccessMenu.OpenCashDrawer', 'OpenCashDrawerMenu', 'column';
exec sp_rename 'AccessMenu.OrderProcessReport', 'OrderProcessReportMenu', 'column';
exec sp_rename 'AccessMenu.OrderProcessStatus', 'OrderProcessStatusMenu', 'column';
exec sp_rename 'AccessMenu.PaymentMethodsSetup', 'PaymentMethodsSetupMenu', 'column';
exec sp_rename 'AccessMenu.PointOfSales', 'PointOfSalesMenu', 'column';
exec sp_rename 'AccessMenu.PrintEndOfDayReport', 'PrintEndOfDayReport', 'column';
exec sp_rename 'AccessMenu.PrintReportAuthorised', 'PrintReportAuthorised', 'column';
exec sp_rename 'AccessMenu.PurchaseOrder', 'PurchaseOrderMenu', 'column';
exec sp_rename 'AccessMenu.PurchaseOrderInquiry', 'PurchaseOrderInquiryMenu', 'column';
exec sp_rename 'AccessMenu.PurchaseReport', 'PurchaseReportMenu', 'column';
exec sp_rename 'AccessMenu.PurchaseSummaryReport', 'PurchaseSummaryReportMenu', 'column';
exec sp_rename 'AccessMenu.ReprintInvoice', 'ReprintInvoice', 'column';
exec sp_rename 'AccessMenu.SalesReport', 'SalesReportMenu', 'column';
exec sp_rename 'AccessMenu.SalesStatistics', 'SalesStatisticsMenu', 'column';
exec sp_rename 'AccessMenu.SalesSummaryReport', 'SalesSummaryReportMenu', 'column';
exec sp_rename 'AccessMenu.ScaleLabelGeneration', 'ScaleLabelGenerationMenu', 'column';
exec sp_rename 'AccessMenu.SecureCode', 'SecureCode', 'column';
exec sp_rename 'AccessMenu.ShelfLabelSetup', 'ShelfLabelSetup', 'column';
exec sp_rename 'AccessMenu.SpecialPriceItemsList', 'SpecialPriceItemsListMenu', 'column';
exec sp_rename 'AccessMenu.StaffName', 'StaffName', 'column';
exec sp_rename 'AccessMenu.StockAdjustment', 'StockAdjustmentMenu', 'column';
exec sp_rename 'AccessMenu.StockAdjustmentInquiry', 'StockAdjustmentInquiryMenu', 'column';
exec sp_rename 'AccessMenu.StockAdjustmentReport', 'StockAdjustmentReportMenu', 'column';
exec sp_rename 'AccessMenu.StockAnalysisReport', 'StockAnalysisReportMenu', 'column';
exec sp_rename 'AccessMenu.StockDamageAndWaste', 'StockDamageAndWasteMenu', 'column';
exec sp_rename 'AccessMenu.StockDamageAndWasteHistoryInquiry', 'StockDamageAndWasteHistoryInquiryMenu', 'column';
exec sp_rename 'AccessMenu.StockDamageAndWasteReport', 'StockDamageAndWasteReportMenu', 'column';
exec sp_rename 'AccessMenu.StockItemInquiry', 'ProductInquiryMenu', 'column';
exec sp_rename 'AccessMenu.StockOnOrderReport', 'StockOnOrderReportMenu', 'column';
exec sp_rename 'AccessMenu.StockReceive', 'StockReceiveMenu', 'column';
exec sp_rename 'AccessMenu.StockReceiveInquiry', 'StockReceiveInquiryMenu', 'column';
exec sp_rename 'AccessMenu.StockReport', 'StockReportMenu', 'column';
exec sp_rename 'AccessMenu.StockTake', 'StockTakeMenu', 'column';
exec sp_rename 'AccessMenu.StockTakeReport', 'StockTakeReportMenu', 'column';
exec sp_rename 'AccessMenu.StockTransferIn', 'StockTransferInMenu', 'column';
exec sp_rename 'AccessMenu.StockTransferInquiry', 'StockTransferInquiryMenu', 'column';
exec sp_rename 'AccessMenu.StockTransferOut', 'StockTransferOutMenu', 'column';
exec sp_rename 'AccessMenu.SystemConfiguration', 'SystemConfiguration', 'column';
exec sp_rename 'AccessMenu.TransferReport', 'TransferReportMenu', 'column';
exec sp_rename 'AccessMenu.TransferSummaryReport', 'TransferSummaryReportMenu', 'column';
exec sp_rename 'AccessMenu.UploadMemberPointsToWebServer', 'UploadMemberPointsToWebServerMenu', 'column';
exec sp_rename 'AccessMenu.UserGroupSetupAuthorized', 'UserGroupSetupAuthorized', 'column';
exec sp_rename 'AccessMenu.UserManager', 'UserManagerMenu', 'column';
exec sp_rename 'AccessMenu.VendorInquiry', 'SupplierInquiryMenu', 'column';
exec sp_rename 'AccessMenu.VendorSetup', 'SupplierSetupMenu', 'column';
exec sp_rename 'AccessMenu.ViewCost', 'ViewCost', 'column';
exec sp_rename 'AccessMenu.ViewSalesProfit', 'ViewSalesProfit', 'column';
exec sp_rename 'AccessMenu.VoidSalesItem', 'VoidSalesItem', 'column';
exec sp_rename 'AccessMenu.VoucherDiscountAllowed', 'VoucherDiscountAllowed', 'column';
exec sp_rename 'AccessMenu.WarehouseSetup', 'WarehouseSetupMenu', 'column';
exec sp_rename 'AccessMenu.CustomerOrder', 'QuotationMenu', 'column';
exec sp_rename 'AccessMenu.CustomerOrderInquiry', 'QuotationInquiryMenu', 'column';
GO
alter table AccessMenu add BAStatementsMenu BIT NULL;
GO
update AccessMenu set BAStatementsMenu = 0;
GO
exec sp_rename 'Attendance.Id' , 'IdNo', 'column';
exec sp_rename 'CashDeclaration.WorkStationId' , 'MachineID', 'column';
exec sp_rename 'CashFloatTable.WorkStationId' , 'MachineID', 'column';
exec sp_rename 'ConsolidatePriceTable.StockId' , 'ProductCode', 'column';
GO
exec sp_rename 'Customer.CustomerId' , 'CustomerCode', 'column';
exec sp_rename 'Customer.VirsualCustomer' , 'SpecialCustomer', 'column';
exec sp_rename 'CustomerOrderHead.CustomerId' , 'CustomerCode', 'column';
exec sp_rename 'CustomerOrderHead.PriceIncludeGST' , 'GSTStatus', 'column';
exec sp_rename 'CustomerOrderHead.LocationCode' , 'WarehouseCode', 'column';
exec sp_rename 'CustomerOrderItem.StockId' , 'ProductCode', 'column';
GO
exec sp_rename 'DrawerOpenRecordTable.WorkStationId' , 'MachineID', 'column';
exec sp_rename 'Expenses.VendorId' , 'SupplierCode', 'column';
GO
exec sp_rename 'HoldHead.CustomerId' , 'CustomerCode', 'column';
exec sp_rename 'HoldHead.WorkStationId' , 'MachineID', 'column';
alter table HoldHead add VoucherId NVARCHAR(20) NULL;
exec sp_rename 'HoldItem.StockId' , 'ProductCode', 'column';
alter table HoldItem add IsRedeemItem BIT NULL;
alter table HoldItem add RedeemItemPoints FLOAT NULL
GO
exec sp_rename 'IndividualCustomerPrice.StockId' , 'ProductCode', 'column';
exec sp_rename 'IndividualCustomerPrice.CustomerId' , 'CustomerCode', 'column';
GO
exec sp_rename 'InvoiceHead.CustomerId' , 'CustomerCode', 'column';
exec sp_rename 'InvoiceHead.Kind' , 'KindFlag', 'column';
exec sp_rename 'InvoiceHead.PriceIncludeGST' , 'GSTStatus', 'column';
exec sp_rename 'InvoiceHead.IsLayBySales' , 'LayByFlag', 'column';
exec sp_rename 'InvoiceHead.WorkStationId' , 'MachineID', 'column';
exec sp_rename 'InvoiceHead.LocationCode' , 'WarehouseCode', 'column';
exec sp_rename 'InvoiceHead.TransactionDate' , 'TransferDateTime', 'column';
exec sp_rename 'InvoiceHead.RedeemPoints' , 'TotalRedeemPoints', 'column';
exec sp_rename 'InvoiceItem.StockId' , 'ProductCode', 'column';
exec sp_rename 'InvoiceItem.Kind' , 'KindFlag', 'column';
exec sp_rename 'InvoiceItem.LocationCode' , 'WarehouseCode', 'column';
GO
exec sp_rename 'LabelOrderTable.StockId' , 'ProductCode', 'column';
alter table LabelOrderTable drop column id;
exec sp_rename 'LayByCustomer.Id' , 'IdNo', 'column';
GO
exec sp_rename 'MachineID.WorkStationId' , 'MachineID', 'column';
GO
exec sp_rename 'PackageNameTable.BundleId' , 'ModelID', 'column';
exec sp_rename 'PackageNameTable.BundleName' , 'ModelName', 'column';
exec sp_rename 'PackageTable.StockId' , 'ProductCode', 'column';
exec sp_rename 'PackageTable.BundleId' , 'ModelID', 'column';
GO
exec sp_rename 'PayAcct.Id' , 'IdNo', 'column';
GO
exec sp_rename 'Product.StockId' , 'ProductCode', 'column';
exec sp_rename 'Product.PriceIncludeGST' , 'GSTStatus', 'column';
exec sp_rename 'Product.InnerUnits' , 'MeasureQty', 'column';
exec sp_rename 'Product.ShelfNumber' , 'Location', 'column';
exec sp_rename 'Product.StockOrdered' , 'StockOnOrder', 'column';
exec sp_rename 'Product.StockWithhold' , 'StockCommitted', 'column';
exec sp_rename 'Product.DefaultVendor' , 'DefaultSupplier', 'column';
GO
alter table ProductFamily add UpdatedAt DATETIME CONSTRAINT [DF_ProductFamily_UpdatedAt] DEFAULT (getdate()) NULL;
GO
exec sp_rename 'PurchaseHead.VendorId' , 'SupplierCode', 'column';
exec sp_rename 'PurchaseHead.PriceIncludeGST' , 'GSTStatus', 'column';
exec sp_rename 'PurchaseItem.StockId' , 'ProductCode', 'column';
GO
exec sp_rename 'RecvAcct.WorkStationId' , 'MachineID', 'column';
exec sp_rename 'RecvAcct.Id' , 'IdNo', 'column';
GO
exec sp_rename 'StockReceiveHead.VendorId' , 'SupplierCode', 'column';
exec sp_rename 'StockReceiveHead.Kind' , 'KindFlag', 'column';
exec sp_rename 'StockReceiveHead.PriceIncludeGST' , 'GSTStatus', 'column';
exec sp_rename 'StockReceiveHead.LocationCode' , 'WarehouseCode', 'column';
exec sp_rename 'StockReceiveHead.TransactionDate' , 'TransferDateTime', 'column';
exec sp_rename 'StockReceiveItem.StockId' , 'ProductCode', 'column';
exec sp_rename 'StockReceiveItem.Kind' , 'KindFlag', 'column';
exec sp_rename 'StockReceiveItem.LocationCode' , 'WarehouseCode', 'column';
GO
exec sp_rename 'StockTakeHead.Kind' , 'KindFlag', 'column';
exec sp_rename 'StockTakeHead.LocationCode' , 'WarehouseCode', 'column';
exec sp_rename 'StockTakeHead.TransactionDate' , 'TransferDateTime', 'column';
exec sp_rename 'StockTakeItem.StockId' , 'ProductCode', 'column';
exec sp_rename 'StockTakeItem.Kind' , 'KindFlag', 'column';
exec sp_rename 'StockTakeItem.LocationCode' , 'WarehouseCode', 'column';
GO
exec sp_rename 'Supplier.VendorId' , 'SupplierCode', 'column';
exec sp_rename 'Supplier.VendorName' , 'SupplierName', 'column';
exec sp_rename 'Supplier.VirsualVendor' , 'SpecialSupplier', 'column';
alter table Supplier add CreditLimit FLOAT NULL;
alter table Supplier add CreditTerm SMALLINT NULL;
GO
exec sp_rename 'Warehouse.LocationCode' , 'WarehouseCode', 'column';
exec sp_rename 'Warehouse.LocationName' , 'WarehouseName', 'column';
alter table Warehouse add Telephone NVARCHAR(15) NULL;
alter table Warehouse add Fax NVARCHAR(15) NULL;
alter table Warehouse add Address NVARCHAR(200) NULL;
GO
alter table Version drop column ProductName;
update Version set version = '1'; 
GO
alter table Profile add GSTStatus BIT NULL;
GO
update Profile set GSTStatus = PriceIncludeGST;
/****** Object:  Table [dbo].[WarrantySupplierSerialNo]    Script Date: 05/09/2022 19:36:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[WarrantySupplierSerialNo](
	[ReferenceNo] [nvarchar](10) NULL,
	[ProductCode] [nvarchar](15) NULL,
	[SerialNo] [nvarchar](30) NULL,
	[SendNo] [nvarchar](10) NULL,
	[Description] [nvarchar](255) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[WarrantySupplierItem]    Script Date: 05/09/2022 19:36:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[WarrantySupplierItem](
	[ReferenceNo] [nvarchar](10) NULL,
	[ProductCode] [nvarchar](15) NULL,
	[Qty] [float] NULL,
	[BackQty] [float] NULL,
	[SendNo] [nvarchar](10) NULL,
	[KindFlag] [int] NULL,
	[ID] [int] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[WarrantySupplierHead]    Script Date: 05/09/2022 19:36:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[WarrantySupplierHead](
	[ReferenceNo] [nvarchar](10) NULL,
	[WarrantyDate] [datetime] NULL,
	[SupplierCode] [nvarchar](15) NULL,
	[SupplierRANo] [nvarchar](15) NULL,
	[Notes] [nvarchar](255) NULL,
	[KindFlag] [int] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[WarrantyCustomerSerialNo]    Script Date: 05/09/2022 19:36:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[WarrantyCustomerSerialNo](
	[ReferenceNo] [nvarchar](10) NULL,
	[ProductCode] [nvarchar](15) NULL,
	[SerialNo] [nvarchar](30) NULL,
	[ReceiveNo] [nvarchar](10) NULL,
	[Description] [nvarchar](255) NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[WarrantyCustomerItem]    Script Date: 05/09/2022 19:36:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[WarrantyCustomerItem](
	[ReferenceNo] [nvarchar](10) NULL,
	[ProductCode] [nvarchar](15) NULL,
	[Qty] [float] NULL,
	[ReturnQty] [float] NULL,
	[ReceiveNo] [nvarchar](10) NULL,
	[KindFlag] [int] NULL,
	[ID] [int] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[WarrantyCustomerHead]    Script Date: 05/09/2022 19:36:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[WarrantyCustomerHead](
	[ReferenceNo] [nvarchar](10) NULL,
	[WarrantyDate] [datetime] NULL,
	[CustomerCode] [nvarchar](15) NULL,
	[CustomerRANo] [nvarchar](15) NULL,
	[Notes] [nvarchar](255) NULL,
	[KindFlag] [int] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Warranty]    Script Date: 05/09/2022 19:36:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Warranty](
	[RANo] [nvarchar](10) NULL,
	[CustomerCode] [nvarchar](15) NULL,
	[CustomerNotes] [nvarchar](20) NULL,
	[ProductCode] [nvarchar](15) NULL,
	[SerialNo] [nvarchar](30) NULL,
	[FaultyDescription] [nvarchar](255) NULL,
	[Received] [bit] NULL,
	[ReceivedDate] [datetime] NULL,
	[Returned] [bit] NULL,
	[ReturnedDate] [datetime] NULL,
	[CustomerWarrantyResult] [smallint] NULL,
	[CustomerNewSerialNo] [nvarchar](30) NULL,
	[SupplierCode] [nvarchar](15) NULL,
	[SupplierRANo] [nvarchar](20) NULL,
	[SendToSupplier] [bit] NULL,
	[SendDate] [datetime] NULL,
	[BackFromSupplier] [bit] NULL,
	[BackDate] [datetime] NULL,
	[SupplierWarrantyResult] [smallint] NULL,
	[SupplierNewSerialNo] [nvarchar](30) NULL,
	[CurrentStatus] [smallint] NULL,
	[SupplierMemo] [nvarchar](255) NULL,
	[IDNo] [smallint] NULL,
	[CustomerRANo] [nvarchar](20) NULL,
	[ReceiverName] [nvarchar](15) NULL,
	[ReturnerName] [nvarchar](15) NULL,
	[SenderName] [nvarchar](15) NULL,
	[BackerName] [nvarchar](15) NULL,
	[ContactCustomer] [int] NULL,
	[ContactDate] [datetime] NULL
) ON [PRIMARY]
GO


enrich.sql