iVend Database Database : TrxConsolidateTransactionForCreditCustomers Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

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

ConTrxTransactionMapping

Table

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

RepIntegrationLog

Table

Stores the Integration records for the SAP

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

TrxTransaction

Table

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

1

CREATE PROCEDURE [dbo].[TrxConsolidateTransactionForCreditCustomers]

AS

BEGIN

SET NOCOUNT ON

DECLARE @ErrorDesc       AS VARCHAR(255),

  @Error       AS INT,

  @lUseConsolidation     AS BIT,

  @lUseCashCustomerForConsolidation AS BIT,

  @NextTransactionNumber   AS nvarchar(50),

  @lCurrentTransactionKey   AS nvarchar(50),

  @SiteId       AS INT,

  @lRollupOnlyCashCustomerInvoices AS BIT

SELECT @SiteId = SiteId From CfgSiteInformation

IF ISNULL(@SiteId, 0) != 1

BEGIN

RETURN 0

END

SELECT @lUseConsolidation = UseTransactionConsolidation,

  @lUseCashCustomerForConsolidation = UseCashCustomerForConsolidation,

  @lRollupOnlyCashCustomerInvoices = RollupOnlyCashCustomerInvoices

FROM CFGEnterprise

---IF THE SYSTEM IS NOT CONSOLIDATING THEN RETURN

If @lUseConsolidation = 'FALSE' OR @lRollupOnlyCashCustomerInvoices = 'FALSE'

BEGIN

RETURN 0

END

BEGIN TRY

BEGIN TRANSACTION

SELECT A.* INTO #TrxTransaction

FROM TrxTransaction A, RtlStore B, InvWarehouse C

Where A.CustomerKey != B.CashCustomerKey

And A.StoreKey = b.StoreKey

AND A.TransactionKey NOT IN

(

SELECT TransactionKey From ConTrxTransactionMapping

)

And IsSuspended = 'FALSE'

AND B.WarehouseKey = C.WarehouseKey

AND C.OwnerShipType != 2

AND A.TransactionKey IN (SELECT SourceKey FROM RepIntegrationLog WHERE SourceType = 143 And IsNull(ApplicationKey, '1') = '1')

DECLARE TransactionNotification CURSOR FOR

SELECT TransactionKey FROM #TrxTransaction

--WHERE TransactionKey NOT IN

--(

-- SELECT TransactionKey FROM #TransactionStep1

--)

ORDER BY 1

OPEN TransactionNotification

FETCH NEXT FROM  TransactionNotification INTO @lCurrentTransactionKey

WHILE @@FETCH_STATUS = 0

BEGIN

--GET THE NEXT CONSOLIDATED TRANSACTION NUMBER

SELECT @NextTransactionNumber = NEWID()

--INSERTING THE ENTRY IN MAPPING TABLE WITH THE NEW TRANSACTIONKEY

INSERT INTO ConTrxTransactionMapping(TransactionKey, ConsolidatedTransactionKey, IsConsolidated)

Values(@lCurrentTransactionKey, @NextTransactionNumber, 'FALSE')

--GENERATE THE ENTRY FOR THE UNCONSOLIDATED TRANSACTION

EXEC DataNotification @pSourceType= 200, @pSourceKey = @NextTransactionNumber, @pBatchKey =0, @pOperationType =0, @pUserKey = 0, @pDebug = 'FALSE'

FETCH NEXT FROM TransactionNotification INTO @lCurrentTransactionKey

END

CLOSE TransactionNotification

DEALLOCATE TransactionNotification

COMMIT

RETURN (0)

END TRY

BEGIN CATCH

SELECT

           ERROR_NUMBER() AS ErrorNumber,

           ERROR_SEVERITY() AS ErrorSeverity,

           ERROR_STATE() AS ErrorState,

           ERROR_PROCEDURE() AS ErrorProcedure,

           ERROR_LINE() AS ErrorLine,

           ERROR_MESSAGE() AS ErrorMessage;

IF CURSOR_STATUS('global','TransactionNotification') > 0

BEGIN

  CLOSE TransactionNotification

  DEALLOCATE TransactionNotification

END

--IF CURSOR_STATUS('global','TransactionCursor') > 0

--BEGIN

-- CLOSE TransactionCursor

-- DEALLOCATE TransactionCursor

--END

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

RETURN (1)

END CATCH

END