iVend Database Database : Integration_GoodsReceipt Stored Procedure

Creation Date

7/4/2019 5:45 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@GoodsReceiptKey

In

The Goods Receipt key (Primary Key of InvGoodReceipt) for which the Goods Rceipt 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

InvGoodReceipt

Table

Stores the Goods receipt done in the system.

1

InvGoodReceiptBatch

Table

This is affected in case the goods receipt is done for a batch product.

1

InvGoodReceiptDetail

Table

Defines the details associated with each goods receipt

1

InvGoodReceiptSerial

Table

This is affected in case the goods receipt is done for a serial tracked product.

1

InvProduct

Table

Stores the product related details.

1

InvStockTransfer

Table

Stores stock transfer details

1

InvStockTransferDetail

Table

Detail table which stores the stock transfer detail information.

1

InvWarehouse

Table

Defines a list of all warehouses defined in the system.

1

PmtCurrency

Table

Define a list of all currecies defined in the system.

1

PurPurchaseOrder

Table

Stores information related to Purchase order transactions done from Console or done from ERP on behalf of this store.

1

PurPurchaseOrderDetail

Table

Stores the line details of the purchase order.

1

PurVendor

Table

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

1

TaxTaxCode

Table

TaxCode is defined at the SBO and it's replicated from SBO to iVend. We can only view the TaxCodes at MC and associate them with the different objects, like Product, Customer, Enterprise, etc.

1

CREATE PROCEDURE [dbo].[Integration_GoodsReceipt]

          @GoodsReceiptKey NVARCHAR(50)

AS

BEGIN

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

    -- interfering with SELECT statements.

SET NOCOUNT ON;

--DECLARE @lPriceListKey NVARCHAR(50)

--SELECT @lPriceListKey = CostPriceListKey From RtlStore Where WarehouseKey = (Select ToWarehouseKey From InvGoodReceipt Where GoodsReceiptKey =  @GoodsReceiptKey)

--IF @lPriceListKey is null

-- SET @lPriceListKey = 0

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

  FROM CfgUserDefinedTable A

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

  WHERE A.TableName = 'InvGoodReceipt'

  UNION ALL

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

  FROM CfgUserDefinedTable A

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

  WHERE A.TableName = 'InvGoodReceiptDetail'

 ) 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     CASE WHEN InvGoodReceipt.SOURCETYPE IN(0, 5) THEN 59 ELSE 20 END AS H_DocType,

    InvGoodReceipt.SourceType,

    InvGoodReceipt.GoodsReceiptKey AS H_CXS_TRKY,

    InvWarehouse.Id AS L_WarehouseCode,

    H.Id as InTransitWarehouseKey,

    InvGoodReceipt.SiteId AS H_CXS_FRST,

    InvGoodReceipt.ReceiptDate AS H_DocDate,

    InvGoodReceipt.ReceiptDate AS H_DocDueDate,

    InvGoodReceipt.ReceiptDate AS H_TaxDate,

    InvGoodReceipt.Id AS H_CXS_TRID,

    InvGoodReceiptDetail.GoodsReceiptDetailKey AS CXS_TDKY,

    PurPurchaseorder.AccountingID,

    InvGoodReceiptDetail.SourceKey,

    SourceDetailKey,

    InvProduct.AccountingID AS L_ItemCode,

    InvGoodReceiptDetail.QuantityReceived AS L_Quantity,

    ISNULL(InvGoodReceiptDetail.UOMQuantityReceived ,0) AS L_UOM_Quantity,

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

    --CASE WHEN InvGoodReceipt.SOURCETYPE = 5 And InvGoodReceiptDetail.Price = 0 Then

    -- ISNULL((Select Price From PrcPriceMatrix Where ProductKey = InvGoodReceiptDetail.ProductKey And PriceListKey = @lPriceListKey),0)

    --else InvGoodReceiptDetail.Price END As L_UnitPrice,

    InvGoodReceiptDetail.Price As L_UnitPrice,

    (Select Count(*) From InvGoodReceiptSerial Where GoodReceiptDetailKey = InvGoodReceiptDetail.GoodsReceiptDetailKey ) AS SerialRows,

    (Select Count(*) From InvGoodReceiptBatch Where GoodReceiptDetailKey = InvGoodReceiptDetail.GoodsReceiptDetailKey ) AS BatchRows,

    PurVendor.ID As VendorCode,

    InvGoodReceiptDetail.LineNumber,

    ISNULL(( Select PurPurchaseOrderDetail.LineNumber

    From  PurPurchaseOrderDetail

    Where PurPurchaseOrderDetail.PurchaseOrderDetailKey = InvGoodReceiptDetail.SourceDetailKey

    ), -1) As P_L_LineNumber,

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

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

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

    Case LTRIM(RTRIM(InvStockTransfer.BranchCode)) When 'SBO' Then InvStockTransfer.Id Else Convert(NVARCHAR, InvGoodReceiptDetail.SourceKey) End As RequestNumber,

    TaxTaxCode.AccountingID As L_TaxCode,

    InvProduct.Description As L_ItemDescription,

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

    isNull(InvGoodReceipt.ReferenceNumber, '') As H_ReferenceNumber,

    CASE WHEN InvGoodReceipt.BusinessPartnerType = 0 THEN ''

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

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

    END AS H_CardCode,

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

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

    IsNull(InvStockTransfer.AccountingID, '') AS SourceAccountingID,

    IsNull(InvStockTransfer.Status, -1) AS SourceStatus,

    ISNULL(PmtCurrency.Id, '') AS H_DOCCURRENCY,

    CASE WHEN InvStockTransferDetail.Status = 1 AND ISNULL(InvStockTransferDetail.ShortQuantityReceived, 0) > 0 THEN 1 ELSE 0 END AS L_ShortReceived,

    ISNULL(InvStockTransferDetail.QuantityOpen ,0) AS L_ShortQuantity,

    ISNULL(InvStockTransferDetail.UOMQuantityOpen ,0) AS L_UOM_ShortQuantity,

    CASE WHEN ISNULL(InvGoodReceiptDetail.SourceDetailKey, '0') = '0' THEN 1 WHEN ISNULL(InvStockTransferDetail.QuantityReceived, 0) > ISNULL(InvStockTransferDetail.Quantity ,0) THEN 1 ELSE 0 END AS L_OverReceived,

    CASE WHEN ISNULL(InvGoodReceiptDetail.SourceDetailKey, '0') = '0' THEN InvGoodReceiptDetail.QuantityReceived WHEN ISNULL(InvStockTransferDetail.QuantityReceived, 0) > ISNULL(InvStockTransferDetail.Quantity ,0) THEN ISNULL(InvStockTransferDetail.QuantityReceived, 0) - ISNULL(InvStockTransferDetail.Quantity ,0) ELSE 0 END AS L_OverReceivedQuantity,

    CASE WHEN ISNULL(InvGoodReceiptDetail.SourceDetailKey, '0') = '0' THEN InvGoodReceiptDetail.UOMQuantityReceived WHEN ISNULL(InvStockTransferDetail.UOMQuantityReceived, 0) > ISNULL(InvStockTransferDetail.UOMQuantity ,0) THEN ISNULL(InvStockTransferDetail.UOMQuantityReceived, 0) - ISNULL(InvStockTransferDetail.UOMQuantity ,0) ELSE 0 END AS L_UOM_OverReceivedQuantity,

     InvGoodReceipt.ExchangeRate

  FROM        InvGoodReceipt INNER JOIN InvGoodReceiptDetail ON InvGoodReceipt.GoodsReceiptKey = InvGoodReceiptDetail.GoodsReceiptKey

                                  INNER JOIN InvWarehouse ON InvGoodReceipt.ToWarehouseKey = InvWarehouse.WarehouseKey

                                  INNER JOIN InvProduct ON InvGoodReceiptDetail.ProductKey = InvProduct.ProductKey

                                  LEFT OUTER JOIN (PurPurchaseorder INNER JOIN PurVendor ON PurPurchaseorder.VendorKey = PurVendor.VendorKey) ON PurPurchaseorder.PurchaseOrderKey = InvGoodReceiptDetail.SourceKey AND InvGoodReceipt.SourceType = 1

        LEFT OUTER JOIN InvWarehouse H ON InvWarehouse.IntransitWarehouseKey = H.WarehouseKey

        --LEFT OUTER JOIN CfgComment As HeaderComment On InvGoodReceipt.CommentKey = HeaderComment.CommentKey

        --LEFT OUTER JOIN CfgComment As LineComment On InvGoodReceiptDetail.CommentKey = LineComment.CommentKey

        LEFT OUTER JOIN InvStockTransfer On InvGoodReceiptDetail.SourceKey = InvStockTransfer.StockTransferKey

        LEFT OUTER JOIN TaxTaxCode On InvGoodReceiptDetail.TaxKey = TaxTaxCode.TaxCodeKey

        LEFT OUTER JOIN CfgReasonCode As HeaderReasonCode On InvGoodReceipt.ReasonCodeKey = HeaderReasonCode.ReasonCodeKey

        LEFT OUTER JOIN CfgReasonCode As LineReasonCode On InvGoodReceiptDetail.ReasonCodeKey = LineReasonCode.ReasonCodeKey

        LEFT OUTER JOIN PmtCurrency On InvGoodReceipt.DocumentCurrencyKey = PmtCurrency.CurrencyKey

        LEFT OUTER JOIN InvStockTransferDetail ON  InvGoodReceiptDetail.SourceDetailKey = InvStockTransferDetail.StockTransferDetailKey AND InvGoodReceipt.SourceType = 2

WHERE  (InvGoodReceipt.GoodsReceiptKey = @GoodsReceiptKey )

ORDER BY SerialRows, BatchRows, InvGoodReceiptDetail.GoodsReceiptDetailKey

--GET ALL THE UDF Data

SET @SQL = '

SELECT InvGoodReceipt.GoodsReceiptKey, InvGoodReceiptDetail.GoodsReceiptDetailKey ' + @UserDefinedFields + '

FROM InvGoodReceipt

INNER JOIN InvGoodReceiptDetail ON InvGoodReceipt.GoodsReceiptKey = InvGoodReceiptDetail.GoodsReceiptKey

WHERE InvGoodReceipt.GoodsReceiptKey = ''' + CAST( @GoodsReceiptKey AS NVARCHAR(50)) + ''''

EXEC (@SQL)

          --SELECT GoodsReceiptKey

END