iVend Database Database : TrxUpdateCustomerBalance Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pDebug

In

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

VarChar

1

@pTransactionKey

In

Reference key of the Transaction

VarWChar

50

@pSiteId

In

Reference key of the Site

VarWChar

50

@pBatchKey

In

Reference key of the batch in which the transaction is getting saved, to maintain concurrency

VarWChar

50

@pTransactionStoreKey

In

Reference key of Store

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

TrxCompleteTransactionUpdates

Stored Procedure

1

Database Object

Object Type

Description

Dep Level

ArrAccountsReceivable

Table

Defines the on Credit details of all Customers

1

ArrAccountsReceivableLog

Table

Stores the account receivable log

1

CfgCultureInformation

Table

1

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

GetCompanyDateTime

User Defined Function

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

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

RepReplicationTransaction

Table

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

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

TrxARPayment

Table

Defines the details of the on account payments or any other settlements being done by the customer and the amount.

1

TrxTransaction

Table

The main table which defined the primary details concerned with every type of transaction.

1

TrxTransactionFulfillment

Table

Defines a list of all those transactions which have a fulfillment plan attached to it.

1

TrxTransactionFulfillmentDetail

Table

Defines the details of all the transactions which have a fulfillment plan attached to them.

1

TrxTransactionLayaway

Table

Store layway type of transactions.

1

TrxTransactionOrder

Table

Stores information about various order booked in the system.

1

TrxTransactionPayment

Table

Stores all the payment details for each transaction.

1

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

TrxTransactionSurcharge

Table

Provides a reference of all the surcharges & other details attached with any transaction.

1

CREATE PROCEDURE [dbo].[TrxUpdateCustomerBalance]

(

@pDebug     CHAR(1),

@pTransactionKey NVARCHAR(50),

@pSiteId   NVARCHAR(50),

@pBatchKey   NVARCHAR(50),

@pTransactionStoreKey NVARCHAR(50)

)

AS

BEGIN

SET NOCOUNT ON

Declare @ErrorDesc varchar(255),

  @modifiedBy NVARCHAR(50),

  @modified DateTime,

  @beforeInventoryItemLogKey NVARCHAR(50),

  @lUpdatedBalanceAmount DECIMAL(20,5),

  @lUpdatedOrderBalanceAmount DECIMAL(20,5),

  @lAccountsReceivableKey NVARCHAR(50),

  @lUpdatedBalance DECIMAL(20,5),

  @lUpdatedLayawayBalanceAmount DECIMAL(20,5),

  @lLayawayDepositAmount DECIMAL(20,5),

  @lPOSSiteId INT,

  @lSubsidiaryKey NVARCHAR(50)

  Set @modified = dbo.GetCompanyDateTime()

  Select @modifiedBy = ModifiedBy, @modified = Modified

  FROM TrxTransaction Where TransactionKey = @pTransactionKey

  SELECT @lSubsidiaryKey = B.SubsidiaryKey FROM RtlStore A

  INNER JOIN InvWarehouse B ON A.WarehouseKey = B.WarehouseKey And A.IsDeleted = 0

  Where A.StoreKey = @pTransactionStoreKey

  Select @lAccountsReceivableKey = AccountsReceivableKey

  FROM ArrAccountsReceivable

  Where CustomerKey = (Select CustomerKey From TrxTransaction Where TransactionKey = @pTransactionKey)

  And SubsidiaryKey = @lSubsidiaryKey

  SELECT @lPOSSiteId = ISNULL(POSSiteId, 0) FROM CfgSiteInformation

  ---1 --PAYMENT ON ACCOUNT

  ---2 --AR PAYMENT

  ---3 --MISSING

  ---4 --DELIVERY AGAINST SO

  ---5 --REDUCING SO OPEN BALANCE BECAUSE OF DELIVERY

  ---6 --REDUCING SO OPEN BALANCE BECAUSE OF SO EDIT

  ---7 --INCREASE SO OPEN BALANCE BECAUSE OF NEW SO / (AFTER EDIT)

  ---8 --DELIVERY AGAINST LAYAWAY

  ---9 --REDUCING LAYAWAY OPEN BALANCE BECAUSE OF DELIVERY

  ---10 --CREATION OF LAYAWAY

  ---11 --Deposit For LAYAWAY

  ---12 --AMOUT RETURNED FOR LAYAWAY CANCELLATION

  ---13   -- Manual Adjustment

  ---14 --AMOUNT OF DELIVERY SURCHARGE NOT FOR ADVANCE PAYMENT

DECLARE @AccountReceivableLogKey NVARCHAR(50)

--- UPDATE THE CUSTOMER BALANCE IN CASE THE CURRENT TRANSACTION HAS ON ACCOUNT TENDER----

IF EXISTS

(

SELECT 1

From TrxTransactionPayment A, PmtPaymentType B

WHERE A.TransactionKey = @pTransactionKey

And A.PaymentTypeKey = B.PaymentTypeKey

And (A.TenderType = 4 OR (B.Type = 10 And B.TreatAsOnAccount = 1))

)

BEGIN

--SELECT @AccountReceivableLogKey = NEWID()

SELECT @AccountReceivableLogKey = NEWID()

INSERT INTO ArrAccountsReceivableLog(AccountsReceivableLogKey, AccountsReceivableKey, Amount, Currency, ExchangeRate, TransactionKey, EntryType, Created)

SELECT @AccountReceivableLogKey, @lAccountsReceivableKey , SUM(Amount), A.CurrencyKey, ExchangeRate, TransactionKey, 1, dbo.GetCompanyDateTime()

From TrxTransactionPayment A, PmtPaymentType B

WHERE A.TransactionKey = @pTransactionKey

And A.PaymentTypeKey = B.PaymentTypeKey

And (A.TenderType = 4 OR (B.Type = 10 And B.TreatAsOnAccount = 1))

group by A.CurrencyKey, ExchangeRate, TransactionKey

END

IF EXISTS

(

  SELECT 1 FROM TrxTransactionSurcharge A,

  (

  Select Distinct B.FulfillmentKey

  From   TrxTransactionFulfillment B With (nolock), TrxTransactionFulfillmentDetail C With (nolock)

  Where B.FulfillmentKey = C.FulfillmentKey

  And B.TransactionKey = @pTransactionKey

  And C.SourceType = 0

  ) M

  Where A.TransactionKey = @pTransactionKey

  And A.PayInAdvance = 0

  And A.SourceType = 7

  And A.IsNewSurcharge = 1

  And A.SourceKey = M.FulfillmentKey

  And A.IsPaymentDiscountReversalSurcharge = 0

)

BEGIN

--SELECT @AccountReceivableLogKey = NEWID()

SELECT @AccountReceivableLogKey = NEWID()

INSERT INTO ArrAccountsReceivableLog(AccountsReceivableLogKey, AccountsReceivableKey, Amount, Currency, ExchangeRate, TransactionKey, EntryType, Created)

SELECT @AccountReceivableLogKey, @lAccountsReceivableKey, ISNULL(SUM(A.Amount),0) As Amount, 1, 1, @pTransactionKey, 14,  dbo.GetCompanyDateTime()

FROM TrxTransactionSurcharge A,

 (

  Select Distinct B.FulfillmentKey

  From   TrxTransactionFulfillment B With (nolock), TrxTransactionFulfillmentDetail C With (nolock)

  Where B.FulfillmentKey = C.FulfillmentKey

  And B.TransactionKey = @pTransactionKey

  And C.SourceType = 0

 ) M

Where A.TransactionKey = @pTransactionKey

And A.PayInAdvance = 0

And A.SourceType = 7

And A.IsNewSurcharge = 1

And A.SourceKey = M.FulfillmentKey

And A.IsPaymentDiscountReversalSurcharge = 0

END

---GENERATE ENTRY FOR THE ADVANCE IF PART OF CURRENT TRANSACTION

IF EXISTS

(

SELECT 1

From TrxARPayment A

WHERE A.TransactionKey = @pTransactionKey

)

BEGIN

SELECT @AccountReceivableLogKey = NEWID()

INSERT INTO ArrAccountsReceivableLog(AccountsReceivableLogKey , AccountsReceivableKey, Amount, Currency, ExchangeRate, TransactionKey, EntryType, Created)

SELECT @AccountReceivableLogKey, @lAccountsReceivableKey , -1 * SUM(ISNULL(Amount,0)), 1, 1, TransactionKey, 2, dbo.GetCompanyDateTime()

From TrxARPayment A

WHERE A.TransactionKey = @pTransactionKey

GROUP BY TransactionKey

END

---SOME PART OF THE ABOVE PAYMENT MIGHT BE ON ACCOUNT OF

---GENERATE ENTRY FOR THE ADVANCE GIVEN FOR LAYAWAY AS THAT INFORMATION DOES NOT GO TO ARPAYMENT TABLE

IF EXISTS

(

SELECT 1

From TrxTransactionStatus A

WHERE A.TransactionKey = @pTransactionKey

And LayawayDepositAmount > 0

)

BEGIN

SELECT @AccountReceivableLogKey = NEWID()

INSERT INTO ArrAccountsReceivableLog(AccountsReceivableLogKey , AccountsReceivableKey, Amount, Currency, ExchangeRate, TransactionKey, EntryType, Created)

SELECT @AccountReceivableLogKey, @lAccountsReceivableKey , -1 * LayawayDepositAmount, 1, 1, TransactionKey, 11, dbo.GetCompanyDateTime()

From TrxTransactionStatus A

WHERE A.TransactionKey = @pTransactionKey

END

---GENERATE ENTRY FOR THE ADVANCE GIVEN FOR LAYAWAY AS THAT INFORMATION DOES NOT GO TO ARPAYMENT TABLE

IF EXISTS

(

SELECT 1

From TrxTransactionStatus A, TrxTransactionLayaway B

WHERE B.OriginalDocumentKey = A.TransactionKey

And B.TransactionKey = @pTransactionKey

And B.Type = 1

And A.LayawayPaidAmount > 0

)

BEGIN

SELECT @AccountReceivableLogKey = NEWID()

INSERT INTO ArrAccountsReceivableLog(AccountsReceivableLogKey , AccountsReceivableKey, Amount, Currency, ExchangeRate, TransactionKey, EntryType, Created)

SELECT @AccountReceivableLogKey, @lAccountsReceivableKey , MIN(ISNULL(LayawayPaidAmount,0)), 1, 1, B.TransactionKey, 12, dbo.GetCompanyDateTime()

From TrxTransactionStatus A, TrxTransactionLayaway B

WHERE B.OriginalDocumentKey = A.TransactionKey

And B.TransactionKey = @pTransactionKey

And B.Type = 1

And A.LayawayPaidAmount > 0

Group by B.TransactionKey

END

---GENERATE ENTRY FOR THE DELIVERY AGAINST THE SALES ORDER

IF EXISTS

(

SELECT 1

From TrxTransactionSaleItem A

WHERE A.TransactionKey = @pTransactionKey

AND A.Type = 3 --INDICATES THAT THE delivery is done against SO

)

BEGIN

SELECT @AccountReceivableLogKey = NEWID()

--get the total surcharges on the original transaction against which the delivery is made

Select A.TransactionKey -- SELECT ISNULL(((SUM(0) / CASE (MIN(0) - Sum(0)) WHEN 0 THEN 1 ELSE 1 END )*  Sum(0)),0) + SUM(0) NetAmountToSettle

 ,ISNULL(((SUM(ISNULL(b.TotalPostSaleDiscount,0)) / (CASE (MIN(ISNULL(A.SpecialOrderAmount,0)) - Sum(ISNULL(C.Amount,0))) WHEN 0 THEN 1 ELSE (MIN(ISNULL(A.SpecialOrderAmount,0)) - SUM(ISNULL(C.Amount,0))) END))* SUM(ISNULL(C.Amount,0))),0) + SUM(ISNULL(b.TotalPostSaleDiscount,0)) NetAmountToSettle

 , SUM(ISNULL(b.TotalPostSaleDiscount,0)) TotalPostSaleDiscount

Into #TempTransaction

FROM TrxTransactionStatus A inner join TrxTransactionSaleItem B

  on A.TransactionKey = B.OriginalDocumentKey

  And B.Type = 3

  LEFT OUTER JOIN TrxTransactionSurcharge C

  On A.TransactionKey = C.TransactionKey

  And C.SourceType = 7

Where B.TransactionKey = @pTransactionKey

group by A.TransactionKey

INSERT INTO ArrAccountsReceivableLog(AccountsReceivableLogKey , AccountsReceivableKey,Amount, Currency, ExchangeRate, TransactionKey, EntryType, Created)

SELECT @AccountReceivableLogKey, @lAccountsReceivableKey ,Sum(NetAmountToSettle), 1, 1, @pTransactionKey, 4, dbo.GetCompanyDateTime()

From #TempTransaction A

SELECT @AccountReceivableLogKey = NEWID()

INSERT INTO ArrAccountsReceivableLog(AccountsReceivableLogKey , AccountsReceivableKey, Amount, Currency, ExchangeRate, TransactionKey, EntryType, Created)

SELECT @AccountReceivableLogKey, @lAccountsReceivableKey ,-1 * Sum(ISNULL(NetAmountToSettle,0)), 1, 1, @pTransactionKey, 5, dbo.GetCompanyDateTime()

From #TempTransaction A

END

---GENERATE ENTRY FOR THE DELIVERY AGAINST THE LAYAWAY

IF EXISTS

(

SELECT 1

From TrxTransactionSaleItem A

WHERE A.TransactionKey = @pTransactionKey

AND A.Type = 5 --INDICATES THAT THE delivery is done against LAYAWAY

)

BEGIN

--get the total surcharges on the original transaction against which the delivery is made

Select A.TransactionKey

 ,ISNULL(((SUM(ISNULL(b.TotalPostSaleDiscount,0)) / (CASE (MIN(ISNULL(A.SpecialOrderAmount,0)) - Sum(ISNULL(C.Amount,0))) WHEN 0 THEN 1 ELSE (MIN(ISNULL(A.SpecialOrderAmount,0)) - SUM(ISNULL(C.Amount,0))) END))* SUM(ISNULL(C.Amount,0))),0) + SUM(ISNULL(b.TotalPostSaleDiscount,0)) NetAmountToSettle

--,ISNULL(((SUM(ISNULL(b.TotalPostSaleDiscount,0)) / (MIN(ISNULL(A.SpecialOrderAmount,0)) - Sum(ISNULL(C.Amount,0))))*  Sum(ISNULL(C.Amount,0))),0) + SUM(ISNULL(b.TotalPostSaleDiscount,0)) NetAmountToSettle

 , SUM(b.TotalPostSaleDiscount) TotalPostSaleDiscount

Into #TempTransactionLayaway

FROM TrxTransactionStatus A inner join TrxTransactionSaleItem B

  on A.TransactionKey = B.OriginalDocumentKey

  And B.Type = 5 --INDICATES THAT THE delivery is done against LAYAWAY

  LEFT OUTER JOIN TrxTransactionSurcharge C

  On A.TransactionKey = C.TransactionKey

  And C.SourceType = 7

Where B.TransactionKey = @pTransactionKey

group by A.TransactionKey

SELECT @AccountReceivableLogKey = NEWID()

INSERT INTO ArrAccountsReceivableLog(AccountsReceivableLogKey , AccountsReceivableKey,Amount, Currency, ExchangeRate, TransactionKey, EntryType, Created)

SELECT @AccountReceivableLogKey, @lAccountsReceivableKey ,Sum(NetAmountToSettle), 1, 1, @pTransactionKey, 8, dbo.GetCompanyDateTime()

From #TempTransactionLayaway A

SELECT @AccountReceivableLogKey = NEWID()

INSERT INTO ArrAccountsReceivableLog(AccountsReceivableLogKey , AccountsReceivableKey, Amount, Currency, ExchangeRate, TransactionKey, EntryType, Created)

SELECT @AccountReceivableLogKey, @lAccountsReceivableKey ,-1 * Sum(NetAmountToSettle), 1, 1, @pTransactionKey, 9, dbo.GetCompanyDateTime()

From #TempTransactionLayaway A

END

---GENERATE ENTRY FOR THE NEW SALES ORDER CREATION

IF EXISTS

(

  SELECT 1

  From TrxTransactionOrder A

  Where A.TransactionKey = @pTransactionKey

)

BEGIN

SELECT @AccountReceivableLogKey = NEWID()

INSERT INTO ArrAccountsReceivableLog(AccountsReceivableLogKey , AccountsReceivableKey,Amount, Currency, ExchangeRate, TransactionKey, EntryType, Created)

SELECT @AccountReceivableLogKey, @lAccountsReceivableKey , -1 * SUM(ISNULL(TotalPostSaleDiscount * OpenQuantity / Quantity, 0)), 1,1, @pTransactionKey ,6 ,dbo.GetCompanyDateTime()

From TrxTransactionOrder A

Where TransactionKey = (Select Distinct OriginalDocumentKey From TrxTransactionOrder Where TransactionKey = @pTransactionKey and OriginalDocumentKey != '0')

And Quantity > 0

GROUP BY A.TransactionKey

SELECT @AccountReceivableLogKey = NEWID()

INSERT INTO ArrAccountsReceivableLog(AccountsReceivableLogKey ,AccountsReceivableKey, Amount, Currency, ExchangeRate, TransactionKey, EntryType, Created)

SELECT @AccountReceivableLogKey, @lAccountsReceivableKey ,SUM(CASE WHEN Quantity = 0 THEN 0 ELSE ISNULL(TotalPostSaleDiscount * OpenQuantity / Quantity,0) END), 1, 1, TransactionKey ,7 ,dbo.GetCompanyDateTime()

From TrxTransactionOrder A

Where A.TransactionKey = @pTransactionKey

GROUP BY A.TransactionKey

END

---GENERATE ENTRY FOR THE NEW LAYWAY CREATION

IF EXISTS

(

  SELECT 1

  From TrxTransactionLayaway A

  Where A.TransactionKey = @pTransactionKey

  And Type = 0

)

BEGIN

SELECT @AccountReceivableLogKey = NEWID()

INSERT INTO ArrAccountsReceivableLog(AccountsReceivableLogKey ,AccountsReceivableKey, Amount, Currency, ExchangeRate, TransactionKey, EntryType, Created)

SELECT @AccountReceivableLogKey, @lAccountsReceivableKey , SUM(CASE WHEN Quantity = 0 THEN 0 ELSE ISNULL(TotalPostSaleDiscount * OpenQuantity / Quantity,0) END), 1, 1, TransactionKey ,10 ,dbo.GetCompanyDateTime()

From TrxTransactionLayaway A

Where A.TransactionKey = @pTransactionKey

GROUP BY A.TransactionKey

END

---GENERATE ENTRY FOR THE LAYWAY CANCELLATION

IF EXISTS

(

  SELECT 1

  From TrxTransactionLayaway A

  Where A.TransactionKey = @pTransactionKey

  And Type = 1

)

BEGIN

SELECT @AccountReceivableLogKey = NEWID()

INSERT INTO ArrAccountsReceivableLog(AccountsReceivableLogKey ,AccountsReceivableKey, Amount, Currency, ExchangeRate, TransactionKey, EntryType, Created)

SELECT @AccountReceivableLogKey, @lAccountsReceivableKey , -1 * SUM(CASE WHEN Quantity = 0 THEN 0 ELSE ISNULL(TotalPostSaleDiscount * OpenQuantity / Quantity,0) END), 1, 1, TransactionKey ,10 ,dbo.GetCompanyDateTime()

From TrxTransactionLayaway A

Where A.TransactionKey = @pTransactionKey

GROUP BY A.TransactionKey

END

IF EXISTS

(

SELECT 1 FROM ArrAccountsReceivableLog

Where TransactionKey = @pTransactionKey

)

BEGIN

  SELECT @lUpdatedBalanceAmount = SUM(ISNULL(AMOUNT,0))

  FROM ArrAccountsReceivableLog

  Where TransactionKey = @pTransactionKey

  And EntryType in( 1, 2, 4, 8, 11, 12, 14)

SELECT @lUpdatedOrderBalanceAmount = SUM(ISNULL(AMOUNT,0))

FROM ArrAccountsReceivableLog

Where TransactionKey = @pTransactionKey

And EntryType in( 5, 6, 7)

SELECT @lUpdatedLayawayBalanceAmount = SUM(ISNULL(AMOUNT,0))

FROM ArrAccountsReceivableLog

Where TransactionKey = @pTransactionKey

And EntryType in(9, 10)

declare @decimalPlace int

select @decimalPlace =  CurrencyDecimalDigits from CfgCultureInformation where Id=(select CultureInfo from CfgEnterprise)

Update ArrAccountsReceivable Set Balance = Round(ISNULL(Balance,0),@decimalPlace) + Round(ISNULL(@lUpdatedBalanceAmount, 0),@decimalPlace),

   OrderBalance = Round(ISNULL(OrderBalance,0),@decimalPlace) + Round(ISNULL(@lUpdatedOrderBalanceAmount, 0),@decimalPlace),

   LayawayBalance = Round(ISNULL(LayawayBalance,0),@decimalPlace) + Round(ISNULL(@lUpdatedLayawayBalanceAmount, 0),@decimalPlace)

Where AccountsReceivableKey = @lAccountsReceivableKey

SELECT @lUpdatedBalance = ROUND((CreditLimit - Balance),2)

FROM ArrAccountsReceivable

Where AccountsReceivableKey = @lAccountsReceivableKey

--GENERATE THE REPLICATION ENTRIES FOR THE ArrAccountsReceivableLog

INSERT INTO RepReplicationTransaction(ReplicationTransactionKey, SourceType,

       SourceKey, BatchKey,OperationType,Flag)

SELECT NEWID(), 319, AccountsReceivableLogKey,

  @pBatchKey, 0, 'FALSE'

FROM ArrAccountsReceivableLog

WHERE TransactionKey = @pTransactionKey

END

RETURN

ERRORHANDLER:

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

RETURN

END