iVend Database Database : TrxConsolidateTransaction Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@debug

In

VarWChar

1

@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

ConTrxTransactionSaleItem

Table

Stores the consolidated values for the Transaction Sale Item

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].[TrxConsolidateTransaction]

@debug as NVARCHAR(1) = 'N'

AS

BEGIN

SET NOCOUNT ON

DECLARE @ErrorDesc       AS VARCHAR(255),

  @Error       AS INT,

  @lUseConsolidation     AS BIT,

  @lUseCashCustomerForConsolidation AS BIT,

  @NextTransactionNumber   AS NVARCHAR(50),

  @lCurrentBusinessDate   AS DATETIME,

  @lCurrentStoreKey     AS NVARCHAR(50),

  @lCurrentCustomerKey   AS NVARCHAR(50),

  @ARTransactionItemKey   AS NVARCHAR(50),

  @RefundTransactionItemKey   AS NVARCHAR(50),

  @lCurrentTransactionKey   AS NVARCHAR(50),

  @SSql       AS NVARCHAR(2000),

  @NumberOfRecords     AS INT,

  @RecordCount     AS INT,

  @DetailRecords     AS INT,

  @MaxLinesInTransaction   AS INT,

  @MinTransactionsInRollup   AS INT,

  @StepSize       AS INT,

  @SiteId       AS INT,

  @lRollupOnlyCashCustomerInvoices AS BIT

BEGIN TRY

BEGIN TRANSACTION

SELECT @SiteId = SiteId From CfgSiteInformation

IF ISNULL(@SiteId, 0) != 1

BEGIN

ROLLBACK

RETURN 0

END

SELECT @lUseConsolidation = UseTransactionConsolidation,

  @lUseCashCustomerForConsolidation = UseCashCustomerForConsolidation,

  @lRollupOnlyCashCustomerInvoices = RollupOnlyCashCustomerInvoices

FROM CFGEnterprise

---IF THE SYSTEM IS NOT CONSOLIDATING THEN RETURN

If @lUseConsolidation = 0

BEGIN

RETURN 0

END

SET @MaxLinesInTransaction = 1000

SET @MinTransactionsInRollup = 200

SET @StepSize = 100

SELECT A.* INTO #TrxTransaction

FROM TrxTransaction A INNER JOIN RtlStore B ON A.StoreKey = B.StoreKey

INNER JOIN InvWarehouse C ON B.WarehouseKey = C.WarehouseKey

LEFT OUTER JOIN ConTrxTransactionMapping D On A.TransactionKey = D.TransactionKey

WHERE D.TransactionKey IS NULL

And A.IsSuspended = 0

And A.IsVoided = 0

And ISNULL(A.IsDeleted, 0) = 0

AND C.OwnerShipType != 2

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

--THIS IS DONE SO THAT A TEMP TABLE IS CREATED

SELECT TransactionKey INTO #TempCheckTransactionNumberTable

FROM #TrxTransaction

TRUNCATE TABLE #TempCheckTransactionNumberTable

--

IF @lUseCashCustomerForConsolidation = 1

BEGIN

  UPDATE A

  SET A.CustomerKey = B.CashCustomerKey

  FROM #TrxTransaction A, RtlStore B

  WHERE A.StoreKey = B.StoreKey

  AND B.CashCustomerKey != 0

END

--This will insert the data into the temp tables......

----------------------------------------------------------------------------------------

SELECT A.* INTO #TrxTransactionGiftCertificate

FROM TrxTransactionGiftCertificate A, #TrxTransaction B

WHERE A.TransactionKey = B.TransactionKey

----------------------------------------------------------------------------------------

SELECT A.* INTO #TrxTransactionSaleItem

FROM TrxTransactionSaleItem A, #TrxTransaction B

WHERE A.TransactionKey = B.TransactionKey

----------------------------------------------------------------------------------------

SELECT A.* INTO #TrxTransactionSurcharge

FROM TrxTransactionSurcharge A, #TrxTransaction B

WHERE A.TransactionKey = B.TransactionKey

----------------------------------------------------------------------------------------

SELECT A.* INTO #TrxTransactionPayment

FROM TrxTransactionPayment A, #TrxTransaction B

WHERE A.TransactionKey = B.TransactionKey

----------------------------------------------------------------------------------------

SELECT A.* INTO #TrxTransactionSalesPerson

FROM TrxTransactionSalesPerson A, #TrxTransaction B

WHERE A.TransactionKey = B.TransactionKey

AND  A.Type = 1 --this is for getting the sales person on the sales line

AND  A.TransactionSalesPersonKey In

(

Select MIN(TransactionSalesPersonKey)

From TrxTransactionSalesPerson A1,  #TrxTransaction B1

Where A1.TransactionKey = B1.TransactionKey

Group By A1.TransactionKey, SourceKey, Type

)

----------------------------------------------------------------------------------------

CREATE TABLE #TransactionStep1(TransactionKey NVARCHAR(50))

--This will get the list of transaction which can be consolidated out of the list of all unrolled transactions

INSERT INTO #TransactionStep1

SELECT TransactionKey

FROM #TrxTransaction A

WHERE

(HasSales = 1 OR HasSaleExchanges = 1 OR HasRefunds = 1 OR HasGiftCertificates = 1)

AND HasARPayments   = 0

AND HasLayaways   = 0

AND HasLayawaysCancellations = 0

AND HasOrders   = 0

AND HasFulFillments   = 0

AND HasDeliveries   = 0

AND HasQuotations   = 0

AND IsSuspended   = 0

----delete the transactions which has the dynamic assemlies in it

Delete A

FROM #TransactionStep1 A, TrxTransactionSaleItem B with (nolock), InvProduct C with (nolock)

Where A.TransactionKey = B.TransactionKey

And B.ProductKey = C.ProductKey

And C.IsDynamicAssembly = 1

--INSERT INTO #TransactionStep1

Delete A

FROM #TransactionStep1 A

, TrxTransactionSaleItem B with (nolock)

, TrxTransactionSaleItem C with (nolock)

, TrxTransactionFulfillmentDetail D with (nolock)

, TrxTransactionFulfillment E with (nolock)

Where A.TransactionKey = B.TransactionKey

And B.Type = 1 --refund

And B.OriginalDocumentKey != 0

And B.OriginalDocumentKey = C.TransactionKey

And B.OriginalDetailKey = C.TransactionItemKey

And C.HasFulfillment = 1

And D.TransactionKey = C.TransactionKey

And D.SourceDetailKey = C.TransactionItemKey

And D.FulfillmentKey = E.FulfillmentKey

And E.InventoryAllocationMethod = 1   --- only delivery

And d.SourceType = 0 --indicates that the fulfillment is for the sale item

---THIS WILL GET RID OF THE RECORDS WHICH DOES NOT USE THE CASH CUSTOMER

---I.E ALL THE RECORDS WITH NO CASH CUSTOMER WILL NOT BE ROLLED UP

IF @lRollupOnlyCashCustomerInvoices = 'TRUE'

BEGIN

  DELETE FROM #TransactionStep1

  WHERE TransactionKey IN

  (

  SELECT A.TransactionKey

  FROM #TrxTransaction A, RtlStore B

  Where A.CustomerKey != B.CashCustomerKey

  And A.StoreKey = b.StoreKey

  )

END

--THIS WILL GIVE ME THE LIST OF TRANSACTION WHICH DO NOT HAVE THE SAME SERIAL KEY USED TWICE

DELETE FROM #TransactionStep1

WHERE TransactionKey in

(

SELECT A1.TransactionKey

FROM #TransactionStep1 A1, TrxTransactionSaleItem B1

Where A1.TransactionKey = B1.TransactionKey

AND  B1.HasSerialNumber = 1

AND  B1.ProductDetailKey != 0

AND  B1.ProductDetailKey IN

 (

  SELECT ProductDetailKey

  FROM #TransactionStep1 A, TrxTransactionSaleItem B

  WHERE A.TransactionKey = B.TransactionKey

  AND  HasSerialNumber = 1

  AND  ProductDetailKey != 0

  GROUP BY ProductDetailKey

  HAVING COUNT(ProductDetailKey) > 1

 )

)

--THIS WILL REMOVE THE TRANSACTION FOR WHICH THE On Account Tender is used at POS

DELETE FROM #TransactionStep1 WHERE TransactionKey IN

 (

  SELECT A1.TransactionKey

  FROM #TransactionStep1 A1 INNER JOIN TrxTransactionPayment B1 ON A1.TransactionKey = B1.TransactionKey

  INNER JOIN PmtPaymentType C1 ON B1.PaymentTypeKey = C1.PaymentTypeKey

  WHERE C1.Type = 4 OR C1.TreatAsOnAccount = 1

 )

IF @debug = 'Y'

BEGIN

  PRINT 'Generating the intergation enteries for unconslidated data.'

END

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

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

CREATE TABLE #TempTransactionNumberTable (TransactionKey nvarchar(50))

DECLARE TransactionCursor CURSOR FOR

SELECT DISTINCT BusinessDate, StoreKey, CustomerKey

FROM #TrxTransaction

WHERE TransactionKey In(SELECT TransactionKey FROM #TransactionStep1)

order by 1 Asc

OPEN TransactionCursor

FETCH NEXT FROM  TransactionCursor INTO @lCurrentBusinessDate, @lCurrentStoreKey, @lCurrentCustomerKey

WHILE @@FETCH_STATUS=0

BEGIN

DELETE FROM #TempTransactionNumberTable

INSERT INTO #TempTransactionNumberTable(TransactionKey)

SELECT A.TransactionKey

FROM #TransactionStep1 A, #TrxTransaction B

WHERE A.TransactionKey = B.TransactionKey

AND BusinessDate = @lCurrentBusinessDate

AND StoreKey   = @lCurrentStoreKey

AND CustomerKey   = @lCurrentCustomerKey

WHILE (Select Count(1) From #TempTransactionNumberTable) > 0

BEGIN

  SET @RecordCount = 0

  TRUNCATE TABLE #TempCheckTransactionNumberTable

  INSERT INTO #TempCheckTransactionNumberTable

  SELECT * FROM #TempTransactionNumberTable

  Select @DetailRecords = Count(Records)

  FROM

   (

    SELECT Count(InventoryItemKey) Records

    FROM #TrxTransactionSaleItem A INNER JOIN #TempCheckTransactionNumberTable B

    ON A.TransactionKey = B.TransactionKey

    LEFT OUTER JOIN #TrxTransactionSalesPerson C

    ON A.TransactionItemKey = C.SourceKey

    AND C.Type = 1

    WHERE A.Type = 0

    GROUP BY A.Type, WarehouseKey, ProductKey,

    InventoryItemKey, Description, HasSerialNumber,

    HasBatchNumber, HasFulfillment, TaxCodeKey, TaxRate,

    BasePrice, ProductDetailKey, C.UserKey, A.UOMGroupDetailKey

   ) S

  IF( @DetailRecords > @MaxLinesInTransaction)

  BEGIN

  --THIS WILL FIND OUT HOW MANY RECORDS CAN COME FIT IN THE CURRENT TRANSACTION

  Set @NumberOfRecords = @MinTransactionsInRollup

  WHILE (ISNULL(@RecordCount, 0)) < @MaxLinesInTransaction

  BEGIN

    SET @NumberOfRecords = @NumberOfRecords + @StepSize

    TRUNCATE TABLE  #TempCheckTransactionNumberTable

    SET @SSql = 'INSERT INTO #TempCheckTransactionNumberTable SELECT TOP '+ convert(nvarchar, @NumberOfRecords) + ' * FROM #TempTransactionNumberTable'

    EXECUTE sp_executesql @SSql

    --THIS IS FOR NOT LETTING THE TRANSACTION MAKE MORE THAN 1000 LINE ITEMS

    Select @RecordCount = Count(Records)

    FROM

    (

    SELECT Count(InventoryItemKey) Records

    FROM #TrxTransactionSaleItem A INNER JOIN #TempCheckTransactionNumberTable B

    ON A.TransactionKey = B.TransactionKey

    LEFT OUTER JOIN #TrxTransactionSalesPerson C

    ON A.TransactionItemKey = C.SourceKey

    AND C.Type = 1

    WHERE A.Type = 0

    GROUP BY A.Type, WarehouseKey, ProductKey,

      InventoryItemKey, Description, HasSerialNumber,

      HasBatchNumber, HasFulfillment, TaxCodeKey, TaxRate,

      BasePrice, ProductDetailKey, C.UserKey, A.BookingWarehouseKey, A.DeliveryWarehouseKey, A.UOMGroupDetailKey

    ) S

    --SELECT 'record count', @RecordCount, @NumberOfRecords

  END

  END --THIS IS FOR IF THE TRANSACTION HAS MORE THAN 100 LINES FOR CONSOLIDATION

  SET @NextTransactionNumber = NEWID()

  SET @ARTransactionItemKey = 0

  SET @RefundTransactionItemKey = 0

  SELECT * FROM #TempCheckTransactionNumberTable

  --THIS QUERY IS FOR CONSLIDATING THE HEADER

  INSERT INTO ConTrxTransaction(

    TransactionKey, StoreKey, BusinessDate,

    ActualDiscountPercent, TransactionId, CustomerKey,

    SubTotal, Total, SurchargesTotal,

    PayableAmount, IsSuspended)

  SELECT @NextTransactionNumber, StoreKey, BusinessDate,

    0, '' TransactionId, CustomerKey,

    SUM(SubTotal) As SubTotal, SUM(Total) As Total, SUM(SurchargesTotal)As SurchargesTotal,

    SUM(PayableAmount) As PayableAmount, 0 AS IsSuspended

  FROM #TrxTransaction A, #TempCheckTransactionNumberTable B

  WHERE A.TransactionKey = B.TransactionKey

  GROUP BY StoreKey, BusinessDate, CustomerKey

  --CONSOLIDATING THE LINE ITEMS.

  INSERT INTO ConTrxTransactionSaleItem

  (

   TransactionItemKey,  TransactionKey, [Type],

   WarehouseKey, ProductKey, InventoryItemKey,

  [Description], HasSerialNumber, HasBatchNumber,

   HasFulfillment, HasSurcharges, HasDiscounts,

   TaxCodeKey, TaxRate, HasTaxCodeOverride,

   OriginalPrice, BasePrice, Price,

   PriceSource, Quantity, OpenQuantity,

   FullfilledQuantity, RefundedQuantity, SubTotal, Tax,

   SurchargeTotal, Total, TotalBeforeSaleDiscount, TotalPostSaleDiscount,

   ProductDetailKey, PostTaxDiscount, DiscountAmount,

   DiscountPercent, DiscountType, SaleDiscountAmount,

   SystemDiscount, ExclusiveDiscountPercent, CommentKey,

   OriginalDetailKey, OriginalDocumentKey, UserKey, DeliveryWarehouseKey,

   BookingWarehouseKey, UOMQuantity, UOMGroupDetailKey

  )

  SELECT NEWID(), @NextTransactionNumber AS TransactionKey, A.Type,

    WarehouseKey, ProductKey, InventoryItemKey,

    Description, HasSerialNumber, HasBatchNumber,

    HasFulfillment,0 AS HasSurcharges,0 AS HasDiscounts,

    TaxCodeKey, TaxRate, 0 AS HasTaxCodeOverride,

    BasePrice AS OriginalPrice, BasePrice, BasePrice,

    0 AS PriceSource, SUM(Quantity), SUM(OpenQuantity),

    SUM(FullfilledQuantity), SUM(RefundedQuantity), SUM(SubTotal), SUM(Tax),

    SUM(SurchargeTotal) , SUM(Total), SUM(TotalPostSaleDiscount), SUM(TotalPostSaleDiscount),

    ProductDetailKey, 0 As PostTaxDiscount, 0 As DiscountAmount,

    0 As DiscountPercent, 0 As DicountType, 0 As SaleDiscountAmount ,

    0 As SystemDiscount, 0 As ExclusiveDiscountPercent, '0' As CommentKey,

    0 AS OriginalDetailKey, 0 As  OriginalDocumentKey, ISNULL(C.UserKey, 0) As UserKey,

    A.DeliveryWarehouseKey, A.BookingWarehouseKey,

    SUM(A.UOMQuantity), A.UOMGroupDetailKey

  FROM #TrxTransactionSaleItem A INNER JOIN #TempCheckTransactionNumberTable B

  ON A.TransactionKey = B.TransactionKey

  LEFT OUTER JOIN #TrxTransactionSalesPerson C

  ON A.TransactionItemKey = C.SourceKey

  AND C.Type = 1

  GROUP BY A.Type, WarehouseKey, ProductKey,

   InventoryItemKey, Description, HasSerialNumber,

   HasBatchNumber, HasFulfillment, TaxCodeKey, TaxRate,

   BasePrice, ProductDetailKey, C.UserKey, DeliveryWarehouseKey, BookingWarehouseKey, A.UOMGroupDetailKey

  --LOGIC FOR PAYMENT CONSOLIDATION.

  INSERT INTO ConTrxTransactionPayment

  (

   TransactionPaymentKey, TransactionKey, PaymentTypeKey,

   TenderType, Amount, ChangeAmount,

   CashBackAmount, ExpirationDate,

   ZipCode, AccountType,

   IsVoided, PaymentReferenceNumber, RoundingAmount,

   DetailKey, CurrencyKey, ForeignCurrencyAmount,

   DiscountAmount, NetPayableAmount, CheckDueDate

  )

  SELECT NEWID(), @NextTransactionNumber, PaymentTypeKey,

    TenderType, SUM(Amount - ChangeAmount - CashBackAmount), 0,0,

    ExpirationDate,

    ZipCode,AccountType,

    IsVoided, PaymentReferenceNumber, SUM(RoundingAmount), DetailKey,

    CurrencyKey , SUM(ForeignCurrencyAmount), SUm(DiscountAmount), SUM(NetPayableAmount), CheckDueDate

  FROM #TrxTransactionPayment A, #TempCheckTransactionNumberTable B

  WHERE A.TransactionKey = B.TransactionKey

  AND  TenderType IN(0, 1, 2, 14) --THIS IS FOR CASH, CREDIT, DEBIT

  GROUP BY PaymentTypeKey, TenderType,

     ExpirationDate, ZipCode, IsVoided,

     PaymentReferenceNumber, DetailKey, AccountType, CurrencyKey, CheckDueDate

  INSERT INTO ConTrxTransactionPayment

  (

   TransactionPaymentKey, TransactionKey, PaymentTypeKey,

   TenderType, Amount, ChangeAmount,

   CashBackAmount, ExpirationDate,

   ZipCode, AccountType,

   IsVoided, PaymentReferenceNumber, RoundingAmount,

   DetailKey, CurrencyKey, ForeignCurrencyAmount,

   DiscountAmount, NetPayableAmount , CheckDueDate

  )

  SELECT NEWID(),

  @NextTransactionNumber, PaymentTypeKey,

   TenderType, Amount-ChangeAmount-CashBackAmount, 0, 0,

   ExpirationDate,

   ZipCode,AccountType,

   IsVoided, PaymentReferenceNumber, RoundingAmount, DetailKey,

   CurrencyKey, ForeignCurrencyAmount,

  ISNULL(DiscountAmount,0), ISNULL(NetPayableAmount,0), CheckDueDate

  --SUM(ISNULL(DiscountAmount,0)), SUM(ISNULL(NetPayableAmount,0))

  FROM #TrxTransactionPayment A, #TempCheckTransactionNumberTable B

  WHERE A.TransactionKey = B.TransactionKey

  AND  TenderType NOT IN(0, 1, 2, 14) --THIS IS FOR ALL EXCEPT FOR CASH, CREDIT, DEBIT

  --UPDATE THE TRANSACTION LEVEL SURCHARGE

  INSERT INTO ConTrxTransactionSurcharge

   (

    TransactionSurchargeKey, TransactionKey, SurchargeKey,

    SourceType,SourceKey, Amount,

    DiscountAmount, Total, IsRefund,Status,

    CommentKey, TaxCodeKey, TaxRate,

    AmountType, AmountPercent, DiscountType,

    DiscountPercent, PayInAdvance, IsNewSurcharge, Tax, IsPaymentDiscountReversalSurcharge

   )

  SELECT NEWID(),

    @NextTransactionNumber, SurchargeKey,

    SourceType, @NextTransactionNumber AS SourceKey, SUM(Amount) AS Amount,

    SUM(DiscountAmount) AS DiscountAmount, SUM(TOTAL) As Total, IsRefund, A.Status,

    '0' As CommentKey, TaxCodeKey As TaxCodeKey, A.TaxRate As TaxRate,

    0 As AmountType, 0 As AmountPercent, 0 As DiscountType,

    0 As DiscountPercent, 0 As PayInAdvance, 0 As IsNewSurcharge, SUM(Tax) As Tax,

    A.IsPaymentDiscountReversalSurcharge

  FROM TrxTransactionSurcharge A, #TempCheckTransactionNumberTable B

  WHERE A.TransactionKey = B.TransactionKey

  AND A.SourceType IN (0, 6) --[AGAINST TRANSACTION]

  GROUP BY SurchargeKey, SourceType, IsRefund, A.Status, A.TaxCodeKey , A.TaxRate, A.IsPaymentDiscountReversalSurcharge

  --UPDATE THE SURCHARGE FOR THE AR INVOICE

  SELECT TOP 1 @ARTransactionItemKey = TransactionItemKey

  FROM ConTrxTransactionSaleItem

  WHERE TransactionKey = @NextTransactionNumber

  AND TYPE = 0

  IF ISNULL(@ARTransactionItemKey,'0') != '0'

  BEGIN

    INSERT INTO ConTrxTransactionSurcharge

     (

      TransactionSurchargeKey, TransactionKey, SurchargeKey,

      SourceType,SourceKey, Amount,

      DiscountAmount, Total, IsRefund, Status,

      CommentKey, TaxCodeKey, TaxRate,

      AmountType, AmountPercent, DiscountType,

      DiscountPercent, PayInAdvance, IsNewSurcharge, Tax, IsPaymentDiscountReversalSurcharge

     )

    SELECT NEWID(), @NextTransactionNumber, A.SurchargeKey,

      A.SourceType, @ARTransactionItemKey AS SourceKey, SUM(A.Amount) AS Amount,

      SUM(A.DiscountAmount) AS DiscountAmount, SUM(A.TOTAL) As Total, A.IsRefund, A.Status,

      '0' As CommentKey, A.TaxCodeKey As TaxCodeKey, A.TaxRate As TaxRate,

      0 As AmountType, 0 As AmountPercent, 0 As DiscountType,

      0 As DiscountPercent, 0 As PayInAdvance, 0 As IsNewSurcharge, SUM(A.Tax)As Tax,

      IsPaymentDiscountReversalSurcharge

    FROM TrxTransactionSurcharge A, #TempCheckTransactionNumberTable B, TrxTransactionSaleItem C

    WHERE A.TransactionKey = B.TransactionKey

    AND A.TransactionKey = C.TransactionKey

    AND A.SourceKey = C.TransactionItemKey

    AND A.SourceType = 4 -- AGAINST SALE ITEM

    AND C.Type = 0   -- SHOWS IT IS A SALE ITEM

    AND A.Status = 0       -- SHOWS THAT THE SURCHARGE IS NOT DELETED

    GROUP BY A.SurchargeKey, A.SourceType, A.IsRefund, A.Status, A.TaxCodeKey, A.TaxRate, A.IsPaymentDiscountReversalSurcharge

  END

  --UPDATE THE SURCHARGE FOR THE CREDIT MEMO

  SELECT TOP 1 @RefundTransactionItemKey = TransactionItemKey

  FROM ConTrxTransactionSaleItem

  WHERE TransactionKey = @NextTransactionNumber

  AND TYPE = 1

  IF ISNULL(@RefundTransactionItemKey , '0') != '0'

  BEGIN

  INSERT INTO ConTrxTransactionSurcharge

    (

     TransactionSurchargeKey, TransactionKey, SurchargeKey,

     SourceType,SourceKey, Amount,

     DiscountAmount, Total, IsRefund,Status,

     CommentKey, TaxCodeKey, TaxRate,

     AmountType, AmountPercent, DiscountType,

     DiscountPercent, PayInAdvance, IsNewSurcharge, Tax,IsPaymentDiscountReversalSurcharge

    )

  SELECT NEWID(), @NextTransactionNumber, A.SurchargeKey,

     A.SourceType, @RefundTransactionItemKey AS SourceKey, SUM(A.Amount) AS Amount,

    SUM(A.DiscountAmount) AS DiscountAmount, SUM(A.TOTAL) As Total, A.IsRefund, A.Status,

    '0' As CommentKey, a.TaxCodeKey As TaxCodeKey, A.TaxRate As TaxRate,

    0 As AmountType, 0 As AmountPercent, 0 As DiscountType,

    0 As DiscountPercent, 0 As PayInAdvance, 0 As IsNewSurcharge, SUM(A.Tax) As Tax,

     A.IsPaymentDiscountReversalSurcharge

  FROM TrxTransactionSurcharge A, #TempCheckTransactionNumberTable B, TrxTransactionSaleItem C

  WHERE A.TransactionKey = B.TransactionKey

  AND A.TransactionKey = C.TransactionKey

  AND A.SourceKey   = C.TransactionItemKey

  AND A.SourceType = 4     -- AGAINST SALE ITEM

  AND C.Type   = 1     -- SHOWS IT IS A REFUND ITEM

  AND A.Status   = 0     -- SHOWS THAT THE SURCHARGE IS NOT DELETED

  GROUP BY A.SurchargeKey, A.SourceType, A.IsRefund, A.Status,A.TaxCodeKey, A.TaxRate, A.IsPaymentDiscountReversalSurcharge

  END

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

  INSERT INTO ConTrxTransactionMapping(TransactionKey, ConsolidatedTransactionKey, IsConsolidated)

  SELECT TransactionKey, @NextTransactionNumber , 'TRUE'

  FROM #TempCheckTransactionNumberTable

  INSERT INTO ConTrxTransactionGiftCertificate(TransactionKey, WarehouseKey, ProductKey,

     InventoryItemKey, Description, Type, Price , Quantity)

  Select @NextTransactionNumber, WarehouseKey, ProductKey,

     InventoryItemKey, Description, Type, SUM(Total), SUM(A.Quantity) --updated for issue related to GC #125142

  From #TrxTransactionGiftCertificate A, #TempCheckTransactionNumberTable B

  Where A.TransactionKey = B.TransactionKey

  Group By A.WarehouseKey, A.ProductKey, A.InventoryItemKey, A.Description, A.Type

  --GENERATING THE ENTRY OF INTEGRATION

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

  Delete From #TempTransactionNumberTable

  Where TransactionKey In

  (

  Select TransactionKey From #TempCheckTransactionNumberTable

  )

END --THIS IS FOR THE END OF THE LOGIC OR THE 1000 RECORDS IN A CONSOLIDATED TRANSACTION

FETCH NEXT FROM TransactionCursor INTO @lCurrentBusinessDate, @lCurrentStoreKey, @lCurrentCustomerKey

END

CLOSE TransactionCursor

DEALLOCATE TransactionCursor

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