From 3d7ac7186afeb549bb211ab82af3327afe5093bf Mon Sep 17 00:00:00 2001 From: Greg Pazo Date: Thu, 17 Apr 2014 18:31:26 -0400 Subject: [PATCH] let's get build_app running on a masterref database --- .../public/functions/changeprdate.sql | 3 +- .../public/trigger_functions/addr.sql | 2 +- .../public/trigger_functions/aropen.sql | 16 +-- .../public/trigger_functions/bomhead.sql | 6 +- .../public/trigger_functions/bomitemsub.sql | 4 +- .../public/trigger_functions/ccard.sql | 4 +- .../public/trigger_functions/cntslip.sql | 12 +-- .../public/trigger_functions/cohead.sql | 102 +++++++++--------- .../public/trigger_functions/comment.sql | 4 +- .../public/trigger_functions/crmacct.sql | 8 +- .../public/trigger_functions/curr_rate.sql | 4 +- .../public/trigger_functions/curr_symbol.sql | 4 +- .../public/trigger_functions/item.sql | 14 +-- .../public/trigger_functions/itemcost.sql | 50 ++++----- .../public/trigger_functions/itemsub.sql | 6 +- .../public/trigger_functions/itemtax.sql | 6 +- .../public/trigger_functions/itemuomconv.sql | 6 +- .../public/trigger_functions/pack.sql | 4 +- .../public/trigger_functions/pohead.sql | 10 +- .../public/trigger_functions/quhead.sql | 42 ++++---- .../public/trigger_functions/quitem.sql | 18 ++-- .../public/trigger_functions/report.sql | 4 +- .../public/trigger_functions/shiptoinfo.sql | 4 +- .../public/trigger_functions/whsinfo.sql | 12 +-- .../public/trigger_functions/wo.sql | 14 +-- 25 files changed, 180 insertions(+), 179 deletions(-) diff --git a/foundation-database/public/functions/changeprdate.sql b/foundation-database/public/functions/changeprdate.sql index 441be59fb..4954dae1a 100644 --- a/foundation-database/public/functions/changeprdate.sql +++ b/foundation-database/public/functions/changeprdate.sql @@ -1,5 +1,6 @@ +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; diff --git a/foundation-database/public/trigger_functions/addr.sql b/foundation-database/public/trigger_functions/addr.sql index 3a8fb0d3b..342da67a6 100644 --- a/foundation-database/public/trigger_functions/addr.sql +++ b/foundation-database/public/trigger_functions/addr.sql @@ -38,7 +38,7 @@ CREATE OR REPLACE FUNCTION _addrtrigger() RETURNS "trigger" AS $$ END; $$ LANGUAGE 'plpgsql'; -DROP TRIGGER addrtrigger ON addr; +DROP TRIGGER IF EXISTS addrtrigger ON addr; CREATE TRIGGER addrtrigger BEFORE INSERT OR UPDATE OR DELETE diff --git a/foundation-database/public/trigger_functions/aropen.sql b/foundation-database/public/trigger_functions/aropen.sql index fbe9a02f5..1623197d3 100644 --- a/foundation-database/public/trigger_functions/aropen.sql +++ b/foundation-database/public/trigger_functions/aropen.sql @@ -1,5 +1,5 @@ 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; @@ -94,7 +94,7 @@ BEGIN 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; @@ -112,15 +112,15 @@ BEGIN 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; @@ -205,11 +205,11 @@ END; $$ 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; diff --git a/foundation-database/public/trigger_functions/bomhead.sql b/foundation-database/public/trigger_functions/bomhead.sql index 2ee74ef3d..def3822bb 100644 --- a/foundation-database/public/trigger_functions/bomhead.sql +++ b/foundation-database/public/trigger_functions/bomhead.sql @@ -1,5 +1,5 @@ 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; @@ -9,10 +9,10 @@ BEGIN 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(); diff --git a/foundation-database/public/trigger_functions/bomitemsub.sql b/foundation-database/public/trigger_functions/bomitemsub.sql index 3a093d94d..eca6c924c 100644 --- a/foundation-database/public/trigger_functions/bomitemsub.sql +++ b/foundation-database/public/trigger_functions/bomitemsub.sql @@ -1,5 +1,5 @@ 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 @@ -17,5 +17,5 @@ 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(); diff --git a/foundation-database/public/trigger_functions/ccard.sql b/foundation-database/public/trigger_functions/ccard.sql index 83d21e497..a5a2c5f62 100644 --- a/foundation-database/public/trigger_functions/ccard.sql +++ b/foundation-database/public/trigger_functions/ccard.sql @@ -1,5 +1,5 @@ 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 @@ -39,5 +39,5 @@ END; ' 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(); diff --git a/foundation-database/public/trigger_functions/cntslip.sql b/foundation-database/public/trigger_functions/cntslip.sql index 70aff6764..ef4642986 100644 --- a/foundation-database/public/trigger_functions/cntslip.sql +++ b/foundation-database/public/trigger_functions/cntslip.sql @@ -1,5 +1,5 @@ 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; @@ -27,19 +27,19 @@ Count Slip #'' || _p.cntslip_number || 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) @@ -57,5 +57,5 @@ Count Slip #'' || _p.cntslip_number || 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(); diff --git a/foundation-database/public/trigger_functions/cohead.sql b/foundation-database/public/trigger_functions/cohead.sql index c9848d779..2212ca36a 100644 --- a/foundation-database/public/trigger_functions/cohead.sql +++ b/foundation-database/public/trigger_functions/cohead.sql @@ -1,6 +1,6 @@ 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; @@ -96,12 +96,12 @@ BEGIN 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'; @@ -110,12 +110,12 @@ BEGIN 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'; @@ -175,13 +175,13 @@ BEGIN 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 @@ -191,7 +191,7 @@ BEGIN 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 @@ -207,7 +207,7 @@ BEGIN 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 @@ -243,7 +243,7 @@ BEGIN 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 @@ -251,8 +251,8 @@ BEGIN --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) @@ -273,36 +273,36 @@ BEGIN 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,''); @@ -310,9 +310,9 @@ BEGIN 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) @@ -337,7 +337,7 @@ BEGIN _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) @@ -356,8 +356,8 @@ BEGIN 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,''); @@ -385,7 +385,7 @@ BEGIN 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) @@ -395,21 +395,21 @@ BEGIN 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.'; @@ -460,7 +460,7 @@ BEGIN 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) ); @@ -484,7 +484,7 @@ BEGIN 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 @@ -508,16 +508,16 @@ BEGIN 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; diff --git a/foundation-database/public/trigger_functions/comment.sql b/foundation-database/public/trigger_functions/comment.sql index 008de9f9f..ad4227d8e 100644 --- a/foundation-database/public/trigger_functions/comment.sql +++ b/foundation-database/public/trigger_functions/comment.sql @@ -1,5 +1,5 @@ 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 @@ -12,5 +12,5 @@ BEGIN 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(); diff --git a/foundation-database/public/trigger_functions/crmacct.sql b/foundation-database/public/trigger_functions/crmacct.sql index 619ba4ea9..b73aa3d8e 100644 --- a/foundation-database/public/trigger_functions/crmacct.sql +++ b/foundation-database/public/trigger_functions/crmacct.sql @@ -1,6 +1,6 @@ -- 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; @@ -58,12 +58,12 @@ BEGIN 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; @@ -255,5 +255,5 @@ BEGIN 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(); diff --git a/foundation-database/public/trigger_functions/curr_rate.sql b/foundation-database/public/trigger_functions/curr_rate.sql index 624d03e74..01f52401a 100644 --- a/foundation-database/public/trigger_functions/curr_rate.sql +++ b/foundation-database/public/trigger_functions/curr_rate.sql @@ -1,5 +1,5 @@ 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; @@ -40,6 +40,6 @@ BEGIN 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(); diff --git a/foundation-database/public/trigger_functions/curr_symbol.sql b/foundation-database/public/trigger_functions/curr_symbol.sql index 3cbf0093a..dfc91233f 100644 --- a/foundation-database/public/trigger_functions/curr_symbol.sql +++ b/foundation-database/public/trigger_functions/curr_symbol.sql @@ -1,5 +1,5 @@ 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; @@ -32,6 +32,6 @@ BEGIN 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(); diff --git a/foundation-database/public/trigger_functions/item.sql b/foundation-database/public/trigger_functions/item.sql index ea2f0d28f..dbc7629ac 100644 --- a/foundation-database/public/trigger_functions/item.sql +++ b/foundation-database/public/trigger_functions/item.sql @@ -1,8 +1,8 @@ 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; @@ -28,11 +28,11 @@ BEGIN 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; @@ -42,7 +42,7 @@ BEGIN 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 @@ -245,11 +245,11 @@ BEGIN 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(); diff --git a/foundation-database/public/trigger_functions/itemcost.sql b/foundation-database/public/trigger_functions/itemcost.sql index 9d762afe5..bcbc3e50c 100644 --- a/foundation-database/public/trigger_functions/itemcost.sql +++ b/foundation-database/public/trigger_functions/itemcost.sql @@ -1,5 +1,5 @@ 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 @@ -78,13 +78,13 @@ 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; @@ -93,53 +93,53 @@ DECLARE _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, @@ -150,8 +150,8 @@ END IF; 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) @@ -160,10 +160,10 @@ END IF; 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, @@ -178,5 +178,5 @@ END IF; 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(); diff --git a/foundation-database/public/trigger_functions/itemsub.sql b/foundation-database/public/trigger_functions/itemsub.sql index 1fe636125..7327b66f8 100644 --- a/foundation-database/public/trigger_functions/itemsub.sql +++ b/foundation-database/public/trigger_functions/itemsub.sql @@ -1,5 +1,5 @@ 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 @@ -7,10 +7,10 @@ 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(); diff --git a/foundation-database/public/trigger_functions/itemtax.sql b/foundation-database/public/trigger_functions/itemtax.sql index 264d57529..ca0362950 100644 --- a/foundation-database/public/trigger_functions/itemtax.sql +++ b/foundation-database/public/trigger_functions/itemtax.sql @@ -1,5 +1,5 @@ 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 @@ -7,10 +7,10 @@ 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(); diff --git a/foundation-database/public/trigger_functions/itemuomconv.sql b/foundation-database/public/trigger_functions/itemuomconv.sql index 23505109e..2750f6ced 100644 --- a/foundation-database/public/trigger_functions/itemuomconv.sql +++ b/foundation-database/public/trigger_functions/itemuomconv.sql @@ -1,5 +1,5 @@ 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 @@ -7,10 +7,10 @@ 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(); diff --git a/foundation-database/public/trigger_functions/pack.sql b/foundation-database/public/trigger_functions/pack.sql index f6d63977c..5fe0119b3 100644 --- a/foundation-database/public/trigger_functions/pack.sql +++ b/foundation-database/public/trigger_functions/pack.sql @@ -1,6 +1,6 @@ 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; @@ -46,5 +46,5 @@ BEGIN 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(); diff --git a/foundation-database/public/trigger_functions/pohead.sql b/foundation-database/public/trigger_functions/pohead.sql index 378643747..dc5b4868d 100644 --- a/foundation-database/public/trigger_functions/pohead.sql +++ b/foundation-database/public/trigger_functions/pohead.sql @@ -1,5 +1,5 @@ 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; @@ -63,7 +63,7 @@ BEGIN 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) ); @@ -80,16 +80,16 @@ BEGIN 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; diff --git a/foundation-database/public/trigger_functions/quhead.sql b/foundation-database/public/trigger_functions/quhead.sql index 65bd09ab2..b87eea614 100644 --- a/foundation-database/public/trigger_functions/quhead.sql +++ b/foundation-database/public/trigger_functions/quhead.sql @@ -1,5 +1,5 @@ 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; @@ -42,7 +42,7 @@ BEGIN 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); @@ -50,7 +50,7 @@ BEGIN --- 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 @@ -135,14 +135,14 @@ BEGIN 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; @@ -175,18 +175,18 @@ BEGIN 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,''); @@ -194,9 +194,9 @@ BEGIN 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) @@ -211,16 +211,16 @@ BEGIN _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,''); @@ -248,7 +248,7 @@ BEGIN 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) @@ -259,11 +259,11 @@ BEGIN 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.'; @@ -313,7 +313,7 @@ BEGIN 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 diff --git a/foundation-database/public/trigger_functions/quitem.sql b/foundation-database/public/trigger_functions/quitem.sql index 0f149bd50..1b5af5cdc 100644 --- a/foundation-database/public/trigger_functions/quitem.sql +++ b/foundation-database/public/trigger_functions/quitem.sql @@ -1,5 +1,5 @@ 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; @@ -32,7 +32,7 @@ BEGIN DELETE FROM charass WHERE ((charass_target_type='QI') AND (charass_target_id=OLD.quitem_id)); - + RETURN OLD; END IF; @@ -65,7 +65,7 @@ BEGIN 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 @@ -73,7 +73,7 @@ CREATE TRIGGER quitemtrigger 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; @@ -86,13 +86,13 @@ BEGIN -- 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)) @@ -103,7 +103,7 @@ BEGIN END; $$ LANGUAGE 'plpgsql'; -DROP TRIGGER quitemBeforeTrigger ON quitem; +DROP TRIGGER IF EXISTS quitemBeforeTrigger ON quitem; CREATE TRIGGER quitemBeforeTrigger BEFORE INSERT OR UPDATE ON quitem @@ -113,7 +113,7 @@ CREATE TRIGGER quitemBeforeTrigger 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; @@ -136,7 +136,7 @@ BEGIN END; $$ LANGUAGE 'plpgsql'; -DROP TRIGGER quitemAfterTrigger ON quitem; +DROP TRIGGER IF EXISTS quitemAfterTrigger ON quitem; CREATE TRIGGER quitemAfterTrigger AFTER INSERT OR UPDATE ON quitem diff --git a/foundation-database/public/trigger_functions/report.sql b/foundation-database/public/trigger_functions/report.sql index 597996fbe..36d83f7df 100644 --- a/foundation-database/public/trigger_functions/report.sql +++ b/foundation-database/public/trigger_functions/report.sql @@ -1,5 +1,5 @@ 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 @@ -9,5 +9,5 @@ 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(); diff --git a/foundation-database/public/trigger_functions/shiptoinfo.sql b/foundation-database/public/trigger_functions/shiptoinfo.sql index 82142c3dd..f95ef4acf 100644 --- a/foundation-database/public/trigger_functions/shiptoinfo.sql +++ b/foundation-database/public/trigger_functions/shiptoinfo.sql @@ -1,5 +1,5 @@ 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; @@ -74,6 +74,6 @@ BEGIN 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(); diff --git a/foundation-database/public/trigger_functions/whsinfo.sql b/foundation-database/public/trigger_functions/whsinfo.sql index cc668aa11..598a578be 100644 --- a/foundation-database/public/trigger_functions/whsinfo.sql +++ b/foundation-database/public/trigger_functions/whsinfo.sql @@ -1,5 +1,5 @@ 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; @@ -26,7 +26,7 @@ BEGIN 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.''; @@ -45,7 +45,7 @@ BEGIN 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 @@ -60,7 +60,7 @@ BEGIN IF (FOUND) THEN RAISE EXCEPTION ''You must supply a unique Count Tag Prefix.''; END IF; - + -- Check Complete -- Change Log IF ( SELECT (metric_value=''t'') @@ -98,11 +98,11 @@ BEGIN 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(); diff --git a/foundation-database/public/trigger_functions/wo.sql b/foundation-database/public/trigger_functions/wo.sql index 5674716cc..b376e2af5 100644 --- a/foundation-database/public/trigger_functions/wo.sql +++ b/foundation-database/public/trigger_functions/wo.sql @@ -1,5 +1,5 @@ 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 @@ -8,7 +8,7 @@ BEGIN 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) @@ -27,7 +27,7 @@ BEGIN 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) @@ -52,7 +52,7 @@ BEGIN 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) @@ -67,7 +67,7 @@ BEGIN 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) @@ -84,7 +84,7 @@ BEGIN ('Status Changed from ' || OLD.wo_status || ' to ' || NEW.wo_status) ); END IF; - END IF; + END IF; END IF; END IF; @@ -101,5 +101,5 @@ BEGIN 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(); -- 2.39.2