iVend Database Database : Integration_TransactionLineAttribute Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@TransactionKey

In

transaction key for which the various transactions that needs to be pulled FROM iVend

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

CREATE PROCEDURE [dbo].[Integration_TransactionLineAttribute]

@TransactionKey NVARCHAR(50)

AS

BEGIN

SET NOCOUNT ON;

--Sale = 0,

--       Order = 1,

--       Layaway = 2,

--       Quotation = 3,

--       LostSales = 4,

--       Refund = 5,

--       Exchange = 6,

--       GiftCertificate = 7,

--       CouponIssue = 8,

--       AdvancePayment = 9

--       Delivery = 10

DECLARE @sql varchar(4000), @table varchar(50)

--Create Table #TransactionAttribute (TransactionKey NVARCHAR(50), SourceKey NVARCHAR(50), Source INT, AttributeValue NVARCHAR(255), AttributeKey NVARCHAR(50))

Create Table #TransactionAttributeValues (DOCDESC NVARCHAR(10), TransactionKey NVARCHAR(50), SourceKey NVARCHAR(50), AttributeValue NVARCHAR(255), IntegratedFieldName NVARCHAR(255))

---GET THE SALE ITEMS-----

INSERT INTO #TransactionAttributeValues

Select 'ARI', A.TransactionKey, A.SourceKey, A.AttributeValue, B.IntegratedFieldName

From TrxTransactionLineItemAttribute A, CfgLineAttribute B, TrxTransactionSaleItem C

Where A.ItemAttributeKey = B.LineAttributeKey

And B.IsIntegrated = 'TRUE'

And B.IsDeleted = 'FALSE'

And A.SourceKey = C.TransactionItemKey

And A.Source = 0

And C.Type = 0

And A.TransactionKey = @TransactionKey

---GET THE GIFT CERTIFICATE ITEMS-----

INSERT INTO #TransactionAttributeValues

Select 'ARI', A.TransactionKey, A.SourceKey, A.AttributeValue, B.IntegratedFieldName

From TrxTransactionLineItemAttribute A, CfgLineAttribute B, TrxTransactionGiftCertificate C

Where A.ItemAttributeKey = B.LineAttributeKey

And B.IsIntegrated = 'TRUE'

And B.IsDeleted = 'FALSE'

And A.SourceKey = C.TransactionGiftCertificateKey

And A.Source = 7

And C.Type = 0

And A.TransactionKey = @TransactionKey

--GET THE LAYAWAY LINE Attributes

INSERT INTO #TransactionAttributeValues

Select 'LRES', A.TransactionKey, A.SourceKey, A.AttributeValue, B.IntegratedFieldName

From TrxTransactionLineItemAttribute A, CfgLineAttribute B, TrxTransactionLayaway C

Where A.ItemAttributeKey = B.LineAttributeKey

And B.IsIntegrated = 'TRUE'

And B.IsDeleted = 'FALSE'

And A.SourceKey = C.TransactionLayawayKey

And A.Source = 2

And C.Type = 0

And A.TransactionKey = @TransactionKey

---GET THE REFUND ITEMS-----

INSERT INTO #TransactionAttributeValues

Select 'CRM', A.TransactionKey, A.SourceKey, A.AttributeValue, B.IntegratedFieldName

From TrxTransactionLineItemAttribute A, CfgLineAttribute B, TrxTransactionSaleItem C

Where A.ItemAttributeKey = B.LineAttributeKey

And B.IsIntegrated = 'TRUE'

And B.IsDeleted = 'FALSE'

And A.SourceKey = C.TransactionItemKey

And C.Type = 1

And A.Source = 5

And A.TransactionKey = @TransactionKey

---GET THE DELIVERY FOR SO-----

INSERT INTO #TransactionAttributeValues

Select 'ARISO', A.TransactionKey, A.SourceKey, A.AttributeValue, B.IntegratedFieldName

From TrxTransactionLineItemAttribute A, CfgLineAttribute B, TrxTransactionSaleItem C

Where A.ItemAttributeKey = B.LineAttributeKey

And B.IsIntegrated = 'TRUE'

And B.IsDeleted = 'FALSE'

And A.SourceKey = C.TransactionItemKey

And C.Type = 3

And A.Source = 10

And A.TransactionKey = @TransactionKey

---GET THE DELIVERY FOR Sales-----

INSERT INTO #TransactionAttributeValues

Select 'ARISO', A.TransactionKey, A.SourceKey, A.AttributeValue, B.IntegratedFieldName

From TrxTransactionLineItemAttribute A, CfgLineAttribute B, TrxTransactionSaleItem C

Where A.ItemAttributeKey = B.LineAttributeKey

And B.IsIntegrated = 'TRUE'

And B.IsDeleted = 'FALSE'

And A.SourceKey = C.TransactionItemKey

And C.Type = 4

And A.Source = 10

And A.TransactionKey = @TransactionKey

---GET THE DELIVERY FOR LAYAWAY-----

INSERT INTO #TransactionAttributeValues

Select 'DEL', A.TransactionKey, A.SourceKey, A.AttributeValue, B.IntegratedFieldName

From TrxTransactionLineItemAttribute A, CfgLineAttribute B, TrxTransactionSaleItem C

Where A.ItemAttributeKey = B.LineAttributeKey

And B.IsIntegrated = 'TRUE'

And B.IsDeleted = 'FALSE'

And A.SourceKey = C.TransactionItemKey

And C.Type = 5

And A.Source = 10

And A.TransactionKey = @TransactionKey

--GET THE ORDER ITEM

INSERT INTO #TransactionAttributeValues

Select 'ORD', A.TransactionKey, A.SourceKey, A.AttributeValue, B.IntegratedFieldName

From TrxTransactionLineItemAttribute A, CfgLineAttribute B, TrxTransactionOrder C

Where A.ItemAttributeKey = B.LineAttributeKey

And B.IsIntegrated = 'TRUE'

And B.IsDeleted = 'FALSE'

And A.SourceKey = C.TransactionOrderKey

And C.Type = 0

And A.Source = 1

And A.TransactionKey = @TransactionKey

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

select * from #TransactionAttributeValues

END

--EXEC [Integration_TransactionLineAttribute] 10000000000008