-- Name: _opheadbeforetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _opheadbeforetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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
  _rec record;
  _check boolean;
  _test text;
BEGIN

  IF(TG_OP = 'DELETE') THEN
    _rec := OLD;
  ELSE
    _rec := NEW;
  END IF;

  --  Auto inactivate
  IF (TG_OP = 'UPDATE') THEN
    IF ( (NEW.ophead_opstage_id != OLD.ophead_opstage_id) AND
         (SELECT opstage_opinactive FROM opstage WHERE opstage_id=NEW.ophead_opstage_id) ) THEN
      NEW.ophead_active := FALSE;
    END IF;
  END IF;

  IF (TG_OP = 'INSERT') THEN
    IF (SELECT opstage_opinactive FROM opstage WHERE opstage_id=NEW.ophead_opstage_id) THEN
      NEW.ophead_active := FALSE;
    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._opheadbeforetrigger() OWNER TO admin;

--
-- Name: _packbeforetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _packbeforetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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;
BEGIN
  SELECT cmnttype_id INTO _cmnttypeid
    FROM cmnttype
    WHERE (cmnttype_name='ChangeLog');
  IF (FOUND) THEN
    IF ((TG_OP = 'INSERT') AND (NEW.pack_head_id) IS NOT NULL)THEN
      PERFORM postComment(_cmnttypeid, 'S', NEW.pack_head_id, 'Added to Packing List Batch');
    END IF;
  END IF;
  IF ((TG_OP = 'INSERT') OR (TG_OP = 'UPDATE')) THEN
    IF (NEW.pack_shiphead_id IS NOT NULL
	 AND NEW.pack_shiphead_id NOT IN (SELECT shiphead_id
			       FROM shiphead
			       WHERE (shiphead_order_id=NEW.pack_head_id)
				 AND (shiphead_order_type=NEW.pack_head_type))) THEN
      RAISE EXCEPTION 'Shipment does not exist for % id %',
		      NEW.pack_head_type, NEW.pack_head_id;
      RETURN OLD;
    END IF;

    IF (NEW.pack_head_type = 'SO'
	AND NEW.pack_head_id   IN (SELECT cohead_id FROM cohead)) THEN
      RETURN NEW;

    ELSEIF (NEW.pack_head_type = 'TO') THEN
      IF (NOT fetchMetricBool('MultiWhs')) THEN
	RAISE EXCEPTION 'Transfer Orders are not supported by this version of the application';
      ELSEIF (NEW.pack_head_id IN (SELECT tohead_id FROM tohead)) THEN
	RETURN NEW;
      END IF;
    END IF;

    RAISE EXCEPTION '% with id % does not exist',
		    NEW.pack_head_type, NEW.pack_head_id;
    RETURN OLD;

  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._packbeforetrigger() OWNER TO admin;

--
-- Name: _periodaftertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _periodaftertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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
  _idoffirst INTEGER;
  _test      INTEGER;
BEGIN
  -- This trigger can easily cause an infinite loop
  -- because of this we have to be very careful to not
  -- do an update on the period table if no updates
  -- are absolutely needed so we don't just keep
  -- trigger ourselves again and again

  -- Figure out which period is the first one
  SELECT period_id
    INTO _idoffirst
    FROM period
   ORDER BY period_start
   LIMIT 1;

  -- If we didn't find anything there is nothing to do
  IF( NOT FOUND ) THEN
    RETURN NEW;
  END IF;

  -- do a select to see if there is at least one record that needs to be
  -- updated. If we do not find any then we can just leave without
  -- causing a retrigger of ourselves
  SELECT period_id
    INTO _test
    FROM period
   WHERE((COALESCE(period_initial, true) AND (NOT period_id=_idoffirst))
      OR ((NOT COALESCE(period_initial, false)) AND (period_id=_idoffirst)))
   LIMIT 1;

  -- Nothing to update - get out of here
  IF( NOT FOUND ) THEN
    RETURN NEW;
  END IF;

  -- Update all the period records that already have the initial flag
  -- set and the one that we know should be the first.
  -- We don't have to be as careful here since we have already ruled
  -- out if don't need to update already.
  UPDATE period
     SET period_initial = (_idoffirst=period_id)
   WHERE((COALESCE(period_initial, true))
      OR (period_id=_idoffirst));

  RETURN NEW;

END;
$$;


ALTER FUNCTION public._periodaftertrigger() OWNER TO admin;

--
-- Name: _pkgcmdaftertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _pkgcmdaftertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 (TG_OP = 'DELETE') THEN
    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._pkgcmdaftertrigger() OWNER TO admin;

--
-- Name: _pkgcmdaltertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _pkgcmdaltertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 (pkgMayBeModified(TG_TABLE_SCHEMA) OR isDba()) THEN
    IF (TG_OP = 'DELETE') THEN
      RETURN OLD;
    ELSE
      RETURN NEW;
    END IF;
  END IF;

  IF (TG_OP = 'INSERT') THEN
    RAISE EXCEPTION 'You may not create custom commands in packages except using the xTuple Updater utility';

  ELSIF (TG_OP = 'UPDATE') THEN
    RAISE EXCEPTION 'You may not alter custom commands in packages except using the xTuple Updater utility';

  ELSIF (TG_OP = 'DELETE') THEN
    RAISE EXCEPTION 'You may not delete custom commands from packages. Try deleting or disabling the package.';

  END IF;

  RETURN NEW;
END;

$$;


ALTER FUNCTION public._pkgcmdaltertrigger() OWNER TO admin;

--
-- Name: _pkgcmdargaftertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _pkgcmdargaftertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 (TG_OP = 'DELETE') THEN
    RETURN OLD;
  END IF;
  RETURN NEW;
END;
$$;


ALTER FUNCTION public._pkgcmdargaftertrigger() OWNER TO admin;

--
-- Name: _pkgcmdargaltertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _pkgcmdargaltertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 (pkgMayBeModified(TG_TABLE_SCHEMA) OR isDba()) THEN
    IF (TG_OP = 'DELETE') THEN
      RETURN OLD;
    ELSE
      RETURN NEW;
    END IF;
  END IF;

  IF (TG_OP = 'INSERT') THEN
    RAISE EXCEPTION 'You may not create command arguments in packages except using the xTuple Updater utility';

  ELSIF (TG_OP = 'UPDATE') THEN
    RAISE EXCEPTION 'You may not alter command arguments in packages except using the xTuple Updater utility';

  ELSIF (TG_OP = 'DELETE') THEN
    RAISE EXCEPTION 'You may not delete command arguments from packages. Try deleting or disabling the package.';

  END IF;

  RETURN NEW;
END;

$$;


ALTER FUNCTION public._pkgcmdargaltertrigger() OWNER TO admin;

--
-- Name: _pkgcmdargbeforetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _pkgcmdargbeforetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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
  _cmdargid     INTEGER;
  _debug        BOOL := false;

BEGIN
  IF (TG_OP = 'UPDATE') THEN
    RETURN NEW;

  ELSIF (TG_OP = 'INSERT') THEN
    RETURN NEW;

  ELSIF (TG_OP = 'DELETE') THEN
    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._pkgcmdargbeforetrigger() OWNER TO admin;

--
-- Name: _pkgcmdbeforetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _pkgcmdbeforetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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
  _cmdid       INTEGER;
  _debug        BOOL := false;

BEGIN
    IF (TG_OP = 'UPDATE') THEN
      IF (_debug) THEN
        RAISE NOTICE 'OLD.cmd_name %, NEW.cmd_name %',
                     OLD.cmd_name, NEW.cmd_name;
      END IF;

      IF (NEW.cmd_name != OLD.cmd_name) THEN
        SELECT cmd_id INTO _cmdid FROM cmd WHERE cmd_name=NEW.cmd_name;
        IF (FOUND) THEN
          RAISE EXCEPTION 'Cannot change command name % because another command with that name already exists.', NEW.cmd_name;
        END IF;
      END IF;

    ELSIF (TG_OP = 'INSERT') THEN
      IF (_debug) THEN
        RAISE NOTICE 'inserting NEW.cmd_name %', NEW.cmd_name;
      END IF;
      SELECT cmd_id INTO _cmdid FROM cmd WHERE cmd_name=NEW.cmd_name;
      IF (FOUND) THEN
        RAISE EXCEPTION 'Cannot create new command % because another command with that name already exists.', NEW.cmd_name;
      END IF;

    ELSIF (TG_OP = 'DELETE') THEN
      DELETE FROM cmdarg WHERE cmdarg_cmd_id=OLD.cmd_id;

      RETURN OLD;
    END IF;

    RETURN NEW;
  END;
$$;


ALTER FUNCTION public._pkgcmdbeforetrigger() OWNER TO admin;

--
-- Name: _pkgheadbeforetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _pkgheadbeforetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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
    _r    RECORD;

  BEGIN
    IF (TG_OP = 'UPDATE') THEN
      NEW.pkghead_created := OLD.pkghead_created;
      NEW.pkghead_updated := CURRENT_TIMESTAMP;
      IF (NEW.pkghead_indev AND NOT userCanCreateUsers(getEffectiveXtUser())) THEN
        NEW.pkghead_indev = FALSE;
      END IF;

    ELSIF (TG_OP = 'INSERT') THEN
      NEW.pkghead_created := CURRENT_TIMESTAMP;
      NEW.pkghead_updated := NEW.pkghead_created;
      IF (NEW.pkghead_indev AND NOT userCanCreateUsers(getEffectiveXtUser())) THEN
        NEW.pkghead_indev = FALSE;
      END IF;

    ELSIF (TG_OP = 'DELETE') THEN
      DELETE FROM pkgdep WHERE pkgdep_pkghead_id=OLD.pkghead_id;

      EXECUTE 'DROP SCHEMA ' || OLD.pkghead_name || ' CASCADE';

      RETURN OLD;
    END IF;

    RETURN NEW;
  END;
$$;


ALTER FUNCTION public._pkgheadbeforetrigger() OWNER TO admin;

--
-- Name: _pkgimageaftertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _pkgimageaftertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 (TG_OP = 'DELETE') THEN
    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._pkgimageaftertrigger() OWNER TO admin;

--
-- Name: _pkgimagealtertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _pkgimagealtertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 (pkgMayBeModified(TG_TABLE_SCHEMA) OR isDba()) THEN
    IF (TG_OP = 'DELETE') THEN
      RETURN OLD;
    ELSE
      RETURN NEW;
    END IF;
  END IF;

  IF (TG_OP = 'INSERT') THEN
    RAISE EXCEPTION 'You may not create images in packages except using the xTuple Updater utility';

  ELSIF (TG_OP = 'UPDATE') THEN
    RAISE EXCEPTION 'You may not alter images in packages except using the xTuple Updater utility';

  ELSIF (TG_OP = 'DELETE') THEN
    RAISE EXCEPTION 'You may not delete images from packages. Try deleting or disabling the package.';

  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._pkgimagealtertrigger() OWNER TO admin;

--
-- Name: _pkgimagebeforetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _pkgimagebeforetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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
  _imageid     INTEGER;
  _debug       BOOL := false;

BEGIN
  IF (TG_OP = 'UPDATE') THEN
    IF (_debug) THEN
      RAISE NOTICE 'OLD.image_name %, NEW.image_name %',
                   OLD.image_name, NEW.image_name;
    END IF;

    IF (NEW.image_name != OLD.image_name) THEN
      SELECT image_id INTO _imageid FROM image WHERE image_name=NEW.image_name;
      IF (FOUND) THEN
        RAISE EXCEPTION 'Cannot change image named % because another image with that name already exists.', NEW.image_name;
      END IF;
    END IF;

  ELSIF (TG_OP = 'INSERT') THEN
    IF (_debug) THEN
      RAISE NOTICE 'inserting NEW.image_name %', NEW.image_name;
    END IF;
    SELECT image_id INTO _imageid FROM image WHERE image_name=NEW.image_name;
    IF (FOUND) THEN
      RAISE EXCEPTION 'Cannot create new image % because another image with that name already exists.', NEW.image_name;
    END IF;

  ELSIF (TG_OP = 'DELETE') THEN
    RETURN OLD;

  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._pkgimagebeforetrigger() OWNER TO admin;

--
-- Name: _pkgitembeforetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _pkgitembeforetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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
    _functionargs TEXT;
    _group        TEXT;
    _object       TEXT;
    _schema       TEXT;
    _debug        BOOL := false;
  BEGIN
    IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
      _object = NEW.pkgitem_name;

      SELECT LOWER(pkghead_name) INTO _schema
      FROM pkghead
      WHERE (pkghead_id=NEW.pkgitem_pkghead_id);
      IF (NOT FOUND) THEN
        _schema := 'public';
      END IF;

      IF (NEW.pkgitem_type = 'F') THEN
        _object := SPLIT_PART(_object, '(', 1);
      ELSIF (NEW.pkgitem_type = 'M') THEN
        _group  := SPLIT_PART(_object, '-', 1);
        _object := SPLIT_PART(_object, '-', 2);
      END IF;
      IF _debug THEN
        RAISE NOTICE '_schema % and _object %', _schema, _object;
      END IF;

      IF (NEW.pkgitem_type = 'C') THEN
        IF (NOT EXISTS(SELECT script_id
                       FROM script
                       WHERE ((script_id=NEW.pkgitem_item_id)
                          AND (script_name=NEW.pkgitem_name)))) THEN
          RAISE EXCEPTION 'Cannot create Script % as a Package Item without a corresponding script record.',
            NEW.pkgitem_name;
        END IF;

      ELSIF (NEW.pkgitem_type = 'D') THEN
        IF (NOT EXISTS(SELECT cmd_id
                       FROM cmd
                       WHERE ((cmd_id=NEW.pkgitem_item_id)
                          AND (cmd_name=NEW.pkgitem_name)))) THEN
          RAISE EXCEPTION 'Cannot create Custom Command % as a Package Item without a corresponding cmd record.',
            NEW.pkgitem_name;
        END IF;

      ELSIF (NEW.pkgitem_type = 'F') THEN
        IF (NOT EXISTS(SELECT pg_proc.oid
                       FROM pg_proc, pg_namespace
                       WHERE ((pg_proc.oid=NEW.pkgitem_item_id)
                          AND (proname = (_object))
                          AND (pronamespace=pg_namespace.oid)
                          AND (nspname=_schema)) )) THEN
          RAISE EXCEPTION 'Cannot create Function % (oid %) as a Package Item without a corresponding function in the database.',
                          NEW.pkgitem_name, NEW.pkgitem_item_id;
        END IF;

      ELSIF (NEW.pkgitem_type = 'G') THEN
        IF (NOT EXISTS(SELECT pg_class.oid
                     FROM pg_trigger, pg_class, pg_namespace
                     WHERE ((tgname=_object)
                        AND (tgrelid=pg_class.oid)
                        AND (relnamespace=pg_namespace.oid)
                        AND (nspname=_schema)))) THEN
          RAISE EXCEPTION 'Cannot create Trigger % as a Package Item without a corresponding trigger in the database.',
            NEW.pkgitem_name;
        END IF;

      ELSIF (NEW.pkgitem_type = 'I') THEN
        IF (NOT EXISTS(SELECT image_id
                       FROM image
                       WHERE ((image_id=NEW.pkgitem_item_id)
                          AND (image_name=NEW.pkgitem_name)))) THEN
          RAISE EXCEPTION 'Cannot create Image % as a Package Item without a corresponding image record.',
            NEW.pkgitem_name;
        END IF;

      ELSIF (NEW.pkgitem_type = 'M') THEN
        IF (NOT EXISTS(SELECT metasql_id
                       FROM metasql
                       WHERE ((metasql_id=NEW.pkgitem_item_id)
                          AND (metasql_group=_group)
                          AND (metasql_name=_object)))) THEN
          RAISE EXCEPTION 'Cannot create MetaSQL statement % as a Package Item without a corresponding metasql record.',
            NEW.pkgitem_name;
        END IF;

      ELSIF (NEW.pkgitem_type = 'P') THEN
        IF (NOT EXISTS(SELECT priv_id
                       FROM priv
                       WHERE ((priv_id=NEW.pkgitem_item_id)
                          AND (priv_name=NEW.pkgitem_name)))) THEN
          RAISE EXCEPTION 'Cannot create Privilege % as a Package Item without a corresponding priv record.',
            NEW.pkgitem_name;
        END IF;

      ELSIF (NEW.pkgitem_type = 'R') THEN
        IF (NOT EXISTS(SELECT report_id
                       FROM report
                       WHERE ((report_id=NEW.pkgitem_item_id)
                          AND (report_name=NEW.pkgitem_name)))) THEN
          RAISE EXCEPTION 'Cannot create Report % as a Package Item without a corresponding report record.',
            NEW.pkgitem_name;
        END IF;

      ELSIF (NEW.pkgitem_type = 'S') THEN
        IF (NOT EXISTS(SELECT oid
                       FROM pg_namespace
                       WHERE (LOWER(nspname)=LOWER(NEW.pkgitem_name)))) THEN
          RAISE EXCEPTION 'Cannot create Schema % as a Package Item without a corresponding schema in the database.',
            NEW.pkgitem_name;
        END IF;

      ELSIF (NEW.pkgitem_type = 'T') THEN
        IF (NOT EXISTS(SELECT pg_class.oid
                     FROM pg_class, pg_namespace
                     WHERE ((relname=_object)
                        AND (relnamespace=pg_namespace.oid)
                        AND (relkind='r')
                        AND (nspname=_schema)))) THEN
          RAISE EXCEPTION 'Cannot create Table % as a Package Item without a corresponding table in the database.',
            NEW.pkgitem_name;
        END IF;

      ELSIF (NEW.pkgitem_type = 'U') THEN
        IF (NOT EXISTS(SELECT uiform_id
                       FROM uiform
                       WHERE ((uiform_id=NEW.pkgitem_item_id)
                          AND (uiform_name=NEW.pkgitem_name)))) THEN
          RAISE EXCEPTION 'Cannot create User Interface Form % as a Package Item without a corresponding uiform record.',
            NEW.pkgitem_name;
        END IF;

      ELSIF (NEW.pkgitem_type = 'V') THEN
        IF (NOT EXISTS(SELECT pg_class.oid
                     FROM pg_class, pg_namespace
                     WHERE ((relname=_object)
                        AND (relnamespace=pg_namespace.oid)
                        AND (relkind='v')
                        AND (nspname=_schema)))) THEN
          RAISE EXCEPTION 'Cannot create View % as a Package Item without a corresponding view in the database.',
            NEW.pkgitem_name;
        END IF;

      ELSE
        RAISE EXCEPTION '"%" is not a valid type of package item.',
          NEW.pkgitem_type;
      END IF;

    ELSIF (TG_OP = 'DELETE') THEN
      IF _debug THEN RAISE NOTICE 'Deleting % % %', OLD.pkgitem_item_id, OLD.pkgitem_name, OLD.pkgitem_type; END IF;

      _object = OLD.pkgitem_name;

      SELECT pkghead_name INTO _schema
      FROM pkghead
      WHERE (pkghead_id=OLD.pkgitem_pkghead_id);
      IF (NOT FOUND) THEN
        _schema := 'public';
      END IF;

      IF (OLD.pkgitem_type = 'F') THEN
        _object := SPLIT_PART(_object, '(', 1);
      ELSIF (OLD.pkgitem_type = 'M') THEN
        _group  := SPLIT_PART(_object, '-', 1);
        _object := SPLIT_PART(_object, '-', 2);
      END IF;
      IF _debug THEN
        RAISE NOTICE '_schema % and _object %', _schema, _object;
      END IF;

      IF (OLD.pkgitem_type = 'C') THEN
        DELETE FROM script WHERE ((script_id=OLD.pkgitem_item_id)
                              AND (script_name=OLD.pkgitem_name));

      ELSIF (OLD.pkgitem_type = 'D') THEN
        DELETE FROM cmd
          WHERE ((cmd_id=OLD.pkgitem_item_id)
            AND  (cmd_name=OLD.pkgitem_name));

      ELSIF (OLD.pkgitem_type = 'F') THEN
        -- SELECT dropIfExists('FUNCTION', CAST (oid::regprocedure AS TEXT), _schema)
        PERFORM dropIfExists('FUNCTION',
                            proname || '(' ||
                            oidvectortypes(proargtypes) || ')',
                            _schema)
        FROM pg_proc
        WHERE (oid=OLD.pkgitem_item_id);

      ELSIF (OLD.pkgitem_type = 'G') THEN
        PERFORM dropIfExists('TRIGGER', _object, _schema);

      ELSIF (OLD.pkgitem_type = 'I') THEN
        DELETE FROM image WHERE ((image_id=OLD.pkgitem_item_id)
                             AND (image_name=OLD.pkgitem_name));

      ELSIF (OLD.pkgitem_type = 'M') THEN
        DELETE FROM metasql WHERE ((metasql_id=OLD.pkgitem_item_id)
                               AND (metasql_group=_group)
                               AND (metasql_name=_object));

      ELSIF (OLD.pkgitem_type = 'P') THEN
        DELETE FROM priv
        WHERE ((priv_id=OLD.pkgitem_item_id) 
           AND (priv_name=OLD.pkgitem_name));

      ELSIF (OLD.pkgitem_type = 'R') THEN
        DELETE FROM report
        WHERE ((report_id=OLD.pkgitem_item_id)
           AND (report_name=OLD.pkgitem_name));

      ELSIF (OLD.pkgitem_type = 'S') THEN
        PERFORM dropIfExists('SCHEMA', OLD.pkgitem_name, OLD.pkgitem_name);

      ELSIF (OLD.pkgitem_type = 'T') THEN
        PERFORM dropIfExists('TABLE', _object, _schema, true);

      ELSIF (OLD.pkgitem_type = 'U') THEN
        DELETE FROM uiform
        WHERE ((uiform_id=OLD.pkgitem_item_id)
           AND (uiform_name=OLD.pkgitem_name));

      ELSIF (OLD.pkgitem_type = 'V') THEN
        PERFORM dropIfExists('VIEW', _object, _schema, true);

      ELSE
        RAISE EXCEPTION '"%" is not a valid type of package item.',
          OLD.pkgitem_type;
      END IF;
      RETURN OLD;
    END IF;

    RETURN NEW;
  END;
$$;


ALTER FUNCTION public._pkgitembeforetrigger() OWNER TO admin;

--
-- Name: _pkgmetasqlaftertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _pkgmetasqlaftertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 (TG_OP = 'DELETE') THEN
    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._pkgmetasqlaftertrigger() OWNER TO admin;

--
-- Name: _pkgmetasqlaltertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _pkgmetasqlaltertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 (pkgMayBeModified(TG_TABLE_SCHEMA) OR isDba()) THEN
    IF (TG_OP = 'DELETE') THEN
      RETURN OLD;
    ELSE
      RETURN NEW;
    END IF;
  END IF;

  -- cannot combine IF's because plpgsql does not always evaluate left-to-right
  IF (TG_OP = 'INSERT') THEN
    IF (NEW.metasql_grade <= 0 AND NOT _isdba) THEN
      RAISE EXCEPTION 'You may not create grade 0 MetaSQL statements in packages except using the xTuple Updater utility';
    END IF;

  ELSIF (TG_OP = 'UPDATE') THEN
    IF (NEW.metasql_grade <= 0 AND NOT _isdba) THEN
      RAISE EXCEPTION 'You may not alter grade 0 MetaSQL statements in packages except using the xTuple Updater utility';
    END IF;

  ELSIF (TG_OP = 'DELETE') THEN
    IF (OLD.metasql_grade <= 0 AND NOT _isdba) THEN
      RAISE EXCEPTION 'You may not delete grade 0 MetaSQL statements from packages. Try deleting or disabling the package.';
    ELSE
      RETURN OLD;
    END IF;

  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._pkgmetasqlaltertrigger() OWNER TO admin;

--
-- Name: _pkgmetasqlbeforetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _pkgmetasqlbeforetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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
  _metasqlid    INTEGER;
  _isdba        BOOLEAN := false;

BEGIN
  SELECT rolsuper INTO _isdba FROM pg_roles WHERE (rolname=getEffectiveXtUser());

  IF (NOT (_isdba OR checkPrivilege('MaintainMetaSQL'))) THEN
    RAISE EXCEPTION '% does not have privileges to maintain MetaSQL statements in %.% (DBA=%)',
                getEffectiveXtUser(), TG_TABLE_SCHEMA, TG_TABLE_NAME, _isdba;
  END IF;

  IF (TG_OP = 'UPDATE') THEN
    RAISE DEBUG 'update OLD %-%-%, NEW %-%-%',
                 OLD.metasql_group, OLD.metasql_name, OLD.metasql_grade,
                 NEW.metasql_group, NEW.metasql_name, NEW.metasql_grade;

    IF (NEW.metasql_name != OLD.metasql_name OR NEW.metasql_group != OLD.metasql_group OR NEW.metasql_grade != OLD.metasql_grade) THEN
      SELECT metasql_id INTO _metasqlid
      FROM metasql
      WHERE metasql_name=NEW.metasql_name AND metasql_group=NEW.metasql_group AND metasql_grade=NEW.metasql_grade;
      IF (FOUND) THEN
        RAISE EXCEPTION 'Cannot change the MetaSQL statement named %-%-% because another MetaSQL statement with that group, name and grade already exists.', NEW.metasql_group, NEW.metasql_name, NEW.metasql_grade;
      END IF;
    END IF;

  ELSIF (TG_OP = 'INSERT') THEN
    RAISE DEBUG 'insert NEW %-% %',
                 NEW.metasql_group, NEW.metasql_name, NEW.metasql_grade;
    SELECT metasql_id INTO _metasqlid
    FROM metasql
    WHERE metasql_name=NEW.metasql_name AND metasql_group=NEW.metasql_group AND metasql_grade=NEW.metasql_grade;
    IF (FOUND) THEN
      RAISE EXCEPTION 'The new MetaSQL statement %-% % conflicts with an existing statement.',
                      NEW.metasql_group, NEW.metasql_name, NEW.metasql_grade;
    END IF;

  ELSIF (TG_OP = 'DELETE') THEN
    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._pkgmetasqlbeforetrigger() OWNER TO admin;

--
-- Name: _pkgprivaftertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _pkgprivaftertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 (TG_OP = 'DELETE') THEN
    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._pkgprivaftertrigger() OWNER TO admin;

--
-- Name: _pkgprivaltertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _pkgprivaltertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 (pkgMayBeModified(TG_TABLE_SCHEMA) OR isDba()) THEN
    IF (TG_OP = 'DELETE') THEN
      RETURN OLD;
    ELSE
      RETURN NEW;
    END IF;
  END IF;

  IF (TG_OP = 'INSERT') THEN
    RAISE EXCEPTION 'You may not create privileges in packages except using the xTuple Updater utility';

  ELSIF (TG_OP = 'UPDATE') THEN
    RAISE EXCEPTION 'You may not alter privileges in packages except using the xTuple Updater utility';

  ELSIF (TG_OP = 'DELETE') THEN
    RAISE EXCEPTION 'You may not delete privileges from packages. Try deleting or disabling the package.';

  END IF;

  RETURN NEW;
END;

$$;


ALTER FUNCTION public._pkgprivaltertrigger() OWNER TO admin;

--
-- Name: _pkgprivbeforetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _pkgprivbeforetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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
  _privid       INTEGER;
  _debug        BOOL := false;

BEGIN
  IF (TG_OP = 'UPDATE') THEN
    IF (_debug) THEN
      RAISE NOTICE 'OLD.priv_name %, NEW.priv_name %',
                   OLD.priv_name, NEW.priv_name;
    END IF;

    IF (NEW.priv_name != OLD.priv_name) THEN
      SELECT priv_id INTO _privid FROM priv WHERE priv_name=NEW.priv_name;
      IF (FOUND) THEN
        RAISE EXCEPTION 'Cannot change privilege name % because another privilege with that name already exists.', NEW.priv_name;
      END IF;
    END IF;

  ELSIF (TG_OP = 'INSERT') THEN
    IF (_debug) THEN
      RAISE NOTICE 'inserting NEW.priv_name %', NEW.priv_name;
    END IF;
    SELECT priv_id INTO _privid FROM priv WHERE priv_name=NEW.priv_name;
    IF (FOUND) THEN
      RAISE EXCEPTION 'Cannot create new privilege % because another privilege with that name already exists.', NEW.priv_name;
    END IF;

  ELSIF (TG_OP = 'DELETE') THEN
    IF (_debug) THEN RAISE NOTICE 'deleting pkgpriv_id %', OLD.priv_id; END IF;
    DELETE FROM usrpriv WHERE usrpriv_priv_id=OLD.priv_id;
    DELETE FROM grppriv WHERE grppriv_priv_id=OLD.priv_id;

    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._pkgprivbeforetrigger() OWNER TO admin;

--
-- Name: _pkgreportaftertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _pkgreportaftertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 (TG_OP = 'DELETE') THEN
    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._pkgreportaftertrigger() OWNER TO admin;

--
-- Name: _pkgreportaltertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _pkgreportaltertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 (pkgMayBeModified(TG_TABLE_SCHEMA) OR isDba()) THEN
    IF (TG_OP = 'DELETE') THEN
      RETURN OLD;
    ELSE
      RETURN NEW;
    END IF;
  END IF;

  IF (TG_OP = 'INSERT') THEN
    RAISE EXCEPTION 'You may not create report definitions in packages except using the xTuple Updater utility';

  ELSIF (TG_OP = 'UPDATE') THEN
    RAISE EXCEPTION 'You may not alter report definitions in packages except using the xTuple Updater utility';

  ELSIF (TG_OP = 'DELETE') THEN
    RAISE EXCEPTION 'You may not delete report definitions from packages. Try deleting or disabling the package.';

  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._pkgreportaltertrigger() OWNER TO admin;

--
-- Name: _pkgreportbeforetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _pkgreportbeforetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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
  _reportid     INTEGER;
  _debug        BOOL := false;

BEGIN
  IF (TG_OP = 'UPDATE') THEN
    IF (_debug) THEN
      RAISE NOTICE 'update OLD % %, NEW % %',
                   OLD.report_name, OLD.report_grade, NEW.report_name, NEW.report_grade;
    END IF;

    IF (NEW.report_name != OLD.report_name) THEN
      SELECT report_id INTO _reportid
      FROM report
      WHERE ((report_name=NEW.report_name)
        AND  (report_grade=NEW.report_grade));
      IF (FOUND) THEN
        RAISE EXCEPTION 'Cannot change report % % because another report with that name and grade already exists.', NEW.report_name, NEW.report_grade;
      END IF;
    END IF;

  ELSIF (TG_OP = 'INSERT') THEN
    IF (_debug) THEN
      RAISE NOTICE 'insert NEW % %', NEW.report_name, NEW.report_grade;
    END IF;
    SELECT report_id INTO _reportid
    FROM report
    WHERE ((report_name=NEW.report_name)
      AND  (report_grade=NEW.report_grade));
    IF (FOUND) THEN
      RAISE EXCEPTION 'Cannot create new report % % because another report with that name and grade already exists.', NEW.report_name, NEW.report_grade;
    END IF;

  ELSIF (TG_OP = 'DELETE') THEN

    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._pkgreportbeforetrigger() OWNER TO admin;

--
-- Name: _pkgscriptaftertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _pkgscriptaftertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 (TG_OP = 'DELETE') THEN
    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._pkgscriptaftertrigger() OWNER TO admin;

--
-- Name: _pkgscriptaltertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _pkgscriptaltertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 (pkgMayBeModified(TG_TABLE_SCHEMA) OR isDba()) THEN
    IF (TG_OP = 'DELETE') THEN
      RETURN OLD;
    ELSE
      RETURN NEW;
    END IF;
  END IF;

  IF (TG_OP = 'INSERT') THEN
    RAISE EXCEPTION 'You may not create scripts in packages except using the xTuple Updater utility';

  ELSIF (TG_OP = 'UPDATE') THEN
    RAISE EXCEPTION 'You may not alter scripts in packages except using the xTuple Updater utility';

  ELSIF (TG_OP = 'DELETE') THEN
    RAISE EXCEPTION 'You may not delete scripts from packages. Try deleting or disabling the package.';

  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._pkgscriptaltertrigger() OWNER TO admin;

--
-- Name: _pkgscriptbeforetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _pkgscriptbeforetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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
  _scriptid     INTEGER;
  _debug        BOOL := false;

BEGIN
  IF (TG_OP = 'UPDATE') THEN
    RETURN NEW;

  ELSIF (TG_OP = 'INSERT') THEN
    RETURN NEW;

  ELSIF (TG_OP = 'DELETE') THEN
    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._pkgscriptbeforetrigger() OWNER TO admin;

--
-- Name: _pkguiformaftertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _pkguiformaftertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 (TG_OP = 'DELETE') THEN
    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._pkguiformaftertrigger() OWNER TO admin;

--
-- Name: _pkguiformaltertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _pkguiformaltertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 (pkgMayBeModified(TG_TABLE_SCHEMA) OR isDba()) THEN
    IF (TG_OP = 'DELETE') THEN
      RETURN OLD;
    ELSE
      RETURN NEW;
    END IF;
  END IF;

  IF (TG_OP = 'INSERT') THEN
    RAISE EXCEPTION 'You may not create forms in packages except using the xTuple Updater utility';

  ELSIF (TG_OP = 'UPDATE') THEN
    RAISE EXCEPTION 'You may not alter forms in packages except using the xTuple Updater utility';

  ELSIF (TG_OP = 'DELETE') THEN
    RAISE EXCEPTION 'You may not delete forms from packages. Try deleting or disabling the package.';

  END IF;

  RETURN NEW;
END;

$$;


ALTER FUNCTION public._pkguiformaltertrigger() OWNER TO admin;

--
-- Name: _pkguiformbeforetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _pkguiformbeforetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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
  _uiformid     INTEGER;
  _debug        BOOL := false;

BEGIN
  IF (TG_OP = 'UPDATE') THEN
    RETURN NEW;

  ELSIF (TG_OP = 'INSERT') THEN
    RETURN NEW;

  ELSIF (TG_OP = 'DELETE') THEN
    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._pkguiformbeforetrigger() OWNER TO admin;

--
-- Name: _poheadafterdeletetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _poheadafterdeletetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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

BEGIN

  DELETE
  FROM charass
  WHERE charass_target_type = 'PO'
    AND charass_target_id = OLD.pohead_id;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._poheadafterdeletetrigger() OWNER TO admin;

--
-- Name: _poheadtrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _poheadtrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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;
  _check	BOOLEAN;
  _maint        BOOLEAN := TRUE;

BEGIN

-- Check if we are doing maintenance
  IF (TG_OP = 'UPDATE') THEN
    IF ( (OLD.pohead_status           != NEW.pohead_status) OR
         (OLD.pohead_printed          != NEW.pohead_printed) ) THEN
      _maint := FALSE;
    END IF;
  END IF;

  -- Check
  IF ( (NOT _maint) AND (NOT checkPrivilege('MaintainPurchaseOrders'))
                    AND (NOT checkPrivilege('PostPurchaseOrders'))
                    AND (NOT checkPrivilege('PrintPurchaseOrders'))
                    AND (NOT checkPrivilege('PostVouchers')) ) THEN
    RAISE EXCEPTION 'You do not have privileges to alter a Purchase Order.';
  END IF;

  IF ( _maint AND (NOT checkPrivilege('MaintainPurchaseOrders')) ) THEN
    RAISE EXCEPTION 'You do not have privileges to alter a Purchase Order.';
  END IF;

  IF (TG_OP = 'INSERT') THEN
    --- clear the number from the issue cache
    PERFORM clearNumberIssue('PoNumber', NEW.pohead_number);
  END IF;

  IF ( (TG_OP = 'INSERT') OR (TG_op = 'UPDATE') ) THEN
    IF (NOT ISNUMERIC(NEW.pohead_number) AND NEW.pohead_saved) THEN
      RAISE EXCEPTION 'Purchase Order Number must be numeric.';
    END IF;
  END IF;

  IF (TG_OP = 'UPDATE') THEN
    IF (NEW.pohead_status = 'O' AND OLD.pohead_released IS NULL) THEN
      NEW.pohead_released := CURRENT_DATE;
    END IF;
  END IF;

  IF ( SELECT (metric_value='t')
       FROM metric
       WHERE (metric_name='POChangeLog') ) THEN

--  Cache the cmnttype_id for ChangeLog
    SELECT cmnttype_id INTO _cmnttypeid
    FROM cmnttype
    WHERE (cmnttype_name='ChangeLog');
    IF (FOUND) THEN
      IF (TG_OP = 'INSERT') THEN
        PERFORM postComment(_cmnttypeid, 'P', NEW.pohead_id, 'Created');

      ELSIF (TG_OP = 'UPDATE') THEN
        IF (OLD.pohead_terms_id <> NEW.pohead_terms_id) THEN
          PERFORM postComment( _cmnttypeid, 'P', NEW.pohead_id,
                               ('Terms Changed from "' || oldterms.terms_code || '" to "' || newterms.terms_code || '"') )
          FROM terms AS oldterms, terms AS newterms
          WHERE ( (oldterms.terms_id=OLD.pohead_terms_id)
           AND (newterms.terms_id=NEW.pohead_terms_id) );
        END IF;

      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 IF;
    END IF;
  END IF;

  IF (TG_OP = 'DELETE') THEN
    RETURN OLD;
  ELSE
    RETURN NEW;
  END IF;

END;
$$;


ALTER FUNCTION public._poheadtrigger() OWNER TO admin;

--
-- Name: _poheadtriggerafter(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _poheadtriggerafter() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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
    WHERE ((itemsite_id=poitem_itemsite_id)
     AND (poitem_pohead_id=NEW.pohead_id));
  END IF;

  -- Do not update closed poitems
  IF (TG_OP = 'UPDATE') THEN
    IF (OLD.pohead_status != NEW.pohead_status) THEN
      UPDATE poitem
      SET poitem_status=NEW.pohead_status
      WHERE ( (poitem_pohead_id=NEW.pohead_id)
        AND   (poitem_status <> 'C') );
    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._poheadtriggerafter() OWNER TO admin;

--
-- Name: _poitemafterdeletetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _poitemafterdeletetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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;
BEGIN

  IF (OLD.poitem_status = 'O') THEN
    IF ( (SELECT (count(*) < 1)
            FROM poitem
           WHERE ((poitem_pohead_id=OLD.poitem_pohead_id)
             AND  (poitem_id != OLD.poitem_id)
             AND  (poitem_status <> 'C')) ) ) THEN
      UPDATE pohead SET pohead_status = 'C'
       WHERE ((pohead_id=OLD.poitem_pohead_id)
         AND  (pohead_status='O'));
    END IF;
  END IF;

  IF ( SELECT fetchMetricBool('POChangeLog') ) THEN
    _changelog := TRUE;
  END IF;

  IF ( _changelog ) THEN
    PERFORM postComment('ChangeLog', 'P', OLD.poitem_pohead_id, ('Deleted Line #' || OLD.poitem_linenumber::TEXT));
  END IF;

  DELETE
  FROM charass
  WHERE charass_target_type = 'PI'
    AND charass_target_id = OLD.poitem_id;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._poitemafterdeletetrigger() OWNER TO admin;

--
-- Name: _poitemaftertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _poitemaftertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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;
BEGIN

  IF (TG_OP = 'UPDATE') THEN
    IF (OLD.poitem_status <> NEW.poitem_status) THEN
      IF ( (SELECT (count(*) < 1)
              FROM poitem
             WHERE ((poitem_pohead_id=NEW.poitem_pohead_id)
               AND  (poitem_id != NEW.poitem_id)
               AND  (poitem_status<>'C')) ) AND (NEW.poitem_status='C') ) THEN
        UPDATE pohead SET pohead_status = 'C'
         WHERE ((pohead_id=NEW.poitem_pohead_id)
           AND  (pohead_status='O'));
      ELSE
        UPDATE pohead SET pohead_status = 'O'
         WHERE ((pohead_id=NEW.poitem_pohead_id)
           AND  (pohead_status='C'));
      END IF;
    END IF;
  END IF;

  IF (TG_OP = 'INSERT') THEN
    PERFORM postEvent('POitemCreate', 'P', NEW.poitem_id,
                      itemsite_warehous_id,
                      (pohead_number || '-' || NEW.poitem_linenumber || ': ' || item_number),
                      NULL, NULL, NULL, NULL)
    FROM pohead JOIN itemsite ON (itemsite_id=NEW.poitem_itemsite_id)
                JOIN item ON (item_id=itemsite_item_id)
    WHERE (pohead_id=NEW.poitem_pohead_id)
      AND (NEW.poitem_duedate <= (CURRENT_DATE + itemsite_eventfence));
  END IF;

  IF ( SELECT fetchMetricBool('POChangeLog') ) THEN
    _changelog := TRUE;
  END IF;

  IF ( _changelog ) THEN
    IF (TG_OP = 'INSERT') THEN
      PERFORM postComment('ChangeLog', 'P', NEW.poitem_pohead_id, ('Created Line #' || NEW.poitem_linenumber::TEXT));
      PERFORM postComment('ChangeLog', 'PI', NEW.poitem_id, 'Created');

    ELSIF (TG_OP = 'UPDATE') THEN
      IF (NEW.poitem_qty_ordered <> OLD.poitem_qty_ordered) THEN
        PERFORM postComment( 'ChangeLog', 'PI', NEW.poitem_id,
                             ( 'Qty. Ordered Changed from ' || formatQty(OLD.poitem_qty_ordered) ||
                               ' to ' || formatQty(NEW.poitem_qty_ordered ) ) );
      END IF;
      IF (NEW.poitem_unitprice <> OLD.poitem_unitprice) THEN
        PERFORM postComment( 'ChangeLog', 'PI', NEW.poitem_id,
                             ( 'Unit Price Changed from ' || formatPurchPrice(OLD.poitem_unitprice) ||
                               ' to ' || formatPurchPrice(NEW.poitem_unitprice ) ) );
      END IF;
      IF (NEW.poitem_duedate <> OLD.poitem_duedate) THEN
        PERFORM postComment( 'ChangeLog', 'PI', NEW.poitem_id,
                             ( 'Due Date Changed from ' || formatDate(OLD.poitem_duedate) ||
                               ' to ' || formatDate(NEW.poitem_duedate ) ) );
      END IF;
      IF (COALESCE(OLD.poitem_taxtype_id, -1) <> COALESCE(NEW.poitem_taxtype_id, -1)) THEN
        PERFORM postComment( 'ChangeLog', 'PI', NEW.poitem_id,
                             ( 'Tax Type Changed from "' ||
                               COALESCE((SELECT taxtype_name FROM taxtype WHERE taxtype_id=OLD.poitem_taxtype_id), 'None') ||
                               '" (' || COALESCE(OLD.poitem_taxtype_id, 0) ||
                               ') to "' ||
                               COALESCE((SELECT taxtype_name FROM taxtype WHERE taxtype_id=NEW.poitem_taxtype_id), 'None') ||
                               '" (' || COALESCE(NEW.poitem_taxtype_id, 0) || ')' ) );
      END IF;
      IF (NEW.poitem_status <> OLD.poitem_status) THEN
        IF (NEW.poitem_status = 'C') THEN
          PERFORM postComment('ChangeLog', 'PI', NEW.poitem_id, 'Closed');
        ELSIF (NEW.poitem_status = 'O') THEN
          PERFORM postComment('ChangeLog', 'PI', NEW.poitem_id, 'Opened');
        END IF;
      END IF;

    END IF;
  END IF;

  RETURN NEW;

END;
$$;


ALTER FUNCTION public._poitemaftertrigger() OWNER TO admin;

--
-- Name: _poitemdeletetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _poitemdeletetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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
BEGIN

  IF (NOT checkPrivilege('MaintainPurchaseOrders')) THEN
    RAISE EXCEPTION 'You do not have privileges to alter a Purchase Order.';
  END IF;

  IF (EXISTS(SELECT recv_id
             FROM recv
             WHERE ((recv_order_type='PO')
                AND (recv_orderitem_id=OLD.poitem_id)
                AND (recv_qty>0)))) THEN
    RAISE EXCEPTION 'Cannot delete an P/O Item which has been received';
  END IF;

  DELETE FROM comment
   WHERE ( (comment_source='PI')
     AND   (comment_source_id=OLD.poitem_id) );

  DELETE FROM charass
   WHERE ((charass_target_type='PI')
     AND  (charass_target_id=OLD.poitem_id));

  RETURN OLD;

END;
$$;


ALTER FUNCTION public._poitemdeletetrigger() OWNER TO admin;

--
-- Name: _poitemtrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _poitemtrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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;
  _status      	CHAR(1);
  _check      	BOOLEAN;
  _cnt     	INTEGER;
  _s 		RECORD;
BEGIN

  -- Check
  IF ( (TG_OP = 'UPDATE') AND
       (NOT checkPrivilege('MaintainPurchaseOrders')) AND
       (NOT checkPrivilege('ChangePurchaseOrderQty')) AND
       (NOT checkPrivilege('EnterReceipts')) AND
       (NOT checkPrivilege('PostVouchers')) ) THEN
    RAISE EXCEPTION 'You do not have privileges to alter a Purchase Order.';
  END IF;
  IF ( (TG_OP = 'INSERT') AND (NOT checkPrivilege('MaintainPurchaseOrders')) ) THEN
    RAISE EXCEPTION 'You do not have privileges to alter a Purchase Order.';
  END IF;

  IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
    SELECT pohead_status INTO _status
    FROM pohead
    WHERE (pohead_id=NEW.poitem_pohead_id);

    IF (NEW.poitem_itemsite_id=-1) THEN
      NEW.poitem_itemsite_id := NULL;
    END IF;
    IF (NEW.poitem_expcat_id=-1) THEN
      NEW.poitem_expcat_id := NULL;
    END IF;

    IF (NEW.poitem_itemsite_id IS NOT NULL AND NEW.poitem_expcat_id IS NOT NULL) THEN
      RAISE EXCEPTION 'A purchase order line may not include both an inventory and non-inventory item';
    ELSIF (NEW.poitem_itemsite_id IS NULL AND NEW.poitem_expcat_id IS NULL) THEN
      RAISE EXCEPTION 'A purchase order line must specify either an inventory item or a non-inventory expense category';
    ELSIF (NEW.poitem_qty_ordered IS NULL) THEN
      RAISE EXCEPTION 'A purchase order line must specify a quantity';
    ELSIF (COALESCE(NEW.poitem_itemsite_id,-1) != -1) THEN
      SELECT (COUNT(item_id)=1) INTO _check
      FROM itemsite, item
      WHERE ((itemsite_id=NEW.poitem_itemsite_id)
      AND (itemsite_item_id=item_id)
      AND (item_type IN ('P','O','M','T')));
      IF NOT (_check) THEN
        RAISE EXCEPTION 'The item is not a purchasable item type';
      END IF;
    END IF;
  END IF;

  IF (TG_OP = 'INSERT') THEN
    IF (_status='C') THEN
      RAISE EXCEPTION 'New lines may not be inserted into a closed purchase order';
    END IF;

    --Fetch and apply default item source data if applicable
    IF ((NEW.poitem_itemsrc_id IS NULL) AND (NEW.poitem_itemsite_id IS NOT NULL)) THEN
      IF (NEW.poitem_itemsrc_id IS NULL) THEN
        SELECT COUNT(itemsrc_id)  INTO _cnt
        FROM pohead,itemsrc,itemsite
        WHERE ((pohead_id=NEW.poitem_pohead_id)
        AND (pohead_vend_id=itemsrc_vend_id)
        AND (itemsite_id=NEW.poitem_itemsite_id)
        AND (itemsite_item_id=itemsrc_item_id));

        IF (_cnt = 1) THEN
          -- We found the one and only item source, so populate data for it
          SELECT itemsrc.* INTO _s
          FROM pohead,itemsrc,itemsite
          WHERE ((pohead_id=NEW.poitem_pohead_id)
          AND (pohead_vend_id=itemsrc_vend_id)
          AND (itemsite_id=NEW.poitem_itemsite_id)
          AND (itemsite_item_id=itemsrc_item_id));
          IF (FOUND) THEN
            NEW.poitem_itemsrc_id 		:= _s.itemsrc_id;
            NEW.poitem_vend_uom            	:= _s.itemsrc_vend_uom;
            NEW.poitem_invvenduomratio    	:= _s.itemsrc_invvendoruomratio;
            NEW.poitem_duedate			:= COALESCE(NEW.poitem_duedate, CURRENT_DATE + _s.itemsrc_leadtime);
            NEW.poitem_vend_item_number 	:= COALESCE(NEW.poitem_vend_item_number,_s.itemsrc_vend_item_number);
            NEW.poitem_vend_item_descrip   	:= COALESCE(NEW.poitem_vend_item_descrip,_s.itemsrc_vend_item_descrip);
            NEW.poitem_manuf_name		:= COALESCE(NEW.poitem_manuf_name,_s.itemsrc_manuf_name);
            NEW.poitem_manuf_item_number	:= COALESCE(NEW.poitem_manuf_item_number, _s.itemsrc_manuf_item_number);
            NEW.poitem_manuf_item_descrip	:= COALESCE(NEW.poitem_manuf_item_descrip, _s.itemsrc_manuf_item_descrip);
          END IF;
        ELSIF (_cnt > 1) THEN
          -- There are multiple sources, see if there is an exact match with provided vendor info.
          SELECT itemsrc.* INTO _s
          FROM pohead,itemsrc,itemsite
          WHERE ((pohead_id=NEW.poitem_pohead_id)
          AND (pohead_vend_id=itemsrc_vend_id)
          AND (itemsite_id=NEW.poitem_itemsite_id)
          AND (itemsite_item_id=itemsrc_item_id)
          AND (NEW.poitem_vend_item_number=itemsrc_vend_item_number)
          AND (COALESCE(NEW.poitem_manuf_name,'')=COALESCE(itemsrc_manuf_name,''))
          AND (COALESCE(NEW.poitem_manuf_item_number,'')=COALESCE(itemsrc_manuf_item_number,'')));
          IF (FOUND) THEN
            NEW.poitem_itemsrc_id 		:= _s.itemsrc_id;
            NEW.poitem_vend_uom            	:= _s.itemsrc_vend_uom;
            NEW.poitem_invvenduomratio    	:= _s.itemsrc_invvendoruomratio;
            NEW.poitem_duedate			:= COALESCE(NEW.poitem_duedate, CURRENT_DATE + _s.itemsrc_leadtime);
            NEW.poitem_vend_item_descrip   	:= COALESCE(NEW.poitem_vend_item_descrip,_s.itemsrc_vend_item_descrip);
            NEW.poitem_manuf_item_descrip	:= COALESCE(NEW.poitem_manuf_item_descrip, _s.itemsrc_manuf_item_descrip);
          END IF;
        END IF;
      END IF;
    END IF;

    IF (NEW.poitem_duedate IS NULL) THEN
      RAISE EXCEPTION  'A due date is required';
    END IF;

    --Set defaults
    NEW.poitem_linenumber    		:= COALESCE(NEW.poitem_linenumber,(
						SELECT COALESCE(MAX(poitem_linenumber),0) + 1
						FROM poitem
						WHERE (poitem_pohead_id=NEW.poitem_pohead_id)));
    NEW.poitem_status                  := _status;
    NEW.poitem_invvenduomratio 	:= COALESCE(NEW.poitem_invvenduomratio,1);
    IF (NEW.poitem_invvenduomratio = 0.0) THEN
      NEW.poitem_invvenduomratio = 1.0;
    END IF;
    NEW.poitem_vend_item_number 	:= COALESCE(NEW.poitem_vend_item_number,'');
    NEW.poitem_vend_item_descrip   	:= COALESCE(NEW.poitem_vend_item_descrip,'');
    NEW.poitem_unitprice       	:= COALESCE(NEW.poitem_unitprice,(
                                                SELECT itemsrcPrice(NEW.poitem_itemsrc_id, COALESCE(itemsite_warehous_id, -1), pohead_dropship,
                                                       NEW.poitem_qty_ordered, pohead_curr_id, CURRENT_DATE)
                                                FROM itemsite, pohead
                                                WHERE ( (itemsite_id=NEW.poitem_itemsite_id)
                                                AND (pohead_id=NEW.poitem_pohead_id) )), 0.0);
    NEW.poitem_stdcost			:= COALESCE(NEW.poitem_stdcost,(
						SELECT stdcost(itemsite_item_id)
						FROM itemsite
						WHERE (itemsite_id=NEW.poitem_itemsite_id)));
    NEW.poitem_bom_rev_id		:= COALESCE(NEW.poitem_bom_rev_id,(
						SELECT getActiveRevId('BOM',itemsite_item_id)
						FROM itemsite
						WHERE (itemsite_id=NEW.poitem_itemsite_id)));
    NEW.poitem_boo_rev_id		:= COALESCE(NEW.poitem_boo_rev_id,(
						SELECT getActiveRevId('BOO',itemsite_item_id)
						FROM itemsite
						WHERE (itemsite_id=NEW.poitem_itemsite_id)));
    NEW.poitem_comments		:= COALESCE(NEW.poitem_comments,'');
    NEW.poitem_freight			:= COALESCE(NEW.poitem_freight,0);
    NEW.poitem_qty_received		:= 0;
    NEW.poitem_qty_returned		:= 0;
    NEW.poitem_qty_vouchered		:= 0;

  END IF;

  IF (TG_OP = 'UPDATE') THEN
    IF (NEW.poitem_itemsite_id != OLD.poitem_itemsite_id) THEN
      RAISE EXCEPTION 'You may not change the item site for a line item.';
    ELSIF (NEW.poitem_expcat_id != OLD.poitem_expcat_id) THEN
      RAISE EXCEPTION 'You may not change the expense category for a line item.';
    END IF;
  END IF;

  RETURN NEW;

END;
$$;


ALTER FUNCTION public._poitemtrigger() OWNER TO admin;

--
-- Name: _prjafterdeletetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _prjafterdeletetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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

BEGIN

  DELETE
  FROM charass
  WHERE charass_target_type = 'PROJ'
    AND charass_target_id = OLD.prj_id;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._prjafterdeletetrigger() OWNER TO admin;

--
-- Name: _prjaftertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _prjaftertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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;
BEGIN

--  Cache the cmnttype_id for ChangeLog
  SELECT cmnttype_id INTO _cmnttypeid
  FROM cmnttype
  WHERE (cmnttype_name='ChangeLog');
  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Comment type ChangeLog not found';
  END IF;

  IF (TG_OP = 'INSERT') THEN
    PERFORM postComment(_cmnttypeid, 'J', NEW.prj_id, 'Created');
  ELSIF (TG_OP = 'UPDATE') THEN
    IF (OLD.prj_start_date <> NEW.prj_start_date) THEN
      PERFORM postComment( _cmnttypeid, 'J', NEW.prj_id,
                           ('Start Date Changed from ' || formatDate(OLD.prj_start_date) || ' to ' || formatDate(NEW.prj_start_date)) );
    END IF;
    IF (OLD.prj_due_date <> NEW.prj_due_date) THEN
      PERFORM postComment( _cmnttypeid, 'J', NEW.prj_id,
                           ('Due Date Changed from ' || formatDate(OLD.prj_due_date) || ' to ' || formatDate(NEW.prj_due_date)) );
    END IF;
    IF (OLD.prj_assigned_date <> NEW.prj_assigned_date) THEN
      PERFORM postComment( _cmnttypeid, 'J', NEW.prj_id,
                           ('Assigned Date Changed from ' || formatDate(OLD.prj_assigned_date) || ' to ' || formatDate(NEW.prj_assigned_date)) );
    END IF;
    IF (OLD.prj_completed_date <> NEW.prj_completed_date) THEN
      PERFORM postComment( _cmnttypeid, 'J', NEW.prj_id,
                           ('Completed Date Changed from ' || formatDate(OLD.prj_completed_date) || ' to ' || formatDate(NEW.prj_completed_date)) );
    END IF;

  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._prjaftertrigger() OWNER TO admin;

--
-- Name: _prjbeforedeletetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _prjbeforedeletetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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
  _recurid     INTEGER;
  _newparentid INTEGER;
BEGIN

  IF (TG_OP = 'DELETE') THEN
    DELETE FROM docass WHERE docass_source_id = OLD.prj_id AND docass_source_type = 'J';
    DELETE FROM docass WHERE docass_target_id = OLD.prj_id AND docass_target_type = 'J';

    SELECT recur_id INTO _recurid
      FROM recur
     WHERE ((recur_parent_id=OLD.prj_id)
        AND (recur_parent_type='J'));

    IF (_recurid IS NOT NULL) THEN
      SELECT MIN(prj_id) INTO _newparentid
        FROM prj
       WHERE ((prj_recurring_prj_id=OLD.prj_id)
          AND (prj_id!=OLD.prj_id));

      -- client is responsible for warning about deleting a recurring prj
      IF (_newparentid IS NULL) THEN
        DELETE FROM recur WHERE recur_id=_recurid;
      ELSE
        UPDATE recur SET recur_parent_id=_newparentid
         WHERE recur_id=_recurid;
      END IF;

    END IF;

    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._prjbeforedeletetrigger() OWNER TO admin;

--
-- Name: _prjtaskafterdeletetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _prjtaskafterdeletetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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

BEGIN

  DELETE
  FROM charass
  WHERE charass_target_type = 'TASK'
    AND charass_target_id = OLD.prjtask_id;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._prjtaskafterdeletetrigger() OWNER TO admin;

--
-- Name: _prjtaskaftertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _prjtaskaftertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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;
BEGIN

  SELECT cmnttype_id INTO _cmnttypeid
  FROM cmnttype
  WHERE (cmnttype_name='ChangeLog');
  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Comment type ChangeLog not found';
  END IF;

  IF (TG_OP = 'INSERT') THEN
    PERFORM postComment(_cmnttypeid, 'TA', NEW.prjtask_id, 'Created');

  ELSIF (TG_OP = 'UPDATE') THEN
    IF (OLD.prjtask_start_date <> NEW.prjtask_start_date) THEN
      PERFORM postComment( _cmnttypeid, 'TA', NEW.prjtask_id,
                           ('Start Date Changed from ' || formatDate(OLD.prjtask_start_date) || ' to ' || formatDate(NEW.prjtask_start_date)) );
    END IF;
    IF (OLD.prjtask_due_date <> NEW.prjtask_due_date) THEN
      PERFORM postComment( _cmnttypeid, 'TA', NEW.prjtask_id,
                           ('Due Date Changed from ' || formatDate(OLD.prjtask_due_date) || ' to ' || formatDate(NEW.prjtask_due_date)) );
    END IF;
    IF (OLD.prjtask_assigned_date <> NEW.prjtask_assigned_date) THEN
      PERFORM postComment( _cmnttypeid, 'TA', NEW.prjtask_id,
                           ('Assigned Date Changed from ' || formatDate(OLD.prjtask_assigned_date) || ' to ' || formatDate(NEW.prjtask_assigned_date)) );
    END IF;
    IF (OLD.prjtask_completed_date <> NEW.prjtask_completed_date) THEN
      PERFORM postComment( _cmnttypeid, 'TA', NEW.prjtask_id,
                           ('Completed Date Changed from ' || formatDate(OLD.prjtask_completed_date) || ' to ' || formatDate(NEW.prjtask_completed_date)) );
    END IF;
    IF (OLD.prjtask_hours_actual != NEW.prjtask_hours_actual) THEN
      PERFORM postComment(_cmnttypeid, 'TA', NEW.prjtask_id,
          'Actual Hours changed from ' || formatQty(OLD.prjtask_hours_actual) || ' to ' || formatQty(NEW.prjtask_hours_actual));
    END IF;
    IF (OLD.prjtask_exp_actual != NEW.prjtask_exp_actual) THEN
      PERFORM postComment(_cmnttypeid, 'TA', NEW.prjtask_id,
          'Actual Expense changed from ' || formatQty(OLD.prjtask_exp_actual) || ' to ' || formatQty(NEW.prjtask_exp_actual));
    END IF;

  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._prjtaskaftertrigger() OWNER TO admin;

--
-- Name: _prjtasktrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _prjtasktrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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

  --  Checks
  IF (NEW.prjtask_owner_username=getEffectiveXtUser()) THEN
    IF (NOT checkPrivilege('MaintainAllProjects') AND NOT checkPrivilege('MaintainPersonalProjects')) THEN
      RAISE EXCEPTION 'You do not have privileges to maintain Projects.';
    END IF;
  ELSIF (NOT checkPrivilege('MaintainAllProjects')) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Projects.';
  ELSIF (LENGTH(COALESCE(NEW.prjtask_number,'')) = 0) THEN
    RAISE EXCEPTION 'You must ender a valid number.';
  ELSIF (LENGTH(COALESCE(NEW.prjtask_name,'')) = 0) THEN
    RAISE EXCEPTION 'You must ender a valid name.';
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._prjtasktrigger() OWNER TO admin;

--
-- Name: _prospectafterdeletetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _prospectafterdeletetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 EXISTS(SELECT 1 FROM quhead WHERE quhead_cust_id = OLD.prospect_id) AND
     NOT EXISTS (SELECT 1 FROM custinfo WHERE cust_id = OLD.prospect_id) THEN
    RAISE EXCEPTION '[xtuple: deleteProspect, -1]';
  END IF;

  IF (fetchMetricBool('ProspectChangeLog')) THEN
    PERFORM postComment(cmnttype_id, 'PSPCT', OLD.prospect_id,
                        'Deleted "' || OLD.prospect_number || '"')
      FROM cmnttype
     WHERE (cmnttype_name='ChangeLog');
  END IF;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._prospectafterdeletetrigger() OWNER TO admin;

--
-- Name: _prospectaftertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _prospectaftertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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;
  _custid       INTEGER;
  _prospectid   INTEGER;

BEGIN

  IF (TG_OP = 'INSERT') THEN
    SELECT crmacct_cust_id, crmacct_prospect_id INTO _custid, _prospectid
      FROM crmacct
     WHERE crmacct_number=NEW.prospect_number;

    IF (_custid > 0 AND _custid != _prospectid) THEN
      RAISE EXCEPTION '[xtuple: createProspect, -2]';
    END IF;

    IF (_prospectid > 0) THEN
      RAISE EXCEPTION '[xtuple: createProspect, -3]';
    END IF;

    -- http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE
    LOOP
      UPDATE crmacct SET crmacct_prospect_id=NEW.prospect_id,
                         crmacct_cust_id=NULL,
                         crmacct_name=NEW.prospect_name
       WHERE crmacct_number=NEW.prospect_number;
      IF (FOUND) THEN
        EXIT;
      END IF;
      BEGIN
        INSERT INTO crmacct(crmacct_number,      crmacct_name,
                            crmacct_active,      crmacct_type,
                            crmacct_prospect_id, crmacct_cntct_id_1
                  ) VALUES (NEW.prospect_number, NEW.prospect_name,
                            NEW.prospect_active, 'O',
                            NEW.prospect_id,     NEW.prospect_cntct_id);
        EXIT;
      EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
      END;
    END LOOP;

    /* TODO: default characteristic assignments based on what? */

  ELSIF (TG_OP = 'UPDATE') THEN
    UPDATE crmacct SET crmacct_number = NEW.prospect_number
    WHERE ((crmacct_prospect_id=NEW.prospect_id)
      AND  (crmacct_number!=NEW.prospect_number));

    UPDATE crmacct SET crmacct_name = NEW.prospect_name
    WHERE ((crmacct_prospect_id=NEW.prospect_id)
      AND  (crmacct_name!=NEW.prospect_name));

  END IF;

  IF (fetchMetricBool('ProspectChangeLog')) THEN
    SELECT cmnttype_id INTO _cmnttypeid
      FROM cmnttype
     WHERE (cmnttype_name='ChangeLog');

    IF (_cmnttypeid IS NOT NULL) THEN
      IF (TG_OP = 'INSERT') THEN
        PERFORM postComment(_cmnttypeid, 'PSPCT', NEW.prospect_id, 'Created');

      ELSIF (TG_OP = 'UPDATE') THEN
        IF (OLD.prospect_active <> NEW.prospect_active) THEN
          PERFORM postComment(_cmnttypeid, 'PSPCT', NEW.prospect_id,
                              CASE WHEN NEW.prospect_active THEN 'Activated'
                                   ELSE 'Deactivated' END);
        END IF;

        IF (OLD.prospect_number <> NEW.prospect_number) THEN
          PERFORM postComment(_cmnttypeid, 'PSPCT', NEW.prospect_id,
                              'Number changed from "' || OLD.prospect_number ||
                              '" to "' || NEW.prospect_number || '"');
        END IF;

        IF (OLD.prospect_name <> NEW.prospect_name) THEN
          PERFORM postComment(_cmnttypeid, 'PSPCT', NEW.prospect_id,
                              'Name changed from "' || OLD.prospect_name ||
                              '" to "' || NEW.prospect_name || '"');
        END IF;

        IF (OLD.prospect_cntct_id <> NEW.prospect_cntct_id) THEN
          PERFORM postComment(_cmnttypeid, 'PSPCT', NEW.prospect_id,
                              'Contact changed from "' ||
                              formatCntctName(OLD.prospect_cntct_id) || '" to "' ||
                              formatCntctName(NEW.prospect_cntct_id) || '"');
        END IF;

        IF (OLD.prospect_taxauth_id <> NEW.prospect_taxauth_id) THEN
          PERFORM postComment(_cmnttypeid, 'PSPCT', NEW.prospect_id,
                              'Tax Authority changed from "' ||
                              (SELECT taxauth_code FROM taxauth
                                WHERE taxauth_id=OLD.prospect_taxauth_id) ||
                              '" to "' ||
                              (SELECT taxauth_code FROM taxauth
                                WHERE taxauth_id=NEW.prospect_taxauth_id) || '"');
        END IF;

        IF (OLD.prospect_salesrep_id <> NEW.prospect_salesrep_id) THEN
          PERFORM postComment(_cmnttypeid, 'PSPCT', NEW.prospect_id,
                              'Sales Rep changed from "' ||
                              (SELECT salesrep_number FROM salesrep
                               WHERE salesrep_id=OLD.prospect_salesrep_id) ||
                              '" to "' ||
                              (SELECT salesrep_number FROM salesrep
                               WHERE salesrep_id=NEW.prospect_salesrep_id) || '"');
        END IF;

        IF (OLD.prospect_warehous_id <> NEW.prospect_warehous_id) THEN
          PERFORM postComment(_cmnttypeid, 'PSPCT', NEW.prospect_id,
                              'Warehouse changed from "' ||
                              (SELECT warehous_code FROM whsinfo
                                WHERE warehous_id=OLD.prospect_warehous_id) ||
                              '" to "' ||
                              (SELECT warehous_code FROM whsinfo
                                WHERE warehous_id=NEW.prospect_warehous_id) || '"');
        END IF;

        IF (OLD.prospect_taxzone_id <> NEW.prospect_taxzone_id) THEN
          PERFORM postComment(_cmnttypeid, 'PSPCT', NEW.prospect_id,
                              'Tax Zone changed from "' ||
                              (SELECT taxzone_code FROM taxzone
                                WHERE taxzone_id=OLD.prospect_taxzone_id) || '" to "' ||
                              (SELECT taxzone_code FROM taxzone
                                WHERE taxzone_id=NEW.prospect_taxzone_id) || '"');
        END IF;

      END IF;
    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._prospectaftertrigger() OWNER TO admin;

--
-- Name: _prospectbeforedeletetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _prospectbeforedeletetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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('MaintainProspectMasters')) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Prospects.';
  END IF;

  UPDATE crmacct SET crmacct_prospect_id = NULL
   WHERE crmacct_prospect_id = OLD.prospect_id;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._prospectbeforedeletetrigger() OWNER TO admin;

--
-- Name: _prospecttrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _prospecttrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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('MaintainProspectMasters')) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Prospects.';
  END IF;

  IF (NEW.prospect_number IS NULL) THEN
    RAISE EXCEPTION 'You must supply a valid Prospect Number.';
  END IF;

  NEW.prospect_number := UPPER(NEW.prospect_number);

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._prospecttrigger() OWNER TO admin;

--
-- Name: _prtrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _prtrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/EULA for the full text of the software license.
BEGIN
  --- clear the number from the issue cache
  PERFORM clearNumberIssue('PrNumber', NEW.pr_number);

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._prtrigger() OWNER TO admin;

--
-- Name: _quheadafterdeletetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _quheadafterdeletetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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

BEGIN

  DELETE
  FROM charass
  WHERE charass_target_type = 'QU'
    AND charass_target_id = OLD.quhead_id;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._quheadafterdeletetrigger() OWNER TO admin;

--
-- Name: _quheadtrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _quheadtrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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;
  _oldHoldType TEXT;
  _newHoldType TEXT;
  _p RECORD;
  _a RECORD;
  _w RECORD;
  _shiptoId INTEGER;
  _addrId INTEGER;
  _prjId INTEGER;
  _check BOOLEAN;
  _numGen CHAR(1);

BEGIN

  --  Checks
  SELECT checkPrivilege('MaintainQuotes') INTO _check;
  IF NOT (_check) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Quotes.';
  END IF;

  -- If this is imported, check the quote number
  IF (TG_OP = 'INSERT') THEN
    IF (NEW.quhead_imported) THEN
      SELECT fetchMetricText('QUNumberGeneration') INTO _numGen;
      IF ((NEW.quhead_number IS NULL) AND (_numGen='M')) THEN
        RAISE EXCEPTION 'You must supply a Quote Number.';
      ELSE
        IF ((NEW.quhead_number IS NOT NULL) AND (_numGen='A')) THEN
          RAISE EXCEPTION 'You may not supply a new Quote Number xTuple will generate the number.';
        ELSE
          IF ((NEW.quhead_number IS NULL) AND (_numGen='S')) THEN
            SELECT fetchsonumber() INTO NEW.quhead_number;
          ELSE
            IF (NEW.quhead_number IS NULL) THEN
              SELECT fetchqunumber() INTO NEW.quhead_number;
            END IF;
          END IF;
        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);
    ELSIF (fetchMetricText('QUNumberGeneration') = 'S') THEN
      --- 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
         RAISE EXCEPTION 'The order number may not be changed.';
       END IF;
    END IF;
  END IF;

  IF (TG_OP IN ('INSERT','UPDATE')) THEN
    -- Get Customer data
    IF (NEW.quhead_shipto_id IS NULL) THEN
      SELECT * INTO _p FROM (
      SELECT cust_number,cust_usespos,cust_blanketpos,cust_ffbillto,
	     cust_ffshipto,cust_name,cust_salesrep_id,cust_terms_id,cust_shipvia,
	     cust_commprcnt,cust_curr_id,cust_taxzone_id,
  	     addr_line1,addr_line2,addr_line3,addr_city,addr_state,addr_postalcode,addr_country,
	     shipto_id,shipto_addr_id,shipto_name,shipto_salesrep_id,shipto_shipvia,
	     shipto_shipchrg_id,shipto_shipform_id,shipto_commission,shipto_taxzone_id
      FROM custinfo
        LEFT OUTER JOIN cntct ON (cust_cntct_id=cntct_id)
        LEFT OUTER JOIN addr ON (cntct_addr_id=addr_id)
        LEFT OUTER JOIN shiptoinfo ON ((cust_id=shipto_cust_id) AND shipto_default)
      WHERE (cust_id=NEW.quhead_cust_id)
      UNION
      SELECT prospect_number,false,false,true,
	     true,prospect_name,prospect_salesrep_id,null,null,
	     null,null,prospect_taxzone_id,
  	     addr_line1,addr_line2,addr_line3,addr_city,addr_state,addr_postalcode,addr_country,
	     null,null,null,null,null,
	     null,null,null,null
      FROM prospect
        LEFT OUTER JOIN cntct ON (prospect_cntct_id=cntct_id)
        LEFT OUTER JOIN addr ON (cntct_addr_id=addr_id)
      WHERE (prospect_id=NEW.quhead_cust_id)) AS data;
    ELSE
      SELECT cust_creditstatus,cust_number,cust_usespos,cust_blanketpos,cust_ffbillto,
	     cust_ffshipto,cust_name,cust_salesrep_id,cust_terms_id,cust_shipvia,
	     cust_shipchrg_id,cust_shipform_id,cust_commprcnt,cust_curr_id,cust_taxzone_id,
  	     addr_line1,addr_line2,addr_line3,addr_city,addr_state,addr_postalcode,addr_country,
	     shipto_id,shipto_addr_id,shipto_name,shipto_salesrep_id,shipto_shipvia,
	     shipto_shipchrg_id,shipto_shipform_id,shipto_commission,shipto_taxzone_id INTO _p
      FROM shiptoinfo,custinfo
        LEFT OUTER JOIN cntct ON (cust_cntct_id=cntct_id)
        LEFT OUTER JOIN addr ON (cntct_addr_id=addr_id)
      WHERE ((cust_id=NEW.quhead_cust_id)
      AND (shipto_id=shipto_id));
    END IF;

    -- If there is customer data, then we can get to work
    IF (FOUND) THEN
      -- Only check PO number for imports because UI checks when whole quote is saved
      IF (TG_OP = 'INSERT') THEN
          -- Set to defaults if values not provided
          NEW.quhead_shipto_id		:= COALESCE(NEW.quhead_shipto_id,_p.shipto_id);
	  NEW.quhead_salesrep_id 	:= COALESCE(NEW.quhead_salesrep_id,_p.shipto_salesrep_id,_p.cust_salesrep_id);
          NEW.quhead_terms_id		:= COALESCE(NEW.quhead_terms_id,_p.cust_terms_id);
          NEW.quhead_shipvia		:= COALESCE(NEW.quhead_shipvia,_p.shipto_shipvia,_p.cust_shipvia);
          NEW.quhead_commission		:= COALESCE(NEW.quhead_commission,_p.shipto_commission,_p.cust_commprcnt);
          NEW.quhead_quotedate		:= COALESCE(NEW.quhead_quotedate,current_date);
          NEW.quhead_packdate		:= COALESCE(NEW.quhead_packdate,NEW.quhead_quotedate);
          NEW.quhead_curr_id		:= COALESCE(NEW.quhead_curr_id,_p.cust_curr_id,basecurrid());
          NEW.quhead_taxzone_id		:= COALESCE(NEW.quhead_taxzone_id,_p.shipto_taxzone_id,_p.cust_taxzone_id);
          NEW.quhead_freight		:= COALESCE(NEW.quhead_freight,0);
          NEW.quhead_custponumber	:= COALESCE(NEW.quhead_custponumber,'');
          NEW.quhead_ordercomments	:= COALESCE(NEW.quhead_ordercomments,'');
          NEW.quhead_shipcomments	:= COALESCE(NEW.quhead_shipcomments,'');
          NEW.quhead_shiptophone	:= COALESCE(NEW.quhead_shiptophone,'');
          NEW.quhead_misc		:= COALESCE(NEW.quhead_misc,0);
          NEW.quhead_misc_descrip	:= COALESCE(NEW.quhead_misc_descrip,'');

          IF ((NEW.quhead_warehous_id IS NULL) OR (NEW.quhead_fob IS NULL)) THEN
            IF (NEW.quhead_warehous_id IS NULL) THEN
              SELECT warehous_id,warehous_fob INTO _w
              FROM usrpref, whsinfo
              WHERE ((warehous_id=CAST(usrpref_value AS INTEGER))
                AND (warehous_shipping)
                AND (warehous_active)
                AND (usrpref_username=getEffectiveXtUser())
                AND (usrpref_name='PreferredWarehouse'));
            ELSE
              SELECT warehous_id,warehous_fob INTO _w
              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;
        IF (_check) THEN
          SELECT NEXTVAL('prj_prj_id_seq') INTO _prjId;
          NEW.quhead_prj_id := _prjId;
          INSERT INTO prj (prj_id, prj_number, prj_name, prj_descrip,
                           prj_status, prj_so, prj_wo, prj_po,
                           prj_owner_username, prj_start_date, prj_due_date,
                           prj_assigned_date, prj_completed_date, prj_username,
                           prj_recurring_prj_id, prj_crmacct_id,
                           prj_cntct_id, prj_prjtype_id)
          SELECT _prjId, NEW.quhead_number, NEW.quhead_number, 'Auto Generated Project from Quote.',
                 'O', TRUE, TRUE, TRUE,
                 getEffectiveXTUser(), NEW.quhead_quotedate, NEW.quhead_packdate,
                 NEW.quhead_quotedate, NULL, getEffectiveXTUser(),
                 NULL, crmacct_id,
                 NEW.quhead_billto_cntct_id, NULL
          FROM crmacct
          WHERE (crmacct_cust_id=NEW.quhead_cust_id)
             OR (crmacct_prospect_id=NEW.quhead_cust_id)
          LIMIT 1;
        END IF;
      END IF;

      -- Deal with Billing Address
      IF (TG_OP = 'INSERT') THEN
        IF (_p.cust_ffbillto) THEN
          -- If they didn't supply data, we'll put in the bill to address
          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_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,'');
        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_billtostate=COALESCE(_p.addr_state,'');
          NEW.quhead_billtozip=COALESCE(_p.addr_postalcode,'');
          NEW.quhead_billtocountry=COALESCE(_p.addr_country,'');
        END IF;
      END IF;

      -- Now let's look at Shipto Address
      -- 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')
       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)
       AND (NEW.quhead_shiptoaddress2 IS NULL)
       AND (NEW.quhead_shiptoaddress3 IS NULL)
       AND (NEW.quhead_shiptocity IS NULL)
       AND (NEW.quhead_shiptostate IS NULL)
       AND (NEW.quhead_shiptocountry IS NULL)) THEN
        IF ((NEW.quhead_shipto_id IS NULL) AND (_p.shipto_id IS NOT NULL)) THEN
          _shiptoId := _p.shipto_addr_id;
        ELSE
          _shiptoId := NEW.quhead_shipto_id;
        END IF;

        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_shiptostate := COALESCE(_a.addr_state,'');
        NEW.quhead_shiptozipcode := COALESCE(_a.addr_postalcode,'');
        NEW.quhead_shiptocountry := COALESCE(_a.addr_country,'');
      ELSE
        IF (_p.cust_ffshipto) THEN
          -- Use Address Save function to see if the new address entered matches
          -- data for the shipto number.  If not that will insert new address for CRM
          SELECT SaveAddr(
            NULL,
            NULL,
            NEW.quhead_shiptoaddress1,
            NEW.quhead_shiptoaddress2,
            NEW.quhead_shiptoaddress3,
            NEW.quhead_shiptocity,
            NEW.quhead_shiptostate,
            NEW.quhead_shiptozipcode,
            NEW.quhead_shiptocountry,
            'CHANGEONE') INTO _addrId;
          SELECT shipto_addr_id INTO _shiptoid FROM shiptoinfo WHERE (shipto_id=NEW.quhead_shipto_id);
           -- If the address passed doesn't match shipto address, then it's something else
           IF (_shiptoid <> _addrId) THEN
             NEW.quhead_shipto_id := NULL;
           END IF;
        ELSE
          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
            FROM shiptoinfo
            LEFT OUTER JOIN cntct ON (shipto_cntct_id=cntct_id)
            LEFT OUTER JOIN addr ON (shipto_addr_id=addr_id)
            WHERE (shipto_id=NEW.quhead_shipto_id);
            IF (FOUND) THEN
              -- Free form not allowed so we're going to make sure address matches Shipto data
              NEW.quhead_shiptoname := COALESCE(_a.shipto_name,'');
              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_shiptostate := COALESCE(_a.addr_state,'');
              NEW.quhead_shiptozipcode := COALESCE(_a.addr_postalcode,'');
              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 IF;
          END IF;
        END IF;
      END IF;
    END IF;
  END IF;

  IF ( SELECT (metric_value='t')
       FROM metric
       WHERE (metric_name='SalesOrderChangeLog') ) THEN

--  Cache the cmnttype_id for ChangeLog
    SELECT cmnttype_id INTO _cmnttypeid
    FROM cmnttype
    WHERE (cmnttype_name='ChangeLog');
    IF (FOUND) THEN
      IF (TG_OP = 'INSERT') THEN
        PERFORM postComment(_cmnttypeid, 'Q', NEW.quhead_id, 'Created');

      ELSIF (TG_OP = 'UPDATE') THEN

        IF (OLD.quhead_terms_id <> NEW.quhead_terms_id) THEN
          PERFORM postComment( _cmnttypeid, 'Q', NEW.quhead_id,
                               ('Terms Changed from "' || oldterms.terms_code || '" to "' || newterms.terms_code || '"') )
          FROM terms AS oldterms, terms AS newterms
          WHERE ( (oldterms.terms_id=OLD.quhead_terms_id)
           AND (newterms.terms_id=NEW.quhead_terms_id) );
        END IF;

      ELSIF (TG_OP = 'DELETE') THEN
        DELETE FROM comment
        WHERE ( (comment_source='Q')
         AND (comment_source_id=OLD.quhead_id) );
      END IF;
    END IF;
  END IF;

  IF (TG_OP = 'DELETE') THEN
    RETURN OLD;
  ELSE
    RETURN NEW;
  END IF;

END;
$$;


ALTER FUNCTION public._quheadtrigger() OWNER TO admin;

--
-- Name: _quitemafterdeletetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _quitemafterdeletetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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

BEGIN

  DELETE
  FROM charass
  WHERE charass_target_type = 'QI'
    AND charass_target_id = OLD.quitem_id;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._quitemafterdeletetrigger() OWNER TO admin;

--
-- Name: _quitemaftertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _quitemaftertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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;
BEGIN

  --If auto calculate freight, recalculate quhead_freight
  IF (SELECT quhead_calcfreight FROM quhead WHERE (quhead_id=NEW.quitem_quhead_id)) THEN
    UPDATE quhead SET quhead_freight =
      (SELECT SUM(freightdata_total) FROM freightDetail('QU',
                                                        quhead_id,
                                                        quhead_cust_id,
                                                        quhead_shipto_id,
                                                        quhead_quotedate,
                                                        quhead_shipvia,
                                                        quhead_curr_id))
    WHERE quhead_id=NEW.quitem_quhead_id;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._quitemaftertrigger() OWNER TO admin;

--
-- Name: _quitembeforetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _quitembeforetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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;
  _itemNumber TEXT;
BEGIN
  -- Check
  IF (NEW.quitem_scheddate IS NULL) THEN
  	RAISE EXCEPTION 'A schedule date is required.';
  END IF;

  -- 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)
     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_id=item_id)
       AND (charass_default)
       AND (char_id=charass_char_id))
       ORDER BY char_name) AS data;
   END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._quitembeforetrigger() OWNER TO admin;

--
-- Name: _quitemtrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _quitemtrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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;
  _check BOOLEAN;

BEGIN
  --  Checks
  SELECT checkPrivilege('MaintainQuotes') INTO _check;
  IF NOT (_check) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Quotes.';
  END IF;

  IF ( SELECT fetchMetricBool('SalesOrderChangeLog') ) THEN
    _changelog := TRUE;
  END IF;

  IF (TG_OP = 'INSERT') THEN
    IF (_changelog) THEN
      PERFORM postComment('ChangeLog', 'QI', NEW.quitem_id, 'Created');
    END IF;

    RETURN NEW;
  END IF;

  IF (TG_OP = 'DELETE') THEN
    DELETE FROM comment
    WHERE ( (comment_source='QI')
     AND (comment_source_id=OLD.quitem_id) );

    DELETE FROM charass
     WHERE ((charass_target_type='QI')
       AND  (charass_target_id=OLD.quitem_id));

    RETURN OLD;
  END IF;

  IF (TG_OP = 'UPDATE') THEN
    IF (_changelog) THEN
      IF (NEW.quitem_qtyord <> OLD.quitem_qtyord) THEN
        PERFORM postComment( 'ChangeLog', 'QI', NEW.quitem_id,
                             ( 'Changed Qty. Ordered from ' || formatQty(OLD.quitem_qtyord) ||
                               ' to ' || formatQty(NEW.quitem_qtyord) ) );
      END IF;

      IF (NEW.quitem_price <> OLD.quitem_price) THEN
        PERFORM postComment( 'ChangeLog', 'QI', NEW.quitem_id,
                             ( 'Changed Unit Price from ' || formatPrice(OLD.quitem_price) ||
                               ' to ' || formatPrice(NEW.quitem_price) ) );
      END IF;

      IF (NEW.quitem_scheddate <> OLD.quitem_scheddate) THEN
        PERFORM postComment( 'ChangeLog', 'QI', NEW.quitem_id,
                             ( 'Changed Sched. Date from ' || formatDate(OLD.quitem_scheddate) ||
                               ' to ' || formatDate(NEW.quitem_scheddate)) );
      END IF;
    END IF;
  END IF;

--  NEW.quitem_lastupdated = CURRENT_TIMESTAMP;

  RETURN NEW;

END;
$$;


ALTER FUNCTION public._quitemtrigger() OWNER TO admin;

--
-- Name: _recuraftertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _recuraftertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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
  _parentid   INTEGER;
  _parenttype TEXT;
BEGIN
  IF (TG_OP = 'DELETE') THEN
    IF (UPPER(OLD.recur_parent_type) = 'TODO') THEN
      UPDATE todoitem SET todoitem_recurring_todoitem_id=NULL
       WHERE (todoitem_recurring_todoitem_id=OLD.recur_parent_id);
    END IF;

    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._recuraftertrigger() OWNER TO admin;

--
-- Name: _reporttrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _reporttrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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

  NEW.report_loaddate = CURRENT_TIMESTAMP;
  RETURN NEW;

END;
$$;


ALTER FUNCTION public._reporttrigger() OWNER TO admin;

--
-- Name: _salesrepafterdeletetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _salesrepafterdeletetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
  IF (SELECT fetchMetricValue('DefaultSalesRep') = OLD.salesrep_id) THEN
    RAISE EXCEPTION 'Cannot delete the default Sales Rep [xtuple: salesrep, -1, %]',
                    OLD.salesrep_number;
  END IF;

  PERFORM postComment('ChangeLog', 'SR', OLD.salesrep_id,
                      'Deleted "' || OLD.salesrep_number || '"');

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._salesrepafterdeletetrigger() OWNER TO admin;

--
-- Name: _salesrepaftertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _salesrepaftertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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

BEGIN

  IF (TG_OP = 'INSERT') THEN
    -- http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE
    LOOP
      UPDATE crmacct SET crmacct_salesrep_id=NEW.salesrep_id,
                         crmacct_name=NEW.salesrep_name
      WHERE crmacct_number=NEW.salesrep_number;
      IF (FOUND) THEN
        EXIT;
      END IF;
      BEGIN
        INSERT INTO crmacct(crmacct_number,      crmacct_name,      crmacct_active,
                            crmacct_type,        crmacct_salesrep_id
                  ) VALUES (NEW.salesrep_number, NEW.salesrep_name, NEW.salesrep_active,
                            'I',                 NEW.salesrep_id);
        EXIT;
      EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
      END;
    END LOOP;

    -- TODO: default characteristic assignments?

  ELSIF (TG_OP = 'UPDATE') THEN
    UPDATE crmacct SET crmacct_number = NEW.salesrep_number
    WHERE ((crmacct_salesrep_id=NEW.salesrep_id)
      AND  (crmacct_number!=NEW.salesrep_number));

    UPDATE crmacct SET crmacct_name = NEW.salesrep_name
    WHERE ((crmacct_salesrep_id=NEW.salesrep_id)
      AND  (crmacct_name!=NEW.salesrep_name));
  END IF;

  IF (fetchMetricBool('SalesRepChangeLog')) THEN
      IF (TG_OP = 'INSERT') THEN
        PERFORM postComment('ChangeLog', 'SR', NEW.salesrep_id, 'Created');

      ELSIF (TG_OP = 'UPDATE') THEN
        IF (OLD.salesrep_active <> NEW.salesrep_active) THEN
          PERFORM postComment('ChangeLog', 'SR', NEW.salesrep_id,
                              CASE WHEN NEW.salesrep_active THEN 'Activated'
                                   ELSE 'Deactivated' END);
        END IF;

        IF (OLD.salesrep_number <> NEW.salesrep_number) THEN
          PERFORM postComment('ChangeLog', 'SR', NEW.salesrep_id,
                              'Number changed from "' || OLD.salesrep_number ||
                              '" to "' || NEW.salesrep_number || '"');
        END IF;

        IF (OLD.salesrep_name <> NEW.salesrep_name) THEN
          PERFORM postComment('ChangeLog', 'SR', NEW.salesrep_id,
                              'Name changed from "' || OLD.salesrep_name ||
                              '" to "' || NEW.salesrep_name || '"');
        END IF;

        IF (OLD.salesrep_commission <> NEW.salesrep_commission) THEN
          PERFORM postComment('ChangeLog', 'SR', NEW.salesrep_id,
                              'Commission changed from "' || OLD.salesrep_commission ||
                              '" to "' || NEW.salesrep_commission || '"');
        END IF;

        IF (OLD.salesrep_method <> NEW.salesrep_method) THEN
          PERFORM postComment('ChangeLog', 'SR', NEW.salesrep_id,
                              'Method changed from "' || OLD.salesrep_method ||
                              '" to "' || NEW.salesrep_method || '"');
        END IF;

    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._salesrepaftertrigger() OWNER TO admin;

--
-- Name: _salesrepbeforetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _salesrepbeforetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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('MaintainSalesReps')) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Sales Reps.';
  END IF;

  IF (TG_OP IN ('INSERT', 'UPDATE')) THEN
    IF (NEW.salesrep_number IS NULL) THEN
      RAISE EXCEPTION 'You must supply a valid Sales Rep Number.';
    END IF;

    IF (NEW.salesrep_commission IS NULL) THEN
      RAISE EXCEPTION 'You must supply a Commission Rate for this Sales Rep.';
    END IF;

    IF (TG_OP = 'INSERT' AND fetchMetricText('CRMAccountNumberGeneration') IN ('A','O') AND isNumeric(NEW.salesrep_number)) THEN
      --- clear the number from the issue cache
      PERFORM clearNumberIssue('CRMAccountNumber', NEW.salesrep_number);
    END IF;

    NEW.salesrep_number = UPPER(NEW.salesrep_number);

    -- deprecated column salesrep_emp_id
    -- TODO: will this prevent breaking the crmacct-emp relationship?
    IF (TG_OP = 'UPDATE') THEN
      SELECT crmacct_emp_id INTO NEW.salesrep_emp_id
        FROM crmacct
       WHERE crmacct_salesrep_id = NEW.salesrep_id;
    END IF;

  ELSIF (TG_OP = 'DELETE') THEN
    UPDATE crmacct SET crmacct_salesrep_id = NULL
     WHERE crmacct_salesrep_id = OLD.salesrep_id;
    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._salesrepbeforetrigger() OWNER TO admin;

--
-- Name: _saletypeaftertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _saletypeaftertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2015 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
--  If this Sale Type is marked "default" set all others to not default
  IF (NEW.saletype_default) THEN
    UPDATE saletype SET saletype_default = false
      WHERE saletype_id <> NEW.saletype_id;
  END IF;    

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._saletypeaftertrigger() OWNER TO admin;

--
-- Name: _saletypebeforedeletetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _saletypebeforedeletetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 TEXT;

BEGIN
--  Check to see if any sales orders are assigned to the passed saletype
  SELECT cohead_number INTO _check
  FROM cohead
  WHERE (cohead_saletype_id=OLD.saletype_id)
  LIMIT 1;
  IF (FOUND) THEN
    RAISE EXCEPTION 'Assigned to Sales Order % and possibly more. [xtuple: deletesaletype, -1]', _check;
  END IF;

--  Check to see if any quotes are assigned to the passed saletype
  SELECT quhead_number INTO _check
  FROM quhead
  WHERE (quhead_saletype_id=OLD.saletype_id)
  LIMIT 1;
  IF (FOUND) THEN
    RAISE EXCEPTION 'Assigned to Quote % and possibly more. [xtuple: deletesaletype, -2]', _check;
  END IF;

--  Check to see if any invoice are assigned to the passed saletype
  SELECT invchead_invcnumber INTO _check
  FROM invchead
  WHERE (invchead_saletype_id=OLD.saletype_id)
  LIMIT 1;
  IF (FOUND) THEN
    RAISE EXCEPTION 'Assigned to Invoice % and possibly more. [xtuple: deletesaletype, -3]', _check;
  END IF;

--  Check to see if any credit memos are assigned to the passed saletype
  SELECT cmhead_number INTO _check
  FROM cmhead
  WHERE (cmhead_saletype_id=OLD.saletype_id)
  LIMIT 1;
  IF (FOUND) THEN
    RAISE EXCEPTION 'Assigned to Credit Memo % and possibly more. [xtuple: deletesaletype, -4]', _check;
  END IF;

--  Check to see if any return ruthorizations are assigned to the passed saletype
  IF (fetchMetricBool('EnableReturnAuth')) THEN
    SELECT rahead_number INTO _check
    FROM rahead
    WHERE (rahead_saletype_id=OLD.saletype_id)
    LIMIT 1;
    IF (FOUND) THEN
      RAISE EXCEPTION 'Assigned to Return Authorization % and possibly more returns. [xtuple: deleteSaleType, -5]', _check;
    END IF;
  END IF;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._saletypebeforedeletetrigger() OWNER TO admin;

--
-- Name: _shipdatasumtrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _shipdatasumtrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 (LENGTH(TRIM(NEW.shipdatasum_shiphead_number)) = 0) THEN
    NEW.shipdatasum_shiphead_number = NULL;
  END IF;

  RETURN NEW;

END;
$$;


ALTER FUNCTION public._shipdatasumtrigger() OWNER TO admin;

--
-- Name: _shipdatatrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _shipdatatrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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
  _newShipdata_cohead_number 	INTEGER;
  _shipdatasum_shipper 		TEXT;
  _rows				INTEGER;
  _cohead_id 			INTEGER;
  _shiphead_number 		TEXT;
  _headcount			INTEGER;

BEGIN
  --  This is where the shipper is identified and may need to be changed
  NEW.shipdata_cohead_number := TRIM(NEW.shipdata_cohead_number);

  IF (substring(NEW.shipdata_cosmisc_tracknum from 1 for 2) = '1Z') THEN
    _shipdatasum_shipper := 'UPS';
  ELSE
    _shipdatasum_shipper := 'UNKNOWN';
  END IF;

  IF (LENGTH(TRIM(NEW.shipdata_shiphead_number)) = 0) THEN
    NEW.shipdata_shiphead_number := NULL;
  END IF;

  IF (NEW.shipdata_cosmisc_tracknum = NEW.shipdata_cosmisc_packnum_tracknum) THEN
    IF (NEW.shipdata_void_ind = 'Y') THEN
      --  Delete the current shipdatasum
      DELETE FROM shipdatasum
      WHERE ((shipdatasum_cohead_number = NEW.shipdata_cohead_number)
	AND  (shipdatasum_cosmisc_tracknum = NEW.shipdata_cosmisc_tracknum));

    ELSIF (TG_OP = 'INSERT') THEN

--      RAISE NOTICE 'Getting cohead_id (%)', NEW.shipdata_cohead_number;
      IF (NEW.shipdata_shiphead_number IS NULL) THEN
        SELECT cohead_id INTO _cohead_id FROM cohead WHERE cohead_number = NEW.shipdata_cohead_number;

        IF (FOUND) THEN

--          RAISE NOTICE 'Getting shiphead number (%)', _cohead_id;
          SELECT count(shiphead_order_id), MAX(shiphead_number) INTO _headcount, _shiphead_number 
          FROM shiphead 
          WHERE ((shiphead_tracknum IS NULL OR shiphead_tracknum = '') 
          AND ( shiphead_order_type = 'SO' and shiphead_order_id = _cohead_id) );
          
          IF (_headcount = 1) THEN
--            RAISE NOTICE 'Updating Shiphead Number (%)', _shiphead_number;
            NEW.shipdata_shiphead_number = _shiphead_number;
            
          ELSIF (_headcount > 1) THEN
            -- Trap for potential workflow problem.  Can only infer shiphead from sales order number 
            -- if shipping one at a time
            RAISE EXCEPTION 'Multiple shipments exist for this order.  Please provide a specific a shipment number.';
          END IF;
        END IF;
      END IF;

      INSERT INTO shipdatasum
	      (shipdatasum_cohead_number, shipdatasum_cosmisc_tracknum,
	       shipdatasum_cosmisc_packnum_tracknum, shipdatasum_weight,
	       shipdatasum_base_freight, shipdatasum_total_freight,
	       shipdatasum_base_freight_curr_id, shipdatasum_total_freight_curr_id,
	       shipdatasum_shipper, shipdatasum_billing_option,
	       shipdatasum_package_type, shipdatasum_shiphead_number)
       VALUES (NEW.shipdata_cohead_number, NEW.shipdata_cosmisc_tracknum,
	       NEW.shipdata_cosmisc_packnum_tracknum, NEW.shipdata_weight,
	       NEW.shipdata_base_freight, NEW.shipdata_total_freight,
	       NEW.shipdata_base_freight_curr_id, NEW.shipdata_total_freight_curr_id,
	       _shipdatasum_shipper, NEW.shipdata_billing_option,
	       NEW.shipdata_package_type, NEW.shipdata_shiphead_number);

    ELSIF (TG_OP = 'UPDATE') THEN
       UPDATE shipdatasum SET
	      shipdatasum_cohead_number=NEW.shipdata_cohead_number,
	      shipdatasum_cosmisc_tracknum=NEW.shipdata_cosmisc_tracknum,
	      shipdatasum_cosmisc_packnum_tracknum=NEW.shipdata_cosmisc_packnum_tracknum,
	      shipdatasum_weight=NEW.shipdata_weight,
	      shipdatasum_base_freight=NEW.shipdata_base_freight,
	      shipdatasum_total_freight=NEW.shipdata_total_freight,
	      shipdatasum_base_freight_curr_id=NEW.shipdata_base_freight_curr_id,
	      shipdatasum_total_freight_curr_id=NEW.shipdata_total_freight_curr_id,
	      shipdatasum_shipper=_shipdatasum_shipper,
	      shipdatasum_billing_option=NEW.shipdata_billing_option,
	      shipdatasum_package_type=NEW.shipdata_package_type,
	      shipdatasum_shiphead_number=NEW.shipdata_shiphead_number
       WHERE ((TRIM(shipdatasum_cohead_number)=TRIM(OLD.shipdata_cohead_number))
	  AND (TRIM(shipdatasum_cosmisc_tracknum)=TRIM(OLD.shipdata_cosmisc_tracknum))
	  AND (TRIM(shipdatasum_cosmisc_packnum_tracknum)=TRIM(OLD.shipdata_cosmisc_packnum_tracknum)));

       GET DIAGNOSTICS _rows = ROW_COUNT;
       IF (_rows <= 0) THEN
	 INSERT INTO shipdatasum
		(shipdatasum_cohead_number, shipdatasum_cosmisc_tracknum,
		 shipdatasum_cosmisc_packnum_tracknum, shipdatasum_weight,
		 shipdatasum_base_freight, shipdatasum_total_freight,
		 shipdatasum_base_freight_curr_id, shipdatasum_total_freight_curr_id,
		 shipdatasum_shipper, shipdatasum_billing_option,
		 shipdatasum_package_type, shipdatasum_shiphead_number)
	 VALUES (NEW.shipdata_cohead_number, NEW.shipdata_cosmisc_tracknum,
		 NEW.shipdata_cosmisc_packnum_tracknum, NEW.shipdata_weight,
		 NEW.shipdata_base_freight, NEW.shipdata_total_freight,
		 NEW.shipdata_base_freight_curr_id, NEW.shipdata_total_freight_curr_id,
		 _shipdatasum_shipper, NEW.shipdata_billing_option,
		 NEW.shipdata_package_type, NEW.shipdata_shiphead_number);
       END IF;
    END IF;
  END IF;

  RETURN NEW;

END;
$$;


ALTER FUNCTION public._shipdatatrigger() OWNER TO admin;

--
-- Name: _shipformafterdeletetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _shipformafterdeletetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 (fetchMetricValue('DefaultShipFormId') = OLD.shipform_id) THEN
    RAISE EXCEPTION 'Cannot delete the default Shipping Form [xtuple: shipform, -1, %, %]',
                    OLD.shipform_name, OLD.shipform_report_name;
  END IF;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._shipformafterdeletetrigger() OWNER TO admin;

--
-- Name: _shipheadbeforetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _shipheadbeforetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 ((TG_OP = 'INSERT') OR (TG_OP = 'UPDATE')) THEN

    IF (NEW.shiphead_order_type = 'SO'
	AND NEW.shiphead_order_id   IN (SELECT cohead_id FROM cohead)) THEN
      RETURN NEW;

    ELSEIF (NEW.shiphead_order_type = 'TO'
	AND NEW.shiphead_order_id   IN (SELECT tohead_id FROM tohead)) THEN
      RETURN NEW;

    END IF;

    RAISE EXCEPTION '% with id % does not exist',
		    NEW.shiphead_order_type, NEW.shiphead_order_id;
    RETURN OLD;

  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._shipheadbeforetrigger() OWNER TO admin;

--
-- Name: _shiptoinfoaftertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _shiptoinfoaftertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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;

BEGIN
  IF (NEW.shipto_default) THEN
    UPDATE shiptoinfo
    SET shipto_default = false
    WHERE ((shipto_cust_id=NEW.shipto_cust_id)
    AND (shipto_id <> NEW.shipto_id));
  END IF;

  IF (SELECT fetchMetricBool('CustomerChangeLog')) THEN
--  Cache the cmnttype_id for ChangeLog
    SELECT cmnttype_id INTO _cmnttypeid
    FROM cmnttype
    WHERE (cmnttype_name='ChangeLog');
    IF (FOUND) THEN
      IF (TG_OP = 'INSERT') THEN
        PERFORM postComment(_cmnttypeid, 'C', NEW.shipto_cust_id, 'Created');

      ELSIF (TG_OP = 'UPDATE') THEN
        IF (OLD.shipto_name <> NEW.shipto_name) THEN
          PERFORM postComment( _cmnttypeid, 'C', NEW.shipto_cust_id,
                               ( NEW.shipto_name || ': Ship To Name Changed from "' || COALESCE(OLD.shipto_name, '') ||
                                 '" to "' || COALESCE(NEW.shipto_name, '') || '"' ) );
        END IF;
        IF (OLD.shipto_shipvia <> NEW.shipto_shipvia) THEN
          PERFORM postComment( _cmnttypeid, 'C', NEW.shipto_cust_id,
                               ( NEW.shipto_name || ': Ship To ShipVia Changed from "' || COALESCE(OLD.shipto_shipvia, '') ||
                                 '" to "' || COALESCE(NEW.shipto_shipvia, '') || '"' ) );
        END IF;
        IF (COALESCE(OLD.shipto_taxzone_id, -1) <> COALESCE(NEW.shipto_taxzone_id, -1)) THEN
          PERFORM postComment( _cmnttypeid, 'C', NEW.shipto_cust_id,
                               ( NEW.shipto_name || ': Ship To Tax Zone Changed from "' || COALESCE((SELECT taxzone_code
                                                                                            FROM taxzone
                                                                                            WHERE taxzone_id=OLD.shipto_taxzone_id), 'None') ||
                                 '" to "' || COALESCE((SELECT taxzone_code
                                              FROM taxzone
                                              WHERE taxzone_id=NEW.shipto_taxzone_id), 'None') || '"' ) );
        END IF;
        IF (OLD.shipto_shipzone_id <> NEW.shipto_shipzone_id) THEN
          PERFORM postComment( _cmnttypeid, 'C', NEW.shipto_cust_id,
                               ( NEW.shipto_name || ': Ship To Shipping Zone Changed from "' || (SELECT shipzone_name
                                                                                                 FROM shipzone
                                                                                                 WHERE shipzone_id=OLD.shipto_shipzone_id) ||
                                 '" to "' || (SELECT shipzone_name
                                              FROM shipzone
                                              WHERE shipzone_id=NEW.shipto_shipzone_id) || '"' ) );
        END IF;
        IF (OLD.shipto_salesrep_id <> NEW.shipto_salesrep_id) THEN
          PERFORM postComment( _cmnttypeid, 'C', NEW.shipto_cust_id,
                               ( NEW.shipto_name || ': Ship To Sales Rep Changed from "' || (SELECT salesrep_name
                                                                                             FROM salesrep
                                                                                             WHERE salesrep_id=OLD.shipto_salesrep_id) ||
                                 '" to "' || (SELECT salesrep_name
                                              FROM salesrep
                                              WHERE salesrep_id=NEW.shipto_salesrep_id) || '"' ) );
        END IF;
        IF (OLD.shipto_active <> NEW.shipto_active) THEN
          IF (NEW.shipto_active) THEN
            PERFORM postComment(_cmnttypeid, 'C', NEW.shipto_cust_id, (NEW.shipto_name || ': Ship To Activated'));
          ELSE
            PERFORM postComment(_cmnttypeid, 'C', NEW.shipto_cust_id, (NEW.shipto_name || ': Ship To Deactivated'));
          END IF;
        END IF;
      END IF;
    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._shiptoinfoaftertrigger() OWNER TO admin;

--
-- Name: _shipviaafterdeletetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _shipviaafterdeletetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 (SELECT fetchMetricValue('DefaultShipViaId') = OLD.shipvia_id) THEN
    RAISE EXCEPTION 'Cannot delete the default Ship-Via [xtuple: shipvia, -1, %]',
                    OLD.shipvia_code;
  END IF;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._shipviaafterdeletetrigger() OWNER TO admin;

--
-- Name: _sltransaltertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _sltransaltertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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
  _externalCompany      BOOLEAN := false;
  _updated BOOLEAN := false;
BEGIN
  IF(TG_OP='DELETE') THEN
    RAISE EXCEPTION 'You may not delete Journal Transactions once they have been created.';
  ELSIF (TG_OP = 'UPDATE') THEN	
    IF(OLD.sltrans_id != NEW.sltrans_id) THEN
      _updated := true;
    ELSIF(OLD.sltrans_date != NEW.sltrans_date) THEN
      _updated := true;
    ELSIF(OLD.sltrans_accnt_id != NEW.sltrans_accnt_id) THEN
      _updated := true;
    ELSIF(OLD.sltrans_amount != NEW.sltrans_amount) THEN
      _updated := true;
    ELSIF(OLD.sltrans_username != NEW.sltrans_username) THEN
      _updated := true;
    ELSIF( (OLD.sltrans_sequence IS NULL     AND NEW.sltrans_sequence IS NOT NULL)
        OR (OLD.sltrans_sequence IS NOT NULL AND NEW.sltrans_sequence IS NULL)
        OR (COALESCE(OLD.sltrans_sequence,0) != COALESCE(NEW.sltrans_sequence,0)) ) THEN
      _updated := true;
    ELSIF( (OLD.sltrans_created IS NULL     AND NEW.sltrans_created IS NOT NULL)
        OR (OLD.sltrans_created IS NOT NULL AND NEW.sltrans_created IS NULL)
        OR (COALESCE(OLD.sltrans_created,now()) != COALESCE(NEW.sltrans_created,now())) ) THEN
      _updated := true;
    ELSIF( (OLD.sltrans_source IS NULL     AND NEW.sltrans_source IS NOT NULL)
        OR (OLD.sltrans_source IS NOT NULL AND NEW.sltrans_source IS NULL)
        OR (COALESCE(OLD.sltrans_source,'') != COALESCE(NEW.sltrans_source,'')) ) THEN
      _updated := true;
    ELSIF( (OLD.sltrans_docnumber IS NULL     AND NEW.sltrans_docnumber IS NOT NULL)
        OR (OLD.sltrans_docnumber IS NOT NULL AND NEW.sltrans_docnumber IS NULL)
        OR (COALESCE(OLD.sltrans_docnumber,'') != COALESCE(NEW.sltrans_docnumber,'')) ) THEN
      _updated := true;
    ELSIF( (OLD.sltrans_doctype IS NULL     AND NEW.sltrans_doctype IS NOT NULL)
        OR (OLD.sltrans_doctype IS NOT NULL AND NEW.sltrans_doctype IS NULL)
        OR (COALESCE(OLD.sltrans_doctype,'') != COALESCE(NEW.sltrans_doctype,'')) ) THEN
      _updated := true;
    END IF;

    IF(_updated) THEN
      RAISE EXCEPTION 'You may not alter some Journal Transaction fields once they have been created.';
    END IF;
  ELSE
    RAISE EXCEPTION 'trigger for sltrans table called in unexpected state.';
  END IF;
  RETURN NEW;
END;
$$;


ALTER FUNCTION public._sltransaltertrigger() OWNER TO admin;

--
-- Name: _sltransinserttrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _sltransinserttrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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
  _reqNotes BOOLEAN;
  _externalCompany      BOOLEAN := false;
BEGIN
  -- Checks
  SELECT company_external INTO _externalCompany
  FROM company JOIN accnt ON (company_number=accnt_company)
  WHERE (accnt_id=NEW.sltrans_accnt_id);
  IF (_externalCompany) THEN
    RAISE EXCEPTION 'Transactions are not allowed for G/L Accounts with External Company segments.';
  END IF;
  
  RETURN NEW;
END;
$$;


ALTER FUNCTION public._sltransinserttrigger() OWNER TO admin;

--
-- Name: _soheadtrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _soheadtrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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;
  _a RECORD;
  _w RECORD;
  _shiptoId INTEGER;
  _addrId INTEGER;
  _prjId INTEGER;
  _check BOOLEAN;
  _numGen CHAR(1);

BEGIN

  -- Checks
  -- Start with privileges
  IF (TG_OP = 'INSERT') THEN
    IF ( (NOT checkPrivilege('MaintainSalesOrders')) AND
       (NOT checkPrivilege('EnterReceipts')) ) THEN
      RAISE EXCEPTION 'You do not have privileges to create a Sales Order.';
    END IF;
  ELSIF (TG_OP = 'UPDATE') THEN
    IF ( (NOT checkPrivilege('MaintainSalesOrders')) AND
         (NOT checkPrivilege('IssueStockToShipping')) AND
         (NEW.cohead_holdtype = OLD.cohead_holdtype) ) THEN
      RAISE EXCEPTION 'You do not have privileges to alter a Sales Order.';
    END IF;
  ELSE
    IF ( (NOT checkPrivilege('MaintainSalesOrders')) AND
         (NOT checkPrivilege('IssueStockToShipping')) ) THEN
      RAISE EXCEPTION 'You do not have privileges to alter a Sales Order.';
    END IF;
  END IF;

  -- If this is imported, check the order number
  IF (TG_OP = 'INSERT') THEN
    IF (NEW.cohead_imported) THEN
      SELECT fetchMetricText('CONumberGeneration') INTO _numGen;
      IF ((NEW.cohead_number IS NULL) AND (_numGen='M')) THEN
        RAISE EXCEPTION 'You must supply an Order Number.';
      ELSE
        IF (NEW.cohead_number IS NULL) THEN
          SELECT fetchsonumber() INTO NEW.cohead_number;
        END IF;
      END IF;
    END IF;

    IF (fetchMetricText('CONumberGeneration') IN ('A','O')) THEN
      --- clear the number from the issue cache
      PERFORM clearNumberIssue('SoNumber', NEW.cohead_number);
    END IF;
  ELSE
    IF (TG_OP = 'UPDATE') THEN
      IF (NEW.cohead_number <> OLD.cohead_number) THEN
        RAISE EXCEPTION 'The order number may not be changed.';
      END IF;
    END IF;
  END IF;

  IF (TG_OP IN ('INSERT','UPDATE')) THEN

    -- Get Customer data
    IF (NEW.cohead_shipto_id IS NULL) THEN
      SELECT cust_creditstatus,cust_number,cust_usespos,cust_blanketpos,cust_ffbillto,
	     cust_ffshipto,cust_name,cust_salesrep_id,cust_terms_id,cust_shipvia,
	     cust_shipchrg_id,cust_shipform_id,cust_commprcnt,cust_curr_id,cust_taxzone_id,
  	     cntct.*,addr.*,
	     shipto_id,shipto_addr_id,shipto_name,shipto_salesrep_id,shipto_shipvia,
	     shipto_shipchrg_id,shipto_shipform_id,shipto_commission,shipto_taxzone_id INTO _p
      FROM custinfo
        LEFT OUTER JOIN cntct ON (cust_cntct_id=cntct_id)
        LEFT OUTER JOIN addr ON (cntct_addr_id=addr_id)
        LEFT OUTER JOIN shiptoinfo ON ((cust_id=shipto_cust_id) AND shipto_default)
      WHERE (cust_id=NEW.cohead_cust_id);
    ELSE
      SELECT cust_creditstatus,cust_number,cust_usespos,cust_blanketpos,cust_ffbillto,
	     cust_ffshipto,cust_name,cust_salesrep_id,cust_terms_id,cust_shipvia,
	     cust_shipchrg_id,cust_shipform_id,cust_commprcnt,cust_curr_id,cust_taxzone_id,
  	     cntct.*,addr.*,
	     shipto_id,shipto_addr_id,shipto_name,shipto_salesrep_id,shipto_shipvia,
	     shipto_shipchrg_id,shipto_shipform_id,shipto_commission,shipto_taxzone_id INTO _p
      FROM shiptoinfo,custinfo
        LEFT OUTER JOIN cntct ON (cust_cntct_id=cntct_id)
        LEFT OUTER JOIN addr ON (cntct_addr_id=addr_id)
      WHERE ((cust_id=NEW.cohead_cust_id)
        AND  (shipto_id=NEW.cohead_shipto_id));
    END IF;

    -- If there is customer data, then we can get to work
    IF (FOUND) THEN

      -- Check Credit
      IF (TG_OP = 'INSERT') THEN
          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
                               for the Customer.',_p.cust_number;
            ELSE
              NEW.cohead_holdtype='C';
            END IF;
          END IF;
          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
                              new Sales Order for the Customer.',_p.cust_number;
            ELSE
              NEW.cohead_holdtype='C';
            END IF;
          END IF;

          -- Set to defaults if values not provided
          NEW.cohead_shipto_id 	:= COALESCE(NEW.cohead_shipto_id,_p.shipto_id);
          NEW.cohead_terms_id		:= COALESCE(NEW.cohead_terms_id,_p.cust_terms_id);
          NEW.cohead_orderdate		:= COALESCE(NEW.cohead_orderdate,current_date);
          NEW.cohead_packdate		:= COALESCE(NEW.cohead_packdate,NEW.cohead_orderdate);
          NEW.cohead_curr_id		:= COALESCE(NEW.cohead_curr_id,_p.cust_curr_id,basecurrid());
          NEW.cohead_freight		:= COALESCE(NEW.cohead_freight,0);
          NEW.cohead_custponumber	:= COALESCE(NEW.cohead_custponumber,'');
          NEW.cohead_ordercomments	:= COALESCE(NEW.cohead_ordercomments,'');
          NEW.cohead_shipcomments	:= COALESCE(NEW.cohead_shipcomments,'');
          NEW.cohead_shiptophone	:= COALESCE(NEW.cohead_shiptophone,'');
          NEW.cohead_misc		:= COALESCE(NEW.cohead_misc,0);
          NEW.cohead_misc_descrip	:= COALESCE(NEW.cohead_misc_descrip,'');
          NEW.cohead_shipcomplete	:= COALESCE(NEW.cohead_shipcomplete,false);

          IF (_p.shipto_id IS NOT NULL) THEN -- Pull in over ride values
	    NEW.cohead_salesrep_id 	:= COALESCE(NEW.cohead_salesrep_id,_p.shipto_salesrep_id);
	    NEW.cohead_shipvia		:= COALESCE(NEW.cohead_shipvia,_p.shipto_shipvia);
	    NEW.cohead_shipchrg_id	:= COALESCE(NEW.cohead_shipchrg_id,_p.shipto_shipchrg_id);
	    NEW.cohead_shipform_id	:= COALESCE(NEW.cohead_shipform_id,_p.shipto_shipform_id);
	    NEW.cohead_commission	:= COALESCE(NEW.cohead_commission,_p.shipto_commission);
	    IF (NEW.cohead_taxzone_id=-1) THEN
	      NEW.cohead_taxzone_id 	:= NULL;
	    ELSE
	      NEW.cohead_taxzone_id	:= COALESCE(NEW.cohead_taxzone_id,_p.shipto_taxzone_id);
	    END IF;
	  ELSE
	    NEW.cohead_salesrep_id 	:= COALESCE(NEW.cohead_salesrep_id,_p.cust_salesrep_id);
	    NEW.cohead_shipvia		:= COALESCE(NEW.cohead_shipvia,_p.cust_shipvia);
	    NEW.cohead_shipchrg_id	:= COALESCE(NEW.cohead_shipchrg_id,_p.cust_shipchrg_id);
	    NEW.cohead_shipform_id	:= COALESCE(NEW.cohead_shipform_id,_p.cust_shipform_id);
	    NEW.cohead_commission	:= COALESCE(NEW.cohead_commission,_p.cust_commprcnt);
	    IF (NEW.cohead_taxzone_id=-1) THEN
	      NEW.cohead_taxzone_id 	:= NULL;
	    ELSE
	      NEW.cohead_taxzone_id	:= COALESCE(NEW.cohead_taxzone_id,_p.cust_taxzone_id);
	    END IF;
          END IF;

          IF ((NEW.cohead_warehous_id IS NULL) OR (NEW.cohead_fob IS NULL)) THEN
            IF (NEW.cohead_warehous_id IS NULL) THEN
              SELECT warehous_id,warehous_fob INTO _w
              FROM usrpref, whsinfo
              WHERE ((warehous_id=CAST(usrpref_value AS INTEGER))
                AND (warehous_shipping)
                AND (warehous_active)
                AND (usrpref_username=getEffectiveXtUser())
                AND (usrpref_name='PreferredWarehouse'));
            ELSE
              SELECT warehous_id,warehous_fob INTO _w
              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 (NEW.cohead_imported) THEN

        -- Check for required Purchase Order
        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
          FROM cohead
          WHERE ((cohead_cust_id=NEW.cohead_cust_id)
          AND  (cohead_id<>NEW.cohead_id)
          AND  (UPPER(cohead_custponumber) = UPPER(NEW.cohead_custponumber)) )
          UNION
          SELECT quhead_id
          FROM quhead
          WHERE ((quhead_cust_id=NEW.cohead_cust_id)
          AND  (quhead_id<>NEW.cohead_id)
          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
                            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
                            existing Sales Order.';
         END IF;
        END IF;
      END IF;

      --Auto create project if applicable
      IF ((TG_OP = 'INSERT') AND (COALESCE(NEW.cohead_prj_id,-1)=-1)) THEN
        SELECT fetchMetricBool('AutoCreateProjectsForOrders') INTO _check;
        IF (_check) THEN
          SELECT NEXTVAL('prj_prj_id_seq') INTO _prjId;
          NEW.cohead_prj_id := _prjId;
          INSERT INTO prj (prj_id, prj_number, prj_name, prj_descrip,
                           prj_status, prj_so, prj_wo, prj_po,
                           prj_owner_username, prj_start_date, prj_due_date,
                           prj_assigned_date, prj_completed_date, prj_username,
                           prj_recurring_prj_id, prj_crmacct_id,
                           prj_cntct_id, prj_prjtype_id)
          SELECT _prjId, NEW.cohead_number, NEW.cohead_number, 'Auto Generated Project from Sales Order.',
                 'O', TRUE, TRUE, TRUE,
                 getEffectiveXTUser(), NEW.cohead_orderdate, NEW.cohead_packdate,
                 NEW.cohead_orderdate, NULL, getEffectiveXTUser(),
                 NULL, crmacct_id,
                 NEW.cohead_billto_cntct_id, NULL
          FROM crmacct
          WHERE (crmacct_cust_id=NEW.cohead_cust_id);
        END IF;
      END IF;

      IF (TG_OP = 'UPDATE') THEN
        SELECT true INTO _check
        FROM coitem
        WHERE ( (coitem_status='C')
        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')
                   AND  (coitem_order_id=pr_id));

        PERFORM changeWoProject(coitem_order_id, NEW.cohead_prj_id, TRUE)
                    FROM coitem
                    WHERE ((coitem_cohead_id=NEW.cohead_id)
                    AND  (coitem_order_type='W'));
        ELSE
          IF NEW.cohead_prj_id <> COALESCE(OLD.cohead_prj_id,-1) THEN
            RAISE EXCEPTION 'You can not change the project ID on orders with closed lines.';
          END IF;
        END IF;
      END IF;

      -- Deal with Billing Address
      IF (TG_OP = 'INSERT') THEN
        IF (_p.cust_ffbillto) THEN
          -- If they didn't supply data, we'll put in the bill to contact and address
          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_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_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_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,'');
        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_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_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_billtostate=COALESCE(_p.addr_state,'');
          NEW.cohead_billtozipcode=COALESCE(_p.addr_postalcode,'');
          NEW.cohead_billtocountry=COALESCE(_p.addr_country,'');
        END IF;
      END IF;

      -- Now let's look at Shipto Address
      -- 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')
        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)
        AND (NEW.cohead_shipto_cntct_first_name IS NULL)
        AND (NEW.cohead_shipto_cntct_middle IS NULL)
        AND (NEW.cohead_shipto_cntct_last_name IS NULL)
        AND (NEW.cohead_shipto_cntct_suffix IS NULL)
        AND (NEW.cohead_shipto_cntct_phone IS NULL)
        AND (NEW.cohead_shipto_cntct_title IS NULL)
        AND (NEW.cohead_shipto_cntct_fax IS NULL)
        AND (NEW.cohead_shipto_cntct_email IS NULL)
        AND (NEW.cohead_shiptoname IS NULL)
        AND (NEW.cohead_shiptoaddress1 IS NULL)
        AND (NEW.cohead_shiptoaddress2 IS NULL)
        AND (NEW.cohead_shiptoaddress3 IS NULL)
        AND (NEW.cohead_shiptocity IS NULL)
        AND (NEW.cohead_shiptostate IS NULL)
        AND (NEW.cohead_shiptocountry IS NULL)) THEN
        IF ((NEW.cohead_shipto_id IS NULL) AND (_p.shipto_id IS NOT NULL)) THEN
          _shiptoId := _p.shipto_addr_id;
        ELSE
          _shiptoId := NEW.cohead_shipto_id;
        END IF;

        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)
        WHERE (shipto_id=_shiptoId);

        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_suffix := COALESCE(_a.cntct_suffix,'');
	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_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_shiptostate := COALESCE(_a.addr_state,'');
        NEW.cohead_shiptozipcode := COALESCE(_a.addr_postalcode,'');
        NEW.cohead_shiptocountry := COALESCE(_a.addr_country,'');
      ELSE
        IF (_p.cust_ffshipto) THEN
          -- Use Address Save function to see if the new address entered matches
          -- data for the shipto number.  If not that will insert new address for CRM
          SELECT SaveAddr(
            NULL,
            NULL,
            NEW.cohead_shiptoaddress1,
            NEW.cohead_shiptoaddress2,
            NEW.cohead_shiptoaddress3,
            NEW.cohead_shiptocity,
            NEW.cohead_shiptostate,
            NEW.cohead_shiptozipcode,
            NEW.cohead_shiptocountry,
            'CHANGEONE') INTO _addrId;
          SELECT shipto_addr_id INTO _shiptoid FROM shiptoinfo WHERE (shipto_id=NEW.cohead_shipto_id);
          -- If the address passed doesn't match shipto address, then it's something else
          IF (_shiptoid <> _addrId) THEN
            NEW.cohead_shipto_id := NULL;
          END IF;
        ELSE
          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
            FROM shiptoinfo
              LEFT OUTER JOIN cntct ON (shipto_cntct_id=cntct_id)
              LEFT OUTER JOIN addr ON (shipto_addr_id=addr_id)
            WHERE (shipto_id=NEW.cohead_shipto_id);
            IF (FOUND) THEN
              -- Free form not allowed so we're going to make sure address matches Shipto data
              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_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_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_shiptostate := COALESCE(_a.addr_state,'');
              NEW.cohead_shiptozipcode := COALESCE(_a.addr_postalcode,'');
              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.';
            END IF;
          END IF;
        END IF;
      END IF;
    END IF;
  END IF;

  IF ( SELECT (metric_value='t')
       FROM metric
       WHERE (metric_name='SalesOrderChangeLog') ) THEN

    IF (TG_OP = 'INSERT') THEN
      PERFORM postComment('ChangeLog', 'S', NEW.cohead_id, 'Created');

    ELSIF (TG_OP = 'UPDATE') THEN

      IF ( (OLD.cohead_terms_id <> NEW.cohead_terms_id) AND
           (OLD.cohead_cust_id = NEW.cohead_cust_id) ) THEN
        PERFORM postComment( 'ChangeLog', 'S', NEW.cohead_id,
                             ('Terms Changed from "' || oldterms.terms_code || '" to "' || newterms.terms_code || '"') )
        FROM terms AS oldterms, terms AS newterms
        WHERE ( (oldterms.terms_id=OLD.cohead_terms_id)
         AND (newterms.terms_id=NEW.cohead_terms_id) );
      END IF;

      IF ( (OLD.cohead_shipvia <> NEW.cohead_shipvia) AND
           (OLD.cohead_cust_id = NEW.cohead_cust_id) ) THEN
        PERFORM postComment ('ChangeLog', 'S', New.cohead_id, ('Shipvia Changed from "' || OLD.cohead_shipvia || '" to "' || NEW.cohead_shipvia || '"'));
      END IF;

      IF (OLD.cohead_holdtype <> NEW.cohead_holdtype) THEN
        PERFORM postComment( 'ChangeLog', 'S', NEW.cohead_id,
                             ( 'Hold Type Changed from ' || (CASE OLD.cohead_holdtype WHEN('N') THEN 'No Hold'
                                                                                      WHEN('C') THEN 'Credit Hold'
                                                                                      WHEN('P') THEN 'Packing Hold'
                                                                                      WHEN('S') THEN 'Shipping Hold'
                                                                                      ELSE 'Unknown/Error' END) ||
                               ' to ' || (CASE NEW.cohead_holdtype WHEN('N') THEN 'No Hold'
                                                                   WHEN('C') THEN 'Credit Hold'
                                                                   WHEN('P') THEN 'Packing Hold'
                                                                   WHEN('S') THEN 'Shipping Hold'
                                                                   ELSE 'Unknown/Error' END) ) );
      END IF;

    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) );
    END IF;
  END IF;

  IF (TG_OP = 'UPDATE') THEN
    IF ( (NOT (OLD.cohead_holdtype = 'N')) AND
         (NEW.cohead_holdtype='N') ) THEN
      PERFORM postEvent('SoReleased', 'S', NEW.cohead_id,
                        NEW.cohead_warehous_id, NEW.cohead_number,
                        NULL, NULL, NULL, NULL);
    END IF;

    IF (OLD.cohead_ordercomments <> NEW.cohead_ordercomments) THEN
      PERFORM postEvent('SoNotesChanged', 'S', NEW.cohead_id,
                        NEW.cohead_warehous_id, NEW.cohead_number,
                        NULL, NULL, NULL, NULL);
    END IF;

    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
        shiphead_shipchrg_id=
	     CASE WHEN (NEW.cohead_shipchrg_id <= 0) THEN NULL
	          ELSE NEW.cohead_shipchrg_id
	     END,
        shiphead_freight=NEW.cohead_freight,
        shiphead_shipvia=NEW.cohead_shipvia
      WHERE ((shiphead_order_type='SO')
      AND  (shiphead_order_id=NEW.cohead_id)
      AND  (NOT shiphead_shipped));
    END IF;
  END IF;

  IF (TG_OP = 'DELETE') THEN
    RETURN OLD;
  ELSE
    NEW.cohead_lastupdated = CURRENT_TIMESTAMP;

    RETURN NEW;
  END IF;

END;
$$;


ALTER FUNCTION public._soheadtrigger() OWNER TO admin;

--
-- Name: _soheadtriggerafter(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _soheadtriggerafter() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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
    WHERE ((itemsite_id=coitem_itemsite_id)
     AND (coitem_cohead_id=NEW.cohead_id));
  END IF;

  -- update comments on any associated drop ship POs
  IF (COALESCE(NEW.cohead_shipcomments, TEXT('')) <> COALESCE(OLD.cohead_shipcomments, TEXT(''))) THEN
    UPDATE pohead SET pohead_comments=NEW.cohead_shipcomments
    FROM poitem JOIN coitem ON (coitem_cohead_id=NEW.cohead_id AND coitem_order_type='P' AND coitem_order_id=poitem_id)
    WHERE (pohead_id=poitem_pohead_id);
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._soheadtriggerafter() OWNER TO admin;

--
-- Name: _soitemafterdeletetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _soitemafterdeletetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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

BEGIN

  IF (OLD.coitem_status = 'O') THEN
    IF ( (SELECT (count(*) < 1)
          FROM coitem
          WHERE ((coitem_cohead_id=OLD.coitem_cohead_id)
            AND  (coitem_id != OLD.coitem_id)
            AND  (coitem_status = 'O')) ) ) THEN
      UPDATE cohead SET cohead_status = 'C'
      WHERE ((cohead_id=OLD.coitem_cohead_id)
        AND  (cohead_status='O'));
    END IF;
  END IF;

  DELETE
  FROM charass
  WHERE charass_target_type = 'SI'
    AND charass_target_id = OLD.coitem_id;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._soitemafterdeletetrigger() OWNER TO admin;

--
-- Name: _soitemaftertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _soitemaftertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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;
  _r RECORD;
  _kit BOOLEAN;
  _fractional BOOLEAN;
  _rec RECORD;
  _kstat TEXT;
  _pstat TEXT;
  _result INTEGER;
  _coheadnumber TEXT;
  _coitemid INTEGER;
  _itemsrcid INTEGER;
  _orderid INTEGER;

BEGIN

  _rec := NEW;

  --Cache some information
  SELECT * INTO _r
  FROM cohead
  WHERE (cohead_id=_rec.coitem_cohead_id);

  --Determine if this is a kit for later processing
  SELECT COALESCE(item_type,'')='K', item_fractional
    INTO _kit, _fractional
    FROM itemsite, item
   WHERE((itemsite_item_id=item_id)
     AND (itemsite_id=_rec.coitem_itemsite_id));
  _kit := COALESCE(_kit, false);
  _fractional := COALESCE(_fractional, false);

  IF (_kit) THEN
  -- Kit Processing
    IF (TG_OP = 'INSERT') THEN
  -- Create Sub Lines for Kit Components
      PERFORM explodeKit(NEW.coitem_cohead_id, NEW.coitem_linenumber, 0, NEW.coitem_itemsite_id,
                         NEW.coitem_qtyord, NEW.coitem_scheddate, NEW.coitem_promdate, NEW.coitem_memo);
      IF (fetchMetricBool('KitComponentInheritCOS')) THEN
  -- Update kit line item COS
        UPDATE coitem
        SET coitem_cos_accnt_id = CASE WHEN (COALESCE(NEW.coitem_cos_accnt_id, -1) != -1)
                                         THEN NEW.coitem_cos_accnt_id
                                       WHEN (NEW.coitem_warranty)
                                         THEN resolveCOWAccount(NEW.coitem_itemsite_id, _r.cohead_cust_id, _r.cohead_saletype_id, _r.cohead_shipzone_id)
                                       ELSE resolveCOSAccount(NEW.coitem_itemsite_id, _r.cohead_cust_id, _r.cohead_saletype_id, _r.cohead_shipzone_id)
                                  END
        WHERE((coitem_cohead_id=NEW.coitem_cohead_id)
          AND (coitem_linenumber = NEW.coitem_linenumber)
          AND (coitem_subnumber > 0));
      END IF;
    END IF;
    IF (TG_OP = 'UPDATE') THEN
      IF (NEW.coitem_qtyord <> OLD.coitem_qtyord) THEN
  -- Recreate Sub Lines for Kit Components
      FOR _coitemid IN
        SELECT coitem_id
        FROM coitem
        WHERE ( (coitem_cohead_id=OLD.coitem_cohead_id)
          AND   (coitem_linenumber=OLD.coitem_linenumber)
          AND   (coitem_subnumber > 0) )
        LOOP
          SELECT deleteSoItem(_coitemid) INTO _result;
          IF (_result < 0) THEN
             RAISE EXCEPTION 'Error deleting kit components: deleteSoItem(integer) Error:%', _result;
          END IF;
        END LOOP;

        PERFORM explodeKit(NEW.coitem_cohead_id, NEW.coitem_linenumber, 0, NEW.coitem_itemsite_id,
                           NEW.coitem_qtyord, NEW.coitem_scheddate, NEW.coitem_promdate);
      END IF;
      IF ( (NEW.coitem_qtyord <> OLD.coitem_qtyord) OR
           (NEW.coitem_cos_accnt_id <> OLD.coitem_cos_accnt_id) ) THEN
        IF (fetchMetricBool('KitComponentInheritCOS')) THEN
  -- Update kit line item COS
          UPDATE coitem
          SET coitem_cos_accnt_id = CASE WHEN (COALESCE(NEW.coitem_cos_accnt_id, -1) != -1)
                                           THEN NEW.coitem_cos_accnt_id
                                         WHEN (NEW.coitem_warranty)
                                           THEN resolveCOWAccount(NEW.coitem_itemsite_id, _r.cohead_cust_id, _r.cohead_saletype_id, _r.cohead_shipzone_id)
                                         ELSE resolveCOSAccount(NEW.coitem_itemsite_id, _r.cohead_cust_id, _r.cohead_saletype_id, _r.cohead_shipzone_id)
                                    END
          WHERE((coitem_cohead_id=NEW.coitem_cohead_id)
            AND (coitem_linenumber = NEW.coitem_linenumber)
            AND (coitem_subnumber > 0));
        END IF;
      END IF;
      IF (NEW.coitem_scheddate <> OLD.coitem_scheddate) THEN
  -- Update kit line item Schedule Date
        UPDATE coitem
        SET coitem_scheddate = NEW.coitem_scheddate
        WHERE((coitem_cohead_id=NEW.coitem_cohead_id)
          AND (coitem_linenumber = NEW.coitem_linenumber)
          AND (coitem_subnumber > 0));
      END IF;
    END IF;
  END IF;

  IF (TG_OP = 'INSERT') THEN
    -- Create Purchase Request if flagged to do so
    IF ((NEW.coitem_order_type='R') AND (NEW.coitem_order_id=-1)) THEN
      SELECT cohead_number INTO _coheadnumber
      FROM cohead, itemsite
      WHERE (cohead_id=NEW.coitem_cohead_id)
        AND (itemsite_id=NEW.coitem_itemsite_id)
        AND (NOT itemsite_stocked);
      IF (FOUND) THEN
        SELECT createPR(CAST(_coheadnumber AS INTEGER), 'S', NEW.coitem_id) INTO _orderid;
        IF (_orderid > 0) THEN
          UPDATE coitem SET coitem_order_id=_orderid
          WHERE (coitem_id=NEW.coitem_id);
        ELSE
          RAISE EXCEPTION 'CreatePR failed, result=%', _orderid;
        END IF;
      END IF;
    END IF;

    -- Create Purchase Order if flagged to do so
    IF ((NEW.coitem_order_type='P') AND (NEW.coitem_order_id=-1)) THEN
      SELECT itemsrc_id INTO _itemsrcid
      FROM itemsite JOIN itemsrc ON (itemsrc_item_id=itemsite_item_id AND itemsrc_default)
      WHERE (itemsite_id=NEW.coitem_itemsite_id)
        AND (NOT itemsite_stocked);
      IF (FOUND) THEN
        SELECT createPurchaseToSale(NEW.coitem_id,
                                    _itemsrcid,
                                    itemsite_dropship,
                                    CASE WHEN (NEW.coitem_prcost=0.0) THEN NULL
                                         ELSE NEW.coitem_prcost
                                    END) INTO _orderid
        FROM itemsite
        WHERE (itemsite_id=NEW.coitem_itemsite_id);
        IF (_orderid > 0) THEN
          UPDATE coitem SET coitem_order_id=_orderid
          WHERE (coitem_id=NEW.coitem_id);
        ELSE
          RAISE EXCEPTION 'CreatePurchaseToSale failed, result=%', _orderid;
        END IF;
      END IF;
    END IF;

    -- Update Purchase Order comments
    IF (NEW.coitem_order_type='P') THEN
      UPDATE poitem SET poitem_comments=NEW.coitem_memo
      WHERE ((poitem_order_id=NEW.coitem_id) AND (poitem_order_type='S'));
    END IF;
  END IF;

  IF (TG_OP = 'UPDATE') THEN
    IF (NEW.coitem_order_type = 'P') THEN
      --If soitem is cancelled
      IF ((NEW.coitem_status = 'X') AND (OLD.coitem_status <> 'X')) THEN
        PERFORM postEvent('PoItemSoCancelled', 'P', poitem_id,
                          itemsite_warehous_id,
                          (pohead_number || '-' || poitem_linenumber || ':' || item_number),
                          NULL, NULL, NULL, NULL)
        FROM poitem JOIN itemsite ON (itemsite_id=poitem_itemsite_id)
                    JOIN item ON (item_id=itemsite_item_id)
                    JOIN pohead ON (pohead_id=poitem_pohead_id)
        WHERE ( (poitem_id=OLD.coitem_order_id)
          AND   (poitem_duedate <= (CURRENT_DATE + itemsite_eventfence)) );
      --If soitem notes changed
      ELSIF (NEW.coitem_memo <> OLD.coitem_memo) THEN
        UPDATE poitem SET poitem_comments=NEW.coitem_memo
        WHERE ((poitem_order_id=NEW.coitem_id) AND (poitem_order_type='S'));
      END IF;
    END IF;
  END IF;

  IF (_rec.coitem_subnumber > 0) THEN
    SELECT coitem_status
      INTO _kstat
      FROM coitem
     WHERE((coitem_cohead_id=_rec.coitem_cohead_id)
       AND (coitem_linenumber=_rec.coitem_linenumber)
       AND (coitem_subnumber = 0));
    IF ((SELECT count(*)
           FROM coitem
          WHERE((coitem_cohead_id=_rec.coitem_cohead_id)
            AND (coitem_linenumber=_rec.coitem_linenumber)
            AND (coitem_subnumber <> _rec.coitem_subnumber)
            AND (coitem_subnumber > 0)
            AND (coitem_status = 'O'))) > 0) THEN
      _pstat := 'O';
    ELSE
      _pstat := _rec.coitem_status;
    END IF;
  END IF;

  IF(TG_OP = 'INSERT') THEN
    IF (_rec.coitem_subnumber > 0 AND _rec.coitem_status = 'O') THEN
      _pstat := 'O';
    END IF;
  ELSIF (TG_OP = 'UPDATE') THEN
    IF (_rec.coitem_subnumber > 0 AND _rec.coitem_status = 'O') THEN
      _pstat := 'O';
    END IF;

    IF ((NEW.coitem_status = 'C') AND (OLD.coitem_status <> 'C')) THEN
      IF(_kit) THEN
        UPDATE coitem
           SET coitem_status='C'
         WHERE((coitem_cohead_id=OLD.coitem_cohead_id)
           AND (coitem_linenumber=OLD.coitem_linenumber)
           AND (coitem_status='O')
           AND (coitem_subnumber > 0));
      END IF;
    END IF;

    IF ((NEW.coitem_status = 'X') AND (OLD.coitem_status <> 'X')) THEN
      IF(_kit) THEN
        UPDATE coitem
           SET coitem_status='X'
         WHERE((coitem_cohead_id=OLD.coitem_cohead_id)
           AND (coitem_linenumber=OLD.coitem_linenumber)
           AND (coitem_status='O')
           AND (coitem_subnumber > 0));
      END IF;
    END IF;

    IF(NEW.coitem_status = 'O' AND OLD.coitem_status <> 'O') THEN
      IF(_kit) THEN
        UPDATE coitem
           SET coitem_status='O'
         WHERE((coitem_cohead_id=OLD.coitem_cohead_id)
           AND (coitem_linenumber=OLD.coitem_linenumber)
           AND ((coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) > 0)
           AND (coitem_subnumber > 0));
      END IF;
    END IF;

  END IF;

  IF ((_kstat IS NOT NULL) AND (_pstat IS NOT NULL) AND (_rec.coitem_subnumber > 0) AND (_kstat <> _pstat)) THEN
    UPDATE coitem
       SET coitem_status = _pstat
     WHERE((coitem_cohead_id=_rec.coitem_cohead_id)
       AND (coitem_linenumber=_rec.coitem_linenumber)
       AND (coitem_subnumber = 0));
  END IF;

  --If auto calculate freight, recalculate cohead_freight
  IF (SELECT cohead_calcfreight FROM cohead WHERE (cohead_id=NEW.coitem_cohead_id)) THEN
    UPDATE cohead SET cohead_freight = COALESCE(
      (SELECT SUM(freightdata_total) FROM freightDetail('SO',
                                                        cohead_id,
                                                        cohead_cust_id,
                                                        cohead_shipto_id,
                                                        cohead_orderdate,
                                                        cohead_shipvia,
                                                        cohead_curr_id)), 0)
    WHERE cohead_id=NEW.coitem_cohead_id;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._soitemaftertrigger() OWNER TO admin;

--
-- Name: _soitembeforedeletetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _soitembeforedeletetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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

  _r              RECORD;
  _kit            BOOLEAN := FALSE;
  _shipped        BOOLEAN := FALSE;
  _coitemid       INTEGER := 0;
  _result         INTEGER := 0;

BEGIN

  -- Check Priv
  IF NOT (checkPrivilege('MaintainSalesOrders')) THEN
    RAISE EXCEPTION 'You do not have privileges to alter a Sales Order.';
  END IF;

  -- Cache some information
  SELECT * INTO _r
    FROM cohead, itemsite, item
   WHERE ( (cohead_id=OLD.coitem_cohead_id)
     AND   (itemsite_id=OLD.coitem_itemsite_id)
     AND   (item_id=itemsite_item_id) );

  _kit := (COALESCE(_r.item_type,'')='K');

  -- Check for shipped kit components
  IF(_kit AND OLD.coitem_status <> 'C' AND OLD.coitem_status <> 'X') THEN
    IF (EXISTS (SELECT coitem_id
                  FROM coitem JOIN shipitem ON (shipitem_orderitem_id=coitem_id)
                              JOIN shiphead ON (shiphead_id=shipitem_shiphead_id AND shiphead_order_type='SO')
                 WHERE ((coitem_cohead_id=OLD.coitem_cohead_id)
                   AND  (coitem_linenumber=OLD.coitem_linenumber)
                   AND (coitem_subnumber > 0))
              GROUP BY coitem_id
                HAVING (SUM(shipitem_qty) > 0)
                 LIMIT 1) ) THEN
      _shipped := TRUE;
    END IF;
  END IF;

  IF(_kit AND _shipped) THEN
    RAISE EXCEPTION 'You can not delete this Sales Order Line as it has several sub components that have already been shipped.';
  END IF;

  DELETE FROM comment
   WHERE ( (comment_source='SI')
     AND   (comment_source_id=OLD.coitem_id) );

  DELETE FROM charass
   WHERE ((charass_target_type='SI')
     AND  (charass_target_id=OLD.coitem_id));

  -- Delete Sub Lines for Kit Components
  IF (OLD.coitem_subnumber = 0) THEN
    FOR _coitemid IN
      SELECT coitem_id
        FROM coitem
       WHERE ( (coitem_cohead_id=OLD.coitem_cohead_id)
         AND   (coitem_linenumber=OLD.coitem_linenumber)
         AND   (coitem_subnumber > 0) )
      LOOP
      SELECT deleteSoItem(_coitemid) INTO _result;
      IF (_result < 0) THEN
        IF NOT (_r.itemsite_createsopo AND (_result = -10 OR _result = -20)) THEN
          RAISE EXCEPTION 'Error deleting kit components: deleteSoItem(integer) Error:%', _result;
        END IF;
      END IF;
    END LOOP;
  END IF;

  IF (OLD.coitem_scheddate <= (CURRENT_DATE + _r.itemsite_eventfence)) THEN
    PERFORM postEvent('SoitemCancelled', 'S', OLD.coitem_id,
                      _r.itemsite_warehous_id, (_r.cohead_number || '-' || OLD.coitem_linenumber),
                      NULL, NULL, NULL, NULL);
  END IF;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._soitembeforedeletetrigger() OWNER TO admin;

--
-- Name: _soitembeforetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _soitembeforetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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;
  _itemNumber TEXT;
  _r RECORD;
  _kit BOOLEAN;

BEGIN

  --Determine if this is a kit for later processing
  SELECT COALESCE(item_type,'')='K'
  INTO _kit
  FROM itemsite, item
  WHERE((itemsite_item_id=item_id)
  AND (itemsite_id=NEW.coitem_itemsite_id));
  _kit := COALESCE(_kit, false);

  IF (TG_OP = 'INSERT') THEN

    -- If this is imported, go ahead and insert default characteristics
    IF (NEW.coitem_imported) THEN
      INSERT INTO charass (charass_target_type, charass_target_id, charass_char_id, charass_value, charass_price)
      SELECT 'SI', NEW.coitem_id, char_id, charass_value,
             itemcharprice(item_id,char_id,charass_value,cohead_cust_id,cohead_shipto_id,NEW.coitem_qtyord,cohead_curr_id,cohead_orderdate)
        FROM (
           SELECT DISTINCT char_id, char_name, charass_value, item_id, cohead_cust_id, cohead_shipto_id, cohead_curr_id, cohead_orderdate
             FROM cohead, charass, char, itemsite, item
            WHERE((itemsite_id=NEW.coitem_itemsite_id)
              AND (itemsite_item_id=item_id)
              AND (charass_target_type='I')
              AND (charass_target_id=item_id)
              AND (charass_default)
              AND (char_id=charass_char_id)
              AND (cohead_id=NEW.coitem_cohead_id))
           ORDER BY char_name) AS data;
    END IF;
  END IF;

  -- Create work order and process if flagged to do so
  IF ((NEW.coitem_order_type='W') AND (NEW.coitem_order_id=-1)) THEN
    SELECT createwo(CAST(cohead_number AS INTEGER),
                    NEW.coitem_itemsite_id,
                    1, -- priority
		    validateOrderQty(NEW.coitem_itemsite_id, NEW.coitem_qtyord, TRUE),
                    itemsite_leadtime,
                    NEW.coitem_scheddate,
		    NEW.coitem_memo,
                    'S',
                    NEW.coitem_id,
		    cohead_prj_id) INTO NEW.coitem_order_id
    FROM cohead, itemsite
    WHERE ((cohead_id=NEW.coitem_cohead_id)
    AND (itemsite_id=NEW.coitem_itemsite_id));

    INSERT INTO charass
      (charass_target_type, charass_target_id,
       charass_char_id, charass_value)
       SELECT 'W', NEW.coitem_order_id, charass_char_id, charass_value
       FROM charass
       WHERE ((charass_target_type='SI')
       AND  (charass_target_id=NEW.coitem_id));
  END IF;

  IF (TG_OP = 'UPDATE') THEN
--  Update P/R date if applicable

    IF (NEW.coitem_scheddate <> OLD.coitem_scheddate AND NEW.coitem_order_type='R' AND NEW.coitem_order_id > 1) THEN
      UPDATE pr SET pr_duedate = NEW.coitem_scheddate WHERE (pr_order_id=NEW.coitem_id AND pr_order_type='S');
    END IF;

--  If closing or cancelling and there is a job item work order, then close job and distribute remaining costs
    IF ((NEW.coitem_status = 'C' AND OLD.coitem_status <> 'C')
     OR (NEW.coitem_status = 'X' AND OLD.coitem_status <> 'X'))
     AND (OLD.coitem_order_id > -1) THEN

      SELECT wo_id, wo_wipvalue INTO _r
       FROM wo,itemsite,item
      WHERE ((wo_ordtype='S')
      AND (wo_ordid=OLD.coitem_id)
      AND (itemsite_id=wo_itemsite_id)
      AND (item_id=itemsite_item_id)
      AND (itemsite_costmethod = 'J'));

      IF (FOUND) THEN
        IF (_r.wo_wipvalue > 0) THEN
        --  Distribute to G/L, debit Cost of Sales, credit WIP
          PERFORM MIN(insertGLTransaction( 'W/O', 'WO', formatWoNumber(NEW.coitem_order_id), 'Job Closed Incomplete',
                                           costcat_wip_accnt_id,
                                           CASE WHEN (COALESCE(NEW.coitem_cos_accnt_id, -1) != -1)
                                                  THEN NEW.coitem_cos_accnt_id
                                                WHEN (NEW.coitem_warranty=TRUE)
                                                  THEN resolveCOWAccount(itemsite_id, cohead_cust_id, cohead_saletype_id, cohead_shipzone_id)
                                                ELSE resolveCOSAccount(itemsite_id, cohead_cust_id, cohead_saletype_id, cohead_shipzone_id)
                                           END,
                                           -1,  _r.wo_wipvalue, current_date ))
          FROM itemsite, costcat, cohead
          WHERE ((itemsite_id=NEW.coitem_itemsite_id)
           AND (itemsite_costcat_id=costcat_id)
           AND (cohead_id=NEW.coitem_cohead_id));
        END IF;

        UPDATE wo SET
          wo_status = 'C',
          wo_wipvalue = 0
        WHERE (wo_id = _r.wo_id);

      END IF;
    END IF;

--  Likewise, reopen the job if line reopened
    IF ((NEW.coitem_status != 'C' AND OLD.coitem_status = 'C')
     OR (NEW.coitem_status != 'X' AND OLD.coitem_status = 'X'))
     AND (OLD.coitem_order_id > -1) THEN
        UPDATE wo SET
          wo_status = 'I'
        FROM itemsite, item
        WHERE ((wo_ordtype = 'S')
         AND (wo_ordid=NEW.coitem_id)
         AND (wo_itemsite_id=itemsite_id)
         AND (itemsite_item_id=item_id)
         AND (itemsite_costmethod='J'));
    END IF;

--  Handle links to Return Authorization
    IF (fetchMetricBool('EnableReturnAuth')) THEN
      SELECT * INTO _r
      FROM raitem,rahead
      WHERE ((raitem_new_coitem_id=NEW.coitem_id)
      AND (rahead_id=raitem_rahead_id));
      IF (FOUND) THEN
        IF ((_r.raitem_qtyauthorized <> NEW.coitem_qtyord OR
            _r.raitem_qty_uom_id <> NEW.coitem_qty_uom_id OR
            _r.raitem_qty_invuomratio <> NEW.coitem_qty_invuomratio OR
            _r.raitem_price_uom_id <> NEW.coitem_price_uom_id OR
            _r.raitem_price_invuomratio <> NEW.coitem_price_invuomratio)
            AND NOT (NEW.coitem_status = 'X' AND _r.raitem_qtyauthorized = 0)) THEN
          RAISE EXCEPTION 'Quantities for line item % may only be changed on the Return Authorization that created it.',NEW.coitem_linenumber;
        END IF;
        IF (OLD.coitem_warranty <> NEW.coitem_warranty) THEN
          UPDATE raitem SET raitem_warranty = NEW.coitem_warranty
           WHERE((raitem_new_coitem_id=NEW.coitem_id)
             AND (raitem_warranty != NEW.coitem_warranty));
        END IF;
        IF (OLD.coitem_cos_accnt_id <> NEW.coitem_cos_accnt_id) THEN
          UPDATE raitem SET raitem_cos_accnt_id = NEW.coitem_cos_accnt_id
           WHERE((raitem_new_coitem_id=NEW.coitem_id)
             AND (COALESCE(raitem_cos_accnt_id,-1) != COALESCE(NEW.coitem_cos_accnt_id,-1)));
        END IF;
        IF (OLD.coitem_taxtype_id <> NEW.coitem_taxtype_id) THEN
          UPDATE raitem SET raitem_taxtype_id = NEW.coitem_taxtype_id
           WHERE((raitem_new_coitem_id=NEW.coitem_id)
             AND (COALESCE(raitem_taxtype_id,-1) != COALESCE(NEW.coitem_taxtype_id,-1)));
        END IF;
        IF (OLD.coitem_scheddate <> NEW.coitem_scheddate) THEN
          UPDATE raitem SET raitem_scheddate = NEW.coitem_scheddate
           WHERE((raitem_new_coitem_id=NEW.coitem_id)
             AND (raitem_scheddate != NEW.coitem_scheddate));
        END IF;
        IF (OLD.coitem_memo <> NEW.coitem_memo) THEN
          UPDATE raitem SET raitem_notes = NEW.coitem_memo
           WHERE((raitem_new_coitem_id=NEW.coitem_id)
             AND (raitem_notes != NEW.coitem_memo));
        END IF;
        IF ((OLD.coitem_qtyshipped <> NEW.coitem_qtyshipped) AND
           (NEW.coitem_qtyshipped >= _r.raitem_qtyauthorized) AND
           ((_r.raitem_disposition = 'S') OR
           (_r.raitem_status = 'O') AND
           (_r.raitem_disposition IN ('P','V')) AND
           (_r.raitem_qtyreceived >= _r.raitem_qtyauthorized))) THEN
          UPDATE raitem SET raitem_status = 'C'
          WHERE (raitem_new_coitem_id=NEW.coitem_id);
        END IF;
      END IF;
    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._soitembeforetrigger() OWNER TO admin;

--
-- Name: _soitemtrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _soitemtrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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;
  _check BOOLEAN;
  _kit BOOLEAN;
  _shipped BOOLEAN;
  _atShipping NUMERIC;
  _tmp INTEGER;
  _rec RECORD;
BEGIN
  -- Check
  SELECT checkPrivilege('MaintainSalesOrders') OR checkPrivilege('ShipOrders') OR checkPrivilege('IssueStockToShipping') INTO _check;
  IF NOT (_check) THEN
    RAISE EXCEPTION 'You do not have privileges to alter a Sales Order.';
  END IF;

  IF ( SELECT fetchMetricBool('SalesOrderChangeLog') ) THEN
    _changelog := TRUE;
  END IF;

  IF (TG_OP IN ('INSERT','UPDATE')) THEN
    IF (NEW.coitem_scheddate IS NULL) THEN
      IF (fetchmetricbool('AllowASAPShipSchedules')) THEN
        NEW.coitem_scheddate := current_date;
      ELSE
        RAISE EXCEPTION 'A schedule date is required.';
      END IF;
    END IF;
  END IF;

  _rec := NEW;

  SELECT COALESCE(item_type,'')='K'
    INTO _kit
    FROM itemsite, item
   WHERE((itemsite_item_id=item_id)
     AND (itemsite_id=_rec.coitem_itemsite_id));
  _kit := COALESCE(_kit, false);
  _shipped := false;
  IF(_kit AND _rec.coitem_status <> 'C' AND _rec.coitem_status <> 'X') THEN
    SELECT coitem_id
      INTO _tmp
      FROM coitem JOIN shipitem ON (shipitem_orderitem_id=coitem_id)
                  JOIN shiphead ON (shiphead_id=shipitem_shiphead_id AND shiphead_order_type='SO')
     WHERE((coitem_cohead_id=_rec.coitem_cohead_id)
       AND (coitem_linenumber=_rec.coitem_linenumber)
       AND (coitem_subnumber > 0))
     GROUP BY coitem_id
    HAVING (SUM(shipitem_qty) > 0)
     LIMIT 1;
    IF (FOUND) THEN
      _shipped := true;
    END IF;
  END IF;

  IF (TG_OP ='UPDATE') THEN
    IF ((OLD.coitem_status <> 'C') AND (NEW.coitem_status = 'C')) THEN
      SELECT qtyAtShipping(NEW.coitem_id) INTO _atShipping;
      IF (_atShipping > 0) THEN
        RAISE EXCEPTION 'Line % cannot be Closed at this time as there is inventory at shipping.',NEW.coitem_linenumber;
      END IF;
    END IF;
  END IF;

  IF (TG_OP = 'INSERT') THEN
    PERFORM postEvent('SoitemCreated', 'S', NEW.coitem_id,
                      itemsite_warehous_id, (cohead_number || '-' || NEW.coitem_linenumber),
                      NULL, NULL, NULL, NULL)
    FROM cohead, itemsite
    WHERE ( (cohead_id=NEW.coitem_cohead_id)
      AND   (itemsite_id=NEW.coitem_itemsite_id)
      AND   (NEW.coitem_scheddate <= (CURRENT_DATE + itemsite_eventfence)) );

    IF (_changelog) THEN
      PERFORM postComment('ChangeLog', 'SI', NEW.coitem_id, 'Created');
    END IF;

    --Set defaults if no values passed
    NEW.coitem_linenumber	:= COALESCE(NEW.coitem_linenumber,
                                          (SELECT (COALESCE(MAX(coitem_linenumber), 0) + 1)
                                           FROM coitem
                                           WHERE (coitem_cohead_id=NEW.coitem_cohead_id)));
    NEW.coitem_status		:= COALESCE(NEW.coitem_status,'O');
    NEW.coitem_scheddate	:= COALESCE(NEW.coitem_scheddate,
					   (SELECT MIN(coitem_scheddate)
					    FROM coitem
					    WHERE (coitem_cohead_id=NEW.coitem_cohead_id)));
    NEW.coitem_memo		:= COALESCE(NEW.coitem_memo,'');
    NEW.coitem_prcost		:= COALESCE(NEW.coitem_prcost,0);
    NEW.coitem_warranty	:= COALESCE(NEW.coitem_warranty,false);

    IF (NEW.coitem_status='O') THEN
      UPDATE cohead SET cohead_status = 'O'
       WHERE ((cohead_id=NEW.coitem_cohead_id)
         AND  (cohead_status='C'));
    END IF;

    RETURN NEW;

  ELSIF (TG_OP = 'UPDATE') THEN
    IF (NEW.coitem_qtyord <> OLD.coitem_qtyord) THEN
      IF(_kit) THEN
        IF(_shipped) THEN
          RAISE EXCEPTION 'You can not change the qty ordered for a Kit item when one or more of its components have shipped inventory.';
        END IF;
      END IF;
      PERFORM postEvent('SoitemQtyChanged', 'S', NEW.coitem_id,
                        itemsite_warehous_id, (cohead_number || '-' || NEW.coitem_linenumber),
                        NEW.coitem_qtyord, OLD.coitem_qtyord, NULL, NULL)
      FROM cohead, itemsite
      WHERE ( (cohead_id=NEW.coitem_cohead_id)
        AND   (itemsite_id=NEW.coitem_itemsite_id)
        AND   ( (NEW.coitem_scheddate <= (CURRENT_DATE + itemsite_eventfence))
         OR     (OLD.coitem_scheddate <= (CURRENT_DATE + itemsite_eventfence)) ) );

      IF (_changelog) THEN
	PERFORM postComment( 'ChangeLog', 'SI', NEW.coitem_id,
			     ( 'Changed Qty. Ordered from ' || formatQty(OLD.coitem_qtyord) ||
			       ' to ' || formatQty(NEW.coitem_qtyord) ) );
      END IF;

    END IF;

    IF (NEW.coitem_price <> OLD.coitem_price) THEN
      IF (_changelog) THEN
	PERFORM postComment( 'ChangeLog', 'SI', NEW.coitem_id,
			     ( 'Changed Unit Price from ' || formatPrice(OLD.coitem_price) ||
			       ' to ' || formatPrice(NEW.coitem_price) ) );
      END IF;

    END IF;

    IF (NEW.coitem_scheddate <> OLD.coitem_scheddate) THEN
      PERFORM postEvent('SoitemSchedDateChanged', 'S', NEW.coitem_id,
                        itemsite_warehous_id, (cohead_number || '-' || NEW.coitem_linenumber),
                        NULL, NULL, NEW.coitem_scheddate, OLD.coitem_scheddate)
      FROM cohead, itemsite
      WHERE ( (cohead_id=NEW.coitem_cohead_id)
        AND   (itemsite_id=NEW.coitem_itemsite_id)
        AND   ( (NEW.coitem_scheddate <= (CURRENT_DATE + itemsite_eventfence))
         OR     (OLD.coitem_scheddate <= (CURRENT_DATE + itemsite_eventfence)) ) );

      IF (_changelog) THEN
	PERFORM postComment( 'ChangeLog', 'SI', NEW.coitem_id,
			     ( 'Changed Sched. Date from ' || formatDate(OLD.coitem_scheddate) ||
			       ' to ' || formatDate(NEW.coitem_scheddate)) );
      END IF;

    END IF;

    IF ((NEW.coitem_status = 'C') AND (OLD.coitem_status <> 'C')) THEN
      NEW.coitem_closedate = CURRENT_TIMESTAMP;
      NEW.coitem_close_username = getEffectiveXtUser();
      NEW.coitem_qtyreserved := 0;

      IF (_changelog) THEN
	PERFORM postComment('ChangeLog', 'SI', NEW.coitem_id, 'Closed');
      END IF;
    END IF;

    IF ((NEW.coitem_status <> 'C') AND (OLD.coitem_status = 'C')) THEN
      NEW.coitem_closedate = NULL;
      NEW.coitem_close_username = NULL;

      IF (_changelog) THEN
	PERFORM postComment('ChangeLog', 'SI', NEW.coitem_id, 'Reopened');
      END IF;
    END IF;

    IF ((NEW.coitem_status = 'X') AND (OLD.coitem_status <> 'X')) THEN
      IF ((OLD.coitem_order_type = 'W') AND
	  (SELECT wo_status IN ('O', 'E', 'R')
	    FROM wo
	    WHERE (wo_id=OLD.coitem_order_id))) THEN
      -- Close any associated W/O
        PERFORM closeWo(OLD.coitem_order_id, FALSE, CURRENT_DATE);
      ELSIF (OLD.coitem_order_type = 'R') THEN
      -- Delete any associated P/R
        PERFORM deletePr(OLD.coitem_order_id);
      END IF;

      NEW.coitem_qtyreserved := 0;

      IF (_changelog) THEN
	PERFORM postComment('ChangeLog', 'SI', NEW.coitem_id, 'Canceled');
	PERFORM postComment('ChangeLog', 'S', NEW.coitem_cohead_id, 'Line # '|| NEW.coitem_linenumber ||' Canceled');
      END IF;

      PERFORM postEvent('SoitemCancelled', 'S', OLD.coitem_id,
                        itemsite_warehous_id, (cohead_number || '-' || OLD.coitem_linenumber),
                        NULL, NULL, NULL, NULL)
      FROM cohead, itemsite
      WHERE ( (cohead_id=OLD.coitem_cohead_id)
        AND   (itemsite_id=OLD.coitem_itemsite_id)
        AND   (OLD.coitem_scheddate <= (CURRENT_DATE + itemsite_eventfence)) );

    END IF;

    IF ((NEW.coitem_qtyreserved <> OLD.coitem_qtyreserved) AND (_changelog)) THEN
      PERFORM postComment('ChangeLog', 'SI', NEW.coitem_id, 'Changed Qty Reserved to '|| NEW.coitem_qtyreserved);
    END IF;

  END IF;

  NEW.coitem_lastupdated = CURRENT_TIMESTAMP;

  -- Handle status for header
  IF (TG_OP = 'UPDATE') THEN
    IF (OLD.coitem_status <> NEW.coitem_status) THEN
      IF ( (SELECT (count(*) < 1)
              FROM coitem
             WHERE ((coitem_cohead_id=NEW.coitem_cohead_id)
               AND  (coitem_id != NEW.coitem_id)
               AND  (coitem_status='O')) ) AND (NEW.coitem_status<>'O') ) THEN
        UPDATE cohead SET cohead_status = 'C'
         WHERE ((cohead_id=NEW.coitem_cohead_id)
           AND  (cohead_status='O'));
      ELSE
        UPDATE cohead SET cohead_status = 'O'
         WHERE ((cohead_id=NEW.coitem_cohead_id)
           AND  (cohead_status='C'));
      END IF;
    END IF;
  END IF;

  RETURN NEW;

END;
$$;


ALTER FUNCTION public._soitemtrigger() OWNER TO admin;

--
-- Name: _sourcebeforeupserttrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _sourcebeforeupserttrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2015 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  IF TG_OP = 'INSERT' THEN
    NEW.source_created := CURRENT_TIMESTAMP;
  ELSE
    NEW.source_created := OLD.source_created;
    NEW.source_docass_num := COALESCE(NEW.source_docass_num, OLD.source_docass_num);
  END IF;

  IF COALESCE(NEW.source_module, '') = '' THEN
    NEW.source_module = 'System';
  END IF;
  IF COALESCE(NEW.source_docass_num, 0) = 0 THEN      /* 0 == Documents::Uninitialized */
    SELECT max(source_docass_num) + 1 FROM source INTO NEW.source_docass_num;
  END IF;

  NEW.source_last_modified := CURRENT_TIMESTAMP;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._sourcebeforeupserttrigger() OWNER TO admin;

--
-- Name: _taxauthafterdeletetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _taxauthafterdeletetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 (EXISTS(SELECT 1
               FROM checkhead
              WHERE checkhead_recip_id = OLD.taxauth_id
                AND checkhead_recip_type='T')) THEN
    RAISE EXCEPTION 'Cannot delete the tax authority % because checks have been written to it [xtuple: deleteTaxAuthority, -7, %]',
                    OLD.taxauth_number, OLD.taxauth_number;
  END IF;

  IF (fetchMetricValue('DefaultTaxAuthority') = OLD.taxauth_id) THEN
    RAISE EXCEPTION 'Cannot delete the default Tax Authority [xtuple: deleteTaxAuthority, -8, %]',
                    OLD.taxauth_code;
  END IF;

  IF (fetchMetricBool('TaxAuthChangeLog')) THEN
    PERFORM postComment(cmnttype_id, 'TAXAUTH', OLD.taxauth_id,
                        'Deleted "' || OLD.taxauth_number || '"')
      FROM cmnttype
     WHERE (cmnttype_name='ChangeLog');
  END IF;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._taxauthafterdeletetrigger() OWNER TO admin;

--
-- Name: _taxauthaftertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _taxauthaftertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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;

BEGIN
  IF (TG_OP = 'INSERT') THEN
    -- http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE
    LOOP
      UPDATE crmacct SET crmacct_taxauth_id=NEW.taxauth_id,
                         crmacct_name=NEW.taxauth_name
       WHERE crmacct_number=NEW.taxauth_code;
      IF (FOUND) THEN
        EXIT;
      END IF;
      BEGIN
        INSERT INTO crmacct(crmacct_number,   crmacct_name,     crmacct_active,
                            crmacct_type,     crmacct_taxauth_id
                  ) VALUES (NEW.taxauth_code, NEW.taxauth_name, TRUE, 
                            'O',              NEW.taxauth_id);
        EXIT;
      EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
      END;
    END LOOP;

    /* TODO: default characteristic assignments based on what? */

  ELSIF (TG_OP = 'UPDATE') THEN
    UPDATE crmacct SET crmacct_number = NEW.taxauth_code
    WHERE ((crmacct_taxauth_id=NEW.taxauth_id)
      AND  (crmacct_number!=NEW.taxauth_code));

    UPDATE crmacct SET crmacct_name = NEW.taxauth_name
    WHERE ((crmacct_taxauth_id=NEW.taxauth_id)
      AND  (crmacct_name!=NEW.taxauth_name));

  END IF;

  IF (fetchMetricBool('TaxAuthChangeLog')) THEN
    SELECT cmnttype_id INTO _cmnttypeid
      FROM cmnttype
     WHERE (cmnttype_name='ChangeLog');

    IF (_cmnttypeid IS NOT NULL) THEN
      IF (TG_OP = 'INSERT') THEN
        PERFORM postComment(_cmnttypeid, 'TAXAUTH', NEW.taxauth_id, 'Created');

      ELSIF (TG_OP = 'UPDATE') THEN
        IF (OLD.taxauth_code <> NEW.taxauth_code) THEN
          PERFORM postComment(_cmnttypeid, 'TAXAUTH', NEW.taxauth_id,
                              'Code changed from "' || OLD.taxauth_code ||
                              '" to "' || NEW.taxauth_code || '"');
        END IF;

        IF (OLD.taxauth_name <> NEW.taxauth_name) THEN
          PERFORM postComment(_cmnttypeid, 'TAXAUTH', NEW.taxauth_id,
                              'Name changed from "' || OLD.taxauth_name ||
                              '" to "' || NEW.taxauth_name || '"');
        END IF;

        IF (OLD.taxauth_extref <> NEW.taxauth_extref) THEN
          PERFORM postComment(_cmnttypeid, 'TAXAUTH', NEW.taxauth_id,
                              'External Ref. changed from "' || OLD.taxauth_extref ||
                              '" to "' || NEW.taxauth_extref || '"');
        END IF;

        IF (OLD.taxauth_addr_id <> NEW.taxauth_addr_id) THEN
          PERFORM postComment(_cmnttypeid, 'TAXAUTH', NEW.taxauth_id,
                              'Address changed from ' || formatAddr(OLD.taxauth_addr_id)
                              || ' to ' || formatAddr(NEW.taxauth_addr_id));
        END IF;

        IF (OLD.taxauth_curr_id <> NEW.taxauth_curr_id) THEN
          PERFORM postComment(_cmnttypeid, 'TAXAUTH', NEW.taxauth_id,
                              'Currency changed from "' ||
                              currConcat(OLD.taxauth_curr_id) || '" to "' ||
                              currConcat(NEW.taxauth_curr_id) || '"');
        END IF;

        IF (OLD.taxauth_county <> NEW.taxauth_county) THEN
          PERFORM postComment(_cmnttypeid, 'TAXAUTH', NEW.taxauth_id,
                              'County changed from "' || OLD.taxauth_county ||
                              '" to "' || NEW.taxauth_county || '"');
        END IF;

        IF (OLD.taxauth_accnt_id <> NEW.taxauth_accnt_id) THEN
          PERFORM postComment(_cmnttypeid, 'TAXAUTH', NEW.taxauth_id,
                              'Account changed from "' ||
                              formatGLAccount(OLD.taxauth_accnt_id) || '" to "' ||
                              formatGLAccount(NEW.taxauth_accnt_id) || '"');
        END IF;

      END IF;
    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._taxauthaftertrigger() OWNER TO admin;

--
-- Name: _taxauthbeforedeletetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _taxauthbeforedeletetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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('MaintainTaxAuthorities')) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Tax Authorities.';
  END IF;

  UPDATE crmacct SET crmacct_taxauth_id = NULL
   WHERE crmacct_taxauth_id = OLD.taxauth_id;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._taxauthbeforedeletetrigger() OWNER TO admin;

--
-- Name: _taxauthbeforetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _taxauthbeforetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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('MaintainTaxAuthorities')) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Tax Authorities.';
  END IF;

  IF (NEW.taxauth_code IS NULL) THEN
    RAISE EXCEPTION 'You must supply a Tax Authority Code.';
  END IF;

  IF (TG_OP = 'INSERT' AND
      fetchMetricText('CRMAccountNumberGeneration') IN ('A','O')) THEN
    PERFORM clearNumberIssue('CRMAccountNumber', NEW.taxauth_code);
  END IF;

  NEW.taxauth_code := UPPER(NEW.taxauth_code);

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._taxauthbeforetrigger() OWNER TO admin;

--
-- Name: _termsafterdeletetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _termsafterdeletetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 (fetchMetricValue('DefaultTerms') = OLD.terms_id) THEN
    RAISE EXCEPTION 'Cannot delete the default Terms [xtuple: terms, -1, %]',
                    OLD.terms_code;
  END IF;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._termsafterdeletetrigger() OWNER TO admin;

--
-- Name: _todoitemtrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _todoitemtrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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
  _recurid     INTEGER;
  _newparentid INTEGER;

BEGIN
  IF (TG_OP = 'DELETE') THEN
    SELECT recur_id INTO _recurid
      FROM recur
     WHERE ((recur_parent_id=OLD.todoitem_id)
       AND  (recur_parent_type='TODO'));

    IF (_recurid IS NOT NULL) THEN
      RAISE DEBUG 'recur_id for deleted todoitem = %', _recurid;

      SELECT todoitem_id INTO _newparentid
        FROM todoitem
       WHERE ((todoitem_recurring_todoitem_id=OLD.todoitem_id)
          AND (todoitem_id!=OLD.todoitem_id))
       ORDER BY todoitem_due_date
       LIMIT 1;

      RAISE DEBUG '_newparentid for deleted todoitem = %', COALESCE(_newparentid, NULL);

      -- client is responsible for warning about deleting a recurring todoitem
      IF (_newparentid IS NULL) THEN
        DELETE FROM recur WHERE recur_id=_recurid;
      ELSE
        UPDATE recur SET recur_parent_id=_newparentid
         WHERE recur_id=_recurid;

        UPDATE todoitem SET todoitem_recurring_todoitem_id=_newparentid
         WHERE todoitem_recurring_todoitem_id=OLD.todoitem_id
           AND todoitem_id != OLD.todoitem_id;

        RAISE DEBUG 'reparented recurrence';
      END IF;
    END IF;

    DELETE FROM alarm
     WHERE ((alarm_source='TODO')
        AND (alarm_source_id=OLD.todoitem_id));

    DELETE FROM docass WHERE docass_source_id = OLD.todoitem_id AND docass_source_type = 'TODO';
    DELETE FROM docass WHERE docass_target_id = OLD.todoitem_id AND docass_target_type = 'TODO';

    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._todoitemtrigger() OWNER TO admin;

--
-- Name: _trialbalaltertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _trialbalaltertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2015 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _accntid INTEGER[];
BEGIN
  IF (TG_OP='DELETE') THEN
    IF (SELECT period_closed FROM period WHERE period_id=OLD.trialbal_period_id) THEN
      RAISE EXCEPTION 'You may not delete Trial Balance Transactions in closed periods.';
    END IF;

    RETURN OLD;
  END IF;

  IF (TG_OP='INSERT' OR TG_OP='UPDATE') THEN
    IF (coalesce(fetchMetricValue('GLCompanySize'),0) = 0) THEN
    --  Get the default account number for year end closing
      _accntid := fetchmetricvalue('YearEndEquityAccount');
    ELSE
    --  Multi-company setup
       _accntid := (SELECT array_agg(company_yearend_accnt_id)
                  FROM company);
    END IF;

    If (NEW.trialbal_accnt_id = ANY(_accntid) OR (NEW.trialbal_beginning = 0.00 AND NEW.trialbal_ending = 0.00)) THEN
    --  Dont check new accounts or Retained Earnings account 
    ELSE
      IF (SELECT period_closed FROM period WHERE period_id=NEW.trialbal_period_id) THEN
        RAISE EXCEPTION 'You may not alter Trial Balance records in a closed Period.';
      END IF;
    END IF;
  
    RETURN NEW;
  END IF;

END;
$$;


ALTER FUNCTION public._trialbalaltertrigger() OWNER TO admin;

--
-- Name: _uomconvupdate(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _uomconvupdate() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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
 
  UPDATE itemuomconv
  SET itemuomconv_to_value = NEW.uomconv_to_value,
  itemuomconv_from_value = NEW.uomconv_from_value,
  itemuomconv_fractional = NEW.uomconv_fractional
  WHERE((itemuomconv_from_uom_id = NEW.uomconv_from_uom_id)
  AND (itemuomconv_to_uom_id = NEW.uomconv_to_uom_id));

RETURN NEW;

END; 
$$;


ALTER FUNCTION public._uomconvupdate() OWNER TO admin;

--
-- Name: _usrprefaftertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _usrprefaftertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
    -- http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE
    IF (NEW.usrpref_name='active') THEN
      LOOP
        UPDATE crmacct SET crmacct_usr_username=NEW.usrpref_username
         WHERE crmacct_number=UPPER(NEW.usrpref_username);
        IF (FOUND) THEN
          EXIT;
        END IF;
        BEGIN
          INSERT INTO crmacct(crmacct_number,        crmacct_active,
                              crmacct_type,          crmacct_usr_username
                    ) VALUES (NEW.usrpref_username,  NEW.usrpref_value::BOOL,
                              'I',                   NEW.usrpref_username);
          EXIT;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
        END;
      END LOOP;

    ELSIF (NEW.usrpref_name='propername') THEN
      LOOP
        UPDATE crmacct SET crmacct_name=NEW.usrpref_value
         WHERE crmacct_number=UPPER(NEW.usrpref_username);
        IF (FOUND) THEN
          EXIT;
        END IF;
        BEGIN
          INSERT INTO crmacct(crmacct_number,        crmacct_active,
                              crmacct_name,
                              crmacct_type,          crmacct_usr_username
                    ) VALUES (UPPER(NEW.usrpref_username), TRUE,
                              NEW.usrpref_value,
                              'I',                   NEW.usrpref_username);
          EXIT;
        EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
        END;
      END LOOP;

    END IF;

  ELSIF (TG_OP = 'DELETE') THEN
    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._usrprefaftertrigger() OWNER TO admin;

--
-- Name: _usrprefbeforetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _usrprefbeforetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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('MaintainUsers') OR
          checkPrivilege('MaintainPreferencesOthers') OR
          (checkPrivilege('MaintainPreferencesSelf'))) THEN
    -- 2 IFs because plpgsql doesn't always evaluate boolean exprs left-to-right
    IF (TG_OP = 'DELETE') THEN
      IF NOT (OLD.usrpref_name LIKE '%/checked' OR OLD.usrpref_name LIKE '%/columnsShown') THEN
        RAISE EXCEPTION 'You do not have privileges to change this User Preference.';
      END IF;
    ELSIF (NEW.usrpref_username = getEffectiveXtUser()) THEN
      IF NOT (NEW.usrpref_name LIKE '%/checked' OR NEW.usrpref_name LIKE '%/columnsShown') THEN
        RAISE EXCEPTION 'You do not have privileges to change this User Preference.';
      END IF;
    END IF;
  END IF;

  IF (TG_OP IN ('INSERT', 'UPDATE')) THEN
    IF (NEW.usrpref_name = 'locale') THEN
      IF NOT EXISTS(SELECT locale_id
                      FROM locale
                     WHERE locale_id = NEW.usrpref_value::INTEGER) THEN
        RAISE EXCEPTION 'You must supply a valid Locale.';
      END IF;

    ELSIF (NEW.usrpref_name IN ('agent', 'active')) THEN
      IF (NEW.usrpref_value NOT IN ('t', 'f')) THEN
        RAISE EXCEPTION '% must be either "t" or "f"', NEW.usrpref_name;
      END IF;
    END IF;

  ELSIF (TG_OP = 'DELETE') THEN
    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._usrprefbeforetrigger() OWNER TO admin;

--
-- Name: _usrprivtrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _usrprivtrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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('MaintainUsers') THEN
    RAISE EXCEPTION '% does not have privileges to modify user privileges.',
                    getEffectiveXtUser();
  END IF;

  -- This looks like a candidate for a foreign key but isn't.
  -- fkeys don't work if the foreign key value resides in a child of the 
  -- table and not the table itself.
  IF ((TG_OP = 'UPDATE' OR TG_OP = 'INSERT') AND
      (NOT EXISTS(SELECT priv_id
                  FROM priv
                  WHERE (priv_id=NEW.usrpriv_priv_id)))) THEN
    RAISE EXCEPTION 'Privilege id % does not exist or is part of a disabled package.',
                NEW.usrpriv_priv_id;
    RETURN OLD;

  ELSIF (TG_OP = 'DELETE') THEN
    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._usrprivtrigger() OWNER TO admin;

--
-- Name: _vendaddrtrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _vendaddrtrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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      BOOLEAN;
  _vendname   TEXT;

BEGIN

--  Checks
  SELECT checkPrivilege('MaintainVendors') INTO _check;
  IF NOT (_check) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Vendors.';
  END IF;

  IF (TG_OP IN ('INSERT','UPDATE')) THEN

    IF (LENGTH(COALESCE(NEW.vendaddr_code, ''))=0) THEN
      RAISE EXCEPTION 'You must supply a valid Vendor Address Number.';
    END IF;

    IF (LENGTH(COALESCE(NEW.vendaddr_name, ''))=0) THEN
      RAISE EXCEPTION 'You must supply a valid Vendor Address Name.';
    END IF;

    IF (NEW.vendaddr_vend_id IS NULL) THEN
      RAISE EXCEPTION 'You must supply a valid Vendor ID.';
    END IF;

    SELECT vendaddr_code INTO _vendname
    FROM vendaddrinfo
    WHERE ( (vendaddr_vend_id=NEW.vendaddr_vend_id)
      AND (UPPER(vendaddr_code)=UPPER(NEW.vendaddr_code))
      AND (vendaddr_id<>NEW.vendaddr_id) );
    IF (FOUND) THEN
      RAISE EXCEPTION 'The Vendor Address Number entered cannot be used as it is in use.';
    END IF;

  END IF;
  
  IF (TG_OP = 'DELETE') THEN
    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._vendaddrtrigger() OWNER TO admin;

--
-- Name: _vendaftertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _vendaftertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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;

BEGIN

  IF (TG_OP = 'INSERT') THEN
    -- http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE
    LOOP
      UPDATE crmacct SET crmacct_vend_id=NEW.vend_id,
                         crmacct_name=NEW.vend_name
       WHERE crmacct_number=NEW.vend_number;
      IF (FOUND) THEN
        EXIT;
      END IF;
      BEGIN
        INSERT INTO crmacct(crmacct_number,     crmacct_name,    crmacct_active,
                            crmacct_type,       crmacct_vend_id,
                            crmacct_cntct_id_1, crmacct_cntct_id_2
                  ) VALUES (NEW.vend_number,    NEW.vend_name,   NEW.vend_active,
                            'O',                NEW.vend_id,
                            NEW.vend_cntct1_id, NEW.vend_cntct2_id);
        EXIT;
      EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
      END;
    END LOOP;

    /* TODO: default characteristic assignments based on vendgrp? */

  ELSIF (TG_OP = 'UPDATE') THEN
    UPDATE crmacct SET crmacct_number = NEW.vend_number
    WHERE ((crmacct_vend_id=NEW.vend_id)
      AND  (crmacct_number!=NEW.vend_number));

    UPDATE crmacct SET crmacct_name = NEW.vend_name
    WHERE ((crmacct_vend_id=NEW.vend_id)
      AND  (crmacct_name!=NEW.vend_name));

  END IF;

  IF (fetchMetricBool('VendorChangeLog')) THEN
    SELECT cmnttype_id INTO _cmnttypeid
      FROM cmnttype
     WHERE (cmnttype_name='ChangeLog');

    IF (_cmnttypeid IS NOT NULL) THEN
      IF (TG_OP = 'INSERT') THEN
        PERFORM postComment(_cmnttypeid, 'V', NEW.vend_id, 'Created');

      ELSIF (TG_OP = 'UPDATE') THEN

        IF (OLD.vend_number <> NEW.vend_number) THEN
          PERFORM postComment(_cmnttypeid, 'V', NEW.vend_id,
                              ('Number Changed from "' || OLD.vend_number ||
                               '" to "' || NEW.vend_number || '"') );
        END IF;

        IF (OLD.vend_name <> NEW.vend_name) THEN
          PERFORM postComment( _cmnttypeid, 'V', NEW.vend_id,
                              ('Name Changed from "' || OLD.vend_name ||
                               '" to "' || NEW.vend_name || '"') );
        END IF;

        IF (OLD.vend_active <> NEW.vend_active) THEN
          PERFORM postComment(_cmnttypeid, 'V', NEW.vend_id,
                              CASE WHEN NEW.vend_active THEN 'Activated'
                                   ELSE 'Deactivated' END);
        END IF;

      END IF;
    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._vendaftertrigger() OWNER TO admin;

--
-- Name: _vendinfoafterdeletetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _vendinfoafterdeletetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 EXISTS(SELECT 1
              FROM checkhead
             WHERE ((checkhead_recip_id=OLD.vend_id)
                AND (checkhead_recip_type='V'))) THEN
    RAISE EXCEPTION '[xtuple: deleteVendor, -7]';
  END IF;

  DELETE FROM taxreg
   WHERE ((taxreg_rel_type='V')
      AND (taxreg_rel_id=OLD.vend_id));

  IF (fetchMetricBool('VendorChangeLog')) THEN
    PERFORM postComment(cmnttype_id, 'V', OLD.vend_id,
                        ('Deleted "' || OLD.vend_number || '"'))
      FROM cmnttype
     WHERE (cmnttype_name='ChangeLog');
  END IF;

  DELETE
  FROM charass
  WHERE charass_target_type = 'V'
    AND charass_target_id = OLD.vend_id;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._vendinfoafterdeletetrigger() OWNER TO admin;

--
-- Name: _vendinfobeforedeletetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _vendinfobeforedeletetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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('MaintainVendors')) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Vendors.';
  END IF;

  DELETE FROM itemsrcp
   WHERE itemsrcp_itemsrc_id IN (SELECT itemsrc_id
                                   FROM itemsrc
                                  WHERE itemsrc_vend_id=OLD.vend_id);

  DELETE FROM itemsrc WHERE (itemsrc_vend_id=OLD.vend_id);

  DELETE FROM vendaddrinfo WHERE (vendaddr_vend_id=OLD.vend_id);

  DELETE FROM docass WHERE docass_source_id = OLD.vend_id AND docass_source_type = 'V';
  DELETE FROM docass WHERE docass_target_id = OLD.vend_id AND docass_target_type = 'V';

  UPDATE crmacct SET crmacct_vend_id = NULL
   WHERE crmacct_vend_id = OLD.vend_id;
  RETURN OLD;
END;
$$;


ALTER FUNCTION public._vendinfobeforedeletetrigger() OWNER TO admin;

--
-- Name: _vendtrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _vendtrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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('MaintainVendors')) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Vendors.';
  END IF;

  IF (LENGTH(COALESCE(NEW.vend_number, ''))=0) THEN
    RAISE EXCEPTION 'You must supply a valid Vendor Number.';
  END IF;

  IF (LENGTH(COALESCE(NEW.vend_name, ''))=0) THEN
    RAISE EXCEPTION 'You must supply a valid Vendor Name.';
  END IF;

  IF (NEW.vend_vendtype_id IS NULL) THEN
    RAISE EXCEPTION 'You must supply a valid Vendor Type ID.';
  END IF;

  IF (NEW.vend_terms_id IS NULL) THEN
    RAISE EXCEPTION 'You must supply a valid Terms Code ID.';
  END IF;

  IF (TG_OP = 'INSERT' AND fetchMetricText('CRMAccountNumberGeneration') IN ('A','O')) THEN
    PERFORM clearNumberIssue('CRMAccountNumber', NEW.vend_number);
  END IF;

  NEW.vend_number := UPPER(NEW.vend_number);

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._vendtrigger() OWNER TO admin;

--
-- Name: _vodistaftertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _vodistaftertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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
  _r RECORD;

BEGIN
  IF ( (TG_OP = 'UPDATE') OR (TG_OP = 'DELETE') ) THEN
    IF (OLD.vodist_tax_id <> -1) THEN
    -- Delete any existing voheadtax adjustment records
      DELETE FROM voheadtax
      WHERE ( (taxhist_parent_id=OLD.vodist_vohead_id)
        AND   (taxhist_tax_id=OLD.vodist_tax_id)
        AND   (taxhist_taxtype_id=getAdjustmentTaxTypeId()) );
    END IF;
  END IF;

  IF (TG_OP = 'DELETE') THEN
    RETURN OLD;
  END IF;

-- Cache Voucher Head
  SELECT * INTO _r
  FROM vohead
  WHERE (vohead_id=NEW.vodist_vohead_id);
  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Voucher head not found';
  END IF;

  IF (NEW.vodist_tax_id <> -1) THEN
  -- Insert adjustment voheadtax
    INSERT INTO voheadtax
      ( taxhist_parent_id,
        taxhist_taxtype_id,
        taxhist_tax_id,
        taxhist_basis,
        taxhist_basis_tax_id,
        taxhist_sequence,
        taxhist_percent,
        taxhist_amount,
        taxhist_tax,
        taxhist_docdate )
    VALUES
      ( NEW.vodist_vohead_id,
        getAdjustmentTaxTypeId(),
        NEW.vodist_tax_id,
        0,
        NULL,
        1,
        0,
        0,
        (NEW.vodist_amount * -1),
        _r.vohead_docdate );
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._vodistaftertrigger() OWNER TO admin;

--
-- Name: _vodistbeforetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _vodistbeforetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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

BEGIN
  IF (TG_OP = 'DELETE') THEN
    IF (OLD.vodist_tax_id <> -1) THEN
    -- Delete any existing voheadtax adjustment records
      DELETE FROM voheadtax
      WHERE ( (taxhist_parent_id=OLD.vodist_vohead_id)
        AND   (taxhist_tax_id=OLD.vodist_tax_id)
        AND   (taxhist_taxtype_id=getAdjustmentTaxTypeId()) );
    END IF;

    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._vodistbeforetrigger() OWNER TO admin;

--
-- Name: _voheadafterdeletetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _voheadafterdeletetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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

BEGIN

  DELETE
  FROM charass
  WHERE charass_target_type = 'VCH'
    AND charass_target_id = OLD.vohead_id;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._voheadafterdeletetrigger() OWNER TO admin;

--
-- Name: _voheadaftertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _voheadaftertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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 (TG_OP = 'DELETE') THEN
    PERFORM releaseVoNumber(CAST(OLD.vohead_number AS INTEGER));
    RETURN OLD;
  END IF;

  IF (TG_OP = 'INSERT') THEN
    PERFORM clearNumberIssue('VcNumber', NEW.vohead_number);
    RETURN NEW;
  END IF;

  IF (TG_OP = 'UPDATE') THEN
    IF ( (COALESCE(NEW.vohead_taxzone_id,-1) <> COALESCE(OLD.vohead_taxzone_id,-1)) OR
         (NEW.vohead_docdate <> OLD.vohead_docdate) OR
         (NEW.vohead_curr_id <> OLD.vohead_curr_id) ) THEN
      PERFORM calculateTaxHist( 'voitemtax',
                                voitem_id,
                                NEW.vohead_taxzone_id,
                                voitem_taxtype_id,
                                NEW.vohead_docdate,
                                NEW.vohead_curr_id,
                                (vodist_amount * -1) )
      FROM voitem JOIN vodist ON ( (vodist_vohead_id=voitem_vohead_id) AND
                                   (vodist_poitem_id=voitem_poitem_id) )
      WHERE (voitem_vohead_id = NEW.vohead_id);
    END IF;

    -- Touch any Misc Tax Distributions so voheadtax is recalculated
    IF (NEW.vohead_docdate <> OLD.vohead_docdate) THEN
      UPDATE vodist SET vodist_vohead_id=NEW.vohead_id
      WHERE ( (vodist_vohead_id=OLD.vohead_id)
        AND   (vodist_tax_id <> -1) );
    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._voheadaftertrigger() OWNER TO admin;

--
-- Name: _voheadbeforetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _voheadbeforetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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
  _recurid     INTEGER;
  _newparentid INTEGER;

BEGIN
  IF (TG_OP = 'DELETE') THEN
    IF (OLD.vohead_posted) THEN
      -- Cannot delete a posted voucher
      RAISE EXCEPTION 'Cannot delete a posted voucher';
    END IF;

    /* TODO: is setting recv_invoiced and poreject_invoiced to FALSE correct?
             this behavior is inherited from the now-defunct deleteVoucher.
     */
    UPDATE recv SET recv_vohead_id = NULL,
                    recv_voitem_id = NULL,
                    recv_invoiced  = FALSE
     WHERE recv_vohead_id = OLD.vohead_id;

    UPDATE poreject SET poreject_vohead_id = NULL,
                        poreject_voitem_id = NULL,
                        poreject_invoiced  = FALSE
     WHERE poreject_vohead_id = OLD.vohead_id;

    DELETE FROM vodist    WHERE vodist_vohead_id  = OLD.vohead_id;
    DELETE FROM voheadtax WHERE taxhist_parent_id = OLD.vohead_id;
    DELETE FROM voitem    WHERE voitem_vohead_id  = OLD.vohead_id;

    SELECT recur_id INTO _recurid
      FROM recur
     WHERE ((recur_parent_id=OLD.vohead_id)
        AND (recur_parent_type='V'));
    IF (_recurid IS NOT NULL) THEN
      SELECT vohead_id INTO _newparentid
        FROM vohead
       WHERE ((vohead_recurring_vohead_id=OLD.vohead_id)
          AND (vohead_id!=OLD.vohead_id))
       ORDER BY vohead_docdate
       LIMIT 1;

      IF (_newparentid IS NULL) THEN
        DELETE FROM recur WHERE recur_id=_recurid;
      ELSE
        UPDATE recur SET recur_parent_id=_newparentid
         WHERE recur_id=_recurid;
        UPDATE vohead SET vohead_recurring_vohead_id=_newparentid
         WHERE vohead_recurring_vohead_id=OLD.vohead_id
           AND vohead_id!=OLD.vohead_id;
      END IF;
    END IF;

    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._voheadbeforetrigger() OWNER TO admin;

--
-- Name: _voitemaftertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _voitemaftertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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
  _r RECORD;

BEGIN
  IF (TG_OP = 'DELETE') THEN
    RETURN OLD;
  END IF;

-- Cache Voucher Head
  SELECT * INTO _r
  FROM vohead
  WHERE (vohead_id=NEW.voitem_vohead_id);
  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Voucher head not found';
  END IF;

-- Calculate Tax
  PERFORM calculateTaxHist( 'voitemtax',
                            NEW.voitem_id,
                            COALESCE(_r.vohead_taxzone_id, -1),
                            NEW.voitem_taxtype_id,
                            COALESCE(_r.vohead_docdate, CURRENT_DATE),
                            COALESCE(_r.vohead_curr_id, -1),
                            COALESCE(SUM(vodist_amount * -1), 0) )
  FROM vodist
  WHERE ( (vodist_vohead_id=_r.vohead_id)
    AND   (vodist_poitem_id=NEW.voitem_poitem_id) );

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._voitemaftertrigger() OWNER TO admin;

--
-- Name: _voitembeforetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _voitembeforetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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

BEGIN
  IF (TG_OP = 'DELETE') THEN
    DELETE FROM voitemtax
    WHERE (taxhist_parent_id=OLD.voitem_id);

    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._voitembeforetrigger() OWNER TO admin;

--
-- Name: _warehoustrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _warehoustrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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;
  _check      BOOLEAN;
  _checkId    INTEGER;

BEGIN

  -- Checks
  -- Start with privileges
  IF (TG_OP = 'INSERT') THEN
    SELECT checkPrivilege('MaintainWarehouses') INTO _check;
    IF NOT (_check) THEN
      RAISE EXCEPTION 'You do not have privileges to add new Sites.';
    END IF;
  ELSE
    SELECT checkPrivilege('MaintainWarehouses') OR checkPrivilege('IssueCountTags') INTO _check;
    IF NOT (_check) THEN
      RAISE EXCEPTION 'You do not have privileges to alter a Site.';
    END IF;
  END IF;

  -- Code is required
  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.';
  END IF;

  -- Cost Category is required for Transit types
  IF ((NEW.warehous_transit) AND (NEW.warehous_costcat_id IS NULL)) THEN
    RAISE EXCEPTION 'You must supply a valid Cost Category for Transit Sites.';
  END IF;

  -- Code must be unique
  SELECT warehous_id INTO _checkId
  FROM whsinfo
  WHERE ( (UPPER(warehous_code)=UPPER(NEW.warehous_code))
    AND   (warehous_id<>NEW.warehous_id) );
  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
    FROM whsinfo
    WHERE (warehous_counttag_prefix=NEW.warehous_counttag_prefix);
  ELSE
    SELECT warehous_id INTO _checkId
    FROM whsinfo
    WHERE ( (warehous_counttag_prefix=NEW.warehous_counttag_prefix)
      AND   (warehous_id<>NEW.warehous_id) );
  END IF;
  IF (FOUND) THEN
    RAISE EXCEPTION 'You must supply a unique Count Tag Prefix.';
  END IF;

  -- Check Complete
  -- Change Log
  IF ( SELECT (metric_value='t')
       FROM metric
       WHERE (metric_name='WarehouseChangeLog') ) THEN

--  Cache the cmnttype_id for ChangeLog
    SELECT cmnttype_id INTO _cmnttypeid
    FROM cmnttype
    WHERE (cmnttype_name='ChangeLog');
    IF (FOUND) THEN
      IF (TG_OP = 'INSERT') THEN
        PERFORM postComment(_cmnttypeid, 'WH', NEW.warehous_id, 'Created');

      ELSIF (TG_OP = 'UPDATE') THEN
        IF (OLD.warehous_code <> NEW.warehous_code) THEN
          PERFORM postComment( _cmnttypeid, 'WH', NEW.warehous_id,
                               ('Code Changed from "' || OLD.warehous_code || '" to "' || NEW.warehous_code || '"') );
        END IF;

        IF (OLD.warehous_descrip <> NEW.warehous_descrip) THEN
          PERFORM postComment( _cmnttypeid, 'WH', NEW.warehous_id,
                               ( 'Description Changed from "' || OLD.warehous_descrip ||
                                 '" to "' || NEW.warehous_descrip || '"' ) );
        END IF;

        IF (OLD.warehous_active <> NEW.warehous_active) THEN
          IF (NEW.warehous_active) THEN
            PERFORM postComment(_cmnttypeid, 'WH', NEW.warehous_id, 'Activated');
          ELSE
            PERFORM postComment(_cmnttypeid, 'WH', NEW.warehous_id, 'Deactivated');
          END IF;
        END IF;

      END IF;
    END IF;
  END IF;

  RETURN NEW;

END;
$$;


ALTER FUNCTION public._warehoustrigger() OWNER TO admin;

--
-- Name: _whsezonetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _whsezonetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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      BOOLEAN;
  _checkId    INTEGER;

BEGIN

  -- Checks
  -- Start with privileges
  IF (TG_OP = 'INSERT') THEN
    SELECT checkPrivilege('MaintainWarehouses') INTO _check;
    IF NOT (_check) THEN
      RAISE EXCEPTION 'You do not have privileges to add new Site Zones.';
    END IF;
  ELSE
    SELECT checkPrivilege('MaintainWarehouses') INTO _check;
    IF NOT (_check) THEN
      RAISE EXCEPTION 'You do not have privileges to alter a Site Zone.';
    END IF;
  END IF;

  -- Name is required
  IF (LENGTH(COALESCE(NEW.whsezone_name,''))=0) THEN
    RAISE EXCEPTION 'You must supply a valid Site Zone Name.';
  END IF;
  
  -- Site is required
  IF (NEW.whsezone_warehous_id IS NULL) THEN
    RAISE EXCEPTION 'You must supply a valid Site.';
  END IF;

  RETURN NEW;

END;
$$;


ALTER FUNCTION public._whsezonetrigger() OWNER TO admin;

--
-- Name: _womatlaftertrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _womatlaftertrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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

BEGIN

  IF (TG_OP = 'INSERT') THEN

  --  Create any required P/R's
    PERFORM createPr('W', NEW.womatl_id)
       FROM itemsite 
      WHERE ((itemsite_id=NEW.womatl_itemsite_id)
        AND  (itemsite_createpr));

  END IF;

  RETURN NEW;

END;
$$;


ALTER FUNCTION public._womatlaftertrigger() OWNER TO admin;

--
-- Name: _wotrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _wotrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- 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

BEGIN

  IF (TG_OP = 'INSERT') THEN
    PERFORM postEvent('WoCreated', 'W', NEW.wo_id,
                      itemsite_warehous_id,
                      (NEW.wo_number || '-' || NEW.wo_subnumber),
                      NULL, NULL, NULL, NULL)
    FROM itemsite
    WHERE (itemsite_id=NEW.wo_itemsite_id)
      AND (NEW.wo_duedate <= (CURRENT_DATE + itemsite_eventfence));

    PERFORM postComment('ChangeLog', 'W', NEW.wo_id, 'Created');

    IF (fetchMetricText('WONumberGeneration') IN ('A','O')) THEN
      --- clear the number from the issue cache
      PERFORM clearNumberIssue('WoNumber', NEW.wo_number);
    END IF;

    RETURN NEW;

  ELSE
      IF (TG_OP = 'DELETE') THEN
      PERFORM postEvent('WoCancelled', 'W', OLD.wo_id,
                        itemsite_warehous_id,
                        (OLD.wo_number || '-' || OLD.wo_subnumber),
                        NULL, NULL, NULL, NULL)
      FROM itemsite
      WHERE (itemsite_id=OLD.wo_itemsite_id)
        AND (OLD.wo_duedate <= (CURRENT_DATE + itemsite_eventfence));

      DELETE FROM docass WHERE docass_source_id = OLD.wo_id AND docass_source_type = 'W';
      DELETE FROM docass WHERE docass_target_id = OLD.wo_id AND docass_target_type = 'W';

      DELETE FROM comment
      WHERE ( (comment_source='W')
       AND (comment_source_id=OLD.wo_id) );

      DELETE FROM charass
       WHERE ((charass_target_type='W')
         AND  (charass_target_id=OLD.wo_id));

       RETURN OLD;

    ELSE
      IF (TG_OP = 'UPDATE') THEN

        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_qtyord, OLD.wo_qtyord, NULL, NULL)
          FROM itemsite
          WHERE (itemsite_id=NEW.wo_itemsite_id)
            AND ( (NEW.wo_duedate <= (CURRENT_DATE + itemsite_eventfence))
             OR   (OLD.wo_duedate <= (CURRENT_DATE + itemsite_eventfence)) );

          PERFORM postComment( 'ChangeLog', 'W', NEW.wo_id,
                               ( 'Qty. Ordered Changed from ' || formatQty(OLD.wo_qtyord) ||
                                 ' to ' || formatQty(NEW.wo_qtyord ) ) );
        END IF;

        IF (NEW.wo_duedate <> OLD.wo_duedate) THEN
          PERFORM postEvent('WoDueDateChanged', 'W', NEW.wo_id,
                            itemsite_warehous_id,
                            (NEW.wo_number || '-' || NEW.wo_subnumber),
                            NULL, NULL, NEW.wo_duedate, OLD.wo_duedate)
          FROM itemsite
          WHERE (itemsite_id=NEW.wo_itemsite_id)
            AND ( (NEW.wo_duedate <= (CURRENT_DATE + itemsite_eventfence))
             OR   (OLD.wo_duedate <= (CURRENT_DATE + itemsite_eventfence)) );

          PERFORM postComment( 'ChangeLog', 'W', NEW.wo_id,
                               ( 'Due Date Changed from ' || formatDate(OLD.wo_duedate) ||
                                 ' to ' || formatDate(NEW.wo_duedate ) ) );
        END IF;

        IF (NEW.wo_status <> OLD.wo_status) THEN
          PERFORM postComment( 'ChangeLog', 'W', NEW.wo_id,
                               ('Status Changed from ' || OLD.wo_status || ' to ' || NEW.wo_status) );
        END IF;

      END IF;
    END IF;
  END IF;

  IF (TG_OP = 'UPDATE') THEN
    IF (NEW.wo_prj_id <> OLD.wo_prj_id) THEN
      UPDATE wo SET wo_prj_id=NEW.wo_prj_id
      WHERE (wo_ordtype='W')
        AND (wo_ordid=NEW.wo_id);
    END IF;
  END IF;

  RETURN NEW;

END;
$$;


ALTER FUNCTION public._wotrigger() OWNER TO admin;

--
-- Name: acknowledgemessage(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION acknowledgemessage(integer) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
-- 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
  pMsgid ALIAS FOR $1;

BEGIN

  UPDATE msguser
  SET msguser_viewed=CURRENT_TIMESTAMP
  WHERE ( (msguser_msg_id=pMsgid)
   AND (msguser_username=getEffectiveXtUser()) );

  RETURN TRUE;

END;
$_$;


ALTER FUNCTION public.acknowledgemessage(integer) OWNER TO admin;

--
-- Name: actcost(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION actcost(integer) RETURNS numeric
    LANGUAGE plpgsql
    AS $_$
-- 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
  RETURN actCost($1, NULL, baseCurrId());
END;
$_$;


ALTER FUNCTION public.actcost(integer) OWNER TO admin;

--
-- Name: actcost(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION actcost(integer, integer) RETURNS numeric
    LANGUAGE plpgsql
    AS $_$
-- 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
  RETURN actCost($1, $2, baseCurrId());
END;
$_$;


ALTER FUNCTION public.actcost(integer, integer) OWNER TO admin;

--
-- Name: actcost(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION actcost(integer, integer, integer) RETURNS numeric
    LANGUAGE plpgsql
    AS $_$
-- 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
  pItemid ALIAS FOR $1;
  pBomitemid ALIAS FOR $2;
  pCurrid ALIAS FOR $3;
  _cost NUMERIC;

BEGIN

  -- Return actual cost in the given currency at the current conversion rate
  SELECT SUM(CASE WHEN (bomitemcost_id IS NOT NULL) THEN
                  ROUND(currToCurr(bomitemcost_curr_id, pCurrid, bomitemcost_actcost, CURRENT_DATE), 6)
                  ELSE
                  ROUND(currToCurr(itemcost_curr_id, pCurrid, itemcost_actcost, CURRENT_DATE), 6)
             END) INTO _cost
  FROM itemcost
    LEFT OUTER JOIN bomitemcost ON (bomitemcost_bomitem_id=pBomitemid AND bomitemcost_costelem_id=itemcost_costelem_id)
  WHERE (itemcost_item_id=pItemid);

  IF (_cost IS NULL) THEN
    RETURN 0;
  ELSE
    RETURN _cost;
  END IF;

END;
$_$;


ALTER FUNCTION public.actcost(integer, integer, integer) OWNER TO admin;

--
-- Name: addrusecount(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION addrusecount(integer) RETURNS integer
    LANGUAGE plpgsql STABLE
    AS $_$
-- 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
  pAddrId ALIAS FOR $1;
  _fk RECORD;
  _r RECORD;
  _seq INTEGER;
  _col TEXT;
  _qry TEXT;
  _count INTEGER = 0;

BEGIN
  -- Determine where this address is used by analyzing foreign key linkages
  -- TO DO: Can this be rationalized with cntctused(int)?
  FOR _fk IN
    SELECT pg_namespace.nspname AS schemaname, con.relname AS tablename, conkey AS seq, conrelid AS class_id 
    FROM pg_constraint, pg_class f, pg_class con, pg_namespace
    WHERE confrelid=f.oid
    AND conrelid=con.oid
    AND f.relname = 'addr'
    AND con.relnamespace=pg_namespace.oid
    AND con.relname NOT IN ('pohead') -- exception(s) where address key doesn't actually drive document information
  LOOP
    -- Validate
    IF (ARRAY_UPPER(_fk.seq,1) > 1) THEN
      RAISE EXCEPTION 'Checks to tables where the address is one of multiple foreign key columns is not supported. Error on Table: %',
        pg_namespace.nspname || '.' || con.relname;
    END IF;
    
    _seq := _fk.seq[1];

    -- Get the specific column name
    SELECT attname INTO _col
    FROM pg_attribute, pg_class
    WHERE ((attrelid=pg_class.oid)
    AND (pg_class.oid=_fk.class_id)
    AND (attnum=_seq));

    -- See if there are dependencies
    _qry := 'SELECT * 
            FROM ' || _fk.schemaname || '.' || _fk.tablename || '
            WHERE ('|| _col || '=' || pAddrId || ');';

    FOR _r IN 
      EXECUTE _qry
    LOOP
      _count := _count + 1;
    END LOOP;
         
  END LOOP;

  RETURN _count;

END;
$_$;


ALTER FUNCTION public.addrusecount(integer) OWNER TO admin;

--
-- Name: addtaxtoglseries(integer, text, text, text, integer, date, date, text, integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION addtaxtoglseries(integer, text, text, text, integer, date, date, text, integer, text) RETURNS numeric
    LANGUAGE plpgsql
    AS $_$
-- 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
  pSequence	ALIAS FOR $1;
  pSource	ALIAS FOR $2;
  pDocType	ALIAS FOR $3;
  pDocNumber	ALIAS FOR $4;
  pCurrId     ALIAS FOR $5;
  pExchDate	ALIAS FOR $6;
  pDistDate	ALIAS FOR $7;
  pTableName	ALIAS FOR $8;
  pParentId	ALIAS FOR $9;
  pNotes	ALIAS FOR $10;

  _count	INTEGER := 0;
  _baseTax	NUMERIC := 0;
  _returnVal	NUMERIC := 0;
  _t		RECORD;
  _test	INTEGER := 0;

BEGIN

-- This is just a fancy select statement on taxhist.
-- Because all tax records tables inherit from taxhist,
-- we can use the same select statement for all.
-- https://www.postgresql.org/docs/8.1/static/ddl-inherit.html
-- pTableName in the where clause narrows down the selection
-- to the correct sub table.

  FOR _t IN SELECT *
            FROM taxhist JOIN tax ON (tax_id = taxhist_tax_id)
                         JOIN pg_class ON (pg_class.oid = taxhist.tableoid)
            WHERE ( (taxhist_parent_id = pParentId)
              AND   (relname = pTableName) ) LOOP

    _count := _count + 1;
    _baseTax := currToBase(pCurrId, _t.taxhist_tax, pExchDate);
    _returnVal := _returnVal + _baseTax;
    PERFORM insertIntoGLSeries( pSequence, pSource, pDocType, pDocNumber,
                                _t.tax_sales_accnt_id, _baseTax,
                                pDistDate, pNotes );
                                
    UPDATE taxhist SET 
      taxhist_docdate=pExchDate,
      taxhist_distdate=pDistDate,
      taxhist_curr_id=pCurrId,
      taxhist_curr_rate=curr_rate
    FROM curr_rate
    WHERE ((taxhist_id=_t.taxhist_id)
      AND (pCurrId=curr_id)
      AND ( pExchDate BETWEEN curr_effective 
                          AND curr_expires) );

  END LOOP;

  RETURN _returnVal;
END;
$_$;


ALTER FUNCTION public.addtaxtoglseries(integer, text, text, text, integer, date, date, text, integer, text) OWNER TO admin;

--
-- Name: addtopackinglistbatch(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION addtopackinglistbatch(integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- 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
  pSoheadid	ALIAS FOR $1;
  returnVal	INTEGER;
BEGIN

  -- MIN because error codes are negative
  SELECT MIN(addToPackingListBatch('SO', pSoheadid, shiphead_id)) INTO returnVal
  FROM shiphead
  WHERE ((shiphead_order_id=pSoheadid)
    AND  (NOT shiphead_shipped)
    AND  (shiphead_order_type='SO'));
  IF (NOT FOUND OR returnVal IS NULL) THEN
    returnVal := addToPackingListBatch('SO', pSoheadid, NULL);
  END IF;

  RETURN returnVal;
END;
$_$;


ALTER FUNCTION public.addtopackinglistbatch(integer) OWNER TO admin;

--
-- Name: addtopackinglistbatch(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION addtopackinglistbatch(integer, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- 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
  RETURN addToPackingListBatch('SO', $1, $2);
END;
$_$;


ALTER FUNCTION public.addtopackinglistbatch(integer, integer) OWNER TO admin;

--
-- Name: addtopackinglistbatch(text, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION addtopackinglistbatch(text, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- 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
  pheadtype	ALIAS FOR $1;
  pheadid	ALIAS FOR $2;
  returnVal	INTEGER;
BEGIN
  -- MIN because error codes are negative
  SELECT MIN(addToPackingListBatch(pheadtype, pheadid, shiphead_id)) INTO returnVal
  FROM shiphead
  WHERE ((shiphead_order_id=pheadid)
    AND  (NOT shiphead_shipped)
    AND  (shiphead_order_type=pheadtype));

  IF (NOT FOUND OR returnVal IS NULL) THEN
    returnVal := addToPackingListBatch(pheadtype, pheadid, NULL);
  END IF;

  RETURN returnVal;
END;
$_$;


ALTER FUNCTION public.addtopackinglistbatch(text, integer) OWNER TO admin;

--
-- Name: addtopackinglistbatch(text, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION addtopackinglistbatch(text, integer, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- 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
  pheadtype	ALIAS FOR $1;
  pheadid	ALIAS FOR $2;
  pshipheadid	ALIAS FOR $3;
  _check INTEGER;

BEGIN
  SELECT pack_id INTO _check
  FROM pack
  WHERE ((pack_head_id=pheadid)
    AND  ((pack_shiphead_id=pshipheadid) OR 
	  (pshipheadid IS NULL AND pack_shiphead_id IS NULL))
    AND  (pack_head_type=pheadtype)
	);

  IF (NOT FOUND) THEN
    INSERT INTO pack
    ( pack_head_type, pack_head_id, pack_shiphead_id, pack_printed )
    VALUES
    ( pheadtype, pheadid, pshipheadid, FALSE );
    -- Auto Firm Sales Orders conditionally based on metric
    IF ( (pheadtype = 'SO') AND (fetchMetricBool('FirmSalesOrderPackingList')) ) THEN
      UPDATE coitem SET coitem_firm=TRUE
      WHERE (coitem_cohead_id=pheadid);
    END IF; 
  END IF;

  RETURN pheadid;

END;
$_$;


ALTER FUNCTION public.addtopackinglistbatch(text, integer, integer) OWNER TO admin;

--
-- Name: addtopackinglistbatch(integer, text, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION addtopackinglistbatch(integer, text, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- 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
  pwarehousid	ALIAS FOR $1;
  pheadtype	ALIAS FOR $2;
  pheadid	ALIAS FOR $3;
  returnVal	INTEGER;
BEGIN
  -- MIN because error codes are negative
  SELECT MIN(addToPackingListBatch(pheadtype, pheadid,
                                   getOpenShipmentId(pheadtype, pheadid, pwarehousid))) INTO returnVal;

  IF (NOT FOUND OR returnVal IS NULL) THEN
    returnVal := addToPackingListBatch(pheadtype, pheadid, NULL);
  END IF;

  RETURN returnVal;
END;
$_$;


ALTER FUNCTION public.addtopackinglistbatch(integer, text, integer) OWNER TO admin;

--
-- Name: adjustinvvalue(integer, numeric, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION adjustinvvalue(integer, numeric, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- 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
  pItemsiteid     ALIAS FOR $1;
  pNewValue       ALIAS FOR $2;
  pAccountid      ALIAS FOR $3;
  _delta          NUMERIC;
  _glreturn       INTEGER;
  _invhistid      INTEGER;
  _itemlocSeries  INTEGER;

BEGIN

  SELECT pNewValue - itemsite_value INTO _delta
  FROM itemsite
  WHERE (itemsite_id=pItemsiteid)
  FOR UPDATE;

  IF (NOT FOUND) THEN
    RETURN -1;
  END IF;

  SELECT insertGLTransaction('I/M', '', 'Post Value',
         'Inventory Value Adjustment for ' || item_number,
         COALESCE (pAccountid, costcat_adjustment_accnt_id),
         costcat_asset_accnt_id, -1,
         _delta, CURRENT_DATE) INTO _glreturn
  FROM itemsite
   JOIN costcat ON (itemsite_costcat_id=costcat_id)
   JOIN item ON (itemsite_item_id=item_id)
  WHERE (itemsite_id=pItemsiteid);

--  Create the AD transaction
  INSERT INTO invhist
   ( invhist_itemsite_id,
     invhist_transdate, invhist_transtype, invhist_invqty,
     invhist_qoh_before, invhist_qoh_after,
     invhist_docnumber, invhist_comments,
     invhist_invuom, invhist_unitcost, invhist_hasdetail,
     invhist_costmethod, invhist_value_before, invhist_value_after,
     invhist_series )
  SELECT itemsite_id,
         CURRENT_TIMESTAMP, 'AD', 0.0,
         itemsite_qtyonhand, itemsite_qtyonhand,
         '', 'Inventory Value Adjustment',
         uom_name, _delta, FALSE,
         itemsite_costmethod, itemsite_value, pNewValue,
         0
  FROM itemsite, item, uom
  WHERE ( (itemsite_item_id=item_id)
   AND (item_inv_uom_id=uom_id)
   AND (itemsite_id=pItemsiteid) );

  UPDATE itemsite SET itemsite_value=pNewValue
  WHERE (itemsite_id=pItemsiteid);

  RETURN 0;

END;
$_$;


ALTER FUNCTION public.adjustinvvalue(integer, numeric, integer) OWNER TO admin;

--
-- Name: adjustments(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION adjustments(text) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
-- 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
  pTransType ALIAS FOR $1;

BEGIN
  IF (pTransType IN ('CC', 'AD')) THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;

END;
$_$;


ALTER FUNCTION public.adjustments(text) OWNER TO admin;

--
-- Name: allocatedforso(integer, date); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION allocatedforso(integer, date) RETURNS numeric
    LANGUAGE plpgsql STABLE
    AS $_$
-- 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
  pItemsiteid ALIAS FOR $1;
  pDate ALIAS FOR $2;

BEGIN

  RETURN allocatedForSo(pItemsiteid, startOfTime(), pDate);

END;
$_$;


ALTER FUNCTION public.allocatedforso(integer, date) OWNER TO admin;

--
-- Name: allocatedforso(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION allocatedforso(integer, integer) RETURNS numeric
    LANGUAGE plpgsql STABLE
    AS $_$
-- 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
  pItemsiteid ALIAS FOR $1;
  pDate ALIAS FOR $2;

BEGIN

  RETURN allocatedForSo(pItemsiteid, startOfTime(), (CURRENT_DATE + pDate));

END;
$_$;


ALTER FUNCTION public.allocatedforso(integer, integer) OWNER TO admin;

--
-- Name: allocatedforso(integer, date, date); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION allocatedforso(integer, date, date) RETURNS numeric
    LANGUAGE plpgsql STABLE
    AS $_$
-- 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
  pItemsiteid ALIAS FOR $1;
  pStartDate ALIAS FOR $2;
  pEndDate ALIAS FOR $3;
  _qty NUMERIC;

BEGIN

  SELECT COALESCE(SUM(noNeg(itemuomtouom(itemsite_item_id, coitem_qty_uom_id, NULL, coitem_qtyord - (coitem_qtyshipped + qtyAtShipping(coitem_id)) + coitem_qtyreturned))), 0.0) INTO _qty
  FROM coitem, itemsite, item
  WHERE ( (coitem_itemsite_id=itemsite_id)
    AND (itemsite_item_id=item_id)
    AND (coitem_status='O')
    AND (coitem_itemsite_id=pItemsiteid)
    AND (coitem_scheddate BETWEEN pStartDate AND pEndDate) );

  RETURN _qty;

END;
$_$;


ALTER FUNCTION public.allocatedforso(integer, date, date) OWNER TO admin;

--
-- Name: allocatedforwo(integer, date); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION allocatedforwo(integer, date) RETURNS numeric
    LANGUAGE plpgsql STABLE
    AS $_$
-- 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
  pItemsiteid ALIAS FOR $1;
  pDate ALIAS FOR $2;

BEGIN

  RETURN allocatedForWo(pItemsiteid, startOfTime(), pDate);

END;
$_$;


ALTER FUNCTION public.allocatedforwo(integer, date) OWNER TO admin;

--
-- Name: allocatedforwo(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION allocatedforwo(integer, integer) RETURNS numeric
    LANGUAGE plpgsql STABLE
    AS $_$
-- 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
  pItemsiteid ALIAS FOR $1;
  pLookAheadDays ALIAS FOR $2;

BEGIN

  RETURN allocatedForWo(pItemsiteid, startOfTime(), (CURRENT_DATE + pLookaheadDays));

END;
$_$;


ALTER FUNCTION public.allocatedforwo(integer, integer) OWNER TO admin;

--
-- Name: allocatedforwo(integer, date, date); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION allocatedforwo(integer, date, date) RETURNS numeric
    LANGUAGE plpgsql STABLE
    AS $_$
-- 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
  pItemsiteid ALIAS FOR $1;
  pStartDate ALIAS FOR $2;
  pEndDate ALIAS FOR $3;
  _itemtype TEXT;
  _qty NUMERIC;

BEGIN

  SELECT item_type INTO _itemtype
  FROM itemsite JOIN item ON (item_id=itemsite_item_id)
  WHERE (itemsite_id=pItemsiteid);

  IF (_itemtype != 'T') THEN
    SELECT
      COALESCE(SUM(noNeg(itemuomtouom(itemsite_item_id, womatl_uom_id, NULL, womatl_qtyreq - womatl_qtyiss))), 0.0) INTO _qty
    FROM womatl JOIN wo ON (wo_id=womatl_wo_id AND wo_status IN ('E','I','R'))
                JOIN itemsite ON (itemsite_id=womatl_itemsite_id)
    WHERE (womatl_itemsite_id=pItemsiteid)
      AND (womatl_duedate BETWEEN pStartDate AND pEndDate);
  ELSE
    SELECT
      COALESCE(SUM(noNeg(itemuomtouom(itemsite_item_id, womatl_uom_id, NULL, womatl_qtyreq))), 0.0)  -
	(
		SELECT COALESCE(SUM(invhist_invqty),0) 
		FROM itemsite, item, wo, womatl
			LEFT OUTER JOIN womatlpost ON (womatl_id=womatlpost_womatl_id)
			LEFT OUTER JOIN invhist ON ((womatlpost_invhist_id=invhist_id)
                            AND (invhist_invqty > 0))
		WHERE ( (womatl_itemsite_id=pItemsiteid)
		AND (womatl_itemsite_id=itemsite_id)
		AND (itemsite_item_id=item_id)
		AND (womatl_duedate BETWEEN pStartDate AND pEndDate) 
		AND (wo_id=womatl_wo_id)
		AND (wo_status IN ('E','I','R')) )
	) INTO _qty
    FROM womatl JOIN wo ON (wo_id=womatl_wo_id AND wo_status IN ('E','I','R'))
                JOIN itemsite ON (itemsite_id=womatl_itemsite_id)
    WHERE (womatl_itemsite_id=pItemsiteid)
      AND (womatl_duedate BETWEEN pStartDate AND pEndDate);
  END IF;

  RETURN COALESCE(_qty,0);

END;
$_$;


ALTER FUNCTION public.allocatedforwo(integer, date, date) OWNER TO admin;

--
-- Name: alterencrypt(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION alterencrypt(text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- 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
  pOldKey ALIAS FOR $1;
  pNewKey ALIAS FOR $2;
  _cc RECORD;
  _ccaud RECORD;
  _metricenc RECORD;
  num_updated INTEGER;

BEGIN

  num_updated := 0;

-- Update ccard

  FOR _cc IN SELECT ccard_id, 
             decrypt(setbytea(ccard_name), setbytea(pOldKey), 'bf') AS ccard_name,
             decrypt(setbytea(ccard_address1), setbytea(pOldKey), 'bf') AS ccard_address1,
             decrypt(setbytea(ccard_address2), setbytea(pOldKey), 'bf') AS ccard_address2,
             decrypt(setbytea(ccard_city), setbytea(pOldKey), 'bf') AS ccard_city,
             decrypt(setbytea(ccard_state), setbytea(pOldKey), 'bf') AS ccard_state,
             decrypt(setbytea(ccard_zip), setbytea(pOldKey), 'bf') AS ccard_zip,
             decrypt(setbytea(ccard_country), setbytea(pOldKey), 'bf') AS ccard_country,
             decrypt(setbytea(ccard_number), setbytea(pOldKey), 'bf') AS ccard_number,
             decrypt(setbytea(ccard_month_expired), setbytea(pOldKey), 'bf') AS ccard_month_expired,
             decrypt(setbytea(ccard_year_expired), setbytea(pOldKey), 'bf') AS ccard_year_expired
      FROM ccard LOOP

      UPDATE ccard
             set ccard_name = encrypt(setbytea(_cc.ccard_name), setbytea(pNewKey), 'bf'),
                 ccard_address1 = encrypt(setbytea(_cc.ccard_address1), setbytea(pNewKey), 'bf'),
                 ccard_address2 = encrypt(setbytea(_cc.ccard_address2), setbytea(pNewKey), 'bf'),
                 ccard_city = encrypt(setbytea(_cc.ccard_city), setbytea(pNewKey), 'bf'),
                 ccard_state = encrypt(setbytea(_cc.ccard_state), setbytea(pNewKey), 'bf'),
                 ccard_zip = encrypt(setbytea(_cc.ccard_zip), setbytea(pNewKey), 'bf'),
                 ccard_country = encrypt(setbytea(_cc.ccard_country), setbytea(pNewKey), 'bf'),
                 ccard_number = encrypt(setbytea(_cc.ccard_number), setbytea(pNewKey), 'bf'),
                 ccard_month_expired = encrypt(setbytea(_cc.ccard_month_expired), setbytea(pNewKey), 'bf'),
                 ccard_year_expired = encrypt(setbytea(_cc.ccard_year_expired), setbytea(pNewKey), 'bf')
      WHERE ccard_id = _cc.ccard_id;

      num_updated := num_updated + 1;

  END LOOP;

-- Update ccardaud

  FOR _ccaud IN SELECT ccardaud_id, 
             decrypt(setbytea(ccardaud_ccard_name_old), setbytea(pOldKey), 'bf') AS ccardaud_ccard_name_old,
             decrypt(setbytea(ccardaud_ccard_name_new), setbytea(pOldKey), 'bf') AS ccardaud_ccard_name_new,
             decrypt(setbytea(ccardaud_ccard_address1_old), setbytea(pOldKey), 'bf') AS ccardaud_ccard_address1_old,
             decrypt(setbytea(ccardaud_ccard_address1_new), setbytea(pOldKey), 'bf') AS ccardaud_ccard_address1_new,
             decrypt(setbytea(ccardaud_ccard_address2_old), setbytea(pOldKey), 'bf') AS ccardaud_ccard_address2_old,
             decrypt(setbytea(ccardaud_ccard_address2_new), setbytea(pOldKey), 'bf') AS ccardaud_ccard_address2_new,
             decrypt(setbytea(ccardaud_ccard_city_old), setbytea(pOldKey), 'bf') AS ccardaud_ccard_city_old,
             decrypt(setbytea(ccardaud_ccard_city_new), setbytea(pOldKey), 'bf') AS ccardaud_ccard_city_new,
             decrypt(setbytea(ccardaud_ccard_state_old), setbytea(pOldKey), 'bf') AS ccardaud_ccard_state_old,
             decrypt(setbytea(ccardaud_ccard_state_new), setbytea(pOldKey), 'bf') AS ccardaud_ccard_state_new,
             decrypt(setbytea(ccardaud_ccard_zip_old), setbytea(pOldKey), 'bf') AS ccardaud_ccard_zip_old,
             decrypt(setbytea(ccardaud_ccard_zip_new), setbytea(pOldKey), 'bf') AS ccardaud_ccard_zip_new,
             decrypt(setbytea(ccardaud_ccard_country_old), setbytea(pOldKey), 'bf') AS ccardaud_ccard_country_old,
             decrypt(setbytea(ccardaud_ccard_country_new), setbytea(pOldKey), 'bf') AS ccardaud_ccard_country_new,
             decrypt(setbytea(ccardaud_ccard_number_old), setbytea(pOldKey), 'bf') AS ccardaud_ccard_number_old,
             decrypt(setbytea(ccardaud_ccard_number_new), setbytea(pOldKey), 'bf') AS ccardaud_ccard_number_new,
             decrypt(setbytea(ccardaud_ccard_month_expired_old), setbytea(pOldKey), 'bf') AS ccardaud_ccard_month_expired_old,
             decrypt(setbytea(ccardaud_ccard_month_expired_new), setbytea(pOldKey), 'bf') AS ccardaud_ccard_month_expired_new,
             decrypt(setbytea(ccardaud_ccard_year_expired_old), setbytea(pOldKey), 'bf') AS ccardaud_ccard_year_expired_old,
             decrypt(setbytea(ccardaud_ccard_year_expired_new), setbytea(pOldKey), 'bf') AS ccardaud_ccard_year_expired_new
      FROM ccardaud LOOP

      UPDATE ccardaud
             set ccardaud_ccard_name_old = encrypt(setbytea(_ccaud.ccardaud_ccard_name_old), setbytea(pNewKey), 'bf'),
                 ccardaud_ccard_name_new = encrypt(setbytea(_ccaud.ccardaud_ccard_name_new), setbytea(pNewKey), 'bf'),
                 ccardaud_ccard_address1_old = encrypt(setbytea(_ccaud.ccardaud_ccard_address1_old), setbytea(pNewKey), 'bf'),
                 ccardaud_ccard_address1_new = encrypt(setbytea(_ccaud.ccardaud_ccard_address1_new), setbytea(pNewKey), 'bf'),
                 ccardaud_ccard_address2_old = encrypt(setbytea(_ccaud.ccardaud_ccard_address2_old), setbytea(pNewKey), 'bf'),
                 ccardaud_ccard_address2_new = encrypt(setbytea(_ccaud.ccardaud_ccard_address2_new), setbytea(pNewKey), 'bf'),
                 ccardaud_ccard_city_old = encrypt(setbytea(_ccaud.ccardaud_ccard_city_old), setbytea(pNewKey), 'bf'),
                 ccardaud_ccard_city_new = encrypt(setbytea(_ccaud.ccardaud_ccard_city_new), setbytea(pNewKey), 'bf'),
                 ccardaud_ccard_state_old = encrypt(setbytea(_ccaud.ccardaud_ccard_state_old), setbytea(pNewKey), 'bf'),
                 ccardaud_ccard_state_new = encrypt(setbytea(_ccaud.ccardaud_ccard_state_new), setbytea(pNewKey), 'bf'),
                 ccardaud_ccard_zip_old = encrypt(setbytea(_ccaud.ccardaud_ccard_zip_old), setbytea(pNewKey), 'bf'),
                 ccardaud_ccard_zip_new = encrypt(setbytea(_ccaud.ccardaud_ccard_zip_new), setbytea(pNewKey), 'bf'),
                 ccardaud_ccard_country_old = encrypt(setbytea(_ccaud.ccardaud_ccard_country_old), setbytea(pNewKey), 'bf'),
                 ccardaud_ccard_country_new = encrypt(setbytea(_ccaud.ccardaud_ccard_country_new), setbytea(pNewKey), 'bf'),
                 ccardaud_ccard_number_old = encrypt(setbytea(_ccaud.ccardaud_ccard_number_old), setbytea(pNewKey), 'bf'),
                 ccardaud_ccard_number_new = encrypt(setbytea(_ccaud.ccardaud_ccard_number_new), setbytea(pNewKey), 'bf'),
                 ccardaud_ccard_month_expired_old = encrypt(setbytea(_ccaud.ccardaud_ccard_month_expired_old), setbytea(pNewKey), 'bf'),
                 ccardaud_ccard_month_expired_new = encrypt(setbytea(_ccaud.ccardaud_ccard_month_expired_new), setbytea(pNewKey), 'bf'),
                 ccardaud_ccard_year_expired_old = encrypt(setbytea(_ccaud.ccardaud_ccard_year_expired_old), setbytea(pNewKey), 'bf'),
                 ccardaud_ccard_year_expired_new = encrypt(setbytea(_ccaud.ccardaud_ccard_year_expired_new), setbytea(pNewKey), 'bf')
      WHERE ccardaud_id = _ccaud.ccardaud_id;

      num_updated := num_updated + 1;

  END LOOP;

-- Update metricenc

  FOR _metricenc IN SELECT metricenc_id, 
             decrypt(setbytea(metricenc_value), setbytea(pOldKey), 'bf') AS metricenc_value
      FROM metricenc LOOP

      UPDATE metricenc
             set metricenc_value = encrypt(setbytea(_metricenc.metricenc_value), setbytea(pNewKey), 'bf')
      WHERE metricenc_id = _metricenc.metricenc_id;

      num_updated := num_updated + 1;

  END LOOP;


  RETURN num_updated;

END;
$_$;


ALTER FUNCTION public.alterencrypt(text, text) OWNER TO admin;

--
-- Name: apaging(date, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION apaging(date, boolean) RETURNS SETOF apaging
    LANGUAGE plpgsql
    AS $_$
-- 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
  pAsOfDate ALIAS FOR $1;
  pUseDocDate ALIAS FOR $2;
  _row apaging%ROWTYPE;
  _x RECORD;
  _returnVal INTEGER;
  _asOfDate DATE;
BEGIN

  _asOfDate := COALESCE(pAsOfDate,current_date);

  FOR _x IN
        SELECT
        --report uses currency rate snapshot to convert all amounts to base based on apopen_docdate to ensure the same exchange rate

        --today and greater base:
        CASE WHEN((apopen.apopen_duedate >= DATE(_asOfDate)))
        THEN (((apopen.apopen_amount - apopen.apopen_paid +
                COALESCE(SUM(apapply_target_paid * apopen.apopen_curr_rate / target.apopen_curr_rate),0))) / apopen.apopen_curr_rate *
                CASE WHEN (apopen.apopen_doctype IN ('D', 'V')) THEN 1 ELSE -1 END) ELSE 0 END AS cur_val,

        --0 to 30 base
        CASE WHEN((apopen.apopen_duedate >= DATE(_asOfDate)-30) AND (apopen.apopen_duedate < DATE(_asOfDate)))
        THEN (((apopen.apopen_amount - apopen.apopen_paid +
                COALESCE(SUM(apapply_target_paid * apopen.apopen_curr_rate / target.apopen_curr_rate),0))) / apopen.apopen_curr_rate *
                CASE WHEN (apopen.apopen_doctype IN ('D', 'V')) THEN 1 ELSE -1 END) ELSE 0 END AS thirty_val,

        --30-60 base
        CASE WHEN((apopen.apopen_duedate >= DATE(_asOfDate)-60) AND (apopen.apopen_duedate < DATE(_asOfDate) - 30 ))
        THEN (((apopen.apopen_amount - apopen.apopen_paid +
                COALESCE(SUM(apapply_target_paid * apopen.apopen_curr_rate / target.apopen_curr_rate),0))) / apopen.apopen_curr_rate *
                CASE WHEN (apopen.apopen_doctype IN ('D', 'V')) THEN 1 ELSE -1 END) ELSE 0 END AS sixty_val,

        --60-90 base
        CASE WHEN((apopen.apopen_duedate >= DATE(_asOfDate)-90) AND (apopen.apopen_duedate < DATE(_asOfDate) - 60))
        THEN (((apopen.apopen_amount - apopen.apopen_paid +
                COALESCE(SUM(apapply_target_paid * apopen.apopen_curr_rate / target.apopen_curr_rate),0))) / apopen.apopen_curr_rate *
                CASE WHEN (apopen.apopen_doctype IN ('D', 'V')) THEN 1 ELSE -1 END) ELSE 0 END AS ninety_val,

        --greater than 90 base:
        CASE WHEN((apopen.apopen_duedate > DATE(_asOfDate)-10000) AND (apopen.apopen_duedate < DATE(_asOfDate) - 90))
        THEN (((apopen.apopen_amount - apopen.apopen_paid +
                COALESCE(SUM(apapply_target_paid * apopen.apopen_curr_rate / target.apopen_curr_rate),0))) / apopen.apopen_curr_rate *
                CASE WHEN (apopen.apopen_doctype IN ('D', 'V')) THEN 1 ELSE -1 END) ELSE 0 END AS plus_val,

        --total amount base:
        CASE WHEN((apopen.apopen_duedate > DATE(_asOfDate)-10000))
        THEN (((apopen.apopen_amount - apopen.apopen_paid +
                COALESCE(SUM(apapply_target_paid * apopen.apopen_curr_rate / target.apopen_curr_rate),0))) / apopen.apopen_curr_rate *
                CASE WHEN (apopen.apopen_doctype IN ('D', 'V')) THEN 1 ELSE -1 END) ELSE 0 END AS total_val,

        --AP Open Amount base
        CASE WHEN apopen.apopen_doctype IN ('C', 'R') 
        THEN (apopen.apopen_amount / apopen.apopen_curr_rate * -1.0)
        ELSE (apopen.apopen_amount / apopen.apopen_curr_rate) END AS apopen_amount,
        
        apopen.apopen_docdate,
        apopen.apopen_duedate,
        apopen.apopen_ponumber,
        apopen.apopen_invcnumber,
        apopen.apopen_docnumber,
        apopen.apopen_doctype,
        vend_id,
        vend_name,
        vend_number,
        vend_vendtype_id,
        vendtype_code,
        terms_descrip,
        determineDiscountDate(terms_id, apopen.apopen_docdate) AS discdate,
        noNeg(apopen.apopen_discountable_amount *
                     CASE WHEN (CURRENT_DATE <= determineDiscountDate(terms_id, apopen.apopen_docdate)) THEN terms_discprcnt
                     ELSE 0.0 END) AS disc_val,
        terms_discdays AS discdays,
        (terms_discprcnt * 100.0) AS discprcnt

        FROM vendinfo, vendtype, apopen
          LEFT OUTER JOIN terms ON (apopen_terms_id=terms_id)
          LEFT OUTER JOIN apapply ON (((apopen_id=apapply_target_apopen_id)
                                    OR (apopen_id=apapply_source_apopen_id))
                                   AND (apapply_postdate >_asOfDate))
          LEFT OUTER JOIN apopen target ON (target.apopen_id=apapply_target_apopen_id)
        WHERE ( (apopen.apopen_vend_id = vend_id)
        AND (vend_vendtype_id=vendtype_id)
        AND (CASE WHEN (pUseDocDate) THEN apopen.apopen_docdate ELSE apopen.apopen_distdate END <= _asOfDate)
        AND (COALESCE(apopen.apopen_closedate,_asOfDate+1)>_asOfDate) )
        GROUP BY apopen.apopen_id,apopen.apopen_docdate,apopen.apopen_duedate,apopen.apopen_ponumber,
                 apopen.apopen_invcnumber,apopen.apopen_docnumber,apopen.apopen_doctype,apopen.apopen_paid,
                 apopen.apopen_curr_id,apopen.apopen_amount,vend_id,vend_name,vend_number,vend_vendtype_id,vendtype_code,terms_descrip,
                 apopen.apopen_curr_rate, terms_id, terms_discdays, terms_discprcnt, apopen.apopen_discountable_amount
        ORDER BY vend_number, apopen.apopen_duedate
  LOOP
        _row.apaging_docdate := _x.apopen_docdate;
        _row.apaging_duedate := _x.apopen_duedate;
        _row.apaging_ponumber := _x.apopen_ponumber;
        _row.apaging_invcnumber := _x.apopen_invcnumber;
        _row.apaging_docnumber := _x.apopen_docnumber;
        _row.apaging_doctype := _x.apopen_doctype;
        _row.apaging_vend_id := _x.vend_id;
        _row.apaging_vend_number := _x.vend_number;
        _row.apaging_vend_name := _x.vend_name;
        _row.apaging_vend_vendtype_id := _x.vend_vendtype_id;
        _row.apaging_vendtype_code := _x.vendtype_code;
        _row.apaging_terms_descrip := _x.terms_descrip;
        _row.apaging_apopen_amount := _x.apopen_amount;
        _row.apaging_cur_val := _x.cur_val;
        _row.apaging_thirty_val := _x.thirty_val;
        _row.apaging_sixty_val := _x.sixty_val;
        _row.apaging_ninety_val := _x.ninety_val;
        _row.apaging_plus_val := _x.plus_val;
        _row.apaging_total_val := _x.total_val;
        _row.apaging_discdate := _x.discdate;
        _row.apaging_disc_val := _x.disc_val;
        _row.apaging_discdays := _x.discdays;
        _row.apaging_discprcnt := _x.discprcnt;
        RETURN NEXT _row;
  END LOOP;
  RETURN;
END;
$_$;


ALTER FUNCTION public.apaging(date, boolean) OWNER TO admin;

--
-- Name: apapplied(integer, date); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION apapplied(integer, date) RETURNS numeric
    LANGUAGE plpgsql
    AS $_$
-- 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
  pApopenid ALIAS FOR $1;
  pDate ALIAS FOR $2;
  _amount NUMERIC;

BEGIN

  -- Return amount applied to an apopen in base currency as of apapply_postdate
  SELECT SUM(currtobase(apapply_curr_id,apapply_amount,apapply_postdate)) INTO _amount
  FROM apapply
  WHERE (((apapply_target_apopen_id = pApopenid) OR (apapply_source_apopen_id = pApopenid))
  AND (((apapply_journalnumber=0) AND (apapply_postdate <= pDate))
  OR EXISTS(SELECT * 
             FROM gltrans 
             WHERE ((gltrans_journalnumber=apapply_journalnumber)
             AND (gltrans_date <= pDate)))));

  IF (_amount IS NULL) THEN
    RETURN 0;
  ELSE
    RETURN _amount;
  END IF;

END;
$_$;


ALTER FUNCTION public.apapplied(integer, date) OWNER TO admin;

--
-- Name: apcheckpending(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION apcheckpending(integer) RETURNS numeric
    LANGUAGE plpgsql STABLE
    AS $_$
-- 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
  pApopenid	ALIAS FOR $1;
  _qty NUMERIC  := 0.0;

BEGIN

  SELECT SUM(checkitem_amount + checkitem_discount) INTO _qty
    FROM checkitem JOIN checkhead ON (checkitem_checkhead_id=checkhead_id)
   WHERE ((checkitem_apopen_id=pApopenid)
     AND (NOT checkhead_deleted)
     AND (NOT checkhead_replaced)
     AND (NOT checkhead_void)
     AND (NOT checkhead_posted));

  RETURN COALESCE(_qty, 0.0);

END;
$_$;


ALTER FUNCTION public.apcheckpending(integer) OWNER TO admin;

--
-- Name: apcurrgain(integer, integer, numeric, date); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION apcurrgain(integer, integer, numeric, date) RETURNS numeric
    LANGUAGE plpgsql
    AS $_$
-- 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
  pApopenId ALIAS FOR $1;
  pCurrId ALIAS FOR $2;
  pValue ALIAS FOR $3;
  pDate ALIAS FOR $4;
  _start DATE;
  _end DATE;
  _gain NUMERIC;
  _r RECORD;

BEGIN
  IF (pApopenId IS NULL OR pValue = 0) THEN
    RETURN 0;
  END IF;

  SELECT apopen_docdate, apopen_curr_rate
    INTO _r
  FROM apopen
  WHERE (apopen_id=pApopenId);

  IF (_r.apopen_docdate > pDate) THEN
    _gain := (currToBase(pCurrId, pValue, pDate) - (pValue / _r.apopen_curr_rate)) * -1;
  ELSE
    _gain := (pValue / _r.apopen_curr_rate) - currToBase(pCurrId, pValue, pDate);
  END IF;
  
  IF (_gain IS NULL) THEN
    RAISE EXCEPTION 'Error processing currency gain/loss.';
  END IF;

  RETURN _gain;
END;
$_$;


ALTER FUNCTION public.apcurrgain(integer, integer, numeric, date) OWNER TO admin;

--
-- Name: applyapcreditmemotobalance(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION applyapcreditmemotobalance(integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- 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
  pApopenid ALIAS FOR $1;
  _amount NUMERIC;
  _curr_id INTEGER;
  _curr_rate NUMERIC;
  _docdate DATE;
  _applyAmount NUMERIC;
  _r RECORD;
  _p RECORD;

BEGIN

--  Find the balance to apply
  SELECT (apopen_amount - apopen_paid - COALESCE(prepared,0.0) - COALESCE(selected,0.0) - COALESCE(SUM(currToCurr(apcreditapply_curr_id,
                                                  apopen_curr_id,
                                                  apcreditapply_amount,
                                                  apopen_docdate)), 0)),
          apopen_curr_id, apopen_curr_rate, apopen_docdate INTO _amount, _curr_id, _curr_rate, _docdate
  FROM apopen 
    LEFT OUTER JOIN apcreditapply ON (apcreditapply_source_apopen_id=apopen_id)
    LEFT OUTER JOIN (SELECT apopen_id AS selected_apopen_id,
                       SUM(currToCurr(apselect_curr_id, apopen_curr_id, apselect_amount + apselect_discount, apselect_date)) AS selected
                     FROM apselect JOIN apopen ON (apselect_apopen_id=apopen_id)
                     GROUP BY apopen_id) AS sub1 ON (apopen_id=selected_apopen_id)
    LEFT OUTER JOIN (SELECT apopen_id AS prepared_apopen_id,
                       SUM(checkitem_amount + checkitem_discount) AS prepared
                     FROM checkhead 
                       JOIN checkitem ON (checkitem_checkhead_id=checkhead_id)
                       JOIN apopen ON (checkitem_apopen_id=apopen_id)
                     WHERE ((NOT checkhead_posted)
                       AND  (NOT checkhead_void))
                     GROUP BY apopen_id) AS sub2 ON (prepared_apopen_id=apopen_id)
  WHERE (apopen_id=pApopenid)
  GROUP BY apopen_amount, apopen_paid, apopen_curr_id, apopen_curr_rate, apopen_docdate, prepared, selected;

  IF (_amount < 0) THEN
    RETURN -1;
  END IF;

--  Loop through the apopen items in order of due date
  FOR _r IN SELECT target.apopen_id AS apopenid,
                   currToCurr(target.apopen_curr_id,source.apopen_curr_id, 
                     target.apopen_amount - target.apopen_paid - COALESCE(prepared,0.0) - COALESCE(selected,0.0) - COALESCE(applied,0.0),
                     current_date) AS balance
           FROM apopen AS source, apopen AS target
             LEFT OUTER JOIN (SELECT apcreditapply_target_apopen_id AS applied_apopen_id,
                                     SUM(currToCurr(apcreditapply_curr_id, apopen_curr_id, apcreditapply_amount, apopen_docdate)) AS applied
                              FROM apcreditapply JOIN apopen ON (apopen_id=apcreditapply_source_apopen_id)
                              GROUP BY apcreditapply_target_apopen_id) AS sub3
                              ON (target.apopen_id=applied_apopen_id)
             LEFT OUTER JOIN (SELECT apopen_id AS selected_apopen_id,
                                SUM(currToCurr(apselect_curr_id, apopen_curr_id, apselect_amount + apselect_discount, apselect_date)) AS selected
                                    FROM apselect JOIN apopen ON (apselect_apopen_id=apopen_id)
                                GROUP BY apopen_id) AS sub1
                                ON (target.apopen_id=selected_apopen_id)
             LEFT OUTER JOIN (SELECT apopen_id AS prepared_apopen_id,
                                SUM(checkitem_amount + checkitem_discount) AS prepared
                              FROM checkhead 
                                JOIN checkitem ON (checkitem_checkhead_id=checkhead_id)
                                JOIN apopen ON (checkitem_apopen_id=apopen_id)
                              WHERE ((NOT checkhead_posted)
                               AND  (NOT checkhead_void))
                              GROUP BY apopen_id) AS sub2 ON (prepared_apopen_id=target.apopen_id)
            WHERE ( (source.apopen_vend_id=target.apopen_vend_id)
             AND (target.apopen_doctype IN ('V', 'D'))
             AND (target.apopen_open)
             AND (source.apopen_id=pApopenid) )
            ORDER BY target.apopen_duedate, (target.apopen_amount - target.apopen_paid) LOOP

--  Determine the amount to apply
    IF (_r.balance <= 0.0) THEN
      CONTINUE;
    ELSEIF (_r.balance > _amount) THEN
      _applyAmount := _amount;
    ELSE
      _applyAmount := _r.balance;
    END IF;

--  Does an apcreditapply record already exist?
    SELECT apcreditapply_id, 
              apcreditapply_amount * _curr_rate / 
                 currRate(apcreditapply_curr_id,_docdate) AS apcreditapply_amount
      INTO _p
    FROM apcreditapply
    WHERE ( (apcreditapply_target_apopen_id=_r.apopenid)
     AND (apcreditapply_source_apopen_id=pApopenid) );

    IF (FOUND) THEN
--  The following is depreciated, just skip the record
--  Recalculate the amount to apply
--      IF ((_r.balance - _p.apcreditapply_amount) > _amount) THEN
--        _applyAmount := _amount;
--      ELSE
--        _applyAmount := (_r.balance - _p.apcreditapply_amount);
--      END IF;

--  Update the apcreditapply with the new amount to apply
--      UPDATE apcreditapply
--      SET apcreditapply_amount = (apcreditapply_amount + 
--          _applyAmount *  currRate(apcreditapply_curr_id,_docdate) / _curr_rate)
--      WHERE (apcreditapply_id=_p.apcreditapply_id);

      CONTINUE;
    ELSE
--  Create a new apcreditapply record
      INSERT INTO apcreditapply
      ( apcreditapply_source_apopen_id, apcreditapply_target_apopen_id,
        apcreditapply_amount, apcreditapply_curr_id )
      VALUES
      ( pApopenid, _r.apopenid, _applyAmount, _curr_id );
    END IF;

    _amount := (_amount - _applyAmount);
    IF (_amount = 0) THEN
      EXIT;
    END IF;

  END LOOP;

  RETURN 1;

END;
$_$;


ALTER FUNCTION public.applyapcreditmemotobalance(integer) OWNER TO admin;

--
-- Name: applyapcredits(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION applyapcredits(integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- 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
  pVendId   ALIAS FOR $1;
  _result   INTEGER;
  _apopenid INTEGER;
  _r        RECORD;

BEGIN

  -- Fetch credit memo(s) for the vendor
  FOR _r IN SELECT apopen_id, apopen_duedate
            FROM apopen JOIN vendinfo ON (apopen_vend_id = vend_id)
            WHERE ((apopen_doctype = 'C')
               AND (apopen_status = 'O')
               AND (vend_id = pVendId))
            ORDER BY apopen_duedate
  LOOP
    -- Apply credit memo(s) according to due date
    SELECT applyapcreditmemotobalance(_r.apopen_id) INTO _result;

    -- Post the credit memo if applied
    IF (_result = 1) THEN
      SELECT postapcreditmemoapplication(_r.apopen_id) INTO _apopenid;
      IF (_apopenid < 0) THEN
        RETURN -1;
      END IF;
    ELSE
      RETURN -1;
    END IF;

  END LOOP;

RETURN 1;

END;
$_$;


ALTER FUNCTION public.applyapcredits(integer) OWNER TO admin;

--
-- Name: applyarcreditmemotobalance(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION applyarcreditmemotobalance(integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- 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
  pAropenid ALIAS FOR $1;

BEGIN

  RETURN applyARCreditMemoToBalance(pAropenid, NULL);

END;
$_$;


ALTER FUNCTION public.applyarcreditmemotobalance(integer) OWNER TO admin;

--
-- Name: applyarcreditmemotobalance(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION applyarcreditmemotobalance(integer, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- 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
  pSourceAropenid ALIAS FOR $1;
  pTargetAropenid ALIAS FOR $2;
  _amount NUMERIC;
  _amountcurrid INTEGER;
  _applyAmount NUMERIC;
  _applycurrid  INTEGER;
  _curr_rate NUMERIC;
  _r RECORD;
  _p RECORD;

BEGIN

--  Find the balance to apply
  SELECT (aropen_amount - COALESCE(SUM(currToCurr(arcreditapply_curr_id,
                                                  aropen_curr_id,
                                                  arcreditapply_amount,
                                                  aropen_docdate)), 0) - aropen_paid - COALESCE(prepared,0.0) - COALESCE(cashapplied,0.0)),
         aropen_curr_id, aropen_curr_rate INTO _amount, _amountcurrid, _curr_rate
  FROM aropen LEFT OUTER JOIN arcreditapply ON (arcreditapply_source_aropen_id=aropen_id)
	      LEFT OUTER JOIN (SELECT aropen_id AS prepared_aropen_id,
                                SUM(checkitem_amount + checkitem_discount) AS prepared
                               FROM checkhead JOIN checkitem ON (checkitem_checkhead_id=checkhead_id)
                                              JOIN aropen ON (checkitem_aropen_id=aropen_id)
                               WHERE ((NOT checkhead_posted)
                                AND  (NOT checkhead_void))
                               GROUP BY aropen_id) AS sub1
                      ON (prepared_aropen_id=aropen_id)
             LEFT OUTER JOIN (SELECT aropen_id AS cash_aropen_id,
                                     SUM(cashrcptitem_amount + cashrcptitem_discount) * -1.0 AS cashapplied
                                FROM cashrcpt JOIN cashrcptitem ON (cashrcptitem_cashrcpt_id=cashrcpt_id)
                                              JOIN aropen ON (cashrcptitem_aropen_id=aropen_id)
                               WHERE (NOT cashrcpt_posted) AND (NOT cashrcpt_void)
                               GROUP BY aropen_id ) AS sub2
                      ON (cash_aropen_id=aropen_id)
  WHERE (aropen_id=pSourceAropenid)
  GROUP BY aropen_amount, aropen_paid, aropen_curr_id, aropen_curr_rate, prepared, cashapplied;

  IF (_amount < 0) THEN
    RETURN -1;
  END IF;

--  Loop through the aropen items in order of due date
  FOR _r IN SELECT target.aropen_id AS aropenid,
                   currToCurr(target.aropen_curr_id,source.aropen_curr_id,
                              (target.aropen_amount - target.aropen_paid - calcpendingarapplications(target.aropen_id)),
                              current_date) AS balance,
                   target.aropen_curr_id AS curr_id,
                   target.aropen_docdate AS docdate
            FROM aropen AS target, aropen AS source
            WHERE ( (source.aropen_cust_id=target.aropen_cust_id)
             AND (target.aropen_doctype IN ('D', 'I'))
             AND (target.aropen_open)
             AND (source.aropen_id=pSourceAropenid)
             AND ((pTargetAropenid IS NULL) OR (target.aropen_id=pTargetAropenid)) )
            ORDER BY target.aropen_duedate, target.aropen_docnumber LOOP

--  Determine the amount to apply
    IF (_r.balance > _amount) THEN
      _applyAmount := _amount;
    ELSE
      _applyAmount := _r.balance;
    END IF;
    _applycurrid := _amountcurrid;

--  Does an arcreditapply record already exist?
    SELECT arcreditapply_id,
           arcreditapply_amount,
           arcreditapply_amount * _curr_rate / 
                 currRate(arcreditapply_curr_id,_r.docdate) AS
                      arcreditapply_amount_applycurr INTO _p
    FROM arcreditapply
    WHERE ( (arcreditapply_target_aropen_id=_r.aropenid)
     AND (arcreditapply_source_aropen_id=pSourceAropenid) );

    IF (FOUND) THEN
--  Offset the amount to apply by the amount already applied
      _applyAmount := (_applyAmount - _p.arcreditapply_amount_applycurr);
      IF (_applyAmount < 0) THEN
        _applyAmount := 0;
      END IF;

--  Update the arcreditapply with the new amount to apply
      UPDATE arcreditapply
      SET arcreditapply_amount = (arcreditapply_amount + 
          _applyAmount *  currRate(arcreditapply_curr_id,_r.docdate) / _curr_rate)
      WHERE (arcreditapply_id=_p.arcreditapply_id);

    ELSE
--  Create a new arcreditapply record
      INSERT INTO arcreditapply
      ( arcreditapply_source_aropen_id, arcreditapply_target_aropen_id,
        arcreditapply_amount, arcreditapply_curr_id )
      VALUES
      ( pSourceAropenid, _r.aropenid, _applyAmount, _applycurrid );
    END IF;

    _amount := _amount - currToCurr(_applycurrid, _amountcurrid, _applyAmount, _r.docdate);
    IF (_amount = 0) THEN
      EXIT;
    END IF;

  END LOOP;

  RETURN 1;

END;
$_$;


ALTER FUNCTION public.applyarcreditmemotobalance(integer, integer) OWNER TO admin;

--
