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

CREATE FUNCTION forwardupdateitemsite(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;
  _invbalid INTEGER;

BEGIN
  SELECT invbal_id INTO _r
  FROM invbal
      JOIN period ON (invbal_period_id=period_id)
  WHERE (invbal_itemsite_id=pItemsiteid)
  ORDER BY period_start
  LIMIT 1;

  IF (FOUND) THEN
    RETURN forwardUpdateInvbalance(_r.invbal_id);
  ELSE
      _invbalid := nextval('invbal_invbal_id_seq');
      
      INSERT INTO invbal
      ( invbal_id,
        invbal_period_id, invbal_itemsite_id,
        invbal_qoh_beginning, invbal_qoh_ending,
        invbal_qty_in, invbal_qty_out,
        invbal_value_beginning, invbal_value_ending,
        invbal_value_in, invbal_value_out,
        invbal_nn_beginning, invbal_nn_ending,
        invbal_nn_in, invbal_nn_out,
        invbal_nnval_beginning, invbal_nnval_ending,
        invbal_nnval_in, invbal_nnval_out,
        invbal_dirty )
      SELECT
        _invbalid,
        period_id, pItemsiteid,
        0, 0, 0, 0, 0, 0, 0, 0,
        0, 0, 0, 0, 0, 0, 0, 0,
        false
      FROM period
      ORDER BY period_start LIMIT 1;

      RETURN forwardUpdateInvbalance(_invbalid);
  END IF;

  RETURN -1;
END;
$_$;


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

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

CREATE FUNCTION forwardupdatetrialbalance(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
  pTrialbalid ALIAS FOR $1;
  _p RECORD;
  _r RECORD;
  _ending NUMERIC;
  _prevYear INTEGER;
  _currYear INTEGER;
  _prevYearClosed BOOLEAN;
  _currYearClosed BOOLEAN;
  _result INTEGER;

BEGIN

  SELECT trialbal_accnt_id, trialbal_ending,
         yearperiod_id, yearperiod_closed,
         period_end, accnt_type IN ('E', 'R') AS revexp INTO _p
  FROM trialbal, period, yearperiod, accnt
  WHERE ( (trialbal_period_id=period_id)
   AND (yearperiod_id=period_yearperiod_id)
   AND (trialbal_accnt_id=accnt_id)
   AND (trialbal_id=pTrialbalid) );

  _ending = _p.trialbal_ending;

  SELECT yearperiod_id, yearperiod_closed INTO _prevYear, _prevYearClosed
    FROM yearperiod
   WHERE (_p.period_end BETWEEN yearperiod_start AND yearperiod_end);
  IF (NOT FOUND) THEN
    _prevYear := -1;
    _prevYearClosed := false;
  END IF;

--  Find all of the subsequent periods and their trialbal, if they exist
  FOR _r IN SELECT period_id, period_end,
                   trialbal_id, trialbal_debits, trialbal_credits,
                   trialbal_yearend
            FROM period LEFT OUTER JOIN trialbal
                 ON ( (trialbal_period_id=period_id) AND (trialbal_accnt_id=_p.trialbal_accnt_id) )
            WHERE (period_start > _p.period_end)
            ORDER BY period_start LOOP

    SELECT yearperiod_id, yearperiod_closed INTO _currYear, _currYearClosed
      FROM yearperiod
     WHERE (_r.period_end BETWEEN yearperiod_start AND yearperiod_end);
    IF (NOT FOUND) THEN
      _currYear := -1;
      _currYearClosed := false;
    END IF;

    IF (_p.revexp AND _currYear != _prevYear) THEN
      _ending := 0;
      IF (_prevYearClosed) THEN
        SELECT updateRetainedEarnings(_prevYear) INTO _result;
        IF (_result < 0) THEN
          RETURN _result;
        END IF;
      END IF;
    END IF;

    _prevYear := _currYear;
    _prevYearClosed := _currYearClosed;

    IF (_r.trialbal_id IS NULL) THEN
      -- SELECT SUM(gltrans_amount) INTO _glAmount
       -- FROM gltrans
       -- WHERE ( (gltrans_date BETWEEN _r.period_start and _r.period_end )
         -- AND   (gltrans_accnt_id=_p.trialbal_accnt_id)
         -- AND   (gltrans_posted) );
        -- and change 2nd and 3rd VALUES line of INSERT to read
        --      _ending, _ending + _glAmount,
        --      noneg(0 - _glAmount), noneg(_glAmount), FALSE );

      INSERT INTO trialbal
      ( trialbal_period_id, trialbal_accnt_id,
        trialbal_beginning, trialbal_ending,
        trialbal_debits, trialbal_credits, trialbal_dirty )
      VALUES
      ( _r.period_id, _p.trialbal_accnt_id,
        _ending, _ending,
        0, 0, FALSE );
    ELSE
      UPDATE trialbal
      SET trialbal_beginning = (_ending + trialbal_yearend),
          trialbal_ending = (_ending + trialbal_yearend - _r.trialbal_debits + _r.trialbal_credits),
          trialbal_dirty = FALSE
      WHERE (trialbal_id=_r.trialbal_id);

      _ending = (_ending + _r.trialbal_yearend - _r.trialbal_debits + _r.trialbal_credits);
    END IF;
  END LOOP;

  UPDATE trialbal
  SET trialbal_dirty = FALSE
  WHERE (trialbal_id=pTrialbalid);

  RETURN pTrialbalid;

END;
$_$;


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

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

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

BEGIN

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

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

  RETURN pPeriodid;

END;
$_$;


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

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

CREATE FUNCTION freightdetail(text, integer, integer, integer, date, text, integer) 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
  pOrderType ALIAS FOR $1;
  pOrderId ALIAS FOR $2;
  pCustId ALIAS FOR $3;
  pShiptoId ALIAS FOR $4;
  pOrderDate ALIAS FOR $5;
  pShipVia ALIAS FOR $6;
  pCurrId ALIAS FOR $7;

  _row freightData%ROWTYPE;
  _order RECORD;
  _weights RECORD;
  _includepkgweight BOOLEAN := FALSE;
  _qry TEXT;
  _debug BOOLEAN := FALSE;

BEGIN
  IF (_debug) THEN
    RAISE NOTICE 'pOrderType = %', pOrderType;
    RAISE NOTICE 'pOrderId = %', pOrderId;
    RAISE NOTICE 'pCustId = %', pCustId;
    RAISE NOTICE 'pShiptoId = %', pShiptoId;
    RAISE NOTICE 'pOrderDate = %', pOrderDate;
    RAISE NOTICE 'pShipVia = %', pShipVia;
    RAISE NOTICE 'pCurrId = %', pCurrId;
  END IF;

  SELECT fetchMetricBool('IncludePackageWeight') INTO _includepkgweight;

  --Get the order header information need to match
  --against price schedules
  IF (pOrderType = 'SO') THEN
    SELECT
      cust_id AS cust_id,
      custtype_id,
      custtype_code,
      COALESCE(shipto_id, -1) AS shipto_id,
      COALESCE(shipto_num, '') AS shipto_num,
      COALESCE(pOrderDate, cohead_orderdate) AS orderdate,
      COALESCE(pShipVia, cohead_shipvia) AS shipvia,
      shipto_shipzone_id AS shipzone_id,
      COALESCE(pCurrId, cohead_curr_id) AS curr_id,
      currConcat(COALESCE(pCurrId, cohead_curr_id)) AS currAbbr
    INTO _order
      FROM cohead
      JOIN custinfo ON (cust_id=COALESCE(pCustId, cohead_cust_id))
      JOIN custtype ON (custtype_id=cust_custtype_id)
      LEFT OUTER JOIN shiptoinfo ON (shipto_id=COALESCE(pShiptoId, cohead_shipto_id))
    WHERE (cohead_id=pOrderId);

  ELSIF (pOrderType = 'QU') THEN
    SELECT
      quhead_cust_id AS cust_id,
      custtype_id,
      custtype_code,
      COALESCE(shipto_id, -1) AS shipto_id,
      COALESCE(shipto_num, '') AS shipto_num,
      quhead_quotedate AS orderdate,
      quhead_shipvia AS shipvia,
      shipto_shipzone_id AS shipzone_id,
      quhead_curr_id AS curr_id,
      currConcat(quhead_curr_id) AS currAbbr
    INTO _order
      FROM quhead
      JOIN custinfo ON (cust_id=quhead_cust_id)
      JOIN custtype ON (custtype_id=cust_custtype_id)
      LEFT OUTER JOIN shiptoinfo ON (shipto_id=quhead_shipto_id)
    WHERE (quhead_id=pOrderId);

  ELSIF (pOrderType = 'RA') THEN
    SELECT
      cust_id AS cust_id,
      custtype_id,
      custtype_code,
      COALESCE(shipto_id, -1) AS shipto_id,
      COALESCE(shipto_num, '') AS shipto_num,
      COALESCE(pOrderDate, rahead_authdate) AS orderdate,
      ''::text AS shipvia,
      shipto_shipzone_id AS shipzone_id,
      COALESCE(pCurrId, rahead_curr_id) AS curr_id,
      currConcat(COALESCE(pCurrId, rahead_curr_id)) AS currAbbr
    INTO _order
      FROM rahead
      JOIN custinfo ON (cust_id=COALESCE(pCustId, rahead_cust_id))
      JOIN custtype ON (custtype_id=cust_custtype_id)
      LEFT OUTER JOIN shiptoinfo ON (shipto_id=COALESCE(pShiptoId, rahead_shipto_id))
    WHERE (rahead_id=pOrderId);

  ELSE
    RAISE EXCEPTION 'Invalid order type.';
  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;
  END IF;

  --Get a list of aggregated weights from sites and
  --freight classes used on order lines

  IF (_includePkgWeight) THEN
    _qry := 'SELECT SUM(orderitem_qty_ordered * orderitem_qty_invuomratio * (item_prodweight + item_packweight)) AS weight, ';
  ELSE
    _qry := 'SELECT SUM(orderitem_qty_ordered * orderitem_qty_invuomratio * item_prodweight) AS weight, ';
  END IF;

  _qry := _qry || 'itemsite_warehous_id, COALESCE(item_freightclass_id, -1) AS item_freightclass_id
    FROM orderitem
    JOIN itemsite ON (itemsite_id=orderitem_itemsite_id)
    JOIN item ON (item_id=itemsite_item_id) ';

  IF (pOrderType = 'RA') THEN
    _qry := _qry || 'JOIN raitem ON ((orderitem_id=raitem_id)
    AND (raitem_disposition IN (''C'',''R'',''P''))) ';
  END IF;

  _qry := _qry || '
    WHERE ( (orderitem_orderhead_type=' || quote_literal(pOrderType) || ')
      AND (orderitem_orderhead_id=' || quote_literal(pOrderId) || ')
      AND (orderitem_status <> ''X'') )
    GROUP BY itemsite_warehous_id, item_freightclass_id;';

  FOR _weights IN
    EXECUTE _qry LOOP

    _row := calculateFreightDetail(
      _order.cust_id, --pCustId
      _order.custtype_id, --pCustTypeId
      _order.custtype_code, --pCustTypeCode
      _order.shipto_id, --pShiptoId
      _order.shipzone_id, --pShipZoneId
      _order.shipto_num, --pShiptoNum
      _order.orderdate, --pOrderDate
      _order.shipvia, --pShipVia
      _order.curr_id, --pCurrId
      _order.currAbbr, --pCurrAbbr
      _weights.itemsite_warehous_id, --pItemSiteWhsId
      _weights.item_freightclass_id, --pItemFreightclassId
      _weights.weight --pWeight
      );

    RETURN NEXT _row;

  END LOOP;
  RETURN;
END;
$_$;


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

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

CREATE FUNCTION freightdetailquote(integer, text, integer, text, date, text, text, text[]) 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;
  pCustNumber ALIAS FOR $2;
  pShiptoId ALIAS FOR $3;
  pShiptoNum ALIAS FOR $4;
  pOrderDate ALIAS FOR $5;
  pShipVia ALIAS FOR $6;
  pItemArrayType ALIAS FOR $7;
  pItemQty ALIAS FOR $8;
    -- Array item_id format = ARRAY[['300','3'],['310','50']]
    -- Array item_number format = ARRAY[['YTRUCK1','3'],['RTRUCK1','50']]
    -- Array itemsite_id format = ARRAY[['293','3'],['302','50']]

  _cust RECORD;
  _shipto RECORD;
  _curr RECORD;
  _includepkgweight BOOLEAN := FALSE;
  _order_date DATE;
  _ship_via TEXT;
  _item_num RECORD;
  _item_id RECORD;
  _weights RECORD;
  _row freightData%ROWTYPE;
  _debug BOOLEAN := FALSE;

BEGIN
-- Parameters are setup to allow this function to be called multiple ways.
-- Check parameters and lookup what is NULL.

  -- Check pCustId and pCustNumber.
  IF (pCustId IS NULL AND (pCustNumber IS NULL OR pCustNumber = '')) THEN
    RAISE EXCEPTION 'You must specify a Customer ID or Number to get a freight quote.';
  ELSIF (pCustId IS NULL AND pCustNumber IS NOT NULL) THEN
    -- Get customer info using pCustNumber.
    SELECT
      cust_id,
      cust_number,
      custtype_id,
      custtype_code,
      cust_curr_id,
      cust_shipvia
    INTO _cust
    FROM custinfo
    LEFT JOIN custtype ON cust_custtype_id = custtype_id
    WHERE 1=1
      AND cust_number = pCustNumber;
  ELSE
    -- Get customer info using pCustId.
    SELECT
      cust_id,
      cust_number,
      custtype_id,
      custtype_code,
      cust_curr_id,
      cust_shipvia
    INTO _cust
    FROM custinfo
    LEFT JOIN custtype ON cust_custtype_id = custtype_id
    WHERE 1=1
      AND cust_id = pCustId;
  END IF;

  IF(NOT FOUND) THEN
    RAISE EXCEPTION 'Invalid Customer specified when trying to get a freight quote.';
  END IF;

  -- Check pShiptoId and pShiptoNum.
  IF (pShiptoId IS NULL AND (pShiptoNum IS NULL OR pShiptoNum = '')) THEN
    -- Get Customer's default shipto.
    SELECT
      shipto_id,
      shipto_name,
      shipto_shipzone_id,
      shipto_shipvia,
      shipto_num
    INTO _shipto
    FROM shiptoinfo
    WHERE 1=1
      AND shipto_cust_id = _cust.cust_id
      AND shipto_default;
  ELSIF (pShiptoId IS NULL AND pShiptoNum IS NOT NULL) THEN
    -- Get shipto info using pShiptoNum.
    SELECT
      shipto_id,
      shipto_name,
      shipto_shipzone_id,
      shipto_shipvia,
      shipto_num
    INTO _shipto
    FROM shiptoinfo
    WHERE 1=1
      AND shipto_cust_id = _cust.cust_id
      AND shipto_num = pShiptoNum;
  ELSE
    -- Get shipto info using pShiptoId.
    SELECT
      shipto_id,
      shipto_name,
      shipto_shipzone_id,
      shipto_shipvia,
      shipto_num
    INTO _shipto
    FROM shiptoinfo
    WHERE 1=1
      AND shipto_cust_id = _cust.cust_id
      AND shipto_id = pShiptoId;
  END IF;

  IF(NOT FOUND) THEN
    RAISE EXCEPTION 'Invalid Ship-to specified when trying to get a freight quote.';
  END IF;

  -- Get curr info.
  SELECT
    curr_id,
    curr_abbr
  INTO _curr
  FROM curr_symbol
  WHERE 1=1
    AND curr_id = _cust.cust_curr_id;

  IF(NOT FOUND) THEN
    RAISE EXCEPTION 'Could not find currency when trying to get a freight quote.';
  END IF;

  -- Check pOrderDate.
  IF (pOrderDate IS NULL) THEN
    _order_date := CURRENT_DATE;
  ELSE
    _order_date := pOrderDate;
  END IF;

  -- Check pShipVia.
  IF (pShipVia IS NULL OR pShipVia = '') THEN
    IF (_shipto.shipto_shipvia IS NULL OR _shipto.shipto_shipvia = '') THEN
      _ship_via := _cust.cust_shipvia;
    ELSE
      _ship_via := _shipto.shipto_shipvia;
    END IF;
  ELSE
    _ship_via := pShipVia;
  END IF;

  -- Determine if package weight should be included in freight calculation.
  SELECT fetchMetricBool('IncludePackageWeight') INTO _includepkgweight;

  -- Check pItemQty.
  IF (pItemQty IS NULL OR array_upper(pItemQty,1) IS NULL) THEN
    -- Item Array is NULL.
    RAISE EXCEPTION 'You must specify an Item ID, Item Number or Itemsite ID to get a freight quote.';
  ELSIF (pItemArrayType = 'item_number' AND (array_upper(pItemQty,1) > 0)) THEN
    -- Using item_number.
    FOR _weights IN
      -- Get a list of aggregated weights from sites and freight classes for items.
      SELECT
        CASE WHEN _includepkgweight THEN
          SUM(qty * (item_prodweight + item_packweight))
        ELSE
          SUM(qty * (item_prodweight))
        END AS weight,
        itemsite_warehous_id,
        COALESCE(item_freightclass_id, -1) AS item_freightclass_id
      FROM
        -- Create item_number -> qty record from array.
        (SELECT
          unnest((SELECT pItemQty[1:array_upper(pItemQty,1)][1])) AS item_number,
          unnest((SELECT pItemQty[1:array_upper(pItemQty,1)][2:array_ndims(pItemQty)]))::numeric AS qty
        ) AS itemnum_qty
        JOIN item USING (item_number)
        JOIN itemsite ON item_id=itemsite_item_id
      WHERE 1=1
        AND itemsite_warehous_id = fetchprefwarehousid()
      GROUP BY
        itemsite_warehous_id,
        item_freightclass_id
    LOOP
      -- Calculate the freight detail for these item weights.
      _row := calculateFreightDetail(
        _cust.cust_id, --pCustId
        _cust.custtype_id, --pCustTypeId
        _cust.custtype_code, --pCustTypeCode
        _shipto.shipto_id, --pShiptoId
        _shipto.shipto_shipzone_id, --pShipZoneId
        _shipto.shipto_num, --pShiptoNum
        _order_date, --pOrderDate
        _ship_via, --pShipVia
        _curr.curr_id, --pCurrId
        _curr.curr_abbr, --pCurrAbbr
        _weights.itemsite_warehous_id, --pItemSiteWhsId
        _weights.item_freightclass_id, --pItemFreightclassId
        _weights.weight --pWeight
        );

      RETURN NEXT _row;
    END LOOP;

  ELSIF (pItemArrayType = 'item_id' AND (array_upper(pItemQty,1) > 0)) THEN
    -- Using item_id.
    FOR _weights IN
      -- Get a list of aggregated weights from sites and freight classes for items.
      SELECT
        CASE WHEN _includepkgweight THEN
          SUM(qty * (item_prodweight + item_packweight))
        ELSE
          SUM(qty * (item_prodweight))
        END AS weight,
        itemsite_warehous_id,
        COALESCE(item_freightclass_id, -1) AS item_freightclass_id
      FROM
        -- Create item_id -> qty record from array.
        (SELECT
          unnest((SELECT pItemQty[1:array_upper(pItemQty,1)][1]))::integer AS item_id,
          unnest((SELECT pItemQty[1:array_upper(pItemQty,1)][2:array_ndims(pItemQty)]))::numeric AS qty
        ) AS itemid_qty
        JOIN item USING (item_id)
        JOIN itemsite ON item_id=itemsite_item_id
      WHERE 1=1
        AND itemsite_warehous_id = fetchprefwarehousid()
      GROUP BY
        itemsite_warehous_id,
        item_freightclass_id
    LOOP
      -- Calculate the freight detail for these item weights.
      _row := calculateFreightDetail(
        _cust.cust_id, --pCustId
        _cust.custtype_id, --pCustTypeId
        _cust.custtype_code, --pCustTypeCode
        _shipto.shipto_id, --pShiptoId
        _shipto.shipto_shipzone_id, --pShipZoneId
        _shipto.shipto_num, --pShiptoNum
        _order_date, --pOrderDate
        _ship_via, --pShipVia
        _curr.curr_id, --pCurrId
        _curr.curr_abbr, --pCurrAbbr
        _weights.itemsite_warehous_id, --pItemSiteWhsId
        _weights.item_freightclass_id, --pItemFreightclassId
        _weights.weight --pWeight
        );

      RETURN NEXT _row;
    END LOOP;
  ELSIF (pItemArrayType = 'itemsite_id' AND (array_upper(pItemQty,1) > 0)) THEN
    -- Using itemsite_id.
    FOR _weights IN
      -- Get a list of aggregated weights from sites and freight classes for items.
      SELECT
        CASE WHEN _includepkgweight THEN
          SUM(qty * (item_prodweight + item_packweight))
        ELSE
          SUM(qty * (item_prodweight))
        END AS weight,
        itemsite_warehous_id,
        COALESCE(item_freightclass_id, -1) AS item_freightclass_id
      FROM
        -- Create itemsite_id -> qty record from array.
        (SELECT
          unnest((SELECT pItemQty[1:array_upper(pItemQty,1)][1]))::integer AS itemsite_id,
          unnest((SELECT pItemQty[1:array_upper(pItemQty,1)][2:array_ndims(pItemQty)]))::numeric AS qty
        ) AS itemsiteid_qty
        JOIN itemsite USING (itemsite_id)
        JOIN item ON item_id=itemsite_item_id
      WHERE 1=1
      GROUP BY
        itemsite_warehous_id,
        item_freightclass_id
    LOOP
      -- Calculate the freight detail for these item weights.
      _row := calculateFreightDetail(
        _cust.cust_id, --pCustId
        _cust.custtype_id, --pCustTypeId
        _cust.custtype_code, --pCustTypeCode
        _shipto.shipto_id, --pShiptoId
        _shipto.shipto_shipzone_id, --pShipZoneId
        _shipto.shipto_num, --pShiptoNum
        _order_date, --pOrderDate
        _ship_via, --pShipVia
        _curr.curr_id, --pCurrId
        _curr.curr_abbr, --pCurrAbbr
        _weights.itemsite_warehous_id, --pItemSiteWhsId
        _weights.item_freightclass_id, --pItemFreightclassId
        _weights.weight --pWeight
        );

      RETURN NEXT _row;
    END LOOP;
  ELSE -- The item array provided is invalid.
    RAISE EXCEPTION 'The Item/Itemsite array provided when trying to get a freight quote is invalid.';
  END IF;

  IF(NOT FOUND) THEN
    RAISE EXCEPTION 'Error trying to aggregated weights when getting a freight quote.';
  END IF;

  -- Print debug.
  IF (_debug) THEN
    RAISE NOTICE 'pCustId = %', _cust.cust_id;
    RAISE NOTICE 'pCustTypeId = %', _cust.custtype_id;
    RAISE NOTICE 'pCustTypeCode = %', _cust.custtype_code;
    RAISE NOTICE 'pShiptoId = %', _shipto.shipto_id;
    RAISE NOTICE 'pShipZoneId = %', _shipto.shipto_shipzone_id;
    RAISE NOTICE 'pShiptoNum = %', _shipto.shipto_num;
    RAISE NOTICE 'pOrderDate = %', _order_date;
    RAISE NOTICE 'pShipVia = %', _ship_via;
    RAISE NOTICE 'pCurrId = %', _curr.curr_id;
    RAISE NOTICE 'pCurrAbbr = %', _curr.curr_abbr;
  END IF;

  RETURN;
END;
$_$;


ALTER FUNCTION public.freightdetailquote(integer, text, integer, text, date, text, text, text[]) OWNER TO admin;

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

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

BEGIN
  IF (pordertype = 'TO' AND NOT fetchMetricBool('MultiWhs')) THEN
    RETURN 0;
  ELSIF (pordertype = 'RA' AND NOT fetchMetricBool('EnableReturnAuth')) THEN
    RETURN 0;
  END IF;

  SELECT SUM(COALESCE(recv_freight, 0)) INTO _freight
  FROM recv
  WHERE ((recv_orderitem_id=porderitemid)
    AND  (recv_posted = pposted)
    AND  (recv_order_type=pordertype));

  RETURN COALESCE(_freight, 0.0);

END;
$_$;


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

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

CREATE FUNCTION gen_salt(text) RETURNS text
    LANGUAGE c STRICT
    AS '$libdir/pgcrypto', 'pg_gen_salt';


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

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

CREATE FUNCTION gen_salt(text, integer) RETURNS text
    LANGUAGE c STRICT
    AS '$libdir/pgcrypto', 'pg_gen_salt_rounds';


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

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

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

BEGIN
  --See if revcontrol turned on
  IF (fetchmetricbool('RevControl')) THEN

    IF (pTargetType='BOM') THEN
      SELECT rev_id INTO _revid
      FROM rev
      WHERE ((rev_target_type='BOM')
      AND (rev_target_id=pTargetid)
      AND (rev_status='A'));
      IF (NOT FOUND) THEN
        _revid:=-1;
      END IF;

      ELSE IF (pTargetType='BOO') THEN
      SELECT rev_id INTO _revid
      FROM rev
      WHERE ((rev_target_type='BOO')
      AND (rev_target_id=pTargetid)
      AND (rev_status='A'));
      IF (NOT FOUND) THEN
        _revid:=-1;
      END IF;

      ELSE
        RAISE EXCEPTION 'Invalid Revision Type';
      END IF;
    END IF;

  ELSE
    _revid:=-1;
  END IF;

  RETURN _revid;

END;
$_$;


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

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

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

  SELECT addr_id INTO _returnVal
  FROM addr
  WHERE (addr_number=pAddressNumber);

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

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getaddrid(paddressnumber text) OWNER TO admin;

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

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

  RETURN _taxtypeid;
END;
$$;


ALTER FUNCTION public.getadjustmenttaxtypeid() OWNER TO admin;

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

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

  SELECT aropen_id INTO _returnVal
  FROM aropen
  WHERE ((aropen_cust_id=getCustId(pCustNumber,true))
    AND  (UPPER(aropen_doctype)=UPPER(pDocType))
    AND  (UPPER(aropen_docnumber)=UPPER(pDocNumber)));

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

  RETURN _returnVal;
END;
$_$;


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

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

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

  SELECT bankaccnt_id INTO _returnVal
  FROM bankaccnt
  WHERE (UPPER(bankaccnt_name)=UPPER(pBankAccntName));

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

  RETURN _returnVal;
END;
$_$;


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

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

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

  SELECT bomitem_id INTO _returnVal
  FROM bomitem(getItemId(pItemNumber),COALESCE(getRevId('BOM',pItemNumber,pRevision)))
  WHERE (bomitem_seqnumber=pSeqNumber);
    
  IF (_returnVal IS NULL) THEN
    RAISE EXCEPTION 'Sequence % on Bill of Material % Revision % not found.', pSeqNumber, pItemNumber, pRevision;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

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

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

  IF (NOT fetchMetricBool('Routings')) THEN
    RETURN -1;
  ELSE
    SELECT booitem_seq_id INTO _returnVal
    FROM booitem(getItemId(pItemNumber))
    WHERE (booitem_seqnumber=CAST(pSeqNumber AS integer));
  END IF;
    
  IF (_returnVal IS NULL) THEN
    RAISE EXCEPTION 'Boo Sequence % for Item % not found.', pSeqNumber, pItemNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

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

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

  SELECT budghead_id INTO _returnVal
  FROM budghead
  WHERE (budghead_name=(pBudghead));

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

  RETURN _returnVal;
END;
$_$;


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

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

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

  SELECT cashrcpt_id INTO _returnVal
  FROM cashrcpt
  WHERE ((cashrcpt_cust_id=getCustId(pCustNumber,true))
    AND  (UPPER(cashrcpt_fundstype)=UPPER(pFundsType))
    AND  (UPPER(cashrcpt_docnumber)=UPPER(pDocNumber)));

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Cash Receipt % not found.', pDocNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

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

CREATE FUNCTION getcharid(pchar text, ptype text) RETURNS integer
    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
  _returnVal INTEGER;
BEGIN
  IF (COALESCE(pChar, '') = '') THEN
    RETURN NULL;
  END IF;

  SELECT char_id INTO _returnVal
    FROM char
    JOIN charuse ON char_id = charuse_char_id
   WHERE char_name = pChar
     AND (charuse_target_type = pType
          OR (pType IN ('C', 'CT')               AND charuse_target_type = 'C')
          OR (pType IN ('SI','QI','W','PI','TI') AND charuse_target_type = 'I')
         )
   LIMIT 1;

  IF (_returnVal IS NULL) THEN
    RAISE EXCEPTION 'Characteristic % not found [xtuple: getCharId, -1, %]',
                    pChar, pChar;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getcharid(pchar text, ptype text) OWNER TO admin;

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

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

  SELECT classcode_id INTO _returnVal
  FROM classcode
  WHERE (classcode_code=pClassCode);

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

  RETURN _returnVal;
END;
$_$;


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

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

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


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

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

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

  SELECT cmhead_id INTO _returnVal
  FROM cmhead
  WHERE (UPPER(cmhead_number)=UPPER(pCreditMemoNumber))
    AND ((pPosted IS NULL) OR (cmhead_posted=pPosted));

  IF (_returnVal IS NULL) THEN
	RAISE EXCEPTION 'Credit Memo % not found.', pCreditMemoNumber;
  END IF;

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

  RETURN _returnVal;
END;
$$;


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

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

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

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

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

  RETURN _returnVal;
END;
$$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

  IF (_returnVal IS NULL) THEN
    RETURN -1;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getcompanyid(pglaccnt integer) OWNER TO admin;

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

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

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

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

  RETURN _returnVal;
END;
$$;


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

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

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

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

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

  RETURN _returnVal;
END;
$$;


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

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

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

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

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

  RETURN _returnVal;
END;
$$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

  RETURN _returnVal;
END;
$$;


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

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

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

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

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

  RETURN _returnVal;
END;
$$;


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

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

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

  RETURN _returnVal;
END;
$$;


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

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

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

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

  RETURN _returnVal;
END;
$$;


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

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

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

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

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

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

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

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

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

  RETURN _custname;
END;
$_$;


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

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

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

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

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

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

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

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

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

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

  RETURN _custnumber;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

BEGIN

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  END IF;

  RETURN;

END;
$_$;


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

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

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

BEGIN

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

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

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

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

END;
$_$;


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

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

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

BEGIN

  SELECT 'No Data' INTO _err;

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

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

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

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

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

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

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

--...for prior month

        IF (_p.flcol_priortype = 'P') THEN

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

        ELSE

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

        END IF;

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


--...for prior quarter

        IF (_p.flcol_priortype='P') THEN

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

        ELSE

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

        END IF;

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

--...for prior year

        IF (_p.flcol_prioryear='F') THEN

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

        ELSE

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

        END IF;

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

-- RETURN RESULTS

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

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

        RETURN NEXT _row;

END;
$_$;


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

--
-- Name: getfltrendhead(integer, integer[], character); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

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

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

   SELECT flhead_type INTO _type FROM flhead WHERE flhead_id = pFlheadId;

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

                        _fld[_i] := _t;

                END LOOP;

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

                                        _fld[_i] := _t;

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

                                _fld[_i] := _t;

                        END LOOP;
                END IF;
        END IF;


-- RETURN RESULTS

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

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

        RETURN NEXT _row;

END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

  RETURN _taxtypeid;
END;
$$;


ALTER FUNCTION public.getfreighttaxtypeid() OWNER TO admin;

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$$;


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

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

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

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

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

  RETURN _returnVal;
END;
$$;


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

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

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

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

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

  RETURN _returnVal;
END;
$$;


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

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

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

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

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

  RETURN _returnVal;
END;
$$;


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

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

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

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

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

  RETURN _returnVal;
END;
$$;


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

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

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

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

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

  RETURN _returnVal;
END;
$$;


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

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

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

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

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

  RETURN _returnVal;
END;
$$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

CREATE FUNCTION getinvcitemlotserial(integer) RETURNS text
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pInvcitemid ALIAS FOR $1;
  _lotserial text;
  _r RECORD;
  _first BOOLEAN;
  _newMethod BOOLEAN;
BEGIN
 
  --Test to see if Lot/Serial Enabled
  SELECT metric_value INTO _lotserial
  FROM metric
  WHERE ((metric_name='LotSerialControl')
  AND (metric_value ='t'));

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

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

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

    IF (_newMethod) THEN

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

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

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


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

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

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

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

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

  RETURN _returnVal;
END;
$$;


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

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

CREATE FUNCTION getipsitemid(pipsname text, pitemnumber text, pqtybreak numeric, pqtyuom text, ppriceuom text) RETURNS integer
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _returnVal INTEGER;
  
BEGIN
  IF (pIpsName IS NULL AND pItemNumber IS NULL AND pQtyBreak IS NULL AND pQtyUom IS NULL AND pPriceUom IS NULL) THEN
	RETURN NULL;
  END IF;

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

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

  RETURN _returnVal;
END;
$$;


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

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

CREATE FUNCTION getipsprodcatid(pipsname text, pprodcat text, pqtybreak numeric) RETURNS integer
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _returnVal INTEGER;
  
BEGIN
  IF (pIpsName IS NULL AND pProdCat IS NULL AND pQtyBreak IS NULL) THEN
	RETURN NULL;
  END IF;

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

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

  RETURN _returnVal;
END;
$$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

  SELECT getcompanyid(warehous_default_accnt_id) INTO _returnVal
    FROM whsinfo
    JOIN itemsite ON (itemsite_warehous_id=warehous_id)
  WHERE itemsite_id = pItemSite;

  IF (_returnVal IS NULL) THEN
    RETURN -1;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getitemsitecompanyid(pitemsite integer) OWNER TO admin;

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

CREATE FUNCTION getitemsiteid(text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pWarehouseCode ALIAS FOR $1;
  pItemNumber ALIAS FOR $2;
  _returnVal INTEGER;
BEGIN
  SELECT getItemsiteId(pWarehouseCode,pItemNumber,'ALL') INTO _returnVal;

  RETURN _returnVal;
END;
$_$;


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

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

CREATE FUNCTION getitemsiteid(text, text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pWarehouseCode ALIAS FOR $1;
  pItemNumber ALIAS FOR $2;
  pType ALIAS FOR $3;
  _type TEXT;
  _p RECORD;
BEGIN
  IF ((pWarehouseCode IS NULL) OR (pItemNumber IS NULL)) THEN
	RETURN NULL;
  END IF;
 
  IF UPPER(pType) NOT IN ('ALL','ACTIVE','SOLD') THEN
    RAISE EXCEPTION 'Invalid Type %. Valid Itemsite types are ALL and SOLD', pType;
  END IF;

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

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

  RETURN _p.itemsite_id;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

BEGIN

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

  SELECT accnt_type INTO _accntType
  FROM accnt
  WHERE accnt_id=pAccntId;

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

  RETURN _result;

END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

BEGIN

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

  RETURN _result;

END;
$$;


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

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

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

BEGIN

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

  RETURN _result;

END;
$$;


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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

  _charname := '';
  _first := true;

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

  RETURN _charname;
  
END
$_$;


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

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

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

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

  _charval := '';
  _first := true;

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

  RETURN _charval;
  
END
$_$;


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

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

CREATE FUNCTION getpacklistitemlotserial(integer, integer) RETURNS text
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pShipheadId ALIAS FOR $1;
  pOrderItemId ALIAS FOR $2;
  _lotserial text;
  _r RECORD;
  _first BOOLEAN;
  
BEGIN
 
  --Test to see if Lot/Serial Enabled
  SELECT metric_value INTO _lotserial
  FROM metric
  WHERE ((metric_name='LotSerialControl')
  AND (metric_value ='t'));

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

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

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


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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


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

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

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

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

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

  RETURN _returnVal;
END;
$$;


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

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

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

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

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

  RETURN _returnVal;
END;
$$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

BEGIN

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

  RETURN _minscheddate;

END;
$_$;


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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

CREATE FUNCTION getsaleslineitemid(text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pSalesOrderItem ALIAS FOR $1;
  _delpos INTEGER = 0;
  _order TEXT;
  _part TEXT;
  _ln INTEGER;
  _sn INTEGER;
BEGIN
  IF (pSalesOrderItem IS NULL) THEN
    RETURN NULL;
  END IF;
  _delpos := strpos(pSalesOrderItem, '-');
  IF (_delpos > 0) THEN
    _order := substr(pSalesOrderItem, 1, (_delpos - 1));
    _part := substr(pSalesOrderItem, (_delpos + 1));
    _delpos := strpos(_part, '.');
    IF (_delpos > 0) THEN
      _ln := CAST(substr(_part, 1, (_delpos - 1)) AS INTEGER);
      _sn := CAST(substr(_part, (_delpos + 1)) AS INTEGER);
    ELSE
      _ln := CAST(_part AS INTEGER);
      _sn := 0;
    END IF;
    RETURN getSalesLineItemId( _order, _ln, _sn );
  END IF;
  RETURN 0;
END;
$_$;


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

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

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


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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


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

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

    RETURN _candidate;

  ELSE

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

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

      RETURN _candidate;

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

      RETURN _candidate;
    END IF;
  END IF;

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

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

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

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

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

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

    RETURN _candidate;
  END IF;

  RETURN '';
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

BEGIN

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

  RETURN _result;

END;
$$;


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

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

CREATE FUNCTION getsoscheddate(integer) RETURNS date
    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
  pCoheadid ALIAS FOR $1;
  _minscheddate DATE;

BEGIN

  SELECT MIN(coitem_scheddate) INTO _minscheddate
  FROM cohead
  JOIN coitem ON (coitem_cohead_id=pCoheadid)
  WHERE true
    AND cohead_id=pCoheadid
    AND CASE WHEN (cohead_status='C')
      THEN coitem_status != 'X'
      ELSE coitem_status NOT IN ('C','X')
    END;

  RETURN _minscheddate;

END;
$_$;


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

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

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

BEGIN

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

  RETURN _result;

END;
$_$;


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

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

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

BEGIN

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

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

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

      RETURN NEXT _row;

    END LOOP;
 
  END LOOP;

END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

CREATE FUNCTION getunassignedaccntid(pcompanyid integer DEFAULT (-1)) 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
  _test INTEGER;
  _returnVal INTEGER;
BEGIN
-- In a Multi-Company scenario we have to check the unassigned accnt for the
-- correct Company.  Otherwise choose the system metric
  
  IF (fetchMetricValue('GLCompanySize') > 0) THEN -- Multi-Company
    IF (pCompanyid = -1) THEN
      RAISE EXCEPTION 'Unassigned G/L Account could not be determined for an unknown Company [xtuple: getunassignedaccntid, -1]';
    ELSE
      SELECT company_unassigned_accnt_id INTO _test
        FROM company
        WHERE company_id = pCompanyid;
    END IF;
  ELSE -- Single Company
    _test := fetchMetricValue('UnassignedAccount');
  END IF;

  IF (_test IS NULL) THEN
    RAISE EXCEPTION 'Metric not found for UnassignedAccount [xtuple: getunassignedaccntid, -2]';
  END IF;

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

  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Metric UnassignedAccount is an invalid G/L Account [xtuple: getunassignedaccntid, -3, %1]', _test;
  END IF;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getunassignedaccntid(pcompanyid integer) OWNER TO admin;

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

--
-- Name: getuomtypeid(text[]); Type: FUNCTION; Schema: public; Owner: admin
--

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

  RETURN _returnVal;
END;
$$;


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

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

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

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

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

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

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.getusrlocaleid() OWNER TO admin;

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

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

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

  RETURN _returnVal;
END;
$$;


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

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

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

BEGIN

  _sourceCounter := 0;

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

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

  END LOOP;

  RETURN _sourceCounter;

END;
$_$;


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

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

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

BEGIN

  _privCounter := 0;

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

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

  END LOOP;

  RETURN _privCounter;

END;
$_$;


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

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

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

BEGIN

  _privCounter := 0;

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

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

  END LOOP;

  RETURN _privCounter;

END;
$_$;


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

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

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

BEGIN

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

  IF (FOUND) THEN
    RETURN FALSE;
  END IF;

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

  RETURN TRUE;

END;
$_$;


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

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

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

BEGIN

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

  IF (FOUND) THEN
    RETURN FALSE;
  END IF;

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

  RETURN TRUE;

END;
$_$;


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

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

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

BEGIN

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

  IF (FOUND) THEN
    RETURN FALSE;
  END IF;

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

  NOTIFY "usrprivUpdated";

  RETURN TRUE;

END;
$_$;


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

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

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

BEGIN

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

  IF (FOUND) THEN
    RETURN FALSE;
  END IF;

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

  NOTIFY "usrprivUpdated";

  RETURN TRUE;

END;
$_$;


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

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

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

BEGIN

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

  IF (FOUND) THEN
    RETURN FALSE;
  END IF;

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

  RETURN TRUE;

END;
$_$;


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

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

CREATE FUNCTION hasalarms() RETURNS boolean
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _alarm          RECORD;
  _alarmtype      TEXT := '';
  _batchId        INTEGER;
  _evntlogordtype TEXT;
  _evnttypeid     INTEGER;
  _evnttypename   TEXT;
  _fromEmail      TEXT;
  _itemid         INTEGER;
  _longsource     TEXT;
  _msgId          INTEGER;
  _recipient      TEXT;
  _recipientPart  INTEGER;
  _returnVal      BOOLEAN := FALSE;
  _summary        TEXT;
  _whsId          INTEGER := -1;

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

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

      _alarmtype = 'To-Do: ';

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

      _alarmtype = 'Incident: ';

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

      _alarmtype = 'Project: ';

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

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

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

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

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

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

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

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

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

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

END;
$$;


ALTER FUNCTION public.hasalarms() OWNER TO admin;

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

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

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

END;
$$;


ALTER FUNCTION public.hasevents() OWNER TO admin;

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

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

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

END;
$$;


ALTER FUNCTION public.hasmessages() OWNER TO admin;

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

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

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

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

  RETURN _returnVal;
END;
$_$;


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

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

CREATE FUNCTION hasprivonobject(pprivtype text, pobjecttype text, pobjectid integer DEFAULT NULL::integer, puser text DEFAULT NULL::text) RETURNS boolean
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _haspriv   BOOLEAN := FALSE;
  _privfound BOOLEAN := FALSE;
  _pkey      TEXT[];
  _privdesc  RECORD;
  _qstr      TEXT;

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

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

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

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

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

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

        EXECUTE _qstr INTO _haspriv;
      END IF;
    END IF;

    EXIT WHEN _haspriv;
  END LOOP;

  RETURN _haspriv OR NOT _privfound;

END;
$$;


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

--
-- Name: FUNCTION hasprivonobject(pprivtype text, pobjecttype text, pobjectid integer, puser text); Type: COMMENT; Schema: public; Owner: admin
--

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


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

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


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

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

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


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

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

CREATE FUNCTION implodewo(pwoid integer, pimplodechildren 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
  resultCode INTEGER;
  _routings  BOOLEAN;
  _wo        RECORD;

BEGIN
  _routings := fetchMetricBool('Routings');

  SELECT * INTO _wo
  FROM wo
  WHERE (wo_id=pWoid);

  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Cannot find W/O id %  [xtuple: implodeWo, -9]', pWoid;
  END IF;

  IF (_routings) THEN
    IF (EXISTS(SELECT wotc_wo_id
               FROM xtmfg.wotc
               WHERE (wotc_wo_id=pWoid))) THEN
      RAISE EXCEPTION 'Cannot implode W/O % with labor posting [xtuple: implodeWo, -1]', formatWoNumber(pWoid);
    END IF;
  END IF;

  IF (_wo.wo_status != 'E') THEN
    RAISE EXCEPTION 'Cannot implode W/O % with status % [xtuple: implodeWo, -2]', formatWoNumber(pWoid), _wo.wo_status;
  END IF;

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

  DELETE FROM womatl
  WHERE (womatl_wo_id=pWoid);

  IF _routings THEN
    DELETE FROM xtmfg.wooper
    WHERE (wooper_wo_id=pWoid);
  END IF;

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

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

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

  RETURN 0;
END;
$$;


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

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

CREATE FUNCTION importbankreccleared(pbankrecid 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
  _bankrecid INTEGER;
  _result INTEGER := 0;
  _cleared BOOLEAN;
  _doctype TEXT;
  _docid INTEGER := -1;
  _bankadjid INTEGER := -1;
  _debitbankadjtypeid INTEGER := -1;
  _creditbankadjtypeid INTEGER := -1;
  _b RECORD;
  _r RECORD;

BEGIN
  -- cache some information
  IF (pBankrecid = -1) THEN
    _bankrecid := fetchMetricValue('ImportBankRecId');
  ELSE
    _bankrecid := pBankrecid;
  END IF;

  SELECT * INTO _b
  FROM bankrec JOIN bankaccnt ON (bankaccnt_id=bankrec_bankaccnt_id)
  WHERE (bankrec_id=_bankrecid);
  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'bankrec not found';
  END IF;
  IF (_b.bankrec_posted) THEN
    RAISE EXCEPTION 'bankrec already posted';
  END IF;

   _debitbankadjtypeid := fetchMetricValue('ImportBankRecDebitAdj');
  IF (_debitbankadjtypeid = -1) THEN
    RAISE EXCEPTION 'Metric ImportBankRecDebitAdj not defined [xtuple: reconcileBankAccount, -1]';
  END IF;

   _creditbankadjtypeid := fetchMetricValue('ImportBankRecCreditAdj');
  IF (_creditbankadjtypeid = -1) THEN
    RAISE EXCEPTION 'Metric ImportBankRecCreditAdj not defined [xtuple: reconcileBankAccount, -2]';
  END IF;

  -- loop thru bankrecimport and toggle cleared items
  FOR _r IN
  SELECT *,
         COALESCE(bankrecimport_debit_amount, 0.0) AS debit,
         COALESCE(bankrecimport_credit_amount, 0.0) AS credit
  FROM bankrecimport
-- TODO how to handle multiple bank accounts
--  WHERE (bankrecimport_?=_b.bankaccnt=?)
  LOOP

    -- TODO how to handle duplicate document numbers
    -- TODO how to handle amount differences
    -- TODO add support for Project Accounting (sltrans)

    IF ( (_r.debit > 0.0) AND (_r.credit > 0.0) ) THEN
      RAISE WARNING 'Bankrecimport % cannot determine if debit or credit', _r.bankrecimport_reference;
      CONTINUE;
    END IF;

    IF (_r.debit > 0.0) THEN

      -- handle receipts

      SELECT cashrcpt_id INTO _docid
      FROM cashrcpt
      WHERE (cashrcpt_docnumber=_r.bankrecimport_reference)
        AND (cashrcpt_posted)
        AND (NOT cashrcpt_void)
      -- TODO workaround for duplicates
      ORDER BY cashrcpt_id DESC
      LIMIT 1;
      IF (FOUND) THEN
        SELECT toggleBankrecCleared(_b.bankrec_id, 'GL', gltrans_id,
                                    cashrcpt_curr_rate, _r.debit,
                                    _r.bankrecimport_effdate) INTO _cleared
        FROM gltrans LEFT OUTER JOIN bankrecitem ON (bankrecitem_source='GL' AND
                                                     bankrecitem_source_id=gltrans_id)
        WHERE (gltrans_source='A/R')
          AND (gltrans_doctype='CR')
          AND (gltrans_misc_id=_docid)
          AND (NOT gltrans_rec)
          AND (gltrans_accnt_id=_b.bankaccnt_accnt_id)
          AND (NOT COALESCE(bankrecitem_cleared, FALSE));
      ELSE

        -- create and toggle bank adjustment
        -- TODO define bank adjustment names

        _bankadjid := -1;
        SELECT bankadj_id INTO _bankadjid
        FROM bankadj JOIN bankadjtype ON (bankadjtype_id=bankadj_bankadjtype_id)
        WHERE (bankadjtype_id=_debitbankadjtypeid)
          AND (bankadj_docnumber=_r.bankrecimport_reference)
          AND (bankadj_bankaccnt_id=_b.bankaccnt_id);
        IF (NOT FOUND) THEN
          INSERT INTO bankadj
            (bankadj_bankaccnt_id, bankadj_bankadjtype_id, bankadj_date,
             bankadj_docnumber, bankadj_amount, bankadj_notes,
             bankadj_curr_id)
          VALUES
            (_b.bankaccnt_id, _debitbankadjtypeid, _r.bankrecimport_effdate,
             _r.bankrecimport_reference, _r.debit, 'Import Bankrec Adjustment',
             _b.bankaccnt_curr_id)
          RETURNING bankadj_id INTO _bankadjid;
        END IF;

        SELECT toggleBankrecCleared(_b.bankrec_id, 'AD', _bankadjid,
                                    1.0, _r.debit,
                                    _r.bankrecimport_effdate) INTO _cleared
        FROM bankadj LEFT OUTER JOIN bankrecitem ON (bankrecitem_source='AD' AND
                                                     bankrecitem_source_id=bankadj_id)
        WHERE (bankadj_id=_bankadjid)
          AND (NOT COALESCE(bankrecitem_cleared, FALSE));
      END IF;

      -- done with receipts

    ELSE

      -- handle checks

      SELECT checkhead_id INTO _docid
      FROM checkhead
      WHERE (checkhead_number::TEXT=_r.bankrecimport_reference)
        AND (checkhead_posted)
        AND (NOT checkhead_void);
      IF (FOUND) THEN
        SELECT toggleBankrecCleared(_b.bankrec_id, 'GL', gltrans_id,
                                    checkhead_curr_rate, _r.credit,
                                    _r.bankrecimport_effdate) INTO _cleared
        FROM gltrans LEFT OUTER JOIN bankrecitem ON (bankrecitem_source='GL' AND
                                                     bankrecitem_source_id=gltrans_id)
        WHERE (gltrans_source='A/P')
          AND (gltrans_doctype='CK')
          AND (gltrans_misc_id=_docid)
          AND (NOT gltrans_rec)
          AND (gltrans_accnt_id=_b.bankaccnt_accnt_id)
          AND (NOT COALESCE(bankrecitem_cleared, FALSE));
      ELSE

        -- create and toggle bank adjustment
        -- TODO define bank adjustment names

        _bankadjid := -1;
        SELECT bankadj_id INTO _bankadjid
        FROM bankadj JOIN bankadjtype ON (bankadjtype_id=bankadj_bankadjtype_id)
        WHERE (bankadjtype_id=_creditbankadjtypeid)
          AND (bankadj_docnumber=_r.bankrecimport_reference);
        IF (NOT FOUND) THEN
          INSERT INTO bankadj
            (bankadj_bankaccnt_id, bankadj_bankadjtype_id, bankadj_date,
             bankadj_docnumber, bankadj_amount, bankadj_notes,
             bankadj_curr_id)
          VALUES
            (_b.bankaccnt_id, _creditbankadjtypeid, _r.bankrecimport_effdate,
             _r.bankrecimport_reference, _r.credit, 'Import Bankrec Adjustment',
             _b.bankaccnt_curr_id)
          RETURNING bankadj_id INTO _bankadjid;
        END IF;

        SELECT toggleBankrecCleared(_b.bankrec_id, 'AD', _bankadjid,
                                    1.0, _r.credit,
                                    _r.bankrecimport_effdate) INTO _cleared
        FROM bankadj LEFT OUTER JOIN bankrecitem ON (bankrecitem_source='AD' AND
                                                     bankrecitem_source_id=_bankadjid)
        WHERE (NOT COALESCE(bankrecitem_cleared, FALSE));
      END IF;

      -- done with checks

    END IF;

  END LOOP;

  RETURN _result;
END;
$$;


ALTER FUNCTION public.importbankreccleared(pbankrecid integer) OWNER TO admin;

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

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


ALTER TABLE incdt OWNER TO admin;

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

COMMENT ON TABLE incdt IS 'Incident table';


--
-- Name: COLUMN incdt.incdt_lotserial; Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- Name: COLUMN incdt.incdt_recurring_incdt_id; Type: COMMENT; Schema: public; Owner: admin
--

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


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

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

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

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

  RETURN;

END;
$$;


ALTER FUNCTION public.incdt() OWNER TO admin;

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

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


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

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

BEGIN

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

  RETURN indentedBOM(pItemid, _revid);

END;
$_$;


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

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

CREATE FUNCTION indentedbom(integer, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pItemid ALIAS FOR $1;
  pRevisionid ALIAS FOR $2;
  _bomworkid INTEGER;
  _indexid INTEGER;
  _r RECORD;
  _batchsize NUMERIC;

BEGIN

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

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

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

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

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

  END LOOP;

--  Return a key to the result
  RETURN _indexid;

END;
$_$;


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

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

CREATE FUNCTION indentedbom(integer, integer, integer, integer) RETURNS SETOF bomdata
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pItemid ALIAS FOR $1;
  pRevisionid ALIAS FOR $2;
  pExpiredDays ALIAS FOR $3;
  pFutureDays ALIAS FOR $4;
  _row bomdata%ROWTYPE;
  _bomworksetid INTEGER;
  _x RECORD;
  _check CHAR(1);
  _inactive BOOLEAN := FALSE;
  _batchsize NUMERIC;
BEGIN

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

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

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

  ELSE

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

  RETURN;
END;
$_$;


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

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

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

BEGIN

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

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

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

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

    _level := _level + 1;

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

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

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

  END LOOP;

  RETURN _indexid;
END;
$_$;


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

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

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

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

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

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

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


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

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

CREATE FUNCTION indentedwo(integer, integer, integer, boolean, boolean) RETURNS SETOF wodata
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
   pwoid ALIAS FOR $1;   
   pwooperid ALIAS FOR $2;
   plevel ALIAS FOR $3;
   pshowmatl ALIAS FOR $4; 
   pshowindent ALIAS FOR $5;  
  _row wodata%ROWTYPE;   
  _opx wodata%ROWTYPE; 
  _x RECORD;
  _subx RECORD;
  _index INTEGER;
  _level INTEGER;
  _qry TEXT;
   
BEGIN 
    --The wodata id column is used to indicate the source of the id
    --there are three different tables used wo, womatl and womatlvar
    --wodata_id_type = 1 = wo_id
    --wodata_id_type = 2 = womatl_id
    --wodata_id_type = 3 = wooper_id
    _level := (plevel + 1);    
    --find all WO with the ordid of the next level up
    _qry := 'SELECT wo_id,wo_number,wo_subnumber,wo_status,wo_startdate,wo_duedate,
         wo_adhoc,wo_itemsite_id,qtyAvailable(itemsite_id) AS availableqoh,wo_qtyord,wo_qtyrcv, wo_prodnotes,
         item_number,item_descrip1, item_descrip2, item_listprice, uom_name,
         womatl_qtyiss, womatl_qtywipscrap, womatl_wooper_id
       FROM itemsite,  wo, item, uom, womatl 
       WHERE ((wo_ordid = ' || pwoid || ')
         AND (wo_ordtype = ''W'')
         AND (itemsite_item_id=item_id)
         AND (item_inv_uom_id=uom_id)   
         AND (wo_womatl_id=womatl_id)   
         AND (wo_itemsite_id = itemsite_id) ';

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

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

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

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

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


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

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

CREATE FUNCTION indentedwomatl(integer, integer) RETURNS SETOF wodata
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
   pwoid ALIAS FOR $1;   
   plevel ALIAS FOR $2;
  _subx RECORD;
   
BEGIN
  FOR _subx IN
    SELECT * FROM indentedwomatl(pwoid, NULL::integer, plevel)
  LOOP
    RETURN NEXT _subx;
  END LOOP;        
  RETURN;
END;
$_$;


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

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

CREATE FUNCTION indentedwomatl(integer, integer, integer) RETURNS SETOF wodata
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
   pwoid ALIAS FOR $1;  
   pwooperid ALIAS FOR $2; 
   plevel ALIAS FOR $3;
  _status TEXT; 
  _subrow wodata%ROWTYPE;  
  _subx RECORD;
  _level INTEGER;
  _qry TEXT;
   
BEGIN
  --The wodata id column is used to indicate the source of the id
  --there are three different tables used wo, womatl and womatlvar
  --wodata_id_type = 1 = wo_id
  --wodata_id_type = 2 = womatl_id
  --wodata_id_type = 3 = wooper_id

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

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

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


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

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

CREATE FUNCTION initeffectivextuser() RETURNS void
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  -- Effective users use a temporary table to store the user information
  -- and this function, called by the other functions, makes sure the temp
  -- tables exist first.
  PERFORM *
     FROM pg_catalog.pg_class
    WHERE relname = 'effective_user'
      AND relnamespace = pg_catalog.pg_my_temp_schema();

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


ALTER FUNCTION public.initeffectivextuser() OWNER TO admin;

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

CREATE FUNCTION initialdistribution(pitemsiteid integer, plocationid 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
  _itemlocid INTEGER;
  _invhistid INTEGER;
  _itemlocSeries INTEGER;
  _r RECORD;

BEGIN

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

  _itemlocSeries := NEXTVAL('itemloc_series_seq');

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

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

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

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

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

    END LOOP;

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

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

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

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

  END IF;

  RETURN _itemlocid;

END;
$$;


ALTER FUNCTION public.initialdistribution(pitemsiteid integer, plocationid integer) OWNER TO admin;

--
-- Name: apopen; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE apopen (
    apopen_id integer DEFAULT nextval(('"apopen_apopen_id_seq"'::text)::regclass) NOT NULL,
    apopen_docdate date,
    apopen_duedate date,
    apopen_terms_id integer,
    apopen_vend_id integer,
    apopen_doctype character(1),
    apopen_docnumber text,
    apopen_amount numeric(20,2),
    apopen_notes text,
    apopen_posted boolean,
    apopen_reference text,
    apopen_invcnumber text,
    apopen_ponumber text,
    apopen_journalnumber integer,
    apopen_paid numeric(20,2) DEFAULT 0,
    apopen_open boolean,
    apopen_username text,
    apopen_discount boolean DEFAULT false NOT NULL,
    apopen_accnt_id integer DEFAULT (-1),
    apopen_curr_id integer DEFAULT basecurrid(),
    apopen_closedate date,
    apopen_distdate date,
    apopen_void boolean DEFAULT false NOT NULL,
    apopen_curr_rate numeric NOT NULL,
    apopen_discountable_amount numeric(20,2) DEFAULT 0,
    apopen_status text,
    CONSTRAINT apopen_apopen_status_check CHECK ((((apopen_status = 'O'::text) OR (apopen_status = 'H'::text)) OR (apopen_status = 'C'::text))),
    CONSTRAINT apopen_apopen_status_notnull CHECK ((apopen_status IS NOT NULL))
);


ALTER TABLE apopen OWNER TO admin;

--
-- Name: TABLE apopen; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE apopen IS 'Accounts Payable (A/P) open Items information';


--
-- Name: curr_symbol; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE curr_symbol (
    curr_id integer NOT NULL,
    curr_base boolean DEFAULT false NOT NULL,
    curr_name character varying(50) NOT NULL,
    curr_symbol character varying(9) NOT NULL,
    curr_abbr character varying(3) NOT NULL
);


ALTER TABLE curr_symbol OWNER TO admin;

--
-- Name: TABLE curr_symbol; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE curr_symbol IS 'Currency Names, Symbols, and Abbreviations';


--
-- Name: terms; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE terms (
    terms_id integer DEFAULT nextval(('terms_terms_id_seq'::text)::regclass) NOT NULL,
    terms_code text NOT NULL,
    terms_descrip text,
    terms_type character(1),
    terms_duedays integer,
    terms_discdays integer,
    terms_discprcnt numeric(10,6),
    terms_cutoffday integer,
    terms_ap boolean,
    terms_ar boolean,
    terms_fincharg boolean DEFAULT true NOT NULL,
    CONSTRAINT terms_terms_code_check CHECK ((terms_code <> ''::text))
);


ALTER TABLE terms OWNER TO admin;

--
-- Name: TABLE terms; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE terms IS 'Billing Terms information';


--
-- Name: vendinfo; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE vendinfo (
    vend_id integer DEFAULT nextval(('vend_vend_id_seq'::text)::regclass) NOT NULL,
    vend_name text,
    vend_lastpurchdate date,
    vend_active boolean,
    vend_po boolean,
    vend_comments text,
    vend_pocomments text,
    vend_number text NOT NULL,
    vend_1099 boolean,
    vend_exported boolean,
    vend_fobsource character(1),
    vend_fob text,
    vend_terms_id integer,
    vend_shipvia text,
    vend_vendtype_id integer,
    vend_qualified boolean,
    vend_ediemail text,
    vend_ediemailbody text,
    vend_edisubject text,
    vend_edifilename text,
    vend_accntnum text,
    vend_emailpodelivery boolean,
    vend_restrictpurch boolean,
    vend_edicc text,
    vend_curr_id integer DEFAULT basecurrid(),
    vend_cntct1_id integer,
    vend_cntct2_id integer,
    vend_addr_id integer,
    vend_match boolean DEFAULT false NOT NULL,
    vend_ach_enabled boolean DEFAULT false NOT NULL,
    vend_ach_accnttype text,
    vend_ach_use_vendinfo boolean DEFAULT true NOT NULL,
    vend_ach_indiv_number text DEFAULT ''::text NOT NULL,
    vend_ach_indiv_name text DEFAULT ''::text NOT NULL,
    vend_ediemailhtml boolean DEFAULT false NOT NULL,
    vend_ach_routingnumber bytea DEFAULT '\x00'::bytea NOT NULL,
    vend_ach_accntnumber bytea DEFAULT '\x00'::bytea NOT NULL,
    vend_taxzone_id integer,
    vend_accnt_id integer,
    vend_expcat_id integer DEFAULT (-1),
    vend_tax_id integer DEFAULT (-1),
    CONSTRAINT vendinfo_vend_ach_accnttype_check CHECK (((vend_ach_accnttype = 'K'::text) OR (vend_ach_accnttype = 'C'::text))),
    CONSTRAINT vendinfo_vend_number_check CHECK ((vend_number <> ''::text))
);


ALTER TABLE vendinfo OWNER TO admin;

--
-- Name: TABLE vendinfo; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE vendinfo IS 'Vendor information';


--
-- Name: COLUMN vendinfo.vend_ach_accnttype; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN vendinfo.vend_ach_accnttype IS 'Type of bank account: K = checKing, C = Cash = savings. These values were chosen to be consistent with bankaccnt_type.';



--
-- Name: apmemo; Type: VIEW; Schema: api; Owner: admin
--

CREATE VIEW apmemo AS
    SELECT vendinfo.vend_number AS vendor_number, apopen.apopen_docdate AS document_date, apopen.apopen_duedate AS due_date, CASE WHEN (apopen.apopen_doctype = 'C'::bpchar) THEN 'Credit Memo'::text ELSE 'Debit Memo'::text END AS document_type, apopen.apopen_docnumber AS document_number, apopen.apopen_ponumber AS po_number, apopen.apopen_journalnumber AS journal_number, terms.terms_code AS terms, curr.curr_abbr AS currency, apopen.apopen_amount AS amount, apopen.apopen_paid AS paid, (apopen.apopen_amount - apopen.apopen_paid) AS balance, apopen.apopen_notes AS notes, CASE WHEN (apopen.apopen_accnt_id = (-1)) THEN NULL::text ELSE public.formatglaccount(apopen.apopen_accnt_id) END AS alternate_prepaid_account FROM (((public.apopen LEFT JOIN public.vendinfo ON ((vendinfo.vend_id = apopen.apopen_vend_id))) LEFT JOIN public.curr_symbol curr ON ((curr.curr_id = apopen.apopen_curr_id))) LEFT JOIN public.terms ON ((terms.terms_id = apopen.apopen_terms_id))) WHERE (apopen.apopen_doctype = ANY (ARRAY['C'::bpchar, 'D'::bpchar]));


ALTER TABLE apmemo OWNER TO admin;

--
-- Name: VIEW apmemo; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW apmemo IS 'A/P Credit and Debit Memo';



--
-- Name: insertapmemo(api.apmemo); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION insertapmemo(api.apmemo) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pNew ALIAS FOR $1;
  _result INTEGER;

BEGIN
  IF (pNew.document_type = 'Credit Memo') THEN
    SELECT createAPCreditMemo( getVendId(pNew.vendor_number),
                               pNew.document_number,
                               pNew.po_number,
                               pNew.document_date,
                               pNew.amount,
                               pNew.notes,
                               getGLAccntId(pNew.alternate_prepaid_account),
                               pNew.due_date,
                               getTermsId(pNew.terms) ) INTO _result;
    IF (_result <= 0) THEN
      RAISE EXCEPTION 'Function createAPCreditMemo failed with result = %', _result;
    END IF;
  ELSE
    IF (pNew.document_type = 'Debit Memo') THEN
      SELECT createAPDebitMemo( null, getVendId(pNew.vendor_number),
                                pNew.journal_number,
                                pNew.document_number,
                                pNew.po_number,
                                pNew.document_date,
                                pNew.amount,
                                pNew.notes,
                                getGLAccntId(pNew.alternate_prepaid_account),
                                pNew.due_date,
                                getTermsId(pNew.terms),
                                COALESCE(getCurrId(pNew.currency), baseCurrId()) ) INTO _result;
      IF (_result <= 0) THEN
        RAISE EXCEPTION 'Function createAPDebitMemo failed with result = %', _result;
      END IF;
    ELSE
      RAISE EXCEPTION 'Function insertAPMemo failed, invalid Document Type';
    END IF;
  END IF;

  RETURN TRUE;
END;
$_$;


ALTER FUNCTION public.insertapmemo(api.apmemo) OWNER TO admin;

--
-- Name: aropen; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE aropen (
    aropen_id integer DEFAULT nextval(('"aropen_aropen_id_seq"'::text)::regclass) NOT NULL,
    aropen_docdate date NOT NULL,
    aropen_duedate date NOT NULL,
    aropen_terms_id integer,
    aropen_cust_id integer,
    aropen_doctype character(1),
    aropen_docnumber text,
    aropen_applyto text,
    aropen_ponumber text,
    aropen_amount numeric(20,2) NOT NULL,
    aropen_notes text,
    aropen_posted boolean DEFAULT false NOT NULL,
    aropen_salesrep_id integer,
    aropen_commission_due numeric(20,2),
    aropen_commission_paid boolean DEFAULT false,
    aropen_ordernumber text,
    aropen_cobmisc_id integer DEFAULT (-1),
    aropen_journalnumber integer,
    aropen_paid numeric(20,2) DEFAULT 0,
    aropen_open boolean,
    aropen_username text,
    aropen_rsncode_id integer,
    aropen_salescat_id integer DEFAULT (-1),
    aropen_accnt_id integer DEFAULT (-1),
    aropen_curr_id integer DEFAULT basecurrid(),
    aropen_closedate date,
    aropen_distdate date,
    aropen_curr_rate numeric NOT NULL,
    aropen_discount boolean DEFAULT false NOT NULL,
    aropen_fincharg_date date,
    aropen_fincharg_amount numeric(20,2)
);


ALTER TABLE aropen OWNER TO admin;

--
-- Name: TABLE aropen; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE aropen IS 'Accounts Receivable (A/R) open Items information';


--
-- Name: custinfo; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE custinfo (
    cust_id integer DEFAULT nextval(('cust_cust_id_seq'::text)::regclass) NOT NULL,
    cust_active boolean NOT NULL,
    cust_custtype_id integer,
    cust_salesrep_id integer,
    cust_commprcnt numeric(10,6),
    cust_name text,
    cust_creditlmt integer,
    cust_creditrating text,
    cust_financecharge boolean,
    cust_backorder boolean NOT NULL,
    cust_partialship boolean NOT NULL,
    cust_terms_id integer,
    cust_discntprcnt numeric(10,6) NOT NULL,
    cust_balmethod character(1) NOT NULL,
    cust_ffshipto boolean NOT NULL,
    cust_shipform_id integer,
    cust_shipvia text,
    cust_blanketpos boolean NOT NULL,
    cust_shipchrg_id integer NOT NULL,
    cust_creditstatus character(1) NOT NULL,
    cust_comments text,
    cust_ffbillto boolean NOT NULL,
    cust_usespos boolean NOT NULL,
    cust_number text NOT NULL,
    cust_dateadded date DEFAULT ('now'::text)::date,
    cust_exported boolean DEFAULT false,
    cust_emaildelivery boolean DEFAULT false,
    cust_ediemail text,
    cust_edisubject text,
    cust_edifilename text,
    cust_ediemailbody text,
    cust_autoupdatestatus boolean NOT NULL,
    cust_autoholdorders boolean NOT NULL,
    cust_edicc text,
    cust_ediprofile_id integer,
    cust_preferred_warehous_id integer DEFAULT (-1) NOT NULL,
    cust_curr_id integer DEFAULT basecurrid(),
    cust_creditlmt_curr_id integer DEFAULT basecurrid(),
    cust_cntct_id integer,
    cust_corrcntct_id integer,
    cust_soemaildelivery boolean DEFAULT false,
    cust_soediemail text,
    cust_soedisubject text,
    cust_soedifilename text,
    cust_soediemailbody text,
    cust_soedicc text,
    cust_soediprofile_id integer,
    cust_gracedays integer,
    cust_ediemailhtml boolean DEFAULT false NOT NULL,
    cust_soediemailhtml boolean DEFAULT false NOT NULL,
    cust_taxzone_id integer,
    cust_statementcycle text,
    CONSTRAINT custinfo_balmethod_check CHECK (((cust_balmethod = 'B'::bpchar) OR (cust_balmethod = 'O'::bpchar))),
    CONSTRAINT custinfo_creditstatus_check CHECK ((((cust_creditstatus = 'G'::bpchar) OR (cust_creditstatus = 'W'::bpchar)) OR (cust_creditstatus = 'H'::bpchar))),
    CONSTRAINT custinfo_cust_number_check CHECK ((cust_number <> ''::text))
);


ALTER TABLE custinfo OWNER TO admin;

--
-- Name: TABLE custinfo; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE custinfo IS 'Customer information';


--
-- Name: COLUMN custinfo.cust_ediemail; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_ediemail IS 'Deprecated column - DO NOT USE';


--
-- Name: COLUMN custinfo.cust_edisubject; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_edisubject IS 'Deprecated column - DO NOT USE';


--
-- Name: COLUMN custinfo.cust_edifilename; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_edifilename IS 'Deprecated column - DO NOT USE';


--
-- Name: COLUMN custinfo.cust_ediemailbody; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_ediemailbody IS 'Deprecated column - DO NOT USE';


--
-- Name: COLUMN custinfo.cust_edicc; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_edicc IS 'Deprecated column - DO NOT USE';


--
-- Name: COLUMN custinfo.cust_ediprofile_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_ediprofile_id IS 'Deprecated column - DO NOT USE';


--
-- Name: COLUMN custinfo.cust_soediemail; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_soediemail IS 'Deprecated column - DO NOT USE';


--
-- Name: COLUMN custinfo.cust_soedisubject; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_soedisubject IS 'Deprecated column - DO NOT USE';


--
-- Name: COLUMN custinfo.cust_soedifilename; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_soedifilename IS 'Deprecated column - DO NOT USE';


--
-- Name: COLUMN custinfo.cust_soediemailbody; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_soediemailbody IS 'Deprecated column - DO NOT USE';


--
-- Name: COLUMN custinfo.cust_soedicc; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_soedicc IS 'Deprecated column - DO NOT USE';


--
-- Name: COLUMN custinfo.cust_soediprofile_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_soediprofile_id IS 'Deprecated column - DO NOT USE';


--
-- Name: COLUMN custinfo.cust_ediemailhtml; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_ediemailhtml IS 'Deprecated column - DO NOT USE';


--
-- Name: COLUMN custinfo.cust_soediemailhtml; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN custinfo.cust_soediemailhtml IS 'Deprecated column - DO NOT USE';


--
-- Name: rsncode; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE rsncode (
    rsncode_id integer NOT NULL,
    rsncode_code text NOT NULL,
    rsncode_descrip text,
    rsncode_doctype text,
    CONSTRAINT rsncode_rsncode_code_check CHECK ((rsncode_code <> ''::text))
);


ALTER TABLE rsncode OWNER TO admin;

--
-- Name: TABLE rsncode; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE rsncode IS 'Debit/Credit Memo Reason Code information';


--
-- Name: salescat; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE salescat (
    salescat_id integer NOT NULL,
    salescat_active boolean,
    salescat_name text NOT NULL,
    salescat_descrip text,
    salescat_sales_accnt_id integer,
    salescat_prepaid_accnt_id integer,
    salescat_ar_accnt_id integer,
    CONSTRAINT salescat_salescat_name_check CHECK ((salescat_name <> ''::text))
);


ALTER TABLE salescat OWNER TO admin;

--
-- Name: TABLE salescat; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE salescat IS 'Sales Category information';


--
-- Name: salesrep; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE salesrep (
    salesrep_id integer DEFAULT nextval(('salesrep_salesrep_id_seq'::text)::regclass) NOT NULL,
    salesrep_active boolean,
    salesrep_number text NOT NULL,
    salesrep_name text,
    salesrep_commission numeric(8,4),
    salesrep_method character(1),
    salesrep_emp_id integer,
    CONSTRAINT salesrep_salesrep_number_check CHECK ((salesrep_number <> ''::text))
);


ALTER TABLE salesrep OWNER TO admin;

--
-- Name: TABLE salesrep; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE salesrep IS 'Sales Representative information';


--
-- Name: COLUMN salesrep.salesrep_emp_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN salesrep.salesrep_emp_id IS 'DEPRECATED - the relationship between Sales Rep and Employee is now maintained through the crmacct table.';



--
-- Name: armemo; Type: VIEW; Schema: api; Owner: admin
--

CREATE VIEW armemo AS
    SELECT custinfo.cust_number AS customer_number, aropen.aropen_docdate AS document_date, aropen.aropen_duedate AS due_date, CASE WHEN (aropen.aropen_doctype = 'C'::bpchar) THEN 'Credit Memo'::text ELSE 'Debit Memo'::text END AS document_type, aropen.aropen_docnumber AS document_number, aropen.aropen_applyto AS order_number, aropen.aropen_journalnumber AS journal_number, rsncode.rsncode_code AS reason_code, terms.terms_code AS terms, salesrep.salesrep_number AS sales_rep, curr.curr_abbr AS currency, aropen.aropen_amount AS amount, aropen.aropen_paid AS paid, (aropen.aropen_amount - aropen.aropen_paid) AS balance, aropen.aropen_commission_due AS commission_due, aropen.aropen_commission_paid AS commission_paid, aropen.aropen_notes AS notes, salescat.salescat_name AS alternate_prepaid_sales_category, CASE WHEN (aropen.aropen_accnt_id = (-1)) THEN NULL::text ELSE public.formatglaccount(aropen.aropen_accnt_id) END AS alternate_prepaid_account FROM ((((((public.aropen LEFT JOIN public.custinfo ON ((custinfo.cust_id = aropen.aropen_cust_id))) LEFT JOIN public.curr_symbol curr ON ((curr.curr_id = aropen.aropen_curr_id))) LEFT JOIN public.salesrep ON ((salesrep.salesrep_id = aropen.aropen_salesrep_id))) LEFT JOIN public.terms ON ((terms.terms_id = aropen.aropen_terms_id))) LEFT JOIN public.salescat ON ((salescat.salescat_id = aropen.aropen_salescat_id))) LEFT JOIN public.rsncode ON ((rsncode.rsncode_id = aropen.aropen_rsncode_id))) WHERE (aropen.aropen_doctype = ANY (ARRAY['C'::bpchar, 'D'::bpchar]));


ALTER TABLE armemo OWNER TO admin;

--
-- Name: VIEW armemo; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW armemo IS 'A/R Credit and Debit Memo';



--
-- Name: insertarmemo(api.armemo); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION insertarmemo(api.armemo) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pNew ALIAS FOR $1;
  _result INTEGER;

BEGIN
  IF (pNew.document_type = 'Credit Memo') THEN
    SELECT createARCreditMemo( NULL,
                               getCustId(pNew.customer_number),
                               pNew.document_number,
                               pNew.order_number,
                               pNew.document_date,
                               pNew.amount,
                               pNew.notes,
                               getRsnId(pNew.reason_code),
                               getSalescatId(pNew.alternate_prepaid_sales_category),
                               getGLAccntId(pNew.alternate_prepaid_account),
                               pNew.due_date,
                               getTermsId(pNew.terms),
                               getSalesrepId(pNew.sales_rep),
                               pNew.commission_due,
                               pNew.journal_number,
                               COALESCE(getCurrId(pNew.currency), baseCurrId()) ) INTO _result;
    IF (_result <= 0) THEN
      RAISE EXCEPTION 'Function createARCreditMemo failed with result = %', _result;
    END IF;
  ELSE
    IF (pNew.document_type = 'Debit Memo') THEN
      SELECT createARDebitMemo( null, getCustId(pNew.customer_number),
                                pNew.journal_number,
                                pNew.document_number,
                                pNew.order_number,
                                pNew.document_date,
                                pNew.amount,
                                pNew.notes,
                                getRsnId(pNew.reason_code),
                                getSalescatId(pNew.alternate_prepaid_sales_category),
                                getGLAccntId(pNew.alternate_prepaid_account),
                                pNew.due_date,
                                getTermsId(pNew.terms),
                                getSalesrepId(pNew.sales_rep),
                                pNew.commission_due,
                                COALESCE(getCurrId(pNew.currency), baseCurrId()) ) INTO _result;
      IF (_result <= 0) THEN
        RAISE EXCEPTION 'Function createARDebitMemo failed with result = %', _result;
      END IF;
    ELSE
      RAISE EXCEPTION 'Function insertARMemo failed, invalid Document Type';
    END IF;
  END IF;

  RETURN TRUE;
END;
$_$;


ALTER FUNCTION public.insertarmemo(api.armemo) OWNER TO admin;

--
-- Name: insertccard(text, boolean, text, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION insertccard(text, boolean, text, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, text) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pCustomer ALIAS FOR $1;
  pActive ALIAS FOR $2;
  pType ALIAS FOR $3;
  pNumber ALIAS FOR $4;
  pName ALIAS FOR $5;
  pAddr1 ALIAS FOR $6;
  pAddr2 ALIAS FOR $7;
  pCity ALIAS FOR $8;
  pState ALIAS FOR $9;
  pPostal ALIAS FOR $10;
  pCountry ALIAS FOR $11;
  pMonth ALIAS FOR $12;
  pYear ALIAS FOR $13;
  pKey ALIAS FOR $14;
  _type CHAR;
  _number TEXT;
  _month INTEGER;
  _year INTEGER;
  _result INTEGER;

BEGIN
  --Initialize
  _number = CAST(encode(pNumber, 'escape') AS text);
  _month = CAST(encode(pMonth, 'escape') AS integer);
  _year = CAST(encode(pYear, 'escape') AS integer);

  -- Check Card holder info
  IF (pName IS NULL) THEN
      RAISE EXCEPTION 'The name of the card holder must be entered';
  END IF;
  IF (pAddr1 IS NULL OR pAddr1 = '') THEN
      RAISE EXCEPTION 'The first address line must be entered';
  END IF;
  IF (pCity IS NULL OR pCity = '') THEN
      RAISE EXCEPTION 'The city must be entered';
  END IF;
  IF (pState IS NULL OR pState = '') THEN
      RAISE EXCEPTION 'The state must be entered';
  END IF;
  IF (pPostal IS NULL OR pPostal = '') THEN
      RAISE EXCEPTION 'The zip code must be entered';
  END IF;
  IF (pCountry IS NULL OR pCountry = '') THEN
      RAISE EXCEPTION 'The country must be entered';
  END IF;
  IF (pMonth IS NULL OR pMonth = '') THEN
      RAISE EXCEPTION 'The Expiration Month must be entered';
  END IF;
  IF (_month < 1 OR _month > 12) THEN
      RAISE EXCEPTION 'Valid Expiration Months are 01 through 12';
  END IF;
  IF (LENGTH(_year::text) <> 4) THEN
      RAISE EXCEPTION 'Valid Expiration Years are CCYY in format';
  END IF;
  IF (_year < 1970 OR _year > 2100) THEN
      RAISE EXCEPTION 'Valid Expiration Years are 1970 through 2100';
  END IF;
  
  -- Check Number Length
  IF ((NOT _number ~  '[0-9]{13,16}') OR (LENGTH(_number) = 14) OR (LENGTH(_number) > 16)) THEN
    RAISE EXCEPTION 'The credit card number must be all numeric (no spaces or hyphens) and must be 13, 15 or 16 characters in length';
  END IF;
 
  -- Convert Type
  IF (pType = 'Visa') THEN
    _type  = 'V';
  ELSE
    IF (pType = 'Master Card') THEN
      _type  = 'M';
    ELSE
      IF (pType = 'American Express') THEN
        _type  = 'A';
      ELSE
        IF (pType = 'Discover') THEN
          _type  = 'D';
        ELSE
          RAISE EXCEPTION 'You must select Master Card, Visa, American 
                            Express or Discover as the credit card type.';
        END IF;
      END IF;
    END IF;
  END IF;
  
  -- Check Card Specific Data
  SELECT editccnumber(_number, _type) INTO _result;

  IF (_result = -1) THEN
    RAISE EXCEPTION 'You must select Master Card, Visa, American 
                      Express or Discover as the credit card type.';
  END IF;
  IF (_result = -2) THEN
    RAISE EXCEPTION 'The length of a Master Card credit card number
                      has to be 16 digits.';
  END IF;
  IF (_result = -3) THEN
    RAISE EXCEPTION 'The length of a Visa credit card number
                      has to be either 13 or 16 digits.';
  END IF;
  IF (_result = -4) THEN
    RAISE EXCEPTION 'The length of an American Express credit card
                      number has to be 15 digits.';
  END IF;
  IF (_result = -5) THEN
    RAISE EXCEPTION 'The length of a Discover credit card number 
                      has to be 16 digits.';
  END IF;
   IF (_result = -6) THEN
    RAISE EXCEPTION 'The first two digits for a valid Master Card
                      number must be between 51 and 55';
  END IF;
  IF (_result = -7) THEN
    RAISE EXCEPTION 'The first digit for a valid Visa number must
                      be 4';
  END IF;
   IF (_result = -8) THEN
    RAISE EXCEPTION 'The first two digits for a valid American
                      Express number must be 34 or 37.';
  END IF;
  IF (_result = -9) THEN
    RAISE EXCEPTION 'The first four digits for a valid Discover
                      Express number must be 6011.';
  END IF;
  IF ((_result = -10) AND NOT fetchmetricbool('CCTest')) THEN
    RAISE EXCEPTION 'The credit card number that you have provided
                      is not valid.';
  END IF;
  IF (_result < -10) THEN
    RAISE EXCEPTION 'Invalid Credit Card Information';
  END IF;

  -- Insert Record

  INSERT INTO ccard ( 
    ccard_seq, 
    ccard_cust_id,
    ccard_active, 
    ccard_name, 
    ccard_address1,
    ccard_address2,
    ccard_city, 
    ccard_state, 
    ccard_zip,
    ccard_country, 
    ccard_number,
    ccard_month_expired, 
    ccard_year_expired, 
    ccard_type)
    VALUES 
    ((SELECT COALESCE(MAX(ccard_seq), 0) + 10
      FROM ccard 
      WHERE (ccard_cust_id =getCustId(pCustomer))),
     getCustId(pCustomer),
     COALESCE(pActive),
     encrypt(setbytea(pName), setbytea(pKey), 'bf'),
     encrypt(setbytea(pAddr1), setbytea(pKey), 'bf'),
     encrypt(setbytea(pAddr2), setbytea(pKey), 'bf'),
     encrypt(setbytea(pCity), setbytea(pKey), 'bf'),
     encrypt(setbytea(pState), setbytea(pKey), 'bf'),
     encrypt(setbytea(pPostal), setbytea(pKey), 'bf'),
     encrypt(setbytea(pCountry), setbytea(pKey), 'bf'),
     encrypt(setbytea(pNumber), setbytea(pKey), 'bf'),
     encrypt(setbytea(pMonth), setbytea(pKey), 'bf'),
     encrypt(setbytea(pYear), setbytea(pKey), 'bf'),
     _type );

  RETURN true;
END;
$_$;


ALTER FUNCTION public.insertccard(text, boolean, text, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, text) OWNER TO admin;

--
-- Name: FUNCTION insertccard(text, boolean, text, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, text); Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON FUNCTION insertccard(text, boolean, text, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, bytea, text) IS 'This function is generally used to support the _custcreditcard API view';


--
-- Name: cmhead; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE cmhead (
    cmhead_id integer DEFAULT nextval(('cmhead_cmhead_id_seq'::text)::regclass) NOT NULL,
    cmhead_number text NOT NULL,
    cmhead_posted boolean,
    cmhead_invcnumber text,
    cmhead_custponumber text,
    cmhead_cust_id integer,
    cmhead_docdate date,
    cmhead_shipto_id integer,
    cmhead_shipto_name text,
    cmhead_shipto_address1 text,
    cmhead_shipto_address2 text,
    cmhead_shipto_address3 text,
    cmhead_shipto_city text,
    cmhead_shipto_state text,
    cmhead_shipto_zipcode text,
    cmhead_salesrep_id integer,
    cmhead_freight numeric(16,4),
    cmhead_misc numeric(16,4),
    cmhead_comments text,
    cmhead_printed boolean,
    cmhead_billtoname text,
    cmhead_billtoaddress1 text,
    cmhead_billtoaddress2 text,
    cmhead_billtoaddress3 text,
    cmhead_billtocity text,
    cmhead_billtostate text,
    cmhead_billtozip text,
    cmhead_hold boolean,
    cmhead_commission numeric(8,4),
    cmhead_misc_accnt_id integer,
    cmhead_misc_descrip text,
    cmhead_rsncode_id integer,
    cmhead_curr_id integer DEFAULT basecurrid(),
    cmhead_freighttaxtype_id integer,
    cmhead_gldistdate date,
    cmhead_billtocountry text,
    cmhead_shipto_country text,
    cmhead_rahead_id integer,
    cmhead_taxzone_id integer,
    cmhead_prj_id integer,
    cmhead_void boolean DEFAULT false,
    cmhead_saletype_id integer,
    cmhead_shipzone_id integer,
    CONSTRAINT cmhead_cmhead_number_check CHECK ((cmhead_number <> ''::text))
);


ALTER TABLE cmhead OWNER TO admin;

--
-- Name: TABLE cmhead; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE cmhead IS 'S/O Credit Memo header information';


--
-- Name: COLUMN cmhead.cmhead_freighttaxtype_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN cmhead.cmhead_freighttaxtype_id IS 'Deprecated column - DO NOT USE';


--
-- Name: COLUMN cmhead.cmhead_saletype_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN cmhead.cmhead_saletype_id IS 'Associated sale type for credit memo.';


--
-- Name: COLUMN cmhead.cmhead_shipzone_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN cmhead.cmhead_shipzone_id IS 'Associated shipping zone for credit memo.';


--
-- Name: shiptoinfo; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE shiptoinfo (
    shipto_id integer DEFAULT nextval(('shipto_shipto_id_seq'::text)::regclass) NOT NULL,
    shipto_cust_id integer NOT NULL,
    shipto_name text,
    shipto_salesrep_id integer,
    shipto_comments text,
    shipto_shipcomments text,
    shipto_shipzone_id integer,
    shipto_shipvia text,
    shipto_commission numeric(10,4) NOT NULL,
    shipto_shipform_id integer,
    shipto_shipchrg_id integer,
    shipto_active boolean NOT NULL,
    shipto_default boolean,
    shipto_num text,
    shipto_ediprofile_id integer,
    shipto_cntct_id integer,
    shipto_addr_id integer,
    shipto_taxzone_id integer,
    shipto_preferred_warehous_id integer DEFAULT (-1) NOT NULL
);


ALTER TABLE shiptoinfo OWNER TO admin;

--
-- Name: TABLE shiptoinfo; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE shiptoinfo IS 'Ship-To information';


--
-- Name: COLUMN shiptoinfo.shipto_ediprofile_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN shiptoinfo.shipto_ediprofile_id IS 'Deprecated column - DO NOT USE';


--
-- Name: taxzone; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE taxzone (
    taxzone_id integer NOT NULL,
    taxzone_code text NOT NULL,
    taxzone_descrip text,
    CONSTRAINT taxzone_taxzone_code_check CHECK ((taxzone_code <> ''::text))
);


ALTER TABLE taxzone OWNER TO admin;

--
-- Name: TABLE taxzone; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE taxzone IS 'Tax zone information';


--
-- Name: COLUMN taxzone.taxzone_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN taxzone.taxzone_id IS 'Primary key';


--
-- Name: COLUMN taxzone.taxzone_code; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN taxzone.taxzone_code IS 'Code';


--
-- Name: COLUMN taxzone.taxzone_descrip; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN taxzone.taxzone_descrip IS 'Description';



--
