Inventory Report

[J G PM]

I am still in the learning part of my Macola experience. My company relies heavily on SQL based reports in part because some of the Macola solutions are clumsy at best. My boss wants a simple history transaction report for Inventory. So far the best I can come up with is a basic inventory report

SELECT iminvtrx_sql.trx_dt, iminvtrx_sql.item_no, iminvtrx_sql.comment, iminvtrx_sql.comment_2, iminvtrx_sql.quantity, imitmidx_sql.item_desc_1, iminvtrx_sql.ord_no, iminvtrx_sql.source, iminvtrx_sql.line_no, iminvtrx_sql.Loc, imitmidx_sql.search_desc, iminvtrx_sql.from_ctl_no, iminvtrx_sql.user_name
FROM iminvtrx_sql INNER JOIN imitmidx_sql ON iminvtrx_sql.item_no = imitmidx_sql.item_no
WHERE (((iminvtrx_sql.trx_dt) > '1/1/1900') AND ((iminvtrx_sql.item_no) = @TXItem) AND ((iminvtrx_sql.Loc) Like '%' + @TXLOC + '%'))

The problem is, it does not show balances before or after the given transaction and there is no way to know if it’s a positive or negative transaction. I have to believe someone has figured this out and does not mind sharing SQL that will help me pull a descent inventory transaction report out of the back side of Macola.

 

[MFaizan]

Hi Everyone, I am looking for an option to upload new inventories quantity in Macola, currently I have to post one by one. Can anyone let me know.

Thanks

 

[J G PM]

MFaizan (MIS0
My team has an Access Database they use to make large transfers in Macola. That being said, we dont use Locations in Macola, so it may be different for you. If you want to hear more about this Access DB let me know I can either work through it with you or if you are Access friendly I can get you a copy?

 

[MFaizan]

We have two locations in system, mostly in one location there are no significant difference, but for other location I want to correct it. if you can help I would really appreciate.
One more question, I am also new to macola and our company does not have manufacturing module, I have corrected some inventories number couple of weeks before but still shows difference. How the inventories calculation happens in Macola, team says it deduct from BoM based on production orders.

 

[J G PM]

Hello, I am still getting my hands around Macola. I dont find it as user friendly as I have other WMS's I have worked with. That being said, we do what we can with what we have. I think it would be easier if you contact me at work so we can reply a bit easier and perhaps share ideas. I should warn you, I may actually try to learn stuff from you too. Email me at jgreen@deltatrak.com and lets see if we can help one another.

 

[NEmacGuy]

The problem is, it does not show balances before or after the given transaction and there is no way to know if it’s a positive or negative transaction. I have to believe someone has figured this out and does not mind sharing SQL that will help me pull a descent inventory transaction report out of the back side of Macola."
Not true - old_quantity is the quantity as it was prior to the transaction. Figuring out if the transaction is positive or negative is a function of the document type. The doc_type has a standard effect, example issues are naturally subtractive, and receipts are additive. Q's and T's are trickier along with the rest.

Uploading inventory is easier said then done, not impossible requires thought. The Inventory location is the primary table for onhand qty, but you could also have data in the bin, lot, or lifo cost tables with onhand data, not to mention there is the inventory transaction history table, bin and lot transaction tables.

I found the best way to update onhand for many items is a physical or cycle count. Another good option would be a third party application like Wisys or using the API from Macola if it talks to the Inventory.

 

[J G PM]

I honestly appreciate all the knowledge and insight on this forum. I am sure there are other software options and other applications that can make any WMS "better" or more user friendly or whatever the case may be. My task is to find a way to make Macola work for me without enlisting a new application. We have SQL and have access to Macola tables, I am just hoping someone can share a report or query that will provide the information I am looking for. As for my original post being True or not, given the SQL and query I have and am working with, it gives me what it gives me. I am not shooting for a rare or exotic thing, its an inventory Transaction report that shows balances before and after the transaction. As pointed out above, the transaction type has some affect on positive or negative. If I am asking for too much or something that is so exotic it cannot be shared then feel free to say so, suggest I get a consultant to resolve this.
Thaks for the time,
J G