cancel
Showing results for 
Search instead for 
Did you mean: 

Inventory Aging Report

Former Member
0 Kudos

Hi Experts,

Is there possible to amend query below (found from others post) to include posting date as parameter instead of warehouse?

For example, I have to generate inventory aging report from 01/07/12 to 30/06/13 (for financial period).

select b.code, b.name, b.Wh, b.Bal, b.Val,

isnull(case when b.days <30 then b.bal end,0)'0-30 Days' ,

isnull(case when b.days between 30 and 60 then b.bal end,0) '30-60 Days',

isnull(case when b.days between 60 and 90 then b.bal end,0) '60-90 Days',

isnull(case when b.days between 90 and 120 then b.bal end,0) '90-120 Days',

isnull(case when b.days between 120 and 150 then b.bal end,0) '120-150 Days',

isnull(case when b.days between 150 and 180 then b.bal end,0) '150-180 Days',

isnull(case when b.days >180 then b.bal end,0) 'Above 180 Days'

from (

select a.code,a.name,a.wh,a.bal,a.val,datediff(dd,dt,getdate())'days'

from (

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

max(t0.Warehouse)'Wh',

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

from oinm t0 inner join oitm t1 on t0.itemcode=t1.itemcode

where t0.warehouse='[%1]'

group by t0.itemcode

)a

)b order by code

Is there any others solution or query to fulfill my requirement? Kindly advise.

Thanks You.

Mun


Accepted Solutions (1)

Accepted Solutions (1)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Yes possible. Try this :

select b.code, b.name, b.Wh, b.Bal, b.Val,

isnull(case when b.days <30 then b.bal end,0)'0-30 Days' ,

isnull(case when b.days between 30 and 60 then b.bal end,0) '30-60 Days',

isnull(case when b.days between 60 and 90 then b.bal end,0) '60-90 Days',

isnull(case when b.days between 90 and 120 then b.bal end,0) '90-120 Days',

isnull(case when b.days between 120 and 150 then b.bal end,0) '120-150 Days',

isnull(case when b.days between 150 and 180 then b.bal end,0) '150-180 Days',

isnull(case when b.days >180 then b.bal end,0) 'Above 180 Days'

from (

select a.code,a.name,a.wh,a.bal,a.val,datediff(dd,dt,getdate())'days'

from (

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

max(t0.Warehouse)'Wh',

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

from oinm t0 inner join oitm t1 on t0.itemcode=t1.itemcode

where t0.warehouse='[%1]' and T0.[DocDate] between [%2] and [%3]

group by t0.itemcode

)a

)b order by code

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Dear Nagarajan,

I facing another problem is the total quantity add up from the aging columns (0-30 days, 30-60 days, 60-90 days, 90-120 days, 120-150 days, 150-180 days, above 180 days) was not tally with the grand total qty on hand (Balance quantity).

Please give me some suggestion.

Thanks You.

Mun

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

You may try with following queries:

http://scn.sap.com/docs/DOC-47132

http://scn.sap.com/docs/DOC-43946

Hope helpful.

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Dear Nagarajan,

Thanks for reply, but above two link is not meet me requirement.

I need to show inventory aging  for 0-30 days, 30-60 days, 60-90 days, 90-120 days, 120-150 days, 150-180 days, above 180 days on date of financial year end.

Do it have any other suggestion to solve it?

Thanks you.

Mun

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

If you want on-hand quantity, you can't choose document date in above.

Try this:

select b.code, b.name, b.Wh, b.Bal, b.Val,

isnull(case when b.days <30 then b.bal end,0)'0-30 Days' ,

isnull(case when b.days between 30 and 60 then b.bal end,0) '30-60 Days',

isnull(case when b.days between 60 and 90 then b.bal end,0) '60-90 Days',

isnull(case when b.days between 90 and 120 then b.bal end,0) '90-120 Days',

isnull(case when b.days between 120 and 150 then b.bal end,0) '120-150 Days',

isnull(case when b.days between 150 and 180 then b.bal end,0) '150-180 Days',

isnull(case when b.days >180 then b.bal end,0) 'Above 180 Days'

from (

select a.code,a.name,a.wh,a.bal,a.val,datediff(dd,dt,getdate())'days'

from (

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

max(t0.Warehouse)'Wh',

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

from oinm t0 inner join oitm t1 on t0.itemcode=t1.itemcode

where t0.warehouse='[%1]'

group by t0.itemcode

)a

)b order by code

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Dear Nagarajan,

Above query is showing on hand quantity as at current date.

However, I want to know on-hand quantity as at financial year end (30/06/2013) which have to include details for for 0-30 days, 30-60 days, 60-90 days, 90-120 days, 120-150 days, 150-180 days, above 180 days.

Is it just need to modify query above or have to create new query to meet my requirement?

Thanks you.

Mun

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try this query:

select b.code, b.name, b.Wh, b.Bal, b.Val,

isnull(case when b.days <30 then b.bal end,0)'0-30 Days' ,

isnull(case when b.days between 30 and 60 then b.bal end,0) '30-60 Days',

isnull(case when b.days between 60 and 90 then b.bal end,0) '60-90 Days',

isnull(case when b.days between 90 and 120 then b.bal end,0) '90-120 Days',

isnull(case when b.days between 120 and 150 then b.bal end,0) '120-150 Days',

isnull(case when b.days between 150 and 180 then b.bal end,0) '150-180 Days',

isnull(case when b.days >180 then b.bal end,0) 'Above 180 Days'

from (

select a.code,a.name,a.wh,a.bal,a.val,datediff(dd,dt,getdate())'days'

from (

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

max(t0.Warehouse)'Wh',

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

from oinm t0 inner join oitm t1 on t0.itemcode=t1.itemcode

where t0.warehouse='[%1]' and T0.[DocDate] between [%2]

group by t0.itemcode

)a

)b order by code

Note: T0.[DocDate] between [%2] should be 30/06/2013

If does not meet your requirement, please close this thread, open new thread with same requirement.

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Dear Nagarajan,

Thanks for the advise.

Mun

former_member217682
Participant
0 Kudos

Hi Nagarajan,

I'm trying to understand the query above.

What is the Val column btw?

Thank you.

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Val---- Transaction value from OITM table

Thanks & Regards,

Nagarajan

former_member217682
Participant
0 Kudos

So for example :

Bal : 300

Val : 315

30-60 days - 300

What does this mean ?

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Please refer an example:

Item AAA  In-stock quantity 3, price 100 there will be posting between 01-01-2014 to 13-01-2014.

If you run above query, the result will be:

Bal : 3 ( Balance quantity as per selected date)

Val : 3 x 100 = 300 ( Value of the item)

0-30 days : 3--- means the above quantity is stock for 0-30 days.

In short, for a given date range, you are calculating balance stock, its values and duration of stock.

Hope you understand.

Thanks & Regards,

Nagarajan

former_member217682
Participant
0 Kudos

Hi Nagarajan,

Thank you so much for your explanation.

I have a very clear picture now.

My question is,

Instead of 30 days, 60 days, 90 days etc,

How do i change it to <2days >3 days > 5days?

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

The dates in datediff function finds the days.

For example,

Your posting date 04-01-2014, getdate() nothing but today's day. As per query formula:

datediff(dd, 04-01-2014,06-01-2014) will return 2 days.

Thanks & Regards,

Nagarajan

former_member217682
Participant
0 Kudos

Hi,

I've added something like this.

select b.code, b.name, b.Wh, b.Bal, b.Val,

isnull(case when b.days <2 then b.bal end,0)'<2 Days' ,

isnull(case when b.days >4 then b.bal end,0)'>4 Days' ,

isnull(case when b.days <30 then b.bal end,0)'0-30 Days' ,

isnull(case when b.days between 30 and 60 then b.bal end,0) '30-60 Days',

isnull(case when b.days between 60 and 90 then b.bal end,0) '60-90 Days',

isnull(case when b.days between 90 and 120 then b.bal end,0) '90-120 Days',

isnull(case when b.days between 120 and 150 then b.bal end,0) '120-150 Days',

isnull(case when b.days between 150 and 180 then b.bal end,0) '150-180 Days',

isnull(case when b.days >180 then b.bal end,0) 'Above 180 Days'

from (

select a.code,a.name,a.wh,a.bal,a.val,datediff(dd,dt,getdate())'days'

from (

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

max(t0.Warehouse)'Wh',

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

from oinm t0 inner join oitm t1 on t0.itemcode=t1.itemcode

where t0.warehouse='[%1]'

group by t0.itemcode

)a

)b order by code

didnt work. did i did something wrong?

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try this, working for me:

select b.code, b.name, b.Wh, b.Bal, b.Val,

isnull(case when b.days <2 then b.bal end,0)'<2 Days' ,

isnull(case when b.days >4 then b.bal end,0)'>4 Days' ,

isnull(case when b.days <30 then b.bal end,0)'0-30 Days' ,

isnull(case when b.days between 30 and 60 then b.bal end,0) '30-60 Days',

isnull(case when b.days between 60 and 90 then b.bal end,0) '60-90 Days',

isnull(case when b.days between 90 and 120 then b.bal end,0) '90-120 Days',

isnull(case when b.days between 120 and 150 then b.bal end,0) '120-150 Days',

isnull(case when b.days between 150 and 180 then b.bal end,0) '150-180 Days',

isnull(case when b.days >180 then b.bal end,0) 'Above 180 Days'

from (

select a.code,a.name,a.wh,a.bal,a.val,datediff(dd,dt,getdate())'days'

from (

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

max(t0.Warehouse)'Wh',

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

from oinm t0 inner join oitm t1 on t0.itemcode=t1.itemcode

where t0.warehouse='[%1]' and T0.[DocDate] between [%2] and [%3]

group by t0.itemcode

)a

)b order by code

Answers (1)

Answers (1)

former_member212148
Participant
0 Kudos

Hi LH,

Here I am giving logic as I have used in customer Customer aging report.

  SELECTION-SCREEN BEGIN OF LINE.
     SELECTION-SCREEN COMMENT 1(10) TEXT-001.
       PARAMETERS : P_NOD1(4) TYPE N DEFAULT 0,
                    P_NOD2(4) TYPE N DEFAULT 15,
                    P_NOD3(4) TYPE N DEFAULT 30,
                    P_NOD4(4) TYPE N DEFAULT 45,
                    P_NOD5(4) TYPE N DEFAULT 60,
                    P_NOD6(4) TYPE N DEFAULT 90,
                    P_NOD7(4) TYPE N DEFAULT 120,
                    P_NOD8(4) TYPE N DEFAULT 180,
                    P_NOD9(4) TYPE N DEFAULT 9999.
       SELECTION-SCREEN END OF LINE.

DATA:        P_AMT1 LIKE BSID-DMBTR,
       P_AMT2 LIKE BSID-DMBTR,
       P_AMT3 LIKE BSID-DMBTR,
       P_AMT4 LIKE BSID-DMBTR,
       P_AMT5 LIKE BSID-DMBTR,
       P_AMT6 LIKE BSID-DMBTR,
       P_AMT7 LIKE BSID-DMBTR,
       P_AMT8 LIKE BSID-DMBTR,
       P_AMT9 LIKE BSID-DMBTR.

IF V_DAYS >= P_NOD1 AND V_DAYS <= P_NOD2.
           IFINAL-P_AMT1 = IFINAL-P_AMT1 + ITAB-DMBTR.
         ELSEIF V_DAYS > P_NOD2 AND V_DAYS <= P_NOD3.
           IFINAL-P_AMT2 = IFINAL-P_AMT2 + ITAB-DMBTR.
         ELSEIF V_DAYS > P_NOD3 AND V_DAYS <= P_NOD4.
           IFINAL-P_AMT3 = IFINAL-P_AMT3 + ITAB-DMBTR.
         ELSEIF V_DAYS > P_NOD4 AND V_DAYS <= P_NOD5.
           IFINAL-P_AMT4 = IFINAL-P_AMT4 + ITAB-DMBTR.
         ELSEIF V_DAYS > P_NOD5 AND V_DAYS <= P_NOD6.
           IFINAL-P_AMT5 = IFINAL-P_AMT5 + ITAB-DMBTR.
         ELSEIF V_DAYS > P_NOD6 AND  V_DAYS <= P_NOD7.
           IFINAL-P_AMT6 = IFINAL-P_AMT6 + ITAB-DMBTR.
         ELSEIF V_DAYS > P_NOD7 AND V_DAYS <= P_NOD8.
           IFINAL-P_AMT7 = IFINAL-P_AMT7 + ITAB-DMBTR.
         ELSEIF V_DAYS > P_NOD8 AND V_DAYS <= P_NOD9.
           IFINAL-P_AMT8 = IFINAL-P_AMT8 + ITAB-DMBTR.
         ELSEIF V_DAYS > P_NOD9 AND V_DAYS < 9999. "AND V_DAYS LE P_NOD7.
           IFINAL-P_AMT9 = IFINAL-P_AMT9 + ITAB-DMBTR.
         ELSE.
           IFINAL-P_AMTERR = IFINAL-P_AMTERR + ITAB-DMBTR.

         ENDIF.