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

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

BEGIN
  RETURN postVoucher(pVoheadid, fetchJournalNumber('AP-VO'), pPostCosts);
END;
$_$;


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

--
-- TOC entry 2050 (class 1255 OID 146566880)
-- Dependencies: 4536 8
-- Name: postvoucher(integer, integer, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postvoucher(integer, integer, boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pVoheadid ALIAS FOR $1;
  pJournalNumber ALIAS FOR $2;
  pPostCosts ALIAS FOR $3;
  _sequence INTEGER;
  _totalAmount_base NUMERIC;
  _totalAmount NUMERIC;
  _itemAmount_base NUMERIC;
  _itemAmount NUMERIC;
  _totalDiscountableAmount NUMERIC;
  _test INTEGER;
  _a RECORD;
  _d RECORD;
  _g RECORD;
  _p RECORD;
  _r RECORD;
  _costx RECORD;
  _pPostCosts BOOLEAN;
  _pExplain BOOLEAN;
  _pLowLevel BOOLEAN;
  _exchGainFreight NUMERIC;
  _taxBaseValue NUMERIC;
  _firstExchDateFreight	DATE;
  _tmpTotal		NUMERIC;
  _glDate		DATE;

BEGIN

  RAISE DEBUG 'postVoucher(%, %, %)', pVoheadid, pJournalNumber, pPostCosts;

  _pPostCosts := TRUE;
  _totalAmount_base := 0;
  _totalAmount := 0;
  _totalDiscountableAmount := 0;
  SELECT fetchGLSequence() INTO _sequence;

--  Cache Voucher Infomation
  SELECT vohead.*,
	 vend_number || '-' || vend_name || ' ' || vohead_reference
							  AS glnotes,
	 COALESCE(pohead_orderdate, vohead_docdate) AS pohead_orderdate,
	 COALESCE(pohead_curr_id, vohead_curr_id) AS pohead_curr_id INTO _p
  FROM vendinfo, vohead LEFT OUTER JOIN pohead ON (vohead_pohead_id = pohead_id)
  WHERE ( (vohead_id=pVoheadid)
  AND (vend_id=vohead_vend_id) )
  FOR UPDATE OF vohead;

  IF (_p.vohead_posted) THEN
    RAISE EXCEPTION 'Cannot post Voucher #% as it is already posted [xtuple: postVoucher, -10, %]',
			_p.vohead_number, _p.vohead_number;
  END IF;

  _glDate := COALESCE(_p.vohead_gldistdate, _p.vohead_distdate);

--  If the vohead_distdate is NULL, assume that this is a NULL vohead and quietly delete it
  IF (_p.vohead_distdate IS NULL) THEN
    DELETE FROM vohead WHERE vohead_id = pVoheadid;
    RETURN 0;
  END IF;
  IF (_p.vohead_amount <= 0) THEN
    RAISE EXCEPTION 'Cannot Post Voucher #% for a negative or zero amount (%) [xtuple: postVoucher, -1, %, %]',
			_p.vohead_number, _p.vohead_amount,
			_p.vohead_number, _p.vohead_amount;
  END IF;

-- there is no currency gain/loss on items, see issue 3892,
-- but there might be on freight, which is first encountered at p/o receipt
  SELECT recv_date::DATE INTO _firstExchDateFreight
      FROM recv
      WHERE (recv_vohead_id = pVoheadid);

  SELECT round(SUM(amount),4) INTO _tmpTotal
  FROM (
  SELECT SUM(vodist_amount) AS amount
    FROM vodist
   WHERE ( (vodist_vohead_id=pVoheadid)
     AND   (vodist_tax_id=-1) )
  UNION ALL
  SELECT SUM(voitem_freight) AS amount
    FROM voitem
   WHERE (voitem_vohead_id=pVoheadid)
  UNION ALL
  SELECT SUM(tax*-1)
  FROM 
    (SELECT round(sum(taxdetail_tax),2) AS tax,
              currToBase(_p.vohead_curr_id, round(sum(taxdetail_tax),2), _p.vohead_docdate) AS taxbasevalue
     FROM tax 
     JOIN calculateTaxDetailSummary('VO', pVoheadid, 'T') ON (taxdetail_tax_id=tax_id)
     GROUP BY tax_id, tax_sales_accnt_id
    ) AS taxdata
  ) AS data;

  IF (_tmpTotal IS NULL OR _tmpTotal <= 0) THEN
    RAISE EXCEPTION 'Cannot Post Voucher #% with negative or zero distributions (%) [xtuple: postVoucher, -2, %, %]',
			_p.vohead_number, _tmpTotal,
			_p.vohead_number, _tmpTotal;
  END IF;

  IF (_tmpTotal > _p.vohead_amount) THEN
    RAISE EXCEPTION 'Cannot Post Voucher #% with distributions greater than the voucher amount (% > %) [xtuple: postVoucher, -3, %, %, %]',
			_p.vohead_number, _tmpTotal, _p.vohead_amount,
			_p.vohead_number, _tmpTotal, _p.vohead_amount;
  END IF;

  IF (_tmpTotal < _p.vohead_amount) THEN
    RAISE EXCEPTION 'Cannot Post Voucher #% with distributions less than the voucher amount (% < %) [xtuple: postVoucher, -4, %, %, %]',
			_p.vohead_number, _tmpTotal, _p.vohead_amount,
			_p.vohead_number, _tmpTotal, _p.vohead_amount;
  END IF;

  SELECT DISTINCT poitem_linenumber INTO _test
    FROM vodist, voitem, poitem 
   WHERE ( (vodist_poitem_id=poitem_id)
     AND   (voitem_poitem_id=poitem_id)
     AND   (voitem_vohead_id=vodist_vohead_id)
     AND   ((poitem_qty_received - poitem_qty_vouchered) = 0)
     AND   (vodist_vohead_id=pVoheadid) )
   LIMIT 1;
  IF (FOUND) THEN
    RAISE EXCEPTION 'Cannot Post Voucher #% as one or more of the line items have already been fully vouchered. Check P/O Line #% [postVoucher, -6, %, %]',
         _p.vohead_number, _test,
         _p.vohead_number, _test;
  END IF;

--  Start by handling taxes
  FOR _r IN SELECT tax_sales_accnt_id, 
              round(sum(taxdetail_tax),2) AS tax,
              currToBase(_p.vohead_curr_id, round(sum(taxdetail_tax),2), _p.vohead_docdate) AS taxbasevalue
            FROM tax 
             JOIN calculateTaxDetailSummary('VO', pVoheadid, 'T') ON (taxdetail_tax_id=tax_id)
	    GROUP BY tax_id, tax_sales_accnt_id LOOP

    PERFORM insertIntoGLSeries( _sequence, 'A/P', 'VO', _p.vohead_number,
                                _r.tax_sales_accnt_id, 
                                _r.taxbasevalue,
                                _glDate, _p.glnotes );

    RAISE DEBUG 'postVoucher: _r.tax=%', _r.tax;

    _totalAmount_base := (_totalAmount_base - _r.taxbasevalue);
    _totalAmount := (_totalAmount - _r.tax);
     
  END LOOP;

-- Update item tax records with posting data
    UPDATE voitemtax SET 
      taxhist_docdate=_p.vohead_docdate,
      taxhist_distdate=_glDate,
      taxhist_curr_id=_p.vohead_curr_id,
      taxhist_curr_rate=curr_rate,
      taxhist_journalnumber=pJournalNumber
    FROM vohead
     JOIN voitem ON (vohead_id=voitem_vohead_id), 
     curr_rate
    WHERE ((vohead_id=pVoheadId)
      AND (taxhist_parent_id=voitem_id)
      AND (_p.vohead_curr_id=curr_id)
      AND (_p.vohead_docdate BETWEEN curr_effective 
                           AND curr_expires) );

-- Update Misc distributions with posting data
    UPDATE voheadtax SET 
      taxhist_docdate=_p.vohead_docdate,
      taxhist_distdate=_glDate,
      taxhist_curr_id=_p.vohead_curr_id,
      taxhist_curr_rate=curr_rate,
      taxhist_journalnumber=pJournalNumber
    FROM curr_rate
    WHERE ((taxhist_parent_id=pVoheadid)
      AND (_p.vohead_curr_id=curr_id)
      AND (_p.vohead_docdate BETWEEN curr_effective 
                           AND curr_expires) );

--  Loop through the vodist records for the passed vohead that
--  are posted against a P/O Item
  FOR _g IN SELECT DISTINCT poitem_id, voitem_id, voitem_qty, poitem_expcat_id,
                            poitem_invvenduomratio, poitem_prj_id,
                            COALESCE(itemsite_id, -1) AS itemsiteid,
                            COALESCE(itemsite_costcat_id, -1) AS costcatid,
                            COALESCE(itemsite_item_id, -1) AS itemsite_item_id,
                            (SELECT SUM(value) 
                             FROM (
                                SELECT SUM(recv_value) AS value
                                FROM recv
                                WHERE (recv_voitem_id=voitem_id)
                             UNION
                                SELECT SUM(poreject_value)*-1 AS value
                                FROM poreject
                                WHERE (poreject_voitem_id=voitem_id)) as data)
                           AS value_base,
			   (poitem_freight_received - poitem_freight_vouchered) /
			       (poitem_qty_received - poitem_qty_vouchered) * voitem_qty AS vouchered_freight,
                            currToBase(_p.pohead_curr_id,
				       (poitem_freight_received - poitem_freight_vouchered) /
				       (poitem_qty_received - poitem_qty_vouchered) * voitem_qty,
				        _firstExchDateFreight ) AS vouchered_freight_base,
			    voitem_freight,
			    currToBase(_p.vohead_curr_id, voitem_freight,
                                       _p.vohead_distdate) AS voitem_freight_base
            FROM vodist, voitem,
                 poitem LEFT OUTER JOIN itemsite ON (poitem_itemsite_id=itemsite_id)
            WHERE ( (vodist_poitem_id=poitem_id)
             AND (voitem_poitem_id=poitem_id)
             AND (voitem_vohead_id=vodist_vohead_id)
             AND (vodist_vohead_id=pVoheadid)) LOOP

--  Grab the G/L Accounts
    IF (_g.costcatid = -1) THEN
      SELECT getPrjAccntId(_g.poitem_prj_id, pp.accnt_id) AS pp_accnt_id,
             lb.accnt_id AS lb_accnt_id,
             fr.accnt_id AS freight_accnt_id INTO _a
      FROM expcat, accnt AS pp, accnt AS lb, accnt AS fr
      WHERE ( (expcat_purchprice_accnt_id=pp.accnt_id)
       AND (expcat_liability_accnt_id=lb.accnt_id)
       AND (expcat_freight_accnt_id=fr.accnt_id)
       AND (expcat_id=_g.poitem_expcat_id) );
      IF (NOT FOUND) THEN
        RAISE EXCEPTION 'Cannot Post Voucher #% due to unassigned G/L Accounts [xtuple: postVoucher, -7, %]',
                        _p.vohead_number, _p.vohead_number;
      END IF;
    ELSE
      SELECT getPrjAccntId(_g.poitem_prj_id, costcat_purchprice_accnt_id) AS pp_accnt_id,
             getPrjAccntId(_g.poitem_prj_id, costcat_liability_accnt_id) AS lb_accnt_id,
             getPrjAccntId(_g.poitem_prj_id, costcat_freight_accnt_id) AS freight_accnt_id
      INTO _a
      FROM costcat
      WHERE (costcat_id=_g.costcatid);
      IF (NOT FOUND) THEN
        RAISE EXCEPTION 'Cannot Post Voucher #% due to unassigned G/L Accounts [xtuple: postVoucher, -8, %]',
                        _p.vohead_number, _p.vohead_number;
      END IF;
    END IF;

--  Clear the Item Amount accumulator
    _itemAmount_base := 0;
    _itemAmount := 0;

--  Figure out the total posted value for this line item
    FOR _d IN SELECT vodist_id, vodist_amount, vodist_discountable,
		     _p.vohead_curr_id, vodist_costelem_id,
		     currToBase(_p.vohead_curr_id, vodist_amount,
				_p.vohead_distdate) AS vodist_amount_base
              FROM vodist
              WHERE ( (vodist_vohead_id=pVoheadid)
               AND (vodist_poitem_id=_g.poitem_id) ) LOOP

       _pExplain := FALSE;
       SELECT * INTO _costx
         FROM itemcost
        WHERE ( (itemcost_item_id = _g.itemsite_item_id)
          AND   (itemcost_costelem_id = _d.vodist_costelem_id) );

       IF (FOUND) THEN
         _pExplain := _costx.itemcost_lowlevel;
       END IF;

--  Post the cost to the Actual if requested
--      IF ( (pPostCosts) AND (_d.vodist_costelem_id <> -1) ) THEN
      IF ( (_d.vodist_costelem_id <> -1) AND (_g.itemsite_item_id <> -1) ) THEN
        PERFORM updateCost( _g.itemsite_item_id, _d.vodist_costelem_id,
                            _pExplain, (_d.vodist_amount / (_g.voitem_qty * _g.poitem_invvenduomratio)),
			    _p.vohead_curr_id );
      END IF;

--  Add the Distribution Amount to the Item Amount
      RAISE DEBUG 'postVoucher: _d.vodist_amount=%', _d.vodist_amount;

      _itemAmount_base := _itemAmount_base + ROUND(_d.vodist_amount_base, 2);
      _itemAmount := _itemAmount + _d.vodist_amount;
      IF (_d.vodist_discountable) THEN
        _totalDiscountableAmount := (_totalDiscountableAmount + _d.vodist_amount);
      END IF;

    END LOOP;

--  Distribute from the clearing account
    PERFORM insertIntoGLSeries( _sequence, 'A/P', 'VO', text(_p.vohead_number),
		_a.lb_accnt_id,
		round(_g.value_base + _g.vouchered_freight_base, 2) * -1,
		_glDate, _p.glnotes );


--  Attribute the correct portion to currency gain/loss
    _exchGainFreight := 0;
    SELECT currGain(_p.pohead_curr_id, _g.vouchered_freight,
		    _firstExchDateFreight, _p.vohead_distdate )
		    INTO _exchGainFreight;
    IF (round(_exchGainFreight, 2) <> 0) THEN
	PERFORM insertIntoGLSeries(_sequence, 'A/P', 'VO',
	    text(_p.vohead_number),
	    getGainLossAccntId(_a.lb_accnt_id), round(_exchGainFreight, 2),
	   _glDate, _p.glnotes);
    END IF;

--  Distribute the remaining variance to the Purchase Price Variance account
    IF (round(_itemAmount_base, 2) <> round(_g.value_base, 2)) THEN
      _tmpTotal := round(_itemAmount_base, 2) - round(_g.value_base, 2);
      PERFORM insertIntoGLSeries( _sequence, 'A/P', 'VO', text(_p.vohead_number),
			          _a.pp_accnt_id,
			          _tmpTotal * -1,
			          _glDate, _p.glnotes );
    END IF;

--  Distribute the remaining freight variance to the Purchase Price Variance account
    IF (round(_g.voitem_freight_base + _exchGainFreight, 2) <> round(_g.vouchered_freight_base, 2)) THEN
      _tmpTotal := round(_g.voitem_freight_base + _exchGainFreight, 2) - round(_g.vouchered_freight_base, 2);
      PERFORM insertIntoGLSeries( _sequence, 'A/P', 'VO', text(_p.vohead_number),
        _a.freight_accnt_id,
	      _tmpTotal * -1,
	      _glDate, _p.glnotes );
    END IF;

--  Add the distribution amount to the total amount to distribute
    RAISE DEBUG 'postVoucher: _itemAmount=%', _itemAmount;

    _totalAmount_base := (_totalAmount_base + _itemAmount_base + _g.voitem_freight_base);
    _totalAmount := (_totalAmount + _itemAmount + _g.voitem_freight);

--  Post all the Tagged Receivings for this P/O Item as Invoiced and
--  record the purchase and receive costs
--  Comment out because recv cost is set at receiving now.
    UPDATE recv
    SET recv_invoiced=TRUE,
	recv_recvcost_curr_id=basecurrid(),
        recv_recvcost=round(_g.value_base / _g.voitem_qty, 2)
    FROM poitem
    WHERE ((recv_orderitem_id=poitem_id)
      AND  (recv_order_type='PO')
      AND  (recv_orderitem_id=_g.poitem_id)
      AND  (recv_vohead_id=pVoheadid) );

--  Post all the Tagged Rejections for this P/O Item as Invoiced
    UPDATE poreject
    SET poreject_invoiced=TRUE
    WHERE ( (poreject_poitem_id=_g.poitem_id)
     AND (poreject_vohead_id=pVoheadid) );

--  Update the qty and freight vouchered fields
    UPDATE poitem
       SET poitem_qty_vouchered = (poitem_qty_vouchered + _g.voitem_qty),
           poitem_freight_vouchered = (poitem_freight_vouchered + _g.vouchered_freight)
     WHERE (poitem_id=_g.poitem_id);

  END LOOP;

--  Loop through the vodist records for the passed vohead that
--  are not posted against a P/O Item
--  Skip the tax distributions
  FOR _d IN SELECT vodist_id, vodist_discountable,
		   currToBase(_p.vohead_curr_id, vodist_amount,
			      _p.vohead_distdate) AS vodist_amount_base,
		   vodist_amount,
		   vodist_accnt_id, vodist_expcat_id
            FROM vodist
            WHERE ( (vodist_vohead_id=pVoheadid)
             AND (vodist_poitem_id=-1)
             AND (vodist_tax_id=-1) ) LOOP

--  Distribute from the misc. account
    IF (_d.vodist_accnt_id = -1) THEN
      PERFORM insertIntoGLSeries( _sequence, 'A/P', 'VO', text(_p.vohead_number),
			  expcat_exp_accnt_id,
			  round(_d.vodist_amount_base, 2) * -1,
			  _glDate, _p.glnotes )
         FROM expcat
        WHERE (expcat_id=_d.vodist_expcat_id);
    ELSE
      PERFORM insertIntoGLSeries( _sequence, 'A/P', 'VO', text(_p.vohead_number),
			  _d.vodist_accnt_id,
			  round(_d.vodist_amount_base, 2) * -1,
			  _glDate, _p.glnotes );
    END IF;

--  Add the Distribution Amount to the Total Amount
    RAISE DEBUG 'postVoucher: _d.vodist_amount=%', _d.vodist_amount;

    _totalAmount_base := _totalAmount_base + ROUND(_d.vodist_amount_base, 2);
    _totalAmount := _totalAmount + _d.vodist_amount;
    IF (_d.vodist_discountable) THEN
      _totalDiscountableAmount := (_totalDiscountableAmount + _d.vodist_amount);
    END IF;

  END LOOP;

  SELECT insertIntoGLSeries( _sequence, 'A/P', 'VO', text(vohead_number),
                             accnt_id, round(_totalAmount_base, 2),
			     _glDate, _p.glnotes ) INTO _test
  FROM vohead LEFT OUTER JOIN accnt ON (accnt_id=findAPAccount(vohead_vend_id))
  WHERE ( (findAPAccount(vohead_vend_id)=0 OR accnt_id > 0) -- G/L interface might be disabled
    AND (vohead_id=pVoheadid) );
  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Cannot Post Voucher #% due to an unassigned A/P Account [xtuple: postVoucher, -9, %]',
                    _p.vohead_number, _p.vohead_number;
  END IF;

  PERFORM postGLSeries(_sequence, pJournalNumber);

--  Create the A/P Open Item
  RAISE DEBUG 'postVoucher: _totalAmount=%, _totalDiscountableAmount=%',
                _totalAmount, _totalDiscountableAmount;

  INSERT INTO apopen
  ( apopen_journalnumber, apopen_docdate, apopen_duedate, apopen_distdate, apopen_open,
    apopen_terms_id, apopen_vend_id, apopen_doctype,
    apopen_docnumber, apopen_invcnumber, apopen_ponumber, apopen_reference,
    apopen_amount, apopen_paid, apopen_notes, apopen_username, apopen_posted,
    apopen_curr_id, apopen_discountable_amount )
-- TODO: 
  SELECT pJournalNumber, vohead_docdate, vohead_duedate, _glDate, TRUE,
         vohead_terms_id, vohead_vend_id, 'V',
         vohead_number, vohead_invcnumber, COALESCE(TEXT(pohead_number), 'Misc.'), vohead_reference,
         round(_totalAmount, 2), 0, '', getEffectiveXtUser(), FALSE,
         vohead_curr_id, round(_totalDiscountableAmount, 2)
  FROM vohead LEFT OUTER JOIN pohead ON (vohead_pohead_id=pohead_id)
  WHERE (vohead_id=pVoheadid);

--  Close all of the P/O Items that should be closed by this Voucher
  UPDATE poitem
  SET poitem_status='C'
  FROM voitem
  WHERE ( (voitem_poitem_id=poitem_id)
   AND (voitem_close)
   AND (voitem_vohead_id=pVoheadid) );

--  Check the P/O items and if they are all closed go ahead
--  and close the P/O head.
  IF ( (SELECT (count(*) < 1)
          FROM vohead, poitem
         WHERE ((vohead_pohead_id=poitem_pohead_id)
           AND  (poitem_status<>'C')
           AND  (vohead_id=pVoheadid) ) ) ) THEN
    PERFORM closePo(vohead_pohead_id)
       FROM vohead
      WHERE (vohead_id=pVoheadid);
  END IF;

--  Set the vohead as posted
  UPDATE vohead
  SET vohead_posted=TRUE, vohead_gldistdate=_glDate
  WHERE (vohead_id=pVoheadid);

  RETURN pJournalNumber;

END;
$_$;


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

--
-- TOC entry 2051 (class 1255 OID 146566882)
-- Dependencies: 4536 8
-- Name: postvouchers(boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postvouchers(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
  pPostCosts ALIAS FOR $1;
  _journalNumber INTEGER;

BEGIN

  SELECT fetchJournalNumber('AP-VO') INTO _journalNumber;

  PERFORM postVoucher(vohead_id, _journalNumber, pPostCosts)
  FROM vohead
  WHERE (NOT vohead_posted);

  RETURN _journalNumber;

END;
$_$;


ALTER FUNCTION public.postvouchers(boolean) OWNER TO admin;

--
-- TOC entry 2052 (class 1255 OID 146566883)
-- Dependencies: 4536 8
-- Name: primarykeyfields(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION primarykeyfields(text, text) 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
  pSchema       ALIAS FOR $1;
  pRelation     ALIAS FOR $2;
  _colname      TEXT;
  _counter      INTEGER := 0;
  _result       TEXT[];

BEGIN
  EXECUTE 'SELECT ARRAY(SELECT attname
                         FROM pg_attribute
                         JOIN pg_class idx ON (attrelid         = idx.oid)
                         JOIN pg_namespace ON (idx.relnamespace = pg_namespace.oid)
                         JOIN pg_index     ON (idx.oid          = indexrelid)
                         JOIN pg_class tab ON (indrelid         = tab.oid)
                        WHERE NOT attisdropped
                          AND nspname = ''' || pSchema || ''' 
                          AND indisprimary
                          AND LOWER(tab.relname) = ''' || pRelation || '''
                       ORDER BY attnum);'
  INTO _result;

  RETURN _result;
END;
$_$;


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

--
-- TOC entry 9070 (class 0 OID 0)
-- Dependencies: 2052
-- Name: FUNCTION primarykeyfields(text, text); Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON FUNCTION primarykeyfields(text, text) IS 'Return an array containing the names of the primary key fields of pSchema.pRelation. The first key field is in _result[1].';


--
-- TOC entry 2053 (class 1255 OID 146566884)
-- Dependencies: 2856 4536 8
-- Name: prj(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION prj() RETURNS SETOF prj
    LANGUAGE plpgsql
    AS $$
-- Copyright (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 prj%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 ('MaintainAllProjects','ViewAllProjects','MaintainPersonalProjects','ViewPersonalProjects')
  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 prj
    LOOP
      RETURN NEXT _row;
    END LOOP;
  -- Otherwise if have any other grant, must be personal privilege.
  ELSIF (_grant) THEN
    FOR _row IN 
      SELECT * FROM prj 
      WHERE getEffectiveXtUser() IN (prj_owner_username, prj_username)
    LOOP
      RETURN NEXT _row;
    END LOOP;
  END IF;

  RETURN;

END;
$$;


ALTER FUNCTION public.prj() OWNER TO admin;

--
-- TOC entry 9071 (class 0 OID 0)
-- Dependencies: 2053
-- Name: FUNCTION prj(); Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- TOC entry 232 (class 1259 OID 146566885)
-- Dependencies: 6041 8
-- Name: prjtask; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE prjtask (
    prjtask_id integer NOT NULL,
    prjtask_number text NOT NULL,
    prjtask_name text NOT NULL,
    prjtask_descrip text,
    prjtask_prj_id integer NOT NULL,
    prjtask_anyuser boolean,
    prjtask_status character(1) NOT NULL,
    prjtask_hours_budget numeric(18,6) NOT NULL,
    prjtask_hours_actual numeric(18,6) NOT NULL,
    prjtask_exp_budget numeric(16,4) NOT NULL,
    prjtask_exp_actual numeric(16,4) NOT NULL,
    prjtask_owner_username text,
    prjtask_start_date date,
    prjtask_due_date date,
    prjtask_assigned_date date,
    prjtask_completed_date date,
    prjtask_username text,
    CONSTRAINT prjtask_prjtask_status_check CHECK ((prjtask_status = ANY (ARRAY['P'::bpchar, 'O'::bpchar, 'C'::bpchar])))
);


ALTER TABLE public.prjtask OWNER TO admin;

--
-- TOC entry 9072 (class 0 OID 0)
-- Dependencies: 232
-- Name: TABLE prjtask; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE prjtask IS 'Project Task information';


--
-- TOC entry 2056 (class 1255 OID 146566892)
-- Dependencies: 2882 4536 8
-- Name: prjtask(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION prjtask() RETURNS SETOF prjtask
    LANGUAGE plpgsql
    AS $$
-- Copyright (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 prjtask%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 ('MaintainAllProjects','ViewAllProjects','MaintainPersonalProjects','ViewPersonalProjects')
  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 prjtask
    LOOP
      RETURN NEXT _row;
    END LOOP;
  -- Otherwise if have any other grant, must be personal privilege.
  ELSIF (_grant) THEN
    FOR _row IN 
      SELECT prjtask.* FROM prjtask
      JOIN prj ON prj_id=prjtask_prj_id
      WHERE getEffectiveXtUser() IN (prjtask_owner_username,prjtask_username,prj_username,prj_owner_username)
    LOOP
      RETURN NEXT _row;
    END LOOP;
  END IF;

  RETURN;

END;
$$;


ALTER FUNCTION public.prjtask() OWNER TO admin;

--
-- TOC entry 9074 (class 0 OID 0)
-- Dependencies: 2056
-- Name: FUNCTION prjtask(); Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- TOC entry 2057 (class 1255 OID 146566893)
-- Dependencies: 4536 8
-- Name: purgecreditmemos(date); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  DELETE FROM cmitem
  WHERE (cmitem_id IN ( SELECT cmitem_id
                        FROM cmitem, cmhead
                        WHERE ( (cmitem_cmhead_id=cmhead_id)
                          AND   (cmhead_posted)
                          AND   (cmhead_printed)
                          AND   (cmhead_docdate<=pCutoffDate)
                          AND   (checkCreditMemoSitePrivs(cmhead_id)) ) ) );

  DELETE FROM cmhead
  WHERE ( (cmhead_posted)
    AND   (cmhead_printed)
    AND   (cmhead_docdate<=pCutoffDate)
    AND   (checkCreditMemoSitePrivs(cmhead_id)) );

  RETURN TRUE;

END;
$_$;


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

--
-- TOC entry 2058 (class 1255 OID 146566894)
-- Dependencies: 4536 8
-- Name: purgecrmacctmerge(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION purgecrmacctmerge(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
  pDestid       ALIAS FOR $1;
  _result       INTEGER := 0;
  _tmpcount     INTEGER := 0;
BEGIN
  IF EXISTS(SELECT 1
              FROM crmacctsel
             WHERE crmacctsel_dest_crmacct_id=pDestid) THEN
    DELETE FROM crmacctsel WHERE crmacctsel_dest_crmacct_id = pDestid;
    GET DIAGNOSTICS _result = ROW_COUNT;

  ELSIF EXISTS(SELECT 1
                 FROM mrgundo
                WHERE mrgundo_base_schema='public'
                  AND mrgundo_base_table='crmacct'
                  AND mrgundo_base_id=pDestid) THEN

    DELETE FROM crmacct
     WHERE crmacct_id IN (
              SELECT mrgundo_pkey_id
                FROM mrgundo
               WHERE mrgundo_schema   = 'public'
                 AND mrgundo_table    = 'crmacct'
                 and mrgundo_pkey_col = 'crmacct_id'
                 AND mrgundo_col IS NULL
                 AND mrgundo_base_schema = 'public'
                 AND mrgundo_base_table  = 'crmacct'
                 AND mrgundo_base_id     = pDestid)
        AND crmacct_id != pDestid;
    GET DIAGNOSTICS _result = ROW_COUNT;

    DELETE FROM mrgundo
     WHERE mrgundo_base_schema ='public'
       AND mrgundo_base_table  ='crmacct'
       AND mrgundo_base_id     = pDestid;
    GET DIAGNOSTICS _tmpcount = ROW_COUNT;

    _result := _result + _tmpcount;
  END IF;

  RETURN _result;
END;
$_$;


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

--
-- TOC entry 2059 (class 1255 OID 146566895)
-- Dependencies: 4536 8
-- Name: purgeinvoicerecord(date, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION purgeinvoicerecord(date, 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
  pCutoffDate ALIAS FOR $1;
  pInvcheadId ALIAS FOR $2;
  _r RECORD;
  _ra RECORD;
  _raheadid INTEGER;
  _result INTEGER;
  _debug BOOLEAN := FALSE;

BEGIN

-- Purge records where the entire Invoice, Billing, Shipper, Sales Order
-- chain of associated documents are closed and complete

  FOR _r IN
  SELECT invchead_id, cobmisc_id, shiphead_id, ordershipped.cohead_id AS ordship_id, orderinvoiced.cohead_id AS ordinv_id
    FROM invchead LEFT OUTER JOIN invcitem ON (invcitem_invchead_id=invchead_id)
                  LEFT OUTER JOIN cobmisc ON (cobmisc_invcnumber::TEXT=invchead_invcnumber)
                  LEFT OUTER JOIN shipitem ON (shipitem_invcitem_id=invcitem_id)
                  LEFT OUTER JOIN shiphead ON (shiphead_id=shipitem_shiphead_id)
                  LEFT OUTER JOIN cohead ordershipped ON (ordershipped.cohead_id=shiphead_order_id)
                  LEFT OUTER JOIN coitem ON (coitem_id=invcitem_coitem_id)
                  LEFT OUTER JOIN cohead orderinvoiced ON (orderinvoiced.cohead_id=coitem_cohead_id)
   WHERE ( (invchead_id = pInvcheadId)
     AND   (invchead_posted)
     AND   (checkInvoiceSitePrivs(invchead_id)) )
  GROUP BY invchead_id, cobmisc_id, shiphead_id, ordship_id, ordinv_id LOOP

-- Check Billing

-- Billing header (cobmisc) must be posted
    SELECT cobmisc_id INTO _result
      FROM cobmisc
     WHERE ( (cobmisc_id=_r.cobmisc_id) AND (NOT cobmisc_posted) );
    IF (FOUND) THEN
      RETURN 'Billing not closed';
    END IF;

-- Billing line items (cobill), associated Invoice line items, and
-- associated Sales Order line items must be closed, posted, and after cutoff date
    SELECT cobill_id INTO _result
      FROM cobill JOIN invcitem ON (invcitem_id=cobill_invcitem_id)
                  JOIN invchead ON ( (invchead_id=invcitem_invchead_id) AND
                                     ((NOT invchead_posted) OR (invchead_invcdate > pCutoffDate)) )
                  JOIN coitem ON ( (coitem_id=cobill_coitem_id) AND
                                   (coitem_status NOT IN ('C', 'X')) )
     WHERE (cobill_cobmisc_id=_r.cobmisc_id);
    IF (FOUND) THEN
      RETURN 'Invoice/Sales Order associated with Billing not closed';
    END IF;

-- Check Shipping

-- Shipping header (shiphead) must be shipped
    SELECT shiphead_id INTO _result
      FROM shiphead
     WHERE ( (shiphead_id=_r.shiphead_id) AND (NOT shiphead_shipped) );
    IF (FOUND) THEN
      RETURN 'Shipper not closed';
    END IF;

-- Shipping line items (shipitem) and associated Sales Order line items
-- must be closed
    SELECT shiphead_id INTO _result
      FROM shiphead, cohead, coitem
     WHERE ( (shiphead_id=_r.shiphead_id)
       AND   ( (shiphead_order_type='SO') AND (shiphead_order_id=cohead_id) )
       AND   (coitem_cohead_id=cohead_id)
       AND   (coitem_status NOT IN ('C', 'X')) );
    IF (FOUND) THEN
      RETURN 'Sales Order associated with Shipper not closed';
    END IF;

-- Shipping line items (shipitem) and associated Invoices must be posted
-- and after cutoff date
    SELECT shiphead_id INTO _result
      FROM shiphead JOIN shipitem ON (shipitem_shiphead_id=shiphead_id)
                    JOIN invcitem ON (invcitem_id=shipitem_invcitem_id)
                    JOIN invchead ON ( (invchead_id=invcitem_invchead_id) AND
                                       ((NOT invchead_posted) OR (invchead_invcdate > pCutoffDate)) )
     WHERE (shiphead_id=_r.shiphead_id);
    IF (FOUND) THEN
      RETURN 'Invoice associated with Shipper not closed';
    END IF;

-- Check Sales Order

-- Sales Order line items (coitem) must be closed
    SELECT cohead_id INTO _result
      FROM cohead JOIN coitem ON ( (coitem_cohead_id=cohead_id) AND
                                   (coitem_status NOT IN ('C', 'X')) )
     WHERE (cohead_id=_r.ordship_id);
    IF (FOUND) THEN
      RETURN 'Shipped Sales Order not closed';
    END IF;
    SELECT cohead_id INTO _result
      FROM cohead JOIN coitem ON ( (coitem_cohead_id=cohead_id) AND
                                   (coitem_status NOT IN ('C', 'X')) )
     WHERE (cohead_id=_r.ordinv_id);
    IF (FOUND) THEN
      RETURN 'Invoiced Sales Order not closed';
    END IF;

    IF (fetchMetricBool('MultiWhs')) THEN
    -- Check Original Return Authorization and cross check to New Sales Order
      SELECT rahead_id INTO _result
        FROM rahead JOIN raitem ON ( (raitem_rahead_id=rahead_id) AND
                                     (raitem_status NOT IN ('C', 'X')) )
                    JOIN coitem ON ( (coitem_id=raitem_new_coitem_id) AND
                                     (coitem_status NOT IN ('C', 'X')) )
                    JOIN invcitem ON (invcitem_coitem_id=coitem_id)
                    JOIN invchead ON ( (invchead_id=invcitem_invchead_id) AND
                                       ((NOT invchead_posted) OR (invchead_invcdate > pCutoffDate)) )
       WHERE (rahead_orig_cohead_id=_r.ordship_id);
      IF (FOUND) THEN
        RETURN 'Shipped Original Return Authorization not closed';
      END IF;
      SELECT rahead_id INTO _result
        FROM rahead JOIN raitem ON ( (raitem_rahead_id=rahead_id) AND
                                     (raitem_status NOT IN ('C', 'X')) )
                    JOIN coitem ON ( (coitem_id=raitem_new_coitem_id) AND
                                     (coitem_status NOT IN ('C', 'X')) )
                    JOIN invcitem ON (invcitem_coitem_id=coitem_id)
                    JOIN invchead ON ( (invchead_id=invcitem_invchead_id) AND
                                       ((NOT invchead_posted) OR (invchead_invcdate > pCutoffDate)) )
       WHERE (rahead_orig_cohead_id=_r.ordinv_id);
      IF (FOUND) THEN
        RETURN 'Invoiced Original Return Authorization not closed';
      END IF;

  -- Check New Return Authorization
      SELECT rahead_id INTO _result
        FROM rahead JOIN raitem ON ( (raitem_rahead_id=rahead_id) AND
                                     (NOT raitem_status IN ('C', 'X')) )
                    JOIN coitem ON ( (coitem_id=raitem_orig_coitem_id) AND
                                     (NOT coitem_status IN ('C', 'X')) )
                    JOIN invcitem ON (invcitem_coitem_id=coitem_id)
                    JOIN invchead ON ( (invchead_id=invcitem_invchead_id) AND
                                       ((NOT invchead_posted) OR (invchead_invcdate > pCutoffDate)) )
       WHERE (rahead_new_cohead_id=_r.ordship_id);
      IF (FOUND) THEN
        RETURN 'Shipped New Return Authorization not closed';
      END IF;
      SELECT rahead_id INTO _result
        FROM rahead JOIN raitem ON ( (raitem_rahead_id=rahead_id) AND
                                     (NOT raitem_status IN ('C', 'X')) )
                    JOIN coitem ON ( (coitem_id=raitem_orig_coitem_id) AND
                                     (NOT coitem_status IN ('C', 'X')) )
                    JOIN invcitem ON (invcitem_coitem_id=coitem_id)
                    JOIN invchead ON ( (invchead_id=invcitem_invchead_id) AND
                                       ((NOT invchead_posted) OR (invchead_invcdate > pCutoffDate)) )
       WHERE (rahead_new_cohead_id=_r.ordinv_id);
      IF (FOUND) THEN
        RETURN 'Invoiced New Return Authorization not closed';
      END IF;
    END IF;

-- Check Lot/Serial Registration

    IF (fetchMetricBool('MultiWhs')) THEN
  -- Registration associated with Sales Order must be expired
      SELECT lsreg_id INTO _result
        FROM lsreg
       WHERE ( (lsreg_cohead_id=_r.ordship_id)
         AND   (lsreg_expiredate > CURRENT_DATE) );
      IF (FOUND) THEN
        RETURN 'Shipped Sales Order Lot/Serial Registration not closed';
      END IF;
      SELECT lsreg_id INTO _result
        FROM lsreg
       WHERE ( (lsreg_cohead_id=_r.ordinv_id)
         AND   (lsreg_expiredate > CURRENT_DATE) );
      IF (FOUND) THEN
        RETURN 'Invoiced Sales Order Lot/Serial Registration not closed';
      END IF;

  -- Registration associated with Shipping must be expired
      SELECT lsreg_id INTO _result
        FROM lsreg
       WHERE ( (lsreg_cohead_id=_r.shiphead_id)
         AND   (lsreg_expiredate > CURRENT_DATE) );
      IF (FOUND) THEN
        RETURN 'Shipper Lot/Serial Registration not closed';
      END IF;
    END IF;

    IF (fetchMetricBool('MultiWhs')) THEN
  -- Registration associated with Original Return Authorization must be expired
      SELECT rahead_id INTO _result
        FROM rahead JOIN raitem ON (raitem_rahead_id=rahead_id)
                    JOIN raitemls ON (raitemls_raitem_id=raitem_id)
                    JOIN lsreg ON ( (lsreg_ls_id=raitemls_ls_id) AND
                                    (lsreg_expiredate > CURRENT_DATE) )
       WHERE (rahead_orig_cohead_id=_r.ordship_id);
      IF (FOUND) THEN
        RETURN 'Shipped Original Return Authorization Lot/Serial Registration not closed';
      END IF;
      SELECT rahead_id INTO _result
        FROM rahead JOIN raitem ON (raitem_rahead_id=rahead_id)
                    JOIN raitemls ON (raitemls_raitem_id=raitem_id)
                    JOIN lsreg ON ( (lsreg_ls_id=raitemls_ls_id) AND
                                    (lsreg_expiredate > CURRENT_DATE) )
       WHERE (rahead_orig_cohead_id=_r.ordinv_id);
      IF (FOUND) THEN
        RETURN 'Invoiced Original Return Authorization Lot/Serial Registration not closed';
      END IF;

  -- Registration associated with New Return Authorization must be expired
      SELECT rahead_id INTO _result
        FROM rahead JOIN raitem ON (raitem_rahead_id=rahead_id)
                    JOIN raitemls ON (raitemls_raitem_id=raitem_id)
                    JOIN lsreg ON ( (lsreg_ls_id=raitemls_ls_id) AND
                                    (lsreg_expiredate > CURRENT_DATE) )
       WHERE (rahead_new_cohead_id=_r.ordship_id);
      IF (FOUND) THEN
        RETURN 'Shipped New Return Authorization Lot/Serial Registration not closed';
      END IF;
      SELECT rahead_id INTO _result
        FROM rahead JOIN raitem ON (raitem_rahead_id=rahead_id)
                    JOIN raitemls ON (raitemls_raitem_id=raitem_id)
                    JOIN lsreg ON ( (lsreg_ls_id=raitemls_ls_id) AND
                                    (lsreg_expiredate > CURRENT_DATE) )
       WHERE (rahead_new_cohead_id=_r.ordinv_id);
      IF (FOUND) THEN
        RETURN 'Invoiced New Return Authorization Lot/Serial Registration not closed';
      END IF;
    END IF;

-- Cash Advances associated with Sales Order cannot exist
    SELECT aropenalloc_doc_id INTO _result   
      FROM aropenalloc
     WHERE ((aropenalloc_doctype='S')
       AND  (aropenalloc_doc_id=_r.ordship_id));
    IF (FOUND) THEN
      RETURN 'Shipped Cash Advance not closed';
    END IF;
    SELECT aropenalloc_doc_id INTO _result   
      FROM aropenalloc
     WHERE ((aropenalloc_doctype='S')
       AND  (aropenalloc_doc_id=_r.ordinv_id));
    IF (FOUND) THEN
      RETURN 'Invoiced Cash Advance not closed';
    END IF;

  END LOOP;

-- Everything is OK, delete the chain
  FOR _r IN
  SELECT invchead_id, cobmisc_id, shiphead_id, ordershipped.cohead_id AS ordship_id, orderinvoiced.cohead_id AS ordinv_id
    FROM invchead LEFT OUTER JOIN invcitem ON (invcitem_invchead_id=invchead_id)
                  LEFT OUTER JOIN cobmisc ON (cobmisc_invcnumber::TEXT=invchead_invcnumber)
                  LEFT OUTER JOIN shipitem ON (shipitem_invcitem_id=invcitem_id)
                  LEFT OUTER JOIN shiphead ON (shiphead_id=shipitem_shiphead_id)
                  LEFT OUTER JOIN cohead ordershipped ON (ordershipped.cohead_id=shiphead_order_id)
                  LEFT OUTER JOIN coitem ON (coitem_id=invcitem_coitem_id)
                  LEFT OUTER JOIN cohead orderinvoiced ON (orderinvoiced.cohead_id=coitem_cohead_id)
   WHERE ( (invchead_id = pInvcheadId)
     AND   (invchead_posted)
     AND   (checkInvoiceSitePrivs(invchead_id)) )
  GROUP BY invchead_id, cobmisc_id, shiphead_id, ordship_id, ordinv_id LOOP

    IF (fetchMetricBool('MultiWhs')) THEN
      FOR _ra IN
        SELECT rahead_id
        FROM rahead
        WHERE (rahead_orig_cohead_id=_r.ordship_id) LOOP
        IF (_debug) THEN
          RAISE NOTICE 'Deleting Original Return head id %', _ra.rahead_id;
        END IF;
        DELETE FROM raitemls WHERE (raitemls_raitem_id IN (SELECT raitem_id
                                                           FROM raitem
                                                           WHERE (raitem_rahead_id=_ra.rahead_id)));
        DELETE FROM rahist WHERE (rahist_rahead_id=_ra.rahead_id);
        DELETE FROM raitem WHERE (raitem_rahead_id=_ra.rahead_id);
        DELETE FROM rahead WHERE (rahead_id=_ra.rahead_id);
      END LOOP;
      FOR _ra IN
        SELECT rahead_id
        FROM rahead
        WHERE (rahead_orig_cohead_id=_r.ordinv_id) LOOP
        IF (_debug) THEN
          RAISE NOTICE 'Deleting Original Return head id %', _ra.rahead_id;
        END IF;
        DELETE FROM raitemls WHERE (raitemls_raitem_id IN (SELECT raitem_id
                                                           FROM raitem
                                                           WHERE (raitem_rahead_id=_ra.rahead_id)));
        DELETE FROM rahist WHERE (rahist_rahead_id=_ra.rahead_id);
        DELETE FROM raitem WHERE (raitem_rahead_id=_ra.rahead_id);
        DELETE FROM rahead WHERE (rahead_id=_ra.rahead_id);
      END LOOP;

      FOR _ra IN
        SELECT rahead_id
          FROM rahead
         WHERE (rahead_new_cohead_id=_r.ordship_id) LOOP
        IF (_debug) THEN
          RAISE NOTICE 'Deleting New Return head id %', _ra.rahead_id;
        END IF;
        DELETE FROM raitemls WHERE (raitemls_raitem_id IN (SELECT raitem_id
                                                           FROM raitem
                                                           WHERE (raitem_rahead_id=_ra.rahead_id)));
        DELETE FROM rahist WHERE (rahist_rahead_id=_ra.rahead_id);
        DELETE FROM raitem WHERE (raitem_rahead_id=_ra.rahead_id);
        DELETE FROM rahead WHERE (rahead_id=_ra.rahead_id);
      END LOOP;
      FOR _ra IN
        SELECT rahead_id
          FROM rahead
         WHERE (rahead_new_cohead_id=_r.ordinv_id) LOOP
        IF (_debug) THEN
          RAISE NOTICE 'Deleting New Return head id %', _ra.rahead_id;
        END IF;
        DELETE FROM raitemls WHERE (raitemls_raitem_id IN (SELECT raitem_id
                                                           FROM raitem
                                                           WHERE (raitem_rahead_id=_ra.rahead_id)));
        DELETE FROM rahist WHERE (rahist_rahead_id=_ra.rahead_id);
        DELETE FROM raitem WHERE (raitem_rahead_id=_ra.rahead_id);
        DELETE FROM rahead WHERE (rahead_id=_ra.rahead_id);
      END LOOP;
    END IF;

    IF (fetchMetricBool('MultiWhs')) THEN
      IF (_debug) THEN
        RAISE NOTICE 'Deleting Lot/Serial Registrations';
      END IF;
      DELETE FROM lsreg WHERE (lsreg_cohead_id=_r.ordship_id);
      DELETE FROM lsreg WHERE (lsreg_cohead_id=_r.ordinv_id);
      DELETE FROM lsreg WHERE (lsreg_shiphead_id=_r.shiphead_id);
    END IF;

    IF (_debug) THEN
      RAISE NOTICE 'Deleting Shipped Sales Order head id %', _r.ordship_id;
    END IF;
    DELETE FROM payco WHERE (payco_cohead_id=_r.ordship_id);
    -- Delete kit components first
    DELETE FROM coitem WHERE (coitem_cohead_id=_r.ordship_id AND coitem_subnumber > 0);
    DELETE FROM coitem WHERE (coitem_cohead_id=_r.ordship_id);
    DELETE FROM cohead WHERE (cohead_id=_r.ordship_id);

    IF (_debug) THEN
      RAISE NOTICE 'Deleting Sales Order head id %', _r.ordinv_id;
    END IF;
    DELETE FROM payco WHERE (payco_cohead_id=_r.ordinv_id);
    -- Delete kit components first
    DELETE FROM coitem WHERE (coitem_cohead_id=_r.ordinv_id AND coitem_subnumber > 0);
    DELETE FROM coitem WHERE (coitem_cohead_id=_r.ordinv_id);
    DELETE FROM cohead WHERE (cohead_id=_r.ordinv_id);

    IF (_debug) THEN
      RAISE NOTICE 'Deleting Ship head id %', _r.shiphead_id;
    END IF;
    DELETE FROM shipitem WHERE (shipitem_shiphead_id=_r.shiphead_id);
    DELETE FROM pack WHERE (pack_shiphead_id=_r.shiphead_id);
    DELETE FROM shiphead WHERE (shiphead_id=_r.shiphead_id);

    IF (_debug) THEN
      RAISE NOTICE 'Deleting Billing head id %', _r.cobmisc_id;
    END IF;
    DELETE FROM cobill WHERE (cobill_cobmisc_id=_r.cobmisc_id);
    DELETE FROM cobmisc WHERE (cobmisc_id=_r.cobmisc_id);
  END LOOP;
  
-- Everything is OK, delete the Invoice
  IF (_debug) THEN
    RAISE NOTICE 'Deleting Invoice head id %', _r.invchead_id;
  END IF;
  DELETE FROM invcitem WHERE (invcitem_invchead_id=_r.invchead_id);
  DELETE FROM invchead WHERE (invchead_id=_r.invchead_id);

  RETURN 'Purged';

END;
$_$;


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

--
-- TOC entry 2060 (class 1255 OID 146566897)
-- Dependencies: 4536 8
-- Name: purgeinvoicerecords(date); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

-- Remove the shipitem records
  DELETE FROM shipitem
  WHERE (shipitem_invcitem_id IN (SELECT invcitem_id 
                                  FROM invcitem 
                                  WHERE invcitem_invchead_id IN ( SELECT invchead_id
                                     FROM invchead
                                     WHERE ( (invchead_invcdate <= pCutoffDate)
                                     AND   (checkInvoiceSitePrivs(invchead_id))
                                     AND   (invchead_posted) ) ) ) );

-- Remove the cobill and cobmisc records
  DELETE FROM cobill
  WHERE (cobill_cobmisc_id IN ( SELECT cobmisc_id
                                FROM cobmisc, invchead
                                WHERE ( (invchead_invcnumber=cobmisc_invcnumber::TEXT)
                                  AND   (checkInvoiceSitePrivs(invchead_id))
                                  AND   (cobmisc_invcdate <= pCutoffDate)
                                  AND   (cobmisc_posted)) ) );

  DELETE FROM cobmisc
  WHERE ( (checkInvoiceSitePrivs(getInvcheadId(cobmisc_invcnumber::TEXT)))
    AND   (cobmisc_invcdate <= pCutoffDate)
    AND   (cobmisc_posted) );

-- Remove the invchead and invcitem records
  DELETE FROM invcitem
  WHERE (invcitem_invchead_id IN ( SELECT invchead_id
                                   FROM invchead
                                   WHERE ( (invchead_invcdate <= pCutoffDate)
                                     AND   (checkInvoiceSitePrivs(invchead_id))
                                     AND   (invchead_posted) ) ) );

  DELETE FROM invchead
  WHERE ( (invchead_invcdate <= pCutoffDate)
    AND   (checkInvoiceSitePrivs(invchead_id))
    AND   (invchead_posted) );

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 2061 (class 1255 OID 146566898)
-- Dependencies: 4536 8
-- Name: purgepostedcountslips(date, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION purgepostedcountslips(date, 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
  pCutoffDate ALIAS FOR $1;
  pWarehousid ALIAS FOR $2;

BEGIN

  IF (pWarehousid = -1) THEN
    DELETE FROM cntslip
    WHERE (cntslip_id IN ( SELECT cntslip_id
                           FROM cntslip, invcnt
                           WHERE ( (cntslip_cnttag_id=invcnt_id)
                            AND (invcnt_posted)
                            AND (cntslip_posted)
                            AND (date(invcnt_postdate) <= pCutoffDate) ) ) );

  ELSE
    DELETE FROM cntslip
    WHERE (cntslip_id IN ( SELECT cntslip_id
                           FROM invcnt, itemsite
                           WHERE ( (cntslip_cnttag_id=invcnt_id)
                            AND (invcnt_posted)
                            AND (cntslip_posted)
                            AND (invcnt_itemsite_id=itemsite_id)
                            AND (date(invcnt_postdate) <= pCutoffDate)
                            AND (itemsite_warehous_id=pWarehousid) ) ) );
  END IF;

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 2062 (class 1255 OID 146566899)
-- Dependencies: 4536 8
-- Name: purgepostedcounttags(date, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION purgepostedcounttags(date, 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
  pCutoffDate ALIAS FOR $1;
  pWarehousid ALIAS FOR $2;

BEGIN

  IF (pWarehousid = -1) THEN
    DELETE FROM cntslip
    WHERE (cntslip_cnttag_id IN ( SELECT invcnt_id
                                  FROM invcnt
                                  WHERE ( (invcnt_posted)
                                   AND (date(invcnt_postdate) <= pCutoffDate) ) ) );

    DELETE FROM invcnt
    WHERE ((invcnt_posted)
     AND (date(invcnt_postdate) <= pCutoffDate));

  ELSE
    DELETE FROM cntslip
    WHERE (cntslip_cnttag_id IN ( SELECT invcnt_id
                                  FROM invcnt, itemsite
                                  WHERE ( (invcnt_posted)
                                   AND (invcnt_itemsite_id=itemsite_id)
                                   AND (date(invcnt_postdate) <= pCutoffDate)
                                   AND (itemsite_warehous_id=pWarehousid) ) ) );

    DELETE FROM invcnt
    WHERE (invcnt_id IN ( SELECT invcnt_id 
                          FROM invcnt, itemsite
                          WHERE ( (invcnt_posted)
                           AND (invcnt_itemsite_id=itemsite_id)
                           AND (date(invcnt_postdate) <= pCutoffDate)
                           AND (itemsite_warehous_id=pWarehousid) ) ) );
  END IF;

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 2064 (class 1255 OID 146566900)
-- Dependencies: 4536 8
-- Name: purgeshipments(date); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  -- Used for transfer orders shipments (which are never invoiced)
  FOR _r IN SELECT shiphead_id
	      FROM shiphead
	     WHERE ( (shiphead_order_type='TO')
               AND   (shiphead_shipped)
               AND   (shiphead_shipdate <= pcutoff) ) LOOP
    DELETE FROM shipitem WHERE (shipitem_shiphead_id=_r.shiphead_id);
    DELETE FROM shiphead WHERE (shiphead_id=_r.shiphead_id);
  END LOOP;

  RETURN 0;

END;
$_$;


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

--
-- TOC entry 2065 (class 1255 OID 146566901)
-- Dependencies: 4536 8
-- Name: qtyallocated(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  RETURN qtyAllocated(pItemsiteid, startOfTime(), (CURRENT_DATE + pLookAheadDays));

END;
$_$;


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

--
-- TOC entry 2066 (class 1255 OID 146566902)
-- Dependencies: 4536 8
-- Name: qtyallocated(integer, date); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  RETURN qtyAllocated(pItemsiteid, startOfTime(), pDate);

END;
$_$;


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

--
-- TOC entry 2067 (class 1255 OID 146566903)
-- Dependencies: 4536 8
-- Name: qtyallocated(integer, date, date); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
 
  IF ( fetchMetricBool('MultiWhs')) THEN
    IF ( SELECT item_sold
         FROM itemsite, item
         WHERE ((itemsite_item_id=item_id)
         AND (itemsite_id=pItemsiteid)) ) THEN
      RETURN (allocatedForTo(pItemsiteid, pStartDate, pEndDate) +
	      allocatedForWo(pItemsiteid, pStartDate, pEndDate) +
	      allocatedForSo(pItemsiteid, pStartDate, pEndDate));
    ELSE
      RETURN (allocatedForTo(pItemsiteid, pStartDate, pEndDate) +
	      allocatedForWo(pItemsiteid, pStartDate, pEndDate));
    END IF;
  ELSE
    IF ( SELECT item_sold
         FROM itemsite, item
         WHERE ((itemsite_item_id=item_id)
         AND (itemsite_id=pItemsiteid)) ) THEN
      RETURN (allocatedForWo(pItemsiteid, pStartDate, pEndDate) +
	      allocatedForSo(pItemsiteid, pStartDate, pEndDate));
    ELSE
      RETURN (allocatedForWo(pItemsiteid, pStartDate, pEndDate));
    END IF;
  END IF;
END;
$_$;


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

--
-- TOC entry 2068 (class 1255 OID 146566904)
-- Dependencies: 4536 8
-- Name: qtyatshipping(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 2069 (class 1255 OID 146566905)
-- Dependencies: 4536 8
-- Name: qtyatshipping(text, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 2070 (class 1255 OID 146566906)
-- Dependencies: 4536 8
-- Name: qtyatshipping(text, integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

-- pstatus U=unshipped
--         S=shipped
--         B=both unshipped and shipped

  IF (pordertype NOT IN ('SO', 'TO')) THEN
    RAISE EXCEPTION '% is not a valid order type', pordertype;
  END IF;

  IF (pstatus NOT IN ('U', 'S', 'B')) THEN
    RAISE EXCEPTION '% is not a valid status', pstatus;
  END IF;

  SELECT COALESCE(SUM(shipitem_qty), 0.0) INTO _qty
  FROM shipitem, shiphead
  WHERE ((shipitem_shiphead_id=shiphead_id)
    AND  (shiphead_order_type=pordertype)
    AND  (shipitem_orderitem_id=plineitemid)
    AND  (((shiphead_shipped) AND (pstatus IN ('S', 'B'))) OR ((NOT shiphead_shipped) AND (pstatus IN ('U', 'B'))))  );

  RETURN _qty;

END;
$_$;


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

--
-- TOC entry 2071 (class 1255 OID 146566907)
-- Dependencies: 4536 8
-- Name: qtyavailable(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  RETURN ( ( SELECT itemsite_qtyonhand
             FROM itemsite
             WHERE (itemsite_id=pItemsiteid) ) +
           (SELECT qtyOrdered(pItemsiteid, pLookAheadDays)) -
           (SELECT qtyAllocated(pitemsiteid, pLookAheadDays)) );
END;
$_$;


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

--
-- TOC entry 2072 (class 1255 OID 146566908)
-- Dependencies: 4536 8
-- Name: qtyavailable(integer, date); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  RETURN ( ( SELECT itemsite_qtyonhand
             FROM itemsite
             WHERE (itemsite_id=pItemsiteid) ) +
           (SELECT qtyOrdered(pItemsiteid, (pDate - CURRENT_DATE))) -
           (SELECT qtyAllocated(pItemsiteid, (pDate - CURRENT_DATE))) );
END;
$_$;


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

--
-- TOC entry 2073 (class 1255 OID 146566909)
-- Dependencies: 4536 8
-- Name: qtyinshipment(text, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  IF (pordertype NOT IN ('SO', 'TO')) THEN
    RAISE EXCEPTION '% is not a valid order type', pordertype;
  END IF;

  IF (pshipheadid IS NULL) THEN
    RAISE EXCEPTION 'Cannot calculate quantity in a shipment with a NULL shipment';
  END IF;

  SELECT SUM(COALESCE(shipitem_qty, 0.0)) INTO _qty
  FROM shipitem, shiphead
  WHERE ((shipitem_shiphead_id=shiphead_id)
      AND (shiphead_order_type=pordertype)
      AND (shipitem_orderitem_id=plineitemid)
      AND (shiphead_id=pshipheadid));

  IF (NOT FOUND) THEN
    RAISE NOTICE 'Quantity of % item % is 0 because shipment % does not exist.',
                  pordertype, plineitemid, pshipheadid;
  END IF;

  RETURN _qty;

END;
$_$;


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

--
-- TOC entry 2076 (class 1255 OID 146566910)
-- Dependencies: 4536 8
-- Name: qtylocation(integer, integer, date, date, integer, text, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION qtylocation(integer, integer, date, date, integer, text, integer, integer) RETURNS numeric
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pLocationId  ALIAS FOR $1;
  pLsId        ALIAS FOR $2;
  pExpiration  ALIAS FOR $3;
  pWarranty    ALIAS FOR $4;
  pItemsiteId  ALIAS FOR $5;
  pOrderType   ALIAS FOR $6;
  pOrderId     ALIAS FOR $7;
  pItemlocdistId ALIAS FOR $8;
  _qty         NUMERIC = 0.0;
  _qtyDist     NUMERIC = 0.0;
  _qtyReserved NUMERIC = 0.0;

BEGIN
-- Summarize itemloc qty for this location/itemsite
  SELECT COALESCE(SUM(itemloc_qty), 0) INTO _qty
    FROM itemloc
   WHERE ( (itemloc_itemsite_id=pItemsiteId)
     AND (itemloc_location_id=pLocationId)
     AND (COALESCE(itemloc_ls_id, -1)=COALESCE(pLsId, itemloc_ls_id, -1))
     AND (COALESCE(itemloc_expiration, endoftime())=COALESCE(pExpiration, itemloc_expiration, endoftime()))
     AND (COALESCE(itemloc_warrpurc, endoftime())=COALESCE(pWarranty, itemloc_warrpurc, endoftime())) );

-- Summarize qty distributed but not yet committed by previous distributions
  SELECT COALESCE(SUM(loc.itemlocdist_qty), 0) INTO _qtyDist
    FROM itemlocdist loc
      JOIN itemlocdist ls ON ((ls.itemlocdist_source_type='O')
			  AND (ls.itemlocdist_id=loc.itemlocdist_itemlocdist_id))
   WHERE ( (ls.itemlocdist_itemsite_id=pItemsiteId)
     AND (loc.itemlocdist_source_type='L')
     AND (loc.itemlocdist_source_id=pLocationId)
     AND (COALESCE(ls.itemlocdist_ls_id, -1)=COALESCE(pLsId, ls.itemlocdist_ls_id, -1))
     AND (COALESCE(ls.itemlocdist_expiration, endoftime())=COALESCE(pExpiration, ls.itemlocdist_expiration, endoftime()))
     AND (COALESCE(ls.itemlocdist_warranty, endoftime())=COALESCE(pWarranty, ls.itemlocdist_warranty, endoftime()))
     AND (ls.itemlocdist_id != pItemlocdistId ) );

-- Summarize reserved qty for this location/itemsite
-- that is reserved for a different order
  IF (fetchMetricBool('EnableSOReservationsByLocation')) THEN
    SELECT COALESCE(SUM(reserve_qty), 0) INTO _qtyReserved
      FROM itemloc JOIN reserve ON ( (reserve_supply_id=itemloc_id AND reserve_supply_type='I')
                                    AND  ((reserve_demand_type <> COALESCE(pOrderType, '')) OR
                                          (reserve_demand_id <> COALESCE(pOrderId, -1))) )
     WHERE ( (itemloc_itemsite_id=pItemsiteId)
       AND (itemloc_location_id=pLocationId)
       AND (COALESCE(itemloc_ls_id, -1)=COALESCE(pLsId, itemloc_ls_id, -1))
       AND (COALESCE(itemloc_expiration, endoftime())=COALESCE(pExpiration, itemloc_expiration, endoftime()))
       AND (COALESCE(itemloc_warrpurc, endoftime())=COALESCE(pWarranty, itemloc_warrpurc, endoftime())) );
  END IF;

  RETURN (_qty + _qtyDist - _qtyReserved);

END;
$_$;


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

--
-- TOC entry 2077 (class 1255 OID 146566911)
-- Dependencies: 4536 8
-- Name: qtyordered(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  RETURN qtyOrdered(pItemsiteid, startOfTime(), (CURRENT_DATE + pLookahead));

END;
$_$;


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

--
-- TOC entry 2078 (class 1255 OID 146566912)
-- Dependencies: 4536 8
-- Name: qtyordered(integer, date); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  RETURN qtyOrdered(pItemsiteid, startOfTime(), pDate);

END;
$_$;


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

--
-- TOC entry 2079 (class 1255 OID 146566913)
-- Dependencies: 4536 8
-- Name: qtyordered(integer, date, date); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT item_type INTO _itemType
  FROM item, itemsite
  WHERE ( (itemsite_item_id=item_id)
   AND (itemsite_id=pItemsiteid) );

  IF ( SELECT metric_value
	FROM metric
	WHERE ((metric_name = 'MultiWhs')
	AND (metric_value = 't'))) THEN
    RETURN orderedByPo(pItemsiteid, pStartDate, pEndDate) +
           orderedByWo(pItemsiteid, pStartDate, pEndDate) +
           orderedByTo(pItemsiteid, pStartDate, pEndDate);
  ELSE
    RETURN orderedByPo(pItemsiteid, pStartDate, pEndDate) +
           orderedByWo(pItemsiteid, pStartDate, pEndDate);

  END IF;

END;
$_$;


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

--
-- TOC entry 2080 (class 1255 OID 146566914)
-- Dependencies: 4536 8
-- Name: qtypr(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  RETURN qtypr(pItemsiteid, startOfTime(), (CURRENT_DATE + pLookahead));
END;
$_$;


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

--
-- TOC entry 2081 (class 1255 OID 146566915)
-- Dependencies: 4536 8
-- Name: qtypr(integer, date); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  RETURN qtypr(pItemsiteid, startOfTime(), pDate);
END;
$_$;


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

--
-- TOC entry 2082 (class 1255 OID 146566916)
-- Dependencies: 4536 8
-- Name: qtypr(integer, date, date); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

SELECT SUM(pr_qtyreq) INTO _prtotal
  FROM pr
  WHERE ((pr_status = 'O')
    AND (pr_itemsite_id=pItemsiteid)
    AND (pr_duedate BETWEEN pStartDate AND pEndDate));

 IF (_prtotal IS NULL) THEN
     RETURN 0.0;
 END IF;

 RETURN _prtotal;

END;
$_$;


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

--
-- TOC entry 2083 (class 1255 OID 146566917)
-- Dependencies: 4536 8
-- Name: qtyreserved(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT COALESCE(SUM(coitem_qtyreserved),0) INTO _qty
    FROM coitem
   WHERE(coitem_itemsite_id=pItemsiteid);

  RETURN _qty;
END;
$_$;


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

--
-- TOC entry 2084 (class 1255 OID 146566918)
-- Dependencies: 4536 8
-- Name: qtytoreceive(text, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

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

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

  SELECT SUM(recv_qty) INTO _qty
  FROM recv
  WHERE ((recv_orderitem_id=porderitemid)
    AND  (NOT recv_posted)
    AND  (recv_order_type=pordertype));

  RETURN COALESCE(_qty, 0.0);

END;
$_$;


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

--
-- TOC entry 2085 (class 1255 OID 146566919)
-- Dependencies: 4536 8
-- Name: recallshipment(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION recallshipment(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 recallShipment($1, CURRENT_TIMESTAMP);
END;
$_$;


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

--
-- TOC entry 2086 (class 1255 OID 146566920)
-- Dependencies: 4536 8
-- Name: recallshipment(integer, timestamp with time zone); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION recallshipment(integer, timestamp with time zone) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (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;
  _timestamp		TIMESTAMP WITH TIME ZONE := $2;
  _allInvoiced		BOOLEAN;
  _invoicePosted	BOOLEAN;
  _in                   RECORD;
  _co			RECORD;
  _cobill		RECORD;
  _h			RECORD;
  _result               INTEGER;
  _invhistid		INTEGER;
  _itemlocSeries	INTEGER;
  _qty			NUMERIC;
  _qtyToBill		NUMERIC;
  _shiphead		RECORD;
  _to			RECORD;
  _ti			RECORD;
  _value                NUMERIC;

BEGIN

  IF (_timestamp IS NULL) THEN
    _timestamp := CURRENT_TIMESTAMP;
  END IF;

  SELECT * INTO _shiphead
  FROM shiphead
  WHERE (shiphead_id=pshipheadid);
  IF (NOT FOUND OR NOT _shiphead.shiphead_shipped) THEN
    RETURN -1;
  END IF;

  IF (_shiphead.shiphead_order_type = 'SO') THEN
    SELECT cohead_number AS head_number, cohead_cust_id AS cust_id, cohead_prj_id AS prj_id,
           cohead_saletype_id AS saletype_id, cohead_shipzone_id AS shipzone_id INTO _h
      FROM cohead
     WHERE (cohead_id=_shiphead.shiphead_order_id);
    IF (NOT FOUND) THEN
      RETURN -1;
    END IF;

    SELECT COALESCE(BOOL_AND(shipitem_invoiced), FALSE) INTO _allInvoiced
     FROM cobill, shipitem
    WHERE ((cobill_coitem_id=shipitem_orderitem_id)
      AND  (shipitem_shiphead_id=pshipheadid));

    IF (_allInvoiced AND NOT checkPrivilege('RecallInvoicedShipment')) THEN
      RETURN -2;
    END IF;

    -- Check for any associated posted Invoices
    SELECT COALESCE(BOOL_AND(invchead_posted), FALSE) INTO _invoicePosted
    FROM shipitem JOIN invcitem ON (invcitem_id=shipitem_invcitem_id)
                  JOIN invchead ON (invchead_id=invcitem_invchead_id)
    WHERE (shipitem_shiphead_id=pshipheadid);

    IF (_invoicePosted) THEN
      RETURN -4;
    END IF;

    -- Delete any associated unposted Invoices
    FOR _in IN SELECT DISTINCT invchead_id
                 FROM shipitem JOIN invcitem ON (invcitem_id=shipitem_invcitem_id)
                               JOIN invchead ON ( (invchead_id=invcitem_invchead_id) AND
                                                  (NOT invchead_posted) )
                WHERE (shipitem_shiphead_id=pshipheadid) LOOP
      SELECT deleteInvoice(_in.invchead_id) INTO _result;
      IF (_result < 0) THEN
        RETURN _result;
      END IF;
    END LOOP;

    FOR _co IN SELECT coitem_id, coitem_itemsite_id, coitem_qty_invuomratio, coitem_warranty, coitem_cos_accnt_id,
                   itemsite_controlmethod
                 FROM coitem
                  JOIN itemsite ON (coitem_itemsite_id=itemsite_id)
                WHERE(coitem_id IN (SELECT shipitem_orderitem_id
                                      FROM shipitem, shiphead
                                     WHERE((shipitem_shiphead_id=shiphead_id)
                                       AND (shiphead_shipped)
                                       AND (shiphead_id=pshipheadid)))) FOR UPDATE LOOP

      SELECT SUM(shipitem_qty),SUM(COALESCE(shipitem_value, 0)) INTO _qty, _value
      FROM shipitem
      WHERE ( (shipitem_orderitem_id=_co.coitem_id)
       AND (shipitem_shiphead_id=pshipheadid) );

      UPDATE coitem
      SET coitem_qtyshipped = (coitem_qtyshipped - _qty)
      WHERE (coitem_id=_co.coitem_id);

      _qtyToBill := _qty;
      FOR _cobill IN SELECT cobill_id, cobill_qty
			 FROM cobill, shipitem
			WHERE ((cobill_coitem_id=shipitem_orderitem_id)
			  AND  (shipitem_shiphead_id=pshipheadid)
			  AND  (cobill_coitem_id=_co.coitem_id)) FOR UPDATE LOOP

        IF (noNeg(_cobill.cobill_qty - _qtyToBill) = 0) THEN
          DELETE FROM cobill WHERE (cobill_id=_cobill.cobill_id);
        ELSE
	  UPDATE cobill
	  SET cobill_qty = noNeg(cobill_qty - _qtyToBill)
	  WHERE (cobill_id=_cobill.cobill_id);
	END IF;

	_qtyToBill = _qtyToBill - _cobill.cobill_qty;
	EXIT WHEN (_qtyToBill <= 0.0);
      END LOOP;

  --  Check to see if all of the cobills have been deleted for this cobmisc
      IF (EXISTS(SELECT cobmisc_id
                 FROM cobmisc JOIN cobill ON (cobill_cobmisc_id=cobmisc_id)
                 WHERE (cobmisc_cohead_id=_shiphead.shiphead_order_id AND NOT cobmisc_posted))) THEN
  --  Lines exist, update the freight
        UPDATE cobmisc SET cobmisc_freight = (cobmisc_freight - _shiphead.shiphead_freight)
        WHERE (cobmisc_cohead_id=_shiphead.shiphead_order_id AND NOT cobmisc_posted);
      ELSE
  --  No lines exist, delete the cobmisc
        DELETE FROM cobmisc
        WHERE (cobmisc_cohead_id=_shiphead.shiphead_order_id AND NOT cobmisc_posted);
      END IF;

  --  Distribute to G/L, debit Shipping Asset, credit COS
      IF (_co.itemsite_controlmethod != 'N') THEN
        PERFORM insertGLTransaction( 'S/R', _shiphead.shiphead_order_type,
	  			   _h.head_number::TEXT, 'Recall Shipment',
                                   CASE WHEN(COALESCE(_co.coitem_cos_accnt_id, -1) != -1)
                                          THEN getPrjAccntId(_h.prj_id, _co.coitem_cos_accnt_id)
                                        WHEN(_co.coitem_warranty = TRUE)
                                          THEN getPrjAccntId(_h.prj_id, resolveCOWAccount(itemsite_id, _h.cust_id, _h.saletype_id, _h.shipzone_id))
				        ELSE getPrjAccntId(_h.prj_id, resolveCOSAccount(itemsite_id, _h.cust_id, _h.saletype_id, _h.shipzone_id))
                                   END,
                                   getPrjAccntId(_h.prj_id,costcat_shipasset_accnt_id), -1,
				   _value,
				   _timestamp::DATE )
        FROM itemsite, costcat
        WHERE ( (itemsite_costcat_id=costcat_id)
         AND (itemsite_id=_co.coitem_itemsite_id) );
       END IF;

    END LOOP;

-- Kit billing selection
-- Set kit billing qty to zero since kits are shipped complete
    FOR _cobill IN SELECT cobill_id, cobill_qty
                   FROM shipitem JOIN coitem sub ON (sub.coitem_id=shipitem_orderitem_id)
                                 JOIN coitem kit ON (kit.coitem_id <> sub.coitem_id AND
                                                     kit.coitem_cohead_id = sub.coitem_cohead_id AND
                                                     kit.coitem_linenumber = sub.coitem_linenumber AND
                                                     kit.coitem_subnumber = 0)
                                 JOIN cobill ON (cobill_coitem_id=kit.coitem_id)
                   WHERE (shipitem_shiphead_id=pshipheadid)
                     AND (sub.coitem_subnumber > 0)
                   GROUP BY cobill_id, cobill_qty
    LOOP
      UPDATE cobill SET cobill_qty = 0.0
      WHERE (cobill_id=_cobill.cobill_id);
    END LOOP;

  ELSEIF (_shiphead.shiphead_order_type = 'TO') THEN
    SELECT * INTO _to
      FROM tohead
     WHERE (tohead_id=_shiphead.shiphead_order_id);
    IF (NOT FOUND) THEN
      RETURN -1;
    END IF;
    IF (_to.tohead_status = 'C') THEN
      RETURN -6;
    END IF;

    FOR _ti IN SELECT toitem_id,
                      sis.itemsite_id AS src_itemsite_id,
                      tis.itemsite_id AS trns_itemsite_id,
                      scc.costcat_shipasset_accnt_id AS src_shipasset_accnt_id,
                      tcc.costcat_asset_accnt_id AS trns_asset_accnt_id,
                      itemcost(tis.itemsite_id) AS trns_cost,
                      SUM(shipitem_qty) AS recall_qty
               FROM shipitem JOIN toitem ON (toitem_id=shipitem_orderitem_id)
                             JOIN itemsite sis ON (sis.itemsite_item_id=toitem_item_id AND sis.itemsite_warehous_id=_to.tohead_src_warehous_id)
                             JOIN itemsite tis ON (tis.itemsite_item_id=toitem_item_id AND tis.itemsite_warehous_id=_to.tohead_trns_warehous_id)
                             JOIN costcat scc ON (scc.costcat_id=sis.itemsite_costcat_id)
                             JOIN costcat tcc ON (tcc.costcat_id=tis.itemsite_costcat_id)
               WHERE (shipitem_shiphead_id=pshipheadid)
               GROUP BY toitem_id, sis.itemsite_id, tis.itemsite_id,
                        scc.costcat_shipasset_accnt_id, tcc.costcat_asset_accnt_id
    LOOP

      _itemlocSeries := NEXTVAL('itemloc_series_seq');
      
      SELECT postInvTrans(_ti.src_itemsite_id, 'TS', (_ti.recall_qty * -1.0), 'I/M',
			  _shiphead.shiphead_order_type, formatToNumber(_ti.toitem_id),
			  _to.tohead_number,
			  'Recall TO Shipment To Src Warehouse',
			  _ti.trns_asset_accnt_id,
			  _ti.src_shipasset_accnt_id,
			  _itemlocSeries, _timestamp,
                          (_ti.trns_cost * _ti.recall_qty * -1.0)) INTO _invhistid;

      IF (_invhistid < 0) THEN
	RETURN _invhistid;
      END IF;

      -- post the inventory history if lot/serial or location control
      PERFORM postItemlocseries(_itemlocSeries);

      -- record inventory history and qoh changes at transit warehouse but
      -- there is only one g/l account to touch
      SELECT postInvTrans(_ti.trns_itemsite_id, 'TR', (_ti.recall_qty * -1.0), 'I/M',
			  _shiphead.shiphead_order_type, formatToNumber(_ti.toitem_id),
			  _to.tohead_number,
			  'Recall TO Shipment From Transit Warehouse',
			  _ti.trns_asset_accnt_id,
			  _ti.trns_asset_accnt_id,
			  _itemlocSeries, _timestamp,
                          (_ti.trns_cost * _ti.recall_qty * -1.0)) INTO _invhistid;

      IF (_invhistid < 0) THEN
	RETURN _invhistid;
      END IF;

      -- post the inventory history if lot/serial or location control
      PERFORM postItemlocseries(_itemlocSeries);

      UPDATE toitem
      SET toitem_qty_shipped = (toitem_qty_shipped - _ti.recall_qty)
      WHERE (toitem_id=_ti.toitem_id);

      UPDATE shipitem SET shipitem_shipdate=NULL,
                          shipitem_shipped=FALSE,
                          shipitem_value=(shipitem_qty * _ti.trns_cost)
      WHERE ((shipitem_orderitem_id=_ti.toitem_id)
        AND  (shipitem_shiphead_id=pshipheadid));

      DELETE FROM recv
	WHERE ((recv_orderitem_id=_ti.toitem_id)
	  AND  (recv_order_type='TO')
	  AND  (NOT recv_posted));

    END LOOP;

  END IF;

  UPDATE shiphead
  SET shiphead_shipped=FALSE
  WHERE (shiphead_id=pshipheadid);

  RETURN _itemlocSeries;

END;
$_$;


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

--
-- TOC entry 2087 (class 1255 OID 146566922)
-- Dependencies: 4536 8
-- Name: recallwo(integer, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

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

  IF (recallChildren) THEN
    returnCode := (SELECT MAX(recallWo(wo_id, TRUE))
                   FROM wo
                   WHERE ((wo_ordtype='W')
                    AND (wo_ordid=pWoid)));
  END IF;

  RETURN 0;
END;
$_$;


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

--
-- TOC entry 2088 (class 1255 OID 146566923)
-- Dependencies: 4536 8
-- Name: receipts(text); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  IF (pTransType IN ('RM', 'RB', 'RT', 'RP', 'RR', 'RX', 'TR')) THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;

END;
$_$;


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

--
-- TOC entry 2089 (class 1255 OID 146566924)
-- Dependencies: 8
-- Name: releaseapmemonumber(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION releaseapmemonumber(integer) RETURNS boolean
    LANGUAGE sql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT releaseNumber('APMemoNumber', $1::INTEGER) > 0;
$_$;


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

--
-- TOC entry 1980 (class 1255 OID 146566925)
-- Dependencies: 8
-- Name: releasearmemonumber(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION releasearmemonumber(integer) RETURNS boolean
    LANGUAGE sql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  select releaseNumber('ARMemoNumber', $1::INTEGER) > 0;
$_$;


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

--
-- TOC entry 2044 (class 1255 OID 146566926)
-- Dependencies: 8
-- Name: releasecashrcptnumber(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION releasecashrcptnumber(integer) RETURNS boolean
    LANGUAGE sql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT releaseNumber('CashRcptNumber', $1) > 0;
$_$;


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

--
-- TOC entry 2054 (class 1255 OID 146566927)
-- Dependencies: 8
-- Name: releasecmnumber(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION releasecmnumber(integer) RETURNS boolean
    LANGUAGE sql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT releaseNumber('CmNumber', $1) > 0;
$_$;


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

--
-- TOC entry 2055 (class 1255 OID 146566928)
-- Dependencies: 8
-- Name: releasecmnumber(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION releasecmnumber(text) RETURNS boolean
    LANGUAGE sql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT releaseNumber('CmNumber', $1::INTEGER) > 0;
$_$;


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

--
-- TOC entry 2063 (class 1255 OID 146566929)
-- Dependencies: 8
-- Name: releasecrmaccountnumber(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION releasecrmaccountnumber(integer) RETURNS boolean
    LANGUAGE sql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT releaseNumber('CRMAccountNumber', $1::INTEGER) > 0;
$_$;


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

--
-- TOC entry 2074 (class 1255 OID 146566930)
-- Dependencies: 8
-- Name: releaseincidentnumber(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION releaseincidentnumber(integer) RETURNS boolean
    LANGUAGE sql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT releaseNumber('IncidentNumber', $1) = 1;
$_$;


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

--
-- TOC entry 2075 (class 1255 OID 146566931)
-- Dependencies: 8
-- Name: releaseinvcnumber(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION releaseinvcnumber(integer) RETURNS boolean
    LANGUAGE sql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT releaseNumber('InvcNumber', $1) > 0;
$_$;


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

--
-- TOC entry 905 (class 1255 OID 146566932)
-- Dependencies: 8
-- Name: releaseinvcnumber(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION releaseinvcnumber(text) RETURNS boolean
    LANGUAGE sql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT releaseNumber('InvcNumber', $1::INTEGER) > 0;
$_$;


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

--
-- TOC entry 2090 (class 1255 OID 146566933)
-- Dependencies: 4536 8
-- Name: releasenumber(text, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION releasenumber(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
  psequence	ALIAS FOR $1;
  pnumber 	ALIAS FOR $2;
BEGIN
  IF (fetchMetricBool('EnableGaplessNumbering')) THEN
    -- drop the number back into the pool if it was not committed
    PERFORM clearNumberIssue(psequence, pnumber);
  END IF;
  
  UPDATE orderseq SET
    orderseq_number = LEAST(pnumber, orderseq_number)
  WHERE (orderseq_name=psequence);

  RETURN 1;
END;
$_$;


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

--
-- TOC entry 2091 (class 1255 OID 146566934)
-- Dependencies: 8
-- Name: releaseponumber(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION releaseponumber(integer) RETURNS boolean
    LANGUAGE sql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT releaseNumber('PoNumber', $1) > 0;
$_$;


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

--
-- TOC entry 2092 (class 1255 OID 146566935)
-- Dependencies: 8
-- Name: releaseponumber(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION releaseponumber(text) RETURNS boolean
    LANGUAGE sql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT releaseNumber('PoNumber', $1::INTEGER) > 0;
$_$;


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

--
-- TOC entry 2093 (class 1255 OID 146566936)
-- Dependencies: 8
-- Name: releaseprnumber(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION releaseprnumber(integer) RETURNS boolean
    LANGUAGE sql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT releaseNumber('PrNumber', $1) > 0;
$_$;


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

--
-- TOC entry 2094 (class 1255 OID 146566937)
-- Dependencies: 4536 8
-- Name: releasepurchaseorder(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  IF ( ( SELECT (COUNT(*) = 0)
         FROM poitem
         WHERE ( (poitem_pohead_id=pPoheadid)
           AND   (poitem_status='U') ) ) ) THEN
    RETURN -1;
  END IF;

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

    --update status and store the date that the order was released on
    UPDATE pohead
    SET pohead_status='O', pohead_released = current_date
    WHERE (pohead_id=pPoheadid);

  END IF;

  --update status and store the duedates at release
  UPDATE poitem
  SET poitem_status='O', poitem_rlsd_duedate = poitem_duedate
  WHERE (poitem_pohead_id=pPoheadid);

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 2095 (class 1255 OID 146566938)
-- Dependencies: 8
-- Name: releasequnumber(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION releasequnumber(integer) RETURNS integer
    LANGUAGE sql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT releaseNumber('QuNumber', $1);
$_$;


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

--
-- TOC entry 2096 (class 1255 OID 146566939)
-- Dependencies: 8
-- Name: releasequnumber(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION releasequnumber(text) RETURNS integer
    LANGUAGE sql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT releaseNumber('QuNumber', $1::INTEGER);
$_$;


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

--
-- TOC entry 2097 (class 1255 OID 146566940)
-- Dependencies: 4536 8
-- Name: releaseshipmentnumber(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION releaseshipmentnumber(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
  pNumber ALIAS FOR $1;
  _test INTEGER;

BEGIN

--  Check to see if a Shipment exists with the passed number
  SELECT shiphead_id INTO _test
  FROM shiphead
  WHERE (shiphead_number=pNumber);

  IF (FOUND) THEN
    RETURN FALSE;
  END IF;

--  Check to see if ShipmentNumber orderseq has been incremented past the passed number
  SELECT orderseq_number INTO _test
  FROM orderseq
  WHERE (orderseq_name='ShipmentNumber');

  IF ((_test - 1) <> pNumber) THEN
    RETURN FALSE;
  END IF;

--  Decrement the orderseq, releasing the passed number
  UPDATE orderseq
  SET orderseq_number = (orderseq_number - 1)
  WHERE (orderseq_name='ShipmentNumber');

  RETURN TRUE;

END;
$_$;


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

--
-- TOC entry 2098 (class 1255 OID 146566941)
-- Dependencies: 4536 8
-- Name: releasesohead(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  DELETE FROM soheadlock
   WHERE ( (soheadlock_sohead_id=pSoheadid)
     AND   (soheadlock_username=getEffectiveXtUser())
     AND   (soheadlock_procpid=pg_backend_pid()) );

  RETURN TRUE;
END;
$_$;


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

--
-- TOC entry 2099 (class 1255 OID 146566942)
-- Dependencies: 8
-- Name: releasesonumber(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION releasesonumber(integer) RETURNS integer
    LANGUAGE sql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT releaseNumber('SoNumber', $1);
$_$;


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

--
-- TOC entry 2100 (class 1255 OID 146566943)
-- Dependencies: 4536 8
-- Name: releasesonumber(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION releasesonumber(text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
	IF (COALESCE($1, '') = '' OR $1 ~ '[^0-9]')
	THEN
	--do nothing;
	RETURN 1;
	ELSE
	RETURN releaseNumber('SoNumber', $1::INTEGER);
	
	END IF;
END;
$_$;


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

--
-- TOC entry 2101 (class 1255 OID 146566944)
-- Dependencies: 4536 8
-- Name: releaseunusedbillingheader(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

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

  SELECT cobill_id INTO _p
    FROM cobill
   WHERE (cobill_cobmisc_id=pCobmiscid)
   LIMIT 1;
  IF (FOUND) THEN
    RETURN -2;
  END IF;

  DELETE FROM cobmisc
  WHERE (cobmisc_id=pCobmiscid);

  RETURN 0;
END;
$_$;


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

--
-- TOC entry 2102 (class 1255 OID 146566945)
-- Dependencies: 8
-- Name: releasevonumber(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION releasevonumber(integer) RETURNS boolean
    LANGUAGE sql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT releaseNumber('VcNumber', $1) > 0;
$_$;


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

--
-- TOC entry 2103 (class 1255 OID 146566946)
-- Dependencies: 4536 8
-- Name: releasewo(integer, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION releasewo(integer, boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pWoid ALIAS FOR $1;
  releaseChildren ALIAS FOR $2;
  returnCode INTEGER;
BEGIN
  UPDATE wo
  SET wo_status='R'
  WHERE ((wo_status='E')
   AND (wo_id=pWoid));

  IF (releaseChildren) THEN
    returnCode := (SELECT MAX(releaseWo(wo_id, TRUE))
                   FROM wo
                   WHERE ((wo_ordtype='W')
                    AND (wo_ordid=pWoid)));
  END IF;

  RETURN 0;
END;
$_$;


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

--
-- TOC entry 2104 (class 1255 OID 146566947)
-- Dependencies: 8
-- Name: releasewonumber(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION releasewonumber(integer) RETURNS boolean
    LANGUAGE sql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT releaseNumber('WoNumber', $1) > 0;
$_$;


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

--
-- TOC entry 930 (class 1255 OID 146566948)
-- Dependencies: 4536 8
-- Name: relocateinventory(integer, integer, integer, numeric, text); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 946 (class 1255 OID 146566949)
-- Dependencies: 4536 8
-- Name: relocateinventory(integer, integer, integer, numeric, text, timestamp with time zone); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION relocateinventory(integer, integer, integer, numeric, text, timestamp with time zone) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pSourceItemlocid      ALIAS FOR $1;
  pTargetLocationid     ALIAS FOR $2;
  pItemsiteid           ALIAS FOR $3;
  pQty                  ALIAS FOR $4;
  pComments             ALIAS FOR $5;
  _GlDistTS             TIMESTAMP WITH TIME ZONE := $6;
  _targetItemlocid      INTEGER;
  _invhistid            INTEGER;
  _p                    RECORD;
  _rsrv                 RECORD;
  _qty                  NUMERIC;
  _qtyunreserved        NUMERIC := 0.0;
  _qtytomove            NUMERIC := 0.0;
  _result               INTEGER := -1;
  _itemlocSeries        INTEGER := NEXTVAL('itemloc_series_seq');

BEGIN

    IF ((_GlDistTS IS NULL) OR (CAST(_GlDistTS AS date)=CURRENT_DATE)) THEN
      _GlDistTS := CURRENT_TIMESTAMP;
    END IF;

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

--  Cache some parameters
  SELECT itemloc_ls_id,
         itemloc_itemsite_id AS itemsiteid,
         itemloc_expiration,
         itemloc_warrpurc,
         itemloc_qty,
         sourceloc.location_netable AS sourcenet,
         targetloc.location_netable AS targetnet INTO _p
  FROM itemloc, location AS sourceloc, location AS targetloc
  WHERE ( (itemloc_location_id=sourceloc.location_id)
   AND (targetloc.location_id=pTargetLocationid)
   AND (itemloc_id=pSourceItemlocid) );

--  Check to make sure the qty being transfered exists
  IF (_p.itemloc_qty < pQty) THEN
    RETURN -1;
  END IF;

--  Create the RL transaction
  SELECT NEXTVAL('invhist_invhist_id_seq') INTO _invhistid;
  INSERT INTO invhist
  ( invhist_id, invhist_itemsite_id,
    invhist_transtype, invhist_invqty,
    invhist_qoh_before, invhist_qoh_after,
    invhist_comments,   invhist_transdate,
    invhist_invuom, invhist_unitcost, invhist_costmethod,
    invhist_value_before, invhist_value_after, invhist_series) 
  SELECT _invhistid, itemsite_id,
         'RL', 0,
         itemsite_qtyonhand, itemsite_qtyonhand,
         pComments, _GlDistTS,
         uom_name,
         CASE WHEN (itemsite_costmethod='A') THEN avgcost(itemsite_id)
              ELSE stdCost(item_id)
         END, itemsite_costmethod,
         itemsite_value, itemsite_value, _itemlocSeries
  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));

--  Relocate the inventory from the source and record the transactions
  INSERT INTO invdetail
  ( invdetail_invhist_id, invdetail_location_id, invdetail_ls_id,
    invdetail_qty, invdetail_qty_before, invdetail_qty_after,
    invdetail_expiration, invdetail_warrpurc )
  SELECT _invhistid, itemloc_location_id, itemloc_ls_id,
         (pQty * -1), itemloc_qty, (itemloc_qty - pQty),
         itemloc_expiration, itemloc_warrpurc
  FROM itemloc
  WHERE (itemloc_id=pSourceItemlocid);

  UPDATE itemloc
  SET itemloc_qty=(itemloc_qty - pQty)
  FROM itemsite
  WHERE ( (itemloc_itemsite_id=itemsite_id)
   AND (NOT itemsite_freeze)
   AND (itemloc_id=pSourceItemlocid) );

--  Check to see if any of the current Lot/Serial #/Expiration exists at the target location
  SELECT itemloc_id INTO _targetItemlocid
  FROM itemloc 
  WHERE ( (COALESCE(itemloc_ls_id, -1)=COALESCE(_p.itemloc_ls_id,-1))
   AND (COALESCE(itemloc_expiration,endOfTime())=COALESCE(_p.itemloc_expiration,endOfTime()))
   AND (COALESCE(itemloc_warrpurc,endOfTime())=COALESCE(_p.itemloc_warrpurc,endOfTime()))
   AND (itemloc_itemsite_id=pItemsiteid)
   AND (itemloc_location_id=pTargetLocationid) );

  IF (NOT FOUND) THEN
    SELECT NEXTVAL('itemloc_itemloc_id_seq') INTO _targetItemlocid;
    INSERT INTO itemloc
    ( itemloc_id, itemloc_itemsite_id, itemloc_location_id,
      itemloc_ls_id, itemloc_expiration, itemloc_warrpurc, itemloc_qty )
    VALUES
    ( _targetItemlocid, pItemsiteid, pTargetLocationid,
      _p.itemloc_ls_id, _p.itemloc_expiration, _p.itemloc_warrpurc, 0 );
  END IF;

--  Relocate the inventory to the resultant target and record the transactions
  INSERT INTO invdetail
  ( invdetail_invhist_id, invdetail_location_id, invdetail_ls_id,
    invdetail_qty, invdetail_qty_before, invdetail_qty_after,
    invdetail_expiration, invdetail_warrpurc )
  SELECT _invhistid, pTargetLocationid, _p.itemloc_ls_id,
         pQty, itemloc_qty, (itemloc_qty + pQty), 
         _p.itemloc_expiration, _p.itemloc_warrpurc
  FROM itemloc
  WHERE (itemloc_id=_targetItemlocid);

  UPDATE itemloc
  SET itemloc_qty=(itemloc_qty + pQty)
  FROM itemsite
  WHERE ( (itemloc_itemsite_id=itemsite_id)
   AND (NOT itemsite_freeze)
   AND (itemloc_id=_targetItemlocid) );

  UPDATE invhist
  SET invhist_hasdetail=TRUE
  WHERE (invhist_id=_invhistid);

--  Post in incomming or outgoing NN transaction if required
  IF (_p.sourcenet <> _p.targetnet) THEN
    IF (_p.targetnet) THEN
      _qty = (pQty * -1);
    ELSE
      _qty = pQty;
    END IF;

    INSERT INTO invhist
    ( invhist_itemsite_id,
      invhist_transtype, invhist_invqty,
      invhist_qoh_before, invhist_qoh_after,
      invhist_docnumber, invhist_comments, invhist_transdate,
      invhist_invuom, invhist_unitcost, invhist_costmethod,
      invhist_value_before, invhist_value_after, invhist_series) 
    SELECT itemsite_id,
           'NN', (_qty * -1),
           itemsite_qtyonhand, (itemsite_qtyonhand - _qty),
           '', '', _GlDistTS,
           uom_name,
           CASE WHEN (itemsite_costmethod='A') THEN avgcost(itemsite_id)
                ELSE stdCost(item_id)
           END, itemsite_costmethod,
           itemsite_value, itemsite_value, _itemlocSeries
    FROM item, itemsite, uom
    WHERE ( (itemsite_item_id=item_id)
     ANd (item_inv_uom_id=uom_id)
     AND (itemsite_controlmethod <> 'N')
     AND (itemsite_id=_p.itemsiteid) );

    UPDATE itemsite
    SET itemsite_qtyonhand = (itemsite_qtyonhand - _qty),
        itemsite_nnqoh = (itemsite_nnqoh + _qty)
    WHERE (itemsite_id=_p.itemsiteid);
  END IF;

--  Check to see if there is anything left at the target Itemloc and delete if not
--  Could be zero if relocate increased a negative quantity to zero
  DELETE FROM itemloc
  WHERE ( (itemloc_qty=0)
   AND (itemloc_id=_targetItemlocid) );

--  Handle Reservations
  IF (fetchMetricBool('EnableSOReservationsByLocation')) THEN
    SELECT CASE WHEN (qtyReservedLocation(itemloc_id) > itemloc_qty)
                THEN (qtyReservedLocation(itemloc_id) - itemloc_qty)
                ELSE 0.0
                END INTO _qtyunreserved
    FROM itemloc
    WHERE (itemloc_id=pSourceItemlocid);
    -- Move reservations as necessary
    WHILE (_qtyunreserved > 0.0) LOOP
      SELECT * INTO _rsrv
      FROM reserve
      WHERE ((reserve_supply_type='I')
        AND  (reserve_supply_id=pSourceItemlocid))
      ORDER BY reserve_qty;
      IF (NOT FOUND) THEN
        RAISE EXCEPTION 'Cannot find reservation to unreserve.';
      END IF;
      IF (_rsrv.reserve_qty > _qtyunreserved) THEN
        _qtytomove := _qtyunreserved;
      ELSE
        _qtytomove := _rsrv.reserve_qty;
      END IF;
      -- Unreserve Source Location
      SELECT unreserveSOLineQty(_rsrv.reserve_demand_id,
                                _qtytomove,
                                pSourceItemlocid) INTO _result;
      IF (_result < 0) THEN
        RAISE EXCEPTION 'unreserveSOLineQty failed with result=%, reserve_id=%, qty=%',
                        _result, _rsrv.reserve_id, _qtytomove;
      END IF;
      -- Reserve to new Location
      SELECT reserveSOLineQty(_rsrv.reserve_demand_id,
                              TRUE,
                              _qtytomove,
                              _targetItemlocid) INTO _result;
      IF (_result < 0) THEN
        RAISE EXCEPTION 'reserveSOLineQty failed with result=%, reserve_id=%, qty=%',
                        _result, _rsrv.reserve_id, _qtytomove;
      END IF;
      -- Calculate running total
      _qtyunreserved := _qtyunreserved - _qtytomove;
    END LOOP;
  END IF;

--  Check to see if there is anything left at the source Itemloc and delete if not
  DELETE FROM itemloc
  WHERE ( (itemloc_qty=0)
   AND (itemloc_id=pSourceItemlocid) );

--  Return the invhist_id
  RETURN _invhistid;

END;
$_$;


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

--
-- TOC entry 2105 (class 1255 OID 146566951)
-- Dependencies: 4536 8
-- Name: reopenbankreconciliation(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

-- Check the accnt information to make sure it is valid
  SELECT accnt_id INTO _accntid
    FROM bankrec, bankaccnt, accnt
   WHERE ( (bankaccnt_accnt_id=accnt_id)
     AND   (bankrec_bankaccnt_id=bankaccnt_id)
     AND   (bankrec_id=pBankrecid) );
  IF ( NOT FOUND ) THEN
    RETURN -1;
  END IF;

  SELECT bankrec_id INTO _bankrecid
    FROM bankrec
   WHERE (NOT bankrec_posted);
  IF (FOUND) THEN
  -- Delete any bankrecitem records for unposted periods
    DELETE FROM bankrecitem
     WHERE (bankrecitem_bankrec_id=_bankrecid);
  -- Delete any bankrec records for unposted period
    DELETE FROM bankrec
     WHERE (bankrec_id=_bankrecid);
  END IF;

-- Mark all the gltrans items that have been cleared as unreconciled.
  UPDATE gltrans
     SET gltrans_rec = FALSE
   WHERE ( (gltrans_id IN (SELECT bankrecitem_source_id
                             FROM bankrecitem
                            WHERE ((bankrecitem_source = 'GL')
                              AND  (bankrecitem_cleared)
                              AND  (bankrecitem_bankrec_id=pBankrecid) ) ) )
     AND   (gltrans_accnt_id=_accntid) ) ;

-- Mark all the sltrans items that have been cleared as unreconciled.
  UPDATE sltrans
     SET sltrans_rec = FALSE
   WHERE ( (sltrans_id IN (SELECT bankrecitem_source_id
                             FROM bankrecitem
                            WHERE ((bankrecitem_source = 'SL')
                              AND  (bankrecitem_cleared)
                              AND  (bankrecitem_bankrec_id=pBankrecid) ) ) )
     AND   (sltrans_accnt_id=_accntid) ) ;

-- Mark the bankrec record as unposted
  UPDATE bankrec SET 
    bankrec_posted = FALSE,
    bankrec_postdate = NULL
   WHERE (bankrec_id=pBankrecid);

  RETURN pBankrecid;
END;
$_$;


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

--
-- TOC entry 2106 (class 1255 OID 146566952)
-- Dependencies: 4536 8
-- Name: reorderdate(integer, integer, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION reorderdate(integer, integer, boolean) 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
  pItemsiteid ALIAS FOR $1;
  pLookAhead ALIAS FOR $2;
  pIncludePlanned ALIAS FOR $3;
  _runningAvailability NUMERIC;
  _reorderLevel NUMERIC;
  _availability RECORD;

BEGIN

--  Make sure that we know how to handle the passed part
  IF ( SELECT (NOT (item_type IN ('M', 'P')))
       FROM item, itemsite
       WHERE ( (itemsite_item_id=item_id)
        AND (itemsite_id=pItemsiteid) ) ) THEN
    RETURN NULL;
  END IF;

--  Load the initial QOH
  SELECT itemsite_qtyonhand INTO _runningAvailability
  FROM itemsite
  WHERE (itemsite_id=pItemsiteid);

--  Grab the Reorder Level, if any
  IF ( ( SELECT itemsite_useparams
         FROM itemsite
         WHERE (itemsite_id=pItemsiteid) ) ) THEN
    SELECT itemsite_reorderlevel INTO _reorderLevel
    FROM itemsite
    WHERE (itemsite_id=pItemsiteid);
  ELSE
    _reorderLevel := 0;
  END IF;

--  If we are already below the Reorder Level then we should order ASAP
  IF (_runningAvailability <= _reorderLevel) THEN
    RETURN CURRENT_DATE;
  END IF;

--  Grab all of the availability trigger points
  FOR _availability IN SELECT 1 AS seq,
                              wo_duedate AS orderdate,
                              (noNeg(wo_qtyord - wo_qtyrcv)) AS balance
                       FROM wo
                       WHERE ((wo_status IN ('O', 'E', 'R', 'I'))
                        AND (wo_duedate <= (CURRENT_DATE + pLookAhead))
                        AND (wo_itemsite_id=pItemsiteid))

                      UNION SELECT 2 AS seq,
                                   womatl_duedate AS orderdate,
                                   (noNeg(itemuomtouom(itemsite_item_id, womatl_uom_id, NULL, womatl_qtyreq - womatl_qtyiss)) * -1) AS balance
                      FROM womatl, wo, itemsite
                      WHERE ((wo_status IN ('O', 'E', 'R', 'I'))
                       AND (womatl_wo_id=wo_id)
                       AND (womatl_itemsite_id=itemsite_id)
                       AND (womatl_duedate <= (CURRENT_DATE + pLookahead))
                       AND (womatl_itemsite_id=pItemsiteid))

                      UNION SELECT 1 AS seq,
                                   poitem_duedate AS orderdate,
                                   (noNeg(poitem_qty_ordered - poitem_qty_received) * poitem_invvenduomratio) AS balance
                      FROM pohead, poitem
                      WHERE ((poitem_pohead_id=pohead_id)
                       AND (poitem_status = 'O')
                       AND (poitem_duedate <= (CURRENT_DATE + pLookAhead))
                       AND (poitem_itemsite_id=pItemsiteid))

                      UNION SELECT 2 AS seq,
                                   coitem_scheddate AS orderdate,
                                   (noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) * -1) AS balance
                      FROM coitem, cohead
                      WHERE ((coitem_status = 'O')
                       AND (coitem_cohead_id=cohead_id)
                       AND (coitem_scheddate <= (CURRENT_DATE + pLookAhead))
                       AND (coitem_itemsite_id=pItemsiteid))

                      UNION SELECT 2 AS seq,
                                   planord_startdate AS orderdate,
                                   (planreq_qty * -1) AS balance
                      FROM planreq, planord
                      WHERE ( (pIncludePlanned)
                       AND (planreq_source='P')
                       AND (planreq_source_id=planord_id)
                       AND (planord_startdate <= (CURRENT_DATE + pLookAhead))
                       AND (planord_itemsite_id=pItemsiteid) )

                      UNION SELECT 1 AS seq,
                                   planord_duedate AS orderdate,
                                   planord_qty AS balance
                      FROM planord
                      WHERE ( (pIncludePlanned)
                       AND (planord_duedate <= (CURRENT_DATE + pLookAhead))
                       AND (planord_itemsite_id=pItemsiteid) )

                      ORDER BY orderdate, seq LOOP

--  Calculate the new projected availability
    _runningAvailability := (_runningAvailability + _availability.balance);

--  Check to see if the project availability drop below the reorder level
    IF (_runningAvailability < _reorderLevel) THEN
      RETURN _availability.orderdate;
    END IF;

  END LOOP;

--  The reorder level was not reached within the look ahead period
  RETURN NULL;

END;
$_$;


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

--
-- TOC entry 2107 (class 1255 OID 146566953)
-- Dependencies: 4536 8
-- Name: replaceallvoidedapchecks(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 2108 (class 1255 OID 146566954)
-- Dependencies: 4536 8
-- Name: replaceallvoidedchecks(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION replaceallvoidedchecks(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
  pBankaccntid	ALIAS FOR $1;
  _returnValue	INTEGER := 0;

BEGIN

  SELECT MIN(replaceVoidedCheck(checkhead_id)) INTO _returnValue
    FROM checkhead
    WHERE ( (checkhead_void)
     AND (NOT checkhead_posted)
     AND (NOT checkhead_replaced)
     AND (NOT checkhead_deleted)
     AND (checkhead_bankaccnt_id=pBankaccntid) );

  RETURN _returnValue;

END;
$_$;


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

--
-- TOC entry 2109 (class 1255 OID 146566955)
-- Dependencies: 4536 8
-- Name: replacevoidedapcheck(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 2110 (class 1255 OID 146566956)
-- Dependencies: 4536 8
-- Name: replacevoidedcheck(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  IF ( ( SELECT ( (NOT checkhead_void) OR checkhead_posted OR checkhead_replaced )
         FROM checkhead
         WHERE (checkhead_id=pCheckid) ) ) THEN
    RETURN -1;
  END IF;

  -- has someone created a new check for one of the items while this was void?
  IF EXISTS (SELECT dup.checkitem_id
             FROM checkitem orig, checkitem dup, checkhead AS duphead
             WHERE ((COALESCE(orig.checkitem_aropen_id,-1)=COALESCE(dup.checkitem_aropen_id,-1))
                AND (COALESCE(orig.checkitem_apopen_id,-1)=COALESCE(dup.checkitem_apopen_id,-1))
                AND (orig.checkitem_checkhead_id!=dup.checkitem_checkhead_id)
                AND (dup.checkitem_checkhead_id=duphead.checkhead_id)
                AND (NOT duphead.checkhead_void)
                AND (orig.checkitem_checkhead_id=pCheckid))) THEN
    RETURN -2;
  END IF;

  SELECT NEXTVAL('checkhead_checkhead_id_seq') INTO _newCheckid;

  INSERT INTO checkhead
  ( checkhead_id, checkhead_recip_id, checkhead_recip_type,
    checkhead_bankaccnt_id, checkhead_checkdate,
    checkhead_number, checkhead_amount,
    checkhead_for, checkhead_journalnumber,
    checkhead_notes,
    checkhead_misc, checkhead_expcat_id, checkhead_curr_id )
  SELECT _newCheckid, checkhead_recip_id, checkhead_recip_type,
	 checkhead_bankaccnt_id, checkhead_checkdate,
	 -1, -- fetchNextCheckNumber(checkhead_bankaccnt_id),
         checkhead_amount,
	 checkhead_for, checkhead_journalnumber,
         checkhead_notes || '
Replaces voided check ' || checkhead_number,
	 checkhead_misc, checkhead_expcat_id, checkhead_curr_id
  FROM checkhead
  WHERE (checkhead_id=pCheckid);

  INSERT INTO checkitem
  ( checkitem_checkhead_id, checkitem_amount, checkitem_discount,
    checkitem_ponumber, checkitem_vouchernumber, checkitem_invcnumber,
    checkitem_apopen_id, checkitem_aropen_id,
    checkitem_docdate, checkitem_curr_id, checkitem_curr_rate )
  SELECT _newCheckid, checkitem_amount, checkitem_discount,
         checkitem_ponumber, checkitem_vouchernumber, checkitem_invcnumber,
	 checkitem_apopen_id, checkitem_aropen_id,
	 checkitem_docdate, checkitem_curr_id, checkitem_curr_rate
  FROM checkitem
  WHERE (checkitem_checkhead_id=pCheckid);

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

  RETURN _newCheckid;

END;
$_$;


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

--
-- TOC entry 2111 (class 1255 OID 146566957)
-- Dependencies: 4536 8
-- Name: reprioritizewo(integer, integer, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION reprioritizewo(integer, integer, boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pWoid ALIAS FOR $1;
  pPriority ALIAS FOR $2;
  pChangeChildren ALIAS FOR $3;
  _status CHAR(1);
  _result INTEGER;

BEGIN

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

  IF (NOT (_status IN ('O', 'E','R','I'))) THEN
    return -1;
  END IF;

  UPDATE wo
  SET wo_priority=pPriority
  WHERE (wo_id=pWoid);

  IF ( (_status IN ('E','R','I')) AND (pChangeChildren) ) THEN
    SELECT COALESCE(MIN(reprioritizeWo(wo_id, pPriority, TRUE)), 1) INTO _result
    FROM wo
    WHERE ( (wo_ordtype='W')
     AND (wo_ordid=pWoid) );
  ELSE
    _result = 1;
  END IF;

  RETURN _result;

END;
$_$;


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

--
-- TOC entry 2113 (class 1255 OID 146566959)
-- Dependencies: 4536 8
-- Name: resetdbobjperms(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION resetdbobjperms() RETURNS integer
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _count        INTEGER := 0;
BEGIN
  SELECT SUM(resetDBObjPerms(nspname || '.' || relname)) INTO _count
  FROM pg_catalog.pg_class, pg_catalog.pg_namespace
  WHERE (relkind IN ('r', 'S', 'v')
    AND  (relnamespace=pg_namespace.oid)
    AND  (nspname IN ('public', 'api')));

  RETURN _count;
END;
$$;


ALTER FUNCTION public.resetdbobjperms() OWNER TO admin;

--
-- TOC entry 2112 (class 1255 OID 146566958)
-- Dependencies: 4536 8
-- Name: resetdbobjperms(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION resetdbobjperms(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
  pObjname      ALIAS FOR $1;
BEGIN
  EXECUTE 'ALTER TABLE '   || pObjname || ' OWNER TO ' || getEffectiveXtUser() || ';';
  EXECUTE 'REVOKE ALL ON ' || pObjname || ' FROM PUBLIC;';
  EXECUTE 'GRANT  ALL ON ' || pObjname || ' TO GROUP xtrole;';
  RETURN 1;
END;
$_$;


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

--
-- TOC entry 2115 (class 1255 OID 146566960)
-- Dependencies: 4536 8
-- Name: resetlowlevelcode(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

    pItemId ALIAS FOR $1;
    _result	INTEGER;
    _counterNum	INTEGER	:= 1;
    _feedBackNum INTEGER := 1;
    _r                  RECORD;

BEGIN
    DELETE FROM costUpdate;

    IF pItemId = -1 THEN 	-- -1 is an invalid item_id => do them all
	INSERT INTO costUpdate ( costUpdate_item_id, costUpdate_item_type )
			SELECT item_id, item_type
			FROM   item;

        -- Recalculate the Item Lowlevel codes
        WHILE _feedBackNum > 0 LOOP
            SELECT updateLowlevel(_counterNum) INTO _feedBackNum;
            _counterNum := _counterNum + 1;
        END LOOP;

    ELSE
	INSERT INTO costUpdate ( costUpdate_item_id, costUpdate_item_type )
			SELECT item_id, item_type
			FROM   item
                        WHERE (item_id=pItemId);
      FOR _r IN SELECT item_id, bomdata_bomwork_level, item_type
                FROM item,
                     indentedBOM(pItemId, getActiveRevId('BOM',pItemId),0,0)
                WHERE (bomdata_item_id=item_id)
                ORDER BY bomdata_bomwork_level LOOP

        -- this only works because of the ORDER BY in the loop SELECT
        UPDATE costUpdate
        SET costupdate_lowlevel_code = _r.bomdata_bomwork_level
        WHERE (costupdate_item_id=_r.item_id);

        IF (NOT FOUND) THEN
          INSERT INTO costUpdate (
            costUpdate_item_id, costUpdate_lowlevel_code, costUpdate_item_type
          ) VALUES (
            _r.item_id, _r.bomdata_bomwork_level, _r.item_type
          );
        END IF;
      END LOOP;

    END IF;

    SELECT count(*) INTO _result
    FROM costUpdate;

    RETURN _result;

END;
$_$;


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

--
-- TOC entry 2116 (class 1255 OID 146566961)
-- Dependencies: 4536 8
-- Name: resetqohbalance(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION resetqohbalance(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 resetQOHBalance($1, CURRENT_TIMESTAMP);
END;
$_$;


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

--
-- TOC entry 2117 (class 1255 OID 146566962)
-- Dependencies: 4536 8
-- Name: resetqohbalance(integer, timestamp with time zone); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION resetqohbalance(integer, timestamp with time zone) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (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;
  pGlDistTS     ALIAS FOR $2;
  _invhistid    INTEGER;
  _itemlocSeries INTEGER;

BEGIN

  IF ( ( SELECT ( (itemsite_controlmethod IN ('L', 'S')) OR
                  (item_type = 'R') OR
                  (itemsite_costmethod = 'J') OR
                  (itemsite_loccntrl) OR
                  (itemsite_qtyonhand > 0) )
         FROM itemsite, item
         WHERE ( (itemsite_item_id=item_id)
          AND (itemsite_id=pItemsiteid) ) ) ) THEN
    RETURN 0;
  END IF;

  _itemlocSeries := NEXTVAL('itemloc_series_seq');

  SELECT postInvTrans( itemsite_id, 'AD', (itemsite_qtyonhand * -1),
                       'I/M', '', '', 'RESET',
                       'Reset QOH Balance to 0',
                       costcat_asset_accnt_id, costcat_adjustment_accnt_id,
                       _itemlocSeries, pGlDistTS ) INTO _invhistid
  FROM itemsite, costcat
  WHERE ( (itemsite_costcat_id=costcat_id)
   AND (itemsite_id=pItemsiteid) );

  PERFORM postItemLocSeries(_itemlocSeries);

  RETURN _invhistid;

END;
$_$;


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

--
-- TOC entry 2118 (class 1255 OID 146566963)
-- Dependencies: 4536 8
-- Name: resolvecosaccount(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

  RETURN resolveCOSAccount($1, $2, -1, -1);

END;
$_$;


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

--
-- TOC entry 2119 (class 1255 OID 146566964)
-- Dependencies: 4536 8
-- Name: resolvecosaccount(integer, integer, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION resolvecosaccount(pitemsiteid integer, pcustid integer, psaletypeid integer, pshipzoneid 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
  _salesaccntid INTEGER;
  _accntid INTEGER;

BEGIN

  SELECT findSalesAccnt(pItemsiteid, 'IS', pCustid, pSaletypeid, pShipzoneid) INTO _salesaccntid;
  IF (_salesaccntid = -1) THEN
    SELECT getUnassignedAccntId() INTO _accntid;
  ELSE
    SELECT salesaccnt_cos_accnt_id INTO _accntid
    FROM salesaccnt
    WHERE (salesaccnt_id=_salesaccntid);
  END IF;

  RETURN _accntid;

END;
$$;


ALTER FUNCTION public.resolvecosaccount(pitemsiteid integer, pcustid integer, psaletypeid integer, pshipzoneid integer) OWNER TO admin;

--
-- TOC entry 2120 (class 1255 OID 146566965)
-- Dependencies: 4536 8
-- Name: resolvecowaccount(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

  RETURN resolveCOWAccount($1, $2, -1, -1);

END;
$_$;


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

--
-- TOC entry 2121 (class 1255 OID 146566966)
-- Dependencies: 4536 8
-- Name: resolvecowaccount(integer, integer, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION resolvecowaccount(pitemsiteid integer, pcustid integer, psaletypeid integer, pshipzoneid 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
  _salesaccntid INTEGER;
  _accntid INTEGER;

BEGIN

  SELECT findSalesAccnt(pItemsiteid, 'IS', pCustid, pSaletypeid, pShipzoneid) INTO _salesaccntid;
  IF (_salesaccntid = -1) THEN
    SELECT getUnassignedAccntId() INTO _accntid;
  ELSE
    SELECT salesaccnt_cow_accnt_id INTO _accntid
    FROM salesaccnt
    WHERE (salesaccnt_id=_salesaccntid);
  END IF;

  RETURN _accntid;

END;
$$;


ALTER FUNCTION public.resolvecowaccount(pitemsiteid integer, pcustid integer, psaletypeid integer, pshipzoneid integer) OWNER TO admin;

--
-- TOC entry 2122 (class 1255 OID 146566967)
-- Dependencies: 4536 8
-- Name: resolvecreditaccount(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

  RETURN resolveCreditAccount($1, $2, -1, -1);

END;
$_$;


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

--
-- TOC entry 2123 (class 1255 OID 146566968)
-- Dependencies: 4536 8
-- Name: resolvecreditaccount(integer, integer, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION resolvecreditaccount(pitemsiteid integer, pcustid integer, psaletypeid integer, pshipzoneid 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
  _salesaccntid INTEGER;
  _accntid INTEGER;

BEGIN

  SELECT findSalesAccnt(pItemsiteid, 'IS', pCustid, pSaletypeid, pShipzoneid) INTO _salesaccntid;
  IF (_salesaccntid = -1) THEN
    SELECT getUnassignedAccntId() INTO _accntid;
  ELSE
    SELECT salesaccnt_credit_accnt_id INTO _accntid
    FROM salesaccnt
    WHERE (salesaccnt_id=_salesaccntid);
  END IF;

  RETURN _accntid;

END;
$$;


ALTER FUNCTION public.resolvecreditaccount(pitemsiteid integer, pcustid integer, psaletypeid integer, pshipzoneid integer) OWNER TO admin;

--
-- TOC entry 2124 (class 1255 OID 146566969)
-- Dependencies: 4536 8
-- Name: resolvesalesaccount(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

  RETURN resolveSalesAccount($1, $2, -1, -1);

END;
$_$;


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

--
-- TOC entry 2126 (class 1255 OID 146566970)
-- Dependencies: 4536 8
-- Name: resolvesalesaccount(integer, integer, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION resolvesalesaccount(pitemsiteid integer, pcustid integer, psaletypeid integer, pshipzoneid 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
  _salesaccntid INTEGER;
  _accntid INTEGER;

BEGIN

  SELECT findSalesAccnt(pItemsiteid, 'IS', pCustid, pSaletypeid, pShipzoneid) INTO _salesaccntid;
  IF (_salesaccntid = -1) THEN
    SELECT getUnassignedAccntId() INTO _accntid;
  ELSE
    SELECT salesaccnt_sales_accnt_id INTO _accntid
    FROM salesaccnt
    WHERE (salesaccnt_id=_salesaccntid);
  END IF;

  RETURN _accntid;

END;
$$;


ALTER FUNCTION public.resolvesalesaccount(pitemsiteid integer, pcustid integer, psaletypeid integer, pshipzoneid integer) OWNER TO admin;

--
-- TOC entry 2127 (class 1255 OID 146566971)
-- Dependencies: 4536 8
-- Name: restoresaleshistory(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

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

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

  DELETE FROM asohisttax
  WHERE (taxhist_parent_id=pAsohistid);

  DELETE FROM asohist
  WHERE (asohist_id=pAsohistid);

  RETURN pAsohistid;

END;
$_$;


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

--
-- TOC entry 2128 (class 1255 OID 146566972)
-- Dependencies: 4536 8
-- Name: returncompleteshipment(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION returncompleteshipment(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 returnCompleteShipment($1, 0, CURRENT_TIMESTAMP);
END;
$_$;


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

--
-- TOC entry 2129 (class 1255 OID 146566973)
-- Dependencies: 4536 8
-- Name: returncompleteshipment(integer, integer, timestamp with time zone); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION returncompleteshipment(integer, integer, timestamp with time zone) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (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;
  _itemlocSeries	INTEGER := $2;
  _timestamp		TIMESTAMP WITH TIME ZONE := $3;
  _r RECORD;
  _result 		RECORD;
  _shiphead_number	TEXT := '';
  _count		INTEGER := 0;
  _countsum		INTEGER := 0;

BEGIN
  FOR _r IN SELECT shipitem_id
            FROM shipitem, shiphead
            WHERE ( (shipitem_shiphead_id=shiphead_id)
             AND (NOT shiphead_shipped)
             AND (shiphead_id=pshipheadid) ) LOOP
    _itemlocSeries := returnShipmentTransaction(_r.shipitem_id, _itemlocSeries, _timestamp);
  END LOOP;

  FOR _result IN SELECT shiphead_number
                   FROM shiphead
                  WHERE ( (shiphead_id=pshipheadid) ) LOOP
    _shiphead_number := _result.shiphead_number;
  END LOOP;

  SELECT COUNT(*) INTO _count
    FROM shipdata
   WHERE(shipdata_shiphead_number=_shiphead_number);

  SELECT COUNT(*) INTO _countsum
    FROM shipdatasum
   WHERE(shipdatasum_shiphead_number=_shiphead_number);

  IF (_count > 0) THEN
    DELETE FROM shipdata
     WHERE(shipdata_shiphead_number=_shiphead_number);
  END IF;
  IF (_countsum > 0) THEN
    DELETE FROM shipdatasum
     WHERE(shipdatasum_shiphead_number=_shiphead_number);
  END IF;

  DELETE FROM pack
   WHERE(pack_shiphead_id=pshipheadid);
  DELETE FROM shiphead
  WHERE (shiphead_id=pshipheadid);

  RETURN _itemlocSeries;

END;
$_$;


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

--
-- TOC entry 2131 (class 1255 OID 146566975)
-- Dependencies: 4536 8
-- Name: returnitemshipments(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION returnitemshipments(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 returnItemShipments('SO', $1, 0, CURRENT_TIMESTAMP);
END;
$_$;


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

--
-- TOC entry 2130 (class 1255 OID 146566974)
-- Dependencies: 4536 8
-- Name: returnitemshipments(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION returnitemshipments(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.
BEGIN
  RETURN returnItemShipments('SO', $1, $2, CURRENT_TIMESTAMP);
END;
$_$;


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

--
-- TOC entry 2132 (class 1255 OID 146566976)
-- Dependencies: 4536 8
-- Name: returnitemshipments(text, integer, integer, timestamp with time zone); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION returnitemshipments(text, integer, integer, timestamp with time zone) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (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;
  pitemid		ALIAS FOR $2;
  _itemlocSeries	INTEGER				:= $3;
  _timestamp		TIMESTAMP WITH TIME ZONE	:= $4;
  _invhistid INTEGER;
  _r RECORD;

BEGIN

  IF (COALESCE(_itemlocSeries,0) = 0 ) THEN
    _itemlocSeries := NEXTVAL('itemloc_series_seq');
  END IF;

  FOR _r IN 
    SELECT shipitem_id
    FROM shipitem
      JOIN shiphead ON (shiphead_id=shipitem_shiphead_id)
    WHERE ((NOT shiphead_shipped)
      AND  (shiphead_order_type=pordertype)
      AND  (shipitem_orderitem_id=pitemid))
  LOOP

    SELECT returnShipmentTransaction(_r.shipitem_id, _itemlocSeries, _timestamp) INTO _itemlocSeries;

    IF (_itemlocSeries < 0) THEN
      RETURN _itemlocSeries;
    END IF;

  END LOOP;

  RETURN _itemlocSeries;

END;
$_$;


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

--
-- TOC entry 2133 (class 1255 OID 146566977)
-- Dependencies: 4536 8
-- Name: returnshipmenttransaction(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION returnshipmenttransaction(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 returnShipmentTransaction($1, 0, CURRENT_TIMESTAMP);
END;
$_$;


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

--
-- TOC entry 2134 (class 1255 OID 146566978)
-- Dependencies: 4536 8
-- Name: returnshipmenttransaction(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION returnshipmenttransaction(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.
BEGIN
  RETURN returnShipmentTransaction($1, $2, CURRENT_TIMESTAMP);
END;
$_$;


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

--
-- TOC entry 2135 (class 1255 OID 146566979)
-- Dependencies: 4536 8
-- Name: returnshipmenttransaction(integer, integer, timestamp with time zone); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION returnshipmenttransaction(integer, integer, timestamp with time zone) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pShipitemId           ALIAS FOR $1;
  pItemlocSeries        ALIAS FOR $2;
  pTimestamp            ALIAS FOR $3;
  _itemlocSeries        INTEGER;
  _invhistid            INTEGER;
  _reserveid            INTEGER;
  _coheadid             INTEGER;
  _rows                 INTEGER;
  _r                    RECORD;
  _rsrv                 RECORD;

BEGIN

    IF (COALESCE(pItemlocSeries, 0) = 0 ) THEN
      _itemlocSeries := NEXTVAL('itemloc_series_seq');
    ELSE
      _itemlocSeries := pItemlocSeries;
    END IF;
  
    -- Find the shipment transaction record
    SELECT shipitem.*,
           shiphead_id, shiphead_number, shiphead_order_type, invhist_series,
           itemsite_loccntrl, itemsite_costmethod, itemsite_controlmethod,
           cohead_prj_id AS prj_id     
      INTO _r
    FROM shipitem
      JOIN shiphead ON (shiphead_id=shipitem_shiphead_id)
      JOIN invhist ON (invhist_id=shipitem_invhist_id)
      JOIN itemsite ON (itemsite_id=invhist_itemsite_id)
      LEFT OUTER JOIN cohead ON ((shiphead_order_type = 'SO') AND (shiphead_order_id = cohead_id))
    WHERE ((NOT shiphead_shipped)
      AND  (shipitem_id=pShipitemId));
      
    GET DIAGNOSTICS _rows = ROW_COUNT;
    IF (_rows = 0 ) THEN
      -- Was it a non-controlled sales order item?
      SELECT shipitem.*,
             shiphead_id, shiphead_number, shiphead_order_type,
             itemsite_loccntrl, itemsite_costmethod, itemsite_controlmethod,
             cohead_prj_id AS prj_id
      INTO _r
      FROM shipitem
        JOIN shiphead ON (shiphead_id=shipitem_shiphead_id)
        JOIN coitem ON (shipitem_orderitem_id=coitem_id)
        JOIN cohead ON (cohead_id=coitem_cohead_id)
        JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
      WHERE ((NOT shiphead_shipped)
        AND  (shipitem_id=pShipitemId)
        AND  (shiphead_order_type = 'SO'));
    END IF;
    
    GET DIAGNOSTICS _rows = ROW_COUNT;
    IF (_rows = 0 AND fetchmetricbool('MultiWhs') ) THEN
      -- Was it a non-controlled transfer order item?
      SELECT shipitem.*,
             shiphead_id, shiphead_number, shiphead_order_type,
             itemsite_loccntrl, itemsite_costmethod, itemsite_controlmethod,
             NULL AS prj_id
      INTO _r
      FROM shipitem
        JOIN shiphead ON (shiphead_id=shipitem_shiphead_id)
        JOIN toitem ON (shipitem_orderitem_id=toitem_id)
        JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
      WHERE ((NOT shiphead_shipped)
        AND  (shipitem_id=pShipitemId)
        AND  (shiphead_order_type = 'TO'));
    END IF;
    
    IF (_rows > 0 ) THEN  
      IF (_r.shiphead_order_type = 'SO') THEN
        -- Handle inventory transaction
        IF (_r.itemsite_controlmethod != 'N' OR _r.itemsite_costmethod = 'J') THEN
          SELECT postInvTrans( itemsite_id, 'RS', (_r.shipitem_qty * coitem_qty_invuomratio),
                               'S/R', _r.shiphead_order_type, formatSoNumber(_r.shipitem_orderitem_id),
                               shiphead_number, 'Return from Shipping',
                               costcat_asset_accnt_id, getPrjAccntId(_r.prj_id, costcat_shipasset_accnt_id),
                               _itemlocSeries, pTimestamp, _r.shipitem_value, _r.shipitem_invhist_id ) INTO _invhistid
          FROM coitem, itemsite, costcat, shiphead, shipitem
          WHERE ((coitem_itemsite_id=itemsite_id)
            AND  (itemsite_costcat_id=costcat_id)
            AND  (coitem_id=_r.shipitem_orderitem_id)
            AND  (shiphead_order_type=_r.shiphead_order_type)
            AND  (shiphead_id=shipitem_shiphead_id)
            AND  (shipitem_orderitem_id=_r.shipitem_orderitem_id));
 
          -- We know the distribution so post this through so the any w/o activity knows about it
          PERFORM postItemlocseries(_itemlocSeries);
        END IF;
 
        IF (_r.itemsite_costmethod = 'J') THEN   
          -- Reopen the work order
          UPDATE wo SET wo_status = 'I' WHERE ((wo_ordtype='S') AND (wo_ordid=_r.shipitem_orderitem_id));
          
          --  Job cost, so correct Production Posting referencing original receipt for reverse info.
          PERFORM correctProduction(wo_id, invhist_invqty, false, _itemlocSeries, pTimestamp, invhist_id)
          FROM wo, invhist
          WHERE ((wo_ordtype = 'S')
            AND  (wo_ordid = _r.shipitem_orderitem_id) 
            AND  (invhist_series=_r.invhist_series)
            AND  (invhist_transtype='RM'));
             
          --  Return eligble material
          PERFORM returnWoMaterial(womatlpost_womatl_id, _itemlocSeries, pTimestamp, womatlpost_invhist_id)
          FROM womatlpost, invhist m, invhist s 
          WHERE ((womatlpost_invhist_id=m.invhist_id)
            AND  (m.invhist_series=s.invhist_series)
            AND  (m.invhist_transtype='IM')
            AND  (s.invhist_id=_r.shipitem_invhist_id));

        END IF; -- end Job Costing

      ELSIF (_r.shiphead_order_type = 'TO') THEN
        SELECT postInvTrans(itemsite_id, 'RS', _r.shipitem_qty,
                            'S/R', _r.shiphead_order_type, formatToNumber(toitem_id),
                            tohead_number, 'Return from Shipping',
                            costcat_asset_accnt_id, costcat_shipasset_accnt_id,
                            _itemlocSeries, pTimestamp, _r.shipitem_value, _r.shipitem_invhist_id ) INTO _invhistid
        FROM toitem, tohead, itemsite, costcat
        WHERE ((toitem_item_id=itemsite_item_id)
          AND  (toitem_tohead_id=tohead_id)
  	  AND  (tohead_src_warehous_id=itemsite_warehous_id)
          AND  (itemsite_costcat_id=costcat_id)
          AND  (toitem_id=_r.shipitem_orderitem_id));

      ELSE
        -- Don't know what kind of order this is
        RETURN -11;
      END IF;

      UPDATE shiphead
      SET shiphead_sfstatus='D'
      WHERE ((shiphead_id=_r.shiphead_id)
        AND  (shiphead_sfstatus='P'));

       -- Handle reservation if applicable
      IF (fetchmetricbool('EnableSOReservations')) THEN
        UPDATE coitem
          SET coitem_qtyreserved = (coitem_qtyreserved + shipitemrsrv_qty)
        FROM shipitemrsrv
        WHERE ((coitem_id=_r.shipitem_orderitem_id)
          AND  (shipitemrsrv_shipitem_id=_r.shipitem_id));

        -- Handle location reservations if applicable
        FOR _rsrv IN
          SELECT *
          FROM shipitemlocrsrv
          WHERE (shipitemlocrsrv_shipitem_id=_r.shipitem_id)
        LOOP
          -- See if a reservation record still exists
          SELECT reserve_id INTO _reserveid
          FROM reserve JOIN itemloc ON (reserve_supply_id=itemloc_id)
          WHERE ((reserve_supply_type='I')
            AND  (reserve_demand_type = 'SO')
            AND  (reserve_demand_id = _r.shipitem_orderitem_id )
            AND  (itemloc_itemsite_id=_rsrv.shipitemlocrsrv_itemsite_id)
            AND  (itemloc_location_id=_rsrv.shipitemlocrsrv_location_id)
            AND  (COALESCE(itemloc_ls_id, -1)=COALESCE(_rsrv.shipitemlocrsrv_ls_id, -1))
            AND  (COALESCE(itemloc_expiration, endOfTime())=COALESCE(_rsrv.shipitemlocrsrv_expiration, endOfTime()))
            AND  (COALESCE(itemloc_warrpurc, endoftime())=COALESCE(_rsrv.shipitemlocrsrv_warrpurc, endoftime())) );

          GET DIAGNOSTICS _rows = ROW_COUNT;
          IF (_rows > 0 ) THEN  
            -- Update existing
            UPDATE reserve
            SET reserve_qty = (reserve_qty + _rsrv.shipitemlocrsrv_qty)
            WHERE (reserve_id=_reserveid);
          ELSE
            -- Recreate record
            INSERT INTO reserve
            SELECT nextval('reserve_reserve_id_seq'), 'SO', _r.shipitem_orderitem_id,
                   'I', itemloc_id, _rsrv.shipitemlocrsrv_qty, 'R'
            FROM itemloc
            WHERE ((itemloc_itemsite_id=_rsrv.shipitemlocrsrv_itemsite_id)
              AND  (itemloc_location_id=_rsrv.shipitemlocrsrv_location_id)
              AND  (COALESCE(itemloc_ls_id, -1)=COALESCE(_rsrv.shipitemlocrsrv_ls_id, -1))
              AND  (COALESCE(itemloc_expiration, endOfTime())=COALESCE(_rsrv.shipitemlocrsrv_expiration, endOfTime()))
              AND  (COALESCE(itemloc_warrpurc, endoftime())=COALESCE(_rsrv.shipitemlocrsrv_warrpurc, endoftime())) );
          END IF;
        END LOOP;
      END IF;
      
      DELETE FROM shipitem WHERE (shipitem_id = _r.shipitem_id );

      -- Clean up if this is the last shipitem on the shipment
      IF (NOT EXISTS(SELECT shipitem_shiphead_id
                     FROM shipitem
                     WHERE (shipitem_shiphead_id=_r.shiphead_id))) THEN
        DELETE FROM shipdata
         WHERE(shipdata_shiphead_number=_r.shiphead_number);
        DELETE FROM shipdatasum
         WHERE(shipdatasum_shiphead_number=_r.shiphead_number);
        DELETE FROM pack
         WHERE(pack_shiphead_id=_r.shiphead_id);
        DELETE FROM shiphead
         WHERE (shiphead_id=_r.shiphead_id);
      END IF;

    END IF;

    RETURN _itemlocSeries;

END;
$_$;


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

--
-- TOC entry 2136 (class 1255 OID 146566981)
-- Dependencies: 4536 8
-- Name: returnwomaterial(integer, numeric, timestamp with time zone); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION returnwomaterial(integer, numeric, timestamp with time zone) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pWomatlid ALIAS FOR $1;
  pQty ALIAS FOR $2;
  pGlDistTS ALIAS FOR $3;
  _itemlocSeries INTEGER;

BEGIN

  SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;
  RETURN returnWoMaterial(pWomatlid, pQty, _itemlocSeries, pGlDistTS);

END;
$_$;


ALTER FUNCTION public.returnwomaterial(integer, numeric, timestamp with time zone) OWNER TO admin;

--
-- TOC entry 2138 (class 1255 OID 146566982)
-- Dependencies: 4536 8
-- Name: returnwomaterial(integer, integer, timestamp with time zone, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION returnwomaterial(integer, integer, timestamp with time zone, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pWomatlid ALIAS FOR $1;
  pItemlocSeries ALIAS FOR $2;
  pGlDistTS ALIAS FOR $3;
  pInvhistId ALIAS FOR $4;
  _woNumber TEXT;
  _invhistid INTEGER;
  _itemlocSeries INTEGER;
  _invqty NUMERIC;
  _womatlqty NUMERIC;
  _cost NUMERIC := 0;
  _rows INTEGER;

BEGIN

  _itemlocSeries := 0;

  SELECT invhist_invqty, invhist_invqty * invhist_unitcost INTO _invqty, _cost
  FROM invhist
  WHERE (invhist_id=pInvhistId);

  GET DIAGNOSTICS _rows = ROW_COUNT;
  
  IF (_rows = 0) THEN
    RAISE EXCEPTION 'No transaction found for invhist_id %', pInvhistId;
  END IF;
  
  SELECT itemuomtouom(itemsite_item_id, NULL, womatl_uom_id, _invqty)
    INTO _womatlqty
    FROM womatl, itemsite
    WHERE((womatl_itemsite_id=itemsite_id)
     AND (womatl_id=pWomatlid));

  GET DIAGNOSTICS _rows = ROW_COUNT;
  
  IF (_rows = 0) THEN
    _womatlqty := _invqty;
  END IF;

  IF ( SELECT (
         CASE WHEN (womatl_qtyreq >= 0) THEN
           womatl_qtyiss < _womatlqty
         ELSE
           womatl_qtyiss > _womatlqty
         END )
       FROM womatl
       WHERE ( womatl_id=pWomatlid ) ) THEN
    RETURN pItemlocSeries;
  END IF;

  SELECT formatWoNumber(womatl_wo_id) INTO _woNumber
  FROM womatl
  WHERE (womatl_id=pWomatlid);

  IF (pItemlocSeries = 0) THEN
    SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;
  ELSE
    _itemlocSeries = pItemlocSeries;
  END IF;

  -- Post the transaction
  SELECT postInvTrans( ci.itemsite_id, 'IM', (_invqty * -1), 
                       'W/O', 'WO', _woNumber, '',
                       ('Return ' || item_number || ' from Work Order'),
                       getPrjAccntId(wo_prj_id, pc.costcat_wip_accnt_id), cc.costcat_asset_accnt_id, _itemlocSeries, pGlDistTS,
                       -- Cost will be ignored by Standard Cost items sites
                       _cost, pInvhistId) INTO _invhistid
    FROM womatl, wo,
         itemsite AS ci, costcat AS cc,
         itemsite AS pi, costcat AS pc,
         item
   WHERE((womatl_itemsite_id=ci.itemsite_id)
     AND (ci.itemsite_costcat_id=cc.costcat_id)
     AND (womatl_wo_id=wo_id)
     AND (wo_itemsite_id=pi.itemsite_id)
     AND (pi.itemsite_costcat_id=pc.costcat_id)
     AND (ci.itemsite_item_id=item_id)
     AND (womatl_id=pWomatlid) );

--  Create linkage to the transaction created
  INSERT INTO womatlpost (womatlpost_womatl_id,womatlpost_invhist_id)
              VALUES (pWomatlid,_invhistid);

--  Decrease the parent W/O's WIP value by the value of the returned components
  UPDATE wo
  SET wo_wipvalue = (wo_wipvalue - (CASE WHEN(itemsite_costmethod IN ('A','J'))
                                              THEN _cost
                                         WHEN(itemsite_costmethod='S')
                                              THEN stdcost(itemsite_item_id) * _invqty
                                         ELSE 0.0 END )),
      wo_postedvalue = (wo_postedvalue - (CASE WHEN(itemsite_costmethod IN ('A','J'))
                                                    THEN _cost
                                               WHEN(itemsite_costmethod='S')
                                                    THEN stdcost(itemsite_item_id) * _invqty
                                               ELSE 0.0 END ))
  FROM womatl, itemsite
  WHERE ( (wo_id=womatl_wo_id)
   AND (womatl_itemsite_id=itemsite_id)
   AND (womatl_id=pWomatlid) );

  UPDATE womatl
  SET womatl_qtyiss = (womatl_qtyiss - _womatlqty),
      womatl_lastreturn = CURRENT_DATE
  WHERE (womatl_id=pWomatlid);

  RETURN _itemlocSeries;
END;
$_$;


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

--
-- TOC entry 9075 (class 0 OID 0)
-- Dependencies: 2138
-- Name: FUNCTION returnwomaterial(integer, integer, timestamp with time zone, integer); Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON FUNCTION returnwomaterial(integer, integer, timestamp with time zone, integer) IS 'Returns material by reversing a specific historical transaction';


--
-- TOC entry 2139 (class 1255 OID 146566983)
-- Dependencies: 4536 8
-- Name: returnwomaterial(integer, numeric, integer, timestamp with time zone, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION returnwomaterial(integer, numeric, integer, timestamp with time zone, boolean DEFAULT false) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pWomatlid ALIAS FOR $1;
  pQty ALIAS FOR $2;
  pItemlocSeries ALIAS FOR $3;
  pGlDistTS ALIAS FOR $4;
  pReqStdCost ALIAS FOR $5;
  _woNumber TEXT;
  _invhistid INTEGER;
  _itemlocSeries INTEGER;
  _qty NUMERIC;
  _cost NUMERIC := 0;

BEGIN

  _itemlocSeries := 0;
  
  IF ( SELECT (
         CASE WHEN (womatl_qtyreq >= 0) THEN
           womatl_qtyiss < pQty
         ELSE
           womatl_qtyiss > pQty
         END )
       FROM womatl
       WHERE ( womatl_id=pWomatlid ) ) THEN
    RETURN pItemlocSeries;
  END IF;

  SELECT itemuomtouom(itemsite_item_id, womatl_uom_id, NULL, pQty)
    INTO _qty
    FROM womatl, itemsite
   WHERE((womatl_itemsite_id=itemsite_id)
     AND (womatl_id=pWomatlid));
  IF (NOT FOUND) THEN
    _qty := pQty;
  END IF;

  SELECT formatWoNumber(womatl_wo_id) INTO _woNumber
  FROM womatl
  WHERE (womatl_id=pWomatlid);

  IF (pItemlocSeries = 0) THEN
    SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;
  ELSE
    _itemlocSeries = pItemlocSeries;
  END IF;

  -- Get the cost average
  IF (pReqStdCost) THEN
    SELECT stdcost(itemsite_item_id) * _qty INTO _cost
    FROM womatl, itemsite
    WHERE((womatl_itemsite_id=itemsite_id)
      AND (womatl_id=pWomatlid));
  ELSE
    SELECT SUM(invhist_value_before - invhist_value_after) / SUM(invhist_qoh_before - invhist_qoh_after)  * _qty INTO _cost
    FROM invhist, womatlpost, womatl 
    WHERE((womatlpost_womatl_id=womatl_id) 
     AND (womatlpost_invhist_id=invhist_id) 
     AND (invhist_qoh_before > invhist_qoh_after)
     AND (womatl_id=pWomatlId));
  END IF;

  _cost := COALESCE(_cost, 0); -- make sure it's not a null value

  -- Post the transaction
  SELECT postInvTrans( ci.itemsite_id, 'IM', (_qty * -1), 
                       'W/O', 'WO', _woNumber, '',
                       ('Return ' || item_number || ' from Work Order'),
                       getPrjAccntId(wo_prj_id, pc.costcat_wip_accnt_id), cc.costcat_asset_accnt_id, _itemlocSeries, pGlDistTS,
                       -- Cost will be ignored by Standard Cost items sites
                       _cost) INTO _invhistid
    FROM womatl, wo,
         itemsite AS ci, costcat AS cc,
         itemsite AS pi, costcat AS pc,
         item
   WHERE((womatl_itemsite_id=ci.itemsite_id)
     AND (ci.itemsite_costcat_id=cc.costcat_id)
     AND (womatl_wo_id=wo_id)
     AND (wo_itemsite_id=pi.itemsite_id)
     AND (pi.itemsite_costcat_id=pc.costcat_id)
     AND (ci.itemsite_item_id=item_id)
     AND (womatl_id=pWomatlid) );

--  Create linkage to the transaction created
  IF (_invhistid != -1) THEN
    INSERT INTO womatlpost (womatlpost_womatl_id,womatlpost_invhist_id)
                VALUES (pWomatlid,_invhistid);
  END IF;

--  Decrease the parent W/O's WIP value by the value of the returned components
  UPDATE wo
  SET wo_wipvalue = (wo_wipvalue - (CASE WHEN(itemsite_costmethod IN ('A','J'))
                                              THEN _cost
                                         WHEN(itemsite_costmethod='S')
                                              THEN stdcost(itemsite_item_id) * _qty
                                         ELSE 0.0 END )),
      wo_postedvalue = (wo_postedvalue - (CASE WHEN(itemsite_costmethod IN ('A','J'))
                                                    THEN _cost
                                               WHEN(itemsite_costmethod='S')
                                                    THEN stdcost(itemsite_item_id) * _qty
                                               ELSE 0.0 END ))
  FROM womatl, itemsite
  WHERE ( (wo_id=womatl_wo_id)
   AND (womatl_itemsite_id=itemsite_id)
   AND (womatl_id=pWomatlid) );

  UPDATE womatl
  SET womatl_qtyiss = (womatl_qtyiss - pQty),
      womatl_lastreturn = CURRENT_DATE
  WHERE (womatl_id=pWomatlid);

  RETURN _itemlocSeries;

END;
$_$;


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

--
-- TOC entry 2140 (class 1255 OID 146566984)
-- Dependencies: 4536 8
-- Name: returnwomaterialbatch(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION returnwomaterialbatch(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
  pWoid ALIAS FOR $1;
  _itemlocSeries INTEGER;
  _woid INTEGER;
  _r RECORD;

BEGIN

  SELECT wo_id INTO _woid
  FROM wo
  WHERE ( (wo_status IN ('E','I'))
   AND (wo_id=pWoid) );

  IF (FOUND) THEN
    SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;

    FOR _r IN SELECT womatl_id, 
                CASE WHEN wo_qtyord >= 0 THEN
                  womatl_qtyiss
                ELSE
                  ((womatl_qtyreq - womatl_qtyiss) * -1)
                END AS qty
              FROM wo, womatl, itemsite
              WHERE ((wo_id=womatl_wo_id)
              AND (womatl_itemsite_id=itemsite_id)
              AND ( (wo_qtyord < 0) OR (womatl_issuemethod IN ('S','M')) )
              AND (womatl_wo_id=pWoid)) LOOP

      IF (_r.qty != 0) THEN
        PERFORM returnWoMaterial(_r.womatl_id, _r.qty, _itemlocSeries, now());
      END IF;

    END LOOP;

--  Reset the W/O Status to E
    UPDATE wo
    SET wo_status='E'
    WHERE (wo_id=pWoid);

    RETURN _itemlocSeries;

  ELSE 
    RETURN -1;
  END IF;

END;
$_$;


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

--
-- TOC entry 2141 (class 1255 OID 146566985)
-- Dependencies: 4536 8
-- Name: reversecashreceipt(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION reversecashreceipt(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
  pCashrcptid ALIAS FOR $1;
  pJournalNumber ALIAS FOR $2;
  _p RECORD;
  _r RECORD;
  _postToAR NUMERIC;
  _postToMisc NUMERIC;
  _posted_base NUMERIC := 0;
  _posted NUMERIC := 0;
  _sequence INTEGER;
  _aropenid INTEGER;
  _arMemoNumber TEXT;
  _arAccntid INTEGER;
  _closed BOOLEAN;
  _debitAccntid INTEGER;
  _exchGain NUMERIC;
  _comment      TEXT;

BEGIN
  _posted := 0;
  _posted_base := 0;

  SELECT fetchGLSequence() INTO _sequence;

  SELECT accnt_id INTO _arAccntid
  FROM cashrcpt, accnt, salescat
  WHERE ((cashrcpt_salescat_id=salescat_id)
    AND  (salescat_ar_accnt_id=accnt_id)
    AND  (cashrcpt_id=pCashrcptid));
  IF (NOT FOUND) THEN
    SELECT accnt_id INTO _arAccntid
    FROM cashrcpt, accnt
    WHERE ( (findARAccount(cashrcpt_cust_id)=accnt_id)
     AND (cashrcpt_id=pCashrcptid) );
    IF (NOT FOUND) THEN
      RETURN -5;
    END IF;
  END IF;

  SELECT cashrcpt_cust_id, ('Reverse Cash Receipt posting for ' || cust_number||'-'||cust_name) AS custnote,
         cashrcpt_fundstype, cashrcpt_number, cashrcpt_docnumber,
         cashrcpt_distdate, cashrcpt_amount, cashrcpt_discount,
         (cashrcpt_amount / cashrcpt_curr_rate) AS cashrcpt_amount_base,
         (cashrcpt_discount / cashrcpt_curr_rate) AS cashrcpt_discount_base,
         cashrcpt_notes,
         cashrcpt_bankaccnt_id AS bankaccnt_id,
         accnt_id AS prepaid_accnt_id,
         cashrcpt_usecustdeposit,
         cashrcpt_curr_id, cashrcpt_curr_rate INTO _p
  FROM accnt, cashrcpt LEFT OUTER JOIN custinfo ON (cashrcpt_cust_id=cust_id)
  WHERE ( (findPrepaidAccount(cashrcpt_cust_id)=accnt_id)
   AND (cashrcpt_id=pCashrcptid) );
  IF (NOT FOUND) THEN
    RETURN -7;
  END IF;

  IF (_p.cashrcpt_fundstype IN ('A', 'D', 'M', 'V')) THEN
    IF NOT EXISTS(SELECT ccpay_id
                  FROM ccpay
                  WHERE ((ccpay_order_number=CAST(pCashrcptid AS TEXT))
                     AND (ccpay_status IN ('C', 'A')))) THEN
      RETURN -8;
    END IF;
    _debitAccntid := findPrepaidAccount(_p.cashrcpt_cust_id);
  ELSE
    SELECT accnt_id INTO _debitAccntid
    FROM cashrcpt, bankaccnt, accnt
    WHERE ( (cashrcpt_bankaccnt_id=bankaccnt_id)
     AND (bankaccnt_accnt_id=accnt_id)
     AND (cashrcpt_id=pCashrcptid) );
    IF (NOT FOUND) THEN
      RETURN -6;
    END IF;
  END IF;

--  Determine the amount to post to A/R Open Items
  SELECT COALESCE(SUM(cashrcptitem_amount),0) INTO _postToAR
  FROM cashrcptitem JOIN aropen ON (aropen_id=cashrcptitem_aropen_id)
  WHERE ((cashrcptitem_cashrcpt_id=pCashrcptid)
   AND (cashrcptitem_applied));
  IF (NOT FOUND) THEN
    _postToAR := 0;
  END IF;

--  Determine the amount to post to Misc. Distributions
  SELECT COALESCE(SUM(cashrcptmisc_amount),0) INTO _postToMisc
  FROM cashrcptmisc
  WHERE (cashrcptmisc_cashrcpt_id=pCashrcptid);
  IF (NOT FOUND) THEN
    _postToMisc := 0;
  END IF;

--  Check to see if the C/R is over applied
  IF ((_postToAR + _postToMisc) > _p.cashrcpt_amount) THEN
    RETURN -1;
  END IF;

--  Check to see if the C/R is positive amount
  IF (_p.cashrcpt_amount <= 0) THEN
    RETURN -2;
  END IF;

--  Distribute A/R Applications
  FOR _r IN SELECT aropen_id, aropen_doctype, aropen_docnumber, aropen_docdate,
                   aropen_duedate, aropen_curr_id, aropen_curr_rate,
                   round(aropen_amount - aropen_paid, 2) <=
                      round(aropen_paid + 
                      currToCurr(_p.cashrcpt_curr_id, aropen_curr_id,abs(cashrcptitem_amount + cashrcptitem_discount),_p.cashrcpt_distdate),2)
                               AS closed,
                   cashrcptitem_id, cashrcptitem_amount, cashrcptitem_discount,
                   (cashrcptitem_amount / _p.cashrcpt_curr_rate) AS cashrcptitem_amount_base,
                   (cashrcptitem_discount / _p.cashrcpt_curr_rate) AS cashrcptitem_discount_base,
                   round(aropen_paid - 
                      currToCurr(_p.cashrcpt_curr_id, aropen_curr_id,abs(cashrcptitem_amount),_p.cashrcpt_distdate),2) AS new_paid,
                   round(currToCurr(_p.cashrcpt_curr_id, aropen_curr_id,abs(cashrcptitem_discount),_p.cashrcpt_distdate),2) AS new_discount
            FROM cashrcptitem JOIN aropen ON (cashrcptitem_aropen_id=aropen_id)
            WHERE ((cashrcptitem_cashrcpt_id=pCashrcptid)
              AND (cashrcptitem_applied)) LOOP

--  Handle discount 
    IF (_r.cashrcptitem_discount_base > 0) THEN
      PERFORM reverseCashReceiptDisc(_r.cashrcptitem_id, pJournalNumber);
    END IF;
     
--  Update the aropen item to post the paid amount
    UPDATE aropen
    SET aropen_paid = _r.new_paid - _r.new_discount,
        aropen_open = TRUE,
        aropen_closedate = NULL
    WHERE (aropen_id=_r.aropen_id);

--  Cache the running amount posted
    _posted_base := _posted_base + _r.cashrcptitem_amount_base;
    _posted := _posted + _r.cashrcptitem_amount;

--  Record the cashrcpt application
    IF (_r.aropen_doctype IN ('I','D')) THEN
      INSERT INTO arapply
      ( arapply_cust_id,
        arapply_source_aropen_id, arapply_source_doctype, arapply_source_docnumber,
        arapply_target_aropen_id, arapply_target_doctype, arapply_target_docnumber,
        arapply_fundstype, arapply_refnumber, arapply_reftype, arapply_ref_id,
        arapply_applied, arapply_closed,
        arapply_postdate, arapply_distdate, arapply_journalnumber, arapply_username,
        arapply_curr_id
       )
      VALUES
      ( _p.cashrcpt_cust_id,
        -1, 'K', _p.cashrcpt_number,
        _r.aropen_id, _r.aropen_doctype, _r.aropen_docnumber,
        _p.cashrcpt_fundstype, _p.cashrcpt_docnumber, 'CRA', _r.cashrcptitem_id,
        (round(_r.cashrcptitem_amount, 2) * -1.0), _r.closed,
        CURRENT_DATE, _p.cashrcpt_distdate, pJournalNumber, getEffectiveXtUser(), _p.cashrcpt_curr_id );
    ELSE
      INSERT INTO arapply
      ( arapply_cust_id,
        arapply_source_aropen_id, arapply_source_doctype, arapply_source_docnumber,
        arapply_target_aropen_id, arapply_target_doctype, arapply_target_docnumber,
        arapply_fundstype, arapply_refnumber, arapply_reftype, arapply_ref_id,
        arapply_applied, arapply_closed, arapply_postdate, arapply_distdate,
        arapply_journalnumber, arapply_username, arapply_curr_id )
      VALUES
      ( _p.cashrcpt_cust_id,
        _r.aropen_id, _r.aropen_doctype, _r.aropen_docnumber,
        -1, 'R', _p.cashrcpt_number,
        '', '', 'CRA', _r.cashrcptitem_id,
        (round(abs(_r.cashrcptitem_amount), 2) * -1.0), _r.closed,
        CURRENT_DATE, _p.cashrcpt_distdate, pJournalNumber, getEffectiveXtUser(), _p.cashrcpt_curr_id );
    END IF;

    _exchGain := arCurrGain(_r.aropen_id,_p.cashrcpt_curr_id, abs(_r.cashrcptitem_amount),
                           _p.cashrcpt_distdate);

    PERFORM insertIntoGLSeries( _sequence, 'A/R', 'CR',
                        (_r.aropen_doctype || '-' || _r.aropen_docnumber),
                        CASE WHEN _r.aropen_doctype != 'R' THEN _arAccntid
                        ELSE findDeferredAccount(_p.cashrcpt_cust_id) END, 
                        (round(_r.cashrcptitem_amount_base + _exchGain, 2) * -1.0),
                        _p.cashrcpt_distdate, _p.custnote );

    IF (_exchGain <> 0) THEN
        PERFORM insertIntoGLSeries(_sequence, 'A/R', 'CR',
               _r.aropen_doctype || '-' || _r.aropen_docnumber,
               getGainLossAccntId(
               CASE WHEN _r.aropen_doctype != 'R' THEN _arAccntid
               ELSE findDeferredAccount(_p.cashrcpt_cust_id) END
               ), round(_exchGain, 2),
               _p.cashrcpt_distdate, _p.custnote);

    END IF;

  END LOOP;

--  Distribute Misc. Applications
  FOR _r IN SELECT cashrcptmisc_id, cashrcptmisc_accnt_id, cashrcptmisc_amount,
                   (cashrcptmisc_amount / _p.cashrcpt_curr_rate) AS cashrcptmisc_amount_base,
                   cashrcptmisc_notes
            FROM cashrcptmisc
            WHERE (cashrcptmisc_cashrcpt_id=pCashrcptid)  LOOP

--  Cache the running amount posted
    _posted_base := (_posted_base + _r.cashrcptmisc_amount_base);
    _posted := (_posted + _r.cashrcptmisc_amount);

--  Record the cashrcpt application
    INSERT INTO arapply
    ( arapply_cust_id,
      arapply_source_aropen_id, arapply_source_doctype, arapply_source_docnumber,
      arapply_target_aropen_id, arapply_target_doctype, arapply_target_docnumber,
      arapply_fundstype, arapply_refnumber,
      arapply_applied, arapply_closed,
      arapply_postdate, arapply_distdate, arapply_journalnumber, arapply_username,
      arapply_curr_id, arapply_reftype, arapply_ref_id )
    VALUES
    ( _p.cashrcpt_cust_id,
      -1, 'K', '',
      -1, 'Misc.', '',
      _p.cashrcpt_fundstype, _p.cashrcpt_docnumber,
      (round(_r.cashrcptmisc_amount, 2) * -1.0), TRUE,
      CURRENT_DATE, _p.cashrcpt_distdate, pJournalNumber, getEffectiveXtUser(), 
      _p.cashrcpt_curr_id, 'CRD', _r.cashrcptmisc_id );

    PERFORM insertIntoGLSeries( _sequence, 'A/R', 'CR', _r.cashrcptmisc_notes,
                                _r.cashrcptmisc_accnt_id,
                                (round(_r.cashrcptmisc_amount_base, 2) * -1.0),
                                _p.cashrcpt_distdate, _p.custnote );

  END LOOP;

--  Post any remaining Cash to an A/R Debit Memo
--  this credit memo may absorb an occasional currency exchange rounding error
  IF (round(_posted_base, 2) < round(_p.cashrcpt_amount_base, 2)) THEN
    _comment := ('Unapplied from ' || _p.cashrcpt_fundstype || '-' || _p.cashrcpt_docnumber);
    PERFORM insertIntoGLSeries( _sequence, 'A/R', 'CR',
                                _comment,
                                _p.prepaid_accnt_id,
                                ((round(_p.cashrcpt_amount_base, 2) - round(_posted_base, 2)) * -1.0),
                                _p.cashrcpt_distdate, _p.custnote );
    SELECT fetchArMemoNumber() INTO _arMemoNumber;
    -- Post A/R Debit Memo
    SELECT createARDebitMemo(NULL, _p.cashrcpt_cust_id, pJournalNumber, _arMemoNumber, '',
                              _p.cashrcpt_distdate, (_p.cashrcpt_amount - _posted),
                              _comment, -1, -1, -1, _p.cashrcpt_distdate, -1, NULL, 0,
                              _p.cashrcpt_curr_id) INTO _aropenid;
    -- Create Cash Receipt Item to capture posting
    INSERT INTO cashrcptitem
      ( cashrcptitem_cashrcpt_id, cashrcptitem_aropen_id, cashrcptitem_amount )
    VALUES
      ( pCashrcptid, _aropenid, ((_p.cashrcpt_amount - _posted) * 1.0) );

  ELSIF (round(_posted_base, 2) > round(_p.cashrcpt_amount_base, 2)) THEN
    PERFORM insertIntoGLSeries(_sequence, 'A/R', 'CR',
                   'Currency Exchange Rounding - ' || _p.cashrcpt_docnumber,
                   getGainLossAccntId(_debitAccntid),
                   ((round(_posted_base, 2) - round((_p.cashrcpt_amount_base + _p.cashrcpt_discount_base), 2)) * 1.0),
                   _p.cashrcpt_distdate, _p.custnote);
  END IF;

--  Debit Cash
  PERFORM insertIntoGLSeries( _sequence, 'A/R', 'CR',
                    (_p.cashrcpt_fundstype || '-' || _p.cashrcpt_docnumber),
                     _debitAccntid, round(_p.cashrcpt_amount_base, 2),
                     _p.cashrcpt_distdate,
                     _p.custnote );

  PERFORM postGLSeries(_sequence, pJournalNumber);

--  Update and void the posted cashrcpt
  UPDATE cashrcpt SET cashrcpt_posted=FALSE,
                      cashrcpt_posteddate=NULL,
                      cashrcpt_postedby=NULL,
                      cashrcpt_void=TRUE
  WHERE (cashrcpt_id=pCashrcptid);

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 2142 (class 1255 OID 146566987)
-- Dependencies: 4536 8
-- Name: reversecashreceiptdisc(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION reversecashreceiptdisc(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
  pCashrcptItemId ALIAS FOR $1;
  pJournalNumber ALIAS FOR $2;
  _r RECORD;
  _t RECORD;
  _v RECORD;
  _ardiscountid INTEGER;
  _arMemoNumber TEXT;
  _arAccntid INTEGER;
  _discountAccntid INTEGER;
  _comment      TEXT;
  _discprcnt NUMERIC;
  _check INTEGER;

BEGIN

    -- Fetch base records for processing
    SELECT aropen_id, aropen_doctype, aropen_amount,
           cashrcptitem_discount,
           cashrcpt_cust_id, cashrcpt_distdate, cashrcpt_applydate,
           cashrcpt_curr_id, cashrcpt_fundstype, cashrcpt_docnumber,
           round(currToCurr(cashrcpt_curr_id, aropen_curr_id, cashrcptitem_discount, cashrcpt_distdate),2) AS aropen_discount
      INTO _r
    FROM cashrcptitem 
      JOIN cashrcpt ON (cashrcptitem_cashrcpt_id=cashrcpt_id)
      JOIN aropen ON ( (aropen_id=cashrcptitem_aropen_id) AND (aropen_doctype IN ('I', 'D')) )
    WHERE (cashrcptitem_id=pCashrcptItemId);

    -- Get discount account
    _discountAccntid := findardiscountaccount(_r.cashrcpt_cust_id);
  
    IF (_r.cashrcptitem_discount > 0) THEN
      --  Determine discount percentage
      _discprcnt := _r.aropen_discount / _r.aropen_amount;

      SELECT fetchArMemoNumber() INTO _arMemoNumber;
      _comment := 'Discount Credit Reversal from ' || _r.cashrcpt_fundstype || '-' || _r.cashrcpt_docnumber;

      -- Create misc debit memo record
      _ardiscountid := nextval('aropen_aropen_id_seq');
      INSERT INTO aropen (
        aropen_id, aropen_docdate, aropen_duedate, aropen_doctype, 
        aropen_docnumber, aropen_curr_id, aropen_posted, aropen_amount ) 
      VALUES ( 
        _ardiscountid, _r.cashrcpt_distdate, _r.cashrcpt_distdate, 'D', 
        _arMemoNumber, _r.cashrcpt_curr_id, false,_r.cashrcptitem_discount);
        
      IF (fetchMetricBool('CreditTaxDiscount')) THEN
        --  proportional tax credits calculated and implemented for the debit memo generated by the discount
        IF (_r.aropen_doctype  = 'I') THEN
          -- Tax for invoices
          SELECT aropen_cobmisc_id AS invcheadid, 
                 invchead_curr_id, 
                 invchead_invcdate INTO _t
          FROM aropen
            LEFT OUTER JOIN invchead ON (aropen_cobmisc_id = invchead_id) 
            LEFT OUTER JOIN invcitem ON (invchead_id = invcitem_invchead_id)
          WHERE aropen_id = _r.aropen_id;

          FOR _v IN SELECT tax_sales_accnt_id,
                           tax_id, 
                           round(sum(taxdetail_tax), 2) AS tax,
                           currToBase(_t.invchead_curr_id, round(sum(taxdetail_tax), 2), _t.invchead_invcdate) AS taxbasevalue
          FROM tax 
            JOIN calculateTaxDetailSummary('I', _t.invcheadid, 'T') ON (taxdetail_tax_id=tax_id)
            GROUP BY tax_id, tax_sales_accnt_id 
          LOOP
            INSERT INTO aropentax(
              taxhist_parent_id, taxhist_taxtype_id, taxhist_tax_id,
              taxhist_percent, taxhist_amount, taxhist_tax, 
              taxhist_docdate, taxhist_basis)
            VALUES (
              _ardiscountid, getadjustmenttaxtypeid(), _v.tax_id, 
              0.00, 0.00, (round((_v.tax * _discprcnt), 2)), 
              _r.cashrcpt_distdate, 0.00);
          END LOOP;

        ELSIF (_r.aropen_doctype  = 'D') THEN
          -- Tax for debit memos
          INSERT INTO aropentax(
            taxhist_parent_id, taxhist_taxtype_id, taxhist_tax_id,
            taxhist_percent, taxhist_amount, taxhist_tax, 
            taxhist_docdate, taxhist_basis)
          SELECT
            _ardiscountid, taxhist_taxtype_id, taxhist_tax_id, 
            0.00, 0.00, (round((taxhist_tax * _discprcnt), 2)),  
            _r.cashrcpt_distdate, 0.00
          FROM aropentax
          WHERE (taxhist_parent_id=_r.aropen_id);
              
        END IF;
      END IF; -- End taxes

      -- Create debit memo for discount
      SELECT createARDebitMemo(_ardiscountid, _r.cashrcpt_cust_id, pJournalNumber, _arMemoNumber, '',
                                _r.cashrcpt_distdate, _r.cashrcptitem_discount,
                                _comment, -1, -1, _discountAccntid, _r.cashrcpt_distdate,
                                -1, NULL, 0, _r.cashrcpt_curr_id) INTO _ardiscountid;

    END IF; -- End handle Discount

    RETURN 1;

END;
$_$;


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

--
-- TOC entry 2143 (class 1255 OID 146566988)
-- Dependencies: 4536 8
-- Name: reverseglseries(integer, date, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION reverseglseries(integer, date, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pSequence ALIAS FOR $1;
  pDistDate ALIAS FOR $2;
  pNotes ALIAS FOR $3;
  _sequence INTEGER := fetchGLSequence();	
  _journal INTEGER;

BEGIN

  IF (SELECT COUNT(gltrans_sequence) > 0 FROM gltrans WHERE gltrans_sequence = pSequence) THEN
    SELECT fetchJournalNumber(jrnluse_use) INTO _journal
    FROM gltrans
      JOIN jrnluse ON (gltrans_journalnumber=jrnluse_number)
    WHERE (gltrans_sequence=pSequence)
    LIMIT 1;
  
    INSERT INTO gltrans (gltrans_created, gltrans_posted, gltrans_exported,
                         gltrans_date, gltrans_sequence, gltrans_accnt_id,
                         gltrans_source, gltrans_docnumber, gltrans_misc_id,
                         gltrans_amount, gltrans_notes, gltrans_journalnumber,
                         gltrans_doctype)
                 SELECT  CURRENT_TIMESTAMP, FALSE, FALSE,
                         pDistDate, _sequence, gltrans_accnt_id,
                         gltrans_source, gltrans_docnumber, gltrans_misc_id,
                         (gltrans_amount * -1), pNotes, _journal,
                         gltrans_doctype
                    FROM gltrans
                   WHERE (gltrans_sequence=pSequence);

    PERFORM postIntoTrialBalance(_sequence);
  ELSE
    SELECT fetchJournalNumber(jrnluse_use) INTO _journal
    FROM sltrans
      JOIN jrnluse ON (sltrans_journalnumber=jrnluse_number)
    WHERE (sltrans_sequence=pSequence)
    LIMIT 1;
    
    INSERT INTO sltrans (sltrans_created, sltrans_posted,
                         sltrans_date, sltrans_sequence, sltrans_accnt_id,
                         sltrans_source, sltrans_docnumber, sltrans_misc_id,
                         sltrans_amount, sltrans_notes, sltrans_journalnumber,
                         sltrans_doctype)
                 SELECT  CURRENT_TIMESTAMP, FALSE,
                         pDistDate, _sequence, sltrans_accnt_id,
                         sltrans_source, sltrans_docnumber, sltrans_misc_id,
                         (sltrans_amount * -1), pNotes, _journal,
                         sltrans_doctype
                    FROM sltrans
                   WHERE (sltrans_sequence=pSequence);
  END IF;

  RETURN _journal;
END;
$_$;


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

--
-- TOC entry 2137 (class 1255 OID 146566989)
-- Dependencies: 4536 8
-- Name: revokeallmodulecmnttypesource(integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  DELETE FROM cmnttypesource
  WHERE (cmnttypesource_id IN ( SELECT cmnttypesource_id
                                FROM cmnttypesource, source
                                WHERE ( (cmnttypesource_source_id=source_id)
                                  AND (cmnttypesource_cmnttype_id=pCmnttypeid)
                                  AND (source_module=pModuleName) ) ) );

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 2114 (class 1255 OID 146566990)
-- Dependencies: 4536 8
-- Name: revokeallmodulepriv(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  DELETE FROM usrpriv
  WHERE (usrpriv_id IN ( SELECT usrpriv_id
                         FROM usrpriv, priv
                         WHERE ( (usrpriv_priv_id=priv_id)
                          AND (usrpriv_username=pUsername)
                          AND (priv_module=pModuleName) ) ) );

  NOTIFY "usrprivUpdated";

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 2125 (class 1255 OID 146566991)
-- Dependencies: 4536 8
-- Name: revokeallmoduleprivgroup(integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  DELETE FROM grppriv
  WHERE (grppriv_id IN ( SELECT grppriv_id
                         FROM grppriv, priv
                         WHERE ( (grppriv_priv_id=priv_id)
                          AND (grppriv_grp_id=pGrpid)
                          AND (priv_module=pModuleName) ) ) );

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 2144 (class 1255 OID 146566992)
-- Dependencies: 4536 8
-- Name: revokecmnttypesource(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  DELETE FROM cmnttypesource
  WHERE ( (cmnttypesource_cmnttype_id=pCmnttypeid)
    AND (cmnttypesource_source_id=pSourceid) );

  RETURN TRUE;

END;
$_$;


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

--
-- TOC entry 2145 (class 1255 OID 146566993)
-- Dependencies: 4536 8
-- Name: revokegroup(text, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  DELETE FROM usrgrp
  WHERE ( (usrgrp_username=pUsername)
   AND (usrgrp_grp_id=pGrpid) );

  RETURN TRUE;

END;
$_$;


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

--
-- TOC entry 2146 (class 1255 OID 146566994)
-- Dependencies: 4536 8
-- Name: revokepriv(text, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  DELETE FROM usrpriv
  WHERE ( (usrpriv_username=pUsername)
   AND (usrpriv_priv_id=pPrivid) );

  NOTIFY "usrprivUpdated";

  RETURN TRUE;

END;
$_$;


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

--
-- TOC entry 2147 (class 1255 OID 146566995)
-- Dependencies: 4536 8
-- Name: revokepriv(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  DELETE FROM usrpriv
  WHERE ( (usrpriv_username=pUsername)
   AND (usrpriv_priv_id IN (SELECT priv_id
                              FROM priv
                             WHERE priv_name=pPrivname) ));

  NOTIFY "usrprivUpdated";

  RETURN TRUE;

END;
$_$;


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

--
-- TOC entry 2148 (class 1255 OID 146566996)
-- Dependencies: 4536 8
-- Name: revokeprivgroup(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  DELETE FROM grppriv
  WHERE ( (grppriv_grp_id=pGrpid)
   AND (grppriv_priv_id=pPrivid) );

  NOTIFY "usrprivUpdated";

  RETURN TRUE;

END;
$_$;


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

--
-- TOC entry 2149 (class 1255 OID 146566997)
-- Dependencies: 4536 8
-- Name: rollupactualcost(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
    RETURN rollUpSorACost(pitemid, TRUE);
END;
$_$;


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

--
-- TOC entry 2150 (class 1255 OID 146566998)
-- Dependencies: 4536 8
-- Name: rollupsoracost(integer, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  _counter := 0;

  SELECT indentedWhereUsed(pItemid) INTO _setid;

  FOR _consumers IN SELECT bomwork_item_id
                    FROM bomwork
                    WHERE (bomwork_set_id=_setid)
                    ORDER BY bomwork_level LOOP
    PERFORM updateSorACost( _consumers.bomwork_item_id, costelem_type, TRUE,
			    lowerCost(_consumers.bomwork_item_id,
				      costelem_type, pActual),
			    pActual )
    FROM costelem
    WHERE (costelem_sys);

    PERFORM updateLowerUserCosts(_consumers.bomwork_item_id, pActual);

    _counter := _counter + 1;

  END LOOP;

  PERFORM deleteBOMWorkset(_setid);

  RETURN _counter;

END;
$_$;


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

--
-- TOC entry 2151 (class 1255 OID 146566999)
-- Dependencies: 4536 8
-- Name: rollupstandardcost(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
    RETURN rollUpSorACost(pItemid, FALSE);
END;
$_$;


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

--
-- TOC entry 2152 (class 1255 OID 146567000)
-- Dependencies: 4536 8
-- Name: roundcost(numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION roundcost(pcost numeric) RETURNS numeric
    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
  _scale INTEGER;

BEGIN
  IF (pCost IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT locale_cost_scale INTO _scale
  FROM locale
  WHERE (locale_id=getUsrLocaleId());

  RETURN ROUND(pCost, _scale);

END;
$$;


ALTER FUNCTION public.roundcost(pcost numeric) OWNER TO admin;

--
-- TOC entry 2153 (class 1255 OID 146567001)
-- Dependencies: 4536 8
-- Name: roundlocale(boolean, numeric, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION roundlocale(pfractional boolean, pqty numeric, plocale text) RETURNS numeric
    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
  _r RECORD;
  _scale INTEGER;

BEGIN
  IF (pFractional) THEN
    SELECT * INTO _r
    FROM locale
    WHERE (locale_id=getUsrLocaleId());

    _scale := CASE pLocale WHEN 'qtyper' THEN _r.locale_qtyper_scale
                           WHEN 'cost' THEN _r.locale_cost_scale
                           ELSE _r.locale_qty_scale
              END;

    RETURN ROUND(pQty, _scale);
  ELSE
    IF (TRUNC(pQty) < pQty) THEN
      RETURN (TRUNC(pQty) + 1);
    ELSE
      RETURN TRUNC(pQty);
    END IF;
  END IF;
END;
$$;


ALTER FUNCTION public.roundlocale(pfractional boolean, pqty numeric, plocale text) OWNER TO admin;

--
-- TOC entry 2154 (class 1255 OID 146567002)
-- Dependencies: 4536 8
-- Name: roundqty(boolean, numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION roundqty(pfractional boolean, pqty numeric) RETURNS numeric
    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
  _scale INTEGER;

BEGIN
  SELECT locale_qty_scale INTO _scale
  FROM locale
  WHERE (locale_id=getUsrLocaleId());

  IF (pFractional) THEN
    RETURN ROUND(pQty, _scale);
  ELSE
    IF (TRUNC(pQty) < ROUND(pQty, _scale)) THEN
      RETURN (TRUNC(pQty) + 1);
    ELSE
      RETURN TRUNC(pQty);
    END IF;
  END IF;
END;
$$;


ALTER FUNCTION public.roundqty(pfractional boolean, pqty numeric) OWNER TO admin;

--
-- TOC entry 2155 (class 1255 OID 146567003)
-- Dependencies: 4536 8
-- Name: roundsale(numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION roundsale(psale numeric) RETURNS numeric
    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
  _scale INTEGER;

BEGIN
  IF (pSale IS NULL) THEN
    RETURN NULL;
  END IF;

  SELECT locale_salesprice_scale INTO _scale
  FROM locale
  WHERE (locale_id=getUsrLocaleId());

  RETURN ROUND(pSale, _scale);

END;
$$;


ALTER FUNCTION public.roundsale(psale numeric) OWNER TO admin;

--
-- TOC entry 2156 (class 1255 OID 146567004)
-- Dependencies: 4536 8
-- Name: roundup(numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION roundup(numeric) RETURNS numeric
    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

  pValue ALIAS FOR $1;
  _checkValue integer;

BEGIN

  _checkValue := pValue::integer;

  IF (_checkValue::numeric < pValue) THEN
    RETURN (_checkValue + 1)::numeric;
  ELSE
    RETURN _checkValue::numeric;
  END IF;

END;
$_$;


ALTER FUNCTION public.roundup(numeric) OWNER TO admin;

--
-- TOC entry 2158 (class 1255 OID 146567006)
-- Dependencies: 4536 8
-- Name: saveaddr(integer, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION saveaddr(integer, text, text, text, text, text, 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
  pAddrId ALIAS FOR $1;
  pNumber ALIAS FOR $2;
  pAddr1 ALIAS FOR $3;
  pAddr2 ALIAS FOR $4;
  pAddr3 ALIAS FOR $5;
  pCity ALIAS FOR $6;
  pState ALIAS FOR $7;
  pPostalCode ALIAS FOR $8;
  pCountry ALIAS FOR $9;
  pFlag ALIAS FOR $10;
  _returnVal INTEGER;

BEGIN
 
  SELECT saveAddr(pAddrId,pNumber, pAddr1,pAddr2,pAddr3,pCity,pState,pPostalCode,pCountry,true,'',pFlag) INTO _returnVal;
  
  RETURN _returnVal;

END;
$_$;


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

--
-- TOC entry 2157 (class 1255 OID 146567005)
-- Dependencies: 4536 8
-- Name: saveaddr(integer, text, text, text, text, text, text, text, text, boolean, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION saveaddr(integer, text, text, text, text, text, text, text, text, boolean, 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
  pAddrId ALIAS FOR $1;
  pNumber ALIAS FOR $2;
  pAddr1 ALIAS FOR $3;
  pAddr2 ALIAS FOR $4;
  pAddr3 ALIAS FOR $5;
  pCity ALIAS FOR $6;
  pState ALIAS FOR $7;
  pPostalCode ALIAS FOR $8;
  pCountry ALIAS FOR $9;
  pActive ALIAS FOR $10;
  pNotes ALIAS FOR $11;
  pFlag ALIAS FOR $12;
  _addrId INTEGER;
  _addrNumber INTEGER;
  _flag TEXT;
  _p RECORD;
  _cnt INTEGER;
  _notes TEXT;

BEGIN
  --Validate
  IF ((pFlag IS NULL) OR (pFlag = '') OR (pFlag = 'CHECK') OR (pFlag = 'CHANGEONE') OR (pFlag = 'CHANGEALL')) THEN
    IF (pFlag='') THEN
      _flag := 'CHECK';
    ELSE
      _flag := COALESCE(pFlag,'CHECK');
    END IF;
  ELSE
	RAISE EXCEPTION 'Invalid Flag (%). Valid flags are CHECK, CHANGEONE or CHANGEALL', pFlag;
  END IF;

  _notes := COALESCE(pNotes,'');
  
  --If there is nothing here, get out
  IF ( (pNumber = '' OR pNumber IS NULL)
    AND (pAddr1 = '' OR pAddr1 IS NULL)
    AND (pAddr2 = '' OR pAddr2 IS NULL)
    AND (pAddr3 = '' OR pAddr3 IS NULL)
    AND (pCity = '' OR pCity IS NULL)
    AND (pState = '' OR pState IS NULL)
    AND (pPostalCode = '' OR pPostalCode IS NULL)
    AND (pCountry = '' OR pCountry IS NULL) ) THEN
    RETURN NULL;
  
  END IF;
  
  _addrId := COALESCE(pAddrId,-1);

  --If we have an ID see if anything has changed, if not get out
  IF (_addrId >= 0) THEN
    SELECT * FROM addr INTO _p
    WHERE ((pAddrId=addr_id)
    AND (COALESCE(pNumber,addr_number)=addr_number)
    AND (COALESCE(pAddr1, '')=COALESCE(addr_line1, ''))
    AND (COALESCE(pAddr2, '')=COALESCE(addr_line2, ''))
    AND (COALESCE(pAddr3, '')=COALESCE(addr_line3, ''))
    AND (COALESCE(pCity, '')=COALESCE(addr_city, ''))
    AND (COALESCE(pState, '')=COALESCE(addr_state, ''))
    AND (COALESCE(pPostalCode, '')=COALESCE(addr_postalcode, ''))
    AND (COALESCE(pCountry, '')=COALESCE(addr_country, ''))
    AND (pActive=addr_active)
    AND (_notes=COALESCE(addr_notes,'')));
    IF (FOUND) THEN
      RETURN _addrId;
    END IF;
  END IF;
 
  --Check to see if duplicate address exists

    SELECT addr_id, addr_notes INTO _p
    FROM addr 
    WHERE ((_addrId <> addr_id)
    AND  (COALESCE(UPPER(addr_line1),'') = COALESCE(UPPER(pAddr1),''))
    AND  (COALESCE(UPPER(addr_line2),'') = COALESCE(UPPER(pAddr2),''))
    AND  (COALESCE(UPPER(addr_line3),'') = COALESCE(UPPER(pAddr3),''))
    AND  (COALESCE(UPPER(addr_city),'') = COALESCE(UPPER(pCity),''))
    AND  (COALESCE(UPPER(addr_state),'') = COALESCE(UPPER(pState),''))
    AND  (COALESCE(UPPER(addr_postalcode),'') = COALESCE(UPPER(pPostalcode),''))
    AND  (COALESCE(UPPER(addr_country),'') = COALESCE(UPPER(pCountry),'')));
    IF (FOUND) THEN
	--Note:  To prevent overwriting of existing notes, the application
	--needs to load any existing notes for a matching address before altering them.
	IF (_notes <> _p.addr_notes) THEN
		UPDATE addr 
		SET addr_notes=addr_notes || '
' || _notes
		WHERE addr_id=_p.addr_id;
	END IF;
        RETURN _p.addr_id;  --A matching address exits
    END IF;
 
  IF (_addrId < 0) THEN
    _flag := 'CHANGEONE';
  END IF;

  IF (_flag = 'CHECK') THEN
    IF addrUseCount(_addrId) > 1 THEN
      RETURN -2;
    ELSIF (SELECT COUNT(addr_id)=0 FROM addr WHERE (addr_id=_addrId)) THEN
      _flag := 'CHANGEONE';
    ELSE
      _flag := 'CHANGEALL';
    END IF;
  END IF;

  IF (_flag = 'CHANGEALL') THEN
    _addrNumber := pNumber;
    IF (_addrNumber IS NULL) THEN
      SELECT addr_number INTO _addrNumber
        FROM addr
       WHERE(addr_id = _addrId);
      IF (_addrNumber IS NULL) THEN
        _addrNumber := fetchNextNumber('AddressNumber');
      END IF;
    END IF;
   
    UPDATE addr SET
      addr_line1 = pAddr1, addr_line2 = pAddr2, addr_line3 = pAddr3,
      addr_city = pCity, addr_state = pState,
      addr_postalcode = pPostalcode, addr_country = pCountry,
      addr_active = pActive, addr_notes = pNotes
    WHERE addr_id = _addrId;
    RETURN _addrId;

  ELSE
    SELECT NEXTVAL('addr_addr_id_seq') INTO _addrId;

    IF (_flag = 'CHANGEONE') THEN
      _addrNumber := fetchNextNumber('AddressNumber');
    ELSE
      _addrNumber := COALESCE(pNumber::text,fetchNextNumber('AddressNumber'));
    END IF;

    INSERT INTO addr ( addr_id, addr_number,
    addr_line1, addr_line2, addr_line3, 
    addr_city, addr_state, addr_postalcode, addr_country, 
    addr_active, addr_notes  
    ) VALUES ( _addrId, _addrNumber,
    pAddr1, pAddr2, pAddr3, 
    pCity, pState, pPostalcode, pCountry,
    pActive, _notes);
    RETURN _addrId;
	
  END IF;
END;
$_$;


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

--
-- TOC entry 2159 (class 1255 OID 146567007)
-- Dependencies: 4536 8
-- Name: savealarm(integer, text, date, time without time zone, integer, text, boolean, text, boolean, text, boolean, text, text, integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION savealarm(integer, text, date, time without time zone, integer, text, boolean, text, boolean, text, boolean, text, text, 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
  pAlarmId ALIAS FOR $1;
  pAlarmNumber ALIAS FOR $2;
  pDate ALIAS FOR $3;
  pTime ALIAS FOR $4;
  pOffset ALIAS FOR $5;
  pQualifier ALIAS FOR $6;
  pEvent ALIAS FOR $7;
  pEventRecipient ALIAS FOR $8;
  pEmail ALIAS FOR $9;
  pEmailRecipient ALIAS FOR $10;
  pSysmsg ALIAS FOR $11;
  pSysmsgRecipient ALIAS FOR $12;
  pSource ALIAS FOR $13;
  pSourceId ALIAS FOR $14;
  pFlag ALIAS FOR $15;
  _alarmId INTEGER;
  _alarmNumber TEXT;
  _alarmTime TIMESTAMP;
  _alarmInterval INTERVAL;
  _alarmTrigger TIMESTAMP;
  _isNew BOOLEAN;
  _flag TEXT;
  _alarmCount INTEGER := 0;
  _debug BOOLEAN := false;

BEGIN
  IF (_debug) THEN
    RAISE NOTICE 'pAlarmId = %', pAlarmId;
    RAISE NOTICE 'pAlarmNumber = %', pAlarmNumber;
    RAISE NOTICE 'pDate = %', pDate;
    RAISE NOTICE 'pTime = %', pTime;
    RAISE NOTICE 'pOffset = %', pOffset;
    RAISE NOTICE 'pQualifier = %', pQualifier;
    RAISE NOTICE 'pEvent = %', pEvent;
    RAISE NOTICE 'pEventRecipient = %', pEventRecipient;
    RAISE NOTICE 'pEmail = %', pEmail;
    RAISE NOTICE 'pEmailRecipient = %', pEmailRecipient;
    RAISE NOTICE 'pSysmsg = %', pSysmsg;
    RAISE NOTICE 'pSysmsgRecipient = %', pSysmsgRecipient;
    RAISE NOTICE 'pSource = %', pSource;
    RAISE NOTICE 'pSourceId = %', pSourceId;
    RAISE NOTICE 'pFlag = %', pFlag;
  END IF;
  --Validate
  IF ((pFlag IS NULL) OR (pFlag = '') OR (pFlag = 'CHECK') OR (pFlag = 'CHANGEONE') OR (pFlag = 'CHANGEALL')) THEN
    IF (pFlag='') THEN
      _flag := 'CHECK';
    ELSE
      _flag := COALESCE(pFlag,'CHECK');
    END IF;
  ELSE
	RAISE EXCEPTION 'Invalid Flag (%). Valid flags are CHECK, CHANGEONE or CHANGEALL', pFlag;
  END IF;
  
  --If there is nothing here get out
  IF ( (pAlarmId IS NULL OR pAlarmId = -1)
	AND (pOffset IS NULL)
        AND (pSourceId IS NULL)
	AND (COALESCE(pQualifier, '') = '')
	AND (COALESCE(pEventRecipient, '') = '')
	AND (COALESCE(pEmailRecipient, '') = '')
	AND (COALESCE(pSysmsgRecipient, '') = '')
	AND (COALESCE(pSource, '') = '') ) THEN
	
	RETURN NULL;

  END IF;
  
  IF (pAlarmId IS NULL OR pAlarmId = -1) THEN 
    _isNew := true;
    _alarmId := nextval('alarm_alarm_id_seq');
    _alarmNumber := fetchNextNumber('AlarmNumber');
  ELSE
    SELECT COUNT(alarm_id) INTO _alarmCount
      FROM alarm
      WHERE ((alarm_id=pAlarmId)
      AND (alarm_source=pSource)
      AND (alarm_source_id=pSourceId));

    -- ask whether new or update if name changes
    -- but only if this isn't a new record with a pre-allocated id
    IF (_alarmCount < 1 AND _flag = 'CHECK') THEN
      IF (EXISTS(SELECT alarm_id
                 FROM alarm
                 WHERE (alarm_id=pAlarmId))) THEN
        RETURN -10;
      ELSE
        _isNew := true;
        _alarmNumber := fetchNextNumber('AlarmNumber');
      END IF;
    ELSIF (_flag = 'CHANGEONE') THEN
      _isNew := true;
      _alarmId := nextval('alarm_alarm_id_seq');
      _alarmNumber := fetchNextNumber('AlarmNumber');
    END IF;
  END IF;

  _alarmNumber := COALESCE(_alarmNumber,pAlarmNumber,fetchNextNumber('AlarmNumber'));

  _alarmTime := COALESCE(pDate, CURRENT_DATE) + COALESCE(pTime, CURRENT_TIME);
  IF (COALESCE(pOffset, 0) > 0) THEN
    _alarmInterval := CASE WHEN (pQualifier IN ('MB', 'MA')) THEN CAST(pOffset AS TEXT) || ' minutes'
                           WHEN (pQualifier IN ('HB', 'HA')) THEN CAST(pOffset AS TEXT) || ' hours'
                           WHEN (pQualifier IN ('DB', 'DA')) THEN CAST(pOffset AS TEXT) || ' days'
                           ELSE ''
                     END;
    _alarmTrigger := CASE WHEN (pQualifier IN ('MB', 'HB', 'DB')) THEN _alarmTime - _alarmInterval 
                          WHEN (pQualifier IN ('MA', 'HA', 'DA')) THEN _alarmTime + _alarmInterval
                          ELSE _alarmTime
                     END; 
  ELSE
    _alarmTrigger := _alarmTime;
  END IF;

  IF (_isNew) THEN
    _alarmId := COALESCE(_alarmId,pAlarmId,nextval('alarm_alarm_id_seq'));
 
    INSERT INTO alarm (
      alarm_id,alarm_number,
      alarm_event, alarm_email, alarm_sysmsg, alarm_trigger,
      alarm_time, alarm_time_offset, alarm_time_qualifier,
      alarm_creator, alarm_event_recipient, alarm_email_recipient, alarm_sysmsg_recipient,
      alarm_source, alarm_source_id )
    VALUES (
      _alarmId, _alarmNumber,
      pEvent, pEmail, pSysmsg, _alarmTrigger,
      _alarmTime, pOffset, pQualifier,
      getEffectiveXtUser(), pEventRecipient, pEmailRecipient, pSysmsgRecipient,
      pSource, pSourceId );

    RETURN _alarmId;

  ELSE
    UPDATE alarm SET
      alarm_number=_alarmNumber,
      alarm_event=COALESCE(pEvent, alarm_event),
      alarm_email=COALESCE(pEmail, alarm_event),
      alarm_sysmsg=COALESCE(pSysmsg, alarm_event),
      alarm_trigger=_alarmTrigger,
      alarm_time=_alarmTime,
      alarm_time_offset=COALESCE(pOffset, alarm_time_offset),
      alarm_time_qualifier=COALESCE(pQualifier, alarm_time_qualifier),
      alarm_event_recipient=COALESCE(pEventRecipient, alarm_event_recipient),
      alarm_email_recipient=COALESCE(pEmailRecipient, alarm_email_recipient),
      alarm_sysmsg_recipient=COALESCE(pSysmsgRecipient, alarm_sysmsg_recipient)
    WHERE (alarm_id=pAlarmId);
    
    RETURN pAlarmId;

  END IF;
END;
$_$;


ALTER FUNCTION public.savealarm(integer, text, date, time without time zone, integer, text, boolean, text, boolean, text, boolean, text, text, integer, text) OWNER TO admin;

--
-- TOC entry 2160 (class 1255 OID 146567009)
-- Dependencies: 4536 8
-- Name: savebomhead(integer, text, date, text, numeric, numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION savebomhead(integer, text, date, text, numeric, numeric) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pItemid ALIAS FOR $1;
  pRevision ALIAS FOR $2;
  pRevisionDate ALIAS FOR $3;
  pDocumentNumber ALIAS FOR $4;
  pBatchSize ALIAS FOR $5;
  pRequiredQtyPer ALIAS FOR $6;
  _seq INTEGER;
  _p RECORD;
  _revid INTEGER;
  
BEGIN

  IF (NOT fetchMetricBool('RevControl')) THEN -- Deal with BOM if Rev Control Turned off
    SELECT bomhead_id INTO _seq
    FROM bomhead 
    WHERE (bomhead_item_id=pItemid);

    IF (NOT FOUND) THEN  -- No bomhead exists
      _seq := NEXTVAL('bomhead_bomhead_id_seq');
      
      INSERT INTO bomhead 
        (bomhead_id,bomhead_item_id,bomhead_docnum,bomhead_revision,
        bomhead_revisiondate,bomhead_batchsize,bomhead_requiredqtyper,bomhead_rev_id)
        VALUES 
        (_seq,pItemid, pDocumentNumber, pRevision, pRevisionDate, pBatchSize, pRequiredQtyPer,-1);   
    ELSE
      UPDATE bomhead SET
        bomhead_revision	= pRevision,
        bomhead_revisiondate	= pRevisionDate,
        bomhead_docnum		= pDocumentNumber,
        bomhead_batchsize	= pBatchSize,
        bomhead_requiredqtyper = pRequiredQtyPer
      WHERE (bomhead_id=_seq);
    END IF;
    
    RETURN _seq;
  ELSE  -- Deal with Revision Control
    IF (COALESCE(pRevision,'') = '' AND getActiveRevId('BOM',pItemid) != -1) THEN 
        RAISE EXCEPTION 'Revision Control records exist for item.  You must provide a new or existing revision number.';
    END IF;
    
    SELECT * INTO _p
    FROM bomhead
      LEFT OUTER JOIN rev ON (bomhead_rev_id=rev_id),
      item
    WHERE ((bomhead_item_id=pItemid)
    AND (COALESCE(bomhead_revision,'')=COALESCE(pRevision,''))
    AND (bomhead_item_id=item_id));

    IF (NOT FOUND) THEN  -- This is a new bomhead record
      IF LENGTH(pRevision) > 0 THEN  -- We need to create a revision record   
        SELECT createbomrev(pItemid, pRevision) INTO _revid;
        
        UPDATE bomhead SET
          bomhead_revisiondate		= pRevisiondate,
          bomhead_docnum		= pDocumentNumber,
          bomhead_batchsize		= pBatchsize,
          bomhead_requiredqtyper	= pRequiredqtyper
        WHERE (bomhead_rev_id=_revid);
        
        SELECT bomhead_id INTO _seq
        FROM bomhead
        WHERE (bomhead_rev_id=_revid);
        
        RETURN _seq;      
      ELSE  -- Just create a regular bom header record
       _seq := NEXTVAL('bomhead_bomhead_id_seq');
       
       INSERT INTO bomhead 
        (bomhead_id,bomhead_item_id,bomhead_docnum,bomhead_revision,
        bomhead_revisiondate,bomhead_batchsize,bomhead_requiredqtyper,bomhead_rev_id)
        VALUES 
        (_seq,pItemid, pDocumentNumber, pRevision, pRevisionDate, pBatchSize, pRequiredQtyPer,-1);
        
        RETURN _seq;      
        
      END IF;
    ELSE  -- We need to update a record
      IF (_p.rev_status = 'I') THEN
        RAISE EXCEPTION 'Revision % for % is inactive.  Update not allowed.', _p.rev_number, _p.item_number;

      ELSIF (COALESCE(pRevision,'') = COALESCE(_p.bomhead_revision,'')) THEN  -- No change, just update
        UPDATE bomhead SET
          bomhead_revisiondate		= pRevisiondate,
          bomhead_docnum		= pDocumentNumber,
          bomhead_batchsize		= pBatchSize,
          bomhead_requiredqtyper	= pRequiredqtyper
        WHERE (bomhead_id=_p.bomhead_id);

        RETURN _p.bomhead_id;
        
      ELSE -- Need a new revision
        SELECT createbomrev(pItemid, pRevision) INTO _revid;
        
        UPDATE bomhead SET
          bomhead_revisiondate		= pRevisiondate,
          bomhead_docnum		= pDocumentNumber,
          bomhead_batchsize		= pBatchSize,
          bomhead_requiredqtyper	= pRequiredqtyper
        WHERE (bomhead_rev_id=_revid);

        SELECT bomhead_id INTO _seq
        FROM bomhead
        WHERE (bomhead_rev_id=_revid);
        
        RETURN _seq;
      END IF;
    END IF;
  END IF;

  RETURN _seq;

END;
$_$;


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

--
-- TOC entry 2162 (class 1255 OID 146567011)
-- Dependencies: 4536 8
-- Name: savecntct(integer, text, integer, text, text, text, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION savecntct(pcntctid integer, pcontactnumber text, paddrid integer, phonorific text, pfirstname text, pmiddlename text, plastname text, psuffix text, pphone text, pphone2 text, pfax text, pemail text, pwebaddr text, ptitle text, pflag 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
  
  SELECT saveCntct(pCntctId,pContactNumber,NULL,pAddrId,pHonorific,pFirstName,pMiddleName,pLastName,pSuffix,NULL,
        NULL,pPhone,pPhone2,pFax,pEmail,pWebAddr,NULL,pTitle,pFlag, NULL) INTO _returnVal;
  
  RETURN _returnVal;

END;
$$;


ALTER FUNCTION public.savecntct(pcntctid integer, pcontactnumber text, paddrid integer, phonorific text, pfirstname text, pmiddlename text, plastname text, psuffix text, pphone text, pphone2 text, pfax text, pemail text, pwebaddr text, ptitle text, pflag text) OWNER TO admin;

--
-- TOC entry 2161 (class 1255 OID 146567010)
-- Dependencies: 4536 8
-- Name: savecntct(integer, text, integer, integer, text, text, text, text, text, text, boolean, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION savecntct(pcntctid integer, pcontactnumber text, pcrmacctid integer, paddrid integer, phonorific text, pfirstname text, pmiddlename text, plastname text, psuffix text, pinitials text, pactive boolean, pphone text, pphone2 text, pfax text, pemail text, pwebaddr text, pnotes text, ptitle text, pflag 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
  
  SELECT saveCntct( pCntctId, pContactNumber, pCrmAcctId, pAddrId, pHonorific, pFirstName, pMiddleName, pLastName, pSuffix, pInitials, 
	pActive, pPhone, pPhone2, pFax, pEmail, pWebAddr, pNotes, pTitle, pFlag, NULL) INTO _returnVal;
  RETURN _returnVal;

END;
$$;


ALTER FUNCTION public.savecntct(pcntctid integer, pcontactnumber text, pcrmacctid integer, paddrid integer, phonorific text, pfirstname text, pmiddlename text, plastname text, psuffix text, pinitials text, pactive boolean, pphone text, pphone2 text, pfax text, pemail text, pwebaddr text, pnotes text, ptitle text, pflag text) OWNER TO admin;

--
-- TOC entry 2163 (class 1255 OID 146567012)
-- Dependencies: 4536 8
-- Name: savecntct(integer, text, integer, integer, text, text, text, text, text, text, boolean, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION savecntct(pcntctid integer, pcontactnumber text, pcrmacctid integer, paddrid integer, phonorific text, pfirstname text, pmiddlename text, plastname text, psuffix text, pinitials text, pactive boolean, pphone text, pphone2 text, pfax text, pemail text, pwebaddr text, pnotes text, ptitle text, pflag text, pownerusername 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
  _cntctId INTEGER;
  _cntctNumber TEXT;
  _isNew BOOLEAN;
  _flag TEXT;
  _contactCount INTEGER := 0;

BEGIN
  --Validate
  IF ((pFlag IS NULL) OR (pFlag = '') OR (pFlag = 'CHECK') OR (pFlag = 'CHANGEONE') OR (pFlag = 'CHANGEALL')) THEN
    IF (pFlag='') THEN
      _flag := 'CHECK';
    ELSE
      _flag := COALESCE(pFlag,'CHECK');
    END IF;
  ELSE
	RAISE EXCEPTION 'Invalid Flag (%). Valid flags are CHECK, CHANGEONE or CHANGEALL', pFlag;
  END IF;
  
  --If there is nothing here get out
  IF ( (pCntctId IS NULL OR pCntctId = -1)
	AND (pAddrId IS NULL)
	AND (COALESCE(pFirstName, '') = '')
	AND (COALESCE(pMiddleName, '') = '')
	AND (COALESCE(pLastName, '') = '')
	AND (COALESCE(pSuffix, '') = '')
	AND (COALESCE(pHonorific, '') = '')
	AND (COALESCE(pInitials, '') = '')
	AND (COALESCE(pPhone, '') = '')
	AND (COALESCE(pPhone2, '') = '')
	AND (COALESCE(pFax, '') = '')
	AND (COALESCE(pEmail, '') = '')
	AND (COALESCE(pWebAddr, '') = '')
	AND (COALESCE(pNotes, '') = '')
	AND (COALESCE(pTitle, '') = '') ) THEN
	
	RETURN NULL;

  END IF;
  
  IF (pCntctId IS NULL OR pCntctId = -1) THEN 
    _isNew := true;
    _cntctId := nextval('cntct_cntct_id_seq');
    _cntctNumber := COALESCE(pContactNumber,fetchNextNumber('ContactNumber'));
  ELSE
    SELECT COUNT(cntct_id) INTO _contactCount
      FROM cntct
      WHERE ((cntct_id=pCntctId)
      AND (cntct_first_name=pFirstName)
      AND (cntct_last_name=pLastName));

    -- ask whether new or update if name changes
    -- but only if this isn't a new record with a pre-allocated id
    IF (_contactCount < 1 AND _flag = 'CHECK') THEN
      IF (EXISTS(SELECT cntct_id
                 FROM cntct
                 WHERE (cntct_id=pCntctId))) THEN
        RETURN -10;
      ELSE
        _isNew := true;
        _cntctNumber := fetchNextNumber('ContactNumber');
      END IF;
    ELSIF (_flag = 'CHANGEONE') THEN
      _isNew := true;
      _cntctId := nextval('cntct_cntct_id_seq');
      _cntctNumber := fetchNextNumber('ContactNumber');
    ELSIF (_flag = 'CHANGEALL') THEN
      _isNew := false;
    END IF;
  END IF;

  IF (pContactNumber = '') THEN
    _cntctNumber := fetchNextNumber('ContactNumber');
  ELSE
    _cntctNumber := COALESCE(_cntctNumber,pContactNumber,fetchNextNumber('ContactNumber'));
  END IF;

  IF (_isNew) THEN
    _cntctId := COALESCE(_cntctId,pCntctId,nextval('cntct_cntct_id_seq'));
 
    INSERT INTO cntct (
      cntct_id,cntct_number,
      cntct_crmacct_id,cntct_addr_id,cntct_first_name,
      cntct_last_name,cntct_honorific,cntct_initials,
      cntct_active,cntct_phone,cntct_phone2,
      cntct_fax,cntct_email,cntct_webaddr,
      cntct_notes,cntct_title,cntct_middle,cntct_suffix, cntct_owner_username ) 
    VALUES (
      _cntctId, COALESCE(_cntctNumber,fetchNextNumber('ContactNumber')) ,pCrmAcctId,pAddrId,
      pFirstName,pLastName,pHonorific,
      pInitials,COALESCE(pActive,true),pPhone,pPhone2,pFax,
      pEmail,pWebAddr,pNotes,pTitle,pMiddleName,pSuffix,pOwnerUsername );

    RETURN _cntctId;

  ELSE
    UPDATE cntct SET
      cntct_number=COALESCE(_cntctNumber,fetchNextNumber('ContactNumber')),
      cntct_crmacct_id=COALESCE(pCrmAcctId,cntct_crmacct_id),
      cntct_addr_id=COALESCE(pAddrId,cntct_addr_id),
      cntct_first_name=COALESCE(pFirstName,cntct_first_name),
      cntct_last_name=COALESCE(pLastName,cntct_last_name),
      cntct_honorific=COALESCE(pHonorific,cntct_honorific),
      cntct_initials=COALESCE(pInitials,cntct_initials),
      cntct_active=COALESCE(pActive,cntct_active),
      cntct_phone=COALESCE(pPhone,cntct_phone),
      cntct_phone2=COALESCE(pPhone2,cntct_phone2),
      cntct_fax=COALESCE(pFax,cntct_fax),
      cntct_email=COALESCE(pEmail,cntct_email),
      cntct_webaddr=COALESCE(pWebAddr,cntct_webaddr),
      cntct_notes=COALESCE(pNotes,cntct_notes),
      cntct_title=COALESCE(pTitle,cntct_title),
      cntct_middle=COALESCE(pMiddleName,cntct_middle),
      cntct_suffix=COALESCE(pSuffix,cntct_suffix),
      cntct_owner_username=COALESCE(pOwnerUsername, cntct_owner_username) 
    WHERE (cntct_id=pCntctId);
    
    RETURN pCntctId;

  END IF;
END;
$$;


ALTER FUNCTION public.savecntct(pcntctid integer, pcontactnumber text, pcrmacctid integer, paddrid integer, phonorific text, pfirstname text, pmiddlename text, plastname text, psuffix text, pinitials text, pactive boolean, pphone text, pphone2 text, pfax text, pemail text, pwebaddr text, pnotes text, ptitle text, pflag text, pownerusername text) OWNER TO admin;

--
-- TOC entry 2165 (class 1255 OID 146567014)
-- Dependencies: 4536 8
-- Name: saveimageass(text, integer, character, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION saveimageass(text, integer, character, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pSource 	ALIAS FOR $1;
  pSourceId 	ALIAS FOR $2;
  pPurpose 	ALIAS FOR $3;
  pImageid 	ALIAS FOR $4;
  _imageassId INTEGER = 0;

BEGIN

--  See if this link already exists
  SELECT imageass_id INTO _imageassId
  FROM imageass
  WHERE ((imageass_source_id=pSourceId)
  AND (imageass_source=pSource)
  AND (imageass_image_id=pImageId)
  AND (imageass_purpose=pPurpose));

  IF (FOUND) THEN
    RETURN _imageassId;
  END IF;
  
-- See if a record with this purpose already exists (item only)
  IF (pSource = 'I' AND pPurpose != 'M') THEN
    SELECT imageass_id INTO _imageassId
    FROM imageass
    WHERE ((imageass_source_id=pSourceId)
    AND (imageass_source=pSource)
    AND (imageass_purpose=pPurpose));
  END IF;

  IF (_imageassId > 0) THEN
    UPDATE imageass SET
      imageass_image_id=pImageId
    WHERE (imageass_id=_imageassId);
  ELSE
    _imageassId := NEXTVAL('imageass_imageass_id_seq');
    INSERT INTO imageass VALUES (_imageassId,pSourceId,pSource,pImageid,CASE WHEN pSource='I' THEN pPurpose ELSE 'M' END);
  END IF;
  
  RETURN _imageassId;
END;
$_$;


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

--
-- TOC entry 2166 (class 1255 OID 146567015)
-- Dependencies: 4536 8
-- Name: saveipsitem(integer, integer, integer, numeric, numeric, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION saveipsitem(integer, integer, integer, numeric, numeric, integer, integer) RETURNS integer
    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
  pIpsItemId	ALIAS FOR $1;
  pIpsHeadId	ALIAS FOR $2;
  pItemId	ALIAS FOR $3;
  pQtyBreak	ALIAS FOR $4;
  pPrice	ALIAS FOR $5;
  pQtyUomId	ALIAS FOR $6;
  pPriceUomId	ALIAS FOR $7;
  _ipsitemid	INTEGER;
  _new		BOOLEAN;
BEGIN

  -- Validation
  IF (SELECT COUNT(item_id)=0 FROM item WHERE (item_id=pItemId)) THEN
    RAISE EXCEPTION 'You must provide a valid Item';
  ELSIF (COALESCE(pQtyBreak,0) < 0) THEN
    RAISE EXCEPTION 'Quantity can not be a negative value';
  ELSIF (COALESCE(pPrice,0) < 0) THEN
    RAISE EXCEPTION 'Price must be a negative value';
  ELSIF ((pQtyUomId IS NOT NULL) AND (SELECT COUNT(item_id)=0 FROM
        (SELECT item_id
         FROM item
         WHERE ((item_id=pItemId)
           AND (item_inv_uom_id=pQtyUomId))
         UNION
         SELECT item_id
         FROM item,itemuomconv,itemuom,uomtype
         WHERE ((item_id=pItemId)
           AND (itemuomconv_item_id=item_id)
           AND (itemuomconv_from_uom_id=pQtyUomId)
           AND (itemuom_itemuomconv_id=itemuomconv_id)
           AND (itemuom_uomtype_id=uomtype_id)
           AND (uomtype_name='Selling'))
         UNION
         SELECT item_id
         FROM item,itemuomconv,itemuom,uomtype
         WHERE ((item_id=pItemId)
           AND (itemuomconv_item_id=item_id)
           AND (itemuomconv_to_uom_id=pQtyUomId)
           AND (itemuom_itemuomconv_id=itemuomconv_id)
           AND (itemuom_uomtype_id=uomtype_id)
           AND (uomtype_name='Selling'))) AS data)) THEN
    RAISE EXCEPTION 'Qty UOM Must be a valid Selling UOM for the Item';
  ELSIF ((pPriceUomId IS NOT NULL) AND (SELECT COUNT(item_id)=0 FROM
        (SELECT item_id
         FROM item
         WHERE ((item_id=pItemId)
           AND (item_inv_uom_id=pPriceUomId))
         UNION
         SELECT item_id
         FROM item,itemuomconv,itemuom,uomtype
         WHERE ((item_id=pItemId)
           AND (itemuomconv_item_id=item_id)
           AND (itemuomconv_from_uom_id=pPriceUomId)
           AND (itemuom_itemuomconv_id=itemuomconv_id)
           AND (itemuom_uomtype_id=uomtype_id)
           AND (uomtype_name='Selling'))
         UNION
         SELECT item_id
         FROM item,itemuomconv,itemuom,uomtype
         WHERE ((item_id=pItemId)
           AND (itemuomconv_item_id=item_id)
           AND (itemuomconv_to_uom_id=pPriceUomId)
           AND (itemuom_itemuomconv_id=itemuomconv_id)
           AND (itemuom_uomtype_id=uomtype_id)
           AND (uomtype_name='Selling'))) AS data)) THEN
    RAISE EXCEPTION 'Price UOM Must be a valid Selling UOM for the Item';
  END IF;

  _new := TRUE;

  IF (pIpsItemId IS NOT NULL) THEN
    SELECT ipsitem_id INTO _ipsitemid
    FROM ipsitem
    WHERE (ipsitem_id=pIpsItemId);

    IF (FOUND) THEN
      _new := FALSE;
    ELSE
      RAISE EXCEPTION 'Pricing Schedule Item not found.';
    END IF;
  ELSE
    SELECT ipsitem_id INTO _ipsitemid
    FROM ipsitem
    WHERE ((ipsitem_ipshead_id	= pIpsheadId)
      AND (ipsitem_item_id 	= pItemId)
      AND (ipsitem_qtybreak 	= pQtyBreak)
      AND (ipsitem_qty_uom_id = COALESCE(pQtyUomId,(SELECT item_inv_uom_id FROM item WHERE item_id = pItemId))) 
      AND (ipsitem_price_uom_id =
           CASE
             WHEN (pQtyUomId = (SELECT item_inv_uom_id FROM item WHERE item_id = pItemId)) THEN
               COALESCE(pPriceUomId,pQtyUomId,(SELECT item_inv_uom_id FROM item WHERE item_id = pItemId))
             ELSE
               COALESCE(pQtyUomId,(SELECT item_inv_uom_id FROM item WHERE item_id = pItemId))
           END));
  END IF;
  
  IF (FOUND) THEN
    _new := false;
  ELSE
    _ipsitemid := nextval('ipsitem_ipsitem_id_seq');
  END IF;
  
  IF (_new) THEN
    INSERT INTO ipsitem (
      ipsitem_id,
      ipsitem_ipshead_id, 
      ipsitem_item_id, 
      ipsitem_qtybreak, 
      ipsitem_price, 
      ipsitem_qty_uom_id, 
      ipsitem_price_uom_id,
      ipsitem_discntprcnt,
      ipsitem_fixedamtdiscount) 
    VALUES (
      _ipsitemid,
      pIpsheadId,
      pItemId,
      pQtyBreak, 
      pPrice,
      COALESCE(pQtyUomId,(SELECT item_inv_uom_id FROM item WHERE item_id = pItemId)), 
      CASE
        WHEN (pQtyUomId = (SELECT item_inv_uom_id FROM item WHERE item_id = pItemId)) THEN
          COALESCE(pPriceUomId,pQtyUomId,(SELECT item_inv_uom_id FROM item WHERE item_id = pItemId))
        ELSE
          COALESCE(pQtyUomId,(SELECT item_inv_uom_id FROM item WHERE item_id = pItemId))
      END,
      0.0,
      0.0);
  ELSE 
    UPDATE ipsitem SET 
      ipsitem_qtybreak = pQtyBreak, 
      ipsitem_price = pPrice, 
      ipsitem_qty_uom_id = COALESCE(pQtyUomId,(SELECT item_inv_uom_id FROM item WHERE item_id = pItemId)), 
      ipsitem_price_uom_id =
      CASE
        WHEN (pQtyUomId = (SELECT item_inv_uom_id FROM item WHERE item_id = pItemId)) THEN
          COALESCE(pPriceUomId,pQtyUomId,(SELECT item_inv_uom_id FROM item WHERE item_id = pItemId))
        ELSE
          COALESCE(pQtyUomId,(SELECT item_inv_uom_id FROM item WHERE item_id = pItemId))
      END
    WHERE (ipsitem_id=_ipsitemid);
   END IF;

   RETURN _ipsitemid;

END;
$_$;


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

--
-- TOC entry 2167 (class 1255 OID 146567016)
-- Dependencies: 4536 8
-- Name: saveipsitem(integer, integer, integer, numeric, numeric, integer, integer, numeric, numeric, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION saveipsitem(pipsitemid integer, pipsheadid integer, pitemid integer, pqtybreak numeric, pprice numeric, pqtyuomid integer, ppriceuomid integer, ppercent numeric, pfixedamt numeric, ptype 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
  _ipsitemid	INTEGER;
  _new		BOOLEAN;
BEGIN

  -- Validation
  IF (SELECT COUNT(item_id)=0 FROM item WHERE (item_id=pItemId)) THEN
    RAISE EXCEPTION 'You must provide a valid Item';
  ELSIF (COALESCE(pQtyBreak,0) < 0) THEN
    RAISE EXCEPTION 'Quantity can not be a negative value';
  ELSIF (COALESCE(pPrice,0) < 0) THEN
    RAISE EXCEPTION 'Price must be a negative value';
  ELSIF ((pQtyUomId IS NOT NULL) AND (SELECT COUNT(item_id)=0 FROM
        (SELECT item_id
         FROM item
         WHERE ((item_id=pItemId)
           AND (item_inv_uom_id=pQtyUomId))
         UNION
         SELECT item_id
         FROM item,itemuomconv,itemuom,uomtype
         WHERE ((item_id=pItemId)
           AND (itemuomconv_item_id=item_id)
           AND (itemuomconv_from_uom_id=pQtyUomId)
           AND (itemuom_itemuomconv_id=itemuomconv_id)
           AND (itemuom_uomtype_id=uomtype_id)
           AND (uomtype_name='Selling'))
         UNION
         SELECT item_id
         FROM item,itemuomconv,itemuom,uomtype
         WHERE ((item_id=pItemId)
           AND (itemuomconv_item_id=item_id)
           AND (itemuomconv_to_uom_id=pQtyUomId)
           AND (itemuom_itemuomconv_id=itemuomconv_id)
           AND (itemuom_uomtype_id=uomtype_id)
           AND (uomtype_name='Selling'))) AS data)) THEN
    RAISE EXCEPTION 'Qty UOM Must be a valid Selling UOM for the Item';
  ELSIF ((pPriceUomId IS NOT NULL) AND (SELECT COUNT(item_id)=0 FROM
        (SELECT item_id
         FROM item
         WHERE ((item_id=pItemId)
           AND (item_inv_uom_id=pPriceUomId))
         UNION
         SELECT item_id
         FROM item,itemuomconv,itemuom,uomtype
         WHERE ((item_id=pItemId)
           AND (itemuomconv_item_id=item_id)
           AND (itemuomconv_from_uom_id=pPriceUomId)
           AND (itemuom_itemuomconv_id=itemuomconv_id)
           AND (itemuom_uomtype_id=uomtype_id)
           AND (uomtype_name='Selling'))
         UNION
         SELECT item_id
         FROM item,itemuomconv,itemuom,uomtype
         WHERE ((item_id=pItemId)
           AND (itemuomconv_item_id=item_id)
           AND (itemuomconv_to_uom_id=pPriceUomId)
           AND (itemuom_itemuomconv_id=itemuomconv_id)
           AND (itemuom_uomtype_id=uomtype_id)
           AND (uomtype_name='Selling'))) AS data)) THEN
    RAISE EXCEPTION 'Price UOM Must be a valid Selling UOM for the Item';
  END IF;

  _new := TRUE;

  IF (pIpsItemId IS NOT NULL) THEN
    SELECT ipsitem_id INTO _ipsitemid
    FROM ipsiteminfo
    WHERE (ipsitem_id=pIpsItemId);

    IF (FOUND) THEN
      _new := FALSE;
    ELSE
      RAISE EXCEPTION 'Pricing Schedule Item not found.';
    END IF;
  ELSE
    SELECT ipsitem_id INTO _ipsitemid
    FROM ipsiteminfo
    WHERE ((ipsitem_ipshead_id	= pIpsheadId)
      AND (ipsitem_item_id 	= pItemId)
      AND (ipsitem_qtybreak 	= pQtyBreak)
      AND (ipsitem_qty_uom_id = COALESCE(pQtyUomId,(SELECT item_inv_uom_id FROM item WHERE item_id = pItemId))) 
      AND (ipsitem_price_uom_id =
           CASE
             WHEN (pQtyUomId = (SELECT item_inv_uom_id FROM item WHERE item_id = pItemId)) THEN
               COALESCE(pPriceUomId,pQtyUomId,(SELECT item_inv_uom_id FROM item WHERE item_id = pItemId))
             ELSE
               COALESCE(pQtyUomId,(SELECT item_inv_uom_id FROM item WHERE item_id = pItemId))
           END));
  END IF;
  
  IF (FOUND) THEN
    _new := false;
  END IF;
  
  IF (_new) THEN
    INSERT INTO ipsiteminfo (
      ipsitem_ipshead_id, 
      ipsitem_item_id, 
      ipsitem_qtybreak, 
      ipsitem_price, 
      ipsitem_qty_uom_id, 
      ipsitem_price_uom_id,
      ipsitem_discntprcnt,
      ipsitem_fixedamtdiscount,
      ipsitem_type) 
    VALUES (
      pIpsheadId,
      pItemId,
      pQtyBreak, 
      pPrice,
      COALESCE(pQtyUomId,(SELECT item_inv_uom_id FROM item WHERE item_id = pItemId)), 
      CASE
        WHEN (pQtyUomId = (SELECT item_inv_uom_id FROM item WHERE item_id = pItemId)) THEN
          COALESCE(pPriceUomId,pQtyUomId,(SELECT item_inv_uom_id FROM item WHERE item_id = pItemId))
        ELSE
          COALESCE(pQtyUomId,(SELECT item_inv_uom_id FROM item WHERE item_id = pItemId))
      END,
      pPercent,
      pFixedAmt,
      pType)
    RETURNING ipsitem_id INTO _ipsitemid;
  ELSE 
    UPDATE ipsiteminfo SET 
      ipsitem_qtybreak = pQtyBreak, 
      ipsitem_price = pPrice, 
      ipsitem_qty_uom_id = COALESCE(pQtyUomId,(SELECT item_inv_uom_id FROM item WHERE item_id = pItemId)), 
      ipsitem_price_uom_id =
      CASE
        WHEN (pQtyUomId = (SELECT item_inv_uom_id FROM item WHERE item_id = pItemId)) THEN
          COALESCE(pPriceUomId,pQtyUomId,(SELECT item_inv_uom_id FROM item WHERE item_id = pItemId))
        ELSE
          COALESCE(pQtyUomId,(SELECT item_inv_uom_id FROM item WHERE item_id = pItemId))
      END,
      ipsitem_discntprcnt=pPercent,
      ipsitem_fixedamtdiscount=pFixedAmt,
      ipsitem_type=pType
    WHERE (ipsitem_id=_ipsitemid);
   END IF;

   RETURN _ipsitemid;

END;
$$;


ALTER FUNCTION public.saveipsitem(pipsitemid integer, pipsheadid integer, pitemid integer, pqtybreak numeric, pprice numeric, pqtyuomid integer, ppriceuomid integer, ppercent numeric, pfixedamt numeric, ptype text) OWNER TO admin;

--
-- TOC entry 2168 (class 1255 OID 146567017)
-- Dependencies: 4536 8
-- Name: saveipsprodcat(integer, integer, integer, numeric, numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION saveipsprodcat(integer, integer, integer, numeric, numeric) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
  pIpsProdcatId	ALIAS FOR $1;
  pIpsHeadId	ALIAS FOR $2;
  pProdCatId	ALIAS FOR $3;
  pQtyBreak	ALIAS FOR $4;
  pDiscount	ALIAS FOR $5;
  _ipsprodcatid	INTEGER;
  _new		BOOLEAN;
  
BEGIN

  -- Validation
  IF (SELECT COUNT(*)=0 FROM prodcat WHERE (prodcat_id=pProdcatId)) THEN
    RAISE EXCEPTION 'You must provide a valid Product Category';
  ELSIF (COALESCE(pQtyBreak,0) < 0) THEN
    RAISE EXCEPTION 'Quantity can not be a negative value';
  ELSIF (COALESCE(pDiscount,0) < 0) THEN
    RAISE EXCEPTION 'Discount must be a negative value';
  END IF;
    
  _new := TRUE;

  IF (pIpsProdcatId IS NOT NULL) THEN
    SELECT ipsprodcat_id INTO _ipsprodcatid
    FROM ipsprodcat
    WHERE (ipsprodcat_id=pIpsprodcatId);

    IF (FOUND) THEN
      _new := FALSE;
    ELSE
      RAISE EXCEPTION 'Pricing Schedule Product Category not found';
    END IF;
  ELSE
    SELECT ipsprodcat_id INTO _ipsprodcatid
    FROM ipsprodcat
    WHERE ((ipsprodcat_ipshead_id=pIpsheadId)
      AND (ipsprodcat_prodcat_id=pProdcatId)
      AND (ipsprodcat_qtybreak=pQtyBreak));

    IF (FOUND) THEN
      _new := false;
    ELSE
      _ipsprodcatid := nextval('ipsprodcat_ipsprodcat_id_seq');
    END IF;
  END IF;
  
  IF (_new) THEN
    INSERT INTO ipsprodcat (
      ipsprodcat_id,
      ipsprodcat_ipshead_id, 
      ipsprodcat_prodcat_id, 
      ipsprodcat_qtybreak, 
      ipsprodcat_discntprcnt) 
    VALUES (
      _ipsprodcatid,
      pIpsheadId,
      pProdcatId,
      pQtyBreak, 
      pDiscount * .01);
  ELSE 
    UPDATE ipsprodcat SET 
      ipsprodcat_qtybreak = pQtyBreak, 
      ipsprodcat_discntprcnt = pDiscount * .01
    WHERE (ipsprodcat_id=_ipsprodcatid);
  END IF;

  RETURN _ipsprodcatid;
END;
$_$;


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

--
-- TOC entry 2169 (class 1255 OID 146567018)
-- Dependencies: 4536 8
-- Name: saveipsprodcat(integer, integer, integer, numeric, numeric, numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION saveipsprodcat(integer, integer, integer, numeric, numeric, numeric) RETURNS integer
    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
  pIpsProdcatId	    ALIAS FOR $1;
  pIpsHeadId	    ALIAS FOR $2;
  pProdCatId	    ALIAS FOR $3;
  pQtyBreak	    ALIAS FOR $4;
  pDiscount	    ALIAS FOR $5;
  pFixedAmtDiscount ALIAS FOR $6;
  _ipsprodcatid	INTEGER;
  _new		BOOLEAN;
  
BEGIN

  -- Validation
  IF (SELECT COUNT(*)=0 FROM prodcat WHERE (prodcat_id=pProdcatId)) THEN
    RAISE EXCEPTION 'You must provide a valid Product Category';
  ELSIF (COALESCE(pQtyBreak,0) < 0) THEN
    RAISE EXCEPTION 'Quantity can not be a negative value';
  ELSIF (COALESCE(pDiscount,0) < 0) THEN
    RAISE EXCEPTION 'Discount must be a negative value';
  END IF;
    
  _new := TRUE;

  IF (pIpsProdcatId IS NOT NULL) THEN
    SELECT ipsprodcat_id INTO _ipsprodcatid
    FROM ipsprodcat
    WHERE (ipsprodcat_id=pIpsprodcatId);

    IF (FOUND) THEN
      _new := FALSE;
    ELSE
      RAISE EXCEPTION 'Pricing Schedule Product Category not found';
    END IF;
  ELSE
    SELECT ipsprodcat_id INTO _ipsprodcatid
    FROM ipsprodcat
    WHERE ((ipsprodcat_ipshead_id=pIpsheadId)
      AND (ipsprodcat_prodcat_id=pProdcatId)
      AND (ipsprodcat_qtybreak=pQtyBreak));

    IF (FOUND) THEN
      _new := false;
    ELSE
      _ipsprodcatid := nextval('ipsprodcat_ipsprodcat_id_seq');
    END IF;
  END IF;
  
  IF (_new) THEN
    INSERT INTO ipsprodcat (
      ipsprodcat_id,
      ipsprodcat_ipshead_id, 
      ipsprodcat_prodcat_id, 
      ipsprodcat_qtybreak, 
      ipsprodcat_discntprcnt,
      ipsprodcat_fixedamtdiscount)  
    VALUES (
      _ipsprodcatid,
      pIpsheadId,
      pProdcatId,
      pQtyBreak, 
      pDiscount * .01,
      pFixedAmtDiscount);
  ELSE 
    UPDATE ipsprodcat SET 
      ipsprodcat_qtybreak = pQtyBreak, 
      ipsprodcat_discntprcnt = pDiscount * .01,
      ipsprodcat_fixedamtdiscount = pFixedAmtDiscount 
    WHERE (ipsprodcat_id=_ipsprodcatid);
  END IF;

  RETURN _ipsprodcatid;
END;
$_$;


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

--
-- TOC entry 2164 (class 1255 OID 146567019)
-- Dependencies: 4536 8
-- Name: saveipsprodcat(integer, integer, integer, numeric, numeric, numeric, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION saveipsprodcat(pipsprodcatid integer, pipsheadid integer, pprodcatid integer, pqtybreak numeric, pdiscount numeric, pfixedamtdiscount numeric, ptype 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
  _ipsitemid	INTEGER;
  _new		BOOLEAN;
  
BEGIN

  -- Validation
  IF (SELECT COUNT(*)=0 FROM prodcat WHERE (prodcat_id=pProdcatId)) THEN
    RAISE EXCEPTION 'You must provide a valid Product Category';
  ELSIF (COALESCE(pQtyBreak,0) < 0) THEN
    RAISE EXCEPTION 'Quantity can not be a negative value';
  ELSIF (COALESCE(pDiscount,0) < 0) THEN
    RAISE EXCEPTION 'Discount must be a negative value';
  END IF;
    
  _new := TRUE;

  IF (pIpsProdcatId IS NOT NULL) THEN
    SELECT ipsitem_id INTO _ipsitemid
    FROM ipsiteminfo
    WHERE (ipsprodcat_id=pIpsprodcatId);

    IF (FOUND) THEN
      _new := FALSE;
    ELSE
      RAISE EXCEPTION 'Pricing Schedule Product Category not found';
    END IF;
  ELSE
    SELECT ipsitem_id INTO _ipsitemid
    FROM ipsiteminfo
    WHERE ((ipsitem_ipshead_id=pIpsheadId)
      AND (ipsitem_prodcat_id=pProdcatId)
      AND (ipsitem_qtybreak=pQtyBreak));

    IF (FOUND) THEN
      _new := false;
    ELSE
      _ipsitemid := nextval('ipsitem_ipsitem_id_seq');
    END IF;
  END IF;
  
  IF (_new) THEN
    INSERT INTO ipsiteminfo (
      ipsitem_id,
      ipsitem_ipshead_id, 
      ipsitem_prodcat_id, 
      ipsitem_qtybreak,
      ipsitem_price, 
      ipsitem_discntprcnt,
      ipsitem_fixedamtdiscount,
      ipsitem_type)  
    VALUES (
      _ipsitemid,
      pIpsheadId,
      pProdcatId,
      pQtyBreak, 
      0.0,
      pDiscount * .01,
      pFixedAmtDiscount,
      pType);
  ELSE 
    UPDATE ipsiteminfo SET 
      ipsitem_qtybreak = pQtyBreak, 
      ipsitem_discntprcnt = pDiscount * .01,
      ipsitem_fixedamtdiscount = pFixedAmtDiscount,
      ipsitem_type = pType 
    WHERE (ipsitem_id=_ipsitemid);
  END IF;

  RETURN _ipsitemid;
END;
$$;


ALTER FUNCTION public.saveipsprodcat(pipsprodcatid integer, pipsheadid integer, pprodcatid integer, pqtybreak numeric, pdiscount numeric, pfixedamtdiscount numeric, ptype text) OWNER TO admin;

--
-- TOC entry 2171 (class 1255 OID 146567020)
-- Dependencies: 4536 8
-- Name: saveitemimage(integer, character, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
-- See if a record with this purpose already exists
  SELECT imageass_id INTO _itemimageId
  FROM imageass
  WHERE ( (imageass_source='I')
    AND   (imageass_source_id=pItemid)
    AND   (imageass_purpose=pPurpose) );

  IF (FOUND) THEN
    UPDATE imageass SET imageass_image_id=pImageId
    WHERE (imageass_id=_itemimageId);
  ELSE
    _itemimageId := NEXTVAL('imageass_imageass_id_seq');
    INSERT INTO imageass VALUES (_itemimageId,pItemid,'I',pImageid,pPurpose);
  END IF;
  
  RETURN _itemimageId;
END;
$_$;


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

--
-- TOC entry 2172 (class 1255 OID 146567021)
-- Dependencies: 4536 8
-- Name: saveitemuomconv(integer, integer, numeric, integer, numeric, boolean, integer[]); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION saveitemuomconv(integer, integer, numeric, integer, numeric, boolean, 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;
  pFromUomId ALIAS FOR $2;
  pFromValue ALIAS FOR $3;
  pToUomId ALIAS FOR $4;
  pToValue ALIAS FOR $5;
  pFractional ALIAS FOR $6;
  pUomTypes ALIAS FOR $7;
  _p RECORD;
  _fromUomId INTEGER;
  _fromValue NUMERIC;
  _toUomId INTEGER;
  _toValue NUMERIC;
  _fractional BOOLEAN;
  _seq INTEGER;
  _i INTEGER;
  _uomtype TEXT;

BEGIN
-- Make sure we have some itemtypes
  IF (pUomTypes IS NULL) OR (ARRAY_UPPER(pUomTypes,1) = 0) THEN
	RAISE EXCEPTION 'You must include at least one item type.';
  END IF;

-- If this is a global UOM, over-ride with global data.
  SELECT * INTO _p
  FROM uomconv
  WHERE ((((uomconv_from_uom_id=pFromUomId)
  AND (uomconv_to_uom_id=pToUomId))
  OR ((uomconv_from_uom_id=pToUomId)
  AND (uomconv_to_uom_id=pFromUomId))));

  IF (FOUND) THEN
    _fromUomId := _p.uomconv_from_uom_id;
    _toUomId := _p.uomconv_to_uom_id;
    _fromValue := _p.uomconv_from_value;
    _toValue := _p.uomconv_to_value;
    _fractional := _p.uomconv_fractional;
    RAISE NOTICE 'Defaulted to global Unit of Measure conversion ratios.';
  ELSE
    _fromUomId := pFromUomId;
    _fromValue := pFromValue;
    _toUomId := pToUomId;
    _toValue := pToValue;
    _fractional := pFractional;
  END IF;

-- See if an item conversion exists going the other way
  SELECT f.uom_name AS f_uom, t.uom_name as t_uom INTO _p
  FROM itemuomconv,uom f, uom t
  WHERE ((itemuomconv_item_id=pItemId)
  AND (itemuomconv_from_uom_id=_toUomId)
  AND (itemuomconv_to_uom_id=_fromUomId)
  AND (f.uom_id=itemuomconv_from_uom_id)
  AND (t.uom_id=itemuomconv_to_uom_id));
  IF (FOUND) THEN
    RAISE EXCEPTION 'Unit of measure conversion already exists going from % to %.',_p.f_uom,_p.t_uom;
  END IF;

-- See if an item conversion record exists
  SELECT * INTO _p
  FROM itemuomconv
  WHERE ((itemuomconv_item_id=pItemId)
  AND (itemuomconv_from_uom_id=_fromUomId)
  AND (itemuomconv_to_uom_id=_toUomId));

-- Update if found
  IF (FOUND) THEN
    UPDATE itemuomconv SET
      itemuomconv_from_value=_fromValue,
      itemuomconv_to_value=_toValue,
      itemuomconv_fractional=_fractional
    WHERE (itemuomconv_id=_p.itemuomconv_id);
    _seq := _p.itemuomconv_id;
    
    --Delete old type list
    DELETE FROM itemuom WHERE itemuom_itemuomconv_id=_p.itemuomconv_id;
  ELSE
  
-- Otherwise create a new one
    SELECT NEXTVAL('itemuomconv_itemuomconv_id_seq') INTO _seq;
    INSERT INTO itemuomconv VALUES
      (_seq, pItemId,_fromUomId,_fromValue,_toUomId,_toValue,_fractional);
  END IF;
  
-- Build new type list
  FOR _i IN 1..ARRAY_UPPER(pUomTypes,1)
  LOOP
    SELECT uomtype_name INTO _uomtype
    FROM itemuomconv, itemuom, uomtype
    WHERE ((itemuom_uomtype_id=uomtype_id)
    AND (itemuomconv_id=itemuom_itemuomconv_id)
    AND (itemuomconv_item_id=pItemId)
    AND (uomtype_name != 'Selling')
    AND (itemuom_uomtype_id=pUomTypes[_i]));
    IF (FOUND) THEN
      RAISE EXCEPTION 'Unit of Measure Type % is already used on this item',_uomtype;
    ELSE
      INSERT INTO itemuom (itemuom_itemuomconv_id,itemuom_uomtype_id)
      VALUES (_seq,pUomTypes[_i]);
    END IF;
  END LOOP;
  
  RETURN _seq;
END;
$_$;


ALTER FUNCTION public.saveitemuomconv(integer, integer, numeric, integer, numeric, boolean, integer[]) OWNER TO admin;

--
-- TOC entry 2173 (class 1255 OID 146567022)
-- Dependencies: 4536 8
-- Name: savemetasql(text, text, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION savemetasql(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.
BEGIN
  RETURN saveMetasql($1, $2, $3, $4, true, NULL, 0);
END;
$_$;


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

--
-- TOC entry 2174 (class 1255 OID 146567023)
-- Dependencies: 4536 8
-- Name: savemetasql(text, text, text, text, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 2175 (class 1255 OID 146567024)
-- Dependencies: 4536 8
-- Name: savemetasql(text, text, text, text, boolean, text); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 2176 (class 1255 OID 146567025)
-- Dependencies: 4536 8
-- Name: savemetasql(text, text, text, text, boolean, text, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION savemetasql(text, text, text, text, boolean, 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
  pGroup	ALIAS FOR $1;
  pName 	ALIAS FOR $2;
  pNotes	ALIAS FOR $3;
  pQuery	ALIAS FOR $4;
  pSystem       ALIAS FOR $5;
  pSchema       ALIAS FOR $6;
  pGrade        ALIAS FOR $7;
  _metasqlid	INTEGER;
  _debug        BOOL    := false;
  _grade        INTEGER;
  _insertstr    TEXT;
  _table        TEXT;
  
BEGIN

  --See if Query already exists
  SELECT metasql_id INTO _metasqlid
  FROM metasql
  WHERE ((metasql_group=pGroup)
     AND (metasql_name=pName)
     AND (metasql_grade=pGrade));

  IF (FOUND) THEN
    IF (_debug) THEN RAISE NOTICE 'update metasql'; END IF;
    UPDATE metasql SET
      metasql_group=pGroup,
      metasql_name=pName,
      metasql_notes=pNotes,
      metasql_query=pQuery
    WHERE (metasql_id=_metasqlid);
  ELSE
    IF (COALESCE(pSchema, 'public') = 'public' OR
        TRIM(pSchema) = '') THEN
      _table := 'metasql';
    ELSE
      _table := pSchema || '.pkgmetasql';
    END IF;

    IF (pGrade IS NULL) THEN
      SELECT MAX(metasql_grade) + 1 INTO _grade
      FROM metasql
      WHERE ((metasql_group=pGroup)
         AND (metasql_name=pName));
    ELSE
      _grade := pGrade;
    END IF;

    _insertstr := 'INSERT INTO ' || _table ||
                  ' (metasql_group, metasql_name, metasql_notes, ' ||
                  '  metasql_query, metasql_grade) VALUES (' ||
                  COALESCE(quote_literal(pGroup),'NULL') || ',' || COALESCE(quote_literal(pName), 'NULL') || ',' ||
                  COALESCE(quote_literal(pNotes), 'NULL') || ',' || COALESCE(quote_literal(pQuery), 'NULL') ||',' ||
                  COALESCE(quote_literal(_grade), 'NULL') || ') RETURNING metasql_id;' ;

    IF (_debug) THEN RAISE NOTICE '%', _insertstr; END IF;
    EXECUTE _insertstr INTO _metasqlid;
  END IF;
 
  RETURN _metasqlid;
END;
$_$;


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

--
-- TOC entry 2177 (class 1255 OID 146567026)
-- Dependencies: 4536 8
-- Name: scraps(text); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  IF (pTransType IN ('SI', 'SM', 'EX')) THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;

END;
$_$;


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

--
-- TOC entry 2178 (class 1255 OID 146567027)
-- Dependencies: 4536 8
-- Name: scrapwomaterial(integer, numeric); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 2179 (class 1255 OID 146567028)
-- Dependencies: 4536 8
-- Name: scrapwomaterial(integer, numeric, timestamp with time zone); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION scrapwomaterial(integer, numeric, timestamp with time zone) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pWomatlid	ALIAS FOR $1;
  pQty		ALIAS FOR $2;
  pGlDistTS     ALIAS FOR $3;
  _costmethod         	CHAR(1);
  _scrapValue		NUMERIC;
  _r                   	RECORD;

BEGIN
  -- Validate
  IF (pQty <= 0) THEN
    RAISE EXCEPTION 'Scrap quantity must be a positive number';
  ELSIF ( ( SELECT (womatl_qtyiss < pQty)
	     FROM womatl
	     WHERE (womatl_id=pWomatlid) ) ) THEN
    RAISE EXCEPTION 'You may not scrap more material than has been issued';
  END IF;

  -- Get the wip G/L account
  SELECT costcat_wip_accnt_id
    INTO _r
    FROM womatl, wo, itemsite, costcat
   WHERE((womatl_wo_id=wo_id)
     AND (wo_itemsite_id=itemsite_id)
     AND (itemsite_costcat_id=costcat_id)
     AND (womatl_id=pWomatlid));

  -- Calculate scrap value
  SELECT itemsite_costmethod INTO _costmethod
  FROM womatl
    JOIN itemsite ON (womatl_itemsite_id=itemsite_id)
  WHERE (womatl_id=pWomatlid);

  IF (_costmethod = 'S') THEN
    SELECT ROUND((stdCost(itemsite_item_id) * itemuomtouom(itemsite_item_id, womatl_uom_id, NULL, pQty)),2)
    INTO _scrapValue
    FROM womatl
      JOIN itemsite ON (womatl_itemsite_id=itemsite_id)
    WHERE (womatl_id=pWomatlid);
     
  ELSIF (_costmethod = 'A') THEN
    SELECT ROUND((SUM(invhist_invqty * invhist_unitcost)-womatl_scrapvalue)/
            (CASE WHEN (SUM(invhist_invqty)-itemuomtouom(itemsite_item_id, womatl_uom_id, NULL, womatl_qtywipscrap) = 0) THEN
              1
            ELSE
              SUM(invhist_invqty)-itemuomtouom(itemsite_item_id, womatl_uom_id, NULL, womatl_qtywipscrap)
            END),2) * itemuomtouom(itemsite_item_id, womatl_uom_id, NULL, pQty)
      INTO _scrapValue
    FROM womatl
        JOIN womatlpost ON (womatl_id=womatlpost_womatl_id)
        JOIN invhist ON (womatlpost_invhist_id=invhist_id)
        JOIN itemsite ON (womatl_itemsite_id=itemsite_id)
    WHERE (womatl_id=pWomatlid)
    GROUP BY itemsite_item_id,womatl_uom_id,womatl_qtywipscrap,womatl_scrapvalue;
  ELSE
    RAISE EXCEPTION 'Cost method not supported to scrap this item';
  END IF;

  --  Distribute to G/L
  PERFORM insertGLTransaction( 'W/O', 'WO', formatWoNumber(womatl_wo_id),
		 ('Scrap ' || item_number || ' from Work Order'),
		 getPrjAccntId(wo_prj_id, _r.costcat_wip_accnt_id), getPrjAccntId(wo_prj_id, costcat_mfgscrap_accnt_id), -1,
		 _scrapValue, date(pGlDistTS) )
  FROM wo, womatl, itemsite, item, costcat
  WHERE ( (wo_id=womatl_wo_id)
   AND (womatl_itemsite_id=itemsite_id)
   AND (itemsite_item_id=item_id)
   AND (itemsite_costcat_id=costcat_id)
   AND (womatl_id=pWomatlid) );

  UPDATE womatl
  SET womatl_qtywipscrap=(womatl_qtywipscrap + pQty),
    womatl_scrapvalue = womatl_scrapvalue + _scrapValue,
    womatl_qtyiss=(womatl_qtyiss - pQty)
  WHERE (womatl_id=pWomatlid);

  UPDATE wo
  SET wo_wipvalue = wo_wipvalue-_scrapValue,
    wo_postedvalue = wo_postedvalue-_scrapValue
  FROM womatl
  WHERE ((womatl_id=pWomatlid)
   AND (wo_id=womatl_wo_id));

  RETURN pWomatlid;

END;
$_$;


ALTER FUNCTION public.scrapwomaterial(integer, numeric, timestamp with time zone) OWNER TO admin;

--
-- TOC entry 2181 (class 1255 OID 146567029)
-- Dependencies: 4536 8
-- Name: selectbalanceforbilling(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION selectbalanceforbilling(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
  pSoheadid ALIAS FOR $1;
  _returnval	BOOLEAN := TRUE;
  _doSelect BOOLEAN;
  _result INTEGER;
  _soitem RECORD;

BEGIN

  FOR _soitem IN
    -- Get the shipments for this SO.  Kits are not shipped
    SELECT cust_partialship, coitem_id,
           coitem_linenumber, 'NOTK' AS item_type,
           SUM(shipitem_qty) AS qty,
           ( (SUM(shipitem_qty) >= (coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned + SUM(shipitem_qty))) OR
             (NOT cust_partialship) ) AS toclose
    FROM cohead JOIN custinfo ON (cust_id=cohead_cust_id)
                JOIN coitem ON (coitem_cohead_id=cohead_id)
                JOIN shipitem ON ( (shipitem_orderitem_id=coitem_id) AND (NOT shipitem_invoiced) )
                JOIN shiphead ON ( (shiphead_id=shipitem_shiphead_id) AND (shiphead_order_type='SO') AND (shiphead_shipped) )
    WHERE (cohead_id=pSoheadid)
    GROUP BY cust_partialship, coitem_id, item_type,
             coitem_linenumber, coitem_qtyord,
             coitem_qtyshipped, coitem_qtyreturned
    UNION
    -- Get the Kits for this SO
    SELECT cust_partialship, coitem_id,
           coitem_linenumber, 'K' AS item_type,
           coitem_qtyord AS qty,
           TRUE AS toclose
    FROM cohead JOIN custinfo ON (cust_id=cohead_cust_id)
                JOIN coitem ON (coitem_cohead_id=cohead_id AND coitem_status='O')
                JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
                JOIN item ON ( (item_id=itemsite_item_id) AND (item_type='K') )
    WHERE (cohead_id=pSoheadid)
  LOOP

    _doSelect := true;
    IF(_soitem.item_type = 'K') THEN
      -- see if all the sub items are shipped
      SELECT coitem_id
        INTO _result
        FROM coitem
       WHERE((coitem_cohead_id=pSoheadid)
         AND (coitem_linenumber=_soitem.coitem_linenumber)
         AND (coitem_subnumber > 0)
         AND ((coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) > 0))
       LIMIT 1;
      IF( FOUND ) THEN
        _doSelect := false;
      END IF;
    END IF;

    IF (_doSelect) THEN
      -- do as much as we can but still report errors if they occur
      IF (selectForBilling(_soitem.coitem_id, _soitem.qty, _soitem.toclose) < 0) THEN
        _returnval := FALSE;
      END IF;
    END IF;

  END LOOP;

  RETURN _returnval;

END;
$_$;


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

--
-- TOC entry 2182 (class 1255 OID 146567030)
-- Dependencies: 4536 8
-- Name: selectdiscountitemsforpayment(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION selectdiscountitemsforpayment(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
  pVendid ALIAS FOR $1;
  pBankaccntid ALIAS FOR $2;
  _currid INTEGER;
  _r RECORD;

BEGIN

  SELECT bankaccnt_curr_id INTO _currid
  FROM bankaccnt
  WHERE (bankaccnt_id=pBankaccntid);

  FOR _r IN SELECT apopen_id
              FROM apopen, terms
             WHERE((CURRENT_DATE <= determineDiscountDate(apopen_terms_id, apopen_docdate))
               AND (terms_discprcnt > 0.0)
               AND (apopen_terms_id=terms_id)
               AND (apopen_open)
               AND (apopen_status = 'O')
               AND (apopen_doctype IN ('V', 'D'))
               AND (apopen_vend_id=pVendid)
               AND (apopen_curr_id=_currid) ) LOOP
    PERFORM selectPayment(_r.apopen_id, pBankaccntid);
  END LOOP;

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 2183 (class 1255 OID 146567031)
-- Dependencies: 4536 8
-- Name: selectdueitemsforpayment(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION selectdueitemsforpayment(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
  pVendid ALIAS FOR $1;
  pBankaccntid ALIAS FOR $2;
  _currid INTEGER;

BEGIN

  SELECT bankaccnt_curr_id INTO _currid
  FROM bankaccnt
  WHERE (bankaccnt_id=pBankaccntid);

  PERFORM selectPayment(apopen_id, pBankaccntid)
     FROM apopen
    WHERE((apopen_open)
      AND (apopen_vend_id=pVendid)
      AND (apopen_duedate <= CURRENT_DATE)
      AND (apopen_status = 'O')
      AND (apopen_doctype IN ('V', 'D'))
      AND (apopen_curr_id=_currid) );

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 2184 (class 1255 OID 146567032)
-- Dependencies: 4536 8
-- Name: selectforbilling(integer, numeric, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION selectforbilling(integer, numeric, boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pSoitemid	ALIAS FOR $1;
  pQty	ALIAS FOR $2;
  pClose	ALIAS FOR $3;
  _itemid	INTEGER := NULL;
  _taxzoneid	INTEGER := NULL;
  _taxid	INTEGER := NULL;
  _taxtypeid	INTEGER := NULL;

BEGIN
  SELECT cobmisc_taxzone_id,  item_id, coitem_taxtype_id
  INTO _taxzoneid,  _itemid, _taxtypeid
  FROM cobmisc, coitem, itemsite, item
  WHERE ((cobmisc_cohead_id = coitem_cohead_id)
  AND   (NOT cobmisc_posted)
  AND   (coitem_itemsite_id = itemsite_id)
  AND   (itemsite_item_id = item_id)
  AND   (coitem_id = pSoitemid) )
  LIMIT 1;

   RETURN selectforbilling(pSoitemid, pQty, pClose, _taxtypeid);
END;
$_$;


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

--
-- TOC entry 2185 (class 1255 OID 146567033)
-- Dependencies: 4536 8
-- Name: selectforbilling(integer, numeric, boolean, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION selectforbilling(integer, numeric, boolean, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pSoitemid	ALIAS FOR $1;
  pQty		ALIAS FOR $2;
  pClose	ALIAS FOR $3;
  ptaxtypeid	ALIAS FOR $4;
  _cobillid INTEGER;
  _r RECORD;

BEGIN

-- Get some information
  SELECT cobmisc_id, cobmisc_taxzone_id, coitem_id, coitem_price,
    coitem_price_invuomratio AS invpricerat, coitem_qty_invuomratio, item_id
  INTO _r
  FROM cobmisc, coitem, itemsite, item, site()
  WHERE ((cobmisc_cohead_id = coitem_cohead_id)
  AND   (NOT cobmisc_posted)
  AND   (coitem_itemsite_id = itemsite_id)
  AND   (itemsite_item_id = item_id)
  AND   (coitem_id = pSoitemid)
  AND   (itemsite_warehous_id = warehous_id) )
  LIMIT 1;

-- check to make sure the qty to bill for is not less than
-- the total un-invoiced shipped amount
  IF ((SELECT (pQty < SUM(shipitem_qty))
       FROM shipitem, shiphead, coitem
       WHERE ( (shipitem_shiphead_id=shiphead_id)
       AND (shiphead_order_type='SO')
       AND (shiphead_order_id=coitem_cohead_id)
       AND (shipitem_orderitem_id=coitem_id)
       AND (shiphead_shipped)
       AND (NOT shipitem_invoiced)
       AND (coitem_id=pSoitemid) ) ) ) THEN
    RETURN -1;
  END IF;

  SELECT cobill_id INTO _cobillid
  FROM cobill, cobmisc, coitem
  WHERE ((cobill_cobmisc_id = cobmisc_id)
  AND (cobmisc_cohead_id = coitem_cohead_id)
  AND (cobill_coitem_id = coitem_id)
  AND (NOT cobmisc_posted)
  AND (coitem_id = pSoitemid));

  IF (FOUND) THEN
    UPDATE cobill
    SET cobill_selectdate = CURRENT_DATE,
        cobill_select_username = getEffectiveXtUser(),
        cobill_qty = pQty,
        cobill_toclose = pClose,
	cobill_taxtype_id = ptaxtypeid
    WHERE (cobill_id=_cobillid);

  ELSE
    SELECT NEXTVAL('cobill_cobill_id_seq') INTO _cobillid;
    INSERT INTO cobill
    (cobill_id, cobill_coitem_id, cobill_cobmisc_id,
     cobill_selectdate, cobill_select_username,
     cobill_qty, cobill_toclose,
     cobill_taxtype_id)
    VALUES
    (_cobillid, _r.coitem_id, _r.cobmisc_id,
      CURRENT_DATE, getEffectiveXtUser(),
      pQty, pClose,
      ptaxtypeid);
  END IF;

  RETURN _cobillid;

END;
$_$;


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

--
-- TOC entry 2186 (class 1255 OID 146567034)
-- Dependencies: 4536 8
-- Name: selectpayment(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION selectpayment(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
  pApopenid ALIAS FOR $1;
  pBankaccntid ALIAS FOR $2;
  _p RECORD;
  _apselectid INTEGER;
  _amount NUMERIC;
  _discount NUMERIC;
BEGIN

  SELECT apopen_amount, apopen_paid,
         apopen_doctype, apopen_docdate,
         apopen_curr_id,
         apopen_amount - apopen_paid - apCheckPending(apopen_id) AS balance,
         noNeg(COALESCE(apopen_discountable_amount, 0) *
               CASE WHEN (CURRENT_DATE <= determineDiscountDate(apopen_terms_id, apopen_docdate)) THEN terms_discprcnt
                    ELSE 0.0 END - discount_applied) AS discount_available
    INTO _p
    FROM apopen LEFT OUTER JOIN terms ON (apopen_terms_id=terms_id),
         (SELECT COALESCE(SUM(apapply_amount),0) AS discount_applied
            FROM apapply, apopen
           WHERE((apapply_target_apopen_id=pApopenid)
             AND (apapply_source_apopen_id=apopen_id)
             AND (apopen_discount)) ) AS data
   WHERE(apopen_id=pApopenid);
  IF(NOT FOUND OR (NOT _p.apopen_doctype IN ('V','D','C'))) THEN
    RETURN -1;
  END IF;

  _discount := round(_p.discount_available, 2);
  _amount := noNeg(round(_p.balance, 2) - _discount);

  IF (round(_p.balance,2) < (_discount + _amount)) THEN
    RETURN -2;
  END IF;

  IF (_amount > 0) THEN
    SELECT apselect_id INTO _apselectid
    FROM apselect
    WHERE (apselect_apopen_id=pApopenid);

    IF (FOUND) THEN
      UPDATE apselect
         SET apselect_amount=_amount,
             apselect_discount=_discount,
             apselect_curr_id = _p.apopen_curr_id
       WHERE(apselect_id=_apselectid);
    ELSE
      SELECT NEXTVAL('apselect_apselect_id_seq') INTO _apselectid;

      INSERT INTO apselect
      ( apselect_id, apselect_apopen_id,
        apselect_amount, apselect_discount,
        apselect_bankaccnt_id,
        apselect_curr_id, apselect_date )
      VALUES
      ( _apselectid, pApopenid,
        _amount, _discount,
        pBankaccntid,
        _p.apopen_curr_id, _p.apopen_docdate );
    END IF;
  ELSE
    _apselectid := 0;
  END IF;
  
  RETURN _apselectid;

END;
$_$;


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

--
-- TOC entry 2187 (class 1255 OID 146567035)
-- Dependencies: 4536 8
