Age Analysis report

[Former Member]

Hello Experts !!!!

My Client requires a Stock Age analysis report .

It is a trading company of steel .

my client wants to know that how old my stock is in warehouse or how much is 30 days old ,60 days old ,90 days old.

Below is my query

SELECT T0.ITEMCODE , T0.DSCRIPTION,T2.U_WT,SUM(T0.QUANTITY)AS QUANTITY,T1.DOCDATE,

CASE WHEN Datediff(day, T1.DOCDATE,GETDATE())<30 THEN SUM(T0.QUANTITY) END '<30 Days(Qty)',

CASE WHEN Datediff(day, T1.DOCDATE,GETDATE())<30 THEN SUM(T0.QUANTITY)*T2.U_WT END '<30 Days(MT)',

CASE WHEN Datediff(day, T1.DOCDATE,GETDATE())>30 AND Datediff(day, T1.DOCDATE,GETDATE())<60 THEN SUM(T0.QUANTITY) END '<60 Days(Qty)',

CASE WHEN Datediff(day, T1.DOCDATE,GETDATE())>30 AND Datediff(day, T1.DOCDATE,GETDATE())<60 THEN SUM(T0.QUANTITY)*T2.U_WT END '<60 Days(MT)',

CASE WHEN Datediff(day, T1.DOCDATE,GETDATE())>60 AND Datediff(day, T1.DOCDATE,GETDATE())<90 THEN SUM(T0.QUANTITY) END '<90 Days(Qty)',

CASE WHEN Datediff(day, T1.DOCDATE,GETDATE())>60 AND Datediff(day, T1.DOCDATE,GETDATE())<90 THEN SUM(T0.QUANTITY)*T2.U_WT END '<90 Days(MT)',

CASE WHEN Datediff(day, T1.DOCDATE,GETDATE())>90 AND Datediff(day, T1.DOCDATE,GETDATE())<120 THEN SUM(T0.QUANTITY) END '<120 Days(Qty)',

CASE WHEN Datediff(day, T1.DOCDATE,GETDATE())>90 AND Datediff(day, T1.DOCDATE,GETDATE())<120 THEN SUM(T0.QUANTITY) END '<120 Days(MT)'

FROM PDN1 T0 INNER JOIN OPDN T1 ON T0.DOCENTRY =T1.DOCENTRY INNER JOIN OITM T2 ON T0.ITEMCODE=T2.ITEMCODE

GROUP BY T0.ITEMCODE , T0.DSCRIPTION,T1.DOCDATE,T2.U_WT

but problem is that i am able to track when goods were arrived in warehouse .But i am enable to track how much went out warehouse.

" MY CLIENT WANTS THE REPORT GOODS RECEIPT NOTE WISE REPORT. " that how much qty has been delivered against a particular Goods receipt note. But we havn't provided any reference to delivery of goods receipt note .

Experts Thanx in advance.

Krishna Vamsi

[Former Member]

Hello Krishna Vamsi,

Try this:

SELECT T1.ITEMCODE,T1.ItemName,T1.U_WT,SUM(IsNull(T0.INQty,0)-IsNull(T0.OutQty,0)) AS QUANTITY,T0.DOCDATE,

CASE WHEN Datediff(dd, T0.DOCDATE,GETDATE())<30 THEN SUM(IsNull(T0.INQty,0)-IsNull(T0.OutQty,0)) END '<30 Days(Qty)',

CASE WHEN Datediff(dd, T0.DOCDATE,GETDATE())<30 THEN SUM(IsNull(T0.INQty,0)-IsNull(T0.OutQty,0))*T1.U_WT END '<30 Days(MT)',

CASE WHEN Datediff(dd, T0.DOCDATE,GETDATE())>30 AND Datediff(dd, T0.DOCDATE,GETDATE())<60 THEN SUM(IsNull(T0.INQty,0)-IsNull(T0.OutQty,0)) END '<60 Days(Qty)',

CASE WHEN Datediff(dd, T0.DOCDATE,GETDATE())>30 AND Datediff(dd, T0.DOCDATE,GETDATE())<60 THEN SUM(IsNull(T0.INQty,0)-IsNull(T0.OutQty,0))*T1.U_WT END '<60 Days(MT)',

CASE WHEN Datediff(dd, T0.DOCDATE,GETDATE())>60 AND Datediff(dd, T0.DOCDATE,GETDATE())<90 THEN SUM(IsNull(T0.INQty,0)-IsNull(T0.OutQty,0)) END '<90 Days(Qty)',

CASE WHEN Datediff(dd, T0.DOCDATE,GETDATE())>60 AND Datediff(dd, T0.DOCDATE,GETDATE())<90 THEN SUM(IsNull(T0.INQty,0)-IsNull(T0.OutQty,0))*T1.U_WT END '<90 Days(MT)',

CASE WHEN Datediff(dd, T0.DOCDATE,GETDATE())>90 AND Datediff(dd, T0.DOCDATE,GETDATE())<120 THEN SUM(IsNull(T0.INQty,0)-IsNull(T0.OutQty,0)) END '<120 Days(Qty)',

CASE WHEN Datediff(dd, T0.DOCDATE,GETDATE())>90 AND Datediff(dd, T0.DOCDATE,GETDATE())<120 THEN SUM(IsNull(T0.INQty,0)-IsNull(T0.OutQty,0)) END '<120 Days(MT)'

FROM dbo.OINM T0

INNER JOIN dbo.OITM T1 ON T0.ITEMCODE=T1.ITEMCODE

GROUP BY T1.ITEMCODE,T1.ItemName,T0.DOCDATE,T1.U_WT

Thanks,

Gordon

[Former Member]

Hi Gordon

Your query worked fine , But still some of my values are coming negative in qty.m unable to understand why some of my items qty are coming negative .

any suggestions please

Regards

Krishna Vamsi

[Former Member]

You may fine tone the query by adding only required transaction type in. The problem for this report is: inventory movement may not be traceable like A/R or A/P aging. Any durations may not work well no matter how hard you try.

[Former Member]

Have you considered using Batch & Serial Number concepts

Regards

Sandeep

[Former Member]

Hello Sandeep

We are giving serials numbers on release only.

So how to track .Any suggestions please let me know sir !!!

Regards

krishna vamsi

[Former Member]

Hi,

What is the valuation method you are using?

If your Valuation Method is FIFO

Then Definitely your goods should move in FIFO Basis Only,

Then you can easily achieve your task by query.

If your Valuation Method is Other Than FIFO

You Should link the GRPO Number in Delivery Doc or you sholud feed some reference.

[Former Member]

Hi sandeep

Even though using serials no how cn we link between Goods receipt note and delivery.

m querying from OSRI and SRI 1 table

any suggestions

Regards

krishna Vamsi

[Former Member]

Hello Krishna Vamsi

To achive your requirment.. I would go for the option Serial Number Managment Method 'on every transaction' i dont know how practical it is for you to go for it.

by choosing the above option you can pull the data you required

Regards

Sandeep

[Former Member]

Hello Sandeep

At present all the transactions have been done in "on release only ".

but if now we change the " Manage Item By " to "on every transaction" , SAP wont allow us to change , since transactions have been performed.

Any suggestions please let me knw.

Regards

Krishna Vamsi

[Former Member]

Thanks i got it cleared

Regards

Krishna Vamsi