iVend Database Database > Stored Procedures > TrxCompleteTransactionUpdates Stored Procedure

Navigation: iVend Database Database > Stored Procedures >

iVend 6.6 Database Help

TrxCompleteTransactionUpdates Stored Procedure

Collapse All Expand All

iVend Database Database : TrxCompleteTransactionUpdates Stored Procedure

Properties

Creation Date	7/9/2019 11:55 AM
Encrypted
Ansi Nulls

Parameters

Parameter	Direction	Description	Data Type	Size
@pTransactionKey	In	Reference key of the Transaction	VarWChar	50
@pBatchKey	In	Reference key of the batch in which the transaction is getting saved, to maintain concurrency	VarWChar	50
@pDebug	In	Whether this procedure has to be executed in Debug mode or not	VarChar	1
@RETURN_VALUE	Return Value		Integer	4

Objects that TrxCompleteTransactionUpdates depends on

Database Object	Object Type	Description	Dep Level
ArrAccountsReceivable	Table	Defines the on Credit details of all Customers	2
ArrAccountsReceivableLog	Table	Stores the account receivable log	2
BatchQuantityUpdates	Stored Procedure		1
CfgCultureInformation	Table		2
CfgEnterprise	Table	Stores the basic configuration entries for the system . These settings are mandatory for the system to work properly.	1
CfgSiteInformation	Table	Stores the site information. Enterprise always has the site id as 1. System Table Warning : Should not be modified directly	1
CusCustomer	Table	Defines the customer details that interact with the enterprise.	1
GetCompanyDateTime	User Defined Function		2
InvAssemblyComponent	Table	Defines the Assembly set up at the console together with the parent as well as the associated products.	2
InvBatch	Table	Batch details gets affected when doing any transaction with item which has been batch tracked.	2
InvBatchLocation	Table	Describes the batch information with Location, Warehouse, Product and Expiry date	2
InvBatchLog	Table	Stores the Delta updates made to any batch in the system. This table gets affected when doing any inventory related transaction for a batch item.	1
InventoryItemQuantityUpdates	Stored Procedure		1
InvInventoryItem	Table	Stores inventory details for each Product for each Warehouse	2
InvInventoryItemLog	Table	Stores any Delta changes to the Inventory. Stores the quantity changes along with the reference of the document due to which the inventory got updated.	1
InvInventoryLocation	Table	Stores the location information for the Inventory available at different locations	2
InvProduct	Table	Stores the product related details.	2
InvProductCostUpdateLog	Table	Stores the log information for the Product cost	2
InvSerial	Table	Serial details gets affected when doing any transaction with the item which has been serial tracked.	2
InvSerialLog	Table	Stores the Delta updates made to any serial in the system. This table gets affected when doing any inventory related transaction for a serial item.	1
InvWarehouse	Table	Defines a list of all warehouses defined in the system.	2
PmtGiftCertificate	Table	Used to store the values for the Gift Certificate issued	2
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
ProPromotionBonusBuysMaster	Table	Stores the master data for the Promotion defined	1
RepReplicationTransaction	Table	Stores the Transaction details for them the data needs to be replicated	1
RtlPOS	Table	Contains details about all POS defined in the system.	1
RtlStore	Table	Contains the details of Stores defined in the system.	2
TilSession	Table	Defines all the till sessions from the time a till is assigned to a POS till the time it is finalized.	1
TilSessionDetail	Table	Defines all the till sessions from the time a till is assigned to a POS till the time it is finalized against each tender type.	2
TilSessionTransaction	Table	Defines the list of all the transactions that occur through the particular till along with the store , POS n user details.	2
TilSessionUser	Table	Stores the various till user session details.	1
TilTill	Table	Till is defined at the MC and attached with the cash drawer at POS. At a time only one till can be associated with the POS. It is the virtual entity in which we defined some settings (like setting the mode in which the till works) that will be applicable at the POS (for which the till is attached).	2
TilTillDetail	Table	Defines the details for the each till set up.	2
TrxARPayment	Table	Defines the details of the on account payments or any other settlements being done by the customer and the amount.	2
TrxGenerateLayawayInstallments	Stored Procedure	Generates Layaway installments for the Layaway transaction	2
TrxGenerateLayawayInstallmentsForSaleLevel	Stored Procedure	Generates Layaway installments for the Layaway transaction	1
TrxHandleARPayments	Stored Procedure	Handles updates related to On Account payments	1
TrxInventoryUpdateBatchNumber	Stored Procedure		1
TrxInventoryUpdateCheckNegativeInventory	Stored Procedure		1
TrxInventoryUpdateLaybyCancellationItem	Stored Procedure		1
TrxInventoryUpdateLaybyItem	Stored Procedure		1
TrxInventoryUpdateOrder	Stored Procedure	Handles updates related to Order item	1
TrxInventoryUpdatePaymentDiscount	Stored Procedure	Handles updates related to sale item	1
TrxInventoryUpdateQuotation	Stored Procedure	Handles updates related to Quotation item	1
TrxInventoryUpdateSaleDelivery	Stored Procedure	Handles updates related to delivery of a Sale item	1
TrxInventoryUpdateSaleItem	Stored Procedure	Handles updates related to sale item	1
TrxInventoryUpdateSerialNumber	Stored Procedure		1
TrxInventoryUpdatesSaleRefund	Stored Procedure	Handles updates related to On Account payments	1
TrxTransaction	Table	The main table which defined the primary details concerned with every type of transaction.	1
TrxTransactionDynamicAssemblyItem	Table	Stores sale/refund/delivery if Dynamic assmebly items attached to a transaction	2
TrxTransactionFulfillment	Table	Defines a list of all those transactions which have a fulfillment plan attached to it.	2
TrxTransactionFulfillmentDetail	Table	Defines the details of all the transactions which have a fulfillment plan attached to them.	2
TrxTransactionGiftCertificate	Table	Stores the values for the Gift certificate for the transaction	2
TrxTransactionInstallmentDetail	Table	Defines the installments details of all those transactions which have a layaway plan attached to it.	1
TrxTransactionLayaway	Table	Store layway type of transactions.	2
TrxTransactionOrder	Table	Stores information about various order booked in the system.	2
TrxTransactionPayment	Table	Stores all the payment details for each transaction.	1
TrxTransactionPromotionDetail	Table	Stores the promotion applied details on the transaction	1
TrxTransactionQuotation	Table	Stores information about various order booked in the system.	2
TrxTransactionSaleItem	Table	Stores sale/refund/delivery items attached to a transaction	1
TrxTransactionStatus	Table	Defines the transaction status with respect to various amounts that are affected by it.	1
TrxUpdateCustomerBalance	Stored Procedure		1
UpdateTillAmounts	Stored Procedure	Handles updates to Till once payment against a transaction has been taken	1
usp_RethrowError	Stored Procedure	Rethrow an error to the calling function/application	2

Procedure Source Code

--Updates all subsidary tables that gets affected after a transaction is completed. Like Inventory related updates, Till related updates etc

-- ##SUMMARY_END

CREATE PROCEDURE [dbo].[TrxCompleteTransactionUpdates]

(

@pTransactionKey NVARCHAR(50),

@pBatchKey NVARCHAR(50),

@pDebug CHAR(1)

)

BEGIN

SET NOCOUNT ON

Declare @ErrorDesc varchar(255),

@pSiteId NVARCHAR(50),

@pOutReplicationKey NVARCHAR(50),

@pOutInventoryItemLogKey NVARCHAR(50),

@InventoryItemLogKey NVARCHAR(50),

@ReplicationKey NVARCHAR(50),

@lIsCustomerActive BIT,

@lIsCustomerOnHold BIT,

@pTransactionStoreKey NVARCHAR(50),

@BusinessDate varchar(8),

@pPOSSiteId INT,

@OnAccountPaymentAmount AS NUMERIC(20,5),

@PaidAmount As Numeric (20, 5),

@HasARPayments As bit,

@HasRefunds As Bit,

@HasOrders As Bit,

@HasCancelledSalesOrder As Bit,

@HasDeliveries As Bit,

@HasFulFillments As Bit,

@HasLayawaysCancellations As Bit,

@HasLayaways As Bit,

@HasQuotations As Bit

SELECT @pSiteId = SiteId, @pPOSSiteId = ISNULL(POSSiteId, 0) FROM CfgSiteInformation

SELECT @pTransactionStoreKey = StoreKey, @BusinessDate = CONVERT(nvarchar(8), BusinessDate, 112)

FROM TrxTransaction WITH (NOLOCK)

Where TransactionKey = @pTransactionKey

SELECT @lIsCustomerActive = IsActive, @lIsCustomerOnHold = OnHold

FROM CusCustomer A WITH (NOLOCK), TrxTransaction B WITH (NOLOCK)

Where A.CustomerKey = B.CustomerKey AND B.TransactionKey = @pTransactionKey

If @lIsCustomerActive = 'FALSE' OR @lIsCustomerOnHold = 'TRUE'

BEGIN

SET @ErrorDesc = 'Customer selected is either Not Active or is OnHold.'

GOTO ERRORHANDLER

END

SELECT @InventoryItemLogKey = NEWID()

SELECT @ReplicationKey = NEWID()

--Select @InventoryItemLogKey = @InventoryItemLogKey - 1

--Select @ReplicationKey = @ReplicationKey - 1

UPDATE A

SET A.TotalRedemptionCount = A.TotalRedemptionCount + ISNULL(B.Quantity,0)

From ProPromotionBonusBuysMaster A,

(

SELECT PromotionKey, SUM(Sets) Quantity

From TrxTransactionPromotionDetail with(nolock)

Where TransactionKey = @pTransactionKey

And TransactionItemKey = '0'

GROUP by PromotionKey

) B

Where A.PromotionKey = B.PromotionKey

Select @OnAccountPaymentAmount = ISNULL(Sum(Amount), 0)

FROM TrxTransactionPayment A with(nolock), PmtPaymentType B with(nolock)

WHERE TransactionKey = @pTransactionKey

AND A.PaymentTypeKey = B.PaymentTypeKey

AND (TenderType = 4 or (A.TenderType = 10 And B.TreatAsOnAccount = 'TRUE'))

IF (@OnAccountPaymentAmount != 0)

BEGIN

Update TrxTransactionStatus

Set @PaidAmount = SalePayableAmount - ISNULL(@OnAccountPaymentAmount, 0)

,PaidAmount = @PaidAmount

,BalanceAmount = SaleAmount - @PaidAmount

WHERE TransactionKey = @pTransactionKey

END

--set @pOutReplicationKey = @ReplicationKey

--set @pOutInventoryItemLogKey = @InventoryItemLogKey

select @HasARPayments = HasARPayments, @HasOrders = HasOrders,

@HasCancelledSalesOrder = HasCancelledSalesOrder, @HasDeliveries= HasDeliveries, @HasFulFillments = HasFulFillments,

@HasLayawaysCancellations= HasLayawaysCancellations,@HasLayaways= HasLayaways, @HasQuotations=HasQuotations

from TrxTransaction Where TransactionKey = @pTransactionKey

Select @HasRefunds = Case When COUNT(1) > 0 Then 1 Else 0 End

from TrxTransaction A With(Nolock), TrxTransactionSaleItem B With(Nolock)

Where A.TransactionKey = B.TransactionKey

And A.TransactionKey = @pTransactionKey

And B.Type = 1

IF (@HasARPayments = 1)

Exec TrxHandleARPayments @pDebug, @pTransactionKey, @pSiteId, @pBatchKey, @pTransactionStoreKey

Exec TrxInventoryUpdateSaleItem @pDebug, @pTransactionKey, @pSiteId, @pBatchKey, @pTransactionStoreKey

IF (@HasRefunds = 1)

Exec TrxInventoryUpdatesSaleRefund @pDebug, @pTransactionKey, @pSiteId, @pBatchKey, @pTransactionStoreKey

IF (@HasOrders =1 OR @HasCancelledSalesOrder=1)

Exec TrxInventoryUpdateOrder @pDebug, @pTransactionKey, @pSiteId, @pBatchKey, @pTransactionStoreKey

IF (@HasDeliveries = 1 OR @HasFulFillments =1)

Exec TrxInventoryUpdateSaleDelivery @pDebug, @pTransactionKey, @pSiteId, @pBatchKey, @pTransactionStoreKey

IF (@HasLayawaysCancellations = 1 OR @HasLayaways = 1 )

Exec TrxInventoryUpdateLaybyItem @pDebug, @pTransactionKey, @pSiteId, @pBatchKey, @pTransactionStoreKey

IF (@HasLayawaysCancellations = 1 OR @HasLayaways = 1 )

Exec TrxInventoryUpdateLaybyCancellationItem @pDebug, @pTransactionKey, @pSiteId, @pBatchKey, @pTransactionStoreKey

--IF (@HasQuotations = 1) --this is done so that a small script which is part of this proc is required to be run in some cases

Exec TrxInventoryUpdateQuotation @pDebug, @pTransactionKey, @pSiteId, @pBatchKey, @pTransactionStoreKey

Exec TrxInventoryUpdateSerialNumber @pDebug, @pTransactionKey, @pSiteId, @pBatchKey, @pTransactionStoreKey

Exec TrxInventoryUpdateBatchNumber @pDebug, @pTransactionKey, @pSiteId, @pBatchKey, @pTransactionStoreKey

Exec TrxInventoryUpdatePaymentDiscount @pDebug, @pTransactionKey, @pSiteId, @pBatchKey, @pTransactionStoreKey

Exec TrxUpdateCustomerBalance @pDebug, @pTransactionKey, @pSiteId, @pBatchKey, @pTransactionStoreKey

IF EXISTS (SELECT 1 FROM CfgEnterprise WHERE ApplyLayawayOnSale = 'TRUE')

AND

EXISTS (SELECT 1 From TrxTransaction Where HasLayaways = 'TRUE' AND TransactionKey = @pTransactionKey)

BEGIN

Exec TrxGenerateLayawayInstallmentsForSaleLevel @pTransactionKey, 'N', @pSiteId, @pBatchKey

UPDATE TrxTransactionStatus

SET LayawayBalanceAmount =

(

SELECT SUM(ISNULL(Balance,0))

FROM TrxTransactionInstallmentDetail with(nolock)

WHERE TransactionKey = @pTransactionKey

)

WHERE TransactionKey = @pTransactionKey

END

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

SELECT NEWID(), 172, InventoryItemLogKey, @pBatchKey, 0, 'FALSE', CASE WHEN @pPOSSiteId !=0 THEN @pPOSSiteId ELSE @pSiteId END

FROM InvInventoryItemLog with(nolock)

WHERE SourceKey = @pTransactionKey

And SourceType IN (7, 8, 9)

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

SELECT NEWID(), 174, InventoryBatchLogKey,

@pBatchKey, 0, 'FALSE' , CASE WHEN @pPOSSiteId !=0 THEN @pPOSSiteId ELSE @pSiteId END

FROM InvBatchLog

WHERE SourceKey = @pTransactionKey

And SourceType In (7,8)

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

SELECT NEWID(), 173, InventorySerialLogKey,

@pBatchKey, 0, 'FALSE', CASE WHEN @pPOSSiteId !=0 THEN @pPOSSiteId ELSE @pSiteId END

FROM InvSerialLog

WHERE SourceKey = @pTransactionKey

And SourceType In (7,8)

--Adjust Inventory

DECLARE @lInventoryKey NVARCHAR(50)

SET @lInventoryKey = '0'

SELECT InventoryItemLogKey Into #TempInvnetoryItemLog From InvInventoryItemLog

Where SourceKey = @pTransactionKey

And SourceType IN (7 ,9, 8)

WHILE EXISTS(SELECT 1 from #TempInvnetoryItemLog)

BEGIN

SELECT TOP 1 @lInventoryKey = InventoryItemLogKey

FROM #TempInvnetoryItemLog

EXEC InventoryItemQuantityUpdates @lInventoryKey

DELETE FROM #TempInvnetoryItemLog Where InventoryItemLogKey = @lInventoryKey

END

--End Inventory

--Adjust batch Inventory

SELECT InventoryBatchLogKey Into #TempInventoryBatchLog

From InvBatchLog

Where SourceKey = @pTransactionKey

And SourceType in (7, 8)

WHILE EXISTS(SELECT 1 from #TempInventoryBatchLog)

BEGIN

SELECT TOP 1 @lInventoryKey = InventoryBatchLogKey

FROM #TempInventoryBatchLog

EXEC BatchQuantityUpdates @lInventoryKey

DELETE FROM #TempInventoryBatchLog Where InventoryBatchLogKey = @lInventoryKey

END

--End Inventory

---since all the invnetory updates are complete by now...check for negatove inventory

Exec TrxInventoryUpdateCheckNegativeInventory @pDebug, @pTransactionKey, @pSiteId

IF EXISTS(SELECT 1 FROM TrxTransactionPayment WITH (NOLOCK) WHERE TransactionKey = @pTransactionKey)

And EXISTS (Select 1 From RtlPOS A With (NOLOCK), TrxTransaction B Where A.POSKey = B.POSKey And B.TransactionKey = @pTransactionKey)

BEGIN

Declare @lModifiedBy NVARCHAR(50),

@lPayableAmount DECIMAL(20, 5),

@lUserKey NVARCHAR(50),

@lTillSessionKey NVARCHAR(50),

@lPOSKey NVARCHAR(50)

-- Get the required data from Transaction Header

SELECT @lModifiedBy = ModifiedBy, @lPayableAmount = PayableAmount, @lUserKey = UserKey, @lPOSKey = POSKey

FROM TrxTransaction WITH (NOLOCK)

WHERE TransactionKey = @pTransactionKey

SET @lTillSessionKey = '0'

-- Get Active Till Session for User

SELECT @lTillSessionKey = TillSessionKey

FROM TilSessionUser

WHERE POSKey = @lPOSKey

AND UserKey = @lUserKey

AND Status = 0 -- OPEN

IF @lTillSessionKey = '0'

BEGIN

SELECT @lTillSessionKey = TillSessionKey

FROM TilSession WHERE POSKey = @lPOSKey

AND CONVERT(nvarchar(8), BusinessDate, 112) = @BusinessDate

AND STATUS = 0

END

-- Till related Updates

Exec UpdateTillAmounts @pTillSessionKey = @lTillSessionKey,

@pUserKey = @lUserKey,

@pComments = '',

@pReasonCodeKey = '0',

@pTransactionType = 2,

@pSourceDocumentKey = @pTransactionKey,

@pAmount = @lPayableAmount,

@pModifiedBy = @lModifiedBy,

@pBatchKey = @pBatchKey,

@pStoreKey = @pTransactionStoreKey,

@pSessionTransaction = 0

IF(@@ERROR <>0)

BEGIN

SET @ErrorDesc = 'Error while updating Till'

GOTO ERRORHANDLER

END

-- Tipple Point Updates

EXEC DataNotification @pSourceType = 143, @pSourceKey = @pTransactionKey, @pBatchKey = @pBatchKey, @pOperationType = 0, @pUserKey = -1, @pDebug= 'false'

RETURN

ERRORHANDLER:

RAISERROR('%s',16,-1,@ErrorDesc)

RETURN

END

Related Objects

iVend Database Database

Send comments on this topic.