Create insert trigger on the table costhist. A new row is inserted to this table whenever the actual or standard cost element for an item has changed. This row reflects the change to a specific costing element (material, user defined, direct labor, etc.) and stores the cost history for all costed items. When a new row is inserted, apply the stdcost(item_id) and actcost(item_id) to the item to which the insert is related and compare the result to item_maxcost. If the new cost is > item_maxcost then insert a row in the table evntnot and evntlog (establish and event for users that sbuscribe to it). This event is not related to a Site so an insert to evntnot will need to be performed for all sites to which a user subscribes.
The Event Type (evntype) will be:
Decrip: Cost Exceeds Max Desired
Note that this event should ignore the itemsite_eventfence
Also, an insert is performed on costhist for each cost element in the item's cost structure that has changed. The evntlog_number should be the Item to which the change is related concatenated with the cost element inserted into the costhist table that resulted in the new cost being greater that max desired concatenated with the word Standard if the new cost was a Standard cost or Actual if it was an Actual concatenated with the new cost for the item:
YTRUCK1-Direct Labor-Actual 10.99
YPAINT1-Special Handling-Standard 1.41
If possible we'd like this built into the base application.
Steps to reproduce:
To alert interested parties in the organization when the Actual and/or Standard Cost of an item exceeds the Max Desired Cost established in the Item Master.
Read more at http://www.xtuple.org/mantis/view.php?id=8463