We are trying to write a report that will use the Quantity Available Field. We are using Sage 50 US Edition and our Qty Available Field is calculated as Qty_on_Hand + Qty_on_PO - Qty_on_SO - Qty_on_WorkTickets. The first three variables of that equation are available for use in Sage Intelligence. However, I cannot find Qty on Work Tickets or Qty Available. The Available field can be used in normal Sage reports.
The only idea I have for now is to write an SQL query on the WORKTKT.DAT table and generate a list of all items on a Work Ticket with their quantities. This will be tough because the item could be at any one of the C1-C300 positions in the table. I may be able to get a query to work for this, but it seems like there may be an easier solution that I am missing.
Any help is appreciated!
!!SOLVED!! I was able to write some SQL code and combine it in with the Inventory Analysis container to make a new report that shows Qty on WT which allows me to calculate Qty Available. I've attached the code for the container code that I created.
I have created a report in the Report Manager using this container and I can run the report successfully using a @SYSDATE@ pass through variable.