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

CREATE FUNCTION getcmnttypeid(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
  pCmntType ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (COALESCE(TRIM(pCmntType), '') = '') THEN
    RETURN NULL;
  END IF;

  SELECT cmnttype_id INTO _returnVal
  FROM cmnttype
  WHERE (cmnttype_name=pCmntType) LIMIT 1;

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Comment Type % not found.', pCmntType;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1670 (class 1255 OID 146566250)
-- Dependencies: 4536 8
-- Name: getcntctid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getcntctid(pcontactnumber text) RETURNS integer
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _returnVal INTEGER;
BEGIN
  SELECT getCntctId(pContactNumber,true) INTO _returnVal;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getcntctid(pcontactnumber text) OWNER TO admin;

--
-- TOC entry 1671 (class 1255 OID 146566251)
-- Dependencies: 4536 8
-- Name: getcntctid(text, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getcntctid(pcontactnumber text, pnotfounderr boolean) RETURNS integer
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _returnVal INTEGER;
BEGIN
  IF (COALESCE(TRIM(pContactNumber), '') = '') THEN
    RETURN NULL;
  END IF;

  SELECT cntct_id INTO _returnVal
  FROM cntct
  WHERE (cntct_number=pContactNumber);

  IF (_returnVal IS NULL AND pNotFoundErr) THEN
    RAISE EXCEPTION 'Contact Number % not found.', pContactNumber;
  ELSIF (_returnVal IS NULL) THEN
    RETURN NULL;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getcntctid(pcontactnumber text, pnotfounderr boolean) OWNER TO admin;

--
-- TOC entry 1672 (class 1255 OID 146566252)
-- Dependencies: 4536 8
-- Name: getcoheadid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getcoheadid(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
  pSalesOrderNumber ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pSalesOrderNumber IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT cohead_id INTO _returnVal
  FROM cohead
  WHERE (cohead_number=pSalesOrderNumber);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Sales Order % not found.', pSalesOrderNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1674 (class 1255 OID 146566253)
-- Dependencies: 4536 8
-- Name: getcoitemid(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getcoitemid(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
  pSalesOrderNumber	ALIAS FOR $1;
  pLineNumber 	    	ALIAS FOR $2;
  _linenumber		INTEGER;
  _subnumber		INTEGER;
  _returnVal 		INTEGER;
BEGIN
  IF (pSalesOrderNumber IS NULL OR pLineNumber IS NULL) THEN
    RETURN NULL;
  END IF;

  --Parse Line Number
  IF (position('.' in pLineNumber) > 0) THEN
    _linenumber	:= CAST(substring(pLineNumber from 1 for position('.' in pLineNumber)-1) AS INTEGER);
    _subnumber	:= CAST(substring(pLineNumber from position('.' in pLineNumber)+1 for length(pLineNumber)) AS INTEGER);
  ELSE
    _linenumber	:= CAST(pLineNumber AS INTEGER);
    _subnumber	:= 0;
  END IF;

  SELECT coitem_id INTO _returnVal
  FROM cohead, coitem
  WHERE ((cohead_number=pSalesOrderNumber)
  AND (coitem_cohead_id=cohead_id)
  AND (coitem_linenumber=_linenumber)
  AND (coitem_subnumber=_subnumber));

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Sales Order % not found.', pSalesOrderNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1675 (class 1255 OID 146566254)
-- Dependencies: 4536 8
-- Name: getcontrcteffective(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getcontrcteffective(pcontrctnumber text) RETURNS date
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _returnVal DATE;
BEGIN
  IF (pContrctNumber IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT contrct_effective INTO _returnVal
  FROM contrct
  WHERE (contrct_number=pContrctNumber);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Contract Number % not found.', pContrctNumber;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getcontrcteffective(pcontrctnumber text) OWNER TO admin;

--
-- TOC entry 1676 (class 1255 OID 146566255)
-- Dependencies: 4536 8
-- Name: getcontrctexpires(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getcontrctexpires(pcontrctnumber text) RETURNS date
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _returnVal DATE;
BEGIN
  IF (pContrctNumber IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT contrct_expires INTO _returnVal
  FROM contrct
  WHERE (contrct_number=pContrctNumber);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Contract Number % not found.', pContrctNumber;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getcontrctexpires(pcontrctnumber text) OWNER TO admin;

--
-- TOC entry 1677 (class 1255 OID 146566256)
-- Dependencies: 4536 8
-- Name: getcontrctid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getcontrctid(pcontrctnumber 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
  _returnVal INTEGER;
BEGIN
  IF (pContrctNumber IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT contrct_id INTO _returnVal
  FROM contrct
  WHERE (contrct_number=pContrctNumber);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Contract Number % not found.', pContrctNumber;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getcontrctid(pcontrctnumber text) OWNER TO admin;

--
-- TOC entry 1678 (class 1255 OID 146566257)
-- Dependencies: 4536 8
-- Name: getcostcatid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getcostcatid(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
  pCostCat ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pCostCat IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT costcat_id INTO _returnVal
  FROM costcat
  WHERE (costcat_code=pCostCat);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Cost Category Code % not found.', pCostCat;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1679 (class 1255 OID 146566258)
-- Dependencies: 4536 8
-- Name: getcostelemid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getcostelemid(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
  pCostElemType ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pCostElemType IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT costelem_id INTO _returnVal
  FROM costelem
  WHERE (costelem_type=pCostElemType);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Cost Element % not found.', pCostElemType;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1680 (class 1255 OID 146566259)
-- Dependencies: 4536 8
-- Name: getcrmacctid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getcrmacctid(pacctnumber text) RETURNS integer
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _returnVal INTEGER;
BEGIN
  
  IF (pAcctNumber IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT crmacct_id INTO _returnVal
  FROM crmacct
  WHERE (UPPER(crmacct_number)=UPPER(pAcctNumber));
  
  IF (_returnVal IS NULL) THEN
      RAISE EXCEPTION 'CRM Account Number % not found.', pAcctNumber;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getcrmacctid(pacctnumber text) OWNER TO admin;

--
-- TOC entry 1681 (class 1255 OID 146566260)
-- Dependencies: 4536 8
-- Name: getcurrid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getcurrid(pcurrname text) RETURNS integer
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _returnVal INTEGER;
BEGIN
  IF (pCurrName IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT curr_id INTO _returnVal
  FROM curr_symbol
  WHERE (curr_abbr=pCurrName);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Currency % not found.', pCurrName;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getcurrid(pcurrname text) OWNER TO admin;

--
-- TOC entry 1682 (class 1255 OID 146566261)
-- Dependencies: 4536 8
-- Name: getcustid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getcustid(pcustnumber text) RETURNS integer
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _returnVal INTEGER;
BEGIN
  
  SELECT getCustId(pCustNumber,false) INTO _returnVal;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getcustid(pcustnumber text) OWNER TO admin;

--
-- TOC entry 1683 (class 1255 OID 146566262)
-- Dependencies: 4536 8
-- Name: getcustid(text, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getcustid(pcustnumber text, pinclprospects boolean) RETURNS integer
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _returnVal INTEGER;
BEGIN
  IF (pCustNumber IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT cust_id INTO _returnVal
  FROM custinfo
  WHERE (cust_number=UPPER(pCustNumber));
  
  IF (_returnVal IS NULL) THEN
    IF (pInclProspects) THEN
      SELECT prospect_id INTO _returnVal
      FROM prospect
      WHERE (UPPER(prospect_number)=UPPER(pCustNumber));
      IF (_returnVal IS NULL) THEN
        RAISE EXCEPTION 'Neither Customer nor Prospect Number % found.', pCustNumber;
      END IF;
    ELSE
      RAISE EXCEPTION 'Customer Number % not found.', pCustNumber;
    END IF;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getcustid(pcustnumber text, pinclprospects boolean) OWNER TO admin;

--
-- TOC entry 1673 (class 1255 OID 146566263)
-- Dependencies: 4536 8
-- Name: getcustnamefrominfo(text, text, text, text, text, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getcustnamefrominfo(text, text, text, text, text, boolean) RETURNS text
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _email	TEXT	:= COALESCE(UPPER($1), '');
  _company	TEXT	:= COALESCE(UPPER($2), '');
  _first	TEXT	:= COALESCE(UPPER($3), '');
  _last		TEXT	:= COALESCE(UPPER($4), '');
  _fullname	TEXT	:= COALESCE(UPPER($5), '');
  _generate	BOOLEAN	:= COALESCE($6, FALSE);
  _counter	INTEGER;
  _custcount	INTEGER	:= 0;
  _custname	TEXT;
  _candidate	RECORD;
  _r		RECORD;
BEGIN
  IF (_email != '') THEN
    SELECT count(*), cust_name INTO _custcount, _custname
    FROM custinfo LEFT OUTER JOIN cntct ON (cust_cntct_id=cntct_id)
    WHERE (UPPER(cntct_email)=_email)
    GROUP BY cust_name;
    IF (NOT FOUND) THEN
      _custcount := 0;
    ELSIF(_custcount = 1) THEN
      RETURN _custname;
    END IF;
  END IF;

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

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

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

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

  IF (_custcount <= 0 AND _generate) THEN
    IF (_company != '') THEN
      RETURN _company;
    ELSIF (_email != '') THEN
      RETURN _email;
    ELSIF (_fullname != '') THEN
      RETURN _fullname;
    ELSE
      RAISE EXCEPTION 'Could not generate a new Customer Name without an email address or the name of a company or person';
    END IF;
  END IF;

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

  RETURN _custname;
END;
$_$;


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

--
-- TOC entry 1685 (class 1255 OID 146566264)
-- Dependencies: 4536 8
-- Name: getcustnumberfrominfo(text, text, text, text, text, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getcustnumberfrominfo(text, text, text, text, text, boolean) RETURNS text
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2012 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _email	TEXT	:= COALESCE(UPPER($1), '');
  _company	TEXT	:= COALESCE(UPPER($2), '');
  _first	TEXT	:= COALESCE(UPPER($3), '');
  _last		TEXT	:= COALESCE(UPPER($4), '');
  _fullname	TEXT	:= COALESCE(UPPER($5), TRIM(_first || ' ' || _last));
  _generate	BOOLEAN	:= COALESCE($6, FALSE);
  _counter	INTEGER;
  _custcount	INTEGER	:= 0;
  _custnumber	TEXT;
  _candidate	TEXT	:= '';
  _loopmax	INTEGER := 0;
  _minlength	INTEGER := 5;
  _maxlength	INTEGER := 8;
  _numformat	TEXT	:= '';
  _testme	TEXT;
BEGIN
  IF (_email != '') THEN
    SELECT count(*), cust_number INTO _custcount, _custnumber
    FROM custinfo LEFT OUTER JOIN cntct ON (cust_cntct_id=cntct_id)
    WHERE (UPPER(cntct_email)=_email)
    GROUP BY cust_number;
    IF (NOT FOUND) THEN
      _custcount := 0;
    ELSIF(_custcount = 1) THEN
      RETURN _custnumber;
    END IF;
  END IF;

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

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

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

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

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

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

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

  RETURN _custnumber;
END;
$_$;


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

--
-- TOC entry 1686 (class 1255 OID 146566265)
-- Dependencies: 4536 8
-- Name: getcusttypeid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getcusttypeid(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
  pCustTypeCode ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pCustTypeCode IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT custtype_id INTO _returnVal
  FROM custtype
  WHERE (custtype_code=pCustTypeCode);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Customer Type % not found.', pCustTypeCode;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1687 (class 1255 OID 146566266)
-- Dependencies: 4536 8
-- Name: getdeptid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getdeptid(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
  pDeptNumber ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (COALESCE(TRIM(pDeptNumber), '') = '') THEN
    RETURN NULL;
  END IF;

  SELECT dept_id INTO _returnVal
  FROM dept
  WHERE (UPPER(dept_number)=UPPER(pDeptNumber));

  IF (_returnVal IS NULL) THEN
    RAISE EXCEPTION 'Department % not found.', pDeptNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1688 (class 1255 OID 146566267)
-- Dependencies: 4536 8
-- Name: getediprofileid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getediprofileid(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
  pEdiProfileName ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pEdiProfileName IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT pkghead_id INTO _returnVal
    FROM pkghead
   WHERE(pkghead_name='xtbatch');
  IF(NOT FOUND) THEN
    RETURN NULL;
  END IF;

  SELECT ediprofile_id INTO _returnVal
  FROM xtbatch.ediprofile
  WHERE (ediprofile_name=pEdiProfileName);

  IF (_returnVal IS NULL) THEN
    RAISE EXCEPTION 'EDI Profile % not found.', pEdiProfileName;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1689 (class 1255 OID 146566268)
-- Dependencies: 4536 8
-- Name: getediprofilename(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getediprofilename(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
  pEdiProfileId ALIAS FOR $1;
  _returnVal TEXT;
BEGIN
  IF (pEdiProfileId IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT pkghead_name INTO _returnVal
    FROM pkghead
   WHERE(pkghead_name='xtbatch');
  IF(NOT FOUND) THEN
    RETURN NULL;
  END IF;

  SELECT ediprofile_name INTO _returnVal
  FROM xtbatch.ediprofile
  WHERE (ediprofile_id=pEdiProfileId);

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1690 (class 1255 OID 146566269)
-- Dependencies: 4536 8
-- Name: getempid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getempid(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
  pEmpCode ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (COALESCE(TRIM(pEmpCode), '') = '') THEN
    RETURN NULL;
  END IF;

  SELECT emp_id INTO _returnVal
  FROM emp
  WHERE (UPPER(emp_code)=UPPER(pEmpCode));

  IF (_returnVal IS NULL) THEN
    RAISE EXCEPTION 'Employee % not found.', pEmpCode;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1691 (class 1255 OID 146566270)
-- Dependencies: 4536 8
-- Name: getexpcatid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getexpcatid(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
  pExpcatCode ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (COALESCE(TRIM(pExpcatCode), '') = '') THEN
    RETURN NULL;
  END IF;

  SELECT expcat_id INTO _returnVal
  FROM expcat
  WHERE (expcat_code=UPPER(pExpcatCode));

  IF (_returnVal IS NULL) THEN
    RAISE EXCEPTION 'Expense Category % not found.', pExpcatCode;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1692 (class 1255 OID 146566271)
-- Dependencies: 2666 4536 8
-- Name: getflcoldata(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getflcoldata(integer, integer) RETURNS SETOF flcoldata
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pFlcolid ALIAS FOR $1;
  pPeriodid ALIAS FOR $2;
  _row flcoldata%ROWTYPE;
  _r RECORD;
  _start DATE;
  _end DATE;
  _col INTEGER := 1;
  _mult INTEGER;

BEGIN

--Get Layout Data
  SELECT * INTO _r
  FROM flcol
  WHERE (flcol_id=pFlcolid);

-- Handle Month...
  IF (_r.flcol_month) THEN
    SELECT period_start, period_end INTO _start, _end
    FROM period
    WHERE (period_id=pPeriodid);
    
    IF (_r.flcol_showdb) THEN
      -- Debits Column
      _row.flcoldata_column := _col;
      _row.flcoldata_start := _start;
      _row.flcoldata_end := _end;
      RETURN NEXT _row;
      _col := _col + 1;

      -- Credits Column
      _row.flcoldata_column := _col;
      _row.flcoldata_start := _start;
      _row.flcoldata_end := _end;
      RETURN NEXT _row;
      _col := _col + 1;
    END IF;

    -- Month Column
    _row.flcoldata_column := _col;
    _row.flcoldata_start := _start;
    _row.flcoldata_end := _end;
    RETURN NEXT _row;
    _col := _col + 1;

    -- These don't have drill down
    IF (_r.flcol_prcnt) THEN 
      _col := _col + 1;
    END IF;
    IF (_r.flcol_budget) THEN
      _col := _col + 1;
      IF (_r.flcol_budgetprcnt) THEN
        _col := _col + 1;
      END IF;
      IF (_r.flcol_budgetdiff) THEN
        _col := _col + 1;
      END IF;
      IF (_r.flcol_budgetdiffprcnt) THEN
        _col := _col + 1;
      END IF;
    END IF; 
  END IF;

-- Handle Quarter...
  IF (_r.flcol_quarter) THEN
    SELECT min(qtr.period_start), max(qtr.period_end) INTO _start, _end
    FROM period p
     JOIN period qtr ON (p.period_quarter=qtr.period_quarter)
                     AND (p.period_yearperiod_id=qtr.period_yearperiod_id)
    WHERE (p.period_id=pPeriodid);
    
    IF (_r.flcol_showdb) THEN
      -- Debits Column
      _row.flcoldata_column := _col;
      _row.flcoldata_start := _start;
      _row.flcoldata_end := _end;
      RETURN NEXT _row;
      _col := _col + 1;

      -- Credits Column
      _row.flcoldata_column := _col;
      _row.flcoldata_start := _start;
      _row.flcoldata_end := _end;
      RETURN NEXT _row;
      _col := _col + 1;
    END IF;

    -- Quarter Column
    _row.flcoldata_column := _col;
    _row.flcoldata_start := _start;
    _row.flcoldata_end := _end;
    RETURN NEXT _row;
    _col := _col + 1;

    -- These don't have drill down
    IF (_r.flcol_prcnt) THEN 
      _col := _col + 1;
    END IF;
    IF (_r.flcol_budget) THEN
      _col := _col + 1;
      IF (_r.flcol_budgetprcnt) THEN
        _col := _col + 1;
      END IF;
      IF (_r.flcol_budgetdiff) THEN
        _col := _col + 1;
      END IF;
      IF (_r.flcol_budgetdiffprcnt) THEN
        _col := _col + 1;
      END IF;
    END IF; 
  END IF;

-- Handle Year...
  IF (_r.flcol_year) THEN
    SELECT yearperiod_start, period_end INTO _start, _end
    FROM period p
     JOIN yearperiod ON (period_yearperiod_id=yearperiod_id)
    WHERE (p.period_id=pPeriodid);
    
    IF (_r.flcol_showdb) THEN
      -- Debits Column
      _row.flcoldata_column := _col;
      _row.flcoldata_start := _start;
      _row.flcoldata_end := _end;
      RETURN NEXT _row;
      _col := _col + 1;

      -- Credits Column
      _row.flcoldata_column := _col;
      _row.flcoldata_start := _start;
      _row.flcoldata_end := _end;
      RETURN NEXT _row;
      _col := _col + 1;
    END IF;

    -- Year Column
    _row.flcoldata_column := _col;
    _row.flcoldata_start := _start;
    _row.flcoldata_end := _end;
    RETURN NEXT _row;
    _col := _col + 1;

    -- These don't have drill down
    IF (_r.flcol_prcnt) THEN 
      _col := _col + 1;
    END IF;
    IF (_r.flcol_budget) THEN
      _col := _col + 1;
      IF (_r.flcol_budgetprcnt) THEN
        _col := _col + 1;
      END IF;
      IF (_r.flcol_budgetdiff) THEN
        _col := _col + 1;
      END IF;
      IF (_r.flcol_budgetdiffprcnt) THEN
        _col := _col + 1;
      END IF;
    END IF;
  END IF;

  -- Handle Prior Month...
  IF (_r.flcol_priormonth) THEN
    SELECT prv.period_start, prv.period_end INTO _start, _end
    FROM period p
      JOIN period prv ON (prv.period_start < p.period_start)
    WHERE (p.period_id=pPeriodid)
    ORDER BY prv.period_start DESC
    LIMIT 1;

    -- Prior Month Column
    _row.flcoldata_column := _col;
    _row.flcoldata_start := _start;
    _row.flcoldata_end := _end;
    RETURN NEXT _row;
    _col := _col + 1;

    -- These don't have drill down
    IF (_r.flcol_priorprcnt) THEN 
      _col := _col + 1;
    END IF;
    IF (_r.flcol_priordiff) THEN 
      _col := _col + 1;
    END IF;
    IF (_r.flcol_priordiffprcnt) THEN 
      _col := _col + 1;
    END IF;
  END IF;

-- Handle Prior Quarter...
  IF (_r.flcol_priorquarter) THEN
    IF (_r.flcol_priortype = 'P') THEN
      -- Prior Quarter
      SELECT min(period_start), max(period_end)
      INTO _start, _end
      FROM (
        SELECT prv.period_start, prv.period_end, prv.period_quarter, prv.period_yearperiod_id
        FROM period p
          JOIN period prv ON (prv.period_start < p.period_start)
                          AND (prv.period_quarter != p.period_quarter)
        WHERE (p.period_id=pPeriodid)) data
      GROUP BY period_quarter, period_yearperiod_id
      ORDER BY min(period_start) DESC
      LIMIT 1;
    ELSE
      -- Prior Year Quarter
      SELECT min(period_start), max(period_end)
      INTO _start, _end
      FROM (
        SELECT prv.period_start, prv.period_end, prv.period_quarter, prv.period_yearperiod_id
        FROM period p
          JOIN period prv ON (prv.period_start < p.period_start)
                          AND (prv.period_yearperiod_id != p.period_yearperiod_id)
                          AND (prv.period_quarter = p.period_quarter)
        WHERE (p.period_id=pPeriodid)) data
      GROUP BY period_quarter, period_yearperiod_id
      ORDER BY min(period_start) DESC
      LIMIT 1;
    END IF;

    -- Prior Quarter Column
    _row.flcoldata_column := _col;
    _row.flcoldata_start := _start;
    _row.flcoldata_end := _end;
    RETURN NEXT _row;
    _col := _col + 1;

    -- These don't have drill down
    IF (_r.flcol_priorprcnt) THEN 
      _col := _col + 1;
    END IF;
    IF (_r.flcol_priordiff) THEN 
      _col := _col + 1;
    END IF;
    IF (_r.flcol_priordiffprcnt) THEN 
      _col := _col + 1;
    END IF;
  END IF;

  -- Handle Prior Year...
  IF (_r.flcol_prioryear IN ('D','F')) THEN
    IF (_r.flcol_prioryear = 'D') THEN
      -- Prior Year to Date
      SELECT yearperiod_start, prv.period_end INTO _start, _end
      FROM period p
        JOIN period prv ON (prv.period_number = p.period_number)
                        AND (prv.period_yearperiod_id != p.period_yearperiod_id)
                        AND (prv.period_start < p.period_start)
        JOIN yearperiod ON (prv.period_yearperiod_id=yearperiod_id)   
      WHERE (p.period_id=pPeriodid)
      ORDER BY prv.period_start DESC
      LIMIT 1;
    ELSE
      -- Prior Full Year
      SELECT prv.yearperiod_start, prv.yearperiod_end INTO _start, _end
      FROM period p
        JOIN yearperiod cur ON (cur.yearperiod_id=p.period_yearperiod_id)
        JOIN yearperiod prv ON (prv.yearperiod_start < cur.yearperiod_start)
      WHERE (p.period_id=pPeriodid)
      ORDER BY prv.yearperiod_start DESC
      LIMIT 1;
    END IF;

    -- Prior Year Column
    _row.flcoldata_column := _col;
    _row.flcoldata_start := _start;
    _row.flcoldata_end := _end;
    RETURN NEXT _row;
    _col := _col + 1;

  END IF;

  RETURN;

END;
$_$;


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

--
-- TOC entry 1694 (class 1255 OID 146566272)
-- Dependencies: 2666 4536 8
-- Name: getflcoldata(character, integer[], boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getflcoldata(character, integer[], boolean) RETURNS SETOF flcoldata
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pInterval ALIAS FOR $1;
  pPeriodids ALIAS FOR $2;
  pBudgets ALIAS FOR $3;
  _row flcoldata%ROWTYPE;
  _r RECORD;
  _start DATE;
  _end DATE;
  _col INTEGER := 1;
  _count INTEGER;
  _i INTEGER := 1;
  _incr INTEGER := 1;

BEGIN

  IF (pBudgets) THEN
    _col := 2;
    _incr := 2;
  END IF;
  
  _count := ARRAY_UPPER(pPeriodIds,1);
  
  IF (pInterval = 'M') THEN
    FOR _i IN 1.._count
    LOOP
      SELECT period_start, period_end INTO _start, _end
      FROM period
      WHERE (period_id=pPeriodids[_i]);

      _row.flcoldata_column := _col;
      _row.flcoldata_start := _start;
      _row.flcoldata_end := _end;
      RETURN NEXT _row;
      _col := _col + _incr;
    END LOOP;
  ELSIF (pInterval = 'Q') THEN
    FOR _i IN 1.._count
    LOOP
      SELECT min(qtr.period_start), max(qtr.period_end) INTO _start, _end
      FROM period cur
        JOIN period qtr ON (cur.period_yearperiod_id=qtr.period_yearperiod_id)
                        AND (cur.period_quarter=qtr.period_quarter)
      WHERE (cur.period_id=pPeriodids[_i]);

      _row.flcoldata_column := _col;
      _row.flcoldata_start := _start;
      _row.flcoldata_end := _end;
      RETURN NEXT _row;
      _col := _col + _incr;
    END LOOP;
  ELSE
    FOR _i IN 1.._count
    LOOP
      SELECT yearperiod_start, yearperiod_end INTO _start, _end
      FROM period
        JOIN yearperiod ON (period_yearperiod_id=yearperiod_id)
      WHERE (period_id=pPeriodids[_i]);

      _row.flcoldata_column := _col;
      _row.flcoldata_start := _start;
      _row.flcoldata_end := _end;
      RETURN NEXT _row;
      _col := _col + _incr;
    END LOOP;
  END IF;
  RETURN;

END;
$_$;


ALTER FUNCTION public.getflcoldata(character, integer[], boolean) OWNER TO admin;

--
-- TOC entry 1695 (class 1255 OID 146566273)
-- Dependencies: 2669 4536 8
-- Name: getflstmthead(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getflstmthead(integer, integer) RETURNS SETOF flstmthead
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pFlcolid ALIAS FOR $1;
  pPeriodid ALIAS FOR $2;
  _row flstmthead%ROWTYPE;
  _p RECORD;
  _month TEXT;
  _qtr TEXT;
  _year TEXT;
  _prmonth TEXT;
  _prqtr TEXT;
  _pryear TEXT;
  _err TEXT;

BEGIN

  SELECT 'No Data' INTO _err;

--Get Layout Data
  SELECT flcol_priortype, flcol_prioryear INTO _p
  FROM flcol
  WHERE (flcol_id=pFlcolid);

--get data...
--...for current Month
        SELECT
          (CASE
                      WHEN period_name='' THEN
                        formatdate(period_start) || '-' || formatdate(period_end)
                      ELSE period_name
          END) INTO _month
        FROM period
        WHERE (period_id=pPeriodId);

        IF _month IS NULL THEN
          _month := _err;
        END IF;

--...for Quarter
        SELECT
          ('Q' || period_quarter || '-' || EXTRACT(year from yearperiod_end)) INTO _qtr
        FROM period, yearperiod
        WHERE ((period_id=pPeriodId)
        AND (period_yearperiod_id=yearperiod_id));

        IF _qtr IS NULL THEN
          _qtr := _err;
        END IF;

--...for Year
        SELECT
          COALESCE((CASE WHEN period_name='' THEN
                (formatdate(period_start) || '-' || formatdate(period_end) || ' YTD')
          ELSE (period_name || ' YTD')
          END),'No Data') INTO _year
        FROM period
        WHERE (period_id=pPeriodId);

        IF _year IS NULL THEN
          _year := _err;
        END IF;

--...for prior month

        IF (_p.flcol_priortype = 'P') THEN

          SELECT
            (CASE WHEN pp.period_name='' THEN
              formatdate(pp.period_start) || '-' || formatdate(pp.period_end)
            ELSE pp.period_name END) INTO _prmonth
          FROM period cp, period pp
          WHERE ((cp.period_id=pPeriodId)
          AND (cp.period_start > pp.period_start))
          ORDER BY pp.period_start DESC LIMIT 1;

        ELSE

          SELECT
            (CASE WHEN pp.period_name='' THEN
              formatdate(pp.period_start) || '-' || formatdate(pp.period_end)
            ELSE pp.period_name END) INTO _prmonth
          FROM period cp, period pp
          WHERE ((cp.period_id=pPeriodId)
           AND (cp.period_id != pp.period_id)
           AND (cp.period_start > pp.period_start)
           AND (cp.period_number = pp.period_number))
          ORDER BY pp.period_start DESC LIMIT 1;

        END IF;

          IF _prmonth IS NULL THEN
            _prmonth := _err;
          END IF;


--...for prior quarter

        IF (_p.flcol_priortype='P') THEN

          SELECT ('Q' || pp.period_quarter || '-' || EXTRACT(year from yearperiod_end)) INTO _prqtr
          FROM period cp, period pp, yearperiod
          WHERE ((cp.period_id=pPeriodId)
          AND (cp.period_start > pp.period_start)
          AND (pp.period_quarter=
            CASE WHEN cp.period_quarter > 1 THEN
              cp.period_quarter - 1
          ELSE 4 END)
          AND (pp.period_start >= cp.period_start - interval '1 year')
          AND (pp.period_yearperiod_id=yearperiod_id))
          ORDER BY pp.period_start DESC LIMIT 1;

        ELSE

          SELECT
            ('Q' || pp.period_quarter || '-' || EXTRACT(year from pp.period_start)) INTO _prqtr
          FROM period cp, period pp, yearperiod cy, yearperiod py
          WHERE ((cp.period_id=pPeriodId)
          AND (cp.period_yearperiod_id=cy.yearperiod_id)
          AND (pp.period_yearperiod_id=py.yearperiod_id)
          AND (cp.period_quarter=pp.period_quarter)
          AND (cy.yearperiod_start > py.yearperiod_start))
          ORDER BY py.yearperiod_start DESC, pp.period_start DESC LIMIT 1;

        END IF;

        IF _prqtr IS NULL THEN
          _prqtr := _err;
        END IF;

--...for prior year

        IF (_p.flcol_prioryear='F') THEN

          SELECT (EXTRACT(year from py.yearperiod_end)||'') INTO _pryear
          FROM period cp, yearperiod cy, yearperiod py
          WHERE ((cp.period_id=pPeriodId)
           AND (cp.period_yearperiod_id = cy.yearperiod_id)
           AND (cy.yearperiod_start > py.yearperiod_start))
          ORDER BY py.yearperiod_start DESC LIMIT 1;

        ELSE

          SELECT
          (CASE
                      WHEN pp.period_name='' THEN
                        formatdate(pp.period_start) || '-' || formatdate(pp.period_end) || ' YTD'
                      ELSE pp.period_name || ' YTD'
          END) INTO _pryear
          FROM period cp, period pp
          WHERE ((cp.period_id=pPeriodId)
            AND (cp.period_number = pp.period_number)
            AND (cp.period_start > pp.period_start))
          ORDER BY pp.period_start DESC LIMIT 1;

        END IF;

        IF _pryear IS NULL THEN
          _pryear := _err;
        END IF;

-- RETURN RESULTS

        SELECT
                flhead_id AS flstmthead_flhead_id,
                flcol_id AS flstmthead_flcol_id,
                pPeriodid AS flstmthead_period,
                getEffectiveXtUser() AS flstmthead_username,
                CASE
                        WHEN flhead_type = 'I' THEN 'Income Statement'
                        WHEN flhead_type = 'B' THEN 'Balance Sheet'
                        WHEN flhead_type = 'C' THEN 'Cash Flow Statement'
                        ELSE 'N/A'
                END AS flstmthead_flhead_typedescrip1,
                CASE
                        WHEN flhead_type = 'I' THEN 'Income'
                        WHEN flhead_type = 'B' THEN 'Balance'
                        WHEN flhead_type = 'C' THEN 'Cash'
                        ELSE 'N/A'
                END AS flstmthead_flhead_typedescrip2,
                flhead_name AS flstmthead_flhead_name,
                flcol_name AS flstmthead_flcol_name,
                _month AS flstmthead_month,
                _qtr AS flstmthead_qtr,
                _year AS flstmthead_year,
                _prmonth AS flstmthead_prmonth,
                _prqtr AS flstmthead_prqtr,
                _pryear AS flstmthead_pryear INTO _p
        FROM flhead,flcol
        WHERE ((flcol_id=pFlcolid)
        AND (flhead_id=flcol_flhead_id));

                _row.flstmthead_flhead_id := _p.flstmthead_flhead_id;
                _row.flstmthead_flcol_id := _p.flstmthead_flcol_id;
                _row.flstmthead_period_id := _p.flstmthead_period;
                _row.flstmthead_username := _p.flstmthead_username;
                _row.flstmthead_typedescrip1 := _p.flstmthead_flhead_typedescrip1;
                _row.flstmthead_typedescrip2 := _p.flstmthead_flhead_typedescrip2;
                _row.flstmthead_flhead_name := _p.flstmthead_flhead_name;
                _row.flstmthead_flcol_name := _p.flstmthead_flcol_name;
                _row.flstmthead_month := _p.flstmthead_month;
                _row.flstmthead_qtr := _p.flstmthead_qtr;
                _row.flstmthead_year := _p.flstmthead_year;
                _row.flstmthead_prmonth := _p.flstmthead_prmonth;
                _row.flstmthead_prqtr := _p.flstmthead_prqtr;
                _row.flstmthead_pryear := _p.flstmthead_pryear;

        RETURN NEXT _row;

END;
$_$;


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

--
-- TOC entry 1696 (class 1255 OID 146566274)
-- Dependencies: 2675 4536 8
-- Name: getfltrendhead(integer, integer[], character); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getfltrendhead(integer, integer[], character) RETURNS SETOF fltrendhead
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pFlheadid ALIAS FOR $1;
  pPeriodids ALIAS FOR $2;
  pInterval ALIAS FOR $3;
  _row fltrendhead%ROWTYPE;
  _p RECORD;
  _count INTEGER;
  _i INTEGER;
  _t TEXT;
  _fld TEXT[];
  _type CHAR;

BEGIN

-- Validate Interval
   IF pInterval <> 'M' AND pInterval <> 'Q' AND pInterval <> 'Y' THEN
     RAISE EXCEPTION 'Invalid Interval --> %', pInterval;
   END IF;

   IF ARRAY_UPPER(pPeriodIds,1) <= 12 THEN
        _count := ARRAY_UPPER(pPeriodIds,1);
   ELSE
        _count := 12;
   END IF;

   SELECT flhead_type INTO _type FROM flhead WHERE flhead_id = pFlheadId;

--get data...
--...for Month
        IF (pInterval = 'M') THEN
                FOR _i IN 1.._count
                LOOP
                        SELECT
                        (CASE
                                WHEN period_name='' THEN
                                        formatdate(period_start) || '-' || formatdate(period_end)
                                ELSE period_name
                        END) INTO _t
                        FROM period
                        WHERE (period_id=pPeriodIds[_i]);

                        _fld[_i] := _t;

                END LOOP;

--...for Quarter
                ELSE IF (pInterval = 'Q') THEN
                        FOR _i IN 1.._count
                        LOOP
                                SELECT
                                        ('Q' || period_quarter || '-' || EXTRACT(year from yearperiod_end)) INTO _t
                                FROM period, yearperiod
                                WHERE ((period_id=pPeriodIds[_i])
                                AND (period_yearperiod_id=yearperiod_id));

                                        _fld[_i] := _t;

                        END LOOP;
--...for Year
                ELSE
                        FOR _i IN 1.._count
                        LOOP
                                SELECT (EXTRACT(year from yearperiod_end)||'') INTO _t
                                FROM period, yearperiod
                                WHERE ((period_id=pPeriodIds[_i])
                                AND (period_yearperiod_id=yearperiod_id));

                                _fld[_i] := _t;

                        END LOOP;
                END IF;
        END IF;


-- RETURN RESULTS

        SELECT
                flhead_id AS fltrendhead_flhead_id,
                getEffectiveXtUser() AS fltrendhead_username,
                CASE
                        WHEN flhead_type = 'I' THEN 'Income Statement'
                        WHEN flhead_type = 'B' THEN 'Balance Sheet'
                        WHEN flhead_type = 'C' THEN 'Cash Flow Statement'
                        ELSE 'Ad Hoc'
                END AS fltrendhead_flhead_typedescrip,
                flhead_name AS fltrendhead_flhead_name INTO _p
        FROM flhead
        WHERE (flhead_id=pFlheadId);

                _row.fltrendhead_flhead_id := _p.fltrendhead_flhead_id;
                _row.fltrendhead_username := _p.fltrendhead_username;
               _row.fltrendhead_typedescrip := _p.fltrendhead_flhead_typedescrip;
                _row.fltrendhead_flhead_name := _p.fltrendhead_flhead_name;
                _row.fltrendhead_fld1 := _fld[1];
                _row.fltrendhead_fld2 := _fld[2];
                _row.fltrendhead_fld3 := _fld[3];
                _row.fltrendhead_fld4 := _fld[4];
                _row.fltrendhead_fld5 := _fld[5];
                _row.fltrendhead_fld6 := _fld[6];
                _row.fltrendhead_fld7 := _fld[7];
                _row.fltrendhead_fld8 := _fld[8];
                _row.fltrendhead_fld9 := _fld[9];
                _row.fltrendhead_fld10 := _fld[10];
                _row.fltrendhead_fld11 := _fld[11];
                _row.fltrendhead_fld12 := _fld[12];
                IF (_type IN ('I','C')) THEN
                        _row.fltrendhead_grndttl := 'Total';
                END IF;

        RETURN NEXT _row;

END;
$_$;


ALTER FUNCTION public.getfltrendhead(integer, integer[], character) OWNER TO admin;

--
-- TOC entry 1697 (class 1255 OID 146566275)
-- Dependencies: 4536 8
-- Name: getfreightclassid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getfreightclassid(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
  pFreightClassCode ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pFreightClassCode IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT freightclass_id INTO _returnVal
  FROM freightclass
  WHERE (freightclass_code=pFreightClassCode);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Freight Class % not found.', pFreightClassCode;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1698 (class 1255 OID 146566276)
-- Dependencies: 4536 8
-- Name: getfreighttaxtypeid(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getfreighttaxtypeid() RETURNS integer
    LANGUAGE plpgsql IMMUTABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _taxtypeid INTEGER;
BEGIN
  SELECT taxtype_id
    INTO _taxtypeid
    FROM taxtype
   WHERE (taxtype_name='Freight');

  RETURN _taxtypeid;
END;
$$;


ALTER FUNCTION public.getfreighttaxtypeid() OWNER TO admin;

--
-- TOC entry 1699 (class 1255 OID 146566277)
-- Dependencies: 4536 8
-- Name: getgainlossaccntid(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getgainlossaccntid(integer) RETURNS integer
    LANGUAGE plpgsql STABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pAccntId ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF ( (pAccntId = 0) OR (pAccntId IS NULL) ) THEN
	RETURN 0;
  END IF;

  IF (fetchMetricValue('GLCompanySize') = 0) THEN
    _returnVal := fetchMetricValue('CurrencyGainLossAccount')::integer;
  ELSE
    SELECT company_gainloss_accnt_id INTO _returnVal
    FROM company
      JOIN accnt ON (company_number=accnt_company)
    WHERE (accnt_id=pAccntId);
  END IF;

  IF (_returnVal IS NULL) THEN
    RAISE EXCEPTION 'Currency Gain/Loss Account not found for %', formatGlAccountLong(pAccntId);
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1700 (class 1255 OID 146566278)
-- Dependencies: 4536 8
-- Name: getglaccntid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getglaccntid(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
  pGlAccnt ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pGlAccnt IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT accnt_id INTO _returnVal
  FROM accnt
  WHERE (formatglaccount(accnt_id)=pGlAccnt);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Account Number % not found.', pGlAccnt;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1701 (class 1255 OID 146566279)
-- Dependencies: 4536 8
-- Name: getglaccntid(text, text, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getglaccntid(text, 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
  pCompany ALIAS FOR $1;
  pProfit ALIAS FOR $2;
  pGlAccnt ALIAS FOR $3;
  pSub ALIAS FOR $4;
  _account TEXT;
  _returnVal INTEGER;
BEGIN
  IF (pGlAccnt IS NULL) THEN
	RETURN NULL;
  END IF;

  IF (pCompany is not null) THEN
    _account := pCompany || '-';
  END IF;

  IF (pProfit is not null) THEN
    _account := _account || pProfit || '-';
  END IF;
  IF (pGlAccnt is not null) THEN
    if (_account is null) then
	_account := pGlAccnt;
    else
	_account := _account || pGlAccnt;
    end if;
  END IF;

  IF (pSub is not null) THEN
    _account := _account || '-' || pSub;
  END IF;

  SELECT accnt_id INTO _returnVal
  FROM accnt
  WHERE (formatglaccount(accnt_id)=_account);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Account Number % not found.', _account;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1702 (class 1255 OID 146566280)
-- Dependencies: 4536 8
-- Name: getimageid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getimageid(pimagename text) RETURNS integer
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _returnVal INTEGER;
BEGIN
  IF (COALESCE(TRIM(pImageName), '') = '') THEN
    RETURN NULL;
  END IF;

  SELECT image_id INTO _returnVal
  FROM image
  WHERE (image_name=pImageName);

  IF (_returnVal IS NULL) THEN
    RAISE EXCEPTION 'Image % not found.', pImageName;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getimageid(pimagename text) OWNER TO admin;

--
-- TOC entry 1703 (class 1255 OID 146566281)
-- Dependencies: 4536 8
-- Name: getincdtcatid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getincdtcatid(pincdtcatname text) RETURNS integer
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _returnVal INTEGER;
BEGIN
  IF (pIncdtCatName IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT incdtcat_id INTO _returnVal
  FROM incdtcat
  WHERE (incdtcat_name=pIncdtCatName);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Incident Category Name % not found.', pIncdtCatName;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getincdtcatid(pincdtcatname text) OWNER TO admin;

--
-- TOC entry 1704 (class 1255 OID 146566282)
-- Dependencies: 4536 8
-- Name: getincdtcrmacctid(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getincdtcrmacctid(pincidentnumber integer) RETURNS integer
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _returnVal INTEGER;
BEGIN
  IF (pIncidentNumber IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT incdt_crmacct_id INTO _returnVal
  FROM incdt
  WHERE (incdt_number=pIncidentNumber);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Incident Number % not found.', pIncidentNumber;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getincdtcrmacctid(pincidentnumber integer) OWNER TO admin;

--
-- TOC entry 1705 (class 1255 OID 146566283)
-- Dependencies: 4536 8
-- Name: getincdtpriorityid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getincdtpriorityid(pincdtpriorityname text) RETURNS integer
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _returnVal INTEGER;
BEGIN
  IF (pIncdtPriorityName IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT incdtpriority_id INTO _returnVal
  FROM incdtpriority
  WHERE (incdtpriority_name=pIncdtPriorityName);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Incident Priority Name % not found.', pIncdtPriorityName;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getincdtpriorityid(pincdtpriorityname text) OWNER TO admin;

--
-- TOC entry 1706 (class 1255 OID 146566284)
-- Dependencies: 4536 8
-- Name: getincdtresolutionid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getincdtresolutionid(pincdtresolutionname text) RETURNS integer
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _returnVal INTEGER;
BEGIN
  IF (pIncdtResolutionName IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT incdtresolution_id INTO _returnVal
  FROM incdtresolution
  WHERE (incdtresolution_name=pIncdtResolutionName);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Incident Resolution Name % not found.', pIncdtResolutionName;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getincdtresolutionid(pincdtresolutionname text) OWNER TO admin;

--
-- TOC entry 1707 (class 1255 OID 146566285)
-- Dependencies: 4536 8
-- Name: getincdtseverityid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getincdtseverityid(pincdtseverityname text) RETURNS integer
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _returnVal INTEGER;
BEGIN
  IF (pIncdtSeverityName IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT incdtseverity_id INTO _returnVal
  FROM incdtseverity
  WHERE (incdtseverity_name=pIncdtSeverityName);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Incident Severity Name % not found.', pIncdtSeverityName;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getincdtseverityid(pincdtseverityname text) OWNER TO admin;

--
-- TOC entry 1708 (class 1255 OID 146566286)
-- Dependencies: 4536 8
-- Name: getincidentid(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getincidentid(pincidentnumber integer) RETURNS integer
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _returnVal INTEGER;
BEGIN
  IF (pIncidentNumber IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT incdt_id INTO _returnVal
  FROM incdt
  WHERE (incdt_number=pIncidentNumber);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Incident Number % not found.', pIncidentNumber;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getincidentid(pincidentnumber integer) OWNER TO admin;

--
-- TOC entry 1709 (class 1255 OID 146566287)
-- Dependencies: 4536 8
-- Name: getinvcheadid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getinvcheadid(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
  pInvcNumber ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pInvcNumber IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT invchead_id INTO _returnVal
  FROM invchead
  WHERE (UPPER(invchead_invcnumber)=UPPER(pInvcNumber));

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Invoice % not found.', pInvcNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1710 (class 1255 OID 146566288)
-- Dependencies: 4536 8
-- Name: getinvcitemlotserial(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getinvcitemlotserial(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
  pInvcitemid ALIAS FOR $1;
  _lotserial text;
  _r RECORD;
  _first BOOLEAN;
  _newMethod BOOLEAN;
BEGIN
 
  --Test to see if Lot/Serial Enabled
  SELECT metric_value INTO _lotserial
  FROM metric
  WHERE ((metric_name='LotSerialControl')
  AND (metric_value ='t'));

  IF (FOUND) THEN
    _lotserial := '';
    _first := true;

--  Two ways of doing this: old method and new method
--  First, find out if new method employed.
--  (new method is more accurate, but unfortunately no
--  way to migrate or correct old data.  Have to keep
--  old method in case someone reprints an old invoice.)

    SELECT (COUNT(*) > 0) INTO _newMethod 
    FROM shipitem 
    WHERE ((shipitem_invcitem_id=pInvcitemid)
    AND (shipitem_invhist_id IS NOT NULL));

    IF (_newMethod) THEN

      FOR _r IN SELECT DISTINCT ls_number
                FROM invdetail, invhist, shipitem, ls
               WHERE ((shipitem_invcitem_id=pInvcitemid)
                 AND  (shipitem_invhist_id=invhist_id)
                 AND  (invhist_id=invdetail_invhist_id)
                 AND  (invdetail_ls_id=ls_id)) LOOP
        IF (_first = false) THEN
          _lotserial := _lotserial || ', ';
        END IF;
        _lotserial := _lotserial || _r.ls_number;
        _first := false;
      END LOOP;

      RETURN _lotserial;
    ELSE
      -- Handle it old way
      FOR _r IN SELECT DISTINCT ls_number
                FROM ls, invdetail JOIN invhist ON (invdetail_invhist_id=invhist_id)
               WHERE ((invhist_transtype='SH')
                 AND  (invdetail_invcitem_id=pInvcitemid)
                 AND  (invdetail_ls_id=ls_id)) LOOP
        IF (_first = false) THEN
          _lotserial := _lotserial || ', ';
        END IF;
        _lotserial := _lotserial || _r.ls_number;
        _first := false;
      END LOOP;

      RETURN _lotserial;
    END IF;
  ELSE
    RETURN '';
  END IF;
  
END
$_$;


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

--
-- TOC entry 1684 (class 1255 OID 146566289)
-- Dependencies: 4536 8
-- Name: getipsheadid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getipsheadid(pipsname 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
  _returnVal INTEGER;
BEGIN
  IF (pIpsName IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT ipshead_id INTO _returnVal
  FROM ipshead
  WHERE (ipshead_name=pIpsName);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Pricing Schedule % not found.', pIpsName;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getipsheadid(pipsname text) OWNER TO admin;

--
-- TOC entry 1712 (class 1255 OID 146566290)
-- Dependencies: 4536 8
-- Name: getipsitemid(text, text, numeric, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getipsitemid(pipsname text, pitemnumber text, pqtybreak numeric, pqtyuom text, ppriceuom 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
  _returnVal INTEGER;
  
BEGIN
  IF (pIpsName IS NULL AND pItemNumber IS NULL AND pQtyBreak IS NULL AND pQtyUom IS NULL AND pPriceUom IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT ipsitem_id INTO _returnVal
  FROM ipsiteminfo
  WHERE ((ipsitem_ipshead_id=getIpsheadId(pIpsName))
  AND (ipsitem_item_id=getItemId(pItemNumber))
  AND (ipsitem_qtybreak=pQtyBreak)
  AND (ipsitem_qty_uom_id=getUomId(pQtyUom))
  AND (ipsitem_price_uom_id=getUomId(pPriceUom)));

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Pricing Schedule Item for Schedule %, Item %,Qt Break %,Qty UOM %, Price UOM % not found.', 
	pIpsName, pItemNumber, pQtyBreak, pQtyUom, pPriceUom;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getipsitemid(pipsname text, pitemnumber text, pqtybreak numeric, pqtyuom text, ppriceuom text) OWNER TO admin;

--
-- TOC entry 1713 (class 1255 OID 146566291)
-- Dependencies: 4536 8
-- Name: getipsprodcatid(text, text, numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getipsprodcatid(pipsname text, pprodcat text, pqtybreak 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
  _returnVal INTEGER;
  
BEGIN
  IF (pIpsName IS NULL AND pProdCat IS NULL AND pQtyBreak IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT ipsitem_id INTO _returnVal
  FROM ipsiteminfo
  WHERE ((ipsitem_ipshead_id=getIpsheadId(pIpsName))
  AND (ipsitem_prodcat_id=getProdcatId(pProdCat))
  AND (ipsitem_qtybreak=pQtyBreak));

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Pricing Schedule Product Category for Schedule %, Product Category %,Qt Break % not found.', 
	pIpsName, pProdCat, pQtyBreak;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getipsprodcatid(pipsname text, pprodcat text, pqtybreak numeric) OWNER TO admin;

--
-- TOC entry 1714 (class 1255 OID 146566292)
-- Dependencies: 4536 8
-- Name: getitemid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getitemid(text) RETURNS integer
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pItemNumber ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pItemNumber IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT item_id INTO _returnVal
  FROM item
  WHERE (item_number=UPPER(pItemNumber));

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Item % not found.', pItemNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1715 (class 1255 OID 146566293)
-- Dependencies: 4536 8
-- Name: getitemidfromupc(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getitemidfromupc(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
  pItemUPC ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pItemUPC IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT item_id INTO _returnVal
  FROM item
  WHERE (item_upccode=UPPER(pItemUPC));

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Item % not found.', pItemUPC;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1716 (class 1255 OID 146566294)
-- Dependencies: 4536 8
-- Name: getitemsiteid(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getitemsiteid(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
  pWarehouseCode ALIAS FOR $1;
  pItemNumber ALIAS FOR $2;
  _returnVal INTEGER;
BEGIN
  SELECT getItemsiteId(pWarehouseCode,pItemNumber,'ALL') INTO _returnVal;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1717 (class 1255 OID 146566295)
-- Dependencies: 4536 8
-- Name: getitemsiteid(text, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getitemsiteid(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
  pWarehouseCode ALIAS FOR $1;
  pItemNumber ALIAS FOR $2;
  pType ALIAS FOR $3;
  _type TEXT;
  _p RECORD;
BEGIN
  IF ((pWarehouseCode IS NULL) OR (pItemNumber IS NULL)) THEN
	RETURN NULL;
  END IF;
 
  IF UPPER(pType) NOT IN ('ALL','ACTIVE','SOLD') THEN
    RAISE EXCEPTION 'Invalid Type %. Valid Itemsite types are ALL and SOLD', pType;
  END IF;

  SELECT * INTO _p
  FROM itemsite, item
  WHERE ((itemsite_item_id=item_id)
  AND (itemsite_warehous_id=getWarehousId(pWarehouseCode,'ALL'))
  AND (item_number=UPPER(pItemNumber)));

  IF NOT (FOUND) THEN
    RAISE EXCEPTION 'Item % not found in Warehouse %', pItemNumber, pWarehouseCode;
  ELSIF ((UPPER(pType)='ACTIVE') OR (UPPER(pType)='SOLD')) THEN
    IF NOT (_p.item_active) THEN
      RAISE EXCEPTION 'Item % is inactive.', pItemNumber;
    ELSE
      IF NOT (_p.itemsite_active) THEN
        RAISE EXCEPTION 'Item % is inactive in Warehouse %', pItemNumber, pWarehouseCode;
      ELSE
        IF ((UPPER(pType)='SOLD') AND NOT _p.item_sold) THEN
          RAISE EXCEPTION 'Item % is not sold', pItemNumber;
        ELSE
          IF ((UPPER(pType)='SOLD') AND NOT _p.itemsite_sold) THEN
            RAISE EXCEPTION 'Item % is not sold from Warehouse %', pItemNumber, pWarehouseCode;
          END IF;
        END IF;
      END IF;
    END IF;
  END IF;

  RETURN _p.itemsite_id;
END;
$_$;


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

--
-- TOC entry 1718 (class 1255 OID 146566296)
-- Dependencies: 4536 8
-- Name: getitemsrcid(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getitemsrcid(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
  pItemNumber ALIAS FOR $1;
  pVendNumber ALIAS FOR $2;
  _returnVal INTEGER;
BEGIN
  IF ((pItemNumber IS NULL) OR (pVendNumber IS NULL)) THEN
    RETURN NULL;
  END IF;

  SELECT itemsrc_id INTO _returnVal
  FROM itemsrc
  WHERE ((itemsrc_item_id=getItemId(pItemNumber))
  AND (itemsrc_vend_id=getVendId(pVendNumber)));

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Item Source Item % Vendor % not found.', pItemNumber,pVendNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1719 (class 1255 OID 146566297)
-- Dependencies: 4536 8
-- Name: getlasttrialbalid(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getlasttrialbalid(integer, integer) RETURNS integer
    LANGUAGE plpgsql STABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pAccntId ALIAS FOR $1;
  pPeriodId ALIAS FOR $2;
  _p RECORD;
  _accntType TEXT;
  _result NUMERIC;

BEGIN

  SELECT period_end,period_yearperiod_id INTO _p
  FROM period
  WHERE period_id=pPeriodId;

  SELECT accnt_type INTO _accntType
  FROM accnt
  WHERE accnt_id=pAccntId;

  IF (_accntType IN ('R','E')) THEN
        SELECT trialbal_id INTO _result
        FROM trialbal
        WHERE ((trialbal_accnt_id=pAccntId)
        AND (trialbal_period_id=pPeriodId));
  ELSE
        SELECT trialbal_id INTO _result
        FROM (SELECT trialbal_id
                FROM trialbal,period
                WHERE ((trialbal_accnt_id=pAccntId)
                AND (trialbal_period_id=period_id)
                AND (period_end <= _p.period_end)
                AND (period_yearperiod_id=_p.period_yearperiod_id))
                ORDER BY period_end DESC) AS data;
  END IF;

  RETURN _result;

END;
$_$;


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

--
-- TOC entry 1693 (class 1255 OID 146566298)
-- Dependencies: 4536 8
-- Name: getlocationid(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getlocationid(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
  pWarehouse ALIAS FOR $1;
  pLocation ALIAS FOR $2;
  _returnVal INTEGER;
BEGIN
  IF (pLocation IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT location_id INTO _returnVal
  FROM location
  WHERE ((location_warehous_id=getWarehousId(pWarehouse,'ACTIVE'))
  AND (formatLocationname(location_id)=pLocation))
  LIMIT 1;

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Location % not found in Warehouse %.', pLocation, pWarehouse;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1721 (class 1255 OID 146566299)
-- Dependencies: 4536 8
-- Name: getlotserialid(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getlotserialid(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
  pItemNumber ALIAS FOR $1;
  pLotSerialNumber ALIAS FOR $2;
  _returnVal INTEGER;
BEGIN
  IF ((pItemNumber IS NULL) OR (pLotSerialNumber IS NULL) OR (pLotSerialNumber='')) THEN
	RETURN NULL;
  END IF;

  SELECT ls_id INTO _returnVal
  FROM ls
  WHERE ((ls_item_id=getItemId(pItemNumber))
  AND (UPPER(ls_number)=UPPER(pLotSerialNumber)));

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'LotSerial % not found.', pLotSerialNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1722 (class 1255 OID 146566300)
-- Dependencies: 4536 8
-- Name: getopenshipment(text, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getopenshipment(pordertype text, porderid integer, pwarehousid integer) RETURNS text
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _result TEXT := '';

BEGIN

  IF (pOrderType = 'SO') THEN
    SELECT shiphead_number INTO _result
    FROM shiphead JOIN shipitem ON (shipitem_shiphead_id=shiphead_id)
                  JOIN coitem ON (coitem_id=shipitem_orderitem_id)
                  JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
    WHERE (shiphead_order_id=pOrderId)
      AND (shiphead_order_type='SO') 
      AND (NOT shiphead_shipped)
      AND (CASE WHEN (pWarehousId IS NULL) THEN TRUE
                ELSE itemsite_warehous_id=pWarehousId END) 
    ORDER BY shiphead_number
    LIMIT 1; 
  ELSEIF (pOrderType = 'TO') THEN
    SELECT shiphead_number INTO _result
    FROM shiphead JOIN tohead ON (tohead_id=shiphead_order_id)
    WHERE (shiphead_order_id=pOrderId)
      AND (shiphead_order_type='TO') 
      AND (NOT shiphead_shipped)
      AND (CASE WHEN (pWarehousId IS NULL) THEN TRUE
                ELSE tohead_src_warehous_id=pWarehousId END) 
    ORDER BY shiphead_number
    LIMIT 1; 
  END IF;

  RETURN _result;

END;
$$;


ALTER FUNCTION public.getopenshipment(pordertype text, porderid integer, pwarehousid integer) OWNER TO admin;

--
-- TOC entry 1723 (class 1255 OID 146566301)
-- Dependencies: 4536 8
-- Name: getopenshipmentid(text, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getopenshipmentid(pordertype text, porderid integer, pwarehousid integer) RETURNS integer
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _result INTEGER := -1;

BEGIN

  IF (pOrderType = 'SO') THEN
    SELECT shiphead_id INTO _result
    FROM shiphead JOIN shipitem ON (shipitem_shiphead_id=shiphead_id)
                  JOIN coitem ON (coitem_id=shipitem_orderitem_id)
                  JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
    WHERE (shiphead_order_id=pOrderId)
      AND (shiphead_order_type='SO') 
      AND (NOT shiphead_shipped)
      AND (CASE WHEN (pWarehousId IS NULL) THEN TRUE
                ELSE itemsite_warehous_id=pWarehousId END) 
    ORDER BY shiphead_number
    LIMIT 1; 
  ELSEIF (pOrderType = 'TO') THEN
    SELECT shiphead_id INTO _result
    FROM shiphead JOIN tohead ON (tohead_id=shiphead_order_id)
    WHERE (shiphead_order_id=pOrderId)
      AND (shiphead_order_type='TO') 
      AND (NOT shiphead_shipped)
      AND (CASE WHEN (pWarehousId IS NULL) THEN TRUE
                ELSE tohead_src_warehous_id=pWarehousId END) 
    ORDER BY shiphead_number
    LIMIT 1; 
  END IF;

  RETURN _result;

END;
$$;


ALTER FUNCTION public.getopenshipmentid(pordertype text, porderid integer, pwarehousid integer) OWNER TO admin;

--
-- TOC entry 1724 (class 1255 OID 146566302)
-- Dependencies: 4536 8
-- Name: getopheadid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getopheadid(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
  pOpHeadName ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  
  IF (pOpHeadName IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT ophead_id INTO _returnVal
  FROM ophead
  WHERE (UPPER(ophead_name)=UPPER(pOpHeadName));
  
  IF (_returnVal IS NULL) THEN
      RAISE EXCEPTION 'Opportunity % not found.', pOpHeadName;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1725 (class 1255 OID 146566303)
-- Dependencies: 4536 8
-- Name: getpacklistcharname(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getpacklistcharname(integer, 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
  pShipheadId ALIAS FOR $1;
  pOrderItemId ALIAS FOR $2;
  _charname text;
  _r RECORD;
  _first BOOLEAN;
  
BEGIN

-- If transfer order, get out
  SELECT shiphead_order_type INTO _r
  FROM shiphead
  WHERE ((shiphead_id=pShipheadId)
  AND (shiphead_order_type='TO'));
  
  IF (FOUND) THEN
    RETURN '';
  END IF;

  _charname := '';
  _first := true;

  FOR _r IN SELECT char_name
            FROM char, charass
            WHERE ((char_id=charass_char_id)
            AND (charass_target_type='SI')
            AND (charass_target_id=pOrderItemId)) 
  LOOP
        IF (_first = false) THEN
          _charname := _charname || '
';
        END IF;
        _charname := _charname || _r.char_name;
        _first := false;
  END LOOP;

  RETURN _charname;
  
END
$_$;


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

--
-- TOC entry 1726 (class 1255 OID 146566304)
-- Dependencies: 4536 8
-- Name: getpacklistcharvalue(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getpacklistcharvalue(integer, 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
  pShipheadId ALIAS FOR $1;
  pOrderItemId ALIAS FOR $2;
  _charval text;
  _r RECORD;
  _first BOOLEAN;
  
BEGIN

-- If transfer order, get out
  SELECT shiphead_order_type INTO _r
  FROM shiphead
  WHERE ((shiphead_id=pShipheadId)
  AND (shiphead_order_type='TO'));
  
  IF (FOUND) THEN
    RETURN '';
  END IF;

  _charval := '';
  _first := true;

  FOR _r IN SELECT charass_value
            FROM char, charass
            WHERE ((char_id=charass_char_id)
            AND (charass_target_type='SI')
            AND (charass_target_id=pOrderItemId)) 
  LOOP
        IF (_first = false) THEN
          _charval := _charval || '
';
        END IF;
        _charval := _charval || _r.charass_value;
        _first := false;
  END LOOP;

  RETURN _charval;
  
END
$_$;


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

--
-- TOC entry 1727 (class 1255 OID 146566305)
-- Dependencies: 4536 8
-- Name: getpacklistitemlotserial(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getpacklistitemlotserial(integer, 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
  pShipheadId ALIAS FOR $1;
  pOrderItemId ALIAS FOR $2;
  _lotserial text;
  _r RECORD;
  _first BOOLEAN;
  
BEGIN
 
  --Test to see if Lot/Serial Enabled
  SELECT metric_value INTO _lotserial
  FROM metric
  WHERE ((metric_name='LotSerialControl')
  AND (metric_value ='t'));

  IF (FOUND) THEN
    _lotserial := '';
    _first := true;

    FOR _r IN SELECT DISTINCT ls_number
              FROM invdetail, invhist, shipitem, ls
             WHERE ((shipitem_shiphead_id=pShipheadId)
               AND  (shipitem_orderitem_id=pOrderItemId)
               AND  (shipitem_invhist_id=invhist_id)
               AND  (invhist_id=invdetail_invhist_id)
               AND  (invdetail_ls_id=ls_id)) LOOP
      IF (_first = false) THEN
        _lotserial := _lotserial || ', ';
      END IF;
      _lotserial := _lotserial || _r.ls_number;
      _first := false;
    END LOOP;

    RETURN _lotserial;
  ELSE
    RETURN '';
  END IF;
  
END
$_$;


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

--
-- TOC entry 1730 (class 1255 OID 146566307)
-- Dependencies: 4536 8
-- Name: getperiodid(date); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getperiodid(date) 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
  pPeriodDate ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pPeriodDate IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT period_id INTO _returnVal
  FROM period
  WHERE ((pPeriodDate) between period_start AND period_end);

  IF (_returnVal IS NULL) THEN
    RAISE EXCEPTION 'Period for % not found.', pPeriodDate;
  END IF;

  RETURN _returnVal;
END;
$_$;


ALTER FUNCTION public.getperiodid(date) OWNER TO admin;

--
-- TOC entry 1729 (class 1255 OID 146566306)
-- Dependencies: 4536 8
-- Name: getperiodid(integer, character); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getperiodid(integer, character) RETURNS SETOF integer
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pPeriodId ALIAS FOR $1;
  pInterval ALIAS FOR $2;
  _x RECORD;
BEGIN

-- Validate Interval
   IF pInterval <> 'M' AND pInterval <> 'Q' AND pInterval <> 'Y' THEN
     RAISE EXCEPTION 'Invalid Interval --> %', pInterval;
   END IF;

   IF pInterval='M' THEN
       RETURN NEXT pPeriodId;
     ELSE IF pInterval='Q' THEN
        FOR _x IN SELECT qp.period_id AS period_id
                FROM period cp, period qp
                WHERE ((cp.period_id=pPeriodId)
                AND (cp.period_yearperiod_id=qp.period_yearperiod_id)
                AND (cp.period_quarter=qp.period_quarter)
                AND (cp.period_start>=qp.period_start))
        ORDER BY qp.period_start
        LOOP
                RETURN NEXT _x.period_id;
        END LOOP;
     ELSE
        FOR _x IN SELECT yp.period_id AS period_id
                FROM period cp, period yp
                WHERE ((cp.period_id=pPeriodId)
                AND (cp.period_yearperiod_id=yp.period_yearperiod_id)
                AND (cp.period_start>=yp.period_start))
        ORDER BY yp.period_start
        LOOP
                RETURN NEXT _x.period_id;
        END LOOP;
     END IF;
   END IF;
  RETURN;
END;
$_$;


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

--
-- TOC entry 1731 (class 1255 OID 146566308)
-- Dependencies: 4536 8
-- Name: getpkgheadid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getpkgheadid(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;
  _returnVal INTEGER;
BEGIN
  IF (ppkgname IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT pkghead_id INTO _returnVal
  FROM pkghead
  WHERE (UPPER(pkghead_name)=UPPER(ppkgname));

  IF (_returnVal IS NULL) THEN
    RAISE EXCEPTION 'Package % not found.', ppkgname;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1732 (class 1255 OID 146566309)
-- Dependencies: 4536 8
-- Name: getplancodeid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getplancodeid(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
  pPlanCode ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pPlanCode IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT plancode_id INTO _returnVal
  FROM plancode
  WHERE (plancode_code=pPlanCode);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Plan Code % not found.', pPlanCode;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1733 (class 1255 OID 146566310)
-- Dependencies: 4536 8
-- Name: getpoheadid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getpoheadid(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
  pPurchaseOrderNumber ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pPurchaseOrderNumber IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT pohead_id INTO _returnVal
  FROM pohead
  WHERE (pohead_number=pPurchaseOrderNumber);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Purchase Order % not found.', pPurchaseOrderNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1734 (class 1255 OID 146566311)
-- Dependencies: 4536 8
-- Name: getpoitemid(text, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getpoitemid(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
  pPurchaseOrderNumber	ALIAS FOR $1;
  pLineNumber 	    	ALIAS FOR $2;
  _returnVal 		INTEGER;
BEGIN
  IF (pPurchaseOrderNumber IS NULL OR pLineNumber IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT poitem_id INTO _returnVal
  FROM pohead, poitem
  WHERE ((pohead_number=pPurchaseOrderNumber)
  AND (poitem_pohead_id=pohead_id)
  AND (poitem_linenumber=pLineNumber));

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Purchase Order % not found.', pSalesOrderNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1735 (class 1255 OID 146566312)
-- Dependencies: 4536 8
-- Name: getprjaccntid(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getprjaccntid(integer, integer) RETURNS integer
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pPrjid ALIAS FOR $1;
  pAccntid ALIAS FOR $2;
  
BEGIN
  -- Project Accounting is required to fully implement this functionality
  RETURN pAccntId;
END;
$_$;


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

--
-- TOC entry 1736 (class 1255 OID 146566313)
-- Dependencies: 4536 8
-- Name: getprjid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getprjid(pprjnumber text) RETURNS integer
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _returnVal INTEGER;
BEGIN
  IF (pPrjNumber IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT prj_id INTO _returnVal
  FROM prj
  WHERE (prj_number=pPrjNumber);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Project Number % not found.', pPrjNumber;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getprjid(pprjnumber text) OWNER TO admin;

--
-- TOC entry 1737 (class 1255 OID 146566314)
-- Dependencies: 4536 8
-- Name: getprjtaskid(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getprjtaskid(pprjnumber text, ptasknumber text) RETURNS integer
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _returnVal INTEGER;
BEGIN
  IF (pPrjNumber IS NULL OR pTaskNumber IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT prjtask_id INTO _returnVal
  FROM prjtask
    JOIN prj ON (prj_id=prjtask_prj_id)
  WHERE ((prj_number=pPrjNumber)
  AND (prjtask_number=pTaskNumber));

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Project Task Number %-% not found.', pPrjNumber, pTaskNumber;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getprjtaskid(pprjnumber text, ptasknumber text) OWNER TO admin;

--
-- TOC entry 1738 (class 1255 OID 146566315)
-- Dependencies: 4536 8
-- Name: getprodcatid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getprodcatid(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
  pProdCat ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pProdCat IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT prodcat_id INTO _returnVal
  FROM prodcat
  WHERE (prodcat_code=pProdCat);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Product Category % not found.', pProdCat;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1739 (class 1255 OID 146566316)
-- Dependencies: 4536 8
-- Name: getprospectid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getprospectid(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
  pProspectNumber ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pProspectNumber IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT prospect_id INTO _returnVal
    FROM prospect
   WHERE(UPPER(prospect_number)=UPPER(pProspectNumber));
  IF (_returnVal IS NULL) THEN
    RAISE EXCEPTION 'Prospect Number % found.', pProspectNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1720 (class 1255 OID 146566317)
-- Dependencies: 4536 8
-- Name: getquoteid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getquoteid(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
  pQuoteNumber ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pQuoteNumber IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT quhead_id INTO _returnVal
  FROM quhead
  WHERE (quhead_number=pQuoteNumber);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Quote Number % not found.', pQuoteNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1740 (class 1255 OID 146566318)
-- Dependencies: 4536 8
-- Name: getquotelineitemid(text, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getquotelineitemid(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
  pQuoteNumber ALIAS FOR $1;
  pLineNumber ALIAS FOR $2;
  _returnVal INTEGER;
BEGIN
  IF ((pQuoteNumber IS NULL) OR (pLineNumber IS NULL)) THEN
    RETURN NULL;
  END IF;

  SELECT quitem_id INTO _returnVal
  FROM quhead, quitem
  WHERE ((quhead_number=pQuoteNumber)
  AND (quhead_id=quitem_quhead_id)
  AND (quitem_linenumber=pLineNumber));

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Quote Line Item %-%not found.', pQuoteNumber,pLineNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1741 (class 1255 OID 146566319)
-- Dependencies: 4536 8
-- Name: getquotescheddate(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getquotescheddate(integer) RETURNS date
    LANGUAGE plpgsql
    AS $_$
-- Copyright (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;
  _minscheddate DATE;

BEGIN

  SELECT MIN(quitem_scheddate) INTO _minscheddate
  FROM quitem
  WHERE (quitem_quhead_id=pQuheadid);

  RETURN _minscheddate;

END;
$_$;


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

--
-- TOC entry 1742 (class 1255 OID 146566320)
-- Dependencies: 4536 8
-- Name: getrevid(text, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getrevid(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
  pType ALIAS FOR $1;
  pItemNumber ALIAS FOR $2;
  pRevision ALIAS FOR $3;
  _returnVal INTEGER;
BEGIN
  IF (pItemNumber IS NULL) THEN
    RETURN NULL;
  END IF;

  IF (NOT fetchMetricBool('RevControl')) THEN
    RETURN -1;
  ELSIF ( (pRevision IS NULL) OR (LENGTH(pRevision)=0) ) THEN
    SELECT getActiveRevId(pType, getItemId(pItemNumber)) INTO _returnVal;
  ELSE
    IF (pType='BOM') THEN
      SELECT rev_id INTO _returnVal
      FROM item, bomhead, rev
      WHERE ((item_id=bomhead_item_id)
      AND (bomhead_rev_id=rev_id)
      AND (UPPER(item_number)=UPPER(pItemNumber))
      AND (rev_number=pRevision));
    ELSIF (pType='BOO') THEN
      SELECT rev_id INTO _returnVal
      FROM item, xtmfg.boohead, rev
      WHERE ((item_id=boohead_item_id)
      AND (boohead_rev_id=rev_id)
      AND (UPPER(item_number)=UPPER(pItemNumber))
      AND (rev_number=pRevision));   
    ELSE
      RAISE EXCEPTION 'Invalid Revision Type.';
    END IF;
  END IF;
    
  IF (_returnVal IS NULL) THEN
    RAISE EXCEPTION '% revision % for % not found.', pType, pRevision, pItemNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1743 (class 1255 OID 146566321)
-- Dependencies: 4536 8
-- Name: getrsnid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getrsnid(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
  pRsnCode ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pRsnCode IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT rsncode_id INTO _returnVal
  FROM rsncode
  WHERE (rsncode_code=pRsnCode);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Reason Code % not found.', pRsnCode;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1744 (class 1255 OID 146566322)
-- Dependencies: 4536 8
-- Name: getsalescatid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getsalescatid(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
  pSalesCatName ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pSalesCatName IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT salescat_id INTO _returnVal
  FROM salescat
  WHERE (salescat_name=pSalesCatName);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Sales Category % not found.', pSalesCatName;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1746 (class 1255 OID 146566324)
-- Dependencies: 4536 8
-- Name: getsaleslineitemid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getsaleslineitemid(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
  pSalesOrderItem ALIAS FOR $1;
  _delpos INTEGER = 0;
  _order TEXT;
  _part TEXT;
  _ln INTEGER;
  _sn INTEGER;
BEGIN
  IF (pSalesOrderItem IS NULL) THEN
    RETURN NULL;
  END IF;
  _delpos := strpos(pSalesOrderItem, '-');
  IF (_delpos > 0) THEN
    _order := substr(pSalesOrderItem, 1, (_delpos - 1));
    _part := substr(pSalesOrderItem, (_delpos + 1));
    _delpos := strpos(_part, '.');
    IF (_delpos > 0) THEN
      _ln := CAST(substr(_part, 1, (_delpos - 1)) AS INTEGER);
      _sn := CAST(substr(_part, (_delpos + 1)) AS INTEGER);
    ELSE
      _ln := CAST(_part AS INTEGER);
      _sn := 0;
    END IF;
    RETURN getSalesLineItemId( _order, _ln, _sn );
  END IF;
  RETURN 0;
END;
$_$;


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

--
-- TOC entry 1745 (class 1255 OID 146566323)
-- Dependencies: 4536 8
-- Name: getsaleslineitemid(text, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getsaleslineitemid(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.
BEGIN
  RETURN getSalesLineItemId($1, $2, 0);
END
$_$;


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

--
-- TOC entry 1747 (class 1255 OID 146566325)
-- Dependencies: 4536 8
-- Name: getsaleslineitemid(text, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getsaleslineitemid(text, integer, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pSalesOrderNumber ALIAS FOR $1;
  pLineNumber ALIAS FOR $2;
  pSubNumber ALIAS FOR $3;
  _returnVal INTEGER;
BEGIN
  IF ((pSalesOrderNumber IS NULL) OR (pLineNumber IS NULL)) THEN
    RETURN NULL;
  END IF;

  SELECT coitem_id INTO _returnVal
  FROM cohead, coitem
  WHERE ((cohead_number=pSalesOrderNumber)
  AND (cohead_id=coitem_cohead_id)
  AND (coitem_linenumber=pLineNumber)
  AND (coitem_subnumber=pSubNumber));

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Sales Line Item %-%not found.', pSalesOrderNumber,pLineNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1748 (class 1255 OID 146566326)
-- Dependencies: 4536 8
-- Name: getsalesorderid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getsalesorderid(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
  pSalesOrderNumber ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pSalesOrderNumber IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT cohead_id INTO _returnVal
  FROM cohead
  WHERE (cohead_number=pSalesOrderNumber);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Sales Order Number % not found.', pSalesOrderNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1749 (class 1255 OID 146566327)
-- Dependencies: 4536 8
-- Name: getsalesrepid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getsalesrepid(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
  pSalesRepNumber ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pSalesRepNumber IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT salesrep_id INTO _returnVal
  FROM salesrep
  WHERE (salesrep_number=pSalesRepNumber);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Sales Rep Number % not found.', pSalesRepNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1751 (class 1255 OID 146566328)
-- Dependencies: 4536 8
-- Name: getsaletypeid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getsaletypeid(psaletype 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
  _returnVal INTEGER;
BEGIN
  IF (pSaleType IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT saletype_id INTO _returnVal
  FROM saletype
  WHERE (saletype_code=UPPER(pSaleType));

  IF (_returnVal IS NULL) THEN
    RAISE EXCEPTION 'Sale Type % not found.', pSaleType;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getsaletypeid(psaletype text) OWNER TO admin;

--
-- TOC entry 1752 (class 1255 OID 146566329)
-- Dependencies: 4536 8
-- Name: getshiftid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getshiftid(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
  pShiftNumber ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (COALESCE(TRIM(pShiftNumber), '') = '') THEN
      RETURN NULL;
  END IF;

  SELECT shift_id INTO _returnVal
  FROM shift
  WHERE (UPPER(shift_number)=UPPER(pShiftNumber));

  IF (_returnVal IS NULL) THEN
    RAISE EXCEPTION 'Shift % not found.', pShiftNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1753 (class 1255 OID 146566330)
-- Dependencies: 4536 8
-- Name: getshipchrgid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getshipchrgid(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
  pShipChrgName ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pShipChrgName IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT shipchrg_id INTO _returnVal
  FROM shipchrg
  WHERE (shipchrg_name=pShipChrgName);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Ship Charge % not found.', pShipChrgName;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1754 (class 1255 OID 146566331)
-- Dependencies: 4536 8
-- Name: getshipformid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getshipformid(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
  pShipFormName ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pShipFormName IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT shipform_id INTO _returnVal
  FROM shipform
  WHERE (shipform_name=pShipFormName) LIMIT 1;

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Ship Form % not found.', pShipFormName;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1755 (class 1255 OID 146566332)
-- Dependencies: 4536 8
-- Name: getshipheadid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getshipheadid(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
  pShipmentNumber ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pShipmentNumber IS NULL OR pShipmentNumber = '') THEN
    RETURN NULL;
  END IF;

  SELECT shiphead_id INTO _returnVal
  FROM shiphead
  WHERE (shiphead_number=pShipmentNumber);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Shipment % not found.', pShipmentNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1756 (class 1255 OID 146566333)
-- Dependencies: 4536 8
-- Name: getshiptoid(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getshiptoid(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
  pCustNumber ALIAS FOR $1;
  pShiptoNumber ALIAS FOR $2;
  _returnVal INTEGER;
BEGIN
  IF ((pCustNumber IS NULL) OR (pShiptoNumber IS NULL)) THEN
	RETURN NULL;
  END IF;

  SELECT shipto_id INTO _returnVal
  FROM shiptoinfo
  WHERE ((shipto_cust_id=getCustId(pCustNumber,true))
  AND (UPPER(shipto_num)=UPPER(pShiptoNumber)));

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Shipto % not found.', pShiptoNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1757 (class 1255 OID 146566334)
-- Dependencies: 4536 8
-- Name: getshiptonumberfrominfo(text, text, text, text, text, text, text, text, text, text, text, text, text, boolean, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getshiptonumberfrominfo(text, text, text, text, text, text, text, text, text, text, text, text, text, boolean, boolean) RETURNS text
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _custname TEXT := COALESCE(TRIM(UPPER( $1)), '');
  _email TEXT 	 := COALESCE(TRIM(UPPER( $2)), '');
  _company TEXT  := COALESCE(TRIM(UPPER( $3)), '');
  _first TEXT 	 := COALESCE(TRIM(UPPER( $4)), '');
  _last TEXT 	 := COALESCE(TRIM(UPPER( $5)), '');
  _fullname TEXT := COALESCE(TRIM(UPPER( $6)), '');
  _addr1 TEXT 	 := COALESCE(TRIM(UPPER( $7)), '');
  _addr2 TEXT 	 := COALESCE(TRIM(UPPER( $8)), '');
  _addr3 TEXT 	 := COALESCE(TRIM(UPPER( $9)), '');
  _city TEXT 	 := COALESCE(TRIM(UPPER($10)), '');
  _state TEXT 	 := COALESCE(TRIM(UPPER($11)), '');
  _postalcode TEXT := COALESCE(TRIM(UPPER($12)), '');
  _country TEXT  := COALESCE(TRIM(UPPER($13)), '');
  _generate BOOLEAN := COALESCE($14, FALSE);
  _create BOOLEAN := COALESCE($15, FALSE);

  _citytrunc TEXT;
  _counter INTEGER;
  _custid INTEGER;
  _custnumber TEXT;
  _candidate TEXT;
  _r RECORD;
  _statetrunc TEXT;
BEGIN
  IF (_custname = '') THEN
    _custname := getCustNameFromInfo(_email, _company, _first, _last,
                     _fullname, FALSE);
  END IF;


  SELECT COUNT(*) INTO _counter
  FROM custinfo, shiptoinfo, addr
  WHERE ((UPPER(cust_name)=UPPER(_custname))
    AND UPPER(shipto_name)=UPPER(_fullname)
    AND (cust_id=shipto_cust_id)
    AND (shipto_addr_id=addr_id));

  IF (_counter = 1) THEN
    SELECT shipto_num INTO _candidate
    FROM custinfo, shiptoinfo, addr
    WHERE ((UPPER(cust_name)=UPPER(_custname))
      AND UPPER(shipto_name)=UPPER(_fullname)
      AND (cust_id=shipto_cust_id)
      AND (shipto_addr_id=addr_id));

    RETURN _candidate;

  ELSE

    SELECT COUNT(*) INTO _counter
    FROM custinfo, shiptoinfo, addr
    WHERE ((UPPER(cust_name)=UPPER(_custname))
      AND (cust_id=shipto_cust_id)
      AND (shipto_addr_id=addr_id));

    IF (_counter = 1) THEN
      SELECT shipto_num INTO _candidate
      FROM custinfo, shiptoinfo, addr
      WHERE ((UPPER(cust_name)=UPPER(_custname))
        AND (cust_id=shipto_cust_id)
        AND (shipto_addr_id=addr_id));

      RETURN _candidate;

    ELSIF (_counter > 1) THEN
      SELECT shipto_num,
         CASE WHEN (UPPER(addr_country) = _country) THEN 1 ELSE 0 END +
         CASE WHEN (UPPER(addr_postalcode) = _postalcode) THEN 1 ELSE 0 END +
         CASE WHEN (UPPER(addr_state) = _state) THEN 1 ELSE 0 END +
         CASE WHEN (UPPER(addr_city) = _city) THEN 1 ELSE 0 END +
         CASE WHEN (UPPER(addr_line3) = _addr3) THEN 1 ELSE 0 END +
         CASE WHEN (UPPER(addr_line2) = _addr2) THEN 1 ELSE 0 END +
         CASE WHEN (UPPER(addr_line1) = _addr1) THEN 1 ELSE 0 END
         AS maxquotient INTO _candidate, _counter
      FROM custinfo, shiptoinfo, addr
      WHERE ((UPPER(cust_name)=_custname)
        AND (cust_id=shipto_cust_id)
        AND (shipto_addr_id=addr_id))
      ORDER BY maxquotient desc
      LIMIT 1;

      RETURN _candidate;
    END IF;
  END IF;

  IF (_generate) THEN
    SELECT cust_number, cust_id INTO _custnumber, _custid
    FROM custinfo
    WHERE (UPPER(cust_name)=_custname);

    -- keep the number short
    _citytrunc := SUBSTRING(_city FOR 5);
    _statetrunc := SUBSTRING(_state FOR 5);

    IF (LENGTH(_citytrunc) > 0 AND NOT EXISTS(SELECT UPPER(shipto_num)
              FROM shiptoinfo
              WHERE ((shipto_cust_id=_custid)
                AND (UPPER(shipto_num)=_citytrunc)) )) THEN
      _candidate := _citytrunc;
    ELSIF (LENGTH(_last || _citytrunc) > 0 AND NOT EXISTS(SELECT UPPER(shipto_num)
              FROM shiptoinfo
              WHERE ((shipto_cust_id=_custid)
                AND (UPPER(shipto_num)=_last || _citytrunc)) )) THEN
      _candidate := _last || _citytrunc;
    ELSIF (LENGTH(_statetrunc) > 0 AND NOT EXISTS(SELECT UPPER(shipto_num)
           FROM shiptoinfo
           WHERE ((shipto_cust_id=_custid)
             AND (UPPER(shipto_num)=_statetrunc)) )) THEN
      _candidate := _statetrunc;
    ELSIF (LENGTH(_last || _statetrunc) > 0 AND NOT EXISTS(SELECT UPPER(shipto_num)
           FROM shiptoinfo
           WHERE ((shipto_cust_id=_custid)
             AND (UPPER(shipto_num)=_last || _statetrunc)) )) THEN
      _candidate := _last || _statetrunc;

    ELSIF (LENGTH(_citytrunc || _statetrunc) > 0 AND NOT EXISTS(SELECT UPPER(shipto_num)
              FROM shiptoinfo
              WHERE ((shipto_cust_id=_custid)
                AND (UPPER(shipto_num)=_citytrunc || _statetrunc)) )) THEN
      _candidate := _citytrunc || _statetrunc;

    ELSE
      SELECT CAST(COALESCE(MAX(CAST(shipto_num AS INTEGER)), 0) + 1 AS TEXT)
      INTO _candidate
      FROM shiptoinfo
      WHERE ((shipto_cust_id=_custid)
       AND (shipto_num~'^[0-9]*$'));
    END IF;

    IF (_create) THEN
      INSERT INTO api.custshipto (
    customer_number, shipto_number, name,
    address1, address2, address3,
    city, state, postal_code, country, address_change,
    first, last, email,
    edi_profile
      ) VALUES (
    _custnumber, _candidate, _candidate,
    _addr1, _addr2, _addr3,
    _city, _state, _postalcode, _country, 'CHANGEONE',
    _first, _last, LOWER(_email),
    'No EDI'
      );
    END IF;

    RETURN _candidate;
  END IF;

  RETURN '';
END;
$_$;


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

--
-- TOC entry 1758 (class 1255 OID 146566335)
-- Dependencies: 4536 8
-- Name: getshipviaid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getshipviaid(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
  pShipViaCode ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pShipViaCode IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT shipvia_id INTO _returnVal
  FROM shipvia
  WHERE (shipvia_code=pShipViaCode);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'ShipVia Code % not found.', pShipViaCode;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1759 (class 1255 OID 146566336)
-- Dependencies: 4536 8
-- Name: getshipzoneid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getshipzoneid(pshipzonename 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
  _returnVal INTEGER;
BEGIN
  IF (pShipZoneName IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT shipzone_id INTO _returnVal
  FROM shipzone
  WHERE (shipzone_name=pShipZoneName);

  IF (_returnVal IS NULL) THEN
    RAISE EXCEPTION 'Ship Zone % not found.', pShipZoneName;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getshipzoneid(pshipzonename text) OWNER TO admin;

--
-- TOC entry 1760 (class 1255 OID 146566337)
-- Dependencies: 4536 8
-- Name: getsitetypeid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getsitetypeid(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
  pSiteType ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pSiteType IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT sitetype_id INTO _returnVal
  FROM sitetype
  WHERE (sitetype_name=pSiteType);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Site Type % not found.', pSiteType;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1761 (class 1255 OID 146566338)
-- Dependencies: 4536 8
-- Name: getsoitemstatus(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getsoitemstatus(pcoitemid integer) RETURNS text
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _result TEXT;

BEGIN

  SELECT
      (CASE WHEN (coitem_status='O' AND (SELECT cust_creditstatus FROM custinfo WHERE cust_id=cohead_cust_id)='H') THEN 'H'
            WHEN (coitem_status='O' AND ((SELECT SUM(invcitem_billed)
                                            FROM invchead, invcitem
                                           WHERE ((CAST(invchead_ordernumber AS text)=cohead_number)
                                             AND  (invcitem_invchead_id=invchead_id)
                                             AND  (invcitem_item_id=itemsite_item_id)
                                             AND  (invcitem_warehous_id=itemsite_warehous_id)
                                             AND  (invcitem_linenumber=coitem_linenumber))) >= coitem_qtyord)) THEN 'I'
            WHEN (coitem_status='O' AND ((SELECT SUM(invcitem_billed)
                                            FROM invchead, invcitem
                                           WHERE ((CAST(invchead_ordernumber AS text)=cohead_number)
                                             AND  (invcitem_invchead_id=invchead_id)
                                             AND  (invcitem_item_id=itemsite_item_id)
                                             AND  (invcitem_warehous_id=itemsite_warehous_id)
                                             AND  (invcitem_linenumber=coitem_linenumber))) > 0)) THEN 'P'
            WHEN (coitem_status='O' AND (itemsite_qtyonhand - qtyAllocated(itemsite_id, CURRENT_DATE)
                                         + qtyOrdered(itemsite_id, CURRENT_DATE))
                                          >= ((coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) * coitem_qty_invuomratio)) THEN 'R'
            ELSE coitem_status END
       || CASE WHEN (coitem_firm) THEN 'F' ELSE '' END
       ) INTO _result
  FROM coitem JOIN cohead ON (cohead_id=coitem_cohead_id)
              JOIN custinfo ON (cust_id=cohead_cust_id)
              JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
  WHERE (coitem_id=pCoitemid);

  RETURN _result;

END;
$$;


ALTER FUNCTION public.getsoitemstatus(pcoitemid integer) OWNER TO admin;

--
-- TOC entry 1762 (class 1255 OID 146566339)
-- Dependencies: 4536 8
-- Name: getsoscheddate(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getsoscheddate(integer) RETURNS date
    LANGUAGE plpgsql
    AS $_$
-- Copyright (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;
  _minscheddate DATE;

BEGIN

  SELECT MIN(coitem_scheddate) INTO _minscheddate
  FROM coitem
  WHERE ( (coitem_cohead_id=pCoheadid)
    AND   (coitem_status NOT IN ('C', 'X')) );

  RETURN _minscheddate;

END;
$_$;


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

--
-- TOC entry 1763 (class 1255 OID 146566340)
-- Dependencies: 4536 8
-- Name: getsostatus(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getsostatus(integer) RETURNS character
    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
  pCoheadid ALIAS FOR $1;
  _result char(1);

BEGIN

  SELECT cohead_status INTO _result
  FROM cohead
  WHERE (cohead_id=pCoheadid);

  RETURN _result;

END;
$_$;


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

--
-- TOC entry 1764 (class 1255 OID 146566341)
-- Dependencies: 2702 4536 8
-- Name: getsubtax(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getsubtax(integer, integer) RETURNS SETOF subtax
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pTaxCodeId ALIAS FOR $1;
  pLevel ALIAS FOR $2;
  _row subtax%ROWTYPE;
  _x RECORD;
  _y RECORD;

BEGIN

  FOR _x IN --Select all tax codes whose calculation basis is pTaxCodeId
    SELECT tax_id, tax_code, tax_descrip
    FROM tax
    WHERE tax_basis_tax_id = pTaxCodeId
    
    
    LOOP
    _row.subtax_taxcode_id := _x.tax_id;
    _row.subtax_taxcode_code := _x.tax_code;
    _row.subtax_taxcode_descrip := _x.tax_descrip;
    _row.subtax_taxcode_level := pLevel + 1;

   RETURN NEXT _row;  
  
    FOR _y IN SELECT * from getSubTax(_x.tax_id, pLevel + 1) --This is the recursive part.
    LOOP

      _row.subtax_taxcode_id := _y.subtax_taxcode_id;
      _row.subtax_taxcode_code := _y.subtax_taxcode_code ;
      _row.subtax_taxcode_descrip := _y.subtax_taxcode_descrip;
      _row.subtax_taxcode_level := pLevel + 2;

      RETURN NEXT _row;

    END LOOP;
 
  END LOOP;

END;
$_$;


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

--
-- TOC entry 1765 (class 1255 OID 146566342)
-- Dependencies: 4536 8
-- Name: gettaxauthid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION gettaxauthid(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
  pTaxAuthCode ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pTaxAuthCode IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT taxauth_id INTO _returnVal
  FROM taxauth
  WHERE (taxauth_code=pTaxAuthCode);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Tax Authority % not found.', pTaxAuthCode;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1766 (class 1255 OID 146566343)
-- Dependencies: 4536 8
-- Name: gettaxid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION gettaxid(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
  pTaxCode ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pTaxCode IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT tax_id INTO _returnVal
  FROM tax
  WHERE (tax_code=pTaxCode);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Tax Code % not found.', pTaxCode;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1767 (class 1255 OID 146566344)
-- Dependencies: 4536 8
-- Name: gettaxtypeid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION gettaxtypeid(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
  pTaxType ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pTaxType IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT taxtype_id INTO _returnVal
  FROM taxtype
  WHERE (taxtype_name=pTaxType);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Tax Type % not found.', pTaxType;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1768 (class 1255 OID 146566345)
-- Dependencies: 4536 8
-- Name: gettaxzoneid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION gettaxzoneid(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
  pTaxZone ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pTaxZone IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT taxzone_id INTO _returnVal
  FROM taxzone
  WHERE (taxzone_code=pTaxZone);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Tax Zone % not found.', pTaxZone;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1769 (class 1255 OID 146566346)
-- Dependencies: 4536 8
-- Name: gettermsid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION gettermsid(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
  pTermsCode ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pTermsCode IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT terms_id INTO _returnVal
  FROM terms
  WHERE (terms_code=pTermsCode);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Terms Code % not found.', pTermsCode;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1770 (class 1255 OID 146566347)
-- Dependencies: 4536 8
-- Name: getunassignedaccntid(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getunassignedaccntid() RETURNS integer
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _test INTEGER;
  _returnVal INTEGER;
BEGIN
  SELECT fetchMetricValue('UnassignedAccount') INTO _test;

  IF (_test IS NULL) THEN
    RAISE EXCEPTION 'Metric not found for UnassignedAccount';
  END IF;

  SELECT accnt_id INTO _returnVal
  FROM accnt
  WHERE (accnt_id=_test);

  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Metric UnassignedAccount is an invalid G/L Account';
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getunassignedaccntid() OWNER TO admin;

--
-- TOC entry 1771 (class 1255 OID 146566348)
-- Dependencies: 4536 8
-- Name: getuomid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getuomid(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
  pUom ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pUom IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT uom_id INTO _returnVal
  FROM uom
  WHERE (uom_name=pUom);

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Unit of Measure % not found.', pUom;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1773 (class 1255 OID 146566349)
-- Dependencies: 4536 8
-- Name: getuomtypeid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getuomtypeid(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
  pUomType ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pUomType IS NULL) THEN
	RETURN NULL;
  END IF;

  SELECT uomtype_id INTO _returnVal
  FROM uomtype
  WHERE (UPPER(uomtype_name)=UPPER(pUomType));

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Unit of Measuer Type % not found.', pUomType;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1774 (class 1255 OID 146566350)
-- Dependencies: 4536 8
-- Name: getuomtypeid(text[]); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getuomtypeid(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
  pUomTypes ALIAS FOR $1;
  _returnVal INTEGER[];
  _val INTEGER;
  _i INTEGER;
BEGIN
  IF (pUomTypes IS NULL) OR (ARRAY_UPPER(pUomTypes,1) = 0) THEN
	RETURN NULL;
  END IF;

  FOR _i IN 1..ARRAY_UPPER(pUomTypes,1)
  LOOP
    SELECT uomtype_id INTO _val
    FROM uomtype
    WHERE (UPPER(uomtype_name)=UPPER(pUomTypes[_i]));

    IF (_val IS NULL) THEN
	RAISE EXCEPTION 'Unit of Measure Type % not found.', pUomTypes[_i];
    ELSE
      _returnVal[_i] := _val;
    END IF;
  END LOOP;

  RETURN _returnVal;
END;
$_$;


ALTER FUNCTION public.getuomtypeid(text[]) OWNER TO admin;

--
-- TOC entry 1775 (class 1255 OID 146566351)
-- Dependencies: 4536 8
-- Name: getusrid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getusrid(pusr text) RETURNS integer
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _returnVal INTEGER;
BEGIN
  SELECT usr_id INTO _returnVal
  FROM usr
  WHERE (usr_username=COALESCE(pUsr, getEffectiveXtUser()));

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'User % not found.', pUsr;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getusrid(pusr text) OWNER TO admin;

--
-- TOC entry 1776 (class 1255 OID 146566352)
-- Dependencies: 4536 8
-- Name: getusrlocaleid(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getusrlocaleid() RETURNS integer
    LANGUAGE plpgsql IMMUTABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _returnVal INTEGER;
BEGIN
  SELECT CAST(usrpref_value AS INTEGER)
  FROM usrpref
  WHERE (usrpref_username=getEffectiveXTUser())
    AND (usrpref_name='locale_id') INTO _returnVal;

  IF (_returnVal IS NULL) THEN
    SELECT locale_id
    FROM locale
    WHERE (LOWER(locale_code) = 'default')
    LIMIT 1 INTO _returnVal;
  END IF;

  IF (_returnVal IS NULL) THEN
    SELECT locale_id
    FROM locale
    ORDER BY locale_id
    LIMIT 1 INTO _returnVal;
  END IF;

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'User Locale not found.';
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getusrlocaleid() OWNER TO admin;

--
-- TOC entry 1777 (class 1255 OID 146566353)
-- Dependencies: 4536 8
-- Name: getvendaddrid(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getvendaddrid(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
  pVendNumber   ALIAS FOR $1;
  pVendAddrCode ALIAS FOR $2;
  _returnVal INTEGER;
BEGIN
  IF ( (pVendNumber IS NULL) OR (pVendAddrCode IS NULL) ) THEN
    RETURN NULL;
  END IF;

  SELECT vendaddr_id INTO _returnVal
    FROM vendaddrinfo
    JOIN vendinfo ON (vend_id=vendaddr_vend_id)
  WHERE ( (vendaddr_code=pVendAddrCode)
    AND   (vend_number=pVendNumber) );

  IF (_returnVal IS NULL) THEN
    RAISE EXCEPTION 'Vendor Number % Address % not found.',
    pVendNumber, pVendAddrCode;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1778 (class 1255 OID 146566354)
-- Dependencies: 4536 8
-- Name: getvendid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getvendid(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
  pVendNumber ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pVendNumber IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT vend_id INTO _returnVal
    FROM vendinfo
   WHERE (vend_number=pVendNumber);

  IF (_returnVal IS NULL) THEN
    RAISE EXCEPTION 'Vendor Number % not found.', pVendNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1779 (class 1255 OID 146566355)
-- Dependencies: 4536 8
-- Name: getvendtypeid(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getvendtypeid(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
  pVendTypeCode ALIAS FOR $1;
  _returnVal INTEGER;
BEGIN
  IF (pVendTypeCode IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT vendtype_id INTO _returnVal
  FROM vendtype
  WHERE (UPPER(vendtype_code)=UPPER(pVendTypeCode));

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Vendor Type % not found.', pVendTypeCode;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1780 (class 1255 OID 146566356)
-- Dependencies: 4536 8
-- Name: getwarehousid(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getwarehousid(pwarehouscode text, ptype text) RETURNS integer
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _active BOOL;
  _shipping BOOL;
  _returnVal INTEGER;
BEGIN
  IF (pWarehousCode IS NULL) THEN
	RETURN NULL;
  END IF;
 
  IF (UPPER(pType) NOT IN ('ALL','ACTIVE','SHIPPING')) THEN
    	RAISE EXCEPTION 'Warehouse lookip type % not valid. Valid types are ALL, ACTIVE and SHIPPING', pType;
  END IF;

  SELECT warehous_id, warehous_active, warehous_shipping INTO _returnVal, _active, _shipping
  FROM site()
  WHERE (warehous_code=UPPER(pWarehousCode));

  IF (_returnVal IS NULL) THEN
    RAISE EXCEPTION 'Warehouse Code % not found.', pWarehousCode;
    ELSE IF ((pType='SHIPPING') AND (_shipping=false)) THEN
      RAISE EXCEPTION 'Warehouse Code % is not a vaild shipping warehouse.', pWarehousCode;
      ELSE IF ((pType IN ('SHIPPING','ACTIVE')) AND (_active=false)) THEN
        RAISE EXCEPTION 'Warehouse Code % is inactive.', pWarehousCode;
      END IF;
    END IF;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getwarehousid(pwarehouscode text, ptype text) OWNER TO admin;

--
-- TOC entry 1781 (class 1255 OID 146566357)
-- Dependencies: 4536 8
-- Name: getwhsezoneid(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION getwhsezoneid(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
  pWhseCode ALIAS FOR $1;
  pWhseZoneName ALIAS FOR $2;
  _returnVal INTEGER;
BEGIN
  IF ((pWhseCode IS NULL) OR (pWhseZoneName IS NULL)) THEN
	RETURN NULL;
  END IF;

  SELECT whsezone_id INTO _returnVal
  FROM whsezone
  WHERE ( (whsezone_warehous_id=getWarehousId(pWhseCode, 'ACTIVE'))
      AND (UPPER(whsezone_name)=UPPER(pWhseZoneName)) );

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Whsezone % not found.', pWhseZoneName;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1782 (class 1255 OID 146566358)
-- Dependencies: 4536 8
-- Name: grantallmodulecmnttypesource(integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION grantallmodulecmnttypesource(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
  pCmnttypeid ALIAS FOR $1;
  pModuleName ALIAS FOR $2;
  _source RECORD;
  _sourceCounter INTEGER;

BEGIN

  _sourceCounter := 0;

  FOR _source IN SELECT source_id
                 FROM source 
                 WHERE (source_module=pModuleName) LOOP

    IF (SELECT grantCmnttypeSource(pCmnttypeid, _source.source_id)) THEN
      _sourceCounter := _sourceCounter + 1;
    END IF;

  END LOOP;

  RETURN _sourceCounter;

END;
$_$;


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

--
-- TOC entry 1783 (class 1255 OID 146566359)
-- Dependencies: 4536 8
-- Name: grantallmodulepriv(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION grantallmodulepriv(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
  pUsername ALIAS FOR $1;
  pModuleName ALIAS FOR $2;
  _priv RECORD;
  _privCounter INTEGER;

BEGIN

  _privCounter := 0;

  FOR _priv IN SELECT priv_id
               FROM priv 
               WHERE (priv_module=pModuleName) LOOP

    IF (SELECT grantPriv(pUsername, _priv.priv_id)) THEN
      _privCounter := _privCounter + 1;
    END IF;

  END LOOP;

  RETURN _privCounter;

END;
$_$;


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

--
-- TOC entry 1784 (class 1255 OID 146566360)
-- Dependencies: 4536 8
-- Name: grantallmoduleprivgroup(integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION grantallmoduleprivgroup(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
  pGrpid ALIAS FOR $1;
  pModuleName ALIAS FOR $2;
  _priv RECORD;
  _privCounter INTEGER;

BEGIN

  _privCounter := 0;

  FOR _priv IN SELECT priv_id
               FROM priv 
               WHERE (priv_module=pModuleName) LOOP

    IF (SELECT grantPrivGroup(pGrpid, _priv.priv_id)) THEN
      _privCounter := _privCounter + 1;
    END IF;

  END LOOP;

  RETURN _privCounter;

END;
$_$;


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

--
-- TOC entry 1785 (class 1255 OID 146566361)
-- Dependencies: 4536 8
-- Name: grantcmnttypesource(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION grantcmnttypesource(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
  pCmnttypeid ALIAS FOR $1;
  pSourceid ALIAS FOR $2;
  _test INTEGER;

BEGIN

  SELECT cmnttypesource_id INTO _test
  FROM cmnttypesource
  WHERE ( (cmnttypesource_cmnttype_id=pCmnttypeid)
    AND (cmnttypesource_source_id=pSourceid) );

  IF (FOUND) THEN
    RETURN FALSE;
  END IF;

  INSERT INTO cmnttypesource
  ( cmnttypesource_cmnttype_id, cmnttypesource_source_id )
  VALUES
  ( pCmnttypeid, pSourceid );

  RETURN TRUE;

END;
$_$;


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

--
-- TOC entry 1786 (class 1255 OID 146566362)
-- Dependencies: 4536 8
-- Name: grantgroup(text, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION grantgroup(text, 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
  pUsername ALIAS FOR $1;
  pGrpid ALIAS FOR $2;
  _test INTEGER;

BEGIN

  SELECT usrgrp_id INTO _test
  FROM usrgrp
  WHERE ( (usrgrp_username=pUsername)
   AND (usrgrp_grp_id=pGrpid) );

  IF (FOUND) THEN
    RETURN FALSE;
  END IF;

  INSERT INTO usrgrp
  ( usrgrp_username, usrgrp_grp_id )
  VALUES
  ( pUsername, pGrpid );

  RETURN TRUE;

END;
$_$;


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

--
-- TOC entry 1787 (class 1255 OID 146566363)
-- Dependencies: 4536 8
-- Name: grantpriv(text, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION grantpriv(text, 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
  pUsername ALIAS FOR $1;
  pPrivid ALIAS FOR $2;
  _test INTEGER;

BEGIN

  SELECT usrpriv_id INTO _test
  FROM usrpriv
  WHERE ( (usrpriv_username=pUsername)
   AND (usrpriv_priv_id=pPrivid) );

  IF (FOUND) THEN
    RETURN FALSE;
  END IF;

  INSERT INTO usrpriv
  ( usrpriv_username, usrpriv_priv_id )
  VALUES
  ( pUsername, pPrivid );

  NOTIFY "usrprivUpdated";

  RETURN TRUE;

END;
$_$;


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

--
-- TOC entry 1788 (class 1255 OID 146566364)
-- Dependencies: 4536 8
-- Name: grantpriv(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION grantpriv(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;
  pPrivname ALIAS FOR $2;
  _test INTEGER;

BEGIN

  SELECT usrpriv_id INTO _test
    FROM usrpriv
    JOIN priv ON (usrpriv_priv_id=priv_id)
  WHERE ((usrpriv_username=pUsername)
     AND (priv_name=pPrivname) );

  IF (FOUND) THEN
    RETURN FALSE;
  END IF;

  INSERT INTO usrpriv
  ( usrpriv_username, usrpriv_priv_id )
  SELECT pUsername, priv_id
    FROM priv
   WHERE (priv_name=pPrivname);

  NOTIFY "usrprivUpdated";

  RETURN TRUE;

END;
$_$;


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

--
-- TOC entry 1789 (class 1255 OID 146566365)
-- Dependencies: 4536 8
-- Name: grantprivgroup(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION grantprivgroup(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
  pGrpid ALIAS FOR $1;
  pPrivid ALIAS FOR $2;
  _test INTEGER;

BEGIN

  SELECT grppriv_id INTO _test
  FROM grppriv
  WHERE ( (grppriv_grp_id=pGrpid)
   AND (grppriv_priv_id=pPrivid) );

  IF (FOUND) THEN
    RETURN FALSE;
  END IF;

  INSERT INTO grppriv
  ( grppriv_grp_id, grppriv_priv_id )
  VALUES
  ( pGrpid, pPrivid );

  RETURN TRUE;

END;
$_$;


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

--
-- TOC entry 1790 (class 1255 OID 146566366)
-- Dependencies: 4536 8
-- Name: hasalarms(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION hasalarms() 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
  _alarm          RECORD;
  _batchId        INTEGER;
  _evntlogordtype TEXT;
  _evnttypeid     INTEGER;
  _evnttypename   TEXT;
  _fromEmail      TEXT;
  _itemid         INTEGER;
  _longsource     TEXT;
  _msgId          INTEGER;
  _recipient      TEXT;
  _recipientPart  INTEGER;
  _returnVal      BOOLEAN := FALSE;
  _summary        TEXT;
  _whsId          INTEGER := -1;

BEGIN
  FOR _alarm IN SELECT *
                FROM alarm
                WHERE ((alarm_creator=getEffectiveXtUser())
                   AND (CURRENT_TIMESTAMP > alarm_trigger)) LOOP
    _returnVal := TRUE;

    IF (_alarm.alarm_source = 'TODO') THEN
      SELECT (todoitem_name || '-' || todoitem_description),
             'T', 'TodoAlarm', 'To-Do Item'
      INTO _summary, _evntlogordtype, _evnttypename, _longsource
      FROM todoitem
      WHERE (todoitem_id = _alarm.alarm_source_id);

    ELSIF (_alarm.alarm_source = 'INCDT') THEN
      SELECT (incdt_number || '-' || incdt_summary),
             'I', 'IncidentAlarm', 'Incident'
      INTO _summary, _evntlogordtype, _evnttypename, _longsource
      FROM incdt
      WHERE (incdt_id = _alarm.alarm_source_id);

    ELSIF (_alarm.alarm_source = 'J') THEN
      SELECT (prj_number || ' ' || prj_name || '-' || prjtask_name),
              'J', 'TaskAlarm', 'Project Task'
      INTO _summary, _evntlogordtype, _evnttypename, _longsource
      FROM prjtask JOIN prj ON (prj_id=prjtask_prj_id)
      WHERE (prjtask_id = _alarm.alarm_source_id);

    ELSE
      CONTINUE; -- there's nothing to do for this iteration of the loop
    END IF;

    -- if event alarm
    IF (_alarm.alarm_event) THEN
      SELECT evnttype_id INTO _evnttypeid
      FROM evnttype
      WHERE (evnttype_name=_evnttypename);

      _recipientPart := 1;
      LOOP
        _recipient := SPLIT_PART(_alarm.alarm_event_recipient, ',', _recipientPart);
        EXIT WHEN (LENGTH(_recipient) = 0);

        SELECT usrpref_value INTO _whsId
        FROM usrpref
        WHERE ( (usrpref_username = _recipient)
          AND   (usrpref_name = 'PreferredWarehouse') );

        INSERT INTO evntlog (evntlog_evnttime, evntlog_username,
                             evntlog_evnttype_id, evntlog_ordtype,
                             evntlog_ord_id, evntlog_warehous_id, evntlog_number
                   ) VALUES (CURRENT_TIMESTAMP, _recipient,
                             _evnttypeid, _evntlogordtype,
                             _alarm.alarm_source_id, _whsId, _summary);

        _recipientPart := _recipientPart + 1;
      END LOOP;
    END IF;

    IF (_alarm.alarm_email) THEN
      SELECT usr_email INTO _fromEmail
      FROM usr
      WHERE (usr_username = _alarm.alarm_creator);

      _recipientPart := 1;
      LOOP
        _recipient := SPLIT_PART(_alarm.alarm_email_recipient, ',', _recipientPart);
        EXIT WHEN (LENGTH(_recipient) <= 0);
        _batchId := xtbatch.submitEmailToBatch(_fromEmail, _recipient, '',
                                               _summary,
                                               'Alarm reminder for '
                                               || _longsource || '.',
                                               NULL, CURRENT_TIMESTAMP,
                                               FALSE, NULL, NULL);
        _recipientPart := _recipientPart + 1;
      END LOOP;
    END IF;

    IF (_alarm.alarm_sysmsg) THEN
      _recipientPart := 1;
      LOOP
        _recipient := SPLIT_PART(_alarm.alarm_sysmsg_recipient, ',', _recipientPart);
        EXIT WHEN (LENGTH(_recipient) <= 0);
        _msgId := postMessage(_recipient, (_longsource || ' - ' || _summary));
        _recipientPart := _recipientPart + 1;
      END LOOP;
    END IF;

    DELETE FROM alarm WHERE alarm_id=_alarm.alarm_id;
  END LOOP;
  RETURN _returnVal;

END;
$$;


ALTER FUNCTION public.hasalarms() OWNER TO admin;

--
-- TOC entry 1791 (class 1255 OID 146566367)
-- Dependencies: 4536 8
-- Name: hasevents(); Type: FUNCTION; Schema: public; Owner: admin
--

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

  PERFORM evntlog_id
  FROM evntlog
  WHERE ( (evntlog_dispatched IS NULL)
   AND (evntlog_username=getEffectiveXtUser()) )
  LIMIT 1;
  RETURN FOUND;

END;
$$;


ALTER FUNCTION public.hasevents() OWNER TO admin;

--
-- TOC entry 1792 (class 1255 OID 146566368)
-- Dependencies: 4536 8
-- Name: hasmessages(); Type: FUNCTION; Schema: public; Owner: admin
--

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

  PERFORM msguser_id
  FROM msg, msguser
  WHERE ( (msguser_username=getEffectiveXtUser())
   AND (msguser_msg_id=msg_id)
   AND (CURRENT_TIMESTAMP BETWEEN msg_scheduled AND msg_expires)
   AND (msguser_viewed IS NULL) )
  LIMIT 1;
  RETURN FOUND;

END;
$$;


ALTER FUNCTION public.hasmessages() OWNER TO admin;

--
-- TOC entry 1794 (class 1255 OID 146566369)
-- Dependencies: 4536 8
-- Name: haspriv(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION haspriv(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
  pPrivName	ALIAS FOR $1;
  _result       INTEGER;
  _returnVal	BOOLEAN;

BEGIN
  RAISE NOTICE 'hasPriv(TEXT) is deprecated. Use checkPrivilege(TEXT) instead.';
  SELECT priv_id INTO _result
    FROM priv, grppriv, usrgrp
   WHERE((usrgrp_grp_id=grppriv_grp_id)
     AND (grppriv_priv_id=priv_id)
     AND (priv_name=pPrivName)
     AND (usrgrp_username=getEffectiveXtUser()));
  IF (FOUND) THEN
    RETURN true;
  END IF;

  SELECT COALESCE(usrpriv_id, 0) != 0 INTO _returnVal
  FROM priv LEFT OUTER JOIN
       usrpriv ON (priv_id=usrpriv_priv_id AND usrpriv_username = getEffectiveXtUser())
  WHERE (priv_name=pPrivName);
  IF (_returnVal IS NULL) THEN
    _returnVal := FALSE;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

--
-- TOC entry 1795 (class 1255 OID 146566370)
-- Dependencies: 4536 8
-- Name: hasprivonobject(text, text, integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION hasprivonobject(pprivtype text, pobjecttype text, pobjectid integer DEFAULT NULL::integer, puser text DEFAULT NULL::text) RETURNS boolean
    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
  _haspriv   BOOLEAN := FALSE;
  _privfound BOOLEAN := FALSE;
  _pkey      TEXT[];
  _privdesc  RECORD;
  _qstr      TEXT;

BEGIN
  IF UPPER(pPrivType) NOT IN ('CREATE', 'EDIT', 'VIEW', 'DELETE') THEN
    RAISE EXCEPTION 'Cannot check if user has % on % [xtuple: hasPrivOnObject, -1, %, %]',
                     pPrivType, pObjectType, pPrivType, pObjectType;
  END IF;

  /* TODO: create privdesc table? can't do it yet because this is a fix for a minor release
     NOTE: only include tables that have a single integer column as pkey
     NOTE: some of these are part of proprietary extensions. how do we make them part of the extension?
  */
  FOR _privdesc IN
  WITH privdesc AS (
    SELECT 'ADDR' AS otype,    'public' AS masterschema,
                                         'addr' AS mastertable,
                                                     'MaintainAddressMasters' AS editall,
                                                                                     'ViewAddressMasters' AS viewall,
                                                                                                               NULL AS ownerfield,        NULL AS editmine,                NULL AS viewmine
     UNION ALL SELECT 'BBH',   'xtmfg',  'bbom',     'MaintainBBOMs',                'ViewBBOMs',              NULL,                      NULL,                            NULL
     UNION ALL SELECT 'BBI',   'xtmfg',  'bbom',     'MaintainBBOMs',                'ViewBBOMs',              NULL,                      NULL,                            NULL
     UNION ALL SELECT 'BMH',   'public', 'bom',      'MaintainBOMs',                 'ViewBOMs',               NULL,                      NULL,                            NULL
     UNION ALL SELECT 'BMI',   'public', 'bom',      'MaintainBOMs',                 'ViewBOMs',               NULL,                      NULL,                            NULL
     UNION ALL SELECT 'BOH',   'xtmfg',  'boo',      'MaintainBOOs',                 'ViewBOOs',               NULL,                      NULL,                            NULL
     UNION ALL SELECT 'BOI',   'xtmfg',  'boo',      'MaintainBOOs',                 'ViewBOOs',               NULL,                      NULL,                            NULL
     UNION ALL SELECT 'C',     'public', 'custinfo', 'MaintainCustomerMasters',      'ViewCustomerMasters',    NULL,                      NULL,                            NULL
     UNION ALL SELECT 'CRMA',  'public', 'crmacct',  'MaintainAllCRMAccounts',       'ViewAllCRMAccounts',     'crmacct_owner_username',  'MaintainPersonalCRMAccounts',   'ViewPersonalCRMAccounts'
     UNION ALL SELECT 'EMP',   'public', 'emp',      'MaintainEmployees',            'ViewEmployees',          NULL,                      NULL,                            NULL
     UNION ALL SELECT 'I',     'public', 'item',     'MaintainItemMasters',          'ViewItemMasters',        NULL,                      NULL,                            NULL
     UNION ALL SELECT 'INCDT', 'public', 'incdt',    'MaintainAllIncidents',         'ViewAllIncidents',       'incdt_owner_username',    'MaintainPersonalIncidents',     'ViewPersonalIncidents'
     UNION ALL SELECT 'IR',    'public', 'itemsrc',  'MaintainItemSources',          'ViewItemSources',        NULL,                      NULL,                            NULL
     UNION ALL SELECT 'IS',    'public', 'itemsite', 'MaintainItemSites',            'ViewItemSites',          NULL,                      NULL,                            NULL
     UNION ALL SELECT 'J',     'public', 'prj',      'MaintainAllProjects',          'ViewAllProjects',        'prj_owner_username',      'MaintainPersonalProjects',      'ViewPersonalProjects'
     UNION ALL SELECT 'J',     'public', 'prj',      'MaintainAllProjects',          'ViewAllProjects',        'prj_username',            'MaintainPersonalProjects',      'ViewPersonalProjects'
     UNION ALL SELECT 'L',     'public', 'location', 'MaintainLocations',            'ViewLocations',          NULL,                      NULL,                            NULL
     UNION ALL SELECT 'OPP',   'public', 'ophead',   'MaintainAllOpportunities',     'ViewAllOpportunities',   'ophead_owner_username',   'MaintainPersonalOpportunities', 'ViewPersonalOpportunities'
     UNION ALL SELECT 'P',     'public', 'pohead',   'MaintainPurchaseOrders',       'ViewPurchaseOrders',     NULL,                      NULL,                            NULL
     UNION ALL SELECT 'PI',    'public', 'pohead',   'MaintainPurchaseOrders',       'ViewPurchaseOrders',     NULL,                      NULL,                            NULL
     UNION ALL SELECT 'Q',     'public', 'quhead',   'MaintainQuotes',               'ViewQuotes',             NULL,                      NULL,                            NULL
     UNION ALL SELECT 'QI',    'public', 'quhead',   'MaintainQuotes',               'ViewQuotes',             NULL,                      NULL,                            NULL
     UNION ALL SELECT 'RA',    'public', 'rahead',   'MaintainReturns',              'ViewReturns',            NULL,                      NULL,                            NULL
     UNION ALL SELECT 'RI',    'public', 'rahead',   'MaintainReturns',              'ViewReturns',            NULL,                      NULL,                            NULL
     UNION ALL SELECT 'S',     'public', 'cohead',   'MaintainSalesOrders',          'ViewSalesOrders',        NULL,                      NULL,                            NULL
     UNION ALL SELECT 'SI',    'public', 'cohead',   'MaintainSalesOrders',          'ViewSalesOrders',        NULL,                      NULL,                            NULL
     UNION ALL SELECT 'T',     'public', 'cntct',    'MaintainAllContacts',          'ViewAllContacts',        'cntct_owner_username',    'MaintainPersonalContacts',      'ViewPersonalContacts'
     UNION ALL SELECT 'TE',    'te',     'tehead',   'MaintainTimeExpense',          'ViewTimeExpenseHistory', 'tehead_username',         'MaintainTimeExpenseSelf',       NULL
     UNION ALL SELECT 'TE',    'te',     'tehead',   'MaintainTimeExpenseOthers',    'ViewTimeExpenseHistory', 'tehead_username',         'MaintainTimeExpenseSelf',       NULL
     UNION ALL SELECT 'TI',    'public', 'tohead',   'MaintainTransferOrders',       'ViewTransferOrders',     NULL,                      NULL,                            NULL
     UNION ALL SELECT 'TO',    'public', 'tohead',   'MaintainTransferOrders',       'ViewTransferOrders',     NULL,                      NULL,                            NULL
     UNION ALL SELECT 'TODO',  'public', 'todoitem', 'MaintainAllToDoItems',         'ViewAllToDoItems',       'todoitem_owner_username', 'MaintainPersonalToDoItems',     'ViewPersonalToDoItems'
     UNION ALL SELECT 'V',     'public', 'vendinfo', 'MaintainVendors',              'ViewVendors',            NULL,                      NULL,                            NULL
     UNION ALL SELECT 'W',     'public', 'wo',       'MaintainWorkOrders',           'ViewWorkOrders',         NULL,                      NULL,                            NULL
     UNION ALL SELECT 'WH',    'public', 'whsinfo',  'MaintainWarehouses',           'ViewWarehouses',         NULL,                      NULL,                            NULL)
  -- UNION ALL SELECT 'LS',    'public', 'ls',       NULL,                           NULL,                     NULL,                      NULL,                            NULL
  -- UNION ALL SELECT 'P',     'public', 'pohead',   'MaintainPostedPurchaseOrders', 'ViewPurchaseOrders',     NULL,                      NULL,                            NULL -- additional criteria?
  -- UNION ALL SELECT 'PI',    'public', 'pohead',   'MaintainPostedPurchaseOrders', 'ViewPurchaseOrders',     NULL,                      NULL,                            NULL -- additional criteria?
     SELECT *
       FROM privdesc
      WHERE otype = pObjectType
  LOOP
    _privfound := TRUE;
    RAISE DEBUG '% % % % % % %',
                _privdesc.otype, _privdesc.masterschema, _privdesc.mastertable,
                _privdesc.editall, _privdesc.viewall, _privdesc.editmine, _privdesc.viewmine;

    IF checkPrivilege(CASE UPPER(pPrivType) WHEN 'CREATE' THEN _privdesc.editall
                                            WHEN 'EDIT'   THEN _privdesc.editall
                                            WHEN 'DELETE' THEN _privdesc.editall
                                            WHEN 'VIEW'   THEN _privdesc.viewall
                      END) THEN
      _haspriv = TRUE;

    ELSIF checkPrivilege(CASE UPPER(pPrivType) WHEN 'CREATE' THEN _privdesc.editmine
                                               WHEN 'EDIT'   THEN _privdesc.editmine
                                               WHEN 'DELETE' THEN _privdesc.editmine
                                               WHEN 'VIEW'   THEN _privdesc.viewmine
                         END) THEN
      IF pObjectId IS NULL THEN
      _haspriv = TRUE;

      ELSE
        _pkey := primaryKeyFields(_privdesc.masterschema, _privdesc.mastertable);

        -- SELECT ... FROM schema.table WHERE pkeyfield = pObjectId AND ownerfield = pUser
        _qstr := 'SELECT EXISTS(SELECT 1
                                  FROM ' || quote_ident(_privdesc.masterschema)
                                   || '.' || quote_ident(_privdesc.mastertable)
                                   || ' WHERE ' || quote_ident(_pkey[1]) || ' = ' || pObjectId
                                   || '   AND ' || quote_ident(_privdesc.ownerfield)
                                   || '= ' || quote_literal(COALESCE(pUser, getEffectiveXtUser())) || ');';
        RAISE DEBUG '%', _qstr;

        EXECUTE _qstr INTO _haspriv;
      END IF;
    END IF;

    EXIT WHEN _haspriv;
  END LOOP;

  RETURN _haspriv OR NOT _privfound;

END;
$$;


ALTER FUNCTION public.hasprivonobject(pprivtype text, pobjecttype text, pobjectid integer, puser text) OWNER TO admin;

--
-- TOC entry 8975 (class 0 OID 0)
-- Dependencies: 1795
-- Name: FUNCTION hasprivonobject(pprivtype text, pobjecttype text, pobjectid integer, puser text); Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON FUNCTION hasprivonobject(pprivtype text, pobjecttype text, pobjectid integer, puser text) IS 'Return if a user has permission to view or edit a specific database object.
pPrivType is either CREATE, EDIT, DELETE, or VIEW, and controls which privilege is checked.
pObjectType is one of the string constants used by the Documents widget, such as ADDR for Addresses.
pObjectId is the internal id of the record in the table associated with pObjectType (defaults to NULL).
pUser is the username to be checked for those pObjectTypes that restrict access to individual users (NULL == current user and is the default).';


--
-- TOC entry 1796 (class 1255 OID 146566372)
-- Dependencies: 8
-- Name: hmac(text, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1797 (class 1255 OID 146566373)
-- Dependencies: 8
-- Name: hmac(bytea, bytea, text); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1798 (class 1255 OID 146566374)
-- Dependencies: 4536 8
-- Name: implodewo(integer, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION implodewo(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
  pWoid ALIAS FOR $1;
  implodeChildren ALIAS FOR $2;
  resultCode INTEGER;
  _wotcCnt   INTEGER;
  _routings  BOOLEAN;

BEGIN
  SELECT metric_value='t' INTO _routings
         FROM metric
         WHERE (metric_name='Routings');

  IF ((SELECT wo_id
       FROM wo
       WHERE ((wo_status='E')
        AND (wo_id=pWoid))) IS NULL) THEN
    RETURN 0;
  END IF;

  IF (_routings) THEN
    SELECT count(*) INTO _wotcCnt
    FROM xtmfg.wotc
    WHERE (wotc_wo_id=pWoid);
    IF (_wotcCnt > 0) THEN
      RETURN -1;
    END IF;
  END IF;

--  Delete any created P/R's for this W/O
  PERFORM deletePr('W', womatl_id)
  FROM womatl
  WHERE (womatl_wo_id=pWoid);

  DELETE FROM womatl
  WHERE (womatl_id IN ( SELECT womatl_id
                        FROM womatl, wo
                        WHERE ((womatl_wo_id=wo_id)
                         AND (wo_status='E')
                         AND (wo_id=pWoid)) ));

  IF _routings THEN

    DELETE FROM xtmfg.wooper
    WHERE (wooper_id IN ( SELECT wooper_id
                          FROM xtmfg.wooper, wo
                          WHERE ((wooper_wo_id=wo_id)
                           AND (wo_status='E')
                           AND (wo_id=pWoid)) ));
  END IF;

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

  IF (implodeChildren) THEN
    resultCode := (SELECT MAX(implodeWo(wo_id, TRUE))
                   FROM wo
                   WHERE ((wo_ordtype='W')
                    AND (wo_ordid=pWoid)));

    resultCode := (SELECT MAX(deleteWo(wo_id, TRUE))
                   FROM wo
                   WHERE ((wo_ordtype='W')
                    AND (wo_ordid=pWoid)));
  END IF;

  RETURN 0;
END;
$_$;


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

--
-- TOC entry 206 (class 1259 OID 146566375)
-- Dependencies: 5952 5953 5954 8
-- Name: incdt; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE incdt (
    incdt_id integer NOT NULL,
    incdt_number integer NOT NULL,
    incdt_crmacct_id integer,
    incdt_cntct_id integer,
    incdt_summary text,
    incdt_descrip text,
    incdt_item_id integer,
    incdt_timestamp timestamp without time zone DEFAULT now() NOT NULL,
    incdt_status character(1) DEFAULT 'N'::bpchar NOT NULL,
    incdt_assigned_username text,
    incdt_incdtcat_id integer,
    incdt_incdtseverity_id integer,
    incdt_incdtpriority_id integer,
    incdt_incdtresolution_id integer,
    incdt_lotserial text,
    incdt_ls_id integer,
    incdt_aropen_id integer,
    incdt_owner_username text,
    incdt_recurring_incdt_id integer,
    incdt_updated timestamp without time zone DEFAULT now() NOT NULL,
    incdt_prj_id integer,
    incdt_public boolean
);


ALTER TABLE public.incdt OWNER TO admin;

--
-- TOC entry 8976 (class 0 OID 0)
-- Dependencies: 206
-- Name: TABLE incdt; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE incdt IS 'Incident table';


--
-- TOC entry 8977 (class 0 OID 0)
-- Dependencies: 206
-- Name: COLUMN incdt.incdt_lotserial; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN incdt.incdt_lotserial IS 'incdt_lotserial is deprecated';


--
-- TOC entry 8978 (class 0 OID 0)
-- Dependencies: 206
-- Name: COLUMN incdt.incdt_recurring_incdt_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN incdt.incdt_recurring_incdt_id IS 'The first incdt record in the series if this is a recurring Incident. If the incdt_recurring_incdt_id is the same as the incdt_id, this record is the first in the series.';


--
-- TOC entry 1799 (class 1255 OID 146566384)
-- Dependencies: 2785 4536 8
-- Name: incdt(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION incdt() RETURNS SETOF incdt
    LANGUAGE plpgsql
    AS $$
-- Copyright (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 incdt%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 ('MaintainAllIncidents','ViewAllIncidents','MaintainPersonalIncidents','ViewPersonalIncidents')
  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 incdt
    LOOP
      RETURN NEXT _row;
    END LOOP;
  -- Otherwise if have any other grant, must be personal privilege.
  ELSIF (_grant) THEN
    FOR _row IN 
      SELECT * FROM incdt 
      WHERE  getEffectiveXtUser() IN (incdt_owner_username, incdt_assigned_username)
    LOOP
      RETURN NEXT _row;
    END LOOP;
  END IF;

  RETURN;

END;
$$;


ALTER FUNCTION public.incdt() OWNER TO admin;

--
-- TOC entry 8980 (class 0 OID 0)
-- Dependencies: 1799
-- Name: FUNCTION incdt(); Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON FUNCTION incdt() IS 'A table function that returns Incident results according to privilege settings.';


--
-- TOC entry 1800 (class 1255 OID 146566385)
-- Dependencies: 4536 8
-- Name: indentedbom(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION indentedbom(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;
  _revid INTEGER;

BEGIN

  SELECT getActiveRevId('BOM',pItemid) INTO _revid;

  RETURN indentedBOM(pItemid, _revid);

END;
$_$;


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

--
-- TOC entry 1801 (class 1255 OID 146566386)
-- Dependencies: 4536 8
-- Name: indentedbom(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION indentedbom(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;
  pRevisionid ALIAS FOR $2;
  _bomworkid INTEGER;
  _indexid INTEGER;
  _r RECORD;
  _batchsize NUMERIC;

BEGIN

  -- Get the batch quantity
  SELECT COALESCE( (
    SELECT bomhead_batchsize
    FROM bomhead
    WHERE ((bomhead_item_id=pItemId)
    AND (bomhead_rev_id=pRevisionid)) LIMIT 1),1) INTO _batchsize;
 
--  Check on the temporary workspace
--  PERFORM maintainBOMWorkspace();

--  Grab a new index for this bomwork set
  SELECT NEXTVAL('misc_index_seq') INTO _indexid;

--  Step through all of the components of the passed pItemid
  FOR _r IN SELECT bomitem.*,
                   item_id,
                   itemuomtouom(item_id, item_inv_uom_id, NULL, (bomitem_qtyfxd/_batchsize + bomitem_qtyper) * (1 + bomitem_scrap), 'qtyper') AS qtyreq,
                   (itemuomtouomratio(bomitem_item_id, bomitem_uom_id, NULL)
                               * bomitem_qtyfxd) AS qtyfxd,
                   (itemuomtouomratio(bomitem_item_id, bomitem_uom_id, NULL)
                               * bomitem_qtyper) AS qtyper,
                   stdcost(item_id, bomitem_id) AS standardcost,
                   actcost(item_id, bomitem_id) AS actualcost
  FROM bomitem(pItemId, pRevisionid), item
  WHERE ( (bomitem_item_id=item_id) ) LOOP

--  Insert the component and bomitem parameters
    SELECT NEXTVAL('bomwork_bomwork_id_seq') INTO _bomworkid;
    INSERT INTO bomwork
    ( bomwork_id, bomwork_set_id, bomwork_parent_id, bomwork_level,
      bomwork_parent_seqnumber, bomwork_seqnumber,
      bomwork_item_id, bomwork_createwo, bomwork_qtyreq,
      bomwork_qtyfxd, bomwork_qtyper, bomwork_scrap, bomwork_issuemethod,
      bomwork_effective, bomwork_expires,
      bomwork_stdunitcost, bomwork_actunitcost,
      bomwork_char_id, bomwork_value, bomwork_notes, bomwork_ref,
      bomwork_bomitem_id, bomwork_ecn )
    VALUES
    ( _bomworkid, _indexid, -1, 1,
      0, _r.bomitem_seqnumber,
      _r.item_id, _r.bomitem_createwo, _r.qtyreq,
      _r.qtyfxd, _r.qtyper, _r.bomitem_scrap, _r.bomitem_issuemethod,
      _r.bomitem_effective, _r.bomitem_expires,
      _r.standardcost, _r.actualcost,
      _r.bomitem_char_id, _r.bomitem_value, _r.bomitem_notes, _r.bomitem_ref,
      _r.bomitem_id, _r.bomitem_ecn );

--  Explode the components of the current component
    PERFORM explodeBOM(_r.item_id, _bomworkid, 1);

  END LOOP;

--  Return a key to the result
  RETURN _indexid;

END;
$_$;


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

--
-- TOC entry 1802 (class 1255 OID 146566387)
-- Dependencies: 2657 4536 8
-- Name: indentedbom(integer, integer, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION indentedbom(integer, integer, integer, integer) RETURNS SETOF bomdata
    LANGUAGE plpgsql
    AS $_$
-- Copyright (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;
  pRevisionid ALIAS FOR $2;
  pExpiredDays ALIAS FOR $3;
  pFutureDays ALIAS FOR $4;
  _row bomdata%ROWTYPE;
  _bomworksetid INTEGER;
  _x RECORD;
  _check CHAR(1);
  _inactive BOOLEAN := FALSE;
  _batchsize NUMERIC;
BEGIN

  IF (pRevisionid != -1) THEN
    --Is this a deactivated revision?
    SELECT rev_status INTO _check
    FROM rev
    WHERE ((rev_id=pRevisionid)
    AND (rev_status='I'));
    IF (FOUND) THEN
      _inactive := TRUE;
    END IF;
  END IF;
 
  -- Get the batch quantity
  SELECT COALESCE( (
    SELECT bomhead_batchsize
    FROM bomhead
    WHERE ((bomhead_item_id=pItemId)
    AND (bomhead_rev_id=pRevisionid)) LIMIT 1),1) INTO _batchsize;
 
  IF NOT (_inactive) THEN

    --We can explode this out based on current data
    SELECT indentedBOM(pItemid, pRevisionid) INTO _bomworksetid;  

    FOR _x IN
        SELECT bomwork_id, bomwork_parent_id, bomwork_level,
               bomworkSequence(bomwork_id) AS seq_ord,
               bomwork_seqnumber, item_id, item_number, uom_name,
               item_descrip1, item_descrip2,
               (item_descrip1 || ' ' || item_descrip2) AS itemdescription,
               bomwork_qtyreq, bomwork_qtyfxd, bomwork_qtyper, bomwork_scrap, bomwork_createwo,
       CASE WHEN (bomwork_issuemethod='S') THEN 'Push'
            WHEN (bomwork_issuemethod='L') THEN 'Pull'
            WHEN (bomwork_issuemethod='M') THEN 'Mixed'
            ELSE 'Special'
       END AS issuemethod,
       bomwork_effective, bomwork_expires,
       (bomwork_expires <= CURRENT_DATE) AS expired,
       (bomwork_effective > CURRENT_DATE) AS future,
       bomwork_actunitcost AS actunitcost,
       bomwork_stdunitcost AS stdunitcost,
       CASE WHEN item_type NOT IN ('R','T') THEN
         itemuomtouom(item_id, item_inv_uom_id, NULL, (bomwork_qtyfxd/_batchsize + bomwork_qtyper) * (1 + bomwork_scrap), 'qtyper') * bomwork_actunitcost
       ELSE 0.0 END AS actextendedcost,
       CASE WHEN item_type NOT IN ('R','T') THEN
         itemuomtouom(item_id, item_inv_uom_id, NULL, (bomwork_qtyfxd/_batchsize + bomwork_qtyper) * (1 + bomwork_scrap), 'qtyper') * bomwork_stdunitcost
       ELSE 0.0 END AS stdextendedcost,
       bomwork_char_id,
       bomwork_value, bomwork_notes, bomwork_ref,
       bomwork_bomitem_id, bomwork_ecn
       FROM bomwork, item, uom 
       WHERE ( (bomwork_item_id=item_id)
       AND (item_inv_uom_id=uom_id)
       AND (bomwork_set_id=_bomworksetid) )
       AND (bomwork_expires > (CURRENT_DATE - pExpiredDays))
       AND (bomwork_effective <= (CURRENT_DATE + pFutureDays))
       UNION
       SELECT -1, -1, 1,
              '0',
              NULL,-1, costelem_type AS bomdata_item_number, '',
              '', '',
              '',
              NULL, NULL, NULL, NULL, NULL,
              '',
              NULL, NULL,
              false,
              false,
              currToBase(itemcost_curr_id, itemcost_actcost, CURRENT_DATE) AS actunitcost,
              itemcost_stdcost AS stdunitcost,
              currToBase(itemcost_curr_id, itemcost_actcost, CURRENT_DATE) AS actextendedcost,
              itemcost_stdcost AS stdextendedcost,
              NULL,
              NULL,NULL,NULL,
              NULL,NULL
       FROM itemcost, costelem 
       WHERE ( (itemcost_costelem_id=costelem_id)
       AND (NOT itemcost_lowlevel)
       AND (itemcost_item_id=pItemid) )
       ORDER BY seq_ord
    LOOP
        _row.bomdata_bomwork_id := _x.bomwork_id;
        _row.bomdata_bomwork_parent_id := _x.bomwork_parent_id;
        _row.bomdata_bomwork_level := _x.bomwork_level;
        _row.bomdata_bomwork_seqnumber := _x.bomwork_seqnumber;
        _row.bomdata_bomitem_id := _x.bomwork_bomitem_id;
        _row.bomdata_item_id := _x.item_id;
        _row.bomdata_item_number := _x.item_number;
        _row.bomdata_uom_name := _x.uom_name;
        _row.bomdata_item_descrip1 := _x.item_descrip1;
        _row.bomdata_item_descrip2 := _x.item_descrip2;
        _row.bomdata_itemdescription := _x.itemdescription;
        _row.bomdata_batchsize := _batchsize;
        _row.bomdata_qtyreq := _x.bomwork_qtyreq;
        _row.bomdata_qtyfxd := _x.bomwork_qtyfxd;
        _row.bomdata_qtyper := _x.bomwork_qtyper;
        _row.bomdata_scrap := _x.bomwork_scrap;
        _row.bomdata_createchild := _x.bomwork_createwo;
        _row.bomdata_issuemethod := _x.issuemethod;
        _row.bomdata_effective := _x.bomwork_effective;
        _row.bomdata_expires := _x.bomwork_expires;
        _row.bomdata_expired := _x.expired;
        _row.bomdata_future := _x.future;
        _row.bomdata_actunitcost := _x.actunitcost;
        _row.bomdata_stdunitcost := _x.stdunitcost;
        _row.bomdata_actextendedcost := _x.actextendedcost;
        _row.bomdata_stdextendedcost := _x.stdextendedcost;
        _row.bomdata_ecn := _x.bomwork_ecn;
        _row.bomdata_char_id := _x.bomwork_char_id;
        _row.bomdata_value := _x.bomwork_value;
        _row.bomdata_notes := _x.bomwork_notes;
        _row.bomdata_ref := _x.bomwork_ref;
        RETURN NEXT _row;
    END LOOP;
    
    PERFORM deleteBOMWorkset(_bomworksetid);

  ELSE

-- Use historical snapshot for inactive revisions
    FOR _x IN
        SELECT bomhist_id, bomhist_parent_id, bomhist_level,
               bomhistSequence(bomhist_seq_id) AS seq_ord,
               bomhist_seqnumber, item_id, item_number, uom_name,
               item_descrip1, item_descrip2,
               (item_descrip1 || ' ' || item_descrip2) AS itemdescription,
               bomhist_qtyreq, bomhist_qtyfxd, bomhist_qtyper, bomhist_scrap,
               bomhist_createwo,
       CASE WHEN (bomhist_issuemethod='S') THEN 'Push'
            WHEN (bomhist_issuemethod='L') THEN 'Pull'
            WHEN (bomhist_issuemethod='M') THEN 'Mixed'
            ELSE 'Special'
       END AS issuemethod,
       bomhist_effective, bomhist_expires,
       (bomhist_expires <= CURRENT_DATE) AS expired,
       (bomhist_effective > CURRENT_DATE) AS future,
       bomhist_actunitcost AS actunitcost,
       bomhist_stdunitcost AS stdunitcost,
       CASE WHEN item_type NOT IN ('R','T') THEN
         (bomist_qtyfxd/_batchsize + bomhist_qtyper) * (1 + bomhist_scrap) * bomhist_actunitcost
       ELSE 0 END AS actextendedcost,
       CASE WHEN item_type NOT IN ('R','T') THEN
         (bomist_qtyfxd/_batchsize + bomhist_qtyper) * (1 + bomhist_scrap) * bomhist_stdunitcost
       ELSE 0 END AS stdextendedcost,
       bomhist_char_id, bomhist_value, bomhist_notes, bomhist_ref 
       FROM bomhist, item, uom 
       WHERE ( (bomhist_item_id=item_id)
       AND (item_inv_uom_id=uom_id)
       AND (bomhist_rev_id=pRevisionid) )
       AND (bomhist_expires > (CURRENT_DATE - pExpiredDays))
       AND (bomhist_effective <= (CURRENT_DATE + pFutureDays))
       UNION
       SELECT -1, -1, 1,
              '0',
              NULL,-1, costelem_type AS bomdata_item_number, '',
              '', '',
              '',
              NULL, NULL, NULL, NULL,
              false,
              '', NULL, NULL,
              false,
              false,
              bomhist_actunitcost AS actunitcost,
              bomhist_stdunitcost AS stdunitcost,
              bomhist_actunitcost AS actextendedcost,
              bomhist_stdunitcost AS stdextendedcost,
              NULL,NULL,NULL,NULL 
       FROM bomhist, costelem 
       WHERE ((bomhist_rev_id=pRevisionid)
       AND (costelem_id=bomhist_item_id))
       ORDER BY seq_ord
    LOOP
        _row.bomdata_bomwork_id := _x.bomhist_id;
        _row.bomdata_bomwork_parent_id := _x.bomhist_parent_id;
        _row.bomdata_bomwork_level := _x.bomhist_level;
        _row.bomdata_bomwork_seqnumber := _x.bomhist_seqnumber;
        _row.bomdata_bomitem_id := -1;
        _row.bomdata_item_id := _x.item_id;
        _row.bomdata_item_number := _x.item_number;
        _row.bomdata_uom_name := _x.uom_name;
        _row.bomdata_item_descrip1 := _x.item_descrip1;
        _row.bomdata_item_descrip2 := _x.item_descrip2;
        _row.bomdata_itemdescription := _x.itemdescription;
        _row.bomdata_batchsize := _batchsize;
        _row.bomdata_qtyreq := _x.bomhist_qtyreq;
        _row.bomdata_qtyfxd := _x.bomist_qtyfxd;
        _row.bomdata_qtyper := _x.bomhist_qtyper;
        _row.bomdata_scrap := _x.bomhist_scrap;
        _row.bomdata_createchild := _x.bomhist_createwo;
        _row.bomdata_issuemethod := _x.issuemethod;
        _row.bomdata_effective := _x.bomhist_effective;
        _row.bomdata_expires := _x.bomhist_expires;
        _row.bomdata_expired := _x.expired;
        _row.bomdata_future := _x.future;
        _row.bomdata_actunitcost := _x.actunitcost;
        _row.bomdata_stdunitcost := _x.stdunitcost;
        _row.bomdata_actextendedcost := _x.actextendedcost;
        _row.bomdata_stdextendedcost := _x.stdextendedcost;
        _row.bomdata_ecn := '';
        _row.bomdata_char_id := _x.bomhist_char_id;
        _row.bomdata_value := _x.bomhist_value;
        _row.bomdata_notes := _x.bomhist_notes;
        _row.bomdata_ref := _x.bomhist_ref;
        RETURN NEXT _row;
    END LOOP;
  END IF;   

  RETURN;
END;
$_$;


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

--
-- TOC entry 1805 (class 1255 OID 146566389)
-- Dependencies: 4536 8
-- Name: indentedwhereused(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION indentedwhereused(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;
  _indexid INTEGER;
  _level INTEGER;

BEGIN

--  Check on the temporary workspace
--  PERFORM maintainBOMWorkspace();

  _indexid := (SELECT NEXTVAL('misc_index_seq'));
  _level := 1;

  INSERT INTO bomwork
  ( bomwork_set_id, bomwork_parent_id,
    bomwork_level, bomwork_seqnumber,
    bomwork_item_id, bomwork_item_type, bomwork_createwo,
    bomwork_qtyfxd, bomwork_qtyper,
    bomwork_scrap, bomwork_issuemethod,
    bomwork_effective, bomwork_expires, bomwork_status,
    bomwork_stdunitcost, bomwork_actunitcost )
  SELECT _indexid, -1,
         1, bomitem_seqnumber,
         item_id, item_type, bomitem_createwo,
         (itemuomtouomratio(bomitem_item_id, bomitem_uom_id, NULL) * bomitem_qtyfxd),
         (itemuomtouomratio(bomitem_item_id, bomitem_uom_id, NULL) * bomitem_qtyper),
         bomitem_scrap, bomitem_issuemethod,
         bomitem_effective, bomitem_expires, 'U',
         stdcost(item_id), actcost(item_id)
  FROM bomitem, item
  WHERE ((bomitem_item_id=pItemid)
    AND (bomitem_parent_item_id=item_id)
    AND (CURRENT_DATE BETWEEN bomitem_effective AND (bomitem_expires - 1))
    AND (bomitem_rev_id=getActiveRevId('BOM',bomitem_parent_item_id)));

  WHILE ( ( SELECT count(*)
            FROM bomwork
            WHERE ((bomwork_status='U')
              AND (bomwork_set_id=_indexid)) ) > 0) LOOP

    _level := _level + 1;

    INSERT INTO bomwork
    ( bomwork_set_id, bomwork_parent_id,
      bomwork_level, bomwork_seqnumber,
      bomwork_item_id, bomwork_item_type, bomwork_createwo,
      bomwork_qtyfxd, bomwork_qtyper,
      bomwork_scrap, bomwork_issuemethod,
      bomwork_effective, bomwork_expires, bomwork_status,
      bomwork_stdunitcost, bomwork_actunitcost )
    SELECT _indexid, bomwork_id,
           _level, bomitem_seqnumber,
           item_id, item_type, bomitem_createwo,
           (bomwork_qtyper * (itemuomtouomratio(bomitem_item_id, bomitem_uom_id, NULL) * bomitem_qtyfxd)),
           (bomwork_qtyper * (itemuomtouomratio(bomitem_item_id, bomitem_uom_id, NULL) * bomitem_qtyper)),
           bomitem_scrap, bomitem_issuemethod,
           CASE WHEN bomitem_effective < bomwork_effective THEN
             bomwork_effective
           ELSE bomitem_effective END, 
           CASE WHEN bomitem_expires > bomwork_expires THEN
             bomwork_expires
           ELSE bomitem_expires END,
           'N',
           stdcost(item_id), actcost(item_id)
    FROM bomwork JOIN bomitem ON ( (bomitem_item_id=bomwork_item_id)
                               AND (CURRENT_DATE BETWEEN bomitem_effective AND (bomitem_expires - 1))
                               AND (bomitem_rev_id=getActiveRevId('BOM',bomitem_parent_item_id)) )
                 JOIN item ON (item_id=bomitem_parent_item_id)
    WHERE (bomwork_status='U');

    UPDATE bomwork
    SET bomwork_status='C'
    WHERE ((bomwork_status='U')
      AND (bomwork_set_id=_indexid));

    UPDATE bomwork
    SET bomwork_status='U'
    WHERE ((bomwork_status='N')
      AND (bomwork_set_id=_indexid));

  END LOOP;

  RETURN _indexid;
END;
$_$;


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

--
-- TOC entry 1806 (class 1255 OID 146566390)
-- Dependencies: 2711 4536 8
-- Name: indentedwo(integer, boolean, boolean, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION indentedwo(integer, boolean, boolean, boolean) RETURNS SETOF wodata
    LANGUAGE plpgsql
    AS $_$
-- Copyright (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;
   pshowops ALIAS FOR $2;
   pshowmatl ALIAS FOR $3; 
   pshowindent ALIAS FOR $4;   
  _row wodata%ROWTYPE;
  _subrow wodata%ROWTYPE;  
  _opx wodata%ROWTYPE;
  _x RECORD;
  _level INTEGER;
   
BEGIN 
    --The wodata_id_type column is used to indicate the source of the wodata_id
    --there are three different tables used wo, womatl and womatlvar
    --wodata_id_type = 1 = wo_id
    --wodata_id_type = 2 = womatl_id
    --wodata_id_type = 3 = wooper_id
    --initialise values
    _level := 0;   
    --get top level works orders
    FOR _x IN
       SELECT wo_id,wo_number,wo_subnumber,wo_status,wo_startdate,
         wo_duedate,wo_adhoc,wo_itemsite_id,itemsite_qtyonhand,
         wo_qtyord,wo_qtyrcv,wo_prodnotes, item_number,
         item_descrip1, item_descrip2, item_listprice, uom_name
       FROM wo, itemsite, item, uom     
       WHERE ((wo_id = pwoid)
         AND (itemsite_id = wo_itemsite_id)
         AND (itemsite_item_id=item_id)
         AND (item_inv_uom_id=uom_id))      
       ORDER BY wo_number, wo_subnumber
    LOOP
        _row.wodata_id := _x.wo_id;
        _row.wodata_id_type := 1;            
        _row.wodata_number := _x.wo_number;
        _row.wodata_subnumber := _x.wo_subnumber;
        _row.wodata_itemnumber := _x.item_number;
        _row.wodata_descrip := _x.item_descrip1 || '-' || _x.item_descrip2;
        _row.wodata_status := _x.wo_status;
        _row.wodata_startdate := _x.wo_startdate;
        _row.wodata_duedate := _x.wo_duedate;
        _row.wodata_adhoc := _x.wo_adhoc;     
        _row.wodata_itemsite_id := _x.wo_itemsite_id;
        _row.wodata_custprice := _x.item_listprice;
        _row.wodata_listprice := _x.item_listprice;
        _row.wodata_qoh := _x.itemsite_qtyonhand;
        _row.wodata_short := noneg(_x.wo_qtyord - _x.wo_qtyrcv);
        _row.wodata_qtyrcv := _x.wo_qtyrcv;   
        _row.wodata_qtyordreq := _x.wo_qtyord;   
        _row.wodata_qtyuom := _x.uom_name;    
        _row.wodata_scrap := 0;        
        _row.wodata_notes := _x.wo_prodnotes;     
        _row.wodata_level := _level;                
        RETURN NEXT _row;
        IF (pshowmatl AND NOT pshowops) THEN
          --expand materials      
          FOR _subrow IN
             SELECT * FROM indentedwomatl(pwoid, _level)
          LOOP                                                  
            RETURN NEXT _subrow;
          END LOOP;
        END IF;
        
        IF ((pshowmatl OR pshowindent) AND NOT pshowops) THEN
          --expand next level down               
          FOR _subrow IN
           SELECT * FROM indentedwo(_x.wo_id, NULL, _level + 1, pshowmatl, pshowindent) 
          LOOP                                           
            RETURN NEXT _subrow; 
          END LOOP;
        END IF;
          
        IF (pshowops) THEN
         --expand materials not on operations   
         IF (pshowmatl) THEN   
           FOR _subrow IN
             SELECT * FROM indentedwomatl(pwoid, -1, _level)
           LOOP                                                  
             RETURN NEXT _subrow;
           END LOOP;
         END IF;

         IF (pshowmatl OR pshowindent) THEN
           --expand next level down             
           FOR _subrow IN
             SELECT * FROM indentedwo(_x.wo_id, -1, _level + 1,  pshowmatl, pshowindent) 
           LOOP                                           
             RETURN NEXT _subrow; 
           END LOOP;
         END IF;

         --expand opeartions
         FOR _opx IN
           SELECT * FROM xtmfg.indentedwoops(pwoid,_level)
         LOOP
           RETURN NEXT _opx;

           IF (pshowmatl) THEN  
              --expand materials on operations      
              FOR _subrow IN
                 SELECT * FROM indentedwomatl(pwoid, _opx.wodata_id, _level + 1)
              LOOP                                                  
                RETURN NEXT _subrow;
              END LOOP;
           END IF;

           IF (pshowmatl OR pshowindent) THEN
              --expand next level down             
              FOR _subrow IN
                SELECT * FROM indentedwo(_x.wo_id, _opx.wodata_id, _level + 2,  pshowmatl, pshowindent) 
              LOOP                                           
                RETURN NEXT _subrow; 
              END LOOP;
           END IF; 
         END LOOP; 
       END IF;                           
  END LOOP;                     
  RETURN;
END;
$_$;


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

--
-- TOC entry 1807 (class 1255 OID 146566391)
-- Dependencies: 2711 4536 8
-- Name: indentedwo(integer, integer, integer, boolean, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION indentedwo(integer, integer, integer, boolean, boolean) RETURNS SETOF wodata
    LANGUAGE plpgsql
    AS $_$
-- Copyright (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;   
   pwooperid ALIAS FOR $2;
   plevel ALIAS FOR $3;
   pshowmatl ALIAS FOR $4; 
   pshowindent ALIAS FOR $5;  
  _row wodata%ROWTYPE;   
  _opx wodata%ROWTYPE; 
  _x RECORD;
  _subx RECORD;
  _index INTEGER;
  _level INTEGER;
  _qry TEXT;
   
BEGIN 
    --The wodata id column is used to indicate the source of the id
    --there are three different tables used wo, womatl and womatlvar
    --wodata_id_type = 1 = wo_id
    --wodata_id_type = 2 = womatl_id
    --wodata_id_type = 3 = wooper_id
    _level := (plevel + 1);    
    --find all WO with the ordid of the next level up
    _qry := 'SELECT wo_id,wo_number,wo_subnumber,wo_status,wo_startdate,wo_duedate,
         wo_adhoc,wo_itemsite_id,itemsite_qtyonhand,wo_qtyord,wo_qtyrcv, wo_prodnotes,
         item_number,item_descrip1, item_descrip2, item_listprice, uom_name,
         womatl_qtyiss, womatl_scrap, womatl_wooper_id
       FROM itemsite,  wo, item, uom, womatl 
       WHERE ((wo_ordid = ' || pwoid || ')
         AND (wo_ordtype = ''W'')
         AND (itemsite_item_id=item_id)
         AND (item_inv_uom_id=uom_id)   
         AND (wo_womatl_id=womatl_id)   
         AND (wo_itemsite_id = itemsite_id) ';

    IF (pwooperid IS NOT NULL) THEN
      _qry := _qry || ' AND (womatl_wooper_id=' || pwooperid || ') ';
    END IF;               

    _qry := _qry || ') ORDER BY wo_number, wo_subnumber';
 /* if (pwooperid IS NOT NULL) THEN
    raise exception 'stop %',_qry;
  END IF;*/
    FOR _x IN
      EXECUTE _qry
    LOOP 
        _row.wodata_id := _x.wo_id;
        _row.wodata_id_type := 1;                     
        _row.wodata_number := _x.wo_number;
        _row.wodata_subnumber := _x.wo_subnumber;
        _row.wodata_itemnumber := _x.item_number;
        _row.wodata_descrip := _x.item_descrip1 || '-' || _x.item_descrip2;
        _row.wodata_status := _x.wo_status;
        _row.wodata_startdate := _x.wo_startdate;
        _row.wodata_duedate := _x.wo_duedate;
        _row.wodata_adhoc := _x.wo_adhoc;      
        _row.wodata_itemsite_id := _x.wo_itemsite_id;        
        _row.wodata_custprice := _x.item_listprice;
        _row.wodata_listprice := _x.item_listprice;
        _row.wodata_qoh := _x.itemsite_qtyonhand;
        _row.wodata_short := noneg(_x.wo_qtyord - _x.wo_qtyrcv);
        _row.wodata_qtyiss := _x.womatl_qtyiss;  
        _row.wodata_qtyrcv := _x.wo_qtyrcv;   
        _row.wodata_qtyordreq := _x.wo_qtyord; 
	_row.wodata_scrap := _x.womatl_scrap;  
        _row.wodata_notes := _x.wo_prodnotes;       
        _row.wodata_level := plevel;                
        RETURN NEXT _row;  
        --if indentation require expand next level
        IF (pshowindent AND pwooperid IS NULL) THEN
          IF (pshowmatl AND pshowindent) THEN    
      	    --get materials for this level
            FOR _subx IN
              SELECT * FROM indentedwomatl(_x.wo_id, plevel) 
	    LOOP                                            
	      RETURN NEXT _subx;
  	    END LOOP;
          END IF;

          IF (pshowindent) THEN  
            --expand lower levels 
            FOR _subx IN
              SELECT * FROM indentedwo(_x.wo_id, NULL, _level, pshowmatl, pshowindent )
            LOOP                                           
	      RETURN NEXT _subx; 
            END LOOP; 
          END IF;    
            
        ELSIF (pshowindent) THEN --Handle operations
          --expand materials not on operations   
          IF (pshowmatl) THEN   
            FOR _subx IN
              SELECT * FROM indentedwomatl(_x.wo_id, -1, plevel)
            LOOP                                                  
              RETURN NEXT _subx;
            END LOOP;
          END IF;

          --expand next level down not on operations
          FOR _subx IN
            SELECT * FROM indentedwo(_x.wo_id, -1, _level,  pshowmatl, pshowindent) 
          LOOP                                           
            RETURN NEXT _subx; 
          END LOOP;
          
          --expand operations
          FOR _opx IN
            SELECT * FROM xtmfg.indentedwoops(_x.wo_id,plevel)
          LOOP
            RETURN NEXT _opx;

            IF (pshowmatl) THEN  
              --expand materials on operations      
              FOR _subx IN
                 SELECT * FROM indentedwomatl(_x.wo_id, _opx.wodata_id, _level)
              LOOP                                                  
                RETURN NEXT _subx;
                --	raise exception 'stop %',_opx.wodata_id;
              END LOOP;
            END IF;
              
            --expand next level down   
            FOR _subx IN
              SELECT * FROM indentedwo(_x.wo_id, _opx.wodata_id, _level + 2,  pshowmatl, pshowindent) 
            LOOP                                        
              RETURN NEXT _subx; 
            END LOOP;
              
          END LOOP;
        END IF;      	              
      END LOOP;                         
  RETURN;
END;
$_$;


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

--
-- TOC entry 1808 (class 1255 OID 146566392)
-- Dependencies: 2711 4536 8
-- Name: indentedwomatl(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION indentedwomatl(integer, integer) RETURNS SETOF wodata
    LANGUAGE plpgsql
    AS $_$
-- Copyright (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;   
   plevel ALIAS FOR $2;
  _subx RECORD;
   
BEGIN
  FOR _subx IN
    SELECT * FROM indentedwomatl(pwoid, NULL::integer, plevel)
  LOOP
    RETURN NEXT _subx;
  END LOOP;        
  RETURN;
END;
$_$;


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

--
-- TOC entry 1809 (class 1255 OID 146566393)
-- Dependencies: 2711 4536 8
-- Name: indentedwomatl(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION indentedwomatl(integer, integer, integer) RETURNS SETOF wodata
    LANGUAGE plpgsql
    AS $_$
-- Copyright (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;  
   pwooperid ALIAS FOR $2; 
   plevel ALIAS FOR $3;
  _status TEXT; 
  _subrow wodata%ROWTYPE;  
  _subx RECORD;
  _level INTEGER;
  _qry TEXT;
   
BEGIN
  --The wodata id column is used to indicate the source of the id
  --there are three different tables used wo, womatl and womatlvar
  --wodata_id_type = 1 = wo_id
  --wodata_id_type = 2 = womatl_id
  --wodata_id_type = 3 = wooper_id

  _qry := 'SELECT womatl_id, wo_number, wo_subnumber, 
      wo_startdate, womatl_duedate, womatl_itemsite_id,
      itemsite_qtyonhand, womatl_qtyreq, womatl_qtyiss,
      womatl_qtyper, womatl_qtyreq, womatl_scrap,
      womatl_ref, womatl_notes, womatl_price, item_listprice,
      item_number, item_descrip1, item_descrip2,
      uom_name
    FROM womatl, wo, itemsite, item, uom
    WHERE ((wo_id = womatl_wo_id)
     AND (wo_id = ' || pwoid || ')
     AND (womatl_itemsite_id = itemsite_id)
     AND (itemsite_item_id=item_id)
     AND (womatl_uom_id=uom_id) ';
-- Need to display in case child w/o is deleted
--     AND (NOT womatl_createwo OR womatl_createwo IS NULL) ';

  IF (pwooperid IS NOT NULL) THEN
    _qry := _qry || 'AND (womatl_wooper_id=' || pwooperid  || ')';
  END IF;

  _qry := _qry || ') ORDER BY item_number;';
  
  _level := plevel + 1;    
  SELECT wo_status FROM wo WHERE wo_id = pwoid  LIMIT 1 INTO _status;
 
  FOR _subx IN
    EXECUTE _qry
  LOOP
    _subrow.wodata_id := _subx.womatl_id;
    _subrow.wodata_id_type  := 2;       
    _subrow.wodata_number := _subx.wo_number;
    _subrow.wodata_subnumber := _subx.wo_subnumber;
    _subrow.wodata_itemnumber := _subx.item_number;
    _subrow.wodata_descrip := _subx.item_descrip1 || '-' || _subx.item_descrip2;
    _subrow.wodata_status := _status;
    _subrow.wodata_startdate := _subx.wo_startdate;
    _subrow.wodata_duedate := _subx.womatl_duedate;
    _subrow.wodata_itemsite_id := _subx.womatl_itemsite_id;    
    _subrow.wodata_custprice := _subx.womatl_price;
    _subrow.wodata_listprice := _subx.item_listprice;
    _subrow.wodata_qoh := _subx.itemsite_qtyonhand;
    IF((_subx.itemsite_qtyonhand > (_subx.womatl_qtyreq - _subx.womatl_qtyiss))) THEN
      _subrow.wodata_short := 0;
    ELSE
      _subrow.wodata_short := (_subx.womatl_qtyreq - _subx.womatl_qtyiss) -  _subx.itemsite_qtyonhand;
    END IF;
    _subrow.wodata_qtyper := _subx.womatl_qtyper;
    _subrow.wodata_qtyiss := _subx.womatl_qtyiss;         
    _subrow.wodata_qtyordreq := _subx.womatl_qtyreq;     
    _subrow.wodata_qtyuom := _subx.uom_name;   
    _subrow.wodata_scrap := _subx.womatl_scrap;        
    _subrow.wodata_notes := _subx.womatl_notes;
    _subrow.wodata_ref := _subx.womatl_ref;       
    _subrow.wodata_level := _level;                                   
    RETURN NEXT _subrow; 
  END LOOP;     
        
  RETURN;
END;
$_$;


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

--
-- TOC entry 1810 (class 1255 OID 146566394)
-- Dependencies: 4536 8
-- Name: initeffectivextuser(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION initeffectivextuser() RETURNS void
    LANGUAGE plpgsql
    AS $$
-- 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
  -- Effective users use a temporary table to store the user information
  -- and this function, called by the other functions, makes sure the temp
  -- tables exist first.
  PERFORM *
     FROM pg_catalog.pg_class
    WHERE relname = 'effective_user'
      AND relnamespace = pg_catalog.pg_my_temp_schema();

  IF NOT FOUND THEN
    CREATE TEMPORARY TABLE effective_user (
      effective_key TEXT,
      effective_value TEXT
    );
    CREATE UNIQUE INDEX effective_user_pkey ON effective_user (effective_key);
  END IF;
END;
$$;


ALTER FUNCTION public.initeffectivextuser() OWNER TO admin;

--
-- TOC entry 1812 (class 1255 OID 146566395)
-- Dependencies: 4536 8
-- Name: initialdistribution(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION initialdistribution(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
  pItemsiteid ALIAS FOR $1;
  pLocationid ALIAS FOR $2;
  _itemlocid INTEGER;
  _invhistid INTEGER;
  _itemlocSeries INTEGER;
  _r RECORD;

BEGIN

--  Make sure the passed itemsite points to a real item
  IF ( (SELECT (item_type IN ('R', 'F') OR itemsite_costmethod = 'J')
         FROM itemsite, item
         WHERE ( (itemsite_item_id=item_id)
          AND (itemsite_id=pItemsiteid) ) ) ) THEN
    RETURN 0;
  END IF;

  _itemlocSeries := NEXTVAL('itemloc_series_seq');

--  Reassign the location_id for all existing itemlocs if
--  the passed itemsite is already lot/serial controlled
  IF ( ( SELECT (itemsite_controlmethod IN ('L', 'S'))
         FROM itemsite
         WHERE (itemsite_id=pItemsiteid) ) ) THEN

    FOR _r IN SELECT itemloc_id, itemloc_ls_id, itemloc_qty
              FROM itemloc
              WHERE (itemloc_itemsite_id=pItemsiteid) LOOP

--  Create the RL transaction
      SELECT NEXTVAL('invhist_invhist_id_seq') INTO _invhistid;
      INSERT INTO invhist
      ( invhist_id, invhist_itemsite_id, invhist_series,
        invhist_transtype, invhist_invqty,
        invhist_qoh_before, invhist_qoh_after,
        invhist_comments,
        invhist_invuom, invhist_unitcost, invhist_hasdetail,
        invhist_costmethod, invhist_value_before, invhist_value_after ) 
      SELECT _invhistid, itemsite_id, _itemlocSeries,
             'RL', 0,
             _r.itemloc_qty, _r.itemloc_qty,
             'Initial Distribution',
             uom_name, stdCost(item_id), TRUE,
             itemsite_costmethod, itemsite_value, itemsite_value
      FROM item, itemsite, uom
      WHERE ( (itemsite_item_id=item_id)
       AND (item_inv_uom_id=uom_id)
       AND (itemsite_controlmethod <> 'N')
       AND (itemsite_id=pItemsiteid) );

--  Update the itemloc
      UPDATE itemloc
      SET itemloc_location_id=pLocationid
      WHERE (itemloc_id=_r.itemloc_id);

--  Record the detail transaction
      INSERT INTO invdetail
      ( invdetail_invhist_id, invdetail_location_id, invdetail_ls_id,
        invdetail_qty, invdetail_qty_before, invdetail_qty_after )
      VALUES
      ( _invhistid, pLocationid, _r.itemloc_ls_id,
        _r.itemloc_qty, 0, _r.itemloc_qty );

--  Adjust QOH if this itemlocdist is to/from a non-netable location
      IF ( SELECT (NOT location_netable)
           FROM location
           WHERE (location_id=pLocationid) ) THEN

        INSERT INTO invhist
        ( invhist_itemsite_id, invhist_series,
          invhist_transtype, invhist_invqty,
          invhist_qoh_before, invhist_qoh_after,
          invhist_comments,
          invhist_invuom, invhist_unitcost,
          invhist_costmethod, invhist_value_before, invhist_value_after  ) 
        SELECT itemsite_id, _itemlocSeries,
               'NN', (_r.itemloc_qty * -1),
               _r.itemloc_qty, 0,
               'Initial Distribution',
               uom_name, stdCost(item_id),
               itemsite_costmethod, itemsite_value, itemsite_value
        FROM itemsite, item, uom
        WHERE ( (itemsite_item_id=item_id)
         AND (item_inv_uom_id=uom_id)
         AND (itemsite_controlmethod <> 'N')
         AND (itemsite_id=pItemsiteid) );

        UPDATE itemsite
        SET itemsite_nnqoh = (itemsite_nnqoh + _r.itemloc_qty),
            itemsite_qtyonhand = (itemsite_qtyonhand - _r.itemloc_qty) 
        WHERE (itemsite_id=pItemsiteid);

      END IF;

    END LOOP;

  ELSE
--  The passed itemsite is not lot/serial controlled
--  Make sure that there are not any stagnent itemlocs
    DELETE FROM itemloc
    WHERE (itemloc_itemsite_id=pItemsiteid);

--  Create the RL transaction
    SELECT NEXTVAL('invhist_invhist_id_seq') INTO _invhistid;
    INSERT INTO invhist
    ( invhist_id, invhist_itemsite_id, invhist_series,
      invhist_transtype, invhist_invqty,
      invhist_qoh_before, invhist_qoh_after,
      invhist_comments,
      invhist_invuom, invhist_unitcost, invhist_hasdetail,
      invhist_costmethod, invhist_value_before, invhist_value_after  ) 
    SELECT _invhistid, itemsite_id, _itemlocSeries,
           'RL', 0,
           itemsite_qtyonhand, itemsite_qtyonhand,
           'Initial Distribution',
           uom_name, stdCost(item_id), TRUE,
           itemsite_costmethod, itemsite_value, itemsite_value
    FROM item, itemsite, uom
    WHERE ( (itemsite_item_id=item_id)
     AND (item_inv_uom_id=uom_id)
     AND (itemsite_controlmethod <> 'N')
     AND (itemsite_id=pItemsiteid) );

--  Create the itemloc
    SELECT NEXTVAL('itemloc_itemloc_id_seq') INTO _itemlocid;
    INSERT INTO itemloc
    ( itemloc_id, itemloc_itemsite_id, itemloc_location_id,
      itemloc_expiration, itemloc_qty )
    SELECT _itemlocid, itemsite_id, pLocationid,
           endOfTime(), itemsite_qtyonhand
    FROM itemsite
    WHERE (itemsite_id=pItemsiteid);

--  Record the detail transaction
    INSERT INTO invdetail
    ( invdetail_invhist_id, invdetail_location_id,
      invdetail_qty, invdetail_qty_before, invdetail_qty_after )
    SELECT _invhistid, pLocationid,
           itemsite_qtyonhand, 0, itemsite_qtyonhand
    FROM itemsite
    WHERE (itemsite_id=pItemsiteid);

--  Adjust QOH if this itemlocdist is to/from a non-netable location
    IF ( SELECT (NOT location_netable)
         FROM location
         WHERE (location_id=pLocationid) ) THEN

      INSERT INTO invhist
      ( invhist_itemsite_id, invhist_series,
        invhist_transtype, invhist_invqty,
        invhist_qoh_before, invhist_qoh_after,
        invhist_comments,
        invhist_invuom, invhist_unitcost,
        invhist_costmethod, invhist_value_before, invhist_value_after  ) 
      SELECT itemsite_id, _itemlocSeries,
             'NN', (itemloc_qty * -1),
             itemloc_qty, 0,
             'Initial Distribution',
             uom_name, stdCost(item_id),
             itemsite_costmethod, itemsite_value, itemsite_value
      FROM itemloc, itemsite, item, uom
      WHERE ( (itemsite_item_id=item_id)
       AND (item_inv_uom_id=uom_id)
       AND (itemsite_controlmethod <> 'N')
       AND (itemloc_itemsite_id=itemsite_id)
       AND (itemloc_id=_itemlocid) );

      UPDATE itemsite
      SET itemsite_nnqoh = itemsite_qtyonhand,
          itemsite_qtyonhand = 0 
      FROM itemloc
      WHERE ( (itemloc_itemsite_id=itemsite_id)
       AND (itemloc_id=_itemlocid) );

    END IF;

  END IF;

  RETURN _itemlocid;

END;
$_$;


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

--
-- TOC entry 207 (class 1259 OID 146566396)
-- Dependencies: 5956 5957 5958 5959 5960 5961 5962 5963 5964 8
-- Name: apopen; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE apopen (
    apopen_id integer DEFAULT nextval(('"apopen_apopen_id_seq"'::text)::regclass) NOT NULL,
    apopen_docdate date,
    apopen_duedate date,
    apopen_terms_id integer,
    apopen_vend_id integer,
    apopen_doctype character(1),
    apopen_docnumber text,
    apopen_amount numeric(20,2),
    apopen_notes text,
    apopen_posted boolean,
    apopen_reference text,
    apopen_invcnumber text,
    apopen_ponumber text,
    apopen_journalnumber integer,
    apopen_paid numeric(20,2) DEFAULT 0,
    apopen_open boolean,
    apopen_username text,
    apopen_discount boolean DEFAULT false NOT NULL,
    apopen_accnt_id integer DEFAULT (-1),
    apopen_curr_id integer DEFAULT basecurrid(),
    apopen_closedate date,
    apopen_distdate date,
    apopen_void boolean DEFAULT false NOT NULL,
    apopen_curr_rate numeric NOT NULL,
    apopen_discountable_amount numeric(20,2) DEFAULT 0,
    apopen_status text,
    CONSTRAINT apopen_apopen_status_check CHECK ((((apopen_status = 'O'::text) OR (apopen_status = 'H'::text)) OR (apopen_status = 'C'::text))),
    CONSTRAINT apopen_apopen_status_notnull CHECK ((apopen_status IS NOT NULL))
);


ALTER TABLE public.apopen OWNER TO admin;

--
-- TOC entry 8981 (class 0 OID 0)
-- Dependencies: 207
-- Name: TABLE apopen; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE apopen IS 'Accounts Payable (A/P) open Items information';


--
-- TOC entry 208 (class 1259 OID 146566411)
-- Dependencies: 5966 8
-- Name: curr_symbol; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE curr_symbol (
    curr_id integer NOT NULL,
    curr_base boolean DEFAULT false NOT NULL,
    curr_name character varying(50) NOT NULL,
    curr_symbol character varying(9) NOT NULL,
    curr_abbr character varying(3) NOT NULL
);


ALTER TABLE public.curr_symbol OWNER TO admin;

--
-- TOC entry 8983 (class 0 OID 0)
-- Dependencies: 208
-- Name: TABLE curr_symbol; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE curr_symbol IS 'Currency Names, Symbols, and Abbreviations';


--
-- TOC entry 209 (class 1259 OID 146566415)
-- Dependencies: 5967 5968 5969 8
-- Name: terms; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE terms (
    terms_id integer DEFAULT nextval(('terms_terms_id_seq'::text)::regclass) NOT NULL,
    terms_code text NOT NULL,
    terms_descrip text,
    terms_type character(1),
    terms_duedays integer,
    terms_discdays integer,
    terms_discprcnt numeric(10,6),
    terms_cutoffday integer,
    terms_ap boolean,
    terms_ar boolean,
    terms_fincharg boolean DEFAULT true NOT NULL,
    CONSTRAINT terms_terms_code_check CHECK ((terms_code <> ''::text))
);


ALTER TABLE public.terms OWNER TO admin;

--
-- TOC entry 8985 (class 0 OID 0)
-- Dependencies: 209
-- Name: TABLE terms; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE terms IS 'Billing Terms information';


--
-- TOC entry 210 (class 1259 OID 146566424)
-- Dependencies: 5970 5971 5972 5973 5974 5975 5976 5977 5978 5979 5980 5981 5982 5983 8
-- Name: vendinfo; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE vendinfo (
    vend_id integer DEFAULT nextval(('vend_vend_id_seq'::text)::regclass) NOT NULL,
    vend_name text,
    vend_lastpurchdate date,
    vend_active boolean,
    vend_po boolean,
    vend_comments text,
    vend_pocomments text,
    vend_number text NOT NULL,
    vend_1099 boolean,
    vend_exported boolean,
    vend_fobsource character(1),
    vend_fob text,
    vend_terms_id integer,
    vend_shipvia text,
    vend_vendtype_id integer,
    vend_qualified boolean,
    vend_ediemail text,
    vend_ediemailbody text,
    vend_edisubject text,
    vend_edifilename text,
    vend_accntnum text,
    vend_emailpodelivery boolean,
    vend_restrictpurch boolean,
    vend_edicc text,
    vend_curr_id integer DEFAULT basecurrid(),
    vend_cntct1_id integer,
    vend_cntct2_id integer,
    vend_addr_id integer,
    vend_match boolean DEFAULT false NOT NULL,
    vend_ach_enabled boolean DEFAULT false NOT NULL,
    vend_ach_accnttype text,
    vend_ach_use_vendinfo boolean DEFAULT true NOT NULL,
    vend_ach_indiv_number text DEFAULT ''::text NOT NULL,
    vend_ach_indiv_name text DEFAULT ''::text NOT NULL,
    vend_ediemailhtml boolean DEFAULT false NOT NULL,
    vend_ach_routingnumber bytea DEFAULT '\x00'::bytea NOT NULL,
    vend_ach_accntnumber bytea DEFAULT '\x00'::bytea NOT NULL,
    vend_taxzone_id integer,
    vend_accnt_id integer,
    vend_expcat_id integer DEFAULT (-1),
    vend_tax_id integer DEFAULT (-1),
    CONSTRAINT vendinfo_vend_ach_accnttype_check CHECK (((vend_ach_accnttype = 'K'::text) OR (vend_ach_accnttype = 'C'::text))),
    CONSTRAINT vendinfo_vend_number_check CHECK ((vend_number <> ''::text))
);


ALTER TABLE public.vendinfo OWNER TO admin;

--
-- TOC entry 8987 (class 0 OID 0)
-- Dependencies: 210
-- Name: TABLE vendinfo; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE vendinfo IS 'Vendor information';


--
-- TOC entry 8988 (class 0 OID 0)
-- Dependencies: 210
-- Name: COLUMN vendinfo.vend_ach_accnttype; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN vendinfo.vend_ach_accnttype IS 'Type of bank account: K = checKing, C = Cash = savings. These values were chosen to be consistent with bankaccnt_type.';



--
-- TOC entry 211 (class 1259 OID 146566444)
-- Dependencies: 8471 6
-- Name: apmemo; Type: VIEW; Schema: api; Owner: admin
--

CREATE VIEW apmemo AS
    SELECT vendinfo.vend_number AS vendor_number, apopen.apopen_docdate AS document_date, apopen.apopen_duedate AS due_date, CASE WHEN (apopen.apopen_doctype = 'C'::bpchar) THEN 'Credit Memo'::text ELSE 'Debit Memo'::text END AS document_type, apopen.apopen_docnumber AS document_number, apopen.apopen_ponumber AS po_number, apopen.apopen_journalnumber AS journal_number, terms.terms_code AS terms, curr.curr_abbr AS currency, apopen.apopen_amount AS amount, apopen.apopen_paid AS paid, (apopen.apopen_amount - apopen.apopen_paid) AS balance, apopen.apopen_notes AS notes, CASE WHEN (apopen.apopen_accnt_id = (-1)) THEN NULL::text ELSE public.formatglaccount(apopen.apopen_accnt_id) END AS alternate_prepaid_account FROM (((public.apopen LEFT JOIN public.vendinfo ON ((vendinfo.vend_id = apopen.apopen_vend_id))) LEFT JOIN public.curr_symbol curr ON ((curr.curr_id = apopen.apopen_curr_id))) LEFT JOIN public.terms ON ((terms.terms_id = apopen.apopen_terms_id))) WHERE (apopen.apopen_doctype = ANY (ARRAY['C'::bpchar, 'D'::bpchar]));


ALTER TABLE api.apmemo OWNER TO admin;

--
-- TOC entry 8990 (class 0 OID 0)
-- Dependencies: 211
-- Name: VIEW apmemo; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW apmemo IS 'A/P Credit and Debit Memo';



--
-- TOC entry 1813 (class 1255 OID 146566449)
-- Dependencies: 2804 4536 8
-- Name: insertapmemo(api.apmemo); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  IF (pNew.document_type = 'Credit Memo') THEN
    SELECT createAPCreditMemo( getVendId(pNew.vendor_number),
                               pNew.document_number,
                               pNew.po_number,
                               pNew.document_date,
                               pNew.amount,
                               pNew.notes,
                               getGLAccntId(pNew.alternate_prepaid_account),
                               pNew.due_date,
                               getTermsId(pNew.terms) ) INTO _result;
    IF (_result <= 0) THEN
      RAISE EXCEPTION 'Function createAPCreditMemo failed with result = %', _result;
    END IF;
  ELSE
    IF (pNew.document_type = 'Debit Memo') THEN
      SELECT createAPDebitMemo( null, getVendId(pNew.vendor_number),
                                pNew.journal_number,
                                pNew.document_number,
                                pNew.po_number,
                                pNew.document_date,
                                pNew.amount,
                                pNew.notes,
                                getGLAccntId(pNew.alternate_prepaid_account),
                                pNew.due_date,
                                getTermsId(pNew.terms),
                                COALESCE(getCurrId(pNew.currency), baseCurrId()) ) INTO _result;
      IF (_result <= 0) THEN
        RAISE EXCEPTION 'Function createAPDebitMemo failed with result = %', _result;
      END IF;
    ELSE
      RAISE EXCEPTION 'Function insertAPMemo failed, invalid Document Type';
    END IF;
  END IF;

  RETURN TRUE;
END;
$_$;


ALTER FUNCTION public.insertapmemo(api.apmemo) OWNER TO admin;

--
-- TOC entry 212 (class 1259 OID 146566450)
-- Dependencies: 5984 5985 5986 5987 5988 5989 5990 5991 5992 8
-- Name: aropen; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE aropen (
    aropen_id integer DEFAULT nextval(('"aropen_aropen_id_seq"'::text)::regclass) NOT NULL,
    aropen_docdate date NOT NULL,
    aropen_duedate date NOT NULL,
    aropen_terms_id integer,
    aropen_cust_id integer,
    aropen_doctype character(1),
    aropen_docnumber text,
    aropen_applyto text,
    aropen_ponumber text,
    aropen_amount numeric(20,2) NOT NULL,
    aropen_notes text,
    aropen_posted boolean DEFAULT false NOT NULL,
    aropen_salesrep_id integer,
    aropen_commission_due numeric(20,2),
    aropen_commission_paid boolean DEFAULT false,
    aropen_ordernumber text,
    aropen_cobmisc_id integer DEFAULT (-1),
    aropen_journalnumber integer,
    aropen_paid numeric(20,2) DEFAULT 0,
    aropen_open boolean,
    aropen_username text,
    aropen_rsncode_id integer,
    aropen_salescat_id integer DEFAULT (-1),
    aropen_accnt_id integer DEFAULT (-1),
    aropen_curr_id integer DEFAULT basecurrid(),
    aropen_closedate date,
    aropen_distdate date,
    aropen_curr_rate numeric NOT NULL,
    aropen_discount boolean DEFAULT false NOT NULL,
    aropen_fincharg_date date,
    aropen_fincharg_amount numeric(20,2)
);


ALTER TABLE public.aropen OWNER TO admin;

--
-- TOC entry 8992 (class 0 OID 0)
-- Dependencies: 212
-- Name: TABLE aropen; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE aropen IS 'Accounts Receivable (A/R) open Items information';


--
-- TOC entry 213 (class 1259 OID 146566465)
-- Dependencies: 5993 5994 5995 5996 5997 5998 5999 6000 6001 6002 6003 6004 6005 8
-- Name: custinfo; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE custinfo (
    cust_id integer DEFAULT nextval(('cust_cust_id_seq'::text)::regclass) NOT NULL,
    cust_active boolean NOT NULL,
    cust_custtype_id integer,
    cust_salesrep_id integer,
    cust_commprcnt numeric(10,6),
    cust_name text,
    cust_creditlmt integer,
    cust_creditrating text,
    cust_financecharge boolean,
    cust_backorder boolean NOT NULL,
    cust_partialship boolean NOT NULL,
    cust_terms_id integer,
    cust_discntprcnt numeric(10,6) NOT NULL,
    cust_balmethod character(1) NOT NULL,
    cust_ffshipto boolean NOT NULL,
    cust_shipform_id integer,
    cust_shipvia text,
    cust_blanketpos boolean NOT NULL,
    cust_shipchrg_id integer NOT NULL,
    cust_creditstatus character(1) NOT NULL,
    cust_comments text,
    cust_ffbillto boolean NOT NULL,
    cust_usespos boolean NOT NULL,
    cust_number text NOT NULL,
    cust_dateadded date DEFAULT ('now'::text)::date,
    cust_exported boolean DEFAULT false,
    cust_emaildelivery boolean DEFAULT false,
    cust_ediemail text,
    cust_edisubject text,
    cust_edifilename text,
    cust_ediemailbody text,
    cust_autoupdatestatus boolean NOT NULL,
    cust_autoholdorders boolean NOT NULL,
    cust_edicc text,
    cust_ediprofile_id integer,
    cust_preferred_warehous_id integer DEFAULT (-1) NOT NULL,
    cust_curr_id integer DEFAULT basecurrid(),
    cust_creditlmt_curr_id integer DEFAULT basecurrid(),
    cust_cntct_id integer,
    cust_corrcntct_id integer,
    cust_soemaildelivery boolean DEFAULT false,
    cust_soediemail text,
    cust_soedisubject text,
    cust_soedifilename text,
    cust_soediemailbody text,
    cust_soedicc text,
    cust_soediprofile_id integer,
    cust_gracedays integer,
    cust_ediemailhtml boolean DEFAULT false NOT NULL,
    cust_soediemailhtml boolean DEFAULT false NOT NULL,
    cust_taxzone_id integer,
    cust_statementcycle text,
    CONSTRAINT custinfo_balmethod_check CHECK (((cust_balmethod = 'B'::bpchar) OR (cust_balmethod = 'O'::bpchar))),
    CONSTRAINT custinfo_creditstatus_check CHECK ((((cust_creditstatus = 'G'::bpchar) OR (cust_creditstatus = 'W'::bpchar)) OR (cust_creditstatus = 'H'::bpchar))),
    CONSTRAINT custinfo_cust_number_check CHECK ((cust_number <> ''::text))
);


ALTER TABLE public.custinfo OWNER TO admin;

--
-- TOC entry 8994 (class 0 OID 0)
-- Dependencies: 213
-- Name: TABLE custinfo; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE custinfo IS 'Customer information';


--
-- TOC entry 8995 (class 0 OID 0)
-- Dependencies: 213
-- Name: COLUMN custinfo.cust_ediemail; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_ediemail IS 'Deprecated column - DO NOT USE';


--
-- TOC entry 8996 (class 0 OID 0)
-- Dependencies: 213
-- Name: COLUMN custinfo.cust_edisubject; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_edisubject IS 'Deprecated column - DO NOT USE';


--
-- TOC entry 8997 (class 0 OID 0)
-- Dependencies: 213
-- Name: COLUMN custinfo.cust_edifilename; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_edifilename IS 'Deprecated column - DO NOT USE';


--
-- TOC entry 8998 (class 0 OID 0)
-- Dependencies: 213
-- Name: COLUMN custinfo.cust_ediemailbody; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_ediemailbody IS 'Deprecated column - DO NOT USE';


--
-- TOC entry 8999 (class 0 OID 0)
-- Dependencies: 213
-- Name: COLUMN custinfo.cust_edicc; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_edicc IS 'Deprecated column - DO NOT USE';


--
-- TOC entry 9000 (class 0 OID 0)
-- Dependencies: 213
-- Name: COLUMN custinfo.cust_ediprofile_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_ediprofile_id IS 'Deprecated column - DO NOT USE';


--
-- TOC entry 9001 (class 0 OID 0)
-- Dependencies: 213
-- Name: COLUMN custinfo.cust_soediemail; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_soediemail IS 'Deprecated column - DO NOT USE';


--
-- TOC entry 9002 (class 0 OID 0)
-- Dependencies: 213
-- Name: COLUMN custinfo.cust_soedisubject; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_soedisubject IS 'Deprecated column - DO NOT USE';


--
-- TOC entry 9003 (class 0 OID 0)
-- Dependencies: 213
-- Name: COLUMN custinfo.cust_soedifilename; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_soedifilename IS 'Deprecated column - DO NOT USE';


--
-- TOC entry 9004 (class 0 OID 0)
-- Dependencies: 213
-- Name: COLUMN custinfo.cust_soediemailbody; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_soediemailbody IS 'Deprecated column - DO NOT USE';


--
-- TOC entry 9005 (class 0 OID 0)
-- Dependencies: 213
-- Name: COLUMN custinfo.cust_soedicc; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_soedicc IS 'Deprecated column - DO NOT USE';


--
-- TOC entry 9006 (class 0 OID 0)
-- Dependencies: 213
-- Name: COLUMN custinfo.cust_soediprofile_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_soediprofile_id IS 'Deprecated column - DO NOT USE';


--
-- TOC entry 9007 (class 0 OID 0)
-- Dependencies: 213
-- Name: COLUMN custinfo.cust_ediemailhtml; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_ediemailhtml IS 'Deprecated column - DO NOT USE';


--
-- TOC entry 9008 (class 0 OID 0)
-- Dependencies: 213
-- Name: COLUMN custinfo.cust_soediemailhtml; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_soediemailhtml IS 'Deprecated column - DO NOT USE';


--
-- TOC entry 214 (class 1259 OID 146566484)
-- Dependencies: 6007 8
-- Name: rsncode; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE rsncode (
    rsncode_id integer NOT NULL,
    rsncode_code text NOT NULL,
    rsncode_descrip text,
    rsncode_doctype text,
    CONSTRAINT rsncode_rsncode_code_check CHECK ((rsncode_code <> ''::text))
);


ALTER TABLE public.rsncode OWNER TO admin;

--
-- TOC entry 9010 (class 0 OID 0)
-- Dependencies: 214
-- Name: TABLE rsncode; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE rsncode IS 'Debit/Credit Memo Reason Code information';


--
-- TOC entry 215 (class 1259 OID 146566491)
-- Dependencies: 6009 8
-- Name: salescat; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE salescat (
    salescat_id integer NOT NULL,
    salescat_active boolean,
    salescat_name text NOT NULL,
    salescat_descrip text,
    salescat_sales_accnt_id integer,
    salescat_prepaid_accnt_id integer,
    salescat_ar_accnt_id integer,
    CONSTRAINT salescat_salescat_name_check CHECK ((salescat_name <> ''::text))
);


ALTER TABLE public.salescat OWNER TO admin;

--
-- TOC entry 9012 (class 0 OID 0)
-- Dependencies: 215
-- Name: TABLE salescat; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE salescat IS 'Sales Category information';


--
-- TOC entry 216 (class 1259 OID 146566498)
-- Dependencies: 6010 6011 8
-- Name: salesrep; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE salesrep (
    salesrep_id integer DEFAULT nextval(('salesrep_salesrep_id_seq'::text)::regclass) NOT NULL,
    salesrep_active boolean,
    salesrep_number text NOT NULL,
    salesrep_name text,
    salesrep_commission numeric(8,4),
    salesrep_method character(1),
    salesrep_emp_id integer,
    CONSTRAINT salesrep_salesrep_number_check CHECK ((salesrep_number <> ''::text))
);


ALTER TABLE public.salesrep OWNER TO admin;

--
-- TOC entry 9014 (class 0 OID 0)
-- Dependencies: 216
-- Name: TABLE salesrep; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE salesrep IS 'Sales Representative information';


--
-- TOC entry 9015 (class 0 OID 0)
-- Dependencies: 216
-- Name: COLUMN salesrep.salesrep_emp_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN salesrep.salesrep_emp_id IS 'DEPRECATED - the relationship between Sales Rep and Employee is now maintained through the crmacct table.';



--
-- TOC entry 217 (class 1259 OID 146566506)
-- Dependencies: 8472 6
-- Name: armemo; Type: VIEW; Schema: api; Owner: admin
--

CREATE VIEW armemo AS
    SELECT custinfo.cust_number AS customer_number, aropen.aropen_docdate AS document_date, aropen.aropen_duedate AS due_date, CASE WHEN (aropen.aropen_doctype = 'C'::bpchar) THEN 'Credit Memo'::text ELSE 'Debit Memo'::text END AS document_type, aropen.aropen_docnumber AS document_number, aropen.aropen_applyto AS order_number, aropen.aropen_journalnumber AS journal_number, rsncode.rsncode_code AS reason_code, terms.terms_code AS terms, salesrep.salesrep_number AS sales_rep, curr.curr_abbr AS currency, aropen.aropen_amount AS amount, aropen.aropen_paid AS paid, (aropen.aropen_amount - aropen.aropen_paid) AS balance, aropen.aropen_commission_due AS commission_due, aropen.aropen_commission_paid AS commission_paid, aropen.aropen_notes AS notes, salescat.salescat_name AS alternate_prepaid_sales_category, CASE WHEN (aropen.aropen_accnt_id = (-1)) THEN NULL::text ELSE public.formatglaccount(aropen.aropen_accnt_id) END AS alternate_prepaid_account FROM ((((((public.aropen LEFT JOIN public.custinfo ON ((custinfo.cust_id = aropen.aropen_cust_id))) LEFT JOIN public.curr_symbol curr ON ((curr.curr_id = aropen.aropen_curr_id))) LEFT JOIN public.salesrep ON ((salesrep.salesrep_id = aropen.aropen_salesrep_id))) LEFT JOIN public.terms ON ((terms.terms_id = aropen.aropen_terms_id))) LEFT JOIN public.salescat ON ((salescat.salescat_id = aropen.aropen_salescat_id))) LEFT JOIN public.rsncode ON ((rsncode.rsncode_id = aropen.aropen_rsncode_id))) WHERE (aropen.aropen_doctype = ANY (ARRAY['C'::bpchar, 'D'::bpchar]));


ALTER TABLE api.armemo OWNER TO admin;

--
-- TOC entry 9017 (class 0 OID 0)
-- Dependencies: 217
-- Name: VIEW armemo; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW armemo IS 'A/R Credit and Debit Memo';



--
-- TOC entry 1814 (class 1255 OID 146566511)
-- Dependencies: 2827 4536 8
-- Name: insertarmemo(api.armemo); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  IF (pNew.document_type = 'Credit Memo') THEN
    SELECT createARCreditMemo( NULL,
                               getCustId(pNew.customer_number),
                               pNew.document_number,
                               pNew.order_number,
                               pNew.document_date,
                               pNew.amount,
                               pNew.notes,
                               getRsnId(pNew.reason_code),
                               getSalescatId(pNew.alternate_prepaid_sales_category),
                               getGLAccntId(pNew.alternate_prepaid_account),
                               pNew.due_date,
                               getTermsId(pNew.terms),
                               getSalesrepId(pNew.sales_rep),
                               pNew.commission_due,
                               pNew.journal_number,
                               COALESCE(getCurrId(pNew.currency), baseCurrId()) ) INTO _result;
    IF (_result <= 0) THEN
      RAISE EXCEPTION 'Function createARCreditMemo failed with result = %', _result;
    END IF;
  ELSE
    IF (pNew.document_type = 'Debit Memo') THEN
      SELECT createARDebitMemo( null, getCustId(pNew.customer_number),
                                pNew.journal_number,
                                pNew.document_number,
                                pNew.order_number,
                                pNew.document_date,
                                pNew.amount,
                                pNew.notes,
                                getRsnId(pNew.reason_code),
                                getSalescatId(pNew.alternate_prepaid_sales_category),
                                getGLAccntId(pNew.alternate_prepaid_account),
                                pNew.due_date,
                                getTermsId(pNew.terms),
                                getSalesrepId(pNew.sales_rep),
                                pNew.commission_due,
                                COALESCE(getCurrId(pNew.currency), baseCurrId()) ) INTO _result;
      IF (_result <= 0) THEN
        RAISE EXCEPTION 'Function createARDebitMemo failed with result = %', _result;
      END IF;
    ELSE
      RAISE EXCEPTION 'Function insertARMemo failed, invalid Document Type';
    END IF;
  END IF;

  RETURN TRUE;
END;
$_$;


ALTER FUNCTION public.insertarmemo(api.armemo) OWNER TO admin;

--
-- TOC entry 1815 (class 1255 OID 146566512)
-- Dependencies: 4536 8
-- Name: insertccard(text, boolean, text, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION insertccard(text, boolean, text, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, 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
  pCustomer ALIAS FOR $1;
  pActive ALIAS FOR $2;
  pType ALIAS FOR $3;
  pNumber ALIAS FOR $4;
  pName ALIAS FOR $5;
  pAddr1 ALIAS FOR $6;
  pAddr2 ALIAS FOR $7;
  pCity ALIAS FOR $8;
  pState ALIAS FOR $9;
  pPostal ALIAS FOR $10;
  pCountry ALIAS FOR $11;
  pMonth ALIAS FOR $12;
  pYear ALIAS FOR $13;
  pKey ALIAS FOR $14;
  _type CHAR;
  _number TEXT;
  _month INTEGER;
  _year INTEGER;
  _result INTEGER;

BEGIN
  --Initialize
  _number = CAST(encode(pNumber, 'escape') AS text);
  _month = CAST(encode(pMonth, 'escape') AS integer);
  _year = CAST(encode(pYear, 'escape') AS integer);

  -- Check Card holder info
  IF (pName IS NULL) THEN
      RAISE EXCEPTION 'The name of the card holder must be entered';
  END IF;
  IF (pAddr1 IS NULL OR pAddr1 = '') THEN
      RAISE EXCEPTION 'The first address line must be entered';
  END IF;
  IF (pCity IS NULL OR pCity = '') THEN
      RAISE EXCEPTION 'The city must be entered';
  END IF;
  IF (pState IS NULL OR pState = '') THEN
      RAISE EXCEPTION 'The state must be entered';
  END IF;
  IF (pPostal IS NULL OR pPostal = '') THEN
      RAISE EXCEPTION 'The zip code must be entered';
  END IF;
  IF (pCountry IS NULL OR pCountry = '') THEN
      RAISE EXCEPTION 'The country must be entered';
  END IF;
  IF (pMonth IS NULL OR pMonth = '') THEN
      RAISE EXCEPTION 'The Expiration Month must be entered';
  END IF;
  IF (_month < 1 OR _month > 12) THEN
      RAISE EXCEPTION 'Valid Expiration Months are 01 through 12';
  END IF;
  IF (LENGTH(_year::text) <> 4) THEN
      RAISE EXCEPTION 'Valid Expiration Years are CCYY in format';
  END IF;
  IF (_year < 1970 OR _year > 2100) THEN
      RAISE EXCEPTION 'Valid Expiration Years are 1970 through 2100';
  END IF;
  
  -- Check Number Length
  IF ((NOT _number ~  '[0-9]{13,16}') OR (LENGTH(_number) = 14) OR (LENGTH(_number) > 16)) THEN
    RAISE EXCEPTION 'The credit card number must be all numeric (no spaces or hyphens) and must be 13, 15 or 16 characters in length';
  END IF;
 
  -- Convert Type
  IF (pType = 'Visa') THEN
    _type  = 'V';
  ELSE
    IF (pType = 'Master Card') THEN
      _type  = 'M';
    ELSE
      IF (pType = 'American Express') THEN
        _type  = 'A';
      ELSE
        IF (pType = 'Discover') THEN
          _type  = 'D';
        ELSE
          RAISE EXCEPTION 'You must select Master Card, Visa, American 
                            Express or Discover as the credit card type.';
        END IF;
      END IF;
    END IF;
  END IF;
  
  -- Check Card Specific Data
  SELECT editccnumber(_number, _type) INTO _result;

  IF (_result = -1) THEN
    RAISE EXCEPTION 'You must select Master Card, Visa, American 
                      Express or Discover as the credit card type.';
  END IF;
  IF (_result = -2) THEN
    RAISE EXCEPTION 'The length of a Master Card credit card number
                      has to be 16 digits.';
  END IF;
  IF (_result = -3) THEN
    RAISE EXCEPTION 'The length of a Visa credit card number
                      has to be either 13 or 16 digits.';
  END IF;
  IF (_result = -4) THEN
    RAISE EXCEPTION 'The length of an American Express credit card
                      number has to be 15 digits.';
  END IF;
  IF (_result = -5) THEN
    RAISE EXCEPTION 'The length of a Discover credit card number 
                      has to be 16 digits.';
  END IF;
   IF (_result = -6) THEN
    RAISE EXCEPTION 'The first two digits for a valid Master Card
                      number must be between 51 and 55';
  END IF;
  IF (_result = -7) THEN
    RAISE EXCEPTION 'The first digit for a valid Visa number must
                      be 4';
  END IF;
   IF (_result = -8) THEN
    RAISE EXCEPTION 'The first two digits for a valid American
                      Express number must be 34 or 37.';
  END IF;
  IF (_result = -9) THEN
    RAISE EXCEPTION 'The first four digits for a valid Discover
                      Express number must be 6011.';
  END IF;
  IF ((_result = -10) AND NOT fetchmetricbool('CCTest')) THEN
    RAISE EXCEPTION 'The credit card number that you have provided
                      is not valid.';
  END IF;
  IF (_result < -10) THEN
    RAISE EXCEPTION 'Invalid Credit Card Information';
  END IF;

  -- Insert Record

  INSERT INTO ccard ( 
    ccard_seq, 
    ccard_cust_id,
    ccard_active, 
    ccard_name, 
    ccard_address1,
    ccard_address2,
    ccard_city, 
    ccard_state, 
    ccard_zip,
    ccard_country, 
    ccard_number,
    ccard_month_expired, 
    ccard_year_expired, 
    ccard_type)
    VALUES 
    ((SELECT COALESCE(MAX(ccard_seq), 0) + 10
      FROM ccard 
      WHERE (ccard_cust_id =getCustId(pCustomer))),
     getCustId(pCustomer),
     COALESCE(pActive),
     encrypt(setbytea(pName), setbytea(pKey), 'bf'),
     encrypt(setbytea(pAddr1), setbytea(pKey), 'bf'),
     encrypt(setbytea(pAddr2), setbytea(pKey), 'bf'),
     encrypt(setbytea(pCity), setbytea(pKey), 'bf'),
     encrypt(setbytea(pState), setbytea(pKey), 'bf'),
     encrypt(setbytea(pPostal), setbytea(pKey), 'bf'),
     encrypt(setbytea(pCountry), setbytea(pKey), 'bf'),
     encrypt(setbytea(pNumber), setbytea(pKey), 'bf'),
     encrypt(setbytea(pMonth), setbytea(pKey), 'bf'),
     encrypt(setbytea(pYear), setbytea(pKey), 'bf'),
     _type );

  RETURN true;
END;
$_$;


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

--
-- TOC entry 9019 (class 0 OID 0)
-- Dependencies: 1815
-- Name: FUNCTION insertccard(text, boolean, text, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, text); Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON FUNCTION insertccard(text, boolean, text, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, text) IS 'This function is generally used to support the _custcreditcard API view';


--
-- TOC entry 218 (class 1259 OID 146566514)
-- Dependencies: 6012 6013 6014 6015 8
-- Name: cmhead; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE cmhead (
    cmhead_id integer DEFAULT nextval(('cmhead_cmhead_id_seq'::text)::regclass) NOT NULL,
    cmhead_number text NOT NULL,
    cmhead_posted boolean,
    cmhead_invcnumber text,
    cmhead_custponumber text,
    cmhead_cust_id integer,
    cmhead_docdate date,
    cmhead_shipto_id integer,
    cmhead_shipto_name text,
    cmhead_shipto_address1 text,
    cmhead_shipto_address2 text,
    cmhead_shipto_address3 text,
    cmhead_shipto_city text,
    cmhead_shipto_state text,
    cmhead_shipto_zipcode text,
    cmhead_salesrep_id integer,
    cmhead_freight numeric(16,4),
    cmhead_misc numeric(16,4),
    cmhead_comments text,
    cmhead_printed boolean,
    cmhead_billtoname text,
    cmhead_billtoaddress1 text,
    cmhead_billtoaddress2 text,
    cmhead_billtoaddress3 text,
    cmhead_billtocity text,
    cmhead_billtostate text,
    cmhead_billtozip text,
    cmhead_hold boolean,
    cmhead_commission numeric(8,4),
    cmhead_misc_accnt_id integer,
    cmhead_misc_descrip text,
    cmhead_rsncode_id integer,
    cmhead_curr_id integer DEFAULT basecurrid(),
    cmhead_freighttaxtype_id integer,
    cmhead_gldistdate date,
    cmhead_billtocountry text,
    cmhead_shipto_country text,
    cmhead_rahead_id integer,
    cmhead_taxzone_id integer,
    cmhead_prj_id integer,
    cmhead_void boolean DEFAULT false,
    cmhead_saletype_id integer,
    cmhead_shipzone_id integer,
    CONSTRAINT cmhead_cmhead_number_check CHECK ((cmhead_number <> ''::text))
);


ALTER TABLE public.cmhead OWNER TO admin;

--
-- TOC entry 9020 (class 0 OID 0)
-- Dependencies: 218
-- Name: TABLE cmhead; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE cmhead IS 'S/O Credit Memo header information';


--
-- TOC entry 9021 (class 0 OID 0)
-- Dependencies: 218
-- Name: COLUMN cmhead.cmhead_freighttaxtype_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN cmhead.cmhead_freighttaxtype_id IS 'Deprecated column - DO NOT USE';


--
-- TOC entry 9022 (class 0 OID 0)
-- Dependencies: 218
-- Name: COLUMN cmhead.cmhead_saletype_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN cmhead.cmhead_saletype_id IS 'Associated sale type for credit memo.';


--
-- TOC entry 9023 (class 0 OID 0)
-- Dependencies: 218
-- Name: COLUMN cmhead.cmhead_shipzone_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN cmhead.cmhead_shipzone_id IS 'Associated shipping zone for credit memo.';


--
-- TOC entry 219 (class 1259 OID 146566524)
-- Dependencies: 6016 6017 8
-- Name: shiptoinfo; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE shiptoinfo (
    shipto_id integer DEFAULT nextval(('shipto_shipto_id_seq'::text)::regclass) NOT NULL,
    shipto_cust_id integer NOT NULL,
    shipto_name text,
    shipto_salesrep_id integer,
    shipto_comments text,
    shipto_shipcomments text,
    shipto_shipzone_id integer,
    shipto_shipvia text,
    shipto_commission numeric(10,4) NOT NULL,
    shipto_shipform_id integer,
    shipto_shipchrg_id integer,
    shipto_active boolean NOT NULL,
    shipto_default boolean,
    shipto_num text,
    shipto_ediprofile_id integer,
    shipto_cntct_id integer,
    shipto_addr_id integer,
    shipto_taxzone_id integer,
    shipto_preferred_warehous_id integer DEFAULT (-1) NOT NULL
);


ALTER TABLE public.shiptoinfo OWNER TO admin;

--
-- TOC entry 9025 (class 0 OID 0)
-- Dependencies: 219
-- Name: TABLE shiptoinfo; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE shiptoinfo IS 'Ship-To information';


--
-- TOC entry 9026 (class 0 OID 0)
-- Dependencies: 219
-- Name: COLUMN shiptoinfo.shipto_ediprofile_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN shiptoinfo.shipto_ediprofile_id IS 'Deprecated column - DO NOT USE';


--
-- TOC entry 220 (class 1259 OID 146566532)
-- Dependencies: 6019 8
-- Name: taxzone; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE taxzone (
    taxzone_id integer NOT NULL,
    taxzone_code text NOT NULL,
    taxzone_descrip text,
    CONSTRAINT taxzone_taxzone_code_check CHECK ((taxzone_code <> ''::text))
);


ALTER TABLE public.taxzone OWNER TO admin;

--
-- TOC entry 9028 (class 0 OID 0)
-- Dependencies: 220
-- Name: TABLE taxzone; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE taxzone IS 'Tax zone information';


--
-- TOC entry 9029 (class 0 OID 0)
-- Dependencies: 220
-- Name: COLUMN taxzone.taxzone_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN taxzone.taxzone_id IS 'Primary key';


--
-- TOC entry 9030 (class 0 OID 0)
-- Dependencies: 220
-- Name: COLUMN taxzone.taxzone_code; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN taxzone.taxzone_code IS 'Code';


--
-- TOC entry 9031 (class 0 OID 0)
-- Dependencies: 220
-- Name: COLUMN taxzone.taxzone_descrip; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN taxzone.taxzone_descrip IS 'Description';



--
-- TOC entry 221 (class 1259 OID 146566539)
-- Dependencies: 8473 6
-- Name: creditmemo; Type: VIEW; Schema: api; Owner: admin
--

CREATE VIEW creditmemo AS
    SELECT cmhead.cmhead_number AS memo_number, CASE WHEN (cmhead.cmhead_invcnumber = '-1'::text) THEN ''::text ELSE cmhead.cmhead_invcnumber END AS apply_to, cmhead.cmhead_docdate AS memo_date, CASE WHEN cmhead.cmhead_posted THEN 'Posted'::text ELSE 'Unposted'::text END AS status, salesrep.salesrep_number AS sales_rep, cmhead.cmhead_commission AS commission, COALESCE(taxzone.taxzone_code, 'None'::text) AS tax_zone, COALESCE(rsncode.rsncode_code, 'None'::text) AS reason_code, cmhead.cmhead_hold AS on_hold, custinfo.cust_number AS customer_number, cmhead.cmhead_billtoname AS billto_name, cmhead.cmhead_billtoaddress1 AS billto_address1, cmhead.cmhead_billtoaddress2 AS billto_address2, cmhead.cmhead_billtoaddress3 AS billto_address3, cmhead.cmhead_billtocity AS billto_city, cmhead.cmhead_billtostate AS billto_state, cmhead.cmhead_billtozip AS billto_postal_code, cmhead.cmhead_billtocountry AS billto_country, shiptoinfo.shipto_num AS shipto_number, cmhead.cmhead_shipto_name AS shipto_name, cmhead.cmhead_shipto_address1 AS shipto_address1, cmhead.cmhead_shipto_address2 AS shipto_address2, cmhead.cmhead_shipto_address3 AS shipto_address3, cmhead.cmhead_shipto_city AS shipto_city, cmhead.cmhead_shipto_state AS shipto_state, cmhead.cmhead_shipto_zipcode AS shipto_postal_code, cmhead.cmhead_shipto_country AS shipto_country, cmhead.cmhead_custponumber AS customer_po_number, cmhead.cmhead_comments AS notes, curr.curr_abbr AS currency, cmhead.cmhead_misc_descrip AS misc_charge_description, cmhead.cmhead_misc AS misc_charge_amount, CASE WHEN (cmhead.cmhead_misc_accnt_id = (-1)) THEN ''::text ELSE public.formatglaccount(cmhead.cmhead_misc_accnt_id) END AS misc_charge_credit_account, cmhead.cmhead_freight AS freight FROM ((((((public.cmhead LEFT JOIN public.custinfo ON ((custinfo.cust_id = cmhead.cmhead_cust_id))) LEFT JOIN public.shiptoinfo ON ((shiptoinfo.shipto_id = cmhead.cmhead_shipto_id))) LEFT JOIN public.curr_symbol curr ON ((curr.curr_id = cmhead.cmhead_curr_id))) LEFT JOIN public.salesrep ON ((salesrep.salesrep_id = cmhead.cmhead_salesrep_id))) LEFT JOIN public.taxzone ON ((taxzone.taxzone_id = cmhead.cmhead_taxzone_id))) LEFT JOIN public.rsncode ON ((rsncode.rsncode_id = cmhead.cmhead_rsncode_id)));


ALTER TABLE api.creditmemo OWNER TO admin;

--
-- TOC entry 9033 (class 0 OID 0)
-- Dependencies: 221
-- Name: VIEW creditmemo; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW creditmemo IS 'Credit Memo Header';



--
-- TOC entry 1816 (class 1255 OID 146566544)
-- Dependencies: 2842 4536 8
-- Name: insertcreditmemo(api.creditmemo); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION insertcreditmemo(api.creditmemo) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
	pNew ALIAS FOR $1;
BEGIN
	-- NOTE: (SELECT getCustId(...)) seems redundant, but it actually produces
	-- a HUGE performance increase because it makes the Postgres query planner
	-- use an index scan rather than an sequential table scan on cust_id
  INSERT INTO cmhead (
		cmhead_number,
		cmhead_posted,
		cmhead_invcnumber,
                cmhead_custponumber,
		cmhead_cust_id,
		cmhead_docdate,
		cmhead_shipto_id,
		cmhead_shipto_name,
		cmhead_shipto_address1,
		cmhead_shipto_address2,
		cmhead_shipto_address3,
		cmhead_shipto_city,
		cmhead_shipto_state,
		cmhead_shipto_zipcode,
		cmhead_shipto_country,
		cmhead_salesrep_id,
		cmhead_freight,
		cmhead_misc,
		cmhead_comments,
		cmhead_printed,
		cmhead_billtoname,
		cmhead_billtoaddress1,
		cmhead_billtoaddress2,
		cmhead_billtoaddress3,
		cmhead_billtocity,
		cmhead_billtostate,
		cmhead_billtozip,
		cmhead_billtocountry,
		cmhead_hold,
		cmhead_commission,
		cmhead_misc_accnt_id,
		cmhead_misc_descrip,
		cmhead_rsncode_id,
		cmhead_curr_id,
		cmhead_taxzone_id,
                cmhead_gldistdate,
                cmhead_rahead_id
		)
	 SELECT
		(CASE -- use a case here so we don't unnecessarily fetch a new CM number
			WHEN pNew.memo_number IS NULL THEN fetchCMNumber()
			ELSE pNew.memo_number
		END),
		FALSE, -- posted
		pNew.apply_to,
		pNew.customer_po_number,
		cust_id,
		COALESCE(pNew.memo_date, CURRENT_DATE),
		COALESCE(shipto_id,-1),
		pNew.shipto_name,
		pNew.shipto_address1,
		pNew.shipto_address2,
		pNew.shipto_address3,
		pNew.shipto_city,
		pNew.shipto_state,
		pNew.shipto_postal_code,
		pNew.shipto_country,
		COALESCE(getSalesRepId(pNew.sales_rep),shipto_salesrep_id,cust_salesrep_id),
		COALESCE(pNew.freight, 0),
		COALESCE(pNew.misc_charge_amount, 0),
		pNew.notes,
		FALSE, -- printed
		COALESCE(pNew.billto_name, invchead_billto_name, cust_name),
		COALESCE(pNew.billto_address1, invchead_billto_address1, addr_line1),
		COALESCE(pNew.billto_address2, invchead_billto_address2, addr_line2),
		COALESCE(pNew.billto_address3, invchead_billto_address3, addr_line3),
		COALESCE(pNew.billto_city, invchead_billto_city, addr_city),
		COALESCE(pNew.billto_state, invchead_billto_state, addr_state),
		COALESCE(pNew.billto_postal_code, invchead_billto_zipcode, addr_postalcode),
		COALESCE(pNew.billto_country, invchead_billto_country, addr_country),
		COALESCE(pNew.on_hold, FALSE),
		COALESCE(pNew.commission, 0),
		COALESCE(getGlAccntId(pNew.misc_charge_credit_account),-1),
		pNew.misc_charge_description,
		(SELECT rsncode_id FROM rsncode WHERE rsncode_code = pNew.reason_code),
		COALESCE(getCurrId(pNew.currency),cust_curr_id,basecurrid()),
                CASE WHEN pNew.tax_zone = 'None' THEN NULL
                     ELSE COALESCE(getTaxZoneID(pNew.tax_zone),cust_taxzone_id)
                END,
                NULL,
                NULL
	FROM custinfo
		LEFT OUTER JOIN shiptoinfo ON (shipto_id=(SELECT CASE
			WHEN getShiptoId(pNew.customer_number,pNew.shipto_number) IS NOT NULL
				THEN getShiptoId(pNew.customer_number,pNew.shipto_number)
			ELSE (SELECT shipto_id FROM shiptoinfo WHERE shipto_cust_id=cust_id AND shipto_default)
		END))
                LEFT OUTER JOIN invchead ON (invchead_id=getInvcheadId(pNEW.apply_to))
                LEFT OUTER JOIN cntct ON (cntct_id=cust_cntct_id)
                LEFT OUTER JOIN addr ON (addr_id=cntct_addr_id)
	WHERE cust_id = (CASE
		WHEN pNew.customer_number IS NOT NULL THEN (SELECT getCustId(pNew.customer_number))
		ELSE (SELECT invchead_cust_id FROM invchead WHERE invchead_invcnumber = pNew.apply_to)
	END);
	RETURN TRUE;
END;
$_$;


ALTER FUNCTION public.insertcreditmemo(api.creditmemo) OWNER TO admin;

--
-- TOC entry 222 (class 1259 OID 146566545)
-- Dependencies: 6020 6021 8
-- Name: cmitem; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE cmitem (
    cmitem_id integer DEFAULT nextval(('cmitem_cmitem_id_seq'::text)::regclass) NOT NULL,
    cmitem_cmhead_id integer NOT NULL,
    cmitem_linenumber integer NOT NULL,
    cmitem_itemsite_id integer NOT NULL,
    cmitem_qtycredit numeric(18,6) NOT NULL,
    cmitem_qtyreturned numeric(18,6) NOT NULL,
    cmitem_unitprice numeric(16,4) NOT NULL,
    cmitem_comments text,
    cmitem_rsncode_id integer,
    cmitem_taxtype_id integer,
    cmitem_qty_uom_id integer NOT NULL,
    cmitem_qty_invuomratio numeric(20,10) NOT NULL,
    cmitem_price_uom_id integer NOT NULL,
    cmitem_price_invuomratio numeric(20,10) NOT NULL,
    cmitem_raitem_id integer,
    cmitem_updateinv boolean DEFAULT true NOT NULL
);


ALTER TABLE public.cmitem OWNER TO admin;

--
-- TOC entry 9035 (class 0 OID 0)
-- Dependencies: 222
-- Name: TABLE cmitem; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE cmitem IS 'S/O Credit Memo Line Item information';



--
-- TOC entry 223 (class 1259 OID 146566553)
-- Dependencies: 8474 6
-- Name: creditmemoline; Type: VIEW; Schema: api; Owner: admin
--

CREATE VIEW creditmemoline AS
    SELECT cmhead.cmhead_number AS memo_number, cmitem.cmitem_linenumber AS line_number, item.item_number, whsinfo.warehous_code AS recv_site, rsncode.rsncode_code AS reason_code, cmitem.cmitem_qtyreturned AS qty_returned, cmitem.cmitem_qtycredit AS qty_to_credit, COALESCE(qty_uom.uom_name, 'None'::text) AS qty_uom, cmitem.cmitem_unitprice AS net_unit_price, COALESCE(price_uom.uom_name, 'None'::text) AS price_uom, COALESCE(taxtype.taxtype_name, 'None'::text) AS tax_type, cmitem.cmitem_comments AS notes FROM ((((((((public.cmitem LEFT JOIN public.cmhead ON ((cmitem.cmitem_cmhead_id = cmhead.cmhead_id))) LEFT JOIN public.itemsite ON ((itemsite.itemsite_id = cmitem.cmitem_itemsite_id))) LEFT JOIN public.item ON ((item.item_id = itemsite.itemsite_item_id))) LEFT JOIN public.whsinfo ON ((whsinfo.warehous_id = itemsite.itemsite_warehous_id))) LEFT JOIN public.rsncode ON ((rsncode.rsncode_id = cmitem.cmitem_rsncode_id))) LEFT JOIN public.taxtype ON ((taxtype.taxtype_id = cmitem.cmitem_taxtype_id))) LEFT JOIN public.uom qty_uom ON ((qty_uom.uom_id = cmitem.cmitem_qty_uom_id))) LEFT JOIN public.uom price_uom ON ((price_uom.uom_id = cmitem.cmitem_price_uom_id)));


ALTER TABLE api.creditmemoline OWNER TO admin;

--
-- TOC entry 9037 (class 0 OID 0)
-- Dependencies: 223
-- Name: VIEW creditmemoline; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW creditmemoline IS 'Credit Memo Line';



--
-- TOC entry 1817 (class 1255 OID 146566558)
-- Dependencies: 2849 4536 8
-- Name: insertcreditmemoline(api.creditmemoline); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  SELECT cmhead_id INTO _check
  FROM cmhead
  WHERE (cmhead_id=getCmheadId(pNew.memo_number, FALSE));
  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Credit Memo # % not found', pNew.memo_number;
  END IF;

  INSERT INTO cmitem ( cmitem_cmhead_id,
                       cmitem_linenumber,
                       cmitem_itemsite_id,
                       cmitem_qtycredit,
                       cmitem_qtyreturned,
                       cmitem_unitprice,
                       cmitem_comments,
                       cmitem_rsncode_id,
                       cmitem_taxtype_id,
                       cmitem_qty_uom_id,
                       cmitem_qty_invuomratio,
                       cmitem_price_uom_id,
                       cmitem_price_invuomratio	)
  SELECT cmhead_id,
         COALESCE(pNew.line_number,
                  (SELECT (COALESCE(MAX(cmitem_linenumber), 0) + 1)
                   FROM cmitem WHERE (cmitem_cmhead_id=cmhead_id))),
         COALESCE(itemsite_id, -1),
         COALESCE(pNew.qty_to_credit, 0),
         COALESCE(pNew.qty_returned, 0),
         COALESCE(pNew.net_unit_price, 0),
         pNew.notes,
         getRsnId(pNew.reason_code),
         taxtype_id,
         COALESCE(getUomId(pNew.qty_uom), item_inv_uom_id),
         CASE
           WHEN item_id IS NOT NULL THEN itemuomtouomratio(item_id, COALESCE(getUomId(pNew.qty_uom),item_inv_uom_id),item_inv_uom_id)
           ELSE 1
         END,
         COALESCE(getUomId(pNew.price_uom),item_price_uom_id),
         CASE
           WHEN item_id IS NOT NULL THEN itemuomtouomratio(item_id, COALESCE(getUomId(pNew.price_uom),item_price_uom_id),item_price_uom_id)
           ELSE 1
        END
  FROM cmhead LEFT OUTER JOIN item ON (item_id=getItemId(pNew.item_number))
              LEFT OUTER JOIN itemsite ON (itemsite_item_id=item_id AND itemsite_warehous_id=getWarehousId(pNew.recv_site, 'ALL'))
              LEFT OUTER JOIN taxtype ON (taxtype_id=CASE WHEN pNew.tax_type IS NULL THEN getItemTaxType(item_id,cmhead_taxzone_id)
                                                          WHEN pNew.tax_type = 'None' THEN NULL
                                                          ELSE getTaxTypeId(pNew.tax_type)
                                                     END)
  WHERE (cmhead_id=getCmheadId(pNew.memo_number, FALSE));

  RETURN TRUE;
END;
$_$;


ALTER FUNCTION public.insertcreditmemoline(api.creditmemoline) OWNER TO admin;

--
-- TOC entry 1803 (class 1255 OID 146566559)
-- Dependencies: 4536 8
-- Name: insertflgroup(integer, integer, integer, integer, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION insertflgroup(integer, integer, integer, integer, boolean) 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
  pFlheadid  ALIAS FOR $1;
  pPeriodid  ALIAS FOR $2;
  pFlgrpid   ALIAS FOR $3;
  pLevel     ALIAS FOR $4;
  pSummarize ALIAS FOR $5;

BEGIN
  RETURN insertFlGroup(pFlheadid, pPeriodid, pFlgrpid, pLevel, pSummarize, NULL);
END;
$_$;


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

--
-- TOC entry 1818 (class 1255 OID 146566560)
-- Dependencies: 4536 8
-- Name: insertflgroup(integer, integer, integer, integer, boolean, character); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION insertflgroup(integer, integer, integer, integer, boolean, character) 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
  pFlheadid  ALIAS FOR $1;
  pPeriodid  ALIAS FOR $2;
  pFlgrpid   ALIAS FOR $3;
  pLevel     ALIAS FOR $4;
  pSummarize ALIAS FOR $5;
  pInterval  ALIAS FOR $6;

BEGIN
  RETURN insertFlGroup(pFlheadid, pPeriodid, pFlgrpid, pLevel, pSummarize, pInterval, NULL);
END;
$_$;


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

--
-- TOC entry 1819 (class 1255 OID 146566561)
-- Dependencies: 4536 8
-- Name: insertflgroup(integer, integer, integer, integer, boolean, character, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION insertflgroup(integer, integer, integer, integer, boolean, 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
  pFlheadid  ALIAS FOR $1;
  pPeriodid  ALIAS FOR $2;
  pFlgrpid   ALIAS FOR $3;
  pLevel     ALIAS FOR $4;
  pSummarize ALIAS FOR $5;
  pInterval  ALIAS FOR $6;
  pPrjid     ALIAS FOR $7;

  _subtotal BOOLEAN;
  _r RECORD;
  _g RECORD;
  _all BOOLEAN;

BEGIN

  _all = COALESCE(pPrjid,-1) = -1;
  
-- Check to see if this group wants a subtotal
  _subtotal := FALSE;
  IF (pFlgrpid != -1) THEN
    SELECT COALESCE(flgrp_subtotal, FALSE) INTO _subtotal
      FROM flgrp
     WHERE ((flgrp_flhead_id=pFlheadid)
       AND  (flgrp_id=pFlgrpid));
  END IF;

  FOR _r IN SELECT 'G' AS type, flgrp_id AS type_id,
                   flgrp_order AS orderby,
                   flgrp_summarize AS summarize,
                   flgrp_subtract AS subtract,
                   CASE WHEN(flgrp_summarize AND (NOT flgrp_showstart)) THEN NULL
                        ELSE 0.00
                   END AS beginning,
                   CASE WHEN(flgrp_summarize AND (NOT flgrp_showend)) THEN NULL
                        ELSE 0.00
                   END AS ending,
                   CASE WHEN(flgrp_summarize AND (NOT flgrp_showdelta)) THEN NULL
                        ELSE 0.00
                   END AS debits,
                   CASE WHEN(flgrp_summarize AND (NOT flgrp_showdelta)) THEN NULL
                        ELSE 0.00
                   END AS credits,
                   CASE WHEN(flgrp_summarize AND (NOT flgrp_showbudget)) THEN NULL
                        ELSE 0.00
                   END AS budget,
                   CASE WHEN(flgrp_summarize AND (NOT flgrp_showdiff)) THEN NULL
                        ELSE 0.00
                   END AS diff,
                   CASE WHEN(flgrp_summarize AND (NOT flgrp_showcustom)) THEN NULL
                        ELSE 0.00
                   END AS custom,
                   CASE WHEN(flgrp_showstartprcnt) THEN 0.00
                        ELSE NULL
                   END AS beginningprcnt,
                   CASE WHEN(flgrp_showendprcnt) THEN 0.00
                        ELSE NULL
                   END AS endingprcnt,
                   CASE WHEN(flgrp_showdeltaprcnt) THEN 0.00
                        ELSE NULL
                   END AS debitsprcnt,
                   CASE WHEN(flgrp_showdeltaprcnt) THEN 0.00
                        ELSE NULL
                   END AS creditsprcnt,
                   CASE WHEN(flgrp_showbudgetprcnt) THEN 0.00
                        ELSE NULL
                   END AS budgetprcnt,
                   CASE WHEN(flgrp_showdiffprcnt) THEN 0.00
                        ELSE NULL
                   END AS diffprcnt,
                   CASE WHEN(flgrp_showcustomprcnt) THEN 0.00
                        ELSE NULL
                   END AS customprcnt,
                   -1 AS accnt_id,
                   '' AS accnt_number
              FROM flgrp
             WHERE ((flgrp_flgrp_id=pFlgrpid)
               AND  (flgrp_flhead_id=pFlheadid))
             UNION ALL
            SELECT 'I' AS type, flitem_id AS type_id,
                   flitem_order AS orderby,
                   FALSE AS summarize,
                   flitem_subtract AS subtract,
                   CASE WHEN (flitem_showstart AND (first_trialbal_id IS NULL)) THEN 0.00
                        WHEN (flitem_showstart) THEN normalizeTrialBal(first_trialbal_id, 'B')
                        ELSE NULL
                   END AS beginning,
                   CASE WHEN (flitem_showend AND (last_trialbal_id IS NULL)) THEN 0.00
                        WHEN (flitem_showend) THEN normalizeTrialBal(last_trialbal_id, 'E')
                        ELSE NULL
                   END AS ending,
                   CASE WHEN (flitem_showdelta) THEN sum_trialbal_debits
                        ELSE NULL
                   END AS debits,
                   CASE WHEN (flitem_showdelta) THEN sum_trialbal_credits
                        ELSE NULL
                   END AS credits,
                   CASE WHEN ((flitem_showbudget) AND (accnt_type IN ('R','E')) AND flhead_type IN ('I','C','A')) THEN COALESCE(sum_budget_amount,0)
                        WHEN ((flitem_showbudget) AND (accnt_type IN ('R','E')) AND flhead_type = 'B' ) THEN
                                (SELECT COALESCE(SUM(b.budget_amount),0)
                                FROM budget b,
                                        (SELECT ytd.period_id AS ytd_period_id
                                        FROM period cp, period ytd
                                        WHERE ((cp.period_id = last_flitem_period_id)
                                        AND (ytd.period_start <= cp.period_start)
                                AND (ytd.period_yearperiod_id = cp.period_yearperiod_id))) AS periods
                                WHERE ((b.budget_accnt_id=accnt_id)
                                AND (b.budget_period_id=ytd_period_id)))
                        WHEN ((flitem_showbudget) AND (accnt_type IN ('A','L','Q')) AND flhead_type = 'C') THEN calccashbudget(accnt_id,last_flitem_period_id,pInterval)
                        ELSE COALESCE(last_budget_amount,0)
                   END AS budget,
                   CASE WHEN (flitem_showdiff AND (first_trialbal_id IS NULL)) THEN 0.00
                        WHEN (flitem_showdiff) THEN COALESCE(normalizeTrialBal(last_trialbal_id, 'E') - normalizeTrialBal(first_trialbal_id, 'B'), 0.00)
                        ELSE NULL
                   END AS diff,
                   CASE WHEN (NOT flitem_showcustom) THEN NULL
                        WHEN (flitem_custom_source='S' AND (first_trialbal_id IS NOT NULL)) THEN normalizeTrialBal(first_trialbal_id, 'B')
                        WHEN (flitem_custom_source='E' AND (first_trialbal_id IS NOT NULL)) THEN normalizeTrialBal(last_trialbal_id, 'E')
                        WHEN (flitem_custom_source='D') THEN sum_trialbal_debits
                        WHEN (flitem_custom_source='C') THEN sum_trialbal_credits
                        WHEN (flitem_custom_source='B') THEN (
                                CASE
                                  WHEN (accnt_type IN ('R','E')) THEN sum_budget_amount
                                  ELSE last_budget_amount
                                END)
                        WHEN (flitem_custom_source='F' AND  (first_trialbal_id IS NOT NULL)) THEN COALESCE(normalizeTrialBal(last_trialbal_id, 'E') - normalizeTrialBal(first_trialbal_id, 'B'), 0.00)
                        ELSE 0.00
                   END AS custom,
                   CASE WHEN(flitem_showstartprcnt) THEN 0.00
                        ELSE NULL
                   END AS beginningprcnt,
                   CASE WHEN(flitem_showendprcnt) THEN 0.00
                        ELSE NULL
                   END AS endingprcnt,
                   CASE WHEN(flitem_showdeltaprcnt) THEN 0.00
                        ELSE NULL
                   END AS debitsprcnt,
                   CASE WHEN(flitem_showdeltaprcnt) THEN 0.00
                        ELSE NULL
                   END AS creditsprcnt,
                   CASE WHEN(flitem_showbudgetprcnt) THEN 0.00
                        ELSE NULL
                   END AS budgetprcnt,
                   CASE WHEN(flitem_showdiffprcnt) THEN 0.00
                        ELSE NULL
                   END AS diffprcnt,
                   CASE WHEN(flitem_showcustomprcnt) THEN 0.00
                        ELSE NULL
                   END AS customprcnt,
                   accnt_id,
                   public.formatglaccount(accnt_id) AS accnt_number
              FROM
                (SELECT 
                  flhead_type,flitem_id,flitem_order,flitem_subtract,flitem_showstart,flitem_showend,
                  flitem_showdelta,flitem_showbudget,flitem_showdiff,flitem_showcustom,
                  flitem_custom_source,flitem_showstartprcnt,flitem_showendprcnt,flitem_showdeltaprcnt,
                  flitem_showbudgetprcnt,flitem_showdiffprcnt,flitem_showcustomprcnt,
                  accnt_id,accnt_type,
                  FIRST(trialbal_id) AS first_trialbal_id, LAST(trialbal_id) AS last_trialbal_id,
                  SUM(trialbal_debits) AS sum_trialbal_debits, SUM(trialbal_credits) AS sum_trialbal_credits, 
                  LAST(flitem_period_id) AS last_flitem_period_id,
                  SUM(budget_amount) AS sum_budget_amount, LAST(budget_amount) AS last_budget_amount
                  FROM
                (SELECT period_id AS flitem_period_id, period_start,flhead_type,flitem_id,flitem_order,flitem_subtract,flitem_showstart,flitem_showend,
                        flitem_showdelta,flitem_showbudget,flitem_showdiff,flitem_showcustom,
                        flitem_custom_source,flitem_showstartprcnt,flitem_showendprcnt,flitem_showdeltaprcnt,
                        flitem_showbudgetprcnt,flitem_showdiffprcnt,flitem_showcustomprcnt,
                        accnt_id,accnt_type,COALESCE(trialbal_id,getlasttrialbalid(accnt_id,period_id)) as trialbal_id,COALESCE(trialbal_debits,0) as trialbal_debits,
                        COALESCE(trialbal_credits,0) AS trialbal_credits,COALESCE(budget_amount,0) AS budget_amount
                   FROM (SELECT period_id, period_start, flhead_type, flitem_id,flitem_order,flitem_subtract,flitem_showstart,flitem_showend,
                                flitem_showdelta,flitem_showbudget,flitem_showdiff,flitem_showcustom,flitem_custom_source,flitem_showstartprcnt,
                                flitem_showendprcnt,flitem_showdeltaprcnt,flitem_showbudgetprcnt,flitem_showdiffprcnt,flitem_showcustomprcnt,
                                accnt_id, accnt_type
                        FROM  period,flaccnt
                        WHERE ((flitem_flhead_id=pFlheadid)
                        AND (flitem_flgrp_id=pFlgrpid)
                        AND (_all OR prj_id=pPrjId)
                        AND (period_id IN  (SELECT * FROM getperiodid(pPeriodId,pInterval))))
                        ORDER BY flitem_id
                        ) AS flitem
                   LEFT OUTER JOIN trialbal
                     ON ((trialbal_accnt_id=accnt_id)
                     AND (trialbal_period_id=period_id))
                   LEFT OUTER JOIN budget
                     ON ((budget_accnt_id=accnt_id)
                     AND (budget_period_id=period_id))
             ORDER BY accnt_id, period_start) AS data
             GROUP BY flhead_type,flitem_id,flitem_order,flitem_subtract,flitem_showstart,flitem_showend,
                flitem_showdelta,flitem_showbudget,flitem_showdiff,flitem_showcustom,
                flitem_custom_source,flitem_showstartprcnt,flitem_showendprcnt,flitem_showdeltaprcnt,
                flitem_showbudgetprcnt,flitem_showdiffprcnt,flitem_showcustomprcnt,accnt_id,accnt_type) AS agg
             UNION ALL
            SELECT 'S' AS type, flspec_id AS type_id,
                   flspec_order AS orderby,
                   FALSE AS summarize,
                   flspec_subtract AS subtract,
                   CASE WHEN (flspec_showstart) THEN findSpecialFinancial('S', flspec_type, pPeriodid)
                        ELSE NULL
                   END AS beginning,
                   CASE WHEN (flspec_showend) THEN findSpecialFinancial('E', flspec_type, pPeriodid)
                        ELSE NULL
                   END AS ending,
                   CASE WHEN (flspec_showdelta) THEN findSpecialFinancial('D', flspec_type, pPeriodid)
                        ELSE NULL
                   END AS debits,
                   CASE WHEN (flspec_showdelta) THEN findSpecialFinancial('C', flspec_type, pPeriodid)
                        ELSE NULL
                   END AS credits,
                   CASE WHEN (flspec_showbudget) THEN findSpecialFinancial('B', flspec_type, pPeriodid)
                        ELSE NULL
                   END AS budget,
                   CASE WHEN (flspec_showdiff) THEN findSpecialFinancial('E', flspec_type, pPeriodid) - findSpecialFinancial('S', flspec_type, pPeriodid)
                        ELSE NULL
                   END AS diff,
                   CASE WHEN (NOT flspec_showcustom) THEN NULL
                        WHEN (flspec_custom_source='F') THEN findSpecialFinancial('E', flspec_type, pPeriodid) - findSpecialFinancial('S', flspec_type, pPeriodid)
                        WHEN (flspec_custom_source IN ('S', 'E', 'D', 'C', 'B')) THEN findSpecialFinancial(flspec_custom_source, flspec_type, pPeriodid)
                        ELSE 0.00
                   END AS custom,
                   CASE WHEN(flspec_showstartprcnt) THEN 0.00
                        ELSE NULL
                   END AS beginningprcnt,
                   CASE WHEN(flspec_showendprcnt) THEN 0.00
                        ELSE NULL
                   END AS endingprcnt,
                   CASE WHEN(flspec_showdeltaprcnt) THEN 0.00
                        ELSE NULL
                   END AS debitsprcnt,
                   CASE WHEN(flspec_showdeltaprcnt) THEN 0.00
                        ELSE NULL
                   END AS creditsprcnt,
                   CASE WHEN(flspec_showbudgetprcnt) THEN 0.00
                        ELSE NULL
                   END AS budgetprcnt,
                   CASE WHEN(flspec_showdiffprcnt) THEN 0.00
                        ELSE NULL
                   END AS diffprcnt,
                   CASE WHEN(flspec_showcustomprcnt) THEN 0.00
                        ELSE NULL
                   END AS customprcnt,
                   -1 AS accnt_id,
                   '' AS accnt_number
              FROM flspec
             WHERE ((flspec_flgrp_id=pFlgrpid)
               AND  (flspec_flhead_id=pFlheadid))
          ORDER BY orderby, accnt_number LOOP

    IF (_r.type = 'G') THEN

-- Create a record for the items sub items to be attached to and be able to update the total
      INSERT INTO flrpt
             (flrpt_flhead_id, flrpt_period_id, flrpt_username,
              flrpt_order,
              flrpt_level, flrpt_type, flrpt_type_id,
              flrpt_beginning, flrpt_ending,
              flrpt_debits, flrpt_credits, flrpt_budget, flrpt_diff, flrpt_custom,
              flrpt_beginningprcnt, flrpt_endingprcnt,
              flrpt_debitsprcnt, flrpt_creditsprcnt, flrpt_budgetprcnt, flrpt_diffprcnt, flrpt_customprcnt,
              flrpt_parent_id, flrpt_interval)
      VALUES (pFlheadid, pPeriodid, getEffectiveXtUser(),
              (COALESCE(( SELECT MAX(flrpt_order)
                            FROM flrpt
                           WHERE ((flrpt_flhead_id=pFlheadid)
                             AND  (flrpt_period_id=pPeriodid)
                             AND (flrpt_interval=pInterval)
                             AND  (flrpt_username=getEffectiveXtUser()))
                        ), 1) + 1),
              pLevel, _r.type, _r.type_id,
              _r.beginning, _r.ending,
              _r.debits, _r.credits, _r.budget, _r.diff, _r.custom,
              _r.beginningprcnt, _r.endingprcnt,
              _r.debitsprcnt, _r.creditsprcnt, _r.budgetprcnt, _r.diffprcnt, _r.customprcnt,
              pFlgrpid, pInterval);

      PERFORM insertFlGroup(pFlheadid, pPeriodid, _r.type_id, (pLevel + 1), (pSummarize OR _r.summarize), pInterval, pPrjid);

-- Update the parent item
      SELECT COALESCE(flrpt_beginning, 0.00) AS beginning,
             COALESCE(flrpt_ending, 0.00) AS ending,
             COALESCE(flrpt_debits, 0.00) AS debits,
             COALESCE(flrpt_credits, 0.00) AS credits,
             COALESCE(flrpt_budget, 0.00) AS budget,
             COALESCE(flrpt_diff, 0.00) AS diff,
             COALESCE(flrpt_custom, 0.00) AS custom INTO _g
        FROM flrpt
       WHERE ((flrpt_flhead_id=pFlheadid)
         AND  (flrpt_period_id=pPeriodid)
          AND (flrpt_interval=pInterval)
         AND  (flrpt_username=getEffectiveXtUser())
         AND  (flrpt_type=_r.type)
         AND  (flrpt_type_id=_r.type_id));
      IF (_r.subtract) THEN
        UPDATE flrpt
           SET flrpt_beginning = flrpt_beginning - _g.beginning,
               flrpt_ending    = flrpt_ending    - _g.ending,
               flrpt_debits    = flrpt_debits    - _g.debits,
               flrpt_credits   = flrpt_credits   - _g.credits,
               flrpt_budget    = flrpt_budget    - _g.budget,
               flrpt_diff      = flrpt_diff      - _g.diff,
               flrpt_custom    = flrpt_custom    - _g.custom
         WHERE ((flrpt_flhead_id=pFlheadid)
           AND  (flrpt_period_id=pPeriodid)
           AND  (flrpt_interval=pInterval)
           AND  (flrpt_username=getEffectiveXtUser())
           AND  (flrpt_type='G')
           AND  (flrpt_type_id=pFlgrpid));
      ELSE
        UPDATE flrpt
           SET flrpt_beginning = flrpt_beginning + _g.beginning,
               flrpt_ending    = flrpt_ending    + _g.ending,
               flrpt_debits    = flrpt_debits    + _g.debits,
               flrpt_credits   = flrpt_credits   + _g.credits,
               flrpt_budget    = flrpt_budget    + _g.budget,
               flrpt_diff      = flrpt_diff      + _g.diff,
               flrpt_custom    = flrpt_custom    + _g.custom
         WHERE ((flrpt_flhead_id=pFlheadid)
           AND  (flrpt_period_id=pPeriodid)
           AND  (flrpt_interval=pInterval)
           AND  (flrpt_username=getEffectiveXtUser())
           AND  (flrpt_type='G')
           AND  (flrpt_type_id=pFlgrpid));
      END IF;

-- If we are summarizing then we need to remove the record we created now that we have updated the total
      IF (pSummarize) THEN
        DELETE FROM flrpt
         WHERE ((flrpt_flhead_id=pFlheadid)
          AND  (flrpt_period_id=pPeriodid)
          AND  (flrpt_interval=pInterval)
          AND  (flrpt_username=getEffectiveXtUser())
          AND  (flrpt_type=_r.type)
          AND  (flrpt_type_id=_r.type_id));
      END IF;

    ELSE
      IF (_r.type = 'I' OR _r.type = 'S' ) THEN

-- If we are not summarizing then create a new entry for this record
        IF (NOT pSummarize) THEN
          INSERT INTO flrpt
                 (flrpt_flhead_id, flrpt_period_id, flrpt_username,
                  flrpt_order,
                  flrpt_level, flrpt_type, flrpt_type_id,
                  flrpt_beginning, flrpt_ending,
                  flrpt_debits, flrpt_credits, flrpt_budget, flrpt_diff, flrpt_custom,
                  flrpt_beginningprcnt, flrpt_endingprcnt,
                  flrpt_debitsprcnt, flrpt_creditsprcnt, flrpt_budgetprcnt, flrpt_diffprcnt, flrpt_customprcnt,
                  flrpt_parent_id,flrpt_accnt_id,flrpt_interval)
          VALUES (pFlheadid, pPeriodid, getEffectiveXtUser(),
                  (COALESCE(( SELECT MAX(flrpt_order)
                               FROM flrpt
                              WHERE ((flrpt_flhead_id=pFlheadid)
                                AND  (flrpt_period_id=pPeriodid)
                                AND  (flrpt_interval=pInterval)
                                AND  (flrpt_username=getEffectiveXtUser()))
                            ), 1) + 1),
                  pLevel, _r.type, _r.type_id,
                  _r.beginning, _r.ending,
                  _r.debits, _r.credits, _r.budget, _r.diff, _r.custom,
                  _r.beginningprcnt, _r.endingprcnt,
                  _r.debitsprcnt, _r.creditsprcnt, _r.budgetprcnt, _r.diffprcnt, _r.customprcnt,
                  pFlgrpid,_r.accnt_id,pInterval);
        END IF;

-- Update the parent item
        IF (_r.subtract) THEN
          UPDATE flrpt
             SET flrpt_beginning = flrpt_beginning - COALESCE(_r.beginning, 0.00),
                 flrpt_ending    = flrpt_ending    - COALESCE(_r.ending, 0.00),
                 flrpt_debits    = flrpt_debits    - COALESCE(_r.debits, 0.00),
                 flrpt_credits   = flrpt_credits   - COALESCE(_r.credits, 0.00),
                 flrpt_budget    = flrpt_budget    - COALESCE(_r.budget, 0.00),
                 flrpt_diff      = flrpt_diff      - COALESCE(_r.diff, 0.00),
                 flrpt_custom    = flrpt_custom    - COALESCE(_r.custom, 0.00)
           WHERE ((flrpt_flhead_id=pFlheadid)
             AND  (flrpt_period_id=pPeriodid)
             AND  (flrpt_interval=pInterval)
             AND  (flrpt_username=getEffectiveXtUser())
             AND  (flrpt_type='G')
             AND  (flrpt_type_id=pFlgrpid));
        ELSE
          UPDATE flrpt
             SET flrpt_beginning = flrpt_beginning + COALESCE(_r.beginning, 0.00),
                 flrpt_ending    = flrpt_ending    + COALESCE(_r.ending, 0.00),
                 flrpt_debits    = flrpt_debits    + COALESCE(_r.debits, 0.00),
                 flrpt_credits   = flrpt_credits   + COALESCE(_r.credits, 0.00),
                 flrpt_budget    = flrpt_budget    + COALESCE(_r.budget, 0.00),
                 flrpt_diff      = flrpt_diff      + COALESCE(_r.diff, 0.00),
                 flrpt_custom    = flrpt_custom    + COALESCE(_r.custom, 0.00)
           WHERE ((flrpt_flhead_id=pFlheadid)
             AND  (flrpt_interval=pInterval)
             AND  (flrpt_period_id=pPeriodid)
             AND  (flrpt_username=getEffectiveXtUser())
             AND  (flrpt_type='G')
             AND  (flrpt_type_id=pFlgrpid));
        END IF;

      END IF;
    END IF;

  END LOOP;

  IF (NOT pSummarize) THEN
-- If this group wants a summarized line create it here.
    IF (_subtotal) THEN
      INSERT INTO flrpt
             (flrpt_flhead_id, flrpt_period_id, flrpt_username,
              flrpt_order,
              flrpt_level, flrpt_type, flrpt_type_id,
              flrpt_beginning, flrpt_ending,
              flrpt_debits, flrpt_credits, flrpt_budget, flrpt_diff, flrpt_custom,
              flrpt_beginningprcnt, flrpt_endingprcnt,
              flrpt_debitsprcnt, flrpt_creditsprcnt, flrpt_budgetprcnt, flrpt_diffprcnt, flrpt_customprcnt,
              flrpt_parent_id, flrpt_altname,flrpt_interval )
      SELECT pFlheadid, pPeriodid, getEffectiveXtUser(),
             (COALESCE(( SELECT MAX(flrpt_order)
                           FROM flrpt
                          WHERE ((flrpt_flhead_id=pFlheadid)
                            AND  (flrpt_period_id=pPeriodid)
                            AND  (flrpt_interval=pInterval)
                            AND  (flrpt_username=getEffectiveXtUser()))
                       ), 1) + 1),
             pLevel, 'T', -1,
             CASE WHEN (flgrp_showstart) THEN flrpt_beginning
                  ELSE NULL
             END,
             CASE WHEN (flgrp_showend) THEN flrpt_ending
                  ELSE NULL
             END,
             CASE WHEN (flgrp_showdelta) THEN flrpt_debits
                  ELSE NULL
             END,
             CASE WHEN (flgrp_showdelta) THEN flrpt_credits
                  ELSE NULL
             END,
             CASE WHEN (flgrp_showbudget) THEN flrpt_budget
                  ELSE NULL
             END,
             CASE WHEN (flgrp_showdiff) THEN flrpt_diff
                  ELSE NULL
             END,
             CASE WHEN (flgrp_showcustom) THEN flrpt_custom
                  ELSE NULL
             END,
             CASE WHEN (flgrp_showstartprcnt) THEN flrpt_beginningprcnt
                  ELSE NULL
             END,
             CASE WHEN (flgrp_showendprcnt) THEN flrpt_endingprcnt
                  ELSE NULL
             END,
             CASE WHEN (flgrp_showdeltaprcnt) THEN flrpt_debitsprcnt
                  ELSE NULL
             END,
             CASE WHEN (flgrp_showdeltaprcnt) THEN flrpt_creditsprcnt
                  ELSE NULL
             END,
             CASE WHEN (flgrp_showbudgetprcnt) THEN flrpt_budgetprcnt
                  ELSE NULL
             END,
             CASE WHEN (flgrp_showdiffprcnt) THEN flrpt_diffprcnt
                  ELSE NULL
             END,
             CASE WHEN (flgrp_showcustomprcnt) THEN flrpt_customprcnt
                  ELSE NULL
             END,
             pFlgrpid,
             CASE WHEN (flgrp_usealtsubtotal) THEN flgrp_altsubtotal
                  ELSE NULL
             END, pInterval
        FROM flrpt, flgrp
       WHERE ((flrpt_flhead_id=flgrp_flhead_id)
         AND  (flrpt_type_id=flgrp_id)
         AND  (flrpt_flhead_id=pFlheadid)
         AND  (flrpt_period_id=pPeriodid)
         AND  (flrpt_interval=pInterval)
         AND  (flrpt_username=getEffectiveXtUser())
         AND  (flrpt_type='G')
         AND  (flrpt_type_id=pFlgrpid));
    END IF;
  END IF;

  return TRUE;
END;
$_$;


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

--
-- TOC entry 1820 (class 1255 OID 146566563)
-- Dependencies: 4536 8
