Dear Baan Experts,
I would like to create a stock aging report. I did Create a new session and new report. Now i stuck in how write the script to get my on hand Qty.

Session User Key in a Aging Date - DATE
Table whltc105 - (Master Table in report) Item , Lot , WH
Table whinr100 - (to get the Inventory After Transaction) Item, Lot, WH,Transaction Date, Inventory After Transaction.

Example
Session input Aging Date as on 12/3/2011

Table whinr100
Item Lot WH Transaction Date Inventory After Transaction
A Lot1 FG 14/9/2010 1000
A Lot1 FG 10/10/2010 800
A Lot1 FG 24/2/2011 400
B Lot2 PH 5/5/2010 9000
B Lot2 PH 4/8/2010 500
B Lot2 PH 2/2/2011 450
B Lot2 PH 30/3/2011 200

Report Result:-
whltc105.Item Whltc105.lot whltc105.cwar Inv after transac
A Lot1 FG 800
B Lot2 PH 450

1) How to get the Inventory After Transaction as on date 12/3/2011?
2) How to link Inventory After Transaction result to whltc105?

I don't see a reason to link to table whltc105, table whinr100 has all the fields you might need. The problem I run into at looking at your request is that I see no way to narrow the records down based off that date. It looks like no matter what is selected you will need to do a full table scan. That is not a good idea if the table is large. Other wise you could just select the warehouse, item, qty and max transaction date.

Dear Baan Expert,
I already created session and report with table whinr100. Now i facing problem on how to get appear the latest transaction date<=Aging Date.

my report sorting is by 1=WH 2=Item

Example
Session input Aging Date as on 12/3/2011

Table whinr100
Item Lot WH Transaction Date Inventory After Transaction
A Lot1 FG 14/9/2010 1000
A Lot1 FG 10/10/2010 800
A Lot1 FG 24/2/2011 400

B Lot2 PH 5/5/2010 9000
B Lot2 PH 4/8/2010 500
B Lot2 PH 2/2/2011 450
B Lot2 PH 30/3/2011 200

D Lot5 FG 9/7/2010 400
D Lot5 FG 10/11/2010 600
D Lot5 FG 2/2/2011 700

Report Result:-
Item lot Inv after transac
WH= FG
A LOT=Lot1 Qty =800
D LOT=Lot5 Qty=700
WH=PH
B LOT=Lot2 Qty =450

1) How to get the Inventory After Transaction as on date 12/3/2011?

Try something like this:


select whinr100.item, whinr100.cwar, whiinr100.lot, max(whinr100.date):max.date
from whinr100
where whinr100.date<= :input.date
group by whinr100.item,whinr100.cwar, whiinr100.lot
selectdo
select a.qty:lot.qty
from whinr100 a
where a._index1 = {:whinr100.item, :whinr100.cwar, :whiinr100.lot,:maxdate}
as set with 1 rows
selectdo
endselect
| set to report
rprt_send()
endselect

You can take care of looking up the format for using the max function and declaring the necessary variables. But something like this should work.

Dear Mark_h,
Thank you so much for the script. It's work. now i would like to link max date transaction with table whltc100 to get the lot receipt date but my data incorrect. please help.

below is my script:

select whinr100.item, whinr100.cwar, whinr100.lot, max(whinr100.date):max.date
from whinr100
where whinr100.date<= :input.date
group by whinr100.item,whinr100.cwar, whinr100.lot
selectdo
select a.qty:lot.qty
from whinr100 a
where a._index1 = {:whinr100.item, :whinr100.cwar, :whinr100.lot,:maxdate}
as set with 1 rows
selectdo
select whltc100.* from whltc100
where whltc100.item=:whinr100.item
and whltc100.clot=:whinr100.clot
selectdo
endselect
endselect
| set to report
rprt_send()
endselect

I am not familiar with these tables so I can't really assist. All I can do is recommend that you make sure you have all the fields necessary to do the select on whltc100. I would assume just using item and lot would return multiple records. I really don't know since we do not use LN.