--
--

-- Started on 2014-03-27 11:30:47 EDT


--
-- TOC entry 6 (class 2615 OID 146565167)
-- Name: api; Type: SCHEMA; Schema: -; Owner: admin
--

CREATE SCHEMA api;


ALTER SCHEMA api OWNER TO admin;

--
-- TOC entry 7 (class 2615 OID 146565168)
-- Name: fixcountry; Type: SCHEMA; Schema: -; Owner: admin
--

CREATE SCHEMA fixcountry;


ALTER SCHEMA fixcountry OWNER TO admin;

--
-- TOC entry 8899 (class 0 OID 0)
-- Dependencies: 7
-- Name: SCHEMA fixcountry; Type: COMMENT; Schema: -; Owner: admin
--

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


--
-- TOC entry 9 (class 2615 OID 146565169)
-- Name: te; Type: SCHEMA; Schema: -; Owner: admin
--

CREATE SCHEMA te;


ALTER SCHEMA te OWNER TO admin;

--
-- TOC entry 8903 (class 0 OID 0)
-- Dependencies: 9
-- Name: SCHEMA te; Type: COMMENT; Schema: -; Owner: admin
--

COMMENT ON SCHEMA te IS 'This file is part of the xTuple ERP: PostBooks Edition, a free and open source Enterprise Resource Planning software suite, Copyright (c) 1999-2010 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.';


--
-- TOC entry 10 (class 2615 OID 146565170)
-- Name: xtdesktop; Type: SCHEMA; Schema: -; Owner: admin
--

CREATE SCHEMA xtdesktop;


ALTER SCHEMA xtdesktop OWNER TO admin;

--
-- TOC entry 8905 (class 0 OID 0)
-- Dependencies: 10
-- Name: SCHEMA xtdesktop; Type: COMMENT; Schema: -; Owner: admin
--

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


--
-- TOC entry 889 (class 3079 OID 11681)
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- TOC entry 8907 (class 0 OID 0)
-- Dependencies: 889
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
--

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



--
-- TOC entry 2648 (class 1247 OID 146565173)
-- Dependencies: 8 165
-- 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 public.achline OWNER TO admin;

--
-- TOC entry 2651 (class 1247 OID 146565176)
-- Dependencies: 8 166
-- 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 public.apaging OWNER TO admin;

--
-- TOC entry 2654 (class 1247 OID 146565179)
-- Dependencies: 8 167
-- 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 public.araging OWNER TO admin;

--
-- TOC entry 2657 (class 1247 OID 146565182)
-- Dependencies: 8 168
-- 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 public.bomdata OWNER TO admin;

--
-- TOC entry 2660 (class 1247 OID 146565185)
-- Dependencies: 8 169
-- 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 public.checkdata OWNER TO admin;

--
-- TOC entry 2663 (class 1247 OID 146565188)
-- Dependencies: 8 170
-- 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 public.cntctdup OWNER TO admin;

--
-- TOC entry 2666 (class 1247 OID 146565191)
-- Dependencies: 8 171
-- Name: flcoldata; Type: TYPE; Schema: public; Owner: admin
--

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


ALTER TYPE public.flcoldata OWNER TO admin;

--
-- TOC entry 2669 (class 1247 OID 146565194)
-- Dependencies: 8 172
-- 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 public.flstmthead OWNER TO admin;

--
-- TOC entry 2672 (class 1247 OID 146565197)
-- Dependencies: 8 173
-- 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 public.flstmtitem OWNER TO admin;

--
-- TOC entry 2675 (class 1247 OID 146565200)
-- Dependencies: 8 174
-- 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 public.fltrendhead OWNER TO admin;

--
-- TOC entry 2678 (class 1247 OID 146565203)
-- Dependencies: 8 175
-- 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 public.fltrenditem OWNER TO admin;

--
-- TOC entry 2681 (class 1247 OID 146565206)
-- Dependencies: 8 176
-- 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 public.freightdata OWNER TO admin;

--
-- TOC entry 2684 (class 1247 OID 146565209)
-- Dependencies: 8 177
-- Name: itemprice; Type: TYPE; Schema: public; Owner: admin
--

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


ALTER TYPE public.itemprice OWNER TO admin;

--
-- TOC entry 2687 (class 1247 OID 146565212)
-- Dependencies: 8 178
-- 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 public.orderitemtype OWNER TO admin;

--
-- TOC entry 2690 (class 1247 OID 146565215)
-- Dependencies: 8 179
-- 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 public.ordhead OWNER TO admin;

--
-- TOC entry 2693 (class 1247 OID 146565218)
-- Dependencies: 8 180
-- 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 public.orditem OWNER TO admin;

--
-- TOC entry 2696 (class 1247 OID 146565221)
-- Dependencies: 8 181
-- 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 public.reordlvl OWNER TO admin;

--
-- TOC entry 2699 (class 1247 OID 146565224)
-- Dependencies: 8 182
-- Name: seqiss; Type: TYPE; Schema: public; Owner: admin
--

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


ALTER TYPE public.seqiss OWNER TO admin;

--
-- TOC entry 2702 (class 1247 OID 146565227)
-- Dependencies: 8 183
-- 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 public.subtax OWNER TO admin;

--
-- TOC entry 2705 (class 1247 OID 146565230)
-- Dependencies: 8 184
-- 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 public.taxassign OWNER TO admin;

--
-- TOC entry 2708 (class 1247 OID 146565233)
-- Dependencies: 8 185
-- 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 public.taxdetail OWNER TO admin;

--
-- TOC entry 2711 (class 1247 OID 146565236)
-- Dependencies: 8 186
-- 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 public.wodata OWNER TO admin;

--
-- TOC entry 2714 (class 1247 OID 146565239)
-- Dependencies: 8 187
-- 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 public.woinvav OWNER TO admin;


--
-- TOC entry 2717 (class 1247 OID 146565242)
-- Dependencies: 10 188
-- 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 xtdesktop.usersonline OWNER TO admin;


--
-- TOC entry 901 (class 1255 OID 146565243)
-- Dependencies: 4536 6
-- 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;


--
-- TOC entry 902 (class 1255 OID 146565244)
-- Dependencies: 4536 8
-- 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;



--
-- TOC entry 189 (class 1259 OID 146565245)
-- Dependencies: 5838 8
-- 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 public.cntslip OWNER TO admin;

--
-- TOC entry 8908 (class 0 OID 0)
-- Dependencies: 189
-- Name: TABLE cntslip; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE cntslip IS 'Count Slip information';


--
-- TOC entry 190 (class 1259 OID 146565252)
-- Dependencies: 5839 8
-- 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 public.invcnt OWNER TO admin;

--
-- TOC entry 8910 (class 0 OID 0)
-- Dependencies: 190
-- Name: TABLE invcnt; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE invcnt IS 'Count Tag information';


--
-- TOC entry 191 (class 1259 OID 146565259)
-- Dependencies: 5840 5841 5842 5843 5844 5845 5846 5847 5848 5849 5850 5851 5852 5853 8
-- 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 public.item OWNER TO admin;

--
-- TOC entry 8912 (class 0 OID 0)
-- Dependencies: 191
-- Name: TABLE item; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE item IS 'Item information';


--
-- TOC entry 8913 (class 0 OID 0)
-- Dependencies: 191
-- 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.';


--
-- TOC entry 8914 (class 0 OID 0)
-- Dependencies: 191
-- 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.';


--
-- TOC entry 192 (class 1259 OID 146565279)
-- Dependencies: 5854 5855 5856 5857 5858 5859 5860 5861 5862 5863 5864 5865 5866 5867 5868 5869 5870 5871 5872 5873 5874 5875 5876 5877 5878 5879 5880 8
-- 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_nnqoh numeric(18,6) DEFAULT 0 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 public.itemsite OWNER TO admin;

--
-- TOC entry 8916 (class 0 OID 0)
-- Dependencies: 192
-- Name: TABLE itemsite; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE itemsite IS 'Item Site information';


--
-- TOC entry 8917 (class 0 OID 0)
-- Dependencies: 192
-- 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';


--
-- TOC entry 8918 (class 0 OID 0)
-- Dependencies: 192
-- 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';


--
-- TOC entry 8919 (class 0 OID 0)
-- Dependencies: 192
-- 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';


--
-- TOC entry 8920 (class 0 OID 0)
-- Dependencies: 192
-- 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';


--
-- TOC entry 193 (class 1259 OID 146565312)
-- Dependencies: 5881 5882 5883 5884 5885 5886 8
-- 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 public.whsinfo OWNER TO admin;

--
-- TOC entry 8922 (class 0 OID 0)
-- Dependencies: 193
-- Name: TABLE whsinfo; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE whsinfo IS 'Warehouse information';



--
-- TOC entry 194 (class 1259 OID 146565324)
-- Dependencies: 8469 6
-- 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 api.physinvcount OWNER TO admin;

--
-- TOC entry 8924 (class 0 OID 0)
-- Dependencies: 194
-- Name: VIEW physinvcount; Type: COMMENT; Schema: api; Owner: admin
--

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


--
-- TOC entry 906 (class 1255 OID 146565329)
-- Dependencies: 2740 4536 6
-- 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')) 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')) 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;


--
-- TOC entry 907 (class 1255 OID 146565330)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 908 (class 1255 OID 146565331)
-- Dependencies: 4536 8
-- Name: formatglaccount(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION formatglaccount(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
  pAccntid ALIAS FOR $1;
  _accnt RECORD;

BEGIN

  SELECT COALESCE(accnt_company, '') AS accnt_company,
         COALESCE(accnt_profit, '') AS accnt_profit,
         accnt_number,
         COALESCE(accnt_sub, '') AS accnt_sub INTO _accnt
  FROM accnt
  WHERE (accnt_id=pAccntid);

  IF (NOT FOUND) THEN
    RETURN 'Error';
  END IF;

  RETURN formatGlAccount(_accnt.accnt_company, _accnt.accnt_profit, _accnt.accnt_number, _accnt.accnt_sub);

END;
$_$;


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

--
-- TOC entry 909 (class 1255 OID 146565332)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 910 (class 1255 OID 146565333)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 911 (class 1255 OID 146565334)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 195 (class 1259 OID 146565335)
-- Dependencies: 5887 5888 5889 5890 5891 5892 5893 5894 5895 5896 5897 5898 5899 8
-- 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 public.cohead OWNER TO admin;

--
-- TOC entry 8926 (class 0 OID 0)
-- Dependencies: 195
-- Name: TABLE cohead; Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- TOC entry 8927 (class 0 OID 0)
-- Dependencies: 195
-- 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.';


--
-- TOC entry 8928 (class 0 OID 0)
-- Dependencies: 195
-- 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.';


--
-- TOC entry 196 (class 1259 OID 146565354)
-- Dependencies: 5900 5901 5902 5903 5904 5905 5906 5907 5908 5909 5910 5911 8
-- 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 public.coitem OWNER TO admin;

--
-- TOC entry 8930 (class 0 OID 0)
-- Dependencies: 196
-- Name: TABLE coitem; Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- TOC entry 8931 (class 0 OID 0)
-- Dependencies: 196
-- 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';


--
-- TOC entry 197 (class 1259 OID 146565372)
-- Dependencies: 5912 5913 5914 5915 5916 5917 5918 5919 8
-- 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 public.pohead OWNER TO admin;

--
-- TOC entry 8933 (class 0 OID 0)
-- Dependencies: 197
-- Name: TABLE pohead; Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- TOC entry 198 (class 1259 OID 146565386)
-- Dependencies: 5920 5921 5922 5923 5924 5925 5926 5927 5928 8
-- 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 public.poitem OWNER TO admin;

--
-- TOC entry 8935 (class 0 OID 0)
-- Dependencies: 198
-- Name: TABLE poitem; Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- TOC entry 199 (class 1259 OID 146565401)
-- Dependencies: 5930 5931 8
-- 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 public.taxtype OWNER TO admin;

--
-- TOC entry 8937 (class 0 OID 0)
-- Dependencies: 199
-- Name: TABLE taxtype; Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- TOC entry 200 (class 1259 OID 146565409)
-- Dependencies: 5933 5934 8
-- 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 public.uom OWNER TO admin;

--
-- TOC entry 8939 (class 0 OID 0)
-- Dependencies: 200
-- Name: TABLE uom; Type: COMMENT; Schema: public; Owner: admin
--

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



--
-- TOC entry 201 (class 1259 OID 146565417)
-- Dependencies: 8470 6
-- 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 api.salesline OWNER TO admin;

--
-- TOC entry 8941 (class 0 OID 0)
-- Dependencies: 201
-- Name: VIEW salesline; Type: COMMENT; Schema: api; Owner: admin
--

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


--
-- TOC entry 912 (class 1255 OID 146565422)
-- Dependencies: 2767 4536 6
-- 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)) THEN
        'W'
      WHEN ((pNEW.create_order AND (_r.item_type = 'P')) OR 
           ((pNEW.create_order IS NULL) AND _r.itemsite_createsopr)) 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)) 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;


--
-- TOC entry 914 (class 1255 OID 146565423)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 915 (class 1255 OID 146565424)
-- Dependencies: 4536 8
-- 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(ffSub, 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;

--
-- TOC entry 916 (class 1255 OID 146565425)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 917 (class 1255 OID 146565426)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 918 (class 1255 OID 146565427)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 919 (class 1255 OID 146565428)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 920 (class 1255 OID 146565429)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 913 (class 1255 OID 146565430)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 903 (class 1255 OID 146565431)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 904 (class 1255 OID 146565432)
-- Dependencies: 4536 8
-- 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('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 ( (NEW.aropen_amount IS NOT NULL) AND (NEW.aropen_amount < 0) ) THEN
    RAISE EXCEPTION 'You must enter a positive Amount for this A/R Memo.';
  END IF;

  IF (TG_OP IN ('INSERT', 'UPDATE') AND NEW.aropen_cust_id < 0) THEN
    RAISE NOTICE '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 NOTICE '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;

--
-- TOC entry 921 (class 1255 OID 146565434)
-- Dependencies: 4536 8
-- 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;
BEGIN
-- Privilege Checks
  IF (NOT checkPrivilege('MaintainBOMs')) THEN
    RAISE EXCEPTION 'You do not have privileges to maintain Bills of Material.';
  END IF;
  
  RETURN NEW;
END;
$$;


ALTER FUNCTION public._bomheadtrigger() OWNER TO admin;

--
-- TOC entry 922 (class 1255 OID 146565435)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 923 (class 1255 OID 146565436)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 924 (class 1255 OID 146565437)
-- Dependencies: 4536 8
-- 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;
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);

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

--
-- TOC entry 925 (class 1255 OID 146565439)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 926 (class 1255 OID 146565440)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 927 (class 1255 OID 146565441)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 928 (class 1255 OID 146565442)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 929 (class 1255 OID 146565443)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 931 (class 1255 OID 146565444)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 932 (class 1255 OID 146565445)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 933 (class 1255 OID 146565446)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 934 (class 1255 OID 146565447)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 935 (class 1255 OID 146565448)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 938 (class 1255 OID 146565449)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 939 (class 1255 OID 146565450)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 940 (class 1255 OID 146565451)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 941 (class 1255 OID 146565452)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 942 (class 1255 OID 146565453)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 943 (class 1255 OID 146565454)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 947 (class 1255 OID 146565455)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 948 (class 1255 OID 146565456)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 949 (class 1255 OID 146565457)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 950 (class 1255 OID 146565458)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 951 (class 1255 OID 146565459)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 936 (class 1255 OID 146565460)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 937 (class 1255 OID 146565461)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 944 (class 1255 OID 146565462)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 952 (class 1255 OID 146565463)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 953 (class 1255 OID 146565464)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 954 (class 1255 OID 146565465)
-- Dependencies: 4536 8
-- 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
     (note: grantPriv() == false if the user already had the priv, true if this
     call granted the priv).
   */
  IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
    IF (NEW.crmacct_cust_id IS NOT NULL) THEN
      _gotpriv := grantPriv(getEffectiveXtUser(), 'MaintainCustomerMasters');
      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));
      IF (_gotpriv) THEN
        PERFORM revokePriv(getEffectiveXtUser(), 'MaintainCustomerMasters');
      END IF;
    END IF;

    IF (NEW.crmacct_emp_id IS NOT NULL) THEN
      _gotpriv := grantPriv(getEffectiveXtUser(), 'MaintainEmployees');
      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));
      IF (_gotpriv) THEN
        PERFORM revokePriv(getEffectiveXtUser(), 'MaintainEmployees');
      END IF;
    END IF;

    IF (NEW.crmacct_prospect_id IS NOT NULL) THEN
      _gotpriv := grantPriv(getEffectiveXtUser(), 'MaintainProspectMasters');
      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));
      IF (_gotpriv) THEN
        PERFORM revokePriv(getEffectiveXtUser(), 'MaintainProspectMasters');
      END IF;
    END IF;

    IF (NEW.crmacct_salesrep_id IS NOT NULL) THEN
      _gotpriv := grantPriv(getEffectiveXtUser(), 'MaintainSalesReps');
      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));
      IF (_gotpriv) THEN
        PERFORM revokePriv(getEffectiveXtUser(), 'MaintainSalesReps');
      END IF;
    END IF;

    IF (NEW.crmacct_taxauth_id IS NOT NULL) THEN
      _gotpriv := grantPriv(getEffectiveXtUser(), 'MaintainTaxAuthorities');
      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));
      IF (_gotpriv) THEN
        PERFORM revokePriv(getEffectiveXtUser(), 'MaintainTaxAuthorities');
      END IF;
    END IF;

    IF (NEW.crmacct_vend_id IS NOT NULL) THEN
      _gotpriv := grantPriv(getEffectiveXtUser(), 'MaintainVendors');
      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));
      IF (_gotpriv) THEN
        PERFORM revokePriv(getEffectiveXtUser(), 'MaintainVendors');
      END IF;
    END IF;

    -- Link Primary and Secondary Contacts to this Account if they are not already
    IF (NEW.crmacct_cntct_id_1 IS NOT NULL) THEN
      _gotpriv := grantPriv(getEffectiveXtUser(), 'MaintainAllContacts');
      UPDATE cntct SET cntct_crmacct_id = NEW.crmacct_id
       WHERE cntct_id=NEW.crmacct_cntct_id_1;
      IF (_gotpriv) THEN
        PERFORM revokePriv(getEffectiveXtUser(), 'MaintainAllContacts');
      END IF;
    END IF;

    IF (NEW.crmacct_cntct_id_2 IS NOT NULL) THEN
      _gotpriv := grantPriv(getEffectiveXtUser(), 'MaintainAllContacts');
      UPDATE cntct SET cntct_crmacct_id = NEW.crmacct_id
       WHERE cntct_id=NEW.crmacct_cntct_id_2;
      IF (_gotpriv) THEN
        PERFORM revokePriv(getEffectiveXtUser(), 'MaintainAllContacts');
      END IF;
    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;

--
-- TOC entry 955 (class 1255 OID 146565467)
-- Dependencies: 4536 8
-- 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;
    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;

--
-- TOC entry 956 (class 1255 OID 146565468)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 957 (class 1255 OID 146565469)
-- Dependencies: 4536 8
-- 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';

  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;

--
-- TOC entry 958 (class 1255 OID 146565470)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 960 (class 1255 OID 146565471)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 961 (class 1255 OID 146565472)
-- Dependencies: 4536 8
-- 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;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._custtypeafterdeletetrigger() OWNER TO admin;

--
-- TOC entry 962 (class 1255 OID 146565473)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 963 (class 1255 OID 146565474)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 964 (class 1255 OID 146565475)
-- Dependencies: 4536 8
-- 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;

  RETURN OLD;
END;
$$;


ALTER FUNCTION public._empafterdeletetrigger() OWNER TO admin;

--
-- TOC entry 965 (class 1255 OID 146565476)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 966 (class 1255 OID 146565477)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 967 (class 1255 OID 146565478)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 968 (class 1255 OID 146565479)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 969 (class 1255 OID 146565480)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 970 (class 1255 OID 146565481)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 971 (class 1255 OID 146565482)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 972 (class 1255 OID 146565483)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 973 (class 1255 OID 146565484)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 974 (class 1255 OID 146565485)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 975 (class 1255 OID 146565486)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 976 (class 1255 OID 146565487)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 977 (class 1255 OID 146565488)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 978 (class 1255 OID 146565489)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 979 (class 1255 OID 146565490)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 980 (class 1255 OID 146565491)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 981 (class 1255 OID 146565492)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 982 (class 1255 OID 146565493)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 984 (class 1255 OID 146565494)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 985 (class 1255 OID 146565495)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 986 (class 1255 OID 146565496)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 987 (class 1255 OID 146565498)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 988 (class 1255 OID 146565499)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 989 (class 1255 OID 146565500)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 990 (class 1255 OID 146565501)
-- Dependencies: 4536 8
-- 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;

      --This could be made a table constraint later, but do not want to create a big problem
      --for users with problematic legacy data over a relatively trivial problem for now,
      --so we will just check moving forword.
      IF (NEW.itemsite_stocked AND NEW.itemsite_reorderlevel<=0) THEN
        RAISE EXCEPTION 'Stocked items must have postive reorder level specified.';
      END IF;
    END IF;

    IF (TG_OP = 'UPDATE') THEN
      --This could be made a table constraint later, but do not want to create a big problem
      --for users with problematic legacy data over a relatively trivial problem for now,
      --so we will just check moving forword.
      IF ((NEW.itemsite_stocked)
        AND (NEW.itemsite_stocked != OLD.itemsite_stocked) --Avoid checking unless explicitly changed
        AND (NEW.itemsite_reorderlevel<=0)) THEN
        RAISE EXCEPTION 'Stocked items must have postive reorder level specified.';
      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

        RAISE NOTICE 'Deleting item site detail records,';

        SELECT SUM(itemloc_qty) INTO _qty
        FROM itemloc, location
        WHERE ((itemloc_location_id=location_id)
        AND (NOT location_netable) 
        AND (itemloc_itemsite_id=OLD.itemsite_id));

        IF (_qty != 0) THEN
          UPDATE itemsite
          SET itemsite_qtyonhand = itemsite_qtyonhand + _qty,
            itemsite_nnqoh = itemsite_nnqoh - _qty
          WHERE (itemsite_id=OLD.itemsite_id);
        END IF;

        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 NOTICE '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;

--
-- TOC entry 991 (class 1255 OID 146565503)
-- Dependencies: 4536 8
-- 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
  SELECT item_type 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;

  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.', NEW.itemsite_id;
  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.', NEW.itemsite_id;
  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;

--
-- TOC entry 992 (class 1255 OID 146565504)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 993 (class 1255 OID 146565505)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 994 (class 1255 OID 146565506)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 995 (class 1255 OID 146565507)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 996 (class 1255 OID 146565508)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 997 (class 1255 OID 146565509)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 998 (class 1255 OID 146565510)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 999 (class 1255 OID 146565511)
-- Dependencies: 4536 8
-- 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

  -- Maintain itemsite_qtyonhand and itemsite_nnqoh when location_netable changes
  IF (TG_OP = 'UPDATE') THEN
    IF (OLD.location_netable <> NEW.location_netable) THEN
      FOR _itemloc IN SELECT * FROM itemloc WHERE (itemloc_location_id=NEW.location_id) LOOP
        IF (NEW.location_netable) THEN
          UPDATE itemsite SET itemsite_qtyonhand = itemsite_qtyonhand + _itemloc.itemloc_qty,
                              itemsite_nnqoh = itemsite_nnqoh - _itemloc.itemloc_qty
          WHERE (itemsite_id=_itemloc.itemloc_itemsite_id);
        ELSE
          UPDATE itemsite SET itemsite_qtyonhand = itemsite_qtyonhand - _itemloc.itemloc_qty,
                              itemsite_nnqoh = itemsite_nnqoh + _itemloc.itemloc_qty
          WHERE (itemsite_id=_itemloc.itemloc_itemsite_id);
        END IF;
      END LOOP;
    END IF;
  END IF;
  
  RETURN NEW;

END;
$$;


ALTER FUNCTION public._locationaftertrigger() OWNER TO admin;

--
-- TOC entry 1000 (class 1255 OID 146565512)
-- Dependencies: 4536 8
-- 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;
  
  RETURN NEW;

END;
$$;


ALTER FUNCTION public._locationtrigger() OWNER TO admin;

--
-- TOC entry 1001 (class 1255 OID 146565513)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 1002 (class 1255 OID 146565514)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 1004 (class 1255 OID 146565515)
-- Dependencies: 4536 8
-- 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;

--
-- TOC entry 1005 (class 1255 OID 146565516)
-- Dependencies: 4536 8
-- Name: _opheadbeforetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _opheadbeforetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _rec record;
  _check boolean;
  _test text;
BEGIN

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

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

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

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._opheadbeforetrigger() OWNER TO admin;

--
-- TOC entry 1006 (class 1255 OID 146565517)
-- Dependencies: 4536 8
-- Name: _packbeforetrigger(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION _packbeforetrigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _cmnttypeid INTEGER;
BEGIN
  SELECT cmnttype_id INTO _cmnttypeid
    FROM cmnttype
    WHERE (cmnttype_name='ChangeLog');
  IF (FOUND) THEN
    IF ((TG_OP = 'INSERT') AND (NEW.pack_head_id) IS NOT NULL)THEN
      PERFORM postComment(_cmnttypeid, 'S', NEW.pack_head_id, 'Added to Packing List Batch');
    END IF;
  END IF;
  IF ((TG_OP = 'INSERT') OR (TG_OP = 'UPDATE')) THEN
    IF (NEW.pack_shiphead_id IS NOT NULL
	 AND NEW.pack_shiphead_id NOT IN (SELECT shiphead_id
			       FROM shiphead
			       WHERE (shiphead_order_id=NEW.pack_head_id)
				 AND (shiphead_order_type=NEW.pack_head_type))) THEN
      RAISE EXCEPTION 'Shipment does not exist for % id %',
		      NEW.pack_head_type, NEW.pack_head_id;
      RETURN OLD;
    END IF;

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

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

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

  END IF;

  RETURN NEW;
END;
$$;


ALTER FUNCTION public._packbeforetrigger() OWNER TO admin;

--
-- TOC entry 1007 (class 1255 OID 146565518)
-- Dependencies: 4536 8
