iVend Database Database : GetSpecialOrderHistory Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pTransactionKey

In

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

TrxTransactionOrder

Table

Stores information about various order booked in the system.

1

-- =============================================

-- Author:  Abinesh

-- Create date: 10/11/2008

-- Description:

-- =============================================

CREATE PROCEDURE [dbo].[GetSpecialOrderHistory]

@pTransactionKey nvarchar(50) = '0'

AS

BEGIN

SET NOCOUNT ON;

  With LastVersion(OriginalDetailKey, OriginalDocumentKey, TransactionOrderKey, TransactionKey, Version) As

(

Select OriginalDetailKey, OriginalDocumentKey, TransactionOrderKey, TransactionKey, 0 as Version

From TrxTransactionOrder

Where TransactionKey = @pTransactionKey

UNION ALL

Select t.OriginalDetailKey, t.OriginalDocumentKey, t.TransactionOrderKey, t.TransactionKey, Version + 1

From TrxTransactionOrder t

  INNER JOIN LastVersion v

  ON v.OriginalDetailKey = t.TransactionOrderKey

  And v.OriginalDocumentKey = t.TransactionKey

)

Select OriginalDetailKey, OriginalDocumentKey, TransactionOrderKey, TransactionKey

From LastVersion

Order By TransactionKey asc

END