iVend Database Database : rpt_StockTransfer Stored Procedure

Creation Date

9/12/2019 6:40 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@FromDate

In

From Date (YYYYMMDD Format)

VarChar

8

@ToDate

In

To Date (YYYYMMDD Format)

VarChar

8

@Subsidiary

In

VarWChar

100

@Store

In

Subsidiary IdStore Id

VarWChar

100

@OpenOnly

In

Display only Open Stock transfers

Boolean

1

@Shipment

In

Specify Whether this is Stock transfer Shipment Report

Boolean

1

@Receipt

In

Specify Whether this is Stock transfer Receipt Report

Boolean

1

@RETURN_VALUE

Return Value

Integer

4

Database Object

Object Type

Description

Dep Level

CfgSiteInformation

Table

Stores the site information. Enterprise always has the site id as 1. System Table Warning : Should not be modified directly

1

RtlStore

Table

Contains the details of Stores defined in the system.

1

--  procedure Stock Transfer Reports are generated.

-- Case1 (@Shipment = 1) Then Stock Transfer Shipment Report is generated at iVend.

-- Case2 (@Receipt = 1) Then Stock Transfer Receipt Report is generated at iVend.

CREATE PROCEDURE [dbo].[rpt_StockTransfer] (

      @FromDate VARCHAR(8),

      @ToDate VARCHAR(8),

@Subsidiary NVARCHAR(100),

      @Store NVARCHAR(100),

      @OpenOnly BIT,

      @Shipment BIT,

      @Receipt BIT

         )

AS

BEGIN

--Exec rpt_StockTransfer '', '', '', '', 1, 1, 0

--Exec rpt_StockTransfer '', '', '', 1, 0, 1

--Select * From RtlStore

If @Store = ''

Begin

Select @Store = RtlStore.Id

From CfgSiteInformation, RtlStore

Where CfgSiteInformation.SiteId = RtlStore.SiteId

End

Create Table #temp

(

 ReferenceNumber1 NVARCHAR(100),

 FromWarehouseKey NVARCHAR(200),

 FromStoreId NVARCHAR(100),

 FromStoreDesc NVARCHAR(200),

 Location   NVARCHAR(200),

 ToWarehouseKey NVARCHAR(200),

 ToStoreId   NVARCHAR(100),

 ToStoreDesc NVARCHAR(200),

 LineNum   SMALLINT,

 ProdId   NVARCHAR(100),

 ProdDesc   NVARCHAR(200),

 Quantity   DECIMAL(20, 5),

 QuantityReceived DECIMAL(20, 5),

 Store   NVARCHAR(100),

 UOM   NVARCHAR(200),

 Subsidiary NVARCHAR(100),

 SubsidiaryDesc NVARCHAR(200)

)

Set NoCount On

If @Shipment = 1

Begin

--Shipped

Insert Into #temp

Select StockTfrHed.ReferenceNumber1,

   StockTfrHed.FromWarehouseKey,

   Store.Id As FromStoreId, Store.Description As FromStoreDesc,

InvLocation.Description AS Location  ,

   StockTfrHed.ToWarehouseKey,

  Space(40) As ToStoreId, space(200) As ToStoreDesc,

   StockTfrDet.LineNumber,

   Product.Id As ProdId,

   StockTfrDet.Description As ProdDesc,

   StockTfrDet.UOMQuantity,

   StockTfrDet.UOMQuantityReceived,

   Store.Id As Store,

   InvUOM.Description,

Sub.Id As Subsidiary,

Sub.Description AS SubsidiaryDesc

From RtlStore As Store WITH (NOLOCK)

INNER JOIN InvStockTransfer As StockTfrHed WITH (NOLOCK) ON Store.WarehouseKey = StockTfrHed.FromWarehouseKey

  INNER JOIN InvStockTransferDetail As StockTfrDet WITH (NOLOCK) ON StockTfrHed.StockTransferKey = StockTfrDet.StockTransferKey

  INNER JOIN InvProduct As Product WITH (NOLOCK) ON StockTfrDet.ProductKey = Product.ProductKey

  Left Outer JOIN InvUOM WITH (NOLOCK) ON StockTfrDet.UOMKey=InvUOM.UOMKey

LEFT OUTER JOIN INVLOCATION WITH (NOLOCK) ON StockTfrDet.fromLOCATIONKEY = InvLocation.LocationKey

LEFT OUTER JOIN RtlSubsidiary Sub WITH (NOLOCK) ON Store.SubsidiaryKey = Sub.SubsidiaryKey

Where StockTfrHed.Status = CASE WHEN @OpenOnly = 1 THEN 0 ELSE StockTfrHed.Status END

And Convert(Varchar, StockTfrHed.BusinessDate, 112) BETWEEN CASE WHEN @FromDate = '' THEN CONVERT(VARCHAR, StockTfrHed.BusinessDate, 112) ELSE @FromDate END

AND CASE WHEN @ToDate = '' THEN CONVERT(VARCHAR, StockTfrHed.BusinessDate, 112) ELSE @ToDate END

And  Store.Id = @Store

And ISNULL(Sub.Id,'') = CASE WHEN @Subsidiary = '' THEN ISNULL(Sub.Id,'') ELSE @Subsidiary END

Update #temp

Set  ToStoreId = Store.Id,

   ToStoreDesc = Store.Description

From #temp As temp, RtlStore As Store

Where temp.ToWarehouseKey = Store.WarehouseKey

End

Else If @Receipt = 1

Begin

--Receipt

Insert Into #temp

Select StockTfrHed.ReferenceNumber1,

   StockTfrHed.FromWarehouseKey,

  Space(40) As FromStoreId, space(200) As FromStoreDesc,

InvLocation.Description AS Location  ,

   StockTfrHed.ToWarehouseKey,

   Store.Id As ToStoreId, Store.Description As ToStoreDesc,

   StockTfrDet.LineNumber,

   Product.Id As ProdId,

   StockTfrDet.Description As ProdDesc,

   StockTfrDet.UOMQuantity,

   StockTfrDet.UOMQuantityReceived,

   Store.Id As Store,

   InvUOM.Description,

Sub.Id As Subsidiary,

Sub.Description AS SubsidiaryDesc

From RtlStore As Store WITH (NOLOCK)

INNER JOIN InvStockTransfer As StockTfrHed WITH (NOLOCK) ON Store.WarehouseKey = StockTfrHed.ToWarehouseKey

  INNER JOIN InvStockTransferDetail As StockTfrDet WITH (NOLOCK) ON StockTfrHed.StockTransferKey = StockTfrDet.StockTransferKey

  INNER JOIN InvProduct As Product WITH (NOLOCK) ON StockTfrDet.ProductKey = Product.ProductKey

  LEFT OUTER JOIN InvUOM WITH (NOLOCK) ON StockTfrDet.UOMKey=InvUOM.UOMKey

LEFT OUTER JOIN INVLOCATION WITH (NOLOCK) ON StockTfrDet.fromLOCATIONKEY = InvLocation.LocationKey

LEFT OUTER JOIN RtlSubsidiary Sub WITH (NOLOCK) ON Store.SubsidiaryKey = Sub.SubsidiaryKey

Where StockTfrHed.Status = Case When @OpenOnly = 1 Then 0 Else StockTfrHed.Status End

And Convert(Varchar, StockTfrHed.BusinessDate, 112) Between Case When @FromDate = '' Then Convert(Varchar, StockTfrHed.BusinessDate, 112) else @FromDate End And Case When @ToDate = '' then Convert(Varchar, StockTfrHed.BusinessDate, 112) else @ToDate end --Date Filter

And  Store.Id = @Store

And ISNULL(Sub.Id,'') = Case When @Subsidiary = '' Then ISNULL(Sub.Id,'') Else @Subsidiary End

Update #temp

Set  FromStoreId = Store.Id,

   FromStoreDesc = Store.Description

From #temp As temp, RtlStore As Store

Where temp.FromWarehouseKey = Store.WarehouseKey

End

Select * From #temp Order By ReferenceNumber1

Set NoCount Off

End