tI_timInvtTran - Cannot INSERT timInvtTran because cost tier effect record is missing.

Question

Error happens when posting/saving a MF>Labor Entry with Cmplt?=Y. 
 I cannot find a timCostTier and timInvtTranCost records. 
 After this error, another error occurs: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0. 
 Anyone have any ideas how to resolve this issue?

Contefication · Accepted Answer

You didn't specify which version your are working with, nor the circumstances, so assuming this worked previously, you will need to narrow the scenario down to specific items, routings, etc. You should review any changes made to settings, item attributes, related data or code potentially made directly in the back-end as well. If you know how to create and review a SQL trace of the code execution, you should record the query activity. Many times these are logic failures, but you might just capture, or at least narrow down the cause.

Tad Bright · Answer

Thanks for replying and the advice! I really appreciate it. We are a version behind, 2021 update 1 (8.10). I am new to my company, to the community, and to the manufacturing module of Sage 500 ERP. I hope to learn all of the above quickly. The most I can glean from the SQL trace is that the insert trigger of timInvtTran aborted the transaction because there was no timInvtTranCost record. From what I know, to complete this problem work order, the last step--material handling (a Labor step)--must be completed by posting a completed Labor entry. From my understanding, completing the Labor entry will complete the last work order step which will cause the whole work order to be completed, all the financial transactions will then be created during the posting process, and then an invoice can be generated. However, the Labor Entry fails to post because of this error. I thought of temporarily commenting out the part of the code of the insert trigger of timInvtTran causing the error so the transaction could go through, but I think it is wiser to get help from Sage support to perhaps to give me a script to rebuild the missing timCostTier and timInvtTranCost records, or tell me a work around (and also to let me know if the latest version of Sage 500 ERP has fixed a bug that causes this issue). As for whether or not changes have been made to the data directly in the back-end (such as deleting the timCostTier and timInvtTranCost records for the finished goods item), I am new here so I cannot answer that question one way or the other.

Contefication · Answer

Most of your understanding of the process is more or less correct, but it could be more complex depending on how the routings/work orders are built. The way you describe the configuration and general problem indicates to me it might be a problem with a specific item, and the exceptionally poor design of some Sage code within that trigger that they have not fixed. 
 You might have some additional clues in your tciSqlExceptionLog table, although there might not be enough information to work around the problem. There are at least a couple of solutions that include your route of modifying the trigger (more than likely permanently), and/or correcting the errant data or condition. You can contact Sage Support to potentially help, or your reseller/consultant. Contact me offline if you need some additional guidance.

Tad Bright · Answer

How would I contact you? What is your background with Sage 500 ERP? I'm busily reading through the manufacturing overview to see if I can understand on a deeper level how the routings/work orders are built for this particular produced item/routing. I will compare to a very similar item where the work order was successfully completed. We have one more support case left to use before the year ends, so I will submit the issue to Sage sometime later today.

Tad Bright · Answer

Contefication, 
 Figured it out. The valuation method in Maintain Items for the produced item was set to "(none)" and should have been set to "Standard". I changed it and the labor entry posted.

Contefication · Answer

Modifying the base data in the back-end is problematic. There are a number of logical rules and associations within the data that indicate a deeper problem as to how your data got into that state, or it might be the way in which the routings are being created. 
 For example: 
 
 An inventory item cannot be created or imported using native features when it does not have an assigned valuation method. 
 A valuation method can only be assigned to inventory item types: Assembled Kit, Finished Good, Raw Material. 
 Unless you established standard cost for the item through one of the methodologies (generally manual entry for a new item), your cost will reflect $0. 
 A warehouse, tracking and valuation method cannot be associated with a non-inventory item, so stock and cost are not tracked. 
 Although you can employ a non-inventory item in a routing step (not allowed in some previous versions), you cannot create stock or run a BOM rollup to adjust standard cost for production of a non-inventory item. A BOM rollup adjusts costs only for produced items. 
 
 There are dozens of other logical rules enforced within the application that apply to inventory management, manufacturing processes, purchase and production planning, as well as product configuration. If you don't have access to the implementation guide, you should review inventory management further and review the processes and/or interfaces for creating items and routings, as well as back-end modifications. 
 BTW, every user profile has attributes that allow or disallow direct communication.

Sage 500

tI_timInvtTran - Cannot INSERT timInvtTran because cost tier effect record is missing.

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING