on 11-08-2013 6:48 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
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
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
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
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?
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.