iVend Database Database > Stored Procedures > ReplicationRecordsToSend Stored Procedure

Navigation: iVend Database Database > Stored Procedures >

iVend 6.6 Database Help

ReplicationRecordsToSend Stored Procedure

Collapse All Expand All

iVend Database Database : ReplicationRecordsToSend Stored Procedure

Properties

Creation Date	8/6/2019 1:34 PM
Encrypted
Ansi Nulls

Parameters

Parameter	Direction	Description	Data Type	Size
@RETURN_VALUE	Return Value		Integer	4

Objects that ReplicationRecordsToSend depends on

Database Object	Object Type	Description	Dep Level
CfgSiteInformation	Table	Stores the site information. Enterprise always has the site id as 1. System Table Warning : Should not be modified directly	1
InvBatchLocation	Table	Describes the batch information with Location, Warehouse, Product and Expiry date	1
InvBatchLocationLog	Table	Stores each record processed for the batch with location deifned.	1
InvGoodReceipt	Table	Stores the Goods receipt done in the system.	1
InvGoodsReturn	Table	Stores the data related to Inventory Goods Return	1
InvInventoryLocation	Table	Stores the location information for the Inventory available at different locations	1
InvInventoryLocationLog	Table	Stores the logs or Audit information of location for the Inventory available at different locations	1
InvLocation	Table	Save the location infomation.	1
InvLocationStockTransfer	Table	Stores the Inter location document information.	1
InvStockTransfer	Table	Stores stock transfer details	1
InvStockTransferRequest	Table	Defines the information for the generation of the Stock Transfer request	1
InvWarehouse	Table	Defines a list of all warehouses defined in the system.	1
RepObjectMapDB	Table	System table. Do not modify	1
RepReplicationTransaction	Table	Stores the Transaction details for them the data needs to be replicated	1
RepReplicationTransactionDetail	Table		1
RtlStore	Table	Contains the details of Stores defined in the system.	1

Procedure Source Code

CREATE PROCEDURE [dbo].[ReplicationRecordsToSend]

BEGIN

SET NOCOUNT ON;

DECLARE @CfgSiteID INT, @POSSiteID INT, @SystemMode NVARCHAR(50), @isOnPremise BIT;

Select ReplicationTransactionDetailKey, Status Into #TempModifiedKey

From RepReplicationTransactionDetail Where 1= 0

SELECT @CfgSiteID = SiteID

FROM CfgSiteInformation;

SELECT @POSSiteID = POSSiteId

FROM CfgSiteInformation;

IF(@CfgSiteID = '1'

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

BEGIN

SET @SystemMode = 'ENTERPRISE';

UPDATE RepReplicationTransaction

SET

StoreId = 1

WHERE StoreId IS NULL;

END;

ELSE

IF(@CfgSiteID = '1'

AND ISNULL(@POSSiteID, '0') != '0')

BEGIN

SET @SystemMode = 'OFFLINEPOS';

UPDATE RepReplicationTransaction

SET

StoreId = @POSSiteID

WHERE StoreId IS NULL;

END;

ELSE

IF(@CfgSiteID != '1'

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

BEGIN

SET @SystemMode = 'STORE';

UPDATE RepReplicationTransaction

SET

StoreId = @CfgSiteID

WHERE StoreId IS NULL;

END;

ELSE

IF(@CfgSiteID != '1'

AND ISNULL(@POSSiteID, '0') != '0')

BEGIN

SET @SystemMode = 'OFFLINEPOS';

UPDATE RepReplicationTransaction

SET

StoreId = @POSSiteID

WHERE StoreId IS NULL;

END;

IF(@SystemMode = 'OFFLINEPOS')

BEGIN

SELECT @isOnPremise = IsOnPremise

FROM RtlStore

WHERE SiteId = @CfgSiteID;

END;

--move the master and integration event at last

IF @SystemMode != 'ENTERPRISE'

BEGIN

Select BatchKey , Max(ProcessDate) MaxProcessDate into #TempBatchUpdate

From RepReplicationTransaction With (nolock)

Where SendDumpSequenceNumber Is NULL

And BatchKey != '0'

Group By BatchKey

Update A

Set A.ProcessDate = DATEADD(MILLISECOND, 100, MaxProcessDate)

From RepReplicationTransaction A, #TempBatchUpdate B

Where A.BatchKey = B.BatchKey

And A.SourceType in (34, 39, 53, 97, 143, 201, 215, 307, 389)

Update A

Set A.ProcessDate = DATEADD(MILLISECOND, 300, MaxProcessDate)

From RepReplicationTransaction A, #TempBatchUpdate B

Where A.BatchKey = B.BatchKey

And A.SourceType in (501)

END

--THIS CHECK IS PUT SO THAT THE REPLICATION OF THE INTEGRATION EVENTS IS NOT DONE FROM ENTERPRISE TO STORE OR OFFLINE POS

IF(@SystemMode = 'ENTERPRISE')

BEGIN

UPDATE RepReplicationTransaction

SET

StoreId = 1

WHERE StoreId IS NULL;

UPDATE RepReplicationTransaction

SET

SendDumpSequenceNumber = 0

WHERE SourceType = 501

AND SendDumpSequenceNumber IS NULL;

END;

---THIS IS TO UPDATE THE RECORDS WHICH DOES NOT HAVE A SOURCE KEY

UPDATE RepReplicationTransaction

SET SendDumpSequenceNumber = 0

WHERE SourceKey = '0'

AND SendDumpSequenceNumber IS NULL;

---UPDATE THE Batch Key of UDF records

UPDATE RepReplicationTransaction

SET BatchKey = '0'

WHERE SourceType IN(5000, 5001, 5002, 5003)

AND SendDumpSequenceNumber IS NULL

IF @SystemMode = 'ENTERPRISE'

BEGIN

DELETE A

FROM RepReplicationTransaction A WITH(NOLOCK), InvLocation B WITH(NOLOCK), InvWarehouse C WITH(NOLOCK)

WHERE A.SourceKey = B.LocationKey

AND B.WarehouseKey = C.WarehouseKey

AND C.WarehouseType = 0

AND A.SourceType = 386;

DELETE A

FROM RepReplicationTransaction A WITH(NOLOCK), InvInventoryLocation B WITH(NOLOCK), InvWarehouse C WITH(NOLOCK)

WHERE A.SourceKey = B.InventoryLocationKey

AND B.WarehouseKey = C.WarehouseKey

AND C.WarehouseType = 0

AND A.SourceType = 393;

DELETE A

FROM RepReplicationTransaction A WITH(NOLOCK), InvInventoryLocationLog B WITH(NOLOCK), InvWarehouse C WITH(NOLOCK)

WHERE A.SourceKey = B.InventoryLocationLogKey

AND B.WarehouseKey = C.WarehouseKey

AND C.WarehouseType = 0

AND A.SourceType = 394;

DELETE A

FROM RepReplicationTransaction A WITH(NOLOCK), InvBatchLocation B WITH(NOLOCK), InvWarehouse C WITH(NOLOCK)

WHERE A.SourceKey = B.BatchLocationKey

AND B.WarehouseKey = C.WarehouseKey

AND C.WarehouseType = 0

AND A.SourceType = 395;

DELETE A

FROM RepReplicationTransaction A WITH(NOLOCK), InvBatchLocationLog B WITH(NOLOCK), InvWarehouse C WITH(NOLOCK)

WHERE A.SourceKey = B.BatchLocationLogKey

AND B.WarehouseKey = C.WarehouseKey

AND C.WarehouseType = 0

AND A.SourceType = 396;

END;

SELECT TOP 50000 X.ReplicationTransactionKey,

X.SourceType,

X.SourceKey,

X.BatchKey,

X.OperationType,

X.Flag,

ISNULL(X.StoreID,

CASE

WHEN @SystemMode = 'OFFLINEPOS'

THEN @POSSiteID

ELSE @CfgSiteID

END) StoreId,

X.FromSBO,

X.Sender,

X.Receiver,

X.SeqenceNo,

Y.TableName,

Y.TablePrimaryColumn,

'N' PStatus,

'S2E' Direction,

Y.TableType,

Y.CanUpdate

INTO #TempRecordsToSend

FROM RepReplicationTransaction X

INNER JOIN RepObjectMapDB Y WITH(NOLOCK) ON X.SourceType = Y.ObjectID

WHERE X.SendDumpSequenceNumber IS NULL

AND X.SourceKey != '0'

ORDER BY SeqenceNo;

---THIS WILL BREAK UP THIS RECORD FOR EACH OF THE STORES

IF @SystemMode = 'ENTERPRISE'

BEGIN

---insert event to for connected stores

INSERT INTO RepReplicationTransactionDetail

(ReplicationTransactionDetailKey,

ReplicationTransactionKey,

STATUS,

DestinationSiteId

)

SELECT NEWID(),

A.ReplicationTransactionKey,

b.SiteId

FROM #TempRecordsToSend A

LEFT OUTER JOIN RtlStore B ON 1 = 1

LEFT OUTER JOIN RepReplicationTransactionDetail C ON A.ReplicationTransactionKey = C.ReplicationTransactionKey

AND B.SiteId = c.DestinationSiteId

WHERE B.IsOnPremise = 1

AND B.SiteId != ISNULL(A.StoreId, 1)

AND B.StoreType = 0

AND B.IsDeleted = 0

AND C.DestinationSiteId IS NULL

AND A.SourceType NOT IN(386, 393, 394, 395, 396);

---insert event for offline POS that are connected to me or connected to non-onpremise store

INSERT INTO RepReplicationTransactionDetail

(ReplicationTransactionDetailKey,

ReplicationTransactionKey,

STATUS,

DestinationSiteId

)

SELECT NEWID(),

A.ReplicationTransactionKey,

d.POSSiteId

FROM #TempRecordsToSend A

LEFT OUTER JOIN RtlStore B ON 1 = 1

INNER JOIN RtlPOS D ON b.StoreKey = d.StoreKey

AND d.IsOfflineEnabled = 1

AND D.IsDeleted = 0

LEFT OUTER JOIN RepReplicationTransactionDetail C ON A.ReplicationTransactionKey = C.ReplicationTransactionKey

AND D.POSSiteId = c.DestinationSiteId

WHERE(B.IsOnPremise = 0

OR B.SiteId = '1') --connected to non-onpremise store OR if offline POS is connected to enterprise in a mixed mode

AND B.StoreType = 0

AND B.IsDeleted = 0

AND C.DestinationSiteId IS NULL

AND A.SourceType NOT IN(386, 393, 394, 395, 396);

---insert event for location related events

BEGIN

INSERT INTO RepReplicationTransactionDetail

(ReplicationTransactionDetailKey,

ReplicationTransactionKey,

STATUS,

DestinationSiteId

)

SELECT NEWID(),

A.ReplicationTransactionKey,

b.SiteId

FROM #TempRecordsToSend A

LEFT OUTER JOIN RtlStore B ON 1 = 1

LEFT OUTER JOIN RepReplicationTransactionDetail C ON A.ReplicationTransactionKey = C.ReplicationTransactionKey

AND B.SiteId = c.DestinationSiteId

INNER JOIN InvLocation L WITH(NOLOCK) ON A.SourceKey = L.LocationKey

WHERE B.IsOnPremise = 1

AND B.SiteId != ISNULL(A.StoreId, 1)

AND B.StoreType = 0

AND B.IsDeleted = 0

AND C.DestinationSiteId IS NULL

AND A.SourceType = 386

AND B.WarehouseKey = L.WarehouseKey;

---insert location event for offline POS that are connected to me or connected to non-onpremise store

INSERT INTO RepReplicationTransactionDetail

(ReplicationTransactionDetailKey,

ReplicationTransactionKey,

STATUS,

DestinationSiteId

)

SELECT NEWID(),

A.ReplicationTransactionKey,

d.POSSiteId

FROM #TempRecordsToSend A

LEFT OUTER JOIN RtlStore B ON 1 = 1

INNER JOIN RtlPOS D ON b.StoreKey = d.StoreKey

AND d.IsOfflineEnabled = 1

AND D.IsDeleted = 0

LEFT OUTER JOIN RepReplicationTransactionDetail C ON A.ReplicationTransactionKey = C.ReplicationTransactionKey

AND D.POSSiteId = c.DestinationSiteId

INNER JOIN InvLocation L WITH(NOLOCK) ON A.SourceKey = L.LocationKey

WHERE(B.IsOnPremise = 0

OR B.SiteId = '1') --connected to non-onpremise store OR if offline POS is connected to enterprise in a mixed mode

AND B.StoreType = 0

AND B.IsDeleted = 0

AND C.DestinationSiteId IS NULL

AND A.SourceType = 386

AND B.WarehouseKey = L.WarehouseKey;

END;

---insert event for inventory location related events

BEGIN

INSERT INTO RepReplicationTransactionDetail

(ReplicationTransactionDetailKey,

ReplicationTransactionKey,

STATUS,

DestinationSiteId

)

SELECT NEWID(),

A.ReplicationTransactionKey,

b.SiteId

FROM #TempRecordsToSend A

LEFT OUTER JOIN RtlStore B ON 1 = 1

LEFT OUTER JOIN RepReplicationTransactionDetail C ON A.ReplicationTransactionKey = C.ReplicationTransactionKey

AND B.SiteId = c.DestinationSiteId

INNER JOIN InvInventoryLocation L WITH(NOLOCK) ON A.SourceKey = L.InventoryLocationKey

WHERE B.IsOnPremise = 1

AND B.SiteId != ISNULL(A.StoreId, 1)

AND B.StoreType = 0

AND B.IsDeleted = 0

AND C.DestinationSiteId IS NULL

AND A.SourceType = 393

AND B.WarehouseKey = L.WarehouseKey;

---insert location event for offline POS that are connected to me or connected to non-onpremise store

INSERT INTO RepReplicationTransactionDetail

(ReplicationTransactionDetailKey,

ReplicationTransactionKey,

STATUS,

DestinationSiteId

)

SELECT NEWID(),

A.ReplicationTransactionKey,

d.POSSiteId

FROM #TempRecordsToSend A

LEFT OUTER JOIN RtlStore B ON 1 = 1

INNER JOIN RtlPOS D ON b.StoreKey = d.StoreKey

AND d.IsOfflineEnabled = 1

AND D.IsDeleted = 0

LEFT OUTER JOIN RepReplicationTransactionDetail C ON A.ReplicationTransactionKey = C.ReplicationTransactionKey

AND D.POSSiteId = c.DestinationSiteId

INNER JOIN InvInventoryLocation L WITH(NOLOCK) ON A.SourceKey = L.InventoryLocationKey

WHERE(B.IsOnPremise = 0

OR B.SiteId = '1') --connected to non-onpremise store OR if offline POS is connected to enterprise in a mixed mode

AND B.StoreType = 0

AND B.IsDeleted = 0

AND C.DestinationSiteId IS NULL

AND A.SourceType = 393

AND B.WarehouseKey = L.WarehouseKey;

END;

---insert event for inventory location log related events

BEGIN

INSERT INTO RepReplicationTransactionDetail

(ReplicationTransactionDetailKey,

ReplicationTransactionKey,

STATUS,

DestinationSiteId

)

SELECT NEWID(),

A.ReplicationTransactionKey,

b.SiteId

FROM #TempRecordsToSend A

LEFT OUTER JOIN RtlStore B ON 1 = 1

LEFT OUTER JOIN RepReplicationTransactionDetail C ON A.ReplicationTransactionKey = C.ReplicationTransactionKey

AND B.SiteId = c.DestinationSiteId

INNER JOIN InvInventoryLocationLog L WITH(NOLOCK) ON A.SourceKey = L.InventoryLocationLogKey

WHERE B.IsOnPremise = 1

AND B.SiteId != ISNULL(A.StoreId, 1)

AND B.StoreType = 0

AND B.IsDeleted = 0

AND C.DestinationSiteId IS NULL

AND A.SourceType = 394

AND B.WarehouseKey = L.WarehouseKey;

---insert location log event for offline POS that are connected to me or connected to non-onpremise store

INSERT INTO RepReplicationTransactionDetail

(ReplicationTransactionDetailKey,

ReplicationTransactionKey,

STATUS,

DestinationSiteId

)

SELECT NEWID(),

A.ReplicationTransactionKey,

d.POSSiteId

FROM #TempRecordsToSend A

LEFT OUTER JOIN RtlStore B ON 1 = 1

INNER JOIN RtlPOS D ON b.StoreKey = d.StoreKey

AND d.IsOfflineEnabled = 1

AND D.IsDeleted = 0

LEFT OUTER JOIN RepReplicationTransactionDetail C ON A.ReplicationTransactionKey = C.ReplicationTransactionKey

AND D.POSSiteId = c.DestinationSiteId

INNER JOIN InvInventoryLocationLog L WITH(NOLOCK) ON A.SourceKey = L.InventoryLocationLogKey

WHERE(B.IsOnPremise = 0

OR B.SiteId = '1') --connected to non-onpremise store OR if offline POS is connected to enterprise in a mixed mode

AND B.StoreType = 0

AND B.IsDeleted = 0

AND C.DestinationSiteId IS NULL

AND A.SourceType = 394

AND B.WarehouseKey = L.WarehouseKey;

END;

---insert event for inventory batch location related events

BEGIN

INSERT INTO RepReplicationTransactionDetail

(ReplicationTransactionDetailKey,

ReplicationTransactionKey,

STATUS,

DestinationSiteId

)

SELECT NEWID(),

A.ReplicationTransactionKey,

b.SiteId

FROM #TempRecordsToSend A

LEFT OUTER JOIN RtlStore B ON 1 = 1

LEFT OUTER JOIN RepReplicationTransactionDetail C ON A.ReplicationTransactionKey = C.ReplicationTransactionKey

AND B.SiteId = c.DestinationSiteId

INNER JOIN InvBatchLocation L WITH(NOLOCK) ON A.SourceKey = L.BatchLocationKey

WHERE B.IsOnPremise = 1

AND B.SiteId != ISNULL(A.StoreId, 1)

AND B.StoreType = 0

AND B.IsDeleted = 0

AND C.DestinationSiteId IS NULL

AND A.SourceType = 395

AND B.WarehouseKey = L.WarehouseKey;

---insert location log event for offline POS that are connected to me or connected to non-onpremise store

INSERT INTO RepReplicationTransactionDetail

(ReplicationTransactionDetailKey,

ReplicationTransactionKey,

STATUS,

DestinationSiteId

)

SELECT NEWID(),

A.ReplicationTransactionKey,

d.POSSiteId

FROM #TempRecordsToSend A

LEFT OUTER JOIN RtlStore B ON 1 = 1

INNER JOIN RtlPOS D ON b.StoreKey = d.StoreKey

AND d.IsOfflineEnabled = 1

AND D.IsDeleted = 0

LEFT OUTER JOIN RepReplicationTransactionDetail C ON A.ReplicationTransactionKey = C.ReplicationTransactionKey

AND D.POSSiteId = c.DestinationSiteId

INNER JOIN InvBatchLocation L WITH(NOLOCK) ON A.SourceKey = L.BatchLocationKey

WHERE(B.IsOnPremise = 0

OR B.SiteId = '1') --connected to non-onpremise store OR if offline POS is connected to enterprise in a mixed mode

AND B.StoreType = 0

AND B.IsDeleted = 0

AND C.DestinationSiteId IS NULL

AND A.SourceType = 395

AND B.WarehouseKey = L.WarehouseKey;

END;

---insert event for inventory batch location log related events

BEGIN

INSERT INTO RepReplicationTransactionDetail

(ReplicationTransactionDetailKey,

ReplicationTransactionKey,

STATUS,

DestinationSiteId

)

SELECT NEWID(),

A.ReplicationTransactionKey,

b.SiteId

FROM #TempRecordsToSend A

LEFT OUTER JOIN RtlStore B ON 1 = 1

LEFT OUTER JOIN RepReplicationTransactionDetail C ON A.ReplicationTransactionKey = C.ReplicationTransactionKey

AND B.SiteId = c.DestinationSiteId

INNER JOIN InvBatchLocationLog L WITH(NOLOCK) ON A.SourceKey = L.BatchLocationLogKey

WHERE B.IsOnPremise = 1

AND B.SiteId != ISNULL(A.StoreId, 1)

AND B.StoreType = 0

AND B.IsDeleted = 0

AND C.DestinationSiteId IS NULL

AND A.SourceType = 396

AND B.WarehouseKey = L.WarehouseKey;

---insert location log event for offline POS that are connected to me or connected to non-onpremise store

INSERT INTO RepReplicationTransactionDetail

(ReplicationTransactionDetailKey,

ReplicationTransactionKey,

STATUS,

DestinationSiteId

)

SELECT NEWID(),

A.ReplicationTransactionKey,

d.POSSiteId

FROM #TempRecordsToSend A

LEFT OUTER JOIN RtlStore B ON 1 = 1

INNER JOIN RtlPOS D ON b.StoreKey = d.StoreKey

AND d.IsOfflineEnabled = 1

AND D.IsDeleted = 0

LEFT OUTER JOIN RepReplicationTransactionDetail C ON A.ReplicationTransactionKey = C.ReplicationTransactionKey

AND D.POSSiteId = c.DestinationSiteId

INNER JOIN InvBatchLocationLog L WITH(NOLOCK) ON A.SourceKey = L.BatchLocationLogKey

WHERE(B.IsOnPremise = 0

OR B.SiteId = '1') --connected to non-onpremise store OR if offline POS is connected to enterprise in a mixed mode

AND B.StoreType = 0

AND B.IsDeleted = 0

AND C.DestinationSiteId IS NULL

AND A.SourceType = 396

AND B.WarehouseKey = L.WarehouseKey;

END;

ELSE

IF @SystemMode = 'STORE'

BEGIN

INSERT INTO RepReplicationTransactionDetail

(ReplicationTransactionDetailKey,

ReplicationTransactionKey,

STATUS,

DestinationSiteId

)

SELECT NEWID(),

A.ReplicationTransactionKey,

'1'

FROM #TempRecordsToSend A

LEFT OUTER JOIN RepReplicationTransactionDetail B ON A.ReplicationTransactionKey = B.ReplicationTransactionKey

WHERE B.ReplicationTransactionKey IS NULL

AND ISNULL(A.StoreId, @CfgSiteID) != 1;

INSERT INTO RepReplicationTransactionDetail

(ReplicationTransactionDetailKey,

ReplicationTransactionKey,

STATUS,

DestinationSiteId

)

SELECT NEWID(),

A.ReplicationTransactionKey,

D.POSSiteId

FROM #TempRecordsToSend A

INNER JOIN RtlStore C ON C.SiteId = @CfgSiteID

AND C.IsDeleted = 0

INNER JOIN RtlPOS D ON C.StoreKey = D.StoreKey

AND D.IsOfflineEnabled = 1

AND D.IsDeleted = 0

LEFT OUTER JOIN RepReplicationTransactionDetail B ON A.ReplicationTransactionKey = B.ReplicationTransactionKey

AND D.POSSiteId = B.DestinationSiteId

WHERE B.DestinationSiteId IS NULL;

--AND B.ReplicationTransactionKey IS NULL

END;

ELSE

IF @SystemMode = 'OFFLINEPOS'

BEGIN

INSERT INTO RepReplicationTransactionDetail

(ReplicationTransactionDetailKey,

ReplicationTransactionKey,

STATUS,

DestinationSiteId

)

SELECT NEWID(),

A.ReplicationTransactionKey,

CASE

WHEN @isOnPremise = 1

THEN @CfgSiteID

ELSE 1

END

FROM #TempRecordsToSend A

LEFT OUTER JOIN RepReplicationTransactionDetail B ON A.ReplicationTransactionKey = B.ReplicationTransactionKey

WHERE B.ReplicationTransactionKey IS NULL;

END;

IF @SystemMode != 'OFFLINEPOS'

BEGIN

INSERT INTO #TempModifiedKey

Select A.ReplicationTransactionDetailKey, 2

FROM RepReplicationTransactionDetail A, RtlPOS B, RepObjectMapDB C, RepReplicationTransaction D

WHERE A.DestinationSiteId = B.POSSiteId

AND A.ReplicationTransactionKey = D.ReplicationTransactionKey

AND C.ObjectID = D.SourceType

AND ISNULL(C.IgnoreForOfflinePOS, 0) = 1

AND D.SendDumpSequenceNumber IS NULL

AND B.IsOfflineEnabled = 1

AND B.POSSiteId > 100000

AND B.IsDeleted = 0

END;

IF @SystemMode = 'ENTERPRISE'

BEGIN

----get all the purchase orders from the temp and then see if they are to be replicated or not

--Select distinct D.SiteId, B.ReplicationTransactionKey, B.BatchKey into #TempPurchaseOrder

--from PurPurchaseOrder A, RepReplicationTransaction B, PurPurchaseOrderDetail C, RtlStore D

--Where A.PurchaseOrderKey = B.SourceKey

--And A.PurchaseOrderKey = C.PurchaseOrderKey

--And B.SourceType = 97 --PURCHASE ORDER

--And C.WarehouseKey = D.WarehouseKey

--AND B.SendDumpSequenceNumber is NULL

--Update C Set Status = 2

--From #TempPurchaseOrder A , RepReplicationTransaction B, RepReplicationTransactionDetail C

--Where A.BatchKey = B.BatchKey

--And B.ReplicationTransactionKey = C.ReplicationTransactionKey

--And A.SiteId != C.DestinationSiteId

--And B.SourceType In (97, 98)

--get all the goods receipt from the temp and then see if they are to be replicated or not

SELECT ISNULL(C.SiteId, 0) SiteId,

B.ReplicationTransactionKey,

B.BatchKey

INTO #TempGoodsReceipt

FROM InvGoodReceipt A

INNER JOIN RepReplicationTransaction B ON A.GoodsReceiptKey = B.SourceKey

AND B.SourceType = 34 --GOODS RECEIPT

AND A.SourceType IN(0, 2) --0 GOODS RECEIPT/ 1- GRPO/ 2- GOODS AGAINST ST

LEFT OUTER JOIN RtlStore C ON A.ToWarehouseKey = C.WarehouseKey

INNER JOIN RepReplicationTransactionDetail E ON B.ReplicationTransactionKey = E.ReplicationTransactionKey

WHERE E.STATUS = 0;

Insert into #TempModifiedKey

Select C.ReplicationTransactionDetailKey, 2

FROM #TempGoodsReceipt A, RepReplicationTransaction B, RepReplicationTransactionDetail C

WHERE A.BatchKey = B.BatchKey

AND B.ReplicationTransactionKey = C.ReplicationTransactionKey

AND A.SiteId != C.DestinationSiteId

AND B.SourceType IN(34, 35, 36, 37);

--get all the GI from the temp and then see if they are to be replicated or not

SELECT DISTINCT

ISNULL(C.SiteId, 0) SiteId,

B.ReplicationTransactionKey,

B.BatchKey

INTO #TempGoodsReturn

FROM InvGoodsReturn A

INNER JOIN RepReplicationTransaction B ON A.GoodsReturnKey = B.SourceKey

AND B.SourceType = 201 --GOODS RETURN

LEFT OUTER JOIN RtlStore C ON A.WarehouseKey = C.WarehouseKey

INNER JOIN RepReplicationTransactionDetail E ON B.ReplicationTransactionKey = E.ReplicationTransactionKey

WHERE E.STATUS = 0;

Insert into #TempModifiedKey

Select C.ReplicationTransactionDetailKey, 2

FROM #TempGoodsReturn A, RepReplicationTransaction B, RepReplicationTransactionDetail C

WHERE A.BatchKey = B.BatchKey

AND B.ReplicationTransactionKey = C.ReplicationTransactionKey

AND A.SiteId != C.DestinationSiteId

AND B.SourceType IN(201, 202, 203, 204);

--get all the STOCK TRANSFER from the temp and then see if they are to be replicated or not

SELECT DISTINCT

ISNULL(C.SiteId, 0) FromSiteId,

ISNULL(D.SiteId, 0) ToSiteId,

B.ReplicationTransactionKey,

B.BatchKey

INTO #TempStockTransfer

FROM InvStockTransfer A

INNER JOIN RepReplicationTransaction B ON A.StockTransferKey = B.SourceKey

LEFT OUTER JOIN RtlStore C ON A.FromWarehouseKey = C.WarehouseKey

LEFT OUTER JOIN RtlStore D ON A.ToWarehouseKey = D.WarehouseKey

INNER JOIN RepReplicationTransactionDetail E ON B.ReplicationTransactionKey = E.ReplicationTransactionKey

WHERE E.STATUS = 0

AND B.SourceType = 53;

Insert into #TempModifiedKey

Select C.ReplicationTransactionDetailKey, 2

FROM #TempStockTransfer A, RepReplicationTransaction B, RepReplicationTransactionDetail C

WHERE A.BatchKey = B.BatchKey

AND B.ReplicationTransactionKey = C.ReplicationTransactionKey

AND C.DestinationSiteId NOT IN(A.FromSiteId, A.ToSiteId)

AND B.SourceType IN(53, 54, 55, 56);

--get all the LOCATION STOCK TRANSFER from the temp and then see if they are to be replicated or not

SELECT DISTINCT

ISNULL(C.SiteId, 0) SiteId,

B.ReplicationTransactionKey,

B.BatchKey

INTO #TempStockTransferLocation

FROM InvLocationStockTransfer A

INNER JOIN RepReplicationTransaction B ON A.LocationStockTransferKey = B.SourceKey

LEFT OUTER JOIN RtlStore C ON A.WarehouseKey = C.WarehouseKey

INNER JOIN RepReplicationTransactionDetail E ON B.ReplicationTransactionKey = E.ReplicationTransactionKey

WHERE E.STATUS = 0

AND B.SourceType = 389;

Insert into #TempModifiedKey

Select C.ReplicationTransactionDetailKey, 2

FROM #TempStockTransferLocation A, RepReplicationTransaction B, RepReplicationTransactionDetail C

WHERE A.BatchKey = B.BatchKey

AND B.ReplicationTransactionKey = C.ReplicationTransactionKey

AND C.DestinationSiteId != A.SiteId

AND B.SourceType IN(389, 390, 391, 392);

--get all the STOCK TRANSFER REQUEST from the temp and then see if they are to be replicated or not

SELECT DISTINCT

ISNULL(C.SiteId, 0) FromSiteId,

ISNULL(D.SiteId, 0) ToSiteId,

B.ReplicationTransactionKey,

B.BatchKey

INTO #TempStockTransferRequest

FROM InvStockTransferRequest A

INNER JOIN RepReplicationTransaction B ON A.StockTransferRequestKey = B.SourceKey

LEFT OUTER JOIN RtlStore C ON A.FromWarehouseKey = C.WarehouseKey

LEFT OUTER JOIN RtlStore D ON A.ToWarehouseKey = D.WarehouseKey

INNER JOIN RepReplicationTransactionDetail E ON B.ReplicationTransactionKey = E.ReplicationTransactionKey

WHERE E.STATUS = 0

AND B.SourceType = 215;

Insert into #TempModifiedKey

Select C.ReplicationTransactionDetailKey, 2

FROM #TempStockTransferRequest A, RepReplicationTransaction B, RepReplicationTransactionDetail C

WHERE A.BatchKey = B.BatchKey

AND B.ReplicationTransactionKey = C.ReplicationTransactionKey

AND C.DestinationSiteId NOT IN(A.FromSiteId, A.ToSiteId)

AND B.SourceType IN(215, 216);

Create Table #TempTransactionLocation (SiteId Int , ReplicationTransactionKey Nvarchar(50), BatchKey Nvarchar(50))

Insert into #TempTransactionLocation

SELECT DISTINCT

ISNULL(C.SiteId, 0) SiteId,

B.ReplicationTransactionKey,

B.BatchKey

FROM TrxTransaction A

INNER JOIN #TempRecordsToSend T0 On A.TransactionKey = T0.SourceKey And T0.SourceType = 143

INNER JOIN RepReplicationTransaction B with(nolock) ON A.TransactionKey = B.SourceKey

LEFT OUTER JOIN RtlStore C with(nolock) ON A.StoreKey = C.StoreKey

INNER JOIN RepReplicationTransactionDetail E with(nolock) ON B.ReplicationTransactionKey = E.ReplicationTransactionKey

WHERE E.STATUS = 0

AND B.SourceType = 143

Insert into #TempTransactionLocation

SELECT DISTINCT

ISNULL(C.SiteId, 0) SiteId,

B.ReplicationTransactionKey,

B.BatchKey

FROM TrxTransaction A

INNER JOIN #TempRecordsToSend TR On A.TransactionKey = TR.SourceKey And TR.SourceType = 143

LEFT OUTER JOIN TrxTransactionSaleItem T0 with(nolock) On A.TransactionKey = T0.TransactionKey

INNER JOIN RepReplicationTransaction B with(nolock) ON A.TransactionKey = B.SourceKey

LEFT OUTER JOIN RtlStore C with(nolock) ON T0.DeliveryWarehouseKey = C.WarehouseKey

INNER JOIN RepReplicationTransactionDetail E with(nolock) ON B.ReplicationTransactionKey = E.ReplicationTransactionKey

WHERE E.STATUS = 0

AND B.SourceType = 143;

Insert into #TempTransactionLocation

SELECT DISTINCT

ISNULL(C.SiteId, 0) SiteId,

B.ReplicationTransactionKey,

B.BatchKey

FROM TrxTransaction A

INNER JOIN #TempRecordsToSend TR On A.TransactionKey = TR.SourceKey And TR.SourceType = 143

LEFT OUTER JOIN TrxTransactionOrder T0 with(nolock) On A.TransactionKey = T0.TransactionKey

INNER JOIN RepReplicationTransaction B with(nolock) ON A.TransactionKey = B.SourceKey

LEFT OUTER JOIN RtlStore C with(nolock) ON T0.DeliveryWarehouseKey = C.WarehouseKey

INNER JOIN RepReplicationTransactionDetail E with(nolock) ON B.ReplicationTransactionKey = E.ReplicationTransactionKey

WHERE E.STATUS = 0

AND B.SourceType = 143;

Insert into #TempTransactionLocation

SELECT DISTINCT

ISNULL(C.SiteId, 0) SiteId,

B.ReplicationTransactionKey,

B.BatchKey

FROM TrxTransaction A

INNER JOIN #TempRecordsToSend TR On A.TransactionKey = TR.SourceKey And TR.SourceType = 143

LEFT OUTER JOIN TrxTransactionLayaway T0 with(nolock) On A.TransactionKey = T0.TransactionKey

INNER JOIN RepReplicationTransaction B with(nolock) ON A.TransactionKey = B.SourceKey

LEFT OUTER JOIN RtlStore C with(nolock) ON T0.DeliveryWarehouseKey = C.WarehouseKey

INNER JOIN RepReplicationTransactionDetail E with(nolock) ON B.ReplicationTransactionKey = E.ReplicationTransactionKey

WHERE E.STATUS = 0

AND B.SourceType = 143;

Insert Into #TempModifiedKey

Select Distinct C.ReplicationTransactionDetailKey, 2

FROM #TempTransactionLocation A, RepReplicationTransaction B, RepReplicationTransactionDetail C, RepobjectMapDb D

WHERE A.BatchKey = B.BatchKey

AND B.ReplicationTransactionKey = C.ReplicationTransactionKey

AND C.DestinationSiteId NOT IN (SELECT SiteId from #TempTransactionLocation F Where A.batchkey = f.batchkey)

And B.SourceType = D.ObjectId

AND D.UseForInterStore = 1

END;

Insert into RepReplicationTransactionDetailArchive

Select B.ReplicationTransactionDetailKey, B.ReplicationTransactionKey, A.Status, B.SentTime, B.ReceiveTime, DestinationSiteId

From #TempModifiedKey A, RepReplicationTransactionDetail B

Where A.ReplicationTransactionDetailKey = B.ReplicationTransactionDetailKey

Delete A from RepReplicationTransactionDetail A, #TempModifiedKey B

Where A.ReplicationTransactionDetailKey = B.ReplicationTransactionDetailKey

SELECT * FROM #TempRecordsToSend;

SELECT COUNT(1) RecordCount,

DestinationSiteId

FROM RepReplicationTransactionDetail WITH(NOLOCK)

WHERE STATUS = 0

GROUP BY DestinationSiteId;

RETURN;

END;

Related Objects

iVend Database Database

Send comments on this topic.