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

CREATE FUNCTION insertgltransaction(text, text, text, text, integer, integer, integer, numeric, date) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pSource ALIAS FOR $1;
  pDocType ALIAS FOR $2;
  pDocNumber ALIAS FOR $3;
  pNotes ALIAS FOR $4;
  pCreditid ALIAS FOR $5;
  pDebitid ALIAS FOR $6;
  pMiscid ALIAS FOR $7;
  pAmount ALIAS FOR $8;
  pDistDate ALIAS FOR $9;
  _return INTEGER;

BEGIN

  SELECT insertGLTransaction( fetchJournalNumber('GL-MISC'),
                              pSource, pDocType, pDocNumber, pNotes,
                              pCreditid, pDebitid, pMiscid, pAmount, pDistDate) INTO _return;

  RETURN _return;

END;
$_$;


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

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

CREATE FUNCTION insertgltransaction(integer, text, text, text, text, integer, integer, integer, numeric, date) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pJournalNumber ALIAS FOR $1;
  pSource ALIAS FOR $2;
  pDocType ALIAS FOR $3;
  pDocNumber ALIAS FOR $4;
  pNotes ALIAS FOR $5;
  pCreditid ALIAS FOR $6;
  pDebitid ALIAS FOR $7;
  pMiscid ALIAS FOR $8;
  pAmount ALIAS FOR $9;
  pDistDate ALIAS FOR $10;
  _return INTEGER;

BEGIN

  SELECT insertGLTransaction( pJournalNumber, pSource, pDocType, pDocNumber, pNotes,
                              pCreditid, pDebitid, pMiscid, pAmount, pDistDate, TRUE) INTO _return;

  RETURN _return;

END;
$_$;


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

--
-- TOC entry 1822 (class 1255 OID 146566565)
-- Dependencies: 4536 8
-- Name: insertgltransaction(text, text, text, text, integer, integer, integer, numeric, date, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION insertgltransaction(text, text, text, text, integer, integer, integer, numeric, date, boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pSource ALIAS FOR $1;
  pDocType ALIAS FOR $2;
  pDocNumber ALIAS FOR $3;
  pNotes ALIAS FOR $4;
  pCreditid ALIAS FOR $5;
  pDebitid ALIAS FOR $6;
  pMiscid ALIAS FOR $7;
  pAmount ALIAS FOR $8;
  pDistDate ALIAS FOR $9;
  pPostTrialBal ALIAS FOR $10;
  _return INTEGER;

BEGIN

  SELECT insertGLTransaction( fetchJournalNumber('GL-MISC'),
                              pSource, pDocType, pDocNumber, pNotes,
                              pCreditid, pDebitid, pMiscid, pAmount, pDistDate, pPostTrialBal) INTO _return;

  RETURN _return;

END;
$_$;


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

--
-- TOC entry 1823 (class 1255 OID 146566566)
-- Dependencies: 4536 8
-- Name: insertgltransaction(integer, text, text, text, text, integer, integer, integer, numeric, date, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION insertgltransaction(integer, text, text, text, text, integer, integer, integer, numeric, date, boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pJournalNumber ALIAS FOR $1;
  pSource ALIAS FOR $2;
  pDocType ALIAS FOR $3;
  pDocNumber ALIAS FOR $4;
  pNotes ALIAS FOR $5;
  pCreditid ALIAS FOR $6;
  pDebitid ALIAS FOR $7;
  pMiscid ALIAS FOR $8;
  pAmount ALIAS FOR $9;
  pDistDate ALIAS FOR $10;
  pPostTrialBal ALIAS FOR $11;
  
  _return INTEGER;

BEGIN

  SELECT insertGLTransaction( pJournalNumber, pSource, pDocType, pDocNumber, pNotes,
                              pCreditid, pDebitid, pMiscid, pAmount, pDistDate, pPostTrialBal, false) INTO _return;

  RETURN _return;

END;
$_$;


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

--
-- TOC entry 1824 (class 1255 OID 146566567)
-- Dependencies: 4536 8
-- Name: insertgltransaction(integer, text, text, text, text, integer, integer, integer, numeric, date, boolean, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION insertgltransaction(integer, text, text, text, text, integer, integer, integer, numeric, date, 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
  pJournalNumber ALIAS FOR $1;
  pSource ALIAS FOR $2;
  pDocType ALIAS FOR $3;
  pDocNumber ALIAS FOR $4;
  pNotes ALIAS FOR $5;
  pCreditid ALIAS FOR $6;
  pDebitid ALIAS FOR $7;
  pMiscid ALIAS FOR $8;
  pAmount ALIAS FOR $9;
  pDistDate ALIAS FOR $10;
  pPostTrialBal ALIAS FOR $11;
  pOnlyGL ALIAS FOR $12;
  _debitid INTEGER;
  _creditid INTEGER;
  _sequence INTEGER;
  _check INTEGER;

BEGIN

--  Check GL Interface metric
  IF (fetchMetricBool('InterfaceToGL') = false AND pSource IN ('I/M', 'P/D', 'S/R', 'W/O')) THEN
    RETURN 0;
  END IF;
  IF (fetchMetricBool('InterfaceAPToGL') = false AND pSource = 'A/P') THEN
    RETURN 0;
  END IF;
  IF (fetchMetricBool('InterfaceARToGL') = false AND pSource IN ('A/R', 'S/O', 'S/R')) THEN
    RETURN 0;
  END IF;

--  Is there anything to post?
--  ToDo - 2 should really be the scale of the base currency
  IF (round(pAmount, 2) = 0) THEN
    RETURN -3;
  END IF;

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

    IF (SELECT (COALESCE(d.accnt_company,'') != COALESCE(c.accnt_company,''))
       FROM accnt d, accnt c
       WHERE ((d.accnt_id=pDebitid)
        AND (c.accnt_id=pCreditid))) THEN
      RAISE EXCEPTION 'G/L Transaction can not be posted because accounts % and % reference two differnt companies.',
        formatGlaccount(pDebitid), formatGlaccount(pCreditid);
    END IF;
  END IF;

--  Validate pDebitid
  IF (pDebitid IN (SELECT accnt_id FROM accnt)) THEN
    _debitid := pDebitid;
  ELSE
    SELECT getUnassignedAccntId() INTO _debitid;
  END IF;

--  Validate pCreditid
  IF (pCreditid IN (SELECT accnt_id FROM accnt)) THEN
    _creditid := pCreditid;
  ELSE
    SELECT getUnassignedAccntId() INTO _creditid;
  END IF;

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

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

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

--  Grab a sequence for the pair
  SELECT fetchGLSequence() INTO _sequence;

  IF (NOT pOnlyGL AND fetchMetricBool('UseJournals')) THEN
  --  First the credit	
    INSERT INTO sltrans
    ( sltrans_journalnumber, sltrans_posted, sltrans_created, sltrans_date,
      sltrans_sequence, sltrans_accnt_id, sltrans_source,
      sltrans_doctype, sltrans_docnumber, sltrans_notes,
      sltrans_misc_id, sltrans_amount )
    VALUES
    ( pJournalNumber, FALSE, CURRENT_TIMESTAMP, pDistDate,
      _sequence, _creditid, pSource,
      pDocType, pDocNumber, pNotes,
      pMiscid, pAmount );

  --  Now the debit
    INSERT INTO sltrans
    ( sltrans_journalnumber, sltrans_posted, sltrans_created, sltrans_date,
      sltrans_sequence, sltrans_accnt_id, sltrans_source,
      sltrans_doctype, sltrans_docnumber, sltrans_notes,
      sltrans_misc_id, sltrans_amount )
    VALUES
    ( pJournalNumber, FALSE, CURRENT_TIMESTAMP, pDistDate,
      _sequence, _debitid, pSource,
      pDocType, pDocNumber, pNotes,
      pMiscid, (pAmount * -1) );
  ELSE
  --  First the credit
    INSERT INTO gltrans
    ( gltrans_journalnumber, gltrans_posted, gltrans_exported, gltrans_created, gltrans_date,
      gltrans_sequence, gltrans_accnt_id, gltrans_source,
      gltrans_doctype, gltrans_docnumber, gltrans_notes,
      gltrans_misc_id, gltrans_amount )
    VALUES
    ( pJournalNumber, FALSE, FALSE, CURRENT_TIMESTAMP, pDistDate,
      _sequence, _creditid, pSource,
      pDocType, pDocNumber, pNotes,
      pMiscid, pAmount );

  --  Now the debit
    INSERT INTO gltrans
    ( gltrans_journalnumber, gltrans_posted, gltrans_exported, gltrans_created, gltrans_date,
      gltrans_sequence, gltrans_accnt_id, gltrans_source,
      gltrans_doctype, gltrans_docnumber, gltrans_notes,
      gltrans_misc_id, gltrans_amount )
    VALUES
    ( pJournalNumber, FALSE, FALSE, CURRENT_TIMESTAMP, pDistDate,
      _sequence, _debitid, pSource,
      pDocType, pDocNumber, pNotes,
      pMiscid, (pAmount * -1) );

    IF (pPostTrialBal) THEN
      PERFORM postIntoTrialBalance(_sequence);
    END IF;
  END IF;

  RETURN _sequence;

END;
$_$;


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

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

CREATE FUNCTION insertintoglseries(integer, text, text, text, 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
  pSequence ALIAS FOR $1;
  pSource ALIAS FOR $2;
  pDocType ALIAS FOR $3;
  pDocNumber ALIAS FOR $4;
  pAccntid ALIAS FOR $5;
  pAmount ALIAS FOR $6;
  _returnValue INTEGER;

BEGIN

  SELECT insertIntoGLSeries( pSequence, pSource, pDocType, pDocNumber,
                             pAccntid, pAmount, CURRENT_DATE, '' ) INTO _returnValue;

  RETURN _returnValue;

END;
$_$;


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

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

CREATE FUNCTION insertintoglseries(integer, text, text, text, integer, numeric, date) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pSequence ALIAS FOR $1;
  pSource ALIAS FOR $2;
  pDocType ALIAS FOR $3;
  pDocNumber ALIAS FOR $4;
  pAccntid ALIAS FOR $5;
  pAmount ALIAS FOR $6;
  pDistDate ALIAS FOR $7;
  _returnValue INTEGER;

BEGIN

  SELECT insertIntoGLSeries( pSequence, pSource, pDocType, pDocNumber,
                             pAccntid, pAmount, pDistDate, '' ) INTO _returnValue;

  RETURN _returnValue;

END;
$_$;


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

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

CREATE FUNCTION insertintoglseries(integer, text, text, text, integer, numeric, date, text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pSequence ALIAS FOR $1;
  pSource ALIAS FOR $2;
  pDocType ALIAS FOR $3;
  pDocNumber ALIAS FOR $4;
  pAccntid ALIAS FOR $5;
  pAmount ALIAS FOR $6;
  pDistDate ALIAS FOR $7;
  pNotes ALIAS FOR $8;
  _returnValue INTEGER;

BEGIN

  SELECT insertIntoGLSeries( pSequence, pSource, pDocType, pDocNumber,
                             pAccntid, pAmount, pDistDate, pNotes, NULL ) INTO _returnValue;

  RETURN _returnValue;

END;
$_$;


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

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

CREATE FUNCTION insertintoglseries(integer, text, text, text, integer, numeric, date, text, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pSequence ALIAS FOR $1;
  pSource ALIAS FOR $2;
  pDocType ALIAS FOR $3;
  pDocNumber ALIAS FOR $4;
  pAccntid ALIAS FOR $5;
  pAmount ALIAS FOR $6;
  pDistDate ALIAS FOR $7;
  pNotes ALIAS FOR $8;
  pMiscid ALIAS FOR $9;
  _glseriesid INTEGER;

BEGIN

--  Check GL Interface metric
  IF (fetchMetricBool('InterfaceToGL') = false AND pSource IN ('I/M', 'P/D', 'S/R', 'W/O')) THEN
    RETURN 0;
  END IF;
  IF (fetchMetricBool('InterfaceAPToGL') = false AND pSource = 'A/P') THEN
    RETURN 0;
  END IF;
  IF (fetchMetricBool('InterfaceARToGL') = false AND pSource IN ('A/R', 'S/O', 'S/R')) THEN
    RETURN 0;
  END IF;

--  Verify the target accnt
  IF ( (pAccntid IS NULL) OR (pAccntid = -1) ) THEN
    RETURN -1;
  END IF;

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

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

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

-- Insert into the glseries
  SELECT NEXTVAL('glseries_glseries_id_seq') INTO _glseriesid;
  INSERT INTO glseries
  ( glseries_id, glseries_sequence, glseries_source, glseries_doctype, glseries_docnumber,
    glseries_accnt_id, glseries_amount, glseries_distdate, glseries_notes, glseries_misc_id )
  VALUES
  ( _glseriesid, pSequence, pSource, pDocType, pDocNumber,
    pAccntid, pAmount, pDistDate, pNotes, pMiscid );

  RETURN _glseriesid;

END;
$_$;


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

--
-- TOC entry 224 (class 1259 OID 146566573)
-- Dependencies: 6022 6023 6024 6026 8
-- Name: invchead; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE invchead (
    invchead_id integer NOT NULL,
    invchead_cust_id integer NOT NULL,
    invchead_shipto_id integer,
    invchead_ordernumber text,
    invchead_orderdate date,
    invchead_posted boolean NOT NULL,
    invchead_printed boolean NOT NULL,
    invchead_invcnumber text NOT NULL,
    invchead_invcdate date NOT NULL,
    invchead_shipdate date,
    invchead_ponumber text,
    invchead_shipvia text,
    invchead_fob text,
    invchead_billto_name text,
    invchead_billto_address1 text,
    invchead_billto_address2 text,
    invchead_billto_address3 text,
    invchead_billto_city text,
    invchead_billto_state text,
    invchead_billto_zipcode text,
    invchead_billto_phone text,
    invchead_shipto_name text,
    invchead_shipto_address1 text,
    invchead_shipto_address2 text,
    invchead_shipto_address3 text,
    invchead_shipto_city text,
    invchead_shipto_state text,
    invchead_shipto_zipcode text,
    invchead_shipto_phone text,
    invchead_salesrep_id integer,
    invchead_commission numeric(20,10) NOT NULL,
    invchead_terms_id integer,
    invchead_freight numeric(16,2) NOT NULL,
    invchead_misc_amount numeric(16,2) NOT NULL,
    invchead_misc_descrip text,
    invchead_misc_accnt_id integer,
    invchead_payment numeric(16,2),
    invchead_paymentref text,
    invchead_notes text,
    invchead_billto_country text,
    invchead_shipto_country text,
    invchead_prj_id integer,
    invchead_curr_id integer DEFAULT basecurrid(),
    invchead_gldistdate date,
    invchead_recurring boolean DEFAULT false NOT NULL,
    invchead_recurring_interval integer,
    invchead_recurring_type text,
    invchead_recurring_until date,
    invchead_recurring_invchead_id integer,
    invchead_shipchrg_id integer,
    invchead_taxzone_id integer,
    invchead_void boolean DEFAULT false,
    invchead_saletype_id integer,
    invchead_shipzone_id integer,
    CONSTRAINT invchead_invchead_invcnumber_check CHECK ((invchead_invcnumber <> ''::text))
);


ALTER TABLE public.invchead OWNER TO admin;

--
-- TOC entry 9039 (class 0 OID 0)
-- Dependencies: 224
-- Name: TABLE invchead; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE invchead IS 'Invoice header information';


--
-- TOC entry 9040 (class 0 OID 0)
-- Dependencies: 224
-- Name: COLUMN invchead.invchead_recurring; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN invchead.invchead_recurring IS 'Deprecated.';


--
-- TOC entry 9041 (class 0 OID 0)
-- Dependencies: 224
-- Name: COLUMN invchead.invchead_recurring_interval; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN invchead.invchead_recurring_interval IS 'Deprecated.';


--
-- TOC entry 9042 (class 0 OID 0)
-- Dependencies: 224
-- Name: COLUMN invchead.invchead_recurring_type; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN invchead.invchead_recurring_type IS 'Deprecated.';


--
-- TOC entry 9043 (class 0 OID 0)
-- Dependencies: 224
-- Name: COLUMN invchead.invchead_recurring_until; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN invchead.invchead_recurring_until IS 'Deprecated.';


--
-- TOC entry 9044 (class 0 OID 0)
-- Dependencies: 224
-- Name: COLUMN invchead.invchead_saletype_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN invchead.invchead_saletype_id IS 'Associated sale type for invoice.';


--
-- TOC entry 9045 (class 0 OID 0)
-- Dependencies: 224
-- Name: COLUMN invchead.invchead_shipzone_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN invchead.invchead_shipzone_id IS 'Associated shipping zone for invoice.';


--
-- TOC entry 225 (class 1259 OID 146566583)
-- Dependencies: 6028 6029 8
-- Name: prj; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE prj (
    prj_id integer NOT NULL,
    prj_number text NOT NULL,
    prj_name text NOT NULL,
    prj_descrip text,
    prj_status character(1) NOT NULL,
    prj_so boolean,
    prj_wo boolean,
    prj_po boolean,
    prj_owner_username text,
    prj_start_date date,
    prj_due_date date,
    prj_assigned_date date,
    prj_completed_date date,
    prj_username text,
    prj_recurring_prj_id integer,
    prj_crmacct_id integer,
    prj_cntct_id integer,
    prj_prjtype_id integer,
    CONSTRAINT prj_prj_number_check CHECK ((prj_number <> ''::text)),
    CONSTRAINT prj_prj_status_check CHECK ((prj_status = ANY (ARRAY['P'::bpchar, 'O'::bpchar, 'C'::bpchar])))
);


ALTER TABLE public.prj OWNER TO admin;

--
-- TOC entry 9047 (class 0 OID 0)
-- Dependencies: 225
-- Name: TABLE prj; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE prj IS 'Project information';


--
-- TOC entry 9048 (class 0 OID 0)
-- Dependencies: 225
-- Name: COLUMN prj.prj_recurring_prj_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN prj.prj_recurring_prj_id IS 'The first prj record in the series if this is a recurring Project. If the prj_recurring_prj_id is the same as the prj_id, this record is the first in the series.';


--
-- TOC entry 226 (class 1259 OID 146566591)
-- Dependencies: 6031 8
-- Name: saletype; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE saletype (
    saletype_id integer NOT NULL,
    saletype_code text NOT NULL,
    saletype_descr text,
    saletype_active boolean DEFAULT true NOT NULL
);


ALTER TABLE public.saletype OWNER TO admin;

--
-- TOC entry 9050 (class 0 OID 0)
-- Dependencies: 226
-- Name: TABLE saletype; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE saletype IS 'Type or Origination of Sale.';


--
-- TOC entry 9051 (class 0 OID 0)
-- Dependencies: 226
-- Name: COLUMN saletype.saletype_id; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN saletype.saletype_id IS 'Sequence identifier for sale type.';


--
-- TOC entry 9052 (class 0 OID 0)
-- Dependencies: 226
-- Name: COLUMN saletype.saletype_code; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN saletype.saletype_code IS 'User defined identifier for sale type.';


--
-- TOC entry 9053 (class 0 OID 0)
-- Dependencies: 226
-- Name: COLUMN saletype.saletype_descr; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN saletype.saletype_descr IS 'Description for sale type.';


--
-- TOC entry 9054 (class 0 OID 0)
-- Dependencies: 226
-- Name: COLUMN saletype.saletype_active; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON COLUMN saletype.saletype_active IS 'Boolean to deactivate a sale type.';


--
-- TOC entry 227 (class 1259 OID 146566598)
-- Dependencies: 6032 6033 8
-- Name: shipzone; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE shipzone (
    shipzone_id integer DEFAULT nextval(('shipzone_shipzone_id_seq'::text)::regclass) NOT NULL,
    shipzone_name text NOT NULL,
    shipzone_descrip text,
    CONSTRAINT shipzone_shipzone_name_check CHECK ((shipzone_name <> ''::text))
);


ALTER TABLE public.shipzone OWNER TO admin;

--
-- TOC entry 9056 (class 0 OID 0)
-- Dependencies: 227
-- Name: TABLE shipzone; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE shipzone IS 'Shipping Zone information';



--
-- TOC entry 228 (class 1259 OID 146566606)
-- Dependencies: 8475 6
-- Name: invoice; Type: VIEW; Schema: api; Owner: admin
--

CREATE VIEW invoice AS
    SELECT invchead.invchead_invcnumber AS invoice_number, invchead.invchead_ordernumber AS order_number, invchead.invchead_invcdate AS invoice_date, invchead.invchead_shipdate AS ship_date, invchead.invchead_orderdate AS order_date, saletype.saletype_code AS sale_type, salesrep.salesrep_number AS sales_rep, invchead.invchead_commission AS commission, COALESCE(taxzone.taxzone_code, 'None'::text) AS tax_zone, terms.terms_code AS terms, custinfo.cust_number AS customer_number, invchead.invchead_billto_name AS billto_name, invchead.invchead_billto_address1 AS billto_address1, invchead.invchead_billto_address2 AS billto_address2, invchead.invchead_billto_address3 AS billto_address3, invchead.invchead_billto_city AS billto_city, invchead.invchead_billto_state AS billto_state, invchead.invchead_billto_zipcode AS billto_postal_code, invchead.invchead_billto_country AS billto_country, invchead.invchead_billto_phone AS billto_phone, shiptoinfo.shipto_num AS shipto_number, invchead.invchead_shipto_name AS shipto_name, invchead.invchead_shipto_address1 AS shipto_address1, invchead.invchead_shipto_address2 AS shipto_address2, invchead.invchead_shipto_address3 AS shipto_address3, invchead.invchead_shipto_city AS shipto_city, invchead.invchead_shipto_state AS shipto_state, invchead.invchead_shipto_zipcode AS shipto_postal_code, invchead.invchead_shipto_country AS shipto_country, shipzone.shipzone_name AS shipto_shipzone, invchead.invchead_shipto_phone AS shipto_phone, invchead.invchead_ponumber AS po_number, invchead.invchead_shipvia AS ship_via, prj.prj_number AS project_number, invchead.invchead_fob AS fob, invchead.invchead_misc_descrip AS misc_charge_description, invchead.invchead_misc_amount AS misc_charge, CASE WHEN (invchead.invchead_misc_accnt_id = (-1)) THEN NULL::text ELSE public.formatglaccount(invchead.invchead_misc_accnt_id) END AS misc_charge_account_number, invchead.invchead_freight AS freight, curr.curr_abbr AS currency, invchead.invchead_payment AS payment, invchead.invchead_notes AS notes FROM (((((((((public.invchead LEFT JOIN public.custinfo ON ((custinfo.cust_id = invchead.invchead_cust_id))) LEFT JOIN public.shiptoinfo ON ((shiptoinfo.shipto_id = invchead.invchead_shipto_id))) LEFT JOIN public.prj ON ((prj.prj_id = invchead.invchead_prj_id))) LEFT JOIN public.curr_symbol curr ON ((curr.curr_id = invchead.invchead_curr_id))) LEFT JOIN public.salesrep ON ((salesrep.salesrep_id = invchead.invchead_salesrep_id))) LEFT JOIN public.terms ON ((terms.terms_id = invchead.invchead_terms_id))) LEFT JOIN public.taxzone ON ((taxzone.taxzone_id = invchead.invchead_taxzone_id))) LEFT JOIN public.saletype ON ((invchead.invchead_saletype_id = saletype.saletype_id))) LEFT JOIN public.shipzone ON ((invchead.invchead_shipzone_id = shipzone.shipzone_id)));


ALTER TABLE api.invoice OWNER TO admin;

--
-- TOC entry 9058 (class 0 OID 0)
-- Dependencies: 228
-- Name: VIEW invoice; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW invoice IS '
This view can be used as an interface to import Invioce Header data directly  
into the system.  Required fields will be checked and default values will be 
populated';



--
-- TOC entry 1829 (class 1255 OID 146566611)
-- Dependencies: 2868 4536 8
-- Name: insertinvoice(api.invoice); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION insertinvoice(api.invoice) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
	pNew ALIAS FOR $1;
BEGIN
	-- NOTE: (SELECT getCustId(...)) seems redundant, but it actually produces
	-- a HUGE performance increase because it makes the Postgres query planner
	-- use an index scan rather than an sequential table scan on cust_id
	INSERT INTO invchead (
		invchead_invcnumber,
		invchead_ordernumber,
		invchead_invcdate,
		invchead_shipdate,
		invchead_orderdate,
		invchead_printed,
		invchead_posted,
		invchead_salesrep_id,
		invchead_commission,
		invchead_taxzone_id,
		invchead_terms_id,
		invchead_cust_id,
		invchead_billto_name,
		invchead_billto_address1,
		invchead_billto_address2,
		invchead_billto_address3,
		invchead_billto_city,
		invchead_billto_state,
		invchead_billto_zipcode,
		invchead_billto_country,
		invchead_billto_phone,
		invchead_shipto_id,
		invchead_shipto_name,
		invchead_shipto_address1,
		invchead_shipto_address2,
		invchead_shipto_address3,
		invchead_shipto_city,
		invchead_shipto_state,
		invchead_shipto_zipcode,
		invchead_shipto_country,
		invchead_shipto_phone,
		invchead_ponumber,
		invchead_shipvia,
		invchead_prj_id,
		invchead_fob,
		invchead_misc_descrip,
		invchead_misc_amount,
		invchead_misc_accnt_id,
		invchead_freight,
		invchead_curr_id,
		invchead_payment,
		invchead_notes,
                invchead_saletype_id,
                invchead_shipzone_id
	) SELECT
		(CASE -- use a case here so we don't unnecessarily fetch a new invoice number
			WHEN pNew.invoice_number IS NULL THEN CAST(fetchInvcNumber() AS TEXT)
			WHEN pNew.invoice_number = '' THEN CAST(fetchInvcNumber() AS TEXT)
			ELSE pNew.invoice_number
		END),
		pNew.order_number,
		COALESCE(pNew.invoice_date, CURRENT_DATE),
		pNew.ship_date,
		pNew.order_date,
		FALSE,
		FALSE,
		COALESCE(getSalesRepId(pNew.sales_rep),shipto_salesrep_id,cust_salesrep_id),
		COALESCE(pNew.commission, 0),
		CASE
			WHEN pNew.tax_zone = 'None' THEN NULL
			ELSE COALESCE(getTaxZoneId(pNew.tax_zone),shipto_taxzone_id,cust_taxzone_id)
		END,
		COALESCE(getTermsId(pNew.terms),cust_terms_id),
		(SELECT getCustId(pNew.customer_number)),
		COALESCE(pNew.billto_name, cohead_billtoname, cust_name),
		COALESCE(pNew.billto_address1, cohead_billtoaddress1, addr_line1),
		COALESCE(pNew.billto_address2, cohead_billtoaddress2, addr_line2),
		COALESCE(pNew.billto_address3, cohead_billtoaddress3, addr_line3),
		COALESCE(pNew.billto_city, cohead_billtocity, addr_city),
		COALESCE(pNew.billto_state, cohead_billtostate, addr_state),
		COALESCE(pNew.billto_postal_code, cohead_billtozipcode, addr_postalcode),
		COALESCE(pNew.billto_country, cohead_billtocountry, addr_country),
		COALESCE(pNew.billto_phone, ''),
		COALESCE(shipto_id,-1),
		pNew.shipto_name,
		pNew.shipto_address1,
		pNew.shipto_address2,
		pNew.shipto_address3,
		pNew.shipto_city,
		pNew.shipto_state,
		pNew.shipto_postal_code,
		pNew.shipto_country,
		pNew.shipto_phone,
		COALESCE(pNew.po_number, ''),
		COALESCE(pNew.ship_via,shipto_shipvia,cust_shipvia),
		COALESCE(getPrjId(pNew.project_number),-1),
		COALESCE(pNew.fob,fetchDefaultFob((
			SELECT CAST(usrpref_value AS INTEGER) 
			FROM usrpref, whsinfo
			WHERE ((warehous_id=CAST(usrpref_value AS INTEGER))
				AND (warehous_shipping)
				AND (warehous_active)
				AND (usrpref_username=getEffectiveXtUser())
				AND (usrpref_name='PreferredWarehouse')
			)
		))),
		pNew.misc_charge_description,
		COALESCE(pNew.misc_charge, 0),
		COALESCE(getGlAccntId(pNew.misc_charge_account_number),-1),
		COALESCE(pNew.freight, 0),
		COALESCE(getCurrId(pNew.currency),(
			SELECT cust_curr_id
			FROM custinfo
			WHERE (cust_id=(SELECT getCustId(pNew.customer_number)))
		),basecurrid()),
		COALESCE(pNew.payment,0),
		COALESCE(pNew.notes,''),
                getSaleTypeId(pNew.sale_type),
                getShipZoneId(pNew.shipto_shipzone)
	FROM custinfo
		LEFT OUTER JOIN shiptoinfo ON (shipto_id=(SELECT CASE
			WHEN getShiptoId(pNew.customer_number,pNew.shipto_number) IS NOT NULL
				THEN getShiptoId(pNew.customer_number,pNew.shipto_number)
			ELSE (SELECT shipto_id FROM shiptoinfo WHERE shipto_cust_id=cust_id AND shipto_default)
		END))
               LEFT OUTER JOIN cohead ON (cohead_number=pNEW.order_number)
               LEFT OUTER JOIN cntct ON (cntct_id=cust_cntct_id)
               LEFT OUTER JOIN addr ON (addr_id=cntct_addr_id)
	WHERE cust_id = (SELECT getCustId(pNew.customer_number));
	RETURN TRUE;
END;
$_$;


ALTER FUNCTION public.insertinvoice(api.invoice) OWNER TO admin;

--
-- TOC entry 229 (class 1259 OID 146566612)
-- Dependencies: 6035 6036 8
-- Name: invcitem; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE invcitem (
    invcitem_id integer NOT NULL,
    invcitem_invchead_id integer NOT NULL,
    invcitem_linenumber integer,
    invcitem_item_id integer,
    invcitem_warehous_id integer DEFAULT (-1),
    invcitem_custpn text,
    invcitem_number text,
    invcitem_descrip text,
    invcitem_ordered numeric(20,6) NOT NULL,
    invcitem_billed numeric(20,6) NOT NULL,
    invcitem_custprice numeric(20,4),
    invcitem_price numeric(20,4) NOT NULL,
    invcitem_notes text,
    invcitem_salescat_id integer,
    invcitem_taxtype_id integer,
    invcitem_qty_uom_id integer,
    invcitem_qty_invuomratio numeric(20,10) NOT NULL,
    invcitem_price_uom_id integer,
    invcitem_price_invuomratio numeric(20,10) NOT NULL,
    invcitem_coitem_id integer,
    invcitem_updateinv boolean DEFAULT false,
    invcitem_rev_accnt_id integer
);


ALTER TABLE public.invcitem OWNER TO admin;

--
-- TOC entry 9060 (class 0 OID 0)
-- Dependencies: 229
-- Name: TABLE invcitem; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE invcitem IS 'Invoice Line Item information';



--
-- TOC entry 230 (class 1259 OID 146566620)
-- Dependencies: 8476 6
-- Name: invoiceline; Type: VIEW; Schema: api; Owner: admin
--

CREATE VIEW invoiceline AS
    SELECT invchead.invchead_invcnumber AS invoice_number, invcitem.invcitem_linenumber AS line_number, item.item_number, invcitem.invcitem_number AS misc_item_number, whsinfo.warehous_code AS site, invcitem.invcitem_descrip AS misc_item_description, salescat.salescat_name AS sales_category, invcitem.invcitem_custpn AS customer_part_number, invcitem.invcitem_ordered AS qty_ordered, invcitem.invcitem_billed AS qty_billed, invcitem.invcitem_updateinv AS update_inventory, invcitem.invcitem_price AS net_unit_price, COALESCE(taxtype.taxtype_name, 'None'::text) AS tax_type, COALESCE(qty_uom.uom_name, 'None'::text) AS qty_uom, COALESCE(price_uom.uom_name, 'None'::text) AS price_uom, invcitem.invcitem_notes AS notes, CASE WHEN (invcitem.invcitem_rev_accnt_id IS NOT NULL) THEN public.formatglaccount(invcitem.invcitem_rev_accnt_id) ELSE NULL::text END AS alternate_rev_account FROM (((((((public.invcitem LEFT JOIN public.invchead ON ((invcitem.invcitem_invchead_id = invchead.invchead_id))) LEFT JOIN public.item ON ((item.item_id = invcitem.invcitem_item_id))) LEFT JOIN public.whsinfo ON ((invcitem.invcitem_warehous_id = whsinfo.warehous_id))) LEFT JOIN public.salescat ON ((salescat.salescat_id = invcitem.invcitem_salescat_id))) LEFT JOIN public.taxtype ON ((taxtype.taxtype_id = invcitem.invcitem_taxtype_id))) LEFT JOIN public.uom qty_uom ON ((qty_uom.uom_id = invcitem.invcitem_qty_uom_id))) LEFT JOIN public.uom price_uom ON ((price_uom.uom_id = invcitem.invcitem_price_uom_id)));


ALTER TABLE api.invoiceline OWNER TO admin;

--
-- TOC entry 9062 (class 0 OID 0)
-- Dependencies: 230
-- Name: VIEW invoiceline; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW invoiceline IS '
This view can be used as an interface to import Invoice Line Items data directly
into the system.  Required fields will be checked and default values will be
populated';



--
-- TOC entry 1830 (class 1255 OID 146566625)
-- Dependencies: 2875 4536 8
-- Name: insertinvoicelineitem(api.invoiceline); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION insertinvoicelineitem(api.invoiceline) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
	pNew ALIAS FOR $1;
	_r RECORD;
BEGIN
	INSERT INTO invcitem (
		invcitem_invchead_id,
		invcitem_linenumber,
		invcitem_item_id,
		invcitem_warehous_id,
		invcitem_custpn,
		invcitem_number,
		invcitem_descrip,
		invcitem_ordered,
		invcitem_billed,
                invcitem_updateinv,
		invcitem_custprice,
		invcitem_price,
		invcitem_notes,
		invcitem_salescat_id,
		invcitem_taxtype_id,
		invcitem_qty_uom_id,
		invcitem_qty_invuomratio,
		invcitem_price_uom_id,
		invcitem_price_invuomratio,
                invcitem_rev_accnt_id
	) SELECT
		invchead_id,
		COALESCE(pNew.line_number,(
			SELECT (COALESCE(MAX(invcitem_linenumber), 0) + 1)
			FROM invcitem WHERE (invcitem_invchead_id=invchead_id)
		)),
		COALESCE(item_id, -1),
		COALESCE(getwarehousid(pNew.site,'ALL'),-1),
		pNew.customer_part_number,
		(CASE WHEN item_id IS NULL THEN pNew.misc_item_number ELSE NULL END),
		(CASE WHEN item_id IS NULL THEN pNew.misc_item_description ELSE NULL END),
		pNew.qty_ordered,
		COALESCE(pNew.qty_billed, 0),
                COALESCE(pNew.update_inventory,FALSE),
		0, -- invcitem_custprice
		COALESCE(pNew.net_unit_price,itemPrice(item_id,invchead_cust_id,
			invchead_shipto_id,pNew.qty_ordered,invchead_curr_id,invchead_orderdate)),
		COALESCE(pNew.notes,''),
		CASE
			WHEN item_id IS NULL THEN
				(SELECT salescat_id FROM salescat WHERE salescat_name = pNew.sales_category)
			ELSE NULL
		END,
		taxtype_id,
		CASE
			WHEN item_id IS NOT NULL THEN
				COALESCE((SELECT uom_id FROM uom WHERE (uom_name=pNew.qty_uom)), item_price_uom_id)
			ELSE NULL
		END,
		CASE
			WHEN item_id IS NOT NULL THEN
				itemuomtouomratio(item_id,
					COALESCE((SELECT uom_id FROM uom WHERE uom_name=pNew.qty_uom),item_price_uom_id),
					item_price_uom_id
				)
			ELSE 1
		END,
		CASE
			WHEN item_id IS NOT NULL THEN
				COALESCE((SELECT uom_id FROM uom WHERE uom_name=pNew.price_uom),item_price_uom_id)
			ELSE NULL
		END,
		CASE
			WHEN item_id IS NOT NULL THEN
				itemuomtouomratio(item_id,
					COALESCE((SELECT uom_id FROM uom WHERE uom_name=pNew.price_uom),item_price_uom_id),
					item_price_uom_id
				)
			ELSE 1
		END,
                getGlAccntId(pNew.alternate_rev_account)
	FROM invchead
		LEFT OUTER JOIN item ON (item_id=getItemId(pNew.item_number))
		LEFT OUTER JOIN taxtype ON (taxtype_id=CASE
			WHEN pNew.tax_type IS NULL THEN getItemTaxType(item_id,invchead_taxzone_id)
			WHEN pNew.tax_type = 'None' THEN NULL
			ELSE (SELECT taxtype_id FROM taxtype WHERE taxtype_name=pNew.tax_type)
		END)
	WHERE (invchead_invcnumber=pNew.invoice_number) AND (invchead_posted=FALSE);
	RETURN TRUE;
END;
$_$;


ALTER FUNCTION public.insertinvoicelineitem(api.invoiceline) OWNER TO admin;

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

CREATE FUNCTION insertitemcost(integer, integer, integer, numeric, boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
pItemId ALIAS FOR $1;
pCostElemId ALIAS FOR $2;
pCurrId ALIAS FOR $3;
pCost ALIAS FOR $4;
pPostToStandard ALIAS FOR $5;
_itemcost_id INTEGER;
_update_return INTEGER;
_postcost_return BOOLEAN;

--This function is used with the api.itemcost View for updating and inserting
--into the itemcosts table

BEGIN
  IF (pCost IS NULL OR pCost < 0) THEN
    RAISE EXCEPTION 'itemcost Actual Cost Invalid ', pCost;
  END IF;

-- Check for uniqueness
  SELECT itemcost_id INTO _itemcost_id
  FROM itemcost
  WHERE ( (itemcost_item_id = pItemId)
    AND   (itemcost_costelem_id = pCostElemId)
    AND   (NOT itemcost_lowlevel) );
  IF (FOUND) THEN
    RAISE EXCEPTION 'itemcost already exists for this Item and Cost Element';
  END IF;

-- Check for valid combination of item_type and costelem_type
  IF (SELECT (COUNT(*) > 0)
      FROM item, costelem
      WHERE (item_id=pItemId)
        AND (costelem_id=pCostElemId)
        AND (item_type IN ('M', 'F', 'B', 'C', 'T'))
        AND (costelem_type IN ('Material'))) THEN
    RAISE EXCEPTION 'itemcost of this type is invalid for Manufactured Item';
  END IF;
  
  IF (SELECT (COUNT(*) > 0)
      FROM item, costelem
      WHERE (item_id=pItemId)
        AND (costelem_id=pCostElemId)
        AND (item_type IN ('O', 'P'))
        AND (costelem_type IN ('Direct Labor', 'Overhead', 'Machine Overhead'))) THEN
    RAISE EXCEPTION 'itemcost of this type is invalid for Purchased Item';
  END IF;
  
  IF (pCost > 0) THEN
    SELECT NEXTVAL('itemcost_itemcost_id_seq') INTO _itemcost_id;
    INSERT INTO itemcost
      ( itemcost_id, itemcost_item_id, itemcost_costelem_id, itemcost_lowlevel,
        itemcost_stdcost, itemcost_posted, itemcost_actcost, itemcost_updated, itemcost_curr_id )
    VALUES
      ( _itemcost_id, pItemId, pCostElemId, FALSE,
        0, startOfTime(), pCost, CURRENT_DATE, pCurrId );

    --Only Post Cost to standard if the parameter is set to true
    IF (pPostToStandard) THEN
      IF (NOT checkPrivilege('PostStandardCosts')) THEN
        RAISE EXCEPTION 'You do not have privileges to poststandard itemcosts. Set api.itemcost post_to_standard to false';
      END IF;
      SELECT postcost(_itemcost_id) INTO _postcost_return;       
      IF (NOT _postcost_return) THEN
        RETURN -2;
      END IF;
    END IF;
  ELSE 
    RETURN -1;
  END IF;

  RETURN _itemcost_id;
  
END;
$_$;


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

--
-- TOC entry 1832 (class 1255 OID 146566627)
-- Dependencies: 2767 4536 8
-- Name: insertsalesline(api.salesline); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION insertsalesline(api.salesline) RETURNS boolean
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2012 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pNEW ALIAS FOR $1;
  _r RECORD;

BEGIN

  IF (NOT EXISTS (SELECT cohead_id FROM cohead WHERE cohead_number=pNEW.order_number)) THEN
    RAISE EXCEPTION 'Function insertSalesLine failed because Sales Order % not found', pNEW.order_number;
  END IF;

  IF (NOT EXISTS (SELECT item_id FROM item WHERE item_number=pNEW.item_number)) THEN
    RAISE EXCEPTION 'Function insertSalesLine failed because Item Number % not found', pNEW.item_number;
  END IF;

  SELECT * INTO _r
  FROM cohead, itemsite, item, whsinfo
  WHERE ((cohead_number=pNEW.order_number)
  AND (itemsite_warehous_id=warehous_id
  AND (itemsite_item_id=item_id)
  AND (itemsite_active)
  AND (item_number=pNEW.item_number)
  AND (warehous_active)
  AND (warehous_id=COALESCE(getWarehousId(pNEW.sold_from_site,'ALL'),cohead_warehous_id,fetchprefwarehousid()))));

  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Function insertSalesLine failed with unknown failure to retrieve Sales Order';
  END IF;

  INSERT INTO coitem (
    coitem_cohead_id,
    coitem_linenumber,
    coitem_itemsite_id,
    coitem_status,
    coitem_scheddate,
    coitem_promdate,
    coitem_qtyord,
    coitem_qty_uom_id,
    coitem_qty_invuomratio,
    coitem_qtyshipped,
    coitem_unitcost,
    coitem_price,
    coitem_price_uom_id,
    coitem_price_invuomratio,
    coitem_custprice,
    coitem_order_id,
    coitem_memo,
    coitem_imported,
    coitem_qtyreturned,
    coitem_custpn,
    coitem_order_type,
    coitem_substitute_item_id,
    coitem_prcost,
    coitem_taxtype_id,
    coitem_warranty,
    coitem_cos_accnt_id,
    coitem_rev_accnt_id)
  VALUES (
    _r.cohead_id,
    pNEW.line_number::INTEGER,
    _r.itemsite_id,
    pNEW.status,
    pNEW.scheduled_date,
    pNEW.promise_date,
    pNEW.qty_ordered,
    COALESCE(getUomId(pNEW.qty_uom),_r.item_inv_uom_id),
    itemuomtouomratio(_r.item_id,COALESCE(getUomId(pNEW.qty_uom),_r.item_inv_uom_id),_r.item_inv_uom_id),
    0,
    stdCost(_r.item_id),
    COALESCE(pNEW.net_unit_price,itemPrice(_r.item_id,_r.cohead_cust_id,
             _r.cohead_shipto_id,pNEW.qty_ordered,_r.cohead_curr_id,_r.cohead_orderdate)),
    COALESCE(getUomId(pNEW.price_uom),_r.item_price_uom_id),
    itemuomtouomratio(_r.item_id,COALESCE(getUomId(pNEW.price_uom),_r.item_price_uom_id),_r.item_price_uom_id),
    itemPrice(_r.item_id,_r.cohead_cust_id,_r.cohead_shipto_id,pNEW.qty_ordered,_r.cohead_curr_id,_r.cohead_orderdate),
    -1,
    pNEW.notes,
    true,
    0,
    pNEW.customer_pn,
    CASE
      WHEN ((pNEW.create_order  AND (_r.item_type = 'M')) OR 
           ((pNEW.create_order IS NULL) AND _r.itemsite_createwo)) THEN
        'W'
      WHEN ((pNEW.create_order AND (_r.item_type = 'P')) OR 
           ((pNEW.create_order IS NULL) AND _r.itemsite_createsopr)) THEN
        'R'
      WHEN ((pNEW.create_order AND (_r.item_type = 'P') AND (_r.itemsite_createsopo)) OR 
           ((pNEW.create_order IS NULL) AND _r.itemsite_createsopo)) THEN
        'P'
    END,
    getitemid(pNEW.substitute_for),
    pNEW.overwrite_po_price,
    COALESCE(getTaxTypeId(pNEW.tax_type), getItemTaxType(_r.itemsite_item_id, _r.cohead_taxzone_id)),
    pNEW.warranty,
    getGlAccntId(pNEW.alternate_cos_account),
    getGlAccntId(pNEW.alternate_rev_account)
    );

  RETURN TRUE;
END;
$_$;


ALTER FUNCTION public.insertsalesline(api.salesline) OWNER TO admin;

--
-- TOC entry 1833 (class 1255 OID 146566628)
-- Dependencies: 8
-- Name: intervaltominutes(interval); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION intervaltominutes(interval) RETURNS numeric
    LANGUAGE sql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT (EXTRACT(DAY FROM $1) * 24 * 60 +
          EXTRACT(HOUR FROM $1) * 60 +
          EXTRACT(MINUTE FROM $1) +
          ROUND((EXTRACT(SECOND FROM $1) / 60)::NUMERIC, 4))::NUMERIC AS result
$_$;


ALTER FUNCTION public.intervaltominutes(interval) OWNER TO admin;

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

CREATE FUNCTION invadjustment(integer, numeric, 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 invAdjustment($1, $2, $3, $4, CURRENT_TIMESTAMP, NULL);
END;
$_$;


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

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

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


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

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

CREATE FUNCTION invadjustment(integer, numeric, text, text, timestamp with time zone, 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
  pItemsiteid     ALIAS FOR $1;
  pQty            ALIAS FOR $2;
  pDocumentNumber ALIAS FOR $3;
  pComments       ALIAS FOR $4;
  pGlDistTS       ALIAS FOR $5;
  pCostValue      ALIAS FOR $6;
  _invhistid      INTEGER;
  _itemlocSeries  INTEGER;

BEGIN

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

  SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;
  SELECT postInvTrans( itemsite_id, 'AD', pQty,
                       'I/M', 'AD', pDocumentNumber, '',
                       ('Miscellaneous Adjustment for item ' || item_number || E'\n' ||  pComments),
                       costcat_asset_accnt_id, costcat_adjustment_accnt_id,
                       _itemlocSeries, pGlDistTS, pCostValue) INTO _invhistid
  FROM itemsite, item, costcat
  WHERE ( (itemsite_item_id=item_id)
   AND (itemsite_costcat_id=costcat_id)
   AND (itemsite_id=pItemsiteid) );

  RETURN _itemlocSeries;

END;
$_$;


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

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

CREATE FUNCTION invexpense(pitemsiteid integer, pqty numeric, pexpcatid integer, pdocumentnumber text, pcomments text, pgldistts timestamp with time zone DEFAULT now(), pprjid integer DEFAULT NULL::integer) RETURNS integer
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _invhistid INTEGER;
  _itemlocSeries INTEGER;

BEGIN

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

  SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;
  SELECT postInvTrans( itemsite_id, 'EX', pQty,
                       'I/M', 'EX', pDocumentNumber, '',
                       CASE WHEN (pQty < 0) THEN ('Reverse Material Expense for item ' || item_number || E'\n' ||  pComments)
                            ELSE  ('Material Expense for item ' || item_number || E'\n' ||  pComments)
                       END,
                       getPrjAccntId(pPrjid, expcat_exp_accnt_id), costcat_asset_accnt_id,
                       _itemlocSeries, pGlDistTS) INTO _invhistid
  FROM itemsite, item, costcat, expcat
  WHERE ( (itemsite_item_id=item_id)
   AND (itemsite_costcat_id=costcat_id)
   AND (itemsite_id=pItemsiteid)
   AND (expcat_id=pExpcatid) );

  INSERT INTO invhistexpcat (invhistexpcat_invhist_id, invhistexpcat_expcat_id)
                     VALUES (_invhistid,               pExpcatid);

  RETURN _itemlocSeries;

END;
$$;


ALTER FUNCTION public.invexpense(pitemsiteid integer, pqty numeric, pexpcatid integer, pdocumentnumber text, pcomments text, pgldistts timestamp with time zone, pprjid integer) OWNER TO admin;

--
-- TOC entry 1839 (class 1255 OID 146566633)
-- Dependencies: 4536 8
-- Name: invhistsense(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION invhistsense(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
  pInvhistId ALIAS FOR $1;
  _count INTEGER;
  _row RECORD;
  _sense INTEGER;
BEGIN

  SELECT invhist_transtype, invhist_ordnumber, itemsite_warehous_id
  INTO _row 
  FROM invhist 
    JOIN itemsite ON (itemsite_id=invhist_itemsite_id)
  WHERE (invhist_id=pInvhistId);

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

  RETURN _sense;
END;
$_$;


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

--
-- TOC entry 1840 (class 1255 OID 146566634)
-- Dependencies: 4536 8
-- Name: invhisttrig(); Type: FUNCTION; Schema: public; Owner: admin
--

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

--  Need to allow 'temporary' negative for average costed item that has been frozen.
--  Check at itemsite after all of the transactions have been thawed will ensure
--  that final Qty On Hand is positive. 
--  IF (NEW.invhist_qoh_after < 0 AND NEW.invhist_costmethod = 'A') THEN
--    RAISE EXCEPTION 'Invhist (%) is recording with average costing and is not allowed to have a negative quantity on hand.', NEW.invhist_id;
--  END IF;

  IF ( ( SELECT itemsite_freeze
         FROM itemsite
         WHERE (itemsite_id=NEW.invhist_itemsite_id) ) ) THEN
    NEW.invhist_posted = FALSE;
  END IF;

  -- never change the created timestamp, which defaults to CURRENT_TIMESTAMP
  IF (TG_OP != 'INSERT') THEN
    NEW.invhist_created = OLD.invhist_created;
  ELSE
    -- Always need a series id for distribution posting
    IF (NEW.invhist_series IS NULL) THEN
      RAISE EXCEPTION 'Column invhist_series may not be null.';
    END IF;
  END IF;

  RETURN NEW;

END;
$$;


ALTER FUNCTION public.invhisttrig() OWNER TO admin;

--
-- TOC entry 1841 (class 1255 OID 146566635)
-- Dependencies: 4536 8
-- Name: invoicetotal(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION invoicetotal(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
  pInvoiceId ALIAS FOR $1;
  _linesum   NUMERIC;
  _linetax   NUMERIC;
  _result    NUMERIC;
  _allocated NUMERIC;
  _posted    BOOLEAN;

BEGIN

  SELECT SUM(ROUND(COALESCE((invcitem_billed * invcitem_qty_invuomratio) *
                             (invcitem_price / COALESCE(invcitem_price_invuomratio,1)), 0),2))
                          INTO _linesum
  FROM invcitem
  WHERE (invcitem_invchead_id=pInvoiceId);

  -- TODO: why sum on the result of select round(sum(), 2)?
  SELECT SUM(tax) INTO _linetax
    FROM (SELECT ROUND(SUM(COALESCE(taxdetail_tax, 0)),2) AS tax 
            FROM tax 
            JOIN calculateTaxDetailSummary('I', pInvoiceId, 'T') ON (taxdetail_tax_id=tax_id)
	  GROUP BY tax_id) AS data;

  SELECT noNeg(invchead_freight + invchead_misc_amount + COALESCE(_linetax, 0) + COALESCE(_linesum, 0)),
         invchead_posted INTO _result, _posted
  FROM invchead
  WHERE (invchead_id=pInvoiceId);

  IF NOT FOUND THEN
    RETURN 0;
  END IF;

  IF (_posted) THEN
    SELECT COALESCE(SUM(currToCurr(arapply_curr_id, aropen_curr_id,
                                   arapply_applied, aropen_docdate)),0) INTO _allocated
     FROM arapply, aropen, invchead
    WHERE ( (invchead_posted)
      AND   (invchead_id=pInvoiceId)
      AND   (aropen_docnumber=invchead_invcnumber)
      AND   (aropen_doctype='I')
      AND   (arapply_target_aropen_id=aropen_id)
      AND   (arapply_reftype='S')
      AND   (invchead_posted) ) ;
  ELSE
    SELECT COALESCE(SUM(CASE WHEN((aropen_amount - aropen_paid) >=
                       currToCurr(aropenalloc_curr_id, aropen_curr_id,
                          aropenalloc_amount, aropen_docdate))
           THEN currToCurr(aropenalloc_curr_id, invchead_curr_id,
                   aropenalloc_amount, aropen_docdate)
           ELSE currToCurr(aropen_curr_id, invchead_curr_id,
                   aropen_amount - aropen_paid, aropen_docdate)
           END),0) INTO _allocated
     FROM invchead LEFT OUTER JOIN cohead ON (cohead_number=invchead_ordernumber)
                   JOIN aropenalloc ON ((aropenalloc_doctype='I' AND aropenalloc_doc_id=invchead_id) OR
                                        (aropenalloc_doctype='S' AND aropenalloc_doc_id=cohead_id))
                   JOIN aropen ON (aropen_id=aropenalloc_aropen_id AND (aropen_amount - aropen_paid) > 0.0)
    WHERE ( (NOT invchead_posted)
      AND   (invchead_id=pInvoiceId) );
  END IF;
  
  RETURN _result - COALESCE(_allocated, 0);

END;
$_$;


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

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

CREATE FUNCTION invreceipt(integer, numeric, 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 invReceipt($1, $2, $3, $4, $5, CURRENT_TIMESTAMP, NULL);
END;
$_$;


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

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

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


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

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

CREATE FUNCTION invreceipt(integer, numeric, text, text, text, timestamp with time zone, 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
  pItemsiteid ALIAS FOR $1;
  pQty ALIAS FOR $2;
  pOrdernumber ALIAS FOR $3;
  pDocumentNumber ALIAS FOR $4;
  pComments ALIAS FOR $5;
  pGlDistTS     ALIAS FOR $6;
  pCostValue ALIAS FOR $7;
  _invhistid INTEGER;
  _itemlocSeries INTEGER;

BEGIN

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

  SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;
  SELECT postInvTrans( itemsite_id, 'RX', pQty,
                       'I/M', 'RX', pDocumentNumber, '',
                       ('Miscellaneous Receipt for item ' || item_number || E'\n' ||  pComments),
                       costcat_asset_accnt_id, costcat_liability_accnt_id,
                       _itemlocSeries, pGlDistTS, pCostValue) INTO _invhistid
  FROM itemsite, item, costcat
  WHERE ( (itemsite_item_id=item_id)
   AND (itemsite_costcat_id=costcat_id)
   AND (itemsite_id=pItemsiteid) );

  RETURN _itemlocSeries;

END;
$_$;


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

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

CREATE FUNCTION invscrap(integer, numeric, 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 invScrap($1, $2, $3, $4, CURRENT_TIMESTAMP);
END;
$_$;


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

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

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


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

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

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


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

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

CREATE FUNCTION invscrap(integer, numeric, text, text, timestamp with time zone, 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
  pItemsiteid ALIAS FOR $1;
  pQty ALIAS FOR $2;
  pDocumentNumber ALIAS FOR $3;
  pComments ALIAS FOR $4;
  pGlDistTS ALIAS FOR $5;
  pInvHistId ALIAS FOR $6;
  pPrjid ALIAS FOR $7;
  _invhistid INTEGER;
  _itemlocSeries INTEGER;

BEGIN

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

  IF (pInvHistId IS NOT NULL) THEN
    SELECT invhist_series INTO _itemlocSeries
    FROM invhist
    WHERE invhist_id=pInvHistId;
  ELSE
    SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;
  END IF;
  
  SELECT postInvTrans( itemsite_id, 'SI', pQty,
                       'I/M', 'SI', pDocumentNumber, '',
                       CASE WHEN (pQty < 0) THEN ('Reverse Material Scrap for item ' || item_number || E'\n' ||  pComments)
                            ELSE ('Material Scrap for item ' || item_number || E'\n' ||  pComments)
                       END,
                       getPrjAccntId(pPrjid, costcat_scrap_accnt_id), costcat_asset_accnt_id,
                       _itemlocSeries, pGlDistTS, NULL, pInvHistId) INTO _invhistid
  FROM itemsite, item, costcat
  WHERE ( (itemsite_item_id=item_id)
   AND (itemsite_costcat_id=costcat_id)
   AND (itemsite_id=pItemsiteid) );

  RETURN _itemlocSeries;

END;
$_$;


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

--
-- TOC entry 1847 (class 1255 OID 146566643)
-- Dependencies: 8
-- Name: isdba(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION isdba(text DEFAULT NULL::text) RETURNS boolean
    LANGUAGE sql
    AS $_$
  SELECT (datdba=pg_roles.oid OR rolsuper) AS issuper
    FROM pg_database, pg_roles
  WHERE ((datname=current_database())
     AND (rolname=COALESCE($1, getEffectiveXtUser())));
$_$;


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

--
-- TOC entry 1848 (class 1255 OID 146566644)
-- Dependencies: 4536 8
-- Name: ismulticurr(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION ismulticurr() RETURNS boolean
    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.
BEGIN
  RETURN (SELECT (count(*) > 1)
          FROM curr_symbol);
END;
$$;


ALTER FUNCTION public.ismulticurr() OWNER TO admin;

--
-- TOC entry 1849 (class 1255 OID 146566645)
-- Dependencies: 4536 8
-- Name: isnumeric(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION isnumeric(text) RETURNS boolean
    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
  pText ALIAS FOR $1;
  _cursor INTEGER;

BEGIN

  IF ( (LENGTH(pText) = 0) OR (pText IS NULL) ) THEN
    RETURN FALSE;
  END IF;

  FOR _cursor IN 1..LENGTH(pText) LOOP
    IF (SUBSTRING(pText FROM _cursor FOR 1) NOT IN ( '0', '1', '2', '3', '4',
                                                     '5' ,'6' ,'7' ,'8' ,'9' )) THEN
      RETURN FALSE;
    END IF;
  END LOOP;

  RETURN TRUE;

END;
$_$;


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

--
-- TOC entry 1850 (class 1255 OID 146566646)
-- Dependencies: 4536 8
-- Name: issueallbalancetoshipping(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION issueallbalancetoshipping(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 issueAllBalanceToShipping('SO', $1, 0, NULL);
END;
$_$;


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

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

CREATE FUNCTION issueallbalancetoshipping(text, integer, integer, timestamp with time zone) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pordertype		ALIAS FOR $1;
  pheadid		ALIAS FOR $2;
  _itemlocSeries	INTEGER 		 := $3;
  _timestamp		TIMESTAMP WITH TIME ZONE := $4;
  _s			RECORD;

BEGIN
  IF (pordertype = 'SO') THEN
    FOR _s IN SELECT coitem_id,
                     CASE WHEN (fetchMetricBool('RequireSOReservations'))
                          THEN coitem_qtyreserved
                          ELSE noNeg( coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned - qtyAtShipping('SO', coitem_id) )
                     END AS _balance
	      FROM coitem LEFT OUTER JOIN (itemsite JOIN item ON (itemsite_item_id=item_id)) ON (coitem_itemsite_id=itemsite_id)
	      WHERE ( (coitem_status NOT IN ('C','X'))
                AND (item_type != 'K')
	       AND (coitem_cohead_id=pheadid) ) LOOP

      IF (_s.balance <> 0) THEN
	_itemlocSeries := issueToShipping(pordertype, _s.coitem_id, _s.balance, _itemlocSeries, _timestamp);
	IF (_itemlocSeries < 0) THEN
	  EXIT;
	END IF;
      END IF;
    END LOOP;

  ELSEIF (pordertype = 'TO') THEN
    FOR _s IN SELECT toitem_id,
                     noNeg( toitem_qty_ordered - toitem_qty_shipped - qtyAtShipping('TO', toitem_id) )
                      AS balance
	      FROM toitem
	      WHERE ( (toitem_status NOT IN ('C','X'))
	       AND (toitem_tohead_id=pheadid) ) LOOP

      IF (_s.balance <> 0) THEN
	_itemlocSeries := issueToShipping(pordertype, _s.toitem_id, _s.balance, _itemlocSeries, _timestamp);
	IF (_itemlocSeries < 0) THEN
	  EXIT;
	END IF;
      END IF;
    END LOOP;

  ELSE
    RETURN -1;
  END IF;

  RETURN _itemlocSeries;

END;
$_$;


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

--
-- TOC entry 1856 (class 1255 OID 146566648)
-- Dependencies: 4536 8
-- Name: issuelinebalancetoshipping(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION issuelinebalancetoshipping(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 issueLineBalanceToShipping('SO', $1, NULL);
END;
$_$;


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

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

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


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

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

CREATE FUNCTION issuelinebalancetoshipping(text, integer, timestamp with time zone, integer, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pordertype		ALIAS FOR $1;
  pitemid		ALIAS FOR $2;
  ptimestamp		ALIAS FOR $3;
  pitemlocseries       	ALIAS FOR $4;
  pinvhistid		ALIAS FOR $5;
  _itemlocSeries	INTEGER := 0;
  _qty			NUMERIC;

BEGIN
  _itemlocSeries := COALESCE(pitemlocseries,0);
  
  IF (pordertype = 'SO') THEN
    SELECT CASE WHEN (fetchMetricBool('RequireSOReservations'))
                THEN coitem_qtyreserved
                ELSE noNeg( coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned - qtyAtShipping('SO', coitem_id) )
           END INTO _qty
    FROM coitem
    WHERE (coitem_id=pitemid);
  ELSEIF (pordertype = 'TO') THEN
    SELECT noNeg( toitem_qty_ordered - toitem_qty_shipped - qtyAtShipping('TO', toitem_id) )
               INTO _qty
    FROM toitem
    WHERE (toitem_id=pitemid);
  ELSE
    RETURN -1;
  END IF;

  IF (_qty > 0) THEN
    _itemlocSeries := issueToShipping(pordertype, pitemid, _qty, _itemlocSeries, ptimestamp, pinvhistid);
  END IF;

  RETURN _itemlocSeries;

END;
$_$;


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

--
-- TOC entry 1859 (class 1255 OID 146566651)
-- Dependencies: 4536 8
-- Name: issues(text); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  IF (pTransType IN ('IM', 'IB', 'IT')) THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;

END;
$_$;


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

--
-- TOC entry 1860 (class 1255 OID 146566652)
-- Dependencies: 4536 8
-- Name: issuetoshipping(integer, numeric); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1861 (class 1255 OID 146566653)
-- Dependencies: 4536 8
-- Name: issuetoshipping(integer, numeric, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION issuetoshipping(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 issueToShipping('SO', $1, $2, $3, CURRENT_TIMESTAMP);
END;
$_$;


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

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

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


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

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

CREATE FUNCTION issuetoshipping(text, integer, numeric, integer, timestamp with time zone, integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pordertype		ALIAS FOR $1;
  pitemid		ALIAS FOR $2;
  pQty			ALIAS FOR $3;
  _itemlocSeries	INTEGER	:= $4;
  _timestamp		TIMESTAMP WITH TIME ZONE := $5;
  pinvhistid		ALIAS FOR $6;
  _coholdtype		TEXT;
  _invhistid		INTEGER;
  _shipheadid		INTEGER;
  _shipnumber		INTEGER;
  _cntctid              INTEGER;
  _p                    RECORD;
  _m                    RECORD;
  _value                NUMERIC;
  _warehouseid		INTEGER;
  _shipitemid     	INTEGER;
  _freight              NUMERIC;

BEGIN

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

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

  IF (pordertype = 'SO') THEN

    -- Check site security
    SELECT warehous_id INTO _warehouseid
    FROM coitem,itemsite,site()
    WHERE ((coitem_id=pitemid)
    AND (itemsite_id=coitem_itemsite_id)
    AND (warehous_id=itemsite_warehous_id));
          
    IF (NOT FOUND) THEN
      RETURN 0;
    END IF;

    -- Check for average cost items going negative
    IF ( SELECT ( (itemsite_costmethod='A') AND
                  ((itemsite_qtyonhand - round(pQty * coitem_qty_invuomratio, 6)) < 0.0) )
         FROM coitem JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
         WHERE (coitem_id=pitemid) ) THEN
      RETURN -20;
    END IF;

    -- Check auto registration
    IF ( SELECT COALESCE(itemsite_autoreg, FALSE)
         FROM coitem JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
         WHERE (coitem_id=pitemid) ) THEN
      SELECT COALESCE(crmacct_cntct_id_1, -1) INTO _cntctid
      FROM coitem JOIN cohead ON (cohead_id=coitem_cohead_id)
                  JOIN crmacct ON (crmacct_cust_id=cohead_cust_id)
      WHERE (coitem_id=pitemid);
      IF (_cntctid = -1) THEN
        RETURN -15;
      END IF;
    END IF; 
  
    SELECT shiphead_id INTO _shipheadid
    FROM shiphead, coitem JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
    WHERE ( (coitem_id=pitemid)
      AND   (shiphead_number=getOpenShipment(pordertype, coitem_cohead_id, itemsite_warehous_id)) );
    IF (NOT FOUND) THEN
      SELECT NEXTVAL('shiphead_shiphead_id_seq') INTO _shipheadid;

      _shipnumber := fetchShipmentNumber();
      IF (_shipnumber < 0) THEN
	RETURN -10;
      END IF;

      SELECT cohead_holdtype INTO _coholdtype
      FROM cohead, coitem
      WHERE ((cohead_id=coitem_cohead_id)
        AND  (coitem_id=pitemid));

      IF (_coholdtype = 'C') THEN
	RETURN -12;
      ELSIF (_coholdtype = 'P') THEN
	RETURN -13;
      ELSIF (_coholdtype = 'R') THEN
	RETURN -14;
      END IF;

      INSERT INTO shiphead
      ( shiphead_id, shiphead_number, shiphead_order_id, shiphead_order_type,
	shiphead_shipped,
	shiphead_sfstatus, shiphead_shipvia, shiphead_shipchrg_id,
	shiphead_freight, shiphead_freight_curr_id,
	shiphead_shipdate, shiphead_notes, shiphead_shipform_id )
      SELECT _shipheadid, _shipnumber, coitem_cohead_id, pordertype,
	     FALSE,
	     'N', cohead_shipvia,
	     CASE WHEN (cohead_shipchrg_id <= 0) THEN NULL
	          ELSE cohead_shipchrg_id
	     END,
	     cohead_freight, cohead_curr_id,
	     _timestamp::DATE, cohead_shipcomments,
	     CASE WHEN cohead_shipform_id = -1 THEN NULL
	          ELSE cohead_shipform_id
	     END
      FROM cohead, coitem
      WHERE ((coitem_cohead_id=cohead_id)
         AND (coitem_id=pitemid) );

      UPDATE pack
      SET pack_shiphead_id = _shipheadid,
	  pack_printed = FALSE
      FROM coitem
      WHERE ((pack_head_id=coitem_cohead_id)
	AND  (pack_shiphead_id IS NULL)
	AND  (pack_head_type='SO')
	AND  (coitem_id=pitemid));

    ELSE
      UPDATE pack
      SET pack_printed = FALSE
      FROM coitem
      WHERE ((pack_head_id=coitem_cohead_id)
	AND  (pack_shiphead_id=_shipheadid)
	AND  (pack_head_type='SO')
	AND  (coitem_id=pitemid));
    END IF;

    -- Handle g/l transaction
    SELECT postInvTrans( itemsite_id, 'SH', (pQty * coitem_qty_invuomratio),
			   'S/R', porderType,
			   formatSoNumber(coitem_id), shiphead_number,
                           ('Issue ' || item_number || ' to Shipping for customer ' || cohead_billtoname),
			   getPrjAccntId(cohead_prj_id, costcat_shipasset_accnt_id), costcat_asset_accnt_id,
			   _itemlocSeries, _timestamp, NULL, pinvhistid ) INTO _invhistid
    FROM coitem, cohead, itemsite, item, costcat, shiphead
    WHERE ( (coitem_cohead_id=cohead_id)
     AND (coitem_itemsite_id=itemsite_id)
     AND (itemsite_item_id=item_id)
     AND (itemsite_costcat_id=costcat_id)
     AND (coitem_id=pitemid)
     AND (shiphead_id=_shipheadid) );

    SELECT (invhist_unitcost * invhist_invqty) INTO _value
    FROM invhist
    WHERE (invhist_id=_invhistid);

    _shipitemid := nextval('shipitem_shipitem_id_seq');
    INSERT INTO shipitem
    ( shipitem_id, shipitem_shiphead_id, shipitem_orderitem_id, shipitem_qty,
      shipitem_transdate, shipitem_trans_username, shipitem_invoiced,
      shipitem_value, shipitem_invhist_id )
    VALUES
    ( _shipitemid, _shipheadid, pitemid, pQty,
      _timestamp, getEffectiveXtUser(), FALSE,
      _value, 
      CASE WHEN _invhistid = -1 THEN
        NULL
      ELSE 
        _invhistid
      END );

    -- Handle reservation
    IF (fetchmetricbool('EnableSOReservations')) THEN
      -- Remember what was reserved so we can re-reserve if this issue is returned
      INSERT INTO shipitemrsrv 
        (shipitemrsrv_shipitem_id, shipitemrsrv_qty)
      SELECT _shipitemid, least(pQty,coitem_qtyreserved)
      FROM coitem
      WHERE ((coitem_id=pitemid)
      AND (coitem_qtyreserved > 0));

      -- Update sales order
      UPDATE coitem
        SET coitem_qtyreserved = noNeg(coitem_qtyreserved - pQty)
      WHERE(coitem_id=pitemid);
    END IF;

    -- Calculate shipment freight
    SELECT calcShipFreight(_shipheadid) INTO _freight;
    UPDATE shiphead SET shiphead_freight=_freight
    WHERE (shiphead_id=_shipheadid);

  ELSEIF (pordertype = 'TO') THEN

    -- Check site security
    IF (fetchMetricBool('MultiWhs')) THEN
      SELECT warehous_id INTO _warehouseid
      FROM toitem, tohead, site()
      WHERE ( (toitem_id=pitemid)
        AND   (tohead_id=toitem_tohead_id)
        AND   (warehous_id=tohead_src_warehous_id) );
          
      IF (NOT FOUND) THEN
        RETURN 0;
      END IF;
    END IF;

    SELECT postInvTrans( itemsite_id, 'SH', pQty, 'S/R',
			 pordertype, formatToNumber(toitem_id), '', 'Issue to Shipping',
			 costcat_shipasset_accnt_id, costcat_asset_accnt_id,
			 _itemlocSeries, _timestamp) INTO _invhistid
    FROM tohead, toitem, itemsite, costcat
    WHERE ((tohead_id=toitem_tohead_id)
      AND  (itemsite_item_id=toitem_item_id)
      AND  (itemsite_warehous_id=tohead_src_warehous_id)
      AND  (itemsite_costcat_id=costcat_id)
      AND  (toitem_id=pitemid) );

    SELECT (invhist_unitcost * invhist_invqty) INTO _value
    FROM invhist
    WHERE (invhist_id=_invhistid);

    SELECT shiphead_id INTO _shipheadid
    FROM shiphead, toitem JOIN tohead ON (tohead_id=toitem_tohead_id)
    WHERE ( (toitem_id=pitemid)
      AND   (shiphead_number=getOpenShipment(pordertype, tohead_id, tohead_src_warehous_id)) );

    IF (NOT FOUND) THEN
      _shipheadid := NEXTVAL('shiphead_shiphead_id_seq');

      _shipnumber := fetchShipmentNumber();
      IF (_shipnumber < 0) THEN
	RETURN -10;
      END IF;

      INSERT INTO shiphead
      ( shiphead_id, shiphead_number, shiphead_order_id, shiphead_order_type,
	shiphead_shipped,
	shiphead_sfstatus, shiphead_shipvia, shiphead_shipchrg_id,
	shiphead_freight, shiphead_freight_curr_id,
	shiphead_shipdate, shiphead_notes, shiphead_shipform_id )
      SELECT _shipheadid, _shipnumber, tohead_id, pordertype,
	     FALSE,
	     'N', tohead_shipvia, tohead_shipchrg_id,
	     tohead_freight + SUM(toitem_freight), tohead_freight_curr_id,
	     _timestamp::DATE, tohead_shipcomments, tohead_shipform_id
      FROM tohead, toitem
      WHERE ((toitem_tohead_id=tohead_id)
         AND (tohead_id IN (SELECT toitem_tohead_id
			    FROM toitem
			    WHERE (toitem_id=pitemid))) )
      GROUP BY tohead_id, tohead_shipvia, tohead_shipchrg_id, tohead_freight,
	       tohead_freight_curr_id, tohead_shipcomments, tohead_shipform_id;
    END IF;

    INSERT INTO shipitem
    ( shipitem_shiphead_id, shipitem_orderitem_id, shipitem_qty,
      shipitem_transdate, shipitem_trans_username, shipitem_invoiced,
      shipitem_value, shipitem_invhist_id )
    VALUES
    ( _shipheadid, pitemid, pQty,
      _timestamp, getEffectiveXtUser(), FALSE,
      _value, 
      CASE WHEN _invhistid = -1 THEN NULL
           ELSE _invhistid
      END
    );

  ELSE
    RETURN -11;
  END IF;

  RETURN _itemlocSeries;

END;
$_$;


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

--
-- TOC entry 1867 (class 1255 OID 146566660)
-- Dependencies: 4536 8
-- Name: issuewomaterial(integer, numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION issuewomaterial(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
  pWomatlid ALIAS FOR $1;
  pQty ALIAS FOR $2;
  _itemlocSeries INTEGER;

BEGIN

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

END;
$_$;


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

--
-- TOC entry 1865 (class 1255 OID 146566658)
-- Dependencies: 4536 8
-- Name: issuewomaterial(integer, numeric, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION issuewomaterial(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.
DECLARE
  pWomatlid ALIAS FOR $1;
  pQty ALIAS FOR $2;
  pItemlocSeries ALIAS FOR $3;
  _p RECORD;
  _invhistid INTEGER;
  _itemlocSeries INTEGER;

BEGIN
  RETURN issueWoMaterial(pWomatlid, pQty, pItemlocSeries, now());
END;
$_$;


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

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

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

BEGIN
  RETURN issueWoMaterial(pWomatlid, pQty, pMarkPush, now());
END;
$_$;


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

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

CREATE FUNCTION issuewomaterial(integer, numeric, 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
  pWomatlid ALIAS FOR $1;
  pQty ALIAS FOR $2;
  pItemlocSeries ALIAS FOR $3;
  pMarkPush ALIAS FOR $4;
  _itemlocSeries INTEGER;

BEGIN
  RETURN issueWoMaterial(pWomatlid, pQty, pItemlocSeries,now());
END;
$_$;


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

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

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


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

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

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

BEGIN

  SELECT issueWoMaterial(pWomatlid, pQty, 0, pGlDistTS) INTO _itemlocSeries;
  IF (_itemlocSeries < 0) THEN
    RETURN _itemlocSeries;
  END IF;

  IF (pMarkPush) THEN
    UPDATE womatl
    SET womatl_issuemethod='S'
    WHERE ((womatl_issuemethod='M')
     AND (womatl_id=pWomatlid));
  END IF;

  RETURN _itemlocSeries;

END;
$_$;


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

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

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

BEGIN

  SELECT issueWoMaterial(pWomatlid, pQty, pItemlocSeries, pGlDistTS) INTO _itemlocSeries;

  IF (pMarkPush) THEN
    UPDATE womatl
    SET womatl_issuemethod='S'
    WHERE ((womatl_issuemethod='M')
     AND (womatl_id=pWomatlid));
  END IF;

  RETURN _itemlocSeries;

END;
$_$;


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

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

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

BEGIN

  SELECT item_id,
         itemsite_id AS c_itemsite_id,
         wo_itemsite_id AS p_itemsite_id,
         itemsite_loccntrl, itemsite_controlmethod,
         womatl_wo_id, womatl_qtyreq, itemsite_item_id, womatl_uom_id, wo_prj_id,
         roundQty(item_fractional, itemuomtouom(itemsite_item_id, womatl_uom_id, NULL, pQty)) AS qty,
         formatWoNumber(wo_id) AS woNumber,
         CASE WHEN(itemsite_costmethod='J' AND item_type='P' AND poitem_id IS NOT NULL) THEN poitem_unitprice
              WHEN(itemsite_costmethod IN ('A','J')) THEN avgcost(itemsite_id)
              WHEN(itemsite_costmethod='S') THEN stdcost(itemsite_item_id)
              ELSE 0.0
         END AS cost,
         womatl_issuemethod AS issueMethod INTO _p
  FROM womatl JOIN wo ON (wo_id=womatl_wo_id)
              JOIN itemsite ON (itemsite_id=womatl_itemsite_id)
              JOIN item ON (item_id=itemsite_item_id)
              LEFT OUTER JOIN poitem ON (poitem_order_id=womatl_id AND poitem_order_type='W')
  WHERE (womatl_id=pWomatlid);

  IF (pQty < 0) THEN
    RETURN pItemlocSeries;
  END IF;

  IF (pItemlocSeries <> 0) THEN
    _itemlocSeries := pItemlocSeries;
  ELSE
    SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;
  END IF;
  SELECT postInvTrans( ci.itemsite_id, 'IM', _p.qty,
                      'W/O', 'WO', _p.woNumber, '',
                      ('Material ' || item_number || ' Issue to Work Order'),
                      getPrjAccntId(_p.wo_prj_id, pc.costcat_wip_accnt_id),
                      cc.costcat_asset_accnt_id, _itemlocSeries, pGlDistTS,
                      NULL, pInvhistid ) INTO _invhistid
  FROM itemsite AS ci, itemsite AS pi,
       costcat AS cc, costcat AS pc,
       item
  WHERE ( (ci.itemsite_costcat_id=cc.costcat_id)
   AND (pi.itemsite_costcat_id=pc.costcat_id)
   AND (ci.itemsite_id=_p.c_itemsite_id)
   AND (pi.itemsite_id=_p.p_itemsite_id)
   AND (ci.itemsite_item_id=item_id) );

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

--  Increase the parent W/O's WIP value by the value of the issued components
  UPDATE wo
  SET wo_wipvalue = (wo_wipvalue + (_p.cost * _p.qty)),
      wo_postedvalue = (wo_postedvalue + (_p.cost * _p.qty))
  WHERE (wo_id=_p.womatl_wo_id);

  UPDATE womatl
  SET womatl_qtyiss = (womatl_qtyiss + itemuomtouom(_p.itemsite_item_id, NULL, _p.womatl_uom_id, _p.qty)),
      womatl_lastissue = pGlDistTS::DATE
  WHERE (womatl_id=pWomatlid);

  UPDATE wo
  SET wo_status='I'
  WHERE ( (wo_status <> 'I')
   AND (wo_id=_p.womatl_wo_id) );

  RETURN _itemlocSeries;

END;
$_$;


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

--
-- TOC entry 1871 (class 1255 OID 146566665)
-- Dependencies: 4536 8
-- Name: issuewomaterialbatch(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;

  FOR _r IN SELECT womatl_id, 
              CASE WHEN (womatl_qtyreq >= 0) THEN
                roundQty(itemuomfractionalbyuom(item_id, womatl_uom_id), noNeg(womatl_qtyreq - womatl_qtyiss))
              ELSE
                roundQty(itemuomfractionalbyuom(item_id, womatl_uom_id), noNeg(womatl_qtyiss * -1)) 
              END AS qty
            FROM womatl, itemsite, item
            WHERE ( (womatl_itemsite_id=itemsite_id)
             AND (itemsite_item_id=item_id)
             AND (womatl_issuemethod IN ('S', 'M'))
             AND (womatl_wo_id=pWoid) ) LOOP

    IF (_r.qty > 0) THEN
      SELECT issueWoMaterial(_r.womatl_id, _r.qty, _itemlocSeries, TRUE) INTO _itemlocSeries;
    END IF;

  END LOOP;

  RETURN _itemlocSeries;

END;
$_$;


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

--
-- TOC entry 1872 (class 1255 OID 146566666)
-- Dependencies: 4536 8
-- Name: itemaltcapinvrat(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  RETURN itemUOMRatioByType(pItemid, 'AltCapacity');
END;
$_$;


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

--
-- TOC entry 1873 (class 1255 OID 146566667)
-- Dependencies: 4536 8
-- Name: itemaltcapuom(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  RETURN itemUOMByType(pItemid, 'AltCapacity');
END;
$_$;


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

--
-- TOC entry 1874 (class 1255 OID 146566668)
-- Dependencies: 4536 8
-- Name: itemcapinvrat(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  RETURN itemUOMRatioByType(pItemid, 'Capacity');
END;
$_$;


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

--
-- TOC entry 1875 (class 1255 OID 146566669)
-- Dependencies: 4536 8
-- Name: itemcapuom(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  RETURN itemUOMByType(pItemid, 'Capacity');
END;
$_$;


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

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

CREATE FUNCTION itemcharprice(integer, integer, text, integer, numeric) 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
  pItemid ALIAS FOR $1;
  pCharid ALIAS FOR $2;
  pCharValue ALIAS FOR $3;
  pCustid ALIAS FOR $4;
  pQty ALIAS FOR $5;

BEGIN
  RETURN itemCharPrice(pItemid, pCharid, pCharValue, pCustid, -1, pQty, baseCurrId(), CURRENT_DATE);
END;
$_$;


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

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

CREATE FUNCTION itemcharprice(integer, integer, text, integer, integer, numeric) 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
  pItemid ALIAS FOR $1;
  pCharid ALIAS FOR $2;
  pCharValue ALIAS FOR $3;
  pCustid ALIAS FOR $4;
  pShiptoid ALIAS FOR $5;
  pQty ALIAS FOR $6;

BEGIN
  RETURN itemCharPrice(pItemid, pCharid, pCharValue, pCustid, pShiptoid, pQty, baseCurrId(), CURRENT_DATE);
END;
$_$;


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

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

CREATE FUNCTION itemcharprice(integer, integer, text, integer, integer, numeric, 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
  pItemid ALIAS FOR $1;
  pCharid ALIAS FOR $2;
  pCharValue ALIAS FOR $3;
  pCustid ALIAS FOR $4;
  pShiptoid ALIAS FOR $5;
  pQty ALIAS FOR $6;
  pCurrid ALIAS FOR $7;

BEGIN
  RETURN itemCharPrice(pItemid, pCharid, pCharValue, pCustid, pShiptoid, pQty, pCurrid, CURRENT_DATE);
END;
$_$;


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

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

CREATE FUNCTION itemcharprice(integer, integer, text, integer, integer, numeric, integer, date) RETURNS numeric
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pItemid ALIAS FOR $1;
  pCharid ALIAS FOR $2;
  pCharValue ALIAS FOR $3;
  pCustid ALIAS FOR $4;
  pShiptoid ALIAS FOR $5;
  pQty ALIAS FOR $6;
  pCurrid ALIAS FOR $7;
  pEffective ALIAS FOR $8;

BEGIN
  RETURN itemCharPrice(pItemid, pCharid, pCharValue, pCustid, pShiptoid, pQty, pCurrid, CURRENT_DATE, CURRENT_DATE);
END;
$_$;


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

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

CREATE FUNCTION itemcharprice(integer, integer, text, integer, integer, numeric, integer, date, date) RETURNS numeric
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pItemid ALIAS FOR $1;
  pCharid ALIAS FOR $2;
  pCharValue ALIAS FOR $3;
  pCustid ALIAS FOR $4;
  pShiptoid ALIAS FOR $5;
  pQty ALIAS FOR $6;
  pCurrid ALIAS FOR $7;
  pEffective ALIAS FOR $8;
  pAsOf ALIAS FOR $9;
  _price NUMERIC;
  _sales NUMERIC;
  _item RECORD;
  _iteminvpricerat NUMERIC;

BEGIN
-- If the charass_value passed in is NULL, we can skip this function
  IF (pCharValue IS NULL) THEN 
    RETURN 0;
  END IF;

-- Return the itemCharPrice in the currency passed in as pCurrid

-- Get a value here so we do not have to call the function several times
  SELECT iteminvpricerat(pItemid)
    INTO _iteminvpricerat;

-- First get a sales price if any so we when we find other prices
-- we can determine if we want that price or this price.
--  Check for a Sale Price
  SELECT currToCurr(ipshead_curr_id, pCurrid,
                      ipsprice_price - (ipsprice_price * cust_discntprcnt),
                      pEffective) INTO _sales
  FROM (
  SELECT ipsitem_ipshead_id AS ipsprice_ipshead_id,
         itemuomtouom(ipsitem_item_id, ipsitem_qty_uom_id, NULL, ipsitem_qtybreak) AS ipsprice_qtybreak,
         (ipsitemchar_price * itemuomtouomratio(ipsitem_item_id, NULL, ipsitem_price_uom_id)) * _iteminvpricerat AS ipsprice_price
    FROM ipsiteminfo,ipsitemchar
   WHERE((ipsitem_item_id=pItemid)
    AND (ipsitemchar_char_id=pCharid)
    AND (ipsitemchar_value=pCharValue)
    AND (ipsitemchar_ipsitem_id=ipsitem_id))
       ) AS
        ipsprice, ipshead, sale, custinfo
  WHERE ( (ipsprice_ipshead_id=ipshead_id)
   AND (sale_ipshead_id=ipshead_id)
   AND (pAsOf BETWEEN sale_startdate AND sale_enddate)
   AND (ipsprice_qtybreak <= pQty)
   AND (cust_id=pCustid) )
  ORDER BY ipsprice_qtybreak DESC, ipsprice_price ASC
  LIMIT 1;

--  Check for a Customer Shipto Price
  SELECT currToCurr(ipshead_curr_id, pCurrid, ipsprice_price, pEffective) INTO _price
  FROM (
  SELECT ipsitem_ipshead_id AS ipsprice_ipshead_id,
         itemuomtouom(ipsitem_item_id, ipsitem_qty_uom_id, NULL, ipsitem_qtybreak) AS ipsprice_qtybreak,
         (ipsitemchar_price * itemuomtouomratio(ipsitem_item_id, NULL, ipsitem_price_uom_id)) * _iteminvpricerat AS ipsprice_price
    FROM ipsiteminfo,ipsitemchar
   WHERE ((ipsitem_item_id=pItemid)
    AND (ipsitemchar_char_id=pCharid)
    AND (ipsitemchar_value=pCharValue)
    AND (ipsitemchar_ipsitem_id=ipsitem_id))
       ) AS
        ipsprice, ipshead, ipsass
  WHERE ( (ipsprice_ipshead_id=ipshead_id)
   AND (ipsass_ipshead_id=ipshead_id)
   AND (pAsOf BETWEEN ipshead_effective AND (ipshead_expires - 1))
   AND (ipsprice_qtybreak <= pQty)
   AND (ipsass_shipto_id != -1)
   AND (ipsass_shipto_id=pShiptoid) )
  ORDER BY ipsprice_qtybreak DESC, ipsprice_price ASC
  LIMIT 1;

  IF (_price IS NOT NULL) THEN
    IF ((_sales IS NOT NULL) AND (_sales < _price)) THEN
      RETURN _sales;
    END IF;
    RETURN _price;
  END IF;

--  Check for a Customer Shipto Pattern Price
  SELECT currToCurr(ipshead_curr_id, pCurrid, ipsprice_price, pEffective) INTO _price
  FROM (
  SELECT ipsitem_ipshead_id AS ipsprice_ipshead_id,
         itemuomtouom(ipsitem_item_id, ipsitem_qty_uom_id, NULL, ipsitem_qtybreak) AS ipsprice_qtybreak,
         (ipsitemchar_price * itemuomtouomratio(ipsitem_item_id, NULL, ipsitem_price_uom_id)) * _iteminvpricerat AS ipsprice_price
    FROM ipsiteminfo,ipsitemchar
   WHERE ((ipsitem_item_id=pItemid)
    AND (ipsitemchar_char_id=pCharid)
    AND (ipsitemchar_value=pCharValue)
    AND (ipsitemchar_ipsitem_id=ipsitem_id))
       ) AS
        ipsprice, ipshead, ipsass, shiptoinfo
  WHERE ( (ipsprice_ipshead_id=ipshead_id)
   AND (ipsass_ipshead_id=ipshead_id)
   AND (pAsOf BETWEEN ipshead_effective AND (ipshead_expires - 1))
   AND (ipsprice_qtybreak <= pQty)
   AND (COALESCE(length(ipsass_shipto_pattern), 0) > 0)
   AND (shipto_num ~ ipsass_shipto_pattern)
   AND (ipsass_cust_id=pCustid)
   AND (shipto_id=pShiptoid) )
  ORDER BY ipsprice_qtybreak DESC, ipsprice_price ASC
  LIMIT 1;

  IF (_price IS NOT NULL) THEN
    IF ((_sales IS NOT NULL) AND (_sales < _price)) THEN
      RETURN _sales;
    END IF;
    RETURN _price;
  END IF;

--  Check for a Customer Price
  SELECT currToCurr(ipshead_curr_id, pCurrid, ipsprice_price, pEffective) INTO _price
  FROM (
  SELECT ipsitem_ipshead_id AS ipsprice_ipshead_id,
         itemuomtouom(ipsitem_item_id, ipsitem_qty_uom_id, NULL, ipsitem_qtybreak) AS ipsprice_qtybreak,
         (ipsitemchar_price * itemuomtouomratio(ipsitem_item_id, NULL, ipsitem_price_uom_id)) * _iteminvpricerat AS ipsprice_price
    FROM ipsiteminfo,ipsitemchar
   WHERE ((ipsitem_item_id=pItemid)
    AND (ipsitemchar_char_id=pCharid)
    AND (ipsitemchar_value=pCharValue)
    AND (ipsitemchar_ipsitem_id=ipsitem_id))
       ) AS
        ipsprice, ipshead, ipsass
  WHERE ( (ipsprice_ipshead_id=ipshead_id)
   AND (ipsass_ipshead_id=ipshead_id)
   AND (pAsOf BETWEEN ipshead_effective AND (ipshead_expires - 1))
   AND (ipsprice_qtybreak <= pQty)
   AND (COALESCE(length(ipsass_shipto_pattern), 0) = 0)
   AND (ipsass_cust_id=pCustid) )
  ORDER BY ipsprice_qtybreak DESC, ipsprice_price ASC
  LIMIT 1;

  IF (_price IS NOT NULL) THEN
    IF ((_sales IS NOT NULL) AND (_sales < _price)) THEN
      RETURN _sales;
    END IF;
    RETURN _price;
  END IF;

--  Check for a Customer Type Price
  SELECT currToCurr(ipshead_curr_id, pCurrid, ipsprice_price, pEffective) INTO _price
  FROM (
  SELECT ipsitem_ipshead_id AS ipsprice_ipshead_id,
         itemuomtouom(ipsitem_item_id, ipsitem_qty_uom_id, NULL, ipsitem_qtybreak) AS ipsprice_qtybreak,
         (ipsitemchar_price * itemuomtouomratio(ipsitem_item_id, NULL, ipsitem_price_uom_id)) * _iteminvpricerat AS ipsprice_price
    FROM ipsiteminfo,ipsitemchar
   WHERE((ipsitem_item_id=pItemid)
    AND (ipsitemchar_char_id=pCharid)
    AND (ipsitemchar_value=pCharValue)
    AND (ipsitemchar_ipsitem_id=ipsitem_id))
       ) AS
        ipsprice, ipshead, ipsass, custinfo
  WHERE ( (ipsprice_ipshead_id=ipshead_id)
   AND (ipsass_ipshead_id=ipshead_id)
   AND (ipsass_custtype_id=cust_custtype_id)
   AND (pAsOf BETWEEN ipshead_effective AND (ipshead_expires - 1))
   AND (ipsprice_qtybreak <= pQty)
   AND (cust_id=pCustid) )
  ORDER BY ipsprice_qtybreak DESC, ipsprice_price ASC
  LIMIT 1;

  IF (_price IS NOT NULL) THEN
    IF ((_sales IS NOT NULL) AND (_sales < _price)) THEN
      RETURN _sales;
    END IF;
    RETURN _price;
  END IF;

--  Check for a Customer Type Pattern Price
  SELECT currToCurr(ipshead_curr_id, pCurrid, ipsprice_price, pEffective) INTO _price
  FROM (
  SELECT ipsitem_ipshead_id AS ipsprice_ipshead_id,
         itemuomtouom(ipsitem_item_id, ipsitem_qty_uom_id, NULL, ipsitem_qtybreak) AS ipsprice_qtybreak,
         (ipsitemchar_price * itemuomtouomratio(ipsitem_item_id, NULL, ipsitem_price_uom_id)) * _iteminvpricerat AS ipsprice_price
    FROM ipsiteminfo,ipsitemchar
   WHERE ((ipsitem_item_id=pItemid)
    AND (ipsitemchar_char_id=pCharid)
    AND (ipsitemchar_value=pCharValue)
    AND (ipsitemchar_ipsitem_id=ipsitem_id))
       ) AS
        ipsprice, ipshead, ipsass, custtype, custinfo
  WHERE ( (ipsprice_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 (ipsprice_qtybreak <= pQty)
   AND (cust_id=pCustid) )
  ORDER BY ipsprice_qtybreak DESC, ipsprice_price ASC
  LIMIT 1;

  IF (_price IS NOT NULL) THEN
    IF ((_sales IS NOT NULL) AND (_sales < _price)) THEN
      RETURN _sales;
    END IF;
    RETURN _price;
  END IF;

-- If we have not found another price yet and we have a
-- sales price we will use that.
  IF (_sales IS NOT NULL) THEN
    RETURN _sales;
  END IF;

--  Check for a list price
  SELECT MIN(currToLocal(pCurrid,
                       charass_price - (charass_price * COALESCE(cust_discntprcnt, 0)),
                       pEffective)) AS price,
         item_exclusive INTO _item
  FROM charass,item LEFT OUTER JOIN custinfo ON (cust_id=pCustid)
  WHERE ((item_id=pItemid)
   AND (charass_char_id=pCharid)
   AND (charass_value=pCharValue)
   AND (charass_target_type='I')
   AND (charass_target_id=item_id))
  GROUP BY item_exclusive;
  IF (FOUND) THEN
    IF (NOT _item.item_exclusive) THEN
      IF (_item.price < 0) THEN
        RETURN 0;
      ELSE
        RETURN _item.price;
      END IF;
    ELSE
      RETURN 0;
    END IF;
  ELSE
    RETURN 0;
  END IF;

END;
$_$;


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

--
-- TOC entry 1881 (class 1255 OID 146566675)
-- Dependencies: 4536 8
-- Name: itemcharvalue(integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION itemcharvalue(integer, 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
  pItemid ALIAS FOR $1;
  pCharName ALIAS FOR $2;
  _value TEXT;

BEGIN

  SELECT charass_value INTO _value
  FROM charass, char
  WHERE ( (charass_char_id=char_id)
   AND (charass_target_type='I')
   AND (charass_target_id=pItemid)
   AND (char_name=pCharName) );
  IF (NOT FOUND) THEN
    _value = '';
  END IF;

  RETURN _value;

END;
$_$;


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

--
-- TOC entry 1882 (class 1255 OID 146566676)
-- Dependencies: 4536 8
-- Name: itemcost(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION itemcost(integer) RETURNS numeric
    LANGUAGE plpgsql STABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pItemsiteid ALIAS FOR $1;
  _cost NUMERIC;
BEGIN
  SELECT CASE WHEN (itemsite_costmethod='A' AND itemsite_qtyonhand != 0.0) THEN (itemsite_value / itemsite_qtyonhand)
              WHEN (itemsite_costmethod='A' AND itemsite_qtyonhand = 0.0) THEN 0.0
              WHEN (itemsite_costmethod='N') THEN 0.0
              ELSE stdCost(itemsite_item_id)
         END INTO _cost
    FROM itemsite
   WHERE(itemsite_id=pItemsiteid);
  RETURN _cost;
END;
$_$;


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

--
-- TOC entry 1883 (class 1255 OID 146566677)
-- Dependencies: 4536 8
-- Name: iteminventoryuominuse(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION iteminventoryuominuse(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;
  _uomid INTEGER;
  _result INTEGER;
BEGIN
  SELECT item_inv_uom_id INTO _uomid
    FROM item
   WHERE(item_id=pItemid);

  SELECT itemuomconv_id INTO _result
    FROM itemuomconv
   WHERE(itemuomconv_item_id=pItemid)
   LIMIT 1;
  IF (FOUND) THEN
    RETURN TRUE;
  END IF;

  SELECT itemsite_id INTO _result
  FROM itemsite WHERE ( (itemsite_item_id=pItemid)
                  AND   ((itemsite_qtyonhand <> 0) OR (itemsite_nnqoh <> 0)) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN TRUE;
  END IF;

  RETURN FALSE;
END;
$_$;


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

--
-- TOC entry 1884 (class 1255 OID 146566678)
-- Dependencies: 4536 8
-- Name: iteminvpricerat(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

  IF(pItemid IS NULL) THEN
    RETURN 1.0;
  END IF;

  SELECT item_inv_uom_id, item_price_uom_id
    INTO _fromUomid, _toUomid
    FROM item
   WHERE(item_id=pItemid);

  IF(NOT FOUND) THEN
    RAISE EXCEPTION 'No item record found for item_id %', pItemid;
  END IF;

  IF(_fromUomid = _toUomid) THEN
    RETURN 1.0;
  END IF;

  -- Return the ration as inventory / price
  SELECT CASE WHEN(itemuomconv_from_uom_id=_fromUomid) THEN itemuomconv_from_value / itemuomconv_to_value
              ELSE itemuomconv_to_value / itemuomconv_from_value
         END
    INTO _ratio
    FROM itemuomconv
   WHERE((((itemuomconv_from_uom_id=_fromUomid) AND (itemuomconv_to_uom_id=_toUomid))
       OR ((itemuomconv_from_uom_id=_toUomid) AND (itemuomconv_to_uom_id=_fromUomid)))
     AND (itemuomconv_item_id=pItemid));

  IF(NOT FOUND) THEN
    RAISE EXCEPTION 'No itemuomconv record found for item_id % to item_price_uomid %', pItemid, _toUomid;
  END IF;
  
  RETURN _ratio;
END;
$_$;


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

--
-- TOC entry 1885 (class 1255 OID 146566679)
-- Dependencies: 2684 4536 8
-- Name: itemipsprice(integer, integer, integer, numeric, integer, integer, integer, date, date, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION itemipsprice(pitemid integer, pcustid integer, pshiptoid integer, pqty numeric, pqtyuom integer, ppriceuom integer, pcurrid integer, peffective date, pasof date, psiteid integer) RETURNS SETOF itemprice
    LANGUAGE plpgsql
    AS $$
-- Copyright (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  itemprice%ROWTYPE;
  _sale RECORD;
  _ips RECORD;
  _item RECORD;
  _cust RECORD;
  _shipto RECORD;
  _iteminvpricerat NUMERIC := 1.0;
  _listprice NUMERIC := 0.0;
  _qty NUMERIC;
  _asof DATE;
  _wholesalepricecosting BOOLEAN := false;
  _long30markups BOOLEAN := false;
  _itempricingprecedence BOOLEAN := false;

BEGIN
  _wholesalepricecosting := fetchMetricBool('WholesalePriceCosting');
  _long30markups := fetchMetricBool('Long30Markups');
  _itempricingprecedence := fetchMetricBool('ItemPricingPrecedence');

-- Return the itemPrice in the currency passed in as pCurrid
  _qty := itemuomtouom(pItemid, pQtyUOM, NULL, pQty);

-- If no as of passed, use current date
  _asof := COALESCE(pAsOf, CURRENT_DATE);

--  Cache Item, Customer and Shipto
  SELECT item.*, itemCost(itemsite_id) AS invcost INTO _item
  FROM item LEFT OUTER JOIN itemsite ON (itemsite_item_id=item_id AND itemsite_warehous_id=pSiteid)
  WHERE (item_id=pItemid);

  SELECT * INTO _cust
  FROM custinfo JOIN custtype ON (custtype_id=cust_custtype_id)
  WHERE (cust_id=pCustid);

  SELECT * INTO _shipto
  FROM shiptoinfo
  WHERE (shipto_id=pShiptoid);

-- Get a value here so we do not have to call the function several times
  SELECT itemuomtouomratio(pItemid, pPriceUOM, _item.item_price_uom_id) AS ratio
    INTO _iteminvpricerat;

-- First get a sales price if any so we when we find other prices
-- we can determine if we want that price or this price.
--  Check for a Sale Price
  SELECT INTO _sale
    currToCurr(ipshead_curr_id, pCurrid, ipsprice_price, pEffective) AS rightprice, ipsitem_type AS righttype
  FROM (
  SELECT ipsitem_ipshead_id AS ipsprice_ipshead_id, ipsitem_type,
         CASE WHEN (ipsitem_type = 'N') THEN
               (ipsitem_price * itemuomtouomratio(_item.item_id, pPriceUOM, ipsitem_price_uom_id))
              WHEN (ipsitem_type = 'D') THEN
               noNeg(_item.item_listprice - (_item.item_listprice * ipsitem_discntprcnt) - ipsitem_fixedamtdiscount) * _iteminvpricerat
              WHEN ((ipsitem_type = 'M') AND _long30markups AND _wholesalepricecosting) THEN
               (_item.item_listcost / (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _iteminvpricerat
              WHEN ((ipsitem_type = 'M') AND _long30markups) THEN
               (_item.invcost / (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _iteminvpricerat
              WHEN (ipsitem_type = 'M' AND _wholesalepricecosting) THEN
               (_item.item_listcost + (_item.item_listcost * ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _iteminvpricerat
              WHEN (ipsitem_type = 'M') THEN
               (_item.invcost + (_item.invcost * ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _iteminvpricerat
              ELSE 0.00
         END AS ipsprice_price,
         CASE WHEN (ipsitem_item_id=_item.item_id) THEN itemuomtouom(ipsitem_item_id, ipsitem_qty_uom_id, NULL, ipsitem_qtybreak)
              ELSE ipsitem_qtybreak
         END AS ipsprice_qtybreak,
         (COALESCE(ipsitem_price_uom_id, -1)=COALESCE(pPriceUOM,-1)) AS uommatched,
         CASE WHEN (_itempricingprecedence) THEN (COALESCE(ipsitem_item_id, -1)=_item.item_id)
              ELSE true END AS itemmatched
    FROM ipsiteminfo
   WHERE(ipsitem_item_id=_item.item_id) OR (ipsitem_prodcat_id=_item.item_prodcat_id) ) AS
        ipsprice, ipshead, ipsass, sale
  WHERE ( (ipsprice_ipshead_id=ipshead_id)
    AND   (sale_ipshead_id=ipsprice_ipshead_id)
    AND   (_asof BETWEEN sale_startdate AND sale_enddate)
    AND   (ipsprice_qtybreak <= _qty)
    AND   (ipsass_ipshead_id=ipshead_id)
    AND ( (ipsass_shipto_id=_shipto.shipto_id)
     OR   ((COALESCE(LENGTH(ipsass_shipto_pattern), 0) > 0) AND (_shipto.shipto_num ~ ipsass_shipto_pattern))
     OR   (ipsass_cust_id=_cust.cust_id)
     OR   (ipsass_custtype_id=_cust.cust_custtype_id)
     OR   ((COALESCE(LENGTH(ipsass_custtype_pattern), 0) > 0) AND (_cust.custtype_code ~ ipsass_custtype_pattern)) )
        )
  ORDER BY itemmatched DESC, uommatched DESC, ipsprice_qtybreak DESC, ipsprice_price ASC
  LIMIT 1;

-- Find the best Price Schedule Price
 
  SELECT INTO _ips
    currToCurr(ipshead_curr_id, pCurrid, protoprice, pEffective) AS rightprice, ipsitem_type AS righttype
  
  FROM (
    SELECT *,
           CASE WHEN (COALESCE(ipsass_shipto_id, -1) > 0) THEN 1
             WHEN (COALESCE(LENGTH(ipsass_shipto_pattern), 0) > 0) THEN 2
             WHEN (COALESCE(ipsass_cust_id, -1) > 0) THEN 3
             WHEN (COALESCE(ipsass_custtype_id, -1) > 0) THEN 4
             WHEN (COALESCE(LENGTH(ipsass_custtype_pattern), 0) > 0) THEN 5
             ELSE 99
           END AS assignseq,
           CASE WHEN (ipsitem_type = 'N') THEN
                 (ipsitem_price * itemuomtouomratio(_item.item_id, pPriceUOM, ipsitem_price_uom_id))
                WHEN (ipsitem_type = 'D') THEN
                 noNeg(_item.item_listprice - (_item.item_listprice * ipsitem_discntprcnt) - ipsitem_fixedamtdiscount) * _iteminvpricerat
                WHEN ((ipsitem_type = 'M') AND _long30markups AND _wholesalepricecosting) THEN
                 (_item.item_listcost / (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _iteminvpricerat
                WHEN ((ipsitem_type = 'M') AND _long30markups) THEN
                 (_item.invcost / (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _iteminvpricerat
                WHEN (ipsitem_type = 'M' AND _wholesalepricecosting) THEN
                 (_item.item_listcost + (_item.item_listcost * ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _iteminvpricerat
                WHEN (ipsitem_type = 'M') THEN
                 (_item.invcost + (_item.invcost * ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _iteminvpricerat
                ELSE 0.00
           END AS protoprice,
           CASE WHEN (ipsitem_item_id=_item.item_id) THEN itemuomtouom(ipsitem_item_id, ipsitem_qty_uom_id, NULL, ipsitem_qtybreak)
                ELSE ipsitem_qtybreak
           END AS protoqtybreak,
           (COALESCE(ipsitem_price_uom_id, -1)=COALESCE(pPriceUOM,-1)) AS uommatched,
           CASE WHEN (_itempricingprecedence) THEN (COALESCE(ipsitem_item_id, -1)=_item.item_id)
                ELSE true END AS itemmatched
    FROM ipsass JOIN ipshead ON (ipshead_id=ipsass_ipshead_id)
                JOIN ipsiteminfo ON (ipsitem_ipshead_id=ipshead_id)
    WHERE ((ipsitem_item_id=_item.item_id) OR (ipsitem_prodcat_id=_item.item_prodcat_id))
      AND (_asof BETWEEN ipshead_effective AND ipshead_expires)
      AND ((ipsitem_warehous_id=pSiteid) OR (ipsitem_warehous_id IS NULL))
      AND ( (ipsass_shipto_id=_shipto.shipto_id)
       OR   ((COALESCE(LENGTH(ipsass_shipto_pattern), 0) > 0) AND (_shipto.shipto_num ~ ipsass_shipto_pattern))
       OR   (ipsass_cust_id=_cust.cust_id)
       OR   (ipsass_custtype_id=_cust.cust_custtype_id)
       OR   ((COALESCE(LENGTH(ipsass_custtype_pattern), 0) > 0) AND (_cust.custtype_code ~ ipsass_custtype_pattern))
          )
  ) AS proto
  WHERE (protoqtybreak <= pQty)
  ORDER BY assignseq, itemmatched DESC, protoqtybreak DESC, rightprice
  LIMIT 1;
 
  IF (_ips.rightprice IS NOT NULL) THEN
    IF ((_sale.rightprice IS NOT NULL) AND (_sale.rightprice < _ips.rightprice)) THEN
      RAISE DEBUG 'itemprice, item=%, cust=%, shipto=%, sale price= %', pItemid, pCustid, pShiptoid, _sale.rightprice;
      _row.itemprice_price := _sale.rightprice;
      _row.itemprice_type := _sale.righttype;
      RETURN NEXT _row;
    END IF;
    RAISE DEBUG 'itemprice, item=%, cust=%, shipto=%, schedule price= %', pItemid, pCustid, pShiptoid, _ips.rightprice;
    _row.itemprice_price := _ips.rightprice;
    _row.itemprice_type := _ips.righttype;
    RETURN NEXT _row;
  END IF;

--  If item is exclusive then list list price does not apply
  IF (_item.item_exclusive) THEN
    RAISE DEBUG 'itemprice, item=%, cust=%, shipto=%, item exclusive, price=-9999', pItemid, pCustid, pShiptoid;
    _row.itemprice_price := -9999.0;
    _row.itemprice_type := '';
    RETURN NEXT _row;
  END IF;

--  Check for a list price
  _listprice := noNeg(currToLocal(pCurrid, _item.item_listprice - (_item.item_listprice * COALESCE(_cust.cust_discntprcnt, 0.0)), pEffective)
                      * itemuomtouomratio(pItemid, pPriceUOM, _item.item_price_uom_id));

  RAISE DEBUG 'itemprice, item=%, cust=%, shipto=%, list price= %', pItemid, pCustid, pShiptoid, _listprice;

  _row.itemprice_price := _listprice;
  _row.itemprice_type := 'P';
  RETURN NEXT _row;

  RETURN;

END;
$$;


ALTER FUNCTION public.itemipsprice(pitemid integer, pcustid integer, pshiptoid integer, pqty numeric, pqtyuom integer, ppriceuom integer, pcurrid integer, peffective date, pasof date, psiteid integer) OWNER TO admin;

--
-- TOC entry 1887 (class 1255 OID 146566682)
-- Dependencies: 4536 8
-- Name: itemlocdistqty(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT COALESCE(SUM(itemlocdist_qty), 0) INTO _qty
  FROM itemlocdist
  WHERE ( (itemlocdist_source_type='L')
   AND (itemlocdist_source_id=pLocationid)
   AND (itemlocdist_itemlocdist_id=pParentid) );

  SELECT COALESCE(SUM(itemlocdist_qty), 0) INTO _tempQty
  FROM itemlocdist, itemloc
  WHERE ( (itemlocdist_source_type='I')
   AND (itemlocdist_source_id=itemloc_id)
   AND (itemloc_location_id=pLocationid)
   AND (itemlocdist_itemlocdist_id=pParentid) );

  _qty := (_qty + _tempQty);

  RETURN _qty;

END;
$_$;


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

--
-- TOC entry 1886 (class 1255 OID 146566681)
-- Dependencies: 4536 8
-- Name: itemlocdistqty(text, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  IF (strpos(pTypes, 'L') > 0) THEN
    SELECT COALESCE(SUM(itemlocdist_qty), 0) INTO _tempQty
    FROM itemlocdist
    WHERE ( (itemlocdist_source_type='L')
     AND (itemlocdist_source_id=pLocationid)
     AND (itemlocdist_itemlocdist_id=pParentid) );

    _qty := (_qty + _tempQty);
  END IF;

  IF (strpos(pTypes, 'I') > 0) THEN
    SELECT COALESCE(SUM(itemlocdist_qty), 0) INTO _tempQty
    FROM itemlocdist, itemloc
    WHERE ( (itemlocdist_source_type='I')
     AND (itemlocdist_source_id=itemloc_id)
     AND (itemloc_location_id=pLocationid)
     AND (itemlocdist_itemlocdist_id=pParentid) );

    _qty := (_qty + _tempQty);
  END IF;

  RETURN _qty;

END;
$_$;


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

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

CREATE FUNCTION itemprice(pitemid integer, pcustid integer, pshiptoid integer, pqty numeric, pcurrid integer, peffective 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
  _item RECORD;

BEGIN
  SELECT item_inv_uom_id, item_price_uom_id
    INTO _item
    FROM item
   WHERE(item_id=pItemid);
  IF (FOUND) THEN
    RETURN itemPrice(pItemid, pCustid, pShiptoid, pQty, _item.item_inv_uom_id, _item.item_price_uom_id, pCurrid, pEffective);
  END IF;
  RETURN -9999;
END;
$$;


ALTER FUNCTION public.itemprice(pitemid integer, pcustid integer, pshiptoid integer, pqty numeric, pcurrid integer, peffective date) OWNER TO admin;

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

CREATE FUNCTION itemprice(pitemid integer, pcustid integer, pshiptoid integer, pqty numeric, pqtyuom integer, ppriceuom integer, pcurrid integer, peffective 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

BEGIN
  RETURN itemPrice(pItemid, pCustid, pShiptoid, pQty, pQtyUOM, pPriceUOM, pCurrid, pEffective, CURRENT_DATE);
END;
$$;


ALTER FUNCTION public.itemprice(pitemid integer, pcustid integer, pshiptoid integer, pqty numeric, pqtyuom integer, ppriceuom integer, pcurrid integer, peffective date) OWNER TO admin;

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

CREATE FUNCTION itemprice(pitemid integer, pcustid integer, pshiptoid integer, pqty numeric, pqtyuom integer, ppriceuom integer, pcurrid integer, peffective date, pasof 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

BEGIN
  RETURN itemPrice(pItemid, pCustid, pShiptoid, pQty, pQtyUOM, pPriceUOM, pCurrid, pEffective, pAsOf, NULL);
END;
$$;


ALTER FUNCTION public.itemprice(pitemid integer, pcustid integer, pshiptoid integer, pqty numeric, pqtyuom integer, ppriceuom integer, pcurrid integer, peffective date, pasof date) OWNER TO admin;

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

CREATE FUNCTION itemprice(pitemid integer, pcustid integer, pshiptoid integer, pqty numeric, pqtyuom integer, ppriceuom integer, pcurrid integer, peffective date, pasof date, psiteid 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
  _r RECORD;

BEGIN
  SELECT * FROM itemIpsPrice(pItemid, pCustid, pShiptoid, pQty, pQtyUOM, pPriceUOM,
                             pCurrid, pEffective, pAsOf, pSiteid) INTO _r;
  RETURN _r.itemprice_price;
END;
$$;


ALTER FUNCTION public.itemprice(pitemid integer, pcustid integer, pshiptoid integer, pqty numeric, pqtyuom integer, ppriceuom integer, pcurrid integer, peffective date, pasof date, psiteid integer) OWNER TO admin;

--
-- TOC entry 1892 (class 1255 OID 146566687)
-- Dependencies: 4536 8
-- Name: itemsellinguom(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  RETURN itemUOMByType(pItemid, 'Selling');
END;
$_$;


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

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

CREATE FUNCTION itemsrcprice(pitemsrcid integer, pqty numeric, pcurrid integer, peffective 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
  _price NUMERIC := 0.0;

BEGIN

  SELECT itemsrcPrice(pItemsrcid, -1, FALSE, pQty, pCurrid, pEffective) INTO _price;

  RETURN _price;

END;
$$;


ALTER FUNCTION public.itemsrcprice(pitemsrcid integer, pqty numeric, pcurrid integer, peffective date) OWNER TO admin;

--
-- TOC entry 1894 (class 1255 OID 146566689)
-- Dependencies: 4536 8
-- Name: itemsrcprice(integer, integer, boolean, numeric, integer, date); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION itemsrcprice(pitemsrcid integer, psiteid integer, pdropship boolean, pqty numeric, pcurrid integer, peffective 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
  _price NUMERIC := 0.0;
  _r RECORD;
  _effective DATE;

BEGIN
-- If no pEffective passed, use current date
  _effective := COALESCE(pEffective, CURRENT_DATE);

--  Cache Itemsrc and Item
  SELECT *
  INTO _r
  FROM itemsrc JOIN item ON (item_id=itemsrc_item_id)
  WHERE (itemsrc_id=pItemsrcid);
  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'itemsrc % not found.', pItemsrcid;
  END IF;

--  Determine price
  SELECT currToCurr(itemsrcp_curr_id, pCurrid, price, _effective) INTO _price
  FROM (
    SELECT *,
           CASE itemsrcp_type WHEN ('N') THEN itemsrcp_price
                              WHEN ('D') THEN (_r.item_listcost - (_r.item_listcost * itemsrcp_discntprcnt) - itemsrcp_fixedamtdiscount)
                              ELSE 0.0
           END AS price
    FROM itemsrcp
    WHERE ( (itemsrcp_itemsrc_id=_r.itemsrc_id)
      AND   ((itemsrcp_warehous_id=pSiteid) OR (itemsrcp_warehous_id=-1))
      AND   ((itemsrcp_dropship=pDropship) OR (NOT itemsrcp_dropship))
      AND   (itemsrcp_qtybreak <= pQty) )
    ORDER BY itemsrcp_qtybreak DESC
    LIMIT 1
       ) AS data
  ;

  RETURN _price;

END;
$$;


ALTER FUNCTION public.itemsrcprice(pitemsrcid integer, psiteid integer, pdropship boolean, pqty numeric, pcurrid integer, peffective date) OWNER TO admin;

--
-- TOC entry 1895 (class 1255 OID 146566690)
-- Dependencies: 4536 8
-- Name: itemuombytype(integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION itemuombytype(integer, text) RETURNS text
    LANGUAGE plpgsql STABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pItemid ALIAS FOR $1;
  pUomtype ALIAS FOR $2;
  _uom TEXT;
BEGIN
  SELECT uom_name INTO _uom FROM (
  SELECT uom_name
    FROM item
    JOIN itemuomconv ON (itemuomconv_item_id=item_id)
    JOIN itemuom ON (itemuom_itemuomconv_id=itemuomconv_id)
    JOIN uomtype ON (itemuom_uomtype_id=uomtype_id)
    JOIN uom ON (itemuomconv_to_uom_id=uom_id)
   WHERE((item_id=pItemid)
     AND (uomtype_name=pUomtype)
     AND (item_inv_uom_id != itemuomconv_to_uom_id))
  UNION
  SELECT uom_name
    FROM item
    JOIN itemuomconv ON (itemuomconv_item_id=item_id)
    JOIN itemuom ON (itemuom_itemuomconv_id=itemuomconv_id)
    JOIN uomtype ON (itemuom_uomtype_id=uomtype_id)
    JOIN uom ON (itemuomconv_from_uom_id=uom_id)
   WHERE((item_id=pItemid)
     AND (uomtype_name=pUomtype)
     AND (item_inv_uom_id != itemuomconv_from_uom_id))) data
   LIMIT 1;

  IF (NOT FOUND) THEN
    SELECT uom_name
      INTO _uom
      FROM item
      JOIN uom ON (item_inv_uom_id=uom_id)
     WHERE(item_id=pItemid);
  END IF;

  RETURN _uom;
END;
$_$;


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

--
-- TOC entry 1896 (class 1255 OID 146566691)
-- Dependencies: 4536 8
-- Name: itemuomfractionalbytype(integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION itemuomfractionalbytype(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
  pItemid ALIAS FOR $1;
  pUomtype ALIAS FOR $2;
  _frac BOOLEAN;
BEGIN
  SELECT itemuomconv_fractional
    INTO _frac
    FROM item
    JOIN itemuomconv ON (itemuomconv_item_id=item_id)
    JOIN itemuom ON (itemuom_itemuomconv_id=itemuomconv_id)
    JOIN uomtype ON (itemuom_uomtype_id=uomtype_id)
   WHERE((item_id=pItemid)
     AND (uomtype_name=pUomtype))
   LIMIT 1;

  IF (NOT FOUND) THEN
    SELECT item_fractional
      INTO _frac
      FROM item
      JOIN uom ON (item_inv_uom_id=uom_id)
     WHERE(item_id=pItemid);
  END IF;

  RETURN _frac;
END;
$_$;


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

--
-- TOC entry 1898 (class 1255 OID 146566692)
-- Dependencies: 4536 8
-- Name: itemuomfractionalbyuom(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION itemuomfractionalbyuom(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;
  pUomid ALIAS FOR $2;
  _frac BOOLEAN;
BEGIN
  SELECT itemuomconv_fractional
    INTO _frac
    FROM item
    JOIN itemuomconv ON (itemuomconv_item_id=item_id)
   WHERE((item_id=pItemid)
     AND ((itemuomconv_from_uom_id=item_inv_uom_id AND itemuomconv_to_uom_id=pUomid)
       OR (itemuomconv_to_uom_id=item_inv_uom_id AND itemuomconv_from_uom_id=pUomid)))
   LIMIT 1;

  IF (NOT FOUND) THEN
    SELECT item_fractional
      INTO _frac
      FROM item
      JOIN uom ON (item_inv_uom_id=uom_id)
     WHERE(item_id=pItemid);
  END IF;

  RETURN _frac;
END;
$_$;


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

--
-- TOC entry 1899 (class 1255 OID 146566693)
-- Dependencies: 4536 8
-- Name: itemuomratiobytype(integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION itemuomratiobytype(integer, text) RETURNS numeric
    LANGUAGE plpgsql STABLE
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pItemid ALIAS FOR $1;
  pUomtype ALIAS FOR $2;
  _ratio NUMERIC;
BEGIN
  -- Return the ration as alternate / inventory uom
  SELECT CASE WHEN(itemuomconv_from_uom_id=item_inv_uom_id) THEN itemuomconv_to_value / itemuomconv_from_value
              ELSE itemuomconv_from_value / itemuomconv_to_value
         END
    INTO _ratio
    FROM item
    JOIN itemuomconv ON (itemuomconv_item_id=item_id)
    JOIN itemuom ON (itemuom_itemuomconv_id=itemuomconv_id)
    JOIN uomtype ON (itemuom_uomtype_id=uomtype_id)
   WHERE((item_id=pItemid)
     AND (uomtype_name=pUomtype))
   LIMIT 1;

  IF (NOT FOUND) THEN
    _ratio := 1.0;
  END IF;

  RETURN _ratio;
END;
$_$;


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

--
-- TOC entry 1900 (class 1255 OID 146566694)
-- Dependencies: 4536 8
-- Name: itemuomtouom(integer, integer, integer, numeric); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION itemuomtouom(integer, integer, integer, numeric) 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 itemuomtouom($1, $2, $3, $4, 'qty');
END;
$_$;


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

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

CREATE FUNCTION itemuomtouom(pitemid integer, puomidfrom integer, puomidto integer, pqtyfrom numeric, plocale text) RETURNS numeric
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _uomidFrom INTEGER;
  _uomidTo   INTEGER;
  _uomidInv  INTEGER;
  _valueFrom NUMERIC := 0.0;
  _valueTo   NUMERIC := 0.0;
  _value     NUMERIC := 0.0;
  _item      RECORD;
  _conv      RECORD;
  _frac      BOOLEAN := FALSE;
BEGIN

  SELECT item_inv_uom_id, item_fractional
    INTO _item
    FROM item
   WHERE(item_id=pItemid);
  IF(NOT FOUND) THEN
    RAISE EXCEPTION 'No item record was found for item id %', pItemid;
  END IF;

  _uomidFrom := COALESCE(pUomidFrom, _item.item_inv_uom_id);
  _uomidTo   := COALESCE(pUomidTo,   _item.item_inv_uom_id);
  _uomidInv  := _item.item_inv_uom_id;

  -- Should we round the qty here or not?
  IF(_uomidFrom = _uomidTo) THEN
    -- Both from/to are the same. If it is the item inv uom
    -- then use the item fractional value otherwise assume
    -- it is fractional for now so the user gets the same value back.
    IF(_uomidFrom = _item.item_inv_uom_id) THEN
      _frac := _item.item_fractional;
    ELSE
      _frac := true;
    END IF;
    RETURN roundLocale(_frac, pQtyFrom, pLocale);
  END IF;

  -- Try a direct conversion
  SELECT itemuomconv_from_uom_id, itemuomconv_from_value,
         itemuomconv_to_uom_id, itemuomconv_to_value,
         itemuomconv_fractional
    INTO _conv
    FROM itemuomconv
   WHERE(((itemuomconv_from_uom_id=_uomidFrom AND itemuomconv_to_uom_id=_uomidTo)
       OR (itemuomconv_from_uom_id=_uomidTo AND itemuomconv_to_uom_id=_uomidFrom))
     AND (itemuomconv_item_id=pItemid));
  IF(FOUND) THEN
    IF(_conv.itemuomconv_from_uom_id=_uomidFrom) THEN
      _valueFrom := _conv.itemuomconv_from_value;
      _valueTo := _conv.itemuomconv_to_value;
    ELSE
      _valueFrom := _conv.itemuomconv_to_value;
      _valueTo := _conv.itemuomconv_from_value;
    END IF;

    -- If we are converting to the item inv uom use the item fractional value
    -- otherwise use the conversion fractional value.
    if(_uomidTo = _uomidInv) THEN
      _frac := _item.item_fractional;
    ELSE
      _frac := _conv.itemuomconv_fractional;
    END IF;
    _value := roundLocale(_frac, ((_valueTo/_valueFrom) * pQtyFrom), pLocale);
  ELSE
    -- Try to convert the from uom to the inventory uom
    SELECT itemuomconv_from_uom_id, itemuomconv_from_value,
           itemuomconv_to_uom_id, itemuomconv_to_value,
           itemuomconv_fractional
      INTO _conv
      FROM itemuomconv
     WHERE(((itemuomconv_from_uom_id=_uomidFrom AND itemuomconv_to_uom_id=_uomidInv)
         OR (itemuomconv_from_uom_id=_uomidInv AND itemuomconv_to_uom_id=_uomidFrom))
       AND (itemuomconv_item_id=pItemid));
    IF(NOT FOUND) THEN
      RAISE EXCEPTION 'A conversion for item_id % from uom_id % to inv_uom_id % was not found.', pItemid, _uomidFrom, _uomidInv;
    END IF;
    IF(_conv.itemuomconv_from_uom_id=_uomidInv) THEN
      _valueFrom := _conv.itemuomconv_from_value;
      _valueTo := _conv.itemuomconv_to_value;
    ELSE
      _valueFrom := _conv.itemuomconv_to_value;
      _valueTo := _conv.itemuomconv_from_value;
    END IF;
    _value := (_valueTo / _valueFrom);
    IF (_conv.itemuomconv_fractional OR _item.item_fractional) THEN
      _frac := TRUE;
    END IF;
    -- Try to convert the to uom to the inventory uom
    SELECT itemuomconv_from_uom_id, itemuomconv_from_value,
           itemuomconv_to_uom_id, itemuomconv_to_value,
           itemuomconv_fractional
      INTO _conv
      FROM itemuomconv
     WHERE(((itemuomconv_from_uom_id=_uomidInv AND itemuomconv_to_uom_id=_uomidTo)
         OR (itemuomconv_from_uom_id=_uomidTo AND itemuomconv_to_uom_id=_uomidInv))
       AND (itemuomconv_item_id=pItemid));
    IF(NOT FOUND) THEN
      RAISE EXCEPTION 'A conversion for item_id % from uom_id % to inv_uom_id % was not found.', pItemid, _uomidTo, _uomidInv;
    END IF;
    IF(_conv.itemuomconv_from_uom_id=_uomidInv) THEN
      _valueFrom := _conv.itemuomconv_from_value;
      _valueTo := _conv.itemuomconv_to_value;
    ELSE
      _valueFrom := _conv.itemuomconv_to_value;
      _valueTo := _conv.itemuomconv_from_value;
    END IF;
    _value := _value * (_valueTo / _valueFrom);
    IF (_conv.itemuomconv_fractional OR _item.item_fractional) THEN
      _frac := TRUE;
    END IF;
    _value := roundLocale(_frac, (_value * pQtyFrom), pLocale);
  END IF;

  RETURN _value;
END;
$$;


ALTER FUNCTION public.itemuomtouom(pitemid integer, puomidfrom integer, puomidto integer, pqtyfrom numeric, plocale text) OWNER TO admin;

--
-- TOC entry 1902 (class 1255 OID 146566696)
-- Dependencies: 4536 8
-- Name: itemuomtouomratio(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION itemuomtouomratio(pitemid integer, puomidfrom integer, puomidto integer) RETURNS numeric
    LANGUAGE plpgsql STABLE
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  _uomidFrom INTEGER;
  _uomidTo   INTEGER;
  _uomidInv  INTEGER;
  _valueFrom NUMERIC := 0.0;
  _valueTo   NUMERIC := 0.0;
  _value     NUMERIC := 0.0;
  _item      RECORD;
  _conv      RECORD;
BEGIN

  SELECT item_inv_uom_id
    INTO _item
    FROM item
   WHERE(item_id=pItemid);
  IF(NOT FOUND) THEN
    RAISE EXCEPTION 'No item record was found for item id %', pItemid;
  END IF;

  _uomidFrom := COALESCE(pUomidFrom, _item.item_inv_uom_id);
  _uomidTo   := COALESCE(pUomidTo,   _item.item_inv_uom_id);
  _uomidInv  := _item.item_inv_uom_id;

  IF(_uomidFrom = _uomidTo) THEN
    RETURN 1.0;
  END IF;

  -- Try a direct conversion
  SELECT itemuomconv_from_uom_id, itemuomconv_from_value,
         itemuomconv_to_uom_id, itemuomconv_to_value
    INTO _conv
    FROM itemuomconv
   WHERE(((itemuomconv_from_uom_id=_uomidFrom AND itemuomconv_to_uom_id=_uomidTo)
       OR (itemuomconv_from_uom_id=_uomidTo AND itemuomconv_to_uom_id=_uomidFrom))
     AND (itemuomconv_item_id=pItemid));
  IF(FOUND) THEN
    IF(_conv.itemuomconv_from_uom_id=_uomidFrom) THEN
      _valueFrom := _conv.itemuomconv_from_value;
      _valueTo := _conv.itemuomconv_to_value;
    ELSE
      _valueFrom := _conv.itemuomconv_to_value;
      _valueTo := _conv.itemuomconv_from_value;
    END IF;
    _value := (_valueTo / _valueFrom);
  ELSE
    -- Try to convert the from uom to the inventory uom
    SELECT itemuomconv_from_uom_id, itemuomconv_from_value,
           itemuomconv_to_uom_id, itemuomconv_to_value
      INTO _conv
      FROM itemuomconv
     WHERE(((itemuomconv_from_uom_id=_uomidFrom AND itemuomconv_to_uom_id=_uomidInv)
         OR (itemuomconv_from_uom_id=_uomidInv AND itemuomconv_to_uom_id=_uomidFrom))
       AND (itemuomconv_item_id=pItemid));
    IF(NOT FOUND) THEN
      RAISE EXCEPTION 'A conversion for item_id % from uom_id % to inv_uom_id % was not found.', pItemid, _uomidFrom, _uomidInv;
    END IF;
    IF(_conv.itemuomconv_from_uom_id=_uomidInv) THEN
      _valueFrom := _conv.itemuomconv_from_value;
      _valueTo := _conv.itemuomconv_to_value;
    ELSE
      _valueFrom := _conv.itemuomconv_to_value;
      _valueTo := _conv.itemuomconv_from_value;
    END IF;
    _value := (_valueTo / _valueFrom);
    -- Try to convert the to uom to the inventory uom
    SELECT itemuomconv_from_uom_id, itemuomconv_from_value,
           itemuomconv_to_uom_id, itemuomconv_to_value
      INTO _conv
      FROM itemuomconv
     WHERE(((itemuomconv_from_uom_id=_uomidInv AND itemuomconv_to_uom_id=_uomidTo)
         OR (itemuomconv_from_uom_id=_uomidTo AND itemuomconv_to_uom_id=_uomidInv))
       AND (itemuomconv_item_id=pItemid));
    IF(NOT FOUND) THEN
      RAISE EXCEPTION 'A conversion for item_id % from uom_id % to inv_uom_id % was not found.', pItemid, _uomidTo, _uomidInv;
    END IF;
    IF(_conv.itemuomconv_from_uom_id=_uomidInv) THEN
      _valueFrom := _conv.itemuomconv_from_value;
      _valueTo := _conv.itemuomconv_to_value;
    ELSE
      _valueFrom := _conv.itemuomconv_to_value;
      _valueTo := _conv.itemuomconv_from_value;
    END IF;
    _value := _value * (_valueTo / _valueFrom);
  END IF;

  RETURN _value;
END;
$$;


ALTER FUNCTION public.itemuomtouomratio(pitemid integer, puomidfrom integer, puomidto integer) OWNER TO admin;

--
-- TOC entry 1903 (class 1255 OID 146566697)
-- Dependencies: 8
-- Name: last_agg(anyelement, anyelement); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION last_agg(anyelement, anyelement) RETURNS anyelement
    LANGUAGE sql STABLE
    AS $_$
  SELECT $2;
$_$;


ALTER FUNCTION public.last_agg(anyelement, anyelement) OWNER TO admin;

--
-- TOC entry 1904 (class 1255 OID 146566698)
-- Dependencies: 4536 8
-- Name: login(); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  RETURN login(false);

END;
$$;


ALTER FUNCTION public.login() OWNER TO admin;

--
-- TOC entry 1905 (class 1255 OID 146566699)
-- Dependencies: 4536 8
-- Name: login(boolean); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  -- added support for PostgreSQL 9.2.0, Incident 21852
  IF (compareversion('9.2.0') <= 0)
  THEN
    PERFORM pg_try_advisory_lock(datid::integer, pid)
     FROM pg_stat_activity
    WHERE(pid = pg_backend_pid());
  ELSE
    PERFORM pg_try_advisory_lock(datid::integer, procpid)
     FROM pg_stat_activity
    WHERE(procpid = pg_backend_pid());
  END IF;

  -- This is new to version 9.0 and higher and will error on older versions
  IF (compareversion('9.0.0') <= 0) THEN
    SET bytea_output TO escape;
  END IF;

  -- this is temporary until either qt fixes the postgres driver or we find &
  -- fix all of the places in our app that can write strings with backslashes
  SET standard_conforming_strings TO false;

  SELECT usr_id, userCanLogin(usr_username) AS usr_active INTO _p
  FROM usr
  WHERE (usr_username=getEffectiveXtUser());

  IF (NOT FOUND) THEN
    RETURN -1;

  ELSIF (NOT _p.usr_active) THEN
    IF(SELECT metric_value='AdminOnly'
         FROM metric
        WHERE metric_name='AllowedUserLogins') THEN
      RETURN -3;
    END IF;
    RETURN -2;
  END IF;

  IF (_setSearchPath) THEN
    IF EXISTS(SELECT 1
                FROM pg_proc
                JOIN pg_namespace ON (pronamespace=pg_namespace.oid)
               WHERE nspname='public'
                 AND proname='buildsearchpath') THEN
      EXECUTE 'SET SEARCH_PATH TO ' || public.buildSearchPath();
    END IF;
  END IF;

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 1906 (class 1255 OID 146566700)
-- Dependencies: 4536 8
-- Name: logout(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION logout() RETURNS integer
    LANGUAGE plpgsql
    AS $$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
BEGIN
  PERFORM pg_advisory_unlock(datid::integer, procpid)
     FROM pg_stat_activity
    WHERE(procpid = pg_backend_pid());

  RETURN 0;
END;
$$;


ALTER FUNCTION public.logout() OWNER TO admin;

--
-- TOC entry 1907 (class 1255 OID 146566701)
-- Dependencies: 4536 8
-- Name: lowercost(integer, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION lowercost(integer, text) 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
  pItemid	ALIAS FOR $1;
  pCosttype	ALIAS FOR $2;

BEGIN
    RETURN lowerCost(pItemid, pCosttype, TRUE);
END;
$_$;


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

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

CREATE FUNCTION lowercost(integer, text, 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
  pItemid ALIAS FOR $1;
  pCosttype ALIAS FOR $2;
  pActual	ALIAS FOR $3;
  _type CHAR(1);
  _actCost	NUMERIC;
  _actCost1	NUMERIC;
  _actCost2	NUMERIC;
  _stdCost	NUMERIC;
  _stdCost1	NUMERIC;
  _stdCost2	NUMERIC;
  _cost		NUMERIC;
  _cost1	NUMERIC;
  _cost2	NUMERIC;
  _batchsize	NUMERIC;

BEGIN

  SELECT item_type INTO _type
  FROM item
  WHERE (item_id=pItemid);

  _batchsize := COALESCE( (
    SELECT bomhead_batchsize
    FROM bomhead
    WHERE ((bomhead_item_id=pItemId)
     AND  (bomhead_rev_id=getActiveRevId('BOM',pItemId))) LIMIT 1), 1);

  -- find the lowercost in the base currency at the current conversion rate
  IF (_type IN ('M', 'F', 'B', 'T')) THEN

    IF (pActual) THEN
      SELECT SUM( CASE WHEN (bomitemcost_id IS NOT NULL AND bc.costelem_id IS NOT NULL) THEN
                  round(currToBase(bomitemcost_curr_id, bomitemcost_actcost, CURRENT_DATE),6) *
                    itemuomtouom(bomitem_item_id, bomitem_uom_id, NULL, (bomitem_qtyfxd/_batchsize + bomitem_qtyper) * (1 + bomitem_scrap), 'qtyper')
                  ELSE
                  round(currToBase(itemcost_curr_id, itemcost_actcost, CURRENT_DATE),6) *
                    itemuomtouom(bomitem_item_id, bomitem_uom_id, NULL, (bomitem_qtyfxd/_batchsize + bomitem_qtyper) * (1 + bomitem_scrap), 'qtyper')
                  END )
	  INTO _cost
      FROM bomitem(pItemid)
        JOIN item ON (item_id=bomitem_item_id AND item_type <> 'T')
        JOIN itemcost ON (itemcost_item_id=bomitem_item_id)
        JOIN costelem ic ON (ic.costelem_id=itemcost_costelem_id AND ic.costelem_type=pCosttype)
        LEFT OUTER JOIN bomitemcost ON (bomitemcost_bomitem_id=bomitem_id)
        LEFT OUTER JOIN costelem bc ON (bc.costelem_id=bomitemcost_costelem_id AND bc.costelem_type=pCosttype)
      WHERE ( CURRENT_DATE BETWEEN bomitem_effective AND (bomitem_expires - 1) );
    ELSE
      SELECT SUM( CASE WHEN (bomitemcost_id IS NOT NULL AND bc.costelem_id IS NOT NULL) THEN
                  bomitemcost_stdcost *
                    itemuomtouom(bomitem_item_id, bomitem_uom_id, NULL, (bomitem_qtyfxd/_batchsize + bomitem_qtyper) * (1 + bomitem_scrap), 'qtyper')
                  ELSE
                  itemcost_stdcost *
                    itemuomtouom(bomitem_item_id, bomitem_uom_id, NULL, (bomitem_qtyfxd/_batchsize + bomitem_qtyper) * (1 + bomitem_scrap), 'qtyper')
                  END )
	  INTO _cost
      FROM bomitem(pItemid)
        JOIN item ON (item_id=bomitem_item_id AND item_type <> 'T')
        JOIN itemcost ON (itemcost_item_id=bomitem_item_id)
        JOIN costelem ON (costelem_id=itemcost_costelem_id AND costelem_type=pCosttype)
        LEFT OUTER JOIN bomitemcost ON (bomitemcost_bomitem_id=bomitem_id)
        LEFT OUTER JOIN costelem bc ON (bc.costelem_id=bomitemcost_costelem_id AND bc.costelem_type=pCosttype)
      WHERE ( CURRENT_DATE BETWEEN bomitem_effective AND (bomitem_expires - 1) ); 
    END IF;
    
    IF (NOT FOUND) THEN
      _cost := NULL;
    END IF;

  ELSIF (_type IN ('C')) THEN
    SELECT SUM(CASE WHEN (bbomitem_qtyper = 0) THEN 0
                    ELSE currToBase(itemcost_curr_id, itemcost_actcost, CURRENT_DATE) / bbomitem_qtyper * bbomitem_costabsorb
               END),
	   SUM(CASE WHEN (bbomitem_qtyper = 0) THEN 0
                    ELSE itemcost_stdcost / bbomitem_qtyper * bbomitem_costabsorb
               END)
	INTO _actCost1, _stdCost1
    FROM itemcost
         JOIN costelem       ON (itemcost_costelem_id=costelem_id)
         JOIN xtmfg.bbomitem ON (bbomitem_parent_item_id=itemcost_item_id)
    WHERE ( (bbomitem_item_id=pItemid)
     AND (CURRENT_DATE BETWEEN bbomitem_effective AND (bbomitem_expires - 1))
     AND (costelem_type=pCosttype) );

    SELECT SUM(CASE WHEN (t.bbomitem_qtyper = 0) THEN 0
                    ELSE currToBase(itemcost_curr_id, itemcost_actcost, CURRENT_DATE) * s.bbomitem_qtyper / t.bbomitem_qtyper * t.bbomitem_costabsorb
               END),
	   SUM(CASE WHEN (t.bbomitem_qtyper = 0) THEN 0
                    ELSE itemcost_stdcost * s.bbomitem_qtyper / t.bbomitem_qtyper * t.bbomitem_costabsorb
               END)
	INTO _actCost2, _stdCost2
    FROM costelem
         JOIN itemcost            ON (costelem_id=itemcost_costelem_id)
         JOIN xtmfg.bbomitem AS s ON (itemcost_item_id=s.bbomitem_item_id)
         JOIN xtmfg.bbomitem AS t ON (s.bbomitem_parent_item_id=t.bbomitem_parent_item_id)
         JOIN  item               ON (s.bbomitem_item_id=item_id)
    WHERE ( (t.bbomitem_item_id=pItemid)
     AND ( CURRENT_DATE BETWEEN s.bbomitem_effective
                        AND (s.bbomitem_expires - 1) )
     AND ( CURRENT_DATE BETWEEN t.bbomitem_effective
                        AND (t.bbomitem_expires - 1) )
     AND (item_type='Y')
     AND (costelem_type=pCosttype) );

    IF (pActual) THEN
	_cost  = _actCost;
	_cost1 = _actCost1;
	_cost2 = _actCost2;
    ELSE
	_cost  = _stdCost;
	_cost1 = _stdCost1;
	_cost2 = _stdCost2;	-- should this be std or act?
    END IF;

    IF (_cost1 IS NULL AND _cost2 IS NULL) THEN
	_cost = NULL;
    ELSE
        _cost = COALESCE(_cost1, 0) + COALESCE(_cost2, 0);
    END IF;

  ELSE
    RETURN NULL;
  END IF;

  RETURN round(_cost,6);

END;
$_$;


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

--
-- TOC entry 1909 (class 1255 OID 146566703)
-- Dependencies: 4536 8
-- Name: maintainbomworkspace(); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT tablename INTO _test
  FROM pg_tables
  WHERE (tablename='bomwork');
  IF (NOT FOUND) THEN
    CREATE TEMPORARY TABLE bomwork
    ( bomwork_id INTEGER, bomwork_set_id INTEGER, bomwork_parent_id INTEGER,
      bomwork_seqnumber INTEGER, bomwork_parent_seqnumber INTEGER,
      bomwork_item_id INTEGER, bomwork_item_type CHARACTER(1), bomwork_status CHARACTER(1),
      bomwork_qtyper NUMERIC(20, 8), bomwork_scrap NUMERIC(20, 10),
      bomwork_level INTEGER, bomwork_effective DATE, bomwork_expires DATE,
      bomwork_stdunitcost NUMERIC(16, 4), bomwork_actunitcost NUMERIC(16, 4),
      bomwork_createwo BOOLEAN, bomwork_issuemethod CHARACTER(1) );
    CREATE INDEX bomwork_set_id_idx ON bomwork(bomwork_set_id);
  END IF;

  RETURN 1;

END;
$$;


ALTER FUNCTION public.maintainbomworkspace() OWNER TO admin;

--
-- TOC entry 1910 (class 1255 OID 146566704)
-- Dependencies: 4536 8
-- Name: markapcheckasposted(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION markapcheckasposted(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 'markAPCheckAsPosted() is deprecated - use markCheckAsPosted() instead';
  RETURN markCheckAsPosted($1);

END;
$_$;


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

--
-- TOC entry 1911 (class 1255 OID 146566705)
-- Dependencies: 4536 8
-- Name: markapcheckasprinted(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION markapcheckasprinted(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 'markAPCheckAsPrinted() is deprecated - use markCheckAsPrinted()';
  RETURN markCheckAsPrinted($1);
END;
$_$;


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

--
-- TOC entry 1912 (class 1255 OID 146566706)
-- Dependencies: 4536 8
-- Name: markcheckasposted(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

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

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 1913 (class 1255 OID 146566707)
-- Dependencies: 4536 8
-- Name: markcheckasprinted(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

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

  RETURN 1;

END;
$_$;


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

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

CREATE FUNCTION massexpirebomitem(integer, date, 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
  pItemid ALIAS FOR $1;
  pExpireDate ALIAS FOR $2;
  pECN ALIAS FOR $3;

BEGIN

  UPDATE bomitem
  SET bomitem_expires=pExpireDate
  WHERE ( (bomitem_expires >= CURRENT_DATE)
   AND (bomitem_item_id=pItemid)
   AND (bomitem_rev_id=getActiveRevId('BOM',bomitem_parent_item_id)) );

  RETURN TRUE;
END;
$_$;


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

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

CREATE FUNCTION massreplacebomitem(integer, 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
  pNewItemid ALIAS FOR $1;
  pOriginalItemid ALIAS FOR $2;
  pEffectiveDate ALIAS FOR $3;
  pECN ALIAS FOR $4;
  _effectiveDate DATE;
  _result		INTEGER;

BEGIN

  _effectiveDate := COALESCE(pEffectiveDate, CURRENT_DATE);

  IF (BOMContains(pOriginalItemid, pNewItemid) OR
      BOMContains(pNewItemid, pOriginalItemid)) THEN
    RETURN -1;
  END IF;

  INSERT INTO bomitem
  ( bomitem_parent_item_id, bomitem_seqnumber,
    bomitem_item_id, bomitem_qtyfxd, bomitem_qtyper, bomitem_uom_id,
    bomitem_scrap, bomitem_effective, bomitem_expires, bomitem_ecn,
    bomitem_createwo, bomitem_issuemethod, bomitem_subtype,
    bomitem_booitem_seq_id, bomitem_schedatwooper, bomitem_moddate, bomitem_rev_id,
    bomitem_char_id, bomitem_value )
  SELECT bomitem_parent_item_id, bomitem_seqnumber,
         pNewItemid, bomitem_qtyfxd, bomitem_qtyper, bomitem_uom_id,
         bomitem_scrap, _effectiveDate, endOfTime(), pECN,
         bomitem_createwo, bomitem_issuemethod, 'I',
         bomitem_booitem_seq_id, bomitem_schedatwooper, CURRENT_DATE, getActiveRevId('BOM',bomitem_parent_item_id),
         bomitem_char_id, bomitem_value
  FROM bomitem
  WHERE ( (_effectiveDate < bomitem_expires)
   AND (bomitem_item_id=pOriginalItemid)
   AND (bomitem_rev_id=getActiveRevId('BOM',bomitem_parent_item_id)) );

  UPDATE bomitem
  SET bomitem_expires=_effectiveDate
  WHERE ( (_effectiveDate < bomitem_expires)
   AND (bomitem_item_id=pOriginalItemid)
   AND (bomitem_rev_id=getActiveRevid('BOM',bomitem_parent_item_id)) );

  RETURN 1;
END;
$_$;


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

--
-- TOC entry 1916 (class 1255 OID 146566710)
-- Dependencies: 4536 8
-- Name: merge2crmaccts(integer, integer, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION merge2crmaccts(integer, integer, boolean) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pSourceId ALIAS FOR $1;
  pTargetId ALIAS FOR $2;
  _purge    BOOLEAN := COALESCE($3, FALSE);

  _coldesc    RECORD;
  _count      INTEGER := 0;
  _hassubtype BOOLEAN;
  _mrgcol     BOOLEAN;
  _result     INTEGER := 0;
  _sel        RECORD;
  _colname    TEXT;
  _tmpid      INTEGER;

BEGIN
  -- Validate
  IF (pSourceId = pTargetId) THEN
    RAISE NOTICE 'Tried to merge a CRM Account with itself: %.', pSourceId;
    RETURN 0;
  ELSIF (pSourceId IS NULL) THEN
    RAISE EXCEPTION 'Merge source id cannot be null [xtuple: merge, -1]';
  ELSIF NOT(EXISTS(SELECT 1 FROM crmacct WHERE crmacct_id=pSourceId)) THEN
    RAISE EXCEPTION 'Merge source % not found [xtuple: merge, -2, %]',
                    pSourceId, pSourceId;
  ELSIF (pTargetId IS NULL) THEN
    RAISE EXCEPTION 'Merge target id cannot be null [xtuple: merge, -3]';
  ELSIF NOT(EXISTS(SELECT 1 FROM crmacct WHERE crmacct_id=pTargetId)) THEN
    RAISE EXCEPTION 'Merge target % not found [xtuple: merge, -4, %]',
                    pTargetId, pTargetId;
  ELSIF NOT(EXISTS(SELECT 1
                     FROM crmacctsel
                    WHERE (crmacctsel_src_crmacct_id=pSourceId)
                      AND (crmacctsel_dest_crmacct_id=pTargetId))) THEN
    RAISE EXCEPTION 'Source % and target % have not been selected for merging [xtuple: merge, -5, %, %]',
                    pSourceId, pTargetId, pSourceId, pTargetId;
  END IF;

  _result:= changeFkeyPointers('public', 'crmacct', pSourceId, pTargetId,
                               ARRAY[ 'crmacctsel', 'crmacctmrgd' ], _purge)
          + changePseudoFKeyPointers('public', 'alarm', 'alarm_source_id',
                                     pSourceId, 'public', 'crmacct', pTargetId,
                                     'alarm_source', 'CRMA', _purge)
          + changePseudoFKeyPointers('public', 'charass', 'charass_target_id',
                                     pSourceId, 'public', 'crmacct', pTargetId,
                                     'charass_target_type', 'CRMACCT', _purge)
          + changePseudoFKeyPointers('public', 'comment', 'comment_source_id',
                                     pSourceId, 'public', 'crmacct', pTargetId,
                                     'comment_source', 'CRMA', _purge)
          + changePseudoFKeyPointers('public', 'docass', 'docass_source_id',
                                     pSourceId, 'public', 'crmacct', pTargetId,
                                     'docass_source_type', 'CRMA', _purge)
          + changePseudoFKeyPointers('public', 'docass', 'docass_target_id',
                                     pSourceId, 'public', 'crmacct', pTargetId,
                                     'docass_target_type', 'CRMA', _purge)
          + changePseudoFKeyPointers('public', 'imageass', 'imageass_source_id',
                                     pSourceId, 'public', 'crmacct', pTargetId,
                                     'imageass_source', 'CRMA', _purge)
          ;

  -- TODO: find a generic way to handle pseudofkeys in packages - see 9401
  IF (fetchMetricBool('EnableBatchManager') AND packageIsEnabled('xtbatch')) THEN
    _result:= _result
            + changePseudoFKeyPointers('xtbatch', 'emlassc', 'emlassc_assc_id',
                                       pSourceId, 'public', 'crmacct', pTargetId,
                                       'emlassc_type', 'CRMA', _purge);
  END IF;

  -- back up all of the values in the target record that are about to be changed
  FOR _coldesc IN SELECT attname, typname
                    FROM pg_attribute
                    JOIN pg_type      ON (atttypid=pg_type.oid)
                    JOIN pg_class     ON (attrelid=pg_class.oid)
                    JOIN pg_namespace ON (relnamespace=pg_namespace.oid)
                   WHERE (attnum >= 0)
                     AND (relname='crmacct')
                     AND (nspname='public')
                     AND (attname NOT IN ('crmacct_id', 'crmacct_number'))
  LOOP

    -- if we're supposed to merge this column at all
    EXECUTE 'SELECT ' || quote_ident('crmacctsel_mrg_' || _coldesc.attname) || '
               FROM crmacctsel
              WHERE ((crmacctsel_src_crmacct_id='  || pSourceId || ')
                 AND (crmacctsel_dest_crmacct_id=' || pTargetId || '))' INTO _mrgcol;

    IF (_mrgcol) THEN
      _colname := REPLACE(_coldesc.attname, 'crmacctsel_mrg_', '');

      -- optionally back up the old value from the destination
      -- we'll back up the old value from the source further down
      IF (NOT _purge) THEN
        BEGIN
          EXECUTE 'INSERT INTO mrgundo (
                       mrgundo_schema,      mrgundo_table,
                       mrgundo_pkey_col,    mrgundo_pkey_id,
                       mrgundo_col,         mrgundo_value,      mrgundo_type,
                       mrgundo_base_schema, mrgundo_base_table, mrgundo_base_id
                 ) SELECT ''public'',     ''crmacct'',
                          ''crmacct_id'', crmacct_id, '   ||
                          quote_literal(_colname)         || ', ' ||
                          quote_ident(_colname)           || ', ' ||
                          quote_literal(_coldesc.typname) || ',
                          ''public'', ''crmacct'', crmacct_id
                     FROM crmacct
                    WHERE (crmacct_id=' || pTargetId || ');' ;
        EXCEPTION WHEN unique_violation THEN
          RAISE EXCEPTION 'Could not make a backup copy of % when merging % into % [xtuple: merge, -8, %, %, public, crmacct, %]',
                       _colname, pSourceId, pTargetId,
                       _colname, pSourceId, pTargetId;
        END;
      END IF;

      -- TODO: what do we do about users?
      /* update the destination crmacct in one of 3 different ways:
         - crmacct_notes might be concatenated from more than one source record
	 - foreign keys to crm account subtype records (e.g. crmacct_cust_id)
           must not leave orphaned records and must avoid uniqueness violations
         - some fields can simply be updated in place
       */
      IF (_colname = 'crmacct_notes') THEN
        EXECUTE 'UPDATE crmacct dest
                    SET '      || quote_ident(_colname) ||
                      '=dest.' || quote_ident(_colname) ||
                      E' || E''\\n'' || src.' || _colname || '
                  FROM crmacct src
                  JOIN crmacctsel ON (src.crmacct_id=crmacctsel_src_crmacct_id)
                 WHERE ((dest.crmacct_id=crmacctsel_dest_crmacct_id)
                    AND (dest.crmacct_id!=crmacctsel_src_crmacct_id));';

      ELSIF (_colname IN ('crmacct_cust_id', 'crmacct_prospect_id', 
                          'crmacct_vend_id', 'crmacct_taxauth_id',
                          'crmacct_emp_id',  'crmacct_salesrep_id')) THEN
        IF (_colname IN ('crmacct_cust_id', 'crmacct_prospect_id')) THEN
          EXECUTE 'SELECT src.' || quote_ident(_colname) || ' IS NOT NULL
                      AND (dest.crmacct_prospect_id IS NOT NULL OR
                           dest.crmacct_cust_id IS NOT NULL)
                     FROM crmacct src
                     JOIN crmacctsel ON (src.crmacct_id=crmacctsel_src_crmacct_id)
                     JOIN crmacct dest ON (crmacctsel_dest_crmacct_id=dest.crmacct_id)
                    WHERE ((src.crmacct_id='  || pSourceId || ')
                       AND (dest.crmacct_id=' || pTargetId || '))' INTO _hassubtype;
          IF (_hassubtype) THEN
            RAISE EXCEPTION 'Cannot merge two CRM Accounts that both refer to Customers and/or Prospects [xtuple: merge, -6, %, %]',
                            pSourceId, pTargetId;
          END IF;
        ELSE
          EXECUTE 'SELECT src.' || quote_ident(_colname) || ' IS NOT NULL
                      AND dest.'|| quote_ident(_colname) || ' IS NOT NULL
                     FROM crmacct src
                     JOIN crmacctsel ON (src.crmacct_id=crmacctsel_src_crmacct_id)
                     JOIN crmacct dest ON (crmacctsel_dest_crmacct_id=dest.crmacct_id)
                    WHERE ((src.crmacct_id='  || pSourceId || ')
                       AND (dest.crmacct_id=' || pTargetId || '))' INTO _hassubtype;

          IF (_hassubtype) THEN
            RAISE EXCEPTION 'Cannot merge CRM Accounts until the % child records have been merged [xtuple: merge, -7, %, %, %]',
                            _colname, _colname, pSourceId, pTargetId;
          END IF;

        END IF;

        /* clearing the source separately from setting the target avoids
           problems with triggers updating the wrong records */
        EXECUTE 'SELECT ' || quote_ident(_colname) || ' FROM crmacct
                  WHERE crmacct_id=' || pSourceId
        INTO _tmpid;

        -- now we have the data to back up the source
        IF (NOT _purge) THEN
          BEGIN
            EXECUTE 'INSERT INTO mrgundo (
                         mrgundo_schema,      mrgundo_table,
                         mrgundo_pkey_col,    mrgundo_pkey_id,
                         mrgundo_col,         mrgundo_value,      mrgundo_type,
                         mrgundo_base_schema, mrgundo_base_table, mrgundo_base_id
                   ) SELECT ''public'',     ''crmacct'',
                            ''crmacct_id'', crmacct_id, '   ||
                            quote_literal(_colname)         || ', ' ||
                            quote_ident(_colname)           || ', ' ||
                            quote_literal(_coldesc.typname) || ',
                            ''public'', ''crmacct'', '      || pTargetId || '
                       FROM crmacct
                      WHERE (crmacct_id=' || pSourceId || ');' ;
          EXCEPTION WHEN unique_violation THEN
            RAISE EXCEPTION 'Could not make a backup copy of % when merging % into % [xtuple: merge, -8, %, %, public, crmacct, %]',
                         _colname, pSourceId, pTargetId,
                         _colname, pSourceId, pTargetId;
          END;
        END IF;

        EXECUTE 'UPDATE crmacct SET ' || quote_ident(_colname) || '=NULL
              WHERE (crmacct_id=' || pSourceId || ');';

        EXECUTE 'UPDATE crmacct
                    SET ' || quote_ident(_colname) || '=' || quote_literal(_tmpid) || '
              WHERE (crmacct_id=' || pTargetId || ');';

      ELSE
        EXECUTE 'UPDATE crmacct dest
                    SET '      || quote_ident(_colname) || '
                        =src.' || quote_ident(_colname) || '
                  FROM crmacct src
                 WHERE ((dest.crmacct_id=' || pTargetId || ')
                    AND (src.crmacct_id='  || pSourceId || '));';
      END IF;

      GET DIAGNOSTICS _count = ROW_COUNT;
      _result := _result + _count;
    END IF;

  END LOOP;

  IF (_purge) THEN
    DELETE FROM crmacct WHERE crmacct = pSourceId;
  ELSE
    INSERT INTO mrgundo (
           mrgundo_schema,      mrgundo_table,
           mrgundo_pkey_col,    mrgundo_pkey_id,
           mrgundo_col,         mrgundo_value,      mrgundo_type,
           mrgundo_base_schema, mrgundo_base_table, mrgundo_base_id
    ) SELECT 'public',         'crmacct',
             'crmacct_id',     pSourceId,
             'crmacct_active', crmacct_active, 'bool',
             'public',         'crmacct',       pTargetId
        FROM crmacct
       WHERE crmacct_active AND (crmacct_id = pSourceId);
    GET DIAGNOSTICS _count = ROW_COUNT;
    IF (_count > 0) THEN
      _result := _result + _count;
      UPDATE crmacct SET crmacct_active = false WHERE (crmacct_id=pSourceId);
    END IF;

    -- make a special record of the source crm account so we can delete it later
    INSERT INTO mrgundo (
           mrgundo_schema,      mrgundo_table,
           mrgundo_pkey_col,    mrgundo_pkey_id,
           mrgundo_col,         mrgundo_value,      mrgundo_type,
           mrgundo_base_schema, mrgundo_base_table, mrgundo_base_id
     ) VALUES (
           'public',     'crmacct',
           'crmacct_id', pSourceId,
           NULL,         NULL,       NULL,
           'public',     'crmacct', pTargetId);
  END IF;

  DELETE FROM crmacctsel WHERE (crmacctsel_src_crmacct_id=pSourceId);

  RETURN _result;
END;
$_$;


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

--
-- TOC entry 9064 (class 0 OID 0)
-- Dependencies: 1916
-- Name: FUNCTION merge2crmaccts(integer, integer, boolean); Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON FUNCTION merge2crmaccts(integer, integer, boolean) IS 'This function merges two crmacct records as decribed in crmacctsel records. For each field in the crmacctsel record marked TRUE, the data are copied from the crmacct record with crmacct_id=pSourceId to the record with crmacct_id=pTargetId. If the purge argument is TRUE, the source record is deleted. If it is FALSE, then mrgundo records are created so the merge can later be undone.';


--
-- TOC entry 1919 (class 1255 OID 146566712)
-- Dependencies: 4536 8
-- Name: mergecrmaccts(integer, boolean); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION mergecrmaccts(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
  pTargetId     ALIAS FOR $1;
  _purge        BOOLEAN := COALESCE($2, FALSE);

  _retval       INTEGER;
BEGIN

  /* if crmacctsel says the target should not keep its original
     notes, clear them.  notes are special because the merge allows
     concatenating them from multiple sources. this needs to be
     kept in sync with merge2crmaccts' similar check.
   */
  IF NOT (SELECT crmacctsel_mrg_crmacct_notes
            FROM crmacctsel
           WHERE crmacctsel_src_crmacct_id=crmacctsel_dest_crmacct_id
             AND crmacctsel_dest_crmacct_id=pTargetId) THEN
    IF (NOT _purge) THEN
      INSERT INTO mrgundo (
             mrgundo_schema,      mrgundo_table,
             mrgundo_pkey_col,    mrgundo_pkey_id,
             mrgundo_col,         mrgundo_value,      mrgundo_type,
             mrgundo_base_schema, mrgundo_base_table, mrgundo_base_id)
      SELECT 'public', 'crmacct', crmacct_id,
             'public', 'crmacct', 'crmacct_id', crmacct_id,
             'crmacct_notes', crmacct_notes, 'text',
             'public', 'crmacct', crmacct_id
        FROM crmacct
       WHERE (crmacct_id=pTargetId);
    END IF;

    UPDATE crmacct
       SET crmacct_notes = ''
     WHERE (crmacct_id=pTargetId);
  END IF;

  -- merge the data from the various source records
  SELECT SUM(merge2crmaccts(crmacctsel_src_crmacct_id, pTargetId, _purge))
         INTO _retval
    FROM crmacctsel
   WHERE ((crmacctsel_dest_crmacct_id=pTargetId)
      AND (crmacctsel_dest_crmacct_id!=crmacctsel_src_crmacct_id));

  DELETE FROM crmacctsel WHERE crmacctsel_dest_crmacct_id=pTargetId;

  RETURN COALESCE(_retval, 0);

END;
$_$;


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

--
-- TOC entry 9065 (class 0 OID 0)
-- Dependencies: 1919
-- Name: FUNCTION mergecrmaccts(integer, boolean); Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON FUNCTION mergecrmaccts(integer, boolean) IS 'This function uses the crmacctsel table to merge multiple crmacct records together. Only the merges into the specified target account are performed. Most of the work is done by repeated calls to the merge2crmaccts function. If the purge argument is FALSE, data are kept to allow reversing the merge.';


--
-- TOC entry 1920 (class 1255 OID 146566713)
-- Dependencies: 4536 8
-- Name: movebomitemdown(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION movebomitemdown(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
  pBomitemid ALIAS FOR $1;
  _nextBomitem RECORD;

BEGIN

  SELECT nextbomitem.bomitem_seqnumber AS next_seqnumber,
         thisbomitem.bomitem_seqnumber AS this_seqnumber,
         thisbomitem.bomitem_parent_item_id AS parent_item_id,
         thisbomitem.bomitem_rev_id AS rev_id
          INTO _nextBomitem
  FROM bomitem AS nextbomitem, bomitem AS thisbomitem
  WHERE ((nextbomitem.bomitem_seqnumber > thisbomitem.bomitem_seqnumber)
   AND (nextbomitem.bomitem_parent_item_id=thisbomitem.bomitem_parent_item_id)
   AND (nextbomitem.bomitem_rev_id=thisbomitem.bomitem_rev_id)
   AND (thisbomitem.bomitem_id=pBomitemid))
  ORDER BY next_seqnumber
  LIMIT 1;

  IF (FOUND) THEN
--  Swap the seqnumber of the current bomitem and the next bomitem
--  There is the potential for multiple bomitems with the same seqnumber

    UPDATE bomitem
    SET bomitem_seqnumber=0
    WHERE (bomitem_seqnumber=_nextBomitem.next_seqnumber)
      AND (bomitem_parent_item_id=_nextBomitem.parent_item_id)
      AND (bomitem_rev_id=_nextBomitem.rev_id);

    UPDATE bomitem 
    SET bomitem_seqnumber=_nextBomitem.next_seqnumber
    WHERE (bomitem_seqnumber=_nextBomitem.this_seqnumber)
      AND (bomitem_parent_item_id=_nextBomitem.parent_item_id)
      AND (bomitem_rev_id=_nextBomitem.rev_id);

    UPDATE bomitem
    SET bomitem_seqnumber=_nextBomitem.this_seqnumber
    WHERE (bomitem_seqnumber=0)
      AND (bomitem_parent_item_id=_nextBomitem.parent_item_id)
      AND (bomitem_rev_id=_nextBomitem.rev_id);
  END IF;

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 1921 (class 1255 OID 146566714)
-- Dependencies: 4536 8
-- Name: movebomitemup(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION movebomitemup(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
  pBomitemid ALIAS FOR $1;
  _nextBomitem RECORD;

BEGIN

  SELECT nextbomitem.bomitem_seqnumber AS next_seqnumber,
         thisbomitem.bomitem_seqnumber AS this_seqnumber,
         thisbomitem.bomitem_parent_item_id AS parent_item_id,
         thisbomitem.bomitem_rev_id AS rev_id
          INTO _nextBomitem
  FROM bomitem AS nextbomitem, bomitem AS thisbomitem
  WHERE ((nextbomitem.bomitem_seqnumber < thisbomitem.bomitem_seqnumber)
   AND (nextbomitem.bomitem_parent_item_id=thisbomitem.bomitem_parent_item_id)
   AND (nextbomitem.bomitem_rev_id=thisbomitem.bomitem_rev_id)
   AND (thisbomitem.bomitem_id=pBomitemid))
  ORDER BY next_seqnumber DESC
  LIMIT 1;

  IF (FOUND) THEN
--  Swap the seqnumber of the current bomitem and the next bomitem
--  There is the potential for multiple bomitems with the same seqnumber

    UPDATE bomitem
    SET bomitem_seqnumber=0
    WHERE (bomitem_seqnumber=_nextBomitem.next_seqnumber)
      AND (bomitem_parent_item_id=_nextBomitem.parent_item_id)
      AND (bomitem_rev_id=_nextBomitem.rev_id);

    UPDATE bomitem 
    SET bomitem_seqnumber=_nextBomitem.next_seqnumber
    WHERE (bomitem_seqnumber=_nextBomitem.this_seqnumber)
      AND (bomitem_parent_item_id=_nextBomitem.parent_item_id)
      AND (bomitem_rev_id=_nextBomitem.rev_id);

    UPDATE bomitem
    SET bomitem_seqnumber=_nextBomitem.this_seqnumber
    WHERE (bomitem_seqnumber=0)
      AND (bomitem_parent_item_id=_nextBomitem.parent_item_id)
      AND (bomitem_rev_id=_nextBomitem.rev_id);
  END IF;

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 1922 (class 1255 OID 146566715)
-- Dependencies: 4536 8
-- Name: moveccarddown(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION moveccarddown(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
  pCcardid ALIAS FOR $1;
  _nextCcard RECORD;

BEGIN

  SELECT nextCcard.ccard_id, nextCcard.ccard_seq AS next_seqnumber,
         thisCcard.ccard_seq AS this_seqnumber INTO _nextCcard
  FROM Ccard AS nextCcard, Ccard AS thisCcard
  WHERE ((nextCcard.ccard_seq > thisCcard.ccard_seq)
   AND (nextCcard.ccard_cust_id=thisCcard.ccard_cust_id)
   AND (thisCcard.ccard_id=pCcardid))
  ORDER BY next_seqnumber
  LIMIT 1;

  IF (FOUND) THEN
--  Swap the seqnumber of the current Ccard and the next Ccard

    UPDATE Ccard
    SET ccard_seq=_nextCcard.next_seqnumber
    WHERE (ccard_id=pCcardid);

    UPDATE Ccard
    SET ccard_seq=_nextCcard.this_seqnumber
    WHERE (ccard_id=_nextCcard.ccard_id);
  END IF;

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 1923 (class 1255 OID 146566716)
-- Dependencies: 4536 8
-- Name: moveccardup(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION moveccardup(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
  pCcardid ALIAS FOR $1;
  _nextCcard RECORD;

BEGIN

  SELECT nextCcard.ccard_id AS ccard_id, nextCcard.ccard_seq AS next_seqnumber,
         thisCcard.ccard_seq AS this_seqnumber INTO _nextCcard
  FROM ccard AS nextCcard, ccard AS thisCcard
  WHERE ((nextCcard.ccard_seq < thisCcard.ccard_seq)
   AND (nextCcard.ccard_cust_id=thisCcard.ccard_cust_id)
   AND (thisCcard.ccard_id=pCcardid))
  ORDER BY next_seqnumber DESC
  LIMIT 1;

  IF (FOUND) THEN
--  Swap the seqnumber of the current Ccard and the next Ccard

    UPDATE Ccard 
    SET ccard_seq=_nextCcard.next_seqnumber
    WHERE (ccard_id=pCcardid);

    UPDATE Ccard
    SET ccard_seq=_nextCcard.this_seqnumber
    WHERE (ccard_id=_nextCcard.ccard_id);
  END IF;

  RETURN 1;

END;
$_$;


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

--
-- TOC entry 1925 (class 1255 OID 146566717)
-- Dependencies: 4536 8
-- Name: moveflgroupdown(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION moveflgroupdown(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;
  _from RECORD;
  _to RECORD;

BEGIN

  SELECT flgrp_id AS id,
         flgrp_flhead_id AS flhead_id,
         flgrp_flgrp_id AS flgrp_id,
         flgrp_order AS ord INTO _from
    FROM flgrp
   WHERE (flgrp_id=pFlgrpid);
  IF (NOT FOUND) THEN
    RETURN -1;
  END IF;

  SELECT id, type, ord INTO _to
    FROM (SELECT flitem_id AS id, 'I' AS type, flitem_order AS ord
            FROM flitem
           WHERE ((flitem_flgrp_id=_from.flgrp_id)
             AND  (flitem_flhead_id=_from.flhead_id))
           UNION
          SELECT flgrp_id AS id, 'G' AS type, flgrp_order AS ord
            FROM flgrp
           WHERE ((flgrp_flgrp_id=_from.flgrp_id)
             AND  (flgrp_flhead_id=_from.flhead_id))
           UNION
          SELECT flspec_id AS id, 'S' AS type, flspec_order AS ord
            FROM flspec
           WHERE ((flspec_flgrp_id=_from.flgrp_id)
             AND  (flspec_flhead_id=_from.flhead_id)) ) AS data
   WHERE (ord > _from.ord)
   ORDER BY ord
   LIMIT 1;
  IF (FOUND) THEN
    UPDATE flgrp
       SET flgrp_order=_to.ord
     WHERE (flgrp_id=_from.id);

    IF (_to.type='I') THEN
      UPDATE flitem
         SET flitem_order=_from.ord
       WHERE (flitem_id=_to.id);
    ELSE
      IF (_to.type='G') THEN
        UPDATE flgrp
           SET flgrp_order=_from.ord
         WHERE (flgrp_id=_to.id);
      ELSE
        IF (_to.type='S') THEN
          UPDATE flspec
             SET flspec_order=_from.ord
           WHERE (flspec_id=_to.id);
        END IF;
      END IF;
    END IF;
  END IF;

  RETURN 0;

END;
$_$;


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

--
-- TOC entry 1926 (class 1255 OID 146566718)
-- Dependencies: 4536 8
-- Name: moveflgroupup(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION moveflgroupup(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;
  _from RECORD;
  _to RECORD;

BEGIN

  SELECT flgrp_id AS id,
         flgrp_flhead_id AS flhead_id,
         flgrp_flgrp_id AS flgrp_id,
         flgrp_order AS ord INTO _from
    FROM flgrp
   WHERE (flgrp_id=pFlgrpid);
  IF (NOT FOUND) THEN
    RETURN -1;
  END IF;

  SELECT id, type, ord INTO _to
    FROM (SELECT flitem_id AS id, 'I' AS type, flitem_order AS ord
            FROM flitem
           WHERE ((flitem_flgrp_id=_from.flgrp_id)
             AND  (flitem_flhead_id=_from.flhead_id))
           UNION
          SELECT flgrp_id AS id, 'G' AS type, flgrp_order AS ord
            FROM flgrp
           WHERE ((flgrp_flgrp_id=_from.flgrp_id)
             AND  (flgrp_flhead_id=_from.flhead_id))
           UNION
          SELECT flspec_id AS id, 'S' AS type, flspec_order AS ord
            FROM flspec
           WHERE ((flspec_flgrp_id=_from.flgrp_id)
             AND  (flspec_flhead_id=_from.flhead_id)) ) AS data
   WHERE (ord < _from.ord)
   ORDER BY ord DESC
   LIMIT 1;
  IF (FOUND) THEN
    UPDATE flgrp
       SET flgrp_order=_to.ord
     WHERE (flgrp_id=_from.id);

    IF (_to.type='I') THEN
      UPDATE flitem
         SET flitem_order=_from.ord
       WHERE (flitem_id=_to.id);
    ELSE
      IF (_to.type='G') THEN
        UPDATE flgrp
           SET flgrp_order=_from.ord
         WHERE (flgrp_id=_to.id);
      ELSE
        IF (_to.type='S') THEN
          UPDATE flspec
             SET flspec_order=_from.ord
           WHERE (flspec_id=_to.id);
        END IF;
      END IF;
    END IF;
  END IF;

  RETURN 0;

END;
$_$;


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

--
-- TOC entry 1927 (class 1255 OID 146566719)
-- Dependencies: 4536 8
-- Name: moveflitemdown(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION moveflitemdown(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
  pFlitemid ALIAS FOR $1;
  _from RECORD;
  _to RECORD;

BEGIN

  SELECT flitem_id AS id,
         flitem_flhead_id AS flhead_id,
         flitem_flgrp_id AS flgrp_id,
         flitem_order AS ord INTO _from
    FROM flitem
   WHERE (flitem_id=pFlitemid);
  IF (NOT FOUND) THEN
    RETURN -1;
  END IF;

  SELECT id, type, ord INTO _to
    FROM (SELECT flitem_id AS id, 'I' AS type, flitem_order AS ord
            FROM flitem
           WHERE ((flitem_flgrp_id=_from.flgrp_id)
             AND  (flitem_flhead_id=_from.flhead_id))
           UNION
          SELECT flgrp_id AS id, 'G' AS type, flgrp_order AS ord
            FROM flgrp
           WHERE ((flgrp_flgrp_id=_from.flgrp_id)
             AND  (flgrp_flhead_id=_from.flhead_id))
           UNION
          SELECT flspec_id AS id, 'S' AS type, flspec_order AS ord
            FROM flspec
           WHERE ((flspec_flgrp_id=_from.flgrp_id)
             AND  (flspec_flhead_id=_from.flhead_id)) ) AS data
   WHERE (ord > _from.ord)
   ORDER BY ord
   LIMIT 1;
  IF (FOUND) THEN
    UPDATE flitem
       SET flitem_order=_to.ord
     WHERE (flitem_id=_from.id);

    IF (_to.type='I') THEN
      UPDATE flitem
         SET flitem_order=_from.ord
       WHERE (flitem_id=_to.id);
    ELSE
      IF (_to.type='G') THEN
        UPDATE flgrp
           SET flgrp_order=_from.ord
         WHERE (flgrp_id=_to.id);
      ELSE
        IF (_to.type='S') THEN
          UPDATE flspec
             SET flspec_order=_from.ord
           WHERE (flspec_id=_to.id);
        END IF;
      END IF;
    END IF;
  END IF;

  RETURN 0;

END;
$_$;


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

--
-- TOC entry 1928 (class 1255 OID 146566720)
-- Dependencies: 4536 8
-- Name: moveflitemup(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION moveflitemup(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
  pFlitemid ALIAS FOR $1;
  _from RECORD;
  _to RECORD;

BEGIN

  SELECT flitem_id AS id,
         flitem_flhead_id AS flhead_id,
         flitem_flgrp_id AS flgrp_id,
         flitem_order AS ord INTO _from
    FROM flitem
   WHERE (flitem_id=pFlitemid);
  IF (NOT FOUND) THEN
    RETURN -1;
  END IF;

  SELECT id, type, ord INTO _to
    FROM (SELECT flitem_id AS id, 'I' AS type, flitem_order AS ord
            FROM flitem
           WHERE ((flitem_flgrp_id=_from.flgrp_id)
             AND  (flitem_flhead_id=_from.flhead_id))
           UNION
          SELECT flgrp_id AS id, 'G' AS type, flgrp_order AS ord
            FROM flgrp
           WHERE ((flgrp_flgrp_id=_from.flgrp_id)
             AND  (flgrp_flhead_id=_from.flhead_id))
           UNION
          SELECT flspec_id AS id, 'S' AS type, flspec_order AS ord
            FROM flspec
           WHERE ((flspec_flgrp_id=_from.flgrp_id)
             AND  (flspec_flhead_id=_from.flhead_id)) ) AS data
   WHERE (ord < _from.ord)
   ORDER BY ord DESC
   LIMIT 1;
  IF (FOUND) THEN
    UPDATE flitem
       SET flitem_order=_to.ord
     WHERE (flitem_id=_from.id);

    IF (_to.type='I') THEN
      UPDATE flitem
         SET flitem_order=_from.ord
       WHERE (flitem_id=_to.id);
    ELSE
      IF (_to.type='G') THEN
        UPDATE flgrp
           SET flgrp_order=_from.ord
         WHERE (flgrp_id=_to.id);
      ELSE
        IF (_to.type='S') THEN
          UPDATE flspec
             SET flspec_order=_from.ord
           WHERE (flspec_id=_to.id);
        END IF;
      END IF;
    END IF;
  END IF;

  RETURN 0;

END;
$_$;


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

--
-- TOC entry 1930 (class 1255 OID 146566721)
-- Dependencies: 4536 8
-- Name: moveflspecdown(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION moveflspecdown(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
  pFlspecid ALIAS FOR $1;
  _from RECORD;
  _to RECORD;

BEGIN

  SELECT flspec_id AS id,
         flspec_flhead_id AS flhead_id,
         flspec_flgrp_id AS flgrp_id,
         flspec_order AS ord INTO _from
    FROM flspec
   WHERE (flspec_id=pFlspecid);
  IF (NOT FOUND) THEN
    RETURN -1;
  END IF;

  SELECT id, type, ord INTO _to
    FROM (SELECT flitem_id AS id, 'I' AS type, flitem_order AS ord
            FROM flitem
           WHERE ((flitem_flgrp_id=_from.flgrp_id)
             AND  (flitem_flhead_id=_from.flhead_id))
           UNION
          SELECT flgrp_id AS id, 'G' AS type, flgrp_order AS ord
            FROM flgrp
           WHERE ((flgrp_flgrp_id=_from.flgrp_id)
             AND  (flgrp_flhead_id=_from.flhead_id))
           UNION
          SELECT flspec_id AS id, 'S' AS type, flspec_order AS ord
            FROM flspec
           WHERE ((flspec_flgrp_id=_from.flgrp_id)
             AND  (flspec_flhead_id=_from.flhead_id)) ) AS data
   WHERE (ord > _from.ord)
   ORDER BY ord
   LIMIT 1;
  IF (FOUND) THEN
    UPDATE flspec
       SET flspec_order=_to.ord
     WHERE (flspec_id=_from.id);

    IF (_to.type='I') THEN
      UPDATE flitem
         SET flitem_order=_from.ord
       WHERE (flitem_id=_to.id);
    ELSE
      IF (_to.type='G') THEN
        UPDATE flgrp
           SET flgrp_order=_from.ord
         WHERE (flgrp_id=_to.id);
      ELSE
        IF (_to.type='S') THEN
          UPDATE flspec
             SET flspec_order=_from.ord
           WHERE (flspec_id=_to.id);
        END IF;
      END IF;
    END IF;
  END IF;

  RETURN 0;

END;
$_$;


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

--
-- TOC entry 1931 (class 1255 OID 146566722)
-- Dependencies: 4536 8
-- Name: moveflspecup(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION moveflspecup(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
  pFlspecid ALIAS FOR $1;
  _from RECORD;
  _to RECORD;

BEGIN

  SELECT flspec_id AS id,
         flspec_flhead_id AS flhead_id,
         flspec_flgrp_id AS flgrp_id,
         flspec_order AS ord INTO _from
    FROM flspec
   WHERE (flspec_id=pFlspecid);
  IF (NOT FOUND) THEN
    RETURN -1;
  END IF;

  SELECT id, type, ord INTO _to
    FROM (SELECT flitem_id AS id, 'I' AS type, flitem_order AS ord
            FROM flitem
           WHERE ((flitem_flgrp_id=_from.flgrp_id)
             AND  (flitem_flhead_id=_from.flhead_id))
           UNION
          SELECT flgrp_id AS id, 'G' AS type, flgrp_order AS ord
            FROM flgrp
           WHERE ((flgrp_flgrp_id=_from.flgrp_id)
             AND  (flgrp_flhead_id=_from.flhead_id))
           UNION
          SELECT flspec_id AS id, 'S' AS type, flspec_order AS ord
            FROM flspec
           WHERE ((flspec_flgrp_id=_from.flgrp_id)
             AND  (flspec_flhead_id=_from.flhead_id)) ) AS data
   WHERE (ord < _from.ord)
   ORDER BY ord DESC
   LIMIT 1;
  IF (FOUND) THEN
    UPDATE flspec
       SET flspec_order=_to.ord
     WHERE (flspec_id=_from.id);

    IF (_to.type='I') THEN
      UPDATE flitem
         SET flitem_order=_from.ord
       WHERE (flitem_id=_to.id);
    ELSE
      IF (_to.type='G') THEN
        UPDATE flgrp
           SET flgrp_order=_from.ord
         WHERE (flgrp_id=_to.id);
      ELSE
        IF (_to.type='S') THEN
          UPDATE flspec
             SET flspec_order=_from.ord
           WHERE (flspec_id=_to.id);
        END IF;
      END IF;
    END IF;
  END IF;

  RETURN 0;

END;
$_$;


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

--
-- TOC entry 1932 (class 1255 OID 146566723)
-- Dependencies: 4536 8
-- Name: movescript(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION movescript(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
  pscriptid ALIAS FOR $1;
  poldpkgid ALIAS FOR $2;
  pnewpkgid ALIAS FOR $3;

  _deletestr    TEXT;
  _destination  TEXT;
  _insertstr    TEXT;
  _rows         INTEGER;
  _selectstr    TEXT;
  _source       TEXT;
  _record       RECORD;

BEGIN
  IF (poldpkgid = pnewpkgid) THEN
    RETURN 0;
  END IF;

  IF (poldpkgid = -1) THEN
    _source = 'public.script';
  ELSE
    SELECT pkghead_name || '.pkgscript' INTO _source
    FROM pkghead
    WHERE pkghead_id=poldpkgid;

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

  IF (pnewpkgid = -1) THEN
    _destination = 'public.script';
  ELSE
    SELECT pkghead_name || '.pkgscript' INTO _destination
    FROM pkghead
    WHERE pkghead_id=pnewpkgid;

    IF NOT FOUND THEN
      RETURN -2;
    END IF;
  END IF;

  _selectstr := ' SELECT * FROM ' || _source ||
                ' WHERE script_id = ' || pscriptid;
  EXECUTE _selectstr INTO _record;

  _deletestr := 'DELETE FROM ONLY ' || _source || 
                ' WHERE script_id = ' || pscriptid;
  EXECUTE _deletestr;
  GET DIAGNOSTICS _rows = ROW_COUNT;
  RAISE NOTICE '% rows from %', _rows, _deletestr;
  IF (_rows < 1) THEN
    RETURN -3;
  ELSIF (_rows > 1) THEN
    RAISE EXCEPTION 'Tried to delete % scripts with the id % when there should be exactly 1',
                    _rows, pscriptid;
  END IF;

  _insertstr := 'INSERT INTO ' || _destination ||
                ' (script_id, script_name, script_order, script_enabled, ' ||
                '  script_source, script_notes) VALUES ('
                || _record.script_id      || ','
                || quote_literal(_record.script_name)    || ','
                || _record.script_order   || ','
                || _record.script_enabled || ','
                || quote_literal(_record.script_source)  || ','
                || quote_literal(_record.script_notes )  || ');'
                ;
  EXECUTE _insertstr;
  GET DIAGNOSTICS _rows = ROW_COUNT;
  RAISE NOTICE '% rows from %', _rows, _insertstr;
  IF (_rows < 1) THEN
    RETURN -4;
  ELSIF (_rows > 1) THEN
    RAISE EXCEPTION 'Tried to insert % scripts with the id % when there should be exactly 1',
                    _rows, pscriptid;
  END IF;

  RETURN pscriptid;

END;
$_$;


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

--
-- TOC entry 1933 (class 1255 OID 146566724)
-- Dependencies: 4536 8
-- Name: moveuiform(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION moveuiform(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
  puiformid ALIAS FOR $1;
  poldpkgid ALIAS FOR $2;
  pnewpkgid ALIAS FOR $3;

  _deletestr    TEXT;
  _destination  TEXT;
  _insertstr    TEXT;
  _rows         INTEGER;
  _selectstr    TEXT;
  _source       TEXT;
  _record       RECORD;

BEGIN
  IF (poldpkgid = pnewpkgid) THEN
    RETURN 0;
  END IF;

  IF (poldpkgid = -1) THEN
    _source = 'public.uiform';
  ELSE
    SELECT pkghead_name || '.pkguiform' INTO _source
    FROM pkghead
    WHERE pkghead_id=poldpkgid;

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

  IF (pnewpkgid = -1) THEN
    _destination = 'public.uiform';
  ELSE
    SELECT pkghead_name || '.pkguiform' INTO _destination
    FROM pkghead
    WHERE pkghead_id=pnewpkgid;

    IF NOT FOUND THEN
      RETURN -2;
    END IF;
  END IF;

  _selectstr := ' SELECT * FROM ' || _source ||
                ' WHERE uiform_id = ' || puiformid;
  EXECUTE _selectstr INTO _record;

  _deletestr := 'DELETE FROM ONLY ' || _source || 
                ' WHERE uiform_id = ' || puiformid;
  EXECUTE _deletestr;
  GET DIAGNOSTICS _rows = ROW_COUNT;
  RAISE NOTICE '% rows from %', _rows, _deletestr;
  IF (_rows < 1) THEN
    RETURN -3;
  ELSIF (_rows > 1) THEN
    RAISE EXCEPTION 'Tried to delete % uiforms with the id % when there should be exactly 1',
                    _rows, puiformid;
  END IF;

  _insertstr := 'INSERT INTO ' || _destination ||
                ' (uiform_id, uiform_name, uiform_order, uiform_enabled, ' ||
                '  uiform_source, uiform_notes) VALUES ('
                || _record.uiform_id      || ','
                || quote_literal(_record.uiform_name)    || ','
                || _record.uiform_order   || ','
                || _record.uiform_enabled || ','
                || quote_literal(_record.uiform_source)  || ','
                || quote_literal(_record.uiform_notes )  || ');'
                ;
  EXECUTE _insertstr;
  GET DIAGNOSTICS _rows = ROW_COUNT;
  RAISE NOTICE '% rows from %', _rows, _insertstr;
  IF (_rows < 1) THEN
    RETURN -4;
  ELSIF (_rows > 1) THEN
    RAISE EXCEPTION 'Tried to insert % uiforms with the id % when there should be exactly 1',
                    _rows, puiformid;
  END IF;

  RETURN puiformid;

END;
$_$;


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

--
-- TOC entry 1934 (class 1255 OID 146566725)
-- Dependencies: 4536 8
-- Name: moveupdown(integer, text, text, text, text, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION moveupdown(pid integer, pschema text, ptable text, pseqcol text, pjoincol text, pextra text, pdir text) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
DECLARE
  _keyfield TEXT;
  _keysize  INTEGER;
  _qry      TEXT;
  _r        RECORD;
  _rowcnt   INTEGER;
  _schema   TEXT := COALESCE(pSchema, 'public');
BEGIN
  RAISE DEBUG 'moveUpDown(%, %, %, %, %, %, %) entered',
              pId, pSchema, pTable, pSeqCol, pJoinCol, pExtra, pDir;

  IF (UPPER(pDir) NOT IN ('UP', 'DOWN')) THEN
     RAISE EXCEPTION 'Cannot change the order of records; unsure what % means for sequencing [xtuple: moveUpDown, -1, %, %.%]',
                     pDir, pDir, _schema, pTable;
  END IF;

  SELECT attname, ARRAY_UPPER(conkey, 1) INTO _keyfield, _keysize
    FROM pg_attribute
    JOIN pg_constraint ON (attrelid=conrelid AND attnum=conkey[1])
    JOIN pg_class      ON (conrelid=pg_class.oid)
    JOIN pg_namespace  ON (relnamespace=pg_namespace.oid)
   WHERE ((contype='p')
      AND (nspname=_schema)
      AND (relname=pTable));

  RAISE DEBUG 'SELECT attname... returned %, %', _keyfield, _keysize;

  IF (_keysize > 1) THEN
    RAISE EXCEPTION 'Cannot change the order of records because %.% has a composite primary key [xtuple: moveUpDown, -2, %.%]',
                     _schema, pTable,
                     _schema, pTable;
  END IF;

  /* SELECT next._keyfield AS nextid,
            next.pSeqCol   AS nextseq,
            this.pSeqCol   AS thisseq
       FROM _schema.pTable AS next,
            _schema.pTable AS this
      WHERE (this._keyfield=$1)
          AND (next.pSeqCol [> or <] this.pSeqCol)
        [ AND (next.pJoinCol=this.pJoinCol) ]
        [ AND (pExtra) ]
      ORDER BY nextseq [ DESC or ASC ]
      LIMIT 1;
  */

  _qry := 'SELECT next.' || quote_ident(_keyfield) || ' AS nextid,
                  next.' || quote_ident(pSeqCol)   || ' AS nextseq,
                  this.' || quote_ident(pSeqCol)   || ' AS thisseq
             FROM ' || _schema || '.' || quote_ident(pTable) || ' AS next,
                  ' || _schema || '.' || quote_ident(pTable) || ' AS this
            WHERE ((this.' || quote_ident(_keyfield)  || '=$1)
               AND (next.' || quote_ident(pSeqCol)    ||
                    CASE pDir WHEN 'UP' THEN ' < ' ELSE ' > ' END ||
                   'this.' || quote_ident(pSeqCol) || ')' ||
               CASE WHEN pJoinCol IS NULL THEN ''
                    ELSE ' AND (next.' || quote_ident(pJoinCol)  ||
                         '=this.' || quote_ident(pJoinCol) || ')'
               END ||
          '     AND (' || COALESCE(pExtra, 'TRUE') || '))
            ORDER BY nextseq ' ||
               CASE pDir WHEN 'UP' THEN 'DESC' ELSE 'ASC' END ||
          ' LIMIT 1;';
  RAISE DEBUG 'moveUpDown about to use % when running %', pId, _qry;

  EXECUTE _qry INTO _r USING pId;
  GET DIAGNOSTICS _rowcnt = ROW_COUNT;
  RAISE DEBUG 'next id %, next seq %, this id %, this seq %',
               _r.nextid, _r.nextseq, pId, _r.thisseq;

  IF (_rowcnt > 0) THEN
    _qry := 'UPDATE ' || _schema || '.' || quote_ident(pTable)  ||
              ' SET ' || pSeqCol || '=CAST($1 AS INTEGER)
             WHERE (' || quote_ident(_keyfield) || '=$2);';

    EXECUTE _qry USING -1,         _r.nextid;
    EXECUTE _qry USING _r.nextseq, pId;
    EXECUTE _qry USING _r.thisseq, _r.nextid;
    RETURN _r.nextid;
  END IF;

  RETURN pId;
END;
$_$;


ALTER FUNCTION public.moveupdown(pid integer, pschema text, ptable text, pseqcol text, pjoincol text, pextra text, pdir text) OWNER TO admin;

--
-- TOC entry 9066 (class 0 OID 0)
-- Dependencies: 1934
-- Name: FUNCTION moveupdown(pid integer, pschema text, ptable text, pseqcol text, pjoincol text, pextra text, pdir text); Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON FUNCTION moveupdown(pid integer, pschema text, ptable text, pseqcol text, pjoincol text, pextra text, pdir text) IS 'moveUpDown moves a particular record up or down in an ordered list.
      pId argument names the record to move.
      pSchema (uses public if NULL) and pTable name the table holding the list.
      pSeqCol is the column that holds the sequence number.
      pJoinCol is the column that distinguishes one list from another in the same table, or NULL if the table holds only one list.
      pExtra is an extra join clause that may be required, or NULL.
      pDir is either UP, meaning move the pId record closer to the beginning,
                or DOWN.
Returns the id of the record with which pId was swapped,
      or pId if the record was already at the end in the specified direction.';


--
-- TOC entry 1897 (class 1255 OID 146566726)
-- Dependencies: 4536 8
-- Name: nextperiodbyinterval(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION nextperiodbyinterval(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
  pPeriodid ALIAS FOR $1;
  pInterval ALIAS FOR $2;
  _periodid INTEGER;
BEGIN
  SELECT b.period_id INTO _periodid
    FROM period AS a, period AS b
   WHERE ((a.period_id=pPeriodid)
     AND  (b.period_start >= a.period_start))
   ORDER BY b.period_start
   LIMIT 1 OFFSET pInterval;
  IF (NOT FOUND) THEN
    RETURN -1;
  END IF;
  RETURN _periodid;
END;
$_$;


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

--
-- TOC entry 1917 (class 1255 OID 146566727)
-- Dependencies: 4536 8
-- Name: nextprsubnumber(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION nextprsubnumber(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
  pPrNumber ALIAS FOR $1;
  _subNumber INTEGER;

BEGIN

  SELECT MAX(pr_subnumber) INTO _subNumber
  FROM pr
  WHERE (pr_number=pPrNumber);

  IF (_subNumber IS NULL)
    THEN _subNumber := 0;
  END IF;

  RETURN (_subNumber + 1);

END;
$_$;


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

--
-- TOC entry 1918 (class 1255 OID 146566728)
-- Dependencies: 8
-- Name: nextwosubnumber(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION nextwosubnumber(integer) RETURNS integer
    LANGUAGE sql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
SELECT COALESCE((MAX(wo_subnumber) + 1), 1)
FROM wo
WHERE (wo_number=($1));
$_$;


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

--
-- TOC entry 1924 (class 1255 OID 146566729)
-- Dependencies: 4536 8
-- Name: noneg(numeric); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  IF (pValue < 0) THEN
    RETURN 0;
  ELSE
   RETURN pValue;
  END IF;

END;
$_$;


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

--
-- TOC entry 1929 (class 1255 OID 146566730)
-- Dependencies: 4536 8
-- Name: nopos(numeric); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  IF (pValue > 0) THEN
    RETURN 0;
  ELSE
   RETURN pValue;
  END IF;

END;
$_$;


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

--
-- TOC entry 1936 (class 1255 OID 146566731)
-- Dependencies: 4536 8
-- Name: normalizetrialbal(integer, character); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION normalizetrialbal(integer, character) 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
  pTrialbalid ALIAS FOR $1;
  pSide ALIAS FOR $2;
  _value NUMERIC;
  _r RECORD;

BEGIN

  SELECT accnt_type, trialbal_beginning, trialbal_ending INTO _r
  FROM trialbal, accnt
  WHERE ( (trialbal_accnt_id=accnt_id)
   AND (trialbal_id=pTrialbalid) );
  IF (NOT FOUND) THEN
    RETURN -1;
  END IF;

-- If we are looking for the Ending Balance, cache it
  IF (pSide = 'E') THEN
    _value = _r.trialbal_ending;

--  We had better been looking for the Beginning Balance!
  ELSE
    _value = _r.trialbal_beginning;
  END IF;

--  If the accnt_type is Asset or Expense, swap the sense
  IF (_r.accnt_type IN ('A', 'E')) THEN
    _value := (_value * -1);
  END IF;

  RETURN _value;

END;
$_$;


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

--
-- TOC entry 1937 (class 1255 OID 146566732)
-- Dependencies: 4536 8
-- Name: numofdatabaseusers(); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  -- in version 9.2.0 the column "procpid" was changed to just "pid" Incident #21852
  IF (compareversion('9.2.0') <= 0)
  THEN
  SELECT count(*)
    INTO _count
    FROM pg_stat_activity, pg_locks
   WHERE((database=datid)
     AND (classid=datid)
     AND (objsubid=2)
     AND (pg_stat_activity.pid = pg_backend_pid()));
  ELSE
  SELECT count(*)
    INTO _count
    FROM pg_stat_activity, pg_locks
   WHERE((database=datid)
     AND (classid=datid)
     AND (objsubid=2)
     AND (procpid = pg_backend_pid()));
  END IF;

  IF (_count IS NULL) THEN
    _count := 0;
  END IF;

  RETURN _count;

END;
$$;


ALTER FUNCTION public.numofdatabaseusers() OWNER TO admin;

--
-- TOC entry 1938 (class 1255 OID 146566733)
-- Dependencies: 4536 8
-- Name: numofserverusers(); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT COUNT(*) INTO _count
  FROM pg_stat_activity;
  IF (_count IS NULL) THEN
    _count := 0;
  END IF;

  RETURN _count;

END;
$$;


ALTER FUNCTION public.numofserverusers() OWNER TO admin;

--
-- TOC entry 1939 (class 1255 OID 146566734)
-- Dependencies: 4536 8
-- Name: openaccountingperiod(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

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

  IF ( ( SELECT (count(period_id) > 0)
           FROM period
          WHERE ((period_end > (
            SELECT period_end 
            FROM period 
            WHERE (period_id=pPeriodId))
          )
           AND (period_closed)) ) ) THEN
    RETURN -3;
  END IF;
  
--  Make sure the year is open
  IF ( ( SELECT (yearperiod_closed)
         FROM yearperiod
           JOIN period ON (period_yearperiod_id=yearperiod_id)
         WHERE (period_id=pPeriodid) ) ) THEN
    RETURN -4;
  END IF;

--  Reset the period_closed flag
  UPDATE period
  SET period_closed=FALSE
  WHERE (period_id=pPeriodid);

--  Post any unposted G/L Transactions into the new period
  FOR _r IN SELECT DISTINCT gltrans_sequence
            FROM gltrans, period
            WHERE ( (NOT gltrans_posted)
             AND (gltrans_date BETWEEN period_start AND period_end)
             AND (period_id=pPeriodid) ) LOOP
    PERFORM postIntoTrialBalance(_r.gltrans_sequence);
  END LOOP;

  RETURN pPeriodid;

END;
$_$;


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

--
-- TOC entry 1940 (class 1255 OID 146566735)
-- Dependencies: 4536 8
-- Name: openaccountingyearperiod(integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

--  Check to make use that the yearperiod is closed
  IF ( ( SELECT (NOT yearperiod_closed)
         FROM yearperiod
         WHERE (yearperiod_id=pYearPeriodId) ) ) THEN
    RETURN -1;
  END IF;

  IF ( ( SELECT (count(yearperiod_id) > 0)
           FROM yearperiod
          WHERE ((yearperiod_end> (
            SELECT yearperiod_end 
            FROM yearperiod 
            WHERE (yearperiod_id=pYearPeriodId))
          )
           AND (yearperiod_closed)) ) ) THEN
    RETURN -2;
  END IF;

--  Reset the yearperiod_closed flag
  UPDATE yearperiod
  SET yearperiod_closed=FALSE
  WHERE (yearperiod_id=pYearPeriodId);

  RETURN pYearPeriodid;

END;
$_$;


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

--
-- TOC entry 1941 (class 1255 OID 146566736)
-- Dependencies: 4536 8
-- Name: openapitemsvalue(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION openapitemsvalue(pvendid integer, pperiodid 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
  _value NUMERIC;

BEGIN

  SELECT SUM( (apopen_amount - apopen_paid) / apopen_curr_rate *
               CASE WHEN (apopen_doctype IN ('D', 'V')) THEN 1 ELSE -1 END )
               INTO _value
  FROM apopen
  WHERE ( (apopen_open)
    AND   (apopen_vend_id=pVendid)
    AND   (apopen_duedate BETWEEN findPeriodStart(pPeriodid) AND findPeriodEnd(pPeriodid)) );

  RETURN COALESCE(_value, 0.0);

END;
$$;


ALTER FUNCTION public.openapitemsvalue(pvendid integer, pperiodid integer) OWNER TO admin;

--
-- TOC entry 1942 (class 1255 OID 146566737)
-- Dependencies: 4536 8
-- Name: openaritemsvalue(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT SUM( CASE WHEN (aropen_doctype IN ('C', 'R')) THEN ((aropen_amount - aropen_paid) * -1)
                   ELSE (aropen_amount - aropen_paid)
              END )  INTO _value
  FROM aropen
  WHERE ( (aropen_open)
    AND (aropen_cust_id=pCustid)
    AND (aropen_duedate BETWEEN findPeriodStart(pPeriodid) AND findPeriodEnd(pPeriodid)) );

  IF (_value IS NULL) THEN
    _value := 0;
  END IF;

  RETURN _value;

END;
$_$;


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

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

CREATE FUNCTION openrecurringitems(integer, text, timestamp with time zone) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pParentid  ALIAS FOR $1;
  pType      TEXT := UPPER($2);
  pDatetime  TIMESTAMP WITH TIME ZONE := COALESCE($3, CURRENT_TIMESTAMP);

  _count     INTEGER := -1;
  _countstmt TEXT;
  _rt        RECORD;

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

  _countstmt := 'SELECT COUNT(*) FROM [fulltable]'
             || ' WHERE (NOT ([done])'
             || '    AND ([schedcol]>=''$1'')'
             || '    AND ([table]_recurring_[table]_id=''$2''));';
  _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, '[schedcol]', _rt.recurtype_schedcol);

  -- 8.4+: EXECUTE _countstmt INTO _count USING pDatetime, pParentid;
  EXECUTE REPLACE(REPLACE(_countstmt, '$1', pDatetime::TEXT),
                                      '$2', pParentid::TEXT) INTO _count;

  RETURN _count;
END;
$_$;


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

--
-- TOC entry 231 (class 1259 OID 146566739)
-- Dependencies: 6038 6039 8
-- Name: ophead; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE ophead (
    ophead_id integer NOT NULL,
    ophead_name text NOT NULL,
    ophead_crmacct_id integer,
    ophead_owner_username text,
    ophead_opstage_id integer,
    ophead_opsource_id integer,
    ophead_optype_id integer,
    ophead_probability_prcnt integer,
    ophead_amount numeric(20,4),
    ophead_target_date date,
    ophead_actual_date date,
    ophead_notes text,
    ophead_curr_id integer,
    ophead_active boolean DEFAULT true,
    ophead_cntct_id integer,
    ophead_username text,
    ophead_start_date date,
    ophead_assigned_date date,
    ophead_priority_id integer,
    ophead_number text NOT NULL,
    CONSTRAINT ophead_ophead_number_check CHECK ((ophead_number <> ''::text))
);


ALTER TABLE public.ophead OWNER TO admin;

--
-- TOC entry 9067 (class 0 OID 0)
-- Dependencies: 231
-- Name: TABLE ophead; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE ophead IS 'Opportunity header.';


--
-- TOC entry 1944 (class 1255 OID 146566747)
-- Dependencies: 2878 4536 8
-- Name: ophead(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION ophead() RETURNS SETOF ophead
    LANGUAGE plpgsql
    AS $$
-- Copyright (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 ophead%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 ('MaintainAllOpportunities','ViewAllOpportunities','MaintainPersonalOpportunities','ViewPersonalOpportunities')
  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 ophead
    LOOP
      RETURN NEXT _row;
    END LOOP;
  -- Otherwise if have any other grant, must be personal privilege.
  ELSIF (_grant) THEN
    FOR _row IN 
      SELECT * FROM ophead 
      WHERE getEffectiveXtUser() IN (ophead_owner_username, ophead_username)
    LOOP
      RETURN NEXT _row;
    END LOOP;
  END IF;

  RETURN;

END;
$$;


ALTER FUNCTION public.ophead() OWNER TO admin;

--
-- TOC entry 9069 (class 0 OID 0)
-- Dependencies: 1944
-- Name: FUNCTION ophead(); Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- TOC entry 1946 (class 1255 OID 146566749)
-- Dependencies: 4536 8
-- Name: orderedbypo(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT orderedByPo(pItemsiteid, startOfTime(), (CURRENT_DATE + pLookAhead)) INTO _qty;
  RETURN _qty;

END;
$_$;


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

--
-- TOC entry 1945 (class 1255 OID 146566748)
-- Dependencies: 4536 8
-- Name: orderedbypo(integer, date, date); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

  SELECT COALESCE(SUM(noNeg(poitem_qty_ordered - poitem_qty_received) * poitem_invvenduomratio), 0.0) INTO _qty
  FROM poitem
  WHERE ( (poitem_itemsite_id=pItemsiteid)
    AND (poitem_status <> 'C')
    AND (poitem_duedate BETWEEN pStartDate AND pEndDate) );

  RETURN _qty;

END;
$_$;


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

--
-- TOC entry 1947 (class 1255 OID 146566750)
-- Dependencies: 4536 8
-- Name: orderedbywo(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN

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

END;
$_$;


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

--
-- TOC entry 1948 (class 1255 OID 146566751)
-- Dependencies: 4536 8
-- Name: orderedbywo(integer, date, date); Type: FUNCTION; Schema: public; Owner: admin
--

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

BEGIN
  SELECT item_type INTO _itemType
  FROM itemsite, item
  WHERE ( (itemsite_item_id=item_id)
   AND (itemsite_id=pItemsiteid) );
  
  IF (_itemType NOT IN ('C','T')) THEN
    SELECT COALESCE(SUM(noNeg(wo_qtyord - wo_qtyrcv)), 0.0) INTO _qty
    FROM wo
    WHERE ( (wo_status <> 'C')
     AND (wo_itemsite_id=pItemsiteid)
     AND (wo_duedate BETWEEN pStartDate AND pEndDate) );
  ELSIF (_itemType = 'C') THEN
    SELECT COALESCE(SUM((noNeg(wo_qtyord - wo_qtyrcv) * brddist_stdqtyper)), 0.0) INTO _qty
    FROM wo, xtmfg.brddist
    WHERE ( (wo_status <> 'C')
     AND (brddist_wo_id=wo_id)
     AND (brddist_itemsite_id=pItemsiteid)
     AND (wo_duedate BETWEEN pStartDate AND pEndDate) );
  ELSIF (_itemType = 'T' AND fetchMetricBool('Routings')) THEN -- Tooling:  Determine quantity already returned
    SELECT
      -- Qty Required
      COALESCE(SUM(noNeg(womatl_qtyreq)),0)  - 
      -- Qty Returned
     (SELECT COALESCE(SUM(abs(invhist_invqty)),0) 
      FROM wo
        JOIN womatl ON (womatl_wo_id=wo_id)
        JOIN womatlpost ON (womatl_id=womatlpost_womatl_id)
        JOIN invhist ON ((womatlpost_invhist_id=invhist_id)
                     AND (invhist_invqty < 0))   
      LEFT OUTER JOIN xtmfg.wooper ON (womatl_wooper_id=wooper_id)
    WHERE ( NOT (COALESCE(wooper_rncomplete,wo_status = 'C'))
     AND (womatl_itemsite_id=pItemsiteid)
     AND (wo_duedate BETWEEN pStartDate AND pEndDate) )
       )
   INTO _qty
    FROM wo
      JOIN womatl ON (womatl_wo_id=wo_id)
      LEFT OUTER JOIN xtmfg.wooper ON (womatl_wooper_id=wooper_id)
    WHERE ( NOT (COALESCE(wooper_rncomplete,wo_status = 'C'))
     AND (womatl_itemsite_id=pItemsiteid)
     AND (wo_duedate BETWEEN pStartDate AND pEndDate) )
    GROUP BY womatl_qtyreq;   
  ELSIF (_itemType = 'T') THEN -- Tooling:  Determine quantity already returned
    SELECT
      -- Qty Required
      COALESCE(SUM(noNeg(womatl_qtyreq)),0)  - 
      -- Qty Returned
     (SELECT COALESCE(SUM(abs(invhist_invqty)),0) 
      FROM wo
        JOIN womatl ON (womatl_wo_id=wo_id)
        JOIN womatlpost ON (womatl_id=womatlpost_womatl_id)
        JOIN invhist ON ((womatlpost_invhist_id=invhist_id)
                     AND (invhist_invqty < 0))
    WHERE ( NOT (wo_status = 'C')
     AND (womatl_itemsite_id=pItemsiteid)
     AND (wo_duedate BETWEEN pStartDate AND pEndDate) )
       )
   INTO _qty
    FROM wo
      JOIN womatl ON (womatl_wo_id=wo_id)
    WHERE ( NOT (wo_status = 'C')
     AND (womatl_itemsite_id=pItemsiteid)
     AND (wo_duedate BETWEEN pStartDate AND pEndDate) )
    GROUP BY womatl_qtyreq;   
  END IF;

  RETURN COALESCE(_qty,0);

END;
$_$;


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

--
-- TOC entry 1949 (class 1255 OID 146566752)
-- Dependencies: 2690 4536 8
-- Name: orderhead(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION orderhead() RETURNS SETOF ordhead
    LANGUAGE plpgsql
    AS $$
-- Copyright (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 ordhead%ROWTYPE;
  _query TEXT;
BEGIN

  _query := '
  SELECT DISTINCT * FROM (
  SELECT pohead_id		AS orderhead_id,
	 ''PO''			AS orderhead_type,
	 pohead_number		AS orderhead_number,
	 pohead_status		AS orderhead_status,
	 pohead_orderdate	AS orderhead_orderdate,
	 (SELECT count(*)
	   FROM poitem
	   WHERE poitem_pohead_id=pohead_id) AS orderhead_linecount,
	 pohead_vend_id		AS orderhead_from_id,
	 vend_name		AS orderhead_from,
	 NULL			AS orderhead_to_id,
	 ''''			AS orderhead_to,
	 pohead_curr_id		AS orderhead_curr_id,
	 pohead_agent_username	AS orderhead_agent_username,
	 pohead_shipvia		AS orderhead_shipvia
  FROM pohead LEFT OUTER JOIN vendinfo ON (pohead_vend_id=vend_id)
  UNION
  SELECT cohead_id		AS orderhead_id,
	 ''SO''			AS orderhead_type,
	 cohead_number		AS orderhead_number,
	 COALESCE(coitem_status,''C'') AS orderhead_status,
	 cohead_orderdate	AS orderhead_orderdate,
	 (SELECT count(*)
	   FROM coitem
	   WHERE coitem_cohead_id=cohead_id) AS orderhead_linecount,
	 NULL			AS orderhead_from_id,
	 ''''			AS orderhead_from,
	 cohead_cust_id		AS orderhead_to_id,
	 cust_name		AS orderhead_to,
	 cohead_curr_id		AS orderhead_curr_id,
	 ''''			AS orderhead_agent_username,
	 cohead_shipvia		AS orderhead_shipvia
  FROM cohead LEFT OUTER JOIN custinfo ON (cohead_cust_id=cust_id)
              LEFT OUTER JOIN coitem ON ((cohead_id=coitem_cohead_id)
                                     AND (coitem_status=''O''))';

  IF (fetchmetricbool('MultiWhs')) THEN
    _query := _query || '
    UNION
    SELECT tohead_id		AS orderhead_id,
	 ''TO''			AS orderhead_type,
  	 tohead_number		AS orderhead_number,
	 tohead_status		AS orderhead_status,
	 tohead_orderdate	AS orderhead_orderdate,
	 (SELECT count(*)
	   FROM toitem
	   WHERE toitem_tohead_id=tohead_id) AS orderhead_linecount,
	 tohead_src_warehous_id	 AS orderhead_from_id,
	 tohead_srcname		AS orderhead_from,
	 tohead_dest_warehous_id AS orderhead_to_id,
	 tohead_destname	AS orderhead_to,
	 tohead_freight_curr_id	AS orderhead_curr_id,
	 tohead_agent_username	AS orderhead_agent_username,
	 tohead_shipvia		AS orderhead_shipvia
    FROM tohead';
  END IF;

  IF (fetchmetricbool('EnableReturnAuth')) THEN
    _query := _query || '
  UNION
    SELECT rahead_id		AS orderhead_id,
	 ''RA''			AS orderhead_type,
	 rahead_number		AS orderhead_number,
	 COALESCE(raitem_status,''C'') AS orderhead_status,
	 rahead_authdate	AS orderhead_orderdate,
	 (SELECT count(*)
	   FROM raitem
	   WHERE raitem_rahead_id=rahead_id) AS orderhead_linecount,
	 rahead_cust_id		AS orderhead_from_id,
	 cust_name		AS orderhead_from,
	 NULL			AS orderhead_to_id,
	 ''''			AS orderhead_to,
	 rahead_curr_id		AS orderhead_curr_id,
	 ''''			AS orderhead_agent_username,
	 ''''			AS orderhead_shipvia
    FROM rahead LEFT OUTER JOIN custinfo ON (rahead_cust_id=cust_id)
              LEFT OUTER JOIN raitem ON ((rahead_id=raitem_rahead_id)
                                     AND (raitem_status=''O''))';
  END IF;

  _query := _query || ') AS data ORDER BY orderhead_type, orderhead_number ;';
  
  FOR _row IN EXECUTE _query
  LOOP
    RETURN NEXT _row;
  END LOOP;

  RETURN;
END;
$$;


ALTER FUNCTION public.orderhead() OWNER TO admin;

--
-- TOC entry 1950 (class 1255 OID 146566753)
-- Dependencies: 2693 4536 8
-- Name: orderitem(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION orderitem() RETURNS SETOF orditem
    LANGUAGE plpgsql
    AS $$
-- Copyright (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 orditem%ROWTYPE;
  _query TEXT;
BEGIN

  _query := '
  SELECT poitem_id		AS orderitem_id,
	 ''PO''			AS orderitem_orderhead_type,
	 poitem_pohead_id	AS orderitem_orderhead_id,
	 poitem_linenumber	AS orderitem_linenumber,
	 poitem_status		AS orderitem_status,
	 poitem_itemsite_id	AS orderitem_itemsite_id,
	 poitem_duedate		AS orderitem_scheddate,
	 poitem_qty_ordered	AS orderitem_qty_ordered,
	 poitem_qty_returned	AS orderitem_qty_shipped,
	 poitem_qty_received	AS orderitem_qty_received,
	 uom_id			AS orderitem_qty_uom_id,
	 poitem_invvenduomratio	AS orderitem_qty_invuomratio,
	 poitem_unitprice	AS orderitem_unitcost,
	 pohead_curr_id         AS orderitem_unitcost_curr_id,
	 poitem_freight		AS orderitem_freight,
	 poitem_freight_received AS orderitem_freight_received,
	 pohead_curr_id         AS orderitem_freight_curr_id

  FROM poitem LEFT OUTER JOIN pohead ON (poitem_pohead_id=pohead_id)
              LEFT OUTER JOIN uom ON (uom_name=poitem_vend_uom)
  UNION
  SELECT coitem_id		AS orderitem_id,
	 ''SO''			AS orderitem_orderhead_type,
	 coitem_cohead_id	AS orderitem_orderhead_id,
	 coitem_linenumber	AS orderitem_linenumber,
	 coitem_status		AS orderitem_status,
	 coitem_itemsite_id	AS orderitem_itemsite_id,
	 coitem_scheddate	AS orderitem_scheddate,
	 coitem_qtyord		AS orderitem_qty_ordered,
	 coitem_qtyshipped	AS orderitem_qty_shipped,
	 coitem_qtyreturned	AS orderitem_qty_received,
	 coitem_qty_uom_id	AS orderitem_qty_uom_id,
	 coitem_qty_invuomratio	AS orderitem_qty_invuomratio,
	 coitem_unitcost	AS orderitem_unitcost,
	 basecurrid()		AS orderitem_unitcost_curr_id,
	 NULL			AS orderitem_freight,
	 NULL			AS orderitem_freight_received,
	 basecurrid()		AS orderitem_freight_curr_id
  FROM coitem';

  IF (fetchmetricbool('MultiWhs')) THEN
    _query := _query || '
    UNION
    SELECT toitem_id		AS orderitem_id,
      ''TO''			AS orderitem_orderhead_type,
      toitem_tohead_id	AS orderitem_orderhead_id,
      toitem_linenumber	AS orderitem_linenumber,
      toitem_status		AS orderitem_status,
      itemsite_id		AS orderitem_itemsite_id,
      toitem_duedate		AS orderitem_scheddate,
      toitem_qty_ordered	AS orderitem_qty_ordered,
      toitem_qty_shipped	AS orderitem_qty_shipped,
      toitem_qty_received	AS orderitem_qty_received,
      uom_id			AS orderitem_qty_uom_id,
      1			AS orderitem_qty_invuomratio,
      toitem_stdcost		AS orderitem_unitcost,
      basecurrid()		AS orderitem_unitcost_curr_id,
      toitem_freight		AS orderitem_freight,
      toitem_freight_received AS orderitem_freight_received,
      toitem_freight_curr_id	AS orderitem_freight_curr_id
    FROM tohead, itemsite, toitem LEFT OUTER JOIN uom ON (uom_name=toitem_uom)
    WHERE ((toitem_tohead_id=tohead_id)
     AND  (tohead_src_warehous_id=itemsite_warehous_id)
     AND  (toitem_item_id=itemsite_item_id)) ';
  END IF;

  IF (fetchmetricbool('EnableReturnAuth')) THEN
    _query := _query || '
    UNION
    SELECT raitem_id		AS orderitem_id,
      ''RA''			AS orderitem_orderhead_type,
      raitem_rahead_id	AS orderitem_orderhead_id,
      raitem_linenumber	AS orderitem_linenumber,
      raitem_status		AS orderitem_status,
      raitem_itemsite_id	AS orderitem_itemsite_id,
      raitem_scheddate	AS orderitem_scheddate,
      raitem_qtyauthorized	AS orderitem_qty_ordered,
      0			AS orderitem_qty_shipped,
      raitem_qtyreceived	AS orderitem_qty_received,
      raitem_qty_uom_id	AS orderitem_qty_uom_id,
      raitem_qty_invuomratio	AS orderitem_qty_invuomratio,
      raitem_unitprice	AS orderitem_unitcost,
      basecurrid()		AS orderitem_unitcost_curr_id,
      NULL			AS orderitem_freight,
      NULL			AS orderitem_freight_received,
      basecurrid()		AS orderitem_freight_curr_id
    FROM raitem';
  END IF;
  
  FOR _row IN EXECUTE _query
  LOOP
    RETURN NEXT _row;
  END LOOP;

  RETURN;
END;
$$;


ALTER FUNCTION public.orderitem() OWNER TO admin;

--
-- TOC entry 1955 (class 1255 OID 146566754)
-- Dependencies: 2687 4536 8
-- Name: orderitemdata(text, integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION orderitemdata(text, integer, integer) RETURNS SETOF orderitemtype
    LANGUAGE plpgsql
    AS $_$
-- Copyright (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;
  pOrderheadid ALIAS FOR $2;
  pOrderitemid ALIAS FOR $3;
  _row orderitemtype%ROWTYPE;
  _set RECORD;

BEGIN

  IF(UPPER(pOrdertype)='PO') THEN
    FOR _set IN 
      SELECT poitem_id              AS orderitem_id,
             'PO'                   AS orderitem_orderhead_type,
             poitem_pohead_id       AS orderitem_orderhead_id,
             poitem_linenumber      AS orderitem_linenumber,
             poitem_status          AS orderitem_status,
             poitem_itemsite_id     AS orderitem_itemsite_id,
             poitem_duedate         AS orderitem_scheddate,
             poitem_qty_ordered     AS orderitem_qty_ordered,
             poitem_qty_returned    AS orderitem_qty_shipped,
             poitem_qty_received    AS orderitem_qty_received,
             uom_id                 AS orderitem_qty_uom_id,
             poitem_invvenduomratio AS orderitem_qty_invuomratio,
             poitem_unitprice       AS orderitem_unitcost,
             (SELECT pohead_curr_id FROM pohead WHERE pohead_id=poitem_pohead_id)
                                    AS orderitem_unitcost_curr_id,
             poitem_freight         AS orderitem_freight,
             poitem_freight_received AS orderitem_freight_received,
             (SELECT pohead_curr_id FROM pohead WHERE pohead_id=poitem_pohead_id)
                                    AS orderitem_freight_curr_id
        FROM poitem LEFT OUTER JOIN uom ON (uom_name=poitem_vend_uom)
       WHERE(((pOrderheadid IS NULL) OR (poitem_pohead_id=pOrderheadid))
         AND ((pOrderitemid IS NULL) OR (poitem_id=pOrderitemid))) LOOP
  
      _row.orderitem_id := _set.orderitem_id;
      _row.orderitem_orderhead_type := _set.orderitem_orderhead_type;
      _row.orderitem_orderhead_id := _set.orderitem_orderhead_id;
      _row.orderitem_linenumber := _set.orderitem_linenumber;
      _row.orderitem_status := _set.orderitem_status;
      _row.orderitem_itemsite_id := _set.orderitem_itemsite_id;
      _row.orderitem_scheddate := _set.orderitem_scheddate;
      _row.orderitem_qty_ordered := _set.orderitem_qty_ordered;
      _row.orderitem_qty_shipped := _set.orderitem_qty_shipped;
      _row.orderitem_qty_received := _set.orderitem_qty_received;
      _row.orderitem_qty_uom_id := _set.orderitem_qty_uom_id;
      _row.orderitem_qty_invuomratio := _set.orderitem_qty_invuomratio;
      _row.orderitem_unitcost := _set.orderitem_unitcost;
      _row.orderitem_unitcost_curr_id := _set.orderitem_unitcost_curr_id;
      _row.orderitem_freight := _set.orderitem_freight;
      _row.orderitem_freight_received := _set.orderitem_freight_received;
      _row.orderitem_freight_curr_id := _set.orderitem_freight_curr_id;

      RETURN NEXT _row;
    END LOOP;
  ELSEIF(UPPER(pOrdertype)='SO') THEN
    FOR _set IN 
      SELECT coitem_id              AS orderitem_id,
             'SO'                   AS orderitem_orderhead_type,
             coitem_cohead_id       AS orderitem_orderhead_id,
             coitem_linenumber      AS orderitem_linenumber,
             coitem_status          AS orderitem_status,
             coitem_itemsite_id     AS orderitem_itemsite_id,
             coitem_scheddate       AS orderitem_scheddate,
             coitem_qtyord          AS orderitem_qty_ordered,
             coitem_qtyshipped      AS orderitem_qty_shipped,
             coitem_qtyreturned     AS orderitem_qty_received,
             coitem_qty_uom_id      AS orderitem_qty_uom_id,
             coitem_qty_invuomratio AS orderitem_qty_invuomratio,
             coitem_unitcost        AS orderitem_unitcost,
             basecurrid()           AS orderitem_unitcost_curr_id,
             NULL                   AS orderitem_freight,
             NULL                   AS orderitem_freight_received,
             basecurrid()           AS orderitem_freight_curr_id
        FROM coitem
       WHERE(((pOrderheadid IS NULL) OR (coitem_cohead_id=pOrderheadid))
         AND ((pOrderitemid IS NULL) OR (coitem_id=pOrderitemid))) LOOP
  
      _row.orderitem_id := _set.orderitem_id;
      _row.orderitem_orderhead_type := _set.orderitem_orderhead_type;
      _row.orderitem_orderhead_id := _set.orderitem_orderhead_id;
      _row.orderitem_linenumber := _set.orderitem_linenumber;
      _row.orderitem_status := _set.orderitem_status;
      _row.orderitem_itemsite_id := _set.orderitem_itemsite_id;
      _row.orderitem_scheddate := _set.orderitem_scheddate;
      _row.orderitem_qty_ordered := _set.orderitem_qty_ordered;
      _row.orderitem_qty_shipped := _set.orderitem_qty_shipped;
      _row.orderitem_qty_received := _set.orderitem_qty_received;
      _row.orderitem_qty_uom_id := _set.orderitem_qty_uom_id;
      _row.orderitem_qty_invuomratio := _set.orderitem_qty_invuomratio;
      _row.orderitem_unitcost := _set.orderitem_unitcost;
      _row.orderitem_unitcost_curr_id := _set.orderitem_unitcost_curr_id;
      _row.orderitem_freight := _set.orderitem_freight;
      _row.orderitem_freight_received := _set.orderitem_freight_received;
      _row.orderitem_freight_curr_id := _set.orderitem_freight_curr_id;

      RETURN NEXT _row;
    END LOOP;
  ELSEIF(UPPER(pOrdertype)='RA') THEN
    FOR _set IN 
      SELECT raitem_id              AS orderitem_id,
             'RA'                   AS orderitem_orderhead_type,
             raitem_rahead_id       AS orderitem_orderhead_id,
             raitem_linenumber      AS orderitem_linenumber,
             raitem_status          AS orderitem_status,
             raitem_itemsite_id     AS orderitem_itemsite_id,
             raitem_scheddate       AS orderitem_scheddate,
             raitem_qtyauthorized   AS orderitem_qty_ordered,
             0                      AS orderitem_qty_shipped,
             raitem_qtyreceived     AS orderitem_qty_received,
             raitem_qty_uom_id      AS orderitem_qty_uom_id,
             raitem_qty_invuomratio AS orderitem_qty_invuomratio,
             raitem_unitprice       AS orderitem_unitcost,
             basecurrid()           AS orderitem_unitcost_curr_id,
             NULL                   AS orderitem_freight,
             NULL                   AS orderitem_freight_received,
             basecurrid()           AS orderitem_freight_curr_id
        FROM raitem
       WHERE(((pOrderheadid IS NULL) OR (raitem_rahead_id=pOrderheadid))
         AND ((pOrderitemid IS NULL) OR (raitem_id=pOrderitemid))) LOOP
  
      _row.orderitem_id := _set.orderitem_id;
      _row.orderitem_orderhead_type := _set.orderitem_orderhead_type;
      _row.orderitem_orderhead_id := _set.orderitem_orderhead_id;
      _row.orderitem_linenumber := _set.orderitem_linenumber;
      _row.orderitem_status := _set.orderitem_status;
      _row.orderitem_itemsite_id := _set.orderitem_itemsite_id;
      _row.orderitem_scheddate := _set.orderitem_scheddate;
      _row.orderitem_qty_ordered := _set.orderitem_qty_ordered;
      _row.orderitem_qty_shipped := _set.orderitem_qty_shipped;
      _row.orderitem_qty_received := _set.orderitem_qty_received;
      _row.orderitem_qty_uom_id := _set.orderitem_qty_uom_id;
      _row.orderitem_qty_invuomratio := _set.orderitem_qty_invuomratio;
      _row.orderitem_unitcost := _set.orderitem_unitcost;
      _row.orderitem_unitcost_curr_id := _set.orderitem_unitcost_curr_id;
      _row.orderitem_freight := _set.orderitem_freight;
      _row.orderitem_freight_received := _set.orderitem_freight_received;
      _row.orderitem_freight_curr_id := _set.orderitem_freight_curr_id;

      RETURN NEXT _row;
    END LOOP;
  ELSEIF(UPPER(pOrdertype)='TO') THEN
    FOR _set IN 
      SELECT toitem_id              AS orderitem_id,
             'TO'                   AS orderitem_orderhead_type,
             toitem_tohead_id       AS orderitem_orderhead_id,
             toitem_linenumber      AS orderitem_linenumber,
             toitem_status          AS orderitem_status,
             itemsite_id            AS orderitem_itemsite_id,
             toitem_duedate         AS orderitem_scheddate,
             toitem_qty_ordered     AS orderitem_qty_ordered,
             toitem_qty_shipped     AS orderitem_qty_shipped,
             toitem_qty_received    AS orderitem_qty_received,
             uom_id                 AS orderitem_qty_uom_id,
             1                      AS orderitem_qty_invuomratio,
             toitem_stdcost         AS orderitem_unitcost,
             basecurrid()           AS orderitem_unitcost_curr_id,
             toitem_freight         AS orderitem_freight,
             toitem_freight_received AS orderitem_freight_received,
             toitem_freight_curr_id AS orderitem_freight_curr_id
        FROM tohead, itemsite, toitem LEFT OUTER JOIN uom ON (uom_name=toitem_uom)
       WHERE((toitem_tohead_id=tohead_id)
         AND (tohead_src_warehous_id=itemsite_warehous_id)
         AND (toitem_item_id=itemsite_item_id)
         AND ((pOrderheadid IS NULL) OR (toitem_tohead_id=pOrderheadid))
         AND ((pOrderitemid IS NULL) OR (toitem_id=pOrderitemid))) LOOP
  
      _row.orderitem_id := _set.orderitem_id;
      _row.orderitem_orderhead_type := _set.orderitem_orderhead_type;
      _row.orderitem_orderhead_id := _set.orderitem_orderhead_id;
      _row.orderitem_linenumber := _set.orderitem_linenumber;
      _row.orderitem_status := _set.orderitem_status;
      _row.orderitem_itemsite_id := _set.orderitem_itemsite_id;
      _row.orderitem_scheddate := _set.orderitem_scheddate;
      _row.orderitem_qty_ordered := _set.orderitem_qty_ordered;
      _row.orderitem_qty_shipped := _set.orderitem_qty_shipped;
      _row.orderitem_qty_received := _set.orderitem_qty_received;
      _row.orderitem_qty_uom_id := _set.orderitem_qty_uom_id;
      _row.orderitem_qty_invuomratio := _set.orderitem_qty_invuomratio;
      _row.orderitem_unitcost := _set.orderitem_unitcost;
      _row.orderitem_unitcost_curr_id := _set.orderitem_unitcost_curr_id;
      _row.orderitem_freight := _set.orderitem_freight;
      _row.orderitem_freight_received := _set.orderitem_freight_received;
      _row.orderitem_freight_curr_id := _set.orderitem_freight_curr_id;

      RETURN NEXT _row;
    END LOOP;
  END IF;

  RETURN;
END;
$_$;


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

--
-- TOC entry 1956 (class 1255 OID 146566755)
-- Dependencies: 8
-- Name: packageisenabled(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION packageisenabled(integer) RETURNS boolean
    LANGUAGE sql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT COUNT(*) >= 8
  FROM pg_inherits, pg_class, pg_namespace, pkghead
  WHERE ((inhrelid=pg_class.oid)
     AND (relnamespace=pg_namespace.oid)
     AND  (nspname=lower(pkghead_name))
     AND  (pkghead_id=$1));
$_$;


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

--
-- TOC entry 1957 (class 1255 OID 146566756)
-- Dependencies: 8
-- Name: packageisenabled(text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION packageisenabled(text) RETURNS boolean
    LANGUAGE sql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
  SELECT COUNT(*) >= 8
  FROM pg_inherits, pg_class, pg_namespace
  WHERE ((inhrelid=pg_class.oid)
     AND (relnamespace=pg_namespace.oid)
     AND  (nspname=lower($1)));
$_$;


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

--
-- TOC entry 1958 (class 1255 OID 146566757)
-- Dependencies: 8
-- Name: pgp_key_id(bytea); Type: FUNCTION; Schema: public; Owner: admin
--

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


ALTER FUNCTION public.pgp_key_id(bytea) OWNER TO admin;

--
-- TOC entry 1959 (class 1255 OID 146566758)
-- Dependencies: 8
-- Name: pgp_pub_decrypt(bytea, bytea); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1960 (class 1255 OID 146566759)
-- Dependencies: 8
-- Name: pgp_pub_decrypt(bytea, bytea, text); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1961 (class 1255 OID 146566760)
-- Dependencies: 8
-- Name: pgp_pub_decrypt(bytea, bytea, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1962 (class 1255 OID 146566761)
-- Dependencies: 8
-- Name: pgp_pub_decrypt_bytea(bytea, bytea); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1963 (class 1255 OID 146566762)
-- Dependencies: 8
-- Name: pgp_pub_decrypt_bytea(bytea, bytea, text); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1964 (class 1255 OID 146566763)
-- Dependencies: 8
-- Name: pgp_pub_decrypt_bytea(bytea, bytea, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1965 (class 1255 OID 146566764)
-- Dependencies: 8
-- Name: pgp_pub_encrypt(text, bytea); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION pgp_pub_encrypt(text, bytea) RETURNS bytea
    LANGUAGE c STRICT
    AS '$libdir/pgcrypto', 'pgp_pub_encrypt_text';


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

--
-- TOC entry 1966 (class 1255 OID 146566765)
-- Dependencies: 8
-- Name: pgp_pub_encrypt(text, bytea, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION pgp_pub_encrypt(text, bytea, text) RETURNS bytea
    LANGUAGE c STRICT
    AS '$libdir/pgcrypto', 'pgp_pub_encrypt_text';


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

--
-- TOC entry 1967 (class 1255 OID 146566766)
-- Dependencies: 8
-- Name: pgp_pub_encrypt_bytea(bytea, bytea); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION pgp_pub_encrypt_bytea(bytea, bytea) RETURNS bytea
    LANGUAGE c STRICT
    AS '$libdir/pgcrypto', 'pgp_pub_encrypt_bytea';


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

--
-- TOC entry 1968 (class 1255 OID 146566767)
-- Dependencies: 8
-- Name: pgp_pub_encrypt_bytea(bytea, bytea, text); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1969 (class 1255 OID 146566768)
-- Dependencies: 8
-- Name: pgp_sym_decrypt(bytea, text); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1970 (class 1255 OID 146566769)
-- Dependencies: 8
-- Name: pgp_sym_decrypt(bytea, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1971 (class 1255 OID 146566770)
-- Dependencies: 8
-- Name: pgp_sym_decrypt_bytea(bytea, text); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1972 (class 1255 OID 146566771)
-- Dependencies: 8
-- Name: pgp_sym_decrypt_bytea(bytea, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1973 (class 1255 OID 146566772)
-- Dependencies: 8
-- Name: pgp_sym_encrypt(text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION pgp_sym_encrypt(text, text) RETURNS bytea
    LANGUAGE c STRICT
    AS '$libdir/pgcrypto', 'pgp_sym_encrypt_text';


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

--
-- TOC entry 1974 (class 1255 OID 146566773)
-- Dependencies: 8
-- Name: pgp_sym_encrypt(text, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION pgp_sym_encrypt(text, text, text) RETURNS bytea
    LANGUAGE c STRICT
    AS '$libdir/pgcrypto', 'pgp_sym_encrypt_text';


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

--
-- TOC entry 1975 (class 1255 OID 146566774)
-- Dependencies: 8
-- Name: pgp_sym_encrypt_bytea(bytea, text); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1976 (class 1255 OID 146566775)
-- Dependencies: 8
-- Name: pgp_sym_encrypt_bytea(bytea, text, text); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1977 (class 1255 OID 146566776)
-- Dependencies: 4536 8
-- Name: pkgmaybemodified(name); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION pkgmaybemodified(name) 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
  pschemaname ALIAS FOR $1;
  _returnval  BOOLEAN;
BEGIN
  SELECT pkghead_indev INTO _returnval
  FROM pkghead
  WHERE (pkghead_name=pschemaname);
  IF (NOT FOUND) THEN
    RETURN FALSE;
  END IF;
  RETURN _returnval;
END;
$_$;


ALTER FUNCTION public.pkgmaybemodified(name) OWNER TO admin;

--
-- TOC entry 1978 (class 1255 OID 146566777)
-- Dependencies: 8
-- Name: plpgsql_call_handler(); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
    LANGUAGE c
    AS '$libdir/plpgsql', 'plpgsql_call_handler';


ALTER FUNCTION public.plpgsql_call_handler() OWNER TO admin;

--
-- TOC entry 1979 (class 1255 OID 146566778)
-- Dependencies: 8
-- Name: plpgsql_validator(oid); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION plpgsql_validator(oid) RETURNS void
    LANGUAGE c
    AS '$libdir/plpgsql', 'plpgsql_validator';


ALTER FUNCTION public.plpgsql_validator(oid) OWNER TO admin;

--
-- TOC entry 1935 (class 1255 OID 146566779)
-- Dependencies: 4536 8
-- Name: postapcheck(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postapcheck(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 'postAPCheck() is deprecated - use postCheck() instead';
  RETURN postCheck($1, fetchJournalNumber('AP-CK'));
END;
$_$;


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

--
-- TOC entry 1951 (class 1255 OID 146566780)
-- Dependencies: 4536 8
-- Name: postapcheck(integer, integer); Type: FUNCTION; Schema: public; Owner: admin
--

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


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

--
-- TOC entry 1952 (class 1255 OID 146566781)
-- Dependencies: 4536 8
-- Name: postapchecks(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postapchecks(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 'postAPChecks() is deprecated - use postChecks() instead';
  RETURN postChecks($1);

END;
$_$;


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

--
-- TOC entry 1981 (class 1255 OID 146566782)
-- Dependencies: 4536 8
-- Name: postapcreditmemoapplication(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postapcreditmemoapplication(integer) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pApopenid ALIAS FOR $1;
  _src RECORD;
  _r RECORD;
  _totalAmount NUMERIC := 0.0;
  _exchGain NUMERIC := 0.0;
  _apaccntid INTEGER;

BEGIN

  SELECT apopen_docnumber, (apopen_amount - apopen_paid) AS balance,
--         SUM(currtocurr(apcreditapply_curr_id, apopen_curr_id,
--				 apcreditapply_amount, CURRENT_DATE)) AS toApply,
	 SUM(apcreditapply_amount) AS toApply,
	 apopen_curr_rate INTO _src
  FROM apopen, apcreditapply
  WHERE ( (apcreditapply_source_apopen_id=apopen_id)
   AND (apopen_id=pApopenid) )
  GROUP BY apopen_docnumber, apopen_amount, apopen_paid,
	   apopen_curr_rate;
  IF (NOT FOUND) THEN
    RETURN -1;
  ELSIF (_src.toApply = 0) THEN
    RETURN -2;
  ELSIF (_src.toApply > _src.balance) THEN
    RETURN -3;
--  ELSIF (_src.toApply IS NULL AND _src.junk IS NOT NULL) THEN
--    RETURN -4;		-- missing exchange rate
  ELSIF (_src.toApply IS NULL) THEN
    RETURN -6;		-- amount to apply is NULL for some unknown reason
  END IF;

  SELECT apopen_id, apopen_vend_id, apopen_docnumber, apopen_doctype, apopen_amount,
         apopen_curr_id, apopen_curr_rate, apopen_docdate, apopen_accnt_id INTO _src
  FROM apopen
  WHERE (apopen_id=pApopenid);
  IF (NOT FOUND) THEN
    RETURN -5;
  END IF;

  FOR _r IN SELECT apcreditapply_id, apcreditapply_target_apopen_id,
		   apcreditapply_amount AS apply_amountSource,
                   currToCurr(apcreditapply_curr_id, apopen_curr_id,
                              apcreditapply_amount, CURRENT_DATE) AS apply_amountTarget,
                   apopen_id, apopen_doctype, apopen_docnumber, apopen_curr_id, apopen_curr_rate, apopen_docdate
            FROM apcreditapply, apopen
            WHERE ( (apcreditapply_source_apopen_id=pApopenid)
             AND (apcreditapply_target_apopen_id=apopen_id) ) LOOP

    IF (_r.apply_amountTarget IS NULL) THEN
      RETURN -4;	-- missing exchange rate
    END IF;

    IF (_r.apply_amountTarget > 0) THEN

--  Update the apopen item to post the paid amount
      UPDATE apopen
      SET apopen_paid = (apopen_paid + _r.apply_amountTarget)
      WHERE (apopen_id=_r.apcreditapply_target_apopen_id);

      UPDATE apopen
      SET apopen_open = false,
        apopen_closedate = current_date
      WHERE ( (apopen_id=_r.apcreditapply_target_apopen_id)
        AND (apopen_amount <= apopen_paid) );

--  Cache the running amount posted
      _totalAmount := (_totalAmount + _r.apply_amountSource);

--  Record the application
      INSERT INTO apapply
      ( apapply_vend_id, apapply_amount,
        apapply_source_apopen_id, apapply_source_doctype, apapply_source_docnumber,
        apapply_target_apopen_id, apapply_target_doctype, apapply_target_docnumber,
        apapply_postdate, apapply_journalnumber, apapply_username, apapply_curr_id )
      VALUES
      ( _src.apopen_vend_id, round(_r.apply_amountSource, 2),
        pApopenid, 'C', _src.apopen_docnumber,
        _r.apopen_id, _r.apopen_doctype, _r.apopen_docnumber,
        CURRENT_DATE, 0, getEffectiveXtUser(), _src.apopen_curr_id );

    END IF;

--  Delete the posted apcreditapply record
    DELETE FROM apcreditapply
    WHERE (apcreditapply_id=_r.apcreditapply_id);

  END LOOP;

--  Record the amount posted and mark the source apopen as closed if it is completely posted
  UPDATE apopen
  SET apopen_paid = (apopen_paid + _totalAmount)
  WHERE (apopen_id=pApopenid);

  UPDATE apopen
  SET apopen_open = false,
    apopen_closedate = current_date
  WHERE ( (apopen_id=pApopenid)
    AND (apopen_amount <= apopen_paid) );

  IF (_r.apopen_curr_id = _src.apopen_curr_id) THEN
    IF (_r.apopen_docdate > _src.apopen_docdate) THEN
      _exchGain := (_totalAmount / _r.apopen_curr_rate - _totalAmount / _src.apopen_curr_rate) * -1;
    ELSE
      _exchGain := _totalAmount / _src.apopen_curr_rate - _totalAmount / _r.apopen_curr_rate;
    END IF;
  END IF;

-- do not post gain/loss to alternate prepaid
--  IF (_src.apopen_accnt_id > -1) THEN
--    _apaccntid := _src.apopen_accnt_id;
--  ELSE 
    _apaccntid := findAPAccount(_src.apopen_vend_id);
--  END IF;

  PERFORM insertGLTransaction(fetchJournalNumber('AP-MISC'), 'A/P', 'CM',
                            _src.apopen_docnumber, 'CM Application',
                            _apaccntid,
			    getGainLossAccntId(_apaccntid), -1,
                            _exchGain,
                            CURRENT_DATE);


  RETURN pApopenid;

END;
$_$;


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

--
-- TOC entry 1982 (class 1255 OID 146566783)
-- Dependencies: 4536 8
-- Name: postapopenitems(); Type: FUNCTION; Schema: public; Owner: admin
--

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

  UPDATE apopen
  SET apopen_posted=TRUE
  WHERE (NOT apopen_posted);

  RETURN TRUE;

END;
$$;


ALTER FUNCTION public.postapopenitems() OWNER TO admin;

--
-- TOC entry 1983 (class 1255 OID 146566784)
-- Dependencies: 4536 8
-- Name: postarcreditmemoapplication(integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postarcreditmemoapplication(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
  pAropenid ALIAS FOR $1;
BEGIN
  RETURN postARCreditMemoApplication(pAropenid, CURRENT_DATE);
END;
$_$;


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

--
-- TOC entry 1984 (class 1255 OID 146566785)
-- Dependencies: 4536 8
-- Name: postarcreditmemoapplication(integer, date); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION postarcreditmemoapplication(integer, date) RETURNS integer
    LANGUAGE plpgsql
    AS $_$
-- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple. 
-- See www.xtuple.com/CPAL for the full text of the software license.
DECLARE
  pAropenid ALIAS FOR $1;
  _applyDate DATE := COALESCE($2, CURRENT_DATE);
  _p RECORD;
  _r RECORD;
  _totalAmount NUMERIC := 0;
  _totalTarget NUMERIC := 0;
  _exchGain NUMERIC := 0;
  _result NUMERIC;
  _araccntid INTEGER;

BEGIN

  SELECT aropen_docnumber,
         ROUND(aropen_amount - aropen_paid, 2) AS balance,
         aropen_open, aropen_curr_rate,
         ROUND(SUM(currToCurr(arcreditapply_curr_id, aropen_curr_id,
              COALESCE(arcreditapply_amount, 0), _applyDate)), 2) AS toApply INTO _p
  FROM aropen, arcreditapply
  WHERE ( (arcreditapply_source_aropen_id=aropen_id)
   AND (aropen_id=pAropenid) )
  GROUP BY aropen_docnumber, aropen_amount, aropen_paid, aropen_open, aropen_curr_rate;
  IF (NOT FOUND) THEN
    RETURN -1;
  ELSIF (_p.toApply = 0) THEN
    RETURN -2;
  ELSIF (_p.toApply > _p.balance) THEN
    RETURN -3;
  END IF;

  SELECT aropen_cust_id, aropen_docnumber, aropen_doctype, aropen_amount,
         aropen_curr_id, aropen_docdate, aropen_accnt_id, aropen_cust_id,
         aropen_curr_rate INTO _p
  FROM aropen
  WHERE (aropen_id=pAropenid);
  IF (NOT FOUND) THEN
    RETURN -5;
  END IF;

  FOR _r IN SELECT arcreditapply_id, arcreditapply_target_aropen_id,
                   arcreditapply_amount AS arcreditapply_amountSource,
                   arcreditapply_reftype, arcreditapply_ref_id,
                   currToCurr(arcreditapply_curr_id, aropen_curr_id,
                              arcreditapply_amount, _applyDate) AS arcreditapply_amountTarget,
                   aropen_id, aropen_doctype, aropen_docnumber, aropen_docdate, aropen_curr_rate
            FROM arcreditapply, aropen
            WHERE ( (arcreditapply_source_aropen_id=pAropenid)
             AND (arcreditapply_target_aropen_id=aropen_id) ) LOOP

    IF (_r.arcreditapply_amountTarget IS NULL) THEN
      RETURN -4;
    END IF;

    IF (_r.arcreditapply_amountTarget > 0) THEN

--  Update the aropen item to post the paid amount
      UPDATE aropen
      SET aropen_paid = round(aropen_paid + _r.arcreditapply_amountTarget, 2)
      WHERE (aropen_id=_r.arcreditapply_target_aropen_id);

      UPDATE aropen
      SET aropen_open = (round(aropen_amount, 2) > round(aropen_paid, 2))
      WHERE (aropen_id=_r.arcreditapply_target_aropen_id);

--  Cache the running amount posted
      _totalAmount := (_totalAmount + _r.arcreditapply_amountSource);
      _totalTarget := (_totalTarget + _r.arcreditapply_amountTarget);

--  Record the application
      INSERT INTO arapply
      ( arapply_cust_id,
        arapply_source_aropen_id, arapply_source_doctype, arapply_source_docnumber,
        arapply_target_aropen_id, arapply_target_doctype, arapply_target_docnumber,
        arapply_fundstype, arapply_refnumber,
        arapply_applied, arapply_closed, arapply_postdate, arapply_distdate,
        arapply_journalnumber, arapply_username, arapply_curr_id,
        arapply_reftype, arapply_ref_id )
      VALUES
      ( _p.aropen_cust_id,
        pAropenid, _p.aropen_doctype, _p.aropen_docnumber,
        _r.aropen_id, _r.aropen_doctype, _r.aropen_docnumber,
        '', '',
        round(_r.arcreditapply_amountSource, 2), TRUE, _applyDate, _applyDate,
        0, getEffectiveXtUser(), _p.aropen_curr_id, 
        _r.arcreditapply_reftype, _r.arcreditapply_ref_id );

    END IF;

--  Delete the posted arcreditapply record
    DELETE FROM arcreditapply
    WHERE (arcreditapply_id=_r.arcreditapply_id);

    IF (_r.aropen_docdate > _p.aropen_docdate) THEN
      _exchGain := (_totalTarget / _r.aropen_curr_rate - _totalAmount / _p.aropen_curr_rate) * -1;
    ELSE
      _exchGain := _totalAmount / _p.aropen_curr_rate - _totalTarget / _r.aropen_curr_rate;
    END IF;

    IF (_p.aropen_accnt_id > -1) THEN
      _araccntid := _p.aropen_accnt_id;
    ELSE 
      _araccntid := findARAccount(_p.aropen_cust_id);
    END IF;
    
    IF (_exchGain <> 0) THEN
        PERFORM insertGLTransaction(fetchJournalNumber('AR-MISC'), 'A/R',
                                    'CR', _p.aropen_docnumber, 'CM Application',
                                    _araccntid, getGainLossAccntId(_araccntid),
                                    -1, _exchGain * -1, _applyDate);
    END IF;

  END LOOP;

-- TODO: If this is a Customer Deposit (aropen_doctype='R')
--       the we need to convert the total to a base transaction
  IF(_p.aropen_doctype='R') THEN
    SELECT insertGLTransaction(fetchJournalNumber('AR-MISC'), 'A/R',
                               'CD', _p.aropen_docnumber, 'CM Application',
                               cr.accnt_id, db.accnt_id,
                               -1, currToBase(_p.aropen_curr_id, _totalAmount, _p.aropen_docdate),
                               _applyDate)
      INTO _result
      FROM accnt AS cr, accnt AS db
     WHERE ((db.accnt_id = findDeferredAccount(_p.aropen_cust_id))
       AND  (cr.accnt_id = findARAccount(_p.aropen_cust_id)) );
    IF(NOT FOUND OR _result < 0) THEN
      RAISE EXCEPTION 'There was an error posting the Customer Deposit GL Transactions.';
    END IF;
  END IF;

--  Record the amount posted and mark the source aropen as closed if it is completely posted
  UPDATE aropen
  SET aropen_paid = round(aropen_paid + _totalAmount, 2)
  WHERE (aropen_id=pAropenid);

  UPDATE aropen
  SET aropen_open = (round(aropen_amount, 2) > round(aropen_paid, 2))
  WHERE (aropen_id=pAropenid);

  RETURN pAropenid;

END;
$_$;


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

--
-- TOC entry 1985 (class 1255 OID 146566787)
-- Dependencies: 4536 8
