Inventory SQL Script Solutions
 |
On Order Quantity Does Not Match PO’s
This can be caused by either over-receiving on a previous PO or by having a PO marked to receive into one warehouse and the receipt (or a partial receipt) received into another warehouse. On Order Qty is a calculation of the number of parts ordered on all POs throughout time, minus the number received over time in a warehouse. It is not possible to reset this quantity as this is a calculation field. However, there is a script to reset these that can be run on the front end of Sedona. The script you can run to correct this number is: //exec reset_on_order 0
Out For Repair Quantity is Incorrect:
This can be caused by creating the repair order in one warehouse and receiving the part back into another warehouse. The same script for On order Quantity is used for Out for Repair Quantity (script above). Same script as above: //exec reset_on_order 0
Transfer Pending Number is not correct:
Sometimes pending transfer number can be incorrect. If this is the case, use the same script as above: //exec reset_on_order 0
On Hand Quantity and its values are not correct:
If the on-hand quantities of parts and their values are not correct, a script can be run to correct this number: //exec reset_part_values
Committed Quantity
The Committed Quantity field totals and tracks the quantities of parts that are on jobs but not yet issued to the job where the “Stock Item” box is checked for that part on the job. The committed quantity list will only show up under the default warehouse. Use this script to correct those totals: //exec Part_Committed_Qty_Reset
Script to Fix the Error when trying to Close a Job
If you receive the following error when trying to close a job: “All parts are not properly issued or received as a Direct Expense!” You can try using the below script to correct the error. If the below script does not correct the error, then the issues and/or returns can usually be made to complete this job.
//update OE_Job_Parts set Issued_Qty = TotalIssue from oe_job_parts jp Inner join (select jpart.Job_Part_Id, Issued_Qty, SUM (jpart.quantity) as TotalIssue
from OE_Job_Issue_Part jpart inner join OE_Job_Parts Parts on parts.Job_Part_Id = jpart.Job_Part_Id group by jpart.Job_Part_Id,
Issued_Qty Having SUM (jpart.quantity) <> Issued_Qty) ref on ref.Job_Part_Id = jp.Job_Part_Id
Parts are showing on the Stock Inventory Report with 0 Count, but they have a positive or negative value
When a customer is using standard costing and has a part with zero on hand but a positive or negative value, the only way to fix it is to make a stock adjustment +1 (go up to 1) and then stock adjust down to zero. This will force the recalculation of the part, and the two adjustments should counter one another out.