Inventory Aging Report

kareng

Member
I've been asked to produce an inventory aging report. I can't find any such canned report. Is anyone aware of anything similar, or have you programmatically created this type of report?
 
It's not an easy report to produce but it can be done - no there isn't a canned version.

The F41021 file is a summary of the On-Hand Quantity by Business Unit, Item (Short only) and Location (and Lot if you are also using that).

You need to read F41021 for items that have an On Hand not equal to zero, then link to the Cardex (sorted by Transaction Date Descending) and age the inventory for that item by reading the receipts for that Item / Location / Lot.

For example, if you have 100 units on hand, 40 received in October, 50 received in September, and 150 received in August (simplified example), then you would allocate 40 to the October column, 50 to the September column, and the remaining 10 to the August column.

I think that should work - you will need to figure out the next way to extract and sort the Cardex to link to the data.

Given that some inventory might be really old, you probably don't want to start with the Cardex. The F41021 doesn't have the LITM field, so you may want to either start with the F4102 or a join of F4101 (or 2) with F41021. That will allow you a good choice of DW data selection and sorting.

Analyse the Cardex, maybe using an SQL within your RPG for each Item/Location/Lot - not sure whether that will be faster doing a gazillion short SQL's rather than a humongous extract or creating a logical over the Cardex.

Well that should give you some ideas - now you just need someone who can program it............... Hope this helps...
 
Karen,

Have you looked at the P034201 DW version and report? I am not sure if
it has what you are looking for or not.
Contact me directly and I will send you an example of what the report
looks like for us.

Jim Rubino
Senior Programmer Analyst
FIKE CORPORATION (r)
704 South 10th Street
Blue Springs, Mo. 64015
* Direct Dial - (816) 655-4579
* Or - (816) 229-6216 Ext. 1079
* [email protected] <mailto:[email protected]>
 
Karen,

Actually this report only shows customers aging.

Can you be a little more specific on what this report would look like?
We have a few Showcase queries that show some aging and movement for
Inventory.
We also have changed the Planners or Buyers Guide program to show some
online usage over a 24 month periods with averages. I can send you a
screen capture of what our screen looks like

Jim Rubino
Senior Programmer Analyst
FIKE CORPORATION (r)
704 South 10th Street
Blue Springs, Mo. 64015
* Direct Dial - (816) 655-4579
* Or - (816) 229-6216 Ext. 1079
* [email protected] <mailto:[email protected]>
 
Thanks for the feedback. I was hoping that there was an easier solution, but do appreciate you taking the time to provide me the logic to handle this with a custom program.
 
Re: RE: Inventory Aging Report

Jim- I have a new VP of operations who is asking for this report in order to identify those items that have been sitting in inventory for extended periods of time. I haven't found any canned JDE reports that provide this data, but am wondering how other JDE customer analyze their inventory to determine if there are products that need to be moved. Thanks. Karen
 
This isn't a canned report, in fact the source database/app is from E1 rather than World, but here's another approach to looking at aging (see attachment)
 

Attachments

  • 114170-AGED INVENTORY POSITIONS.pdf
    177.8 KB · Views: 573
Re: RE: Inventory Aging Report

I don't know if this is the same thing you are looking for but is sounds similar to what some of our users refer to as a "dead stock report". There is nothing that JDE has to determine this so I wrote a custom pgm to do this. They provide a "not used since" date as the input and my pgm runs over the F4111 to look for items that don't have tranactions since that date. I wrote it such that it is just like standard JDE DreamWriter type reports but if you don't have a programmer to do such things, it might be possible that 3rd party query tools could do something similar?

kareng <[email protected]> wrote: Jim- I have a new VP of operations who is asking for this report in order to identify those items that have been sitting in inventory for extended periods of time. I haven't found any canned JDE reports that provide this data, but am wondering how other JDE customer analyze their inventory to determine if there are products that need to be moved. Thanks. Karen
 
Oracle SQL

Mind to change the where and ands to your needs...

SELECT LITM"Artikel",OMS"Omschrijving",VRIJEVOORRAAD"Vrije Voorraad",
MAX(DECODE(PLANK,'0-30','V','-')) "0-30",
MAX(DECODE(PLANK,'30-60','V','-')) "30-60",
MAX(DECODE(PLANK,'60-120','V','-')) "60-120",
MAX(DECODE(PLANK,'120-180','V','-')) "120-180",
MAX(DECODE(PLANK,'>180','V','-')) ">180"
FROM
(
SELECT /*+RULE */
DISTINCT
IL.ILLITM LITM,
IM.IMDSC1||IM.IMDSC2 OMS,
decode(trunc((sysdate-JDETO_DATE(IL.ILTRDJ))/30),0,'0-30',1,'30-60',2,'60-120',3,'120-180',4,'120-180',5,'120-180','>180') PLANK,
vv.vrijevoorraad VRIJEVOORRAAD
FROM F4111 IL,
F4101 IM,
(
SELECT LI.LIITM ARTIKEL,
SUM(LI.LIPQOH - (LI.LIHCOM + LI.LIPCOM)) VRIJEVOORRAAD
FROM F41021 LI
WHERE LI.LIMCU = ' SN-2'
GROUP BY LI.LIITM
HAVING SUM(LIPQOH - (LIHCOM + LIPCOM)) >0
) VV
WHERE VV.ARTIKEL = IL.ILITM AND
IL.ILITM = IM.IMITM AND
IM.IMLITM NOT LIKE '%-V %' AND
IM.IMSRP2 <> '321' AND
IL.ILDCT IN ('RI', 'IT', 'SO')
)
GROUP BY LITM,OMS,VRIJEVOORRAAD;
 
[ QUOTE ]
This isn't a canned report, in fact the source database/app is from E1 rather than World, but here's another approach to looking at aging (see attachment)

[/ QUOTE ]

Larry,
The output report is great looking. Could you tell me how you created it and what app or report writer you used.

Dan
 
You can create a simple report , if your AS of file F41112 is populated, you can extract the list of item from F41021 whose on hand is not zero and check those item on F41112 for past months net quantity field . For example if you need a aging report for last 3 months ..you have to check if the last 3 month net quantity field Equals to Zero where as the F41021 On hand should not be zero.

Thanks,
Bhuvanendar
 
Back
Top