iVend Database Database : Integration_GoodsIssue Stored Procedure

Creation Date

7/4/2019 5:45 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@InventoryTransactionKey

In

The transaction key for which Goods Issue transactions needs to be pulled from iVend

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

CfgReasonCode

Table

Stores the various reason codes created in the system.

1

InvInventoryTransaction

Table

This gets affected when the inventory of a particular item is consumed at the store itself for any reason.

1

InvInventoryTransactionBatch

Table

This gets affected in case the product on the goods issue note is batch tracked.

1

InvInventoryTransactionDetail

Table

Defines the details of the product on the goods issue note

1

InvInventoryTransactionSerial

Table

This gets affected in case the product on the goods issue note is serial tracked.

1

InvProduct

Table

Stores the product related details.

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

--Based on the output of this procedure the Inventory Goods Issue document is getting posted in SAP Business One.

--If the item is serial or batch managed then this procedure does not returns the serial and batch no. for the item.

--Instead the 'Integration_GoodsIssue_SerialBatch' prcedure gives the serail and batch information for the item.

--the output of both the procedure is joined based on 'U_CXS_TDKY' field of both the procedure's output

--##SUMMARY_END

CREATE PROCEDURE [dbo].[Integration_GoodsIssue]

@InventoryTransactionKey NVARCHAR(50)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @COUNT INT, @COUNTER INT, @UserDefinedFields NVARCHAR(MAX), @FieldName NVARCHAR(100)

SET @UserDefinedFields = ''

CREATE TABLE #tmp_UserDefinedFields (RowNum INT, FieldName NVARCHAR(100));

WITH UserDefinedFields(FieldName)

AS

 (

  SELECT ', InvInventoryTransaction.' + B.FieldName + ' AS H_' + B.FieldName

  FROM CfgUserDefinedTable A

  INNER JOIN CfgUserDefinedFieldDetail B ON A.UserDefinedTableKey = B.UserDefinedTableKey

  WHERE A.TableName = 'InvInventoryTransaction'

  UNION ALL

  SELECT ', InvInventoryTransactionDetail.' + B.FieldName + ' AS L_' + B.FieldName

  FROM CfgUserDefinedTable A

  INNER JOIN CfgUserDefinedFieldDetail B ON A.UserDefinedTableKey = B.UserDefinedTableKey

  WHERE A.TableName = 'InvInventoryTransactionDetail'

 ) INSERT INTO #tmp_UserDefinedFields SELECT ROW_NUMBER() OVER (ORDER BY FieldName), FieldName FROM UserDefinedFields

SET @COUNT = @@ROWCOUNT

SET @COUNTER = 1

WHILE @COUNTER <= @COUNT

BEGIN

  SELECT @FieldName = FieldName FROM #tmp_UserDefinedFields WHERE RowNum = @COUNTER

  SET @UserDefinedFields = @UserDefinedFields + @FieldName

  SET @COUNTER = @COUNTER + 1

END

DECLARE @SQL NVARCHAR(MAX)

  -- Insert statements for procedure here

SELECT

       InvInventoryTransaction.InventoryTransactionKey AS U_CXS_TRKY,

       InvInventoryTransaction.Id As ReferenceNumber,

       InvInventoryTransaction.BusinessDate AS DocDate,

       InvInventoryTransaction.BusinessDate AS TaxDate,

       InvInventoryTransaction.SiteId As SiteId,

       InvWarehouse.Id AS WarehouseCode,

       InvInventoryTransactionDetail.InvInventoryTransactionDetailKey AS U_CXS_TDKY,

       InvProduct.AccountingID AS ItemCode,

       InvInventoryTransactionDetail.Quantity As L_Quantity,

      ISNULL(InvInventoryTransactionDetail.UOMQuantity ,0) AS L_UOM_Quantity,

      ISNULL(InvInventoryTransactionDetail.UOMKey,0) AS L_UOMKey,

        (

      Select Count(1)

      From InvInventoryTransactionSerial

      Where InventoryTransactionDetailKey = InvInventoryTransactionDetail.InvInventoryTransactionDetailKey

       ) AS SerialRows,

       (

      Select Count(1)

      From InvInventoryTransactionBatch

      Where InventoryTransactionDetailKey = InvInventoryTransactionDetail.InvInventoryTransactionDetailKey

       ) AS BatchRows,

      cast(isNull(InvInventoryTransaction.Comments, '') as nvarchar(254)) As H_Comment,

      cast(isNull(InvInventoryTransactionDetail.Comments, '') as nvarchar(100)) As L_Comment,

      ISNULL(LineReasonCode.Id,'') As L_ReasonCode,

      ISNULL(HeaderReasonCode.Id,'') As H_ReasonCode,

      CASE WHEN InvWarehouse.BranchCode = 'SBO' THEN '0' ELSE InvWarehouse.BranchCode END AS H_BranchKey

FROM         InvInventoryTransaction INNER JOIN InvInventoryTransactionDetail ON InvInventoryTransaction.InventoryTransactionKey = InvInventoryTransactionDetail.InventoryTransactionKey

      INNER JOIN  InvWarehouse ON InvInventoryTransaction.WarehouseKey = InvWarehouse.WarehouseKey

      INNER JOIN  InvProduct ON InvInventoryTransactionDetail.ProductKey = InvProduct.ProductKey

      LEFT OUTER JOIN CfgReasonCode As LineReasonCode On InvInventoryTransactionDetail.ReasonKey = LineReasonCode.ReasonCodeKey

      LEFT OUTER JOIN CfgReasonCode As HeaderReasonCode On InvInventoryTransaction.ReasonKey = HeaderReasonCode.ReasonCodeKey

WHERE   (InvInventoryTransactionDetail.Type IN (0,1))

AND  (InvInventoryTransaction.InventoryTransactionKey = @InventoryTransactionKey )

SET @SQL = '

SELECT InvInventoryTransaction.InventoryTransactionKey, InvInventoryTransactionDetail.InvInventoryTransactionDetailKey ' + @UserDefinedFields + '

FROM InvInventoryTransaction

 INNER JOIN InvInventoryTransactionDetail  ON InvInventoryTransaction.InventoryTransactionKey = InvInventoryTransactionDetail.InventoryTransactionKey

WHERE  (InvInventoryTransactionDetail.Type IN (0,1))

AND InvInventoryTransaction.InventoryTransactionKey =  ''' + CAST( @InventoryTransactionKey AS NVARCHAR(50))+''''

EXEC (@SQL)

END

-- Drop stored procedure if it already exists

SET ANSI_NULLS ON