iVend Database Database : ReplicationMobileMonitor Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pDestinationSiteID

In

The DeviceId which is to receive data.

VarWChar

100

@pOutRecordCount

Out

Integer

4

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

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

CREATE PROCEDURE [dbo].[ReplicationMobileMonitor]

@pDestinationSiteID NVARCHAR(100),

@pOutRecordCount INT OUT

AS

BEGIN

SET NOCOUNT ON

Declare @lSendSequence   bigint,

  @lSendTransactionKey bigint,

  @lReplicationSequence bigint,

  @lLastSequenceNo bigint ,

  @processedRecord INT,

  @unProcessedRecord INT

Select @lLastSequenceNo = 0

Select @lLastSequenceNo = LastSequenceNo from RepReplicationSendStatusMobile

  Where DeviceId = @pDestinationSiteID

Set @pOutRecordCount =

(Select Count(*) From RepReplicationTransactionMobile Where SequenceNo > @lLastSequenceNo)

Select TOP 300 X.*

From

(

-- FAILED RECORDS

Select R.SequenceNo, R.ReplicationTransactionMobileKey [ReplicationKey], R.SourceKey,

  R.ProcessDate, 'Failed' Status, Cast(S.LastErrorString as nvarchar) 'Error', R.SourceType, R.OperationType

From RepReplicationTransactionMobile R with (nolock)

  Left outer join RepObjectMapDB M with (nolock) on R.SourceType = M.ObjectID

  Inner Join RepReplicationSendStatusMobile S with (nolock)

  On S.LastErrorRecordNo = R.SequenceNo

And  S.DeviceId = @pDestinationSiteID

UNION

-- RECORDS NOT PROCESSED

Select Top 300 R.SequenceNo, R.ReplicationTransactionMobileKey [ReplicationKey], R.SourceKey,

  R.ProcessDate, 'Not Processed'  Status, '' Error, R.SourceType, R.OperationType

From RepReplicationTransactionMobile R with (nolock)

  Left outer join RepObjectMapDB M with (nolock) on R.SourceType = M.ObjectID

Where SequenceNo > @lLastSequenceNo

) X

Order by X.SequenceNo, X.ReplicationKey

END