-- Function: fetchnextchecknumber(integer)

-- DROP FUNCTION fetchnextchecknumber(integer);

CREATE OR REPLACE FUNCTION fetchnextchecknumber(integer)
  RETURNS integer AS
$BODY$
-- 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
  pBankaccntid ALIAS FOR $1;
  _nextChkNumber INTEGER;
  v_existing INTEGER;

BEGIN

  SELECT
        (to_char(LEAST(accnt_number::integer,999),'FM000')  || to_char(bankaccnt_nextchknum, 'FM000000'))::integer 
        INTO
          _nextChkNumber
        FROM
            bankaccnt
        LEFT JOIN
            accnt
        ON
            accnt_id = bankaccnt_accnt_id
        WHERE
            (bankaccnt_id=pBankaccntid);


    SELECT
            checkhead_id
        INTO
            v_existing
        FROM
            checkhead
        WHERE 
            checkhead_number = _nextChkNumber;
            
    IF FOUND THEN
        RAISE EXCEPTION 'Check % already exists - please increase the check number', _nextChkNumber;
    END IF;


  UPDATE
          bankaccnt
        SET
            bankaccnt_nextchknum = (bankaccnt_nextchknum + 1)
        WHERE
            (bankaccnt_id=pBankaccntid);

  RETURN _nextChkNumber;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION fetchnextchecknumber(integer)
  OWNER TO admin;
