Query Correction

[Former Member]

Hi friends kindly correct below query

Below Query i need ware house wise stock also where i consider particular warehouses (ExRm','ExWip','Rework','ExSc')

Already i have taken instock(total onhand stock) details for  these four warehouse (ExRm','ExWip','Rework','ExSc') and i also need separate warehouse stock also . how i modify below query...

DECLARE @RunDate as Varchar(100)

SET @RunDate=(Select GETDATE())

SELECT T0.[ItemCode], T0.[ItemName],t0.U_pgmname,T0.[CardCode],T0.ItmsGrpCod,T0.ValidFor,T1.[CardName],

SUM( T3.[OnHand]) as 'Instock', T0.[MinLevel], T0.[OrdrIntrvl], T2.Name,T0.[OrdrMulti],

(CASE WHEN (T0.[U_MinInvDays] > 0 AND T0.[MinLevel] > 0) THEN ((Sum(T3.[OnHand]))/(T0.[MinLevel]/T0.[U_MinInvDays])) ELSE 0 END)[Coverage]

FROM OITM T0  INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode

inner join OCYC T2 ON T0.[OrdrIntrvl]=T2.Code

inner join OITW T3 ON T3.ItemCode=t0.ItemCode

WHERE T0.[ItmsGrpCod] ='109' and T0.ValidFor='Y' and t0.U_pgmname='Gear' and T0.ItemCode='1234'

and T3.WhsCode in ('ExRm','ExWip','Rework','ExSc')

group by T0.[ItemCode], T0.[ItemName],t0.U_pgmname,T0.[CardCode],T0.ItmsGrpCod,T0.ValidFor,

T1.[CardName],T0.[MinLevel],T0.[OrdrIntrvl], T2.Name,T0.[OrdrMulti], T0.[MinOrdrQty], T0.[U_MinInvDays]

[former_member212181 · Accepted solution]

Hi Ramesh,

Please try below Query

DECLARE @RunDate as Varchar(100)

SET @RunDate=(Select GETDATE())

SELECT T0.[ItemCode], T0.[ItemName],t0.U_pgmname,T0.[CardCode],T0.ItmsGrpCod,T0.ValidFor,T1.[CardName],

SUM( T3.[OnHand]) as 'Instock', T0.[MinLevel], T0.[OrdrIntrvl], T2.Name,T0.[OrdrMulti],

(CASE WHEN (T0.[U_MinInvDays] > 0 AND T0.[MinLevel] > 0) THEN ((Sum(T3.[OnHand]))/(T0.[MinLevel]/T0.[U_MinInvDays])) ELSE 0 END)[Coverage]

FROM OITM T0

INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode

inner join OCYC T2 ON T0.[OrdrIntrvl]=T2.Code

inner join OITW T3 ON T3.ItemCode=t0.ItemCode

WHERE T0.[ItmsGrpCod] ='109'

and T0.ValidFor='Y'

and t0.U_pgmname='Gear'

and T0.ItemCode='1234'

and T3.WhsCode = '[%0]'

--in ('ExRm','ExWip','Rework','ExSc')

group by T0.[ItemCode], T0.[ItemName],t0.U_pgmname,T0.[CardCode],T0.ItmsGrpCod,T0.ValidFor,

T1.[CardName],T0.[MinLevel],T0.[OrdrIntrvl], T2.Name,T0.[OrdrMulti], T0.[MinOrdrQty], T0.[U_MinInvDays]

Thanks

Unnikrishnan

[Former Member · Accepted solution]

please find out my attachment you can also understand what kind of result i need

[KennedyT21 · Accepted solution]

Try this

DECLARE @RunDate AS VARCHAR(100)

SET @RunDate = (

        SELECT GETDATE()

    )

SELECT T0.[ItemCode],

       T0.[ItemName],

       t0.U_pgmname,

       T0.[CardCode],

       T0.ItmsGrpCod,

       T0.ValidFor,

       T1.[CardName],

       (

           SELECT SUM(c.onhand)

           FROM   oitw c

           WHERE  c.itemcode = t3.itemcode

                  AND c.whscode = 'ExRm'

       ) AS ExRm,

       (

           SELECT SUM(d.onhand)

           FROM   oitw d

           WHERE  d.itemcode = t3.itemcode

                  AND d.whscode = 'ExWip'

       ) AS ExWip,

       (

           SELECT SUM(e.onhand)

           FROM   oitw e

           WHERE  e.itemcode = t3.itemcode

                  AND e.whscode = 'Rework'

       ) AS Rework,

       (

           SELECT SUM(f.onhand)

           FROM   oitw f

           WHERE  f.itemcode = t3.itemcode

                  AND f.whscode = 'ExSc'

       ) AS ExSc,

       SUM(T3.[OnHand]) AS 'Instock',

       T0.[MinLevel],

       T0.[OrdrIntrvl],

       T2.Name,

       T0.[OrdrMulti],

       (

           CASE

                WHEN (T0.[U_MinInvDays] > 0 AND T0.[MinLevel] > 0) THEN ((SUM(T3.[OnHand])) / (T0.[MinLevel] / T0.[U_MinInvDays]))

                ELSE 0

           END

       )[Coverage]

FROM   OITM T0

       INNER JOIN OCRD T1

            ON  T0.CardCode = T1.CardCode

       INNER JOIN OCYC T2

            ON  T0.[OrdrIntrvl] = T2.Code

       INNER JOIN OITW T3

            ON  T3.ItemCode = t0.ItemCode

WHERE  T0.[ItmsGrpCod] = '109'

       AND T0.ValidFor = 'Y'

       AND t0.U_pgmname = 'Gear'

       AND T0.ItemCode = '1234'

       AND T3.WhsCode IN ('ExRm', 'ExWip', 'Rework', 'ExSc')

GROUP BY

       T0.[ItemCode],

       T0.[ItemName],

       t0.U_pgmname,

       T0.[CardCode],

       T0.ItmsGrpCod,

       T0.ValidFor,

       T1.[CardName],

       T0.[MinLevel],

       T0.[OrdrIntrvl],

       T2.Name,

       T0.[OrdrMulti],

       T0.[MinOrdrQty],

       T0.[U_MinInvDays]

Regards

Kennedy

[Former Member]

Hi...

Try this one also,

SELECT DISTINCT

T2.ItemCode,

   T2.ItemName,

   t5.DistNumber 'Batch Number',

   t5.MnfDate 'Manufacture Date',

t5.Indate 'Batch InDate',

   T6.Quantity,T6.WhsCode,T5.U_MRP1,T5.U_Production_Date_02 as 'Expected Release Date'

FROM OWTR T0

INNER JOIN WTR1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OITM T2 ON T2.ItemCode = T1.ItemCode

INNER JOIN OILM T3 ON T1.DocEntry = T3.DocEntry

AND T1.ObjType = T3.TransType

AND T1.LineNum = T3.DocLineNum

INNER JOIN ILM1 T4 ON T3.MessageID = T4.MessageID

INNER JOIN OBTN T5 ON T4.ItemCode = T5.ItemCode

AND T4.SysNumber = T5.SysNumber

INNER JOIN OBTQ T6 ON T6.ItemCode = T5.ItemCode

AND T6.SysNumber = T5.SysNumber

WHERE T6.WhsCode = [%0]

GROUP BY T5.DistNumber,

   T2.ItemCode,

   T2.ItemName,

   T1.WhsCode,

   T5.MnfDate,

   T4.Quantity,

   T6.Quantity,

   T6.WhsCode,

   T5.U_MRP1,

   T5.U_Production_Date_02,

   T5.InDate

   Order By T2.ItemName

Thanks,

Harshal Makwana

[Former Member]

Hi Ramesh ,

Try this query, Select any one warehouse to see the result for that particular warehouse and if you want to see all the warehouse then no need to select warehouse code, Just give OK in parameter window

DECLARE @RunDate as Varchar(100)

SET @RunDate=(Select GETDATE())

SELECT T0.[ItemCode], T0.[ItemName],t0.U_pgmname,T0.[CardCode],T0.ItmsGrpCod,T0.ValidFor,T1.[CardName],

SUM( T3.[OnHand]) as 'Instock', T0.[MinLevel], T0.[OrdrIntrvl], T2.Name,T0.[OrdrMulti],

(CASE WHEN (T0.[U_MinInvDays] > 0 AND T0.[MinLevel] > 0) THEN ((Sum(T3.[OnHand]))/(T0.[MinLevel]/T0.[U_MinInvDays])) ELSE 0 END)[Coverage]

FROM OITM T0  INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode

inner join OCYC T2 ON T0.[OrdrIntrvl]=T2.Code

inner join OITW T3 ON T3.ItemCode=t0.ItemCode

WHERE T0.[ItmsGrpCod] ='109' and T0.ValidFor='Y' and t0.U_pgmname='Gear' and T0.ItemCode='1234'

and (T3.WhsCode ='[%0]' or '[%0]'='')

group by T0.[ItemCode], T0.[ItemName],t0.U_pgmname,T0.[CardCode],T0.ItmsGrpCod,T0.ValidFor,

T1.[CardName],T0.[MinLevel],T0.[OrdrIntrvl], T2.Name,T0.[OrdrMulti], T0.[MinOrdrQty], T0.[U_MinInvDays]

[Former Member]

[Former Member]

Hi Ramesh,

Try this query

DECLARE @RunDate as Varchar(100)

SET @RunDate=(Select GETDATE())

SELECT T0.[ItemCode], T0.[ItemName],t0.U_pgmname

,T0.[CardCode],T0.ItmsGrpCod,T0.ValidFor,T1.[CardName],

SUM( T3.[OnHand]) as 'Instock',

(select sum(w.OnHand ) from oitw w where w.ItemCode=t3.ItemCode and w.WhsCode='ExRm') 'ExRm',

(select sum(w.OnHand ) from oitw w where w.ItemCode=t3.ItemCode and w.WhsCode='ExWip') 'ExWip',

(select sum(w.OnHand ) from oitw w where w.ItemCode=t3.ItemCode and w.WhsCode='Rework') 'Rework',

(select sum(w.OnHand ) from oitw w where w.ItemCode=t3.ItemCode and w.WhsCode='ExSc') 'ExSc',

T0.[MinLevel], T0.[OrdrIntrvl], T2.Name,T0.[OrdrMulti],

(CASE WHEN (T0.[U_MinInvDays] > 0 AND T0.[MinLevel] > 0) THEN ((Sum(T3.[OnHand]))/(T0.[MinLevel]/T0.[U_MinInvDays])) ELSE 0 END)[Coverage]

FROM OITM T0  INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode

inner join OCYC T2 ON T0.[OrdrIntrvl]=T2.Code

inner join OITW T3 ON T3.ItemCode=t0.ItemCode

WHERE T0.[ItmsGrpCod] ='109' and T0.ValidFor='Y' and t0.U_pgmname='Gear' and T0.ItemCode='1234'

and T3.WhsCode in ('ExRm','ExWip','Rework','ExSc')

group by T0.[ItemCode],t3.ItemCode, T0.[ItemName],t0.U_pgmname,T0.[CardCode],T0.ItmsGrpCod,T0.ValidFor,

T1.[CardName],T0.[MinLevel],T0.[OrdrIntrvl], T2.Name,T0.[OrdrMulti], T0.[MinOrdrQty], T0.[U_MinInvDays]