iVend Database Database : RepUpdateCustomerBalance Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pAccountsReceivableLogKey

In

Reference key of the AccountsReceivable Log Table.

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

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

CREATE PROCEDURE [dbo].[RepUpdateCustomerBalance]

(

@pAccountsReceivableLogKey nvarchar(50)

)

AS

BEGIN

SET NOCOUNT ON

Declare @ErrorDesc varchar(255)

  ---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 --AMOUNT RETURNED FOR LAYAWAY CANCELLATION

  ---13   -- Manual Adjustment

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

DECLARE @lUpdatedBalanceAmount DECIMAL(20, 5),

  @lUpdatedOrderBalanceAmount DECIMAL(20, 5),

  @lUpdatedLayawayBalanceAmount DECIMAL(20, 5)

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

  FROM ArrAccountsReceivableLog

  Where AccountsReceivableLogKey = @pAccountsReceivableLogKey

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

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

  FROM ArrAccountsReceivableLog

  Where AccountsReceivableLogKey = @pAccountsReceivableLogKey

  And EntryType in( 5, 6, 7)

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

  FROM ArrAccountsReceivableLog

  Where AccountsReceivableLogKey = @pAccountsReceivableLogKey

  And EntryType in( 9, 10)

  Update A

  Set Balance = ISNULL(Balance,0) + ISNULL(@lUpdatedBalanceAmount, 0),

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

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

  From ArrAccountsReceivable A, ArrAccountsReceivableLog B

  Where A.AccountsReceivableKey =  B.AccountsReceivableKey

  And B.AccountsReceivableLogKey = @pAccountsReceivableLogKey

  IF(@@ERROR <>0)

  BEGIN

  SET @ErrorDesc = 'Error while updating account balances'

  GOTO ERRORHANDLER

  END

RETURN

ERRORHANDLER:

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

RETURN

END