Keywords: Manfacturing, Standard Cost, Revaluation, Replace Costs
A client recently encountered the error below after updating pending cost changes (using WilloWare’s Proposed Cost Update), then doing the Roll Up and Replace Costs routines on the Standard Cost Changes window in GP Manufacturing.
Problems were encountered while creating batch header information. Revaluation aborted.
The Roll Up routine ran OK. Clicking the Replace Costs button resulted in the error.
A Dexterity Script Log showed that the process stopped at a call to Init_GL_Post_Headers. This manufacturing routine creates a Batch Header record and a Batch Activity record.
The Manufacturing Module creates Batch IDs in a format similar to this: dsmithGL_0.
The SY00500 (Batch Headers) table did not contain the Batch ID (i.e. dsmith GL_0) but it did exist in DYNAMICS..SY00800 (Batch Activity).
Further investigation found a SQL table trigger on SY00500 (Company Database) related to an auditing product. The trigger was writing batch headers to a table belonging to the auditing product.
The conclusion was that as part of a test migration from Actual to Standard Cost, some bad data had been created in the inventory module. This caused the first attempt to “Replace Costs” to fail. The data issues were then fixed.
However, the audit software (trigger) had already captured the Batch Number and then failed to clear the record from its table because the Replace Costs posting routine failed.
Subsequent attempts to re-run “Replace Costs” caused Manufacturing to generate the same Batch Number (i.e. dsmithGL_0), and when it tried to add that record to the Batch Headers table the auditing trigger created a duplicate key error on its own table which in turn caused GP to fail to create the Batch Header record.
This case highlights the troublesome nature of SQL table triggers. First, they can cause GP to fail with unhelpful error messages. The GP application doesn’t know source of failure, so it doesn’t know how to respond to it. Second, table triggers are often undocumented, and nobody remembers which tables have them, which makes troubleshooting complicated.