iVend Database Database : Integration_funcTransaction User Defined Function

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

BigInt

8

@TABLE_RETURN_VALUE

Return Value

Result table returned by table valued function

Empty

0

Database Object

Object Type

Description

Dep Level

InvProduct

Table

Stores the product related details.

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

TrxTransaction

Table

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

1

TrxTransactionFulfillment

Table

Defines a list of all those transactions which have a fulfillment plan attached to it.

1

TrxTransactionFulfillmentDetail

Table

Defines the details of all the transactions which have a fulfillment plan attached to them.

1

TrxTransactionLayaway

Table

Store layway type of transactions.

1

TrxTransactionSaleItem

Table

Stores sale/refund/delivery items attached to a transaction

1

CREATE function [dbo].[Integration_funcTransaction] (@TransactionKey bigint)

Returns Table

As

Return

(

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

-- B2. AR INVOICE Against SO(NO FULFILLMENT OR FULFILLMENT WITH INVENTORY ALLOCATION ON SALE)

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

SELECT 'ARI' AS DOCDESC, InvWarehouse.ID AS L_WAREHOUSECODE, InvProduct.AccountingID AS L_ITEMCODE, TrxTransactionSaleItem.Quantity AS L_QUANTITY

FROM [TrxTransaction]

INNER JOIN TrxTransactionSaleItem ON TrxTransactionSaleItem.TransactionKey = TrxTransaction.TransactionKey

INNER JOIN InvWarehouse ON TrxTransactionSaleItem.WarehouseKey = InvWarehouse.WarehouseKey

INNER JOIN InvProduct ON TrxTransactionSaleItem.ProductKey = InvProduct.ProductKey

WHERE   TrxTransaction.TransactionKey = @TransactionKey

AND  TrxTransaction.IsSuspended = 0

AND  TrxTransactionSaleItem.Type = 0

AND  (HasFulfillment = 0     --No Fulfillment

  OR ( HasFulfillment = 1 AND   --Fulfillment With Inventory Allocation on Sale

     (SELECT InventoryAllocationMethod

      FROM   TrxTransactionFulfillment INNER JOIN

       TrxTransactionFulfillmentDetail

        ON TrxTransactionFulfillment.FulfillmentKey = TrxTransactionFulfillmentDetail.FulfillmentKey

      WHERE  (TrxTransactionSaleItem.TransactionItemKey = TrxTransactionFulfillmentDetail.SourceDetailKey)

      AND (TrxTransactionFulfillment.TransactionKey = TrxTransactionSaleItem.TransactionKey)

      AND (TrxTransactionFulfillmentDetail.SourceType = 0)

      ) = 0

    )

   )

Union

SELECT 'ARISO' AS DOCDESC, InvWarehouse.ID AS L_WAREHOUSECODE, InvProduct.AccountingID AS L_ITEMCODE, TrxTransactionSaleItem.Quantity AS L_QUANTITY

FROM [TrxTransaction]

INNER JOIN TrxTransactionSaleItem ON TrxTransactionSaleItem.TransactionKey = TrxTransaction.TransactionKey

INNER JOIN InvWarehouse ON TrxTransactionSaleItem.WarehouseKey = InvWarehouse.WarehouseKey

INNER JOIN InvProduct ON TrxTransactionSaleItem.ProductKey = InvProduct.ProductKey

WHERE   TrxTransaction.TransactionKey = @TransactionKey

AND  TrxTransaction.IsSuspended = 0

AND  TrxTransactionSaleItem.Type = 3

AND  (HasFulfillment = 0     --No Fulfillment

  OR ( HasFulfillment = 1 AND   --Fulfillment With Inventory Allocation on Sale

     (SELECT InventoryAllocationMethod

      FROM   TrxTransactionFulfillment INNER JOIN TrxTransactionFulfillmentDetail

        ON TrxTransactionFulfillment.FulfillmentKey = TrxTransactionFulfillmentDetail.FulfillmentKey

      WHERE  (TrxTransactionSaleItem.TransactionItemKey = TrxTransactionFulfillmentDetail.SourceDetailKey)

      AND (TrxTransactionFulfillment.TransactionKey = TrxTransactionSaleItem.TransactionKey)

      AND (TrxTransactionFulfillmentDetail.SourceType = 0)

      ) = 0

    )

   )

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

-- E. Delivery based on Reserve Invoice

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

UNION

SELECT 'DEL' AS DocDesc, InvWarehouse.ID AS L_WAREHOUSECODE, InvProduct.AccountingID AS L_ITEMCODE, TrxTransactionSaleItem.Quantity AS L_QUANTITY

FROM [TrxTransaction]

INNER JOIN TrxTransactionSaleItem ON TrxTransactionSaleItem.TransactionKey = TrxTransaction.TransactionKey

INNER JOIN InvWarehouse ON TrxTransactionSaleItem.WarehouseKey = InvWarehouse.WarehouseKey

INNER JOIN InvProduct ON TrxTransactionSaleItem.ProductKey = InvProduct.ProductKey

LEFT OUTER JOIN TrxTransactionFulfillmentDetail As FulFillmentDetail on TrxTransactionSaleItem.OriginalDetailKey = FulFillmentDetail.SourceDetailKey

LEFT OUTER JOIN TrxTransactionFulfillment As FulFillmentHeader on FulFillmentDetail.FulfillmentKey = FulFillmentHeader.FulfillmentKey

WHERE   TrxTransaction.TransactionKey = @TransactionKey

AND  TrxTransaction.IsSuspended = 0

AND  TrxTransactionSaleItem.Type = 4

AND  FulFillmentHeader.InventoryAllocationMethod = 1

AND  FulFillmentDetail.SourceType = 0

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

-- F. LAYAWAY (AR Invoice)

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

UNION

SELECT 'LARI' AS DOCDESC, InvWarehouse.ID AS L_WAREHOUSECODE, InvProduct.AccountingID AS L_ITEMCODE, TrxTransactionLayaway.Quantity AS L_QUANTITY

FROM [TrxTransaction]

INNER JOIN TrxTransactionLayaway ON TrxTransactionLayaway.TransactionKey = TrxTransaction.TransactionKey

INNER JOIN InvWarehouse ON TrxTransactionLayaway.WarehouseKey = InvWarehouse.WarehouseKey

INNER JOIN InvProduct ON TrxTransactionLayaway.ProductKey = InvProduct.ProductKey

WHERE   TrxTransaction.TransactionKey = @TransactionKey

  AND TrxTransaction.IsSuspended = 0

  AND TrxTransactionLayaway.Type = 0

  AND TrxTransactionLayaway.InventoryAllocationMethod = 0

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

-- I. Delivery based on Layaway Reserve Invoice (LDEL)

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

UNION

SELECT 'LDEL' AS DocDesc, InvWarehouse.ID AS L_WAREHOUSECODE, InvProduct.AccountingID AS L_ITEMCODE, TrxTransactionSaleItem.Quantity AS L_QUANTITY

FROM [TrxTransaction]

INNER JOIN TrxTransactionSaleItem ON TrxTransactionSaleItem.TransactionKey = TrxTransaction.TransactionKey

INNER JOIN InvWarehouse ON TrxTransactionSaleItem.WarehouseKey = InvWarehouse.WarehouseKey

INNER JOIN InvProduct ON TrxTransactionSaleItem.ProductKey = InvProduct.ProductKey

LEFT OUTER JOIN TrxTransactionFulfillmentDetail As FulFillmentDetail on TrxTransactionSaleItem.OriginalDetailKey = FulFillmentDetail.SourceDetailKey

LEFT OUTER JOIN TrxTransactionFulfillment As FulFillmentHeader on FulFillmentDetail.FulfillmentKey = FulFillmentHeader.FulfillmentKey

WHERE   TrxTransaction.TransactionKey = @TransactionKey

AND  TrxTransaction.IsSuspended = 0

AND  TrxTransactionSaleItem.Type = 5

AND  FulFillmentHeader.InventoryAllocationMethod = 1

AND  FulFillmentDetail.SourceType = 2

UNION

SELECT 'CRM' AS DOCDESC, InvWarehouse.ID AS L_WAREHOUSECODE, InvProduct.AccountingID AS L_ITEMCODE, TrxTransactionSaleItem.Quantity * -1 AS L_QUANTITY

FROM [TrxTransaction]

INNER JOIN TrxTransactionSaleItem ON TrxTransactionSaleItem.TransactionKey = TrxTransaction.TransactionKey

INNER JOIN InvWarehouse ON TrxTransactionSaleItem.WarehouseKey = InvWarehouse.WarehouseKey

INNER JOIN InvProduct ON TrxTransactionSaleItem.ProductKey = InvProduct.ProductKey

WHERE   TrxTransaction.TransactionKey = @TransactionKey

AND  TrxTransaction.IsSuspended = 0

AND  TrxTransactionSaleItem.Type = 1

)