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

CREATE FUNCTION applycashreceiptlinebalance(integer, integer, numeric, integer) RETURNS numeric
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pCashrcptId ALIAS FOR $1;
  pAropenid ALIAS FOR $2;
  pAmount ALIAS FOR $3;
  pCurrId ALIAS FOR $4;
  _balance NUMERIC;
  _amount NUMERIC;
  _applyAmount NUMERIC := 0;
  _discount NUMERIC := 0;
  _discprct NUMERIC;
  _docDate DATE;
  _r RECORD;
  _doctype CHAR(1);

BEGIN

--  All calculations performed in currency of Cash Receipt

--  Clear previously applied
  DELETE FROM cashrcptitem WHERE ((cashrcptitem_cashrcpt_id=pCashrcptId) AND (cashrcptitem_aropen_id=pAropenId));

--  Find the balance to apply
  SELECT (pAmount - (COALESCE(SUM(cashrcptitem_amount), 0) ) ),
    COALESCE(cashrcpt_docdate, current_date)
    INTO _amount, _docDate
  FROM cashrcpt LEFT OUTER JOIN cashrcptitem ON (cashrcptitem_cashrcpt_id = cashrcpt_id)
  WHERE (cashrcpt_id=pCashrcptid)
  GROUP BY cashrcpt_curr_id, cashrcpt_distdate, cashrcpt_docdate;

  SELECT (_amount - COALESCE(SUM(cashrcptmisc_amount), 0)) INTO _amount
  FROM cashrcptmisc
  WHERE (cashrcptmisc_cashrcpt_id=pCashrcptid);

  SELECT aropen_doctype INTO _doctype
  FROM aropen
  WHERE (aropen_id=pAropenId);
  
  RAISE DEBUG 'Amount (%) DocType (%)', _amount, _doctype;

  IF (_amount <= 0 AND _doctype IN ('I','D')) THEN
    RETURN 0;
  END IF;

--  Determine Line balance
  SELECT currToCurr(aropen_curr_id, cashrcpt_curr_id,
         aropen_amount - aropen_paid, cashrcpt_distdate) -
         COALESCE((SELECT (SUM(cashrcptitem_amount) + SUM(cashrcptitem_discount))
                   FROM cashrcptitem, cashrcpt
                   WHERE ((cashrcpt_id=cashrcptitem_cashrcpt_id)
                     AND  (NOT cashrcpt_void)
                     AND  (NOT cashrcpt_posted)
                     AND  (cashrcpt_id != pCashrcptId)
                     AND  (cashrcptitem_aropen_id=pAropenId))), 0)
         INTO _balance
         FROM aropen, cashrcpt
           WHERE ((aropen_id=pAropenId)
           AND (cashrcpt_id=pCashrcptId));

  RAISE DEBUG 'Balance (%)', _balance;
            
--  If Invoice or Debit Memo, determine Max Discount as per Terms
  IF (_doctype IN ('I','D')) THEN
    SELECT  round(noNeg(_balance * 
            CASE WHEN (_docDate <= determineDiscountDate(terms_id, aropen_docdate)) THEN COALESCE(terms_discprcnt, 0.0) 
            ELSE 0.00 END - applied),2),
            CASE WHEN (_docDate <= determineDiscountDate(terms_id, aropen_docdate)) THEN COALESCE(terms_discprcnt, 0.0) 
            ELSE 0.00 END INTO _discount, _discprct
    FROM aropen LEFT OUTER JOIN terms ON (terms_id=aropen_terms_id), 
         (SELECT COALESCE(SUM(arapply_applied), 0.00) AS applied  
	  FROM arapply, aropen 
          WHERE ((arapply_target_aropen_id=pAropenId) 
           AND (arapply_source_aropen_id=pAropenId) 
           AND  (aropen_discount) )
             ) AS data 
    WHERE (aropen_id=pAropenId);

--  Determine the amount to apply
    IF (_balance <= _amount + _discount) THEN
      _applyAmount := _balance - _discount;
    ELSE
      _discount := round((_amount / (1 - _discprct)) - _amount, 2);
      _applyAmount := _amount;
    END IF;
  ELSIF (_doctype IN ('C', 'R')) THEN
  -- Handle Credits, discounts don't apply here
    _applyAmount := _balance * -1;
  ELSE
    _applyAmount := _amount;
  END IF;

  IF (_applyAmount != 0) THEN
--  Create a new cashrcptitem
      INSERT INTO cashrcptitem
      ( cashrcptitem_aropen_id, cashrcptitem_cashrcpt_id,
        cashrcptitem_amount,cashrcptitem_discount )
      VALUES
      ( pAropenid, pCashrcptid, round(_applyAmount, 2), round(_discount, 2) );
  END IF;

  RETURN abs(_applyAmount);

END;
$_$;


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

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

CREATE FUNCTION applycashreceipttobalance(integer, 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
  pCashrcptid ALIAS FOR $1;
  pAmount ALIAS FOR $2;

BEGIN
  RETURN applyCashReceiptToBalance(pCashrcptid, pAmount, baseCurrId() );
END;
$_$;


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

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

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

BEGIN

  RETURN applyCashReceiptToBalance(pCashrcptid, pAmount, pCurrId, false);

END;
$_$;


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

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

CREATE FUNCTION applycashreceipttobalance(integer, numeric, 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
  pCashrcptid ALIAS FOR $1;
  pAmount ALIAS FOR $2;
  pCurrId ALIAS FOR $3;
  pInclCredits ALIAS FOR $4;
  _amount NUMERIC;
  _applied NUMERIC := 0;
  _applyAmount NUMERIC;
  _discount NUMERIC;
  _discprct NUMERIC;
  _docDate DATE;
  _r RECORD;
  _toApply NUMERIC;

BEGIN

--  Apply open credits first if applicable
  IF (pInclCredits) THEN
    -- First find total debits unaccounted for by this receipt so we can apply as much credit 
    -- as possible to clear, but no more
    SELECT coalesce(noNeg(sum(currToCurr(aropen_curr_id, cashrcpt_curr_id,
         aropen_amount - aropen_paid, cashrcpt_distdate) -
         COALESCE((SELECT (SUM(cashrcptitem_amount) + SUM(cashrcptitem_discount))
                   FROM cashrcptitem, cashrcpt
                   WHERE ((cashrcpt_id=cashrcptitem_cashrcpt_id)
                     AND  (NOT cashrcpt_void)
                     AND  (NOT cashrcpt_posted)
                     AND  (cashrcpt_id != pCashrcptid)
                     AND  (cashrcptitem_aropen_id=aropen_id))), 0)) - pAmount),0)
    INTO _toApply
    FROM cashrcpt
      JOIN custinfo ON (cashrcpt_cust_id=cust_id)
      JOIN aropen ON (cust_id=aropen_cust_id)
    WHERE ((cashrcpt_id=pCashrcptid)
      AND (aropen_open)
      AND (aropen_doctype IN ('I','D')));
           
    -- Loop through and apply credits until we account for all remaining debits we can
    FOR _r IN 
      SELECT aropen_id
      FROM cashrcpt
        JOIN custinfo ON (cashrcpt_cust_id=cust_id)
        JOIN aropen ON (cust_id=aropen_cust_id)
      WHERE ((cashrcpt_id=pCashrcptid)
        AND (aropen_open)
        AND (aropen_doctype IN ('C','R')))
      ORDER BY aropen_duedate, aropen_docnumber
    LOOP
     EXIT WHEN _toApply <= 0;
      _toApply := _toApply - applyCashReceiptLineBalance(pCashrcptid, _r.aropen_id, _toApply, pCurrId);
    END LOOP;
  END IF;

--  Find the balance to apply
  SELECT (currToCurr(pCurrId, cashrcpt_curr_id, pAmount, cashrcpt_distdate) -
              (COALESCE(SUM(cashrcptitem_amount), 0) ) ),
              COALESCE(cashrcpt_docdate, current_date) 
              INTO _amount, _docDate
  FROM cashrcpt LEFT OUTER JOIN cashrcptitem ON (cashrcptitem_cashrcpt_id = cashrcpt_id)
  WHERE (cashrcpt_id=pCashrcptid)
  GROUP BY cashrcpt_curr_id, cashrcpt_distdate, cashrcpt_docdate;

  SELECT (_amount - COALESCE(SUM(cashrcptmisc_amount), 0)) INTO _amount
  FROM cashrcptmisc
  WHERE (cashrcptmisc_cashrcpt_id=pCashrcptid);

  IF (_amount = 0) THEN
    RETURN 1;
  END IF;

--  Loop through the aropen item in order of due date, searching only for
--  aropen items that are open, for the current customer and have an outstanding balance
  FOR _r IN SELECT aropen_id,
               currToCurr(aropen_curr_id, cashrcpt_curr_id,
               aropen_amount - aropen_paid, cashrcpt_distdate) -
               COALESCE((SELECT SUM(cashrcptitem_amount) + SUM(cashrcptitem_discount)
                           FROM cashrcptitem, cashrcpt
                           WHERE ((cashrcpt_id=cashrcptitem_cashrcpt_id)
                             AND  (NOT cashrcpt_void)
                             AND  (NOT cashrcpt_posted)
                             AND  (cashrcpt_id != pCashrcptId)
                             AND  (cashrcptitem_aropen_id=aropen_id))), 0) AS balance,
                   s.cashrcptitem_id AS cashrcptitem_id
            FROM cashrcpt, aropen LEFT OUTER JOIN
                 cashrcptitem s ON (s.cashrcptitem_aropen_id=aropen_id AND s.cashrcptitem_cashrcpt_id=pCashrcptId)
                 LEFT OUTER JOIN terms ON (aropen_terms_id=terms_id),
                 (SELECT COALESCE(SUM(arapply_applied), 0.00) AS applied  
                  FROM arapply, aropen 
                  WHERE ((arapply_target_aropen_id=aropen_id) 
                    AND (arapply_source_aropen_id=aropen_id) 
                    AND  (aropen_discount) )
                 ) AS data

            WHERE ( (aropen_cust_id=cashrcpt_cust_id)
             AND (aropen_doctype IN ('I', 'D'))
             AND (aropen_open)
             AND (cashrcpt_id=pCashrcptid) )
            ORDER BY aropen_duedate, aropen_amount, balance LOOP

--  Determine Max Discount as per Terms
    SELECT  COALESCE(round(noNeg(_r.balance * 
            CASE WHEN (_docDate <= determineDiscountDate(terms_id, aropen_docdate)) THEN terms_discprcnt 
            ELSE 0.00 END - applied),2), 0),
            COALESCE(CASE WHEN (_docDate <= determineDiscountDate(terms_id, aropen_docdate)) THEN terms_discprcnt 
            ELSE 0.00 END, 0)
            INTO _discount, _discprct
            FROM aropen LEFT OUTER JOIN terms ON (terms_id=aropen_terms_id), 
                 (SELECT COALESCE(SUM(arapply_applied), 0.00) AS applied  
		          FROM arapply, aropen 
                  WHERE ((arapply_target_aropen_id=_r.aropen_id) 
                  AND (arapply_source_aropen_id=_r.aropen_id) 
                  AND  (aropen_discount) )
                 ) AS data 
            WHERE (aropen_id=_r.aropen_id);

--  Determine the amount to apply
    IF (_r.balance <= _amount + _discount) THEN
      _applyAmount := _r.balance - _discount;
    ELSE
      _discount := round((_amount / (1 - _discprct)) - _amount, 2);
      _applyAmount := _amount;
    END IF;

    IF (_applyAmount > 0) THEN
--  Does an cashrcptitem already exist?
      IF (_r.cashrcptitem_id IS NOT NULL) THEN
--  Update the cashrcptitem with the new amount to apply
        UPDATE cashrcptitem
        SET cashrcptitem_amount = round(cashrcptitem_amount + _applyAmount, 2),
            cashrcptitem_discount = round(_discount, 2)
        WHERE (cashrcptitem_id=_r.cashrcptitem_id);
      ELSE
--  Create a new cashrcptitem
        INSERT INTO cashrcptitem
        ( cashrcptitem_aropen_id, cashrcptitem_cashrcpt_id,
          cashrcptitem_amount, cashrcptitem_discount )
        VALUES
        ( _r.aropen_id, pCashrcptid, round(_applyAmount, 2), round(_discount, 2) );
      END IF;

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

    END IF;
  END LOOP;

  RETURN 1;

END;
$_$;


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

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

CREATE FUNCTION araging(date, boolean) RETURNS SETOF araging
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pAsOfDate ALIAS FOR $1;
  pUseDocDate ALIAS FOR $2;
  _row araging%ROWTYPE;

BEGIN

  FOR _row IN SELECT *
            FROM araging(pAsOfDate, pUseDocDate, true)
  LOOP
    RETURN NEXT _row;
  END LOOP;

  RETURN;
END;
$_$;


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

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

CREATE FUNCTION araging(date, boolean, boolean) RETURNS SETOF araging
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pAsOfDate ALIAS FOR $1;
  pUseDocDate ALIAS FOR $2;
  pConvBaseCurr ALIAS FOR $3;
  _row araging%ROWTYPE;
  _x RECORD;
  _returnVal INTEGER;
  _asOfDate DATE;
BEGIN

  _asOfDate := COALESCE(pAsOfDate,current_date);

  FOR _x IN
        SELECT
        aropen_docdate,
        aropen_duedate,
        aropen_ponumber,
        aropen_docnumber,
        aropen_doctype,
        cust_id,
        cust_name,
        cust_number,
        cust_custtype_id,
        custtype_code,
        terms_descrip,

        --if pConvBaseCurr is true then use currtobase to convert all amounts to base based on aropen_docdate to ensure the same exchange rate
        --otherwise use currtocurr to convert all amounts to customer's currency based on aropen_docdate to ensure the same exchange rate

        --today and greater:
        CASE WHEN((aropen_duedate >= DATE(_asOfDate))) THEN balance
             ELSE 0.0 END AS cur_val,

        --0 to 30
        CASE WHEN((aropen_duedate >= DATE(_asOfDate)-30) AND (aropen_duedate < DATE(_asOfDate))) THEN balance
             ELSE 0.0 END AS thirty_val,

        --30-60
        CASE WHEN((aropen_duedate >= DATE(_asOfDate)-60) AND (aropen_duedate < DATE(_asOfDate) - 30 )) THEN balance
             ELSE 0.0 END AS sixty_val,

        --60-90
        CASE WHEN((aropen_duedate >= DATE(_asOfDate)-90) AND (aropen_duedate < DATE(_asOfDate) - 60)) THEN balance
             ELSE 0.0 END AS ninety_val,

        --greater than 90:
        CASE WHEN((aropen_duedate > DATE(_asOfDate)-10000) AND (aropen_duedate < DATE(_asOfDate) - 90)) THEN balance
             ELSE 0.0 END AS plus_val,

        --total amount:
        CASE WHEN((aropen_duedate > DATE(_asOfDate)-10000)) THEN balance
             ELSE 0.0 END AS total_val,

        --AR Open Amount base
        aropen_amount

        FROM (
          SELECT
          (((aropen_amount - aropen_paid + COALESCE(SUM(arapply_target_paid),0))) /
             CASE WHEN (pConvBaseCurr) THEN aropen_curr_rate
                  ELSE currRate(aropen_curr_id, cust_curr_id, aropen_docdate)
             END *
             CASE WHEN (aropen_doctype IN ('C', 'R')) THEN -1.0
                  ELSE 1.0
             END) AS balance,
          ((aropen_amount) /
             CASE WHEN (pConvBaseCurr) THEN aropen_curr_rate
                  ELSE currRate(aropen_curr_id, cust_curr_id, aropen_docdate)
             END *
             CASE WHEN (aropen_doctype IN ('C', 'R')) THEN -1.0
                  ELSE 1.0
             END) AS aropen_amount,
          aropen_docdate,
          aropen_duedate,
          aropen_ponumber,
          aropen_docnumber,
          aropen_doctype,
          cust_id,
          cust_name,
          cust_number,
          cust_custtype_id,
          custtype_code,
          COALESCE(arterms.terms_descrip, custterms.terms_descrip, '') AS terms_descrip

          FROM aropen
            JOIN custinfo ON (cust_id=aropen_cust_id)
            JOIN custtype ON (custtype_id=cust_custtype_id)
            LEFT OUTER JOIN terms arterms ON (arterms.terms_id=aropen_terms_id)
            LEFT OUTER JOIN terms custterms ON (custterms.terms_id=cust_terms_id)
            LEFT OUTER JOIN arapply ON (((aropen_id=arapply_target_aropen_id)
                                      OR (aropen_id=arapply_source_aropen_id))
                                     AND (arapply_distdate>_asOfDate))
          WHERE ( (CASE WHEN (pUseDocDate) THEN aropen_docdate ELSE aropen_distdate END <= _asOfDate)
          AND (COALESCE(aropen_closedate,_asOfDate+1)>_asOfDate) )
          GROUP BY aropen_id,aropen_docdate,aropen_duedate,aropen_ponumber,aropen_docnumber,aropen_doctype,aropen_paid,
                   aropen_curr_id,aropen_amount,cust_id,cust_name,cust_number,cust_custtype_id,custtype_code,
                   arterms.terms_descrip,custterms.terms_descrip, aropen_curr_rate, aropen_curr_id, cust_curr_id
          ORDER BY cust_number, aropen_duedate ) AS data
  LOOP
        _row.araging_docdate := _x.aropen_docdate;
        _row.araging_duedate := _x.aropen_duedate;
        _row.araging_ponumber := _x.aropen_ponumber;
        _row.araging_docnumber := _x.aropen_docnumber;
        _row.araging_doctype := _x.aropen_doctype;
        _row.araging_cust_id := _x.cust_id;
        _row.araging_cust_number := _x.cust_number;
        _row.araging_cust_name := _x.cust_name;
        _row.araging_cust_custtype_id := _x.cust_custtype_id;
        _row.araging_custtype_code := _x.custtype_code;
        _row.araging_terms_descrip := _x.terms_descrip;
        _row.araging_aropen_amount := _x.aropen_amount;
        _row.araging_cur_val := _x.cur_val;
        _row.araging_thirty_val := _x.thirty_val;
        _row.araging_sixty_val := _x.sixty_val;
        _row.araging_ninety_val := _x.ninety_val;
        _row.araging_plus_val := _x.plus_val;
        _row.araging_total_val := _x.total_val;
        RETURN NEXT _row;
  END LOOP;
  RETURN;
END;
$_$;


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

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

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

BEGIN

  -- Return amount applied to an aropen in base currency item as of the parameter date
  SELECT SUM(currtobase(arapply_curr_id,arapply_applied,pDate)) INTO _amount
  FROM arapply
  WHERE (((arapply_target_aropen_id = pAropenid) OR (arapply_source_aropen_id = pAropenid))
  AND (((arapply_journalnumber=0) AND (arapply_postdate <= pDate))
  OR EXISTS(SELECT * 
             FROM gltrans 
             WHERE ((gltrans_journalnumber=arapply_journalnumber)
             AND (gltrans_date <= pDate)))));

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

END;
$_$;


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

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

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

BEGIN

  INSERT INTO asohist ( asohist_id,
                        asohist_cust_id,
                        asohist_itemsite_id,
                        asohist_shipdate,
                        asohist_invcdate,
                        asohist_duedate,
                        asohist_promisedate,
                        asohist_ordernumber,
                        asohist_invcnumber,
                        asohist_qtyshipped,
                        asohist_unitprice,
                        asohist_unitcost,
                        asohist_billtoname,
                        asohist_billtoaddress1,
                        asohist_billtoaddress2,
                        asohist_billtoaddress3,
                        asohist_billtocity,
                        asohist_billtostate,
                        asohist_billtozip,
                        asohist_shiptoname,
                        asohist_shiptoaddress1,
                        asohist_shiptoaddress2,
                        asohist_shiptoaddress3,
                        asohist_shiptocity,
                        asohist_shiptostate,
                        asohist_shiptozip,
                        asohist_shipto_id,
                        asohist_shipvia,
                        asohist_salesrep_id,
                        asohist_misc_type,
                        asohist_misc_descrip,
                        asohist_misc_id,
                        asohist_commission,
                        asohist_commissionpaid,
                        asohist_doctype,
                        asohist_orderdate,
                        asohist_imported,
			asohist_ponumber,
                        asohist_curr_id,
                        asohist_taxtype_id,
                        asohist_taxzone_id )
  SELECT cohist_id,
         cohist_cust_id,
         cohist_itemsite_id,
         cohist_shipdate,
         cohist_invcdate,
         cohist_duedate,
         cohist_promisedate,
         cohist_ordernumber,
         cohist_invcnumber,
         cohist_qtyshipped,
         cohist_unitprice,
         cohist_unitcost,
         cohist_billtoname,
         cohist_billtoaddress1,
         cohist_billtoaddress2,
         cohist_billtoaddress3,
         cohist_billtocity,
         cohist_billtostate,
         cohist_billtozip,
         cohist_shiptoname,
         cohist_shiptoaddress1,
         cohist_shiptoaddress2,
         cohist_shiptoaddress3,
         cohist_shiptocity,
         cohist_shiptostate,
         cohist_shiptozip,
         cohist_shipto_id,
         cohist_shipvia,
         cohist_salesrep_id,
         cohist_misc_type,
         cohist_misc_descrip,
         cohist_misc_id,
         cohist_commission,
         cohist_commissionpaid,
         cohist_doctype,
         cohist_orderdate,
         cohist_imported,
         cohist_ponumber,
	 cohist_curr_id,
         cohist_taxtype_id,
         cohist_taxzone_id
  FROM cohist
  WHERE (cohist_id=pSohistid);

  INSERT INTO asohisttax ( taxhist_id,
                           taxhist_parent_id,
                           taxhist_taxtype_id,
                           taxhist_tax_id,
                           taxhist_basis,
                           taxhist_basis_tax_id,
                           taxhist_sequence,
                           taxhist_percent,
                           taxhist_amount,
                           taxhist_tax,
                           taxhist_docdate,
                           taxhist_distdate,
                           taxhist_curr_id,
                           taxhist_curr_rate,
                           taxhist_journalnumber )
  SELECT taxhist_id,
         taxhist_parent_id,
         taxhist_taxtype_id,
         taxhist_tax_id,
         taxhist_basis,
         taxhist_basis_tax_id,
         taxhist_sequence,
         taxhist_percent,
         taxhist_amount,
         taxhist_tax,
         taxhist_docdate,
         taxhist_distdate,
         taxhist_curr_id,
         taxhist_curr_rate,
         taxhist_journalnumber
  FROM cohisttax
  WHERE (taxhist_parent_id=pSohistid);

  DELETE FROM cohisttax
  WHERE (taxhist_parent_id=pSohistid);

  DELETE FROM cohist
  WHERE (cohist_id=pSohistid);

  RETURN pSohistid;

END;
$_$;


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

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

CREATE FUNCTION arcurrgain(integer, integer, numeric, date) RETURNS numeric
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pAropenId ALIAS FOR $1;
  pCurrId ALIAS FOR $2;
  pValue ALIAS FOR $3;
  pDate ALIAS FOR $4;
  _start DATE;
  _end DATE;
  _gain NUMERIC;
  _r RECORD;

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

  SELECT aropen_docdate, aropen_curr_id, aropen_curr_rate
    INTO _r
  FROM aropen
  WHERE (aropen_id=pAropenId);

  IF (_r.aropen_docdate > pDate) THEN
    _gain := (currToBase(pCurrId, pValue, pDate) - currToCurr(pCurrId,_r.aropen_curr_id, pValue, pDate) / _r.aropen_curr_rate) * -1;
  ELSE
    _gain := currToCurr(pCurrId,_r.aropen_curr_id, pValue, pDate) / _r.aropen_curr_rate - currToBase(pCurrId, pValue, pDate);
  END IF;

  IF (_gain IS NULL) THEN
    RAISE EXCEPTION 'Error processing currency gain/loss.';
  END IF;

  RETURN _gain;
END;
$_$;


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

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

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


ALTER FUNCTION public.armor(bytea) OWNER TO admin;

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

CREATE TABLE invbal (
    invbal_id integer NOT NULL,
    invbal_period_id integer,
    invbal_itemsite_id integer,
    invbal_qoh_beginning numeric(18,6) NOT NULL,
    invbal_qoh_ending numeric(18,6) NOT NULL,
    invbal_qty_in numeric(18,6) NOT NULL,
    invbal_qty_out numeric(18,6) NOT NULL,
    invbal_value_beginning numeric(12,2) NOT NULL,
    invbal_value_ending numeric(12,2) NOT NULL,
    invbal_value_in numeric(12,2) NOT NULL,
    invbal_value_out numeric(12,2) NOT NULL,
    invbal_nn_beginning numeric(18,6) NOT NULL,
    invbal_nn_ending numeric(18,6) NOT NULL,
    invbal_nn_in numeric(18,6) NOT NULL,
    invbal_nn_out numeric(18,6) NOT NULL,
    invbal_nnval_beginning numeric(12,2) NOT NULL,
    invbal_nnval_ending numeric(12,2) NOT NULL,
    invbal_nnval_in numeric(12,2) NOT NULL,
    invbal_nnval_out numeric(12,2) NOT NULL,
    invbal_dirty boolean DEFAULT true NOT NULL
);


ALTER TABLE invbal OWNER TO admin;

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

CREATE FUNCTION asofinvbal(integer, date) RETURNS SETOF invbal
    LANGUAGE plpgsql STABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pItemsiteId ALIAS FOR $1;
  pAsofDate ALIAS FOR $2;
  _result invbal%ROWTYPE;
  _i RECORD;
  _h RECORD;
  _r RECORD;
  _prevCostmethod TEXT := 'A';
  _prevDate TIMESTAMP WITH TIME ZONE;
  _runningQty NUMERIC := 0;
  _runningNn NUMERIC := 0;
  _runningValue NUMERIC := 0;
  _runningNnval NUMERIC := 0;

BEGIN
  /* This is a base function to gather data.  Because it is STABLE it should only need
  to be calculated once, even though it is likely to be called several times by other
  functions in parent query to present the various data.
  */
  
  -- First make sure inventory balance is forward updated
  PERFORM forwardUpdateItemsite(pItemsiteId);

  -- Next find the previous period balace to use as a starting point
  SELECT invbal.*, period_start, itemsite_costmethod INTO _i
  FROM invbal 
    JOIN itemsite ON (invbal_itemsite_id=itemsite_id) 
    JOIN period ON (invbal_period_id=period_id)
  WHERE ((invbal_itemsite_id=pItemsiteId)
    AND  (pAsofDate >= period_start))
  ORDER BY period_start DESC
  LIMIT 1;

  _runningQty := _i.invbal_qoh_beginning;
  _runningNn := _i.invbal_nn_beginning;
  _runningValue := _i.invbal_value_beginning;
  _runningNnval := _i.invbal_nnval_beginning;
  _prevDate := _i.period_start;
  _prevCostmethod := _i.itemsite_costmethod;

  FOR _r IN 
    SELECT invhist_id, invhist_created, invhist_invqty, invhist_transtype, invhist_unitcost,
      invhist_costmethod, itemsite_item_id, invhistSense(invhist_id) AS sense
    FROM invhist
      JOIN itemsite ON (itemsite_id=invhist_itemsite_id)
    WHERE ((invhist_itemsite_id=pItemsiteId)
    AND (invhist_transdate::date BETWEEN _i.period_start AND pAsofdate))
    ORDER BY invhist_created, invhist_id
  LOOP
    -- Update balances changed by any standard cost update between transactions
    IF (_prevCostmethod = 'S' AND _runningQty != 0) THEN
      FOR _h IN
        SELECT costhist_oldcost, costhist_newcost
        FROM costhist
          JOIN item ON (costhist_item_id=item_id)
          JOIN itemsite ON (itemsite_item_id=item_id)
        WHERE ((itemsite_id=pItemsiteId)
          AND (costhist_date BETWEEN _prevDate AND _r.invhist_created)
          AND (costhist_type IN ('S','D')))
      LOOP
        _runningValue := _runningValue + round((_h.costhist_newcost-_h.costhist_oldcost) * _runningQty,2);
        _runningNnval := _runningNnval + round((_h.costhist_newcost-_h.costhist_oldcost) * _runningNn,2);
      END LOOP;
    END IF;

    _prevDate := _r.invhist_created;
    _prevCostmethod := _r.invhist_costmethod;
    _runningQty := _runningQty + _r.invhist_invqty * _r.sense;
    _runningValue := _runningValue + round( _r.invhist_invqty * _r.sense * _r.invhist_unitcost,2);
    IF (_r.invhist_transtype = 'NN') THEN
      _runningNn := _runningNn + _r.invhist_invqty * -1;
      _runningNnval := _runningNnval + round( _r.invhist_invqty * -1 * _r.invhist_unitcost,2);
    END IF;
    
  END LOOP;

  _prevDate := COALESCE(_prevDate, _i.period_start);
  _prevCostmethod := COALESCE(_r.invhist_costmethod, _i.itemsite_costmethod);
  
  IF (_prevCostmethod = 'S' AND _runningQty != 0) THEN
    FOR _h IN
      SELECT costhist_oldcost, costhist_newcost
      FROM costhist
        JOIN item ON (costhist_item_id=item_id)
        JOIN itemsite ON (itemsite_item_id=item_id)
      WHERE ((itemsite_id=pItemsiteId)
        AND (costhist_date BETWEEN _prevDate AND CAST(pAsofDate + 1 AS TIMESTAMP WITH TIME ZONE))
        AND (costhist_type IN ('S','D')))
    LOOP
      _runningValue := _runningValue + round((_h.costhist_newcost-_h.costhist_oldcost) * _runningQty,2);
      _runningNnval := _runningNnval + round((_h.costhist_newcost-_h.costhist_oldcost) * _runningNn,2);
    END LOOP;
  END IF;

  _result := _i;
  _result.invbal_qoh_ending := _runningQty;
  _result.invbal_value_ending := _runningValue;
  _result.invbal_nn_ending := _runningNn;
  _result.invbal_nnval_ending := _runningNnval;

  RETURN NEXT _result;

  RETURN;
  
END;
$_$;


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

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

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

BEGIN

  SELECT invbal_nn_ending INTO _result
  FROM asofinvbal(pItemsiteId, pAsofDate);

  RETURN COALESCE(_result, 0);
  
END;
$_$;


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

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

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

BEGIN

  SELECT invbal_qoh_ending INTO _result
  FROM asofinvbal(pItemsiteId, pAsofDate);

  RETURN COALESCE(_result, 0);
  
END;
$_$;


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

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

CREATE FUNCTION assessfinancecharge(paropenid integer, passessdate date, passessamount 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
  _fc           RECORD;
  _ar           RECORD;
  _invcheadid   INTEGER;

BEGIN

  -- cache some information
  SELECT * INTO _fc FROM fincharg;
  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'assessFinanceCharge, configuration not set.';
  END IF;

  SELECT * INTO _ar FROM aropen WHERE (aropen_id=pAropenid);
  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'assessFinanceCharge, aropen not found.';
  END IF;

  -- create invoice head
  INSERT INTO invchead
    ( invchead_cust_id,
      invchead_shipto_id,
      invchead_ordernumber,
      invchead_orderdate,
      invchead_posted,
      invchead_printed,
      invchead_invcnumber,
      invchead_invcdate,
      invchead_shipdate,
      invchead_ponumber,
      invchead_shipvia,
      invchead_fob,
      invchead_billto_name,
      invchead_billto_address1,
      invchead_billto_address2,
      invchead_billto_address3,
      invchead_billto_city,
      invchead_billto_state,
      invchead_billto_zipcode,
      invchead_billto_phone,
      invchead_shipto_name,
      invchead_shipto_address1,
      invchead_shipto_address2,
      invchead_shipto_address3,
      invchead_shipto_city,
      invchead_shipto_state,
      invchead_shipto_zipcode,
      invchead_shipto_phone,
      invchead_salesrep_id,
      invchead_commission,
      invchead_terms_id,
      invchead_freight,
      invchead_misc_amount,
      invchead_misc_descrip,
      invchead_misc_accnt_id,
      invchead_payment,
      invchead_paymentref,
      invchead_notes,
      invchead_billto_country,
      invchead_shipto_country,
      invchead_prj_id,
      invchead_curr_id,
      invchead_gldistdate,
      invchead_recurring,
      invchead_recurring_interval,
      invchead_recurring_type,
      invchead_recurring_until,
      invchead_recurring_invchead_id,
      invchead_shipchrg_id,
      invchead_taxzone_id,
      invchead_void,
      invchead_saletype_id,
      invchead_shipzone_id )
  SELECT
      invchead_cust_id,
      invchead_shipto_id,
      invchead_ordernumber,
      invchead_orderdate,
      FALSE,
      FALSE,
      fetchInvcNumber(),
      pAssessDate,
      invchead_shipdate,
      invchead_ponumber,
      invchead_shipvia,
      invchead_fob,
      invchead_billto_name,
      invchead_billto_address1,
      invchead_billto_address2,
      invchead_billto_address3,
      invchead_billto_city,
      invchead_billto_state,
      invchead_billto_zipcode,
      invchead_billto_phone,
      invchead_shipto_name,
      invchead_shipto_address1,
      invchead_shipto_address2,
      invchead_shipto_address3,
      invchead_shipto_city,
      invchead_shipto_state,
      invchead_shipto_zipcode,
      invchead_shipto_phone,
      invchead_salesrep_id,
      0.0,
      invchead_terms_id,
      0.0,
      0.0,
      NULL,
      NULL,
      0.0,
      NULL,
      '',
      invchead_billto_country,
      invchead_shipto_country,
      invchead_prj_id,
      invchead_curr_id,
      NULL,
      FALSE,
      NULL,
      NULL,
      NULL,
      NULL,
      invchead_shipchrg_id,
      invchead_taxzone_id,
      invchead_void,
      invchead_saletype_id,
      invchead_shipzone_id
  FROM invchead
  WHERE (invchead_invcnumber=_ar.aropen_docnumber)
  RETURNING invchead_id INTO _invcheadid;

  -- create invoice item
  INSERT INTO invcitem
    ( invcitem_invchead_id,
      invcitem_linenumber,
      invcitem_item_id,
      invcitem_warehous_id,
      invcitem_custpn,
      invcitem_number,
      invcitem_descrip,
      invcitem_ordered,
      invcitem_billed,
      invcitem_custprice,
      invcitem_price,
      invcitem_notes,
      invcitem_salescat_id,
      invcitem_taxtype_id,
      invcitem_qty_uom_id,
      invcitem_qty_invuomratio,
      invcitem_price_uom_id,
      invcitem_price_invuomratio,
      invcitem_coitem_id,
      invcitem_updateinv,
      invcitem_rev_accnt_id )
  VALUES
    ( _invcheadid,
      1,
      -1,
      -1,
      NULL,
      _fc.fincharg_markoninvoice,
--    - enhance data shown in the item description for the invoice line to indicate which invoice is affected
--    - Feature Request 23344       
--      'Finance Charge Assessment',
      'Finance Charge Assessment - Invoice Number ' || _ar.aropen_docnumber || ' - Past Due Balance ' || (_ar.aropen_amount - _ar.aropen_paid) || ' Due Date - ' || _ar.aropen_duedate,
      1.0,
      1.0,
      pAssessAmount,
      pAssessAmount,
      '',
      _fc.fincharg_salescat_id,
      NULL,
      NULL,
      1.0,
      NULL,
      1.0,
      NULL,
      FALSE,
      _fc.fincharg_accnt_id );

  -- update aropen
  UPDATE aropen SET aropen_fincharg_date = pAssessDate,
                    aropen_fincharg_amount = COALESCE(aropen_fincharg_amount, 0.0) + pAssessAmount
  WHERE (aropen_id=pAropenid);


  RETURN 0;

END;
$$;


ALTER FUNCTION public.assessfinancecharge(paropenid integer, passessdate date, passessamount numeric) OWNER TO admin;

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

CREATE FUNCTION attachcontact(integer, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pcntctId    ALIAS FOR $1;
  pcrmacctId  ALIAS FOR $2;
BEGIN
  UPDATE cntct SET cntct_crmacct_id = pcrmacctId
  WHERE cntct_id = pcntctId;

  RETURN 0;
END;
$_$;


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

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

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

-- Check Quote
  IF (NOT EXISTS(SELECT quhead_id
                 FROM quhead
                 WHERE (quhead_id=pQuheadid))) THEN
    RETURN -1;
  END IF;

-- Check Opportunity
  IF (NOT EXISTS(SELECT ophead_id
                 FROM ophead
                 WHERE (ophead_id=pOpheadid))) THEN
    RETURN -2;
  END IF;

-- Cannot attach if already attached
  IF (EXISTS(SELECT quhead_id
	     FROM quhead
	     WHERE ((quhead_id=pQuheadid)
	       AND  (quhead_ophead_id IS NOT NULL)))) THEN
    RETURN -3;
  END IF;

  UPDATE quhead SET quhead_ophead_id=pOpheadid
  WHERE (quhead_id=pQuheadid);

  RETURN 0;

END;
$_$;


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

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

CREATE FUNCTION attachsalesordertoopportunity(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
  pSoheadid	ALIAS FOR $1;
  pOpheadid	ALIAS FOR $2;
BEGIN

-- Check Sales Order
  IF (NOT EXISTS(SELECT cohead_id
                 FROM cohead
                 WHERE (cohead_id=pSoheadid))) THEN
    RETURN -1;
  END IF;

-- Check Opportunity
  IF (NOT EXISTS(SELECT ophead_id
                 FROM ophead
                 WHERE (ophead_id=pOpheadid))) THEN
    RETURN -2;
  END IF;

-- Cannot attach if already attached
  IF (EXISTS(SELECT cohead_id
	     FROM cohead
	     WHERE ((cohead_id=pSoheadid)
	       AND  (cohead_ophead_id IS NOT NULL)))) THEN
    RETURN -3;
  END IF;

  UPDATE cohead SET cohead_ophead_id=pOpheadid
  WHERE (cohead_id=pSoheadid);

  RETURN 0;

END;
$_$;


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

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

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

BEGIN
-- Returns value in base currency
-- ToDo: is cohist_shipdate the right DATE to use?
  SELECT SUM(cohist_qtyshipped * currToBase(cohist_curr_id, cohist_unitprice,
                                            cohist_shipdate)) AS totalsales,
         SUM(cohist_qtyshipped) AS totalship INTO _p
  FROM cohist
  WHERE ( (cohist_itemsite_id=pItemsiteid)
   AND (cohist_invcdate BETWEEN pStartDate AND pEndDate) );

  IF ( (_p.totalship IS NULL) OR
       (_p.totalship = 0) ) THEN
    RETURN 0;
  ELSE
    RETURN (_p.totalsales / _p.totalship);
  END IF;

END;
$_$;


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

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

CREATE FUNCTION avgcost(pitemsiteid integer) RETURNS numeric
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _value NUMERIC;
  _qoh NUMERIC;
BEGIN
  SELECT itemsite_value, itemsite_qtyonhand
    INTO _value, _qoh
    FROM itemsite
   WHERE(itemsite_id=pItemsiteid);
  IF (_qoh = 0.0) THEN
    RETURN 0.0;
  END IF;
  RETURN _value / _qoh;
END;
$$;


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

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

CREATE FUNCTION balanceitemsite(pitemsiteid integer) RETURNS integer
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _itemlocseries INTEGER;
  _balanced NUMERIC;
  _qoh NUMERIC;

BEGIN

--  Make sure that that passed Itemsite is MLC or Lot/Serial controlled
  IF ( ( SELECT (NOT ( (itemsite_loccntrl) OR (itemsite_controlmethod IN ('L', 'S')) ))
         FROM itemsite
         WHERE (itemsite_id=pItemsiteid) ) ) THEN
    RETURN 0;
  END IF;

  IF ( ( SELECT itemsite_freeze
           FROM itemsite
          WHERE(itemsite_id=pItemsiteid) ) ) THEN
    RETURN -1;
  END IF;

--  Calculate the qoh
  SELECT COALESCE(SUM(itemloc_qty), 0) INTO _balanced
  FROM itemloc
  WHERE (itemloc_itemsite_id=pItemsiteid);

--  Post an AD Transaction
  SELECT invAdjustment( itemsite_id, (_balanced - itemsite_qtyonhand),
                        'Balance', 'Inventory Balance' ) INTO _itemlocseries
  FROM itemsite
  WHERE (itemsite_id=pItemsiteid);

--  Post the itemloc series which will postIntoTrialBalance and postInvHist
  PERFORM postItemlocSeries(_itemlocseries);

--  Kill the resultant distribution records
  DELETE FROM itemlocdist
  WHERE (itemlocdist_series=_itemlocseries);

  RETURN 1;

END;
$$;


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

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

CREATE FUNCTION bankreconciliation(pbankrecid integer, ptask 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.
-- posting and reopening bank reconciliations are nearly identical.
-- the main differences revolve around what cleanup is done before starting.
-- other than that, posting and reopening touch the same g/l accounts but with
-- debits and credits reversed.
DECLARE
  _accntid      INTEGER;
  _bankrecid    INTEGER;
  _gltransid    INTEGER;
  _post         BOOLEAN;
  _r            RECORD;
  _result       INTEGER;
  _sequence     INTEGER;
  _sign         INTEGER := 1;
  _tax          RECORD;

BEGIN

  CASE lower(pTask)
    WHEN 'post'   THEN _post = TRUE;
    WHEN 'reopen' THEN _post = FALSE;
    ELSE RAISE EXCEPTION
          'bankReconciliation got an invalid task %1 [xtuple: bankReconciliation, -2, %2]',
          pTask, pTask;
  END CASE;

  -- Check the accnt information to make sure it is valid
  SELECT accnt_id INTO _accntid
    FROM bankrec
    JOIN bankaccnt ON (bankrec_bankaccnt_id=bankaccnt_id)
    JOIN accnt     ON (bankaccnt_accnt_id=accnt_id)
   WHERE (bankrec_id=pBankrecid);
  IF ( NOT FOUND ) THEN
    RAISE EXCEPTION 'bankReconciliation %1 %2 did not find the bank''s G/L account [xtuple: bankReconciliation, -1, %3, %4]',
                    pTask, pBankrecid, pTask, pBankrecid;
  END IF;

  IF _post THEN
    DELETE FROM bankrecitem
     WHERE ( (NOT bankrecitem_cleared)
       AND   (bankrecitem_bankrec_id=pBankrecid) );

    -- Post any cleared bankadj items and convert the bankrecitem
    FOR _r IN SELECT bankrecitem_id, bankrecitem_source_id
                FROM bankrecitem, bankadj
               WHERE ( (bankrecitem_source = 'AD')
                 AND   (bankrecitem_source_id=bankadj_id)
                 AND   (bankrecitem_cleared)
                 AND   (NOT bankadj_posted)
                 AND   (bankrecitem_bankrec_id=pBankrecid) ) LOOP

      _sequence := postBankAdjustment(_r.bankrecitem_source_id);

      IF (_sequence < 0) THEN
        RAISE EXCEPTION 'postBankAdjustment %1 %2 failed during bankReconciliation [xtuple: postBankAdjustment, -10, %3, %4, %5]',
                         pTask, pBankrecid, pTask, pBankrecid, _sequence;
      END IF;

      SELECT gltrans_id INTO _gltransid
        FROM gltrans
       WHERE ( (gltrans_sequence=_sequence)
         AND   (gltrans_accnt_id=_accntid) );
      IF ( NOT FOUND ) THEN
        RAISE EXCEPTION 'bankReconciliation %1 %2 did not find exactly one gltrans record for %3 [xtuple: bankReconciliation, -11, %4, %5, %6]',
                        pTask, pBankrecid, _sequence, pTask, pBankrecid, _sequence;
      END IF;

      UPDATE bankrecitem
         SET bankrecitem_source = 'GL',
             bankrecitem_source_id=_gltransid
       WHERE (bankrecitem_id=_r.bankrecitem_id);

    END LOOP;

  ELSE -- NOT _post, therefore must be reopen
    _sign := -1;
    SELECT bankrec_id INTO _bankrecid
      FROM bankrec
     WHERE (NOT bankrec_posted);
    IF (FOUND) THEN
      -- Delete any bankrecitem records for unposted periods
      DELETE FROM bankrecitem
       WHERE (bankrecitem_bankrec_id=_bankrecid);
      -- Delete any bankrec records for unposted period
      DELETE FROM bankrec
       WHERE (bankrec_id=_bankrecid);
    END IF;
  END IF;

  IF (fetchMetricBool('CashBasedTax')) THEN
    -- Cash based tax distributions
    -- GL Transactions
    SELECT fetchGLSequence() INTO _sequence;
    FOR _r IN SELECT *
              FROM bankrecitem
             WHERE ( (bankrecitem_cleared)
               AND   (bankrecitem_bankrec_id=pBankrecid) ) LOOP
      -- first, debit the tax liability clearing account
      -- and credit the tax liability distribution account
      -- for each tax code
      FOR _tax IN SELECT docnumber, custname, distdate, source, doctype,
                         tax_sales_accnt_id, tax_dist_accnt_id,
                         ROUND(currToBase(currid, ROUND(SUM(taxhist_tax),2), taxhist_docdate) * percentpaid, 2) AS taxbasevalue
                  FROM (
                        -- Cash receipt, gltrans
                        SELECT aropen_docnumber AS docnumber, cust_name AS custname,
                               aropen_curr_id AS currid, gltrans_date AS distdate,
                               (cashrcptitem_amount / aropen_amount) AS percentpaid,
                               gltrans_source AS source, gltrans_doctype AS doctype,
                               tax_sales_accnt_id, tax_dist_accnt_id,
                               taxhist_tax, taxhist_docdate
                        FROM gltrans JOIN cashrcpt  ON ((gltrans_source='A/R')
                                                    AND (gltrans_doctype='CR')
                                                    AND (gltrans_misc_id=cashrcpt_id))
                                     JOIN cashrcptitem ON (cashrcptitem_cashrcpt_id=cashrcpt_id)
                                     JOIN aropen ON (aropen_id=cashrcptitem_aropen_id)
                                     JOIN custinfo ON (cust_id=aropen_cust_id)
                                     JOIN cohist ON (cohist_invcnumber=aropen_docnumber AND cohist_doctype=aropen_doctype)
                                     JOIN cohisttax ON (taxhist_parent_id=cohist_id)
                                     JOIN tax ON (tax_id=taxhist_tax_id)
                        WHERE (gltrans_id=_r.bankrecitem_source_id)
                        -- Cash receipt, sltrans
                        UNION
                        SELECT aropen_docnumber AS docnumber, cust_name AS custname,
                               aropen_curr_id AS currid, sltrans_date AS distdate,
                               (cashrcptitem_amount / aropen_amount) AS percentpaid,
                               sltrans_source AS source, sltrans_doctype AS doctype,
                               tax_sales_accnt_id, tax_dist_accnt_id,
                               taxhist_tax, taxhist_docdate
                        FROM sltrans JOIN cashrcpt  ON ((sltrans_source='A/R')
                                                    AND (sltrans_doctype='CR')
                                                    AND (sltrans_misc_id=cashrcpt_id))
                                     JOIN cashrcptitem ON (cashrcptitem_cashrcpt_id=cashrcpt_id)
                                     JOIN aropen ON (aropen_id=cashrcptitem_aropen_id)
                                     JOIN custinfo ON (cust_id=aropen_cust_id)
                                     JOIN cohist ON (cohist_invcnumber=aropen_docnumber AND cohist_doctype=aropen_doctype)
                                     JOIN cohisttax ON (taxhist_parent_id=cohist_id)
                                     JOIN tax ON (tax_id=taxhist_tax_id)
                        WHERE (sltrans_id=_r.bankrecitem_source_id)
                        -- Cash payment, gltrans
                        UNION
                        SELECT apopen_docnumber AS docnumber, vend_name AS vendname,
                               apopen_curr_id AS currid, gltrans_date AS distdate,
                               (checkitem_amount / apopen_amount) AS percentpaid,
                               gltrans_source AS source, gltrans_doctype AS doctype,
                               tax_sales_accnt_id, tax_dist_accnt_id,
                               taxhist_tax, taxhist_docdate
                        FROM gltrans JOIN checkhead ON ((gltrans_source='A/P')
                                                    AND (gltrans_doctype='CK')
                                                    AND (gltrans_misc_id=checkhead_id))
                                     JOIN checkitem ON (checkitem_checkhead_id=checkhead_id)
                                     JOIN apopen ON (apopen_id=checkitem_apopen_id)
                                     JOIN vohead ON (vohead_number=apopen_docnumber)
                                     JOIN vendinfo ON (vend_id=apopen_vend_id)
                                     JOIN voheadtax ON (taxhist_parent_id=vohead_id)
                                     JOIN tax ON (tax_id=taxhist_tax_id)
                        WHERE (gltrans_id=_r.bankrecitem_source_id)
                        UNION
                        SELECT apopen_docnumber AS docnumber, vend_name AS vendname,
                               apopen_curr_id AS currid, gltrans_date AS distdate,
                               (checkitem_amount / apopen_amount) AS percentpaid,
                               gltrans_source AS source, gltrans_doctype AS doctype,
                               tax_sales_accnt_id, tax_dist_accnt_id,
                               taxhist_tax, taxhist_docdate
                        FROM gltrans JOIN checkhead ON ((gltrans_source='A/P')
                                                    AND (gltrans_doctype='CK')
                                                    AND (gltrans_misc_id=checkhead_id))
                                     JOIN checkitem ON (checkitem_checkhead_id=checkhead_id)
                                     JOIN apopen ON (apopen_id=checkitem_apopen_id)
                                     JOIN vohead ON (vohead_number=apopen_docnumber)
                                     JOIN vendinfo ON (vend_id=apopen_vend_id)
                                     JOIN voitem ON (voitem_vohead_id=vohead_id)
                                     JOIN voitemtax ON (taxhist_parent_id=voitem_id)
                                     JOIN tax ON (tax_id=taxhist_tax_id)
                        WHERE (gltrans_id=_r.bankrecitem_source_id)
                        -- Cash payment, sltrans
                        UNION
                        SELECT apopen_docnumber AS docnumber, vend_name AS vendname,
                               apopen_curr_id AS currid, sltrans_date AS distdate,
                               (checkitem_amount / apopen_amount) AS percentpaid,
                               sltrans_source AS source, sltrans_doctype AS doctype,
                               tax_sales_accnt_id, tax_dist_accnt_id,
                               taxhist_tax, taxhist_docdate
                        FROM sltrans JOIN checkhead ON ((sltrans_source='A/P')
                                                    AND (sltrans_doctype='CK')
                                                    AND (sltrans_misc_id=checkhead_id))
                                     JOIN checkitem ON (checkitem_checkhead_id=checkhead_id)
                                     JOIN apopen ON (apopen_id=checkitem_apopen_id)
                                     JOIN vohead ON (vohead_number=apopen_docnumber)
                                     JOIN vendinfo ON (vend_id=apopen_vend_id)
                                     JOIN voheadtax ON (taxhist_parent_id=vohead_id)
                                     JOIN tax ON (tax_id=taxhist_tax_id)
                        WHERE (sltrans_id=_r.bankrecitem_source_id)
                        UNION
                        SELECT apopen_docnumber AS docnumber, vend_name AS vendname,
                               apopen_curr_id AS currid, sltrans_date AS distdate,
                               (checkitem_amount / apopen_amount) AS percentpaid,
                               sltrans_source AS source, sltrans_doctype AS doctype,
                               tax_sales_accnt_id, tax_dist_accnt_id,
                               taxhist_tax, taxhist_docdate
                        FROM sltrans JOIN checkhead ON ((sltrans_source='A/P')
                                                    AND (sltrans_doctype='CK')
                                                    AND (sltrans_misc_id=checkhead_id))
                                     JOIN checkitem ON (checkitem_checkhead_id=checkhead_id)
                                     JOIN apopen ON (apopen_id=checkitem_apopen_id)
                                     JOIN vohead ON (vohead_number=apopen_docnumber)
                                     JOIN vendinfo ON (vend_id=apopen_vend_id)
                                     JOIN voitem ON (voitem_vohead_id=vohead_id)
                                     JOIN voitemtax ON (taxhist_parent_id=voitem_id)
                                     JOIN tax ON (tax_id=taxhist_tax_id)
                        WHERE (sltrans_id=_r.bankrecitem_source_id)
                       ) AS data
                  GROUP BY docnumber, custname, currid, distdate, percentpaid,
                           source, doctype,
                           tax_sales_accnt_id, tax_dist_accnt_id, taxhist_docdate
      LOOP
        SELECT insertIntoGLSeries( _sequence, _tax.source, _tax.doctype, _tax.docnumber,
                                   _tax.tax_dist_accnt_id, 
                                   _tax.taxbasevalue * _sign,
                                   COALESCE(_r.bankrecitem_effdate, _tax.distdate), _tax.custname ) INTO _result;
        IF (_result < 0) THEN
          RAISE EXCEPTION 'insertIntoGLSeries failed, result=%', _result;
        END IF;
        SELECT insertIntoGLSeries( _sequence, _tax.source, _tax.doctype, _tax.docnumber,
                                   _tax.tax_sales_accnt_id, 
                                   (_tax.taxbasevalue * -1.0 * _sign),
                                   COALESCE(_r.bankrecitem_effdate, _tax.distdate), _tax.custname ) INTO _result;
        IF (_result < 0) THEN
          RAISE EXCEPTION 'insertIntoGLSeries failed, result=%', _result;
        END IF;
      END LOOP;

      -- second, create a taxpay row for each taxhist
      FOR _tax IN SELECT taxhist_id, applyid, distdate,
                         ROUND(taxhist_tax * percentpaid, 2) AS taxpaid
                  FROM (
                        -- Cash receipt, gltrans
                        SELECT taxhist_id, aropen_id AS applyid, gltrans_date AS distdate, taxhist_tax,
                               (cashrcptitem_amount / aropen_amount) AS percentpaid
                          FROM gltrans JOIN cashrcpt  ON ((gltrans_source='A/R')
                                                      AND (gltrans_doctype='CR')
                                                      AND (gltrans_misc_id=cashrcpt_id))
                                       JOIN cashrcptitem ON (cashrcptitem_cashrcpt_id=cashrcpt_id)
                                       JOIN aropen ON (aropen_id=cashrcptitem_aropen_id)
                                       JOIN cohist ON (cohist_invcnumber=aropen_docnumber AND cohist_doctype=aropen_doctype)
                                       JOIN cohisttax ON (taxhist_parent_id=cohist_id)
                          WHERE (gltrans_id=_r.bankrecitem_source_id)
                        -- Cash receipt, sltrans
                        UNION
                        SELECT taxhist_id, aropen_id AS applyid, sltrans_date AS distdate, taxhist_tax,
                               (cashrcptitem_amount / aropen_amount) AS percentpaid
                          FROM sltrans JOIN cashrcpt  ON ((sltrans_source='A/R')
                                                      AND (sltrans_doctype='CR')
                                                      AND (sltrans_misc_id=cashrcpt_id))
                                       JOIN cashrcptitem ON (cashrcptitem_cashrcpt_id=cashrcpt_id)
                                       JOIN aropen ON (aropen_id=cashrcptitem_aropen_id)
                                       JOIN cohist ON (cohist_invcnumber=aropen_docnumber AND cohist_doctype=aropen_doctype)
                                       JOIN cohisttax ON (taxhist_parent_id=cohist_id)
                          WHERE (sltrans_id=_r.bankrecitem_source_id)
                        -- Cash payment, gltrans
                        UNION
                        SELECT taxhist_id, apopen_id AS applyid, gltrans_date AS distdate, taxhist_tax,
                               (checkitem_amount / apopen_amount) AS percentpaid
                          FROM gltrans JOIN checkhead  ON ((gltrans_source='A/P')
                                                       AND (gltrans_doctype='CK')
                                                       AND (gltrans_misc_id=checkhead_id))
                                       JOIN checkitem ON (checkitem_checkhead_id=checkhead_id)
                                       JOIN apopen ON (apopen_id=checkitem_apopen_id)
                                       JOIN vohead ON (vohead_number=apopen_docnumber)
                                       JOIN voheadtax ON (taxhist_parent_id=vohead_id)
                          WHERE (gltrans_id=_r.bankrecitem_source_id)
                        UNION
                        SELECT taxhist_id, apopen_id AS applyid, gltrans_date AS distdate, taxhist_tax,
                               (checkitem_amount / apopen_amount) AS percentpaid
                          FROM gltrans JOIN checkhead  ON ((gltrans_source='A/P')
                                                       AND (gltrans_doctype='CK')
                                                       AND (gltrans_misc_id=checkhead_id))
                                       JOIN checkitem ON (checkitem_checkhead_id=checkhead_id)
                                       JOIN apopen ON (apopen_id=checkitem_apopen_id)
                                       JOIN vohead ON (vohead_number=apopen_docnumber)
                                       JOIN voitem ON (voitem_vohead_id=vohead_id)
                                       JOIN voitemtax ON (taxhist_parent_id=voitem_id)
                          WHERE (gltrans_id=_r.bankrecitem_source_id)
                        -- Cash payment, sltrans
                        UNION
                        SELECT taxhist_id, apopen_id AS applyid, sltrans_date AS distdate, taxhist_tax,
                               (checkitem_amount / apopen_amount) AS percentpaid
                          FROM sltrans JOIN checkhead  ON ((sltrans_source='A/P')
                                                       AND (sltrans_doctype='CK')
                                                       AND (sltrans_misc_id=checkhead_id))
                                       JOIN checkitem ON (checkitem_checkhead_id=checkhead_id)
                                       JOIN apopen ON (apopen_id=checkitem_apopen_id)
                                       JOIN vohead ON (vohead_number=apopen_docnumber)
                                       JOIN voheadtax ON (taxhist_parent_id=vohead_id)
                          WHERE (sltrans_id=_r.bankrecitem_source_id)
                        UNION
                        SELECT taxhist_id, apopen_id AS applyid, sltrans_date AS distdate, taxhist_tax,
                               (checkitem_amount / apopen_amount) AS percentpaid
                          FROM sltrans JOIN checkhead  ON ((sltrans_source='A/P')
                                                       AND (sltrans_doctype='CK')
                                                       AND (sltrans_misc_id=checkhead_id))
                                       JOIN checkitem ON (checkitem_checkhead_id=checkhead_id)
                                       JOIN apopen ON (apopen_id=checkitem_apopen_id)
                                       JOIN vohead ON (vohead_number=apopen_docnumber)
                                       JOIN voitem ON (voitem_vohead_id=vohead_id)
                                       JOIN voitemtax ON (taxhist_parent_id=voitem_id)
                          WHERE (sltrans_id=_r.bankrecitem_source_id)
                       ) AS data
      LOOP
        IF _post THEN
          INSERT INTO taxpay
          ( taxpay_taxhist_id, taxpay_apply_id, taxpay_distdate, taxpay_tax )
          VALUES
          ( _tax.taxhist_id, _tax.applyid, COALESCE(_r.bankrecitem_effdate, _tax.distdate), _tax.taxpaid );
        ELSE
          DELETE FROM taxpay
          WHERE ((taxpay_taxhist_id=_tax.taxhist_id)
             AND (taxpay_apply_id=_tax.applyid)
             AND (taxpay_distdate=COALESCE(_r.bankrecitem_effdate, _tax.distdate))
             AND (taxpay_tax=_tax.taxpaid));
        END IF;
      END LOOP;

    END LOOP;

    SELECT postGLSeries(_sequence, fetchJournalNumber('GL-MISC')) INTO _result;
    IF (_result < 0) THEN
      RAISE EXCEPTION 'postGLSeries failed, result=%', _result;
    END IF;

  END IF;

  UPDATE gltrans
     SET gltrans_rec = _post
   WHERE ( (gltrans_id IN (SELECT bankrecitem_source_id
                             FROM bankrecitem
                            WHERE ((bankrecitem_source = 'GL')
                              AND  (bankrecitem_cleared)
                              AND  (bankrecitem_bankrec_id=pBankrecid) ) ) )
     AND   (gltrans_accnt_id=_accntid) ) ;

  UPDATE sltrans
     SET sltrans_rec = _post
   WHERE ( (sltrans_id IN (SELECT bankrecitem_source_id
                             FROM bankrecitem
                            WHERE ((bankrecitem_source = 'SL')
                              AND  (bankrecitem_cleared)
                              AND  (bankrecitem_bankrec_id=pBankrecid) ) ) )
     AND   (sltrans_accnt_id=_accntid) ) ;

  UPDATE bankrec SET 
    bankrec_posted = _post,
    bankrec_postdate = CASE _post WHEN TRUE THEN now() ELSE NULL END
   WHERE (bankrec_id=pBankrecid);

  RETURN pBankrecid;
END;
$$;


ALTER FUNCTION public.bankreconciliation(pbankrecid integer, ptask text) OWNER TO admin;

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

CREATE FUNCTION bomcontains(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
  pparentitemid	ALIAS FOR $1;
  pchilditemid	ALIAS FOR $2;
  _bomworksetid	INTEGER;
  _result	BOOLEAN;

BEGIN
  _bomworksetid := indentedWhereUsed(pchilditemid);
  _result := EXISTS(SELECT bomwork_id
		    FROM bomwork
		    WHERE ((bomwork_set_id=_bomworksetid)
		      AND  (bomwork_item_id=pparentitemid) ));

  PERFORM deleteBOMWorkset(_bomworksetid);

  RETURN _result;
END;
$_$;


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

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

CREATE FUNCTION bomhistsequence(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
  pHistid ALIAS FOR $1;
  _wid INTEGER;
  _seqnum TEXT;
  _bomhist RECORD;

BEGIN
  _wid := pHistid;

  SELECT bomhist_parent_id AS parent,
         to_char(bomhist_seqnumber, '00009') AS seq INTO _bomhist
    FROM bomhist
   WHERE bomhist_seq_id=_wid;

  IF (FOUND) THEN
    _seqnum := _bomhist.seq;
    _wid := _bomhist.parent;

    WHILE (_wid != -1) LOOP
      SELECT bomhist_parent_id AS parent,
             to_char(bomhist_seqnumber, '00009') AS seq INTO _bomhist
      FROM bomhist
      WHERE bomhist_seq_id=_wid;

      IF (FOUND) THEN
        _seqnum := _bomhist.seq || '-' || _seqnum;
        _wid    := _bomhist.parent;
      ELSE
        _wid := -1;
      END IF;
    END LOOP;
  ELSE
    _seqnum := ''::TEXT;
  END IF;

  RETURN _seqnum;
END;
$_$;


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

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

CREATE TABLE bomitem (
    bomitem_id integer DEFAULT nextval(('bomitem_bomitem_id_seq'::text)::regclass) NOT NULL,
    bomitem_parent_item_id integer NOT NULL,
    bomitem_seqnumber integer,
    bomitem_item_id integer NOT NULL,
    bomitem_qtyper numeric(20,8) NOT NULL,
    bomitem_scrap numeric(8,4) NOT NULL,
    bomitem_status character(1),
    bomitem_effective date NOT NULL,
    bomitem_expires date NOT NULL,
    bomitem_createwo boolean NOT NULL,
    bomitem_issuemethod character(1) NOT NULL,
    bomitem_schedatwooper boolean NOT NULL,
    bomitem_ecn text,
    bomitem_moddate date,
    bomitem_subtype character(1) NOT NULL,
    bomitem_uom_id integer NOT NULL,
    bomitem_rev_id integer DEFAULT (-1),
    bomitem_booitem_seq_id integer DEFAULT (-1),
    bomitem_char_id integer,
    bomitem_value text,
    bomitem_notes text,
    bomitem_ref text,
    bomitem_qtyfxd numeric(20,8) DEFAULT 0 NOT NULL,
    bomitem_issuewo boolean DEFAULT false NOT NULL,
    CONSTRAINT bomitem_bomitem_issuemethod_check CHECK ((((bomitem_issuemethod = 'M'::bpchar) OR (bomitem_issuemethod = 'S'::bpchar)) OR (bomitem_issuemethod = 'L'::bpchar))),
    CONSTRAINT bomitem_bomitem_subtype_check CHECK ((((bomitem_subtype = 'N'::bpchar) OR (bomitem_subtype = 'I'::bpchar)) OR (bomitem_subtype = 'B'::bpchar)))
);


ALTER TABLE bomitem OWNER TO admin;

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

COMMENT ON TABLE bomitem IS 'Bill of Materials (BOM) component Items information';


--
-- Name: COLUMN bomitem.bomitem_qtyfxd; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN bomitem.bomitem_qtyfxd IS 'The fixed quantity required';


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

CREATE FUNCTION bomitem(integer) RETURNS SETOF bomitem
    LANGUAGE sql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT * FROM bomitem WHERE ((bomitem_parent_item_id=$1) AND (bomitem_rev_id=getActiveRevId('BOM',$1)));
$_$;


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

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

CREATE FUNCTION bomitem(integer, integer) RETURNS SETOF bomitem
    LANGUAGE sql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT * FROM bomitem WHERE ((bomitem_parent_item_id=$1) AND (bomitem_rev_id=$2));
$_$;


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

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

CREATE FUNCTION bomlevelbyitem(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;
  _cnt INTEGER;
  _result INTEGER;
  _bomitem RECORD;

BEGIN
  _cnt := 0;

  BEGIN
  FOR _bomitem IN SELECT bomitem_parent_item_id
                    FROM bomitem
                   WHERE ((bomitem_item_id=pItemid)
                     AND  (bomitem_rev_id=getActiveRevId('BOM',bomitem_parent_item_id))
                     AND  (CURRENT_DATE BETWEEN bomitem_effective AND (bomitem_expires - 1)))
  LOOP
    SELECT bomLevelByItem(_bomitem.bomitem_parent_item_id) + 1 INTO _result;
    IF (_result > _cnt) THEN
      _cnt := _result;
    END IF;
  END LOOP;
  EXCEPTION WHEN statement_too_complex THEN
      RAISE EXCEPTION 'potential recursive BOM found for item_id %', pItemid;
  END;

  return _cnt;
END;
$_$;


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

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

CREATE FUNCTION bomlevelbyitem(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;
  pBomrevid ALIAS FOR $2;
  _cnt INTEGER;
  _result INTEGER;
  _bomitem RECORD;

BEGIN
  _cnt := 0;

  BEGIN
  FOR _bomitem IN SELECT bomitem_parent_item_id
                    FROM bomitem
                   WHERE ((bomitem_item_id=pItemid)
                     AND  (bomitem_rev_id=pBomrevid)
                     AND  (CURRENT_DATE BETWEEN bomitem_effective AND (bomitem_expires - 1)))
  LOOP
    SELECT bomLevelByItem(_bomitem.bomitem_parent_item_id, pBomrevid) + 1 INTO _result;
    IF (_result > _cnt) THEN
      _cnt := _result;
    END IF;
  END LOOP;
  EXCEPTION WHEN statement_too_complex THEN
      RAISE EXCEPTION 'potential recursive BOM found for item_id %', pItemid;
  END;

  return _cnt;
END;
$_$;


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

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

CREATE FUNCTION bomworkeffective(integer, date) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
    workid ALIAS FOR $1;
    effdate ALIAS FOR $2;
    _wid INTEGER;
    _bomwork RECORD;
BEGIN
    _wid := workid;
    WHILE (_wid != -1) LOOP
        SELECT bomwork_parent_id AS parent,
               bomwork_effective AS effective
          INTO _bomwork
          FROM bomwork
         WHERE bomwork_id=_wid;

         IF (FOUND) THEN
             _wid := _bomwork.parent;
             IF (_bomwork.effective > effdate) THEN
                 RETURN FALSE;
             END IF;
         ELSE
             _wid := -1;
         END IF;
    END LOOP;
    RETURN TRUE;
END;
$_$;


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

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

CREATE FUNCTION bomworkexpired(integer, date) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
    workid ALIAS FOR $1;
    expdate ALIAS FOR $2;
    _wid INTEGER;
    _bomwork RECORD;
BEGIN
    _wid := workid;
    WHILE (_wid != -1) LOOP
        SELECT bomwork_parent_id AS parent,
               bomwork_expires AS expires
          INTO _bomwork
          FROM bomwork
         WHERE bomwork_id=_wid;

         IF (FOUND) THEN
             _wid := _bomwork.parent;
             IF (_bomwork.expires <= expdate) THEN
                 RETURN TRUE;
             END IF;
         ELSE
             _wid := -1;
         END IF;
    END LOOP;
    RETURN FALSE;
END;
$_$;


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

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

CREATE FUNCTION bomworkitemsequence(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
  pWorkid ALIAS FOR $1;
  _wid INTEGER;
  _seqnum TEXT;
  _bomwork RECORD;

BEGIN
  _wid := pWorkid;

  SELECT bomwork_parent_id AS parent,
         item_number AS seq INTO _bomwork
    FROM bomwork, item
   WHERE ((bomwork_id=_wid)
   AND (bomwork_item_id=item_id));

  IF (FOUND) THEN
    _seqnum := _bomwork.seq;
    _wid := _bomwork.parent;

    WHILE (_wid != -1) LOOP
      SELECT bomwork_parent_id AS parent,
             item_number AS seq INTO _bomwork
      FROM bomwork, item
      WHERE ((bomwork_id=_wid)
      AND (bomwork_item_id=item_id));

      IF (FOUND) THEN
        _seqnum := _bomwork.seq || '-' || _seqnum;
        _wid    := _bomwork.parent;
      ELSE
        _wid := -1;
      END IF;
    END LOOP;
  ELSE
    _seqnum := ''::TEXT;
  END IF;

  RETURN _seqnum;
END;
$_$;


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

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

CREATE FUNCTION bomworksequence(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
  pWorkid ALIAS FOR $1;
  _wid INTEGER;
  _seqnum TEXT;
  _bomwork RECORD;

BEGIN
  _wid := pWorkid;

  SELECT bomwork_parent_id AS parent,
         to_char(bomwork_seqnumber, '00009') AS seq INTO _bomwork
    FROM bomwork
   WHERE bomwork_id=_wid;

  IF (FOUND) THEN
    _seqnum := _bomwork.seq;
    _wid := _bomwork.parent;

    WHILE (_wid != -1) LOOP
      SELECT bomwork_parent_id AS parent,
             to_char(bomwork_seqnumber, '00009') AS seq INTO _bomwork
      FROM bomwork
      WHERE bomwork_id=_wid;

      IF (FOUND) THEN
        _seqnum := _bomwork.seq || '-' || _seqnum;
        _wid    := _bomwork.parent;
      ELSE
        _wid := -1;
      END IF;
    END LOOP;
  ELSE
    _seqnum := ''::TEXT;
  END IF;

  RETURN _seqnum;
END;
$_$;


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

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

CREATE FUNCTION buildinvbal(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;
  _r RECORD;
  _prevCostmethod TEXT := 'A';
  _prevDate TIMESTAMP WITH TIME ZONE;
  _runningQty NUMERIC := 0;
  _runningNn NUMERIC := 0;

BEGIN
  -- Validate
  IF (SELECT (count(invhist_id) > 0)
      FROM invhist
      WHERE ((invhist_itemsite_id=pItemsiteId)
      AND (NOT invhist_posted))) THEN

    SELECT item_number, warehous_code INTO _r
    FROM itemsite
      JOIN item ON (item_id=itemsite_item_id)
      JOIN whsinfo ON (itemsite_warehous_id=warehous_id)
    WHERE (itemsite_id=pItemsiteId);

    RAISE EXCEPTION 'Unposted inventory transactions exist for % at % [xtuple: buildInvBal, -1, %, %]',
                    _r.item_number, _r.warehous_code,
                    _r.item_number, _r.warehous_code;
  END IF;

  -- Remove any old records
  DELETE FROM invbal WHERE invbal_itemsite_id=pItemsiteId;

  FOR _r IN
    SELECT invhist.*,
      itemsite_item_id, invhistSense(invhist_id) AS sense,
      item_number, warehous_code
    FROM invhist
      JOIN itemsite ON (itemsite_id=invhist_itemsite_id)
      JOIN item ON (itemsite_item_id=item_id)
      JOIN whsinfo ON (itemsite_warehous_id=warehous_id)
    WHERE (invhist_itemsite_id=pItemsiteId)
    ORDER BY invhist_created, invhist_id
  LOOP
    --RAISE NOTICE 'Calculating balances for Item % at Site % against transaction %, transtype %, sense %, qty %, %', _r.item_number, _r.warehous_code, _r.invhist_id, _r.invhist_transtype, _r.sense, _r.invhist_invqty, _r.invhist_comments;
    -- Update balances changed by any standard cost update between transactions
    IF (_prevCostmethod = 'S' AND _runningQty != 0) THEN
      PERFORM postValueintoInvBalance(pItemsiteid, costhist_date::date, _runningQty, _runningNn, costhist_oldcost, costhist_newcost )
      FROM costhist
      WHERE ((costhist_item_id=_r.itemsite_item_id)
        AND (costhist_date BETWEEN _prevDate AND _r.invhist_created)
        AND (costhist_type IN ('S','D')));
    END IF;

    -- Post transaction into inventory balance table
    PERFORM postIntoInvBalance(_r.invhist_id);

    _prevDate := _r.invhist_created;
    _prevCostmethod := _r.invhist_costmethod;
    _runningQty := _runningQty + _r.invhist_invqty * _r.sense;
    IF (_r.invhist_transtype = 'NN') THEN
      _runningNn := _runningNn + _r.invhist_invqty * -1;
    END IF;

  END LOOP;

  -- Update balances changed by any standard cost since last transaction
  IF (_prevCostmethod = 'S' AND _runningQty != 0) THEN
    PERFORM postValueintoInvBalance(pItemsiteid, costhist_date::date, _runningQty, _runningNn, costhist_oldcost, costhist_newcost )
    FROM costhist
    WHERE ((costhist_item_id=_r.itemsite_item_id)
      AND (costhist_date > _prevDate)
      AND (costhist_type IN ('S','D')));
  END IF;

  -- Forward update changes through all the balances
  PERFORM forwardupdateitemsite(pItemsiteId);

  RETURN 1;
END;
$_$;


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

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

CREATE FUNCTION buildsearchpath() RETURNS text
    LANGUAGE plpgsql
    AS $_$
DECLARE
  _path   TEXT    := '';
  _schema TEXT;
  _seq    INTEGER;
BEGIN
  -- get the schemas as ordered by the administrator
  SELECT concatagg(quote_ident(schemaord_name) || ',') INTO _path
    FROM (SELECT schemaord_name
            FROM schemaord
            LEFT OUTER JOIN pkghead ON (schemaord_name=pkghead_name)
           WHERE (pkghead_id IS NULL
               OR (pkghead_id IS NOT NULL AND packageisenabled(pkghead_id)))
           ORDER BY schemaord_order
         ) AS xtspq;

  -- add others that we think/know we need
  -- TODO: is there a reason not to include public, api, or packages?
  FOR _schema, _seq IN
      SELECT pkghead_name AS schema, 0 AS seq
        FROM pkghead
       WHERE packageisenabled(pkghead_id)
      UNION ALL
      SELECT 'public', 1
      UNION ALL
      SELECT 'api', 2
      ORDER BY seq, schema
  LOOP
    IF (_path !~* (E'(^|\\W)' || _schema || E'(\\W|$)')) THEN
      _path := _path || ',' || quote_ident(_schema);
    END IF;
  END LOOP;

  -- remove extraneous spaces and commas
  _path = BTRIM(REGEXP_REPLACE(_path, '( ?, ?)+', ',', 'g'),
                ', ');

  RAISE DEBUG 'buildSearchPath() returning %', _path;

  RETURN _path;
END;
$_$;


ALTER FUNCTION public.buildsearchpath() OWNER TO admin;

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

COMMENT ON FUNCTION buildsearchpath() IS 'buildSearchPath() examines the schemaord and pkghead tables to build a search
path string. It ensures that public, api, and all enabled packages are included
even if they are not listed in the schemaord table.
It returns the constructed search_path but does not set it.';


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

CREATE FUNCTION calccashbudget(integer, integer, character) RETURNS numeric
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pAccntId ALIAS FOR $1;
  pPeriodId ALIAS FOR $2;
  pInterval ALIAS FOR $3;
  _accntType CHAR;
  _currentBudget NUMERIC;
  _priorBudget NUMERIC;
  _result NUMERIC;
BEGIN

        SELECT accnt_type INTO _accntType
        FROM accnt
        WHERE (accnt_id=pAccntId);

        SELECT COALESCE(SUM(budget_amount),0) INTO _currentBudget
        FROM budget
        WHERE ((budget_accnt_id=pAccntId)
        AND (budget_period_id=pPeriodId));

        IF (pInterval='M') THEN
        SELECT (COALESCE(SUM(budget_amount),0)) INTO _priorBudget
                FROM budget,
                (SELECT COALESCE(pp.period_id,-1) AS prior_period_id
                        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) AS data
                WHERE ((budget_accnt_id=pAccntId)
                AND (budget_period_id=prior_period_id));

                ELSE IF (pInterval='Q') THEN
                        SELECT (COALESCE(SUM(budget_amount),0)) INTO _priorBudget
                        FROM budget,
                                (SELECT COALESCE(pp.period_id,-1) AS prior_period_id
                                FROM period cp, period pp
                                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'))
                                ORDER BY pp.period_start DESC LIMIT 1) AS data
                        WHERE ((budget_accnt_id=pAccntId)
                        AND (budget_period_id=prior_period_id));


                ELSE
                        SELECT (COALESCE(SUM(budget_amount),0)) INTO _priorBudget
                        FROM budget,
                                (SELECT pp.period_id AS prior_period_id
                        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 (cy.yearperiod_start > py.yearperiod_start))
                        ORDER BY pp.period_start DESC LIMIT 1) AS data
                        WHERE ((budget_accnt_id=pAccntId)
                        AND (budget_period_id=prior_period_id));

                END IF;
        END IF;

        IF _accntType='A' THEN
                _result := ((_priorBudget-_currentBudget) * -1 );

        ELSE IF (_accntType IN ('L','Q')) THEN
                _result := ((_priorBudget-_currentBudget) *-1);

        ELSE RETURN -1;
        END IF;
  END IF;


  RETURN _result;


END;
$_$;


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

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

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

BEGIN

  SELECT SUM(COALESCE(extprice, 0)) INTO _amount
  FROM cmhead JOIN creditmemoitem ON (cmhead_id=cmitem_cmhead_id)
  WHERE (cmhead_id=pCmheadid);

  RETURN _amount;

END;
$_$;


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

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

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

BEGIN

  SELECT COALESCE(SUM(taxhist_tax), 0) INTO _headamount
  FROM cmhead JOIN cmheadtax ON (taxhist_parent_id=cmhead_id)
  WHERE (cmhead_id=pCmheadid);

  SELECT SUM(COALESCE(tax, 0)) INTO _itemamount
  FROM cmhead JOIN creditmemoitem ON (cmhead_id=cmitem_cmhead_id)
  WHERE (cmhead_id=pCmheadid);

  _amount := _headamount + _itemamount;
  RETURN (_amount * -1.0);

END;
$_$;


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

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

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

BEGIN

  SELECT COALESCE(round((cobill_qty * coitem_qty_invuomratio) *
                        (coitem_price / coitem_price_invuomratio), 2), 0) INTO _amount
  FROM cobill JOIN coitem ON (coitem_id=cobill_coitem_id)
  WHERE (cobill_id=pCobillid);

  RETURN _amount;

END;
$_$;


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

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

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

BEGIN

  SELECT COALESCE(calculateTax(cobmisc_taxzone_id,
                               cobill_taxtype_id,
                               cobmisc_shipdate,
                               cobmisc_curr_id,
                               calcCobillAmt(cobill_id)), 0) INTO _amount
  FROM cobill JOIN coitem ON (coitem_id=cobill_coitem_id)
              JOIN cobmisc ON (cobmisc_id=cobill_cobmisc_id)
  WHERE (cobill_id=pCobillid);

  RETURN _amount;

END;
$_$;


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

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

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

BEGIN

  SELECT SUM(COALESCE(calcCobillAmt(cobill_id), 0)) INTO _amount
  FROM cobmisc JOIN cobill ON (cobmisc_id=cobill_cobmisc_id)
  WHERE (cobmisc_id=pCobmiscid);

  RETURN _amount;

END;
$_$;


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

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

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

BEGIN

  SELECT SUM(
         COALESCE(calculateTax(cobmisc_taxzone_id,
                               cobill_taxtype_id,
                               cobmisc_shipdate,
                               cobmisc_curr_id,
                               COALESCE(round((cobill_qty * coitem_qty_invuomratio) * (coitem_price / coitem_price_invuomratio), 2), 0))
                 , 0)
            ) INTO _amount
  FROM cobmisc JOIN cobill ON (cobmisc_id=cobill_cobmisc_id)
               JOIN coitem ON (coitem_id=cobill_coitem_id)
  WHERE (cobmisc_id=pCobmiscid);

  RETURN _amount;

END;
$_$;


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

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

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

  RETURN calcInvoiceAmt(pInvcheadid, 'T');

END;
$$;


ALTER FUNCTION public.calcinvoiceamt(pinvcheadid integer) OWNER TO admin;

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

CREATE FUNCTION calcinvoiceamt(pinvcheadid integer, ptype text) RETURNS numeric
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2015 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _subtotal NUMERIC := 0.0;
  _cost NUMERIC := 0.0;
  _tax NUMERIC := 0.0;
  _freight NUMERIC := 0.0;
  _misc NUMERIC := 0.0;
  _amount NUMERIC := 0.0;

BEGIN

  -- pType: S = line item subtotal
  --        T = total
  --        X = tax
  --        M = margin

  SELECT COALESCE(SUM(ROUND((invcitem_billed * invcitem_qty_invuomratio) *
                            (invcitem_price / COALESCE(invcitem_price_invuomratio, 1.0)), 2)), 0.0),
         COALESCE(SUM(ROUND((invcitem_billed * invcitem_qty_invuomratio) *
                            (currtolocal(invchead_curr_id, COALESCE(coitem_unitcost, itemCost(itemsite_id), 0.0), invchead_invcdate)
                             / COALESCE(coitem_price_invuomratio, 1.0)), 2)), 0.0)
         INTO _subtotal, _cost
  FROM invcitem 
    JOIN invchead ON (invchead_id = invcitem_invchead_id)
    LEFT OUTER JOIN coitem ON (coitem_id=invcitem_coitem_id)
    LEFT OUTER JOIN itemsite ON (itemsite_item_id=invcitem_item_id AND
                                 itemsite_warehous_id=invcitem_warehous_id)
  WHERE (invcitem_invchead_id=pInvcheadid);

  IF (pType IN ('T', 'X')) THEN
    SELECT COALESCE(SUM(tax), 0.0) INTO _tax
    FROM (SELECT COALESCE(ROUND(SUM(taxdetail_tax), 2), 0.0) AS tax
          FROM tax
               JOIN calculateTaxDetailSummary('I', pInvcheadid, 'T')ON (taxdetail_tax_id=tax_id)
          GROUP BY tax_id) AS data;
  END IF;

  IF (pType = 'T') THEN
    SELECT COALESCE(invchead_freight, 0), COALESCE(invchead_misc_amount, 0)
           INTO _freight, _misc
    FROM invchead
    WHERE (invchead_id=pinvcheadid);
  END IF;

  _amount := CASE pType WHEN 'S' THEN (_subtotal)
                        WHEN 'T' THEN (_subtotal + _tax + _freight + _misc)
                        WHEN 'X' THEN (_tax)
                        WHEN 'M' THEN (_subtotal - _cost)
                        ELSE 0.0
             END;

  RETURN _amount;

END;
$$;


ALTER FUNCTION public.calcinvoiceamt(pinvcheadid integer, ptype text) OWNER TO admin;

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

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

BEGIN
  SELECT aropen_curr_id,
    (CASE WHEN aropen_doctype IN ('I','D') THEN 1 ELSE -1 END) 
    INTO _aropencurrid, _sense
  FROM aropen
  WHERE (aropen_id=paropenid);

  SELECT SUM(currToCurr(cashrcpt_curr_id, _aropencurrid,
                        cashrcptitem_amount + cashrcptitem_discount, coalesce(cashrcpt_applydate, cashrcpt_distdate))) * _sense INTO _cashrcptsum
  FROM cashrcptitem, cashrcpt
  WHERE ((cashrcptitem_cashrcpt_id=cashrcpt_id)
    AND  (NOT cashrcpt_posted)
    AND  (NOT cashrcpt_void)
    AND  (cashrcptitem_aropen_id=paropenid)
    );

  SELECT SUM(currToCurr(arcreditapply_curr_id, _aropencurrid,
                        arcreditapply_amount, CURRENT_DATE)) INTO _arcreditsum
  FROM arcreditapply
  WHERE ((arcreditapply_target_aropen_id=paropenid)
    );

  RETURN round(COALESCE(_cashrcptsum, 0) + COALESCE(_arcreditsum, 0),2);
END;
$_$;


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

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

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

  RETURN calcPurchaseOrderAmt(pPoheadid, 'T');

END;
$$;


ALTER FUNCTION public.calcpurchaseorderamt(ppoheadid integer) OWNER TO admin;

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

CREATE FUNCTION calcpurchaseorderamt(ppoheadid integer, ptype text) RETURNS numeric
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _subtotal NUMERIC := 0;
  _freightsub NUMERIC := 0;
  _tax NUMERIC := 0;
  _freight NUMERIC := 0;
  _currid INTEGER := 0;
  _effdate DATE := CURRENT_DATE;
  _amount NUMERIC := 0;

BEGIN

  -- pType: S = line item subtotal
  --        T = total
  --        X = tax

  SELECT COALESCE(SUM(ROUND(poitem_qty_ordered * poitem_unitprice, 2)), 0),
         COALESCE(SUM(ROUND(poitem_freight, 2)), 0)
         INTO _subtotal, _freightsub
  FROM poitem
  WHERE (poitem_pohead_id=pPoheadid);

  SELECT COALESCE(SUM(tax), 0) INTO _tax
  FROM ( SELECT COALESCE(ROUND(SUM(taxdetail_tax), 2), 0.0) AS tax
         FROM tax JOIN calculateTaxDetailSummary('PO', pPoheadid, 'T') ON (taxdetail_tax_id=tax_id)
         GROUP BY tax_id ) AS data;

  SELECT COALESCE(pohead_freight, 0), pohead_curr_id, pohead_orderdate
         INTO _freight, _currid, _effdate
  FROM pohead
  WHERE (pohead_id=pPoheadid);

  _amount := currToBase(_currid,
                        CASE pType WHEN 'S' THEN (_subtotal)
                                   WHEN 'T' THEN (_subtotal + _tax + _freight + _freightsub)
                                   WHEN 'X' THEN (_tax)
                                   ELSE 0.0
                        END,
                        _effdate);

  RETURN _amount;

END;
$$;


ALTER FUNCTION public.calcpurchaseorderamt(ppoheadid integer, ptype text) OWNER TO admin;

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

CREATE FUNCTION calcpurchaseorderduedate(ppoheadid 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
  _result DATE;
BEGIN

  SELECT MIN(poitem_duedate) INTO _result
  FROM poitem
  WHERE (poitem_pohead_id=pPoheadid);

  RETURN _result;

END;
$$;


ALTER FUNCTION public.calcpurchaseorderduedate(ppoheadid integer) OWNER TO admin;

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

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

  RETURN calcQuoteAmt(pQuheadid, 'T');

END;
$$;


ALTER FUNCTION public.calcquoteamt(pquheadid integer) OWNER TO admin;

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

CREATE FUNCTION calcquoteamt(pquheadid integer, ptype text) RETURNS numeric
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _subtotal NUMERIC := 0.0;
  _cost NUMERIC := 0.0;
  _tax NUMERIC := 0.0;
  _freight NUMERIC := 0.0;
  _misc NUMERIC := 0.0;
  _amount NUMERIC := 0.0;

BEGIN

  -- pType: S = line item subtotal
  --        T = total
  --        X = tax
  --        M = margin

  SELECT COALESCE(SUM(ROUND((quitem_qtyord * quitem_qty_invuomratio) *
                            (quitem_price / quitem_price_invuomratio), 2)), 0.0),
         COALESCE(SUM(ROUND((quitem_qtyord * quitem_qty_invuomratio) *
                            (quitem_unitcost / quitem_price_invuomratio), 2)), 0.0)
         INTO _subtotal, _cost
  FROM quitem
  WHERE (quitem_quhead_id=pQuheadid);

  IF (pType IN ('T', 'X')) THEN
    SELECT COALESCE(SUM(tax), 0.0) INTO _tax
    FROM (SELECT COALESCE(ROUND(SUM(taxdetail_tax), 2), 0.0) AS tax
          FROM tax
               JOIN calculateTaxDetailSummary('Q', pQuheadid, 'T')ON (taxdetail_tax_id=tax_id)
          GROUP BY tax_id) AS data;
  END IF;

  IF (pType = 'T') THEN
    SELECT COALESCE(quhead_freight, 0), COALESCE(quhead_misc, 0) INTO _freight, _misc
    FROM quhead
    WHERE (quhead_id=pQuheadid);
  END IF;

  _amount := CASE pType WHEN 'S' THEN (_subtotal)
                        WHEN 'T' THEN (_subtotal + _tax + _freight + _misc)
                        WHEN 'X' THEN (_tax)
                        WHEN 'M' THEN (_subtotal - _cost)
                        ELSE 0.0
             END;

  RETURN _amount;

END;
$$;


ALTER FUNCTION public.calcquoteamt(pquheadid integer, ptype text) OWNER TO admin;

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

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

  RETURN calcSalesOrderAmt(pCoheadid, 'T');

END;
$$;


ALTER FUNCTION public.calcsalesorderamt(pcoheadid integer) OWNER TO admin;

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

CREATE FUNCTION calcsalesorderamt(pcoheadid integer, ptype text) RETURNS numeric
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _subtotal NUMERIC := 0.0;
  _cost NUMERIC := 0.0;
  _tax NUMERIC := 0.0;
  _freight NUMERIC := 0.0;
  _misc NUMERIC := 0.0;
  _credit NUMERIC := 0.0;
  _amount NUMERIC := 0.0;

BEGIN

  -- pType: S = line item subtotal
  --        T = total
  --        B = balance due
  --        C = allocated credits
  --        X = tax
  --        M = margin

  SELECT COALESCE(SUM(ROUND((coitem_qtyord * coitem_qty_invuomratio) *
                            (coitem_price / coitem_price_invuomratio), 2)), 0.0),
         COALESCE(SUM(ROUND((coitem_qtyord * coitem_qty_invuomratio) *
                            (CASE WHEN (coitem_subnumber > 0) THEN 0.0 ELSE coitem_unitcost END
                             / coitem_price_invuomratio), 2)), 0.0)
         INTO _subtotal, _cost
  FROM coitem
  WHERE (coitem_cohead_id=pCoheadid)
    AND (coitem_status != 'X');

  IF (pType IN ('T', 'B', 'X')) THEN
    SELECT COALESCE(SUM(tax), 0.0) INTO _tax
    FROM (SELECT COALESCE(ROUND(SUM(taxdetail_tax), 2), 0.0) AS tax
          FROM tax
               JOIN calculateTaxDetailSummary('S', pCoheadid, 'T')ON (taxdetail_tax_id=tax_id)
          GROUP BY tax_id) AS data;
  END IF;

  IF (pType IN ('T', 'B', 'C')) THEN
    SELECT COALESCE(cohead_freight, 0), COALESCE(cohead_misc, 0),
           COALESCE(SUM(currToCurr(aropenalloc_curr_id, cohead_curr_id,
                                   aropenalloc_amount, CURRENT_DATE)),0)
           INTO _freight, _misc, _credit
    FROM cohead
         LEFT OUTER JOIN aropenalloc ON (aropenalloc_doctype='S' AND aropenalloc_doc_id=cohead_id)
    WHERE (cohead_id=pCoheadid)
    GROUP BY cohead_freight, cohead_misc, cohead_curr_id;
  END IF;

  _amount := CASE pType WHEN 'S' THEN (_subtotal)
                        WHEN 'T' THEN (_subtotal + _tax + _freight + _misc)
                        WHEN 'B' THEN (_subtotal + _tax + _freight + _misc - _credit)
                        WHEN 'C' THEN (_credit)
                        WHEN 'X' THEN (_tax)
                        WHEN 'M' THEN (_subtotal - _cost)
                        ELSE 0.0
             END;

  RETURN _amount;

END;
$$;


ALTER FUNCTION public.calcsalesorderamt(pcoheadid integer, ptype text) OWNER TO admin;

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

CREATE FUNCTION calcshipfreight(integer) RETURNS numeric
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pShipheadId ALIAS FOR $1;
  _result NUMERIC := 0;
  _order RECORD;
  _shipment RECORD;
  _weights RECORD;
  _price RECORD;
  _sales RECORD;
  _freightid INTEGER;
  _totalprice NUMERIC;
  _includepkgweight BOOLEAN := FALSE;
  _freight RECORD;
  _debug BOOLEAN := false;
BEGIN
  --Get shipment
  SELECT shiphead_order_id, shiphead_order_type, shiphead_freight 
  INTO _shipment
  FROM shiphead
  WHERE (shiphead_id=pShipheadId);

  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Shipment not found';
  END IF;

  IF (_shipment.shiphead_order_type = 'SO') THEN
  --Sales Orders
  
  --Get the order header information
    SELECT cust_id AS cust_id,
           custtype_id,
           custtype_code,
           shipto_id,
           shipto_num,
           cohead_orderdate AS orderdate,
           cohead_shipvia AS shipvia,
           shipto_shipzone_id AS shipzone_id,
           cohead_curr_id AS curr_id,
           currConcat(cohead_curr_id) AS currAbbr,
           cohead_calcfreight,
           cohead_freight
    INTO _order
    FROM cohead 
      JOIN custinfo ON (cust_id=cohead_cust_id)
      JOIN custtype ON (custtype_id=cust_custtype_id)
      LEFT OUTER JOIN shiptoinfo ON (shipto_id=cohead_shipto_id)
    WHERE (cohead_id=_shipment.shiphead_order_id);

    IF (NOT FOUND) THEN
      RAISE EXCEPTION 'Order not found';
    END IF;

    IF (_debug) THEN
      RAISE NOTICE 'cust_id = %', _order.cust_id;
      RAISE NOTICE 'custtype_id = %', _order.custtype_id;
      RAISE NOTICE 'shipto_id = %', _order.shipto_id;
      RAISE NOTICE 'shipto_num = %', _order.shipto_num;
      RAISE NOTICE 'orderdate = %', _order.orderdate;
      RAISE NOTICE 'shipvia = %', _order.shipvia;
      RAISE NOTICE 'shipzone_id = %', _order.shipzone_id;
      RAISE NOTICE 'curr_id = %', _order.curr_id;
      RAISE NOTICE 'currAbbr = %', _order.currAbbr;
      RAISE NOTICE 'calcfreight = %', _order.cohead_calcfreight;
      RAISE NOTICE 'freight = %', _order.cohead_freight;
    END IF;

    IF (NOT _order.cohead_calcfreight) THEN
      SELECT noNeg( _order.cohead_freight -
                    COALESCE((SELECT SUM(shiphead_freight)
                              FROM shiphead
                              WHERE (shiphead_order_id = _shipment.shiphead_order_id)
                                AND (shiphead_shipped='true')), 0) ) INTO _result;
      RETURN _result;
    END IF;

    SELECT fetchMetricBool('IncludePackageWeight') INTO _includepkgweight;

    --Calculate Sales Order freight
    --Get a list of aggregated weights from sites and
    --freight classes used on order lines
    FOR _weights IN
      SELECT CASE WHEN (_includePkgWeight) THEN
                        SUM(shipitem_qty * (item_prodweight + item_packweight))
                  ELSE  SUM(shipitem_qty * item_prodweight)
             END AS weight,
             itemsite_warehous_id, item_freightclass_id
      FROM shiphead
        JOIN shipitem ON (shipitem_shiphead_id=shiphead_id)
        JOIN coitem ON (shipitem_orderitem_id=coitem_id)
        JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
        JOIN item ON (item_id=itemsite_item_id)
      WHERE ( (shiphead_id=pShipheadId)
        AND   (item_freightclass_id IS NOT NULL) )
      GROUP BY itemsite_warehous_id, item_freightclass_id LOOP

    IF (_debug) THEN
      RAISE NOTICE '_weights.weight - %', _weights.weight;
      RAISE NOTICE '_weights.itemsite_warehous_id = %', _weights.itemsite_warehous_id;
      RAISE NOTICE '_weights.item_freightclass_id = %', _weights.item_freightclass_id;
    END IF;

    -- First get a sales price if any so we when we find other prices
    -- we can determine if we want that price or this price.
    --  Check for a Sale Price
    SELECT ipsfreight_id,
           CASE WHEN (ipsfreight_type='F') THEN currToCurr(ipshead_curr_id, _order.curr_id,
                                                           ipsfreight_price, _order.orderdate)
                ELSE currToCurr(ipshead_curr_id, _order.curr_id,
                                (_weights.weight * ipsfreight_price), _order.orderdate)
           END AS price
           INTO _sales
    FROM ipsfreight JOIN ipshead ON (ipshead_id=ipsfreight_ipshead_id)
                    JOIN sale ON (sale_ipshead_id=ipshead_id)
    WHERE ( (ipsfreight_qtybreak <= _weights.weight)
      AND   ((ipsfreight_warehous_id IS NULL) OR (ipsfreight_warehous_id=_weights.itemsite_warehous_id))
      AND   ((ipsfreight_freightclass_id IS NULL) OR (ipsfreight_freightclass_id=_weights.item_freightclass_id))
      AND   ((ipsfreight_shipzone_id IS NULL) OR (ipsfreight_shipzone_id=_order.shipzone_id))
      AND   ((ipsfreight_shipvia IS NULL) OR (ipsfreight_shipvia=_order.shipvia))
      AND   (CURRENT_DATE BETWEEN sale_startdate AND sale_enddate) )
    ORDER BY ipsfreight_qtybreak DESC, price ASC
    LIMIT 1;

    IF (_debug) THEN
      IF (_sales.price IS NOT NULL) THEN
        RAISE NOTICE 'Sales Price found, %', _sales.price;
      END IF;
    END IF;

    --  Check for a Customer Shipto Price
    SELECT ipsfreight_id,
           CASE WHEN (ipsfreight_type='F') THEN currToCurr(ipshead_curr_id, _order.curr_id,
                                                           ipsfreight_price, _order.orderdate)
                ELSE currToCurr(ipshead_curr_id, _order.curr_id,
                                (_weights.weight * ipsfreight_price), _order.orderdate)
           END AS price
           INTO _price
    FROM ipsfreight JOIN ipshead ON (ipshead_id=ipsfreight_ipshead_id)
                    JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
    WHERE ( (ipsfreight_qtybreak <= _weights.weight)
      AND   ((ipsfreight_warehous_id IS NULL) OR (ipsfreight_warehous_id=_weights.itemsite_warehous_id))
      AND   ((ipsfreight_freightclass_id IS NULL) OR (ipsfreight_freightclass_id=_weights.item_freightclass_id))
      AND   ((ipsfreight_shipzone_id IS NULL) OR (ipsfreight_shipzone_id=_order.shipzone_id))
      AND   ((ipsfreight_shipvia IS NULL) OR (ipsfreight_shipvia=_order.shipvia))
      AND   (CURRENT_DATE BETWEEN ipshead_effective AND (ipshead_expires - 1))
      AND   (ipsass_shipto_id != -1)
      AND   (ipsass_shipto_id=_order.shipto_id) )
    ORDER BY ipsfreight_qtybreak DESC, price ASC
    LIMIT 1;

    IF (_debug) THEN
      IF (_price.price IS NOT NULL) THEN
        RAISE NOTICE 'Customer Shipto Price found, %', _price.price;
      END IF;
    END IF;

    IF (_price.price IS NULL) THEN
    --  Check for a Customer Shipto Pattern Price
      SELECT ipsfreight_id,
             CASE WHEN (ipsfreight_type='F') THEN currToCurr(ipshead_curr_id, _order.curr_id,
                                                             ipsfreight_price, _order.orderdate)
                  ELSE currToCurr(ipshead_curr_id, _order.curr_id,
                                  (_weights.weight * ipsfreight_price), _order.orderdate)
             END AS price
             INTO _price
      FROM ipsfreight JOIN ipshead ON (ipshead_id=ipsfreight_ipshead_id)
                      JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
      WHERE ( (ipsfreight_qtybreak <= _weights.weight)
        AND   (CURRENT_DATE BETWEEN ipshead_effective AND (ipshead_expires - 1))
        AND   (ipsass_cust_id=_order.cust_id)
        AND   (COALESCE(LENGTH(ipsass_shipto_pattern), 0) > 0)
        AND   (_order.shipto_num ~ ipsass_shipto_pattern)
        AND   ((ipsfreight_warehous_id IS NULL) OR (ipsfreight_warehous_id=_weights.itemsite_warehous_id))
        AND   ((ipsfreight_freightclass_id IS NULL) OR (ipsfreight_freightclass_id=_weights.item_freightclass_id))
        AND   ((ipsfreight_shipzone_id IS NULL) OR (ipsfreight_shipzone_id=_order.shipzone_id))
        AND   ((ipsfreight_shipvia IS NULL) OR (ipsfreight_shipvia=_order.shipvia)) )
      ORDER BY ipsfreight_qtybreak DESC, price ASC
      LIMIT 1;

      IF (_debug) THEN
        IF (_price.price IS NOT NULL) THEN
          RAISE NOTICE 'Customer Shipto Pattern Price found, %', _price.price;
        END IF;
      END IF;

    END IF;

    IF (_price.price IS NULL) THEN
    --  Check for a Customer Price
      SELECT ipsfreight_id,
             CASE WHEN (ipsfreight_type='F') THEN currToCurr(ipshead_curr_id, _order.curr_id,
                                                             ipsfreight_price, _order.orderdate)
                  ELSE currToCurr(ipshead_curr_id, _order.curr_id,
                                  (_weights.weight * ipsfreight_price), _order.orderdate)
             END AS price
             INTO _price
      FROM ipsfreight JOIN ipshead ON (ipshead_id=ipsfreight_ipshead_id)
                      JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
      WHERE ( (ipsfreight_qtybreak <= _weights.weight)
        AND   ((ipsfreight_warehous_id IS NULL) OR (ipsfreight_warehous_id=_weights.itemsite_warehous_id))
        AND   ((ipsfreight_freightclass_id IS NULL) OR (ipsfreight_freightclass_id=_weights.item_freightclass_id))
        AND   ((ipsfreight_shipzone_id IS NULL) OR (ipsfreight_shipzone_id=_order.shipzone_id))
        AND   ((ipsfreight_shipvia IS NULL) OR (ipsfreight_shipvia=_order.shipvia))
        AND   (CURRENT_DATE BETWEEN ipshead_effective AND (ipshead_expires - 1))
        AND   (ipsass_cust_id=_order.cust_id)
        AND   (COALESCE(LENGTH(ipsass_shipto_pattern), 0) = 0) )
      ORDER BY ipsfreight_qtybreak DESC, price ASC
      LIMIT 1;

      IF (_debug) THEN
        IF (_price.price IS NOT NULL) THEN
          RAISE NOTICE 'Customer Price found, %', _price.price;
        END IF;
      END IF;

    END IF;

    IF (_price.price IS NULL) THEN
    --  Check for a Customer Type Price
      SELECT ipsfreight_id,
             CASE WHEN (ipsfreight_type='F') THEN currToCurr(ipshead_curr_id, _order.curr_id,
                                                             ipsfreight_price, _order.orderdate)
                  ELSE currToCurr(ipshead_curr_id, _order.curr_id,
                                  (_weights.weight * ipsfreight_price), _order.orderdate)
             END AS price
             INTO _price
      FROM ipsfreight JOIN ipshead ON (ipshead_id=ipsfreight_ipshead_id)
                      JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
      WHERE ( (ipsfreight_qtybreak <= _weights.weight)
        AND   ((ipsfreight_warehous_id IS NULL) OR (ipsfreight_warehous_id=_weights.itemsite_warehous_id))
        AND   ((ipsfreight_freightclass_id IS NULL) OR (ipsfreight_freightclass_id=_weights.item_freightclass_id))
        AND   ((ipsfreight_shipzone_id IS NULL) OR (ipsfreight_shipzone_id=_order.shipzone_id))
        AND   ((ipsfreight_shipvia IS NULL) OR (ipsfreight_shipvia=_order.shipvia))
        AND   (CURRENT_DATE BETWEEN ipshead_effective AND (ipshead_expires - 1))
        AND   (ipsass_custtype_id=_order.custtype_id) )
      ORDER BY ipsfreight_qtybreak DESC, price ASC
      LIMIT 1;

      IF (_debug) THEN
        IF (_price.price IS NOT NULL) THEN
          RAISE NOTICE 'Customer Type Price found, %', _price.price;
        END IF;
      END IF;

    END IF;

    IF (_price.price IS NULL) THEN
    --  Check for a Customer Type Pattern Price
      SELECT ipsfreight_id,
             CASE WHEN (ipsfreight_type='F') THEN currToCurr(ipshead_curr_id, _order.curr_id,
                                                             ipsfreight_price, _order.orderdate)
                  ELSE currToCurr(ipshead_curr_id, _order.curr_id,
                                  (_weights.weight * ipsfreight_price), _order.orderdate)
             END AS price
             INTO _price
      FROM ipsfreight JOIN ipshead ON (ipshead_id=ipsfreight_ipshead_id)
                      JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
      WHERE ( (ipsfreight_qtybreak <= _weights.weight)
        AND   ((ipsfreight_warehous_id IS NULL) OR (ipsfreight_warehous_id=_weights.itemsite_warehous_id))
        AND   ((ipsfreight_freightclass_id IS NULL) OR (ipsfreight_freightclass_id=_weights.item_freightclass_id))
        AND   ((ipsfreight_shipzone_id IS NULL) OR (ipsfreight_shipzone_id=_order.shipzone_id))
        AND   ((ipsfreight_shipvia IS NULL) OR (ipsfreight_shipvia=_order.shipvia))
        AND   (CURRENT_DATE BETWEEN ipshead_effective AND (ipshead_expires - 1))
        AND   (COALESCE(LENGTH(ipsass_custtype_pattern), 0) > 0)
        AND   (_order.custtype_code ~ ipsass_custtype_pattern) )
      ORDER BY ipsfreight_qtybreak DESC, price ASC
      LIMIT 1;

      IF (_debug) THEN
        IF (_price.price IS NOT NULL) THEN
          RAISE NOTICE 'Customer Type Pattern Price found, %', _price.price;
        END IF;
      END IF;

    END IF;

    -- Select the lowest price  
    IF ( (_price.price IS NOT NULL) AND ((_sales.price IS NULL) OR (_price.price < _sales.price)) ) THEN
      _freightid := _price.ipsfreight_id;
      _totalprice := _price.price;
    ELSE
      IF ( (_sales.price IS NOT NULL) AND ((_price.price IS NULL) OR (_sales.price <= _price.price)) ) THEN
        _freightid := _sales.ipsfreight_id;
        _totalprice := _sales.price;
      END IF;
    END IF;

    -- Total
    IF (_freightid IS NOT NULL) THEN
      _result := _result + _totalprice;
    END IF;

    END LOOP;
    RETURN ROUND(_result,2);
  END IF;

  IF (_shipment.shiphead_order_type = 'TO') THEN
  --Transfer Orders
  
    SELECT noNeg( (SELECT SUM(toitem_freight) + tohead_freight
                   FROM tohead, toitem
                   WHERE (toitem_tohead_id=tohead_id)
                     AND (tohead_id = _shipment.shiphead_order_id)
                   GROUP BY tohead_freight) -
                  COALESCE((SELECT SUM(shiphead_freight)
                            FROM shiphead
                            WHERE (shiphead_order_id = _shipment.shiphead_order_id)
                              AND (shiphead_shipped='true')), 0) ) INTO _result;
    RETURN _result;
  END IF;

  RETURN _result;

END;
$_$;


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

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

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

BEGIN

  SELECT SUM(COALESCE(cntslip_qty, 0.0)) INTO _qty
  FROM cntslip
  WHERE (cntslip_cnttag_id=pTagid);

  RETURN _qty;

END;
$_$;


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

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

CREATE FUNCTION calculatefreightdetail(integer, integer, text, integer, integer, text, date, text, integer, character varying, integer, integer, numeric) RETURNS SETOF freightdata
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pCustId ALIAS FOR $1;
  pCustTypeId ALIAS FOR $2;
  pCustTypeCode ALIAS FOR $3;
  pShiptoId ALIAS FOR $4;
  pShipZoneId ALIAS FOR $5;
  pShiptoNum ALIAS FOR $6;
  pOrderDate ALIAS FOR $7;
  pShipVia ALIAS FOR $8;
  pCurrId ALIAS FOR $9;
  pCurrAbbr ALIAS FOR $10;
  pItemSiteWhsId ALIAS FOR $11;
  pItemFreightclassId ALIAS FOR $12;
  pWeight ALIAS FOR $13;

  _row freightData%ROWTYPE;
  _price RECORD;
  _sales RECORD;
  _freightid INTEGER;
  _totalprice NUMERIC;
  _asof DATE;
  _debug BOOLEAN := FALSE;

BEGIN

  --Get pricing effectivity metric
  IF (SELECT fetchMetricText('soPriceEffective') = 'OrderDate') THEN
    _asof := pOrderDate;
  ELSE
    _asof := CURRENT_DATE;
  END IF;

  _freightid := NULL;
  _totalprice := 0.0;

  IF (_debug) THEN
    RAISE NOTICE 'pWeight - %', pWeight;
    RAISE NOTICE 'pItemSiteWhsId = %', pItemSiteWhsId;
    RAISE NOTICE 'pItemFreightclassId = %', pItemFreightclassId;
  END IF;

-- First get a sales price if any so when we find other prices
-- we can determine if we want that price or this sales price.
--  Check for a Sale Price
  SELECT ipsfreight_id,
    CASE WHEN (ipsfreight_type='F') THEN currToCurr(ipshead_curr_id, pCurrId,
        ipsfreight_price, pOrderDate)
      ELSE currToCurr(ipshead_curr_id, pCurrId,
        (pWeight * ipsfreight_price), pOrderDate)
    END AS price
  INTO _sales
  FROM ipsfreight
  JOIN ipshead ON (ipshead_id=ipsfreight_ipshead_id)
  JOIN sale ON (sale_ipshead_id=ipshead_id)
  WHERE ( (ipsfreight_qtybreak <= pWeight)
    AND ((ipsfreight_warehous_id IS NULL) OR (ipsfreight_warehous_id=pItemSiteWhsId))
    AND ((ipsfreight_freightclass_id IS NULL) OR (ipsfreight_freightclass_id=pItemFreightclassId))
    AND ((ipsfreight_shipzone_id IS NULL) OR (ipsfreight_shipzone_id=pShipZoneId))
    AND ((ipsfreight_shipvia IS NULL) OR (ipsfreight_shipvia=pShipVia))
    AND (_asof BETWEEN sale_startdate AND sale_enddate)
    AND (pCustId IS NOT NULL) )
  ORDER BY ipsfreight_qtybreak DESC, price ASC
  LIMIT 1;

  IF (_debug) THEN
    IF (_sales.price IS NOT NULL) THEN
      RAISE NOTICE 'Sales Price found, %', _sales.price;
    END IF;
  END IF;

--  Check for a Customer Shipto Price
  SELECT ipsfreight_id,
    CASE WHEN (ipsfreight_type='F') THEN currToCurr(ipshead_curr_id, pCurrId,
        ipsfreight_price, pOrderDate)
      ELSE currToCurr(ipshead_curr_id, pCurrId,
        (pWeight * ipsfreight_price), pOrderDate)
    END AS price
  INTO _price
  FROM ipsfreight
  JOIN ipshead ON (ipshead_id=ipsfreight_ipshead_id)
  JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
  WHERE ( (ipsfreight_qtybreak <= pWeight)
    AND ((ipsfreight_warehous_id IS NULL) OR (ipsfreight_warehous_id=pItemSiteWhsId))
    AND ((ipsfreight_freightclass_id IS NULL) OR (ipsfreight_freightclass_id=pItemFreightclassId))
    AND ((ipsfreight_shipzone_id IS NULL) OR (ipsfreight_shipzone_id=pShipZoneId))
    AND ((ipsfreight_shipvia IS NULL) OR (ipsfreight_shipvia=pShipVia))
    AND (_asof BETWEEN ipshead_effective AND (ipshead_expires - 1))
    AND   (ipsass_shipto_id != -1)
    AND   (ipsass_shipto_id=pShiptoId) )
  ORDER BY ipsfreight_qtybreak DESC, price ASC
  LIMIT 1;

  IF (_debug) THEN
    IF (_price.price IS NOT NULL) THEN
      RAISE NOTICE 'Customer Shipto Price found, %', _price.price;
    END IF;
  END IF;

  IF (_price.price IS NULL) THEN
--  Check for a Customer Shipto Pattern Price
  SELECT ipsfreight_id,
    CASE WHEN (ipsfreight_type='F') THEN currToCurr(ipshead_curr_id, pCurrId,
        ipsfreight_price, pOrderDate)
      ELSE currToCurr(ipshead_curr_id, pCurrId,
        (pWeight * ipsfreight_price), pOrderDate)
    END AS price
  INTO _price
  FROM ipsfreight
  JOIN ipshead ON (ipshead_id=ipsfreight_ipshead_id)
  JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
  WHERE ( (ipsfreight_qtybreak <= pWeight)
    AND (_asof BETWEEN ipshead_effective AND (ipshead_expires - 1))
    AND (ipsass_cust_id=pCustId)
    AND (COALESCE(LENGTH(ipsass_shipto_pattern), 0) > 0)
    AND (pShiptoNum ~ ipsass_shipto_pattern)
    AND ((ipsfreight_warehous_id IS NULL) OR (ipsfreight_warehous_id=pItemSiteWhsId))
    AND ((ipsfreight_freightclass_id IS NULL) OR (ipsfreight_freightclass_id=pItemFreightclassId))
    AND ((ipsfreight_shipzone_id IS NULL) OR (ipsfreight_shipzone_id=pShipZoneId))
    AND ((ipsfreight_shipvia IS NULL) OR (ipsfreight_shipvia=pShipVia)) )
  ORDER BY ipsfreight_qtybreak DESC, price ASC
  LIMIT 1;

  IF (_debug) THEN
    IF (_price.price IS NOT NULL) THEN
      RAISE NOTICE 'Customer Shipto Pattern Price found, %', _price.price;
    END IF;
  END IF;

  END IF;

  IF (_price.price IS NULL) THEN
--  Check for a Customer Price
  SELECT ipsfreight_id,
    CASE WHEN (ipsfreight_type='F') THEN currToCurr(ipshead_curr_id, pCurrId,
        ipsfreight_price, pOrderDate)
      ELSE currToCurr(ipshead_curr_id, pCurrId,
        (pWeight * ipsfreight_price), pOrderDate)
    END AS price
  INTO _price
  FROM ipsfreight
  JOIN ipshead ON (ipshead_id=ipsfreight_ipshead_id)
  JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
  WHERE ( (ipsfreight_qtybreak <= pWeight)
    AND((ipsfreight_warehous_id IS NULL) OR (ipsfreight_warehous_id=pItemSiteWhsId))
    AND ((ipsfreight_freightclass_id IS NULL) OR (ipsfreight_freightclass_id=pItemFreightclassId))
    AND ((ipsfreight_shipzone_id IS NULL) OR (ipsfreight_shipzone_id=pShipZoneId))
    AND ((ipsfreight_shipvia IS NULL) OR (ipsfreight_shipvia=pShipVia))
    AND (_asof BETWEEN ipshead_effective AND (ipshead_expires - 1))
    AND (ipsass_cust_id=pCustId)
    AND (COALESCE(LENGTH(ipsass_shipto_pattern), 0) = 0) )
  ORDER BY ipsfreight_qtybreak DESC, price ASC
  LIMIT 1;

  IF (_debug) THEN
    IF (_price.price IS NOT NULL) THEN
      RAISE NOTICE 'Customer Price found, %', _price.price;
    END IF;
  END IF;

  END IF;

  IF (_price.price IS NULL) THEN
--  Check for a Customer Type Price
  SELECT ipsfreight_id,
    CASE WHEN (ipsfreight_type='F') THEN currToCurr(ipshead_curr_id, pCurrId,
        ipsfreight_price, pOrderDate)
      ELSE currToCurr(ipshead_curr_id, pCurrId,
        (pWeight * ipsfreight_price), pOrderDate)
    END AS price
  INTO _price
  FROM ipsfreight JOIN ipshead ON (ipshead_id=ipsfreight_ipshead_id)
                  JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
  WHERE ( (ipsfreight_qtybreak <= pWeight)
    AND ((ipsfreight_warehous_id IS NULL) OR (ipsfreight_warehous_id=pItemSiteWhsId))
    AND ((ipsfreight_freightclass_id IS NULL) OR (ipsfreight_freightclass_id=pItemFreightclassId))
    AND ((ipsfreight_shipzone_id IS NULL) OR (ipsfreight_shipzone_id=pShipZoneId))
    AND ((ipsfreight_shipvia IS NULL) OR (ipsfreight_shipvia=pShipVia))
    AND (_asof BETWEEN ipshead_effective AND (ipshead_expires - 1))
    AND (ipsass_custtype_id=pCustTypeId) )
  ORDER BY ipsfreight_qtybreak DESC, price ASC
  LIMIT 1;

  IF (_debug) THEN
    IF (_price.price IS NOT NULL) THEN
      RAISE NOTICE 'Customer Type Price found, %', _price.price;
    END IF;
  END IF;

  END IF;

  IF (_price.price IS NULL) THEN
--  Check for a Customer Type Pattern Price
  SELECT ipsfreight_id,
    CASE WHEN (ipsfreight_type='F') THEN currToCurr(ipshead_curr_id, pCurrId,
        ipsfreight_price, pOrderDate)
      ELSE currToCurr(ipshead_curr_id, pCurrId,
        (pWeight * ipsfreight_price), pOrderDate)
    END AS price
  INTO _price
  FROM ipsfreight JOIN ipshead ON (ipshead_id=ipsfreight_ipshead_id)
                  JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
  WHERE ( (ipsfreight_qtybreak <= pWeight)
    AND ((ipsfreight_warehous_id IS NULL) OR (ipsfreight_warehous_id=pItemSiteWhsId))
    AND ((ipsfreight_freightclass_id IS NULL) OR (ipsfreight_freightclass_id=pItemFreightclassId))
    AND ((ipsfreight_shipzone_id IS NULL) OR (ipsfreight_shipzone_id=pShipZoneId))
    AND ((ipsfreight_shipvia IS NULL) OR (ipsfreight_shipvia=pShipVia))
    AND (_asof BETWEEN ipshead_effective AND (ipshead_expires - 1))
    AND (COALESCE(LENGTH(ipsass_custtype_pattern), 0) > 0)
    AND (pCustTypeCode ~ ipsass_custtype_pattern) )
  ORDER BY ipsfreight_qtybreak DESC, price ASC
  LIMIT 1;

  IF (_debug) THEN
    IF (_price.price IS NOT NULL) THEN
      RAISE NOTICE 'Customer Type Pattern Price found, %', _price.price;
    END IF;
  END IF;

  END IF;

  -- Select the lowest price
  IF ( (_price.price IS NOT NULL) AND ((_sales.price IS NULL) OR (_price.price < _sales.price)) ) THEN
    _freightid := _price.ipsfreight_id;
    _totalprice := _price.price;
  ELSE
    IF ( (_sales.price IS NOT NULL) AND ((_price.price IS NULL) OR (_sales.price <= _price.price)) ) THEN
      _freightid := _sales.ipsfreight_id;
      _totalprice := _sales.price;
    END IF;
  END IF;

  IF (_debug) THEN
    RAISE NOTICE '_freightid = %', _freightid;
    RAISE NOTICE '_totalprice = %', _totalprice;
  END IF;

  -- Get information for the selected ipsfreight
  -- and return
  IF (_freightid IS NULL) THEN
    _row.freightdata_schedule := 'N/A';
    _row.freightdata_from := '';
    _row.freightdata_to := '';
    _row.freightdata_shipvia := '';
    _row.freightdata_freightclass := '';
    _row.freightdata_weight := 0;
    _row.freightdata_uom := '';
    _row.freightdata_price := 0;
    _row.freightdata_type := '';
    _row.freightdata_total := 0;
    _row.freightdata_currency := '';
    RETURN NEXT _row;
  ELSE
    SELECT ipshead_name AS freightdata_schedule,
      COALESCE(warehous_code, 'Any') AS freightdata_from,
      COALESCE(shipzone_name, 'Any') AS freightdata_to,
      COALESCE(ipsfreight_shipvia, 'Any') AS freightdata_shipvia,
      COALESCE(freightclass_code, 'Any') AS freightdata_freightclass,
      pWeight AS freightdata_weight,
      uom_name AS freightdata_uom,
      currToCurr(ipshead_curr_id, pCurrId, ipsfreight_price, pOrderDate) AS freightdata_price,
      CASE WHEN (ipsfreight_type='F') THEN 'Flat Rate'
        ELSE 'Per UOM'
      END AS freightdata_type,
      _totalprice AS freightdata_total,
      pCurrAbbr AS freightdata_currency
    INTO _row
    FROM ipsfreight
      JOIN ipshead ON (ipshead_id=ipsfreight_ipshead_id)
      LEFT OUTER JOIN uom ON (uom_item_weight)
      LEFT OUTER JOIN whsinfo ON (warehous_id=ipsfreight_warehous_id)
      LEFT OUTER JOIN shipzone ON (shipzone_id=ipsfreight_shipzone_id)
      LEFT OUTER JOIN freightclass ON (freightclass_id=ipsfreight_freightclass_id)
    WHERE (ipsfreight_id=_freightid);

    RETURN NEXT _row;
  END IF;

  RETURN;
END;
$_$;


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

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

CREATE FUNCTION calculatesubtax(integer, date, integer, numeric, integer) RETURNS SETOF taxdetail
    LANGUAGE plpgsql
    AS $_$
-- Copyright (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;
  pDate ALIAS FOR $2;
  pCurrId ALIAS FOR $3;
  pAmount ALIAS FOR $4;
  pLevel ALIAS FOR $5;
  _row taxdetail%ROWTYPE;
  _rownumber INTEGER := 1;
  _calc_tax Numeric :=0;
  _x RECORD;
  _y RECORD;

BEGIN
  FOR _x IN 

  SELECT tax_id, tax_code, tax_descrip, tax_basis_tax_id,
    taxrate_id, taxrate_percent, taxrate_curr_id, taxrate_amount,  
    taxclass_id, taxclass_code, COALESCE(taxclass_sequence,0) AS taxclass_sequence,
    curr_id, curr_abbr
  FROM tax, taxrate, taxclass, curr_symbol
  WHERE ((tax_id = taxrate_tax_id)
  AND (tax_taxclass_id = taxclass_id)
  AND (taxrate_curr_id = curr_id)
  AND (tax_basis_tax_id = pTaxCodeId)
  AND (pDate BETWEEN taxrate_effective AND taxrate_expires)
  AND (taxrate_curr_id = pCurrId))
  
  LOOP
    SELECT 
      ROUND((_x.taxrate_percent * pAmount + currToCurr(_x.curr_id, pCurrId, _x.taxrate_amount, pDate)), 6) 
    INTO _calc_tax;

    _row.taxdetail_tax_id = _x.tax_id;
    _row.taxdetail_tax_code = _x.tax_code;
    _row.taxdetail_tax_descrip = _x.tax_descrip;
    _row.taxdetail_tax_basis_tax_id = _x.tax_basis_tax_id ;
    _row.taxdetail_taxrate_percent = _x.taxrate_percent;
    _row.taxdetail_taxrate_amount = _x.taxrate_amount;
    _row.taxdetail_level = pLevel + 1;
    _row.taxdetail_taxclass_id = _x.taxclass_id ; 
    _row.taxdetail_taxclass_code = _x.taxclass_code;
    _row.taxdetail_taxclass_sequence = _x.taxclass_sequence;
    _row.taxdetail_tax = _calc_tax;
    _row.taxdetail_curr_id = _x.curr_id;
    _row.taxdetail_curr_abbr = _x.curr_abbr;

    RETURN NEXT _row;
    _rownumber := _rownumber + 1;

    FOR _y IN  
    SELECT * 
    FROM calculateSubTax( _x.tax_id, pDate, pCurrId, _calc_tax, pLevel + 1)
    LOOP
      _row.taxdetail_tax_id = _y.taxdetail_tax_id;
      _row.taxdetail_tax_code = _y.taxdetail_tax_code;
      _row.taxdetail_tax_descrip = _y.taxdetail_tax_descrip;
      _row.taxdetail_tax_basis_tax_id = _y.taxdetail_tax_basis_tax_id ;
      _row.taxdetail_taxrate_percent = _y.taxdetail_taxrate_percent;
      _row.taxdetail_taxrate_amount = _y.taxdetail_taxrate_amount;
      _row.taxdetail_level = _y.taxdetail_level + 1;
      _row.taxdetail_taxclass_id = _y.taxdetail_taxclass_id ; 
      _row.taxdetail_taxclass_code = _y.taxdetail_taxclass_code;
      _row.taxdetail_taxclass_sequence = _y.taxdetail_taxclass_sequence;
      _row.taxdetail_tax = _y.taxdetail_tax;
      _row.taxdetail_curr_id = _y.taxdetail_curr_id;
      _row.taxdetail_curr_abbr = _y.taxdetail_curr_abbr;
      
      RETURN NEXT _row;
      _rownumber := _rownumber + 1;

    END LOOP;

  END LOOP;

END;
$_$;


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

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

CREATE FUNCTION calculatetax(integer, integer, date, integer, numeric) RETURNS numeric
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pTaxZoneId ALIAS FOR  $1;
  pTaxTypeId ALIAS FOR  $2;
  pDate ALIAS FOR  $3;
  pCurrId ALIAS FOR $4;
  pAmount ALIAS FOR $5;
  _tottax numeric := 0;  -- total tax
  
BEGIN

  SELECT COALESCE(ROUND(SUM(taxdetail_tax),6),0)
    INTO _tottax 
  FROM calculateTaxDetail(pTaxZoneId, pTaxTypeId, pDate, pCurrId, pAmount);

  RETURN _tottax;
  
END;
$_$;


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

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

CREATE FUNCTION calculatetaxdetail(integer, integer, date, integer, numeric) RETURNS SETOF taxdetail
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pTaxZoneId ALIAS FOR  $1;
  pTaxTypeId ALIAS FOR  $2;
  pDate ALIAS FOR  $3;
  pCurrId ALIAS FOR $4;
  pAmount ALIAS FOR $5;
  _row taxdetail%ROWTYPE;
  _x RECORD;
  _y RECORD;
  _z RECORD;
  _currcum numeric := 0;  -- Current cumulative tax
  _currseq numeric := 0;  -- Current group sequence
  _prevcum numeric := 0;  -- Previous cumulative tax
  _tax numeric := 0;      -- Calculated tax amount
  _taxbasis numeric := 0;  -- Used for calculating sub taxes

BEGIN

  IF ((COALESCE(pTaxTypeId,-1) = -1) OR (COALESCE(pTaxZoneId,-1) = -1)) THEN
    RETURN;
  END IF;

  SELECT DISTINCT
    COALESCE(taxass_taxzone_id, -1) AS taxzone_id,
    COALESCE(taxass_taxtype_id, -1) AS taxtype_id,
    taxass_tax_id,
    CASE
      WHEN ((taxass_taxzone_id IS NOT NULL) AND (taxass_taxtype_id IS NOT NULL)) THEN
        0
      WHEN ((taxass_taxzone_id IS NOT NULL) AND (taxass_taxtype_id IS NULL)) THEN
        1
      WHEN ((taxass_taxzone_id IS NULL) AND (taxass_taxtype_id IS NOT NULL)) THEN
        2
      ELSE
        3
    END AS sequence
    INTO _x
  FROM taxass
  WHERE  ((COALESCE(taxass_taxzone_id, pTaxZoneId, -1) = COALESCE(pTaxZoneId,-1))
  AND    (COALESCE(taxass_taxtype_id, pTaxTypeId, -1) = COALESCE(pTaxTypeId,-1)))
  ORDER BY sequence LIMIT 1;

  --Now loop through each tax detail record and return calculated result
  FOR _y IN
    SELECT  --the data required by taxdetail type.  Coalesce group sequence to 0 if no class.
    tax_id
    ,tax_code
    ,tax_descrip
    ,tax_basis_tax_id
    ,taxrate_percent
    ,taxrate_amount
    ,0 as taxdetail_level 
    ,taxclass_id
    ,taxclass_code
    ,COALESCE(taxclass_sequence, 0) AS taxclass_sequence
    ,0 as taxdetail_tax
    ,curr_id
    ,curr_abbr	
    FROM taxass, taxclass RIGHT OUTER JOIN tax  
      LEFT OUTER JOIN taxrate ON (taxrate_tax_id=tax_id)
    ON (tax_taxclass_id=taxclass_id),
    curr_symbol 
    WHERE 
    taxass_tax_id=tax_id
    AND taxrate_curr_id=curr_id
    AND COALESCE(taxass_taxzone_id, -1) = _x.taxzone_id
    AND COALESCE(taxass_taxtype_id, -1) = _x.taxtype_id
    AND pDate BETWEEN COALESCE(taxrate_effective, startoftime()) AND COALESCE(taxrate_expires, endoftime())
    ORDER BY COALESCE(taxclass_sequence, 0)
  LOOP
    -- If sequence has changed, cache the previous cumulative tax
    IF (_currseq != _x.sequence) THEN
      _prevcum := _currcum;
    END IF;

    -- Calculate the tax amount.  Convert currency for flat rate amounts
    SELECT 
    ROUND((_y.taxrate_percent * (pAmount + _prevcum) + currToCurr(_y.curr_id, pCurrId, _y.taxrate_amount, pDate)), 6) 
    INTO _tax
    FROM tax JOIN  taxrate ON (tax_id = taxrate_tax_id)
    WHERE (tax_id=_x.taxass_tax_id)
    AND (pDate BETWEEN COALESCE(taxrate_effective, startoftime()) AND COALESCE(taxrate_expires, endoftime()));

    --Map fields to _row

    _row.taxdetail_tax_id := _y.tax_id;
    _row.taxdetail_tax_code := _y.tax_code;
    _row.taxdetail_tax_descrip := _y.tax_descrip;
    _row.taxdetail_tax_basis_tax_id := _y.tax_basis_tax_id;
    _row.taxdetail_taxrate_percent := _y.taxrate_percent;
    _row.taxdetail_taxrate_amount := _y.taxrate_amount;
    _row.taxdetail_level := _y.taxdetail_level;
    _row.taxdetail_taxclass_id := _y.taxclass_id;
    _row.taxdetail_taxclass_code := _y.taxclass_code;
    _row.taxdetail_taxclass_sequence := _y.taxclass_sequence;
    _row.taxdetail_tax := _tax;
    _row.taxdetail_curr_id := _y.curr_id;
    _row.taxdetail_curr_abbr := _y.curr_abbr;
  
    RETURN NEXT _row;

    -- Increment cumulative balance and sequence number
    IF(_y.taxclass_sequence <> 0) THEN
      _currcum := _currcum + _tax;
    END IF;
    _currseq := _y.taxclass_sequence;

    -- Loop to Calculate sub taxes
    FOR _z IN
    SELECT *
    FROM calculateSubTax(_y.tax_id,pDate, pCurrId, _tax, 0)
    LOOP
     --Mapping of data
    _row.taxdetail_tax_id := _z.taxdetail_tax_id;
    _row.taxdetail_tax_code := _z.taxdetail_tax_code;
    _row.taxdetail_tax_descrip := _z.taxdetail_tax_descrip;
    _row.taxdetail_tax_basis_tax_id := _z.taxdetail_tax_basis_tax_id;
    _row.taxdetail_taxrate_percent := _z.taxdetail_taxrate_percent;
    _row.taxdetail_taxrate_amount := _z.taxdetail_taxrate_amount;
    _row.taxdetail_level := _z.taxdetail_level;
    _row.taxdetail_taxclass_id := _z.taxdetail_taxclass_id;
    _row.taxdetail_taxclass_code := _z.taxdetail_taxclass_code;
    _row.taxdetail_taxclass_sequence := _z.taxdetail_taxclass_sequence;
    _row.taxdetail_tax := _z.taxdetail_tax;
    _row.taxdetail_curr_id := _z.taxdetail_curr_id;
    _row.taxdetail_curr_abbr := _z.taxdetail_curr_abbr;

     RETURN NEXT _row;
     --Add to cumulative counter (_curcum)
     _currcum := _currcum + _z.taxdetail_tax ;

    END LOOP;

   END LOOP;
 
END;
 $_$;


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

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

CREATE FUNCTION calculatetaxdetailline(text, integer) RETURNS SETOF taxdetail
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pOrderType ALIAS FOR $1;
  pOrderId ALIAS FOR $2;
  _row taxdetail%ROWTYPE;
  _qry text;
  _totaltax numeric;
  _y RECORD;
  _table text;

BEGIN
   _totaltax=0.0;

   IF pOrderType = 'II' THEN
     _table := 'invcitemtax';
   ELSIF pOrderType = 'BI' THEN
     _table := 'cobilltax';
   ELSIF pOrderType = 'CI' THEN
     _table := 'cmitemtax';
   ELSIF pOrderType = 'VI' THEN
     _table := 'voitemtax';
   ELSIF pOrderType = 'TI' THEN
     _table := 'toitemtax';
   ELSIF pOrderType = 'AR' THEN
     _table := 'aropentax';
   ELSIF pOrderType = 'AP' THEN
     _table := 'apopentax';
   END IF;

   _qry := 'SELECT taxhist_tax_id as tax_id, tax_code, tax_descrip, taxhist_tax, COALESCE(taxhist_sequence,0) AS taxhist_sequence
            FROM ' || _table || '
             JOIN tax ON (taxhist_tax_id=tax_id)
            WHERE ( (taxhist_parent_id = ' || pOrderId || ') );';

   FOR _y IN  EXECUTE _qry
   LOOP
     _row.taxdetail_tax_id=_y.tax_id;
     _row.taxdetail_tax_code = _y.tax_code;
     _row.taxdetail_tax_descrip = _y.tax_descrip;
     _row.taxdetail_tax = _y.taxhist_tax;
     _row.taxdetail_level= 0 ;
     _row.taxdetail_taxclass_sequence= _y.taxhist_sequence;
     _totaltax = _totaltax + _y.taxhist_tax;
     RETURN NEXT _row;
   END LOOP;
 END;
$_$;


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

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

CREATE FUNCTION calculatetaxdetailsummary(text, integer, text) RETURNS SETOF taxdetail
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pOrderType ALIAS FOR $1;
  pOrderId ALIAS FOR $2;
  pDisplayType ALIAS FOR $3;
  _row taxdetail%ROWTYPE;
  _qry text := '';
  _qry1 text;
  _totaltax numeric;
  _x RECORD;
  _y RECORD;
  _table text;

BEGIN
 _totaltax=0.0;
 IF pOrderType IN ('S','Q','RA','PO') THEN

   IF pOrderType = 'S' THEN
     _qry := 'SELECT ' || 'COALESCE(cohead_taxzone_id, -1) AS taxzone_id, cohead_orderdate AS order_date,
                cohead_curr_id AS curr_id, COALESCE(coitem_taxtype_id, -1) AS taxtype_id,
                ROUND((coitem_qtyord * coitem_qty_invuomratio) * (coitem_price / coitem_price_invuomratio),2) AS amount
              FROM cohead, coitem
              WHERE ( (coitem_cohead_id = ' || pOrderId || ')
               AND (' || 'cohead_id = coitem_cohead_id)
               AND ( coitem_status != ''X'') )';
   ELSEIF  pOrderType = 'Q' THEN
     _qry := 'SELECT ' || 'COALESCE(quhead_taxzone_id, -1) AS taxzone_id, quhead_quotedate AS order_date,
                quhead_curr_id AS curr_id, COALESCE(quitem_taxtype_id, -1) AS taxtype_id,
                ROUND((quitem_qtyord * quitem_qty_invuomratio) * (quitem_price / quitem_price_invuomratio),2) AS amount
              FROM quhead, quitem
              WHERE ( (quitem_quhead_id = ' || pOrderId || ')
               AND (quhead_id = quitem_quhead_id) )';
   ELSEIF  pOrderType = 'RA' THEN
     _qry := 'SELECT ' || 'COALESCE(rahead_taxzone_id, -1) AS taxzone_id, rahead_authdate AS order_date,
                rahead_curr_id AS curr_id, COALESCE(raitem_taxtype_id, -1) AS taxtype_id,
                ROUND((raitem_qtyauthorized * raitem_qty_invuomratio) * (raitem_unitprice / raitem_price_invuomratio),2) AS amount
              FROM rahead, raitem
              WHERE ( (raitem_rahead_id = ' || pOrderId || ')
               AND (rahead_id = raitem_rahead_id) )';
   ELSEIF  pOrderType = 'PO' THEN
     _qry := 'SELECT ' || 'COALESCE(pohead_taxzone_id, -1) AS taxzone_id, pohead_orderdate AS order_date,
                pohead_curr_id AS curr_id, COALESCE(poitem_taxtype_id, -1) AS taxtype_id,
                ROUND(poitem_qty_ordered * poitem_unitprice, 2) AS amount
              FROM pohead, poitem
              WHERE ( (poitem_pohead_id = ' || pOrderId || ')
               AND (pohead_id = poitem_pohead_id) )';
  END IF;

  FOR _x IN EXECUTE _qry
  LOOP
    _qry1 := 'SELECT * from calculatetaxdetail(' || _x.taxzone_id || ',' || _x.taxtype_id || ',''' || _x.order_date || ''',' || _x.curr_id  || ',' || _x.amount || ')';
    FOR _y IN  EXECUTE _qry1
    LOOP
      _row.taxdetail_tax_id=_y.taxdetail_tax_id;
      _row.taxdetail_tax_code = _y.taxdetail_tax_code;
      _row.taxdetail_tax_descrip = _y.taxdetail_tax_descrip;
      _row.taxdetail_tax = _y.taxdetail_tax;
      _row.taxdetail_level=_y.taxdetail_level;
      _row.taxdetail_taxclass_sequence= _y.taxdetail_taxclass_sequence;
      _totaltax = _totaltax + _y.taxdetail_tax;
      RETURN NEXT _row;
    END LOOP;
  END LOOP;

  IF pDisplayType = 'T' AND pOrderType <> 'PO' THEN
   IF pOrderType = 'S' THEN
    _qry := 'SELECT COALESCE(cohead_taxzone_id, -1) AS taxzone_id, cohead_orderdate AS order_date,
               cohead_curr_id AS curr_id, cohead_freight AS freight
             FROM cohead WHERE cohead_id = ' || pOrderId ;
   ELSEIF  pOrderType = 'Q' THEN
    _qry := 'SELECT COALESCE(quhead_taxzone_id, -1) AS taxzone_id, quhead_quotedate AS order_date,
               quhead_curr_id AS curr_id, COALESCE(quhead_freight,0) AS freight
             FROM quhead WHERE quhead_id = ' || pOrderId;
   ELSEIF pOrderType = 'RA' THEN
    _qry := 'SELECT COALESCE(rahead_taxzone_id, -1) AS taxzone_id, COALESCE(rahead_authdate,CURRENT_DATE) AS order_date,
               rahead_curr_id AS curr_id, COALESCE(rahead_freight,0) AS freight
             FROM rahead WHERE rahead_id = ' || pOrderId;
   END IF;

  FOR _x IN EXECUTE _qry
  LOOP
     _qry1 := 'SELECT * from calculatetaxdetail(' || _x.taxzone_id || ', getfreighttaxtypeid(),''' || _x.order_date || ''',' || _x.curr_id  || ',' || _x.freight || ')';
    FOR _y IN  EXECUTE _qry1
    LOOP
       _row.taxdetail_tax_id=_y.taxdetail_tax_id;
       _row.taxdetail_tax_code = _y.taxdetail_tax_code;
       _row.taxdetail_tax_descrip = _y.taxdetail_tax_descrip;
       _row.taxdetail_tax = _y.taxdetail_tax;
       _row.taxdetail_level=_y.taxdetail_level;
       _row.taxdetail_taxclass_sequence= _y.taxdetail_taxclass_sequence;
       _totaltax = _totaltax + _y.taxdetail_tax;
       RETURN NEXT _row;
     END LOOP;
   END LOOP;
  END IF;

 ELSEIF pOrderType IN ('I','B','CM', 'VO','TO') THEN
   IF (pOrderType='I') THEN
     _table := 'invcheadtax';
   ELSIF (pOrderType='B') THEN
     _table := 'cobmisctax';
   ELSIF (pOrderType='CM') THEN
     _table := 'cmheadtax';
   ELSIF (pOrderType='VO') THEN
     _table := 'voheadtax';
   ELSIF (pOrderType='TO') THEN
     _table := 'toheadtax';
   END IF;

   IF pOrderType = 'I' AND (pDisplayType IN ('L','T')) THEN
     _qry := 'SELECT taxhist_tax_id as tax_id, tax_code, tax_descrip, taxhist_tax, taxhist_sequence
              FROM invchead, invcitemtax LEFT OUTER JOIN tax ON (taxhist_tax_id=tax_id)
               LEFT OUTER JOIN invcitem ON (invcitem_id=taxhist_parent_id)
              WHERE invcitem_invchead_id = ' || pOrderId || '
               AND invchead_id = invcitem_invchead_id ';
   ELSIF pOrderType = 'B' AND (pDisplayType IN ('L','T')) THEN
    _qry := 'SELECT taxhist_tax_id as tax_id, tax_code, tax_descrip, taxhist_tax, taxhist_sequence
             FROM cobmisc, cobilltax LEFT OUTER JOIN tax ON (taxhist_tax_id=tax_id)
             LEFT OUTER JOIN cobill ON (cobill_id=taxhist_parent_id)
             WHERE cobill_cobmisc_id = ' || pOrderId || '
             AND cobmisc_id = cobill_cobmisc_id ';
   ELSIF pOrderType = 'CM' AND (pDisplayType IN ('L','T')) THEN
    _qry := 'SELECT taxhist_tax_id as tax_id, tax_code, tax_descrip, taxhist_tax, taxhist_sequence
             FROM cmhead, cmitemtax LEFT OUTER JOIN tax ON (taxhist_tax_id=tax_id)
             LEFT OUTER JOIN cmitem ON (cmitem_id=taxhist_parent_id)
             WHERE cmitem_cmhead_id = ' || pOrderId || '
             AND cmhead_id = cmitem_cmhead_id ';
   ELSIF pOrderType = 'VO' AND (pDisplayType IN ('L','T')) THEN
    _qry := 'SELECT taxhist_tax_id as tax_id, tax_code, tax_descrip, taxhist_tax, taxhist_sequence
             FROM vohead, voitemtax LEFT OUTER JOIN tax ON (taxhist_tax_id=tax_id)
             LEFT OUTER JOIN voitem ON (voitem_id=taxhist_parent_id)
             WHERE voitem_vohead_id = ' || pOrderId || '
             AND vohead_id = voitem_vohead_id ';
   ELSIF pOrderType = 'TO' AND (pDisplayType IN ('L','T')) THEN
    _qry := 'SELECT taxhist_tax_id as tax_id, tax_code, tax_descrip, taxhist_tax, taxhist_sequence
             FROM tohead, toitemtax LEFT OUTER JOIN tax ON (taxhist_tax_id=tax_id)
             LEFT OUTER JOIN toitem ON (toitem_id=taxhist_parent_id)
             WHERE toitem_tohead_id = ' || pOrderId || '
             AND tohead_id = toitem_tohead_id ';
   END IF;
   IF pDisplayType IN ('F','T') AND pOrderType <> 'VO' THEN
     IF (length(_qry) > 0) THEN
       _qry := _qry || ' UNION ALL ';
     END IF;
     _qry := _qry || 'SELECT taxhist_tax_id as tax_id, tax_code, tax_descrip, taxhist_tax, taxhist_sequence
              FROM ' || _table || '
               JOIN tax ON (taxhist_tax_id=tax_id)
              WHERE ( (taxhist_parent_id = ' || pOrderId || ')
               AND (taxhist_taxtype_id=getfreighttaxtypeid()) )';
   END IF;
   IF pDisplayType IN ('A','T') THEN
     IF (length(_qry) > 0) THEN
       _qry := _qry || ' UNION ALL ';
     END IF;
     _qry := _qry || 'SELECT taxhist_tax_id as tax_id, tax_code, tax_descrip, taxhist_tax, taxhist_sequence
              FROM ' || _table || '
               JOIN tax ON (taxhist_tax_id=tax_id)
              WHERE ( (taxhist_parent_id = ' || pOrderId || ')
               AND (taxhist_taxtype_id=getadjustmenttaxtypeid()) )';

   END IF;
   FOR _y IN  EXECUTE _qry
   LOOP
     _row.taxdetail_tax_id=_y.tax_id;
     _row.taxdetail_tax_code = _y.tax_code;
     _row.taxdetail_tax_descrip = _y.tax_descrip;
     _row.taxdetail_tax = _y.taxhist_tax;
     _row.taxdetail_level= 0 ;
     _row.taxdetail_taxclass_sequence= COALESCE(_y.taxhist_sequence,0);
     _totaltax = _totaltax + _y.taxhist_tax;
     RETURN NEXT _row;
   END LOOP;
 END IF;
 END;
$_$;


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

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

CREATE FUNCTION calculatetaxhist(text, integer, integer, integer, date, integer, numeric) 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
  pTableName ALIAS FOR $1;
  pParentId  ALIAS FOR $2;
  pTaxZoneId ALIAS FOR $3;
  pTaxTypeId ALIAS FOR $4;
  pDate      ALIAS FOR $5;
  pCurrId    ALIAS FOR $6;
  pAmount    ALIAS FOR $7;
  _qry TEXT;
  
BEGIN
  IF (pTableName IS NULL) THEN
    RAISE EXCEPTION 'A table name is required to calculate tax history';
  ELSEIF (pParentId IS NULL) THEN
    RAISE EXCEPTION 'A parent ID is required to calculate tax history';
  ELSEIF (pDate IS NULL) THEN
    RAISE EXCEPTION 'A date is required to calculate tax history';
  ELSEIF (pAmount IS NULL) THEN
     RAISE EXCEPTION 'An amount is required to calculate tax history';
  END IF;

  -- Build a query that deletes any previous tax history for this document record
  _qry := 'DELETE FROM ' || pTableName || ' WHERE taxhist_parent_id = ' || pParentId || ' AND taxhist_taxtype_id <> getadjustmenttaxtypeid();';
  EXECUTE _qry;

  -- Next, build and execute query that inserts new rows.
  _qry := 'INSERT INTO ' || pTableName || ' (
             taxhist_parent_id, taxhist_taxtype_id, taxhist_tax_id, taxhist_basis,
             taxhist_basis_tax_id, taxhist_sequence, taxhist_percent, 
             taxhist_amount, taxhist_tax, taxhist_docdate)
           SELECT ' || pParentId || ',';
  IF (pTaxTypeId IS NULL) THEN
    _qry := _qry || 'NULL';
  ELSE
    _qry := _qry || pTaxTypeId;
  END If;
  _qry := _qry || ', taxdetail_tax_id,' || pAmount || ', 
             taxdetail_tax_basis_tax_id, taxdetail_taxclass_sequence, taxdetail_taxrate_percent,
             taxdetail_taxrate_amount, taxdetail_tax, ''' || pDate || '''
           FROM calculatetaxdetail(' || COALESCE(pTaxZoneId,-1) || ',' || COALESCE(pTaxTypeId,-1) ||',''' || pDate || ''',' || pCurrId || ',' || pAmount || ');';
  EXECUTE _qry;

  RETURN true;
END;
$_$;


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

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

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

BEGIN

  SELECT SUM(COALESCE(vodist_amount, 0)) INTO _amount
  FROM vodist
  WHERE (vodist_vohead_id=pVoucherid);

  RETURN _amount;

END;
$_$;


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

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

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

BEGIN

  SELECT SUM(COALESCE(voitem_freight, 0)) INTO _amount
  FROM voitem
  WHERE (voitem_vohead_id=pVoucherid);

  RETURN _amount;

END;
$_$;


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

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

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

BEGIN

  SELECT COALESCE(calculateTax(vohead_taxzone_id,
                               vohead_taxtype_id,
                               vohead_docdate,
                               vohead_curr_id,
                               calcVoucherAmt(vohead_id)), 0) INTO _amount
  FROM vohead
  WHERE (vohead_id=pVoucherid);

  RETURN _amount;

END;
$_$;


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

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

CREATE FUNCTION calcwooperstartstub(integer, 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
  pWoId         ALIAS FOR $1;
  pBooitemSeqId ALIAS FOR $2;
  _result       DATE;
BEGIN

  IF ( SELECT ((metric_value='t') AND packageIsEnabled('xtmfg'))
         FROM metric
        WHERE(metric_name='Routings') ) THEN
    RETURN xtmfg.calcWooperStart(pWoId, pBooitemSeqId);
  END IF;
  RETURN null;
END;
$_$;


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

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

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

BEGIN

  IF ( ( SELECT cobmisc_posted
         FROM cobmisc
         WHERE (cobmisc_id=pCobmiscid) ) ) THEN
    RETURN -1;
  END IF;

  DELETE FROM cobill
  WHERE (cobill_cobmisc_id=pCobmiscid); 

  DELETE FROM cobmisc
  WHERE (cobmisc_id=pCobmiscid);

  RETURN 1;

END;
$_$;


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

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

CREATE FUNCTION changeaccountingperioddates(integer, date, 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
  pPeriodid ALIAS FOR $1;
  pStartDate ALIAS FOR $2;
  pEndDate ALIAS FOR $3;
  _check INTEGER;
  _r RECORD;

BEGIN

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

--  Check to make sure that the passed start date does not fall
--  into another period
  SELECT period_id INTO _check
  FROM period
  WHERE ( (pStartDate BETWEEN period_start AND period_end)
    AND (period_id <> pPeriodid) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -2;
  END IF;

--  Check to make sure that the passed end date does not fall
--  into another period
  SELECT period_id INTO _check
  FROM period
  WHERE ( (pEndDate BETWEEN period_start AND period_end)
    AND (period_id <> pPeriodid) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -3;
  END IF;

--  Check to make sure that the new passed start and end dates do not
--  orphan a posted G/L Transaction
  SELECT gltrans_id INTO _check
  FROM gltrans, period
  WHERE ( (gltrans_date BETWEEN period_start AND period_end)
   AND (gltrans_posted)
   AND (NOT (gltrans_date BETWEEN pStartDate AND pEndDate))
   AND (period_id=pPeriodid) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -4;
  END IF;

--  Alter the start and end dates of the pass period
  UPDATE period
  SET period_start=pStartDate, period_end=pEndDate
  WHERE (period_id=pPeriodid);

--  Post any unposted G/L Transactions into the period
  FOR _r IN SELECT DISTINCT gltrans_sequence
            FROM gltrans
            WHERE ( (NOT gltrans_posted)
             AND (gltrans_date BETWEEN pStartDate AND pEndDate) ) LOOP
    PERFORM postIntoTrialBalance(_r.gltrans_sequence);
  END LOOP;

--  All done
  RETURN 1;

END;
$_$;


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

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

CREATE FUNCTION changeaccountingyearperioddates(integer, date, 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
  pPeriodid ALIAS FOR $1;
  pStartDate ALIAS FOR $2;
  pEndDate ALIAS FOR $3;
  _check INTEGER;
  _checkBool BOOLEAN;
  _r RECORD;

BEGIN

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

--  Check to make sure that the passed start date does not fall
--  into another yearperiod
  SELECT yearperiod_id INTO _check
  FROM yearperiod
  WHERE ( (pStartDate BETWEEN yearperiod_start AND yearperiod_end)
    AND (yearperiod_id <> pPeriodid) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -2;
  END IF;

--  Check to make sure that the passed end date does not fall
--  into another yearperiod
  SELECT yearperiod_id INTO _check
  FROM yearperiod
  WHERE ( (pEndDate BETWEEN yearperiod_start AND yearperiod_end)
    AND (yearperiod_id <> pPeriodid) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -3;
  END IF;

--  Check to make sure that the passed yearperiod is not closed
  IF ( ( SELECT (count(period_id) > 0)
         FROM period
         WHERE ((period_yearperiod_id=pPeriodid)
          AND (period_start < pStartDate OR period_end > pEndDate)) ) ) THEN
    RETURN -4;
  END IF;

--  Make sure that the passed start is prior to the end date
  SELECT (pStartDate > pEndDate) INTO _checkBool;
  IF (_checkBool) THEN
    RETURN -5;
  END IF;


--  Alter the start and end dates of the pass period
  UPDATE yearperiod
  SET yearperiod_start=pStartDate, yearperiod_end=pEndDate
  WHERE (yearperiod_id=pPeriodid);

--  All done
  RETURN 1;

END;
$_$;


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

--
-- Name: changefkeypointers(text, text, integer, integer, text[], boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION changefkeypointers(text, text, integer, integer, text[], 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
  pSchema       ALIAS FOR $1;
  pTable        ALIAS FOR $2;
  pSourceId     ALIAS FOR $3;
  pTargetId     ALIAS FOR $4;
  pIgnore       ALIAS FOR $5;
  _purge        BOOLEAN := COALESCE($6, FALSE);

  _counter      INTEGER := 0;
  _count1       INTEGER := 0;
  _fk           RECORD;
  _pk           TEXT[];

BEGIN
  -- for all foreign keys that point to pSchema.pTable
  FOR _fk IN
    EXECUTE 'SELECT fkeyns.nspname AS schemaname, fkeytab.relname AS tablename,
                    conkey, attname, typname
               FROM pg_constraint
               JOIN pg_class     basetab ON (confrelid=basetab.oid)
               JOIN pg_namespace basens  ON (basetab.relnamespace=basens.oid)
               JOIN pg_class     fkeytab ON (conrelid=fkeytab.oid)
               JOIN pg_namespace fkeyns  ON (fkeytab.relnamespace=fkeyns.oid)
               JOIN pg_attribute         ON (attrelid=conrelid AND attnum=conkey[1])
               JOIN pg_type              ON (atttypid=pg_type.oid)
              WHERE basetab.relname = ' || quote_literal(pTable)  || '
                AND basens.nspname  = ' || quote_literal(pSchema) || '
                AND fkeytab.relname NOT IN (''' || ARRAY_TO_STRING(pIgnore, ''', ''') || ''')'
  LOOP
    IF (ARRAY_UPPER(_fk.conkey, 1) > 1) THEN
      RAISE EXCEPTION 'Cannot change the foreign key in %.% that refers to %.% because the foreign key constraint has multiple columns. [xtuple: changefkeypointers, -1, %.%, %.%]',
        _fk.schemaname, _fk.tablename, pSchema, pTable,
        _fk.schemaname, _fk.tablename, pSchema, pTable;
    END IF;
    
    -- optionally make a backup copy of the data
    IF (NOT _purge) THEN
      -- determine the primary key column of the fkey table
      _pk := primaryKeyFields(_fk.schemaname, _fk.tablename);
      IF (ARRAY_UPPER(_pk, 1) > 1) THEN
        RAISE EXCEPTION 'Cannot change foreign key references in %.% because it has a composite primary key. Try setting the purge option. [xtuple: changefkeypointers, -4, %.%]',
                        _fk.schemaname, _fk.tablename, _fk.schemaname, _fk.tablename;
      END IF;

      -- make the backup copy
      EXECUTE 'INSERT INTO mrgundo (
                     mrgundo_schema,      mrgundo_table,
                     mrgundo_pkey_col,    mrgundo_pkey_id,
                     mrgundo_col,         mrgundo_value,      mrgundo_type,
                     mrgundo_base_schema, mrgundo_base_table, mrgundo_base_id
             ) SELECT ' || quote_literal(_fk.schemaname) || ', '
                        || quote_literal(_fk.tablename)  || ', '
                        || quote_literal(_pk[1])         || ', ' 
                        || _pk[1]                        || ', '
                        || quote_literal(_fk.attname)    || ', ' 
                        || _fk.attname                   || ', ' 
                        || quote_literal(_fk.typname)    || ', '
                        || quote_literal(pSchema)        || ', '
                        || quote_literal(pTable)         || ', '
                        || pTargetId                     || '
                 FROM ' || _fk.schemaname || '.' || _fk.tablename ||
              ' WHERE ('|| _fk.attname    || '=' || pSourceId || ');';
    END IF;

    -- actually change the foreign keys to point to the desired base table record
    EXECUTE 'UPDATE '  || _fk.schemaname || '.' || _fk.tablename ||
              ' SET '  || _fk.attname    || '=' || pTargetId ||
            ' WHERE (' || _fk.attname    || '=' || pSourceId || ');';

    GET DIAGNOSTICS _count1 = ROW_COUNT;
    _counter := _counter + _count1;
  END LOOP;

  RETURN _counter;
END;
$_$;


ALTER FUNCTION public.changefkeypointers(text, text, integer, integer, text[], boolean) OWNER TO admin;

--
-- Name: FUNCTION changefkeypointers(text, text, integer, integer, text[], boolean); Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON FUNCTION changefkeypointers(text, text, integer, integer, text[], boolean) IS 'Change the data in all tables with foreign key relationships so they point to the pSchema.pTable record with primary key pTargetId instead of the record with primary key pSourceId. Ignore any tables listed in pIgnore. If the final arg is TRUE, make a backup copy of the original data in the mrgundo table.';


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

CREATE FUNCTION changepoitemduedate(ppoitemid integer, pdate 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.
BEGIN

  RETURN changePoitemDueDate(pPoitemid, pDate, false);

END;
$$;


ALTER FUNCTION public.changepoitemduedate(ppoitemid integer, pdate date) OWNER TO admin;

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

CREATE FUNCTION changepoitemduedate(ppoitemid integer, pdate date, pbyso boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN

  IF ( ( SELECT (poitem_status IN ('C'))
         FROM poitem
         WHERE (poitem_id=pPoitemid) ) ) THEN
    RETURN -1;
  END IF;

  UPDATE poitem
  SET poitem_duedate=pDate
  WHERE (poitem_id=pPoitemid);

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

  RETURN pPoitemid;

END;
$$;


ALTER FUNCTION public.changepoitemduedate(ppoitemid integer, pdate date, pbyso boolean) OWNER TO admin;

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

CREATE FUNCTION changepoitemqty(ppoitemid integer, pqty 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.
BEGIN

  RETURN changePoitemQty(pPoitemid, pQty, false);

END;
$$;


ALTER FUNCTION public.changepoitemqty(ppoitemid integer, pqty numeric) OWNER TO admin;

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

CREATE FUNCTION changepoitemqty(ppoitemid integer, pqty numeric, pbyso 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

BEGIN

  IF ( ( SELECT (poitem_status IN ('C'))
         FROM poitem
         WHERE (poitem_id=pPoitemid) ) ) THEN
    RETURN -1;
  END IF;

  UPDATE poitem
  SET poitem_qty_ordered=pQty
  WHERE (poitem_id=pPoitemid);

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

  RETURN pPoitemid;

END;
$$;


ALTER FUNCTION public.changepoitemqty(ppoitemid integer, pqty numeric, pbyso boolean) OWNER TO admin;

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

CREATE FUNCTION changeprdate(integer, 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
  pPrid ALIAS FOR $1;
  pDueDate ALIAS FOR $2;

BEGIN

  UPDATE pr
  SET pr_duedate=pDueDate
  WHERE (pr_id=pPrid);

  RETURN 0;

END;
$_$;


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

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

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

BEGIN

  UPDATE pr
  SET pr_qtyreq=pQty
  WHERE (pr_id=pPrid);

  RETURN TRUE;

END;
$_$;


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

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

CREATE FUNCTION changeprqty(integer, date) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
  pPrid ALIAS FOR $1;
  pDueDate ALIAS FOR $2;

BEGIN

  UPDATE pr
  SET pr_duedate=pDueDate
  WHERE (pr_id=pPrid);

  RETURN 0;

END;
$_$;


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

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

CREATE FUNCTION changepseudofkeypointers(text, text, text, integer, text, text, integer, text, text, 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
  pSchema       ALIAS FOR $1;
  pTable        ALIAS FOR $2;
  pFkeyCol      ALIAS FOR $3;
  pSourceId     ALIAS FOR $4;
  pBaseSchema   ALIAS FOR $5;
  pBaseTable    ALIAS FOR $6;
  pTargetId     ALIAS FOR $7;
  pTypeCol      ALIAS FOR $8;
  pType         ALIAS FOR $9;
  _purge        BOOLEAN := COALESCE($10, FALSE);

  _counter      INTEGER := 0;
  _coltype      TEXT;
  _pk           TEXT[];

BEGIN
  IF (NOT _purge) THEN
    EXECUTE 'SELECT typname
               FROM pg_type
               JOIN pg_attribute ON (pg_type.oid=atttypid)
               JOIN pg_class     ON (attrelid=pg_class.oid)
               JOIN pg_namespace ON (relnamespace=pg_namespace.oid)
              WHERE (relname=' || quote_literal(pTable)   || ')
                AND (nspname=' || quote_literal(pSchema)  || ')
                AND (attname=' || quote_literal(pFkeyCol) || ')' INTO _coltype;

    _pk := primaryKeyFields(pSchema, pTable);
    IF (ARRAY_UPPER(_pk, 1) > 1) THEN
       RAISE EXCEPTION 'Cannot change pseudo-foreign key references in %.% because it has a composite primary key. Try setting the purge option. [xtuple: changepseudofkeypointers, -1, %.%',
                        pSchema, pTable, pSchema, pTable;
    END IF;

    EXECUTE 'INSERT INTO mrgundo (
                     mrgundo_schema,      mrgundo_table,
                     mrgundo_pkey_col,    mrgundo_pkey_id,
                     mrgundo_col,         mrgundo_value,      mrgundo_type,
                     mrgundo_base_schema, mrgundo_base_table, mrgundo_base_id
           ) SELECT ' || quote_literal(pSchema)     || ', '
                      || quote_literal(pTable)      || ', '
                      || quote_literal(_pk[1])      || ', ' 
                      || quote_ident(_pk[1])        || ', '
                      || quote_literal(pFkeyCol)    || ', ' 
                      || quote_ident(pFkeyCol)      || ', ' 
                      || quote_literal(_coltype)    || ', '
                      || quote_literal(pBaseSchema) || ', '
                      || quote_literal(pBaseTable)  || ', '
                      || pTargetId                  || '
               FROM '  || quote_ident(pSchema)  || '.' || quote_ident(pTable) || '
              WHERE (('|| quote_ident(pFkeyCol) || '=' || pSourceId || ')
                 AND ('|| quote_ident(pTypeCol) || '=' || quote_literal(pType) || '));';
  END IF;

  -- actually change the foreign keys to point to the desired base table record
  EXECUTE 'UPDATE '  || quote_ident(pSchema)  || '.' || quote_ident(pTable) ||
            ' SET '  || quote_ident(pFkeyCol) || '=' || pTargetId ||
          ' WHERE ((' || quote_ident(pFkeyCol) || '=' || pSourceId || ')
               AND (' || quote_ident(pTypeCol) || '=' || quote_literal(pType) || '));';

  GET DIAGNOSTICS _counter = ROW_COUNT;

  RETURN _counter;
END;
$_$;


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

--
-- Name: FUNCTION changepseudofkeypointers(text, text, text, integer, text, text, integer, text, text, boolean); Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON FUNCTION changepseudofkeypointers(text, text, text, integer, text, text, integer, text, text, boolean) IS 'Change the data in pSchema.pTable with a pseudo-foreign key relationship to another (unnamed) table. Make pSchema.pTable point to the record with primary key pTargetId instead of the record with primary key pSourceId. pSchema.pTable cannot have a true foreign key relationship because it holds data that can point to any of several tables. The pType value in the pTypeCol column describes which table the data refer to (e.g. "T" may indicate that the current record refers to a "cntct"). If the final arg is TRUE, make a backup copy of the data in the mrgundo table.';


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

CREATE FUNCTION changepurchasedropship(pcoitemid integer, ppoitemid integer, pdropship 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
  _s RECORD;
  _w RECORD;
  _p RECORD;
  _result INTEGER;
  _poitemid INTEGER;

BEGIN

  -- Check for existing poitem for this coitem
  SELECT * INTO _p
  FROM poitem JOIN pohead ON (pohead_id=poitem_pohead_id)
  WHERE (poitem_id=pPoitemId)
    AND (poitem_order_id=pCoitemId)
    AND (poitem_order_type='S');
  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Change Purchase Drop Ship PO not found';
  END IF;

  SELECT * INTO _s
  FROM coitem JOIN cohead ON (cohead_id = coitem_cohead_id)
    LEFT OUTER JOIN shiptoinfo ON (cohead_shipto_id = shipto_id)
    LEFT OUTER JOIN cntct ON (shipto_cntct_id = cntct_id)
    LEFT OUTER JOIN addr ON (shipto_addr_id = addr_id)
  WHERE (coitem_id = pCoitemId);
  IF (NOT FOUND) THEN
    RETURN -1;
  END IF;

  IF (_p.pohead_status != 'U') THEN
    RETURN -3;
  END IF;

  SELECT deletePoitem(_p.poitem_id) INTO _result;
  IF (_result < 0) THEN
    RETURN _result;
  END IF;

  SELECT createPurchaseToSale(_s.coitem_id, _p.poitem_itemsrc_id,
                              pDropShip, _s.coitem_qtyord,
                              _s.coitem_scheddate, NULL)
     INTO _poitemid;
  
  RETURN _poitemid;

END;
$$;


ALTER FUNCTION public.changepurchasedropship(pcoitemid integer, ppoitemid integer, pdropship boolean) OWNER TO admin;

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

CREATE FUNCTION changewodates(integer, date, date, 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;
  pStartDate ALIAS FOR $2;
  pDueDate ALIAS FOR $3;
  changeChildren ALIAS FOR $4;
  _p RECORD;
  returnCode INTEGER;
  _vtemp NUMERIC;

BEGIN

  SELECT wo_status, wo_startdate, itemsite_warehous_id INTO _p
  FROM wo
  Inner Join itemsite on
      wo_itemsite_id=itemsite_id
  WHERE (wo_id=pWoid);

  IF (_p.wo_status = 'C') THEN 
    returnCode := 0;

  ELSIF (_p.wo_status IN ('R','I')) THEN
    PERFORM postEvent('RWoDueDateRequestChange', 'W', wo_id,
                      itemsite_warehous_id, formatWoNumber(wo_id),
                      NULL, NULL, pDueDate, wo_duedate)
    FROM wo JOIN itemsite ON (itemsite_id=wo_itemsite_id)
            JOIN item ON (item_id=itemsite_item_id)
    WHERE (wo_id=pWoid);

     returnCode := 0;

  END IF;
  
--  Reschedule operations if routings enabled
  IF (fetchMetricBool('Routings')) THEN

--    Reschedule wooper
    IF (fetchMetricBool('UseSiteCalendar')) THEN
      UPDATE xtmfg.wooper
      SET wooper_scheduled = calculatenextworkingdate(itemsite_warehous_id,DATE(pStartDate),
                             CAST(calculateworkdays(itemsite_warehous_id, DATE(wo_startdate), DATE(wooper_scheduled)) as INTEGER))
      FROM wo JOIN itemsite ON (wo_itemsite_id=itemsite_id)
      WHERE ( (wooper_wo_id=wo_id)
        AND   (wo_id=pWoid) );
    ELSE
      UPDATE xtmfg.wooper
      SET wooper_scheduled = (wooper_scheduled::DATE + (pStartDate - wo_startdate))
      FROM wo
      WHERE ( (wooper_wo_id=wo_id)
        AND   (wo_id=pWoid) );
    END IF;

--    Reschedule any womatl that is linked to wooper items
--    and is set to be scheduled with the wooper in question
    UPDATE womatl
    SET womatl_duedate=wooper_scheduled
    FROM xtmfg.wooper
    WHERE ( (womatl_schedatwooper)
     AND (womatl_wooper_id=wooper_id)
     AND (womatl_wo_id=pWoid) );

  END IF;

-- Reschedule any womatl that is not linked to wooper items
  UPDATE womatl
  SET womatl_duedate=pStartDate
  WHERE ( (NOT womatl_schedatwooper)
   AND (womatl_wo_id=pWoid) );

--  Reschedule the W/O
  UPDATE wo
  SET wo_startdate=pStartDate,
      wo_duedate=pDueDate
  WHERE (wo_id=pWoid);

--  Do the same for the children
  IF (changeChildren) THEN
    SELECT MAX(changeWoDates(wo_id, (pStartDate - itemsite_leadtime), pStartDate, TRUE)) INTO returnCode
    FROM wo, itemsite
    WHERE ( (wo_itemsite_id=itemsite_id)
     AND (wo_ordtype='W')
     AND (wo_ordid=pWoid) );
  END IF;

  IF (returnCode IS NULL) THEN
    returnCode := 0;
  END IF;

  RETURN returnCode;

END;
$_$;


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

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

CREATE FUNCTION changewoproject(integer, 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;
  pPrjid ALIAS FOR $2;
  changeChildren ALIAS FOR $3;
  woStatus CHAR(1);
  _result INTEGER;

BEGIN

  SELECT wo_status INTO woStatus
  FROM wo
  WHERE (wo_id=pWoid);

  UPDATE wo
  SET wo_prj_id=pPrjid
  WHERE (wo_id=pWoid);

  IF (woStatus = 'E' AND changeChildren) THEN
    _result := ( SELECT MIN(changeWoProject(wo_id, pPrjid, TRUE))
                   FROM womatl, wo
                  WHERE ((womatl_itemsite_id=wo_itemsite_id)
                    AND (wo_ordtype='W')
                    AND (womatl_wo_id=pWoid)
                    AND (wo_ordid=pWoid)) );

    UPDATE pr SET pr_prj_id=pPrjid
      FROM womatl
     WHERE ((womatl_wo_id=pWoid)
       AND  (pr_order_type='W')
       AND  (pr_order_id=womatl_id));
  ELSE
    _result = 1;
  END IF;

  RETURN _result;
END;
$_$;


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

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

CREATE FUNCTION changewoqty(integer, numeric, 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;
  pQty ALIAS FOR $2;
  changeChildren ALIAS FOR $3;
  _r RECORD;
  _result INTEGER := 1;

BEGIN

  SELECT wo_qtyord, wo_status, item_fractional INTO _r
  FROM wo JOIN itemsite ON (itemsite_id=wo_itemsite_id)
          JOIN item ON (item_id=itemsite_item_id)
  WHERE (wo_id=pWoid);

  IF (_r.wo_qtyord = pQty) THEN
    RETURN 0;
  END IF;

  IF (NOT _r.wo_status IN ('O','E','R','I')) THEN
    RETURN 1;
  END IF;

  IF (_r.wo_status IN ('R','I')) THEN
    PERFORM postEvent('RWoQtyRequestChange', 'W', wo_id,
                      itemsite_warehous_id, formatWoNumber(wo_id),
                      pQty, wo_qtyord, NULL, NULL)
    FROM wo JOIN itemsite ON (itemsite_id=wo_itemsite_id)
            JOIN item ON (item_id=itemsite_item_id)
    WHERE (wo_id=pWoid);

     _result = 0;
  END IF;

  UPDATE wo
  SET wo_qtyord=roundQty(_r.item_fractional, pQty)
  WHERE (wo_id=pWoid);

  UPDATE womatl
  SET womatl_qtyreq=(womatl_qtyfxd + wo_qtyord * womatl_qtyper) * (1 + womatl_scrap)
  FROM wo, itemsite
  WHERE ((womatl_wo_id=wo_id)
    AND  (womatl_itemsite_id=itemsite_id)
    AND  (wo_id=pWoid));

  IF (fetchMetricBool('Routings')) THEN

      UPDATE xtmfg.wooper
         SET wooper_rntime = CASE WHEN ((booitem_rnqtyper = 0) OR (booitem_invproduomratio = 0)) THEN 0
                                  WHEN (NOT booitem_rnrpt) THEN 0
                                  ELSE ( ( booitem_rntime /
                                           booitem_rnqtyper /
                                           booitem_invproduomratio ) * wo_qtyord )
                             END
        FROM xtmfg.booitem, wo
       WHERE ((wooper_wo_id=wo_id)
         AND  (wooper_booitem_id=booitem_id)
         AND  (wo_id=pWoid));
  END IF;

  IF (changeChildren) THEN
    _result := ( SELECT MIN(changeWoQty(wo_id, womatl_qtyreq, TRUE))
                 FROM womatl, wo
                 WHERE ((womatl_itemsite_id=wo_itemsite_id)
                  AND (wo_ordtype='W')
                  AND (womatl_wo_id=pWoid)
                  AND (wo_ordid=pWoid)) );
  END IF;

  RETURN _result;
END;
$_$;


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

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

CREATE FUNCTION characteristicstostring(text, integer, text, text) 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
  pTargetType ALIAS FOR $1;
  pTargetId ALIAS FOR $2;
  pValKeySep ALIAS FOR $3;
  pPairSep ALIAS FOR $4;
  _string TEXT := '';
  _extra BOOLEAN := false;
  _r RECORD;
BEGIN
  FOR _r IN SELECT char_name, charass_value
              FROM charass, char
             WHERE ((charass_char_id=char_id)
               AND  (charass_target_type=pTargetType)
               AND  (charass_target_id=pTargetId)) LOOP
    IF(_extra) THEN
      _string := _string || pPairSep;
    END IF;
    _extra := true;

    _string := _string || _r.char_name || pValKeySep || _r.charass_value;
  END LOOP;

  RETURN _string;
END;
$_$;


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

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

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

BEGIN

  IF (NOT fetchMetricBool('MultiWhs')) THEN
    RETURN true;
  END IF;

  IF (NOT fetchUsrPrefBool('selectedSites')) THEN
    RETURN true;
  END IF;

  SELECT COALESCE(COUNT(*), 0) INTO _result
    FROM ( SELECT cmitem_id
             FROM cmitem JOIN itemsite ON (itemsite_id=cmitem_itemsite_id)
            WHERE ( (cmitem_cmhead_id=pCmheadid)
              AND   (itemsite_warehous_id NOT IN (SELECT usrsite_warehous_id
                                                    FROM usrsite
                                                   WHERE (usrsite_username=getEffectiveXtUser()))) )
         ) AS data;
  IF (_result > 0) THEN
    RETURN false;
  END IF;

  RETURN true;
END;
$_$;


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

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

CREATE FUNCTION checkdetailformatted(integer, integer) RETURNS SETOF checkdata
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pCheckheadid ALIAS FOR $1;
  pMaxLines ALIAS FOR $2;
  _row checkdata%ROWTYPE;
  _checkhead RECORD;
  _checkdetail RECORD;
  _rowcount INTEGER := 0;
  _page INTEGER := 1;
  _docnumber TEXT := '';
  _docreference TEXT := '';
  _docdate TEXT := '';
  _docamount TEXT := '';
  _docdiscount TEXT := '';
  _docnetamount TEXT := '';
BEGIN

-- Check header information
  SELECT checkhead_number AS checknumber,
         INITCAP(spellAmount(checkhead_amount, curr_id)) AS checkwords,
         formatDate(checkhead_checkdate) AS checkdate,
         formatMoney(checkhead_amount) AS checkamount,
         curr_symbol AS checkcurrsymbol,
         curr_abbr AS checkcurrabbr,
         curr_name AS checkcurrname,
         CASE WHEN checkhead_recip_type = 'C' THEN (SELECT cust_name
                                                      FROM custinfo
                                                     WHERE cust_id=checkhead_recip_id)
              WHEN checkhead_recip_type = 'T' THEN (SELECT taxauth_name
                                                      FROM taxauth
                                                     WHERE taxauth_id=checkhead_recip_id)
              WHEN checkhead_recip_type = 'V' THEN
                                  COALESCE((SELECT vendaddr_name
                                              FROM vendaddrinfo
                                             WHERE((UPPER(vendaddr_code)='REMIT')
                                               AND (vendaddr_vend_id=checkhead_recip_id))),
                                           (SELECT vend_name
                                              FROM vendinfo
                                             WHERE(vend_id=checkhead_recip_id)))
         END AS checkpayto,
         formatAddr(CASE WHEN checkhead_recip_type = 'C' THEN
                                                  (SELECT cntct_addr_id
                                                   FROM cntct, custinfo
                                                    WHERE((cust_cntct_id=cntct_id)
                                                      AND (cust_id=checkhead_recip_id)))
                         WHEN checkhead_recip_type = 'T' THEN 
                                                  (SELECT taxauth_addr_id
                                                     FROM taxauth
                                                    WHERE(taxauth_id=checkhead_recip_id))
                         WHEN checkhead_recip_type = 'V' THEN
                                 COALESCE((SELECT vendaddr_addr_id
                                             FROM vendaddrinfo
                                            WHERE((UPPER(vendaddr_code)='REMIT')
                                              AND (vendaddr_vend_id=checkhead_recip_id))),
                                          (SELECT vend_addr_id
                                             FROM vendinfo
                                            WHERE(vend_id=checkhead_recip_id)))
                    END) AS checkaddress,
         checkhead_for AS checkmemo
    INTO _checkhead
    FROM checkhead, curr_symbol
   WHERE((checkhead_curr_id = curr_id)
     AND (checkhead_id=pCheckheadid) );
  IF (NOT FOUND) THEN
    RETURN;
  END IF;

  _row.checkdata_page := _page;
  _row.checkdata_checknumber := _checkhead.checknumber;
  _row.checkdata_checkwords := _checkhead.checkwords;
  _row.checkdata_checkdate := _checkhead.checkdate;
  _row.checkdata_checkamount := _checkhead.checkamount;
  _row.checkdata_checkcurrsymbol := _checkhead.checkcurrsymbol;
  _row.checkdata_checkcurrabbr := _checkhead.checkcurrabbr;
  _row.checkdata_checkcurrname := _checkhead.checkcurrname;
  _row.checkdata_checkpayto := _checkhead.checkpayto;
  _row.checkdata_checkaddress := _checkhead.checkaddress;
  _row.checkdata_checkmemo := _checkhead.checkmemo;

-- Check item details
  FOR _checkdetail IN 
  SELECT  --VOUCHER-------------
    1 AS ord,
    1 AS sequence_value,
    checkitem_invcnumber,
    checkitem_ponumber,
    formatMoney(checkitem_amount) AS docnetamount,
    'Invoice#: ' || vohead_invcnumber AS docnumber,
    formatDate(vohead_docdate) AS docdate,
    vohead_reference AS docreference,
    'Voucher: ' || checkitem_vouchernumber AS vouchernumber,
    formatMoney(apopen_amount) AS docamount,
    formatMoney(checkitem_discount) AS docdiscount
  FROM checkitem, vohead, apopen
  WHERE ((checkitem_checkhead_id=pCheckheadid)
    AND  (checkitem_vouchernumber = vohead_number)
    AND  (checkitem_apopen_id = apopen_id)
    AND  (apopen_doctype = 'V'))
  
  UNION
  
  SELECT --DEBIT MEMO -------------------------
    2 AS ord,
    1 AS sequence_value,
    checkitem_invcnumber,
    checkitem_ponumber,
    formatMoney(checkitem_amount) AS f_amount,
    'Debit Memo PO#: ' || checkitem_ponumber AS doc_number,
    ''  AS f_docdate,
    'Debit Memo: ' || checkitem_vouchernumber AS doc_reference,
    checkitem_vouchernumber AS vouchernumber,
    formatMoney(apopen_amount) AS amount,
    formatMoney(checkitem_discount) AS disc_cred
  FROM checkitem, apopen
  WHERE ((checkitem_checkhead_id=pCheckheadid)
    AND  (checkitem_apopen_id = apopen_id)
    AND  (apopen_doctype = 'D'))
  
  UNION
  
  SELECT --CREDIT MEMO -------------------------
    2 AS ord,
    1 AS sequence_value,
    checkitem_invcnumber,
    checkitem_ponumber,
    formatMoney(checkitem_amount * -1.0) AS f_amount,
    'Credit Memo PO#: ' || checkitem_ponumber AS doc_number,
    ''  AS f_docdate,
    'Credit Memo: ' || checkitem_vouchernumber AS doc_reference,
    checkitem_vouchernumber AS vouchernumber,
    '' AS amount,
    formatMoney(checkitem_amount) AS disc_cred
  FROM checkitem, apopen
  WHERE ((checkitem_checkhead_id=pCheckheadid)
    AND  (checkitem_apopen_id = apopen_id)
    AND  (apopen_doctype = 'C'))
  
  UNION
  
  SELECT --CREDITs FOR VOUCHERS-----------------
    3 AS ord,
    1 AS sequence_value,
    checkitem_invcnumber,
    checkitem_ponumber,
    formatMoney(checkitem_amount) AS f_amount,
    'Invoice#: ' || vohead_invcnumber AS doc_number,
    formatDate(vohead_docdate) AS f_docdate,
    'Credit Applied: ' || apapply_source_doctype || ' ' ||
                          apapply_source_docnumber AS doc_reference,
    'Voucher: ' || checkitem_vouchernumber AS vouchernumber,
    '' AS amount,
    formatMoney((apapply_amount)) AS disc_cred
  FROM checkitem, vohead, apapply
  WHERE ((checkitem_checkhead_id=pCheckheadid)
    AND  (checkitem_vouchernumber = vohead_number)
    AND  (apapply_target_docnumber = checkitem_vouchernumber)
    AND  (apapply_target_doctype = 'V')
    AND  (apapply_source_doctype = 'C'))
  
  UNION 
  
  SELECT --CREDITs FOR DEBIT MEMOS-----------
    3 AS ord,
    1 AS sequence_value,
    checkitem_invcnumber,
    checkitem_ponumber,
    formatMoney(checkitem_amount) AS f_amount,
    'Debit Memo PO#: ' || checkitem_ponumber AS doc_number,
    '' AS f_docdate,
    'Credit Applied: ' || apapply_source_doctype || ' ' ||
                          apapply_source_docnumber AS doc_reference,
    'Debit Memo: ' || checkitem_vouchernumber AS vouchernumber,
    '' AS amount,
    formatMoney((apapply_amount)) AS disc_cred
  FROM checkitem, apopen, apapply
  WHERE ((checkitem_checkhead_id=pCheckheadid)
    AND  (checkitem_apopen_id = apopen_id)
    AND  (apopen_doctype = 'D')
    AND  (apapply_target_apopen_id = apopen_id)
    AND  (apapply_target_doctype = 'D')
    AND  (apapply_source_doctype = 'C'))
  
  UNION 
  
  SELECT --NON-VENDOR-----------------------
    4 AS ord,
    1 AS sequence_value,
    checkitem_invcnumber,
    checkitem_ponumber,
    formatMoney(checkitem_amount) AS f_amount,
    checkitem_invcnumber AS doc_number,
    formatDate(checkitem_docdate) AS f_docdate,
    '' AS doc_reference,
    '' AS vouchernumber,
    '' AS amount,
    '' AS disc_cred
  FROM checkhead LEFT OUTER JOIN
       checkitem ON (checkitem_checkhead_id=checkhead_id)
  WHERE ((checkhead_id=pCheckheadid) 
    AND  (checkhead_recip_type != 'V')) LOOP
    IF (_rowcount = pMaxLines) THEN
      _row.checkdata_docnumber := _docnumber;
      _row.checkdata_docreference := _docreference;
      _row.checkdata_docdate := _docdate;
      _row.checkdata_docamount := _docamount;
      _row.checkdata_docdiscount := _docdiscount;
      _row.checkdata_docnetamount := _docnetamount;
      RETURN NEXT _row;

-- update/reset some variables
      _rowcount = 0;
      _page := _page + 1;
      _docnumber := '';
      _docreference := '';
      _docdate := '';
      _docamount := '';
      _docdiscount := '';
      _docnetamount := '';

      _row.checkdata_page := _page;
      _row.checkdata_checknumber := _checkhead.checknumber;
      _row.checkdata_checkwords := 'VOID VOID PAGE '||_page||' OF CHECK #'||_checkhead.checknumber||' VOID VOID';
      _row.checkdata_checkdate := 'VOID VOID VOID';
      _row.checkdata_checkamount := 'VOID VOID VOID';
      --_row.checkdata_checkcurrsymbol := _checkhead.checkcurrsymbol;
      --_row.checkdata_checkcurrabbr := _checkhead.checkcurrabbr;
      --_row.checkdata_checkcurrname := _checkhead.checkcurrname;
      _row.checkdata_checkpayto := 'VOID VOID VOID';
      --_row.checkdata_checkaddress := _checkhead.checkaddress;
      _row.checkdata_checkmemo := 'VOID VOID PAGE '||_page||' OF CHECK #'||_checkhead.checknumber||' VOID VOID';
    END IF;

    _rowcount := _rowcount + 1;
    _docnumber := _docnumber || _checkdetail.docnumber || E'\n';
    _docreference := _docreference || _checkdetail.docreference || E'\n';
    _docdate := _docdate || _checkdetail.docdate || E'\n';
    _docamount := _docamount || _checkdetail.docamount || E'\n';
    _docdiscount := _docdiscount || _checkdetail.docdiscount || E'\n';
    _docnetamount := _docnetamount || _checkdetail.docnetamount || E'\n';
  END LOOP;

  _row.checkdata_docnumber := _docnumber;
  _row.checkdata_docreference := _docreference;
  _row.checkdata_docdate := _docdate;
  _row.checkdata_docamount := _docamount;
  _row.checkdata_docdiscount := _docdiscount;
  _row.checkdata_docnetamount := _docnetamount;

  RETURN NEXT _row;
  RETURN;
END;
$_$;


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

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

CREATE FUNCTION checkinvoicesiteprivs(integer) 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
  pInvcheadid ALIAS FOR $1;
  _check    BOOLEAN;
  _result   INTEGER;

BEGIN

  IF (NOT fetchMetricBool('MultiWhs')) THEN
    RETURN true;
  END IF;

  IF (NOT fetchUsrPrefBool('selectedSites')) THEN
    RETURN true;
  END IF;

  SELECT COALESCE(COUNT(*), 0) INTO _result
    FROM ( SELECT invcitem_id
             FROM invcitem
            WHERE ( (invcitem_invchead_id=pInvcheadid)
              AND   (invcitem_warehous_id <> -1)
              AND   (invcitem_warehous_id NOT IN (SELECT usrsite_warehous_id
                                                    FROM usrsite
                                                   WHERE (usrsite_username=getEffectiveXtUser()))) )
         ) AS data;
  IF (_result > 0) THEN
    RETURN false;
  END IF;

  RETURN true;
END;
$_$;


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

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

CREATE FUNCTION checkpositeprivs(integer) 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
  pPoheadid ALIAS FOR $1;
  _check    BOOLEAN;
  _result   INTEGER;

BEGIN

  IF (NOT fetchMetricBool('MultiWhs')) THEN
    RETURN true;
  END IF;

  IF (NOT fetchUsrPrefBool('selectedSites')) THEN
    RETURN true;
  END IF;

  SELECT COALESCE(COUNT(*), 0) INTO _result
    FROM ( SELECT poitem_id
             FROM poitem, itemsite
            WHERE ( (poitem_pohead_id=pPoheadid)
              AND   (poitem_itemsite_id=itemsite_id)
              AND   (itemsite_warehous_id NOT IN (SELECT usrsite_warehous_id
                                                    FROM usrsite
                                                   WHERE (usrsite_username=getEffectiveXtUser()))) )
           UNION
           SELECT pohead_warehous_id
             FROM pohead
            WHERE ( (pohead_id=pPoheadid)
              AND   (pohead_warehous_id NOT IN (SELECT usrsite_warehous_id
                                                  FROM usrsite
                                                 WHERE (usrsite_username=getEffectiveXtUser()))) )
         ) AS data;
  IF (_result > 0) THEN
    RETURN false;
  END IF;

  RETURN true;
END;
$_$;


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

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

CREATE FUNCTION checkprivilege(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
  pPrivilege ALIAS FOR $1;
  _result TEXT;
BEGIN
  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=pPrivilege)
     AND (usrgrp_username=getEffectiveXtUser()));
  IF (FOUND) THEN
    RETURN true;
  END IF;

  SELECT priv_id INTO _result
  FROM priv, usrpriv
  WHERE ((priv_id=usrpriv_priv_id)
  AND (priv_name=pPrivilege)
  AND (usrpriv_username=getEffectiveXtUser()));
  
  IF (FOUND) THEN
    RETURN true;
  ELSE
    RETURN false;
  END IF;
END;
$_$;


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

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

CREATE FUNCTION checkquotesiteprivs(integer) 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
  pQuheadid ALIAS FOR $1;

BEGIN

  RETURN checkQuoteSitePrivs(pQuheadid, NULL);

END;
$_$;


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

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

CREATE FUNCTION checkquotesiteprivs(integer, integer) 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
  pQuheadid ALIAS FOR $1;
  pWarehousid ALIAS FOR $2;
  _result   INTEGER := 0;

BEGIN

--  RAISE NOTICE 'checkQuoteSitePrivs, pQuheadid = %', pQuheadid;
--  RAISE NOTICE 'checkQuoteSitePrivs, pWarehousid = %', pWarehousid;

  IF (NOT fetchMetricBool('MultiWhs')) THEN
    RETURN true;
  END IF;

  IF ( (NOT fetchUsrPrefBool('selectedSites')) AND (pWarehousid IS NULL) ) THEN
    RETURN true;
  END IF;

  IF (pWarehousid IS NULL) THEN
    SELECT COALESCE(COUNT(*), 0) INTO _result
    FROM quitem JOIN itemsite ON (itemsite_id=quitem_itemsite_id)
                JOIN site() ON (warehous_id=itemsite_warehous_id)
    WHERE (quitem_quhead_id=pQuheadid);
  ELSE
    SELECT COALESCE(COUNT(*), 0) INTO _result
    FROM quitem JOIN itemsite ON (itemsite_id=quitem_itemsite_id)
                JOIN site() ON (warehous_id=itemsite_warehous_id)
    WHERE ( (quitem_quhead_id=pQuheadid)
      AND   (itemsite_warehous_id=pWarehousid) );
  END IF;

  IF (_result > 0) THEN
    RETURN true;
  END IF;

  RETURN false;

END;
$_$;


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

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

CREATE FUNCTION checkrasiteprivs(integer) 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
  pRaheadid ALIAS FOR $1;
  _check    BOOLEAN;
  _result   INTEGER;

BEGIN

  IF (NOT fetchMetricBool('MultiWhs')) THEN
    RETURN true;
  END IF;

  IF (NOT fetchUsrPrefBool('selectedSites')) THEN
    RETURN true;
  END IF;

  SELECT COALESCE(COUNT(*), 0) INTO _result
    FROM ( SELECT raitem_id
             FROM raitem, itemsite
            WHERE ( (raitem_rahead_id=pRaheadid)
              AND   (raitem_itemsite_id=itemsite_id)
              AND   (itemsite_warehous_id NOT IN (SELECT usrsite_warehous_id
                                                    FROM usrsite
                                                   WHERE (usrsite_username=getEffectiveXtUser()))) )
           UNION
           SELECT raitem_id
             FROM raitem, itemsite
            WHERE ( (raitem_rahead_id=pRaheadid)
              AND   (raitem_coitem_itemsite_id=itemsite_id)
              AND   (itemsite_warehous_id NOT IN (SELECT usrsite_warehous_id
                                                  FROM usrsite
                                                 WHERE (usrsite_username=getEffectiveXtUser()))) )
         ) AS data;
  IF (_result > 0) THEN
    RETURN false;
  END IF;

  RETURN true;
END;
$_$;


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

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

CREATE FUNCTION checkshipmentsiteprivs(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
  pShipheadid ALIAS FOR $1;
  _check    BOOLEAN;
  _result   INTEGER;

BEGIN

  IF (NOT fetchMetricBool('MultiWhs')) THEN
    RETURN true;
  END IF;

  IF (NOT fetchUsrPrefBool('selectedSites')) THEN
    RETURN true;
  END IF;

  SELECT COALESCE(COUNT(*), 0) INTO _result
    FROM ( SELECT coitem_id
             FROM shipitem, coitem, itemsite
            WHERE ( (shipitem_shiphead_id=pShipheadid)
              AND   (coitem_id=shipitem_orderitem_id)
              AND   (coitem_itemsite_id=itemsite_id)
              AND   (itemsite_warehous_id NOT IN (SELECT usrsite_warehous_id
                                                    FROM usrsite
                                                   WHERE (usrsite_username=getEffectiveXtUser()))) )
           UNION
           SELECT cohead_warehous_id
             FROM shipitem, coitem, cohead
            WHERE ( (shipitem_shiphead_id=pShipheadid)
              AND   (coitem_id=shipitem_orderitem_id)
              AND   (cohead_id=coitem_cohead_id)
              AND   (cohead_warehous_id NOT IN (SELECT usrsite_warehous_id
                                                  FROM usrsite
                                                 WHERE (usrsite_username=getEffectiveXtUser()))) )
         ) AS data;
  IF (_result > 0) THEN
    RETURN false;
  END IF;

  RETURN true;
END;
$_$;


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

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

CREATE FUNCTION checksositeprivs(integer) 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
  pSoheadid ALIAS FOR $1;
  _check    BOOLEAN;
  _result   INTEGER;

BEGIN

  IF (NOT fetchMetricBool('MultiWhs')) THEN
    RETURN true;
  END IF;

  IF (NOT fetchUsrPrefBool('selectedSites')) THEN
    RETURN true;
  END IF;

  SELECT COALESCE(COUNT(*), 0) INTO _result
    FROM ( SELECT coitem_id
             FROM coitem, itemsite
            WHERE ( (coitem_cohead_id=pSoheadid)
              AND   (coitem_itemsite_id=itemsite_id)
              AND   (itemsite_warehous_id NOT IN (SELECT usrsite_warehous_id
                                                    FROM usrsite
                                                   WHERE (usrsite_username=getEffectiveXtUser()))) )
           UNION
           SELECT cohead_warehous_id
             FROM cohead
            WHERE ( (cohead_id=pSoheadid)
              AND   (cohead_warehous_id NOT IN (SELECT usrsite_warehous_id
                                                  FROM usrsite
                                                 WHERE (usrsite_username=getEffectiveXtUser()))) )
         ) AS data;
  IF (_result > 0) THEN
    RETURN false;
  END IF;

  RETURN true;
END;
$_$;


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

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

CREATE FUNCTION checkvouchersiteprivs(integer) 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
  pVoheadid ALIAS FOR $1;
  _check    BOOLEAN;
  _result   INTEGER;

BEGIN

  IF (NOT fetchMetricBool('MultiWhs')) THEN
    RETURN true;
  END IF;

  IF (NOT fetchUsrPrefBool('selectedSites')) THEN
    RETURN true;
  END IF;

  SELECT COALESCE(COUNT(*), 0) INTO _result
    FROM ( SELECT voitem_id
             FROM voitem, poitem, itemsite
            WHERE ( (voitem_vohead_id=pVoheadid)
              AND   (poitem_id=voitem_poitem_id)
              AND   (poitem_itemsite_id=itemsite_id)
              AND   (itemsite_warehous_id NOT IN (SELECT usrsite_warehous_id
                                                    FROM usrsite
                                                   WHERE (usrsite_username=getEffectiveXtUser()))) )
           UNION
           SELECT pohead_warehous_id
             FROM vohead, pohead
            WHERE ( (vohead_id=pVoheadid)
              AND   (pohead_id=vohead_pohead_id)
              AND   (pohead_warehous_id NOT IN (SELECT usrsite_warehous_id
                                                  FROM usrsite
                                                 WHERE (usrsite_username=getEffectiveXtUser()))) )
         ) AS data;
  IF (_result > 0) THEN
    RETURN false;
  END IF;

  RETURN true;
END;
$_$;


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

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

CREATE FUNCTION clearnumberissue(psequence text, pnumber 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
  __seqiss	seqiss[];
  __newiss	seqiss[] := ARRAY[]::seqiss[];
  _i		INTEGER;
  _result	BOOLEAN := FALSE;
  _interval	TEXT := fetchMetricText('NumberIssueResetIntervalDays') || ' day';
  _number	INTEGER;
BEGIN
  -- get the sequence to update
  SELECT orderseq_seqiss INTO __seqiss
  FROM orderseq
  WHERE (orderseq_name=psequence);

  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Invalid orderseq_name %', psequence;
  END IF;

  IF(ARRAY_LENGTH(COALESCE(__seqiss,__newiss),1) IS NULL) THEN
    RETURN FALSE;
  END IF;

  -- build a new array sans the number we are releasing
  FOR _i IN 1..ARRAY_LENGTH(__seqiss,1)
  LOOP
    IF((__seqiss[_i]).seqiss_number = pnumber) THEN
      _result = TRUE;
    -- don't bother re-adding stale numbers
    ELSIF (now() - _interval::INTERVAL > (__seqiss[_i]).seqiss_time) THEN
      IF (_number IS NULL) THEN
        _number := (__seqiss[_i]).seqiss_number;
      ELSE 
        _number := LEAST((__seqiss[_i]).seqiss_number, _number);
      END IF;
    ELSE
      __newiss := __newiss || __seqiss[_i];
    END IF;
  END LOOP;

  -- update the order sequence with the result
  UPDATE orderseq SET
    orderseq_seqiss = __newiss
  WHERE (orderseq_name=psequence);

  -- reset to any cleared stale number
  IF(_number IS NOT NULL) THEN
    UPDATE orderseq SET
      orderseq_number = _number
    WHERE (orderseq_name=psequence);
  END IF;
  
  RETURN _result;
END;
$$;


ALTER FUNCTION public.clearnumberissue(psequence text, pnumber integer) OWNER TO admin;

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

CREATE FUNCTION clearnumberissue(psequence text, pnumber 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
  _castpnumber  INTEGER;
BEGIN
  -- for now, order numbers in the database are text but usually
  -- string representations of integers. allow for the occasional non-integer.
  BEGIN
    _castpnumber  := CAST(pnumber AS INTEGER);
  EXCEPTION WHEN cannot_coerce OR
                 invalid_text_representation OR
                 numeric_value_out_of_range
  THEN
    RAISE DEBUG 'clearNumberIssue(%, %) received an unexpected pnumber',
                  psequence, pnumber;
    RETURN FALSE;
  END;

  RETURN clearNumberIssue(psequence, _castpnumber);
END;
$$;


ALTER FUNCTION public.clearnumberissue(psequence text, pnumber text) OWNER TO admin;

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

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

BEGIN

  DELETE FROM apselect
  WHERE (apselect_id=pApselectid);

  RETURN 1;

END;
$_$;


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

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

CREATE FUNCTION closeaccountingperiod(integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pPeriodid ALIAS FOR $1;
  _r RECORD;
  _nextPeriodid INTEGER;
  _trialbalid INTEGER;
  _ending NUMERIC;
  _currYear INTEGER;
  _nextYear INTEGER;
BEGIN

--  Bypass error checking is this the the initial period
  IF ( NOT ( SELECT period_initial
             FROM period
             WHERE (period_id=pPeriodid) ) ) THEN

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

--  Make sure that the day before this period belongs to another period
    SELECT prev.period_id AS periodid, prev.period_closed AS closed INTO _r
    FROM period AS prev, period AS curr
    WHERE ( (prev.period_end = (curr.period_start - 1))
     AND (curr.period_id=pPeriodid) );
    IF (NOT FOUND) THEN
      RETURN -2;
    END IF;

--  Make sure that the previous period is closed
    IF (NOT _r.closed) THEN
      RETURN -3;
    END IF;

  END IF;

--  Make sure that there the next period is defined
  SELECT next.period_id INTO _nextPeriodid
  FROM period AS next, period AS curr
  WHERE ( (next.period_start = (curr.period_end + 1))
   AND (curr.period_id=pPeriodid) );
  IF (NOT FOUND) THEN
    RETURN -4;
  END IF;

--  Make sure that the user is not trying to prematurely close the Period
  IF ( ( SELECT (period_end >= CURRENT_DATE)
         FROM period
         WHERE (period_id=pPeriodid) ) ) THEN
    RETURN -5;
  END IF;

  SELECT yearperiod_id INTO _currYear
    FROM yearperiod, period
   WHERE ((period_end BETWEEN yearperiod_start and yearperiod_end)
     AND  (period_id=pPeriodid));
  IF (NOT FOUND) THEN
    _currYear := -1;
  END IF;

  SELECT yearperiod_id INTO _nextYear
    FROM yearperiod, period
   WHERE ((period_end BETWEEN yearperiod_start and yearperiod_end)
     AND  (period_id=_nextPeriodid));
  IF (NOT FOUND) THEN
    RETURN -6;
  END IF;

--  Walk through the entire COA, calculating the ending balance and pushing
--  it to the beginning balance for the next period
  FOR _r IN SELECT accnt_id, accnt_type IN ('E', 'R') AS revexp,
                   trialbal_id, trialbal_beginning,
                   trialbal_credits, trialbal_debits
            FROM accnt LEFT OUTER JOIN trialbal ON ( (trialbal_accnt_id=accnt_id) AND (trialbal_period_id=pPeriodid) )
            ORDER BY accnt_id LOOP
    IF (_r.trialbal_id IS NULL) THEN
      _ending = 0;

      INSERT INTO trialbal
      ( trialbal_period_id, trialbal_accnt_id,
        trialbal_beginning, trialbal_ending, trialbal_dirty,
        trialbal_credits, trialbal_debits )
      VALUES
      ( pPeriodid, _r.accnt_id,
        0, 0, FALSE,
        0, 0 );
    ELSE
      _ending = (_r.trialbal_beginning - _r.trialbal_debits + _r.trialbal_credits);

      UPDATE trialbal
      SET trialbal_ending=_ending,
          trialbal_dirty = FALSE
      WHERE (trialbal_id=_r.trialbal_id);

      PERFORM forwardUpdateTrialBalance(_r.trialbal_id);
    END IF;

    IF (_r.revexp AND _currYear != _nextYear) THEN
      _ending := 0;
    END IF;

--  Find the trialbal record for the next period
    SELECT trialbal_id INTO _trialbalid
    FROM trialbal
    WHERE ( (trialbal_period_id=_nextPeriodid)
     AND (trialbal_accnt_id=_r.accnt_id) );
    IF (FOUND) THEN
      UPDATE trialbal
      SET trialbal_beginning = (_ending + trialbal_yearend),
          trialbal_ending = (_ending + trialbal_yearend - trialbal_debits + trialbal_credits)
      WHERE (trialbal_id=_trialbalid);
    ELSE
      INSERT INTO trialbal
      ( trialbal_period_id, trialbal_accnt_id,
        trialbal_beginning, trialbal_ending, trialbal_dirty,
        trialbal_credits, trialbal_debits )
      VALUES(_nextPeriodid, _r.accnt_id,
             _ending, _ending, TRUE,
             0, 0 );
    END IF;

  END LOOP;

--  Set the period_closed flag
  UPDATE period
  SET period_closed=TRUE
  WHERE (period_id=pPeriodid);

  RETURN pPeriodid;

END;
$_$;


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

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

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

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

  IF ( ( SELECT (count(period_id) > 0)
           FROM period
          WHERE ((period_yearperiod_id=pYearPeriodid)
           AND (NOT period_closed)) ) ) THEN
    RETURN -10;
  END IF;

  IF ( ( SELECT (count(yearperiod_id) > 0)
           FROM yearperiod
          WHERE ((yearperiod_end< (
            SELECT yearperiod_end 
            FROM yearperiod 
            WHERE (yearperiod_id=pYearPeriodId))
          )
           AND (NOT yearperiod_closed)) ) ) THEN
    RETURN -11;
  END IF;

--  Should we check for a previous yearperiod existing already ?
--  If so then we should return -2 if one does not.

--  If we did the previous yearperiod we should check to make sure that
--  it is also closed. Returning -3 if it is not.

--  Make sure that the user is not trying to prematurely close the YearPeriod
  IF ( ( SELECT (yearperiod_end >= CURRENT_DATE)
           FROM yearperiod
          WHERE (yearperiod_id=pYearPeriodid) ) ) THEN
    RETURN -5;
  END IF;

--  Update the year end Retained Earnings
  SELECT updateRetainedEarnings(pYearPeriodid) INTO _result;
  IF (_result < 0) THEN
    RETURN _result;
  END IF;

  UPDATE yearperiod
    SET yearperiod_closed = TRUE
  WHERE yearperiod_id = pYearPeriodid;

  RETURN 0;

END;
$_$;


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

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

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

BEGIN

  UPDATE poitem
  SET poitem_status='C'
  WHERE (poitem_pohead_id=pPoheadid);

-- _poitemTrigger will close pohead when the last poitem is closed
--  UPDATE pohead
--  SET pohead_status='C'
--  WHERE (pohead_id=pPoheadid);

  RETURN 1;

END;
$_$;


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

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

CREATE FUNCTION closewo(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;
  pPostMaterialVariances ALIAS FOR $2;

BEGIN
  
  RETURN closeWo(pWoid, pPostMaterialVariances, CURRENT_DATE);
END;
$_$;


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

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

CREATE FUNCTION closewo(integer, boolean, 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
  pWoid ALIAS FOR $1;
  pPostMaterialVariances ALIAS FOR $2;
  pTransDate ALIAS FOR $3;
  _woNumber TEXT;
  _check CHAR;
  _itemlocSeries INTEGER := 0;

BEGIN

  --Comment this out
  --In addition to IssueToShipping driving PostProduction,
  --not PostProduction can drive IssueToShipping.
  --Must allow closing of Job items

  --If this is item type Job then we cannot close here
  --SELECT itemsite_costmethod INTO _check
  --FROM wo,itemsite
  --WHERE ((wo_id=pWoid)
  --AND (wo_itemsite_id=itemsite_id)
  --AND (itemsite_costmethod = 'J'));
  --IF (FOUND) THEN
  --  RAISE EXCEPTION 'Work orders for Job items are closed when all quantities are shipped';
  --END IF;

  SELECT formatWoNumber(pWoid) INTO _woNumber;

-- If there are any tools issued on this job then we cannot close here
  IF ( SELECT (count(*) > 0)
       FROM womatl
       JOIN itemsite ON (womatl_itemsite_id=itemsite_id)
       JOIN item ON ((itemsite_item_id=item_id) AND (item_type='T'))
       WHERE ((womatl_wo_id=pWoid)
         AND  (womatl_qtyiss > 0)) ) THEN
    RAISE EXCEPTION 'All Tools must be returned before the W/O can be closed';
  END IF;

--  Distribute any remaining wo_wipvalue to G/L - debit Inventory Cost, credit WIP
  PERFORM insertGLTransaction( 'W/O', 'WO', _woNumber, ('Manufacturing Inventory Cost Variance for ' || item_number),
                               getPrjAccntId(wo_prj_id, costcat_wip_accnt_id), 
                               getPrjAccntId(wo_prj_id, costcat_invcost_accnt_id), -1,
                               COALESCE(wo_wipvalue, 0), pTransDate )
  FROM wo, itemsite, item, costcat
  WHERE ( (wo_itemsite_id=itemsite_id)
   AND (itemsite_item_id=item_id)
   AND (itemsite_costcat_id=costcat_id)
   AND (wo_id=pWoid) );

--  Distribute any remaining wo_brdvalue to G/L - debit Inventory Cost, credit WIP
  PERFORM insertGLTransaction( 'W/O', 'WO', _woNumber, ('Breeder Inventory Cost Variance for ' || item_number),
                               getPrjAccntId(wo_prj_id, costcat_wip_accnt_id),
                               CASE WHEN(itemsite_costmethod='A') THEN costcat_asset_accnt_id
                                    ELSE getPrjAccntId(wo_prj_id, costcat_invcost_accnt_id)
                               END,
                               -1,
                               COALESCE(wo_brdvalue, 0), pTransDate )
  FROM wo, itemsite, item, costcat
  WHERE ( (wo_itemsite_id=itemsite_id)
   AND (itemsite_item_id=item_id)
   AND (itemsite_costcat_id=costcat_id)
   AND (wo_id=pWoid) );

--  Don't bother with posting variances if the qtyrcv is 0 as
--  they are meaningless.
  IF ( ( SELECT wo_qtyrcv
         FROM wo
         WHERE (wo_id=pWoid) ) > 0 ) THEN

    IF (pPostMaterialVariances) THEN
--  Post womatl variances
    INSERT INTO womatlvar ( womatlvar_number, womatlvar_subnumber, womatlvar_posted,
        womatlvar_parent_itemsite_id, womatlvar_component_itemsite_id,
        womatlvar_qtyord, womatlvar_qtyrcv,
        womatlvar_qtyiss, womatlvar_qtyfxd, womatlvar_qtyper,
        womatlvar_scrap, womatlvar_wipscrap, womatlvar_bomitem_id,
        womatlvar_notes, womatlvar_ref )
      SELECT wo_number, wo_subnumber, pTransDate,
             wo_itemsite_id, womatl_itemsite_id,
             wo_qtyord, wo_qtyrcv,
             itemuomtouom(itemsite_item_id, womatl_uom_id, NULL, womatl_qtyiss),
             itemuomtouom(itemsite_item_id, womatl_uom_id, NULL, womatl_qtyfxd),
             itemuomtouom(itemsite_item_id, womatl_uom_id, NULL, womatl_qtyper),
             womatl_scrap,
             itemuomtouom(itemsite_item_id, womatl_uom_id, NULL, womatl_qtywipscrap),
             womatl_bomitem_id,
             womatl_notes, womatl_ref             
      FROM wo, womatl, itemsite, item
      WHERE ((womatl_wo_id=wo_id)
       AND (womatl_itemsite_id=itemsite_id)
       AND (itemsite_item_id=item_id)
       AND (item_type <> 'T')      
       AND (wo_id=pWoid));
    END IF;
  END IF;

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

  UPDATE wo
  SET wo_wipvalue = 0, wo_brdvalue=0,
      wo_status='C'
  WHERE (wo_id=pWoid);

  RETURN 1;
END;
$_$;


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

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

CREATE TABLE cntct (
    cntct_id integer NOT NULL,
    cntct_crmacct_id integer,
    cntct_addr_id integer,
    cntct_first_name text,
    cntct_last_name text,
    cntct_honorific text,
    cntct_initials text,
    cntct_active boolean DEFAULT true,
    cntct_phone text,
    cntct_phone2 text,
    cntct_fax text,
    cntct_email text,
    cntct_webaddr text,
    cntct_notes text,
    cntct_title text,
    cntct_number text NOT NULL,
    cntct_middle text,
    cntct_suffix text,
    cntct_owner_username text,
    cntct_name text
);


ALTER TABLE cntct OWNER TO admin;

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

COMMENT ON TABLE cntct IS 'Contact - information on how to reach a living person';


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

CREATE FUNCTION cntct() RETURNS SETOF cntct
    LANGUAGE plpgsql
    AS $$
-- Copyright (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 cntct%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 ('MaintainAllContacts','ViewAllContacts','MaintainPersonalContacts','ViewPersonalContacts')
  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 cntct
    LOOP
      RETURN NEXT _row;
    END LOOP;
  -- Otherwise if have any other grant, must be personal privilege.
  ELSIF (_grant) THEN
    FOR _row IN 
      SELECT * FROM cntct 
      WHERE cntct_owner_username = getEffectiveXtUser()
    LOOP
      RETURN NEXT _row;
    END LOOP;
  END IF;

  RETURN;

END;
$$;


ALTER FUNCTION public.cntct() OWNER TO admin;

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

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


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

CREATE FUNCTION cntctdups(text, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean) RETURNS SETOF cntctdup
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pSearchText ALIAS FOR $1;
  pSearchContactName ALIAS FOR $2;
  pSearchPhone ALIAS FOR $3;
  pSearchEmail ALIAS FOR $4;
  pSearchNumber ALIAS FOR $5;
  pSearchName ALIAS FOR $6;
  pShowInactive ALIAS FOR $7;
  pIgnoreBlanks ALIAS FOR $8;
  pIndentedDups ALIAS FOR $9;
  pCheckHnfc ALIAS FOR $10;
  pCheckFirst ALIAS FOR $11;
  pCheckMiddle ALIAS FOR $12;
  pCheckLast ALIAS FOR $13;
  pCheckSuffix ALIAS FOR $14;
  pCheckPhone ALIAS FOR $15;
  pCheckEmail ALIAS FOR $16;
  _cntct cntctdup%ROWTYPE;
  _cntctdup cntctdup%ROWTYPE;
  _rec RECORD;
  _operator TEXT := '';
  _clause TEXT;
  _qry  TEXT := '';
  _return BOOLEAN := true;
  _text TEXT;
  _first BOOLEAN := true;

BEGIN
  -- Validate
  IF (pIndentedDups AND NOT pCheckHnfc AND NOT pCheckFirst AND NOT pCheckMiddle AND 
      NOT pCheckLast AND NOT pCheckSuffix AND NOT pCheckEmail AND NOT pCheckPhone) THEN
    RETURN;
  END IF;

  _text = quote_literal(pSearchText);

  IF (pIndentedDups) THEN
    _qry := 'SELECT 
	-1 AS cntct_id,
	-1 AS cntct_crmacct_id,
	-1 AS cntct_addr_id,';
    IF (NOT pCheckFirst) THEN
      _qry := _qry || ''''' AS ';
    END IF;
    _qry := _qry || ' cntct_first_name,';
    IF (NOT pCheckLast) THEN
      _qry := _qry || ''''' AS ';
    END IF;
    _qry := _qry || ' cntct_last_name,';
    IF (NOT pCheckHnfc) THEN
      _qry := _qry || ''''' AS ';
    END IF;
    _qry := _qry || ' cntct_honorific,';
    _qry := _qry || ' '''' AS cntct_initials,';
    _qry := _qry || ' NULL AS cntct_active,';
    IF (NOT pCheckPhone) THEN
      _qry := _qry || ''''' AS ';
    END IF;
    _qry := _qry || ' cntct_phone,';
    IF (NOT pCheckPhone) THEN
      _qry := _qry || ''''' AS ';
    END IF;
    _qry := _qry || ' cntct_phone2,';
    _qry := _qry || ' '''' AS cntct_fax,';
    IF (NOT pCheckEmail) THEN
      _qry := _qry || ''''' AS ';
    END IF;
    _qry := _qry || ' cntct_email,';
    _qry := _qry || ' '''' AS cntct_webaddr,';
    _qry := _qry || ' '''' AS cntct_notes,';
    _qry := _qry || ' '''' AS cntct_title,';
    _qry := _qry || ' '''' AS cntct_number,';
    IF (NOT pCheckMiddle) THEN
      _qry := _qry || ''''' AS ';
    END IF;
    _qry := _qry || ' cntct_middle,';
    IF (NOT pCheckSuffix) THEN
      _qry := _qry || ''''' AS ';
    END IF;
    _qry := _qry || ' cntct_suffix,';
    _qry := _qry || ' '''' AS cntct_owner_username,';
    _qry := _qry || ' '''' AS cntct_name,';
    _qry := _qry || ' '''' AS crmacct_number, ';
    _qry := _qry || ' '''' AS crmacct_name, ';
    _qry := _qry || ' NULL AS addr_id,
		NULL AS addr_active,
		'''' AS addr_line1,
		'''' AS addr_line2,
		'''' AS addr_line3,
		'''' AS addr_city,
		'''' AS addr_state,
		'''' AS addr_postalcode,
		'''' AS addr_country,
		'''' AS addr_notes,
		'''' AS addr_number,
		cntctdup_level FROM (';
  END IF;
    _clause := 'SELECT 
		cntct_id,
		cntct_crmacct_id,
		cntct_addr_id,
		UPPER(cntct_first_name) AS cntct_first_name,
		UPPER(cntct_last_name) AS cntct_last_name,
		UPPER(cntct_honorific) AS cntct_honorific,
		cntct_initials,
		cntct_active,
		cntct_phone,
		cntct_phone2,
		cntct_fax,
		UPPER(cntct_email) AS cntct_email,
		cntct_webaddr,
		cntct_notes,
		cntct_title,
		cntct_number,
		UPPER(cntct_middle) AS cntct_middle,
		UPPER(cntct_suffix) AS cntct_suffix,
		cntct_owner_username,
                cntct_name,
		crmacct_number, 
		crmacct_name,
		addr_id,
		addr_active,
		addr_line1,
		addr_line2,
		addr_line3,
		addr_city,
		addr_state,
		addr_postalcode,
		addr_country,
		addr_notes,
		addr_number,
		0 AS cntctdup_level
             FROM cntct()
               LEFT OUTER JOIN crmacct ON (cntct_crmacct_id=crmacct_id) 
               LEFT OUTER JOIN addr ON (cntct_addr_id=addr_id) 
	     WHERE ';

  IF (NOT pIndentedDups) THEN
    WHILE position('UPPER' in _clause) > 0
    LOOP
      _clause := regexp_replace(_clause, 'UPPER', '');
    END LOOP;
  END IF;

  _qry := _qry || _clause;
  	   
  IF (NOT pShowInactive) THEN
    _qry := _qry || ' cntct_active AND ';
  END IF;

  IF (pIgnoreBlanks) THEN
    _qry := _qry || ' (COALESCE(LENGTH(cntct_first_name || cntct_last_name),0) > 0) AND ';
  END IF;

    _qry := _qry || '(false ';

  IF (pSearchNumber) THEN
    _qry := _qry || ' OR (crmacct_number ~* ' || quote_literal(pSearchText) || ') ';
  END IF;

  IF (pSearchName) THEN
    _qry := _qry || ' OR (crmacct_name ~* ' || quote_literal(pSearchText) || ') ';
  END IF;

  IF (pSearchContactName) THEN
    _qry := _qry || ' OR (cntct_first_name || '' '' || cntct_last_name ~* ' || quote_literal(pSearchText) || ') ';
  END IF;
  
  IF (pSearchPhone) THEN
    _qry := _qry || ' OR (cntct_phone || '' '' || cntct_phone2 || '' '' || cntct_fax ~* ' || quote_literal(pSearchText) || ') ';
  END IF;

  IF (pSearchEmail) THEN
    _qry := _qry || ' OR (cntct_email ~* ' || quote_literal(pSearchText) || ') ';
  END IF;

  _qry := _qry || ' ) ';
  
  IF (pIndentedDups) THEN
    _qry := _qry || ') data';
    _clause := ' GROUP BY cntctdup_level';
    IF (pCheckHnfc) THEN
      _clause := _clause || ',cntct_honorific';
    END IF;
    IF (pCheckFirst) THEN
      _clause := _clause || ',cntct_first_name';
    END IF;
    IF (pCheckMiddle) THEN
      _clause := _clause || ',cntct_middle';
    END IF;
    IF (pCheckLast) THEN
      _clause := _clause || ',cntct_last_name';
    END IF;
    IF (pCheckSuffix) THEN
      _clause := _clause || ',cntct_suffix';
    END IF;
    IF (pCheckEmail) THEN
      _clause := _clause || ',cntct_email';
    END IF;
    IF (pCheckPhone) THEN
      _clause := _clause || ',cntct_phone';
      _clause := _clause || ',cntct_phone2';
    END IF;

    _qry := _qry || _clause; 

    _clause := ' HAVING(';
    IF (pCheckHnfc) THEN
      _clause := _clause || 'OR COUNT(cntct_honorific) > 1 ';
    END IF;
    IF (pCheckFirst) THEN
      _clause := _clause || 'OR COUNT(cntct_first_name) > 1 ';
    END IF;
    IF (pCheckMiddle) THEN
      _clause := _clause || 'OR COUNT(cntct_middle) > 1 ';
    END IF;
    IF (pCheckLast) THEN
      _clause := _clause || 'OR COUNT(cntct_last_name) > 1 ';
    END IF;
    IF (pCheckSuffix) THEN
      _clause := _clause || 'OR COUNT(cntct_suffix) > 1 ';
    END IF;
    IF (pCheckEmail) THEN
      _clause := _clause || 'OR COUNT(cntct_email) > 1 ';
    END IF;
    IF (pCheckPhone) THEN
      _clause := _clause || 'OR (COUNT(cntct_phone) > 1 AND LENGTH(cntct_phone) > 0) ';
      _clause := _clause || 'OR (COUNT(cntct_phone2) > 1 AND LENGTH(cntct_phone2) > 0) ';
    END IF;
    _clause := _clause || ') ';
    _clause := overlay(_clause placing '' from 9 for 2);

    IF (pCheckHnfc) THEN
      _clause := _clause || 'AND LENGTH(cntct_honorific) > 0 ';
    END IF;
    IF (pCheckFirst) THEN
      _clause := _clause || 'AND LENGTH(cntct_first_name) > 0  ';
    END IF;
    IF (pCheckMiddle) THEN
      _clause := _clause || 'AND LENGTH(cntct_middle) > 0  ';
    END IF;
    IF (pCheckLast) THEN
      _clause := _clause || 'AND LENGTH(cntct_last_name) > 0  ';
    END IF;
    IF (pCheckSuffix) THEN
      _clause := _clause || 'AND LENGTH(cntct_suffix) > 0  ';
    END IF;
    IF (pCheckEmail) THEN
      _clause := _clause || 'AND LENGTH(cntct_email) > 0  ';
    END IF;
    
    _qry := _qry || _clause;
  END IF;

  _qry := _qry || ' ORDER BY cntct_last_name, cntct_first_name;'; 

-- raise exception '%',_qry;
  FOR _cntct IN
    EXECUTE _qry
  LOOP

    RETURN NEXT _cntct;

    -- If duplicates, get duplicates
    IF (pIndentedDups) THEN
    
      _qry := 'SELECT                
                 cntct.*,
                 crmacct_number, 
                 crmacct_name,
                 addr.*,
                 1 AS cntctdup_level
               FROM cntct()
                 LEFT OUTER JOIN crmacct ON (cntct_crmacct_id=crmacct_id) 
                 LEFT OUTER JOIN addr ON (cntct_addr_id=addr_id)
               WHERE (true) ';

      IF (pCheckHnfc) THEN
        _qry := _qry || ' AND (UPPER(cntct_honorific)=' || quote_literal(_cntct.cntct_honorific) || ')';
      END IF;

      IF (pCheckFirst) THEN
        _qry := _qry || ' AND (UPPER(cntct_first_name)=' || quote_literal(_cntct.cntct_first_name) || ')';
      END IF;

      IF (pCheckMiddle) THEN
        _qry := _qry || ' AND (UPPER(cntct_middle)=' || quote_literal(_cntct.cntct_middle) || ')';
      END IF;

      IF (pCheckLast) THEN
        _qry := _qry || ' AND (UPPER(cntct_last_name)=' || quote_literal(_cntct.cntct_last_name) || ')';
      END IF;

      IF (pCheckSuffix) THEN
        _qry := _qry || ' AND (UPPER(cntct_suffix)=' ||  quote_literal(_cntct.cntct_suffix) || ')';
      END IF;

      IF (pCheckPhone) THEN
        _qry := _qry || ' AND (cntct_phone=' || quote_literal(_cntct.cntct_phone)  || ')';
      END IF;

      IF (pCheckEmail) THEN
        _qry := _qry || ' AND (UPPER(cntct_email)=' || quote_literal(_cntct.cntct_email) || ')';
      END IF;

-- raise exception '%',_qry;
      FOR _cntctdup IN
        EXECUTE _qry
      LOOP
        RETURN NEXT _cntctdup;
      END LOOP;

    END IF;
    
  END LOOP;

  RETURN;
END;
$_$;


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

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

CREATE FUNCTION cntctmerge(integer, integer, boolean) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2015 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pSourceCntctId ALIAS FOR $1;
  pTargetCntctId ALIAS FOR $2;
  pPurge ALIAS FOR $3;
  _fk		RECORD;
  _pk   	RECORD;
  _sel		RECORD;
  _seq  	INTEGER;
  _col		TEXT;
  _pkcol  	TEXT;
  _qry  	TEXT;
  _multi	BOOLEAN;

BEGIN
  -- Validate
  IF (pSourceCntctId IS NULL) THEN
    RAISE EXCEPTION 'Source contact id can not be null';
  ELSIF (pTargetCntctId IS NULL) THEN
    RAISE EXCEPTION 'Target contact id can not be null';
  ELSIF (pPurge IS NULL) THEN
    RAISE EXCEPTION 'Purge flag can not be null';
  END IF;
  
  -- Determine where this contact is used by analyzing foreign key linkages and update each
  FOR _fk IN
    SELECT pg_namespace.nspname AS schemaname, con.relname AS tablename, conkey AS seq, conrelid AS class_id 
    FROM pg_constraint, pg_class f, pg_class con, pg_namespace
    WHERE confrelid=f.oid
    AND conrelid=con.oid
    AND f.relname = 'cntct'
    AND con.relnamespace=pg_namespace.oid
    AND con.relname NOT IN ('cntctsel', 'cntctmrgd', 'mrghist','trgthist')
  LOOP
    -- Validate
    IF (ARRAY_UPPER(_fk.seq,1) > 1) THEN
      RAISE EXCEPTION 'Updates to tables where the contact is one of multiple foreign key columns is not supported. Error on Table: %',
        pg_namespace.nspname || '.' || con.relname;
    END IF;
    
    _seq := _fk.seq[1];

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

    IF (NOT pPurge) THEN
    -- Cache what we're going to do so we can restore if need be.
    -- Start by determining the primary key column for this table.
      _multi := false;
      _qry := 'SELECT pg_attribute.attname AS key
               FROM pg_attribute, pg_class 
               WHERE pg_class.relnamespace = (
                 SELECT oid 
                 FROM pg_namespace 
                 WHERE pg_namespace.nspname = ''' || _fk.schemaname || ''') 
                AND  pg_class.oid IN (
                 SELECT indexrelid 
                 FROM pg_index 
                 WHERE indisprimary = true 
                  AND indrelid IN (
                    SELECT oid 
                    FROM pg_class 
                    WHERE lower(relname) = ''' || _fk.tablename || ''')) 
                AND pg_attribute.attrelid = pg_class.oid 
                AND pg_attribute.attisdropped = false 
               ORDER BY pg_attribute.attnum;';

      FOR _pk IN 
        EXECUTE _qry
      LOOP
        IF (_multi) THEN
          RAISE EXCEPTION 'Reference tables with composite primary keys not supported.  Try the merge and purge option.';
        END IF;
        _pkcol := _pk.key;
        _multi := true;
      END LOOP;

      -- Gather and store the history
      _qry := 'INSERT INTO mrghist 
               SELECT ' || pSourceCntctId || ', ''' 
                        || _fk.schemaname || '.' || _fk.tablename || ''', ''' 
                        || _pkcol || ''', ' 
                        || _pkcol || ', '''
                        || _col || ''' 
               FROM ' || _fk.schemaname || '.' || _fk.tablename || '
               WHERE (' || _col || '=' || pSourceCntctId || ');';
                   --           raise exception '%',_qry;
      EXECUTE _qry;
      
    END IF;

    -- Merge references
    _qry := 'UPDATE ' || _fk.schemaname || '.' || _fk.tablename ||
            ' SET ' || _col || '=' || pTargetCntctId ||
            ' WHERE (' || _col || '=' || pSourceCntctId || ');';
            
    EXECUTE _qry;
         
  END LOOP;

  -- Merge cases with no foreign key
  IF (NOT pPurge) THEN
    INSERT INTO mrghist 
    SELECT pSourceCntctId,
      'comment',
      'comment_id', 
      comment_id,
      'comment_source_id'
    FROM comment
    WHERE ((comment_source_id= pSourceCntctId)
    AND (comment_source='T'));

    INSERT INTO mrghist 
    SELECT pSourceCntctId,
      'docass',
      'docass_id', 
      docass_id,
      'docass_source_id'
    FROM docass
    WHERE ((docass_source_id= pSourceCntctId)
    AND (docass_source_type='T'));

    INSERT INTO mrghist 
    SELECT pSourceCntctId,
      'docass',
      'docass_id', 
      docass_id,
      'docass_target_id'
    FROM docass
    WHERE ((docass_target_id= pSourceCntctId)
    AND (docass_target_type='T'));

    INSERT INTO mrghist 
    SELECT pSourceCntctId,
      'vendinfo',
      'vend_id', 
      vend_id,
      'vend_cntct1_id'
    FROM vendinfo
    WHERE (vend_cntct1_id=pSourceCntctId);

    INSERT INTO mrghist 
    SELECT pSourceCntctId,
      'vendinfo',
      'vend_id', 
      vend_id,
      'vend_cntct2_id'
    FROM vendinfo
    WHERE (vend_cntct2_id=pSourceCntctId);

    IF (fetchMetricBool('EnableBatchManager') AND packageIsEnabled('xtbatch')) THEN
      INSERT INTO mrghist 
      SELECT pSourceCntctId,
      'xtbatch.emlassc',
      'emlassc_id', 
      emlassc_id,
      'emlassc_assc_id'
      FROM xtbatch.emlassc
      WHERE ((emlassc_assc_id= pSourceCntctId)
      AND (emlassc_type='T'));
    END IF;
  END IF;

  UPDATE comment
  SET comment_source_id = pTargetCntctId
  WHERE ((comment_source = 'T')
   AND (comment_source_id = pSourceCntctId));

  UPDATE docass
  SET docass_source_id = pTargetCntctId
  WHERE ((docass_source_type = 'T')
   AND (docass_source_id = pSourceCntctId));

  UPDATE docass
  SET docass_target_id = pTargetCntctId
  WHERE ((docass_target_type = 'T')
   AND (docass_target_id = pSourceCntctId));

  UPDATE vendinfo
  SET vend_cntct1_id = pTargetCntctId
  WHERE (vend_cntct1_id = pSourceCntctId);

  UPDATE vendinfo
  SET vend_cntct2_id = pTargetCntctId
  WHERE (vend_cntct2_id = pSourceCntctId);

  IF (fetchMetricBool('EnableBatchManager') AND packageIsEnabled('xtbatch')) THEN
    UPDATE xtbatch.emlassc
    SET emlassc_assc_id = pTargetCntctId
    WHERE ((emlassc_type = 'T')
     AND (emlassc_assc_id = pSourceCntctId));
  END IF;

  IF (NOT pPurge) THEN
  -- Record that this has been merged if not already
    IF (SELECT (COUNT(cntctmrgd_cntct_id) = 0) 
        FROM cntctmrgd
        WHERE (cntctmrgd_cntct_id=pSourceCntctId)) THEN
      INSERT INTO cntctmrgd (cntctmrgd_cntct_id, cntctmrgd_error) VALUES (pSourceCntctId,false);
    END IF;
  END IF;

 -- Merge field detail to target
  SELECT * INTO _sel 
  FROM cntctsel 
    JOIN cntct ON (cntctsel_cntct_id=cntct_id)
  WHERE (cntctsel_cntct_id=pSourceCntctId);
  
  IF (FOUND) THEN
    IF (_sel.cntctsel_mrg_crmacct_id) THEN
      IF (NOT pPurge) THEN
        INSERT INTO trgthist
        SELECT pSourceCntctId,pTargetCntctId,'cntct_crmacct_id', cntct_crmacct_id::text || '::integer'
        FROM cntct
        WHERE (cntct_id=pTargetCntctId);
      END IF;
      UPDATE cntct SET cntct_crmacct_id=_sel.cntct_crmacct_id WHERE (cntct_id=pTargetCntctId);
    END IF;
    IF (_sel.cntctsel_mrg_addr_id) THEN
      IF (NOT pPurge) THEN
        INSERT INTO trgthist
        SELECT pSourceCntctId,pTargetCntctId,'cntct_addr_id', cntct_addr_id::text || '::integer'
        FROM cntct
        WHERE (cntct_id=pTargetCntctId);
      END IF;
      UPDATE cntct SET cntct_addr_id=_sel.cntct_addr_id WHERE (cntct_id=pTargetCntctId);
    END IF;
    IF (_sel.cntctsel_mrg_first_name) THEN
      IF (NOT pPurge) THEN
        INSERT INTO trgthist
        SELECT pSourceCntctId,pTargetCntctId,'cntct_first_name', '''' || cntct_first_name || ''''
        FROM cntct
        WHERE (cntct_id=pTargetCntctId);
      END IF;
      UPDATE cntct SET cntct_first_name=_sel.cntct_first_name WHERE (cntct_id=pTargetCntctId);
    END IF;
    IF (_sel.cntctsel_mrg_last_name) THEN
      IF (NOT pPurge) THEN
        INSERT INTO trgthist
        SELECT pSourceCntctId,pTargetCntctId,'cntct_last_name', '''' || cntct_last_name || ''''
        FROM cntct
        WHERE (cntct_id=pTargetCntctId);
      END IF;
      UPDATE cntct SET cntct_last_name=_sel.cntct_last_name WHERE (cntct_id=pTargetCntctId);
    END IF;
    IF (_sel.cntctsel_mrg_honorific) THEN
      IF (NOT pPurge) THEN
        INSERT INTO trgthist
        SELECT pSourceCntctId,pTargetCntctId,'cntct_honorific', '''' || cntct_honorific || ''''
        FROM cntct
        WHERE (cntct_id=pTargetCntctId);
      END IF;
      UPDATE cntct SET cntct_honorific=_sel.cntct_honorific WHERE (cntct_id=pTargetCntctId);
    END IF;
    IF (_sel.cntctsel_mrg_initials) THEN
      IF (NOT pPurge) THEN
        INSERT INTO trgthist
        SELECT pSourceCntctId,pTargetCntctId,'cntct_initials', '''' || cntct_initials || ''''
        FROM cntct
        WHERE (cntct_id=pTargetCntctId);
      END IF;
      UPDATE cntct SET cntct_initials=_sel.cntct_initials WHERE (cntct_id=pTargetCntctId);
    END IF;
    IF (_sel.cntctsel_mrg_phone) THEN
      IF (NOT pPurge) THEN
        INSERT INTO trgthist
        SELECT pSourceCntctId,pTargetCntctId,'cntct_phone', '''' || cntct_phone || ''''
        FROM cntct
        WHERE (cntct_id=pTargetCntctId);
      END IF;
      UPDATE cntct SET cntct_phone=_sel.cntct_phone WHERE (cntct_id=pTargetCntctId);
    END IF;
    IF (_sel.cntctsel_mrg_phone2) THEN
      IF (NOT pPurge) THEN
        INSERT INTO trgthist
        SELECT pSourceCntctId,pTargetCntctId,'cntct_phone2', '''' || cntct_phone2 || ''''
        FROM cntct
        WHERE (cntct_id=pTargetCntctId);
      END IF;
      UPDATE cntct SET cntct_phone2=_sel.cntct_phone2 WHERE (cntct_id=pTargetCntctId);
    END IF;
    IF (_sel.cntctsel_mrg_fax)  THEN
      IF (NOT pPurge) THEN
        INSERT INTO trgthist
        SELECT pSourceCntctId,pTargetCntctId,'cntct_fax', '''' || cntct_fax || ''''
        FROM cntct
        WHERE (cntct_id=pTargetCntctId);
      END IF;
      UPDATE cntct SET cntct_fax=_sel.cntct_fax WHERE (cntct_id=pTargetCntctId);
    END IF;
    IF (_sel.cntctsel_mrg_email)  THEN
      IF (NOT pPurge) THEN
        INSERT INTO trgthist
        SELECT pSourceCntctId,pTargetCntctId,'cntct_email', '''' || cntct_email || ''''
        FROM cntct
        WHERE (cntct_id=pTargetCntctId);
      END IF;
      UPDATE cntct SET cntct_email=_sel.cntct_email WHERE (cntct_id=pTargetCntctId);
    END IF;
    IF (_sel.cntctsel_mrg_webaddr) THEN
      IF (NOT pPurge) THEN
        INSERT INTO trgthist
        SELECT pSourceCntctId,pTargetCntctId,'cntct_webaddr', '''' || cntct_webaddr || ''''
        FROM cntct
        WHERE (cntct_id=pTargetCntctId);
      END IF;
      UPDATE cntct SET cntct_webaddr=_sel.cntct_webaddr WHERE (cntct_id=pTargetCntctId);
    END IF;
    IF (_sel.cntctsel_mrg_notes) THEN
      IF (NOT pPurge) THEN
        INSERT INTO trgthist
        SELECT pSourceCntctId,pTargetCntctId,'cntct_notes', '''' || cntct_notes || ''''
        FROM cntct
        WHERE (cntct_id=pTargetCntctId);
      END IF;
      UPDATE cntct SET cntct_notes=cntct_notes || '

      ' || _sel.cntct_notes WHERE (cntct_id=pTargetCntctId);
    END IF;
    IF (_sel.cntctsel_mrg_title) THEN
      IF (NOT pPurge) THEN
        INSERT INTO trgthist
        SELECT pSourceCntctId,pTargetCntctId,'cntct_title', '''' || cntct_title || ''''
        FROM cntct
        WHERE (cntct_id=pTargetCntctId);
      END IF;
      UPDATE cntct SET cntct_title=_sel.cntct_title WHERE (cntct_id=pTargetCntctId);
    END IF;
    IF (_sel.cntctsel_mrg_middle) THEN
      IF (NOT pPurge) THEN
        INSERT INTO trgthist
        SELECT pSourceCntctId,pTargetCntctId,'cntct_middle', '''' || cntct_middle || ''''
        FROM cntct
        WHERE (cntct_id=pTargetCntctId);
      END IF;
      UPDATE cntct SET cntct_middle=_sel.cntct_middle WHERE (cntct_id=pTargetCntctId);
    END IF;
    IF (_sel.cntctsel_mrg_suffix) THEN
      IF (NOT pPurge) THEN
        INSERT INTO trgthist
        SELECT pSourceCntctId,pTargetCntctId,'cntct_suffix', '''' || cntct_suffix || ''''
        FROM cntct
        WHERE (cntct_id=pTargetCntctId);
      END IF;
      UPDATE cntct SET cntct_suffix=_sel.cntct_suffix WHERE (cntct_id=pTargetCntctId);
    END IF;
    IF (_sel.cntctsel_mrg_owner_username) THEN
      IF (NOT pPurge) THEN
        INSERT INTO trgthist
        SELECT pSourceCntctId,pTargetCntctId,'cntct_owner_username', '''' || cntct_owner_username || ''''
        FROM cntct
        WHERE (cntct_id=pTargetCntctId);
      END IF;
      UPDATE cntct SET cntct_owner_username=_sel.cntct_owner_username WHERE (cntct_id=pTargetCntctId);
    END IF;
  ELSE
    RAISE EXCEPTION 'Source Contact not Found';
  END IF;

  -- Disposition source contact
  IF (pPurge) THEN
    DELETE FROM cntct WHERE cntct_id = pSourceCntctId;
  END IF;

  -- Deactivate contact
  UPDATE cntct SET cntct_active = false WHERE (cntct_id=pSourceCntctId);
  
  -- Clean up
  DELETE FROM cntctsel WHERE (cntctsel_cntct_id=pSourceCntctId);

  RETURN true;
END;
$_$;


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

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

CREATE FUNCTION cntctrestore(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
  pCntctId ALIAS FOR $1;
  _r RECORD;
  _qry TEXT;

BEGIN
  -- Validate
  SELECT * INTO _r FROM cntctmrgd WHERE (cntctmrgd_cntct_id=pCntctId);
  IF (NOT FOUND) THEN
    RETURN false;
  END IF;
  
  -- Gather the list of affected records
  FOR _r IN
    SELECT * FROM mrghist
    WHERE (mrghist_cntct_id=pCntctId)
  LOOP
    -- Restore the old references
    _qry := 'UPDATE ' || _r.mrghist_table ||
            ' SET ' || _r.mrghist_cntct_col || '=' || pCntctId ||
            ' WHERE (' || _r.mrghist_pkey_col || '=' || _r.mrghist_pkey_id || ');';
    
   EXECUTE _qry;
         
  END LOOP;

  -- Gather the list of affected fields
  FOR _r IN
    SELECT * FROM trgthist
    WHERE (trgthist_src_cntct_id=pCntctId)
  LOOP
    -- Restore the old values
    _qry := 'UPDATE cntct
              SET ' || _r.trgthist_col || '=' || _r.trgthist_value ||
            ' WHERE (cntct_id=' || _r.trgthist_trgt_cntct_id || ');';
    
   EXECUTE _qry;
         
  END LOOP;

  -- Clean up
  UPDATE cntct SET cntct_active=true WHERE (cntct_id=pCntctId);
  DELETE FROM mrghist WHERE (mrghist_cntct_id=pCntctId);
  DELETE FROM trgthist WHERE (trgthist_src_cntct_id=pCntctId);
  DELETE FROM cntctmrgd WHERE (cntctmrgd_cntct_id=pCntctId);

  RETURN true;

END;
$_$;


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

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

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

BEGIN
  -- If target, delete any other targets
  IF (pTarget) THEN
    DELETE FROM cntctsel WHERE cntctsel_target;
  END IF;
  
  -- Delete any previous selection of this contact
  DELETE FROM cntctsel WHERE cntctsel_cntct_id=pCntctId;

  -- Add this contact in appropriate selection state
  INSERT INTO cntctsel (cntctsel_cntct_id, cntctsel_target) VALUES (pCntctId,pTarget);

  RETURN true;
END;
$_$;


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

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

CREATE FUNCTION cntctselectcol(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
  pCntctId ALIAS FOR $1;
  pColNumber ALIAS FOR $2;

BEGIN

  IF (pColNumber = 2 OR pColNumber = 3) THEN
    UPDATE cntctsel SET cntctsel_mrg_crmacct_id=false WHERE (cntctsel_mrg_crmacct_id AND cntctsel_cntct_id != pCntctId);
    UPDATE cntctsel SET cntctsel_mrg_crmacct_id=true WHERE (cntctsel_cntct_id = pCntctId);
    RETURN true;
  ELSIF (pColNumber = 4) THEN
    UPDATE cntctsel SET cntctsel_mrg_honorific=false WHERE (cntctsel_mrg_honorific AND cntctsel_cntct_id != pCntctId);
    UPDATE cntctsel SET cntctsel_mrg_honorific=true WHERE (cntctsel_cntct_id = pCntctId);
    RETURN true;
  ELSIF (pColNumber = 5) THEN
    UPDATE cntctsel SET cntctsel_mrg_first_name=false WHERE (cntctsel_mrg_first_name AND cntctsel_cntct_id != pCntctId);
    UPDATE cntctsel SET cntctsel_mrg_first_name=true WHERE (cntctsel_cntct_id = pCntctId);
    RETURN true;
  ELSIF (pColNumber = 6) THEN
    UPDATE cntctsel SET cntctsel_mrg_middle=false WHERE (cntctsel_mrg_middle AND cntctsel_cntct_id != pCntctId);
    UPDATE cntctsel SET cntctsel_mrg_middle=true WHERE (cntctsel_cntct_id = pCntctId);
    RETURN true;
  ELSIF (pColNumber = 7) THEN
    UPDATE cntctsel SET cntctsel_mrg_last_name=false WHERE (cntctsel_mrg_last_name AND cntctsel_cntct_id != pCntctId);
    UPDATE cntctsel SET cntctsel_mrg_last_name=true WHERE (cntctsel_cntct_id = pCntctId);
    RETURN true;
  ELSIF (pColNumber = 8) THEN
    UPDATE cntctsel SET cntctsel_mrg_suffix=false WHERE (cntctsel_mrg_suffix AND cntctsel_cntct_id != pCntctId);
    UPDATE cntctsel SET cntctsel_mrg_suffix=true WHERE (cntctsel_cntct_id = pCntctId);
    RETURN true;
  ELSIF (pColNumber = 9) THEN
    UPDATE cntctsel SET cntctsel_mrg_initials=false WHERE (cntctsel_mrg_initials AND cntctsel_cntct_id != pCntctId);
    UPDATE cntctsel SET cntctsel_mrg_initials=true WHERE (cntctsel_cntct_id = pCntctId);
    RETURN true;
  ELSIF (pColNumber = 10) THEN
    UPDATE cntctsel SET cntctsel_mrg_phone=false WHERE (cntctsel_mrg_phone AND cntctsel_cntct_id != pCntctId);
    UPDATE cntctsel SET cntctsel_mrg_phone=true WHERE (cntctsel_cntct_id = pCntctId);
    RETURN true;
  ELSIF (pColNumber = 11) THEN
    UPDATE cntctsel SET cntctsel_mrg_phone2=false WHERE (cntctsel_mrg_phone2 AND cntctsel_cntct_id != pCntctId);
    UPDATE cntctsel SET cntctsel_mrg_phone2=true WHERE (cntctsel_cntct_id = pCntctId);
    RETURN true;
  ELSIF (pColNumber = 12) THEN
    UPDATE cntctsel SET cntctsel_mrg_fax=false WHERE (cntctsel_mrg_fax AND cntctsel_cntct_id != pCntctId);
    UPDATE cntctsel SET cntctsel_mrg_fax=true WHERE (cntctsel_cntct_id = pCntctId);
    RETURN true;
  ELSIF (pColNumber = 13) THEN
    UPDATE cntctsel SET cntctsel_mrg_email=false WHERE (cntctsel_mrg_email AND cntctsel_cntct_id != pCntctId);
    UPDATE cntctsel SET cntctsel_mrg_email=true WHERE (cntctsel_cntct_id = pCntctId);
    RETURN true;
  ELSIF (pColNumber = 14) THEN
    UPDATE cntctsel SET cntctsel_mrg_webaddr=false WHERE (cntctsel_mrg_webaddr AND cntctsel_cntct_id != pCntctId);
    UPDATE cntctsel SET cntctsel_mrg_webaddr=true WHERE (cntctsel_cntct_id = pCntctId);
    RETURN true;
  ELSIF (pColNumber = 15) THEN
    UPDATE cntctsel SET cntctsel_mrg_title=false WHERE (cntctsel_mrg_title AND cntctsel_cntct_id != pCntctId);
    UPDATE cntctsel SET cntctsel_mrg_title=true WHERE (cntctsel_cntct_id = pCntctId);
    RETURN true;
  ELSIF (pColNumber = 16) THEN
    UPDATE cntctsel SET cntctsel_mrg_owner_username=false WHERE (cntctsel_mrg_owner_username AND cntctsel_cntct_id != pCntctId);
    UPDATE cntctsel SET cntctsel_mrg_owner_username=true WHERE (cntctsel_cntct_id = pCntctId);
    RETURN true;
  ELSIF (pColNumber = 17) THEN
    UPDATE cntctsel SET cntctsel_mrg_notes=false WHERE (cntctsel_mrg_notes AND cntctsel_cntct_id != pCntctId);
    UPDATE cntctsel SET cntctsel_mrg_notes=true WHERE (cntctsel_cntct_id = pCntctId);
    RETURN true;
  ELSIF (pColNumber >= 18) THEN
    UPDATE cntctsel SET cntctsel_mrg_addr_id=false WHERE (cntctsel_mrg_addr_id AND cntctsel_cntct_id != pCntctId);
    UPDATE cntctsel SET cntctsel_mrg_addr_id=true WHERE (cntctsel_cntct_id = pCntctId);
    RETURN true;
  END IF;

  RETURN false;
END;
$_$;


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

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

CREATE FUNCTION cntctused(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
  pCntctId ALIAS FOR $1;
  _fk RECORD;
  _r RECORD;
  _seq INTEGER;
  _col TEXT;
  _qry TEXT;

BEGIN
  -- Determine where this contact is used by analyzing foreign key linkages
  -- but ignore child tables and those with impermanent relationships
  FOR _fk IN
    SELECT pg_namespace.nspname AS schemaname, con.relname AS tablename, conkey AS seq, conrelid AS class_id 
    FROM pg_constraint, pg_class f, pg_class con, pg_namespace
    WHERE confrelid=f.oid
    AND conrelid=con.oid
    AND f.relname = 'cntct'
    AND con.relnamespace=pg_namespace.oid
    AND con.relname NOT IN ('cntctaddr', 'cntctdata', 'cntcteml',
                            'cohead',    'pohead',    'quhead',   'tohead',
                            'cntctsel',  'cntctmrgd', 'mrghist',  'trgthist')
  LOOP
    -- Validate
    IF (ARRAY_UPPER(_fk.seq,1) > 1) THEN
      RAISE EXCEPTION 'Cannot check dependencies when the contact is one of multiple foreign key columns (%.%) [xtuple: fkeycheck, -1, %, %]',
        _fk.nspname, _fk.relname, _fk.nspname, _fk.relname;
    END IF;
    
    _seq := _fk.seq[1];

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

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

    FOR _r IN 
      EXECUTE _qry
    LOOP
      RETURN true;
    END LOOP;
         
  END LOOP;

  RETURN false;

END;
$_$;


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

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

CREATE FUNCTION coheadstatecolor(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
  pCoheadId	ALIAS FOR $1;
  _shipheadid	INTEGER;
  _result	TEXT := '';

BEGIN
  
  IF (pCoheadid IS NULL) THEN
    RAISE EXCEPTION 'Customer Id is required.';
  END IF;
  
  SELECT 
    shiphead_id INTO _shipheadid
  FROM cohead
    JOIN shiphead ON ((shiphead_order_id=cohead_id)
                  AND (shiphead_order_type='SO'))
    JOIN shipitem ON (shiphead_id=shipitem_shiphead_id)
  WHERE ((cohead_id=pCoheadId)
    AND (NOT shipitem_invoiced))
  ORDER BY shiphead_id DESC
  LIMIT 1;

  IF (FOUND) THEN
    SELECT 
      CASE 
        WHEN ((shiphead_shipped) 
         AND (COALESCE(shiphead_order_id,0) > 0) 
         AND (SUM(noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned)) <= 0)) THEN 
           'altemphasis'
        WHEN ((COALESCE(cobmisc_cohead_id,0) > 0)       
         AND (SUM(noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned)) > 0)) THEN 
           'error' 
        WHEN (NOT shiphead_shipped) THEN 
          'emphasis' 
       END INTO _result
    FROM cohead
      JOIN coitem ON (cohead_id=coitem_cohead_id)
      JOIN shiphead ON ((shiphead_order_id=cohead_id)
                    AND (shiphead_order_type='SO'))
      JOIN shipitem ON (shiphead_id=shipitem_shiphead_id)
      LEFT OUTER JOIN (SELECT DISTINCT cobmisc_cohead_id FROM cobmisc) AS cobmisc ON (cobmisc_cohead_id=cohead_id) 
    WHERE (shiphead_id=_shipheadid)
    GROUP BY shiphead_id,shiphead_shipped,shiphead_order_id,cobmisc_cohead_id
    ORDER BY shiphead_id DESC;
  ELSE
    _result := '';
  END IF;
  
  RETURN _result;
  
END;
$_$;


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

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

CREATE FUNCTION compareversion(pleft text, pright text DEFAULT split_part(version(), ' '::text, 2)) 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.
-- Returns 1 if the left version is greater than the right version
-- -1 if the right is greater than the left
--  0 if the versions are equal.
-- parameter two defaults to current server version
DECLARE
  _left   SMALLINT[] := _normalizeVersion(pLeft);
  _right  SMALLINT[] := _normalizeVersion(pRight);
  _result SMALLINT   := 0;
  _major  SMALLINT   := 1;  -- 4
  _minor  SMALLINT   := 2;  -- .5
  _point  SMALLINT   := 3;  -- .6
  _qual   SMALLINT   := 4;  -- beta
  _qualn  SMALLINT   := 5;  -- 2
  _subq   SMALLINT   := 6;  -- A
BEGIN
  CASE WHEN _left[_major] > _right[_major] THEN _result :=  1;
       WHEN _left[_major] < _right[_major] THEN _result := -1;
       WHEN _left[_minor] > _right[_minor] THEN _result :=  1;
       WHEN _left[_minor] < _right[_minor] THEN _result := -1;
       WHEN _left[_point] > _right[_point] THEN _result :=  1;
       WHEN _left[_point] < _right[_point] THEN _result := -1;
       WHEN _left[_qual]  > _right[_qual]  THEN _result :=  1;
       WHEN _left[_qual]  < _right[_qual]  THEN _result := -1;
       WHEN _left[_qualn] > _right[_qualn] THEN _result :=  1;
       WHEN _left[_qualn] < _right[_qualn] THEN _result := -1;
       WHEN _left[_subq]  > _right[_subq]  THEN _result :=  1;
       WHEN _left[_subq]  < _right[_subq]  THEN _result := -1;
       WHEN _left[_subq]  = _right[_subq]  THEN _result :=  0;
  END CASE;

  RETURN _result;

END;
$$;


ALTER FUNCTION public.compareversion(pleft text, pright text) OWNER TO admin;

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

CREATE FUNCTION concataggsfunc(text, text) 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
  prevstate     ALIAS FOR $1;
  newval        ALIAS FOR $2;
BEGIN
  RETURN prevstate || newval;
END;
$_$;


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

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

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

BEGIN

  UPDATE itemloc
  SET itemloc_consolflag = TRUE
  WHERE (itemloc_itemsite_id=pItemsiteid);

  FOR _r IN SELECT itemloc_location_id, SUM(itemloc_qty) AS qty
            FROM itemloc
            WHERE (itemloc_itemsite_id=pItemsiteid)
            GROUP BY itemloc_location_id LOOP
    INSERT INTO itemloc
    ( itemloc_itemsite_id, itemloc_location_id,
      itemloc_expiration, itemloc_qty, itemloc_consolflag )
    VALUES
    ( pItemsiteid, _r.itemloc_location_id,
      endOfTime(), _r.qty, FALSE );
  END LOOP;

  DELETE FROM itemloc
  WHERE ( (itemloc_itemsite_id=pItemsiteid)
   AND (itemloc_consolflag) );

  RETURN 1;

END;
$_$;


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

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

CREATE FUNCTION convertcustomertoprospect(integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pCustId     ALIAS FOR $1;
  _c          RECORD;
BEGIN
  SELECT * INTO _c
  FROM custinfo
  WHERE (cust_id=pCustId);

  INSERT INTO prospect (
        prospect_id, prospect_active, prospect_number,
        prospect_name, prospect_cntct_id, prospect_taxzone_id,
        prospect_salesrep_id, prospect_warehous_id, prospect_comments
  ) VALUES (
       _c.cust_id, _c.cust_active, _c.cust_number,
       _c.cust_name, _c.cust_cntct_id, _c.cust_taxzone_id,
       CASE WHEN(_c.cust_salesrep_id > 0) THEN _c.cust_salesrep_id
            ELSE NULL
       END,
       CASE WHEN(_c.cust_preferred_warehous_id > 0) THEN _c.cust_preferred_warehous_id
            ELSE NULL
       END,
       _c.cust_comments);

  DELETE FROM custinfo WHERE (cust_id=pCustId);

  RETURN pCustId;
END;
$_$;


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

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

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


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

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

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

BEGIN
  SELECT * INTO _p
  FROM prospect
  WHERE (prospect_id=pProspectId);

  IF (EXISTS(SELECT cust_id FROM custinfo WHERE cust_id=pProspectId)) THEN
    RAISE EXCEPTION '[xtuple: convertProspectToCustomer, -10]';
  END IF;

  INSERT INTO custinfo (
    cust_id,
    cust_active,
    cust_number,
    cust_name,
    cust_cntct_id,
    cust_taxzone_id,
    cust_comments,
    cust_creditstatus,
    cust_salesrep_id,
    cust_preferred_warehous_id,
    cust_terms_id,
    cust_custtype_id,
    cust_shipform_id,
    cust_shipvia,
    cust_balmethod,
    cust_ffshipto,
    cust_backorder,
    cust_partialship,
    cust_creditlmt,
    cust_creditrating,
    cust_commprcnt,
    cust_discntprcnt,
    cust_blanketpos,
    cust_shipchrg_id,
    cust_ffbillto,
    cust_usespos,
    cust_emaildelivery,
    cust_autoupdatestatus,
    cust_autoholdorders,
    cust_soemaildelivery
  )
  SELECT
    _p.prospect_id,
    _p.prospect_active,
    _p.prospect_number,
    _p.prospect_name,
    _p.prospect_cntct_id,
    _p.prospect_taxzone_id,
    _p.prospect_comments,
    'G',
    salesrep_id,
    COALESCE(_p.prospect_warehous_id, -1),
    FetchMetricValue('DefaultTerms'),
    FetchMetricValue('DefaultCustType'),
    FetchMetricValue('DefaultShipFormId'),
    COALESCE(FetchMetricValue('DefaultShipViaId'),-1),
    FetchMetricText('DefaultBalanceMethod'),
    FetchMetricBool('DefaultFreeFormShiptos'),
    FetchMetricBool('DefaultBackOrders'),
    FetchMetricBool('DefaultPartialShipments'),
    FetchMetricValue('SOCreditLimit'),
    FetchMetricText('SOCreditRate'),
    salesrep_commission,
    0,
    false,
    COALESCE(FetchMetricValue('DefaultShipChrgId'), -1),
    false,
    false,
    false,
    false,
    false,
    false
  FROM salesrep
  WHERE (salesrep_id=COALESCE(_p.prospect_salesrep_id, FetchMetricValue('DefaultSalesRep')));

  DELETE FROM prospect WHERE (prospect_id=pprospectId);

  IF (pdoquotes) THEN
    BEGIN
      FOR _q IN SELECT quhead_number, convertQuote(quhead_id) AS err
                  FROM quhead
                 WHERE ((COALESCE(quhead_expire, endOfTime()) >= CURRENT_DATE)
                    AND (quhead_cust_id=pProspectId)) LOOP
        IF (_q.err < 0) THEN
          RAISE WARNING 'Quote % for % didn''t convert to a Sales Order [xtuple: convertQuote, %]',
                       _q.quhead_number, _p.prospect_number, _q.err;
        END IF;
      END LOOP;
    EXCEPTION WHEN OTHERS THEN
      RAISE WARNING 'Ignored errors convering quotes: % %', SQLSTATE, SQLERRM;
    END;
  END IF;

  RETURN pProspectId;
END;
$_$;


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

--
