iVend Database Database : DataNotification Stored Procedure

Creation Date

8/1/2019 7:08 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pSourceType

In

Object type for which the data has been updated

Integer

4

@pSourceKey

In

Reference key of the Source object

VarWChar

50

@pBatchKey

In

Reference key of the transaction batch

VarWChar

50

@pOperationType

In

Integer

4

@pUserKey

In

The user who has made the changes

VarWChar

50

@pDebug

In

Whether this procedure has to be executed in Debug mode or not

Boolean

1

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

CfgAuditLog

Table

Stores system logs.

2

CfgAuditLogFilter

Table

System Table. Defines for which columns the logs will be captured.

2

CfgAuditLogMaster

Table

System Table. Stores the list of tables for which the system logs are generated.

1

CfgEnterprise

Table

Stores the basic configuration entries for the system . These settings are mandatory for the system to work properly.

1

CfgRegisteredApplication

Table

Stores the third party Registered applications in the iVend.

1

CfgRegisteredApplicationEvents

Table

Stores the enabled Module list of registered application in ivend. For these module event would be generate.

1

CfgSiteInformation

Table

Stores the site information. Enterprise always has the site id as 1. System Table Warning : Should not be modified directly

1

CfgUserDefinedTable

Table

Stores the Users defined tables

1

GetCompanyDateTime

User Defined Function

2

GetNextSiteNumber

Stored Procedure

2

InvSerial

Table

Serial details gets affected when doing any transaction with the item which has been serial tracked.

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

LogAuditInformation

Stored Procedure

1

RepIntegrationLog

Table

Stores the Integration records for the SAP

1

RepObjectMapDB

Table

System table. Do not modify

1

RepReplicationSendStatusMobile

Table

Table used to track the replication record status

1

RepReplicationTransaction

Table

Stores the Transaction details for them the data needs to be replicated

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

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

CREATE PROCEDURE [dbo].[DataNotification]

(

@pSourceType INT,

@pSourceKey   NVARCHAR(50),

@pBatchKey   NVARCHAR(50),

@pOperationType INT,

@pUserKey   NVARCHAR(50),

@pDebug   BIT = 'FALSE'

)

AS

BEGIN

  SET NOCOUNT ON

  DECLARE @lErrorDesc VARCHAR(255),

  @lNewKey NVARCHAR(50),

  @lSiteId INT,

  @lOwnerShipType INT,

  @lStoreCount INT,

  @lMobileCount INT,

  @TableName VARCHAR(255),

  @PrimaryKey VARCHAR(255),

  @RecordXML  XML,

  @Sql   NVARCHAR(1000),

  @lPOSSiteId INT,

  @RetailProfileKey NVARCHAR(50),

  @ProfileData XML,

  @ButtonPanelKey NVARCHAR(50),

  @lMobileLocations INT

  IF @pSourceType = 0 -- NONE

      RETURN

  --BEGIN TRY

      SELECT @lSiteId = SiteId,

              @lPOSSiteId = ISNULL(POSSiteId, 0)

      FROM    CfgSiteInformation

  IF @@ROWCOUNT = 0

          RAISERROR ( '%s', 16,- 1, 'Site Not Found' )

  if @pSourceType = 399 and @pOperationType = 0 --this is to update the entry in the audit log master

  BEGIN

  Update B Set B.ObjectName = A.TableName

  From CfgUserDefinedTable A, CfgAuditLogMaster B

  Where A.TableName = B.TableName

  And B.AuditLogMasterKey = @pSourceKey

  And A.IsHistoryTracked = 1

  END

      if (@lSiteId > 1 or @lPOSSiteId > 1)

  begin

  set @lStoreCount = 1

  end

else

  begin

  Set @lStoreCount = 0

  SELECT @lStoreCount = ISNULL(@lStoreCount,0) + COUNT(1)

  FROM    RtlStore

  WHERE   SiteId > 1

  SELECT @lStoreCount = ISNULL(@lStoreCount,0) + COUNT(1)

  FROM    RtlPOS

  WHERE   IsDeleted = 0

  AND  POSType = 1

  if (@lSiteId = 1)

    begin

    Select @lStoreCount = ISNULL(@lStoreCount,0) + Count(1)

    From RtlStore A, RtlPos B

    Where A.StoreKey = B.StoreKey

    And A.IsOnPremise = 0

    And B.IsOfflineEnabled = 1

    And A.IsDeleted = 0

    And B.IsDeleted = 0

    Select @lStoreCount = ISNULL(@lStoreCount,0) + Count(1)

    From RtlStore A, RtlPos B

    Where A.StoreKey = B.StoreKey

    And A.SiteId = 1

    And B.IsOfflineEnabled = 1

    And A.IsDeleted = 0

    And B.IsDeleted = 0

    end

end

if @pSourceType = 52

  Update InvSerial Set OriginalSerialKey = SerialKey Where SerialKey = @pSourceKey And ISNULL(OriginalSerialKey, '0') = '0'

      EXEC dbo.LogAuditInformation @pSourceType, @pSourceKey, @pOperationType, @pUserKey, @lSiteId, @pBatchKey

      SELECT @lOwnerShipType = OwnerShipType

      FROM    RtlStore s,

               InvWarehouse w

      WHERE   s.WarehouseKey = w.WarehouseKey

AND  s.SiteId = @lSiteId

      IF @@ROWCOUNT = 0

          SET @lOwnerShipType = 0 -- COCO

      IF @pDebug = 'TRUE'

          SELECT '@lSiteId' = @lSiteId, '@lOwnerShipType' = @lOwnerShipType, '@lPOSSiteId' = @lPOSSiteId

--THIS IS DONE SO THAT THE SYSTEM DOES NOT GENERATE THE REPLICATION ENTRY FOR THE TRANSACTION CONSOLIDATION

      IF @pSourceType != 200 AND @lStoreCount > 0

      BEGIN

  IF @lOwnerShipType = 2

                  AND @pSourceType IN ( 97, 98, 99, 100, 101, 102, 34, 35, 36, 37, 201, 202, 203, 204, 39, 40, 41, 42, 167, 168 )

                  RETURN

  IF @lSiteId = 1

    BEGIN

    Select @lMobileLocations = COUNT(1) FROM RtlPOS

    WHERE POSType = 1 AND IsDeleted = 0 AND DataSourceType = 1

    Select @lMobileLocations = ISNULL(@lMobileLocations, 0) + COUNT(1)

    FROM RtlPOS A, RtlStore B WHERE A.StoreKey = b.StoreKey

    AND B.SiteId = 1 AND A.POSType = 1 AND A.IsDeleted = 0 AND A.DataSourceType = 2

    END

  ELSE

    BEGIN

    Select @lMobileLocations = count(1)

    FROM RtlPOS A, RtlStore B WHERE A.StoreKey = B.StoreKey

    AND B.SiteId = @lSiteId AND A.POSType = 1 AND A.IsDeleted = 0 AND DataSourceType = 2

    END

  IF ISNULL(@lMobileLocations, 0) >0

  BEGIN

    -- Delete device status which are not exists anymore.

    DELETE FROM RepReplicationSendStatusMobile WHERE DeviceId NOT IN (SELECT ISNULL(HardwareId, '') FROM RtlPOS WHERE POSType <> 0)

    -- Delete queue which are already synced on all devices.

    set rowcount 10000

    DELETE FROM RepReplicationTransactionMobile WHERE SequenceNo <= (SELECT ISNULL(MIN(LastSequenceNo), 0) FROM RepReplicationSendStatusMobile)

    set rowcount 0

    -- Delete queue for old records of current object to generate new events.

    DELETE FROM RepReplicationTransactionMobile WHERE SourceType = @pSourceType AND SourceKey = @pSourceKey

  END

  -- GENERATE NEXT NUMBER

              SELECT @lNewKey = NEWID()

              --EXEC GetNextSiteNumber @lSiteId, 'Table.ReplicationTransaction', @lNewKey OUTPUT, 0, @lPOSSiteId

  -- INSERT INTO REPLICATION DETAIL TABLE

  INSERT INTO RepReplicationTransaction ( ReplicationTransactionKey, SourceType, SourceKey, BatchKey, OperationType, Flag, StoreId )

  SELECT

    @lNewKey, @pSourceType, @pSourceKey, CASE WHEN Priority = 1 THEN '0' ELSE @pBatchKey END,

    @pOperationType, 'FALSE' , CASE WHEN ISNULL(@lPOSSiteId,'0') ='0' THEN @lSiteId ELSE @lPOSSiteId END

  FROM RepObjectMapDB

  WHERE ObjectID = @pSourceType

  IF ISNULL(@lMobileLocations, 0) >0

  BEGIN

    -- INSERT INTO MOBILE REPLICATION DETAIL TABLE FOR RETAIL PROFILE.

    IF (@pSourceType = 108) --POS UPDATE

    BEGIN

    INSERT INTO RepReplicationTransactionMobile(SourceType, SourceKey, OperationType)

    SELECT distinct 191, a.ProfileData.value('(/RetailProfile/ButtonPanelKey/node())[1]', 'nvarchar(max)'), 1

    FROM RtlRetailProfile A, RtlPOS b

    Where b.POSKey = @pSourceKey

    and @pSourceType = 108

    and b.RetailProfileKey = a.RetailProfileKey

    and a.ProfileData.value('(/RetailProfile/ButtonPanelKey/node())[1]', 'nvarchar(max)') != '0'

    END

    IF (@pSourceType = 114) --STORE UPDATE

    BEGIN

    INSERT INTO RepReplicationTransactionMobile(SourceType, SourceKey, OperationType)

    SELECT distinct 191, a.ProfileData.value('(/RetailProfile/ButtonPanelKey/node())[1]', 'nvarchar(max)'), 1

    FROM RtlRetailProfile A, RtlStore b

    Where b.StoreKey = @pSourceKey

    and @pSourceType = 114

    and b.RetailProfileKey = a.RetailProfileKey

    and a.ProfileData.value('(/RetailProfile/ButtonPanelKey/node())[1]', 'nvarchar(max)') != '0'

    END

    IF (@pSourceType = 8) --ENTERPRISE UPDATE

    BEGIN

    INSERT INTO RepReplicationTransactionMobile(SourceType, SourceKey, OperationType)

    SELECT distinct 191, a.ProfileData.value('(/RetailProfile/ButtonPanelKey/node())[1]', 'nvarchar(max)'), 1

    FROM RtlRetailProfile A, CfgEnterprise b

    Where b.EnterpriseKey = @pSourceKey

    and @pSourceType = 8

    and b.RetailProfileKey = a.RetailProfileKey

    and a.ProfileData.value('(/RetailProfile/ButtonPanelKey/node())[1]', 'nvarchar(max)') != '0'

    END

    IF (@pSourceType = 113)

    BEGIN

    INSERT INTO RepReplicationTransactionMobile(SourceType, SourceKey, OperationType)

    SELECT distinct 191, ProfileData.value('(/RetailProfile/ButtonPanelKey/node())[1]', 'nvarchar(max)'), 1

    FROM RtlRetailProfile

    WHERE @pSourceType = 113

    and RetailProfileKey = @pSourceKey

    and ProfileData.value('(/RetailProfile/ButtonPanelKey/node())[1]', 'nvarchar(max)') != '0'

    END

  END

          END

  --If the Integration user has made the changes then do not generated the integration entry again

  --User with user key 2 is the Integration User.... (iVendAPI)

      IF @pUserKey = '2'

          RETURN

      IF @pUserKey != '-1' AND @pSourceType = 143 --Transaction

          RETURN

DECLARE @lApplicationKey NVARCHAR(50)

DECLARE db_cursor CURSOR FOR

Select A.ApplicationKey

From CfgRegisteredApplication A, CfgRegisteredApplicationEvents B

Where A.ApplicationKey = B.ApplicationKey

And B.ObjectId = @pSourceType

And A.IsDeleted = 0

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @lApplicationKey

WHILE @@FETCH_STATUS = 0

BEGIN

  SELECT @lNewKey = NEWID()

  INSERT INTO RepIntegrationLog (IntegrationKey, SourceType, SourceKey, BatchKey, OperationType, Flag, ApplicationKey, StoreId, [Status])

  VALUES( @lNewKey, @pSourceType, @pSourceKey, @pBatchKey, @pOperationType, 'FALSE', @lApplicationKey, @lSiteId, 0)

  INSERT INTO RepReplicationTransaction ( ReplicationTransactionKey, SourceType, SourceKey, BatchKey, OperationType, Flag, StoreId )

  SELECT NEWID(), 501, @lNewKey, @pBatchKey, 0, 'FALSE' , CASE WHEN ISNULL(@lPOSSiteId,'0') ='0' THEN @lSiteId ELSE @lPOSSiteId END

    FETCH NEXT FROM db_cursor INTO @lApplicationKey

END

CLOSE db_cursor

DEALLOCATE db_cursor

  --END TRY

  --BEGIN CATCH

  --    EXECUTE usp_RethrowError

  --END CATCH

END