If you know a little SQL and PL/pgSQL, the procedural language for PostgreSQL, then you may know enough already to add custom events to xTuple ERP. This article shows you how.
To add a custom event to xTuple ERP's event notification system, you have to know a little SQL and PL/pgSQL, Postgres' procedural language, and you have to understand how xTuple stores the data you want to watch.
For example, let's say you want to be notified whenever the quantity on hand of an item drops below the reorder level of that item at a particular site. Here are the steps you need to follow:
- Find out where the quantity on hand information is stored. It turns out this is the itemsite_qtyonhand column of the itemsite table.
- Create a new event type. The event types are stored in the evnttype table, so
INSERT INTO evnttype(evnttype_name, evnttype_descrip, evnttype_module)
VALUES ('QOHBelowReorderLvl', 'QOH dropped below reorder level', 'I/M');
- Create a new trigger function on the itemsite table that watches the itemsite_qtyonhand column (see below). Do not modify an existing trigger, since that might be changed during a later upgrade and your event notification will stop working.
- In the xTuple ERP application, open the "Preferences" window. On the Events tab, select the user who should be notified of the event, select the event type on the left side of the window, and turn on the event notification on the right side of the window.
- Finally, test all of this to make sure it works the way you expect.
Here is a sample definition of a quantity on hand trigger function and trigger. Note that it isn't enough to create the function. You also have to define the trigger itself, which tells Postgres when to call the function.
CREATE OR REPLACE FUNCTION _lowqohTrigger() RETURNS trigger AS
-- if the trigger function was called because the parent table was updated in any way
IF (TG_OP = 'UPDATE') THEN
-- if the quantity on hand changed and the new qoh is below the reorder level for this itemsite
IF (NEW.itemsite_qtyonhand <> OLD.itemsite_qtyonhand AND
NEW.itemsite_qtyonhand < NEW.itemsite_reorderlevel) THEN
-- insert one new event, represented by an evntlog record
-- for every person set up (in the evntnot table)
-- at this site (warehous_id)
-- to hear about the QOHBelowReorderLevel event type
INSERT INTO evntlog(evntlog_evnttime, evntlog_username, evntlog_evnttype_id,
evntlog_ordtype, evntlog_ord_id, evntlog_warehous_id, evntlog_number)
SELECT CURRENT_TIMESTAMP, evntnot_username, evnttype_id,
'I', NEW.itemsite_id, warehous_id, item_number
FROM evntnot, evnttype, item, warehous
AND (evnttype_name='QOHBelowReorderLvl') );
ALTER FUNCTION _lowqohTrigger() OWNER TO admin;
DROP TRIGGER IF EXISTS lowqohTrigger ON itemsite;
CREATE TRIGGER lowqohTrigger AFTER UPDATE ON itemsite
FOR EACH ROW EXECUTE PROCEDURE _lowqohTrigger();
Thanks to Sebastián Salgado for this example.