Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 
KennedyT21
Active Contributor

select a.ItemCode,a.[Name],SUM(a.[OB-Qty]) [OB-Qty],SUM(a.[OB-Value]) [OB-Value],

sum(a.[Issue]) [Issue] ,sum(a.[Receipt]) [Receipt],

SUM(a.[Cls-Qty]) [Cls-Qty], SUM([ClsValue]) [ClsValue]  from(

select t1.ItemCode,max(t1.dscription) [Name],

(sum(isnull(t1.inqty,0)) - sum(isnull(t1.outqty,0)) ) [OB-Qty],

sum(isnull(t1.transvalue,0)) [OB-Value],0 [Issue], 0 [Receipt],

0 [Cls-Qty],0 [ClsValue]

from OINM t1 where

t1.docdate < '[%0]' and t1.[Warehouse] = '[%3]' group by t1.ItemCode

union all

select t1.ItemCode,max(t1.dscription) [Name],0 [OB-Qty],0 [OB-Value],

(sum(isnull(t1.outqty,0))) [Issue],

(sum(isnull(t1.inqty,0))) [Receipt],

0 [Cls-Qty],0 [ClsValue]

from OINM t1

where t1.docdate >= '[%0]' and  t1.DocDate <= '[%1]' and t1.[Warehouse] = '[%3]'

group by t1.ItemCode

union all

select t1.ItemCode,max(t1.dscription) [Name],0 [OB-Qty],0 [OB-Value],

0 [Issue],0 [Receipt],

(sum(isnull(t1.inqty,0)) - sum(isnull(t1.outqty,0)) ) [Cls-Qty],

sum(isnull(t1.transvalue,0))  [ClsValue]

from OINM t1

where t1.DocDate <= '[%1]' and t1.[Warehouse] = '[%3]'

group by t1.ItemCode)a

group by a.ItemCode,a.[Name]

-----------------------------------------------------------------------------------------------------------------------------------------------------------------

Declare @ToDate Datetime

set @FromDate =

    (Select min(S0.Docdate) from OINM S0 where S0.Docdate >='[%0]')

set @ToDate =

    (Select max(S1.Docdate) from OINM S1 where S1.Docdate <='[%1]')

select * from

(

    SELECT T0.itemcode,

    min(T0.Dscription) as 'Item Description',

    min(B1.ItmsGrpNam) as 'Item Group', W1.Whscode, C1.Location,

    (isnull((

        Select sum(isnull(inqty,0))

        from OINM O1

        where O1.itemcode=T1.itemcode

        and O1.Warehouse=W1.Whscode

        and O1.docdate<@FromDate ),0)-

    isnull((

        Select sum(isnull(outqty,0))

        from OINM O1

        where O1.itemcode=T1.itemcode

        and O1.Warehouse=W1.Whscode

        and O1.docdate<@FromDate),0)

    ) as [Opening Stock],

    isnull((

        Select sum(isnull(inqty,0))

        from OINM O1

        where O1.itemcode=T1.itemcode

        and O1.Warehouse=W1.Whscode

        and O1.docdate>=@FromDate

        and O1.docdate<=@ToDate and O1.inqty>0

        and O1.transtype in (20,18)),0

    ) as [Purchase Quantity],

    isnull((

        Select sum(isnull(outqty,0))

        from OINM O1

        where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode

        and O1.docdate>=@FromDate and O1.docdate<=@ToDate

        and O1.outqty>0 and O1.transtype in (21,19)),0

    ) as [Purchase Return Quantity],

   isnull((

        Select sum(isnull(outqty,0))

        from OINM O1

        where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode

        and O1.docdate>=@FromDate and O1.docdate<=@ToDate and O1.outqty>0

        and O1.transtype in (13,15)),0

    ) as [sale Quatity],

    (isnull

        ((

        Select sum(isnull(inqty,0))

        from OINM O1

        where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode

        and O1.docdate<=@ToDate),0

        )-

        isnull((

            Select sum(isnull(outqty,0))

            from OINM O1

            where O1.itemcode=T1.itemcode and O1.Warehouse=W1.Whscode

            and O1.docdate<=@ToDate),0)

    ) as [Closing Stock]

    FROM OINM T0

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

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

    INNER JOIN OITB B1 ON T1.ItmsGrpCod=B1.ItmsGrpCod

    INNER JOIN OWHS W1 ON T2.WhsCode = W1.WhsCode

    INNER JOIN OLCT C1 ON W1.Location=C1.Code

    Group by T1.itemcode, T0.Itemcode, W1.WhsCode, C1.Location

) a

where (a.[Opening Stock]

        +a.[Purchase Quantity]

        + a.[Purchase Return Quantity]

        +a.[sale Quatity]+a.[Closing Stock]

       ) !=0

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

declare @a char(60)

set @a  = (select distinct t20.docdate from oinm t20 where t20.docdate = '[%2]')

select  distinct c.code, c.name

,SUM(c.bal) [Bal],sum(c.Val) [Val],

(case when avg(c.[FullStock]) > 0 then avg(c.[Pric]) / avg(c.[FullStock]) end ) [AvgPrice],

(case when SUM(c.[0-30 Days]) >= SUM(c.bal)  then SUM(c.bal)  else SUM(c.[0-30 Days]) end) [0-30 Days],

(case when SUM(c.[0-30 Days]) >= SUM(c.bal)  then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >=  SUM(c.bal) then (SUM(c.bal) - sum(c.[0-30 Days]))

else sum(c.[30-60 Days]) end)end)   [30-60 Days],

(case when SUM(c.[0-30 Days]) >= SUM(c.bal)  then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >=  SUM(c.bal) then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days])) >= SUM(c.bal) then

(SUM(c.bal) - (sum(c.[0-30 Days]) + sum(c.[30-60 Days]))) else

sum(c.[60-90 Days])end)end) end)   [60-90 Days],

(case when SUM(c.[0-30 Days]) >= SUM(c.bal)  then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >=  SUM(c.bal) then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days])) >= SUM(c.bal) then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days]) + sum(c.[90-180 Days])) >= SUM(c.bal) then

(SUM(c.bal) - (sum(c.[0-30 Days]) + sum(c.[30-60 Days]) + sum(c.[60-90 Days]))) else sum(c.[90-180 Days])

end)end) end) end)  [90-180 Days],

(case when SUM(c.[0-30 Days]) >= SUM(c.bal)  then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >=  SUM(c.bal) then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days])) >= SUM(c.bal) then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days]) + sum(c.[90-180 Days])) >= SUM(c.bal) then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days]) +

sum(c.[90-180 Days]) +  sum(c.[180-360 Days]) ) >= SUM(c.bal) then

(SUM(c.bal) - (sum(c.[0-30 Days]) + sum(c.[30-60 Days]) + sum(c.[60-90 Days]) + sum(c.[90-180 Days])))

else sum(c.[180-360 Days])end)end)end)end)end) [180-360 Days],

(case when SUM(c.[0-30 Days]) >= SUM(c.bal)  then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >=  SUM(c.bal) then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days])) >= SUM(c.bal) then 0 else sum(c.[Above 360 Days])

end)end)end) [Above 360 Days]

from (

select b.code, b.name,

b.Bal, b.Val,b.[Pric],b.[FullStock],

isnull(case when b.days <=30 then b.Bal end,0)[0-30 Days] ,

isnull(case when b.days <= 30 then b.val end,0)[0-30 Days-Value] ,

isnull(case when b.days between 31 and 60 then b.Bal end,0) [30-60 Days],

isnull(case when b.days between 61 and 90 then b.Bal end,0) [60-90 Days],

isnull(case when b.days between 91 and 180 then b.Bal end,0) [90-180 Days],

isnull(case when b.days between 181 and 360 then b.Bal end,0) [180-360 Days],

isnull(case when b.days >= 361 then b.Bal end,0) [Above 360 Days]

from

(

select a.code,a.name,

a.bal,a.val,datediff(dd,dt,@a)'days',a.[Receipt],a.[Pric],a.[FullStock]

from

(

select max(t0.itemcode)'Code',max(t0.Dscription)'Name',

(select sum(isnull(t11.transvalue,0))  from oinm t11 where (t11.itemcode = (t0.itemcode))

and (t11.docdate <= '[%2]' )) [Pric],

(select (sum(isnull(t11.inqty,0)) - sum(isnull(t11.outqty,0)) ) from oinm t11 where (t11.itemcode = (t0.itemcode))

and (t11.docdate <= '[%2]' )) [FullStock],

--(t0.Warehouse)'Wh',

(sum(isnull(t0.inqty,0))) [Receipt],

(sum(isnull(t0.inqty,0)) - sum(isnull(t0.outqty,0)) )'Bal',sum(isnull(t0.transvalue,0))'Val',(t0.docdate)'dt'

from oinm t0 inner join oitm t1 on t0.itemcode=t1.itemcode inner join oitb ob on ob.itmsgrpcod = t1.itmsgrpcod

where  (ob.itmsgrpnam = '[%1]')

and (t0.docdate <= '[%2]')

group by t0.itemcode,t0.docdate

)a

)b

)c where c.bal>0

group by c.code, c.name

Hope Helpful

Regards

Kennedy

4 Comments
Labels in this area