iVend Database Database > Stored Procedures > GetMobileReplicationData Stored Procedure

Navigation: iVend Database Database > Stored Procedures >

iVend 6.6 Database Help

GetMobileReplicationData Stored Procedure

Collapse All Expand All

iVend Database Database : GetMobileReplicationData Stored Procedure

Properties

Creation Date	4/13/2015 12:00 PM
Encrypted
Ansi Nulls

Parameters

Parameter	Direction	Description	Data Type	Size
@pDeviceId	In	Reference device Id of the POS	VarWChar	100
@RETURN_VALUE	Return Value		Integer	4

Objects that GetMobileReplicationData depends on

Database Object	Object Type	Description	Dep Level
CfgBarCodeMask	Table	Store the barcode mask.	1
CfgBranchSetup	Table	Provides the details for the branch setup	1
CfgCountry	Table	The country details are specified which can be selected further at the addresses entry of the customer/ vendor or which are related to the business in some way.	1
CfgCustomTransactionInfoGrid	Table	Stores the master values for the Transaction information grid	1
CfgEnterprise	Table	Stores the basic configuration entries for the system . These settings are mandatory for the system to work properly.	1
CfgLineAttribute	Table	Stores the sales attributes (Added information that can be attached to each transaction at POS) created in the system. Maximum of 10 attributes are allowed to be created.	1
CfgLoyaltyConfig	Table	Stores the configuration information for the loyalty	1
CfgSaleAttribute	Table	Stores the sales attributes (Added information that can be attached to each transaction at POS) created in the system. Maximum of 10 attributes are allowed to be created.	1
CfgSiteInformation	Table	Stores the site information. Enterprise always has the site id as 1. System Table Warning : Should not be modified directly	2
CfgState	Table	The state details are specified which can be selected further at the addresses entry of the customer/ vendor or which are related to the business in some way.	1
CfgUserDefinedFieldDetail	Table	Stores the Details of the User Defined field values	1
CfgUserDefinedFieldValidValue	Table	Stores the User defined field values	1
CfgUserDefinedTable	Table	Stores the Users defined tables	1
CusCustomer	Table	Defines the customer details that interact with the enterprise.	1
CusCustomerBranch	Table	Stores the customer branch information	1
CusCustomerDiscountGroup	Table	Defined the various Discount groups under which the Customers can be categorised. Used in Navision Integration only	1
CusCustomerGroup	Table	Defines the groups in which the customers can be catagorised.	1
CusCustomerPriceGroup	Table	Defined the various Price groups under which the Customers can be categorised. Used in Navision Integration only	1
FulFulfillmentPlan	Table	Defines the plan through which the sold quantities can be fulfilled later to the customer.	1
GetCompanyDateTime	User Defined Function		1
InvBatch	Table	Batch details gets affected when doing any transaction with item which has been batch tracked.	1
InvBatchLocation	Table	Describes the batch information with Location, Warehouse, Product and Expiry date	1
InvLocation	Table	Save the location infomation.	1
InvManufacturer	Table	Stores the manufacturer information	1
InvProduct	Table	Stores the product related details.	1
InvProductCategory	Table	Defines the categories under which the products can be put for logical classification.	1
InvProductCost	Table	Store the historical cost of the product.	1
InvProductGroup	Table	Defined the various groups under which the products can be categorised.	1
InvProductImages	Table	Defines any images that have been put against the product	1
InvSerial	Table	Serial details gets affected when doing any transaction with the item which has been serial tracked.	1
InvUOM	Table	Master table for the Inv UOM	1
InvUOMGroup	Table	Stores the value for the UOM group	1
InvUPCTranslation	Table	Specified multiple UPC Codes or Bar codes for a product.	1
InvWarehouse	Table	Defines a list of all warehouses defined in the system.	1
LbrUser	Table	Defines a list of all employees/ labor users created in the system	1
LoyLoyaltyCardInformation	Table	Store the Details of Loyalty Customer in the system.	1
PmtCurrency	Table	Define a list of all currecies defined in the system.	1
PmtExchangeRate	Table	Stores the Exchange Rate values for the currencies defined in application	1
PmtLayawayPlan	Table	Defines the plan through which the items can be put on layaways to the customers.	1
PmtPaymentType	Table	Defines a list of payment types specified in the system. These payment types are used to take payment against a transaction at POS.	1
PmtPaymentTypeStore	Table	Stores the payment types associated with a store	1
PrcDiscountGroup	Table	Stores the Discount group related details. Used in Navision Integration only	1
PrcPriceList	Table	Defines the price lists which are integrated through ERP system.	1
PrcPriceMatrix	Table	Defines the list of products that are associated with each price list. It is also integrated through ERP.	1
PrcProductDiscountGroup	Table	Stores the Product discount group related details. Used in Navision Integration only	1
PrcSalesPrice	Table	Stores the Sales price related details. Used in Navision Integration only	1
PrcSurcharge	Table	Defines a list of surcharges that have been defined in the system. Surcharges can be applicable both at the Sale and Purchase side	1
PrcUOMPriceMatrix	Table	Defines the values for the UOM price matrix	1
ProCoupon	Table	Stores the issued coupon details for iVend	1
RepReplicationMobileInitialization	Table		1
RepReplicationSendStatusMobile	Table	Table used to track the replication record status	1
RepReplicationTransactionMobile	Table	Stores the Transaction details for them the data needs to be replicated from mobile	1
RtlPOS	Table	Contains details about all POS defined in the system.	1
RtlPOSDocumentNumberSeries	Table	Stores the Document Number series details for a POS	1
RtlRetailProfile	Table	Contains different profiles defined in the system. Profiles contains information which is defines the behaviour of the POS and Console.	1
RtlStore	Table	Contains the details of Stores defined in the system.	1
RtlStoreGroup	Table	Stores the values for a store group	1
RtlStorePriceList	Table	Stores the price list that will be effective for the store on current date	1
RtlSubsidiary	Table	Defines all the Subsidiaries defined in the application	1
SecSecurityRoleSecurityFunction	Table	This table defines the security rights on the main modules granted to a specific security role.	1
SecSecurityRoleSecuritySubFunction	Table	This table defines the security rights on the sub modules granted to a specific security role.	1
SecSecurityUser	Table	We can make any employee of the system as a security user and specify details like login id and password and electronic id etc in this table , who may be doing POS transactions. It is mandatory to make an employee a security user before he/she can do POS transactions.	1
SubSubsidiaryItem	Table	Stores Subsidiary information for each master, which can have multiple Subsidiaries assigned	1
TaxTaxCode	Table	TaxCode is defined at the SBO and it's replicated from SBO to iVend. We can only view the TaxCodes at MC and associate them with the different objects, like Product, Customer, Enterprise, etc.	1
TrxDeliveryPackage	Table	Stores the Delivery package information	1
usp_RethrowError	Stored Procedure	Rethrow an error to the calling function/application	1

Procedure Source Code

CREATE PROCEDURE [dbo].[GetMobileReplicationData]

(

@pDeviceId NVARCHAR(100)

)

BEGIN

DECLARE @POSKey NVARCHAR(50),

@StoreKey NVARCHAR(50),

@WarehouseKey NVARCHAR(50),

@PriceListKey NVARCHAR(50),

@RetailProfileKey NVARCHAR(50),

@ProfileData XML,

@ButtonPanelKey NVARCHAR(50),

@BranchCode NVARCHAR(30),

@BranchCount INT ,

@SubsidiaryKey NVARCHAR(50) ,

@IsMultipleSubsidiaryEnabled BIT

BEGIN TRY

SELECT @SubsidiaryKey = '0'

SELECT @IsMultipleSubsidiaryEnabled = 0

-- Check if the device is registered or not.

IF NOT EXISTS ( SELECT 1 FROM RtlPOS Where HardwareId = @pDeviceId)

RAISERROR('%s',16,-1,'POSLogic.DeviceNotRegistered')

-- Get the POS and Store for device id

SELECT @POSKey = POSKey, @StoreKey = StoreKey FROM RtlPOS WHERE HardwareId = @pDeviceId

-- Get the Store warehouse

SELECT @WarehouseKey = WarehouseKey FROM RtlStore WHERE StoreKey = @StoreKey

--Get IsMultipleSubsidiaryEnabled

SELECT @IsMultipleSubsidiaryEnabled = ISNULL( IsMultipleSubsidiaryEnabled, '0' ) FROM CfgEnterprise

-- Get the warehouse Subsidiary

if(@IsMultipleSubsidiaryEnabled = 1)

SELECT @SubsidiaryKey = ISNULL( SubsidiaryKey, '0') FROM InvWarehouse WHERE WarehouseKey = @WarehouseKey

-- Check if the POS is attached with Store or not.

IF @StoreKey = '0'

RAISERROR('%s',16,-1,'POSLogic.StoreNotSelected')

SELECT @BranchCode = ISNULL(BranchCode,'') FROM InvWarehouse Where WarehouseKey = @WarehouseKey

SELECT @BranchCount = COUNT(1) FROM CfgBranchSetup WHERE IsDeleted = 0 AND IsActive = 1

--============================ Delete sync status for device ==========================

Delete from RepReplicationSendStatusMobile Where DeviceId = @pDeviceId

--============================ Delete initialization data =============================

Delete from RepReplicationMobileInitialization Where POSKey = @POSKey

--============================ Event for Enterprise ===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 8, EnterpriseKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM CfgEnterprise

--============================ Event for Store ===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 114, StoreKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM RtlStore WHERE StoreKey = @StoreKey

--============================ Event for POS ===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 108, POSKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM RtlPOS WHERE POSType = 1 AND StoreKey = @StoreKey AND IsDeleted = 0 AND POSKey = @POSKey

--============================ Event for Warehouse ===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 61, WarehouseKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM InvWarehouse WHERE IsDeleted = 0 AND IsActive = 1

AND ISNULL(SubsidiaryKey, '0') = @SubsidiaryKey

--============================ Event for Security User ===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 124, SecurityUserKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM SecSecurityUser

WHERE IsActive = 1 AND IsDeleted = 0 AND IsLocked = 0

--============================ Event for User ===================================

if(@IsMultipleSubsidiaryEnabled = 0)

BEGIN

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 68, UserKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM LbrUser WHERE IsActive = 1 AND IsDeleted = 0

END

ELSE

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 68, UserKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM LbrUser WHERE IsActive = 1 AND IsDeleted = 0

AND @SubsidiaryKey IN(SELECT SubsidiaryKey FROM SubSubsidiaryItem WHERE SourceKey = UserKey AND SourceType = 68)

--============================ Event for Tax Code ===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 127, TaxCodeKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM TaxTaxCode WHERE IsDeleted = 0 AND ISNULL( SubsidiaryKey, '0') = @SubsidiaryKey

--============================ Event for Customer Groups ===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 24, CustomerGroupKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM CusCustomerGroup WHERE IsDeleted = 0

--============================ Event for Customers ===================================

if(@IsMultipleSubsidiaryEnabled = 0)

BEGIN

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 20, CustomerKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM CusCustomer WHERE IsDeleted = 0 AND IsActive = 1

AND OnHold = 0 AND IsMarketing = 0 AND IsTemplate = 0 AND MultiCurrency = 1

AND (@BranchCount = 0 OR @BranchCode = '' OR CustomerKey IN(SELECT CustomerKey FROM CusCustomerBranch WHERE CONVERT(NVARCHAR,BranchKey) = @BranchCode))

END

ELSE

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 20, CustomerKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM CusCustomer WHERE IsDeleted = 0 AND IsActive = 1

AND OnHold = 0 AND IsMarketing = 0 AND IsTemplate = 0 AND MultiCurrency = 1

AND (@BranchCount = 0 OR @BranchCode = '' OR CustomerKey IN(SELECT CustomerKey FROM CusCustomerBranch WHERE CONVERT(NVARCHAR,BranchKey) = @BranchCode))

AND @SubsidiaryKey IN(SELECT SubsidiaryKey FROM SubSubsidiaryItem WHERE SourceKey = CustomerKey AND SourceType = 20)

--============================ Event for Product Groups ===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 50, ProductGroupKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM InvProductGroup WHERE IsDeleted = 0

--============================ Event for Products ===================================

if(@IsMultipleSubsidiaryEnabled = 0)

BEGIN

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 46, ProductKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM InvProduct WHERE IsDeleted = 0 AND IsOnHold = 0 AND IsMatrixItem = 0

END

ELSE

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 46, ProductKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM InvProduct WHERE IsDeleted = 0 AND IsOnHold = 0 AND IsMatrixItem = 0

AND @SubsidiaryKey IN(SELECT SubsidiaryKey FROM SubSubsidiaryItem WHERE SourceKey = ProductKey AND SourceType = 46)

--============================ Event for Product Category ===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 48, ProductCategoryKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM InvProductCategory WHERE IsDeleted = 0

--============================ Event for Price List ===================================

-- Get price list attached to Store

SELECT @PriceListKey = PriceListKey FROM RtlStore WHERE StoreKey = @StoreKey

-- Get price list of attached pricing store group

IF @PriceListKey = '0'

SELECT @PriceListKey = PriceListKey FROM RtlStoreGroup WHERE StoreGroupKey = (SELECT PricingStoreGroupKey FROM RtlStore WHERE StoreKey = @StoreKey)

AND ISNULL( SubsidiaryKey, '0') = @SubsidiaryKey

-- Price List

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 80, PriceListKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM PrcPriceList WHERE PriceListKey = @PriceListKey AND IsDeleted = 0 AND ISNULL( SubsidiaryKey, '0') = @SubsidiaryKey

-- Price Matrix

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 81, PriceMatrixKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM PrcPriceMatrix WHERE PriceListKey = @PriceListKey AND IsDeleted = 0

-- Price Matrix

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 270, UOMPriceMatrixKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM PrcUOMPriceMatrix WHERE PriceListKey = @PriceListKey AND IsDeleted = 0

--============================ Event for Batch ===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 33, BatchKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM InvBatch WHERE WarehouseKey = @WarehouseKey AND IsDeleted = 0

AND ProductKey IN (SELECT ProductKey FROM InvProduct WHERE IsDeleted = 0

AND IsOnHold = 0 AND IsMatrixItem = 0)

--============================ Event for Serial ===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 52, SerialKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM InvSerial WHERE IsDeleted = 0

AND ProductKey IN (SELECT ProductKey FROM InvProduct WHERE IsDeleted = 0

AND IsOnHold = 0 AND IsMatrixItem = 0)

--============================ Event for UPC Translation ===================================

if(@IsMultipleSubsidiaryEnabled = 0)

BEGIN

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 59, UPCTranslationKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM InvUPCTranslation

WHERE ProductKey IN (SELECT ProductKey FROM InvProduct WHERE IsDeleted = 0

AND IsOnHold = 0 AND IsMatrixItem = 0)

END

ELSE

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 59, UPCTranslationKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM InvUPCTranslation

WHERE ProductKey IN (SELECT ProductKey FROM InvProduct WHERE IsDeleted = 0

AND IsOnHold = 0 AND IsMatrixItem = 0)

AND @SubsidiaryKey IN(SELECT SubsidiaryKey FROM SubSubsidiaryItem WHERE SourceKey = ProductKey AND SourceType = 46)

--============================ Event for Currency ===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 71, CurrencyKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM PmtCurrency WHERE IsDeleted = 0

--============================ Event for Surcharges ===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 89, SurchargeKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM PrcSurcharge WHERE IsDeleted = 0 AND ISNULL( SubsidiaryKey, '0') = @SubsidiaryKey

--============================ Event for Payment Types ===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 77, PaymentTypeKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM PmtPaymentType WHERE IsDeleted = 0 AND IsActive = 1

AND PaymentTypeKey IN (SELECT PaymentTypeKey FROM PmtPaymentTypeStore WHERE StoreKey = @StoreKey) AND ISNULL( SubsidiaryKey, '0') = @SubsidiaryKey

--============================ Event for Quick Button Panel ===================================

-- Get retail profile attached with POS

SELECT @RetailProfileKey = RetailProfileKey FROM RtlPOS WHERE POSKey = @POSKey

-- Get retail profile attached with Store

IF @RetailProfileKey = 0

SELECT @RetailProfileKey = RetailProfileKey FROM RtlStore WHERE StoreKey = @StoreKey

-- Get retail profile attached with Enterprise

IF @RetailProfileKey = 0

SELECT @RetailProfileKey = RetailProfileKey FROM CfgEnterprise

Select @ProfileData = ProfileData From RtlRetailProfile Where RetailProfileKey = @RetailProfileKey

-- Get button panel attached with Retail Profile

SELECT @ButtonPanelKey = ISNULL(a.b.value('ButtonPanelKey[1]','NVARCHAR(50)'), 0) FROM @ProfileData.nodes('RetailProfile') a(b)

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 191, CustomTransactionInfoGridKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM CfgCustomTransactionInfoGrid WHERE IsDeleted = 0

AND CustomTransactionInfoGridKey = @ButtonPanelKey AND ISNULL( SubsidiaryKey, '0') = @SubsidiaryKey

--============================ Event for Countries ===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 7, CountryKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM CfgCountry WHERE IsDeleted = 0

--============================ Event for States ===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 13, StateKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM CfgState WHERE IsDeleted = 0

AND CountryKey IN (SELECT CountryKey FROM CfgCountry WHERE IsDeleted = 0)

--============================ Event for Manufacturers ===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 45, ManufacturerKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM InvManufacturer WHERE IsDeleted = 0

--============================ Event for Number Series ===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 251, POSDocumentNumberSeriesKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM RtlPOSDocumentNumberSeries

WHERE @POSKey = @POSKey AND IsActive = 1 AND IsDeleted = 0

--============================ Event for Layaway Plan ===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 74, LayawayPlanKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM PmtLayawayPlan WHERE IsDeleted = 0 AND IsActive = 1 AND ISNULL( SubsidiaryKey, '0') = @SubsidiaryKey

--============================ Event for Fulfillment Plan ===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 28, FulfillmentPlanKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM FulFulfillmentPlan WHERE IsDeleted = 0 AND ISNULL( SubsidiaryKey, '0') = @SubsidiaryKey

--============================ Event for Security Role Security Function ===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 121, SecurityRoleSecurityFunctionKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM SecSecurityRoleSecurityFunction WHERE SecurityFunctionKey = 13

--============================ Event for Security Role Security Sub Function ===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 122, SecurityRoleSecuritySubFunctionKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM SecSecurityRoleSecuritySubFunction

WHERE SecurityFunctionKey = 13

--============================ Event for UOM ===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 265, UOMKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM InvUOM WHERE IsDeleted = 0

--============================ Event for UOM Group ===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 266, UOMGroupKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM InvUOMGroup WHERE IsDeleted = 0

--============================ Event for Barcode Mask=================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 3, BarCodeMaskKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM CfgBarCodeMask WHERE IsDeleted = 0

--============================ Event for Sale Attributes =================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 10, SaleAttributeKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM CfgSaleAttribute WHERE IsDeleted = 0

--============================ Event for Item Attributes =================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 205, LineAttributeKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM CfgLineAttribute WHERE IsDeleted = 0

--============================ Event for Retail Profile =================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 113, RetailProfileKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM RtlRetailProfile

WHERE RetailProfileKey = @RetailProfileKey AND ISNULL( SubsidiaryKey, '0') = @SubsidiaryKey

--============================ Event for Product Image=================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 51, ProductImageKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM InvProductImages

WHERE ProductKey IN (SELECT ProductKey FROM InvProduct WHERE IsDeleted = 0 AND IsOnHold = 0 AND IsMatrixItem = 0)

--============================ Event for Exchange Rate=================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 73, ExchangeRateKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM PmtExchangeRate WHERE IsDeleted = 0

--============================ Event for Delivery Package=================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 307, DeliveryPackageKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM TrxDeliveryPackage WHERE POSKey = @POSKey

AND IsDeleted = 0

--============================ Event for Coupon=================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 94, CouponKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM ProCoupon WHERE IsDeleted = 0 AND ISNULL( SubsidiaryKey, '0') = @SubsidiaryKey

--============================ Event for User Defined Fields Detail=================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 5001, A.UserDefinedFieldDetailKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM CfgUserDefinedFieldDetail A

INNER JOIN CfgUserDefinedTable B ON B.UserDefinedTableKey = A.UserDefinedTableKey

WHERE ObjectId IN(20,46)

--============================ Event for Loyalty Config=================================

if(@IsMultipleSubsidiaryEnabled = 0)

BEGIN

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 328, LoyaltyConfigKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM CfgLoyaltyConfig

END

ELSE

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 328, LoyaltyConfigKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM CfgLoyaltyConfig

WHERE @SubsidiaryKey IN(SELECT SubsidiaryKey FROM SubSubsidiaryItem WHERE SourceKey = LoyaltyConfigKey AND SourceType = 328)

--============================ Event for Loyalty Card Information=================================

if(@IsMultipleSubsidiaryEnabled = 0)

BEGIN

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 338, LoyaltyCardInformationKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM LoyLoyaltyCardInformation Where IsDeleted = 0 AND IsActive = 1

END

ELSE

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 338, LoyaltyCardInformationKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM LoyLoyaltyCardInformation

WHERE @SubsidiaryKey IN(SELECT SubsidiaryKey FROM SubSubsidiaryItem WHERE SourceKey = LoyaltyConfigKey AND SourceType = 328) AND IsDeleted = 0 AND IsActive = 1

--============================ Event for User Defined Fields Valid Values=================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 5002, A.UserDefinedFieldValidValueKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM CfgUserDefinedFieldValidValue A

INNER JOIN CfgUserDefinedFieldDetail B ON B.UserDefinedFieldDetailKey = A.UserDefinedFieldDetailKey

INNER JOIN CfgUserDefinedTable C ON C.UserDefinedTableKey = B.UserDefinedTableKey

WHERE C.ObjectId IN(20,46)

--============================ Event for Location===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 386, LocationKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM InvLocation WHERE WarehouseKey = @WarehouseKey AND IsDeleted = 0

--============================ Event for Batch Location===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 395, BatchLocationKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM InvBatchLocation WHERE WarehouseKey = @WarehouseKey AND IsDeleted = 0

AND ProductKey IN (SELECT ProductKey FROM InvProduct WHERE IsDeleted = 0

AND IsOnHold = 0 AND IsMatrixItem = 0)

--============================ Event for Subsidiary===================================

if(@IsMultipleSubsidiaryEnabled = 1)

BEGIN

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 387, SubsidiaryKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM RtlSubsidiary

WHERE IsDeleted = 0 AND ISNULL( SubsidiaryKey, '0') = @SubsidiaryKey

END

--============================ Event for Subsidiary Item===================================

if(@IsMultipleSubsidiaryEnabled = 1)

BEGIN

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 397, SubsidiaryItemKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM SubSubsidiaryItem

WHERE ISNULL( SubsidiaryKey, '0') = @SubsidiaryKey

END

--============================ Event for ProductCost===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 274, ProductCostKey, 0, dbo.GetCompanyDateTime(), @POSKey

FROM InvProductCost

INNER JOIN InvProduct ON InvProduct.ProductKey = InvProductCost.ProductKey

WHERE InvProductCost.IsDeleted = 0 AND ISNULL( SubsidiaryKey, '0') = @SubsidiaryKey

AND InvProduct.IsOnHold = 0 AND InvProduct.IsMatrixItem = 0

AND ISNULL( WarehouseKey, 0) IN(0,@WarehouseKey)

AND (@SubsidiaryKey = 0 OR @SubsidiaryKey IN(SELECT SubsidiaryKey FROM SubSubsidiaryItem

WHERE SourceKey = InvProduct.ProductKey

AND SourceType = 46))

--============================ Event for Sales Price===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 381, SalesPriceKey, 0, dbo.GetCompanyDateTime(), @POSKey

FROM PrcSalesPrice where IsDeleted=0

--============================ Event for Discount Group===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 380, DiscountGroupKey, 0, dbo.GetCompanyDateTime(), @POSKey

FROM PrcDiscountGroup where IsDeleted=0

--============================ Event for Customer Price Group===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 378, CustomerPriceGroupKey, 0, dbo.GetCompanyDateTime(), @POSKey

FROM CusCustomerPriceGroup where IsDeleted=0

--============================ Event for Customer Discount Group===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 377, CustomerDiscountGroupKey, 0, dbo.GetCompanyDateTime(), @POSKey

FROM CusCustomerDiscountGroup where IsDeleted=0

--============================ Event for Product Discount Group===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 379, ProductDiscountGroupKey, 0, dbo.GetCompanyDateTime(), @POSKey

FROM PrcProductDiscountGroup where IsDeleted=0

--============================ Event for Store Price List===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 411, StorePriceListKey, 0, dbo.GetCompanyDateTime(), @POSKey

FROM RtlStorePriceList where IsDeleted=0

--============================ Event for Store Groups ===================================

INSERT INTO RepReplicationMobileInitialization

(SourceType, SourceKey, OperationType, ProcessDate, POSKey)

SELECT 222, StoreGroupKey, 0, dbo.GetCompanyDateTime(), @POSKey FROM RtlStoreGroup WHERE IsDeleted = 0

--============================ Set sync status for device ==========================

DECLARE @MaxSequenceNumber BIGINT

SELECT @MaxSequenceNumber = ISNULL(Max(SequenceNo), 0) FROM RepReplicationTransactionMobile

INSERT INTO RepReplicationSendStatusMobile VALUES (@pDeviceId, @MaxSequenceNumber, 0, '', 0, dbo.GetCompanyDateTime())

END TRY

BEGIN CATCH

-- Raise the Error that caused the Error

Exec usp_RethrowError

RETURN (1)

END CATCH

END

Related Objects

iVend Database Database

Send comments on this topic.