-- Name: creditmemo; Type: VIEW; Schema: api; Owner: admin
--

CREATE VIEW creditmemo AS
    SELECT cmhead.cmhead_number AS memo_number, CASE WHEN (cmhead.cmhead_invcnumber = '-1'::text) THEN ''::text ELSE cmhead.cmhead_invcnumber END AS apply_to, cmhead.cmhead_docdate AS memo_date, CASE WHEN cmhead.cmhead_posted THEN 'Posted'::text ELSE 'Unposted'::text END AS status, salesrep.salesrep_number AS sales_rep, cmhead.cmhead_commission AS commission, COALESCE(taxzone.taxzone_code, 'None'::text) AS tax_zone, COALESCE(rsncode.rsncode_code, 'None'::text) AS reason_code, cmhead.cmhead_hold AS on_hold, custinfo.cust_number AS customer_number, cmhead.cmhead_billtoname AS billto_name, cmhead.cmhead_billtoaddress1 AS billto_address1, cmhead.cmhead_billtoaddress2 AS billto_address2, cmhead.cmhead_billtoaddress3 AS billto_address3, cmhead.cmhead_billtocity AS billto_city, cmhead.cmhead_billtostate AS billto_state, cmhead.cmhead_billtozip AS billto_postal_code, cmhead.cmhead_billtocountry AS billto_country, shiptoinfo.shipto_num AS shipto_number, cmhead.cmhead_shipto_name AS shipto_name, cmhead.cmhead_shipto_address1 AS shipto_address1, cmhead.cmhead_shipto_address2 AS shipto_address2, cmhead.cmhead_shipto_address3 AS shipto_address3, cmhead.cmhead_shipto_city AS shipto_city, cmhead.cmhead_shipto_state AS shipto_state, cmhead.cmhead_shipto_zipcode AS shipto_postal_code, cmhead.cmhead_shipto_country AS shipto_country, cmhead.cmhead_custponumber AS customer_po_number, cmhead.cmhead_comments AS notes, curr.curr_abbr AS currency, cmhead.cmhead_misc_descrip AS misc_charge_description, cmhead.cmhead_misc AS misc_charge_amount, CASE WHEN (cmhead.cmhead_misc_accnt_id = (-1)) THEN ''::text ELSE public.formatglaccount(cmhead.cmhead_misc_accnt_id) END AS misc_charge_credit_account, cmhead.cmhead_freight AS freight FROM ((((((public.cmhead LEFT JOIN public.custinfo ON ((custinfo.cust_id = cmhead.cmhead_cust_id))) LEFT JOIN public.shiptoinfo ON ((shiptoinfo.shipto_id = cmhead.cmhead_shipto_id))) LEFT JOIN public.curr_symbol curr ON ((curr.curr_id = cmhead.cmhead_curr_id))) LEFT JOIN public.salesrep ON ((salesrep.salesrep_id = cmhead.cmhead_salesrep_id))) LEFT JOIN public.taxzone ON ((taxzone.taxzone_id = cmhead.cmhead_taxzone_id))) LEFT JOIN public.rsncode ON ((rsncode.rsncode_id = cmhead.cmhead_rsncode_id)));


ALTER TABLE creditmemo OWNER TO admin;

--
-- Name: VIEW creditmemo; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW creditmemo IS 'Credit Memo Header';



--
-- Name: insertcreditmemo(api.creditmemo); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION insertcreditmemo(api.creditmemo) 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 cmhead (
		cmhead_number,
		cmhead_posted,
		cmhead_invcnumber,
                cmhead_custponumber,
		cmhead_cust_id,
		cmhead_docdate,
		cmhead_shipto_id,
		cmhead_shipto_name,
		cmhead_shipto_address1,
		cmhead_shipto_address2,
		cmhead_shipto_address3,
		cmhead_shipto_city,
		cmhead_shipto_state,
		cmhead_shipto_zipcode,
		cmhead_shipto_country,
		cmhead_salesrep_id,
		cmhead_freight,
		cmhead_misc,
		cmhead_comments,
		cmhead_printed,
		cmhead_billtoname,
		cmhead_billtoaddress1,
		cmhead_billtoaddress2,
		cmhead_billtoaddress3,
		cmhead_billtocity,
		cmhead_billtostate,
		cmhead_billtozip,
		cmhead_billtocountry,
		cmhead_hold,
		cmhead_commission,
		cmhead_misc_accnt_id,
		cmhead_misc_descrip,
		cmhead_rsncode_id,
		cmhead_curr_id,
		cmhead_taxzone_id,
                cmhead_gldistdate,
                cmhead_rahead_id
		)
	 SELECT
		(CASE -- use a case here so we don't unnecessarily fetch a new CM number
			WHEN pNew.memo_number IS NULL THEN fetchCMNumber()
			ELSE pNew.memo_number
		END),
		FALSE, -- posted
		pNew.apply_to,
		pNew.customer_po_number,
		cust_id,
		COALESCE(pNew.memo_date, CURRENT_DATE),
		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,
		COALESCE(getSalesRepId(pNew.sales_rep),shipto_salesrep_id,cust_salesrep_id),
		COALESCE(pNew.freight, 0),
		COALESCE(pNew.misc_charge_amount, 0),
		pNew.notes,
		FALSE, -- printed
		COALESCE(pNew.billto_name, invchead_billto_name, cust_name),
		COALESCE(pNew.billto_address1, invchead_billto_address1, addr_line1),
		COALESCE(pNew.billto_address2, invchead_billto_address2, addr_line2),
		COALESCE(pNew.billto_address3, invchead_billto_address3, addr_line3),
		COALESCE(pNew.billto_city, invchead_billto_city, addr_city),
		COALESCE(pNew.billto_state, invchead_billto_state, addr_state),
		COALESCE(pNew.billto_postal_code, invchead_billto_zipcode, addr_postalcode),
		COALESCE(pNew.billto_country, invchead_billto_country, addr_country),
		COALESCE(pNew.on_hold, FALSE),
		COALESCE(pNew.commission, 0),
		COALESCE(getGlAccntId(pNew.misc_charge_credit_account),-1),
		pNew.misc_charge_description,
		(SELECT rsncode_id FROM rsncode WHERE rsncode_code = pNew.reason_code),
		COALESCE(getCurrId(pNew.currency),cust_curr_id,basecurrid()),
                CASE WHEN pNew.tax_zone = 'None' THEN NULL
                     ELSE COALESCE(getTaxZoneID(pNew.tax_zone),cust_taxzone_id)
                END,
                NULL,
                NULL
	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 invchead ON (invchead_id=getInvcheadId(pNEW.apply_to))
                LEFT OUTER JOIN cntct ON (cntct_id=cust_cntct_id)
                LEFT OUTER JOIN addr ON (addr_id=cntct_addr_id)
	WHERE cust_id = (CASE
		WHEN pNew.customer_number IS NOT NULL THEN (SELECT getCustId(pNew.customer_number))
		ELSE (SELECT invchead_cust_id FROM invchead WHERE invchead_invcnumber = pNew.apply_to)
	END);
	RETURN TRUE;
END;
$_$;


ALTER FUNCTION public.insertcreditmemo(api.creditmemo) OWNER TO admin;

--
-- Name: cmitem; Type: TABLE; Schema: public; Owner: admin; Tablespace: 
--

CREATE TABLE cmitem (
    cmitem_id integer DEFAULT nextval(('cmitem_cmitem_id_seq'::text)::regclass) NOT NULL,
    cmitem_cmhead_id integer NOT NULL,
    cmitem_linenumber integer NOT NULL,
    cmitem_itemsite_id integer NOT NULL,
    cmitem_qtycredit numeric(18,6) NOT NULL,
    cmitem_qtyreturned numeric(18,6) NOT NULL,
    cmitem_unitprice numeric(16,4) NOT NULL,
    cmitem_comments text,
    cmitem_rsncode_id integer,
    cmitem_taxtype_id integer,
    cmitem_qty_uom_id integer NOT NULL,
    cmitem_qty_invuomratio numeric(20,10) NOT NULL,
    cmitem_price_uom_id integer NOT NULL,
    cmitem_price_invuomratio numeric(20,10) NOT NULL,
    cmitem_raitem_id integer,
    cmitem_updateinv boolean DEFAULT true NOT NULL
);


ALTER TABLE cmitem OWNER TO admin;

--
-- Name: TABLE cmitem; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE cmitem IS 'S/O Credit Memo Line Item information';



--
-- Name: creditmemoline; Type: VIEW; Schema: api; Owner: admin
--

CREATE VIEW creditmemoline AS
    SELECT cmhead.cmhead_number AS memo_number, cmitem.cmitem_linenumber AS line_number, item.item_number, whsinfo.warehous_code AS recv_site, rsncode.rsncode_code AS reason_code, cmitem.cmitem_qtyreturned AS qty_returned, cmitem.cmitem_qtycredit AS qty_to_credit, COALESCE(qty_uom.uom_name, 'None'::text) AS qty_uom, cmitem.cmitem_unitprice AS net_unit_price, COALESCE(price_uom.uom_name, 'None'::text) AS price_uom, COALESCE(taxtype.taxtype_name, 'None'::text) AS tax_type, cmitem.cmitem_comments AS notes FROM ((((((((public.cmitem LEFT JOIN public.cmhead ON ((cmitem.cmitem_cmhead_id = cmhead.cmhead_id))) LEFT JOIN public.itemsite ON ((itemsite.itemsite_id = cmitem.cmitem_itemsite_id))) LEFT JOIN public.item ON ((item.item_id = itemsite.itemsite_item_id))) LEFT JOIN public.whsinfo ON ((whsinfo.warehous_id = itemsite.itemsite_warehous_id))) LEFT JOIN public.rsncode ON ((rsncode.rsncode_id = cmitem.cmitem_rsncode_id))) LEFT JOIN public.taxtype ON ((taxtype.taxtype_id = cmitem.cmitem_taxtype_id))) LEFT JOIN public.uom qty_uom ON ((qty_uom.uom_id = cmitem.cmitem_qty_uom_id))) LEFT JOIN public.uom price_uom ON ((price_uom.uom_id = cmitem.cmitem_price_uom_id)));


ALTER TABLE creditmemoline OWNER TO admin;

--
-- Name: VIEW creditmemoline; Type: COMMENT; Schema: api; Owner: admin
--

COMMENT ON VIEW creditmemoline IS 'Credit Memo Line';



--
-- Name: insertcreditmemoline(api.creditmemoline); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION insertcreditmemoline(api.creditmemoline) 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;
  _check INTEGER;
  _r RECORD;

BEGIN
  SELECT cmhead_id INTO _check
  FROM cmhead
  WHERE (cmhead_id=getCmheadId(pNew.memo_number, FALSE));
  IF (NOT FOUND) THEN
    RAISE EXCEPTION 'Credit Memo # % not found', pNew.memo_number;
  END IF;

  INSERT INTO cmitem ( cmitem_cmhead_id,
                       cmitem_linenumber,
                       cmitem_itemsite_id,
                       cmitem_qtycredit,
                       cmitem_qtyreturned,
                       cmitem_unitprice,
                       cmitem_comments,
                       cmitem_rsncode_id,
                       cmitem_taxtype_id,
                       cmitem_qty_uom_id,
                       cmitem_qty_invuomratio,
                       cmitem_price_uom_id,
                       cmitem_price_invuomratio	)
  SELECT cmhead_id,
         COALESCE(pNew.line_number,
                  (SELECT (COALESCE(MAX(cmitem_linenumber), 0) + 1)
                   FROM cmitem WHERE (cmitem_cmhead_id=cmhead_id))),
         COALESCE(itemsite_id, -1),
         COALESCE(pNew.qty_to_credit, 0),
         COALESCE(pNew.qty_returned, 0),
         COALESCE(pNew.net_unit_price, 0),
         pNew.notes,
         getRsnId(pNew.reason_code),
         taxtype_id,
         COALESCE(getUomId(pNew.qty_uom), item_inv_uom_id),
         CASE
           WHEN item_id IS NOT NULL THEN itemuomtouomratio(item_id, COALESCE(getUomId(pNew.qty_uom),item_inv_uom_id),item_inv_uom_id)
           ELSE 1
         END,
         COALESCE(getUomId(pNew.price_uom),item_price_uom_id),
         CASE
           WHEN item_id IS NOT NULL THEN itemuomtouomratio(item_id, COALESCE(getUomId(pNew.price_uom),item_price_uom_id),item_price_uom_id)
           ELSE 1
        END
  FROM cmhead LEFT OUTER JOIN item ON (item_id=getItemId(pNew.item_number))
              LEFT OUTER JOIN itemsite ON (itemsite_item_id=item_id AND itemsite_warehous_id=getWarehousId(pNew.recv_site, 'ALL'))
              LEFT OUTER JOIN taxtype ON (taxtype_id=CASE WHEN pNew.tax_type IS NULL THEN getItemTaxType(item_id,cmhead_taxzone_id)
                                                          WHEN pNew.tax_type = 'None' THEN NULL
                                                          ELSE getTaxTypeId(pNew.tax_type)
                                                     END)
  WHERE (cmhead_id=getCmheadId(pNew.memo_number, FALSE));

  RETURN TRUE;
END;
$_$;


ALTER FUNCTION public.insertcreditmemoline(api.creditmemoline) OWNER TO admin;

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

CREATE FUNCTION insertflgroup(pflheadid integer, pperiodid integer, pflgrpid integer, plevel integer, psummarize boolean, pinterval character DEFAULT NULL::bpchar, pprjid integer DEFAULT NULL::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.

/* see the "performance vs. history" comment in financialreport() */
DECLARE
  _subtotal BOOLEAN;
  _r RECORD;
  _g RECORD;
  _all BOOLEAN;
  _username TEXT := getEffectiveXtUser();

BEGIN

  _all = COALESCE(pPrjid,-1) = -1;
  
-- Check to see if this group wants a subtotal
  _subtotal := FALSE;
  IF (pFlgrpid != -1) THEN
    SELECT COALESCE(flgrp_subtotal, FALSE) INTO _subtotal
      FROM flgrp
     WHERE ((flgrp_flhead_id=pFlheadid)
       AND  (flgrp_id=pFlgrpid));
  END IF;

  FOR _r IN SELECT 'G' AS type, flgrp_id AS type_id,
                   flgrp_order AS orderby,
                   flgrp_summarize AS summarize,
                   flgrp_subtract AS subtract,
                   CASE WHEN(flgrp_summarize AND (NOT flgrp_showstart)) THEN NULL
                        ELSE 0.00
                   END AS beginning,
                   CASE WHEN(flgrp_summarize AND (NOT flgrp_showend)) THEN NULL
                        ELSE 0.00
                   END AS ending,
                   CASE WHEN(flgrp_summarize AND (NOT flgrp_showdelta)) THEN NULL
                        ELSE 0.00
                   END AS debits,
                   CASE WHEN(flgrp_summarize AND (NOT flgrp_showdelta)) THEN NULL
                        ELSE 0.00
                   END AS credits,
                   CASE WHEN(flgrp_summarize AND (NOT flgrp_showbudget)) THEN NULL
                        ELSE 0.00
                   END AS budget,
                   CASE WHEN(flgrp_summarize AND (NOT flgrp_showdiff)) THEN NULL
                        ELSE 0.00
                   END AS diff,
                   CASE WHEN(flgrp_summarize AND (NOT flgrp_showcustom)) THEN NULL
                        ELSE 0.00
                   END AS custom,
                   CASE WHEN(flgrp_showstartprcnt) THEN 0.00
                        ELSE NULL
                   END AS beginningprcnt,
                   CASE WHEN(flgrp_showendprcnt) THEN 0.00
                        ELSE NULL
                   END AS endingprcnt,
                   CASE WHEN(flgrp_showdeltaprcnt) THEN 0.00
                        ELSE NULL
                   END AS debitsprcnt,
                   CASE WHEN(flgrp_showdeltaprcnt) THEN 0.00
                        ELSE NULL
                   END AS creditsprcnt,
                   CASE WHEN(flgrp_showbudgetprcnt) THEN 0.00
                        ELSE NULL
                   END AS budgetprcnt,
                   CASE WHEN(flgrp_showdiffprcnt) THEN 0.00
                        ELSE NULL
                   END AS diffprcnt,
                   CASE WHEN(flgrp_showcustomprcnt) THEN 0.00
                        ELSE NULL
                   END AS customprcnt,
                   -1 AS accnt_id,
                   '' AS accnt_number
              FROM flgrp
             WHERE ((flgrp_flgrp_id=pFlgrpid)
               AND  (flgrp_flhead_id=pFlheadid))
             UNION ALL
            SELECT 'I' AS type, flitem_id AS type_id,
                   flitem_order AS orderby,
                   FALSE AS summarize,
                   flitem_subtract AS subtract,
                   CASE WHEN (flitem_showstart AND (first_trialbal_id IS NULL)) THEN 0.00
                        WHEN (flitem_showstart) THEN normalizeTrialBal(first_trialbal_id, 'B')
                        ELSE NULL
                   END AS beginning,
                   CASE WHEN (flitem_showend AND (last_trialbal_id IS NULL)) THEN 0.00
                        WHEN (flitem_showend) THEN normalizeTrialBal(last_trialbal_id, 'E')
                        ELSE NULL
                   END AS ending,
                   CASE WHEN (flitem_showdelta) THEN sum_trialbal_debits
                        ELSE NULL
                   END AS debits,
                   CASE WHEN (flitem_showdelta) THEN sum_trialbal_credits
                        ELSE NULL
                   END AS credits,
                   CASE WHEN ((flitem_showbudget) AND (accnt_type IN ('R','E')) AND flhead_type IN ('I','C','A')) THEN COALESCE(sum_budget_amount,0)
                        WHEN ((flitem_showbudget) AND (accnt_type IN ('R','E')) AND flhead_type = 'B' ) THEN
                                (SELECT COALESCE(SUM(b.budget_amount),0)
                                FROM budget b,
                                        (SELECT ytd.period_id AS ytd_period_id
                                        FROM period cp, period ytd
                                        WHERE ((cp.period_id = last_flitem_period_id)
                                        AND (ytd.period_start <= cp.period_start)
                                AND (ytd.period_yearperiod_id = cp.period_yearperiod_id))) AS periods
                                WHERE ((b.budget_accnt_id=accnt_id)
                                AND (b.budget_period_id=ytd_period_id)))
                        WHEN ((flitem_showbudget) AND (accnt_type IN ('A','L','Q')) AND flhead_type = 'C') THEN calccashbudget(accnt_id,last_flitem_period_id,pInterval)
                        ELSE COALESCE(last_budget_amount,0)
                   END AS budget,
                   CASE WHEN (flitem_showdiff AND (first_trialbal_id IS NULL)) THEN 0.00
                        WHEN (flitem_showdiff) THEN COALESCE(normalizeTrialBal(last_trialbal_id, 'E') - normalizeTrialBal(first_trialbal_id, 'B'), 0.00)
                        ELSE NULL
                   END AS diff,
                   CASE WHEN (NOT flitem_showcustom) THEN NULL
                        WHEN (flitem_custom_source='S' AND (first_trialbal_id IS NOT NULL)) THEN normalizeTrialBal(first_trialbal_id, 'B')
                        WHEN (flitem_custom_source='E' AND (first_trialbal_id IS NOT NULL)) THEN normalizeTrialBal(last_trialbal_id, 'E')
                        WHEN (flitem_custom_source='D') THEN sum_trialbal_debits
                        WHEN (flitem_custom_source='C') THEN sum_trialbal_credits
                        WHEN (flitem_custom_source='B') THEN (
                                CASE
                                  WHEN (accnt_type IN ('R','E')) THEN sum_budget_amount
                                  ELSE last_budget_amount
                                END)
                        WHEN (flitem_custom_source='F' AND  (first_trialbal_id IS NOT NULL)) THEN COALESCE(normalizeTrialBal(last_trialbal_id, 'E') - normalizeTrialBal(first_trialbal_id, 'B'), 0.00)
                        ELSE 0.00
                   END AS custom,
                   CASE WHEN(flitem_showstartprcnt) THEN 0.00
                        ELSE NULL
                   END AS beginningprcnt,
                   CASE WHEN(flitem_showendprcnt) THEN 0.00
                        ELSE NULL
                   END AS endingprcnt,
                   CASE WHEN(flitem_showdeltaprcnt) THEN 0.00
                        ELSE NULL
                   END AS debitsprcnt,
                   CASE WHEN(flitem_showdeltaprcnt) THEN 0.00
                        ELSE NULL
                   END AS creditsprcnt,
                   CASE WHEN(flitem_showbudgetprcnt) THEN 0.00
                        ELSE NULL
                   END AS budgetprcnt,
                   CASE WHEN(flitem_showdiffprcnt) THEN 0.00
                        ELSE NULL
                   END AS diffprcnt,
                   CASE WHEN(flitem_showcustomprcnt) THEN 0.00
                        ELSE NULL
                   END AS customprcnt,
                   accnt_id,
                   public.formatglaccount(accnt_id) AS accnt_number
              FROM
                (SELECT 
                  flhead_type,flitem_id,flitem_order,flitem_subtract,flitem_showstart,flitem_showend,
                  flitem_showdelta,flitem_showbudget,flitem_showdiff,flitem_showcustom,
                  flitem_custom_source,flitem_showstartprcnt,flitem_showendprcnt,flitem_showdeltaprcnt,
                  flitem_showbudgetprcnt,flitem_showdiffprcnt,flitem_showcustomprcnt,
                  accnt_id,accnt_type,
                  FIRST(trialbal_id) AS first_trialbal_id, LAST(trialbal_id) AS last_trialbal_id,
                  SUM(trialbal_debits) AS sum_trialbal_debits, SUM(trialbal_credits) AS sum_trialbal_credits, 
                  LAST(flitem_period_id) AS last_flitem_period_id,
                  SUM(budget_amount) AS sum_budget_amount, LAST(budget_amount) AS last_budget_amount
                  FROM
                (SELECT period_id AS flitem_period_id, period_start,flhead_type,flitem_id,flitem_order,flitem_subtract,flitem_showstart,flitem_showend,
                        flitem_showdelta,flitem_showbudget,flitem_showdiff,flitem_showcustom,
                        flitem_custom_source,flitem_showstartprcnt,flitem_showendprcnt,flitem_showdeltaprcnt,
                        flitem_showbudgetprcnt,flitem_showdiffprcnt,flitem_showcustomprcnt,
                        accnt_id,accnt_type,COALESCE(trialbal_id,getlasttrialbalid(accnt_id,period_id)) as trialbal_id,COALESCE(trialbal_debits,0) as trialbal_debits,
                        COALESCE(trialbal_credits,0) AS trialbal_credits,COALESCE(budget_amount,0) AS budget_amount
                   FROM (SELECT period_id, period_start, flhead_type, flitem_id,flitem_order,flitem_subtract,flitem_showstart,flitem_showend,
                                flitem_showdelta,flitem_showbudget,flitem_showdiff,flitem_showcustom,flitem_custom_source,flitem_showstartprcnt,
                                flitem_showendprcnt,flitem_showdeltaprcnt,flitem_showbudgetprcnt,flitem_showdiffprcnt,flitem_showcustomprcnt,
                                accnt_id, accnt_type
                        FROM  period,flaccnt
                        WHERE ((flitem_flhead_id=pFlheadid)
                        AND (flitem_flgrp_id=pFlgrpid)
                        AND (_all OR prj_id=pPrjId)
                        AND (period_id IN  (SELECT * FROM getperiodid(pPeriodId,pInterval))))
                        ORDER BY flitem_id
                        ) AS flitem
                   LEFT OUTER JOIN trialbal
                     ON ((trialbal_accnt_id=accnt_id)
                     AND (trialbal_period_id=period_id))
                   LEFT OUTER JOIN budget
                     ON ((budget_accnt_id=accnt_id)
                     AND (budget_period_id=period_id))
             ORDER BY accnt_id, period_start) AS data
             GROUP BY flhead_type,flitem_id,flitem_order,flitem_subtract,flitem_showstart,flitem_showend,
                flitem_showdelta,flitem_showbudget,flitem_showdiff,flitem_showcustom,
                flitem_custom_source,flitem_showstartprcnt,flitem_showendprcnt,flitem_showdeltaprcnt,
                flitem_showbudgetprcnt,flitem_showdiffprcnt,flitem_showcustomprcnt,accnt_id,accnt_type) AS agg
             UNION ALL
            SELECT 'S' AS type, flspec_id AS type_id,
                   flspec_order AS orderby,
                   FALSE AS summarize,
                   flspec_subtract AS subtract,
                   CASE WHEN (flspec_showstart) THEN findSpecialFinancial('S', flspec_type, pPeriodid)
                        ELSE NULL
                   END AS beginning,
                   CASE WHEN (flspec_showend) THEN findSpecialFinancial('E', flspec_type, pPeriodid)
                        ELSE NULL
                   END AS ending,
                   CASE WHEN (flspec_showdelta) THEN findSpecialFinancial('D', flspec_type, pPeriodid)
                        ELSE NULL
                   END AS debits,
                   CASE WHEN (flspec_showdelta) THEN findSpecialFinancial('C', flspec_type, pPeriodid)
                        ELSE NULL
                   END AS credits,
                   CASE WHEN (flspec_showbudget) THEN findSpecialFinancial('B', flspec_type, pPeriodid)
                        ELSE NULL
                   END AS budget,
                   CASE WHEN (flspec_showdiff) THEN findSpecialFinancial('E', flspec_type, pPeriodid) - findSpecialFinancial('S', flspec_type, pPeriodid)
                        ELSE NULL
                   END AS diff,
                   CASE WHEN (NOT flspec_showcustom) THEN NULL
                        WHEN (flspec_custom_source='F') THEN findSpecialFinancial('E', flspec_type, pPeriodid) - findSpecialFinancial('S', flspec_type, pPeriodid)
                        WHEN (flspec_custom_source IN ('S', 'E', 'D', 'C', 'B')) THEN findSpecialFinancial(flspec_custom_source, flspec_type, pPeriodid)
                        ELSE 0.00
                   END AS custom,
                   CASE WHEN(flspec_showstartprcnt) THEN 0.00
                        ELSE NULL
                   END AS beginningprcnt,
                   CASE WHEN(flspec_showendprcnt) THEN 0.00
                        ELSE NULL
                   END AS endingprcnt,
                   CASE WHEN(flspec_showdeltaprcnt) THEN 0.00
                        ELSE NULL
                   END AS debitsprcnt,
                   CASE WHEN(flspec_showdeltaprcnt) THEN 0.00
                        ELSE NULL
                   END AS creditsprcnt,
                   CASE WHEN(flspec_showbudgetprcnt) THEN 0.00
                        ELSE NULL
                   END AS budgetprcnt,
                   CASE WHEN(flspec_showdiffprcnt) THEN 0.00
                        ELSE NULL
                   END AS diffprcnt,
                   CASE WHEN(flspec_showcustomprcnt) THEN 0.00
                        ELSE NULL
                   END AS customprcnt,
                   -1 AS accnt_id,
                   '' AS accnt_number
              FROM flspec
             WHERE ((flspec_flgrp_id=pFlgrpid)
               AND  (flspec_flhead_id=pFlheadid))
          ORDER BY orderby, accnt_number LOOP

    IF (_r.type = 'G') THEN

-- Create a record for the items sub items to be attached to and be able to update the total
      INSERT INTO flrpt
             (flrpt_flhead_id, flrpt_period_id, flrpt_username,
              flrpt_order,
              flrpt_level, flrpt_type, flrpt_type_id,
              flrpt_beginning, flrpt_ending,
              flrpt_debits, flrpt_credits, flrpt_budget, flrpt_diff, flrpt_custom,
              flrpt_beginningprcnt, flrpt_endingprcnt,
              flrpt_debitsprcnt, flrpt_creditsprcnt, flrpt_budgetprcnt, flrpt_diffprcnt, flrpt_customprcnt,
              flrpt_parent_id, flrpt_interval)
      VALUES (pFlheadid, pPeriodid, _username,
              (COALESCE(( SELECT MAX(flrpt_order)
                            FROM flrpt
                           WHERE ((flrpt_flhead_id=pFlheadid)
                             AND  (flrpt_period_id=pPeriodid)
                             AND (flrpt_interval=pInterval)
--                           AND  (flrpt_username=_username)
                             )
                        ), 1) + 1),
              pLevel, _r.type, _r.type_id,
              _r.beginning, _r.ending,
              _r.debits, _r.credits, _r.budget, _r.diff, _r.custom,
              _r.beginningprcnt, _r.endingprcnt,
              _r.debitsprcnt, _r.creditsprcnt, _r.budgetprcnt, _r.diffprcnt, _r.customprcnt,
              pFlgrpid, pInterval);

      PERFORM insertFlGroup(pFlheadid, pPeriodid, _r.type_id, (pLevel + 1), (pSummarize OR _r.summarize), pInterval, pPrjid);

-- Update the parent item
      SELECT COALESCE(flrpt_beginning, 0.00) AS beginning,
             COALESCE(flrpt_ending, 0.00) AS ending,
             COALESCE(flrpt_debits, 0.00) AS debits,
             COALESCE(flrpt_credits, 0.00) AS credits,
             COALESCE(flrpt_budget, 0.00) AS budget,
             COALESCE(flrpt_diff, 0.00) AS diff,
             COALESCE(flrpt_custom, 0.00) AS custom INTO _g
        FROM flrpt
       WHERE ((flrpt_flhead_id=pFlheadid)
         AND  (flrpt_period_id=pPeriodid)
          AND (flrpt_interval=pInterval)
--       AND  (flrpt_username=_username)
         AND  (flrpt_type=_r.type)
         AND  (flrpt_type_id=_r.type_id));
      IF (_r.subtract) THEN
        UPDATE flrpt
           SET flrpt_beginning = flrpt_beginning - _g.beginning,
               flrpt_ending    = flrpt_ending    - _g.ending,
               flrpt_debits    = flrpt_debits    - _g.debits,
               flrpt_credits   = flrpt_credits   - _g.credits,
               flrpt_budget    = flrpt_budget    - _g.budget,
               flrpt_diff      = flrpt_diff      - _g.diff,
               flrpt_custom    = flrpt_custom    - _g.custom
         WHERE ((flrpt_flhead_id=pFlheadid)
           AND  (flrpt_period_id=pPeriodid)
           AND  (flrpt_interval=pInterval)
--         AND  (flrpt_username=_username)
           AND  (flrpt_type='G')
           AND  (flrpt_type_id=pFlgrpid));
      ELSE
        UPDATE flrpt
           SET flrpt_beginning = flrpt_beginning + _g.beginning,
               flrpt_ending    = flrpt_ending    + _g.ending,
               flrpt_debits    = flrpt_debits    + _g.debits,
               flrpt_credits   = flrpt_credits   + _g.credits,
               flrpt_budget    = flrpt_budget    + _g.budget,
               flrpt_diff      = flrpt_diff      + _g.diff,
               flrpt_custom    = flrpt_custom    + _g.custom
         WHERE ((flrpt_flhead_id=pFlheadid)
           AND  (flrpt_period_id=pPeriodid)
           AND  (flrpt_interval=pInterval)
--         AND  (flrpt_username=_username)
           AND  (flrpt_type='G')
           AND  (flrpt_type_id=pFlgrpid));
      END IF;

-- If we are summarizing then we need to remove the record we created now that we have updated the total
      IF (pSummarize) THEN
        DELETE FROM flrpt
         WHERE ((flrpt_flhead_id=pFlheadid)
          AND  (flrpt_period_id=pPeriodid)
          AND  (flrpt_interval=pInterval)
--        AND  (flrpt_username=_username)
          AND  (flrpt_type=_r.type)
          AND  (flrpt_type_id=_r.type_id));
      END IF;

    ELSE
      IF (_r.type = 'I' OR _r.type = 'S' ) THEN

-- If we are not summarizing then create a new entry for this record
        IF (NOT pSummarize) THEN
          INSERT INTO flrpt
                 (flrpt_flhead_id, flrpt_period_id, flrpt_username,
                  flrpt_order,
                  flrpt_level, flrpt_type, flrpt_type_id,
                  flrpt_beginning, flrpt_ending,
                  flrpt_debits, flrpt_credits, flrpt_budget, flrpt_diff, flrpt_custom,
                  flrpt_beginningprcnt, flrpt_endingprcnt,
                  flrpt_debitsprcnt, flrpt_creditsprcnt, flrpt_budgetprcnt, flrpt_diffprcnt, flrpt_customprcnt,
                  flrpt_parent_id,flrpt_accnt_id,flrpt_interval)
          VALUES (pFlheadid, pPeriodid, _username,
                  (COALESCE(( SELECT MAX(flrpt_order)
                               FROM flrpt
                              WHERE ((flrpt_flhead_id=pFlheadid)
                                AND  (flrpt_period_id=pPeriodid)
                                AND  (flrpt_interval=pInterval)
--                              AND  (flrpt_username=_username)
                                )
                            ), 1) + 1),
                  pLevel, _r.type, _r.type_id,
                  _r.beginning, _r.ending,
                  _r.debits, _r.credits, _r.budget, _r.diff, _r.custom,
                  _r.beginningprcnt, _r.endingprcnt,
                  _r.debitsprcnt, _r.creditsprcnt, _r.budgetprcnt, _r.diffprcnt, _r.customprcnt,
                  pFlgrpid,_r.accnt_id,pInterval);
        END IF;

-- Update the parent item
        IF (_r.subtract) THEN
          UPDATE flrpt
             SET flrpt_beginning = flrpt_beginning - COALESCE(_r.beginning, 0.00),
                 flrpt_ending    = flrpt_ending    - COALESCE(_r.ending, 0.00),
                 flrpt_debits    = flrpt_debits    - COALESCE(_r.debits, 0.00),
                 flrpt_credits   = flrpt_credits   - COALESCE(_r.credits, 0.00),
                 flrpt_budget    = flrpt_budget    - COALESCE(_r.budget, 0.00),
                 flrpt_diff      = flrpt_diff      - COALESCE(_r.diff, 0.00),
                 flrpt_custom    = flrpt_custom    - COALESCE(_r.custom, 0.00)
           WHERE ((flrpt_flhead_id=pFlheadid)
             AND  (flrpt_period_id=pPeriodid)
             AND  (flrpt_interval=pInterval)
--           AND  (flrpt_username=_username)
             AND  (flrpt_type='G')
             AND  (flrpt_type_id=pFlgrpid));
        ELSE
          UPDATE flrpt
             SET flrpt_beginning = flrpt_beginning + COALESCE(_r.beginning, 0.00),
                 flrpt_ending    = flrpt_ending    + COALESCE(_r.ending, 0.00),
                 flrpt_debits    = flrpt_debits    + COALESCE(_r.debits, 0.00),
                 flrpt_credits   = flrpt_credits   + COALESCE(_r.credits, 0.00),
                 flrpt_budget    = flrpt_budget    + COALESCE(_r.budget, 0.00),
                 flrpt_diff      = flrpt_diff      + COALESCE(_r.diff, 0.00),
                 flrpt_custom    = flrpt_custom    + COALESCE(_r.custom, 0.00)
           WHERE ((flrpt_flhead_id=pFlheadid)
             AND  (flrpt_interval=pInterval)
             AND  (flrpt_period_id=pPeriodid)
--           AND  (flrpt_username=_username)
             AND  (flrpt_type='G')
             AND  (flrpt_type_id=pFlgrpid));
        END IF;

      END IF;
    END IF;

  END LOOP;

  IF (NOT pSummarize) THEN
-- If this group wants a summarized line create it here.
    IF (_subtotal) THEN
      INSERT INTO flrpt
             (flrpt_flhead_id, flrpt_period_id, flrpt_username,
              flrpt_order,
              flrpt_level, flrpt_type, flrpt_type_id,
              flrpt_beginning, flrpt_ending,
              flrpt_debits, flrpt_credits, flrpt_budget, flrpt_diff, flrpt_custom,
              flrpt_beginningprcnt, flrpt_endingprcnt,
              flrpt_debitsprcnt, flrpt_creditsprcnt, flrpt_budgetprcnt, flrpt_diffprcnt, flrpt_customprcnt,
              flrpt_parent_id, flrpt_altname,flrpt_interval )
      SELECT pFlheadid, pPeriodid, _username,
             (COALESCE(( SELECT MAX(flrpt_order)
                           FROM flrpt
                          WHERE ((flrpt_flhead_id=pFlheadid)
                            AND  (flrpt_period_id=pPeriodid)
                            AND  (flrpt_interval=pInterval)
--                          AND  (flrpt_username=_username)
                                )
                       ), 1) + 1),
             pLevel, 'T', -1,
             CASE WHEN (flgrp_showstart) THEN flrpt_beginning
                  ELSE NULL
             END,
             CASE WHEN (flgrp_showend) THEN flrpt_ending
                  ELSE NULL
             END,
             CASE WHEN (flgrp_showdelta) THEN flrpt_debits
                  ELSE NULL
             END,
             CASE WHEN (flgrp_showdelta) THEN flrpt_credits
                  ELSE NULL
             END,
             CASE WHEN (flgrp_showbudget) THEN flrpt_budget
                  ELSE NULL
             END,
             CASE WHEN (flgrp_showdiff) THEN flrpt_diff
                  ELSE NULL
             END,
             CASE WHEN (flgrp_showcustom) THEN flrpt_custom
                  ELSE NULL
             END,
             CASE WHEN (flgrp_showstartprcnt) THEN flrpt_beginningprcnt
                  ELSE NULL
             END,
             CASE WHEN (flgrp_showendprcnt) THEN flrpt_endingprcnt
                  ELSE NULL
             END,
             CASE WHEN (flgrp_showdeltaprcnt) THEN flrpt_debitsprcnt
                  ELSE NULL
             END,
             CASE WHEN (flgrp_showdeltaprcnt) THEN flrpt_creditsprcnt
                  ELSE NULL
             END,
             CASE WHEN (flgrp_showbudgetprcnt) THEN flrpt_budgetprcnt
                  ELSE NULL
             END,
             CASE WHEN (flgrp_showdiffprcnt) THEN flrpt_diffprcnt
                  ELSE NULL
             END,
             CASE WHEN (flgrp_showcustomprcnt) THEN flrpt_customprcnt
                  ELSE NULL
             END,
             pFlgrpid,
             CASE WHEN (flgrp_usealtsubtotal) THEN flgrp_altsubtotal
                  ELSE NULL
             END, pInterval
        FROM flrpt, flgrp
       WHERE ((flrpt_flhead_id=flgrp_flhead_id)
         AND  (flrpt_type_id=flgrp_id)
         AND  (flrpt_flhead_id=pFlheadid)
         AND  (flrpt_period_id=pPeriodid)
         AND  (flrpt_interval=pInterval)
--       AND  (flrpt_username=_username)
         AND  (flrpt_type='G')
         AND  (flrpt_type_id=pFlgrpid));
    END IF;
  END IF;

  return TRUE;
END;
$$;


ALTER FUNCTION public.insertflgroup(pflheadid integer, pperiodid integer, pflgrpid integer, plevel integer, psummarize boolean, pinterval character, pprjid integer) OWNER TO admin;

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

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

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

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

--
-- 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 different companies.',
        formatGlaccount(pDebitid), formatGlaccount(pCreditid);
    END IF;
  END IF;

--  Validate pDebitid
  IF (pDebitid IN (SELECT accnt_id FROM accnt)) THEN
    _debitid := pDebitid;
  ELSE
    -- Try and work out the unassigned accnt from the credit accnt
    SELECT getUnassignedAccntId(getcompanyid(pCreditid)) INTO _debitid;
  END IF;

--  Validate pCreditid
  IF (pCreditid IN (SELECT accnt_id FROM accnt)) THEN
    _creditid := pCreditid;
  ELSE
    -- Try and work out the unassigned accnt from the debit accnt
    SELECT getUnassignedAccntId(getcompanyid(pDebitid)) 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;

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

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

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

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

--
-- 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 invchead OWNER TO admin;

--
-- Name: TABLE invchead; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE invchead IS 'Invoice header information';


--
-- Name: COLUMN invchead.invchead_recurring; Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- Name: COLUMN invchead.invchead_recurring_interval; Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- Name: COLUMN invchead.invchead_recurring_type; Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- Name: COLUMN invchead.invchead_recurring_until; Type: COMMENT; Schema: public; Owner: admin
--

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


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


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


--
-- 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 prj OWNER TO admin;

--
-- Name: TABLE prj; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE prj IS 'Project information';


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


--
-- 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,
    saletype_default boolean DEFAULT false NOT NULL
);


ALTER TABLE saletype OWNER TO admin;

--
-- Name: TABLE saletype; Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- Name: COLUMN saletype.saletype_id; Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- Name: COLUMN saletype.saletype_code; Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- Name: COLUMN saletype.saletype_descr; Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- Name: COLUMN saletype.saletype_active; Type: COMMENT; Schema: public; Owner: admin
--

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


--
-- 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 shipzone OWNER TO admin;

--
-- Name: TABLE shipzone; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE shipzone IS 'Shipping Zone information';



--
-- 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 invoice OWNER TO admin;

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



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

--
-- 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 invcitem OWNER TO admin;

--
-- Name: TABLE invcitem; Type: COMMENT; Schema: public; Owner: admin
--

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



--
-- 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 invoiceline OWNER TO admin;

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



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

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

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

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

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

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

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

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

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

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

-- #19160 Exclude frozen check when "Edit Transaction" ABC analyze flag is amended
  IF ( (TG_OP = 'INSERT')
       OR (TG_OP = 'UPDATE' AND OLD.invhist_analyze = NEW.invhist_analyze)) THEN
    IF ( ( SELECT itemsite_freeze
         FROM itemsite
         WHERE (itemsite_id=NEW.invhist_itemsite_id) ) ) THEN
      NEW.invhist_posted = FALSE;
    END IF;
  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;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

--
-- Name: issuetoshipping(text, integer, numeric, integer, timestamp with time zone, integer); Type: FUNCTION; Schema: public; Owner: admin
--

CREATE FUNCTION issuetoshipping(pordertype text, pitemid integer, pqty numeric, pitemlocseries integer, ptimestamp timestamp with time zone, pinvhistid 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
  _itemlocSeries        INTEGER;
  _timestamp            TIMESTAMP WITH TIME ZONE;
  _coholdtype           TEXT;
  _balance              NUMERIC;
  _invhistid            INTEGER;
  _shipheadid           INTEGER;
  _shipnumber           INTEGER;
  _cntctid              INTEGER;
  _p                    RECORD;
  _m                    RECORD;
  _value                NUMERIC;
  _warehouseid          INTEGER;
  _shipitemid           INTEGER;
  _freight              NUMERIC;

BEGIN

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

  IF (pItemlocSeries = 0) THEN
    _itemlocSeries := NEXTVAL('itemloc_series_seq');
  ELSE
    _itemlocSeries := pItemlocSeries;
  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; 
  
    -- Check Credit Hold
    SELECT cohead_holdtype INTO _coholdtype
    FROM coitem JOIN cohead ON (cohead_id=coitem_cohead_id)
    WHERE (coitem_id=pitemid);

    SELECT calcSalesOrderAmt(cohead_id) -
           COALESCE(SUM(currToCurr(aropenalloc_curr_id, cohead_curr_id,
                                   aropenalloc_amount, cohead_orderdate)),0) INTO _balance
    FROM coitem JOIN cohead ON (cohead_id=coitem_cohead_id)
                LEFT OUTER JOIN aropenalloc ON (aropenalloc_doctype='S' AND
                                                aropenalloc_doc_id=cohead_id)
    WHERE (coitem_id=pitemid)
    GROUP BY cohead_id;

    --RAISE NOTICE 'issueToShipping - order balance is %', _balance;
    IF ( (_coholdtype = 'C') AND (_balance > 0.0) ) THEN
      RETURN -12;
    ELSIF (_coholdtype = 'P') THEN
      RETURN -13;
    ELSIF (_coholdtype = 'R') THEN
      RETURN -14;
    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;

      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(pordertype text, pitemid integer, pqty numeric, pitemlocseries integer, ptimestamp timestamp with time zone, pinvhistid integer) OWNER TO admin;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

CREATE FUNCTION itemcost(pitemsiteid 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
  _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 stdCost(itemsite_item_id)
              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(pitemsiteid integer) OWNER TO admin;

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

CREATE FUNCTION itemcost(pitemid integer, pcustid integer, pshiptoid integer, pqty numeric, pqtyuom integer, ppriceuom integer, pcurrid integer, peffective date, pasof date, psiteid 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.
--
-- Overload for future costing enhancements
--
BEGIN
  RETURN itemCost(pItemid,
                  pCustid,
                  pShiptoid,
                  pQty,
                  pQtyUOM,
                  pPriceUOM,
                  pCurrid,
                  pEffective,
                  pAsOf,
                  pSiteid,
                  FALSE);
END;
$$;


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

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

CREATE FUNCTION itemcost(pitemid integer, pcustid integer, pshiptoid integer, pqty numeric, pqtyuom integer, ppriceuom integer, pcurrid integer, peffective date, pasof date, psiteid integer, pdropship boolean) 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.
--
-- Overload for future costing enhancements
--
DECLARE
  _r RECORD;
  _cost NUMERIC := 0.0;
BEGIN
  -- cache item info
  SELECT *, itemInvPriceRat(item_id) AS itempriceinvrat INTO _r
  FROM itemsite, item
  WHERE (itemsite_item_id=pItemid)
    AND (itemsite_warehous_id=pSiteid)
    AND (item_id=pItemid);

  IF (_r.item_type = 'K') THEN
    SELECT SUM(roundQty(itemuomfractionalbyuom(bomitem_item_id, bomitem_uom_id),
                                               (bomitem_qtyfxd + bomitem_qtyper) * (1 + bomitem_scrap))
               * stdCost(bomitem_item_id)) INTO _cost
    FROM bomitem
    WHERE (bomitem_parent_item_id=_r.item_id)
      AND (bomitem_rev_id=getActiveRevid('BOM', _r.item_id))
      AND (pEffective BETWEEN bomitem_effective AND (bomitem_expires - 1));
  ELSEIF (fetchMetricBool('WholesalePriceCosting')) THEN
    _cost := _r.item_listcost / _r.itempriceinvrat;
  ELSE
    SELECT itemcost(_r.itemsite_id) INTO _cost;
  END IF;

  RETURN _cost;
END;
$$;


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

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

CREATE FUNCTION iteminventoryuominuse(pitemid 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
  _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) )
  LIMIT 1;
  IF (FOUND) THEN
    RETURN TRUE;
  END IF;

  RETURN FALSE;
END;
$$;


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

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

--
-- 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;
  _itempricepricerat 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) / itemuomtouomratio(item_id, item_inv_uom_id, item_price_uom_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 _itempricepricerat;

-- Price Schedule Assignment Order of Precedence
-- 1. Specific Customer Shipto Id
-- 2. Specific Customer Shipto Pattern
-- 3. Any Customer Shipto Pattern
-- 4. Specific Customer
-- 5. Customer Type
-- 6. Customer Type Pattern

-- 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) * _itempricepricerat
              WHEN ((ipsitem_type = 'M') AND _long30markups AND _wholesalepricecosting) THEN
               (_item.item_listcost / (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _itempricepricerat
              WHEN ((ipsitem_type = 'M') AND _long30markups) THEN
               (_item.invcost / (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _itempricepricerat
              WHEN (ipsitem_type = 'M' AND _wholesalepricecosting) THEN
               (_item.item_listcost + (_item.item_listcost * ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _itempricepricerat
              WHEN (ipsitem_type = 'M') THEN
               (_item.invcost + (_item.invcost * ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _itempricepricerat
              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 - 1))
    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 (ipsass_cust_id > -1) AND (_shipto.shipto_num ~ ipsass_shipto_pattern) AND (ipsass_cust_id = _cust.cust_id))
     OR   ((COALESCE(LENGTH(ipsass_shipto_pattern), 0) > 0) AND (ipsass_cust_id = -1) AND (_shipto.shipto_num ~ ipsass_shipto_pattern))
     OR   ((COALESCE(LENGTH(ipsass_shipto_pattern), 0) = 0) AND (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 AND COALESCE(ipsass_cust_id, -1) > 0) THEN 2
                WHEN (COALESCE(LENGTH(ipsass_shipto_pattern), 0) > 0) THEN 3
                WHEN (COALESCE(ipsass_cust_id, -1) > 0) THEN 4
                WHEN (COALESCE(ipsass_custtype_id, -1) > 0) THEN 5
                WHEN (COALESCE(LENGTH(ipsass_custtype_pattern), 0) > 0) THEN 6
                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) * _itempricepricerat
                WHEN ((ipsitem_type = 'M') AND _long30markups AND _wholesalepricecosting) THEN
                 (_item.item_listcost / (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _itempricepricerat
                WHEN ((ipsitem_type = 'M') AND _long30markups) THEN
                 (_item.invcost / (1.0 - ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _itempricepricerat
                WHEN (ipsitem_type = 'M' AND _wholesalepricecosting) THEN
                 (_item.item_listcost + (_item.item_listcost * ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _itempricepricerat
                WHEN (ipsitem_type = 'M') THEN
                 (_item.invcost + (_item.invcost * ipsitem_discntprcnt) + ipsitem_fixedamtdiscount) * _itempricepricerat
                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 - 1))
      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 (ipsass_cust_id > -1) AND (_shipto.shipto_num ~ ipsass_shipto_pattern) AND (ipsass_cust_id = _cust.cust_id))
       OR   ((COALESCE(LENGTH(ipsass_shipto_pattern), 0) > 0) AND (ipsass_cust_id = -1) AND (_shipto.shipto_num ~ ipsass_shipto_pattern))
       OR   ((COALESCE(LENGTH(ipsass_shipto_pattern), 0) = 0) AND (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;

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

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

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

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

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

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

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

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

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

-- Sometimes NULL itemsrc is passed (#25898)
  IF (pItemsrcid IS NULL) THEN 
    RETURN NULL;
  END IF; 

-- 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 WHEN (itemsrcp_dropship) THEN 0
                ELSE 1
           END AS seq,
           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 seq, 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;

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

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

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

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

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

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

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

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

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

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

  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;

--
-- 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
  IF (compareversion('9.2.0') <= 0)
  THEN
    PERFORM pg_advisory_unlock(datid::integer, pid)
     FROM pg_stat_activity
    WHERE(pid = pg_backend_pid());
  ELSE
    PERFORM pg_advisory_unlock(datid::integer, procpid)
       FROM pg_stat_activity
      WHERE(procpid = pg_backend_pid());
  END IF;

  RETURN 0;
END;
$$;


ALTER FUNCTION public.logout() OWNER TO admin;

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

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

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

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

END;
$_$;


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

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


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

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

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

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

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

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

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


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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

--
-- 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.
BEGIN
  RETURN numOfDatabaseUsers(NULL);
END;
$$;


ALTER FUNCTION public.numofdatabaseusers() OWNER TO admin;

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

CREATE FUNCTION numofdatabaseusers(pappname 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
  _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_locks
    LEFT JOIN pg_stat_activity ON pg_stat_activity.pid = pg_locks.pid
   WHERE     
     (pg_locks.objsubid = 2)
     AND (pg_stat_activity.datname = current_database())
     AND CASE WHEN (trim(coalesce(pAppName, '')) = '') THEN true ELSE application_name = pAppName END;
  ELSE
  SELECT count(*)
    INTO _count
    FROM pg_locks    
    LEFT JOIN pg_stat_activity ON pg_stat_activity.procpid = pg_locks.pid
   WHERE        
     (pg_locks.objsubid = 2)
     AND (pg_stat_activity.datname = current_database())
     AND CASE WHEN (trim(coalesce(pAppName, '')) = '') THEN true ELSE application_name = pAppName END;
  END IF;

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

  RETURN _count;

END;
$$;


ALTER FUNCTION public.numofdatabaseusers(pappname text) OWNER TO admin;

--
-- 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 WHERE datname = current_database();
  IF (_count IS NULL) THEN
    _count := 0;
  END IF;

  RETURN _count;

END;
$$;


ALTER FUNCTION public.numofserverusers() OWNER TO admin;

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

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

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

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

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

--
-- 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 ophead OWNER TO admin;

--
-- Name: TABLE ophead; Type: COMMENT; Schema: public; Owner: admin
--

COMMENT ON TABLE ophead IS 'Opportunity header.';


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

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


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

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

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

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

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

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

--
