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

CREATE FUNCTION createinvoiceconsolidated(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
  pCustid ALIAS FOR $1;
  _invcheadid INTEGER;
  _invcitemid INTEGER;
  _qtyToInvoice	NUMERIC;
  _r		RECORD;
  _s		RECORD;
  _c		RECORD;
  _i		RECORD;
  _count      INTEGER;
  _invcnumber INTEGER;
  _lastlinenumber INTEGER;

BEGIN
  _count := 0;

  FOR _c IN SELECT min(cobmisc_id) AS cobmisc_id, count(*) AS cnt,
-- there are the key values for consolidation
                   cohead_billtoname, cohead_billtoaddress1,
                   cohead_billtoaddress2, cohead_billtoaddress3,
                   cohead_billtocity, cohead_billtostate,
                   cohead_billtozipcode, cntct_phone AS cust_phone,
                   cohead_billtocountry,
                   cohead_salesrep_id, cohead_commission,
                   cohead_terms_id,
                   cobmisc_misc_accnt_id,
                   cohead_prj_id, cobmisc_curr_id,
                   cobmisc_taxzone_id,
                   cohead_shipchrg_id,
                   cohead_saletype_id,
                   cohead_shipzone_id,
                   
		-- the following are consolidated values to use in creating the header
                   MIN(cohead_number) AS cohead_number,
                   MIN(cohead_orderdate) AS cohead_orderdate,
                   MIN(cobmisc_invcdate) AS cobmisc_invcdate,
                   MIN(cobmisc_shipdate) AS cobmisc_shipdate,
                   SUM(cobmisc_freight) AS cobmisc_freight,
                   SUM(cobmisc_misc) AS cobmisc_misc,
                   SUM(cobmisc_payment) AS cobmisc_payment
                   
              FROM cobmisc
              JOIN cohead   ON (cobmisc_cohead_id=cohead_id)
              JOIN custinfo ON (cohead_cust_id=cust_id)
              LEFT OUTER JOIN cntct ON (cust_cntct_id=cntct_id)
             WHERE(NOT cobmisc_posted
               AND (cohead_cust_id=pCustid)
               )
          GROUP BY cohead_billtoname, cohead_billtoaddress1,
                   cohead_billtoaddress2, cohead_billtoaddress3,
                   cohead_billtocity, cohead_billtostate,
                   cohead_billtozipcode, cust_phone,
                   cohead_billtocountry,
                   cohead_salesrep_id, cohead_commission,
                   cohead_terms_id,
                   cobmisc_misc_accnt_id,
                   cohead_prj_id, cobmisc_curr_id,
                   cobmisc_taxzone_id,
                   cohead_shipchrg_id,
                   cohead_saletype_id,
                   cohead_shipzone_id
		LOOP

    IF(_c.cnt = 1) THEN
      PERFORM createInvoice(_c.cobmisc_id);
      _count := (_count + 1);
    ELSE
      SELECT NEXTVAL('invchead_invchead_id_seq'), fetchInvcNumber() INTO _invcheadid, _invcnumber;
  
  --  Create the Invoice header
      INSERT INTO invchead
      ( invchead_id, invchead_cust_id, invchead_shipto_id,
        invchead_ordernumber, invchead_orderdate,
        invchead_posted, invchead_printed,
        invchead_invcnumber, invchead_invcdate, invchead_shipdate,
        invchead_ponumber, invchead_shipvia, invchead_fob,
        invchead_billto_name, invchead_billto_address1,
        invchead_billto_address2, invchead_billto_address3,
        invchead_billto_city, invchead_billto_state,
        invchead_billto_zipcode, invchead_billto_phone,
        invchead_billto_country,
        invchead_shipto_name, invchead_shipto_address1,
        invchead_shipto_address2, invchead_shipto_address3,
        invchead_shipto_city, invchead_shipto_state,
        invchead_shipto_zipcode, invchead_shipto_phone,
        invchead_shipto_country,
        invchead_salesrep_id, invchead_commission,
        invchead_terms_id,
        invchead_freight,
        invchead_misc_amount, invchead_misc_descrip, invchead_misc_accnt_id,
        invchead_payment, invchead_paymentref,
        invchead_notes, invchead_prj_id, invchead_curr_id,
        invchead_taxzone_id,
        invchead_shipchrg_id,
        invchead_saletype_id, invchead_shipzone_id )
      VALUES(_invcheadid,
             pCustid, -1,
             NULL, _c.cohead_orderdate,
             FALSE, FALSE,
             _invcnumber, _c.cobmisc_invcdate, _c.cobmisc_shipdate,
             'MULTIPLE', '', '',
             _c.cohead_billtoname, _c.cohead_billtoaddress1,
             _c.cohead_billtoaddress2, _c.cohead_billtoaddress3,
             _c.cohead_billtocity, _c.cohead_billtostate,
             _c.cohead_billtozipcode, _c.cust_phone,
             _c.cohead_billtocountry,
             '', '', '', '', '', '', '', '', '',
             _c.cohead_salesrep_id, COALESCE(_c.cohead_commission, 0),
             _c.cohead_terms_id,
             _c.cobmisc_freight,
             _c.cobmisc_misc, CASE WHEN(_c.cobmisc_misc <> 0) THEN 'Multiple' ELSE '' END,
             _c.cobmisc_misc_accnt_id,
             _c.cobmisc_payment, '',
             'Multiple Sales Order # Invoice', _c.cohead_prj_id, _c.cobmisc_curr_id,
             _c.cobmisc_taxzone_id,
             _c.cohead_shipchrg_id,
             _c.cohead_saletype_id, _c.cohead_shipzone_id
             );
 
    _lastlinenumber := 1;
    FOR _i IN SELECT cobmisc_id
                FROM cobmisc
                JOIN cohead   ON (cobmisc_cohead_id=cohead_id)
                JOIN custinfo ON (cohead_cust_id=cust_id)
                LEFT OUTER JOIN cntct ON (cust_cntct_id=cntct_id)
               WHERE(NOT cobmisc_posted
                 AND (cohead_cust_id=pCustid)
                 AND (COALESCE(cohead_billtoname,'')         = COALESCE(_c.cohead_billtoname,''))
                 AND (COALESCE(cohead_billtoaddress1,'')     = COALESCE(_c.cohead_billtoaddress1,''))
                 AND (COALESCE(cohead_billtoaddress2,'')     = COALESCE(_c.cohead_billtoaddress2,''))
                 AND (COALESCE(cohead_billtoaddress3,'')     = COALESCE(_c.cohead_billtoaddress3,''))
                 AND (COALESCE(cohead_billtocity,'')         = COALESCE(_c.cohead_billtocity,''))
                 AND (COALESCE(cohead_billtostate,'')        = COALESCE(_c.cohead_billtostate,''))
                 AND (COALESCE(cohead_billtozipcode,'')      = COALESCE(_c.cohead_billtozipcode,''))
                 AND (COALESCE(cntct_phone,'')               = COALESCE(_c.cust_phone,''))
                 AND (COALESCE(cohead_billtocountry,'')      = COALESCE(_c.cohead_billtocountry,''))
                 AND (COALESCE(cohead_salesrep_id, 0)        = COALESCE(_c.cohead_salesrep_id, 0))
                 AND (COALESCE(cohead_commission, 0)         = COALESCE(_c.cohead_commission, 0))
                 AND (COALESCE(cohead_terms_id, 0)           = COALESCE(_c.cohead_terms_id, 0))
                 AND (COALESCE(cobmisc_misc_accnt_id, 0)     = COALESCE(_c.cobmisc_misc_accnt_id, 0))
                 AND (COALESCE(cohead_prj_id, 0)             = COALESCE(_c.cohead_prj_id, 0))
                 AND (COALESCE(cobmisc_curr_id, 0)           = COALESCE(_c.cobmisc_curr_id, 0))
                 AND (COALESCE(cobmisc_taxzone_id, 0)        = COALESCE(_c.cobmisc_taxzone_id, 0))
                 AND (COALESCE(cohead_saletype_id, 0)        = COALESCE(_c.cohead_saletype_id, 0))
                 AND (COALESCE(cohead_shipzone_id, 0)        = COALESCE(_c.cohead_shipzone_id, 0))
                ) LOOP

    --  Create the Invoice Head tax
        INSERT INTO invcheadtax(taxhist_parent_id, taxhist_taxtype_id, taxhist_tax_id, taxhist_basis, 
                                taxhist_basis_tax_id, taxhist_sequence, taxhist_percent, taxhist_amount, taxhist_tax, taxhist_docdate)
        SELECT _invcheadid,taxhist_taxtype_id, taxhist_tax_id, taxhist_basis, 
               taxhist_basis_tax_id, taxhist_sequence, taxhist_percent, taxhist_amount, taxhist_tax, taxhist_docdate
        FROM cobmisctax 
        WHERE taxhist_parent_id = _i.cobmisc_id
          AND taxhist_taxtype_id = getadjustmenttaxtypeid();

    --  Give this selection a number if it has not been assigned one
        UPDATE cobmisc
           SET cobmisc_invcnumber=_invcnumber
         WHERE(cobmisc_id=_i.cobmisc_id);
      
    --  Create the Invoice items
        FOR _r IN SELECT coitem_id, coitem_linenumber, coitem_subnumber, coitem_custpn,
                         coitem_qtyord, cobill_qty,
                         coitem_qty_uom_id, coitem_qty_invuomratio,
                         coitem_custprice, coitem_price,
                         coitem_price_uom_id, coitem_price_invuomratio,
                         coitem_memo,
                         itemsite_item_id, itemsite_warehous_id,
                         cobill_taxtype_id
                    FROM cohead, coitem, cobill, itemsite
                   WHERE((cobill_coitem_id=coitem_id)
                     AND (cohead_id=coitem_cohead_id)
                     AND (coitem_itemsite_id=itemsite_id)
                     AND (cobill_cobmisc_id=_i.cobmisc_id) ) 
                   ORDER BY cohead_number, coitem_linenumber, coitem_subnumber LOOP
      
          SELECT NEXTVAL('invcitem_invcitem_id_seq') INTO _invcitemid;
          INSERT INTO invcitem
          ( invcitem_id, invcitem_invchead_id,
            invcitem_linenumber, invcitem_item_id, invcitem_warehous_id,
            invcitem_custpn, invcitem_number, invcitem_descrip,
            invcitem_ordered, invcitem_billed,
            invcitem_qty_uom_id, invcitem_qty_invuomratio,
            invcitem_custprice, invcitem_price,
            invcitem_price_uom_id, invcitem_price_invuomratio,
            invcitem_notes,
            invcitem_taxtype_id,
            invcitem_coitem_id )
          VALUES
          ( _invcitemid, _invcheadid,
            _lastlinenumber,
            _r.itemsite_item_id, _r.itemsite_warehous_id,
            _r.coitem_custpn, '', '',
            _r.coitem_qtyord, _r.cobill_qty,
            _r.coitem_qty_uom_id, _r.coitem_qty_invuomratio,
            _r.coitem_custprice, _r.coitem_price,
            _r.coitem_price_uom_id, _r.coitem_price_invuomratio,
            _r.coitem_memo,
            _r.cobill_taxtype_id,
            _r.coitem_id );
          
      --  Find and mark any Lot/Serial invdetail records associated with this bill
          UPDATE invdetail SET invdetail_invcitem_id = _invcitemid
           WHERE (invdetail_id IN (SELECT invdetail_id
                                     FROM coitem, cohead, invhist, invdetail
                                    WHERE ((coitem_cohead_id=cohead_id)
                                      AND  (invdetail_invhist_id=invhist_id)
                                      AND  (invhist_ordnumber = text(cohead_number||'-'||formatSoLineNumber(coitem_id)))
                                      AND  (invdetail_invcitem_id IS NULL)
                                      AND  (coitem_id=_r.coitem_id)) ) );
      
      --  Mark any shipped, uninvoiced shipitems for the current coitem as invoiced
          _qtyToInvoice :=  _r.cobill_qty;
          FOR _s IN SELECT shipitem.*, shipitem_qty = _r.cobill_qty AS matched
	            FROM shipitem, shiphead
	            WHERE ((shipitem_shiphead_id=shiphead_id)
	              AND  (shipitem_orderitem_id=_r.coitem_id)
	              AND  (shiphead_shipped)
		      AND  (shiphead_order_type='SO')
	              AND  (NOT shipitem_invoiced))
	            ORDER BY matched DESC, shipitem_qty DESC FOR UPDATE LOOP
            IF (_qtyToInvoice >= _s.shipitem_qty) THEN
	      UPDATE shipitem
	      SET shipitem_invoiced=TRUE, shipitem_invcitem_id=_invcitemid
	      WHERE (shipitem_id=_s.shipitem_id);
	      _qtyToInvoice := _qtyToInvoice - _s.shipitem_qty;
            END IF;
            IF (_qtyToInvoice <= 0) THEN
	      EXIT;
            END IF;
          END LOOP;

          UPDATE cobill SET cobill_invcnum=cobmisc_invcnumber,
		          cobill_invcitem_id=invcitem_id
          FROM invcitem, coitem, cobmisc
          WHERE ((invcitem_linenumber=_lastlinenumber )
            AND  (coitem_id=cobill_coitem_id)
            AND  (cobmisc_id=cobill_cobmisc_id)
            AND  (cobill_cobmisc_id=_i.cobmisc_id)
            AND  (invcitem_invchead_id=_invcheadid));


          _lastlinenumber := _lastlinenumber + 1;
          
        END LOOP;
  
      --  Close all requested coitem's
        IF ( ( SELECT cobmisc_closeorder
               FROM cobmisc
               WHERE (cobmisc_id=_i.cobmisc_id) ) ) THEN
          UPDATE coitem
          SET coitem_status='C'
          FROM cobmisc
          WHERE ( (coitem_status NOT IN ('C', 'X'))
           AND (coitem_cohead_id=cobmisc_cohead_id)
           AND (cobmisc_id=_i.cobmisc_id) );
        ELSE
          UPDATE coitem
          SET coitem_status='C'
          FROM cobill
          WHERE ( (cobill_coitem_id=coitem_id)
           AND (coitem_status <> 'X')
           AND (cobill_toclose)
           AND (cobill_cobmisc_id=_i.cobmisc_id) );
        END IF;
      
      --  Mark the cobmisc as posted
        UPDATE cobmisc
        SET cobmisc_posted=TRUE, cobmisc_invchead_id=_invcheadid
        WHERE (cobmisc_id=_i.cobmisc_id);
    
      --  All done
        _count := (_count + 1);
      END LOOP;
    END IF;
  END LOOP;
  RETURN _count;
END;
$_$;


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

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

CREATE FUNCTION createinvoices() 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
  _counter INTEGER;
  _cobmisc RECORD;

BEGIN

  _counter := 0;

  FOR _cobmisc IN SELECT cobmisc_id
                  FROM cobmisc
                  WHERE (NOT cobmisc_posted) LOOP

    PERFORM createinvoice(_cobmisc.cobmisc_id);
    _counter := (_counter + 1);

  END LOOP;

  RETURN _counter;
END;
$$;


ALTER FUNCTION public.createinvoices() OWNER TO admin;

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

CREATE FUNCTION createinvoices(integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  RETURN createinvoices($1, false);
END;
$_$;


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

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

CREATE FUNCTION createinvoices(integer, boolean) 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
  pCustTypeId ALIAS FOR $1;
  pConsolidate ALIAS FOR $2;
  _counter INTEGER;
  _tcounter INTEGER;
  _cobmisc RECORD;

BEGIN

  _counter := 0;

  IF (pConsolidate) THEN
    FOR _cobmisc IN SELECT DISTINCT cust_id
                      FROM cobmisc, cohead, custinfo
                     WHERE((NOT cobmisc_posted)
                       AND (cohead_id=cobmisc_cohead_id)
                       AND (cust_id=cohead_cust_id)
                       AND (cust_custtype_id=pCustTypeId)) LOOP

      SELECT createinvoiceConsolidated(_cobmisc.cust_id)
        INTO _tcounter;
      _counter := (_counter + _tcounter);
    END LOOP;
  ELSE
    FOR _cobmisc IN SELECT cobmisc_id
                      FROM cobmisc, cohead, custinfo
                     WHERE((NOT cobmisc_posted)
                       AND (cohead_id=cobmisc_cohead_id)
                       AND (cust_id=cohead_cust_id)
                       AND (cust_custtype_id=pCustTypeId)) LOOP
  
      PERFORM createinvoice(_cobmisc.cobmisc_id);
      _counter := (_counter + 1);

    END LOOP;
  END IF;

  RETURN _counter;
END;
$_$;


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

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

CREATE FUNCTION createmiscapcheck(integer, integer, date, numeric, integer, text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  RAISE WARNING 'createMiscAPCheck() is deprecated - use createCheck() instead';
  RETURN createCheck($1, 'V', $2, $3, $4, baseCurrId(), $5, NULL, $6, $7, FALSE);
END;
$_$;


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

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

CREATE FUNCTION createmiscapcheck(integer, integer, date, numeric, integer, integer, text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  RAISE WARNING 'createMiscAPCheck() is deprecated - use createCheck() instead';
  RETURN createCheck($1, 'V', $2, $3, pAmount, $5, $6, NULL, $7, $8, FALSE);
END;
$_$;


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

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

CREATE FUNCTION createpkgschema(text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pname         ALIAS FOR $1;
  pcomment      ALIAS FOR $2;
  _namespaceoid INTEGER := -1;
  _tabs         TEXT[] := ARRAY['cmd',  'cmdarg', 'image',  'metasql',
                                'priv', 'report', 'script', 'uiform'] ;
  _pkgtab       TEXT;

BEGIN
  IF (LENGTH(COALESCE(pname, '')) <= 0) THEN
    RAISE EXCEPTION 'Cannot create a schema for this package without a name.';
  END IF;

  SELECT oid INTO _namespaceoid
  FROM pg_namespace
  WHERE (LOWER(nspname)=LOWER(pname));
  IF (NOT FOUND) THEN
    EXECUTE format('CREATE SCHEMA %s;', LOWER(pname));
    EXECUTE format('GRANT ALL ON SCHEMA %s TO GROUP xtrole;', LOWER(pname));

    SELECT oid INTO _namespaceoid
    FROM pg_namespace
    WHERE (LOWER(nspname)=LOWER(pname));
  END IF;

  FOR i IN ARRAY_LOWER(_tabs,1)..ARRAY_UPPER(_tabs,1) LOOP
    _pkgtab := 'pkg' || _tabs[i];

    IF NOT EXISTS(SELECT oid
                  FROM pg_class
                  WHERE ((relname=_pkgtab)
                     AND (relnamespace=_namespaceoid))) THEN
      EXECUTE format('CREATE TABLE %s.%s () INHERITS (%s);',
                     pname, _pkgtab, _tabs[i]);

      EXECUTE format($f$ALTER TABLE %s.%s ALTER %s_id SET NOT NULL,
                         ADD PRIMARY KEY (%s_id),
                         ALTER %s_id SET DEFAULT NEXTVAL('%s_%s_id_seq');$f$,
                     pname, _pkgtab, _tabs[i], _tabs[i], _tabs[i], _tabs[i], _tabs[i]);

      EXECUTE format('REVOKE ALL ON %s.%s FROM PUBLIC;',     pname, _pkgtab);
      EXECUTE format('GRANT  ALL ON %s.%s TO GROUP xtrole;', pname, _pkgtab);

      IF (_tabs[i] = 'cmdarg') THEN
        EXECUTE format($f$ALTER TABLE %s.%s ADD FOREIGN KEY (cmdarg_cmd_id)
                          REFERENCES %s.pkgcmd(cmd_id);$f$,
                       pname, _pkgtab, pname);
      END IF;

      EXECUTE format('DROP TRIGGER IF EXISTS %sbeforetrigger ON %s.%s;',
                     _pkgtab, pname, _pkgtab);
      EXECUTE format('CREATE TRIGGER %sbeforetrigger' ||
                     ' BEFORE INSERT OR UPDATE OR DELETE ON %s.%s' ||
                     ' FOR EACH ROW EXECUTE PROCEDURE _%sbeforetrigger();',
                      _pkgtab, pname, _pkgtab, _pkgtab);

      EXECUTE format('DROP TRIGGER IF EXISTS %saltertrigger ON %s.%s;',
                      _pkgtab, pname, _pkgtab);
      EXECUTE format('CREATE TRIGGER %saltertrigger' ||
                     ' BEFORE INSERT OR UPDATE OR DELETE ON %s.%s' ||
                     ' FOR EACH ROW EXECUTE PROCEDURE _%saltertrigger();',
                     _pkgtab, pname, _pkgtab, _pkgtab);

      EXECUTE format('DROP TRIGGER IF EXISTS %saftertrigger ON %s.%s;',
                     _pkgtab, pname, _pkgtab);
      EXECUTE format('CREATE TRIGGER %saftertrigger' ||
                     ' AFTER INSERT OR UPDATE OR DELETE ON %s.%s' ||
                     ' FOR EACH ROW EXECUTE PROCEDURE _%saftertrigger();',
                     _pkgtab, pname, _pkgtab, _pkgtab);

    END IF;
  END LOOP;

  EXECUTE 'COMMENT ON SCHEMA ' || quote_ident(pname) || ' IS ' ||
           quote_literal(pcomment) || ';';

  RETURN _namespaceoid;
END;
$_$;


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

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

CREATE FUNCTION createpr(character, 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
  pParentType ALIAS FOR $1;
  pParentId ALIAS FOR $2;
  _orderNumber INTEGER;
  _prid INTEGER;

BEGIN

  IF (pParentType = 'W') THEN
    SELECT wo_number INTO _orderNumber
    FROM wo, womatl
    WHERE ((womatl_wo_id=wo_id)
     AND (womatl_id=pParentId));

  ELSIF (pParentType = 'S') THEN
    SELECT CAST(cohead_number AS INTEGER) INTO _orderNumber
    FROM cohead, coitem
    WHERE ((coitem_cohead_id=cohead_id)
     AND (coitem_id=pParentId));

  ELSIF (pParentType = 'F') THEN
    SELECT fetchPrNumber() INTO _orderNumber;

  ELSE
    RETURN -2;
  END IF;

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

  SELECT createPr(_orderNumber, pParentType, pParentId) INTO _prid;

  RETURN _prid;

END;
$_$;


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

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

CREATE FUNCTION createpr(integer, character, 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
  pOrderNumber ALIAS FOR $1;
  pParentType ALIAS FOR $2;
  pParentId ALIAS FOR $3;
  _parent RECORD;
  _prid INTEGER;
  _orderNumber INTEGER;

BEGIN

  IF (pOrderNumber = -1) THEN
    SELECT fetchPrNumber() INTO _orderNumber;
  ELSE
    _orderNumber := pOrderNumber;
  END IF;

  IF (pParentType = 'W') THEN
    SELECT womatl_itemsite_id AS itemsiteid,
           itemuomtouom(itemsite_item_id, womatl_uom_id, NULL, womatl_qtyreq) AS qty,
           womatl_duedate AS duedate, wo_prj_id AS prjid,
           womatl_notes AS notes INTO _parent
    FROM wo, womatl, itemsite
    WHERE ((womatl_wo_id=wo_id)
     AND (womatl_itemsite_id=itemsite_id)
     AND (womatl_id=pParentId));

  ELSIF (pParentType = 'S') THEN
    SELECT coitem_itemsite_id AS itemsiteid,
           (coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) AS qty,
           coitem_scheddate AS duedate, cohead_prj_id AS prjid,
           coitem_memo AS notes INTO _parent
    FROM coitem, cohead
    WHERE ((cohead_id=coitem_cohead_id)
     AND (coitem_id=pParentId));

  ELSIF (pParentType = 'F') THEN
    SELECT planord_itemsite_id AS itemsiteid,
           planord_qty AS qty,
           planord_duedate AS duedate, NULL::INTEGER AS prjid,
           planord_comments AS notes INTO _parent
    FROM planord
    WHERE (planord_id=pParentId);

  ELSE
    RETURN -2;
  END IF;

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

  SELECT NEXTVAL('pr_pr_id_seq') INTO _prid;
  INSERT INTO pr
  ( pr_id, pr_number, pr_subnumber, pr_status,
    pr_order_type, pr_order_id, pr_prj_id,
    pr_itemsite_id, pr_qtyreq,
    pr_duedate, pr_releasenote )
  VALUES
  ( _prid, _orderNumber, nextPrSubnumber(_orderNumber), 'O',
    pParentType, pParentId, _parent.prjid,
    _parent.itemsiteid, validateOrderQty(_parent.itemsiteid, _parent.qty, TRUE),
    _parent.duedate, _parent.notes );

  RETURN _prid;

END;
$_$;


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

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

CREATE FUNCTION createpr(integer, character, integer, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pOrderNumber ALIAS FOR $1;
  pParentType ALIAS FOR $2;
  pParentId ALIAS FOR $3;
  pParentNotes ALIAS FOR $4;
  _parent RECORD;
  _prid INTEGER;
  _orderNumber INTEGER;

BEGIN

  IF (pOrderNumber = -1) THEN
    SELECT fetchPrNumber() INTO _orderNumber;
  ELSE
    _orderNumber := pOrderNumber;
  END IF;

  IF (pParentType = 'W') THEN
    SELECT womatl_itemsite_id AS itemsiteid,
           itemuomtouom(itemsite_item_id, womatl_uom_id, NULL, womatl_qtyreq) AS qty,
           womatl_duedate AS duedate, wo_prj_id AS prjid INTO _parent
    FROM wo, womatl, itemsite
    WHERE ((womatl_wo_id=wo_id)
     AND (womatl_itemsite_id=itemsite_id)
     AND (womatl_id=pParentId));

  ELSIF (pParentType = 'S') THEN
    SELECT coitem_itemsite_id AS itemsiteid,
           (coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) AS qty,
           coitem_scheddate AS duedate, cohead_prj_id AS prjid INTO _parent
    FROM coitem, cohead
    WHERE ((cohead_id=coitem_cohead_id)
     AND (coitem_id=pParentId));

  ELSIF (pParentType = 'F') THEN
    SELECT planord_itemsite_id AS itemsiteid,
           planord_qty AS qty,
           planord_duedate AS duedate, NULL::INTEGER AS prjid 
           INTO _parent
    FROM planord
    WHERE (planord_id=pParentId);

  ELSE
    RETURN -2;
  END IF;

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

  SELECT NEXTVAL('pr_pr_id_seq') INTO _prid;
  INSERT INTO pr
  ( pr_id, pr_number, pr_subnumber, pr_status,
    pr_order_type, pr_order_id, pr_prj_id,
    pr_itemsite_id, pr_qtyreq, pr_duedate, pr_releasenote )
  VALUES
  ( _prid, _orderNumber, nextPrSubnumber(_orderNumber), 'O',
    pParentType, pParentId, _parent.prjid,
    _parent.itemsiteid, validateOrderQty(_parent.itemsiteid, _parent.qty, TRUE),
    _parent.duedate, pParentNotes );

  RETURN _prid;

END;
$_$;


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

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

CREATE FUNCTION createpr(integer, integer, numeric, date, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pOrderNumber ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pQty ALIAS FOR $3;
  pDueDate ALIAS FOR $4;
  pNotes ALIAS FOR $5;
  _prid INTEGER;

BEGIN

  SELECT NEXTVAL('pr_pr_id_seq') INTO _prid;
  INSERT INTO pr
  ( pr_id, pr_number, pr_subnumber, pr_status,
    pr_order_type, pr_order_id,
    pr_itemsite_id, pr_qtyreq, pr_duedate, pr_releasenote )
  VALUES
  ( _prid, pOrderNumber, nextPrSubnumber(pOrderNumber), 'O',
    'M', -1,
    pItemsiteid, pQty, pDuedate, pNotes);

  RETURN _prid;

END;
$_$;


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

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

CREATE FUNCTION createpr(integer, integer, numeric, date, text, character, 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
  pOrderNumber ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pQty ALIAS FOR $3;
  pDueDate ALIAS FOR $4;
  pNotes ALIAS FOR $5;
  pOrderType ALIAS FOR $6;
  pOrderId ALIAS FOR $7;
  _prid INTEGER;

BEGIN

  SELECT NEXTVAL('pr_pr_id_seq') INTO _prid;
  INSERT INTO pr
  ( pr_id, pr_number, pr_subnumber, pr_status,
    pr_order_type, pr_order_id,
    pr_itemsite_id, pr_qtyreq, pr_duedate, pr_releasenote )
  VALUES
  ( _prid, pOrderNumber, nextPrSubnumber(pOrderNumber), 'O',
    pOrderType, pOrderId,
    pItemsiteid, pQty, pDuedate, pNotes );

  RETURN _prid;

END;
$_$;


ALTER FUNCTION public.createpr(integer, integer, numeric, date, text, character, integer) OWNER TO admin;

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

CREATE FUNCTION createpriv(text, text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pModule ALIAS FOR $1;
  pName   ALIAS FOR $2;
  pDesc   ALIAS FOR $3;
  _id     INTEGER;
BEGIN

  SELECT priv_id
    INTO _id
    FROM priv
   WHERE(priv_name=pName);

  IF (FOUND) THEN
    UPDATE priv
       SET priv_module=pModule,
           priv_descrip=pDesc
     WHERE(priv_id=_id);
  ELSE
    SELECT nextval('priv_priv_id_seq') INTO _id;
    INSERT INTO priv
          (priv_id, priv_module, priv_name, priv_descrip)
    VALUES(_id, pModule, pName, pDesc);
  END IF;

  RETURN _id;
END;
$_$;


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

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

CREATE FUNCTION createpurchasetoquote(pquitemid integer, pitemsourceid integer, pdropship boolean, pprice numeric DEFAULT NULL::numeric) RETURNS integer
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2015 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _pohead INTEGER := NULL; -- Legacy parameter
  _s RECORD;
  _w RECORD;
  _i RECORD;
  _c RECORD;
  _shipto RECORD;
  _poheadid INTEGER := -1;
  _poitemid INTEGER := -1;
  _taxtypeid INTEGER := -1;
  _charassid INTEGER := -1;
  _polinenumber INTEGER;
  _ponumber NUMERIC;
  _price NUMERIC;
  _temp INTEGER;

BEGIN
  -- Check for existing poitem for this quitem
  SELECT poitem_id INTO _poitemid
  FROM poitem
  WHERE (poitem_order_id=pQuitemId)
    AND (poitem_order_type='S');
  IF (FOUND) THEN
    RETURN _poitemid;
  END IF;

  SELECT quhead_id, quhead_prj_id, quhead_shipcomments, quhead_shipto_cntct_email,
    quhead_shipto_cntct_fax, quhead_shipto_cntct_first_name, quhead_shipto_cntct_honorific,
    quhead_shipto_cntct_id, quhead_shipto_cntct_last_name, quhead_shipto_cntct_middle,
    quhead_shipto_cntct_phone, quhead_shipto_cntct_suffix, quhead_shipto_cntct_title,
    quhead_shiptoaddress1, quhead_shiptoaddress2, quhead_shiptoaddress3, quhead_shiptocity,
    quhead_shiptocountry, quhead_shiptoname, quhead_shiptostate, quhead_shiptozipcode,
    quhead_warehous_id, quitem_itemsite_id, quitem_memo, quitem_scheddate, addr_city,
    addr_country, addr_id, addr_line1, addr_line2, addr_line3, addr_postalcode,
    addr_state, cntct_email, cntct_fax, cntct_first_name, cntct_honorific, cntct_last_name
    cntct_middle, cntct_phone, cntct_suffix, cntct_title, itemsite_id, shipto_addr_id
    shipto_cntct_id, shipto_name,
    COALESCE(roundQty(item_fractional, (quitem_qtyord * quitem_qty_invuomratio)), 0.0) AS orderqty
  INTO _s
  FROM quitem JOIN quhead ON (quhead_id = quitem_quhead_id)
              LEFT OUTER JOIN shiptoinfo ON (quhead_shipto_id = shipto_id)
              LEFT OUTER JOIN addr ON (shipto_addr_id = addr_id)
              LEFT OUTER JOIN cntct ON (shipto_cntct_id = cntct_id)
              LEFT OUTER JOIN itemsite ON (quitem_itemsite_id = itemsite_id)
              LEFT OUTER JOIN item ON (item_id = itemsite_item_id)
  WHERE (quitem_id = pQuitemId);
  IF (NOT FOUND) THEN
    RETURN -1;
  END IF;

  SELECT addr_city, addr_country, addr_id, addr_line1, addr_line2,
    addr_line3, addr_postalcode, addr_state, cntct_email, cntct_fax,
    cntct_first_name, cntct_honorific, cntct_id, cntct_last_name,
    cntct_middle, cntct_phone, cntct_suffix, cntct_title
  INTO _w
  FROM itemsite JOIN whsinfo ON (warehous_id = itemsite_warehous_id)
                LEFT OUTER JOIN addr ON (warehous_addr_id = addr_id)
                LEFT OUTER JOIN cntct ON (warehous_cntct_id = cntct_id)
  WHERE (itemsite_id = _s.itemsite_id);

  SELECT itemsrc_vend_id, vend_taxzone_id, vend_curr_id, vend_shipvia,
    vend_terms_id, cntct_id, addr_city, addr_country, addr_line1, addr_line2,
    addr_line3, addr_postalcode, addr_state, cntct_email, cntct_fax, cntct_first_name,
    cntct_honorific, cntct_last_name, cntct_middle, cntct_phone, cntct_suffix, cntct_title,
    itemsrc_invvendoruomratio, itemsrc_item_id, itemsrc_manuf_item_descrip,
    itemsrc_manuf_item_number, itemsrc_manuf_name, itemsrc_vend_item_descrip,
    itemsrc_vend_item_number, itemsrc_vend_uom
  INTO _i
  FROM itemsrc JOIN vendinfo ON (itemsrc_vend_id = vend_id)
               LEFT OUTER JOIN cntct ON (vend_cntct1_id = cntct_id)
               LEFT OUTER JOIN addr ON (vend_addr_id = addr_id)
  WHERE (itemsrc_id = pItemSourceId);
  IF (NOT FOUND) THEN
    RETURN -2;
  END IF;

  -- _pohead - NULL=add to existing PO if one exists
  --               -1=must create new PO
  --               >0=must add to existing specified PO
  IF (pDropShip) THEN
    SELECT COALESCE(pohead_id, -1) INTO _temp
    FROM pohead
    WHERE ( (pohead_status = 'U')
      AND (pohead_vend_id = _i.itemsrc_vend_id)
      AND (COALESCE(pohead_shiptoname, '') = COALESCE(_s.quhead_shiptoname, _s.shipto_name, ''))
      AND (COALESCE(pohead_shiptoaddress1, '') = COALESCE(_s.quhead_shiptoaddress1, _s.addr_line1, ''))
      AND (COALESCE(pohead_shiptoaddress2, '') = COALESCE(_s.quhead_shiptoaddress2, _s.addr_line2, ''))
      AND (COALESCE(pohead_shiptoaddress3, '') = COALESCE(_s.quhead_shiptoaddress3, _s.addr_line3, ''))
      AND (COALESCE(pohead_shiptocity, '') = COALESCE(_s.quhead_shiptocity, _s.addr_city, ''))
      AND (COALESCE(pohead_shiptostate, '') = COALESCE(_s.quhead_shiptostate, _s.addr_state, ''))
      AND (COALESCE(pohead_shiptozipcode, '') = COALESCE(_s.quhead_shiptozipcode, _s.addr_postalcode, ''))
      AND (COALESCE(pohead_shiptocountry, '') = COALESCE(_s.quhead_shiptocountry, _s.addr_country, ''))
      AND ((pohead_id=_pohead) OR (_pohead IS NULL)) );
  ELSE
    SELECT COALESCE(pohead_id, -1) INTO _temp
    FROM pohead
    WHERE ( (pohead_status = 'U')
      AND (pohead_vend_id = _i.itemsrc_vend_id)
      AND (COALESCE(pohead_shiptoaddress1, '') = COALESCE(_w.addr_line1, ''))
      AND (COALESCE(pohead_shiptoaddress2, '') = COALESCE(_w.addr_line2, ''))
      AND (COALESCE(pohead_shiptoaddress3, '') = COALESCE(_w.addr_line3, ''))
      AND (COALESCE(pohead_shiptocity, '') = COALESCE(_w.addr_city, ''))
      AND (COALESCE(pohead_shiptostate, '') = COALESCE(_w.addr_state, ''))
      AND (COALESCE(pohead_shiptozipcode, '') = COALESCE(_w.addr_postalcode, ''))
      AND (COALESCE(pohead_shiptocountry, '') = COALESCE(_w.addr_country, ''))
      AND ((pohead_id=_pohead) OR (_pohead IS NULL)) );
  END IF;

  IF (FOUND) THEN
    -- Add to Existing
    _poheadid := _temp;
    UPDATE pohead
    SET pohead_dropship = pDropShip
    WHERE (pohead_id = _poheadid);
  ELSE
    -- Create New
    SELECT NEXTVAL('pohead_pohead_id_seq'), fetchPoNumber()
     INTO _poheadid, _ponumber;

    IF (pDropShip) THEN
      INSERT INTO pohead
        ( pohead_id, pohead_number, pohead_status, pohead_dropship,
          pohead_agent_username, pohead_vend_id, pohead_taxzone_id,
          pohead_orderdate, pohead_curr_id,
          pohead_warehous_id, pohead_shipvia,
          pohead_terms_id, pohead_shipto_cntct_id,
          pohead_shipto_cntct_honorific, pohead_shipto_cntct_first_name,
          pohead_shipto_cntct_middle, pohead_shipto_cntct_last_name,
          pohead_shipto_cntct_suffix, pohead_shipto_cntct_phone,
          pohead_shipto_cntct_title, pohead_shipto_cntct_fax,
          pohead_shipto_cntct_email, pohead_shiptoaddress_id,
          pohead_shiptoname,
          pohead_shiptoaddress1,
          pohead_shiptoaddress2,
          pohead_shiptoaddress3,
          pohead_shiptocity,
          pohead_shiptostate, pohead_shiptozipcode,
          pohead_shiptocountry, pohead_vend_cntct_id,
          pohead_vend_cntct_honorific, pohead_vend_cntct_first_name,
          pohead_vend_cntct_middle, pohead_vend_cntct_last_name,
          pohead_vend_cntct_suffix, pohead_vend_cntct_phone,
          pohead_vend_cntct_title, pohead_vend_cntct_fax,
          pohead_vend_cntct_email, pohead_vendaddress1,
          pohead_vendaddress2, pohead_vendaddress3,
          pohead_vendcity, pohead_vendstate,
          pohead_vendzipcode, pohead_vendcountry, pohead_comments )
      VALUES
        ( _poheadid, _ponumber, 'U', pDropShip,
          getEffectiveXtUser(), _i.itemsrc_vend_id, _i.vend_taxzone_id,
	  CURRENT_DATE, COALESCE(_i.vend_curr_id, basecurrid()),
          COALESCE(_s.quhead_warehous_id, -1), COALESCE(_i.vend_shipvia, TEXT('')),
          COALESCE(_i.vend_terms_id, -1), COALESCE(_s.quhead_shipto_cntct_id, _s.shipto_cntct_id),
          COALESCE(_s.quhead_shipto_cntct_honorific, _s.cntct_honorific), COALESCE(_s.quhead_shipto_cntct_first_name, _s.cntct_first_name),
          COALESCE(_s.quhead_shipto_cntct_middle, _s.cntct_middle), COALESCE(_s.quhead_shipto_cntct_last_name, _s.cntct_last_name),
          COALESCE(_s.quhead_shipto_cntct_suffix, _s.cntct_suffix), COALESCE(_s.quhead_shipto_cntct_phone, _s.cntct_phone),
          COALESCE(_s.quhead_shipto_cntct_title, _s.cntct_title), COALESCE(_s.quhead_shipto_cntct_fax, _s.cntct_fax),
          COALESCE(_s.quhead_shipto_cntct_email, _s.cntct_email), COALESCE(_s.shipto_addr_id, _s.addr_id),
          COALESCE(_s.quhead_shiptoname, _s.shipto_name, ''),
          COALESCE(_s.quhead_shiptoaddress1, _s.addr_line1, ''),
          COALESCE(_s.quhead_shiptoaddress2, _s.addr_line2, ''),
          COALESCE(_s.quhead_shiptoaddress3, _s.addr_line3, ''),
          COALESCE(_s.quhead_shiptocity, _s.addr_city, ''),
          COALESCE(_s.quhead_shiptostate, _s.addr_state, ''), COALESCE(_s.quhead_shiptozipcode, _s.addr_postalcode, ''),
          COALESCE(_s.quhead_shiptocountry, _s.addr_country, ''), _i.cntct_id,
          COALESCE(_i.cntct_honorific, TEXT('')), COALESCE(_i.cntct_first_name, TEXT('')),
          COALESCE(_i.cntct_middle, TEXT('')), COALESCE(_i.cntct_last_name, TEXT('')),
          COALESCE(_i.cntct_suffix, TEXT('')), COALESCE(_i.cntct_phone, TEXT('')),
          COALESCE(_i.cntct_title, TEXT('')), COALESCE(_i.cntct_fax, TEXT('')),
          COALESCE(_i.cntct_email, TEXT('')), COALESCE(_i.addr_line1, TEXT('')),
          COALESCE(_i.addr_line2, TEXT('')), COALESCE(_i.addr_line3, TEXT('')),
          COALESCE(_i.addr_city, TEXT('')), COALESCE(_i.addr_state, TEXT('')),
          COALESCE(_i.addr_postalcode, TEXT('')), COALESCE(_i.addr_country, TEXT('')), COALESCE(_s.quhead_shipcomments, TEXT('')) );
    ELSE
      INSERT INTO pohead
        ( pohead_id, pohead_number, pohead_status, pohead_dropship,
          pohead_agent_username, pohead_vend_id, pohead_taxzone_id,
          pohead_orderdate, pohead_curr_id,
          pohead_warehous_id, pohead_shipvia,
          pohead_terms_id, pohead_shipto_cntct_id,
          pohead_shipto_cntct_honorific, pohead_shipto_cntct_first_name,
          pohead_shipto_cntct_middle, pohead_shipto_cntct_last_name,
          pohead_shipto_cntct_suffix, pohead_shipto_cntct_phone,
          pohead_shipto_cntct_title, pohead_shipto_cntct_fax,
          pohead_shipto_cntct_email, pohead_shiptoaddress_id,
          pohead_shiptoaddress1,
          pohead_shiptoaddress2,
          pohead_shiptoaddress3,
          pohead_shiptocity,
          pohead_shiptostate, pohead_shiptozipcode,
          pohead_shiptocountry, pohead_vend_cntct_id,
          pohead_vend_cntct_honorific, pohead_vend_cntct_first_name,
          pohead_vend_cntct_middle, pohead_vend_cntct_last_name,
          pohead_vend_cntct_suffix, pohead_vend_cntct_phone,
          pohead_vend_cntct_title, pohead_vend_cntct_fax,
          pohead_vend_cntct_email, pohead_vendaddress1,
          pohead_vendaddress2, pohead_vendaddress3,
          pohead_vendcity, pohead_vendstate,
          pohead_vendzipcode, pohead_vendcountry )
      VALUES
        ( _poheadid, _ponumber, 'U', pDropShip,
          getEffectiveXtUser(), _i.itemsrc_vend_id, _i.vend_taxzone_id,
	  CURRENT_DATE, COALESCE(_i.vend_curr_id, basecurrid()),
          COALESCE(_s.quhead_warehous_id, -1), COALESCE(_i.vend_shipvia, TEXT('')),
          COALESCE(_i.vend_terms_id, -1), _w.cntct_id,
          _w.cntct_honorific, _w.cntct_first_name,
          _w.cntct_middle, _w.cntct_last_name,
          _w.cntct_suffix, _w.cntct_phone,
          _w.cntct_title, _w.cntct_fax,
          _w.cntct_email, _w.addr_id,
          COALESCE(_w.addr_line1, ''),
          COALESCE(_w.addr_line2, ''),
          COALESCE(_w.addr_line3, ''),
          COALESCE(_w.addr_city, ''),
          COALESCE(_w.addr_state, ''), COALESCE(_w.addr_postalcode, ''),
          COALESCE(_w.addr_country, ''), _i.cntct_id,
          COALESCE(_i.cntct_honorific, TEXT('')), COALESCE(_i.cntct_first_name, TEXT('')),
          COALESCE(_i.cntct_middle, TEXT('')), COALESCE(_i.cntct_last_name, TEXT('')),
          COALESCE(_i.cntct_suffix, TEXT('')), COALESCE(_i.cntct_phone, TEXT('')),
          COALESCE(_i.cntct_title, TEXT('')), COALESCE(_i.cntct_fax, TEXT('')),
          COALESCE(_i.cntct_email, TEXT('')), COALESCE(_i.addr_line1, TEXT('')),
          COALESCE(_i.addr_line2, TEXT('')), COALESCE(_i.addr_line3, TEXT('')),
          COALESCE(_i.addr_city, TEXT('')), COALESCE(_i.addr_state, TEXT('')),
          COALESCE(_i.addr_postalcode, TEXT('')), COALESCE(_i.addr_country, TEXT('')) );
    END IF;
  END IF;

  -- Copy characteristics from the quhead to the pohead
  -- while avoiding duplicates
  FOR _c IN
  SELECT charass.*
    FROM charass
    JOIN char    ON char_id = charass_char_id
    JOIN charuse ON char_id = charuse_char_id AND charuse_target_type = 'PO'
   WHERE charass_target_type = 'QU'
     AND charass_target_id   = _s.quhead_id
  LOOP
    SELECT charass_id INTO _charassid
    FROM charass
    WHERE ( (charass_target_type='PO')
      AND   (charass_target_id=_poheadid)
      AND   (charass_char_id=_c.charass_char_id)
      AND   (charass_value=_c.charass_value) );
    IF (NOT FOUND) THEN
      INSERT INTO charass
        ( charass_target_type, charass_target_id, charass_char_id,
          charass_value, charass_default, charass_price )
      VALUES
        ( 'PO', _poheadid, _c.charass_char_id,
          _c.charass_value, _c.charass_default, _c.charass_price );
    END IF;
  END LOOP;

  SELECT NEXTVAL('poitem_poitem_id_seq') INTO _poitemid;

  SELECT (COALESCE(MAX(poitem_linenumber), 0) + 1) INTO _polinenumber
  FROM poitem
  WHERE (poitem_pohead_id = _poheadid);

  SELECT COALESCE(itemtax_taxtype_id, -1) INTO _taxtypeid
  FROM itemtax
  WHERE (itemtax_item_id = _i.itemsrc_item_id);

  IF (pPrice IS NULL) THEN
    SELECT itemsrcPrice(pItemSourceId,
                        COALESCE(_s.quhead_warehous_id, -1),
                        pDropShip,
                        (_s.orderqty / COALESCE(_i.itemsrc_invvendoruomratio, 1.00)),
                        COALESCE(_i.vend_curr_id, baseCurrId()),
                        CURRENT_DATE) INTO _price;
  ELSE
    _price := pPrice;
  END IF;

  INSERT INTO poitem
      ( poitem_id, poitem_status, poitem_pohead_id, poitem_linenumber,
        poitem_duedate, poitem_itemsite_id,
        poitem_vend_item_descrip, poitem_vend_uom,
        poitem_invvenduomratio, poitem_qty_ordered,
        poitem_unitprice, poitem_vend_item_number,
        poitem_itemsrc_id, poitem_order_id, poitem_order_type, poitem_prj_id, poitem_stdcost,
        poitem_manuf_name, poitem_manuf_item_number,
        poitem_manuf_item_descrip, poitem_taxtype_id, poitem_comments )
  VALUES
      ( _poitemid, 'U', _poheadid, _polinenumber,
        _s.quitem_scheddate, _s.quitem_itemsite_id,
        COALESCE(_i.itemsrc_vend_item_descrip, TEXT('')), COALESCE(_i.itemsrc_vend_uom, TEXT('')),
        COALESCE(_i.itemsrc_invvendoruomratio, 1.00), (_s.orderqty / COALESCE(_i.itemsrc_invvendoruomratio, 1.00)),
        _price, COALESCE(_i.itemsrc_vend_item_number, TEXT('')),
        pItemSourceId, pQuitemId, 'Q', _s.quhead_prj_id, stdcost(_i.itemsrc_item_id),
        COALESCE(_i.itemsrc_manuf_name, TEXT('')), COALESCE(_i.itemsrc_manuf_item_number, TEXT('')),
        COALESCE(_i.itemsrc_manuf_item_descrip, TEXT('')), _taxtypeid,
        COALESCE(_s.quitem_memo, TEXT('')));

  -- Copy characteristics from the quitem to the poitem
  INSERT INTO charass
    ( charass_target_type, charass_target_id, charass_char_id,
      charass_value, charass_default, charass_price )
  SELECT 'PI', _poitemid, charass_char_id,
         charass_value, charass_default, charass_price
    FROM charass
    JOIN char    ON char_id = charass_char_id
    JOIN charuse ON char_id = charuse_char_id AND charuse_target_type = 'PI'
   WHERE charass_target_type = 'QI'
     AND charass_target_id = pQuitemId;

  -- Generate the PoItemCreatedBySo event notice
  PERFORM postEvent('PoItemCreatedBySo', 'P', poitem_id,
                    itemsite_warehous_id,
                    (pohead_number || '-'|| poitem_linenumber || ': ' || item_number),
                    NULL, NULL, NULL, NULL)
  FROM poitem JOIN pohead ON (pohead_id=poitem_pohead_id)
              JOIN itemsite ON (itemsite_id=poitem_itemsite_id)
              JOIN item ON (item_id=itemsite_item_id)
  WHERE (poitem_id=_poitemid)
    AND (poitem_duedate <= (CURRENT_DATE + itemsite_eventfence));

  RETURN _poitemid;

END;
$$;


ALTER FUNCTION public.createpurchasetoquote(pquitemid integer, pitemsourceid integer, pdropship boolean, pprice numeric) OWNER TO admin;

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

CREATE FUNCTION createpurchasetosale(integer, integer, boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2015 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pCoitemId ALIAS FOR $1;
  pItemSourceId ALIAS FOR $2;
  pDropShip ALIAS FOR $3;

BEGIN

  RETURN createPurchaseToSale(pCoitemId, pItemSourceId, pDropShip, NULL, NULL, NULL);

END;
$_$;


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

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

CREATE FUNCTION createpurchasetosale(integer, integer, boolean, numeric) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2015 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pCoitemId ALIAS FOR $1;
  pItemSourceId ALIAS FOR $2;
  pDropShip ALIAS FOR $3;
  pPrice ALIAS FOR $4;

BEGIN

  RETURN createPurchaseToSale(pCoitemId, pItemSourceId, pDropShip, NULL, NULL, pPrice);

END;
$_$;


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

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

CREATE FUNCTION createpurchasetosale(integer, integer, boolean, numeric, date, numeric) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2015 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pCoitemId ALIAS FOR $1;
  pItemSourceId ALIAS FOR $2;
  pDropShip ALIAS FOR $3;
  pQty ALIAS FOR $4;
  pDueDate ALIAS FOR $5;
  pPrice ALIAS FOR $6;

BEGIN

  RETURN createPurchaseToSale(pCoitemId, pItemSourceId, pDropShip, pQty, pDueDate, pPrice, NULL);

END;
$_$;


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

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

CREATE FUNCTION createpurchasetosale(pcoitemid integer, pitemsourceid integer, pdropship boolean, pqty numeric, pduedate date, pprice numeric, ppoheadid integer) RETURNS integer
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2015 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _s RECORD;
  _w RECORD;
  _i RECORD;
  _c RECORD;
  _poheadid INTEGER := -1;
  _poitemid INTEGER := -1;
  _taxtypeid INTEGER := -1;
  _charassid INTEGER := -1;
  _polinenumber INTEGER;
  _ponumber NUMERIC;
  _price NUMERIC;
  _temp INTEGER;

BEGIN

  -- Check for existing poitem for this coitem
  SELECT poitem_id INTO _poitemid
  FROM poitem
  WHERE (poitem_order_id=pCoitemId)
    AND (poitem_order_type='S');
  IF (FOUND) THEN
    RETURN _poitemid;
  END IF;

  SELECT *,
         COALESCE(roundQty(item_fractional, (coitem_qtyord * coitem_qty_invuomratio)), 0.0) AS orderqty
  INTO _s
  FROM coitem JOIN cohead ON (cohead_id = coitem_cohead_id)
              LEFT OUTER JOIN shiptoinfo ON (cohead_shipto_id = shipto_id)
              LEFT OUTER JOIN addr ON (shipto_addr_id = addr_id)
              LEFT OUTER JOIN cntct ON (shipto_cntct_id = cntct_id)
              LEFT OUTER JOIN itemsite ON (coitem_itemsite_id = itemsite_id)
              LEFT OUTER JOIN item ON (item_id = itemsite_item_id)
  WHERE (coitem_id = pCoitemId);
  IF (NOT FOUND) THEN
    RETURN -1;
  END IF;

  SELECT * INTO _w
  FROM itemsite JOIN whsinfo ON (warehous_id = itemsite_warehous_id)
                LEFT OUTER JOIN addr ON (warehous_addr_id = addr_id)
                LEFT OUTER JOIN cntct ON (warehous_cntct_id = cntct_id)
  WHERE (itemsite_id = _s.itemsite_id);

  SELECT * INTO _i
  FROM itemsrc JOIN vendinfo ON (itemsrc_vend_id = vend_id)
               LEFT OUTER JOIN cntct ON (vend_cntct1_id = cntct_id)
               LEFT OUTER JOIN addr ON (vend_addr_id = addr_id)
  WHERE (itemsrc_id = pItemSourceId);
  IF (NOT FOUND) THEN
    RETURN -2;
  END IF;

  -- pPoheadId - NULL=add to existing PO if one exists
  --               -1=must create new PO
  --               >0=must add to existing specified PO
  IF (pDropShip) THEN
    SELECT COALESCE(pohead_id, -1) INTO _temp
    FROM pohead
    WHERE ( (pohead_status = 'U')
      AND (pohead_vend_id = _i.itemsrc_vend_id)
      AND (COALESCE(pohead_shiptoname, '') = COALESCE(_s.cohead_shiptoname, _s.shipto_name, ''))
      AND (COALESCE(pohead_shiptoaddress1, '') = COALESCE(_s.cohead_shiptoaddress1, _s.addr_line1, ''))
      AND (COALESCE(pohead_shiptoaddress2, '') = COALESCE(_s.cohead_shiptoaddress2, _s.addr_line2, ''))
      AND (COALESCE(pohead_shiptoaddress3, '') = COALESCE(_s.cohead_shiptoaddress3, _s.addr_line3, ''))
      AND (COALESCE(pohead_shiptocity, '') = COALESCE(_s.cohead_shiptocity, _s.addr_city, ''))
      AND (COALESCE(pohead_shiptostate, '') = COALESCE(_s.cohead_shiptostate, _s.addr_state, ''))
      AND (COALESCE(pohead_shiptozipcode, '') = COALESCE(_s.cohead_shiptozipcode, _s.addr_postalcode, ''))
      AND (COALESCE(pohead_shiptocountry, '') = COALESCE(_s.cohead_shiptocountry, _s.addr_country, ''))
      AND ((pohead_id=pPoheadId) OR (pPoheadid IS NULL)) );
  ELSE
    SELECT COALESCE(pohead_id, -1) INTO _temp
    FROM pohead
    WHERE ( (pohead_status = 'U')
      AND (pohead_vend_id = _i.itemsrc_vend_id)
      AND (COALESCE(pohead_shiptoaddress1, '') = COALESCE(_w.addr_line1, ''))
      AND (COALESCE(pohead_shiptoaddress2, '') = COALESCE(_w.addr_line2, ''))
      AND (COALESCE(pohead_shiptoaddress3, '') = COALESCE(_w.addr_line3, ''))
      AND (COALESCE(pohead_shiptocity, '') = COALESCE(_w.addr_city, ''))
      AND (COALESCE(pohead_shiptostate, '') = COALESCE(_w.addr_state, ''))
      AND (COALESCE(pohead_shiptozipcode, '') = COALESCE(_w.addr_postalcode, ''))
      AND (COALESCE(pohead_shiptocountry, '') = COALESCE(_w.addr_country, ''))
      AND ((pohead_id=pPoheadId) OR (pPoheadid IS NULL)) );
  END IF;

  IF (FOUND) THEN
    IF (pPoheadId = -1) THEN
      RAISE EXCEPTION 'Problem creating new PO';
    END IF;
    _poheadid := _temp;
    UPDATE pohead
    SET pohead_dropship = pDropShip
    WHERE (pohead_id = _poheadid);
  ELSE
    IF (pPoheadId > 0) THEN
      RAISE EXCEPTION 'Problem adding to existing PO';
    END IF;
    SELECT NEXTVAL('pohead_pohead_id_seq') INTO _poheadid;
    SELECT fetchPoNumber() INTO _ponumber;

    IF (pDropShip) THEN
      INSERT INTO pohead
        ( pohead_id, pohead_number, pohead_status, pohead_dropship,
          pohead_agent_username, pohead_vend_id, pohead_taxzone_id,
          pohead_orderdate, pohead_curr_id, pohead_cohead_id,
          pohead_warehous_id, pohead_shipvia,
          pohead_terms_id, pohead_shipto_cntct_id,
          pohead_shipto_cntct_honorific, pohead_shipto_cntct_first_name,
          pohead_shipto_cntct_middle, pohead_shipto_cntct_last_name,
          pohead_shipto_cntct_suffix, pohead_shipto_cntct_phone,
          pohead_shipto_cntct_title, pohead_shipto_cntct_fax,
          pohead_shipto_cntct_email, pohead_shiptoaddress_id,
          pohead_shiptoname,
          pohead_shiptoaddress1,
          pohead_shiptoaddress2,
          pohead_shiptoaddress3,
          pohead_shiptocity,
          pohead_shiptostate, pohead_shiptozipcode,
          pohead_shiptocountry, pohead_vend_cntct_id,
          pohead_vend_cntct_honorific, pohead_vend_cntct_first_name,
          pohead_vend_cntct_middle, pohead_vend_cntct_last_name,
          pohead_vend_cntct_suffix, pohead_vend_cntct_phone,
          pohead_vend_cntct_title, pohead_vend_cntct_fax,
          pohead_vend_cntct_email, pohead_vendaddress1,
          pohead_vendaddress2, pohead_vendaddress3,
          pohead_vendcity, pohead_vendstate,
          pohead_vendzipcode, pohead_vendcountry, pohead_comments )
      VALUES
        ( _poheadid, _ponumber, 'U', pDropShip,
          getEffectiveXtUser(), _i.itemsrc_vend_id, _i.vend_taxzone_id,
	  CURRENT_DATE, COALESCE(_i.vend_curr_id, basecurrid()), _s.cohead_id,
          COALESCE(_s.cohead_warehous_id, -1), COALESCE(_i.vend_shipvia, TEXT('')),
          COALESCE(_i.vend_terms_id, -1), COALESCE(_s.cohead_shipto_cntct_id, _s.shipto_cntct_id),
          COALESCE(_s.cohead_shipto_cntct_honorific, _s.cntct_honorific), COALESCE(_s.cohead_shipto_cntct_first_name, _s.cntct_first_name),
          COALESCE(_s.cohead_shipto_cntct_middle, _s.cntct_middle), COALESCE(_s.cohead_shipto_cntct_last_name, _s.cntct_last_name),
          COALESCE(_s.cohead_shipto_cntct_suffix, _s.cntct_suffix), COALESCE(_s.cohead_shipto_cntct_phone, _s.cntct_phone),
          COALESCE(_s.cohead_shipto_cntct_title, _s.cntct_title), COALESCE(_s.cohead_shipto_cntct_fax, _s.cntct_fax),
          COALESCE(_s.cohead_shipto_cntct_email, _s.cntct_email), COALESCE(_s.shipto_addr_id, _s.addr_id),
          COALESCE(_s.cohead_shiptoname, _s.shipto_name, ''),
          COALESCE(_s.cohead_shiptoaddress1, _s.addr_line1, ''),
          COALESCE(_s.cohead_shiptoaddress2, _s.addr_line2, ''),
          COALESCE(_s.cohead_shiptoaddress3, _s.addr_line3, ''),
          COALESCE(_s.cohead_shiptocity, _s.addr_city, ''),
          COALESCE(_s.cohead_shiptostate, _s.addr_state, ''), COALESCE(_s.cohead_shiptozipcode, _s.addr_postalcode, ''),
          COALESCE(_s.cohead_shiptocountry, _s.addr_country, ''), _i.cntct_id,
          COALESCE(_i.cntct_honorific, TEXT('')), COALESCE(_i.cntct_first_name, TEXT('')),
          COALESCE(_i.cntct_middle, TEXT('')), COALESCE(_i.cntct_last_name, TEXT('')),
          COALESCE(_i.cntct_suffix, TEXT('')), COALESCE(_i.cntct_phone, TEXT('')),
          COALESCE(_i.cntct_title, TEXT('')), COALESCE(_i.cntct_fax, TEXT('')),
          COALESCE(_i.cntct_email, TEXT('')), COALESCE(_i.addr_line1, TEXT('')),
          COALESCE(_i.addr_line2, TEXT('')), COALESCE(_i.addr_line3, TEXT('')),
          COALESCE(_i.addr_city, TEXT('')), COALESCE(_i.addr_state, TEXT('')),
          COALESCE(_i.addr_postalcode, TEXT('')), COALESCE(_i.addr_country, TEXT('')), COALESCE(_s.cohead_shipcomments, TEXT('')) );
    ELSE
      INSERT INTO pohead
        ( pohead_id, pohead_number, pohead_status, pohead_dropship,
          pohead_agent_username, pohead_vend_id, pohead_taxzone_id,
          pohead_orderdate, pohead_curr_id, pohead_cohead_id,
          pohead_warehous_id, pohead_shipvia,
          pohead_terms_id, pohead_shipto_cntct_id,
          pohead_shipto_cntct_honorific, pohead_shipto_cntct_first_name,
          pohead_shipto_cntct_middle, pohead_shipto_cntct_last_name,
          pohead_shipto_cntct_suffix, pohead_shipto_cntct_phone,
          pohead_shipto_cntct_title, pohead_shipto_cntct_fax,
          pohead_shipto_cntct_email, pohead_shiptoaddress_id,
          pohead_shiptoaddress1,
          pohead_shiptoaddress2,
          pohead_shiptoaddress3,
          pohead_shiptocity,
          pohead_shiptostate, pohead_shiptozipcode,
          pohead_shiptocountry, pohead_vend_cntct_id,
          pohead_vend_cntct_honorific, pohead_vend_cntct_first_name,
          pohead_vend_cntct_middle, pohead_vend_cntct_last_name,
          pohead_vend_cntct_suffix, pohead_vend_cntct_phone,
          pohead_vend_cntct_title, pohead_vend_cntct_fax,
          pohead_vend_cntct_email, pohead_vendaddress1,
          pohead_vendaddress2, pohead_vendaddress3,
          pohead_vendcity, pohead_vendstate,
          pohead_vendzipcode, pohead_vendcountry )
      VALUES
        ( _poheadid, _ponumber, 'U', pDropShip,
          getEffectiveXtUser(), _i.itemsrc_vend_id, _i.vend_taxzone_id,
	  CURRENT_DATE, COALESCE(_i.vend_curr_id, basecurrid()), _s.cohead_id,
          COALESCE(_s.cohead_warehous_id, -1), COALESCE(_i.vend_shipvia, TEXT('')),
          COALESCE(_i.vend_terms_id, -1), _w.cntct_id,
          _w.cntct_honorific, _w.cntct_first_name,
          _w.cntct_middle, _w.cntct_last_name,
          _w.cntct_suffix, _w.cntct_phone,
          _w.cntct_title, _w.cntct_fax,
          _w.cntct_email, _w.addr_id,
          COALESCE(_w.addr_line1, ''),
          COALESCE(_w.addr_line2, ''),
          COALESCE(_w.addr_line3, ''),
          COALESCE(_w.addr_city, ''),
          COALESCE(_w.addr_state, ''), COALESCE(_w.addr_postalcode, ''),
          COALESCE(_w.addr_country, ''), _i.cntct_id,
          COALESCE(_i.cntct_honorific, TEXT('')), COALESCE(_i.cntct_first_name, TEXT('')),
          COALESCE(_i.cntct_middle, TEXT('')), COALESCE(_i.cntct_last_name, TEXT('')),
          COALESCE(_i.cntct_suffix, TEXT('')), COALESCE(_i.cntct_phone, TEXT('')),
          COALESCE(_i.cntct_title, TEXT('')), COALESCE(_i.cntct_fax, TEXT('')),
          COALESCE(_i.cntct_email, TEXT('')), COALESCE(_i.addr_line1, TEXT('')),
          COALESCE(_i.addr_line2, TEXT('')), COALESCE(_i.addr_line3, TEXT('')),
          COALESCE(_i.addr_city, TEXT('')), COALESCE(_i.addr_state, TEXT('')),
          COALESCE(_i.addr_postalcode, TEXT('')), COALESCE(_i.addr_country, TEXT('')) );
    END IF;
  END IF;

  -- Copy characteristics from the cohead to the pohead
  -- while avoiding duplicates
  FOR _c IN
    SELECT charass.*
      FROM charass
      JOIN char    ON char_id = charass_char_id
      JOIN charuse ON char_id = charuse_char_id AND charuse_target_type = 'PO'
     WHERE charass_target_type = 'SO'
       AND charass_target_id   = _s.cohead_id
  LOOP
    SELECT charass_id INTO _charassid
    FROM charass
    WHERE ( (charass_target_type='PO')
      AND   (charass_target_id=_poheadid)
      AND   (charass_char_id=_c.charass_char_id)
      AND   (charass_value=_c.charass_value) );
    IF (NOT FOUND) THEN
      INSERT INTO charass
        ( charass_target_type, charass_target_id, charass_char_id,
          charass_value, charass_default, charass_price )
      VALUES
        ( 'PO', _poheadid, _c.charass_char_id,
          _c.charass_value, _c.charass_default, _c.charass_price );
    END IF;
  END LOOP;

  SELECT NEXTVAL('poitem_poitem_id_seq') INTO _poitemid;

  SELECT (COALESCE(MAX(poitem_linenumber), 0) + 1) INTO _polinenumber
  FROM poitem
  WHERE (poitem_pohead_id = _poheadid);

  SELECT COALESCE(itemtax_taxtype_id, -1) INTO _taxtypeid
  FROM itemtax
  WHERE (itemtax_item_id = _i.itemsrc_item_id);

  IF (pPrice IS NULL) THEN
    SELECT itemsrcPrice(pItemSourceId,
                        COALESCE(_s.cohead_warehous_id, -1),
                        pDropShip,
                        (COALESCE(pQty, _s.orderqty) / COALESCE(_i.itemsrc_invvendoruomratio, 1.00)),
                        COALESCE(_i.vend_curr_id, baseCurrId()),
                        CURRENT_DATE) INTO _price;
  ELSE
    _price := pPrice;
  END IF;

  IF (pDropShip) THEN
    INSERT INTO poitem
      ( poitem_id, poitem_status, poitem_pohead_id, poitem_linenumber,
        poitem_duedate, poitem_itemsite_id,
        poitem_vend_item_descrip, poitem_vend_uom,
        poitem_invvenduomratio, poitem_qty_ordered,
        poitem_unitprice, poitem_vend_item_number,
        poitem_itemsrc_id, poitem_order_id, poitem_order_type, poitem_prj_id, poitem_stdcost,
        poitem_manuf_name, poitem_manuf_item_number,
        poitem_manuf_item_descrip, poitem_taxtype_id, poitem_comments )
    VALUES
      ( _poitemid, 'U', _poheadid, _polinenumber,
        COALESCE(pDueDate, _s.coitem_scheddate), _s.coitem_itemsite_id,
        COALESCE(_i.itemsrc_vend_item_descrip, TEXT('')), COALESCE(_i.itemsrc_vend_uom, TEXT('')),
        COALESCE(_i.itemsrc_invvendoruomratio, 1.00), (COALESCE(pQty, _s.orderqty) / COALESCE(_i.itemsrc_invvendoruomratio, 1.00)),
        _price, COALESCE(_i.itemsrc_vend_item_number, TEXT('')),
        pItemSourceId, pCoitemId, 'S', _s.cohead_prj_id, stdcost(_i.itemsrc_item_id),
        COALESCE(_i.itemsrc_manuf_name, TEXT('')), COALESCE(_i.itemsrc_manuf_item_number, TEXT('')),
        COALESCE(_i.itemsrc_manuf_item_descrip, TEXT('')), _taxtypeid,
        COALESCE(_s.coitem_memo, TEXT('')));
  ELSE
    INSERT INTO poitem
      ( poitem_id, poitem_status, poitem_pohead_id, poitem_linenumber,
        poitem_duedate, poitem_itemsite_id,
        poitem_vend_item_descrip, poitem_vend_uom,
        poitem_invvenduomratio, poitem_qty_ordered,
        poitem_unitprice, poitem_vend_item_number,
        poitem_itemsrc_id, poitem_order_id, poitem_order_type, poitem_prj_id, poitem_stdcost,
        poitem_manuf_name, poitem_manuf_item_number,
        poitem_manuf_item_descrip, poitem_taxtype_id, poitem_comments )
    VALUES
      ( _poitemid, 'U', _poheadid, _polinenumber,
        COALESCE(pDueDate, _s.coitem_scheddate), _s.coitem_itemsite_id,
        COALESCE(_i.itemsrc_vend_item_descrip, TEXT('')), COALESCE(_i.itemsrc_vend_uom, TEXT('')),
        COALESCE(_i.itemsrc_invvendoruomratio, 1.00), (COALESCE(pQty, _s.orderqty) / COALESCE(_i.itemsrc_invvendoruomratio, 1.00)),
        _price, COALESCE(_i.itemsrc_vend_item_number, TEXT('')),
        pItemSourceId, pCoitemId, 'S', _s.cohead_prj_id, stdcost(_i.itemsrc_item_id),
        COALESCE(_i.itemsrc_manuf_name, TEXT('')), COALESCE(_i.itemsrc_manuf_item_number, TEXT('')),
        COALESCE(_i.itemsrc_manuf_item_descrip, TEXT('')), _taxtypeid,
        COALESCE(_s.coitem_memo, TEXT('')));
  END IF;

  -- Copy characteristics from the coitem to the poitem
  INSERT INTO charass
    ( charass_target_type, charass_target_id, charass_char_id,
      charass_value, charass_default, charass_price )
  SELECT 'PI', _poitemid, charass_char_id,
         charass_value, charass_default, charass_price
    FROM charass
    JOIN char    ON char_id = charass_char_id
    JOIN charuse ON char_id = charuse_char_id AND charuse_target_type = 'PI'
   WHERE charass_target_type = 'SI'
     AND charass_target_id   = pCoitemId;

  UPDATE coitem
  SET coitem_order_type = 'P',
      coitem_order_id = _poitemid
  WHERE ( coitem_id = pCoitemId );

  -- Generate the PoItemCreatedBySo event notice
  PERFORM postEvent('PoItemCreatedBySo', 'P', poitem_id,
                    itemsite_warehous_id,
                    (pohead_number || '-'|| poitem_linenumber || ': ' || item_number),
                    NULL, NULL, NULL, NULL)
  FROM poitem JOIN pohead ON (pohead_id=poitem_pohead_id)
              JOIN itemsite ON (itemsite_id=poitem_itemsite_id)
              JOIN item ON (item_id=itemsite_item_id)
  WHERE (poitem_id=_poitemid)
    AND (poitem_duedate <= (CURRENT_DATE + itemsite_eventfence));

  RETURN _poitemid;

END;
$$;


ALTER FUNCTION public.createpurchasetosale(pcoitemid integer, pitemsourceid integer, pdropship boolean, pqty numeric, pduedate date, pprice numeric, ppoheadid integer) OWNER TO admin;

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

CREATE FUNCTION createrecurringinvoices() RETURNS integer
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  RAISE WARNING 'createRecurringInvoices() has been deprecated; use createRecurringItems(NULL, ''I'') instead.';

  RETURN createRecurringItems(NULL, 'I');
END;
$$;


ALTER FUNCTION public.createrecurringinvoices() OWNER TO admin;

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

CREATE FUNCTION createrecurringitems(integer, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pParentid  ALIAS FOR $1;      -- if NULL then all items with the given pType
  pType      TEXT := UPPER($2); -- if NULL then all types
                                -- if both are null then all items of all types
  _copystmt  TEXT;
  _count     INTEGER := 0;
  _countstmt TEXT;
  _existcnt  INTEGER;
  _id        INTEGER;
  _interval  TEXT;
  _last      TIMESTAMP WITH TIME ZONE;
  _loopcount INTEGER := 1;
  _maxstmt   TEXT;
  _maxdate   TIMESTAMP WITH TIME ZONE := endOfTime();
  _result    INTEGER := 0;
  _next      TIMESTAMP WITH TIME ZONE;
  _r         RECORD;
  _rt        RECORD;
  _tmp       INTEGER;

BEGIN
  RAISE DEBUG 'createRecurringItems(%, %) entered', pParentid, pType;

  FOR _r IN SELECT *
              FROM recur
             WHERE ((COALESCE(recur_end, endOfTime()) >= CURRENT_TIMESTAMP)
                AND (pParentid IS NULL OR recur_parent_id=pParentid)
                AND (pType IS NULL OR UPPER(recur_parent_type)=UPPER(pType))) LOOP

    RAISE DEBUG 'createRecurringItems looking at recur %, %',
                _r.recur_id, _r.recur_parent_type;
    _r.recur_max := COALESCE(_r.recur_max,
                             CAST(fetchMetricValue('RecurringInvoiceBuffer') AS INTEGER),
                             1);
    _interval := CASE _r.recur_period WHEN 'Y' THEN ' year'
                                      WHEN 'M' THEN ' month'
                                      WHEN 'W' THEN ' week'
                                      WHEN 'D' THEN ' day'
                                      WHEN 'H' THEN ' hour'
                                      WHEN 'm' THEN ' minute'
                                      ELSE NULL
                 END;

    IF (_interval IS NULL OR COALESCE(_r.recur_freq, 0) <= 0) THEN
      RAISE EXCEPTION 'Unknown recurrence frequency % % ON % %',
                      _r.recur_freq,        _r.recur_period,
                      _r.recur_parent_type, _r.recur_parent_id;
    END IF;

    SELECT * INTO _rt FROM recurtype WHERE (UPPER(recurtype_type)=UPPER(pType));
    GET DIAGNOSTICS _count = ROW_COUNT;
    IF (_count <= 0) THEN
      RETURN -10;
    END IF;

    -- if the recurrence type has a max lookahead window, use it
    IF (_r.recur_parent_type = 'I') THEN
      _maxdate := CURRENT_TIMESTAMP + CAST(fetchMetricText('RecurringInvoiceBuffer') || ' days' AS INTERVAL);
    END IF;
    IF (_r.recur_parent_type = 'V') THEN
      _maxdate := CURRENT_TIMESTAMP + CAST(fetchMetricText('RecurringVoucherBuffer') || ' days' AS INTERVAL);
    END IF;
    IF (_maxdate > _r.recur_end) THEN   -- if recur_end is null, _maxdate is ok
      _maxdate = _r.recur_end;
    END IF;

    -- build statements dynamically from the recurtype table because packages
    -- might also require recurring items. this way the algorithm is fixed
    -- and the details are data-driven
    _countstmt := 'SELECT COUNT(*) FROM [fulltable]' 
               || ' WHERE (($1=[table]_recurring_[table]_id)'
               || ' AND NOT([done]) '
               || ' AND ([limit]));';
    _countstmt := REPLACE(_countstmt, '[fulltable]', _rt.recurtype_table);
    _countstmt := REPLACE(_countstmt, '[table]',
                          REGEXP_REPLACE(_rt.recurtype_table, E'.*\\.', ''));
    _countstmt := REPLACE(_countstmt, '[done]',  _rt.recurtype_donecheck);
    _countstmt := REPLACE(_countstmt, '[limit]',
                          COALESCE(_rt.recurtype_limit, 'TRUE'));

    _maxstmt := 'SELECT MAX([schedcol]) FROM [fulltable]'
               || ' WHERE (($1=[table]_recurring_[table]_id)'
               || '    AND ([limit]));';
    _maxstmt := REPLACE(_maxstmt, '[schedcol]', _rt.recurtype_schedcol);
    _maxstmt := REPLACE(_maxstmt, '[fulltable]',_rt.recurtype_table);
    _maxstmt := REPLACE(_maxstmt, '[table]',
                          REGEXP_REPLACE(_rt.recurtype_table, E'.*\\.', ''));
    _maxstmt := REPLACE(_maxstmt, '[limit]', COALESCE(_rt.recurtype_limit,
                                                     'TRUE'));

    _copystmt := 'SELECT [copy]($1, [datetime] [more]);';
    _copystmt := REPLACE(_copystmt, '[copy]', _rt.recurtype_copyfunc);
    _copystmt := REPLACE(_copystmt, '[datetime]',
                         CASE WHEN UPPER(_rt.recurtype_copyargs[2])='DATE' THEN
                                    'CAST(''$2'' AS DATE)'
                              ELSE '''$2''' END);
    -- 8.4+:
    -- _copystmt := REPLACE(_copystmt, '[more]',
    --                      REPEAT(', NULL',
    --                             array_length(_rt.recurtype_copyargs) - 2));
    _tmp := CAST(REPLACE(REGEXP_REPLACE(array_dims(_rt.recurtype_copyargs),
                                        '.*:', ''), ']', '') AS INTEGER);
    _copystmt := REPLACE(_copystmt, '[more]', REPEAT(', NULL', _tmp - 2));

    EXECUTE REPLACE(_countstmt, '$1', _r.recur_parent_id::TEXT) INTO _existcnt;
    EXECUTE REPLACE(_maxstmt,   '$1', _r.recur_parent_id::TEXT) INTO _last;
    RAISE DEBUG E'% got %, % got %', _countstmt, _existcnt, _maxstmt, _last;

    _next := _last;
    _loopcount := 1;
    WHILE (_existcnt < _r.recur_max AND _next < _maxdate) LOOP
      _next := _last +
               CAST(_r.recur_freq * _loopcount || _interval AS INTERVAL);
      RAISE DEBUG 'createrecurringitems looping, existcnt = %, max = %, is % between % and %?',
                  _existcnt, _r.recur_max, _next, _r.recur_start, _r.recur_end;

      IF (_next BETWEEN _r.recur_start AND _maxdate) THEN
        RAISE DEBUG 'createrecurringitems executing % with % and %',
                    _copystmt, _r.recur_parent_id, _next;
        -- 8.4+: EXECUTE _copystmt INTO _id USING _r.recur_parent_id, _next;
        EXECUTE REPLACE(REPLACE(_copystmt, '$1', _r.recur_parent_id::TEXT),
                                           '$2', _next::TEXT) INTO _id;
        RAISE DEBUG 'Copying for % returned %', _next, _id;
        _result   := _result   + 1;
        _existcnt := _existcnt + 1;
      END IF;
      _loopcount := _loopcount + 1;
    END LOOP;
  END LOOP;

  RETURN _result;
END;
$_$;


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

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

CREATE FUNCTION createtodoitem(integer, text, text, text, integer, integer, integer, date, date, character, date, date, integer, text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$  
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  RETURN createTodoItem($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, NULL);
END;
$_$;


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

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

CREATE FUNCTION createtodoitem(integer, text, text, text, integer, integer, integer, date, date, character, date, date, integer, text, text, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  ptodoid     ALIAS FOR  $1;
  pusername   ALIAS FOR  $2;
  pname       ALIAS FOR  $3;
  pdesc       ALIAS FOR  $4;
  pincdtid    ALIAS FOR  $5;
  pcrmacctid  ALIAS FOR  $6;
  pOpheadid   ALIAS FOR  $7;
  pstarted    ALIAS FOR  $8;
  pdue        ALIAS FOR  $9;
  pstatus     ALIAS FOR $10;
  passigned   ALIAS FOR $11;
  pcompleted  ALIAS FOR $12;
  ppriority   ALIAS FOR $13;
  pnotes      ALIAS FOR $14;
  powner      ALIAS FOR $15;
  pcntctid    ALIAS FOR $16;

  _todoid     INTEGER;
  _priority   INTEGER         := ppriority;
  _status     CHARACTER(1)    := pstatus;
  _incdtid    INTEGER         := pincdtid;
  _crmacctid  INTEGER         := pcrmacctid;
  _opheadid   INTEGER         := pOpheadid;
  _assigned   DATE            := passigned;
  _result     INTEGER;

BEGIN
  IF (pusername IS NULL OR pusername = '') THEN
    RETURN -1;
  END IF;

  IF (pname IS NULL OR pname = '') THEN
    RETURN -2;
  END IF;

  IF (pdue IS NULL) THEN
    RETURN -3;
  END IF;

  IF (pcompleted IS NOT NULL) THEN
    _status := 'C';
  ELSIF (pstatus IS NULL AND pstarted IS NOT NULL) THEN
    _status := 'I';
  ELSIF (pstatus IS NULL) THEN
    _status := 'N';
  END IF;

  IF (_incdtid <= 0) THEN
    _incdtid := NULL;
  END IF;

  IF (_crmacctid <= 0) THEN
    _crmacctid := NULL;
  END IF;

  IF (_opheadid <= 0) THEN
    _opheadid := NULL;
  END IF;

  IF (_priority <= 0) THEN
    _priority := NULL;
  END IF;

  IF (_assigned IS NULL) THEN
    _assigned := CURRENT_DATE;
  END IF;

  IF (ptodoid IS NULL) THEN
    SELECT NEXTVAL('todoitem_todoitem_id_seq') INTO _todoid;
  ELSE
    _todoid := ptodoid;
  END IF;

  INSERT INTO todoitem ( todoitem_id, todoitem_username, todoitem_name,
                         todoitem_description, todoitem_incdt_id,
                         todoitem_creator_username, todoitem_status,
                         todoitem_active, todoitem_start_date,
                         todoitem_due_date, todoitem_assigned_date,
                         todoitem_completed_date, todoitem_priority_id,
                         todoitem_notes, todoitem_crmacct_id,
                         todoitem_ophead_id, todoitem_owner_username,
                         todoitem_cntct_id
              ) VALUES ( _todoid, pusername, pname,
                         pdesc, _incdtid,
                         getEffectiveXtUser(), _status,
                         TRUE, pstarted,
                         pdue, _assigned,
                         pcompleted, _priority, pnotes, _crmacctid, _opheadid, powner,
                         pcntctid );

  RETURN _todoid;
END;
$_$;


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

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

CREATE FUNCTION createurl(text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
declare
  pTitle ALIAS FOR $1;
  pUrl ALIAS FOR $2;
  _id integer;
begin
  _id := nextval('urlinfo_url_id_seq');
  insert into urlinfo (url_id, url_title, url_url) values (_id, pTitle, pUrl);
  return _id;
end;
$_$;


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

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

CREATE FUNCTION createuser(pusername text, pcreateusers boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  IF (pCreateUsers) THEN
    EXECUTE 'CREATE USER "' || pUsername || '" CREATEROLE   IN GROUP xtrole;';
  ELSE
    EXECUTE 'CREATE USER "' || pUsername || '" NOCREATEROLE IN GROUP xtrole;';
  END IF;
  RETURN 1;
END;
$$;


ALTER FUNCTION public.createuser(pusername text, pcreateusers boolean) OWNER TO admin;

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

CREATE FUNCTION createwo(integer, integer, numeric, integer, date, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pWoNumber ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pQtyOrdered ALIAS FOR $3;
  pLeadTime ALIAS FOR $4;
  pDueDate ALIAS FOR $5;
  pProductionNotes ALIAS FOR $6;

BEGIN
  RETURN createWo( pWoNumber, pItemsiteid, 1, pQtyOrdered,
                   (pDueDate - pLeadTime), pDueDate,
                   pProductionNotes, NULL, NULL, -1 ); 
END;
$_$;


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

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

CREATE FUNCTION createwo(integer, integer, numeric, date, date, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pWoNumber ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pQtyOrdered ALIAS FOR $3;
  pStartDate ALIAS FOR $4;
  pDueDate ALIAS FOR $5;
  pProductionNotes ALIAS FOR $6;

BEGIN
  RETURN createWo( pWoNumber, pItemsiteid, 1, pQtyOrdered,
                   pStartDate, pDueDate, pProductionNotes, NULL, NULL, -1);
END;
$_$;


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

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

CREATE FUNCTION createwo(integer, integer, numeric, integer, date, text, character, 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
  pWoNumber ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pQtyOrdered ALIAS FOR $3;
  pLeadTime ALIAS FOR $4;
  pDueDate ALIAS FOR $5;
  pProductionNotes ALIAS FOR $6;
  pParentType ALIAS FOR $7;
  pParentId ALIAS FOR $8;

BEGIN
  RETURN createWo( pWoNumber, pItemsiteid, 1, pQtyOrdered,
                   (pDueDate - pLeadTime), pDueDate,
                   pProductionNotes, pParentType, pParentId, -1 ); 
END;
$_$;


ALTER FUNCTION public.createwo(integer, integer, numeric, integer, date, text, character, integer) OWNER TO admin;

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

CREATE FUNCTION createwo(integer, integer, integer, numeric, integer, date, text, character, 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
  pWoNumber ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pPriority ALIAS FOR $3;
  pQtyOrdered ALIAS FOR $4;
  pLeadTime ALIAS FOR $5;
  pDueDate ALIAS FOR $6;
  pProductionNotes ALIAS FOR $7;
  pParentType ALIAS FOR $8;
  pParentId ALIAS FOR $9;

BEGIN
  RETURN createWo(pWoNumber, pItemsiteid, pPriority, pQtyOrdered,
                  (pDueDate - pLeadTime), pDueDate, pProductionNotes,
                  pParentType, pParentId, -1);
END;
$_$;


ALTER FUNCTION public.createwo(integer, integer, integer, numeric, integer, date, text, character, integer) OWNER TO admin;

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

CREATE FUNCTION createwo(integer, integer, integer, numeric, integer, date, text, character, 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
  pWoNumber ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pPriority ALIAS FOR $3;
  pQtyOrdered ALIAS FOR $4;
  pLeadTime ALIAS FOR $5;
  pDueDate ALIAS FOR $6;
  pProductionNotes ALIAS FOR $7;
  pParentType ALIAS FOR $8;
  pParentId ALIAS FOR $9;
  pProjectId ALIAS FOR $10;

BEGIN
  RETURN createWo(pWoNumber, pItemsiteid, pPriority, pQtyOrdered,
                  (pDueDate - pLeadTime), pDueDate, pProductionNotes,
                  pParentType, pParentId, pProjectId);
END;
$_$;


ALTER FUNCTION public.createwo(integer, integer, integer, numeric, integer, date, text, character, integer, integer) OWNER TO admin;

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

CREATE FUNCTION createwo(integer, integer, integer, numeric, date, date, text, character, 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
  pWoNumber ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pPriority ALIAS FOR $3;
  pQtyOrdered ALIAS FOR $4;
  pStartDate ALIAS FOR $5;
  pDueDate ALIAS FOR $6;
  pProductionNotes ALIAS FOR $7;
  pParentType ALIAS FOR $8;
  pParentId ALIAS FOR $9;
  pProjectId ALIAS FOR $10;
  _woid INTEGER;
  _result INTEGER;
  _parentType char(1);
  _bomrevid INTEGER;
  _boorevid INTEGER;

BEGIN

  SELECT getActiveRevId('BOM',itemsite_item_id) INTO _bomrevid
  FROM itemsite
  WHERE (itemsite_id=pItemsiteid);

  SELECT getActiveRevId('BOO',itemsite_item_id) INTO _boorevid
  FROM itemsite
  WHERE (itemsite_id=pItemsiteid);
  
  RETURN createWo(pWoNumber, pItemsiteid, pPriority, pQtyOrdered,
                  pStartDate, pDueDate, pProductionNotes,
                  pParentType, pParentId, pProjectId, _bomrevid, _boorevid, NULL);

END;
$_$;


ALTER FUNCTION public.createwo(integer, integer, integer, numeric, date, date, text, character, integer, integer) OWNER TO admin;

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

CREATE FUNCTION createwo(integer, integer, integer, numeric, integer, date, text, character, integer, integer, 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
  pWoNumber ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pPriority ALIAS FOR $3;
  pQtyOrdered ALIAS FOR $4;
  pLeadTime ALIAS FOR $5;
  pDueDate ALIAS FOR $6;
  pProductionNotes ALIAS FOR $7;
  pParentType ALIAS FOR $8;
  pParentId ALIAS FOR $9;
  pProjectId ALIAS FOR $10;
  pBomRevId ALIAS FOR $11;
  pBooRevId ALIAS FOR $12;

BEGIN
  RETURN createWo(pWoNumber, pItemsiteid, pPriority, pQtyOrdered,
                  (pDueDate - pLeadTime), pDueDate, pProductionNotes,
                  pParentType, pParentId, pProjectId, pBomRevId, pBooRevId, NULL);
END;
$_$;


ALTER FUNCTION public.createwo(integer, integer, integer, numeric, integer, date, text, character, integer, integer, integer, integer) OWNER TO admin;

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

CREATE FUNCTION createwo(integer, integer, integer, numeric, date, date, text, character, integer, integer, 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
  pWoNumber ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pPriority ALIAS FOR $3;
  pQtyOrdered ALIAS FOR $4;
  pStartDate ALIAS FOR $5;
  pDueDate ALIAS FOR $6;
  pProductionNotes ALIAS FOR $7;
  pParentType ALIAS FOR $8;
  pParentId ALIAS FOR $9;
  pProjectId ALIAS FOR $10;
  pBomRevId ALIAS FOR $11;
  pBooRevId ALIAS FOR $12;
BEGIN
  RETURN createWo(pWoNumber, pItemsiteid, pPriority, pQtyOrdered,
                  pStartDate, pDueDate, pProductionNotes,
                  pParentType, pParentId, pProjectId, pBomRevId, pBooRevId, NULL);
END;
$_$;


ALTER FUNCTION public.createwo(integer, integer, integer, numeric, date, date, text, character, integer, integer, integer, integer) OWNER TO admin;

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

CREATE FUNCTION createwo(integer, integer, integer, numeric, date, date, text, character, integer, integer, integer, integer, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pWoNumber ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pPriority ALIAS FOR $3;
  pQtyOrdered ALIAS FOR $4;
  pStartDate ALIAS FOR $5;
  pDueDate ALIAS FOR $6;
  pProductionNotes ALIAS FOR $7;
  pParentType ALIAS FOR $8;
  pParentId ALIAS FOR $9;
  pProjectId ALIAS FOR $10;
  pBomRevId ALIAS FOR $11;
  pBooRevId ALIAS FOR $12;
  pCosMethod ALIAS FOR $13;
  _startDate DATE;
  _woid INTEGER;
  _result INTEGER;
  _parentType char(1);
  _parentId INTEGER;
  _cosmethod char(1);
  _itemsite RECORD;
  
BEGIN
  
  _parentType := COALESCE(pParentType, ' ');
  _parentId   := COALESCE(pParentId, -1);

  SELECT * INTO _itemsite FROM itemsite WHERE itemsite_id = pItemsiteid;

--  Check to make sure the itemsite specified is supplied at itemsite
  IF (NOT _itemsite.itemsite_wosupply) THEN
    RETURN -1;
  END IF;

--  Check to make sure if this is a job item that it is tied to a sales order
--  Or if it is just an avarage costed item
  IF (pCosMethod IN ('D', 'P')) THEN
    _cosmethod := pCosMethod;
  ELSE
    IF (_itemsite.itemsite_costmethod = 'J') THEN
      IF (_parentType = ' ' OR _parentId = -1) THEN
        RAISE EXCEPTION 'Work Orders for Item Sites that are Job cost must have a parent order.';
      ELSE
        SELECT COALESCE(itemsite_cosdefault,fetchmetrictext('JobItemCosDefault'),'D') INTO _cosmethod FROM itemsite WHERE itemsite_id=pItemsiteid;
      END IF;
    ELSIF (_itemsite.itemsite_costmethod = 'A') THEN
      _cosmethod := COALESCE(_itemsite.itemsite_cosdefault,fetchmetrictext('JobItemCosDefault'),'D');
    END IF;
  END IF;

--  Check to see if the site calendar metric is set, and if so adjust the start date if necessary
  IF (fetchmetricbool('UseSiteCalendar')) THEN
    _startDate := calculatenextworkingdate(_itemsite.itemsite_warehous_id, pStartDate, 0);
    IF (_startDate != pStartDate) THEN
      _startDate := calculatenextworkingdate(_itemsite.itemsite_warehous_id, pDueDate, -_itemsite.itemsite_leadtime);
    END IF;
  ELSE
    _startDate := pStartDate;
  END IF;
  
--  Grab the next wo_id
  SELECT NEXTVAL('wo_wo_id_seq') INTO _woid;

--  Create the W/O
  INSERT INTO wo
  ( wo_id, wo_number, wo_subnumber, wo_itemsite_id,
    wo_priority, wo_ordtype, wo_ordid,
    wo_status, wo_startdate, wo_duedate,
    wo_qtyord, wo_qtyrcv, wo_prodnotes, wo_prj_id,
    wo_bom_rev_id, wo_boo_rev_id, wo_cosmethod )
  SELECT _woid, pWoNumber, nextWoSubnumber(pWoNumber), itemsite_id,
         pPriority, _parentType, pParentId,
         'O', _startDate, pDueDate,
         roundQty(item_fractional, pQtyOrdered), 0, pProductionNotes, pProjectId, 
         pBomRevid, pBooRevid, _cosmethod
  FROM itemsite, item
  WHERE ((itemsite_item_id=item_id)
   AND (itemsite_id=pItemsiteid));

--  Explode the newly created W/O according to metrics
  IF ( ( SELECT (metric_value='t')
         FROM metric
         WHERE (metric_name='AutoExplodeWO') ) ) THEN
    SELECT explodeWo( _woid, ( SELECT (metric_value = 'M')
                               FROM metric
                               WHERE (metric_name='WOExplosionLevel') ) ) INTO _result;
  ELSE
    _result := _woid;
  END IF;

  RETURN _result;

END;
$_$;


ALTER FUNCTION public.createwo(integer, integer, integer, numeric, date, date, text, character, integer, integer, integer, integer, text) OWNER TO admin;

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

CREATE FUNCTION createwomaterial(integer, integer, character, numeric, numeric) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
  pWoid ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pIssueMethod ALIAS FOR $3;
  pQtyPer ALIAS FOR $4;
  pScrap ALIAS FOR $5;
  _result INTEGER;
BEGIN
  SELECT createWoMaterial(pWoid, pItemsiteid, pIssueMethod, item_inv_uom_id, pQtyPer, pScrap)
    INTO _result
    FROM itemsite JOIN item ON (itemsite_item_id=item_id)
   WHERE(itemsite_id=pItemsiteid);
  RETURN _result;
END;
$_$;


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

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

CREATE FUNCTION createwomaterial(integer, integer, character, integer, numeric, numeric) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
  pWoid ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pIssueMethod ALIAS FOR $3;
  pUomId ALIAS FOR $4;
  pQtyPer ALIAS FOR $5;
  pScrap ALIAS FOR $6;
  _womatlid INTEGER;

BEGIN

  SELECT createWoMaterial(pWoid,pItemsiteid,pIssueMethod,pUomId,pQtyPer,pScrap,-1, NULL, NULL) INTO _womatlid;

  RETURN _womatlid;
END;
$_$;


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

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

CREATE FUNCTION createwomaterial(integer, integer, character, numeric, numeric, numeric) 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
  pWoid ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pIssueMethod ALIAS FOR $3;
  pQtyFxd ALIAS FOR $4;
  pQtyPer ALIAS FOR $5;
  pScrap ALIAS FOR $6;
  _result INTEGER;
BEGIN
  SELECT createWoMaterial(pWoid, pItemsiteid, pIssueMethod, item_inv_uom_id, pQtyFxd, pQtyPer, pScrap)
    INTO _result
    FROM itemsite JOIN item ON (itemsite_item_id=item_id)
   WHERE(itemsite_id=pItemsiteid);
  RETURN _result;
END;
$_$;


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

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

CREATE FUNCTION createwomaterial(integer, integer, character, integer, numeric, numeric, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
  pWoid ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pIssueMethod ALIAS FOR $3;
  pUomId ALIAS FOR $4;
  pQtyPer ALIAS FOR $5;
  pScrap ALIAS FOR $6;
  pBomitemId ALIAS FOR $7;
  _womatlid INTEGER;

BEGIN

  _womatlid := (SELECT NEXTVAL('womatl_womatl_id_seq'));

  INSERT INTO womatl
  ( womatl_id, womatl_wo_id, womatl_itemsite_id,
    womatl_issuemethod, womatl_uom_id, womatl_qtyper, womatl_scrap,
    womatl_qtyreq, womatl_qtyiss, womatl_qtywipscrap,
    womatl_wooper_id, womatl_bomitem_id, womatl_duedate )
  SELECT _womatlid, wo_id, pItemsiteid,
         pIssueMethod, pUomId, pQtyPer, pScrap,
         roundQty(item_fractional, (wo_qtyord * (pQtyPer * (1 + pScrap)))), 0, 0,
         -1, pBomitemId, wo_startdate
  FROM wo, itemsite, item
  WHERE ( (itemsite_item_id=item_id)
   AND (wo_id=pWoid)
   AND (itemsite_id=pItemsiteid) );

  UPDATE wo
  SET wo_adhoc=TRUE
  WHERE (wo_id=pWoid);

  UPDATE wo
  SET wo_status='E'
  WHERE ( (wo_status='O')
   AND (wo_id=pWoid) );

  RETURN _womatlid;
END;
$_$;


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

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

CREATE FUNCTION createwomaterial(integer, integer, character, integer, numeric, numeric, numeric) 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
  pWoid ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pIssueMethod ALIAS FOR $3;
  pUomId ALIAS FOR $4;
  pQtyFxd ALIAS FOR $5;
  pQtyPer ALIAS FOR $6;
  pScrap ALIAS FOR $7;
  _womatlid INTEGER;

BEGIN

  SELECT createWoMaterial(pWoid,pItemsiteid,pIssueMethod,pUomId,pQtyFxd,pQtyPer,pScrap,-1, NULL, NULL) INTO _womatlid;

  RETURN _womatlid;
END;
$_$;


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

--
-- Name: createwomaterial(integer, integer, character, integer, numeric, numeric, integer, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createwomaterial(integer, integer, character, integer, numeric, numeric, integer, text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
  pWoid ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pIssueMethod ALIAS FOR $3;
  pUomId ALIAS FOR $4;
  pQtyPer ALIAS FOR $5;
  pScrap ALIAS FOR $6;
  pBomitemId ALIAS FOR $7;
  pNotes ALIAS FOR $8;
  pRef ALIAS FOR $9;
  _womatlid INTEGER;
  _p RECORD;

BEGIN

  _womatlid := (SELECT NEXTVAL('womatl_womatl_id_seq'));

  INSERT INTO womatl
  ( womatl_id, womatl_wo_id, womatl_itemsite_id,
    womatl_issuemethod, womatl_uom_id, womatl_qtyper, womatl_scrap,
    womatl_qtyreq, womatl_qtyiss, womatl_qtywipscrap,
    womatl_wooper_id, womatl_bomitem_id, womatl_duedate, womatl_notes, womatl_ref )
  SELECT _womatlid, wo_id, pItemsiteid,
         pIssueMethod, pUomId, pQtyPer, pScrap,
         roundQty(item_fractional, (wo_qtyord * (pQtyPer * (1 + pScrap)))), 0, 0,
         -1, pBomitemId, wo_startdate, pNotes, pRef
  FROM wo, itemsite, item
  WHERE ( (itemsite_item_id=item_id)
   AND (wo_id=pWoid)
   AND (itemsite_id=pItemsiteid) );

-- Handle all of the Phantom material requirements
  WHILE ( ( SELECT COUNT(*)
            FROM womatl, itemsite, item
            WHERE ( (womatl_itemsite_id=itemsite_id)
             AND (itemsite_item_id=item_id)
             AND (womatl_wo_id=pWoid)
             AND (item_type='F') ) ) > 0 ) LOOP

    FOR _p IN SELECT wo_qtyord, wo_startdate, womatl_id, womatl_wooper_id
              FROM wo, womatl, itemsite, item
              WHERE ( (womatl_itemsite_id=itemsite_id)
               AND (itemsite_item_id=item_id)
               AND (item_type='F')
               AND (womatl_wo_id=wo_id)
               AND (wo_id=pWoid) ) LOOP

      INSERT INTO womatl
      ( womatl_wo_id, womatl_itemsite_id, womatl_wooper_id,
        womatl_schedatwooper, womatl_duedate,
        womatl_uom_id, womatl_qtyper, womatl_scrap,
        womatl_qtyreq,
        womatl_qtyiss, womatl_qtywipscrap,
        womatl_lastissue, womatl_lastreturn,
        womatl_cost, womatl_picklist, womatl_createwo,
        womatl_issuemethod, womatl_notes, womatl_ref )
      SELECT pWoid, cs.itemsite_id, _p.womatl_wooper_id,
             womatl_schedatwooper, womatl_duedate,
             bomitem_uom_id, (bomitem_qtyper * womatl_qtyper), bomitem_scrap,
             roundQty(itemuomfractionalbyuom(bomitem_item_id, bomitem_uom_id), (_p.wo_qtyord * bomitem_qtyper * womatl_qtyper * (1 + bomitem_scrap))),
             0, 0,
             startOfTime(), startOfTime(),
             0, ci.item_picklist, ( (ci.item_type='M') AND (bomitem_createwo) ),
             bomitem_issuemethod, bomitem_notes, bomitem_ref
      FROM wo, womatl, bomitem,
           itemsite AS cs, itemsite AS ps,
           item AS ci, item AS pi
      WHERE ( (womatl_itemsite_id=ps.itemsite_id)
       AND (womatl_wo_id=wo_id)
       AND (bomitem_parent_item_id=pi.item_id)
       AND (bomitem_item_id=ci.item_id)
       AND (ps.itemsite_warehous_id=cs.itemsite_warehous_id)
       AND (cs.itemsite_item_id=ci.item_id)
       AND (ps.itemsite_item_id=pi.item_id)
       AND (woEffectiveDate(_p.wo_startdate) BETWEEN bomitem_effective AND (bomitem_expires - 1))
       AND (womatl_id=_p.womatl_id));

      DELETE FROM womatl
      WHERE (womatl_id=_p.womatl_id);

    END LOOP;
  END LOOP;

  UPDATE wo
  SET wo_adhoc=TRUE
  WHERE (wo_id=pWoid);

  UPDATE wo
  SET wo_status='E'
  WHERE ( (wo_status='O')
   AND (wo_id=pWoid) );

  RETURN _womatlid;
END;
$_$;


ALTER FUNCTION public.createwomaterial(integer, integer, character, integer, numeric, numeric, integer, text, text) OWNER TO admin;

--
-- Name: createwomaterial(integer, integer, character, integer, numeric, numeric, numeric, integer, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createwomaterial(integer, integer, character, integer, numeric, numeric, numeric, integer, text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pWoid ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pIssueMethod ALIAS FOR $3;
  pUomId ALIAS FOR $4;
  pQtyFxd ALIAS FOR $5;
  pQtyPer ALIAS FOR $6;
  pScrap ALIAS FOR $7;
  pBomitemId ALIAS FOR $8;
  pNotes ALIAS FOR $9;
  pRef ALIAS FOR $10;
  _womatlid INTEGER;

BEGIN

  SELECT createWoMaterial(pWoid,pItemsiteid,pIssueMethod,pUomId,pQtyFxd,pQtyPer,pScrap,pBomitemId,pNotes,pRef,NULL,NULL) INTO _womatlid;

  RETURN _womatlid;
END;
$_$;


ALTER FUNCTION public.createwomaterial(integer, integer, character, integer, numeric, numeric, numeric, integer, text, text) OWNER TO admin;

--
-- Name: createwomaterial(integer, integer, character, integer, numeric, numeric, numeric, integer, text, text, integer, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createwomaterial(pwoid integer, pitemsiteid integer, pissuemethod character, puomid integer, pqtyfxd numeric, pqtyper numeric, pscrap numeric, pbomitemid integer, pnotes text, pref text, pwooperid integer, ppicklist boolean) 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
  _womatlid INTEGER;

BEGIN

  SELECT createWoMaterial(pWoid,
                          pItemsiteid,
                          pIssueMethod,
                          pUomId,
                          pQtyFxd,
                          pQtyPer,
                          pScrap,
                          pBomitemId,
                          pNotes,
                          pRef,
                          pWooperId,
                          pPickList,
                          0.0) INTO _womatlid;

  RETURN _womatlid;
END;
$$;


ALTER FUNCTION public.createwomaterial(pwoid integer, pitemsiteid integer, pissuemethod character, puomid integer, pqtyfxd numeric, pqtyper numeric, pscrap numeric, pbomitemid integer, pnotes text, pref text, pwooperid integer, ppicklist boolean) OWNER TO admin;

--
-- Name: createwomaterial(integer, integer, character, integer, numeric, numeric, numeric, integer, text, text, integer, boolean, numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createwomaterial(pwoid integer, pitemsiteid integer, pissuemethod character, puomid integer, pqtyfxd numeric, pqtyper numeric, pscrap numeric, pbomitemid integer, pnotes text, pref text, pwooperid integer, ppicklist boolean, pprice numeric) 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
  _womatlid INTEGER;
  _p RECORD;

BEGIN

  _womatlid := (SELECT NEXTVAL('womatl_womatl_id_seq'));

  INSERT INTO womatl
  ( womatl_id, womatl_wo_id, womatl_itemsite_id,
    womatl_issuemethod, womatl_uom_id, womatl_qtyfxd,
    womatl_qtyper, womatl_scrap, womatl_qtyreq,
    womatl_qtyiss, womatl_qtywipscrap, womatl_wooper_id,
    womatl_bomitem_id, womatl_duedate, womatl_notes,
    womatl_ref, womatl_picklist, womatl_price )
  SELECT _womatlid, wo_id, pItemsiteid,
         pIssueMethod, pUomId, pQtyFxd,
         pQtyPer, pScrap, roundQty(item_fractional, (pQtyFxd + wo_qtyord * pQtyPer) * (1 + pScrap) ),
         0, 0, COALESCE(pWooperId, -1),
         pBomitemId, wo_startdate, pNotes,
         pRef, COALESCE(pPickList, item_picklist), pPrice 
  FROM wo, itemsite JOIN item ON (item_id=itemsite_item_id)
  WHERE ( (wo_id=pWoid)
   AND (itemsite_id=pItemsiteid) );

-- Handle all of the Phantom material requirements
  WHILE ( ( SELECT COUNT(*)
            FROM womatl, itemsite, item
            WHERE ( (womatl_itemsite_id=itemsite_id)
             AND (itemsite_item_id=item_id)
             AND (womatl_wo_id=pWoid)
             AND (item_type='F') ) ) > 0 ) LOOP

    FOR _p IN SELECT wo_qtyord, wo_startdate, womatl_id, womatl_wooper_id
              FROM wo, womatl, itemsite, item
              WHERE ( (womatl_itemsite_id=itemsite_id)
               AND (itemsite_item_id=item_id)
               AND (item_type='F')
               AND (womatl_wo_id=wo_id)
               AND (wo_id=pWoid) ) LOOP

      INSERT INTO womatl
      ( womatl_wo_id, womatl_itemsite_id, womatl_wooper_id,
        womatl_schedatwooper, womatl_duedate,
        womatl_uom_id, womatl_qtyfxd, womatl_qtyper, womatl_scrap,
        womatl_qtyreq,
        womatl_qtyiss, womatl_qtywipscrap,
        womatl_lastissue, womatl_lastreturn,
        womatl_cost, womatl_picklist, womatl_createwo,
        womatl_issuemethod, womatl_notes, womatl_ref )
      SELECT pWoid, cs.itemsite_id, _p.womatl_wooper_id,
             womatl_schedatwooper, womatl_duedate,
             bomitem_uom_id, bomitem_qtyfxd, (bomitem_qtyper * womatl_qtyper), bomitem_scrap,
             roundQty(itemuomfractionalbyuom(bomitem_item_id, bomitem_uom_id), 
                     ((bomitem_qtyfxd + _p.wo_qtyord * bomitem_qtyper) * womatl_qtyper * (1 + bomitem_scrap))),
             0, 0,
             startOfTime(), startOfTime(),
             0, ci.item_picklist, ( (ci.item_type='M') AND (bomitem_createwo) ),
             bomitem_issuemethod, bomitem_notes, bomitem_ref 
      FROM wo, womatl, bomitem, 
           itemsite AS cs, itemsite AS ps,
           item AS ci, item AS pi
      WHERE ( (womatl_itemsite_id=ps.itemsite_id)
       AND (womatl_wo_id=wo_id)
       AND (bomitem_parent_item_id=pi.item_id)
       AND (bomitem_item_id=ci.item_id)
       AND (ps.itemsite_warehous_id=cs.itemsite_warehous_id)
       AND (cs.itemsite_item_id=ci.item_id)
       AND (ps.itemsite_item_id=pi.item_id)
       AND (woEffectiveDate(_p.wo_startdate) BETWEEN bomitem_effective AND (bomitem_expires - 1))
       AND (womatl_id=_p.womatl_id));

      DELETE FROM womatl
      WHERE (womatl_id=_p.womatl_id);

    END LOOP;
  END LOOP;

  UPDATE wo
  SET wo_adhoc=TRUE
  WHERE (wo_id=pWoid);

  UPDATE wo
  SET wo_status='E'
  WHERE ( (wo_status='O')
   AND (wo_id=pWoid) );

  RETURN _womatlid;
END;
$$;


ALTER FUNCTION public.createwomaterial(pwoid integer, pitemsiteid integer, pissuemethod character, puomid integer, pqtyfxd numeric, pqtyper numeric, pscrap numeric, pbomitemid integer, pnotes text, pref text, pwooperid integer, ppicklist boolean, pprice numeric) OWNER TO admin;

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

CREATE FUNCTION creditmemototal(integer) RETURNS numeric
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pCreditmemoId ALIAS FOR $1;
  _result       NUMERIC;

BEGIN

  -- TO DO:  Add in line item taxes
  SELECT COALESCE(cmhead_freight,0.0) + COALESCE(cmhead_misc,0.0) +
         ( SELECT COALESCE(ROUND(SUM((cmitem_qtycredit * cmitem_qty_invuomratio) * cmitem_unitprice / cmitem_price_invuomratio), 2), 0.0)
             FROM cmitem
            WHERE (cmitem_cmhead_id=cmhead_id)
           ) +
         (SELECT COALESCE(SUM(tax) * -1, 0) AS tax
           FROM ( SELECT ROUND(SUM(taxdetail_tax),2) AS tax
                  FROM tax
                  JOIN calculateTaxDetailSummary('CM', cmhead_id, 'T') ON (taxdetail_tax_id=tax_id)
                  GROUP BY tax_id) AS data)
           INTO _result
  FROM cmhead
  WHERE (cmhead_id=pCreditmemoId);

  IF (NOT FOUND) THEN
    return 0;
  ELSE
    RETURN _result;
  END IF;

END;
$_$;


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

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

CREATE TABLE crmacct (
    crmacct_id integer NOT NULL,
    crmacct_number text NOT NULL,
    crmacct_name text,
    crmacct_active boolean DEFAULT true,
    crmacct_type character(1),
    crmacct_cust_id integer,
    crmacct_competitor_id integer,
    crmacct_partner_id integer,
    crmacct_prospect_id integer,
    crmacct_vend_id integer,
    crmacct_cntct_id_1 integer,
    crmacct_cntct_id_2 integer,
    crmacct_parent_id integer,
    crmacct_notes text,
    crmacct_taxauth_id integer,
    crmacct_owner_username text,
    crmacct_emp_id integer,
    crmacct_salesrep_id integer,
    crmacct_usr_username text,
    CONSTRAINT crmacct_crmacct_number_check CHECK ((crmacct_number <> ''::text)),
    CONSTRAINT crmacct_crmacct_type_check CHECK ((crmacct_type = ANY (ARRAY['I'::bpchar, 'O'::bpchar]))),
    CONSTRAINT crmacct_crmacct_usr_username_check CHECK ((btrim(crmacct_usr_username) <> ''::text)),
    CONSTRAINT crmacct_owner_username_check CHECK ((btrim(crmacct_owner_username) <> ''::text))
);


ALTER TABLE crmacct OWNER TO admin;

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

COMMENT ON TABLE crmacct IS 'CRM Accounts are umbrella records that tie together people and organizations with whom we have business relationships.';


--
-- Name: COLUMN crmacct.crmacct_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_id IS 'Internal ID of this CRM Account.';


--
-- Name: COLUMN crmacct.crmacct_number; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_number IS 'Abbreviated human-readable identifier for this CRM Account.';


--
-- Name: COLUMN crmacct.crmacct_name; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_name IS 'Long name of this CRM Account.';


--
-- Name: COLUMN crmacct.crmacct_active; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_active IS 'This CRM Account is available for new activity.';


--
-- Name: COLUMN crmacct.crmacct_type; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_type IS 'This indicates whether the CRM Account represents an organization or an individual person.';


--
-- Name: COLUMN crmacct.crmacct_cust_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_cust_id IS 'If this is not null, this CRM Account is a Customer.';


--
-- Name: COLUMN crmacct.crmacct_competitor_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_competitor_id IS 'For now, > 0 indicates this CRM Account is a competitor. Eventually this may become a foreign key to a table of competitors.';


--
-- Name: COLUMN crmacct.crmacct_partner_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_partner_id IS 'For now, > 0 indicates this CRM Account is a partner. Eventually this may become a foreign key to a table of partners.';


--
-- Name: COLUMN crmacct.crmacct_prospect_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_prospect_id IS 'If this is not null, this CRM Account is a Prospect.';


--
-- Name: COLUMN crmacct.crmacct_vend_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_vend_id IS 'If this is not null, this CRM Account is a Vendor.';


--
-- Name: COLUMN crmacct.crmacct_cntct_id_1; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_cntct_id_1 IS 'The primary contact for the CRM Account.';


--
-- Name: COLUMN crmacct.crmacct_cntct_id_2; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_cntct_id_2 IS 'The secondary contact for the CRM Account.';


--
-- Name: COLUMN crmacct.crmacct_parent_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_parent_id IS 'The internal ID of an (optional) parent CRM Account. For example, if the current CRM Account is a subsidiary of another company, the crmacct_parent_id points to the CRM Account representing that parent company.';


--
-- Name: COLUMN crmacct.crmacct_notes; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_notes IS 'Free-form comments pertaining to the CRM Account.';


--
-- Name: COLUMN crmacct.crmacct_taxauth_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_taxauth_id IS 'If this is not null, this CRM Account is a Tax Authority.';


--
-- Name: COLUMN crmacct.crmacct_owner_username; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_owner_username IS 'The application User responsible for this CRM Account.';


--
-- Name: COLUMN crmacct.crmacct_emp_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_emp_id IS 'If this is not null, this CRM Account is an Employee.';


--
-- Name: COLUMN crmacct.crmacct_salesrep_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_salesrep_id IS 'If this is not null, this CRM Account is a Sales Rep.';


--
-- Name: COLUMN crmacct.crmacct_usr_username; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_usr_username IS 'If this is not null, this CRM Account is an application User.';


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

CREATE FUNCTION crmacct() RETURNS SETOF crmacct
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _row crmacct%ROWTYPE;
  _priv TEXT;
  _grant BOOLEAN;

BEGIN
  -- This query will give us the most permissive privilege the user has been granted
  SELECT privilege, granted INTO _priv, _grant
  FROM privgranted 
  WHERE privilege IN ('MaintainAllCRMAccounts','ViewAllCRMAccounts','MaintainPersonalCRMAccounts','ViewPersonalCRMAccounts')
  ORDER BY granted DESC, sequence
  LIMIT 1;

  -- If have an 'All' privilege return all results
  IF (_priv ~ 'All' AND _grant) THEN
    FOR _row IN 
      SELECT * FROM crmacct
    LOOP
      RETURN NEXT _row;
    END LOOP;
  -- Otherwise if have any other grant, must be personal privilege.
  ELSIF (_grant) THEN
    FOR _row IN 
      SELECT * FROM crmacct 
      WHERE crmacct_owner_username = getEffectiveXtUser()
    LOOP
      RETURN NEXT _row;
    END LOOP;
  END IF;

  RETURN;

END;
$$;


ALTER FUNCTION public.crmacct() OWNER TO admin;

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

COMMENT ON FUNCTION crmacct() IS 'A table function that returns CRM Account results according to privilege settings.';


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

CREATE FUNCTION crypt(text, text) RETURNS text
    LANGUAGE c IMMUTABLE STRICT
    AS '$libdir/pgcrypto', 'pg_crypt';


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

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

CREATE FUNCTION currconcat(integer) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  id ALIAS FOR $1;
  returnVal   VARCHAR(15) := '';
BEGIN
  SELECT currConcat(curr_abbr, curr_symbol) INTO returnVal
      FROM curr_symbol WHERE curr_id = id;
  RETURN returnVal;
END;
$_$;


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

--
-- Name: currconcat(character varying, character varying); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION currconcat(character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  curr_abbr   ALIAS FOR $1;
  curr_symbol ALIAS FOR $2;
  returnVal   VARCHAR(15) := '';
BEGIN
  IF length(trim(curr_abbr)) > 0 AND length(trim(curr_symbol)) > 0 THEN
      returnVal := trim(curr_abbr) || ' - ' || trim(curr_symbol);

  ELSIF length(trim(curr_abbr)) > 0 THEN
      returnVal := curr_abbr;

  ELSIF length(trim(curr_symbol)) > 0 THEN
      returnVal := curr_symbol;
  END IF;

  RETURN returnVal;
END;
$_$;


ALTER FUNCTION public.currconcat(character varying, character varying) OWNER TO admin;

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

CREATE FUNCTION currentapmemonumber() 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
  _number INTEGER;

BEGIN

  SELECT orderseq_number INTO _number
  FROM orderseq
  WHERE (orderseq_name='APMemoNumber');
  IF (NOT FOUND) THEN
    _number := 0;
  END IF;

  RETURN _number;

END;
$$;


ALTER FUNCTION public.currentapmemonumber() OWNER TO admin;

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

CREATE FUNCTION currentarmemonumber() 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
  _number INTEGER;

BEGIN

  SELECT orderseq_number INTO _number
  FROM orderseq
  WHERE (orderseq_name='ARMemoNumber');
  IF (NOT FOUND) THEN
    _number := 0;
  END IF;

  RETURN _number;

END;
$$;


ALTER FUNCTION public.currentarmemonumber() OWNER TO admin;

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

CREATE FUNCTION currentcashrcptnumber() 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
  _number INTEGER;

BEGIN

  SELECT orderseq_number INTO _number
  FROM orderseq
  WHERE (orderseq_name='CashRcptNumber');
  IF (NOT FOUND) THEN
    _number := 0;
  END IF;

  RETURN _number;

END;
$$;


ALTER FUNCTION public.currentcashrcptnumber() OWNER TO admin;

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

CREATE FUNCTION currentnumber(text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pName   ALIAS FOR $1;
  _number INTEGER;

BEGIN
  SELECT orderseq_number INTO _number
  FROM orderseq
  WHERE (orderseq_name=pName);
  IF (NOT FOUND) THEN
    _number := 0;
  END IF;

  RETURN _number;

END;
$_$;


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

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

CREATE FUNCTION currexchangecheckoverlap() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
    numberOfOverlaps INTEGER NOT NULL := 0;
    curr_string VARCHAR(16);
    new_id INTEGER;
BEGIN
  new_id := NEW.curr_id;
  -- ensure that effective date <= expiration date
  IF NEW.curr_effective > NEW.curr_expires THEN
    RAISE EXCEPTION
      'Effective date % must be earlier than expiration date %',
      NEW.curr_effective, NEW.curr_expires;
  END IF;

  -- ensure new exchange rate does not overlap in time with any others
  SELECT count(*) INTO numberOfOverlaps
    FROM curr_rate
    WHERE curr_id = NEW.curr_id
      AND curr_rate_id != NEW.curr_rate_id
      AND (
          (curr_effective BETWEEN
              NEW.curr_effective AND NEW.curr_expires OR
           curr_expires BETWEEN
              NEW.curr_effective AND NEW.curr_expires)
         OR (curr_effective <= NEW.curr_effective AND
             curr_expires   >= NEW.curr_expires)
      );
  IF numberOfOverlaps > 0 THEN
    SELECT currConcat(curr_symbol, curr_abbr)
      INTO curr_string
      FROM curr_symbol
      WHERE curr_id = new_id;
    RAISE EXCEPTION
      'The date range % to % overlaps with another date range.',
      NEW.curr_effective, NEW.curr_expires;
  END IF;
  RETURN NEW;
END;
$$;


ALTER FUNCTION public.currexchangecheckoverlap() OWNER TO admin;

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

CREATE FUNCTION currgain(integer, numeric, date, date) RETURNS numeric
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pId ALIAS FOR $1;
  pValue ALIAS FOR $2;
  pStart ALIAS FOR $3;
  pEnd ALIAS FOR $4;
  _start DATE;
  _end DATE;
  _gain NUMERIC;
  _multiplier	INTEGER	:= 1;

BEGIN
  IF (pEnd = pStart OR pValue = 0) THEN
    RETURN 0;
  END IF;

  IF (pStart > pEnd) THEN
    _start := pEnd;
    _end   := pStart;
    _multiplier := -1;
  ELSE
    _start := pStart;
    _end := pEnd;
  END IF;

  _gain := currToBase(pId, pValue, _start) - currToBase(pId, pValue, _end);
  IF (_gain IS NULL) THEN
    RAISE EXCEPTION 'Missing exchange rate for curr_id % on % or %',
                    pId, _start, _end;
  END IF;

  RETURN _gain * _multiplier;
END;
$_$;


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

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

CREATE FUNCTION curronebase() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  baseCount INTEGER := 0;
BEGIN
  IF NEW.curr_base = TRUE THEN
    SELECT count(*)
      INTO baseCount
      FROM curr_symbol
      WHERE curr_base = TRUE
        AND curr_id != NEW.curr_id;
    IF baseCount > 0 THEN
      RAISE EXCEPTION
          'Cannot make % - % the base currency because one is already defined.',
          NEW.curr_symbol, NEW.curr_abbr;
    ELSE
      SELECT count(*)
        INTO baseCount
        FROM curr_rate
        WHERE curr_id = NEW.curr_id;
      IF baseCount = 0 THEN
        -- put a row in the curr_rate table to avoid special-case
        -- code for converting base currency to base currency
        INSERT INTO curr_rate
          (curr_id, curr_rate, curr_effective, curr_expires) VALUES
          (NEW.curr_id, 1, startOfTime(), endOfTime());
      END IF;
    END IF;
  END IF;
  RETURN NEW;
END;
$$;


ALTER FUNCTION public.curronebase() OWNER TO admin;

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

CREATE FUNCTION currrate(integer, date) RETURNS numeric
    LANGUAGE plpgsql STABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  RETURN currRate($1, NULL, $2);

END;
$_$;


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

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

CREATE FUNCTION currrate(pfromcurr integer, ptocurr integer, pdate date) RETURNS numeric
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _fromRate NUMERIC := 1.0;
  _toRate NUMERIC := 1.0;
  _returnVal NUMERIC := 1.0;
BEGIN
  IF pFromCurr = pToCurr THEN
    RETURN _returnVal;
  END IF;

  IF (pFromCurr IS NOT NULL) THEN
    SELECT curr_rate INTO _fromRate
    FROM curr_rate
    WHERE ( (curr_id=pFromCurr)
    AND (pDate BETWEEN curr_effective AND curr_expires) );

    IF ( NOT FOUND) THEN
      RAISE EXCEPTION 'Currency exchange rate for currency % not found on %', pFromCurr, formatDate(pDate);
    END IF;
  END IF;

  IF (pToCurr IS NOT NULL) THEN
    SELECT curr_rate INTO _toRate
    FROM curr_rate
    WHERE ( (curr_id=pToCurr)
    AND (pDate BETWEEN curr_effective AND curr_expires) );

    IF ( NOT FOUND) THEN
      RAISE EXCEPTION 'Currency exchange rate for currency % not found on %', pToCurr, formatDate(pDate);
    END IF;
  END IF;

  _returnVal := _fromRate / _toRate;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.currrate(pfromcurr integer, ptocurr integer, pdate date) OWNER TO admin;

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

CREATE FUNCTION currtobase(integer, numeric, date) RETURNS numeric
    LANGUAGE plpgsql STABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pId     ALIAS FOR $1;
  pValue  ALIAS FOR $2;
  _date  DATE;
  _output NUMERIC;
BEGIN
  _date := $3;
  IF _date IS NULL THEN
    _date := 'now';
  END IF;

  IF pValue = 0 OR pValue IS NULL THEN
    _output := 0;
  ELSIF (baseCurrId() = pId) THEN
    _output := pValue;
  ELSE
    SELECT pValue / curr_rate
        INTO  _output
      FROM  curr_rate
     WHERE curr_id = pId
       AND _date BETWEEN curr_effective AND curr_expires;
    IF (_output IS NULL OR NOT FOUND) THEN
      RAISE EXCEPTION 'No exchange rate for % on %', pId, _date;
    END IF;
  END IF;
  RETURN _output;
END;
$_$;


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

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

CREATE FUNCTION currtocurr(integer, integer, numeric, date) RETURNS numeric
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pFromCurr ALIAS FOR $1;
  pToCurr ALIAS FOR $2;
  pValue   ALIAS FOR $3;
  pEffective ALIAS FOR $4;
  _convertedValue NUMERIC;
  _fromRate NUMERIC;
  _toRate NUMERIC;
BEGIN
  IF pFromCurr = pToCurr THEN
    RETURN pValue;
  END IF;

  IF pValue = 0 OR pValue IS NULL THEN
    RETURN 0;
  END IF;

  SELECT curr_rate INTO _fromRate
  FROM curr_rate
  WHERE curr_id = pFromCurr
    AND pEffective BETWEEN curr_effective AND curr_expires;

  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'No exchange rate for % on %', pFromCurr, pEffective;
  END IF;

  SELECT curr_rate INTO _toRate
  FROM curr_rate
  WHERE curr_id = pToCurr
    AND pEffective BETWEEN curr_effective AND curr_expires;

  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'No exchange rate for % on %', pToCurr, pEffective;
  END IF;

  _convertedValue := pValue * _toRate / _fromRate;

  RETURN _convertedValue;
END;
$_$;


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

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

CREATE FUNCTION currtolocal(integer, numeric, date) RETURNS numeric
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
    pId     ALIAS FOR $1;
    pValue  ALIAS FOR $2;
    _date   DATE;
    _output NUMERIC;
BEGIN
    _date := $3;
    IF _date IS NULL THEN
        _date := 'now';
    END IF;

    IF pValue = 0 OR pValue IS NULL THEN
        _output := 0;
    ELSIF (baseCurrId() = pId) THEN
      _output := pValue;
    ELSE
        SELECT pValue * curr_rate
            INTO  _output
            FROM  curr_rate
            WHERE curr_id = pId
              AND _date BETWEEN curr_effective AND curr_expires;
        IF (_output IS NULL OR NOT FOUND) THEN
          RAISE EXCEPTION 'No exchange rate for % on %', pId, _date;
        END IF;
    END IF;
    RETURN _output;
END;
$_$;


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

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

CREATE FUNCTION custitem(cust_id integer, shipto_id integer DEFAULT (-1), asof date DEFAULT ('now'::text)::date) RETURNS SETOF integer
    LANGUAGE sql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.

  -- Non Exclusive
  SELECT item_id
  FROM item 
  WHERE (NOT item_exclusive)
   AND (item_sold)
  UNION
  -- Exclusive, Shipto match
  SELECT item_id
  FROM item
    JOIN ipsiteminfo ON (ipsitem_item_id=item_id)
    JOIN ipshead ON (ipshead_id=ipsitem_ipshead_id)
    JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
  WHERE (item_exclusive)
   AND (item_sold)
   AND ($2 != -1)
   AND (ipsass_shipto_id=$2)
   AND ($3 BETWEEN ipshead_effective AND (ipshead_expires - 1))
  UNION
  SELECT item_id
  FROM item
    JOIN ipsiteminfo ON (ipsitem_prodcat_id=item_prodcat_id)
    JOIN ipshead ON (ipshead_id=ipsitem_ipshead_id)
    JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
  WHERE (item_exclusive)
   AND (item_sold)
   AND ($2 != -1)
   AND (ipsass_shipto_id=$2)
   AND ($3 BETWEEN ipshead_effective AND (ipshead_expires - 1))
  UNION
   -- Exclusive, Shipto pattern match
  SELECT item_id
  FROM item
    JOIN ipsiteminfo ON (ipsitem_item_id=item_id)
    JOIN ipshead ON (ipshead_id=ipsitem_ipshead_id)
    JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
    JOIN shiptoinfo ON (shipto_num ~ ipsass_shipto_pattern)
  WHERE (item_exclusive)
   AND (item_sold)
   AND (COALESCE(length(ipsass_shipto_pattern), 0) > 0)
   AND (ipsass_cust_id=$1)
   AND ($2 != -1)
   AND (shipto_id=$2)
   AND ($3 BETWEEN ipshead_effective AND (ipshead_expires - 1))
  UNION
  SELECT item_id
  FROM item
    JOIN ipsiteminfo ON (ipsitem_prodcat_id=item_prodcat_id)
    JOIN ipshead ON (ipshead_id=ipsitem_ipshead_id)
    JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
    JOIN shiptoinfo ON (shipto_num ~ ipsass_shipto_pattern)
  WHERE (item_exclusive)
   AND (item_sold)
   AND (COALESCE(length(ipsass_shipto_pattern), 0) > 0)
   AND (ipsass_cust_id=$1)
   AND ($2 != -1)
   AND (shipto_id=$2)
   AND ($3 BETWEEN ipshead_effective AND (ipshead_expires - 1))
  UNION
   -- Exclusive, Customer match
  SELECT item_id
  FROM item
    JOIN ipsiteminfo ON (ipsitem_item_id=item_id)
    JOIN ipshead ON (ipshead_id=ipsitem_ipshead_id)
    JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
  WHERE (item_exclusive)
   AND (item_sold)
   AND (ipsass_cust_id=$1)
   AND (ipsass_shipto_id=-1)
   AND (ipsass_shipto_pattern='')
   AND (ipsass_custtype_id=-1)
   AND (ipsass_custtype_pattern='')
   AND ($3 BETWEEN ipshead_effective AND (ipshead_expires - 1))
  UNION
  SELECT item_id
  FROM item
    JOIN ipsiteminfo ON (ipsitem_prodcat_id=item_prodcat_id)
    JOIN ipshead ON (ipshead_id=ipsitem_ipshead_id)
    JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
  WHERE (item_exclusive)
   AND (item_sold)
   AND (ipsass_cust_id=$1)
   AND (ipsass_shipto_id=-1)
   AND (ipsass_shipto_pattern='')
   AND (ipsass_custtype_id=-1)
   AND (ipsass_custtype_pattern='')
   AND ($3 BETWEEN ipshead_effective AND (ipshead_expires - 1))
  UNION
  -- Exclusive, Customer Type match
  SELECT item_id
  FROM item
    JOIN ipsiteminfo ON (ipsitem_item_id=item_id)
    JOIN ipshead ON (ipshead_id=ipsitem_ipshead_id)
    JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
    JOIN custinfo ON (ipsass_custtype_id=cust_custtype_id)
  WHERE (item_exclusive)
   AND (item_sold)
   AND (cust_id=$1)
   AND ($3 BETWEEN ipshead_effective AND (ipshead_expires - 1))
  UNION
  SELECT item_id
  FROM item
    JOIN ipsiteminfo ON (ipsitem_prodcat_id=item_prodcat_id)
    JOIN ipshead ON (ipshead_id=ipsitem_ipshead_id)
    JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
    JOIN custinfo ON (ipsass_custtype_id=cust_custtype_id)
  WHERE (item_exclusive)
   AND (item_sold)
   AND (cust_id=$1)
   AND ($3 BETWEEN ipshead_effective AND (ipshead_expires - 1))
  UNION
  -- Exclusive, Customer Type pattern match
  SELECT item_id
  FROM item
    JOIN ipsiteminfo ON (ipsitem_item_id=item_id)
    JOIN ipshead ON (ipshead_id=ipsitem_ipshead_id)
    JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
    JOIN custtype ON (custtype_code ~ ipsass_custtype_pattern)
    JOIN custinfo ON (cust_custtype_id=custtype_id)
  WHERE (item_exclusive)
   AND (item_sold)
   AND (COALESCE(length(ipsass_custtype_pattern), 0) > 0)
   AND (cust_id=$1)
   AND ($3 BETWEEN ipshead_effective AND (ipshead_expires - 1))
  UNION
  SELECT item_id
  FROM item
    JOIN ipsiteminfo ON (ipsitem_prodcat_id=item_prodcat_id)
    JOIN ipshead ON (ipshead_id=ipsitem_ipshead_id)
    JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
    JOIN custtype ON (custtype_code ~ ipsass_custtype_pattern)
    JOIN custinfo ON (cust_custtype_id=custtype_id)
  WHERE (item_exclusive)
   AND (item_sold)
   AND (COALESCE(length(ipsass_custtype_pattern), 0) > 0)
   AND (cust_id=$1)
   AND ($3 BETWEEN ipshead_effective AND (ipshead_expires - 1))
   
$_$;


ALTER FUNCTION public.custitem(cust_id integer, shipto_id integer, asof date) OWNER TO admin;

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

CREATE FUNCTION customercanpurchase(integer, integer) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pitemid ALIAS FOR $1;
  pCustid ALIAS FOR $2;

BEGIN
  RETURN customerCanPurchase(pitemid, pCustid, -1);
END;
$_$;


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

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

CREATE FUNCTION customercanpurchase(integer, integer, integer) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pitemid ALIAS FOR $1;
  pCustid ALIAS FOR $2;
  pShiptoid AlIAS FOR $3;
  _id INTEGER;
  _item RECORD;

BEGIN
  RETURN customerCanPurchase(pitemid, pCustid, pShiptoid, CURRENT_DATE);
END;
$_$;


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

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

CREATE FUNCTION customercanpurchase(integer, integer, integer, date) 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
  pitemid ALIAS FOR $1;
  pCustid ALIAS FOR $2;
  pShiptoid AlIAS FOR $3;
  pAsOf ALIAS FOR $4;
  _id INTEGER;
  _item RECORD;

BEGIN

  SELECT item_sold, item_exclusive
    INTO _item
    FROM item
   WHERE(item_id=pItemid);

--  Make sure that this is at least a sold Item
  IF (NOT _item.item_sold) THEN
    RETURN FALSE;
  END IF;

--  Everyone can purchase a non-exclusive item
  IF (NOT _item.item_exclusive) THEN
    RETURN TRUE;
  END IF;

  IF(pShiptoid != -1) THEN
--  Check for a shipto Assigned Price
    SELECT ipsitem_id INTO _id
      FROM ipsiteminfo, ipshead, ipsass
     WHERE((ipsitem_ipshead_id=ipshead_id)
       AND (ipsass_ipshead_id=ipshead_id)
       AND (pAsOf BETWEEN ipshead_effective AND (ipshead_expires - 1))
       AND (ipsitem_item_id=pItemid)
       AND (ipsass_shipto_id != -1)
       AND (ipsass_shipto_id=pShiptoid))
     LIMIT 1;
    IF (FOUND) THEN
      RETURN TRUE;
    END IF;
    SELECT ipsitem_id INTO _id
      FROM ipsiteminfo, item, ipshead, ipsass
     WHERE((ipsitem_ipshead_id=ipshead_id)
       AND (ipsitem_prodcat_id = item_prodcat_id)
       AND (ipsass_ipshead_id=ipshead_id)
       AND (pAsOf BETWEEN ipshead_effective AND (ipshead_expires - 1))
       AND (item_id=pItemid)
       AND (ipsass_shipto_id != -1)
       AND (ipsass_shipto_id=pShiptoid))
     LIMIT 1;
    IF (FOUND) THEN
      RETURN TRUE;
    END IF;

--  Check for a Shipto Pattern Assigned Price
    SELECT ipsitem_id INTO _id
      FROM ipsiteminfo, ipshead, ipsass, shiptoinfo
     WHERE((ipsitem_ipshead_id=ipshead_id)
       AND (ipsass_ipshead_id=ipshead_id)
       AND (pAsOf BETWEEN ipshead_effective AND (ipshead_expires - 1))
       AND (COALESCE(length(ipsass_shipto_pattern), 0) > 0)
       AND (shipto_num ~ ipsass_shipto_pattern)
       AND (ipsass_cust_id=pCustid)
       AND (ipsitem_item_id=pItemid)
       AND (shipto_id=pShiptoid))
     LIMIT 1;
    IF (FOUND) THEN
      RETURN TRUE;
    END IF;
    SELECT ipsitem_id INTO _id
      FROM ipsiteminfo, item, ipshead, ipsass, shiptoinfo
     WHERE((ipsitem_ipshead_id=ipshead_id)
       AND (ipsitem_prodcat_id = item_prodcat_id)
       AND (ipsass_ipshead_id=ipshead_id)
       AND (pAsOf BETWEEN ipshead_effective AND (ipshead_expires - 1))
       AND (COALESCE(length(ipsass_shipto_pattern), 0) > 0)
       AND (shipto_num ~ ipsass_shipto_pattern)
       AND (ipsass_cust_id=pCustid)
       AND (item_id=pItemid)
       AND (shipto_id=pShiptoid))
     LIMIT 1;
    IF (FOUND) THEN
      RETURN TRUE;
    END IF;
  END IF;

--  Check for a Customer Assigned Price
  SELECT ipsitem_id INTO _id
    FROM ipsiteminfo, ipshead, ipsass
   WHERE((ipsitem_ipshead_id=ipshead_id)
     AND (ipsass_ipshead_id=ipshead_id)
     AND (pAsOf BETWEEN ipshead_effective AND (ipshead_expires - 1))
     AND (ipsitem_item_id=pItemid)
     AND (COALESCE(length(ipsass_shipto_pattern), 0) = 0)
     AND (ipsass_cust_id=pCustid))
   LIMIT 1;
  IF (FOUND) THEN
    RETURN TRUE;
  END IF;
  SELECT ipsitem_id INTO _id
    FROM ipsiteminfo, item, ipshead, ipsass
   WHERE((ipsitem_ipshead_id=ipshead_id)
     AND (ipsitem_prodcat_id = item_prodcat_id)
     AND (ipsass_ipshead_id=ipshead_id)
     AND (pAsOf BETWEEN ipshead_effective AND (ipshead_expires - 1))
     AND (item_id=pItemid)
     AND (COALESCE(length(ipsass_shipto_pattern), 0) = 0)
     AND (ipsass_cust_id=pCustid))
   LIMIT 1;
  IF (FOUND) THEN
    RETURN TRUE;
  END IF;

--  Check for a Customer Type Assigned Price
  SELECT ipsitem_id INTO _id
    FROM ipsiteminfo, ipshead, ipsass, custinfo
   WHERE( (ipsitem_ipshead_id=ipshead_id)
     AND  (ipsass_ipshead_id=ipshead_id)
     AND  (ipsass_custtype_id != -1)
     AND  (cust_custtype_id = ipsass_custtype_id)
     AND  (pAsOf BETWEEN ipshead_effective AND (ipshead_expires - 1))
     AND  (ipsitem_item_id=pItemid)
     AND  (cust_id=pCustid))
    LIMIT 1;
  IF (FOUND) THEN
    RETURN TRUE;
  END IF;
  SELECT ipsitem_id INTO _id
    FROM ipsiteminfo, item, ipshead, ipsass, custinfo
   WHERE( (ipsitem_ipshead_id=ipshead_id)
     AND  (ipsitem_prodcat_id = item_prodcat_id)
     AND  (ipsass_ipshead_id=ipshead_id)
     AND  (ipsass_custtype_id != -1)
     AND  (cust_custtype_id = ipsass_custtype_id)
     AND  (pAsOf BETWEEN ipshead_effective AND (ipshead_expires - 1))
     AND  (item_id=pItemid)
     AND  (cust_id=pCustid))
    LIMIT 1;
  IF (FOUND) THEN
    RETURN TRUE;
  END IF;

--  Check for a Customer Type Pattern Assigned Price
  SELECT ipsitem_id INTO _id
    FROM ipsiteminfo, ipshead, ipsass, custtype, custinfo
   WHERE((ipsitem_ipshead_id=ipshead_id)
     AND (ipsass_ipshead_id=ipshead_id)
     AND (coalesce(length(ipsass_custtype_pattern), 0) > 0)
     AND (custtype_code ~ ipsass_custtype_pattern)
     AND (cust_custtype_id=custtype_id)
     AND (pAsOf BETWEEN ipshead_effective AND (ipshead_expires - 1))
     AND (ipsitem_item_id=pItemid)
     AND (cust_id=pCustid))
   LIMIT 1;
  IF (FOUND) THEN
    RETURN TRUE;
  END IF;
  SELECT ipsitem_id INTO _id
    FROM ipsiteminfo, item, ipshead, ipsass, custtype, custinfo
   WHERE((ipsitem_ipshead_id=ipshead_id)
     AND (ipsitem_prodcat_id = item_prodcat_id)
     AND (ipsass_ipshead_id=ipshead_id)
     AND (coalesce(length(ipsass_custtype_pattern), 0) > 0)
     AND (custtype_code ~ ipsass_custtype_pattern)
     AND (cust_custtype_id=custtype_id)
     AND (pAsOf BETWEEN ipshead_effective AND (ipshead_expires - 1))
     AND (item_id=pItemid)
     AND (cust_id=pCustid))
   LIMIT 1;
  IF (FOUND) THEN
    RETURN TRUE;
  END IF;

--  That's it, Sales don't count - yet
  RETURN FALSE;

END;
$_$;


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

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

CREATE FUNCTION dearmor(text) RETURNS bytea
    LANGUAGE c IMMUTABLE STRICT
    AS '$libdir/pgcrypto', 'pg_dearmor';


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

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

CREATE FUNCTION decrypt(bytea, bytea, text) RETURNS bytea
    LANGUAGE c IMMUTABLE STRICT
    AS '$libdir/pgcrypto', 'pg_decrypt';


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

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

CREATE FUNCTION decrypt_iv(bytea, bytea, bytea, text) RETURNS bytea
    LANGUAGE c IMMUTABLE STRICT
    AS '$libdir/pgcrypto', 'pg_decrypt_iv';


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

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

CREATE FUNCTION defaultlocationname(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
  pItemsiteid ALIAS FOR $1;
  _p RECORD;

BEGIN

  SELECT itemsite_location_id, itemsite_location INTO _p
  FROM itemsite
  WHERE (itemsite_id=pItemsiteid);

  IF (NOT FOUND) THEN
    RETURN 'Error';
  ELSIF (_p.itemsite_location_id = -1) THEN
    RETURN _p.itemsite_location;
  ELSE
    RETURN formatLocationName(_p.itemsite_location_id);
  END IF;

END;
$_$;


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

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

CREATE FUNCTION deleteaccount(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
  pAccntid ALIAS FOR $1;
  _check INTEGER;

BEGIN

--  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=pAccntid)
     OR   (costcat_liability_accnt_id=pAccntid)
     OR   (costcat_adjustment_accnt_id=pAccntid)
     OR   (costcat_matusage_accnt_id=pAccntid)
     OR   (costcat_purchprice_accnt_id=pAccntid)
     OR   (costcat_scrap_accnt_id=pAccntid)
     OR   (costcat_invcost_accnt_id=pAccntid)
     OR   (costcat_wip_accnt_id=pAccntid)
     OR   (costcat_shipasset_accnt_id=pAccntid)
     OR   (costcat_mfgscrap_accnt_id=pAccntid)
     OR   (costcat_transform_accnt_id=pAccntid)
     OR   (costcat_freight_accnt_id=pAccntid)
     OR   (costcat_exp_accnt_id=pAccntid) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

  IF (fetchMetricText('Application') = 'Standard') THEN
    SELECT costcat_id INTO _check
    FROM costcat
    WHERE ( (costcat_toliability_accnt_id=pAccntid)
       OR   (costcat_laboroverhead_accnt_id=pAccntid) )
    LIMIT 1;
    IF (FOUND) THEN
      RETURN -1;
    END IF;
  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=pAccntid)
     OR   (salesaccnt_credit_accnt_id=pAccntid)
     OR   (salesaccnt_cos_accnt_id=pAccntid) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -2;
  END IF;

  IF (fetchMetricText('Application') = 'Standard') THEN
    SELECT salesaccnt_id INTO _check
    FROM salesaccnt
    WHERE ( (salesaccnt_returns_accnt_id=pAccntid)
       OR   (salesaccnt_cor_accnt_id=pAccntid)
       OR   (salesaccnt_cow_accnt_id=pAccntid) )
    LIMIT 1;
    IF (FOUND) THEN
      RETURN -2;
    END IF;
  END IF;

--  Check to see if the passed accnt is used in a Sales Category
  SELECT salescat_id INTO _check
  FROM salescat
  WHERE ( (salescat_sales_accnt_id=pAccntid)
     OR   (salescat_prepaid_accnt_id=pAccntid)
     OR   (salescat_ar_accnt_id=pAccntid) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -2;
  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=pAccntid)
     OR   (araccnt_ar_accnt_id=pAccntid)
     OR   (araccnt_prepaid_accnt_id=pAccntid)
     OR   (araccnt_deferred_accnt_id=pAccntid)
     OR   (araccnt_discount_accnt_id=pAccntid) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -3;
  END IF;

--  Check to see if the passed accnt is used in a Warehouse
  IF EXISTS (SELECT 1
               FROM whsinfo
              WHERE (warehous_default_accnt_id=pAccntid)) THEN
    RETURN -4;
  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=pAccntid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -5;
  END IF;

  SELECT bankadjtype_id INTO _check
  FROM bankadjtype
  WHERE (bankadjtype_accnt_id=pAccntid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -5;
  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=pAccntid)
     OR   (expcat_liability_accnt_id=pAccntid)
     OR   (expcat_purchprice_accnt_id=pAccntid)
     OR   (expcat_freight_accnt_id=pAccntid) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -6;
  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=pAccntid)
     OR   (tax_dist_accnt_id=pAccntid) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -7;
  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=pAccntid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -8;
  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=pAccntid)
     OR   (apaccnt_prepaid_accnt_id=pAccntid)
     OR   (apaccnt_discount_accnt_id=pAccntid) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -9;
  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=pAccntid)
   LIMIT 1;
  IF (FOUND) THEN
    RETURN -11;
  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=pAccntid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -99;
  END IF;

  SELECT glseries_accnt_id INTO _check
  FROM glseries
  WHERE (glseries_accnt_id=pAccntid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -99;
  END IF;

  SELECT trialbal_accnt_id INTO _check
  FROM trialbal
  WHERE (trialbal_accnt_id=pAccntid)
    AND (trialbal_beginning != 0 OR trialbal_ending != 0)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -99;
  END IF;

  SELECT cashrcptmisc_accnt_id INTO _check
  FROM cashrcptmisc
  WHERE (cashrcptmisc_accnt_id=pAccntid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -99;
  END IF;

--  Delete any non-critical use
  DELETE FROM flitem
  WHERE (flitem_accnt_id=pAccntid);

  -- only possible because of trialbal error-check above
  DELETE FROM trialbal
  WHERE (trialbal_accnt_id=pAccntid)
    AND (trialbal_beginning=0)
    AND (trialbal_ending=0);

--  Delete the Account
  DELETE FROM accnt
  WHERE (accnt_id=pAccntid);

  RETURN 0;

END;
$_$;


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

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

CREATE FUNCTION deleteaccountingperiod(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
  pPeriodid ALIAS FOR $1;
  _check RECORD;

BEGIN

--  Check to make sure that the passed period is not closed
  IF ( ( SELECT period_closed
         FROM period
         WHERE (period_id=pPeriodid) ) ) THEN
    RETURN -1;
  END IF;

--  Check to make sure that there are not any posted G/L Transactions
--  in the period.
  SELECT gltrans_id INTO _check
  FROM gltrans, period
  WHERE ( (gltrans_date BETWEEN period_start AND period_end)
   AND (gltrans_posted)
   AND (period_id=pPeriodid) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -4;
  END IF;

  SELECT b.period_id INTO _check
    FROM period AS a, period AS b
   WHERE((a.period_id=pPeriodid)
     AND (a.period_end < b.period_start))
   LIMIT 1;
  IF (FOUND) THEN
    RETURN -5;
  END IF;

--  Delete the period
  DELETE FROM period
  WHERE (period_id=pPeriodid);

--  Also Delete the period from TrialBalance
  DELETE FROM trialbal
  WHERE (trialbal_period_id=pPeriodid);

  RETURN 1;

END;
$_$;


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

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

CREATE FUNCTION deleteaccountingyearperiod(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
  pPeriodid ALIAS FOR $1;
  _check RECORD;

BEGIN

--  Check to make sure that the passed yearperiod is not closed
  IF ( ( SELECT yearperiod_closed
         FROM yearperiod
         WHERE (yearperiod_id=pPeriodid) ) ) THEN
    RETURN -1;
  END IF;

  -- this yearperiod is in use by existing periods
  IF (EXISTS(SELECT period_id
             FROM period
             WHERE (period_yearperiod_id=pPeriodid))) THEN
    RETURN -2;
  END IF;

--  Delete the yearperiod
  DELETE FROM yearperiod
  WHERE (yearperiod_id=pPeriodid);

  RETURN 1;

END;
$_$;


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

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

CREATE FUNCTION deleteaddress(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
  paddrId     ALIAS FOR $1;
  _count      INTEGER := 0;
BEGIN
  SELECT count(*) INTO _count
    FROM cntct
    WHERE (cntct_active
      AND  (cntct_addr_id = paddrId));
  IF (_count > 0) THEN
    RETURN -1;
  END IF;

  SELECT count(*) INTO _count
    FROM vendinfo
    WHERE (vend_active
      AND  (vend_addr_id = paddrId));
  IF (_count > 0) THEN
    RETURN -2;
  END IF;

  SELECT count(*) INTO _count
    FROM shiptoinfo
    WHERE (shipto_active
      AND  (shipto_addr_id = paddrId));
  IF (_count > 0) THEN
    RETURN -3;
  END IF;

  SELECT count(*) INTO _count
    FROM vendaddrinfo
    WHERE (vendaddr_addr_id = paddrId);
  IF (_count > 0) THEN
    RETURN -4;
  END IF;

  SELECT count(*) INTO _count
    FROM whsinfo
    WHERE (warehous_active
      AND  (warehous_addr_id = paddrId));
  IF (_count > 0) THEN
    RETURN -5;
  END IF;

  UPDATE cntct SET cntct_addr_id = NULL WHERE (cntct_addr_id = paddrId);
  UPDATE vendinfo SET vend_addr_id = NULL WHERE (vend_addr_id = paddrId);
  UPDATE shiptoinfo SET shipto_addr_id = NULL WHERE (shipto_addr_id =paddrId);
  UPDATE vendaddrinfo SET vendaddr_addr_id = NULL
    WHERE (vendaddr_addr_id = paddrId);
  UPDATE whsinfo SET warehous_addr_id = NULL WHERE (warehous_addr_id=paddrId);

  DELETE FROM addr WHERE addr_id = paddrId;
  RETURN 0;
END;
$_$;


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

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

CREATE FUNCTION deleteapcheck(integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  RAISE WARNING 'deleteAPCheck() is deprecated - use deleteCheck() instead';
  RETURN deleteCheck($1);
END;
$_$;


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

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

CREATE FUNCTION deletebankadjustmenttype(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
  pBankadjtypeid ALIAS FOR $1;
  _check INTEGER;

BEGIN

-- Check to see if the the adjustment type is being used in any adjustments
  SELECT bankadj_bankadjtype_id INTO _check
    FROM bankadj
   WHERE (bankadj_bankadjtype_id=pBankadjtypeid)
   LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

--  Delete the Account
  DELETE FROM bankadjtype
  WHERE (bankadjtype_id=pbankadjtypeid);

  RETURN 0;

END;
$_$;


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

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

CREATE FUNCTION deletebankreconciliation(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
  pbankrecid    ALIAS FOR $1;
BEGIN
  DELETE FROM bankrecitem
  WHERE bankrecitem_bankrec_id=pbankrecid;

  DELETE FROM bankrec
  WHERE bankrec_id=pbankrecid;

  RETURN 0;
END;
$_$;


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

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

CREATE FUNCTION deletebom(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;
  _result INTEGER;

BEGIN

  IF (fetchmetricbool('RevControl')) THEN
    SELECT rev_id INTO _result
    FROM rev
    WHERE ((rev_target_id=pItemid)
    AND (rev_target_type = 'BOM'))
    LIMIT 1;
    IF (FOUND) THEN
      RAISE EXCEPTION 'Bill of Materials has revision control records and may not be deleted.';
    END IF;
  END IF;

  DELETE FROM bomhead
  WHERE (bomhead_item_id=pItemid);
  DELETE FROM bomitem
  WHERE (bomitem_parent_item_id=pItemid);

  RETURN 0;

END;
$_$;


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

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

CREATE FUNCTION deletebomworkset(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
  pWorksetid ALIAS FOR $1;

BEGIN

--  All done with the bomwork set indicated by pWorksetid, delete all of it
  DELETE FROM bomwork
  WHERE (bomwork_set_id=pWorksetid);

  RETURN 1;

END;
$_$;


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

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

CREATE FUNCTION deletebudget(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
  pBudgheadid ALIAS FOR $1;

BEGIN
  DELETE FROM budgitem WHERE (budgitem_budghead_id=pBudgheadid);
  DELETE FROM budghead WHERE (budghead_id=pBudgheadid);

  RETURN pBudgheadid;
END;
$_$;


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

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

CREATE FUNCTION deletebudgetitems(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
  pBudgheadid ALIAS FOR $1;

BEGIN
  DELETE FROM budgitem WHERE (budgitem_budghead_id=pBudgheadid);

  RETURN pBudgheadid;
END;
$_$;


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

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

CREATE FUNCTION deletecashrcpt(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
  pcashrcptid ALIAS FOR $1;
  _ccreceipt    BOOLEAN;

BEGIN

  IF EXISTS(SELECT cashrcpt_id
            FROM cashrcpt
            JOIN ccpay ON (cashrcpt_cust_id=ccpay_cust_id)
                       AND ((CASE WHEN TRIM(COALESCE(cashrcpt_docnumber, ''))='' THEN TEXT(cashrcpt_id)
                                  ELSE cashrcpt_docnumber
                             END)=ccpay_order_number)
            WHERE ((cashrcpt_fundstype IN ('A', 'D', 'M', 'V'))
               AND (ccpay_status NOT IN ('D', 'X'))
               AND (ccpay_id NOT IN (SELECT payco_ccpay_id FROM payco))
               AND (cashrcpt_id=pcashrcptid))) THEN
    RETURN -1;
  END IF;

  IF EXISTS(SELECT cashrcpt_id
            FROM cashrcpt
            WHERE ( (cashrcpt_id=pcashrcptid)
              AND   (cashrcpt_posted) )) THEN
    RETURN -2;
  END IF;

  -- If there are applications for this Cash Receipt then
  -- it has been posted and reversed.  Void instead of delete.
  IF EXISTS(SELECT cashrcpt_id
            FROM cashrcpt JOIN cashrcptitem ON (cashrcptitem_cashrcpt_id=cashrcpt_id)
                          JOIN arapply ON ((arapply_reftype='CRA') AND
                                           (arapply_ref_id=cashrcptitem_id))
            WHERE (cashrcpt_id=pcashrcptid))
     OR
     EXISTS(SELECT cashrcpt_id
            FROM cashrcpt JOIN cashrcptmisc ON (cashrcptmisc_cashrcpt_id=cashrcpt_id)
                          JOIN arapply ON ((arapply_reftype='CRD') AND
                                           (arapply_ref_id=cashrcptmisc_id))
            WHERE (cashrcpt_id=pcashrcptid)) THEN
    UPDATE cashrcpt SET cashrcpt_void = TRUE
    WHERE (cashrcpt_id=pcashrcptid);
    RETURN 1;
  END IF;

  DELETE FROM cashrcptitem
  WHERE (cashrcptitem_cashrcpt_id=pcashrcptid);

  DELETE FROM cashrcptmisc
  WHERE (cashrcptmisc_cashrcpt_id=pcashrcptid);

  DELETE FROM cashrcpt
  WHERE (cashrcpt_id=pcashrcptid);

  RETURN 1;

END;
$_$;


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

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

CREATE FUNCTION deletecharacteristic(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
  pCharid ALIAS FOR $1;
  _uses   TEXT;

BEGIN

  IF NOT EXISTS(SELECT 1 FROM char WHERE char_id = pCharid) THEN
    RETURN 0;
  END IF;

  SELECT string_agg(DISTINCT(charass_target_type), ':') INTO _uses
    FROM charass
   WHERE charass_char_id = pCharid;

  IF _uses IS NOT NULL THEN
    RAISE EXCEPTION 'This characteristic cannot be deleted because it is in use [xtuple: deleteCharacteristic, -99, %]',
                    _uses;
  END IF;

  DELETE FROM "char" WHERE (char_id=pCharid);

  RETURN pCharid;

END;
$_$;


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

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

CREATE FUNCTION deletecheck(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
  pCheckid ALIAS FOR $1;

BEGIN
  IF (SELECT (NOT checkhead_void) OR checkhead_posted OR checkhead_replaced
              OR checkhead_deleted
              OR (checkhead_ach_batch IS NOT NULL AND checkhead_printed)
      FROM checkhead
      WHERE (checkhead_id=pCheckid) ) THEN
    RETURN -1;
  END IF;

  UPDATE checkhead
  SET checkhead_deleted=TRUE
  WHERE (checkhead_id=pCheckid);

  RETURN 1;

END;
$_$;


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

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

CREATE FUNCTION deleteclasscode(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
  pClasscodeid ALIAS FOR $1;
  _check INTEGER;

BEGIN

--  Check to see if any items are assigned to the passed classcode
  SELECT item_id INTO _check
  FROM item
  WHERE (item_classcode_id=pClasscodeid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

--  Delete the passed classcode
  DELETE FROM classcode
  WHERE (classcode_id=pClasscodeid);

  RETURN pClasscodeid;

END;
$_$;


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

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

CREATE FUNCTION deletecompany(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
  pcompanyid ALIAS FOR $1;

BEGIN
  IF (EXISTS(SELECT accnt_id
             FROM accnt, company
             WHERE ((accnt_company=company_number)
               AND  (company_id=pcompanyid))
            )) THEN
    RETURN -1;
  END IF;

  DELETE FROM company
  WHERE (company_id=pcompanyid);

  RETURN pcompanyid;

END;
$_$;


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

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

CREATE FUNCTION deletecreditmemo(integer) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pCmheadid ALIAS FOR $1;

BEGIN

  DELETE FROM cmitem
  WHERE (cmitem_cmhead_id=pCmheadid);

  DELETE FROM cmhead
  WHERE (cmhead_id=pCmheadid);

  RETURN TRUE;

END;
$_$;


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

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

CREATE FUNCTION deletecustomer(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
  pCustid ALIAS FOR $1;

BEGIN

  PERFORM shipto_id
  FROM shiptoinfo
  WHERE (shipto_cust_id=pCustid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

  PERFORM cohead_id
  FROM cohead
  WHERE (cohead_cust_id=pCustid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -2;
  END IF;

  PERFORM cmhead_id
  FROM cmhead
  WHERE (cmhead_cust_id=pCustid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -3;
  END IF;

  PERFORM cohist_id
  FROM cohist
  WHERE (cohist_cust_id=pCustid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -4;
  END IF;

  PERFORM aropen_id
  FROM aropen
  WHERE (aropen_cust_id=pCustid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -5;
  END IF;

  PERFORM checkhead_recip_id
    FROM checkhead
   WHERE ((checkhead_recip_id=pCustid)
     AND  (checkhead_recip_type='C'))
   LIMIT 1;
   IF (FOUND) THEN
     RETURN -6;
   END IF;

  PERFORM invchead_id
     FROM invchead
    WHERE(invchead_cust_id=pCustid)
    LIMIT 1;
  IF (FOUND) THEN
    RETURN -7;
  END IF;

  PERFORM quhead_id
     FROM quhead
    WHERE(quhead_cust_id=pCustid)
    LIMIT 1;
  IF (FOUND) THEN
    RETURN -8;
  END IF;

  DELETE FROM taxreg
   WHERE ((taxreg_rel_type='C')
     AND  (taxreg_rel_id=pCustid));

  DELETE FROM ipsass
  WHERE (ipsass_cust_id=pCustid);

  DELETE FROM custinfo
  WHERE (cust_id=pCustid);

  UPDATE crmacct SET crmacct_cust_id = NULL
  WHERE (crmacct_cust_id=pCustid);

  RETURN 0;

END;
$_$;


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

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

CREATE FUNCTION deletecustomertype(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
  pCusttypeid ALIAS FOR $1;

BEGIN

  IF EXISTS(SELECT 1
              FROM custinfo
             WHERE (cust_custtype_id=pCusttypeid)) THEN
    RETURN -1;
  END IF;

  DELETE FROM ipsass
  WHERE (ipsass_custtype_id=pCusttypeid);

  DELETE FROM salesaccnt
  WHERE (salesaccnt_custtype_id=pCusttypeid);

  DELETE FROM araccnt
  WHERE (araccnt_custtype_id=pCusttypeid);

  DELETE FROM custform
  WHERE (custform_custtype_id=pCusttypeid);

  DELETE FROM custtype
  WHERE (custtype_id=pCusttypeid);

  RETURN pCusttypeid;

END;
$_$;


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

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

CREATE FUNCTION deleteempgrp(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
  pempgrpid ALIAS FOR $1;

BEGIN
--  Check to see if any employees are assigned to the passed empgrp
  PERFORM empgrpitem_emp_id
  FROM empgrpitem
  WHERE (empgrpitem_empgrp_id=pempgrpid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

  DELETE FROM empgrp     WHERE (empgrp_id=pempgrpid);

  RETURN 0;
END;
$_$;


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

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

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

BEGIN

  FOR _r IN SELECT ipshead_id
    FROM ipshead
    WHERE (ipshead_expires <= current_date)
  LOOP

    DELETE FROM ipsass
      WHERE (ipsass_ipshead_id=_r.ipshead_id);
    DELETE FROM ipsiteminfo
      WHERE (ipsitem_ipshead_id=_r.ipshead_id);
    DELETE FROM ipsfreight
      WHERE (ipsfreight_ipshead_id=_r.ipshead_id);
    DELETE FROM ipshead
      WHERE (ipshead_id=_r.ipshead_id);
  END LOOP;

  RETURN TRUE;

END;
$$;


ALTER FUNCTION public.deleteexpiredips() OWNER TO admin;

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

CREATE FUNCTION deletefile(integer) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
declare
  pId ALIAS FOR $1;
begin
  delete from file
  where ( file_id in (
    select file_id
    from file
      join docass on (docass_target_id=file_id)
                 and (docass_target_type='FILE')
    where ( docass_id = pId ) ) );

  delete from docass where docass_id = pId;

  return true;
end;
$_$;


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

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

CREATE FUNCTION deleteflgrp(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
  pFlgrpid ALIAS FOR $1;
  _r RECORD;

BEGIN

  FOR _r IN SELECT flgrp_id
            FROM flgrp
            WHERE (flgrp_flgrp_id=pFlgrpid) LOOP
    PERFORM deleteFlgrp(_r.flgrp_id);
  END LOOP;

  DELETE FROM flitem
  WHERE (flitem_flgrp_id=pFlgrpid);

  DELETE FROM flspec
  WHERE (flspec_flgrp_id=pFlgrpid);

  DELETE FROM flgrp
  WHERE (flgrp_id=pFlgrpid);

  RETURN 1;

END;
$_$;


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

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

CREATE FUNCTION deleteform(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
  pFormid ALIAS FOR $1;
  _key TEXT;
  _check INTEGER;

BEGIN

--  Cache the key of the passed form
  SELECT form_key INTO _key
  FROM form
  WHERE (form_id=pFormid);
  IF (NOT(FOUND)) THEN
    RETURN 0;
  END IF;

--  Handle checks based on the type of the form
  IF (_key='Chck') THEN
    SELECT bankaccnt_id INTO _check
    FROM bankaccnt
    WHERE (bankaccnt_check_form_id=pFormid)
    LIMIT 1;
    IF (FOUND) THEN
      RETURN -1;
    END IF;

  END IF;

--  Delete the form
  DELETE FROM form
  WHERE (form_id=pFormid);

  RETURN pFormid;

END;
$_$;


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

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

CREATE FUNCTION deletefreightclass(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
  pFreightClassid ALIAS FOR $1;
  _check INTEGER;

BEGIN

--  Check to see if any items are assigned to the passed freightclass
  SELECT item_id INTO _check
  FROM item
  WHERE (item_freightclass_id=pFreightClassid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

--  Delete the passed freightclass
  DELETE FROM freightclass
  WHERE (freightclass_id=pFreightClassid);

  RETURN pFreightClassid;

END;
$_$;


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

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

CREATE FUNCTION deleteglseries(integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
  pSequence ALIAS FOR $1;

BEGIN

  DELETE FROM glseries
  WHERE (glseries_sequence=pSequence);

  RETURN pSequence;

END;
$_$;


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

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

CREATE FUNCTION deleteglseries(integer, text) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pSequence ALIAS FOR $1;
  pNotes ALIAS FOR $2;
  _trialbalid INTEGER;
  _count INTEGER;
  _r RECORD;

BEGIN

--  March through all of the G/L Transactions for the passed sequence
  FOR _r IN SELECT gltrans_id, gltrans_date, gltrans_accnt_id, gltrans_amount, gltrans_posted, gltrans_rec,
                   accnt_forwardupdate, period_id, period_closed, period_freeze
            FROM accnt, gltrans LEFT OUTER JOIN period ON (gltrans_date BETWEEN period_start AND period_end)
            WHERE ( (gltrans_accnt_id=accnt_id)
             AND (NOT gltrans_deleted)
             AND (gltrans_sequence=pSequence) ) LOOP

--  If we can post into a Trial Balance, do so
    IF ( (NOT _r.period_closed) AND 
       ( (NOT _r.period_freeze) OR (checkPrivilege('PostFrozenPeriod')) ) AND
       (  NOT _r.gltrans_rec) AND
       ( _r.gltrans_posted ) ) THEN

--  Try to find an existing trialbal
      SELECT trialbal_id INTO _trialbalid
      FROM trialbal
      WHERE ( (trialbal_period_id=_r.period_id)
       AND (trialbal_accnt_id=_r.gltrans_accnt_id) );

      GET DIAGNOSTICS _count = ROW_COUNT;
      IF (_count > 0) THEN

--  We found a trialbal, update it with the G/L Transaction
--  Note - two stage update to avoid any funny value caching logic
        IF (_r.gltrans_amount > 0) THEN
          UPDATE trialbal
          SET trialbal_credits = (trialbal_credits - _r.gltrans_amount)
          WHERE (trialbal_id=_trialbalid);
        ELSE
          UPDATE trialbal
          SET trialbal_debits = (trialbal_debits - (_r.gltrans_amount * -1))
          WHERE (trialbal_id=_trialbalid);
        END IF;

        UPDATE trialbal
        SET trialbal_ending = (trialbal_beginning - trialbal_debits + trialbal_credits),
            trialbal_dirty=TRUE
        WHERE (trialbal_id=_trialbalid);

      ELSE
        RAISE EXCEPTION 'Can not delete G/L Series.  Trial balance record not found.';
      END IF;

--  Forward update if we should
      IF (_r.accnt_forwardupdate AND fetchmetricbool('ManualForwardUpdate')) THEN
        PERFORM forwardUpdateTrialBalance(_trialbalid);
      END IF;

--  Delete any bank reconciliation records if this was marked cleared but non reconciled
    DELETE FROM bankrecitem
    WHERE ((bankrecitem_source='GL')
    AND (bankrecitem_source_id=_r.gltrans_id));

--  Unflag any journals as posted as a result of this series
    UPDATE sltrans SET
      sltrans_posted=false,
      sltrans_gltrans_journalnumber=null
    FROM gltrans
    WHERE ((gltrans_sequence=pSequence)
    AND (sltrans_gltrans_journalnumber=gltrans_journalnumber));
    
--  Mark the G/L Transaction as deleted
      UPDATE gltrans SET
        gltrans_posted=false,
        gltrans_deleted=true,
        gltrans_notes=gltrans_notes || E'\n' || pNotes
      WHERE (gltrans_id=_r.gltrans_id);

    ELSIF (_r.period_freeze) THEN
        RAISE EXCEPTION 'Can not delete a G/L Transaction in a frozen period';
    ELSIF (_r.period_closed) THEN
        RAISE EXCEPTION 'Can not delete a G/L Transaction on account % in a closed period', formatGlAccount(_r.gltrans_accnt_id);
    ELSIF (_r.gltrans_rec) THEN
        RAISE EXCEPTION 'Can not delete a G/L Transaction that has been reconciled';
    ELSIF (NOT _r.gltrans_posted) THEN
        RAISE EXCEPTION 'Can not delete a G/L Transaction that has not been posted to Trial Balance';
    END IF;

  END LOOP;

  RETURN true;

END;
$_$;


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

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

CREATE FUNCTION deleteincident(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
  pincdtid    ALIAS FOR $1;
  _count      INTEGER := 0;
  _incdtnbr   INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO _count
  FROM todoitem
  WHERE (todoitem_incdt_id=pincdtid);
  IF (_count > 0) THEN
    RETURN -1;
  END IF;

  DELETE FROM comment
   WHERE((comment_source='INCDT')
     AND (comment_source_id=pincdtid));

  DELETE FROM incdthist
   WHERE (incdthist_incdt_id=pincdtid);

  DELETE FROM imageass
  WHERE ((imageass_source='INCDT')
     AND (imageass_source_id=pincdtid));

  DELETE FROM url
  WHERE ((url_source='INCDT')
     AND (url_source_id=pincdtid));

  SELECT incdt_number INTO _incdtnbr
  FROM incdt
  WHERE (incdt_id=pincdtid);

  DELETE FROM incdt
    WHERE (incdt_id=pincdtid);

-- Incident #11538 needs to be fully resolved before release can be implemented
--    PERFORM releaseIncidentNumber(_incdtnbr);

  RETURN 0;
END;
$_$;


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

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

CREATE FUNCTION deleteinvoice(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
  pInvcheadid ALIAS FOR $1;
  _cobmiscid INTEGER := -1;
  _cobill RECORD;
  _cobillid INTEGER := -1;

BEGIN
  UPDATE shipitem SET shipitem_invoiced=FALSE, shipitem_invcitem_id=NULL
  FROM invcitem
  WHERE ((shipitem_invoiced)
    AND  (shipitem_invcitem_id=invcitem_id)
    AND  (invcitem_invchead_id=pInvcheadid));

  UPDATE coitem SET coitem_status = 'O'
  WHERE ((coitem_status = 'C')
    AND  (coitem_id IN (SELECT cobill_coitem_id
            FROM cobill, invcitem
      WHERE ((cobill_invcitem_id=invcitem_id)
        AND  (invcitem_invchead_id=pInvcheadid)))));

  UPDATE invdetail SET invdetail_invcitem_id=NULL
  FROM invcitem
  WHERE ((invdetail_invcitem_id=invcitem_id)
    AND  (invcitem_invchead_id=pInvcheadid));

  -- Check for unposted cobmisc for the same S/O
  -- If found then consolidate
  SELECT cobmisc_id INTO _cobmiscid
  FROM cobmisc
  WHERE ((NOT cobmisc_posted)
    AND  (cobmisc_cohead_id IN (SELECT coitem_cohead_id
                                FROM invcitem JOIN coitem ON (coitem_id=invcitem_coitem_id)
                                WHERE (invcitem_invchead_id=pInvcheadid))))
  LIMIT 1;
  IF (FOUND) THEN
    -- Check for uposted cobill for the same S/O Item
    -- If found then consolidate
    FOR _cobill IN SELECT cobill.*
                   FROM invcitem JOIN cobill ON (cobill_invcitem_id=invcitem_id)
                   WHERE (invcitem_invchead_id=pInvcheadid) LOOP
      SELECT cobill_id INTO _cobillid
      FROM cobill
      WHERE (cobill_cobmisc_id=_cobmiscid)
        AND (cobill_coitem_id=_cobill.cobill_coitem_id);
      IF (FOUND) THEN
        UPDATE cobill SET cobill_qty = cobill_qty + _cobill.cobill_qty
        WHERE (cobill_id=_cobillid);
        DELETE FROM cobill
        WHERE (cobill_id=_cobill.cobill_id);
      ELSE
        UPDATE cobill SET cobill_invcnum=NULL, cobill_invcitem_id=NULL, cobill_cobmisc_id=_cobmiscid
        WHERE (cobill_id=_cobill.cobill_id);
      END IF;
    END LOOP;
    DELETE FROM cobmisc
    WHERE (cobmisc_invchead_id=pInvcheadid);
  ELSE
    UPDATE cobill SET cobill_invcnum=NULL, cobill_invcitem_id=NULL
    FROM invcitem
    WHERE ((cobill_invcitem_id=invcitem_id)
      AND  (invcitem_invchead_id=pInvcheadid));

    UPDATE cobmisc SET cobmisc_invcnumber=NULL, cobmisc_invchead_id=NULL,
         cobmisc_posted=FALSE
    WHERE (cobmisc_invchead_id=pInvcheadid);
  END IF;

  DELETE FROM aropenalloc
  WHERE (aropenalloc_doctype='I')
    AND (aropenalloc_doc_id=pInvcheadid);

  DELETE FROM charass
  WHERE (charass_target_type='INV')
    AND (charass_target_id=pInvcheadid);

  DELETE FROM invcitem
  WHERE (invcitem_invchead_id=pInvcheadid);

  DELETE FROM invchead
  WHERE (invchead_id=pInvcheadid);

  RETURN pInvcheadid;

END;
$_$;


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

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

CREATE FUNCTION deleteipsitem(pipsitemid 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

BEGIN

  DELETE FROM ipsiteminfo WHERE ipsitem_id=pIpsItemId;

  RETURN 1;
END;
$$;


ALTER FUNCTION public.deleteipsitem(pipsitemid integer) OWNER TO admin;

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

CREATE FUNCTION deleteipsprodcat(pipsitemid 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

BEGIN

  DELETE FROM ipsiteminfo WHERE ipsitem_id=pIpsItemId;
  
  RETURN 1;
END;
$$;


ALTER FUNCTION public.deleteipsprodcat(pipsitemid integer) OWNER TO admin;

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

CREATE FUNCTION deleteitem(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;
  _result INTEGER;

BEGIN

  SELECT bomitem_id INTO _result
  FROM bomitem
  WHERE (bomitem_item_id=pItemid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

  SELECT itemsite_id INTO _result
  FROM itemsite
  WHERE (itemsite_item_id=pItemid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -2;
  END IF;

  SELECT itemsub_id INTO _result
  FROM itemsub
  WHERE (itemsub_sub_item_id=pItemid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -3;
  END IF;

  IF (fetchmetricbool('RevControl')) THEN
    SELECT rev_id INTO _result
    FROM rev
    WHERE ((rev_target_id=pItemid)
    AND (rev_target_type = 'BOM'))
    LIMIT 1;
    IF (FOUND) THEN
      RETURN -6;
    END IF;
  END IF;

  DELETE FROM bomhead
  WHERE (bomhead_item_id=pItemid);
  DELETE FROM bomitem
  WHERE (bomitem_item_id=pItemid);

  DELETE FROM itemcost
  WHERE (itemcost_item_id=pItemid);
  DELETE FROM costhist
  WHERE (costhist_item_id=pItemid);

  DELETE FROM itemsub
  WHERE (itemsub_parent_item_id=pItemid);
  DELETE FROM itemsub
  WHERE (itemsub_sub_item_id=pItemid);

  DELETE FROM itemsrcp
  WHERE (itemsrcp_itemsrc_id IN (SELECT itemsrc_id FROM itemsrc WHERE (itemsrc_item_id=pItemid)));
  DELETE FROM itemsrc
  WHERE (itemsrc_item_id=pItemid);

  DELETE FROM itemalias
  WHERE (itemalias_item_id=pItemid);

  DELETE FROM itemgrpitem
  WHERE (itemgrpitem_item_id=pItemid);

  DELETE FROM ipsiteminfo
  WHERE (ipsitem_item_id=pItemid);

  DELETE FROM imageass
  WHERE ( (imageass_source='I')
    AND   (imageass_source_id=pItemid) );

  DELETE FROM locitem
  WHERE (locitem_item_id=pItemid);

  DELETE FROM itemtax
   WHERE(itemtax_item_id=pItemid);

  DELETE FROM itemsite
  WHERE (itemsite_item_id=pItemid);

  DELETE FROM itemuom
   WHERE(itemuom_itemuomconv_id IN (SELECT itemuomconv_id
                                      FROM itemuomconv
                                     WHERE(itemuomconv_item_id=pItemid)));

  DELETE FROM itemuomconv
   WHERE(itemuomconv_item_id=pItemid);

  DELETE FROM item
  WHERE (item_id=pItemid);

  RETURN 0;

END;
$_$;


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

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

CREATE FUNCTION deleteitemcost(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;
  pCostElemId ALIAS FOR $2;
  _itemcost_id INTEGER;
  _postcost_return BOOLEAN;
  _std_cost NUMERIC;

BEGIN
  SELECT itemcost_id INTO _itemcost_id
  FROM itemcost
  WHERE ( (itemcost_item_id = pItemId) AND (itemcost_costelem_id = pCostElemId) );

  IF (NOT FOUND) THEN
	RAISE EXCEPTION 'itemcost % not found for. ', pItemId || ' & ' || pCostElemId;
  END IF;

  SELECT itemcost_stdcost INTO _std_cost
  FROM itemcost
  WHERE (itemcost_id = _itemcost_id);

  IF (_std_cost > 0) THEN
--Actual Cost is updated to zero to ensure inventory is valued correctly
    PERFORM updateCost(_itemcost_id, 0);
  END IF;

  DELETE FROM itemcost
  WHERE (itemcost_id=_itemcost_id);
 
  RETURN _itemcost_id;	

END;
$_$;


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

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

CREATE FUNCTION deleteitemsite(pitemsiteid 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
  _result INTEGER;
  _lotserial BOOLEAN;
  _bbom BOOLEAN;
  _mfg BOOLEAN;
  _standard BOOLEAN;

BEGIN

  IF ( ( SELECT (itemsite_qtyonhand <> 0)
         FROM itemsite
         WHERE (itemsite_id=pItemsiteid) ) ) THEN
    RETURN -9;
  END IF;

  SELECT metric_value='t' INTO _bbom
    FROM metric
   WHERE (metric_name='BBOM');

  SELECT metric_value='t' INTO _lotserial
    FROM metric
   WHERE (metric_name='LotSerialControl');

  SELECT metric_value NOT IN ('PostBooks', 'Standard') INTO _mfg
    FROM metric
   WHERE (metric_name='Application');

  SELECT metric_value='Standard' INTO _standard
    FROM metric
   WHERE (metric_name='Application');

  SELECT invhist_id INTO _result
  FROM invhist
  WHERE (invhist_itemsite_id=pItemsiteid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

  IF (_lotserial) THEN
    SELECT lsdetail_id INTO _result
    FROM lsdetail
    WHERE (lsdetail_itemsite_id=pItemsiteid)
    LIMIT 1;
    IF (FOUND) THEN
      RETURN -1;
    END IF;
  END IF;

  SELECT wo_id INTO _result
  FROM wo
  WHERE (wo_itemsite_id=pItemsiteid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -2;
  END IF;

  SELECT womatl_id INTO _result
  FROM womatl
  WHERE (womatl_itemsite_id=pItemsiteid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -2;
  END IF;

  SELECT womatlvar_id INTO _result
  FROM womatlvar
  WHERE ( (womatlvar_parent_itemsite_id=pItemsiteid)
   OR (womatlvar_component_itemsite_id=pItemsiteid) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -2;
  END IF;

  IF (_bbom) THEN
    SELECT brdvar_id INTO _result
    FROM xtmfg.brdvar
    WHERE ( (brdvar_itemsite_id=pItemsiteid)
     OR (brdvar_parent_itemsite_id=pItemsiteid) )
    LIMIT 1;
    IF (FOUND) THEN
      RETURN -2;
    END IF;
  END IF;

  SELECT coitem_id INTO _result
  FROM coitem
  WHERE (coitem_itemsite_id=pItemsiteid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -3;
  END IF;

  SELECT cohist_id INTO _result
  FROM cohist
  WHERE (cohist_itemsite_id=pItemsiteid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -3;
  END IF;

  SELECT quitem_id INTO _result
  FROM quitem
  WHERE (quitem_itemsite_id=pItemsiteid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -3;
  END IF;

  SELECT cmitem_id INTO _result
  FROM cmitem
  WHERE (cmitem_itemsite_id=pItemsiteid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -3;
  END IF;


  SELECT poitem_id INTO _result
  FROM poitem
  WHERE (poitem_itemsite_id=pItemsiteid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -4;
  END IF;

  SELECT recv_id INTO _result
  FROM recv
  WHERE (recv_itemsite_id=pItemsiteid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -4;
  END IF;

  SELECT poreject_id INTO _result
  FROM poreject
  WHERE (poreject_itemsite_id=pItemsiteid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -4;
  END IF;

  SELECT pr_id INTO _result
  FROM pr
  WHERE (pr_itemsite_id=pItemsiteid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -4;
  END IF;

  IF (_mfg OR _standard) THEN
    SELECT planord_id INTO _result
    FROM planord
    WHERE (planord_itemsite_id=pItemsiteid)
    LIMIT 1;
    IF (FOUND) THEN
      RETURN -5;
    END IF;
  END IF;

  IF (_mfg) THEN
    SELECT pschitem_id INTO _result
    FROM xtmfg.pschitem
    WHERE (pschitem_itemsite_id=pItemsiteid)
    LIMIT 1;
    IF (FOUND) THEN
      RETURN -6;
    END IF;

    SELECT woopervar_id INTO _result
    FROM xtmfg.woopervar
    WHERE (woopervar_parent_itemsite_id=pItemsiteid)
    LIMIT 1;
    IF (FOUND) THEN
      RETURN -2;
    END IF;
    
  END IF;

  IF (_mfg OR _standard) THEN
    SELECT itemsite_id INTO _result
    FROM itemsite
    WHERE (itemsite_supply_itemsite_id=pItemsiteid)
    LIMIT 1;
    IF (FOUND) THEN
      RETURN -7;
    END IF;
  END IF;

  DELETE FROM invcnt
  WHERE (invcnt_itemsite_id=pItemsiteid);

  DELETE FROM itemloc
  WHERE (itemloc_itemsite_id=pItemsiteid);
  DELETE FROM itemlocdist
  WHERE (itemlocdist_itemsite_id=pItemsiteid);

  IF (_bbom) THEN
    DELETE FROM xtmfg.brddist
    WHERE (brddist_itemsite_id=pItemsiteid);
  END IF;

  DELETE FROM itemsite
  WHERE (itemsite_id=pItemsiteid);

  RETURN 0;

END;
$$;


ALTER FUNCTION public.deleteitemsite(pitemsiteid integer) OWNER TO admin;

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

CREATE FUNCTION deleteitemuom(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
  pItemuomid ALIAS FOR $1;

BEGIN
  DELETE FROM itemuom WHERE itemuom_id=pItemuomid;

  RETURN 0;
END;
$_$;


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

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

CREATE FUNCTION deleteitemuomconv(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
  pItemuomconvid ALIAS FOR $1;
  _fromuomid     INTEGER;
  _invuomid      INTEGER;
  _itemid        INTEGER;
  _touomid       INTEGER;

BEGIN
  SELECT itemuomconv_item_id, item_inv_uom_id,
         itemuomconv_from_uom_id, itemuomconv_to_uom_id
          INTO _itemid, _invuomid, _fromuomid, _touomid
  FROM itemuomconv JOIN item ON (itemuomconv_item_id=item_id)
  WHERE (itemuomconv_id=pItemuomconvid);

  IF EXISTS(SELECT *
            FROM uomusedforitem(_itemid)
            WHERE ((uom_id IN (_fromuomid, _touomid))
               AND (uom_id != _invuomid)) ) THEN
    RETURN -1;
  END IF;

  DELETE FROM itemuom WHERE itemuom_itemuomconv_id=pItemuomconvid;
  DELETE FROM itemuomconv WHERE itemuomconv_id=pItemuomconvid;

  RETURN 0;
END;
$_$;


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

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

CREATE FUNCTION deletelocation(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
  pLocationid ALIAS FOR $1;
  _check INTEGER;

BEGIN

--  Check to see if any itemsite used the passed location as their default
  SELECT itemsite_id INTO _check
  FROM itemsite
  WHERE (itemsite_location_id=pLocationid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

--  Check to see if any inventory is currently stored at the passed location
  SELECT itemloc_id INTO _check
  FROM itemloc
  WHERE (itemloc_location_id=pLocationid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -2;
  END IF;

--  Check to see if any undistributed inventory transactions are currently posted at the passed location
  SELECT itemlocdist_id INTO _check
  FROM itemlocdist
  WHERE ( (itemlocdist_source_type='L')
  AND (itemlocdist_source_id=pLocationid) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -3;
  END IF;

--  Check to see if the passed location has any Inventory Detail posted against it
  SELECT invdetail_id INTO _check
  FROM invdetail
  WHERE (invdetail_location_id=pLocationid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -4;
  END IF;

--  Delete any associated locitem records
  DELETE FROM locitem
  WHERE (locitem_location_id=pLocationid);

--  Delete the location record
  DELETE FROM location
  WHERE (location_id=pLocationid);

  RETURN pLocationid;

END;
$_$;


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

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

CREATE FUNCTION deletemetasql(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
  pid ALIAS FOR $1;
BEGIN
  DELETE FROM metasql WHERE metasql_id = pid;
  RETURN 0;
END;
$_$;


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

--
-- Name: deleteopenrecurringitems(integer, text, timestamp with time zone, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deleteopenrecurringitems(integer, text, timestamp with time zone, boolean) 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
  pParentid     ALIAS FOR $1;
  pType         TEXT                     := UPPER($2);
  pDatetime     TIMESTAMP WITH TIME ZONE := COALESCE($3, startOfTime());
  pInclParent   BOOLEAN                  := COALESCE($4, FALSE);

  _count         INTEGER := 0;
  _delchildstmt  TEXT;
  _delparentstmt TEXT;
  _rt            RECORD;
  _tmp           INTEGER;
BEGIN
  RAISE DEBUG 'deleteOpenRecurringItems(%, %, %)', pParentid, pType, pDatetime;

  IF (pParentid IS NULL) THEN
    RETURN -11;
  END IF;

  SELECT * INTO _rt FROM recurtype WHERE (UPPER(recurtype_type)=pType);
  GET DIAGNOSTICS _count = ROW_COUNT;
  IF (_count <= 0) THEN
    RETURN -10;
  END IF;

  -- 2 deletes avoid reparenting problems if the parent gets deleted first
  IF (_rt.recurtype_delfunc IS NULL) THEN
    _delchildstmt := 'DELETE FROM [fulltable] '
                  || ' WHERE (NOT ([done])'
                  || '    AND ([schedcol]>''$2'')'
                  || '    AND ([table]_recurring_[table]_id=$1)'
                  || '    AND ([table]_id!=$1));';

    _delparentstmt := 'DELETE FROM [fulltable] USING recur'
                   || ' WHERE (NOT ([done])'
                   || '    AND ([schedcol]>''$2'')'
                   || '    AND ([table]_recurring_[table]_id=$1)'
                   || '    AND ([table]_id=$1));';

  ELSE
    _delchildstmt := 'SELECT [delfunc]([table]_id)'
                  || '  FROM [fulltable] '
                  || ' WHERE (NOT ([done])'
                  || '    AND ([schedcol]>''$2'')'
                  || '    AND ([table]_recurring_[table]_id=$1)'
                  || '    AND ([table]_id!=$1));';
    _delparentstmt := 'SELECT [delfunc]([table]_id)'
                   || '  FROM [fulltable] '
                   || ' WHERE (NOT ([done])'
                   || '    AND ([schedcol]>''$2'')'
                   || '    AND ([table]_recurring_[table]_id=$1)'
                   || '    AND ([table]_id!=$1));';
    _delchildstmt  := REPLACE(_delchildstmt,  '[delfunc]', _rt.recurtype_delfunc);
    _delparentstmt := REPLACE(_delparentstmt, '[delfunc]', _rt.recurtype_delfunc);
  END IF;

  RAISE DEBUG '_delchildstmt has been set to %', _delchildstmt;

  _delchildstmt := REPLACE(_delchildstmt, '[fulltable]', _rt.recurtype_table);
  _delchildstmt := REPLACE(_delchildstmt, '[table]',
                            REGEXP_REPLACE(_rt.recurtype_table, E'.*\\.', ''));
  _delchildstmt := REPLACE(_delchildstmt, '[done]',  _rt.recurtype_donecheck);
  _delchildstmt := REPLACE(_delchildstmt, '[schedcol]', _rt.recurtype_schedcol);

  _delparentstmt := REPLACE(_delparentstmt, '[fulltable]', _rt.recurtype_table);
  _delparentstmt := REPLACE(_delparentstmt, '[table]',
                            REGEXP_REPLACE(_rt.recurtype_table, E'.*\\.', ''));
  _delparentstmt := REPLACE(_delparentstmt, '[done]',  _rt.recurtype_donecheck);
  _delparentstmt := REPLACE(_delparentstmt, '[schedcol]', _rt.recurtype_schedcol);

  RAISE DEBUG 'substitutions changed _delchildstmt to %', _delchildstmt;

  IF (_rt.recurtype_delfunc IS NULL) THEN
    -- 8.4+: EXECUTE _delchildstmt  USING pDatetime, pType;
    RAISE DEBUG '% with % and %', _delchildstmt, pType, pDatetime;
    EXECUTE REPLACE(REPLACE(_delchildstmt, '$1', pParentid::TEXT),
                                           '$2', pDatetime::TEXT);
    GET DIAGNOSTICS _count = ROW_COUNT;

    IF (pInclParent) THEN
      -- 8.4+: EXECUTE _delparentstmt USING pDatetime, pType;
      RAISE DEBUG '% with % and %', _delparentstmt, pType, pDatetime;
      EXECUTE REPLACE(REPLACE(_delparentstmt, '$1', pParentid::TEXT),
                                              '$2', pDatetime::TEXT);
      GET DIAGNOSTICS _tmp   = ROW_COUNT;
      _count := _count + _tmp;
    END IF;

  ELSE
    -- 8.4+: FOR _tmp IN EXECUTE _delchildstmt USING pDatetime, pType LOOP
    FOR _tmp IN EXECUTE REPLACE(REPLACE(_delchildstmt, '$1', pParentid::TEXT),
                                                       '$2', pDatetime::TEXT)
    LOOP
      IF _tmp < 0 THEN
        RETURN _tmp;
      END IF;
      _count := _count + 1;
    END LOOP;

    IF (pInclParent) THEN
      -- 8.4+: EXECUTE _delparentstmt INTO _tmp USING pDatetime, pType;
      EXECUTE REPLACE(REPLACE(_delparentstmt, '$1', pParentid::TEXT),
                                              '$2', pDatetime::TEXT) INTO _tmp;
      IF (_tmp < 0) THEN
        RETURN _tmp;
      END IF;
      _count := _count + 1;
    END IF;
  END IF;

  RAISE DEBUG 'deleteOpenrecurringItems() returning %', _count;
  RETURN _count;
END;
$_$;


ALTER FUNCTION public.deleteopenrecurringitems(integer, text, timestamp with time zone, boolean) OWNER TO admin;

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

CREATE FUNCTION deleteopportunity(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
  pOpheadid ALIAS FOR $1;
  _test INTEGER;
BEGIN

  SELECT todoitem_id INTO _test
    FROM todoitem
   WHERE(todoitem_ophead_id=pOpheadid)
   LIMIT 1;
  IF(FOUND) THEN
    RETURN -1;
  END IF;

  SELECT quhead_id INTO _test
    FROM quhead
   WHERE(quhead_ophead_id=pOpheadid)
   LIMIT 1;
  IF(FOUND) THEN
    RETURN -2;
  END IF;

  SELECT cohead_id INTO _test
    FROM cohead
   WHERE(cohead_ophead_id=pOpheadid)
   LIMIT 1;
  IF(FOUND) THEN
    RETURN -3;
  END IF;

  DELETE
    FROM charass
   WHERE((charass_target_type='OPP')
     AND (charass_target_id=pOpheadid));

  DELETE
    FROM comment
   WHERE((comment_source='OPP')
     AND (comment_source_id=pOpheadid));

  DELETE
    FROM ophead
   WHERE(ophead_id=pOpheadid);
  
  return 0;
END;
$_$;


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

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

CREATE FUNCTION deletepackage(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
  ppkgheadid    ALIAS FOR $1;
  _i            INTEGER := 0;
  _pkgname      TEXT;
  _r            RECORD;
  _tabs         TEXT[] := ARRAY['cmd',  'cmdarg', 'image',  'metasql',
                                'priv', 'report', 'script', 'uiform'];
  _debug        BOOL := false;

BEGIN
  IF (EXISTS(SELECT *
             FROM pkgdep
             WHERE (pkgdep_parent_pkghead_id=ppkgheadid))) THEN
    RETURN -1;
  END IF;

  SELECT pkghead_name INTO _pkgname
  FROM pkghead
  WHERE (pkghead_id=ppkgheadid);
  IF (NOT FOUND) THEN
    RETURN -2;
  END IF;

  IF (LOWER(_pkgname) = 'public' OR LOWER(_pkgname) = 'api') THEN
    RETURN -3;
  END IF;

  FOR _i IN ARRAY_LOWER(_tabs,1)..ARRAY_UPPER(_tabs,1) LOOP
    EXECUTE 'ALTER TABLE ' || _pkgname || '.pkg' || _tabs[_i] ||
            ' DISABLE TRIGGER pkg' || _tabs[_i] || 'altertrigger;';
  END LOOP;

  DELETE FROM pkghead WHERE pkghead_id=ppkgheadid;

  RETURN ppkgheadid;
END;
$_$;


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

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

CREATE FUNCTION deletepo(integer) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pPoheadid ALIAS FOR $1;
  _poitemid INTEGER;

BEGIN

  IF ( ( SELECT pohead_status
         FROM pohead
         WHERE (pohead_id=pPoheadid) ) = 'U' ) THEN

    -- Unlink from any Sales Orders
    UPDATE coitem SET coitem_order_type=NULL,
                      coitem_order_id=NULL
    FROM poitem 
    WHERE ( (coitem_order_type='P')
      AND   (coitem_order_id=poitem_id)
      AND   (poitem_pohead_id=pPoheadid) );

    DELETE FROM poitem
    WHERE (poitem_pohead_id=pPoheadid);

    DELETE FROM pohead
    WHERE (pohead_id=pPoheadid);

    RETURN TRUE;

  ELSE
    RETURN FALSE;
  END IF;

END;
$_$;


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

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

CREATE FUNCTION deletepoitem(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
  pPoitemid ALIAS FOR $1;
  _poheadid INTEGER := -1;
  _status CHARACTER;

BEGIN
  SELECT poitem_pohead_id, poitem_status INTO _poheadid, _status
  FROM poitem
  WHERE (poitem_id=pPoitemid);

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

  IF ( _status = 'U' ) THEN
    DELETE FROM poitem
    WHERE (poitem_id=pPoitemid);
  ELSE   
    IF ( _status = 'O' ) THEN
      PERFORM recv_id
      FROM recv
      WHERE ( (recv_order_type='PO')
       AND (recv_orderitem_id=pPoitemid) );
      IF (FOUND) THEN
        RETURN -10;
      ELSE
        RETURN -20;
      END IF;
    ELSE
      RETURN -10;
    END IF;
  END IF;

  PERFORM poitem_id
  FROM poitem
  WHERE poitem_pohead_id = _poheadid;

  IF NOT(FOUND) THEN
    DELETE FROM pohead
    WHERE (pohead_id = _poheadid);
  END IF;
  
  RETURN 0;

END;
$_$;


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

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

CREATE FUNCTION deletepr(integer) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pPrid ALIAS FOR $1;

BEGIN

  DELETE FROM pr
  WHERE ( (pr_status='O')
   AND (pr_id=pPrid) );

  RETURN TRUE;

END;
$_$;


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

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

CREATE FUNCTION deletepr(character, integer) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pParentType ALIAS FOR $1;
  pParentId ALIAS FOR $2;

BEGIN

  DELETE FROM pr
  WHERE ((pr_status='O')
   AND (pr_order_type=pParentType)
   AND (pr_order_id=pParentId));

  RETURN TRUE;

END;
$_$;


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

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

CREATE FUNCTION deleteproductcategory(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
  pProdcatid ALIAS FOR $1;
  _check INTEGER;

BEGIN

--  Check to see if any items are assigned to the passed classcode
  SELECT item_id INTO _check
  FROM item
  WHERE (item_prodcat_id=pProdcatid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

--  Delete any assocated records
  DELETE FROM salesaccnt
  WHERE (salesaccnt_prodcat_id=pProdcatid);

--  Delete the passed prodcat
  DELETE FROM prodcat
  WHERE (prodcat_id=pProdcatid);

  RETURN pProdcatid;

END;
$_$;


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

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

CREATE FUNCTION deleteprofitcenter(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
  pid ALIAS FOR $1;

BEGIN
  IF (EXISTS(SELECT accnt_id
             FROM accnt, prftcntr
             WHERE ((accnt_company=prftcntr_number)
               AND  (prftcntr_id=pid))
            )) THEN
    RETURN -1;
  END IF;

  DELETE FROM prftcntr
  WHERE (prftcntr_id=pid);

  RETURN pid;

END;
$_$;


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

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

CREATE FUNCTION deleteproject(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
  pPrjid ALIAS FOR $1;
  _result INTEGER;
BEGIN

  SELECT quhead_id INTO _result
    FROM quhead
   WHERE (quhead_prj_id=pPrjid)
   LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

  SELECT cohead_id INTO _result
    FROM cohead
   WHERE (cohead_prj_id=pPrjid)
   LIMIT 1;
  IF (FOUND) THEN
    RETURN -2;
  END IF;

  SELECT wo_id INTO _result
    FROM wo
   WHERE (wo_prj_id=pPrjid)
   LIMIT 1;
  IF (FOUND) THEN
    RETURN -3;
  END IF;

  SELECT pr_id INTO _result
    FROM pr
   WHERE (pr_prj_id=pPrjid)
   LIMIT 1;
  IF (FOUND) THEN
    RETURN -4;
  END IF;

  SELECT poitem_id INTO _result
    FROM poitem
   WHERE (poitem_prj_id=pPrjid)
   LIMIT 1;
  IF (FOUND) THEN
    RETURN -5;
  END IF;

  SELECT invchead_id INTO _result
    FROM invchead
   WHERE (invchead_prj_id=pPrjid)
   LIMIT 1;
  IF (FOUND) THEN
    RETURN -6;
  END IF;

  DELETE FROM comment
  WHERE ((comment_source='J')
  AND (comment_source_id=pPrjid));

  DELETE FROM comment
  WHERE ((comment_source='TA')
  AND (comment_source_id IN (
    SELECT prjtask_id
    FROM prjtask
    WHERE (prjtask_prj_id=pPrjId))));

  DELETE FROM prjtask
   WHERE (prjtask_prj_id=pPrjid);

  UPDATE prj
     SET prj_recurring_prj_id=null
   WHERE(prj_recurring_prj_id=pPrjid);

  DELETE FROM prj
   WHERE (prj_id=pPrjid);
  RETURN pPrjid;
END;
$_$;


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

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

CREATE FUNCTION deleteprojecttask(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
  pPrjtaskid ALIAS FOR $1;
  _row RECORD;
  _result INTEGER;
BEGIN

  SELECT * INTO _row
    FROM prjtask
   WHERE (prjtask_id=pPrjtaskid)
   LIMIT 1;
  IF (NOT FOUND) THEN
    RETURN -1;
  END IF;

  IF (COALESCE(_row.prjtask_hours_actual, 0.0) > 0.0) THEN
    RETURN -2;
  END IF;

  IF (COALESCE(_row.prjtask_exp_actual, 0.0) > 0.0) THEN
    RETURN -3;
  END IF;

  DELETE FROM comment
  WHERE ((comment_source='TA')
  AND (comment_source_id=pPrjtaskid));

  DELETE FROM prjtask
   WHERE (prjtask_id=pPrjtaskid);

  RETURN 0;

END;
$_$;


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

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

CREATE FUNCTION deleteqryhead(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
  pqryheadid    ALIAS FOR $1;

BEGIN
  DELETE FROM qryitem WHERE (qryitem_qryhead_id=pqryheadid);
  DELETE FROM qryhead WHERE (qryhead_id=pqryheadid);

  RETURN pqryheadid;
END;
$_$;


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

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

CREATE FUNCTION deletequote(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
  pQuheadid ALIAS FOR $1;
BEGIN
  RETURN deleteQuote(pQuheadid, NULL::TEXT);
END;
$_$;


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

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

CREATE FUNCTION deletequote(integer, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pQuheadid	ALIAS FOR $1;
  pQuoteNumber	ALIAS FOR $2;

  _quNumberScheme	TEXT;
  _quoteNumber		TEXT;
  _quitemid             INTEGER;
  _result               INTEGER;

BEGIN

  SELECT fetchMetricText('QUNumberGeneration') INTO _quNumberScheme;

  IF (pQuoteNumber IS NULL) THEN
    SELECT quhead_number INTO _quoteNumber
    FROM quhead
    WHERE (quhead_id=pQuheadid);
  ELSE
    _quoteNumber := pQuoteNumber;
  END IF;

  PERFORM deleteQuoteItem(quitem_id)
  FROM quitem
  WHERE (quitem_quhead_id=pQuheadid);

  DELETE FROM charass
  WHERE (charass_target_type='QU')
    AND (charass_target_id=pQuheadid);

  DELETE FROM quhead
  WHERE (quhead_id=pQuheadid);

  IF (_quoteNumber IS NOT NULL) THEN
    IF (_quNumberScheme IN ('A', 'O')) THEN
      -- do not release quote # if quote converted to sales order
      IF (NOT EXISTS (SELECT cohead_id
		      FROM cohead
		      WHERE (cohead_number=_quoteNumber))) THEN
	_result = releaseQuNumber(_quoteNumber);
      END IF;
    ELSEIF (_quNumberScheme = 'S') THEN
      _result = releaseSoNumber(_quoteNumber);
    END IF;
  END IF;

  -- Don't care about result of release number
  RETURN 0;

END;
$_$;


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

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

CREATE FUNCTION deletequote(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
  pQuheadid ALIAS FOR $1;
  pQuoteNumber	ALIAS FOR $2;
BEGIN
  RETURN deleteQuote(pQuheadid, pQuoteNumber::TEXT);
END;
$_$;


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

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

CREATE FUNCTION deletequoteitem(integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
  pQuitemid ALIAS FOR $1;

  _result               INTEGER;

BEGIN

  DELETE FROM charass
  WHERE (charass_target_type='QI')
    AND (charass_target_id=pQuitemid);

  DELETE FROM quitem
  WHERE (quitem_id=pQuitemid);

  RETURN 0;

END;
$_$;


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

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

CREATE FUNCTION deleterecvfororder(text, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pordertype	ALIAS FOR $1;
  porderid	ALIAS FOR $2;

BEGIN
  DELETE FROM recv
  USING orderitem
  WHERE ((recv_orderitem_id=orderitem_id)
    AND  (recv_order_type=orderitem_orderhead_type)
    AND  (NOT recv_posted)
    AND  (orderitem_orderhead_id=porderid)
    AND  (orderitem_orderhead_type=pordertype));

  RETURN 0;

END;
$_$;


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

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

CREATE FUNCTION deletesalescategory(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
  pSalescatid ALIAS FOR $1;

BEGIN

  PERFORM invcitem_salescat_id
  FROM invchead, invcitem
  WHERE ( (invcitem_invchead_id=invchead_id)
   AND (NOT invchead_posted)
   AND (invcitem_salescat_id=pSalescatid) );
  IF (FOUND) THEN
    RETURN -1;
  END IF;

  PERFORM invcitem_salescat_id
  FROM invchead, invcitem
  WHERE ( (invcitem_invchead_id=invchead_id)
   AND (invchead_posted)
   AND (invcitem_salescat_id=pSalescatid) );
  IF (FOUND) THEN
    RETURN -2;
  END IF;

  PERFORM aropen_salescat_id
     FROM aropen
    WHERE (aropen_salescat_id=pSalescatid);
  IF (FOUND) THEN
    RETURN -3;
  END IF;

  DELETE FROM salescat
  WHERE (salescat_id=pSalescatid);

  RETURN 0;

END;
$_$;


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

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

CREATE FUNCTION deleteshippingcharge(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
  pShipchrgid ALIAS FOR $1;

BEGIN

  IF EXISTS(SELECT 1
              FROM custinfo
             WHERE (cust_shipchrg_id=pShipchrgid)) THEN
    RETURN -1;
  END IF;

  DELETE FROM shipchrg
  WHERE (shipchrg_id=pShipchrgid);

  RETURN pShipchrgid;

END;
$_$;


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

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

CREATE FUNCTION deleteshippingchargetype(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
  pShipchrgid ALIAS FOR $1;
  _check INTEGER;

BEGIN

--  Check to see if the passed shipchrg is used as a default for any customers
  SELECT cust_id INTO _check
  FROM custinfo
  WHERE (cust_shipchrg_id=pShipchrgid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

--  Check to see if the passed shipchrg is used as a default for any shiptos
  SELECT shipto_id INTO _check
  FROM shiptoinfo
  WHERE (shipto_shipchrg_id=pShipchrgid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -2;
  END IF;

--  Check to see if the passed shipchrg is used on any sales orders
  SELECT cohead_id INTO _check
  FROM cohead
  WHERE (cohead_shipchrg_id=pShipchrgid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -3;
  END IF;

--  Check to see if the passed shipchrg is used on any shippers
  SELECT shiphead_id INTO _check
  FROM shiphead
  WHERE (shiphead_shipchrg_id=pShipchrgid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -4;
  END IF;

--  Check to see if the passed shipchrg is used on any invoices
  SELECT invchead_id INTO _check
  FROM invchead
  WHERE (invchead_shipchrg_id=pShipchrgid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -5;
  END IF;

--  Delete the passed shipchrg
  DELETE FROM shipchrg
  WHERE (shipchrg_id=pShipchrgid);

  RETURN pShipchrgid;

END;
$_$;


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

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

CREATE FUNCTION deleteshipto(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
  pShiptoid ALIAS FOR $1;

BEGIN

  PERFORM asohist_id
  FROM asohist
  WHERE (asohist_shipto_id=pShiptoid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

  PERFORM cohead_id
  FROM cohead
  WHERE (cohead_shipto_id=pShiptoid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -2;
  END IF;

  PERFORM cmhead_id
  FROM cmhead
  WHERE (cmhead_shipto_id=pShiptoid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -3;
  END IF;

  PERFORM cohist_id
  FROM cohist
  WHERE (cohist_shipto_id=pShiptoid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -4;
  END IF;

  PERFORM quhead_id
  FROM quhead
  WHERE (quhead_shipto_id=pShiptoid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -5;
  END IF;

  PERFORM invchead_id
  FROM invchead
  WHERE (invchead_shipto_id=pShiptoid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -6;
  END IF;

  DELETE FROM ipsass
  WHERE (ipsass_shipto_id=pShiptoid);

  DELETE FROM shiptoinfo
  WHERE (shipto_id=pShiptoid);

  RETURN 0;

END;
$_$;


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

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

CREATE FUNCTION deleteso(integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2015 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pSoheadid    ALIAS FOR $1;
BEGIN
  RETURN deleteSo(pSoheadid, NULL);
END;
$_$;


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

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

CREATE FUNCTION deleteso(integer, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2015 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pSoheadid	ALIAS FOR $1;
  pSonumber	ALIAS FOR $2;

  _r            RECORD;
  _coitemid     INTEGER;
  _result       INTEGER;
  _poStatus     INTEGER := 0;

BEGIN

-- Get cohead
  SELECT * INTO _r FROM cohead WHERE (cohead_id=pSoheadid);

   IF (NOT FOUND) THEN
     IF (COALESCE(pSonumber,'') != '') THEN
       _result := releaseSoNumber(pSonumber);
     END IF;  
     RETURN 0;
   END IF;

-- Cannot delete if credit card payments
  IF (EXISTS(SELECT ccpay_id
	     FROM ccpay, payco
	     WHERE ((ccpay_status IN ('C'))
	       AND  (ccpay_id=payco_ccpay_id)
	       AND  (payco_cohead_id=pSoheadid)))) THEN
    RETURN -1;
  END IF;

-- Cannot delete if credit card history
  IF (EXISTS(SELECT ccpay_id
	     FROM ccpay, payco
	     WHERE ((ccpay_status != 'C')
	       AND  (ccpay_id=payco_ccpay_id)
	       AND  (payco_cohead_id=pSoheadid)))) THEN
    RETURN -2;
  END IF;

-- Delete Sales Order Items
  FOR _coitemid IN
    SELECT coitem_id
    FROM coitem
    WHERE ( (coitem_cohead_id=pSoheadid)
      AND   (coitem_subnumber=0) ) LOOP

    SELECT deleteSoItem(_coitemid) INTO _result;
    IF (_result < 0) THEN
      IF (_result = -20) THEN
        _poStatus := _poStatus - 1;
      ELSE
        RETURN _result;
      END IF;
    END IF;

  END LOOP;

  DELETE FROM pack
  WHERE (pack_head_id=pSoheadid and pack_head_type = 'SO');

  DELETE FROM charass
  WHERE (charass_target_type='SO')
    AND (charass_target_id=pSoheadid);

  DELETE FROM cohead
  WHERE (cohead_id=pSoheadid);

  IF (fetchMetricBool('AutoCreateProjectsForOrders')) THEN
    PERFORM deleteProject(_r.cohead_prj_id);
  END IF;

  DELETE FROM aropenalloc
  WHERE ((aropenalloc_doctype='S')
    AND  (aropenalloc_doc_id=pSoheadid));

  IF ( (NOT _r.cohead_wasquote) AND (NOT _r.cohead_imported) ) THEN
    IF (COALESCE(pSonumber,'') != '') THEN
      _result = releaseSoNumber(pSonumber);
    ELSEIF (_r.cohead_number IS NOT NULL) THEN
      _result = releaseSoNumber(_r.cohead_number);
    END IF;
  END IF;

  IF (_poStatus < 0) THEN
    RETURN -20;
  ELSE
    RETURN 0;
  END IF;

END;
$_$;


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

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

CREATE FUNCTION deletesoitem(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
  pSoitemid	ALIAS FOR $1;

  _r            RECORD;
  _s            RECORD;
  _result       INTEGER;
  _deletePO     INTEGER := 0;
  _recvId       INTEGER := -1;
  _poStatus     TEXT;
  _jobItem      BOOLEAN;

BEGIN
-- Get coitem
   SELECT * INTO _r FROM coitem WHERE (coitem_id=pSoitemid);

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

-- Cannot delete if shipped
  IF (_r.coitem_qtyshipped > 0) THEN
    RETURN -101;
  END IF;

-- Cannot delete if issued to shipping
  SELECT shipitem_id INTO _result
  FROM shipitem JOIN shiphead ON (shiphead_id=shipitem_shiphead_id AND shiphead_order_type='SO')
  WHERE (shipitem_orderitem_id=pSoitemid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -102;
  END IF;

-- Cannot delete if returned
  IF (fetchMetricBool('MultiWhs')) THEN
    SELECT raitem_id INTO _result
    FROM raitem
    WHERE ( (raitem_orig_coitem_id=pSoitemid)
       OR   (raitem_new_coitem_id=pSoitemid) )
    LIMIT 1;
    IF (FOUND) THEN
      RETURN -103;
    END IF;
  END IF;

-- Cannot delete if any inventory history
  SELECT invhist_id INTO _result
  FROM invhist
  WHERE ( (invhist_ordnumber=formatSoNumber(pSoitemid))
    AND   (invhist_ordtype='SO') )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -105;
  END IF;

-- If Kit, check deletion of component items
  IF (_r.coitem_subnumber = 0) THEN
    FOR _s IN
      SELECT *
      FROM coitem
      WHERE ((coitem_cohead_id = _r.coitem_cohead_id)
        AND  (coitem_linenumber = _r.coitem_linenumber)
        AND  (coitem_subnumber > 0))
    LOOP
      IF ((COALESCE(_s.coitem_order_id, -1) > 0)
       AND (_s.coitem_order_type = 'P')) THEN
        SELECT poitem_status, COALESCE(recv_id, -1)
          INTO _poStatus, _recvId
        FROM poitem LEFT OUTER JOIN recv
               ON ((recv_orderitem_id=poitem_id)
                 AND (recv_order_type='PO'))
        WHERE (poitem_id = _s.coitem_order_id);

        IF ((_recvId > 0) OR (_poStatus = 'C')) THEN
          RETURN -10;
        ELSIF ((_recvId = -1) AND (_poStatus = 'O')) THEN
          _deletePO := _deletePO - 1;
        END IF;
      END IF;
    END LOOP;
  END IF;


  SELECT (itemsite_costmethod='J') INTO _jobItem
  FROM coitem JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
  WHERE (coitem_id=pSoitemid);

  IF (_jobItem AND _r.coitem_order_type='W') THEN
-- Delete associated Job Work Order
    SELECT deleteWo(_r.coitem_order_id, TRUE, TRUE) INTO _result;
    IF (_result < 0) THEN
      RETURN -104;
    END IF;
  ELSIF (_r.coitem_order_type='W') THEN
-- Delete associated Job Work Order
    SELECT deleteWo(_r.coitem_order_id, TRUE) INTO _result;
    IF (_result < 0) THEN
      -- Cannot delete so break association
      PERFORM changeWoProject(_r.coitem_order_id, -1, TRUE);
    END IF;
  ELSIF (_r.coitem_order_type='R') THEN
-- Delete associated Purchase Request
    PERFORM deletePr(_r.coitem_order_id);
  ELSIF (_r.coitem_order_type='P') THEN
-- Delete associated Purchase Order Item
    SELECT deletepoitem(_r.coitem_order_id) INTO _result;
    IF ((_result < 0) AND (_result <> -20)) THEN
      RETURN _result;
    ELSIF (_result = -20) THEN
      _deletePO := _deletePO - 1;
    END IF;
  END IF;

-- Delete characteristic assignments
  DELETE FROM charass
  WHERE (charass_target_type='SI')
    AND (charass_target_id=pSoitemid);

-- Delete reservations
  IF (fetchMetricBool('EnableSOReservationsByLocation')) THEN
    DELETE FROM reserve
    WHERE (reserve_demand_type='SO')
      AND (reserve_demand_id=pSoitemid);
  END IF;

-- Delete the coitem
  DELETE FROM coitem
  WHERE (coitem_id=pSoitemid);

  IF (_deletePO < 0) THEN
    RETURN -20;
  ELSE
    RETURN 0;
  END IF;

END;
$_$;


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

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

CREATE FUNCTION deletestandardjournal(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
  pStdjrnlid ALIAS FOR $1;

BEGIN

  DELETE FROM stdjrnlitem
  WHERE (stdjrnlitem_stdjrnl_id=pStdjrnlid);

  DELETE FROM stdjrnlgrpitem
  WHERE (stdjrnlgrpitem_stdjrnl_id=pStdjrnlid);

  DELETE FROM stdjrnl
  WHERE (stdjrnl_id=pStdjrnlid);

  RETURN 1;

END;
$_$;


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

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

CREATE FUNCTION deletestandardjournalgroup(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
  pStdjrnlgrpid ALIAS FOR $1;

BEGIN

  DELETE FROM stdjrnlgrpitem
  WHERE (stdjrnlgrpitem_stdjrnlgrp_id=pStdjrnlgrpid);

  DELETE FROM stdjrnlgrp
  WHERE (stdjrnlgrp_id=pStdjrnlgrpid);

  RETURN 1;

END;
$_$;


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

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

CREATE FUNCTION deletesubaccount(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
  pid ALIAS FOR $1;

BEGIN
  IF (EXISTS(SELECT accnt_id
             FROM accnt, subaccnt
             WHERE ((accnt_company=subaccnt_number)
               AND  (subaccnt_id=pid))
            )) THEN
    RETURN -1;
  END IF;

  DELETE FROM subaccnt
  WHERE (subaccnt_id=pid);

  RETURN pid;

END;
$_$;


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

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

CREATE FUNCTION deletesubaccounttype(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
  pSubAccntTypeid ALIAS FOR $1;
  _check INTEGER;

BEGIN

--  Check to see if the passed subaccnttype is used in any accounts
  SELECT accnt_id INTO _check
  FROM accnt, subaccnttype
  WHERE ( (accnt_subaccnttype_code=subaccnttype_code)
   AND (subaccnttype_id=pSubAccntTypeid) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

--  Delete the Sub Account Type
  DELETE FROM subaccnttype
  WHERE (subaccnttype_id=pSubAccntTypeid);

  RETURN 0;

END;
$_$;


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

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

CREATE FUNCTION deletetax(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
  ptaxid	ALIAS FOR $1;
BEGIN
  -- these checks allow nice error reporting instead of throwing an SQL error
  IF EXISTS(SELECT taxass_id FROM taxass WHERE (taxass_tax_id=ptaxid)) THEN
    RETURN -10;
  END IF;
  IF EXISTS(SELECT taxhist_id FROM taxhist WHERE (taxhist_tax_id=ptaxid)) THEN
    RETURN -20;
  END IF;

  DELETE FROM taxrate WHERE (taxrate_tax_id = ptaxid);
  DELETE FROM tax WHERE (tax_id = ptaxid);

  RETURN ptaxid;

END;
$_$;


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

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

CREATE FUNCTION deletetaxclass(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
pTaxclassid ALIAS FOR $1;
_result INTEGER;

BEGIN

-- Check to find if the tax class is used in any tax code
SELECT tax_id INTO _result
FROM tax
WHERE (tax_taxclass_id = pTaxclassid);
IF (FOUND) THEN
   RETURN -1;
END IF;

-- Delete the tax class if the above condition doesn't match
DELETE FROM taxclass WHERE taxclass_id = pTaxclassid ;

RETURN pTaxclassid;

END;
$_$;


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

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

CREATE FUNCTION deletetaxtype(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
  pTaxtypeid ALIAS FOR $1;
  _result INTEGER;
BEGIN

  SELECT taxtype_id
    INTO _result
    FROM taxtype
   WHERE ((taxtype_sys)
     AND  (taxtype_id=pTaxtypeid));
  IF (FOUND) THEN
    RETURN -1;
  END IF;

  SELECT taxass_id
    INTO _result
    FROM taxass
   WHERE (taxass_taxtype_id=pTaxtypeid);
  IF (FOUND) THEN
    RETURN -2;
  END IF;

  SELECT taxhist_id
    INTO _result
    FROM taxhist
   WHERE (taxhist_taxtype_id=pTaxtypeid);
  IF (FOUND) THEN
    RETURN -3;
  END IF;

  DELETE
    FROM taxtype
   WHERE (taxtype_id=pTaxtypeid);

  RETURN pTaxtypeid;

END;
$_$;


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

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

CREATE FUNCTION deletetaxzone(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
pTaxzoneid ALIAS FOR $1;
_result INTEGER;

BEGIN

-- Check to find if the tax zone is used in any tax assignment
SELECT taxass_id INTO _result
FROM taxass
WHERE (taxass_taxzone_id=pTaxzoneid);
IF (FOUND) THEN
   RETURN -1;
END IF;

-- Check to find if the tax zone has been referenced in any tax registration
SELECT taxreg_id INTO _result 
FROM taxreg
WHERE (taxreg_taxzone_id=pTaxzoneid);
IF (FOUND) THEN
   RETURN -2;
END IF;

-- Delete the tax zone if none of the above conditions match
DELETE FROM taxzone WHERE taxzone_id = pTaxzoneid ;

RETURN pTaxzoneid;

END;
$_$;


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

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

CREATE FUNCTION deletetodoitem(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
  ptodoItemId ALIAS FOR $1;
BEGIN
  DELETE FROM alarm WHERE ( (alarm_source='TODO') AND (alarm_source_id=ptodoItemId) );
  DELETE FROM todoitem WHERE todoitem_id = ptodoItemId;
  RETURN 0;
END;
$_$;


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

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

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

  DELETE FROM classcode
  WHERE (classcode_id NOT IN (SELECT DISTINCT item_classcode_id FROM item));

  RETURN 0;

END;
$$;


ALTER FUNCTION public.deleteunusedclasscodes() OWNER TO admin;

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

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

  DELETE FROM freightclass
  WHERE (freightclass_id NOT IN (SELECT DISTINCT COALESCE(item_freightclass_id, 0) FROM item));

  RETURN 0;

END;
$$;


ALTER FUNCTION public.deleteunusedfreightclasses() OWNER TO admin;

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

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

--  Delete any associated records
  DELETE FROM salesaccnt
  WHERE ( (salesaccnt_prodcat_id <> -1)
   AND (salesaccnt_prodcat_id NOT IN (SELECT DISTINCT item_prodcat_id FROM item)) );

  DELETE FROM prodcat
  WHERE (prodcat_id NOT IN (SELECT DISTINCT item_prodcat_id FROM item));

  RETURN 0;

END;
$$;


ALTER FUNCTION public.deleteunusedproductcategories() OWNER TO admin;

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

CREATE FUNCTION deleteuom(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
  pUomid ALIAS FOR $1;

BEGIN

  DELETE FROM uomconv WHERE uomconv_from_uom_id=pUomid;
  DELETE FROM uomconv WHERE uomconv_to_uom_id=pUomid;
  DELETE FROM uom WHERE uom_id=pUomid;

  RETURN 0;
END;
$_$;


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

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

CREATE FUNCTION deleteuomconv(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
  pUomconvid ALIAS FOR $1;

BEGIN
  DELETE FROM uomconv WHERE uomconv_id=pUomconvid;

  RETURN 0;
END;
$_$;


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

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

CREATE FUNCTION deleteurl(integer) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
declare
  pId ALIAS FOR $1;
begin
  delete from urlinfo
  where ( url_id in (
    select url_id
    from urlinfo
      join docass on (docass_target_id=url_id)
                 and (docass_target_type='URL')
      where ( docass_id = pId ) ) );

  delete from docass where docass_id = pId;

  return true;
end;
$_$;


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

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

CREATE FUNCTION deleteuserpreference(text) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pPrefname ALIAS FOR $1;
  _return BOOLEAN;

BEGIN

  SELECT deleteUserPreference(getEffectiveXtUser(), pPrefname) INTO _return;

  RETURN _return;

END;
$_$;


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

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

CREATE FUNCTION deleteuserpreference(text, text) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pUsername ALIAS FOR $1;
  pPrefname ALIAS FOR $2;

BEGIN

  DELETE FROM usrpref
  WHERE ( (usrpref_username=pUsername)
   AND (usrpref_name=pPrefname) );

  RETURN TRUE;

END;
$_$;


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

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

CREATE FUNCTION deletevendoraddress(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
  pVendaddrid ALIAS FOR $1;
  _test INTEGER;

BEGIN

--  Check to see if the passed vendor address is used in pohead
  SELECT pohead_id INTO _test
  FROM pohead
  WHERE (pohead_vendaddr_id=pVendaddrid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

--  Delete the passed vendor address
  DELETE FROM vendaddrinfo
  WHERE (vendaddr_id=pVendaddrid);

  RETURN 0;

END;
$_$;


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

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

CREATE FUNCTION deletevendortype(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
  pVendtypeid ALIAS FOR $1;
  _test INTEGER;

BEGIN

--  Check to see if the passed vendor type is used in vendinfo
  SELECT vend_id INTO _test
  FROM vendinfo
  WHERE (vend_vendtype_id=pVendtypeid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

--  Delete the passed vendor type
  DELETE FROM vendtype
  WHERE (vendtype_id=pVendtypeid);

  RETURN 0;

END;
$_$;


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

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

CREATE FUNCTION deletewo(pwoid integer, pdeletechildren boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  RETURN deleteWo(pWoid, pDeleteChildren, FALSE);
END;
$$;


ALTER FUNCTION public.deletewo(pwoid integer, pdeletechildren boolean) OWNER TO admin;

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

CREATE FUNCTION deletewo(pwoid integer, pdeletechildren boolean, pdeleteforce boolean) 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
  woStatus CHAR(1);
  itemType CHAR(1);
  ordtype CHAR(1);
  ordid INTEGER;
  returnCode INTEGER;
  _wotcCnt	INTEGER;
  _routings BOOLEAN;

BEGIN
  SELECT wo_status, wo_ordtype, wo_ordid, item_type
  INTO woStatus, ordtype, ordid, itemType
  FROM wo JOIN itemsite ON (itemsite_id=wo_itemsite_id)
          JOIN item ON (item_id=itemsite_item_id)
  WHERE (wo_id=pWoid);

  IF (pDeleteForce) THEN
    IF (NOT woStatus IN ('O', 'E', 'R', 'C')) THEN
      RETURN -3;
    END IF;
  ELSE
    IF (NOT woStatus IN ('O', 'E')) THEN
      RETURN -3;
    END IF;

    IF (itemType = 'J') THEN
      RETURN -2;
    END IF;
  END IF;

  SELECT fetchMetricBool('Routings') INTO _routings;

  IF (_routings AND woStatus != 'C') THEN
    SELECT count(*) INTO _wotcCnt
    FROM xtmfg.wotc
    WHERE (wotc_wo_id=pWoid);
    IF (_wotcCnt > 0) THEN
      RETURN -1;
    END IF;
  END IF;

  IF (woStatus = 'R') THEN
    PERFORM postEvent('RWoRequestCancel', 'W', wo_id,
                      itemsite_warehous_id, formatWoNumber(wo_id),
                      NULL, NULL, NULL, NULL)
    FROM wo JOIN itemsite ON (itemsite_id=wo_itemsite_id)
            JOIN item ON (item_id=itemsite_item_id)
    WHERE (wo_id=pWoid);

     RETURN 0;
  ELSE
    IF (woStatus = 'E') THEN
      returnCode := (SELECT implodeWo(pWoid, FALSE));
    END IF;
  END IF;

  IF (woStatus IN ('O', 'E', 'C')) THEN
    DELETE FROM womatl
    WHERE (womatl_wo_id=pWoid);

    IF _routings THEN
      DELETE FROM xtmfg.wotc
      WHERE (wotc_wo_id=pWoid);
      DELETE FROM xtmfg.wooper
      WHERE (wooper_wo_id=pWoid);
    END IF;

    IF (ordtype = 'S') THEN
      UPDATE coitem SET coitem_order_type=NULL, coitem_order_id=NULL
      WHERE coitem_id=ordid;
    END IF;

    DELETE FROM wo
    WHERE (wo_id=pWoid);
  END IF;

  IF (pDeleteChildren) THEN
    returnCode := (SELECT MAX(deleteWo(wo_id, TRUE))
                   FROM wo
                   WHERE ((wo_ordtype='W')
                    AND (wo_ordid=pWoid)));
  END IF;

  RETURN 0;
END;
$$;


ALTER FUNCTION public.deletewo(pwoid integer, pdeletechildren boolean, pdeleteforce boolean) OWNER TO admin;

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

CREATE FUNCTION deletewomaterial(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
  pWomatlid ALIAS FOR $1;

BEGIN

  UPDATE wo
  SET wo_adhoc=TRUE
  FROM womatl
  WHERE ((womatl_wo_id=wo_id)
   AND (womatl_id=pWomatlid));

--  Delete any created P/R for this Womatl
  PERFORM deletePr('W', pWomatlid);

  DELETE FROM womatl
  WHERE (womatl_id=pWomatlid);

  RETURN 0;
END;
$_$;


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

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

CREATE FUNCTION detachccpayfromso(integer, 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
  pcoheadid		ALIAS FOR $1;
  pwarehousid		ALIAS FOR $2;
  pcustid		ALIAS FOR $3;

BEGIN
  RAISE WARNING 'detachCCPayFromSO(INTEGER, INTEGER, INTEGER): deprecated';
  RETURN 0;
END;
$_$;


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

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

CREATE FUNCTION detachcontact(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
  pcntctId    ALIAS FOR $1;
  pcrmacctId  ALIAS FOR $2;
BEGIN
  UPDATE cntct SET cntct_crmacct_id = NULL
  WHERE cntct_id = pcntctId
    AND cntct_crmacct_id = pcrmacctId;

  UPDATE crmacct SET crmacct_cntct_id_1 = NULL
  WHERE crmacct_id = pcrmacctId
    AND crmacct_cntct_id_1 = pcntctId;

  UPDATE crmacct SET crmacct_cntct_id_2 = NULL
  WHERE crmacct_id = pcrmacctId
    AND crmacct_cntct_id_2 = pcntctId;

  RETURN 0;
END;
$_$;


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

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

CREATE FUNCTION detag(text) 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
  pSource ALIAS FOR $1;
  _result TEXT := '';

BEGIN
  SELECT regexp_replace(pSource, E'<[^>]*>', '', 'g') INTO _result;
  RETURN _result;
END;
$_$;


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

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

CREATE FUNCTION detailednnqoh(integer, boolean) RETURNS numeric
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
--
-- Deprecated
--
DECLARE
  pItemsiteid ALIAS FOR $1;
  pABS ALIAS FOR $2;
  _qoh NUMERIC;

BEGIN

  IF (pABS) THEN
    SELECT SUM(noNeg(itemloc_qty)) INTO _qoh
    FROM itemloc, location
    WHERE ( (itemloc_location_id=location_id)
     AND (NOT location_netable)
     AND (itemloc_itemsite_id=pItemsiteid) );
  ELSE
    SELECT SUM(itemloc_qty) INTO _qoh
    FROM itemloc, location
    WHERE ( (itemloc_location_id=location_id)
     AND (NOT location_netable)
     AND (itemloc_itemsite_id=pItemsiteid) );
  END IF;

  IF (_qoh IS NULL) THEN
    _qoh := 0;
  END IF;

  RETURN _qoh;

END;
$_$;


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

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

CREATE FUNCTION detailedqoh(pitemsiteid integer, pabs boolean) RETURNS numeric
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _qoh NUMERIC;

BEGIN

  IF (pABS) THEN
    SELECT SUM(noNeg(itemloc_qty)) INTO _qoh
    FROM itemloc
    WHERE (itemloc_itemsite_id=pItemsiteid);
  ELSE
    SELECT SUM(itemloc_qty) INTO _qoh
    FROM itemloc
    WHERE (itemloc_itemsite_id=pItemsiteid);
  END IF;

  IF (_qoh IS NULL) THEN
    _qoh := 0.0;
  END IF;

  RETURN _qoh;

END;
$$;


ALTER FUNCTION public.detailedqoh(pitemsiteid integer, pabs boolean) OWNER TO admin;

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

CREATE FUNCTION determinediscountdate(integer, date) RETURNS date
    LANGUAGE plpgsql STABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pTermsid ALIAS FOR $1;
  pSourceDate ALIAS FOR $2;
  _discDate DATE;
  _p RECORD;

BEGIN

  SELECT terms_type, terms_discdays, terms_cutoffday INTO _p
  FROM terms
  WHERE (terms_id=pTermsid);
  IF (NOT FOUND) THEN
    _discDate := pSourceDate;

--  Handle type D terms
  ELSIF (_p.terms_type = 'D') THEN
    _discDate := (pSourceDate + _p.terms_discdays);

--  Handle type P terms
  ELSIF (_p.terms_type = 'P') THEN
    IF (date_part('day', pSourceDate) <= _p.terms_cutoffday) THEN
      _discDate := (DATE(date_trunc('month', pSourceDate)) + (_p.terms_discdays - 1));
    ELSE
      _discDate := (DATE(date_trunc('month', pSourceDate)) + (_p.terms_discdays - 1) + INTERVAL '1 month');
    END IF;

--  Handle unknown terms
  ELSE
    _discDate := pSourceDate;
  END IF;

  RETURN _discDate;

END;
$_$;


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

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

CREATE FUNCTION determineduedate(integer, date) RETURNS date
    LANGUAGE plpgsql STABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pTermsid ALIAS FOR $1;
  pSourceDate ALIAS FOR $2;
  _dueDate DATE;
  _p RECORD;

BEGIN

  SELECT terms_type, terms_duedays, terms_cutoffday INTO _p
  FROM terms
  WHERE (terms_id=pTermsid);
  IF (NOT FOUND) THEN
    _dueDate := pSourceDate;

--  Handle type D terms
  ELSIF (_p.terms_type = 'D') THEN
    _dueDate := (pSourceDate + _p.terms_duedays);

--  Handle type P terms
  ELSIF (_p.terms_type = 'P') THEN
    IF (date_part('day', pSourceDate) <= _p.terms_cutoffday) THEN
      _dueDate := (DATE(date_trunc('month', pSourceDate)) + (_p.terms_duedays - 1));
    ELSE
      _dueDate := (DATE(date_trunc('month', pSourceDate)) + (_p.terms_duedays - 1) + INTERVAL '1 month');
    END IF;

--  Handle unknown terms
  ELSE
    _dueDate := pSourceDate;
  END IF;

  RETURN _dueDate;

END;
$_$;


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

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

CREATE FUNCTION digest(text, text) RETURNS bytea
    LANGUAGE c IMMUTABLE STRICT
    AS '$libdir/pgcrypto', 'pg_digest';


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

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

CREATE FUNCTION digest(bytea, text) RETURNS bytea
    LANGUAGE c IMMUTABLE STRICT
    AS '$libdir/pgcrypto', 'pg_digest';


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

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

CREATE FUNCTION disablepackage(text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  ppkgname ALIAS FOR $1;
  _i       INTEGER := 0;
  _tabs    TEXT[] := ARRAY['cmd',  'cmdarg', 'image',  'metasql',
                           'priv', 'report', 'script', 'uiform'];

BEGIN
  IF (version() < 'PostgreSQL 8.2') THEN
    RETURN -1;
  END IF;

  FOR _i IN ARRAY_LOWER(_tabs,1)..ARRAY_UPPER(_tabs,1) LOOP
    EXECUTE 'ALTER TABLE ' || ppkgname || '.pkg' || _tabs[_i] ||
            ' NO INHERIT public.' || _tabs[_i] || ';';
  END LOOP;
  
  RETURN 0;
END;
$_$;


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

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

CREATE FUNCTION disablepackage(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
  ppkgheadid    ALIAS FOR $1;
  _pkgname      TEXT;

BEGIN
  SELECT pkghead_name INTO _pkgname
  FROM pkghead
  WHERE (pkghead_id=ppkgheadid);
  IF (NOT FOUND) THEN
    RETURN -2;
  END IF;

  RETURN disablePackage(_pkgname);
END;
$_$;


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

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

CREATE FUNCTION distributeitemlocseries(pitemlocseries 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
  _distCounter     INTEGER;
  _itemlocdist     RECORD;
  _itemlocid       INTEGER;
  _invhistid       INTEGER;
  _check           BOOLEAN;
  _debug           BOOLEAN := false;
BEGIN

  IF (_debug) THEN
    RAISE NOTICE 'distributeItemlocSeries, series=%', pItemlocSeries;
  END IF;

  _distCounter := 0;

--  March through all of the itemlocdists for pItemlocSeries
  FOR _itemlocdist IN SELECT itemlocdist_id AS itemlocdistid,
                             itemlocdist_source_type AS type,
                             itemlocdist_source_id AS sourceid,
                             itemlocdist_qty AS qty,
                             itemlocdist_itemsite_id AS itemsiteid,
                             itemsite_freeze,
                             itemlocdist_invhist_id AS invhistid,
                             itemlocdist_ls_id AS lotserialid,
                             itemlocdist_expiration AS expiration,
                             itemlocdist_flush,
                             itemlocdist_warranty AS warranty,
                             itemlocdist_series AS series
                      FROM itemlocdist, itemsite
                      WHERE ( (itemlocdist_itemsite_id=itemsite_id)
                       AND (itemlocdist_series=pItemlocSeries) )
                      ORDER BY itemlocdist_flush DESC LOOP

    _distCounter := _distCounter + 1;
    IF (_debug) THEN
      RAISE NOTICE 'itemlocdist loop %', _distCounter;
      RAISE NOTICE 'itemlocdistid=%', _itemlocdist.itemlocdistid;
      RAISE NOTICE 'type=%', _itemlocdist.type;
      RAISE NOTICE 'sourceid=%', _itemlocdist.sourceid;
      RAISE NOTICE 'qty=%', _itemlocdist.qty;
      RAISE NOTICE 'itemsiteid=%', _itemlocdist.itemsiteid;
      RAISE NOTICE 'freeze=%', _itemlocdist.itemsite_freeze;
      RAISE NOTICE 'invhistid=%', _itemlocdist.invhistid;
      RAISE NOTICE 'lotserialid=%', _itemlocdist.lotserialid;
      RAISE NOTICE 'expiration=%', _itemlocdist.expiration;
      RAISE NOTICE 'flush=%', _itemlocdist.itemlocdist_flush;
      RAISE NOTICE 'warranty=%', _itemlocdist.warranty;
    END IF;

--  Commit invhist to itemsite
    IF (NOT _itemlocdist.itemsite_freeze) THEN
	PERFORM postInvHist(_itemlocdist.invhistid);
    END IF;

--  Mark the invhist tuple for the itemlocdist in question as having detail
    UPDATE invhist
    SET invhist_hasdetail=TRUE
    WHERE ( (NOT invhist_hasdetail)
     AND (invhist_id=_itemlocdist.invhistid) );

--  If this itemlocdist is a flush, write a invdetail tuple that records the empty
    IF (_itemlocdist.itemlocdist_flush) THEN
      INSERT INTO invdetail
      ( invdetail_invhist_id, invdetail_location_id, invdetail_ls_id,
        invdetail_qty, invdetail_qty_before, invdetail_qty_after, invdetail_expiration,
        invdetail_warrpurc )
      SELECT _itemlocdist.invhistid, itemloc_location_id, itemloc_ls_id,
             (itemloc_qty * -1), itemloc_qty, 0, itemloc_expiration,
             _itemlocdist.warranty
      FROM itemloc
      WHERE ( (itemloc_qty <> 0)
       AND (itemloc_id=_itemlocdist.sourceid) );

--  Delete the flushed itemloc if its parent itemsite is not frozen
      IF (NOT _itemlocdist.itemsite_freeze) THEN
        DELETE FROM itemloc
        WHERE (itemloc_id=_itemlocdist.sourceid);
      END IF;

    ELSE
--  If this is a location type distribution, check to see if the target itemloc
--  already exists
      IF (_itemlocdist.type = 'L') THEN
        SELECT itemloc_id INTO _itemlocid
        FROM itemloc
        WHERE ( (itemloc_itemsite_id=_itemlocdist.itemsiteid)
         AND (itemloc_location_id=_itemlocdist.sourceid)
         AND (COALESCE(itemloc_ls_id,-1)=COALESCE(_itemlocdist.lotserialid,-1))
         AND (COALESCE(itemloc_expiration,endOfTime())=COALESCE(_itemlocdist.expiration,endOfTime()))
         AND (COALESCE(itemloc_warrpurc,endoftime())=COALESCE(_itemlocdist.warranty,endoftime())) );

--  Nope, create it
        IF (NOT FOUND) THEN
          SELECT NEXTVAL('itemloc_itemloc_id_seq') INTO _itemlocid;

          INSERT INTO itemloc
          ( itemloc_id, itemloc_itemsite_id,
            itemloc_location_id, itemloc_qty,
            itemloc_ls_id, itemloc_expiration,
            itemloc_warrpurc )
          VALUES
          ( _itemlocid, _itemlocdist.itemsiteid,
            _itemlocdist.sourceid, 0,
            _itemlocdist.lotserialid, _itemlocdist.expiration,
            _itemlocdist.warranty );
        END IF;

      ELSE
        _itemlocid = _itemlocdist.sourceid;

        IF (_itemlocid IS NOT NULL AND (SELECT count(itemloc_id) = 0 FROM itemloc WHERE itemloc_id=_itemlocid)) THEN
          RAISE EXCEPTION 'No record to distribute against. Someone else may have already distributed this record.';
        END IF;
      END IF;

--  Record the invdetail
      INSERT INTO invdetail
      (invdetail_invhist_id, invdetail_location_id, invdetail_ls_id,
       invdetail_qty, invdetail_qty_before, invdetail_qty_after, invdetail_expiration,
       invdetail_warrpurc)
      SELECT _itemlocdist.invhistid, itemloc_location_id, _itemlocdist.lotserialid,
             _itemlocdist.qty, itemloc_qty, (itemloc_qty + _itemlocdist.qty),
             itemloc_expiration,_itemlocdist.warranty
      FROM itemloc
      WHERE (itemloc_id=_itemlocid);

--  Update the itemloc_qty if its parent itemsite is not frozen
      IF (NOT _itemlocdist.itemsite_freeze) THEN
        UPDATE itemloc
        SET itemloc_qty = (itemloc_qty + _itemlocdist.qty)
        WHERE (itemloc_id=_itemlocid);
      END IF;

    END IF;

--  If, after the distribution, the target itemloc_qty = 0, delete the itemloc
--  if its parent itemsite is not frozen
    IF (NOT _itemlocdist.itemsite_freeze) THEN
      DELETE FROM itemloc
      WHERE ( (itemloc_qty=0)
       AND (itemloc_id=_itemlocid) );
    END IF;

  END LOOP;

  DELETE FROM itemlocdist
  WHERE (itemlocdist_series=pItemlocSeries);

  RETURN _distCounter;

END;
$$;


ALTER FUNCTION public.distributeitemlocseries(pitemlocseries integer) OWNER TO admin;

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

CREATE FUNCTION distributetodefault(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
  pItemlocdistid ALIAS FOR $1;

BEGIN

  RETURN distributeToDefault(pItemlocdistid, 'O');

END;
$_$;


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

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

CREATE FUNCTION distributetodefault(integer, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pItemlocdistid ALIAS FOR $1;
  pTranstype ALIAS FOR $2;
  _locationid INTEGER;
  _itemlocdistid INTEGER;
  _qty NUMERIC;

BEGIN

--  Make sure that the itemsite in question has a default location
  SELECT CASE WHEN (pTranstype='R') THEN itemsite_recvlocation_id
              WHEN (pTranstype='I') THEN itemsite_issuelocation_id
              ELSE itemsite_location_id
         END INTO _locationid
  FROM itemlocdist, itemsite
  WHERE ( (itemlocdist_itemsite_id=itemsite_id)
   AND (itemlocdist_id=pItemlocdistid) );
  IF ( (NOT FOUND) OR (_locationid = -1) ) THEN
    RETURN -1;
  END IF;

--  Determine the remaining qty required to distribute
  SELECT (p.itemlocdist_qty - COALESCE(SUM(c.itemlocdist_qty), 0)) INTO _qty
  FROM itemlocdist AS p LEFT OUTER JOIN itemlocdist AS c ON (c.itemlocdist_itemlocdist_id=p.itemlocdist_id)
  WHERE (p.itemlocdist_id=pItemlocdistid)
  GROUP BY p.itemlocdist_qty;

  IF (_qty = 0) THEN
    RETURN -2;
  END IF;

--  Check to see if an itemlocdist with the correct location/lotserial/expiration already exists
  SELECT target.itemlocdist_id INTO _itemlocdistid
  FROM itemlocdist AS source, itemlocdist AS target, itemloc, itemsite
  WHERE ( (target.itemlocdist_source_type='L')
   AND (target.itemlocdist_source_id=_locationid)
   AND (target.itemlocdist_itemsite_id=source.itemlocdist_itemsite_id)
   AND (COALESCE(target.itemlocdist_ls_id)=COALESCE(source.itemlocdist_ls_id))
   AND (target.itemlocdist_expiration=source.itemlocdist_expiration)
   AND (target.itemlocdist_itemlocdist_id=source.itemlocdist_itemlocdist_id)
   AND (target.itemlocdist_itemsite_id=itemsite_id)
   AND (source.itemlocdist_id=pItemlocdistid) );

  IF (FOUND) THEN
    UPDATE itemlocdist
    SET itemlocdist_qty = (itemlocdist_qty + _qty)
    WHERE (itemlocdist_id=_itemlocdistid);

    RETURN _itemlocdistid;
  END IF;

--  Create a new itemlocdist
  SELECT NEXTVAL('itemlocdist_itemlocdist_id_seq') INTO _itemlocdistid;

  INSERT INTO itemlocdist
  ( itemlocdist_id, itemlocdist_itemlocdist_id, itemlocdist_source_type,
    itemlocdist_ls_id, itemlocdist_expiration,
    itemlocdist_source_id, itemlocdist_itemsite_id, itemlocdist_qty )
  SELECT _itemlocdistid, pItemlocdistid, 'L',
         itemlocdist_ls_id, itemlocdist_expiration,
         _locationid, itemlocdist_itemsite_id, _qty
  FROM itemlocdist
  WHERE (itemlocdist_id=pItemlocdistid);

  RETURN _itemlocdistid;

END;
$_$;


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

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

CREATE FUNCTION distributetodefaultitemloc(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
  pItemlocdistid ALIAS FOR $1;

BEGIN

  RETURN distributeToDefaultItemLoc(pItemlocdistid, 'O');

END;
$_$;


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

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

CREATE FUNCTION distributetodefaultitemloc(integer, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pItemlocdistid ALIAS FOR $1;
  pTranstype ALIAS FOR $2;
  _itemlocid INTEGER;
  _itemlocdistid INTEGER;
  _qty NUMERIC;

BEGIN

--  Make sure that the itemsite in question has a default location
  SELECT itemloc_id INTO _itemlocid
    FROM itemlocdist, itemsite, itemloc
   WHERE ((itemlocdist_itemsite_id=itemsite_id)
     AND ( (itemsite_location_id=itemloc_location_id AND pTranstype='O') OR
           (itemsite_recvlocation_id=itemloc_location_id AND pTranstype='R') OR
           (itemsite_issuelocation_id=itemloc_location_id AND pTranstype='I') )
     AND (itemloc_itemsite_id=itemsite_id)
     AND (itemlocdist_id=pItemlocdistid));
  IF ( (NOT FOUND) OR (_itemlocid = -1) ) THEN
    RETURN -1;
  END IF;

--  Determine the remaining qty required to distribute
  SELECT (p.itemlocdist_qty - COALESCE(SUM(c.itemlocdist_qty), 0)) INTO _qty
    FROM itemlocdist AS p LEFT OUTER JOIN itemlocdist AS c
      ON (c.itemlocdist_itemlocdist_id=p.itemlocdist_id)
   WHERE (p.itemlocdist_id=pItemlocdistid)
   GROUP BY p.itemlocdist_qty;

  IF (_qty = 0) THEN
    RETURN -2;
  END IF;

--  Check to see if an itemlocdist with the correct location/lotserial/expiration already exists
  SELECT target.itemlocdist_id INTO _itemlocdistid
  FROM itemlocdist AS source, itemlocdist AS target
  WHERE ( (target.itemlocdist_source_type='I')
   AND (target.itemlocdist_source_id=_itemlocid)
   AND (COALESCE(target.itemlocdist_ls_id,-1)=COALESCE(source.itemlocdist_ls_id,-1))
   AND (target.itemlocdist_expiration=source.itemlocdist_expiration)
   AND (target.itemlocdist_itemlocdist_id=source.itemlocdist_id)
   AND (source.itemlocdist_id=pItemlocdistid) );

  IF (FOUND) THEN
    UPDATE itemlocdist
    SET itemlocdist_qty = (itemlocdist_qty + _qty)
    WHERE (itemlocdist_id=_itemlocdistid);

    RETURN _itemlocdistid;
  END IF;

--  Create a new itemlocdist
  SELECT NEXTVAL('itemlocdist_itemlocdist_id_seq') INTO _itemlocdistid;

  INSERT INTO itemlocdist
  ( itemlocdist_id, itemlocdist_itemlocdist_id,
    itemlocdist_source_type, itemlocdist_source_id,
    itemlocdist_qty, itemlocdist_expiration )
  VALUES
  ( _itemlocdistid, pItemlocdistid,
    'I', _itemlocid,
    _qty, endOfTime() );

  RETURN _itemlocdistid;

END;
$_$;


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

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

CREATE FUNCTION distributetolocations(pitemlocdistid 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
  _distCounter INTEGER;
  _itemlocdist RECORD;
  _itemlocid INTEGER;
  _runningQty NUMERIC;
  _tmp RECORD;

BEGIN

  _distCounter := 0;
  _runningQty  := 0;

-- A scenario can occur where two people try to post distributions
-- to the same itemsite against two or more lot/serial/mlc locations
-- leading to a deadlock. This line tries to prevent that by locking
-- ahead of time all the itemsites that the transaction will need
-- before any of the other tables are locked individually.
  SELECT itemsite_id
    INTO _tmp
    FROM itemsite
   WHERE(itemsite_id in (SELECT DISTINCT itemlocdist_itemsite_id
                           FROM itemlocdist
                          WHERE(itemlocdist_id=pItemlocdistid)))
     FOR UPDATE;

--  March through all of the itemlocdist owned by the passed parent itemlocdist
  FOR _itemlocdist IN SELECT c.itemlocdist_id AS itemlocdistid,
                             c.itemlocdist_source_type AS type,
                             c.itemlocdist_source_id AS sourceid,
                             c.itemlocdist_qty AS qty,
                             p.itemlocdist_itemsite_id AS itemsiteid,
                             itemsite_freeze,
                             p.itemlocdist_invhist_id AS invhistid,
                             p.itemlocdist_ls_id AS lotserialid,
                             p.itemlocdist_expiration AS expiration,
                             p.itemlocdist_warranty AS warranty,
                             p.itemlocdist_order_type AS ordertype,
                             p.itemlocdist_order_id AS orderid,
                             p.itemlocdist_series AS series
                      FROM itemlocdist AS c, itemlocdist AS p, itemsite
                      WHERE ( (c.itemlocdist_itemlocdist_id=p.itemlocdist_id)
                       AND (p.itemlocdist_source_type='O')
                       AND (p.itemlocdist_itemsite_id=itemsite_id)
                       AND (p.itemlocdist_id=pItemlocdistid) ) LOOP

    _distCounter := _distCounter + 1;

--  If the target for this itemlocdist is a location, check to see if the
--  required itemloc already exists
    IF (_itemlocdist.type = 'L') THEN
      SELECT itemloc_id INTO _itemlocid
      FROM itemloc
      WHERE ( (itemloc_itemsite_id=_itemlocdist.itemsiteid)
       AND (itemloc_location_id=_itemlocdist.sourceid)
       AND (COALESCE(itemloc_ls_id, -1)=COALESCE(_itemlocdist.lotserialid, -1))
       AND (COALESCE(itemloc_expiration,endOfTime())=COALESCE(_itemlocdist.expiration,endOfTime()))
       AND (COALESCE(itemloc_warrpurc,endoftime())=COALESCE(_itemlocdist.warranty,endoftime())) );

--  Nope, make it
      IF (NOT FOUND) THEN
        SELECT NEXTVAL('itemloc_itemloc_id_seq') INTO _itemlocid;
        INSERT INTO itemloc
        ( itemloc_id, itemloc_itemsite_id,
          itemloc_location_id, itemloc_qty,
          itemloc_ls_id, itemloc_expiration,
          itemloc_warrpurc )
        VALUES
        ( _itemlocid, _itemlocdist.itemsiteid,
          _itemlocdist.sourceid, 0,
          _itemlocdist.lotserialid, _itemlocdist.expiration,
          _itemlocdist.warranty );
      END IF;

    ELSE
--  Yep, cache it
      _itemlocid = _itemlocdist.sourceid;

      IF (_itemlocid IS NOT NULL AND (SELECT count(itemloc_id) = 0 FROM itemloc WHERE itemloc_id=_itemlocid)) THEN
        RAISE EXCEPTION 'No record to distribute against. Someone else may have already distributed this record.';
      END IF;
    END IF;

--  Record the invdetail for this itemlocdist
    INSERT INTO invdetail
    ( invdetail_invhist_id, invdetail_location_id, invdetail_ls_id,
      invdetail_qty, invdetail_qty_before, invdetail_qty_after, invdetail_expiration, 
      invdetail_warrpurc )
    SELECT _itemlocdist.invhistid, itemloc_location_id, itemloc_ls_id,
           _itemlocdist.qty, itemloc_qty, (itemloc_qty + _itemlocdist.qty),
           itemloc_expiration,_itemlocdist.warranty
    FROM itemloc
    WHERE (itemloc_id=_itemlocid);

--  Update the parent invhist to indicate that it has invdetail records
    UPDATE invhist
    SET invhist_hasdetail=TRUE
    WHERE ((invhist_hasdetail=FALSE)
     AND (invhist_id=_itemlocdist.invhistid));

--  Update the itemloc_qty if its parent itemsite is not frozen
    IF (NOT _itemlocdist.itemsite_freeze) THEN
      UPDATE itemloc
      SET itemloc_qty = (itemloc_qty + _itemlocdist.qty)
      WHERE (itemloc_id=_itemlocid);

      PERFORM postInvHist(_itemlocdist.invhistid);

--  Handle reservation data
      IF ( (SELECT fetchMetricBool('EnableSOReservationsByLocation')) AND
           (_itemlocdist.qty < 0) ) THEN

--  If a shipment on a sales order, record reservation change before updating
--  so it can be reversed later if necessary
        IF (_itemlocdist.ordertype = 'SO') THEN
          INSERT INTO shipitemlocrsrv
          SELECT nextval('shipitemlocrsrv_shipitemlocrsrv_id_seq'),
            shipitem_id, itemloc_itemsite_id, itemloc_location_id,
            itemloc_ls_id, itemloc_expiration, itemloc_warrpurc,
            least((_itemlocdist.qty * -1.0), reserve_qty)
          FROM shipitem, itemloc
            JOIN reserve ON (itemloc_id=reserve_supply_id AND reserve_supply_type='I')
          WHERE ( (shipitem_invhist_id=_itemlocdist.invhistid)
            AND   (itemloc_id=_itemlocid)
            AND   (reserve_demand_type=_itemlocdist.ordertype)
            AND   (reserve_demand_id=_itemlocdist.orderid) );
        END IF;

--  Update the reservation
        UPDATE reserve
        SET reserve_qty = (reserve_qty + _itemlocdist.qty)
        WHERE ( (reserve_supply_id=_itemlocid)
          AND   (reserve_supply_type='I')
          AND   (reserve_demand_type=_itemlocdist.ordertype)
          AND   (reserve_demand_id=_itemlocdist.orderid) );
          
--  Delete reservation if fully distributed
        DELETE FROM reserve
        WHERE ( (reserve_supply_id=_itemlocid)
          AND   (reserve_supply_type='I')
          AND   (reserve_demand_type=_itemlocdist.ordertype)
          AND   (reserve_demand_id=_itemlocdist.orderid)
          AND   (reserve_qty=0) );
      END IF;
    END IF;

--  Cache the running qty.
    _runningQty := _runningQty + _itemlocdist.qty;

--  Dene with the child itemlocdist, so delete it
    DELETE FROM itemlocdist
    WHERE (itemlocdist_id=_itemlocdist.itemlocdistid);

--  If the target itemloc is now at qty=0, delete it if its parent
--  itemsite is not frozen
    IF (NOT _itemlocdist.itemsite_freeze) THEN
      DELETE FROM itemloc
      WHERE ( (itemloc_qty=0)
       AND (itemloc_id=_itemlocid) );
    END IF;

  END LOOP;

--  If the running qty for the detailed distributions is the same as the
--  total qty to distribute indicated by the parent itemlocdist, then the
--  parent itemlocdist has been fully distributed and should be deleted.
  IF ( ( SELECT itemlocdist_qty
         FROM itemlocdist
         WHERE (itemlocdist_id=pItemlocdistid) ) = _runningQty) THEN
    DELETE FROM itemlocdist
    WHERE (itemlocdist_id=pItemlocdistid);
  ELSE
--  There is still some more qty to distribute in the parent itemlocdist.
--  Update the qty to distribute with the qty that has been distributed.
    UPDATE itemlocdist
    SET itemlocdist_qty = (itemlocdist_qty - _runningQty)
    WHERE (itemlocdist_id=pItemlocdistid);
  END IF;

  RETURN _distCounter;

END;
$$;


ALTER FUNCTION public.distributetolocations(pitemlocdistid integer) OWNER TO admin;

--
