Running shortage or cumulative shortage query

[former_member193355]

Dear Experts,

I am trying to use this following query to track the balance quantity of PO after receipt and return or shortage running quantity.  What I mean is the remain PO quantity sum from the supplier.

Here is the query:

select por1.LineNum+1"linenum", por1.ItemCode,   s.baseentry, s.returnRunningTotalQty"Qty Return",
isnull(por1.quantity,0)"Qty PO", (isnull(s.[remaining quantity after return],0))"running total GRPO", s.DocEntry"GRPO docentry",
isnull(por1.Quantity,0)-ISNULL(s.[remaining quantity after return],0)"shortage"
from
por1 inner join
( select pdn.DocEntry, pdn.BaseEntry, pdn.BaseLine, pdn.grporunningtotalqty, pdn.GRQty, rpd.ReturnRunningTotalQty, rpd.GRTQty, rpd.baseline"grtbaseline",
CASE when isnull(rpd.GRTQty,0) = 0
then isnull(pdn.grporunningtotalqty,0)
when isnull(rpd.GRTQty,0) > 0
then (isnull(pdn.grporunningtotalqty,0) - isnull(rpd.ReturnRunningTotalQty,0)) end as 'remaining quantity after return'
from
( select distinct
--a.Itemcode,
a.docentry,
      a.Quantity"GRQty", a.LineNum,
       a.Baseref,
       a.baseline,
       a2.basetype,
       a2.baseentry,
       a2.u_sol_return,
      grporunningtotalqty = SUM(isnull(a2.quantity,0))
    FROM pdn1 a
   
      INNER JOIN pdn1 a2 ON a.DocEntry >= a2.DocEntry
      --and a.BaseLine >= a2.baseline
    where a.BaseEntry = a2.BaseEntry  and a.BaseLine = a2.BaseLine
    and a.LineNum = a2.LineNum
   --(a2.U_RETURN = 'reject' or a.U_RETURN is null)
 
   --and a.BaseLine = '0'
    GROUP BY
      a.Itemcode, a.LineNum, a.BaseLine, a.DocEntry,
      a.quantity, a2.BaseType,
      a.Baseref, a2.BaseEntry,
             a2.u_sol_return
             ) pdn
             inner join OPDN on pdn.docentry = opdn.docentry
            
             left join
                   (
      select distinct
   
      --a.Itemcode,
      a.Quantity"GRTQty",
       a.Baseref,
       a.baseline,
       a2.basetype,
       a2.baseentry,
       a2.u_sol_return,
      ReturnRunningTotalQty = SUM(isnull(a.Quantity,0))
    FROM rpd1 a
      INNER JOIN rpd1 a2 ON a.DocEntry >= a2.DocEntry 
    where a.BaseEntry >= a2.BaseEntry  and a.itemcode = a2.itemcode and a.BaseLine >= a2.baseline
  and  (a2.U_SOL_RETURN = 'reject')
  --and a.LineNum = a2.LineNum
   --and a.BaseEntry = '1953'
   --and a.quantity >= a2.quantity
    GROUP BY
      a.Itemcode, a.BaseLine,
      a.quantity, a2.BaseType,
      a.Baseref, a2.BaseEntry,
             a2.u_sol_return
      --order by a.BaseEntry

--) rpd1

) RPD on

rpd.BaseEntry = pdn.DocEntry and
rpd.BaseRef = opdn.DocNum and
rpd.BaseLine = pdn.LineNum and
rpd.BaseType = opdn.ObjType
       
     
     
      ) s
      on s.BaseEntry = por1.DocEntry and s.BaseLine = por1.LineNum
      --and s.grtbaseline >= por1.LineNum
         
      where  s.BaseEntry = '1000'
      order by por1.LineNum asc

The result can be seen in the following picture:

The last row value in the shortage column should be 500 and not 300. It is the 300 + 200. 200 is return quantity.

I said so because the item code is same for no. 3 & 4 and also the PO qty.

The different of the both of linenum # 2  are the GRPO docnum.

Can you please help me to solve the issue because my query can't give the expected shortage to me ? I appreciate it so much. TIA

Steve

[Former Member]

Hi Steve,

   You can use Sub Query that is better solution, I give examples for oinv Table

select T1.DocNum,T1.DocTotal,T1.PaidToDate,T1.GrosProfit, (select sum(DocTotal)+sum(PaidToDate-GrosProfit) from

OINV T0 where T0.DocNum=T1.DocNum)

AS RunningTotal  from OINV T1

where CreateDate between '12/01/2012' and '12/01/2012'

You can use the above Query, this may be helpful to you.

[former_member193355]

Hi,

I agree with you but the issue is the sub query can give a report of balance quantity of PO after receipt and then returned partially or full return. So, there are 3 affected documents

The sample is correct for running total of one document only.

Steve

[Former Member]

Hi Steve.,

You will check the below Query. I hope that should helpful to you

  You can use Sub Query that is better solution, I give examples for oinv Table

select T0.DocNum,T0.DocTotal,T0.GrosProfit

,(select sum(T1.DocTotal+T1.GrosProfit) from OINV T1

                      where T1.DocNum <= T0.DocNum) as RunningTotal

from OINV T0 order by T0.DocNum

You can use the above Query, this may be helpful to you.

Hope Helpful

Thanks

Madeswaran

[Former Member]

Hi Steve,

if you use sql server 2012 means, You will use the below Query

select T0.DocNum,T0.DocTotal, T0.GrosProfit

,SUM(,T1.DocTotal+T1.GrosProfit) OVER (ORDER BY T0.DocNum ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) CUM_SUM

from OINV T0 order by T0.DocNum

Thanks

Madeswaran

[former_member193355]

Hi Mad,

Thank you for your reply.

I am using SQL 2008.

I appreciate your answer so far but I don't understand why your answer using OINV. I am not asking the OINV. I am asking PO.

Rgds,

Steve

[former_member193355]

Hi Experts,

Just to update you that my problem is actuall similar with the question in this link:

http://stackoverflow.com/questions/6180874/sql-running-subtraction-and-deviation?rq=1

but the solution in the link doesn't consider good return to calculate the running shortage quantity. thank you.

Rgds,

Steve

[Former Member]

Hi Steve,

       The problem solved or not. In case the solved then close the thread

Thanks

Madeswaran P

[former_member193355]

Not yet solved

[Former Member]

Hi Steve.,

select por1.LineNum+1 'linenum', por1.ItemCode,   s.baseentry, s.returnRunningTotalQty 'Qty Return',

isnull(por1.quantity,0) 'Qty PO', (isnull(s.[remaining quantity after return],0)) 'running total GRPO', s.DocEntry 'GRPO docentry',

isnull(por1.Quantity,0)-(ISNULL(s.[remaining quantity after return],0)+ ISNULL(s.returnRunningTotalQty,0))'shortage'

from

por1 inner join

( select pdn.DocEntry, pdn.BaseEntry, pdn.BaseLine, pdn.grporunningtotalqty, pdn.GRQty, rpd.ReturnRunningTotalQty, rpd.GRTQty, rpd.baseline"grtbaseline",

CASE when isnull(rpd.GRTQty,0) = 0

then isnull(pdn.grporunningtotalqty,0)

when isnull(rpd.GRTQty,0) > 0

then (isnull(pdn.grporunningtotalqty,0) - isnull(rpd.ReturnRunningTotalQty,0)) end as 'remaining quantity after return'

from

( select distinct

--a.Itemcode,

a.docentry,

      a.Quantity"GRQty", a.LineNum,

       a.Baseref,

       a.baseline,

       a2.basetype,

       a2.baseentry,

       a2.u_sol_return,

      grporunningtotalqty = SUM(isnull(a2.quantity,0))

    FROM pdn1 a

      INNER JOIN pdn1 a2 ON a.DocEntry >= a2.DocEntry

      --and a.BaseLine >= a2.baseline

    where a.BaseEntry = a2.BaseEntry  and a.BaseLine = a2.BaseLine

    and a.LineNum = a2.LineNum

   --(a2.U_RETURN = 'reject' or a.U_RETURN is null)

   --and a.BaseLine = '0'

    GROUP BY

      a.Itemcode, a.LineNum, a.BaseLine, a.DocEntry,

      a.quantity, a2.BaseType,

      a.Baseref, a2.BaseEntry,

             a2.u_sol_return

             ) pdn

             inner join OPDN on pdn.docentry = opdn.docentry

             left join

                   (

      select distinct

      --a.Itemcode,

      a.Quantity"GRTQty",

       a.Baseref,

       a.baseline,

       a2.basetype,

       a2.baseentry,

       a2.u_sol_return,

      ReturnRunningTotalQty = SUM(isnull(a.Quantity,0))

    FROM rpd1 a

      INNER JOIN rpd1 a2 ON a.DocEntry >= a2.DocEntry

    where a.BaseEntry >= a2.BaseEntry  and a.itemcode = a2.itemcode and a.BaseLine >= a2.baseline

  and  (a2.U_SOL_RETURN = 'reject')

  --and a.LineNum = a2.LineNum

   --and a.BaseEntry = '1953'

   --and a.quantity >= a2.quantity

    GROUP BY

      a.Itemcode, a.BaseLine,

      a.quantity, a2.BaseType,

      a.Baseref, a2.BaseEntry,

             a2.u_sol_return

      --order by a.BaseEntry

--) rpd1

) RPD on

rpd.BaseEntry = pdn.DocEntry and

rpd.BaseRef = opdn.DocNum and

rpd.BaseLine = pdn.LineNum and

rpd.BaseType = opdn.ObjType

      ) s

      on s.BaseEntry = por1.DocEntry and s.BaseLine = por1.LineNum

      --and s.grtbaseline >= por1.LineNum

      where  s.BaseEntry = '1000'

      order by por1.LineNum asc

I Hope this will be helpful

Thanks

Madeswaran

[former_member193355]

Hi Mades,

Thank you for your reply.

The result is not as expected. The last row shortage, no. 4, is not 500 but still 300.

The row no.3 must 1000 because there are 200 return after receipt 1200.

Please help

Regds,

steve

[Former Member]

Hi Steve

What is the formula you have for calculating Shortage quantity?

The formula in the query is quite confusing