--
--


--
-- Name: api; Type: SCHEMA; Schema: -; Owner: admin
--

CREATE SCHEMA api;


ALTER SCHEMA api OWNER TO admin;

--
-- Name: fixcountry; Type: SCHEMA; Schema: -; Owner: admin
--

CREATE SCHEMA fixcountry;


ALTER SCHEMA fixcountry OWNER TO admin;

--
-- Name: SCHEMA fixcountry; Type: COMMENT; Schema: -; Owner: admin
--

COMMENT ON SCHEMA fixcountry IS 'Schema to hold contents of fixcountry';


--
-- Name: te; Type: SCHEMA; Schema: -; Owner: admin
--

CREATE SCHEMA te;


ALTER SCHEMA te OWNER TO admin;

--
-- Name: SCHEMA te; Type: COMMENT; Schema: -; Owner: admin
--

COMMENT ON SCHEMA te IS 'Time and Expense system add-on package for xTuple ERP.

This file is part of the xtte package for xTuple ERP: PostBooks Edition,
a free and open source Enterprise Resource Planning software suite,
Copyright (c) 1999-2015 by OpenMFG LLC, d/b/a xTuple.
It is licensed to you under the Common Public Attribution License
version 1.0, the full text of which (including xTuple-specific Exhibits)
is available at www.xtuple.com/CPAL.  By using this software, you agree
to be bound by its terms.';


--
-- Name: xt; Type: SCHEMA; Schema: -; Owner: admin
--

CREATE SCHEMA xt;


ALTER SCHEMA xt OWNER TO admin;

--
-- Name: xtdesktop; Type: SCHEMA; Schema: -; Owner: admin
--

CREATE SCHEMA xtdesktop;


ALTER SCHEMA xtdesktop OWNER TO admin;

--
-- Name: SCHEMA xtdesktop; Type: COMMENT; Schema: -; Owner: admin
--

COMMENT ON SCHEMA xtdesktop IS 'Schema to hold contents of xtdesktop';


--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';


--
-- Name: plv8; Type: EXTENSION; Schema: -; Owner: 
--

CREATE EXTENSION IF NOT EXISTS plv8 WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plv8; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION plv8 IS 'PL/JavaScript (v8) trusted procedural language';



--
-- Name: _docinfo; Type: TYPE; Schema: public; Owner: admin
--

CREATE TYPE _docinfo AS (
	id integer,
	target_number text,
	target_type text,
	target_id integer,
	source_type text,
	source_id integer,
	name text,
	description text,
	purpose text
);


ALTER TYPE _docinfo OWNER TO admin;

--
-- Name: achline; Type: TYPE; Schema: public; Owner: admin
--

CREATE TYPE achline AS (
	achline_checkhead_id integer,
	achline_batch text,
	achline_type text,
	achline_value text
);


ALTER TYPE achline OWNER TO admin;

--
-- Name: apaging; Type: TYPE; Schema: public; Owner: admin
--

CREATE TYPE apaging AS (
	apaging_docdate text,
	apaging_duedate date,
	apaging_ponumber text,
	apaging_reference text,
	apaging_invcnumber text,
	apaging_docnumber text,
	apaging_doctype text,
	apaging_vend_id integer,
	apaging_vend_number text,
	apaging_vend_name text,
	apaging_vend_vendtype_id integer,
	apaging_vendtype_code text,
	apaging_terms_descrip text,
	apaging_apopen_amount numeric,
	apaging_cur_val numeric,
	apaging_thirty_val numeric,
	apaging_sixty_val numeric,
	apaging_ninety_val numeric,
	apaging_plus_val numeric,
	apaging_total_val numeric,
	apaging_discdate date,
	apaging_disc_val numeric,
	apaging_discdays numeric,
	apaging_discprcnt numeric
);


ALTER TYPE apaging OWNER TO admin;

--
-- Name: araging; Type: TYPE; Schema: public; Owner: admin
--

CREATE TYPE araging AS (
	araging_docdate date,
	araging_duedate date,
	araging_ponumber text,
	araging_docnumber text,
	araging_doctype text,
	araging_cust_id integer,
	araging_cust_number text,
	araging_cust_name text,
	araging_cust_custtype_id integer,
	araging_custtype_code text,
	araging_terms_descrip text,
	araging_aropen_amount numeric,
	araging_cur_val numeric,
	araging_thirty_val numeric,
	araging_sixty_val numeric,
	araging_ninety_val numeric,
	araging_plus_val numeric,
	araging_total_val numeric
);


ALTER TYPE araging OWNER TO admin;

--
-- Name: bomdata; Type: TYPE; Schema: public; Owner: admin
--

CREATE TYPE bomdata AS (
	bomdata_bomwork_id integer,
	bomdata_bomwork_parent_id integer,
	bomdata_bomwork_level integer,
	bomdata_bomwork_seqnumber integer,
	bomdata_bomitem_id integer,
	bomdata_item_id integer,
	bomdata_item_number text,
	bomdata_uom_name text,
	bomdata_item_descrip1 text,
	bomdata_item_descrip2 text,
	bomdata_itemdescription text,
	bomdata_batchsize numeric,
	bomdata_qtyfxd numeric,
	bomdata_qtyper numeric,
	bomdata_qtyreq numeric,
	bomdata_scrap numeric,
	bomdata_createchild boolean,
	bomdata_issuemethod text,
	bomdata_effective date,
	bomdata_expires date,
	bomdata_expired boolean,
	bomdata_future boolean,
	bomdata_actunitcost numeric,
	bomdata_stdunitcost numeric,
	bomdata_actextendedcost numeric,
	bomdata_stdextendedcost numeric,
	bomdata_ecn text,
	bomdata_char_id integer,
	bomdata_value text,
	bomdata_notes text,
	bomdata_ref text
);


ALTER TYPE bomdata OWNER TO admin;

--
-- Name: checkdata; Type: TYPE; Schema: public; Owner: admin
--

CREATE TYPE checkdata AS (
	checkdata_page integer,
	checkdata_checknumber text,
	checkdata_checkwords text,
	checkdata_checkdate text,
	checkdata_checkamount text,
	checkdata_checkcurrsymbol text,
	checkdata_checkcurrabbr text,
	checkdata_checkcurrname text,
	checkdata_checkpayto text,
	checkdata_checkaddress text,
	checkdata_checkmemo text,
	checkdata_docnumber text,
	checkdata_docreference text,
	checkdata_docdate text,
	checkdata_docamount text,
	checkdata_docdiscount text,
	checkdata_docnetamount text
);


ALTER TYPE checkdata OWNER TO admin;

--
-- Name: cntctdup; Type: TYPE; Schema: public; Owner: admin
--

CREATE TYPE cntctdup AS (
	cntct_id integer,
	cntct_crmacct_id integer,
	cntct_addr_id integer,
	cntct_first_name text,
	cntct_last_name text,
	cntct_honorific text,
	cntct_initials text,
	cntct_active boolean,
	cntct_phone text,
	cntct_phone2 text,
	cntct_fax text,
	cntct_email text,
	cntct_webaddr text,
	cntct_notes text,
	cntct_title text,
	cntct_number text,
	cntct_middle text,
	cntct_suffix text,
	cntct_owner_username text,
	cntct_name text,
	crmacct_number text,
	crmacct_name text,
	addr_id integer,
	addr_active boolean,
	addr_line1 text,
	addr_line2 text,
	addr_line3 text,
	addr_city text,
	addr_state text,
	addr_postalcode text,
	addr_country text,
	addr_notes text,
	addr_number text,
	cntctdup_level integer
);


ALTER TYPE cntctdup OWNER TO admin;

--
-- Name: flcoldata; Type: TYPE; Schema: public; Owner: admin
--

CREATE TYPE flcoldata AS (
	flcoldata_column integer,
	flcoldata_start date,
	flcoldata_end date
);


ALTER TYPE flcoldata OWNER TO admin;

--
-- Name: flstmthead; Type: TYPE; Schema: public; Owner: admin
--

CREATE TYPE flstmthead AS (
	flstmthead_flhead_id integer,
	flstmthead_flcol_id integer,
	flstmthead_period_id integer,
	flstmthead_username text,
	flstmthead_typedescrip1 text,
	flstmthead_typedescrip2 text,
	flstmthead_flhead_name text,
	flstmthead_flcol_name text,
	flstmthead_month text,
	flstmthead_qtr text,
	flstmthead_year text,
	flstmthead_prmonth text,
	flstmthead_prqtr text,
	flstmthead_pryear text
);


ALTER TYPE flstmthead OWNER TO admin;

--
-- Name: flstmtitem; Type: TYPE; Schema: public; Owner: admin
--

CREATE TYPE flstmtitem AS (
	flstmtitem_flhead_id integer,
	flstmtitem_period_id integer,
	flstmtitem_username text,
	flstmtitem_order integer,
	flstmtitem_level integer,
	flstmtitem_subgrp integer,
	flstmtitem_type text,
	flstmtitem_type_id integer,
	flstmtitem_parent_id integer,
	flstmtitem_accnt_id integer,
	flstmtitem_name text,
	flstmtitem_month numeric,
	flstmtitem_monthdb numeric,
	flstmtitem_monthcr numeric,
	flstmtitem_monthprcnt numeric,
	flstmtitem_monthbudget numeric,
	flstmtitem_monthbudgetprcnt numeric,
	flstmtitem_monthbudgetdiff numeric,
	flstmtitem_monthbudgetdiffprcnt numeric,
	flstmtitem_qtr numeric,
	flstmtitem_qtrdb numeric,
	flstmtitem_qtrcr numeric,
	flstmtitem_qtrprcnt numeric,
	flstmtitem_qtrbudget numeric,
	flstmtitem_qtrbudgetprcnt numeric,
	flstmtitem_qtrbudgetdiff numeric,
	flstmtitem_qtrbudgetdiffprcnt numeric,
	flstmtitem_year numeric,
	flstmtitem_yeardb numeric,
	flstmtitem_yearcr numeric,
	flstmtitem_yearprcnt numeric,
	flstmtitem_yearbudget numeric,
	flstmtitem_yearbudgetprcnt numeric,
	flstmtitem_yearbudgetdiff numeric,
	flstmtitem_yearbudgetdiffprcnt numeric,
	flstmtitem_prmonth numeric,
	flstmtitem_prmonthprcnt numeric,
	flstmtitem_prmonthdiff numeric,
	flstmtitem_prmonthdiffprcnt numeric,
	flstmtitem_prqtr numeric,
	flstmtitem_prqtrprcnt numeric,
	flstmtitem_prqtrdiff numeric,
	flstmtitem_prqtrdiffprcnt numeric,
	flstmtitem_pryear numeric,
	flstmtitem_pryearprcnt numeric,
	flstmtitem_pryeardiff numeric,
	flstmtitem_pryeardiffprcnt numeric
);


ALTER TYPE flstmtitem OWNER TO admin;

--
-- Name: fltrendhead; Type: TYPE; Schema: public; Owner: admin
--

CREATE TYPE fltrendhead AS (
	fltrendhead_flhead_id integer,
	fltrendhead_username text,
	fltrendhead_typedescrip text,
	fltrendhead_flhead_name text,
	fltrendhead_fld1 text,
	fltrendhead_fld2 text,
	fltrendhead_fld3 text,
	fltrendhead_fld4 text,
	fltrendhead_fld5 text,
	fltrendhead_fld6 text,
	fltrendhead_fld7 text,
	fltrendhead_fld8 text,
	fltrendhead_fld9 text,
	fltrendhead_fld10 text,
	fltrendhead_fld11 text,
	fltrendhead_fld12 text,
	fltrendhead_grndttl text
);


ALTER TYPE fltrendhead OWNER TO admin;

--
-- Name: fltrenditem; Type: TYPE; Schema: public; Owner: admin
--

CREATE TYPE fltrenditem AS (
	fltrenditem_flhead_id integer,
	fltrenditem_username text,
	fltrenditem_order integer,
	fltrenditem_level integer,
	fltrenditem_subgrp integer,
	fltrenditem_type text,
	fltrenditem_type_id integer,
	fltrenditem_parent_id integer,
	fltrenditem_accnt_id integer,
	fltrenditem_name text,
	fltrenditem_fld1 text,
	fltrenditem_fld2 text,
	fltrenditem_fld3 text,
	fltrenditem_fld4 text,
	fltrenditem_fld5 text,
	fltrenditem_fld6 text,
	fltrenditem_fld7 text,
	fltrenditem_fld8 text,
	fltrenditem_fld9 text,
	fltrenditem_fld10 text,
	fltrenditem_fld11 text,
	fltrenditem_fld12 text,
	fltrenditem_grndttl text
);


ALTER TYPE fltrenditem OWNER TO admin;

--
-- Name: freightdata; Type: TYPE; Schema: public; Owner: admin
--

CREATE TYPE freightdata AS (
	freightdata_schedule text,
	freightdata_from text,
	freightdata_to text,
	freightdata_shipvia text,
	freightdata_freightclass text,
	freightdata_weight numeric,
	freightdata_uom text,
	freightdata_price numeric,
	freightdata_type text,
	freightdata_total numeric,
	freightdata_currency text
);


ALTER TYPE freightdata OWNER TO admin;

--
-- Name: itemprice; Type: TYPE; Schema: public; Owner: admin
--

CREATE TYPE itemprice AS (
	itemprice_price numeric,
	itemprice_type character(1)
);


ALTER TYPE itemprice OWNER TO admin;

--
-- Name: orderitemtype; Type: TYPE; Schema: public; Owner: admin
--

CREATE TYPE orderitemtype AS (
	orderitem_id integer,
	orderitem_orderhead_type text,
	orderitem_orderhead_id integer,
	orderitem_linenumber integer,
	orderitem_status text,
	orderitem_itemsite_id integer,
	orderitem_scheddate date,
	orderitem_qty_ordered numeric,
	orderitem_qty_shipped numeric,
	orderitem_qty_received numeric,
	orderitem_qty_uom_id integer,
	orderitem_qty_invuomratio numeric,
	orderitem_unitcost numeric,
	orderitem_unitcost_curr_id integer,
	orderitem_freight numeric,
	orderitem_freight_received numeric,
	orderitem_freight_curr_id integer
);


ALTER TYPE orderitemtype OWNER TO admin;

--
-- Name: ordhead; Type: TYPE; Schema: public; Owner: admin
--

CREATE TYPE ordhead AS (
	orderhead_id integer,
	orderhead_type text,
	orderhead_number text,
	orderhead_status text,
	orderhead_orderdate date,
	orderhead_linecount integer,
	orderhead_from_id integer,
	orderhead_from text,
	orderhead_to_id integer,
	orderhead_to text,
	orderhead_curr_id integer,
	orderhead_agent_username text,
	orderhead_shipvia text
);


ALTER TYPE ordhead OWNER TO admin;

--
-- Name: orditem; Type: TYPE; Schema: public; Owner: admin
--

CREATE TYPE orditem AS (
	orderitem_id integer,
	orderitem_orderhead_type text,
	orderitem_orderhead_id integer,
	orderitem_linenumber integer,
	orderitem_status text,
	orderitem_itemsite_id integer,
	orderitem_scheddate date,
	orderitem_qty_ordered numeric,
	orderitem_qty_shipped numeric,
	orderitem_qty_received numeric,
	orderitem_qty_uom_id integer,
	orderitem_qty_invuomratio numeric,
	orderitem_unitcost numeric,
	orderitem_unitcost_curr_id integer,
	orderitem_freight numeric,
	orderitem_freight_received numeric,
	orderitem_freight_curr_id integer
);


ALTER TYPE orditem OWNER TO admin;

--
-- Name: reordlvl; Type: TYPE; Schema: public; Owner: admin
--

CREATE TYPE reordlvl AS (
	reordlvl_itemsite_id integer,
	reordlvl_item_id integer,
	reordlvl_warehous_code text,
	reordlvl_item_number text,
	reordlvl_item_descrip text,
	reordlvl_leadtime integer,
	reordlvl_daysofstock integer,
	reordlvl_curr_level numeric,
	reordlvl_total_days numeric,
	reordlvl_total_usage numeric,
	reordlvl_calc_level integer
);


ALTER TYPE reordlvl OWNER TO admin;

--
-- Name: seqiss; Type: TYPE; Schema: public; Owner: admin
--

CREATE TYPE seqiss AS (
	seqiss_number integer,
	seqiss_time timestamp with time zone
);


ALTER TYPE seqiss OWNER TO admin;

--
-- Name: subtax; Type: TYPE; Schema: public; Owner: admin
--

CREATE TYPE subtax AS (
	subtax_taxcode_id integer,
	subtax_taxcode_code text,
	subtax_taxcode_descrip text,
	subtax_taxcode_level integer
);


ALTER TYPE subtax OWNER TO admin;

--
-- Name: taxassign; Type: TYPE; Schema: public; Owner: admin
--

CREATE TYPE taxassign AS (
	taxassign_taxzone_id integer,
	taxassign_taxtype_id integer,
	taxassign_level integer,
	taxassign_zone_code text,
	taxassign_type_descrip text,
	taxassign_taxclass_code text,
	taxassign_taxclass_sequence integer
);


ALTER TYPE taxassign OWNER TO admin;

--
-- Name: taxdetail; Type: TYPE; Schema: public; Owner: admin
--

CREATE TYPE taxdetail AS (
	taxdetail_tax_id integer,
	taxdetail_tax_code text,
	taxdetail_tax_descrip text,
	taxdetail_tax_basis_tax_id integer,
	taxdetail_taxrate_percent numeric(10,6),
	taxdetail_taxrate_amount numeric(16,2),
	taxdetail_level integer,
	taxdetail_taxclass_id integer,
	taxdetail_taxclass_code text,
	taxdetail_taxclass_sequence integer,
	taxdetail_tax numeric(16,6),
	taxdetail_curr_id integer,
	taxdetail_curr_abbr text
);


ALTER TYPE taxdetail OWNER TO admin;

--
-- Name: wodata; Type: TYPE; Schema: public; Owner: admin
--

CREATE TYPE wodata AS (
	wodata_id integer,
	wodata_id_type integer,
	wodata_number integer,
	wodata_subnumber integer,
	wodata_itemnumber text,
	wodata_descrip text,
	wodata_status character(1),
	wodata_startdate date,
	wodata_duedate date,
	wodata_adhoc boolean,
	wodata_itemsite_id integer,
	wodata_listprice numeric,
	wodata_custprice numeric,
	wodata_qoh numeric,
	wodata_short numeric,
	wodata_qtyper numeric,
	wodata_qtyiss numeric,
	wodata_qtyrcv numeric,
	wodata_qtyordreq numeric,
	wodata_qtyuom text,
	wodata_scrap numeric,
	wodata_setup numeric,
	wodata_run numeric,
	wodata_notes text,
	wodata_ref text,
	wodata_level integer
);


ALTER TYPE wodata OWNER TO admin;

--
-- Name: woinvav; Type: TYPE; Schema: public; Owner: admin
--

CREATE TYPE woinvav AS (
	woinvav_itemsite_id integer,
	woinvav_womatl_id integer,
	woinvav_type character(1),
	woinvav_item_wo_number text,
	woinvav_descrip text,
	woinvav_uomname text,
	woinvav_qoh numeric,
	woinvav_balance numeric,
	woinvav_allocated numeric,
	woinvav_ordered numeric,
	woinvav_woavail numeric,
	woinvav_totalavail numeric,
	woinvav_reorderlevel numeric,
	woinvav_level integer
);


ALTER TYPE woinvav OWNER TO admin;


--
-- Name: usersonline; Type: TYPE; Schema: xtdesktop; Owner: admin
--

CREATE TYPE usersonline AS (
	usr_id integer,
	usr_username text,
	usr_propername text,
	usr_email text,
	client_start timestamp with time zone,
	query_start timestamp with time zone,
	cnt_internal bigint,
	cnt_external bigint,
	client_addr inet
);


ALTER TYPE usersonline OWNER TO admin;


--
-- Name: getcustnumberfrominfo(text, text, text, text, text, boolean); Type: FUNCTION; Schema: api; Owner: admin
--

CREATE FUNCTION getcustnumberfrominfo(text, text, text, text, text, boolean) RETURNS text
    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
  _email	TEXT	:= COALESCE(UPPER($1), '');
  _company	TEXT	:= COALESCE(UPPER($2), '');
  _first	TEXT	:= COALESCE(UPPER($3), '');
  _last		TEXT	:= COALESCE(UPPER($4), '');
  _fullname	TEXT	:= COALESCE(UPPER($5), TRIM(_first || ' ' || _last));
  _generate	BOOLEAN	:= COALESCE($6, FALSE);
  _counter	INTEGER;
  _custcount	INTEGER	:= 0;
  _custnumber	TEXT;
  _candidate	TEXT	:= '';
  _loopmax	INTEGER := 0;
  _minlength	INTEGER := 5;
  _maxlength	INTEGER := 8;
  _numformat	TEXT	:= '';
  _testme	TEXT;
BEGIN
  IF (_email != '') THEN
    SELECT count(*), cust_number INTO _custcount, _custnumber
    FROM custinfo LEFT OUTER JOIN cntct ON (cust_cntct_id=cntct_id)
    WHERE (UPPER(cntct_email)=_email)
    GROUP BY cust_number;
    IF (NOT FOUND) THEN
      _custcount := 0;
    ELSIF(_custcount = 1) THEN
      RETURN _custnumber;
    END IF;
  END IF;

  IF (_company != '') THEN
    SELECT count(*), cust_number INTO _custcount, _custnumber
    FROM custinfo
    WHERE (UPPER(cust_name)=_company)
    GROUP BY cust_number;
    IF (NOT FOUND) THEN
      _custcount := 0;
    ELSIF(_custcount = 1) THEN
      RETURN _custnumber;
    END IF;
  END IF;

  IF (_fullname = '' AND (_first != '' OR _last != '')) THEN
    _fullname := TRIM(_first || ' ' || _last);
  END IF;

  IF (_custcount <= 0 AND _fullname != '') THEN
    SELECT count(*), cust_number INTO _custcount, _custnumber
    FROM custinfo
    WHERE (UPPER(cust_name)=_fullname)
    GROUP BY cust_number;
    IF (NOT FOUND) THEN
      _custcount := 0;
    ELSIF(_custcount = 1) THEN
      RETURN _custnumber;
    END IF;
  END IF;

  IF (_custcount > 1) THEN
    RAISE EXCEPTION 'Found % possible Customers for % and % and %',
		    _custcount, _email, _company, _fullname;
  END IF;

  IF (_custcount <= 0 AND _generate) THEN
    IF (_maxlength < _minlength) THEN
      RAISE EXCEPTION 'Fix getCustNumberFromInfo: max length < min length';
    END IF;

    IF (_company != '') THEN
      _candidate := _company;
    ELSIF (_email != '') THEN
      _candidate := SUBSTRING(_email FOR POSITION('@' IN _email) - 1);
    ELSIF (_last != '') THEN
      _candidate := _last;
      IF (_first != '') THEN
	_candidate := _candidate || _first;
      END IF;
    ELSIF (_fullname != '' AND (POSITION(' ' IN _fullname) > 0)) THEN
      _candidate := SUBSTRING(_fullname FROM POSITION(' ' IN _fullname) + 1) ||
		    SUBSTRING(_fullname FOR  POSITION(' ' IN _fullname) - 1);
    END IF;
    WHILE (POSITION(' ' IN _candidate) > 0) LOOP
      _candidate := SUBSTRING(_candidate FOR  POSITION(' ' IN _candidate) - 1) ||
		    SUBSTRING(_candidate FROM POSITION(' ' IN _candidate) + 1);
    END LOOP;
    FOR _counter IN _minlength.._maxlength LOOP
      _testme := SUBSTRING(_candidate FOR _counter);
      IF (NOT EXISTS(SELECT cust_number
		     FROM custinfo
		     WHERE (cust_number=_testme))) THEN
	_custnumber := _testme;
	EXIT;
      END IF;
    END LOOP;
    IF (_custnumber IS NULL OR _custnumber = '') THEN
      IF (LENGTH(_candidate) < _minlength) THEN
	_minlength := LENGTH(_candidate);
      END IF;
      FOR _counter IN _minlength.._maxlength LOOP
	_loopmax := _loopmax * 10 + 9;
	_numformat := _numformat || '0';
      END LOOP;
      FOR _counter IN 1.._loopmax LOOP
	_testme := SUBSTRING(_candidate FOR _minlength) ||
		   TRIM(TO_CHAR(_counter, _numformat));
	IF (NOT EXISTS(SELECT cust_number
		       FROM custinfo
		       WHERE (cust_number=_testme))) THEN
	  _custnumber := _testme;
	  EXIT;
	END IF;
      END LOOP;
    END IF;
    IF (_custnumber IS NULL OR _custnumber = '') THEN
      RAISE EXCEPTION 'Could not generate a new Customer Number';
    END IF;
  END IF;

  IF (_custnumber IS NULL OR _custnumber = '') THEN
    RAISE EXCEPTION 'Could not find Customer Number for % and % and %',
		    _email, _company, _fullname;
  END IF;

  RETURN _custnumber;
END;
$_$;


ALTER FUNCTION api.getcustnumberfrominfo(text, text, text, text, text, boolean) OWNER TO admin;


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

CREATE FUNCTION formatlocationname(integer) RETURNS text
    LANGUAGE plpgsql IMMUTABLE
    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
  pLocationid ALIAS FOR $1;
  _name TEXT;
  _r RECORD;

BEGIN

  SELECT location_aisle, location_rack,
         location_bin, location_name INTO _r
  FROM location
  WHERE (location_id=pLocationid);
  IF (FOUND) THEN
    IF (_r.location_aisle IS NOT NULL) THEN
      _name := _r.location_aisle;
    ELSE
      _name := '';
    END IF;

    IF (_r.location_rack IS NOT NULL) THEN
      _name := (_name || _r.location_rack);
    END IF;

    IF (_r.location_bin IS NOT NULL) THEN
      _name := (_name || _r.location_bin);
    END IF;

    IF (_r.location_name IS NOT NULL) THEN
      _name := (_name || _r.location_name);
    END IF;

    RETURN _name;
  ELSE
    RETURN 'N/A';
  END IF;

END;
$_$;


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



--
-- Name: cntslip; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE cntslip (
    cntslip_id integer DEFAULT nextval(('"cntslip_cntslip_id_seq"'::text)::regclass) NOT NULL,
    cntslip_cnttag_id integer,
    cntslip_entered timestamp with time zone,
    cntslip_posted boolean,
    cntslip_number text,
    cntslip_qty numeric(18,6),
    cntslip_comments text,
    cntslip_location_id integer,
    cntslip_lotserial text,
    cntslip_lotserial_expiration date,
    cntslip_lotserial_warrpurc date,
    cntslip_username text
);


ALTER TABLE cntslip OWNER TO admin;

--
-- Name: TABLE cntslip; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE cntslip IS 'Count Slip information';


--
-- Name: invcnt; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE invcnt (
    invcnt_id integer DEFAULT nextval(('invcnt_invcnt_id_seq'::text)::regclass) NOT NULL,
    invcnt_itemsite_id integer,
    invcnt_tagdate timestamp with time zone,
    invcnt_cntdate timestamp with time zone,
    invcnt_qoh_before numeric(18,6),
    invcnt_qoh_after numeric(18,6),
    invcnt_matcost numeric(16,6),
    invcnt_posted boolean,
    invcnt_postdate timestamp with time zone,
    invcnt_comments text,
    invcnt_priority boolean,
    invcnt_tagnumber text,
    invcnt_invhist_id integer,
    invcnt_location_id integer,
    invcnt_cnt_username text,
    invcnt_post_username text,
    invcnt_tag_username text
);


ALTER TABLE invcnt OWNER TO admin;

--
-- Name: TABLE invcnt; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE invcnt IS 'Count Tag information';


--
-- Name: item; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE item (
    item_id integer DEFAULT nextval(('item_item_id_seq'::text)::regclass) NOT NULL,
    item_number text NOT NULL,
    item_descrip1 text NOT NULL,
    item_descrip2 text NOT NULL,
    item_classcode_id integer NOT NULL,
    item_picklist boolean DEFAULT true NOT NULL,
    item_comments text,
    item_sold boolean NOT NULL,
    item_fractional boolean NOT NULL,
    item_active boolean NOT NULL,
    item_type character(1) DEFAULT 'R'::bpchar NOT NULL,
    item_prodweight numeric(16,2) DEFAULT 0 NOT NULL,
    item_packweight numeric(16,2) DEFAULT 0 NOT NULL,
    item_prodcat_id integer NOT NULL,
    item_exclusive boolean DEFAULT false NOT NULL,
    item_listprice numeric(16,4) NOT NULL,
    item_config boolean DEFAULT false,
    item_extdescrip text,
    item_upccode text,
    item_maxcost numeric(16,6) DEFAULT 0 NOT NULL,
    item_inv_uom_id integer NOT NULL,
    item_price_uom_id integer NOT NULL,
    item_warrdays integer DEFAULT 0,
    item_freightclass_id integer,
    item_tax_recoverable boolean DEFAULT false NOT NULL,
    item_listcost numeric(16,6) DEFAULT 0.0 NOT NULL,
    CONSTRAINT item_item_number_check CHECK ((item_number <> ''::text)),
    CONSTRAINT item_item_type_check CHECK (((((((((((((item_type = 'P'::bpchar) OR (item_type = 'M'::bpchar)) OR (item_type = 'F'::bpchar)) OR (item_type = 'O'::bpchar)) OR (item_type = 'R'::bpchar)) OR (item_type = 'S'::bpchar)) OR (item_type = 'T'::bpchar)) OR (item_type = 'B'::bpchar)) OR (item_type = 'L'::bpchar)) OR (item_type = 'Y'::bpchar)) OR (item_type = 'C'::bpchar)) OR (item_type = 'K'::bpchar))),
    CONSTRAINT item_sold_check CHECK ((NOT (item_sold AND (item_prodcat_id = (-1)))))
);


ALTER TABLE item OWNER TO admin;

--
-- Name: TABLE item; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE item IS 'Item information';


--
-- Name: COLUMN item.item_maxcost; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN item.item_maxcost IS 'Maximum cost for item.  Used to constrain purchase order price.';


--
-- Name: COLUMN item.item_listcost; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN item.item_listcost IS 'List cost for item.  Basis for markup pricing.';


--
-- Name: itemsite; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE itemsite (
    itemsite_id integer DEFAULT nextval(('itemsite_itemsite_id_seq'::text)::regclass) NOT NULL,
    itemsite_item_id integer NOT NULL,
    itemsite_warehous_id integer,
    itemsite_qtyonhand numeric(18,6) NOT NULL,
    itemsite_reorderlevel numeric(18,6) NOT NULL,
    itemsite_ordertoqty numeric(18,6) NOT NULL,
    itemsite_cyclecountfreq integer NOT NULL,
    itemsite_datelastcount date,
    itemsite_datelastused date,
    itemsite_loccntrl boolean NOT NULL,
    itemsite_safetystock numeric(18,6) NOT NULL,
    itemsite_minordqty numeric(18,6) NOT NULL,
    itemsite_multordqty numeric(18,6) NOT NULL,
    itemsite_leadtime integer NOT NULL,
    itemsite_abcclass character(1),
    itemsite_issuemethod character(1),
    itemsite_controlmethod character(1),
    itemsite_active boolean NOT NULL,
    itemsite_plancode_id integer NOT NULL,
    itemsite_costcat_id integer NOT NULL,
    itemsite_eventfence integer NOT NULL,
    itemsite_sold boolean NOT NULL,
    itemsite_stocked boolean NOT NULL,
    itemsite_freeze boolean DEFAULT false NOT NULL,
    itemsite_location_id integer NOT NULL,
    itemsite_useparams boolean NOT NULL,
    itemsite_useparamsmanual boolean NOT NULL,
    itemsite_soldranking integer DEFAULT 1,
    itemsite_createpr boolean,
    itemsite_location text,
    itemsite_location_comments text,
    itemsite_notes text,
    itemsite_perishable boolean NOT NULL,
    itemsite_autoabcclass boolean NOT NULL,
    itemsite_ordergroup integer DEFAULT 1 NOT NULL,
    itemsite_disallowblankwip boolean DEFAULT false NOT NULL,
    itemsite_maxordqty numeric(18,6) DEFAULT 0.0 NOT NULL,
    itemsite_mps_timefence integer DEFAULT 0 NOT NULL,
    itemsite_createwo boolean DEFAULT false NOT NULL,
    itemsite_warrpurc boolean DEFAULT false NOT NULL,
    itemsite_autoreg boolean DEFAULT false,
    itemsite_costmethod character(1) NOT NULL,
    itemsite_value numeric(12,2) NOT NULL,
    itemsite_ordergroup_first boolean DEFAULT false NOT NULL,
    itemsite_supply_itemsite_id integer,
    itemsite_planning_type character(1) DEFAULT 'M'::bpchar NOT NULL,
    itemsite_wosupply boolean DEFAULT false NOT NULL,
    itemsite_posupply boolean DEFAULT false NOT NULL,
    itemsite_lsseq_id integer,
    itemsite_cosdefault character(1),
    itemsite_createsopr boolean DEFAULT false,
    itemsite_createsopo boolean DEFAULT false,
    itemsite_dropship boolean DEFAULT false,
    itemsite_recvlocation_id integer DEFAULT (-1) NOT NULL,
    itemsite_issuelocation_id integer DEFAULT (-1) NOT NULL,
    itemsite_location_dist boolean DEFAULT false NOT NULL,
    itemsite_recvlocation_dist boolean DEFAULT false NOT NULL,
    itemsite_issuelocation_dist boolean DEFAULT false NOT NULL,
    CONSTRAINT itemsite_itemsite_abcclass_check CHECK (((((itemsite_abcclass = 'A'::bpchar) OR (itemsite_abcclass = 'B'::bpchar)) OR (itemsite_abcclass = 'C'::bpchar)) OR (itemsite_abcclass = 'T'::bpchar))),
    CONSTRAINT itemsite_itemsite_controlmethod_check CHECK (((((itemsite_controlmethod = 'N'::bpchar) OR (itemsite_controlmethod = 'R'::bpchar)) OR (itemsite_controlmethod = 'S'::bpchar)) OR (itemsite_controlmethod = 'L'::bpchar))),
    CONSTRAINT itemsite_itemsite_costmethod_check CHECK (((((itemsite_costmethod = 'N'::bpchar) OR (itemsite_costmethod = 'A'::bpchar)) OR (itemsite_costmethod = 'S'::bpchar)) OR (itemsite_costmethod = 'J'::bpchar))),
    CONSTRAINT itemsite_itemsite_ordergroup_check CHECK ((itemsite_ordergroup > 0))
);


ALTER TABLE itemsite OWNER TO admin;

--
-- Name: TABLE itemsite; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE itemsite IS 'Item Site information';


--
-- Name: COLUMN itemsite.itemsite_lsseq_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN itemsite.itemsite_lsseq_id IS 'Foreign key reference for automatic lot/serial numbering';


--
-- Name: COLUMN itemsite.itemsite_createsopr; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN itemsite.itemsite_createsopr IS 'Used to flag Sales items that create P/Rs';


--
-- Name: COLUMN itemsite.itemsite_createsopo; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN itemsite.itemsite_createsopo IS 'Used to flag Sales items that create P/Os';


--
-- Name: COLUMN itemsite.itemsite_dropship; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN itemsite.itemsite_dropship IS 'Used to flag Sales items to drop ship';


--
-- Name: whsinfo; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE whsinfo (
    warehous_id integer DEFAULT nextval(('warehous_warehous_id_seq'::text)::regclass) NOT NULL,
    warehous_code text NOT NULL,
    warehous_descrip text,
    warehous_fob text,
    warehous_active boolean,
    warehous_counttag_prefix text,
    warehous_counttag_number integer,
    warehous_bol_prefix text,
    warehous_bol_number integer,
    warehous_shipping boolean,
    warehous_useslips boolean,
    warehous_usezones boolean,
    warehous_aislesize integer,
    warehous_aislealpha boolean,
    warehous_racksize integer,
    warehous_rackalpha boolean,
    warehous_binsize integer,
    warehous_binalpha boolean,
    warehous_locationsize integer,
    warehous_locationalpha boolean,
    warehous_enforcearbl boolean,
    warehous_default_accnt_id integer,
    warehous_shipping_commission numeric(8,4) DEFAULT 0.00,
    warehous_cntct_id integer,
    warehous_addr_id integer,
    warehous_transit boolean DEFAULT false NOT NULL,
    warehous_shipform_id integer,
    warehous_shipvia_id integer,
    warehous_shipcomments text,
    warehous_costcat_id integer,
    warehous_sitetype_id integer,
    warehous_taxzone_id integer,
    warehous_sequence integer DEFAULT 0 NOT NULL,
    CONSTRAINT whsinfo_check CHECK (((warehous_transit AND (warehous_costcat_id IS NOT NULL)) OR (NOT warehous_transit))),
    CONSTRAINT whsinfo_warehous_code_check CHECK ((warehous_code <> ''::text))
);


ALTER TABLE whsinfo OWNER TO admin;

--
-- Name: TABLE whsinfo; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE whsinfo IS 'Warehouse information';



--
-- Name: physinvcount; Type: VIEW; Schema: api; Owner: admin
--

CREATE VIEW physinvcount AS
    SELECT whsinfo.warehous_code AS site, item.item_number, invcnt.invcnt_tagnumber AS tag_number, cntslip.cntslip_qty AS quantity, public.formatlocationname(cntslip.cntslip_location_id) AS location, cntslip.cntslip_lotserial AS lotserial, cntslip.cntslip_comments AS comment FROM ((((public.invcnt JOIN public.itemsite ON ((itemsite.itemsite_id = invcnt.invcnt_itemsite_id))) JOIN public.whsinfo ON ((whsinfo.warehous_id = itemsite.itemsite_warehous_id))) JOIN public.item ON ((item.item_id = itemsite.itemsite_item_id))) LEFT JOIN public.cntslip ON ((cntslip.cntslip_cnttag_id = invcnt.invcnt_id)));


ALTER TABLE physinvcount OWNER TO admin;

--
-- Name: VIEW physinvcount; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW physinvcount IS 'Physical Inventory Count Tag and Slip';


--
-- Name: insertphysinvcount(physinvcount); Type: FUNCTION; Schema: api; Owner: admin
--

CREATE FUNCTION insertphysinvcount(physinvcount) 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
  pNEW ALIAS FOR $1;
  _itemid INTEGER;
  _type TEXT;
  _siteid INTEGER;
  _itemsiteid INTEGER;
  _controlmethod TEXT;
  _loccntrl BOOLEAN;
  _locationid INTEGER;
  _lsid INTEGER;
  _invcntid INTEGER;
  _cntslipid INTEGER;
  _result INTEGER;

BEGIN

  -- Check Item
  SELECT item_id, item_type INTO _itemid, _type
  FROM item
  WHERE (item_number=UPPER(pNEW.item_number));
  IF (NOT FOUND OR _type IN ('F', 'R', 'L','J', 'K')) THEN
    SELECT item_id, item_type INTO _itemid, _type
    FROM item
    WHERE (item_upccode=pNEW.item_number);
    IF (NOT FOUND OR _type IN ('F', 'R', 'L','J', 'K')) THEN
      RAISE EXCEPTION 'Function insertPhysInvCount failed because Item % not found or invalid type', pNEW.item_number;
    END IF;
  END IF;

  -- Check Site
  SELECT warehous_id INTO _siteid
  FROM whsinfo
  WHERE (warehous_code=COALESCE(pNEW.site, (SELECT warehous_code
                                            FROM usrpref,whsinfo
                                            WHERE (usrpref_username=getEffectiveXtUser())
                                              AND (usrpref_name='PreferredWarehouse')
                                              AND (warehous_id=CAST(usrpref_value AS INTEGER)))));
  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Function insertPhysInvCount failed because Site % not found', pNEW.site;
  END IF;

  -- Check Itemsite
  SELECT itemsite_id, itemsite_controlmethod, itemsite_loccntrl INTO _itemsiteid, _controlmethod, _loccntrl
  FROM itemsite
  WHERE (itemsite_item_id=_itemid)
    AND (itemsite_warehous_id=_siteid);
  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Function insertPhysInvCount failed because Itemsite %, % not found', pNEW.site, pNEW.item_number;
  END IF;
  IF (_controlmethod = 'N') THEN
    RAISE EXCEPTION 'Function insertPhysInvCount failed because Itemsite %, % not inventory control method', pNEW.site, pNEW.item_number;
  END IF;
  IF (_controlmethod IN ('L', 'S') AND COALESCE(pNEW.lotserial, '') = '') THEN
    RAISE EXCEPTION 'Function insertPhysInvCount failed because Itemsite %, % lot/serial controlled and lotserial not provided', pNEW.site, pNEW.item_number;
  END IF;
  IF (_controlmethod = 'S') THEN
    -- Check for unique serial id
    SELECT ls_id INTO _lsid
    FROM ls
    WHERE (ls_number=pNEW.lotserial);
    IF (FOUND) THEN
      RAISE EXCEPTION 'Function insertPhysInvCount failed because Serial %, %, % not unique', pNEW.site, pNEW.item_number, pNEW.lotserial;
    END IF;
  END IF;
  IF (_loccntrl) THEN
    IF (pNEW.location IS NULL) THEN
      RAISE EXCEPTION 'Function insertPhysInvCount failed because Itemsite %, % multi location and location not provided', pNEW.site, pNEW.item_number;
    ELSE
      -- Check Location
      SELECT location_id INTO _locationid
      FROM location
      WHERE (location_id=getLocationId(pNEW.site, pNEW.location));
      IF (NOT FOUND) THEN
        RAISE EXCEPTION 'Function insertPhysInvCount failed because Location %, % not found', pNEW.site, pNEW.location;
      END IF;
    END IF;
  END IF;

  -- Create Count Tag
  SELECT CreateCountTag(_itemsiteid, pNEW.comment, FALSE, FALSE) INTO _invcntid;
  IF (_invcntid <= 0) THEN
    RAISE EXCEPTION 'Function insertPhysInvCount failed because CreateCountTag failed for Itemsite %, %', pNEW.site, pNEW.item_number;
  END IF;

  -- Create Count Slip
  INSERT INTO cntslip
  ( cntslip_cnttag_id,
    cntslip_username, cntslip_entered, cntslip_posted,
    cntslip_number, cntslip_qty,
    cntslip_location_id, cntslip_lotserial,
    cntslip_lotserial_expiration,
    cntslip_lotserial_warrpurc,
    cntslip_comments )
  VALUES
  ( _invcntid,
    getEffectiveXtUser(), CURRENT_TIMESTAMP, FALSE,
    'N/A', pNEW.quantity,
    COALESCE(_locationid, -1), pNEW.lotserial,
    NULL,
    NULL,
    pNEW.comment )
  RETURNING cntslip_id INTO _cntslipid;

  -- Post Count Slip
  SELECT postCountSlip(_cntslipid) INTO _result;
  IF (_result < 0) THEN
    RAISE EXCEPTION 'Function insertPhysInvCount failed because postCountSlip failed for Itemsite %, %, %', pNEW.site, pNEW.item_number, _result;
  END IF;

  RETURN TRUE;
END;
$_$;


ALTER FUNCTION api.insertphysinvcount(physinvcount) OWNER TO admin;


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

CREATE FUNCTION basecurrid() RETURNS integer
    LANGUAGE plpgsql IMMUTABLE
    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
  returnVal INTEGER;
BEGIN
  SELECT curr_id INTO returnVal
    FROM curr_symbol
   WHERE curr_base = TRUE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'No base currency found';
  END IF;
  RETURN returnVal;
END;
$$;


ALTER FUNCTION public.basecurrid() OWNER TO admin;

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

CREATE FUNCTION formatglaccount(paccntid integer) RETURNS text
    LANGUAGE plpgsql IMMUTABLE
    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 formatGlAccount(accnt_company, accnt_profit, accnt_number, accnt_sub)
  FROM accnt
  WHERE (accnt_id=pAccntid);

END;
$$;


ALTER FUNCTION public.formatglaccount(paccntid integer) OWNER TO admin;

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

CREATE FUNCTION formatsolinenumber(integer) RETURNS text
    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
  pSoitemid ALIAS FOR $1;
  _r RECORD;

BEGIN

  SELECT coitem_linenumber, coitem_subnumber
    INTO _r
    FROM coitem
   WHERE(coitem_id=pSoitemid);

  IF(NOT FOUND) THEN
    RETURN NULL;
  END IF;

  IF(COALESCE(_r.coitem_subnumber, 0) > 0) THEN
    RETURN _r.coitem_linenumber || '.' || _r.coitem_subnumber;
  END IF;

  RETURN _r.coitem_linenumber; 
END;
$_$;


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

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

CREATE FUNCTION geteffectivextuser() RETURNS text
    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.
BEGIN
/*
  The default return value of this function is simply
  the user currently connected.
  
  Overload this function from another schema 
  to implement specific user handling from an external 
  application that uses connection pooling. 
  Use setEffectiveXtUser(text) to create a temporary table that 
  inserts user data that can in turn be used as a lookup 
  reference for an over loaded version of this function like so:
  
  SELECT effective_value
  FROM effective_user
  WHERE effective_key = 'username'
*/

  RETURN CURRENT_USER;

END;
$$;


ALTER FUNCTION public.geteffectivextuser() OWNER TO admin;

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

CREATE FUNCTION getitemtaxtype(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
  pItemid ALIAS FOR $1;
  pTaxzoneid ALIAS FOR $2;
  _taxtypeid INTEGER;
BEGIN
  SELECT itemtax_taxtype_id
    INTO _taxtypeid
    FROM itemtax
   WHERE ((itemtax_item_id=pItemid)
     AND  (itemtax_taxzone_id=pTaxzoneid));
  IF (NOT FOUND) THEN
    SELECT itemtax_taxtype_id
      INTO _taxtypeid
      FROM itemtax
     WHERE ((itemtax_item_id=pItemid)
       AND  (itemtax_taxzone_id IS NULL));
    IF (NOT FOUND) THEN
      RETURN NULL;
    END IF;
  END IF;

  RETURN _taxtypeid;
END;
$_$;


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

--
-- Name: cohead; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE cohead (
    cohead_id integer DEFAULT nextval(('cohead_cohead_id_seq'::text)::regclass) NOT NULL,
    cohead_number text NOT NULL,
    cohead_cust_id integer NOT NULL,
    cohead_custponumber text,
    cohead_type character(1),
    cohead_orderdate date,
    cohead_warehous_id integer,
    cohead_shipto_id integer,
    cohead_shiptoname text,
    cohead_shiptoaddress1 text,
    cohead_shiptoaddress2 text,
    cohead_shiptoaddress3 text,
    cohead_shiptoaddress4 text,
    cohead_shiptoaddress5 text,
    cohead_salesrep_id integer NOT NULL,
    cohead_terms_id integer NOT NULL,
    cohead_fob text,
    cohead_shipvia text,
    cohead_shiptocity text,
    cohead_shiptostate text,
    cohead_shiptozipcode text,
    cohead_freight numeric(16,4) NOT NULL,
    cohead_misc numeric(16,4) DEFAULT 0 NOT NULL,
    cohead_imported boolean DEFAULT false,
    cohead_ordercomments text,
    cohead_shipcomments text,
    cohead_shiptophone text,
    cohead_shipchrg_id integer,
    cohead_shipform_id integer,
    cohead_billtoname text,
    cohead_billtoaddress1 text,
    cohead_billtoaddress2 text,
    cohead_billtoaddress3 text,
    cohead_billtocity text,
    cohead_billtostate text,
    cohead_billtozipcode text,
    cohead_misc_accnt_id integer,
    cohead_misc_descrip text,
    cohead_commission numeric(16,4),
    cohead_miscdate date,
    cohead_holdtype character(1),
    cohead_packdate date,
    cohead_prj_id integer,
    cohead_wasquote boolean DEFAULT false NOT NULL,
    cohead_lastupdated timestamp without time zone DEFAULT ('now'::text)::timestamp(6) with time zone NOT NULL,
    cohead_shipcomplete boolean DEFAULT false NOT NULL,
    cohead_created timestamp without time zone DEFAULT ('now'::text)::timestamp(6) with time zone,
    cohead_creator text DEFAULT geteffectivextuser(),
    cohead_quote_number text,
    cohead_billtocountry text,
    cohead_shiptocountry text,
    cohead_curr_id integer DEFAULT basecurrid(),
    cohead_calcfreight boolean DEFAULT false NOT NULL,
    cohead_shipto_cntct_id integer,
    cohead_shipto_cntct_honorific text,
    cohead_shipto_cntct_first_name text,
    cohead_shipto_cntct_middle text,
    cohead_shipto_cntct_last_name text,
    cohead_shipto_cntct_suffix text,
    cohead_shipto_cntct_phone text,
    cohead_shipto_cntct_title text,
    cohead_shipto_cntct_fax text,
    cohead_shipto_cntct_email text,
    cohead_billto_cntct_id integer,
    cohead_billto_cntct_honorific text,
    cohead_billto_cntct_first_name text,
    cohead_billto_cntct_middle text,
    cohead_billto_cntct_last_name text,
    cohead_billto_cntct_suffix text,
    cohead_billto_cntct_phone text,
    cohead_billto_cntct_title text,
    cohead_billto_cntct_fax text,
    cohead_billto_cntct_email text,
    cohead_taxzone_id integer,
    cohead_taxtype_id integer,
    cohead_ophead_id integer,
    cohead_status character(1) DEFAULT 'O'::bpchar NOT NULL,
    cohead_saletype_id integer,
    cohead_shipzone_id integer,
    CONSTRAINT cohead_check CHECK (((cohead_misc = (0)::numeric) OR ((cohead_misc <> (0)::numeric) AND (cohead_misc_accnt_id IS NOT NULL)))),
    CONSTRAINT cohead_cohead_number_check CHECK ((cohead_number <> ''::text))
);


ALTER TABLE cohead OWNER TO admin;

--
-- Name: TABLE cohead; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE cohead IS 'Sales Order header information';


--
-- Name: COLUMN cohead.cohead_saletype_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN cohead.cohead_saletype_id IS 'Associated sale type for sales order.';


--
-- Name: COLUMN cohead.cohead_shipzone_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN cohead.cohead_shipzone_id IS 'Associated shipping zone for sales order.';


--
-- Name: coitem; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE coitem (
    coitem_id integer DEFAULT nextval(('coitem_coitem_id_seq'::text)::regclass) NOT NULL,
    coitem_cohead_id integer,
    coitem_linenumber integer NOT NULL,
    coitem_itemsite_id integer,
    coitem_status character(1),
    coitem_scheddate date,
    coitem_promdate date,
    coitem_qtyord numeric(18,6) NOT NULL,
    coitem_unitcost numeric(16,6) NOT NULL,
    coitem_price numeric(16,4) NOT NULL,
    coitem_custprice numeric(16,4) NOT NULL,
    coitem_qtyshipped numeric(18,6) NOT NULL,
    coitem_order_id integer,
    coitem_memo text,
    coitem_imported boolean DEFAULT false,
    coitem_qtyreturned numeric(18,6),
    coitem_closedate timestamp with time zone,
    coitem_custpn text,
    coitem_order_type character(1),
    coitem_close_username text,
    coitem_lastupdated timestamp without time zone DEFAULT ('now'::text)::timestamp(6) with time zone NOT NULL,
    coitem_substitute_item_id integer,
    coitem_created timestamp without time zone DEFAULT ('now'::text)::timestamp(6) with time zone,
    coitem_creator text DEFAULT geteffectivextuser(),
    coitem_prcost numeric(16,6),
    coitem_qty_uom_id integer NOT NULL,
    coitem_qty_invuomratio numeric(20,10) NOT NULL,
    coitem_price_uom_id integer NOT NULL,
    coitem_price_invuomratio numeric(20,10) NOT NULL,
    coitem_warranty boolean DEFAULT false NOT NULL,
    coitem_cos_accnt_id integer,
    coitem_qtyreserved numeric(18,6) DEFAULT 0.0 NOT NULL,
    coitem_subnumber integer DEFAULT 0 NOT NULL,
    coitem_firm boolean DEFAULT false NOT NULL,
    coitem_taxtype_id integer,
    coitem_rev_accnt_id integer,
    coitem_pricemode character(1) DEFAULT 'D'::bpchar NOT NULL,
    CONSTRAINT coitem_coitem_status_check CHECK ((((coitem_status = 'O'::bpchar) OR (coitem_status = 'C'::bpchar)) OR (coitem_status = 'X'::bpchar))),
    CONSTRAINT valid_coitem_pricemode CHECK ((coitem_pricemode = ANY (ARRAY['D'::bpchar, 'M'::bpchar])))
);


ALTER TABLE coitem OWNER TO admin;

--
-- Name: TABLE coitem; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE coitem IS 'Sales Order Line Item information';


--
-- Name: COLUMN coitem.coitem_pricemode; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN coitem.coitem_pricemode IS 'Pricing mode for sales order item.  Valid values are D-discount, and M-markup';


--
-- Name: pohead; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE pohead (
    pohead_id integer DEFAULT nextval(('pohead_pohead_id_seq'::text)::regclass) NOT NULL,
    pohead_status character(1),
    pohead_number text NOT NULL,
    pohead_orderdate date,
    pohead_vend_id integer,
    pohead_fob text,
    pohead_shipvia text,
    pohead_comments text,
    pohead_freight numeric(16,2) DEFAULT 0,
    pohead_printed boolean DEFAULT false,
    pohead_terms_id integer,
    pohead_warehous_id integer,
    pohead_vendaddr_id integer,
    pohead_agent_username text,
    pohead_curr_id integer DEFAULT basecurrid(),
    pohead_saved boolean DEFAULT true NOT NULL,
    pohead_taxzone_id integer,
    pohead_taxtype_id integer,
    pohead_dropship boolean DEFAULT false,
    pohead_vend_cntct_id integer,
    pohead_vend_cntct_honorific text,
    pohead_vend_cntct_first_name text,
    pohead_vend_cntct_middle text,
    pohead_vend_cntct_last_name text,
    pohead_vend_cntct_suffix text,
    pohead_vend_cntct_phone text,
    pohead_vend_cntct_title text,
    pohead_vend_cntct_fax text,
    pohead_vend_cntct_email text,
    pohead_vendaddress1 text,
    pohead_vendaddress2 text,
    pohead_vendaddress3 text,
    pohead_vendcity text,
    pohead_vendstate text,
    pohead_vendzipcode text,
    pohead_vendcountry text,
    pohead_shipto_cntct_id integer,
    pohead_shipto_cntct_honorific text,
    pohead_shipto_cntct_first_name text,
    pohead_shipto_cntct_middle text,
    pohead_shipto_cntct_last_name text,
    pohead_shipto_cntct_suffix text,
    pohead_shipto_cntct_phone text,
    pohead_shipto_cntct_title text,
    pohead_shipto_cntct_fax text,
    pohead_shipto_cntct_email text,
    pohead_shiptoaddress_id integer,
    pohead_shiptoaddress1 text,
    pohead_shiptoaddress2 text,
    pohead_shiptoaddress3 text,
    pohead_shiptocity text,
    pohead_shiptostate text,
    pohead_shiptozipcode text,
    pohead_shiptocountry text,
    pohead_cohead_id integer,
    pohead_released date,
    pohead_shiptoname text,
    CONSTRAINT pohead_pohead_number_check CHECK ((pohead_number <> ''::text)),
    CONSTRAINT pohead_pohead_status_check CHECK ((((pohead_status = 'U'::bpchar) OR (pohead_status = 'O'::bpchar)) OR (pohead_status = 'C'::bpchar)))
);


ALTER TABLE pohead OWNER TO admin;

--
-- Name: TABLE pohead; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE pohead IS 'Purchase Order header information';


--
-- Name: poitem; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE poitem (
    poitem_id integer DEFAULT nextval(('poitem_poitem_id_seq'::text)::regclass) NOT NULL,
    poitem_status character(1),
    poitem_pohead_id integer,
    poitem_linenumber integer,
    poitem_duedate date,
    poitem_itemsite_id integer,
    poitem_vend_item_descrip text,
    poitem_vend_uom text,
    poitem_invvenduomratio numeric(20,10),
    poitem_qty_ordered numeric(18,6) NOT NULL,
    poitem_qty_received numeric(18,6) DEFAULT 0.0 NOT NULL,
    poitem_qty_returned numeric(18,6) DEFAULT 0.0 NOT NULL,
    poitem_qty_vouchered numeric(18,6) DEFAULT 0.0 NOT NULL,
    poitem_unitprice numeric(16,6),
    poitem_vend_item_number text,
    poitem_comments text,
    poitem_qty_toreceive numeric(18,6),
    poitem_expcat_id integer,
    poitem_itemsrc_id integer,
    poitem_freight numeric(16,4) DEFAULT 0.0 NOT NULL,
    poitem_freight_received numeric(16,4) DEFAULT 0.0 NOT NULL,
    poitem_freight_vouchered numeric(16,4) DEFAULT 0.0 NOT NULL,
    poitem_prj_id integer,
    poitem_stdcost numeric(16,6),
    poitem_bom_rev_id integer,
    poitem_boo_rev_id integer,
    poitem_manuf_name text,
    poitem_manuf_item_number text,
    poitem_manuf_item_descrip text,
    poitem_taxtype_id integer,
    poitem_tax_recoverable boolean DEFAULT true NOT NULL,
    poitem_rlsd_duedate date,
    poitem_order_id integer,
    poitem_order_type character(1),
    CONSTRAINT poitem_poitem_status_check CHECK ((((poitem_status = 'U'::bpchar) OR (poitem_status = 'O'::bpchar)) OR (poitem_status = 'C'::bpchar)))
);


ALTER TABLE poitem OWNER TO admin;

--
-- Name: TABLE poitem; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE poitem IS 'Purchase Order Line Item information';


--
-- Name: taxtype; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE taxtype (
    taxtype_id integer NOT NULL,
    taxtype_name text NOT NULL,
    taxtype_descrip text,
    taxtype_sys boolean DEFAULT false NOT NULL,
    CONSTRAINT taxtype_taxtype_name_check CHECK ((taxtype_name <> ''::text))
);


ALTER TABLE taxtype OWNER TO admin;

--
-- Name: TABLE taxtype; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE taxtype IS 'The list of Tax Types';


--
-- Name: uom; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE uom (
    uom_id integer NOT NULL,
    uom_name text NOT NULL,
    uom_descrip text,
    uom_item_weight boolean DEFAULT false NOT NULL,
    CONSTRAINT uom_uom_name_check CHECK ((uom_name <> ''::text))
);


ALTER TABLE uom OWNER TO admin;

--
-- Name: TABLE uom; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE uom IS 'Unit of Measure information';



--
-- Name: salesline; Type: VIEW; Schema: api; Owner: admin
--

CREATE VIEW salesline AS
    SELECT (cohead.cohead_number)::character varying AS order_number, (public.formatsolinenumber(coitem.coitem_id))::character varying AS line_number, l.item_number, coitem.coitem_custpn AS customer_pn, s.item_number AS substitute_for, whsinfo.warehous_code AS sold_from_site, coitem.coitem_status AS status, coitem.coitem_qtyord AS qty_ordered, q.uom_name AS qty_uom, coitem.coitem_price AS net_unit_price, p.uom_name AS price_uom, coitem.coitem_scheddate AS scheduled_date, coitem.coitem_promdate AS promise_date, coitem.coitem_warranty AS warranty, COALESCE((SELECT taxtype.taxtype_name FROM public.taxtype WHERE (taxtype.taxtype_id = public.getitemtaxtype(l.item_id, cohead.cohead_taxzone_id))), 'None'::text) AS tax_type, CASE WHEN (coitem.coitem_price = (0)::numeric) THEN '100'::text WHEN (coitem.coitem_custprice = (0)::numeric) THEN 'N/A'::text ELSE (round((((1)::numeric - (coitem.coitem_price / coitem.coitem_custprice)) * (100)::numeric), 4))::text END AS discount_pct_from_list, CASE WHEN (coitem.coitem_order_id = (-1)) THEN false ELSE true END AS create_order, CASE WHEN (coitem.coitem_order_id = (-1)) THEN ''::text ELSE ((pohead.pohead_number || '-'::text) || poitem.poitem_linenumber) END AS create_po, coitem.coitem_prcost AS overwrite_po_price, coitem.coitem_memo AS notes, CASE WHEN (coitem.coitem_cos_accnt_id IS NOT NULL) THEN public.formatglaccount(coitem.coitem_cos_accnt_id) ELSE NULL::text END AS alternate_cos_account, CASE WHEN (coitem.coitem_rev_accnt_id IS NOT NULL) THEN public.formatglaccount(coitem.coitem_rev_accnt_id) ELSE NULL::text END AS alternate_rev_account FROM public.cohead, (((public.coitem LEFT JOIN public.itemsite isb ON ((coitem.coitem_substitute_item_id = isb.itemsite_id))) LEFT JOIN public.item s ON ((isb.itemsite_item_id = s.item_id))) LEFT JOIN (public.poitem JOIN public.pohead ON ((poitem.poitem_pohead_id = pohead.pohead_id))) ON ((poitem.poitem_id = coitem.coitem_order_id))), public.itemsite il, public.item l, public.whsinfo, public.uom q, public.uom p WHERE ((((((cohead.cohead_id = coitem.coitem_cohead_id) AND (coitem.coitem_itemsite_id = il.itemsite_id)) AND (il.itemsite_item_id = l.item_id)) AND (il.itemsite_warehous_id = whsinfo.warehous_id)) AND (coitem.coitem_qty_uom_id = q.uom_id)) AND (coitem.coitem_price_uom_id = p.uom_id)) ORDER BY cohead.cohead_number, coitem.coitem_linenumber, coitem.coitem_subnumber;


ALTER TABLE salesline OWNER TO admin;

--
-- Name: VIEW salesline; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW salesline IS 'Sales Order Line Item';


--
-- Name: insertsalesline(salesline); Type: FUNCTION; Schema: api; Owner: admin
--

CREATE FUNCTION insertsalesline(salesline) 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
  pNEW ALIAS FOR $1;
  _r RECORD;

BEGIN

  IF (NOT EXISTS (SELECT cohead_id FROM cohead WHERE cohead_number=pNEW.order_number)) THEN
    RAISE EXCEPTION 'Function insertSalesLine failed because Sales Order % not found', pNEW.order_number;
  END IF;

  IF (NOT EXISTS (SELECT item_id FROM item WHERE item_number=pNEW.item_number)) THEN
    RAISE EXCEPTION 'Function insertSalesLine failed because Item Number % not found', pNEW.item_number;
  END IF;

  SELECT * INTO _r
  FROM cohead, itemsite, item, whsinfo
  WHERE ((cohead_number=pNEW.order_number)
  AND (itemsite_warehous_id=warehous_id
  AND (itemsite_item_id=item_id)
  AND (itemsite_active)
  AND (item_number=pNEW.item_number)
  AND (warehous_active)
  AND (warehous_id=COALESCE(getWarehousId(pNEW.sold_from_site,'ALL'),cohead_warehous_id,fetchprefwarehousid()))));

  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Function insertSalesLine failed with unknown failure to retrieve Sales Order';
  END IF;

  INSERT INTO coitem (
    coitem_cohead_id,
    coitem_linenumber,
    coitem_itemsite_id,
    coitem_status,
    coitem_scheddate,
    coitem_promdate,
    coitem_qtyord,
    coitem_qty_uom_id,
    coitem_qty_invuomratio,
    coitem_qtyshipped,
    coitem_unitcost,
    coitem_price,
    coitem_price_uom_id,
    coitem_price_invuomratio,
    coitem_custprice,
    coitem_order_id,
    coitem_memo,
    coitem_imported,
    coitem_qtyreturned,
    coitem_custpn,
    coitem_order_type,
    coitem_substitute_item_id,
    coitem_prcost,
    coitem_taxtype_id,
    coitem_warranty,
    coitem_cos_accnt_id,
    coitem_rev_accnt_id)
  VALUES (
    _r.cohead_id,
    pNEW.line_number::INTEGER,
    _r.itemsite_id,
    pNEW.status,
    pNEW.scheduled_date,
    pNEW.promise_date,
    pNEW.qty_ordered,
    COALESCE(getUomId(pNEW.qty_uom),_r.item_inv_uom_id),
    itemuomtouomratio(_r.item_id,COALESCE(getUomId(pNEW.qty_uom),_r.item_inv_uom_id),_r.item_inv_uom_id),
    0,
    stdCost(_r.item_id),
    COALESCE(pNEW.net_unit_price,itemPrice(_r.item_id,_r.cohead_cust_id,
             _r.cohead_shipto_id,pNEW.qty_ordered,_r.cohead_curr_id,_r.cohead_orderdate)),
    COALESCE(getUomId(pNEW.price_uom),_r.item_price_uom_id),
    itemuomtouomratio(_r.item_id,COALESCE(getUomId(pNEW.price_uom),_r.item_price_uom_id),_r.item_price_uom_id),
    itemPrice(_r.item_id, _r.cohead_cust_id, _r.cohead_shipto_id,
              pNEW.qty_ordered, _r.item_inv_uom_id, _r.item_price_uom_id,
              _r.cohead_curr_id,_r.cohead_orderdate,
              CASE WHEN (fetchMetricText('soPriceEffective') = 'ScheduleDate') THEN pNEW.scheduled_date
                   WHEN (fetchMetricText('soPriceEffective') = 'OrderDate') THEN _r.cohead_orderdate
                   ELSE CURRENT_DATE END,
              NULL)
    -1,
    pNEW.notes,
    true,
    0,
    pNEW.customer_pn,
    CASE
      WHEN ((pNEW.create_order  AND (_r.item_type = 'M')) OR
           ((pNEW.create_order IS NULL) AND _r.itemsite_createwo) AND (NOT _r.itemsite_stocked)) THEN
        'W'
      WHEN ((pNEW.create_order AND (_r.item_type = 'P')) OR
           ((pNEW.create_order IS NULL) AND _r.itemsite_createsopr) AND (NOT _r.itemsite_stocked)) THEN
        'R'
      WHEN ((pNEW.create_order AND (_r.item_type = 'P') AND (_r.itemsite_createsopo)) OR
           ((pNEW.create_order IS NULL) AND _r.itemsite_createsopo) AND (NOT _r.itemsite_stocked)) THEN
        'P'
    END,
    getitemid(pNEW.substitute_for),
    pNEW.overwrite_po_price,
    COALESCE(getTaxTypeId(pNEW.tax_type), getItemTaxType(_r.itemsite_item_id, _r.cohead_taxzone_id)),
    pNEW.warranty,
    getGlAccntId(pNEW.alternate_cos_account),
    getGlAccntId(pNEW.alternate_rev_account)
    );

  RETURN TRUE;
END;
$_$;


ALTER FUNCTION api.insertsalesline(salesline) OWNER TO admin;


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

CREATE FUNCTION _accntdeletetrigger() 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
  _accntnum     TEXT := formatGLAccount(OLD.accnt_id);
  _check INTEGER;
BEGIN
-- This trigger is to protect against accounts that are in use

--  Check to see if the passed accnt is used in a Cost Category
  SELECT costcat_id INTO _check
  FROM costcat
  WHERE ( (costcat_asset_accnt_id=OLD.accnt_id)
     OR   (costcat_liability_accnt_id=OLD.accnt_id)
     OR   (costcat_adjustment_accnt_id=OLD.accnt_id)
     OR   (costcat_purchprice_accnt_id=OLD.accnt_id)
     OR   (costcat_laboroverhead_accnt_id=OLD.accnt_id)
     OR   (costcat_scrap_accnt_id=OLD.accnt_id)
     OR   (costcat_invcost_accnt_id=OLD.accnt_id)
     OR   (costcat_wip_accnt_id=OLD.accnt_id)
     OR   (costcat_shipasset_accnt_id=OLD.accnt_id)
     OR   (costcat_mfgscrap_accnt_id=OLD.accnt_id)
     OR   (costcat_transform_accnt_id=OLD.accnt_id)
     OR   (costcat_freight_accnt_id=OLD.accnt_id) )
  LIMIT 1;
  IF (FOUND) THEN
    RAISE EXCEPTION 'Can not delete, used in Cost Category';
  END IF;

--  Check to see if the passed accnt is used in a Sales Account Assignment
  SELECT salesaccnt_id INTO _check
  FROM salesaccnt
  WHERE ( (salesaccnt_sales_accnt_id=OLD.accnt_id)
     OR   (salesaccnt_credit_accnt_id=OLD.accnt_id)
     OR   (salesaccnt_cos_accnt_id=OLD.accnt_id) )
  LIMIT 1;
  IF (FOUND) THEN
    RAISE EXCEPTION 'Can not delete, used in Sales Account Assignment';
  END IF;

--  Check to see if the passed accnt is used in a A/R Account Assignment
  SELECT araccnt_id INTO _check
  FROM araccnt
  WHERE ( (araccnt_freight_accnt_id=OLD.accnt_id)
     OR   (araccnt_ar_accnt_id=OLD.accnt_id)
     OR   (araccnt_prepaid_accnt_id=OLD.accnt_id) )
  LIMIT 1;
  IF (FOUND) THEN
    RAISE EXCEPTION 'Can not delete, used in A/R Account Assignment';
  END IF;

--  Check to see if the passed accnt is used in a Warehouse
  SELECT warehous_id INTO _check
  FROM whsinfo
  WHERE (warehous_default_accnt_id=OLD.accnt_id)
  LIMIT 1;
  IF (FOUND) THEN
    RAISE EXCEPTION 'Can not delete, used in Site';
  END IF;

--  Check to see if the passed accnt is used in a Bank Account
  SELECT bankaccnt_id INTO _check
  FROM bankaccnt
  WHERE (bankaccnt_accnt_id=OLD.accnt_id)
  LIMIT 1;
  IF (FOUND) THEN
    RAISE EXCEPTION 'Can not delete, used in Bank Account';
  END IF;

--  Check to see if the passed accnt is used in an Expense Category
  SELECT expcat_id INTO _check
  FROM expcat
  WHERE ( (expcat_exp_accnt_id=OLD.accnt_id)
     OR   (expcat_liability_accnt_id=OLD.accnt_id)
     OR   (expcat_purchprice_accnt_id=OLD.accnt_id)
     OR   (expcat_freight_accnt_id=OLD.accnt_id) )
  LIMIT 1;
  IF (FOUND) THEN
    RAISE EXCEPTION 'Can not delete, used in Expense Category';
  END IF;

--  Check to see if the passed accnt is used in a Tax Code
  SELECT tax_id INTO _check
  FROM tax
  WHERE (tax_sales_accnt_id=OLD.accnt_id)
  LIMIT 1;
  IF (FOUND) THEN
    RAISE EXCEPTION 'Can not delete, used in Tax Code';
  END IF;

--  Check to see if the passed accnt is used in a Standard Journal Item
  SELECT stdjrnlitem_id INTO _check
  FROM stdjrnlitem
  WHERE (stdjrnlitem_accnt_id=OLD.accnt_id)
  LIMIT 1;
  IF (FOUND) THEN
    RAISE EXCEPTION 'Can not delete, used in Standard Journal Item';
  END IF;

--  Check to see if the passed accnt is used in a A/P Account Assignment
  SELECT apaccnt_ap_accnt_id INTO _check
  FROM apaccnt
  WHERE ( (apaccnt_ap_accnt_id=OLD.accnt_id)
     OR   (apaccnt_prepaid_accnt_id=OLD.accnt_id)
     OR   (apaccnt_discount_accnt_id=OLD.accnt_id) )
  LIMIT 1;
  IF (FOUND) THEN
    RAISE EXCEPTION 'Can not delete, used in A/P Account Assignment';
  END IF;

--  Check to see if the passed accnt is used in an A/R Open Item record
  SELECT aropen_accnt_id INTO _check
    FROM aropen
   WHERE (aropen_accnt_id=OLD.accnt_id)
   LIMIT 1;
  IF (FOUND) THEN
    RAISE EXCEPTION 'Can not delete, used in A/R Open Item';
  END IF;

--  Check to see if the passed accnt has been used in the G/L
  SELECT gltrans_accnt_id INTO _check
  FROM gltrans
  WHERE (gltrans_accnt_id=OLD.accnt_id)
  LIMIT 1;
  IF (FOUND) THEN
    RAISE EXCEPTION 'Can not delete, used in G/L Transaction';
  END IF;

  SELECT sltrans_accnt_id INTO _check
  FROM sltrans
  WHERE (sltrans_accnt_id=OLD.accnt_id)
  LIMIT 1;
  IF (FOUND) THEN
    RAISE EXCEPTION 'Can not delete, used in G/L Journal Transaction';
  END IF;

  SELECT glseries_accnt_id INTO _check
  FROM glseries
  WHERE (glseries_accnt_id=OLD.accnt_id)
  LIMIT 1;
  IF (FOUND) THEN
    RAISE EXCEPTION 'Can not delete, used in G/L Series';
  END IF;

  SELECT trialbal_accnt_id INTO _check
  FROM trialbal
  WHERE (trialbal_accnt_id=OLD.accnt_id)
    AND (trialbal_beginning != 0 OR trialbal_ending != 0)
  LIMIT 1;
  IF (FOUND) THEN
    RAISE EXCEPTION 'Can not delete, used in Trial Balance';
  END IF;

  SELECT cashrcptmisc_accnt_id INTO _check
  FROM cashrcptmisc
  WHERE (cashrcptmisc_accnt_id=OLD.accnt_id)
  LIMIT 1;
  IF (FOUND) THEN
    RAISE EXCEPTION 'Can not delete, used in Cash Receipt Misc. Application';
  END IF;

  -- TODO: everything above here should be replaced by fkeys
  IF (OLD.accnt_id = fetchMetricValue('DefaultAPAccount')) THEN
    RAISE EXCEPTION 'Cannot delete the default A/P Account [xtuple: accnt, -1, %]',
                    _accntnum;
  ELSIF (OLD.accnt_id = fetchMetricValue('DefaultARAccount')) THEN
    RAISE EXCEPTION 'Cannot delete the default A/R Account [xtuple: accnt, -2, %]',
                    _accntnum;
  END IF;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._accntdeletetrigger() OWNER TO admin;

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

CREATE FUNCTION _accnttrigger() 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
  ffSub BOOLEAN;
  ffProfit BOOLEAN;
  result INTEGER;
BEGIN
  SELECT (metric_value='t')
    INTO ffSub
    FROM metric
   WHERE(metric_name='GLFFSubaccounts')
   LIMIT 1;
  ffSub := COALESCE(ffSub, false);

  SELECT (metric_value='t')
    INTO ffProfit
    FROM metric
   WHERE(metric_name='GLFFProfitCenters')
   LIMIT 1;
  ffProfit := COALESCE(ffProfit, false);

  IF (NEW.accnt_sub IS NOT NULL AND ffSub = false) THEN
    SELECT subaccnt_id
      INTO result
      FROM subaccnt
     WHERE(subaccnt_number=NEW.accnt_sub)
     LIMIT 1;
    IF (NOT FOUND) THEN
      RAISE EXCEPTION 'You must supply a valid Sub Account Number.';
    END IF;
  END IF;

  IF (NEW.accnt_profit IS NOT NULL AND ffProfit = false) THEN
    SELECT prftcntr_id
      INTO result
      FROM prftcntr
     WHERE(prftcntr_number=NEW.accnt_profit)
     LIMIT 1;
    IF (NOT FOUND) THEN
      RAISE EXCEPTION 'You must supply a valid Profit Center Number.';
    END IF;
  END IF;

  IF (TG_OP = 'UPDATE') THEN
    IF ((NEW.accnt_type != OLD.accnt_type) AND
        (SELECT (count(*) > 0) FROM gltrans WHERE (gltrans_accnt_id=NEW.accnt_id))) THEN
      RAISE EXCEPTION 'You may not change the account type of an account that has transaction history';
    END IF;
  END IF;

  NEW.accnt_name := formatGlAccount(NEW.accnt_company, NEW.accnt_profit, NEW.accnt_number, NEW.accnt_sub);

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._accnttrigger() OWNER TO admin;

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

CREATE FUNCTION _accntuniquetrigger() 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
  -- This trigger is to protect against id collision on inherited tables since there is no way 
  -- to enforce that with regular constraints.  It should be applied to accnt and any table that 
  -- inherits accnt.
  IF (SELECT (count(accnt_id) > 0) FROM accnt WHERE (accnt_id = NEW.accnt_id)) THEN
    RAISE EXCEPTION 'Can not create record on account with duplicate key %.', NEW.accnt_id;
  END IF;
  
  RETURN NEW;
END;
$$;


ALTER FUNCTION public._accntuniquetrigger() OWNER TO admin;

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

CREATE FUNCTION _addrafterdeletetrigger() 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 = 'ADDR'
    AND charass_target_id = OLD.addr_id;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._addrafterdeletetrigger() OWNER TO admin;

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

CREATE FUNCTION _addrtrigger() 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
    _uses	INTEGER	:= 0;

  BEGIN

    IF (TG_OP = 'INSERT') THEN
      --- clear the number from the issue cache
      PERFORM clearNumberIssue('AddressNumber', NEW.addr_number);
    ELSE
      SELECT count(*) INTO _uses
      FROM cntct
      WHERE ((cntct_addr_id=OLD.addr_id)
        AND   cntct_active);
    END IF;

    IF (TG_OP = 'UPDATE') THEN
      IF (OLD.addr_active AND NOT NEW.addr_active AND _uses > 0) THEN
	RAISE EXCEPTION 'Cannot inactivate Address with Active Contacts (%)',
			_uses;
      END IF;
    ELSIF (TG_OP = 'DELETE') THEN
      IF (_uses > 0) THEN
	RAISE EXCEPTION 'Cannot Delete Address with Active Contacts (%)',
			_uses;
      END IF;

      UPDATE cntct SET cntct_addr_id = NULL
      WHERE ((cntct_addr_id=OLD.addr_id)
	AND  (NOT cntct_active));

      RETURN OLD;
    END IF;

    RETURN NEW;
  END;
$$;


ALTER FUNCTION public._addrtrigger() OWNER TO admin;

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

CREATE FUNCTION _alarmbeforetrigger() 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
    PERFORM clearNumberIssue('AlarmNumber', NEW.alarm_number);

    RETURN NEW;
  END;
$$;


ALTER FUNCTION public._alarmbeforetrigger() OWNER TO admin;

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

CREATE FUNCTION _apapplytrigger() 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
  _tpaid NUMERIC;

BEGIN

-- get the exchange rate for the doc date
  IF (TG_OP = 'INSERT') THEN
    SELECT currtocurr(NEW.apapply_curr_id,apopen_curr_id,NEW.apapply_amount,NEW.apapply_postdate) 
      INTO _tpaid
    FROM apopen
    WHERE ( apopen_id=NEW.apapply_target_apopen_id );
    IF (FOUND) THEN
      NEW.apapply_target_paid := _tpaid;
    ELSE
      RAISE EXCEPTION 'Error calculating paid amount on application';
    END IF;
  END IF;

  RETURN NEW;

END;

$$;


ALTER FUNCTION public._apapplytrigger() OWNER TO admin;

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

CREATE FUNCTION _apopentrigger() 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
  _currrate NUMERIC;

BEGIN

-- get the base exchange rate for the doc date
  IF (TG_OP = 'INSERT' AND NEW.apopen_curr_rate IS NULL) THEN
    SELECT curr_rate INTO _currrate
    FROM curr_rate
    WHERE ( (NEW.apopen_curr_id=curr_id)
    AND ( NEW.apopen_docdate BETWEEN curr_effective 
                                 AND curr_expires) );
    IF (FOUND) THEN
      NEW.apopen_curr_rate := _currrate;
    ELSE
      RAISE EXCEPTION 'Currency exchange rate not found';
    END IF;
  END IF;

  NEW.apopen_open := NEW.apopen_amount > NEW.apopen_paid;

  IF (TG_OP = 'INSERT') THEN
    IF (NEW.apopen_open=FALSE) THEN
      NEW.apopen_status='C';
    ELSE
      NEW.apopen_status='O';
    END IF;

     --- clear the number from the issue cache
    PERFORM clearNumberIssue('APMemoNumber', NEW.apopen_docnumber);
  END IF;
  
  IF (TG_OP = 'UPDATE') THEN
    IF ((OLD.apopen_open=TRUE) AND (NEW.apopen_open=FALSE)) THEN
      NEW.apopen_status='C';
      IF (NEW.apopen_closedate IS NULL) THEN
        NEW.apopen_closedate=CURRENT_DATE;
      END IF;
    END IF;
    
    IF ((OLD.apopen_open=FALSE) AND (NEW.apopen_open=TRUE)) THEN
      NEW.apopen_status='O';
      NEW.apopen_closedate=NULL;
    END IF;
  END IF;

  RETURN NEW;

END;

$$;


ALTER FUNCTION public._apopentrigger() OWNER TO admin;

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

CREATE FUNCTION _arapplytrigger() 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
  _tpaid NUMERIC;

BEGIN

-- get the exchange rate for the doc date
  IF (TG_OP = 'INSERT') THEN
    IF (NEW.arapply_target_doctype != 'K') THEN 
      SELECT round(currtocurr(NEW.arapply_curr_id,aropen_curr_id,NEW.arapply_applied,NEW.arapply_postdate),2) 
        INTO NEW.arapply_target_paid
      FROM aropen
      WHERE ( aropen_id=NEW.arapply_target_aropen_id );
    ELSE
      SELECT round(currtocurr(NEW.arapply_curr_id,aropen_curr_id,NEW.arapply_applied,NEW.arapply_postdate),2) 
        INTO NEW.arapply_target_paid
      FROM aropen
      WHERE ( aropen_id=NEW.arapply_source_aropen_id );
    END IF;
    IF NOT FOUND THEN
      NEW.arapply_target_paid := NEW.arapply_applied;
    END IF;
  END IF;

  RETURN NEW;

END;

$$;


ALTER FUNCTION public._arapplytrigger() OWNER TO admin;

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

CREATE FUNCTION _aropenaftertrigger() 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
  _openAmount NUMERIC;
  _lateCount INTEGER := 0;
  _graceDays INTEGER;
  _checkLate BOOLEAN := false;
  _checkLimit BOOLEAN := false;
  _id INTEGER;
BEGIN

  IF (TG_OP = 'INSERT') THEN
    _id := NEW.aropen_id;
  ELSE
    _id := OLD.aropen_id;
  END IF;
-- If metric is set then auto close any associated incidents when AR is closed
  IF (fetchMetricBool('AutoCloseARIncident')) THEN
    IF (NEW.aropen_open = FALSE) THEN
      UPDATE incdt SET incdt_status='L' WHERE (incdt_aropen_id=_id);
    END IF;
  END IF;

  RETURN NEW;

END;
$$;


ALTER FUNCTION public._aropenaftertrigger() OWNER TO admin;

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

CREATE FUNCTION _aropentrigger() 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
  _openAmount NUMERIC;
  _p RECORD;
  _lateCount INTEGER := 0;
  _graceDays INTEGER;
  _checkLate BOOLEAN := false;
  _checkLimit BOOLEAN := false;
  _id INTEGER;
  _currRate NUMERIC;
BEGIN
  -- Checks
  -- Start with privileges
  IF ( (NOT checkPrivilege('MaintainARMemos')) AND
       (NOT checkPrivilege('PostMiscInvoices')) AND
       (NOT checkPrivilege('MaintainSalesOrders')) AND -- #24608 Required to post payments on SOs
       (NOT checkPrivilege('PostARDocuments')) ) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain A/R Memos.';
  END IF;

  IF ( (NEW.aropen_docnumber IS NULL) OR (LENGTH(NEW.aropen_docnumber) = 0) ) THEN
    RAISE EXCEPTION 'You must enter a valid Document # for this A/R Memo.';
  END IF;

  IF (TG_OP IN ('INSERT', 'UPDATE') AND NEW.aropen_cust_id < 0) THEN
    RAISE WARNING 'Fixing deprecated use of negative aropen_cust_id';
    NEW.aropen_cust_id := NULL;
  END IF;

  IF (TG_OP IN ('INSERT', 'UPDATE') AND NEW.aropen_salesrep_id < 0) THEN
    RAISE WARNING 'Fixing deprecated use of negative aropen_salesrep_id';
    NEW.aropen_salesrep_id := NULL;
  END IF;

  IF (TG_OP = 'INSERT') THEN
    SELECT aropen_id INTO _id
    FROM aropen
    WHERE ( (aropen_doctype=NEW.aropen_doctype)
      AND   (aropen_docnumber=NEW.aropen_docnumber) )
    LIMIT 1;
    IF (FOUND) THEN
      RAISE EXCEPTION 'This Document Type/Number already exists. You may not enter a duplicate A/R Memo.';
    END IF;

    --- clear the number from the issue cache if applicable
    PERFORM clearNumberIssue('ARMemoNumber', NEW.aropen_docnumber);
  END IF;

-- Determine the number of late invoices
  IF ( SELECT (metric_value='t')
         FROM metric
        WHERE(metric_name='AutoCreditWarnLateCustomers')) THEN
    _checkLate := true;

    SELECT COALESCE(metric_value::integer, _graceDays)
      INTO _graceDays
      FROM metric
     WHERE(metric_name='DefaultAutoCreditWarnGraceDays');
    IF (NOT FOUND) THEN
      _graceDays := 30;
    END IF;
    SELECT COALESCE(cust_gracedays, _graceDays)
      INTO _graceDays
      FROM custinfo
     WHERE(cust_id=NEW.aropen_cust_id);
    IF (NOT FOUND) THEN
      _graceDays := 30;
    END IF;

    SELECT count(aropen_id)
      INTO _lateCount
      FROM aropen
     WHERE((NEW.aropen_cust_id = aropen_cust_id)
       AND (aropen_open)
       AND (aropen_amount > aropen_paid)
       AND (aropen_doctype IN ('I', 'D'))
       AND (aropen_duedate < (CURRENT_DATE - _graceDays)));

  --  Adjust _lateCount if late invoice being paid
    IF ( (NEW.aropen_paid = NEW.aropen_amount)
     AND (NEW.aropen_doctype IN ('I', 'D'))
     AND (NEW.aropen_duedate < (CURRENT_DATE - _graceDays))) THEN
      _lateCount := _lateCount - 1;
    END IF;
  END IF;

-- get the base exchange rate for the doc date
  IF (TG_OP = 'INSERT' AND NEW.aropen_curr_rate IS NULL) THEN
    SELECT curr_rate INTO _currrate
      FROM curr_rate
    WHERE ( (NEW.aropen_curr_id=curr_id)
      AND ( NEW.aropen_docdate BETWEEN curr_effective
                                   AND curr_expires) );
    IF (FOUND) THEN
      NEW.aropen_curr_rate := _currrate;
    ELSE
      RAISE EXCEPTION 'Currency exchange rate not found';
    END IF;
  END IF;

--  Close this aropen if it is paid
  IF (NEW.aropen_paid = NEW.aropen_amount) THEN
    NEW.aropen_open=FALSE;

--  Remove any aropenalloc regards that reference this aropen item
    DELETE FROM aropenalloc WHERE (aropenalloc_aropen_id=NEW.aropen_id);
  END IF;

  IF (TG_OP = 'INSERT') THEN
    IF (NEW.aropen_open=FALSE)
    AND (NEW.aropen_closedate IS NULL) THEN
      NEW.aropen_closedate=current_date;
    END IF;
  END IF;

  IF (TG_OP = 'UPDATE') THEN
    IF ((OLD.aropen_open=TRUE)
    AND (NEW.aropen_open=FALSE)
    AND (NEW.aropen_closedate IS NULL)) THEN
      NEW.aropen_closedate=current_date;
    END IF;
  END IF;

--  Only check if the customer in question has a non-zero Credit Limit
  SELECT cust_id, cust_creditlmt, cust_creditstatus,
         cust_autoupdatestatus, cust_autoholdorders INTO _p
  FROM custinfo
  WHERE (cust_id=NEW.aropen_cust_id);
  IF (_p.cust_creditlmt > 0) THEN
    _checkLimit := true;

    SELECT COALESCE(SUM( CASE WHEN (aropen_doctype IN ('I', 'D')) THEN (aropen_amount - aropen_paid)
                     ELSE ((aropen_amount - aropen_paid) * -1)
                END ), 0.0) INTO _openAmount
    FROM aropen AS current
    WHERE ( (current.aropen_cust_id=NEW.aropen_cust_id)
     AND (current.aropen_open)
     AND (current.aropen_id <> NEW.aropen_id) );

--  Add in the value of the current aropen item
    IF (NEW.aropen_doctype IN ('I', 'D')) THEN
      _openAmount := (_openAmount + (NEW.aropen_amount - NEW.aropen_paid));
    ELSE
      _openAmount := (_openAmount - (NEW.aropen_amount - NEW.aropen_paid));
    END IF;
  ELSE
    _openAmount := 0;
  END IF;

  IF (_checkLimit OR _checkLate) THEN
--  Handle a Customer that is going under its credit limit
    IF ((_p.cust_creditlmt >= _openAmount) AND (_lateCount <= 0)) THEN

--  Handle the Customer Status
      IF ( (_p.cust_autoupdatestatus) AND (_p.cust_creditstatus='W') ) THEN
        UPDATE custinfo
        SET cust_creditstatus='G'
        WHERE (cust_id=NEW.aropen_cust_id);
      END IF;

--  Handle the open Sales Orders
      IF (_p.cust_autoholdorders) THEN
        UPDATE cohead
        SET cohead_holdtype='N'
        FROM coitem
        WHERE ( (coitem_cohead_id=cohead_id)
         AND (cohead_holdtype='C')
         AND (coitem_status='O')
         AND (cohead_cust_id=_p.cust_id) );
      END IF;

--  Handle a Customer that is going over its credit limit
    ELSIF ((_p.cust_creditlmt < _openAmount) OR (_lateCount > 0)) THEN

--  Handle the Customer Status
      IF ( (_p.cust_autoupdatestatus) AND (_p.cust_creditstatus = 'G') ) THEN
        UPDATE custinfo
        SET cust_creditstatus='W'
        WHERE (cust_id=NEW.aropen_cust_id);
      END IF;

--  Handle the open Sales Orders
      IF (_p.cust_autoholdorders) THEN
        UPDATE cohead
        SET cohead_holdtype='C'
        FROM coitem
        WHERE ( (coitem_cohead_id=cohead_id)
         AND (cohead_holdtype='N')
         AND (coitem_status='O')
         AND (cohead_cust_id=_p.cust_id) );
      END IF;

    END IF;

  END IF;

  RETURN NEW;

END;
$$;


ALTER FUNCTION public._aropentrigger() OWNER TO admin;

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

CREATE FUNCTION _bomheadtrigger() 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
  _revid INTEGER;
  _check TEXT;
  _cmnttypeid INTEGER;
BEGIN
-- Privilege Checks
  IF (NOT checkPrivilege('MaintainBOMs')) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Bills of Material.';
  END IF;

--  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, 'BMH', NEW.bomhead_id, 'Created');

      ELSIF (TG_OP = 'UPDATE') THEN
        IF (OLD.bomhead_revision <> NEW.bomhead_revision) THEN
          PERFORM postComment(_cmnttypeid, 'BMH', NEW.bomhead_id, 'Revision Updated from '||
                                       OLD.bomhead_revision||' to '||NEW.bomhead_revision);
        ELSE                                      
          PERFORM postComment(_cmnttypeid, 'BMH', NEW.bomhead_id, 'Updated');
        END IF;  
      END IF;
    END IF;  

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._bomheadtrigger() OWNER TO admin;

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

CREATE FUNCTION _bomitemaftertrigger() 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 ( SELECT fetchMetricBool('ItemChangeLog') ) THEN
    IF (TG_OP = 'INSERT') THEN
      PERFORM postComment('ChangeLog', 'BMI', NEW.bomitem_id, ('Created BOM Item Sequence ' || NEW.bomitem_seqnumber::TEXT));

    ELSIF (TG_OP = 'UPDATE') THEN
      IF (NEW.bomitem_effective <> OLD.bomitem_effective) THEN
        PERFORM postComment( 'ChangeLog', 'BMI', NEW.bomitem_id,
                             ( 'Effective Date Changed from ' || formatDate(OLD.bomitem_effective, 'Always') ||
                               ' to ' || formatDate(NEW.bomitem_effective, 'Always' ) ) );
      END IF;

      IF (NEW.bomitem_expires <> OLD.bomitem_expires) THEN
        PERFORM postComment( 'ChangeLog', 'BMI', NEW.bomitem_id,
                             ( 'Expiration Date Changed from ' || formatDate(OLD.bomitem_expires, 'Never') ||
                               ' to ' || formatDate(NEW.bomitem_expires, 'Never' ) ) );
      END IF;

      IF (NEW.bomitem_qtyfxd <> OLD.bomitem_qtyfxd) THEN
        PERFORM postComment( 'ChangeLog', 'BMI', NEW.bomitem_id,
                             ( 'Fixed Qty. Changed from ' || formatQtyPer(OLD.bomitem_qtyfxd) ||
                               ' to ' || formatQtyPer(NEW.bomitem_qtyfxd ) ) );
      END IF;

      IF (NEW.bomitem_qtyper <> OLD.bomitem_qtyper) THEN
        PERFORM postComment( 'ChangeLog', 'BMI', NEW.bomitem_id,
                             ( 'Qty. Per Changed from ' || formatQtyPer(OLD.bomitem_qtyper) ||
                               ' to ' || formatQtyPer(NEW.bomitem_qtyper ) ) );
      END IF;

      IF (NEW.bomitem_scrap <> OLD.bomitem_scrap) THEN
        PERFORM postComment( 'ChangeLog', 'BMI', NEW.bomitem_id,
                             ( 'Scrap % Changed from ' || formatPrcnt(OLD.bomitem_scrap) ||
                               ' to ' || formatPrcnt(NEW.bomitem_scrap ) ) );
      END IF;

      IF (NEW.bomitem_issuemethod <> OLD.bomitem_issuemethod) THEN
        PERFORM postComment( 'ChangeLog', 'BMI', NEW.bomitem_id,
                             ( 'Issue Method Changed from ' || (CASE WHEN(OLD.bomitem_issuemethod='S') THEN 'Push'
                                                                     WHEN(OLD.bomitem_issuemethod='L') THEN 'Pull'
                                                                     WHEN(OLD.bomitem_issuemethod='M') THEN 'Mixed'
                                                                     ELSE OLD.bomitem_issuemethod END) ||
                               ' to ' || (CASE WHEN(NEW.bomitem_issuemethod='S') THEN 'Push'
                                               WHEN(NEW.bomitem_issuemethod='L') THEN 'Pull'
                                               WHEN(NEW.bomitem_issuemethod='M') THEN 'Mixed'
                                               ELSE NEW.bomitem_issuemethod END) ) );
      END IF;

      IF (NEW.bomitem_ecn <> OLD.bomitem_ecn) THEN
        PERFORM postComment( 'ChangeLog', 'BMI', NEW.bomitem_id,
                             ( 'ECN Changed from ' || OLD.bomitem_ecn ||
                               ' to ' || NEW.bomitem_ecn ) );
      END IF;

      IF (OLD.bomitem_createwo <> NEW.bomitem_createwo) THEN
        IF (NEW.bomitem_createwo) THEN
          PERFORM postComment('ChangeLog', 'BMI', NEW.bomitem_id, 'Create Child W/O activated');
        ELSE
          PERFORM postComment('ChangeLog', 'BMI', NEW.bomitem_id, 'Create Child W/O deactivated');
        END IF;
      END IF;

      IF (OLD.bomitem_issuewo <> NEW.bomitem_issuewo) THEN
        IF (NEW.bomitem_issuewo) THEN
          PERFORM postComment('ChangeLog', 'BMI', NEW.bomitem_id, 'Issue Child W/O activated');
        ELSE
          PERFORM postComment('ChangeLog', 'BMI', NEW.bomitem_id, 'Issue Child W/O deactivated');
        END IF;
      END IF;

    END IF;
  END IF;

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

    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._bomitemaftertrigger() OWNER TO admin;

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

CREATE FUNCTION _bomitembeforedeletetrigger() 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 comment
   WHERE ( (comment_source='BMI')
     AND   (comment_source_id=OLD.bomitem_id) );

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._bomitembeforedeletetrigger() OWNER TO admin;

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

CREATE FUNCTION _bomitembeforetrigger() 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
  _bomworksetid INTEGER;
  _bomworkid INTEGER;
  _seqNumber INTEGER;
  _parentItem RECORD;
  _bomheadid INTEGER;
BEGIN

  -- Privilege Checks
  IF (NOT checkPrivilege('MaintainBOMs')) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Bills of Material.';
  END IF;

  -- Cache Parent Item
   SELECT * INTO _parentItem
   FROM item
   WHERE (item_id=NEW.bomitem_parent_item_id);

  IF (TG_OP = 'INSERT') THEN
    --  Make sure that the parent and component are not the same
    IF (NEW.bomitem_parent_item_id = NEW.bomitem_item_id) THEN
      RAISE EXCEPTION 'BOM Item Parent and Component Item cannot be the same. [xtuple: createBOMItem, -1]';
    END IF;

    --  Make sure that the parent is not used in the component at some level
    SELECT indentedWhereUsed(NEW.bomitem_parent_item_id) INTO _bomworksetid;
    SELECT bomwork_id INTO _bomworkid
    FROM bomwork
    WHERE ((bomwork_set_id=_bomworksetid)
      AND  (bomwork_item_id=NEW.bomitem_item_id))
    LIMIT 1;
    IF (FOUND) THEN
      PERFORM deleteBOMWorkset(_bomworksetid);
      RAISE EXCEPTION 'BOM Item Parent is used by Component, BOM is recursive. [xtuple: createBOMItem, -2]';
    END IF;

    PERFORM deleteBOMWorkset(_bomworksetid);

    -- Create bomhead record if one does not exist

    SELECT bomhead_id INTO _bomheadid
    FROM bomhead
    WHERE (bomhead_item_id=NEW.bomitem_parent_item_id);

    IF (NOT FOUND) THEN
      INSERT INTO bomhead (bomhead_item_id, bomhead_batchsize, bomhead_rev_id)
                   VALUES (NEW.bomitem_parent_item_id, 1, -1);
    END IF;

    -- Set defaults
    NEW.bomitem_rev_id := COALESCE(NEW.bomitem_rev_id, -1);
    NEW.bomitem_booitem_seq_id := COALESCE(NEW.bomitem_booitem_seq_id, -1);
    NEW.bomitem_schedatwooper := COALESCE(NEW.bomitem_schedatwooper, FALSE);
    IF (NEW.bomitem_seqnumber IS NULL) THEN
      --  Grab the next Sequence Number, if any
      SELECT MAX(bomitem_seqnumber) INTO _seqNumber
      FROM bomitem(NEW.bomitem_parent_item_id,NEW.bomitem_rev_id);
      IF (_seqNumber IS NOT NULL) THEN
        NEW.bomitem_seqnumber := (_seqNumber + 10);
      ELSE
        NEW.bomitem_seqnumber := 10;
      END IF;
    END IF;
  END IF; -- end Insert specific

  IF (TG_OP = 'UPDATE') THEN
    -- Disallow changes that would compromise revision control integrity
    IF (NEW.bomitem_parent_item_id != OLD.bomitem_parent_item_id) THEN
      RAISE EXCEPTION 'Parent Item ID may not be changed.';
    END IF;

    IF (NEW.bomitem_item_id != OLD.bomitem_item_id) THEN
      RAISE EXCEPTION 'Item ID may not be changed.';
    END IF;

    IF ((fetchMetricBool('RevControl')) AND (OLD.bomitem_rev_id > -1)) THEN
      IF (SELECT (rev_status = 'I') FROM rev WHERE (rev_id=OLD.bomitem_rev_id)) THEN
        RAISE EXCEPTION 'Bill of material is Inactive and may not be modified';
      END IF;
    END IF;
  END IF; -- end Update specific

  -- Check for valid UOM
  IF (SELECT (count(*) != 1)
      FROM
             (SELECT uom_id
                FROM item JOIN uom ON (item_inv_uom_id=uom_id)
                WHERE(item_id=NEW.bomitem_item_id)
              UNION 
              SELECT uom_id
                FROM item JOIN itemuomconv ON (itemuomconv_item_id=item_id)
                          JOIN uom ON (itemuomconv_to_uom_id=uom_id),
                     itemuom, uomtype 
               WHERE((itemuomconv_from_uom_id=item_inv_uom_id)
                 AND (item_id=NEW.bomitem_item_id) 
                 AND (itemuom_itemuomconv_id=itemuomconv_id) 
                 AND (uomtype_id=itemuom_uomtype_id) 
                 AND (uomtype_name='MaterialIssue'))
              UNION 
              SELECT uom_id
                FROM item JOIN itemuomconv ON (itemuomconv_item_id=item_id)
                          JOIN uom ON (itemuomconv_from_uom_id=uom_id),
                     itemuom, uomtype 
               WHERE((itemuomconv_to_uom_id=item_inv_uom_id)
                 AND (item_id=NEW.bomitem_item_id) 
                 AND (itemuom_itemuomconv_id=itemuomconv_id) 
                 AND (uomtype_id=itemuom_uomtype_id) 
                 AND (uomtype_name='MaterialIssue'))) AS data
        WHERE (uom_id=NEW.bomitem_uom_id)) THEN
    RAISE EXCEPTION 'Unit of Measure Invalid for Material Issue.';
  END IF;

-- Disallow configuration parameters if parent is not a job item
   IF (NEW.bomitem_char_id IS NOT NULL) THEN
     IF (NOT _parentItem.item_config) THEN
       RAISE EXCEPTION 'Configuration characteristics may only be defined for Configured Items';
     END IF;
   END IF;

  -- Kit items must be sold and not kits themselves
  IF (_parentItem.item_type = 'K') THEN
    IF (SELECT (COUNT(item_id) = 0)
          FROM item
         WHERE ((item_id=NEW.bomitem_item_id)
           AND (item_sold)
           AND (item_type != 'K'))) THEN
       RAISE EXCEPTION 'Bill of Material Items for kits must be sold and not kits themselves';
     END IF;
   END IF;

  -- Over ride logic to disallow invalid data
  IF (NEW.bomitem_createwo) THEN
    IF (SELECT (item_type != 'M') 
          FROM item 
         WHERE (item_id=NEW.bomitem_item_id)) THEN
      NEW.bomitem_createwo := FALSE;
    END IF;
    IF (NEW.bomitem_booitem_seq_id = -1) THEN
      NEW.bomitem_schedatwooper := FALSE;
    END IF;
  END IF;

  NEW.bomitem_moddate := COALESCE(NEW.bomitem_moddate, CURRENT_DATE);

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._bomitembeforetrigger() OWNER TO admin;

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

CREATE FUNCTION _bomitemsubtrigger() 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

-- Privilege Checks
  IF (NOT checkPrivilege('MaintainBOMs')) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Bills of Material.';
  END IF;

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

END;
$$;


ALTER FUNCTION public._bomitemsubtrigger() OWNER TO admin;

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

CREATE FUNCTION _cashrcptitemaftertrigger() 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
  _total      NUMERIC;

BEGIN

  -- Checks
  -- Total Over Application Warning
  SELECT (cashrcpt_amount - SUM(COALESCE(cashrcptitem_amount, 0))) INTO _total
  FROM cashrcptitem JOIN cashrcpt ON (cashrcpt_id=cashrcptitem_cashrcpt_id)
  WHERE (cashrcptitem_cashrcpt_id=NEW.cashrcptitem_cashrcpt_id)
  GROUP BY cashrcpt_amount;
  IF (_total < 0.0) THEN
    RAISE WARNING 'Warning -- the Cash Receipt has been over applied.';
  END IF;
  
  RETURN NEW;

END;
$$;


ALTER FUNCTION public._cashrcptitemaftertrigger() OWNER TO admin;

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

CREATE FUNCTION _cashrcptitemtrigger() 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;
  _openAmount NUMERIC;

BEGIN

  -- Checks
  -- Start with Privileges
  IF (TG_OP = 'INSERT') THEN
    SELECT checkPrivilege('MaintainCashReceipts') INTO _check;
    IF NOT (_check) THEN
      RAISE EXCEPTION 'You do not have privileges to add a new Cash Receipt Application.';
    END IF;
  ELSE
    SELECT checkPrivilege('MaintainCashReceipts') INTO _check;
    IF NOT (_check) THEN
      RAISE EXCEPTION 'You do not have privileges to alter a Cash Receipt Application.';
    END IF;
  END IF;

  -- Over Application
  SELECT round(currToCurr(aropen_curr_id, cashrcpt_curr_id,
               aropen_amount - aropen_paid, cashrcpt_distdate) -
               COALESCE((SELECT SUM(cashrcptitem_amount)
                           FROM cashrcptitem, cashrcpt
                           WHERE ((cashrcpt_id=cashrcptitem_cashrcpt_id)
                             AND  (NOT cashrcpt_void)
                             AND  (NOT cashrcpt_posted)
                             AND  (cashrcpt_id != NEW.cashrcptitem_cashrcpt_id)
                             AND  (cashrcptitem_aropen_id=NEW.cashrcptitem_aropen_id))), 0),2) INTO _openAmount
  FROM aropen, cashrcpt
  WHERE ( (aropen_id=NEW.cashrcptitem_aropen_id)
    AND   (cashrcpt_id=NEW.cashrcptitem_cashrcpt_id) );
  IF (NEW.cashrcptitem_amount > _openAmount) THEN
    RAISE EXCEPTION 'You may not apply more than the balance of this item.';
  END IF;


  RETURN NEW;

END;
$$;


ALTER FUNCTION public._cashrcptitemtrigger() OWNER TO admin;

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

CREATE FUNCTION _cashrcptmisctrigger() 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;

BEGIN

  -- Checks
  -- Start with Privileges
  IF (TG_OP = 'INSERT') THEN
    SELECT checkPrivilege('MaintainCashReceipts') INTO _check;
    IF NOT (_check) THEN
      RAISE EXCEPTION 'You do not have privileges to add a new Cash Receipt Misc. Application.';
    END IF;
  ELSE
    SELECT checkPrivilege('MaintainCashReceipts') INTO _check;
    IF NOT (_check) THEN
      RAISE EXCEPTION 'You do not have privileges to alter a Cash Receipt Misc. Application.';
    END IF;
  END IF;

  -- Account is required
  IF (NEW.cashrcptmisc_accnt_id IS NULL) THEN
    RAISE EXCEPTION 'You must supply a valid GL Account.';
  END IF;

  -- Amount is required
  IF (COALESCE(NEW.cashrcptmisc_amount, 0) = 0) THEN
    RAISE EXCEPTION 'You must supply a valid Amount.';
  END IF;

  RETURN NEW;

END;
$$;


ALTER FUNCTION public._cashrcptmisctrigger() OWNER TO admin;

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

CREATE FUNCTION _cashrcpttrigger() 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;
  _currId     INTEGER;
  _bankCurrId INTEGER;
  _currrate   NUMERIC;

BEGIN

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

  -- Currency must be same as Bank Currency
  IF (TG_OP = 'INSERT') THEN
    _currId = COALESCE(NEW.cashrcpt_curr_id, basecurrid());

     --- clear the number from the issue cache
    PERFORM clearNumberIssue('CashRcptNumber', NEW.cashrcpt_number);
  ELSE
    _currId = NEW.cashrcpt_curr_id;
  END IF;

-- get the base exchange rate for the dist date
  IF (NEW.cashrcpt_curr_rate IS NULL) THEN
    SELECT curr_rate INTO _currrate
    FROM curr_rate
    WHERE ( (NEW.cashrcpt_curr_id=curr_id)
      AND ( NEW.cashrcpt_distdate BETWEEN curr_effective 
                                 AND curr_expires) );
    IF (FOUND) THEN
      NEW.cashrcpt_curr_rate := _currrate;
    ELSE
      RAISE EXCEPTION 'Currency exchange rate not found';
    END IF;
  END IF;

  -- Create CashReceiptPosted Event
  IF (TG_OP = 'UPDATE') THEN
    IF (OLD.cashrcpt_posted=FALSE AND NEW.cashrcpt_posted=TRUE) THEN
      PERFORM postEvent('CashReceiptPosted', NULL, NEW.cashrcpt_id,
                        NULL,
                        (cust_number || '-' ||
                        NEW.cashrcpt_docnumber || ' ' ||
                        currConcat(NEW.cashrcpt_curr_id) ||
                        formatMoney(NEW.cashrcpt_amount)),
                        NULL, NULL, NULL, NULL)
      FROM custinfo
      WHERE (cust_id=NEW.cashrcpt_cust_id);
    END IF;
  END IF;

  RETURN NEW;

END;
$$;


ALTER FUNCTION public._cashrcpttrigger() OWNER TO admin;

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

CREATE FUNCTION _ccardtrigger() 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

  New.ccard_lastupdated := current_timestamp;
  New.ccard_last_updated_by_username := getEffectiveXtUser();

  IF (TG_OP = 'UPDATE') THEN
    INSERT INTO ccardaud
         VALUES (nextval('ccardaud_ccardaud_id_seq'), NEW.ccard_id,
                 OLD.ccard_seq, NEW.ccard_seq, OLD.ccard_cust_id, NEW.ccard_cust_id,
                 OLD.ccard_active, NEW.ccard_active, OLD.ccard_name, NEW.ccard_name,
                 OLD.ccard_address1, NEW.ccard_address1, OLD.ccard_address2,
                 NEW.ccard_address2, OLD.ccard_city, NEW.ccard_city, OLD.ccard_state,
                 NEW.ccard_state, OLD.ccard_zip, NEW.ccard_zip, OLD.ccard_country,
                 NEW.ccard_country, OLD.ccard_number, NEW.ccard_number, OLD.ccard_debit,
                 NEW.ccard_debit, OLD.ccard_month_expired, NEW.ccard_month_expired,
                 OLD.ccard_year_expired, NEW.ccard_year_expired, OLD.ccard_type, NEW.ccard_type);
  ELSE
-- We are inserting a record, therefore no old values
    INSERT INTO ccardaud
         VALUES (nextval('ccardaud_ccardaud_id_seq'), NEW.ccard_id,
                 NULL, NEW.ccard_seq, NULL, NEW.ccard_cust_id, NULL,
                 NEW.ccard_active, NULL, NEW.ccard_name, NULL,
                 NEW.ccard_address1, NULL, NEW.ccard_address2, NULL,
                 NEW.ccard_city, NULL, NEW.ccard_state, NULL,
                 NEW.ccard_zip, NULL, NEW.ccard_country, NULL,
                 NEW.ccard_number, NULL, NEW.ccard_debit, NULL,
                 NEW.ccard_month_expired, NULL, NEW.ccard_year_expired, NULL,
                 NEW.ccard_type);
  END IF;

  RETURN NEW;

END;
$$;


ALTER FUNCTION public._ccardtrigger() OWNER TO admin;

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

CREATE FUNCTION _ccpaybeforetrigger() 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
  _cardType TEXT;

BEGIN
  IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
    -- If ccpay_ccard_id is set, we don't care if ccpay_card_type is set,
    -- we want to get the Card Type from ccard.
    IF (NEW.ccpay_ccard_id IS NOT NULL) THEN
      SELECT ccard_type INTO _cardType
      FROM ccard
      WHERE ccard_id = NEW.ccpay_ccard_id;

      IF (_cardType IS NOT NULL) THEN
        NEW.ccpay_card_type = _cardType;
      END IF;
    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._ccpaybeforetrigger() OWNER TO admin;

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

CREATE FUNCTION _charasshistorytrigger() 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
    IF (OLD.charass_target_type = 'INCDT') THEN
      INSERT INTO incdthist
            (incdthist_incdt_id, incdthist_descrip)
      VALUES(OLD.charass_target_id,
             ('Characteristic ' || 
               COALESCE((SELECT char_name 
                           FROM char
                          WHERE (char_id=OLD.charass_char_id)), '')
              || ' Deleted: "' || 
              COALESCE(OLD.charass_value,'')
              || '"') );
    END IF;
    RETURN OLD;
  ELSIF (NEW.charass_target_type = 'INCDT') THEN
    IF (TG_OP = 'INSERT') THEN
      INSERT INTO incdthist
            (incdthist_incdt_id, incdthist_descrip)
      VALUES(NEW.charass_target_id,
             ('Characteristic ' || 
               COALESCE((SELECT char_name 
                           FROM char
                          WHERE (char_id=NEW.charass_char_id)), '')
              || ' Added: "' || 
              COALESCE(NEW.charass_value,'')
              || '"') );
    ELSIF (TG_OP = 'UPDATE') THEN
      IF (COALESCE(NEW.charass_value,'') <> COALESCE(OLD.charass_value,'')) THEN
        INSERT INTO incdthist
              (incdthist_incdt_id, incdthist_descrip)
        VALUES(NEW.charass_target_id,
               ('Characteristic ' || 
                 COALESCE((SELECT char_name 
                             FROM char
                            WHERE (char_id=NEW.charass_char_id)), '')
                || ' Changed: "' || 
                COALESCE(OLD.charass_value,'')
                || '" -> "' ||
                COALESCE(NEW.charass_value,'')
                || '"') );
      END IF;
    END IF;
  END IF;
  RETURN NEW;
END;
$$;


ALTER FUNCTION public._charasshistorytrigger() OWNER TO admin;

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

CREATE FUNCTION _charasstrigger() 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

-- Privilege Checks
   IF (NEW.charass_target_type = 'I' AND NOT checkPrivilege('MaintainItemMasters')) THEN
     RAISE EXCEPTION 'You do not have privileges to maintain Items.';
   END IF;

   IF (NEW.charass_target_type = 'C' AND NOT checkPrivilege('MaintainCustomerMasters')) THEN
     RAISE EXCEPTION 'You do not have privileges to maintain Customers.';
   END IF;

-- Data check
  IF (NEW.charass_char_id IS NULL) THEN
	RAISE EXCEPTION 'You must supply a valid Characteristic ID.';
  END IF;

-- Default Logic
  IF (NEW.charass_default) THEN
    UPDATE charass
    SET charass_default = false 
    WHERE ((charass_target_id=NEW.charass_target_id)
    AND  (charass_target_type=NEW.charass_target_type)
    AND  (charass_char_id=NEW.charass_char_id)
    AND  (charass_id <> NEW.charass_ID));
  END IF;

-- Incident update
  IF (NEW.charass_target_type = 'INCDT') THEN
    UPDATE incdt SET incdt_updated = now() WHERE incdt_id = NEW.charass_target_id;
  END IF;
  
  RETURN NEW;
END;
$$;


ALTER FUNCTION public._charasstrigger() OWNER TO admin;

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

CREATE FUNCTION _charbeforetrigger() 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('MaintainCharacteristics')) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Characteristics.';
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._charbeforetrigger() OWNER TO admin;

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

CREATE FUNCTION _charopttrigger() 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('MaintainCharacteristics')) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Characteristic options.';
  END IF;

  IF (TG_OP = 'UPDATE') THEN
    UPDATE charass SET
      charass_value = NEW.charopt_value
    WHERE ((charass_char_id=NEW.charopt_char_id)
      AND (charass_value=OLD.charopt_value));
  END IF;

  IF (TG_OP = 'DELETE') THEN
    IF (SELECT (count(charass_id) > 0)
        FROM charass
        WHERE ((charass_char_id=OLD.charopt_char_id)
         AND (charass_value=OLD.charopt_value))) THEN
       RAISE EXCEPTION 'This characteristic option value is in use and can not be deleted.';
    END IF;
  END IF;
  
  RETURN NEW;
END;
$$;


ALTER FUNCTION public._charopttrigger() OWNER TO admin;

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

CREATE FUNCTION _charusebeforeupserttrigger() 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
BEGIN
  IF NEW.charuse_target_type != 'LSR' AND
      NOT EXISTS (SELECT 1 FROM source WHERE source_charass = NEW.charuse_target_type) THEN
    RAISE EXCEPTION 'Cannot use Characteristic % with undefined target type % [ xtuple: charuse, -1, %, % ]',
                    NEW.charuse_char_id, NEW.charuse_target_type,
                    NEW.charuse_char_id, NEW.charuse_target_type;
  END IF;
  IF (TG_OP = 'INSERT') THEN
    NEW.charuse_created = CURRENT_TIMESTAMP;
  ELSE
    NEW.charuse_created = OLD.charuse_created;
  END IF;
  NEW.charuse_last_modified = CURRENT_TIMESTAMP;

  RETURN NEW;
END
$$;


ALTER FUNCTION public._charusebeforeupserttrigger() OWNER TO admin;

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

CREATE FUNCTION _checkheadbeforetrigger() 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 
  _amount NUMERIC;
  _currrate NUMERIC;

BEGIN

-- get the base exchange rate for the check date
  IF (TG_OP = 'INSERT' AND NEW.checkhead_curr_rate IS NULL) THEN
    SELECT curr_rate INTO _currrate
    FROM curr_rate
    WHERE ( (NEW.checkhead_curr_id=curr_id)
      AND ( NEW.checkhead_checkdate BETWEEN curr_effective 
                                   AND curr_expires) );
    IF (FOUND) THEN
      NEW.checkhead_curr_rate := _currrate;
    ELSE
      RAISE EXCEPTION 'Currency exchange rate not found';
    END IF;
  END IF;

  IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
    IF (NOT EXISTS (SELECT checkrecip_id
		    FROM checkrecip
		    WHERE ((checkrecip_type=NEW.checkhead_recip_type)
		      AND  (checkrecip_id=NEW.checkhead_recip_id)) )) THEN
      RAISE EXCEPTION 'Cannot verify recipient for check % (type %  id %)',
		      NEW.checkhead_number, NEW.checkhead_recip_type,
		      NEW.checkhead_recip_id;
    END IF;

    IF (NEW.checkhead_journalnumber IS NOT NULL
        AND NOT EXISTS (SELECT jrnluse_number
			FROM jrnluse
			WHERE (jrnluse_number=NEW.checkhead_journalnumber))
	) THEN
      RAISE EXCEPTION 'Journal Number % does not exist and cannot be used for check %.',
		      NEW.checkhead_journalnumber, NEW.checkhead_number;
    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._checkheadbeforetrigger() OWNER TO admin;

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

CREATE FUNCTION _cmheadbeforetrigger() 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;
  _id INTEGER;
BEGIN
  -- Checks
  -- Start with privileges
  SELECT checkPrivilege('MaintainCreditMemos') INTO _check;
  IF ( (TG_OP = 'INSERT') OR (TG_OP = 'DELETE') ) THEN
    IF NOT (_check) THEN
      RAISE EXCEPTION 'You do not have privileges to maintain Credit Memos.';
    END IF;
  END IF;
  IF (TG_OP = 'UPDATE') THEN
    IF ((OLD.cmhead_printed = NEW.cmhead_printed) AND NOT (_check) ) THEN
      RAISE EXCEPTION 'You do not have privileges to maintain Credit Memos.';
    END IF;
  END IF;

  IF (TG_OP = 'DELETE') THEN
    DELETE FROM cmheadtax
    WHERE (taxhist_parent_id=OLD.cmhead_id);

    RETURN OLD;
  END IF;

  IF ( (NEW.cmhead_number IS NULL) OR (LENGTH(NEW.cmhead_number) = 0) ) THEN
    RAISE EXCEPTION 'You must enter a valid Memo # for this Credit Memo.';
  END IF;

  IF (TG_OP = 'INSERT') THEN
    SELECT cmhead_id INTO _id
    FROM cmhead
    WHERE (cmhead_number=NEW.cmhead_number);
    IF (FOUND) THEN
      RAISE EXCEPTION 'The Memo # is already in use.';
    END IF;

    IF (fetchMetricText('CMNumberGeneration') IN ('A','O')) THEN
      --- clear the number from the issue cache
      PERFORM clearNumberIssue('CmNumber', NEW.cmhead_number);
    ELSIF (fetchMetricText('CMNumberGeneration') = 'S') THEN
      --- clear the number from the issue cache
      PERFORM clearNumberIssue('SoNumber', NEW.cmhead_number);
    END IF;
  END IF;

  IF (NEW.cmhead_cust_id IS NOT NULL) THEN
    SELECT cust_id INTO _id
    FROM custinfo
    WHERE (cust_id=NEW.cmhead_cust_id);
    IF (NOT FOUND) THEN
      RAISE EXCEPTION 'You must enter a valid Customer # for this Credit Memo.';
    END IF;
  END IF;

  IF ( (NEW.cmhead_misc > 0) AND (NEW.cmhead_misc_accnt_id = -1) ) THEN
    RAISE EXCEPTION 'You may not enter a Misc. Charge without indicating the G/L Sales Account.';
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._cmheadbeforetrigger() OWNER TO admin;

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

CREATE FUNCTION _cmheadtrigger() 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
    -- If this was created by a return, then reset the return
    IF (OLD.cmhead_rahead_id IS NOT NULL) THEN
      UPDATE rahead SET
        rahead_headcredited=false
      WHERE (rahead_id=OLD.cmhead_rahead_id);
      DELETE FROM rahist
      WHERE ((rahist_rahead_id=OLD.cmhead_rahead_id)
      AND (rahist_source='CM')
      AND (rahist_source_id=OLD.cmhead_id));
    END IF;
    RETURN OLD;
  END IF;

-- Insert new row
  IF (TG_OP = 'INSERT') THEN

  -- Calculate Freight Tax
    IF (NEW.cmhead_freight <> 0) THEN
      PERFORM calculateTaxHist( 'cmheadtax',
                                NEW.cmhead_id,
                                NEW.cmhead_taxzone_id,
                                getFreightTaxtypeId(),
                                NEW.cmhead_docdate,
                                NEW.cmhead_curr_id,
                                NEW.cmhead_freight * -1 );
    END IF;
  END IF;

-- Update row
  IF (TG_OP = 'UPDATE') THEN

    IF ( (NEW.cmhead_freight <> OLD.cmhead_freight) OR
         (COALESCE(NEW.cmhead_taxzone_id,-1) <> COALESCE(OLD.cmhead_taxzone_id,-1)) OR
         (NEW.cmhead_docdate <> OLD.cmhead_docdate) OR
         (NEW.cmhead_curr_id <> OLD.cmhead_curr_id) ) THEN
  -- Calculate cmhead Tax
      PERFORM calculateTaxHist( 'cmheadtax',
                                NEW.cmhead_id,
                                NEW.cmhead_taxzone_id,
                                getFreightTaxtypeId(),
                                NEW.cmhead_docdate,
                                NEW.cmhead_curr_id,
                                NEW.cmhead_freight * -1 );
    END IF;

    IF ( (COALESCE(NEW.cmhead_taxzone_id,-1) <> COALESCE(OLD.cmhead_taxzone_id,-1)) OR
         (NEW.cmhead_docdate <> OLD.cmhead_docdate) OR
         (NEW.cmhead_curr_id <> OLD.cmhead_curr_id) ) THEN
  -- Calculate cmitem Tax
      IF (COALESCE(NEW.cmhead_taxzone_id,-1) <> COALESCE(OLD.cmhead_taxzone_id,-1)) THEN
    -- Cmitem trigger will calculate tax
        UPDATE cmitem SET cmitem_taxtype_id=getItemTaxType(itemsite_item_id,NEW.cmhead_taxzone_id)
        FROM itemsite 
        WHERE ((itemsite_id=cmitem_itemsite_id)
          AND (cmitem_cmhead_id=NEW.cmhead_id));
      ELSE
        PERFORM calculateTaxHist( 'cmitemtax',
                                  cmitem_id,
                                  NEW.cmhead_taxzone_id,
                                  cmitem_taxtype_id,
                                  NEW.cmhead_docdate,
                                  NEW.cmhead_curr_id,
                                  (cmitem_qtycredit * cmitem_qty_invuomratio) *
                                  (cmitem_unitprice / cmitem_price_invuomratio) * -1)
        FROM cmitem
        WHERE (cmitem_cmhead_id = NEW.cmhead_id);
      END IF;
    END IF;

  END IF;


  RETURN NEW;
END;
$$;


ALTER FUNCTION public._cmheadtrigger() OWNER TO admin;

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

CREATE FUNCTION _cmitembeforetrigger() 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;
  _id INTEGER;
BEGIN
  -- Checks
  -- Start with privileges
  SELECT checkPrivilege('MaintainCreditMemos') INTO _check;
  IF NOT (_check) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Credit Memos.';
  END IF;

  IF (TG_OP = 'DELETE') THEN
    DELETE FROM cmitemtax
    WHERE (taxhist_parent_id=OLD.cmitem_id);

    RETURN OLD;
  END IF;

  IF (TG_OP = 'INSERT') THEN
    IF ( (NEW.cmitem_qtycredit IS NULL) OR (NEW.cmitem_qtycredit = 0) ) THEN
      RAISE EXCEPTION 'Quantity to Credit must be greater than zero.';
    END IF;
    SELECT cmitem_id INTO _id
    FROM cmitem
    WHERE ( (cmitem_cmhead_id=NEW.cmitem_cmhead_id) AND (cmitem_linenumber=NEW.cmitem_linenumber) );
    IF (FOUND) THEN
      RAISE EXCEPTION 'The Memo Line Number is already in use.';
    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._cmitembeforetrigger() OWNER TO admin;

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

CREATE FUNCTION _cmitemtrigger() 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
  _ext NUMERIC;
  _r RECORD;

BEGIN
  IF (TG_OP = 'DELETE') THEN

--  If this was created by a return, reset return values
    IF (OLD.cmitem_raitem_id) IS NOT NULL THEN
      _ext := ROUND((OLD.cmitem_qtycredit * OLD.cmitem_qty_invuomratio) *  (OLD.cmitem_unitprice / OLD.cmitem_price_invuomratio),2);
      UPDATE raitem SET
        raitem_status = 'O',
        raitem_qtycredited = raitem_qtycredited-OLD.cmitem_qtycredit,
        raitem_amtcredited = raitem_amtcredited-_ext
      WHERE (raitem_id=OLD.cmitem_raitem_id);
    END IF;
    RETURN OLD;
  END IF;

-- Cache Credit Memo Head
  SELECT * INTO _r
  FROM cmhead
  WHERE (cmhead_id=NEW.cmitem_cmhead_id);
  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Credit Memo head not found';
  END IF;

-- Insert new row
  IF (TG_OP = 'INSERT') THEN

  -- Calculate Tax
      PERFORM calculateTaxHist( 'cmitemtax',
                                NEW.cmitem_id,
                                COALESCE(_r.cmhead_taxzone_id, -1),
                                NEW.cmitem_taxtype_id,
                                COALESCE(_r.cmhead_docdate, CURRENT_DATE),
                                COALESCE(_r.cmhead_curr_id, -1),
                                (NEW.cmitem_qtycredit * NEW.cmitem_qty_invuomratio) *
                                (NEW.cmitem_unitprice / NEW.cmitem_price_invuomratio) * -1);
  END IF;

-- Update row
  IF (TG_OP = 'UPDATE') THEN

  -- Calculate Tax
    IF ( (NEW.cmitem_qtycredit <> OLD.cmitem_qtycredit) OR
         (NEW.cmitem_qty_invuomratio <> OLD.cmitem_qty_invuomratio) OR
         (NEW.cmitem_unitprice <> OLD.cmitem_unitprice) OR
         (NEW.cmitem_price_invuomratio <> OLD.cmitem_price_invuomratio) OR
         (COALESCE(NEW.cmitem_taxtype_id, -1) <> COALESCE(OLD.cmitem_taxtype_id, -1)) ) THEN
      PERFORM calculateTaxHist( 'cmitemtax',
                                NEW.cmitem_id,
                                COALESCE(_r.cmhead_taxzone_id, -1),
                                NEW.cmitem_taxtype_id,
                                COALESCE(_r.cmhead_docdate, CURRENT_DATE),
                                COALESCE(_r.cmhead_curr_id, -1),
                                (NEW.cmitem_qtycredit * NEW.cmitem_qty_invuomratio) *
                                (NEW.cmitem_unitprice / NEW.cmitem_price_invuomratio) * -1);
    END IF;
  END IF;


  RETURN NEW;
END;
$$;


ALTER FUNCTION public._cmitemtrigger() OWNER TO admin;

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

CREATE FUNCTION _cntctafterdeletetrigger() 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 = 'CNTCT'
    AND charass_target_id = OLD.cntct_id;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._cntctafterdeletetrigger() OWNER TO admin;

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

CREATE FUNCTION _cntcttrigger() 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.cntct_name := formatCntctName(NULL, NEW.cntct_first_name, NEW.cntct_middle, NEW.cntct_last_name, NEW.cntct_suffix);
  NEW.cntct_email := lower(NEW.cntct_email);

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

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._cntcttrigger() OWNER TO admin;

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

CREATE FUNCTION _cntcttriggerafter() 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
  _cntctemlid INTEGER;
  _rows INTEGER;
BEGIN
  IF (TG_OP = 'INSERT') THEN
    IF(length(coalesce(NEW.cntct_email,'')) > 0) THEN
      INSERT INTO cntcteml (
        cntcteml_cntct_id, cntcteml_primary, cntcteml_email )
      VALUES (
        NEW.cntct_id, true, NEW.cntct_email );
    END IF;
    PERFORM postComment('ChangeLog', 'T', NEW.cntct_id,
                        ('Created by ' || getEffectiveXtUser()));
  ELSIF (TG_OP = 'UPDATE') THEN
    IF (OLD.cntct_email != NEW.cntct_email) THEN
      SELECT cntcteml_id INTO _cntctemlid
      FROM cntcteml
      WHERE ((cntcteml_cntct_id=NEW.cntct_id)
        AND (cntcteml_email=NEW.cntct_email));

      GET DIAGNOSTICS _rows = ROW_COUNT;
      IF (_rows = 0) THEN
        UPDATE cntcteml SET
          cntcteml_primary=false
        WHERE ((cntcteml_cntct_id=NEW.cntct_id)
         AND (cntcteml_primary=true));

        INSERT INTO cntcteml (
          cntcteml_cntct_id, cntcteml_primary, cntcteml_email )
        VALUES (
          NEW.cntct_id, true, NEW.cntct_email );
      ELSE
        UPDATE cntcteml SET
          cntcteml_primary=false
        WHERE ((cntcteml_cntct_id=NEW.cntct_id)
         AND (cntcteml_primary=true));

        UPDATE cntcteml SET
          cntcteml_primary=true
        WHERE (cntcteml_id=_cntctemlid);
      END IF;
    END IF;
  ELSIF (TG_OP = 'DELETE') THEN
      DELETE FROM comment
       WHERE (comment_source_id=OLD.cntct_id AND comment_source = 'T');
      DELETE FROM docass
       WHERE (docass_source_id=OLD.cntct_id AND docass_source_type = 'T')
          OR (docass_target_id=OLD.cntct_id AND docass_target_type = 'T');

      RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._cntcttriggerafter() OWNER TO admin;

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

CREATE FUNCTION _cntcttriggerbeforedelete() 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
    DELETE FROM cntctaddr WHERE cntctaddr_cntct_id=OLD.cntct_id;
    DELETE FROM cntctdata WHERE cntctdata_cntct_id=OLD.cntct_id;
    DELETE FROM cntcteml  WHERE cntcteml_cntct_id=OLD.cntct_id;
    DELETE FROM docass WHERE docass_source_id = OLD.cntct_id AND docass_source_type = 'T';
    DELETE FROM docass WHERE docass_target_id = OLD.cntct_id AND docass_target_type = 'T';

    -- these have denormalized cntct info so it should be ok to update them
    UPDATE cohead SET cohead_billto_cntct_id=NULL
     WHERE cohead_billto_cntct_id=OLD.cntct_id;
    UPDATE cohead SET cohead_shipto_cntct_id=NULL
     WHERE cohead_shipto_cntct_id=OLD.cntct_id;

    UPDATE pohead SET pohead_vend_cntct_id=NULL
     WHERE pohead_vend_cntct_id=OLD.cntct_id;
    UPDATE pohead SET pohead_shipto_cntct_id=NULL
     WHERE pohead_shipto_cntct_id=OLD.cntct_id;

    UPDATE quhead SET quhead_billto_cntct_id=NULL
     WHERE quhead_billto_cntct_id=OLD.cntct_id;
    UPDATE quhead SET quhead_shipto_cntct_id=NULL
     WHERE quhead_shipto_cntct_id=OLD.cntct_id;

    IF (fetchMetricBool('MultiWhs')) THEN
      UPDATE tohead SET tohead_destcntct_id=NULL
       WHERE tohead_destcntct_id=OLD.cntct_id;
      UPDATE tohead SET tohead_srccntct_id=NULL
       WHERE tohead_srccntct_id=OLD.cntct_id;
    END IF;

  END IF;
  RETURN OLD;
END;
$$;


ALTER FUNCTION public._cntcttriggerbeforedelete() OWNER TO admin;

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

CREATE FUNCTION _cntsliptrigger() 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;
  _comments TEXT;
  _temp TEXT;

BEGIN
  IF (TG_OP = 'DELETE') THEN
    SELECT itemsite_loccntrl, itemsite_controlmethod,
           cntslip_posted, cntslip_lotserial, cntslip_comments,
           cntslip_number, cntslip_qty INTO _p
      FROM cntslip, invcnt, itemsite
     WHERE ( (cntslip_cnttag_id=invcnt_id)
       AND   (invcnt_itemsite_id=itemsite_id)
       AND   (cntslip_id=OLD.cntslip_id) );

    IF(_p.cntslip_posted) THEN
      SELECT ( '
Count Slip #' || _p.cntslip_number ||
             ' deleted ' || formatQty(_p.cntslip_qty) ) INTO _comments;

--  Add the Location name if the itemsite is MLC
      IF (_p.itemsite_loccntrl) THEN
        SELECT ( ', Location:' || location_name ) INTO _temp
          FROM location, cntslip
         WHERE ( (cntslip_location_id=location_id)
           AND   (cntslip_id=OLD.cntslip_id) );

        _comments := (_comments || _temp);
      END IF;

--  Add the Lot/Serial if the itemsite is Lot or Serial controlled
      IF (_p.itemsite_controlmethod = 'L') THEN
        _comments := (_comments || ( ', Lot #:' || _p.cntslip_lotserial));
      ELSIF (_p.itemsite_controlmethod = 'S') THEN
        _comments := (_comments || ( ', Serial #:' || _p.cntslip_lotserial));
      END IF;

      _comments := (_comments || ' ' || _p.cntslip_comments);

      UPDATE invcnt
         SET invcnt_qoh_after = ( COALESCE(invcnt_qoh_after, 0) - cntslip_qty),
             invcnt_comments = (invcnt_comments || _comments)
        FROM cntslip
       WHERE ( (cntslip_cnttag_id=invcnt_id)
         AND   (NOT invcnt_posted)
         AND   (cntslip_id=OLD.cntslip_id) );

    END IF;

    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._cntsliptrigger() OWNER TO admin;

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

CREATE FUNCTION _cobillbeforetrigger() 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 cobilltax
    WHERE (taxhist_parent_id=OLD.cobill_id);

    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._cobillbeforetrigger() OWNER TO admin;

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

CREATE FUNCTION _cobilltrigger() 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 Billing Head
  SELECT * INTO _r
  FROM cobmisc
  WHERE (cobmisc_id=NEW.cobill_cobmisc_id);
  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Billing head not found';
  END IF;

-- Insert new row
  IF (TG_OP = 'INSERT') THEN

  -- Calculate Tax
      PERFORM calculateTaxHist( 'cobilltax',
                                NEW.cobill_id,
                                COALESCE(_r.cobmisc_taxzone_id, -1),
                                NEW.cobill_taxtype_id,
                                COALESCE(_r.cobmisc_shipdate, CURRENT_DATE),
                                COALESCE(_r.cobmisc_curr_id, -1),
                                (NEW.cobill_qty * coitem_qty_invuomratio) *
                                (coitem_price / coitem_price_invuomratio) )
      FROM coitem
      WHERE (coitem_id=NEW.cobill_coitem_id);
  END IF;

-- Update row
  IF (TG_OP = 'UPDATE') THEN

  -- Calculate Tax
    IF ( (NEW.cobill_qty <> OLD.cobill_qty) OR
         (NEW.cobill_taxtype_id <> OLD.cobill_taxtype_id) ) THEN
      PERFORM calculateTaxHist( 'cobilltax',
                                NEW.cobill_id,
                                COALESCE(_r.cobmisc_taxzone_id, -1),
                                NEW.cobill_taxtype_id,
                                COALESCE(_r.cobmisc_shipdate, CURRENT_DATE),
                                COALESCE(_r.cobmisc_curr_id, -1),
                                (NEW.cobill_qty * coitem_qty_invuomratio) *
                                (coitem_price / coitem_price_invuomratio) )
      FROM coitem
      WHERE (coitem_id=NEW.cobill_coitem_id);
    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._cobilltrigger() OWNER TO admin;

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

CREATE FUNCTION _cobmiscbeforetrigger() 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 cobmisctax
    WHERE (taxhist_parent_id=OLD.cobmisc_id);

    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._cobmiscbeforetrigger() OWNER TO admin;

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

CREATE FUNCTION _cobmisctrigger() 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
    -- Something can go here
    RETURN OLD;
  END IF;

-- Insert new row
  IF (TG_OP = 'INSERT') THEN

  -- Calculate Freight Tax
    IF (NEW.cobmisc_freight <> 0) THEN
      PERFORM calculateTaxHist( 'cobmisctax',
                                NEW.cobmisc_id,
                                NEW.cobmisc_taxzone_id,
                                getFreightTaxtypeId(),
                                NEW.cobmisc_invcdate,
                                NEW.cobmisc_curr_id,
                                NEW.cobmisc_freight );
    END IF;
  END IF;

-- Update row
  IF (TG_OP = 'UPDATE') THEN

  -- Calculate Tax
    IF (COALESCE(NEW.cobmisc_taxzone_id,-1) <> COALESCE(OLD.cobmisc_taxzone_id,-1)) THEN
      UPDATE cobill SET cobill_taxtype_id=getItemTaxType(itemsite_item_id,NEW.cobmisc_taxzone_id)
      FROM coitem
        JOIN itemsite ON (coitem_itemsite_id=itemsite_id)
      WHERE ((coitem_id=cobill_coitem_id)
       AND (cobill_cobmisc_id=NEW.cobmisc_id));
    END IF;
    
    IF ( (NEW.cobmisc_freight <> OLD.cobmisc_freight) OR
         (COALESCE(NEW.cobmisc_taxzone_id,-1) <> COALESCE(OLD.cobmisc_taxzone_id,-1)) OR
         (NEW.cobmisc_invcdate <> OLD.cobmisc_invcdate) OR
         (NEW.cobmisc_curr_id <> OLD.cobmisc_curr_id) ) THEN
      PERFORM calculateTaxHist( 'cobmisctax',
                                NEW.cobmisc_id,
                                NEW.cobmisc_taxzone_id,
                                getFreightTaxtypeId(),
                                NEW.cobmisc_invcdate,
                                NEW.cobmisc_curr_id,
                                NEW.cobmisc_freight );
      PERFORM calculateTaxHist( 'cobilltax',
                                cobill_id,
                                NEW.cobmisc_taxzone_id,
                                cobill_taxtype_id,
                                NEW.cobmisc_invcdate,
                                NEW.cobmisc_curr_id,
                                (cobill_qty * coitem_qty_invuomratio) *
                                (coitem_price / coitem_price_invuomratio) )
      FROM cobill JOIN coitem ON (coitem_id = cobill_coitem_id)
      WHERE (cobill_cobmisc_id = NEW.cobmisc_id);
    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._cobmisctrigger() OWNER TO admin;

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

CREATE FUNCTION _coheadafterdeletetrigger() 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 = 'SO'
    AND charass_target_id = OLD.cohead_id;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._coheadafterdeletetrigger() OWNER TO admin;

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

CREATE FUNCTION _coitembeforeimptaxtypedeftrigger() 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
  _itemid INTEGER := 0;
  _cohead RECORD;
BEGIN

  IF (NEW.coitem_taxtype_id IS NULL) THEN
    -- Get the cohead_taxzone_id and cohead_imported
    SELECT cohead_taxzone_id, cohead_imported INTO _cohead
    FROM cohead
    WHERE (cohead_id = NEW.coitem_cohead_id);

    IF (_cohead.cohead_imported) THEN
      -- Get the item_id
      SELECT item_id INTO _itemid
      FROM item
      LEFT JOIN itemsite ON item.item_id = itemsite.itemsite_item_id
      WHERE (itemsite_id = NEW.coitem_itemsite_id);

      -- Set coitem_taxtype_id default
      NEW.coitem_taxtype_id = getItemTaxType(_itemid, _cohead.cohead_taxzone_id);
    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._coitembeforeimptaxtypedeftrigger() OWNER TO admin;

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

CREATE FUNCTION _commenttrigger() 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 (NEW.comment_cmnttype_id IS NULL) THEN
	RAISE EXCEPTION 'You must supply a valid Comment Type ID.';
  ELSIF (NEW.comment_source = 'INCDT') THEN
    UPDATE incdt SET incdt_updated = now() WHERE incdt_id = NEW.comment_source_id;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._commenttrigger() OWNER TO admin;

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

CREATE FUNCTION _companytrigger() 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
  _used	BOOLEAN := false;

BEGIN
  IF (NEW.company_external AND NOT OLD.company_external) THEN
    IF EXISTS(SELECT accnt_id
              FROM accnt, company, (
                  SELECT DISTINCT apaccnt_ap_accnt_id AS test_accnt_id FROM apaccnt
                  UNION SELECT DISTINCT apaccnt_discount_accnt_id FROM apaccnt
                  UNION SELECT DISTINCT apaccnt_prepaid_accnt_id FROM apaccnt
                  UNION SELECT DISTINCT apopen_accnt_id FROM apopen
                  UNION SELECT DISTINCT araccnt_ar_accnt_id FROM araccnt
                  UNION SELECT DISTINCT araccnt_deferred_accnt_id FROM araccnt
                  UNION SELECT DISTINCT araccnt_freight_accnt_id FROM araccnt
                  UNION SELECT DISTINCT araccnt_prepaid_accnt_id FROM araccnt
                  UNION SELECT DISTINCT aropen_accnt_id FROM aropen
                  UNION SELECT DISTINCT bankaccnt_accnt_id FROM bankaccnt
                  UNION SELECT DISTINCT bankaccnt_rec_accnt_id FROM bankaccnt
                  UNION SELECT DISTINCT budgitem_accnt_id FROM budgitem
                  UNION SELECT DISTINCT cashrcptmisc_accnt_id FROM cashrcptmisc
                  UNION SELECT DISTINCT cmhead_misc_accnt_id FROM cmhead
                  UNION SELECT DISTINCT cobmisc_misc_accnt_id FROM cobmisc
                  UNION SELECT DISTINCT cohead_misc_accnt_id FROM cohead
                  UNION SELECT DISTINCT coitem_cos_accnt_id FROM coitem
                  UNION SELECT DISTINCT costcat_adjustment_accnt_id FROM costcat
                  UNION SELECT DISTINCT costcat_asset_accnt_id FROM costcat
                  UNION SELECT DISTINCT costcat_freight_accnt_id FROM costcat
                  UNION SELECT DISTINCT costcat_invcost_accnt_id FROM costcat
                  UNION SELECT DISTINCT costcat_laboroverhead_accnt_id FROM costcat
                  UNION SELECT DISTINCT costcat_liability_accnt_id FROM costcat
                  UNION SELECT DISTINCT costcat_matusage_accnt_id FROM costcat
                  UNION SELECT DISTINCT costcat_mfgscrap_accnt_id FROM costcat
                  UNION SELECT DISTINCT costcat_purchprice_accnt_id FROM costcat
                  UNION SELECT DISTINCT costcat_scrap_accnt_id FROM costcat
                  UNION SELECT DISTINCT costcat_shipasset_accnt_id FROM costcat
                  UNION SELECT DISTINCT costcat_toliability_accnt_id FROM costcat
                  UNION SELECT DISTINCT costcat_transform_accnt_id FROM costcat
                  UNION SELECT DISTINCT costcat_wip_accnt_id FROM costcat
                  UNION SELECT DISTINCT costelem_exp_accnt_id FROM costelem
                  UNION SELECT DISTINCT expcat_exp_accnt_id FROM expcat
                  UNION SELECT DISTINCT expcat_freight_accnt_id FROM expcat
                  UNION SELECT DISTINCT expcat_liability_accnt_id FROM expcat
                  UNION SELECT DISTINCT expcat_purchprice_accnt_id FROM expcat
                  UNION SELECT DISTINCT glseries_accnt_id FROM glseries
                  UNION SELECT DISTINCT gltrans_accnt_id FROM gltrans
                  UNION SELECT DISTINCT invchead_misc_accnt_id FROM invchead
                  UNION SELECT DISTINCT quhead_misc_accnt_id FROM quhead
                  UNION SELECT DISTINCT salesaccnt_cor_accnt_id FROM salesaccnt
                  UNION SELECT DISTINCT salesaccnt_cos_accnt_id FROM salesaccnt
                  UNION SELECT DISTINCT salesaccnt_cow_accnt_id FROM salesaccnt
                  UNION SELECT DISTINCT salesaccnt_credit_accnt_id FROM salesaccnt
                  UNION SELECT DISTINCT salesaccnt_returns_accnt_id FROM salesaccnt
                  UNION SELECT DISTINCT salesaccnt_sales_accnt_id FROM salesaccnt
                  UNION SELECT DISTINCT salescat_ar_accnt_id FROM salescat
                  UNION SELECT DISTINCT salescat_prepaid_accnt_id FROM salescat
                  UNION SELECT DISTINCT salescat_sales_accnt_id FROM salescat
                  UNION SELECT DISTINCT stdjrnlitem_accnt_id FROM stdjrnlitem
                  UNION SELECT DISTINCT tax_sales_accnt_id FROM tax
                  UNION SELECT DISTINCT taxauth_accnt_id FROM taxauth
                  UNION SELECT DISTINCT vodist_accnt_id FROM vodist
                  UNION SELECT DISTINCT warehous_default_accnt_id FROM whsinfo
                ) AS dummy
              WHERE ((accnt_id=test_accnt_id)
                AND  (accnt_company=company_number)
                AND  (accnt_company=NEW.company_number))
    ) THEN
      RAISE EXCEPTION 'Cannot make Company % External because it is used in the local database.',
                      NEW.company_number;
    ELSIF (fetchMetricBool('EnableReturnAuth')) THEN
      IF EXISTS(SELECT accnt_id
              FROM accnt, company, (
                  SELECT DISTINCT rahead_misc_accnt_id AS test_accnt_id FROM rahead
                  UNION SELECT DISTINCT raitem_cos_accnt_id FROM raitem
                ) AS dummy
              WHERE ((accnt_id=test_accnt_id)
                AND  (accnt_company=company_number)
                AND  (accnt_company=NEW.company_number))
      ) THEN
        RAISE EXCEPTION 'Cannot make Company % External because it is used in the local database.',
                        NEW.company_number;
      END IF;
    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._companytrigger() OWNER TO admin;

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

CREATE FUNCTION _contrctaftertrigger() 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

  -- synchronize contract effectivity with item source effectivity
  IF (NEW.contrct_effective <> OLD.contrct_effective) THEN
    UPDATE itemsrc SET itemsrc_effective=NEW.contrct_effective
    WHERE itemsrc_contrct_id=NEW.contrct_id;
  END IF;

  IF (NEW.contrct_expires <> OLD.contrct_expires) THEN
    UPDATE itemsrc SET itemsrc_expires=NEW.contrct_expires
    WHERE itemsrc_contrct_id=NEW.contrct_id;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._contrctaftertrigger() OWNER TO admin;

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

CREATE FUNCTION _crmacctafterdeletetrigger() 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 = 'CRMACCT'
    AND charass_target_id = OLD.crmacct_id;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._crmacctafterdeletetrigger() OWNER TO admin;

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

CREATE FUNCTION _crmacctaftertrigger() 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;
  _gotpriv    BOOLEAN;

BEGIN
  /* update _number and _name separately to propagate just what changed.
     the priv manipulation allows targeted updates of crmaccount-maintained data
   */
  IF TG_OP = 'UPDATE' AND
      (OLD.crmacct_number != NEW.crmacct_number OR
       OLD.crmacct_name   != NEW.crmacct_name) THEN
      IF NEW.crmacct_cust_id IS NOT NULL THEN
        UPDATE custinfo SET cust_number = NEW.crmacct_number
        WHERE ((cust_id=NEW.crmacct_cust_id)
          AND  (cust_number!=NEW.crmacct_number));
        UPDATE custinfo SET cust_name = NEW.crmacct_name
        WHERE ((cust_id=NEW.crmacct_cust_id)
          AND  (cust_name!=NEW.crmacct_name));
      END IF;

      IF NEW.crmacct_emp_id IS NOT NULL THEN
        UPDATE emp SET emp_code = NEW.crmacct_number
        WHERE ((emp_id=NEW.crmacct_emp_id)
          AND  (emp_code!=NEW.crmacct_number));
        UPDATE emp SET emp_name = NEW.crmacct_name
        WHERE ((emp_id=NEW.crmacct_emp_id)
          AND  (emp_name!=NEW.crmacct_name));
      END IF;

      IF (NEW.crmacct_prospect_id IS NOT NULL) THEN
        UPDATE prospect SET prospect_number = NEW.crmacct_number
        WHERE ((prospect_id=NEW.crmacct_prospect_id)
          AND  (prospect_number!=NEW.crmacct_number));
        UPDATE prospect SET prospect_name = NEW.crmacct_name
        WHERE ((prospect_id=NEW.crmacct_prospect_id)
          AND  (prospect_name!=NEW.crmacct_name));
      END IF;

      IF (NEW.crmacct_salesrep_id IS NOT NULL) THEN
        UPDATE salesrep SET salesrep_number = NEW.crmacct_number
        WHERE ((salesrep_id=NEW.crmacct_salesrep_id)
          AND  (salesrep_number!=NEW.crmacct_number));
        UPDATE salesrep SET salesrep_name = NEW.crmacct_name
        WHERE ((salesrep_id=NEW.crmacct_salesrep_id)
          AND  (salesrep_name!=NEW.crmacct_name));
      END IF;

      IF (NEW.crmacct_taxauth_id IS NOT NULL) THEN
        UPDATE taxauth SET taxauth_code = NEW.crmacct_number
        WHERE ((taxauth_id=NEW.crmacct_taxauth_id)
          AND  (taxauth_code!=NEW.crmacct_number));
        UPDATE taxauth SET taxauth_name = NEW.crmacct_name
        WHERE ((taxauth_id=NEW.crmacct_taxauth_id)
          AND  (taxauth_name!=NEW.crmacct_name));
      END IF;

      IF (NEW.crmacct_vend_id IS NOT NULL) THEN
        UPDATE vendinfo SET vend_number = NEW.crmacct_number
        WHERE ((vend_id=NEW.crmacct_vend_id)
          AND  (vend_number!=NEW.crmacct_number));
        UPDATE vendinfo SET vend_name = NEW.crmacct_name
        WHERE ((vend_id=NEW.crmacct_vend_id)
          AND  (vend_name!=NEW.crmacct_name));
      END IF;
  END IF;

  IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
    -- Link Primary and Secondary Contacts to this Account if they are not already
    IF (NEW.crmacct_cntct_id_1 IS NOT NULL) THEN
      UPDATE cntct SET cntct_crmacct_id = NEW.crmacct_id
       WHERE cntct_id=NEW.crmacct_cntct_id_1
         AND COALESCE(cntct_crmacct_id, -1) != NEW.crmacct_id;
    END IF;

    IF (NEW.crmacct_cntct_id_2 IS NOT NULL) THEN
      UPDATE cntct SET cntct_crmacct_id = NEW.crmacct_id
       WHERE cntct_id=NEW.crmacct_cntct_id_2
         AND COALESCE(cntct_crmacct_id, -1) != NEW.crmacct_id;
    END IF;

    -- cannot have fkey references to system catalogs so enforce them here
    IF (NEW.crmacct_usr_username IS NOT NULL) THEN
      IF (NOT EXISTS(SELECT usr_username
                       FROM usr
                      WHERE usr_username=NEW.crmacct_usr_username)) THEN
        RAISE EXCEPTION 'User % does not exist so this CRM Account Number is invalid.',
                        NEW.crmacct_usr_username;
      END IF;
      IF (TG_OP = 'UPDATE') THEN
        -- reminder: this evaluates to false if either is NULL
        IF (NEW.crmacct_usr_username != OLD.crmacct_usr_username) THEN
          RAISE EXCEPTION 'Cannot change the user name for %',
                          OLD.crmacct_usr_username;
        END IF;
      END IF;
      UPDATE usrpref SET usrpref_value = NEW.crmacct_name
      WHERE ((usrpref_username=NEW.crmacct_usr_username)
        AND  (usrpref_name='propername')
        AND  (usrpref_value!=NEW.crmacct_name));
    END IF;

  ELSIF (TG_OP = 'DELETE') THEN
    IF (OLD.crmacct_cust_id IS NOT NULL) THEN
      RAISE EXCEPTION 'Cannot delete CRM Account because it is a Customer [xtuple: deleteCrmAccount, -1]';
    END IF;

    IF (OLD.crmacct_emp_id IS NOT NULL) THEN
      RAISE EXCEPTION 'Cannot delete CRM Account because it is an Employee [xtuple: deleteCrmAccount, -7]';
    END IF;

    IF (OLD.crmacct_prospect_id IS NOT NULL) THEN
      RAISE EXCEPTION 'Cannot delete CRM Account because it is a Prospect [xtuple: deleteCrmAccount, -3]';
    END IF;

    DELETE FROM salesrep WHERE salesrep_id  = OLD.crmacct_salesrep_id;
    IF (OLD.crmacct_salesrep_id IS NOT NULL) THEN
      RAISE EXCEPTION 'Cannot delete CRM Account because it is a Sales Rep [xtuple: deleteCrmAccount, -6]';
    END IF;

    IF (OLD.crmacct_taxauth_id IS NOT NULL) THEN
      RAISE EXCEPTION 'Cannot delete CRM Account because it is a Tax Authority [xtuple: deleteCrmAccount, -5]';
    END IF;

    IF (EXISTS(SELECT usename
                 FROM pg_user
                WHERE usename=OLD.crmacct_usr_username)) THEN
      RAISE EXCEPTION 'Cannot delete CRM Account because it is a User [xtuple: deleteCrmAccount, -8]';
    END IF;

    IF (OLD.crmacct_vend_id IS NOT NULL) THEN
      RAISE EXCEPTION 'Cannot delete CRM Account because it is a Vendor [xtuple: deleteCrmAccount, -2]';
    END IF;

    DELETE FROM imageass
     WHERE (imageass_source_id=OLD.crmacct_id) AND (imageass_source='CRMA');
    DELETE FROM url
     WHERE (url_source_id=OLD.crmacct_id)      AND (url_source='CRMA');

  END IF;

  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, 'CRMA', NEW.crmacct_id,
                          ('Created by ' || getEffectiveXtUser()));

    ELSIF (TG_OP = 'DELETE') THEN
      PERFORM postComment(_cmnttypeid, 'CRMA', OLD.crmacct_id,
                          'Deleted "' || OLD.crmacct_number || '"');
    END IF;
  END IF;

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

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._crmacctaftertrigger() OWNER TO admin;

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

CREATE FUNCTION _crmacctbeforetrigger() 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
  _count        INTEGER;
BEGIN
  -- disallow reusing crmacct_numbers
  IF (TG_OP IN ('INSERT', 'UPDATE')) THEN
    IF (TG_OP = 'INSERT' AND fetchMetricText('CRMAccountNumberGeneration') IN ('A','O')) THEN
      PERFORM clearNumberIssue('CRMAccountNumber', NEW.crmacct_number);
    END IF;

    NEW.crmacct_usr_username := LOWER(TRIM(NEW.crmacct_usr_username));
    IF (NEW.crmacct_usr_username = '') THEN
      NEW.crmacct_usr_username = NULL;
    END IF;

    NEW.crmacct_owner_username := LOWER(TRIM(NEW.crmacct_owner_username));
    IF (COALESCE(NEW.crmacct_owner_username, '') = '') THEN
      NEW.crmacct_owner_username = getEffectiveXtUser();
    END IF;

    IF (NEW.crmacct_competitor_id < 0) THEN
      NEW.crmacct_competitor_id := NULL;
    END IF;
    IF (NEW.crmacct_partner_id < 0) THEN
      NEW.crmacct_partner_id := NULL;
    END IF;

    NEW.crmacct_number = UPPER(NEW.crmacct_number);

    IF (TG_OP = 'UPDATE') THEN
      -- TODO: why not ALTER USER OLD.crmacct_number RENAME TO LOWER(NEW.crmacct_number)?
      IF (NEW.crmacct_number != UPPER(OLD.crmacct_number) AND
          NEW.crmacct_usr_username IS NOT NULL            AND
          UPPER(NEW.crmacct_usr_username) != NEW.crmacct_number) THEN
        RAISE EXCEPTION 'The CRM Account % is associated with a system User so the number cannot be changed.',
                        NEW.crmacct_number;
      END IF;

      -- It appears possible to remove a user account without cleaning up the CRM account (#25291)
      -- Tidy up CRM Account in this scenario to prevent errors
      IF (NEW.crmacct_usr_username IS NOT NULL) THEN
        IF (NOT EXISTS(SELECT usr_username
                       FROM usr
                      WHERE usr_username=NEW.crmacct_usr_username)) THEN
          NEW.crmacct_usr_username = NULL;
        END IF;
      END IF;
    END IF;

  ELSIF (TG_OP = 'DELETE') THEN
    UPDATE cntct SET cntct_crmacct_id = NULL
     WHERE cntct_crmacct_id = OLD.crmacct_id;

    DELETE FROM docass WHERE docass_source_id = OLD.crmacct_id AND docass_source_type = 'CRMA';
    DELETE FROM docass WHERE docass_target_id = OLD.crmacct_id AND docass_target_type = 'CRMA';

    GET DIAGNOSTICS _count = ROW_COUNT;
    RAISE DEBUG 'updated % contacts', _count;

    RETURN OLD;

  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._crmacctbeforetrigger() OWNER TO admin;

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

CREATE FUNCTION _custaftertrigger() 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;
  _whsId      INTEGER := -1;

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_cust_id=NEW.cust_id,
                         crmacct_name=NEW.cust_name,
                         crmacct_prospect_id=NULL
      WHERE crmacct_number=NEW.cust_number;
      IF (FOUND) THEN
        DELETE FROM prospect WHERE prospect_id=NEW.cust_id;
        EXIT;
      END IF;
      BEGIN
        INSERT INTO crmacct(crmacct_number,  crmacct_name,    crmacct_active,
                            crmacct_type,    crmacct_cust_id, crmacct_cntct_id_1,
                            crmacct_cntct_id_2
                  ) VALUES (NEW.cust_number, NEW.cust_name,   NEW.cust_active,
                            'O',             NEW.cust_id,     NEW.cust_cntct_id,
                            NEW.cust_corrcntct_id);
        EXIT;
      EXCEPTION WHEN unique_violation THEN
            -- do nothing, and loop to try the UPDATE again
      END;
    END LOOP;

    PERFORM updateCharAssignment('C', NEW.cust_id, char_id, charass_value)
       FROM custtype
       JOIN charass ON (custtype_id=charass_target_id AND charass_target_type='CT')
       JOIN char ON (charass_char_id=char_id)
       WHERE ((custtype_id=NEW.cust_custtype_id)
          AND (custtype_char)
          AND (charass_default));

  ELSIF (TG_OP = 'UPDATE') THEN
    UPDATE crmacct SET crmacct_number = NEW.cust_number
    WHERE ((crmacct_cust_id=NEW.cust_id)
      AND  (crmacct_number!=NEW.cust_number));

    UPDATE crmacct SET crmacct_name = NEW.cust_name
    WHERE ((crmacct_cust_id=NEW.cust_id)
      AND  (crmacct_name!=NEW.cust_name));
  END IF;

  IF (TG_OP = 'INSERT') THEN
    PERFORM postEvent('NewCustomer', 'C', NEW.cust_id,
                      NULL, NEW.cust_number,
                      NULL, NULL, NULL, NULL);
  END IF;

  IF (fetchMetricBool('CustomerChangeLog')) 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, 'C', NEW.cust_id, 'Created');

      ELSIF (TG_OP = 'UPDATE') THEN

        IF (OLD.cust_number <> NEW.cust_number) THEN
          PERFORM postComment( _cmnttypeid, 'C', NEW.cust_id,
                              ('Number changed from "' || OLD.cust_number ||
                               '" to "' || NEW.cust_number || '"') );
        END IF;

        IF (OLD.cust_name <> NEW.cust_name) THEN
          PERFORM postComment( _cmnttypeid, 'C', NEW.cust_id,
                              ('Name changed from "' || OLD.cust_name ||
                               '" to "' || NEW.cust_name || '"') );
        END IF;

        IF (OLD.cust_active <> NEW.cust_active) THEN
          PERFORM postComment(_cmnttypeid, 'C', NEW.cust_id,
                              CASE WHEN NEW.cust_active THEN 'Activated'
                                   ELSE 'Deactivated' END);
        END IF;

        IF (OLD.cust_discntprcnt <> NEW.cust_discntprcnt) THEN
          PERFORM postComment(_cmnttypeid, 'C', NEW.cust_id,
                              ('Discount changed from "' ||
                               formatprcnt(OLD.cust_discntprcnt) || '%" to "' ||
                               formatprcnt(NEW.cust_discntprcnt) || '%"') );
        END IF;

        IF (OLD.cust_creditlmt <> NEW.cust_creditlmt) THEN
          PERFORM postComment(_cmnttypeid, 'C', NEW.cust_id,
                              ('Credit Limit changed from ' || formatMoney(OLD.cust_creditlmt) ||
                               ' to ' || formatMoney(NEW.cust_creditlmt)));
        END IF;

        IF (OLD.cust_creditstatus <> NEW.cust_creditstatus) THEN
          PERFORM postComment(_cmnttypeid, 'C', NEW.cust_id,
                              ('Credit Status Changed from "' ||
                               CASE OLD.cust_creditstatus
                                    WHEN 'G' THEN 'In Good Standing'
                                    WHEN 'W' THEN 'Credit Warning'
                                    WHEN 'H' THEN 'Credit Hold'
                                    ELSE 'Unknown/Error'
                               END || '" to "' ||
                               CASE NEW.cust_creditstatus
                                    WHEN 'G' THEN 'In Good Standing'
                                    WHEN 'W' THEN 'Credit Warning'
                                    WHEN 'H' THEN 'Credit Hold'
                                    ELSE 'Unknown/Error'
                               END || '"') );
        END IF;

        IF (OLD.cust_custtype_id <> NEW.cust_custtype_id) THEN
          PERFORM postComment(_cmnttypeid, 'C', NEW.cust_id,
                              ('Customer type changed from "' ||
                               (SELECT custtype_code FROM custtype
                                 WHERE custtype_id = OLD.cust_custtype_id) || '" to "' ||
                               (SELECT custtype_code FROM custtype
                                 WHERE custtype_id = NEW.cust_custtype_id) || '"') );
        END IF;

        IF (COALESCE(OLD.cust_gracedays,-1) <> COALESCE(NEW.cust_gracedays,-1)) THEN
          PERFORM postComment(_cmnttypeid, 'C', NEW.cust_id,
                              ('Grace Days changed from "' ||
                               COALESCE(TEXT(OLD.cust_gracedays), 'Default') ||
                               '" to "' ||
                               COALESCE(TEXT(NEW.cust_gracedays), 'Default') || '"'));
        END IF;

        IF (OLD.cust_terms_id <> NEW.cust_terms_id) THEN
          PERFORM postComment(_cmnttypeid, 'C', NEW.cust_id,
                              ('Terms changed from "' ||
                               (SELECT terms_code FROM terms
                                 WHERE terms_id = OLD.cust_terms_id) || '" to "' ||
                               (SELECT terms_code FROM terms
                                 WHERE terms_id = NEW.cust_terms_id) || '"'));
        END IF;

      END IF;
    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._custaftertrigger() OWNER TO admin;

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

CREATE FUNCTION _custinfoafterdeletetrigger() 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
  -- handle transitory state when converting customer to prospect
  IF EXISTS(SELECT quhead_id
              FROM quhead
             WHERE (quhead_cust_id=OLD.cust_id) AND
     NOT EXISTS(SELECT prospect_id
                  FROM prospect
                 WHERE prospect_id=OLD.cust_id)) THEN
    RAISE EXCEPTION '[xtuple: deleteCustomer, -8]';
  END IF;

  IF EXISTS(SELECT invchead_id
              FROM invchead
             WHERE (invchead_cust_id=OLD.cust_id)) THEN
    RAISE EXCEPTION '[xtuple: deleteCustomer, -7]';
  END IF;
  -- end TODO

  IF EXISTS(SELECT checkhead_recip_id
              FROM checkhead
             WHERE ((checkhead_recip_id=OLD.cust_id)
               AND  (checkhead_recip_type='C'))) THEN
    RAISE EXCEPTION '[xtuple: deleteCustomer, -6]';
  END IF;

  DELETE FROM taxreg
   WHERE ((taxreg_rel_type='C')
     AND  (taxreg_rel_id=OLD.cust_id));

  DELETE FROM ipsass
   WHERE (ipsass_cust_id=OLD.cust_id);

  DELETE FROM docass WHERE docass_source_id = OLD.cust_id AND docass_source_type = 'C';
  DELETE FROM docass WHERE docass_target_id = OLD.cust_id AND docass_target_type = 'C';

  DELETE
  FROM charass
  WHERE charass_target_type = 'C'
    AND charass_target_id = OLD.cust_id;

  IF (fetchMetricBool('CustomerChangeLog')) THEN
    PERFORM postComment(cmnttype_id, 'C', OLD.cust_id,
                        ('Deleted "' || OLD.cust_number || '"'))
      FROM cmnttype
     WHERE (cmnttype_name='ChangeLog');
  END IF;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._custinfoafterdeletetrigger() OWNER TO admin;

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

CREATE FUNCTION _custinfobeforedeletetrigger() 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('MaintainCustomerMasters')) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Customers.';
  END IF;

  UPDATE crmacct SET crmacct_cust_id = NULL
   WHERE crmacct_cust_id = OLD.cust_id;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._custinfobeforedeletetrigger() OWNER TO admin;

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

CREATE FUNCTION _custtrigger() 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('MaintainCustomerMasters') OR
          checkPrivilege('PostMiscInvoices')) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Customers.';
  END IF;

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

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

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

  IF (NEW.cust_salesrep_id IS NULL) THEN
        RAISE EXCEPTION 'You must supply a valid Sales Rep ID.';
  END IF;

  IF (NEW.cust_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.cust_number);
  END IF;

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

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._custtrigger() OWNER TO admin;

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

CREATE FUNCTION _custtypeafterdeletetrigger() 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('DefaultCustType') = OLD.custtype_id) THEN
    RAISE EXCEPTION 'Cannot delete the default Customer Type [xtuple: custtype, -1, %]',
                    OLD.custtype_code;
  END IF;

  DELETE
  FROM charass
  WHERE charass_target_type = 'CT'
    AND charass_target_id = OLD.custtype_id;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._custtypeafterdeletetrigger() OWNER TO admin;

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

CREATE FUNCTION _custtypetrigger() 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;
  _code       TEXT;

BEGIN

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

    IF (LENGTH(COALESCE(NEW.custtype_code, ''))=0) THEN
      RAISE EXCEPTION 'You must supply a valid Customer Type Code.';
    END IF;

    SELECT custtype_code INTO _code
    FROM custtype
    WHERE ( (UPPER(custtype_code)=UPPER(NEW.custtype_code))
      AND (custtype_id<>NEW.custtype_id) );
    IF (FOUND) THEN
      RAISE EXCEPTION 'The Customer Type Code entered cannot be used as it is in use.';
    END IF;

  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._custtypetrigger() OWNER TO admin;

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

CREATE FUNCTION _docasstrigger() 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 (NEW.docass_source_type = 'INCDT') THEN
    UPDATE incdt SET incdt_updated = now() WHERE incdt_id = NEW.docass_source_id;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._docasstrigger() OWNER TO admin;

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

CREATE FUNCTION _docinfo(req_id integer, req_type text) RETURNS SETOF _docinfo
    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.
-- Return all document associations, optionally limited to the given document type
DECLARE
  _current      TEXT := '';
  _row          _docinfo%rowtype;
  _desc         record;
  _crm          record;
  _rev          TEXT := $$SELECT '' AS revnumber, '' AS revname, '' AS revdesc,
                                 -1 AS revid,     '' AS revtype$$;
  _reverseQ     TEXT := $$SELECT %s AS revnumber, %s AS revname, %s AS revdesc,
                                 %s AS revid,     '%s' AS revtype
                            FROM %s %s$$;
  _docassQ      TEXT := $$SELECT docass_id AS id,
                                %s AS target_number,
                                %s AS target_type,
                                %s AS target_id,
                                %s AS source_type,
                                %s AS source_id,
                                %s AS name, %s AS description,
                                docass_purpose AS purpose
                           FROM docass JOIN %s ON docass_target_id = %s
                           %s %s
                          WHERE docass_target_type = '%s'
                            AND docass_source_id = %s
                            AND docass_source_type = '%s'
                         UNION ALL
                         SELECT docass_id AS id,
                                revnumber AS target_number,
                                %s AS target_type,
                                %s AS target_id,
                                %s AS source_type,
                                %s AS source_id,
                                revname AS name, revdesc AS description,
                                CASE
                                  WHEN docass_purpose = 'A' THEN 'C'
                                  WHEN docass_purpose = 'C' THEN 'A'
                                  ELSE docass_purpose
                                END AS purpose
                           FROM docass JOIN %s ON docass_target_id = %s
                           JOIN rev ON revid = docass_source_id AND revtype = docass_source_type
                           %s
                          WHERE docass_target_type = '%s'
                            AND docass_source_id = %s
                            AND docass_source_type = '%s'
                       $$;
  _imgQ         TEXT := $$SELECT imageass_id AS id,
                                 image_id::text AS target_number,
                                 'IMG' AS target_type,
                                 imageass_image_id AS target_id,
                                 imageass_source AS source_type,
                                 imageass_source_id AS source_id,
                                 image_name AS name, image_descrip AS description,
                                 imageass_purpose AS purpose
                          FROM imageass
                          JOIN image ON imageass_image_id=image_id
                          WHERE true
                            AND imageass_source_id = %s
                            AND imageass_source = '%s'
                         $$;
  _urlQ         TEXT := $$SELECT url_id AS id,
                                 url_id::text AS target_number,
                                 'URL' AS target_type,
                                 url_id AS target_id,
                                 url_source AS source_type,
                                 url_source_id AS source_id,
                                 url_title AS name, url_url AS description,
                                 'S' AS doc_purpose
                           FROM url
                           WHERE (url_stream IS NULL)
                             AND url_source_id = %s
                             AND url_source = '%s'
                        $$;
  _fileQ        TEXT := $$SELECT url_id AS id,
                                 url_id::text AS target_number,
                                 'FILE' AS target_type,
                                 url_id AS target_id,
                                 url_source AS source_type,
                                 url_source_id AS source_id,
                                 url_title AS name, url_url AS description,
                                 'S' AS doc_purpose
                          FROM url
                          WHERE (url_stream IS NOT NULL)
                            AND url_source_id = %s
                            AND url_source = '%s'
                        $$;

  _crmIdField       TEXT := '';
  _crmChildIdField  TEXT := '';
BEGIN
  -- TODO: normalize image, url, and file into docass
  _current := _current || format(_imgQ, req_id, req_type);
  _current := _current || ' UNION ALL ' || format(_urlQ, req_id, req_type);
  _current := _current || ' UNION ALL ' || format(_fileQ, req_id, req_type);

  SELECT source.* INTO _crm
    FROM source
   WHERE source_docass = 'CRMA';   -- must match populate_source.sql

  FOR _desc IN SELECT source.*
                 FROM source
                 JOIN pg_class c on source_table = relname
                 JOIN pg_namespace n on relnamespace = n.oid
                 JOIN regexp_split_to_table(buildSearchPath(), E',\\s*') sp
                      on nspname = sp
                WHERE relkind = 'r'
  LOOP
    _rev := _rev || ' UNION ALL ' ||
            format(_reverseQ,
                   _desc.source_number_field, _desc.source_name_field,
                   _desc.source_desc_field,   _desc.source_key_field,
                   _desc.source_docass,       _desc.source_table,
                   _desc.source_joins);
    _current := _current || ' UNION ALL ' ||
                format(_docassQ,
                       _desc.source_number_field,
                       'docass_target_type',      'docass_target_id',
                       'docass_source_type',      'docass_source_id',
                       _desc.source_name_field,
                       _desc.source_desc_field,   _desc.source_table,
                       _desc.source_key_field,    _desc.source_joins,
                       '',                        _desc.source_docass,
                       req_id,                    req_type,
                       'docass_source_type',      'docass_source_id',
                       'docass_target_type',      'docass_target_id',
                       _desc.source_table,        _desc.source_key_field,
                       _desc.source_joins,        _desc.source_docass,
                       req_id,                    req_type);

    -- must match populate_source.sql
    IF _desc.source_docass IN ('C', 'V', 'EMP', 'PSPCT', 'SR', 'USR', 'TAXAUTH')  THEN
      /* for each type of CRM Account child (e.g. customer, vendor),
         - return all CRM Account child doc associations as belonging to the CRM Account
         - return all CRM Account document associations as belonging to the child
       */
      CASE _desc.source_docass
        WHEN 'C'       THEN _crmIdField := 'crmacct_cust_id';      _crmChildIdField := 'cust_id';
        WHEN 'V'       THEN _crmIdField := 'crmacct_vend_id';      _crmChildIdField := 'vend_id';
        WHEN 'EMP'     THEN _crmIdField := 'crmacct_emp_id';       _crmChildIdField := 'emp_id';
        WHEN 'PSPCT'   THEN _crmIdField := 'crmacct_prospect_id';  _crmChildIdField := 'prospect_id';
        WHEN 'SR'      THEN _crmIdField := 'crmacct_salesrep_id';  _crmChildIdField := 'salesrep_id';
        WHEN 'USR'     THEN _crmIdField := 'crmacct_usr_username'; _crmChildIdField := 'usr_username';
        WHEN 'TAXAUTH' THEN _crmIdField := 'crmacct_taxauth_id';   _crmChildIdField := 'taxauth_id';
      END CASE;

      _current := _current || ' UNION ALL ' ||
                  format(_docassQ,
                         _crm.source_number_field,
                         '$$' || _crm.source_docass || '$$', _crm.source_key_field,
                         'docass_source_type',      'docass_source_id',
                         _desc.source_name_field,
                         _desc.source_desc_field,   _desc.source_table,
                         _desc.source_key_field,    _desc.source_joins,
                         format('JOIN crmacct ON %s = %s', _crmIdField, _crmChildIdField),
                         _desc.source_docass,
                         req_id,                    req_type,
                         'docass_source_type',      'docass_source_id',
                         '$$' || _crm.source_docass || '$$', _crm.source_key_field,
                         _desc.source_table,               _desc.source_key_field,
                         format('JOIN crmacct ON %s = %s', _crmIdField, _crmChildIdField),
                         _desc.source_docass,
                         req_id,                    req_type);

    END IF;
  END LOOP;

  _current := 'WITH rev AS (' || _rev || ') ' || _current;

  FOR _row IN EXECUTE(_current) LOOP
    RETURN NEXT _row;
  END LOOP;

  RETURN;
END;
$_$;


ALTER FUNCTION public._docinfo(req_id integer, req_type text) OWNER TO admin;

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

CREATE FUNCTION _empafterdeletetrigger() 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 (fetchMetricBool('EmployeeChangeLog')) THEN
    PERFORM postComment(cmnttype_id, 'EMP', OLD.emp_id,
                        ('Deleted "' || OLD.emp_code || '"'))
      FROM cmnttype
     WHERE (cmnttype_name='ChangeLog');
  END IF;

  DELETE
  FROM charass
  WHERE charass_target_type = 'EMP'
    AND charass_target_id = OLD.emp_id;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._empafterdeletetrigger() OWNER TO admin;

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

CREATE FUNCTION _empaftertrigger() 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;
  _newcrmacctname TEXT;

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_emp_id=NEW.emp_id,
                         crmacct_name=NEW.emp_name
       WHERE crmacct_number=NEW.emp_code;
      IF (FOUND) THEN
        EXIT;
      END IF;
      BEGIN
        INSERT INTO crmacct(crmacct_number,  crmacct_name,    crmacct_active,
                            crmacct_type,    crmacct_emp_id,  crmacct_cntct_id_1
                  ) VALUES (NEW.emp_code,    NEW.emp_name,    NEW.emp_active,
                            'I',             NEW.emp_id,      NEW.emp_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 empgrp? */

  ELSIF (TG_OP = 'UPDATE') THEN
    UPDATE crmacct SET crmacct_number = NEW.emp_code
    WHERE ((crmacct_emp_id=NEW.emp_id)
      AND  (crmacct_number!=NEW.emp_code));

    UPDATE crmacct SET crmacct_name = NEW.emp_name
    WHERE ((crmacct_emp_id=NEW.emp_id)
      AND  (crmacct_name!=NEW.emp_name));
  END IF;

  IF (fetchMetricBool('EmployeeChangeLog')) 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, 'EMP', NEW.emp_id, 'Created');

      ELSIF (TG_OP = 'UPDATE') THEN

        IF (OLD.emp_number <> NEW.emp_number) THEN
          PERFORM postComment(_cmnttypeid, 'EMP', NEW.emp_id,
                              ('Number Changed from "' || OLD.emp_number ||
                               '" to "' || NEW.emp_number || '"'));
        END IF;

        IF (OLD.emp_code <> NEW.emp_code) THEN
          PERFORM postComment(_cmnttypeid, 'EMP', NEW.emp_id,
                              ('Code Changed from "' || OLD.emp_code ||
                               '" to "' || NEW.emp_code || '"'));
        END IF;

        IF (OLD.emp_active <> NEW.emp_active) THEN
          PERFORM postComment(_cmnttypeid, 'EMP', NEW.emp_id,
                              CASE WHEN NEW.emp_active THEN 'Activated'
                                   ELSE 'Deactivated' END);
        END IF;

        IF (COALESCE(OLD.emp_dept_id, -1) <> COALESCE(NEW.emp_dept_id, -1)) THEN
          PERFORM postComment(_cmnttypeid, 'EMP', NEW.emp_id,
                              ('Department Changed from "' ||
                               COALESCE((SELECT dept_number FROM dept
                                          WHERE dept_id=OLD.emp_dept_id), '')
                               || '" to "' ||
                               COALESCE((SELECT dept_number FROM dept
                                          WHERE dept_id=NEW.emp_dept_id), '') || '"'));
        END IF;

        IF (COALESCE(OLD.emp_shift_id, -1) <> COALESCE(NEW.emp_shift_id, -1)) THEN
          PERFORM postComment(_cmnttypeid, 'EMP', NEW.emp_id,
                              ('Shift Changed from "' ||
                               COALESCE((SELECT shift_number FROM shift
                                          WHERE shift_id=OLD.emp_shift_id), '')
                               || '" to "' ||
                               COALESCE((SELECT shift_number FROM shift
                                          WHERE shift_id=NEW.emp_shift_id), '') || '"'));
        END IF;

      END IF;
    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._empaftertrigger() OWNER TO admin;

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

CREATE FUNCTION _empbeforedeletetrigger() 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('MaintainEmployees')) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Employees.';
  END IF;

  UPDATE crmacct SET crmacct_emp_id = NULL
   WHERE crmacct_emp_id = OLD.emp_id;

  UPDATE salesrep SET salesrep_emp_id = NULL
   WHERE salesrep_emp_id = OLD.emp_id;

  DELETE FROM docass WHERE docass_source_id = OLD.emp_id AND docass_source_type = 'EMP';
  DELETE FROM docass WHERE docass_target_id = OLD.emp_id AND docass_target_type = 'EMP';

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._empbeforedeletetrigger() OWNER TO admin;

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

CREATE FUNCTION _empbeforetrigger() 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('MaintainEmployees')) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Employees.';
  END IF;

  IF (NEW.emp_code IS NULL) THEN
    RAISE EXCEPTION 'You must supply a valid Employee Code.';
  END IF;

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

  IF (NEW.emp_id = NEW.emp_mgr_emp_id) THEN
    RAISE EXCEPTION 'An Employee may not be his or her own Manager.';
  END IF;

  -- ERROR:  cannot use column references in default expression
  IF (NEW.emp_name IS NULL) THEN
    NEW.emp_name = COALESCE(formatCntctName(NEW.emp_cntct_id), NEW.emp_number);
  END IF;

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

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

  -- deprecated column emp_username
  IF (TG_OP = 'UPDATE' AND
      LOWER(NEW.emp_username) != LOWER(NEW.emp_code) AND
      EXISTS(SELECT 1
               FROM crmacct
              WHERE crmacct_emp_id = NEW.emp_id
                AND crmacct_usr_username IS NOT NULL)) THEN
    NEW.emp_username = LOWER(NEW.emp_code);
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._empbeforetrigger() OWNER TO admin;

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

CREATE FUNCTION _evntlogafterinserttrigger() 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 (NEW.evntlog_username = 'autopilot') THEN
    SELECT * INTO _r FROM evnttype WHERE (evnttype_id=NEW.evntlog_evnttype_id);
    IF (_r.evnttype_name = 'SoCreated') THEN
      PERFORM createPrjToSale(NEW.evntlog_ord_id);
    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._evntlogafterinserttrigger() OWNER TO admin;

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

CREATE FUNCTION _gltransaltertrigger() 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 G/L Transactions once they have been created.';
  ELSIF (TG_OP = 'UPDATE') THEN
    SELECT company_external INTO _externalCompany
    FROM company JOIN accnt ON (company_number=accnt_company)
    WHERE (accnt_id=NEW.gltrans_accnt_id);
    IF (_externalCompany) THEN
      RAISE EXCEPTION 'Transactions are not allowed for G/L Accounts with External Company segments.';
    END IF;

    IF(OLD.gltrans_id != NEW.gltrans_id) THEN
      _updated := true;
    ELSIF(OLD.gltrans_date != NEW.gltrans_date) THEN
      _updated := true;
    ELSIF(OLD.gltrans_accnt_id != NEW.gltrans_accnt_id) THEN
      _updated := true;
    ELSIF(OLD.gltrans_amount != NEW.gltrans_amount) THEN
      _updated := true;
    ELSIF(OLD.gltrans_username != NEW.gltrans_username) THEN
      _updated := true;
    ELSIF( (OLD.gltrans_sequence IS NULL     AND NEW.gltrans_sequence IS NOT NULL)
        OR (OLD.gltrans_sequence IS NOT NULL AND NEW.gltrans_sequence IS NULL)
        OR (COALESCE(OLD.gltrans_sequence,0) != COALESCE(NEW.gltrans_sequence,0)) ) THEN
      _updated := true;
    ELSIF( (OLD.gltrans_created IS NULL     AND NEW.gltrans_created IS NOT NULL)
        OR (OLD.gltrans_created IS NOT NULL AND NEW.gltrans_created IS NULL)
        OR (COALESCE(OLD.gltrans_created,now()) != COALESCE(NEW.gltrans_created,now())) ) THEN
      _updated := true;
    ELSIF( (OLD.gltrans_source IS NULL     AND NEW.gltrans_source IS NOT NULL)
        OR (OLD.gltrans_source IS NOT NULL AND NEW.gltrans_source IS NULL)
        OR (COALESCE(OLD.gltrans_source,'') != COALESCE(NEW.gltrans_source,'')) ) THEN
      _updated := true;
    ELSIF( (OLD.gltrans_docnumber IS NULL     AND NEW.gltrans_docnumber IS NOT NULL)
        OR (OLD.gltrans_docnumber IS NOT NULL AND NEW.gltrans_docnumber IS NULL)
        OR (COALESCE(OLD.gltrans_docnumber,'') != COALESCE(NEW.gltrans_docnumber,'')) ) THEN
      _updated := true;
    ELSIF( (OLD.gltrans_doctype IS NULL     AND NEW.gltrans_doctype IS NOT NULL)
        OR (OLD.gltrans_doctype IS NOT NULL AND NEW.gltrans_doctype IS NULL)
        OR (COALESCE(OLD.gltrans_doctype,'') != COALESCE(NEW.gltrans_doctype,'')) ) THEN
      _updated := true;
    END IF;

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


ALTER FUNCTION public._gltransaltertrigger() OWNER TO admin;

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

CREATE FUNCTION _gltransinserttrigger() 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
  -- Start with privileges
  IF ((NEW.gltrans_doctype='JE') AND (NOT checkPrivilege('PostJournalEntries'))) THEN
      RAISE EXCEPTION 'You do not have privileges to create a Journal Entry.';
  END IF;

  SELECT company_external INTO _externalCompany
  FROM company JOIN accnt ON (company_number=accnt_company)
  WHERE (accnt_id=NEW.gltrans_accnt_id);
  IF (_externalCompany) THEN
    RAISE EXCEPTION 'Transactions are not allowed for G/L Accounts with External Company segments.';
  END IF;
  -- RAISE NOTICE '_gltransInsertTrigger(): company_external = %', _externalCompany;

  SELECT metric_value='t'
    INTO _reqNotes
    FROM metric
   WHERE(metric_name='MandatoryGLEntryNotes');
  IF (_reqNotes IS NULL) THEN
    _reqNotes := false;
  END IF;
  IF ((NEW.gltrans_doctype='JE') AND _reqNotes AND (TRIM(BOTH FROM COALESCE(NEW.gltrans_notes,''))='')) THEN
      RAISE EXCEPTION 'Notes are required for Journal Entries.';
  END IF;
  
  RETURN NEW;
END;
$$;


ALTER FUNCTION public._gltransinserttrigger() OWNER TO admin;

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

CREATE FUNCTION _grpprivtrigger() 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;
  _returnVal INTEGER;
BEGIN
  -- 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.grppriv_priv_id)))) THEN
    RAISE EXCEPTION 'Privilege id % does not exist or is part of a disabled package.',
                NEW.grppriv_priv_id;
    RETURN OLD;

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

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._grpprivtrigger() OWNER TO admin;

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

CREATE FUNCTION _imageasstrigger() 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 (NEW.imageass_source = 'INCDT') THEN
    UPDATE incdt SET incdt_updated = now() WHERE incdt_id = NEW.imageass_source_id;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._imageasstrigger() OWNER TO admin;

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

CREATE FUNCTION _incdtafterdeletetrigger() 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 = 'INCDT'
    AND charass_target_id = OLD.incdt_id;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._incdtafterdeletetrigger() OWNER TO admin;

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

CREATE FUNCTION _incdtbeforedeletetrigger() 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.incdt_id)
        AND (recur_parent_type='INCDT'));

     IF (_recurid IS NOT NULL) THEN
       SELECT MIN(incdt_id) INTO _newparentid
         FROM incdt
        WHERE ((incdt_recurring_incdt_id=OLD.inctd_id)
           AND (incdt_id!=OLD.incdt_id));

      -- client is responsible for warning about deleting a recurring incdt
      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._incdtbeforedeletetrigger() OWNER TO admin;

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

CREATE FUNCTION _incdtbeforetrigger() 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;
  _crmacct      INTEGER;

BEGIN

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

  -- Set the incident number if blank
  IF (TG_OP = 'INSERT') THEN
    IF (NEW.incdt_number IS NULL) THEN
      SELECT fetchIncidentNumber() INTO NEW.incdt_number;
    END IF;

    --- clear the number from the issue cache
    PERFORM clearNumberIssue('IncidentNumber', NEW.incdt_number);
  END IF;

  -- Description is required
  IF (LENGTH(COALESCE(NEW.incdt_summary,''))=0) THEN
    RAISE EXCEPTION 'You must supply a valid Incident Description.';
  END IF;

  -- CRM Account is required
  IF (NEW.incdt_crmacct_id IS NULL) THEN
    RAISE EXCEPTION 'You must supply a valid CRM Account.';
  END IF;

  -- Contact is required
  IF (NEW.incdt_cntct_id IS NULL) THEN
    RAISE EXCEPTION 'You must supply a valid Contact.';
  END IF;

  NEW.incdt_updated := now();

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._incdtbeforetrigger() OWNER TO admin;

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

CREATE FUNCTION _incdttrigger() 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;
  _counter	INTEGER :=  0;
  _whsId	INTEGER := -1;
  _evntType	TEXT;
  _cmnttypeid   INTEGER := -1;
  _cmntid       INTEGER := -1;
BEGIN

  SELECT cmnttype_id INTO _cmnttypeid
    FROM cmnttype
    WHERE (cmnttype_name='Notes to Comment');
  IF NOT FOUND OR _cmnttypeid IS NULL THEN
    _cmnttypeid := -1;
  END IF;

  IF (TG_OP = 'DELETE') THEN
--  This should never happen
    RETURN OLD;
  ELSIF (TG_OP = 'INSERT') THEN
    INSERT INTO incdthist
	  (incdthist_incdt_id,
	   incdthist_change, incdthist_target_id,
	   incdthist_descrip)
    VALUES(NEW.incdt_id,
	   'N', NULL,
	   'Incident Added');

    _evntType = 'NewIncident';

    IF (_cmnttypeid <> -1 AND COALESCE(NEW.incdt_descrip, '') <> '') THEN
      PERFORM postComment(_cmnttypeid, 'INCDT', NEW.incdt_id, NEW.incdt_descrip);
    END IF;
  ELSIF (TG_OP = 'UPDATE') THEN
    _evntType = 'UpdatedIncident';

    IF (COALESCE(NEW.incdt_cntct_id,-1) <> COALESCE(OLD.incdt_cntct_id,-1)) THEN
      INSERT INTO incdthist
	    (incdthist_incdt_id,
	     incdthist_change, incdthist_target_id,
	     incdthist_descrip)
      VALUES(NEW.incdt_id,
	     'C', NEW.incdt_cntct_id,
	     ('Contact Changed: "' ||
	       COALESCE((SELECT cntct_first_name || ' ' || cntct_last_name
			   FROM cntct
			  WHERE (cntct_id=OLD.incdt_cntct_id)), '')
	      || '" -> "' ||
	       COALESCE((SELECT cntct_first_name || ' ' || cntct_last_name
			   FROM cntct
			  WHERE (cntct_id=NEW.incdt_cntct_id)), '')
	      || '"') );
    END IF;

    IF (COALESCE(NEW.incdt_summary,'') <> COALESCE(OLD.incdt_summary,'')) THEN
      INSERT INTO incdthist
	    (incdthist_incdt_id,
	     incdthist_descrip)
      VALUES(NEW.incdt_id,
	     ('Description Updated: "' ||
	       COALESCE(OLD.incdt_summary, '') ||
	      '" -> "' ||
	       COALESCE(NEW.incdt_summary, '') ||
	      '"') );
    END IF;

    IF (COALESCE(NEW.incdt_descrip,'') <> COALESCE(OLD.incdt_descrip,'')) THEN
      INSERT INTO incdthist
	    (incdthist_incdt_id,
	     incdthist_descrip)
      VALUES(NEW.incdt_id,
	     ('Notes Updated: "' ||
	       substr(COALESCE(OLD.incdt_descrip, ''), 1, 20) ||
	      '..." -> "' ||
	       substr(COALESCE(NEW.incdt_descrip, ''), 1, 20) ||
	      '..."') );

      IF (_cmnttypeid <> -1) THEN
        -- find an existing comment
        SELECT comment_id
          INTO _cmntid
          FROM comment
         WHERE comment_source = 'INCDT'
           AND comment_source_id = NEW.incdt_id
           -- back out change for 21068
           -- AND comment_user = getEffectiveXtUser()
           AND comment_cmnttype_id = _cmnttypeid;
        IF FOUND THEN
          UPDATE comment SET comment_text = NEW.incdt_descrip
          WHERE comment_id = _cmntid;
        ELSE
          PERFORM postComment(_cmnttypeid, 'INCDT', NEW.incdt_id, NEW.incdt_descrip);
        END IF;
      END IF;
    END IF;

    IF (NEW.incdt_status <> OLD.incdt_status) THEN
      INSERT INTO incdthist
	    (incdthist_incdt_id,
	     incdthist_change, incdthist_target_id,
	     incdthist_descrip)
      VALUES(NEW.incdt_id,
	     'S', NULL,
	     ('Status Changed: ' ||
	      CASE WHEN(OLD.incdt_status='N') THEN 'New'
		   WHEN(OLD.incdt_status='F') THEN 'Feedback'
		   WHEN(OLD.incdt_status='C') THEN 'Confirmed'
		   WHEN(OLD.incdt_status='A') THEN 'Assigned'
		   WHEN(OLD.incdt_status='R') THEN 'Resolved'
		   WHEN(OLD.incdt_status='L') THEN 'Closed'
		   ELSE OLD.incdt_status
	      END
	      || ' -> ' ||
	      CASE WHEN(NEW.incdt_status='N') THEN 'New'
		   WHEN(NEW.incdt_status='F') THEN 'Feedback'
		   WHEN(NEW.incdt_status='C') THEN 'Confirmed'
		   WHEN(NEW.incdt_status='A') THEN 'Assigned'
		   WHEN(NEW.incdt_status='R') THEN 'Resolved'
		   WHEN(NEW.incdt_status='L') THEN 'Closed'
		   ELSE NEW.incdt_status
	      END
	      ) );
      IF (NEW.incdt_status = 'L') THEN
	_evntType = 'ClosedIncident';
      ELSIF (OLD.incdt_status = 'L') THEN
	_evntType = 'ReopenedIncident';
      END IF;
    END IF;

    IF (COALESCE(NEW.incdt_assigned_username,'') <> COALESCE(OLD.incdt_assigned_username,'')) THEN
      INSERT INTO incdthist
	    (incdthist_incdt_id,
	     incdthist_change, incdthist_target_id,
	     incdthist_descrip)
      VALUES(NEW.incdt_id,
	     'A', NULL,
	     ('Assigned to: "' ||
	       COALESCE(OLD.incdt_assigned_username, '') ||
	      '" -> "' ||
	       COALESCE(NEW.incdt_assigned_username, '') ||
	      '"') );
    END IF;

    IF (COALESCE(NEW.incdt_incdtcat_id,-1) <> COALESCE(OLD.incdt_incdtcat_id,-1)) THEN
      INSERT INTO incdthist
	    (incdthist_incdt_id,
	     incdthist_change, incdthist_target_id,
	     incdthist_descrip)
      VALUES(NEW.incdt_id,
	     'T', NEW.incdt_incdtcat_id,
	     ('Category Changed: ' ||
	       COALESCE((SELECT incdtcat_name
			   FROM incdtcat
			  WHERE (incdtcat_id=OLD.incdt_incdtcat_id)), '')
	      || ' -> ' ||
	       COALESCE((SELECT incdtcat_name
			   FROM incdtcat
			  WHERE (incdtcat_id=NEW.incdt_incdtcat_id)), '')
	      || '') );
    END IF;

    IF (COALESCE(NEW.incdt_incdtseverity_id,-1) <> COALESCE(OLD.incdt_incdtseverity_id,-1)) THEN
      INSERT INTO incdthist
	    (incdthist_incdt_id,
	     incdthist_change, incdthist_target_id,
	     incdthist_descrip)
      VALUES(NEW.incdt_id,
	     'V', NEW.incdt_incdtseverity_id,
	     ('Severity Changed: ' ||
	       COALESCE((SELECT incdtseverity_name
			   FROM incdtseverity
			  WHERE (incdtseverity_id=OLD.incdt_incdtseverity_id)), '')
	      || ' -> ' ||
	       COALESCE((SELECT incdtseverity_name
			   FROM incdtseverity
			  WHERE (incdtseverity_id=NEW.incdt_incdtseverity_id)), '')
	      || '') );
    END IF;

    IF (COALESCE(NEW.incdt_incdtpriority_id,-1) <> COALESCE(OLD.incdt_incdtpriority_id,-1)) THEN
      INSERT INTO incdthist
	    (incdthist_incdt_id,
	     incdthist_change, incdthist_target_id,
	     incdthist_descrip)
      VALUES(NEW.incdt_id,
	     'P', NEW.incdt_incdtpriority_id,
	     ('Priority Changed: ' ||
	       COALESCE((SELECT incdtpriority_name
			   FROM incdtpriority
			  WHERE (incdtpriority_id=OLD.incdt_incdtpriority_id)), '')
	      || ' -> ' ||
	       COALESCE((SELECT incdtpriority_name
			   FROM incdtpriority
			  WHERE (incdtpriority_id=NEW.incdt_incdtpriority_id)), '')
	      || '') );
    END IF;

    IF (COALESCE(NEW.incdt_incdtresolution_id,-1) <> COALESCE(OLD.incdt_incdtresolution_id,-1)) THEN
      INSERT INTO incdthist
	    (incdthist_incdt_id,
	     incdthist_change, incdthist_target_id,
	     incdthist_descrip)
      VALUES(NEW.incdt_id,
	     'E', NEW.incdt_incdtresolution_id,
	     ('Resolution Changed: ' ||
	       COALESCE((SELECT incdtresolution_name
			   FROM incdtresolution
			  WHERE (incdtresolution_id=OLD.incdt_incdtresolution_id)), '')
	      || ' -> ' ||
	       COALESCE((SELECT incdtresolution_name
			   FROM incdtresolution
			  WHERE (incdtresolution_id=NEW.incdt_incdtresolution_id)), '')
	      || '') );
    END IF;
  END IF;

    PERFORM postEvent(_evntType, 'IC', NEW.incdt_id,
                      NULL, NEW.incdt_number::TEXT,
                      NULL, NULL, NULL, NULL);

  RETURN NEW;
  END;
$$;


ALTER FUNCTION public._incdttrigger() OWNER TO admin;

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

CREATE FUNCTION _invcheadafterdeletetrigger() 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 = 'INV'
    AND charass_target_id = OLD.invchead_id;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._invcheadafterdeletetrigger() OWNER TO admin;

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

CREATE FUNCTION _invcheadaftertrigger() 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;
    _cohead_id INTEGER;

  BEGIN
--  Create a comment entry when on a Sales Order when an Invoice is Posted for that order

--  Cache the cmnttype_id for ChangeLog
    SELECT cmnttype_id INTO _cmnttypeid
    FROM cmnttype
    WHERE (cmnttype_name='ChangeLog');
    IF (FOUND) THEN
      IF (TG_OP = 'UPDATE') THEN
	IF ((OLD.invchead_posted != NEW.invchead_posted) AND NEW.invchead_posted) THEN
	  SELECT cohead_id INTO _cohead_id
	  FROM cohead
	  WHERE (cohead_number = OLD.invchead_ordernumber);
	  IF (FOUND) THEN
            PERFORM postComment( _cmnttypeid, 'S', _cohead_id,
                                 ('Invoice, ' || NEW.invchead_invcnumber || ', posted for this order') );
          END IF;
	END IF;
      END IF;
    END IF;
  RETURN NEW;
  END;
$$;


ALTER FUNCTION public._invcheadaftertrigger() OWNER TO admin;

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

CREATE FUNCTION _invcheadbeforetrigger() 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 = 'UPDATE') THEN
    IF (OLD.invchead_posted
      AND ((OLD.invchead_invcnumber != NEW.invchead_invcnumber)
        OR (OLD.invchead_invcdate != NEW.invchead_invcdate)
        OR (OLD.invchead_terms_id != NEW.invchead_terms_id)
        OR (OLD.invchead_salesrep_id != NEW.invchead_salesrep_id)
        OR (OLD.invchead_commission != NEW.invchead_commission)
        OR (OLD.invchead_taxzone_id != NEW.invchead_taxzone_id)
        OR (OLD.invchead_shipchrg_id != NEW.invchead_shipchrg_id)
        OR (OLD.invchead_prj_id != NEW.invchead_prj_id)
        OR (OLD.invchead_misc_accnt_id != NEW.invchead_misc_accnt_id)
        OR (OLD.invchead_misc_amount != NEW.invchead_misc_amount)
        OR (OLD.invchead_freight != NEW.invchead_freight))) THEN
      RAISE EXCEPTION 'Edit not allow on Posted Invoice.';
    END IF;
  END IF;

  IF (TG_OP = 'DELETE') THEN
    DELETE FROM invcheadtax
    WHERE (taxhist_parent_id=OLD.invchead_id);

    SELECT recur_id INTO _recurid
      FROM recur
     WHERE ((recur_parent_id=OLD.invchead_id)
        AND (recur_parent_type='I'));
    IF (_recurid IS NOT NULL) THEN
      SELECT invchead_id INTO _newparentid
        FROM invchead
       WHERE ((invchead_recurring_invchead_id=OLD.invchead_id)
          AND (invchead_id!=OLD.invchead_id))
       ORDER BY invchead_invcdate
       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 invchead SET invchead_recurring_invchead_id=_newparentid
         WHERE invchead_recurring_invchead_id=OLD.invchead_id
           AND invchead_id!=OLD.invchead_id;
      END IF;
    END IF;

    RETURN OLD;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._invcheadbeforetrigger() OWNER TO admin;

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

CREATE FUNCTION _invcheadtrigger() 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
    -- Something can go here
    RETURN OLD;
  END IF;

-- Insert new row
  IF (TG_OP = 'INSERT') THEN

  -- Calculate Freight Tax
    IF (NEW.invchead_freight <> 0) THEN
      PERFORM calculateTaxHist( 'invcheadtax',
                                NEW.invchead_id,
                                NEW.invchead_taxzone_id,
                                getFreightTaxtypeId(),
                                NEW.invchead_invcdate,
                                NEW.invchead_curr_id,
                                NEW.invchead_freight );
    END IF;

    --- clear the number from the issue cache
    PERFORM clearNumberIssue('InvcNumber', NEW.invchead_invcnumber);
  END IF;

-- Update row
  IF (TG_OP = 'UPDATE') THEN

    IF ( (NEW.invchead_freight <> OLD.invchead_freight) OR
         (COALESCE(NEW.invchead_taxzone_id,-1) <> COALESCE(OLD.invchead_taxzone_id,-1)) OR
         (NEW.invchead_invcdate <> OLD.invchead_invcdate) OR
         (NEW.invchead_curr_id <> OLD.invchead_curr_id) ) THEN
  -- Calculate invchead Tax
      PERFORM calculateTaxHist( 'invcheadtax',
                                NEW.invchead_id,
                                NEW.invchead_taxzone_id,
                                getFreightTaxtypeId(),
                                NEW.invchead_invcdate,
                                NEW.invchead_curr_id,
                                NEW.invchead_freight );
    END IF;

    IF ( (COALESCE(NEW.invchead_taxzone_id,-1) <> COALESCE(OLD.invchead_taxzone_id,-1)) OR
         (NEW.invchead_invcdate <> OLD.invchead_invcdate) OR
         (NEW.invchead_curr_id <> OLD.invchead_curr_id) ) THEN
  -- Calculate invcitem Tax
      IF (COALESCE(NEW.invchead_taxzone_id,-1) <> COALESCE(OLD.invchead_taxzone_id,-1)) THEN

        UPDATE invcitem SET invcitem_taxtype_id=getItemTaxType(invcitem_item_id,NEW.invchead_taxzone_id)
        WHERE (invcitem_invchead_id=NEW.invchead_id);

        PERFORM calculateTaxHist( 'invcitemtax',
                                  invcitem_id,
                                  NEW.invchead_taxzone_id,
                                  invcitem_taxtype_id,
                                  NEW.invchead_invcdate,
                                  NEW.invchead_curr_id,
                                  (invcitem_billed * invcitem_qty_invuomratio) *
                                  (invcitem_price / invcitem_price_invuomratio) )
        FROM invcitem
        WHERE (invcitem_invchead_id = NEW.invchead_id);
      END IF;
    END IF;

  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._invcheadtrigger() OWNER TO admin;

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

CREATE FUNCTION _invcitembeforetrigger() 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
  _itemfractional BOOLEAN;

BEGIN
  IF (TG_OP = 'DELETE') THEN
    DELETE FROM invcitemtax
    WHERE (taxhist_parent_id=OLD.invcitem_id);

    RETURN OLD;
  END IF;

  IF (TG_OP IN ('UPDATE','DELETE')) THEN
    IF (SELECT COUNT(invchead_id) > 0
        FROM invchead
        WHERE ((invchead_id=OLD.invcitem_invchead_id)
          AND (invchead_posted))) THEN
      RAISE EXCEPTION 'Edit not allowed on Posted Invoices.';
    END IF;
  END IF;

  -- If regular Item then enforce item_fractional
  IF (COALESCE(NEW.invcitem_item_id, -1) <> -1) THEN
    SELECT itemuomfractionalbyuom(NEW.invcitem_item_id, NEW.invcitem_qty_uom_id) INTO _itemfractional;
    IF (NOT _itemfractional) THEN
      IF (TRUNC(NEW.invcitem_ordered) <> NEW.invcitem_ordered) THEN
        RAISE EXCEPTION 'Item does not support fractional quantities';
      END IF;
      IF (TRUNC(NEW.invcitem_billed) <> NEW.invcitem_billed) THEN
        RAISE EXCEPTION 'Item does not support fractional quantities';
      END IF;
    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._invcitembeforetrigger() OWNER TO admin;

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

CREATE FUNCTION _invcitemtrigger() 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 Invoice Head
  SELECT * INTO _r
  FROM invchead
  WHERE (invchead_id=NEW.invcitem_invchead_id);
  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Invoice head not found';
  END IF;

-- Insert new row
  IF (TG_OP = 'INSERT') THEN

  -- Calculate Tax
      PERFORM calculateTaxHist( 'invcitemtax',
                                NEW.invcitem_id,
                                COALESCE(_r.invchead_taxzone_id, -1),
                                NEW.invcitem_taxtype_id,
                                COALESCE(_r.invchead_invcdate, CURRENT_DATE),
                                COALESCE(_r.invchead_curr_id, -1),
                                (NEW.invcitem_billed * NEW.invcitem_qty_invuomratio) *
                                (NEW.invcitem_price / NEW.invcitem_price_invuomratio) );
  END IF;

-- Update row
  IF (TG_OP = 'UPDATE') THEN

  -- Calculate Tax
    IF ( (NEW.invcitem_billed <> OLD.invcitem_billed) OR
         (NEW.invcitem_qty_invuomratio <> OLD.invcitem_qty_invuomratio) OR
         (NEW.invcitem_price <> OLD.invcitem_price) OR
         (NEW.invcitem_price_invuomratio <> OLD.invcitem_price_invuomratio) OR
         (COALESCE(NEW.invcitem_taxtype_id, -1) <> COALESCE(OLD.invcitem_taxtype_id, -1)) ) THEN
      PERFORM calculateTaxHist( 'invcitemtax',
                                NEW.invcitem_id,
                                COALESCE(_r.invchead_taxzone_id, -1),
                                NEW.invcitem_taxtype_id,
                                COALESCE(_r.invchead_invcdate, CURRENT_DATE),
                                COALESCE(_r.invchead_curr_id, -1),
                                (NEW.invcitem_billed * NEW.invcitem_qty_invuomratio) *
                                (NEW.invcitem_price / NEW.invcitem_price_invuomratio) );
    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._invcitemtrigger() OWNER TO admin;

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

CREATE FUNCTION _ipsassbeforetrigger() 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 NOT (checkPrivilege('MaintainPricingSchedules')) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Price Schedules.';
  END IF;

  -- Business logic, disallow invalid combinations
  IF (TG_OP IN ('INSERT','UPDATE')) THEN
    IF (LENGTH(COALESCE(NEW.ipsass_custtype_pattern,'')) != 0) THEN
      new.ipsass_cust_id 		= 	-1;
      new.ipsass_custtype_id 		= 	-1;
      new.ipsass_shipto_id		= 	-1;
      new.ipsass_shipto_pattern	=	'';
    ELSIF (COALESCE(NEW.ipsass_custtype_id,-1) > -1) THEN
      new.ipsass_cust_id 		= 	-1;
      new.ipsass_shipto_id		= 	-1;
      new.ipsass_shipto_pattern	=	'';
      new.ipsass_custtype_pattern	=	'';
    ELSIF (LENGTH(COALESCE(NEW.ipsass_shipto_pattern,'')) != 0) THEN
      new.ipsass_custtype_id 		= 	-1;
      new.ipsass_shipto_id		= 	-1;
      new.ipsass_custtype_pattern	=	'';
    ELSE
      new.ipsass_shipto_id		= 	COALESCE(NEW.ipsass_shipto_id,-1);
      new.ipsass_custtype_id 		= 	-1;
      new.ipsass_shipto_pattern	=	'';
      new.ipsass_custtype_pattern	=	'';
    END IF;

    RETURN NEW;
  ELSE
    RETURN OLD;
  END IF;
  
END;
$$;


ALTER FUNCTION public._ipsassbeforetrigger() OWNER TO admin;

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

CREATE FUNCTION _ipsheadbeforetrigger() 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 NOT (checkPrivilege('MaintainPricingSchedules')) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Price Schedules.';
  END IF;

  IF (TG_OP IN ('INSERT','UPDATE')) THEN
    RETURN NEW;
  ELSE
    RETURN OLD;
  END IF;
END;
$$;


ALTER FUNCTION public._ipsheadbeforetrigger() OWNER TO admin;

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

CREATE FUNCTION _ipsitemcharbeforetrigger() 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 NOT (checkPrivilege('MaintainPricingSchedules')) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Price Schedules.';
  END IF;
  
  IF (TG_OP IN ('INSERT','UPDATE')) THEN
    IF (SELECT (COUNT(item_id)=0)
        FROM ipsiteminfo JOIN item ON (item_id=ipsitem_item_id) 
        WHERE ((ipsitem_id=NEW.ipsitemchar_ipsitem_id)
        AND (item_config))) THEN
      RAISE EXCEPTION 'Characteristic prices may only be set on configured items.';
    ELSIF (SELECT (COUNT(item_id)=0)
        FROM ipsiteminfo JOIN item ON (item_id=ipsitem_item_id)
                         JOIN charass ON (charass_target_id=item_id AND charass_target_type='I') 
        WHERE ((ipsitem_id=NEW.ipsitemchar_ipsitem_id)
        AND (charass_char_id=NEW.ipsitemchar_char_id)
        AND (charass_value=NEW.ipsitemchar_value))) THEN
      RAISE EXCEPTION 'No characteristic with matching value exists for this item.';
    END IF;
    RETURN NEW;
  ELSE
    RETURN OLD;
  END IF;
END;
$$;


ALTER FUNCTION public._ipsitemcharbeforetrigger() OWNER TO admin;

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

CREATE FUNCTION _ipsiteminfobeforetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN

  --  Checks
  IF NOT (checkPrivilege('MaintainPricingSchedules')) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Price Schedules.';
  END IF;
  
  IF (TG_OP IN ('INSERT','UPDATE')) THEN
    RETURN NEW;
  ELSE
    RETURN OLD;
  END IF;
END;
$$;


ALTER FUNCTION public._ipsiteminfobeforetrigger() OWNER TO admin;

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

CREATE FUNCTION _item_uom_check() 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.

-- This trigger checks the all item uom_id columns have valid conversion ratios.
BEGIN

  IF (NEW.item_active AND NEW.item_sold) THEN
    IF (NEW.item_inv_uom_id <> NEW.item_price_uom_id) THEN
      BEGIN
        PERFORM itemuomtouomratio(NEW.item_id, NEW.item_inv_uom_id, NEW.item_price_uom_id)
        FROM item
        WHERE TRUE
          AND item_id = NEW.item_id;
      EXCEPTION
        WHEN SQLSTATE 'P0001' THEN
          RAISE EXCEPTION 'An invalid UOM was set on this item. Please verify that the Unit Price UOM has a conversion to the Inventory UOM.';
      END;
    END IF;

  END IF;

  RETURN NEW;

END;
$$;


ALTER FUNCTION public._item_uom_check() OWNER TO admin;

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

CREATE FUNCTION _itemafterdeletetrigger() 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 = 'I'
    AND charass_target_id = OLD.item_id;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._itemafterdeletetrigger() OWNER TO admin;

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

CREATE FUNCTION _itemaftertrigger() 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
-- Privilege Checks
   IF (NOT checkPrivilege('MaintainItemMasters')) THEN
     RAISE EXCEPTION 'You do not have privileges to maintain Items.';
   END IF;

-- Integrity checks
  IF (TG_OP = 'UPDATE') THEN
    IF ((OLD.item_type <> NEW.item_type) AND (NEW.item_type = 'L')) THEN
      IF (SELECT COUNT(*) != 0 FROM bomitem WHERE (bomitem_item_id = OLD.item_id)) THEN
        RAISE EXCEPTION 'This item is part of one or more Bills of Materials and cannot be a Planning Item.';
      END IF;
    END IF;

    IF ((OLD.item_type <> NEW.item_type) AND
       (NEW.item_type IN ('R','S','T'))) THEN
      IF (SELECT COUNT(*) != 0
        FROM itemsite
        WHERE ((itemsite_item_id=OLD.item_id)
        AND (itemsite_qtyonhand + qtyallocated(itemsite_id,startoftime(),endoftime()) +
	   qtyordered(itemsite_id,startoftime(),endoftime()) > 0 ))) THEN
          RAISE EXCEPTION 'Item type not allowed when there are itemsites with quantities with on hand quantities or pending inventory activity for this item.';
      END IF;
    END IF;
-- If type changed remove costs and deactivate item sites
    IF (NEW.item_type <> OLD.item_type) THEN
      PERFORM updateCost(itemcost_id, 0) FROM itemcost WHERE (itemcost_item_id=OLD.item_id);
      UPDATE itemsite SET itemsite_active=false WHERE (itemsite_item_id=OLD.item_id);
      IF (NEW.item_type = 'R') THEN
        UPDATE itemsite SET itemsite_controlmethod='N' WHERE (itemsite_item_id=OLD.item_id);
      END IF;
    END IF;
  END IF;

  IF ( SELECT (metric_value='t')
       FROM metric
       WHERE (metric_name='ItemChangeLog') ) 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, 'I', NEW.item_id, 'Created');

      ELSIF (TG_OP = 'UPDATE') THEN
        IF (OLD.item_active <> NEW.item_active) THEN
          IF (NEW.item_active) THEN
            PERFORM postComment(_cmnttypeid, 'I', NEW.item_id, 'Activated');
          ELSE
            PERFORM postComment(_cmnttypeid, 'I', NEW.item_id, 'Deactivated');
          END IF;
        END IF;

        IF (OLD.item_descrip1 <> NEW.item_descrip1) THEN
          PERFORM postComment( _cmnttypeid, 'I', NEW.item_id,
                               ( 'Description 1 Changed from "' || OLD.item_descrip1 ||
                                 '" to "' || NEW.item_descrip1 || '"' ) );
        END IF;

        IF (OLD.item_descrip2 <> NEW.item_descrip2) THEN
          PERFORM postComment( _cmnttypeid, 'I', NEW.item_id,
                               ( 'Description 2 Changed from "' || OLD.item_descrip2 ||
                                 '" to "' || NEW.item_descrip2 || '"' ) );
        END IF;

        IF (OLD.item_inv_uom_id <> NEW.item_inv_uom_id) THEN
          PERFORM postComment( _cmnttypeid, 'I', NEW.item_id,
                               ( 'Inventory UOM Changed from "' ||
                                 (SELECT uom_name FROM uom WHERE uom_id=OLD.item_inv_uom_id) ||
                                 '" (' || CAST(OLD.item_inv_uom_id AS TEXT) ||
                                 ') to "' ||
                                 (SELECT uom_name FROM uom WHERE uom_id=NEW.item_inv_uom_id) ||
                                 '" (' || CAST(NEW.item_inv_uom_id AS TEXT) || ')' ) );
        END IF;

        IF (OLD.item_sold <> NEW.item_sold) THEN
          PERFORM postComment( _cmnttypeid, 'I', NEW.item_id,
                               CASE WHEN (NEW.item_sold) THEN 'Sold Changed from FALSE to TRUE'
                                    ELSE 'Sold Changed from TRUE to FALSE'
                               END );
        END IF;

        IF (OLD.item_picklist <> NEW.item_picklist) THEN
          PERFORM postComment( _cmnttypeid, 'I', NEW.item_id,
                               CASE WHEN (NEW.item_picklist) THEN 'Pick List Changed from FALSE to TRUE'
                                    ELSE 'Pick List Changed from TRUE to FALSE'
                               END );
        END IF;

        IF (OLD.item_fractional <> NEW.item_fractional) THEN
          PERFORM postComment( _cmnttypeid, 'I', NEW.item_id,
                               CASE WHEN (NEW.item_fractional) THEN 'Fractional Changed from FALSE to TRUE'
                                    ELSE 'Fractional Changed from TRUE to FALSE'
                               END );
        END IF;

        IF (OLD.item_exclusive <> NEW.item_exclusive) THEN
          PERFORM postComment( _cmnttypeid, 'I', NEW.item_id,
                               CASE WHEN (NEW.item_exclusive) THEN 'Exclusive Changed from FALSE to TRUE'
                                    ELSE 'Exclusive Changed from TRUE to FALSE'
                               END );
        END IF;
        IF (OLD.item_config <> NEW.item_config) THEN
          PERFORM postComment( _cmnttypeid, 'I', NEW.item_id,
                               CASE WHEN (NEW.item_config) THEN 'Configured Changed from FALSE to TRUE'
                                    ELSE 'Configured Changed from TRUE to FALSE'
                               END );
        END IF;

        IF (OLD.item_listprice <> NEW.item_listprice) THEN
          PERFORM postComment( _cmnttypeid, 'I', NEW.item_id,
                               ( 'List Price Changed from "' || formatSalesPrice(OLD.item_listprice) ||
                                 '" to "' || formatSalesPrice(NEW.item_listprice) || '"' ) );
        END IF;

-- Add New stuff

        IF (OLD.item_type <> NEW.item_type) THEN
          PERFORM postComment( _cmnttypeid, 'I', NEW.item_id,
                               ( 'Type Changed from "' || OLD.item_type ||
                                 '" to "' || NEW.item_type || '"' ) );
        END IF;

        IF (OLD.item_price_uom_id <> NEW.item_price_uom_id) THEN
          PERFORM postComment( _cmnttypeid, 'I', NEW.item_id,
                               ( 'Price UOM Changed from "' ||
                                 (SELECT uom_name FROM uom WHERE uom_id=OLD.item_price_uom_id) ||
                                 '" (' || CAST(OLD.item_price_uom_id AS TEXT) ||
                                 ') to "' ||
                                 (SELECT uom_name FROM uom WHERE uom_id=NEW.item_price_uom_id) ||
                                 '" (' || CAST(NEW.item_price_uom_id AS TEXT) || ')' ) );
        END IF;

        IF (OLD.item_classcode_id <> NEW.item_classcode_id) THEN
          PERFORM postComment( _cmnttypeid, 'I', NEW.item_id,
                               ( 'Class Code Changed from "' ||
                                 (SELECT classcode_code || '-' || classcode_descrip FROM classcode WHERE classcode_id=OLD.item_classcode_id) ||
                                 '" (' || CAST(OLD.item_classcode_id AS TEXT) ||
                                 ') to "' ||
                                 (SELECT classcode_code || '-' || classcode_descrip FROM classcode WHERE classcode_id=NEW.item_classcode_id) ||
                                 '" (' || CAST(NEW.item_classcode_id AS TEXT) || ')' ) );
        END IF;

        IF (OLD.item_freightclass_id <> NEW.item_freightclass_id) THEN
          PERFORM postComment( _cmnttypeid, 'I', NEW.item_id,
                               ( 'Freight Class Changed from "' ||
                                 (SELECT freightclass_code || '-' || freightclass_descrip FROM freightclass WHERE freightclass_id=OLD.item_freightclass_id) ||
                                 '" (' || CAST(OLD.item_freightclass_id AS TEXT) ||
                                 ') to "' ||
                                 (SELECT freightclass_code || '-' || freightclass_descrip FROM freightclass WHERE freightclass_id=NEW.item_freightclass_id) ||
                                 '" (' || CAST(NEW.item_freightclass_id AS TEXT) || ')' ) );
        END IF;

        IF (OLD.item_prodcat_id <> NEW.item_prodcat_id) THEN
          PERFORM postComment( _cmnttypeid, 'I', NEW.item_id,
                               ( 'Product Category Changed from "' ||
                                 (SELECT prodcat_code || '-' || prodcat_descrip FROM prodcat WHERE prodcat_id=OLD.item_prodcat_id) ||
                                 '" (' || CAST(OLD.item_prodcat_id AS TEXT) ||
                                 ') to "' ||
                                 (SELECT prodcat_code || '-' || prodcat_descrip FROM prodcat WHERE prodcat_id=NEW.item_prodcat_id) ||
                                 '" (' || CAST(NEW.item_prodcat_id AS TEXT) || ')' ) );
        END IF;

        IF (OLD.item_upccode <> NEW.item_upccode) THEN
          PERFORM postComment( _cmnttypeid, 'I', NEW.item_id,
                               ( 'UPC Code Changed from "' || OLD.item_upccode ||
                                 '" to "' || NEW.item_upccode || '"' ) );
        END IF;

        IF (OLD.item_prodweight <> NEW.item_prodweight) THEN
          PERFORM postComment( _cmnttypeid, 'I', NEW.item_id,
                               ( 'Product Weight Changed from "' || formatWeight(OLD.item_prodweight) ||
                                 '" to "' || formatWeight(NEW.item_prodweight) || '"' ) );
        END IF;

        IF (OLD.item_packweight <> NEW.item_packweight) THEN
          PERFORM postComment( _cmnttypeid, 'I', NEW.item_id,
                               ( 'Packaging Weight Changed from "' || formatWeight(OLD.item_packweight) ||
                                 '" to "' || formatWeight(NEW.item_packweight) || '"' ) );
        END IF;

        IF (OLD.item_maxcost <> NEW.item_maxcost) THEN
          PERFORM postComment( _cmnttypeid, 'I', NEW.item_id,
                               ( 'Maximum Desired Cost Changed from "' || formatCost(OLD.item_maxcost) ||
                                 '" to "' || formatCost(NEW.item_maxcost) || '"' ) );
        END IF;

        IF (OLD.item_listcost <> NEW.item_listcost) THEN
          PERFORM postComment( _cmnttypeid, 'I', NEW.item_id,
                               ( 'List Cost Changed from "' || formatCost(OLD.item_listcost) ||
                                 '" to "' || formatCost(NEW.item_listcost) || '"' ) );
        END IF;
-- End changes

      END IF;
    END IF;
  END IF;

  IF (TG_OP = 'DELETE') THEN
    DELETE FROM imageass WHERE ((imageass_source_id=OLD.item_id) AND (imageass_source='I'));
    DELETE FROM url WHERE ((url_source_id=OLD.item_id) AND (url_source='I'));
    DELETE FROM docass WHERE docass_source_id = OLD.item_id AND docass_source_type = 'I';
    DELETE FROM docass WHERE docass_target_id = OLD.item_id AND docass_target_type = 'I';

    RETURN OLD;
  END IF;

  RETURN NEW;

END;
$$;


ALTER FUNCTION public._itemaftertrigger() OWNER TO admin;

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

CREATE FUNCTION _itemaliastrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2012 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN

-- Privilege Checks
   IF (NOT checkPrivilege('MaintainItemMasters')) THEN
     RAISE EXCEPTION 'You do not have privileges to maintain Item Aliases.';
   END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._itemaliastrigger() OWNER TO admin;

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

CREATE FUNCTION _itemcostaftertrigger() 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
  _itemNumber TEXT;
  _maxCost NUMERIC;
  _oldStdCost NUMERIC;
  _oldActCost NUMERIC;
  _actualCost NUMERIC;
  _standardCost NUMERIC;

BEGIN

--  Create Event if Standard or Actual Cost is greater than Max Cost

IF NOT EXISTS(SELECT 1
     FROM evntnot
       JOIN evnttype ON (evnttype_id = evntnot_evnttype_id)
       JOIN usrpref ON (evntnot_username = usrpref_username)
     WHERE
          evnttype_name = 'CostExceedsMaxDesired'
          AND usrpref_name = 'active'
          AND usrpref_value = 't')
   THEN
     RETURN NEW;
END IF;

  SELECT item_number, item_maxcost, actcost(item_id), stdcost(item_id) INTO _itemNumber, _maxCost, _actualCost, _standardCost
  FROM item
  WHERE (item_id=NEW.itemcost_item_id);

  IF (_maxCost > 0.0) THEN
   -- IF (_standardCost > _maxCost)
      IF NOT EXISTS(SELECT 1 --COUNT(evntlog_id)
                    FROM
                      evntlog, evnttype
                      WHERE evntlog_evnttype_id = evnttype_id
                      AND evntlog_number LIKE
                          (_itemNumber || ' -Standard- New:' || '%')

                      AND (evntlog_dispatched IS NULL)
                      AND CAST(evntlog_evnttime AS DATE) = current_date

                      )
                      AND (_standardCost > _maxCost) THEN


      IF (TG_OP = 'INSERT') THEN
        _oldStdCost := 0;
        _oldActCost := 0;
      ELSE
        _oldStdCost := OLD.itemcost_stdcost;
        _oldActCost := OLD.itemcost_stdcost;
      END IF;
      PERFORM postEvent('CostExceedsMaxDesired', NULL, NEW.itemcost_item_id,
                        itemsite_warehous_id,
                        (_itemNumber || ' -Standard- ' ||
                         'New: ' || formatCost(_standardCost) ||
                         ' Max: '|| formatCost(_MaxCost)),
                        NEW.itemcost_stdcost, _oldStdCost,
                        NULL, NULL)
      FROM itemsite
      WHERE (itemsite_item_id=NEW.itemcost_item_id);
    END IF;
       IF NOT EXISTS(
                     SELECT 1 FROM
                      evntlog, evnttype
                      WHERE evntlog_evnttype_id = evnttype_id
                      AND evntlog_number LIKE
                          (_itemNumber || ' -Actual- New:' || '%')

                      AND (evntlog_dispatched IS NULL)
                      AND CAST(evntlog_evnttime AS DATE) = current_date
                      )

                 AND  (_actualCost > _maxCost)
          THEN

      PERFORM postEvent('CostExceedsMaxDesired', NULL, NEW.itemcost_item_id,
                        itemsite_warehous_id,
                        (_itemNumber || ' -Actual- ' ||
                         'New: ' || formatCost(_actualCost) ||
                         ' Max: '|| formatCost(_MaxCost)),
                        NEW.itemcost_actcost, _oldActCost,
                        NULL, NULL)
      FROM itemsite
      WHERE (itemsite_item_id=NEW.itemcost_item_id);
    END IF;
  END IF;

  RETURN NEW;

END;
$$;


ALTER FUNCTION public._itemcostaftertrigger() OWNER TO admin;

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

CREATE FUNCTION _itemcosttrigger() 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

  --Privilege Checks
  IF ( (TG_OP = 'INSERT') AND (NOT checkPrivilege('CreateCosts')) AND (NOT checkPrivilege('PostVouchers')) ) THEN
    RAISE EXCEPTION 'You do not have privileges to enter Item Costs.';
  END IF;

  IF ( (TG_OP = 'UPDATE') AND (NOT checkPrivilege('EnterActualCosts')) AND (NOT checkPrivilege('PostVouchers')) AND (NOT checkPrivilege('UpdateActualCosts')) AND (NOT checkPrivilege('PostActualCosts')) AND (NOT checkPrivilege('PostStandardCosts')) ) THEN
    RAISE EXCEPTION 'You do not have privileges to update Item Costs.';
  END IF;

  IF ( (TG_OP = 'DELETE') AND (NOT checkPrivilege('DeleteCosts')) ) THEN
    RAISE EXCEPTION 'You do not have privileges to delete Item Costs.';
  END IF;

  IF (TG_OP = 'UPDATE') THEN
    IF (NEW.itemcost_actcost <> OLD.itemcost_actcost OR
        NEW.itemcost_curr_id <> OLD.itemcost_curr_id) THEN
      INSERT INTO costhist
      ( costhist_item_id, costhist_costelem_id, costhist_type,
        costhist_lowlevel, costhist_username, costhist_date,
        costhist_oldcost, costhist_newcost,
        costhist_oldcurr_id, costhist_newcurr_id )
      VALUES
      ( NEW.itemcost_item_id, NEW.itemcost_costelem_id, 'A',
        NEW.itemcost_lowlevel, getEffectiveXtUser(), CURRENT_TIMESTAMP,
        OLD.itemcost_actcost, NEW.itemcost_actcost,
        OLD.itemcost_curr_id, NEW.itemcost_curr_id );
    END IF;

    IF (NEW.itemcost_stdcost <> OLD.itemcost_stdcost) THEN
      INSERT INTO costhist
      ( costhist_item_id, costhist_costelem_id, costhist_type,
        costhist_lowlevel, costhist_username, costhist_date,
        costhist_oldcost, costhist_newcost,
        costhist_oldcurr_id, costhist_newcurr_id )
      VALUES
      ( NEW.itemcost_item_id, NEW.itemcost_costelem_id, 'S',
        NEW.itemcost_lowlevel, getEffectiveXtUser(), CURRENT_TIMESTAMP,
        OLD.itemcost_stdcost, NEW.itemcost_stdcost,
        baseCurrId(), baseCurrId() );
    END IF;

    RETURN NEW;

  ELSIF (TG_OP = 'INSERT') THEN
    INSERT INTO costhist
    ( costhist_item_id, costhist_costelem_id, costhist_type,
      costhist_lowlevel, costhist_username, costhist_date,
      costhist_oldcost, costhist_newcost,
      costhist_oldcurr_id, costhist_newcurr_id )
    VALUES
    ( NEW.itemcost_item_id, NEW.itemcost_costelem_id, 'N',
      NEW.itemcost_lowlevel, getEffectiveXtUser(), CURRENT_TIMESTAMP,
      0, NEW.itemcost_actcost,
      baseCurrId(), NEW.itemcost_curr_id );

    RETURN NEW;

  ELSIF (TG_OP = 'DELETE') THEN
    INSERT INTO costhist
    ( costhist_item_id, costhist_costelem_id, costhist_type,
      costhist_lowlevel, costhist_username, costhist_date,
      costhist_oldcost, costhist_newcost,
      costhist_oldcurr_id, costhist_newcurr_id )
    VALUES
    ( OLD.itemcost_item_id, OLD.itemcost_costelem_id, 'D',
      OLD.itemcost_lowlevel, getEffectiveXtUser(), CURRENT_TIMESTAMP,
      OLD.itemcost_stdcost, 0,
      OLD.itemcost_curr_id, baseCurrId() );

    RETURN OLD;
  END IF;

END;
$$;


ALTER FUNCTION public._itemcosttrigger() OWNER TO admin;

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

CREATE FUNCTION _itemsiteaftertrigger() 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
  _state INTEGER;
  _wasLocationControl BOOLEAN;
  _isLocationControl BOOLEAN;
  _wasLotSerial BOOLEAN;
  _isLotSerial BOOLEAN;
  _wasPerishable BOOLEAN;
  _isPerishable BOOLEAN;
  _qty NUMERIC;
  _maint BOOLEAN;
  _cost NUMERIC;
  _variance NUMERIC;
  _application TEXT;

BEGIN
-- Cache Application
  SELECT fetchMetricText('Application') INTO _application;

-- Check if we are doing maintenance
  IF (TG_OP = 'INSERT') THEN
    _maint := TRUE;
  ELSIF (TG_OP = 'UPDATE') THEN
    IF ((OLD.itemsite_item_id           != NEW.itemsite_item_id)
     OR (OLD.itemsite_warehous_id       != NEW.itemsite_warehous_id)
     OR (OLD.itemsite_reorderlevel      != NEW.itemsite_reorderlevel)
     OR (OLD.itemsite_ordertoqty        != NEW.itemsite_ordertoqty)
     OR (OLD.itemsite_cyclecountfreq    != NEW.itemsite_cyclecountfreq)
     OR (OLD.itemsite_planning_type     != NEW.itemsite_planning_type)
     OR (OLD.itemsite_posupply          != NEW.itemsite_posupply)
     OR (OLD.itemsite_wosupply          != NEW.itemsite_wosupply)
     OR (OLD.itemsite_loccntrl          != NEW.itemsite_loccntrl)
     OR (OLD.itemsite_safetystock       != NEW.itemsite_safetystock)
     OR (OLD.itemsite_minordqty         != NEW.itemsite_minordqty)
     OR (OLD.itemsite_multordqty        != NEW.itemsite_multordqty)
     OR (OLD.itemsite_leadtime          != NEW.itemsite_leadtime)
     OR (OLD.itemsite_abcclass          != NEW.itemsite_abcclass)
     OR (OLD.itemsite_controlmethod     != NEW.itemsite_controlmethod)
     OR (OLD.itemsite_active            != NEW.itemsite_active)
     OR (OLD.itemsite_plancode_id       != NEW.itemsite_plancode_id)
     OR (OLD.itemsite_costcat_id        != NEW.itemsite_costcat_id)
     OR (OLD.itemsite_eventfence        != NEW.itemsite_eventfence)
     OR (OLD.itemsite_sold              != NEW.itemsite_sold)
     OR (OLD.itemsite_stocked           != NEW.itemsite_stocked)
     OR (OLD.itemsite_location_id       != NEW.itemsite_location_id)
     OR (OLD.itemsite_recvlocation_id   != NEW.itemsite_recvlocation_id)
     OR (OLD.itemsite_issuelocation_id  != NEW.itemsite_issuelocation_id)
     OR (OLD.itemsite_location_dist     != NEW.itemsite_location_dist)
     OR (OLD.itemsite_recvlocation_dist != NEW.itemsite_recvlocation_dist)
     OR (OLD.itemsite_issuelocation_dist != NEW.itemsite_issuelocation_dist)
     OR (OLD.itemsite_useparams         != NEW.itemsite_useparams)
     OR (OLD.itemsite_useparamsmanual   != NEW.itemsite_useparamsmanual)
     OR (OLD.itemsite_soldranking       != NEW.itemsite_soldranking)
     OR (OLD.itemsite_createpr          != NEW.itemsite_createpr)
     OR (OLD.itemsite_location          != NEW.itemsite_location)
     OR (OLD.itemsite_location_comments != NEW.itemsite_location_comments)
     OR (OLD.itemsite_notes             != NEW.itemsite_notes)
     OR (OLD.itemsite_perishable        != NEW.itemsite_perishable)
     OR (OLD.itemsite_autoabcclass      != NEW.itemsite_autoabcclass)
     OR (OLD.itemsite_ordergroup        != NEW.itemsite_ordergroup)
     OR (OLD.itemsite_disallowblankwip  != NEW.itemsite_disallowblankwip)
     OR (OLD.itemsite_maxordqty         != NEW.itemsite_maxordqty)
     OR (OLD.itemsite_mps_timefence     != NEW.itemsite_mps_timefence)
     OR (OLD.itemsite_createwo          != NEW.itemsite_createwo)
     OR (OLD.itemsite_warrpurc          != NEW.itemsite_warrpurc)
     OR (OLD.itemsite_costmethod        != NEW.itemsite_costmethod)
     OR (OLD.itemsite_autoreg           != NEW.itemsite_autoreg)
     OR (OLD.itemsite_lsseq_id          != NEW.itemsite_lsseq_id) ) THEN
      IF (OLD.itemsite_item_id != NEW.itemsite_item_id) THEN
        RAISE EXCEPTION 'The item number on an itemsite may not be changed.';
      ELSIF (OLD.itemsite_warehous_id != NEW.itemsite_warehous_id) THEN
        RAISE EXCEPTION 'The warehouse code on an itemsite may not be changed.';
      END IF;
      _maint := TRUE;
    END IF;
  ELSE
    _maint := FALSE;
  END IF;

  IF (_maint) THEN -- Begin Maintenance
-- Privilege Checks
    IF ( NOT checkPrivilege('MaintainItemSites') ) THEN
       RAISE EXCEPTION 'You do not have privileges to maintain Item Sites.';
    END IF;

-- Override values to avoid invalid data combinations
    IF (NOT NEW.itemsite_posupply) THEN
      UPDATE itemsite SET
        itemsite_createpr = FALSE
      WHERE (itemsite_id=NEW.itemsite_id);
    END IF;
    IF (NOT NEW.itemsite_wosupply) THEN
      UPDATE itemsite SET
        itemsite_createwo = FALSE
      WHERE (itemsite_id=NEW.itemsite_id);
    END IF;

    IF (NEW.itemsite_controlmethod NOT IN ('S','L')) THEN
      UPDATE itemsite SET
        itemsite_perishable = FALSE,
        itemsite_warrpurc = FALSE,
        itemsite_autoreg = FALSE,
        itemsite_lsseq_id = NULL
      WHERE (itemsite_id=NEW.itemsite_id);
    END IF;

    IF (NOT NEW.itemsite_loccntrl) THEN
      UPDATE itemsite SET
        itemsite_disallowblankwip = FALSE
      WHERE (itemsite_id=NEW.itemsite_id);
    END IF;

    IF (NOT NEW.itemsite_useparams) THEN
      UPDATE itemsite SET
        itemsite_reorderlevel    = 0,
        itemsite_ordertoqty      = 0,
        itemsite_minordqty       = 0,
        itemsite_maxordqty       = 0,
        itemsite_multordqty      = 0,
        itemsite_useparamsmanual = FALSE
      WHERE (itemsite_id = NEW.itemsite_id);
    END IF;

-- Integrity check

    -- Both insert and update
    IF ( (NEW.itemsite_controlmethod IN ('S', 'L')) AND
         (NEW.itemsite_location_dist OR NEW.itemsite_recvlocation_dist OR NEW.itemsite_issuelocation_dist) ) THEN
      RAISE EXCEPTION 'You cannot auto-distribute Lot/Serial controlled Item Sites.';
    END IF;

    IF (TG_OP = 'INSERT') THEN
      -- Handle MLC logic
      IF ( (NEW.itemsite_loccntrl) AND (NEW.itemsite_warehous_id IS NOT NULL) ) THEN
        IF (SELECT count(*)=0
            FROM location
            WHERE ((location_warehous_id=NEW.itemsite_warehous_id)
            AND ( (NOT location_restrict) OR
                ( (location_restrict) AND
                (location_id IN ( SELECT locitem_location_id
                                  FROM locitem
                                  WHERE (locitem_item_id=NEW.itemsite_item_id) ) ) ) ))) THEN
          RAISE EXCEPTION 'You must first create at least one valid
	    		  Location for this Item Site before it may be
	   	          multiply located.';
        END IF;
      END IF;
    END IF;

    IF (TG_OP = 'UPDATE') THEN

-- Integrity check
      IF (NOT OLD.itemsite_loccntrl AND NEW.itemsite_loccntrl) THEN
        IF (SELECT count(*)=0
          FROM location
          WHERE ((location_warehous_id=NEW.itemsite_warehous_id)
          AND ( (NOT location_restrict) OR
              ( (location_restrict) AND
              (location_id IN ( SELECT locitem_location_id
                                FROM locitem
                                WHERE (locitem_item_id=NEW.itemsite_item_id) ) ) ) ))) THEN
           RAISE EXCEPTION 'You must first create at least one valid
			  Location for this Item Site before it may be
		          multiply located.';
        END IF;
      END IF;

-- Update detail records based on control method changes
      _wasLocationControl := OLD.itemsite_loccntrl;
      _isLocationControl := NEW.itemsite_loccntrl;
      _wasLotSerial := OLD.itemsite_controlmethod IN ('S','L');
      _isLotSerial := NEW.itemsite_controlmethod IN ('S','L');
      _wasPerishable := OLD.itemsite_perishable;
      _isPerishable := NEW.itemsite_perishable;
      _state := 0;

      IF ( (_wasLocationControl) AND (_isLocationControl) ) THEN
        _state := 10;
      ELSIF ( (NOT _wasLocationControl) AND (NOT _isLocationControl) ) THEN
        _state := 20;
      ELSIF ( (NOT _wasLocationControl) AND (_isLocationControl) ) THEN
        _state := 30;
      ELSIF ( (_wasLocationControl) AND (NOT _isLocationControl) ) THEN
        _state := 40;
      END IF;

      IF ( (_wasLotSerial) AND (_isLotSerial) ) THEN
        _state := _state + 1;
      ELSIF ( (NOT _wasLotSerial) AND (NOT _isLotSerial) ) THEN
        _state := _state + 2;
      ELSIF ( (NOT _wasLotSerial) AND (_isLotSerial) ) THEN
        _state := _state + 3;
      ELSIF ( (_wasLotSerial) AND (NOT _isLotSerial) ) THEN
        _state := _state + 4;
      END IF;

      IF ( (_application = 'Standard') AND (_state IN (41, 43, 14, 34, 24, 42, 44)) ) THEN
        -- Check for Reservations
        IF (SELECT COUNT(*) > 0
            FROM itemloc JOIN reserve ON (reserve_supply_id=itemloc_id AND reserve_supply_type='I')
            WHERE (itemloc_itemsite_id=OLD.itemsite_id)) THEN
          RAISE EXCEPTION 'Sales Order Reservations by Location exist for this Item Site';
        END IF;
      END IF;

      IF (_state IN (41, 43)) THEN
        PERFORM consolidateLotSerial(OLD.itemsite_id);
      ELSIF (_state IN (14, 34)) THEN
        PERFORM consolidateLocations(OLD.itemsite_id);
      ELSIF (_state IN (24, 42, 44)) THEN
        DELETE FROM itemloc
        WHERE (itemloc_itemsite_id=OLD.itemsite_id);
      END IF;

     IF (NEW.itemsite_qtyonhand != 0) THEN
--  Handle detail creation
--  Create itemloc records if they do not exist
       IF (_state IN (23, 32, 33)) THEN
          INSERT INTO itemloc
            ( itemloc_itemsite_id, itemloc_location_id,
              itemloc_expiration, itemloc_qty )
            VALUES
            ( NEW.itemsite_id, -1,
              endOfTime(), NEW.itemsite_qtyonhand );
        END IF;

--  Handle Location distribution
        IF (_state IN (31, 32, 33, 34)) THEN
          IF (SELECT (COUNT(*)=1)
              FROM location
              WHERE ((location_id=NEW.itemsite_location_id)
              AND (location_warehous_id=NEW.itemsite_warehous_id)
              AND ( (NOT location_restrict) OR
                  ( (location_restrict) AND
                  (location_id IN ( SELECT locitem_location_id
                                    FROM locitem
                                    WHERE (locitem_item_id=NEW.itemsite_item_id) ) ) ) ))) THEN
           PERFORM initialDistribution(NEW.itemsite_id, NEW.itemsite_location_id);
          ELSE
            RAISE EXCEPTION 'A valid default location must be selected to distribute existing inventory to.';
          END IF;
        END IF;

--  Handle Lot/Serial distribution
        IF ( (_state = 13) OR (_state = 23) OR (_state = 33) OR (_state = 43) ) THEN
          RAISE WARNING 'You should now use the Reassign Lot/Serial # window to assign Lot/Serial #s.';
        END IF;
      END IF;
      IF (OLD.itemsite_costmethod='A' AND NEW.itemsite_costmethod='S') THEN
        -- TODO: Average costing cost method change
        SELECT stdcost(NEW.itemsite_item_id) * NEW.itemsite_qtyonhand
          INTO _cost;
        _variance := _cost - NEW.itemsite_value;
        NEW.itemsite_value := _cost;
        IF(_variance <> 0.0) THEN
          PERFORM insertGLTransaction( 'P/D', '', '', 'Itemsite converted from Average to Standard cost.',
                                       costcat_invcost_accnt_id, costcat_asset_accnt_id, NEW.itemsite_id,
                                      _variance, CURRENT_DATE )
             FROM costcat
            WHERE(costcat_id=NEW.itemsite_costcat_id);
          UPDATE itemsite SET itemsite_value = _cost WHERE (itemsite_id = NEW.itemsite_id);
        END IF;
      END IF;
    END IF;

--  Handle Perishable
    IF ( (_application = 'Standard') AND (_wasPerishable) AND (NOT _isPerishable) ) THEN
      UPDATE itemloc SET itemloc_expiration = endOfTime()
      WHERE (itemloc_itemsite_id = OLD.itemsite_id);
      PERFORM consolidateLotSerial(OLD.itemsite_id);
    END IF;

--  If Planning Type changed to None then delete all Planned Orders
    IF ( (_application = 'Standard') AND (TG_OP = 'UPDATE') ) THEN
      IF (NEW.itemsite_planning_type = 'N' AND OLD.itemsite_planning_type <> 'N') THEN
        PERFORM deletePlannedOrder(planord_id, TRUE)
        FROM planord
        WHERE (planord_itemsite_id=NEW.itemsite_id);
      END IF;
    END IF;

  END IF;  -- End Maintenance

  RETURN NEW;

END;
$$;


ALTER FUNCTION public._itemsiteaftertrigger() OWNER TO admin;

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

CREATE FUNCTION _itemsitetrigger() 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;
  _r RECORD;

BEGIN

  -- Cache some information
  -- Added item_number as part of feature request 21645
  SELECT item_type, item_number INTO _r
  FROM item
  WHERE (item_id=NEW.itemsite_item_id);

-- Override values to avoid invalid data combinations
  IF (_r.item_type IN ('J','R','S')) THEN
    NEW.itemsite_planning_type := 'N';
  END IF;

  IF (_r.item_type = 'L') THEN
    NEW.itemsite_planning_type := 'S';
  END IF;

  IF (TG_OP = 'UPDATE') THEN
    IF ( (NEW.itemsite_qtyonhand <> OLD.itemsite_qtyonhand) ) THEN
      IF (OLD.itemsite_freeze) THEN
        NEW.itemsite_qtyonhand := OLD.itemsite_qtyonhand;
      ELSE
        NEW.itemsite_datelastused := CURRENT_DATE;
      END IF;

      IF ( (NEW.itemsite_qtyonhand < 0) AND (OLD.itemsite_qtyonhand >= 0) AND (NEW.itemsite_eventfence > 0) ) THEN
        PERFORM postEvent('QOHBelowZero', 'I', NEW.itemsite_id,
                          warehous_id,
                          (item_number || '/' || warehous_code),
                          NULL, NULL, NULL, NULL)
        FROM item, whsinfo
        WHERE (item_id=NEW.itemsite_item_id)
          AND (warehous_id=NEW.itemsite_warehous_id);
      END IF;
    END IF;
    IF ( (NEW.itemsite_value <> OLD.itemsite_value) AND (OLD.itemsite_freeze) ) THEN
      NEW.itemsite_value := OLD.itemsite_value;
    END IF;
  END IF;

-- Added item_number to error messages displayed to fulfill Feature Request 21645
  IF (NEW.itemsite_qtyonhand < 0 AND NEW.itemsite_costmethod = 'A') THEN
    RAISE EXCEPTION 'Itemsite (%) is set to use average costing and is not allowed to have a negative quantity on hand.', 'ID: ' || NEW.itemsite_id || ', Item: ' || _r.item_number;
  ELSIF (NEW.itemsite_value < 0 AND NEW.itemsite_costmethod = 'A') THEN
    RAISE EXCEPTION 'This transaction results in a negative itemsite value.  Itemsite (%) is set to use average costing and is not allowed to have a negative value.', 'ID: ' || NEW.itemsite_id || ', Item: ' || _r.item_number;  END IF;

--  Handle the ChangeLog
  IF ( SELECT (metric_value='t')
       FROM metric
       WHERE (metric_name='ItemSiteChangeLog') ) 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, 'IS', NEW.itemsite_id, 'Created');

      ELSIF (TG_OP = 'UPDATE') THEN

        IF (OLD.itemsite_plancode_id <> NEW.itemsite_plancode_id) THEN
          PERFORM postComment( _cmnttypeid, 'IS', NEW.itemsite_id,
                               ( 'Planner Code Changed from "' || oldplancode.plancode_code ||
                                 '" to "' || newplancode.plancode_code || '"' ) )
          FROM plancode AS oldplancode, plancode AS newplancode
          WHERE ( (oldplancode.plancode_id=OLD.itemsite_plancode_id)
           AND (newplancode.plancode_id=NEW.itemsite_plancode_id) );
        END IF;

        IF (NEW.itemsite_reorderlevel <> OLD.itemsite_reorderlevel) THEN
          PERFORM postComment( _cmnttypeid, 'IS', NEW.itemsite_id,
                               ( 'Reorder Level Changed from ' || formatQty(OLD.itemsite_reorderlevel) ||
                                 ' to ' || formatQty(NEW.itemsite_reorderlevel ) ) );
        END IF;

        IF (NEW.itemsite_ordertoqty <> OLD.itemsite_ordertoqty) THEN
          PERFORM postComment( _cmnttypeid, 'IS', NEW.itemsite_id,
                               ( 'Order Up To Changed from ' || formatQty(OLD.itemsite_ordertoqty) ||
                                 ' to ' || formatQty(NEW.itemsite_ordertoqty ) ) );
        END IF;

        IF (NEW.itemsite_leadtime <> OLD.itemsite_leadtime) THEN
          PERFORM postComment( _cmnttypeid, 'IS', NEW.itemsite_id,
                               ( 'Itemsite Leadtime Changed from ' || formatQty(OLD.itemsite_leadtime) ||
                                 ' to ' || formatQty(NEW.itemsite_leadtime ) ) );
        END IF;

        IF (NEW.itemsite_abcclass <> OLD.itemsite_abcclass) THEN
          PERFORM postComment( _cmnttypeid, 'IS', NEW.itemsite_id,
                               ( 'Itemsite ABC Class Changed from ' || COALESCE(OLD.itemsite_abcclass, 'None') ||
                                 ' to ' || COALESCE(NEW.itemsite_abcclass,'None') ) );
        END IF;

        IF (NEW.itemsite_controlmethod <> OLD.itemsite_controlmethod) THEN
          PERFORM postComment( _cmnttypeid, 'IS', NEW.itemsite_id,
                               ( 'Itemsite Control Method Changed from ' || COALESCE(OLD.itemsite_controlmethod,'None') ||
                                 ' to ' || COALESCE(NEW.itemsite_controlmethod,'None') ) );
        END IF;

        IF (OLD.itemsite_sold <> NEW.itemsite_sold) THEN
          PERFORM postComment( _cmnttypeid, 'IS', NEW.itemsite_id,
            CASE WHEN (NEW.itemsite_sold) THEN 'Sold Changed from FALSE to TRUE'
                                          ELSE 'Sold Changed from TRUE to FALSE'
            END );
        END IF;

        IF (OLD.itemsite_active <> NEW.itemsite_active) THEN
          IF (NEW.itemsite_active) THEN
            PERFORM postComment(_cmnttypeid, 'IS', NEW.itemsite_id, 'Activated');
          ELSE
            PERFORM postComment(_cmnttypeid, 'IS', NEW.itemsite_id, 'Deactivated');
          END IF;
        END IF;

      END IF;
    END IF;
  END IF;

  RETURN NEW;

END;
$$;


ALTER FUNCTION public._itemsitetrigger() OWNER TO admin;

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

CREATE FUNCTION _itemsrcaftertrigger() 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

-- Privilege Checks
  IF (NOT checkPrivilege('MaintainItemSources')) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Item Sources.';
  END IF;

-- Set default to false for other item sources of this item
  IF (COALESCE(NEW.itemsrc_default, FALSE) = TRUE) THEN
    UPDATE itemsrc SET itemsrc_default = FALSE
    WHERE ( (itemsrc_item_id = NEW.itemsrc_item_id)
      AND (itemsrc_id <> NEW.itemsrc_id) );
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._itemsrcaftertrigger() OWNER TO admin;

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

CREATE FUNCTION _itemsrcptrigger() 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

-- Privilege Checks
   IF (NOT checkPrivilege('MaintainItemSources')) THEN
     RAISE EXCEPTION 'You do not have privileges to maintain Item Sources.';
   END IF;

-- Set defaults
   NEW.itemsrcp_curr_id	:= COALESCE(NEW.itemsrcp_curr_id,basecurrid());
  
  RETURN NEW;
END;
$$;


ALTER FUNCTION public._itemsrcptrigger() OWNER TO admin;

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

CREATE FUNCTION _itemsrctrigger() 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

-- Privilege Checks
   IF (NOT checkPrivilege('MaintainItemSources')) THEN
     RAISE EXCEPTION 'You do not have privileges to maintain Item Sources.';
   END IF;

-- Set defaults
   NEW.itemsrc_invvendoruomratio	:= COALESCE(NEW.itemsrc_invvendoruomratio,1);
   NEW.itemsrc_minordqty		:= COALESCE(NEW.itemsrc_minordqty,0);
   NEW.itemsrc_multordqty		:= COALESCE(NEW.itemsrc_multordqty,0);
   NEW.itemsrc_active			:= COALESCE(NEW.itemsrc_active,true);
   NEW.itemsrc_leadtime			:= COALESCE(NEW.itemsrc_leadtime,0);
   NEW.itemsrc_ranking			:= COALESCE(NEW.itemsrc_ranking,1);
  
  RETURN NEW;
END;
$$;


ALTER FUNCTION public._itemsrctrigger() OWNER TO admin;

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

CREATE FUNCTION _itemsubtrigger() 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

-- Privilege Checks
   IF (NOT checkPrivilege('MaintainItemMasters')) THEN
     RAISE EXCEPTION 'You do not have privileges to maintain Item Substitutes.';
   END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._itemsubtrigger() OWNER TO admin;

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

CREATE FUNCTION _itemtaxtrigger() 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

-- Privilege Checks
   IF (NOT checkPrivilege('MaintainItemMasters')) THEN
     RAISE EXCEPTION 'You do not have privileges to maintain Items.';
   END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._itemtaxtrigger() OWNER TO admin;

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

CREATE FUNCTION _itemtrigger() 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

-- Override values to avoid invalid data combinations
  IF (NEW.item_type IN ('R','S','O','L','B')) THEN
    NEW.item_picklist := FALSE;
  END IF;

  IF (NEW.item_type IN ('F','S','O','L','B')) THEN
    NEW.item_picklist := FALSE;
    NEW.item_sold := FALSE;
    NEW.item_prodcat_id := -1;
    NEW.item_exclusive := false;
    NEW.item_listprice := 0;
    NEW.item_upccode := '';
    NEW.item_prodweight := 0;
    NEW.item_packweight := 0;
  END IF;

  IF (NEW.item_type NOT IN ('M','R')) THEN
    NEW.item_config := false;
  END IF;

  RETURN NEW;

END;
$$;


ALTER FUNCTION public._itemtrigger() OWNER TO admin;

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

CREATE FUNCTION _itemuomconvtrigger() 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

-- Privilege Checks
   IF (NOT checkPrivilege('MaintainItemMasters')) THEN
     RAISE EXCEPTION 'You do not have privileges to maintain Items.';
   END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._itemuomconvtrigger() OWNER TO admin;

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

CREATE FUNCTION _locationaftertrigger() 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
  _itemloc    RECORD;

BEGIN

  RETURN NEW;

END;
$$;


ALTER FUNCTION public._locationaftertrigger() OWNER TO admin;

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

CREATE FUNCTION _locationtrigger() 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('MaintainLocations') INTO _check;
    IF NOT (_check) THEN
      RAISE EXCEPTION 'You do not have privileges to add new Locations.';
    END IF;
  ELSE
    SELECT checkPrivilege('MaintainLocations') INTO _check;
    IF NOT (_check) THEN
      RAISE EXCEPTION 'You do not have privileges to alter a Location.';
    END IF;
  END IF;

  -- Code is required
  IF ( (LENGTH(COALESCE(NEW.location_name,''))=0) AND
       (LENGTH(COALESCE(NEW.location_aisle,''))=0) AND
       (LENGTH(COALESCE(NEW.location_rack,''))=0) AND
       (LENGTH(COALESCE(NEW.location_bin,''))=0) ) THEN
    RAISE EXCEPTION 'You must supply a valid Location Identifier.';
  END IF;
  
  -- Site is required
  IF (NEW.location_warehous_id IS NULL) THEN
    RAISE EXCEPTION 'You must supply a valid Site.';
  END IF;

  -- Location Identifier must be unique
  SELECT location_id INTO _checkId
  FROM location
  WHERE ( (UPPER(location_name)=UPPER(NEW.location_name))
    AND   (UPPER(location_aisle)=UPPER(NEW.location_aisle))
    AND   (UPPER(location_rack)=UPPER(NEW.location_rack))
    AND   (UPPER(location_bin)=UPPER(NEW.location_bin))
    AND   (location_warehous_id=NEW.location_warehous_id)
    AND   (location_id<>NEW.location_id) );
  IF (FOUND) THEN
    RAISE EXCEPTION 'You must supply a unique Location Identifier for this Site.';
  END IF;

  -- Populate formatted name
  IF (NEW.location_aisle IS NOT NULL) THEN
    NEW.location_formatname := NEW.location_aisle;
  ELSE
    NEW.location_formatname := '';
  END IF;

  IF (NEW.location_rack IS NOT NULL) THEN
    NEW.location_formatname := (NEW.location_formatname || NEW.location_rack);
  END IF;

  IF (NEW.location_bin IS NOT NULL) THEN
    NEW.location_formatname := (NEW.location_formatname || NEW.location_bin);
  END IF;

  IF (NEW.location_name IS NOT NULL) THEN
    NEW.location_formatname := (NEW.location_formatname || NEW.location_name);
  END IF;
  
  RETURN NEW;

END;
$$;


ALTER FUNCTION public._locationtrigger() OWNER TO admin;

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

CREATE FUNCTION _metasqlaltertrigger() 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 (isDBA() OR checkPrivilege('MaintainMetaSQL'))) THEN
    RAISE EXCEPTION '% does not have privileges to maintain MetaSQL statements in %.%',
                getEffectiveXtUser(), TG_TABLE_SCHEMA, TG_TABLE_NAME;
  END IF;

  IF ((TG_OP = 'UPDATE' OR TG_OP = 'DELETE')
      AND NEW.metasql_grade <= 0
      AND NOT isDBA()) THEN
    RAISE EXCEPTION 'You may not alter grade 0 metasql queries except using the xTuple Updater utility';
  END IF;

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

  RETURN NEW;
END;

$$;


ALTER FUNCTION public._metasqlaltertrigger() OWNER TO admin;

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

CREATE FUNCTION _metasqltrigger() 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.metasql_lastuser 		:= getEffectiveXtUser();
  NEW.metasql_lastupdate 	:= current_date;
  RETURN NEW;

END;

$$;


ALTER FUNCTION public._metasqltrigger() OWNER TO admin;

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

CREATE FUNCTION _normalizeversion(pversion text) RETURNS smallint[]
    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.
-- Convert an arbitrary version number string to an array of integers.
-- Supports the following styles:
--   xTuple   4.5.6Beta2A
--   semver   4.5.6-beta.2.a+buildId
--   postgres 9.3
DECLARE
  _result SMALLINT[];  -- 4.5.6
  _part   TEXT[];
  _debug  BOOLEAN := false;
BEGIN
  -- extract 4.5.6
  _part := regexp_matches(pVersion, --                  v----------- 6 -----------v
             -- capture                v-3-v                         v---- 8 ----v
             --   1          2         4   5            7            9          10
             --   4       .  5         .   6       -    beta         2           a
             E'^([0-9]+)\\.([0-9]+)((\\.)([0-9]+))?-?(([a-z]+)\\.?(([0-9]*)\\.?([a-z]?)?)?)?', 'i');
  IF _debug THEN
    RAISE NOTICE 'M: % N: % dP: % d: % P: % rest: % alpha: % sub: % num: % let: %',
                _part[1], _part[2], _part[3], _part[4], _part[5],
                _part[6], _part[7], _part[8], _part[9], _part[10];
  END IF;
  _result := ARRAY [
               _part[1]::SMALLINT,
               _part[2]::SMALLINT,
               COALESCE(_part[5]::SMALLINT, 0),
               CASE lower(COALESCE(_part[7], '')) 
                    WHEN ''      THEN 50
                    WHEN 'rc'    THEN 40
                    WHEN 'beta'  THEN 30
                    WHEN 'alpha' THEN 20
                    ELSE 10
               END,
               CASE WHEN _part[9] IS NULL THEN 0
                    WHEN _part[9] = ''    THEN 0
                    ELSE _part[9]::SMALLINT             -- beta < beta2
               END,
               ascii(CASE WHEN _part[10] IS NULL THEN '_'
                          WHEN _part[10] = ''    THEN '_'
                          ELSE lower(COALESCE(_part[10], '_'))
                     END)   -- 2 < 2a
             ];
  IF _debug THEN RAISE NOTICE '_part: % -> _result: %', _part, _result; END IF;
  IF _part[1] IS NULL OR _part[2] IS NULL THEN
    RAISE EXCEPTION '% is not recognized as a valid version number', pVersion;
  END IF;
  RETURN _result;
END;
$$;


ALTER FUNCTION public._normalizeversion(pversion text) OWNER TO admin;

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

CREATE FUNCTION _opheadafterdeletetrigger() 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 = 'OPP'
    AND charass_target_id = OLD.ophead_id;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._opheadafterdeletetrigger() OWNER TO admin;

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

CREATE FUNCTION _opheadaftertrigger() 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 = 'DELETE') THEN
    DELETE FROM docass WHERE docass_source_id = OLD.ophead_id AND docass_source_type = 'OPP';
    DELETE FROM docass WHERE docass_target_id = OLD.ophead_id AND docass_target_type = 'OPP';
  END IF;

  --  Comments
  IF ( SELECT (metric_value='t') FROM metric WHERE (metric_name='OpportunityChangeLog') ) 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, 'OPP', NEW.ophead_id, 'Created');

        --- clear the number from the issue cache
        PERFORM clearNumberIssue('OpportunityNumber', NEW.ophead_number);
      ELSIF (TG_OP = 'UPDATE') THEN
        IF (OLD.ophead_active <> NEW.ophead_active) THEN
          IF (NEW.ophead_active) THEN
            PERFORM postComment(_cmnttypeid, 'OPP', NEW.ophead_id, 'Activated');
          ELSE
            PERFORM postComment(_cmnttypeid, 'OPP', NEW.ophead_id, 'Deactivated');
          END IF;
        END IF;

        IF (OLD.ophead_name <> NEW.ophead_name) THEN
          PERFORM postComment( _cmnttypeid, 'OPP', NEW.ophead_id,
                               ( 'Name Changed from "' || OLD.ophead_name ||
                                 '" to "' || NEW.ophead_name || '"' ) );
        END IF;

        IF (OLD.ophead_owner_username <> NEW.ophead_owner_username) THEN
          PERFORM postComment( _cmnttypeid, 'OPP', NEW.ophead_id,
                               ( 'Owner Name Changed from "' || OLD.ophead_owner_username ||
                                 '" to "' || NEW.ophead_owner_username || '"' ) );
        END IF;

        IF (OLD.ophead_probability_prcnt <> NEW.ophead_probability_prcnt) THEN
          PERFORM postComment( _cmnttypeid, 'OPP', NEW.ophead_id,
                               ( 'Probability % Changed from "' || OLD.ophead_probability_prcnt ||
                                 '" to "' || NEW.ophead_probability_prcnt || '"' ) );
        END IF;

        IF (OLD.ophead_amount <> NEW.ophead_amount) THEN
          PERFORM postComment( _cmnttypeid, 'OPP', NEW.ophead_id,
                               ( 'Amount Changed from "' || OLD.ophead_amount ||
                                 '" to "' || NEW.ophead_amount || '"' ) );
        END IF;

        IF (OLD.ophead_target_date <> NEW.ophead_target_date) THEN
          PERFORM postComment( _cmnttypeid, 'OPP', NEW.ophead_id,
                               ( 'Target Date Changed from "' || OLD.ophead_target_date ||
                                 '" to "' || NEW.ophead_target_date || '"' ) );
        END IF;

        IF (OLD.ophead_actual_date <> NEW.ophead_actual_date) THEN
          PERFORM postComment( _cmnttypeid, 'OPP', NEW.ophead_id,
                               ( 'Actual Date Changed from "' || OLD.ophead_actual_date ||
                                 '" to "' || NEW.ophead_actual_date || '"' ) );
        END IF;

        IF (OLD.ophead_crmacct_id <> NEW.ophead_crmacct_id) THEN
          PERFORM postComment( _cmnttypeid, 'OPP', NEW.ophead_id,
                               ( 'CRM Account Changed from "' ||
                                 (SELECT crmacct_name FROM crmacct WHERE crmacct_id=OLD.ophead_crmacct_id) ||
                                 '" (' || OLD.ophead_crmacct_id ||
                                 ') to "' ||
                                 (SELECT crmacct_name FROM crmacct WHERE crmacct_id=NEW.ophead_crmacct_id) ||
                                 '" (' || NEW.ophead_crmacct_id || ')' ) );
        END IF;

        IF (OLD.ophead_curr_id <> NEW.ophead_curr_id) THEN
          PERFORM postComment( _cmnttypeid, 'OPP', NEW.ophead_id,
                               ( 'Currency Changed from "' ||
                                 (SELECT curr_name FROM curr_symbol WHERE curr_id=OLD.ophead_curr_id) ||
                                 '" (' || OLD.ophead_curr_id ||
                                 ') to "' ||
                                 (SELECT curr_name FROM curr_symbol WHERE curr_id=NEW.ophead_curr_id) ||
                                 '" (' || NEW.ophead_curr_id || ')' ) );
        END IF;

        IF (OLD.ophead_opstage_id <> NEW.ophead_opstage_id) THEN
          PERFORM postComment( _cmnttypeid, 'OPP', NEW.ophead_id,
                               ( 'Stage Changed from "' ||
                                 (SELECT opstage_name FROM opstage WHERE opstage_id=OLD.ophead_opstage_id) ||
                                 '" (' || OLD.ophead_opstage_id ||
                                 ') to "' ||
                                 (SELECT opstage_name FROM opstage WHERE opstage_id=NEW.ophead_opstage_id) ||
                                 '" (' || NEW.ophead_opstage_id || ')' ) );
        END IF;

        IF (OLD.ophead_opsource_id <> NEW.ophead_opsource_id) THEN
          PERFORM postComment( _cmnttypeid, 'OPP', NEW.ophead_id,
                               ( 'Source Changed from "' ||
                                 (SELECT opsource_name FROM opsource WHERE opsource_id=OLD.ophead_opsource_id) ||
                                 '" (' || OLD.ophead_opsource_id ||
                                 ') to "' ||
                                 (SELECT opsource_name FROM opsource WHERE opsource_id=NEW.ophead_opsource_id) ||
                                 '" (' || NEW.ophead_opsource_id || ')' ) );
        END IF;

        IF (OLD.ophead_optype_id <> NEW.ophead_optype_id) THEN
          PERFORM postComment( _cmnttypeid, 'OPP', NEW.ophead_id,
                               ( 'Type Changed from "' ||
                                 (SELECT optype_name FROM optype WHERE optype_id=OLD.ophead_optype_id) ||
                                 '" (' || OLD.ophead_optype_id ||
                                 ') to "' ||
                                 (SELECT optype_name FROM optype WHERE optype_id=NEW.ophead_optype_id) ||
                                 '" (' || NEW.ophead_optype_id || ')' ) );
        END IF;

      END IF;
    END IF;
  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._opheadaftertrigger() OWNER TO admin;

--
