Execution Timeout Expired creating works order from sales order

Sage 200 cloud Pro 12.00.0031.

After importing stock records and stock opening balances, customer created sales orders.  After creating a BOM for one of the products, customer checked the 'Create Works Order' checkbox on the sales order line item and saved the item.  After clicking 'Save' on the sales order entry screen the usual dialigue for customer order number etc is displayed, then a progress bar called 'Amending Lines' appears.  After 30 seconds the 'Sage - Unexpected Exception' message appears.  There is no way to exit from the sales order other than terminating the Sage 200 client process.

The works order seems to have been created correctly.  However, on trying to save the order again another works order is created, so clearly the order isn't getting updated with the fact that a WO exists.

Has anyone seen this before?  I've tried getting a SQL profile and I've repeated the steps from that in SSMS and all works without error (in a dummy company set up for the purpose).  If I run sp_who2 while the 'amending lines' progress bar is displayed I can see there is one SPID which is blocked, but the blocking SPID is sleeping, awaiting cmd and seems to be a SELECT.

Any ideas how to troubleshoot this greatly appreciated.

  • 0
    and seems to be a SELECT.

    It's the blocked SPID which is a SELECT, not the blocking SPID.  It has a status of 'SUSPENDED'

  • 0 in reply to Paul C

    Is there any bespoke? If so does the same happen if you load up the application without it?
    It wasn't clear so apologies but does the same happen for a built item which has been manually created in Sage 200 using manually created stock items rather than imported?
    Same issue on the server?

  • 0 in reply to Stu Mc

    No there is no bespoke.  After I posted my question I did try manually creating stock items with the same result.  I am about to try the same but after restoring the database back to pre-stock import.  The issue is on all workstations and on the server.

  • 0 in reply to Paul C

    The problem seems to have started when I ran the year end. The customer wanted some historical nominal data for comparative reporting so I set up the new company in the previous year, imported monthly account summary N/L journals for the year and the closing balance sheet then ran the year end to bring forward the opening balances for the current year. I took backups immediately before and after running the year end. There were no stock records in the system at that point – but there were product groups and UOMs.

    If I restore the pre-year end backup, then create stock items (manually – not imported), a BOM and a sales order, tick the ‘create works order’ checkbox and save the order it all works fine – a works order is created and there is no timeout error.

    If I restore the post-year end backup and do exactly the same, I get the timeout error when saving the sales order.

    I feel I may have missed something when I processed the year end.  Can't think what it could be though.

  • 0 in reply to Paul C
    No there is no bespoke. 

    Actually that's not strictly true.  'Eureka Addons Base Pack for Sage 200c' is installed.

  • 0 in reply to Paul C

    If you add to the mix performing the Year End again does it happen?

  • 0 in reply to Stu Mc

    I'll try it and see.

  • 0 in reply to Paul C

    So, it appears that the year end thing is a red herring.  When I restored the database again and re-tested I got the timeout error when creating a works order from the sales order.  However, since I had seen it work once I spent the rest of the day testing various scenarios. 

    One of the product groups has been set up with multiple units of measure with a precision of 1.00000 on the stock UOM.  If I create a stock item and leave the precision set to 1.00000, the timeout error occurs.  But if I create a stock item and change the precision to 0.00001, or set the product group precision to 0.00001 before creating the stock item, then the timeout error doesn't occur.  I've tested this several times and it is reproducible (on this dataset - not tried others).  The really weird thing is that after successfully creating a works order from a sales order I can change the precision to 1.00000 on the stock item and subsequently create sales orders with it and works orders without issue.

    Unfortunately, changing the precision from 1.00000 to 0.00001 on an existing stock item doesn't fix the problem.

    I would be grateful for any ideas.

  • 0 in reply to Paul C

    This isn't something I've come across before as you can imagine!

    If you haven't done so already perhaps start with a new Unit of measure(s), then a new product group and then a new stock item (Of course ensure Eureka is not loaded up when doing so).

  • 0 in reply to Paul C

    I've had a quick look at this - as far as I can see this will only happen under very specific circumstances.

    I can only replicate the issue if the BOM that I create a SOP line for has:

    a) At least one component line where the UOM is not the component stock item's base unit.
    b) At least one component (not necessarily the same one as above, but it could be) which is set to 'OnlyForWOCosting = true' in the ComComponents table (i.e. 'Bulk Issue' is set to true in the Stock Item record for the component).

    This is the sequence which causes the error:

    1) The SOP order is saved. This happens inside a SQL transaction, which is not committed by the time we get to...
    2) ...the SOP manufacturing coordinator is called on to create a Works Order via the pr_WoCreateNewWo stored procedure.
    3) An object - WorksOrderUOMHelper then looks at each line in WorksOrderAllocIssues for the created WO; each line is run through the Stock UOM converter. Any lines which need to be altered - those being any lines where the UOM <> Base Unit as discussed above - are amended and updated. This is where part of the problem begins to creep in, as these updates are inside their own SQL transactions. Because these transactions are inside the ambient uncommitted transaction which began in step 1, they cannot commit until the ambient transaction commits.
    4) Finally, the SOP manufacturing coordinator issues a single SQL command - to update the WO and set the Precision to whatever value it's calculated from the above steps.
    5) In in ideal world (which it isn't) the process finishes and the outer SQL transaction commits.

    The remaining part of the problem is the fact that there is a trigger on the WorksOrders table, called tr_WoUpdateBulkIssueItems. Exactly what this does isn't really important to this discussion; suffice to say that if any lines in WorksOrderAllocIssues for the inserted or updated WO record are set to UpdateStock=0 (and if condition (b) above is met then there will be) the trigger will try to issue an update to the WorksorderAllocIssues table. You might already have worked out the problem by now: In step (3) above we ended up with uncommitted transactions on WorksOrderAllocIssues. This trigger runs immediately after the update to the WO table is isssued in step (4). This means you’re trying to update a table that you've already got an uncommitted update transaction open on and you deadlock yourself.

    Short answer – amend the component lines of the offending BOMs and express the quantities in the base units.

    Definitely a bug, this one.

  • 0 in reply to Chris Burke

    Thanks Chris.

    The BOMs I have been creating to troubleshoot this are very simple - just one component and no operations.  The component is always just a standard component, not bulk issue.  The UOM is different to the stock item's base UOM.  The timeout error occurs if the precision on the stock UOM is set to 1.00000.  The precision on the base UOM is set to 0.00001, the default.  If I set them the same then there are advisory messages suggesting that I change one of them.  The stock UOM was set to 1.00000 because the customer deals only in integer quantities of these items.

    I think we're going to bite the bullet and restore back to before the stock file import, set the product group UOM precision values to 0.00001 and then re-import the stock file.  I've tested this and it will work.  The customer will lose a few days work done over the w/end and this week.

  • 0 in reply to Paul C

    Well if you're sure that no BOMs are bulk issue, try this first - it might save you (and you customer) a lot of time.

    Edit the tr_WoUpdateBulkIssueItems trigger on the WorksOrders table. 

    Find the following line:

    SET @RecCount = (SELECT COUNT(*) FROM WorksOrderAllocIssues WHERE (UpdateStock=0) AND (HeaderID IN (SELECT ID FROM Inserted)))

    ..and amend it to:

    SET @RecCount = 0

     

    See what that does.

  • 0 in reply to Chris Burke

    Well, my experimental BOMs didn't have bulk issue components but that may not be the case in production.  I'll try it in my test dataset.  Thanks.