Inventory Report
Inventory Report
(OP)
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.
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.
RE: Inventory Report
Thanks
RE: Inventory Report
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?
RE: Inventory Report
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.
RE: Inventory Report
RE: Inventory Report
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.
RE: Inventory Report
Thaks for the time,
J G