+DROP FUNCTION IF EXISTS changeprdate(integer, date);
CREATE OR REPLACE FUNCTION changePrDate(INTEGER, DATE) RETURNS INTEGER AS $$
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
pPrid ALIAS FOR $1;
END;
$$ LANGUAGE 'plpgsql';
-DROP TRIGGER addrtrigger ON addr;
+DROP TRIGGER IF EXISTS addrtrigger ON addr;
CREATE TRIGGER addrtrigger
BEFORE INSERT OR
UPDATE OR DELETE
CREATE OR REPLACE FUNCTION _aropenTrigger() RETURNS TRIGGER AS $$
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
_openAmount NUMERIC;
SELECT curr_rate INTO _currrate
FROM curr_rate
WHERE ( (NEW.aropen_curr_id=curr_id)
- AND ( NEW.aropen_docdate BETWEEN curr_effective
+ AND ( NEW.aropen_docdate BETWEEN curr_effective
AND curr_expires) );
IF (FOUND) THEN
NEW.aropen_curr_rate := _currrate;
END IF;
IF (TG_OP = 'INSERT') THEN
- IF (NEW.aropen_open=FALSE)
+ IF (NEW.aropen_open=FALSE)
AND (NEW.aropen_closedate IS NULL) THEN
NEW.aropen_closedate=current_date;
END IF;
END IF;
-
+
IF (TG_OP = 'UPDATE') THEN
- IF ((OLD.aropen_open=TRUE)
- AND (NEW.aropen_open=FALSE)
+ IF ((OLD.aropen_open=TRUE)
+ AND (NEW.aropen_open=FALSE)
AND (NEW.aropen_closedate IS NULL)) THEN
NEW.aropen_closedate=current_date;
END IF;
$$
LANGUAGE 'plpgsql';
-DROP TRIGGER aropenTrigger ON aropen;
+DROP TRIGGER IF EXISTS aropenTrigger ON aropen;
CREATE TRIGGER aropenTrigger BEFORE INSERT OR UPDATE ON aropen FOR EACH ROW EXECUTE PROCEDURE _aropenTrigger();
CREATE OR REPLACE FUNCTION _aropenAfterTrigger() RETURNS TRIGGER AS $$
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
_openAmount NUMERIC;
CREATE OR REPLACE FUNCTION _bomheadTrigger() RETURNS TRIGGER AS '
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
_revid INTEGER;
IF (NOT checkPrivilege(''MaintainBOMs'')) THEN
RAISE EXCEPTION ''You do not have privileges to maintain Bills of Material.'';
END IF;
-
+
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
-DROP TRIGGER bomheadTrigger ON bomhead;
+DROP TRIGGER IF EXISTS bomheadTrigger ON bomhead;
CREATE TRIGGER bomheadTrigger AFTER INSERT OR UPDATE OR DELETE ON bomhead FOR EACH ROW EXECUTE PROCEDURE _bomheadTrigger();
CREATE OR REPLACE FUNCTION _bomitemsubTrigger() RETURNS TRIGGER AS '
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
END;
' LANGUAGE 'plpgsql';
-DROP TRIGGER bomitemsubTrigger ON bomitemsub;
+DROP TRIGGER IF EXISTS bomitemsubTrigger ON bomitemsub;
CREATE TRIGGER bomitemsubTrigger BEFORE INSERT OR UPDATE OR DELETE ON bomitemsub FOR EACH ROW EXECUTE PROCEDURE _bomitemsubTrigger();
CREATE OR REPLACE FUNCTION _ccardtrigger() RETURNS TRIGGER AS '
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
'
LANGUAGE 'plpgsql';
-DROP TRIGGER ccardtrigger ON ccard;
+DROP TRIGGER IF EXISTS ccardtrigger ON ccard;
CREATE TRIGGER ccardtrigger BEFORE INSERT OR UPDATE ON ccard FOR EACH ROW EXECUTE PROCEDURE _ccardtrigger();
CREATE OR REPLACE FUNCTION _cntslipTrigger() RETURNS TRIGGER AS '
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
_p RECORD;
FROM location, cntslip
WHERE ( (cntslip_location_id=location_id)
AND (cntslip_id=OLD.cntslip_id) );
-
+
_comments := (_comments || _temp);
END IF;
-
+
-- Add the Lot/Serial if the itemsite is Lot or Serial controlled
IF (_p.itemsite_controlmethod = ''L'') THEN
_comments := (_comments || ( '', Lot #:'' || _p.cntslip_lotserial));
ELSIF (_p.itemsite_controlmethod = ''S'') THEN
_comments := (_comments || ( '', Serial #:'' || _p.cntslip_lotserial));
END IF;
-
+
_comments := (_comments || '' '' || _p.cntslip_comments);
-
+
UPDATE invcnt
SET invcnt_qoh_after = ( COALESCE(invcnt_qoh_after, 0) - cntslip_qty),
invcnt_comments = (invcnt_comments || _comments)
END;
' LANGUAGE 'plpgsql';
-DROP TRIGGER cntslipTrigger ON cntslip;
+DROP TRIGGER IF EXISTS cntslipTrigger ON cntslip;
CREATE TRIGGER cntslipTrigger BEFORE INSERT OR UPDATE OR DELETE ON cntslip FOR EACH ROW EXECUTE PROCEDURE _cntslipTrigger();
CREATE OR REPLACE FUNCTION _soheadTrigger() RETURNS TRIGGER AS $$
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
_p RECORD;
IF (_p.cust_creditstatus = 'H') THEN
SELECT checkPrivilege('CreateSOForHoldCustomer') INTO _check;
IF NOT (_check) THEN
- RAISE EXCEPTION 'Customer % has been placed
- on a Credit Hold and you do not have
- privilege to create Sales Orders for
- Customers on Credit Hold. The selected
- Customer must be taken off of Credit Hold
- before you may create a new Sales Order
+ RAISE EXCEPTION 'Customer % has been placed
+ on a Credit Hold and you do not have
+ privilege to create Sales Orders for
+ Customers on Credit Hold. The selected
+ Customer must be taken off of Credit Hold
+ before you may create a new Sales Order
for the Customer.',_p.cust_number;
ELSE
NEW.cohead_holdtype='C';
IF (_p.cust_creditstatus = 'W') THEN
SELECT checkPrivilege('CreateSOForWarnCustomer') INTO _check;
IF NOT (_check) THEN
- RAISE EXCEPTION 'Customer % has been placed on
- a Credit Warning and you do not have
- privilege to create Sales Orders for
- Customers on Credit Warning. The
- selected Customer must be taken off of
- Credit Warning before you may create a
+ RAISE EXCEPTION 'Customer % has been placed on
+ a Credit Warning and you do not have
+ privilege to create Sales Orders for
+ Customers on Credit Warning. The
+ selected Customer must be taken off of
+ Credit Warning before you may create a
new Sales Order for the Customer.',_p.cust_number;
ELSE
NEW.cohead_holdtype='C';
FROM whsinfo
WHERE (warehous_id=NEW.cohead_warehous_id);
END IF;
-
+
IF (FOUND) THEN
NEW.cohead_warehous_id := COALESCE(NEW.cohead_warehous_id,_w.warehous_id);
NEW.cohead_fob := COALESCE(NEW.cohead_fob,_w.warehous_fob);
END IF;
END IF;
-
+
END IF;
-- Only Check P/O logic for imports, because UI checks when entire order is saved
IF (_p.cust_usespos AND ((NEW.cohead_custponumber IS NULL) OR (TRIM(BOTH FROM NEW.cohead_custponumber)=''))) THEN
RAISE EXCEPTION 'You must enter a Customer P/O for this Sales Order.';
END IF;
-
+
-- Check for duplicate Purchase Orders if not allowed
IF (_p.cust_usespos AND NOT (_p.cust_blanketpos)) THEN
SELECT cohead_id INTO _a
AND (UPPER(quhead_custponumber) = UPPER(NEW.cohead_custponumber)) );
IF (FOUND) THEN
RAISE EXCEPTION 'This Customer does not use Blanket P/O
- Numbers and the P/O Number you entered has
+ Numbers and the P/O Number you entered has
already been used for another Sales Order.
Please verify the P/O Number and either
enter a new P/O Number or add to the
SELECT true INTO _check
FROM coitem
WHERE ( (coitem_status='C')
- AND (coitem_cohead_id=NEW.cohead_id) )
+ AND (coitem_cohead_id=NEW.cohead_id) )
LIMIT 1;
IF (NOT FOUND) THEN
--Update project references on supply
UPDATE pr SET pr_prj_id=NEW.cohead_prj_id
FROM coitem
- WHERE ((coitem_cohead_id=NEW.cohead_id)
- AND (coitem_order_type='R')
+ WHERE ((coitem_cohead_id=NEW.cohead_id)
+ AND (coitem_order_type='R')
AND (coitem_order_id=pr_id));
PERFORM changeWoProject(coitem_order_id, NEW.cohead_prj_id, TRUE)
NEW.cohead_billto_cntct_id=COALESCE(NEW.cohead_billto_cntct_id,_p.cntct_id);
NEW.cohead_billto_cntct_honorific=COALESCE(NEW.cohead_billto_cntct_honorific,_p.cntct_honorific,'');
NEW.cohead_billto_cntct_first_name=COALESCE(NEW.cohead_billto_cntct_first_name,_p.cntct_first_name,'');
- NEW.cohead_billto_cntct_middle=COALESCE(NEW.cohead_billto_cntct_middle,_p.cntct_middle,'');
- NEW.cohead_billto_cntct_last_name=COALESCE(NEW.cohead_billto_cntct_last_name,_p.cntct_last_name,'');
+ NEW.cohead_billto_cntct_middle=COALESCE(NEW.cohead_billto_cntct_middle,_p.cntct_middle,'');
+ NEW.cohead_billto_cntct_last_name=COALESCE(NEW.cohead_billto_cntct_last_name,_p.cntct_last_name,'');
NEW.cohead_billto_cntct_phone=COALESCE(NEW.cohead_billto_cntct_phone,_p.cntct_phone,'');
NEW.cohead_billto_cntct_title=COALESCE(NEW.cohead_billto_cntct_title,_p.cntct_title,'');
- NEW.cohead_billto_cntct_fax=COALESCE(NEW.cohead_billto_cntct_fax,_p.cntct_fax,'');
- NEW.cohead_billto_cntct_email=COALESCE(NEW.cohead_billto_cntct_email,_p.cntct_email,'');
+ NEW.cohead_billto_cntct_fax=COALESCE(NEW.cohead_billto_cntct_fax,_p.cntct_fax,'');
+ NEW.cohead_billto_cntct_email=COALESCE(NEW.cohead_billto_cntct_email,_p.cntct_email,'');
NEW.cohead_billtoname=COALESCE(NEW.cohead_billtoname,_p.cust_name,'');
NEW.cohead_billtoaddress1=COALESCE(NEW.cohead_billtoaddress1,_p.addr_line1,'');
NEW.cohead_billtoaddress2=COALESCE(NEW.cohead_billtoaddress2,_p.addr_line2,'');
- NEW.cohead_billtoaddress3=COALESCE(NEW.cohead_billtoaddress3,_p.addr_line3,'');
- NEW.cohead_billtocity=COALESCE(NEW.cohead_billtocity,_p.addr_city,'');
+ NEW.cohead_billtoaddress3=COALESCE(NEW.cohead_billtoaddress3,_p.addr_line3,'');
+ NEW.cohead_billtocity=COALESCE(NEW.cohead_billtocity,_p.addr_city,'');
NEW.cohead_billtostate=COALESCE(NEW.cohead_billtostate,_p.addr_state,'');
NEW.cohead_billtozipcode=COALESCE(NEW.cohead_billtozipcode,_p.addr_postalcode,'');
- NEW.cohead_billtocountry=COALESCE(NEW.cohead_billtocountry,_p.addr_country,'');
+ NEW.cohead_billtocountry=COALESCE(NEW.cohead_billtocountry,_p.addr_country,'');
ELSE
-- Free form not allowed, we're going to put in the address regardless
NEW.cohead_billto_cntct_id=_p.cntct_id;
NEW.cohead_billto_cntct_honorific=COALESCE(_p.cntct_honorific,'');
NEW.cohead_billto_cntct_first_name=COALESCE(_p.cntct_first_name,'');
- NEW.cohead_billto_cntct_middle=COALESCE(_p.cntct_middle,'');
- NEW.cohead_billto_cntct_last_name=COALESCE(_p.cntct_last_name,'');
+ NEW.cohead_billto_cntct_middle=COALESCE(_p.cntct_middle,'');
+ NEW.cohead_billto_cntct_last_name=COALESCE(_p.cntct_last_name,'');
NEW.cohead_billto_cntct_phone=COALESCE(_p.cntct_phone,'');
NEW.cohead_billto_cntct_title=COALESCE(_p.cntct_title,'');
- NEW.cohead_billto_cntct_fax=COALESCE(_p.cntct_fax,'');
- NEW.cohead_billto_cntct_email=COALESCE(_p.cntct_email,'');
+ NEW.cohead_billto_cntct_fax=COALESCE(_p.cntct_fax,'');
+ NEW.cohead_billto_cntct_email=COALESCE(_p.cntct_email,'');
NEW.cohead_billtoname=COALESCE(_p.cust_name,'');
NEW.cohead_billtoaddress1=COALESCE(_p.addr_line1,'');
NEW.cohead_billtoaddress2=COALESCE(_p.addr_line2,'');
- NEW.cohead_billtoaddress3=COALESCE(_p.addr_line3,'');
- NEW.cohead_billtocity=COALESCE(_p.addr_city,'');
+ NEW.cohead_billtoaddress3=COALESCE(_p.addr_line3,'');
+ NEW.cohead_billtocity=COALESCE(_p.addr_city,'');
NEW.cohead_billtostate=COALESCE(_p.addr_state,'');
NEW.cohead_billtozipcode=COALESCE(_p.addr_postalcode,'');
NEW.cohead_billtocountry=COALESCE(_p.addr_country,'');
END IF;
-- Now let's look at Shipto Address
- -- If there's nothing in the address fields and there is a shipto id
+ -- If there's nothing in the address fields and there is a shipto id
-- or there is a default address available, let's put in some shipto address data
- IF ((TG_OP = 'INSERT')
+ IF ((TG_OP = 'INSERT')
AND NOT ((NEW.cohead_shipto_id IS NULL) AND NOT _p.cust_ffshipto)
AND (NEW.cohead_shipto_cntct_id IS NULL)
AND (NEW.cohead_shipto_cntct_honorific IS NULL)
_shiptoId := NEW.cohead_shipto_id;
END IF;
- SELECT * INTO _a
+ SELECT * INTO _a
FROM shiptoinfo
LEFT OUTER JOIN addr ON (addr_id=shipto_addr_id)
LEFT OUTER JOIN cntct ON (cntct_id=shipto_cntct_id)
NEW.cohead_shiptoname := COALESCE(_p.shipto_name,'');
NEW.cohead_shiptoaddress1 := COALESCE(_a.addr_line1,'');
NEW.cohead_shiptoaddress2 := COALESCE(_a.addr_line2,'');
- NEW.cohead_shiptoaddress3 := COALESCE(_a.addr_line3,'');
- NEW.cohead_shiptocity := COALESCE(_a.addr_city,'');
+ NEW.cohead_shiptoaddress3 := COALESCE(_a.addr_line3,'');
+ NEW.cohead_shiptocity := COALESCE(_a.addr_city,'');
NEW.cohead_shiptostate := COALESCE(_a.addr_state,'');
NEW.cohead_shiptozipcode := COALESCE(_a.addr_postalcode,'');
NEW.cohead_shiptocountry := COALESCE(_a.addr_country,'');
SELECT cohead_shipto_id INTO _shiptoid FROM cohead WHERE (cohead_id=NEW.cohead_id);
-- Get the shipto address
IF (COALESCE(NEW.cohead_shipto_id,-1) <> COALESCE(_shiptoid,-1)) THEN
- SELECT * INTO _a
+ SELECT * INTO _a
FROM shiptoinfo
LEFT OUTER JOIN cntct ON (shipto_cntct_id=cntct_id)
LEFT OUTER JOIN addr ON (shipto_addr_id=addr_id)
NEW.cohead_shipto_cntct_id=_a.cntct_id;
NEW.cohead_shipto_cntct_honorific=COALESCE(_a.cntct_honorific,'');
NEW.cohead_shipto_cntct_first_name=COALESCE(_a.cntct_first_name,'');
- NEW.cohead_shipto_cntct_middle=COALESCE(_a.cntct_middle,'');
- NEW.cohead_shipto_cntct_last_name=COALESCE(_a.cntct_last_name,'');
+ NEW.cohead_shipto_cntct_middle=COALESCE(_a.cntct_middle,'');
+ NEW.cohead_shipto_cntct_last_name=COALESCE(_a.cntct_last_name,'');
NEW.cohead_shipto_cntct_phone=COALESCE(_a.cntct_phone,'');
NEW.cohead_shipto_cntct_title=COALESCE(_a.cntct_title,'');
- NEW.cohead_shipto_cntct_fax=COALESCE(_a.cntct_fax,'');
- NEW.cohead_shipto_cntct_email=COALESCE(_a.cntct_email,'');
+ NEW.cohead_shipto_cntct_fax=COALESCE(_a.cntct_fax,'');
+ NEW.cohead_shipto_cntct_email=COALESCE(_a.cntct_email,'');
NEW.cohead_shiptoname := COALESCE(_a.shipto_name,'');
NEW.cohead_shiptophone := COALESCE(_a.cntct_phone,'');
NEW.cohead_shiptoaddress1 := COALESCE(_a.addr_line1,'');
NEW.cohead_shiptoaddress2 := COALESCE(_a.addr_line2,'');
- NEW.cohead_shiptoaddress3 := COALESCE(_a.addr_line3,'');
- NEW.cohead_shiptocity := COALESCE(_a.addr_city,'');
+ NEW.cohead_shiptoaddress3 := COALESCE(_a.addr_line3,'');
+ NEW.cohead_shiptocity := COALESCE(_a.addr_city,'');
NEW.cohead_shiptostate := COALESCE(_a.addr_state,'');
NEW.cohead_shiptozipcode := COALESCE(_a.addr_postalcode,'');
- NEW.cohead_shiptocountry := COALESCE(_a.addr_country,'');
+ NEW.cohead_shiptocountry := COALESCE(_a.addr_country,'');
ELSE
-- If no shipto data and free form not allowed, this won't work
RAISE EXCEPTION 'Free form Shipto is not allowed on this Customer. You must supply a valid Shipto ID.';
ELSIF (TG_OP = 'DELETE') THEN
DELETE FROM docass WHERE docass_source_id = OLD.cohead_id AND docass_source_type = 'S';
DELETE FROM docass WHERE docass_target_id = OLD.cohead_id AND docass_target_type = 'S';
-
+
DELETE FROM comment
WHERE ( (comment_source='S')
AND (comment_source_id=OLD.cohead_id) );
IF ((OLD.cohead_shipchrg_id != NEW.cohead_shipchrg_id)
OR (OLD.cohead_freight != NEW.cohead_freight)
OR (OLD.cohead_shipvia != NEW.cohead_shipvia)) THEN
- UPDATE shiphead SET
+ UPDATE shiphead SET
shiphead_shipchrg_id=
CASE WHEN (NEW.cohead_shipchrg_id <= 0) THEN NULL
ELSE NEW.cohead_shipchrg_id
END;
$$ LANGUAGE 'plpgsql';
-DROP TRIGGER soheadTrigger ON cohead;
+DROP TRIGGER IF EXISTS soheadTrigger ON cohead;
CREATE TRIGGER soheadTrigger BEFORE INSERT OR UPDATE OR DELETE ON cohead FOR EACH ROW EXECUTE PROCEDURE _soheadTrigger();
CREATE OR REPLACE FUNCTION _soheadTriggerAfter() RETURNS TRIGGER AS $$
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
IF (COALESCE(NEW.cohead_taxzone_id,-1) <> COALESCE(OLD.cohead_taxzone_id,-1)) THEN
UPDATE coitem SET coitem_taxtype_id=getItemTaxType(itemsite_item_id,NEW.cohead_taxzone_id)
- FROM itemsite
+ FROM itemsite
WHERE ((itemsite_id=coitem_itemsite_id)
AND (coitem_cohead_id=NEW.cohead_id));
END IF;
CREATE OR REPLACE FUNCTION _commentTrigger () RETURNS TRIGGER AS $$
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
IF (NEW.comment_cmnttype_id IS NULL) THEN
END;
$$ LANGUAGE 'plpgsql';
-DROP TRIGGER commentTrigger ON comment;
+DROP TRIGGER IF EXISTS commentTrigger ON comment;
CREATE TRIGGER commentTrigger AFTER INSERT OR UPDATE ON comment FOR EACH ROW EXECUTE PROCEDURE _commentTrigger();
-- TODO: add special handling for converting prospects <-> customers?
CREATE OR REPLACE FUNCTION _crmacctBeforeTrigger () RETURNS TRIGGER AS $$
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
_count INTEGER;
END;
$$ LANGUAGE 'plpgsql';
-DROP TRIGGER crmacctBeforeTrigger ON crmacct;
+DROP TRIGGER IF EXISTS crmacctBeforeTrigger ON crmacct;
CREATE TRIGGER crmacctBeforeTrigger BEFORE INSERT OR UPDATE OR DELETE
ON crmacct FOR EACH ROW EXECUTE PROCEDURE _crmacctBeforeTrigger();
CREATE OR REPLACE FUNCTION _crmacctAfterTrigger () RETURNS TRIGGER AS $$
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
_cmnttypeid INTEGER;
END;
$$ LANGUAGE 'plpgsql';
-DROP TRIGGER crmacctAfterTrigger ON crmacct;
+DROP TRIGGER IF EXISTS crmacctAfterTrigger ON crmacct;
CREATE TRIGGER crmacctAfterTrigger AFTER INSERT OR UPDATE OR DELETE ON crmacct FOR EACH ROW EXECUTE PROCEDURE _crmacctAfterTrigger();
CREATE OR REPLACE FUNCTION currExchangeCheckOverlap () RETURNS trigger AS '
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
numberOfOverlaps INTEGER NOT NULL := 0;
END;
' LANGUAGE 'plpgsql';
-DROP TRIGGER currExchangeCheckOverlap ON curr_rate;
+DROP TRIGGER IF EXISTS currExchangeCheckOverlap ON curr_rate;
CREATE TRIGGER currExchangeCheckOverlap BEFORE INSERT OR UPDATE ON curr_rate
FOR EACH ROW EXECUTE PROCEDURE currExchangeCheckOverlap();
CREATE OR REPLACE FUNCTION currOneBase() RETURNS trigger AS '
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
baseCount INTEGER := 0;
END;
' LANGUAGE 'plpgsql';
-DROP TRIGGER currOneBase ON curr_symbol;
+DROP TRIGGER IF EXISTS currOneBase ON curr_symbol;
CREATE TRIGGER currOneBase AFTER INSERT OR UPDATE ON curr_symbol
FOR EACH ROW EXECUTE PROCEDURE currOneBase();
CREATE OR REPLACE FUNCTION _itemTrigger () RETURNS TRIGGER AS $$
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
-
+
-- Override values to avoid invalid data combinations
IF (NEW.item_type IN ('R','S','O','L','B')) THEN
NEW.item_picklist := FALSE;
END;
$$ LANGUAGE 'plpgsql';
-DROP TRIGGER itemTrigger ON item;
+DROP TRIGGER IF EXISTS itemTrigger ON item;
CREATE TRIGGER itemTrigger BEFORE INSERT OR UPDATE ON item FOR EACH ROW EXECUTE PROCEDURE _itemTrigger();
CREATE OR REPLACE FUNCTION _itemAfterTrigger () RETURNS TRIGGER AS $$
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
_cmnttypeid INTEGER;
IF (NOT checkPrivilege('MaintainItemMasters')) THEN
RAISE EXCEPTION 'You do not have privileges to maintain Items.';
END IF;
-
+
-- Integrity checks
IF (TG_OP = 'UPDATE') THEN
IF ((OLD.item_type <> NEW.item_type) AND (NEW.item_type = 'L')) THEN
RETURN OLD;
END IF;
-
+
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';
-DROP TRIGGER itemAfterTrigger ON item;
+DROP TRIGGER IF EXISTS itemAfterTrigger ON item;
CREATE TRIGGER itemAfterTrigger AFTER INSERT OR UPDATE OR DELETE ON item FOR EACH ROW EXECUTE PROCEDURE _itemAfterTrigger();
CREATE OR REPLACE FUNCTION _itemCostTrigger() RETURNS TRIGGER AS $$
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
END;
$$ LANGUAGE 'plpgsql';
-DROP TRIGGER itemCostTrigger ON itemcost;
+DROP TRIGGER IF EXISTS itemCostTrigger ON itemcost;
CREATE TRIGGER itemCostTrigger BEFORE INSERT OR UPDATE OR DELETE ON itemcost FOR EACH ROW EXECUTE PROCEDURE _itemCostTrigger();
CREATE OR REPLACE FUNCTION _itemCostAfterTrigger() RETURNS TRIGGER AS $$
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
_itemNumber TEXT;
_oldActCost NUMERIC;
_actualCost NUMERIC;
_standardCost NUMERIC;
-
+
BEGIN
-- Create Event if Standard or Actual Cost is greater than Max Cost
-IF NOT EXISTS(SELECT 1
+IF NOT EXISTS(SELECT 1
FROM evntnot
JOIN evnttype ON (evnttype_id = evntnot_evnttype_id)
JOIN usrpref ON (evntnot_username = usrpref_username)
WHERE
evnttype_name = 'CostExceedsMaxDesired'
- AND usrpref_name = 'active'
- AND usrpref_value = 't')
+ AND usrpref_name = 'active'
+ AND usrpref_value = 't')
THEN
- RETURN NEW;
-END IF;
+ RETURN NEW;
+END IF;
SELECT item_number, item_maxcost, actcost(item_id), stdcost(item_id) INTO _itemNumber, _maxCost, _actualCost, _standardCost
FROM item
WHERE (item_id=NEW.itemcost_item_id);
IF (_maxCost > 0.0) THEN
- -- IF (_standardCost > _maxCost)
- IF NOT EXISTS(SELECT 1 --COUNT(evntlog_id)
+ -- IF (_standardCost > _maxCost)
+ IF NOT EXISTS(SELECT 1 --COUNT(evntlog_id)
FROM
evntlog, evnttype
- WHERE evntlog_evnttype_id = evnttype_id
- AND evntlog_number LIKE
+ WHERE evntlog_evnttype_id = evnttype_id
+ AND evntlog_number LIKE
(_itemNumber || ' -Standard- New:' || '%')
-
+
AND (evntlog_dispatched IS NULL)
AND CAST(evntlog_evnttime AS DATE) = current_date
-
- )
+
+ )
AND (_standardCost > _maxCost) THEN
-
-
+
+
IF (TG_OP = 'INSERT') THEN
_oldStdCost := 0;
_oldActCost := 0;
ELSE
_oldStdCost := OLD.itemcost_stdcost;
_oldActCost := OLD.itemcost_stdcost;
- END IF;
+ END IF;
PERFORM postEvent('CostExceedsMaxDesired', NULL, NEW.itemcost_item_id,
itemsite_warehous_id,
- (_itemNumber || ' -Standard- ' ||
+ (_itemNumber || ' -Standard- ' ||
'New: ' || formatCost(_standardCost) ||
' Max: '|| formatCost(_MaxCost)),
NEW.itemcost_stdcost, _oldStdCost,
IF NOT EXISTS(
SELECT 1 FROM
evntlog, evnttype
- WHERE evntlog_evnttype_id = evnttype_id
- AND evntlog_number LIKE
+ WHERE evntlog_evnttype_id = evnttype_id
+ AND evntlog_number LIKE
(_itemNumber || ' -Actual- New:' || '%')
AND (evntlog_dispatched IS NULL)
AND (_actualCost > _maxCost)
THEN
-
+
PERFORM postEvent('CostExceedsMaxDesired', NULL, NEW.itemcost_item_id,
itemsite_warehous_id,
- (_itemNumber || ' -Actual- ' ||
+ (_itemNumber || ' -Actual- ' ||
'New: ' || formatCost(_actualCost) ||
' Max: '|| formatCost(_MaxCost)),
NEW.itemcost_actcost, _oldActCost,
END;
$$ LANGUAGE 'plpgsql';
-DROP TRIGGER itemCostAfterTrigger ON itemcost;
+DROP TRIGGER IF EXISTS itemCostAfterTrigger ON itemcost;
CREATE TRIGGER itemCostAfterTrigger AFTER INSERT OR UPDATE ON itemcost FOR EACH ROW EXECUTE PROCEDURE _itemCostAfterTrigger();
CREATE OR REPLACE FUNCTION _itemsubTrigger () RETURNS TRIGGER AS $$
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
IF (NOT checkPrivilege('MaintainItemMasters')) THEN
RAISE EXCEPTION 'You do not have privileges to maintain Item Substitutes.';
END IF;
-
+
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';
-DROP TRIGGER itemsubTrigger ON itemsub;
+DROP TRIGGER IF EXISTS itemsubTrigger ON itemsub;
CREATE TRIGGER itemsubTrigger AFTER INSERT OR UPDATE ON itemsub FOR EACH ROW EXECUTE PROCEDURE _itemsubTrigger();
CREATE OR REPLACE FUNCTION _itemtaxTrigger () RETURNS TRIGGER AS '
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
IF (NOT checkPrivilege(''MaintainItemMasters'')) THEN
RAISE EXCEPTION ''You do not have privileges to maintain Items.'';
END IF;
-
+
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
-DROP TRIGGER itemtaxTrigger ON itemtax;
+DROP TRIGGER IF EXISTS itemtaxTrigger ON itemtax;
CREATE TRIGGER itemtaxTrigger AFTER INSERT OR UPDATE ON itemtax FOR EACH ROW EXECUTE PROCEDURE _itemtaxTrigger();
CREATE OR REPLACE FUNCTION _itemuomconvTrigger () RETURNS TRIGGER AS '
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
IF (NOT checkPrivilege(''MaintainItemMasters'')) THEN
RAISE EXCEPTION ''You do not have privileges to maintain Items.'';
END IF;
-
+
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
-DROP TRIGGER itemuomconvTrigger ON itemuomconv;
+DROP TRIGGER IF EXISTS itemuomconvTrigger ON itemuomconv;
CREATE TRIGGER itemuomconvTrigger AFTER INSERT OR UPDATE ON itemuomconv FOR EACH ROW EXECUTE PROCEDURE _itemuomconvTrigger();
CREATE OR REPLACE FUNCTION _packBeforeTrigger() RETURNS TRIGGER AS $$
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
_cmnttypeid INTEGER;
END;
$$ LANGUAGE 'plpgsql';
-DROP TRIGGER packBeforeTrigger ON pack;
+DROP TRIGGER IF EXISTS packBeforeTrigger ON pack;
CREATE TRIGGER packBeforeTrigger BEFORE INSERT OR UPDATE ON pack FOR EACH ROW EXECUTE PROCEDURE _packBeforeTrigger();
CREATE OR REPLACE FUNCTION _poheadTrigger() RETURNS TRIGGER AS $$
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
_cmnttypeid INTEGER;
ELSIF (TG_OP = 'DELETE') THEN
DELETE FROM docass WHERE docass_source_id = OLD.pohead_id AND docass_source_type = 'P';
DELETE FROM docass WHERE docass_target_id = OLD.pohead_id AND docass_target_type = 'P';
-
+
DELETE FROM comment
WHERE ( (comment_source='P')
AND (comment_source_id=OLD.pohead_id) );
END;
$$ LANGUAGE 'plpgsql';
-DROP TRIGGER poheadTrigger ON pohead;
+DROP TRIGGER IF EXISTS poheadTrigger ON pohead;
CREATE TRIGGER poheadTrigger BEFORE INSERT OR UPDATE OR DELETE ON pohead FOR EACH ROW EXECUTE PROCEDURE _poheadTrigger();
CREATE OR REPLACE FUNCTION _poheadTriggerAfter() RETURNS TRIGGER AS $$
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
IF (COALESCE(NEW.pohead_taxzone_id,-1) <> COALESCE(OLD.pohead_taxzone_id,-1)) THEN
UPDATE poitem SET poitem_taxtype_id=getItemTaxType(itemsite_item_id,NEW.pohead_taxzone_id)
- FROM itemsite
+ FROM itemsite
WHERE ((itemsite_id=poitem_itemsite_id)
AND (poitem_pohead_id=NEW.pohead_id));
END IF;
CREATE OR REPLACE FUNCTION _quheadtrigger() RETURNS "trigger" AS $$
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
_cmnttypeid INTEGER;
END IF;
END IF;
END IF;
-
+
IF (fetchMetricText('QUNumberGeneration') IN ('A','O')) THEN
--- clear the number from the issue cache
PERFORM clearNumberIssue('QuNumber', NEW.quhead_number);
--- clear the number from the issue cache
PERFORM clearNumberIssue('SoNumber', NEW.quhead_number);
END IF;
-
+
ELSE
IF (TG_OP = 'UPDATE') THEN
IF (NEW.quhead_number <> OLD.quhead_number) THEN
FROM whsinfo
WHERE (warehous_id=NEW.quhead_warehous_id);
END IF;
-
+
IF (FOUND) THEN
NEW.quhead_warehous_id := COALESCE(NEW.quhead_warehous_id,_w.warehous_id);
NEW.quhead_fob := COALESCE(NEW.quhead_fob,_w.warehous_fob);
END IF;
END IF;
END IF;
-
+
--Auto create project if applicable
IF ((TG_OP = 'INSERT') AND (COALESCE(NEW.quhead_prj_id,-1)=-1)) THEN
SELECT fetchMetricBool('AutoCreateProjectsForOrders') INTO _check;
NEW.quhead_billtoname=COALESCE(NEW.quhead_billtoname,_p.cust_name,'');
NEW.quhead_billtoaddress1=COALESCE(NEW.quhead_billtoaddress1,_p.addr_line1,'');
NEW.quhead_billtoaddress2=COALESCE(NEW.quhead_billtoaddress2,_p.addr_line2,'');
- NEW.quhead_billtoaddress3=COALESCE(NEW.quhead_billtoaddress3,_p.addr_line3,'');
- NEW.quhead_billtocity=COALESCE(NEW.quhead_billtocity,_p.addr_city,'');
+ NEW.quhead_billtoaddress3=COALESCE(NEW.quhead_billtoaddress3,_p.addr_line3,'');
+ NEW.quhead_billtocity=COALESCE(NEW.quhead_billtocity,_p.addr_city,'');
NEW.quhead_billtostate=COALESCE(NEW.quhead_billtostate,_p.addr_state,'');
NEW.quhead_billtozip=COALESCE(NEW.quhead_billtozip,_p.addr_postalcode,'');
- NEW.quhead_billtocountry=COALESCE(NEW.quhead_billtocountry,_p.addr_country,'');
+ NEW.quhead_billtocountry=COALESCE(NEW.quhead_billtocountry,_p.addr_country,'');
ELSE
-- Free form not allowed, we're going to put in the address regardless
NEW.quhead_billtoname=COALESCE(_p.cust_name,'');
NEW.quhead_billtoaddress1=COALESCE(_p.addr_line1,'');
NEW.quhead_billtoaddress2=COALESCE(_p.addr_line2,'');
- NEW.quhead_billtoaddress3=COALESCE(_p.addr_line3,'');
- NEW.quhead_billtocity=COALESCE(_p.addr_city,'');
+ NEW.quhead_billtoaddress3=COALESCE(_p.addr_line3,'');
+ NEW.quhead_billtocity=COALESCE(_p.addr_city,'');
NEW.quhead_billtostate=COALESCE(_p.addr_state,'');
NEW.quhead_billtozip=COALESCE(_p.addr_postalcode,'');
NEW.quhead_billtocountry=COALESCE(_p.addr_country,'');
END IF;
-- Now let's look at Shipto Address
- -- If there's nothing in the address fields and there is a shipto id
+ -- If there's nothing in the address fields and there is a shipto id
-- or there is a default address available, let's put in some shipto address data
- IF ((TG_OP = 'INSERT')
+ IF ((TG_OP = 'INSERT')
AND NOT ((NEW.quhead_shipto_id IS NULL) AND NOT _p.cust_ffshipto)
AND (NEW.quhead_shiptoname IS NULL)
AND (NEW.quhead_shiptoaddress1 IS NULL)
_shiptoId := NEW.quhead_shipto_id;
END IF;
- SELECT * INTO _a
- FROM shiptoinfo, addr
+ SELECT * INTO _a
+ FROM shiptoinfo, addr
WHERE ((shipto_id=_shiptoId)
AND (addr_id=shipto_addr_id));
NEW.quhead_shiptoname := COALESCE(_p.shipto_name,'');
NEW.quhead_shiptoaddress1 := COALESCE(_a.addr_line1,'');
NEW.quhead_shiptoaddress2 := COALESCE(_a.addr_line2,'');
- NEW.quhead_shiptoaddress3 := COALESCE(_a.addr_line3,'');
- NEW.quhead_shiptocity := COALESCE(_a.addr_city,'');
+ NEW.quhead_shiptoaddress3 := COALESCE(_a.addr_line3,'');
+ NEW.quhead_shiptocity := COALESCE(_a.addr_city,'');
NEW.quhead_shiptostate := COALESCE(_a.addr_state,'');
NEW.quhead_shiptozipcode := COALESCE(_a.addr_postalcode,'');
NEW.quhead_shiptocountry := COALESCE(_a.addr_country,'');
SELECT quhead_shipto_id INTO _shiptoid FROM quhead WHERE (quhead_id=NEW.quhead_id);
-- Get the shipto address
IF (COALESCE(NEW.quhead_shipto_id,-1) <> COALESCE(_shiptoid,-1)) THEN
- SELECT * INTO _a
+ SELECT * INTO _a
FROM shiptoinfo
LEFT OUTER JOIN cntct ON (shipto_cntct_id=cntct_id)
LEFT OUTER JOIN addr ON (shipto_addr_id=addr_id)
NEW.quhead_shiptophone := COALESCE(_a.cntct_phone,'');
NEW.quhead_shiptoaddress1 := COALESCE(_a.addr_line1,'');
NEW.quhead_shiptoaddress2 := COALESCE(_a.addr_line2,'');
- NEW.quhead_shiptoaddress3 := COALESCE(_a.addr_line3,'');
- NEW.quhead_shiptocity := COALESCE(_a.addr_city,'');
+ NEW.quhead_shiptoaddress3 := COALESCE(_a.addr_line3,'');
+ NEW.quhead_shiptocity := COALESCE(_a.addr_city,'');
NEW.quhead_shiptostate := COALESCE(_a.addr_state,'');
NEW.quhead_shiptozipcode := COALESCE(_a.addr_postalcode,'');
- NEW.quhead_shiptocountry := COALESCE(_a.addr_country,'');
+ NEW.quhead_shiptocountry := COALESCE(_a.addr_country,'');
ELSE
-- If no shipto data and free form not allowed, this won't work
RAISE EXCEPTION 'Free form Shipto is not allowed on this Customer. You must supply a valid Shipto ID.';
END;
$$ LANGUAGE 'plpgsql';
-DROP TRIGGER quheadtrigger ON quhead;
+DROP TRIGGER IF EXISTS quheadtrigger ON quhead;
CREATE TRIGGER quheadtrigger
BEFORE INSERT OR UPDATE OR DELETE
ON quhead
CREATE OR REPLACE FUNCTION _quitemtrigger() RETURNS "trigger" AS $$
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
_changelog BOOLEAN := FALSE;
DELETE FROM charass
WHERE ((charass_target_type='QI')
AND (charass_target_id=OLD.quitem_id));
-
+
RETURN OLD;
END IF;
END;
$$ LANGUAGE 'plpgsql';
-DROP TRIGGER quitemtrigger ON quitem;
+DROP TRIGGER IF EXISTS quitemtrigger ON quitem;
CREATE TRIGGER quitemtrigger
BEFORE INSERT OR UPDATE OR DELETE
ON quitem
EXECUTE PROCEDURE _quitemtrigger();
CREATE OR REPLACE FUNCTION _quitemBeforeTrigger() RETURNS TRIGGER AS $$
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
_check NUMERIC;
-- If this is imported, go ahead and insert default characteristics
IF ((TG_OP = 'INSERT') AND NEW.quitem_imported) THEN
- PERFORM updateCharAssignment('SI', NEW.quitem_id, char_id, charass_value)
+ PERFORM updateCharAssignment('SI', NEW.quitem_id, char_id, charass_value)
FROM (
SELECT DISTINCT char_id, char_name, charass_value
FROM charass, char, itemsite, item
WHERE ((itemsite_id=NEW.quitem_itemsite_id)
AND (itemsite_item_id=item_id)
- AND (charass_target_type='I')
+ AND (charass_target_type='I')
AND (charass_target_id=item_id)
AND (charass_default)
AND (char_id=charass_char_id))
END;
$$ LANGUAGE 'plpgsql';
-DROP TRIGGER quitemBeforeTrigger ON quitem;
+DROP TRIGGER IF EXISTS quitemBeforeTrigger ON quitem;
CREATE TRIGGER quitemBeforeTrigger
BEFORE INSERT OR UPDATE
ON quitem
CREATE OR REPLACE FUNCTION _quitemAfterTrigger() RETURNS TRIGGER AS $$
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
_check NUMERIC;
END;
$$ LANGUAGE 'plpgsql';
-DROP TRIGGER quitemAfterTrigger ON quitem;
+DROP TRIGGER IF EXISTS quitemAfterTrigger ON quitem;
CREATE TRIGGER quitemAfterTrigger
AFTER INSERT OR UPDATE
ON quitem
CREATE OR REPLACE FUNCTION _reportTrigger () RETURNS TRIGGER AS '
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
END;
' LANGUAGE 'plpgsql';
-DROP TRIGGER reportTrigger ON report;
+DROP TRIGGER IF EXISTS reportTrigger ON report;
CREATE TRIGGER reportTrigger BEFORE INSERT OR UPDATE ON report FOR EACH ROW EXECUTE PROCEDURE _reportTrigger();
CREATE OR REPLACE FUNCTION _shiptoinfoAfterTrigger () RETURNS TRIGGER AS $$
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
_cmnttypeid INTEGER;
END;
$$ LANGUAGE 'plpgsql';
-DROP TRIGGER shiptoinfoAfterTrigger ON shiptoinfo;
+DROP TRIGGER IF EXISTS shiptoinfoAfterTrigger ON shiptoinfo;
CREATE TRIGGER shiptoinfoAfterTrigger AFTER INSERT OR UPDATE ON shiptoinfo FOR EACH ROW EXECUTE PROCEDURE _shiptoinfoAfterTrigger();
CREATE OR REPLACE FUNCTION _warehousTrigger () RETURNS TRIGGER AS '
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
_cmnttypeid INTEGER;
IF (LENGTH(COALESCE(NEW.warehous_code,''''))=0) THEN
RAISE EXCEPTION ''You must supply a valid Site Code.'';
END IF;
-
+
-- Sitetype is required
IF (NEW.warehous_sitetype_id IS NULL) THEN
RAISE EXCEPTION ''You must supply a valid Site Type.'';
IF (FOUND) THEN
RAISE EXCEPTION ''You must supply a unique Site Code.'';
END IF;
-
+
-- Count Tag Prefix must be unique
IF (TG_OP = ''INSERT'') THEN
SELECT warehous_id INTO _checkId
IF (FOUND) THEN
RAISE EXCEPTION ''You must supply a unique Count Tag Prefix.'';
END IF;
-
+
-- Check Complete
-- Change Log
IF ( SELECT (metric_value=''t'')
END IF;
END IF;
END IF;
-
+
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
-DROP TRIGGER warehousTrigger ON whsinfo;
+DROP TRIGGER IF EXISTS warehousTrigger ON whsinfo;
CREATE TRIGGER warehousTrigger BEFORE INSERT OR UPDATE ON whsinfo FOR EACH ROW EXECUTE PROCEDURE _warehousTrigger();
CREATE OR REPLACE FUNCTION _woTrigger() RETURNS TRIGGER AS $$
--- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
+-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
IF (TG_OP = 'INSERT') THEN
PERFORM postEvent('WoCreated', 'W', NEW.wo_id,
itemsite_warehous_id,
- (NEW.wo_number || '-' || NEW.wo_subnumber),
+ (NEW.wo_number || '-' || NEW.wo_subnumber),
NULL, NULL, NULL, NULL)
FROM itemsite
WHERE (itemsite_id=NEW.wo_itemsite_id)
IF (TG_OP = 'DELETE') THEN
PERFORM postEvent('WoCancelled', 'W', OLD.wo_id,
itemsite_warehous_id,
- (OLD.wo_number || '-' || OLD.wo_subnumber),
+ (OLD.wo_number || '-' || OLD.wo_subnumber),
NULL, NULL, NULL, NULL)
FROM itemsite
WHERE (itemsite_id=OLD.wo_itemsite_id)
IF (NEW.wo_qtyord <> OLD.wo_qtyord) THEN
PERFORM postEvent('WoQtyChanged', 'W', NEW.wo_id,
itemsite_warehous_id,
- (NEW.wo_number || '-' || NEW.wo_subnumber),
+ (NEW.wo_number || '-' || NEW.wo_subnumber),
NEW.wo_qtyord, OLD.wo_qtyord, NULL, NULL)
FROM itemsite
WHERE (itemsite_id=NEW.wo_itemsite_id)
IF (NEW.wo_duedate <> OLD.wo_duedate) THEN
PERFORM postEvent('WoDueDateChanged', 'W', NEW.wo_id,
itemsite_warehous_id,
- (NEW.wo_number || '-' || NEW.wo_subnumber),
+ (NEW.wo_number || '-' || NEW.wo_subnumber),
NULL, NULL, NEW.wo_duedate, OLD.wo_duedate)
FROM itemsite
WHERE (itemsite_id=NEW.wo_itemsite_id)
('Status Changed from ' || OLD.wo_status || ' to ' || NEW.wo_status) );
END IF;
- END IF;
+ END IF;
END IF;
END IF;
END;
$$ LANGUAGE 'plpgsql';
-DROP TRIGGER woTrigger ON wo;
+DROP TRIGGER IF EXISTS woTrigger ON wo;
CREATE TRIGGER woTrigger BEFORE INSERT OR UPDATE OR DELETE ON wo FOR EACH ROW EXECUTE PROCEDURE _woTrigger();
projects / xtuple / commitdiff