Stock Turnover Analysis query

[millicentdark]

Hi Experts,

I have looked at the report in SAP Business one and it looks like it was made with commands. Is anyone privileged with the query that makes up the command in the screen below.

This is quiet urgent and i need it.

regards

Justice

[millicentdark · Accepted solution]

Hello Experts,

Any help on my request?

Regards

Justice

[frank_wang6 · Accepted solution]

it is store procedure. I copied content here, you can find the same thing inside ur database

ALTER Procedure [dbo].[CRSP_INVENTORY_TURNOVER_RATE]

  @FromDate datetime,

  @ToDate datetime,

  @Items nvarchar(4000)

As

BEGIN

  if object_id(N'tempdb..#CRSP_INVENTORY_ITEMS_LOCAL',N'U') is not null

  DROP TABLE #CRSP_INVENTORY_ITEMS_LOCAL

  CREATE  TABLE #CRSP_INVENTORY_ITEMS_LOCAL (ItemCode nvarchar(40));

  -- Insert selected result to temp table created above

  declare @sqlQuery nvarchar(4000)

  set @sqlQuery = N'INSERT INTO #CRSP_INVENTORY_ITEMS_LOCAL SELECT T0."ItemCode" FROM OITM T0 Where ' + @Items

  exec sp_executesql @sqlQuery

  select

  T1."LocType" as LocType,

  T1."LocCode" as LocCode,

  T1."ItemCode" as ItemCode,

  T1."CloseStock" as CloseStock,

  T4."OpenStock"as OpenStock,

  T5."IssueQty" as IssueQty,

  T6."LastDate" as LastDate,

  T2."OnHand" as OnHandTotal,

  T6."OnHand" as OnHand,

  T6."MinStock" as MinStock,

  T6."WhsCode" as WhsCode,

  T3."WhsName" as WhsName,

  T2."ItmsGrpCod" as ItmsGrpCod,

  T2."ItemName" as ItemName,

  T2."MinLevel" as MinLevel,

  T2."ByWh" as ByWh,

  T2."InvntryUom" as InvntryUom,

  T2."LeadTime" as LeadTime,

  T2."ItmsGrpNam" as ItmsGrpNam

  from

  (

  Select "LocType", "LocCode", "ItemCode", Sum("InQty" - "OutQty") as "CloseStock"

  From "OIVL"

  Where "DocDate" <= @ToDate  

  Group by "LocType", "LocCode", "ItemCode"

  ) T1 /*CloseStock*/

  Join

  (

  SELECT T0."ItemCode",

  T0."ItmsGrpCod",

  T0."ItemName",

  T0."OnHand",

  T0."MinLevel",

  T0."ByWh",

  T0."InvntryUom",

  T0."LeadTime",

  T1."ItmsGrpNam"

  From "OITM" T0

  Join "OITB" T1 On T0."ItmsGrpCod" = T1."ItmsGrpCod"

  Join #CRSP_INVENTORY_ITEMS_LOCAL T2 On T0."ItemCode" collate chinese_prc_ci_as = T2."ItemCode"

  ) T2 /*OITM & OITB*/

  on T1."ItemCode" = T2."ItemCode"

  Join

  (

  SELECT T0."WhsCode", T0."WhsName"

  From "OWHS" T0

  ) T3 /*OWHS*/ on T1."LocCode" = T3."WhsCode"

  left join

  (

  Select "LocType", "LocCode", "ItemCode", Sum("InQty" - "OutQty") as "OpenStock"

  From "OIVL"

  Where "DocDate" < @FromDate

  Group by "LocType", "LocCode", "ItemCode"

  ) T4 /*OpenStock*/

  on T1."LocType" = T4."LocType"

  and T1."LocCode" = T4."LocCode"

  and T1."ItemCode" = T4."ItemCode"

  left join

  (

  Select "LocType", "LocCode", "ItemCode", Sum("OutQty") as "IssueQty"

  From "OIVL"

  Where "DocDate" Between @FromDate And @ToDate

  Group by "LocType", "LocCode", "ItemCode"

  ) T5 /*IssueQty*/

  on T1."LocType" = T5."LocType"

  and T1."LocCode" = T5."LocCode"

  and T1."ItemCode" = T5."ItemCode"

  left join

  (

  select T1."LocType", T1."LocCode", T1."ItemCode", T1."LastDate",

    T2."WhsCode", T2."OnHand", T2."MinStock" from

  (

  Select "LocType", "LocCode", "ItemCode", max("DocDate") as "LastDate"

  From "OIVL"

  Where "InQty" > 0

  Group By "LocType", "LocCode", "ItemCode"

  ) T1 /*LastDate*/

  join

  (

  SELECT "ItemCode", "WhsCode", "OnHand", "MinStock" From "OITW"

  ) T2 /*OITW*/

  on T1."ItemCode" = T2."ItemCode" and T1."LocCode" = T2."WhsCode"

  ) T6

  on T1."LocType" = T6."LocType"

  and T1."LocCode" = T6."LocCode"

  and T1."ItemCode" = T6."ItemCode";

  --DROP TABLE #CRSP_INVENTORY_ITEMS_LOCAL; /* cause client of rev48 disconnected */

END

GO