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

CREATE FUNCTION createrecurringinvoices() RETURNS integer
    LANGUAGE plpgsql
    AS $$
-- 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 'createRecurringInvoices() has been deprecated; use createRecurringItems(NULL, ''I'') instead.';

  RETURN createRecurringItems(NULL, 'I');
END;
$$;


ALTER FUNCTION public.createrecurringinvoices() OWNER TO admin;

--
-- TOC entry 1349 (class 1255 OID 146565905)
-- Dependencies: 4536 8
-- Name: createrecurringitems(integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createrecurringitems(integer, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pParentid  ALIAS FOR $1;      -- if NULL then all items with the given pType
  pType      TEXT := UPPER($2); -- if NULL then all types
                                -- if both are null then all items of all types
  _copystmt  TEXT;
  _count     INTEGER := 0;
  _countstmt TEXT;
  _existcnt  INTEGER;
  _id        INTEGER;
  _interval  TEXT;
  _last      TIMESTAMP WITH TIME ZONE;
  _loopcount INTEGER := 1;
  _maxstmt   TEXT;
  _maxdate   TIMESTAMP WITH TIME ZONE := endOfTime();
  _result    INTEGER := 0;
  _next      TIMESTAMP WITH TIME ZONE;
  _r         RECORD;
  _rt        RECORD;
  _tmp       INTEGER;

BEGIN
  RAISE DEBUG 'createRecurringItems(%, %) entered', pParentid, pType;

  FOR _r IN SELECT *
              FROM recur
             WHERE ((COALESCE(recur_end, endOfTime()) >= CURRENT_TIMESTAMP)
                AND (pParentid IS NULL OR recur_parent_id=pParentid)
                AND (pType IS NULL OR UPPER(recur_parent_type)=UPPER(pType))) LOOP

    RAISE DEBUG 'createRecurringItems looking at recur %, %',
                _r.recur_id, _r.recur_parent_type;
    _r.recur_max := COALESCE(_r.recur_max,
                             CAST(fetchMetricValue('RecurringInvoiceBuffer') AS INTEGER),
                             1);
    _interval := CASE _r.recur_period WHEN 'Y' THEN ' year'
                                      WHEN 'M' THEN ' month'
                                      WHEN 'W' THEN ' week'
                                      WHEN 'D' THEN ' day'
                                      WHEN 'H' THEN ' hour'
                                      WHEN 'm' THEN ' minute'
                                      ELSE NULL
                 END;

    IF (_interval IS NULL OR COALESCE(_r.recur_freq, 0) <= 0) THEN
      RAISE EXCEPTION 'Unknown recurrence frequency % % ON % %',
                      _r.recur_freq,        _r.recur_period,
                      _r.recur_parent_type, _r.recur_parent_id;
    END IF;

    SELECT * INTO _rt FROM recurtype WHERE (UPPER(recurtype_type)=UPPER(pType));
    GET DIAGNOSTICS _count = ROW_COUNT;
    IF (_count <= 0) THEN
      RETURN -10;
    END IF;

    -- if the recurrence type has a max lookahead window, use it
    IF (_r.recur_parent_type = 'I') THEN
      _maxdate := CURRENT_TIMESTAMP + CAST(fetchMetricText('RecurringInvoiceBuffer') || ' days' AS INTERVAL);
    END IF;
    IF (_r.recur_parent_type = 'V') THEN
      _maxdate := CURRENT_TIMESTAMP + CAST(fetchMetricText('RecurringVoucherBuffer') || ' days' AS INTERVAL);
    END IF;
    IF (_maxdate > _r.recur_end) THEN   -- if recur_end is null, _maxdate is ok
      _maxdate = _r.recur_end;
    END IF;

    -- build statements dynamically from the recurtype table because packages
    -- might also require recurring items. this way the algorithm is fixed
    -- and the details are data-driven
    _countstmt := 'SELECT COUNT(*) FROM [fulltable]' 
               || ' WHERE (($1=[table]_recurring_[table]_id)'
               || ' AND NOT([done]) '
               || ' AND ([limit]));';
    _countstmt := REPLACE(_countstmt, '[fulltable]', _rt.recurtype_table);
    _countstmt := REPLACE(_countstmt, '[table]',
                          REGEXP_REPLACE(_rt.recurtype_table, E'.*\\.', ''));
    _countstmt := REPLACE(_countstmt, '[done]',  _rt.recurtype_donecheck);
    _countstmt := REPLACE(_countstmt, '[limit]',
                          COALESCE(_rt.recurtype_limit, 'TRUE'));

    _maxstmt := 'SELECT MAX([schedcol]) FROM [fulltable]'
               || ' WHERE (($1=[table]_recurring_[table]_id)'
               || '    AND ([limit]));';
    _maxstmt := REPLACE(_maxstmt, '[schedcol]', _rt.recurtype_schedcol);
    _maxstmt := REPLACE(_maxstmt, '[fulltable]',_rt.recurtype_table);
    _maxstmt := REPLACE(_maxstmt, '[table]',
                          REGEXP_REPLACE(_rt.recurtype_table, E'.*\\.', ''));
    _maxstmt := REPLACE(_maxstmt, '[limit]', COALESCE(_rt.recurtype_limit,
                                                     'TRUE'));

    _copystmt := 'SELECT [copy]($1, [datetime] [more]);';
    _copystmt := REPLACE(_copystmt, '[copy]', _rt.recurtype_copyfunc);
    _copystmt := REPLACE(_copystmt, '[datetime]',
                         CASE WHEN UPPER(_rt.recurtype_copyargs[2])='DATE' THEN
                                    'CAST(''$2'' AS DATE)'
                              ELSE '''$2''' END);
    -- 8.4+:
    -- _copystmt := REPLACE(_copystmt, '[more]',
    --                      REPEAT(', NULL',
    --                             array_length(_rt.recurtype_copyargs) - 2));
    _tmp := CAST(REPLACE(REGEXP_REPLACE(array_dims(_rt.recurtype_copyargs),
                                        '.*:', ''), ']', '') AS INTEGER);
    _copystmt := REPLACE(_copystmt, '[more]', REPEAT(', NULL', _tmp - 2));

    EXECUTE REPLACE(_countstmt, '$1', _r.recur_parent_id::TEXT) INTO _existcnt;
    EXECUTE REPLACE(_maxstmt,   '$1', _r.recur_parent_id::TEXT) INTO _last;
    RAISE DEBUG E'% got %, % got %', _countstmt, _existcnt, _maxstmt, _last;

    _next := _last;
    _loopcount := 1;
    WHILE (_existcnt < _r.recur_max AND _next < _maxdate) LOOP
      _next := _last +
               CAST(_r.recur_freq * _loopcount || _interval AS INTERVAL);
      RAISE DEBUG 'createrecurringitems looping, existcnt = %, max = %, is % between % and %?',
                  _existcnt, _r.recur_max, _next, _r.recur_start, _r.recur_end;

      IF (_next BETWEEN _r.recur_start AND _maxdate) THEN
        RAISE DEBUG 'createrecurringitems executing % with % and %',
                    _copystmt, _r.recur_parent_id, _next;
        -- 8.4+: EXECUTE _copystmt INTO _id USING _r.recur_parent_id, _next;
        EXECUTE REPLACE(REPLACE(_copystmt, '$1', _r.recur_parent_id::TEXT),
                                           '$2', _next::TEXT) INTO _id;
        RAISE DEBUG 'Copying for % returned %', _next, _id;
        _result   := _result   + 1;
        _existcnt := _existcnt + 1;
      END IF;
      _loopcount := _loopcount + 1;
    END LOOP;
  END LOOP;

  RETURN _result;
END;
$_$;


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

--
-- TOC entry 1350 (class 1255 OID 146565907)
-- Dependencies: 4536 8
-- Name: createtodoitem(integer, text, text, text, integer, integer, integer, date, date, character, date, date, integer, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createtodoitem(integer, text, text, text, integer, integer, integer, date, date, character, date, date, 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
  RETURN createTodoItem($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, NULL);
END;
$_$;


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

--
-- TOC entry 1352 (class 1255 OID 146565908)
-- Dependencies: 4536 8
-- Name: createtodoitem(integer, text, text, text, integer, integer, integer, date, date, character, date, date, integer, text, text, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createtodoitem(integer, text, text, text, integer, integer, integer, date, date, character, date, date, integer, text, 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
  ptodoid     ALIAS FOR  $1;
  pusername   ALIAS FOR  $2;
  pname       ALIAS FOR  $3;
  pdesc       ALIAS FOR  $4;
  pincdtid    ALIAS FOR  $5;
  pcrmacctid  ALIAS FOR  $6;
  pOpheadid   ALIAS FOR  $7;
  pstarted    ALIAS FOR  $8;
  pdue        ALIAS FOR  $9;
  pstatus     ALIAS FOR $10;
  passigned   ALIAS FOR $11;
  pcompleted  ALIAS FOR $12;
  ppriority   ALIAS FOR $13;
  pnotes      ALIAS FOR $14;
  powner      ALIAS FOR $15;
  pcntctid    ALIAS FOR $16;

  _todoid     INTEGER;
  _priority   INTEGER         := ppriority;
  _status     CHARACTER(1)    := pstatus;
  _incdtid    INTEGER         := pincdtid;
  _crmacctid  INTEGER         := pcrmacctid;
  _opheadid   INTEGER         := pOpheadid;
  _assigned   DATE            := passigned;
  _result     INTEGER;

BEGIN
  IF (pusername IS NULL OR pusername = '') THEN
    RETURN -1;
  END IF;

  IF (pname IS NULL OR pname = '') THEN
    RETURN -2;
  END IF;

  IF (pdue IS NULL) THEN
    RETURN -3;
  END IF;

  IF (pcompleted IS NOT NULL) THEN
    _status := 'C';
  ELSIF (pstatus IS NULL AND pstarted IS NOT NULL) THEN
    _status := 'I';
  ELSIF (pstatus IS NULL) THEN
    _status := 'N';
  END IF;

  IF (_incdtid <= 0) THEN
    _incdtid := NULL;
  END IF;

  IF (_crmacctid <= 0) THEN
    _crmacctid := NULL;
  END IF;

  IF (_opheadid <= 0) THEN
    _opheadid := NULL;
  END IF;

  IF (_priority <= 0) THEN
    _priority := NULL;
  END IF;

  IF (_assigned IS NULL) THEN
    _assigned := CURRENT_DATE;
  END IF;

  IF (ptodoid IS NULL) THEN
    SELECT NEXTVAL('todoitem_todoitem_id_seq') INTO _todoid;
  ELSE
    _todoid := ptodoid;
  END IF;

  INSERT INTO todoitem ( todoitem_id, todoitem_username, todoitem_name,
                         todoitem_description, todoitem_incdt_id,
                         todoitem_creator_username, todoitem_status,
                         todoitem_active, todoitem_start_date,
                         todoitem_due_date, todoitem_assigned_date,
                         todoitem_completed_date, todoitem_priority_id,
                         todoitem_notes, todoitem_crmacct_id,
                         todoitem_ophead_id, todoitem_owner_username,
                         todoitem_cntct_id
              ) VALUES ( _todoid, pusername, pname,
                         pdesc, _incdtid,
                         getEffectiveXtUser(), _status,
                         TRUE, pstarted,
                         pdue, _assigned,
                         pcompleted, _priority, pnotes, _crmacctid, _opheadid, powner,
                         pcntctid );

  RETURN _todoid;
END;
$_$;


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

--
-- TOC entry 1353 (class 1255 OID 146565909)
-- Dependencies: 4536 8
-- Name: createurl(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createurl(text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
declare
  pTitle ALIAS FOR $1;
  pUrl ALIAS FOR $2;
  _id integer;
begin
  _id := nextval('urlinfo_url_id_seq');
  insert into urlinfo (url_id, url_title, url_url) values (_id, pTitle, pUrl);
  return _id;
end;
$_$;


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

--
-- TOC entry 1354 (class 1255 OID 146565910)
-- Dependencies: 4536 8
-- Name: createuser(text, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createuser(pusername text, pcreateusers boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  IF (pCreateUsers) THEN
    EXECUTE 'CREATE USER "' || pUsername || '" CREATEROLE   IN GROUP xtrole;';
  ELSE
    EXECUTE 'CREATE USER "' || pUsername || '" NOCREATEROLE IN GROUP xtrole;';
  END IF;
  RETURN 1;
END;
$$;


ALTER FUNCTION public.createuser(pusername text, pcreateusers boolean) OWNER TO admin;

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

CREATE FUNCTION createwo(integer, integer, numeric, integer, date, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pWoNumber ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pQtyOrdered ALIAS FOR $3;
  pLeadTime ALIAS FOR $4;
  pDueDate ALIAS FOR $5;
  pProductionNotes ALIAS FOR $6;

BEGIN
  RETURN createWo( pWoNumber, pItemsiteid, 1, pQtyOrdered,
                   (pDueDate - pLeadTime), pDueDate,
                   pProductionNotes, NULL, NULL, -1 ); 
END;
$_$;


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

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

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

BEGIN
  RETURN createWo( pWoNumber, pItemsiteid, 1, pQtyOrdered,
                   pStartDate, pDueDate, pProductionNotes, NULL, NULL, -1);
END;
$_$;


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

--
-- TOC entry 1355 (class 1255 OID 146565911)
-- Dependencies: 4536 8
-- Name: createwo(integer, integer, numeric, integer, date, text, character, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createwo(integer, integer, numeric, integer, date, text, character, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pWoNumber ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pQtyOrdered ALIAS FOR $3;
  pLeadTime ALIAS FOR $4;
  pDueDate ALIAS FOR $5;
  pProductionNotes ALIAS FOR $6;
  pParentType ALIAS FOR $7;
  pParentId ALIAS FOR $8;

BEGIN
  RETURN createWo( pWoNumber, pItemsiteid, 1, pQtyOrdered,
                   (pDueDate - pLeadTime), pDueDate,
                   pProductionNotes, pParentType, pParentId, -1 ); 
END;
$_$;


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

--
-- TOC entry 1358 (class 1255 OID 146565914)
-- Dependencies: 4536 8
-- Name: createwo(integer, integer, integer, numeric, integer, date, text, character, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createwo(integer, integer, integer, numeric, integer, date, text, character, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pWoNumber ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pPriority ALIAS FOR $3;
  pQtyOrdered ALIAS FOR $4;
  pLeadTime ALIAS FOR $5;
  pDueDate ALIAS FOR $6;
  pProductionNotes ALIAS FOR $7;
  pParentType ALIAS FOR $8;
  pParentId ALIAS FOR $9;

BEGIN
  RETURN createWo(pWoNumber, pItemsiteid, pPriority, pQtyOrdered,
                  (pDueDate - pLeadTime), pDueDate, pProductionNotes,
                  pParentType, pParentId, -1);
END;
$_$;


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

--
-- TOC entry 1359 (class 1255 OID 146565915)
-- Dependencies: 4536 8
-- Name: createwo(integer, integer, integer, numeric, integer, date, text, character, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createwo(integer, integer, integer, numeric, integer, date, text, character, 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
  pWoNumber ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pPriority ALIAS FOR $3;
  pQtyOrdered ALIAS FOR $4;
  pLeadTime ALIAS FOR $5;
  pDueDate ALIAS FOR $6;
  pProductionNotes ALIAS FOR $7;
  pParentType ALIAS FOR $8;
  pParentId ALIAS FOR $9;
  pProjectId ALIAS FOR $10;

BEGIN
  RETURN createWo(pWoNumber, pItemsiteid, pPriority, pQtyOrdered,
                  (pDueDate - pLeadTime), pDueDate, pProductionNotes,
                  pParentType, pParentId, pProjectId);
END;
$_$;


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

--
-- TOC entry 1360 (class 1255 OID 146565916)
-- Dependencies: 4536 8
-- Name: createwo(integer, integer, integer, numeric, date, date, text, character, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createwo(integer, integer, integer, numeric, date, date, text, character, 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
  pWoNumber ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pPriority ALIAS FOR $3;
  pQtyOrdered ALIAS FOR $4;
  pStartDate ALIAS FOR $5;
  pDueDate ALIAS FOR $6;
  pProductionNotes ALIAS FOR $7;
  pParentType ALIAS FOR $8;
  pParentId ALIAS FOR $9;
  pProjectId ALIAS FOR $10;
  _woid INTEGER;
  _result INTEGER;
  _parentType char(1);
  _bomrevid INTEGER;
  _boorevid INTEGER;

BEGIN

  SELECT getActiveRevId('BOM',itemsite_item_id) INTO _bomrevid
  FROM itemsite
  WHERE (itemsite_id=pItemsiteid);

  SELECT getActiveRevId('BOO',itemsite_item_id) INTO _boorevid
  FROM itemsite
  WHERE (itemsite_id=pItemsiteid);
  
  RETURN createWo(pWoNumber, pItemsiteid, pPriority, pQtyOrdered,
                  pStartDate, pDueDate, pProductionNotes,
                  pParentType, pParentId, pProjectId, _bomrevid, _boorevid, NULL);

END;
$_$;


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

--
-- TOC entry 1361 (class 1255 OID 146565917)
-- Dependencies: 4536 8
-- Name: createwo(integer, integer, integer, numeric, integer, date, text, character, integer, integer, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createwo(integer, integer, integer, numeric, integer, date, text, character, integer, 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
  pWoNumber ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pPriority ALIAS FOR $3;
  pQtyOrdered ALIAS FOR $4;
  pLeadTime ALIAS FOR $5;
  pDueDate ALIAS FOR $6;
  pProductionNotes ALIAS FOR $7;
  pParentType ALIAS FOR $8;
  pParentId ALIAS FOR $9;
  pProjectId ALIAS FOR $10;
  pBomRevId ALIAS FOR $11;
  pBooRevId ALIAS FOR $12;

BEGIN
  RETURN createWo(pWoNumber, pItemsiteid, pPriority, pQtyOrdered,
                  (pDueDate - pLeadTime), pDueDate, pProductionNotes,
                  pParentType, pParentId, pProjectId, pBomRevId, pBooRevId, NULL);
END;
$_$;


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

--
-- TOC entry 1362 (class 1255 OID 146565918)
-- Dependencies: 4536 8
-- Name: createwo(integer, integer, integer, numeric, date, date, text, character, integer, integer, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createwo(integer, integer, integer, numeric, date, date, text, character, integer, 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
  pWoNumber ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pPriority ALIAS FOR $3;
  pQtyOrdered ALIAS FOR $4;
  pStartDate ALIAS FOR $5;
  pDueDate ALIAS FOR $6;
  pProductionNotes ALIAS FOR $7;
  pParentType ALIAS FOR $8;
  pParentId ALIAS FOR $9;
  pProjectId ALIAS FOR $10;
  pBomRevId ALIAS FOR $11;
  pBooRevId ALIAS FOR $12;
BEGIN
  RETURN createWo(pWoNumber, pItemsiteid, pPriority, pQtyOrdered,
                  pStartDate, pDueDate, pProductionNotes,
                  pParentType, pParentId, pProjectId, pBomRevId, pBooRevId, NULL);
END;
$_$;


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

--
-- TOC entry 1363 (class 1255 OID 146565919)
-- Dependencies: 4536 8
-- Name: createwo(integer, integer, integer, numeric, date, date, text, character, integer, integer, integer, integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createwo(integer, integer, integer, numeric, date, date, text, character, integer, integer, integer, integer, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pWoNumber ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pPriority ALIAS FOR $3;
  pQtyOrdered ALIAS FOR $4;
  pStartDate ALIAS FOR $5;
  pDueDate ALIAS FOR $6;
  pProductionNotes ALIAS FOR $7;
  pParentType ALIAS FOR $8;
  pParentId ALIAS FOR $9;
  pProjectId ALIAS FOR $10;
  pBomRevId ALIAS FOR $11;
  pBooRevId ALIAS FOR $12;
  pCosMethod ALIAS FOR $13;
  _startDate DATE;
  _woid INTEGER;
  _result INTEGER;
  _parentType char(1);
  _parentId INTEGER;
  _cosmethod char(1);
  _itemsite RECORD;
  
BEGIN
  
  _parentType := COALESCE(pParentType, ' ');
  _parentId   := COALESCE(pParentId, -1);

  SELECT * INTO _itemsite FROM itemsite WHERE itemsite_id = pItemsiteid;

--  Check to make sure the itemsite specified is supplied at itemsite
  IF (NOT _itemsite.itemsite_wosupply) THEN
    RETURN -1;
  END IF;

--  Check to make sure if this is a job item that it is tied to a sales order
--  Or if it is just an avarage costed item
  IF (pCosMethod IN ('D', 'P')) THEN
    _cosmethod := pCosMethod;
  ELSE
    IF (_itemsite.itemsite_costmethod = 'J') THEN
      IF (_parentType = ' ' OR _parentId = -1) THEN
        RAISE EXCEPTION 'Work Orders for Item Sites that are Job cost must have a parent order.';
      ELSE
        SELECT COALESCE(itemsite_cosdefault,fetchmetrictext('JobItemCosDefault'),'D') INTO _cosmethod FROM itemsite WHERE itemsite_id=pItemsiteid;
      END IF;
    ELSIF (_itemsite.itemsite_costmethod = 'A') THEN
      _cosmethod := COALESCE(_itemsite.itemsite_cosdefault,fetchmetrictext('JobItemCosDefault'),'D');
    END IF;
  END IF;

--  Check to see if the site calendar metric is set, and if so adjust the start date if necessary
  IF (fetchmetricbool('UseSiteCalendar')) THEN
    _startDate := calculatenextworkingdate(_itemsite.itemsite_warehous_id, pStartDate, 0);
    IF (_startDate != pStartDate) THEN
      _startDate := calculatenextworkingdate(_itemsite.itemsite_warehous_id, pDueDate, -_itemsite.itemsite_leadtime);
    END IF;
  ELSE
    _startDate := pStartDate;
  END IF;
  
--  Grab the next wo_id
  SELECT NEXTVAL('wo_wo_id_seq') INTO _woid;

--  Create the W/O
  INSERT INTO wo
  ( wo_id, wo_number, wo_subnumber, wo_itemsite_id,
    wo_priority, wo_ordtype, wo_ordid,
    wo_status, wo_startdate, wo_duedate,
    wo_qtyord, wo_qtyrcv, wo_prodnotes, wo_prj_id,
    wo_bom_rev_id, wo_boo_rev_id, wo_cosmethod )
  SELECT _woid, pWoNumber, nextWoSubnumber(pWoNumber), itemsite_id,
         pPriority, _parentType, pParentId,
         'O', _startDate, pDueDate,
         roundQty(item_fractional, pQtyOrdered), 0, pProductionNotes, pProjectId, 
         pBomRevid, pBooRevid, _cosmethod
  FROM itemsite, item
  WHERE ((itemsite_item_id=item_id)
   AND (itemsite_id=pItemsiteid));

--  Explode the newly created W/O according to metrics
  IF ( ( SELECT (metric_value='t')
         FROM metric
         WHERE (metric_name='AutoExplodeWO') ) ) THEN
    SELECT explodeWo( _woid, ( SELECT (metric_value = 'M')
                               FROM metric
                               WHERE (metric_name='WOExplosionLevel') ) ) INTO _result;
  ELSE
    _result := _woid;
  END IF;

  RETURN _result;

END;
$_$;


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

--
-- TOC entry 1364 (class 1255 OID 146565920)
-- Dependencies: 4536 8
-- Name: createwomaterial(integer, integer, character, numeric, numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createwomaterial(integer, integer, character, numeric, numeric) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
  pWoid ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pIssueMethod ALIAS FOR $3;
  pQtyPer ALIAS FOR $4;
  pScrap ALIAS FOR $5;
  _result INTEGER;
BEGIN
  SELECT createWoMaterial(pWoid, pItemsiteid, pIssueMethod, item_inv_uom_id, pQtyPer, pScrap)
    INTO _result
    FROM itemsite JOIN item ON (itemsite_item_id=item_id)
   WHERE(itemsite_id=pItemsiteid);
  RETURN _result;
END;
$_$;


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

--
-- TOC entry 1365 (class 1255 OID 146565921)
-- Dependencies: 4536 8
-- Name: createwomaterial(integer, integer, character, integer, numeric, numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createwomaterial(integer, integer, character, integer, numeric, numeric) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
  pWoid ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pIssueMethod ALIAS FOR $3;
  pUomId ALIAS FOR $4;
  pQtyPer ALIAS FOR $5;
  pScrap ALIAS FOR $6;
  _womatlid INTEGER;

BEGIN

  SELECT createWoMaterial(pWoid,pItemsiteid,pIssueMethod,pUomId,pQtyPer,pScrap,-1, NULL, NULL) INTO _womatlid;

  RETURN _womatlid;
END;
$_$;


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

--
-- TOC entry 1368 (class 1255 OID 146565924)
-- Dependencies: 4536 8
-- Name: createwomaterial(integer, integer, character, numeric, numeric, numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createwomaterial(integer, integer, character, numeric, numeric, numeric) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pWoid ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pIssueMethod ALIAS FOR $3;
  pQtyFxd ALIAS FOR $4;
  pQtyPer ALIAS FOR $5;
  pScrap ALIAS FOR $6;
  _result INTEGER;
BEGIN
  SELECT createWoMaterial(pWoid, pItemsiteid, pIssueMethod, item_inv_uom_id, pQtyFxd, pQtyPer, pScrap)
    INTO _result
    FROM itemsite JOIN item ON (itemsite_item_id=item_id)
   WHERE(itemsite_id=pItemsiteid);
  RETURN _result;
END;
$_$;


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

--
-- TOC entry 1366 (class 1255 OID 146565922)
-- Dependencies: 4536 8
-- Name: createwomaterial(integer, integer, character, integer, numeric, numeric, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createwomaterial(integer, integer, character, integer, numeric, numeric, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
  pWoid ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pIssueMethod ALIAS FOR $3;
  pUomId ALIAS FOR $4;
  pQtyPer ALIAS FOR $5;
  pScrap ALIAS FOR $6;
  pBomitemId ALIAS FOR $7;
  _womatlid INTEGER;

BEGIN

  _womatlid := (SELECT NEXTVAL('womatl_womatl_id_seq'));

  INSERT INTO womatl
  ( womatl_id, womatl_wo_id, womatl_itemsite_id,
    womatl_issuemethod, womatl_uom_id, womatl_qtyper, womatl_scrap,
    womatl_qtyreq, womatl_qtyiss, womatl_qtywipscrap,
    womatl_wooper_id, womatl_bomitem_id, womatl_duedate )
  SELECT _womatlid, wo_id, pItemsiteid,
         pIssueMethod, pUomId, pQtyPer, pScrap,
         roundQty(item_fractional, (wo_qtyord * (pQtyPer * (1 + pScrap)))), 0, 0,
         -1, pBomitemId, wo_startdate
  FROM wo, itemsite, item
  WHERE ( (itemsite_item_id=item_id)
   AND (wo_id=pWoid)
   AND (itemsite_id=pItemsiteid) );

  UPDATE wo
  SET wo_adhoc=TRUE
  WHERE (wo_id=pWoid);

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

  RETURN _womatlid;
END;
$_$;


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

--
-- TOC entry 1369 (class 1255 OID 146565925)
-- Dependencies: 4536 8
-- Name: createwomaterial(integer, integer, character, integer, numeric, numeric, numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createwomaterial(integer, integer, character, integer, numeric, numeric, numeric) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pWoid ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pIssueMethod ALIAS FOR $3;
  pUomId ALIAS FOR $4;
  pQtyFxd ALIAS FOR $5;
  pQtyPer ALIAS FOR $6;
  pScrap ALIAS FOR $7;
  _womatlid INTEGER;

BEGIN

  SELECT createWoMaterial(pWoid,pItemsiteid,pIssueMethod,pUomId,pQtyFxd,pQtyPer,pScrap,-1, NULL, NULL) INTO _womatlid;

  RETURN _womatlid;
END;
$_$;


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

--
-- TOC entry 1367 (class 1255 OID 146565923)
-- Dependencies: 4536 8
-- Name: createwomaterial(integer, integer, character, integer, numeric, numeric, integer, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createwomaterial(integer, integer, character, integer, numeric, numeric, integer, text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
  pWoid ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pIssueMethod ALIAS FOR $3;
  pUomId ALIAS FOR $4;
  pQtyPer ALIAS FOR $5;
  pScrap ALIAS FOR $6;
  pBomitemId ALIAS FOR $7;
  pNotes ALIAS FOR $8;
  pRef ALIAS FOR $9;
  _womatlid INTEGER;
  _p RECORD;

BEGIN

  _womatlid := (SELECT NEXTVAL('womatl_womatl_id_seq'));

  INSERT INTO womatl
  ( womatl_id, womatl_wo_id, womatl_itemsite_id,
    womatl_issuemethod, womatl_uom_id, womatl_qtyper, womatl_scrap,
    womatl_qtyreq, womatl_qtyiss, womatl_qtywipscrap,
    womatl_wooper_id, womatl_bomitem_id, womatl_duedate, womatl_notes, womatl_ref )
  SELECT _womatlid, wo_id, pItemsiteid,
         pIssueMethod, pUomId, pQtyPer, pScrap,
         roundQty(item_fractional, (wo_qtyord * (pQtyPer * (1 + pScrap)))), 0, 0,
         -1, pBomitemId, wo_startdate, pNotes, pRef 
  FROM wo, itemsite, item
  WHERE ( (itemsite_item_id=item_id)
   AND (wo_id=pWoid)
   AND (itemsite_id=pItemsiteid) );

-- Handle all of the Phantom material requirements
  WHILE ( ( SELECT COUNT(*)
            FROM womatl, itemsite, item
            WHERE ( (womatl_itemsite_id=itemsite_id)
             AND (itemsite_item_id=item_id)
             AND (womatl_wo_id=pWoid)
             AND (item_type='F') ) ) > 0 ) LOOP

    FOR _p IN SELECT wo_qtyord, wo_startdate, womatl_id, womatl_wooper_id
              FROM wo, womatl, itemsite, item
              WHERE ( (womatl_itemsite_id=itemsite_id)
               AND (itemsite_item_id=item_id)
               AND (item_type='F')
               AND (womatl_wo_id=wo_id)
               AND (wo_id=pWoid) ) LOOP

      INSERT INTO womatl
      ( womatl_wo_id, womatl_itemsite_id, womatl_wooper_id,
        womatl_schedatwooper, womatl_duedate,
        womatl_uom_id, womatl_qtyper, womatl_scrap,
        womatl_qtyreq,
        womatl_qtyiss, womatl_qtywipscrap,
        womatl_lastissue, womatl_lastreturn,
        womatl_cost, womatl_picklist, womatl_createwo,
        womatl_issuemethod, womatl_notes, womatl_ref )
      SELECT pWoid, cs.itemsite_id, _p.womatl_wooper_id,
             womatl_schedatwooper, womatl_duedate,
             bomitem_uom_id, (bomitem_qtyper * womatl_qtyper), bomitem_scrap,
             roundQty(itemuomfractionalbyuom(bomitem_item_id, bomitem_uom_id), (_p.wo_qtyord * bomitem_qtyper * womatl_qtyper * (1 + bomitem_scrap))),
             0, 0,
             startOfTime(), startOfTime(),
             0, ci.item_picklist, ( (ci.item_type='M') AND (bomitem_createwo) ),
             bomitem_issuemethod, bomitem_notes, bomitem_ref 
      FROM wo, womatl, bomitem, 
           itemsite AS cs, itemsite AS ps,
           item AS ci, item AS pi
      WHERE ( (womatl_itemsite_id=ps.itemsite_id)
       AND (womatl_wo_id=wo_id)
       AND (bomitem_parent_item_id=pi.item_id)
       AND (bomitem_item_id=ci.item_id)
       AND (ps.itemsite_warehous_id=cs.itemsite_warehous_id)
       AND (cs.itemsite_item_id=ci.item_id)
       AND (ps.itemsite_item_id=pi.item_id)
       AND (woEffectiveDate(_p.wo_startdate) BETWEEN bomitem_effective AND (bomitem_expires - 1))
       AND (womatl_id=_p.womatl_id));

      DELETE FROM womatl
      WHERE (womatl_id=_p.womatl_id);

    END LOOP;
  END LOOP;

  UPDATE wo
  SET wo_adhoc=TRUE
  WHERE (wo_id=pWoid);

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

  RETURN _womatlid;
END;
$_$;


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

--
-- TOC entry 1370 (class 1255 OID 146565926)
-- Dependencies: 4536 8
-- Name: createwomaterial(integer, integer, character, integer, numeric, numeric, numeric, integer, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createwomaterial(integer, integer, character, integer, numeric, numeric, numeric, 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.
DECLARE
  pWoid ALIAS FOR $1;
  pItemsiteid ALIAS FOR $2;
  pIssueMethod ALIAS FOR $3;
  pUomId ALIAS FOR $4;
  pQtyFxd ALIAS FOR $5;
  pQtyPer ALIAS FOR $6;
  pScrap ALIAS FOR $7;
  pBomitemId ALIAS FOR $8;
  pNotes ALIAS FOR $9;
  pRef ALIAS FOR $10;
  _womatlid INTEGER;

BEGIN

  SELECT createWoMaterial(pWoid,pItemsiteid,pIssueMethod,pUomId,pQtyFxd,pQtyPer,pScrap,pBomitemId,pNotes,pRef,NULL,NULL) INTO _womatlid;

  RETURN _womatlid;
END;
$_$;


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

--
-- TOC entry 1371 (class 1255 OID 146565927)
-- Dependencies: 4536 8
-- Name: createwomaterial(integer, integer, character, integer, numeric, numeric, numeric, integer, text, text, integer, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createwomaterial(pwoid integer, pitemsiteid integer, pissuemethod character, puomid integer, pqtyfxd numeric, pqtyper numeric, pscrap numeric, pbomitemid integer, pnotes text, pref text, pwooperid integer, ppicklist 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
  _womatlid INTEGER;

BEGIN

  SELECT createWoMaterial(pWoid,
                          pItemsiteid,
                          pIssueMethod,
                          pUomId,
                          pQtyFxd,
                          pQtyPer,
                          pScrap,
                          pBomitemId,
                          pNotes,
                          pRef,
                          pWooperId,
                          pPickList,
                          0.0) INTO _womatlid;

  RETURN _womatlid;
END;
$$;


ALTER FUNCTION public.createwomaterial(pwoid integer, pitemsiteid integer, pissuemethod character, puomid integer, pqtyfxd numeric, pqtyper numeric, pscrap numeric, pbomitemid integer, pnotes text, pref text, pwooperid integer, ppicklist boolean) OWNER TO admin;

--
-- TOC entry 1372 (class 1255 OID 146565928)
-- Dependencies: 4536 8
-- Name: createwomaterial(integer, integer, character, integer, numeric, numeric, numeric, integer, text, text, integer, boolean, numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION createwomaterial(pwoid integer, pitemsiteid integer, pissuemethod character, puomid integer, pqtyfxd numeric, pqtyper numeric, pscrap numeric, pbomitemid integer, pnotes text, pref text, pwooperid integer, ppicklist boolean, pprice 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
  _womatlid INTEGER;
  _p RECORD;

BEGIN

  _womatlid := (SELECT NEXTVAL('womatl_womatl_id_seq'));

  INSERT INTO womatl
  ( womatl_id, womatl_wo_id, womatl_itemsite_id,
    womatl_issuemethod, womatl_uom_id, womatl_qtyfxd,
    womatl_qtyper, womatl_scrap, womatl_qtyreq,
    womatl_qtyiss, womatl_qtywipscrap, womatl_wooper_id,
    womatl_bomitem_id, womatl_duedate, womatl_notes,
    womatl_ref, womatl_picklist, womatl_price )
  SELECT _womatlid, wo_id, pItemsiteid,
         pIssueMethod, pUomId, pQtyFxd,
         pQtyPer, pScrap, roundQty(item_fractional, (pQtyFxd + wo_qtyord * pQtyPer) * (1 + pScrap) ),
         0, 0, COALESCE(pWooperId, -1),
         pBomitemId, wo_startdate, pNotes,
         pRef, COALESCE(pPickList, item_picklist), pPrice 
  FROM wo, itemsite JOIN item ON (item_id=itemsite_item_id)
  WHERE ( (wo_id=pWoid)
   AND (itemsite_id=pItemsiteid) );

-- Handle all of the Phantom material requirements
  WHILE ( ( SELECT COUNT(*)
            FROM womatl, itemsite, item
            WHERE ( (womatl_itemsite_id=itemsite_id)
             AND (itemsite_item_id=item_id)
             AND (womatl_wo_id=pWoid)
             AND (item_type='F') ) ) > 0 ) LOOP

    FOR _p IN SELECT wo_qtyord, wo_startdate, womatl_id, womatl_wooper_id
              FROM wo, womatl, itemsite, item
              WHERE ( (womatl_itemsite_id=itemsite_id)
               AND (itemsite_item_id=item_id)
               AND (item_type='F')
               AND (womatl_wo_id=wo_id)
               AND (wo_id=pWoid) ) LOOP

      INSERT INTO womatl
      ( womatl_wo_id, womatl_itemsite_id, womatl_wooper_id,
        womatl_schedatwooper, womatl_duedate,
        womatl_uom_id, womatl_qtyfxd, womatl_qtyper, womatl_scrap,
        womatl_qtyreq,
        womatl_qtyiss, womatl_qtywipscrap,
        womatl_lastissue, womatl_lastreturn,
        womatl_cost, womatl_picklist, womatl_createwo,
        womatl_issuemethod, womatl_notes, womatl_ref )
      SELECT pWoid, cs.itemsite_id, _p.womatl_wooper_id,
             womatl_schedatwooper, womatl_duedate,
             bomitem_uom_id, bomitem_qtyfxd, (bomitem_qtyper * womatl_qtyper), bomitem_scrap,
             roundQty(itemuomfractionalbyuom(bomitem_item_id, bomitem_uom_id), 
                     ((bomitem_qtyfxd + _p.wo_qtyord * bomitem_qtyper) * womatl_qtyper * (1 + bomitem_scrap))),
             0, 0,
             startOfTime(), startOfTime(),
             0, ci.item_picklist, ( (ci.item_type='M') AND (bomitem_createwo) ),
             bomitem_issuemethod, bomitem_notes, bomitem_ref 
      FROM wo, womatl, bomitem, 
           itemsite AS cs, itemsite AS ps,
           item AS ci, item AS pi
      WHERE ( (womatl_itemsite_id=ps.itemsite_id)
       AND (womatl_wo_id=wo_id)
       AND (bomitem_parent_item_id=pi.item_id)
       AND (bomitem_item_id=ci.item_id)
       AND (ps.itemsite_warehous_id=cs.itemsite_warehous_id)
       AND (cs.itemsite_item_id=ci.item_id)
       AND (ps.itemsite_item_id=pi.item_id)
       AND (woEffectiveDate(_p.wo_startdate) BETWEEN bomitem_effective AND (bomitem_expires - 1))
       AND (womatl_id=_p.womatl_id));

      DELETE FROM womatl
      WHERE (womatl_id=_p.womatl_id);

    END LOOP;
  END LOOP;

  UPDATE wo
  SET wo_adhoc=TRUE
  WHERE (wo_id=pWoid);

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

  RETURN _womatlid;
END;
$$;


ALTER FUNCTION public.createwomaterial(pwoid integer, pitemsiteid integer, pissuemethod character, puomid integer, pqtyfxd numeric, pqtyper numeric, pscrap numeric, pbomitemid integer, pnotes text, pref text, pwooperid integer, ppicklist boolean, pprice numeric) OWNER TO admin;

--
-- TOC entry 1373 (class 1255 OID 146565929)
-- Dependencies: 4536 8
-- Name: creditmemototal(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  -- TO DO:  Add in line item taxes
  SELECT COALESCE(cmhead_freight,0.0) + COALESCE(cmhead_misc,0.0) +
         ( SELECT COALESCE(ROUND(SUM((cmitem_qtycredit * cmitem_qty_invuomratio) * cmitem_unitprice / cmitem_price_invuomratio), 2), 0.0)
             FROM cmitem
            WHERE (cmitem_cmhead_id=cmhead_id)
           ) +
         (SELECT COALESCE(SUM(tax) * -1, 0) AS tax
           FROM ( SELECT ROUND(SUM(taxdetail_tax),2) AS tax
                  FROM tax
                  JOIN calculateTaxDetailSummary('CM', cmhead_id, 'T') ON (taxdetail_tax_id=tax_id)
                  GROUP BY tax_id) AS data)
           INTO _result
  FROM cmhead
  WHERE (cmhead_id=pCreditmemoId);

  IF (NOT FOUND) THEN
    return 0;
  ELSE
    RETURN _result;
  END IF;

END;
$_$;


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

--
-- TOC entry 205 (class 1259 OID 146565930)
-- Dependencies: 5947 5948 5949 5950 5951 8
-- Name: crmacct; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE crmacct (
    crmacct_id integer NOT NULL,
    crmacct_number text NOT NULL,
    crmacct_name text,
    crmacct_active boolean DEFAULT true,
    crmacct_type character(1),
    crmacct_cust_id integer,
    crmacct_competitor_id integer,
    crmacct_partner_id integer,
    crmacct_prospect_id integer,
    crmacct_vend_id integer,
    crmacct_cntct_id_1 integer,
    crmacct_cntct_id_2 integer,
    crmacct_parent_id integer,
    crmacct_notes text,
    crmacct_taxauth_id integer,
    crmacct_owner_username text,
    crmacct_emp_id integer,
    crmacct_salesrep_id integer,
    crmacct_usr_username text,
    CONSTRAINT crmacct_crmacct_number_check CHECK ((crmacct_number <> ''::text)),
    CONSTRAINT crmacct_crmacct_type_check CHECK ((crmacct_type = ANY (ARRAY['I'::bpchar, 'O'::bpchar]))),
    CONSTRAINT crmacct_crmacct_usr_username_check CHECK ((btrim(crmacct_usr_username) <> ''::text)),
    CONSTRAINT crmacct_owner_username_check CHECK ((btrim(crmacct_owner_username) <> ''::text))
);


ALTER TABLE public.crmacct OWNER TO admin;

--
-- TOC entry 8953 (class 0 OID 0)
-- Dependencies: 205
-- Name: TABLE crmacct; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE crmacct IS 'CRM Accounts are umbrella records that tie together people and organizations with whom we have business relationships.';


--
-- TOC entry 8954 (class 0 OID 0)
-- Dependencies: 205
-- Name: COLUMN crmacct.crmacct_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_id IS 'Internal ID of this CRM Account.';


--
-- TOC entry 8955 (class 0 OID 0)
-- Dependencies: 205
-- Name: COLUMN crmacct.crmacct_number; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_number IS 'Abbreviated human-readable identifier for this CRM Account.';


--
-- TOC entry 8956 (class 0 OID 0)
-- Dependencies: 205
-- Name: COLUMN crmacct.crmacct_name; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_name IS 'Long name of this CRM Account.';


--
-- TOC entry 8957 (class 0 OID 0)
-- Dependencies: 205
-- Name: COLUMN crmacct.crmacct_active; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_active IS 'This CRM Account is available for new activity.';


--
-- TOC entry 8958 (class 0 OID 0)
-- Dependencies: 205
-- Name: COLUMN crmacct.crmacct_type; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_type IS 'This indicates whether the CRM Account represents an organization or an individual person.';


--
-- TOC entry 8959 (class 0 OID 0)
-- Dependencies: 205
-- Name: COLUMN crmacct.crmacct_cust_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_cust_id IS 'If this is not null, this CRM Account is a Customer.';


--
-- TOC entry 8960 (class 0 OID 0)
-- Dependencies: 205
-- Name: COLUMN crmacct.crmacct_competitor_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_competitor_id IS 'For now, > 0 indicates this CRM Account is a competitor. Eventually this may become a foreign key to a table of competitors.';


--
-- TOC entry 8961 (class 0 OID 0)
-- Dependencies: 205
-- Name: COLUMN crmacct.crmacct_partner_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_partner_id IS 'For now, > 0 indicates this CRM Account is a partner. Eventually this may become a foreign key to a table of partners.';


--
-- TOC entry 8962 (class 0 OID 0)
-- Dependencies: 205
-- Name: COLUMN crmacct.crmacct_prospect_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_prospect_id IS 'If this is not null, this CRM Account is a Prospect.';


--
-- TOC entry 8963 (class 0 OID 0)
-- Dependencies: 205
-- Name: COLUMN crmacct.crmacct_vend_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_vend_id IS 'If this is not null, this CRM Account is a Vendor.';


--
-- TOC entry 8964 (class 0 OID 0)
-- Dependencies: 205
-- Name: COLUMN crmacct.crmacct_cntct_id_1; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_cntct_id_1 IS 'The primary contact for the CRM Account.';


--
-- TOC entry 8965 (class 0 OID 0)
-- Dependencies: 205
-- Name: COLUMN crmacct.crmacct_cntct_id_2; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_cntct_id_2 IS 'The secondary contact for the CRM Account.';


--
-- TOC entry 8966 (class 0 OID 0)
-- Dependencies: 205
-- Name: COLUMN crmacct.crmacct_parent_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_parent_id IS 'The internal ID of an (optional) parent CRM Account. For example, if the current CRM Account is a subsidiary of another company, the crmacct_parent_id points to the CRM Account representing that parent company.';


--
-- TOC entry 8967 (class 0 OID 0)
-- Dependencies: 205
-- Name: COLUMN crmacct.crmacct_notes; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_notes IS 'Free-form comments pertaining to the CRM Account.';


--
-- TOC entry 8968 (class 0 OID 0)
-- Dependencies: 205
-- Name: COLUMN crmacct.crmacct_taxauth_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_taxauth_id IS 'If this is not null, this CRM Account is a Tax Authority.';


--
-- TOC entry 8969 (class 0 OID 0)
-- Dependencies: 205
-- Name: COLUMN crmacct.crmacct_owner_username; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_owner_username IS 'The application User responsible for this CRM Account.';


--
-- TOC entry 8970 (class 0 OID 0)
-- Dependencies: 205
-- Name: COLUMN crmacct.crmacct_emp_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_emp_id IS 'If this is not null, this CRM Account is an Employee.';


--
-- TOC entry 8971 (class 0 OID 0)
-- Dependencies: 205
-- Name: COLUMN crmacct.crmacct_salesrep_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_salesrep_id IS 'If this is not null, this CRM Account is a Sales Rep.';


--
-- TOC entry 8972 (class 0 OID 0)
-- Dependencies: 205
-- Name: COLUMN crmacct.crmacct_usr_username; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN crmacct.crmacct_usr_username IS 'If this is not null, this CRM Account is an application User.';


--
-- TOC entry 1374 (class 1255 OID 146565941)
-- Dependencies: 2781 4536 8
-- Name: crmacct(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION crmacct() RETURNS SETOF crmacct
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _row crmacct%ROWTYPE;
  _priv TEXT;
  _grant BOOLEAN;

BEGIN
  -- This query will give us the most permissive privilege the user has been granted
  SELECT privilege, granted INTO _priv, _grant
  FROM privgranted 
  WHERE privilege IN ('MaintainAllCRMAccounts','ViewAllCRMAccounts','MaintainPersonalCRMAccounts','ViewPersonalCRMAccounts')
  ORDER BY granted DESC, sequence
  LIMIT 1;

  -- If have an 'All' privilege return all results
  IF (_priv ~ 'All' AND _grant) THEN
    FOR _row IN 
      SELECT * FROM crmacct
    LOOP
      RETURN NEXT _row;
    END LOOP;
  -- Otherwise if have any other grant, must be personal privilege.
  ELSIF (_grant) THEN
    FOR _row IN 
      SELECT * FROM crmacct 
      WHERE crmacct_owner_username = getEffectiveXtUser()
    LOOP
      RETURN NEXT _row;
    END LOOP;
  END IF;

  RETURN;

END;
$$;


ALTER FUNCTION public.crmacct() OWNER TO admin;

--
-- TOC entry 8974 (class 0 OID 0)
-- Dependencies: 1374
-- Name: FUNCTION crmacct(); Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON FUNCTION crmacct() IS 'A table function that returns CRM Account results according to privilege settings.';


--
-- TOC entry 1375 (class 1255 OID 146565942)
-- Dependencies: 8
-- Name: crypt(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION crypt(text, text) RETURNS text
    LANGUAGE c IMMUTABLE STRICT
    AS '$libdir/pgcrypto', 'pg_crypt';


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

--
-- TOC entry 1376 (class 1255 OID 146565943)
-- Dependencies: 4536 8
-- Name: currconcat(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION currconcat(integer) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  id ALIAS FOR $1;
  returnVal   VARCHAR(15) := '';
BEGIN
  SELECT currConcat(curr_abbr, curr_symbol) INTO returnVal
      FROM curr_symbol WHERE curr_id = id;
  RETURN returnVal;
END;
$_$;


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

--
-- TOC entry 1377 (class 1255 OID 146565944)
-- Dependencies: 4536 8
-- Name: currconcat(character varying, character varying); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION currconcat(character varying, character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  curr_abbr   ALIAS FOR $1;
  curr_symbol ALIAS FOR $2;
  returnVal   VARCHAR(15) := '';
BEGIN
  IF length(trim(curr_abbr)) > 0 AND length(trim(curr_symbol)) > 0 THEN
      returnVal := trim(curr_abbr) || ' - ' || trim(curr_symbol);

  ELSIF length(trim(curr_abbr)) > 0 THEN
      returnVal := curr_abbr;

  ELSIF length(trim(curr_symbol)) > 0 THEN
      returnVal := curr_symbol;
  END IF;

  RETURN returnVal;
END;
$_$;


ALTER FUNCTION public.currconcat(character varying, character varying) OWNER TO admin;

--
-- TOC entry 1378 (class 1255 OID 146565945)
-- Dependencies: 4536 8
-- Name: currentapmemonumber(); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT orderseq_number INTO _number
  FROM orderseq
  WHERE (orderseq_name='APMemoNumber');
  IF (NOT FOUND) THEN
    _number := 0;
  END IF;

  RETURN _number;

END;
$$;


ALTER FUNCTION public.currentapmemonumber() OWNER TO admin;

--
-- TOC entry 1351 (class 1255 OID 146565946)
-- Dependencies: 4536 8
-- Name: currentarmemonumber(); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT orderseq_number INTO _number
  FROM orderseq
  WHERE (orderseq_name='ARMemoNumber');
  IF (NOT FOUND) THEN
    _number := 0;
  END IF;

  RETURN _number;

END;
$$;


ALTER FUNCTION public.currentarmemonumber() OWNER TO admin;

--
-- TOC entry 1172 (class 1255 OID 146565947)
-- Dependencies: 4536 8
-- Name: currentcashrcptnumber(); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT orderseq_number INTO _number
  FROM orderseq
  WHERE (orderseq_name='CashRcptNumber');
  IF (NOT FOUND) THEN
    _number := 0;
  END IF;

  RETURN _number;

END;
$$;


ALTER FUNCTION public.currentcashrcptnumber() OWNER TO admin;

--
-- TOC entry 1267 (class 1255 OID 146565948)
-- Dependencies: 4536 8
-- Name: currentnumber(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION currentnumber(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
  pName   ALIAS FOR $1;
  _number INTEGER;

BEGIN
  SELECT orderseq_number INTO _number
  FROM orderseq
  WHERE (orderseq_name=pName);
  IF (NOT FOUND) THEN
    _number := 0;
  END IF;

  RETURN _number;

END;
$_$;


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

--
-- TOC entry 1379 (class 1255 OID 146565949)
-- Dependencies: 4536 8
-- Name: currexchangecheckoverlap(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION currexchangecheckoverlap() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
    numberOfOverlaps INTEGER NOT NULL := 0;
    curr_string VARCHAR(16);
    new_id INTEGER;
BEGIN
  new_id := NEW.curr_id;
  -- ensure that effective date <= expiration date
  IF NEW.curr_effective > NEW.curr_expires THEN
    RAISE EXCEPTION
      'Effective date % must be earlier than expiration date %',
      NEW.curr_effective, NEW.curr_expires;
  END IF;

  -- ensure new exchange rate does not overlap in time with any others
  SELECT count(*) INTO numberOfOverlaps
    FROM curr_rate
    WHERE curr_id = NEW.curr_id
      AND curr_rate_id != NEW.curr_rate_id
      AND (
          (curr_effective BETWEEN
              NEW.curr_effective AND NEW.curr_expires OR
           curr_expires BETWEEN
              NEW.curr_effective AND NEW.curr_expires)
         OR (curr_effective <= NEW.curr_effective AND
             curr_expires   >= NEW.curr_expires)
      );
  IF numberOfOverlaps > 0 THEN
    SELECT currConcat(curr_symbol, curr_abbr)
      INTO curr_string
      FROM curr_symbol
      WHERE curr_id = new_id;
    RAISE EXCEPTION
      'The date range % to % overlaps with another date range.',
      NEW.curr_effective, NEW.curr_expires;
  END IF;
  RETURN NEW;
END;
$$;


ALTER FUNCTION public.currexchangecheckoverlap() OWNER TO admin;

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

CREATE FUNCTION currgain(integer, numeric, date, date) RETURNS numeric
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pId ALIAS FOR $1;
  pValue ALIAS FOR $2;
  pStart ALIAS FOR $3;
  pEnd ALIAS FOR $4;
  _start DATE;
  _end DATE;
  _gain NUMERIC;
  _multiplier	INTEGER	:= 1;

BEGIN
  IF (pEnd = pStart OR pValue = 0) THEN
    RETURN 0;
  END IF;

  IF (pStart > pEnd) THEN
    _start := pEnd;
    _end   := pStart;
    _multiplier := -1;
  ELSE
    _start := pStart;
    _end := pEnd;
  END IF;

  _gain := currToBase(pId, pValue, _start) - currToBase(pId, pValue, _end);
  IF (_gain IS NULL) THEN
    RAISE EXCEPTION 'Missing exchange rate for curr_id % on % or %',
                    pId, _start, _end;
  END IF;

  RETURN _gain * _multiplier;
END;
$_$;


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

--
-- TOC entry 1381 (class 1255 OID 146565951)
-- Dependencies: 4536 8
-- Name: curronebase(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION curronebase() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  baseCount INTEGER := 0;
BEGIN
  IF NEW.curr_base = TRUE THEN
    SELECT count(*)
      INTO baseCount
      FROM curr_symbol
      WHERE curr_base = TRUE
        AND curr_id != NEW.curr_id;
    IF baseCount > 0 THEN
      RAISE EXCEPTION
          'Cannot make % - % the base currency because one is already defined.',
          NEW.curr_symbol, NEW.curr_abbr;
    ELSE
      SELECT count(*)
        INTO baseCount
        FROM curr_rate
        WHERE curr_id = NEW.curr_id;
      IF baseCount = 0 THEN
        -- put a row in the curr_rate table to avoid special-case
        -- code for converting base currency to base currency
        INSERT INTO curr_rate
          (curr_id, curr_rate, curr_effective, curr_expires) VALUES
          (NEW.curr_id, 1, startOfTime(), endOfTime());
      END IF;
    END IF;
  END IF;
  RETURN NEW;
END;
$$;


ALTER FUNCTION public.curronebase() OWNER TO admin;

--
-- TOC entry 1382 (class 1255 OID 146565952)
-- Dependencies: 4536 8
-- Name: currrate(integer, date); Type: FUNCTION; Schema: public; Owner: admin
--

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

END;
$_$;


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

--
-- TOC entry 1383 (class 1255 OID 146565953)
-- Dependencies: 4536 8
-- Name: currrate(integer, integer, date); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION currrate(pfromcurr integer, ptocurr integer, pdate date) RETURNS numeric
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _fromRate NUMERIC := 1.0;
  _toRate NUMERIC := 1.0;
  _returnVal NUMERIC := 1.0;
BEGIN
  IF pFromCurr = pToCurr THEN
    RETURN _returnVal;
  END IF;

  IF (pFromCurr IS NOT NULL) THEN
    SELECT curr_rate INTO _fromRate
    FROM curr_rate
    WHERE ( (curr_id=pFromCurr)
    AND (pDate BETWEEN curr_effective AND curr_expires) );

    IF ( NOT FOUND) THEN
      RAISE EXCEPTION 'Currency exchange rate for currency % not found on %', pFromCurr, formatDate(pDate);
    END IF;
  END IF;

  IF (pToCurr IS NOT NULL) THEN
    SELECT curr_rate INTO _toRate
    FROM curr_rate
    WHERE ( (curr_id=pToCurr)
    AND (pDate BETWEEN curr_effective AND curr_expires) );

    IF ( NOT FOUND) THEN
      RAISE EXCEPTION 'Currency exchange rate for currency % not found on %', pToCurr, formatDate(pDate);
    END IF;
  END IF;

  _returnVal := _fromRate / _toRate;

  RETURN _returnVal;
END;
$$;


ALTER FUNCTION public.currrate(pfromcurr integer, ptocurr integer, pdate date) OWNER TO admin;

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

CREATE FUNCTION currtobase(integer, numeric, date) RETURNS numeric
    LANGUAGE plpgsql STABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pId     ALIAS FOR $1;
  pValue  ALIAS FOR $2;
  _date  DATE;
  _output NUMERIC;
BEGIN
  _date := $3;
  IF _date IS NULL THEN
    _date := 'now';
  END IF;

  IF pValue = 0 OR pValue IS NULL THEN
    _output := 0;
  ELSIF (baseCurrId() = pId) THEN
    _output := pValue;
  ELSE
    SELECT pValue / curr_rate
        INTO  _output
      FROM  curr_rate
     WHERE curr_id = pId
       AND _date BETWEEN curr_effective AND curr_expires;
    IF (_output IS NULL OR NOT FOUND) THEN
      RAISE EXCEPTION 'No exchange rate for % on %', pId, _date;
    END IF;
  END IF;
  RETURN _output;
END;
$_$;


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

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

CREATE FUNCTION currtocurr(integer, integer, numeric, date) RETURNS numeric
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pFromCurr ALIAS FOR $1;
  pToCurr ALIAS FOR $2;
  pValue   ALIAS FOR $3;
  pEffective ALIAS FOR $4;
  _convertedValue NUMERIC;
  _fromRate NUMERIC;
  _toRate NUMERIC;
BEGIN
  IF pFromCurr = pToCurr THEN
    RETURN pValue;
  END IF;

  IF pValue = 0 OR pValue IS NULL THEN
    RETURN 0;
  END IF;

  SELECT curr_rate INTO _fromRate
  FROM curr_rate
  WHERE curr_id = pFromCurr
    AND pEffective BETWEEN curr_effective AND curr_expires;

  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'No exchange rate for % on %', pFromCurr, pEffective;
  END IF;

  SELECT curr_rate INTO _toRate
  FROM curr_rate
  WHERE curr_id = pToCurr
    AND pEffective BETWEEN curr_effective AND curr_expires;

  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'No exchange rate for % on %', pToCurr, pEffective;
  END IF;

  _convertedValue := pValue * _toRate / _fromRate;

  RETURN _convertedValue;
END;
$_$;


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

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

CREATE FUNCTION currtolocal(integer, numeric, date) RETURNS numeric
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
    pId     ALIAS FOR $1;
    pValue  ALIAS FOR $2;
    _date   DATE;
    _output NUMERIC;
BEGIN
    _date := $3;
    IF _date IS NULL THEN
        _date := 'now';
    END IF;

    IF pValue = 0 OR pValue IS NULL THEN
        _output := 0;
    ELSIF (baseCurrId() = pId) THEN
      _output := pValue;
    ELSE
        SELECT pValue * curr_rate
            INTO  _output
            FROM  curr_rate
            WHERE curr_id = pId
              AND _date BETWEEN curr_effective AND curr_expires;
        IF (_output IS NULL OR NOT FOUND) THEN
          RAISE EXCEPTION 'No exchange rate for % on %', pId, _date;
        END IF;
    END IF;
    RETURN _output;
END;
$_$;


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

--
-- TOC entry 1388 (class 1255 OID 146565957)
-- Dependencies: 8
-- Name: custitem(integer, integer, date); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION custitem(cust_id integer, shipto_id integer DEFAULT (-1), asof date DEFAULT ('now'::text)::date) RETURNS SETOF integer
    LANGUAGE sql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.

  -- Non Exclusive
  SELECT item_id
  FROM item 
  WHERE (NOT item_exclusive)
   AND (item_sold)
  UNION
  -- Exclusive, Shipto match
  SELECT item_id
  FROM item
    JOIN ipsiteminfo ON (ipsitem_item_id=item_id)
    JOIN ipshead ON (ipshead_id=ipsitem_ipshead_id)
    JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
  WHERE (item_exclusive)
   AND (item_sold)
   AND ($2 != -1)
   AND (ipsass_shipto_id=$2)
   AND ($3 BETWEEN ipshead_effective AND (ipshead_expires - 1))
  UNION
  SELECT item_id
  FROM item
    JOIN ipsiteminfo ON (ipsitem_prodcat_id=item_prodcat_id)
    JOIN ipshead ON (ipshead_id=ipsitem_ipshead_id)
    JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
  WHERE (item_exclusive)
   AND (item_sold)
   AND ($2 != -1)
   AND (ipsass_shipto_id=$2)
   AND ($3 BETWEEN ipshead_effective AND (ipshead_expires - 1))
  UNION
   -- Exclusive, Shipto pattern match
  SELECT item_id
  FROM item
    JOIN ipsiteminfo ON (ipsitem_item_id=item_id)
    JOIN ipshead ON (ipshead_id=ipsitem_ipshead_id)
    JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
    JOIN shiptoinfo ON (shipto_num ~ ipsass_shipto_pattern)
  WHERE (item_exclusive)
   AND (item_sold)
   AND (COALESCE(length(ipsass_shipto_pattern), 0) > 0)
   AND (ipsass_cust_id=$1)
   AND ($2 != -1)
   AND (shipto_id=$2)
   AND ($3 BETWEEN ipshead_effective AND (ipshead_expires - 1))
  UNION
  SELECT item_id
  FROM item
    JOIN ipsiteminfo ON (ipsitem_prodcat_id=item_prodcat_id)
    JOIN ipshead ON (ipshead_id=ipsitem_ipshead_id)
    JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
    JOIN shiptoinfo ON (shipto_num ~ ipsass_shipto_pattern)
  WHERE (item_exclusive)
   AND (item_sold)
   AND (COALESCE(length(ipsass_shipto_pattern), 0) > 0)
   AND (ipsass_cust_id=$1)
   AND ($2 != -1)
   AND (shipto_id=$2)
   AND ($3 BETWEEN ipshead_effective AND (ipshead_expires - 1))
  UNION
   -- Exclusive, Customer match
  SELECT item_id
  FROM item
    JOIN ipsiteminfo ON (ipsitem_item_id=item_id)
    JOIN ipshead ON (ipshead_id=ipsitem_ipshead_id)
    JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
  WHERE (item_exclusive)
   AND (item_sold)
   AND (ipsass_cust_id=$1)
   AND (ipsass_shipto_id=-1)
   AND (ipsass_shipto_pattern='')
   AND (ipsass_custtype_id=-1)
   AND (ipsass_custtype_pattern='')
   AND ($3 BETWEEN ipshead_effective AND (ipshead_expires - 1))
  UNION
  SELECT item_id
  FROM item
    JOIN ipsiteminfo ON (ipsitem_prodcat_id=item_prodcat_id)
    JOIN ipshead ON (ipshead_id=ipsitem_ipshead_id)
    JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
  WHERE (item_exclusive)
   AND (item_sold)
   AND (ipsass_cust_id=$1)
   AND (ipsass_shipto_id=-1)
   AND (ipsass_shipto_pattern='')
   AND (ipsass_custtype_id=-1)
   AND (ipsass_custtype_pattern='')
   AND ($3 BETWEEN ipshead_effective AND (ipshead_expires - 1))
  UNION
  -- Exclusive, Customer Type match
  SELECT item_id
  FROM item
    JOIN ipsiteminfo ON (ipsitem_item_id=item_id)
    JOIN ipshead ON (ipshead_id=ipsitem_ipshead_id)
    JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
    JOIN custinfo ON (ipsass_custtype_id=cust_custtype_id)
  WHERE (item_exclusive)
   AND (item_sold)
   AND (cust_id=$1)
   AND ($3 BETWEEN ipshead_effective AND (ipshead_expires - 1))
  UNION
  SELECT item_id
  FROM item
    JOIN ipsiteminfo ON (ipsitem_prodcat_id=item_prodcat_id)
    JOIN ipshead ON (ipshead_id=ipsitem_ipshead_id)
    JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
    JOIN custinfo ON (ipsass_custtype_id=cust_custtype_id)
  WHERE (item_exclusive)
   AND (item_sold)
   AND (cust_id=$1)
   AND ($3 BETWEEN ipshead_effective AND (ipshead_expires - 1))
  UNION
  -- Exclusive, Customer Type pattern match
  SELECT item_id
  FROM item
    JOIN ipsiteminfo ON (ipsitem_item_id=item_id)
    JOIN ipshead ON (ipshead_id=ipsitem_ipshead_id)
    JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
    JOIN custtype ON (custtype_code ~ ipsass_custtype_pattern)
    JOIN custinfo ON (cust_custtype_id=custtype_id)
  WHERE (item_exclusive)
   AND (item_sold)
   AND (COALESCE(length(ipsass_custtype_pattern), 0) > 0)
   AND (cust_id=$1)
   AND ($3 BETWEEN ipshead_effective AND (ipshead_expires - 1))
  UNION
  SELECT item_id
  FROM item
    JOIN ipsiteminfo ON (ipsitem_prodcat_id=item_prodcat_id)
    JOIN ipshead ON (ipshead_id=ipsitem_ipshead_id)
    JOIN ipsass ON (ipsass_ipshead_id=ipshead_id)
    JOIN custtype ON (custtype_code ~ ipsass_custtype_pattern)
    JOIN custinfo ON (cust_custtype_id=custtype_id)
  WHERE (item_exclusive)
   AND (item_sold)
   AND (COALESCE(length(ipsass_custtype_pattern), 0) > 0)
   AND (cust_id=$1)
   AND ($3 BETWEEN ipshead_effective AND (ipshead_expires - 1))
   
$_$;


ALTER FUNCTION public.custitem(cust_id integer, shipto_id integer, asof date) OWNER TO admin;

--
-- TOC entry 1389 (class 1255 OID 146565958)
-- Dependencies: 4536 8
-- Name: customercanpurchase(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  RETURN customerCanPurchase(pitemid, pCustid, -1);
END;
$_$;


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

--
-- TOC entry 1390 (class 1255 OID 146565959)
-- Dependencies: 4536 8
-- Name: customercanpurchase(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION customercanpurchase(integer, integer, integer) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pitemid ALIAS FOR $1;
  pCustid ALIAS FOR $2;
  pShiptoid AlIAS FOR $3;
  _id INTEGER;
  _item RECORD;

BEGIN
  RETURN customerCanPurchase(pitemid, pCustid, pShiptoid, CURRENT_DATE);
END;
$_$;


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

--
-- TOC entry 1391 (class 1255 OID 146565960)
-- Dependencies: 4536 8
-- Name: customercanpurchase(integer, integer, integer, date); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION customercanpurchase(integer, integer, integer, date) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pitemid ALIAS FOR $1;
  pCustid ALIAS FOR $2;
  pShiptoid AlIAS FOR $3;
  pAsOf ALIAS FOR $4;
  _id INTEGER;
  _item RECORD;

BEGIN

  SELECT item_sold, item_exclusive
    INTO _item
    FROM item
   WHERE(item_id=pItemid);

--  Make sure that this is at least a sold Item
  IF (NOT _item.item_sold) THEN
    RETURN FALSE;
  END IF;

--  Everyone can purchase a non-exclusive item
  IF (NOT _item.item_exclusive) THEN
    RETURN TRUE;
  END IF;

  IF(pShiptoid != -1) THEN
--  Check for a shipto Assigned Price
    SELECT ipsitem_id INTO _id
      FROM ipsiteminfo, ipshead, ipsass
     WHERE((ipsitem_ipshead_id=ipshead_id)
       AND (ipsass_ipshead_id=ipshead_id)
       AND (pAsOf BETWEEN ipshead_effective AND (ipshead_expires - 1))
       AND (ipsitem_item_id=pItemid)
       AND (ipsass_shipto_id != -1)
       AND (ipsass_shipto_id=pShiptoid))
     LIMIT 1;
    IF (FOUND) THEN
      RETURN TRUE;
    END IF;
    SELECT ipsitem_id INTO _id
      FROM ipsiteminfo, item, ipshead, ipsass
     WHERE((ipsitem_ipshead_id=ipshead_id)
       AND (ipsitem_prodcat_id = item_prodcat_id)
       AND (ipsass_ipshead_id=ipshead_id)
       AND (pAsOf BETWEEN ipshead_effective AND (ipshead_expires - 1))
       AND (item_id=pItemid)
       AND (ipsass_shipto_id != -1)
       AND (ipsass_shipto_id=pShiptoid))
     LIMIT 1;
    IF (FOUND) THEN
      RETURN TRUE;
    END IF;

--  Check for a Shipto Pattern Assigned Price
    SELECT ipsitem_id INTO _id
      FROM ipsiteminfo, ipshead, ipsass, shiptoinfo
     WHERE((ipsitem_ipshead_id=ipshead_id)
       AND (ipsass_ipshead_id=ipshead_id)
       AND (pAsOf BETWEEN ipshead_effective AND (ipshead_expires - 1))
       AND (COALESCE(length(ipsass_shipto_pattern), 0) > 0)
       AND (shipto_num ~ ipsass_shipto_pattern)
       AND (ipsass_cust_id=pCustid)
       AND (ipsitem_item_id=pItemid)
       AND (shipto_id=pShiptoid))
     LIMIT 1;
    IF (FOUND) THEN
      RETURN TRUE;
    END IF;
    SELECT ipsitem_id INTO _id
      FROM ipsiteminfo, item, ipshead, ipsass, shiptoinfo
     WHERE((ipsitem_ipshead_id=ipshead_id)
       AND (ipsitem_prodcat_id = item_prodcat_id)
       AND (ipsass_ipshead_id=ipshead_id)
       AND (pAsOf BETWEEN ipshead_effective AND (ipshead_expires - 1))
       AND (COALESCE(length(ipsass_shipto_pattern), 0) > 0)
       AND (shipto_num ~ ipsass_shipto_pattern)
       AND (ipsass_cust_id=pCustid)
       AND (item_id=pItemid)
       AND (shipto_id=pShiptoid))
     LIMIT 1;
    IF (FOUND) THEN
      RETURN TRUE;
    END IF;
  END IF;

--  Check for a Customer Assigned Price
  SELECT ipsitem_id INTO _id
    FROM ipsiteminfo, ipshead, ipsass
   WHERE((ipsitem_ipshead_id=ipshead_id)
     AND (ipsass_ipshead_id=ipshead_id)
     AND (pAsOf BETWEEN ipshead_effective AND (ipshead_expires - 1))
     AND (ipsitem_item_id=pItemid)
     AND (COALESCE(length(ipsass_shipto_pattern), 0) = 0)
     AND (ipsass_cust_id=pCustid))
   LIMIT 1;
  IF (FOUND) THEN
    RETURN TRUE;
  END IF;
  SELECT ipsitem_id INTO _id
    FROM ipsiteminfo, item, ipshead, ipsass
   WHERE((ipsitem_ipshead_id=ipshead_id)
     AND (ipsitem_prodcat_id = item_prodcat_id)
     AND (ipsass_ipshead_id=ipshead_id)
     AND (pAsOf BETWEEN ipshead_effective AND (ipshead_expires - 1))
     AND (item_id=pItemid)
     AND (COALESCE(length(ipsass_shipto_pattern), 0) = 0)
     AND (ipsass_cust_id=pCustid))
   LIMIT 1;
  IF (FOUND) THEN
    RETURN TRUE;
  END IF;

--  Check for a Customer Type Assigned Price
  SELECT ipsitem_id INTO _id
    FROM ipsiteminfo, ipshead, ipsass, custinfo
   WHERE( (ipsitem_ipshead_id=ipshead_id)
     AND  (ipsass_ipshead_id=ipshead_id)
     AND  (ipsass_custtype_id != -1)
     AND  (cust_custtype_id = ipsass_custtype_id)
     AND  (pAsOf BETWEEN ipshead_effective AND (ipshead_expires - 1))
     AND  (ipsitem_item_id=pItemid)
     AND  (cust_id=pCustid))
    LIMIT 1;
  IF (FOUND) THEN
    RETURN TRUE;
  END IF;
  SELECT ipsitem_id INTO _id
    FROM ipsiteminfo, item, ipshead, ipsass, custinfo
   WHERE( (ipsitem_ipshead_id=ipshead_id)
     AND  (ipsitem_prodcat_id = item_prodcat_id)
     AND  (ipsass_ipshead_id=ipshead_id)
     AND  (ipsass_custtype_id != -1)
     AND  (cust_custtype_id = ipsass_custtype_id)
     AND  (pAsOf BETWEEN ipshead_effective AND (ipshead_expires - 1))
     AND  (item_id=pItemid)
     AND  (cust_id=pCustid))
    LIMIT 1;
  IF (FOUND) THEN
    RETURN TRUE;
  END IF;

--  Check for a Customer Type Pattern Assigned Price
  SELECT ipsitem_id INTO _id
    FROM ipsiteminfo, ipshead, ipsass, custtype, custinfo
   WHERE((ipsitem_ipshead_id=ipshead_id)
     AND (ipsass_ipshead_id=ipshead_id)
     AND (coalesce(length(ipsass_custtype_pattern), 0) > 0)
     AND (custtype_code ~ ipsass_custtype_pattern)
     AND (cust_custtype_id=custtype_id)
     AND (pAsOf BETWEEN ipshead_effective AND (ipshead_expires - 1))
     AND (ipsitem_item_id=pItemid)
     AND (cust_id=pCustid))
   LIMIT 1;
  IF (FOUND) THEN
    RETURN TRUE;
  END IF;
  SELECT ipsitem_id INTO _id
    FROM ipsiteminfo, item, ipshead, ipsass, custtype, custinfo
   WHERE((ipsitem_ipshead_id=ipshead_id)
     AND (ipsitem_prodcat_id = item_prodcat_id)
     AND (ipsass_ipshead_id=ipshead_id)
     AND (coalesce(length(ipsass_custtype_pattern), 0) > 0)
     AND (custtype_code ~ ipsass_custtype_pattern)
     AND (cust_custtype_id=custtype_id)
     AND (pAsOf BETWEEN ipshead_effective AND (ipshead_expires - 1))
     AND (item_id=pItemid)
     AND (cust_id=pCustid))
   LIMIT 1;
  IF (FOUND) THEN
    RETURN TRUE;
  END IF;

--  That's it, Sales don't count - yet
  RETURN FALSE;

END;
$_$;


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

--
-- TOC entry 1392 (class 1255 OID 146565961)
-- Dependencies: 8
-- Name: dearmor(text); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1393 (class 1255 OID 146565962)
-- Dependencies: 8
-- Name: decrypt(bytea, bytea, text); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1394 (class 1255 OID 146565963)
-- Dependencies: 8
-- Name: decrypt_iv(bytea, bytea, bytea, text); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1395 (class 1255 OID 146565964)
-- Dependencies: 4536 8
-- Name: defaultlocationname(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT itemsite_location_id, itemsite_location INTO _p
  FROM itemsite
  WHERE (itemsite_id=pItemsiteid);

  IF (NOT FOUND) THEN
    RETURN 'Error';
  ELSIF (_p.itemsite_location_id = -1) THEN
    RETURN _p.itemsite_location;
  ELSE
    RETURN formatLocationName(_p.itemsite_location_id);
  END IF;

END;
$_$;


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

--
-- TOC entry 1396 (class 1255 OID 146565965)
-- Dependencies: 4536 8
-- Name: deleteaccount(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deleteaccount(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
  pAccntid ALIAS FOR $1;
  _check INTEGER;

BEGIN

--  Check to see if the passed accnt is used in a Cost Category
  SELECT costcat_id INTO _check
  FROM costcat
  WHERE ( (costcat_asset_accnt_id=pAccntid)
     OR   (costcat_liability_accnt_id=pAccntid)
     OR   (costcat_adjustment_accnt_id=pAccntid)
     OR   (costcat_purchprice_accnt_id=pAccntid)
     OR   (costcat_laboroverhead_accnt_id=pAccntid)
     OR   (costcat_scrap_accnt_id=pAccntid)
     OR   (costcat_invcost_accnt_id=pAccntid)
     OR   (costcat_wip_accnt_id=pAccntid)
     OR   (costcat_shipasset_accnt_id=pAccntid)
     OR   (costcat_mfgscrap_accnt_id=pAccntid)
     OR   (costcat_transform_accnt_id=pAccntid)
     OR   (costcat_freight_accnt_id=pAccntid) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

--  Check to see if the passed accnt is used in a Sales Account Assignment
  SELECT salesaccnt_id INTO _check
  FROM salesaccnt
  WHERE ( (salesaccnt_sales_accnt_id=pAccntid)
     OR   (salesaccnt_credit_accnt_id=pAccntid)
     OR   (salesaccnt_cos_accnt_id=pAccntid) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -2;
  END IF;

--  Check to see if the passed accnt is used in a A/R Account Assignment
  SELECT araccnt_id INTO _check
  FROM araccnt
  WHERE ( (araccnt_freight_accnt_id=pAccntid)
     OR   (araccnt_ar_accnt_id=pAccntid)
     OR   (araccnt_prepaid_accnt_id=pAccntid) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -3;
  END IF;

--  Check to see if the passed accnt is used in a Warehouse
  IF EXISTS (SELECT 1
               FROM whsinfo
              WHERE (warehous_default_accnt_id=pAccntid)) THEN
    RETURN -4;
  END IF;

--  Check to see if the passed accnt is used in a Bank Account
  SELECT bankaccnt_id INTO _check
  FROM bankaccnt
  WHERE (bankaccnt_accnt_id=pAccntid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -5;
  END IF;

--  Check to see if the passed accnt is used in an Expense Category
  SELECT expcat_id INTO _check
  FROM expcat
  WHERE ( (expcat_exp_accnt_id=pAccntid)
     OR   (expcat_liability_accnt_id=pAccntid)
     OR   (expcat_purchprice_accnt_id=pAccntid)
     OR   (expcat_freight_accnt_id=pAccntid) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -6;
  END IF;

--  Check to see if the passed accnt is used in a Tax Code
  SELECT tax_id INTO _check
  FROM tax
  WHERE (tax_sales_accnt_id=pAccntid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -7;
  END IF;

--  Check to see if the passed accnt is used in a Standard Journal Item
  SELECT stdjrnlitem_id INTO _check
  FROM stdjrnlitem
  WHERE (stdjrnlitem_accnt_id=pAccntid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -8;
  END IF;

--  Check to see if the passed accnt is used in a A/P Account Assignment
  SELECT apaccnt_ap_accnt_id INTO _check
  FROM apaccnt
  WHERE ( (apaccnt_ap_accnt_id=pAccntid)
     OR   (apaccnt_prepaid_accnt_id=pAccntid)
     OR   (apaccnt_discount_accnt_id=pAccntid) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -9;
  END IF;

--  Check to see if the passed accnt is used in an A/R Open Item record
  SELECT aropen_accnt_id INTO _check
    FROM aropen
   WHERE (aropen_accnt_id=pAccntid)
   LIMIT 1;
  IF (FOUND) THEN
    RETURN -11;
  END IF;

--  Check to see if the passed accnt has been used in the G/L
  SELECT gltrans_accnt_id INTO _check
  FROM gltrans
  WHERE (gltrans_accnt_id=pAccntid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -99;
  END IF;

  SELECT glseries_accnt_id INTO _check
  FROM glseries
  WHERE (glseries_accnt_id=pAccntid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -99;
  END IF;

  SELECT trialbal_accnt_id INTO _check
  FROM trialbal
  WHERE (trialbal_accnt_id=pAccntid)
    AND (trialbal_beginning != 0 OR trialbal_ending != 0)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -99;
  END IF;

  SELECT cashrcptmisc_accnt_id INTO _check
  FROM cashrcptmisc
  WHERE (cashrcptmisc_accnt_id=pAccntid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -99;
  END IF;

--  Delete any non-critical use
  DELETE FROM flitem
  WHERE (flitem_accnt_id=pAccntid);

  -- only possible because of trialbal error-check above
  DELETE FROM trialbal
  WHERE (trialbal_accnt_id=pAccntid)
    AND (trialbal_beginning=0)
    AND (trialbal_ending=0);

--  Delete the Account
  DELETE FROM accnt
  WHERE (accnt_id=pAccntid);

  RETURN 0;

END;
$_$;


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

--
-- TOC entry 1398 (class 1255 OID 146565966)
-- Dependencies: 4536 8
-- Name: deleteaccountingperiod(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

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

--  Check to make sure that there are not any posted G/L Transactions
--  in the period.
  SELECT gltrans_id INTO _check
  FROM gltrans, period
  WHERE ( (gltrans_date BETWEEN period_start AND period_end)
   AND (gltrans_posted)
   AND (period_id=pPeriodid) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -4;
  END IF;

  SELECT b.period_id INTO _check
    FROM period AS a, period AS b
   WHERE((a.period_id=pPeriodid)
     AND (a.period_end < b.period_start))
   LIMIT 1;
  IF (FOUND) THEN
    RETURN -5;
  END IF;

--  Delete the period
  DELETE FROM period
  WHERE (period_id=pPeriodid);

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 1399 (class 1255 OID 146565967)
-- Dependencies: 4536 8
-- Name: deleteaccountingyearperiod(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

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

  -- this yearperiod is in use by existing periods
  IF (EXISTS(SELECT period_id
             FROM period
             WHERE (period_yearperiod_id=pPeriodid))) THEN
    RETURN -2;
  END IF;

--  Delete the yearperiod
  DELETE FROM yearperiod
  WHERE (yearperiod_id=pPeriodid);

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 1400 (class 1255 OID 146565968)
-- Dependencies: 4536 8
-- Name: deleteaddress(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deleteaddress(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
  paddrId     ALIAS FOR $1;
  _count      INTEGER := 0;
BEGIN
  SELECT count(*) INTO _count
    FROM cntct
    WHERE (cntct_active
      AND  (cntct_addr_id = paddrId));
  IF (_count > 0) THEN
    RETURN -1;
  END IF;

  SELECT count(*) INTO _count
    FROM vendinfo
    WHERE (vend_active
      AND  (vend_addr_id = paddrId));
  IF (_count > 0) THEN
    RETURN -2;
  END IF;

  SELECT count(*) INTO _count
    FROM shiptoinfo
    WHERE (shipto_active
      AND  (shipto_addr_id = paddrId));
  IF (_count > 0) THEN
    RETURN -3;
  END IF;

  SELECT count(*) INTO _count
    FROM vendaddrinfo
    WHERE (vendaddr_addr_id = paddrId);
  IF (_count > 0) THEN
    RETURN -4;
  END IF;

  SELECT count(*) INTO _count
    FROM whsinfo
    WHERE (warehous_active
      AND  (warehous_addr_id = paddrId));
  IF (_count > 0) THEN
    RETURN -5;
  END IF;

  UPDATE cntct SET cntct_addr_id = NULL WHERE (cntct_addr_id = paddrId);
  UPDATE vendinfo SET vend_addr_id = NULL WHERE (vend_addr_id = paddrId);
  UPDATE shiptoinfo SET shipto_addr_id = NULL WHERE (shipto_addr_id =paddrId);
  UPDATE vendaddrinfo SET vendaddr_addr_id = NULL
    WHERE (vendaddr_addr_id = paddrId);
  UPDATE whsinfo SET warehous_addr_id = NULL WHERE (warehous_addr_id=paddrId);

  DELETE FROM addr WHERE addr_id = paddrId;
  RETURN 0;
END;
$_$;


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

--
-- TOC entry 1401 (class 1255 OID 146565969)
-- Dependencies: 4536 8
-- Name: deleteapcheck(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deleteapcheck(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 'deleteAPCheck() is deprecated - use deleteCheck() instead';
  RETURN deleteCheck($1);
END;
$_$;


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

--
-- TOC entry 1402 (class 1255 OID 146565970)
-- Dependencies: 4536 8
-- Name: deletebankadjustmenttype(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deletebankadjustmenttype(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
  pBankadjtypeid ALIAS FOR $1;
  _check INTEGER;

BEGIN

-- Check to see if the the adjustment type is being used in any adjustments
  SELECT bankadj_bankadjtype_id INTO _check
    FROM bankadj
   WHERE (bankadj_bankadjtype_id=pBankadjtypeid)
   LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

--  Delete the Account
  DELETE FROM bankadjtype
  WHERE (bankadjtype_id=pbankadjtypeid);

  RETURN 0;

END;
$_$;


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

--
-- TOC entry 1403 (class 1255 OID 146565971)
-- Dependencies: 4536 8
-- Name: deletebankreconciliation(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deletebankreconciliation(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;
BEGIN
  DELETE FROM bankrecitem
  WHERE bankrecitem_bankrec_id=pbankrecid;

  DELETE FROM bankrec
  WHERE bankrec_id=pbankrecid;

  RETURN 0;
END;
$_$;


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

--
-- TOC entry 1404 (class 1255 OID 146565972)
-- Dependencies: 4536 8
-- Name: deletebom(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  IF (fetchmetricbool('RevControl')) THEN
    SELECT rev_id INTO _result
    FROM rev
    WHERE ((rev_target_id=pItemid)
    AND (rev_target_type = 'BOM'))
    LIMIT 1;
    IF (FOUND) THEN
      RAISE EXCEPTION 'Bill of Materials has revision control records and may not be deleted.';
    END IF;
  END IF;

  DELETE FROM bomhead
  WHERE (bomhead_item_id=pItemid);
  DELETE FROM bomitem
  WHERE (bomitem_parent_item_id=pItemid);

  RETURN 0;

END;
$_$;


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

--
-- TOC entry 1405 (class 1255 OID 146565973)
-- Dependencies: 4536 8
-- Name: deletebomworkset(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

--  All done with the bomwork set indicated by pWorksetid, delete all of it
  DELETE FROM bomwork
  WHERE (bomwork_set_id=pWorksetid);

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 1406 (class 1255 OID 146565974)
-- Dependencies: 4536 8
-- Name: deletebudget(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  DELETE FROM budgitem WHERE (budgitem_budghead_id=pBudgheadid);
  DELETE FROM budghead WHERE (budghead_id=pBudgheadid);

  RETURN pBudgheadid;
END;
$_$;


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

--
-- TOC entry 1407 (class 1255 OID 146565975)
-- Dependencies: 4536 8
-- Name: deletebudgetitems(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  DELETE FROM budgitem WHERE (budgitem_budghead_id=pBudgheadid);

  RETURN pBudgheadid;
END;
$_$;


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

--
-- TOC entry 1409 (class 1255 OID 146565976)
-- Dependencies: 4536 8
-- Name: deletecashrcpt(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  IF EXISTS(SELECT cashrcpt_id
            FROM cashrcpt
            JOIN ccpay ON (cashrcpt_cust_id=ccpay_cust_id)
                       AND ((CASE WHEN TRIM(COALESCE(cashrcpt_docnumber, ''))='' THEN TEXT(cashrcpt_id)
                                  ELSE cashrcpt_docnumber
                             END)=ccpay_order_number)
            WHERE ((cashrcpt_fundstype IN ('A', 'D', 'M', 'V'))
               AND (ccpay_status NOT IN ('D', 'X'))
               AND (ccpay_id NOT IN (SELECT payco_ccpay_id FROM payco))
               AND (cashrcpt_id=pcashrcptid))) THEN
    RETURN -1;
  END IF;

  IF EXISTS(SELECT cashrcpt_id
            FROM cashrcpt
            WHERE ( (cashrcpt_id=pcashrcptid)
              AND   (cashrcpt_posted) )) THEN
    RETURN -2;
  END IF;

  -- If there are applications for this Cash Receipt then
  -- it has been posted and reversed.  Void instead of delete.
  IF EXISTS(SELECT cashrcpt_id
            FROM cashrcpt JOIN cashrcptitem ON (cashrcptitem_cashrcpt_id=cashrcpt_id)
                          JOIN arapply ON ((arapply_reftype='CRA') AND
                                           (arapply_ref_id=cashrcptitem_id))
            WHERE (cashrcpt_id=pcashrcptid))
     OR
     EXISTS(SELECT cashrcpt_id
            FROM cashrcpt JOIN cashrcptmisc ON (cashrcptmisc_cashrcpt_id=cashrcpt_id)
                          JOIN arapply ON ((arapply_reftype='CRD') AND
                                           (arapply_ref_id=cashrcptmisc_id))
            WHERE (cashrcpt_id=pcashrcptid)) THEN
    UPDATE cashrcpt SET cashrcpt_void = TRUE
    WHERE (cashrcpt_id=pcashrcptid);
    RETURN 1;
  END IF;

  DELETE FROM cashrcptitem
  WHERE (cashrcptitem_cashrcpt_id=pcashrcptid);

  DELETE FROM cashrcptmisc
  WHERE (cashrcptmisc_cashrcpt_id=pcashrcptid);

  DELETE FROM cashrcpt
  WHERE (cashrcpt_id=pcashrcptid);

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 1410 (class 1255 OID 146565977)
-- Dependencies: 4536 8
-- Name: deletecharacteristic(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deletecharacteristic(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
  pCharid ALIAS FOR $1;
  _check INTEGER;
  _r RECORD;

BEGIN

--  Cache the specifics of the characteristic
  SELECT * INTO _r
  FROM char
  WHERE (char_id=pCharid);
  IF (NOT(FOUND)) THEN
    RETURN 0;
  END IF;

--  If the passed characteristic is used
  SELECT * INTO _r
  FROM charass
  WHERE (charass_char_id=pCharid)
  LIMIT 1;
  IF (FOUND) THEN
    IF (_r.charass_target_type = 'I') THEN
      RETURN -1;
    ELSIF (_r.charass_target_type = 'C') THEN
      RETURN -2;
    ELSIF (_r.charass_target_type = 'ADDR') THEN
      RETURN -3;
    ELSIF (_r.charass_target_type = 'CNTCT') THEN
      RETURN -4;
    ELSIF (_r.charass_target_type = 'CRMACCT') THEN
      RETURN -5;
    ELSIF (_r.charass_target_type = 'INCDT	') THEN
      RETURN -6;
    ELSIF (_r.charass_target_type = 'EMP') THEN
      RETURN -7;
    ELSIF (_r.charass_target_type = 'QU') THEN
      RETURN -8;
    ELSIF (_r.charass_target_type = 'SO') THEN
      RETURN -9;
    ELSIF (_r.charass_target_type = 'INV') THEN
      RETURN -10;
    ELSIF (_r.charass_target_type = 'V') THEN
      RETURN -11;
    ELSIF (_r.charass_target_type = 'PO') THEN
      RETURN -12;
    ELSIF (_r.charass_target_type = 'VO') THEN
      RETURN -13;
    ELSE
      RETURN -99;
    END IF;
  END IF;

--  Delete the passed characterisitic
  DELETE FROM char
  WHERE (char_id=pCharid);

  RETURN pCharid;

END;
$_$;


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

--
-- TOC entry 1411 (class 1255 OID 146565978)
-- Dependencies: 4536 8
-- Name: deletecheck(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  IF (SELECT (NOT checkhead_void) OR checkhead_posted OR checkhead_replaced
              OR checkhead_deleted
              OR (checkhead_ach_batch IS NOT NULL AND checkhead_printed)
      FROM checkhead
      WHERE (checkhead_id=pCheckid) ) THEN
    RETURN -1;
  END IF;

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

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 1412 (class 1255 OID 146565979)
-- Dependencies: 4536 8
-- Name: deleteclasscode(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deleteclasscode(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
  pClasscodeid ALIAS FOR $1;
  _check INTEGER;

BEGIN

--  Check to see if any items are assigned to the passed classcode
  SELECT item_id INTO _check
  FROM item
  WHERE (item_classcode_id=pClasscodeid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

--  Delete the passed classcode
  DELETE FROM classcode
  WHERE (classcode_id=pClasscodeid);

  RETURN pClasscodeid;

END;
$_$;


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

--
-- TOC entry 1413 (class 1255 OID 146565980)
-- Dependencies: 4536 8
-- Name: deletecompany(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  IF (EXISTS(SELECT accnt_id
             FROM accnt, company
             WHERE ((accnt_company=company_number)
               AND  (company_id=pcompanyid))
            )) THEN
    RETURN -1;
  END IF;

  DELETE FROM company
  WHERE (company_id=pcompanyid);

  RETURN pcompanyid;

END;
$_$;


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

--
-- TOC entry 1414 (class 1255 OID 146565981)
-- Dependencies: 4536 8
-- Name: deletecreditmemo(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  DELETE FROM cmitem
  WHERE (cmitem_cmhead_id=pCmheadid);

  DELETE FROM cmhead
  WHERE (cmhead_id=pCmheadid);

  RETURN TRUE;

END;
$_$;


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

--
-- TOC entry 1415 (class 1255 OID 146565982)
-- Dependencies: 4536 8
-- Name: deletecustomer(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deletecustomer(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

  PERFORM shipto_id
  FROM shiptoinfo
  WHERE (shipto_cust_id=pCustid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

  PERFORM cohead_id
  FROM cohead
  WHERE (cohead_cust_id=pCustid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -2;
  END IF;

  PERFORM cmhead_id
  FROM cmhead
  WHERE (cmhead_cust_id=pCustid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -3;
  END IF;

  PERFORM cohist_id
  FROM cohist
  WHERE (cohist_cust_id=pCustid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -4;
  END IF;

  PERFORM aropen_id
  FROM aropen
  WHERE (aropen_cust_id=pCustid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -5;
  END IF;

  PERFORM checkhead_recip_id
    FROM checkhead
   WHERE ((checkhead_recip_id=pCustid)
     AND  (checkhead_recip_type='C'))
   LIMIT 1;
   IF (FOUND) THEN
     RETURN -6;
   END IF;

  PERFORM invchead_id
     FROM invchead
    WHERE(invchead_cust_id=pCustid)
    LIMIT 1;
  IF (FOUND) THEN
    RETURN -7;
  END IF;

  PERFORM quhead_id
     FROM quhead
    WHERE(quhead_cust_id=pCustid)
    LIMIT 1;
  IF (FOUND) THEN
    RETURN -8;
  END IF;

  DELETE FROM taxreg
   WHERE ((taxreg_rel_type='C')
     AND  (taxreg_rel_id=pCustid));

  DELETE FROM ipsass
  WHERE (ipsass_cust_id=pCustid);

  DELETE FROM custinfo
  WHERE (cust_id=pCustid);

  UPDATE crmacct SET crmacct_cust_id = NULL
  WHERE (crmacct_cust_id=pCustid);

  RETURN 0;

END;
$_$;


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

--
-- TOC entry 1416 (class 1255 OID 146565983)
-- Dependencies: 4536 8
-- Name: deletecustomertype(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  IF EXISTS(SELECT 1
              FROM custinfo
             WHERE (cust_custtype_id=pCusttypeid)) THEN
    RETURN -1;
  END IF;

  DELETE FROM ipsass
  WHERE (ipsass_custtype_id=pCusttypeid);

  DELETE FROM salesaccnt
  WHERE (salesaccnt_custtype_id=pCusttypeid);

  DELETE FROM araccnt
  WHERE (araccnt_custtype_id=pCusttypeid);

  DELETE FROM custform
  WHERE (custform_custtype_id=pCusttypeid);

  DELETE FROM custtype
  WHERE (custtype_id=pCusttypeid);

  RETURN pCusttypeid;

END;
$_$;


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

--
-- TOC entry 1305 (class 1255 OID 146565984)
-- Dependencies: 4536 8
-- Name: deleteempgrp(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
--  Check to see if any employees are assigned to the passed empgrp
  PERFORM empgrpitem_emp_id
  FROM empgrpitem
  WHERE (empgrpitem_empgrp_id=pempgrpid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

  DELETE FROM empgrp     WHERE (empgrp_id=pempgrpid);

  RETURN 0;
END;
$_$;


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

--
-- TOC entry 1386 (class 1255 OID 146565985)
-- Dependencies: 4536 8
-- Name: deleteexpiredips(); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  FOR _r IN SELECT ipshead_id
    FROM ipshead
    WHERE (ipshead_expires <= current_date)
  LOOP

    DELETE FROM ipsass
      WHERE (ipsass_ipshead_id=_r.ipshead_id);
    DELETE FROM ipsiteminfo
      WHERE (ipsitem_ipshead_id=_r.ipshead_id);
    DELETE FROM ipsfreight
      WHERE (ipsfreight_ipshead_id=_r.ipshead_id);
    DELETE FROM ipshead
      WHERE (ipshead_id=_r.ipshead_id);
  END LOOP;

  RETURN TRUE;

END;
$$;


ALTER FUNCTION public.deleteexpiredips() OWNER TO admin;

--
-- TOC entry 1397 (class 1255 OID 146565986)
-- Dependencies: 4536 8
-- Name: deletefile(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deletefile(integer) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
declare
  pId ALIAS FOR $1;
begin
  delete from file
  where ( file_id in (
    select file_id
    from file
      join docass on (docass_target_id=file_id)
                 and (docass_target_type='FILE')
    where ( docass_id = pId ) ) );

  delete from docass where docass_id = pId;

  return true;
end;
$_$;


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

--
-- TOC entry 1417 (class 1255 OID 146565987)
-- Dependencies: 4536 8
-- Name: deleteflgrp(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  FOR _r IN SELECT flgrp_id
            FROM flgrp
            WHERE (flgrp_flgrp_id=pFlgrpid) LOOP
    PERFORM deleteFlgrp(_r.flgrp_id);
  END LOOP;

  DELETE FROM flitem
  WHERE (flitem_flgrp_id=pFlgrpid);

  DELETE FROM flspec
  WHERE (flspec_flgrp_id=pFlgrpid);

  DELETE FROM flgrp
  WHERE (flgrp_id=pFlgrpid);

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 1418 (class 1255 OID 146565988)
-- Dependencies: 4536 8
-- Name: deleteform(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deleteform(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
  pFormid ALIAS FOR $1;
  _key TEXT;
  _check INTEGER;

BEGIN

--  Cache the key of the passed form
  SELECT form_key INTO _key
  FROM form
  WHERE (form_id=pFormid);
  IF (NOT(FOUND)) THEN
    RETURN 0;
  END IF;

--  Handle checks based on the type of the form
  IF (_key='Chck') THEN
    SELECT bankaccnt_id INTO _check
    FROM bankaccnt
    WHERE (bankaccnt_check_form_id=pFormid)
    LIMIT 1;
    IF (FOUND) THEN
      RETURN -1;
    END IF;

  END IF;

--  Delete the form
  DELETE FROM form
  WHERE (form_id=pFormid);

  RETURN pFormid;

END;
$_$;


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

--
-- TOC entry 1419 (class 1255 OID 146565989)
-- Dependencies: 4536 8
-- Name: deletefreightclass(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deletefreightclass(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
  pFreightClassid ALIAS FOR $1;
  _check INTEGER;

BEGIN

--  Check to see if any items are assigned to the passed freightclass
  SELECT item_id INTO _check
  FROM item
  WHERE (item_freightclass_id=pFreightClassid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

--  Delete the passed freightclass
  DELETE FROM freightclass
  WHERE (freightclass_id=pFreightClassid);

  RETURN pFreightClassid;

END;
$_$;


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

--
-- TOC entry 1420 (class 1255 OID 146565990)
-- Dependencies: 4536 8
-- Name: deleteglseries(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deleteglseries(integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
  pSequence ALIAS FOR $1;

BEGIN

  DELETE FROM glseries
  WHERE (glseries_sequence=pSequence);

  RETURN pSequence;

END;
$_$;


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

--
-- TOC entry 1421 (class 1255 OID 146565991)
-- Dependencies: 4536 8
-- Name: deleteglseries(integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

--  March through all of the G/L Transactions for the passed sequence
  FOR _r IN SELECT gltrans_id, gltrans_date, gltrans_accnt_id, gltrans_amount, gltrans_posted, gltrans_rec,
                   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_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')) ) AND
       (  NOT _r.gltrans_rec) AND
       ( _r.gltrans_posted ) ) 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) );

      GET DIAGNOSTICS _count = ROW_COUNT;
      IF (_count > 0) 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
        RAISE EXCEPTION 'Can not delete G/L Series.  Trial balance record not found.';
      END IF;

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

--  Delete any bank reconciliation records if this was marked cleared but non reconciled
    DELETE FROM bankrecitem
    WHERE ((bankrecitem_source='GL')
    AND (bankrecitem_source_id=_r.gltrans_id));

--  Unflag any journals as posted as a result of this series
    UPDATE sltrans SET
      sltrans_posted=false,
      sltrans_gltrans_journalnumber=null
    FROM gltrans
    WHERE ((gltrans_sequence=pSequence)
    AND (sltrans_gltrans_journalnumber=gltrans_journalnumber));
    
--  Mark the G/L Transaction as deleted
      UPDATE gltrans SET
        gltrans_posted=false,
        gltrans_deleted=true,
        gltrans_notes=gltrans_notes || E'\n' || pNotes
      WHERE (gltrans_id=_r.gltrans_id);

    ELSIF (_r.period_freeze) THEN
        RAISE EXCEPTION 'Can not delete a G/L Transaction in a frozen period';
    ELSIF (_r.period_closed) THEN
        RAISE EXCEPTION 'Can not delete a G/L Transaction on account % in a closed period', formatGlAccount(_r.gltrans_accnt_id);
    ELSIF (_r.gltrans_rec) THEN
        RAISE EXCEPTION 'Can not delete a G/L Transaction that has been reconciled';
    ELSIF (NOT _r.gltrans_posted) THEN
        RAISE EXCEPTION 'Can not delete a G/L Transaction that has not been posted to Trial Balance';
    END IF;

  END LOOP;

  RETURN true;

END;
$_$;


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

--
-- TOC entry 1422 (class 1255 OID 146565992)
-- Dependencies: 4536 8
-- Name: deleteincident(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deleteincident(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
  pincdtid    ALIAS FOR $1;
  _count      INTEGER := 0;
  _incdtnbr   INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO _count
  FROM todoitem
  WHERE (todoitem_incdt_id=pincdtid);
  IF (_count > 0) THEN
    RETURN -1;
  END IF;

  DELETE FROM comment
   WHERE((comment_source='INCDT')
     AND (comment_source_id=pincdtid));

  DELETE FROM incdthist
   WHERE (incdthist_incdt_id=pincdtid);

  DELETE FROM imageass
  WHERE ((imageass_source='INCDT')
     AND (imageass_source_id=pincdtid));

  DELETE FROM url
  WHERE ((url_source='INCDT')
     AND (url_source_id=pincdtid));

  SELECT incdt_number INTO _incdtnbr
  FROM incdt
  WHERE (incdt_id=pincdtid);

  DELETE FROM incdt
    WHERE (incdt_id=pincdtid);

-- Incident #11538 needs to be fully resolved before release can be implemented
--    PERFORM releaseIncidentNumber(_incdtnbr);

  RETURN 0;
END;
$_$;


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

--
-- TOC entry 1423 (class 1255 OID 146565993)
-- Dependencies: 4536 8
-- Name: deleteinvoice(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  UPDATE shipitem SET shipitem_invoiced=FALSE, shipitem_invcitem_id=NULL
  FROM invcitem
  WHERE ((shipitem_invoiced)
    AND  (shipitem_invcitem_id=invcitem_id)
    AND  (invcitem_invchead_id=pInvcheadid));

  UPDATE coitem SET coitem_status = 'O'
  WHERE ((coitem_status = 'C')
    AND  (coitem_id IN (SELECT cobill_coitem_id
		        FROM cobill, invcitem
			WHERE ((cobill_invcitem_id=invcitem_id)
			  AND  (invcitem_invchead_id=pInvcheadid)))));

  UPDATE cobill SET cobill_invcnum=NULL, cobill_invcitem_id=NULL
  FROM invcitem
  WHERE ((cobill_invcitem_id=invcitem_id)
    AND  (invcitem_invchead_id=pInvcheadid));

  UPDATE invdetail SET invdetail_invcitem_id=NULL
  FROM invcitem
  WHERE ((invdetail_invcitem_id=invcitem_id)
    AND  (invcitem_invchead_id=pInvcheadid));

  UPDATE cobmisc SET cobmisc_invcnumber=NULL, cobmisc_invchead_id=NULL,
		     cobmisc_posted=FALSE
  WHERE (cobmisc_invchead_id=pInvcheadid);

  DELETE FROM aropenalloc
  WHERE (aropenalloc_doctype='I')
    AND (aropenalloc_doc_id=pInvcheadid);

  DELETE FROM charass
  WHERE (charass_target_type='INV')
    AND (charass_target_id=pInvcheadid);

  DELETE FROM invcitem
  WHERE (invcitem_invchead_id=pInvcheadid);

  DELETE FROM invchead
  WHERE (invchead_id=pInvcheadid);

  RETURN pInvcheadid;

END;
$_$;


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

--
-- TOC entry 1424 (class 1255 OID 146565994)
-- Dependencies: 4536 8
-- Name: deleteipsitem(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deleteipsitem(pipsitemid 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

BEGIN

  DELETE FROM ipsiteminfo WHERE ipsitem_id=pIpsItemId;

  RETURN 1;
END;
$$;


ALTER FUNCTION public.deleteipsitem(pipsitemid integer) OWNER TO admin;

--
-- TOC entry 1425 (class 1255 OID 146565995)
-- Dependencies: 4536 8
-- Name: deleteipsprodcat(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deleteipsprodcat(pipsitemid 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

BEGIN

  DELETE FROM ipsiteminfo WHERE ipsitem_id=pIpsItemId;
  
  RETURN 1;
END;
$$;


ALTER FUNCTION public.deleteipsprodcat(pipsitemid integer) OWNER TO admin;

--
-- TOC entry 1426 (class 1255 OID 146565996)
-- Dependencies: 4536 8
-- Name: deleteitem(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT bomitem_id INTO _result
  FROM bomitem
  WHERE (bomitem_item_id=pItemid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

  SELECT itemsite_id INTO _result
  FROM itemsite
  WHERE (itemsite_item_id=pItemid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -2;
  END IF;

  SELECT itemsub_id INTO _result
  FROM itemsub
  WHERE (itemsub_sub_item_id=pItemid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -3;
  END IF;

  IF (fetchmetricbool('RevControl')) THEN
    SELECT rev_id INTO _result
    FROM rev
    WHERE ((rev_target_id=pItemid)
    AND (rev_target_type = 'BOM'))
    LIMIT 1;
    IF (FOUND) THEN
      RETURN -6;
    END IF;
  END IF;

  DELETE FROM bomhead
  WHERE (bomhead_item_id=pItemid);
  DELETE FROM bomitem
  WHERE (bomitem_item_id=pItemid);

  DELETE FROM itemcost
  WHERE (itemcost_item_id=pItemid);
  DELETE FROM costhist
  WHERE (costhist_item_id=pItemid);

  DELETE FROM itemsub
  WHERE (itemsub_parent_item_id=pItemid);
  DELETE FROM itemsub
  WHERE (itemsub_sub_item_id=pItemid);

  DELETE FROM itemsrcp
  WHERE (itemsrcp_itemsrc_id IN (SELECT itemsrc_id FROM itemsrc WHERE (itemsrc_item_id=pItemid)));
  DELETE FROM itemsrc
  WHERE (itemsrc_item_id=pItemid);

  DELETE FROM itemalias
  WHERE (itemalias_item_id=pItemid);

  DELETE FROM itemgrpitem
  WHERE (itemgrpitem_item_id=pItemid);

  DELETE FROM ipsiteminfo
  WHERE (ipsitem_item_id=pItemid);

  DELETE FROM imageass
  WHERE ( (imageass_source='I')
    AND   (imageass_source_id=pItemid) );

  DELETE FROM locitem
  WHERE (locitem_item_id=pItemid);

  DELETE FROM itemtax
   WHERE(itemtax_item_id=pItemid);

  DELETE FROM itemsite
  WHERE (itemsite_item_id=pItemid);

  DELETE FROM itemuom
   WHERE(itemuom_itemuomconv_id IN (SELECT itemuomconv_id
                                      FROM itemuomconv
                                     WHERE(itemuomconv_item_id=pItemid)));

  DELETE FROM itemuomconv
   WHERE(itemuomconv_item_id=pItemid);

  DELETE FROM item
  WHERE (item_id=pItemid);

  RETURN 0;

END;
$_$;


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

--
-- TOC entry 1427 (class 1255 OID 146565997)
-- Dependencies: 4536 8
-- Name: deleteitemcost(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deleteitemcost(integer, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pItemId ALIAS FOR $1;
  pCostElemId ALIAS FOR $2;
  _itemcost_id INTEGER;
  _postcost_return BOOLEAN;
  _std_cost NUMERIC;

BEGIN
  SELECT itemcost_id INTO _itemcost_id
  FROM itemcost
  WHERE ( (itemcost_item_id = pItemId) AND (itemcost_costelem_id = pCostElemId) );

  IF (NOT FOUND) THEN
	RAISE EXCEPTION 'itemcost % not found for. ', pItemId || ' & ' || pCostElemId;
  END IF;

  SELECT itemcost_stdcost INTO _std_cost
  FROM itemcost
  WHERE (itemcost_id = _itemcost_id);

  IF (_std_cost > 0) THEN
--Actual Cost is updated to zero to ensure inventory is valued correctly
    PERFORM updateCost(_itemcost_id, 0);
  END IF;

  DELETE FROM itemcost
  WHERE (itemcost_id=_itemcost_id);
 
  RETURN _itemcost_id;	

END;
$_$;


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

--
-- TOC entry 1428 (class 1255 OID 146565998)
-- Dependencies: 4536 8
-- Name: deleteitemsite(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  IF ( ( SELECT ( (itemsite_qtyonhand <> 0) OR (itemsite_nnqoh <> 0) )
         FROM itemsite
         WHERE (itemsite_id=pItemsiteid) ) ) THEN
    RETURN -9;
  END IF;

  SELECT metric_value='t' INTO _bbom
    FROM metric
   WHERE (metric_name='BBOM');

  SELECT metric_value='t' INTO _lotserial
    FROM metric
   WHERE (metric_name='LotSerialControl');

  SELECT metric_value NOT IN ('PostBooks', 'Standard') INTO _mfg
    FROM metric
   WHERE (metric_name='Application');

  SELECT metric_value='Standard' INTO _standard
    FROM metric
   WHERE (metric_name='Application');

  SELECT invhist_id INTO _result
  FROM invhist
  WHERE (invhist_itemsite_id=pItemsiteid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

  IF (_lotserial) THEN
    SELECT lsdetail_id INTO _result
    FROM lsdetail
    WHERE (lsdetail_itemsite_id=pItemsiteid)
    LIMIT 1;
    IF (FOUND) THEN
      RETURN -1;
    END IF;
  END IF;

  SELECT wo_id INTO _result
  FROM wo
  WHERE (wo_itemsite_id=pItemsiteid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -2;
  END IF;

  SELECT womatl_id INTO _result
  FROM womatl
  WHERE (womatl_itemsite_id=pItemsiteid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -2;
  END IF;

  SELECT womatlvar_id INTO _result
  FROM womatlvar
  WHERE ( (womatlvar_parent_itemsite_id=pItemsiteid)
   OR (womatlvar_component_itemsite_id=pItemsiteid) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -2;
  END IF;

  IF (_bbom) THEN
    SELECT brdvar_id INTO _result
    FROM xtmfg.brdvar
    WHERE ( (brdvar_itemsite_id=pItemsiteid)
     OR (brdvar_parent_itemsite_id=pItemsiteid) )
    LIMIT 1;
    IF (FOUND) THEN
      RETURN -2;
    END IF;
  END IF;

  SELECT coitem_id INTO _result
  FROM coitem
  WHERE (coitem_itemsite_id=pItemsiteid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -3;
  END IF;

  SELECT cohist_id INTO _result
  FROM cohist
  WHERE (cohist_itemsite_id=pItemsiteid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -3;
  END IF;

  SELECT quitem_id INTO _result
  FROM quitem
  WHERE (quitem_itemsite_id=pItemsiteid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -3;
  END IF;

  SELECT cmitem_id INTO _result
  FROM cmitem
  WHERE (cmitem_itemsite_id=pItemsiteid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -3;
  END IF;


  SELECT poitem_id INTO _result
  FROM poitem
  WHERE (poitem_itemsite_id=pItemsiteid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -4;
  END IF;

  SELECT recv_id INTO _result
  FROM recv
  WHERE (recv_itemsite_id=pItemsiteid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -4;
  END IF;

  SELECT poreject_id INTO _result
  FROM poreject
  WHERE (poreject_itemsite_id=pItemsiteid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -4;
  END IF;

  SELECT pr_id INTO _result
  FROM pr
  WHERE (pr_itemsite_id=pItemsiteid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -4;
  END IF;

  IF (_mfg OR _standard) THEN
    SELECT planord_id INTO _result
    FROM planord
    WHERE (planord_itemsite_id=pItemsiteid)
    LIMIT 1;
    IF (FOUND) THEN
      RETURN -5;
    END IF;
  END IF;

  IF (_mfg) THEN
    SELECT pschitem_id INTO _result
    FROM xtmfg.pschitem
    WHERE (pschitem_itemsite_id=pItemsiteid)
    LIMIT 1;
    IF (FOUND) THEN
      RETURN -6;
    END IF;

    SELECT woopervar_id INTO _result
    FROM xtmfg.woopervar
    WHERE (woopervar_parent_itemsite_id=pItemsiteid)
    LIMIT 1;
    IF (FOUND) THEN
      RETURN -2;
    END IF;
    
  END IF;

  IF (_mfg OR _standard) THEN
    SELECT itemsite_id INTO _result
    FROM itemsite
    WHERE (itemsite_supply_itemsite_id=pItemsiteid)
    LIMIT 1;
    IF (FOUND) THEN
      RETURN -7;
    END IF;
  END IF;

  DELETE FROM invcnt
  WHERE (invcnt_itemsite_id=pItemsiteid);

  DELETE FROM itemloc
  WHERE (itemloc_itemsite_id=pItemsiteid);
  DELETE FROM itemlocdist
  WHERE (itemlocdist_itemsite_id=pItemsiteid);

  IF (_bbom) THEN
    DELETE FROM xtmfg.brddist
    WHERE (brddist_itemsite_id=pItemsiteid);
  END IF;

  DELETE FROM itemsite
  WHERE (itemsite_id=pItemsiteid);

  RETURN 0;

END;
$_$;


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

--
-- TOC entry 1429 (class 1255 OID 146565999)
-- Dependencies: 4536 8
-- Name: deleteitemuom(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  DELETE FROM itemuom WHERE itemuom_id=pItemuomid;

  RETURN 0;
END;
$_$;


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

--
-- TOC entry 1430 (class 1255 OID 146566000)
-- Dependencies: 4536 8
-- Name: deleteitemuomconv(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deleteitemuomconv(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
  pItemuomconvid ALIAS FOR $1;
  _fromuomid     INTEGER;
  _invuomid      INTEGER;
  _itemid        INTEGER;
  _touomid       INTEGER;

BEGIN
  SELECT itemuomconv_item_id, item_inv_uom_id,
         itemuomconv_from_uom_id, itemuomconv_to_uom_id
          INTO _itemid, _invuomid, _fromuomid, _touomid
  FROM itemuomconv JOIN item ON (itemuomconv_item_id=item_id)
  WHERE (itemuomconv_id=pItemuomconvid);

  IF EXISTS(SELECT *
            FROM uomusedforitem(_itemid)
            WHERE ((uom_id IN (_fromuomid, _touomid))
               AND (uom_id != _invuomid)) ) THEN
    RETURN -1;
  END IF;

  DELETE FROM itemuom WHERE itemuom_itemuomconv_id=pItemuomconvid;
  DELETE FROM itemuomconv WHERE itemuomconv_id=pItemuomconvid;

  RETURN 0;
END;
$_$;


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

--
-- TOC entry 1431 (class 1255 OID 146566001)
-- Dependencies: 4536 8
-- Name: deletelocation(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deletelocation(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
  pLocationid ALIAS FOR $1;
  _check INTEGER;

BEGIN

--  Check to see if any itemsite used the passed location as their default
  SELECT itemsite_id INTO _check
  FROM itemsite
  WHERE (itemsite_location_id=pLocationid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

--  Check to see if any inventory is currently stored at the passed location
  SELECT itemloc_id INTO _check
  FROM itemloc
  WHERE (itemloc_location_id=pLocationid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -2;
  END IF;

--  Check to see if any undistributed inventory transactions are currently posted at the passed location
  SELECT itemlocdist_id INTO _check
  FROM itemlocdist
  WHERE ( (itemlocdist_source_type='L')
  AND (itemlocdist_source_id=pLocationid) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -3;
  END IF;

--  Check to see if the passed location has any Inventory Detail posted against it
  SELECT invdetail_id INTO _check
  FROM invdetail
  WHERE (invdetail_location_id=pLocationid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -4;
  END IF;

--  Delete any associated locitem records
  DELETE FROM locitem
  WHERE (locitem_location_id=pLocationid);

--  Delete the location record
  DELETE FROM location
  WHERE (location_id=pLocationid);

  RETURN pLocationid;

END;
$_$;


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

--
-- TOC entry 1432 (class 1255 OID 146566002)
-- Dependencies: 4536 8
-- Name: deletemetasql(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deletemetasql(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
  pid ALIAS FOR $1;
BEGIN
  DELETE FROM metasql WHERE metasql_id = pid;
  RETURN 0;
END;
$_$;


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

--
-- TOC entry 1435 (class 1255 OID 146566003)
-- Dependencies: 4536 8
-- Name: deleteopenrecurringitems(integer, text, timestamp with time zone, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deleteopenrecurringitems(integer, text, timestamp with time zone, 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
  pParentid     ALIAS FOR $1;
  pType         TEXT                     := UPPER($2);
  pDatetime     TIMESTAMP WITH TIME ZONE := COALESCE($3, startOfTime());
  pInclParent   BOOLEAN                  := COALESCE($4, FALSE);

  _count         INTEGER := 0;
  _delchildstmt  TEXT;
  _delparentstmt TEXT;
  _rt            RECORD;
  _tmp           INTEGER;
BEGIN
  RAISE DEBUG 'deleteOpenRecurringItems(%, %, %)', pParentid, pType, pDatetime;

  IF (pParentid IS NULL) THEN
    RETURN -11;
  END IF;

  SELECT * INTO _rt FROM recurtype WHERE (UPPER(recurtype_type)=pType);
  GET DIAGNOSTICS _count = ROW_COUNT;
  IF (_count <= 0) THEN
    RETURN -10;
  END IF;

  -- 2 deletes avoid reparenting problems if the parent gets deleted first
  IF (_rt.recurtype_delfunc IS NULL) THEN
    _delchildstmt := 'DELETE FROM [fulltable] '
                  || ' WHERE (NOT ([done])'
                  || '    AND ([schedcol]>''$2'')'
                  || '    AND ([table]_recurring_[table]_id=$1)'
                  || '    AND ([table]_id!=$1));';

    _delparentstmt := 'DELETE FROM [fulltable] USING recur'
                   || ' WHERE (NOT ([done])'
                   || '    AND ([schedcol]>''$2'')'
                   || '    AND ([table]_recurring_[table]_id=$1)'
                   || '    AND ([table]_id=$1));';

  ELSE
    _delchildstmt := 'SELECT [delfunc]([table]_id)'
                  || '  FROM [fulltable] '
                  || ' WHERE (NOT ([done])'
                  || '    AND ([schedcol]>''$2'')'
                  || '    AND ([table]_recurring_[table]_id=$1)'
                  || '    AND ([table]_id!=$1));';
    _delparentstmt := 'SELECT [delfunc]([table]_id)'
                   || '  FROM [fulltable] '
                   || ' WHERE (NOT ([done])'
                   || '    AND ([schedcol]>''$2'')'
                   || '    AND ([table]_recurring_[table]_id=$1)'
                   || '    AND ([table]_id!=$1));';
    _delchildstmt  := REPLACE(_delchildstmt,  '[delfunc]', _rt.recurtype_delfunc);
    _delparentstmt := REPLACE(_delparentstmt, '[delfunc]', _rt.recurtype_delfunc);
  END IF;

  RAISE DEBUG '_delchildstmt has been set to %', _delchildstmt;

  _delchildstmt := REPLACE(_delchildstmt, '[fulltable]', _rt.recurtype_table);
  _delchildstmt := REPLACE(_delchildstmt, '[table]',
                            REGEXP_REPLACE(_rt.recurtype_table, E'.*\\.', ''));
  _delchildstmt := REPLACE(_delchildstmt, '[done]',  _rt.recurtype_donecheck);
  _delchildstmt := REPLACE(_delchildstmt, '[schedcol]', _rt.recurtype_schedcol);

  _delparentstmt := REPLACE(_delparentstmt, '[fulltable]', _rt.recurtype_table);
  _delparentstmt := REPLACE(_delparentstmt, '[table]',
                            REGEXP_REPLACE(_rt.recurtype_table, E'.*\\.', ''));
  _delparentstmt := REPLACE(_delparentstmt, '[done]',  _rt.recurtype_donecheck);
  _delparentstmt := REPLACE(_delparentstmt, '[schedcol]', _rt.recurtype_schedcol);

  RAISE DEBUG 'substitutions changed _delchildstmt to %', _delchildstmt;

  IF (_rt.recurtype_delfunc IS NULL) THEN
    -- 8.4+: EXECUTE _delchildstmt  USING pDatetime, pType;
    RAISE DEBUG '% with % and %', _delchildstmt, pType, pDatetime;
    EXECUTE REPLACE(REPLACE(_delchildstmt, '$1', pParentid::TEXT),
                                           '$2', pDatetime::TEXT);
    GET DIAGNOSTICS _count = ROW_COUNT;

    IF (pInclParent) THEN
      -- 8.4+: EXECUTE _delparentstmt USING pDatetime, pType;
      RAISE DEBUG '% with % and %', _delparentstmt, pType, pDatetime;
      EXECUTE REPLACE(REPLACE(_delparentstmt, '$1', pParentid::TEXT),
                                              '$2', pDatetime::TEXT);
      GET DIAGNOSTICS _tmp   = ROW_COUNT;
      _count := _count + _tmp;
    END IF;

  ELSE
    -- 8.4+: FOR _tmp IN EXECUTE _delchildstmt USING pDatetime, pType LOOP
    FOR _tmp IN EXECUTE REPLACE(REPLACE(_delchildstmt, '$1', pParentid::TEXT),
                                                       '$2', pDatetime::TEXT)
    LOOP
      IF _tmp < 0 THEN
        RETURN _tmp;
      END IF;
      _count := _count + 1;
    END LOOP;

    IF (pInclParent) THEN
      -- 8.4+: EXECUTE _delparentstmt INTO _tmp USING pDatetime, pType;
      EXECUTE REPLACE(REPLACE(_delparentstmt, '$1', pParentid::TEXT),
                                              '$2', pDatetime::TEXT) INTO _tmp;
      IF (_tmp < 0) THEN
        RETURN _tmp;
      END IF;
      _count := _count + 1;
    END IF;
  END IF;

  RAISE DEBUG 'deleteOpenrecurringItems() returning %', _count;
  RETURN _count;
END;
$_$;


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

--
-- TOC entry 1436 (class 1255 OID 146566004)
-- Dependencies: 4536 8
-- Name: deleteopportunity(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deleteopportunity(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
  pOpheadid ALIAS FOR $1;
  _test INTEGER;
BEGIN

  SELECT todoitem_id INTO _test
    FROM todoitem
   WHERE(todoitem_ophead_id=pOpheadid)
   LIMIT 1;
  IF(FOUND) THEN
    RETURN -1;
  END IF;

  SELECT quhead_id INTO _test
    FROM quhead
   WHERE(quhead_ophead_id=pOpheadid)
   LIMIT 1;
  IF(FOUND) THEN
    RETURN -2;
  END IF;

  SELECT cohead_id INTO _test
    FROM cohead
   WHERE(cohead_ophead_id=pOpheadid)
   LIMIT 1;
  IF(FOUND) THEN
    RETURN -3;
  END IF;

  DELETE
    FROM charass
   WHERE((charass_target_type='OPP')
     AND (charass_target_id=pOpheadid));

  DELETE
    FROM comment
   WHERE((comment_source='OPP')
     AND (comment_source_id=pOpheadid));

  DELETE
    FROM ophead
   WHERE(ophead_id=pOpheadid);
  
  return 0;
END;
$_$;


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

--
-- TOC entry 1437 (class 1255 OID 146566005)
-- Dependencies: 4536 8
-- Name: deletepackage(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deletepackage(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
  ppkgheadid    ALIAS FOR $1;
  _i            INTEGER := 0;
  _pkgname      TEXT;
  _r            RECORD;
  _tabs         TEXT[] := ARRAY['cmd',  'cmdarg', 'image',  'metasql',
                                'priv', 'report', 'script', 'uiform'];
  _debug        BOOL := false;

BEGIN
  IF (EXISTS(SELECT *
             FROM pkgdep
             WHERE (pkgdep_parent_pkghead_id=ppkgheadid))) THEN
    RETURN -1;
  END IF;

  SELECT pkghead_name INTO _pkgname
  FROM pkghead
  WHERE (pkghead_id=ppkgheadid);
  IF (NOT FOUND) THEN
    RETURN -2;
  END IF;

  IF (LOWER(_pkgname) = 'public' OR LOWER(_pkgname) = 'api') THEN
    RETURN -3;
  END IF;

  FOR _i IN ARRAY_LOWER(_tabs,1)..ARRAY_UPPER(_tabs,1) LOOP
    EXECUTE 'ALTER TABLE ' || _pkgname || '.pkg' || _tabs[_i] ||
            ' DISABLE TRIGGER pkg' || _tabs[_i] || 'altertrigger;';
  END LOOP;

  DELETE FROM pkghead WHERE pkghead_id=ppkgheadid;

  RETURN ppkgheadid;
END;
$_$;


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

--
-- TOC entry 1438 (class 1255 OID 146566006)
-- Dependencies: 4536 8
-- Name: deletepo(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

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

    -- Unlink from any Sales Orders
    UPDATE coitem SET coitem_order_type=NULL,
                      coitem_order_id=NULL
    FROM poitem 
    WHERE ( (coitem_order_type='P')
      AND   (coitem_order_id=poitem_id)
      AND   (poitem_pohead_id=pPoheadid) );

    DELETE FROM poitem
    WHERE (poitem_pohead_id=pPoheadid);

    DELETE FROM pohead
    WHERE (pohead_id=pPoheadid);

    RETURN TRUE;

  ELSE
    RETURN FALSE;
  END IF;

END;
$_$;


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

--
-- TOC entry 1439 (class 1255 OID 146566007)
-- Dependencies: 4536 8
-- Name: deletepoitem(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deletepoitem(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
  pPoitemid ALIAS FOR $1;
  _poheadid INTEGER := -1;
  _status CHARACTER;

BEGIN
  SELECT poitem_pohead_id, poitem_status INTO _poheadid, _status
  FROM poitem
  WHERE (poitem_id=pPoitemid);

  IF NOT(FOUND) THEN
    RETURN 0;
  END IF;

  IF ( _status = 'U' ) THEN
    DELETE FROM poitem
    WHERE (poitem_id=pPoitemid);
  ELSE   
    IF ( _status = 'O' ) THEN
      PERFORM recv_id
      FROM recv
      WHERE ( (recv_order_type='PO')
       AND (recv_orderitem_id=pPoitemid) );
      IF (FOUND) THEN
        RETURN -10;
      ELSE
        RETURN -20;
      END IF;
    ELSE
      RETURN -10;
    END IF;
  END IF;

  PERFORM poitem_id
  FROM poitem
  WHERE poitem_pohead_id = _poheadid;

  IF NOT(FOUND) THEN
    DELETE FROM pohead
    WHERE (pohead_id = _poheadid);
  END IF;
  
  RETURN 0;

END;
$_$;


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

--
-- TOC entry 1441 (class 1255 OID 146566009)
-- Dependencies: 4536 8
-- Name: deletepr(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  DELETE FROM pr
  WHERE ( (pr_status='O')
   AND (pr_id=pPrid) );

  RETURN TRUE;

END;
$_$;


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

--
-- TOC entry 1440 (class 1255 OID 146566008)
-- Dependencies: 4536 8
-- Name: deletepr(character, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deletepr(character, 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
  pParentType ALIAS FOR $1;
  pParentId ALIAS FOR $2;

BEGIN

  DELETE FROM pr
  WHERE ((pr_status='O')
   AND (pr_order_type=pParentType)
   AND (pr_order_id=pParentId));

  RETURN TRUE;

END;
$_$;


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

--
-- TOC entry 1442 (class 1255 OID 146566010)
-- Dependencies: 4536 8
-- Name: deleteproductcategory(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deleteproductcategory(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
  pProdcatid ALIAS FOR $1;
  _check INTEGER;

BEGIN

--  Check to see if any items are assigned to the passed classcode
  SELECT item_id INTO _check
  FROM item
  WHERE (item_prodcat_id=pProdcatid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

--  Delete any assocated records
  DELETE FROM salesaccnt
  WHERE (salesaccnt_prodcat_id=pProdcatid);

--  Delete the passed prodcat
  DELETE FROM prodcat
  WHERE (prodcat_id=pProdcatid);

  RETURN pProdcatid;

END;
$_$;


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

--
-- TOC entry 1408 (class 1255 OID 146566011)
-- Dependencies: 4536 8
-- Name: deleteprofitcenter(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  IF (EXISTS(SELECT accnt_id
             FROM accnt, prftcntr
             WHERE ((accnt_company=prftcntr_number)
               AND  (prftcntr_id=pid))
            )) THEN
    RETURN -1;
  END IF;

  DELETE FROM prftcntr
  WHERE (prftcntr_id=pid);

  RETURN pid;

END;
$_$;


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

--
-- TOC entry 1444 (class 1255 OID 146566012)
-- Dependencies: 4536 8
-- Name: deleteproject(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

  SELECT quhead_id INTO _result
    FROM quhead
   WHERE (quhead_prj_id=pPrjid)
   LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

  SELECT cohead_id INTO _result
    FROM cohead
   WHERE (cohead_prj_id=pPrjid)
   LIMIT 1;
  IF (FOUND) THEN
    RETURN -2;
  END IF;

  SELECT wo_id INTO _result
    FROM wo
   WHERE (wo_prj_id=pPrjid)
   LIMIT 1;
  IF (FOUND) THEN
    RETURN -3;
  END IF;

  SELECT pr_id INTO _result
    FROM pr
   WHERE (pr_prj_id=pPrjid)
   LIMIT 1;
  IF (FOUND) THEN
    RETURN -4;
  END IF;

  SELECT poitem_id INTO _result
    FROM poitem
   WHERE (poitem_prj_id=pPrjid)
   LIMIT 1;
  IF (FOUND) THEN
    RETURN -5;
  END IF;

  SELECT invchead_id INTO _result
    FROM invchead
   WHERE (invchead_prj_id=pPrjid)
   LIMIT 1;
  IF (FOUND) THEN
    RETURN -6;
  END IF;

  DELETE FROM comment
  WHERE ((comment_source='J')
  AND (comment_source_id=pPrjid));

  DELETE FROM comment
  WHERE ((comment_source='TA')
  AND (comment_source_id IN (
    SELECT prjtask_id
    FROM prjtask
    WHERE (prjtask_prj_id=pPrjId))));

  DELETE FROM prjtask
   WHERE (prjtask_prj_id=pPrjid);

  UPDATE prj
     SET prj_recurring_prj_id=null
   WHERE(prj_recurring_prj_id=pPrjid);

  DELETE FROM prj
   WHERE (prj_id=pPrjid);
  RETURN pPrjid;
END;
$_$;


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

--
-- TOC entry 1445 (class 1255 OID 146566013)
-- Dependencies: 4536 8
-- Name: deleteprojecttask(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deleteprojecttask(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
  pPrjtaskid ALIAS FOR $1;
  _row RECORD;
  _result INTEGER;
BEGIN

  SELECT * INTO _row
    FROM prjtask
   WHERE (prjtask_id=pPrjtaskid)
   LIMIT 1;
  IF (NOT FOUND) THEN
    RETURN -1;
  END IF;

  IF (COALESCE(_row.prjtask_hours_actual, 0.0) > 0.0) THEN
    RETURN -2;
  END IF;

  IF (COALESCE(_row.prjtask_exp_actual, 0.0) > 0.0) THEN
    RETURN -3;
  END IF;

  DELETE FROM comment
  WHERE ((comment_source='TA')
  AND (comment_source_id=pPrjtaskid));

  DELETE FROM prjtask
   WHERE (prjtask_id=pPrjtaskid);

  RETURN 0;

END;
$_$;


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

--
-- TOC entry 1446 (class 1255 OID 146566014)
-- Dependencies: 4536 8
-- Name: deleteqryhead(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  DELETE FROM qryitem WHERE (qryitem_qryhead_id=pqryheadid);
  DELETE FROM qryhead WHERE (qryhead_id=pqryheadid);

  RETURN pqryheadid;
END;
$_$;


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

--
-- TOC entry 1448 (class 1255 OID 146566016)
-- Dependencies: 4536 8
-- Name: deletequote(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1447 (class 1255 OID 146566015)
-- Dependencies: 4536 8
-- Name: deletequote(integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

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

  _quNumberScheme	TEXT;
  _quoteNumber		TEXT;
  _quitemid             INTEGER;
  _result               INTEGER;

BEGIN

  SELECT fetchMetricText('QUNumberGeneration') INTO _quNumberScheme;

  IF (pQuoteNumber IS NULL) THEN
    SELECT quhead_number INTO _quoteNumber
    FROM quhead
    WHERE (quhead_id=pQuheadid);
  ELSE
    _quoteNumber := pQuoteNumber;
  END IF;

  PERFORM deleteQuoteItem(quitem_id)
  FROM quitem
  WHERE (quitem_quhead_id=pQuheadid);

  DELETE FROM charass
  WHERE (charass_target_type='QU')
    AND (charass_target_id=pQuheadid);

  DELETE FROM quhead
  WHERE (quhead_id=pQuheadid);

  IF (_quoteNumber IS NOT NULL) THEN
    IF (_quNumberScheme IN ('A', 'O')) THEN
      -- do not release quote # if quote converted to sales order
      IF (NOT EXISTS (SELECT cohead_id
		      FROM cohead
		      WHERE (cohead_number=_quoteNumber))) THEN
	_result = releaseQuNumber(_quoteNumber);
      END IF;
    ELSEIF (_quNumberScheme = 'S') THEN
      _result = releaseSoNumber(_quoteNumber);
    END IF;
  END IF;

  -- Don't care about result of release number
  RETURN 0;

END;
$_$;


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

--
-- TOC entry 1449 (class 1255 OID 146566017)
-- Dependencies: 4536 8
-- Name: deletequote(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1450 (class 1255 OID 146566018)
-- Dependencies: 4536 8
-- Name: deletequoteitem(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deletequoteitem(integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
  pQuitemid ALIAS FOR $1;

  _result               INTEGER;

BEGIN

  DELETE FROM charass
  WHERE (charass_target_type='QI')
    AND (charass_target_id=pQuitemid);

  DELETE FROM quitem
  WHERE (quitem_id=pQuitemid);

  RETURN 0;

END;
$_$;


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

--
-- TOC entry 1451 (class 1255 OID 146566019)
-- Dependencies: 4536 8
-- Name: deleterecvfororder(text, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deleterecvfororder(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
  pordertype	ALIAS FOR $1;
  porderid	ALIAS FOR $2;

BEGIN
  DELETE FROM recv
  USING orderitem
  WHERE ((recv_orderitem_id=orderitem_id)
    AND  (recv_order_type=orderitem_orderhead_type)
    AND  (NOT recv_posted)
    AND  (orderitem_orderhead_id=porderid)
    AND  (orderitem_orderhead_type=pordertype));

  RETURN 0;

END;
$_$;


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

--
-- TOC entry 1452 (class 1255 OID 146566020)
-- Dependencies: 4536 8
-- Name: deletesalescategory(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  PERFORM invcitem_salescat_id
  FROM invchead, invcitem
  WHERE ( (invcitem_invchead_id=invchead_id)
   AND (NOT invchead_posted)
   AND (invcitem_salescat_id=pSalescatid) );
  IF (FOUND) THEN
    RETURN -1;
  END IF;

  PERFORM invcitem_salescat_id
  FROM invchead, invcitem
  WHERE ( (invcitem_invchead_id=invchead_id)
   AND (invchead_posted)
   AND (invcitem_salescat_id=pSalescatid) );
  IF (FOUND) THEN
    RETURN -2;
  END IF;

  PERFORM aropen_salescat_id
     FROM aropen
    WHERE (aropen_salescat_id=pSalescatid);
  IF (FOUND) THEN
    RETURN -3;
  END IF;

  DELETE FROM salescat
  WHERE (salescat_id=pSalescatid);

  RETURN 0;

END;
$_$;


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

--
-- TOC entry 1453 (class 1255 OID 146566021)
-- Dependencies: 4536 8
-- Name: deleteshippingcharge(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  IF EXISTS(SELECT 1
              FROM custinfo
             WHERE (cust_shipchrg_id=pShipchrgid)) THEN
    RETURN -1;
  END IF;

  DELETE FROM shipchrg
  WHERE (shipchrg_id=pShipchrgid);

  RETURN pShipchrgid;

END;
$_$;


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

--
-- TOC entry 1455 (class 1255 OID 146566022)
-- Dependencies: 4536 8
-- Name: deleteshippingchargetype(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deleteshippingchargetype(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
  pShipchrgid ALIAS FOR $1;
  _check INTEGER;

BEGIN

--  Check to see if the passed shipchrg is used as a default for any customers
  SELECT cust_id INTO _check
  FROM custinfo
  WHERE (cust_shipchrg_id=pShipchrgid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

--  Check to see if the passed shipchrg is used as a default for any shiptos
  SELECT shipto_id INTO _check
  FROM shiptoinfo
  WHERE (shipto_shipchrg_id=pShipchrgid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -2;
  END IF;

--  Check to see if the passed shipchrg is used on any sales orders
  SELECT cohead_id INTO _check
  FROM cohead
  WHERE (cohead_shipchrg_id=pShipchrgid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -3;
  END IF;

--  Check to see if the passed shipchrg is used on any shippers
  SELECT shiphead_id INTO _check
  FROM shiphead
  WHERE (shiphead_shipchrg_id=pShipchrgid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -4;
  END IF;

--  Check to see if the passed shipchrg is used on any invoices
  SELECT invchead_id INTO _check
  FROM invchead
  WHERE (invchead_shipchrg_id=pShipchrgid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -5;
  END IF;

--  Delete the passed shipchrg
  DELETE FROM shipchrg
  WHERE (shipchrg_id=pShipchrgid);

  RETURN pShipchrgid;

END;
$_$;


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

--
-- TOC entry 1456 (class 1255 OID 146566023)
-- Dependencies: 4536 8
-- Name: deleteshipto(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  PERFORM asohist_id
  FROM asohist
  WHERE (asohist_shipto_id=pShiptoid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

  PERFORM cohead_id
  FROM cohead
  WHERE (cohead_shipto_id=pShiptoid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -2;
  END IF;

  PERFORM cmhead_id
  FROM cmhead
  WHERE (cmhead_shipto_id=pShiptoid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -3;
  END IF;

  PERFORM cohist_id
  FROM cohist
  WHERE (cohist_shipto_id=pShiptoid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -4;
  END IF;

  PERFORM quhead_id
  FROM quhead
  WHERE (quhead_shipto_id=pShiptoid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -5;
  END IF;

  PERFORM invchead_id
  FROM invchead
  WHERE (invchead_shipto_id=pShiptoid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -6;
  END IF;

  DELETE FROM ipsass
  WHERE (ipsass_shipto_id=pShiptoid);

  DELETE FROM shiptoinfo
  WHERE (shipto_id=pShiptoid);

  RETURN 0;

END;
$_$;


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

--
-- TOC entry 1457 (class 1255 OID 146566024)
-- Dependencies: 4536 8
-- Name: deleteso(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1458 (class 1255 OID 146566025)
-- Dependencies: 4536 8
-- Name: deleteso(integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

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

  _r            RECORD;
  _coitemid     INTEGER;
  _result       INTEGER;
  _poStatus     INTEGER := 0;

BEGIN
-- Get cohead
  SELECT * INTO _r FROM cohead WHERE (cohead_id=pSoheadid);

   IF (NOT FOUND) THEN
     RETURN 0;
   END IF;

-- Cannot delete if credit card payments
  IF (EXISTS(SELECT ccpay_id
	     FROM ccpay, payco
	     WHERE ((ccpay_status IN ('C'))
	       AND  (ccpay_id=payco_ccpay_id)
	       AND  (payco_cohead_id=pSoheadid)))) THEN
    RETURN -1;
  END IF;

-- Cannot delete if credit card history
  IF (EXISTS(SELECT ccpay_id
	     FROM ccpay, payco
	     WHERE ((ccpay_status != 'C')
	       AND  (ccpay_id=payco_ccpay_id)
	       AND  (payco_cohead_id=pSoheadid)))) THEN
    RETURN -2;
  END IF;

-- Delete Sales Order Items
  FOR _coitemid IN
    SELECT coitem_id
    FROM coitem
    WHERE ( (coitem_cohead_id=pSoheadid)
      AND   (coitem_subnumber=0) ) LOOP

    SELECT deleteSoItem(_coitemid) INTO _result;
    IF (_result < 0) THEN
      IF (_result = -20) THEN
        _poStatus := _poStatus - 1;
      ELSE
        RETURN _result;
      END IF;
    END IF;

  END LOOP;

  DELETE FROM pack
  WHERE (pack_head_id=pSoheadid and pack_head_type = 'SO');

  DELETE FROM charass
  WHERE (charass_target_type='SO')
    AND (charass_target_id=pSoheadid);

  DELETE FROM cohead
  WHERE (cohead_id=pSoheadid);

  IF (fetchMetricBool('AutoCreateProjectsForOrders')) THEN
    PERFORM deleteProject(_r.cohead_prj_id);
  END IF;

  DELETE FROM aropenalloc
  WHERE ((aropenalloc_doctype='S')
    AND  (aropenalloc_doc_id=pSoheadid));

  IF ( (NOT _r.cohead_wasquote) AND (NOT _r.cohead_imported) ) THEN
    IF (COALESCE(pSonumber,'') != '') THEN
      _result = releaseSoNumber(pSonumber);
    ELSEIF (_r.cohead_number IS NOT NULL) THEN
      _result = releaseSoNumber(_r.cohead_number);
    END IF;
  END IF;

  IF (_poStatus < 0) THEN
    RETURN -20;
  ELSE
    RETURN 0;
  END IF;

END;
$_$;


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

--
-- TOC entry 1459 (class 1255 OID 146566026)
-- Dependencies: 4536 8
-- Name: deletesoitem(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

  _r            RECORD;
  _s            RECORD;
  _result       INTEGER;
  _deletePO     INTEGER := 0;
  _recvId       INTEGER := -1;
  _poStatus     TEXT;
  _jobItem      BOOLEAN;

BEGIN
-- Get coitem
   SELECT * INTO _r FROM coitem WHERE (coitem_id=pSoitemid);

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

-- Cannot delete if shipped
  IF (_r.coitem_qtyshipped > 0) THEN
    RETURN -101;
  END IF;

-- Cannot delete if issued to shipping
  SELECT shipitem_id INTO _result
  FROM shipitem JOIN shiphead ON (shiphead_id=shipitem_shiphead_id AND shiphead_order_type='SO')
  WHERE (shipitem_orderitem_id=pSoitemid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -102;
  END IF;

-- Cannot delete if returned
  IF (fetchMetricBool('MultiWhs')) THEN
    SELECT raitem_id INTO _result
    FROM raitem
    WHERE ( (raitem_orig_coitem_id=pSoitemid)
       OR   (raitem_new_coitem_id=pSoitemid) )
    LIMIT 1;
    IF (FOUND) THEN
      RETURN -103;
    END IF;
  END IF;

-- Cannot delete if any inventory history
  SELECT invhist_id INTO _result
  FROM invhist
  WHERE ( (invhist_ordnumber=formatSoNumber(pSoitemid))
    AND   (invhist_ordtype='SO') )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -105;
  END IF;

-- If Kit, check deletion of component items
  IF (_r.coitem_subnumber = 0) THEN
    FOR _s IN
      SELECT *
      FROM coitem
      WHERE ((coitem_cohead_id = _r.coitem_cohead_id)
        AND  (coitem_linenumber = _r.coitem_linenumber)
        AND  (coitem_subnumber > 0))
    LOOP
      IF ((COALESCE(_s.coitem_order_id, -1) > 0)
       AND (_s.coitem_order_type = 'P')) THEN
        SELECT poitem_status, COALESCE(recv_id, -1)
          INTO _poStatus, _recvId
        FROM poitem LEFT OUTER JOIN recv
               ON ((recv_orderitem_id=poitem_id)
                 AND (recv_order_type='PO'))
        WHERE (poitem_id = _s.coitem_order_id);

        IF ((_recvId > 0) OR (_poStatus = 'C')) THEN
          RETURN -10;
        ELSIF ((_recvId = -1) AND (_poStatus = 'O')) THEN
          _deletePO := _deletePO - 1;
        END IF;
      END IF;
    END LOOP;
  END IF;


  SELECT (itemsite_costmethod='J') INTO _jobItem
  FROM coitem JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
  WHERE (coitem_id=pSoitemid);

  IF (_jobItem AND _r.coitem_order_type='W') THEN
-- Delete associated Job Work Order
    SELECT deleteWo(_r.coitem_order_id, TRUE, TRUE) INTO _result;
    IF (_result < 0) THEN
      RETURN -104;
    END IF;
  ELSIF (_r.coitem_order_type='W') THEN
-- Delete associated Job Work Order
    SELECT deleteWo(_r.coitem_order_id, TRUE) INTO _result;
    IF (_result < 0) THEN
      -- Cannot delete so break association
      PERFORM changeWoProject(_r.coitem_order_id, -1, TRUE);
    END IF;
  ELSIF (_r.coitem_order_type='R') THEN
-- Delete associated Purchase Request
    PERFORM deletePr(_r.coitem_order_id);
  ELSIF (_r.coitem_order_type='P') THEN
-- Delete associated Purchase Order Item
    SELECT deletepoitem(_r.coitem_order_id) INTO _result;
    IF ((_result < 0) AND (_result <> -20)) THEN
      RETURN _result;
    ELSIF (_result = -20) THEN
      _deletePO := _deletePO - 1;
    END IF;
  END IF;

-- Delete characteristic assignments
  DELETE FROM charass
  WHERE (charass_target_type='SI')
    AND (charass_target_id=pSoitemid);

-- Delete reservations
  IF (fetchMetricBool('EnableSOReservationsByLocation')) THEN
    DELETE FROM reserve
    WHERE (reserve_demand_type='SO')
      AND (reserve_demand_id=pSoitemid);
  END IF;

-- Delete the coitem
  DELETE FROM coitem
  WHERE (coitem_id=pSoitemid);

  IF (_deletePO < 0) THEN
    RETURN -20;
  ELSE
    RETURN 0;
  END IF;

END;
$_$;


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

--
-- TOC entry 1460 (class 1255 OID 146566027)
-- Dependencies: 4536 8
-- Name: deletestandardjournal(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  DELETE FROM stdjrnlitem
  WHERE (stdjrnlitem_stdjrnl_id=pStdjrnlid);

  DELETE FROM stdjrnlgrpitem
  WHERE (stdjrnlgrpitem_stdjrnl_id=pStdjrnlid);

  DELETE FROM stdjrnl
  WHERE (stdjrnl_id=pStdjrnlid);

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 1461 (class 1255 OID 146566028)
-- Dependencies: 4536 8
-- Name: deletestandardjournalgroup(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  DELETE FROM stdjrnlgrpitem
  WHERE (stdjrnlgrpitem_stdjrnlgrp_id=pStdjrnlgrpid);

  DELETE FROM stdjrnlgrp
  WHERE (stdjrnlgrp_id=pStdjrnlgrpid);

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 1462 (class 1255 OID 146566029)
-- Dependencies: 4536 8
-- Name: deletesubaccount(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  IF (EXISTS(SELECT accnt_id
             FROM accnt, subaccnt
             WHERE ((accnt_company=subaccnt_number)
               AND  (subaccnt_id=pid))
            )) THEN
    RETURN -1;
  END IF;

  DELETE FROM subaccnt
  WHERE (subaccnt_id=pid);

  RETURN pid;

END;
$_$;


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

--
-- TOC entry 1433 (class 1255 OID 146566030)
-- Dependencies: 4536 8
-- Name: deletesubaccounttype(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deletesubaccounttype(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
  pSubAccntTypeid ALIAS FOR $1;
  _check INTEGER;

BEGIN

--  Check to see if the passed subaccnttype is used in any accounts
  SELECT accnt_id INTO _check
  FROM accnt, subaccnttype
  WHERE ( (accnt_subaccnttype_code=subaccnttype_code)
   AND (subaccnttype_id=pSubAccntTypeid) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

--  Delete the Sub Account Type
  DELETE FROM subaccnttype
  WHERE (subaccnttype_id=pSubAccntTypeid);

  RETURN 0;

END;
$_$;


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

--
-- TOC entry 1464 (class 1255 OID 146566031)
-- Dependencies: 4536 8
-- Name: deletetax(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deletetax(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
  ptaxid	ALIAS FOR $1;
BEGIN
  -- these checks allow nice error reporting instead of throwing an SQL error
  IF EXISTS(SELECT taxass_id FROM taxass WHERE (taxass_tax_id=ptaxid)) THEN
    RETURN -10;
  END IF;
  IF EXISTS(SELECT taxhist_id FROM taxhist WHERE (taxhist_tax_id=ptaxid)) THEN
    RETURN -20;
  END IF;

  DELETE FROM taxrate WHERE (taxrate_tax_id = ptaxid);
  DELETE FROM tax WHERE (tax_id = ptaxid);

  RETURN ptaxid;

END;
$_$;


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

--
-- TOC entry 1465 (class 1255 OID 146566032)
-- Dependencies: 4536 8
-- Name: deletetaxclass(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

-- Check to find if the tax class is used in any tax code
SELECT tax_id INTO _result
FROM tax
WHERE (tax_taxclass_id = pTaxclassid);
IF (FOUND) THEN
   RETURN -1;
END IF;

-- Delete the tax class if the above condition doesn't match
DELETE FROM taxclass WHERE taxclass_id = pTaxclassid ;

RETURN pTaxclassid;

END;
$_$;


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

--
-- TOC entry 1466 (class 1255 OID 146566033)
-- Dependencies: 4536 8
-- Name: deletetaxtype(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

  SELECT taxtype_id
    INTO _result
    FROM taxtype
   WHERE ((taxtype_sys)
     AND  (taxtype_id=pTaxtypeid));
  IF (FOUND) THEN
    RETURN -1;
  END IF;

  SELECT taxass_id
    INTO _result
    FROM taxass
   WHERE (taxass_taxtype_id=pTaxtypeid);
  IF (FOUND) THEN
    RETURN -2;
  END IF;

  SELECT taxhist_id
    INTO _result
    FROM taxhist
   WHERE (taxhist_taxtype_id=pTaxtypeid);
  IF (FOUND) THEN
    RETURN -3;
  END IF;

  DELETE
    FROM taxtype
   WHERE (taxtype_id=pTaxtypeid);

  RETURN pTaxtypeid;

END;
$_$;


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

--
-- TOC entry 1467 (class 1255 OID 146566034)
-- Dependencies: 4536 8
-- Name: deletetaxzone(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

-- Check to find if the tax zone is used in any tax assignment
SELECT taxass_id INTO _result
FROM taxass
WHERE (taxass_taxzone_id=pTaxzoneid);
IF (FOUND) THEN
   RETURN -1;
END IF;

-- Check to find if the tax zone has been referenced in any tax registration
SELECT taxreg_id INTO _result 
FROM taxreg
WHERE (taxreg_taxzone_id=pTaxzoneid);
IF (FOUND) THEN
   RETURN -2;
END IF;

-- Delete the tax zone if none of the above conditions match
DELETE FROM taxzone WHERE taxzone_id = pTaxzoneid ;

RETURN pTaxzoneid;

END;
$_$;


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

--
-- TOC entry 1468 (class 1255 OID 146566035)
-- Dependencies: 4536 8
-- Name: deletetodoitem(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deletetodoitem(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
  ptodoItemId ALIAS FOR $1;
BEGIN
  DELETE FROM alarm WHERE ( (alarm_source='TODO') AND (alarm_source_id=ptodoItemId) );
  DELETE FROM todoitem WHERE todoitem_id = ptodoItemId;
  RETURN 0;
END;
$_$;


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

--
-- TOC entry 1469 (class 1255 OID 146566036)
-- Dependencies: 4536 8
-- Name: deleteunusedclasscodes(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deleteunusedclasscodes() RETURNS integer
    LANGUAGE plpgsql
    AS $$
-- 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

  DELETE FROM classcode
  WHERE (classcode_id NOT IN (SELECT DISTINCT item_classcode_id FROM item));

  RETURN 0;

END;
$$;


ALTER FUNCTION public.deleteunusedclasscodes() OWNER TO admin;

--
-- TOC entry 1470 (class 1255 OID 146566037)
-- Dependencies: 4536 8
-- Name: deleteunusedfreightclasses(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deleteunusedfreightclasses() RETURNS integer
    LANGUAGE plpgsql
    AS $$
-- 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

  DELETE FROM freightclass
  WHERE (freightclass_id NOT IN (SELECT DISTINCT COALESCE(item_freightclass_id, 0) FROM item));

  RETURN 0;

END;
$$;


ALTER FUNCTION public.deleteunusedfreightclasses() OWNER TO admin;

--
-- TOC entry 1471 (class 1255 OID 146566038)
-- Dependencies: 4536 8
-- Name: deleteunusedproductcategories(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deleteunusedproductcategories() RETURNS integer
    LANGUAGE plpgsql
    AS $$
-- 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

--  Delete any associated records
  DELETE FROM salesaccnt
  WHERE ( (salesaccnt_prodcat_id <> -1)
   AND (salesaccnt_prodcat_id NOT IN (SELECT DISTINCT item_prodcat_id FROM item)) );

  DELETE FROM prodcat
  WHERE (prodcat_id NOT IN (SELECT DISTINCT item_prodcat_id FROM item));

  RETURN 0;

END;
$$;


ALTER FUNCTION public.deleteunusedproductcategories() OWNER TO admin;

--
-- TOC entry 1472 (class 1255 OID 146566039)
-- Dependencies: 4536 8
-- Name: deleteuom(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  DELETE FROM uomconv WHERE uomconv_from_uom_id=pUomid;
  DELETE FROM uomconv WHERE uomconv_to_uom_id=pUomid;
  DELETE FROM uom WHERE uom_id=pUomid;

  RETURN 0;
END;
$_$;


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

--
-- TOC entry 1473 (class 1255 OID 146566040)
-- Dependencies: 4536 8
-- Name: deleteuomconv(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  DELETE FROM uomconv WHERE uomconv_id=pUomconvid;

  RETURN 0;
END;
$_$;


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

--
-- TOC entry 1474 (class 1255 OID 146566041)
-- Dependencies: 4536 8
-- Name: deleteurl(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deleteurl(integer) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
declare
  pId ALIAS FOR $1;
begin
  delete from urlinfo
  where ( url_id in (
    select url_id
    from urlinfo
      join docass on (docass_target_id=url_id)
                 and (docass_target_type='URL')
      where ( docass_id = pId ) ) );

  delete from docass where docass_id = pId;

  return true;
end;
$_$;


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

--
-- TOC entry 1475 (class 1255 OID 146566042)
-- Dependencies: 4536 8
-- Name: deleteuserpreference(text); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT deleteUserPreference(getEffectiveXtUser(), pPrefname) INTO _return;

  RETURN _return;

END;
$_$;


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

--
-- TOC entry 1476 (class 1255 OID 146566043)
-- Dependencies: 4536 8
-- Name: deleteuserpreference(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  DELETE FROM usrpref
  WHERE ( (usrpref_username=pUsername)
   AND (usrpref_name=pPrefname) );

  RETURN TRUE;

END;
$_$;


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

--
-- TOC entry 1479 (class 1255 OID 146566044)
-- Dependencies: 4536 8
-- Name: deletevendoraddress(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

--  Check to see if the passed vendor address is used in pohead
  SELECT pohead_id INTO _test
  FROM pohead
  WHERE (pohead_vendaddr_id=pVendaddrid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

--  Delete the passed vendor address
  DELETE FROM vendaddrinfo
  WHERE (vendaddr_id=pVendaddrid);

  RETURN 0;

END;
$_$;


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

--
-- TOC entry 1480 (class 1255 OID 146566045)
-- Dependencies: 4536 8
-- Name: deletevendortype(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

--  Check to see if the passed vendor type is used in vendinfo
  SELECT vend_id INTO _test
  FROM vendinfo
  WHERE (vend_vendtype_id=pVendtypeid)
  LIMIT 1;
  IF (FOUND) THEN
    RETURN -1;
  END IF;

--  Delete the passed vendor type
  DELETE FROM vendtype
  WHERE (vendtype_id=pVendtypeid);

  RETURN 0;

END;
$_$;


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

--
-- TOC entry 1481 (class 1255 OID 146566046)
-- Dependencies: 4536 8
-- Name: deletewo(integer, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  RETURN deleteWo(pWoid, deleteChildren, FALSE);
END;
$_$;


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

--
-- TOC entry 1482 (class 1255 OID 146566047)
-- Dependencies: 4536 8
-- Name: deletewo(integer, boolean, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION deletewo(integer, 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
  pWoid ALIAS FOR $1;
  deleteChildren ALIAS FOR $2;
  deleteForce ALIAS FOR $3;
  woStatus CHAR(1);
  itemType CHAR(1);
  ordtype CHAR(1);
  ordid INTEGER;
  returnCode INTEGER;
  _wotcCnt	INTEGER;
  _routings BOOLEAN;

BEGIN
  SELECT wo_status, wo_ordtype, wo_ordid, item_type
  INTO   woStatus, ordtype, ordid, itemType
  FROM wo JOIN itemsite ON (itemsite_id=wo_itemsite_id)
          JOIN item ON (item_id=itemsite_item_id)
  WHERE (wo_id=pWoid);

  IF (NOT woStatus IN ('O', 'E', 'C')) THEN
    RETURN -3;
  END IF;

  IF (NOT deleteForce) THEN
    IF (itemType = 'J') THEN
      RETURN -2;
    END IF;
  END IF;

  SELECT fetchMetricBool('Routings') INTO _routings;

  IF _routings THEN
    SELECT count(*) INTO _wotcCnt
    FROM xtmfg.wotc
    WHERE (wotc_wo_id=pWoid);
    IF (_wotcCnt > 0) THEN
      RETURN -1;
    END IF;
  END IF;

  IF (woStatus = 'R') THEN
    PERFORM postEvent('RWoRequestCancel', 'W', wo_id,
                      itemsite_warehous_id, formatWoNumber(wo_id),
                      NULL, NULL, NULL, NULL)
    FROM wo JOIN itemsite ON (itemsite_id=wo_itemsite_id)
            JOIN item ON (item_id=itemsite_item_id)
    WHERE (wo_id=pWoid);

     RETURN 0;
  ELSE
    IF (woStatus = 'E') THEN
      returnCode := (SELECT implodeWo(pWoid, FALSE));
    END IF;
  END IF;

  IF (woStatus IN ('O', 'E', 'C')) THEN
    DELETE FROM womatl
    WHERE (womatl_wo_id=pWoid);

    IF _routings THEN
      DELETE FROM xtmfg.wooper
      WHERE (wooper_wo_id=pWoid);
    END IF;

    IF (ordtype = 'S') THEN
      UPDATE coitem SET coitem_order_type=NULL, coitem_order_id=NULL
      WHERE coitem_id=ordid;
    END IF;

    DELETE FROM wo
    WHERE (wo_id=pWoid);
  END IF;

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

  RETURN 0;
END;
$_$;


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

--
-- TOC entry 1483 (class 1255 OID 146566048)
-- Dependencies: 4536 8
-- Name: deletewomaterial(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  UPDATE wo
  SET wo_adhoc=TRUE
  FROM womatl
  WHERE ((womatl_wo_id=wo_id)
   AND (womatl_id=pWomatlid));

--  Delete any created P/R for this Womatl
  PERFORM deletePr('W', pWomatlid);

  DELETE FROM womatl
  WHERE (womatl_id=pWomatlid);

  RETURN 0;
END;
$_$;


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

--
-- TOC entry 1484 (class 1255 OID 146566049)
-- Dependencies: 4536 8
-- Name: detachccpayfromso(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION detachccpayfromso(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
  pcoheadid		ALIAS FOR $1;
  pwarehousid		ALIAS FOR $2;
  pcustid		ALIAS FOR $3;

BEGIN
  RAISE NOTICE 'detachCCPayFromSO(INTEGER, INTEGER, INTEGER): deprecated';
  RETURN 0;
END;
$_$;


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

--
-- TOC entry 1485 (class 1255 OID 146566050)
-- Dependencies: 4536 8
-- Name: detachcontact(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

  UPDATE crmacct SET crmacct_cntct_id_1 = NULL
  WHERE crmacct_id = pcrmacctId
    AND crmacct_cntct_id_1 = pcntctId;

  UPDATE crmacct SET crmacct_cntct_id_2 = NULL
  WHERE crmacct_id = pcrmacctId
    AND crmacct_cntct_id_2 = pcntctId;

  RETURN 0;
END;
$_$;


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

--
-- TOC entry 1486 (class 1255 OID 146566051)
-- Dependencies: 4536 8
-- Name: detag(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION detag(text) RETURNS text
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pSource ALIAS FOR $1;
  _result TEXT := '';

BEGIN
  SELECT regexp_replace(pSource, E'<[^>]*>', '', 'g') INTO _result;
  RETURN _result;
END;
$_$;


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

--
-- TOC entry 1487 (class 1255 OID 146566052)
-- Dependencies: 4536 8
-- Name: detailednnqoh(integer, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  IF (pABS) THEN
    SELECT SUM(noNeg(itemloc_qty)) INTO _qoh
    FROM itemloc, location
    WHERE ( (itemloc_location_id=location_id)
     AND (NOT location_netable)
     AND (itemloc_itemsite_id=pItemsiteid) );
  ELSE
    SELECT SUM(itemloc_qty) INTO _qoh
    FROM itemloc, location
    WHERE ( (itemloc_location_id=location_id)
     AND (NOT location_netable)
     AND (itemloc_itemsite_id=pItemsiteid) );
  END IF;

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

  RETURN _qoh;

END;
$_$;


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

--
-- TOC entry 1488 (class 1255 OID 146566053)
-- Dependencies: 4536 8
-- Name: detailedqoh(integer, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  IF (pABS) THEN
    SELECT SUM(noNeg(itemloc_qty)) INTO _qoh
    FROM itemloc LEFT OUTER JOIN location ON (itemloc_location_id=location_id)
    WHERE ( ( (location_id IS NULL) OR (location_netable) )
     AND (itemloc_itemsite_id=pItemsiteid) );
  ELSE
    SELECT SUM(itemloc_qty) INTO _qoh
    FROM itemloc LEFT OUTER JOIN location ON (itemloc_location_id=location_id)
    WHERE ( ( (location_id IS NULL) OR (location_netable) )
     AND (itemloc_itemsite_id=pItemsiteid) );
  END IF;

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

  RETURN _qoh;

END;
$_$;


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

--
-- TOC entry 1491 (class 1255 OID 146566054)
-- Dependencies: 4536 8
-- Name: determinediscountdate(integer, date); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT terms_type, terms_discdays, terms_cutoffday INTO _p
  FROM terms
  WHERE (terms_id=pTermsid);
  IF (NOT FOUND) THEN
    _discDate := pSourceDate;

--  Handle type D terms
  ELSIF (_p.terms_type = 'D') THEN
    _discDate := (pSourceDate + _p.terms_discdays);

--  Handle type P terms
  ELSIF (_p.terms_type = 'P') THEN
    IF (date_part('day', pSourceDate) <= _p.terms_cutoffday) THEN
      _discDate := (DATE(date_trunc('month', pSourceDate)) + (_p.terms_discdays - 1));
    ELSE
      _discDate := (DATE(date_trunc('month', pSourceDate)) + (_p.terms_discdays - 1) + INTERVAL '1 month');
    END IF;

--  Handle unknown terms
  ELSE
    _discDate := pSourceDate;
  END IF;

  RETURN _discDate;

END;
$_$;


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

--
-- TOC entry 1492 (class 1255 OID 146566055)
-- Dependencies: 4536 8
-- Name: determineduedate(integer, date); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT terms_type, terms_duedays, terms_cutoffday INTO _p
  FROM terms
  WHERE (terms_id=pTermsid);
  IF (NOT FOUND) THEN
    _dueDate := pSourceDate;

--  Handle type D terms
  ELSIF (_p.terms_type = 'D') THEN
    _dueDate := (pSourceDate + _p.terms_duedays);

--  Handle type P terms
  ELSIF (_p.terms_type = 'P') THEN
    IF (date_part('day', pSourceDate) <= _p.terms_cutoffday) THEN
      _dueDate := (DATE(date_trunc('month', pSourceDate)) + (_p.terms_duedays - 1));
    ELSE
      _dueDate := (DATE(date_trunc('month', pSourceDate)) + (_p.terms_duedays - 1) + INTERVAL '1 month');
    END IF;

--  Handle unknown terms
  ELSE
    _dueDate := pSourceDate;
  END IF;

  RETURN _dueDate;

END;
$_$;


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

--
-- TOC entry 1493 (class 1255 OID 146566056)
-- Dependencies: 8
-- Name: digest(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1494 (class 1255 OID 146566057)
-- Dependencies: 8
-- Name: digest(bytea, text); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1495 (class 1255 OID 146566058)
-- Dependencies: 4536 8
-- Name: disablepackage(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION disablepackage(text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  ppkgname ALIAS FOR $1;
  _i       INTEGER := 0;
  _tabs    TEXT[] := ARRAY['cmd',  'cmdarg', 'image',  'metasql',
                           'priv', 'report', 'script', 'uiform'];

BEGIN
  IF (version() < 'PostgreSQL 8.2') THEN
    RETURN -1;
  END IF;

  FOR _i IN ARRAY_LOWER(_tabs,1)..ARRAY_UPPER(_tabs,1) LOOP
    EXECUTE 'ALTER TABLE ' || ppkgname || '.pkg' || _tabs[_i] ||
            ' NO INHERIT public.' || _tabs[_i] || ';';
  END LOOP;
  
  RETURN 0;
END;
$_$;


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

--
-- TOC entry 1496 (class 1255 OID 146566059)
-- Dependencies: 4536 8
-- Name: disablepackage(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION disablepackage(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
  ppkgheadid    ALIAS FOR $1;
  _pkgname      TEXT;

BEGIN
  SELECT pkghead_name INTO _pkgname
  FROM pkghead
  WHERE (pkghead_id=ppkgheadid);
  IF (NOT FOUND) THEN
    RETURN -2;
  END IF;

  RETURN disablePackage(_pkgname);
END;
$_$;


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

--
-- TOC entry 1497 (class 1255 OID 146566060)
-- Dependencies: 4536 8
-- Name: distributeitemlocseries(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION distributeitemlocseries(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
  pItemlocSeries   ALIAS FOR $1;
  _distCounter     INTEGER;
  _itemlocdist     RECORD;
  _itemlocid       INTEGER;
  _invhistid       INTEGER;
  _check           BOOLEAN;
  _debug           BOOLEAN := true;
BEGIN

  IF (_debug) THEN
    RAISE NOTICE 'distributeItemlocSeries, series=%', pItemlocSeries;
  END IF;

  _distCounter := 0;

--  March through all of the itemlocdists for pItemlocSeries
  FOR _itemlocdist IN SELECT itemlocdist_id AS itemlocdistid,
                             itemlocdist_source_type AS type,
                             itemlocdist_source_id AS sourceid,
                             itemlocdist_qty AS qty,
                             itemlocdist_itemsite_id AS itemsiteid,
                             itemsite_freeze,
                             itemlocdist_invhist_id AS invhistid,
                             itemlocdist_ls_id AS lotserialid,
                             itemlocdist_expiration AS expiration,
                             itemlocdist_flush,
                             itemlocdist_warranty AS warranty,
                             itemlocdist_series AS series
                      FROM itemlocdist, itemsite
                      WHERE ( (itemlocdist_itemsite_id=itemsite_id)
                       AND (itemlocdist_series=pItemlocSeries) )
                      ORDER BY itemlocdist_flush DESC LOOP

    _distCounter := _distCounter + 1;
    IF (_debug) THEN
      RAISE NOTICE 'itemlocdist loop %', _distCounter;
      RAISE NOTICE 'itemlocdistid=%', _itemlocdist.itemlocdistid;
      RAISE NOTICE 'type=%', _itemlocdist.type;
      RAISE NOTICE 'sourceid=%', _itemlocdist.sourceid;
      RAISE NOTICE 'qty=%', _itemlocdist.qty;
      RAISE NOTICE 'itemsiteid=%', _itemlocdist.itemsiteid;
      RAISE NOTICE 'freeze=%', _itemlocdist.itemsite_freeze;
      RAISE NOTICE 'invhistid=%', _itemlocdist.invhistid;
      RAISE NOTICE 'lotserialid=%', _itemlocdist.lotserialid;
      RAISE NOTICE 'expiration=%', _itemlocdist.expiration;
      RAISE NOTICE 'flush=%', _itemlocdist.itemlocdist_flush;
      RAISE NOTICE 'warranty=%', _itemlocdist.warranty;
    END IF;

--  Commit invhist to itemsite
    IF (NOT _itemlocdist.itemsite_freeze) THEN
	PERFORM postInvHist(_itemlocdist.invhistid);
    END IF;

--  Mark the invhist tuple for the itemlocdist in question as having detail
    UPDATE invhist
    SET invhist_hasdetail=TRUE
    WHERE ( (NOT invhist_hasdetail)
     AND (invhist_id=_itemlocdist.invhistid) );

--  If this itemlocdist is a flush, write a invdetail tuple that records the empty
    IF (_itemlocdist.itemlocdist_flush) THEN
      INSERT INTO invdetail
      ( invdetail_invhist_id, invdetail_location_id, invdetail_ls_id,
        invdetail_qty, invdetail_qty_before, invdetail_qty_after, invdetail_expiration,
        invdetail_warrpurc )
      SELECT _itemlocdist.invhistid, itemloc_location_id, itemloc_ls_id,
             (itemloc_qty * -1), itemloc_qty, 0, itemloc_expiration, 
             _itemlocdist.warranty
      FROM itemloc
      WHERE ( (itemloc_qty <> 0)
       AND (itemloc_id=_itemlocdist.sourceid) );

--  Delete the flushed itemloc if its parent itemsite is not frozen
      IF (NOT _itemlocdist.itemsite_freeze) THEN
        DELETE FROM itemloc
        WHERE (itemloc_id=_itemlocdist.sourceid);
      END IF;

    ELSE
--  If this is a location type distribution, check to see if the target itemloc
--  already exists
      IF (_itemlocdist.type = 'L') THEN
        SELECT itemloc_id INTO _itemlocid
        FROM itemloc
        WHERE ( (itemloc_itemsite_id=_itemlocdist.itemsiteid)
         AND (itemloc_location_id=_itemlocdist.sourceid)
         AND (COALESCE(itemloc_ls_id,-1)=COALESCE(_itemlocdist.lotserialid,-1))
         AND (COALESCE(itemloc_expiration,endOfTime())=COALESCE(_itemlocdist.expiration,endOfTime()))
         AND (COALESCE(itemloc_warrpurc,endoftime())=COALESCE(_itemlocdist.warranty,endoftime())) );

--  Nope, create it
        IF (NOT FOUND) THEN
          SELECT NEXTVAL('itemloc_itemloc_id_seq') INTO _itemlocid;

          INSERT INTO itemloc
          ( itemloc_id, itemloc_itemsite_id,
            itemloc_location_id, itemloc_qty,
            itemloc_ls_id, itemloc_expiration,
            itemloc_warrpurc )
          VALUES
          ( _itemlocid, _itemlocdist.itemsiteid,
            _itemlocdist.sourceid, 0,
            _itemlocdist.lotserialid, _itemlocdist.expiration,
            _itemlocdist.warranty );
        END IF;

      ELSE
        _itemlocid = _itemlocdist.sourceid;

        IF (_itemlocid IS NOT NULL AND (SELECT count(itemloc_id) = 0 FROM itemloc WHERE itemloc_id=_itemlocid)) THEN
          RAISE EXCEPTION 'No record to distribute against. Someone else may have already distributed this record.';
        END IF;
      END IF;

--  Record the invdetail
      INSERT INTO invdetail
      (invdetail_invhist_id, invdetail_location_id, invdetail_ls_id,
       invdetail_qty, invdetail_qty_before, invdetail_qty_after, invdetail_expiration,
       invdetail_warrpurc)
      SELECT _itemlocdist.invhistid, itemloc_location_id, _itemlocdist.lotserialid,
             _itemlocdist.qty, itemloc_qty, (itemloc_qty + _itemlocdist.qty),
             itemloc_expiration,_itemlocdist.warranty
      FROM itemloc
      WHERE (itemloc_id=_itemlocid);

--  Update the itemloc_qty if its parent itemsite is not frozen
      IF (NOT _itemlocdist.itemsite_freeze) THEN
        UPDATE itemloc
        SET itemloc_qty = (itemloc_qty + _itemlocdist.qty)
        WHERE (itemloc_id=_itemlocid);
      END IF;

--  Adjust QOH if this itemlocdist is to/from a non-netable location
      IF ( SELECT (NOT location_netable)
           FROM itemloc, location
           WHERE ( (itemloc_location_id=location_id)
            AND (itemloc_id=_itemlocid) ) ) THEN

--  Record the netable->non-netable (or visaveras) invhist
        SELECT NEXTVAL('invhist_invhist_id_seq') INTO _invhistid;
        INSERT INTO invhist
        ( invhist_id, invhist_itemsite_id, 
          invhist_transtype, invhist_invqty,
          invhist_qoh_before, invhist_qoh_after,
          invhist_docnumber, invhist_comments,
          invhist_invuom, invhist_unitcost,
          invhist_costmethod, invhist_value_before, invhist_value_after,
          invhist_series ) 
        SELECT _invhistid, itemsite_id, 
               'NN', (_itemlocdist.qty * -1),
               itemsite_qtyonhand, (itemsite_qtyonhand - _itemlocdist.qty),
               invhist_docnumber, invhist_comments,
               uom_name, stdCost(item_id),
               itemsite_costmethod, itemsite_value,
               (itemsite_value + (_itemlocdist.qty * -1 * CASE WHEN(itemsite_costmethod='A') THEN avgcost(itemsite_id)
                                                               ELSE stdCost(itemsite_item_id)
                                                          END)),
               _itemlocdist.series
        FROM item, itemsite, invhist, uom
        WHERE ((itemsite_item_id=item_id)
         AND (item_inv_uom_id=uom_id)
         AND (itemsite_controlmethod <> 'N')
         AND (itemsite_id=_itemlocdist.itemsiteid)
         AND (invhist_id=_itemlocdist.invhistid));

--  Adjust the parent itemsite
        IF (NOT _itemlocdist.itemsite_freeze) THEN
          UPDATE itemsite
          SET itemsite_qtyonhand = (itemsite_qtyonhand - _itemlocdist.qty),
              itemsite_nnqoh = (itemsite_nnqoh + _itemlocdist.qty)
          FROM itemloc
          WHERE ((itemloc_itemsite_id=itemsite_id)
           AND (itemloc_id=_itemlocid));
        END IF;
      END IF;

    END IF;

--  If, after the distribution, the target itemloc_qty = 0, delete the itemloc
--  if its parent itemsite is not frozen
    IF (NOT _itemlocdist.itemsite_freeze) THEN
      DELETE FROM itemloc
      WHERE ( (itemloc_qty=0)
       AND (itemloc_id=_itemlocid) );
    END IF;

  END LOOP;

  DELETE FROM itemlocdist
  WHERE (itemlocdist_series=pItemlocSeries);

  RETURN _distCounter;

END;
$_$;


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

--
-- TOC entry 1498 (class 1255 OID 146566062)
-- Dependencies: 4536 8
-- Name: distributetodefault(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  RETURN distributeToDefault(pItemlocdistid, 'O');

END;
$_$;


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

--
-- TOC entry 1499 (class 1255 OID 146566063)
-- Dependencies: 4536 8
-- Name: distributetodefault(integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

--  Make sure that the itemsite in question has a default location
  SELECT CASE WHEN (pTranstype='R') THEN itemsite_recvlocation_id
              WHEN (pTranstype='I') THEN itemsite_issuelocation_id
              ELSE itemsite_location_id
         END INTO _locationid
  FROM itemlocdist, itemsite
  WHERE ( (itemlocdist_itemsite_id=itemsite_id)
   AND (itemlocdist_id=pItemlocdistid) );
  IF ( (NOT FOUND) OR (_locationid = -1) ) THEN
    RETURN -1;
  END IF;

--  Determine the remaining qty required to distribute
  SELECT (p.itemlocdist_qty - COALESCE(SUM(c.itemlocdist_qty), 0)) INTO _qty
  FROM itemlocdist AS p LEFT OUTER JOIN itemlocdist AS c ON (c.itemlocdist_itemlocdist_id=p.itemlocdist_id)
  WHERE (p.itemlocdist_id=pItemlocdistid)
  GROUP BY p.itemlocdist_qty;

  IF (_qty = 0) THEN
    RETURN -2;
  END IF;

--  Check to see if an itemlocdist with the correct location/lotserial/expiration already exists
  SELECT target.itemlocdist_id INTO _itemlocdistid
  FROM itemlocdist AS source, itemlocdist AS target, itemloc, itemsite
  WHERE ( (target.itemlocdist_source_type='L')
   AND (target.itemlocdist_source_id=_locationid)
   AND (target.itemlocdist_itemsite_id=source.itemlocdist_itemsite_id)
   AND (COALESCE(target.itemlocdist_ls_id)=COALESCE(source.itemlocdist_ls_id))
   AND (target.itemlocdist_expiration=source.itemlocdist_expiration)
   AND (target.itemlocdist_itemlocdist_id=source.itemlocdist_itemlocdist_id)
   AND (target.itemlocdist_itemsite_id=itemsite_id)
   AND (source.itemlocdist_id=pItemlocdistid) );

  IF (FOUND) THEN
    UPDATE itemlocdist
    SET itemlocdist_qty = (itemlocdist_qty + _qty)
    WHERE (itemlocdist_id=_itemlocdistid);

    RETURN _itemlocdistid;
  END IF;

--  Create a new itemlocdist
  SELECT NEXTVAL('itemlocdist_itemlocdist_id_seq') INTO _itemlocdistid;

  INSERT INTO itemlocdist
  ( itemlocdist_id, itemlocdist_itemlocdist_id, itemlocdist_source_type,
    itemlocdist_ls_id, itemlocdist_expiration,
    itemlocdist_source_id, itemlocdist_itemsite_id, itemlocdist_qty )
  SELECT _itemlocdistid, pItemlocdistid, 'L',
         itemlocdist_ls_id, itemlocdist_expiration,
         _locationid, itemlocdist_itemsite_id, _qty
  FROM itemlocdist
  WHERE (itemlocdist_id=pItemlocdistid);

  RETURN _itemlocdistid;

END;
$_$;


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

--
-- TOC entry 1500 (class 1255 OID 146566064)
-- Dependencies: 4536 8
-- Name: distributetodefaultitemloc(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  RETURN distributeToDefaultItemLoc(pItemlocdistid, 'O');

END;
$_$;


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

--
-- TOC entry 1501 (class 1255 OID 146566065)
-- Dependencies: 4536 8
-- Name: distributetodefaultitemloc(integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

--  Make sure that the itemsite in question has a default location
  SELECT itemloc_id INTO _itemlocid
    FROM itemlocdist, itemsite, itemloc
   WHERE ((itemlocdist_itemsite_id=itemsite_id)
     AND ( (itemsite_location_id=itemloc_location_id AND pTranstype='O') OR
           (itemsite_recvlocation_id=itemloc_location_id AND pTranstype='R') OR
           (itemsite_issuelocation_id=itemloc_location_id AND pTranstype='I') )
     AND (itemloc_itemsite_id=itemsite_id)
     AND (itemlocdist_id=pItemlocdistid));
  IF ( (NOT FOUND) OR (_itemlocid = -1) ) THEN
    RETURN -1;
  END IF;

--  Determine the remaining qty required to distribute
  SELECT (p.itemlocdist_qty - COALESCE(SUM(c.itemlocdist_qty), 0)) INTO _qty
    FROM itemlocdist AS p LEFT OUTER JOIN itemlocdist AS c
      ON (c.itemlocdist_itemlocdist_id=p.itemlocdist_id)
   WHERE (p.itemlocdist_id=pItemlocdistid)
   GROUP BY p.itemlocdist_qty;

  IF (_qty = 0) THEN
    RETURN -2;
  END IF;

--  Check to see if an itemlocdist with the correct location/lotserial/expiration already exists
  SELECT target.itemlocdist_id INTO _itemlocdistid
  FROM itemlocdist AS source, itemlocdist AS target
  WHERE ( (target.itemlocdist_source_type='I')
   AND (target.itemlocdist_source_id=_itemlocid)
   AND (COALESCE(target.itemlocdist_ls_id,-1)=COALESCE(source.itemlocdist_ls_id,-1))
   AND (target.itemlocdist_expiration=source.itemlocdist_expiration)
   AND (target.itemlocdist_itemlocdist_id=source.itemlocdist_id)
   AND (source.itemlocdist_id=pItemlocdistid) );

  IF (FOUND) THEN
    UPDATE itemlocdist
    SET itemlocdist_qty = (itemlocdist_qty + _qty)
    WHERE (itemlocdist_id=_itemlocdistid);

    RETURN _itemlocdistid;
  END IF;

--  Create a new itemlocdist
  SELECT NEXTVAL('itemlocdist_itemlocdist_id_seq') INTO _itemlocdistid;

  INSERT INTO itemlocdist
  ( itemlocdist_id, itemlocdist_itemlocdist_id,
    itemlocdist_source_type, itemlocdist_source_id,
    itemlocdist_qty, itemlocdist_expiration )
  VALUES
  ( _itemlocdistid, pItemlocdistid,
    'I', _itemlocid,
    _qty, endOfTime() );

  RETURN _itemlocdistid;

END;
$_$;


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

--
-- TOC entry 1502 (class 1255 OID 146566066)
-- Dependencies: 4536 8
-- Name: distributetolocations(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION distributetolocations(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
  pItemlocdistid ALIAS FOR $1;
  _distCounter INTEGER;
  _itemlocdist RECORD;
  _itemlocid INTEGER;
  _runningQty NUMERIC;
  _tmp RECORD;

BEGIN

  _distCounter := 0;
  _runningQty  := 0;

-- A scenario can occur where two people try to post distributions
-- to the same itemsite against two or more lot/serial/mlc locations
-- leading to a deadlock. This line tries to prevent that by locking
-- ahead of time all the itemsites that the transaction will need
-- before any of the other tables are locked individually.
  SELECT itemsite_id
    INTO _tmp
    FROM itemsite
   WHERE(itemsite_id in (SELECT DISTINCT itemlocdist_itemsite_id
                           FROM itemlocdist
                          WHERE(itemlocdist_id=pItemlocdistid)))
     FOR UPDATE;

--  March through all of the itemlocdist owned by the passed parent itemlocdist
  FOR _itemlocdist IN SELECT c.itemlocdist_id AS itemlocdistid,
                             c.itemlocdist_source_type AS type,
                             c.itemlocdist_source_id AS sourceid,
                             c.itemlocdist_qty AS qty,
                             p.itemlocdist_itemsite_id AS itemsiteid,
                             itemsite_freeze,
                             p.itemlocdist_invhist_id AS invhistid,
                             p.itemlocdist_ls_id AS lotserialid,
                             p.itemlocdist_expiration AS expiration,
                             p.itemlocdist_warranty AS warranty,
                             p.itemlocdist_order_type AS ordertype,
                             p.itemlocdist_order_id AS orderid,
                             p.itemlocdist_series AS series
                      FROM itemlocdist AS c, itemlocdist AS p, itemsite
                      WHERE ( (c.itemlocdist_itemlocdist_id=p.itemlocdist_id)
                       AND (p.itemlocdist_source_type='O')
                       AND (p.itemlocdist_itemsite_id=itemsite_id)
                       AND (p.itemlocdist_id=pItemlocdistid) ) LOOP

    _distCounter := _distCounter + 1;

--  If the target for this itemlocdist is a location, check to see if the
--  required itemloc already exists
    IF (_itemlocdist.type = 'L') THEN
      SELECT itemloc_id INTO _itemlocid
      FROM itemloc
      WHERE ( (itemloc_itemsite_id=_itemlocdist.itemsiteid)
       AND (itemloc_location_id=_itemlocdist.sourceid)
       AND (COALESCE(itemloc_ls_id, -1)=COALESCE(_itemlocdist.lotserialid, -1))
       AND (COALESCE(itemloc_expiration,endOfTime())=COALESCE(_itemlocdist.expiration,endOfTime()))
       AND (COALESCE(itemloc_warrpurc,endoftime())=COALESCE(_itemlocdist.warranty,endoftime())) );

--  Nope, make it
      IF (NOT FOUND) THEN
        SELECT NEXTVAL('itemloc_itemloc_id_seq') INTO _itemlocid;
        INSERT INTO itemloc
        ( itemloc_id, itemloc_itemsite_id,
          itemloc_location_id, itemloc_qty,
          itemloc_ls_id, itemloc_expiration,
          itemloc_warrpurc )
        VALUES
        ( _itemlocid, _itemlocdist.itemsiteid,
          _itemlocdist.sourceid, 0,
          _itemlocdist.lotserialid, _itemlocdist.expiration,
          _itemlocdist.warranty );
      END IF;

    ELSE
--  Yep, cache it
      _itemlocid = _itemlocdist.sourceid;

      IF (_itemlocid IS NOT NULL AND (SELECT count(itemloc_id) = 0 FROM itemloc WHERE itemloc_id=_itemlocid)) THEN
        RAISE EXCEPTION 'No record to distribute against. Someone else may have already distributed this record.';
      END IF;
    END IF;

--  Record the invdetail for this itemlocdist
    INSERT INTO invdetail
    ( invdetail_invhist_id, invdetail_location_id, invdetail_ls_id,
      invdetail_qty, invdetail_qty_before, invdetail_qty_after, invdetail_expiration, 
      invdetail_warrpurc )
    SELECT _itemlocdist.invhistid, itemloc_location_id, itemloc_ls_id,
           _itemlocdist.qty, itemloc_qty, (itemloc_qty + _itemlocdist.qty),
           itemloc_expiration,_itemlocdist.warranty
    FROM itemloc
    WHERE (itemloc_id=_itemlocid);

--  Update the parent invhist to indicate that it has invdetail records
    UPDATE invhist
    SET invhist_hasdetail=TRUE
    WHERE ((invhist_hasdetail=FALSE)
     AND (invhist_id=_itemlocdist.invhistid));

--  Update the itemloc_qty if its parent itemsite is not frozen
    IF (NOT _itemlocdist.itemsite_freeze) THEN
      UPDATE itemloc
      SET itemloc_qty = (itemloc_qty + _itemlocdist.qty)
      WHERE (itemloc_id=_itemlocid);

      PERFORM postInvHist(_itemlocdist.invhistid);

--  Handle reservation data
      IF ( (SELECT fetchMetricBool('EnableSOReservationsByLocation')) AND
           (_itemlocdist.qty < 0) ) THEN

--  If a shipment on a sales order, record reservation change before updating
--  so it can be reversed later if necessary
        IF (_itemlocdist.ordertype = 'SO') THEN
          INSERT INTO shipitemlocrsrv
          SELECT nextval('shipitemlocrsrv_shipitemlocrsrv_id_seq'),
            shipitem_id, itemloc_itemsite_id, itemloc_location_id,
            itemloc_ls_id, itemloc_expiration, itemloc_warrpurc,
            least((_itemlocdist.qty * -1.0), reserve_qty)
          FROM shipitem, itemloc
            JOIN reserve ON (itemloc_id=reserve_supply_id AND reserve_supply_type='I')
          WHERE ( (shipitem_invhist_id=_itemlocdist.invhistid)
            AND   (itemloc_id=_itemlocid)
            AND   (reserve_demand_type=_itemlocdist.ordertype)
            AND   (reserve_demand_id=_itemlocdist.orderid) );
        END IF;

--  Update the reservation
        UPDATE reserve
        SET reserve_qty = (reserve_qty + _itemlocdist.qty)
        WHERE ( (reserve_supply_id=_itemlocid)
          AND   (reserve_supply_type='I')
          AND   (reserve_demand_type=_itemlocdist.ordertype)
          AND   (reserve_demand_id=_itemlocdist.orderid) );
          
--  Delete reservation if fully distributed
        DELETE FROM reserve
        WHERE ( (reserve_supply_id=_itemlocid)
          AND   (reserve_supply_type='I')
          AND   (reserve_demand_type=_itemlocdist.ordertype)
          AND   (reserve_demand_id=_itemlocdist.orderid)
          AND   (reserve_qty=0) );
      END IF;
    END IF;

--  Adjust QOH if this itemlocdist is to/from a non-netable location
    IF ( SELECT (NOT location_netable)
         FROM itemloc, location
         WHERE ((itemloc_location_id=location_id)
          AND (itemloc_id=_itemlocid)) ) THEN

--  Record the invhist record for the netable->non-netable (or visaversa)
      INSERT INTO invhist
      ( invhist_itemsite_id,
        invhist_transtype, invhist_invqty,
        invhist_qoh_before, invhist_qoh_after,
        invhist_docnumber, invhist_comments,
        invhist_invuom, invhist_unitcost,
        invhist_costmethod, invhist_value_before, invhist_value_after,
        invhist_series )
      SELECT itemsite_id,
             'NN', (_itemlocdist.qty * -1),
             itemsite_qtyonhand, (itemsite_qtyonhand - _itemlocdist.qty),
             invhist_docnumber, invhist_comments,
             uom_name, stdCost(item_id),
             itemsite_costmethod, itemsite_value,
             (itemsite_value + (_itemlocdist.qty * -1 * CASE WHEN(itemsite_costmethod='A') THEN avgcost(itemsite_id)
                                                             ELSE stdCost(itemsite_item_id)
                                                        END)),
             _itemlocdist.series
      FROM item, itemsite, invhist, uom
      WHERE ( (itemsite_item_id=item_id)
       AND (item_inv_uom_id=uom_id)
       AND (itemsite_controlmethod <> 'N')
       AND (itemsite_id=_itemlocdist.itemsiteid)
       AND (invhist_id=_itemlocdist.invhistid) );

--  Update the itemsite_qoh
      IF (NOT _itemlocdist.itemsite_freeze) THEN
        UPDATE itemsite
        SET itemsite_qtyonhand = (itemsite_qtyonhand - _itemlocdist.qty),
            itemsite_nnqoh = (itemsite_nnqoh + _itemlocdist.qty)
        FROM itemloc
        WHERE ((itemloc_itemsite_id=itemsite_id)
         AND (itemloc_id=_itemlocid));
      END IF;
    END IF;

--  Cache the running qty.
    _runningQty := _runningQty + _itemlocdist.qty;

--  Dene with the child itemlocdist, so delete it
    DELETE FROM itemlocdist
    WHERE (itemlocdist_id=_itemlocdist.itemlocdistid);

--  If the target itemloc is now at qty=0, delete it if its parent
--  itemsite is not frozen
    IF (NOT _itemlocdist.itemsite_freeze) THEN
      DELETE FROM itemloc
      WHERE ( (itemloc_qty=0)
       AND (itemloc_id=_itemlocid) );
    END IF;

  END LOOP;

--  If the running qty for the detailed distributions is the same as the
--  total qty to distribute indicated by the parent itemlocdist, then the
--  parent itemlocdist has been fully distributed and should be deleted.
  IF ( ( SELECT itemlocdist_qty
         FROM itemlocdist
         WHERE (itemlocdist_id=pItemlocdistid) ) = _runningQty) THEN
    DELETE FROM itemlocdist
    WHERE (itemlocdist_id=pItemlocdistid);
  ELSE
--  There is still some more qty to distribute in the parent itemlocdist.
--  Update the qty to distribute with the qty that has been distributed.
    UPDATE itemlocdist
    SET itemlocdist_qty = (itemlocdist_qty - _runningQty)
    WHERE (itemlocdist_id=pItemlocdistid);
  END IF;

  RETURN _distCounter;

END;
$_$;


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

--
-- TOC entry 1504 (class 1255 OID 146566068)
-- Dependencies: 4536 8
-- Name: distributevoucherline(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION distributevoucherline(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
  pVoucherId ALIAS FOR $1;
  pPoitemId ALIAS FOR $2;
  pCurrId ALIAS FOR $3;
  _count INTEGER;
  _costelemId INTEGER;
  _close BOOLEAN;
  _r RECORD;
  _qtyOrdered NUMERIC;
  _voitemId INTEGER;
  _taxtypeid INTEGER;

BEGIN

--  Make sure the P/O and Voucher are same currency

  SELECT COALESCE(COUNT(*),0) INTO _count
        FROM poitem JOIN pohead ON (pohead_id=poitem_pohead_id)
        WHERE ((poitem_id=pPoitemid)
        AND (pohead_curr_id=pCurrId));
  IF (_count = 0) THEN
        RETURN -3;
  END IF;

--  Validate and get cost element

        SELECT COALESCE(COUNT(*),0) INTO _count
                FROM itemcost, item, itemsite, poitem
                WHERE ((itemcost_item_id=item_id)
                AND (item_id=itemsite_item_id)
                AND (itemsite_id=poitem_itemsite_id)
                AND (poitem_id=pPoitemId));

        IF (_count > 1) THEN
                RETURN -5;
        ELSEIF (_count = 1) THEN
                SELECT itemcost_costelem_id INTO _costelemId
                        FROM itemcost, item, itemsite, poitem
                        WHERE ((itemcost_item_id=item_id)
                        AND (item_id=itemsite_item_id)
                        AND (itemsite_id=poitem_itemsite_id)
                        AND (poitem_id=pPoitemId));
        ELSE
                SELECT costelem_id INTO _costelemId
                        FROM costelem
                        WHERE (costelem_type='Material');
        END IF;


--  Clear previous distributions

        UPDATE recv SET recv_vohead_id=NULL, recv_voitem_id=NULL
                WHERE ((recv_vohead_id=pVoucherId)
                AND (recv_orderitem_id=pPoitemId)
		AND (recv_order_type='PO'));
        UPDATE poreject SET poreject_vohead_id=NULL, poreject_voitem_id=NULL
                WHERE ((poreject_vohead_id=pVoucherId)
                AND (poreject_poitem_id=pPoitemId));
        DELETE FROM vodist
                WHERE ((vodist_poitem_id=pPoitemId)
                AND (vodist_vohead_id=pVoucherId));
        DELETE FROM voitem
                WHERE ((voitem_poitem_id=pPoitemId)
                AND (voitem_vohead_id=pVoucherId));

--  Determine Line balances

        SELECT  COALESCE(SUM(qty_received),0) AS qty_received,
                COALESCE(SUM(qty_rejected),0) AS qty_rejected,
                COALESCE(SUM(qty_vouchered),0) AS qty_vouchered,
                round(COALESCE(SUM(balance),0),2) AS balance,
                round(COALESCE(SUM(freight),0),2) AS freight INTO _r
         FROM   (
                SELECT  recv_qty AS qty_received,
                        0 AS qty_rejected,
                        0 AS qty_vouchered,
                        (recv_qty * COALESCE(recv_purchcost, poitem_unitprice)) AS balance,
                        recv_freight AS freight
                FROM poitem JOIN recv ON ((recv_orderitem_id=poitem_id) AND
                                          (recv_order_type='PO'))
                WHERE ( (recv_vohead_id IS NULL)
                        AND (NOT recv_invoiced)
                        AND (recv_posted)
                        AND (poitem_id=pPoitemId) )

                UNION ALL

                SELECT  0 AS qty_received,
                        (poreject_qty) AS qty_rejected,
                        0 AS qty_vouchered,
                        (poreject_qty * -1 * COALESCE(recv_purchcost, poitem_unitprice)) AS balance,
                        0 AS freight
                FROM poitem JOIN poreject ON (poreject_poitem_id=poitem_id)
                            LEFT OUTER JOIN recv ON (recv_id=poreject_recv_id)
                WHERE ( (poreject_posted)
                        AND (poreject_vohead_id IS NULL)
                        AND (NOT poreject_invoiced)
                        AND (poitem_id=pPoitemId) )

                UNION ALL

                SELECT  0 AS qty_received,
                        0 AS qty_rejected,
                        voitem_qty AS qty_vouchered,
                        0 AS balance,
                        0 AS freight
                FROM voitem, poitem
                WHERE ( (voitem_poitem_id=pPoitemId)
                        AND (poitem_id=voitem_poitem_id) )
                ) AS data;

                SELECT poitem_qty_ordered INTO _qtyOrdered
                FROM poitem
                WHERE (poitem_id=pPoitemId);

        IF _r.balance < 0 THEN
                RETURN -4;
        ELSEIF ( ((_r.qty_received <> 0) OR (_r.qty_received <> 0)) AND (_r.qty_received - _r.qty_rejected = 0) ) THEN
                RETURN -2;
        ELSEIF ((_r.qty_received - _r.qty_rejected) = 0) THEN
                RETURN 0;
        END IF;

-- Determine whether to close P/O item

        IF (_r.qty_received -_r.qty_rejected + _r.qty_vouchered) >= _qtyOrdered THEN
                _close:=True;
        ELSE
                _close:=False;
        END IF;


-- Create distribution

        INSERT INTO vodist
                (vodist_poitem_id,vodist_vohead_id,vodist_costelem_id,vodist_amount,vodist_qty,vodist_expcat_id)
                VALUES (pPoitemId,pVoucherId,_costelemId,_r.balance,(_r.qty_received -_r.qty_rejected),-1);

-- Create voucher item
        SELECT poitem_taxtype_id INTO _taxtypeid
        FROM poitem
        WHERE (poitem_id=pPoitemId);

        SELECT NEXTVAL('voitem_voitem_id_seq') INTO _voitemId;

        INSERT INTO voitem (voitem_id,voitem_vohead_id,voitem_poitem_id,voitem_close,voitem_qty,voitem_freight, voitem_taxtype_id)
                VALUES (_voitemId,pVoucherId,pPoitemId,_close,(_r.qty_received -_r.qty_rejected),_r.freight, _taxtypeid);

-- Tag receipt records

        UPDATE recv
        SET recv_vohead_id=pVoucherId, recv_voitem_id=_voitemId
        WHERE ((recv_orderitem_id=pPoitemId)
	  AND  (recv_order_type='PO')
          AND  (recv_vohead_id IS NULL));

        UPDATE poreject
        SET poreject_vohead_id=pVoucherId,poreject_voitem_id=_voitemId
        WHERE ((poreject_poitem_id=pPoitemId)
        AND (NOT poreject_invoiced)
        AND (poreject_vohead_id IS NULL));


  RETURN 1;

END;
$_$;


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

--
-- TOC entry 1505 (class 1255 OID 146566069)
-- Dependencies: 4536 8
-- Name: dopostcosts(boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION dopostcosts(boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, 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

  pMaterial ALIAS FOR $1;
  plowerMaterial ALIAS FOR $2;
  pdirectLabor ALIAS FOR $3;
  plowerDirectLabor ALIAS FOR $4;
  poverhead ALIAS FOR $5;
  plowerOverhead ALIAS FOR $6;
  pmachOverhead ALIAS FOR $7;
  plowerMachOverhead ALIAS FOR $8;
  pUser ALIAS FOR $9;
  plowerUser ALIAS FOR $10;
  prollUp ALIAS FOR $11;
  _item RECORD;
  _result INTEGER := 0;

BEGIN

  PERFORM resetLowLevelCode(-1);

  FOR _item IN SELECT costUpdate_item_id
            FROM costUpdate
            ORDER BY costUpdate_lowlevel_code DESC LOOP
    PERFORM doPostCosts(_item.costUpdate_item_id, FALSE,
		       pMaterial, plowerMaterial, pdirectLabor,
		       plowerDirectLabor, poverhead, plowerOverhead,
		       pmachOverhead, plowerMachOverhead,
		       puser, plowerUser, prollUp);
  END LOOP;

  RETURN _result;

END;
 $_$;


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

--
-- TOC entry 1506 (class 1255 OID 146566070)
-- Dependencies: 4536 8
-- Name: dopostcosts(integer, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION dopostcosts(integer, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, 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

  pItemId ALIAS FOR $1;
  pResetLowLevel ALIAS FOR $2;
  pMaterial ALIAS FOR $3;
  plowerMaterial ALIAS FOR $4;
  pdirectLabor ALIAS FOR $5;
  plowerDirectLabor ALIAS FOR $6;
  poverhead ALIAS FOR $7;
  plowerOverhead ALIAS FOR $8;
  pmachOverhead ALIAS FOR $9;
  plowerMachOverhead ALIAS FOR $10;
  pUser ALIAS FOR $11;
  plowerUser ALIAS FOR $12;
  prollUp ALIAS FOR $13;
  _itemcost RECORD;
  _result INTEGER;

BEGIN

    IF (pResetLowLevel) THEN
	PERFORM resetLowLevelCode(pItemId);
    END IF;

    FOR _itemcost IN SELECT itemcost_id, costelem_sys, costelem_type,
			    itemcost_lowlevel,
			    costUpdate_lowlevel_code, costUpdate_item_type
            FROM itemcost, costelem, costUpdate
            WHERE itemcost_item_id = pItemId
	      AND costUpdate_item_id = itemcost_item_id
              AND itemcost_costelem_id = costelem_id LOOP
      IF (NOT _itemcost.costelem_sys) THEN
        IF ( (pUser) AND ( NOT _itemcost.itemcost_lowlevel) ) THEN
          PERFORM postCost(_itemcost.itemcost_id);
          _result := _result + 1;
        END IF;
        IF ( (plowerUser) AND ( _itemcost.itemcost_lowlevel) ) THEN
          PERFORM postCost(_itemcost.itemcost_id);
          _result := _result + 1;
        END IF;
      END IF;

      IF (_itemcost.costelem_type = 'Material') THEN
        IF ( (pMaterial) AND ( NOT _itemcost.itemcost_lowlevel) ) THEN
          PERFORM postCost(_itemcost.itemcost_id);
          _result := _result + 1;
        END IF;
        IF ( (plowerMaterial) AND ( _itemcost.itemcost_lowlevel) ) THEN
          PERFORM postCost(_itemcost.itemcost_id);
          _result := _result + 1;
        END IF;
      END IF;

      IF (_itemcost.costelem_type = 'Direct Labor') THEN
        IF ( (pdirectLabor) AND ( NOT _itemcost.itemcost_lowlevel) ) THEN
          PERFORM postCost(_itemcost.itemcost_id);
          _result := _result + 1;
        END IF;
        IF ( (plowerDirectLabor) AND ( _itemcost.itemcost_lowlevel) ) THEN
          PERFORM postCost(_itemcost.itemcost_id);
          _result := _result + 1;
        END IF;
      END IF;

      IF (_itemcost.costelem_type = 'Overhead') THEN
        IF ( (poverhead) AND ( NOT _itemcost.itemcost_lowlevel) ) THEN
          PERFORM postCost(_itemcost.itemcost_id);
          _result := _result + 1;
        END IF;
        IF ( (plowerOverhead) AND ( _itemcost.itemcost_lowlevel) ) THEN
          PERFORM postCost(_itemcost.itemcost_id);
          _result := _result + 1;
        END IF;
      END IF;

      IF (_itemcost.costelem_type = 'Machine Overhead') THEN
        IF ( (pmachOverhead) AND ( NOT _itemcost.itemcost_lowlevel) ) THEN
          PERFORM postCost(_itemcost.itemcost_id);
          _result := _result + 1;
        END IF;
        IF ( (plowerMachOverhead) AND ( _itemcost.itemcost_lowlevel) ) THEN
          PERFORM postCost(_itemcost.itemcost_id);
          _result := _result + 1;
        END IF;
      END IF;

    END LOOP;

    IF (prollUp) THEN    
      PERFORM rollUpStandardCost(pItemId);
      _result := _result + 1;
    END IF;

    RETURN _result;

END;
 $_$;


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

--
-- TOC entry 1507 (class 1255 OID 146566071)
-- Dependencies: 4536 8
-- Name: doupdatecosts(boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION doupdatecosts(boolean, boolean, boolean, boolean, boolean, boolean, boolean, 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

  plowerMaterial ALIAS FOR $1;
  pdirectLabor ALIAS FOR $2;
  plowerDirectLabor ALIAS FOR $3;
  poverhead ALIAS FOR $4;
  plowerOverhead ALIAS FOR $5;
  pmachOverhead ALIAS FOR $6;
  plowerMachOverhead ALIAS FOR $7;
  plowerUser ALIAS FOR $8;
  prollUp ALIAS FOR $9;

BEGIN
  RETURN doUpdateCosts(plowerMaterial, pdirectLabor, plowerDirectLabor,
		       poverhead, plowerOverhead, pmachOverhead,
		       plowerMachOverhead, plowerUser, prollUp, TRUE);
END;
$_$;


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

--
-- TOC entry 1509 (class 1255 OID 146566073)
-- Dependencies: 4536 8
-- Name: doupdatecosts(boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION doupdatecosts(boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, 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

  plowerMaterial ALIAS FOR $1;
  pdirectLabor ALIAS FOR $2;
  plowerDirectLabor ALIAS FOR $3;
  poverhead ALIAS FOR $4;
  plowerOverhead ALIAS FOR $5;
  pmachOverhead ALIAS FOR $6;
  plowerMachOverhead ALIAS FOR $7;
  plowerUser ALIAS FOR $8;
  prollUp ALIAS FOR $9;
  pActual ALIAS FOR $10;
  _item RECORD;
  _bom RECORD;
  _result INTEGER := 0;

BEGIN

  PERFORM resetLowLevelCode(-1);

  FOR _item IN SELECT costUpdate_item_id
            FROM costUpdate
            ORDER BY costUpdate_lowlevel_code DESC LOOP
    PERFORM doUpdateCosts(_item.costUpdate_item_id, false, plowerMaterial,
		  pdirectLabor, plowerDirectLabor, poverhead, plowerOverhead,
		  pmachOverhead, plowerMachOverhead, plowerUser, prollUp,
		  pActual);
  END LOOP;

  RETURN _result;

END;
 $_$;


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

--
-- TOC entry 1508 (class 1255 OID 146566072)
-- Dependencies: 4536 8
-- Name: doupdatecosts(integer, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION doupdatecosts(integer, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, 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

  pItemId ALIAS FOR $1;
  pResetLowLevel ALIAS FOR $2;
  plowerMaterial ALIAS FOR $3;
  pdirectLabor ALIAS FOR $4;
  plowerDirectLabor ALIAS FOR $5;
  poverhead ALIAS FOR $6;
  plowerOverhead ALIAS FOR $7;
  pmachOverhead ALIAS FOR $8;
  plowerMachOverhead ALIAS FOR $9;
  plowerUser ALIAS FOR $10;
  prollUp ALIAS FOR $11;

BEGIN
    RETURN doUpdateCosts(pItemId, pResetLowLevel, plowerMaterial, pdirectLabor,
			 plowerDirectLabor, poverhead, plowerOverhead,
			 pmachOverhead, plowerMachOverhead, plowerUser, prollUp,
			 TRUE);
END;
$_$;


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

--
-- TOC entry 1510 (class 1255 OID 146566074)
-- Dependencies: 4536 8
-- Name: doupdatecosts(integer, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION doupdatecosts(integer, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, boolean, 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

  pItemId ALIAS FOR $1;
  pResetLowLevel ALIAS FOR $2;
  plowerMaterial ALIAS FOR $3;
  pdirectLabor ALIAS FOR $4;
  plowerDirectLabor ALIAS FOR $5;
  poverhead ALIAS FOR $6;
  plowerOverhead ALIAS FOR $7;
  pmachOverhead ALIAS FOR $8;
  plowerMachOverhead ALIAS FOR $9;
  plowerUser ALIAS FOR $10;
  prollUp ALIAS FOR $11;
  pUpdateActual ALIAS FOR $12;
  _item RECORD;
  _bom RECORD;
  _result INTEGER := 0;
  _resultFromReset INTEGER;
  _counterNum INTEGER;
  _feedBackNum INTEGER;

BEGIN

    IF (pResetLowLevel) THEN
	PERFORM resetLowLevelCode(pItemId);
    END IF;

    SELECT costUpdate_item_id AS item_id, costUpdate_item_type AS item_type
	INTO _item
    FROM costUpdate
    WHERE costUpdate_item_id = pItemId;

    IF ((plowerMaterial) AND ((_item.item_type <> 'P') AND (_item.item_type <> 'O'))) THEN    
      PERFORM updateSorACost(_item.item_id, 'Material', TRUE, lowerCost(_item.item_id, 'Material', pUpdateActual), pUpdateActual);
    END IF;

    IF (pdirectLabor) THEN    
      PERFORM updateSorACost(_item.item_id, 'Direct Labor', FALSE, xtmfg.directLaborCost(_item.item_id), pUpdateActual);
    END IF;

    IF (plowerDirectLabor) THEN    
      PERFORM updateSorACost(_item.item_id, 'Direct Labor', TRUE, lowerCost(_item.item_id, 'Direct Labor', pUpdateActual), pUpdateActual);
    END IF;

    IF (poverhead) THEN    
      PERFORM updateSorACost(_item.item_id, 'Overhead', FALSE, xtmfg.overheadCost(_item.item_id), pUpdateActual);
    END IF;

    IF (plowerOverhead) THEN    
      PERFORM updateSorACost(_item.item_id, 'Overhead', TRUE, lowerCost(_item.item_id, 'Overhead', pUpdateActual), pUpdateActual);
    END IF;

    IF (pmachOverhead) THEN    
      PERFORM updateSorACost(_item.item_id, 'Machine Overhead', FALSE, xtmfg.machineOverheadCost(_item.item_id), pUpdateActual);
    END IF;

    IF (plowerMachOverhead) THEN    
      PERFORM updateSorACost(_item.item_id, 'Machine Overhead', TRUE, lowerCost(_item.item_id, 'Machine Overhead', pUpdateActual), pUpdateActual);
    END IF;

    IF (plowerUser) THEN    
      PERFORM updateLowerUserCosts(_item.item_id, pUpdateActual);
    END IF;

    IF (prollUp) THEN    
      PERFORM rollUpSorACost(_item.item_id, pUpdateActual);
    END IF;

    RETURN _result;

END;
 $_$;


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

--
-- TOC entry 1443 (class 1255 OID 146566075)
-- Dependencies: 4536 8
-- Name: dropifexists(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1477 (class 1255 OID 146566076)
-- Dependencies: 4536 8
-- Name: dropifexists(text, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1511 (class 1255 OID 146566077)
-- Dependencies: 4536 8
-- Name: dropifexists(text, text, text, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION dropifexists(text, text, text, boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pType         ALIAS FOR $1;
  pObject       ALIAS FOR $2;
  pSchema       ALIAS FOR $3;
  pCascade      ALIAS FOR $4;
  _table	TEXT;
  _query	TEXT;
BEGIN
  IF (UPPER(pType) = 'INDEX') THEN
    _query = 'DROP INDEX ' || quote_ident(LOWER(pSchema)) || '.' || quote_ident(LOWER(pObject));
    
    BEGIN
      EXECUTE _query;
    EXCEPTION WHEN undefined_object OR invalid_schema_name THEN
		RETURN 0;
	      WHEN OTHERS THEN RAISE EXCEPTION '% %', SQLSTATE, SQLERRM;
    END;

  ELSEIF (UPPER(pType) = 'TABLE') THEN
    _query = 'DROP TABLE ' || quote_ident(LOWER(pSchema)) || '.' || quote_ident(LOWER(pObject)); 
    
    IF (pCascade) THEN
      _query = _query || ' CASCADE';
    END IF;

    BEGIN
      EXECUTE _query;
    EXCEPTION WHEN undefined_table OR invalid_schema_name THEN
		RETURN 0;
	      WHEN OTHERS THEN RAISE EXCEPTION '% %', SQLSTATE, SQLERRM;
    END;

  ELSIF (UPPER(pType) = 'VIEW') THEN
    _query = 'DROP VIEW ' || quote_ident(LOWER(pSchema)) || '.' || quote_ident(LOWER(pObject));

    IF (pCascade) THEN
      _query = _query || ' CASCADE';
    END IF;
    
    BEGIN
      EXECUTE _query;
    EXCEPTION WHEN undefined_table OR invalid_schema_name THEN
		RETURN 0;
	      WHEN OTHERS THEN RAISE EXCEPTION '% %', SQLSTATE, SQLERRM;
    END;

  ELSIF (UPPER(pType) = 'TRIGGER') THEN
    SELECT relname INTO _table
    FROM pg_trigger, pg_class
    WHERE ((tgrelid=pg_class.oid)
      AND  (UPPER(tgname)=UPPER(pObject)));
    IF (NOT FOUND) THEN
      _table := '[no table]';
    END IF;

    _query = 'DROP TRIGGER ' || quote_ident(LOWER(pObject)) ||
	     ' ON ' || quote_ident(LOWER(pSchema)) || '.' || quote_ident(LOWER(_table));
    BEGIN
      EXECUTE _query;
    EXCEPTION WHEN undefined_object THEN
		RETURN 0;
	      WHEN undefined_table OR invalid_schema_name THEN
		RETURN 0;
	      WHEN OTHERS THEN RAISE EXCEPTION '% %', SQLSTATE, SQLERRM;
    END;

  ELSIF (UPPER(pType) = 'FUNCTION') THEN
    _query = 'DROP FUNCTION ' || (LOWER(pSchema)) || '.' ||
                                   (LOWER(pObject));
    BEGIN
      EXECUTE _query;
    EXCEPTION WHEN undefined_object OR undefined_function OR invalid_schema_name THEN
		RETURN 0;
	      WHEN OTHERS THEN RAISE EXCEPTION '% %', SQLSTATE, SQLERRM;
    END;

  ELSIF (UPPER(pType) = 'CONSTRAINT') THEN
    IF( (SELECT count(*)
           FROM pg_constraint, pg_class, pg_namespace
          WHERE((conrelid=pg_class.oid)
            AND (connamespace=pg_namespace.oid)
            AND (conname=pObject)
            AND (nspname=pSchema))
         ) > 1 ) THEN
      RAISE EXCEPTION 'dropIfExists called on constraint name that matches more than 1 constraint.';
    END IF;
    SELECT relname INTO _table
    FROM pg_constraint, pg_class, pg_namespace
    WHERE ((conrelid=pg_class.oid)
      AND  (connamespace=pg_namespace.oid)
      AND  (conname=pObject)
      AND  (nspname=pSchema));
    IF (NOT FOUND) THEN
      RETURN 0;
    END IF;
    _query = 'ALTER TABLE ' || quote_ident(LOWER(pSchema)) || '.' || quote_ident(LOWER(_table))
             || ' DROP CONSTRAINT ' || quote_ident(LOWER(pObject));
    BEGIN
      EXECUTE _query;
    EXCEPTION WHEN undefined_table OR invalid_schema_name THEN
		RETURN 0;
	      WHEN OTHERS THEN RAISE EXCEPTION '% %', SQLSTATE, SQLERRM;
    END;

  ELSIF (UPPER(pType) = 'SCHEMA') THEN
    _query = 'DROP SCHEMA ' || quote_ident(LOWER(pObject));
    BEGIN
      EXECUTE _query;
    EXCEPTION WHEN invalid_schema_name THEN
                RETURN 0;
              WHEN OTHERS THEN RAISE EXCEPTION '% %', SQLSTATE, SQLERRM;
    END;

  ELSIF (UPPER(pType) = 'TYPE') THEN
    _query = 'DROP TYPE ' || quote_ident(LOWER(pSchema)) || '.' ||
                               quote_ident(LOWER(pObject));
    IF (pCascade) THEN
      _query = _query || ' CASCADE';
    END IF;
    
    BEGIN
      EXECUTE _query;
    EXCEPTION WHEN undefined_object OR invalid_schema_name THEN
                RETURN 0;
              WHEN OTHERS THEN RAISE EXCEPTION '% %', SQLSTATE, SQLERRM;
    END;

  ELSE
    RAISE EXCEPTION 'dropIfExists(%, %): unknown pType %', pType, pObject, pType;
  END IF;

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 1512 (class 1255 OID 146566078)
-- Dependencies: 4536 8
-- Name: dropifexists(text, text, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION dropifexists(text, text, text, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pType         ALIAS FOR $1;
  pObject       ALIAS FOR $2;
  pSchema       ALIAS FOR $3;
  pRelation     ALIAS FOR $4;
  _table        TEXT;
  _query	TEXT;
BEGIN
  IF (UPPER(pType) = 'CONSTRAINT') THEN
    SELECT relname INTO _table
    FROM pg_constraint, pg_class, pg_namespace
    WHERE ((conrelid=pg_class.oid)
      AND  (connamespace=pg_namespace.oid)
      AND  (conname=pObject)
      AND  (relname=pRelation)
      AND  (nspname=pSchema));
    IF (NOT FOUND) THEN
      RETURN 0;
    END IF;
    _query = 'ALTER TABLE ' || quote_ident(LOWER(pSchema)) || '.' || quote_ident(LOWER(pRelation))
             || ' DROP CONSTRAINT ' || quote_ident(LOWER(pObject));
    BEGIN
      EXECUTE _query;
    EXCEPTION WHEN undefined_table OR invalid_schema_name THEN
		RETURN 0;
	      WHEN OTHERS THEN RAISE EXCEPTION '% %', SQLSTATE, SQLERRM;
    END;

  ELSE
    RAISE EXCEPTION 'dropIfExists(%, %, %, %): pType % is not supported when relation is specified', pType, pObject, pSchema, pRelation, pType;
  END IF;

  RETURN 1;
END;
$_$;


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

--
-- TOC entry 1513 (class 1255 OID 146566079)
-- Dependencies: 4536 8
-- Name: dropstdopntable(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION dropstdopntable() RETURNS boolean
    LANGUAGE plpgsql
    AS $$
BEGIN
  IF((SELECT metric_value != 'Manufacturing' FROM metric WHERE metric_name = 'Application')) THEN
    PERFORM dropIfExists('TABLE', 'stdopn');
    RETURN true;
  END IF;
  RETURN false;
END;
$$;


ALTER FUNCTION public.dropstdopntable() OWNER TO admin;

--
-- TOC entry 1514 (class 1255 OID 146566080)
-- Dependencies: 4536 8
-- Name: editccnumber(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION editccnumber(text, text) RETURNS text
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pCcardnum ALIAS FOR $1;
  pCcardtype ALIAS FOR $2;
  card_length INTEGER;
  card_valid boolean := false;
  starting_digits TEXT;
  _sum INTEGER := 0;
  _digit INTEGER := 0;
  _timesTwo BOOLEAN := false;
BEGIN

-- Check the card type
  IF (pCcardtype NOT IN ('M', 'V', 'A', 'D')) THEN
-- Unknown Card Type
   RETURN -1;
  END IF;

  card_length := length(pCcardnum);

-- Process Master Card Checking length
-- Process Master Card Starting digits
  IF (pCcardtype = 'M') THEN
    IF (card_length != 16) THEN
-- Bad Card Length Card Type
      RETURN -2;
    END IF;
    starting_digits := substr(pCcardnum, 1, 2);
    IF (starting_digits < '51' OR starting_digits > '55') THEN
-- Bad Starting digits
      RETURN -6;
    END IF;
  END IF;

-- Process Visa Card Checking length
-- Process Visa Card Starting digits
  IF (pCcardtype = 'V') THEN
    IF (card_length != 13 AND card_length != 16) THEN
-- Bad Card Length Card Type
      RETURN -3;
    END IF;
    starting_digits := substr(pCcardnum, 1, 1);
    IF (starting_digits != '4') THEN
-- Bad Starting digits
      RETURN -7;
    END IF;
  END IF;

-- Process American Express Card Checking length
-- Process American Express Card Starting digits
  IF (pCcardtype = 'A') THEN
    IF (card_length != 15) THEN
-- Bad Card Length Card Type
      RETURN -4;
    END IF;
    starting_digits := substr(pCcardnum, 1, 2);
    IF (starting_digits != '34' AND starting_digits != '37') THEN
-- Bad Starting digits
      RETURN -8;
    END IF;
  END IF;

-- Process Discover Card Checking length
-- Process Discover Card Starting digits
  IF (pCcardtype = 'D') THEN
    IF (card_length != 16) THEN
-- Bad Card Length Card Type
      RETURN -5;
    END IF;
    starting_digits := substr(pCcardnum, 1, 4);
    IF (starting_digits != '6011') THEN
-- Bad Starting digits
      RETURN -9;
    END IF;
  END IF;

-- Now comes the fun part of doing the "check" for the check sum
-- perform a luhn checksum
  FOR i IN REVERSE card_length .. 1 LOOP
    _digit := int4(substr(pCcardnum, i, 1));
    IF (_timesTwo) THEN
      _digit := _digit * 2;
      IF (_digit > 9) THEN
        _digit := _digit - 9;
      END IF;
    END IF;
    _sum := _sum + _digit;
    _timesTwo := NOT _timesTwo;
  END LOOP;

  IF (mod(_sum, 10) != 0) THEN
    RETURN -10;
  END IF;

  RETURN 0; -- No Error

END;
$_$;


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

--
-- TOC entry 1515 (class 1255 OID 146566081)
-- Dependencies: 4536 8
-- Name: enablepackage(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION enablepackage(text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  ppkgname  ALIAS FOR $1;
  _i        INTEGER := 0;
  _tabs     TEXT[] := ARRAY['cmd',  'cmdarg', 'image',  'metasql',
                            'priv', 'report', 'script', 'uiform'];

BEGIN
  IF (version() < 'PostgreSQL 8.2') THEN
    RETURN -1;
  END IF;

  FOR _i IN ARRAY_LOWER(_tabs,1)..ARRAY_UPPER(_tabs,1) LOOP
    EXECUTE 'ALTER TABLE ' || ppkgname || '.pkg' || _tabs[_i] ||
            ' INHERIT public.' || _tabs[_i] || ';';
  END LOOP;

  RETURN 0;
END;
$_$;


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

--
-- TOC entry 1516 (class 1255 OID 146566082)
-- Dependencies: 4536 8
-- Name: enablepackage(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION enablepackage(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
  ppkgheadid    ALIAS FOR $1;
  _pkgname      TEXT;

BEGIN
  SELECT pkghead_name INTO _pkgname
  FROM pkghead
  WHERE (pkghead_id=ppkgheadid);
  IF (NOT FOUND) THEN
    RETURN -2;
  END IF;

  RETURN enablePackage(_pkgname);
END;
$_$;


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

--
-- TOC entry 1517 (class 1255 OID 146566083)
-- Dependencies: 8
-- Name: encrypt(bytea, bytea, text); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1518 (class 1255 OID 146566084)
-- Dependencies: 8
-- Name: encrypt_iv(bytea, bytea, bytea, text); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1519 (class 1255 OID 146566085)
-- Dependencies: 8
-- Name: endoftime(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION endoftime() RETURNS date
    LANGUAGE sql IMMUTABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
SELECT DATE('2100-01-01') as result;
$$;


ALTER FUNCTION public.endoftime() OWNER TO admin;

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

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

  UPDATE invcnt
  SET invcnt_qoh_after = pQty,
      invcnt_comments = CASE WHEN ( (LENGTH(invcnt_comments) = 0) AND
                                    (LENGTH(pComments) > 0) ) THEN pComments
                             WHEN (LENGTH(pComments) > 0) THEN (invcnt_comments || E'\n' || pComments)
                             ELSE invcnt_comments
                        END,
      invcnt_cntdate = CURRENT_TIMESTAMP,
      invcnt_cnt_username = getEffectiveXtUser()
  WHERE (invcnt_id=pInvcntid);

  RETURN 0;
END;
$_$;


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

--
-- TOC entry 1521 (class 1255 OID 146566087)
-- Dependencies: 4536 8
-- Name: enterporeceipt(integer, numeric); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

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

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


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

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

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


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

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

CREATE FUNCTION enterporeceipt(integer, numeric, numeric, text, 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.
BEGIN
  RETURN enterReceipt('PO', $1, $2, $3, $4, $5, $6);
END;
$_$;


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

--
-- TOC entry 1503 (class 1255 OID 146566091)
-- Dependencies: 4536 8
-- Name: enterporeturn(integer, numeric, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1523 (class 1255 OID 146566092)
-- Dependencies: 4536 8
-- Name: enterporeturn(integer, numeric, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION enterporeturn(integer, numeric, 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
  pPoitemid ALIAS FOR $1;
  pQty ALIAS FOR $2;
  pRjctcodeid ALIAS FOR $3;
  pRecvid ALIAS FOR $4;
  _porejectid INTEGER;

BEGIN

  SELECT NEXTVAL('poreject_poreject_id_seq') INTO _porejectid;

  INSERT INTO poreject
  ( poreject_id, poreject_date, poreject_ponumber, poreject_poitem_id, poreject_trans_username,
    poreject_agent_username, poreject_itemsite_id,
    poreject_vend_id, poreject_vend_item_number, poreject_vend_item_descrip, poreject_vend_uom,
    poreject_qty, poreject_rjctcode_id, poreject_posted, poreject_invoiced, poreject_recv_id )
  SELECT _porejectid, CURRENT_TIMESTAMP, pohead_number, poitem_id, getEffectiveXtUser(),
         pohead_agent_username, poitem_itemsite_id,
         pohead_vend_id, poitem_vend_item_number, poitem_vend_item_descrip, poitem_vend_uom,
         pQty, pRjctcodeid, FALSE, FALSE, pRecvid
  FROM poitem JOIN pohead ON (pohead_id=poitem_pohead_id)
  WHERE (poitem_id=pPoitemid);

  RETURN _porejectid;

END;
$_$;


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

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

CREATE FUNCTION enterreceipt(text, integer, numeric, numeric, text, 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.
BEGIN
  RETURN enterReceipt($1, $2, $3, $4, $5, $6, $7, NULL);
END;
$_$;


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

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

CREATE FUNCTION enterreceipt(text, integer, numeric, numeric, text, integer, date, 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
  pordertype	ALIAS FOR $1;
  porderitemid	ALIAS FOR $2;
  pQty		ALIAS FOR $3;
  pFreight	ALIAS FOR $4;
  pNotes	ALIAS FOR $5;
  pcurrid	ALIAS FOR $6;	-- NULL is handled by SELECT ... INTO _o
  precvdate	ALIAS FOR $7;	-- NULL is handled by INSERT INTO recv
  pRecvCost	ALIAS FOR $8;
  _timestamp    TIMESTAMP;
  _o		RECORD;
  _recvid	INTEGER;
  _warehouseid 	INTEGER;
  _recvcost	NUMERIC;

BEGIN
  IF(precvdate IS NULL OR precvdate = CURRENT_DATE) THEN
    _timestamp := CURRENT_TIMESTAMP;
  ELSE
    _timestamp := precvdate;
  END IF;
  SELECT NEXTVAL('recv_recv_id_seq') INTO _recvid;

  DELETE FROM recv
  WHERE ((NOT recv_posted)
    AND  (recv_order_type=pordertype)
    AND  (recv_orderitem_id=porderitemid) );

  IF (pQty > 0) THEN
    IF (pordertype='PO') THEN
      SELECT pohead_number AS orderhead_number,
  	   poitem_id AS orderitem_id,
	   pohead_agent_username AS orderhead_agent_username,
	   CASE WHEN (poitem_itemsite_id = -1) THEN NULL
		ELSE poitem_itemsite_id
	   END AS itemsite_id,
	   vend_id,
	   COALESCE(poitem_vend_item_number, '') AS vend_item_number,
	   COALESCE(poitem_vend_item_descrip, '') AS vend_item_descrip,
	   COALESCE(poitem_vend_uom, '') AS vend_uom,
	   poitem_duedate AS duedate,
	   poitem_unitprice AS orderitem_unitcost,
	   pohead_curr_id AS orderitem_unitcost_curr_id,
	   pohead_curr_id AS freight_curr_id,
	   poitem_rlsd_duedate AS rlsd_duedate INTO _o
        FROM pohead
          JOIN poitem ON (pohead_id=poitem_pohead_id)
          JOIN vendinfo ON (pohead_vend_id=vend_id)
        WHERE (poitem_id=porderitemid);
        
    ELSIF (pordertype='RA') THEN
       SELECT rahead_number AS orderhead_number,
  	   raitem_id AS orderitem_id,
	   ''::text AS orderhead_agent_username,
	   raitem_itemsite_id AS itemsite_id,
	   NULL::integer AS vend_id,
	   ''::text AS vend_item_number,
	   ''::text AS vend_item_descrip,
	   ''::text AS vend_uom,
	   raitem_scheddate AS duedate,
	   raitem_unitprice AS orderitem_unitcost,
	   rahead_curr_id AS orderitem_unitcost_curr_id,
	   rahead_curr_id AS freight_curr_id,
           raitem_scheddate AS rlsd_duedate INTO _o
        FROM rahead
          JOIN raitem ON (rahead_id=raitem_rahead_id)
        WHERE (raitem_id=porderitemid);
        
    ELSIF (pordertype='TO') THEN
         SELECT tohead_number AS orderhead_number,
  	   toitem_id AS orderitem_id,
	   tohead_agent_username AS orderhead_agent_username,
	   itemsite_id,
	   NULL::integer AS vend_id,
	   ''::text AS vend_item_number,
	   ''::text AS vend_item_descrip,
	   ''::text AS vend_uom,
	   toitem_duedate AS duedate,
	   toitem_stdcost AS orderitem_unitcost,
	   baseCurrId() AS orderitem_unitcost_curr_id,
	   toitem_freight_curr_id AS freight_curr_id,
           toitem_duedate AS rlsd_duedate INTO _o
        FROM itemsite, tohead
          JOIN toitem ON (tohead_id=toitem_tohead_id)
        WHERE ((toitem_id=porderitemid)
          AND  (tohead_dest_warehous_id=itemsite_warehous_id)
          AND  (toitem_item_id=itemsite_item_id));
    END IF;

    --Make sure user has site privileges
     IF ((FOUND) AND (_o.itemsite_id IS NOT NULL)) THEN
       SELECT warehous_id INTO _warehouseid
       FROM itemsite,site()
       WHERE ((itemsite_id=_o.itemsite_id)
         AND (warehous_id=itemsite_warehous_id));
          
       IF (NOT FOUND) THEN
         RETURN 0;
        END IF;
      END IF;   

    --Make sure we aren't trying to receive a Kit
    IF ((FOUND) AND (_o.itemsite_id IS NOT NULL)) THEN
      IF (SELECT (item_type='K')
          FROM itemsite, item
          WHERE ((itemsite_id=_o.itemsite_id)
            AND  (item_id=itemsite_item_id))) THEN
        RETURN 0;
      END IF;
    END IF;   

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

    -- default to orderitem_unitcost if recv_purchcost is not specified
    IF(pRecvCost IS NULL) THEN
      _recvcost := _o.orderitem_unitcost;
    ELSE
      _recvcost := pRecvCost;
    END IF;

    INSERT INTO recv
    ( recv_id, recv_date,
      recv_order_number, recv_order_type, recv_orderitem_id,
      recv_trans_usr_name, recv_agent_username, recv_itemsite_id,
      recv_vend_id, recv_vend_item_number, recv_vend_item_descrip,
      recv_vend_uom, recv_qty, recv_duedate,
      recv_purchcost, recv_purchcost_curr_id,
      recv_notes, recv_freight, recv_freight_curr_id, recv_rlsd_duedate
    ) VALUES (
      _recvid, _timestamp,
      _o.orderhead_number, pordertype, _o.orderitem_id::INTEGER,
      getEffectiveXtUser(), _o.orderhead_agent_username, _o.itemsite_id::INTEGER,
      _o.vend_id::INTEGER, _o.vend_item_number, _o.vend_item_descrip,
      _o.vend_uom, pQty, _o.duedate,
      _recvcost, _o.orderitem_unitcost_curr_id::INTEGER,
      pNotes, pFreight, _o.freight_curr_id::INTEGER, _o.rlsd_duedate);
  END IF;

  RETURN _recvid;

END;
$_$;


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

--
-- TOC entry 1526 (class 1255 OID 146566095)
-- Dependencies: 4536 8
-- Name: expirecreditcard(integer, bytea); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION expirecreditcard(integer, bytea) RETURNS text
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pCust ALIAS FOR $1;
  pKey ALIAS FOR $2;
  num_updated INTEGER;
  cc_year INTEGER;
  cc_month INTEGER;
  cc_year_t TEXT;
  cc_month_t TEXT;
  _dr RECORD;
  _cc RECORD;
  bf TEXT;

BEGIN

  num_updated := 0;
  bf := 'bf';

  select cast(date_part('year', CURRENT_DATE) AS INTEGER) AS check_year, cast(date_part('month', CURRENT_DATE) AS INTEGER) AS check_month INTO _dr;

  FOR _cc IN SELECT ccard_id, 
                    decrypt(setbytea(ccard_month_expired), setbytea(pKey), 'bf') AS ccard_month_expired,
                    decrypt(setbytea(ccard_year_expired), setbytea(pKey), 'bf') AS ccard_year_expired
      FROM ccard
      WHERE ( (ccard_cust_id=pCust)
        AND   (ccard_active) ) LOOP

      SELECT formatbytea(_cc.ccard_month_expired) INTO cc_month_t;
      SELECT formatbytea(_cc.ccard_year_expired) INTO cc_year_t;
      SELECT cast(cc_month_t AS INTEGER) INTO cc_month;
      SELECT cast(cc_year_t AS INTEGER) INTO cc_year;

      IF (cc_year < _dr.check_year) THEN
--  We have an expired card
        UPDATE ccard set ccard_active = FALSE where ccard_id = _cc.ccard_id;
        num_updated := num_updated + 1;
      ELSIF (cc_year = _dr.check_year AND cc_month < _dr.check_month) THEN
--  We have an expired card
        UPDATE ccard set ccard_active = FALSE where ccard_id = _cc.ccard_id;
        num_updated := num_updated + 1;
      END IF;

  END LOOP;

  RETURN num_updated;

END;
$_$;


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

--
-- TOC entry 1527 (class 1255 OID 146566096)
-- Dependencies: 4536 8
-- Name: explodebom(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION explodebom(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
  pItemid ALIAS FOR $1;
  pParentid ALIAS FOR $2;
  pLevel ALIAS FOR $3;
  _revid INTEGER;

BEGIN

  SELECT getActiveRevId('BOM',pItemid) INTO _revid;

  RETURN explodeBOM(pItemid, _revid, pParentid, pLevel);

END;
$_$;


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

--
-- TOC entry 1528 (class 1255 OID 146566097)
-- Dependencies: 4536 8
-- Name: explodebom(integer, integer, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION explodebom(integer, 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
  pItemid ALIAS FOR $1;
  pRevisionid ALIAS FOR $2;
  pParentid ALIAS FOR $3;
  pLevel ALIAS FOR $4;
  _bomworkid INTEGER;
  _level INTEGER;
  _p RECORD;
  _r RECORD;
  _temp TEXT;

BEGIN

  _level := (pLevel + 1);

--  Cache some parameters about the parent
  SELECT bomwork_item_id, bomwork_set_id, bomwork_qtyreq,
         bomwork_seqnumber, bomwork_effective, bomwork_expires INTO _p
  FROM bomwork
  WHERE (bomwork_id=pParentid);

--  Step through all of the components of the parent component
  FOR _r IN SELECT bomitem.*,
                   item_id,
                   (itemuomtouomratio(bomitem_item_id, bomitem_uom_id, NULL) * bomitem_qtyfxd) AS qtyfxd,
                   (itemuomtouomratio(bomitem_item_id, bomitem_uom_id, NULL) * bomitem_qtyper) AS qtyper,
                   CASE WHEN (_p.bomwork_effective > bomitem_effective) THEN _p.bomwork_effective
                        ELSE bomitem_effective
                   END AS effective,
                   CASE WHEN (_p.bomwork_expires < bomitem_expires) THEN _p.bomwork_expires
                        ELSE bomitem_expires
                   END AS expires,
                   stdcost(item_id, bomitem_id) AS standardcost, actcost(item_id, bomitem_id) AS actualcost
  FROM bomitem(pItemid, pRevisionid), item
  WHERE ( (bomitem_item_id=item_id)
  AND (bomitem_expires > _p.bomwork_effective) ) LOOP

--  Insert the current component and some bomitem parameters into the bomwork set
    SELECT NEXTVAL('bomwork_bomwork_id_seq') INTO _bomworkid;
    INSERT INTO bomwork
    ( bomwork_id, bomwork_set_id, bomwork_parent_id, bomwork_level,
      bomwork_parent_seqnumber, bomwork_seqnumber,
      bomwork_item_id, bomwork_createwo, bomwork_qtyreq,
      bomwork_qtyfxd, bomwork_qtyper, bomwork_scrap, bomwork_issuemethod,
      bomwork_effective, bomwork_expires,
      bomwork_stdunitcost, bomwork_actunitcost, 
      bomwork_notes, bomwork_ref,
      bomwork_bomitem_id, bomwork_ecn )
    VALUES
    ( _bomworkid, _p.bomwork_set_id, pParentid, _level,
      _p.bomwork_seqnumber, _r.bomitem_seqnumber,
      _r.item_id, _r.bomitem_createwo, (_p.bomwork_qtyreq * _r.qtyper + _r.qtyfxd),
      _r.qtyfxd, _r.qtyper, _r.bomitem_scrap, _r.bomitem_issuemethod,
      _r.effective, _r.expires,
      _r.standardcost, _r.actualcost,
      _r.bomitem_notes, _r.bomitem_ref,
      _r.bomitem_id, _r.bomitem_ecn );

--  Recursively repeat for this component's components
    PERFORM explodeBOM(_r.item_id, _bomworkid, _level);
  END LOOP;

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 1529 (class 1255 OID 146566098)
-- Dependencies: 4536 8
-- Name: explodekit(integer, integer, integer, integer, numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION explodekit(integer, integer, integer, 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
  pSoheadid ALIAS FOR $1;
  pLinenumber ALIAS FOR $2;
  pSubnumber ALIAS FOR $3;
  pItemsiteid ALIAS FOR $4;
  pQty ALIAS FOR $5;
BEGIN
  RETURN explodeKit(pSoheadid, pLinenumber, pSubnumber, pItemsiteid, pQty, CURRENT_DATE, NULL);
END;
$_$;


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

--
-- TOC entry 1434 (class 1255 OID 146566099)
-- Dependencies: 4536 8
