iVend Database Database : GetSerialTrackedProductDetails Stored Procedure

Creation Date

8/11/2019 8:38 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pProductCodeFrom

In

VarWChar

50

@pProductCodeTo

In

VarWChar

50

@pProductGroupKey

In

VarWChar

50

@pPurchasingDocument

In

VarWChar

50

@pSalesDocument

In

VarWChar

50

@pInventory

In

VarWChar

50

@pStoreKey

In

VarWChar

50

@RETURN_VALUE

Return Value

Integer

4

CREATE PROCEDURE [dbo].[GetSerialTrackedProductDetails]

(

@pProductCodeFrom NVARCHAR(50),

@pProductCodeTo NVARCHAR(50),

@pProductGroupKey NVARCHAR(50),

@pPurchasingDocument NVARCHAR(50),

@pSalesDocument NVARCHAR(50),

@pInventory   NVARCHAR(50),

@pStoreKey   NVARCHAR(50)

)

AS

BEGIN

SET NOCOUNT ON

DECLARE @SqlStatement AS NVARCHAR(MAX)

SET @SqlStatement = 'SELECT DISTINCT  InvSerial.SerialKey, InvSerial.SerialNumber, InvSerial.ProductKey, InvSerial.WarehouseKey, InvSerial.ExpirationDate AS ExpirationDate,

   InvSerial.ManufacturingDate, InvSerial.AdmissionDate, InvSerial.Status, InvSerial.Price,

   InvSerial.SystemNumber, InvSerial.ManufacturerSerialNumber, InvSerial.LocationKey, InvSerial.Comments

  FROM InvProduct

   INNER JOIN InvSerial ON InvProduct.ProductKey = InvSerial.ProductKey

   INNER JOIN InvWarehouse ON InvWarehouse.WarehouseKey = InvSerial.WarehouseKey '

--Purchasing Document Conditions

IF @pPurchasingDocument IS NOT NULL

BEGIN

IF @pPurchasingDocument = 'Goods Receipt PO'

BEGIN

SET @SqlStatement = @SqlStatement + ' INNER JOIN RtlStore ON RtlStore.WarehouseKey = InvWarehouse.WarehouseKey

    INNER JOIN InvGoodReceipt ON InvGoodReceipt.ToWarehouseKey = InvWarehouse.WarehouseKey

    INNER JOIN InvGoodReceiptSerial ON  InvGoodReceiptSerial.SerialKey = InvSerial.SerialKey

    INNER JOIN InvGoodReceiptDetail  On InvGoodReceiptDetail.ProductKey = InvSerial.ProductKey

    INNER JOIN PurPurchaseOrderDetail On InvGoodReceiptDetail.SourceDetailKey = PurPurchaseOrderDetail.PurchaseOrderDetailKey

    INNER JOIN PurPurchaseOrder On InvGoodReceiptDetail.SourceKey = PurPurchaseOrder.PurchaseOrderKey  '

END

IF @pPurchasingDocument = 'Goods Return'

BEGIN

IF CHARINDEX('RtlStore', @SqlStatement) = 0

BEGIN

  SET @SqlStatement = @SqlStatement + '  INNER JOIN RtlStore ON RtlStore.WarehouseKey = InvWarehouse.WarehouseKey '

END

SET @SqlStatement = @SqlStatement + '  INNER JOIN InvGoodsReturnSerial ON InvGoodsReturnSerial.SerialKey = InvSerial.SerialKey

    INNER JOIN InvGoodsReturn ON InvGoodsReturn.WarehouseKey = InvWarehouse.WarehouseKey'

END

END

--Inventory

IF @pInventory IS NOT NULL

BEGIN

IF @pInventory = 'Goods Issue'

BEGIN

IF CHARINDEX('RtlStore', @SqlStatement) = 0

BEGIN

  SET @SqlStatement = @SqlStatement + '  INNER JOIN RtlStore ON RtlStore.WarehouseKey = InvWarehouse.WarehouseKey '

END

SET @SqlStatement = @SqlStatement + '  INNER JOIN InvInventoryTransaction ON InvInventoryTransaction.WarehouseKey = InvWarehouse.WarehouseKey

      INNER JOIN InvInventoryTransactionSerial ON InvInventoryTransactionSerial.SerialKey = InvSerial.SerialKey'

END

IF @pInventory = 'Goods Receipt'

BEGIN

IF CHARINDEX('RtlStore', @SqlStatement) = 0

BEGIN

  SET @SqlStatement = @SqlStatement + '  INNER JOIN RtlStore ON RtlStore.WarehouseKey = InvWarehouse.WarehouseKey '

END

SET @SqlStatement = @SqlStatement + ' INNER JOIN InvGoodReceipt AS InvGoodReceiptGR ON InvGoodReceiptGR.ToWarehouseKey = InvWarehouse.WarehouseKey

      INNER JOIN InvGoodReceiptSerial AS InvGoodReceiptSerialGR ON  InvGoodReceiptSerialGR.SerialKey = InvSerial.SerialKey'

END

IF @pInventory = 'Stock Transfer Shipment'

BEGIN

SET @SqlStatement = @SqlStatement + ' INNER JOIN InvStockTransferSerial ON InvStockTransferSerial.SerialKey = InvSerial.SerialKey

   INNER JOIN InvStockTransferDetail ON InvStockTransferSerial.StockTransferDetailKey = InvStockTransferDetail.StockTransferDetailKey

   INNER JOIN RtlStore RtlStoreSTShipment ON  InvStockTransferDetail.ToWarehouseKey = RtlStoreSTShipment.WarehouseKey'

END

END

--Sales Documents

IF @pSalesDocument IS NOT NULL

BEGIN

IF @pSalesDocument = 'Sale'

BEGIN

IF CHARINDEX('RtlStore', @SqlStatement) = 0

BEGIN

  SET @SqlStatement = @SqlStatement + '  INNER JOIN RtlStore ON RtlStore.WarehouseKey = InvWarehouse.WarehouseKey '

END

SET @SqlStatement = @SqlStatement + '  INNER JOIN TrxTransactionSaleItem ON TrxTransactionSaleItem.ProductDetailKey = InvSerial.SerialKey

   INNER JOIN TrxTransaction ON TrxTransaction.TransactionKey = TrxTransactionSaleItem.TransactionKey

   WHERE (TrxTransactionSaleItem.Type = 0 OR TrxTransactionSaleItem.Type = 3) AND TrxTransaction.IsSuspended = ''FALSE'' AND TrxTransaction.IsVoided = 0'

END

  IF @pSalesDocument = 'Refund'

BEGIN

IF CHARINDEX('RtlStore', @SqlStatement) = 0

BEGIN

  SET @SqlStatement = @SqlStatement + '  INNER JOIN RtlStore ON RtlStore.WarehouseKey = InvWarehouse.WarehouseKey '

END

SET @SqlStatement = @SqlStatement + ' INNER JOIN TrxTransactionSaleItem ON TrxTransactionSaleItem.ProductDetailKey = InvSerial.SerialKey

   INNER JOIN TrxTransaction ON TrxTransaction.TransactionKey = TrxTransactionSaleItem.TransactionKey

   WHERE TrxTransactionSaleItem.Type = 1 AND TrxTransaction.IsSuspended = ''FALSE'' AND TrxTransaction.IsVoided = 0'

END

  IF @pSalesDocument = 'Layaway'

BEGIN

IF CHARINDEX('RtlStore', @SqlStatement) = 0

BEGIN

  SET @SqlStatement = @SqlStatement + '  INNER JOIN RtlStore ON RtlStore.WarehouseKey = InvWarehouse.WarehouseKey '

END

SET @SqlStatement = @SqlStatement + ' INNER JOIN TrxTransactionLayAway ON TrxTransactionLayAway.ProductDetailKey = InvSerial.SerialKey

   INNER JOIN TrxTransaction ON TrxTransaction.TransactionKey = TrxTransactionLayAway.TransactionKey

   WHERE TrxTransaction.IsSuspended = ''FALSE'' AND TrxTransaction.IsVoided = 0 AND TrxTransactionLayAway.[Type] = 0 AND TrxTransactionLayAway.Status<>2'

END

IF @pSalesDocument = 'Fulfillment'

BEGIN

IF CHARINDEX('RtlStore', @SqlStatement) = 0

BEGIN

  SET @SqlStatement = @SqlStatement + '  INNER JOIN RtlStore ON RtlStore.WarehouseKey = InvWarehouse.WarehouseKey '

END

SET @SqlStatement = @SqlStatement + ' INNER JOIN TrxTransactionFulfillmentDetail ON TrxTransactionFulfillmentDetail.ProductDetailKey = InvSerial.SerialKey

   INNER JOIN TrxTransactionFulfillment ON TrxTransactionFulfillment.TransactionKey = TrxTransactionFulfillmentDetail.TransactionKey

   INNER JOIN TrxTransaction ON TrxTransaction.TransactionKey = TrxTransactionFulfillment.TransactionKey

   WHERE TrxTransaction.IsSuspended = ''FALSE'' AND TrxTransaction.IsVoided = 0 '

END

END

IF CHARINDEX('RtlStoreSTShipment', @SqlStatement) = 0 AND CHARINDEX('RtlStore', @SqlStatement) = 0

BEGIN

SET @SqlStatement = @SqlStatement + '  INNER JOIN RtlStore ON RtlStore.WarehouseKey = InvWarehouse.WarehouseKey '

END

--Where Clause

IF CHARINDEX('WHERE', @SqlStatement) > 0

BEGIN

SET @SqlStatement = @SqlStatement + ' AND ((InvProduct.Id >= @pProductCodeFrom OR @pProductCodeFrom IS NULL) AND (InvProduct.Id <= @pProductCodeTo OR @pProductCodeTo IS NULL))

    AND (InvProduct.ProductGroupKey = @pProductGroupKey OR @pProductGroupKey IS NULL)'

END

ELSE

BEGIN

SET @SqlStatement = @SqlStatement + ' WHERE ((InvProduct.Id >=  @pProductCodeFrom OR @pProductCodeFrom IS NULL) AND (InvProduct.Id <= @pProductCodeTo OR @pProductCodeTo IS NULL))

    AND (InvProduct.ProductGroupKey = @pProductGroupKey OR @pProductGroupKey IS NULL)'

END

IF CHARINDEX('RtlStoreSTShipment', @SqlStatement) > 0

BEGIN

SET @SqlStatement = @SqlStatement + ' AND (RtlStoreSTShipment.StoreKey = @pStoreKey OR @pStoreKey IS NULL)'

END

ELSE

BEGIN

SET @SqlStatement = @SqlStatement + ' AND (RtlStore.StoreKey = @pStoreKey OR @pStoreKey IS NULL)'

END

EXEC SP_EXECUTESQL @SqlStatement, N'@pProductCodeFrom  NVARCHAR(50), @pProductCodeTo NVARCHAR(50), @pProductGroupKey NVARCHAR(50), @pStoreKey NVARCHAR(50)',

    @pProductCodeFrom, @pProductCodeTo, @pProductGroupKey, @pStoreKey

END