Inventory Location File and Inventory Bin File out of balance Report

[lrousey]

Hi.

Does anyone know if there is a report in macola that will show only the records that are out of balance between the IMINVLOC table and the IMINVBIN table? Has anyone created a sql script or report that returns these records? I've tried to create a crystal report. I had to create a sub report and link it back to my main report (iminvbin info), but I can't select only the records where the 2 tables don't match (I get everything).

Any help would be greatly appreciated.

Happy Thanksgiving!!

Laura

 

[JayTheGeek]

Although the stock status report does now raise a flag when bin quantities are out of balance, there is no report that displays only these 'balance problems'. (at least nothing up to batch 372)

I have struggled with this issue for two years now. I am on to my third version of SQL Query & Report. Current activity focuses on getting this all into a stored procedure so it runs all at once.

My report looks at the balance between the three inventory layers: Location, Bin and Dye Lot. The query is complicated by the fact that quantities can be in error, but in our environment we can also get Bin records without the matching Dye Lot record. The reverse is true as well, where a Dye Lot record can exist without the corresponding Bin record.

I don't really have a problem sharing what I have done so far. But the fact is that I know my query does not correctly identify all of the problems.

 

[JayTheGeek]

So here is the query behind my BIN LAYER check. It should be a good starting point for you.

I would certainly be interested in hearing other people's thoughts on this issue. It is an ongoing challege.

SELECT     loc, item_no, qty_on_hand, ISNULL
                          ((SELECT     SUM(dbo.iminvbin_sql.qty_on_hand)
                              FROM         dbo.iminvbin_sql
                              WHERE     dbo.iminvbin_sql.item_no = dbo.iminvloc_sql.item_no AND dbo.iminvbin_sql.loc = dbo.iminvloc_sql.loc), 0) AS BinQty
FROM         dbo.iminvloc_sql
GROUP BY loc, item_no, qty_on_hand
HAVING      (ISNULL
                          ((SELECT     SUM(dbo.iminvbin_sql.qty_on_hand)
                              FROM         dbo.iminvbin_sql
                              WHERE     dbo.iminvbin_sql.item_no = dbo.iminvloc_sql.item_no AND dbo.iminvbin_sql.loc = dbo.iminvloc_sql.loc), 0) <> qty_on_hand)

 

[dgillz]

I have a crystal report and an event manager event that tells you when this happens, check out my website for details or ask your Macola business partner or Exact regional office.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[lrousey]

Jay,

That's exactly what I needed! Thanks so much for sharing the script w/ me.

Laura
lrousey@dnpribbons.com