Stock Status from SQL Query

Yahoo Archive
1
Thanks John.
I think I will have to do this going forward because it looks like there might not be a way around the issues I’m having.


[cid:bb5307.png@ccdd2af8.4684a6cd]


Joe Rojas
Director of Information Technology
(781) 408-9278 Mobile
(781) 573-0291 Local
(781) 232-5191 Fax


[http://matsinc.com/images/e-mail-signatures/tagline.jpg%5d
[http://matsinc.com/images/e-mail-signatures/graphics/logo_matsinc.png%5d<http://matsinc.com> [http://matsinc.com/images/e-mail-signatures/graphics/icon_twitter.png%5d <http://twitter.com/Mats_Inc> [http://matsinc.com/images/e-mail-signatures/graphics/icon_facebook.png%5d <http://www.facebook.com/commercial.flooring> [http://matsinc.com/images/e-mail-signatures/graphics/icon_youtube.png%5d <http://www.youtube.com/user/MatsIncTV> [http://matsinc.com/images/e-mail-signatures/graphics/icon_pinterest.png%5d <http://www.pinterest.com/contractfloors/> [http://matsinc.com/images/e-mail-signatures/graphics/icon_blog.png%5d <http://matsinc.com/blog>

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Tuesday, December 15, 2015 11:46 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Stock Status from SQL Query



Joe,

We ran a stored procedure on a weekly basis to calculate inventory levels and costs and write it into a new DB. This wasn't used for financial reporting but just for tracking inventory over periods of time.

John



[Non-text portions of this message have been removed]
2
Hi All,

One of the issues with the Stock Status Report is that it cannot give you the cost of inventory for a historical date.

I’ve been working on a SQL query that will attempt to overcome this be using PartTran records to calculate on hand quantities and inventory value.
Has anyone else attempted this and was successful?

I’ve made a lot of progress and I’m now in the process of digging deeper to find out why some of my numbers don’t tie out and I think I’m hitting some road blocks that I may not be able to overcome.
All of our part costing methods are either FIFO or Lot FIFO, which has created a whole level of complexity for my query.


[cid:4c330d.png@e416c407.42a4ee31]


Joe Rojas
Director of Information Technology
(781) 408-9278 Mobile
(781) 573-0291 Local
(781) 232-5191 Fax


[http://matsinc.com/images/e-mail-signatures/tagline.jpg%5d
[http://matsinc.com/images/e-mail-signatures/graphics/logo_matsinc.png%5d<http://matsinc.com> [http://matsinc.com/images/e-mail-signatures/graphics/icon_twitter.png%5d <http://twitter.com/Mats_Inc> [http://matsinc.com/images/e-mail-signatures/graphics/icon_facebook.png%5d <http://www.facebook.com/commercial.flooring> [http://matsinc.com/images/e-mail-signatures/graphics/icon_youtube.png%5d <http://www.youtube.com/user/MatsIncTV> [http://matsinc.com/images/e-mail-signatures/graphics/icon_pinterest.png%5d <http://www.pinterest.com/contractfloors/> [http://matsinc.com/images/e-mail-signatures/graphics/icon_blog.png%5d <http://matsinc.com/blog>



[Non-text portions of this message have been removed]
3

Mr. Joe: This may be a little convoluted - but I created a BAQ based on the part master and joined the Warehouses and Bins tables and the PartCost tables to give me a list of All parts in the part master and the On-Hand Quantities and costing rates (many average parts) for all. 


I also included a date and time fields to the BAQ.


I then added the BAQ to the BAQ Export utility and scheduled it to create a CSV file every night at 11:45pm.


I then created a BATCH file on the server to move the CSV file out of the default location and into a network folder.  The batch file renames the CSV and appends the Date to the file name.  It also adds the CSV file to a master file that contains all previous files.


Because you have the Date and Time for each capture you can either open each file individually or open the master file and filter by date and/or time.


As I said it is a bit convoluted - but seems to work.


DaveO


4
Joe,

We ran a stored procedure on a weekly basis to calculate inventory levels and costs and write it into a new DB. This wasn't used for financial reporting but just for tracking inventory over periods of time.

John
5
Thanks Dave.
I may find that I need to do this going forward.
The more I stare at my costing issues in my query, the more I’m convincing myself this cannot be done.


[cid:3787bd.png@e6df646c.47a3a0ff]


Joe Rojas
Director of Information Technology
(781) 408-9278 Mobile
(781) 573-0291 Local
(781) 232-5191 Fax


[http://matsinc.com/images/e-mail-signatures/tagline.jpg%5d
[http://matsinc.com/images/e-mail-signatures/graphics/logo_matsinc.png%5d<http://matsinc.com> [http://matsinc.com/images/e-mail-signatures/graphics/icon_twitter.png%5d <http://twitter.com/Mats_Inc> [http://matsinc.com/images/e-mail-signatures/graphics/icon_facebook.png%5d <http://www.facebook.com/commercial.flooring> [http://matsinc.com/images/e-mail-signatures/graphics/icon_youtube.png%5d <http://www.youtube.com/user/MatsIncTV> [http://matsinc.com/images/e-mail-signatures/graphics/icon_pinterest.png%5d <http://www.pinterest.com/contractfloors/> [http://matsinc.com/images/e-mail-signatures/graphics/icon_blog.png%5d <http://matsinc.com/blog>

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Tuesday, December 15, 2015 11:44 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Stock Status from SQL Query



Mr. Joe: This may be a little convoluted - but I created a BAQ based on the part master and joined the Warehouses and Bins tables and the PartCost tables to give me a list of All parts in the part master and the On-Hand Quantities and costing rates (many average parts) for all.



I also included a date and time fields to the BAQ.



I then added the BAQ to the BAQ Export utility and scheduled it to create a CSV file every night at 11:45pm.



I then created a BATCH file on the server to move the CSV file out of the default location and into a network folder. The batch file renames the CSV and appends the Date to the file name. It also adds the CSV file to a master file that contains all previous files.



Because you have the Date and Time for each capture you can either open each file individually or open the master file and filter by date and/or time.



As I said it is a bit convoluted - but seems to work.



DaveO





[Non-text portions of this message have been removed]