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

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

  UPDATE aropen
  SET aropen_posted=TRUE
  WHERE (NOT aropen_posted);

  RETURN TRUE;

END;
$$;


ALTER FUNCTION public.postaropenitems() OWNER TO admin;

--
-- TOC entry 1986 (class 1255 OID 146566788)
-- Dependencies: 4536 8
-- Name: postbankadjustment(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postbankadjustment(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
  pBankadjid ALIAS FOR $1;
  _sequence INTEGER;
  _r RECORD;

BEGIN

--  Post the G/L transaction
  SELECT insertGLTransaction( fetchJournalNumber('GL-MISC'), 'G/L', 'AD',
                              bankadj_docnumber, (bankadjtype_name || '-' || bankadj_notes),
                              bankadjtype_accnt_id, bankaccnt_accnt_id, bankadj_id,
                              round(currToBase(bankaccnt_curr_id,
                                         CASE WHEN(bankadjtype_iscredit) THEN
                                             (bankadj_amount * -1)
                                         ELSE bankadj_amount END,
                                         bankadj_date), 2),
                              bankadj_date, TRUE, TRUE ) INTO _sequence
    FROM bankadj, bankaccnt, bankadjtype
   WHERE ( (bankadj_bankaccnt_id=bankaccnt_id)
     AND   (bankadj_bankadjtype_id=bankadjtype_id)
     AND   (NOT bankadj_posted)
     AND   (bankadj_id=pBankadjid) );
  IF ( NOT FOUND ) THEN
    RETURN -1;
  END IF;

  IF (_sequence >= 0) THEN
--  Update the bankadj record with this sequence and mark it posted
    UPDATE bankadj
       SET bankadj_sequence = _sequence,
           bankadj_posted = TRUE
     WHERE bankadj_id=pBankadjid;
  END IF;

  RETURN _sequence;

END;
$_$;


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

--
-- TOC entry 1987 (class 1255 OID 146566789)
-- Dependencies: 4536 8
-- Name: postbankreconciliation(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

-- Delete any bankrecitem records that are not marked as cleared for cleanliness
  DELETE FROM bankrecitem
   WHERE ( (NOT bankrecitem_cleared)
     AND   (bankrecitem_bankrec_id=pBankrecid) );

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

    SELECT postBankAdjustment(_r.bankrecitem_source_id) INTO _sequence;

    IF (_sequence < 0) THEN
      RETURN -10;
    END IF;

    SELECT gltrans_id INTO _gltransid
      FROM gltrans
     WHERE ( (gltrans_sequence=_sequence)
       AND   (gltrans_accnt_id=_accntid) );
    IF ( NOT FOUND ) THEN
      RETURN -11;
    END IF;

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

  END LOOP;

-- Mark all the gltrans items that have been cleared as reconciled.
  UPDATE gltrans
     SET gltrans_rec = TRUE
   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 reconciled.
  UPDATE sltrans
     SET sltrans_rec = TRUE
   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 posted
  UPDATE bankrec SET 
    bankrec_posted = TRUE,
    bankrec_postdate = now()
   WHERE (bankrec_id=pBankrecid);

  RETURN pBankrecid;
END;
$_$;


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

--
-- TOC entry 1988 (class 1255 OID 146566790)
-- Dependencies: 4536 8
-- Name: postbillingselection(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  RAISE NOTICE 'postBillingselection(integer) has been deprecated.  Please use createInvoice(integer).';
  RETURN createInvoice(pCobmiscid);

END;
$_$;


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

--
-- TOC entry 1989 (class 1255 OID 146566791)
-- Dependencies: 4536 8
-- Name: postbillingselectionconsolidated(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  RAISE NOTICE 'postBillingselectionConsolidated(integer) has been deprecated.  Please use createInvoiceConsolidated(integer).';
  RETURN createInvoiceConsolidated(pCustid);

END;
$_$;


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

--
-- TOC entry 1990 (class 1255 OID 146566792)
-- Dependencies: 4536 8
-- Name: postbillingselections(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postbillingselections() RETURNS integer
    LANGUAGE plpgsql
    AS $$
-- 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 'postBillingselections() has been deprecated.  Please use createInvoices().';
  RETURN createInvoices();
  
END;
$$;


ALTER FUNCTION public.postbillingselections() OWNER TO admin;

--
-- TOC entry 1991 (class 1255 OID 146566793)
-- Dependencies: 4536 8
-- Name: postbillingselections(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postbillingselections(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 'postBillingselections(int) has been deprecated.  Please use createInvoices(int).';
  RETURN createInvoices($1, false);
END;
$_$;


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

--
-- TOC entry 1651 (class 1255 OID 146566794)
-- Dependencies: 4536 8
-- Name: postbillingselections(integer, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  RAISE NOTICE 'postBillingselections(int,bool) has been deprecated.  Please use createInvoices(int,bool).';
  RETURN createInvoices(pCustTypeId, pConsolidate);
  
END;
$_$;


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

--
-- TOC entry 1711 (class 1255 OID 146566795)
-- Dependencies: 4536 8
-- Name: postcashreceipt(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postcashreceipt(pcashrcptid integer, pjournalnumber 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
  _ccpayid  INTEGER;
  _cctype TEXT;
  _p RECORD;
  _r RECORD;
  _t RECORD;
  _v RECORD;
  _postToAR NUMERIC;
  _postToMisc NUMERIC;
  _postToCM NUMERIC;
  _posted_base NUMERIC := 0;
  _posted NUMERIC := 0;
  _sequence INTEGER;
  _aropenid INTEGER;
  _arMemoNumber TEXT;
  _arAccntid INTEGER;
  _closed BOOLEAN;
  _debitAccntid INTEGER;
  _exchGain NUMERIC;
  _comment TEXT;
  _predist BOOLEAN;
  _check INTEGER;

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 LEFT OUTER JOIN accnt ON (accnt_id=findARAccount(cashrcpt_cust_id))
    WHERE ( (findARAccount(cashrcpt_cust_id)=0 OR accnt_id > 0) -- G/L interface might be disabled
     AND (cashrcpt_id=pCashrcptid) );
    IF (NOT FOUND) THEN
      RETURN -5;
    END IF;
  END IF;

  SELECT cashrcpt_cust_id, (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,
         COALESCE(cashrcpt_applydate, cashrcpt_distdate) AS applydate,
         cashrcpt_curr_id, cashrcpt_curr_rate, cashrcpt_posted, cashrcpt_void INTO _p
  FROM cashrcpt LEFT OUTER JOIN custinfo ON (cashrcpt_cust_id=cust_id)
                LEFT OUTER JOIN accnt ON (accnt_id=findPrepaidAccount(cashrcpt_cust_id))
  WHERE ( (findPrepaidAccount(cashrcpt_cust_id)=0 OR accnt_id > 0) -- G/L interface might be disabled
     AND (cashrcpt_id=pCashrcptid) );
  IF (NOT FOUND) THEN
    RETURN -7;
  END IF;

  IF (COALESCE(_p.cashrcpt_distdate > _p.applydate, false)) THEN
    RAISE EXCEPTION 'Cannot post cashrcpt % because application date is before distribution date.', _p.cashrcpt_docnumber;
  END IF;

  IF (COALESCE(_p.cashrcpt_posted, false)) THEN
    RAISE EXCEPTION 'Cannot post cashrcpt % because the document has already been posted.', _p.cashrcpt_docnumber;
  END IF;

  IF (COALESCE(_p.cashrcpt_void, false)) THEN
    RAISE EXCEPTION 'Cannot post cashrcpt % because the document has been voided.', _p.cashrcpt_docnumber;
  END IF;

  _predist := COALESCE(_p.cashrcpt_distdate < _p.applydate, false);

  IF (_p.cashrcpt_fundstype IN ('A', 'D', 'M', 'V')) THEN
    SELECT ccpay_id, ccpay_type INTO _ccpayid, _cctype
    FROM ccpay
    WHERE ((ccpay_r_ordernum IN (CAST(pCashrcptid AS TEXT), _p.cashrcpt_docnumber))
       AND (ccpay_status IN ('C', 'A')));

    IF NOT FOUND THEN
      -- the following select seems to work except for xikar - bug 8848. why?
      -- raise warning so there is some visibility if people fall into this path.
      SELECT ccpay_id, ccpay_type INTO _ccpayid, _cctype
      FROM ccpay
      WHERE ((ccpay_order_number IN (CAST(pCashrcptid AS TEXT), _p.cashrcpt_docnumber))
         AND (ccpay_status IN ('C', 'A')));
      IF (NOT FOUND) THEN
        RETURN -8;
      ELSE
        RAISE NOTICE 'PostCashReceipt() found ccpay_id % for order number %/% (ref 8848).',
                      _ccpayid, pCashrcptid, _p.cashrcpt_docnumber;
      END IF;
    END IF;

-- If there is a ccpay entry and the card was charged directly, use the prepaid account
    IF (_cctype = 'C' ) THEN
      _debitAccntid := findPrepaidAccount(_p.cashrcpt_cust_id);
-- If there is a ccpay entry and the card was preauthed and then charged, use the Bank account
    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;
  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);
  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;

--  Determine the amount to post to Discount Credit Memo
  SELECT COALESCE(SUM(cashrcptitem_discount), 0) INTO _postToCM
  FROM cashrcptitem JOIN aropen ON ( (aropen_id=cashrcptitem_aropen_id) AND (aropen_doctype IN ('I', 'D')) )
  WHERE (cashrcptitem_cashrcpt_id=pCashrcptid);
  IF (NOT FOUND) THEN
    _postToCM := 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, aropen_amount,
                     round(aropen_amount - aropen_paid, 2) <=
                        round(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,cashrcptitem_discount,_p.cashrcpt_distdate),2) AS new_discount
              FROM cashrcptitem JOIN aropen ON (aropen_id=cashrcptitem_aropen_id)
              WHERE ((cashrcptitem_cashrcpt_id=pCashrcptid)
               AND (NOT _predist OR aropen_doctype IN ('C','R'))) LOOP
  
  --  Handle discount 
      IF (_r.cashrcptitem_discount_base > 0) THEN
        PERFORM postCashReceiptDisc(_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 = (NOT _r.closed),
          aropen_closedate = CASE WHEN _r.closed THEN _p.cashrcpt_distdate END
      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), _r.closed,
        _p.applydate, _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), _r.closed,
        _p.applydate, _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),
                          _p.cashrcpt_distdate, _p.custnote, pCashrcptid );      
                          
      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) * -1,
                 _p.cashrcpt_distdate, _p.custnote, pCashrcptid);
      END IF;

    END LOOP;

--  Distribute Misc. Applications
  FOR _r IN SELECT cashrcptmisc_id, cashrcptmisc_accnt_id, cashrcptmisc_amount,
                   (cashrcptmisc_amount / cashrcpt_curr_rate) AS cashrcptmisc_amount_base,
                   cashrcptmisc_notes, cashrcpt_curr_id
            FROM cashrcptmisc JOIN
                 cashrcpt ON (cashrcptmisc_cashrcpt_id = cashrcpt_id)
            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), TRUE,
      _p.applydate, _p.cashrcpt_distdate, pJournalNumber, getEffectiveXtUser(), 
      _r.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),
                                _p.cashrcpt_distdate, _p.custnote, pCashrcptid );

  END LOOP;

--  Post any remaining Cash to an A/R Cash Despoit (Credit 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),
                                _p.cashrcpt_distdate, _p.custnote, pCashrcptid );
    SELECT fetchArMemoNumber() INTO _arMemoNumber;
    IF(_p.cashrcpt_usecustdeposit) THEN
      -- Post Customer Deposit
      SELECT createARCashDeposit(_p.cashrcpt_cust_id, _arMemoNumber, '',
                                 _p.cashrcpt_distdate, (_p.cashrcpt_amount - _posted),
                                 _comment, pJournalNumber, _p.cashrcpt_curr_id) INTO _aropenid;
    ELSE
      -- Post A/R Credit Memo
      _aropenid := createARCreditMemo(NULL, _p.cashrcpt_cust_id, _arMemoNumber, '',
                                _p.cashrcpt_distdate, (_p.cashrcpt_amount - _posted),
                                _comment, -1, -1, -1, _p.cashrcpt_distdate, -1, NULL, 0,
                                pJournalNumber, _p.cashrcpt_curr_id, _arAccntid);
    END IF;

    IF (_ccpayid IS NOT NULL) THEN
      INSERT INTO payaropen (payaropen_ccpay_id, payaropen_aropen_id,
                             payaropen_amount,   payaropen_curr_id
                   ) VALUES (_ccpayid,           _aropenid,
                             _p.cashrcpt_amount, _p.cashrcpt_curr_id);
    END IF;

    -- Create Cash Receipt Item to capture posting
    IF (_predist=false) THEN
      INSERT INTO cashrcptitem
        ( cashrcptitem_cashrcpt_id, cashrcptitem_aropen_id, cashrcptitem_amount, cashrcptitem_applied )
      VALUES
        ( pCashrcptid, _aropenid, (_p.cashrcpt_amount - _posted), false );
    END IF;

  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),
                   _p.cashrcpt_distdate, _p.custnote, pCashrcptid);
  END IF;

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

  PERFORM postGLSeries(_sequence, pJournalNumber);

  -- convert the cashrcptitem records to applications against the cm/cd if we are _predist
  IF(_predist=true) THEN
    FOR _r IN SELECT *
              FROM cashrcptitem
              WHERE ((cashrcptitem_cashrcpt_id=pCashrcptid)
                AND (cashrcptitem_amount > 0)) LOOP

      -- Handle discount if applicable
      IF (_r.cashrcptitem_discount > 0) THEN
        PERFORM postCashReceiptDisc(_r.cashrcptitem_id, pJournalNumber);
      END IF;
      
      INSERT INTO arcreditapply (arcreditapply_source_aropen_id, arcreditapply_target_aropen_id,
                                 arcreditapply_amount, arcreditapply_curr_id)
                          VALUES(_aropenid, _r.cashrcptitem_aropen_id,
                                 _r.cashrcptitem_amount, _p.cashrcpt_curr_id);
      _posted := (_posted + _r.cashrcptitem_amount);
      
    END LOOP;

    PERFORM postArCreditMemoApplication(_aropenid, _p.applydate);
    
    -- If there is any left over go ahead and create an additional cashrcptitem record for it with the amount
    IF (round(_posted, 2) < round(_p.cashrcpt_amount, 2)) THEN
      INSERT INTO cashrcptitem
        ( cashrcptitem_cashrcpt_id, cashrcptitem_aropen_id, cashrcptitem_amount, cashrcptitem_applied )
      VALUES
        ( pCashrcptid, _aropenid, (_p.cashrcpt_amount - _posted), false );
    END IF;
  END IF;

--  Update the posted cashrcpt
  UPDATE cashrcpt SET cashrcpt_posted=TRUE,
                      cashrcpt_posteddate=CURRENT_DATE,
                      cashrcpt_postedby=getEffectiveXtUser()
  WHERE (cashrcpt_id=pCashrcptid);

  RETURN 1;

END;
$$;


ALTER FUNCTION public.postcashreceipt(pcashrcptid integer, pjournalnumber integer) OWNER TO admin;

--
-- TOC entry 1728 (class 1255 OID 146566797)
-- Dependencies: 4536 8
-- Name: postcashreceiptdisc(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postcashreceiptdisc(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 from ' || _r.cashrcpt_fundstype || '-' || _r.cashrcpt_docnumber;

      -- Create misc credit 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, 'C', 
        _arMemoNumber, _r.cashrcpt_curr_id, false,_r.cashrcptitem_discount);
        
      IF (fetchMetricBool('CreditTaxDiscount')) THEN
        --  proportional tax credits calculated and implemented for the credit 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) * -1), 
              _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) * -1),  
            _r.cashrcpt_distdate, 0.00
          FROM aropentax
          WHERE (taxhist_parent_id=_r.aropen_id);
              
        END IF;
      END IF; -- End taxes

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

      -- Apply discount credit memo
      INSERT INTO arcreditapply ( 
        arcreditapply_source_aropen_id, arcreditapply_target_aropen_id,
        arcreditapply_amount, arcreditapply_curr_id )
      VALUES ( 
        _ardiscountid, _r.aropen_id, _r.cashrcptitem_discount, _r.cashrcpt_curr_id );
 
      SELECT postARCreditMemoApplication(_ardiscountid, _r.cashrcpt_applydate) INTO _check;
      IF (_check < 0) THEN
        RAISE EXCEPTION 'Error posting discount credit memo application. Code %', _check;
      END IF;
        
   END IF; -- End handle Discount

   RETURN 1;

END;
$_$;


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

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

CREATE FUNCTION postcccashreceipt(pccpay integer, pdocid integer, pdoctype text DEFAULT NULL::text, pamount numeric DEFAULT NULL::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
  _aropenid     INTEGER;
  _bankaccnt_id INTEGER;
  _c            RECORD;
  _ccOrderDesc  TEXT;
  _journal      INTEGER;
  _realaccnt    INTEGER;
  _return       INTEGER := 0;

BEGIN
  SELECT * INTO _c
     FROM ccpay, ccard, custinfo
     WHERE ( (ccpay_id = pCCpay)
       AND   (ccpay_ccard_id = ccard_id)
       AND   (ccpay_cust_id = cust_id) );

  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Cannot find the Credit Card transaction information [xtuple: postCCcashReceipt, -11, %]',
                    pCCpay;
  END IF;

  IF (pamount IS NOT NULL) THEN
    _c.ccpay_amount = pamount;
  END IF;

  SELECT bankaccnt_id, bankaccnt_accnt_id INTO _bankaccnt_id, _realaccnt
  FROM ccbank JOIN bankaccnt ON (ccbank_bankaccnt_id=bankaccnt_id)
  WHERE (ccbank_ccard_type=_c.ccard_type);

  IF (_bankaccnt_id IS NULL) THEN
    RAISE EXCEPTION 'Cannot find the default Bank Account for this Credit Card [xtuple: postCCcredit, -1, %]',
                    _c.ccard_type;
  END IF;

  _ccOrderDesc := (_c.ccard_type || '-' || _c.ccpay_order_number::TEXT ||
		   '-' || _c.ccpay_order_number_seq::TEXT);

  _journal := fetchJournalNumber('C/R');

  IF (pdoctype = 'cashrcpt') THEN
    IF (COALESCE(pdocid, -1) < 0) THEN
      INSERT INTO cashrcpt (
        cashrcpt_cust_id,   cashrcpt_amount,     cashrcpt_curr_id,
        cashrcpt_fundstype, cashrcpt_docnumber,  cashrcpt_notes,
        cashrcpt_distdate,  cashrcpt_bankaccnt_id,
        cashrcpt_usecustdeposit
      ) VALUES (
        _c.ccpay_cust_id,   _c.ccpay_amount,     _c.ccpay_curr_id,
        _c.ccard_type,      _c.ccpay_r_ordernum, _ccOrderDesc,
        CURRENT_DATE,       _bankaccnt_id,
        fetchMetricBool('EnableCustomerDeposits'))
      RETURNING cashrcpt_id INTO _return;
    ELSE
      UPDATE cashrcpt
      SET cashrcpt_cust_id=_c.ccpay_cust_id,
          cashrcpt_amount=_c.ccpay_amount,
          cashrcpt_curr_id=_c.ccpay_curr_id,
          cashrcpt_fundstype=_c.ccard_type,
          cashrcpt_docnumber=_c.ccpay_r_ordernum,
          cashrcpt_notes=_ccOrderDesc,
          cashrcpt_distdate=CURRENT_DATE,
          cashrcpt_bankaccnt_id=_bankaccnt_id
      WHERE (cashrcpt_id=pdocid);
      _return := pdocid;
    END IF;

  ELSIF (pdoctype = 'cohead') THEN
    SELECT createARCreditMemo(NULL,               _c.ccpay_cust_id,
                             fetchArMemoNumber(), cohead_number,
                             CURRENT_DATE,        _c.ccpay_amount,
                             'Unapplied from ' || _ccOrderDesc,
                             NULL,                NULL, NULL,
                             CURRENT_DATE,        NULL,
                             cohead_salesrep_id,  NULL,
                             _journal,            _c.ccpay_curr_id,
                             NULL,                pCCpay) INTO _aropenid
      FROM cohead
     WHERE cohead_id = pdocid;
    IF (COALESCE(_aropenid, -1) < 0) THEN       -- coalesce handles not-found case
      RAISE EXCEPTION '[xtuple: createARCreditMemo, %]', _aropenid;
    END IF;

    INSERT INTO payaropen (payaropen_ccpay_id, payaropen_aropen_id,
                           payaropen_amount,   payaropen_curr_id)
                  VALUES  (pccpay,             _aropenid,
                           _c.ccpay_amount,    _c.ccpay_curr_id);
    INSERT INTO aropenalloc (aropenalloc_aropen_id, aropenalloc_doctype, aropenalloc_doc_id,
                             aropenalloc_amount,    aropenalloc_curr_id)
                     VALUES (_aropenid, 'S',          pdocid,
                             _c.ccpay_amount,    _c.ccpay_curr_id);
    _return := _aropenid;
  END IF;

  PERFORM insertGLTransaction(_journal, 'A/R', 'CR', _ccOrderDesc, 
                              ('Cash Receipt from Credit Card ' || _c.cust_name),
                              findPrepaidAccount(_c.ccpay_cust_id),
                              _realaccnt,
                              NULL,
			      ROUND(currToBase(_c.ccpay_curr_id,
					       _c.ccpay_amount,
					       _c.ccpay_transaction_datetime::DATE),2),
                              CURRENT_DATE);

  RETURN _return;
END;
$$;


ALTER FUNCTION public.postcccashreceipt(pccpay integer, pdocid integer, pdoctype text, pamount numeric) OWNER TO admin;

--
-- TOC entry 1992 (class 1255 OID 146566801)
-- Dependencies: 4536 8
-- Name: postcccredit(integer, text, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postcccredit(integer, 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
  pCCpay	ALIAS FOR $1;
  preftype      ALIAS FOR $2;
  prefid        ALIAS FOR $3;
  _c		RECORD;
  _ccOrderDesc	TEXT;
  _cglaccnt     INTEGER;
  _dglaccnt	INTEGER;
  _glseriesres  INTEGER;
  _notes	TEXT := 'Credit via Credit Card';
  _r		RECORD;
  _sequence	INTEGER;
  _dmaropenid	INTEGER;

BEGIN
  IF ((preftype = 'cohead') AND NOT EXISTS(SELECT cohead_id
					     FROM cohead
					     WHERE (cohead_id=prefid))) THEN
    RAISE EXCEPTION 'Cannot find original Sales Order for this Credit Card credit [xtuple: postCCcredit, -2, %, %, %]',
                    pCCpay, preftype, prefid;
  ELSIF ((preftype = 'aropen') AND NOT EXISTS(SELECT aropen_id
                                                FROM aropen
                                                WHERE (aropen_id=prefid))) THEN
    RAISE EXCEPTION 'Cannot find original A/R Open record for this Credit Card credit [xtuple: postCCcredit, -2, %, %, %]',
                    pCCpay, preftype, prefid;
  ELSIF ((preftype = 'cmhead') AND NOT EXISTS(SELECT cmhead_id
                                                FROM cmhead
                                               WHERE cmhead_id=prefid)) THEN
    RAISE EXCEPTION 'Cannot find original Credit Memo record for this Credit Card credit [xtuple: postCCcredit, -2, %, %, %]',
                    pCCpay, preftype, prefid;
  END IF;

  SELECT * INTO _c
     FROM ccpay
     JOIN ccard  ON (ccpay_ccard_id = ccard_id)
     JOIN ccbank ON (ccard_type=ccbank_ccard_type)
    WHERE (ccpay_id = pCCpay);

  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Cannot find the record for this Credit Card credit [xtuple: postCCcredit, -3, %, %, %]',
                    pCCpay, preftype, prefid;
  END IF;

  IF (preftype = 'cohead') THEN
    _dglaccnt := findPrepaidAccount(_c.ccpay_cust_id);
  ELSE
    _dglaccnt := findARAccount(_c.ccpay_cust_id);
  END IF;

  SELECT bankaccnt_accnt_id INTO _cglaccnt
  FROM bankaccnt
  WHERE (bankaccnt_id=_c.ccbank_bankaccnt_id);

  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Cannot find the default Bank Account for this Credit Card [xtuple: postCCcredit, -1, %]',
                    pCCpay;
  END IF;

  IF (_c.ccpay_type != 'R') THEN
    RAISE EXCEPTION 'This Credit Card transaction is not a credit/refund [xtuple: postCCcredit, -4, %]',
                    pCCpay;
  END IF;

  _sequence := fetchGLSequence();

  IF (_c.ccpay_r_ref IS NOT NULL) THEN
    _ccOrderDesc := (_c.ccard_type || '-' || _c.ccpay_r_ref);
  ELSE
    _ccOrderDesc := (_c.ccard_type || '-' || _c.ccpay_order_number::TEXT ||
		     '-' || COALESCE(_c.ccpay_order_number_seq::TEXT, ''));
  END IF;

  _glseriesres := insertIntoGLSeries(_sequence, 'A/R', 'CC', _ccOrderDesc,
                                     _dglaccnt,
                                     ROUND(currToBase(_c.ccpay_curr_id,
                                                      _c.ccpay_amount,
                                                      _c.ccpay_transaction_datetime::DATE), 2) * -1,
                                     CURRENT_DATE, _notes);
  IF (_glseriesres < 0) THEN
    RAISE EXCEPTION 'Could not write debit side of Credit Card credit to the G/L [xtuple: insertIntoGLSeries, %]',
                    _glseriesres;
  END IF;

  _glseriesres := insertIntoGLSeries(_sequence, 'A/R', 'CC', _ccOrderDesc,
                                     _cglaccnt,
                                     ROUND(currToBase(_c.ccpay_curr_id,
                                                      _c.ccpay_amount,
                                                      _c.ccpay_transaction_datetime::DATE),2),
                                     CURRENT_DATE, _notes);
  IF (_glseriesres < 0) THEN
    RAISE EXCEPTION 'Could not write credit side of Credit Card credit to the G/L [xtuple: insertIntoGLSeries, %]',
                    _glseriesres;
  END IF;

  _glseriesres := postGLSeries(_sequence, fetchJournalNumber('C/R') );
  IF (_glseriesres < 0) THEN
    RAISE EXCEPTION 'Could not post Credit Card credit to the G/L [xtuple: postglseries, %]',
                    _glseriesres;
  END IF;

  IF (preftype = 'aropen') THEN
    SELECT * INTO _r
    FROM aropen
    WHERE (aropen_id=prefid);

  ELSE
    SELECT aropen.* INTO _r
    FROM ccpay n
      JOIN ccpay o  ON (o.ccpay_id=n.ccpay_ccpay_id)
      JOIN payaropen ON (payaropen_ccpay_id=o.ccpay_id)
      JOIN aropen ON (payaropen_aropen_id=aropen_id)
    WHERE (n.ccpay_id=pCCpay);
  END IF;

  IF (FOUND) THEN
    SELECT createardebitmemo(
            NULL, 
            _r.aropen_cust_id, NULL, fetchARMemoNumber(),
            _r.aropen_ordernumber, current_date, _c.ccpay_amount,
            _notes,
            -1, -1, -1, CURRENT_DATE, -1, NULL, 0, 
            _r.aropen_curr_id) INTO _dmaropenid;

    IF (_r.aropen_open) THEN
      PERFORM applyARCreditMemoToBalance(_r.aropen_id, _dmaropenid);
      PERFORM postARCreditMemoApplication(_r.aropen_id);
    END IF;
    
  END IF;

  IF (preftype = 'cohead') THEN
    INSERT INTO payco (
      payco_ccpay_id, payco_cohead_id, payco_amount, payco_curr_id 
    ) VALUES (
      pCCpay, prefid, 0 - _c.ccpay_amount, _c.ccpay_curr_id
    );
  END IF;

  RETURN 0;

END;
$_$;


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

--
-- TOC entry 1993 (class 1255 OID 146566802)
-- Dependencies: 4536 8
-- Name: postccvoid(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  -- for now this is very simple: mark the ccpay record voided.
  -- in the future this might be expanded to back out changes to other tables
  -- but for now the VOID request is sent to the credit card processing company
  -- before those other tables are modified.

  UPDATE ccpay SET ccpay_status = 'V' WHERE (ccpay_id=pccpayid);

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

  RETURN 0;

END;
$_$;


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

--
-- TOC entry 1994 (class 1255 OID 146566803)
-- Dependencies: 4536 8
-- Name: postcheck(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postcheck(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
  pcheckid		ALIAS FOR $1;
  _journalNumber	INTEGER := $2;
  _amount_base		NUMERIC := 0;
  _credit_glaccnt	INTEGER;
  _exchGain		NUMERIC := 0;
  _exchGainTmp		NUMERIC := 0;
  _gltransNote		TEXT;
  _p			RECORD;
  _r			RECORD;
  _t			RECORD;
  _sequence		INTEGER;
  _test                 INTEGER;
  _cm                   BOOLEAN;
  _amount_check         NUMERIC := 0;

BEGIN

  _cm := FALSE;

  SELECT fetchGLSequence() INTO _sequence;
  IF (_journalNumber IS NULL) THEN
    _journalNumber := fetchJournalNumber('AP-CK');
  END IF;

  SELECT checkhead.*,
         checkhead_amount / checkhead_curr_rate AS checkhead_amount_base,
         bankaccnt_accnt_id AS bankaccntid INTO _p
  FROM checkhead
   JOIN bankaccnt ON (checkhead_bankaccnt_id=bankaccnt_id)
  WHERE (checkhead_id=pcheckid);

  IF (FOUND) THEN
    IF (_p.checkhead_recip_type = 'V') THEN
      SELECT
        vend_number AS checkrecip_number,
        vend_name AS checkrecip_name,
        findAPAccount(vend_id) AS checkrecip_accnt_id,
        'A/P'::text AS checkrecip_gltrans_source
        INTO _t
      FROM vendinfo
      WHERE (vend_id=_p.checkhead_recip_id);
    ELSIF (_p.checkhead_recip_type = 'C') THEN
      SELECT
        cust_number AS checkrecip_number,
        cust_name AS checkrecip_name,
        findARAccount(cust_id) AS checkrecip_accnt_id,
        'A/R'::text AS checkrecip_gltrans_source
        INTO _t
      FROM custinfo
      WHERE (cust_id=_p.checkhead_recip_id); 
    ELSIF (_p.checkhead_recip_type = 'T') THEN
      SELECT
        taxauth_code AS checkrecip_number,
        taxauth_name AS checkrecip_name,
        taxauth_accnt_id AS checkrecip_accnt_id,
        'G/L'::text AS checkrecip_gltrans_source
        INTO _t
      FROM taxauth
      WHERE (taxauth_id=_p.checkhead_recip_id);
    ELSE
      RETURN -11;
    END IF;
  ELSE
    RETURN -11;
  END IF;

  IF (_p.checkhead_posted) THEN
    RETURN -10;
  END IF;

  IF (_p.checkhead_recip_type = 'C') THEN
    SELECT checkitem_id FROM checkitem INTO _test
    WHERE (checkitem_checkhead_id=pcheckid)
    LIMIT 1;
    IF (FOUND) THEN
      _cm := TRUE;
    END IF;
  END IF;

  _gltransNote := _t.checkrecip_number || '-' || _t.checkrecip_name;

  IF (_p.checkhead_misc AND NOT _cm) THEN
    IF (COALESCE(_p.checkhead_expcat_id, -1) < 0) THEN
      IF (_p.checkhead_recip_type = 'V') THEN
	PERFORM createAPCreditMemo( _p.checkhead_recip_id, _journalNumber,
				    CAST(fetchAPMemoNumber() AS text), '',
				    _p.checkhead_checkdate, _p.checkhead_amount,
				    _gltransNote || ' ' || _p.checkhead_notes,
				    -1, _p.checkhead_checkdate,
				    -1, _p.checkhead_curr_id );
	_credit_glaccnt := findAPPrepaidAccount(_p.checkhead_recip_id);

      ELSIF (_p.checkhead_recip_type = 'C') THEN
	PERFORM createARDebitMemo(NULL, _p.checkhead_recip_id, NULL,
	  			     fetchARMemoNumber(), '',
				     _p.checkhead_checkdate, _p.checkhead_amount,
				     _gltransNote || ' ' || _p.checkhead_notes,
                                     -1, -1, -1, _p.checkhead_checkdate, -1, NULL, 0,
				     _p.checkhead_curr_id );
        _credit_glaccnt := findPrepaidAccount(_p.checkhead_recip_id);
      ELSIF (_p.checkhead_recip_type = 'T') THEN
	-- TODO: should we create a credit memo for the tax authority? how?
	_credit_glaccnt := _t.checkrecip_accnt_id;

      END IF; -- recip type

    ELSE
      IF (_cm) THEN
        _credit_glaccnt := findARAccount(_p.checkhead_recip_id);
      ELSE
        SELECT expcat_exp_accnt_id INTO _credit_glaccnt
        FROM expcat
        WHERE (expcat_id=_p.checkhead_expcat_id);
        IF (NOT FOUND) THEN
          RETURN -12;
        END IF;
      END IF;
    END IF;

    IF (COALESCE(_credit_glaccnt, -1) < 0) THEN
      RETURN -13;
    END IF;

    PERFORM insertIntoGLSeries( _sequence, _t.checkrecip_gltrans_source, 'CK',
				CAST(_p.checkhead_number AS TEXT),
				_credit_glaccnt,
				round(_p.checkhead_amount_base, 2) * -1,
				_p.checkhead_checkdate, _gltransNote, pcheckid );

    _amount_base := _p.checkhead_amount_base;

  ELSE
    FOR _r IN SELECT checkitem_amount, checkitem_discount,
                     CASE WHEN (checkitem_apopen_id IS NOT NULL AND apopen_doctype='C') THEN
                            checkitem_amount / apopen_curr_rate * -1.0
                          WHEN (checkitem_apopen_id IS NOT NULL) THEN
                            checkitem_amount / apopen_curr_rate
                          ELSE
                            currToBase(checkitem_curr_id,
                                       checkitem_amount,
                                       COALESCE(checkitem_docdate, _p.checkhead_checkdate)) 
                     END AS checkitem_amount_base,
                     currTocurr(checkitem_curr_id, _p.checkhead_curr_id,
                                CASE WHEN (checkitem_apopen_id IS NOT NULL AND apopen_doctype='C') THEN
                                          checkitem_amount * -1.0
                                     ELSE checkitem_amount END,
                                  _p.checkhead_checkdate) AS amount_check,
                     apopen_id, apopen_doctype, apopen_docnumber,
                     aropen_id, aropen_doctype, aropen_docnumber,
                     checkitem_curr_id, checkitem_curr_rate, apopen_curr_rate,
                     COALESCE(checkitem_docdate, _p.checkhead_checkdate) AS docdate
              FROM (checkitem LEFT OUTER JOIN
		    apopen ON (checkitem_apopen_id=apopen_id)) LEFT OUTER JOIN
		    aropen ON (checkitem_aropen_id=aropen_id)
              WHERE (checkitem_checkhead_id=pcheckid) LOOP

      _exchGainTmp := 0;
      IF (_r.apopen_id IS NOT NULL) THEN
	--  take the discount if specified before we do anything else
        IF(_r.checkitem_discount > 0.0) THEN
          PERFORM createAPDiscount(_r.apopen_id, _r.checkitem_discount);
        END IF;

        UPDATE apopen

        SET apopen_paid = round(apopen_paid + _r.checkitem_amount, 2),
            apopen_open = round(apopen_amount, 2) >
			  round(apopen_paid + _r.checkitem_amount, 2),
            apopen_closedate = CASE WHEN (round(apopen_amount, 2) <=
			                  round(apopen_paid + _r.checkitem_amount, 2)) THEN _p.checkhead_checkdate END
        WHERE (apopen_id=_r.apopen_id);

	--  Post the application
        INSERT INTO apapply
        ( apapply_vend_id, apapply_postdate, apapply_username,
          apapply_source_apopen_id, apapply_source_doctype, apapply_source_docnumber,
          apapply_target_apopen_id, apapply_target_doctype, apapply_target_docnumber,
          apapply_journalnumber, apapply_amount, apapply_curr_id, apapply_checkhead_id )
        VALUES
        ( _p.checkhead_recip_id, _p.checkhead_checkdate, getEffectiveXtUser(),
          -1, 'K', _p.checkhead_number,
          _r.apopen_id, _r.apopen_doctype, _r.apopen_docnumber,
          _journalNumber, _r.checkitem_amount, _r.checkitem_curr_id, _p.checkhead_id );
      END IF; -- if check item's apopen_id is not null

      IF (_r.aropen_id IS NOT NULL) THEN

        UPDATE aropen
        SET aropen_paid = round(aropen_paid + _r.checkitem_amount, 2),
            aropen_open = round(aropen_amount, 2) >
			  round(aropen_paid + _r.checkitem_amount, 2),
            aropen_closedate = CASE WHEN (round(aropen_amount, 2) <=
			                  round(aropen_paid + _r.checkitem_amount, 2)) THEN _p.checkhead_checkdate END
        WHERE (aropen_id=_r.aropen_id);

	--  Post the application
        INSERT INTO arapply
        ( arapply_cust_id, arapply_postdate, arapply_distdate, arapply_username,
          arapply_source_aropen_id, arapply_source_doctype, arapply_source_docnumber,
          arapply_target_aropen_id, arapply_target_doctype, arapply_target_docnumber,
          arapply_journalnumber, arapply_applied, arapply_curr_id )
        VALUES
        ( _p.checkhead_recip_id, _p.checkhead_checkdate, _p.checkhead_checkdate, getEffectiveXtUser(),
          _r.aropen_id,_r.aropen_doctype, _r.aropen_docnumber,
          -1, 'K',_p.checkhead_number ,
          _journalNumber, _r.checkitem_amount, _r.checkitem_curr_id );

      END IF; -- if check item's aropen_id is not null

      IF (_r.apopen_id IS NOT NULL) THEN
        SELECT apCurrGain(_r.apopen_id,_r.checkitem_curr_id, _r.checkitem_amount,
                        _p.checkhead_checkdate)
              INTO _exchGainTmp;
      ELSIF (_r.aropen_id IS NOT NULL) THEN
        SELECT arCurrGain(_r.aropen_id,_r.checkitem_curr_id, _r.checkitem_amount,
                        _p.checkhead_checkdate)
              INTO _exchGainTmp;
      END IF;
      _exchGain := _exchGain + _exchGainTmp;

      PERFORM insertIntoGLSeries( _sequence, _t.checkrecip_gltrans_source,
                                  'CK', CAST(_p.checkhead_number AS TEXT),
                                  _t.checkrecip_accnt_id,
                                  round(_r.checkitem_amount_base, 2) * -1.0,
                                  _p.checkhead_checkdate, _gltransNote, pcheckid );
      IF (_exchGainTmp <> 0) THEN
	PERFORM insertIntoGLSeries( _sequence, _t.checkrecip_gltrans_source,
                                   'CK', CAST(_p.checkhead_number AS TEXT),
                                   getGainLossAccntId(_t.checkrecip_accnt_id),
                                   round(_exchGainTmp,2),
                                   _p.checkhead_checkdate, _gltransNote, pcheckid );
      END IF;

      _amount_check := (_amount_check + _r.amount_check);
      _amount_base := (_amount_base + _r.checkitem_amount_base);

    END LOOP;

    IF( (_amount_check - _p.checkhead_amount) <> 0.0 ) THEN 
      _exchGainTmp := currToBase(_p.checkhead_curr_id,
                                 _amount_check - _p.checkhead_amount,
                                 _p.checkhead_checkdate);
      _exchGain := _exchGain + _exchGainTmp;
    END IF;
    --  ensure that the check balances, attribute rounding errors to gain/loss
    IF round(_amount_base, 2) - round(_exchGain, 2) <> round(_p.checkhead_amount_base, 2) THEN
      IF round(_amount_base - _exchGain, 2) = round(_p.checkhead_amount_base, 2) THEN
	PERFORM insertIntoGLSeries( _sequence, _t.checkrecip_gltrans_source,
				    'CK',
				    CAST(_p.checkhead_number AS TEXT),
                                    getGainLossAccntId(_p.bankaccntid),
				    round(_amount_base, 2) -
				      round(_exchGain, 2) -
				      round(_p.checkhead_amount_base, 2),
				    _p.checkhead_checkdate, _gltransNote, pcheckid );
      ELSE
	RAISE EXCEPTION 'checkhead_id % does not balance (% - % <> %)', pcheckid,
	      _amount_base, _exchGain, _p.checkhead_amount_base;
      END IF;
    END IF;
  END IF;

  PERFORM insertIntoGLSeries( _sequence, _t.checkrecip_gltrans_source, 'CK',
			      CAST(_p.checkhead_number AS TEXT),
                              _p.bankaccntid,
			      round(_p.checkhead_amount_base, 2),
                              _p.checkhead_checkdate, _gltransNote, pcheckid );

  PERFORM postGLSeries(_sequence, _journalNumber);

  UPDATE checkhead
  SET checkhead_posted=TRUE,
      checkhead_journalnumber=_journalNumber
  WHERE (checkhead_id=pcheckid);

  RETURN _journalNumber;

END;
$_$;


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

--
-- TOC entry 1995 (class 1255 OID 146566805)
-- Dependencies: 4536 8
-- Name: postchecks(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postchecks(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;
  _journalNumber INTEGER;

BEGIN

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

  PERFORM postCheck(checkhead_id, _journalNumber)
  FROM checkhead
  WHERE ( (NOT checkhead_void)
    AND   (NOT checkhead_posted)
    AND   (checkhead_printed)
    AND   (checkhead_bankaccnt_id=pBankaccntid) );

  RETURN _journalNumber;

END;
$_$;


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

--
-- TOC entry 1996 (class 1255 OID 146566806)
-- Dependencies: 4536 8
-- Name: postcomment(integer, text, integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postcomment(pcmnttypeid integer, psource text, psourceid integer, ptext 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

BEGIN
  RETURN postComment(pCmnttypeid, pSource, pSourceid, pText, NULL);
END
$$;


ALTER FUNCTION public.postcomment(pcmnttypeid integer, psource text, psourceid integer, ptext text) OWNER TO admin;

--
-- TOC entry 1998 (class 1255 OID 146566808)
-- Dependencies: 4536 8
-- Name: postcomment(text, text, integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postcomment(pcmnttypename text, psource text, psourceid integer, ptext 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
  _cmnttypeid INTEGER;

BEGIN
  SELECT cmnttype_id INTO _cmnttypeid
  FROM cmnttype
  WHERE (cmnttype_name=pCmnttypename);
  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Comment type % not found.', pCmnttypename;
  END IF;

  RETURN postComment(_cmnttypeid, pSource, pSourceid, pText, NULL);
END
$$;


ALTER FUNCTION public.postcomment(pcmnttypename text, psource text, psourceid integer, ptext text) OWNER TO admin;

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

CREATE FUNCTION postcomment(pcmnttypeid integer, psource text, psourceid integer, ptext text, ppublic 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
  _commentid INTEGER;
  _public BOOLEAN;

BEGIN
  _public := COALESCE(pPublic, fetchmetricbool('CommentPublicDefault'));

  INSERT INTO comment
  ( comment_cmnttype_id, comment_source, comment_source_id,
    comment_date, comment_user, comment_text, comment_public )
  VALUES
  ( pCmnttypeid, pSource, pSourceid,
    CURRENT_TIMESTAMP, getEffectiveXtUser(), pText, _public )
  RETURNING comment_id INTO _commentid;

  RETURN _commentid;

END;
$$;


ALTER FUNCTION public.postcomment(pcmnttypeid integer, psource text, psourceid integer, ptext text, ppublic boolean) OWNER TO admin;

--
-- TOC entry 1999 (class 1255 OID 146566809)
-- Dependencies: 4536 8
-- Name: postcost(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT round(currToBase(itemcost_curr_id, itemcost_actcost, CURRENT_DATE),6) AS newcost,
         itemcost_curr_id, CURRENT_DATE AS effective,
         item_number,
         itemcost_stdcost AS oldcost INTO _p
  FROM itemcost, item
  WHERE ((itemcost_item_id=item_id)
    AND  (itemcost_id=pItemcostid));

  IF (_p.newcost IS NULL) THEN
      RAISE EXCEPTION 'There is no valid Exchange Rate for this currency. (%, %)',
                  _p.itemcost_curr_id, _p.effective;
      RETURN FALSE;
  END IF;

  RETURN updateStdCost(pItemcostid, _p.newcost, _p.oldcost, 'Post Cost',
               ('Post Actual Cost to Standard for item ' || _p.item_number));

END;
$_$;


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

--
-- TOC entry 2001 (class 1255 OID 146566810)
-- Dependencies: 4536 8
-- Name: postcountslip(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postcountslip(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
  pCntslipid ALIAS FOR $1;
  _p RECORD;
  _comments TEXT;
  _temp TEXT;

BEGIN

  SELECT itemsite_loccntrl, itemsite_controlmethod,
         cntslip_posted, cntslip_lotserial, cntslip_comments,
         cntslip_number, cntslip_qty INTO _p
  FROM cntslip, invcnt, itemsite
  WHERE ( (cntslip_cnttag_id=invcnt_id)
   AND (invcnt_itemsite_id=itemsite_id)
   AND (cntslip_id=pCntslipid) );

  IF (NOT _p.cntslip_posted) THEN
    SELECT ( E'\nCount Slip #' || _p.cntslip_number ||
             ' counted ' || formatQty(_p.cntslip_qty) ) INTO _comments;

--  Add the Location name if the itemsite is MLC
    IF (_p.itemsite_loccntrl) THEN
      SELECT ( ', Location:' || location_name ) INTO _temp
      FROM location, cntslip
      WHERE ( (cntslip_location_id=location_id)
       AND (cntslip_id=pCntslipid) );

      _comments := (_comments || _temp);
    END IF;

--  Add the Lot/Serial if the itemsite is Lot or Serial controlled
    IF (_p.itemsite_controlmethod = 'L') THEN
      _comments := (_comments || ( ', Lot #:' || _p.cntslip_lotserial));
    ELSIF (_p.itemsite_controlmethod = 'S') THEN
      _comments := (_comments || ( ', Serial #:' || _p.cntslip_lotserial));
    END IF;

    _comments := (_comments || ' ' || _p.cntslip_comments);

    UPDATE cntslip
    SET cntslip_posted=TRUE
    WHERE (cntslip_id=pCntslipid);

    UPDATE invcnt
    SET invcnt_qoh_after = ( COALESCE(invcnt_qoh_after, 0) + cntslip_qty),
        invcnt_comments = (invcnt_comments || _comments)
    FROM cntslip
    WHERE ( (cntslip_cnttag_id=invcnt_id)
     AND (cntslip_id=pCntslipid) );

    RETURN 1;

  ELSE
    RETURN -1;
  END IF;

END;
$_$;


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

--
-- TOC entry 2002 (class 1255 OID 146566811)
-- Dependencies: 4536 8
-- Name: postcounttag(integer, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postcounttag(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
  pInvcntid ALIAS FOR $1;
  pThaw ALIAS FOR $2;
  _avgCostingMethod TEXT;
  _invhistid INTEGER;
  _postDate TIMESTAMP;
  _runningQty NUMERIC;
  _errorCode INTEGER;
  _itemlocSeries INTEGER := 0;
  _hasDetail BOOLEAN;
  _p RECORD;
  _itemloc RECORD;
  _cntslip RECORD;
  _lsid INTEGER;

BEGIN

  SELECT COALESCE(fetchMetricText('CountAvgCostMethod'), 'STD') INTO _avgCostingMethod;

  SELECT invcnt_id, invcnt_tagnumber, invcnt_qoh_after,
         invcnt_location_id,
         item_number,
         itemsite_id, itemsite_freeze,
         itemsite_qtyonhand,
         itemsite_loccntrl, itemsite_location_id,
         CASE WHEN (itemsite_costmethod = 'N') THEN 0
              WHEN ( (itemsite_costmethod = 'A') AND
                     (itemsite_qtyonhand = 0) AND
                     (_avgCostingMethod = 'ACT') ) THEN actcost(itemsite_item_id)
              WHEN ( (itemsite_costmethod = 'A') AND
                     (_avgCostingMethod IN ('ACT', 'AVG')) ) THEN avgcost(itemsite_id)
              ELSE stdcost(itemsite_item_id)
         END AS cost, itemsite_costmethod,
         itemsite_controlmethod,
         itemsite_value INTO _p
  FROM invcnt, itemsite, item
  WHERE ( (invcnt_itemsite_id=itemsite_id)
   AND (itemsite_item_id=item_id)
   AND (invcnt_qoh_after IS NOT NULL)
   AND (NOT invcnt_posted)
   AND (invcnt_id=pInvcntid) );
  IF (FOUND) THEN
-- If the invcnt_location_id is not null then
-- call a separate function so as not to affect
-- the existing functionality.
    IF (_p.invcnt_location_id IS NOT NULL) THEN
      RETURN postCountTagLocation(pInvcntid, pThaw);
    END IF;

    SELECT NEXTVAL('invhist_invhist_id_seq') INTO _invhistid;

    IF (_p.itemsite_freeze) THEN
      SELECT invcnt_tagdate INTO _postDate
      FROM invcnt
      WHERE (invcnt_id=pInvcntid) ;
    ELSE
      _postDate = CURRENT_TIMESTAMP;
    END IF;

    _hasDetail = FALSE;

--  Post the detail indicated by cntslips
    IF ( (_p.itemsite_loccntrl) OR
         (_p.itemsite_controlmethod IN ('L', 'S')) ) THEN

      SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;

--  Adjust any existing detail to 0
      FOR _itemloc IN SELECT itemloc_id, itemloc_location_id,
                             itemloc_ls_id, itemloc_qty
                      FROM itemloc
                      WHERE (itemloc_itemsite_id=_p.itemsite_id) LOOP

        _hasDetail = TRUE;

--  Create the itemlocdist flushing records
        INSERT INTO itemlocdist
        ( itemlocdist_series, itemlocdist_source_type, itemlocdist_source_id,
          itemlocdist_expiration,
          itemlocdist_itemsite_id, itemlocdist_invhist_id, itemlocdist_flush )
        VALUES
        ( _itemlocSeries, 'I', _itemloc.itemloc_id,
          endOfTime(),
          _p.itemsite_id, _invhistid, TRUE );

      END LOOP;

--  Clear the running detail Qty
      _runningQty := 0;

--  Adjust the detail to the cntslip indicated value
      FOR _cntslip IN SELECT cntslip_location_id, cntslip_lotserial,
                             cntslip_lotserial_expiration,
                             cntslip_lotserial_warrpurc,
                             SUM(cntslip_qty) AS qty,
                             itemsite_item_id
                      FROM cntslip, invcnt, itemsite
                      WHERE ((cntslip_cnttag_id=pInvcntid)
                      AND (cntslip_cnttag_id=invcnt_id)
                      AND (invcnt_itemsite_id=itemsite_id))
                      GROUP BY cntslip_location_id, cntslip_lotserial, 
                      cntslip_lotserial_expiration, cntslip_lotserial_warrpurc,itemsite_item_id LOOP

--  Handle the LotSerial
        IF (LENGTH(_cntslip.cntslip_lotserial)>0) THEN
          SELECT ls_id INTO _lsid
          FROM ls
          WHERE ((ls_item_id=_cntslip.itemsite_item_id)
          AND (UPPER(ls_number)=UPPER(_cntslip.cntslip_lotserial)));

          IF (NOT FOUND) THEN
            _lsid := NEXTVAL('ls_ls_id_seq');
            INSERT INTO ls
            VALUES (_lsid,_cntslip.itemsite_item_id,UPPER(_cntslip.cntslip_lotserial));
          END IF;
        END IF;

--  Track the running Qty
        _runningQty := (_runningQty + _cntslip.qty);
        _hasDetail = TRUE;

--  Create the itemlocdist populating record
        INSERT INTO itemlocdist
        ( itemlocdist_series, itemlocdist_source_type, itemlocdist_source_id,
          itemlocdist_itemsite_id,
          itemlocdist_ls_id, itemlocdist_expiration, itemlocdist_warranty,
          itemlocdist_qty, itemlocdist_invhist_id )
        VALUES
        ( _itemlocSeries, 'L', _cntslip.cntslip_location_id,
          _p.itemsite_id,
          _lsid, COALESCE(_cntslip.cntslip_lotserial_expiration, endOfTime()),
          _cntslip.cntslip_lotserial_warrpurc,_cntslip.qty, _invhistid );

      END LOOP;

      IF (_runningQty > _p.invcnt_qoh_after) THEN
--  The total Count Slip Qty is greater than the Count Tag Qty,
--  Don't post the Count.
        _errorCode = -1;

      ELSIF ( (_runningQty < _p.invcnt_qoh_after) AND
              (_p.itemsite_controlmethod IN ('L', 'S')) ) THEN
--  The total Count Slip Qty is less than the Count Tag Qty,
--  and the Item Site is Lot/Serial controlled.
--  Don't post the Count.
        _errorCode = -2;

      ELSIF (_runningQty < _p.invcnt_qoh_after) THEN
        IF ( (NOT _p.itemsite_loccntrl) OR
             (_p.itemsite_location_id = -1) ) THEN
--  The total Count Slip Qty is less than the Count Tag Qty,
--  and there isn't a default location to post into.
--  Don't post the Count.
          _errorCode = -3;

        ELSIF ( SELECT (metric_value='f')
                FROM metric
                WHERE (metric_name='PostCountTagToDefault') ) THEN
--  The total Count Slip Qty is less than the Count Tag Qty,
--  and we don't post Count Tags to default Locations
--  Don't post the Count.
          _errorCode = -4;

        ELSE
--  Distribute the remaining qty into the default location.
          INSERT INTO itemlocdist
          ( itemlocdist_series, itemlocdist_source_type, itemlocdist_source_id,
            itemlocdist_itemsite_id,
            itemlocdist_expiration,
            itemlocdist_qty, itemlocdist_invhist_id )
          SELECT _itemlocSeries, 'L', _p.itemsite_location_id,
                 _p.itemsite_id,
                 endOfTime(),
                 (_p.invcnt_qoh_after - _runningQty), _invhistid;

          _hasDetail = TRUE;
          _errorCode = 0;
        END IF;
      ELSE
--  The Count Slip Qty. must equal the Count Tag Qty.
        _errorCode = 0;
      END IF;

--  If we shouldn't post the count then delete the itemlocdist records,
--  and return with the error.
      IF (_errorCode <> 0) THEN
        DELETE FROM itemlocdist
        WHERE (itemlocdist_series=_itemlocSeries);
  
        RETURN _errorCode;
      END IF;

    END IF;

--  Mod. the Count Tag.
    UPDATE invcnt
    SET invcnt_qoh_before=_p.itemsite_qtyonhand,
        invcnt_postdate=_postDate,
        invcnt_posted=TRUE,
        invcnt_invhist_id=_invhistid,
        invcnt_post_username=getEffectiveXtUser()
    WHERE (invcnt_id=pInvcntid);

--  Create the CC transaction
    INSERT INTO invhist
     ( invhist_id, invhist_itemsite_id,
       invhist_transdate, invhist_transtype, invhist_invqty,
       invhist_qoh_before, invhist_qoh_after,
       invhist_docnumber, invhist_comments,
       invhist_invuom, invhist_unitcost, invhist_hasdetail,
       invhist_costmethod, invhist_value_before, invhist_value_after,
       invhist_series )
    SELECT _invhistid, itemsite_id,
           _postDate, 'CC', (invcnt_qoh_after - invcnt_qoh_before),
           invcnt_qoh_before, invcnt_qoh_after,
           invcnt_tagnumber, invcnt_comments,
           uom_name, _p.cost, _hasDetail,
           _p.itemsite_costmethod, _p.itemsite_value, 
           _p.itemsite_value + (_p.cost * (invcnt_qoh_after - invcnt_qoh_before)),
           _itemlocSeries
    FROM itemsite, invcnt, item, uom
    WHERE ( (invcnt_itemsite_id=itemsite_id)
     AND (itemsite_item_id=item_id)
     AND (item_inv_uom_id=uom_id)
     AND (itemsite_controlmethod <> 'N')
     AND (invcnt_id=pInvcntid) );

    IF ( SELECT metric_value
        FROM metric
        WHERE ((metric_name = 'EnableAsOfQOH')
        AND (metric_value = 't'))) THEN
      IF (NOT postIntoInvBalance(_invhistid)) THEN
        RAISE EXCEPTION 'Post into Inventory Balance for invhist_id=% was unsuccessful',_invhistid;
      END IF;
    END IF;

--  Update the QOH
--  Avoid negative value when average cost item
    UPDATE itemsite
    SET itemsite_qtyonhand=_p.invcnt_qoh_after,
        itemsite_nnqoh = 0,
        itemsite_value = CASE WHEN ((itemsite_costmethod='A') AND (_p.itemsite_value + (_p.cost * (_p.invcnt_qoh_after - itemsite_qtyonhand))) < 0.0) THEN 0.0
                              ELSE (_p.itemsite_value + (_p.cost * (_p.invcnt_qoh_after - itemsite_qtyonhand)))
                         END,
        itemsite_datelastcount=_postDate
    WHERE (itemsite_id=_p.itemsite_id);
 
--  Post the detail, if any
    IF (_hasDetail) THEN
      PERFORM distributeItemlocSeries(_itemlocSeries);
    END IF;

--  Thaw the itemsite if it's frozen
    IF (pThaw) THEN
      PERFORM thawItemSite(invcnt_itemsite_id) 
      FROM invcnt
      WHERE (invcnt_id=pInvcntid);
    END IF;

--  Distribute to G/L
    PERFORM insertGLTransaction( 'I/M', 'CT', _p.invcnt_tagnumber, ('Post Count Tag #' || _p.invcnt_tagnumber || ' for Item ' || _p.item_number),
                                 costcat_adjustment_accnt_id, costcat_asset_accnt_id, _invhistid,
                                 ( (_p.invcnt_qoh_after - _p.itemsite_qtyonhand) * _p.cost), _postDate::DATE )
    FROM invcnt, itemsite, costcat
    WHERE ( (invcnt_itemsite_id=itemsite_id)
     AND (itemsite_costcat_id=costcat_id)
     AND (invcnt_id=pInvcntid) );

    RETURN 0;

  ELSE
    RETURN -9;
  END IF;

END;
$_$;


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

--
-- TOC entry 2003 (class 1255 OID 146566813)
-- Dependencies: 4536 8
-- Name: postcounttaglocation(integer, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postcounttaglocation(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
  pInvcntid ALIAS FOR $1;
  pThaw ALIAS FOR $2;
  _avgCostingMethod TEXT;
  _invhistid INTEGER;
  _postDate TIMESTAMP;
  _runningQty NUMERIC;
  _errorCode INTEGER;
  _itemlocSeries INTEGER := 0;
  _hasDetail BOOLEAN;
  _p RECORD;
  _itemloc RECORD;
  _cntslip RECORD;
  _origLocQty NUMERIC;
  _netable BOOLEAN;
  _lsid INTEGER;
BEGIN

  SELECT COALESCE(fetchMetricText('CountAvgCostMethod'), 'STD') INTO _avgCostingMethod;

  SELECT invcnt_id, invcnt_tagnumber, invcnt_qoh_after,
         invcnt_location_id, invcnt_tagdate,
         item_number,
         itemsite_id, itemsite_freeze,
         itemsite_qtyonhand,
         itemsite_loccntrl, COALESCE(invcnt_location_id, -1) AS itemsite_location_id,
         CASE WHEN (itemsite_costmethod = 'N') THEN 0
              WHEN ( (itemsite_costmethod = 'A') AND
                     (itemsite_qtyonhand = 0) AND
                     (_avgCostingMethod = 'ACT') ) THEN actcost(itemsite_item_id)
              WHEN ( (itemsite_costmethod = 'A') AND
                     (_avgCostingMethod IN ('ACT', 'AVG')) ) THEN avgcost(itemsite_id)
              ELSE stdcost(itemsite_item_id)
         END AS cost, itemsite_costmethod,
         itemsite_controlmethod, itemsite_value INTO _p
  FROM invcnt, itemsite, item
  WHERE ( (invcnt_itemsite_id=itemsite_id)
   AND (itemsite_item_id=item_id)
   AND (invcnt_qoh_after IS NOT NULL)
   AND (NOT invcnt_posted)
   AND (invcnt_id=pInvcntid) );
  IF (NOT FOUND) THEN
    RETURN -9;
  END IF;

  SELECT COALESCE(SUM(itemloc_qty),0.0), location_netable INTO _origLocQty,_netable
    FROM itemloc,location
   WHERE ((itemloc_itemsite_id=_p.itemsite_id)
     AND  (location_id=itemloc_location_id)
     AND  (itemloc_location_id=_p.invcnt_location_id))
   GROUP BY location_netable;
  IF (NOT FOUND) THEN
    _origLocQty := 0.0;
    _netable := TRUE;
  END IF;

  SELECT NEXTVAL('invhist_invhist_id_seq') INTO _invhistid;

  IF (_p.itemsite_freeze) THEN
    _postDate := _p.invcnt_tagdate;
  ELSE
    _postDate := CURRENT_TIMESTAMP;
  END IF;

  _hasDetail = FALSE;

--  Post the detail indicated by cntslips
  IF ( (_p.itemsite_loccntrl) OR
       (_p.itemsite_controlmethod IN ('L', 'S')) ) THEN

    SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;

--  Adjust any existing detail to 0
    FOR _itemloc IN SELECT itemloc_id, itemloc_location_id,
                           itemloc_ls_id, itemloc_qty
                    FROM itemloc
                    WHERE ((itemloc_itemsite_id=_p.itemsite_id)
                      AND  (itemloc_location_id=_p.invcnt_location_id)) LOOP

      _hasDetail = TRUE;

--  Create the itemlocdist flushing records
      INSERT INTO itemlocdist
      ( itemlocdist_series, itemlocdist_source_type, itemlocdist_source_id,
        itemlocdist_expiration,
        itemlocdist_itemsite_id, itemlocdist_invhist_id, itemlocdist_flush )
      VALUES
      ( _itemlocSeries, 'I', _itemloc.itemloc_id,
        endOfTime(),
        _p.itemsite_id, _invhistid, TRUE );

    END LOOP;

--  Clear the running detail Qty
    _runningQty := 0;

--  Adjust the detail to the cntslip indicated value
    FOR _cntslip IN SELECT cntslip_location_id, cntslip_lotserial,
                           cntslip_lotserial_expiration,
                           cntslip_lotserial_warrpurc,
                           SUM(cntslip_qty) AS qty,
                           itemsite_item_id
                    FROM cntslip,invcnt,itemsite
                    WHERE ((cntslip_cnttag_id=pInvcntid)
                    AND (cntslip_cnttag_id=invcnt_id)
                    AND (invcnt_itemsite_id=itemsite_id))
                    GROUP BY cntslip_location_id, cntslip_lotserial, cntslip_lotserial_expiration,
                    cntslip_lotserial_warrpurc, itemsite_item_id LOOP

--  Handle the LotSerial
      IF (LENGTH(_cntslip.cntslip_lotserial)>0) THEN
        SELECT ls_id INTO _lsid
        FROM ls
        WHERE ((ls_item_id=_cntslip.itemsite_item_id)
        AND (UPPER(ls_number)=UPPER(_cntslip.cntslip_lotserial)));

        IF (NOT FOUND) THEN
          _lsid := NEXTVAL('ls_ls_id_seq');
          INSERT INTO ls
          VALUES (_lsid,_cntslip.itemsite_item_id,UPPER(_cntslip.cntslip_lotserial));
        END IF;
      END IF;
       
--  Track the running Qty
      _runningQty := (_runningQty + _cntslip.qty);
      _hasDetail = TRUE;

--  Create the itemlocdist populating record
      INSERT INTO itemlocdist
      ( itemlocdist_series, itemlocdist_source_type, itemlocdist_source_id,
        itemlocdist_itemsite_id,
        itemlocdist_ls_id, itemlocdist_expiration, itemlocdist_warranty,
        itemlocdist_qty, itemlocdist_invhist_id )
      VALUES
      ( _itemlocSeries, 'L', _cntslip.cntslip_location_id,
        _p.itemsite_id,
        _lsid, COALESCE(_cntslip.cntslip_lotserial_expiration, endOfTime()),
        _cntslip.cntslip_lotserial_warrpurc,
        _cntslip.qty, _invhistid );

    END LOOP;

    IF (_runningQty > _p.invcnt_qoh_after) THEN
--  The total Count Slip Qty is greater than the Count Tag Qty,
--  Don't post the Count.
      _errorCode = -1;

    ELSIF ( (_runningQty < _p.invcnt_qoh_after) AND
            (_p.itemsite_controlmethod IN ('L', 'S')) ) THEN
--  The total Count Slip Qty is less than the Count Tag Qty,
--  and the Item Site is Lot/Serial controlled.
--  Don't post the Count.
      _errorCode = -2;

    ELSIF (_runningQty < _p.invcnt_qoh_after) THEN
      IF ( (NOT _p.itemsite_loccntrl) OR
           (_p.itemsite_location_id = -1) ) THEN
--  The total Count Slip Qty is less than the Count Tag Qty,
--  and there isn't a default location to post into.
--  Don't post the Count.
        _errorCode = -3;

      ELSIF ( SELECT (metric_value='f')
              FROM metric
              WHERE (metric_name='PostCountTagToDefault') ) THEN
--  The total Count Slip Qty is less than the Count Tag Qty,
--  and we don't post Count Tags to default Locations
--  Don't post the Count.
        _errorCode = -4;

      ELSE
--  Distribute the remaining qty into the default location.
        INSERT INTO itemlocdist
        ( itemlocdist_series, itemlocdist_source_type, itemlocdist_source_id,
          itemlocdist_itemsite_id,
          itemlocdist_ls_id, itemlocdist_expiration,
          itemlocdist_qty, itemlocdist_invhist_id )
        SELECT _itemlocSeries, 'L', _p.itemsite_location_id,
               _p.itemsite_id,
               _lsid, endOfTime(),
               (_p.invcnt_qoh_after - _runningQty), _invhistid;

        _hasDetail = TRUE;
        _errorCode = 0;
      END IF;
    ELSE
--  The Count Slip Qty. must equal the Count Tag Qty.
      _errorCode = 0;
    END IF;

--  If we shouldn't post the count then delete the itemlocdist records,
--  and return with the error.
    IF (_errorCode <> 0) THEN
      DELETE FROM itemlocdist
      WHERE (itemlocdist_series=_itemlocSeries);
  
      RETURN _errorCode;
    END IF;

  END IF;

--  Mod. the Count Tag.
  UPDATE invcnt
  SET invcnt_qoh_before=_origLocQty,
      invcnt_postdate=_postDate,
      invcnt_posted=TRUE,
      invcnt_invhist_id=_invhistid,
      invcnt_post_username=getEffectiveXtUser()
  WHERE (invcnt_id=pInvcntid);

--  Create the CC transaction
  INSERT INTO invhist
   ( invhist_id, invhist_itemsite_id,
     invhist_transdate, invhist_transtype, invhist_invqty,
     invhist_qoh_before, invhist_qoh_after,
     invhist_docnumber, invhist_comments,
     invhist_invuom, invhist_unitcost, invhist_hasdetail,
     invhist_costmethod, invhist_value_before, invhist_value_after,
     invhist_series )
  SELECT _invhistid, itemsite_id,
         _postDate, 'CC', (invcnt_qoh_after - invcnt_qoh_before),
         invcnt_qoh_before, invcnt_qoh_after,
         invcnt_tagnumber, invcnt_comments,
         uom_name, _p.cost, _hasDetail,
         _p.itemsite_costmethod, _p.itemsite_value,
         _p.itemsite_value + (_p.cost * (invcnt_qoh_after - invcnt_qoh_before)),
         _itemlocSeries
  FROM itemsite, invcnt, item, uom
  WHERE ( (invcnt_itemsite_id=itemsite_id)
   AND (itemsite_item_id=item_id)
   AND (item_inv_uom_id=uom_id)
   AND (itemsite_controlmethod <> 'N')
   AND (invcnt_id=pInvcntid) );

--  Update the QOH
  IF (_netable) THEN
    UPDATE itemsite
    SET itemsite_qtyonhand= itemsite_qtyonhand + (_p.invcnt_qoh_after - _origLocQty),
        itemsite_datelastcount=_postDate
    WHERE (itemsite_id=_p.itemsite_id);
  ELSE
    UPDATE itemsite
    SET itemsite_nnqoh =  itemsite_nnqoh - _origLocQty,
	itemsite_qtyonhand = itemsite_qtyonhand + _p.invcnt_qoh_after,
        itemsite_datelastcount=_postDate
    WHERE (itemsite_id=_p.itemsite_id);
  END IF;
 
--  Post the detail, if any
  IF (_hasDetail) THEN
    PERFORM distributeItemlocSeries(_itemlocSeries);
  END IF;

--  Thaw the itemsite if it's frozen
  IF (pThaw) THEN
    PERFORM thawItemSite(invcnt_itemsite_id) 
    FROM invcnt
    WHERE (invcnt_id=pInvcntid);
  END IF;

--  Distribute to G/L
  PERFORM insertGLTransaction( 'I/M', 'CT', _p.invcnt_tagnumber, ('Post Count Tag #' || _p.invcnt_tagnumber || ' for Item ' || _p.item_number),
                               costcat_adjustment_accnt_id, costcat_asset_accnt_id, _invhistid,
                               ( (_p.invcnt_qoh_after - _origLocQty) * _p.cost), CURRENT_DATE )
  FROM invcnt, itemsite, costcat
  WHERE ( (invcnt_itemsite_id=itemsite_id)
   AND (itemsite_costcat_id=costcat_id)
   AND (invcnt_id=pInvcntid) );

  RETURN 0;
END;
$_$;


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

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

CREATE FUNCTION postcounttaglocation(integer, boolean, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
  pInvcntid ALIAS FOR $1;
  pThaw ALIAS FOR $2;
  pAvgCostingMethod ALIAS FOR $3;
  _invhistid INTEGER;
  _postDate TIMESTAMP;
  _runningQty NUMERIC;
  _errorCode INTEGER;
  _itemlocSeries INTEGER := 0;
  _hasDetail BOOLEAN;
  _p RECORD;
  _itemloc RECORD;
  _cntslip RECORD;
  _origLocQty NUMERIC;
  _netable BOOLEAN;
  _lsid INTEGER;
BEGIN

  SELECT invcnt_id, invcnt_tagnumber, invcnt_qoh_after,
         invcnt_location_id, invcnt_tagdate,
         item_number,
         itemsite_id, itemsite_freeze,
         itemsite_qtyonhand,
         itemsite_loccntrl, COALESCE(invcnt_location_id, -1) AS itemsite_location_id,
         CASE WHEN (itemsite_costmethod = 'N') THEN 0
              WHEN ( (itemsite_costmethod = 'A') AND
                     (itemsite_qtyonhand = 0) AND
                     (pAvgCostingMethod = 'ACT') ) THEN actcost(itemsite_item_id)
              WHEN ( (itemsite_costmethod = 'A') AND
                     (pAvgCostingMethod IN ('ACT', 'AVG')) ) THEN avgcost(itemsite_id)
              ELSE stdcost(itemsite_item_id)
         END AS cost, itemsite_costmethod,
         itemsite_controlmethod, itemsite_value INTO _p
  FROM invcnt, itemsite, item
  WHERE ( (invcnt_itemsite_id=itemsite_id)
   AND (itemsite_item_id=item_id)
   AND (invcnt_qoh_after IS NOT NULL)
   AND (NOT invcnt_posted)
   AND (invcnt_id=pInvcntid) );
  IF (NOT FOUND) THEN
    RETURN -9;
  END IF;

  SELECT COALESCE(SUM(itemloc_qty),0.0), location_netable INTO _origLocQty,_netable
    FROM itemloc,location
   WHERE ((itemloc_itemsite_id=_p.itemsite_id)
     AND  (location_id=itemloc_location_id)
     AND  (itemloc_location_id=_p.invcnt_location_id))
   GROUP BY location_netable;
  IF (NOT FOUND) THEN
    _origLocQty := 0.0;
    _netable := TRUE;
  END IF;

  SELECT NEXTVAL('invhist_invhist_id_seq') INTO _invhistid;

  IF (_p.itemsite_freeze) THEN
    _postDate := _p.invcnt_tagdate;
  ELSE
    _postDate := CURRENT_TIMESTAMP;
  END IF;

  _hasDetail = FALSE;

--  Post the detail indicated by cntslips
  IF ( (_p.itemsite_loccntrl) OR
       (_p.itemsite_controlmethod IN ('L', 'S')) ) THEN

    SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;

--  Adjust any existing detail to 0
    FOR _itemloc IN SELECT itemloc_id, itemloc_location_id,
                           itemloc_ls_id, itemloc_qty
                    FROM itemloc
                    WHERE ((itemloc_itemsite_id=_p.itemsite_id)
                      AND  (itemloc_location_id=_p.invcnt_location_id)) LOOP

      _hasDetail = TRUE;

--  Create the itemlocdist flushing records
      INSERT INTO itemlocdist
      ( itemlocdist_series, itemlocdist_source_type, itemlocdist_source_id,
        itemlocdist_expiration,
        itemlocdist_itemsite_id, itemlocdist_invhist_id, itemlocdist_flush )
      VALUES
      ( _itemlocSeries, 'I', _itemloc.itemloc_id,
        endOfTime(),
        _p.itemsite_id, _invhistid, TRUE );

    END LOOP;

--  Clear the running detail Qty
    _runningQty := 0;

--  Adjust the detail to the cntslip indicated value
    FOR _cntslip IN SELECT cntslip_location_id, cntslip_lotserial,
                           cntslip_lotserial_expiration,
                           cntslip_lotserial_warrpurc,
                           SUM(cntslip_qty) AS qty,
                           itemsite_item_id
                    FROM cntslip,invcnt,itemsite
                    WHERE ((cntslip_cnttag_id=pInvcntid)
                    AND (cntslip_cnttag_id=invcnt_id)
                    AND (invcnt_itemsite_id=itemsite_id))
                    GROUP BY cntslip_location_id, cntslip_lotserial, cntslip_lotserial_expiration,
                    cntslip_lotserial_warrpurc, itemsite_item_id LOOP

--  Handle the LotSerial
      IF (LENGTH(_cntslip.cntslip_lotserial)>0) THEN
        SELECT ls_id INTO _lsid
        FROM ls
        WHERE ((ls_item_id=_cntslip.itemsite_item_id)
        AND (UPPER(ls_number)=UPPER(_cntslip.cntslip_lotserial)));

        IF (NOT FOUND) THEN
          _lsid := NEXTVAL('ls_ls_id_seq');
          INSERT INTO ls
          VALUES (_lsid,_cntslip.itemsite_item_id,UPPER(_cntslip.cntslip_lotserial));
        END IF;
      END IF;
       
--  Track the running Qty
      _runningQty := (_runningQty + _cntslip.qty);
      _hasDetail = TRUE;

--  Create the itemlocdist populating record
      INSERT INTO itemlocdist
      ( itemlocdist_series, itemlocdist_source_type, itemlocdist_source_id,
        itemlocdist_itemsite_id,
        itemlocdist_ls_id, itemlocdist_expiration, itemlocdist_warranty,
        itemlocdist_qty, itemlocdist_invhist_id )
      VALUES
      ( _itemlocSeries, 'L', _cntslip.cntslip_location_id,
        _p.itemsite_id,
        _lsid, COALESCE(_cntslip.cntslip_lotserial_expiration, endOfTime()),
        _cntslip.cntslip_lotserial_warrpurc,
        _cntslip.qty, _invhistid );

    END LOOP;

    IF (_runningQty > _p.invcnt_qoh_after) THEN
--  The total Count Slip Qty is greater than the Count Tag Qty,
--  Don't post the Count.
      _errorCode = -1;

    ELSIF ( (_runningQty < _p.invcnt_qoh_after) AND
            (_p.itemsite_controlmethod IN ('L', 'S')) ) THEN
--  The total Count Slip Qty is less than the Count Tag Qty,
--  and the Item Site is Lot/Serial controlled.
--  Don't post the Count.
      _errorCode = -2;

    ELSIF (_runningQty < _p.invcnt_qoh_after) THEN
      IF ( (NOT _p.itemsite_loccntrl) OR
           (_p.itemsite_location_id = -1) ) THEN
--  The total Count Slip Qty is less than the Count Tag Qty,
--  and there isn't a default location to post into.
--  Don't post the Count.
        _errorCode = -3;

      ELSIF ( SELECT (metric_value='f')
              FROM metric
              WHERE (metric_name='PostCountTagToDefault') ) THEN
--  The total Count Slip Qty is less than the Count Tag Qty,
--  and we don't post Count Tags to default Locations
--  Don't post the Count.
        _errorCode = -4;

      ELSE
--  Distribute the remaining qty into the default location.
        INSERT INTO itemlocdist
        ( itemlocdist_series, itemlocdist_source_type, itemlocdist_source_id,
          itemlocdist_itemsite_id,
          itemlocdist_ls_id, itemlocdist_expiration,
          itemlocdist_qty, itemlocdist_invhist_id )
        SELECT _itemlocSeries, 'L', _p.itemsite_location_id,
               _p.itemsite_id,
               _lsid, endOfTime(),
               (_p.invcnt_qoh_after - _runningQty), _invhistid;

        _hasDetail = TRUE;
        _errorCode = 0;
      END IF;
    ELSE
--  The Count Slip Qty. must equal the Count Tag Qty.
      _errorCode = 0;
    END IF;

--  If we shouldn't post the count then delete the itemlocdist records,
--  and return with the error.
    IF (_errorCode <> 0) THEN
      DELETE FROM itemlocdist
      WHERE (itemlocdist_series=_itemlocSeries);
  
      RETURN _errorCode;
    END IF;

  END IF;

--  Mod. the Count Tag.
  UPDATE invcnt
  SET invcnt_qoh_before=_origLocQty,
      invcnt_postdate=_postDate,
      invcnt_posted=TRUE,
      invcnt_invhist_id=_invhistid,
      invcnt_post_username=CURRENT_USER
  WHERE (invcnt_id=pInvcntid);

--  Create the CC transaction
  INSERT INTO invhist
   ( invhist_id, invhist_itemsite_id,
     invhist_transdate, invhist_transtype, invhist_invqty,
     invhist_qoh_before, invhist_qoh_after,
     invhist_docnumber, invhist_comments,
     invhist_invuom, invhist_unitcost, invhist_hasdetail,
     invhist_costmethod, invhist_value_before, invhist_value_after,
     invhist_series )
  SELECT _invhistid, itemsite_id,
         _postDate, 'CC', (invcnt_qoh_after - invcnt_qoh_before),
         invcnt_qoh_before, invcnt_qoh_after,
         invcnt_tagnumber, invcnt_comments,
         uom_name, _p.cost, _hasDetail,
         _p.itemsite_costmethod, _p.itemsite_value,
         _p.itemsite_value + (_p.cost * (invcnt_qoh_after - invcnt_qoh_before)),
         _itemlocSeries
  FROM itemsite, invcnt, item, uom
  WHERE ( (invcnt_itemsite_id=itemsite_id)
   AND (itemsite_item_id=item_id)
   AND (item_inv_uom_id=uom_id)
   AND (itemsite_controlmethod <> 'N')
   AND (invcnt_id=pInvcntid) );

--  Update the QOH
  IF (_netable) THEN
    UPDATE itemsite
    SET itemsite_qtyonhand= itemsite_qtyonhand + (_p.invcnt_qoh_after - _origLocQty),
        itemsite_datelastcount=_postDate
    WHERE (itemsite_id=_p.itemsite_id);
  ELSE
    UPDATE itemsite
    SET itemsite_nnqoh =  itemsite_nnqoh - _origLocQty,
	itemsite_qtyonhand = itemsite_qtyonhand + _p.invcnt_qoh_after,
        itemsite_datelastcount=_postDate
    WHERE (itemsite_id=_p.itemsite_id);
  END IF;
 
--  Post the detail, if any
  IF (_hasDetail) THEN
    PERFORM distributeItemlocSeries(_itemlocSeries);
  END IF;

--  Thaw the itemsite if it's frozen
  IF (pThaw) THEN
    PERFORM thawItemSite(invcnt_itemsite_id) 
    FROM invcnt
    WHERE (invcnt_id=pInvcntid);
  END IF;

--  Distribute to G/L
  PERFORM insertGLTransaction( 'I/M', 'CT', _p.invcnt_tagnumber, ('Post Count Tag #' || _p.invcnt_tagnumber || ' for Item ' || _p.item_number),
                               costcat_adjustment_accnt_id, costcat_asset_accnt_id, _invhistid,
                               ( (_p.invcnt_qoh_after - _origLocQty) * _p.cost), CURRENT_DATE )
  FROM invcnt, itemsite, costcat
  WHERE ( (invcnt_itemsite_id=itemsite_id)
   AND (itemsite_costcat_id=costcat_id)
   AND (invcnt_id=pInvcntid) );

  RETURN 0;
END;
$_$;


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

--
-- TOC entry 2005 (class 1255 OID 146566817)
-- Dependencies: 4536 8
-- Name: postcounttags(integer, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postcounttags(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
  pWarehousid ALIAS FOR $1;
  pThaw ALIAS FOR $2;
  _invcnt RECORD;
  _result INTEGER := 0;
  _return INTEGER := 0;

BEGIN

  FOR _invcnt IN SELECT invcnt_id
                 FROM invcnt, itemsite
                 WHERE ( (invcnt_itemsite_id=itemsite_id)
                  AND ( (pWarehousid=-1) OR (itemsite_warehous_id=pWarehousid) )
                  AND (invcnt_qoh_after IS NOT NULL)
                  AND (NOT invcnt_posted) ) LOOP
    SELECT postCountTag(_invcnt.invcnt_id, pThaw) INTO _result;
    IF (_result < _return) THEN
      _return := _result;
    END IF;
  END LOOP;

  RETURN _return;

END;
$_$;


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

--
-- TOC entry 2006 (class 1255 OID 146566818)
-- Dependencies: 4536 8
-- Name: postcreditmemo(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postcreditmemo(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
  pCmheadid ALIAS FOR $1;
  pItemlocSeries ALIAS FOR $2;
  _return INTEGER;

BEGIN

  SELECT postCreditMemo(pCmheadid, fetchJournalNumber('AR-CM'), pItemlocSeries) INTO _return;

  RETURN _return;

END;
$_$;


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

--
-- TOC entry 2007 (class 1255 OID 146566819)
-- Dependencies: 4536 8
-- Name: postcreditmemo(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postcreditmemo(integer, integer, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pCmheadid ALIAS FOR $1;
  pJournalNumber ALIAS FOR $2;
  pItemlocSeries ALIAS FOR $3;
  _r RECORD;
  _p RECORD;
  _aropenid INTEGER;
  _cohistid INTEGER;
  _sequence INTEGER;
  _itemlocSeries INTEGER;
  _invhistid INTEGER;
  _test INTEGER;
  _totalAmount NUMERIC   := 0;
  _commissionDue NUMERIC := 0;
  _toApply NUMERIC;
  _toClose BOOLEAN;
  _glDate	DATE;
  _taxBaseValue	NUMERIC	:= 0;

BEGIN

--  Cache some parameters
  SELECT cmhead.*,
         findARAccount(cmhead_cust_id) AS ar_accnt_id,
         ( SELECT COALESCE(SUM(taxhist_tax), 0)
           FROM cmheadtax
           WHERE ( (taxhist_parent_id = cmhead_id)
             AND   (taxhist_taxtype_id = getAdjustmentTaxtypeId()) ) ) AS adjtax
         INTO _p
  FROM cmhead
  WHERE (cmhead_id=pCmheadid);

  IF (_p.cmhead_posted) THEN
    RETURN -10;
  END IF;

  IF (_p.cmhead_hold) THEN
    RETURN -11;
  END IF;

  _glDate := COALESCE(_p.cmhead_gldistdate, _p.cmhead_docdate);

  _itemlocSeries = pItemlocSeries;

  SELECT fetchGLSequence() INTO _sequence;

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

    PERFORM insertIntoGLSeries( _sequence, 'A/R', 'CM', _p.cmhead_number,
                                _r.tax_sales_accnt_id, 
                                _r.taxbasevalue,
                                _glDate, _p.cmhead_billtoname );

    _totalAmount := _totalAmount + _r.tax * -1;
  END LOOP;

-- Update item tax records with posting data
  UPDATE cmitemtax SET 
    taxhist_docdate=_p.cmhead_docdate,
    taxhist_distdate=_glDate,
    taxhist_curr_id=_p.cmhead_curr_id,
    taxhist_curr_rate=curr_rate,
    taxhist_journalnumber=pJournalNumber
  FROM cmhead
   JOIN cmitem ON (cmhead_id=cmitem_cmhead_id),
   curr_rate
  WHERE ((cmhead_id=pCmheadId)
    AND (taxhist_parent_id=cmitem_id)
    AND (_p.cmhead_curr_id=curr_id)
    AND (_p.cmhead_docdate BETWEEN curr_effective 
                           AND curr_expires) );

-- Update Header taxes (Freight and Adjustments) with posting data
  UPDATE cmheadtax SET 
    taxhist_docdate=_p.cmhead_docdate,
    taxhist_distdate=_glDate,
    taxhist_curr_id=_p.cmhead_curr_id,
    taxhist_curr_rate=curr_rate,
    taxhist_journalnumber=pJournalNumber
  FROM curr_rate
  WHERE ((taxhist_parent_id=pCmheadId)
    AND (_p.cmhead_curr_id=curr_id)
    AND (_p.cmhead_docdate BETWEEN curr_effective 
                           AND curr_expires) );

-- Process line items
-- Always use std cost
  FOR _r IN SELECT *, stdCost(item_id) AS std_cost
            FROM creditmemoitem
            WHERE ( (cmitem_cmhead_id=pCmheadid)
              AND   (cmitem_qtycredit <> 0 ) ) LOOP

--  Calcuate the Commission to be debited
    _commissionDue := (_commissionDue + (_r.extprice * _p.cmhead_commission));

    IF (_r.extprice <> 0) THEN
--  Debit the Sales Account for the current cmitem
      SELECT insertIntoGLSeries( _sequence, 'A/R', 'CM', _p.cmhead_number,
                                 CASE WHEN _p.cmhead_rahead_id IS NULL THEN
                                   getPrjAccntId(_p.cmhead_prj_id, salesaccnt_credit_accnt_id)
                                 ELSE
                                   getPrjAccntId(_p.cmhead_prj_id, salesaccnt_returns_accnt_id)
                                 END,
                               round(currToBase(_p.cmhead_curr_id,
                                                _r.extprice * -1,
                                                _p.cmhead_docdate), 2),
                                 _glDate, _p.cmhead_billtoname) INTO _test
      FROM salesaccnt
      WHERE (salesaccnt_id=findSalesAccnt(_r.cmitem_itemsite_id, 'IS', _p.cmhead_cust_id,
                                          _p.cmhead_saletype_id, _p.cmhead_shipzone_id));
      IF (NOT FOUND) THEN
        PERFORM deleteGLSeries(_sequence);
        RETURN -12;
      END IF;
    END IF;

--  Record Sales History for this C/M Item
    SELECT nextval('cohist_cohist_id_seq') INTO _cohistid;
    INSERT INTO cohist
    ( cohist_id, cohist_cust_id, cohist_itemsite_id, cohist_shipto_id,
      cohist_shipdate, cohist_shipvia,
      cohist_ordernumber, cohist_ponumber, cohist_orderdate,
      cohist_doctype, cohist_invcnumber, cohist_invcdate,
      cohist_qtyshipped, cohist_unitprice, cohist_unitcost,
      cohist_salesrep_id, cohist_commission, cohist_commissionpaid,
      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_curr_id, cohist_taxtype_id, cohist_taxzone_id,
      cohist_shipzone_id, cohist_saletype_id )
    VALUES
    ( _cohistid, _p.cmhead_cust_id, _r.cmitem_itemsite_id, _p.cmhead_shipto_id,
      _p.cmhead_docdate, '',
      _p.cmhead_number, _p.cmhead_custponumber, _p.cmhead_docdate,
      'C', _p.cmhead_invcnumber, _p.cmhead_docdate,
      (_r.qty * -1), _r.unitprice, _r.std_cost,
      _p.cmhead_salesrep_id, (_p.cmhead_commission * _r.extprice * -1), FALSE,
      _p.cmhead_billtoname, _p.cmhead_billtoaddress1,
      _p.cmhead_billtoaddress2, _p.cmhead_billtoaddress3,
      _p.cmhead_billtocity, _p.cmhead_billtostate, _p.cmhead_billtozip,
      _p.cmhead_shipto_name, _p.cmhead_shipto_address1,
      _p.cmhead_shipto_address2, _p.cmhead_shipto_address3,
      _p.cmhead_shipto_city, _p.cmhead_shipto_state, _p.cmhead_shipto_zipcode,
      _p.cmhead_curr_id, _r.cmitem_taxtype_id, _p.cmhead_taxzone_id,
      _p.cmhead_shipzone_id, _p.cmhead_saletype_id );
    INSERT INTO cohisttax
    ( 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 _cohistid, 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 cmitemtax
    WHERE (taxhist_parent_id=_r.cmitem_id);

    _totalAmount := _totalAmount + round(_r.extprice, 2);

  END LOOP;

--  Credit the Misc. Account for Miscellaneous Charges
  IF (_p.cmhead_misc <> 0) THEN
    SELECT insertIntoGLSeries( _sequence, 'A/R', 'CM', _p.cmhead_number,
                               getPrjAccntId(_p.cmhead_prj_id, accnt_id), round(currToBase(_p.cmhead_curr_id,
                                                          _p.cmhead_misc * -1,
                                                          _p.cmhead_docdate), 2),
                               _glDate, _p.cmhead_billtoname) INTO _test
    FROM accnt
    WHERE (accnt_id=_p.cmhead_misc_accnt_id);

--  If the Misc. Charges Account was not found then punt
    IF (NOT FOUND) THEN
      PERFORM deleteGLSeries(_sequence);
      RETURN -14;
    END IF;

--  Record the Sales History for any Misc. Charge
    INSERT INTO cohist
    ( cohist_cust_id, cohist_itemsite_id, cohist_shipto_id,
      cohist_misc_type, cohist_misc_descrip, cohist_misc_id,
      cohist_shipdate, cohist_shipvia,
      cohist_ordernumber, cohist_ponumber, cohist_orderdate,
      cohist_doctype, cohist_invcnumber, cohist_invcdate,
      cohist_qtyshipped, cohist_unitprice, cohist_unitcost,
      cohist_salesrep_id, cohist_commission, cohist_commissionpaid,
      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_curr_id,
      cohist_shipzone_id, cohist_saletype_id )
    VALUES
    ( _p.cmhead_cust_id, -1, _p.cmhead_shipto_id,
      'M', _p.cmhead_misc_descrip, _p.cmhead_misc_accnt_id,
      _p.cmhead_docdate, '',
      _p.cmhead_number, _p.cmhead_custponumber, _p.cmhead_docdate,
      'C', _p.cmhead_invcnumber, _p.cmhead_docdate,
      1, (_p.cmhead_misc * -1), (_p.cmhead_misc * -1),
      _p.cmhead_salesrep_id, 0, FALSE,
      _p.cmhead_billtoname, _p.cmhead_billtoaddress1,
      _p.cmhead_billtoaddress2, _p.cmhead_billtoaddress3,
      _p.cmhead_billtocity, _p.cmhead_billtostate, _p.cmhead_billtozip,
      _p.cmhead_shipto_name, _p.cmhead_shipto_address1,
      _p.cmhead_shipto_address2, _p.cmhead_shipto_address3,
      _p.cmhead_shipto_city, _p.cmhead_shipto_state, _p.cmhead_shipto_zipcode,
      _p.cmhead_curr_id,
      _p.cmhead_shipzone_id, _p.cmhead_saletype_id );

--  Cache the Misc. Amount distributed
    _totalAmount := _totalAmount + _p.cmhead_misc;
  END IF;

  -- Credit Tax Adjustments
  IF (_p.adjtax <> 0) THEN
  --  Record the Sales History for Tax Adjustment
    SELECT nextval('cohist_cohist_id_seq') INTO _cohistid;
    INSERT INTO cohist
    ( cohist_id, cohist_cust_id, cohist_itemsite_id, cohist_shipto_id,
      cohist_misc_type, cohist_misc_descrip,
      cohist_shipdate, cohist_shipvia,
      cohist_ordernumber, cohist_ponumber, cohist_orderdate,
      cohist_doctype, cohist_invcnumber, cohist_invcdate,
      cohist_qtyshipped, cohist_unitprice, cohist_unitcost,
      cohist_salesrep_id, cohist_commission, cohist_commissionpaid,
      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_curr_id, cohist_taxtype_id, cohist_taxzone_id,
      cohist_shipzone_id, cohist_saletype_id )
    VALUES
    ( _cohistid, _p.cmhead_cust_id, -1, _p.cmhead_shipto_id,
      'T', 'Misc Tax Adjustment',
      _p.cmhead_docdate, '',
      _p.cmhead_number, _p.cmhead_custponumber, _p.cmhead_docdate,
      'C', _p.cmhead_invcnumber, _p.cmhead_docdate,
      0, 0, 0,
      _p.cmhead_salesrep_id, 0, FALSE,
      _p.cmhead_billtoname, _p.cmhead_billtoaddress1,
      _p.cmhead_billtoaddress2, _p.cmhead_billtoaddress3,
      _p.cmhead_billtocity, _p.cmhead_billtostate, _p.cmhead_billtozip,
      _p.cmhead_shipto_name, _p.cmhead_shipto_address1,
      _p.cmhead_shipto_address2, _p.cmhead_shipto_address3,
      _p.cmhead_shipto_city, _p.cmhead_shipto_state, _p.cmhead_shipto_zipcode,
      _p.cmhead_curr_id, getAdjustmentTaxtypeId(), _p.cmhead_taxzone_id,
      _p.cmhead_shipzone_id, _p.cmhead_saletype_id );
    INSERT INTO cohisttax
    ( 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 _cohistid, taxhist_taxtype_id, taxhist_tax_id,
           (taxhist_basis * -1), 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 cmheadtax
    WHERE ( (taxhist_parent_id=_p.cmhead_id)
      AND   (taxhist_taxtype_id=getAdjustmentTaxtypeId()) );

  END IF;

--  Debit the Freight Account
  IF (_p.cmhead_freight <> 0) THEN
    SELECT insertIntoGLSeries( _sequence, 'A/R', 'CM', _p.cmhead_number,
                               getPrjAccntId(_p.cmhead_prj_id, accnt_id),
                               round(currToBase(_p.cmhead_curr_id,
                                                _p.cmhead_freight * -1,
                                                _p.cmhead_docdate), 2),
                               _glDate, _p.cmhead_billtoname) INTO _test
    FROM accnt
    WHERE (accnt_id=findFreightAccount(_p.cmhead_cust_id));

--  If the Freight Charges Account was not found then punt
    IF (NOT FOUND) THEN
      PERFORM deleteGLSeries(_sequence);
      RETURN -16;
    END IF;

--  Cache the Amount Distributed to Freight
    _totalAmount := _totalAmount + _p.cmhead_freight;

--  Record the Sales History for any Freight
    SELECT nextval('cohist_cohist_id_seq') INTO _cohistid;
    INSERT INTO cohist
    ( cohist_id, cohist_cust_id, cohist_itemsite_id, cohist_shipto_id,
      cohist_misc_type, cohist_misc_descrip,
      cohist_shipdate, cohist_shipvia,
      cohist_ordernumber, cohist_ponumber, cohist_orderdate,
      cohist_doctype, cohist_invcnumber, cohist_invcdate,
      cohist_qtyshipped, cohist_unitprice, cohist_unitcost,
      cohist_salesrep_id, cohist_commission, cohist_commissionpaid,
      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_curr_id, cohist_taxtype_id, cohist_taxzone_id,
      cohist_shipzone_id, cohist_saletype_id )
    VALUES
    ( _cohistid, _p.cmhead_cust_id, -1, _p.cmhead_shipto_id,
      'F', 'Freight Charge',
      _p.cmhead_docdate, '',
      _p.cmhead_number, _p.cmhead_custponumber, _p.cmhead_docdate,
      'C', _p.cmhead_invcnumber, _p.cmhead_docdate,
      1, (_p.cmhead_freight * -1), (_p.cmhead_freight * -1),
      _p.cmhead_salesrep_id, 0, FALSE,
      _p.cmhead_billtoname, _p.cmhead_billtoaddress1,
      _p.cmhead_billtoaddress2, _p.cmhead_billtoaddress3,
      _p.cmhead_billtocity, _p.cmhead_billtostate, _p.cmhead_billtozip,
      _p.cmhead_shipto_name, _p.cmhead_shipto_address1,
      _p.cmhead_shipto_address2, _p.cmhead_shipto_address3,
      _p.cmhead_shipto_city, _p.cmhead_shipto_state, _p.cmhead_shipto_zipcode,
      _p.cmhead_curr_id, getFreightTaxtypeId(), _p.cmhead_taxzone_id,
      _p.cmhead_shipzone_id, _p.cmhead_saletype_id );
    INSERT INTO cohisttax
    ( 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 _cohistid, taxhist_taxtype_id, taxhist_tax_id,
           (taxhist_basis * -1), 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 cmheadtax
    WHERE ( (taxhist_parent_id=_p.cmhead_id)
      AND   (taxhist_taxtype_id=getFreightTaxtypeId()) );

  END IF;

  _totalAmount := _totalAmount;

--  Credit the A/R for the total Amount
  IF (_totalAmount <> 0) THEN
    IF (_p.ar_accnt_id != -1) THEN
      PERFORM insertIntoGLSeries( _sequence, 'A/R', 'CM', _p.cmhead_number,
                                  _p.ar_accnt_id,
                                  round(currToBase(_p.cmhead_curr_id,
                                                   _totalAmount,
                                                   _p.cmhead_docdate), 2),
                                  _glDate, _p.cmhead_billtoname);
    ELSE
      PERFORM deleteGLSeries(_sequence);
      RETURN -18;
    END IF;
  END IF;

--  Commit the GLSeries;
  PERFORM postGLSeries(_sequence, pJournalNumber);

--  Create the Invoice aropen item
  SELECT NEXTVAL('aropen_aropen_id_seq') INTO _aropenid;
  INSERT INTO aropen
  ( aropen_id, aropen_username, aropen_journalnumber,
    aropen_open, aropen_posted,
    aropen_cust_id, aropen_ponumber,
    aropen_docnumber,
    aropen_applyto, aropen_doctype,
    aropen_docdate, aropen_duedate, aropen_distdate, aropen_terms_id,
    aropen_amount, aropen_paid,
    aropen_salesrep_id, aropen_commission_due, aropen_commission_paid,
    aropen_ordernumber, aropen_notes,
    aropen_rsncode_id, aropen_curr_id )
  SELECT _aropenid, getEffectiveXtUser(), pJournalNumber,
         TRUE, FALSE,
         cmhead_cust_id, cmhead_custponumber,
         cmhead_number,
         CASE WHEN (cmhead_invcnumber='-1') THEN 'OPEN'
              ELSE (cmhead_invcnumber::TEXT)
         END,
         'C',
         cmhead_docdate, cmhead_docdate, _glDate, -1,
         _totalAmount, 0,
         cmhead_salesrep_id, (_commissionDue * -1), FALSE,
         cmhead_number::TEXT, cmhead_comments,
         cmhead_rsncode_id, cmhead_curr_id
  FROM cmhead
  WHERE (cmhead_id=pCmheadid);

-- Handle the Inventory and G/L Transactions for any returned Inventory where cmitem_updateinv is true
  FOR _r IN SELECT cmitem_itemsite_id AS itemsite_id, cmitem_id,
                   (cmitem_qtyreturned * cmitem_qty_invuomratio) AS qty,
                   cmhead_number, cmhead_cust_id AS cust_id, item_number,
                   cmhead_saletype_id AS saletype_id, cmhead_shipzone_id AS shipzone_id,
                   stdCost(item_id) AS std_cost, cmhead_prj_id,
                   itemsite_costmethod
            FROM cmhead, cmitem, itemsite, item
            WHERE ( (cmitem_cmhead_id=cmhead_id)
             AND (cmitem_itemsite_id=itemsite_id)
             AND (itemsite_item_id=item_id)
             AND (cmitem_qtyreturned <> 0)
             AND (cmitem_updateinv)
             AND (cmhead_id=pCmheadid) ) LOOP

--  Return credited stock to inventory
    IF (_itemlocSeries = 0) THEN
      _itemlocSeries := NEXTVAL('itemloc_series_seq');
    END IF;
    IF (_r.itemsite_costmethod != 'J') THEN
      SELECT postInvTrans(itemsite_id, 'RS', _r.qty,
                         'S/O', 'CM', _r.cmhead_number, '',
                         ('Credit Return ' || _r.item_number),
                         costcat_asset_accnt_id,
                         getPrjAccntId(_r.cmhead_prj_id, resolveCOSAccount(itemsite_id, _r.cust_id, _r.saletype_id, _r.shipzone_id)), 
                         _itemlocSeries, _glDate, _r.std_cost) INTO _invhistid
        FROM itemsite, costcat
       WHERE ((itemsite_costcat_id=costcat_id)
          AND (itemsite_id=_r.itemsite_id));
    ELSE
      RAISE DEBUG 'postCreditMemo(%, %, %) tried to postInvTrans a %-costed item',
                  pCmheadid, pJournalNumber, pItemlocSeries,
                  _r.itemsite_costmethod;
    END IF;

  END LOOP;

--  Update coitem to reflect the returned qty where cmitem_updateinv is true
  FOR _r IN SELECT cmitem_qtyreturned, cmitem_itemsite_id, cohead_id
            FROM cmitem, cmhead, invchead, cohead
            WHERE ( (cmitem_cmhead_id=cmhead_id)
             AND (cmhead_invcnumber=invchead_invcnumber)
             AND (invchead_ordernumber=cohead_number)
             AND (cmitem_qtyreturned <> 0)
             AND (cmitem_updateinv)
             AND (cmhead_id=pCmheadid) ) LOOP
    UPDATE coitem
    SET coitem_qtyreturned = (coitem_qtyreturned + _r.cmitem_qtyreturned)
    WHERE coitem_id IN ( SELECT coitem_id
                         FROM coitem
                         WHERE ( (coitem_cohead_id=_r.cohead_id)
                          AND (coitem_itemsite_id = _r.cmitem_itemsite_id) )
                         LIMIT 1 );
  END LOOP;

--  Mark the cmhead as posted
  UPDATE cmhead
  SET cmhead_posted=TRUE, cmhead_gldistdate=_glDate
  WHERE (cmhead_id=pCmheadid);

--  Find the apply-to document and make the application
  SELECT cmhead_number, cmhead_curr_id, cmhead_docdate,
         aropen_id, aropen_cust_id, aropen_docnumber,
         currToCurr(aropen_curr_id, cmhead_curr_id, aropen_amount - aropen_paid,
                    cmhead_docdate) AS balance INTO _p
  FROM aropen, cmhead
  WHERE ( (aropen_doctype='I')
   AND (aropen_docnumber=cmhead_invcnumber)
   AND (cmhead_id=pCmheadid) );
  IF (FOUND) THEN

    IF round(_totalAmount, 2) <= round(_p.balance, 2) THEN
      _toApply = _totalAmount;
    ELSE
      _toApply = _p.balance;
    END IF;

    UPDATE aropen
    SET aropen_paid = round(aropen_paid + currToCurr(_p.cmhead_curr_id,
                                                     aropen_curr_id, _toApply,
                                                     _p.cmhead_docdate), 2)
    WHERE (aropen_id=_p.aropen_id);

--  Alter the new A/R Open Item to reflect the application
    UPDATE aropen
    SET aropen_paid = round(currToCurr(_p.cmhead_curr_id, aropen_curr_id,
                                       _toApply, _p.cmhead_docdate), 2)
    WHERE (aropen_id=_aropenid);

--  Record the 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_curr_id )
    VALUES
    ( _p.aropen_cust_id,
      _aropenid, 'C', _p.cmhead_number,
      _p.aropen_id, 'I', _p.aropen_docnumber,
      '', '',
      round(_toApply, 2), _toClose,
      CURRENT_DATE, _p.cmhead_docdate, 0, _p.cmhead_curr_id );

  END IF;
    
  RETURN _itemlocSeries;

END;
$_$;


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

--
-- TOC entry 2008 (class 1255 OID 146566821)
-- Dependencies: 4536 8
-- Name: postcreditmemos(boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postcreditmemos(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
  pPostUnprinted ALIAS FOR $1;
  _cmhead RECORD;
  _result INTEGER;
  _return INTEGER        := 0;
  _itemlocSeries INTEGER := 0;

BEGIN

  _itemlocSeries := 0;

  FOR _cmhead IN SELECT cmhead_id
                 FROM cmhead
                 WHERE ( (NOT cmhead_posted)
                   AND   (NOT cmhead_hold)
                   AND   (checkCreditMemoSitePrivs(cmhead_id))
                   AND   ((pPostUnprinted) OR (cmhead_printed)) ) LOOP

    SELECT postCreditMemo(_cmhead.cmhead_id, _itemlocSeries) INTO _result;
    IF (_result < _return) THEN
      _return := _result;
    END IF;

  END LOOP;

  RETURN _return;

END;
$_$;


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

--
-- TOC entry 2009 (class 1255 OID 146566822)
-- Dependencies: 4536 8
-- Name: postcreditmemos(boolean, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postcreditmemos(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
  pPostUnprinted ALIAS FOR $1;
  pJournalNumber ALIAS FOR $2;
  _r RECORD;
  _itemlocSeries INTEGER := 0;

BEGIN

  _itemlocSeries := 0;

  FOR _r IN SELECT cmhead_id
            FROM cmhead
            WHERE ( (NOT cmhead_posted)
              AND   (NOT cmhead_hold)
              AND   (checkCreditMemoSitePrivs(cmhead_id))
              AND   ((pPostUnprinted) OR (cmhead_printed)) ) LOOP

    SELECT postCreditMemo(_r.cmhead_id, pJournalNumber, _itemlocSeries) INTO _itemlocSeries;

  END LOOP;

  RETURN _itemlocSeries;

END;
$_$;


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

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

CREATE FUNCTION postevent(pevnttypename text, pordtype text, pordid integer, pwhsid integer, pnumber text, pnewvalue numeric, poldvalue numeric, pnewdate date, polddate 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
  _evnttypeid INTEGER;
  _whsid INTEGER;

BEGIN
  -- Find event type
  SELECT evnttype_id INTO _evnttypeid
  FROM evnttype
  WHERE (evnttype_name=pEvnttypename);
  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Event type % not found.', pEvnttypename;
  END IF;

  IF (pWhsid IS NULL) THEN
    -- Find user preferred warehouse
    SELECT usrpref_value  INTO _whsid
    FROM usrpref
    WHERE usrpref_username = getEffectiveXtUser()
      AND usrpref_name = 'PreferredWarehouse';
  ELSE
    _whsid := pWhsid;
  END IF;

  INSERT INTO evntlog ( evntlog_evnttime, evntlog_evnttype_id,
                        evntlog_ordtype, evntlog_ord_id,
                        evntlog_warehous_id, evntlog_number,
                        evntlog_newvalue, evntlog_oldvalue,
                        evntlog_newdate, evntlog_olddate,
                        evntlog_username )
  SELECT CURRENT_TIMESTAMP, evnttype_id,
         pOrdtype, pOrdid,
         _whsid, pNumber,
         pNewValue, pOldValue,
         pNewDate, pOldDate,
         evntnot_username
  FROM evnttype JOIN evntnot ON ( (evntnot_evnttype_id=evnttype_id) AND
                                  (evntnot_warehous_id=_whsid) )
  WHERE (evnttype_id=_evnttypeid);

  RETURN 0;

END
$$;


ALTER FUNCTION public.postevent(pevnttypename text, pordtype text, pordid integer, pwhsid integer, pnumber text, pnewvalue numeric, poldvalue numeric, pnewdate date, polddate date) OWNER TO admin;

--
-- TOC entry 1772 (class 1255 OID 146566824)
-- Dependencies: 4536 8
-- Name: postglseries(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postglseries(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;
  _journalNumber INTEGER;
  _returnValue INTEGER;

BEGIN

  SELECT postGLSeries(pSequence, fetchJournalNumber('G/L')) INTO _returnValue;
  RETURN _returnValue;

END;
$_$;


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

--
-- TOC entry 1793 (class 1255 OID 146566825)
-- Dependencies: 4536 8
-- Name: postglseries(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postglseries(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
  pSequence ALIAS FOR $1;
  pJournalNumber ALIAS FOR $2;
  _returnValue INTEGER;

BEGIN

  SELECT postGLSeries(pSequence, pJournalNumber, true) INTO _returnValue;
  RETURN _returnValue;

END;
$_$;


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

--
-- TOC entry 1804 (class 1255 OID 146566826)
-- Dependencies: 4536 8
-- Name: postglseries(integer, integer, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postglseries(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
  pSequence 		ALIAS FOR $1;
  pJournalNumber 	ALIAS FOR $2;
  pPostZero		ALIAS FOR $3;
  _glseries RECORD;
  _transCount INTEGER := 0;
  _delta NUMERIC;
  _discrepDate DATE;
  _discrepAccntid INTEGER;
  _rows INTEGER;
BEGIN

/*  Make sure we don't create an imbalance across companies.
    The 'IgnoreCompanyBalance' metric is a back door mechanism to
    allow legacy users to create transactions accross companies if
    they have been using the company segment for something else
    and they MUST continue to be able to do so.  It can only be 
    implemented by direct sql update to the metric table and should 
    otherwise be discouraged.
*/ 
  IF (COALESCE(fetchMetricValue('GLCompanySize'),0) > 0 
    AND fetchMetricBool('IgnoreCompany') = false)  THEN

    SELECT count(accnt_company) INTO _rows
    FROM (
      SELECT DISTINCT accnt_company
      FROM accnt 
        JOIN glseries ON (glseries_accnt_id=accnt_id)
      WHERE (glseries_sequence=pSequence)) _data;
    
    IF (_rows > 1) THEN
      RAISE EXCEPTION 'G/L Series can not be posted because multiple companies are referenced in the same series.';
    END IF;
  END IF;
  
--  Make sure that we balance
  SELECT SUM(glseries_amount), MAX(glseries_distdate) INTO _delta, _discrepDate
    FROM glseries
   WHERE (glseries_sequence=pSequence);
  IF ( _delta <> 0 ) THEN
    IF (COALESCE(fetchMetricValue('GLCompanySize'),0) = 0) THEN
      SELECT accnt_id INTO _discrepAccntid
        FROM accnt, metric
       WHERE ((metric_name='GLSeriesDiscrepancyAccount')
         AND  (accnt_id=CAST(metric_value AS INTEGER)));
    ELSE
       SELECT company_dscrp_accnt_id INTO _discrepAccntid
        FROM company
          JOIN accnt ON (accnt_company=company_number) 
          JOIN glseries ON (glseries_accnt_id=accnt_id)
       WHERE (glseries_sequence=pSequence)
       LIMIT 1;
    END IF;

    IF (NOT FOUND) THEN
      RETURN -5;
    END IF;
    
    INSERT INTO glseries
           ( glseries_sequence, glseries_source, glseries_doctype, glseries_docnumber,
             glseries_accnt_id, glseries_amount, glseries_distdate, glseries_notes )
    SELECT glseries_sequence, glseries_source, glseries_doctype, glseries_docnumber,
             _discrepAccntid, (_delta * -1), _discrepDate, 'G/L Series Discrepancy'
      FROM glseries
     WHERE (glseries_sequence=pSequence)
     LIMIT 1;
  END IF;

--  March through the glseries members, posting them one at a time
  FOR _glseries IN SELECT glseries_source, glseries_doctype, glseries_docnumber,
                          glseries_accnt_id, glseries_distdate, glseries_notes,
                          glseries_misc_id,
                          SUM(glseries_amount) as amount
                     FROM glseries
                    WHERE ((glseries_amount<>0.0)
                      AND  (glseries_sequence=pSequence))
                    GROUP BY glseries_source, glseries_doctype, glseries_docnumber,
                             glseries_accnt_id, glseries_distdate, glseries_notes,
                             glseries_misc_id LOOP

-- refuse to accept postings into closed periods
    IF (SELECT BOOL_AND(COALESCE(period_closed, FALSE))
        FROM accnt LEFT OUTER JOIN
             period ON (_glseries.glseries_distdate BETWEEN period_start AND period_end)
        WHERE (accnt_id = _glseries.glseries_accnt_id)) THEN
      RAISE EXCEPTION 'Cannot post to closed period (%).', _glseries.glseries_distdate;
      RETURN -4;        -- remove raise exception when all callers check return code
    END IF;

-- refuse to accept postings into frozen periods without proper priv
    IF (SELECT NOT BOOL_AND(checkPrivilege('PostFrozenPeriod')) AND
               BOOL_AND(COALESCE(period_freeze, FALSE))
        FROM accnt LEFT OUTER JOIN
             period ON (_glseries.glseries_distdate BETWEEN period_start AND period_end)
        WHERE (accnt_id = _glseries.glseries_accnt_id)) THEN
      RAISE EXCEPTION 'Cannot post to frozen period (%).', _glseries.glseries_distdate;
      RETURN -4;        -- remove raise exception when all callers check return code
    END IF;

-- refuse to accept postings into nonexistent periods
    IF NOT EXISTS(SELECT period_id
                  FROM period
                  WHERE (_glseries.glseries_distdate BETWEEN period_start AND period_end)) THEN
      RAISE EXCEPTION 'Cannot post to nonexistent period (%).', pDistDate;
    END IF;

    IF (_glseries.amount != 0 OR pPostZero) THEN
      IF (fetchMetricBool('UseJournals')) THEN
       INSERT INTO sltrans
        ( sltrans_posted, sltrans_created, sltrans_date, sltrans_misc_id,
          sltrans_sequence, sltrans_accnt_id, sltrans_source, sltrans_notes,
          sltrans_doctype, sltrans_docnumber, sltrans_amount, sltrans_journalnumber )
        VALUES
        ( FALSE, CURRENT_TIMESTAMP, _glseries.glseries_distdate, _glseries.glseries_misc_id,
          pSequence, _glseries.glseries_accnt_id, _glseries.glseries_source, _glseries.glseries_notes,
          _glseries.glseries_doctype, _glseries.glseries_docnumber, _glseries.amount, pJournalNumber );      
      ELSE
       INSERT INTO gltrans
        ( gltrans_posted, gltrans_exported, gltrans_created, gltrans_date, gltrans_misc_id,
          gltrans_sequence, gltrans_accnt_id, gltrans_source, gltrans_notes,
          gltrans_doctype, gltrans_docnumber, gltrans_amount, gltrans_journalnumber )
        VALUES
        ( FALSE, FALSE, CURRENT_TIMESTAMP, _glseries.glseries_distdate, _glseries.glseries_misc_id,
          pSequence, _glseries.glseries_accnt_id, _glseries.glseries_source, _glseries.glseries_notes,
          _glseries.glseries_doctype, _glseries.glseries_docnumber, _glseries.amount, pJournalNumber );
      END IF;
      
      _transCount := _transCount + 1;
    END IF;
  END LOOP;

--  Delete all of the posted glseries members
  DELETE FROM glseries
  WHERE (glseries_sequence=pSequence);

  PERFORM postIntoTrialBalance(pSequence);

  RETURN _transCount;

END;
$_$;


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

--
-- TOC entry 1853 (class 1255 OID 146566828)
-- Dependencies: 4536 8
-- Name: postglseriesnosumm(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postglseriesnosumm(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;
  _journalNumber INTEGER;
  _returnValue INTEGER;

BEGIN

  SELECT postGLSeriesNoSumm(pSequence, fetchJournalNumber('G/L')) INTO _returnValue;
  RETURN _returnValue;

END;
$_$;


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

--
-- TOC entry 1854 (class 1255 OID 146566829)
-- Dependencies: 4536 8
-- Name: postglseriesnosumm(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postglseriesnosumm(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
  pSequence ALIAS FOR $1;
  pJournalNumber ALIAS FOR $2;
  _glseries RECORD;
  _transCount INTEGER := 0;
  _rows INTEGER;

BEGIN

/*  Make sure we don't create an imbalance across companies.
    The 'IgnoreCompanyBalance' metric is a back door mechanism to
    allow legacy users to create transactions accross companies if
    they have been using the company segment for something else
    and they MUST continue to be able to do so.  It can only be 
    implemented by direct sql update to the metric table and should 
    otherwise be discouraged.
*/  
  IF (COALESCE(fetchMetricValue('GLCompanySize'),0) > 0 
    AND fetchMetricBool('IgnoreCompany') = false)  THEN

    SELECT count(accnt_company) INTO _rows
    FROM (
      SELECT DISTINCT accnt_company
      FROM accnt 
        JOIN glseries ON (glseries_accnt_id=accnt_id)
      WHERE (glseries_sequence=pSequence)) _data;
    
    IF (_rows > 1) THEN
      RAISE EXCEPTION 'G/L Series can not be posted because multiple companies are referenced in the same series.';
    END IF;
  END IF;
  
--  Make sure that we balance
  IF ( ( SELECT SUM(glseries_amount)
         FROM glseries
         WHERE (glseries_sequence=pSequence) ) <> 0 ) THEN
    RETURN -1;
  END IF;

--  March through the glseries members, posting them one at a time
  FOR _glseries IN SELECT glseries_source, glseries_doctype, glseries_docnumber,
                          glseries_accnt_id, glseries_distdate, glseries_notes,
                          glseries_misc_id, glseries_amount as amount
                     FROM glseries
                    WHERE ((glseries_amount<>0.0)
                      AND  (glseries_sequence=pSequence)) LOOP

-- refuse to accept postings into closed periods
    IF (SELECT BOOL_AND(COALESCE(period_closed, FALSE))
        FROM accnt LEFT OUTER JOIN
             period ON (_glseries.glseries_distdate BETWEEN period_start AND period_end)
        WHERE (accnt_id = _glseries.glseries_accnt_id)) THEN
      RAISE EXCEPTION 'Cannot post to closed period (%).', _glseries.glseries_distdate;
      RETURN -4;        -- remove raise exception when all callers check return code
    END IF;

-- refuse to accept postings into frozen periods without proper priv
    IF (SELECT NOT BOOL_AND(checkPrivilege('PostFrozenPeriod')) AND
               BOOL_AND(COALESCE(period_freeze, FALSE))
        FROM accnt LEFT OUTER JOIN
             period ON (_glseries.glseries_distdate BETWEEN period_start AND period_end)
        WHERE (accnt_id = _glseries.glseries_accnt_id)) THEN
      RAISE EXCEPTION 'Cannot post to frozen period (%).', _glseries.glseries_distdate;
      RETURN -4;        -- remove raise exception when all callers check return code
    END IF;

-- refuse to accept postings into nonexistent periods
    IF NOT EXISTS(SELECT period_id
                  FROM period
                  WHERE (_glseries.glseries_distdate BETWEEN period_start AND period_end)) THEN
      RAISE EXCEPTION 'Cannot post to nonexistent period (%).', pDistDate;
    END IF;

    IF (fetchMetricBool('UseJournals')) THEN
      INSERT INTO sltrans
      ( sltrans_posted, sltrans_created, sltrans_date, sltrans_misc_id,
        sltrans_sequence, sltrans_accnt_id, sltrans_source, sltrans_notes,
        sltrans_doctype, sltrans_docnumber, sltrans_amount, sltrans_journalnumber )
      VALUES
      ( FALSE, CURRENT_TIMESTAMP, _glseries.glseries_distdate, _glseries.glseries_misc_id,
        pSequence, _glseries.glseries_accnt_id, _glseries.glseries_source, _glseries.glseries_notes,
        _glseries.glseries_doctype, _glseries.glseries_docnumber, _glseries.amount, pJournalNumber );
    ELSE
      INSERT INTO gltrans
      ( gltrans_posted, gltrans_exported, gltrans_created, gltrans_date, gltrans_misc_id,
        gltrans_sequence, gltrans_accnt_id, gltrans_source, gltrans_notes,
        gltrans_doctype, gltrans_docnumber, gltrans_amount, gltrans_journalnumber )
      VALUES
      ( FALSE, FALSE, CURRENT_TIMESTAMP, _glseries.glseries_distdate, _glseries.glseries_misc_id,
        pSequence, _glseries.glseries_accnt_id, _glseries.glseries_source, _glseries.glseries_notes,
        _glseries.glseries_doctype, _glseries.glseries_docnumber, _glseries.amount, pJournalNumber );
    END IF;

    _transCount := _transCount + 1;

  END LOOP;

--  Delete all of the posted glseries members
  DELETE FROM glseries
  WHERE (glseries_sequence=pSequence);

  PERFORM postIntoTrialBalance(pSequence);

  RETURN _transCount;

END;
$_$;


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

--
-- TOC entry 1868 (class 1255 OID 146566831)
-- Dependencies: 4536 8
-- Name: postintoinvbalance(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postintoinvbalance(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
  pInvhistId ALIAS FOR $1;
  _invbalid INTEGER;
  _r RECORD;
  _count INTEGER;
  _qty NUMERIC;

BEGIN

--  Grab the invhist record to post
--  Special fix for transit sites when transtype=TS and invqty<0
--  Set the sense to 1 to correct invhist populated incorrectly.
  SELECT invhist.*,
         CASE WHEN (invhist_transtype='TS' AND invhist_invqty < 0.0 AND warehous_transit) THEN 1
              ELSE invhistSense(invhist_id)
         END AS sense,
         period_id INTO _r
  FROM invhist
    JOIN itemsite ON (itemsite_id=invhist_itemsite_id)
    JOIN whsinfo ON (warehous_id=itemsite_warehous_id)
    LEFT OUTER JOIN period ON (invhist_transdate::date BETWEEN period_start AND period_end)
  WHERE ( invhist_id=pInvhistId );

  GET DIAGNOSTICS _count = ROW_COUNT;

--  If we can post into a Inv Balance, do so
  IF ( _count > 0 ) THEN

--  Validate
    IF (_r.period_id IS NULL) THEN
      RAISE EXCEPTION 'No accounting period exists for invhist_id %, transaction date %.  Transaction can not be posted.', _r.invhist_id, formatDate(_r.invhist_transdate);
    END IF;

--  If cycle count, then we need to reference balance which needs to be accurate
--    IF (_r.invhist_transtype = 'CC') THEN
--      PERFORM forwardupdateitemsite(_r.invhist_itemsite_id);
--    END IF;

--  Try to find an existing invbal
    SELECT 
      invbal_id, 
--      CASE WHEN (_r.invhist_transtype != 'CC') THEN _r.invhist_invqty ELSE _r.invhist_invqty - invbal_qoh_ending END 
      _r.invhist_invqty
      INTO _invbalid, _qty
    FROM invbal
    WHERE ( (invbal_period_id=_r.period_id)
      AND (invbal_itemsite_id=_r.invhist_itemsite_id) );

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

--  We found a invbal, update it with the Inventory Transaction
--  Note - two stage update to avoid any funny value caching logic
    IF (_r.sense * _qty > 0) THEN
      UPDATE invbal SET 
        invbal_qty_in = (invbal_qty_in + abs(_qty)),
        invbal_value_in = (invbal_value_in + abs(_qty) * _r.invhist_unitcost)
      WHERE (invbal_id=_invbalid);
    ELSIF (_r.sense * _qty < 0) THEN
      UPDATE invbal SET 
        invbal_qty_out = (invbal_qty_out + abs(_qty)),
        invbal_value_out = (invbal_value_out + abs(_qty) *  _r.invhist_unitcost)
      WHERE (invbal_id=_invbalid);
    END IF;

    -- Non-netable transactions have their own balances
    IF (_r.invhist_transtype = 'NN') THEN
      UPDATE invbal SET 
        invbal_nn_in = (invbal_nn_in + _qty * -1),
        invbal_nnval_in = (invbal_nnval_in + _qty * -1 * _r.invhist_unitcost)
      WHERE (invbal_id=_invbalid);
    END IF;

    UPDATE invbal SET 
      invbal_qoh_ending = (invbal_qoh_beginning + invbal_qty_in - invbal_qty_out),
      invbal_value_ending = (invbal_value_beginning + invbal_value_in - invbal_value_out),
      invbal_nn_ending = (invbal_nn_beginning + invbal_nn_in - invbal_nn_out),
      invbal_nnval_ending = (invbal_nnval_beginning + invbal_nnval_in - invbal_nnval_out),
      invbal_dirty=true
    WHERE (invbal_id=_invbalid);
  ELSE

--  No existing invbal, make one
    SELECT NEXTVAL('invbal_invbal_id_seq') INTO _invbalid;
      INSERT INTO invbal
        ( invbal_id, invbal_itemsite_id, invbal_period_id,
          invbal_qoh_beginning,
          invbal_qoh_ending,
          invbal_qty_in,
          invbal_qty_out,
          invbal_value_beginning,
          invbal_value_ending,
          invbal_value_in,
          invbal_value_out,
          invbal_nn_beginning,
          invbal_nn_ending,
          invbal_nn_in,
          invbal_nn_out,
          invbal_nnval_beginning,
          invbal_nnval_ending,
          invbal_nnval_in,
          invbal_nnval_out,
          invbal_dirty )
      VALUES
        ( _invbalid, _r.invhist_itemsite_id, _r.period_id,
         -- Netable
          0, 
          _r.invhist_invqty * _r.sense,
          CASE WHEN (_r.sense > 0) THEN _r.invhist_invqty
               ELSE 0
          END,
          CASE WHEN (_r.sense < 0) THEN (_r.invhist_invqty)
               ELSE 0
          END,
          0,
          _r.invhist_invqty * _r.invhist_unitcost * _r.sense,
          CASE WHEN (_r.sense > 0) THEN _r.invhist_invqty * _r.invhist_unitcost
               ELSE 0
          END,
          CASE WHEN (_r.sense < 0) THEN (_r.invhist_invqty  * _r.invhist_unitcost)
               ELSE 0
          END,
          -- Non netable
          0, 
          CASE WHEN (_r.invhist_transtype='NN') THEN _r.invhist_invqty * -1
               ELSE 0
          END,
          CASE WHEN (_r.sense > 0 AND _r.invhist_transtype='NN') THEN _r.invhist_invqty * -1
               ELSE 0
          END,
          CASE WHEN (_r.sense < 0 AND _r.invhist_transtype='NN') THEN _r.invhist_invqty * -1
               ELSE 0
          END,
          0,
          CASE WHEN (_r.invhist_transtype='NN') THEN _r.invhist_invqty * _r.invhist_unitcost * -1
               ELSE 0
          END,
          CASE WHEN (_r.sense > 0 AND _r.invhist_transtype='NN') THEN _r.invhist_invqty * -1 * _r.invhist_unitcost
               ELSE 0
          END,
          CASE WHEN (_r.sense < 0 AND _r.invhist_transtype='NN') THEN (_r.invhist_invqty  * -1 * _r.invhist_unitcost)
               ELSE 0
          END,
          true );
    END IF;
  ELSE
    RETURN FALSE;
  END IF;

  RETURN TRUE;

END;
$_$;


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

--
-- TOC entry 2000 (class 1255 OID 146566833)
-- Dependencies: 4536 8
-- Name: postintotrialbalance(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postintotrialbalance(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;
  _trialbalid INTEGER;
  _r RECORD;

BEGIN

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

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

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

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

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

--  No existing trialbal, make one
        SELECT NEXTVAL('trialbal_trialbal_id_seq') INTO _trialbalid;
        INSERT INTO trialbal
        ( trialbal_id, trialbal_accnt_id, trialbal_period_id,
          trialbal_beginning, trialbal_dirty,
          trialbal_ending,
          trialbal_credits,
          trialbal_debits )
        VALUES
        ( _trialbalid, _r.gltrans_accnt_id, _r.period_id,
          0, TRUE,
          _r.gltrans_amount,
          CASE WHEN (_r.gltrans_amount > 0) THEN _r.gltrans_amount
               ELSE 0
          END,
          CASE WHEN (_r.gltrans_amount < 0) THEN (_r.gltrans_amount * -1)
               ELSE 0
          END );
      END IF;

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

--  Mark the G/L Transaction as posted
      UPDATE gltrans
      SET gltrans_posted=TRUE
      WHERE (gltrans_id=_r.gltrans_id);

    END IF;

  END LOOP;

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 2012 (class 1255 OID 146566834)
-- Dependencies: 4536 8
-- Name: postinvhist(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postinvhist(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
  pInvhistId ALIAS FOR $1;
  _r RECORD;
BEGIN

    IF ( SELECT metric_value
        FROM metric
        WHERE ((metric_name = 'EnableAsOfQOH')
        AND (metric_value = 't'))) THEN
      IF (NOT postIntoInvBalance(pInvhistId)) THEN
        RAISE EXCEPTION 'Post into Inventory Balance for invhist_id=% was unsuccessful',pInvhistId;
      END IF;
    END IF;

    --Update itemsite qoh and change posted flag
    UPDATE itemsite SET 
      itemsite_qtyonhand = (itemsite_qtyonhand + (invhist_invqty * invhistSense(invhist_id))),
      itemsite_value = itemsite_value + invhist_value_after - invhist_value_before
    FROM invhist
    WHERE ( (itemsite_id=invhist_itemsite_id)
    AND (invhist_id=pInvhistId)
    AND (NOT invhist_posted) );

    --Flag as posted
    UPDATE invhist SET
      invhist_posted=TRUE
    WHERE ( (invhist_id=pInvhistId)
    AND (invhist_posted=FALSE) );

RETURN TRUE;

END;
$_$;


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

--
-- TOC entry 2013 (class 1255 OID 146566835)
-- Dependencies: 4536 8
-- Name: postinvoice(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT postInvoice(pInvcheadid, fetchJournalNumber('AR-IN')) INTO _return;

  RETURN _return;

END;
$_$;


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

--
-- TOC entry 2014 (class 1255 OID 146566836)
-- Dependencies: 4536 8
-- Name: postinvoice(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postinvoice(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
  pInvcheadid ALIAS FOR $1;
  pJournalNumber ALIAS FOR $2;
  _itemlocSeries INTEGER;
  _return INTEGER;

BEGIN

  SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;
  SELECT postInvoice(pInvcheadid, pJournalNumber, _itemlocseries) INTO _return;

  RETURN _return;

END;
$_$;


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

--
-- TOC entry 2015 (class 1255 OID 146566837)
-- Dependencies: 4536 8
-- Name: postinvoice(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postinvoice(integer, integer, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pInvcheadid ALIAS FOR $1;
  pJournalNumber ALIAS FOR $2;
  pItemlocSeries ALIAS FOR $3;
  _aropenid INTEGER;
  _cohistid INTEGER;
  _itemlocSeries INTEGER := 0;
  _invhistid INTEGER := 0;
  _amount NUMERIC;
  _roundedBase NUMERIC;
  _sequence INTEGER;
  _r RECORD;
  _p RECORD;
  _test INTEGER;
  _totalAmount          NUMERIC := 0;
  _totalRoundedBase     NUMERIC := 0;
  _totalAmountBase      NUMERIC := 0;
  _appliedAmount        NUMERIC := 0;
  _commissionDue        NUMERIC := 0;
  _tmpAccntId INTEGER;
  _tmpCurrId  INTEGER;
  _firstExchDate        DATE;
  _glDate		DATE;
  _exchGain             NUMERIC := 0;

BEGIN

  IF ( ( SELECT invchead_posted
         FROM invchead
         WHERE (invchead_id=pInvcheadid) ) ) THEN
    RETURN -10;
  END IF;

--  Cache some parameters
  SELECT invchead.*, fetchGLSequence() AS sequence,
         findFreightAccount(invchead_cust_id) AS freightaccntid,
         findARAccount(invchead_cust_id) AS araccntid,
         ( SELECT COALESCE(SUM(taxhist_tax), 0)
           FROM invcheadtax
           WHERE ( (taxhist_parent_id = invchead_id)
             AND   (taxhist_taxtype_id = getFreightTaxtypeId()) ) ) AS freighttax,
         ( SELECT COALESCE(SUM(taxhist_tax), 0)
           FROM invcheadtax
           WHERE ( (taxhist_parent_id = invchead_id)
             AND   (taxhist_taxtype_id = getAdjustmentTaxtypeId()) ) ) AS adjtax
       INTO _p 
  FROM invchead
  WHERE (invchead_id=pInvcheadid);

  _itemlocSeries = pItemlocSeries;

  _glDate := COALESCE(_p.invchead_gldistdate, _p.invchead_invcdate);

  IF (_p.invchead_salesrep_id < 0) THEN
    RAISE NOTICE 'Patch negative invchead_salesrep_id until invchead_salesrep_id is a true fkey';
    _p.invchead_salesrep_id := NULL;
  END IF;

-- the 1st MC iteration used the cohead_orderdate so we could get curr exch
-- gain/loss between the sales and invoice dates, but see issue 3892.  leave
-- this condition TRUE until we make this configurable or decide not to.
  IF TRUE THEN
      _firstExchDate := _p.invchead_invcdate;
  ELSE
-- can we save a select by using: _firstExchDate := _p.invchead_orderdate;
      SELECT cohead_orderdate INTO _firstExchDate
      FROM cohead JOIN invchead ON (cohead_number = invchead_ordernumber)
      WHERE (invchead_id = pInvcheadid);
  END IF;

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

    PERFORM insertIntoGLSeries( _p.sequence, 'A/R', 'IN', _p.invchead_invcnumber,
                                _r.tax_sales_accnt_id, 
                                _r.taxbasevalue,
                                _glDate, _p.invchead_billto_name );

    _totalAmount := _totalAmount + _r.tax;
    _totalRoundedBase := _totalRoundedBase + _r.taxbasevalue;  
  END LOOP;

-- Update item tax records with posting data
    UPDATE invcitemtax SET 
      taxhist_docdate=_firstExchDate,
      taxhist_distdate=_glDate,
      taxhist_curr_id=_p.invchead_curr_id,
      taxhist_curr_rate=curr_rate,
      taxhist_journalnumber=pJournalNumber
    FROM invchead
     JOIN invcitem ON (invchead_id=invcitem_invchead_id), 
     curr_rate
    WHERE ((invchead_id=pInvcheadId)
      AND (taxhist_parent_id=invcitem_id)
      AND (_p.invchead_curr_id=curr_id)
      AND ( _firstExchDate BETWEEN curr_effective 
                           AND curr_expires) );

-- Update Invchead taxes (Freight and Adjustments) with posting data
    UPDATE invcheadtax SET 
      taxhist_docdate=_firstExchDate,
      taxhist_distdate=_glDate,
      taxhist_curr_id=_p.invchead_curr_id,
      taxhist_curr_rate=curr_rate,
      taxhist_journalnumber=pJournalNumber
    FROM curr_rate
    WHERE ((taxhist_parent_id=pInvcheadid)
      AND (_p.invchead_curr_id=curr_id)
      AND ( _firstExchDate BETWEEN curr_effective 
                           AND curr_expires) );

--  March through the Non-Misc. Invcitems
  FOR _r IN SELECT *
            FROM invoiceitem
            WHERE ( (invcitem_invchead_id = pInvcheadid)
              AND   (invcitem_item_id <> -1) ) LOOP

--  Cache the amount due for this line
    _amount := _r.extprice;

    IF (_amount > 0) THEN
--  Credit the Sales Account for the invcitem item
      IF (_r.invcitem_rev_accnt_id IS NOT NULL) THEN
        SELECT getPrjAccntId(_p.invchead_prj_id, _r.invcitem_rev_accnt_id)
	INTO _tmpAccntId;
      ELSEIF (_r.itemsite_id IS NULL) THEN
	SELECT getPrjAccntId(_p.invchead_prj_id, salesaccnt_sales_accnt_id) 
	INTO _tmpAccntId
	FROM salesaccnt
	WHERE (salesaccnt_id=findSalesAccnt(_r.invcitem_item_id, 'I', _p.invchead_cust_id,
                                            _p.invchead_saletype_id, _p.invchead_shipzone_id));
      ELSE
	SELECT getPrjAccntId(_p.invchead_prj_id, salesaccnt_sales_accnt_id) 
	INTO _tmpAccntId
	FROM salesaccnt
	WHERE (salesaccnt_id=findSalesAccnt(_r.itemsite_id, 'IS', _p.invchead_cust_id,
                                            _p.invchead_saletype_id, _p.invchead_shipzone_id));
      END IF;

--  If the Sales Account Assignment was not found then punt
      IF (NOT FOUND) THEN
        PERFORM deleteGLSeries(_p.sequence);
        DELETE FROM cohist
         WHERE ((cohist_sequence=_p.sequence)
           AND  (cohist_invcnumber=_p.invchead_invcnumber));
        RETURN -11;
      END IF;

      _roundedBase := round(currToBase(_p.invchead_curr_id, _amount, _firstExchDate), 2);
      SELECT insertIntoGLSeries( _p.sequence, 'A/R', 'IN',
                                 _p.invchead_invcnumber, _tmpAccntId,
                                 _roundedBase, _glDate, _p.invchead_billto_name ) INTO _test;

      _totalAmount := (_totalAmount + _amount);
      _totalRoundedBase := _totalRoundedBase + _roundedBase;
      _commissionDue := (_commissionDue + (_amount * _p.invchead_commission));
    END IF;

    _totalAmount := _totalAmount;
    _totalRoundedBase := _totalRoundedBase;

--  Record Sales History for this S/O Item
    SELECT nextval('cohist_cohist_id_seq') INTO _cohistid;
    INSERT INTO cohist
    ( cohist_id, cohist_cust_id, cohist_itemsite_id, cohist_shipto_id,
      cohist_shipdate, cohist_shipvia,
      cohist_ordernumber, cohist_ponumber, cohist_orderdate,
      cohist_doctype, cohist_invcnumber, cohist_invcdate,
      cohist_qtyshipped, cohist_unitprice, cohist_unitcost,
      cohist_salesrep_id, cohist_commission, cohist_commissionpaid,
      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_curr_id, cohist_sequence, cohist_taxtype_id, cohist_taxzone_id,
      cohist_shipzone_id, cohist_saletype_id )
    VALUES
    ( _cohistid, _p.invchead_cust_id, _r.itemsite_id, _p.invchead_shipto_id,
      _p.invchead_shipdate, _p.invchead_shipvia,
      COALESCE(_p.invchead_ordernumber, _r.cohead_number), _p.invchead_ponumber, _p.invchead_orderdate,
      'I', _p.invchead_invcnumber, _p.invchead_invcdate,
      _r.qty, _r.unitprice, _r.unitcost,
      _p.invchead_salesrep_id, (_p.invchead_commission * _r.extprice), FALSE,
      _p.invchead_billto_name, _p.invchead_billto_address1,
      _p.invchead_billto_address2, _p.invchead_billto_address3,
      _p.invchead_billto_city, _p.invchead_billto_state, _p.invchead_billto_zipcode,
      _p.invchead_shipto_name, _p.invchead_shipto_address1,
      _p.invchead_shipto_address2, _p.invchead_shipto_address3,
      _p.invchead_shipto_city, _p.invchead_shipto_state,
      _p.invchead_shipto_zipcode, _p.invchead_curr_id,
      _p.sequence, _r.invcitem_taxtype_id, _p.invchead_taxzone_id,
      _p.invchead_shipzone_id, _p.invchead_saletype_id );
    INSERT INTO cohisttax
    ( 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 _cohistid, 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 invcitemtax
    WHERE (taxhist_parent_id=_r.invcitem_id);

  END LOOP;

--  March through the Misc. Invcitems
  FOR _r IN SELECT *
            FROM invoiceitem JOIN salescat ON (salescat_id = invcitem_salescat_id)
            WHERE ( (invcitem_item_id = -1)
              AND   (invcitem_invchead_id=pInvcheadid) ) LOOP

--  Cache the amount due for this line and the commission due for such
    _amount := _r.extprice;

    IF (_amount > 0) THEN
--  Credit the Sales Account for the invcitem item
      _roundedBase = round(currToBase(_p.invchead_curr_id, _amount,
                                      _firstExchDate), 2);
      SELECT insertIntoGLSeries( _p.sequence, 'A/R', 'IN', _p.invchead_invcnumber,
                                 getPrjAccntId(_p.invchead_prj_id, COALESCE(_r.invcitem_rev_accnt_id, _r.salescat_sales_accnt_id)), 
                                 _roundedBase,
                                 _glDate, _p.invchead_billto_name ) INTO _test;
      IF (_test < 0) THEN
        PERFORM deleteGLSeries(_p.sequence);
        DELETE FROM cohist
         WHERE ((cohist_sequence=_p.sequence)
           AND  (cohist_invcnumber=_p.invchead_invcnumber));
        RETURN _test;
      END IF;

      _totalAmount := (_totalAmount + _amount);
      _totalRoundedBase :=  _totalRoundedBase + _roundedBase;
      _commissionDue := (_commissionDue + (_amount * _p.invchead_commission));
    END IF;

--  Record Sales History for this S/O Item
    SELECT nextval('cohist_cohist_id_seq') INTO _cohistid;
    INSERT INTO cohist
    ( cohist_id, cohist_cust_id, cohist_itemsite_id, cohist_shipto_id,
      cohist_misc_type, cohist_misc_descrip,
      cohist_shipdate, cohist_shipvia,
      cohist_ordernumber, cohist_ponumber, cohist_orderdate,
      cohist_doctype, cohist_invcnumber, cohist_invcdate,
      cohist_qtyshipped, cohist_unitprice, cohist_unitcost,
      cohist_salesrep_id, cohist_commission, cohist_commissionpaid,
      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_curr_id, cohist_sequence, cohist_taxtype_id, cohist_taxzone_id,
      cohist_shipzone_id, cohist_saletype_id )
    VALUES
    ( _cohistid, _p.invchead_cust_id, -1, _p.invchead_shipto_id,
      'M', (_r.invcitem_number || '-' || _r.invcitem_descrip),
      _p.invchead_shipdate, _p.invchead_shipvia,
      COALESCE(_p.invchead_ordernumber, _r.cohead_number), _p.invchead_ponumber, _p.invchead_orderdate,
      'I', _p.invchead_invcnumber, _p.invchead_invcdate,
      _r.qty, _r.unitprice, 0,
      _p.invchead_salesrep_id, (_p.invchead_commission * _r.extprice), FALSE,
      _p.invchead_billto_name, _p.invchead_billto_address1,
      _p.invchead_billto_address2, _p.invchead_billto_address3,
      _p.invchead_billto_city, _p.invchead_billto_state, _p.invchead_billto_zipcode,
      _p.invchead_shipto_name, _p.invchead_shipto_address1,
      _p.invchead_shipto_address2, _p.invchead_shipto_address3,
      _p.invchead_shipto_city, _p.invchead_shipto_state,
      _p.invchead_shipto_zipcode, _p.invchead_curr_id,
      _p.sequence, _r.invcitem_taxtype_id, _p.invchead_taxzone_id,
      _p.invchead_shipzone_id, _p.invchead_saletype_id );
    INSERT INTO cohisttax
    ( 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 _cohistid, 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 invcitemtax
    WHERE (taxhist_parent_id=_r.invcitem_id);

  END LOOP;

--  Credit the Freight Account for Freight Charges
  IF (_p.invchead_freight <> 0) THEN
    IF (_p.freightaccntid <> -1) THEN
      _roundedBase = round(currToBase(_p.invchead_curr_id, _p.invchead_freight,
                                      _firstExchDate), 2);
      SELECT insertIntoGLSeries( _p.sequence, 'A/R', 'IN', _p.invchead_invcnumber,
                                 getPrjAccntId(_p.invchead_prj_id,_p.freightaccntid), 
                                 _roundedBase,
                                 _glDate, _p.invchead_billto_name ) INTO _test;

--  Cache the Freight Amount distributed
        _totalAmount := (_totalAmount + _p.invchead_freight);
        _totalRoundedBase := _totalRoundedBase + _roundedBase;
    ELSE
      _test := -14;
    END IF;

--  If the Freight Account was not found then punt
    IF (_test < 0) THEN
      PERFORM deleteGLSeries(_p.sequence);
      DELETE FROM cohist
       WHERE ((cohist_sequence=_p.sequence)
         AND  (cohist_invcnumber=_p.invchead_invcnumber));
      RETURN _test;
    END IF;

--  Record Sales History for the Freight
    SELECT nextval('cohist_cohist_id_seq') INTO _cohistid;
    INSERT INTO cohist
    ( cohist_id, cohist_cust_id, cohist_itemsite_id, cohist_shipto_id,
      cohist_misc_type, cohist_misc_descrip,
      cohist_shipdate, cohist_shipvia,
      cohist_ordernumber, cohist_ponumber, cohist_orderdate,
      cohist_doctype, cohist_invcnumber, cohist_invcdate,
      cohist_qtyshipped, cohist_unitprice, cohist_unitcost,
      cohist_salesrep_id, cohist_commission, cohist_commissionpaid,
      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_curr_id, cohist_sequence, cohist_taxtype_id, cohist_taxzone_id,
      cohist_shipzone_id, cohist_saletype_id )
    VALUES
    ( _cohistid, _p.invchead_cust_id, -1, _p.invchead_shipto_id,
      'F', 'Freight',
      _p.invchead_shipdate, _p.invchead_shipvia,
      _p.invchead_ordernumber, _p.invchead_ponumber, _p.invchead_orderdate,
      'I', _p.invchead_invcnumber, _p.invchead_invcdate,
      1, _p.invchead_freight, _p.invchead_freight,
      _p.invchead_salesrep_id, 0, FALSE,
      _p.invchead_billto_name, _p.invchead_billto_address1,
      _p.invchead_billto_address2, _p.invchead_billto_address3,
      _p.invchead_billto_city, _p.invchead_billto_state, _p.invchead_billto_zipcode,
      _p.invchead_shipto_name, _p.invchead_shipto_address1,
      _p.invchead_shipto_address2, _p.invchead_shipto_address3,
      _p.invchead_shipto_city, _p.invchead_shipto_state,
      _p.invchead_shipto_zipcode, _p.invchead_curr_id,
      _p.sequence, getFreightTaxtypeId(), _p.invchead_taxzone_id,
      _p.invchead_shipzone_id, _p.invchead_saletype_id );
    INSERT INTO cohisttax
    ( 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 _cohistid, 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 invcheadtax
    WHERE ( (taxhist_parent_id=_p.invchead_id)
      AND   (taxhist_taxtype_id=getFreightTaxtypeId()) );

  END IF;

--  Credit the Misc. Account for Miscellaneous Charges
  IF (_p.invchead_misc_amount <> 0) THEN
    _roundedBase := round(currToBase(_p.invchead_curr_id, _p.invchead_misc_amount,
                                     _firstExchDate), 2);
    SELECT insertIntoGLSeries( _p.sequence, 'A/R', 'IN', _p.invchead_invcnumber,
                               getPrjAccntId(_p.invchead_prj_id, _p.invchead_misc_accnt_id), 
                               _roundedBase,
                               _glDate, _p.invchead_billto_name ) INTO _test;

--  If the Misc. Charges Account was not found then punt
    IF (_test < 0) THEN
      PERFORM deleteGLSeries(_p.sequence);
      DELETE FROM cohist
       WHERE ((cohist_sequence=_p.sequence)
         AND  (cohist_invcnumber=_p.invchead_invcnumber));
      RETURN _test;
    END IF;

--  Cache the Misc. Amount distributed
    _totalAmount := (_totalAmount + _p.invchead_misc_amount);
    _totalRoundedBase := _totalRoundedBase + _roundedBase;

--  Record Sales History for the Misc. Charge
    INSERT INTO cohist
    ( cohist_cust_id, cohist_itemsite_id, cohist_shipto_id,
      cohist_misc_type, cohist_misc_descrip, cohist_misc_id,
      cohist_shipdate, cohist_shipvia,
      cohist_ordernumber, cohist_ponumber, cohist_orderdate,
      cohist_doctype, cohist_invcnumber, cohist_invcdate,
      cohist_qtyshipped, cohist_unitprice, cohist_unitcost,
      cohist_salesrep_id, cohist_commission, cohist_commissionpaid,
      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_curr_id, cohist_sequence,
      cohist_shipzone_id, cohist_saletype_id )
    VALUES
    ( _p.invchead_cust_id, -1, _p.invchead_shipto_id,
      'M', _p.invchead_misc_descrip, _p.invchead_misc_accnt_id,
      _p.invchead_shipdate, _p.invchead_shipvia,
      _p.invchead_ordernumber, _p.invchead_ponumber, _p.invchead_orderdate,
      'I', _p.invchead_invcnumber, _p.invchead_invcdate,
      1, _p.invchead_misc_amount, _p.invchead_misc_amount,
      _p.invchead_salesrep_id, 0, FALSE,
      _p.invchead_billto_name, _p.invchead_billto_address1,
      _p.invchead_billto_address2, _p.invchead_billto_address3,
      _p.invchead_billto_city, _p.invchead_billto_state, _p.invchead_billto_zipcode,
      _p.invchead_shipto_name, _p.invchead_shipto_address1,
      _p.invchead_shipto_address2, _p.invchead_shipto_address3,
      _p.invchead_shipto_city, _p.invchead_shipto_state,
      _p.invchead_shipto_zipcode, _p.invchead_curr_id,
      _p.sequence,
      _p.invchead_shipzone_id, _p.invchead_saletype_id );

  END IF;

--  Record Sales History for the Tax Adjustment
  IF (_p.adjtax <> 0) THEN
    SELECT nextval('cohist_cohist_id_seq') INTO _cohistid;
    INSERT INTO cohist
    ( cohist_id, cohist_cust_id, cohist_itemsite_id, cohist_shipto_id,
      cohist_misc_type, cohist_misc_descrip,
      cohist_shipdate, cohist_shipvia,
      cohist_ordernumber, cohist_ponumber, cohist_orderdate,
      cohist_doctype, cohist_invcnumber, cohist_invcdate,
      cohist_qtyshipped, cohist_unitprice, cohist_unitcost,
      cohist_salesrep_id, cohist_commission, cohist_commissionpaid,
      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_curr_id, cohist_sequence, cohist_taxtype_id, cohist_taxzone_id,
      cohist_shipzone_id, cohist_saletype_id )
    VALUES
    ( _cohistid, _p.invchead_cust_id, -1, _p.invchead_shipto_id,
      'T', 'Misc Tax Adjustment',
      _p.invchead_shipdate, _p.invchead_shipvia,
      _p.invchead_ordernumber, _p.invchead_ponumber, _p.invchead_orderdate,
      'I', _p.invchead_invcnumber, _p.invchead_invcdate,
      1, 0.0, 0.0,
      _p.invchead_salesrep_id, 0, FALSE,
      _p.invchead_billto_name, _p.invchead_billto_address1,
      _p.invchead_billto_address2, _p.invchead_billto_address3,
      _p.invchead_billto_city, _p.invchead_billto_state, _p.invchead_billto_zipcode,
      _p.invchead_shipto_name, _p.invchead_shipto_address1,
      _p.invchead_shipto_address2, _p.invchead_shipto_address3,
      _p.invchead_shipto_city, _p.invchead_shipto_state,
      _p.invchead_shipto_zipcode, _p.invchead_curr_id,
      _p.sequence, getAdjustmentTaxtypeId(), _p.invchead_taxzone_id,
      _p.invchead_shipzone_id, _p.invchead_saletype_id );
    INSERT INTO cohisttax
    ( 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 _cohistid, 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 invcheadtax
    WHERE ( (taxhist_parent_id=_p.invchead_id)
      AND   (taxhist_taxtype_id=getAdjustmentTaxtypeId()) );

  END IF;

-- ToDo: handle rounding errors
    _exchGain := currGain(_p.invchead_curr_id, _totalAmount,
                          _firstExchDate, _glDate);
    IF (_exchGain <> 0) THEN
        SELECT insertIntoGLSeries( _p.sequence, 'A/R', 'IN',
                                   _p.invchead_invcnumber, getGainLossAccntId(_p.araccntid),
                                   round(_exchGain, 2) * -1,
                                   _glDate, _p.invchead_billto_name ) INTO _test ;
        IF (_test < 0) THEN
          PERFORM deleteGLSeries(_p.sequence);
          DELETE FROM cohist
           WHERE ((cohist_sequence=_p.sequence)
             AND  (cohist_invcnumber=_p.invchead_invcnumber));
          RETURN _test;
        END IF;
    END IF;

--  Debit A/R for the total Amount
  IF (_totalRoundedBase <> 0) THEN
    IF (_p.araccntid != -1) THEN
      PERFORM insertIntoGLSeries( _p.sequence, 'A/R', 'IN', _p.invchead_invcnumber,
                                  _p.araccntid, round(_totalRoundedBase * -1, 2),
                                  _glDate, _p.invchead_billto_name );
    ELSE
      PERFORM deleteGLSeries(_p.sequence);
      DELETE FROM cohist
       WHERE ((cohist_sequence=_p.sequence)
         AND  (cohist_invcnumber=_p.invchead_invcnumber));
      RETURN -17;
    END IF;
  END IF;

--  Commit the GLSeries;
  SELECT postGLSeries(_p.sequence, pJournalNumber) INTO _test;
  IF (_test < 0) THEN
    PERFORM deleteGLSeries(_p.sequence);
    DELETE FROM cohist
     WHERE ((cohist_sequence=_p.sequence)
       AND  (cohist_invcnumber=_p.invchead_invcnumber));
    RETURN _test;
  END IF;

--  Create the Invoice aropen item
  SELECT nextval('aropen_aropen_id_seq') INTO _aropenid;
  INSERT INTO aropen
  ( aropen_id, aropen_username, aropen_journalnumber,
    aropen_open, aropen_posted,
    aropen_cust_id, aropen_ponumber,
    aropen_docnumber, aropen_applyto, aropen_doctype,
    aropen_docdate, aropen_duedate, aropen_distdate, aropen_terms_id,
    aropen_amount, aropen_paid,
    aropen_salesrep_id, aropen_commission_due, aropen_commission_paid,
    aropen_ordernumber, aropen_notes, aropen_cobmisc_id,
    aropen_curr_id )
  VALUES
  ( _aropenid, getEffectiveXtUser(), pJournalNumber,
    TRUE, FALSE,
    _p.invchead_cust_id, _p.invchead_ponumber,
    _p.invchead_invcnumber, _p.invchead_invcnumber, 'I',
    _p.invchead_invcdate, determineDueDate(_p.invchead_terms_id, _p.invchead_invcdate), _glDate, _p.invchead_terms_id,
    round(_totalAmount, 2), 0, 
    _p.invchead_salesrep_id, _commissionDue, FALSE,
    _p.invchead_ordernumber::text, _p.invchead_notes, pInvcheadid,
    _p.invchead_curr_id );

-- Handle the Inventory and G/L Transactions for any billed Inventory where invcitem_updateinv is true
  FOR _r IN SELECT itemsite_id AS itemsite_id, invcitem_id,
                   (invcitem_billed * invcitem_qty_invuomratio) AS qty,
                   invchead_invcnumber, invchead_cust_id AS cust_id, item_number,
                   invchead_saletype_id AS saletype_id, invchead_shipzone_id AS shipzone_id,
                   invchead_prj_id, itemsite_costmethod
            FROM invchead JOIN invcitem ON ( (invcitem_invchead_id=invchead_id) AND
                                             (invcitem_billed <> 0) AND
                                             (invcitem_updateinv) )
                          JOIN itemsite ON ( (itemsite_item_id=invcitem_item_id) AND
                                             (itemsite_warehous_id=invcitem_warehous_id) )
                          JOIN item ON (item_id=invcitem_item_id)
            WHERE (invchead_id=pInvcheadid) LOOP

--  Issue billed stock from inventory
    IF (_itemlocSeries = 0) THEN
      _itemlocSeries := NEXTVAL('itemloc_series_seq');
    END IF;
    IF (_r.itemsite_costmethod != 'J') THEN
      SELECT postInvTrans(itemsite_id, 'SH', _r.qty,
                         'S/O', 'IN', _r.invchead_invcnumber, '',
                         ('Invoice Billed ' || _r.item_number),
                         getPrjAccntId(_r.invchead_prj_id, resolveCOSAccount(itemsite_id, _r.cust_id, _r.saletype_id, _r.shipzone_id)),
                         costcat_asset_accnt_id, _itemlocSeries, _glDate) INTO _invhistid
      FROM itemsite, costcat
      WHERE ( (itemsite_costcat_id=costcat_id)
       AND (itemsite_id=_r.itemsite_id) );
    ELSE
      RAISE DEBUG 'postInvoice(%, %, %) tried to postInvTrans a %-costed item',
                  pInvcheadid, pJournalNumber, pItemlocSeries,
                  _r.itemsite_costmethod;
    END IF;

  END LOOP;

--  Mark the invoice as posted
  UPDATE invchead
  SET invchead_posted=TRUE, invchead_gldistdate=_glDate
  WHERE (invchead_id=pInvcheadid);
 
  IF (_totalAmount > 0) THEN
    -- get a list of allocated CMs
    FOR _r IN SELECT aropen_id,
		     CASE WHEN((aropen_amount - aropen_paid) >=
                                aropenalloc_amount / (1 / aropen_curr_rate / 
                                currRate(aropenalloc_curr_id,_firstExchDate))) THEN
			      aropenalloc_amount / (1 / aropen_curr_rate / 
                                currRate(aropenalloc_curr_id,_firstExchDate))
			  ELSE (aropen_amount - aropen_paid)
		     END AS balance,
		     aropen_curr_id, aropen_curr_rate,
		     aropenalloc_doctype, aropenalloc_doc_id
                FROM aropenalloc, aropen
               WHERE ( (aropenalloc_aropen_id=aropen_id)
                 AND   ((aropenalloc_doctype='S' AND aropenalloc_doc_id=(SELECT cohead_id
                                                                           FROM cohead
                                                                          WHERE cohead_number=_p.invchead_ordernumber)) OR
                        (aropenalloc_doctype='I' AND aropenalloc_doc_id=_p.invchead_id)) ) LOOP

      _appliedAmount := _r.balance;
      IF (_totalAmount < _appliedAmount / (1 / currRate(_r.aropen_curr_id,_firstExchDate) /
                        _r.aropen_curr_rate)) THEN
        _appliedAmount := _totalAmount;
	_tmpCurrId := _p.invchead_curr_id;
      ELSE
	_tmpCurrId := _r.aropen_curr_id;
      END IF;

      -- ignore if no appliable balance
      IF (_appliedAmount > 0) THEN
        -- create an arcreditapply record linking the source c/m and the target invoice
        -- for an amount that is equal to the balance on the invoice or the balance on
        -- c/m whichever is greater.
        INSERT INTO arcreditapply
              (arcreditapply_source_aropen_id, arcreditapply_target_aropen_id,
	       arcreditapply_amount, arcreditapply_curr_id, arcreditapply_reftype, arcreditapply_ref_id)
        VALUES(_r.aropen_id, _aropenid, _appliedAmount, _tmpCurrId, 'S',  _r.aropenalloc_doc_id);

        -- call postARCreditMemoApplication(aropen_id of C/M)
        SELECT postARCreditMemoApplication(_r.aropen_id) into _test;

        -- if no error decrement the balance and contiue on
        IF (_test >= 0) THEN
          _totalAmount := _totalAmount - currToCurr(_tmpCurrId, _p.invchead_curr_id,
						    _appliedAmount, _firstExchDate);
        END IF;

        -- delete the allocation
        DELETE FROM aropenalloc
        WHERE (aropenalloc_doctype='I')
          AND (aropenalloc_doc_id=_p.invchead_id);

      END IF;
    END LOOP;
  END IF;

  RETURN _itemlocSeries;

END;
$_$;


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

--
-- TOC entry 2016 (class 1255 OID 146566839)
-- Dependencies: 4536 8
-- Name: postinvoices(boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postinvoices(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
  pPostUnprinted ALIAS FOR $1;
BEGIN
  RETURN postInvoices(pPostUnprinted, FALSE);
END;
$_$;


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

--
-- TOC entry 2017 (class 1255 OID 146566840)
-- Dependencies: 4536 8
-- Name: postinvoices(boolean, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postinvoices(boolean, 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
  pPostUnprinted ALIAS FOR $1;
  pInclZeros     ALIAS FOR $2;
BEGIN
  RETURN postInvoices(pPostUnprinted, pInclZeros, fetchJournalNumber('AR-IN'));
END;
$_$;


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

--
-- TOC entry 2018 (class 1255 OID 146566841)
-- Dependencies: 4536 8
-- Name: postinvoices(boolean, boolean, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postinvoices(boolean, 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
  pPostUnprinted ALIAS FOR $1;
  pInclZeros     ALIAS FOR $2;
  pJournalNumber ALIAS FOR $3;
  _invcheadid    INTEGER;
  _itemlocSeries INTEGER;
  _counter INTEGER;
  _r RECORD;

BEGIN

  _itemlocSeries := 0;

  IF (pInclZeros) THEN

    FOR _invcheadid IN
      SELECT invchead_id
      FROM invchead
      WHERE ( (NOT invchead_posted)
       AND (checkInvoiceSitePrivs(invchead_id))
       AND (pPostUnprinted OR invchead_printed) ) LOOP

      SELECT postInvoice(_invcheadid, pJournalNumber, _itemlocSeries) INTO _itemlocSeries;
      IF (_itemlocSeries < 0) THEN
        RETURN _itemlocSeries;
      END IF;
    END LOOP;

  ELSE

    FOR _invcheadid IN
      SELECT invchead_id
      FROM invchead LEFT OUTER JOIN invcitem ON (invchead_id=invcitem_invchead_id)
                    LEFT OUTER JOIN item ON (invcitem_item_id=item_id)  
      WHERE((NOT invchead_posted)
        AND (checkInvoiceSitePrivs(invchead_id))
        AND (pPostUnprinted OR invchead_printed))
      GROUP BY invchead_id, invchead_freight, invchead_misc_amount
      HAVING (COALESCE(SUM(round((invcitem_billed * invcitem_qty_invuomratio) * (invcitem_price /  
              CASE WHEN (item_id IS NULL) THEN 1 
              ELSE invcitem_price_invuomratio END), 2)),0)
             + invchead_freight + invchead_misc_amount) > 0 LOOP

      SELECT postInvoice(_invcheadid, pJournalNumber, _itemlocSeries) INTO _itemlocSeries;
      IF (_itemlocSeries < 0) THEN
        RETURN _itemlocSeries;
      END IF;
    END LOOP;

  END IF;

  RETURN _itemlocSeries;

END;
$_$;


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

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

CREATE FUNCTION postinvtrans(pitemsiteid integer, ptranstype text, pqty numeric, pmodule text, pordertype text, pordernumber text, pdocnumber text, pcomments text, pdebitid integer, pcreditid integer, pitemlocseries integer, ptimestamp timestamp with time zone DEFAULT now(), pcostovrld numeric DEFAULT NULL::numeric, pinvhistid integer DEFAULT NULL::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.
-- pInvhistid is the original transaction to be returned, reversed, etc.
DECLARE
  _creditid	     INTEGER;
  _debitid	     INTEGER;
  _glreturn	     INTEGER;
  _invhistid	     INTEGER;
  _itemlocdistid     INTEGER;
  _r		     RECORD;
  _sense	     INTEGER;  -- direction in which to adjust inventory QOH
  _t		     RECORD;
  _timestamp         TIMESTAMP WITH TIME ZONE;
  _xferwhsid	     INTEGER;

BEGIN

  --  Cache item and itemsite info  
  SELECT CASE WHEN(itemsite_costmethod IN ('A','J')) THEN COALESCE(abs(pCostOvrld / pQty), avgcost(itemsite_id))
              ELSE stdCost(itemsite_item_id)
         END AS cost,
         itemsite_costmethod,
         itemsite_qtyonhand,
	 itemsite_warehous_id,
         ( (item_type = 'R') OR (itemsite_controlmethod = 'N') ) AS nocontrol,
         (itemsite_controlmethod IN ('L', 'S')) AS lotserial,
         (itemsite_loccntrl) AS loccntrl,
         itemsite_freeze AS frozen INTO _r
  FROM itemsite JOIN item ON (item_id=itemsite_item_id)
  WHERE (itemsite_id=pItemsiteid);

  --Post the Inventory Transactions
  IF (_r.nocontrol) THEN
    RETURN -1; -- non-fatal error so dont throw an exception?
  END IF;

  IF (COALESCE(pItemlocSeries,0) = 0) THEN
    RAISE EXCEPTION 'Transaction series must be provided';
  END IF;

  SELECT NEXTVAL('invhist_invhist_id_seq') INTO _invhistid;

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

  IF (pTransType = 'TS' OR pTransType = 'TR') THEN
    SELECT * INTO _t FROM tohead WHERE (tohead_number=pDocNumber);
    IF (pTransType = 'TS') THEN
      _xferwhsid := CASE
          WHEN (_t.tohead_src_warehous_id=_r.itemsite_warehous_id) THEN _t.tohead_trns_warehous_id
          WHEN (_t.tohead_trns_warehous_id=_r.itemsite_warehous_id AND pComments ~* 'recall') THEN _t.tohead_src_warehous_id
          WHEN (_t.tohead_trns_warehous_id=_r.itemsite_warehous_id) THEN _t.tohead_dest_warehous_id
          WHEN (_t.tohead_dest_warehous_id=_r.itemsite_warehous_id) THEN _t.tohead_trns_warehous_id
          ELSE NULL
          END;
    ELSIF (pTransType = 'TR') THEN
      _xferwhsid := CASE
          WHEN (_t.tohead_src_warehous_id=_r.itemsite_warehous_id) THEN _t.tohead_trns_warehous_id
          WHEN (_t.tohead_trns_warehous_id=_r.itemsite_warehous_id AND pComments ~* 'recall') THEN _t.tohead_dest_warehous_id
          WHEN (_t.tohead_trns_warehous_id=_r.itemsite_warehous_id) THEN _t.tohead_src_warehous_id
          WHEN (_t.tohead_dest_warehous_id=_r.itemsite_warehous_id) THEN _t.tohead_trns_warehous_id
          ELSE NULL
          END;
    END IF;
  END IF;


  -- increase inventory: AD RM RT RP RR RS RX RB TR
  -- decrease inventory: IM IB IT SH SI EX RI
  -- TS and TR are special: shipShipment and recallShipment should not change
  -- QOH at the Transfer Order src whs (as this was done by issueToShipping)
  -- but postReceipt should change QOH at the transit whs
  IF (pTransType='TS') THEN
    _sense := CASE WHEN (SELECT tohead_trns_warehous_id=_r.itemsite_warehous_id
                         FROM tohead
                         WHERE (tohead_number=pDocNumber)) THEN -1
                         ELSE 0
                         END;
  ELSIF (pTransType='TR') THEN
    _sense := CASE WHEN (SELECT tohead_src_warehous_id=_r.itemsite_warehous_id
                         FROM tohead
                         WHERE (tohead_number=pDocNumber)) THEN 0
                         ELSE 1
                         END;
  ELSIF (pTransType IN ('IM', 'IB', 'IT', 'SH', 'SI', 'EX', 'RI')) THEN
    _sense := -1;

  ELSE
    _sense := 1;
  END IF;

  IF((_r.itemsite_costmethod='A') AND (_r.itemsite_qtyonhand + round(_sense * pQty, 6)) < 0) THEN
    -- Can not let average costed itemsites go negative
    RAISE EXCEPTION 'This transaction will cause an Average Costed item to go negative which is not allowed [xtuple: postinvtrans, -2]';
  END IF;

  INSERT INTO invhist
  ( invhist_id, invhist_itemsite_id, invhist_transtype, invhist_transdate,
      invhist_invqty, invhist_qoh_before,
      invhist_qoh_after,
      invhist_costmethod, invhist_value_before, invhist_value_after,
      invhist_ordtype, invhist_ordnumber, invhist_docnumber, invhist_comments,
      invhist_invuom, invhist_unitcost, invhist_xfer_warehous_id, invhist_posted,
      invhist_series )
  SELECT
    _invhistid, itemsite_id, pTransType, _timestamp,
    pQty, itemsite_qtyonhand,
    (itemsite_qtyonhand + (_sense * pQty)),
    itemsite_costmethod, itemsite_value, itemsite_value + (_r.cost * _sense * pQty),
    pOrderType, pOrderNumber, pDocNumber, pComments,
    uom_name, _r.cost, _xferwhsid, FALSE, pItemlocSeries
  FROM itemsite, item, uom
  WHERE ( (itemsite_item_id=item_id)
   AND (item_inv_uom_id=uom_id)
   AND (itemsite_id=pItemsiteid) );

  IF (pCreditid IN (SELECT accnt_id FROM accnt)) THEN
    _creditid = pCreditid;
  ELSE
    SELECT warehous_default_accnt_id INTO _creditid
    FROM itemsite, whsinfo
    WHERE ( (itemsite_warehous_id=warehous_id)
      AND  (itemsite_id=pItemsiteid) );
  END IF;

  IF (pDebitid IN (SELECT accnt_id FROM accnt)) THEN
    _debitid = pDebitid;
  ELSE
    SELECT warehous_default_accnt_id INTO _debitid
    FROM itemsite, whsinfo
    WHERE ( (itemsite_warehous_id=warehous_id)
      AND  (itemsite_id=pItemsiteid) );
  END IF;

  --  Post the G/L Transaction
  IF (_creditid <> _debitid) THEN
    SELECT insertGLTransaction(pModule, pOrderType, pOrderNumber, pComments,
                               _creditid, _debitid, _invhistid,
                               (_r.cost * pQty), _timestamp::DATE, FALSE) INTO _glreturn;
  END IF;

  --  Distribute this if this itemsite is controlled
  IF ( _r.lotserial OR _r.loccntrl ) THEN

    _itemlocdistid := nextval('itemlocdist_itemlocdist_id_seq');
    INSERT INTO itemlocdist
    ( itemlocdist_id,
      itemlocdist_itemsite_id,
      itemlocdist_source_type,
      itemlocdist_reqlotserial,
      itemlocdist_distlotserial,
      itemlocdist_expiration,
      itemlocdist_qty,
      itemlocdist_series,
      itemlocdist_invhist_id,
      itemlocdist_order_type,
      itemlocdist_order_id )
    SELECT _itemlocdistid,
           pItemsiteid,
           'O',
           (((pQty * _sense) > 0)  AND _r.lotserial),
           ((pQty * _sense) < 0),
           endOfTime(),
           (_sense * pQty),
           pItemlocSeries,
           _invhistid,
           pOrderType, 
           CASE WHEN pOrderType='SO' THEN getSalesLineItemId(pOrderNumber)
                ELSE NULL
           END;

    -- populate distributions if invhist_id parameter passed to undo
    IF (pInvhistid IS NOT NULL) THEN
      INSERT INTO itemlocdist
        ( itemlocdist_itemlocdist_id, itemlocdist_source_type, itemlocdist_source_id,
          itemlocdist_itemsite_id, itemlocdist_ls_id, itemlocdist_expiration,
          itemlocdist_qty, itemlocdist_series, itemlocdist_invhist_id ) 
      SELECT _itemlocdistid, 'L', COALESCE(invdetail_location_id, -1),
             invhist_itemsite_id, invdetail_ls_id,  COALESCE(invdetail_expiration, endoftime()),
             (invdetail_qty * -1.0), pItemlocSeries, _invhistid
      FROM invhist JOIN invdetail ON (invdetail_invhist_id=invhist_id)
      WHERE (invhist_id=pInvhistid);

      IF ( _r.lotserial)  THEN          
        INSERT INTO lsdetail 
          ( lsdetail_itemsite_id, lsdetail_ls_id, lsdetail_created,
            lsdetail_source_type, lsdetail_source_id, lsdetail_source_number ) 
        SELECT invhist_itemsite_id, invdetail_ls_id, CURRENT_TIMESTAMP,
               'I', _itemlocdistid, ''
        FROM invhist JOIN invdetail ON (invdetail_invhist_id=invhist_id)
        WHERE (invhist_id=pInvhistid);
      END IF;

      PERFORM distributeitemlocseries(pItemlocSeries);
      
    END IF;

  END IF;   -- end of distributions

  -- These records will be used for posting G/L transactions to trial balance after records committed.
  -- If we try to do it now concurrency locking prevents any transactions while
  -- user enters item distribution information.  Cant have that.
  INSERT INTO itemlocpost ( itemlocpost_glseq, itemlocpost_itemlocseries)
  VALUES ( _glreturn, pItemlocSeries );

  RETURN _invhistid;

END;
$$;


ALTER FUNCTION public.postinvtrans(pitemsiteid integer, ptranstype text, pqty numeric, pmodule text, pordertype text, pordernumber text, pdocnumber text, pcomments text, pdebitid integer, pcreditid integer, pitemlocseries integer, ptimestamp timestamp with time zone, pcostovrld numeric, pinvhistid integer) OWNER TO admin;

--
-- TOC entry 2020 (class 1255 OID 146566844)
-- Dependencies: 4536 8
-- Name: postitemlocseries(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postitemlocseries(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
  pItemlocseries ALIAS FOR $1;
  _result INTEGER;

BEGIN

  PERFORM postIntoTrialBalance(itemlocpost_glseq)
  FROM (
    SELECT DISTINCT itemlocpost_glseq, gltrans_accnt_id
    FROM itemlocpost
      JOIN gltrans ON (itemlocpost_glseq=gltrans_sequence)
    WHERE (itemlocpost_itemlocseries=pItemlocseries)
    ORDER BY gltrans_accnt_id
  ) AS data;
  
  PERFORM postInvHist(invhist_id)
  FROM invhist
    JOIN itemsite ON (invhist_itemsite_id=itemsite_id)
  WHERE ( (invhist_series=pItemlocseries)
  AND ( NOT invhist_posted) 
  AND ( NOT itemsite_freeze) );

  DELETE FROM itemlocpost WHERE (itemlocpost_itemlocseries=pItemlocseries);

  RETURN TRUE;
  
END;
$_$;


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

--
-- TOC entry 2024 (class 1255 OID 146566845)
-- Dependencies: 4536 8
-- Name: postjournals(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postjournals(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;
  _transCount INTEGER := 0;
  _journalnumber INTEGER := fetchJournalNumber('J/P');
  _sequence INTEGER := fetchGLSequence();
  _sltrans RECORD;
BEGIN

--  Make sure that we balance
  IF (SELECT SUM(sltrans_amount) != 0
      FROM sltrans
      WHERE ((NOT sltrans_posted )
       AND (sltrans_sequence=pSequence))) THEN
     RAISE EXCEPTION 'Can not post journals. Transactions do not balance in selected date range.';
  END IF;

--  March through the sltrans members, posting them one at a time
  FOR _sltrans IN SELECT sltrans_source, sltrans_accnt_id,
                          SUM(sltrans_amount) as amount
                     FROM sltrans
                    WHERE ((sltrans_amount<>0.0)
                      AND  (NOT sltrans_posted)
                      AND  (sltrans_sequence=pSequence))
                    GROUP BY sltrans_source, sltrans_accnt_id LOOP

-- refuse to accept postings into closed periods
    IF (SELECT BOOL_AND(COALESCE(period_closed, FALSE))
        FROM accnt LEFT OUTER JOIN
             period ON (CURRENT_DATE BETWEEN period_start AND period_end)
        WHERE (accnt_id = _sltrans.sltrans_accnt_id)) THEN
      RAISE EXCEPTION 'Cannot post to closed period (%).', _sltrans.sltrans_distdate;
      RETURN -4;        -- remove raise exception when all callers check return code
    END IF;

-- refuse to accept postings into frozen periods without proper priv
    IF (SELECT NOT BOOL_AND(checkPrivilege('PostFrozenPeriod')) AND
               BOOL_AND(COALESCE(period_freeze, FALSE))
        FROM accnt LEFT OUTER JOIN
             period ON (CURRENT_DATE BETWEEN period_start AND period_end)
        WHERE (accnt_id = _sltrans.sltrans_accnt_id)) THEN
      RAISE EXCEPTION 'Cannot post to frozen period (%).', _sltrans.sltrans_distdate;
      RETURN -4;        -- remove raise exception when all callers check return code
    END IF;

    IF (_sltrans.amount != 0) THEN
       INSERT INTO gltrans
        ( gltrans_posted, gltrans_exported, gltrans_created, gltrans_date,
          gltrans_sequence, gltrans_accnt_id, gltrans_source, gltrans_notes,
          gltrans_doctype, gltrans_docnumber, gltrans_amount, gltrans_journalnumber, gltrans_rec )
        VALUES
        ( FALSE, FALSE, CURRENT_TIMESTAMP, CURRENT_DATE,
          _sequence, _sltrans.sltrans_accnt_id, _sltrans.sltrans_source, 'Journal Posting',
          'JP', _journalnumber, _sltrans.amount, _journalnumber, TRUE );
      
      _transCount := _transCount + 1;
    END IF;
  END LOOP;

--  Update all of the posted sltrans members
  UPDATE sltrans SET
    sltrans_posted=true,
    sltrans_gltrans_journalnumber=_journalnumber
  WHERE ((NOT sltrans_posted)
    AND (sltrans_sequence=pSequence));

  PERFORM postIntoTrialBalance(_sequence);

  RETURN _journalnumber;

END;
$_$;


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

--
-- TOC entry 2025 (class 1255 OID 146566846)
-- Dependencies: 4536 8
-- Name: postjournals(text[], date, date, date); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postjournals(text[], date, date, date) RETURNS SETOF integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pSources		ALIAS FOR $1;
  pStartDate 		ALIAS FOR $2;
  pEndDate		ALIAS FOR $3;
  pDistDate     	ALIAS FOR $4;
  _i INTEGER;
BEGIN
  FOR _i IN 1..ARRAY_UPPER(pSources,1)
  LOOP
    RETURN NEXT postJournals(pSources[_i], pStartDate, pEndDate, pDistDate);
  END LOOP;
  RETURN;
END;
$_$;


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

--
-- TOC entry 2026 (class 1255 OID 146566847)
-- Dependencies: 4536 8
-- Name: postjournals(text, date, date, date); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postjournals(text, date, date, date) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pSource		ALIAS FOR $1;
  pStartDate 		ALIAS FOR $2;
  pEndDate		ALIAS FOR $3;
  pDistDate     	ALIAS FOR $4;
  _transCount INTEGER := 0;
  _journalnumber INTEGER := fetchJournalNumber('J/P');
  _sequence INTEGER := fetchGLSequence();
  _sltrans RECORD;
BEGIN

--  Make sure that we balance
  IF (SELECT SUM(sltrans_amount) != 0
      FROM sltrans
      WHERE ((NOT sltrans_posted )
       AND (sltrans_source=pSource)
       AND (sltrans_date BETWEEN pStartDate AND pEndDate))) THEN
     RAISE EXCEPTION 'Can not post journals. Transactions do not balance in selected date range.';
  END IF;

--  March through the sltrans members, posting them one at a time
  FOR _sltrans IN SELECT sltrans_source, sltrans_accnt_id,
                          SUM(sltrans_amount) as amount
                     FROM sltrans
                    WHERE ((sltrans_amount<>0.0)
                      AND  (NOT sltrans_posted)
                      AND  (sltrans_source=pSource)
                      AND  (sltrans_date BETWEEN pStartDate AND pEndDate))
                    GROUP BY sltrans_source, sltrans_accnt_id LOOP

-- refuse to accept postings into frozen periods if any of the accounts disallow it
    IF (SELECT NOT BOOL_AND(checkPrivilege('PostFrozenPeriod')) AND
               BOOL_AND(COALESCE(period_freeze, FALSE))
        FROM accnt LEFT OUTER JOIN
             period ON (pDistDate BETWEEN period_start AND period_end)
        WHERE (accnt_id = _sltrans.sltrans_accnt_id)) THEN
      RAISE EXCEPTION 'Cannot post to frozen period (%).', _sltrans.sltrans_distdate;
      RETURN -4;        -- remove raise exception when all callers check return code
    END IF;

    IF (_sltrans.amount != 0) THEN
       INSERT INTO gltrans
        ( gltrans_posted, gltrans_exported, gltrans_created, gltrans_date,
          gltrans_sequence, gltrans_accnt_id, gltrans_source, gltrans_notes,
          gltrans_doctype, gltrans_docnumber, gltrans_amount, gltrans_journalnumber )
        VALUES
        ( FALSE, FALSE, CURRENT_TIMESTAMP, pDistDate,
          _sequence, _sltrans.sltrans_accnt_id, _sltrans.sltrans_source, 'Journal Posting',
          'JP', _journalnumber, _sltrans.amount, _journalnumber );
      
      _transCount := _transCount + 1;
    END IF;
  END LOOP;

--  Update all of the posted sltrans members
  UPDATE sltrans SET
    sltrans_posted=true,
    sltrans_gltrans_journalnumber=_journalnumber
  WHERE ((NOT sltrans_posted)
    AND (sltrans_source=pSource)
    AND (sltrans_date BETWEEN pStartDate AND pEndDate));

  PERFORM postIntoTrialBalance(_sequence);

  RETURN _journalnumber;

END;
$_$;


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

--
-- TOC entry 2028 (class 1255 OID 146566849)
-- Dependencies: 4536 8
-- Name: postmessage(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postmessage(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;
  pText ALIAS FOR $2;
  _msgid INTEGER;

BEGIN

  SELECT NEXTVAL('msg_msg_id_seq') INTO _msgid;
  INSERT INTO msg
  (msg_id, msg_posted, msg_scheduled, msg_expires, msg_username, msg_text)
  VALUES
  (_msgid, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, endOfTime(), getEffectiveXtUser(), pText);

  INSERT INTO msguser
  ( msguser_msg_id, msguser_username )
  VALUES
  ( _msgid, pUsername );

  NOTIFY "messagePosted";

  RETURN _msgid;

END;
$_$;


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

--
-- TOC entry 2027 (class 1255 OID 146566848)
-- Dependencies: 4536 8
-- Name: postmessage(timestamp without time zone, timestamp without time zone, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postmessage(timestamp without time zone, timestamp without time zone, 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
  pScheduled ALIAS FOR $1;
  pExpires ALIAS FOR $2;
  pText ALIAS FOR $3;
  _msgid INTEGER;

BEGIN

  SELECT NEXTVAL('msg_msg_id_seq') INTO _msgid;
  INSERT INTO msg
  (msg_id, msg_posted, msg_scheduled, msg_expires, msg_username, msg_text)
  VALUES
  (_msgid, CURRENT_TIMESTAMP, pScheduled, pExpires, getEffectiveXtUser(), pText);

  INSERT INTO msguser
  ( msguser_msg_id, msguser_username )
  SELECT _msgid, usr_username
  FROM usr
  WHERE (usr_username <> getEffectiveXtUser());

  NOTIFY "messagePosted";

  RETURN _msgid;

END;
$_$;


ALTER FUNCTION public.postmessage(timestamp without time zone, timestamp without time zone, text) OWNER TO admin;

--
-- TOC entry 2029 (class 1255 OID 146566850)
-- Dependencies: 4536 8
-- Name: postmessage(timestamp without time zone, timestamp without time zone, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postmessage(timestamp without time zone, timestamp without time zone, 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
  pScheduled ALIAS FOR $1;
  pExpires ALIAS FOR $2;
  pUsername ALIAS FOR $3;
  pText ALIAS FOR $4;
  _msgid INTEGER;

BEGIN

  SELECT NEXTVAL('msg_msg_id_seq') INTO _msgid;
  INSERT INTO msg
  (msg_id, msg_posted, msg_scheduled, msg_expires, msg_username, msg_text)
  VALUES
  (_msgid, CURRENT_TIMESTAMP, pScheduled, pExpires, getEffectiveXtUser(), pText);

  INSERT INTO msguser
  ( msguser_msg_id, msguser_username )
  VALUES
  ( _msgid, pUsername );

  NOTIFY "messagePosted";

  RETURN _msgid;

END;
$_$;


ALTER FUNCTION public.postmessage(timestamp without time zone, timestamp without time zone, text, text) OWNER TO admin;

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

CREATE FUNCTION postmisccount(pitemsiteid integer, pqty numeric, pcomments 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
  pItemsiteid ALIAS FOR $1;
  pQty ALIAS FOR $2;
  pComments ALIAS FOR $3;
  _invcntid INTEGER;
  _result INTEGER;

BEGIN

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

  SELECT invcnt_id INTO _invcntid
  FROM invcnt
  WHERE ( (NOT invcnt_posted)
   AND (invcnt_itemsite_id=pItemsiteid) );

  IF (_invcntid IS NULL) THEN
    _invcntid := NEXTVAL('invcnt_invcnt_id_seq');

    INSERT INTO invcnt
     ( invcnt_id, invcnt_itemsite_id, invcnt_tagdate,
       invcnt_qoh_before, invcnt_qoh_after,
       invcnt_tag_username, invcnt_cntdate, invcnt_cnt_username,
       invcnt_postdate, invcnt_post_username, invcnt_posted,
       invcnt_priority, invcnt_comments )
    SELECT _invcntid, pItemsiteid, now(),
           itemsite_qtyonhand, pQty,
           getEffectiveXtUser(), now(), getEffectiveXtUser(),
           now(), getEffectiveXtUser(), FALSE,
           FALSE, pComments
    FROM itemsite
    WHERE (itemsite_id=pItemsiteid);

    SELECT postCountTag(_invcntid, FALSE) INTO _result;
    IF (_result < 0) THEN
      DELETE FROM invcnt
      WHERE (invcnt_id=_invcntid);
    END IF;

    RETURN _result;
  ELSE
    RETURN -2;
  END IF;

END;
$_$;


ALTER FUNCTION public.postmisccount(pitemsiteid integer, pqty numeric, pcomments text) OWNER TO admin;

--
-- TOC entry 2011 (class 1255 OID 146566852)
-- Dependencies: 4536 8
-- Name: postpogltransactions(); Type: FUNCTION; Schema: public; Owner: admin
--

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

  UPDATE gltrans
  SET gltrans_exported=TRUE
  WHERE ( (NOT gltrans_exported)
   AND (gltrans_source='A/P')
   AND (gltrans_doctype IN ('VO')) );

  RETURN TRUE;

END;
$$;


ALTER FUNCTION public.postpogltransactions() OWNER TO admin;

--
-- TOC entry 2021 (class 1255 OID 146566853)
-- Dependencies: 4536 8
-- Name: postporeceipt(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postporeceipt(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 postReceipt($1, $2);
END;
$_$;


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

--
-- TOC entry 2022 (class 1255 OID 146566854)
-- Dependencies: 4536 8
-- Name: postporeceipts(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postporeceipts(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 postReceipts('PO', $1, NULL);
END;
$_$;


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

--
-- TOC entry 2023 (class 1255 OID 146566855)
-- Dependencies: 4536 8
-- Name: postporeturncreditmemo(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  RETURN postPoReturnCreditMemo(pPorejectId, NULL);
END;
$_$;


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

--
-- TOC entry 2031 (class 1255 OID 146566856)
-- Dependencies: 4536 8
-- Name: postporeturncreditmemo(integer, numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postporeturncreditmemo(integer, numeric) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pPorejectId ALIAS FOR $1;
  pAmount ALIAS FOR $2;
  _p RECORD;
  _a RECORD;
  _itemsiteId INTEGER;
  _docNumber TEXT;
  _sequence INTEGER;
  _journalNumber INTEGER;
  _apopenid INTEGER;
  _exchGainItem NUMERIC;
  _itemAmount_base NUMERIC;
  _itemAmount NUMERIC;
  _glseriesTotal NUMERIC;
  _tmpTotal NUMERIC;
  _test INTEGER;
  _exchDate DATE;
  _tax RECORD;
  _taxAmount NUMERIC := 0;
  _taxAmount_base NUMERIC;
  _apaccntid INTEGER;

BEGIN
--Set things up
  SELECT NEXTVAL('apopen_apopen_id_seq') INTO _apopenid;
  SELECT fetchGLSequence() INTO _sequence;
  SELECT fetchJournalNumber('AP-MISC') INTO _journalNumber;
  SELECT fetchapmemonumber() INTO _docNumber;
  _glseriesTotal := 0;

--Get poreject data
  SELECT pohead_vend_id, pohead_number, pohead_curr_id, pohead_orderdate, pohead_taxzone_id,
         poitem_id, poitem_itemsite_id,poitem_expcat_id, poitem_taxtype_id,
        itemsite_costcat_id, poreject_qty, poreject_date,
        ('Return of Item ' || COALESCE(item_number,poitem_vend_item_number)
           || ', qty. ' || formatqty(poreject_qty)) AS notes,
        poreject_value AS value,
        currToBase(pohead_curr_id,(poitem_unitprice * poreject_qty),CURRENT_DATE) AS itemAmount_base,
        (poitem_unitprice * poreject_qty) AS itemAmount
        INTO _p
  FROM pohead, poreject, poitem
        LEFT OUTER JOIN itemsite ON (poitem_itemsite_id=itemsite_id)
        LEFT OUTER JOIN item ON (itemsite_item_id=item_id)
  WHERE ((poreject_poitem_id=poitem_id)
  AND (pohead_id=poitem_pohead_id)
  AND (poreject_id=pPorejectId));

  _itemAmount := _p.itemAmount;
  _itemAmount_base := _p.itemAmount_base;
  IF (pAmount IS NOT NULL) THEN
    _itemAmount := pAmount;
    _itemAmount_base := currToBase(_p.pohead_curr_id, pAmount, CURRENT_DATE);
  END IF;
  

--  Grab the G/L Accounts
  IF (COALESCE(_p.poitem_itemsite_id, -1) = -1) THEN
    SELECT pp.accnt_id AS pp_accnt_id,
           lb.accnt_id AS lb_accnt_id INTO _a
    FROM expcat, accnt AS pp, accnt AS lb
    WHERE ( (expcat_purchprice_accnt_id=pp.accnt_id)
     AND (expcat_liability_accnt_id=lb.accnt_id)
     AND (expcat_id=_p.poitem_expcat_id) );
    IF (NOT FOUND) THEN
      RAISE EXCEPTION 'Cannot Post Credit Memo due to unassigned G/L Accounts.';
    END IF;
  ELSE
    SELECT pp.accnt_id AS pp_accnt_id,
           lb.accnt_id AS lb_accnt_id INTO _a
    FROM costcat, accnt AS pp, accnt AS lb
    WHERE ( (costcat_purchprice_accnt_id=pp.accnt_id)
     AND (costcat_liability_accnt_id=lb.accnt_id)
     AND (costcat_id=_p.itemsite_costcat_id) );
    IF (NOT FOUND) THEN
      RAISE EXCEPTION 'Cannot Post Credit Memo due to unassigned G/L Accounts.';
    END IF;
  END IF;

--  AP Open Item record
    INSERT INTO apopen
    ( apopen_id, apopen_username, apopen_journalnumber,
      apopen_vend_id, apopen_docnumber, apopen_doctype, apopen_ponumber,
      apopen_docdate, apopen_duedate, apopen_distdate, apopen_terms_id,
      apopen_amount, apopen_paid, apopen_open, apopen_notes, apopen_accnt_id, apopen_curr_id,
      apopen_closedate )
    VALUES
    ( _apopenid, getEffectiveXtUser(), _journalNumber,
      _p.pohead_vend_id, _docNumber, 'C', _p.pohead_number,
      CURRENT_DATE, CURRENT_DATE, CURRENT_DATE, -1,
      round(_itemAmount, 2), 0, (round(_itemAmount, 2) <> 0), _p.notes, -1, _p.pohead_curr_id,
      CASE WHEN (round(_itemAmount, 2) = 0) THEN _p.poreject_date END );

-- Taxes
    FOR _tax IN
      SELECT taxdetail_tax_id, sum(taxdetail_tax) AS taxdetail_tax,
        currToBase(_p.pohead_curr_id, round(sum(taxdetail_tax),2), current_date) AS taxbasevalue
      FROM calculateTaxDetail(_p.pohead_taxzone_id, _p.poitem_taxtype_id,
	  		       current_date, _p.pohead_curr_id, 
			       _itemAmount) 
      GROUP BY taxdetail_tax_id
    LOOP
      INSERT INTO apopentax (taxhist_basis,taxhist_percent,taxhist_amount,taxhist_docdate, taxhist_tax_id, taxhist_tax, 
                             taxhist_taxtype_id, taxhist_parent_id, taxhist_journalnumber ) 
      VALUES (0, 0, 0, current_date, _tax.taxdetail_tax_id, _tax.taxdetail_tax, getadjustmenttaxtypeid(), 
              _apopenid, _journalNumber);

      _taxAmount := _taxAmount + _tax.taxdetail_tax;
 
    END LOOP;

    _taxAmount_base := addTaxToGLSeries(_sequence,
		       'A/P', 'CM', _docNumber,
		       _p.pohead_curr_id, current_date, current_date,
                      'apopentax', _apopenid,
                      _p.notes);

    UPDATE apopen SET apopen_amount = round(_itemAmount + _taxAmount,2)
    WHERE (apopen_id = _apopenid);

--  Distribute from the clearing account
    PERFORM insertIntoGLSeries( _sequence, 'A/P', 'CM', _docNumber,
                _a.lb_accnt_id,
                round(_p.value, 2),
                current_date, _p.notes );
    _glseriesTotal := _glseriesTotal + round(_p.value, 2);

--  Distribute the remaining variance to the Purchase Price Variance account
    IF (round(_itemAmount_base, 2) <> round(_p.value, 2)) THEN
      _tmpTotal := round(_itemAmount_base, 2) - round(_p.value, 2);
      PERFORM insertIntoGLSeries( _sequence, 'A/P', 'CM', _docNumber,
                                  _a.pp_accnt_id,
                                  _tmpTotal,
                                  current_date, _p.notes );
        _glseriesTotal := _glseriesTotal + _tmpTotal;
    END IF;

--  Post the reject item for this P/O Item as Invoiced
    UPDATE poreject
    SET poreject_invoiced=TRUE
    WHERE poreject_id=pPorejectId;

--  Update the qty vouchered field
    UPDATE poitem
       SET poitem_qty_vouchered = (poitem_qty_vouchered - _p.poreject_qty)
     WHERE (poitem_id=_p.poitem_id);

--  Post to A/P
  SELECT findAPAccount(_p.pohead_vend_id) INTO _apaccntid;
  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Cannot Post Credit Memo due to an unassigned A/P Account.';
  END IF;

  SELECT insertIntoGLSeries( _sequence, 'A/P', 'CM', _docNumber,
                             _apaccntid, round(_itemAmount_base + _taxAmount_base, 2) *-1,
                             current_date, _p.notes ) INTO _test;
  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Cannot Post Credit Memo.';
  END IF;

-- Clean up loose ends

  _glseriesTotal := _glseriesTotal + round(_itemAmount_base, 2)*-1;

  IF (round(_glseriesTotal, 2) != 0) THEN
        PERFORM insertIntoGLSeries(_sequence, 'A/P', 'CM',
            'Currency Exchange Rounding - ' || _docNumber,
            getGainLossAccntId(_apaccntid), round(_glseriesTotal, 2) * -1,
           current_date, _p.notes);
  END IF;

--  Post it all
  PERFORM postGLSeries(_sequence, _journalNumber);

  RETURN _journalNumber;

END;
$_$;


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

--
-- TOC entry 1953 (class 1255 OID 146566858)
-- Dependencies: 4536 8
-- Name: postporeturns(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postporeturns(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;
  _itemlocSeries INTEGER;
  _p RECORD;
  _returnval	INTEGER;

BEGIN

  _itemlocSeries := 0;

  SELECT postPoReturns(pPoheadid,false) INTO _itemlocseries;

  RETURN _itemlocSeries;

END;
$_$;


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

--
-- TOC entry 1954 (class 1255 OID 146566859)
-- Dependencies: 4536 8
-- Name: postporeturns(integer, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postporeturns(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
  pPoheadid ALIAS FOR $1;
  pCreateMemo ALIAS FOR $2;
  _itemlocSeries INTEGER;
  _p RECORD;
  _returnval	INTEGER;
  _tmp        INTEGER;
  _pricevar   NUMERIC := 0.00;
  _invhistid		INTEGER;
  _journalNumber INTEGER := fetchJournalNumber('GL-MISC');

BEGIN

  _itemlocSeries := 0;

  FOR _p IN SELECT pohead_number, pohead_curr_id, poreject_id, poitem_prj_id,
		   poreject_poitem_id, poitem_id, poitem_expcat_id, poitem_linenumber,
		   currToBase(COALESCE(recv_purchcost_curr_id, pohead_curr_id),
                              COALESCE(recv_purchcost, poitem_unitprice),
			      pohead_orderdate) AS poitem_unitprice_base,
                   COALESCE(itemsite_id, -1) AS itemsiteid, poitem_invvenduomratio,
                   SUM(poreject_qty) AS totalqty,
                   itemsite_item_id, itemsite_costmethod, itemsite_controlmethod, recv_date
            FROM pohead JOIN poitem ON (poitem_pohead_id=pohead_id)
                        JOIN poreject ON (poreject_poitem_id=poitem_id AND NOT poreject_posted) 
                        LEFT OUTER JOIN itemsite ON (poitem_itemsite_id=itemsite_id)
                        LEFT OUTER JOIN recv ON (recv_id=poreject_recv_id)
            WHERE (pohead_id=pPoheadid)
            GROUP BY poreject_id, pohead_number, poreject_poitem_id, poitem_id, poitem_prj_id,
		     poitem_expcat_id, poitem_linenumber, poitem_unitprice, pohead_curr_id,
		     pohead_orderdate, itemsite_id, poitem_invvenduomratio,
                     itemsite_item_id, itemsite_costmethod, itemsite_controlmethod, recv_date,
                     recv_purchcost_curr_id, recv_purchcost LOOP

    IF (_p.itemsiteid = -1) THEN
        SELECT insertGLTransaction( 'S/R', 'PO', _p.pohead_number, 'Return Non-Inventory to P/O',
                                     expcat_liability_accnt_id, 
                                     getPrjAccntId(_p.poitem_prj_id, expcat_exp_accnt_id), -1,
                                     round(_p.poitem_unitprice_base * _p.totalqty * -1, 2),
				     CURRENT_DATE ) INTO _returnval
        FROM expcat
        WHERE (expcat_id=_p.poitem_expcat_id);

        UPDATE poreject
        SET poreject_posted=TRUE, poreject_value= round(_p.poitem_unitprice_base * _p.totalqty, 2)
        WHERE (poreject_id=_p.poreject_id);

    ELSEIF (_p.itemsite_controlmethod='N') THEN
      SELECT insertGLTransaction('S/R', 'PO', _p.pohead_number, 'Return Non-Controlled Inventory from PO',
                                 costcat_liability_accnt_id,
                                 getPrjAccntId(_p.poitem_prj_id, costcat_exp_accnt_id), -1,
                                 round((_p.poitem_unitprice_base * _p.totalqty * -1), 2),
                                 CURRENT_DATE ) INTO _returnval
      FROM itemsite, costcat
      WHERE((itemsite_costcat_id=costcat_id)
        AND (itemsite_id=_p.itemsiteid));
      IF (_returnval = -3) THEN -- zero value transaction
        _returnval := 0;
      END IF;
      UPDATE poreject
      SET poreject_posted=TRUE, poreject_value= round(_p.poitem_unitprice_base * _p.totalqty, 2)
      WHERE (poreject_id=_p.poreject_id);
    ELSE
      IF (_itemlocSeries = 0) THEN
        SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;
      END IF;

      SELECT postInvTrans( itemsite_id, 'RP', (_p.totalqty * _p.poitem_invvenduomratio * -1),
                           'S/R', 'PO', (_p.pohead_number || '-' || _p.poitem_linenumber::TEXT), '', 'Return Inventory to P/O',
                           costcat_asset_accnt_id, costcat_liability_accnt_id, _itemlocSeries, CURRENT_TIMESTAMP) INTO _returnval
      FROM itemsite, costcat
      WHERE ( (itemsite_costcat_id=costcat_id)
       AND (itemsite_id=_p.itemsiteid) );

      UPDATE poreject
      SET poreject_posted=TRUE, poreject_value=(invhist_unitcost *_p.totalqty * _p.poitem_invvenduomratio)
      FROM invhist
      WHERE ((poreject_id=_p.poreject_id)
      AND (invhist_id=_returnval));

    END IF;

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


    UPDATE poitem
    SET poitem_qty_returned=(poitem_qty_returned + _p.totalqty),
	poitem_status='O'
    WHERE (poitem_id=_p.poitem_id);

      IF (fetchMetricBool('RecordPPVonReceipt')) THEN -- If the 'Purchase Price Variance on Receipt' option is true
         _invhistid := _returnval;
         -- Find the difference in the purchase price value expected from the P/O and the value of the transaction
         SELECT ((_p.poitem_unitprice_base * poitem_qty_returned) - (invhist_value_before - invhist_value_after)) INTO _pricevar
         FROM invhist, poitem
         WHERE ((invhist_id = _invhistid)
           AND  (poitem_id=_p.poitem_id));

         -- If difference exists then
         IF (_pricevar <> 0.00) THEN
           -- Record an additional GL Transaction for the purchase price variance
           SELECT insertGLTransaction( _journalNumber,
                'S/R', 'PO', _p.pohead_number,
                                       'Purchase price variance adjusted for P/O ' || _p.pohead_number || ' for item ' || _p.poitem_linenumber::TEXT,
                                       costcat_liability_accnt_id,
                                       getPrjAccntId(_p.poitem_prj_id, costcat_purchprice_accnt_id), -1,
                                       _pricevar,
                                       CURRENT_DATE, false ) INTO _tmp
           FROM itemsite, costcat, poitem
           WHERE ((itemsite_costcat_id=costcat_id)
              AND (itemsite_id=poitem_itemsite_id) );
           IF (NOT FOUND) THEN
             RAISE EXCEPTION 'Could not insert G/L transaction: no cost category found for itemsite_id %',
             _p.itemsiteid;
           ELSIF (_tmp < 0 AND _tmp != -3) THEN -- error but not 0-value transaction
             RETURN _tmp;
           ELSE
             -- Posting to trial balance is deferred to prevent locking
             INSERT INTO itemlocpost ( itemlocpost_glseq, itemlocpost_itemlocseries)
             VALUES ( _tmp, _itemlocSeries );
           END IF;
         END IF;
       END IF;

    IF (pCreateMemo) THEN
	SELECT postPoReturnCreditMemo(_p.poreject_id) INTO _returnval;
    END IF;

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

  END LOOP;

  RETURN _itemlocSeries;

END;
$_$;


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

--
-- TOC entry 2032 (class 1255 OID 146566861)
-- Dependencies: 4536 8
-- Name: postproduction(integer, numeric, boolean, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postproduction(integer, numeric, boolean, 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
  RAISE NOTICE 'postProduction(INTEGER, NUMERIC, BOOLEAN, BOOLEAN) is deprecated. please use postProduction(INTEGER, NUMERIC, BOOLEAN, INTEGER, TIMESTAMP WITH TIME ZONE) instead';
  RETURN postProduction($1, $2, $3, 0, now());
END;
$_$;


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

--
-- TOC entry 2033 (class 1255 OID 146566862)
-- Dependencies: 4536 8
-- Name: postproduction(integer, numeric, boolean, boolean, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postproduction(integer, numeric, boolean, 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.
BEGIN
  RAISE NOTICE 'postProduction(INTEGER, NUMERIC, BOOLEAN, BOOLEAN, INTEGER) is deprecated. please use postProduction(INTEGER, NUMERIC, BOOLEAN, INTEGER, TIMESTAMP WITH TIME ZONE) instead';
  RETURN postProduction($1, $2, $3, $5, now());
END;
$_$;


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

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

CREATE FUNCTION postproduction(integer, numeric, boolean, 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
  pWoid          ALIAS FOR $1;
  pQty           ALIAS FOR $2;
  pBackflush     ALIAS FOR $3;
  pItemlocSeries ALIAS FOR $4;
  pGlDistTS      ALIAS FOR $5;
  _test          INTEGER;
  _invhistid     INTEGER;
  _itemlocSeries INTEGER;
  _parentQty     NUMERIC;
  _r             RECORD;
  _sense         TEXT;
  _wipPost       NUMERIC;
  _woNumber      TEXT;
  _ucost         NUMERIC;

BEGIN

  IF (pQty = 0) THEN
    RETURN 0;
  ELSIF (pQty > 0) THEN
    _sense = 'from';
  ELSE
    _sense = 'to';
  END IF;

  IF ( ( SELECT wo_status
         FROM wo
         WHERE (wo_id=pWoid) ) NOT IN  ('R','E','I') ) THEN
    RETURN -1;
  END IF;

--  Make sure that all Component Item Sites exist
  SELECT bomitem_id INTO _test
  FROM wo, bomitem, itemsite
  WHERE ( (wo_itemsite_id=itemsite_id)
   AND (itemsite_item_id=bomitem_parent_item_id)
   AND (woEffectiveDate(wo_startdate) BETWEEN bomitem_effective AND (bomitem_expires - 1))
   AND (wo_id=pWoid)
   AND (bomitem_rev_id=wo_bom_rev_id)
   AND (bomitem_item_id NOT IN
        ( SELECT component.itemsite_item_id
          FROM itemsite AS component, itemsite AS parent
          WHERE ( (wo_itemsite_id=parent.itemsite_id)
           AND (parent.itemsite_item_id=bomitem_parent_item_id)
           AND (bomitem_item_id=component.itemsite_item_id)
           AND (woEffectiveDate(wo_startdate) BETWEEN bomitem_effective AND (bomitem_expires - 1))
           AND (bomitem_rev_id=wo_bom_rev_id)
           AND (component.itemsite_active)
           AND (component.itemsite_warehous_id=parent.itemsite_warehous_id) ) ) ) )
  LIMIT 1;
  IF (FOUND AND pBackflush) THEN
    RETURN -2;
  END IF;

  SELECT formatWoNumber(pWoid) INTO _woNumber;

  SELECT roundQty(item_fractional, pQty) INTO _parentQty
  FROM wo, itemsite, item
  WHERE ((wo_itemsite_id=itemsite_id)
   AND (itemsite_item_id=item_id)
   AND (wo_id=pWoid));

--  Create the material receipt transaction
  IF (pItemlocSeries = 0) THEN
    SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;
  ELSE
    _itemlocSeries = pItemlocSeries;
  END IF;

  IF (pBackflush) THEN
    FOR _r IN SELECT womatl_id, womatl_qtyiss + 
		     (CASE 
		       WHEN (womatl_qtywipscrap >  ((womatl_qtyfxd + (_parentQty + wo_qtyrcv) * womatl_qtyper) * womatl_scrap)) THEN
                         (womatl_qtyfxd + (_parentQty + wo_qtyrcv) * womatl_qtyper) * womatl_scrap
		       ELSE 
		         womatl_qtywipscrap 
		      END) AS consumed,
		     (womatl_qtyfxd + ((_parentQty + wo_qtyrcv) * womatl_qtyper)) * (1 + womatl_scrap) AS expected
	      FROM womatl, wo, itemsite, item
	      WHERE ((womatl_issuemethod IN ('L', 'M'))
		AND  (womatl_wo_id=pWoid)
		AND  (womatl_wo_id=wo_id)
		AND  (womatl_itemsite_id=itemsite_id)
		AND  (itemsite_item_id=item_id)) LOOP
      -- Don't issue more than should have already been consumed at this point
      IF (pQty > 0) THEN
        IF (noNeg(_r.expected - _r.consumed) > 0) THEN
          SELECT issueWoMaterial(_r.womatl_id, noNeg(_r.expected - _r.consumed), _itemlocSeries, pGlDistTS) INTO _itemlocSeries;
        END IF;
      ELSE
        -- Used by postMiscProduction of disassembly
        SELECT returnWoMaterial(_r.womatl_id, (_r.expected * -1.0), _itemlocSeries, CURRENT_TIMESTAMP, true) INTO _itemlocSeries;
      END IF;

      UPDATE womatl
      SET womatl_issuemethod='L'
      WHERE ( (womatl_issuemethod='M')
       AND (womatl_id=_r.womatl_id) );

    END LOOP;
  END IF;

  SELECT CASE WHEN (pQty < 0 AND itemsite_costmethod='S') THEN stdcost(itemsite_item_id) * pQty
              WHEN (pQty < 0) THEN avgcost(itemsite_id) * pQty
              WHEN (wo_cosmethod = 'D') THEN wo_wipvalue
              ELSE  round((wo_wipvalue - (wo_postedvalue / wo_qtyord * (wo_qtyord -
                    CASE WHEN (wo_qtyord < wo_qtyrcv + pQty) THEN wo_qtyord
                         ELSE wo_qtyrcv + pQty
                    END ))),2)
         END INTO _wipPost
  FROM wo
    JOIN itemsite ON (wo_itemsite_id=itemsite_id)
  WHERE (wo_id=pWoid);

  SELECT postInvTrans( itemsite_id, 'RM', _parentQty,
                       'W/O', 'WO', _woNumber, '', ('Receive Inventory ' || item_number || ' ' || _sense || ' Manufacturing'),
                       costcat_asset_accnt_id, getPrjAccntId(wo_prj_id, costcat_wip_accnt_id), _itemlocSeries, pGlDistTS,
                       -- the following is only actually used when the item is average or job costed
                       _wipPost ) INTO _invhistid
  FROM wo, itemsite, item, costcat
  WHERE ( (wo_itemsite_id=itemsite_id)
   AND (itemsite_item_id=item_id)
   AND (itemsite_costcat_id=costcat_id)
   AND (wo_id=pWoid) );

  IF (pQty < 0 ) THEN
    _wipPost := _wipPost * -1;
  END IF;
  
--  Increase this W/O's received qty decrease its WIP value
  UPDATE wo
  SET wo_qtyrcv = (wo_qtyrcv + _parentQty),
      wo_wipvalue = (wo_wipvalue - (CASE WHEN (itemsite_costmethod IN ('A','J'))
                                               THEN _wipPost
                                         WHEN (itemsite_costmethod='S')
                                               THEN (stdcost(itemsite_item_id) * _parentQty)
                                         ELSE 0.0  END))
  FROM itemsite, item
  WHERE ((wo_itemsite_id=itemsite_id)
   AND (itemsite_item_id=item_id)
   AND (wo_id=pWoid));

--  ROB Increase this W/O's WIP value for custom costing
  SELECT SUM(itemcost_stdcost * _parentQty) INTO _ucost 
  FROM wo JOIN itemsite ON (itemsite_id=wo_itemsite_id)
          JOIN itemcost ON (itemcost_item_id=itemsite_item_id)
          JOIN costelem ON ((costelem_id=itemcost_costelem_id) AND
                            (costelem_exp_accnt_id IS NOT NULL) AND
                            (NOT costelem_sys))
  WHERE (wo_id=pWoid);

  UPDATE wo
  SET wo_wipvalue = (wo_wipvalue + coalesce(_ucost,0))
  WHERE (wo_id=pWoid);

--  ROB Distribute to G/L - create Cost Variance, debit WIP
  PERFORM insertGLTransaction( 'W/O', 'WO', _woNumber,
                               ('Post Other Cost ' || item_number || ' ' || _sense || ' Manufacturing'),
                               getPrjAccntId(wo_prj_id, costelem_exp_accnt_id), 
                               getPrjAccntId(wo_prj_id,costcat_wip_accnt_id), _invhistid,
			       (itemcost_stdcost * _parentQty),
                                pGlDistTS::DATE )
FROM wo, costelem, itemcost, costcat, itemsite, item
WHERE 
  ((wo_id=pWoid) AND
  (wo_itemsite_id=itemsite_id) AND
  (itemsite_item_id=item_id) AND
  (costelem_id = itemcost_costelem_id) AND
  (itemcost_item_id = itemsite_item_id) AND
  (itemsite_costcat_id = costcat_id) AND
  (costelem_exp_accnt_id) IS NOT NULL  AND 
  (costelem_sys = false));
--End


--  Make sure the W/O is at issue status
  UPDATE wo
  SET wo_status='I'
  WHERE (wo_id=pWoid);

  RETURN _itemlocSeries;

END;
$_$;


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

--
-- TOC entry 2034 (class 1255 OID 146566863)
-- Dependencies: 4536 8
-- Name: postproduction(integer, numeric, boolean, boolean, integer, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postproduction(integer, numeric, boolean, boolean, integer, text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  RAISE NOTICE 'postProduction(INTEGER, NUMERIC, BOOLEAN, BOOLEAN, INTEGER, TEXT, TEXT) is deprecated. please use postProduction(INTEGER, NUMERIC, BOOLEAN, INTEGER, TIMESTAMP WITH TIME ZONE) instead';
  RETURN postProduction($1, $2, $3, $5, now());
END;
$_$;


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

--
-- TOC entry 2036 (class 1255 OID 146566866)
-- Dependencies: 4536 8
-- Name: postreceipt(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postreceipt(integer, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2011 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  precvid		ALIAS FOR $1;
  _itemlocSeries	INTEGER := COALESCE($2, 0);
  _freightAccnt		INTEGER;
  _glDate		TIMESTAMP WITH TIME ZONE;
  _o			RECORD;
  _ordertypeabbr	TEXT;
  _r			RECORD;
  _ra			RECORD;
  _recvinvqty          NUMERIC := 0.00;
  _recvvalue		NUMERIC := 0.00;
  _pricevar            NUMERIC := 0.00;
  _tmp			INTEGER;
  _toitemitemid		INTEGER;
  _coheadid		INTEGER;
  _coitemid		INTEGER;
  _linenumber          INTEGER;
  _invhistid		INTEGER;
  _shipheadid		INTEGER;
  _ship               	BOOLEAN;
  _i			RECORD;

BEGIN
  SELECT recv_id, recv_order_type, recv_orderitem_id, recv_qty,
	 round(currToBase(recv_freight_curr_id, recv_freight, recv_date::DATE),
	       2) AS recv_freight_base,
	 recv_freight, recv_freight_curr_id, recv_date, recv_gldistdate,
	 itemsite_id, itemsite_item_id, item_inv_uom_id, itemsite_costmethod,
         itemsite_controlmethod, vend_name, item_number, item_fractional
	 INTO _r
  FROM recv LEFT OUTER JOIN itemsite ON (recv_itemsite_id=itemsite_id)
            LEFT OUTER JOIN item ON (itemsite_item_id=item_id)
            LEFT OUTER JOIN vendinfo ON (recv_vend_id=vend_id)
  WHERE ((recv_id=precvid)
    AND  (NOT recv_posted));

  IF (NOT FOUND) THEN
    IF (_itemlocSeries = 0) THEN
      RETURN -10;
    END IF;
    RETURN _itemlocSeries;

  ELSEIF (_r.recv_qty <= 0) THEN
    RETURN -11;

  ELSIF (_r.recv_order_type ='PO') THEN
    _ordertypeabbr := ('P/O for ' || _r.vend_name || ' for item ' || _r.item_number);

    SELECT pohead_number AS orderhead_number, poitem_id AS orderitem_id,
           poitem_linenumber AS orderitem_linenumber,
	   currToBase(pohead_curr_id,
                      COALESCE(recv_purchcost, poitem_unitprice),
		      recv_date::DATE) AS item_unitprice_base,
	   poitem_invvenduomratio AS invvenduomratio,
	   pohead_orderdate AS orderdate, pohead_dropship,
	   poitem_prj_id AS prj_id INTO _o
    FROM recv, pohead, poitem
    WHERE ((recv_orderitem_id=poitem_id)
      AND  (poitem_pohead_id=pohead_id)
      AND  (NOT recv_posted)
      AND  (recv_id=precvid));
  ELSIF (_r.recv_order_type ='RA') THEN
    _ordertypeabbr := 'R/A for item ' || _r.item_number;
    
    SELECT rahead_id AS orderhead_id, rahead_number AS orderhead_number, raitem_id AS orderitem_id,
           raitem_linenumber AS orderitem_linenumber,
	   currToBase(rahead_curr_id, raitem_unitprice,
		    recv_date::DATE) AS item_unitprice_base,
	   raitem_qty_invuomratio AS invvenduomratio,
	   rahead_authdate AS orderdate,
	   raitem_unitcost AS unitcost,
	   rahead_prj_id AS prj_id INTO _o
    FROM recv, rahead, raitem
    WHERE ((recv_orderitem_id=raitem_id)
      AND  (raitem_rahead_id=rahead_id)
      AND  (NOT recv_posted)
      AND  (recv_id=precvid));
  ELSIF (_r.recv_order_type ='TO') THEN
     _ordertypeabbr := 'T/O for item ' || _r.item_number;

    SELECT tohead_number AS orderhead_number, toitem_id AS orderitem_id,
           toitem_linenumber AS orderitem_linenumber,
	   toitem_stdcost AS item_unitprice_base,
	   1.0 AS invvenduomratio,
	   tohead_orderdate AS orderdate,
	   NULL AS prj_id INTO _o
    FROM recv, tohead, toitem
    WHERE ((recv_orderitem_id=toitem_id)
      AND  (toitem_tohead_id=tohead_id)
      AND  (NOT recv_posted)
      AND  (recv_id=precvid));
  ELSE
    RETURN -13;	-- don't know how to handle this order type
  END IF;

  IF (NOT FOUND) THEN
    IF (_itemlocSeries = 0) THEN
      RETURN -10;
    END IF;
    RETURN _itemlocSeries;
  END IF;

  IF (_itemlocSeries = 0) THEN
    _itemlocSeries := NEXTVAL('itemloc_series_seq');
  ELSEIF (_itemlocSeries < 0) THEN
    RETURN _itemlocSeries;
  END IF;

  _glDate := COALESCE(_r.recv_gldistdate, _r.recv_date);
  _recvinvqty := roundQty(_r.item_fractional, (_r.recv_qty * _o.invvenduomratio));

  IF ( (_r.recv_order_type = 'PO') AND
        (_r.itemsite_id = -1 OR _r.itemsite_id IS NULL OR _r.itemsite_controlmethod = 'N') ) THEN

    IF (_r.itemsite_id IS NOT NULL) THEN
      SELECT insertGLTransaction( fetchJournalNumber('GL-MISC'), 
				  'S/R', _r.recv_order_type, _o.orderhead_number,
	  			  'Receive Non-Controlled Inventory from ' || _ordertypeabbr,
				   costcat_liability_accnt_id,
				   getPrjAccntId(_o.prj_id, costcat_exp_accnt_id), -1,
				   round((_o.item_unitprice_base * _r.recv_qty),2),
				   _glDate::DATE, false ) INTO _tmp
      FROM poitem, itemsite, costcat
      WHERE((poitem_itemsite_id=itemsite_id)
        AND (itemsite_costcat_id=costcat_id)
        AND (poitem_id=_o.orderitem_id));
    ELSE
      SELECT insertGLTransaction(fetchJournalNumber('GL-MISC'),
				  'S/R', _r.recv_order_type, _o.orderhead_number,
	  			  'Receive Non-Inventory from ' || 'P/O for ' || _r.vend_name || ' for ' || expcat_code,
				   expcat_liability_accnt_id,
				   getPrjAccntId(_o.prj_id, expcat_exp_accnt_id), -1,
				   round((_o.item_unitprice_base * _r.recv_qty),2),
				   _glDate::DATE, false ) INTO _tmp
      FROM poitem, expcat
      WHERE((poitem_expcat_id=expcat_id)
        AND (poitem_id=_o.orderitem_id));
    END IF;
      

    IF (_tmp < 0 AND _tmp != -3) THEN -- error but not 0-value transaction
      RETURN _tmp;
    ELSE
      -- Posting to trial balance is deferred to prevent locking
      INSERT INTO itemlocpost ( itemlocpost_glseq, itemlocpost_itemlocseries)
      VALUES ( _tmp, _itemlocSeries );
      
    END IF;

    SELECT insertGLTransaction( fetchJournalNumber('GL-MISC'),
				'S/R', _r.recv_order_type, _o.orderhead_number,
				'Receive Non-Inventory Freight from ' || _ordertypeabbr,
				 expcat_liability_accnt_id,
				 getPrjAccntId(_o.prj_id, expcat_freight_accnt_id), -1,
				 _r.recv_freight_base,
				 _glDate::DATE, false ),
	   expcat_freight_accnt_id INTO _tmp, _freightAccnt
    FROM poitem, expcat
    WHERE((poitem_expcat_id=expcat_id)
      AND (poitem_id=_o.orderitem_id));

    IF (_tmp < 0 AND _tmp != -3) THEN -- error but not 0-value transaction
      RETURN _tmp;
    ELSE
      -- Posting to trial balance is deferred to prevent locking
      INSERT INTO itemlocpost ( itemlocpost_glseq, itemlocpost_itemlocseries)
      VALUES ( _tmp, _itemlocSeries );
    END IF;

    _recvvalue := ROUND((_o.item_unitprice_base * _r.recv_qty),2);

    UPDATE poitem
    SET poitem_qty_received = (poitem_qty_received + _r.recv_qty),
	poitem_freight_received = (poitem_freight_received + _r.recv_freight_base)
    WHERE (poitem_id=_o.orderitem_id);

  ELSEIF ( (_r.recv_order_type = 'RA') AND
           (_r.itemsite_id = -1 OR _r.itemsite_id IS NULL) ) THEN
    RAISE NOTICE 'itemsite controlmethod is %, cannot post receipt.', _r.itemsite_controlmethod;
    RETURN -14;	-- otherwise how do we get the accounts?

  ELSEIF ( (_r.recv_order_type = 'TO') AND
           (_r.itemsite_id = -1 OR _r.itemsite_id IS NULL) ) THEN
    RAISE NOTICE 'itemsite missing';
    RETURN -14;	-- otherwise how do we get the accounts?

  ELSE	-- not ELSIF: some code is shared between diff order types
    IF (_r.recv_order_type = 'PO') THEN
      SELECT postInvTrans( itemsite_id, 'RP'::TEXT,
			   _recvinvqty,
			   'S/R'::TEXT,
			   _r.recv_order_type::TEXT, _o.orderhead_number::TEXT || '-' || _o.orderitem_linenumber::TEXT,
			   ''::TEXT,
			   'Receive Inventory from ' || _ordertypeabbr,
			   costcat_asset_accnt_id, costcat_liability_accnt_id,
			   _itemlocSeries,
			   _glDate,
                           round((_o.item_unitprice_base * _r.recv_qty),2) -- always passing this in since it is ignored if it is not average costed item
			   ) INTO _tmp
      FROM itemsite, costcat
      WHERE ( (itemsite_costcat_id=costcat_id)
       AND (itemsite_id=_r.itemsite_id) );
      IF (NOT FOUND) THEN
	RAISE EXCEPTION 'Could not post inventory transaction: no cost category found for itemsite_id %',
	  _r.itemsite_id;
      ELSIF (_tmp < -1) THEN -- less than -1 because -1 means it is a none controlled item
	IF(_tmp = -3) THEN
	  RETURN -12; -- The GL trans value was 0 which means we likely do not have a std cost
	END IF;
	RETURN _tmp;
      END IF;

      -- If the 'Purchase Price Variance on Receipt' option is true
      IF (fetchMetricBool('RecordPPVonReceipt')) THEN
        _invhistid := _tmp;
        -- Find the difference in the purchase price value expected from the P/O and the value of the transaction
        SELECT ((_o.item_unitprice_base * _r.recv_qty) - (invhist_value_after - invhist_value_before)) INTO _pricevar
        FROM invhist
        WHERE (invhist_id = _invhistid);

        -- If difference exists then
        IF (_pricevar <> 0.00) THEN
          -- Record an additional GL Transaction for the purchase price variance
          SELECT insertGLTransaction( fetchJournalNumber('GL-MISC'),
				       'S/R', _r.recv_order_type, _o.orderhead_number,
                                      'Purchase price variance adjusted for P/O ' || _o.orderhead_number || ' for item ' || _r.item_number,
                                      costcat_liability_accnt_id,
                                      getPrjAccntId(_o.prj_id, costcat_purchprice_accnt_id), -1,
                                      _pricevar,
                                      _glDate::DATE, false ) INTO _tmp
          FROM itemsite, costcat
          WHERE ((itemsite_costcat_id=costcat_id)
             AND (itemsite_id=_r.itemsite_id) );
          IF (NOT FOUND) THEN
            RAISE EXCEPTION 'Could not insert G/L transaction: no cost category found for itemsite_id %',
            _r.itemsite_id;
          ELSIF (_tmp < 0 AND _tmp != -3) THEN -- error but not 0-value transaction
            RETURN _tmp;
          ELSE
            -- Posting to trial balance is deferred to prevent locking
            INSERT INTO itemlocpost ( itemlocpost_glseq, itemlocpost_itemlocseries)
            VALUES ( _tmp, _itemlocSeries );
          END IF;
        END IF;
      END IF;

      SELECT insertGLTransaction(fetchJournalNumber('GL-MISC'),
				  'S/R', _r.recv_order_type, _o.orderhead_number,
				  'Receive Inventory Freight from ' || _o.orderhead_number || ' for item ' || _r.item_number,
				   costcat_liability_accnt_id,
				   getPrjAccntId(_o.prj_id, costcat_freight_accnt_id), -1,
				   _r.recv_freight_base,
				   _glDate::DATE, false ),
	     costcat_freight_accnt_id INTO _tmp, _freightAccnt
      FROM itemsite, costcat
      WHERE ( (itemsite_costcat_id=costcat_id)
       AND (itemsite_id=_r.itemsite_id) );
      IF (NOT FOUND) THEN
	RAISE EXCEPTION 'Could not insert G/L transaction: no cost category found for itemsite_id %',
	  _r.itemsite_id;
      ELSIF (_tmp < 0 AND _tmp != -3) THEN -- error but not 0-value transaction
	RETURN _tmp;
      ELSE
          -- Posting to trial balance is deferred to prevent locking
          INSERT INTO itemlocpost ( itemlocpost_glseq, itemlocpost_itemlocseries)
          VALUES ( _tmp, _itemlocSeries );
      END IF;

      UPDATE poitem
      SET poitem_qty_received = (poitem_qty_received + _r.recv_qty),
	  poitem_freight_received = (poitem_freight_received + _r.recv_freight_base)
      WHERE (poitem_id=_o.orderitem_id);

    ELSIF (_r.recv_order_type = 'RA') THEN
      SELECT rahead.*, raitem.* INTO _ra
	    FROM rahead, raitem
        WHERE ((rahead_id=raitem_rahead_id)
        AND  (raitem_id=_r.recv_orderitem_id));

      IF (_r.itemsite_controlmethod = 'N') THEN
        SELECT insertGLTransaction( fetchJournalNumber('GL-MISC'), 
                                    'S/R', _r.recv_order_type, _o.orderhead_number,
                                    'Receive Non-Controlled Inventory from ' || _ordertypeabbr,
                                    costcat_liability_accnt_id,
                                    getPrjAccntId(_o.prj_id, costcat_exp_accnt_id), -1,
                                    round((_o.item_unitprice_base * _r.recv_qty),2),
                                    _glDate::DATE, false ) INTO _tmp
        FROM itemsite JOIN costcat ON (costcat_id=itemsite_costcat_id)
        WHERE(itemsite_id=_r.itemsite_id);
        IF (NOT FOUND) THEN
          RAISE EXCEPTION 'Could not post inventory transaction: no cost category found for itemsite_id %', _r.itemsite_id;
--        ELSIF (_tmp < -1) THEN
--          RETURN _tmp;
        END IF;
      ELSE
        SELECT postInvTrans(_r.itemsite_id, 'RR',
                            _recvinvqty,
                            'S/R',
                            _r.recv_order_type, _ra.rahead_number::TEXT || '-' || _ra.raitem_linenumber::TEXT,
                            '',
                            'Receive Inventory from ' || _ordertypeabbr,
                            costcat_asset_accnt_id,
                            CASE WHEN(COALESCE(_ra.raitem_cos_accnt_id, -1) != -1) THEN 
                                  getPrjAccntId(_o.prj_id, _ra.raitem_cos_accnt_id)
                                 WHEN (_ra.raitem_warranty) THEN 
                                  getPrjAccntId(_o.prj_id, resolveCOWAccount(_r.itemsite_id, _ra.rahead_cust_id, _ra.rahead_saletype_id, _ra.rahead_shipzone_id))
                                 ELSE
                                  getPrjAccntId(_o.prj_id, resolveCORAccount(_r.itemsite_id, _ra.rahead_cust_id, _ra.rahead_saletype_id, _ra.rahead_shipzone_id))
                            END,
                            _itemlocSeries, _glDate, COALESCE(_o.unitcost,stdcost(itemsite_item_id)) * _recvinvqty) INTO _tmp
        FROM itemsite, costcat
        WHERE ( (itemsite_costcat_id=costcat_id)
         AND (itemsite_id=_r.itemsite_id) );

        IF (NOT FOUND) THEN
          RAISE EXCEPTION 'Could not post inventory transaction: no cost category found for itemsite_id %', _r.itemsite_id;
        ELSIF (_tmp < -1) THEN -- less than -1 because -1 means it is a none controlled item
          IF(_tmp = -3) THEN
            RAISE NOTICE 'The GL trans value was 0 which means we likely do not have a std cost';
            RETURN -12; -- The GL trans value was 0 which means we likely do not have a std cost
          END IF;
          RETURN _tmp;
        END IF;
      END IF;

      INSERT INTO rahist (rahist_itemsite_id, rahist_date,
			  rahist_descrip,
			  rahist_qty, rahist_uom_id,
			  rahist_source, rahist_source_id, rahist_rahead_id
	  ) VALUES (_r.itemsite_id, _glDate,
		      'Receive Inventory from ' || _ordertypeabbr,
		      _recvinvqty, _r.item_inv_uom_id,
		      'RR', _r.recv_id, _ra.rahead_id
	          );

      SELECT insertGLTransaction(fetchJournalNumber('GL-MISC'),
				  'S/R', _r.recv_order_type, _o.orderhead_number,
				  'Receive Inventory Freight from ' || _o.orderhead_number || ' for item ' || _r.item_number,
				   costcat_liability_accnt_id,
				   getPrjAccntId(_o.prj_id, costcat_freight_accnt_id), -1,
				   _r.recv_freight_base,
				   _glDate::DATE, false ),
	     costcat_freight_accnt_id INTO _tmp, _freightAccnt
      FROM itemsite, costcat
      WHERE ( (itemsite_costcat_id=costcat_id)
       AND (itemsite_id=_r.itemsite_id) );
      IF (_tmp < 0 AND _tmp != -3) THEN -- error but not 0-value transaction
	    RETURN _tmp;
      ELSE
        -- Posting to trial balance is deferred to prevent locking
        INSERT INTO itemlocpost ( itemlocpost_glseq, itemlocpost_itemlocseries)
        VALUES ( _tmp, _itemlocSeries );
      END IF;

      INSERT INTO rahist (rahist_date, rahist_descrip,
			  rahist_source, rahist_source_id,
			  rahist_curr_id, rahist_amount,
			  rahist_rahead_id
	  ) VALUES (_glDate, 'Receive Inventory Freight from ' || _ordertypeabbr,
		  'RR', _r.recv_id, _r.recv_freight_curr_id, _r.recv_freight,
		  _ra.rahead_id
	  );

      UPDATE raitem
      SET raitem_qtyreceived = (raitem_qtyreceived + _r.recv_qty)
      WHERE (raitem_id=_o.orderitem_id);
      
-- Expire date doesn't mean anything once the RA is received 
-- WARNING: INSERTING 'NULL' MIGHT CAUSE PROBLEMS!!
      UPDATE rahead
      SET rahead_expiredate = NULL
      WHERE (rahead_id=_o.orderhead_id);

--  Look for 'ship' lines
    SELECT (count(*) > 0) INTO _ship
    FROM raitem
    WHERE ((raitem_disposition = 'S')
     AND (raitem_new_coitem_id IS NULL)
     AND (raitem_rahead_id=_ra.rahead_id));

--  If receiving a qty on a shippable and upon receipt item, create coitem
      IF ((_ra.rahead_timing='R') AND
          (_ship OR (
          (_ra.raitem_disposition IN ('P','V')) AND
          (_ra.raitem_new_coitem_id IS NULL) AND
          (_ra.raitem_qtyauthorized > 0)))) THEN

          IF (_ra.rahead_new_cohead_id IS NOT NULL) THEN
            _coheadid = _ra.rahead_new_cohead_id;
          ELSE  
--  No header, so create a Sales Order header first.
            SELECT nextval('cohead_cohead_id_seq') INTO _coheadid;

            INSERT INTO cohead (
              cohead_id,cohead_number,cohead_cust_id,cohead_custponumber,
              cohead_orderdate,cohead_salesrep_id,cohead_terms_id,
              cohead_shipvia,cohead_shipto_id,cohead_shiptoname,
              cohead_shiptoaddress1,cohead_shiptoaddress2,cohead_shiptoaddress3,
              cohead_shiptocity,cohead_shiptostate,cohead_shiptozipcode,
              cohead_shiptocountry,cohead_freight,cohead_shiptophone,
              cohead_shipto_cntct_id, cohead_shipto_cntct_honorific,
              cohead_shipto_cntct_first_name, cohead_shipto_cntct_middle,
              cohead_shipto_cntct_last_name, cohead_shipto_cntct_suffix,
              cohead_shipto_cntct_phone, cohead_shipto_cntct_title,
              cohead_shipto_cntct_fax, cohead_shipto_cntct_email,
              cohead_shipchrg_id, cohead_shipform_id,cohead_billtoname,
              cohead_billtoaddress1,cohead_billtoaddress2,cohead_billtoaddress3,
              cohead_billtocity,cohead_billtostate,cohead_billtozipcode,
              cohead_billtocountry,cohead_misc_accnt_id,cohead_misc_descrip,
              cohead_commission,cohead_holdtype,cohead_prj_id,cohead_shipcomplete,
              cohead_curr_id,cohead_taxzone_id,cohead_saletype_id,cohead_shipzone_id)
            SELECT _coheadid,fetchsonumber(),rahead_cust_id,rahead_custponumber,
              current_date,rahead_salesrep_id,COALESCE(cohead_terms_id,cust_terms_id),
              COALESCE(cohead_shipvia,cust_shipvia),rahead_shipto_id,rahead_shipto_name,
              rahead_shipto_address1,rahead_shipto_address2,rahead_shipto_address3,
              rahead_shipto_city,rahead_shipto_state,rahead_shipto_zipcode,
              rahead_shipto_country,0,COALESCE(cohead_shiptophone,''),
              cntct_id, cntct_honorific,
              cntct_first_name, cntct_middle,
              cntct_last_name, cntct_suffix,
              cntct_phone, cntct_title,
              cntct_fax, cntct_email,
              COALESCE(cohead_shipchrg_id,cust_shipchrg_id),
              COALESCE(cohead_shipform_id,cust_shipform_id),
              rahead_billtoname,rahead_billtoaddress1,rahead_billtoaddress2,rahead_billtoaddress3,
              rahead_billtocity,rahead_billtostate,rahead_billtozip,
              rahead_billtocountry,NULL,'',rahead_commission, 'N', rahead_prj_id,
              COALESCE(cohead_shipcomplete,
                CASE WHEN cust_partialship THEN 
                  false 
                ELSE true
                END),rahead_curr_id,rahead_taxzone_id,rahead_saletype_id,rahead_shipzone_id
            FROM rahead
              JOIN custinfo ON (rahead_cust_id=cust_id)
              LEFT OUTER JOIN cohead ON (rahead_orig_cohead_id=cohead_id)
              LEFT OUTER JOIN shiptoinfo ON (rahead_shipto_id=shipto_id)
              LEFT OUTER JOIN cntct ON (shipto_cntct_id=cntct_id)
            WHERE (rahead_id=_ra.rahead_id);

            UPDATE rahead SET rahead_new_cohead_id=_coheadid WHERE rahead_id=_ra.rahead_id;
            
          END IF;
                  
-- Now enter the line item(s)
        IF (_ra.raitem_disposition IN ('P','V')) AND
           (_ra.raitem_new_coitem_id IS NULL) AND
           (_ra.raitem_qtyauthorized > 0) THEN
           
          SELECT nextval('coitem_coitem_id_seq') INTO _coitemid;

          SELECT COALESCE(MAX(coitem_linenumber),0)+1 INTO _linenumber
          FROM coitem
          WHERE (coitem_cohead_id=_coheadid);
      
          INSERT INTO coitem (
            coitem_id,coitem_cohead_id,coitem_linenumber,coitem_itemsite_id,
            coitem_status,coitem_scheddate,coitem_promdate, coitem_qtyord,
            coitem_unitcost,coitem_price,coitem_custprice,coitem_qtyshipped,
            coitem_order_id,coitem_memo,coitem_qtyreturned,
            coitem_taxtype_id,coitem_qty_uom_id,coitem_qty_invuomratio,
            coitem_price_uom_id,coitem_price_invuomratio,coitem_warranty,
            coitem_cos_accnt_id,coitem_order_type, coitem_custpn)
          SELECT _coitemid,_coheadid,_linenumber,_ra.raitem_coitem_itemsite_id,
              'O',_ra.raitem_scheddate,_ra.raitem_scheddate,_ra.raitem_qtyauthorized,
              stdcost(itemsite_item_id),COALESCE(_ra.raitem_saleprice,0),0,0,
              -1,_ra.raitem_notes,0,
              _ra.raitem_taxtype_id,_ra.raitem_qty_uom_id,_ra.raitem_qty_invuomratio,
              _ra.raitem_price_uom_id,_ra.raitem_price_invuomratio,_ra.raitem_warranty,
              _ra.raitem_cos_accnt_id,
              CASE WHEN itemsite_createwo THEN 'W' ELSE NULL END, _ra.raitem_custpn
          FROM itemsite
          WHERE (itemsite_id=_ra.raitem_coitem_itemsite_id);

          UPDATE raitem SET raitem_new_coitem_id=_coitemid WHERE (raitem_id=_ra.raitem_id);
        END IF;
        
        -- Create items to ship that have no direct relation to receipts.
        IF (_ship) THEN
          FOR _i IN
            SELECT raitem_id FROM raitem
            WHERE ((raitem_rahead_id=_ra.rahead_id)
              AND (raitem_disposition = 'S')
              AND (raitem_new_coitem_id IS NULL))
          LOOP

            SELECT nextval('coitem_coitem_id_seq') INTO _coitemid;

            SELECT COALESCE(MAX(coitem_linenumber),0)+1 INTO _linenumber
              FROM coitem
            WHERE (coitem_cohead_id=_coheadid);
      
            INSERT INTO coitem (
              coitem_id,coitem_cohead_id,coitem_linenumber,coitem_itemsite_id,
              coitem_status,coitem_scheddate,coitem_promdate, coitem_qtyord,
              coitem_unitcost,coitem_price,coitem_custprice,coitem_qtyshipped,
              coitem_order_id,coitem_memo,coitem_qtyreturned,
              coitem_taxtype_id,coitem_qty_uom_id,coitem_qty_invuomratio,
              coitem_price_uom_id,coitem_price_invuomratio,coitem_warranty,
              coitem_cos_accnt_id,coitem_order_type,coitem_custpn)
            SELECT _coitemid,_coheadid,_linenumber,raitem_coitem_itemsite_id,
              'O',raitem_scheddate,raitem_scheddate,raitem_qtyauthorized,
              stdcost(itemsite_item_id),COALESCE(raitem_saleprice,0),0,0,
              -1,raitem_notes,0,
              raitem_taxtype_id,raitem_qty_uom_id,raitem_qty_invuomratio,
              raitem_price_uom_id,raitem_price_invuomratio,raitem_warranty,
              raitem_cos_accnt_id,
              CASE WHEN itemsite_createwo THEN 'W' ELSE NULL END,raitem_custpn
            FROM raitem
              JOIN itemsite ON (itemsite_id=raitem_itemsite_id)
            WHERE (raitem_id=_i.raitem_id);
                        
            UPDATE raitem SET raitem_new_coitem_id=_coitemid WHERE (raitem_id=_i.raitem_id);

          END LOOP;
        END IF;
      END IF;


    ELSIF (_r.recv_order_type = 'TO' AND fetchMetricBool('MultiWhs')) THEN
      SELECT interWarehouseTransfer(toitem_item_id, tohead_trns_warehous_id,
            tohead_dest_warehous_id, _r.recv_qty, 
            'TO', formatToNumber(toitem_id), 'Receive from Transit To Dest Warehouse', _itemlocSeries, _glDate ) INTO _tmp
      FROM tohead, toitem
      WHERE ((tohead_id=toitem_tohead_id)
        AND  (toitem_id=_r.recv_orderitem_id));     

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

      SELECT insertGLTransaction(fetchJournalNumber('GL-MISC'), 
				  'S/R', _r.recv_order_type, _o.orderhead_number,
				  'Receive Inventory Freight from ' || _o.orderhead_number || ' for item ' || _r.item_number,
				   costcat_toliability_accnt_id,
				   costcat_freight_accnt_id, -1,
				   _r.recv_freight_base,
				   _glDate::DATE, false ),
	     costcat_freight_accnt_id INTO _tmp, _freightAccnt
      FROM itemsite, costcat
      WHERE ( (itemsite_costcat_id=costcat_id)
       AND (itemsite_id=_r.itemsite_id) );
      IF (_tmp < 0 AND _tmp != -3) THEN -- error but not 0-value transaction
	    RETURN _tmp;
      ELSE
        -- Posting to trial balance is deferred to prevent locking
        INSERT INTO itemlocpost ( itemlocpost_glseq, itemlocpost_itemlocseries)
        VALUES ( _tmp, _itemlocSeries );
      END IF;

      UPDATE toitem
      SET toitem_qty_received = (toitem_qty_received + _r.recv_qty),
	  toitem_freight_received = (toitem_freight_received +
				      currToCurr(_r.recv_freight_curr_id,
						 toitem_freight_curr_id,
						 _r.recv_freight, _glDate::DATE))
      WHERE (toitem_id=_o.orderitem_id);

    END IF;
    IF(_r.itemsite_costmethod='A') THEN
      _recvvalue := ROUND((_o.item_unitprice_base * _r.recv_qty),2);
    ELSIF (fetchMetricBool('RecordPPVonReceipt')) THEN
      _recvvalue := ROUND((_o.item_unitprice_base * _r.recv_qty), 2);
    ELSE
      _recvvalue := ROUND(stdcost(_r.itemsite_item_id) * _recvinvqty, 2);
    END IF;
  END IF;

  UPDATE recv
  SET recv_value=_recvvalue, recv_recvcost=_recvvalue / recv_qty, recv_posted=TRUE, recv_gldistdate=_glDate::DATE
  WHERE (recv_id=precvid);
  
  IF (_r.recv_order_type = 'PO') THEN
    -- If this is a drop-shipped PO, then Issue the item to Shipping and Ship the item
    IF (_o.pohead_dropship = TRUE) THEN

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

    END IF;
  END IF;
  RETURN _itemlocSeries;

END;
$_$;


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

--
-- TOC entry 2037 (class 1255 OID 146566868)
-- Dependencies: 4536 8
-- Name: postreceipts(text, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT SUM(qtyToReceive(pordertype, recv_orderitem_id)) INTO _qtyToRecv
  FROM recv, orderitem
  WHERE ((recv_orderitem_id=orderitem_id)
    AND  (recv_order_type=pordertype)
    AND  (orderitem_orderhead_type=pordertype)
    AND  (orderitem_orderhead_id=porderid));

  IF (_qtyToRecv <= 0) THEN
    RETURN -11;
  END IF;

  IF (_itemlocSeries IS NULL OR _itemlocSeries <= 0) THEN
    _itemlocSeries := NEXTVAL('itemloc_series_seq');
  END IF;

  FOR _r IN SELECT postReceipt(recv_id, _itemlocSeries) AS postResult
	    FROM recv, orderitem
	    WHERE ((recv_orderitem_id=orderitem_id)
	      AND  (orderitem_orderhead_id=porderid)
	      AND  (orderitem_orderhead_type=pordertype)
	      AND  (NOT recv_posted)
-- Check for multiple users receiving the same order
              AND  (recv_trans_usr_name=getEffectiveXtUser())
	      AND  (recv_order_type=pordertype)) LOOP
    IF (_r.postResult < 0 AND _r.postResult != -11) THEN
      RETURN _r.postResult; -- fail on 1st error but ignore lines with qty == 0
    END IF;
  END LOOP;

  RETURN _itemlocSeries;
END;
$_$;


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

--
-- TOC entry 2038 (class 1255 OID 146566869)
-- Dependencies: 4536 8
-- Name: postsogltransactions(); Type: FUNCTION; Schema: public; Owner: admin
--

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

  UPDATE gltrans
  SET gltrans_exported=TRUE
  WHERE ( (NOT gltrans_exported)
   AND (gltrans_source='A/R')
   AND (gltrans_doctype IN ('IN', 'CM')) );

  RETURN TRUE;

END;
$$;


ALTER FUNCTION public.postsogltransactions() OWNER TO admin;

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

CREATE FUNCTION postsoitemproduction(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
  pSoitemId      ALIAS FOR $1;
  pGlDistTS      ALIAS FOR $2;
  _qty NUMERIC;
  
BEGIN
  -- Issuing all, so determine line balance
  SELECT noNeg( coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned - 
              ( SELECT COALESCE(SUM(shipitem_qty), 0)
                FROM shipitem, shiphead
                WHERE ((shipitem_orderitem_id=coitem_id)
                  AND  (shipitem_shiphead_id=shiphead_id)
                  AND  (NOT shiphead_shipped) ) ) ) INTO _qty
  FROM coitem
  WHERE (coitem_id=pSoitemId);

  RETURN postSoItemProduction($1, _qty, $2);
END;
$_$;


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

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

CREATE FUNCTION postsoitemproduction(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
  pSoitemId ALIAS FOR $1;
  pQty ALIAS FOR $2;
  pGlDistTS ALIAS FOR $3;
  _itemlocSeries INTEGER := 0;
  
BEGIN
  --If this cost method is not Job then we are using the wrong function
  IF (NOT EXISTS(SELECT itemsite_costmethod
             FROM coitem,itemsite
             WHERE ((coitem_id=pSoitemId)
                AND (coitem_itemsite_id=itemsite_id)
                AND (itemsite_costmethod = 'J')))) THEN
    RAISE EXCEPTION 'The postSoLineBalanceProduction function may only be used with Job costed item sites';
  END IF;

  IF (pQty > 0) THEN
    SELECT COALESCE(postProduction(wo_id, (pQty * coitem_qty_invuomratio), true, 0, pGlDistTS),-1) INTO _itemlocSeries
    FROM wo, coitem
    WHERE ((wo_ordid=pSoItemid)
     AND (wo_ordtype='S')
     AND (coitem_id=pSoItemid));
    
    UPDATE wo SET wo_status = 'C'
    WHERE ((wo_ordid=pSoItemid)
     AND (wo_ordtype='S')
     AND (wo_qtyrcv >= wo_qtyord));
  END IF;

  RETURN _itemlocSeries;
END;
$_$;


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

--
-- TOC entry 2041 (class 1255 OID 146566872)
-- Dependencies: 4536 8
-- Name: poststandardjournal(integer, date); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT postStandardJournal(pStdjrnlid, pDate, FALSE, fetchGLSequence()) INTO _returnValue;

  RETURN _returnValue;

END;
$_$;


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

--
-- TOC entry 2042 (class 1255 OID 146566873)
-- Dependencies: 4536 8
-- Name: poststandardjournal(integer, date, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION poststandardjournal(integer, 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
  pStdjrnlid ALIAS FOR $1;
  pDate ALIAS FOR $2;
  pGlSequence ALIAS FOR $3;

BEGIN

  RETURN postStandardJournal(pStdjrnlid, pDate, FALSE, pGLSequence);

END;
$_$;


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

--
-- TOC entry 2043 (class 1255 OID 146566874)
-- Dependencies: 4536 8
-- Name: poststandardjournal(integer, date, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  RETURN postStandardJournal(pStdjrnlid, pDate, pReverse, fetchGLSequence());

END;
$_$;


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

--
-- TOC entry 2045 (class 1255 OID 146566875)
-- Dependencies: 4536 8
-- Name: poststandardjournal(integer, date, boolean, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION poststandardjournal(integer, date, 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
  pStdjrnlid ALIAS FOR $1;
  pDate ALIAS FOR $2;
  pReverse ALIAS FOR $3;
  pGlSequence ALIAS FOR $4;

BEGIN

  INSERT INTO glseries
  ( glseries_sequence, glseries_source, glseries_doctype, glseries_docnumber,
    glseries_notes, glseries_accnt_id, glseries_amount, glseries_distdate )
  SELECT pGlSequence, 'G/L', 'ST', stdjrnl_name,
         stdjrnlitem_notes, stdjrnlitem_accnt_id,
         CASE WHEN (pReverse=TRUE) THEN (stdjrnlitem_amount * -1)
              ELSE stdjrnlitem_amount
         END,
         pDate
  FROM stdjrnlitem, stdjrnl
  WHERE ( (stdjrnlitem_stdjrnl_id=stdjrnl_id)
   AND (stdjrnl_id=pStdjrnlid) );

  RETURN pGlSequence;

END;
$_$;


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

--
-- TOC entry 2046 (class 1255 OID 146566876)
-- Dependencies: 4536 8
-- Name: poststandardjournalgroup(integer, date); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 2047 (class 1255 OID 146566877)
-- Dependencies: 4536 8
-- Name: poststandardjournalgroup(integer, date, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION poststandardjournalgroup(integer, date, boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pStdjrnlgrpid ALIAS FOR $1;
  pDate ALIAS FOR $2;
  pReverse ALIAS FOR $3;
  _r RECORD;
  _glSequence INTEGER := -1;

BEGIN

  FOR _r IN SELECT stdjrnlgrpitem_id, stdjrnlgrpitem_stdjrnl_id
            FROM stdjrnlgrpitem
            WHERE ( (stdjrnlgrpitem_stdjrnlgrp_id=pStdjrnlgrpid)
             AND (CURRENT_DATE BETWEEN stdjrnlgrpitem_effective AND (stdjrnlgrpitem_expires - 1))
             AND ( (stdjrnlgrpitem_toapply = -1)
              OR (stdjrnlgrpitem_toapply > stdjrnlgrpitem_applied) ) ) LOOP

    IF (_glSequence = -1) THEN
      SELECT fetchGLSequence() INTO _glSequence;
    END IF;

    PERFORM postStandardJournal(_r.stdjrnlgrpitem_stdjrnl_id, pDate, pReverse, _glSequence);

    UPDATE stdjrnlgrpitem
    SET stdjrnlgrpitem_applied=(stdjrnlgrpitem_applied + 1)
    WHERE (stdjrnlgrpitem_id=_r.stdjrnlgrpitem_id);

  END LOOP;

  RETURN _glSequence;

END;
$_$;


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

--
-- TOC entry 2048 (class 1255 OID 146566878)
-- Dependencies: 4536 8
-- Name: postvalueintoinvbalance(integer, date, numeric, numeric, numeric, numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postvalueintoinvbalance(integer, date, numeric, numeric, numeric, numeric) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pItemsiteId ALIAS FOR $1;
  pDate ALIAS FOR $2;
  pQoh ALIAS FOR $3;
  pNn ALIAS FOR $4;
  pOldCost ALIAS FOR $5;
  pNewCost ALIAS FOR $6;
  _invbalid INTEGER;
  _r RECORD;
  _count INTEGER;
  _valChange NUMERIC;
  _nnvalChange NUMERIC;

BEGIN

--  Grab the costhist record to post
  SELECT period_id INTO _r
  FROM period
  WHERE (pDate BETWEEN period_start AND period_end);

  GET DIAGNOSTICS _count = ROW_COUNT;
  
--  Find an inventory balance to post into
  IF ( _count > 0 ) THEN
--  Try to find an existing invbal
    SELECT invbal_id INTO _invbalid
    FROM invbal
    WHERE ( (invbal_period_id=_r.period_id)
      AND (invbal_itemsite_id=pItemsiteId) );

    GET DIAGNOSTICS _count = ROW_COUNT;
    IF (_count = 0) THEN
      -- Wasn't there, so forward update
      PERFORM forwardUpdateItemsite(pItemsiteId);

      --  Try to find an existing invbal again
      SELECT invbal_id INTO _invbalid
      FROM invbal
      WHERE ( (invbal_period_id=_r.period_id)
        AND (invbal_itemsite_id=pItemsiteId) );

      GET DIAGNOSTICS _count = ROW_COUNT;
      IF (_count = 0) THEN
        RAISE EXCEPTION 'An inventory balance record was not found for updating standard costs';
      END IF;
    END IF;

    _valChange := round((pNewCost - pOldCost) * pQoh, 2);
    _nnvalChange := round((pNewCost - pOldCost) * pNn, 2);
    
--  We found an invbal, update it with the change
    IF (_valChange > 0) THEN
      UPDATE invbal SET 
        invbal_value_in = (invbal_value_in + _valChange)
      WHERE (invbal_id=_invbalid);
    ELSE
      UPDATE invbal SET 
        invbal_value_out = (invbal_value_out - _valChange)
      WHERE (invbal_id=_invbalid);
    END IF;

    IF (_nnvalChange > 0) THEN
      UPDATE invbal SET 
        invbal_nnval_in = (invbal_nnval_in + _nnvalChange)
      WHERE (invbal_id=_invbalid);
    ELSE
      UPDATE invbal SET 
        invbal_nnval_out = (invbal_nnval_out - _nnvalChange)
      WHERE (invbal_id=_invbalid);
    END IF;

    UPDATE invbal SET 
      invbal_value_ending = (invbal_value_beginning + invbal_value_in - invbal_value_out),
      invbal_nnval_ending = (invbal_nnval_beginning + invbal_nnval_in - invbal_nnval_out),
      invbal_dirty=true
    WHERE (invbal_id=_invbalid);  

  ELSE
    RAISE EXCEPTION 'No period exists for date %.', pDate;
  END IF;

  RETURN TRUE;

END;
$_$;


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

--
-- TOC entry 2049 (class 1255 OID 146566879)
-- Dependencies: 4536 8
