iVend Database Database : GetBatchTrackedProductDetails 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].[GetBatchTrackedProductDetails]

(

@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 InvBatch.BatchKey , InvBatch.BatchNumber, InvBatch.ProductKey, InvBatch.WarehouseKey, InvBatch.AvailableQuantity,

   InvBatch.ExpirationDate AS ExpirationDate, InvBatch.ManufacturingDate, InvBatch.AdmissionDate, InvBatch.Status, InvBatch.Price,

   InvBatch.InStockQuantity, InvBatch.CommittedQuantity, InvBatch.OnOrderQuantity, InvBatch.LostQuantity, InvBatch.InReturnQuantity,

   InvBatch.OnLayawayQuantity, InvBatch.AllocatedQuantity, InvBatch.ReservedQuantity, InvBatch.Comments

   FROM InvProduct

   INNER JOIN InvBatch ON InvProduct.ProductKey = InvBatch.ProductKey

   INNER JOIN InvWarehouse ON InvWarehouse.WarehouseKey = InvBatch.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 InvGoodReceiptBatch ON  InvGoodReceiptBatch.BatchKey = InvBatch.BatchKey

    INNER JOIN InvGoodReceiptDetail  On InvGoodReceiptDetail.ProductKey = InvBatch.ProductKey

    INNER JOIN PurPurchaseOrderDetail On InvGoodReceiptDetail.SourceDetailKey = PurPurchaseOrderDetail.PurchaseOrderDetailKey

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

END

IF @pPurchasingDocument = 'Goods Return'

BEGIN

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

          INNER JOIN InvGoodsReturnBatch ON InvGoodsReturnBatch.BatchKey = InvBatch.BatchKey

    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 InvInventoryTransactionBatch ON InvInventoryTransactionBatch.BatchKey = InvBatch.BatchKey'

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 InvGoodReceiptBatch AS InvGoodReceiptBatchGR ON  InvGoodReceiptBatchGR.BatchKey = InvBatch.BatchKey'

END

IF @pInventory = 'Stock Transfer Shipment'

BEGIN

SET @SqlStatement = @SqlStatement + '   INNER JOIN InvStockTransferBatch ON InvStockTransferBatch.BatchKey = InvBatch.BatchKey

   INNER JOIN InvStockTransferDetail ON InvStockTransferBatch.StockTransferDetailKey = InvStockTransferDetail.StockTransferDetailKey

   INNER JOIN RtlStore AS 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 = InvBatch.BatchKey

   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 = InvBatch.BatchKey

   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 = InvBatch.BatchKey

   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 = InvBatch.BatchKey

   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