iVend Database Database : TrxInventoryUpdateQuotation 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

CfgEnterprise

Table

Stores the basic configuration entries for the system . These settings are mandatory for the system to work properly.

2

CfgSiteInformation

Table

Stores the site information. Enterprise always has the site id as 1. System Table Warning : Should not be modified directly

2

GetCompanyDateTime

User Defined Function

1

RepReplicationTransaction

Table

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

1

TrxTransaction

Table

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

1

TrxTransactionOrder

Table

Stores information about various order booked in the system.

1

TrxTransactionQuotation

Table

Stores information about various order booked in the system.

1

TrxTransactionSaleItem

Table

Stores sale/refund/delivery items attached to a transaction

1

TrxTransactionSurcharge

Table

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

1

CREATE PROCEDURE [dbo].[TrxInventoryUpdateQuotation]

(

@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,

  @Error INT

  Set @modified = dbo.GetCompanyDateTime()

  Select @modifiedBy = ModifiedBy, @modified = Modified

  FROM TrxTransaction Where TransactionKey = @pTransactionKey

      --------------THIS IS TO MARK THE QUOTATIONS AS CONVERTED IF THE SAME IS USED IN SALES OR ORDER--------

--this will update for sales

Update TrxTransactionQuotation

Set Status = 2 --INDICATES THAT THIS IS CONVERTED TO SALES

WHERE TransactionKey In

 (

  SELECT DISTINCT  A.TransactionKey

  FROM TrxTransactionQuotation A, TrxTransactionSaleItem B

  WHERE A.TransactionQuotationKey = B.TransactionQuotationKey

  AND B.TransactionKey = @pTransactionKey

 )

---for generating the replication entry for the surcharge of the original sales Quotation-------------

INSERT INTO RepReplicationTransaction(ReplicationTransactionKey, SourceType, SourceKey, BatchKey,OperationType,Flag)

SELECT NEWID(), 182, TransactionQuotationKey, @pBatchKey, 1 , 'FALSE'

FROM TrxTransactionQuotation

WHERE TransactionKey In

 (

  SELECT DISTINCT  A.TransactionKey

  FROM TrxTransactionQuotation A, TrxTransactionSaleItem B

  WHERE A.TransactionQuotationKey = B.TransactionQuotationKey

  AND B.TransactionKey = @pTransactionKey

 )

--this will update for order

Update TrxTransactionQuotation

Set Status = 2 --INDICATES THAT THIS IS CONVERTED TO SALES

WHERE TransactionKey In

 (

  SELECT DISTINCT  A.TransactionKey

  FROM TrxTransactionQuotation A, TrxTransactionOrder B

  WHERE A.TransactionQuotationKey = B.TransactionQuotationKey

  AND B.TransactionKey = @pTransactionKey

 )

---for generating the replication entry for the surcharge of the original sales Quotation-------------

INSERT INTO RepReplicationTransaction(ReplicationTransactionKey, SourceType, SourceKey, BatchKey,OperationType,Flag)

SELECT NEWID(), 182, TransactionQuotationKey, @pBatchKey, 1, 'FALSE'

FROM TrxTransactionQuotation

WHERE TransactionKey In

 (

  SELECT DISTINCT  A.TransactionKey

  FROM TrxTransactionQuotation A, TrxTransactionOrder B

  WHERE A.TransactionQuotationKey = B.TransactionQuotationKey

  AND B.TransactionKey = @pTransactionKey

 )

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

--THE FOLLOWING SECTION IS TO CAPTURE THE CANCELLATIONS

-------------------------UPDATING THE ITEM SURCHAGES FOR THE EARLY TRANSACTION------------------

If @pDebug = 'Y'

  Select 'Updating the item surchages for the early transaction'

Update su

Set  su.Status = 1   -- DENOTES THE SURCHARGE IS CANCELLED

FROM TrxTransaction h, TrxTransactionQuotation s, TrxTransactionQuotation original, TrxTransactionSurcharge su

WHERE h.TransactionKey = s.TransactionKey

AND  h.TransactionKey = @pTransactionKey

AND  s.OriginalDocumentKey = original.TransactionKey

AND  s.OriginalDetailKey = original.TransactionQuotationKey

AND  su.SourceType   = 8   -- DENOTES SURCHAGRES AGAINST QUOTATION

AND  su.SourceKey   = original.TransactionQuotationKey

---for generating the replication entry for the surcharge of the original sales Quotation-------------

INSERT INTO RepReplicationTransaction(ReplicationTransactionKey, SourceType, SourceKey, BatchKey,OperationType,Flag)

SELECT NEWID(), 155, TransactionSurchargeKey, @pBatchKey,1, 'FALSE'

FROM TrxTransactionQuotation A,  TrxTransactionSurcharge B

WHERE A.OriginalDocumentKey = B.TransactionKey

AND  A.OriginalDetailKey = B.SourceKey

AND  B.SourceType   = 8 -- DENOTES SURCHAGRES AGAINST QUOTATION

AND  A.OriginalDocumentKey <> '0' -- DENOTES THIS ENTRY IS FOR EDITING OF THE PREVIOUS ENTRY

AND  A.OriginalDetailKey <> '0' -- DENOTES THIS ENTRY IS FOR EDITING OF THE PREVIOUS ENTRY

AND  A.TransactionKey = @pTransactionKey

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

-------------------------UPDATING THE STATUS OF THE EARLIER Quotation ITEMS.------------------

If @pDebug = 'Y'

  Select 'Updating The status of the earlier Quotation items.'

Update original

Set  original.Status   = 1 -- DENOTES THE QUOTATION IS CANCELLED

FROM TrxTransaction h, TrxTransactionQuotation s, TrxTransactionQuotation original

WHERE h.TransactionKey = s.TransactionKey

AND  h.TransactionKey = @pTransactionKey

AND  s.OriginalDocumentKey = original.TransactionKey

AND  s.OriginalDetailKey = original.TransactionQuotationKey

---FOR GENERATING THE REPLICATION ENTRY FOR THE ORIGINAL SO-------------

INSERT INTO RepReplicationTransaction(ReplicationTransactionKey, SourceType, SourceKey, BatchKey,OperationType,Flag)

SELECT NEWID(), 182, B.TransactionQuotationKey, @pBatchKey,1, 'FALSE'

FROM TrxTransactionQuotation A,  TrxTransactionQuotation B

WHERE A.OriginalDocumentKey = B.TransactionKey

AND  A.OriginalDetailKey = B.TransactionQuotationKey

AND  A.OriginalDocumentKey <> '0' -- DENOTES THIS ENTRY IS FOR EDITING OF THE PREVIOUS ENTRY

AND  A.OriginalDetailKey <> '0' -- DENOTES THIS ENTRY IS FOR EDITING OF THE PREVIOUS ENTRY

AND  A.TransactionKey = @pTransactionKey

RETURN

ERRORHANDLER:

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

RETURN

END