iVend Database Database : Integration_StockTransfer Stored Procedure

Creation Date

7/4/2019 5:45 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@StockTransferKey

In

(Primary Key of InvStockTransfer) for which the Stock Transfer information 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

CusCustomer

Table

Defines the customer details that interact with the enterprise.

1

InvProduct

Table

Stores the product related details.

1

InvStockTransfer

Table

Stores stock transfer details

1

InvStockTransferBatch

Table

This gets affected in case the stock transfer is done for a batch product

1

InvStockTransferDetail

Table

Detail table which stores the stock transfer detail information.

1

InvStockTransferSerial

Table

This gets affected in case the stock transfer is done for a serially tracked product

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

PurVendor

Table

Defines the vendor details of an enterprise from which the products are purchased.

1

RtlPOSDocumentNumberSeries

Table

Stores the Document Number series details for a POS

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

--Based on the output Stock Transfer document gets posted in SAP Business One.

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

--Instead the 'Integration_StockTransferSerialBatch' procedure gives the serail and batch information for the item.

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

--##SUMMARY_END

CREATE PROCEDURE [dbo].[Integration_StockTransfer]

    -- Add the parameters for the stored procedure here

    @StockTransferKey NVARCHAR(50)

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    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 ', A.' + B.FieldName + ' AS H_' + B.FieldName

  FROM CfgUserDefinedTable A

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

  WHERE A.TableName = 'InvStockTransfer'

  UNION ALL

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

  FROM CfgUserDefinedTable A

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

  WHERE A.TableName = 'InvStockTransferDetail'

 ) 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)

  SELECT A.StockTransferKey AS H_CXS_TRKY,

      B.Id as ToWarehouseKey,

      H.Id as InTransitWarehouseKey,

      (Select CASE WHEN Count(*) = 0 THEN 'Y' ELSE 'N' END From RtlStore I Where I.WarehouseKey = A.ToWarehouseKey) AS NonStoreWarehouse,

      C.Id as FromWarehouseKey,A.SiteId,A.BusinessDate,

      A.Id As RequestNumber,A.RequestedDate,A.ReferenceNumber1,A.ReferenceNumber2,A.Status,A.CommentKey,A.Created,A.CreatedBy,

      A.Modified,A.ModifiedBy,A.BranchCode,A.AccountingID,D.StockTransferDetailKey AS CXS_TDKY,

      D.LineNumber,E.AccountingID AS ItemCode,D.Description,

      D.Quantity As L_Quantity,

      ISNULL(D.UOMQuantity ,D.Quantity) AS L_UOM_Quantity,

      ISNULL(D.UOMKey,'0') AS L_UOMKey,

      D.QuantityOpen,D.QuantityReceived,D.QuantityShipped,D. Status as LineStatus,

      (Select Count(*) From InvStockTransferSerial F Where F.StockTransferDetailKey = D.StockTransferDetailKey) AS SerialRows,

      (Select Count(*) From InvStockTransferBatch G Where G.StockTransferDetailKey = D.StockTransferDetailKey ) AS BatchRows,

      (Select SiteID from rtlstore where rtlstore.warehousekey=A.FromWarehouseKey) AS RTLSiteID,

      isNull(Nullif(A.SalesPersonKey, '0'), '-1') As SalesPersonKey,

      A.Comments Comment,

      CASE WHEN A.POSDocumentNumberSeriesKey !='0' AND LEN(ISNULL(A.Id, '')) >= LEN(ISNULL(N.Prefix,''))

        THEN RIGHT(ISNULL(A.Id,''), LEN(ISNULL(A.Id,''))- LEN(ISNULL(N.Prefix,'')))

        ELSE '' END         AS H_FolioNumber,

      'FALSE'   AS H_ManualFolioNumber,

      CASE WHEN BusinessPartnerType = 0 THEN ''

        WHEN BusinessPartnerType = 1 THEN ISNULL((SELECT Id FROM CusCustomer WHERE CustomerKey =  A.BusinessPartnerKey AND IsDeleted = 0), '')

        WHEN BusinessPartnerType = 2 THEN ISNULL((SELECT Id FROM PurVendor WHERE VendorKey =  A.BusinessPartnerKey AND IsDeleted = 0), '')

        END AS H_CardCode,

      ISNULL(J.Id,'') As H_ReasonCode,--Changes for Reason Code

      ISNULL(K.Id,'') As L_ReasonCode, --Changes for Reason Code

      A.SourceType, D.OriginalDocumentSourceKey AS SourceKey, D.SourceKey AS SourceDetailKey

    FROM InvStockTransfer A INNER JOIN InvWarehouse B ON A.ToWarehouseKey = B.WarehouseKey

      INNER JOIN InvWarehouse C ON A.FromWarehouseKey = C.WarehouseKey

      INNER JOIN InvStockTransferDetail D ON A.StockTransferKey = D.StockTransferKey

      INNER JOIN InvProduct E ON D.ProductKey = E.ProductKey

      LEFT OUTER JOIN InvWarehouse H ON B.InTransitWarehouseKey = H.WarehouseKey

      --LEFT OUTER JOIN CFGComment I ON A.CommentKey = I.CommentKey

      LEFT OUTER JOIN RtlPOSDocumentNumberSeries N on A.POSDocumentNumberSeriesKey = N.POSDocumentNumberSeriesKey

      LEFT OUTER JOIN CfgReasonCode As J On A.ReasonCodeKey = J.ReasonCodeKey --Changes for Reason Code

      LEFT OUTER JOIN CfgReasonCode As K On D.ReasonCodeKey = K.ReasonCodeKey --Changes for Reason Code

    WHERE    (A.StockTransferKey = @StockTransferKey )

      And  A.SourceType = 1

SET @SQL = '

SELECT A.StockTransferKey, D.StockTransferDetailKey ' + @UserDefinedFields + '

FROM InvStockTransfer A

INNER JOIN InvStockTransferDetail D ON A.StockTransferKey = D.StockTransferKey

WHERE  A.StockTransferKey = ''' + CAST( @StockTransferKey AS NVARCHAR(50)) + ''' AND A.SourceType = 1 '

EXEC (@SQL)

END