iVend Database Database : EnableRollup Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pEnableRollup

In

Boolean

1

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

ConTrxTransactionMapping

Table

1

RepIntegrationLog

Table

Stores the Integration records for the SAP

1

TrxTransaction

Table

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

1

CREATE PROCEDURE [dbo].[EnableRollup]

@pEnableRollup Bit

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

Declare @ErrorDesc VARCHAR(255),

  @Error INT

  --If @RecordsFound = 0

BEGIN

IF @pEnableRollup = 'TRUE'

  BEGIN

  --CHECK IF THE ROLLUP CAN BE ENABLED OR NOT

  --1. THERE SHOULD NOT BE ANY PENDING RECORD FOR THE SOURCE TYPE 143 FOR INTEGRATION WITH ERP

  IF EXISTS(Select 1 FROM RepIntegrationLog WHERE Flag = 0 And SourceType = 143)

  BEGIN

  SET @ErrorDesc = 'There are records still pending for integration.'

  GOTO ERRORHANDLER

  END

  --THIS WILL INSERT THE RECORDS IN THE TRXTRANSACTIONKEY

  INSERT INTO ConTrxTransactionMapping

  (TransactionKey, ConsolidatedTransactionKey, IsConsolidated)

  SELECT  TrxTransaction.TransactionKey, TrxTransaction.TransactionKey, 'FALSE'

  FROM TrxTransaction

  Where TransactionKey Not In

  (

  Select TransactionKey From ConTrxTransactionMapping

  )

  END

IF @pEnableRollup = 'FALSE'

  BEGIN

  --THERE SHOULD NOT BE ANY PENDING RECORD FOR THE SOURCE TYPE 200 FOR INTEGRATION WITH ERP

  IF EXISTS(Select 1 FROM RepIntegrationLog WHERE Flag = 0 And SourceType = 200)

  BEGIN

  SET @ErrorDesc = 'There are records still pending for integration.'

  GOTO ERRORHANDLER

  END

  END

RETURN

ERRORHANDLER:

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

RETURN

END

END