Skip to Content

Inventory Aging Report


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

To report this post you need to login first.

34 Comments

You must be Logged on to comment or reply to a post.

  1. naga srinu

    Dear sir pales help me chart of accounts queries

    for ex:=

                                          april may jun july  in all mounts balancess in  mount wise pivot table querys

    5004 purchase of milk a/c

    plase tell me

    <removed by moderator> 

    venubabu

    (0) 
    1. Swapnil Vichare

      Hi naga srinu

      I think this Query will full fill your requirement.

      Declare @test VARCHAR(100)

      Select @test= AcctCode from dbo.OACT S0 where S0.AcctCode= ‘[%0]’

      select *

      from

      (select  datename(month, T0.RefDate) As Mth,T1.[Account]

      ,SUM(T1.[Debit] – T1.[Credit]) as [Opening Balance]

      from OJDT T0  INNER JOIN

           JDT1 T1 ON T0.TransId = T1.TransId AND T1.[Account] = @test AND T0.RefDate between ‘20130401’ and ‘20140331’

           group by datename(month, T0.RefDate),T1.[Account]

      ) PP

      pivot( sum([Opening Balance]) for mth in ([April],[May],[June],[July],[August],[September],[October],[November],[December],[January],[February],[March])) as X

      (0) 
    2. naga srinu

      Swapnil Vichare

      your Query report is ok

      THANKING YOU

      G VENU BABU

      And another problem 

      Dear sir pales help me chart of accounts queries

      for ex:=

                                            april may jun july  in all mounts balancess in  mount wise pivot table querys

      5004 purchase of milk a/c BANALNCESS IN B.P CODE WISE

      plase tell me

      VENUBABU

      (0) 
        1. naga srinu

          chart of account 5004 balancess in year (mounth wise) report is ok

          but another Query is

          1. chart of account  balance’s in year (mounts wise balance’s) project code wise query

          2.chart of account balance’s in year (mounts wise balances) vendor code wise query (based on journal eatery)

          please tell me

          venubabu

          (0) 
            1. Swapnil Vichare

              This query gives you project wise & Account wise report.

              If you required a report for all accounts and required selection of Project Code then just remove  “AND T1.[Account] = @AccCode” From Join Between OJDT & JDT1

              Declare @AccCode VARCHAR(100)

              Select @AccCode= AcctCode from dbo.OACT S0 where S0.AcctCode= ‘[%0]’

              Declare @PrjCode VARCHAR(100)

              Select @PrjCode= PrjCode from dbo.OPRJ S1 where S1.PrjCode= ‘[%1]’

              select *

              from

              (select  datename(month, T0.RefDate) As Mth,T1.[Account]

              ,SUM(T1.[Debit] – T1.[Credit]) as [Opening Balance]

              from OJDT T0  INNER JOIN

                   JDT1 T1 ON T0.TransId = T1.TransId AND T1.[Account] = @AccCode AND T0.RefDate between ‘20130401’ and ‘20140331’ Inner Join

                   OACT T2 ON T1.Account=T2.AcctCode Left Outer Join

                   OPRJ T3 ON T2.Project=T3.PrjCode

              Where T3.PrjCode =@PrjCode     

              group by datename(month, T0.RefDate),T1.[Account]

              ) PP

              pivot( sum([Opening Balance]) for mth in ([April],[May],[June],[July],[August],[September],[October],[November],[December],[January],[February],[March])) as X

              (0) 
              1. naga srinu

                DEAR SIR REPORT IS EXECUTE BUT OUT PUT NO DATA

                HELP ME CHANGE QUERY

                MY PROBLEM

                1.ONE GL A/C CODE IS  SELECTED DISPLAY THE PROJECT CODE 

                WISE,ALL MOUNT’S BALANCES  IN SAP BUSINESS ON QUERY

                (OR)

                2.GL A/C EX:- 5004 IS FIXED AND PROJECT CODE EX:-(CHI,MAP,GKD, NVP) IS FIXED

                AND DISPLAY REPORT TITLES  (PROJECT CODE ,ALL MONTHS)

                3.ONE GL A/C CODE IS  SELECTED ,PROJECT CODE  SELECTED

                VENDOR CODE WISE MOUTH (Business Partners)WISE  BALANCES  BASE IN (Journal Entry)   QUERY

                PEASE

                HELP ME CHANGE QUERY

                VENUBABU

                (0) 
                1. Swapnil Vichare

                  Declare @AccCode VARCHAR(100)

                  Select @AccCode= AcctCode from dbo.OACT S0 where S0.AcctCode= ‘[%0]’

                  select *

                  from

                  (select  datename(month, T0.RefDate) As Mth,T1.[Account]

                  ,SUM(T1.[Debit] – T1.[Credit]) as [Opening Balance]

                  from OJDT T0  INNER JOIN

                       JDT1 T1 ON T0.TransId = T1.TransId AND T1.[Account] = @AccCode AND T0.RefDate between ‘20130401’ and ‘20140331’ Inner Join

                       OACT T2 ON T1.Account=T2.AcctCode Left Outer Join

                       OPRJ T3 ON T2.Project=T3.PrjCode

                  Where (T3.PrjCode ='[%2]’  OR ‘[%2]’ =’ ‘)  

                  group by datename(month, T0.RefDate),T1.[Account]

                  ) PP

                  pivot( sum([Opening Balance]) for mth in ([April],[May],[June],[July],[August],[September],[October],[November],[December],[January],[February],[March])) as X

                  This Query will gives you proper report But it is important to select Proper Project Code for that selected Account.

                  There is other provision in this query, If you does not know project code for that account then leave project code field blank . 

                  (0) 
                  1. naga srinu

                    QUERY IS NOT WORKING SIR

                    5004 – IS  PURCHASE OF MILK A/CIN CHART OF ACCOUNT CODE

                    MY PURCHASE OF MILK IN BRANCHS IS THER

                    EX:  APRIAL MOUNTH

                    5004- CHART OF ACCOUNT BALANCESS IN 100000/- MAIN HEAD TOTAL

                    BRANCHS PROJECTCODE OR GROUPMAME WISE

                    GROUPNAME   PROJECT CODE  AMOUNT

                    CHITTOOR-                         CHI         50000

                    MADANAPALLI                                MAP 25000

                    GURRAMKONDA             GKD       25000

                                                                    TOTAL  100000

                    THIS QUERY IS EXECUT IN CURRANT ANSWER IN BETWEEN DAYS

                    SELECT T0.[Project],(sum(T0.Debit)-sum(T0.Credit))as ‘value’ FROM JDT1 T0 WHERE T0.Account=’5004′ and T0.RefDate>=[%0] and T0.RefDate<=[%1] GROUP BY T0.Project

                    #

                    Project Code

                    value

                    1

                    CHI

                    2,349,671.12

                    2

                    GAV

                    926,025.83

                    3

                    GKD

                    1,146,867.70

                    4

                    GPP

                    434,241.00

                    5

                    HYD

                    979,550.00

                    6

                    KLK

                    651,926.00

                    7

                    KOD

                    4,661,521.33

                    8

                    KSP

                    3,208,826.18

                    9

                    MAP

                    2,202,668.56

                    10

                    NVP

                    1,368,536.86

                    11

                    SHA

                    3,129,242.22

                    12

                    VAR

                    3,672,568.59

                      BUT MY PROBLEM IS MOUNTH WISE BALANCESS IN PIOVT TABLE QUERY IN ALL MOUNTHES IN BASE ON UPQUERY

                    VENUBABU


                    (0) 
                    1. Swapnil Vichare

                      This Query definitely solve your issue for Project wise balance of chart of account 🙂

                      Declare @AccCode VARCHAR(100) Select @AccCode= AcctCode from dbo.OACT S0 where S0.AcctCode= ‘[%0]’     select * from (select  datename(month, T0.RefDate) As Mth,T1.[Account],T1.[Project]  ,SUM(T1.[Debit] – T1.[Credit]) as [Opening Balance]     from OJDT T0  INNER JOIN      JDT1 T1 ON T0.TransId = T1.TransId AND T1.[Account] = @AccCode AND T0.RefDate between ‘20130401’ and ‘20140331’ Inner Join      OACT T2 ON T1.Account=T2.AcctCode Left Outer Join      OPRJ T3 ON T2.Project=T3.PrjCode Where (T3.PrjCode ='[%2]’  OR ‘[%2]’ =’ ‘)    group by datename(month, T0.RefDate),T1.[Account],T1.[Project]      ) PP pivot( sum([Opening Balance]) for mth in ([April],[May],[June],[July],[August],[September],[October],[November],[December],[January],[February],[March])) as X/body>

                      (0) 
              1. naga srinu

                Select  P.CardCode,P.CardName,

                [1] as [Jan],

                [2] as [Feb],

                [3] as [Mar],

                [4] as [Apr],

                [5] as [May],

                [6] as [Jun],

                [7] as [Jul],

                [8] as [Aug],

                [9] as [Sep],

                [10] as [Oct],

                [11] as [Nov],

                [12] as [Dec]

                from((select T1.CardCode ,T1.CardName,(sum(T0.Debit)-sum(T0.Credit))as ‘value’ ,month(t0.docdate) as ‘Month’

                from JDT1 T0 Inner join OCRD T1 on T1.CardCode=T0.ShortName

                inner join OCRG T2 on T1.GroupCode=T2.GroupCode

                where T0.ContraAct=’2657′ and  T2.GroupName=[%0]

                and T1.CardType=’S’ and T2.GroupType=’S’

                group by T1.CardCode,T1.CardName) as A

                Pivot((sum(T0.Debit)-sum(T0.Credit))as ‘value’ FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P

                order by T1.CardCode

                QUERY IS PROBLEM SIR

                THIS Query is execute me

                solow the vendor wise balancess report problem

                plase help me

                (0) 
                1. naga srinu

                  Select * from(select T1.CardCode ,T1.CardName,(sum(T0.Debit)-sum(T0.Credit))as ‘value’

                  from JDT1 T0 Inner join OCRD T1 on T1.CardCode=T0.ShortName

                  inner join OCRG T2 on T1.GroupCode=T2.GroupCode

                  where T0.ContraAct=’2657′ and  T2.GroupName=[%0]

                  and T1.CardType=’S’ and T2.GroupType=’S’

                  group by T1.CardCode,T1.CardName) as A

                  where A.value<>0

                  order by A.CardCode

                  this query total balances in all mount’s but  i am expect query in mounts wise balancess query

                  pales help me

                  venubabu

                  (0) 
                  1. Swapnil Vichare

                    This query will solve your issue for monthwise balance of BP, Just select Group name for it.

                    select *

                    from

                    (SELECT datename(month, T0.RefDate) As Mth,T2.CardCode,T2.CardName,SUM(T1.Credit-T1.Debit) AS [Value]  FROM

                    OJDT T0  INNER JOIN

                    JDT1 T1 ON T0.TransId = T1.TransId AND T1.ContraAct=’2657‘ Inner Join

                    OCRD T2 ON T1.ShortName=T2.CardCode AND T2.CardType=’S’ Inner Join

                    OCRG T3 ON T2.GroupCode=T3.GroupCode AND T3.GroupType=’S’

                    Where T3.GroupName ='[%0]’

                     

                    group by datename(month, T0.RefDate),T2.CardCode,T2.CardName

                    ) PP

                    pivot( sum([Value]) for mth in ([April],[May],[June],[July],[August],[September],[October],[November],[December],[January],[February],[March])) as X

                    (0) 
                    1. Swapnil Vichare

                      If you want total report for all groups then use this query and keep Group name field blank .

                      select *

                      from

                      (SELECT datename(month, T0.RefDate) As Mth,T2.CardCode,T2.CardName,T3.GroupName,SUM(T1.Credit-T1.Debit) AS [Value]  FROM

                      OJDT T0  INNER JOIN

                      JDT1 T1 ON T0.TransId = T1.TransId AND T1.ContraAct=’2657′ Inner Join

                      OCRD T2 ON T1.ShortName=T2.CardCode AND T2.CardType=’S’ Inner Join

                      OCRG T3 ON T2.GroupCode=T3.GroupCode AND T3.GroupType=’S’

                      Where (T3.GroupName ='[%0]’ OR ‘[%0]’=’ ‘)

                       

                      group by datename(month, T0.RefDate),T2.CardCode,T2.CardName,T3.GroupName

                      ) PP

                      pivot( sum([Value]) for mth in ([April],[May],[June],[July],[August],[September],[October],[November],[December],[January],[February],[March])) as X

                      (0) 
                      1. Swapnil Vichare

                        i think your both problems r solved, if u have any issues then tell me about that.

                        •    1)  This Query definitely solve your issue for Project wise balance of chart of account 🙂

                        Declare @AccCode VARCHAR(100)

                        Select @AccCode= AcctCode from dbo.OACT S0 where S0.AcctCode= ‘[%0]’

                        select *

                        from

                        (select  datename(month, T0.RefDate) As Mth,T1.[Account],T1.[Project]

                        ,SUM(T1.[Debit] – T1.[Credit]) as [Opening Balance]

                        from OJDT T0  INNER JOIN

                             JDT1 T1 ON T0.TransId = T1.TransId AND T1.[Account] = @AccCode AND T0.RefDate between ‘20130401’ and ‘20140331’ Inner Join

                             OACT T2 ON T1.Account=T2.AcctCode Left Outer Join

                             OPRJ T3 ON T2.Project=T3.PrjCode

                        Where (T3.PrjCode ='[%2]’  OR ‘[%2]’ =’ ‘)  

                        group by datename(month, T0.RefDate),T1.[Account],T1.[Project]

                        ) PP

                        pivot( sum([Opening Balance]) for mth in ([April],[May],[June],[July],[August],[September],[October],[November],[December],[January],[February],[March])) as X

                        • 2)          This query will solve your issue for monthwise balance of BP, Just select Group name for     it.

                        If you want total report for all groups then use this query and keep Group name field blank .

                        select *

                        from

                        (SELECT datename(month, T0.RefDate) As Mth,T2.CardCode,T2.CardName,T3.GroupName,SUM(T1.Credit-T1.Debit) AS [Value]  FROM

                        OJDT T0  INNER JOIN

                        JDT1 T1 ON T0.TransId = T1.TransId AND T1.ContraAct=’2657′ Inner Join

                        OCRD T2 ON T1.ShortName=T2.CardCode AND T2.CardType=’S’ Inner Join

                        OCRG T3 ON T2.GroupCode=T3.GroupCode AND T3.GroupType=’S’

                        Where (T3.GroupName ='[%0]’ OR ‘[%0]’=’ ‘)

                        group by datename(month, T0.RefDate),T2.CardCode,T2.CardName,T3.GroupName

                        ) PP

                        pivot( sum([Value]) for mth in ([April],[May],[June],[July],[August],[September],[October],[November],[December],[January],[February],[March])) as X


                        (0) 
                      1. naga srinu

                        dear

                        sir

                        my name is venubabu

                        my qualification is m,com

                        i am accountant in jyothi dairy pvt ltd Hyderabad

                        4 years  in working in sap business one software in accounting purpose my job

                        i am change this job software side (implantation side sap business one)

                        basic qualification tell me in implantation side sap business one

                        what are you course training tell me sir 

                        pales your suggestion tell me    

                        venubabu    

                        (0) 
  2. naga srinu

    dear sir

    help me

    query manager in queries is execute report  BUT EXECUTE direct in FIXED REPORT (PLD,EXCEL)  PLASE TELL ME

    VENUBABU

    (0) 
      1. naga srinu

        SIR

        MY problem is queries is setting in execute to excel format or any stranded report is pose-bull

        tell me in process

        venubabu            

        (0) 
        1. Swapnil Vichare

          I don’t exactly understand your problem. 😕

          I think you required to Export the output of your report in Excel format.

          Run your Query in Query manager and click on Excel icon in tool bar to Export your report in Excel Format.

          (0) 
  3. naga srinu

    DEAR

    sir

    please help in Business Partner Master Data query report

    BP CODE ,BP NAME ,OFFSET ACCOUNT  5657, 4006 ,1607 ,1662, 1686 BALANCES

    EX:- SELECTED BETWEEN DAYS 01.04.13 TO 15.04.13

    CS001-VENUBABU-                                   5000-  600-  8000-  4000- 1500

    IN BETWEEN DAY

    HELP ME

    VENUBABU

    (0) 
  4. naga srinu

    dear sir plase help me

    Select * from(select T1.CardCode ,T1.CardName,(sum(T0.Debit)-sum(T0.Credit))as ‘purchase’,

    (sum(T0.Debit)-sum(T0.Credit))as ‘advance’

    From  JDT1 T0 Inner join OCRD T1 on T1.CardCode=T0.ShortName

    inner join OCRG T2 on T1.GroupCode=T2.GroupCode

    (where T0.Account=’2511′ as ‘purchase’ and where T0.Account=’1857′ as ‘advance’),

    where T2.GroupName=[%0] and ((T0.RefDate>=[%1] and T0.RefDate<=[%2]) or (T0.RefDate<[%1]))

    and T1.CardType=’S’ and T2.GroupType=’S’

    group by T1.CardCode,T1.CardName

    order by A.CardCode

    this report not excute sir

    my problem vendor chart of accountent balaancess in bussiness partener master data  parchase ac 2511 and advance  ac 1857 two account balancess is there query is excute  2511 balancess and 1857 balancess

    ex:-total bp balance 15000/- 2511 ac -10000/-and 1857 ac  +25000/-

    plase help me this report

    (0) 

Leave a Reply