11 Replies Latest reply: Mar 13, 2013 11:00 AM by steve andre RSS

Running shortage or cumulative shortage query

steve andre
Currently Being Moderated

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:

 

shorta.png

 

 

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

  • Re: Running shortage or cumulative shortage query
    kamba dasan
    Currently Being Moderated

    Hi Steve

     

    What is the formula you have for calculating Shortage quantity?

     

    The formula in the query is quite confusing

  • Re: Running shortage or cumulative shortage query
    madeswaran ponnusamy
    Currently Being Moderated

    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

  • Re: Running shortage or cumulative shortage query
    madeswaran ponnusamy
    Currently Being Moderated

    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.

Actions